« back to all changes in this revision
Viewing changes to src/dbops.c
- browse files at revision 7
- compare with another revision
- download diff
- download tarball
- view history from revision 7
- Committer: Bazaar Package Importer
- Author(s): Kapil Hari Paranjape
- Date: 2008-04-09 18:55:23 UTC
- mfrom: (1.1.4 upstream) (3.1.1 lenny)
- Revision ID: james.westby@ubuntu.com-20080409185523-dqokcloumyn1ibn4
Tags: 3.3.4-1
* New upstream version (3.3.4).
- Convert between iso8859-1 and utf-8 on the fly.
- Support reading of pdf using pdftohtml.
- French translation of html documentation.
- Old html documentation renamed to usersguide.
- Additional documentation on philosophy.
* debian/control:
- Changed homepage to sourcefource site.
- Moved homepage from description to its own field.
- Added "poppler-utils | xpdf-utils" to Recommends.
- Added "www-browser", "mail-reader" and "editor" to Provides.
- Removed "XS-" from Vcs-Svn tag.
- Standards-Version: 3.7.3
* debian/docs: Added new documentation files
from "doc/" subdirectory.
* debian/watch: Updated to use sourceforge site.
* debian/edbrowse.doc-base:
- Changed name of upstream provided html documentation from
"ebdoc.html" to "usersguide.html".
- Changed section from "net" to "Network/Web Browsing".
* debian/install: Compiled binary is now in "src/".
- Convert between iso8859-1 and utf-8 on the fly.
- Support reading of pdf using pdftohtml.
- French translation of html documentation.
- Old html documentation renamed to usersguide.
- Additional documentation on philosophy.
* debian/control:
- Changed homepage to sourcefource site.
- Moved homepage from description to its own field.
- Added "poppler-utils | xpdf-utils" to Recommends.
- Added "www-browser", "mail-reader" and "editor" to Provides.
- Removed "XS-" from Vcs-Svn tag.
- Standards-Version: 3.7.3
* debian/docs: Added new documentation files
from "doc/" subdirectory.
* debian/watch: Updated to use sourceforge site.
* debian/edbrowse.doc-base:
- Changed name of upstream provided html documentation from
"ebdoc.html" to "usersguide.html".
- Changed section from "net" to "Network/Web Browsing".
* debian/install: Compiled binary is now in "src/".
added
removed
src/dbops.c
1
/* dbops.c
2
* Database operations.
3
* Copyright (c) Karl Dahlke, 2008
4
* This file is part of the edbrowse project, released under GPL.
5
*/
6
7
#include "eb.h"
8
#include "dbapi.h"
9
10
const char *sql_debuglog = "ebsql.log"; /* log of debug prints */
11
const char *sql_database; /* name of current database */
12
13
char *
14
lineFormat(const char *line, ...)
15
{
16
char *s;
17
va_list p;
18
va_start(p, line);
19
s = lineFormatStack(line, 0, &p);
20
va_end(p);
21
return s;
22
} /* lineFormat */
23
24
#define LFBUFSIZE 4000
25
static char lfbuf[LFBUFSIZE]; /* line formatting buffer */
26
static const char selfref[] =
27
"@lineFormat attempts to expand within its own static buffer";
28
static const char lfoverflow[] = "@lineFormat(), line is too long, limit %d";
29
30
char *
31
lineFormatStack(const char *line, /* the sprintf-like formatting string */
32
LF * argv, /* pointer to array of values */
33
va_list * parmv_p)
34
{ /* pointers to parameters on the stack */
35
va_list parmv = 0;
36
short i, len, maxlen, len_given, flags;
37
long n;
38
double dn; /* double number */
39
char *q, pdir, inquote;
40
const char *t, *perc;
41
char fmt[12];
42
43
if(parmv_p)
44
parmv = *parmv_p;
45
if(parmv && argv || !parmv && !argv)
46
errorPrint
47
("@exactly one of the last two arguments to lineFormatStack should be null");
48
49
if(line == lfbuf) {
50
if(strchr(line, '%'))
51
errorPrint(selfref);
52
return (char *)line;
53
}
54
55
lfbuf[0] = 0;
56
q = lfbuf;
57
t = line;
58
59
while(*t) { /* more text to format */
60
/* copy up to the next % */
61
if(*t != '%' || t[1] == '%' && ++t) {
62
if(q - lfbuf >= LFBUFSIZE - 1)
63
errorPrint(lfoverflow, LFBUFSIZE);
64
*q++ = *t++;
65
continue;
66
}
67
68
/* % found */
69
perc = t++;
70
inquote = 0;
71
len = 0;
72
len_given = 0;
73
74
if(*t == '-')
75
++t;
76
for(; isdigit(*t); ++t) {
77
len_given = 1;
78
len = 10 * len + *t - '0';
79
}
80
while(*t == '.' || isdigit(*t))
81
++t;
82
pdir = *t++;
83
if(isupper(pdir)) {
84
pdir = tolower(pdir);
85
inquote = '"';
86
}
87
if(t - perc >= sizeof (fmt))
88
errorPrint("2percent directive in lineFormat too long");
89
strncpy(fmt, perc, t - perc);
90
fmt[t - perc] = 0;
91
maxlen = len;
92
if(maxlen < 11)
93
maxlen = 11;
94
95
/* get the next vararg */
96
if(pdir == 'f') {
97
if(parmv)
98
dn = va_arg(parmv, double);
99
else
100
dn = argv->f;
101
} else {
102
if(parmv)
103
n = va_arg(parmv, int);
104
else
105
n = argv->l;
106
}
107
if(argv)
108
++argv;
109
110
if(pdir == 's' && n) {
111
i = strlen((char *)n);
112
if(i > maxlen)
113
maxlen = i;
114
if(inquote && strchr((char *)n, inquote)) {
115
inquote = '\'';
116
if(strchr((char *)n, inquote))
117
errorPrint("2lineFormat() cannot put quotes around %s", n);
118
}
119
}
120
if(inquote)
121
maxlen += 2;
122
if(q + maxlen >= lfbuf + LFBUFSIZE)
123
errorPrint(lfoverflow, LFBUFSIZE);
124
125
/* check for null parameter */
126
if(pdir == 'c' && !n ||
127
pdir == 's' && isnullstring((char *)n) ||
128
pdir == 'f' && dn == nullfloat ||
129
!strchr("scf", pdir) && isnull(n)) {
130
if(!len_given) {
131
char *q1;
132
/* turn = %d to is null */
133
for(q1 = q - 1; q1 >= lfbuf && *q1 == ' '; --q1) ;
134
if(q1 >= lfbuf && *q1 == '=') {
135
if(q1 > lfbuf && q1[-1] == '!') {
136
strcpy(q1 - 1, "IS NOT ");
137
q = q1 + 6;
138
} else {
139
strcpy(q1, "IS ");
140
q = q1 + 3;
141
}
142
}
143
strcpy(q, "NULL");
144
q += 4;
145
continue;
146
} /* null with no length specified */
147
pdir = 's';
148
n = (int)"";
149
}
150
/* parameter is null */
151
if(inquote)
152
*q++ = inquote;
153
fmt[t - perc - 1] = pdir;
154
switch (pdir) {
155
case 'i':
156
flags = DTDELIMIT;
157
if(len) {
158
if(len >= 11)
159
flags |= DTAMPM;
160
if(len < 8)
161
flags = DTDELIMIT | DTCRUNCH;
162
if(len < 5)
163
flags = DTCRUNCH;
164
}
165
strcpy(q, timeString(n, flags));
166
break;
167
case 'a':
168
flags = DTDELIMIT;
169
if(len) {
170
if(len < 10)
171
flags = DTCRUNCH | DTDELIMIT;
172
if(len < 8)
173
flags = DTCRUNCH;
174
if(len == 5)
175
flags = DTCRUNCH | DTDELIMIT;
176
}
177
strcpy(q, dateString(n, flags));
178
if(len == 4 || len == 5)
179
q[len] = 0;
180
break;
181
case 'm':
182
strcpy(q, moneyString(n));
183
break;
184
case 'f':
185
sprintf(q, fmt, dn);
186
break;
187
case 's':
188
if(n == (int)lfbuf)
189
errorPrint(selfref);
190
/* extra code to prevent %09s from printing out all zeros
191
when the argument is null (empty string) */
192
if(!*(char *)n && fmt[1] == '0')
193
strcpy(fmt + 1, fmt + 2);
194
/* fall through */
195
default:
196
sprintf(q, fmt, n);
197
} /* switch */
198
q += strlen(q);
199
if(inquote)
200
*q++ = inquote;
201
} /* loop printing pieces of the string */
202
203
*q = 0; /* null terminate */
204
205
/* we relie on the calling function to invoke va_end(), since the arg list
206
is not always the ... varargs of a function, though it usually is.
207
See lineFormat() above for a typical example.
208
Note that the calling function may wish to process additional arguments
209
before calling va_end. */
210
211
if(parmv)
212
*parmv_p = parmv;
213
return lfbuf;
214
} /* lineFormatStack */
215
216
/* given a datatype, return the character that, when appended to %,
217
causes lineFormat() to print the data element properly. */
218
static char
219
sprintfChar(char datatype)
220
{
221
char c;
222
switch (datatype) {
223
case 'S':
224
c = 's';
225
break;
226
case 'C':
227
c = 'c';
228
break;
229
case 'M':
230
case 'N':
231
c = 'd';
232
break;
233
case 'D':
234
c = 'a';
235
break;
236
case 'I':
237
c = 'i';
238
break;
239
case 'F':
240
c = 'f';
241
break;
242
case 'B':
243
case 'T':
244
c = 'd';
245
break;
246
default:
247
c = 0;
248
} /* switch */
249
return c;
250
} /* sprintfChar */
251
252
/*********************************************************************
253
Using the values just fetched or selected, build a line in unload format.
254
All fields are expanded into ascii, with pipes between them.
255
Conversely, given a line of pipe separated fields,
256
put them back into binary, ready for retsCopy().
257
*********************************************************************/
258
259
char *
260
sql_mkunld(char delim)
261
{
262
char fmt[NUMRETS * 4 + 1];
263
int i;
264
char pftype;
265
266
for(i = 0; i < rv_numRets; ++i) {
267
pftype = sprintfChar(rv_type[i]);
268
if(!pftype)
269
errorPrint("2sql_mkunld cannot convert datatype %c", rv_type[i]);
270
sprintf(fmt + 4 * i, "%%0%c%c", pftype, delim);
271
} /* loop over returns */
272
273
return lineFormatStack(fmt, rv_data, 0);
274
} /* sql_mkunld */
275
276
/* like the above, but we build a comma-separated list with quotes,
277
ready for SQL insert or update.
278
You might be tempted to call this routine first, obtaining a string,
279
and then call lineFormat("insert into foo values(%s)",
280
but don't do that!
281
The returned string is built by lineFormat and is already in the buffer.
282
You instead need to make a copy of the string and then call lineFormat. */
283
char *
284
sql_mkinsupd()
285
{
286
char fmt[NUMRETS * 3 + 1];
287
int i;
288
char pftype;
289
290
for(i = 0; i < rv_numRets; ++i) {
291
pftype = sprintfChar(rv_type[i]);
292
if(!pftype)
293
errorPrint("2sql_mkinsupd cannot convert datatype %c", rv_type[i]);
294
if(pftype != 'd' && pftype != 'f')
295
pftype = toupper(pftype);
296
sprintf(fmt + 3 * i, "%%%c,", pftype);
297
} /* loop over returns */
298
fmt[3 * i - 1] = 0;
299
300
return lineFormatStack(fmt, rv_data, 0);
301
} /* sql_mkinsupd */
302
303
304
/*********************************************************************
305
Date time functions.
306
*********************************************************************/
307
308
static char ndays[] = { 0, 31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
309
310
bool
311
isLeapYear(int year)
312
{
313
if(year % 4)
314
return false;
315
if(year % 100)
316
return true;
317
if(year % 400)
318
return false;
319
return true;
320
} /* isLeapYear */
321
322
/* convert year, month, and day into a date. */
323
/* return -1 = bad year, -2 = bad month, -3 = bad day */
324
date
325
dateEncode(int year, int month, int day)
326
{
327
short i;
328
long d;
329
if((year | month | day) == 0)
330
return nullint;
331
if(year < 1640 || year > 3000)
332
return -1;
333
if(month <= 0 || month > 12)
334
return -2;
335
if(day <= 0 || day > ndays[month])
336
return -3;
337
if(day == 29 && month == 2 && !isLeapYear(year))
338
return -3;
339
--year;
340
d = year * 365L + year / 4 - year / 100 + year / 400;
341
for(i = 1; i < month; ++i)
342
d += ndays[i];
343
++year;
344
if(month > 2 && !isLeapYear(year))
345
--d;
346
d += (day - 1);
347
d -= 598632;
348
return d;
349
} /* dateEncode */
350
351
/* convert a date back into year, month, and day */
352
/* the inverse of the above */
353
void
354
dateDecode(date d, int *yp, int *mp, int *dp)
355
{
356
int year, month, day;
357
year = month = day = 0;
358
if(d >= 0 && d <= 497094) {
359
/* how many years have rolled by; at worst 366 days in each */
360
year = d / 366;
361
year += 1640;
362
while(dateEncode(++year, 1, 1) <= d) ;
363
--year;
364
d -= dateEncode(year, 1, 1);
365
if(!isLeapYear(year))
366
ndays[2] = 28;
367
for(month = 1; month <= 12; ++month) {
368
if(d < ndays[month])
369
break;
370
d -= ndays[month];
371
}
372
day = d + 1;
373
ndays[2] = 29; /* put it back */
374
}
375
*yp = year;
376
*mp = month;
377
*dp = day;
378
} /* dateDecode */
379
380
/* convert a string into a date */
381
/* return -4 for bad format */
382
date
383
stringDate(const char *s, bool yearfirst)
384
{
385
short year, month, day, i, l;
386
char delim;
387
char buf[12];
388
char *t;
389
390
if(!s)
391
return nullint;
392
l = strlen(s);
393
while(l && s[l - 1] == ' ')
394
--l;
395
if(!l)
396
return nullint;
397
if(l != 8 && l != 10)
398
return -4;
399
strncpy(buf, s, l);
400
buf[l] = 0;
401
delim = yearfirst ? '-' : '/';
402
t = strchr(buf, delim);
403
if(t)
404
strcpy(t, t + 1);
405
t = strchr(buf, delim);
406
if(t)
407
strcpy(t, t + 1);
408
l = strlen(buf);
409
if(l != 8)
410
return -4;
411
if(!strcmp(buf, " "))
412
return nullint;
413
if(yearfirst) {
414
char swap[4];
415
strncpy(swap, buf, 4);
416
strncpy(buf, buf + 4, 4);
417
strncpy(buf + 4, swap, 4);
418
}
419
for(i = 0; i < 8; ++i)
420
if(!isdigit(buf[i]))
421
return -4;
422
month = 10 * (buf[0] - '0') + buf[1] - '0';
423
day = 10 * (buf[2] - '0') + buf[3] - '0';
424
year = atoi(buf + 4);
425
return dateEncode(year, month, day);
426
} /* stringDate */
427
428
/* convert a date into a string, held in a static buffer */
429
/* cram squashes out the century, delimit puts in slashes */
430
char *
431
dateString(date d, int flags)
432
{
433
static char buf[12];
434
char swap[8];
435
int year, month, day;
436
dateDecode(d, &year, &month, &day);
437
if(!year)
438
strcpy(buf, " / / ");
439
else
440
sprintf(buf, "%02d/%02d/%04d", month, day, year);
441
if(flags & DTCRUNCH)
442
strcpy(buf + 6, buf + 8);
443
if(flags & YEARFIRST) {
444
strncpy(swap, buf, 6);
445
swap[2] = swap[5] = 0;
446
strcpy(buf, buf + 6);
447
if(flags & DTDELIMIT)
448
strcat(buf, "-");
449
strcat(buf, swap);
450
if(flags & DTDELIMIT)
451
strcat(buf, "-");
452
strcat(buf, swap + 3);
453
} else if(!(flags & DTDELIMIT)) {
454
char *s;
455
s = strchr(buf, '/');
456
strcpy(s, s + 1);
457
s = strchr(buf, '/');
458
strcpy(s, s + 1);
459
}
460
return buf;
461
} /* dateString */
462
463
char *
464
timeString(interval seconds, int flags)
465
{
466
short h, m, s;
467
char c = 'A';
468
static char buf[12];
469
if(seconds < 0 || seconds >= 86400)
470
strcpy(buf, " : : AM");
471
else {
472
h = seconds / 3600;
473
seconds -= h * 3600L;
474
m = seconds / 60;
475
seconds -= m * 60;
476
s = (short)seconds;
477
if(flags & DTAMPM) {
478
if(h == 0)
479
h = 12;
480
else if(h >= 12) {
481
c = 'P';
482
if(h > 12)
483
h -= 12;
484
}
485
}
486
sprintf(buf, "%02d:%02d:%02d %cM", h, m, s, c);
487
}
488
if(!(flags & DTAMPM))
489
buf[8] = 0;
490
if(flags & DTCRUNCH)
491
strcpy(buf + 5, buf + 8);
492
if(!(flags & DTDELIMIT)) {
493
strcpy(buf + 2, buf + 3);
494
if(buf[4] == ':')
495
strcpy(buf + 4, buf + 5);
496
}
497
return buf;
498
} /* timeString */
499
500
/* convert string into time.
501
* Like stringDate, we can return bad hour, bad minute, bad second, or bad format */
502
interval
503
stringTime(const char *t)
504
{
505
short h, m, s;
506
bool ampm = false;
507
char c;
508
char buf[12];
509
short i, l;
510
if(!t)
511
return nullint;
512
l = strlen(t);
513
while(l && t[l - 1] == ' ')
514
--l;
515
if(!l)
516
return nullint;
517
if(l < 4 || l > 11)
518
return -4;
519
strncpy(buf, t, l);
520
buf[l] = 0;
521
if(buf[l - 1] == 'M' && buf[l - 3] == ' ') {
522
ampm = true;
523
c = buf[l - 2];
524
if(c != 'A' && c != 'P')
525
return -4;
526
buf[l - 3] = 0;
527
l -= 3;
528
}
529
if(l < 4 || l > 8)
530
return -4;
531
if(buf[2] == ':')
532
strcpy(buf + 2, buf + 3);
533
if(buf[4] == ':')
534
strcpy(buf + 4, buf + 5);
535
l = strlen(buf);
536
if(l != 4 && l != 6)
537
return -4;
538
if(!strncmp(buf, " ", l))
539
return nullint;
540
for(i = 0; i < l; ++i)
541
if(!isdigit(buf[i]))
542
return -4;
543
h = 10 * (buf[0] - '0') + buf[1] - '0';
544
m = 10 * (buf[2] - '0') + buf[3] - '0';
545
s = 0;
546
if(l == 6)
547
s = 10 * (buf[4] - '0') + buf[5] - '0';
548
if(ampm) {
549
if(h == 12) {
550
if(c == 'A')
551
h = 0;
552
} else if(c == 'P')
553
h += 12;
554
}
555
if(h < 0 || h >= 24)
556
return -1;
557
if(m < 0 || m >= 60)
558
return -2;
559
if(s < 0 || s >= 60)
560
return -3;
561
return h * 3600L + m * 60 + s;
562
} /* stringTime */
563
564
char *
565
moneyString(money m)
566
{
567
static char buf[20], *s = buf;
568
if(m == nullint)
569
return "";
570
if(m < 0)
571
*s++ = '-', m = -m;
572
sprintf(s, "$%ld.%02d", m / 100, (int)(m % 100));
573
return buf;
574
} /* moneyString */
575
576
money
577
stringMoney(const char *s)
578
{
579
short sign = 1;
580
long m;
581
double d;
582
if(!s)
583
return nullint;
584
skipWhite(&s);
585
if(*s == '-')
586
sign = -sign, ++s;
587
skipWhite(&s);
588
if(*s == '$')
589
++s;
590
skipWhite(&s);
591
if(!*s)
592
return nullint;
593
if(!stringIsFloat(s, &d))
594
return -nullint;
595
m = (long)(d * 100.0 + 0.5);
596
return m * sign;
597
} /* stringMoney */
598
599
/* Make sure edbrowse is connected to the database */
600
static bool
601
ebConnect(void)
602
{
603
const short exclist[] = { EXCSQLMISC, EXCNOCONNECT, 0 };
604
if(sql_database)
605
return true;
606
if(!dbarea) {
607
setError(MSG_DBUnspecified);
608
return false;
609
}
610
sql_exclist(exclist);
611
sql_connect(dbarea, dblogin, dbpw);
612
if(rv_lastStatus) {
613
setError(MSG_DBConnect, rv_vendorStatus);
614
return false;
615
}
616
if(!sql_database)
617
errorPrint("@sql connected, but database not set");
618
return true;
619
} /* ebConnect */
620
621
void
622
dbClose(void)
623
{
624
sql_disconnect();
625
} /* dbClose */
626
627
static char myTab[64];
628
static const char *myWhere;
629
static char *scl; /* select clause */
630
static int scllen;
631
static char *wcl; /* where clause */
632
static int wcllen;
633
static char wherecol[COLNAMELEN + 2];
634
static struct DBTABLE *td;
635
636
static void
637
unexpected(void)
638
{
639
setError(MSG_DBUnexpected, rv_vendorStatus);
640
} /* unexpected */
641
642
static void
643
buildSelectClause(void)
644
{
645
int i;
646
scl = initString(&scllen);
647
stringAndString(&scl, &scllen, "select ");
648
for(i = 0; i < td->ncols; ++i) {
649
if(i)
650
stringAndChar(&scl, &scllen, ',');
651
stringAndString(&scl, &scllen, td->cols[i]);
652
}
653
stringAndString(&scl, &scllen, " from ");
654
stringAndString(&scl, &scllen, td->name);
655
} /* buildSelectClause */
656
657
static bool
658
buildWhereClause(void)
659
{
660
int i, l, n;
661
const char *w = myWhere;
662
const char *e;
663
664
wcl = initString(&wcllen);
665
wherecol[0] = 0;
666
if(stringEqual(w, "*"))
667
return true;
668
669
e = strchr(w, '=');
670
if(!e) {
671
if(!td->key1) {
672
setError(MSG_DBNoKey);
673
return false;
674
}
675
e = td->cols[td->key1 - 1];
676
l = strlen(e);
677
if(l > COLNAMELEN) {
678
setError(MSG_DBColumnLong, e, COLNAMELEN);
679
return false;
680
}
681
strcpy(wherecol, e);
682
e = w - 1;
683
} else if(isdigit(*w)) {
684
n = strtol(w, (char **)&w, 10);
685
if(w != e) {
686
setError(MSG_DBSyntax);
687
return false;
688
}
689
if(n == 0 || n > td->ncols) {
690
setError(MSG_DBColRange, n);
691
return false;
692
}
693
goto setcol_n;
694
} else {
695
n = 0;
696
if(e - w <= COLNAMELEN) {
697
strncpy(wherecol, w, e - w);
698
wherecol[e - w] = 0;
699
for(i = 0; i < td->ncols; ++i) {
700
if(!strstr(td->cols[i], wherecol))
701
continue;
702
if(n) {
703
setError(MSG_DBManyColumns, wherecol);
704
return false;
705
}
706
n = i + 1;
707
}
708
}
709
if(!n) {
710
setError(MSG_DBNoColumn, wherecol);
711
return false;
712
}
713
setcol_n:
714
w = td->cols[n - 1];
715
l = strlen(w);
716
if(l > COLNAMELEN) {
717
setError(MSG_DBColumnLong, w, COLNAMELEN);
718
return false;
719
}
720
strcpy(wherecol, w);
721
}
722
723
stringAndString(&wcl, &wcllen, "where ");
724
stringAndString(&wcl, &wcllen, wherecol);
725
++e;
726
w = e;
727
if(!*e) {
728
stringAndString(&wcl, &wcllen, " is null");
729
} else if((i = strtol(e, (char **)&e, 10)) >= 0 &&
730
*e == '-' && (n = strtol(e + 1, (char **)&e, 10)) >= 0 && *e == 0) {
731
stringAndString(&wcl, &wcllen, " between ");
732
stringAndNum(&wcl, &wcllen, i);
733
stringAndString(&wcl, &wcllen, " and ");
734
stringAndNum(&wcl, &wcllen, n);
735
} else if(w[strlen(w) - 1] == '*') {
736
stringAndString(&wcl, &wcllen, lineFormat(" matches %S", w));
737
} else {
738
stringAndString(&wcl, &wcllen, lineFormat(" = %S", w));
739
}
740
741
return true;
742
} /* buildWhereClause */
743
744
static bool
745
setTable(void)
746
{
747
static const short exclist[] = { EXCNOTABLE, EXCNOCOLUMN, EXCSQLMISC, 0 };
748
int cid, nc, i, part1, part2;
749
const char *s = cw->fileName;
750
const char *t = strchr(s, ']');
751
if(t - s >= sizeof (myTab))
752
errorPrint("2table name too long, limit %d characters",
753
sizeof (myTab) - 4);
754
strncpy(myTab, s, t - s);
755
myTab[t - s] = 0;
756
myWhere = t + 1;
757
758
td = cw->table;
759
if(td)
760
return true;
761
762
/* haven't glommed onto this table yet */
763
td = findTableDescriptor(myTab);
764
if(td) {
765
if(!td->types) {
766
buildSelectClause();
767
sql_exclist(exclist);
768
cid = sql_prepare(scl);
769
nzFree(scl);
770
if(rv_lastStatus) {
771
if(rv_lastStatus == EXCNOTABLE)
772
setError(MSG_DBNoTable, td->name);
773
else if(rv_lastStatus == EXCNOCOLUMN)
774
setError(MSG_DBBadColumn);
775
else
776
unexpected();
777
return false;
778
}
779
td->types = cloneString(rv_type);
780
sql_free(cid);
781
}
782
783
} else {
784
785
sql_exclist(exclist);
786
cid = sql_prepare("select * from %s", myTab);
787
if(rv_lastStatus) {
788
if(rv_lastStatus == EXCNOTABLE)
789
setError(MSG_DBNoTable, myTab);
790
else
791
unexpected();
792
return false;
793
}
794
td = newTableDescriptor(myTab);
795
if(!td) {
796
sql_free(cid);
797
return false;
798
}
799
nc = rv_numRets;
800
if(nc > MAXTCOLS) {
801
printf("warning, only the first %d columns will be selected\n",
802
MAXTCOLS);
803
nc = MAXTCOLS;
804
}
805
td->types = cloneString(rv_type);
806
td->types[nc] = 0;
807
td->ncols = nc;
808
for(i = 0; i < nc; ++i)
809
td->cols[i] = cloneString(rv_name[i]);
810
sql_free(cid);
811
812
getPrimaryKey(myTab, &part1, &part2);
813
if(part1 > nc)
814
part1 = 0;
815
if(part2 > nc)
816
part2 = 0;
817
td->key1 = part1;
818
td->key2 = part2;
819
}
820
821
s = strpbrk(td->types, "BT");
822
if(s)
823
s = strpbrk(s + 1, "BT");
824
if(s) {
825
setError(MSG_DBManyBlobs);
826
return false;
827
}
828
829
cw->table = td;
830
return true;
831
} /* setTable */
832
833
void
834
showColumns(void)
835
{
836
char c;
837
const char *desc;
838
int i;
839
840
if(!setTable())
841
return;
842
printf("table %s", td->name);
843
if(!stringEqual(td->name, td->shortname))
844
printf(" [%s]", td->shortname);
845
i = sql_selectOne("select count(*) from %s", td->name);
846
printf(", %d rows\n", i);
847
848
for(i = 0; i < td->ncols; ++i) {
849
printf("%d ", i + 1);
850
if(td->key1 == i + 1 || td->key2 == i + 1)
851
printf("*");
852
printf("%s ", td->cols[i]);
853
c = td->types[i];
854
switch (c) {
855
case 'N':
856
desc = "int";
857
break;
858
case 'D':
859
desc = "date";
860
break;
861
case 'I':
862
desc = "time";
863
break;
864
case 'M':
865
desc = "money";
866
break;
867
case 'F':
868
desc = "float";
869
break;
870
case 'S':
871
desc = "string";
872
break;
873
case 'C':
874
desc = "char";
875
break;
876
case 'B':
877
desc = "blob";
878
break;
879
case 'T':
880
desc = "text";
881
break;
882
default:
883
desc = "?";
884
break;
885
} /* switch */
886
printf("%s\n", desc);
887
}
888
} /* showColumns */
889
890
bool
891
sqlReadRows(const char *filename, char **bufptr)
892
{
893
int cid;
894
char *rbuf, *unld, *s;
895
int rlen;
896
897
*bufptr = EMPTYSTRING;
898
if(!ebConnect())
899
return false;
900
if(!setTable())
901
return false;
902
903
rbuf = initString(&rlen);
904
myWhere = strchr(filename, ']') + 1;
905
if(*myWhere) {
906
if(!buildWhereClause())
907
return false;
908
buildSelectClause();
909
rv_blobFile = 0;
910
cid = sql_prepOpen("%s %0s", scl, wcl);
911
nzFree(scl);
912
nzFree(wcl);
913
while(sql_fetchNext(cid, 0)) {
914
unld = sql_mkunld('\177');
915
if(strchr(unld, '|')) {
916
setError(MSG_DBPipes);
917
goto abort;
918
}
919
if(strchr(unld, '\n')) {
920
setError(MSG_DBNewline);
921
goto abort;
922
}
923
for(s = unld; *s; ++s)
924
if(*s == '\177')
925
*s = '|';
926
s[-1] = '\n'; /* overwrite the last pipe */
927
928
/* look for blob column */
929
if(s = strpbrk(td->types, "BT")) {
930
int bfi = s - td->types; /* blob field index */
931
int cx = 0; /* context, where to put the blob */
932
int j;
933
char *u, *v, *end;
934
u = unld;
935
for(j = 0; j < bfi; ++j)
936
u = strchr(u, '|') + 1;
937
v = strpbrk(u, "|\n");
938
end = v + strlen(v);
939
if(rv_blobSize) {
940
cx = sideBuffer(0, rv_blobLoc, rv_blobSize, 0, false);
941
nzFree(rv_blobLoc);
942
}
943
sprintf(myTab, "<%d>", cx);
944
if(!cx)
945
myTab[0] = 0;
946
j = strlen(myTab);
947
/* unld is pretty long; I'm just going to assume there is enough room for this */
948
memmove(u + j, v, end - v);
949
u[j + (end - v)] = 0;
950
memcpy(u, myTab, j);
951
}
952
953
stringAndString(&rbuf, &rlen, unld);
954
}
955
sql_closeFree(cid);
956
}
957
958
*bufptr = rbuf;
959
return true;
960
961
abort:
962
nzFree(rbuf);
963
sql_closeFree(cid);
964
return false;
965
} /* sqlReadRows */
966
967
static char *lineFields[MAXTCOLS];
968
969
/* Split a line at pipe boundaries, and make sure the field count is correct */
970
static bool
971
intoFields(char *line)
972
{
973
char *s = line;
974
int j = 0;
975
int c;
976
977
while(1) {
978
lineFields[j] = s;
979
s = strpbrk(s, "|\n");
980
c = *s;
981
*s++ = 0;
982
++j;
983
if(c == '\n')
984
break;
985
if(j < td->ncols)
986
continue;
987
setError(MSG_DBAddField);
988
return false;
989
}
990
991
if(j == td->ncols)
992
return true;
993
setError(MSG_DBLostField);
994
return false;
995
} /* intoFields */
996
997
static bool
998
rowCountCheck(int action, int cnt1)
999
{
1000
int cnt2 = rv_lastNrows;
1001
1002
if(cnt1 == cnt2)
1003
return true;
1004
1005
setError(MSG_DBDeleteCount + action, cnt1, cnt2);
1006
return false;
1007
} /* rowCountCheck */
1008
1009
static int
1010
keyCountCheck(void)
1011
{
1012
if(!td->key1) {
1013
setError(MSG_DBNoKeyCol);
1014
return false;
1015
}
1016
return (td->key2 ? 2 : 1);
1017
} /* keyCountCheck */
1018
1019
/* Typical error conditions for insert update delete */
1020
static const short insupdExceptions[] = { EXCSQLMISC,
1021
EXCVIEWUSE, EXCREFINT, EXCITEMLOCK, EXCPERMISSION,
1022
EXCDEADLOCK, EXCCHECK, EXCTIMEOUT, EXCNOTNULLCOLUMN, 0
1023
};
1024
1025
static bool
1026
insupdError(int action, int rcnt)
1027
{
1028
int rc = rv_lastStatus;
1029
int msg;
1030
1031
if(rc) {
1032
switch (rc) {
1033
case EXCVIEWUSE:
1034
msg = MSG_DBView;
1035
break;
1036
case EXCREFINT:
1037
msg = MSG_DBRefInt;
1038
break;
1039
case EXCITEMLOCK:
1040
msg = MSG_DBLocked;
1041
break;
1042
case EXCPERMISSION:
1043
msg = MSG_DBPerms;
1044
break;
1045
case EXCDEADLOCK:
1046
msg = MSG_DBDeadlock;
1047
break;
1048
case EXCNOTNULLCOLUMN:
1049
msg = MSG_DBNotNull;
1050
break;
1051
case EXCCHECK:
1052
msg = MSG_DBCheck;
1053
break;
1054
case EXCTIMEOUT:
1055
msg = MSG_DBTimeout;
1056
break;
1057
default:
1058
setError(MSG_DBMisc, rv_vendorStatus);
1059
return false;
1060
}
1061
1062
setError(msg);
1063
return false;
1064
}
1065
1066
return rowCountCheck(action, rcnt);
1067
} /* insupdError */
1068
1069
bool
1070
sqlDelRows(int start, int end)
1071
{
1072
int nkeys, ndel, key1, key2, ln;
1073
1074
if(!setTable())
1075
return false;
1076
1077
nkeys = keyCountCheck();
1078
key1 = td->key1 - 1;
1079
key2 = td->key2 - 1;
1080
if(!nkeys)
1081
return false;
1082
1083
ndel = end - start + 1;
1084
ln = start;
1085
if(ndel > 100) {
1086
setError(MSG_DBMassDelete);
1087
return false;
1088
}
1089
1090
/* We could delete all the rows with one statement, using an in(list),
1091
* but that won't work when the key is two columns.
1092
* I have to write the one-line-at-a-time code anyways,
1093
* I'll just use that for now. */
1094
while(ndel--) {
1095
char *line = (char *)fetchLine(ln, 0);
1096
intoFields(line);
1097
sql_exclist(insupdExceptions);
1098
if(nkeys == 1)
1099
sql_exec("delete from %s where %s = %S",
1100
td->name, td->cols[key1], lineFields[key1]);
1101
else
1102
sql_exec("delete from %s where %s = %S and %s = %S",
1103
td->name, td->cols[key1], lineFields[key1],
1104
td->cols[key2], lineFields[key2]);
1105
nzFree(line);
1106
if(!insupdError(0, 1))
1107
return false;
1108
delText(ln, ln);
1109
}
1110
1111
return true;
1112
} /* sqlDelRows */
1113
1114
bool
1115
sqlUpdateRow(pst source, int slen, pst dest, int dlen)
1116
{
1117
char *d2; /* clone of dest */
1118
char *s, *t;
1119
int j, l1, l2, nkeys, key1, key2;
1120
char *u1, *u2; /* pieces of the update statement */
1121
int u1len, u2len;
1122
1123
/* compare all the way out to newline, so we know both strings end at the same time */
1124
if(slen == dlen && !memcmp(source, dest, slen + 1))
1125
return true;
1126
1127
if(!setTable())
1128
return false;
1129
1130
nkeys = keyCountCheck();
1131
key1 = td->key1 - 1;
1132
key2 = td->key2 - 1;
1133
if(!nkeys)
1134
return false;
1135
1136
d2 = (char *)clonePstring(dest);
1137
if(!intoFields(d2)) {
1138
nzFree(d2);
1139
return false;
1140
}
1141
1142
j = 0;
1143
u1 = initString(&u1len);
1144
u2 = initString(&u2len);
1145
s = (char *)source;
1146
1147
while(1) {
1148
t = strpbrk(s, "|\n");
1149
l1 = t - s;
1150
l2 = strlen(lineFields[j]);
1151
if(l1 != l2 || memcmp(s, lineFields[j], l1)) {
1152
if(j == key1 || j == key2) {
1153
setError(MSG_DBChangeKey);
1154
goto abort;
1155
}
1156
if(td->types[j] == 'B') {
1157
setError(MSG_DBChangeBlob);
1158
goto abort;
1159
}
1160
if(td->types[j] == 'T') {
1161
setError(MSG_DBChangeText);
1162
goto abort;
1163
}
1164
if(*u1)
1165
stringAndChar(&u1, &u1len, ',');
1166
stringAndString(&u1, &u1len, td->cols[j]);
1167
if(*u2)
1168
stringAndChar(&u2, &u2len, ',');
1169
stringAndString(&u2, &u2len, lineFormat("%S", lineFields[j]));
1170
}
1171
if(*t == '\n')
1172
break;
1173
s = t + 1;
1174
++j;
1175
}
1176
1177
sql_exclist(insupdExceptions);
1178
if(nkeys == 1)
1179
sql_exec("update %s set(%s) = (%s) where %s = %S",
1180
td->name, u1, u2, td->cols[key1], lineFields[key1]);
1181
else
1182
sql_exec("update %s set(%s) = (%s) where %s = %S and %s = %S",
1183
td->name, u1, u2,
1184
td->cols[key1], lineFields[key1], td->cols[key2], lineFields[key2]);
1185
if(!insupdError(2, 1))
1186
goto abort;
1187
1188
nzFree(d2);
1189
nzFree(u1);
1190
nzFree(u2);
1191
return true;
1192
1193
abort:
1194
nzFree(d2);
1195
nzFree(u1);
1196
nzFree(u2);
1197
return false;
1198
} /* sqlUpdateRow */
1199
1200
bool
1201
sqlAddRows(int ln)
1202
{
1203
char *u1, *u2; /* pieces of the insert statement */
1204
char *unld, *s;
1205
int u1len, u2len;
1206
int j, l, rowid;
1207
double dv;
1208
char inp[256];
1209
1210
if(!setTable())
1211
return false;
1212
1213
while(1) {
1214
u1 = initString(&u1len);
1215
u2 = initString(&u2len);
1216
for(j = 0; j < td->ncols; ++j) {
1217
reenter:
1218
if(strchr("BT", td->types[j]))
1219
continue;
1220
printf("%s: ", td->cols[j]);
1221
fflush(stdout);
1222
if(!fgets(inp, sizeof (inp), stdin)) {
1223
puts("EOF");
1224
ebClose(1);
1225
}
1226
l = strlen(inp);
1227
if(l && inp[l - 1] == '\n')
1228
inp[--l] = 0;
1229
if(stringEqual(inp, ".")) {
1230
nzFree(u1);
1231
nzFree(u2);
1232
return true;
1233
}
1234
1235
/* For now, a null field is always excepted. */
1236
/* Someday we may want to check this against the not-null constraint. */
1237
if(inp[0] == 0)
1238
goto goodfield;
1239
1240
/* verify the integrity of the entered field */
1241
if(strchr(inp, '|')) {
1242
puts("please, no pipes in the data");
1243
goto reenter;
1244
}
1245
1246
switch (td->types[j]) {
1247
case 'N':
1248
s = inp;
1249
if(*s == '-')
1250
++s;
1251
if(stringIsNum(s) < 0) {
1252
puts("number expected");
1253
goto reenter;
1254
}
1255
break;
1256
case 'F':
1257
if(!stringIsFloat(inp, &dv)) {
1258
puts("decimal number expected");
1259
goto reenter;
1260
}
1261
break;
1262
case 'C':
1263
if(strlen(inp) > 1) {
1264
puts("one character expected");
1265
goto reenter;
1266
}
1267
break;
1268
case 'D':
1269
if(stringDate(inp, false) < 0) {
1270
puts("date expected");
1271
goto reenter;
1272
}
1273
break;
1274
case 'I':
1275
if(stringTime(inp) < 0) {
1276
puts("time expected");
1277
goto reenter;
1278
}
1279
break;
1280
}
1281
1282
goodfield:
1283
if(*u1)
1284
stringAndChar(&u1, &u1len, ',');
1285
stringAndString(&u1, &u1len, td->cols[j]);
1286
if(*u2)
1287
stringAndChar(&u2, &u2len, ',');
1288
stringAndString(&u2, &u2len, lineFormat("%S", inp));
1289
}
1290
sql_exclist(insupdExceptions);
1291
sql_exec("insert into %s (%s) values (%s)", td->name, u1, u2);
1292
nzFree(u1);
1293
nzFree(u2);
1294
if(!insupdError(1, 1)) {
1295
printf("Error: ");
1296
showError();
1297
continue;
1298
}
1299
/* Fetch the row just entered;
1300
its serial number may have changed from 0 to something real */
1301
rowid = rv_lastRowid;
1302
buildSelectClause();
1303
sql_select("%s where rowid = %d", scl, rowid, 0);
1304
nzFree(scl);
1305
unld = sql_mkunld('|');
1306
l = strlen(unld);
1307
unld[l - 1] = '\n'; /* overwrite the last pipe */
1308
if(!addTextToBuffer((pst) unld, l, ln))
1309
return false;
1310
++ln;
1311
}
1312
1313
/* This pointis not reached; make the compilerhappy */
1314
return true;
1315
} /* sqlAddRows */
1316
1317
1318
/*********************************************************************
1319
Sync up two tables, or corresponding sections of two tables.
1320
These are usually equischema tables in parallel databases or machines.
1321
This isn't used by edbrowse; it's just something I wrote,
1322
and I thought you might find it useful.
1323
It follows the C convention of copying the second argument
1324
to the first, like the string and memory functions,
1325
rather than the shell convention of copying (cp) the first argument to the second.
1326
Hey - why have one standard, when you can have two?
1327
*********************************************************************/
1328
1329
static const char *synctable; /* table being sync-ed */
1330
static const char *synckeycol; /* key column */
1331
static const char *sync_clause; /* additional clause, to sync only part of the table */
1332
1333
static int
1334
syncup_comm_fn(char action, char *line1, char *line2, int key)
1335
{
1336
switch (action) {
1337
case '<': /* delete */
1338
sql_exec("delete from %s where %s = %d %0s",
1339
synctable, synckeycol, key, sync_clause);
1340
break;
1341
case '>': /* insert */
1342
sql_exec("insert into %s values(%s)", synctable, line2);
1343
break;
1344
case '*': /* update */
1345
sql_exec("update %s set * = (%s) where %s = %d %0s",
1346
synctable, line2, synckeycol, key, sync_clause);
1347
break;
1348
} /* switch */
1349
return 0;
1350
} /* syncup_comm_fn */
1351
1352
/* make table1 look like table2 */
1353
void
1354
syncup_table(const char *table1, const char *table2, /* the two tables */
1355
const char *keycol, /* the key column */
1356
const char *otherclause)
1357
{
1358
char stmt1[200], stmt2[200];
1359
int len;
1360
1361
synctable = table1;
1362
synckeycol = keycol;
1363
sync_clause = otherclause;
1364
len = strlen(table1);
1365
if((int)strlen(table2) > len)
1366
len = strlen(table2);
1367
if(otherclause)
1368
len += strlen(otherclause);
1369
len += strlen(keycol);
1370
if(len + 30 > sizeof (stmt1))
1371
errorPrint
1372
("2constructed select statement in syncup_table() is too long");
1373
1374
if(otherclause) {
1375
while(*otherclause == ' ')
1376
++otherclause;
1377
if(strncmp(otherclause, "and ", 4) && strncmp(otherclause, "AND ", 4))
1378
errorPrint
1379
("2restricting clause in syncup_table() does not start with \"and\".");
1380
sprintf(stmt1, "select * from %s where %s order by %s", table1,
1381
otherclause + 4, keycol);
1382
sprintf(stmt2, "select * from %s where %s order by %s", table2,
1383
otherclause + 4, keycol);
1384
} else {
1385
sprintf(stmt1, "select * from %s order by %s", table1, keycol);
1386
sprintf(stmt2, "select * from %s order by %s", table2, keycol);
1387
}
1388
1389
cursor_comm(stmt1, stmt2, keycol, (fnptr) syncup_comm_fn, 0);
1390
} /* syncup_table */
Loggerhead is a web-based interface for Breezy
Version: 2.0.1