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"
20 EXEC SQL INCLUDE sqlca; /* SQL Communications Area */
21 EXEC SQL INCLUDE sqlda; /* SQL Descriptor Area */
26 EXEC SQL BEGIN DECLARE SECTION;
28 char stmt_buf[MR_STMTBUF_LEN];
29 EXEC SQL END DECLARE SECTION;
32 extern char *table_name[];
33 extern char *sqlbuffer[QMAXARGS];
37 EXEC SQL BEGIN DECLARE SECTION;
38 int query_timeout = 30;
39 char *database = "moira";
40 EXEC SQL END DECLARE SECTION;
44 /* Put this in a variable so that we can patch it if necessary */
45 int max_row_count = 4096;
47 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[]);
48 int do_retrieve(struct query *q, char *pqual,
49 int (*action)(int, char *[], void *), void *actarg);
50 int do_update(struct query *q, char *argv[], char *qual,
51 int (*action)(int, char *[], void *), void *actarg);
52 int do_append(struct query *q, char *argv[], char *pqual,
53 int (*action)(int, char *[], void *), void *actarg);
54 int do_delete(struct query *q, char *qual,
55 int (*action)(int, char *[], void *), void *actarg);
56 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
57 char *argv[], char *qual);
59 SQLDA *mr_alloc_sqlda(void);
60 void sqlglm(char *, int *, int *);
63 * dbmserr: Called when the DBMS indicates an error.
68 EXEC SQL BEGIN DECLARE SECTION;
70 EXEC SQL END DECLARE SECTION;
71 int bufsize = 256, msglength = 0;
73 dbms_errno = -sqlca.sqlcode;
74 mr_errcode = MR_DBMS_ERR;
75 com_err(whoami, MR_DBMS_ERR, " code %d\n", dbms_errno);
76 sqlglm(err_msg, &bufsize, &msglength);
77 err_msg[msglength] = 0;
78 com_err(whoami, 0, "SQL error text = %s", err_msg);
79 critical_alert("MOIRA", "Moira server encountered DBMS ERROR %d\n%s",
83 /* This is declarative, not executed. Applies from here on, in this file. */
84 EXEC SQL WHENEVER SQLERROR DO dbmserr();
86 int mr_open_database(void)
89 static first_open = 1;
95 /* initialize local argv */
96 for (i = 0; i < 16; i++)
97 Argv[i] = xmalloc(MAX_FIELD_WIDTH);
99 mr_sqlda = mr_alloc_sqlda();
108 /* open the database */
109 EXEC SQL CONNECT :database IDENTIFIED BY :database;
114 EXEC SQL SELECT data_length INTO :mr_sig_length FROM user_tab_columns
115 WHERE table_name = 'USERS' and column_name = 'SIGNATURE';
116 EXEC SQL COMMIT WORK;
123 void mr_close_database(void)
126 EXEC SQL COMMIT RELEASE;
129 int mr_check_access(client *cl, char *name, int argc, char *argv_ro[])
136 q = get_query_by_name(name);
140 return mr_verify_query(cl, q, argc, argv_ro);
143 int mr_process_query(client *cl, char *name, int argc, char *argv_ro[],
144 int (*action)(int, char *[], void *), void *actarg)
150 EXEC SQL BEGIN DECLARE SECTION;
152 EXEC SQL END DECLARE SECTION;
153 struct save_queue *sq;
158 /* list queries command */
159 if (!strcmp(name, "_list_queries"))
161 list_queries(action, actarg);
165 /* help query command */
166 if (!strcmp(name, "_help"))
170 q = get_query_by_name(argv_ro[0]);
173 help_query(q, action, actarg);
177 /* get query structure, return error if named query does not exist */
178 q = get_query_by_name(name);
183 /* setup argument vector, verify access and arguments */
184 if ((status = mr_verify_query(cl, q, argc, argv_ro)) != MR_SUCCESS)
187 /* perform any special query pre-processing */
190 status = (*v->pre_rtn)(q, Argv, cl);
191 if (status != MR_SUCCESS)
198 /* for queries that do not permit wildcarding, check if row
202 status = validate_row(q, Argv, v);
203 if (status != MR_EXISTS)
207 /* build "where" clause if needed */
209 qual = build_qual(q->qual, q->argc, Argv);
211 /* if there is a followup routine, then we must save the results */
212 /* of the first query for use by the followup routine */
213 /* if q->rvar = NULL, perform post_rtn only */
216 if (v && v->post_rtn)
219 status = do_retrieve(q, qual, sq_save_args, sq);
220 if (status != MR_SUCCESS)
225 while (sq_get_data(sq, &argv))
227 for (i = 0; i < q->vcnt; i++)
234 status = (*v->post_rtn)(q, sq, v, action, actarg, cl);
238 /* normal retrieve */
239 status = do_retrieve(q, qual, action, actarg);
241 if (status != MR_SUCCESS)
245 status = (*v->post_rtn)(q, Argv, cl, action, actarg);
250 /* see if row already exists */
253 status = validate_row(q, Argv, v);
254 if (status != MR_EXISTS)
258 /* build "where" clause and perform update */
259 /* if q->rvar = NULL, perform post_rtn only */
262 qual = build_qual(q->qual, q->argc, Argv);
263 incremental_before(q->rtable, qual, argv_ro);
264 status = do_update(q, &Argv[q->argc], qual, action, actarg);
265 incremental_after(q->rtable, qual, argv_ro);
266 if (status != MR_SUCCESS)
268 flush_name(argv_ro[0], q->rtable);
269 table = table_name[q->rtable];
270 if (strcmp(q->shortname, "sshi") && strcmp(q->shortname, "ssif"))
272 EXEC SQL UPDATE tblstats
273 SET updates = updates + 1, modtime = SYSDATE
274 WHERE table_name = :table;
278 /* execute followup routine (if any) */
280 status = (*v->post_rtn)(q, Argv, cl);
285 /* see if row already exists */
288 status = validate_row(q, Argv, v);
289 if (status != MR_NO_MATCH)
293 /* build "where" clause if needed */
295 qual = build_qual(q->qual, q->argc, Argv);
297 /* perform the append */
298 /* if q->rvar = NULL, perform post_rtn only */
301 incremental_clear_before();
302 status = do_append(q, &Argv[q->argc], qual, action, actarg);
303 if (status != MR_SUCCESS)
305 if (v && v->object_id)
307 qual = realloc(qual, 15 + strlen(q->rvar) +
308 strlen(Argv[q->argc + q->vcnt]));
309 sprintf(qual, "%s.%s = %s", q->rvar, v->object_id,
310 Argv[q->argc + q->vcnt]);
311 incremental_after(q->rtable, qual, argv_ro);
314 incremental_after(q->rtable, qual, argv_ro);
316 table = table_name[q->rtable];
317 EXEC SQL UPDATE tblstats
318 SET appends = appends + 1, modtime = SYSDATE
319 WHERE table_name = :table;
322 /* execute followup routine */
324 status = (*v->post_rtn)(q, Argv, cl);
328 /* see if row already exists */
331 status = validate_row(q, Argv, v);
332 if (status != MR_EXISTS)
336 /* build "where" clause and perform delete */
337 /* if q->rvar = NULL, perform post_rtn only */
340 qual = build_qual(q->qual, q->argc, Argv);
341 table = table_name[q->rtable];
342 incremental_before(q->rtable, qual, argv_ro);
343 status = do_delete(q, qual, action, actarg);
344 incremental_clear_after();
345 if (status != MR_SUCCESS)
347 flush_name(argv_ro[0], q->rtable);
348 EXEC SQL UPDATE tblstats
349 SET deletes = deletes + 1, modtime = SYSDATE
350 WHERE table_name = :table;
353 /* execute followup routine */
355 status = (*v->post_rtn)(q, Argv, cl);
365 if (status == MR_SUCCESS && dbms_errno != 0)
367 com_err(whoami, MR_INTERNAL, "Server didn't notice DBMS ERROR %d",
372 if (q->type == RETRIEVE)
373 EXEC SQL COMMIT WORK;
376 if (status == MR_SUCCESS)
378 EXEC SQL COMMIT WORK;
385 fprintf(journal, "%% %s %s %s",
386 cl->clname, cl->entity, ctime(&now));
387 fprintf(journal, "%s ", q->name);
388 for (i = 0; i < argc; i++)
392 buf = requote(argv_ro[i]);
399 incremental_update();
404 EXEC SQL ROLLBACK WORK;
408 cache_commit(); /* commit following abort is safe */
410 if (status != MR_SUCCESS)
411 com_err(whoami, status, " (Query failed)");
415 char *build_qual(char *fmt_buf, int argc, char *argv[])
417 char *res, *result_buf, *fmt, *arg, *like, *p;
419 result_buf = xmalloc(2 * (strlen(fmt_buf) + argc * ARGLEN));
424 like = strstr(fmt, "LIKE");
425 arg = strchr(fmt, '%');
427 /* Look through the format for LIKE expressions and arguments.
428 Substitute in the arguments, simplify the `LIKE's to `='s
429 where possible, and insert ESCAPE clauses where needed */
435 /* only plain text remains */
437 res = strchr(res, '\0');
440 else if (!like || arg < like)
442 /* regular arg: copy up to arg, then substitute */
443 strncpy(res, fmt, arg - fmt);
455 /* copy string, doubling single quotes */
466 res += sprintf(res, "%d", *(int *)*argv++);
471 arg = strchr(fmt, '%');
473 /* LIKE arg: copy over up to the arg, then copy and convert arg */
474 int escape = 0, pattern = 0;
475 char *likepos = res + (like - fmt);
477 strncpy(res, fmt, arg - fmt);
480 /* copy arg, converting UNIX globs to `SQL voodoo', and noting
481 if we'll need an ESCAPE clause */
482 for (p = *argv++; *p; p++)
488 *res++ = '%'; /* need to double for build_sql_stmt */
515 /* if no pattern characters, write over "LIKE" with " = " */
516 if (!pattern && !escape)
517 memcpy(likepos, " = ", 4);
520 while (*fmt && *fmt != ' ')
524 res += sprintf(res, " ESCAPE '*'");
526 arg = strchr(fmt, '%');
527 like = strstr(fmt, "LIKE");
532 result_buf = realloc(result_buf, strlen(result_buf) + 1);
536 /* Build arguement vector, verify query and arguments */
540 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[])
544 struct validate *v = q->validate;
546 char *to, *fr, *stop;
550 /* check argument count */
552 if (q->type == UPDATE || q->type == APPEND)
557 /* copy the arguments into a local argv that we can modify */
558 for (i = 0; i < argc; i++)
560 for (to = Argv[i], fr = argv_ro[i], stop = to + MAX_FIELD_WIDTH; (*fr) && (to < stop);)
564 return MR_ARG_TOO_LONG;
567 if (to > Argv[i] && *--to == '\\')
571 /* check initial query access */
572 status = check_query_access(q, Argv, cl);
573 if (status != MR_SUCCESS && status != MR_PERM)
575 if (status == MR_SUCCESS)
578 /* validate arguments */
581 status = validate_fields(q, Argv, v->valobj, v->objcnt);
582 if (status != MR_SUCCESS)
586 /* perform special query access check */
587 if (!privileged && v && v->acs_rtn)
589 status = (*v->acs_rtn)(q, Argv, cl);
590 if (status != MR_SUCCESS && status != MR_PERM)
592 if (status == MR_SUCCESS)
596 return privileged ? MR_SUCCESS : MR_PERM;
600 /* This routine caches info from the database. Each query acl is stored
601 * in the query structure, and whether that acl contains everybody.
604 int check_query_access(struct query *q, char *argv[], client *cl)
606 EXEC SQL BEGIN DECLARE SECTION;
610 EXEC SQL END DECLARE SECTION;
612 /* initialize default uid */
614 EXEC SQL SELECT users_id INTO :def_uid FROM users WHERE login = 'default';
616 /* get query access control list */
622 EXEC SQL SELECT list_id INTO :acl_id FROM capacls WHERE tag = :name;
623 if (sqlca.sqlcode < 0)
625 if (sqlca.sqlcode == SQL_NO_MATCH)
629 /* check for default access */
630 EXEC SQL SELECT member_id INTO :acl_id FROM imembers
631 WHERE list_id = :acl_id AND member_type = 'USER'
632 AND member_id = :def_uid;
633 if (sqlca.sqlerrd[2] == 0)
642 if (find_member("LIST", acl_id, cl))
649 int find_member(char *list_type, int list_id, client *cl)
651 EXEC SQL BEGIN DECLARE SECTION;
652 int flag, users_id, client_id;
653 EXEC SQL END DECLARE SECTION;
655 if (!strcmp(strtrim(list_type), "USER") && list_id == cl->users_id)
658 if (!strcmp(strtrim(list_type), "KERBEROS") && list_id == -cl->client_id)
661 /* see if client is a member of list */
663 users_id = cl->users_id;
664 client_id = -cl->client_id;
665 EXEC SQL SELECT COUNT(member_id) INTO :flag FROM imembers
666 WHERE list_id = :list_id
667 AND ( ( member_type = 'USER' AND member_id = :users_id )
668 OR (member_type = 'KERBEROS' AND member_id = :client_id ) );
669 if (sqlca.sqlcode == 0)
675 int do_retrieve(struct query *q, char *pqual,
676 int (*action)(int, char *[], void *), void *actarg)
678 build_sql_stmt(stmt_buf, "SELECT", q->tlist, NULL, pqual);
681 strcat(stmt_buf, " ORDER BY ");
682 strcat(stmt_buf, q->sort);
685 return do_for_all_rows(stmt_buf, q->vcnt, action, actarg);
688 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
689 char *argv[], char *qual)
691 char fmt_buf[MR_STMTBUF_LEN];
695 sprintf(fmt_buf, "%s %s WHERE %s", cmd, targetlist, qual);
697 sprintf(fmt_buf, "%s %s", cmd, targetlist);
699 for (res = result_buf, fmt = fmt_buf; *fmt; fmt++)
707 case '%': /* %% -> % */
717 *res++ = '\''; /* double the ' */
724 res += sprintf(res, "%d", *(int *)*argv++);
726 default: /* Swallow other %? pairs */
734 *res++ = *fmt; /* text -> result buffer */
739 int do_update(struct query *q, char *argv[], char *qual,
740 int (*action)(int, char *[], void *), void *actarg)
742 build_sql_stmt(stmt_buf, "UPDATE", q->tlist, argv, qual);
743 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
749 int do_append(struct query *q, char *argv[], char *pqual,
750 int (*action)(int, char *[], void *), void *actarg)
752 build_sql_stmt(stmt_buf, "INSERT", q->tlist, argv, pqual);
753 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
759 int do_delete(struct query *q, char *qual,
760 int (*action)(int, char *[], void *), void *actarg)
762 sprintf(stmt_buf, "DELETE FROM %s WHERE %s", table_name[q->rtable], qual);
763 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
771 ** set_next_object_id - set next object id in values table
773 ** Inputs: object - object name in values table and in objects
774 ** table - name of table objects are found in
775 ** limit - should the ID be range limited
777 ** - called before an APPEND operation to set the next object id to
778 ** be used for the new record to the next free value
782 int set_next_object_id(char *object, enum tables table, int limit)
784 EXEC SQL BEGIN DECLARE SECTION;
787 EXEC SQL END DECLARE SECTION;
790 EXEC SQL SELECT value INTO :value FROM numvalues WHERE name = :obj;
791 if (sqlca.sqlerrd[2] != 1)
794 starting_value = value;
797 if (limit && value > MAX_ID_VALUE)
798 value = MIN_ID_VALUE;
800 sprintf(stmt_buf, "SELECT %s FROM %s WHERE %s = %d",
801 object, table_name[table], object, value);
803 if (sqlca.sqlcode < 0)
805 if (sqlca.sqlcode == SQL_NO_MATCH)
809 if (limit && value == starting_value)
811 com_err(whoami, 0, "All id values have been used");
816 com_err(whoami, 0, "setting ID %s to %d", object, value);
817 EXEC SQL UPDATE numvalues SET value = :value WHERE name = :obj;
822 /* Turn a kerberos name into the user's ID of the account that principal
823 * owns. Sets the kerberos ID and user ID.
826 int set_krb_mapping(char *name, char *login, int ok, int *kid, int *uid)
828 EXEC SQL BEGIN DECLARE SECTION;
831 EXEC SQL END DECLARE SECTION;
837 EXEC SQL SELECT km.users_id, km.string_id INTO :u_id, :k_id
838 FROM krbmap km, strings str
839 WHERE km.string_id = str.string_id AND str.string = :krbname;
840 EXEC SQL COMMIT WORK;
845 if (sqlca.sqlerrd[2] == 1)
852 if (name_to_id(name, STRINGS_TABLE, &k_id) == MR_SUCCESS)
861 if (name_to_id(login, USERS_TABLE, uid) != MR_SUCCESS)
872 /* For now this just checks the argc's. It should also see that there
873 * are no duplicate names.
876 void sanity_check_queries(void)
879 int maxv = 0, maxa = 0;
880 extern int QueryCount2;
881 extern struct query Queries2[];
883 #define MAX(x, y) ((x) > (y) ? (x) : (y))
885 for (i = 0; i < QueryCount2; i++)
887 maxv = MAX(maxv, Queries2[i].vcnt);
888 maxa = MAX(maxa, Queries2[i].argc);
890 if (MAX(maxv, maxa) > QMAXARGS)
892 com_err(whoami, 0, "A query has more args than QMAXARGS");
898 /* Generically do a SELECT, storing the results in the provided buffers */
900 void dosql(char *buffers[])
902 int i, errcode = 0, errlen;
904 EXEC SQL PREPARE inc_stmt FROM :stmt_buf;
907 EXEC SQL DECLARE inc_crs CURSOR FOR inc_stmt;
908 EXEC SQL OPEN inc_crs;
909 mr_sqlda->N = QMAXARGS;
910 EXEC SQL DESCRIBE SELECT LIST FOR inc_stmt INTO mr_sqlda;
911 mr_sqlda->N = mr_sqlda->F;
912 for (i = 0; i < mr_sqlda->N; i++)
914 mr_sqlda->V[i] = buffers[i];
916 mr_sqlda->L[i] = MAX_FIELD_WIDTH;
918 EXEC SQL FETCH inc_crs USING DESCRIPTOR mr_sqlda;
920 /* if we got an error from the FETCH, we have to preserve it or the
921 close will reset it and the caller will think nothing happened */
924 errcode = sqlca.sqlcode;
925 errlen = sqlca.sqlerrm.sqlerrml;
928 EXEC SQL CLOSE inc_crs;
931 sqlca.sqlcode = errcode;
932 sqlca.sqlerrm.sqlerrml = errlen;
936 int do_for_all_rows(char *query, int count,
937 int (*action)(int, char *[], void *), void *actarg)
940 EXEC SQL BEGIN DECLARE SECTION;
942 EXEC SQL END DECLARE SECTION;
944 EXEC SQL PREPARE stmt FROM :q;
947 EXEC SQL DECLARE curs CURSOR FOR stmt;
950 EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO mr_sqlda;
951 mr_sqlda->N = mr_sqlda->F;
952 for (i = 0; i < mr_sqlda->N; i++)
954 mr_sqlda->V[i] = sqlbuffer[i];
956 mr_sqlda->L[i] = MAX_FIELD_WIDTH;
959 while (rowcount < max_row_count)
961 EXEC SQL FETCH curs USING DESCRIPTOR mr_sqlda;
964 (*action)(count, sqlbuffer, actarg);
971 if (rowcount == max_row_count)
973 critical_alert("moirad", "attempted query with too many rows");
976 else if (rowcount == 0)