#--------------------------------------------------------------
# Scone SQL data model
# Version: 1.2.0
# Last Update: 10. October 2006
#--------------------------------------------------------------
# To install it, call at the command line:
# mysql -u root -p < sconedb.sql
#
# You should also set
# set-variable = max_allowed_packet=16M
# in the "my.ini" file. This is required if you want to store
# the document body or source (for mysql <=4.0)
# For mysql 4.1 ff please set/add
# max_allowed_packet=16M
# in the [mysqld] section
# and add  "jdbcCompliantTruncation=false" to the connection string, i.e. start
# "runscone -config", goto "database" and set database URI to
# jdbc:mysql://localhost:3306/Scone?jdbcCompliantTruncation=false


CREATE DATABASE IF NOT EXISTS scone;
USE scone;

drop table if exists NetNodeTable;
drop table if exists HtmlDocumentTable;
drop table if exists ServerTable;
drop table if exists LinkTable;
drop table if exists InclusionTable;

drop table if exists AccessTable;

drop table if exists Node2KeyTable;
drop table if exists KeywordTable;

drop table if exists UserTable;
drop table if exists PersonTable;
#drop table if exists GroupTable;
#drop table if exists Person2GroupTable;

#--------------------
# netnodes are all objects accessable by an uri.
CREATE TABLE NetNodeTable(
nodeId           CHAR(16)  NOT NULL PRIMARY KEY DEFAULT "",   # (Quite) unique hashkey of node.
uri              CHAR(255) NOT NULL DEFAULT "",    # complete uri
size             INT DEFAULT -1,                   # size of the netnode in bytes
lastModified     BIGINT DEFAULT -1,   	           # last modification date of file on server
mimeType         CHAR(32) DEFAULT "",              # mime-type of netnode
accessStatus     CHAR(3) DEFAULT -1,               # http-status at last access (e.g. 404), -1 = never visited.
host             CHAR(64)  NOT NULL DEFAULT "",    # hostname, used for faster access.
type             INT       NOT NULL DEFAULT 0,     # NetNode type information: See netobjects/NetNode.java
firstAccess      BIGINT DEFAULT 0,            	   # creation date of the record.
lastAccess       BIGINT DEFAULT 0,            	   # last access to the node
lastRobotAccess  BIGINT DEFAULT 0,            	   # last access of robot to the netnode
accessCounter    INT DEFAULT 0,               	   # total numer of user accesses to the netnode.
INDEX nodeId (nodeId),
INDEX host (host(16)),
INDEX uri (uri(32)) ) TYPE=MyISAM ;

# HtmlDocuments are those netnodes that are Web-Documents (mime Type HTML)
CREATE TABLE HtmlDocumentTable(
nodeId                  CHAR(16) NOT NULL PRIMARY KEY DEFAULT "", #every HtmlDocument has a NetNode
title                   VARCHAR(255) DEFAULT "",  #<title>Welcome to my homepage...</title>
author                  VARCHAR(255) DEFAULT "",  #Meta
description             VARCHAR(255) DEFAULT "",  #Meta
abstract                VARCHAR(255) DEFAULT "",  #First lines of Text...
language                CHAR(2) DEFAULT "",       #de, en, fr etc.
bodytext                MEDIUMBLOB DEFAULT "",    #The body of the document without tags. (optional, see requirements)
sourcecode              MEDIUMBLOB DEFAULT "",    #The complete source code of the page. (optional, see requirements)
numberOfLinks           INT DEFAULT -1,           #Number of links of this document: Only requirement needed: ParseDocument
numberOfExternalLinks   INT DEFAULT -1,           #Number of external links (to other server)
numberOfImages          INT DEFAULT -1,           #Number of Images <IMG>
spaceOfImages           BIGINT DEFAULT -1,        #Space in Pixel*Pixel, which is covered by images.
numberOfWords           INT DEFAULT -1,           #Number of words
numberOfParagraphs      INT DEFAULT -1,           #Number of paragraphs (<P>s)
fingerprint             CHAR(16) DEFAULT "",      #Fingerprint of the page: From sourcecode calculated hex number to identify two identical pages.
type                    INT NOT NULL DEFAULT 0,   #Bit-Array with Type-Infos to this Document
INDEX nodeId (nodeId)) TYPE=MyISAM ;

