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);
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;
96 /* initialize local argv */
97 for (i = 0; i < 16; i++)
98 Argv[i] = malloc(ARGLEN);
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 WHERE table_name='USERS' and column_name='SIGNATURE';
116 EXEC SQL COMMIT WORK;
123 void mr_close_database(void)
126 EXEC SQL COMMIT RELEASE;
129 int mr_check_access(cl, name, argc, argv_ro)
131 char *name, *argv_ro[];
139 q = get_query_by_name(name, cl->args->mr_version_no);
140 if (q == (struct query *)0)
141 return(MR_NO_HANDLE);
143 return(mr_verify_query(cl, q, argc, argv_ro));
146 int mr_process_query(cl, name, argc, argv_ro, action, actarg)
148 char *name, *argv_ro[], *actarg;
149 int argc, (*action)();
151 register struct query *q;
153 register struct validate *v;
158 EXEC SQL BEGIN DECLARE SECTION;
160 EXEC SQL END DECLARE SECTION;
161 struct save_queue *sq;
166 /* list queries command */
167 if (!strcmp(name, "_list_queries")) {
168 list_queries(cl->args->mr_version_no, action, actarg);
172 /* help query command */
173 if (!strcmp(name, "_help")) {
176 q = get_query_by_name(argv_ro[0], cl->args->mr_version_no);
177 if (q == (struct query *)0) return(MR_NO_HANDLE);
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);
184 if (q == (struct query *)0) return(MR_NO_HANDLE);
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 */
192 if (v && v->pre_rtn) {
193 status = (*v->pre_rtn)(q, Argv, cl, 0);
194 if (status != MR_SUCCESS)
200 /* for queries that do not permit wildcarding, check if row
203 status = validate_row(q, Argv, v);
204 if (status != MR_EXISTS) break;
207 /* build "where" clause if needed */
209 build_qual(q->qual, q->argc, Argv, qual);
215 /* build "sort" clause if needed */
216 if (v && v->valobj) {
217 psort = build_sort(v, sort);
222 /* if there is a followup routine, then we must save the results */
223 /* of the first query for use by the followup routine */
224 /* if q->rvar = NULL, perform post_rtn only */
226 if (v && v->post_rtn) {
228 status = do_retrieve(q, pqual, psort, sq_save_args,
230 if (status != MR_SUCCESS) {
234 status = (*v->post_rtn)(q, sq, v, action, actarg, cl);
236 /* normal retrieve */
237 status = do_retrieve(q, pqual, psort, action, actarg);
239 if (status != MR_SUCCESS) break;
241 status = (*v->post_rtn)(q, Argv, cl, action, actarg);
247 /* see if row already exists */
249 status = validate_row(q, Argv, v);
250 if (status != MR_EXISTS) break;
253 /* build "where" clause and perform update */
254 /* if q->rvar = NULL, perform post_rtn only */
256 build_qual(q->qual, q->argc, Argv, qual);
257 incremental_before(q->rtable, qual, argv_ro);
258 status = do_update(q, &Argv[q->argc], qual, action, actarg);
259 incremental_after(q->rtable, qual, argv_ro);
260 if (status != MR_SUCCESS) break;
261 flush_name(argv_ro[0], q->rtable);
262 table = table_name[q->rtable];
263 if (strcmp(q->shortname, "sshi") && strcmp(q->shortname, "ssif")) {
264 EXEC SQL UPDATE tblstats
265 SET updates = updates + 1, modtime = SYSDATE
266 WHERE table_name = :table;
270 /* execute followup routine (if any) */
271 if (v->post_rtn) status = (*v->post_rtn)(q, Argv, cl);
276 /* see if row already exists */
278 status = validate_row(q, Argv, v);
279 if (status != MR_NO_MATCH) break;
282 /* build "where" clause if needed */
284 build_qual(q->qual, q->argc, Argv, qual);
290 /* perform the append */
291 /* if q->rvar = NULL, perform post_rtn only */
293 incremental_clear_before();
294 status = do_append(q, &Argv[q->argc], pqual, action, actarg);
295 if (status != MR_SUCCESS) break;
296 if (v && v->object_id) {
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);
301 incremental_after(q->rtable, pqual, argv_ro);
303 table = table_name[q->rtable];
304 EXEC SQL UPDATE tblstats
305 SET appends = appends + 1, modtime = SYSDATE
306 WHERE table_name = :table;
309 /* execute followup routine */
310 if (v->post_rtn) status = (*v->post_rtn)(q, Argv, cl);
314 /* see if row already exists */
316 status = validate_row(q, Argv, v);
317 if (status != MR_EXISTS) break;
320 /* build "where" clause and perform delete */
321 /* if q->rvar = NULL, perform post_rtn only */
323 build_qual(q->qual, q->argc, Argv, qual);
324 table = table_name[q->rtable];
325 incremental_before(q->rtable, qual, argv_ro);
326 status = do_delete(q, qual, action, actarg);
327 incremental_clear_after();
328 if (status != MR_SUCCESS) break;
329 flush_name(argv_ro[0], q->rtable);
330 EXEC SQL UPDATE tblstats
331 SET deletes = deletes + 1, modtime = SYSDATE
332 WHERE table_name = :table;
335 /* execute followup routine */
336 if (v->post_rtn) status = (*v->post_rtn)(q, Argv, cl);
342 if (status == MR_SUCCESS && dbms_errno != 0) {
343 com_err(whoami, MR_INTERNAL, "Server didn't notice DBMS ERROR %d",
348 if (q->type == RETRIEVE) {
349 EXEC SQL COMMIT WORK;
351 if (status == MR_SUCCESS) {
352 EXEC SQL COMMIT WORK;
358 fprintf(journal, "%% %s %s %s",
359 cl->clname, cl->entity, ctime(&now));
360 fprintf(journal, "%s[%d] ", q->name, cl->args->mr_version_no);
361 for (i = 0; i < argc; i++) {
365 requote(buf, argv_ro[i], sizeof(buf));
371 incremental_update();
374 EXEC SQL ROLLBACK WORK;
378 cache_commit(); /* commit following abort is safe */
380 if (status != MR_SUCCESS && log_flags & LOG_RES)
381 com_err(whoami, status, " (Query failed)");
385 int build_qual(fmt_buf, argc, argv, qual)
386 char *fmt_buf, *argv[], *qual;
391 for(res=qual, fmt=fmt_buf; *fmt; fmt++) {
395 case '%': /* %% -> % */
402 if(*p=='\'') *res++='\''; /* double the ' */
409 res+=sprintf(res,"%d",*(int *)*argv++);
411 default: /* Swallow other %? pairs */
415 } else *res++ = *fmt; /* text -> result buffer */
420 char *build_sort(v, sort)
424 register struct valobj *vo;
433 if (vo->type == V_SORT) {
434 sprintf(elem, "%d", vo->index + 1); /* Result column number */
435 if (*sort) strcat(sort, ", ");
441 return ((*sort) ? sort : 0);
445 /* Build arguement vector, verify query and arguments */
449 int mr_verify_query(cl, q, argc, argv_ro)
457 register struct validate *v = q->validate;
459 register char *to,*fr,*stop;
463 /* copy the arguments into a local argv that we can modify */
464 if (argc >= QMAXARGS)
466 for (i = 0; i < argc; i++) {
467 for (to=Argv[i], fr=argv_ro[i], stop=to+ARGLEN; (*fr) && (to<stop);)
471 return(MR_ARG_TOO_LONG);
478 /* check initial query access */
479 status = check_query_access(q, Argv, cl);
480 if (status != MR_SUCCESS && status != MR_PERM)
482 if (status == MR_SUCCESS)
485 /* check argument count */
487 if (q->type == UPDATE || q->type == APPEND) argreq += q->vcnt;
488 if (argc != argreq) return(MR_ARGS);
490 /* validate arguments */
491 if (v && v->valobj) {
492 status = validate_fields(q, Argv, v->valobj, v->objcnt);
493 if (status != MR_SUCCESS) return(status);
496 /* perform special query access check */
497 if (!privileged && v && v->acs_rtn) {
498 status = (*v->acs_rtn)(q, Argv, cl);
499 if (status != MR_SUCCESS && status != MR_PERM)
501 if (status == MR_SUCCESS)
505 return(privileged ? MR_SUCCESS : MR_PERM);
509 /* This routine caches info from the database. Each query acl is stored
510 * in the query structure, and whether that acl contains everybody.
513 int check_query_access(q, argv, cl)
518 EXEC SQL BEGIN DECLARE SECTION;
522 EXEC SQL END DECLARE SECTION;
526 /* initialize default uid */
528 EXEC SQL SELECT users_id INTO :def_uid FROM users WHERE login='default';
531 /* get query access control list */
536 EXEC SQL SELECT list_id INTO :acl_id FROM capacls WHERE tag = :name;
537 if (sqlca.sqlcode < 0) return(MR_DBMS_ERR);
538 if (sqlca.sqlcode == SQL_NO_MATCH) return(MR_PERM);
541 /* check for default access */
542 EXEC SQL SELECT member_id INTO :acl_id FROM imembers
543 WHERE list_id = :acl_id AND member_type = 'USER'
544 AND member_id = :def_uid;
545 if (sqlca.sqlerrd[2] == 0)
554 if (get_client(cl, &client_type, &client_id) != MR_SUCCESS)
556 if (find_member("LIST", acl_id, client_type, client_id))
563 int get_client(cl, client_type, client_id)
568 if (cl->users_id > 0) {
569 *client_id = cl->users_id;
570 *client_type = "USER";
574 if (cl->client_id < 0) {
575 *client_id = -cl->users_id;
576 *client_type = "KERBEROS";
583 int find_member(list_type, list_id, member_type, member_id)
584 char *list_type, *member_type;
585 int list_id, member_id;
587 EXEC SQL BEGIN DECLARE SECTION;
589 EXEC SQL END DECLARE SECTION;
591 if (!strcmp(strtrim(list_type), strtrim(member_type)) &&
592 list_id == member_id)
595 /* see if client is a direct member of list */
597 EXEC SQL SELECT member_id INTO :flag FROM imembers
598 WHERE list_id = :list_id AND member_type = :member_type
599 AND member_id = :member_id;
600 if(flag!=0) flag=1; /** Not strictly necessary */
601 if (sqlca.sqlcode == 0)
607 int do_retrieve(q, pqual, psort, action, actarg)
609 char *pqual, *psort, *actarg;
612 build_sql_stmt(stmt_buf,"SELECT",q->tlist,NULL,pqual);
614 strcat(stmt_buf," ORDER BY ");
615 strcat(stmt_buf,psort);
618 return do_for_all_rows(stmt_buf, q->vcnt, action, (int)actarg);
621 char *sqlstrstr(str, pat)
624 register char *p=pat;
627 if(*str=='\'') { /* Skip over single-quote delimited substrings */
628 while(*++str && (*str!='\''))
635 while(*++p && (*++s==*p))
637 if(*p) p=pat; /* failed */
639 } while(*p && *++str);
645 void optimize_sql_stmt(buf)
648 char *point=buf, *pat, *eopat, *esc1, *esc2, *csr;
650 for(point=buf; point=sqlstrstr(point,"LIKE"); point++) {
651 /* Now pointing to string "LIKE" */
653 /* Look at next word */
654 for(pat=point+4; *pat==' '; pat++) ;
656 /* Is it a single-quote delimited string? */
657 if(*pat!='\'') continue;
659 /* look for "escape" clause - save escape character */
660 /* 1. Find end of pattern */
661 for(eopat=pat+1; 1; eopat++) {
663 if(eopat[1]=='\'') /* single-quote is self-escaping */
670 /* 2. Look at next word */
671 for(esc1=eopat; *++esc1==' ';) ;
673 /* 3. esc1=0 if not "ESCAPE '?'", where the ? may be any character. */
674 if(strncmp(esc1,"ESCAPE",6)) esc1=NULL;
677 for(esc2=esc1+6; *esc2==' '; esc2++) ;
679 if(*esc2++!='\'') continue; /* Bad SQL syntax. Skip. */
680 /* esc2 now points at the escape character itself */
681 if(esc2[1]!='\'') continue; /* Weird escape string. Skip. */
686 /* Is pattern free from special characters? */
687 for(csr=pat; csr<eopat; csr++)
688 if((*csr=='%') || (*csr=='_') || (*csr==*esc2)) break;
689 if(csr!=eopat) continue; /* Uses pattern matching. Skip. */
691 /* Optimize the query statement */
692 /* 1. Change "LIKE" to " = " */
693 memcpy(point," = ",4);
695 /* 2. Change "ESCAPE" to " " */
698 /* 3. Change "'*'" to " " */
699 /* (Changes '''' to " ") */
700 if(esc2) memset(esc2-1,' ',(*esc2=='\'')?4:3);
705 void build_sql_stmt(result_buf, cmd, targetlist, argv, qual)
706 char *result_buf, *cmd, *targetlist, *argv[], *qual;
708 char fmt_buf[MR_STMTBUF_LEN];
709 register char *res, *fmt;
712 sprintf(fmt_buf,"%s %s WHERE %s",cmd,targetlist,qual);
714 sprintf(fmt_buf,"%s %s",cmd,targetlist);
716 for(res=result_buf, fmt=fmt_buf; *fmt; fmt++) {
720 case '%': /* %% -> % */
727 if(*p=='\'') *res++='\''; /* double the ' */
734 res+=sprintf(res,"%d",*(int *)*argv++);
736 default: /* Swallow other %? pairs */
740 } else *res++ = *fmt; /* text -> result buffer */
744 optimize_sql_stmt(result_buf);
747 int do_update(q, argv, qual, action, actarg)
749 char *argv[], *qual, *actarg;
752 build_sql_stmt(stmt_buf,"UPDATE",q->tlist,argv,qual);
753 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
754 if (mr_errcode) return(mr_errcode);
758 int do_append(q, argv, pqual, action, actarg)
760 char *argv[], *pqual, *actarg;
763 build_sql_stmt(stmt_buf,"INSERT",q->tlist,argv,pqual);
764 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
765 if (mr_errcode) return(mr_errcode);
769 int do_delete(q, qual, action, actarg)
774 sprintf(stmt_buf,"DELETE FROM %s WHERE %s",table_name[q->rtable],qual);
775 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
776 if (mr_errcode) return(mr_errcode);
782 ** set_next_object_id - set next object id in values table
784 ** Inputs: object - object name in values table and in objects
785 ** table - name of table objects are found in
786 ** limit - should the ID be range limited
788 ** - called before an APPEND operation to set the next object id to
789 ** be used for the new record to the next free value
793 int set_next_object_id(object, table, limit)
794 EXEC SQL BEGIN DECLARE SECTION;
796 EXEC SQL END DECLARE SECTION;
800 EXEC SQL BEGIN DECLARE SECTION;
802 EXEC SQL END DECLARE SECTION;
805 EXEC SQL SELECT value INTO :value FROM numvalues WHERE name = :object;
806 if (sqlca.sqlerrd[2] != 1)
809 starting_value=value;
811 if (limit && value > MAX_ID_VALUE)
812 value = MIN_ID_VALUE;
814 sprintf(stmt_buf,"SELECT %s FROM %s WHERE %s=%d",
815 object,table_name[table],object,value);
817 if (sqlca.sqlcode < 0) return(mr_errcode);
818 if (sqlca.sqlcode == SQL_NO_MATCH) break;
821 if (limit && value == starting_value) {
822 com_err(whoami,0,"All id values have been used");
828 com_err(whoami, 0, "setting ID %s to %d", object, value);
829 EXEC SQL UPDATE numvalues SET value = :value WHERE name = :object;
834 /* Turn a kerberos name into the user's ID of the account that principal
835 * owns. Sets the kerberos ID and user ID.
838 int set_krb_mapping(name, login, ok, kid, uid)
842 EXEC SQL BEGIN DECLARE SECTION;
845 EXEC SQL END DECLARE SECTION;
851 EXEC SQL SELECT km.users_id, km.string_id INTO :u_id, :k_id
852 FROM krbmap km, strings str
853 WHERE km.string_id = str.string_id AND str.string = :krbname;
854 EXEC SQL COMMIT WORK;
856 if (dbms_errno) return(mr_errcode);
858 if (sqlca.sqlerrd[2] == 1) { /* rowcount */
864 if (name_to_id(name, STRINGS_TABLE, &k_id) == MR_SUCCESS)
872 if (name_to_id(login, USERS_TABLE, uid) != MR_SUCCESS)
877 if (dbms_errno) return(mr_errcode);
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++) {
896 maxv = MAX(maxv, Queries2[i].vcnt);
897 maxa = MAX(maxa, Queries2[i].argc);
899 if (MAX(maxv, maxa) > QMAXARGS) {
900 com_err(whoami, 0, "A query has more args than QMAXARGS");
906 /* Generically do a SELECT, storing the results in the provided buffers */
911 int i, errcode=0, errlen;
913 EXEC SQL PREPARE inc_stmt FROM :stmt_buf;
914 if(sqlca.sqlcode) return;
915 EXEC SQL DECLARE inc_crs CURSOR FOR inc_stmt;
916 EXEC SQL OPEN inc_crs;
917 mr_sqlda->N = QMAXARGS;
918 EXEC SQL DESCRIBE SELECT LIST FOR inc_stmt INTO mr_sqlda;
919 mr_sqlda->N = mr_sqlda->F;
920 for(i=0; i<mr_sqlda->N; i++) {
921 mr_sqlda->V[i]=buffers[i];
923 mr_sqlda->L[i]=ARGLEN;
925 EXEC SQL FETCH inc_crs USING DESCRIPTOR mr_sqlda;
927 /* if we got an error from the FETCH, we have to preserve it or the
928 close will reset it and the caller will think nothing happened */
930 errcode=sqlca.sqlcode;
931 errlen=sqlca.sqlerrm.sqlerrml;
934 EXEC SQL CLOSE inc_crs;
936 sqlca.sqlcode=errcode;
937 sqlca.sqlerrm.sqlerrml=errlen;
941 int do_for_all_rows(query, count, action, actarg)
942 EXEC SQL BEGIN DECLARE SECTION;
944 EXEC SQL END DECLARE SECTION;
945 int count, (*action)(), actarg;
949 EXEC SQL PREPARE stmt FROM :query;
950 if(sqlca.sqlcode) return;
951 EXEC SQL DECLARE curs CURSOR FOR stmt;
954 EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO mr_sqlda;
955 mr_sqlda->N = mr_sqlda->F;
956 for(i=0; i<mr_sqlda->N; i++) {
957 mr_sqlda->V[i]=sqlbuffer[i];
959 mr_sqlda->L[i]=ARGLEN;
963 EXEC SQL FETCH curs USING DESCRIPTOR mr_sqlda;
964 if(sqlca.sqlcode != 0) break;
965 (*action)(count, sqlbuffer, actarg);
970 if (mr_errcode) return(mr_errcode);
971 return ((rowcount == 0) ? MR_NO_MATCH : MR_SUCCESS);