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

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

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