# Mindmeld MySQL schema v1.17.2.1
# Mindmeld version 1.2
# Copyright (c) 2003 ars Cognita, Inc. all rights reserved

CREATE TABLE Thought (
thoughtId        INTEGER NOT NULL AUTO_INCREMENT,
summary          VARCHAR(255) NOT NULL,
detail           LONGTEXT,
datasource       VARCHAR(128),
dstype           VARCHAR(8) DEFAULT 'LOCAL',
dsname           VARCHAR(32),
interacted       DATETIME NOT NULL,
confidence       INTEGER NOT NULL DEFAULT 50,
certification    INTEGER DEFAULT 0,
certifier        VARCHAR(32),
learn            INTEGER NOT NULL DEFAULT 0,
review           INTEGER NOT NULL DEFAULT 0,
displayRaw       INTEGER NOT NULL DEFAULT 0,
status           VARCHAR(16) NOT NULL DEFAULT 'DRAFT',
                 PRIMARY KEY (thoughtId)
);
# --------------------------------------------------------

CREATE INDEX ThoughtSummary ON Thought (summary);
# --------------------------------------------------------

CREATE TABLE Symbol (
symbol           VARCHAR(80) NOT NULL,
interacted       DATETIME NOT NULL,
reality          VARCHAR(255),
tsNcount         INTEGER,
noiseWord        INTEGER,
                 PRIMARY KEY (symbol)
);
# --------------------------------------------------------

CREATE TABLE UpdSymbol (
symbol           VARCHAR(80) NOT NULL,
interacted       DATETIME NOT NULL,
reality          VARCHAR(255),
tsNcount         INTEGER,
noiseWord        INTEGER
);
# --------------------------------------------------------

CREATE TABLE TsNeuron (
thought          INTEGER NOT NULL,
symbol           VARCHAR(80) NOT NULL,
spatiality       INTEGER NOT NULL,
dominance        INTEGER NOT NULL,
enabled          TINYINT NOT NULL,
interacted       DATETIME NOT NULL,
                 PRIMARY KEY (thought, symbol)
);
# --------------------------------------------------------

CREATE INDEX TsNeuronEnabledSymbol ON TsNeuron (enabled , symbol);
# --------------------------------------------------------

CREATE INDEX TsNeuronThought ON TsNeuron (thought);
# --------------------------------------------------------

CREATE INDEX TsNeuronSymbol ON TsNeuron (symbol);
# --------------------------------------------------------

CREATE TABLE TsnModDom (
query            INTEGER NOT NULL,
thought          INTEGER NOT NULL,
symbol           VARCHAR(80) NOT NULL,
spatiality       INTEGER,
dominance        INTEGER NOT NULL,
enabled          TINYINT NOT NULL,
interacted       DATETIME NOT NULL
);
# --------------------------------------------------------

CREATE TABLE TtNeuron (
thought1         INTEGER NOT NULL,
thought2         INTEGER NOT NULL,
spatiality       INTEGER NOT NULL,
dominance        INTEGER NOT NULL,
enabled          TINYINT NOT NULL,
interacted       DATETIME NOT NULL,
                 PRIMARY KEY (thought1, thought2)
);
# --------------------------------------------------------

CREATE TABLE ThinkWork (
query            INTEGER NOT NULL,
thought          INTEGER NOT NULL,
symbol           VARCHAR(80) NOT NULL,
strength         INTEGER NOT NULL
);
# --------------------------------------------------------

CREATE TABLE Query (
queryId          INTEGER NOT NULL AUTO_INCREMENT,
metaquery        VARCHAR(64),
text             LONGTEXT NOT NULL,
created          DATETIME NOT NULL,
status           VARCHAR(30) NOT NULL,
                 PRIMARY KEY (queryId)
);
# --------------------------------------------------------

CREATE INDEX QueryStatus ON Query (status);
# --------------------------------------------------------

CREATE TABLE Stimulus (
query            INTEGER NOT NULL,
stimulus         VARCHAR(80) NOT NULL,
reality          VARCHAR(255)
);
# --------------------------------------------------------

CREATE TABLE RmStimulus (
query            INTEGER NOT NULL,
stimulus         VARCHAR(80) NOT NULL,
reality          VARCHAR(255)
);
# --------------------------------------------------------

CREATE TABLE Decision (
query            INTEGER NOT NULL,
thought          INTEGER NOT NULL,
strength         INTEGER NOT NULL,
visited          DATETIME,
status           VARCHAR(15),
                 PRIMARY KEY (query, thought)
);
# --------------------------------------------------------

CREATE TABLE RmDecision (
query            INTEGER NOT NULL,
thought          INTEGER NOT NULL,
strength         INTEGER NOT NULL,
visited          DATETIME,
status           VARCHAR(15)
);
# --------------------------------------------------------

CREATE TABLE Role (
role             VARCHAR(32) NOT NULL,
                 PRIMARY KEY (role)
);
# --------------------------------------------------------

