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

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

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