/* exclude pod command for mysql

=head1 NAME

crea_tabs - script to create all open theory tables using MySQL

=head1 SYNOPSIS

This script includes all DML-commands for the creation of open theory
tables.

=head1 DESCRIPTION

The data structure of the tables are roughly described in the following.

=head2 table member

Projects are used by members, or initiated by members = maintainer

Field opt = options bitwise, meaning: the user wants to
S<1: get the Newsletter
2: get emails from other project members (meta mailinglist)
4: be informed about new projects
8: be informed about new versions in membership projects
16: be informed about new versions in all projects>

table member:
S<
memb_id smallint unsigned not null        # member id
                          auto_increment, #
last_n  varchar( 50 )     not null,       # last name
first_n varchar( 30 )     not null,       # first name
email   varchar( 80 )     not null,       # email address
passwd  varchar( 50 )     not null,       # password
subscr  datetime          not null,       # subcription date
opt     smallint          not null        # options
                          default -1,     # all are enabled
bounce  smallint          not null        # count bounces
                          default 0,      # default no bounces
x_id    int unsigned      not null        # session id
                          default 0,      # no session
primary key( memb_id ),                   # memb_id = p_key
unique key email_idx( email )             # index on email>

=cut */

create table member (
	memb_id	smallint unsigned	not null auto_increment,
	last_n	varchar( 50 )		not null,
	first_n	varchar( 30 )		not null,
	email	varchar( 80 )		not null,
	passwd	varchar( 50 )		not null,
	subscr	datetime		not null,
	opt	smallint		not null default -1,
	bounce	smallint		not null default 0,
	x_id	int unsigned		not null default 0,
	primary key( memb_id ),
	unique key email_idx( email )
);/*

=head2 table metatext

Metatexts generally chracterize projects.
A member given in the metatext record is the maintainer of the project.
Valid values of status are:
S<"ini" (initialized)
"act" (active)
"arc" (archives)
"fin" (finished)>

Field membnum: number of members of the project including all subprojects

table metatext:
S<
proj_id smallint unsigned not null        # project id
vers_id smallint unsigned not null,       # version number
memb_id smallint unsigned not null,       # member id
title   varchar( 255 )    not null,       # project title
titlabb varchar( 15 )     not null,       # title abbreviation
submit  datetime          not null,       # submission date
status  char( 3 )         not null,       # text status
lang    char( 2 )         not null        # language
                          default 'de',   #
ref_by  smallint unsigned not null,       # referenced by project
maillst varchar( 127 )    not null,       # mailinglist name
link    varchar( 255 )    null,           # link to base text
keywds  varchar( 255 )    null,           # keywords for metatag
membnum smallint unsigned not null        # member number
                          default 1,      #
descr   blob              not null,       # short description
primary key( proj_id, vers_id ),          # p_id/v_id = p_key
key titlabb_idx( titlabb )                # index on titlabb>

=cut */

create table metatext (
	proj_id	smallint unsigned	not null,
	vers_id	smallint unsigned	not null,
	memb_id	smallint unsigned	not null,
	title	varchar( 255 )		not null,
	titlabb	varchar( 15 )		not null,
	submit	datetime		not null,
	status	char( 3 )		not null,
	lang	char( 2 )		not null default 'de',
	ref_by	smallint unsigned	not null,
	maillst	varchar( 127 )		not null,
	link	varchar( 255 )		null,
	keywds	varchar( 255 )		null,
	membnum	smallint unsigned	not null default 1,
	descr	blob			not null,
	primary key( proj_id, vers_id ),
	key titlabb_idx( titlabb )
); /*

=head2 table paragraph

A paragraph is the basic text block of a text project. Paragraphs are
described by the paragraph string builded by three parts:
S<- 6 leading chars defining 999999 paragraphs of the text in maximum
- 61 x 4 chars defining 61 comment levels with max. 9999 paragraphs each
- 1 trailing char indicating the string end ("#")>

table paragraph:
S<
proj_id smallint unsigned not null,       # projekt id
vers_id smallint unsigned not null,       # version number
parastr varchar( 251 )    not null,       # paragraph string
submit  datetime          not null,       # submission date
send_by varchar( 120 )    null,           # subm date and name
headln  varchar( 255 )    null,           # paragraph headline
text    blob              not null,       # paragraph text
primary key( proj_id, vers_id, parastr ), # p_id/v_id/pstr = p_key
key pj_ver_idx( proj_id, vers_id )        # index on p_id/v_id>

=cut */

