create table lng_project
( projectnr   integer unsigned not null auto_increment
,    primary key (projectnr)
, description varchar(255)
, summary     text
, url         varchar(255)
, grants      text
, documentroot integer unsigned
);

create table lng_user
( usernr     integer unsigned not null auto_increment
,    primary key (usernr)
, name       varchar(255) not null
, title      varchar(255)
, affiliationcode varchar(5)
, email      varchar(255)
, snailmail  varchar(255)
, fax        varchar(255)
, telephone  varchar(255)
, url        varchar(255)
);

create table lng_document
( documentnr integer unsigned not null auto_increment
,    primary key (documentnr)
, title      varchar(255) not null
,    index i_doc_1(title)
, description text
, creation_date date
, url         varchar(255) not null
,     index i_doc_2(url)
, usernr     integer unsigned
);

create table lng_doc_doc
( docdocnr    integer unsigned not null auto_increment
,     primary key (docdocnr)
, documentnr_1 integer unsigned not null
, documentnr_2 integer unsigned not null
, linkcode varchar(5) not null
, description varchar(50)
);

create table lng_doc_refs
( documentnr integer unsigned not null
, referencenr integer unsigned not null
,     primary key (documentnr, referencenr)
);

create table lng_linkcode
( linkcode   varchar(5) not null
,     primary key (linkcode)
, description varchar(255) not null
);

create table lng_language
( languagenr integer unsigned not null auto_increment
,    primary key (languagenr)
, language   varchar(255) not null
, parent_languagenr integer unsigned
, description text
, documentroot integer unsigned
);

create table lng_reference
( referencenr integer unsigned not null auto_increment
,    primary key (referencenr)
, author      varchar(255) not null
,    index i_refs_1 (author)
, year        varchar(4) not null
,    index i_refs_2 (year)
, title       varchar(255) not null
,    index i_refs_3 (title)
, place       varchar(255) not null
,    index i_refs_4 (place)
, catalogue_card text
, note           text
, main_categorycode varchar(10) not null
,    index i_refs_5 (main_categorycode)
, sub_categorycode  varchar(10) not null
,    index i_refs_6 (sub_categorycode)
);

create table lng_proj_user
( prusnr    integer unsigned not null auto_increment
, projectnr integer unsigned not null
, usernr    integer unsigned not null
,    primary key (prusnr)
);

create table  lng_proj_lngg
( prlnnr      integer unsigned not null auto_increment
, projectnr   integer unsigned not null
, languagenr  integer unsigned not null
,    primary key (prlnnr)
);

create table lng_recording
( recordingnr integer unsigned not null auto_increment
,    primary key (recordingnr)
, description text
, url         varchar(255) not null
, source      varchar(255)
, tapenr      varchar(255)
, tape_location varchar(255)
, informant   varchar(255)
, duration    integer unsigned
, title       varchar(255) not null
, usernr      integer unsigned
, recording_date date 
, languagenr  integer unsigned not null
, projectnr  integer unsigned not null
);

create table lng_scan
( scannr     integer unsigned not null auto_increment
,    primary key (scannr)
, description text
, url        varchar(255)
, manuscript_location varchar(255)
, title      varchar(255)  not null
, page       varchar(255)
, usernr     integer unsigned
, scan_date  date
, size       varchar(255)
, languagenr  integer unsigned not null
, projectnr integer unsigned  not null
);

create table lng_text
( textnr     integer unsigned  not null auto_increment
,    primary key (textnr)
, recordingnr integer unsigned
, scannr     integer unsigned
, description text
, url        varchar(255)
, title      varchar(255)   not null
, usernr     integer unsigned
, transcription_date date
, raw_text   text           not null
, languagenr integer unsigned  not null
);

create table lng_proj_text
( prtxnr     integer unsigned  not null auto_increment
, projectnr  integer unsigned  not null
, textnr     integer unsigned  not null
,    primary key (prtxnr)
);

create table lng_stream
( streamnr   integer unsigned  not null auto_increment
,    primary key (streamnr)
, textnr     integer unsigned  not null
, seqnr      int   not null  
,    unique u_strm (textnr, seqnr)
, text       text           not null
, languagenr integer unsigned
, usernr     integer unsigned
, datestamp  timestamp
);

