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;
27 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;
43 extern int QueryCount, max_version;
44 extern struct query Queries[];
46 /* Put this in a variable so that we can patch it if necessary */
47 int max_row_count = 8192;
49 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[]);
50 int do_retrieve(struct query *q, char *pqual,
51 int (*action)(int, char *[], void *), void *actarg);
52 int do_update(struct query *q, char *argv[], char *qual,
53 int (*action)(int, char *[], void *), void *actarg);
54 int do_append(struct query *q, char *argv[], char *pqual,
55 int (*action)(int, char *[], void *), void *actarg);
56 int do_delete(struct query *q, char *qual,
57 int (*action)(int, char *[], void *), void *actarg);
58 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
59 char *argv[], char *qual);
61 SQLDA *mr_alloc_sqlda(void);
62 void sqlglm(char *, int *, int *);
65 * dbmserr: Called when the DBMS indicates an error.
70 EXEC SQL BEGIN DECLARE SECTION;
72 EXEC SQL END DECLARE SECTION;
73 int bufsize = 256, msglength = 0;
75 dbms_errno = -sqlca.sqlcode;
76 mr_errcode = MR_DBMS_ERR;
77 com_err(whoami, MR_DBMS_ERR, " code %d\n", dbms_errno);
78 sqlglm(err_msg, &bufsize, &msglength);
79 err_msg[msglength] = 0;
80 com_err(whoami, 0, "SQL error text = %s", err_msg);
81 critical_alert(whoami, "MOIRA", "Moira server encountered DBMS ERROR %d\n%s",
85 /* This is declarative, not executed. Applies from here on, in this file. */
86 EXEC SQL WHENEVER SQLERROR DO dbmserr();
88 int mr_open_database(void)
91 static int first_open = 1;
97 /* initialize local argv */
98 for (i = 0; i < QMAXARGS; i++)
99 Argv[i] = xmalloc(MAX_FIELD_WIDTH);
101 mr_sqlda = mr_alloc_sqlda();
109 /* open the database */
110 EXEC SQL CONNECT :database IDENTIFIED BY :database;
115 EXEC SQL SELECT list_id INTO :proxy_acl FROM capacls
116 WHERE capability = 'proxy';
123 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, cl->version);
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)
149 EXEC SQL BEGIN DECLARE SECTION;
151 EXEC SQL END DECLARE SECTION;
152 struct save_queue *sq;
157 /* list queries command */
158 if (!strcmp(name, "_list_queries"))
160 list_queries(cl, action, actarg);
164 /* help query command */
165 if (!strcmp(name, "_help"))
169 q = get_query_by_name(argv_ro[0], cl->version);
172 help_query(q, action, actarg);
176 /* get query structure, return error if named query does not exist */
177 q = get_query_by_name(name, cl->version);
182 /* setup argument vector, verify access and arguments */
183 if ((status = mr_verify_query(cl, q, argc, argv_ro)) != MR_SUCCESS)
186 /* perform any special query pre-processing */
189 status = (*v->pre_rtn)(q, Argv, cl);
190 if (status != MR_SUCCESS)
197 /* for queries that do not permit wildcarding, check if row
201 status = validate_row(q, Argv, v);
202 if (status != MR_EXISTS)
206 /* build "where" clause if needed */
208 qual = build_qual(q->qual, q->argc, Argv);
210 /* if there is a followup routine, then we must save the results */
211 /* of the first query for use by the followup routine */
212 /* if q->rvar = NULL, perform post_rtn only */
215 if (v && v->post_rtn)
218 status = do_retrieve(q, qual, sq_save_args, sq);
219 if (status != MR_SUCCESS)
224 while (sq_get_data(sq, &argv))
226 for (i = 0; i < q->vcnt; i++)
233 status = (*v->post_rtn)(q, sq, v, action, actarg, cl);
237 /* normal retrieve */
238 status = do_retrieve(q, qual, action, actarg);
240 if (status != MR_SUCCESS)
244 status = (*v->post_rtn)(q, Argv, cl, action, actarg);
249 /* see if row already exists */
252 status = validate_row(q, Argv, v);
253 if (status != MR_EXISTS)
257 /* build "where" clause and perform update */
258 /* if q->rvar = NULL, perform post_rtn only */
261 qual = build_qual(q->qual, q->argc, Argv);
262 incremental_before(q->rtable, qual, argv_ro);
263 status = do_update(q, &Argv[q->argc], qual, action, actarg);
264 incremental_after(q->rtable, qual, argv_ro);
265 if (status != MR_SUCCESS)
267 table = table_name[q->rtable];
268 if (strcmp(q->shortname, "sshi") && strcmp(q->shortname, "ssif"))
270 EXEC SQL UPDATE tblstats
271 SET updates = updates + 1, modtime = SYSDATE
272 WHERE table_name = :table;
276 /* execute followup routine (if any) */
278 status = (*v->post_rtn)(q, Argv, cl);
283 /* see if row already exists */
286 status = validate_row(q, Argv, v);
287 if (status != MR_NO_MATCH)
291 /* build "where" clause if needed */
293 qual = build_qual(q->qual, q->argc, Argv);
295 /* perform the append */
296 /* if q->rvar = NULL, perform post_rtn only */
299 incremental_clear_before();
300 status = do_append(q, &Argv[q->argc], qual, action, actarg);
301 if (status != MR_SUCCESS)
303 if (v && v->object_id)
305 qual = realloc(qual, 15 + strlen(q->rvar) +
306 strlen(Argv[q->argc + q->vcnt]));
307 sprintf(qual, "%s.%s = %s", q->rvar, v->object_id,
308 Argv[q->argc + q->vcnt]);
309 incremental_after(q->rtable, qual, argv_ro);
312 incremental_after(q->rtable, qual, argv_ro);
314 table = table_name[q->rtable];
315 EXEC SQL UPDATE tblstats
316 SET appends = appends + 1, modtime = SYSDATE
317 WHERE table_name = :table;
320 /* execute followup routine */
322 status = (*v->post_rtn)(q, Argv, cl);
326 /* see if row already exists */
329 status = validate_row(q, Argv, v);
330 if (status != MR_EXISTS)
334 /* build "where" clause and perform delete */
335 /* if q->rvar = NULL, perform post_rtn only */
338 qual = build_qual(q->qual, q->argc, Argv);
339 table = table_name[q->rtable];
340 incremental_before(q->rtable, qual, argv_ro);
341 status = do_delete(q, qual, action, actarg);
342 incremental_clear_after();
343 if (status != MR_SUCCESS)
345 EXEC SQL UPDATE tblstats
346 SET deletes = deletes + 1, modtime = SYSDATE
347 WHERE table_name = :table;
350 /* execute followup routine */
352 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 == MR_Q_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();
400 EXEC SQL ROLLBACK WORK;
405 if (status != MR_SUCCESS)
406 com_err(whoami, status, " (Query failed)");
410 char *build_qual(char *fmt_buf, int argc, char *argv[])
412 char *res, *result_buf, *fmt, *arg, *like, *p;
414 result_buf = xmalloc(2 * (strlen(fmt_buf) + argc * ARGLEN));
419 like = strstr(fmt, "LIKE");
420 arg = strchr(fmt, '%');
422 /* Look through the format for LIKE expressions and arguments.
423 Substitute in the arguments, simplify the `LIKE's to `='s
424 where possible, and insert ESCAPE clauses where needed */
429 if ((!like && !arg) || argc == 0)
431 /* only plain text remains */
433 res = strchr(res, '\0');
436 else if (!like || arg < like)
438 /* regular arg: copy up to arg, then substitute */
439 strncpy(res, fmt, arg - fmt);
451 /* copy string, doubling single quotes */
462 res += sprintf(res, "%d", *(int *)*argv++);
467 arg = strchr(fmt, '%');
469 /* LIKE arg: copy over up to the arg, then copy and convert arg */
470 int escape = 0, pattern = 0;
471 char *likepos = res + (like - fmt);
473 strncpy(res, fmt, arg - fmt);
476 /* copy arg, converting UNIX globs to `SQL voodoo', and noting
477 if we'll need an ESCAPE clause */
478 for (p = *argv++; *p; p++)
484 *res++ = '%'; /* need to double for build_sql_stmt */
511 /* if no pattern characters, write over "LIKE" with " = " */
512 if (!pattern && !escape)
513 memcpy(likepos, " = ", 4);
516 while (*fmt && *fmt != ' ')
520 res += sprintf(res, " ESCAPE '*'");
522 arg = strchr(fmt, '%');
523 like = strstr(fmt, "LIKE");
528 result_buf = realloc(result_buf, strlen(result_buf) + 1);
532 /* Build arguement vector, verify query and arguments */
536 int mr_verify_query(client *cl, struct query *q, int argc, char *argv_ro[])
540 struct validate *v = q->validate;
542 char *to, *fr, *stop;
546 /* check argument count */
548 if (q->type == MR_Q_UPDATE || q->type == MR_Q_APPEND)
553 /* copy the arguments into a local argv that we can modify */
554 for (i = 0; i < argc; i++)
556 for (to = Argv[i], fr = argv_ro[i], stop = to + MAX_FIELD_WIDTH; (*fr) && (to < stop);)
560 return MR_ARG_TOO_LONG;
564 /* Check initial query access. If we're acting as a proxy, only allow
565 * access if the query has "default" as a capacl.
567 status = check_query_access(q, Argv, cl);
568 if (status != MR_SUCCESS && status != MR_PERM)
570 if (status == MR_SUCCESS && (!cl->proxy_id || q->everybody))
573 /* validate arguments */
576 status = validate_fields(q, Argv, v->valobj, v->objcnt);
577 if (status != MR_SUCCESS)
581 /* perform special query access check */
582 if (!privileged && v && v->acs_rtn)
584 status = (*v->acs_rtn)(q, Argv, cl);
585 if (status != MR_SUCCESS && status != MR_PERM)
587 if (status == MR_SUCCESS)
591 return privileged ? MR_SUCCESS : MR_PERM;
594 int check_query_access(struct query *q, char *argv[], client *cl)
596 EXEC SQL BEGIN DECLARE SECTION;
600 EXEC SQL END DECLARE SECTION;
602 /* initialize default uid */
604 EXEC SQL SELECT users_id INTO :def_uid FROM users WHERE login = 'default';
607 EXEC SQL SELECT list_id INTO :acl_id FROM capacls WHERE tag = :name;
608 if (sqlca.sqlcode < 0)
610 if (sqlca.sqlcode == SQL_NO_MATCH)
614 /* check for default access */
615 EXEC SQL SELECT member_id INTO :acl_id FROM imembers
616 WHERE list_id = :acl_id AND member_type = 'USER'
617 AND member_id = :def_uid;
618 if (sqlca.sqlerrd[2] == 0)
626 if (find_member("LIST", acl_id, cl))
632 int find_member(char *list_type, int list_id, client *cl)
634 EXEC SQL BEGIN DECLARE SECTION;
635 int flag, users_id, client_id;
636 EXEC SQL END DECLARE SECTION;
638 if (!strcmp(strtrim(list_type), "USER") && list_id == cl->users_id)
641 if (!strcmp(strtrim(list_type), "KERBEROS") && list_id == -cl->client_id)
644 if (!strcmp(strtrim(list_type), "LIST"))
646 /* see if client is a member of list */
648 users_id = cl->users_id;
649 client_id = -cl->client_id;
650 EXEC SQL SELECT COUNT(member_id) INTO :flag FROM imembers
651 WHERE list_id = :list_id
652 AND ( ( member_type = 'USER' AND member_id = :users_id )
653 OR (member_type = 'KERBEROS' AND member_id = :client_id ) );
654 if (sqlca.sqlcode == 0)
662 int do_retrieve(struct query *q, char *pqual,
663 int (*action)(int, char *[], void *), void *actarg)
665 build_sql_stmt(stmt_buf, "SELECT", q->tlist, NULL, pqual);
668 strcat(stmt_buf, " ORDER BY ");
669 strcat(stmt_buf, q->sort);
672 return do_for_all_rows(stmt_buf, q->vcnt, action, actarg);
675 void build_sql_stmt(char *result_buf, char *cmd, char *targetlist,
676 char *argv[], char *qual)
678 char fmt_buf[MR_STMTBUF_LEN];
682 sprintf(fmt_buf, "%s %s WHERE %s", cmd, targetlist, qual);
684 sprintf(fmt_buf, "%s %s", cmd, targetlist);
686 for (res = result_buf, fmt = fmt_buf; *fmt; fmt++)
694 case '%': /* %% -> % */
704 *res++ = '\''; /* double the ' */
711 res += sprintf(res, "%d", *(int *)*argv++);
713 default: /* Swallow other %? pairs */
721 *res++ = *fmt; /* text -> result buffer */
726 int do_update(struct query *q, char *argv[], char *qual,
727 int (*action)(int, char *[], void *), void *actarg)
729 build_sql_stmt(stmt_buf, "UPDATE", q->tlist, argv, qual);
730 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
736 int do_append(struct query *q, char *argv[], char *pqual,
737 int (*action)(int, char *[], void *), void *actarg)
739 build_sql_stmt(stmt_buf, "INSERT", q->tlist, argv, pqual);
740 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
746 int do_delete(struct query *q, char *qual,
747 int (*action)(int, char *[], void *), void *actarg)
749 sprintf(stmt_buf, "DELETE FROM %s WHERE %s", table_name[q->rtable], qual);
750 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
758 ** set_next_object_id - set next object id in values table
760 ** Inputs: object - object name in values table and in objects
761 ** table - name of table objects are found in
762 ** limit - should the ID be range limited
764 ** - called before an MR_Q_APPEND operation to set the next object id to
765 ** be used for the new record to the next free value
769 int set_next_object_id(char *object, enum tables table, int limit)
771 EXEC SQL BEGIN DECLARE SECTION;
774 EXEC SQL END DECLARE SECTION;
777 EXEC SQL SELECT value INTO :value FROM numvalues WHERE name = :obj;
778 if (sqlca.sqlerrd[2] != 1)
781 starting_value = value;
784 #ifdef ULTRIX_ID_HOLE
785 if (limit && value > 31999 && value < 32768)
788 if (limit && value > MAX_ID_VALUE)
789 value = MIN_ID_VALUE;
791 sprintf(stmt_buf, "SELECT %s FROM %s WHERE %s = %d",
792 object, table_name[table], object, value);
794 if (sqlca.sqlcode < 0)
796 if (sqlca.sqlcode == SQL_NO_MATCH)
800 if (limit && value == starting_value)
802 com_err(whoami, 0, "All id values have been used");
807 com_err(whoami, 0, "setting ID %s to %d", object, value);
808 EXEC SQL UPDATE numvalues SET value = :value WHERE name = :obj;
813 /* Turn a kerberos name into the user's ID of the account that principal
814 * owns. Sets the kerberos ID and user ID.
817 int set_krb_mapping(char *name, char *login, int ok, int *kid, int *uid)
819 EXEC SQL BEGIN DECLARE SECTION;
822 EXEC SQL END DECLARE SECTION;
828 EXEC SQL SELECT km.users_id, km.string_id INTO :u_id, :k_id
829 FROM krbmap km, strings str
830 WHERE km.string_id = str.string_id AND str.string = :krbname;
831 EXEC SQL COMMIT WORK;
836 if (sqlca.sqlerrd[2] == 1)
843 if (name_to_id(name, STRINGS_TABLE, &k_id) == MR_SUCCESS)
852 if (name_to_id(login, USERS_TABLE, uid) != MR_SUCCESS)
863 void sanity_check_queries(void)
866 int maxv = 0, maxa = 0;
868 #define MAX(x, y) ((x) > (y) ? (x) : (y))
870 for (i = 0; i < QueryCount; i++)
872 maxv = MAX(maxv, Queries[i].vcnt);
873 maxa = MAX(maxa, Queries[i].argc);
874 max_version = MAX(max_version, Queries[i].version);
876 if (MAX(maxv, maxa) > QMAXARGS)
878 com_err(whoami, 0, "A query has more args than QMAXARGS");
884 /* Generically do a SELECT, storing the results in the provided buffers */
886 void dosql(char *buffers[])
888 int i, errcode = 0, errlen;
890 EXEC SQL PREPARE inc_stmt FROM :stmt_buf;
893 EXEC SQL DECLARE inc_crs CURSOR FOR inc_stmt;
894 EXEC SQL OPEN inc_crs;
895 mr_sqlda->N = QMAXARGS;
896 EXEC SQL DESCRIBE SELECT LIST FOR inc_stmt INTO mr_sqlda;
897 mr_sqlda->N = mr_sqlda->F;
898 for (i = 0; i < mr_sqlda->N; i++)
900 mr_sqlda->V[i] = buffers[i];
902 mr_sqlda->L[i] = MAX_FIELD_WIDTH;
904 EXEC SQL FETCH inc_crs USING DESCRIPTOR mr_sqlda;
906 /* if we got an error from the FETCH, we have to preserve it or the
907 close will reset it and the caller will think nothing happened */
910 errcode = sqlca.sqlcode;
911 errlen = sqlca.sqlerrm.sqlerrml;
914 EXEC SQL CLOSE inc_crs;
917 sqlca.sqlcode = errcode;
918 sqlca.sqlerrm.sqlerrml = errlen;
922 int do_for_all_rows(char *query, int count,
923 int (*action)(int, char *[], void *), void *actarg)
926 EXEC SQL BEGIN DECLARE SECTION;
928 EXEC SQL END DECLARE SECTION;
930 EXEC SQL PREPARE stmt FROM :q;
933 EXEC SQL DECLARE curs CURSOR FOR stmt;
936 EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO mr_sqlda;
937 mr_sqlda->N = mr_sqlda->F;
938 for (i = 0; i < mr_sqlda->N; i++)
940 mr_sqlda->V[i] = sqlbuffer[i];
942 mr_sqlda->L[i] = MAX_FIELD_WIDTH;
945 while (rowcount < max_row_count)
947 EXEC SQL FETCH curs USING DESCRIPTOR mr_sqlda;
950 (*action)(count, sqlbuffer, actarg);
957 if (rowcount == max_row_count)
959 critical_alert(whoami, "moirad", "attempted query with too many rows");
962 else if (rowcount == 0)
968 /* Do a name to ID translation. Moved from cache.pc because we did away
969 * with the cache, but what this function does is still useful to us.
972 int name_to_id(char *name, enum tables type, int *id)
974 EXEC SQL BEGIN DECLARE SECTION;
977 EXEC SQL END DECLARE SECTION;
984 if (strchr(iname, '@') || (strlen(iname) > 8))
986 sqlca.sqlcode = SQL_NO_MATCH;
989 EXEC SQL SELECT users_id INTO :j FROM users WHERE login = :iname;
992 EXEC SQL SELECT list_id INTO :j FROM list WHERE name = :iname;
995 EXEC SQL SELECT mach_id INTO :j FROM machine WHERE name = UPPER(:iname);
998 EXEC SQL SELECT snet_id INTO :j FROM subnet WHERE name = UPPER(:iname);
1000 case CLUSTERS_TABLE:
1001 EXEC SQL SELECT clu_id INTO :j FROM clusters WHERE name = :iname;
1004 EXEC SQL SELECT filsys_id INTO :j FROM filesys WHERE label = :iname;
1007 if (!iname[0]) /* special-case empty string */
1012 EXEC SQL SELECT string_id INTO :j FROM strings WHERE string = :iname;
1014 case CONTAINERS_TABLE:
1015 EXEC SQL SELECT cnt_id INTO :j FROM containers WHERE LOWER(name) =
1021 if (sqlca.sqlcode == SQL_NO_MATCH)
1023 if (sqlca.sqlerrd[2] > 1)
1024 return MR_NOT_UNIQUE;
1032 /* Perform an ID to name mapping. name should be a pointer to a pointer to
1033 * malloc'ed data. The buffer it refers to will be freed, and a new buffer
1034 * allocated with the answer.
1036 * This used to be in cache.pc, but we've removed the cache, and this function
1037 * is still useful to us.
1040 int id_to_name(int id, enum tables type, char **name)
1042 EXEC SQL BEGIN DECLARE SECTION;
1043 char iname[MAX_FIELD_WIDTH];
1045 EXEC SQL END DECLARE SECTION;
1052 EXEC SQL SELECT login INTO :iname FROM users WHERE users_id = :j;
1055 EXEC SQL SELECT name INTO :iname FROM list WHERE list_id = :j;
1058 EXEC SQL SELECT name INTO :iname FROM machine WHERE mach_id = :j;
1061 EXEC SQL SELECT name INTO :iname FROM subnet WHERE snet_id = :j;
1063 case CLUSTERS_TABLE:
1064 EXEC SQL SELECT name INTO :iname FROM clusters WHERE clu_id = :j;
1067 EXEC SQL SELECT label INTO :iname FROM filesys WHERE filsys_id = :j;
1070 EXEC SQL SELECT string INTO :iname FROM strings WHERE string_id = :j;
1072 case CONTAINERS_TABLE:
1073 EXEC SQL SELECT name INTO :iname FROM containers WHERE cnt_id = :j;
1078 if (sqlca.sqlcode == SQL_NO_MATCH)
1081 sprintf(iname, "#%d", j);
1082 *name = xstrdup(iname);
1085 if (sqlca.sqlerrd[2] > 1)
1090 *name = xstrdup(strtrim(iname));