3 * Query-processing routines
5 * Copyright (C) 1987-1998 by the Massachusetts Institute of Technology
6 * For copying and distribution information, please see the file
11 #include <mit-copyright.h>
12 #include "mr_server.h"
19 EXEC SQL INCLUDE sqlca; /* SQL Communications Area */
20 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;
33 extern char *table_name[];
34 extern char *sqlbuffer[QMAXARGS];
38 EXEC SQL BEGIN DECLARE SECTION;
39 int query_timeout = 30;
40 char *database = "moira";
41 EXEC SQL END DECLARE SECTION;
45 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[]);
46 int do_retrieve(struct query *q, char *pqual, char *psort,
47 int (*action)(int, char *[], void *), void *actarg);
48 int do_update(struct query *q, char *argv[], char *qual,
49 int (*action)(int, char *[], void *), void *actarg);
50 int do_append(struct query *q, char *argv[], char *pqual,
51 int (*action)(int, char *[], void *), void *actarg);
52 int do_delete(struct query *q, char *qual,
53 int (*action)(int, char *[], void *), void *actarg);
54 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
55 char *argv[], char *qual);
56 char *build_sort(struct validate *v, char *sort);
57 char *sqlstrstr(char *str, char *pat);
58 void optimize_sql_stmt(char *buf);
60 SQLDA *mr_alloc_sqlda(void);
61 void sqlglm(char *, int *, int *);
64 * dbmserr: Called when the DBMS indicates an error.
69 EXEC SQL BEGIN DECLARE SECTION;
71 EXEC SQL END DECLARE SECTION;
72 int bufsize = 256, msglength = 0;
74 dbms_errno = -sqlca.sqlcode;
75 mr_errcode = MR_DBMS_ERR;
76 com_err(whoami, MR_DBMS_ERR, " code %d\n", dbms_errno);
77 sqlglm(err_msg, &bufsize, &msglength);
78 err_msg[msglength] = 0;
79 com_err(whoami, 0, "SQL error text = %s", err_msg);
80 critical_alert("MOIRA", "Moira server encountered DBMS ERROR %d\n%s",
84 /* This is declarative, not executed. Applies from here on, in this file. */
85 EXEC SQL WHENEVER SQLERROR DO dbmserr();
87 int mr_open_database(void)
90 static first_open = 1;
96 /* initialize local argv */
97 for (i = 0; i < 16; i++)
98 Argv[i] = malloc(ARGLEN);
100 mr_sqlda = mr_alloc_sqlda();
109 /* open the database */
110 EXEC SQL CONNECT :database IDENTIFIED BY :database;
115 EXEC SQL SELECT data_length INTO :mr_sig_length FROM user_tab_columns
116 WHERE table_name = 'USERS' and column_name = 'SIGNATURE';
117 EXEC SQL COMMIT WORK;
124 void mr_close_database(void)
127 EXEC SQL COMMIT RELEASE;
130 int mr_check_access(client *cl, char *name, int argc, char *argv_ro[])
137 q = get_query_by_name(name, cl->args->mr_version_no);
141 return mr_verify_query(cl, q, argc, argv_ro);
144 int mr_process_query(client *cl, char *name, int argc, char *argv_ro[],
145 int (*action)(int, char *[], void *), void *actarg)
154 EXEC SQL BEGIN DECLARE SECTION;
156 EXEC SQL END DECLARE SECTION;
157 struct save_queue *sq;
162 /* list queries command */
163 if (!strcmp(name, "_list_queries"))
165 list_queries(cl->args->mr_version_no, action, actarg);
169 /* help query command */
170 if (!strcmp(name, "_help"))
174 q = get_query_by_name(argv_ro[0], cl->args->mr_version_no);
177 help_query(q, action, actarg);
181 /* get query structure, return error if named query does not exist */
182 q = get_query_by_name(name, cl->args->mr_version_no);
187 /* setup argument vector, verify access and arguments */
188 if ((status = mr_verify_query(cl, q, argc, argv_ro)) != MR_SUCCESS)
191 /* perform any special query pre-processing */
194 status = (*v->pre_rtn)(q, Argv, cl);
195 if (status != MR_SUCCESS)
202 /* for queries that do not permit wildcarding, check if row
206 status = validate_row(q, Argv, v);
207 if (status != MR_EXISTS)
211 /* build "where" clause if needed */
214 build_qual(q->qual, q->argc, Argv, qual);
220 /* build "sort" clause if needed */
222 psort = build_sort(v, sort);
226 /* if there is a followup routine, then we must save the results */
227 /* of the first query for use by the followup routine */
228 /* if q->rvar = NULL, perform post_rtn only */
231 if (v && v->post_rtn)
234 status = do_retrieve(q, pqual, psort, sq_save_args, sq);
235 if (status != MR_SUCCESS)
240 status = (*v->post_rtn)(q, sq, v, action, actarg, cl);
244 /* normal retrieve */
245 status = do_retrieve(q, pqual, psort, action, actarg);
247 if (status != MR_SUCCESS)
251 status = (*v->post_rtn)(q, Argv, cl, action, actarg);
256 /* see if row already exists */
259 status = validate_row(q, Argv, v);
260 if (status != MR_EXISTS)
264 /* build "where" clause and perform update */
265 /* if q->rvar = NULL, perform post_rtn only */
268 build_qual(q->qual, q->argc, Argv, qual);
269 incremental_before(q->rtable, qual, argv_ro);
270 status = do_update(q, &Argv[q->argc], qual, action, actarg);
271 incremental_after(q->rtable, qual, argv_ro);
272 if (status != MR_SUCCESS)
274 flush_name(argv_ro[0], q->rtable);
275 table = table_name[q->rtable];
276 if (strcmp(q->shortname, "sshi") && strcmp(q->shortname, "ssif"))
278 EXEC SQL UPDATE tblstats
279 SET updates = updates + 1, modtime = SYSDATE
280 WHERE table_name = :table;
284 /* execute followup routine (if any) */
286 status = (*v->post_rtn)(q, Argv, cl);
291 /* see if row already exists */
294 status = validate_row(q, Argv, v);
295 if (status != MR_NO_MATCH)
299 /* build "where" clause if needed */
302 build_qual(q->qual, q->argc, Argv, qual);
308 /* perform the append */
309 /* if q->rvar = NULL, perform post_rtn only */
312 incremental_clear_before();
313 status = do_append(q, &Argv[q->argc], pqual, action, actarg);
314 if (status != MR_SUCCESS)
316 if (v && v->object_id)
318 sprintf(qual, "%s.%s = %s", q->rvar, v->object_id,
319 Argv[q->argc + q->vcnt]);
320 incremental_after(q->rtable, qual, argv_ro);
323 incremental_after(q->rtable, pqual, argv_ro);
325 table = table_name[q->rtable];
326 EXEC SQL UPDATE tblstats
327 SET appends = appends + 1, modtime = SYSDATE
328 WHERE table_name = :table;
331 /* execute followup routine */
333 status = (*v->post_rtn)(q, Argv, cl);
337 /* see if row already exists */
340 status = validate_row(q, Argv, v);
341 if (status != MR_EXISTS)
345 /* build "where" clause and perform delete */
346 /* if q->rvar = NULL, perform post_rtn only */
349 build_qual(q->qual, q->argc, Argv, qual);
350 table = table_name[q->rtable];
351 incremental_before(q->rtable, qual, argv_ro);
352 status = do_delete(q, qual, action, actarg);
353 incremental_clear_after();
354 if (status != MR_SUCCESS)
356 flush_name(argv_ro[0], q->rtable);
357 EXEC SQL UPDATE tblstats
358 SET deletes = deletes + 1, modtime = SYSDATE
359 WHERE table_name = :table;
362 /* execute followup routine */
364 status = (*v->post_rtn)(q, Argv, cl);
372 if (status == MR_SUCCESS && dbms_errno != 0)
374 com_err(whoami, MR_INTERNAL, "Server didn't notice DBMS ERROR %d",
379 if (q->type == RETRIEVE)
380 EXEC SQL COMMIT WORK;
383 if (status == MR_SUCCESS)
385 EXEC SQL COMMIT WORK;
392 fprintf(journal, "%% %s %s %s",
393 cl->clname, cl->entity, ctime(&now));
394 fprintf(journal, "%s[%ld] ", q->name, cl->args->mr_version_no);
395 for (i = 0; i < argc; i++)
399 requote(buf, argv_ro[i], sizeof(buf));
405 incremental_update();
410 EXEC SQL ROLLBACK WORK;
414 cache_commit(); /* commit following abort is safe */
416 if (status != MR_SUCCESS && log_flags & LOG_RES)
417 com_err(whoami, status, " (Query failed)");
421 void build_qual(char *fmt_buf, int argc, char *argv[], char *qual)
425 for (res = qual, fmt = fmt_buf; *fmt; fmt++)
433 case '%': /* %% -> % */
443 *res++ = '\''; /* double the ' */
450 res += sprintf(res, "%d", *(int *)*argv++);
452 default: /* Swallow other %? pairs */
460 *res++ = *fmt; /* text -> result buffer */
465 char *build_sort(struct validate *v, char *sort)
477 if (vo->type == V_SORT)
479 sprintf(elem, "%d", vo->index + 1); /* Result column number */
487 return (*sort) ? sort : 0;
491 /* Build arguement vector, verify query and arguments */
495 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[])
499 struct validate *v = q->validate;
501 char *to, *fr, *stop;
505 /* check argument count */
507 if (q->type == UPDATE || q->type == APPEND)
512 /* copy the arguments into a local argv that we can modify */
513 for (i = 0; i < argc; i++)
515 for (to = Argv[i], fr = argv_ro[i], stop = to + ARGLEN; (*fr) && (to < stop);)
519 return MR_ARG_TOO_LONG;
526 /* check initial query access */
527 status = check_query_access(q, Argv, cl);
528 if (status != MR_SUCCESS && status != MR_PERM)
530 if (status == MR_SUCCESS)
533 /* validate arguments */
536 status = validate_fields(q, Argv, v->valobj, v->objcnt);
537 if (status != MR_SUCCESS)
541 /* perform special query access check */
542 if (!privileged && v && v->acs_rtn)
544 status = (*v->acs_rtn)(q, Argv, cl);
545 if (status != MR_SUCCESS && status != MR_PERM)
547 if (status == MR_SUCCESS)
551 return privileged ? MR_SUCCESS : MR_PERM;
555 /* This routine caches info from the database. Each query acl is stored
556 * in the query structure, and whether that acl contains everybody.
559 int check_query_access(struct query *q, char *argv[], client *cl)
561 EXEC SQL BEGIN DECLARE SECTION;
565 EXEC SQL END DECLARE SECTION;
567 /* initialize default uid */
569 EXEC SQL SELECT users_id INTO :def_uid FROM users WHERE login = 'default';
571 /* get query access control list */
577 EXEC SQL SELECT list_id INTO :acl_id FROM capacls WHERE tag = :name;
578 if (sqlca.sqlcode < 0)
580 if (sqlca.sqlcode == SQL_NO_MATCH)
584 /* check for default access */
585 EXEC SQL SELECT member_id INTO :acl_id FROM imembers
586 WHERE list_id = :acl_id AND member_type = 'USER'
587 AND member_id = :def_uid;
588 if (sqlca.sqlerrd[2] == 0)
597 if (find_member("LIST", acl_id, cl))
604 int find_member(char *list_type, int list_id, client *cl)
606 EXEC SQL BEGIN DECLARE SECTION;
607 int flag, users_id, client_id;
608 EXEC SQL END DECLARE SECTION;
610 if (!strcmp(strtrim(list_type), "USER") && list_id == cl->users_id)
613 if (!strcmp(strtrim(list_type), "KERBEROS") && list_id == -cl->client_id)
616 /* see if client is a member of list */
618 users_id = cl->users_id;
619 client_id = -cl->client_id;
620 EXEC SQL SELECT COUNT(member_id) INTO :flag FROM imembers
621 WHERE list_id = :list_id
622 AND ( ( member_type = 'USER' AND member_id = :users_id )
623 OR (member_type = 'KERBEROS' AND member_id = :client_id ) );
624 if (sqlca.sqlcode == 0)
630 int do_retrieve(struct query *q, char *pqual, char *psort,
631 int (*action)(int, char *[], void *), void *actarg)
633 build_sql_stmt(stmt_buf, "SELECT", q->tlist, NULL, pqual);
636 strcat(stmt_buf, " ORDER BY ");
637 strcat(stmt_buf, psort);
640 return do_for_all_rows(stmt_buf, q->vcnt, action, actarg);
643 char *sqlstrstr(char *str, char *pat)
649 if (*str == '\'') /* Skip over single-quote delimited substrings */
651 while (*++str && (*str != '\''))
659 while (*++p && (*++s == *p))
662 p = pat; /* failed */
665 while (*p && *++str);
672 void optimize_sql_stmt(char *buf)
674 char *point = buf, *pat, *eopat, *esc1, *esc2, *csr;
676 for (point = buf; (point = sqlstrstr(point, "LIKE")); point++)
678 /* Now pointing to string "LIKE" */
680 /* Look at next word */
681 for (pat = point + 4; *pat == ' '; pat++)
684 /* Is it a single-quote delimited string? */
688 /* look for "escape" clause - save escape character */
689 /* 1. Find end of pattern */
690 for (eopat = pat + 1; 1; eopat++)
694 if (eopat[1] == '\'') /* single-quote is self-escaping */
701 /* 2. Look at next word */
702 for (esc1 = eopat; *++esc1 == ' ';)
705 /* 3. esc1 = 0 if not "ESCAPE '?'", where the ? may be any character. */
706 if (strncmp(esc1, "ESCAPE", 6))
711 for (esc2 = esc1 + 6; *esc2 == ' '; esc2++)
715 continue; /* Bad SQL syntax. Skip. */
716 /* esc2 now points at the escape character itself */
718 continue; /* Weird escape string. Skip. */
723 /* Is pattern free from special characters? */
724 for (csr = pat; csr < eopat; csr++)
726 if ((*csr == '%') || (*csr == '_') || (*csr == *esc2))
730 continue; /* Uses pattern matching. Skip. */
732 /* Optimize the query statement */
733 /* 1. Change "LIKE" to " = " */
734 memcpy(point, " = ", 4);
736 /* 2. Change "ESCAPE" to " " */
739 memset(esc1, ' ', 6);
740 /* 3. Change "'*'" to " " */
741 /* (Changes '''' to " ") */
743 memset(esc2 - 1, ' ', (*esc2 == '\'') ? 4 : 3);
748 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
749 char *argv[], char *qual)
751 char fmt_buf[MR_STMTBUF_LEN];
755 sprintf(fmt_buf, "%s %s WHERE %s", cmd, targetlist, qual);
757 sprintf(fmt_buf, "%s %s", cmd, targetlist);
759 for (res = result_buf, fmt = fmt_buf; *fmt; fmt++)
767 case '%': /* %% -> % */
777 *res++ = '\''; /* double the ' */
784 res += sprintf(res, "%d", *(int *)*argv++);
786 default: /* Swallow other %? pairs */
794 *res++ = *fmt; /* text -> result buffer */
798 optimize_sql_stmt(result_buf);
801 int do_update(struct query *q, char *argv[], char *qual,
802 int (*action)(int, char *[], void *), void *actarg)
804 build_sql_stmt(stmt_buf, "UPDATE", q->tlist, argv, qual);
805 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
811 int do_append(struct query *q, char *argv[], char *pqual,
812 int (*action)(int, char *[], void *), void *actarg)
814 build_sql_stmt(stmt_buf, "INSERT", q->tlist, argv, pqual);
815 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
821 int do_delete(struct query *q, char *qual,
822 int (*action)(int, char *[], void *), void *actarg)
824 sprintf(stmt_buf, "DELETE FROM %s WHERE %s", table_name[q->rtable], qual);
825 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
833 ** set_next_object_id - set next object id in values table
835 ** Inputs: object - object name in values table and in objects
836 ** table - name of table objects are found in
837 ** limit - should the ID be range limited
839 ** - called before an APPEND operation to set the next object id to
840 ** be used for the new record to the next free value
844 int set_next_object_id(char *object, enum tables table, int limit)
846 EXEC SQL BEGIN DECLARE SECTION;
849 EXEC SQL END DECLARE SECTION;
852 EXEC SQL SELECT value INTO :value FROM numvalues WHERE name = :obj;
853 if (sqlca.sqlerrd[2] != 1)
856 starting_value = value;
859 if (limit && value > MAX_ID_VALUE)
860 value = MIN_ID_VALUE;
862 sprintf(stmt_buf, "SELECT %s FROM %s WHERE %s = %d",
863 object, table_name[table], object, value);
865 if (sqlca.sqlcode < 0)
867 if (sqlca.sqlcode == SQL_NO_MATCH)
871 if (limit && value == starting_value)
873 com_err(whoami, 0, "All id values have been used");
879 com_err(whoami, 0, "setting ID %s to %d", object, value);
880 EXEC SQL UPDATE numvalues SET value = :value WHERE name = :obj;
885 /* Turn a kerberos name into the user's ID of the account that principal
886 * owns. Sets the kerberos ID and user ID.
889 int set_krb_mapping(char *name, char *login, int ok, int *kid, int *uid)
891 EXEC SQL BEGIN DECLARE SECTION;
894 EXEC SQL END DECLARE SECTION;
900 EXEC SQL SELECT km.users_id, km.string_id INTO :u_id, :k_id
901 FROM krbmap km, strings str
902 WHERE km.string_id = str.string_id AND str.string = :krbname;
903 EXEC SQL COMMIT WORK;
908 if (sqlca.sqlerrd[2] == 1)
915 if (name_to_id(name, STRINGS_TABLE, &k_id) == MR_SUCCESS)
924 if (name_to_id(login, USERS_TABLE, uid) != MR_SUCCESS)
935 /* For now this just checks the argc's. It should also see that there
936 * are no duplicate names.
939 void sanity_check_queries(void)
942 int maxv = 0, maxa = 0;
943 extern int QueryCount2;
944 extern struct query Queries2[];
946 #define MAX(x, y) ((x) > (y) ? (x) : (y))
948 for (i = 0; i < QueryCount2; i++)
950 maxv = MAX(maxv, Queries2[i].vcnt);
951 maxa = MAX(maxa, Queries2[i].argc);
953 if (MAX(maxv, maxa) > QMAXARGS)
955 com_err(whoami, 0, "A query has more args than QMAXARGS");
961 /* Generically do a SELECT, storing the results in the provided buffers */
963 void dosql(char *buffers[])
965 int i, errcode = 0, errlen;
967 EXEC SQL PREPARE inc_stmt FROM :stmt_buf;
970 EXEC SQL DECLARE inc_crs CURSOR FOR inc_stmt;
971 EXEC SQL OPEN inc_crs;
972 mr_sqlda->N = QMAXARGS;
973 EXEC SQL DESCRIBE SELECT LIST FOR inc_stmt INTO mr_sqlda;
974 mr_sqlda->N = mr_sqlda->F;
975 for (i = 0; i < mr_sqlda->N; i++)
977 mr_sqlda->V[i] = buffers[i];
979 mr_sqlda->L[i] = ARGLEN;
981 EXEC SQL FETCH inc_crs USING DESCRIPTOR mr_sqlda;
983 /* if we got an error from the FETCH, we have to preserve it or the
984 close will reset it and the caller will think nothing happened */
987 errcode = sqlca.sqlcode;
988 errlen = sqlca.sqlerrm.sqlerrml;
991 EXEC SQL CLOSE inc_crs;
994 sqlca.sqlcode = errcode;
995 sqlca.sqlerrm.sqlerrml = errlen;
999 int do_for_all_rows(char *query, int count,
1000 int (*action)(int, char *[], void *), void *actarg)
1002 int i, rowcount = 0;
1003 EXEC SQL BEGIN DECLARE SECTION;
1005 EXEC SQL END DECLARE SECTION;
1007 EXEC SQL PREPARE stmt FROM :q;
1010 EXEC SQL DECLARE curs CURSOR FOR stmt;
1012 mr_sqlda->N = count;
1013 EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO mr_sqlda;
1014 mr_sqlda->N = mr_sqlda->F;
1015 for (i = 0; i < mr_sqlda->N; i++)
1017 mr_sqlda->V[i] = sqlbuffer[i];
1018 mr_sqlda->T[i] = 97;
1019 mr_sqlda->L[i] = ARGLEN;
1024 EXEC SQL FETCH curs USING DESCRIPTOR mr_sqlda;
1027 (*action)(count, sqlbuffer, actarg);
1030 EXEC SQL CLOSE curs;
1034 return (rowcount == 0) ? MR_NO_MATCH : MR_SUCCESS;