source: trunk/tools/database/db.cpp@ 830

Last change on this file since 830 was 830, checked in by bird, 26 years ago

Initial checkin of the database.

File size: 37.8 KB
Line 
1/* $Id: db.cpp,v 1.1 1999-09-05 02:53:05 bird Exp $ */
2/*
3 * DB - contains all database routines.
4 *
5 * Copyright (c) 1999 knut st. osmundsen
6 *
7 */
8
9/*******************************************************************************
10* Defined Constants *
11*******************************************************************************/
12#define CheckLogContinue(sprintfargs) \
13 if (rc < 0) \
14 { \
15 if (pszError[1] == '\xFE') \
16 { \
17 strcat(pszError, "\n\t"); \
18 pszError += 2; \
19 } \
20 sprintf sprintfargs; \
21 ulRc++; \
22 pszError += strlen(pszError); \
23 pszError[1] = '\xFE'; \
24 }
25
26
27#define CheckFKError(table, msg) \
28 pres2 = mysql_store_result(pmysql);\
29 if (rc < 0 || pres2 == NULL || \
30 mysql_num_rows(pres2) <= 0) \
31 { \
32 if (pszError[1] == '\xFE') \
33 { \
34 strcat(pszError, "\n\t"); \
35 pszError += 2; \
36 } \
37 sprintf(pszError, table ":" \
38 msg \
39 " (refcode=%s) " \
40 "(sql=%s)", \
41 row1[0], \
42 pszQuery); \
43 ulRc++; \
44 pszError += strlen(pszError); \
45 pszError[1] = '\xFE'; \
46 } \
47 if (pres2 != NULL) \
48 mysql_free_result(pres2);
49
50
51/*******************************************************************************
52* Header Files *
53*******************************************************************************/
54#define INCL_DOSMISC
55#include <os2.h>
56#include <stdio.h>
57#include <stdlib.h>
58#include <string.h>
59#include <memory.h>
60#include <signal.h>
61#include <assert.h>
62#include <limits.h>
63#include <mysql.h>
64
65#include "db.h"
66
67
68/*@Global***********************************************************************
69* Global Variables *
70*******************************************************************************/
71static MYSQL mysql;
72static MYSQL *pmysql = NULL;
73
74
75/*@IntFunc**********************************************************************
76* Internal Functions *
77*******************************************************************************/
78static long getvalue(int iField, MYSQL_ROW pRow);
79static unsigned long CheckAuthorError(char * &pszError, const char *pszFieldName, const char *pszFieldValue, const char *pszQuery);
80static unsigned long logDbError(char * &pszError, const char *pszQuery);
81#ifdef NOTDLL
82 extern "C" void dbHandler(int sig);
83#endif
84
85
86/**
87 * Gets the descriptions of the last database error.
88 * @returns Readonly string.
89 */
90char * _System dbGetLastErrorDesc(void)
91{
92 return mysql_error(&mysql);
93}
94
95
96/**
97 * Connects to local database.
98 * @returns Success indicator, TRUE / FALSE.
99 * @param pszDatabase Name of database to use.
100 */
101BOOL _System dbConnect(const char *pszHost, const char *pszUser, const char *pszPassword, const char *pszDatabase)
102{
103 BOOL fRet = FALSE;
104 #ifdef NOTDLL
105 static fHandler = FALSE;
106 /* signal handler */
107 if (!fHandler)
108 {
109 if ( SIG_ERR == signal(SIGBREAK, dbHandler)
110 || SIG_ERR == signal(SIGINT, dbHandler)
111 || SIG_ERR == signal(SIGTERM, dbHandler)
112 || SIG_ERR == signal(SIGABRT, dbHandler)
113 || SIG_ERR == signal(SIGSEGV, dbHandler)
114 || SIG_ERR == signal(SIGILL, dbHandler)
115 )
116 fprintf(stderr, "Error installing signalhandler...");
117 else
118 fHandler = TRUE;
119 }
120 #endif
121
122 /* connect to server */
123 memset(&mysql, 0, sizeof(mysql));
124 pmysql = mysql_connect(&mysql, pszHost, pszUser, pszPassword);
125 if (pmysql != NULL)
126 {
127 /* connect to database */
128 fRet = mysql_select_db(pmysql, pszDatabase) >= 0;
129 if (fRet)
130 mysql_refresh(pmysql, REFRESH_TABLES);
131 }
132
133 return fRet;
134}
135
136
137/**
138 * Disconnects from database.
139 * @returns Success indicator. TRUE / FALSE.
140 */
141BOOL _System dbDisconnect(void)
142{
143 if (pmysql != NULL)
144 {
145 mysql_refresh(pmysql, REFRESH_TABLES);
146 mysql_close(pmysql);
147 pmysql = NULL;
148 }
149 return TRUE;
150}
151
152
153/**
154 * Checks if dll exists. If not exists the dll is inserted.
155 * @returns Dll refcode. -1 on errors.
156 * @param pszDll Dll name.
157 * @remark This search must be case insensitive.
158 * (In the mysql-world everything is case insensitive!)
159 */
160signed short _System dbCheckInsertDll(const char *pszDll)
161{
162 signed short rc;
163 char szQuery[256];
164 MYSQL_RES *pres;
165
166 /* try find match */
167 sprintf(&szQuery[0], "SELECT refcode, name FROM dll WHERE name = '%s'\n", pszDll);
168 rc = mysql_query(pmysql, &szQuery[0]);
169 pres = mysql_store_result(pmysql);
170
171 /* not found? - insert dll */
172 if (rc < 0 || pres == NULL || mysql_num_rows(pres) == 0)
173 {
174 mysql_free_result(pres);
175
176 sprintf(&szQuery[0], "INSERT INTO dll(name) VALUES('%s')\n", pszDll);
177 rc = mysql_query(pmysql, &szQuery[0]);
178
179 /* select row to get refcode */
180 sprintf(&szQuery[0], "SELECT refcode, name FROM dll WHERE name = '%s'\n", pszDll);
181 rc = mysql_query(pmysql, &szQuery[0]);
182 pres = mysql_store_result(pmysql);
183 }
184
185 if (rc >= 0 && pres != NULL && mysql_num_rows(pres) == 1)
186 rc = getvalue(0, mysql_fetch_row(pres));
187 else
188 rc = -1;
189 mysql_free_result(pres);
190
191 return rc;
192}
193
194
195/**
196 * Simple select for a long value.
197 * @returns long value
198 * @param pszTable From part.
199 * @param pszGetColumn Name of column to retreive.
200 * @param pszMatch1 Match column/expression
201 * @param pszMatchValue1 Match value.
202 * @remark Dirty! Don't use this!
203 */
204unsigned short _System dbGet(const char *pszTable, const char *pszGetColumn,
205 const char *pszMatch1, const char *pszMatchValue1)
206{
207 signed short rc;
208 char szQuery[256];
209 MYSQL_RES *pres;
210
211 /* try find match */
212 sprintf(&szQuery[0], "SELECT %s FROM %s WHERE %s = '%s'\n",
213 pszGetColumn, pszTable, pszMatch1, pszMatchValue1);
214 rc = mysql_query(pmysql, &szQuery[0]);
215 pres = mysql_store_result(pmysql);
216
217 if (rc >= 0 && pres != NULL && mysql_num_rows(pres) == 1)
218 rc = getvalue(0, mysql_fetch_row(pres));
219 else
220 rc = -1;
221 mysql_free_result(pres);
222
223 return rc;
224}
225
226
227/**
228 * Updates or inserts a function name into the database.
229 * @returns Success indicator. TRUE / FALSE.
230 * @param usDll Dll refcode.
231 * @param pszFunction Functionname.
232 * @param ulOrdinal Ordinal value.
233 * @param fIgnoreOrdinal Do not update ordinal value.
234 */
235BOOL _System dbInsertUpdateFunction(unsigned short usDll, const char *pszFunction,
236 unsigned long ulOrdinal, BOOL fIgnoreOrdinal)
237{
238 int rc;
239 long lFunction = -1;
240 char szQuery[256];
241 MYSQL_RES *pres;
242
243 /* try find function */
244 sprintf(&szQuery[0], "SELECT refcode FROM function WHERE dll = %d AND name = '%s'", usDll, pszFunction);
245 rc = mysql_query(pmysql, &szQuery[0]);
246 pres = mysql_store_result(pmysql);
247 if (rc >= 0 && pres != NULL && mysql_num_rows(pres) > 0)
248 { /* update function (function is found) */
249 if (mysql_num_rows(pres) > 1)
250 {
251 fprintf(stderr, "internal database integrity error(%s): More function by the same name for the same dll. "
252 "usDll = %d, pszFunction = %s\n", __FUNCTION__, usDll, pszFunction);
253 return FALSE;
254 }
255
256 lFunction = getvalue(0, mysql_fetch_row(pres));
257 mysql_free_result(pres);
258
259 if (!fIgnoreOrdinal)
260 {
261 sprintf(&szQuery[0], "UPDATE function SET ordinal = %ld WHERE refcode = %ld",
262 ulOrdinal, lFunction);
263 rc = mysql_query(pmysql, &szQuery[0]);
264 }
265 }
266 else
267 { /* insert */
268 sprintf(&szQuery[0], "INSERT INTO function(dll, name, ordinal) VALUES(%d, '%s', %ld)",
269 usDll, pszFunction, ulOrdinal);
270 rc = mysql_query(pmysql, &szQuery[0]);
271 }
272
273 return rc >= 0;
274}
275
276
277/**
278 * Get a long value.
279 * @returns Number value of pRow[iField]. -1 on error.
280 * @param iField Index into pRow.
281 * @param pRow Pointer to array (of string pointers).
282 */
283static long getvalue(int iField, MYSQL_ROW papszRow)
284{
285 if (papszRow[iField] != NULL)
286 return atol((char*)papszRow[iField]);
287
288 return -1;
289}
290
291
292/**
293 * Find occurences of a function, given by name.
294 * @returns success indicator, TRUE / FALSE.
295 * @param pszFunctionName
296 * @param pFnFindBuf
297 */
298BOOL _System dbFindFunction(const char *pszFunctionName, PFNFINDBUF pFnFindBuf)
299{
300 MYSQL_RES *pres;
301 MYSQL_ROW row;
302 int rc;
303 char szQuery[256];
304
305 sprintf(&szQuery[0], "SELECT refcode, dll FROM function WHERE name = '%s'", pszFunctionName);
306
307 rc = mysql_query(pmysql, &szQuery[0]);
308 if (rc >= 0)
309 {
310 pres = mysql_store_result(pmysql);
311 if (pres != NULL)
312 {
313 pFnFindBuf->cFns = 0;
314 while ((row = mysql_fetch_row(pres)) != NULL)
315 {
316 pFnFindBuf->alRefCode[pFnFindBuf->cFns] = atol(row[0]);
317 pFnFindBuf->alDllRefCode[pFnFindBuf->cFns] = atol(row[1]);
318
319 /* next */
320 pFnFindBuf->cFns++;
321 }
322 mysql_free_result(pres);
323 }
324 else
325 rc = -1;
326 }
327
328 return rc >= 0;
329}
330
331
332/**
333 * Finds the refcode for an author, if the author exists.
334 * @returns Author 'refcode'.
335 * @param pszAuthor String which holds the identifier of an author.
336 * This doesn't have to be the name. Initials, alias and email
337 * is also searched.
338 */
339signed long _System dbFindAuthor(const char *pszAuthor)
340{
341 signed long refcode = -1;
342 MYSQL_RES *pres;
343 char szQuery[256];
344
345 sprintf(&szQuery[0],
346 "SELECT refcode FROM author "
347 "WHERE name = '%s' OR "
348 " initials = '%s' OR "
349 " alias = '%s' OR "
350 " email = '%s'",
351 pszAuthor, pszAuthor, pszAuthor, pszAuthor);
352 if (mysql_query(pmysql, &szQuery[0]) >= 0)
353 {
354 pres = mysql_store_result(pmysql);
355 if (pres != NULL)
356 {
357 /* integrety check */
358 if (mysql_num_rows(pres) > 1)
359 fprintf(stderr, "Integrety: author '%s' is not unique!\n", pszAuthor);
360 refcode = getvalue(0, mysql_fetch_row(pres));
361 mysql_free_result(pres);
362 }
363 }
364
365 return refcode;
366}
367
368
369/**
370 * Gets the state of a function.
371 * @returns state code. On error -1.
372 * @param lRefCode Function refcode.
373 */
374signed long _System dbGetFunctionState(signed long lRefCode)
375{
376 signed long lState = -1;
377 MYSQL_RES *pres;
378 char szQuery[128];
379
380 sprintf(&szQuery[0], "SELECT state FROM function WHERE refcode = %ld", lRefCode);
381 if (mysql_query(pmysql, &szQuery[0]) >= 0)
382 {
383 pres = mysql_store_result(pmysql);
384 if (pres != NULL)
385 {
386 lState = getvalue(0, mysql_fetch_row(pres));
387 mysql_free_result(pres);
388 }
389 }
390
391 return lState;
392}
393
394
395/**
396 * Updates function information.
397 * @returns number of errors.
398 * @param pFnDesc
399 * @param pszError
400 * @result on error(s) pszError will hold information about the error(s).
401 */
402unsigned long _System dbUpdateFunction(PFNDESC pFnDesc, char *pszError)
403{
404 char szQuery[256];
405 char *pszQuery = &szQuery[0];
406 long lCurrentState;
407 int i,rc;
408 unsigned long ulRc = 0;
409 BOOL f = FALSE;
410
411 lCurrentState = dbGetFunctionState(pFnDesc->lRefCode);
412 if (lCurrentState == -1)
413 {
414 strcpy(pszError, dbGetLastErrorDesc());
415 return 1;
416 }
417
418 /* update function table first */
419 strcpy(pszQuery, "UPDATE function SET ");
420 pszQuery += strlen(pszQuery);
421 if (pFnDesc->lStatus != 99 || lCurrentState == 0)
422 {
423 f = TRUE;
424 sprintf(pszQuery, "state = %ld ", pFnDesc->lStatus);
425 }
426 pszQuery += strlen(pszQuery);
427
428 if (pFnDesc->pszReturnType != NULL)
429 {
430 f = TRUE;
431 if (pszQuery[-1] != ' ')
432 {
433 strcat(pszQuery, ", ");
434 pszQuery += strlen(pszQuery);
435 }
436 sprintf(pszQuery, ", return = '%s' ", pFnDesc->pszReturnType);
437 pszQuery += strlen(pszQuery);
438 }
439
440 if (f)
441 {
442 sprintf(pszQuery, "WHERE refcode = %ld", pFnDesc->lRefCode);
443 rc = mysql_query(pmysql, &szQuery[0]);
444 if (rc < 0)
445 {
446 sprintf(pszError, "Updating functiontable failed with error: %s - (sql=%s) ",
447 dbGetLastErrorDesc(), &szQuery[0]);
448 pszError += strlen(pszError) - 1;
449 ulRc++;
450 }
451 }
452
453 /* parameters */
454 pszQuery = &szQuery[0];
455 sprintf(pszQuery, "DELETE FROM parameter WHERE function = %ld", pFnDesc->lRefCode);
456 rc = mysql_query(pmysql, &szQuery[0]);
457 if (rc < 0)
458 {
459 if (*pszError == ' ')
460 strcpy(pszError++, "\n\t");
461 sprintf(pszError, "Deleting old parameters failed with error: %s - (sql=%s) ",
462 dbGetLastErrorDesc(), &szQuery[0]);
463 pszError += strlen(pszError) - 1;
464 ulRc++;
465 }
466
467 for (i = 0; i < pFnDesc->cParams; i++)
468 {
469 sprintf(pszQuery, "INSERT INTO parameter(function, sequencenbr, type, name) "
470 "VALUES (%ld, %d, '%s', '%s')",
471 pFnDesc->lRefCode, i,
472 pFnDesc->apszParamType[i] != NULL ? pFnDesc->apszParamType[i] : "",
473 pFnDesc->apszParamName[i] != NULL ? pFnDesc->apszParamName[i] : ""
474 );
475 rc = mysql_query(pmysql, pszQuery);
476 if (rc < 0)
477 {
478 if (*pszError == ' ')
479 strcpy(pszError++, "\n\t");
480 sprintf(pszError, "Inserting parameter %i failed with error: %s - (sql=%s) ",
481 i, dbGetLastErrorDesc(), &szQuery[0]);
482 pszError += strlen(pszError) - 1;
483 ulRc++;
484 }
485 }
486
487 /* authors */
488 pszQuery = &szQuery[0];
489 sprintf(pszQuery, "DELETE FROM fnauthor WHERE function = %ld", pFnDesc->lRefCode);
490 rc = mysql_query(pmysql, &szQuery[0]);
491 if (rc < 0)
492 {
493 if (*pszError == ' ')
494 strcpy(pszError++, "\n\t");
495 sprintf(pszError, "Deleting old authors failed with error: %s - (sql=%s) ",
496 dbGetLastErrorDesc(), &szQuery[0]);
497 pszError += strlen(pszError) - 1;
498 ulRc++;
499 }
500
501 for (i = 0; i < pFnDesc->cAuthors; i++)
502 {
503 if (pFnDesc->alAuthorRefCode[i] == -1)
504 continue;
505 sprintf(pszQuery, "INSERT INTO fnauthor(author, function) "
506 "VALUES (%ld, %ld)",
507 pFnDesc->alAuthorRefCode[i], pFnDesc->lRefCode);
508 rc = mysql_query(pmysql, pszQuery);
509 if (rc < 0)
510 {
511 if (*pszError == ' ')
512 strcpy(pszError++, "\n\t");
513 sprintf(pszError, "Inserting parameter %i failed with error: %s - (sql=%s) ",
514 i, dbGetLastErrorDesc(), &szQuery[0]);
515 pszError += strlen(pszError) - 1;
516 ulRc++;
517 }
518 }
519
520 return ulRc;
521}
522
523
524/**
525 * Updates the history tables.
526 * @returns Number of signals/errors.
527 * @param pszError Pointer to buffer which will hold the error messages.
528 * @remark This should be called whenever updates have been completed.
529 */
530unsigned long _System dbCreateHistory(char *pszError)
531{
532 unsigned long ulRc = 0;
533 MYSQL_RES *pres;
534 MYSQL_ROW row;
535 char szQuery[256];
536 char *pszQuery = &szQuery[0];
537 int rc;
538 char szCurDt[20] = {0}; /*yyyy-mm-dd\0*/
539
540 mysql_refresh(pmysql, REFRESH_TABLES);
541
542 /* get currentdate - just in case the date changes between the delete and the update is completed. */
543 strcpy(pszQuery, "SELECT CURDATE()");
544 rc = mysql_query(pmysql, pszQuery);
545 pres = mysql_use_result(pmysql);
546 if (rc >= 0 && pres != NULL)
547 {
548 row = mysql_fetch_row(pres);
549 if (row != NULL && mysql_num_rows(pres) == 1)
550 {
551 strcpy(&szCurDt[0], row[0]);
552 while (mysql_fetch_row(pres) != NULL)
553 (void)0;
554
555 /* delete - all rows on this date in the history tables */
556 sprintf(pszQuery, "DELETE FROM historydll WHERE date = '%s'", &szCurDt[0]);
557 rc = mysql_query(pmysql, pszQuery);
558 CheckLogContinue((pszError, "error removing old history rows: %s - (sql=%s) ", dbGetLastErrorDesc(), pszQuery));
559
560 sprintf(pszQuery, "DELETE FROM historyapigroup WHERE date = '%s'", &szCurDt[0]);
561 rc = mysql_query(pmysql, pszQuery);
562 CheckLogContinue((pszError, "error removing old history rows: %s - (sql=%s) ", dbGetLastErrorDesc(), pszQuery));
563
564 sprintf(pszQuery, "DELETE FROM historydlltotal WHERE date = '%s'", &szCurDt[0]);
565 rc = mysql_query(pmysql, pszQuery);
566 CheckLogContinue((pszError, "error removing old history rows: %s - (sql=%s) ", dbGetLastErrorDesc(), pszQuery));
567
568 sprintf(pszQuery, "DELETE FROM historyapigrouptotal WHERE date = '%s'", &szCurDt[0]);
569 CheckLogContinue((pszError, "error removing old history rows: %s - (sql=%s) ", dbGetLastErrorDesc(), pszQuery));
570
571 /* insert new stats */
572 sprintf(pszQuery, "INSERT INTO historydll(dll, state, date, count) "
573 "SELECT dll, state, '%s', count(*) FROM function GROUP BY dll, state",
574 &szCurDt[0]);
575 rc = mysql_query(pmysql, pszQuery);
576 CheckLogContinue((pszError, "error inserting: %s - (sql=%s) ", dbGetLastErrorDesc(), pszQuery));
577
578 sprintf(pszQuery, "INSERT INTO historyapigroup(apigroup, state, date, count) "
579 "SELECT dll, state, '%s', count(*) FROM function WHERE apigroup IS NOT NULL "
580 "GROUP BY apigroup, state",
581 &szCurDt[0]);
582 rc = mysql_query(pmysql, pszQuery);
583 CheckLogContinue((pszError, "error inserting: %s - (sql=%s) ", dbGetLastErrorDesc(), pszQuery));
584
585 /* inserting new totals */
586 sprintf(pszQuery, "INSERT INTO historydlltotal(dll, date, totalcount) "
587 "SELECT dll, '%s', count(*) FROM function GROUP BY dll",
588 &szCurDt[0]);
589 rc = mysql_query(pmysql, pszQuery);
590 CheckLogContinue((pszError, "error inserting: %s - (sql=%s) ", dbGetLastErrorDesc(), pszQuery));
591
592 sprintf(pszQuery, "INSERT INTO historyapigrouptotal(apigroup, date, totalcount) "
593 "SELECT apigroup, '%s', count(*) FROM function WHERE apigroup IS NOT NULL "
594 "GROUP BY apigroup",
595 &szCurDt[0]);
596 rc = mysql_query(pmysql, pszQuery);
597 CheckLogContinue((pszError, "error inserting: %s - (sql=%s) ", dbGetLastErrorDesc(), pszQuery));
598 }
599 else
600 {
601 sprintf(pszError, "error getting current date (row == NULL): %s - (sql=%s) ",
602 dbGetLastErrorDesc(), pszQuery);
603 ulRc++;
604 }
605 }
606 else
607 {
608 sprintf(pszError, "error getting current date: %s - (sql=%s) ",
609 dbGetLastErrorDesc(), pszQuery);
610 ulRc++;
611 }
612
613 mysql_refresh(pmysql, REFRESH_TABLES);
614
615 return ulRc;
616}
617
618
619/**
620 * Check that database integrety is ok. Verfies foreign keys.
621 * @returns numbers of errors.
622 * @param pszError Very large buffer which will hold error messges (if any).
623 * @sketch
624 * @remark current versions of mysql don't support 'SELECT ... WHERE id NOT IN(SELECT id FROM table)'
625 */
626unsigned long _System dbCheckIntegrity(char *pszError)
627{
628 char szQuery[384];
629 char *pszQuery = &szQuery[0];
630 MYSQL_RES *pres1;
631 MYSQL_RES *pres2;
632 MYSQL_ROW row1;
633 int rc;
634 unsigned long ulRc = 0;
635
636 mysql_refresh(pmysql, REFRESH_TABLES);
637
638 /* foreign keys in function table */
639 strcpy(pszQuery, "SELECT refcode, dll, state, apigroup FROM function");
640 rc = mysql_query(pmysql, pszQuery);
641 if (rc >= 0)
642 {
643 pres1 = mysql_store_result(pmysql);
644 if (pres1 != NULL)
645 {
646 while ((row1 = mysql_fetch_row(pres1)) != NULL)
647 {
648 /* check dll */
649 sprintf(pszQuery, "SELECT name FROM dll WHERE refcode = %s", row1[1]);
650 rc = mysql_query(pmysql, pszQuery);
651 CheckFKError("function/dll", "Foreign key 'dll' not found in the dll table");
652
653 /* check state */
654 sprintf(pszQuery, "SELECT name FROM state WHERE refcode = %s", row1[2]);
655 rc = mysql_query(pmysql, pszQuery);
656 CheckFKError("function/state", "Foreign key 'state' not found in the state table");
657
658 /* check apigroup */
659 if (row1[3] != NULL)
660 {
661 sprintf(pszQuery, "SELECT name FROM apigroup WHERE refcode = %s", row1[3]);
662 rc = mysql_query(pmysql, pszQuery);
663 CheckFKError("function/state", "Foreign key 'state' not found in the state table");
664 }
665 }
666 mysql_free_result(pres1);
667 }
668 }
669 else
670 ulRc += logDbError(pszError, pszQuery);
671
672 /* foreign keys in apigroup */
673 strcpy(pszQuery, "SELECT refcode, dll FROM apigroup");
674 rc = mysql_query(pmysql, pszQuery);
675 if (rc >= 0)
676 {
677 pres1 = mysql_store_result(pmysql);
678 if (pres1 != NULL)
679 {
680 while ((row1 = mysql_fetch_row(pres1)) != NULL)
681 {
682 /* check dll */
683 sprintf(pszQuery, "SELECT refcode FROM dll WHERE refcode = %s", row1[1]);
684 rc = mysql_query(pmysql, pszQuery);
685 CheckFKError("apigroup/dll", "Foreign key 'dll' not found in the dll table");
686 }
687 mysql_free_result(pres1);
688 }
689 }
690 else
691 ulRc += logDbError(pszError, pszQuery);
692
693 /* foreign keys in fnauthor */
694 strcpy(pszQuery, "SELECT function, author FROM fnauthor");
695 rc = mysql_query(pmysql, pszQuery);
696 if (rc >= 0)
697 {
698 pres1 = mysql_store_result(pmysql);
699 if (pres1 != NULL)
700 {
701 while ((row1 = mysql_fetch_row(pres1)) != NULL)
702 {
703 /* check function */
704 sprintf(pszQuery, "SELECT refcode FROM function WHERE refcode = %s", row1[1]);
705 rc = mysql_query(pmysql, pszQuery);
706 CheckFKError("fnauthor/function", "Foreign key 'function' not found in the function table");
707
708 /* check author */
709 sprintf(pszQuery, "SELECT refcode FROM author WHERE refcode = %s", row1[1]);
710 rc = mysql_query(pmysql, pszQuery);
711 CheckFKError("fnauthor/author", "Foreign key 'author' not found in the author table");
712 }
713 mysql_free_result(pres1);
714 }
715 }
716 else
717 ulRc += logDbError(pszError, pszQuery);
718
719 /* foreign keys in historydll table */
720 strcpy(pszQuery, "SELECT date, dll, state FROM historydll");
721 rc = mysql_query(pmysql, pszQuery);
722 if (rc >= 0)
723 {
724 pres1 = mysql_store_result(pmysql);
725 if (pres1 != NULL)
726 {
727 while ((row1 = mysql_fetch_row(pres1)) != NULL)
728 {
729 /* check dll */
730 sprintf(pszQuery, "SELECT refcode FROM dll WHERE refcode = %s", row1[1]);
731 rc = mysql_query(pmysql, pszQuery);
732 CheckFKError("historydll/dll", "Foreign key 'dll' not found in the dll table");
733
734 /* check state */
735 sprintf(pszQuery, "SELECT refcode FROM state WHERE refcode = %s", row1[2]);
736 rc = mysql_query(pmysql, pszQuery);
737 CheckFKError("historydll/state", "Foreign key 'state' not found in the state table");
738 }
739 mysql_free_result(pres1);
740 }
741 }
742 else
743 ulRc += logDbError(pszError, pszQuery);
744
745 /* foreign keys in historyapigroup table */
746 strcpy(pszQuery, "SELECT date, apigroup, state FROM historyapigroup");
747 rc = mysql_query(pmysql, pszQuery);
748 if (rc >= 0)
749 {
750 pres1 = mysql_store_result(pmysql);
751 if (pres1 != NULL)
752 {
753 while ((row1 = mysql_fetch_row(pres1)) != NULL)
754 {
755 /* check dll */
756 sprintf(pszQuery, "SELECT refcode FROM apigroup WHERE refcode = %s", row1[1]);
757 rc = mysql_query(pmysql, pszQuery);
758 CheckFKError("historyapigroup/apigroup", "Foreign key 'apigroup' not found in the apigroup table");
759
760 /* check state */
761 sprintf(pszQuery, "SELECT refcode FROM state WHERE refcode = %s", row1[2]);
762 rc = mysql_query(pmysql, pszQuery);
763 CheckFKError("historyapigroup/state", "Foreign key 'state' not found in the state table");
764 }
765 mysql_free_result(pres1);
766 }
767 }
768 else
769 ulRc += logDbError(pszError, pszQuery);
770
771 /* foreign keys in historydlltotal table */
772 strcpy(pszQuery, "SELECT date, dll FROM historydlltotal");
773 rc = mysql_query(pmysql, pszQuery);
774 if (rc >= 0)
775 {
776 pres1 = mysql_store_result(pmysql);
777 if (pres1 != NULL)
778 {
779 while ((row1 = mysql_fetch_row(pres1)) != NULL)
780 {
781 /* check dll */
782 sprintf(pszQuery, "SELECT refcode FROM dll WHERE refcode = %s", row1[1]);
783 rc = mysql_query(pmysql, pszQuery);
784 CheckFKError("historydlltotal/dll", "Foreign key 'dll' not found in the dll table");
785 }
786 mysql_free_result(pres1);
787 }
788 }
789 else
790 ulRc += logDbError(pszError, pszQuery);
791
792 /* foreign keys in historyapigroup table */
793 strcpy(pszQuery, "SELECT date, apigroup FROM historyapigrouptotal");
794 rc = mysql_query(pmysql, pszQuery);
795 if (rc >= 0)
796 {
797 pres1 = mysql_store_result(pmysql);
798 if (pres1 != NULL)
799 {
800 while ((row1 = mysql_fetch_row(pres1)) != NULL)
801 {
802 /* check dll */
803 sprintf(pszQuery, "SELECT refcode FROM apigroup WHERE refcode = %s", row1[1]);
804 rc = mysql_query(pmysql, pszQuery);
805 CheckFKError("historyapigrouptotal/apigroup", "Foreign key 'apigroup' not found in the apigroup table");
806 }
807 mysql_free_result(pres1);
808 }
809 }
810 else
811 ulRc += logDbError(pszError, pszQuery);
812
813 /* foreign keys in parameter table */
814 strcpy(pszQuery, "SELECT sequencenbr, function FROM parameter");
815 rc = mysql_query(pmysql, pszQuery);
816 if (rc >= 0)
817 {
818 pres1 = mysql_store_result(pmysql);
819 if (pres1 != NULL)
820 {
821 while ((row1 = mysql_fetch_row(pres1)) != NULL)
822 {
823 /* check function */
824 sprintf(pszQuery, "SELECT refcode FROM function WHERE refcode = %s", row1[1]);
825 rc = mysql_query(pmysql, pszQuery);
826 CheckFKError("parameter/function", "Foreign key 'function' not found in the function table");
827 }
828 mysql_free_result(pres1);
829 }
830 }
831 else
832 ulRc += logDbError(pszError, pszQuery);
833
834 /* Author table is special, since you should be able to interchangably reference an
835 * author by any of the following tables:
836 * name
837 * initials
838 * alias
839 * email
840 */
841 strcpy(pszQuery, "SELECT name, initials, alias, email FROM author");
842 rc = mysql_query(pmysql, pszQuery);
843 if (rc >= 0)
844 {
845 pres1 = mysql_store_result(pmysql);
846 if (pres1 != NULL)
847 {
848 while ((row1 = mysql_fetch_row(pres1)) != NULL)
849 {
850 /* check name */
851 sprintf(pszQuery, "SELECT name FROM author WHERE "
852 "initials = '%s' OR alias = '%s' OR email = '%s'",
853 row1[0], row1[0], row1[0]);
854 ulRc += CheckAuthorError(pszError, "name", row1[0], pszQuery);
855
856 /* check initials */
857 sprintf(pszQuery, "SELECT name FROM author WHERE "
858 "alias = '%s' OR email = '%s'",
859 row1[1], row1[1]);
860 ulRc += CheckAuthorError(pszError, "initials", row1[1], pszQuery);
861
862 /* alias */
863 if (row1[2] != NULL)
864 {
865 sprintf(pszQuery, "SELECT name FROM author WHERE "
866 "email = '%s'",
867 row1[2]);
868 ulRc += CheckAuthorError(pszError, "alias", row1[2], pszQuery);
869 }
870 }
871 mysql_free_result(pres1);
872 }
873 }
874 else
875 ulRc += logDbError(pszError, pszQuery);
876
877 return ulRc;
878}
879
880
881/**
882 * Checks for duplicate key and sql error for a given author key in the author table... (arg!)
883 * @returns Number of errors.
884 * @param pszError Reference to error buffer pointer.
885 * @param pszFieldName Key field name; used for logging.
886 * @param pszFieldValue Key value; used for logging
887 * @param pszQuery Query which is to be exectued to test for duplicate key.
888 * @remark Uses pszError[1] == '\xFE' to detect when to insert '\n\t'.
889 */
890static unsigned long CheckAuthorError(char * &pszError, const char *pszFieldName, const char *pszFieldValue, const char *pszQuery)
891{
892 MYSQL_ROW row;
893 MYSQL_RES *pres;
894 unsigned long ulRc = 0;
895 int rc;
896
897 rc = mysql_query(pmysql, pszQuery);
898 pres = mysql_store_result(pmysql);
899 if (rc < 0 || (pres != NULL && mysql_num_rows(pres) > 0))
900 { /* some kind of error has occurred */
901 if (pszError[1] == '\xFE')
902 {
903 strcat(pszError, "\n\t");
904 pszError += 2;
905 }
906
907 if (rc < 0) /* sql error or 'duplicate key' */
908 {
909 sprintf(pszError, "author/%s: select failed - %s (sql=%s)",
910 pszFieldName, dbGetLastErrorDesc(), pszQuery);
911 }
912 else
913 { /* 'duplicate key' - print duplicates */
914 sprintf(pszError, "author/%s: 'duplicate key', %s='%s': ",
915 pszFieldName, pszFieldValue, pszFieldName);
916
917 while ((row = mysql_fetch_row(pres)) != NULL)
918 {
919 pszError += strlen(pszError);
920 sprintf(pszError, "'%s' ", row[0]);
921 }
922 }
923
924 pszError += strlen(pszError);
925 pszError[1] = '\xFE';
926 ulRc = 1;
927 }
928 if (pres != NULL)
929 mysql_free_result(pres);
930
931 return ulRc;
932}
933
934
935/**
936 * Writes db error (rc<0) to the log buffer.
937 * @returns Number of signals.
938 * @param pszError Reference to the error buffer pointer.
939 * @param pszQuery Pointer to query which was executed.
940 * @remark Uses pszError[1] == '\xFE' to detect when to insert '\n\t'.
941 */
942static unsigned long logDbError(char * &pszError, const char *pszQuery)
943{
944 if (pszError[1] == '\xFE')
945 {
946 strcat(pszError, "\n\t");
947 pszError += 2;
948 }
949 sprintf(pszError, "select failed: %s - (sql=%s)", dbGetLastErrorDesc(), pszQuery);
950
951 pszError += strlen(pszError);
952 pszError[1] = '\xFE';
953
954 return 1;
955}
956
957
958/**
959 * Executes a give query and returns a result identifier/pointer.
960 * @returns Query result identifier/pointer. NULL on error.
961 * @param pszQuery Pointer to query.
962 * @remark Used by and designed for kHtmlPC.
963 */
964void * _System dbExecuteQuery(const char *pszQuery)
965{
966 assert(pmysql != NULL);
967 if (mysql_query(pmysql, pszQuery) >= 0)
968 return mysql_store_result(pmysql);
969
970 return NULL;
971}
972
973
974/**
975 * Asks for the number of rows in the result.
976 * @returns Number of rows in the result. -1 on error.
977 * @param pres Query result identifier/pointer.
978 * @remark Used by and designed for kHtmlPC.
979 */
980signed long _System dbQueryResultRows(void *pres)
981{
982 if (pres == NULL)
983 return -1;
984 return mysql_num_rows((MYSQL_RES*)pres);
985}
986
987
988/**
989 * Frees the storage allocated by the given result.
990 * @returns Success indicator, TRUE/FALSE.
991 * @param pres Query result identifier/pointer.
992 * @remark Used by and designed for kHtmlPC.
993 */
994BOOL _System dbFreeResult(void *pres)
995{
996 if (pres != NULL)
997 mysql_free_result((MYSQL_RES*)pres);
998 else
999 return FALSE;
1000 return TRUE;
1001}
1002
1003
1004/**
1005 * Fetch data from a result. Returns the data by calling the given callback function.
1006 * @returns Success indicator, TRUE/FALSE.
1007 * @param pres Query result identifier/pointer.
1008 * @param dbFetchCallBack Callback-function.
1009 * @param pvUser User parameter which is passed onto dbFetchCallBack.
1010 * @remark Used by and designed for kHtmlPC.
1011 */
1012BOOL _System dbFetch(void *pres, DBCALLBACKFETCH dbFetchCallBack, void *pvUser)
1013{
1014 BOOL fRc = FALSE;
1015 MYSQL_ROW row = mysql_fetch_row((MYSQL_RES*)pres);
1016
1017 if (row)
1018 {
1019 MYSQL_FIELD *pField;
1020 int i = 0;
1021 mysql_field_seek((MYSQL_RES*)pres, 0);
1022
1023 while ((pField = mysql_fetch_field((MYSQL_RES*)pres)) != NULL)
1024 if (dbFetchCallBack(row[i++], pField->name, pvUser) != 0)
1025 return FALSE;
1026
1027 fRc = TRUE;
1028 }
1029
1030 return fRc;
1031}
1032
1033
1034/**
1035 * Converts an ISO date to days after Christ, year 0.
1036 * @returns days. -1 on error;
1037 * @param pszDate ISO Date.
1038 */
1039signed long _System dbDateToDaysAfterChrist(const char *pszDate)
1040{
1041 signed long lRet = -1;
1042 char szQuery[128];
1043
1044 sprintf(&szQuery[0], "SELECT to_days('%s')", pszDate);
1045 if (mysql_query(pmysql, &szQuery[0]) >= 0)
1046 {
1047 MYSQL_ROW row;
1048 MYSQL_RES *pres = mysql_use_result(pmysql);
1049 row = mysql_fetch_row(pres);
1050 if (row != NULL)
1051 {
1052 lRet = atol(row[0]);
1053 do { row = mysql_fetch_row(pres); } while (row != NULL);
1054 }
1055 }
1056
1057 return lRet;
1058}
1059
1060
1061/**
1062 * Converts days after Christ (year 0) to ISO date.
1063 * @returns Success indicator. TRUE/FALSE;
1064 * @param lDays Days after Christ (year 0).
1065 * @param pszDate ISO Date. Result.
1066 */
1067BOOL _System dbDaysAfterChristToDate(signed long lDays, char *pszDate)
1068{
1069 BOOL fRet = FALSE;
1070 char szQuery[128];
1071
1072 if (lDays < 0)
1073 return FALSE;
1074
1075 sprintf(&szQuery[0], "SELECT from_days(%ld)", lDays);
1076 if (mysql_query(pmysql, &szQuery[0]) >= 0)
1077 {
1078 MYSQL_ROW row;
1079 MYSQL_RES *pres = mysql_use_result(pmysql);
1080 row = mysql_fetch_row(pres);
1081 if (row != NULL)
1082 {
1083 fRet = strlen(row[0]) == (4+1+2+1+2) && row[0][4] == '-' && row[0][7] == '-'
1084 && strcmp(row[0], "0000-00-00") != 0;
1085 if (fRet)
1086 strcpy(pszDate, row[0]);
1087 do { row = mysql_fetch_row(pres); } while (row != NULL);
1088 }
1089 }
1090
1091 return fRet;
1092}
1093
1094
1095#ifdef NOTDLL
1096/**
1097 * Signal handler.
1098 * Ensures that the database connection is closed at termination.
1099 * @param sig Signal number.
1100 */
1101void dbHandler(int sig)
1102{
1103 if (pmysql != NULL)
1104 {
1105 fprintf(stderr, "\n\t!disconnecting from database!\n");
1106 dbDisconnect();
1107 }
1108
1109 flushall();
1110 switch (sig)
1111 {
1112 case SIGBREAK:
1113 printf("\nSIGBREAK\n");
1114 exit(-1);
1115 break;
1116 case SIGINT:
1117 printf("\nSIGINT\n");
1118 exit(-1);
1119 break;
1120 case SIGTERM:
1121 printf("\nSIGTERM\n");
1122 exit(-1);
1123 break;
1124 case SIGSEGV:
1125 raise(sig);
1126 break;
1127 case SIGILL:
1128 printf("\nSIGILL\n");
1129 exit(-1);
1130 break;
1131 }
1132}
1133
1134
1135#else
1136/*******/
1137/* DLL */
1138/*******/
1139/* prototypes used in the _DLL_InitTerm function */
1140extern "C"
1141{
1142 int _CRT_init(void);
1143 void _CRT_term(void);
1144 void __ctordtorInit( void );
1145 void __ctordtorTerm( void );
1146 unsigned long _System _DLL_InitTerm(unsigned long hModule, unsigned long ulFlag);
1147}
1148
1149
1150/**
1151 * Dll InitTerm function.
1152 * @returns 0 on success.
1153 * 1 on error.
1154 * @param hModule
1155 * @param ulFlags
1156 * @remark We'll ensure that the database connection is terminated as we terminate.
1157 */
1158unsigned long _System _DLL_InitTerm(unsigned long hModule, unsigned long ulFlag)
1159{
1160 /*-------------------------------------------------------------------------*/
1161 /* If ulFlag is zero then the DLL is being loaded so initialization should */
1162 /* be performed. If ulFlag is 1 then the DLL is being freed so */
1163 /* termination should be performed. */
1164 /*-------------------------------------------------------------------------*/
1165
1166 switch (ulFlag)
1167 {
1168 case 0:
1169 if (_CRT_init() == -1)
1170 return 0;
1171 __ctordtorInit();
1172 break;
1173
1174 case 1:
1175 /* ensure that db connection is terminated */
1176 if (pmysql != NULL)
1177 {
1178 fprintf(stderr, "\n\t!disconnecting from database!\n");
1179 dbDisconnect();
1180 }
1181 __ctordtorTerm();
1182 break;
1183
1184 default:
1185 return 0;
1186 }
1187 hModule = hModule;
1188 return 1;
1189}
1190
1191/*****************************************************************/
1192/* -why is this terminate function referenced but not defined??? */
1193/* and where is it referenced??? */
1194/* -Probably an export missing from the libraries. */
1195/*****************************************************************/
1196void terminate(void)
1197{
1198 DosPutMessage(0, sizeof("terminate")-1, "terminate");
1199 exit(-1);
1200}
1201
1202/****************************************/
1203/* EMX run-time trouble */
1204/* _environ is missing when using -Zomf */
1205/****************************************/
1206char **_environ = environ;
1207
1208#endif
Note: See TracBrowser for help on using the repository browser.