« back to all changes in this revision
Viewing changes to dbops.c
- browse files at revision 4
- compare with another revision
- download diff
- download tarball
- view history from revision 4
- Committer: Bazaar Package Importer
- Author(s): Kapil Hari Paranjape
- Date: 2007-05-09 07:33:04 UTC
- mfrom: (1.1.2 upstream)
- Revision ID: james.westby@ubuntu.com-20070509073304-ywptg9g6iiitsg17
Tags: 3.2.1-1
* New upstream version (3.2.1). Closes: #421451.
- can fetch and execute a local javascript file
if required by local html file.
- provide COPYING and CHANGES files.
* debian/rules:
- add CHANGES to dh_installchangelogs line.
- add dh_installman entry to install the man page.
* debian/copyright: updated to include the COPYING file.
* debian/edbrowse.1: added a basic man page.
- can fetch and execute a local javascript file
if required by local html file.
- provide COPYING and CHANGES files.
* debian/rules:
- add CHANGES to dh_installchangelogs line.
- add dh_installman entry to install the man page.
* debian/copyright: updated to include the COPYING file.
* debian/edbrowse.1: added a basic man page.
added
removed
dbops.c
1
/* dbops.c
2
* Database operations.
3
* Copyright (c) Karl Dahlke, 2007
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
608
("the config file does not specify the database - cannot connect");
609
return false;
610
}
611
sql_exclist(exclist);
612
sql_connect(dbarea, dblogin, dbpw);
613
if(rv_lastStatus) {
614
setError("cannot connect to the database - error %d", rv_vendorStatus);
615
return false;
616
}
617
if(!sql_database)
618
errorPrint("@sql connected, but database not set");
619
return true;
620
} /* ebConnect */
621
622
void
623
dbClose(void)
624
{
625
sql_disconnect();
626
} /* dbClose */
627
628
static char myTab[64];
629
static const char *myWhere;
630
static char *scl; /* select clause */
631
static int scllen;
632
static char *wcl; /* where clause */
633
static int wcllen;
634
static char wherecol[COLNAMELEN + 2];
635
static const char synwhere[] = "syntax error in where clause";
636
static struct DBTABLE *td;
637
638
static void
639
unexpected(void)
640
{
641
setError("unexpected sql error %d", rv_vendorStatus);
642
} /* unexpected */
643
644
static void
645
buildSelectClause(void)
646
{
647
int i;
648
scl = initString(&scllen);
649
stringAndString(&scl, &scllen, "select ");
650
for(i = 0; i < td->ncols; ++i) {
651
if(i)
652
stringAndChar(&scl, &scllen, ',');
653
stringAndString(&scl, &scllen, td->cols[i]);
654
}
655
stringAndString(&scl, &scllen, " from ");
656
stringAndString(&scl, &scllen, td->name);
657
} /* buildSelectClause */
658
659
static bool
660
buildWhereClause(void)
661
{
662
int i, l, n;
663
const char *w = myWhere;
664
const char *e;
665
666
wcl = initString(&wcllen);
667
wherecol[0] = 0;
668
if(stringEqual(w, "*"))
669
return true;
670
671
e = strchr(w, '=');
672
if(!e) {
673
if(!td->key1) {
674
setError("no key column specified");
675
return false;
676
}
677
e = td->cols[td->key1 - 1];
678
l = strlen(e);
679
if(l > COLNAMELEN) {
680
setError("column name %s is too long, limit %d characters", e,
681
COLNAMELEN);
682
return false;
683
}
684
strcpy(wherecol, e);
685
e = w - 1;
686
} else if(isdigit(*w)) {
687
n = strtol(w, (char **)&w, 10);
688
if(w != e) {
689
setError(synwhere);
690
return false;
691
}
692
if(n == 0 || n > td->ncols) {
693
setError("column %d is out of range", n);
694
return false;
695
}
696
goto setcol_n;
697
} else {
698
n = 0;
699
if(e - w <= COLNAMELEN) {
700
strncpy(wherecol, w, e - w);
701
wherecol[e - w] = 0;
702
for(i = 0; i < td->ncols; ++i) {
703
if(!strstr(td->cols[i], wherecol))
704
continue;
705
if(n) {
706
setError("multiple columns match %s", wherecol);
707
return false;
708
}
709
n = i + 1;
710
}
711
}
712
if(!n) {
713
setError("no column matches %s", wherecol);
714
return false;
715
}
716
setcol_n:
717
w = td->cols[n - 1];
718
l = strlen(w);
719
if(l > COLNAMELEN) {
720
setError("column name %s is too long, limit %d characters", w,
721
COLNAMELEN);
722
return false;
723
}
724
strcpy(wherecol, w);
725
}
726
727
stringAndString(&wcl, &wcllen, "where ");
728
stringAndString(&wcl, &wcllen, wherecol);
729
++e;
730
w = e;
731
if(!*e) {
732
stringAndString(&wcl, &wcllen, " is null");
733
} else if((i = strtol(e, (char **)&e, 10)) >= 0 &&
734
*e == '-' && (n = strtol(e + 1, (char **)&e, 10)) >= 0 && *e == 0) {
735
stringAndString(&wcl, &wcllen, " between ");
736
stringAndNum(&wcl, &wcllen, i);
737
stringAndString(&wcl, &wcllen, " and ");
738
stringAndNum(&wcl, &wcllen, n);
739
} else if(w[strlen(w) - 1] == '*') {
740
stringAndString(&wcl, &wcllen, lineFormat(" matches %S", w));
741
} else {
742
stringAndString(&wcl, &wcllen, lineFormat(" = %S", w));
743
}
744
745
return true;
746
} /* buildWhereClause */
747
748
static bool
749
setTable(void)
750
{
751
static const short exclist[] = { EXCNOTABLE, EXCNOCOLUMN, EXCSQLMISC, 0 };
752
int cid, nc, i, part1, part2;
753
const char *s = cw->fileName;
754
const char *t = strchr(s, ']');
755
if(t - s >= sizeof (myTab))
756
errorPrint("2table name too long, limit %d characters",
757
sizeof (myTab) - 4);
758
strncpy(myTab, s, t - s);
759
myTab[t - s] = 0;
760
myWhere = t + 1;
761
762
td = cw->table;
763
if(td)
764
return true;
765
766
/* haven't glommed onto this table yet */
767
td = findTableDescriptor(myTab);
768
if(td) {
769
if(!td->types) {
770
buildSelectClause();
771
sql_exclist(exclist);
772
cid = sql_prepare(scl);
773
nzFree(scl);
774
if(rv_lastStatus) {
775
if(rv_lastStatus == EXCNOTABLE)
776
setError("no such table %s", td->name);
777
else if(rv_lastStatus == EXCNOCOLUMN)
778
setError("invalid column name");
779
else
780
unexpected();
781
return false;
782
}
783
td->types = cloneString(rv_type);
784
sql_free(cid);
785
}
786
787
} else {
788
789
sql_exclist(exclist);
790
cid = sql_prepare("select * from %s", myTab);
791
if(rv_lastStatus) {
792
if(rv_lastStatus == EXCNOTABLE)
793
setError("no such table %s", myTab);
794
else
795
unexpected();
796
return false;
797
}
798
td = newTableDescriptor(myTab);
799
if(!td) {
800
sql_free(cid);
801
return false;
802
}
803
nc = rv_numRets;
804
if(nc > MAXTCOLS) {
805
printf("warning, only the first %d columns will be selected\n",
806
MAXTCOLS);
807
nc = MAXTCOLS;
808
}
809
td->types = cloneString(rv_type);
810
td->types[nc] = 0;
811
td->ncols = nc;
812
for(i = 0; i < nc; ++i)
813
td->cols[i] = cloneString(rv_name[i]);
814
sql_free(cid);
815
816
getPrimaryKey(myTab, &part1, &part2);
817
if(part1 > nc)
818
part1 = 0;
819
if(part2 > nc)
820
part2 = 0;
821
td->key1 = part1;
822
td->key2 = part2;
823
}
824
825
s = strpbrk(td->types, "BT");
826
if(s)
827
s = strpbrk(s + 1, "BT");
828
if(s) {
829
setError("cannot select more than one blob column");
830
return false;
831
}
832
833
cw->table = td;
834
return true;
835
} /* setTable */
836
837
void
838
showColumns(void)
839
{
840
char c;
841
const char *desc;
842
int i;
843
844
if(!setTable())
845
return;
846
printf("table %s", td->name);
847
if(!stringEqual(td->name, td->shortname))
848
printf(" [%s]", td->shortname);
849
i = sql_selectOne("select count(*) from %s", td->name);
850
printf(", %d rows\n", i);
851
852
for(i = 0; i < td->ncols; ++i) {
853
printf("%d ", i + 1);
854
if(td->key1 == i + 1 || td->key2 == i + 1)
855
printf("*");
856
printf("%s ", td->cols[i]);
857
c = td->types[i];
858
switch (c) {
859
case 'N':
860
desc = "int";
861
break;
862
case 'D':
863
desc = "date";
864
break;
865
case 'I':
866
desc = "time";
867
break;
868
case 'M':
869
desc = "money";
870
break;
871
case 'F':
872
desc = "float";
873
break;
874
case 'S':
875
desc = "string";
876
break;
877
case 'C':
878
desc = "char";
879
break;
880
case 'B':
881
desc = "blob";
882
break;
883
case 'T':
884
desc = "text";
885
break;
886
default:
887
desc = "?";
888
break;
889
} /* switch */
890
printf("%s\n", desc);
891
}
892
} /* showColumns */
893
894
bool
895
sqlReadRows(const char *filename, char **bufptr)
896
{
897
int cid;
898
char *rbuf, *unld, *s;
899
int rlen;
900
901
*bufptr = EMPTYSTRING;
902
if(!ebConnect())
903
return false;
904
if(!setTable())
905
return false;
906
907
rbuf = initString(&rlen);
908
myWhere = strchr(filename, ']') + 1;
909
if(*myWhere) {
910
if(!buildWhereClause())
911
return false;
912
buildSelectClause();
913
rv_blobFile = 0;
914
cid = sql_prepOpen("%s %0s", scl, wcl);
915
nzFree(scl);
916
nzFree(wcl);
917
while(sql_fetchNext(cid, 0)) {
918
unld = sql_mkunld('\177');
919
if(strchr(unld, '|')) {
920
setError
921
("the data contains pipes, which is my reserved delimiter");
922
goto abort;
923
}
924
if(strchr(unld, '\n')) {
925
setError("the data contains newlines");
926
goto abort;
927
}
928
for(s = unld; *s; ++s)
929
if(*s == '\177')
930
*s = '|';
931
s[-1] = '\n'; /* overwrite the last pipe */
932
933
/* look for blob column */
934
if(s = strpbrk(td->types, "BT")) {
935
int bfi = s - td->types; /* blob field index */
936
int cx = 0; /* context, where to put the blob */
937
int j;
938
char *u, *v, *end;
939
u = unld;
940
for(j = 0; j < bfi; ++j)
941
u = strchr(u, '|') + 1;
942
v = strpbrk(u, "|\n");
943
end = v + strlen(v);
944
if(rv_blobSize) {
945
cx = sideBuffer(0, rv_blobLoc, rv_blobSize, 0, false);
946
nzFree(rv_blobLoc);
947
}
948
sprintf(myTab, "<%d>", cx);
949
if(!cx)
950
myTab[0] = 0;
951
j = strlen(myTab);
952
/* unld is pretty long; I'm just going to assume there is enough room for this */
953
memmove(u + j, v, end - v);
954
u[j + (end - v)] = 0;
955
memcpy(u, myTab, j);
956
}
957
958
stringAndString(&rbuf, &rlen, unld);
959
}
960
sql_closeFree(cid);
961
}
962
963
*bufptr = rbuf;
964
return true;
965
966
abort:
967
nzFree(rbuf);
968
sql_closeFree(cid);
969
return false;
970
} /* sqlReadRows */
971
972
static char *lineFields[MAXTCOLS];
973
974
/* Split a line at pipe boundaries, and make sure the field count is correct */
975
static bool
976
intoFields(char *line)
977
{
978
char *s = line;
979
int j = 0;
980
int c;
981
982
while(1) {
983
lineFields[j] = s;
984
s = strpbrk(s, "|\n");
985
c = *s;
986
*s++ = 0;
987
++j;
988
if(c == '\n')
989
break;
990
if(j < td->ncols)
991
continue;
992
setError
993
("line contains too many fields, please do not introduce any pipes into the text");
994
return false;
995
}
996
997
if(j == td->ncols)
998
return true;
999
setError
1000
("line contains too few fields, please do not remove any pipes from the text");
1001
return false;
1002
} /* intoFields */
1003
1004
static bool
1005
rowCountCheck(const char *action, int cnt1)
1006
{
1007
int cnt2 = rv_lastNrows;
1008
static char rowword1[] = "rows";
1009
static char rowword2[] = "records";
1010
1011
if(cnt1 == cnt2)
1012
return true;
1013
1014
rowword1[3] = (cnt1 == 1 ? 0 : 's');
1015
rowword2[6] = (cnt2 == 1 ? 0 : 's');
1016
setError("oops! I %s %d %s, and %d database %s were affected.",
1017
action, cnt1, rowword1, cnt2, rowword2);
1018
return false;
1019
} /* rowCountCheck */
1020
1021
static int
1022
keyCountCheck(void)
1023
{
1024
if(!td->key1) {
1025
setError("key column not specified");
1026
return false;
1027
}
1028
return (td->key2 ? 2 : 1);
1029
} /* keyCountCheck */
1030
1031
/* Typical error conditions for insert update delete */
1032
static const short insupdExceptions[] = { EXCSQLMISC,
1033
EXCVIEWUSE, EXCREFINT, EXCITEMLOCK, EXCPERMISSION,
1034
EXCDEADLOCK, EXCCHECK, EXCTIMEOUT, EXCNOTNULLCOLUMN, 0
1035
};
1036
1037
static bool
1038
insupdError(const char *action, int rcnt)
1039
{
1040
int rc = rv_lastStatus;
1041
const char *desc;
1042
1043
if(rc) {
1044
switch (rc) {
1045
case EXCVIEWUSE:
1046
desc = "cannot modify a view";
1047
break;
1048
case EXCREFINT:
1049
desc = "some other row in the database depends on this row";
1050
break;
1051
case EXCITEMLOCK:
1052
desc = "row or table is locked";
1053
break;
1054
case EXCPERMISSION:
1055
desc =
1056
"you do not have permission to modify the database in this way";
1057
break;
1058
case EXCDEADLOCK:
1059
desc = "deadlock detected";
1060
break;
1061
case EXCNOTNULLCOLUMN:
1062
desc = "placing null into a not-null column";
1063
break;
1064
case EXCCHECK:
1065
desc = "check constraint violated";
1066
break;
1067
case EXCTIMEOUT:
1068
desc = "daatabase timeout";
1069
break;
1070
default:
1071
setError("miscelaneous sql error %d", rv_vendorStatus);
1072
return false;
1073
}
1074
setError(desc);
1075
return false;
1076
}
1077
1078
return rowCountCheck(action, rcnt);
1079
} /* insupdError */
1080
1081
bool
1082
sqlDelRows(int start, int end)
1083
{
1084
int nkeys, ndel, key1, key2, ln;
1085
1086
if(!setTable())
1087
return false;
1088
1089
nkeys = keyCountCheck();
1090
key1 = td->key1 - 1;
1091
key2 = td->key2 - 1;
1092
if(!nkeys)
1093
return false;
1094
1095
ndel = end - start + 1;
1096
ln = start;
1097
if(ndel > 100) {
1098
setError("cannot delete more than 100 rows at a time");
1099
return false;
1100
}
1101
1102
/* We could delete all the rows with one statement, using an in(list),
1103
* but that won't work when the key is two columns.
1104
* I have to write the one-line-at-a-time code anyways,
1105
* I'll just use that for now. */
1106
while(ndel--) {
1107
char *line = (char *)fetchLine(ln, 0);
1108
intoFields(line);
1109
sql_exclist(insupdExceptions);
1110
if(nkeys == 1)
1111
sql_exec("delete from %s where %s = %S",
1112
td->name, td->cols[key1], lineFields[key1]);
1113
else
1114
sql_exec("delete from %s where %s = %S and %s = %S",
1115
td->name, td->cols[key1], lineFields[key1],
1116
td->cols[key2], lineFields[key2]);
1117
nzFree(line);
1118
if(!insupdError("deleted", 1))
1119
return false;
1120
delText(ln, ln);
1121
}
1122
1123
return true;
1124
} /* sqlDelRows */
1125
1126
bool
1127
sqlUpdateRow(pst source, int slen, pst dest, int dlen)
1128
{
1129
char *d2; /* clone of dest */
1130
char *s, *t;
1131
int j, l1, l2, nkeys, key1, key2;
1132
char *u1, *u2; /* pieces of the update statement */
1133
int u1len, u2len;
1134
1135
/* compare all the way out to newline, so we know both strings end at the same time */
1136
if(slen == dlen && !memcmp(source, dest, slen + 1))
1137
return true;
1138
1139
if(!setTable())
1140
return false;
1141
1142
nkeys = keyCountCheck();
1143
key1 = td->key1 - 1;
1144
key2 = td->key2 - 1;
1145
if(!nkeys)
1146
return false;
1147
1148
d2 = (char *)clonePstring(dest);
1149
if(!intoFields(d2)) {
1150
nzFree(d2);
1151
return false;
1152
}
1153
1154
j = 0;
1155
u1 = initString(&u1len);
1156
u2 = initString(&u2len);
1157
s = (char *)source;
1158
1159
while(1) {
1160
t = strpbrk(s, "|\n");
1161
l1 = t - s;
1162
l2 = strlen(lineFields[j]);
1163
if(l1 != l2 || memcmp(s, lineFields[j], l1)) {
1164
if(j == key1 || j == key2) {
1165
setError("cannot change a key column");
1166
goto abort;
1167
}
1168
if(td->types[j] == 'B') {
1169
setError("cannot change a blob field");
1170
goto abort;
1171
}
1172
if(td->types[j] == 'T') {
1173
setError("cannot change a text field");
1174
goto abort;
1175
}
1176
if(*u1)
1177
stringAndChar(&u1, &u1len, ',');
1178
stringAndString(&u1, &u1len, td->cols[j]);
1179
if(*u2)
1180
stringAndChar(&u2, &u2len, ',');
1181
stringAndString(&u2, &u2len, lineFormat("%S", lineFields[j]));
1182
}
1183
if(*t == '\n')
1184
break;
1185
s = t + 1;
1186
++j;
1187
}
1188
1189
sql_exclist(insupdExceptions);
1190
if(nkeys == 1)
1191
sql_exec("update %s set(%s) = (%s) where %s = %S",
1192
td->name, u1, u2, td->cols[key1], lineFields[key1]);
1193
else
1194
sql_exec("update %s set(%s) = (%s) where %s = %S and %s = %S",
1195
td->name, u1, u2,
1196
td->cols[key1], lineFields[key1], td->cols[key2], lineFields[key2]);
1197
if(!insupdError("updated", 1))
1198
goto abort;
1199
1200
nzFree(d2);
1201
nzFree(u1);
1202
nzFree(u2);
1203
return true;
1204
1205
abort:
1206
nzFree(d2);
1207
nzFree(u1);
1208
nzFree(u2);
1209
return false;
1210
} /* sqlUpdateRow */
1211
1212
bool
1213
sqlAddRows(int ln)
1214
{
1215
char *u1, *u2; /* pieces of the insert statement */
1216
char *unld, *s;
1217
int u1len, u2len;
1218
int j, l, rowid;
1219
double dv;
1220
char inp[256];
1221
1222
if(!setTable())
1223
return false;
1224
1225
while(1) {
1226
u1 = initString(&u1len);
1227
u2 = initString(&u2len);
1228
for(j = 0; j < td->ncols; ++j) {
1229
reenter:
1230
if(strchr("BT", td->types[j]))
1231
continue;
1232
printf("%s: ", td->cols[j]);
1233
fflush(stdout);
1234
if(!fgets(inp, sizeof (inp), stdin)) {
1235
puts("EOF");
1236
ebClose(1);
1237
}
1238
l = strlen(inp);
1239
if(l && inp[l - 1] == '\n')
1240
inp[--l] = 0;
1241
if(stringEqual(inp, ".")) {
1242
nzFree(u1);
1243
nzFree(u2);
1244
return true;
1245
}
1246
1247
/* For now, a null field is always excepted. */
1248
/* Someday we may want to check this against the not-null constraint. */
1249
if(inp[0] == 0)
1250
goto goodfield;
1251
1252
/* verify the integrity of the entered field */
1253
if(strchr(inp, '|')) {
1254
puts("please, no pipes in the data");
1255
goto reenter;
1256
}
1257
1258
switch (td->types[j]) {
1259
case 'N':
1260
s = inp;
1261
if(*s == '-')
1262
++s;
1263
if(stringIsNum(s) < 0) {
1264
puts("number expected");
1265
goto reenter;
1266
}
1267
break;
1268
case 'F':
1269
if(!stringIsFloat(inp, &dv)) {
1270
puts("decimal number expected");
1271
goto reenter;
1272
}
1273
break;
1274
case 'C':
1275
if(strlen(inp) > 1) {
1276
puts("one character expected");
1277
goto reenter;
1278
}
1279
break;
1280
case 'D':
1281
if(stringDate(inp, false) < 0) {
1282
puts("date expected");
1283
goto reenter;
1284
}
1285
break;
1286
case 'I':
1287
if(stringTime(inp) < 0) {
1288
puts("time expected");
1289
goto reenter;
1290
}
1291
break;
1292
}
1293
1294
goodfield:
1295
if(*u1)
1296
stringAndChar(&u1, &u1len, ',');
1297
stringAndString(&u1, &u1len, td->cols[j]);
1298
if(*u2)
1299
stringAndChar(&u2, &u2len, ',');
1300
stringAndString(&u2, &u2len, lineFormat("%S", inp));
1301
}
1302
sql_exclist(insupdExceptions);
1303
sql_exec("insert into %s (%s) values (%s)", td->name, u1, u2);
1304
nzFree(u1);
1305
nzFree(u2);
1306
if(!insupdError("inserted", 1)) {
1307
printf("Error: ");
1308
showError();
1309
continue;
1310
}
1311
/* Fetch the row just entered;
1312
its serial number may have changed from 0 to something real */
1313
rowid = rv_lastRowid;
1314
buildSelectClause();
1315
sql_select("%s where rowid = %d", scl, rowid, 0);
1316
nzFree(scl);
1317
unld = sql_mkunld('|');
1318
l = strlen(unld);
1319
unld[l - 1] = '\n'; /* overwrite the last pipe */
1320
if(!addTextToBuffer((pst) unld, l, ln))
1321
return false;
1322
++ln;
1323
}
1324
1325
/* This pointis not reached; make the compilerhappy */
1326
return true;
1327
} /* sqlAddRows */
1328
1329
1330
/*********************************************************************
1331
Sync up two tables, or corresponding sections of two tables.
1332
These are usually equischema tables in parallel databases or machines.
1333
This isn't used by edbrowse; it's just something I wrote,
1334
and I thought you might find it useful.
1335
It follows the C convention of copying the second argument
1336
to the first, like the string and memory functions,
1337
rather than the shell convention of copying (cp) the first argument to the second.
1338
Hey - why have one standard, when you can have two?
1339
*********************************************************************/
1340
1341
static const char *synctable; /* table being sync-ed */
1342
static const char *synckeycol; /* key column */
1343
static const char *sync_clause; /* additional clause, to sync only part of the table */
1344
1345
static int
1346
syncup_comm_fn(char action, char *line1, char *line2, int key)
1347
{
1348
switch (action) {
1349
case '<': /* delete */
1350
sql_exec("delete from %s where %s = %d %0s",
1351
synctable, synckeycol, key, sync_clause);
1352
break;
1353
case '>': /* insert */
1354
sql_exec("insert into %s values(%s)", synctable, line2);
1355
break;
1356
case '*': /* update */
1357
sql_exec("update %s set * = (%s) where %s = %d %0s",
1358
synctable, line2, synckeycol, key, sync_clause);
1359
break;
1360
} /* switch */
1361
return 0;
1362
} /* syncup_comm_fn */
1363
1364
/* make table1 look like table2 */
1365
void
1366
syncup_table(const char *table1, const char *table2, /* the two tables */
1367
const char *keycol, /* the key column */
1368
const char *otherclause)
1369
{
1370
char stmt1[200], stmt2[200];
1371
int len;
1372
1373
synctable = table1;
1374
synckeycol = keycol;
1375
sync_clause = otherclause;
1376
len = strlen(table1);
1377
if((int)strlen(table2) > len)
1378
len = strlen(table2);
1379
if(otherclause)
1380
len += strlen(otherclause);
1381
len += strlen(keycol);
1382
if(len + 30 > sizeof (stmt1))
1383
errorPrint
1384
("2constructed select statement in syncup_table() is too long");
1385
1386
if(otherclause) {
1387
while(*otherclause == ' ')
1388
++otherclause;
1389
if(strncmp(otherclause, "and ", 4) && strncmp(otherclause, "AND ", 4))
1390
errorPrint
1391
("2restricting clause in syncup_table() does not start with \"and\".");
1392
sprintf(stmt1, "select * from %s where %s order by %s", table1,
1393
otherclause + 4, keycol);
1394
sprintf(stmt2, "select * from %s where %s order by %s", table2,
1395
otherclause + 4, keycol);
1396
} else {
1397
sprintf(stmt1, "select * from %s order by %s", table1, keycol);
1398
sprintf(stmt2, "select * from %s order by %s", table2, keycol);
1399
}
1400
1401
cursor_comm(stmt1, stmt2, keycol, (fnptr) syncup_comm_fn, 0);
1402
} /* syncup_table */
Loggerhead is a web-based interface for Breezy
Version: 2.0.1