create database if not exists p3bridge;

use p3bridge;

/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
Schema for the Pro/3 p3bridge query dispatcher database, including the poker
web system.
Pro/3 version 1.0
Last updated: 20.7.2006 Jens H. Holm
*/
/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3admin;
create table p3admin
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
Only one row is used for the last used request# (shared by all Pro/3 servers using this p3bridge).
*/
( last_used_no int unsigned not null primary key not null primary key
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3message;
create table p3message
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
Each row corresponds to a message from the p3bridge database administrator to clients.
*/
(
no int unsigned not null primary key, type tinyint not null, /* 0=for all users; 1=for all users of given KB; 2=for given user */
posted datetime not null, /* datetime posted */
user_name char(64), /* --> p3user */
kb char(64), /* --> p3server */
message text /* the message */
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3server;
create table p3server
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per Pro/3 server (pro3.exe invocation) using this p3bridge.
Records are set by the Pro/3 servers for information to clients.
*/
(
kb char(64) not null primary key,
    /* the name of the KB */
knowledge_stream varchar(128),
     /* optional knowledge stream table associated with the KB (must have DB qualifier) */
status_time datetime,
    /* datetime when status was set */
status tinyint not null
    /* 0=disconnected 1=serving requests, 2=paused */
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3request;
create table p3request
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per request.
*/
(
request_no int unsigned not null primary key, /* 1,2,... */
user_name char(64) not null, /* --> p3user */
kb char(64), /* --> p3server */
response_format tinyint, /* subset of Pro/3 output formats (1,8,9,10,11) [refer to Pro/3 documentation for details] */
request_time datetime not null, /* when request submitted */
request_start datetime, /* when processing started by server */
request_end datetime, /* when processing ended by server */
query_type tinyint not null,
/* 
0=rederive sentences,
1=NL query,
9=PR query,
51= NL DB query,
59=PR DB query,
61=NL cluster query,
69=PR cluster query,
101,102,... reports
*/
query_text text,
request_status int not null,
/*
1=input (by client),
2=read (by server - being processed),
3=answered (by server),
4=(only if no errors) answer read (by client),
5=(only if errors) error report read (by client),
6=(only if no errors) response records/tables deleted (also final state for type 0 requests)
7=(only if errors) response records/tables deleted,
<0 errors:
-1 invalid response type,
-2 invalid response format,
-3 invalid query type,
-4 invalid query,
-10 other processing error,
-11 user break (during IR questioning),
-12 user timeout (during IR questioning),
-13 invalid request type,
-100 invalid user name,
-101 user name does not exist
-102 other error when creating user account
-200 rederivation request in non-subscriptions mode
*/
no_of_solutions int, /* #solutions in case of non-table output; #tables in case of table output; error# when errors (=original request status) */
save_query tinyint unsigned not null, /* 1=save query, 0=not (applies to query_type 1/9 only) queries with errors are not saved */
query_name text /* query name (applies to save_query=1 only) */
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3response_table;
create table p3response_table
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per table generated by a (sub-)request (for response_format 8 only).
*/
(
request_no int unsigned not null, /* --> p3requesr */
sub_no tinyint unsigned not null, /* 1,2,... */
table_no int not null, /* 1,2,... */
table_name char(66) not null, /* 'Rnxxx' where n=request_no and xxx is derived from the ST */
title text not null,
no_of_cols tinyint unsigned not null,
width int not null,
no_of_rows int unsigned not null,
primary key (request_no,sub_no,table_no)
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3response_column;
create table p3response_column
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per response table and column generated by a (sub-)request (for response_format 8 only).
*/
(
request_no int unsigned not null, /* --> p3request */
sub_no tinyint unsigned not null, /* 1,2,... */
table_no int not null, /* 1,2,... */
column_no tinyint unsigned not null, /* 1,2,... */
width int unsigned not null,
domain char(64) not null,
sql_type char(16) not null,
title text not null,
column_name char(66) not null,
primary key (request_no,sub_no,table_no,column_no)
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3response;
create table p3response
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per (sub-)request response line (= sentence or single-value) (for response_format <> 8 only).
*/
(
request_no int unsigned not null, /* p3request */
sub_no tinyint unsigned not null, /* 1,2,... */
line_no int unsigned not null, /* 1,2,... */
line text not null,
primary key (request_no,sub_no,line_no)
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3user;
create table p3user
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per user (user_name and password are always stored in lower case).
*/
(
user_name char(64) not null primary key,
password char(32) not null,
password_hint text, /* not used by Pro/3 web client */
date_created datetime not null,
date_last_used datetime,
access_count int not null /* login count */
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3cluster;
create table p3cluster
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per DB query cluster  [ref. Pro/3 documentation: Queries and Query Menus Stored in the KB].
*/
(
cluster_name char(128) not null,
kb char(64) not null, /* --> p3server */
user_name char(64) not null, /* -->p3user or Pro/3 */
query_format char(2) not null, /* NL or PR */
primary key (kb,user_name,cluster_name)
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3query_cluster;
create table p3query_cluster
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per query in cluster  [ref. Pro/3 documentation: Queries and Query Menus Stored in the KB].
*/
(
cluster_name char(128) not null, /* --> p3cluster */
kb char(64) not null, /* --> p3server */
user_name char(64) not null, /* --> p3user or Pro/3 */
query_name char(128) not null, /* --> p3query */
primary key (kb,user_name,cluster_name,query_name)
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3query;
create table p3query
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per query [ref. Pro/3 documentation: Queries and Query Menus Stored in the KB].
*/
(
query_name char(128) not null, /* a (menu-)name assigned to the query */
query_text text not null, /* the query possibly with parameters */
user_name char(64) not null, /* --> p3user or Pro/3 */
kb char(64) not null, /* --> p3server */
query_format char(2) not null, /* NL or PR */
primary key (kb,user_name,query_name)
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3parameter;
create table p3parameter
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per (query) parameter [ref. Pro/3 documentation: Queries and Query Menus Stored in the KB].
*/
(
parameter_name char(64) not null, /* the name of the parameter (as it is used in queries) */
kb char(64) not null, /* --> p3server */
user_name char(64) not null, /* --> p3user or Pro/3 */
description text not null, /* parameter descriptive name */
domain char(32) not null, /* the parameter's domain (in NL-format) */
quoted tinyint unsigned not null, /* 1=value should be quoted, 0=value should NOT be quoted */
primary key (kb,user_name,parameter_name)
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3alternative;
create table p3alternative
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per parameter answer alternative [ref. Pro/3 documentation: Queries and Query Menus Stored in the KB].
*/
(
alternative_no tinyint unsigned not null, /* 1,2,... */
alternative text not null, /* the answer alternative (value) */
kb char(64) not null, /* --> p3server */
user_name char(64) not null,  /* -->p3user or Pro/3 */
parameter_name char(64) not null, /* --> p3parameter */
primary key (kb,user_name,parameter_name,alternative_no)
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3query_parameter;
create table p3query_parameter
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per parameter in query [ref. Pro/3 documentation: Queries and Query Menus Stored in the KB].
*/
(
parameter_name char(64) not null, /* --> p3parameter */
kb char(64) not null, /* --> p3server */
user_name char(64) not null, /* --> p3user (owner) */
query_name char(128), /* --> p3query */
primary key (kb,parameter_name,query_name)
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3input_facility;
create table p3input_facility
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per IR input facility. there can be at most one record per request_no at any time. [ref. Pro/3 documentation of inexact rule input facilities.
*/
(
request_no int not null primary key, /* --> p3request */
facility_type tinyint unsigned not null, /* 1=series, 2=cluster */
no_of_questions int unsigned not null, /* #questions in facility */
status tinyint unsigned not null, /* 1=facility given, 2=facility received, 3=facility answered, 4=IR evaluation aborted */
prompt text not null /* facility prompt */
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3question;
create table p3question
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per inexact rule question. there can be at most one record per request_no at any time for non series/cluster
questions. there can be questions from at most one series/cluster at any time.
*/
(
request_no int not null, /* --> p3request */
question_no int unsigned not null, /* not in cluster/series:0, else 1,2,.. */
rule char(64) not null,
question_type tinyint unsigned not null, /* 0=simple question, >0=question with N alternative answers */
question text not null,
domain char(32) not null,
status tinyint unsigned not null, /* 1=question given, 2=question received, 3=question answered */
default_answer text not null,
question_help text,
answer text,
primary key (request_no,question_no)
);

/* ------------------------------------------------------------------------------------------------------------------------------ */
drop table if exists p3answer;
create table p3answer
/* ------------------------------------------------------------------------------------------------------------------------------ */
/*
One row per inexact rule answer alternative.
*/
(
request_no int not null, /* --> p3request */
question_no int not null, /* --> p3question */
alternative tinyint unsigned not null, /* --> p3alternative */
answer text not null,
primary key (request_no,question_no,alternative)
);