create table paragraph (
	proj_id	smallint unsigned	not null,
	vers_id	smallint unsigned	not null,
	parastr	varchar( 251 )		not null,
	submit	datetime		not null,
	send_by	varchar( 120 )		null,
	headln	varchar( 255 )		null,
	text	blob			not null,
	primary key( proj_id, vers_id, parastr ),
	key pj_ver_idx( proj_id, vers_id )
); /*

=head2 table par_temp

The paragraph temporary table is used to copy versions of texts

table par_temp:
S<
proj_id smallint unsigned not null,       # projekt id
vers_id smallint unsigned not null,       # version number
parastr varchar( 251 )    not null,       # paragraph string
submit  datetime          not null,       # submission date
send_by varchar( 120 )    null,           # subm date and name
headln  varchar( 255 )    null,           # paragraph headline
text    blob              not null        # paragraph text>

=cut */

create table par_temp (
	proj_id	smallint unsigned	not null,
	vers_id	smallint unsigned	not null,
	parastr	varchar( 251 )		not null,
	submit	datetime		not null,
	send_by varchar( 120 )		null,
	headln	varchar( 255 )		null,
	text	blob			not null
); /*

=head2 table par_mail

The paragraph mailing table accumulates comments to send them later

table par_mail:
S<
proj_id smallint unsigned not null,       # projekt id
memb_id smallint unsigned not null,       # member id
parastr varchar( 251 )    not null,       # comment para string
subj    varchar( 255 )    not null,       # subject (=headline)
text    blob              not null,       # mail text (ref par)
ctext   blob              not null        # comment>

=cut */

create table par_mail (
	proj_id	smallint unsigned	not null,	# projekt id
	memb_id	smallint unsigned	not null,	# member id
	parastr	varchar( 251 )		not null,	# comment para string
	subj	varchar( 255 )		not null,	# subject (=headline)
	text	blob			not null,	# mail text (ref par)
	ctext	blob			not null	# comment
); /*

=head2 table projmemb

The intersection projmemb joins between member and metatext: a member can
be a member in one or more projects, and a project has 1 or more members

table projmemb:
S<
proj_id smallint unsigned not null,       # project id
memb_id smallint unsigned not null,       # member id
ismaint tinyint unsigned  not null        # is-maintainer of
                          default 0,      # project flag
primary key( proj_id, memb_id ),          # p_id/m_id = p_key
key proj_idx( proj_id ),                  # index on p_id
key memb_idx( memb_id )                   # index on m_id>

=cut */

create table projmemb (
	proj_id	smallint unsigned	not null,
	memb_id	smallint unsigned	not null,
	ismaint tinyint unsigned	not null default 0,
	primary key( proj_id, memb_id ),
	key proj_idx( proj_id ),
	key memb_idx( memb_id )
); /*

=head2 table subpjs

The table subpjs contains refs of all sub projects from a main project

table subpjs:
S<
proj_id  smallint unsigned not null,      # project id
subpj_id smallint unsigned not null,      # sub project id
primary key( proj_id, subpj_id ),         # p_id/sp_id = p_key
key proj_idx( proj_id ),                  # index on p_id
key subpj_idx( subpj_id )                 # index on sp_id>

=cut */

create table subpjs(
	proj_id	smallint unsigned	not null,
	subpj_id smallint unsigned	not null,
	primary key( proj_id, subpj_id ),
	key proj_idx( proj_id ),
	key subpj_idx( subpj_id )
); /*


=head2 tables receive

The mail interface of open theory is realized using database tables.
Received mails are stored in table receive

table receive:
S<
rec_id  int unsigned      not null        #
                          auto_increment, #
msg_id  varchar( 128 )    not null,       #
proj_addr varchar( 128 )  not null,       #
proj_id smallint          not null        #
                          default 0,      #
addr_to varchar( 255 )    null,           #
addr_cc varchar( 255 )    null,           #
subject varchar( 255 )    null,           #
sender  varchar( 255 )    not null,       #
memb_id smallint unsigned not null,       #
send    varchar( 64 )     not null,       #
body    blob              not null,       #
primary key( rec_id ),                    #
unique key msg_id_proj_addr_idx( msg_id, proj_addr )>

=cut */

