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];
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;
44 extern int QueryCount, max_version;
45 extern struct query Queries[];
47 /* Put this in a variable so that we can patch it if necessary */
48 int max_row_count = 4096;
50 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[]);
51 int do_retrieve(struct query *q, char *pqual,
52 int (*action)(int, char *[], void *), void *actarg);
53 int do_update(struct query *q, char *argv[], char *qual,
54 int (*action)(int, char *[], void *), void *actarg);
55 int do_append(struct query *q, char *argv[], char *pqual,
56 int (*action)(int, char *[], void *), void *actarg);
57 int do_delete(struct query *q, char *qual,
58 int (*action)(int, char *[], void *), void *actarg);
59 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
60 char *argv[], char *qual);
62 SQLDA *mr_alloc_sqlda(void);
63 void sqlglm(char *, int *, int *);
66 * dbmserr: Called when the DBMS indicates an error.
71 EXEC SQL BEGIN DECLARE SECTION;
73 EXEC SQL END DECLARE SECTION;
74 int bufsize = 256, msglength = 0;
76 dbms_errno = -sqlca.sqlcode;
77 mr_errcode = MR_DBMS_ERR;
78 com_err(whoami, MR_DBMS_ERR, " code %d\n", dbms_errno);
79 sqlglm(err_msg, &bufsize, &msglength);
80 err_msg[msglength] = 0;
81 com_err(whoami, 0, "SQL error text = %s", err_msg);
82 critical_alert("MOIRA", "Moira server encountered DBMS ERROR %d\n%s",
86 /* This is declarative, not executed. Applies from here on, in this file. */
87 EXEC SQL WHENEVER SQLERROR DO dbmserr();
89 int mr_open_database(void)
92 static int first_open = 1;
98 /* initialize local argv */
99 for (i = 0; i < 16; i++)
100 Argv[i] = xmalloc(MAX_FIELD_WIDTH);
102 mr_sqlda = mr_alloc_sqlda();
111 /* open the database */
112 EXEC SQL CONNECT :database IDENTIFIED BY :database;
117 EXEC SQL SELECT data_length INTO :mr_sig_length FROM user_tab_columns
118 WHERE table_name = 'USERS' and column_name = 'SIGNATURE';
121 EXEC SQL SELECT list_id INTO :proxy_acl FROM capacls
122 WHERE capability = 'proxy';
129 void mr_close_database(void)
132 EXEC SQL COMMIT RELEASE;
135 int mr_check_access(client *cl, char *name, int argc, char *argv_ro[])
142 q = get_query_by_name(name, cl->version);
146 return mr_verify_query(cl, q, argc, argv_ro);
149 int mr_process_query(client *cl, char *name, int argc, char *argv_ro[],
150 int (*action)(int, char *[], void *), void *actarg)
156 EXEC SQL BEGIN DECLARE SECTION;
158 EXEC SQL END DECLARE SECTION;
159 struct save_queue *sq;
164 /* list queries command */
165 if (!strcmp(name, "_list_queries"))
167 list_queries(action, actarg);
171 /* help query command */
172 if (!strcmp(name, "_help"))
176 q = get_query_by_name(argv_ro[0], cl->version);
179 help_query(q, action, actarg);
183 /* get query structure, return error if named query does not exist */
184 q = get_query_by_name(name, cl->version);
189 /* setup argument vector, verify access and arguments */
190 if ((status = mr_verify_query(cl, q, argc, argv_ro)) != MR_SUCCESS)
193 /* perform any special query pre-processing */
196 status = (*v->pre_rtn)(q, Argv, cl);
197 if (status != MR_SUCCESS)
204 /* for queries that do not permit wildcarding, check if row
208 status = validate_row(q, Argv, v);
209 if (status != MR_EXISTS)
213 /* build "where" clause if needed */
215 qual = build_qual(q->qual, q->argc, Argv);
217 /* if there is a followup routine, then we must save the results */
218 /* of the first query for use by the followup routine */
219 /* if q->rvar = NULL, perform post_rtn only */
222 if (v && v->post_rtn)
225 status = do_retrieve(q, qual, sq_save_args, sq);
226 if (status != MR_SUCCESS)
231 while (sq_get_data(sq, &argv))
233 for (i = 0; i < q->vcnt; i++)
240 status = (*v->post_rtn)(q, sq, v, action, actarg, cl);
244 /* normal retrieve */
245 status = do_retrieve(q, qual, 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 qual = build_qual(q->qual, q->argc, Argv);
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 */
301 qual = build_qual(q->qual, q->argc, Argv);
303 /* perform the append */
304 /* if q->rvar = NULL, perform post_rtn only */
307 incremental_clear_before();
308 status = do_append(q, &Argv[q->argc], qual, action, actarg);
309 if (status != MR_SUCCESS)
311 if (v && v->object_id)
313 qual = realloc(qual, 15 + strlen(q->rvar) +
314 strlen(Argv[q->argc + q->vcnt]));
315 sprintf(qual, "%s.%s = %s", q->rvar, v->object_id,
316 Argv[q->argc + q->vcnt]);
317 incremental_after(q->rtable, qual, argv_ro);
320 incremental_after(q->rtable, qual, argv_ro);
322 table = table_name[q->rtable];
323 EXEC SQL UPDATE tblstats
324 SET appends = appends + 1, modtime = SYSDATE
325 WHERE table_name = :table;
328 /* execute followup routine */
330 status = (*v->post_rtn)(q, Argv, cl);
334 /* see if row already exists */
337 status = validate_row(q, Argv, v);
338 if (status != MR_EXISTS)
342 /* build "where" clause and perform delete */
343 /* if q->rvar = NULL, perform post_rtn only */
346 qual = build_qual(q->qual, q->argc, Argv);
347 table = table_name[q->rtable];
348 incremental_before(q->rtable, qual, argv_ro);
349 status = do_delete(q, qual, action, actarg);
350 incremental_clear_after();
351 if (status != MR_SUCCESS)
353 flush_name(argv_ro[0], q->rtable);
354 EXEC SQL UPDATE tblstats
355 SET deletes = deletes + 1, modtime = SYSDATE
356 WHERE table_name = :table;
359 /* execute followup routine */
361 status = (*v->post_rtn)(q, Argv, cl);
371 if (status == MR_SUCCESS && dbms_errno != 0)
373 com_err(whoami, MR_INTERNAL, "Server didn't notice DBMS ERROR %d",
378 if (q->type == RETRIEVE)
379 EXEC SQL COMMIT WORK;
382 if (status == MR_SUCCESS)
384 EXEC SQL COMMIT WORK;
391 fprintf(journal, "%% %s %s %s",
392 cl->clname, cl->entity, ctime(&now));
393 fprintf(journal, "%s ", q->name);
394 for (i = 0; i < argc; i++)
398 buf = requote(argv_ro[i]);
405 incremental_update();
410 EXEC SQL ROLLBACK WORK;
414 cache_commit(); /* commit following abort is safe */
416 if (status != MR_SUCCESS)
417 com_err(whoami, status, " (Query failed)");
421 char *build_qual(char *fmt_buf, int argc, char *argv[])
423 char *res, *result_buf, *fmt, *arg, *like, *p;
425 result_buf = xmalloc(2 * (strlen(fmt_buf) + argc * ARGLEN));
430 like = strstr(fmt, "LIKE");
431 arg = strchr(fmt, '%');
433 /* Look through the format for LIKE expressions and arguments.
434 Substitute in the arguments, simplify the `LIKE's to `='s
435 where possible, and insert ESCAPE clauses where needed */
441 /* only plain text remains */
443 res = strchr(res, '\0');
446 else if (!like || arg < like)
448 /* regular arg: copy up to arg, then substitute */
449 strncpy(res, fmt, arg - fmt);
461 /* copy string, doubling single quotes */
472 res += sprintf(res, "%d", *(int *)*argv++);
477 arg = strchr(fmt, '%');
479 /* LIKE arg: copy over up to the arg, then copy and convert arg */
480 int escape = 0, pattern = 0;
481 char *likepos = res + (like - fmt);
483 strncpy(res, fmt, arg - fmt);
486 /* copy arg, converting UNIX globs to `SQL voodoo', and noting
487 if we'll need an ESCAPE clause */
488 for (p = *argv++; *p; p++)
494 *res++ = '%'; /* need to double for build_sql_stmt */
521 /* if no pattern characters, write over "LIKE" with " = " */
522 if (!pattern && !escape)
523 memcpy(likepos, " = ", 4);
526 while (*fmt && *fmt != ' ')
530 res += sprintf(res, " ESCAPE '*'");
532 arg = strchr(fmt, '%');
533 like = strstr(fmt, "LIKE");
538 result_buf = realloc(result_buf, strlen(result_buf) + 1);
542 /* Build arguement vector, verify query and arguments */
546 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[])
550 struct validate *v = q->validate;
552 char *to, *fr, *stop;
556 /* check argument count */
558 if (q->type == UPDATE || q->type == APPEND)
563 /* copy the arguments into a local argv that we can modify */
564 for (i = 0; i < argc; i++)
566 for (to = Argv[i], fr = argv_ro[i], stop = to + MAX_FIELD_WIDTH; (*fr) && (to < stop);)
570 return MR_ARG_TOO_LONG;
573 if (to > Argv[i] && *--to == '\\')
577 /* check initial query access, unless we're acting as a proxy */
580 status = check_query_access(q, Argv, cl);
581 if (status != MR_SUCCESS && status != MR_PERM)
583 if (status == MR_SUCCESS)
587 /* validate arguments */
590 status = validate_fields(q, Argv, v->valobj, v->objcnt);
591 if (status != MR_SUCCESS)
595 /* perform special query access check */
596 if (!privileged && v && v->acs_rtn)
598 status = (*v->acs_rtn)(q, Argv, cl);
599 if (status != MR_SUCCESS && status != MR_PERM)
601 if (status == MR_SUCCESS)
605 return privileged ? MR_SUCCESS : MR_PERM;
609 /* This routine caches info from the database. Each query acl is stored
610 * in the query structure, and whether that acl contains everybody.
613 int check_query_access(struct query *q, char *argv[], client *cl)
615 EXEC SQL BEGIN DECLARE SECTION;
619 EXEC SQL END DECLARE SECTION;
621 /* initialize default uid */
623 EXEC SQL SELECT users_id INTO :def_uid FROM users WHERE login = 'default';
625 /* get query access control list */
631 EXEC SQL SELECT list_id INTO :acl_id FROM capacls WHERE tag = :name;
632 if (sqlca.sqlcode < 0)
634 if (sqlca.sqlcode == SQL_NO_MATCH)
638 /* check for default access */
639 EXEC SQL SELECT member_id INTO :acl_id FROM imembers
640 WHERE list_id = :acl_id AND member_type = 'USER'
641 AND member_id = :def_uid;
642 if (sqlca.sqlerrd[2] == 0)
651 if (find_member("LIST", acl_id, cl))
657 int find_member(char *list_type, int list_id, client *cl)
659 EXEC SQL BEGIN DECLARE SECTION;
660 int flag, users_id, client_id;
661 EXEC SQL END DECLARE SECTION;
663 if (!strcmp(strtrim(list_type), "USER") && list_id == cl->users_id)
666 if (!strcmp(strtrim(list_type), "KERBEROS") && list_id == -cl->client_id)
669 /* see if client is a member of list */
671 users_id = cl->users_id;
672 client_id = -cl->client_id;
673 EXEC SQL SELECT COUNT(member_id) INTO :flag FROM imembers
674 WHERE list_id = :list_id
675 AND ( ( member_type = 'USER' AND member_id = :users_id )
676 OR (member_type = 'KERBEROS' AND member_id = :client_id ) );
677 if (sqlca.sqlcode == 0)
683 int do_retrieve(struct query *q, char *pqual,
684 int (*action)(int, char *[], void *), void *actarg)
686 build_sql_stmt(stmt_buf, "SELECT", q->tlist, NULL, pqual);
689 strcat(stmt_buf, " ORDER BY ");
690 strcat(stmt_buf, q->sort);
693 return do_for_all_rows(stmt_buf, q->vcnt, action, actarg);
696 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
697 char *argv[], char *qual)
699 char fmt_buf[MR_STMTBUF_LEN];
703 sprintf(fmt_buf, "%s %s WHERE %s", cmd, targetlist, qual);
705 sprintf(fmt_buf, "%s %s", cmd, targetlist);
707 for (res = result_buf, fmt = fmt_buf; *fmt; fmt++)
715 case '%': /* %% -> % */
725 *res++ = '\''; /* double the ' */
732 res += sprintf(res, "%d", *(int *)*argv++);
734 default: /* Swallow other %? pairs */
742 *res++ = *fmt; /* text -> result buffer */
747 int do_update(struct query *q, char *argv[], char *qual,
748 int (*action)(int, char *[], void *), void *actarg)
750 build_sql_stmt(stmt_buf, "UPDATE", q->tlist, argv, qual);
751 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
757 int do_append(struct query *q, char *argv[], char *pqual,
758 int (*action)(int, char *[], void *), void *actarg)
760 build_sql_stmt(stmt_buf, "INSERT", q->tlist, argv, pqual);
761 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
767 int do_delete(struct query *q, char *qual,
768 int (*action)(int, char *[], void *), void *actarg)
770 sprintf(stmt_buf, "DELETE FROM %s WHERE %s", table_name[q->rtable], qual);
771 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
779 ** set_next_object_id - set next object id in values table
781 ** Inputs: object - object name in values table and in objects
782 ** table - name of table objects are found in
783 ** limit - should the ID be range limited
785 ** - called before an APPEND operation to set the next object id to
786 ** be used for the new record to the next free value
790 int set_next_object_id(char *object, enum tables table, int limit)
792 EXEC SQL BEGIN DECLARE SECTION;
795 EXEC SQL END DECLARE SECTION;
798 EXEC SQL SELECT value INTO :value FROM numvalues WHERE name = :obj;
799 if (sqlca.sqlerrd[2] != 1)
802 starting_value = value;
805 #ifdef ULTRIX_ID_HOLE
806 if (limit && value > 31999 && value < 32768)
809 if (limit && value > MAX_ID_VALUE)
810 value = MIN_ID_VALUE;
812 sprintf(stmt_buf, "SELECT %s FROM %s WHERE %s = %d",
813 object, table_name[table], object, value);
815 if (sqlca.sqlcode < 0)
817 if (sqlca.sqlcode == SQL_NO_MATCH)
821 if (limit && value == starting_value)
823 com_err(whoami, 0, "All id values have been used");
828 com_err(whoami, 0, "setting ID %s to %d", object, value);
829 EXEC SQL UPDATE numvalues SET value = :value WHERE name = :obj;
834 /* Turn a kerberos name into the user's ID of the account that principal
835 * owns. Sets the kerberos ID and user ID.
838 int set_krb_mapping(char *name, char *login, int ok, int *kid, int *uid)
840 EXEC SQL BEGIN DECLARE SECTION;
843 EXEC SQL END DECLARE SECTION;
849 EXEC SQL SELECT km.users_id, km.string_id INTO :u_id, :k_id
850 FROM krbmap km, strings str
851 WHERE km.string_id = str.string_id AND str.string = :krbname;
852 EXEC SQL COMMIT WORK;
857 if (sqlca.sqlerrd[2] == 1)
864 if (name_to_id(name, STRINGS_TABLE, &k_id) == MR_SUCCESS)
873 if (name_to_id(login, USERS_TABLE, uid) != MR_SUCCESS)
884 void sanity_check_queries(void)
887 int maxv = 0, maxa = 0;
889 #define MAX(x, y) ((x) > (y) ? (x) : (y))
891 for (i = 0; i < QueryCount; i++)
893 maxv = MAX(maxv, Queries[i].vcnt);
894 maxa = MAX(maxa, Queries[i].argc);
895 max_version = MAX(max_version, Queries[i].version);
897 if (MAX(maxv, maxa) > QMAXARGS)
899 com_err(whoami, 0, "A query has more args than QMAXARGS");
905 /* Generically do a SELECT, storing the results in the provided buffers */
907 void dosql(char *buffers[])
909 int i, errcode = 0, errlen;
911 EXEC SQL PREPARE inc_stmt FROM :stmt_buf;
914 EXEC SQL DECLARE inc_crs CURSOR FOR inc_stmt;
915 EXEC SQL OPEN inc_crs;
916 mr_sqlda->N = QMAXARGS;
917 EXEC SQL DESCRIBE SELECT LIST FOR inc_stmt INTO mr_sqlda;
918 mr_sqlda->N = mr_sqlda->F;
919 for (i = 0; i < mr_sqlda->N; i++)
921 mr_sqlda->V[i] = buffers[i];
923 mr_sqlda->L[i] = MAX_FIELD_WIDTH;
925 EXEC SQL FETCH inc_crs USING DESCRIPTOR mr_sqlda;
927 /* if we got an error from the FETCH, we have to preserve it or the
928 close will reset it and the caller will think nothing happened */
931 errcode = sqlca.sqlcode;
932 errlen = sqlca.sqlerrm.sqlerrml;
935 EXEC SQL CLOSE inc_crs;
938 sqlca.sqlcode = errcode;
939 sqlca.sqlerrm.sqlerrml = errlen;
943 int do_for_all_rows(char *query, int count,
944 int (*action)(int, char *[], void *), void *actarg)
947 EXEC SQL BEGIN DECLARE SECTION;
949 EXEC SQL END DECLARE SECTION;
951 EXEC SQL PREPARE stmt FROM :q;
954 EXEC SQL DECLARE curs CURSOR FOR stmt;
957 EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO mr_sqlda;
958 mr_sqlda->N = mr_sqlda->F;
959 for (i = 0; i < mr_sqlda->N; i++)
961 mr_sqlda->V[i] = sqlbuffer[i];
963 mr_sqlda->L[i] = MAX_FIELD_WIDTH;
966 while (rowcount < max_row_count)
968 EXEC SQL FETCH curs USING DESCRIPTOR mr_sqlda;
971 (*action)(count, sqlbuffer, actarg);
978 if (rowcount == max_row_count)
980 critical_alert("moirad", "attempted query with too many rows");
983 else if (rowcount == 0)