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;
45 /* Put this in a variable so that we can patch it if necessary */
46 int max_row_count = 4096;
48 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[]);
49 int do_retrieve(struct query *q, char *pqual,
50 int (*action)(int, char *[], void *), void *actarg);
51 int do_update(struct query *q, char *argv[], char *qual,
52 int (*action)(int, char *[], void *), void *actarg);
53 int do_append(struct query *q, char *argv[], char *pqual,
54 int (*action)(int, char *[], void *), void *actarg);
55 int do_delete(struct query *q, char *qual,
56 int (*action)(int, char *[], void *), void *actarg);
57 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
58 char *argv[], char *qual);
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] = xmalloc(MAX_FIELD_WIDTH);
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';
119 EXEC SQL SELECT list_id INTO :proxy_acl FROM capacls
120 WHERE capability = 'proxy';
127 void mr_close_database(void)
130 EXEC SQL COMMIT RELEASE;
133 int mr_check_access(client *cl, char *name, int argc, char *argv_ro[])
140 q = get_query_by_name(name);
144 return mr_verify_query(cl, q, argc, argv_ro);
147 int mr_process_query(client *cl, char *name, int argc, char *argv_ro[],
148 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(action, actarg);
169 /* help query command */
170 if (!strcmp(name, "_help"))
174 q = get_query_by_name(argv_ro[0]);
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);
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 */
213 qual = build_qual(q->qual, q->argc, Argv);
215 /* if there is a followup routine, then we must save the results */
216 /* of the first query for use by the followup routine */
217 /* if q->rvar = NULL, perform post_rtn only */
220 if (v && v->post_rtn)
223 status = do_retrieve(q, qual, sq_save_args, sq);
224 if (status != MR_SUCCESS)
229 while (sq_get_data(sq, &argv))
231 for (i = 0; i < q->vcnt; i++)
238 status = (*v->post_rtn)(q, sq, v, action, actarg, cl);
242 /* normal retrieve */
243 status = do_retrieve(q, qual, action, actarg);
245 if (status != MR_SUCCESS)
249 status = (*v->post_rtn)(q, Argv, cl, action, actarg);
254 /* see if row already exists */
257 status = validate_row(q, Argv, v);
258 if (status != MR_EXISTS)
262 /* build "where" clause and perform update */
263 /* if q->rvar = NULL, perform post_rtn only */
266 qual = build_qual(q->qual, q->argc, Argv);
267 incremental_before(q->rtable, qual, argv_ro);
268 status = do_update(q, &Argv[q->argc], qual, action, actarg);
269 incremental_after(q->rtable, qual, argv_ro);
270 if (status != MR_SUCCESS)
272 flush_name(argv_ro[0], q->rtable);
273 table = table_name[q->rtable];
274 if (strcmp(q->shortname, "sshi") && strcmp(q->shortname, "ssif"))
276 EXEC SQL UPDATE tblstats
277 SET updates = updates + 1, modtime = SYSDATE
278 WHERE table_name = :table;
282 /* execute followup routine (if any) */
284 status = (*v->post_rtn)(q, Argv, cl);
289 /* see if row already exists */
292 status = validate_row(q, Argv, v);
293 if (status != MR_NO_MATCH)
297 /* build "where" clause if needed */
299 qual = build_qual(q->qual, q->argc, Argv);
301 /* perform the append */
302 /* if q->rvar = NULL, perform post_rtn only */
305 incremental_clear_before();
306 status = do_append(q, &Argv[q->argc], qual, action, actarg);
307 if (status != MR_SUCCESS)
309 if (v && v->object_id)
311 qual = realloc(qual, 15 + strlen(q->rvar) +
312 strlen(Argv[q->argc + q->vcnt]));
313 sprintf(qual, "%s.%s = %s", q->rvar, v->object_id,
314 Argv[q->argc + q->vcnt]);
315 incremental_after(q->rtable, qual, argv_ro);
318 incremental_after(q->rtable, qual, argv_ro);
320 table = table_name[q->rtable];
321 EXEC SQL UPDATE tblstats
322 SET appends = appends + 1, modtime = SYSDATE
323 WHERE table_name = :table;
326 /* execute followup routine */
328 status = (*v->post_rtn)(q, Argv, cl);
332 /* see if row already exists */
335 status = validate_row(q, Argv, v);
336 if (status != MR_EXISTS)
340 /* build "where" clause and perform delete */
341 /* if q->rvar = NULL, perform post_rtn only */
344 qual = build_qual(q->qual, q->argc, Argv);
345 table = table_name[q->rtable];
346 incremental_before(q->rtable, qual, argv_ro);
347 status = do_delete(q, qual, action, actarg);
348 incremental_clear_after();
349 if (status != MR_SUCCESS)
351 flush_name(argv_ro[0], q->rtable);
352 EXEC SQL UPDATE tblstats
353 SET deletes = deletes + 1, modtime = SYSDATE
354 WHERE table_name = :table;
357 /* execute followup routine */
359 status = (*v->post_rtn)(q, Argv, cl);
369 if (status == MR_SUCCESS && dbms_errno != 0)
371 com_err(whoami, MR_INTERNAL, "Server didn't notice DBMS ERROR %d",
376 if (q->type == RETRIEVE)
377 EXEC SQL COMMIT WORK;
380 if (status == MR_SUCCESS)
382 EXEC SQL COMMIT WORK;
389 fprintf(journal, "%% %s %s %s",
390 cl->clname, cl->entity, ctime(&now));
391 fprintf(journal, "%s ", q->name);
392 for (i = 0; i < argc; i++)
396 buf = requote(argv_ro[i]);
403 incremental_update();
408 EXEC SQL ROLLBACK WORK;
412 cache_commit(); /* commit following abort is safe */
414 if (status != MR_SUCCESS)
415 com_err(whoami, status, " (Query failed)");
419 char *build_qual(char *fmt_buf, int argc, char *argv[])
421 char *res, *result_buf, *fmt, *arg, *like, *p;
423 result_buf = xmalloc(2 * (strlen(fmt_buf) + argc * ARGLEN));
428 like = strstr(fmt, "LIKE");
429 arg = strchr(fmt, '%');
431 /* Look through the format for LIKE expressions and arguments.
432 Substitute in the arguments, simplify the `LIKE's to `='s
433 where possible, and insert ESCAPE clauses where needed */
439 /* only plain text remains */
441 res = strchr(res, '\0');
444 else if (!like || arg < like)
446 /* regular arg: copy up to arg, then substitute */
447 strncpy(res, fmt, arg - fmt);
459 /* copy string, doubling single quotes */
470 res += sprintf(res, "%d", *(int *)*argv++);
475 arg = strchr(fmt, '%');
477 /* LIKE arg: copy over up to the arg, then copy and convert arg */
478 int escape = 0, pattern = 0;
479 char *likepos = res + (like - fmt);
481 strncpy(res, fmt, arg - fmt);
484 /* copy arg, converting UNIX globs to `SQL voodoo', and noting
485 if we'll need an ESCAPE clause */
486 for (p = *argv++; *p; p++)
492 *res++ = '%'; /* need to double for build_sql_stmt */
519 /* if no pattern characters, write over "LIKE" with " = " */
520 if (!pattern && !escape)
521 memcpy(likepos, " = ", 4);
524 while (*fmt && *fmt != ' ')
528 res += sprintf(res, " ESCAPE '*'");
530 arg = strchr(fmt, '%');
531 like = strstr(fmt, "LIKE");
536 result_buf = realloc(result_buf, strlen(result_buf) + 1);
540 /* Build arguement vector, verify query and arguments */
544 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[])
548 struct validate *v = q->validate;
550 char *to, *fr, *stop;
554 /* check argument count */
556 if (q->type == UPDATE || q->type == APPEND)
561 /* copy the arguments into a local argv that we can modify */
562 for (i = 0; i < argc; i++)
564 for (to = Argv[i], fr = argv_ro[i], stop = to + MAX_FIELD_WIDTH; (*fr) && (to < stop);)
568 return MR_ARG_TOO_LONG;
571 if (to > Argv[i] && *--to == '\\')
575 /* check initial query access, unless we're acting as a proxy */
578 status = check_query_access(q, Argv, cl);
579 if (status != MR_SUCCESS && status != MR_PERM)
581 if (status == MR_SUCCESS)
585 /* validate arguments */
588 status = validate_fields(q, Argv, v->valobj, v->objcnt);
589 if (status != MR_SUCCESS)
593 /* perform special query access check */
594 if (!privileged && v && v->acs_rtn)
596 status = (*v->acs_rtn)(q, Argv, cl);
597 if (status != MR_SUCCESS && status != MR_PERM)
599 if (status == MR_SUCCESS)
603 return privileged ? MR_SUCCESS : MR_PERM;
607 /* This routine caches info from the database. Each query acl is stored
608 * in the query structure, and whether that acl contains everybody.
611 int check_query_access(struct query *q, char *argv[], client *cl)
613 EXEC SQL BEGIN DECLARE SECTION;
617 EXEC SQL END DECLARE SECTION;
619 /* initialize default uid */
621 EXEC SQL SELECT users_id INTO :def_uid FROM users WHERE login = 'default';
623 /* get query access control list */
629 EXEC SQL SELECT list_id INTO :acl_id FROM capacls WHERE tag = :name;
630 if (sqlca.sqlcode < 0)
632 if (sqlca.sqlcode == SQL_NO_MATCH)
636 /* check for default access */
637 EXEC SQL SELECT member_id INTO :acl_id FROM imembers
638 WHERE list_id = :acl_id AND member_type = 'USER'
639 AND member_id = :def_uid;
640 if (sqlca.sqlerrd[2] == 0)
649 if (find_member("LIST", acl_id, cl))
655 int find_member(char *list_type, int list_id, client *cl)
657 EXEC SQL BEGIN DECLARE SECTION;
658 int flag, users_id, client_id;
659 EXEC SQL END DECLARE SECTION;
661 if (!strcmp(strtrim(list_type), "USER") && list_id == cl->users_id)
664 if (!strcmp(strtrim(list_type), "KERBEROS") && list_id == -cl->client_id)
667 /* see if client is a member of list */
669 users_id = cl->users_id;
670 client_id = -cl->client_id;
671 EXEC SQL SELECT COUNT(member_id) INTO :flag FROM imembers
672 WHERE list_id = :list_id
673 AND ( ( member_type = 'USER' AND member_id = :users_id )
674 OR (member_type = 'KERBEROS' AND member_id = :client_id ) );
675 if (sqlca.sqlcode == 0)
681 int do_retrieve(struct query *q, char *pqual,
682 int (*action)(int, char *[], void *), void *actarg)
684 build_sql_stmt(stmt_buf, "SELECT", q->tlist, NULL, pqual);
687 strcat(stmt_buf, " ORDER BY ");
688 strcat(stmt_buf, q->sort);
691 return do_for_all_rows(stmt_buf, q->vcnt, action, actarg);
694 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
695 char *argv[], char *qual)
697 char fmt_buf[MR_STMTBUF_LEN];
701 sprintf(fmt_buf, "%s %s WHERE %s", cmd, targetlist, qual);
703 sprintf(fmt_buf, "%s %s", cmd, targetlist);
705 for (res = result_buf, fmt = fmt_buf; *fmt; fmt++)
713 case '%': /* %% -> % */
723 *res++ = '\''; /* double the ' */
730 res += sprintf(res, "%d", *(int *)*argv++);
732 default: /* Swallow other %? pairs */
740 *res++ = *fmt; /* text -> result buffer */
745 int do_update(struct query *q, char *argv[], char *qual,
746 int (*action)(int, char *[], void *), void *actarg)
748 build_sql_stmt(stmt_buf, "UPDATE", q->tlist, argv, qual);
749 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
755 int do_append(struct query *q, char *argv[], char *pqual,
756 int (*action)(int, char *[], void *), void *actarg)
758 build_sql_stmt(stmt_buf, "INSERT", q->tlist, argv, pqual);
759 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
765 int do_delete(struct query *q, char *qual,
766 int (*action)(int, char *[], void *), void *actarg)
768 sprintf(stmt_buf, "DELETE FROM %s WHERE %s", table_name[q->rtable], qual);
769 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
777 ** set_next_object_id - set next object id in values table
779 ** Inputs: object - object name in values table and in objects
780 ** table - name of table objects are found in
781 ** limit - should the ID be range limited
783 ** - called before an APPEND operation to set the next object id to
784 ** be used for the new record to the next free value
788 int set_next_object_id(char *object, enum tables table, int limit)
790 EXEC SQL BEGIN DECLARE SECTION;
793 EXEC SQL END DECLARE SECTION;
796 EXEC SQL SELECT value INTO :value FROM numvalues WHERE name = :obj;
797 if (sqlca.sqlerrd[2] != 1)
800 starting_value = value;
803 #ifdef ULTRIX_ID_HOLE
804 if (limit && value > 31999 && value < 32768)
807 if (limit && value > MAX_ID_VALUE)
808 value = MIN_ID_VALUE;
810 sprintf(stmt_buf, "SELECT %s FROM %s WHERE %s = %d",
811 object, table_name[table], object, value);
813 if (sqlca.sqlcode < 0)
815 if (sqlca.sqlcode == SQL_NO_MATCH)
819 if (limit && value == starting_value)
821 com_err(whoami, 0, "All id values have been used");
826 com_err(whoami, 0, "setting ID %s to %d", object, value);
827 EXEC SQL UPDATE numvalues SET value = :value WHERE name = :obj;
832 /* Turn a kerberos name into the user's ID of the account that principal
833 * owns. Sets the kerberos ID and user ID.
836 int set_krb_mapping(char *name, char *login, int ok, int *kid, int *uid)
838 EXEC SQL BEGIN DECLARE SECTION;
841 EXEC SQL END DECLARE SECTION;
847 EXEC SQL SELECT km.users_id, km.string_id INTO :u_id, :k_id
848 FROM krbmap km, strings str
849 WHERE km.string_id = str.string_id AND str.string = :krbname;
850 EXEC SQL COMMIT WORK;
855 if (sqlca.sqlerrd[2] == 1)
862 if (name_to_id(name, STRINGS_TABLE, &k_id) == MR_SUCCESS)
871 if (name_to_id(login, USERS_TABLE, uid) != MR_SUCCESS)
882 /* For now this just checks the argc's. It should also see that there
883 * are no duplicate names.
886 void sanity_check_queries(void)
889 int maxv = 0, maxa = 0;
890 extern int QueryCount2;
891 extern struct query Queries2[];
893 #define MAX(x, y) ((x) > (y) ? (x) : (y))
895 for (i = 0; i < QueryCount2; i++)
897 maxv = MAX(maxv, Queries2[i].vcnt);
898 maxa = MAX(maxa, Queries2[i].argc);
900 if (MAX(maxv, maxa) > QMAXARGS)
902 com_err(whoami, 0, "A query has more args than QMAXARGS");
908 /* Generically do a SELECT, storing the results in the provided buffers */
910 void dosql(char *buffers[])
912 int i, errcode = 0, errlen;
914 EXEC SQL PREPARE inc_stmt FROM :stmt_buf;
917 EXEC SQL DECLARE inc_crs CURSOR FOR inc_stmt;
918 EXEC SQL OPEN inc_crs;
919 mr_sqlda->N = QMAXARGS;
920 EXEC SQL DESCRIBE SELECT LIST FOR inc_stmt INTO mr_sqlda;
921 mr_sqlda->N = mr_sqlda->F;
922 for (i = 0; i < mr_sqlda->N; i++)
924 mr_sqlda->V[i] = buffers[i];
926 mr_sqlda->L[i] = MAX_FIELD_WIDTH;
928 EXEC SQL FETCH inc_crs USING DESCRIPTOR mr_sqlda;
930 /* if we got an error from the FETCH, we have to preserve it or the
931 close will reset it and the caller will think nothing happened */
934 errcode = sqlca.sqlcode;
935 errlen = sqlca.sqlerrm.sqlerrml;
938 EXEC SQL CLOSE inc_crs;
941 sqlca.sqlcode = errcode;
942 sqlca.sqlerrm.sqlerrml = errlen;
946 int do_for_all_rows(char *query, int count,
947 int (*action)(int, char *[], void *), void *actarg)
950 EXEC SQL BEGIN DECLARE SECTION;
952 EXEC SQL END DECLARE SECTION;
954 EXEC SQL PREPARE stmt FROM :q;
957 EXEC SQL DECLARE curs CURSOR FOR stmt;
960 EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO mr_sqlda;
961 mr_sqlda->N = mr_sqlda->F;
962 for (i = 0; i < mr_sqlda->N; i++)
964 mr_sqlda->V[i] = sqlbuffer[i];
966 mr_sqlda->L[i] = MAX_FIELD_WIDTH;
969 while (rowcount < max_row_count)
971 EXEC SQL FETCH curs USING DESCRIPTOR mr_sqlda;
974 (*action)(count, sqlbuffer, actarg);
981 if (rowcount == max_row_count)
983 critical_alert("moirad", "attempted query with too many rows");
986 else if (rowcount == 0)