create table lng_element
( elementnr  integer unsigned not null auto_increment
,    primary key (elementnr)
, streamnr   integer unsigned not null
, seqnr      int  not null
,    unique u_elmt (streamnr, seqnr)
, parent_elementnr integer unsigned
, text       varchar(255) not null
,    index i_elmt_1 (text)
, lexnr      integer unsigned
, languagenr integer unsigned
, usernr     integer unsigned
, datestamp  timestamp
);

create table lng_lex
( lexnr      integer unsigned not null auto_increment
,    primary key (lexnr)
, form       varchar(50) not null
,    index i_lex (form)
, phonetic_form varchar(100)
, glosse      varchar(255) not null
, description text
, languagenr  integer unsigned not null
, usernr      integer unsigned
, datestamp   timestamp
);

create table lng_lex_lex
( lxlxnr      integer unsigned not null auto_increment
,    primary  key (lxlxnr)
, lexnr_1     integer unsigned not null
, lexnr_2     integer unsigned not null
,   unique u_lxlx(lexnr_1, lexnr_2)
, lxlxrelcode varchar(255)
, note        text
, usernr      integer unsigned
, datestamp   timestamp
);

create table lng_lxlxrelcode
( lxlxrelcode varchar(10) not null
,   primary key(lxlxrelcode)
, description varchar(255)
);

create table lng_text_tag
( text_tagnr integer unsigned  not null 
,   primary key (text_tagnr)
, textnr     integer unsigned not null
,   index i_txtg_1(textnr)
, tag        varchar(50)  not null
,   index i_txtg_2(tag)
, value      varchar(200) not null
, description varchar(200)
, note       text
, usernr     integer unsigned
, datestamp  timestamp
);

create table lng_stream_tag
( stream_tagnr integer unsigned not null
,   primary key (stream_tagnr)
, streamnr   integer unsigned not null
,   index i_sttg_1(streamnr)
, tag        varchar(50)  not null
,   index i_sttg_2(tag)
, value      varchar(200)
, description varchar(200)
, note       text
, usernr     integer unsigned
, datestamp  timestamp
);

create table lng_element_tag
( element_tagnr integer unsigned not null
,   primary key (element_tagnr)
, elementnr  integer unsigned not null
,   index i_elmt_1 (elementnr)
, tag        varchar(50)  not null
,   index i_elmt_2 (tag)
, value      varchar(200)
, description varchar(200)
, note       text
, usernr     integer unsigned
, datestamp  timestamp
);

create table lng_lex_tag
( lex_tagnr  integer unsigned not null
,   primary key (lex_tagnr)
, lexnr      integer unsigned not null
,   index i_lxtg_1(lexnr)
, tag        varchar(50)  not null
,   index i_lxtg_2(tag)
, value      varchar(200)
, description varchar(200)
, note       text
, usernr     integer unsigned
, datestamp  timestamp
);

create table lng_tag
( tag         varchar(50) not null
,    primary key (tag)
, name        varchar(255) not null
,    unique (name)
, description text
, tagtypecode varchar(5) not null
, text        numeric(1,0) not null default 1
, stream      numeric(1,0) not null default 1
, element     numeric(1,0) not null default 1
, lexeme      numeric(1,0) not null default 1
, stylecode   varchar(50)
);

create table lng_stylecode
( stylecode   varchar(50) not null
,    primary key (stylecode)
, font        varchar(50) not null default "helvetica"
, bold        numeric(1,0) not null default 0
, italic      numeric(1,0) not null default 0
, size        numeric(2,0) not null default 12
, showifnull  numeric(1,0) not null default 1
, wordwrap    numeric(1,0) not null default 1
);

create table lng_tagtypecode
( tagtypecode varchar(5) not null
,    primary key (tagtypecode)
, description varchar(255) not null
, isdomain    numeric(1,0) not null default 0
, isvalue     numeric(1,0) not null default 1
, isnote      numeric(1,0) not null default 0
, isreference numeric(1,0) not null default 0
);

create table lng_tagdomain
( domainnr integer unsigned not null auto_increment
,    primary key (domainnr) 
, abbreviation varchar(10) not null
, tag          varchar(50) not null
,    unique (abbreviation, tag)
, description  varchar(50) 
);

create table lng_affiliationcode
( affiliationcode  varchar(5) not null
,    primary key (affiliationcode)
, description      varchar(255) not null
, institution      varchar(255)
, url              varchar(255)
);

create table lng_categorycode
( categorycode varchar(10) not null
,    primary key (categorycode)
, description varchar(255) not null
);

create table lng_parametersys
(   parametersys   varchar(10)           not null
,   value          varchar(255)          not null
,     primary key (parametersys)
);