CREATE TABLE Agent (
userId           VARCHAR(32) NOT NULL,
realName         VARCHAR(64),
email            VARCHAR(128),
status           INTEGER,
password         VARCHAR(64),
                 PRIMARY KEY (userId)
);
# --------------------------------------------------------

CREATE TABLE AgentRole (
userId           VARCHAR(32) NOT NULL,
role             VARCHAR(32) NOT NULL,
                 PRIMARY KEY (userId, role)
);
# --------------------------------------------------------

CREATE TABLE EventHistory (
userId           VARCHAR(32),
event            VARCHAR(32),
data             VARCHAR(64),
eventDate        DATETIME,
descr            VARCHAR(255)
);
# --------------------------------------------------------

CREATE TABLE Query_History (
query            INTEGER NOT NULL,
created          DATETIME NOT NULL,
closed           DATETIME NOT NULL,
thought          INTEGER,
strength         INTEGER,
query_status     VARCHAR(16) NOT NULL,
decision_status  VARCHAR(16)
);
# --------------------------------------------------------

CREATE TABLE Fundamentals (
fname            VARCHAR(40) NOT NULL,
fvalue           INTEGER,
fstring          VARCHAR(255),
description      VARCHAR(255),
                 PRIMARY KEY (fname)
);
# --------------------------------------------------------

CREATE TABLE DataSource (
dsname           VARCHAR(32) NOT NULL,
dsuser           VARCHAR(32),
dspass           VARCHAR(128),
arg1             VARCHAR(255),
arg2             VARCHAR(255),
arg3             VARCHAR(255),
arg4             VARCHAR(255),
descr            VARCHAR(255),
                 PRIMARY KEY (dsname)
);
# --------------------------------------------------------

 insert into Fundamentals ( fname, fvalue, description ) values ( 'default_tsneuron_dominance', 100, 'Default dominance value for a new tsNeuron.' );
# --------------------------------------------------------

 insert into Fundamentals ( fname, fvalue, description ) values ( 'tsneuron_reinforcement', 10, 'Amount a tsNeuron is reinforced after a successful search.' );
# --------------------------------------------------------

 insert into Fundamentals ( fname, fvalue, description ) values ( 'tsneuron_weakening', 1, 'Amount a tsNeuron is weakened after an unsuccessful search.' );
# --------------------------------------------------------

 insert into Fundamentals ( fname, fvalue, description ) values ( 'thought_quality_weighting', 5 , 'Impact of thought quality on decision sorting, expressed as a percentage.' );
# --------------------------------------------------------

 insert into Fundamentals ( fname, fvalue, description ) values ( 'noiseword_threshold', 4, 'Words which greater than this number of standard deviations of occurances are considered noise.' );
# --------------------------------------------------------

 insert into Fundamentals ( fname, fvalue, description ) values ( 'stale_query_age', '120', 'Dream state will ignore all queries younger then this age (in minutes)' );
# --------------------------------------------------------

 insert into Fundamentals ( fname, fvalue, description ) values ( 'max_results_returned', 20, 'Maximum number of results returned by a search.' );
# --------------------------------------------------------

 insert into Fundamentals ( fname, fstring, description ) values ( 'default_answer_status', 'ACTIVE', 'Default status for newly-entered answers.' );
# --------------------------------------------------------

 insert into Fundamentals ( fname, fvalue, description ) values ( 'min_symbol_variance', 10.0, 'Minimum variance in symbol counts before noisefilter will run.' );
# --------------------------------------------------------

 insert into Fundamentals ( fname, fvalue, description ) values ( 'wysiwyg_editor', 0, 'Enable WYSIWYG editor for editing answers.' );
# --------------------------------------------------------

 insert into Fundamentals ( fname, fstring, description ) values ( 'new_tsneuron_freq_weighting', '0.1', 'Dominance increase (as a fraction of default dominance) for each additional occurance in a new thought' );
# --------------------------------------------------------

 insert into Role ( role ) values ( 'read' );
# --------------------------------------------------------

 insert into Role ( role ) values ( 'add' );
# --------------------------------------------------------

 insert into Role ( role ) values ( 'edit' );
# --------------------------------------------------------

 insert into Role ( role ) values ( 'delete' );
# --------------------------------------------------------

 insert into Role ( role ) values ( 'admin' );
# --------------------------------------------------------

 insert into Agent( userId, status, password ) values ( 'Administrator', 1, '7b7bc2512ee1fedcd76bdc68926d4f7b' );
# --------------------------------------------------------

 insert into AgentRole( userId, role ) values ( 'Administrator', 'admin' );
# --------------------------------------------------------

 insert into EventHistory ( userId, event, data, eventDate, descr ) values ( 'SYSTEM', 'CREATE', 'MindMeld Database', now(), user() );
# --------------------------------------------------------

# End of Mindmeld schema