create table receive (
	rec_id		int unsigned not null auto_increment,
	msg_id		varchar( 128 ) not null,
	proj_addr	varchar( 128 ) not null,
	proj_id		smallint not null default 0,
	addr_to		varchar( 255 ) null,
	addr_cc		varchar( 255 ) null,
	subject		varchar( 255 ) null,
	sender		varchar( 255 ) not null,
	memb_id		smallint unsigned not null,
	send		varchar( 64 ) not null,
	body		blob not null,
	primary key( rec_id ),
	unique key msg_id_proj_addr_idx( msg_id, proj_addr )
); /*

=head2 table command

Extracted commands from the received mail are stored in table command

table command:
S<
rec_id  int unsigned      not null,       #
cmd_id  int unsigned      not null,       #
cmdnum  int unsigned      not null        #
                          default 1,      #
cmdkey  varchar( 64 )     not null        #
                          default 'error',#
cmdval  blob              null,           #
stcode  int               not null        #
                          default 3,      #
status  varchar( 10 )     not null        #
                          default 'ok',   #
primary key( cmd_id ),                    #
key rec_id_idx( rec_id ),                 #
key status_idx( status )                  #>

=cut */

create table command (
	rec_id		int unsigned not null,
	cmd_id		int unsigned not null,
	cmdnum		int unsigned not null default 1,
	cmdkey		varchar( 64 ) not null default 'error',
	cmdval		blob,
	stcode		int not null default 3,
	status		varchar( 16 ) not null default 'ok',
	primary key( cmd_id ),
	key rec_id_idx( rec_id ),
	key status_idx( status )
); /*

=head2 table session

Table for session management.

table session:
S<
x_id    int unsigned      not null,       # session id
m_id    smallint unsigned null,           # member id
lacc    datetime          null,           # last access time
xfn     varchar( 30 )     null,           # first name
xln     varchar( 50 )     null,           # last name
xem     varchar( 80 )     null,           # email address
xmt     tinyint unsigned  not null        # is-maintainer
                          default 0,      # flag
xdt     datetime          null,           # subscribe date
primary key( x_id )                       # p-key>

=cut */

create table session (
	x_id		int unsigned not null,
	m_id		smallint unsigned null,
	lacc		datetime null,
	xfn		varchar( 30 ) null,
	xln		varchar( 50 ) null,
	xem		varchar( 80 ) null,
	xmt		tinyint unsigned not null default 0,
	xdt		datetime null,
	primary key( x_id )
); /*

=head2 

Tables for all texts used in open theory

In order to ease the online translation of open theory texts, they are
stored in tables.

=head2 table msg_sect

This table defines the message sections used in table msg.

Messages sections are grouped:
S<1-9: mail interface
10-x: web interface>

table msg_sect:
S<
create table msg_sect (
sect_id tinyint unsigned  not null        # section id
                          auto_increment, #
sect    tinyint unsigned  not null,       # message section
lang    char( 2 )         not null,       # language
text    varchar( 64 )     not null,       # section description
primary key( sect_id )                    # sect_id = p_key>

=cut */

create table msg_sect (
	sect_id		tinyint unsigned not null auto_increment,
	sect		tinyint unsigned not null,
	lang		char( 2 ) not null,
	text		varchar( 64 ) not null,
	primary key( sect_id )
); /*

=head2 table msg

All texts and message used in the mail interface are stored in table msg.
These texts are both used by the perl mail interface and the PHP web
interface.

Messages are grouped in sections:
S<1-9: mail interface
10-x: web interface>

table msg:
S<
msg_id  int unsigned      not null        # message id
                          auto_increment, #
sect    tinyint unsigned  not null,       # message section
lang    char( 2 )         not null,       # language
txt     blob              null,           # message text
primary key( msg_id ),                    # msg_id = p_key
key sect_idx( sect ),                     #
unique key sect_lang_idx( sect, lang )    #>

=cut */

create table msg (
	msg_id		int unsigned not null auto_increment,
	sect		tinyint unsigned not null,
	lang		char( 2 ) not null,
	txt		blob null,
	primary key( msg_id ),
	unique key sect_lang_idx( sect, lang )
);