# Table to store web-server specific information.
CREATE TABLE ServerTable(
host             CHAR(64) PRIMARY KEY DEFAULT "", #dns host name, e.g. vsis-www.informatik.uni-hamburg.de
title            VARCHAR(255) DEFAULT "",         #parsed titel of homepage
description      VARCHAR(255) DEFAULT "",         #description of the homepage
delay            INT DEFAULT 0,                   #estimated delay of server at last access
accessStatus     CHAR(3) DEFAULT "",              #instead of httpStatus
firstAccess      BIGINT DEFAULT 0,                #creation date of the record
lastAccess       BIGINT DEFAULT 0,                #last update of the record
accessCounter    INT DEFAULT 0,               	  #number of accesses to this server in total?
INDEX host (host(16)) ) TYPE=MyISAM ;

# Table of (hyper)-links
CREATE TABLE LinkTable(
linkId           CHAR(16) NOT NULL PRIMARY KEY DEFAULT "-1",		#Quite unique key of link Object.
fromNodeId       CHAR(16) NOT NULL DEFAULT "",    #ID of netnode (i.e. htmlDocument) where the link is located
toNodeId         CHAR(16) NOT NULL DEFAULT "",    #referenced netnode (i.e. uri)
fragment         VARCHAR(64) NOT NULL DEFAULT "", #the part of the uri after the hash(#)-symbol
linkText         VARCHAR(255) DEFAULT "",         #<a ...>linkText</a>
target           VARCHAR(32) NOT NULL DEFAULT "", #target-frame/window
title            VARCHAR(255) DEFAULT "",         #title attribute
alt              VARCHAR(255) DEFAULT "",         #ALT-text of graphics inside that link.
rel              VARCHAR(32) DEFAULT "",          #Type of the relation (rarely used...)
rev              VARCHAR(32) DEFAULT "",          #Reverse type (even more rarely used)
type             INT         NOT NULL DEFAULT 0, #Link type information, stored in 32 Bits. See netobjects/Link.java
INDEX linkId 		(linkId),
INDEX fromNodeId 	(fromNodeId),
INDEX toNodeId 	(toNodeId),
INDEX fragment 	(fragment(16)),
INDEX type      	(type) ) TYPE=MyISAM ;

# Inclusions are Images, Frames, Applets. Only filled if requirement is set. See scone.Plugin
CREATE TABLE InclusionTable(
inclusionId      CHAR(16) NOT NULL PRIMARY KEY DEFAULT "",
parentNodeId     CHAR(16) NOT NULL DEFAULT "",    # What netnode / htmlDocument does include sth.
childNodeId      CHAR(16) NOT NULL DEFAULT "",    # What is included
tag              VARCHAR(12) DEFAULT "",          # Name of the tag, e.g. img, frame, applet etc.
info             VARCHAR(64) DEFAULT "",          # additional information. see util/documentParser
INDEX inclusionId	 (inclusionId),
INDEX parentNodeId (parentNodeId),
INDEX childNodeId  (childNodeId)) TYPE=MyISAM ;

