6 * Copyright (C) 1987 by the Massachusetts Institute of Technology
7 * For copying and distribution information, please see the file
13 static char *rcsid_qsupport_dc = "$Header$";
16 #include <mit-copyright.h>
19 #include "mr_server.h"
21 EXEC SQL INCLUDE sqlca;
22 EXEC SQL INCLUDE sqlda;
26 extern int ingres_errno, mr_errcode;
28 EXEC SQL BEGIN DECLARE SECTION;
29 extern char stmt_buf[];
30 EXEC SQL END DECLARE SECTION;
32 EXEC SQL WHENEVER SQLERROR CALL ingerr;
35 /* Validation Routines */
37 validate_row(q, argv, v)
38 register struct query *q;
40 register struct validate *v;
42 EXEC SQL BEGIN DECLARE SECTION;
46 EXEC SQL END DECLARE SECTION;
48 /* build where clause */
49 build_qual(v->qual, v->argc, argv, qual);
51 if (log_flags & LOG_VALID)
52 /* tell the logfile what we're doing */
53 com_err(whoami, 0, "validating row: %s", qual);
55 /* look for the record */
56 sprintf(stmt_buf,"SELECT COUNT (*) FROM %s WHERE %s",q->rtable,qual);
57 EXEC SQL PREPARE stmt INTO :SQLDA USING NAMES FROM :stmt_buf;
60 EXEC SQL DECLARE csr126 CURSOR FOR stmt;
62 EXEC SQL FETCH csr126 USING DESCRIPTOR :SQLDA;
63 EXEC SQL CLOSE csr126;
64 rowcount = *(int *)SQLDA->sqlvar[0].sqldata;
66 if (ingres_errno) return(mr_errcode);
67 if (rowcount == 0) return(MR_NO_MATCH);
68 if (rowcount > 1) return(MR_NOT_UNIQUE);
72 validate_fields(q, argv, vo, n)
74 register char *argv[];
75 register struct valobj *vo;
83 if (log_flags & LOG_VALID)
84 com_err(whoami, 0, "validating %s in %s: %s",
85 vo->namefield, vo->table, argv[vo->index]);
86 status = validate_name(argv, vo);
90 if (log_flags & LOG_VALID)
91 com_err(whoami, 0, "validating %s in %s: %s",
92 vo->idfield, vo->table, argv[vo->index]);
93 status = validate_id(q, argv, vo);
97 if (log_flags & LOG_VALID)
98 com_err(whoami, 0, "validating date: %s", argv[vo->index]);
99 status = validate_date(argv, vo);
103 if (log_flags & LOG_VALID)
104 com_err(whoami, 0, "validating %s type: %s",
105 vo->table, argv[vo->index]);
106 status = validate_type(argv, vo);
110 if (log_flags & LOG_VALID)
111 com_err(whoami, 0, "validating typed data (%s): %s",
112 argv[vo->index - 1], argv[vo->index]);
113 status = validate_typedata(q, argv, vo);
117 if (log_flags & LOG_VALID)
118 com_err(whoami, 0, "validating rename %s in %s",
119 argv[vo->index], vo->table);
120 status = validate_rename(argv, vo);
124 if (log_flags & LOG_VALID)
125 com_err(whoami, 0, "validating chars: %s", argv[vo->index]);
126 status = validate_chars(argv[vo->index]);
134 status = lock_table(vo);
138 status = convert_wildcards(argv[vo->index]);
142 status = convert_wildcards_uppercase(argv[vo->index]);
147 if (status != MR_EXISTS) return(status);
151 if (ingres_errno) return(mr_errcode);
156 /* validate_chars: verify that there are no illegal characters in
157 * the string. Legal characters are printing chars other than
158 * ", *, ?, \, [ and ].
160 static int illegalchars[] = {
161 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, /* ^@ - ^O */
162 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, /* ^P - ^_ */
163 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, /* SPACE - / */
164 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, /* 0 - ? */
165 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, /* : - O */
166 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 0, 0, /* P - _ */
167 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, /* ` - o */
168 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, /* p - ^? */
169 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
170 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
171 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
172 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
173 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
174 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
175 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
176 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
183 if (illegalchars[*s++])
189 validate_id(q, argv, vo)
192 register struct valobj *vo;
194 EXEC SQL BEGIN DECLARE SECTION;
195 char *name, *tbl, *namefield, *idfield;
197 EXEC SQL END DECLARE SECTION;
201 name = argv[vo->index];
203 namefield = vo->namefield;
204 idfield = vo->idfield;
206 if ((!strcmp(tbl, "users") && !strcmp(namefield, "login")) ||
207 !strcmp(tbl, "machine") ||
208 !strcmp(tbl, "subnet") ||
209 !strcmp(tbl, "filesys") ||
210 !strcmp(tbl, "list") ||
211 !strcmp(tbl, "cluster") ||
212 !strcmp(tbl, "strings")) {
213 if (!strcmp(tbl, "machine") || !strcmp(tbl, "subnet"))
214 for (c = name; *c; c++) if (islower(*c)) *c = toupper(*c);
215 status = name_to_id(name, tbl, &id);
217 *(int *)argv[vo->index] = id;
219 } else if (status == MR_NO_MATCH && !strcmp(tbl, "strings") &&
220 (q->type == APPEND || q->type == UPDATE)) {
222 cache_entry(name, "STRING", id);
223 *(int *)argv[vo->index] = id;
225 } else if (status == MR_NO_MATCH || status == MR_NOT_UNIQUE)
231 if (!strcmp(namefield, "uid")) {
232 sprintf(stmt_buf,"SELECT %s FROM %s WHERE %s = %s",idfield,tbl,namefield,name);
234 sprintf(stmt_buf,"SELECT %s FROM %s WHERE %s = '%s'",idfield,tbl,namefield,name);
236 EXEC SQL PREPARE stmt INTO :SQLDA USING NAMES FROM :stmt_buf;
239 EXEC SQL DECLARE csr127 CURSOR FOR stmt;
240 EXEC SQL OPEN csr127;
242 EXEC SQL FETCH csr127 USING DESCRIPTOR :SQLDA;
243 if(sqlca.sqlcode == 0) {
245 EXEC SQL FETCH csr127 USING DESCRIPTOR :SQLDA;
246 if(sqlca.sqlcode == 0) rowcount++;
248 EXEC SQL CLOSE csr127;
252 if (rowcount != 1) return(vo->error);
253 bcopy(SQLDA->sqlvar[0].sqldata,argv[vo->index],sizeof(int));
257 validate_name(argv, vo)
259 register struct valobj *vo;
261 EXEC SQL BEGIN DECLARE SECTION;
262 char *name, *tbl, *namefield;
264 EXEC SQL END DECLARE SECTION;
267 name = argv[vo->index];
269 namefield = vo->namefield;
270 if (!strcmp(tbl, "servers") && !strcmp(namefield, "name")) {
271 for (c = name; *c; c++)
275 sprintf(stmt_buf,"SELECT DISTINCT COUNT(*) FROM %s WHERE %s.%s = '%s'",
276 tbl,tbl,namefield,name);
277 EXEC SQL PREPARE stmt INTO :SQLDA USING NAMES FROM :stmt_buf;
280 EXEC SQL DECLARE csr128 CURSOR FOR stmt;
281 EXEC SQL OPEN csr128;
282 EXEC SQL FETCH csr128 USING DESCRIPTOR :SQLDA;
283 rowcount = *(int *)SQLDA->sqlvar[0].sqldata;
284 EXEC SQL CLOSE csr128;
286 if (ingres_errno) return(mr_errcode);
287 return ((rowcount == 1) ? MR_EXISTS : vo->error);
290 validate_date(argv, vo)
294 EXEC SQL BEGIN DECLARE SECTION;
298 EXEC SQL END DECLARE SECTION;
300 idate = argv[vo->index];
301 EXEC SQL SELECT interval('years',date(:idate)-date('today')) INTO :dd;
303 if (sqlca.sqlcode != 0 || dd > 5.0) return(MR_DATE);
308 validate_rename(argv, vo)
312 EXEC SQL BEGIN DECLARE SECTION;
313 char *name, *tbl, *namefield, *idfield;
315 EXEC SQL END DECLARE SECTION;
319 c = name = argv[vo->index];
321 if (illegalchars[*c++])
324 /* minor kludge to upcasify machine names */
325 if (!strcmp(tbl, "machine"))
326 for (c = name; *c; c++) if (islower(*c)) *c = toupper(*c);
327 namefield = vo->namefield;
328 idfield = vo->idfield;
331 if (!strcmp(argv[vo->index], argv[vo->index - 1]))
333 sprintf(stmt_buf,"SELECT %s FROM %s WHERE %s = LEFT('%s',SIZE(%s))",
334 namefield,tbl,namefield,name,namefield);
335 EXEC SQL PREPARE stmt INTO :SQLDA USING NAMES FROM :stmt_buf;
338 EXEC SQL DECLARE csr129 CURSOR FOR stmt;
339 EXEC SQL OPEN csr129;
340 EXEC SQL FETCH csr129 USING DESCRIPTOR :SQLDA;
341 if(sqlca.sqlcode == 0) id=1; else id=0;
342 EXEC SQL CLOSE csr129;
344 if (ingres_errno) return(mr_errcode);
350 status = name_to_id(name, tbl, &id);
351 if (status == MR_NO_MATCH || id == *(int *)argv[vo->index - 1])
358 validate_type(argv, vo)
360 register struct valobj *vo;
362 EXEC SQL BEGIN DECLARE SECTION;
366 EXEC SQL END DECLARE SECTION;
369 typename = vo->table;
370 c = val = argv[vo->index];
372 if (illegalchars[*c++])
376 /* uppercase type fields */
377 for (c = val; *c; c++) if (islower(*c)) *c = toupper(*c);
379 EXEC SQL SELECT COUNT(trans) INTO :cnt FROM alias
380 WHERE name = :typename AND type='TYPE' AND trans = :val;
381 if (ingres_errno) return(mr_errcode);
382 return (cnt ? MR_EXISTS : vo->error);
385 /* validate member or type-specific data field */
387 validate_typedata(q, argv, vo)
388 register struct query *q;
389 register char *argv[];
390 register struct valobj *vo;
392 EXEC SQL BEGIN DECLARE SECTION;
397 EXEC SQL END DECLARE SECTION;
402 /* get named object */
403 name = argv[vo->index];
405 /* get field type string (known to be at index-1) */
406 field_type = argv[vo->index-1];
408 /* get corresponding data type associated with field type name */
409 EXEC SQL SELECT trans INTO :data_type FROM alias
410 WHERE name = :field_type AND type='TYPEDATA';
411 if (ingres_errno) return(mr_errcode);
412 if (sqlca.sqlerrd[2] != 1) return(MR_TYPE);
414 /* now retrieve the record id corresponding to the named object */
415 if (index(data_type, ' '))
416 *index(data_type, ' ') = 0;
417 if (!strcmp(data_type, "user")) {
419 if (index(name, '@'))
421 status = name_to_id(name, data_type, &id);
422 if (status && (status == MR_NO_MATCH || status == MR_NOT_UNIQUE))
424 if (status) return(status);
425 } else if (!strcmp(data_type, "list")) {
427 status = name_to_id(name, data_type, &id);
428 if (status && status == MR_NOT_UNIQUE)
430 if (status == MR_NO_MATCH) {
431 /* if idfield is non-zero, then if argv[0] matches the string
432 * that we're trying to resolve, we should get the value of
433 * numvalues.[idfield] for the id.
435 if (vo->idfield && !strcmp(argv[0], argv[vo->index])) {
436 set_next_object_id(q->validate->object_id, q->rtable, 0);
438 EXEC SQL REPEATED SELECT value INTO :id FROM numvalues
440 if (sqlca.sqlerrd[2] != 1) return(MR_LIST);
443 } else if (status) return(status);
444 } else if (!strcmp(data_type, "machine")) {
446 for (c = name; *c; c++) if (islower(*c)) *c = toupper(*c);
447 status = name_to_id(name, data_type, &id);
448 if (status && (status == MR_NO_MATCH || status == MR_NOT_UNIQUE))
450 if (status) return(status);
451 } else if (!strcmp(data_type, "string")) {
453 status = name_to_id(name, data_type, &id);
454 if (status && status == MR_NOT_UNIQUE)
456 if (status == MR_NO_MATCH) {
457 if (q->type != APPEND && q->type != UPDATE) return(MR_STRING);
459 cache_entry(name, "STRING", id);
460 } else if (status) return(status);
461 } else if (!strcmp(data_type, "none")) {
467 /* now set value in argv */
468 *(int *)argv[vo->index] = id;
474 /* Lock the table named by the validation object */
479 sprintf(stmt_buf,"UPDATE %s SET modtime='now' WHERE %s.%s = 0",
480 vo->table,vo->table,vo->idfield);
481 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
482 if (ingres_errno) return(mr_errcode);
483 if (sqlca.sqlerrd[2] != 1)
490 /* Check the database at startup time. For now this just resets the
491 * inprogress flags that the DCM uses.
494 sanity_check_database()
499 /* Dynamic SQL support routines */
500 MR_SQLDA_T *mr_alloc_SQLDA()
503 short *null_indicators;
506 if((it=(MR_SQLDA_T *)malloc(sizeof(MR_SQLDA_T)))==NULL) {
507 com_err(whoami, MR_NO_MEM, "setting up SQLDA");
511 if((null_indicators=(short *)calloc(QMAXARGS,sizeof(short)))==NULL) {
512 com_err(whoami, MR_NO_MEM, "setting up SQLDA null indicators");
516 for(j=0; j<QMAXARGS; j++) {
517 if((it->sqlvar[j].sqldata=malloc(sizeof(short)+ARGLEN))==NULL) {
518 com_err(whoami, MR_NO_MEM, "setting up SQLDA variables");
521 it->sqlvar[j].sqllen=ARGLEN;
522 it->sqlvar[j].sqlind=null_indicators+j;
523 null_indicators[j]=0;
530 /* Use this after FETCH USING DESCRIPTOR one or more
531 * result columns may contain NULLs. This routine is
532 * not currently needed, since db/schema creates all
533 * columns with a NOT NULL WITH DEFAULT clause.
535 * This is currently dead flesh, since no Moira columns
536 * allow null values; all use default values.
538 mr_fix_nulls_in_SQLDA(da)
541 register IISQLVAR *var;
545 for(j=0, var=da->sqlvar; j<da->sqld; j++, var++) {
546 switch(var->sqltype) {
553 intp=(int *)var->sqldata;
562 * This routine fetches an appropriate value from the numvalues table.
563 * It is a little hack to get around the fact that SQL doesn't let you
564 * do something like INSERT INTO table (foo) VALUES (other_table.bar).
566 * It is called from the query table as (*v->pre_rtn)(q,Argv,cl) or
567 * from within a setup_...() routine with the appropriate arguments.
569 * Correct functioning of this routine may depend on the assumption
570 * that this query is an APPEND.
573 prefetch_value(q,argv,cl)
578 EXEC SQL BEGIN DECLARE SECTION;
579 char *name = q->validate->object_id;
581 EXEC SQL END DECLARE SECTION;
582 int status, limit, argc;
584 /* set next object id, limiting it if necessary */
585 if(!strcmp(name, "uid") || !strcmp(name, "gid"))
586 limit = 1; /* So far as I know, this isn't needed. Just CMA. */
589 if((status = set_next_object_id(name, q->rtable, limit)) != MR_SUCCESS)
592 /* fetch object id */
593 EXEC SQL SELECT value INTO :value FROM numvalues WHERE name=:name;
594 if(ingres_errno) return(mr_errcode);
595 if(sqlca.sqlerrd[2] != 1) return(MR_INTERNAL);
597 argc = q->argc + q->vcnt; /* end of Argv for APPENDs */
598 sprintf(argv[argc],"%d",value); /** Could save this step by changing tlist from %s to %d **/
603 /* prefetch_filesys():
604 * Fetches the phys_id from filesys based on the filsys_id in argv[0].
605 * Appends the filsys_id and the phys_id to the argv so they can be
606 * referenced in an INSERT into a table other than filesys. Also
607 * see comments at prefetch_value().
609 * Assumes the existence of a row where filsys_id = argv[0], since a
610 * filesys label has already been resolved to a filsys_id.
612 prefetch_filesys(q,argv,cl)
617 EXEC SQL BEGIN DECLARE SECTION;
619 EXEC SQL END DECLARE SECTION;
622 fid = *(int *)argv[0];
623 EXEC SQL SELECT phys_id INTO :phid FROM filesys WHERE filsys_id = :fid;
624 if(ingres_errno) return(mr_errcode);
626 argc=q->argc+q->vcnt;
627 sprintf(argv[argc++],"%d",phid);
628 sprintf(argv[argc],"%d",fid);
633 /* Convert normal Unix-style wildcards to SQL voodoo */
634 convert_wildcards(arg)
637 static char buffer[ARGLEN];
638 register char *s, *d;
640 for(d=buffer,s=arg;*s;s++) {
642 case '*': *d++='%'; *d++='%'; break;
643 case '?': *d++='_'; break;
646 case ']': *d++='*'; *d++ = *s; break;
647 case '%': *d++='*'; *d++='%'; *d++='%'; break;
648 default: *d++ = *s; break;
653 /* Copy back into argv */
659 /* This version includes uppercase conversion, for things like gmac.
660 * This is necessary because "LIKE" doesn't work with "uppercase()".
661 * Including it in a wildcard routine saves making two passes over
662 * the argument string.
664 convert_wildcards_uppercase(arg)
667 static char buffer[ARGLEN];
668 register char *s, *d;
670 for(d=buffer,s=arg;*s;s++) {
672 case '*': *d++='%'; *d++='%'; break;
673 case '?': *d++='_'; break;
676 case ']': *d++='*'; *d++ = *s; break;
677 case '%': *d++='*'; *d++='%'; *d++='%'; break;
678 default: *d++=toupper(*s); break; /* This is the only diff. */
683 /* Copy back into argv */
690 /* Looks like it's time to build an abstraction barrier, Yogi */
692 EXEC SQL BEGIN DECLARE SECTION;
694 EXEC SQL END DECLARE SECTION;
698 EXEC SQL PREPARE stmt FROM :stmt;
699 EXEC SQL DESCRIBE stmt INTO :SQLDA;
700 if(SQLDA->sqld==0) /* Not a SELECT */
702 EXEC SQL DECLARE csr CURSOR FOR stmt;
704 EXEC SQL FETCH csr USING DESCRIPTOR :SQLDA;
707 else if((sqlca.sqlcode<0) && mr_errcode)
717 /* Adds a string to the string table. Returns the id number.
721 EXEC SQL BEGIN DECLARE SECTION;
723 EXEC SQL END DECLARE SECTION;
725 EXEC SQL BEGIN DECLARE SECTION;
728 EXEC SQL END DECLARE SECTION;
730 EXEC SQL SELECT value INTO :id FROM numvalues WHERE name = 'strings_id';
732 EXEC SQL UPDATE numvalues SET value = :id WHERE name = 'strings_id';
734 /* Use sprintf to get around problem with doubled single quotes */
735 sprintf(buf,"INSERT INTO strings (string_id, string) VALUES (%d, '%s')",id,name);
736 EXEC SQL EXECUTE IMMEDIATE :buf;