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 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[]);
45 int do_retrieve(struct query *q, char *pqual,
46 int (*action)(int, char *[], void *), void *actarg);
47 int do_update(struct query *q, char *argv[], char *qual,
48 int (*action)(int, char *[], void *), void *actarg);
49 int do_append(struct query *q, char *argv[], char *pqual,
50 int (*action)(int, char *[], void *), void *actarg);
51 int do_delete(struct query *q, char *qual,
52 int (*action)(int, char *[], void *), void *actarg);
53 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
54 char *argv[], char *qual);
56 SQLDA *mr_alloc_sqlda(void);
57 void sqlglm(char *, int *, int *);
60 * dbmserr: Called when the DBMS indicates an error.
65 EXEC SQL BEGIN DECLARE SECTION;
67 EXEC SQL END DECLARE SECTION;
68 int bufsize = 256, msglength = 0;
70 dbms_errno = -sqlca.sqlcode;
71 mr_errcode = MR_DBMS_ERR;
72 com_err(whoami, MR_DBMS_ERR, " code %d\n", dbms_errno);
73 sqlglm(err_msg, &bufsize, &msglength);
74 err_msg[msglength] = 0;
75 com_err(whoami, 0, "SQL error text = %s", err_msg);
76 critical_alert("MOIRA", "Moira server encountered DBMS ERROR %d\n%s",
80 /* This is declarative, not executed. Applies from here on, in this file. */
81 EXEC SQL WHENEVER SQLERROR DO dbmserr();
83 int mr_open_database(void)
86 static first_open = 1;
92 /* initialize local argv */
93 for (i = 0; i < 16; i++)
94 Argv[i] = xmalloc(MAX_FIELD_WIDTH);
96 mr_sqlda = mr_alloc_sqlda();
105 /* open the database */
106 EXEC SQL CONNECT :database IDENTIFIED BY :database;
111 EXEC SQL SELECT data_length INTO :mr_sig_length FROM user_tab_columns
112 WHERE table_name = 'USERS' and column_name = 'SIGNATURE';
113 EXEC SQL COMMIT WORK;
120 void mr_close_database(void)
123 EXEC SQL COMMIT RELEASE;
126 int mr_check_access(client *cl, char *name, int argc, char *argv_ro[])
133 q = get_query_by_name(name);
137 return mr_verify_query(cl, q, argc, argv_ro);
140 int mr_process_query(client *cl, char *name, int argc, char *argv_ro[],
141 int (*action)(int, char *[], void *), void *actarg)
147 EXEC SQL BEGIN DECLARE SECTION;
149 EXEC SQL END DECLARE SECTION;
150 struct save_queue *sq;
155 /* list queries command */
156 if (!strcmp(name, "_list_queries"))
158 list_queries(action, actarg);
162 /* help query command */
163 if (!strcmp(name, "_help"))
167 q = get_query_by_name(argv_ro[0]);
170 help_query(q, action, actarg);
174 /* get query structure, return error if named query does not exist */
175 q = get_query_by_name(name);
180 /* setup argument vector, verify access and arguments */
181 if ((status = mr_verify_query(cl, q, argc, argv_ro)) != MR_SUCCESS)
184 /* perform any special query pre-processing */
187 status = (*v->pre_rtn)(q, Argv, cl);
188 if (status != MR_SUCCESS)
195 /* for queries that do not permit wildcarding, check if row
199 status = validate_row(q, Argv, v);
200 if (status != MR_EXISTS)
204 /* build "where" clause if needed */
206 qual = build_qual(q->qual, q->argc, Argv);
208 /* if there is a followup routine, then we must save the results */
209 /* of the first query for use by the followup routine */
210 /* if q->rvar = NULL, perform post_rtn only */
213 if (v && v->post_rtn)
216 status = do_retrieve(q, qual, sq_save_args, sq);
217 if (status != MR_SUCCESS)
222 status = (*v->post_rtn)(q, sq, v, action, actarg, cl);
226 /* normal retrieve */
227 status = do_retrieve(q, qual, action, actarg);
229 if (status != MR_SUCCESS)
233 status = (*v->post_rtn)(q, Argv, cl, action, actarg);
238 /* see if row already exists */
241 status = validate_row(q, Argv, v);
242 if (status != MR_EXISTS)
246 /* build "where" clause and perform update */
247 /* if q->rvar = NULL, perform post_rtn only */
250 qual = build_qual(q->qual, q->argc, Argv);
251 incremental_before(q->rtable, qual, argv_ro);
252 status = do_update(q, &Argv[q->argc], qual, action, actarg);
253 incremental_after(q->rtable, qual, argv_ro);
254 if (status != MR_SUCCESS)
256 flush_name(argv_ro[0], q->rtable);
257 table = table_name[q->rtable];
258 if (strcmp(q->shortname, "sshi") && strcmp(q->shortname, "ssif"))
260 EXEC SQL UPDATE tblstats
261 SET updates = updates + 1, modtime = SYSDATE
262 WHERE table_name = :table;
266 /* execute followup routine (if any) */
268 status = (*v->post_rtn)(q, Argv, cl);
273 /* see if row already exists */
276 status = validate_row(q, Argv, v);
277 if (status != MR_NO_MATCH)
281 /* build "where" clause if needed */
283 qual = build_qual(q->qual, q->argc, Argv);
285 /* perform the append */
286 /* if q->rvar = NULL, perform post_rtn only */
289 incremental_clear_before();
290 status = do_append(q, &Argv[q->argc], qual, action, actarg);
291 if (status != MR_SUCCESS)
293 if (v && v->object_id)
295 qual = realloc(qual, 15 + strlen(q->rvar) +
296 strlen(Argv[q->argc + q->vcnt]));
297 sprintf(qual, "%s.%s = %s", q->rvar, v->object_id,
298 Argv[q->argc + q->vcnt]);
299 incremental_after(q->rtable, qual, argv_ro);
302 incremental_after(q->rtable, qual, argv_ro);
304 table = table_name[q->rtable];
305 EXEC SQL UPDATE tblstats
306 SET appends = appends + 1, modtime = SYSDATE
307 WHERE table_name = :table;
310 /* execute followup routine */
312 status = (*v->post_rtn)(q, Argv, cl);
316 /* see if row already exists */
319 status = validate_row(q, Argv, v);
320 if (status != MR_EXISTS)
324 /* build "where" clause and perform delete */
325 /* if q->rvar = NULL, perform post_rtn only */
328 qual = build_qual(q->qual, q->argc, Argv);
329 table = table_name[q->rtable];
330 incremental_before(q->rtable, qual, argv_ro);
331 status = do_delete(q, qual, action, actarg);
332 incremental_clear_after();
333 if (status != MR_SUCCESS)
335 flush_name(argv_ro[0], q->rtable);
336 EXEC SQL UPDATE tblstats
337 SET deletes = deletes + 1, modtime = SYSDATE
338 WHERE table_name = :table;
341 /* execute followup routine */
343 status = (*v->post_rtn)(q, Argv, cl);
353 if (status == MR_SUCCESS && dbms_errno != 0)
355 com_err(whoami, MR_INTERNAL, "Server didn't notice DBMS ERROR %d",
360 if (q->type == RETRIEVE)
361 EXEC SQL COMMIT WORK;
364 if (status == MR_SUCCESS)
366 EXEC SQL COMMIT WORK;
373 fprintf(journal, "%% %s %s %s",
374 cl->clname, cl->entity, ctime(&now));
375 fprintf(journal, "%s ", q->name);
376 for (i = 0; i < argc; i++)
380 buf = requote(argv_ro[i]);
387 incremental_update();
392 EXEC SQL ROLLBACK WORK;
396 cache_commit(); /* commit following abort is safe */
398 if (status != MR_SUCCESS)
399 com_err(whoami, status, " (Query failed)");
403 char *build_qual(char *fmt_buf, int argc, char *argv[])
405 char *res, *result_buf, *fmt, *arg, *like, *p;
407 result_buf = xmalloc(2 * (strlen(fmt_buf) + argc * ARGLEN));
412 like = strstr(fmt, "LIKE");
413 arg = strchr(fmt, '%');
415 /* Look through the format for LIKE expressions and arguments.
416 Substitute in the arguments, simplify the `LIKE's to `='s
417 where possible, and insert ESCAPE clauses where needed */
423 /* only plain text remains */
425 res = strchr(res, '\0');
428 else if (!like || arg < like)
430 /* regular arg: copy up to arg, then substitute */
431 strncpy(res, fmt, arg - fmt);
443 /* copy string, doubling single quotes */
454 res += sprintf(res, "%d", *(int *)*argv++);
459 arg = strchr(fmt, '%');
461 /* LIKE arg: copy over up to the arg, then copy and convert arg */
462 int escape = 0, pattern = 0;
463 char *likepos = res + (like - fmt);
465 strncpy(res, fmt, arg - fmt);
468 /* copy arg, converting UNIX globs to `SQL voodoo', and noting
469 if we'll need an ESCAPE clause */
470 for (p = *argv++; *p; p++)
476 *res++ = '%'; /* need to double for build_sql_stmt */
503 /* if no pattern characters, write over "LIKE" with " = " */
504 if (!pattern && !escape)
505 memcpy(likepos, " = ", 4);
508 while (*fmt && *fmt != ' ')
512 res += sprintf(res, " ESCAPE '*'");
514 arg = strchr(fmt, '%');
515 like = strstr(fmt, "LIKE");
520 result_buf = realloc(result_buf, strlen(result_buf) + 1);
524 /* Build arguement vector, verify query and arguments */
528 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[])
532 struct validate *v = q->validate;
534 char *to, *fr, *stop;
538 /* check argument count */
540 if (q->type == UPDATE || q->type == APPEND)
545 /* copy the arguments into a local argv that we can modify */
546 for (i = 0; i < argc; i++)
548 for (to = Argv[i], fr = argv_ro[i], stop = to + MAX_FIELD_WIDTH; (*fr) && (to < stop);)
552 return MR_ARG_TOO_LONG;
555 if (to > Argv[i] && *--to == '\\')
559 /* check initial query access */
560 status = check_query_access(q, Argv, cl);
561 if (status != MR_SUCCESS && status != MR_PERM)
563 if (status == MR_SUCCESS)
566 /* validate arguments */
569 status = validate_fields(q, Argv, v->valobj, v->objcnt);
570 if (status != MR_SUCCESS)
574 /* perform special query access check */
575 if (!privileged && v && v->acs_rtn)
577 status = (*v->acs_rtn)(q, Argv, cl);
578 if (status != MR_SUCCESS && status != MR_PERM)
580 if (status == MR_SUCCESS)
584 return privileged ? MR_SUCCESS : MR_PERM;
588 /* This routine caches info from the database. Each query acl is stored
589 * in the query structure, and whether that acl contains everybody.
592 int check_query_access(struct query *q, char *argv[], client *cl)
594 EXEC SQL BEGIN DECLARE SECTION;
598 EXEC SQL END DECLARE SECTION;
600 /* initialize default uid */
602 EXEC SQL SELECT users_id INTO :def_uid FROM users WHERE login = 'default';
604 /* get query access control list */
610 EXEC SQL SELECT list_id INTO :acl_id FROM capacls WHERE tag = :name;
611 if (sqlca.sqlcode < 0)
613 if (sqlca.sqlcode == SQL_NO_MATCH)
617 /* check for default access */
618 EXEC SQL SELECT member_id INTO :acl_id FROM imembers
619 WHERE list_id = :acl_id AND member_type = 'USER'
620 AND member_id = :def_uid;
621 if (sqlca.sqlerrd[2] == 0)
630 if (find_member("LIST", acl_id, cl))
637 int find_member(char *list_type, int list_id, client *cl)
639 EXEC SQL BEGIN DECLARE SECTION;
640 int flag, users_id, client_id;
641 EXEC SQL END DECLARE SECTION;
643 if (!strcmp(strtrim(list_type), "USER") && list_id == cl->users_id)
646 if (!strcmp(strtrim(list_type), "KERBEROS") && list_id == -cl->client_id)
649 /* see if client is a member of list */
651 users_id = cl->users_id;
652 client_id = -cl->client_id;
653 EXEC SQL SELECT COUNT(member_id) INTO :flag FROM imembers
654 WHERE list_id = :list_id
655 AND ( ( member_type = 'USER' AND member_id = :users_id )
656 OR (member_type = 'KERBEROS' AND member_id = :client_id ) );
657 if (sqlca.sqlcode == 0)
663 int do_retrieve(struct query *q, char *pqual,
664 int (*action)(int, char *[], void *), void *actarg)
666 build_sql_stmt(stmt_buf, "SELECT", q->tlist, NULL, pqual);
669 strcat(stmt_buf, " ORDER BY ");
670 strcat(stmt_buf, q->sort);
673 return do_for_all_rows(stmt_buf, q->vcnt, action, actarg);
676 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
677 char *argv[], char *qual)
679 char fmt_buf[MR_STMTBUF_LEN];
683 sprintf(fmt_buf, "%s %s WHERE %s", cmd, targetlist, qual);
685 sprintf(fmt_buf, "%s %s", cmd, targetlist);
687 for (res = result_buf, fmt = fmt_buf; *fmt; fmt++)
695 case '%': /* %% -> % */
705 *res++ = '\''; /* double the ' */
712 res += sprintf(res, "%d", *(int *)*argv++);
714 default: /* Swallow other %? pairs */
722 *res++ = *fmt; /* text -> result buffer */
727 int do_update(struct query *q, char *argv[], char *qual,
728 int (*action)(int, char *[], void *), void *actarg)
730 build_sql_stmt(stmt_buf, "UPDATE", q->tlist, argv, qual);
731 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
737 int do_append(struct query *q, char *argv[], char *pqual,
738 int (*action)(int, char *[], void *), void *actarg)
740 build_sql_stmt(stmt_buf, "INSERT", q->tlist, argv, pqual);
741 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
747 int do_delete(struct query *q, char *qual,
748 int (*action)(int, char *[], void *), void *actarg)
750 sprintf(stmt_buf, "DELETE FROM %s WHERE %s", table_name[q->rtable], qual);
751 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
759 ** set_next_object_id - set next object id in values table
761 ** Inputs: object - object name in values table and in objects
762 ** table - name of table objects are found in
763 ** limit - should the ID be range limited
765 ** - called before an APPEND operation to set the next object id to
766 ** be used for the new record to the next free value
770 int set_next_object_id(char *object, enum tables table, int limit)
772 EXEC SQL BEGIN DECLARE SECTION;
775 EXEC SQL END DECLARE SECTION;
778 EXEC SQL SELECT value INTO :value FROM numvalues WHERE name = :obj;
779 if (sqlca.sqlerrd[2] != 1)
782 starting_value = value;
785 if (limit && value > MAX_ID_VALUE)
786 value = MIN_ID_VALUE;
788 sprintf(stmt_buf, "SELECT %s FROM %s WHERE %s = %d",
789 object, table_name[table], object, value);
791 if (sqlca.sqlcode < 0)
793 if (sqlca.sqlcode == SQL_NO_MATCH)
797 if (limit && value == starting_value)
799 com_err(whoami, 0, "All id values have been used");
804 com_err(whoami, 0, "setting ID %s to %d", object, value);
805 EXEC SQL UPDATE numvalues SET value = :value WHERE name = :obj;
810 /* Turn a kerberos name into the user's ID of the account that principal
811 * owns. Sets the kerberos ID and user ID.
814 int set_krb_mapping(char *name, char *login, int ok, int *kid, int *uid)
816 EXEC SQL BEGIN DECLARE SECTION;
819 EXEC SQL END DECLARE SECTION;
825 EXEC SQL SELECT km.users_id, km.string_id INTO :u_id, :k_id
826 FROM krbmap km, strings str
827 WHERE km.string_id = str.string_id AND str.string = :krbname;
828 EXEC SQL COMMIT WORK;
833 if (sqlca.sqlerrd[2] == 1)
840 if (name_to_id(name, STRINGS_TABLE, &k_id) == MR_SUCCESS)
849 if (name_to_id(login, USERS_TABLE, uid) != MR_SUCCESS)
860 /* For now this just checks the argc's. It should also see that there
861 * are no duplicate names.
864 void sanity_check_queries(void)
867 int maxv = 0, maxa = 0;
868 extern int QueryCount2;
869 extern struct query Queries2[];
871 #define MAX(x, y) ((x) > (y) ? (x) : (y))
873 for (i = 0; i < QueryCount2; i++)
875 maxv = MAX(maxv, Queries2[i].vcnt);
876 maxa = MAX(maxa, Queries2[i].argc);
878 if (MAX(maxv, maxa) > QMAXARGS)
880 com_err(whoami, 0, "A query has more args than QMAXARGS");
886 /* Generically do a SELECT, storing the results in the provided buffers */
888 void dosql(char *buffers[])
890 int i, errcode = 0, errlen;
892 EXEC SQL PREPARE inc_stmt FROM :stmt_buf;
895 EXEC SQL DECLARE inc_crs CURSOR FOR inc_stmt;
896 EXEC SQL OPEN inc_crs;
897 mr_sqlda->N = QMAXARGS;
898 EXEC SQL DESCRIBE SELECT LIST FOR inc_stmt INTO mr_sqlda;
899 mr_sqlda->N = mr_sqlda->F;
900 for (i = 0; i < mr_sqlda->N; i++)
902 mr_sqlda->V[i] = buffers[i];
904 mr_sqlda->L[i] = MAX_FIELD_WIDTH;
906 EXEC SQL FETCH inc_crs USING DESCRIPTOR mr_sqlda;
908 /* if we got an error from the FETCH, we have to preserve it or the
909 close will reset it and the caller will think nothing happened */
912 errcode = sqlca.sqlcode;
913 errlen = sqlca.sqlerrm.sqlerrml;
916 EXEC SQL CLOSE inc_crs;
919 sqlca.sqlcode = errcode;
920 sqlca.sqlerrm.sqlerrml = errlen;
924 int do_for_all_rows(char *query, int count,
925 int (*action)(int, char *[], void *), void *actarg)
928 EXEC SQL BEGIN DECLARE SECTION;
930 EXEC SQL END DECLARE SECTION;
932 EXEC SQL PREPARE stmt FROM :q;
935 EXEC SQL DECLARE curs CURSOR FOR stmt;
938 EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO mr_sqlda;
939 mr_sqlda->N = mr_sqlda->F;
940 for (i = 0; i < mr_sqlda->N; i++)
942 mr_sqlda->V[i] = sqlbuffer[i];
944 mr_sqlda->L[i] = MAX_FIELD_WIDTH;
949 EXEC SQL FETCH curs USING DESCRIPTOR mr_sqlda;
952 (*action)(count, sqlbuffer, actarg);
959 return (rowcount == 0) ? MR_NO_MATCH : MR_SUCCESS;