/*
Schema-template for Pro/3 knowledge bases (KBs) and publication networks.

1. An SQL server can include any number of databases, and each database may or
may not include one or more KBs (besides, possibly tables non-related to the KBs).
There is no restriction on the name of databases which contain KBs (and no
required relationship between the name of the database and the name of the KB).
RECOMMENDED: Use the same name for the database and the KB (and thus
have a one-to-one relationship between database(s) and KB(s).

2. Pro/3 generates all KB-related tables and optionally the database upon KB
initialization or KB export.

3. One knowledge base cannot span more than one SQL database (excluding published
or subscribed sentence types which can reside anywhere - ref. 5).

4. A KB xxx always include the two tables  "p3kb_xxx" and "p3pm_xxx", besides any
number of chain tables (chain table names are prefixed according to user preference).

5. An SQL server can also include one Pro/3 publication network database. This
database has name "p3publications" and always includes the five tables "pub_net",
"publication", "subscription", "cluster" and "cluster_member" (the tables can be empty).
Tables with published sentences can reside in any database including
"p3publications".

6. A knowledge stream-table can be located inside a knowledge base database
(or actually in any other database).

7.

Pro/3 version 1.0
Last updated: 18.6.2006 Jens H. Holm
*/
/* ------------------------------------------------------------------------------------------------------------------------------ */
create table p3kb_xxx
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
Table p3kb_xxx is the main table for KB xxx.
The table has one row per KB record (any chain). It contains a "summary" of the record
which exists in full in a separate chain table (columns chain+ref identifies the corresponding
record in the chain table. Refer to Pro/3 KB internals documentation.
*/
(
record_type tinyint not null,
     /* ref. to record type in Pro/3 */
base_type tinyint unsigned not null,
     /* ref. to record base type in Pro/3 */
active tinyint unsigned not null,
     /* 0/1 */
suspended tinyint unsigned not null,
    /* 0/1*/
record_date datetime not null,
     /* record datetime */
realm varchar(128) not null,
    /* record realm */
recno int unsigned not null,
    /* record no */
subno int unsigned not null,
    /* record sub no */
st varchar(255),
     /* sentence type if applicable */
chain varchar(128) not null,
    /* record chain */
ref int unsigned not null auto_increment,
    /* this number relates the row to the corresponding row in a chain table */
primary key (chain,ref),
index (base_type),
index (active),
index (suspended),
index (realm),
index (realm,recno,subno),
index (st),
index (chain),
index (ref)
);
/* ------------------------------------------------------------------------------------------------------------------------------ */
create table p3pm_xxx
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
Table p3pm_xxx is the parameter table for KB xxx. The table has only record which
contains a set of parameters for the KB.
*/
(
version int unsigned not null,
     /* e.g. 506 for Pro/3 version 5.6 [note that 0 was used prior to 5.6] */
created datetime not null,
     /* table creation datetime */
chain_prefix varchar(32) not null,
     /* prefix for chain-table names - default is "c_" - can be blank */
st_prefix varchar(32) not null,
     /* prefix for ST-table names - default is "s_" - can be blank */
name_case tinyint unsigned not null,
     /* 1:mixed,2:lower, 3:upper case for generated SQL table and column names */
use_underscore char(1) not null,
     /* character (e.g. underscore) to replace replace blanks in generated SQL table and column
        names. Blanks are simply ignored if the value is blank. */
max_length tinyint not null,
     /* max. length of SQL column and table names */
st_db varchar(64) not null,
     /* default SQL database for ST-tables */
term_format tinyint not null
     /* Term-format in this KB: 1:Pro/3 term-strings, 2:VIP term-strings [v. 5.6 onwards] */
term_length int not null
     /* The max. length of term-strings [v.5.6 onwards] */
);
/* ------------------------------------------------------------------------------------------------------------------------------ */
create table [prefix]chain
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
Table [prefix]chain corresponds to a KB chain and each row to a record.
Refer to Knowledge Base Internals in Pro/3 documentation.
*/
(
ref int unsigned not null primary key,
     /* this number relates the row to the corresponding row in the main table */
base_type tinyint not null,
     /* ref. to record base type in Pro/3 */
st varchar(255),
     /* sentence type (IF) */
ie tinyint unsigned not null,
     /* 1=record is relevant for inference engine, 0=not */
lterm text not null,
     /* record's left term (conclusion) */
rterm text not null,
     /* record's right term (condition) */
index (ie),
index (base_type),
index (st)
);
/* ------------------------------------------------------------------------------------------------------------------------------ */
create table pub_net
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
The table contains one row per publishing network.
*/
(
name varchar(64) not null primary key,
     /* name of the publication network */
share_format tinyint not null,
     /* 0=no open sharing, else open sharing with knowledge format 1=NL,8=PR,9=SQL,
        10=XML/NL,11=XML/PR */
share_table varchar(128),
     /* (if share_format<>0) name of open share table (qualified with db_name) */
default_db varchar(128),
     /* default publishing database */
updated datetime not null
     /*  (if share_format<>0) datetime of last update of open share table */
);
/* ------------------------------------------------------------------------------------------------------------------------------ */
create table publication
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
The table contains one row per publication.
*/
(
net varchar(64) not null,
     /* name of the publication network */
st varchar(128) not null,
     /* sentence type (IF) or blank if open share */
table_name varchar(64) not null,
     /* name of table used for this publication (format: db_name.table_name, blank if open share publication) */
st_format tinyint not null,
     /* 1=NL,8=PR,9=SQL,10=XML/NL,11=XML/PR, 0=if open share publication */
mode tinyint unsigned not null,
     /* 1=incremental update, 2=replacement update */
kb varchar(128),
     /* the publishing KB - only one if usage=2 */
last_pub datetime not null,
     /*  datetime of last update of sentence type */
primary key (net,st,table_name,kb)
);
/* ------------------------------------------------------------------------------------------------------------------------------ */
create table subscription
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
The table contains one row per sentence type subscription.
*/
(
net varchar(64) not null,
     /* name of the publication network */
st varchar(128) not null,
     /* sentence type (IF) or blank if open share */
mode tinyint unsigned not null,
     /* 1=incremental update, 2=replacement update */
kb varchar(128),
     /* the subscribing KB */
last_sub datetime not null,
     /*  datetime of last subscription */
primary key (net,st,kb)
);
/* ------------------------------------------------------------------------------------------------------------------------------ */
create table cluster
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
The table contains one row per KB cluster.
*/
(
cluster_name varchar(64) not null,
     /* name of the cluster */
net_name varchar(64) not null,
     /* publishing net with cluster's open share table */
knowledge_stream varchar(128)
     /* knowledge stream table (must have DB qualifier) */
);
/* ------------------------------------------------------------------------------------------------------------------------------ */
create table cluster_member
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
The table contains one row per KB cluster member KB.
*/
(
cluster_name varchar(64) not null,
     /* name of the cluster */
kb varchar(128) not null,
     /* member's qualified KB name */
member_no int not null,
     /* an integer which uniquely distinguishes each member of a cluster. member 0
        is the originating member of the cluster. the unqualified name of this KB is
        always the same as the cluster name. all other members of the cluster has
        the unqualified KB equal to the cluster name with the number joined postfix
        with an underscare e.g. xxx_3 */
last_share datetime not null,
     /* datetime of last open share publication */
last_derivation datetime not null,
     /* datetime of last sentence derivation */
complete tinyint unsigned not null
     /* 1=all knowledge derived, 0=not */
);
/* ------------------------------------------------------------------------------------------------------------------------------ */
create table <open_share_table>
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
The table contains the open share for a publication network (one row per sentence)
*/
(
sentence text not null,
     /* a sentence in the format designated in the pub_net table */
created datetime not null
     /*  datetime of row creation */
);
/* ------------------------------------------------------------------------------------------------------------------------------ */
create table <sentence_sql_table>
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
The table contains a published sentence type (SQL format) for a publication network.
The rows have one column per data element type in the sentence type in the same order
plus one column for the creation date.
*/
(
created datetime not null
     /*  datetime of row creation */
);
/* ------------------------------------------------------------------------------------------------------------------------------ */
create table <sentence_non_sql_table>
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
The table contains a published sentence type (all formats except SQL) for a publication
network.
*/
(
sentence text not null,
     /* a sentence in the format designated in the pub_net table */
created datetime not null
     /*  datetime of row creation */
);
/* ------------------------------------------------------------------------------------------------------------------------------ */
create table <knowledge_stream>
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
The table contains a knowledge stream where each row represents a knowledge record.
*/
(
k_format text not null,
     /* 1=NL,8=PR,10=XML */
k_type tinyint unsigned not null
     /*  2=sentence, 3=sentence rule, 4=function definition, 6=inexact rule */
knowledge text not null,
     /* a sentence in the format designated in the pub_net table */
created datetime not null
     /*  datetime of row creation */
processed tinyint unsigned not null,
     /*  1=record is read, 0=not */
kb varchar(128) not null,
     /* read by: qualified KB name */
source varchar(64) not null
    /* source of knowledge e.g. user */
);