6 * Copyright (C) 1987, 1988 by the Massachusetts Institute of Technology
7 * For copying and distribution information, please see the file
13 static char *rcsid_qrtn_dc = "$Header$";
16 #include <mit-copyright.h>
18 #include "mr_server.h"
20 EXEC SQL INCLUDE sqlca; /* SQL Communications Area */
21 EXEC SQL INCLUDE sqlda; /* SQL Descriptor Area */
25 EXEC SQL BEGIN DECLARE SECTION;
28 char cdummy[MR_CDUMMY_LEN];
29 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 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[]);
46 int do_retrieve(struct query *q, char *pqual, char *psort,
47 int (*action)(), char *actarg);
48 int do_update(struct query *q, char *argv[], char *qual,
49 int (*action)(), char *actarg);
50 int do_append(struct query *q, char *argv[], char *pqual,
51 int (*action)(), char *actarg);
52 int do_delete(struct query *q, char *qual,
53 int (*action)(), char *actarg);
54 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
55 char *argv[], char *qual);
56 char *build_sort(struct validate *v, char *sort);
58 /* from qvalidate.dc */
59 int validate_fields(struct query *q, char *argv[], struct valobj *vo, int n);
60 int validate_row(struct query *q, char *argv[], struct validate *v);
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 SQLDA *mr_alloc_sqlda();
91 static first_open = 1;
97 /* initialize local argv */
98 for (i = 0; i < 16; i++)
99 Argv[i] = malloc(ARGLEN);
101 mr_sqlda = mr_alloc_sqlda();
110 /* open the database */
111 EXEC SQL CONNECT :database IDENTIFIED BY :database;
116 EXEC SQL SELECT data_length INTO :mr_sig_length FROM user_tab_columns
117 WHERE table_name = 'USERS' and column_name = 'SIGNATURE';
118 EXEC SQL COMMIT WORK;
125 void mr_close_database(void)
128 EXEC SQL COMMIT RELEASE;
131 int mr_check_access(client *cl, char *name, int argc, char *argv_ro[])
138 q = get_query_by_name(name, cl->args->mr_version_no);
142 return mr_verify_query(cl, q, argc, argv_ro);
145 int mr_process_query(client *cl, char *name, int argc, char *argv_ro[],
146 int (*action)(), char *actarg)
148 register struct query *q;
150 register struct validate *v;
155 EXEC SQL BEGIN DECLARE SECTION;
157 EXEC SQL END DECLARE SECTION;
158 struct save_queue *sq;
163 /* list queries command */
164 if (!strcmp(name, "_list_queries"))
166 list_queries(cl->args->mr_version_no, action, actarg);
170 /* help query command */
171 if (!strcmp(name, "_help"))
175 q = get_query_by_name(argv_ro[0], cl->args->mr_version_no);
178 help_query(q, action, actarg);
182 /* get query structure, return error if named query does not exist */
183 q = get_query_by_name(name, cl->args->mr_version_no);
188 /* setup argument vector, verify access and arguments */
189 if ((status = mr_verify_query(cl, q, argc, argv_ro)) != MR_SUCCESS)
192 /* perform any special query pre-processing */
195 status = (*v->pre_rtn)(q, Argv, cl, 0);
196 if (status != MR_SUCCESS)
203 /* for queries that do not permit wildcarding, check if row
207 status = validate_row(q, Argv, v);
208 if (status != MR_EXISTS)
212 /* build "where" clause if needed */
215 build_qual(q->qual, q->argc, Argv, qual);
221 /* build "sort" clause if needed */
223 psort = build_sort(v, sort);
227 /* if there is a followup routine, then we must save the results */
228 /* of the first query for use by the followup routine */
229 /* if q->rvar = NULL, perform post_rtn only */
232 if (v && v->post_rtn)
235 status = do_retrieve(q, pqual, psort, sq_save_args, (char *)sq);
236 if (status != MR_SUCCESS)
241 status = (*v->post_rtn)(q, sq, v, action, actarg, cl);
245 /* normal retrieve */
246 status = do_retrieve(q, pqual, psort, action, actarg);
248 if (status != MR_SUCCESS)
252 status = (*v->post_rtn)(q, Argv, cl, action, actarg);
257 /* see if row already exists */
260 status = validate_row(q, Argv, v);
261 if (status != MR_EXISTS)
265 /* build "where" clause and perform update */
266 /* if q->rvar = NULL, perform post_rtn only */
269 build_qual(q->qual, q->argc, Argv, qual);
270 incremental_before(q->rtable, qual, argv_ro);
271 status = do_update(q, &Argv[q->argc], qual, action, actarg);
272 incremental_after(q->rtable, qual, argv_ro);
273 if (status != MR_SUCCESS)
275 flush_name(argv_ro[0], q->rtable);
276 table = table_name[q->rtable];
277 if (strcmp(q->shortname, "sshi") && strcmp(q->shortname, "ssif"))
279 EXEC SQL UPDATE tblstats
280 SET updates = updates + 1, modtime = SYSDATE
281 WHERE table_name = :table;
285 /* execute followup routine (if any) */
287 status = (*v->post_rtn)(q, Argv, cl);
292 /* see if row already exists */
295 status = validate_row(q, Argv, v);
296 if (status != MR_NO_MATCH)
300 /* build "where" clause if needed */
303 build_qual(q->qual, q->argc, Argv, qual);
309 /* perform the append */
310 /* if q->rvar = NULL, perform post_rtn only */
313 incremental_clear_before();
314 status = do_append(q, &Argv[q->argc], pqual, action, actarg);
315 if (status != MR_SUCCESS)
317 if (v && v->object_id)
319 sprintf(qual, "%s.%s = %s", q->rvar, v->object_id,
320 Argv[q->argc + q->vcnt]);
321 incremental_after(q->rtable, qual, argv_ro);
324 incremental_after(q->rtable, pqual, argv_ro);
326 table = table_name[q->rtable];
327 EXEC SQL UPDATE tblstats
328 SET appends = appends + 1, modtime = SYSDATE
329 WHERE table_name = :table;
332 /* execute followup routine */
334 status = (*v->post_rtn)(q, Argv, cl);
338 /* see if row already exists */
341 status = validate_row(q, Argv, v);
342 if (status != MR_EXISTS)
346 /* build "where" clause and perform delete */
347 /* if q->rvar = NULL, perform post_rtn only */
350 build_qual(q->qual, q->argc, Argv, qual);
351 table = table_name[q->rtable];
352 incremental_before(q->rtable, qual, argv_ro);
353 status = do_delete(q, qual, action, actarg);
354 incremental_clear_after();
355 if (status != MR_SUCCESS)
357 flush_name(argv_ro[0], q->rtable);
358 EXEC SQL UPDATE tblstats
359 SET deletes = deletes + 1, modtime = SYSDATE
360 WHERE table_name = :table;
363 /* execute followup routine */
365 status = (*v->post_rtn)(q, Argv, cl);
370 if (status == MR_SUCCESS && dbms_errno != 0)
372 com_err(whoami, MR_INTERNAL, "Server didn't notice DBMS ERROR %d",
377 if (q->type == RETRIEVE)
378 EXEC SQL COMMIT WORK;
381 if (status == MR_SUCCESS)
383 EXEC SQL COMMIT WORK;
390 fprintf(journal, "%% %s %s %s",
391 cl->clname, cl->entity, ctime(&now));
392 fprintf(journal, "%s[%d] ", q->name, cl->args->mr_version_no);
393 for (i = 0; i < argc; i++)
397 requote(buf, argv_ro[i], sizeof(buf));
403 incremental_update();
408 EXEC SQL ROLLBACK WORK;
412 cache_commit(); /* commit following abort is safe */
414 if (status != MR_SUCCESS && log_flags & LOG_RES)
415 com_err(whoami, status, " (Query failed)");
419 int build_qual(char *fmt_buf, int argc, char *argv[], char *qual)
423 for (res = qual, fmt = fmt_buf; *fmt; fmt++)
431 case '%': /* %% -> % */
441 *res++ = '\''; /* double the ' */
448 res += sprintf(res, "%d", *(int *)*argv++);
450 default: /* Swallow other %? pairs */
458 *res++ = *fmt; /* text -> result buffer */
463 char *build_sort(struct validate *v, char *sort)
465 register struct valobj *vo;
475 if (vo->type == V_SORT)
477 sprintf(elem, "%d", vo->index + 1); /* Result column number */
485 return (*sort) ? sort : 0;
489 /* Build arguement vector, verify query and arguments */
493 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[])
497 register struct validate *v = q->validate;
499 register char *to, *fr, *stop;
503 /* check argument count */
505 if (q->type == UPDATE || q->type == APPEND)
510 /* copy the arguments into a local argv that we can modify */
511 for (i = 0; i < argc; i++)
513 for (to = Argv[i], fr = argv_ro[i], stop = to + ARGLEN; (*fr) && (to < stop);)
517 return MR_ARG_TOO_LONG;
524 /* check initial query access */
525 status = check_query_access(q, Argv, cl);
526 if (status != MR_SUCCESS && status != MR_PERM)
528 if (status == MR_SUCCESS)
531 /* validate arguments */
534 status = validate_fields(q, Argv, v->valobj, v->objcnt);
535 if (status != MR_SUCCESS)
539 /* perform special query access check */
540 if (!privileged && v && v->acs_rtn)
542 status = (*v->acs_rtn)(q, Argv, cl);
543 if (status != MR_SUCCESS && status != MR_PERM)
545 if (status == MR_SUCCESS)
549 return privileged ? MR_SUCCESS : MR_PERM;
553 /* This routine caches info from the database. Each query acl is stored
554 * in the query structure, and whether that acl contains everybody.
557 int check_query_access(struct query *q, char *argv[], client *cl)
559 EXEC SQL BEGIN DECLARE SECTION;
563 EXEC SQL END DECLARE SECTION;
565 /* initialize default uid */
567 EXEC SQL SELECT users_id INTO :def_uid FROM users WHERE login = 'default';
569 /* get query access control list */
575 EXEC SQL SELECT list_id INTO :acl_id FROM capacls WHERE tag = :name;
576 if (sqlca.sqlcode < 0)
578 if (sqlca.sqlcode == SQL_NO_MATCH)
582 /* check for default access */
583 EXEC SQL SELECT member_id INTO :acl_id FROM imembers
584 WHERE list_id = :acl_id AND member_type = 'USER'
585 AND member_id = :def_uid;
586 if (sqlca.sqlerrd[2] == 0)
595 if (find_member("LIST", acl_id, cl))
602 int find_member(char *list_type, int list_id, client *cl)
604 EXEC SQL BEGIN DECLARE SECTION;
605 int flag, users_id, client_id;
606 EXEC SQL END DECLARE SECTION;
608 if (!strcmp(strtrim(list_type), "USER") && list_id == cl->users_id)
611 if (!strcmp(strtrim(list_type), "KERBEROS") && list_id == -cl->client_id)
614 /* see if client is a member of list */
616 users_id = cl->users_id;
617 client_id = -cl->client_id;
618 EXEC SQL SELECT COUNT(member_id) INTO :flag FROM imembers
619 WHERE list_id = :list_id
620 AND ( ( member_type = 'USER' AND member_id = :users_id )
621 OR (member_type = 'KERBEROS' AND member_id = :client_id ) );
622 if (sqlca.sqlcode == 0)
628 int do_retrieve(struct query *q, char *pqual, char *psort,
629 int (*action)(), char *actarg)
631 build_sql_stmt(stmt_buf, "SELECT", q->tlist, NULL, pqual);
634 strcat(stmt_buf, " ORDER BY ");
635 strcat(stmt_buf, psort);
638 return do_for_all_rows(stmt_buf, q->vcnt, action, (int)actarg);
641 char *sqlstrstr(char *str, char *pat)
643 register char *p = pat;
647 if (*str == '\'') /* Skip over single-quote delimited substrings */
649 while (*++str && (*str != '\''))
657 while (*++p && (*++s == *p))
660 p = pat; /* failed */
663 while (*p && *++str);
670 void optimize_sql_stmt(char *buf)
672 char *point = buf, *pat, *eopat, *esc1, *esc2, *csr;
674 for (point = buf; point = sqlstrstr(point, "LIKE"); point++)
676 /* Now pointing to string "LIKE" */
678 /* Look at next word */
679 for (pat = point + 4; *pat == ' '; pat++)
682 /* Is it a single-quote delimited string? */
686 /* look for "escape" clause - save escape character */
687 /* 1. Find end of pattern */
688 for (eopat = pat + 1; 1; eopat++)
692 if (eopat[1] == '\'') /* single-quote is self-escaping */
699 /* 2. Look at next word */
700 for (esc1 = eopat; *++esc1 == ' ';)
703 /* 3. esc1 = 0 if not "ESCAPE '?'", where the ? may be any character. */
704 if (strncmp(esc1, "ESCAPE", 6))
709 for (esc2 = esc1 + 6; *esc2 == ' '; esc2++)
713 continue; /* Bad SQL syntax. Skip. */
714 /* esc2 now points at the escape character itself */
716 continue; /* Weird escape string. Skip. */
721 /* Is pattern free from special characters? */
722 for (csr = pat; csr < eopat; csr++)
724 if ((*csr == '%') || (*csr == '_') || (*csr == *esc2))
728 continue; /* Uses pattern matching. Skip. */
730 /* Optimize the query statement */
731 /* 1. Change "LIKE" to " = " */
732 memcpy(point, " = ", 4);
734 /* 2. Change "ESCAPE" to " " */
737 memset(esc1, ' ', 6);
738 /* 3. Change "'*'" to " " */
739 /* (Changes '''' to " ") */
741 memset(esc2 - 1, ' ', (*esc2 == '\'') ? 4 : 3);
746 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
747 char *argv[], char *qual)
749 char fmt_buf[MR_STMTBUF_LEN];
750 register char *res, *fmt;
753 sprintf(fmt_buf, "%s %s WHERE %s", cmd, targetlist, qual);
755 sprintf(fmt_buf, "%s %s", cmd, targetlist);
757 for (res = result_buf, fmt = fmt_buf; *fmt; fmt++)
765 case '%': /* %% -> % */
775 *res++ = '\''; /* double the ' */
782 res += sprintf(res, "%d", *(int *)*argv++);
784 default: /* Swallow other %? pairs */
792 *res++ = *fmt; /* text -> result buffer */
796 optimize_sql_stmt(result_buf);
799 int do_update(struct query *q, char *argv[], char *qual,
800 int (*action)(), char *actarg)
802 build_sql_stmt(stmt_buf, "UPDATE", q->tlist, argv, qual);
803 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
809 int do_append(struct query *q, char *argv[], char *pqual,
810 int (*action)(), char *actarg)
812 build_sql_stmt(stmt_buf, "INSERT", q->tlist, argv, pqual);
813 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
819 int do_delete(struct query *q, char *qual, int (*action)(), char *actarg)
821 sprintf(stmt_buf, "DELETE FROM %s WHERE %s", table_name[q->rtable], qual);
822 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
830 ** set_next_object_id - set next object id in values table
832 ** Inputs: object - object name in values table and in objects
833 ** table - name of table objects are found in
834 ** limit - should the ID be range limited
836 ** - called before an APPEND operation to set the next object id to
837 ** be used for the new record to the next free value
841 int set_next_object_id(char *object, enum tables table, int limit)
843 EXEC SQL BEGIN DECLARE SECTION;
846 EXEC SQL END DECLARE SECTION;
849 EXEC SQL SELECT value INTO :value FROM numvalues WHERE name = :obj;
850 if (sqlca.sqlerrd[2] != 1)
853 starting_value = value;
856 if (limit && value > MAX_ID_VALUE)
857 value = MIN_ID_VALUE;
859 sprintf(stmt_buf, "SELECT %s FROM %s WHERE %s = %d",
860 object, table_name[table], object, value);
862 if (sqlca.sqlcode < 0)
864 if (sqlca.sqlcode == SQL_NO_MATCH)
868 if (limit && value == starting_value)
870 com_err(whoami, 0, "All id values have been used");
876 com_err(whoami, 0, "setting ID %s to %d", object, value);
877 EXEC SQL UPDATE numvalues SET value = :value WHERE name = :obj;
882 /* Turn a kerberos name into the user's ID of the account that principal
883 * owns. Sets the kerberos ID and user ID.
886 int set_krb_mapping(char *name, char *login, int ok, int *kid, int *uid)
888 EXEC SQL BEGIN DECLARE SECTION;
891 EXEC SQL END DECLARE SECTION;
897 EXEC SQL SELECT km.users_id, km.string_id INTO :u_id, :k_id
898 FROM krbmap km, strings str
899 WHERE km.string_id = str.string_id AND str.string = :krbname;
900 EXEC SQL COMMIT WORK;
905 if (sqlca.sqlerrd[2] == 1)
912 if (name_to_id(name, STRINGS_TABLE, &k_id) == MR_SUCCESS)
921 if (name_to_id(login, USERS_TABLE, uid) != MR_SUCCESS)
932 /* For now this just checks the argc's. It should also see that there
933 * are no duplicate names.
936 void sanity_check_queries(void)
939 int maxv = 0, maxa = 0;
940 extern int QueryCount2;
941 extern struct query Queries2[];
943 #define MAX(x, y) ((x) > (y) ? (x) : (y))
945 for (i = 0; i < QueryCount2; i++)
947 maxv = MAX(maxv, Queries2[i].vcnt);
948 maxa = MAX(maxa, Queries2[i].argc);
950 if (MAX(maxv, maxa) > QMAXARGS)
952 com_err(whoami, 0, "A query has more args than QMAXARGS");
958 /* Generically do a SELECT, storing the results in the provided buffers */
960 void dosql(char *buffers[])
962 int i, errcode = 0, errlen;
964 EXEC SQL PREPARE inc_stmt FROM :stmt_buf;
967 EXEC SQL DECLARE inc_crs CURSOR FOR inc_stmt;
968 EXEC SQL OPEN inc_crs;
969 mr_sqlda->N = QMAXARGS;
970 EXEC SQL DESCRIBE SELECT LIST FOR inc_stmt INTO mr_sqlda;
971 mr_sqlda->N = mr_sqlda->F;
972 for (i = 0; i < mr_sqlda->N; i++)
974 mr_sqlda->V[i] = buffers[i];
976 mr_sqlda->L[i] = ARGLEN;
978 EXEC SQL FETCH inc_crs USING DESCRIPTOR mr_sqlda;
980 /* if we got an error from the FETCH, we have to preserve it or the
981 close will reset it and the caller will think nothing happened */
984 errcode = sqlca.sqlcode;
985 errlen = sqlca.sqlerrm.sqlerrml;
988 EXEC SQL CLOSE inc_crs;
991 sqlca.sqlcode = errcode;
992 sqlca.sqlerrm.sqlerrml = errlen;
996 int do_for_all_rows(char *query, int count, int (*action)(), int actarg)
999 EXEC SQL BEGIN DECLARE SECTION;
1001 EXEC SQL END DECLARE SECTION;
1003 EXEC SQL PREPARE stmt FROM :q;
1006 EXEC SQL DECLARE curs CURSOR FOR stmt;
1008 mr_sqlda->N = count;
1009 EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO mr_sqlda;
1010 mr_sqlda->N = mr_sqlda->F;
1011 for (i = 0; i < mr_sqlda->N; i++)
1013 mr_sqlda->V[i] = sqlbuffer[i];
1014 mr_sqlda->T[i] = 97;
1015 mr_sqlda->L[i] = ARGLEN;
1020 EXEC SQL FETCH curs USING DESCRIPTOR mr_sqlda;
1023 (*action)(count, sqlbuffer, actarg);
1026 EXEC SQL CLOSE curs;
1030 return (rowcount == 0) ? MR_NO_MATCH : MR_SUCCESS;