1 /*++++++++++++++++++++
2 dbfncs.c: refdb database-specific support functions
3 markus@mhoenicka.de 2002-08-06
4
5 This program is free software; you can redistribute it and/or modify
6 it under the terms of the GNU General Public License as published by
7 the Free Software Foundation; either version 2 of the License, or
8 (at your option) any later version.
9
10 This program is distributed in the hope that it will be useful,
11 but WITHOUT ANY WARRANTY; without even the implied warranty of
12 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 GNU General Public License for more details.
14
15 You should have received a copy of the GNU General Public License
16 along with this program; if not, write to the Free Software
17 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18
19 +++++++++++++++++++++++++*/
20
21 #include <dbi/dbi.h>
22 #include <syslog.h>
23 #include <string.h>
24 #include <stdio.h>
25
26 #include "linklist.h"
27 #include "refdb.h"
28 #include "refdbd.h"
29 #include "strfncs.h"
30 #include "dbfncs.h"
31 #include "connect.h"
32
33 /* global variables */
34 extern int n_log_level;
35 extern char main_db[];
36 extern struct db_caps* ptr_dbcaps;
37
38 /* forward declarations of static functions */
39 static int my_dbi_conn_transaction(dbi_conn conn, int trans_type);
40 static int set_cap_versioninfo(dbi_conn conn, const char* drivername);
41
42 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
43 new_db_caps(): creates and initializes a new db_caps struct
44
45 struct db_caps* new_db_caps returns a ptr to the struct if successful
46 NULL if out of memory
47
48 struct CLIENT_REQUEST* ptr_clrequest ptr to structure with client info
49
50 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
new_db_caps(struct CLIENT_REQUEST * ptr_clrequest)51 struct db_caps* new_db_caps(struct CLIENT_REQUEST* ptr_clrequest) {
52 struct db_caps* ptr_caps;
53
54 if ((ptr_caps = malloc(sizeof(struct db_caps))) == NULL) {
55 return NULL;
56 }
57
58 strcpy(ptr_caps->defval, "f");
59
60 if (!strcmp(ptr_clrequest->dbserver, "mysql")) {
61 /* these settings should work for 3.23.19 and later. We'll check the
62 database engine version at runtime and fill in more appropriate
63 values if we're using a newer version. Some settings (like
64 transactions) are not supported by older versions, but do not cause
65 an error */
66 strcpy(ptr_caps->multiple_db, "t");
67 strcpy(ptr_caps->sql_enum, "t");
68 strcpy(ptr_caps->rlike, "RLIKE BINARY");
69 strcpy(ptr_caps->not_rlike, "NOT RLIKE BINARY");
70 strcpy(ptr_caps->transaction, "t");
71 strcpy(ptr_caps->localhost, "localhost");
72 strcpy(ptr_caps->encoding, "");
73 strcpy(ptr_caps->groups, "f");
74 strcpy(ptr_caps->admin_systable, "mysql.user");
75 strcpy(ptr_caps->listall, ".*");
76 strcpy(ptr_caps->bigint, "t");
77 strcpy(ptr_caps->sql_union, "f");
78 strcpy(ptr_caps->named_seq, "f");
79 strcpy(ptr_caps->charlength, "CHAR_LENGTH");
80 strcpy(ptr_caps->substring, "SUBSTRING");
81 strcpy(ptr_caps->substring_from, " FROM ");
82 strcpy(ptr_caps->substring_for, " FOR ");
83 strcpy(ptr_caps->sql_except, "f");
84 strcpy(ptr_caps->unix_regexp, "t");
85 ptr_caps->has_versioninfo = 0;
86 }
87 else if (!strcmp(ptr_clrequest->dbserver, "pgsql")) {
88 strcpy(ptr_caps->multiple_db, "f");
89 strcpy(ptr_caps->sql_enum, "f");
90 strcpy(ptr_caps->rlike, "~");
91 strcpy(ptr_caps->not_rlike, "!~");
92 strcpy(ptr_caps->transaction, "t");
93 strcpy(ptr_caps->localhost, "");
94 strcpy(ptr_caps->encoding, "WITH ENCODING =");
95 strcpy(ptr_caps->groups, "t");
96 strcpy(ptr_caps->admin_systable, "pg_shadow");
97 strcpy(ptr_caps->listall, ".*");
98 strcpy(ptr_caps->bigint, "t");
99 strcpy(ptr_caps->sql_union, "t");
100 strcpy(ptr_caps->named_seq, "t");
101 strcpy(ptr_caps->charlength, "CHAR_LENGTH");
102 strcpy(ptr_caps->substring, "SUBSTRING");
103 strcpy(ptr_caps->substring_from, " FROM ");
104 strcpy(ptr_caps->substring_for, " FOR ");
105 strcpy(ptr_caps->sql_except, "t");
106 strcpy(ptr_caps->unix_regexp, "t");
107 ptr_caps->has_versioninfo = 1;
108 }
109 else if (!strcmp(ptr_clrequest->dbserver, "sqlite")) {
110 strcpy(ptr_caps->multiple_db, "f");
111 strcpy(ptr_caps->sql_enum, "f");
112 strcpy(ptr_caps->rlike, "LIKE");
113 strcpy(ptr_caps->not_rlike, "NOT LIKE");
114 strcpy(ptr_caps->transaction, "t");
115 strcpy(ptr_caps->localhost, "NA");
116 strcpy(ptr_caps->encoding, "");
117 strcpy(ptr_caps->groups, "NA");
118 strcpy(ptr_caps->admin_systable, "NA");
119 strcpy(ptr_caps->listall, "%");
120 strcpy(ptr_caps->bigint, "f");
121 strcpy(ptr_caps->sql_union, "t");
122 strcpy(ptr_caps->named_seq, "f");
123 strcpy(ptr_caps->charlength, "LENGTH");
124 strcpy(ptr_caps->substring, "SUBSTR");
125 strcpy(ptr_caps->substring_from, ",");
126 strcpy(ptr_caps->substring_for, ",");
127 strcpy(ptr_caps->sql_except, "t");
128 strcpy(ptr_caps->unix_regexp, "f");
129 ptr_caps->has_versioninfo = 1;
130 }
131 else if (!strcmp(ptr_clrequest->dbserver, "sqlite3")) {
132 strcpy(ptr_caps->multiple_db, "f");
133 strcpy(ptr_caps->sql_enum, "f");
134 strcpy(ptr_caps->rlike, "LIKE");
135 strcpy(ptr_caps->not_rlike, "NOT LIKE");
136 strcpy(ptr_caps->transaction, "t");
137 strcpy(ptr_caps->localhost, "NA");
138 strcpy(ptr_caps->encoding, "");
139 strcpy(ptr_caps->groups, "NA");
140 strcpy(ptr_caps->admin_systable, "NA");
141 strcpy(ptr_caps->listall, "%");
142 strcpy(ptr_caps->bigint, "t");
143 strcpy(ptr_caps->sql_union, "t");
144 strcpy(ptr_caps->named_seq, "f");
145 strcpy(ptr_caps->charlength, "LENGTH");
146 strcpy(ptr_caps->substring, "SUBSTR");
147 strcpy(ptr_caps->substring_from, ",");
148 strcpy(ptr_caps->substring_for, ",");
149 strcpy(ptr_caps->sql_except, "t");
150 strcpy(ptr_caps->unix_regexp, "f");
151 ptr_caps->has_versioninfo = 1;
152 }
153 else {
154 LOG_PRINT(LOG_WARNING, "unknown libdbi driver requested");
155 free(ptr_caps);
156 return NULL;
157 }
158
159 return ptr_caps;
160 }
161
162 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
163 free_db_caps(): frees the memory of a db_caps struct
164
165 free_db_caps returns nothing
166
167 struct db_caps* ptr_caps ptr to a db_caps structure
168
169 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
free_db_caps(struct db_caps * ptr_caps)170 void free_db_caps(struct db_caps* ptr_caps) {
171 if (ptr_caps) {
172 free(ptr_caps);
173 }
174 }
175
176 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
177 create_tables_mysql(): creates MySQL-specific reference data tables
178
179 int create_tables_mysql returns >0 if error, 0 if successful
180
181 dbi_conn conn database connection structure
182
183 struct CLIENT_REQUEST* ptr_clrequest ptr to structure with client info
184
185 int is_temp if nonzero, create a temporary table, otherwise a permanent table
186
187 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
create_tables_mysql(dbi_conn conn,struct CLIENT_REQUEST * ptr_clrequest,int is_temp)188 int create_tables_mysql(dbi_conn conn, struct CLIENT_REQUEST* ptr_clrequest, int is_temp) {
189 char* sql_command;
190 char temporary[] = TEMP_TABLE_SPECIFIER;
191 char prefix[] = TEMP_TABLE_NAME_PREFIX;
192 size_t sql_command_len = 2048;
193 dbi_result dbires;
194
195 /* get us some memory for the query strings */
196 if ((sql_command = malloc(sql_command_len)) == NULL) {
197 return 801;
198 }
199
200 /* fix the inserts */
201 if (!is_temp) {
202 *temporary = '\0';
203 *prefix = '\0';
204 }
205
206 /* create the metadata table */
207 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%smeta ("
208 "meta_app VARCHAR(20),"
209 "meta_type VARCHAR(20),"
210 "meta_version VARCHAR(20),"
211 "meta_dbversion SMALLINT,"
212 "meta_create_date DATETIME,"
213 "meta_modify_date DATETIME)", temporary, prefix);
214 LOG_PRINT(LOG_DEBUG, sql_command);
215
216 dbires = dbi_conn_query(conn, sql_command);
217
218 if (!dbires) {
219 free(sql_command);
220 LOG_PRINT(LOG_ERR, get_status_msg(211));
221 return 211;
222 }
223
224 dbi_result_free(dbires);
225
226 /* create the main table */
227 /* Version issue: later versions support something like:
228 refdb_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
229 without a separate PRIMARY KEY statement */
230 /* using a DATE field for refdb_pubyear is not possible as MySQL < 3.23
231 does not allow to store dates like 2000-00-00, i.e. when only the
232 year is known */
233 /* refdb_periodical_id is actually a FOREIGN KEY, but MySQL does not
234 support this so a simple INT has to do the trick */
235
236 /* todo: the next incarnation of the internal representation of
237 reference data might use properties, i.e. key-value pairs in a
238 separate table, instead of a wealth of (mostly empty)
239 special-purpose columns */
240
241 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%srefdb ("
242 "refdb_id BIGINT NOT NULL AUTO_INCREMENT,"
243 "refdb_citekey VARCHAR(255),"
244 "refdb_type VARCHAR(6),"
245 "refdb_pubyear SMALLINT NOT NULL,"
246 "refdb_secyear SMALLINT NOT NULL,"
247 "refdb_startpage VARCHAR(255),"
248 "refdb_endpage VARCHAR(255),"
249 "refdb_abstract MEDIUMTEXT,"
250 "refdb_title TEXT," /* part title */
251 "refdb_volume VARCHAR(255),"
252 "refdb_issue VARCHAR(255),"
253 "refdb_booktitle TEXT," /* publication title */
254 "refdb_city VARCHAR(255),"
255 "refdb_publisher VARCHAR(255),"
256 "refdb_title_series TEXT," /* set title */
257 "refdb_address TEXT,"
258 "refdb_issn VARCHAR(255),"
259 "refdb_pyother_info VARCHAR(255),"
260 "refdb_secother_info VARCHAR(255),"
261 "refdb_periodical_id BIGINT,"
262 "refdb_user1 VARCHAR(255),"
263 "refdb_user2 VARCHAR(255),"
264 "refdb_user3 VARCHAR(255),"
265 "refdb_user4 VARCHAR(255),"
266 "refdb_user5 VARCHAR(255),"
267 "refdb_typeofwork VARCHAR(255),"
268 "refdb_area VARCHAR(255),"
269 "refdb_ostype VARCHAR(255),"
270 "refdb_degree VARCHAR(255),"
271 "refdb_runningtime VARCHAR(255),"
272 "refdb_classcodeintl VARCHAR(255),"
273 "refdb_classcodeus VARCHAR(255),"
274 "refdb_senderemail VARCHAR(255),"
275 "refdb_recipientemail VARCHAR(255),"
276 "refdb_mediatype VARCHAR(255),"
277 "refdb_numvolumes VARCHAR(255),"
278 "refdb_edition VARCHAR(255),"
279 "refdb_computer VARCHAR(255),"
280 "refdb_conferencelocation VARCHAR(255),"
281 "refdb_registrynum VARCHAR(255),"
282 "refdb_classification VARCHAR(255),"
283 "refdb_section VARCHAR(255),"
284 "refdb_pamphletnum VARCHAR(255),"
285 "refdb_chapternum VARCHAR(255),"
286 "KEY (refdb_pubyear),"
287 "UNIQUE (refdb_citekey),"
288 "PRIMARY KEY (refdb_id))", temporary, prefix);
289
290 LOG_PRINT(LOG_DEBUG, sql_command);
291
292 dbires = dbi_conn_query(conn, sql_command);
293
294 if (!dbires) {
295 free(sql_command);
296 LOG_PRINT(LOG_ERR, get_status_msg(212));
297 return 212;
298 }
299
300 dbi_result_free(dbires);
301
302 /* create the author table */
303 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sauthor ("
304 "author_id BIGINT NOT NULL AUTO_INCREMENT,"
305 "author_name VARCHAR(255) NOT NULL,"
306 "author_lastname VARCHAR(255),"
307 "author_firstname VARCHAR(255),"
308 "author_middlename VARCHAR(255),"
309 "author_suffix VARCHAR(255),"
310 "PRIMARY KEY (author_id),"
311 "KEY (author_name(7)))", temporary, prefix);
312 LOG_PRINT(LOG_DEBUG, sql_command);
313
314 dbires = dbi_conn_query(conn, sql_command);
315
316 if (!dbires) {
317 free(sql_command);
318 LOG_PRINT(LOG_ERR, get_status_msg(213));
319 return 213;
320 }
321
322 dbi_result_free(dbires);
323
324 /* create the keyword table */
325 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%skeyword ("
326 "keyword_id BIGINT NOT NULL AUTO_INCREMENT,"
327 "keyword_name VARCHAR(255) NOT NULL,"
328 "PRIMARY KEY (keyword_id),"
329 "KEY (keyword_name(10)))", temporary, prefix);
330 LOG_PRINT(LOG_DEBUG, sql_command);
331
332 dbires = dbi_conn_query(conn, sql_command);
333
334 if (!dbires) {
335 free(sql_command);
336 LOG_PRINT(LOG_ERR, get_status_msg(214));
337 return 214;
338 }
339
340 dbi_result_free(dbires);
341
342 /* create the periodical table */
343 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%speriodical ("
344 "periodical_id BIGINT NOT NULL AUTO_INCREMENT,"
345 "periodical_name VARCHAR(255) NOT NULL,"
346 "periodical_abbrev VARCHAR(255) NOT NULL,"
347 "periodical_custabbrev1 VARCHAR(255) NOT NULL,"
348 "periodical_custabbrev2 VARCHAR(255) NOT NULL,"
349 "PRIMARY KEY (periodical_id),"
350 "KEY (periodical_name(20)),"
351 "KEY (periodical_abbrev(5)),"
352 "KEY (periodical_custabbrev1(5)),"
353 "KEY (periodical_custabbrev2(5)))", temporary, prefix);
354 LOG_PRINT(LOG_DEBUG, sql_command);
355
356 dbires = dbi_conn_query(conn, sql_command);
357
358 if (!dbires) {
359 free(sql_command);
360 LOG_PRINT(LOG_ERR, get_status_msg(215));
361 return 215;
362 }
363
364 dbi_result_free(dbires);
365
366 /* create the notes table */
367 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%snote ("
368 "note_id BIGINT NOT NULL AUTO_INCREMENT,"
369 "note_key VARCHAR(255),"
370 "note_title VARCHAR(255),"
371 "note_content TEXT,"
372 "note_content_type VARCHAR(255),"
373 "note_content_xmllang VARCHAR(255),"
374 "note_user_id BIGINT,"
375 "note_date DATE,"
376 "note_share SMALLINT,"
377 "KEY (note_title),"
378 "KEY (note_user_id),"
379 "KEY (note_date),"
380 "UNIQUE (note_key),"
381 "PRIMARY KEY (note_id))", temporary, prefix);
382 LOG_PRINT(LOG_DEBUG, sql_command);
383
384 dbires = dbi_conn_query(conn, sql_command);
385
386 if (!dbires) {
387 free(sql_command);
388 LOG_PRINT(LOG_ERR, get_status_msg(216));
389 return 216;
390 }
391
392 dbi_result_free(dbires);
393
394 /* create the user table */
395 /* the user_name length limit is imposed by MySQL, don't know about PostgreSQL */
396 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%suser ("
397 "user_id BIGINT NOT NULL AUTO_INCREMENT,"
398 "user_name VARCHAR(16) NOT NULL,"
399 "PRIMARY KEY (user_id),"
400 "KEY (user_name(8)))", temporary, prefix);
401 LOG_PRINT(LOG_DEBUG, sql_command);
402
403 dbires = dbi_conn_query(conn, sql_command);
404
405 if (!dbires) {
406 free(sql_command);
407 LOG_PRINT(LOG_ERR, get_status_msg(217));
408 return 217;
409 }
410
411 dbi_result_free(dbires);
412
413 /* create the links table */
414 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%slink ("
415 "link_id BIGINT NOT NULL AUTO_INCREMENT,"
416 "link_url TEXT NOT NULL,"
417 "PRIMARY KEY (link_id),"
418 "KEY (link_url(16)))", temporary, prefix);
419 LOG_PRINT(LOG_DEBUG, sql_command);
420
421 dbires = dbi_conn_query(conn, sql_command);
422
423 if (!dbires) {
424 free(sql_command);
425 LOG_PRINT(LOG_ERR, get_status_msg(257));
426 return 257;
427 }
428
429 dbi_result_free(dbires);
430
431 /* create the author xtable */
432 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxauthor ("
433 "xauthor_id BIGINT NOT NULL AUTO_INCREMENT,"
434 "author_id BIGINT NOT NULL,"
435 "refdb_id BIGINT NOT NULL,"
436 "xauthor_type ENUM('part', 'publication', 'set'),"
437 "xauthor_role VARCHAR(64),"
438 "xauthor_position INT,"
439 "PRIMARY KEY (xauthor_id),"
440 "KEY (author_id),"
441 "KEY (refdb_id))", temporary, prefix);
442 LOG_PRINT(LOG_DEBUG, sql_command);
443
444 dbires = dbi_conn_query(conn, sql_command);
445
446 if (!dbires) {
447 free(sql_command);
448 LOG_PRINT(LOG_ERR, get_status_msg(218));
449 return 218;
450 }
451
452 dbi_result_free(dbires);
453
454 /* create the keyword xtable */
455 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxkeyword ("
456 "xkeyword_id BIGINT NOT NULL AUTO_INCREMENT,"
457 "keyword_id BIGINT NOT NULL,"
458 "xref_id BIGINT NOT NULL,"
459 "xkeyword_type ENUM(\"REFERENCE\",\"NOTE\"),"
460 "PRIMARY KEY (xkeyword_id),"
461 "KEY (keyword_id),"
462 "KEY (xref_id))", temporary, prefix);
463 LOG_PRINT(LOG_DEBUG, sql_command);
464
465 dbires = dbi_conn_query(conn, sql_command);
466
467 if (!dbires) {
468 free(sql_command);
469 LOG_PRINT(LOG_ERR, get_status_msg(219));
470 return 219;
471 }
472
473 dbi_result_free(dbires);
474
475 /* create the user xtable */
476 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxuser ("
477 "xuser_id BIGINT NOT NULL AUTO_INCREMENT,"
478 "user_id BIGINT NOT NULL,"
479 "refdb_id BIGINT NOT NULL,"
480 "xuser_reprint ENUM(\"IN FILE\",\"NOT IN FILE\",\"ON REQUEST\"),"
481 "xuser_date DATE,"
482 "xuser_avail VARCHAR(255),"
483 "xuser_notes MEDIUMTEXT,"
484 "PRIMARY KEY (xuser_id),"
485 "KEY (user_id),"
486 "KEY (refdb_id))", temporary, prefix);
487 LOG_PRINT(LOG_DEBUG, sql_command);
488
489 dbires = dbi_conn_query(conn, sql_command);
490
491 if (!dbires) {
492 free(sql_command);
493 LOG_PRINT(LOG_ERR, get_status_msg(220));
494 return 220;
495 }
496
497 dbi_result_free(dbires);
498
499 /* create the notes xtable */
500 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxnote ("
501 "xnote_id BIGINT NOT NULL AUTO_INCREMENT,"
502 "note_id BIGINT NOT NULL,"
503 "xref_id BIGINT NOT NULL,"
504 "xnote_type ENUM(\"REFERENCE\",\"KEYWORD\",\"AUTHOR\",\"PERIODICAL\"),"
505 "PRIMARY KEY (xnote_id),"
506 "KEY (note_id),"
507 "KEY (xref_id),"
508 "KEY (xnote_type))", temporary, prefix);
509 LOG_PRINT(LOG_DEBUG, sql_command);
510
511 dbires = dbi_conn_query(conn, sql_command);
512
513 if (!dbires) {
514 free(sql_command);
515 LOG_PRINT(LOG_ERR, get_status_msg(221));
516 return 221;
517 }
518
519 dbi_result_free(dbires);
520
521 /* create the link xtable */
522 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxlink ("
523 "xlink_id BIGINT NOT NULL AUTO_INCREMENT,"
524 "link_id BIGINT NOT NULL,"
525 "xref_id BIGINT NOT NULL,"
526 "user_id BIGINT DEFAULT 0,"
527 "xlink_type ENUM(\"URL\",\"PDF\",\"FULLTEXT\",\"RELATED\", \"IMAGE\", \"DOI\"),"
528 "xlink_source ENUM(\"REFERENCE\",\"NOTE\"),"
529 "PRIMARY KEY (xlink_id),"
530 "KEY (link_id),"
531 "KEY (xref_id))", temporary, prefix);
532 LOG_PRINT(LOG_DEBUG, sql_command);
533
534 dbires = dbi_conn_query(conn, sql_command);
535
536 if (!dbires) {
537 free(sql_command);
538 LOG_PRINT(LOG_ERR, get_status_msg(258));
539 return 258;
540 }
541
542 dbi_result_free(dbires);
543 free(sql_command);
544
545 return 0;
546 }
547
548 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
549 create_xdup_tables_mysql(): creates MySQL-specific duplicate check tables
550
551 int create_xdup_tables_mysql returns >0 if error, 0 if successful
552
553 dbi_conn conn database connection structure
554
555 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
create_xdup_tables_mysql(dbi_conn conn)556 int create_xdup_tables_mysql(dbi_conn conn) {
557 char* sql_command;
558 size_t sql_command_len = 512;
559 dbi_result dbires;
560
561 /* get us some memory for the query strings */
562 if ((sql_command = malloc(sql_command_len)) == NULL) {
563 return 801;
564 }
565 sprintf(sql_command, "CREATE TEMPORARY TABLE t_temp_xdup ("
566 "xdup_id BIGINT NOT NULL AUTO_INCREMENT,"
567 "xdup_type ENUM (\"TITLE\",\"LOCATION\",\"AUTHOR\",\'CITEKEY\',\'KEYWORD\',\'PERIODICAL\'),"
568 "match_type ENUM (\"IDENT\",\"CASE\",\"LIKE\",\"ABBREV\"),"
569 "temp_refdb_id BIGINT,"
570 "refdb_id BIGINT,"
571 "value_name TEXT,"
572 "temp_value_name TEXT,"
573 "value_name_2 TEXT,"
574 "temp_value_name_2 TEXT,"
575 "value_name_3 TEXT,"
576 "temp_value_name_3 TEXT,"
577 "PRIMARY KEY (xdup_id))");
578
579 LOG_PRINT(LOG_DEBUG, sql_command);
580
581 dbires = dbi_conn_query(conn, sql_command);
582 free(sql_command);
583
584 if (!dbires) {
585 LOG_PRINT(LOG_ERR, get_status_msg(268));
586 return 268;
587 }
588
589 dbi_result_free(dbires);
590
591 return 0;
592 }
593
594 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
595 create_tables_pgsql(): creates PostgreSQL-specific reference data tables
596
597 int create_tables_pgsql returns >0 if error, 0 if successful
598
599 dbi_conn conn database connection structure
600
601 struct CLIENT_REQUEST* ptr_clrequest ptr to structure with client info
602
603 int is_temp if nonzero, create a temporary table, otherwise a permanent table
604
605 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
create_tables_pgsql(dbi_conn conn,struct CLIENT_REQUEST * ptr_clrequest,int is_temp)606 int create_tables_pgsql(dbi_conn conn, struct CLIENT_REQUEST* ptr_clrequest, int is_temp) {
607 char* sql_command;
608 char buffer[512];
609 char temporary[] = TEMP_TABLE_SPECIFIER;
610 char prefix[] = TEMP_TABLE_NAME_PREFIX;
611 size_t sql_command_len = 2048;
612 dbi_result dbires;
613
614 /* get us some memory for the query strings */
615 if ((sql_command = malloc(sql_command_len)) == NULL) {
616 return 801;
617 }
618
619 /* fix the inserts */
620 if (!is_temp) {
621 *temporary = '\0';
622 *prefix = '\0';
623 }
624
625
626 /* create the metadata table */
627 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%smeta ("
628 "meta_app VARCHAR(20),"
629 "meta_type VARCHAR(20),"
630 "meta_version VARCHAR(20),"
631 "meta_dbversion SMALLINT,"
632 "meta_create_date TIMESTAMP,"
633 "meta_modify_date TIMESTAMP)", temporary, prefix);
634 LOG_PRINT(LOG_DEBUG, sql_command);
635
636 dbires = dbi_conn_query(conn, sql_command);
637
638 if (!dbires) {
639 free(sql_command);
640 LOG_PRINT(LOG_ERR, get_status_msg(211));
641 return 211;
642 }
643
644 dbi_result_free(dbires);
645
646 /* create the main table */
647 /* using a DATE field for refdb_pubyear is not possible as MySQL < 3.23
648 does not allow to store dates like 2000-00-00, i.e. when only the
649 year is known */
650 /* refdb_periodical_id is actually a FOREIGN KEY, but MySQL does not
651 support this so a simple INT has to do the trick */
652
653 /* this will implicitly create a SEQUENCE t_refdb_refdb_id_seq */
654 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%srefdb ("
655 "refdb_id BIGSERIAL,"
656 "refdb_citekey VARCHAR(255) UNIQUE,"
657 "refdb_type VARCHAR(6),"
658 "refdb_pubyear SMALLINT,"
659 "refdb_secyear SMALLINT,"
660 "refdb_startpage VARCHAR(255),"
661 "refdb_endpage VARCHAR(255),"
662 "refdb_abstract TEXT,"
663 "refdb_title TEXT,"
664 "refdb_volume VARCHAR(255),"
665 "refdb_issue VARCHAR(255),"
666 "refdb_booktitle TEXT,"
667 "refdb_city VARCHAR(255),"
668 "refdb_publisher VARCHAR(255),"
669 "refdb_title_series TEXT,"
670 "refdb_address TEXT,"
671 "refdb_issn VARCHAR(255),"
672 "refdb_pyother_info VARCHAR(255),"
673 "refdb_secother_info VARCHAR(255),"
674 "refdb_periodical_id BIGINT,"
675 "refdb_user1 VARCHAR(255),"
676 "refdb_user2 VARCHAR(255),"
677 "refdb_user3 VARCHAR(255),"
678 "refdb_user4 VARCHAR(255),"
679 "refdb_user5 VARCHAR(255),"
680 "refdb_typeofwork VARCHAR(255),"
681 "refdb_area VARCHAR(255),"
682 "refdb_ostype VARCHAR(255),"
683 "refdb_degree VARCHAR(255),"
684 "refdb_runningtime VARCHAR(255),"
685 "refdb_classcodeintl VARCHAR(255),"
686 "refdb_classcodeus VARCHAR(255),"
687 "refdb_senderemail VARCHAR(255),"
688 "refdb_recipientemail VARCHAR(255),"
689 "refdb_mediatype VARCHAR(255),"
690 "refdb_numvolumes VARCHAR(255),"
691 "refdb_edition VARCHAR(255),"
692 "refdb_computer VARCHAR(255),"
693 "refdb_conferencelocation VARCHAR(255),"
694 "refdb_registrynum VARCHAR(255),"
695 "refdb_classification VARCHAR(255),"
696 "refdb_section VARCHAR(255),"
697 "refdb_pamphletnum VARCHAR(255),"
698 "refdb_chapternum VARCHAR(255),"
699 "PRIMARY KEY (refdb_id))", temporary, prefix);
700 LOG_PRINT(LOG_DEBUG, sql_command);
701
702 dbires = dbi_conn_query(conn, sql_command);
703
704 if (!dbires) {
705 free(sql_command);
706 LOG_PRINT(LOG_ERR, get_status_msg(212));
707 return 212;
708 }
709
710 dbi_result_free(dbires);
711
712 /* create the periodical index */
713 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%spubyear ON t_%srefdb (refdb_pubyear)", prefix, prefix);
714 LOG_PRINT(LOG_DEBUG, sql_command);
715
716 dbires = dbi_conn_query(conn, sql_command);
717
718 if (!dbires) {
719 free(sql_command);
720 LOG_PRINT(LOG_ERR, get_status_msg(212));
721 return 212;
722 }
723
724 dbi_result_free(dbires);
725
726 /* create the author table */
727 /* this will implicitly create a SEQUENCE t_author_author_id_seq */
728 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sauthor ("
729 "author_id BIGSERIAL,"
730 "author_name VARCHAR(255) NOT NULL,"
731 "author_lastname VARCHAR(255),"
732 "author_firstname VARCHAR(255),"
733 "author_middlename VARCHAR(255),"
734 "author_suffix VARCHAR(255),"
735 "PRIMARY KEY (author_id))", temporary, prefix);
736 LOG_PRINT(LOG_DEBUG, sql_command);
737
738 dbires = dbi_conn_query(conn, sql_command);
739
740 if (!dbires) {
741 free(sql_command);
742 LOG_PRINT(LOG_ERR, get_status_msg(213));
743 return 213;
744 }
745
746 dbi_result_free(dbires);
747
748 /* create the author_name index */
749 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sauthor_name ON t_%sauthor (author_name)", prefix, prefix);
750 LOG_PRINT(LOG_DEBUG, sql_command);
751
752 dbires = dbi_conn_query(conn, sql_command);
753
754 if (!dbires) {
755 free(sql_command);
756 LOG_PRINT(LOG_ERR, get_status_msg(213));
757 return 213;
758 }
759
760 dbi_result_free(dbires);
761
762 /* create the keyword table */
763 /* this will implicitly create a SEQUENCE t_keyword_keyword_id_seq */
764 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%skeyword ("
765 "keyword_id BIGSERIAL,"
766 "keyword_name VARCHAR(255) NOT NULL,"
767 "PRIMARY KEY (keyword_id))", temporary, prefix);
768 LOG_PRINT(LOG_DEBUG, sql_command);
769
770 dbires = dbi_conn_query(conn, sql_command);
771 if (!dbires) {
772 free(sql_command);
773 LOG_PRINT(LOG_ERR, get_status_msg(214));
774 return 214;
775 }
776
777 dbi_result_free(dbires);
778
779 /* create the keyword_name index */
780 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%skeyword_name ON t_%skeyword (keyword_name)", prefix, prefix);
781 LOG_PRINT(LOG_DEBUG, sql_command);
782
783 dbires = dbi_conn_query(conn, sql_command);
784
785 if (!dbires) {
786 free(sql_command);
787 LOG_PRINT(LOG_ERR, get_status_msg(214));
788 return 214;
789 }
790
791 dbi_result_free(dbires);
792
793 /* create the periodical table */
794 /* this will implicitly create a SEQUENCE t_periodical_periodical_id_seq */
795 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%speriodical ("
796 "periodical_id BIGSERIAL,"
797 "periodical_name VARCHAR(255),"
798 "periodical_abbrev VARCHAR(255),"
799 "periodical_custabbrev1 VARCHAR(255),"
800 "periodical_custabbrev2 VARCHAR(255),"
801 "PRIMARY KEY (periodical_id))", temporary, prefix);
802 LOG_PRINT(LOG_DEBUG, sql_command);
803
804 dbires = dbi_conn_query(conn, sql_command);
805
806 if (!dbires) {
807 free(sql_command);
808 LOG_PRINT(LOG_ERR, get_status_msg(215));
809 return 215;
810 }
811
812 dbi_result_free(dbires);
813
814 /* create the t_periodical indices */
815 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%speriodical_name ON t_%speriodical (periodical_name)", prefix, prefix);
816 LOG_PRINT(LOG_DEBUG, sql_command);
817
818 dbires = dbi_conn_query(conn, sql_command);
819
820 if (!dbires) {
821 free(sql_command);
822 LOG_PRINT(LOG_ERR, get_status_msg(215));
823 return 215;
824 }
825
826 dbi_result_free(dbires);
827
828 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%speriodical_abbrev ON t_%speriodical (periodical_abbrev)", prefix, prefix);
829 LOG_PRINT(LOG_DEBUG, sql_command);
830
831 dbires = dbi_conn_query(conn, sql_command);
832
833 if (!dbires) {
834 free(sql_command);
835 LOG_PRINT(LOG_ERR, get_status_msg(215));
836 return 215;
837 }
838
839 dbi_result_free(dbires);
840
841 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%speriodical_custabbrev1 ON t_%speriodical (periodical_custabbrev1)", prefix, prefix);
842 LOG_PRINT(LOG_DEBUG, sql_command);
843
844 dbires = dbi_conn_query(conn, sql_command);
845
846 if (!dbires) {
847 free(sql_command);
848 LOG_PRINT(LOG_ERR, get_status_msg(215));
849 return 215;
850 }
851
852 dbi_result_free(dbires);
853
854 snprintf(sql_command, sql_command_len, "CREATE INDEX i%s_periodical_custabbrev2 ON t_%speriodical (periodical_custabbrev2)", prefix, prefix);
855 LOG_PRINT(LOG_DEBUG, sql_command);
856
857 dbires = dbi_conn_query(conn, sql_command);
858
859 if (!dbires) {
860 free(sql_command);
861 LOG_PRINT(LOG_ERR, get_status_msg(215));
862 return 215;
863 }
864
865 dbi_result_free(dbires);
866
867 /* create the notes table */
868 /* this will implicitly create a SEQUENCE t_note_note_id_seq */
869 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%snote ("
870 "note_id BIGSERIAL,"
871 "note_key VARCHAR(255) UNIQUE,"
872 "note_title VARCHAR(255),"
873 "note_content TEXT,"
874 "note_content_type VARCHAR(255),"
875 "note_content_xmllang VARCHAR(255),"
876 "note_user_id BIGINT,"
877 "note_date DATE,"
878 "note_share SMALLINT,"
879 "PRIMARY KEY (note_id))", temporary, prefix);
880 LOG_PRINT(LOG_DEBUG, sql_command);
881
882 dbires = dbi_conn_query(conn, sql_command);
883
884 if (!dbires) {
885 free(sql_command);
886 LOG_PRINT(LOG_ERR, get_status_msg(216));
887 return 216;
888 }
889
890 dbi_result_free(dbires);
891
892 /* create the t_note indexes */
893 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%snote_title ON t_%snote (note_title)", prefix, prefix);
894 LOG_PRINT(LOG_DEBUG, sql_command);
895
896 dbires = dbi_conn_query(conn, sql_command);
897
898 if (!dbires) {
899 free(sql_command);
900 LOG_PRINT(LOG_ERR, get_status_msg(216));
901 return 216;
902 }
903
904 dbi_result_free(dbires);
905
906 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%snote_note_user_id ON t_%snote (note_user_id)", prefix, prefix);
907 LOG_PRINT(LOG_DEBUG, sql_command);
908
909 dbires = dbi_conn_query(conn, sql_command);
910
911 if (!dbires) {
912 free(sql_command);
913 LOG_PRINT(LOG_ERR, get_status_msg(216));
914 return 216;
915 }
916
917 dbi_result_free(dbires);
918
919 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%snote_date ON t_%snote (note_date)", prefix, prefix);
920 LOG_PRINT(LOG_DEBUG, sql_command);
921
922 dbires = dbi_conn_query(conn, sql_command);
923
924 if (!dbires) {
925 free(sql_command);
926 LOG_PRINT(LOG_ERR, get_status_msg(216));
927 return 216;
928 }
929
930 dbi_result_free(dbires);
931
932 /* create the user table */
933 /* the user_name length limit is imposed by MySQL, don't know about PostgreSQL */
934 /* this will implicitly create a SEQUENCE t_user_user_id_seq */
935 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%suser ("
936 "user_id BIGSERIAL,"
937 "user_name VARCHAR(16) NOT NULL,"
938 "PRIMARY KEY (user_id))", temporary, prefix);
939 LOG_PRINT(LOG_DEBUG, sql_command);
940
941 dbires = dbi_conn_query(conn, sql_command);
942
943 if (!dbires) {
944 free(sql_command);
945 LOG_PRINT(LOG_ERR, get_status_msg(217));
946 return 217;
947 }
948
949 dbi_result_free(dbires);
950
951 /* create user_name index */
952 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%suser_name ON t_%suser (user_name)", prefix, prefix);
953 LOG_PRINT(LOG_DEBUG, sql_command);
954
955 dbires = dbi_conn_query(conn, sql_command);
956
957 if (!dbires) {
958 free(sql_command);
959 LOG_PRINT(LOG_ERR, get_status_msg(217));
960 return 217;
961 }
962
963 dbi_result_free(dbires);
964
965 /* create the link table */
966
967 /* this will implicitly create a SEQUENCE t_link_link_id_seq */
968 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%slink ("
969 "link_id BIGSERIAL,"
970 "link_url TEXT NOT NULL,"
971 "PRIMARY KEY (link_id))", temporary, prefix);
972 LOG_PRINT(LOG_DEBUG, sql_command);
973
974 dbires = dbi_conn_query(conn, sql_command);
975
976 if (!dbires) {
977 free(sql_command);
978 LOG_PRINT(LOG_ERR, get_status_msg(257));
979 return 257;
980 }
981
982 dbi_result_free(dbires);
983
984 /* create link_url index */
985 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%slink_url ON t_%slink (link_url)", prefix, prefix);
986 LOG_PRINT(LOG_DEBUG, sql_command);
987
988 dbires = dbi_conn_query(conn, sql_command);
989
990 if (!dbires) {
991 free(sql_command);
992 LOG_PRINT(LOG_ERR, get_status_msg(257));
993 return 257;
994 }
995
996 dbi_result_free(dbires);
997
998 /* create the author xtable */
999 /* this will implicitly create a SEQUENCE t_xauthor_xauthor_id_seq */
1000 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxauthor ("
1001 "xauthor_id BIGSERIAL,"
1002 "author_id BIGINT NOT NULL,"
1003 "refdb_id BIGINT NOT NULL,"
1004 "xauthor_type SMALLINT DEFAULT 1,"
1005 "xauthor_role VARCHAR(64),"
1006 "xauthor_position INTEGER,"
1007 "PRIMARY KEY (xauthor_id))", temporary, prefix);
1008 LOG_PRINT(LOG_DEBUG, sql_command);
1009
1010 dbires = dbi_conn_query(conn, sql_command);
1011
1012 if (!dbires) {
1013 free(sql_command);
1014 LOG_PRINT(LOG_ERR, get_status_msg(218));
1015 return 218;
1016 }
1017
1018 dbi_result_free(dbires);
1019
1020 /* create x_author indices */
1021 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxauthor_author_id ON t_%sxauthor (author_id)", prefix, prefix);
1022 LOG_PRINT(LOG_DEBUG, sql_command);
1023
1024 dbires = dbi_conn_query(conn, sql_command);
1025
1026 if (!dbires) {
1027 free(sql_command);
1028 LOG_PRINT(LOG_ERR, get_status_msg(218));
1029 return 218;
1030 }
1031
1032 dbi_result_free(dbires);
1033
1034 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxauthor_refdb_id ON t_%sxauthor (refdb_id)", prefix, prefix);
1035 LOG_PRINT(LOG_DEBUG, sql_command);
1036
1037 dbires = dbi_conn_query(conn, sql_command);
1038
1039 if (!dbires) {
1040 free(sql_command);
1041 LOG_PRINT(LOG_ERR, get_status_msg(218));
1042 return 218;
1043 }
1044
1045 dbi_result_free(dbires);
1046
1047 /* create the keyword xtable */
1048 /* this will implicitly create a SEQUENCE t_xkeyword_xkeyword_id_seq */
1049 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxkeyword ("
1050 "xkeyword_id BIGSERIAL,"
1051 "keyword_id BIGINT NOT NULL,"
1052 "xkeyword_type VARCHAR(11) DEFAULT \'REFERENCE\',"
1053 "xref_id BIGINT NOT NULL,"
1054 "PRIMARY KEY (xkeyword_id))", temporary, prefix);
1055 LOG_PRINT(LOG_DEBUG, sql_command);
1056
1057 dbires = dbi_conn_query(conn, sql_command);
1058
1059 if (!dbires) {
1060 free(sql_command);
1061 LOG_PRINT(LOG_ERR, get_status_msg(219));
1062 return 219;
1063 }
1064
1065 dbi_result_free(dbires);
1066
1067 /* create x_keyword indices */
1068 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxkeyword_keyword_id ON t_%sxkeyword (keyword_id)", prefix, prefix);
1069 LOG_PRINT(LOG_DEBUG, sql_command);
1070
1071 dbires = dbi_conn_query(conn, sql_command);
1072
1073 if (!dbires) {
1074 free(sql_command);
1075 LOG_PRINT(LOG_ERR, get_status_msg(219));
1076 return 219;
1077 }
1078
1079 dbi_result_free(dbires);
1080
1081 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxkeyword_xref_id ON t_%sxkeyword (xref_id)", prefix, prefix);
1082 LOG_PRINT(LOG_DEBUG, sql_command);
1083
1084 dbires = dbi_conn_query(conn, sql_command);
1085
1086 if (!dbires) {
1087 free(sql_command);
1088 LOG_PRINT(LOG_ERR, get_status_msg(219));
1089 return 219;
1090 }
1091
1092 dbi_result_free(dbires);
1093
1094 /* create the user xtable */
1095 /* this will implicitly create a SEQUENCE t_xuser_xuser_id_seq */
1096 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxuser ("
1097 "xuser_id BIGSERIAL,"
1098 "user_id BIGINT NOT NULL,"
1099 "refdb_id BIGINT NOT NULL,"
1100 "xuser_reprint VARCHAR(11) DEFAULT \'NOT IN FILE\',"
1101 "xuser_date DATE,"
1102 "xuser_avail VARCHAR(255),"
1103 "xuser_notes TEXT,"
1104 "PRIMARY KEY (xuser_id))", temporary, prefix);
1105 LOG_PRINT(LOG_DEBUG, sql_command);
1106
1107 dbires = dbi_conn_query(conn, sql_command);
1108
1109 if (!dbires) {
1110 free(sql_command);
1111 LOG_PRINT(LOG_ERR, get_status_msg(220));
1112 return 220;
1113 }
1114
1115 dbi_result_free(dbires);
1116
1117 /* create x_user indices */
1118 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxuser_user_id ON t_%sxuser (user_id)", prefix, prefix);
1119 LOG_PRINT(LOG_DEBUG, sql_command);
1120
1121 dbires = dbi_conn_query(conn, sql_command);
1122
1123 if (!dbires) {
1124 free(sql_command);
1125 LOG_PRINT(LOG_ERR, get_status_msg(220));
1126 return 220;
1127 }
1128
1129 dbi_result_free(dbires);
1130
1131 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxuser_refdb_id ON t_%sxuser (refdb_id)", prefix, prefix);
1132 LOG_PRINT(LOG_DEBUG, sql_command);
1133
1134 dbires = dbi_conn_query(conn, sql_command);
1135
1136 if (!dbires) {
1137 free(sql_command);
1138 LOG_PRINT(LOG_ERR, get_status_msg(220));
1139 return 220;
1140 }
1141
1142 dbi_result_free(dbires);
1143
1144 /* create the notes xtable */
1145 /* this will implicitly create a SEQUENCE t_xnote_xnote_id_seq */
1146 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxnote ("
1147 "xnote_id BIGSERIAL,"
1148 "note_id BIGINT NOT NULL,"
1149 "xref_id BIGINT NOT NULL,"
1150 "xnote_type VARCHAR(11) DEFAULT \'REFERENCE\',"
1151 "PRIMARY KEY (xnote_id))", temporary, prefix);
1152 LOG_PRINT(LOG_DEBUG, sql_command);
1153
1154 dbires = dbi_conn_query(conn, sql_command);
1155
1156 if (!dbires) {
1157 free(sql_command);
1158 LOG_PRINT(LOG_ERR, get_status_msg(221));
1159 return 221;
1160 }
1161
1162 dbi_result_free(dbires);
1163
1164 /* create x_note indices */
1165 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxnote_note_id ON t_%sxnote (note_id)", prefix, prefix);
1166 LOG_PRINT(LOG_DEBUG, sql_command);
1167
1168 dbires = dbi_conn_query(conn, sql_command);
1169
1170 if (!dbires) {
1171 free(sql_command);
1172 LOG_PRINT(LOG_ERR, get_status_msg(221));
1173 return 221;
1174 }
1175
1176 dbi_result_free(dbires);
1177
1178 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxnote_xref_id ON t_%sxnote (xref_id)", prefix, prefix);
1179 LOG_PRINT(LOG_DEBUG, sql_command);
1180
1181 dbires = dbi_conn_query(conn, sql_command);
1182
1183 if (!dbires) {
1184 free(sql_command);
1185 LOG_PRINT(LOG_ERR, get_status_msg(221));
1186 return 221;
1187 }
1188
1189 dbi_result_free(dbires);
1190
1191 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxnote_xnote_type ON t_%sxnote (xnote_type)", prefix, prefix);
1192 LOG_PRINT(LOG_DEBUG, sql_command);
1193
1194 dbires = dbi_conn_query(conn, sql_command);
1195
1196 if (!dbires) {
1197 free(sql_command);
1198 LOG_PRINT(LOG_ERR, get_status_msg(221));
1199 return 221;
1200 }
1201
1202 dbi_result_free(dbires);
1203
1204 /* create the links xtable */
1205 /* this will implicitly create a SEQUENCE t_xlink_xlink_id_seq */
1206 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxlink ("
1207 "xlink_id BIGSERIAL,"
1208 "link_id BIGINT NOT NULL,"
1209 "xref_id BIGINT NOT NULL,"
1210 "user_id BIGINT DEFAULT 0,"
1211 "xlink_type VARCHAR(11) DEFAULT \'URL\',"
1212 "xlink_source VARCHAR(11) DEFAULT \'REFERENCE\',"
1213 "PRIMARY KEY (xlink_id))", temporary, prefix);
1214 LOG_PRINT(LOG_DEBUG, sql_command);
1215
1216 dbires = dbi_conn_query(conn, sql_command);
1217
1218 if (!dbires) {
1219 free(sql_command);
1220 LOG_PRINT(LOG_ERR, get_status_msg(258));
1221 return 258;
1222 }
1223
1224 dbi_result_free(dbires);
1225
1226 /* create x_link indices */
1227 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxlink_link_id ON t_%sxlink (link_id)", prefix, prefix);
1228 LOG_PRINT(LOG_DEBUG, sql_command);
1229
1230 dbires = dbi_conn_query(conn, sql_command);
1231
1232 if (!dbires) {
1233 free(sql_command);
1234 LOG_PRINT(LOG_ERR, get_status_msg(258));
1235 return 258;
1236 }
1237
1238 dbi_result_free(dbires);
1239
1240 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxlink_xref_id ON t_%sxlink (xref_id)", prefix, prefix);
1241 LOG_PRINT(LOG_DEBUG, sql_command);
1242
1243 dbires = dbi_conn_query(conn, sql_command);
1244
1245 if (!dbires) {
1246 free(sql_command);
1247 LOG_PRINT(LOG_ERR, get_status_msg(258));
1248 return 258;
1249 }
1250
1251 dbi_result_free(dbires);
1252
1253 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxlink_xlink_type ON t_%sxlink (xlink_type)", prefix, prefix);
1254 LOG_PRINT(LOG_DEBUG, sql_command);
1255
1256 dbires = dbi_conn_query(conn, sql_command);
1257
1258 if (!dbires) {
1259 free(sql_command);
1260 LOG_PRINT(LOG_ERR, get_status_msg(258));
1261 return 258;
1262 }
1263
1264 dbi_result_free(dbires);
1265
1266 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxlink_xlink_source ON t_%sxlink (xlink_source)", prefix, prefix);
1267 LOG_PRINT(LOG_DEBUG, sql_command);
1268
1269 dbires = dbi_conn_query(conn, sql_command);
1270
1271 if (!dbires) {
1272 free(sql_command);
1273 LOG_PRINT(LOG_ERR, get_status_msg(258));
1274 return 258;
1275 }
1276
1277 dbi_result_free(dbires);
1278
1279 /* create user permissions */
1280 if (!is_temp) {
1281 /* PostgreSQL uses table-based permissions, so it is easier to create
1282 them here than in adduser. We grant read/write permissions to a
1283 group "<dbname>user" and read permissions to "<dbname>ruser"
1284 here, whereas adduser just adds users to/removes from this group */
1285
1286 /* ptr_clrequest->argument contains the database name */
1287 if (is_group_pgsql(conn, ptr_clrequest->argument, 0 /* read/write */) != 1) {
1288 sprintf(buffer, "CREATE GROUP %suser", ptr_clrequest->argument);
1289 LOG_PRINT(LOG_DEBUG, buffer);
1290
1291 dbires = dbi_conn_query(conn, buffer);
1292
1293 if (!dbires) {
1294 free(sql_command);
1295 LOG_PRINT(LOG_ERR, get_status_msg(222));
1296 return 222;
1297 }
1298
1299 dbires = dbi_conn_query(conn, sql_command);
1300 }
1301
1302 sprintf(buffer, "GRANT SELECT, INSERT, UPDATE, DELETE ON t_meta, t_refdb, t_author, t_keyword, t_periodical, t_user, t_note, t_link, t_xauthor, t_xkeyword, t_xuser, t_xnote, t_xlink, t_refdb_refdb_id_seq, t_author_author_id_seq, t_keyword_keyword_id_seq, t_periodical_periodical_id_seq, t_user_user_id_seq, t_note_note_id_seq, t_link_link_id_seq, t_xauthor_xauthor_id_seq, t_xkeyword_xkeyword_id_seq, t_xuser_xuser_id_seq, t_xnote_xnote_id_seq, t_xlink_xlink_id_seq TO GROUP %suser", ptr_clrequest->argument);
1303 LOG_PRINT(LOG_DEBUG, buffer);
1304
1305 dbires = dbi_conn_query(conn, buffer);
1306
1307 if (!dbires) {
1308 free(sql_command);
1309 LOG_PRINT(LOG_ERR, get_status_msg(223));
1310 return 223;
1311 }
1312
1313 dbi_result_free(dbires);
1314
1315 if (is_group_pgsql(conn, ptr_clrequest->argument, 1 /* read-only */) != 1) {
1316 sprintf(buffer, "CREATE GROUP %sruser", ptr_clrequest->argument);
1317 LOG_PRINT(LOG_DEBUG, buffer);
1318
1319 dbires = dbi_conn_query(conn, buffer);
1320
1321 if (!dbires) {
1322 free(sql_command);
1323 LOG_PRINT(LOG_ERR, get_status_msg(222));
1324 return 222;
1325 }
1326
1327 dbires = dbi_conn_query(conn, sql_command);
1328 }
1329
1330 sprintf(buffer, "GRANT SELECT ON t_meta, t_refdb, t_author, t_keyword, t_periodical, t_user, t_note, t_link, t_xauthor, t_xkeyword, t_xuser, t_xnote, t_xlink, t_refdb_refdb_id_seq, t_author_author_id_seq, t_keyword_keyword_id_seq, t_periodical_periodical_id_seq, t_user_user_id_seq, t_note_note_id_seq, t_link_link_id_seq, t_xauthor_xauthor_id_seq, t_xkeyword_xkeyword_id_seq, t_xuser_xuser_id_seq, t_xnote_xnote_id_seq, t_xlink_xlink_id_seq TO GROUP %sruser", ptr_clrequest->argument);
1331 LOG_PRINT(LOG_DEBUG, buffer);
1332
1333 dbires = dbi_conn_query(conn, buffer);
1334
1335 if (!dbires) {
1336 free(sql_command);
1337 LOG_PRINT(LOG_ERR, get_status_msg(223));
1338 return 223;
1339 }
1340
1341 dbi_result_free(dbires);
1342 }
1343
1344 free(sql_command);
1345
1346 return 0;
1347 }
1348
1349 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1350 create_xdup_tables_pgsql(): creates PostgreSQL-specific duplicate
1351 check tables
1352
1353 int create_xdup_tables_pgsql returns >0 if error, 0 if successful
1354
1355 dbi_conn conn database connection structure
1356
1357 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
create_xdup_tables_pgsql(dbi_conn conn)1358 int create_xdup_tables_pgsql(dbi_conn conn) {
1359 char* sql_command;
1360 size_t sql_command_len = 512;
1361 dbi_result dbires;
1362
1363 /* get us some memory for the query strings */
1364 if ((sql_command = malloc(sql_command_len)) == NULL) {
1365 return 801;
1366 }
1367 sprintf(sql_command, "CREATE TEMPORARY TABLE t_temp_xdup ("
1368 "xdup_id BIGSERIAL,"
1369 "xdup_type VARCHAR(10),"
1370 "match_type VARCHAR(10),"
1371 "temp_refdb_id BIGINT,"
1372 "refdb_id BIGINT,"
1373 "value_name TEXT,"
1374 "temp_value_name TEXT,"
1375 "value_name_2 TEXT,"
1376 "temp_value_name_2 TEXT,"
1377 "value_name_3 TEXT,"
1378 "temp_value_name_3 TEXT,"
1379 "PRIMARY KEY (xdup_id))");
1380
1381 LOG_PRINT(LOG_DEBUG, sql_command);
1382
1383 dbires = dbi_conn_query(conn, sql_command);
1384
1385 free(sql_command);
1386
1387 if (!dbires) {
1388 LOG_PRINT(LOG_ERR, get_status_msg(268));
1389 return 268;
1390 }
1391
1392 dbi_result_free(dbires);
1393
1394 return 0;
1395 }
1396
1397 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1398 create_tables_sqlite(): creates SQLite-specific reference data tables
1399
1400 int create_tables_sqlite returns >0 if error, 0 if successful
1401
1402 dbi_conn conn database connection structure
1403
1404 struct CLIENT_REQUEST* ptr_clrequest ptr to structure with client info
1405
1406 int is_temp if nonzero, create a temporary table, otherwise a permanent table
1407
1408 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
create_tables_sqlite(dbi_conn conn,struct CLIENT_REQUEST * ptr_clrequest,int is_temp)1409 int create_tables_sqlite(dbi_conn conn, struct CLIENT_REQUEST* ptr_clrequest, int is_temp) {
1410 char* sql_command;
1411 char temporary[] = TEMP_TABLE_SPECIFIER;
1412 char prefix[] = TEMP_TABLE_NAME_PREFIX;
1413 size_t sql_command_len = 2048;
1414 dbi_result dbires;
1415
1416 /* get us some memory for the query strings */
1417 if ((sql_command = malloc(sql_command_len)) == NULL) {
1418 return 801;
1419 }
1420
1421 /* fix the inserts */
1422 if (!is_temp) {
1423 *temporary = '\0';
1424 *prefix = '\0';
1425 }
1426
1427 /* create the metadata table */
1428 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%smeta ("
1429 "meta_app VARCHAR(20),"
1430 "meta_type VARCHAR(20),"
1431 "meta_version VARCHAR(20),"
1432 "meta_dbversion SMALLINT,"
1433 "meta_create_date DATETIME,"
1434 "meta_modify_date DATETIME)", temporary, prefix);
1435 LOG_PRINT(LOG_DEBUG, sql_command);
1436
1437 dbires = dbi_conn_query(conn, sql_command);
1438
1439 if (!dbires) {
1440 free(sql_command);
1441 LOG_PRINT(LOG_ERR, get_status_msg(211));
1442 return 211;
1443 }
1444
1445 dbi_result_free(dbires);
1446
1447 /* create the main table */
1448 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%srefdb ("
1449 "refdb_id INTEGER PRIMARY KEY,"
1450 "refdb_citekey TEXT UNIQUE,"
1451 "refdb_type TEXT,"
1452 "refdb_pubyear SMALLINT,"
1453 "refdb_secyear SMALLINT,"
1454 "refdb_startpage TEXT,"
1455 "refdb_endpage TEXT,"
1456 "refdb_abstract TEXT,"
1457 "refdb_title TEXT,"
1458 "refdb_volume TEXT,"
1459 "refdb_issue TEXT,"
1460 "refdb_booktitle TEXT,"
1461 "refdb_city TEXT,"
1462 "refdb_publisher TEXT,"
1463 "refdb_title_series TEXT,"
1464 "refdb_address TEXT,"
1465 "refdb_issn TEXT,"
1466 "refdb_pyother_info TEXT,"
1467 "refdb_secother_info TEXT,"
1468 "refdb_periodical_id INTEGER,"
1469 "refdb_user1 TEXT,"
1470 "refdb_user2 TEXT,"
1471 "refdb_user3 TEXT,"
1472 "refdb_user4 TEXT,"
1473 "refdb_user5 TEXT,"
1474 "refdb_typeofwork TEXT,"
1475 "refdb_area TEXT,"
1476 "refdb_ostype TEXT,"
1477 "refdb_degree TEXT,"
1478 "refdb_runningtime TEXT,"
1479 "refdb_classcodeintl TEXT,"
1480 "refdb_classcodeus TEXT,"
1481 "refdb_senderemail TEXT,"
1482 "refdb_recipientemail TEXT,"
1483 "refdb_mediatype TEXT,"
1484 "refdb_numvolumes TEXT,"
1485 "refdb_edition TEXT,"
1486 "refdb_computer TEXT,"
1487 "refdb_conferencelocation TEXT,"
1488 "refdb_registrynum TEXT,"
1489 "refdb_classification TEXT,"
1490 "refdb_section TEXT,"
1491 "refdb_pamphletnum TEXT,"
1492 "refdb_chapternum TEXT)", temporary, prefix);
1493 LOG_PRINT(LOG_DEBUG, sql_command);
1494
1495 dbires = dbi_conn_query(conn, sql_command);
1496
1497 if (!dbires) {
1498 free(sql_command);
1499 LOG_PRINT(LOG_ERR, get_status_msg(212));
1500 return 212;
1501 }
1502
1503 dbi_result_free(dbires);
1504
1505 /* create the main table indexes */
1506 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%srefdb_pubyear ON t_%srefdb (refdb_pubyear)", prefix, prefix);
1507 LOG_PRINT(LOG_DEBUG, sql_command);
1508
1509 dbires = dbi_conn_query(conn, sql_command);
1510
1511 if (!dbires) {
1512 free(sql_command);
1513 LOG_PRINT(LOG_ERR, get_status_msg(212));
1514 return 212;
1515 }
1516
1517 dbi_result_free(dbires);
1518
1519 snprintf(sql_command, sql_command_len, "CREATE UNIQUE INDEX i_%srefdb_citekey ON t_%srefdb (refdb_citekey)", prefix, prefix);
1520 LOG_PRINT(LOG_DEBUG, sql_command);
1521
1522 dbires = dbi_conn_query(conn, sql_command);
1523
1524 if (!dbires) {
1525 free(sql_command);
1526 LOG_PRINT(LOG_ERR, get_status_msg(212));
1527 return 212;
1528 }
1529
1530 dbi_result_free(dbires);
1531
1532 /* create the author table */
1533 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sauthor ("
1534 "author_id INTEGER PRIMARY KEY,"
1535 "author_name TEXT NOT NULL,"
1536 "author_lastname TEXT,"
1537 "author_firstname TEXT,"
1538 "author_middlename TEXT,"
1539 "author_suffix TEXT)", temporary, prefix);
1540 LOG_PRINT(LOG_DEBUG, sql_command);
1541
1542 dbires = dbi_conn_query(conn, sql_command);
1543
1544 if (!dbires) {
1545 free(sql_command);
1546 LOG_PRINT(LOG_ERR, get_status_msg(213));
1547 return 213;
1548 }
1549
1550 dbi_result_free(dbires);
1551
1552 /* create t_author table index */
1553 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sauthor_name ON t_%sauthor (author_name)", prefix, prefix);
1554 LOG_PRINT(LOG_DEBUG, sql_command);
1555
1556 dbires = dbi_conn_query(conn, sql_command);
1557
1558 if (!dbires) {
1559 free(sql_command);
1560 LOG_PRINT(LOG_ERR, get_status_msg(213));
1561 return 213;
1562 }
1563
1564 dbi_result_free(dbires);
1565
1566 /* create the keyword table */
1567 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%skeyword ("
1568 "keyword_id INTEGER PRIMARY KEY,"
1569 "keyword_name TEXT NOT NULL)", temporary, prefix);
1570 LOG_PRINT(LOG_DEBUG, sql_command);
1571
1572 dbires = dbi_conn_query(conn, sql_command);
1573
1574 if (!dbires) {
1575 free(sql_command);
1576 LOG_PRINT(LOG_ERR, get_status_msg(214));
1577 return 214;
1578 }
1579
1580 dbi_result_free(dbires);
1581
1582 /* create t_keyword table index */
1583 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%skeyword_name ON t_%skeyword (keyword_name)", prefix, prefix);
1584 LOG_PRINT(LOG_DEBUG, sql_command);
1585
1586 dbires = dbi_conn_query(conn, sql_command);
1587
1588 if (!dbires) {
1589 free(sql_command);
1590 LOG_PRINT(LOG_ERR, get_status_msg(214));
1591 return 214;
1592 }
1593
1594 dbi_result_free(dbires);
1595
1596 /* create the periodical table */
1597 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%speriodical ("
1598 "periodical_id INTEGER PRIMARY KEY,"
1599 "periodical_name TEXT,"
1600 "periodical_abbrev TEXT,"
1601 "periodical_custabbrev1 TEXT,"
1602 "periodical_custabbrev2 TEXT)", temporary, prefix);
1603 LOG_PRINT(LOG_DEBUG, sql_command);
1604
1605 dbires = dbi_conn_query(conn, sql_command);
1606
1607 if (!dbires) {
1608 free(sql_command);
1609 LOG_PRINT(LOG_ERR, get_status_msg(215));
1610 return 215;
1611 }
1612
1613 dbi_result_free(dbires);
1614
1615 /* create t_periodical table indexes */
1616 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%speriodical_name ON t_%speriodical (periodical_name)", prefix, prefix);
1617 LOG_PRINT(LOG_DEBUG, sql_command);
1618
1619 dbires = dbi_conn_query(conn, sql_command);
1620
1621 if (!dbires) {
1622 free(sql_command);
1623 LOG_PRINT(LOG_ERR, get_status_msg(215));
1624 return 215;
1625 }
1626
1627 dbi_result_free(dbires);
1628
1629 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%speriodical_abbrev ON t_%speriodical (periodical_abbrev)", prefix, prefix);
1630 LOG_PRINT(LOG_DEBUG, sql_command);
1631
1632 dbires = dbi_conn_query(conn, sql_command);
1633
1634 if (!dbires) {
1635 free(sql_command);
1636 LOG_PRINT(LOG_ERR, get_status_msg(215));
1637 return 215;
1638 }
1639
1640 dbi_result_free(dbires);
1641
1642 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%speriodical_custabbrev1 ON t_%speriodical (periodical_custabbrev1)", prefix, prefix);
1643 LOG_PRINT(LOG_DEBUG, sql_command);
1644
1645 dbires = dbi_conn_query(conn, sql_command);
1646
1647 if (!dbires) {
1648 free(sql_command);
1649 LOG_PRINT(LOG_ERR, get_status_msg(215));
1650 return 215;
1651 }
1652
1653 dbi_result_free(dbires);
1654
1655 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%speriodical_custabbrev2 ON t_%speriodical (periodical_custabbrev2)", prefix, prefix);
1656 LOG_PRINT(LOG_DEBUG, sql_command);
1657
1658 dbires = dbi_conn_query(conn, sql_command);
1659
1660 if (!dbires) {
1661 free(sql_command);
1662 LOG_PRINT(LOG_ERR, get_status_msg(215));
1663 return 215;
1664 }
1665
1666 dbi_result_free(dbires);
1667
1668 /* create the notes table */
1669 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%snote ("
1670 "note_id INTEGER PRIMARY KEY,"
1671 "note_key TEXT UNIQUE,"
1672 "note_title TEXT,"
1673 "note_content_type TEXT,"
1674 "note_content_xmllang TEXT,"
1675 "note_user_id INTEGER,"
1676 "note_date DATE,"
1677 "note_share SMALLINT,"
1678 "note_content TEXT)", temporary, prefix);
1679 LOG_PRINT(LOG_DEBUG, sql_command);
1680
1681 dbires = dbi_conn_query(conn, sql_command);
1682
1683 if (!dbires) {
1684 free(sql_command);
1685 LOG_PRINT(LOG_ERR, get_status_msg(216));
1686 return 216;
1687 }
1688
1689 dbi_result_free(dbires);
1690
1691 /* create t_note table indexes */
1692 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%snote_title ON t_%snote (note_title)", prefix, prefix);
1693 LOG_PRINT(LOG_DEBUG, sql_command);
1694
1695 dbires = dbi_conn_query(conn, sql_command);
1696
1697 if (!dbires) {
1698 free(sql_command);
1699 LOG_PRINT(LOG_ERR, get_status_msg(216));
1700 return 216;
1701 }
1702
1703 dbi_result_free(dbires);
1704
1705 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%snote_note_userid ON t_%snote (note_user_id)", prefix, prefix);
1706 LOG_PRINT(LOG_DEBUG, sql_command);
1707
1708 dbires = dbi_conn_query(conn, sql_command);
1709
1710 if (!dbires) {
1711 free(sql_command);
1712 LOG_PRINT(LOG_ERR, get_status_msg(216));
1713 return 216;
1714 }
1715
1716 dbi_result_free(dbires);
1717
1718 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%snote_date ON t_%snote (note_date)", prefix, prefix);
1719 LOG_PRINT(LOG_DEBUG, sql_command);
1720
1721 dbires = dbi_conn_query(conn, sql_command);
1722
1723 if (!dbires) {
1724 free(sql_command);
1725 LOG_PRINT(LOG_ERR, get_status_msg(216));
1726 return 216;
1727 }
1728
1729 dbi_result_free(dbires);
1730
1731 /* create the user table */
1732 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%suser ("
1733 "user_id INTEGER PRIMARY KEY,"
1734 "user_name TEXT NOT NULL)", temporary, prefix);
1735 LOG_PRINT(LOG_DEBUG, sql_command);
1736
1737 dbires = dbi_conn_query(conn, sql_command);
1738
1739 if (!dbires) {
1740 free(sql_command);
1741 LOG_PRINT(LOG_ERR, get_status_msg(217));
1742 return 217;
1743 }
1744
1745 dbi_result_free(dbires);
1746
1747 /* create t_user table index */
1748 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%suser_name ON t_%suser (user_name)", prefix, prefix);
1749 LOG_PRINT(LOG_DEBUG, sql_command);
1750
1751 dbires = dbi_conn_query(conn, sql_command);
1752
1753 if (!dbires) {
1754 free(sql_command);
1755 LOG_PRINT(LOG_ERR, get_status_msg(217));
1756 return 217;
1757 }
1758
1759 dbi_result_free(dbires);
1760
1761 /* create the link table */
1762 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%slink ("
1763 "link_id INTEGER PRIMARY KEY,"
1764 "link_url TEXT NOT NULL)", temporary, prefix);
1765 LOG_PRINT(LOG_DEBUG, sql_command);
1766
1767 dbires = dbi_conn_query(conn, sql_command);
1768
1769 if (!dbires) {
1770 free(sql_command);
1771 LOG_PRINT(LOG_ERR, get_status_msg(257));
1772 return 257;
1773 }
1774
1775 dbi_result_free(dbires);
1776
1777 /* create t_link table index */
1778 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%slink_url ON t_%slink (link_url)", prefix, prefix);
1779 LOG_PRINT(LOG_DEBUG, sql_command);
1780
1781 dbires = dbi_conn_query(conn, sql_command);
1782
1783 if (!dbires) {
1784 free(sql_command);
1785 LOG_PRINT(LOG_ERR, get_status_msg(257));
1786 return 257;
1787 }
1788
1789 dbi_result_free(dbires);
1790
1791 /* create the author xtable */
1792 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxauthor ("
1793 "xauthor_id INTEGER PRIMARY KEY,"
1794 "author_id INTEGER NOT NULL,"
1795 "refdb_id INTEGER NOT NULL,"
1796 "xauthor_type SMALLINT DEFAULT 1,"
1797 "xauthor_role TEXT,"
1798 "xauthor_position INTEGER)", temporary, prefix);
1799 LOG_PRINT(LOG_DEBUG, sql_command);
1800
1801 dbires = dbi_conn_query(conn, sql_command);
1802
1803 if (!dbires) {
1804 free(sql_command);
1805 LOG_PRINT(LOG_ERR, get_status_msg(218));
1806 return 218;
1807 }
1808
1809 dbi_result_free(dbires);
1810
1811 /* create t_xauthor table indexes */
1812 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxauthor_autid ON t_%sxauthor (author_id)", prefix, prefix);
1813 LOG_PRINT(LOG_DEBUG, sql_command);
1814
1815 dbires = dbi_conn_query(conn, sql_command);
1816
1817 if (!dbires) {
1818 free(sql_command);
1819 LOG_PRINT(LOG_ERR, get_status_msg(218));
1820 return 218;
1821 }
1822
1823 dbi_result_free(dbires);
1824
1825 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxauthor_refid ON t_%sxauthor (refdb_id)", prefix, prefix);
1826 LOG_PRINT(LOG_DEBUG, sql_command);
1827
1828 dbires = dbi_conn_query(conn, sql_command);
1829
1830 if (!dbires) {
1831 free(sql_command);
1832 LOG_PRINT(LOG_ERR, get_status_msg(218));
1833 return 218;
1834 }
1835
1836 dbi_result_free(dbires);
1837
1838 /* create the keyword xtable */
1839 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxkeyword ("
1840 "xkeyword_id INTEGER PRIMARY KEY,"
1841 "xkeyword_type TEXT,"
1842 "keyword_id INTEGER NOT NULL,"
1843 "xref_id INTEGER NOT NULL)", temporary, prefix);
1844 LOG_PRINT(LOG_DEBUG, sql_command);
1845
1846 dbires = dbi_conn_query(conn, sql_command);
1847
1848 if (!dbires) {
1849 LOG_PRINT(LOG_ERR, get_status_msg(219));
1850 return 219;
1851 }
1852
1853 dbi_result_free(dbires);
1854
1855 /* create t_xkeyword table indexes */
1856 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxkeyword_kwid ON t_%sxkeyword (keyword_id)", prefix, prefix);
1857 LOG_PRINT(LOG_DEBUG, sql_command);
1858
1859 dbires = dbi_conn_query(conn, sql_command);
1860
1861 if (!dbires) {
1862 free(sql_command);
1863 LOG_PRINT(LOG_ERR, get_status_msg(219));
1864 return 219;
1865 }
1866
1867 dbi_result_free(dbires);
1868
1869 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxkeyword_xrefid ON t_%sxkeyword (xref_id)", prefix, prefix);
1870 LOG_PRINT(LOG_DEBUG, sql_command);
1871
1872 dbires = dbi_conn_query(conn, sql_command);
1873
1874 if (!dbires) {
1875 free(sql_command);
1876 LOG_PRINT(LOG_ERR, get_status_msg(219));
1877 return 219;
1878 }
1879
1880 dbi_result_free(dbires);
1881
1882 /* create the user xtable */
1883 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxuser ("
1884 "xuser_id INTEGER PRIMARY KEY,"
1885 "user_id INTEGER NOT NULL,"
1886 "refdb_id INTEGER NOT NULL,"
1887 "xuser_reprint TEXT DEFAULT \'NOT IN FILE\',"
1888 "xuser_date DATE,"
1889 "xuser_avail TEXT,"
1890 "xuser_notes TEXT)", temporary, prefix);
1891 LOG_PRINT(LOG_DEBUG, sql_command);
1892
1893 dbires = dbi_conn_query(conn, sql_command);
1894
1895 if (!dbires) {
1896 free(sql_command);
1897 LOG_PRINT(LOG_ERR, get_status_msg(220));
1898 return 220;
1899 }
1900
1901 dbi_result_free(dbires);
1902
1903 /* create t_xuser table indexes */
1904 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxuser_userid ON t_%sxuser (user_id)", prefix, prefix);
1905 LOG_PRINT(LOG_DEBUG, sql_command);
1906
1907 dbires = dbi_conn_query(conn, sql_command);
1908
1909 if (!dbires) {
1910 free(sql_command);
1911 LOG_PRINT(LOG_ERR, get_status_msg(220));
1912 return 220;
1913 }
1914
1915 dbi_result_free(dbires);
1916
1917 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxuser_refid ON t_%sxuser (refdb_id)", prefix, prefix);
1918 LOG_PRINT(LOG_DEBUG, sql_command);
1919
1920 dbires = dbi_conn_query(conn, sql_command);
1921
1922 if (!dbires) {
1923 free(sql_command);
1924 LOG_PRINT(LOG_ERR, get_status_msg(220));
1925 return 220;
1926 }
1927
1928 dbi_result_free(dbires);
1929
1930 /* create the notes xtable */
1931 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxnote ("
1932 "xnote_id INTEGER PRIMARY KEY,"
1933 "note_id INTEGER NOT NULL,"
1934 "xref_id INTEGER NOT NULL,"
1935 "xnote_type TEXT DEFAULT \'REFERENCE\')", temporary, prefix);
1936 LOG_PRINT(LOG_DEBUG, sql_command);
1937
1938 dbires = dbi_conn_query(conn, sql_command);
1939
1940 if (!dbires) {
1941 free(sql_command);
1942 LOG_PRINT(LOG_ERR, get_status_msg(221));
1943 return 221;
1944 }
1945
1946 dbi_result_free(dbires);
1947
1948 /* create t_xnote table indexes */
1949 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxnote_noteid ON t_%sxnote (note_id)", prefix, prefix);
1950 LOG_PRINT(LOG_DEBUG, sql_command);
1951
1952 dbires = dbi_conn_query(conn, sql_command);
1953
1954 if (!dbires) {
1955 free(sql_command);
1956 LOG_PRINT(LOG_ERR, get_status_msg(221));
1957 return 221;
1958 }
1959
1960 dbi_result_free(dbires);
1961
1962 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxnote_xrefid ON t_%sxnote (xref_id)", prefix, prefix);
1963 LOG_PRINT(LOG_DEBUG, sql_command);
1964
1965 dbires = dbi_conn_query(conn, sql_command);
1966
1967 if (!dbires) {
1968 free(sql_command);
1969 LOG_PRINT(LOG_ERR, get_status_msg(221));
1970 return 221;
1971 }
1972
1973 dbi_result_free(dbires);
1974
1975 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxnote_xnotetype ON t_%sxnote (xnote_type)", prefix, prefix);
1976 LOG_PRINT(LOG_DEBUG, sql_command);
1977
1978 dbires = dbi_conn_query(conn, sql_command);
1979
1980 if (!dbires) {
1981 free(sql_command);
1982 LOG_PRINT(LOG_ERR, get_status_msg(221));
1983 return 221;
1984 }
1985
1986 dbi_result_free(dbires);
1987
1988 /* create the links xtable */
1989 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxlink ("
1990 "xlink_id INTEGER PRIMARY KEY,"
1991 "link_id INTEGER NOT NULL,"
1992 "xref_id INTEGER NOT NULL,"
1993 "user_id INTEGER DEFAULT 0,"
1994 "xlink_type TEXT DEFAULT \'URL\',"
1995 "xlink_source TEXT DEFAULT \'REFERENCE\')", temporary, prefix);
1996 LOG_PRINT(LOG_DEBUG, sql_command);
1997
1998 dbires = dbi_conn_query(conn, sql_command);
1999
2000 if (!dbires) {
2001 free(sql_command);
2002 LOG_PRINT(LOG_ERR, get_status_msg(258));
2003 return 258;
2004 }
2005
2006 dbi_result_free(dbires);
2007
2008 /* create t_xlink table indexes */
2009 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxlink_linkid ON t_%sxlink (link_id)", prefix, prefix);
2010 LOG_PRINT(LOG_DEBUG, sql_command);
2011
2012 dbires = dbi_conn_query(conn, sql_command);
2013
2014 if (!dbires) {
2015 free(sql_command);
2016 LOG_PRINT(LOG_ERR, get_status_msg(258));
2017 return 258;
2018 }
2019
2020 dbi_result_free(dbires);
2021
2022 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxlink_xrefid ON t_%sxlink (xref_id)", prefix, prefix);
2023 LOG_PRINT(LOG_DEBUG, sql_command);
2024
2025 dbires = dbi_conn_query(conn, sql_command);
2026
2027 if (!dbires) {
2028 free(sql_command);
2029 LOG_PRINT(LOG_ERR, get_status_msg(258));
2030 return 258;
2031 }
2032
2033 dbi_result_free(dbires);
2034
2035 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxlink_xlinktype ON t_%sxlink (xlink_type)", prefix, prefix);
2036 LOG_PRINT(LOG_DEBUG, sql_command);
2037
2038 dbires = dbi_conn_query(conn, sql_command);
2039
2040 if (!dbires) {
2041 free(sql_command);
2042 LOG_PRINT(LOG_ERR, get_status_msg(258));
2043 return 258;
2044 }
2045
2046 dbi_result_free(dbires);
2047
2048 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxlink_xlinksource ON t_%sxlink (xlink_source)", prefix, prefix);
2049 LOG_PRINT(LOG_DEBUG, sql_command);
2050
2051 dbires = dbi_conn_query(conn, sql_command);
2052
2053 if (!dbires) {
2054 free(sql_command);
2055 LOG_PRINT(LOG_ERR, get_status_msg(258));
2056 return 258;
2057 }
2058
2059 dbi_result_free(dbires);
2060 free(sql_command);
2061
2062 return 0;
2063 }
2064
2065 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2066 create_xdup_tables_sqlite(): creates SQLite-specific duplicate check tables
2067
2068 int create_xdup_tables_sqlite returns >0 if error, 0 if successful
2069
2070 dbi_conn conn database connection structure
2071
2072 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
create_xdup_tables_sqlite(dbi_conn conn)2073 int create_xdup_tables_sqlite(dbi_conn conn) {
2074 char* sql_command;
2075 size_t sql_command_len = 512;
2076 dbi_result dbires;
2077
2078 /* get us some memory for the query strings */
2079 if ((sql_command = malloc(sql_command_len)) == NULL) {
2080 return 801;
2081 }
2082 sprintf(sql_command, "CREATE TEMPORARY TABLE t_temp_xdup ("
2083 "xdup_id INTEGER PRIMARY KEY,"
2084 "xdup_type VARCHAR(10),"
2085 "match_type VARCHAR(10),"
2086 "temp_refdb_id INTEGER,"
2087 "refdb_id INTEGER,"
2088 "value_name TEXT,"
2089 "temp_value_name TEXT,"
2090 "value_name_2 TEXT,"
2091 "temp_value_name_2 TEXT,"
2092 "value_name_3 TEXT,"
2093 "temp_value_name_3 TEXT)");
2094
2095 LOG_PRINT(LOG_DEBUG, sql_command);
2096
2097 dbires = dbi_conn_query(conn, sql_command);
2098 free(sql_command);
2099
2100 if (!dbires) {
2101 LOG_PRINT(LOG_ERR, get_status_msg(268));
2102 return 268;
2103 }
2104
2105 dbi_result_free(dbires);
2106
2107 return 0;
2108 }
2109
2110 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2111 create_tables_sqlite3(): creates SQLite3-specific reference data tables
2112
2113 int create_tables_sqlite3 returns >0 if error, 0 if successful
2114
2115 struct CLIENT_REQUEST* ptr_clrequest ptr to structure with client info
2116
2117 int is_temp if nonzero, create a temporary table, otherwise a permanent table
2118
2119 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
create_tables_sqlite3(dbi_conn conn,struct CLIENT_REQUEST * ptr_clrequest,int is_temp)2120 int create_tables_sqlite3(dbi_conn conn, struct CLIENT_REQUEST* ptr_clrequest, int is_temp) {
2121 char* sql_command;
2122 char temporary[] = TEMP_TABLE_SPECIFIER;
2123 char prefix[] = TEMP_TABLE_NAME_PREFIX;
2124 size_t sql_command_len = 2048;
2125 dbi_result dbires;
2126
2127 /* get us some memory for the query strings */
2128 if ((sql_command = malloc(sql_command_len)) == NULL) {
2129 return 801;
2130 }
2131
2132 /* fix the inserts */
2133 if (!is_temp) {
2134 *temporary = '\0';
2135 *prefix = '\0';
2136 }
2137
2138 /* create the metadata table */
2139 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%smeta ("
2140 "meta_app VARCHAR(20),"
2141 "meta_type VARCHAR(20),"
2142 "meta_version VARCHAR(20),"
2143 "meta_dbversion SMALLINT,"
2144 "meta_create_date DATETIME,"
2145 "meta_modify_date DATETIME)", temporary, prefix);
2146 LOG_PRINT(LOG_DEBUG, sql_command);
2147
2148 dbires = dbi_conn_query(conn, sql_command);
2149 if (!dbires) {
2150 free(sql_command);
2151 LOG_PRINT(LOG_ERR, get_status_msg(211));
2152 return 211;
2153 }
2154
2155 dbi_result_free(dbires);
2156
2157 /* create the main table */
2158 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%srefdb ("
2159 "refdb_id INTEGER PRIMARY KEY,"
2160 "refdb_citekey TEXT UNIQUE,"
2161 "refdb_type TEXT,"
2162 "refdb_pubyear SMALLINT,"
2163 "refdb_secyear SMALLINT,"
2164 "refdb_startpage TEXT,"
2165 "refdb_endpage TEXT,"
2166 "refdb_abstract TEXT,"
2167 "refdb_title TEXT,"
2168 "refdb_volume TEXT,"
2169 "refdb_issue TEXT,"
2170 "refdb_booktitle TEXT,"
2171 "refdb_city TEXT,"
2172 "refdb_publisher TEXT,"
2173 "refdb_title_series TEXT,"
2174 "refdb_address TEXT,"
2175 "refdb_issn TEXT,"
2176 "refdb_pyother_info TEXT,"
2177 "refdb_secother_info TEXT,"
2178 "refdb_periodical_id BIGINT,"
2179 "refdb_user1 TEXT,"
2180 "refdb_user2 TEXT,"
2181 "refdb_user3 TEXT,"
2182 "refdb_user4 TEXT,"
2183 "refdb_user5 TEXT,"
2184 "refdb_typeofwork TEXT,"
2185 "refdb_area TEXT,"
2186 "refdb_ostype TEXT,"
2187 "refdb_degree TEXT,"
2188 "refdb_runningtime TEXT,"
2189 "refdb_classcodeintl TEXT,"
2190 "refdb_classcodeus TEXT,"
2191 "refdb_senderemail TEXT,"
2192 "refdb_recipientemail TEXT,"
2193 "refdb_mediatype TEXT,"
2194 "refdb_numvolumes TEXT,"
2195 "refdb_edition TEXT,"
2196 "refdb_computer TEXT,"
2197 "refdb_conferencelocation TEXT,"
2198 "refdb_registrynum TEXT,"
2199 "refdb_classification TEXT,"
2200 "refdb_section TEXT,"
2201 "refdb_pamphletnum TEXT,"
2202 "refdb_chapternum TEXT)", temporary, prefix);
2203 LOG_PRINT(LOG_DEBUG, sql_command);
2204
2205 dbires = dbi_conn_query(conn, sql_command);
2206
2207 if (!dbires) {
2208 free(sql_command);
2209 LOG_PRINT(LOG_ERR, get_status_msg(212));
2210 return 212;
2211 }
2212
2213 dbi_result_free(dbires);
2214
2215 /* create the main table indexes */
2216 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%srefdb_pubyear ON t_%srefdb (refdb_pubyear)" , prefix, prefix);
2217 LOG_PRINT(LOG_DEBUG, sql_command);
2218
2219 dbires = dbi_conn_query(conn, sql_command);
2220
2221 if (!dbires) {
2222 free(sql_command);
2223 LOG_PRINT(LOG_ERR, get_status_msg(212));
2224 return 212;
2225 }
2226
2227 dbi_result_free(dbires);
2228
2229 snprintf(sql_command, sql_command_len, "CREATE UNIQUE INDEX i_%srefdb_citekey ON t_%srefdb (refdb_citekey)", prefix, prefix);
2230 LOG_PRINT(LOG_DEBUG, sql_command);
2231
2232 dbires = dbi_conn_query(conn, sql_command);
2233
2234 if (!dbires) {
2235 free(sql_command);
2236 LOG_PRINT(LOG_ERR, get_status_msg(212));
2237 return 212;
2238 }
2239
2240 dbi_result_free(dbires);
2241
2242 /* create the author table */
2243 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sauthor ("
2244 "author_id INTEGER PRIMARY KEY,"
2245 "author_name TEXT NOT NULL,"
2246 "author_lastname TEXT,"
2247 "author_firstname TEXT,"
2248 "author_middlename TEXT,"
2249 "author_suffix TEXT)", temporary, prefix);
2250 LOG_PRINT(LOG_DEBUG, sql_command);
2251
2252 dbires = dbi_conn_query(conn, sql_command);
2253
2254 if (!dbires) {
2255 free(sql_command);
2256 LOG_PRINT(LOG_ERR, get_status_msg(213));
2257 return 213;
2258 }
2259
2260 dbi_result_free(dbires);
2261
2262 /* create t_author table index */
2263 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sauthor_name ON t_%sauthor (author_name)", prefix, prefix);
2264 LOG_PRINT(LOG_DEBUG, sql_command);
2265
2266 dbires = dbi_conn_query(conn, sql_command);
2267
2268 if (!dbires) {
2269 free(sql_command);
2270 LOG_PRINT(LOG_ERR, get_status_msg(213));
2271 return 213;
2272 }
2273
2274 dbi_result_free(dbires);
2275
2276 /* create the keyword table */
2277 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%skeyword ("
2278 "keyword_id INTEGER PRIMARY KEY,"
2279 "keyword_name TEXT NOT NULL)", temporary, prefix);
2280 LOG_PRINT(LOG_DEBUG, sql_command);
2281
2282 dbires = dbi_conn_query(conn, sql_command);
2283
2284 if (!dbires) {
2285 free(sql_command);
2286 LOG_PRINT(LOG_ERR, get_status_msg(214));
2287 return 214;
2288 }
2289
2290 dbi_result_free(dbires);
2291
2292 /* create t_keyword table index */
2293 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%skeyword_name ON t_%skeyword (keyword_name)", prefix, prefix);
2294 LOG_PRINT(LOG_DEBUG, sql_command);
2295
2296 dbires = dbi_conn_query(conn, sql_command);
2297
2298 if (!dbires) {
2299 free(sql_command);
2300 LOG_PRINT(LOG_ERR, get_status_msg(214));
2301 return 214;
2302 }
2303
2304 dbi_result_free(dbires);
2305
2306 /* create the periodical table */
2307 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%speriodical ("
2308 "periodical_id INTEGER PRIMARY KEY,"
2309 "periodical_name TEXT,"
2310 "periodical_abbrev TEXT,"
2311 "periodical_custabbrev1 TEXT,"
2312 "periodical_custabbrev2 TEXT)", temporary, prefix);
2313 LOG_PRINT(LOG_DEBUG, sql_command);
2314
2315 dbires = dbi_conn_query(conn, sql_command);
2316
2317 if (!dbires) {
2318 free(sql_command);
2319 LOG_PRINT(LOG_ERR, get_status_msg(215));
2320 return 215;
2321 }
2322
2323 dbi_result_free(dbires);
2324
2325 /* create t_periodical table indexes */
2326 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%speriodical_name ON t_%speriodical (periodical_name)", prefix, prefix);
2327 LOG_PRINT(LOG_DEBUG, sql_command);
2328
2329 dbires = dbi_conn_query(conn, sql_command);
2330
2331 if (!dbires) {
2332 free(sql_command);
2333 LOG_PRINT(LOG_ERR, get_status_msg(215));
2334 return 215;
2335 }
2336
2337 dbi_result_free(dbires);
2338
2339 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%speriodical_abbrev ON t_%speriodical (periodical_abbrev)", prefix, prefix);
2340 LOG_PRINT(LOG_DEBUG, sql_command);
2341
2342 dbires = dbi_conn_query(conn, sql_command);
2343
2344 if (!dbires) {
2345 free(sql_command);
2346 LOG_PRINT(LOG_ERR, get_status_msg(215));
2347 return 215;
2348 }
2349
2350 dbi_result_free(dbires);
2351
2352 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%speriodical_custabbrev1 ON t_%speriodical (periodical_custabbrev1)", prefix, prefix);
2353 LOG_PRINT(LOG_DEBUG, sql_command);
2354
2355 dbires = dbi_conn_query(conn, sql_command);
2356
2357 if (!dbires) {
2358 free(sql_command);
2359 LOG_PRINT(LOG_ERR, get_status_msg(215));
2360 return 215;
2361 }
2362
2363 dbi_result_free(dbires);
2364
2365 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%speriodical_custabbrev2 ON t_%speriodical (periodical_custabbrev2)", prefix, prefix);
2366 LOG_PRINT(LOG_DEBUG, sql_command);
2367
2368 dbires = dbi_conn_query(conn, sql_command);
2369
2370 if (!dbires) {
2371 free(sql_command);
2372 LOG_PRINT(LOG_ERR, get_status_msg(215));
2373 return 215;
2374 }
2375
2376 dbi_result_free(dbires);
2377
2378 /* create the notes table */
2379 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%snote ("
2380 "note_id INTEGER PRIMARY KEY,"
2381 "note_key TEXT UNIQUE,"
2382 "note_title TEXT,"
2383 "note_content_type TEXT,"
2384 "note_content_xmllang TEXT,"
2385 "note_user_id BIGINT,"
2386 "note_date DATE,"
2387 "note_share SMALLINT,"
2388 "note_content TEXT)" , temporary, prefix);
2389 LOG_PRINT(LOG_DEBUG, sql_command);
2390
2391 dbires = dbi_conn_query(conn, sql_command);
2392
2393 if (!dbires) {
2394 free(sql_command);
2395 LOG_PRINT(LOG_ERR, get_status_msg(216));
2396 return 216;
2397 }
2398
2399 dbi_result_free(dbires);
2400
2401 /* create t_note table indexes */
2402 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%snote_title ON t_%snote (note_title)", prefix, prefix);
2403 LOG_PRINT(LOG_DEBUG, sql_command);
2404
2405 dbires = dbi_conn_query(conn, sql_command);
2406
2407 if (!dbires) {
2408 free(sql_command);
2409 LOG_PRINT(LOG_ERR, get_status_msg(216));
2410 return 216;
2411 }
2412
2413 dbi_result_free(dbires);
2414
2415 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%snote_note_userid ON t_%snote (note_user_id)", prefix, prefix);
2416 LOG_PRINT(LOG_DEBUG, sql_command);
2417
2418 dbires = dbi_conn_query(conn, sql_command);
2419
2420 if (!dbires) {
2421 free(sql_command);
2422 LOG_PRINT(LOG_ERR, get_status_msg(216));
2423 return 216;
2424 }
2425
2426 dbi_result_free(dbires);
2427
2428 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%snote_date ON t_%snote (note_date)", prefix, prefix);
2429 LOG_PRINT(LOG_DEBUG, sql_command);
2430
2431 dbires = dbi_conn_query(conn, sql_command);
2432
2433 if (!dbires) {
2434 free(sql_command);
2435 LOG_PRINT(LOG_ERR, get_status_msg(216));
2436 return 216;
2437 }
2438
2439 dbi_result_free(dbires);
2440
2441 /* create the user table */
2442 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%suser ("
2443 "user_id INTEGER PRIMARY KEY,"
2444 "user_name TEXT NOT NULL)", temporary, prefix);
2445 LOG_PRINT(LOG_DEBUG, sql_command);
2446
2447 dbires = dbi_conn_query(conn, sql_command);
2448
2449 if (!dbires) {
2450 free(sql_command);
2451 LOG_PRINT(LOG_ERR, get_status_msg(217));
2452 return 217;
2453 }
2454
2455 dbi_result_free(dbires);
2456
2457 /* create t_user table index */
2458 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%suser_name ON t_%suser (user_name)", prefix, prefix);
2459 LOG_PRINT(LOG_DEBUG, sql_command);
2460
2461 dbires = dbi_conn_query(conn, sql_command);
2462
2463 if (!dbires) {
2464 free(sql_command);
2465 LOG_PRINT(LOG_ERR, get_status_msg(217));
2466 return 217;
2467 }
2468
2469 dbi_result_free(dbires);
2470
2471 /* create the link table */
2472 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%slink ("
2473 "link_id INTEGER PRIMARY KEY,"
2474 "link_url TEXT NOT NULL)", temporary, prefix);
2475 LOG_PRINT(LOG_DEBUG, sql_command);
2476
2477 dbires = dbi_conn_query(conn, sql_command);
2478
2479 if (!dbires) {
2480 free(sql_command);
2481 LOG_PRINT(LOG_ERR, get_status_msg(257));
2482 return 257;
2483 }
2484
2485 dbi_result_free(dbires);
2486
2487 /* create t_link table index */
2488 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%slink_url ON t_%slink (link_url)", prefix, prefix);
2489 LOG_PRINT(LOG_DEBUG, sql_command);
2490
2491 dbires = dbi_conn_query(conn, sql_command);
2492
2493 if (!dbires) {
2494 free(sql_command);
2495 LOG_PRINT(LOG_ERR, get_status_msg(257));
2496 return 257;
2497 }
2498
2499 dbi_result_free(dbires);
2500
2501 /* create the author xtable */
2502 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxauthor ("
2503 "xauthor_id INTEGER PRIMARY KEY,"
2504 "author_id BIGINT NOT NULL,"
2505 "refdb_id BIGINT NOT NULL,"
2506 "xauthor_type SMALLINT DEFAULT 1,"
2507 "xauthor_role TEXT,"
2508 "xauthor_position INTEGER)", temporary, prefix);
2509 LOG_PRINT(LOG_DEBUG, sql_command);
2510
2511 dbires = dbi_conn_query(conn, sql_command);
2512
2513 if (!dbires) {
2514 free(sql_command);
2515 LOG_PRINT(LOG_ERR, get_status_msg(218));
2516 return 218;
2517 }
2518
2519 dbi_result_free(dbires);
2520
2521 /* create t_xauthor table indexes */
2522 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxauthor_autid ON t_%sxauthor (author_id)", prefix, prefix);
2523 LOG_PRINT(LOG_DEBUG, sql_command);
2524
2525 dbires = dbi_conn_query(conn, sql_command);
2526
2527 if (!dbires) {
2528 free(sql_command);
2529 LOG_PRINT(LOG_ERR, get_status_msg(218));
2530 return 218;
2531 }
2532
2533 dbi_result_free(dbires);
2534
2535 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxauthor_refid ON t_%sxauthor (refdb_id)", prefix, prefix);
2536 LOG_PRINT(LOG_DEBUG, sql_command);
2537
2538 dbires = dbi_conn_query(conn, sql_command);
2539
2540 if (!dbires) {
2541 free(sql_command);
2542 LOG_PRINT(LOG_ERR, get_status_msg(218));
2543 return 218;
2544 }
2545
2546 dbi_result_free(dbires);
2547
2548 /* create the keyword xtable */
2549 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxkeyword ("
2550 "xkeyword_id INTEGER PRIMARY KEY,"
2551 "xkeyword_type TEXT,"
2552 "keyword_id BIGINT NOT NULL,"
2553 "xref_id BIGINT NOT NULL)", temporary, prefix);
2554 LOG_PRINT(LOG_DEBUG, sql_command);
2555
2556 dbires = dbi_conn_query(conn, sql_command);
2557
2558 if (!dbires) {
2559 free(sql_command);
2560 LOG_PRINT(LOG_ERR, get_status_msg(219));
2561 return 219;
2562 }
2563
2564 dbi_result_free(dbires);
2565
2566 /* create t_xkeyword table indexes */
2567 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxkeyword_kwid ON t_%sxkeyword (keyword_id)", prefix, prefix);
2568 LOG_PRINT(LOG_DEBUG, sql_command);
2569
2570 dbires = dbi_conn_query(conn, sql_command);
2571
2572 if (!dbires) {
2573 free(sql_command);
2574 LOG_PRINT(LOG_ERR, get_status_msg(219));
2575 return 219;
2576 }
2577
2578 dbi_result_free(dbires);
2579
2580 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxkeyword_xrefid ON t_%sxkeyword (xref_id)", prefix, prefix);
2581 LOG_PRINT(LOG_DEBUG, sql_command);
2582
2583 dbires = dbi_conn_query(conn, sql_command);
2584
2585 if (!dbires) {
2586 free(sql_command);
2587 LOG_PRINT(LOG_ERR, get_status_msg(219));
2588 return 219;
2589 }
2590
2591 dbi_result_free(dbires);
2592
2593 /* create the user xtable */
2594 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxuser ("
2595 "xuser_id INTEGER PRIMARY KEY,"
2596 "user_id BIGINT NOT NULL,"
2597 "refdb_id BIGINT NOT NULL,"
2598 "xuser_reprint TEXT DEFAULT \'NOT IN FILE\',"
2599 "xuser_date DATE,"
2600 "xuser_avail TEXT,"
2601 "xuser_notes TEXT)", temporary, prefix);
2602 LOG_PRINT(LOG_DEBUG, sql_command);
2603
2604 dbires = dbi_conn_query(conn, sql_command);
2605
2606 if (!dbires) {
2607 free(sql_command);
2608 LOG_PRINT(LOG_ERR, get_status_msg(220));
2609 return 220;
2610 }
2611
2612 dbi_result_free(dbires);
2613
2614 /* create t_xuser table indexes */
2615 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxuser_userid ON t_%sxuser (user_id)", prefix, prefix);
2616 LOG_PRINT(LOG_DEBUG, sql_command);
2617
2618 dbires = dbi_conn_query(conn, sql_command);
2619
2620 if (!dbires) {
2621 free(sql_command);
2622 LOG_PRINT(LOG_ERR, get_status_msg(220));
2623 return 220;
2624 }
2625
2626 dbi_result_free(dbires);
2627
2628 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxuser_refid ON t_%sxuser (refdb_id)", prefix, prefix);
2629 LOG_PRINT(LOG_DEBUG, sql_command);
2630
2631 dbires = dbi_conn_query(conn, sql_command);
2632
2633 if (!dbires) {
2634 free(sql_command);
2635 LOG_PRINT(LOG_ERR, get_status_msg(220));
2636 return 220;
2637 }
2638
2639 dbi_result_free(dbires);
2640
2641 /* create the notes xtable */
2642 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxnote ("
2643 "xnote_id INTEGER PRIMARY KEY,"
2644 "note_id BIGINT NOT NULL,"
2645 "xref_id BIGINT NOT NULL,"
2646 "xnote_type TEXT DEFAULT \'REFERENCE\')", temporary, prefix);
2647 LOG_PRINT(LOG_DEBUG, sql_command);
2648
2649 dbires = dbi_conn_query(conn, sql_command);
2650
2651 if (!dbires) {
2652 free(sql_command);
2653 LOG_PRINT(LOG_ERR, get_status_msg(221));
2654 return 221;
2655 }
2656
2657 dbi_result_free(dbires);
2658
2659 /* create t_xnote table indexes */
2660 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxnote_noteid ON t_%sxnote (note_id)", prefix, prefix);
2661 LOG_PRINT(LOG_DEBUG, sql_command);
2662
2663 dbires = dbi_conn_query(conn, sql_command);
2664
2665 if (!dbires) {
2666 free(sql_command);
2667 LOG_PRINT(LOG_ERR, get_status_msg(221));
2668 return 221;
2669 }
2670
2671 dbi_result_free(dbires);
2672
2673 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxnote_xrefid ON t_%sxnote (xref_id)", prefix, prefix);
2674 LOG_PRINT(LOG_DEBUG, sql_command);
2675
2676 dbires = dbi_conn_query(conn, sql_command);
2677
2678 if (!dbires) {
2679 free(sql_command);
2680 LOG_PRINT(LOG_ERR, get_status_msg(221));
2681 return 221;
2682 }
2683
2684 dbi_result_free(dbires);
2685
2686 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxnote_xnotetype ON t_%sxnote (xnote_type)", prefix, prefix);
2687 LOG_PRINT(LOG_DEBUG, sql_command);
2688
2689 dbires = dbi_conn_query(conn, sql_command);
2690
2691 if (!dbires) {
2692 free(sql_command);
2693 LOG_PRINT(LOG_ERR, get_status_msg(221));
2694 return 221;
2695 }
2696
2697 dbi_result_free(dbires);
2698
2699 /* create the links xtable */
2700 snprintf(sql_command, sql_command_len, "CREATE %sTABLE t_%sxlink ("
2701 "xlink_id INTEGER PRIMARY KEY,"
2702 "link_id BIGINT NOT NULL,"
2703 "xref_id BIGINT NOT NULL,"
2704 "user_id BIGINT DEFAULT 0,"
2705 "xlink_type TEXT DEFAULT \'URL\',"
2706 "xlink_source TEXT DEFAULT \'REFERENCE\')", temporary, prefix);
2707 LOG_PRINT(LOG_DEBUG, sql_command);
2708
2709 dbires = dbi_conn_query(conn, sql_command);
2710
2711 if (!dbires) {
2712 free(sql_command);
2713 LOG_PRINT(LOG_ERR, get_status_msg(258));
2714 return 258;
2715 }
2716
2717 dbi_result_free(dbires);
2718
2719 /* create t_xlink table indexes */
2720 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxlink_linkid ON t_%sxlink (link_id)", prefix, prefix);
2721 LOG_PRINT(LOG_DEBUG, sql_command);
2722
2723 dbires = dbi_conn_query(conn, sql_command);
2724
2725 if (!dbires) {
2726 free(sql_command);
2727 LOG_PRINT(LOG_ERR, get_status_msg(258));
2728 return 258;
2729 }
2730
2731 dbi_result_free(dbires);
2732
2733 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxlink_xrefid ON t_%sxlink (xref_id)", prefix, prefix);
2734 LOG_PRINT(LOG_DEBUG, sql_command);
2735
2736 dbires = dbi_conn_query(conn, sql_command);
2737
2738 if (!dbires) {
2739 free(sql_command);
2740 LOG_PRINT(LOG_ERR, get_status_msg(258));
2741 return 258;
2742 }
2743
2744 dbi_result_free(dbires);
2745
2746 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxlink_xlinktype ON t_%sxlink (xlink_type)", prefix, prefix);
2747 LOG_PRINT(LOG_DEBUG, sql_command);
2748
2749 dbires = dbi_conn_query(conn, sql_command);
2750
2751 if (!dbires) {
2752 free(sql_command);
2753 LOG_PRINT(LOG_ERR, get_status_msg(258));
2754 return 258;
2755 }
2756
2757 dbi_result_free(dbires);
2758
2759 snprintf(sql_command, sql_command_len, "CREATE INDEX i_%sxlink_xlinksource ON t_%sxlink (xlink_source)", prefix, prefix);
2760 LOG_PRINT(LOG_DEBUG, sql_command);
2761
2762 dbires = dbi_conn_query(conn, sql_command);
2763
2764 if (!dbires) {
2765 free(sql_command);
2766 LOG_PRINT(LOG_ERR, get_status_msg(258));
2767 return 258;
2768 }
2769
2770 dbi_result_free(dbires);
2771 free(sql_command);
2772
2773 return 0;
2774 }
2775
2776 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2777 create_xdup_tables_sqlite3(): creates SQLite3-specific duplicate check tables
2778
2779 int create_xdup_tables_sqlite3 returns >0 if error, 0 if successful
2780
2781 dbi_conn conn database connection structure
2782
2783 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
create_xdup_tables_sqlite3(dbi_conn conn)2784 int create_xdup_tables_sqlite3(dbi_conn conn) {
2785 char* sql_command;
2786 size_t sql_command_len = 512;
2787 dbi_result dbires;
2788
2789 /* get us some memory for the query strings */
2790 if ((sql_command = malloc(sql_command_len)) == NULL) {
2791 return 801;
2792 }
2793 sprintf(sql_command, "CREATE TEMPORARY TABLE t_temp_xdup ("
2794 "xdup_id INTEGER PRIMARY KEY,"
2795 "xdup_type VARCHAR(10),"
2796 "match_type VARCHAR(10),"
2797 "temp_refdb_id BIGINT,"
2798 "refdb_id BIGINT,"
2799 "value_name TEXT,"
2800 "temp_value_name TEXT,"
2801 "value_name_2 TEXT,"
2802 "temp_value_name_2 TEXT,"
2803 "value_name_3 TEXT,"
2804 "temp_value_name_3 TEXT)");
2805
2806 LOG_PRINT(LOG_DEBUG, sql_command);
2807
2808 dbires = dbi_conn_query(conn, sql_command);
2809 free(sql_command);
2810
2811 if (!dbires) {
2812 LOG_PRINT(LOG_ERR, get_status_msg(268));
2813 return 268;
2814 }
2815
2816 dbi_result_free(dbires);
2817
2818 return 0;
2819 }
2820
2821 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2822 is_user_pgsql(): checks whether a user exists
2823
2824 int is_user_pgsql returns 1 if the user exists, 0 if the user doesn't
2825 exist, -1 if an error occurs
2826
2827 dbi_conn conn connection to the database to query
2828
2829 const char* username ptr to string with the name of the user
2830
2831 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
is_user_pgsql(dbi_conn conn,const char * username)2832 int is_user_pgsql(dbi_conn conn, const char* username) {
2833 char sql_command[256];
2834 dbi_result dbires;
2835
2836 /* The PostgreSQL system table pg_user contains a list of users */
2837 sprintf(sql_command, "SELECT usename FROM pg_user WHERE usename=\'%s\'", username);
2838 LOG_PRINT(LOG_DEBUG, sql_command);
2839
2840 dbires = dbi_conn_query(conn, sql_command);
2841
2842
2843 if (!dbires) {
2844 return -1;
2845 }
2846
2847 if (dbi_result_get_numrows(dbires)) {
2848 dbi_result_free(dbires);
2849 return 1;
2850 }
2851 else {
2852 dbi_result_free(dbires);
2853 return 0;
2854 }
2855 }
2856
2857 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2858 is_group_pgsql(): checks whether a group exists
2859
2860 int is_group_pgsql returns 1 if the group exists, 0 if the group doesn't
2861 exist, -1 if an error occurs
2862
2863 dbi_conn conn connection to the database to query
2864
2865 const char* dbname ptr to string with the name of the database
2866 related to the group
2867
2868 int is_readonly if 1, checks for a read-only group. If 0, checks
2869 for a read/write group
2870
2871 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
is_group_pgsql(dbi_conn conn,const char * dbname,int is_readonly)2872 int is_group_pgsql(dbi_conn conn, const char* dbname, int is_readonly) {
2873 char sql_command[256];
2874 dbi_result dbires;
2875
2876 /* The PostgreSQL system table pg_group contains a list of groups */
2877 if (!is_readonly) {
2878 sprintf(sql_command, "SELECT groname FROM pg_group WHERE groname=\'%suser\'", dbname);
2879 }
2880 else {
2881 sprintf(sql_command, "SELECT groname FROM pg_group WHERE groname=\'%sruser\'", dbname);
2882 }
2883
2884 LOG_PRINT(LOG_DEBUG, sql_command);
2885
2886 dbires = dbi_conn_query(conn, sql_command);
2887
2888
2889 if (!dbires) {
2890 return -1;
2891 }
2892
2893 if (dbi_result_get_numrows(dbires)) {
2894 dbi_result_free(dbires);
2895 return 1;
2896 }
2897 else {
2898 dbi_result_free(dbires);
2899 return 0;
2900 }
2901 }
2902
2903 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2904 create_temporary_tables(): creates temporary tables for checking refs
2905
2906 int create_temporary tables returns >0 if error, 0 if successful
2907
2908 dbi_conn conn database connection structure
2909
2910 struct CLIENT_REQUEST* ptr_clrequest ptr to structure with client info
2911
2912 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
create_temporary_tables(dbi_conn conn,struct CLIENT_REQUEST * ptr_clrequest)2913 int create_temporary_tables(dbi_conn conn, struct CLIENT_REQUEST* ptr_clrequest) {
2914 const char *drivername;
2915
2916 drivername = dbi_driver_get_name(dbi_conn_get_driver(conn));
2917
2918 if (!strcmp(drivername, "mysql")) {
2919 return create_tables_mysql(conn, ptr_clrequest, 1 /* temporary */);
2920 }
2921 else if (!strcmp(drivername, "pgsql")) {
2922 return create_tables_pgsql(conn, ptr_clrequest, 1 /* temporary */);
2923 }
2924 if (!strcmp(drivername, "sqlite")) {
2925 return create_tables_sqlite(conn, ptr_clrequest, 1 /* temporary */);
2926 }
2927 if (!strcmp(drivername, "sqlite3")) {
2928 return create_tables_sqlite3(conn, ptr_clrequest, 1 /* temporary */);
2929 }
2930 else {
2931 return 1;
2932 }
2933 }
2934
2935 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2936 my_dbi_conn_transaction(): deals with starting/ending transactions
2937
2938 static int my_dbi_conn_transaction returns 0 if ok, 1 if error
2939
2940 dbi_conn conn database connection
2941
2942 int trans_type 0 is "BEGIN", 1 is "COMMIT", 2 is "ROLLBACK"
2943
2944 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_conn_transaction(dbi_conn conn,int trans_type)2945 static int my_dbi_conn_transaction(dbi_conn conn, int trans_type) {
2946 const char *drivername;
2947 char sql_command[2][3][14] = {
2948 {"BEGIN WORK", "COMMIT WORK", "ROLLBACK WORK"},
2949 {"BEGIN", "COMMIT", "ROLLBACK"}
2950 };
2951
2952 dbi_result dbires;
2953
2954 drivername = dbi_driver_get_name(dbi_conn_get_driver(conn));
2955
2956 if (!strcmp(drivername, "pgsql")) {
2957 dbires = dbi_conn_query(conn, sql_command[0][trans_type]);
2958 LOG_PRINT(LOG_DEBUG, sql_command[0][trans_type]);
2959 if (!dbires) {
2960 return 1;
2961 }
2962 dbi_result_free(dbires);
2963 }
2964 else { /* SQLite and MySQL */
2965 /* MySQL supports transactions only in transaction-safe tables
2966 (InnoDB, BDB) but the transaction-related commands do not cause
2967 errors in the other table types. Therefore it safe to use them
2968 anyway, even if they don't have the desired effect. The
2969 non-transaction-safe tables work in auto-commit mode, i.e. each
2970 command is committed immediately. Rollbacks do not have any
2971 effect in this case */
2972 dbires = dbi_conn_query(conn, sql_command[1][trans_type]);
2973 LOG_PRINT(LOG_DEBUG, sql_command[1][trans_type]);
2974 if (!dbires) {
2975 return 1;
2976 }
2977 dbi_result_free(dbires);
2978 }
2979
2980 return 0;
2981 }
2982
2983 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2984 my_dbi_conn_begin(): begin a transaction
2985
2986 int my_dbi_conn_begin returns 0 if ok, 1 if error
2987
2988 dbi_conn conn database connection
2989
2990 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_conn_begin(dbi_conn conn)2991 int my_dbi_conn_begin(dbi_conn conn) {
2992 return my_dbi_conn_transaction(conn, 0);
2993 }
2994
2995 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2996 my_dbi_conn_commit(): commits a transaction
2997
2998 int my_dbi_conn_commit returns 0 if ok, 1 if error
2999
3000 dbi_conn conn database connection
3001
3002 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_conn_commit(dbi_conn conn)3003 int my_dbi_conn_commit(dbi_conn conn) {
3004 return my_dbi_conn_transaction(conn, 1);
3005 }
3006
3007 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3008 my_dbi_conn_rollback(): ends a transaction and discards all changes
3009
3010 int my_dbi_conn_rollback returns 0 if ok, 1 if error
3011
3012 dbi_conn conn database connection
3013
3014 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_conn_rollback(dbi_conn conn)3015 int my_dbi_conn_rollback(dbi_conn conn) {
3016 return my_dbi_conn_transaction(conn, 2);
3017 }
3018
3019 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3020 my_dbi_conn_lock(): locks reference database tables
3021
3022 int my_dbi_conn_lock returns 0 if ok, 1 if error
3023
3024 dbi_conn conn database connection
3025
3026 int replace_ref indicates the context of the operation
3027 0 = add data
3028 1 = replace data
3029 2 = update personal data
3030 3 = add to temporary tables
3031
3032 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_conn_lock(dbi_conn conn,int replace_ref)3033 int my_dbi_conn_lock(dbi_conn conn, int replace_ref) {
3034 const char *drivername;
3035 char prefix[] = TEMP_TABLE_NAME_PREFIX;
3036 dbi_result dbires;
3037
3038 /* fix insert */
3039 if (replace_ref != 3) {
3040 *prefix = '\0';
3041 }
3042
3043 drivername = dbi_driver_get_name(dbi_conn_get_driver(conn));
3044
3045 if (!strcmp(drivername, "mysql")) {
3046 char* sql_command;
3047
3048 if ((sql_command = malloc(512)) == NULL) {
3049 return 1;
3050 }
3051 sprintf(sql_command, "LOCK TABLES t_%srefdb WRITE, t_%sauthor WRITE, t_%skeyword WRITE, t_%speriodical WRITE, t_%suser WRITE, t_%sxauthor WRITE, t_%sxkeyword WRITE, t_%sxuser WRITE, t_%snote WRITE, t_%sxnote WRITE, t_%slink WRITE, t_%sxlink WRITE, %s.t_journal_words WRITE", prefix, prefix, prefix, prefix, prefix, prefix, prefix, prefix, prefix, prefix, prefix, prefix, main_db);
3052 LOG_PRINT(LOG_DEBUG, sql_command);
3053 dbires = dbi_conn_query(conn, sql_command);
3054 free(sql_command);
3055 if (!dbires) {
3056 return 1;
3057 }
3058 dbi_result_free(dbires);
3059 }
3060 else if (!strcmp(drivername, "pgsql")) {
3061 int i;
3062 char sql_command[64];
3063 const char tables[12][13] = {
3064 "refdb",
3065 "author",
3066 "keyword",
3067 "periodical",
3068 "user",
3069 "xauthor",
3070 "xkeyword",
3071 "xuser",
3072 "note",
3073 "xnote",
3074 "link",
3075 "xlink"
3076 };
3077
3078 for (i = 0; i < 12; i++) {
3079 sprintf(sql_command, "LOCK TABLE t_%s%s IN SHARE MODE", prefix, tables[i]);
3080 dbires = dbi_conn_query(conn, sql_command);
3081 LOG_PRINT(LOG_DEBUG, sql_command);
3082 if (!dbires) {
3083 return 1;
3084 }
3085 dbi_result_free(dbires);
3086 }
3087 }
3088 /* else: sqlite does not support table locking */
3089
3090 return 0;
3091 }
3092
3093 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3094 my_dbi_conn_lock_note(): locks notes database tables
3095
3096 int my_dbi_conn_lock_note returns 0 if ok, 1 if error
3097
3098 dbi_conn conn database connection
3099
3100 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_conn_lock_note(dbi_conn conn)3101 int my_dbi_conn_lock_note(dbi_conn conn) {
3102 const char *drivername;
3103 dbi_result dbires;
3104
3105 drivername = dbi_driver_get_name(dbi_conn_get_driver(conn));
3106
3107 if (!strcmp(drivername, "mysql")) {
3108 char* sql_command;
3109
3110 if ((sql_command = malloc(256)) == NULL) {
3111 return 1;
3112 }
3113 strcpy(sql_command, "LOCK TABLES t_keyword WRITE, t_user WRITE, t_xkeyword WRITE, t_xuser WRITE, t_note WRITE, t_xnote WRITE, t_periodical WRITE, t_author WRITE, t_refdb WRITE, t_link WRITE, t_xlink WRITE");
3114 LOG_PRINT(LOG_DEBUG, sql_command);
3115 dbires = dbi_conn_query(conn, sql_command);
3116 free(sql_command);
3117 if (!dbires) {
3118 return 1;
3119 }
3120 dbi_result_free(dbires);
3121 }
3122 else if (!strcmp(drivername, "pgsql")) {
3123 int i;
3124 char sql_command[64];
3125 const char tables[8][13] = {
3126 "t_keyword",
3127 "t_user",
3128 "t_xkeyword",
3129 "t_xuser",
3130 "t_note",
3131 "t_xnote",
3132 "t_link",
3133 "t_xlink"
3134 };
3135
3136 for (i = 0; i < 8; i++) {
3137 sprintf(sql_command, "LOCK TABLE %s IN SHARE MODE", tables[i]);
3138 dbires = dbi_conn_query(conn, sql_command);
3139 LOG_PRINT(LOG_DEBUG, sql_command);
3140 if (!dbires) {
3141 return 1;
3142 }
3143 dbi_result_free(dbires);
3144 }
3145 }
3146 /* else: sqlite does not support table locking */
3147
3148 return 0;
3149 }
3150
3151 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3152 my_dbi_conn_unlock(): unlocks locked tables
3153
3154 int my_dbi_conn_unlock returns 0 if ok, 1 if error
3155
3156 dbi_conn conn database connection
3157
3158 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_conn_unlock(dbi_conn conn)3159 int my_dbi_conn_unlock(dbi_conn conn) {
3160 dbi_result dbires;
3161 const char *drivername;
3162
3163 drivername = dbi_driver_get_name(dbi_conn_get_driver(conn));
3164
3165 if (!strcmp(drivername, "mysql")) {
3166 dbires = dbi_conn_query(conn, "UNLOCK TABLES");
3167 LOG_PRINT(LOG_DEBUG, "UNLOCK TABLES");
3168 if (!dbires) {
3169 return 1;
3170 }
3171 dbi_result_free(dbires);
3172 }
3173 /* else: pgsql unlocks when a transaction is finished, */
3174 /* sqlite does not support lock/unlock */
3175
3176 return 0;
3177 }
3178
3179 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3180 my_dbi_conn_get_cap(): returns a driver capability
3181
3182 const char* my_dbi_conn_get_cap returns a capability string or NULL on error
3183
3184 dbi_conn conn database connection
3185
3186 const char* cap string containing the name of the requested
3187 capability
3188
3189 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_conn_get_cap(dbi_conn conn,const char * cap)3190 const char* my_dbi_conn_get_cap(dbi_conn conn, const char* cap) {
3191 dbi_driver driver;
3192 const char *drivername;
3193
3194 driver = dbi_conn_get_driver(conn);
3195
3196 drivername = dbi_driver_get_name(driver);
3197
3198 /* check whether we already have the version-specific information */
3199 if (!ptr_dbcaps->has_versioninfo) {
3200 set_cap_versioninfo(conn, drivername);
3201 }
3202
3203 return my_dbi_driver_get_cap(driver, cap);
3204 }
3205
3206 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3207 my_dbi_driver_get_cap(): returns a driver capability
3208
3209 const char* my_dbi_driver_get_cap returns a capability string or NULL on error
3210
3211 dbi_driver database driver
3212
3213 const char* cap string containing the name of the requested
3214 capability
3215
3216 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_driver_get_cap(dbi_driver driver,const char * cap)3217 const char* my_dbi_driver_get_cap(dbi_driver driver, const char* cap) {
3218 if (!strcmp(cap, "multiple_db")) {
3219 return ptr_dbcaps->multiple_db;
3220 }
3221 else if (!strcmp(cap, "enum")) {
3222 return ptr_dbcaps->sql_enum;
3223 }
3224 else if (!strcmp(cap, "rlike")) {
3225 return ptr_dbcaps->rlike;
3226 }
3227 else if (!strcmp(cap, "not_rlike")) {
3228 return ptr_dbcaps->not_rlike;
3229 }
3230 else if (!strcmp(cap, "transaction")) {
3231 return ptr_dbcaps->transaction;
3232 }
3233 else if (!strcmp(cap, "localhost")) {
3234 return ptr_dbcaps->localhost;
3235 }
3236 else if (!strcmp(cap, "encoding")) {
3237 return ptr_dbcaps->encoding;
3238 }
3239 else if (!strcmp(cap, "groups")) {
3240 return ptr_dbcaps->groups;
3241 }
3242 else if (!strcmp(cap, "admin_systable")) {
3243 return ptr_dbcaps->admin_systable;
3244 }
3245 else if (!strcmp(cap, "listall")) {
3246 return ptr_dbcaps->listall;
3247 }
3248 else if (!strcmp(cap, "bigint")) {
3249 return ptr_dbcaps->bigint;
3250 }
3251 else if (!strcmp(cap, "union")) {
3252 return ptr_dbcaps->sql_union;
3253 }
3254 else if (!strcmp(cap, "named_seq")) {
3255 return ptr_dbcaps->named_seq;
3256 }
3257 else if (!strcmp(cap, "charlength")) {
3258 return ptr_dbcaps->charlength;
3259 }
3260 else if (!strcmp(cap, "substring")) {
3261 return ptr_dbcaps->substring;
3262 }
3263 else if (!strcmp(cap, "substring_from")) {
3264 return ptr_dbcaps->substring_from;
3265 }
3266 else if (!strcmp(cap, "substring_for")) {
3267 return ptr_dbcaps->substring_for;
3268 }
3269 else if (!strcmp(cap, "except")) {
3270 return ptr_dbcaps->sql_except;
3271 }
3272 else {
3273 return ptr_dbcaps->defval;
3274 }
3275
3276 /* unknown cap */
3277 return NULL;
3278 }
3279
3280 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3281 my_dbi_result_get_idval(): returns an id value by field name. Takes
3282 care of the fact that some db servers do
3283 not support ulonglongs
3284
3285 unsigned long long my_dbi_result_get_idval returns an id value
3286
3287 dbi_result dbires result of a previous query
3288
3289 const char* fieldname name of the field to retrieve
3290
3291 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_result_get_idval(dbi_result dbires,const char * fieldname)3292 unsigned long long my_dbi_result_get_idval(dbi_result dbires, const char* fieldname) {
3293 dbi_conn conn;
3294 unsigned int the_uint;
3295 unsigned long long the_ulonglong;
3296
3297 conn = dbi_result_get_conn(dbires);
3298
3299 if (!strcmp(my_dbi_conn_get_cap(conn, "bigint"), "f")) {
3300 /* database server does not support long long ints, retrieve as
3301 long int instead and cast */
3302 the_uint = dbi_result_get_uint(dbires, fieldname);
3303 /* sprintf(buffer, "ulong: u %u<< lu %lu<< llu %llu", the_ulong, the_ulong, the_ulong); */
3304 /* LOG_PRINT(LOG_DEBUG, buffer); */
3305 return (unsigned long long)the_uint;
3306 }
3307 else {
3308 the_ulonglong = dbi_result_get_ulonglong(dbires, fieldname);
3309 /* sprintf(buffer, "ulonglong: u %u<< lu %lu<< llu %llu", the_ulonglong, the_ulonglong, the_ulonglong); */
3310 /* LOG_PRINT(LOG_DEBUG, buffer); */
3311 return the_ulonglong;
3312 }
3313 }
3314
3315 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3316 my_dbi_result_get_idval_idx(): returns an id value by index. Takes
3317 care of the fact that some db servers
3318 do not support ulonglongs
3319
3320 unsigned long long my_dbi_result_get_idval_idx returns an id value
3321
3322 dbi_result dbires result of a previous query
3323
3324 unsigned int idx index of field to query
3325
3326 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_result_get_idval_idx(dbi_result dbires,unsigned int idx)3327 unsigned long long my_dbi_result_get_idval_idx(dbi_result dbires, unsigned int idx) {
3328 dbi_conn conn;
3329
3330 conn = dbi_result_get_conn(dbires);
3331
3332 if (!strcmp(my_dbi_conn_get_cap(conn, "bigint"), "f")) {
3333 /* database server does not support long long ints, retrieve as
3334 long int instead and cast */
3335 return (unsigned long long)dbi_result_get_uint_idx(dbires, idx);
3336 }
3337 else {
3338 return dbi_result_get_ulonglong_idx(dbires, idx);
3339 }
3340 }
3341
3342 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3343 my_dbi_result_get_int_idval(): returns an id value (4-byte integer)
3344 by field name.
3345
3346 unsigned int my_dbi_result_get_int_idval returns an id value
3347
3348 dbi_result dbires result of a previous query
3349
3350 const char* fieldname name of the field to retrieve
3351
3352 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_result_get_int_idval(dbi_result dbires,const char * fieldname)3353 unsigned int my_dbi_result_get_int_idval(dbi_result dbires, const char* fieldname) {
3354 dbi_conn conn;
3355 unsigned int field_attrib;
3356 unsigned int the_uint;
3357 unsigned long long the_ulonglong;
3358
3359 conn = dbi_result_get_conn(dbires);
3360
3361 field_attrib = dbi_result_get_field_attrib(dbires, fieldname, DBI_INTEGER_SIZE4, DBI_INTEGER_SIZE4);
3362 /* printf("field_type went to %d\n", (int)field_type); */
3363
3364 if (field_attrib) {
3365 /* value is a 4-byte integer */
3366 the_uint = dbi_result_get_uint(dbires, fieldname);
3367 /* sprintf(buffer, "ulong: u %u<< lu %lu<< llu %llu", the_ulong, the_ulong, the_ulong); */
3368 /* LOG_PRINT(LOG_DEBUG, buffer); */
3369 return the_uint;
3370 }
3371 else {
3372 /* value is an 8-byte integer. Cast to uint */
3373 the_ulonglong = dbi_result_get_ulonglong(dbires, fieldname);
3374 /* sprintf(buffer, "ulonglong: u %u<< lu %lu<< llu %llu", the_ulonglong, the_ulonglong, the_ulonglong); */
3375 /* LOG_PRINT(LOG_DEBUG, buffer); */
3376 return (unsigned int)the_ulonglong;
3377 }
3378 }
3379
3380 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3381 my_dbi_result_get_int_idval_idx(): returns an id value (4-byte integer)
3382 by index.
3383
3384 unsigned int my_dbi_result_get_int_idval_idx returns an id value
3385
3386 dbi_result dbires result of a previous query
3387
3388 unsigned int idx index of the field to retrieve
3389
3390 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_result_get_int_idval_idx(dbi_result dbires,unsigned int idx)3391 unsigned int my_dbi_result_get_int_idval_idx(dbi_result dbires, unsigned int idx) {
3392 dbi_conn conn;
3393 unsigned int field_attrib;
3394 unsigned int the_uint;
3395 unsigned long long the_ulonglong;
3396
3397 conn = dbi_result_get_conn(dbires);
3398
3399 field_attrib = dbi_result_get_field_attrib_idx(dbires, idx, DBI_INTEGER_SIZE4, DBI_INTEGER_SIZE4);
3400 /* printf("field_type went to %d\n", (int)field_type); */
3401
3402 if (field_attrib) {
3403 /* value is a 4-byte integer */
3404 the_uint = dbi_result_get_uint_idx(dbires, idx);
3405 /* sprintf(buffer, "ulong: u %u<< lu %lu<< llu %llu", the_ulong, the_ulong, the_ulong); */
3406 /* LOG_PRINT(LOG_DEBUG, buffer); */
3407 return the_uint;
3408 }
3409 else {
3410 /* value is an 8-byte integer. Cast to uint */
3411 the_ulonglong = dbi_result_get_ulonglong_idx(dbires, idx);
3412 /* sprintf(buffer, "ulonglong: u %u<< lu %lu<< llu %llu", the_ulonglong, the_ulonglong, the_ulonglong); */
3413 /* LOG_PRINT(LOG_DEBUG, buffer); */
3414 return (unsigned int)the_ulonglong;
3415 }
3416 }
3417
3418 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3419 my_dbi_conn_get_versioninfo(): retrieves the version info of a db engine
3420
3421 int my_dbi_conn_get_versioninfo returns 0 if ok, 1 if error
3422
3423 dbi_conn conn connection to a database engine
3424
3425 struct VERSIONINFO* ptr_ver ptr to struct to receive version info
3426
3427 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
my_dbi_conn_get_versioninfo(dbi_conn conn,struct VERSIONINFO * ptr_ver)3428 int my_dbi_conn_get_versioninfo(dbi_conn conn, struct VERSIONINFO* ptr_ver) {
3429 char* versioninfo = NULL;
3430 char sql_command[] = "SELECT VERSION()";
3431 dbi_result dbires;
3432
3433 ptr_ver->major = 0;
3434 ptr_ver->minor = 0;
3435 ptr_ver->minuscule = 0;
3436
3437 dbires = dbi_conn_query(conn, sql_command);
3438 LOG_PRINT(LOG_DEBUG, sql_command);
3439 if (dbires) {
3440 if (dbi_result_next_row(dbires)) {
3441 versioninfo = dbi_result_get_string_copy_idx(dbires, 1); /* 1-base index */
3442 }
3443 dbi_result_free(dbires);
3444 }
3445
3446 if (versioninfo) {
3447 if (parse_versioninfo(versioninfo, ptr_ver)) {
3448 return 1;
3449 }
3450 free(versioninfo);
3451 /* printf("major: %d minor: %d minuscule: %d\n", ver.major, ver.minor, ver.minuscule); */
3452 }
3453
3454 return 0;
3455 }
3456
3457 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3458 set_cap_versioninfo(): sets version-specific info in dbcaps structure
3459
3460 static int set_cap_versioninfo returns 0 if ok, 1 if error
3461
3462 dbi_conn conn connection to a database engine
3463
3464 const char* drivername name of db driver
3465
3466 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
set_cap_versioninfo(dbi_conn conn,const char * drivername)3467 static int set_cap_versioninfo(dbi_conn conn, const char* drivername) {
3468 struct VERSIONINFO ver;
3469
3470 /* try only once */
3471 ptr_dbcaps->has_versioninfo = 1;
3472
3473 if (my_dbi_conn_get_versioninfo(conn, &ver)) {
3474 return 1;
3475 }
3476
3477 if (!strcmp(drivername, "mysql")) {
3478 if (ver.major >= 4) { /* 4.0 and later */
3479 strcpy(ptr_dbcaps->sql_union, "t");
3480 }
3481 if ((ver.major == 4 && ver.minor > 0)
3482 || ver.major > 4) { /* 4.1 and later */
3483 strcpy(ptr_dbcaps->encoding, "CHARACTER SET");
3484 }
3485 }
3486 /* else: nothing to do */
3487
3488 return 0;
3489 }
3490