1 # marc_biblio contains 1 record for each biblio in the DB
2 CREATE TABLE marc_biblio (
3 bibid bigint(20) unsigned NOT NULL auto_increment,
4 datecreated date NOT NULL default '0000-00-00',
5 datemodified date default NULL,
6 origincode char(20) default NULL,
8 KEY origincode (origincode)
12 # marc_NXX_tag_table contains 1 record for each tag of every biblio
14 CREATE TABLE marc_0XX_tag_table (
15 tagid bigint(20) unsigned NOT NULL auto_increment,
16 bibid bigint(20) NOT NULL default '0',
17 tagnumber char(3) NOT NULL default '',
18 tagorder tinyint(4) NOT NULL default '0',
19 indicator char(2) NOT NULL default '',
21 KEY (bibid,tagnumber,tagorder)
23 CREATE TABLE marc_1XX_tag_table (
24 tagid bigint(20) unsigned NOT NULL auto_increment,
25 bibid bigint(20) NOT NULL default '0',
26 tagnumber char(3) NOT NULL default '',
27 tagorder tinyint(4) NOT NULL default '0',
28 indicator char(2) NOT NULL default '',
30 KEY (bibid,tagnumber,tagorder)
32 CREATE TABLE marc_2XX_tag_table (
33 tagid bigint(20) unsigned NOT NULL auto_increment,
34 bibid bigint(20) NOT NULL default '0',
35 tagnumber char(3) NOT NULL default '',
36 tagorder tinyint(4) NOT NULL default '0',
37 indicator char(2) NOT NULL default '',
39 KEY (bibid,tagnumber,tagorder)
41 CREATE TABLE marc_3XX_tag_table (
42 tagid bigint(20) unsigned NOT NULL auto_increment,
43 bibid bigint(20) NOT NULL default '0',
44 tagnumber char(3) NOT NULL default '',
45 tagorder tinyint(4) NOT NULL default '0',
46 indicator char(2) NOT NULL default '',
48 KEY (bibid,tagnumber,tagorder)
50 CREATE TABLE marc_4XX_tag_table (
51 tagid bigint(20) unsigned NOT NULL auto_increment,
52 bibid bigint(20) NOT NULL default '0',
53 tagnumber char(3) NOT NULL default '',
54 tagorder tinyint(4) NOT NULL default '0',
55 indicator char(2) NOT NULL default '',
57 KEY (bibid,tagnumber,tagorder)
59 CREATE TABLE marc_5XX_tag_table (
60 tagid bigint(20) unsigned NOT NULL auto_increment,
61 bibid bigint(20) NOT NULL default '0',
62 tagnumber char(3) NOT NULL default '',
63 tagorder tinyint(4) NOT NULL default '0',
64 indicator char(2) NOT NULL default '',
66 KEY (bibid,tagnumber,tagorder)
68 CREATE TABLE marc_6XX_tag_table (
69 tagid bigint(20) unsigned NOT NULL auto_increment,
70 bibid bigint(20) NOT NULL default '0',
71 tagnumber char(3) NOT NULL default '',
72 tagorder tinyint(4) NOT NULL default '0',
73 indicator char(2) NOT NULL default '',
75 KEY (bibid,tagnumber,tagorder)
77 CREATE TABLE marc_7XX_tag_table (
78 tagid bigint(20) unsigned NOT NULL auto_increment,
79 bibid bigint(20) NOT NULL default '0',
80 tagnumber char(3) NOT NULL default '',
81 tagorder tinyint(4) NOT NULL default '0',
82 indicator char(2) NOT NULL default '',
84 KEY (bibid,tagnumber,tagorder)
86 CREATE TABLE marc_8XX_tag_table (
87 tagid bigint(20) unsigned NOT NULL auto_increment,
88 bibid bigint(20) NOT NULL default '0',
89 tagnumber char(3) NOT NULL default '',
90 tagorder tinyint(4) NOT NULL default '0',
91 indicator char(2) NOT NULL default '',
93 KEY (bibid,tagnumber,tagorder)
95 CREATE TABLE marc_9XX_tag_table (
96 tagid bigint(20) unsigned NOT NULL auto_increment,
97 bibid bigint(20) NOT NULL default '0',
98 tagnumber char(3) NOT NULL default '',
99 tagorder tinyint(4) NOT NULL default '0',
100 indicator char(2) NOT NULL default '',
102 KEY (bibid,tagnumber,tagorder)
106 # marc_NXX_subfield_table contains 1 record for each subfield of every tag if
107 # the subfield value is more than 255 length, the value is in the marc_blob_tag
108 # table, the valuebloblink contains the number of the blob
110 CREATE TABLE marc_0XX_subfield_table (
111 subfieldid bigint(20) unsigned NOT NULL auto_increment,
112 tagid bigint(20) NOT NULL default '0',
113 bibid bigint(20) NOT NULL default '0',
114 subfieldorder tinyint(4) NOT NULL default '0',
115 subfieldcode char(1) NOT NULL default '',
116 subfieldvalue varchar(255) default NULL,
117 valuebloblink bigint(20) default NULL,
118 PRIMARY KEY (subfieldid),
119 KEY (bibid,tagid,subfieldcode)
121 CREATE TABLE marc_1XX_subfield_table (
122 subfieldid bigint(20) unsigned NOT NULL auto_increment,
123 tagid bigint(20) NOT NULL default '0',
124 bibid bigint(20) NOT NULL default '0',
125 subfieldorder tinyint(4) NOT NULL default '0',
126 subfieldcode char(1) NOT NULL default '',
127 subfieldvalue varchar(255) default NULL,
128 valuebloblink bigint(20) default NULL,
129 PRIMARY KEY (subfieldid),
130 KEY (bibid,tagid,subfieldcode)
132 CREATE TABLE marc_2XX_subfield_table (
133 subfieldid bigint(20) unsigned NOT NULL auto_increment,
134 tagid bigint(20) NOT NULL default '0',
135 bibid bigint(20) NOT NULL default '0',
136 subfieldorder tinyint(4) NOT NULL default '0',
137 subfieldcode char(1) NOT NULL default '',
138 subfieldvalue varchar(255) default NULL,
139 valuebloblink bigint(20) default NULL,
140 PRIMARY KEY (subfieldid),
141 KEY (bibid,tagid,subfieldcode)
143 CREATE TABLE marc_3XX_subfield_table (
144 subfieldid bigint(20) unsigned NOT NULL auto_increment,
145 tagid bigint(20) NOT NULL default '0',
146 bibid bigint(20) NOT NULL default '0',
147 subfieldorder tinyint(4) NOT NULL default '0',
148 subfieldcode char(1) NOT NULL default '',
149 subfieldvalue varchar(255) default NULL,
150 valuebloblink bigint(20) default NULL,
151 PRIMARY KEY (subfieldid),
152 KEY (bibid,tagid,subfieldcode)
154 CREATE TABLE marc_4XX_subfield_table (
155 subfieldid bigint(20) unsigned NOT NULL auto_increment,
156 tagid bigint(20) NOT NULL default '0',
157 bibid bigint(20) NOT NULL default '0',
158 subfieldorder tinyint(4) NOT NULL default '0',
159 subfieldcode char(1) NOT NULL default '',
160 subfieldvalue varchar(255) default NULL,
161 valuebloblink bigint(20) default NULL,
162 PRIMARY KEY (subfieldid),
163 KEY (bibid,tagid,subfieldcode)
165 CREATE TABLE marc_5XX_subfield_table (
166 subfieldid bigint(20) unsigned NOT NULL auto_increment,
167 tagid bigint(20) NOT NULL default '0',
168 bibid bigint(20) NOT NULL default '0',
169 subfieldorder tinyint(4) NOT NULL default '0',
170 subfieldcode char(1) NOT NULL default '',
171 subfieldvalue varchar(255) default NULL,
172 valuebloblink bigint(20) default NULL,
173 PRIMARY KEY (subfieldid),
174 KEY (bibid,tagid,subfieldcode)
176 CREATE TABLE marc_6XX_subfield_table (
177 subfieldid bigint(20) unsigned NOT NULL auto_increment,
178 tagid bigint(20) NOT NULL default '0',
179 bibid bigint(20) NOT NULL default '0',
180 subfieldorder tinyint(4) NOT NULL default '0',
181 subfieldcode char(1) NOT NULL default '',
182 subfieldvalue varchar(255) default NULL,
183 valuebloblink bigint(20) default NULL,
184 PRIMARY KEY (subfieldid),
185 KEY (bibid,tagid,subfieldcode)
187 CREATE TABLE marc_7XX_subfield_table (
188 subfieldid bigint(20) unsigned NOT NULL auto_increment,
189 tagid bigint(20) NOT NULL default '0',
190 bibid bigint(20) NOT NULL default '0',
191 subfieldorder tinyint(4) NOT NULL default '0',
192 subfieldcode char(1) NOT NULL default '',
193 subfieldvalue varchar(255) default NULL,
194 valuebloblink bigint(20) default NULL,
195 PRIMARY KEY (subfieldid),
196 KEY (bibid,tagid,subfieldcode)
198 CREATE TABLE marc_8XX_subfield_table (
199 subfieldid bigint(20) unsigned NOT NULL auto_increment,
200 tagid bigint(20) NOT NULL default '0',
201 bibid bigint(20) NOT NULL default '0',
202 subfieldorder tinyint(4) NOT NULL default '0',
203 subfieldcode char(1) NOT NULL default '',
204 subfieldvalue varchar(255) default NULL,
205 valuebloblink bigint(20) default NULL,
206 PRIMARY KEY (subfieldid),
207 KEY (bibid,tagid,subfieldcode)
209 CREATE TABLE marc_9XX_subfield_table (
210 subfieldid bigint(20) unsigned NOT NULL auto_increment,
211 tagid bigint(20) NOT NULL default '0',
212 bibid bigint(20) NOT NULL default '0',
213 subfieldorder tinyint(4) NOT NULL default '0',
214 subfieldcode char(1) NOT NULL default '',
215 subfieldvalue varchar(255) default NULL,
216 valuebloblink bigint(20) default NULL,
217 PRIMARY KEY (subfieldid),
218 KEY (bibid,tagid,subfieldcode)
222 # marc_blob_tag containts tag longer than 255 car.
223 # They are linked to a marc_NXX_tag_table record by bloblink
224 CREATE TABLE marc_blob_tag (
225 blobidlink bigint(20) NOT NULL auto_increment,
226 tagvalue longtext NOT NULL,
227 PRIMARY KEY (blobidlink)
230 # marc_subfield_structure contains the definition of the marc
231 # subfields. Any MARC is supposed to be support-able
232 CREATE TABLE marc_subfield_structure (
233 tagfield char(3) NOT NULL default '',
234 tagsubfield char(1) NOT NULL default '',
235 liblibrarian char(255) NOT NULL default '', # the text shown to a librarian
236 libopac char(255) NOT NULL default '', # the text shown to an opac user
237 repeatable tinyint(4) NOT NULL default '0', # is the field repeatable 0/1 ?
238 mandatory tinyint(4) NOT NULL default '0', # is the subfield mandatory in manual add 0/1 ?
239 kohafield char(40) NOT NULL default '', # the name of the normal-koha- DB field
240 PRIMARY KEY (tagfield,tagsubfield)
243 # marc_tag_structure contains the definition of the marc tags.
244 # any MARC is supposed to be support-able
245 CREATE TABLE marc_tag_structure (
246 tagfield char(3) NOT NULL default '',
247 liblibrarian char(255) NOT NULL default '',
248 libopac char(255) NOT NULL default '',
249 repeatable tinyint(4) NOT NULL default '0',
250 mandatory tinyint(4) NOT NULL default '0',
251 PRIMARY KEY (tagfield)
254 # marc_tag_word contains 1 record for each word in each subfield in each tag in each biblio
255 CREATE TABLE marc_tag_word (
256 bibid bigint(20) NOT NULL default '0',
257 tagnumber char(3) NOT NULL default '',
258 subfieldid char(1) NOT NULL default '',
259 word varchar(255) NOT NULL default '',
260 sndx_word varchar(255) NOT NULL default '', # the soundex version of the word (indexed)
261 PRIMARY KEY (bibid,tagnumber,subfieldid),
263 KEY sndx_word (sndx_word)