# AccessTable stores the user history. Only filled if accesstracking is "required".
CREATE TABLE AccessTable(                 #Netnode access logging
userId               CHAR(16) NOT NULL DEFAULT "",
nodeId               CHAR(16) NOT NULL DEFAULT "",  #the netNodeId of this page
time                 BIGINT NOT NULL DEFAULT 0,      #access time
frameName            CHAR(32) NOT NULL DEFAULT "",   #name of the frame or window (created if not assigned)
accessId             CHAR(16) NOT NULL DEFAULT "",   #id string
parentAccessId       CHAR(16) DEFAULT "",            #points to the parent access event in a frame set
action               INT DEFAULT 0,                  #Last action of the user as bitarray
event                VARCHAR(255) DEFAULT "",        #Last action of the user as string (needed for Firefox events!)
stepsInHistory       INT DEFAULT 0,                  #Number of Pages jumped over by back or next pulldown menu
fragment             VARCHAR(30) DEFAULT "",         #Part of the URL after the hash symbol
query                MEDIUMBLOB DEFAULT "",          #did the user specify a query? Get parameters are stored here
post                 MEDIUMBLOB DEFAULT "",          #POST parameters are stored here if available
parentFrameNodeId    CHAR(16) DEFAULT "",            #nodeId of parent frame
parentFrameName      VARCHAR(100) DEFAULT "",        #name of parent frame
referrerNodeId       CHAR(16) DEFAULT "",            #where does the user come from?
linkId               CHAR(16) DEFAULT "",            #this link was clicked by the user to leave the page
linkType             INT DEFAULT 0,                  #bit array with type of link. See netnodes.link for meaning
linkAnchor           VARCHAR(255) DEFAULT "",        #Contents of link anchor (limited to 255 chars...)
linkTitle            VARCHAR(255) DEFAULT "",        #Title Attribute of link anchor (limited to 255 chars...)
linkPos              VARCHAR(12)  DEFAULT "",        #Position of link on page, e.g. 200x123
windowSize           VARCHAR(12)  DEFAULT "",        #size of browser window, e.g. 900x500
loadTime             BIGINT DEFAULT 0,               #page load time in ms
stayTime             BIGINT DEFAULT 0,               #stay time in ms
PRIMARY KEY (userId,nodeId,time,frameName,accessId),
INDEX identity (userId, nodeId, time, frameName),
INDEX userId (userId),
INDEX nodeId (nodeId),
INDEX time   (time),
INDEX frameName (frameName),
INDEX accessId (accessId)) TYPE=MyISAM ;

# Store keywords to Databases.
CREATE TABLE Node2KeyTable(
nodeId           CHAR(16) NOT NULL DEFAULT "0",
keywordId        CHAR(16) NOT NULL DEFAULT "0",
PRIMARY KEY (nodeId,keywordId),
INDEX nodeId		(nodeId),
INDEX keywordId 	(keywordId)) TYPE=MyISAM ;

CREATE TABLE KeywordTable(
keywordId        CHAR(16)     NOT NULL PRIMARY KEY DEFAULT "",
keyword          VARCHAR(64)  NOT NULL DEFAULT "",
INDEX keywordId 	(keywordId),
INDEX keyword 		(keyword(16))) TYPE=MyISAM ;

# Tables used for usertracking. Scone can handle and distinguish different users by using cookies.
CREATE TABLE UserTable(
userId      INT AUTO_INCREMENT PRIMARY KEY,
userName    CHAR(16) NOT NULL DEFAULT "",
userPass    CHAR(16) NOT NULL DEFAULT "",
personId    CHAR(16) NOT NULL DEFAULT "",
lastLogin   BIGINT DEFAULT 0,
KEY (userName)) TYPE=MyISAM ;

# Name etc. of the user.
CREATE TABLE PersonTable(
personId     CHAR(16) PRIMARY KEY DEFAULT "",
firstName    VARCHAR(32) DEFAULT "",
secondName   VARCHAR(32) DEFAULT "",
lastName     VARCHAR(32) DEFAULT "",
organization VARCHAR(32) DEFAULT "",
street       VARCHAR(64) DEFAULT "",
city         VARCHAR(32) DEFAULT "",
state        VARCHAR(32) DEFAULT "",
postalCode   VARCHAR(12) DEFAULT "",
phone1       VARCHAR(32) DEFAULT "",
phone2       VARCHAR(32) DEFAULT "",
mobile       VARCHAR(32) DEFAULT "",
faxNumber    VARCHAR(32) DEFAULT "",
email        VARCHAR(32) DEFAULT "",
homepage     VARCHAR(255) DEFAULT "") TYPE=MyISAM ;

