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);
55 char *sqlstrstr(char *str, char *pat);
56 void optimize_sql_stmt(char *buf);
58 SQLDA *mr_alloc_sqlda(void);
59 void sqlglm(char *, int *, int *);
62 * dbmserr: Called when the DBMS indicates an error.
67 EXEC SQL BEGIN DECLARE SECTION;
69 EXEC SQL END DECLARE SECTION;
70 int bufsize = 256, msglength = 0;
72 dbms_errno = -sqlca.sqlcode;
73 mr_errcode = MR_DBMS_ERR;
74 com_err(whoami, MR_DBMS_ERR, " code %d\n", dbms_errno);
75 sqlglm(err_msg, &bufsize, &msglength);
76 err_msg[msglength] = 0;
77 com_err(whoami, 0, "SQL error text = %s", err_msg);
78 critical_alert("MOIRA", "Moira server encountered DBMS ERROR %d\n%s",
82 /* This is declarative, not executed. Applies from here on, in this file. */
83 EXEC SQL WHENEVER SQLERROR DO dbmserr();
85 int mr_open_database(void)
88 static first_open = 1;
94 /* initialize local argv */
95 for (i = 0; i < 16; i++)
96 Argv[i] = xmalloc(MAX_FIELD_WIDTH);
98 mr_sqlda = mr_alloc_sqlda();
107 /* open the database */
108 EXEC SQL CONNECT :database IDENTIFIED BY :database;
113 EXEC SQL SELECT data_length INTO :mr_sig_length FROM user_tab_columns
114 WHERE table_name = 'USERS' and column_name = 'SIGNATURE';
115 EXEC SQL COMMIT WORK;
122 void mr_close_database(void)
125 EXEC SQL COMMIT RELEASE;
128 int mr_check_access(client *cl, char *name, int argc, char *argv_ro[])
135 q = get_query_by_name(name);
139 return mr_verify_query(cl, q, argc, argv_ro);
142 int mr_process_query(client *cl, char *name, int argc, char *argv_ro[],
143 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 */
210 build_qual(q->qual, q->argc, Argv, qual);
216 /* if there is a followup routine, then we must save the results */
217 /* of the first query for use by the followup routine */
218 /* if q->rvar = NULL, perform post_rtn only */
221 if (v && v->post_rtn)
224 status = do_retrieve(q, pqual, sq_save_args, sq);
225 if (status != MR_SUCCESS)
230 status = (*v->post_rtn)(q, sq, v, action, actarg, cl);
234 /* normal retrieve */
235 status = do_retrieve(q, pqual, action, actarg);
237 if (status != MR_SUCCESS)
241 status = (*v->post_rtn)(q, Argv, cl, action, actarg);
246 /* see if row already exists */
249 status = validate_row(q, Argv, v);
250 if (status != MR_EXISTS)
254 /* build "where" clause and perform update */
255 /* if q->rvar = NULL, perform post_rtn only */
258 build_qual(q->qual, q->argc, Argv, qual);
259 incremental_before(q->rtable, qual, argv_ro);
260 status = do_update(q, &Argv[q->argc], qual, action, actarg);
261 incremental_after(q->rtable, qual, argv_ro);
262 if (status != MR_SUCCESS)
264 flush_name(argv_ro[0], q->rtable);
265 table = table_name[q->rtable];
266 if (strcmp(q->shortname, "sshi") && strcmp(q->shortname, "ssif"))
268 EXEC SQL UPDATE tblstats
269 SET updates = updates + 1, modtime = SYSDATE
270 WHERE table_name = :table;
274 /* execute followup routine (if any) */
276 status = (*v->post_rtn)(q, Argv, cl);
281 /* see if row already exists */
284 status = validate_row(q, Argv, v);
285 if (status != MR_NO_MATCH)
289 /* build "where" clause if needed */
292 build_qual(q->qual, q->argc, Argv, qual);
298 /* perform the append */
299 /* if q->rvar = NULL, perform post_rtn only */
302 incremental_clear_before();
303 status = do_append(q, &Argv[q->argc], pqual, action, actarg);
304 if (status != MR_SUCCESS)
306 if (v && v->object_id)
308 sprintf(qual, "%s.%s = %s", q->rvar, v->object_id,
309 Argv[q->argc + q->vcnt]);
310 incremental_after(q->rtable, qual, argv_ro);
313 incremental_after(q->rtable, pqual, argv_ro);
315 table = table_name[q->rtable];
316 EXEC SQL UPDATE tblstats
317 SET appends = appends + 1, modtime = SYSDATE
318 WHERE table_name = :table;
321 /* execute followup routine */
323 status = (*v->post_rtn)(q, Argv, cl);
327 /* see if row already exists */
330 status = validate_row(q, Argv, v);
331 if (status != MR_EXISTS)
335 /* build "where" clause and perform delete */
336 /* if q->rvar = NULL, perform post_rtn only */
339 build_qual(q->qual, q->argc, Argv, qual);
340 table = table_name[q->rtable];
341 incremental_before(q->rtable, qual, argv_ro);
342 status = do_delete(q, qual, action, actarg);
343 incremental_clear_after();
344 if (status != MR_SUCCESS)
346 flush_name(argv_ro[0], q->rtable);
347 EXEC SQL UPDATE tblstats
348 SET deletes = deletes + 1, modtime = SYSDATE
349 WHERE table_name = :table;
352 /* execute followup routine */
354 status = (*v->post_rtn)(q, Argv, cl);
362 if (status == MR_SUCCESS && dbms_errno != 0)
364 com_err(whoami, MR_INTERNAL, "Server didn't notice DBMS ERROR %d",
369 if (q->type == RETRIEVE)
370 EXEC SQL COMMIT WORK;
373 if (status == MR_SUCCESS)
375 EXEC SQL COMMIT WORK;
382 fprintf(journal, "%% %s %s %s",
383 cl->clname, cl->entity, ctime(&now));
384 fprintf(journal, "%s ", q->name);
385 for (i = 0; i < argc; i++)
389 buf = requote(argv_ro[i]);
396 incremental_update();
401 EXEC SQL ROLLBACK WORK;
405 cache_commit(); /* commit following abort is safe */
407 if (status != MR_SUCCESS)
408 com_err(whoami, status, " (Query failed)");
412 void build_qual(char *fmt_buf, int argc, char *argv[], char *qual)
416 for (res = qual, fmt = fmt_buf; *fmt; fmt++)
424 case '%': /* %% -> % */
434 *res++ = '\''; /* double the ' */
441 res += sprintf(res, "%d", *(int *)*argv++);
443 default: /* Swallow other %? pairs */
451 *res++ = *fmt; /* text -> result buffer */
456 /* Build arguement vector, verify query and arguments */
460 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[])
464 struct validate *v = q->validate;
466 char *to, *fr, *stop;
470 /* check argument count */
472 if (q->type == UPDATE || q->type == APPEND)
477 /* copy the arguments into a local argv that we can modify */
478 for (i = 0; i < argc; i++)
480 for (to = Argv[i], fr = argv_ro[i], stop = to + MAX_FIELD_WIDTH; (*fr) && (to < stop);)
484 return MR_ARG_TOO_LONG;
487 if (to > Argv[i] && *--to == '\\')
491 /* check initial query access */
492 status = check_query_access(q, Argv, cl);
493 if (status != MR_SUCCESS && status != MR_PERM)
495 if (status == MR_SUCCESS)
498 /* validate arguments */
501 status = validate_fields(q, Argv, v->valobj, v->objcnt);
502 if (status != MR_SUCCESS)
506 /* perform special query access check */
507 if (!privileged && v && v->acs_rtn)
509 status = (*v->acs_rtn)(q, Argv, cl);
510 if (status != MR_SUCCESS && status != MR_PERM)
512 if (status == MR_SUCCESS)
516 return privileged ? MR_SUCCESS : MR_PERM;
520 /* This routine caches info from the database. Each query acl is stored
521 * in the query structure, and whether that acl contains everybody.
524 int check_query_access(struct query *q, char *argv[], client *cl)
526 EXEC SQL BEGIN DECLARE SECTION;
530 EXEC SQL END DECLARE SECTION;
532 /* initialize default uid */
534 EXEC SQL SELECT users_id INTO :def_uid FROM users WHERE login = 'default';
536 /* get query access control list */
542 EXEC SQL SELECT list_id INTO :acl_id FROM capacls WHERE tag = :name;
543 if (sqlca.sqlcode < 0)
545 if (sqlca.sqlcode == SQL_NO_MATCH)
549 /* check for default access */
550 EXEC SQL SELECT member_id INTO :acl_id FROM imembers
551 WHERE list_id = :acl_id AND member_type = 'USER'
552 AND member_id = :def_uid;
553 if (sqlca.sqlerrd[2] == 0)
562 if (find_member("LIST", acl_id, cl))
569 int find_member(char *list_type, int list_id, client *cl)
571 EXEC SQL BEGIN DECLARE SECTION;
572 int flag, users_id, client_id;
573 EXEC SQL END DECLARE SECTION;
575 if (!strcmp(strtrim(list_type), "USER") && list_id == cl->users_id)
578 if (!strcmp(strtrim(list_type), "KERBEROS") && list_id == -cl->client_id)
581 /* see if client is a member of list */
583 users_id = cl->users_id;
584 client_id = -cl->client_id;
585 EXEC SQL SELECT COUNT(member_id) INTO :flag FROM imembers
586 WHERE list_id = :list_id
587 AND ( ( member_type = 'USER' AND member_id = :users_id )
588 OR (member_type = 'KERBEROS' AND member_id = :client_id ) );
589 if (sqlca.sqlcode == 0)
595 int do_retrieve(struct query *q, char *pqual,
596 int (*action)(int, char *[], void *), void *actarg)
598 build_sql_stmt(stmt_buf, "SELECT", q->tlist, NULL, pqual);
601 strcat(stmt_buf, " ORDER BY ");
602 strcat(stmt_buf, q->sort);
605 return do_for_all_rows(stmt_buf, q->vcnt, action, actarg);
608 char *sqlstrstr(char *str, char *pat)
614 if (*str == '\'') /* Skip over single-quote delimited substrings */
616 while (*++str && (*str != '\''))
624 while (*++p && (*++s == *p))
627 p = pat; /* failed */
630 while (*p && *++str);
637 void optimize_sql_stmt(char *buf)
639 char *point = buf, *pat, *eopat, *esc1, *esc2, *csr;
641 for (point = buf; (point = sqlstrstr(point, "LIKE")); point++)
643 /* Now pointing to string "LIKE" */
645 /* Look at next word */
646 for (pat = point + 4; *pat == ' '; pat++)
649 /* Is it a single-quote delimited string? */
653 /* look for "escape" clause - save escape character */
654 /* 1. Find end of pattern */
655 for (eopat = pat + 1; 1; eopat++)
659 if (eopat[1] == '\'') /* single-quote is self-escaping */
666 /* 2. Look at next word */
667 for (esc1 = eopat; *++esc1 == ' ';)
670 /* 3. esc1 = 0 if not "ESCAPE '?'", where the ? may be any character. */
671 if (strncmp(esc1, "ESCAPE", 6))
676 for (esc2 = esc1 + 6; *esc2 == ' '; esc2++)
680 continue; /* Bad SQL syntax. Skip. */
681 /* esc2 now points at the escape character itself */
683 continue; /* Weird escape string. Skip. */
688 /* Is pattern free from special characters? */
689 for (csr = pat; csr < eopat; csr++)
691 if ((*csr == '%') || (*csr == '_') || (*csr == *esc2))
695 continue; /* Uses pattern matching. Skip. */
697 /* Optimize the query statement */
698 /* 1. Change "LIKE" to " = " */
699 memcpy(point, " = ", 4);
701 /* 2. Change "ESCAPE" to " " */
704 memset(esc1, ' ', 6);
705 /* 3. Change "'*'" to " " */
706 /* (Changes '''' to " ") */
708 memset(esc2 - 1, ' ', (*esc2 == '\'') ? 4 : 3);
713 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
714 char *argv[], char *qual)
716 char fmt_buf[MR_STMTBUF_LEN];
720 sprintf(fmt_buf, "%s %s WHERE %s", cmd, targetlist, qual);
722 sprintf(fmt_buf, "%s %s", cmd, targetlist);
724 for (res = result_buf, fmt = fmt_buf; *fmt; fmt++)
732 case '%': /* %% -> % */
742 *res++ = '\''; /* double the ' */
749 res += sprintf(res, "%d", *(int *)*argv++);
751 default: /* Swallow other %? pairs */
759 *res++ = *fmt; /* text -> result buffer */
763 optimize_sql_stmt(result_buf);
766 int do_update(struct query *q, char *argv[], char *qual,
767 int (*action)(int, char *[], void *), void *actarg)
769 build_sql_stmt(stmt_buf, "UPDATE", q->tlist, argv, qual);
770 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
776 int do_append(struct query *q, char *argv[], char *pqual,
777 int (*action)(int, char *[], void *), void *actarg)
779 build_sql_stmt(stmt_buf, "INSERT", q->tlist, argv, pqual);
780 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
786 int do_delete(struct query *q, char *qual,
787 int (*action)(int, char *[], void *), void *actarg)
789 sprintf(stmt_buf, "DELETE FROM %s WHERE %s", table_name[q->rtable], qual);
790 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
798 ** set_next_object_id - set next object id in values table
800 ** Inputs: object - object name in values table and in objects
801 ** table - name of table objects are found in
802 ** limit - should the ID be range limited
804 ** - called before an APPEND operation to set the next object id to
805 ** be used for the new record to the next free value
809 int set_next_object_id(char *object, enum tables table, int limit)
811 EXEC SQL BEGIN DECLARE SECTION;
814 EXEC SQL END DECLARE SECTION;
817 EXEC SQL SELECT value INTO :value FROM numvalues WHERE name = :obj;
818 if (sqlca.sqlerrd[2] != 1)
821 starting_value = value;
824 if (limit && value > MAX_ID_VALUE)
825 value = MIN_ID_VALUE;
827 sprintf(stmt_buf, "SELECT %s FROM %s WHERE %s = %d",
828 object, table_name[table], object, value);
830 if (sqlca.sqlcode < 0)
832 if (sqlca.sqlcode == SQL_NO_MATCH)
836 if (limit && value == starting_value)
838 com_err(whoami, 0, "All id values have been used");
843 com_err(whoami, 0, "setting ID %s to %d", object, value);
844 EXEC SQL UPDATE numvalues SET value = :value WHERE name = :obj;
849 /* Turn a kerberos name into the user's ID of the account that principal
850 * owns. Sets the kerberos ID and user ID.
853 int set_krb_mapping(char *name, char *login, int ok, int *kid, int *uid)
855 EXEC SQL BEGIN DECLARE SECTION;
858 EXEC SQL END DECLARE SECTION;
864 EXEC SQL SELECT km.users_id, km.string_id INTO :u_id, :k_id
865 FROM krbmap km, strings str
866 WHERE km.string_id = str.string_id AND str.string = :krbname;
867 EXEC SQL COMMIT WORK;
872 if (sqlca.sqlerrd[2] == 1)
879 if (name_to_id(name, STRINGS_TABLE, &k_id) == MR_SUCCESS)
888 if (name_to_id(login, USERS_TABLE, uid) != MR_SUCCESS)
899 /* For now this just checks the argc's. It should also see that there
900 * are no duplicate names.
903 void sanity_check_queries(void)
906 int maxv = 0, maxa = 0;
907 extern int QueryCount2;
908 extern struct query Queries2[];
910 #define MAX(x, y) ((x) > (y) ? (x) : (y))
912 for (i = 0; i < QueryCount2; i++)
914 maxv = MAX(maxv, Queries2[i].vcnt);
915 maxa = MAX(maxa, Queries2[i].argc);
917 if (MAX(maxv, maxa) > QMAXARGS)
919 com_err(whoami, 0, "A query has more args than QMAXARGS");
925 /* Generically do a SELECT, storing the results in the provided buffers */
927 void dosql(char *buffers[])
929 int i, errcode = 0, errlen;
931 EXEC SQL PREPARE inc_stmt FROM :stmt_buf;
934 EXEC SQL DECLARE inc_crs CURSOR FOR inc_stmt;
935 EXEC SQL OPEN inc_crs;
936 mr_sqlda->N = QMAXARGS;
937 EXEC SQL DESCRIBE SELECT LIST FOR inc_stmt INTO mr_sqlda;
938 mr_sqlda->N = mr_sqlda->F;
939 for (i = 0; i < mr_sqlda->N; i++)
941 mr_sqlda->V[i] = buffers[i];
943 mr_sqlda->L[i] = MAX_FIELD_WIDTH;
945 EXEC SQL FETCH inc_crs USING DESCRIPTOR mr_sqlda;
947 /* if we got an error from the FETCH, we have to preserve it or the
948 close will reset it and the caller will think nothing happened */
951 errcode = sqlca.sqlcode;
952 errlen = sqlca.sqlerrm.sqlerrml;
955 EXEC SQL CLOSE inc_crs;
958 sqlca.sqlcode = errcode;
959 sqlca.sqlerrm.sqlerrml = errlen;
963 int do_for_all_rows(char *query, int count,
964 int (*action)(int, char *[], void *), void *actarg)
967 EXEC SQL BEGIN DECLARE SECTION;
969 EXEC SQL END DECLARE SECTION;
971 EXEC SQL PREPARE stmt FROM :q;
974 EXEC SQL DECLARE curs CURSOR FOR stmt;
977 EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO mr_sqlda;
978 mr_sqlda->N = mr_sqlda->F;
979 for (i = 0; i < mr_sqlda->N; i++)
981 mr_sqlda->V[i] = sqlbuffer[i];
983 mr_sqlda->L[i] = MAX_FIELD_WIDTH;
988 EXEC SQL FETCH curs USING DESCRIPTOR mr_sqlda;
991 (*action)(count, sqlbuffer, actarg);
998 return (rowcount == 0) ? MR_NO_MATCH : MR_SUCCESS;