6 * Copyright (C) 1987, 1988 by the Massachusetts Institute of Technology
7 * For copying and distribution information, please see the file
13 static char *rcsid_qrtn_dc = "$Header$";
16 #include <mit-copyright.h>
19 #include "mr_server.h"
20 EXEC SQL INCLUDE sqlca; /* SQL Communications Area */
21 EXEC SQL INCLUDE sqlda; /* SQL Descriptor Area */
25 EXEC SQL BEGIN DECLARE SECTION;
28 char cdummy[MR_CDUMMY_LEN];
29 char stmt_buf[MR_STMTBUF_LEN];
30 EXEC SQL END DECLARE SECTION;
36 EXEC SQL BEGIN DECLARE SECTION;
37 int query_timeout = 30;
38 char *database = "moira";
39 EXEC SQL END DECLARE SECTION;
43 #define INGRES_BAD_DATE1 40206
44 #define INGRES_BAD_DATE2 40207
45 #define INGRES_DEADLOCK 49900
46 #define INGRES_TIMEOUT 39100
47 #define INGRES_BAD_COLUMN 30110
48 #define INGRES_ASGN_ERR 40204
49 #define INGRES_NO_CURSOR 30120
50 #define INGRES_NO_STMT 30130
53 * ingerr: Called when Ingres indicates an error.
58 EXEC SQL BEGIN DECLARE SECTION;
60 EXEC SQL END DECLARE SECTION;
61 ingres_errno = -sqlca.sqlcode;
63 switch (ingres_errno) {
64 case INGRES_BAD_DATE1:
65 case INGRES_BAD_DATE2:
69 mr_errcode = MR_DEADLOCK;
70 com_err(whoami, 0, "INGRES deadlock detected");
73 /* May be something other than timeout! #39100 is "Unknown error"
74 * Really should parse the error message enough to decide if it's a timeout */
76 com_err(whoami, 0, "timed out getting lock");
78 /* These should never come up unless someone breaks the query table */
79 case INGRES_NO_CURSOR:
80 if (mr_errcode != MR_BUSY &&
81 mr_errcode != MR_DEADLOCK)
82 mr_errcode = MR_INTERNAL;
83 com_err(whoami, 0, "Cursor not opened");
86 mr_errcode = MR_INTERNAL;
87 com_err(whoami, 0, "Statement not declared");
89 case INGRES_BAD_COLUMN:
90 mr_errcode = MR_INTERNAL;
91 com_err(whoami, 0, "Bad column name in query table");
94 mr_errcode = MR_INTERNAL;
95 com_err(whoami, 0, "Error in SQL assignment statement");
99 mr_errcode = MR_INGRES_ERR;
100 com_err(whoami, MR_INGRES_ERR, " code %d\n", ingres_errno);
101 EXEC SQL INQUIRE_SQL(:err_msg = errortext);
102 com_err(whoami, 0, "SQL error text = %s", err_msg);
103 critical_alert("MOIRA", "Moira server encountered INGRES ERROR %d", ingres_errno);
107 /* This is declarative, not executed. Applies from here on, in this file. */
108 EXEC SQL WHENEVER SQLERROR CALL ingerr;
110 int mr_open_database()
113 MR_SQLDA_T *mr_alloc_SQLDA();
114 static first_open = 1;
119 /* initialize local argv */
120 for (i = 0; i < 16; i++)
121 Argv[i] = malloc(ARGLEN);
123 SQLDA = mr_alloc_SQLDA();
132 /* open the database */
134 EXEC SQL CONNECT :database;
136 return (ingres_errno);
137 EXEC SQL set lockmode session where level = table, timeout = :query_timeout;
138 EXEC SQL set lockmode on capacls where readlock = shared;
139 EXEC SQL set lockmode on alias where readlock = shared;
142 EXEC SQL DATABASE moira
148 EXEC SQL SELECT SIZE(signature) INTO :mr_sig_length FROM users WHERE users_id=0; /* Harmless on second open */
149 EXEC SQL COMMIT WORK;
156 int mr_close_database()
164 mr_check_access(cl, name, argc, argv_ro)
171 struct query *get_query_by_name();
176 q = get_query_by_name(name, cl->args->mr_version_no);
177 if (q == (struct query *)0)
178 return(MR_NO_HANDLE);
180 return(mr_verify_query(cl, q, argc, argv_ro));
183 mr_process_query(cl, name, argc, argv_ro, action, actarg)
191 register struct query *q;
193 register struct validate *v;
198 EXEC SQL BEGIN DECLARE SECTION;
200 EXEC SQL END DECLARE SECTION;
201 struct save_queue *sq;
202 struct query *get_query_by_name();
204 struct save_queue *sq_create();
210 /* list queries command */
211 if (!strcmp(name, "_list_queries")) {
212 list_queries(cl->args->mr_version_no, action, actarg);
216 /* help query command */
217 if (!strcmp(name, "_help")) {
220 q = get_query_by_name(argv_ro[0], cl->args->mr_version_no);
221 if (q == (struct query *)0) return(MR_NO_HANDLE);
222 help_query(q, action, actarg);
226 /* get query structure, return error if named query does not exist */
227 q = get_query_by_name(name, cl->args->mr_version_no);
228 if (q == (struct query *)0) return(MR_NO_HANDLE);
231 /* setup argument vector, verify access and arguments */
232 if ((status = mr_verify_query(cl, q, argc, argv_ro)) != MR_SUCCESS)
235 /* perform any special query pre-processing */
236 if (v && v->pre_rtn) {
237 status = (*v->pre_rtn)(q, Argv, cl, 0);
238 if (status != MR_SUCCESS)
244 /* for queries that do not permit wildcarding, check if row
247 status = validate_row(q, Argv, v);
248 if (status != MR_EXISTS) break;
251 /* build "where" clause if needed */
253 build_qual(q->qual, q->argc, Argv, qual);
259 /* build "sort" clause if needed */
260 if (v && v->valobj) {
261 psort = build_sort(v, sort);
266 /* if there is a followup routine, then we must save the results */
267 /* of the first query for use by the followup routine */
268 /* if q->rvar = NULL, perform post_rtn only */
270 if (v && v->post_rtn) {
272 status = do_retrieve(q, pqual, psort, sq_save_args, sq);
273 if (status != MR_SUCCESS) {
277 status = (*v->post_rtn)(q, sq, v, action, actarg, cl);
279 /* normal retrieve */
280 status = do_retrieve(q, pqual, psort, action, actarg);
282 if (status != MR_SUCCESS) break;
284 status = (*v->post_rtn)(q, Argv, cl, action, actarg);
290 /* see if row already exists */
292 status = validate_row(q, Argv, v);
293 if (status != MR_EXISTS) break;
296 /* build "where" clause and perform update */
297 /* if q->rvar = NULL, perform post_rtn only */
299 build_qual(q->qual, q->argc, Argv, qual);
300 incremental_before(q->rtable, qual, argv_ro);
301 status = do_update(q, &Argv[q->argc], qual, action, actarg);
302 incremental_after(q->rtable, qual, argv_ro);
303 if (status != MR_SUCCESS) break;
304 flush_name(argv_ro[0], q->rtable);
305 table_name = q->rtable;
306 if (strcmp(q->shortname, "sshi") && strcmp(q->shortname, "ssif")) {
307 EXEC SQL UPDATE tblstats
308 SET updates = updates + 1, modtime = 'now'
309 WHERE table_name = :table_name;
313 /* execute followup routine (if any) */
314 if (v->post_rtn) status = (*v->post_rtn)(q, Argv, cl);
319 /* see if row already exists */
321 status = validate_row(q, Argv, v);
322 if (status != MR_NO_MATCH) break;
325 /* build "where" clause if needed */
327 build_qual(q->qual, q->argc, Argv, qual);
333 /* perform the append */
334 /* if q->rvar = NULL, perform post_rtn only */
336 incremental_clear_before();
337 status = do_append(q, &Argv[q->argc], pqual, action, actarg);
338 if (status != MR_SUCCESS) break;
339 if (v && v->object_id) {
340 sprintf(qual, "%s.%s = %s",q->rvar, v->object_id,
341 Argv[q->argc+q->vcnt]);
342 incremental_after(q->rtable, qual, argv_ro);
344 incremental_after(q->rtable, pqual, argv_ro);
346 table_name = q->rtable;
347 EXEC SQL UPDATE tblstats
348 SET appends = appends + 1, modtime = 'now'
349 WHERE table_name = :table_name;
352 /* execute followup routine */
353 if (v->post_rtn) status = (*v->post_rtn)(q, Argv, cl);
357 /* see if row already exists */
359 status = validate_row(q, Argv, v);
360 if (status != MR_EXISTS) break;
363 /* build "where" clause and perform delete */
364 /* if q->rvar = NULL, perform post_rtn only */
366 build_qual(q->qual, q->argc, Argv, qual);
367 table_name = q->rtable;
368 incremental_before(q->rtable, qual, argv_ro);
369 status = do_delete(q, qual, action, actarg);
370 incremental_clear_after();
371 if (status != MR_SUCCESS) break;
372 flush_name(argv_ro[0], q->rtable);
373 EXEC SQL UPDATE tblstats
374 SET deletes = deletes + 1, modtime = 'now'
375 WHERE table_name = :table_name;
378 /* execute followup routine */
379 if (v->post_rtn) status = (*v->post_rtn)(q, Argv, cl);
385 if (status == MR_SUCCESS && ingres_errno != 0) {
386 com_err(whoami, MR_INTERNAL, "Server didn't notice INGRES ERROR %d",
391 if (q->type == RETRIEVE) {
392 EXEC SQL COMMIT WORK;
394 if (status == MR_SUCCESS) {
395 EXEC SQL COMMIT WORK;
401 fprintf(journal, "%% %s %s %s",
402 cl->clname, cl->entity, ctime(&now));
403 fprintf(journal, "%s[%d] ", q->name, cl->args->mr_version_no);
404 for (i = 0; i < argc; i++) {
408 requote(buf, argv_ro[i], sizeof(buf));
414 incremental_update();
417 if (ingres_errno != INGRES_DEADLOCK) {
418 EXEC SQL ROLLBACK WORK;
423 cache_commit(); /* commit following abort is safe */
425 if (status != MR_SUCCESS && log_flags & LOG_RES)
426 com_err(whoami, status, " (Query failed)");
430 build_qual(fmt, argc, argv, qual)
441 for (i = 0; i < argc; i++) {
443 if (c++ == (char *)0) return(MR_ARGS);
447 *(int *)&args[i] = *(int *)argv[i]; /* sigh */
449 return(MR_INGRES_ERR);
451 if (c = index(c, '%')) {
452 args[i] = args[i - 1];
461 sprintf(qual, fmt, args[0]);
465 sprintf(qual, fmt, args[0], args[1]);
469 sprintf(qual, fmt, args[0], args[1], args[2]);
473 sprintf(qual, fmt, args[0], args[1], args[2], args[3]);
477 com_err(whoami, MR_INTERNAL,
478 "Internal arg count error processing query");
486 register struct validate *v;
489 register struct valobj *vo;
492 #ifdef _DEBUG_MALLOC_INC
501 if (vo->type == V_SORT) {
502 sprintf(elem, "%d", vo->index + 1); /* Result column number */
503 if (*sort) strcat(sort, ", ");
509 return ((*sort) ? sort : 0);
513 /* Build arguement vector, verify query and arguments */
517 mr_verify_query(cl, q, argc, argv_ro)
525 register struct validate *v = q->validate;
527 register char *to,*fr,*stop;
531 /* copy the arguments into a local argv that we can modify */
532 if (argc >= QMAXARGS)
534 for (i = 0; i < argc; i++) {
535 /* Single quotes must be doubled for SQL */
536 for (to=Argv[i], fr=argv_ro[i], stop=to+ARGLEN; (*fr) && (to<stop);) {
542 return(MR_ARG_TOO_LONG);
549 /* check initial query access */
550 status = check_query_access(q, Argv, cl);
551 if (status != MR_SUCCESS && status != MR_PERM)
553 if (status == MR_SUCCESS)
556 /* check argument count */
558 if (q->type == UPDATE || q->type == APPEND) argreq += q->vcnt;
559 if (argc != argreq) return(MR_ARGS);
561 /* validate arguments */
562 if (v && v->valobj) {
563 status = validate_fields(q, Argv, v->valobj, v->objcnt);
564 if (status != MR_SUCCESS) return(status);
567 /* perform special query access check */
568 if (!privileged && v && v->acs_rtn) {
569 status = (*v->acs_rtn)(q, Argv, cl);
570 if (status != MR_SUCCESS && status != MR_PERM)
572 if (status == MR_SUCCESS)
576 return(privileged ? MR_SUCCESS : MR_PERM);
580 /* This routine caches info from the database. Each query acl is stored
581 * in the query structure, and whether that acl contains everybody.
584 check_query_access(q, argv, cl)
589 EXEC SQL BEGIN DECLARE SECTION;
595 EXEC SQL END DECLARE SECTION;
600 /* initialize default uid */
602 EXEC SQL SELECT users_id INTO :def_uid FROM users WHERE login='default';
605 /* get query access control list */
610 EXEC SQL SELECT list_id INTO :acl_id FROM capacls WHERE tag = :name;
611 if (sqlca.sqlcode < 0) return(MR_INGRES_ERR);
612 if (sqlca.sqlcode == 100) return(MR_PERM);
615 /* check for default access */
616 EXEC SQL SELECT member_id INTO :acl_id FROM imembers
617 WHERE list_id = :acl_id AND member_type = 'USER'
618 AND member_id = :def_uid;
619 if (sqlca.sqlerrd[2] == 0)
628 if (get_client(cl, &client_type, &client_id) != MR_SUCCESS)
630 if (find_member("LIST", acl_id, client_type, client_id, 0))
637 get_client(cl, client_type, client_id)
642 if (cl->users_id > 0) {
643 *client_id = cl->users_id;
644 *client_type = "USER";
648 if (cl->client_id < 0) {
649 *client_id = -cl->users_id;
650 *client_type = "KERBEROS";
657 find_member(list_type, list_id, member_type, member_id)
659 EXEC SQL BEGIN DECLARE SECTION;
663 EXEC SQL END DECLARE SECTION;
665 EXEC SQL BEGIN DECLARE SECTION;
667 EXEC SQL END DECLARE SECTION;
669 if (!strcmp(strtrim(list_type), strtrim(member_type)) &&
670 list_id == member_id)
673 /* see if client is a direct member of list */
675 EXEC SQL SELECT member_id INTO :flag FROM imembers
676 WHERE list_id = :list_id AND member_type = :member_type
677 AND member_id = :member_id;
678 if(flag!=0) flag=1; /** Not strictly necessary */
679 if (sqlca.sqlcode == 0)
685 do_retrieve(q, pqual, psort, action, actarg)
686 register struct query *q;
687 EXEC SQL BEGIN DECLARE SECTION;
690 EXEC SQL END DECLARE SECTION;
694 static char **vaddrs = (char **)NULL;
700 if ((vaddrs = (char **)malloc(sizeof(char *) * QMAXARGS)) == NULL) {
701 com_err(whoami, MR_NO_MEM, "setting up static argv");
704 for (i = 0; i < QMAXARGS; i++) {
705 vaddrs[i]=SQLDA->sqlvar[i].sqldata;
709 build_sql_stmt(stmt_buf,"SELECT",q->tlist,vaddrs,pqual);
710 if(psort) { strcat(stmt_buf," ORDER BY "); strcat(stmt_buf,psort); }
711 EXEC SQL PREPARE stmt INTO :SQLDA USING NAMES FROM :stmt_buf;
714 EXEC SQL DECLARE csr001 CURSOR FOR stmt;
715 EXEC SQL OPEN csr001;
718 EXEC SQL FETCH csr001 USING DESCRIPTOR :SQLDA;
719 if(sqlca.sqlcode != 0) break;
720 (*action)(q->vcnt, vaddrs, actarg);
723 EXEC SQL CLOSE csr001;
725 if (mr_errcode) return(mr_errcode);
726 return ((rowcount == 0) ? MR_NO_MATCH : MR_SUCCESS);
729 char *sqlstrstr(str,pat)
733 register char *p=pat;
736 if(*str=='\'') { /* Skip over single-quote delimited substrings */
737 while(*++str && (*str!='\''))
744 while(*++p && (*++s==*p))
746 if(*p) p=pat; /* failed */
748 } while(*p && *++str);
754 void optimize_sql_stmt(buf)
757 char *point=buf, *pat, *eopat, *esc1, *esc2, *csr;
759 for(point=buf; point=sqlstrstr(point,"LIKE"); point++) {
760 /* Now pointing to string "LIKE" */
762 /* Look at next word */
763 for(pat=point+4; *pat==' '; pat++) ;
765 /* Is it a single-quote delimited string? */
766 if(*pat!='\'') continue;
768 /* look for "escape" clause - save escape character */
769 /* 1. Find end of pattern */
770 for(eopat=pat+1; 1; eopat++) {
772 if(eopat[1]=='\'') /* single-quote is self-escaping */
779 /* 2. Look at next word */
780 for(esc1=eopat; *++esc1==' ';) ;
782 /* 3. esc1=0 if not "ESCAPE '?'", where the ? may be any character. */
783 if(strncmp(esc1,"ESCAPE",6)) esc1=NULL;
786 for(esc2=esc1+6; *esc2==' '; esc2++) ;
788 if(*esc2++!='\'') continue; /* Bad SQL syntax. Skip. */
789 /* esc2 now points at the escape character itself */
790 if(esc2[1]!='\'') continue; /* Weird escape string. Skip. */
795 /* Is pattern free from special characters? */
796 for(csr=pat; csr<eopat; csr++)
797 if((*csr=='%') || (*csr=='_') || (*csr==*esc2)) break;
798 if(csr!=eopat) continue; /* Uses pattern matching. Skip. */
800 /* Optimize the query statement */
801 /* 1. Change "LIKE" to " = " */
802 memcpy(point," = ",4);
804 /* 2. Change "ESCAPE" to " " */
807 /* 3. Change "'*'" to " " */
808 /* (Changes '''' to " ") */
809 if(esc2) memset(esc2-1,' ',(*esc2=='\'')?4:3);
814 build_sql_stmt(result_buf,cmd,targetlist,argv,qual)
821 char fmt_buf[MR_STMTBUF_LEN];
822 register char *res, *fmt;
825 sprintf(fmt_buf,"%s %s WHERE %s",cmd,targetlist,qual);
827 sprintf(fmt_buf,"%s %s",cmd,targetlist);
829 for(res=result_buf, fmt=fmt_buf; *fmt; fmt++) {
833 case '%': /* %% -> % */
845 sprintf(res,"%d",*(int *)*argv++); /* print to result buffer */
848 default: /* Swallow other %? pairs */
852 } else *res++ = *fmt; /* text -> result buffer */
856 optimize_sql_stmt(result_buf);
859 do_update(q, argv, qual, action, actarg)
860 register struct query *q;
866 build_sql_stmt(stmt_buf,"UPDATE",q->tlist,argv,qual);
867 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
868 if (mr_errcode) return(mr_errcode);
872 do_append(q, argv, pqual, action, actarg)
873 register struct query *q;
879 build_sql_stmt(stmt_buf,"INSERT",q->tlist,argv,pqual);
880 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
881 if (mr_errcode) return(mr_errcode);
885 do_delete(q, qual, action, actarg)
886 register struct query *q;
891 sprintf(stmt_buf,"DELETE FROM %s WHERE %s",q->rtable,qual);
892 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
893 if (mr_errcode) return(mr_errcode);
899 ** set_next_object_id - set next object id in values table
901 ** Inputs: object - object name in values table and in objects
902 ** table - name of table objects are found in
903 ** limit - should the ID be range limited
905 ** - called before an APPEND operation to set the next object id to
906 ** be used for the new record to the next free value
910 set_next_object_id(object, table_name, limit)
911 EXEC SQL BEGIN DECLARE SECTION;
915 EXEC SQL END DECLARE SECTION;
917 EXEC SQL BEGIN DECLARE SECTION;
919 EXEC SQL END DECLARE SECTION;
922 EXEC SQL SELECT value INTO :value FROM numvalues WHERE name = :object;
923 if (sqlca.sqlerrd[2] != 1)
926 starting_value=value;
928 if (limit && value > MAX_ID_VALUE)
929 value = MIN_ID_VALUE;
931 sprintf(stmt_buf,"SELECT %s FROM %s WHERE %s=%d",object,table_name,object,value);
932 EXEC SQL PREPARE stmt INTO :SQLDA USING NAMES FROM :stmt_buf;
935 EXEC SQL DECLARE csr002 CURSOR FOR stmt;
936 EXEC SQL OPEN csr002;
937 EXEC SQL FETCH csr002 USING DESCRIPTOR :SQLDA;
938 if (sqlca.sqlcode < 0) return(mr_errcode);
939 if (sqlca.sqlcode == 100) break;
941 EXEC SQL CLOSE csr002;
943 if (limit && value == starting_value) {
944 com_err(whoami,0,"All id values have been used");
948 EXEC SQL CLOSE csr002;
951 com_err(whoami, 0, "setting ID %s to %d", object, value);
952 EXEC SQL UPDATE numvalues SET value = :value WHERE name = :object;
957 /* Turn a kerberos name into the user's ID of the account that principal
958 * owns. Sets the kerberos ID and user ID.
961 int set_krb_mapping(name, login, ok, kid, uid)
968 EXEC SQL BEGIN DECLARE SECTION;
971 EXEC SQL END DECLARE SECTION;
977 EXEC SQL SELECT km.users_id, km.string_id INTO :u_id, :k_id
978 FROM krbmap km, strings str
979 WHERE km.string_id = str.string_id AND str.string = :krbname;
980 EXEC SQL COMMIT WORK;
982 if (ingres_errno) return(mr_errcode);
984 if (sqlca.sqlerrd[2] == 1) { /* rowcount */
990 if (name_to_id(name, "STRINGS", &k_id) == MR_SUCCESS)
998 if (name_to_id(login, "USERS", uid) != MR_SUCCESS)
1003 if (ingres_errno) return(mr_errcode);
1008 /* For now this just checks the argc's. It should also see that there
1009 * are no duplicate names.
1012 sanity_check_queries()
1015 int maxv = 0, maxa = 0;
1016 #ifdef MULTIPROTOCOLS
1017 extern int QueryCount1, QueryCount2;
1018 extern struct query Queries1[], Queries2[];
1020 extern int QueryCount2;
1021 extern struct query Queries2[];
1022 #endif MULTIPROTOCOLS
1024 #define MAX(x,y) ((x) > (y) ? (x) : (y))
1026 #ifdef MULTIPROTOCOLS
1027 for (i = 0; i < QueryCount1; i++) {
1028 maxv = MAX(maxv, Queries1[i].vcnt);
1029 maxa = MAX(maxa, Queries1[i].argc);
1031 #endif MULTIPROTOCOLS
1032 for (i = 0; i < QueryCount2; i++) {
1033 maxv = MAX(maxv, Queries2[i].vcnt);
1034 maxa = MAX(maxa, Queries2[i].argc);
1036 if (MAX(maxv, maxa) > QMAXARGS) {
1037 com_err(whoami, 0, "A query has more args than QMAXARGS");