From 8def1d132eac5937fa9a6408849cff13c80f3000 Mon Sep 17 00:00:00 2001 From: tonnesen Date: Thu, 16 May 2002 20:27:12 +0000 Subject: [PATCH] Added marc_NXX_subfield_table tables --- marc/marcschema.sql | 208 ++++++++++++++++++++++++++++++++++---------- 1 file changed, 162 insertions(+), 46 deletions(-) diff --git a/marc/marcschema.sql b/marc/marcschema.sql index 7b64ecdbe2..31013a1547 100644 --- a/marc/marcschema.sql +++ b/marc/marcschema.sql @@ -1,107 +1,223 @@ # marc_biblio contains 1 record for each biblio in the DB CREATE TABLE marc_biblio ( - bibcode bigint(20) unsigned NOT NULL auto_increment, + bibid bigint(20) unsigned NOT NULL auto_increment, datecreated date NOT NULL default '0000-00-00', datemodified date default NULL, origincode char(20) default NULL, - PRIMARY KEY (bibcode), + PRIMARY KEY (bibid), KEY origincode (origincode) ) TYPE=MyISAM; -# marc NXX_table contains 1 record for each tag of every biblio -# if the tag is more than 255 length, the value is in the marc_blob_tag table, -# the valuebloblink contains the number of the blob +# marc_NXX_tag_table contains 1 record for each tag of every biblio + CREATE TABLE marc_0XX_tag_table ( - bibcode bigint(20) NOT NULL default '0', + tagid bigint(20) unsigned NOT NULL auto_increment, + bibid bigint(20) NOT NULL default '0', tagnumber char(3) NOT NULL default '', tagorder tinyint(4) NOT NULL default '0', indicator char(2) NOT NULL default '', - tagvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (bibcode,tagnumber,tagorder) + PRIMARY KEY (tagid), + KEY (bibid,tagnumber,tagorder) ) TYPE=MyISAM; CREATE TABLE marc_1XX_tag_table ( - bibcode bigint(20) NOT NULL default '0', + tagid bigint(20) unsigned NOT NULL auto_increment, + bibid bigint(20) NOT NULL default '0', tagnumber char(3) NOT NULL default '', tagorder tinyint(4) NOT NULL default '0', indicator char(2) NOT NULL default '', - tagvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (bibcode,tagnumber,tagorder) + PRIMARY KEY (tagid), + KEY (bibid,tagnumber,tagorder) ) TYPE=MyISAM; CREATE TABLE marc_2XX_tag_table ( - bibcode bigint(20) NOT NULL default '0', + tagid bigint(20) unsigned NOT NULL auto_increment, + bibid bigint(20) NOT NULL default '0', tagnumber char(3) NOT NULL default '', tagorder tinyint(4) NOT NULL default '0', indicator char(2) NOT NULL default '', - tagvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (bibcode,tagnumber,tagorder) + PRIMARY KEY (tagid), + KEY (bibid,tagnumber,tagorder) ) TYPE=MyISAM; CREATE TABLE marc_3XX_tag_table ( - bibcode bigint(20) NOT NULL default '0', + tagid bigint(20) unsigned NOT NULL auto_increment, + bibid bigint(20) NOT NULL default '0', tagnumber char(3) NOT NULL default '', tagorder tinyint(4) NOT NULL default '0', indicator char(2) NOT NULL default '', - tagvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (bibcode,tagnumber,tagorder) + PRIMARY KEY (tagid), + KEY (bibid,tagnumber,tagorder) ) TYPE=MyISAM; CREATE TABLE marc_4XX_tag_table ( - bibcode bigint(20) NOT NULL default '0', + tagid bigint(20) unsigned NOT NULL auto_increment, + bibid bigint(20) NOT NULL default '0', tagnumber char(3) NOT NULL default '', tagorder tinyint(4) NOT NULL default '0', indicator char(2) NOT NULL default '', - tagvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (bibcode,tagnumber,tagorder) + PRIMARY KEY (tagid), + KEY (bibid,tagnumber,tagorder) ) TYPE=MyISAM; CREATE TABLE marc_5XX_tag_table ( - bibcode bigint(20) NOT NULL default '0', + tagid bigint(20) unsigned NOT NULL auto_increment, + bibid bigint(20) NOT NULL default '0', tagnumber char(3) NOT NULL default '', tagorder tinyint(4) NOT NULL default '0', indicator char(2) NOT NULL default '', - tagvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (bibcode,tagnumber,tagorder) + PRIMARY KEY (tagid), + KEY (bibid,tagnumber,tagorder) ) TYPE=MyISAM; CREATE TABLE marc_6XX_tag_table ( - bibcode bigint(20) NOT NULL default '0', + tagid bigint(20) unsigned NOT NULL auto_increment, + bibid bigint(20) NOT NULL default '0', tagnumber char(3) NOT NULL default '', tagorder tinyint(4) NOT NULL default '0', indicator char(2) NOT NULL default '', - tagvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (bibcode,tagnumber,tagorder) + PRIMARY KEY (tagid), + KEY (bibid,tagnumber,tagorder) ) TYPE=MyISAM; CREATE TABLE marc_7XX_tag_table ( - bibcode bigint(20) NOT NULL default '0', + tagid bigint(20) unsigned NOT NULL auto_increment, + bibid bigint(20) NOT NULL default '0', tagnumber char(3) NOT NULL default '', tagorder tinyint(4) NOT NULL default '0', indicator char(2) NOT NULL default '', - tagvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (bibcode,tagnumber,tagorder) + PRIMARY KEY (tagid), + KEY (bibid,tagnumber,tagorder) ) TYPE=MyISAM; CREATE TABLE marc_8XX_tag_table ( - bibcode bigint(20) NOT NULL default '0', + tagid bigint(20) unsigned NOT NULL auto_increment, + bibid bigint(20) NOT NULL default '0', tagnumber char(3) NOT NULL default '', tagorder tinyint(4) NOT NULL default '0', indicator char(2) NOT NULL default '', - tagvalue varchar(255) default NULL, - valuebloblink bigint(20) default NULL, - PRIMARY KEY (bibcode,tagnumber,tagorder) + PRIMARY KEY (tagid), + KEY (bibid,tagnumber,tagorder) ) TYPE=MyISAM; CREATE TABLE marc_9XX_tag_table ( - bibcode bigint(20) NOT NULL default '0', + tagid bigint(20) unsigned NOT NULL auto_increment, + bibid bigint(20) NOT NULL default '0', tagnumber char(3) NOT NULL default '', tagorder tinyint(4) NOT NULL default '0', indicator char(2) NOT NULL default '', - tagvalue varchar(255) default NULL, + PRIMARY KEY (tagid), + KEY (bibid,tagnumber,tagorder) + ) TYPE=MyISAM; + + +# marc_NXX_subfield_table contains 1 record for each subfield of every tag if +# the subfield value is more than 255 length, the value is in the marc_blob_tag +# table, the valuebloblink contains the number of the blob + + CREATE TABLE marc_0XX_subfield_table ( + subfieldid bigint(20) unsigned NOT NULL auto_increment, + tagid bigint(20) NOT NULL default '0', + bibid bigint(20) NOT NULL default '0', + subfieldorder tinyint(4) NOT NULL default '0', + subfieldcode char(1) NOT NULL default '', + subfieldvalue varchar(255) default NULL, + valuebloblink bigint(20) default NULL, + PRIMARY KEY (subfieldcode), + KEY (bibid,tagid,subfieldcode) + ) TYPE=MyISAM; + CREATE TABLE marc_1XX_subfield_table ( + subfieldid bigint(20) unsigned NOT NULL auto_increment, + tagid bigint(20) NOT NULL default '0', + bibid bigint(20) NOT NULL default '0', + subfieldorder tinyint(4) NOT NULL default '0', + subfieldcode char(1) NOT NULL default '', + subfieldvalue varchar(255) default NULL, valuebloblink bigint(20) default NULL, - PRIMARY KEY (bibcode,tagnumber,tagorder) + PRIMARY KEY (subfieldcode), + KEY (bibid,tagid,subfieldcode) ) TYPE=MyISAM; + CREATE TABLE marc_2XX_subfield_table ( + subfieldid bigint(20) unsigned NOT NULL auto_increment, + tagid bigint(20) NOT NULL default '0', + bibid bigint(20) NOT NULL default '0', + subfieldorder tinyint(4) NOT NULL default '0', + subfieldcode char(1) NOT NULL default '', + subfieldvalue varchar(255) default NULL, + valuebloblink bigint(20) default NULL, + PRIMARY KEY (subfieldcode), + KEY (bibid,tagid,subfieldcode) + ) TYPE=MyISAM; + CREATE TABLE marc_3XX_subfield_table ( + subfieldid bigint(20) unsigned NOT NULL auto_increment, + tagid bigint(20) NOT NULL default '0', + bibid bigint(20) NOT NULL default '0', + subfieldorder tinyint(4) NOT NULL default '0', + subfieldcode char(1) NOT NULL default '', + subfieldvalue varchar(255) default NULL, + valuebloblink bigint(20) default NULL, + PRIMARY KEY (subfieldcode), + KEY (bibid,tagid,subfieldcode) + ) TYPE=MyISAM; + CREATE TABLE marc_4XX_subfield_table ( + subfieldid bigint(20) unsigned NOT NULL auto_increment, + tagid bigint(20) NOT NULL default '0', + bibid bigint(20) NOT NULL default '0', + subfieldorder tinyint(4) NOT NULL default '0', + subfieldcode char(1) NOT NULL default '', + subfieldvalue varchar(255) default NULL, + valuebloblink bigint(20) default NULL, + PRIMARY KEY (subfieldcode), + KEY (bibid,tagid,subfieldcode) + ) TYPE=MyISAM; + CREATE TABLE marc_5XX_subfield_table ( + subfieldid bigint(20) unsigned NOT NULL auto_increment, + tagid bigint(20) NOT NULL default '0', + bibid bigint(20) NOT NULL default '0', + subfieldorder tinyint(4) NOT NULL default '0', + subfieldcode char(1) NOT NULL default '', + subfieldvalue varchar(255) default NULL, + valuebloblink bigint(20) default NULL, + PRIMARY KEY (subfieldcode), + KEY (bibid,tagid,subfieldcode) + ) TYPE=MyISAM; + CREATE TABLE marc_6XX_subfield_table ( + subfieldid bigint(20) unsigned NOT NULL auto_increment, + tagid bigint(20) NOT NULL default '0', + bibid bigint(20) NOT NULL default '0', + subfieldorder tinyint(4) NOT NULL default '0', + subfieldcode char(1) NOT NULL default '', + subfieldvalue varchar(255) default NULL, + valuebloblink bigint(20) default NULL, + PRIMARY KEY (subfieldcode), + KEY (bibid,tagid,subfieldcode) + ) TYPE=MyISAM; + CREATE TABLE marc_7XX_subfield_table ( + subfieldid bigint(20) unsigned NOT NULL auto_increment, + tagid bigint(20) NOT NULL default '0', + bibid bigint(20) NOT NULL default '0', + subfieldorder tinyint(4) NOT NULL default '0', + subfieldcode char(1) NOT NULL default '', + subfieldvalue varchar(255) default NULL, + valuebloblink bigint(20) default NULL, + PRIMARY KEY (subfieldcode), + KEY (bibid,tagid,subfieldcode) + ) TYPE=MyISAM; + CREATE TABLE marc_8XX_subfield_table ( + subfieldid bigint(20) unsigned NOT NULL auto_increment, + tagid bigint(20) NOT NULL default '0', + bibid bigint(20) NOT NULL default '0', + subfieldorder tinyint(4) NOT NULL default '0', + subfieldcode char(1) NOT NULL default '', + subfieldvalue varchar(255) default NULL, + valuebloblink bigint(20) default NULL, + PRIMARY KEY (subfieldcode), + KEY (bibid,tagid,subfieldcode) + ) TYPE=MyISAM; + CREATE TABLE marc_9XX_subfield_table ( + subfieldid bigint(20) unsigned NOT NULL auto_increment, + tagid bigint(20) NOT NULL default '0', + bibid bigint(20) NOT NULL default '0', + subfieldorder tinyint(4) NOT NULL default '0', + subfieldcode char(1) NOT NULL default '', + subfieldvalue varchar(255) default NULL, + valuebloblink bigint(20) default NULL, + PRIMARY KEY (subfieldcode), + KEY (bibid,tagid,subfieldcode) + ) TYPE=MyISAM; + # marc_blob_tag containts tag longer than 255 car. # They are linked to a marc_NXX_tag_table record by bloblink @@ -137,12 +253,12 @@ # marc_tag_word contains 1 record for each word in each subfield in each tag in each biblio CREATE TABLE marc_tag_word ( - bibcode bigint(20) NOT NULL default '0', + bibid bigint(20) NOT NULL default '0', tagnumber char(3) NOT NULL default '', subfieldid char(1) NOT NULL default '', word varchar(255) NOT NULL default '', sndx_word varchar(255) NOT NULL default '', # the soundex version of the word (indexed) - PRIMARY KEY (bibcode,tagnumber,subfieldid), + PRIMARY KEY (bibid,tagnumber,subfieldid), KEY word (word), KEY sndx_word (sndx_word) ) TYPE=MyISAM; -- 2.39.2