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, sq);
229 if (status != MR_SUCCESS) {
233 status = (*v->post_rtn)(q, sq, v, action, actarg, cl);
235 /* normal retrieve */
236 status = do_retrieve(q, pqual, psort, action, actarg);
238 if (status != MR_SUCCESS) break;
240 status = (*v->post_rtn)(q, Argv, cl, action, actarg);
246 /* see if row already exists */
248 status = validate_row(q, Argv, v);
249 if (status != MR_EXISTS) break;
252 /* build "where" clause and perform update */
253 /* if q->rvar = NULL, perform post_rtn only */
255 build_qual(q->qual, q->argc, Argv, qual);
256 incremental_before(q->rtable, qual, argv_ro);
257 status = do_update(q, &Argv[q->argc], qual, action, actarg);
258 incremental_after(q->rtable, qual, argv_ro);
259 if (status != MR_SUCCESS) break;
260 flush_name(argv_ro[0], q->rtable);
261 table = table_name[q->rtable];
262 if (strcmp(q->shortname, "sshi") && strcmp(q->shortname, "ssif")) {
263 EXEC SQL UPDATE tblstats
264 SET updates = updates + 1, modtime = SYSDATE
265 WHERE table_name = :table;
269 /* execute followup routine (if any) */
270 if (v->post_rtn) status = (*v->post_rtn)(q, Argv, cl);
275 /* see if row already exists */
277 status = validate_row(q, Argv, v);
278 if (status != MR_NO_MATCH) break;
281 /* build "where" clause if needed */
283 build_qual(q->qual, q->argc, Argv, qual);
289 /* perform the append */
290 /* if q->rvar = NULL, perform post_rtn only */
292 incremental_clear_before();
293 status = do_append(q, &Argv[q->argc], pqual, action, actarg);
294 if (status != MR_SUCCESS) break;
295 if (v && v->object_id) {
296 sprintf(qual, "%s.%s = %s",q->rvar, v->object_id,
297 Argv[q->argc+q->vcnt]);
298 incremental_after(q->rtable, qual, argv_ro);
300 incremental_after(q->rtable, pqual, argv_ro);
302 table = table_name[q->rtable];
303 EXEC SQL UPDATE tblstats
304 SET appends = appends + 1, modtime = SYSDATE
305 WHERE table_name = :table;
308 /* execute followup routine */
309 if (v->post_rtn) status = (*v->post_rtn)(q, Argv, cl);
313 /* see if row already exists */
315 status = validate_row(q, Argv, v);
316 if (status != MR_EXISTS) break;
319 /* build "where" clause and perform delete */
320 /* if q->rvar = NULL, perform post_rtn only */
322 build_qual(q->qual, q->argc, Argv, qual);
323 table = table_name[q->rtable];
324 incremental_before(q->rtable, qual, argv_ro);
325 status = do_delete(q, qual, action, actarg);
326 incremental_clear_after();
327 if (status != MR_SUCCESS) break;
328 flush_name(argv_ro[0], q->rtable);
329 EXEC SQL UPDATE tblstats
330 SET deletes = deletes + 1, modtime = SYSDATE
331 WHERE table_name = :table;
334 /* execute followup routine */
335 if (v->post_rtn) status = (*v->post_rtn)(q, Argv, cl);
341 if (status == MR_SUCCESS && dbms_errno != 0) {
342 com_err(whoami, MR_INTERNAL, "Server didn't notice DBMS ERROR %d",
347 if (q->type == RETRIEVE) {
348 EXEC SQL COMMIT WORK;
350 if (status == MR_SUCCESS) {
351 EXEC SQL COMMIT WORK;
357 fprintf(journal, "%% %s %s %s",
358 cl->clname, cl->entity, ctime(&now));
359 fprintf(journal, "%s[%d] ", q->name, cl->args->mr_version_no);
360 for (i = 0; i < argc; i++) {
364 requote(buf, argv_ro[i], sizeof(buf));
370 incremental_update();
373 EXEC SQL ROLLBACK WORK;
377 cache_commit(); /* commit following abort is safe */
379 if (status != MR_SUCCESS && log_flags & LOG_RES)
380 com_err(whoami, status, " (Query failed)");
384 int build_qual(fmt_buf, argc, argv, qual)
385 char *fmt_buf, *argv[], *qual;
390 for(res=qual, fmt=fmt_buf; *fmt; fmt++) {
394 case '%': /* %% -> % */
401 if(*p=='\'') *res++='\''; /* double the ' */
408 res+=sprintf(res,"%d",*(int *)*argv++);
410 default: /* Swallow other %? pairs */
414 } else *res++ = *fmt; /* text -> result buffer */
419 char *build_sort(v, sort)
423 register struct valobj *vo;
432 if (vo->type == V_SORT) {
433 sprintf(elem, "%d", vo->index + 1); /* Result column number */
434 if (*sort) strcat(sort, ", ");
440 return ((*sort) ? sort : 0);
444 /* Build arguement vector, verify query and arguments */
448 int mr_verify_query(cl, q, argc, argv_ro)
456 register struct validate *v = q->validate;
458 register char *to,*fr,*stop;
462 /* copy the arguments into a local argv that we can modify */
463 if (argc >= QMAXARGS)
465 for (i = 0; i < argc; i++) {
466 for (to=Argv[i], fr=argv_ro[i], stop=to+ARGLEN; (*fr) && (to<stop);)
470 return(MR_ARG_TOO_LONG);
477 /* check initial query access */
478 status = check_query_access(q, Argv, cl);
479 if (status != MR_SUCCESS && status != MR_PERM)
481 if (status == MR_SUCCESS)
484 /* check argument count */
486 if (q->type == UPDATE || q->type == APPEND) argreq += q->vcnt;
487 if (argc != argreq) return(MR_ARGS);
489 /* validate arguments */
490 if (v && v->valobj) {
491 status = validate_fields(q, Argv, v->valobj, v->objcnt);
492 if (status != MR_SUCCESS) return(status);
495 /* perform special query access check */
496 if (!privileged && v && v->acs_rtn) {
497 status = (*v->acs_rtn)(q, Argv, cl);
498 if (status != MR_SUCCESS && status != MR_PERM)
500 if (status == MR_SUCCESS)
504 return(privileged ? MR_SUCCESS : MR_PERM);
508 /* This routine caches info from the database. Each query acl is stored
509 * in the query structure, and whether that acl contains everybody.
512 int check_query_access(q, argv, cl)
517 EXEC SQL BEGIN DECLARE SECTION;
521 EXEC SQL END DECLARE SECTION;
525 /* initialize default uid */
527 EXEC SQL SELECT users_id INTO :def_uid FROM users WHERE login='default';
530 /* get query access control list */
535 EXEC SQL SELECT list_id INTO :acl_id FROM capacls WHERE tag = :name;
536 if (sqlca.sqlcode < 0) return(MR_DBMS_ERR);
537 if (sqlca.sqlcode == SQL_NO_MATCH) return(MR_PERM);
540 /* check for default access */
541 EXEC SQL SELECT member_id INTO :acl_id FROM imembers
542 WHERE list_id = :acl_id AND member_type = 'USER'
543 AND member_id = :def_uid;
544 if (sqlca.sqlerrd[2] == 0)
553 if (get_client(cl, &client_type, &client_id) != MR_SUCCESS)
555 if (find_member("LIST", acl_id, client_type, client_id))
562 int get_client(cl, client_type, client_id)
567 if (cl->users_id > 0) {
568 *client_id = cl->users_id;
569 *client_type = "USER";
573 if (cl->client_id < 0) {
574 *client_id = -cl->users_id;
575 *client_type = "KERBEROS";
582 int find_member(list_type, list_id, member_type, member_id)
583 char *list_type, *member_type;
584 int list_id, member_id;
586 EXEC SQL BEGIN DECLARE SECTION;
588 EXEC SQL END DECLARE SECTION;
590 if (!strcmp(strtrim(list_type), strtrim(member_type)) &&
591 list_id == member_id)
594 /* see if client is a direct member of list */
596 EXEC SQL SELECT member_id INTO :flag FROM imembers
597 WHERE list_id = :list_id AND member_type = :member_type
598 AND member_id = :member_id;
599 if(flag!=0) flag=1; /** Not strictly necessary */
600 if (sqlca.sqlcode == 0)
606 int do_retrieve(q, pqual, psort, action, actarg)
608 char *pqual, *psort, *actarg;
611 build_sql_stmt(stmt_buf,"SELECT",q->tlist,NULL,pqual);
613 strcat(stmt_buf," ORDER BY ");
614 strcat(stmt_buf,psort);
617 return do_for_all_rows(stmt_buf, q->vcnt, action, actarg);
620 char *sqlstrstr(str, pat)
623 register char *p=pat;
626 if(*str=='\'') { /* Skip over single-quote delimited substrings */
627 while(*++str && (*str!='\''))
634 while(*++p && (*++s==*p))
636 if(*p) p=pat; /* failed */
638 } while(*p && *++str);
644 void optimize_sql_stmt(buf)
647 char *point=buf, *pat, *eopat, *esc1, *esc2, *csr;
649 for(point=buf; point=sqlstrstr(point,"LIKE"); point++) {
650 /* Now pointing to string "LIKE" */
652 /* Look at next word */
653 for(pat=point+4; *pat==' '; pat++) ;
655 /* Is it a single-quote delimited string? */
656 if(*pat!='\'') continue;
658 /* look for "escape" clause - save escape character */
659 /* 1. Find end of pattern */
660 for(eopat=pat+1; 1; eopat++) {
662 if(eopat[1]=='\'') /* single-quote is self-escaping */
669 /* 2. Look at next word */
670 for(esc1=eopat; *++esc1==' ';) ;
672 /* 3. esc1=0 if not "ESCAPE '?'", where the ? may be any character. */
673 if(strncmp(esc1,"ESCAPE",6)) esc1=NULL;
676 for(esc2=esc1+6; *esc2==' '; esc2++) ;
678 if(*esc2++!='\'') continue; /* Bad SQL syntax. Skip. */
679 /* esc2 now points at the escape character itself */
680 if(esc2[1]!='\'') continue; /* Weird escape string. Skip. */
685 /* Is pattern free from special characters? */
686 for(csr=pat; csr<eopat; csr++)
687 if((*csr=='%') || (*csr=='_') || (*csr==*esc2)) break;
688 if(csr!=eopat) continue; /* Uses pattern matching. Skip. */
690 /* Optimize the query statement */
691 /* 1. Change "LIKE" to " = " */
692 memcpy(point," = ",4);
694 /* 2. Change "ESCAPE" to " " */
697 /* 3. Change "'*'" to " " */
698 /* (Changes '''' to " ") */
699 if(esc2) memset(esc2-1,' ',(*esc2=='\'')?4:3);
704 void build_sql_stmt(result_buf, cmd, targetlist, argv, qual)
705 char *result_buf, *cmd, *targetlist, *argv[], *qual;
707 char fmt_buf[MR_STMTBUF_LEN];
708 register char *res, *fmt;
711 sprintf(fmt_buf,"%s %s WHERE %s",cmd,targetlist,qual);
713 sprintf(fmt_buf,"%s %s",cmd,targetlist);
715 for(res=result_buf, fmt=fmt_buf; *fmt; fmt++) {
719 case '%': /* %% -> % */
726 if(*p=='\'') *res++='\''; /* double the ' */
733 res+=sprintf(res,"%d",*(int *)*argv++);
735 default: /* Swallow other %? pairs */
739 } else *res++ = *fmt; /* text -> result buffer */
743 optimize_sql_stmt(result_buf);
746 int do_update(q, argv, qual, action, actarg)
748 char *argv[], *qual, *actarg;
751 build_sql_stmt(stmt_buf,"UPDATE",q->tlist,argv,qual);
752 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
753 if (mr_errcode) return(mr_errcode);
757 int do_append(q, argv, pqual, action, actarg)
759 char *argv[], *pqual, *actarg;
762 build_sql_stmt(stmt_buf,"INSERT",q->tlist,argv,pqual);
763 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
764 if (mr_errcode) return(mr_errcode);
768 int do_delete(q, qual, action, actarg)
773 sprintf(stmt_buf,"DELETE FROM %s WHERE %s",table_name[q->rtable],qual);
774 EXEC SQL EXECUTE IMMEDIATE :stmt_buf;
775 if (mr_errcode) return(mr_errcode);
781 ** set_next_object_id - set next object id in values table
783 ** Inputs: object - object name in values table and in objects
784 ** table - name of table objects are found in
785 ** limit - should the ID be range limited
787 ** - called before an APPEND operation to set the next object id to
788 ** be used for the new record to the next free value
792 int set_next_object_id(object, table, limit)
793 EXEC SQL BEGIN DECLARE SECTION;
795 EXEC SQL END DECLARE SECTION;
799 EXEC SQL BEGIN DECLARE SECTION;
801 EXEC SQL END DECLARE SECTION;
804 EXEC SQL SELECT value INTO :value FROM numvalues WHERE name = :object;
805 if (sqlca.sqlerrd[2] != 1)
808 starting_value=value;
810 if (limit && value > MAX_ID_VALUE)
811 value = MIN_ID_VALUE;
813 sprintf(stmt_buf,"SELECT %s FROM %s WHERE %s=%d",
814 object,table_name[table],object,value);
816 if (sqlca.sqlcode < 0) return(mr_errcode);
817 if (sqlca.sqlcode == SQL_NO_MATCH) break;
820 if (limit && value == starting_value) {
821 com_err(whoami,0,"All id values have been used");
827 com_err(whoami, 0, "setting ID %s to %d", object, value);
828 EXEC SQL UPDATE numvalues SET value = :value WHERE name = :object;
833 /* Turn a kerberos name into the user's ID of the account that principal
834 * owns. Sets the kerberos ID and user ID.
837 int set_krb_mapping(name, login, ok, kid, uid)
841 EXEC SQL BEGIN DECLARE SECTION;
844 EXEC SQL END DECLARE SECTION;
850 EXEC SQL SELECT km.users_id, km.string_id INTO :u_id, :k_id
851 FROM krbmap km, strings str
852 WHERE km.string_id = str.string_id AND str.string = :krbname;
853 EXEC SQL COMMIT WORK;
855 if (dbms_errno) return(mr_errcode);
857 if (sqlca.sqlerrd[2] == 1) { /* rowcount */
863 if (name_to_id(name, STRINGS_TABLE, &k_id) == MR_SUCCESS)
871 if (name_to_id(login, USERS_TABLE, uid) != MR_SUCCESS)
876 if (dbms_errno) return(mr_errcode);
881 /* For now this just checks the argc's. It should also see that there
882 * are no duplicate names.
885 void sanity_check_queries(void)
888 int maxv = 0, maxa = 0;
889 extern int QueryCount2;
890 extern struct query Queries2[];
892 #define MAX(x,y) ((x) > (y) ? (x) : (y))
894 for (i = 0; i < QueryCount2; i++) {
895 maxv = MAX(maxv, Queries2[i].vcnt);
896 maxa = MAX(maxa, Queries2[i].argc);
898 if (MAX(maxv, maxa) > QMAXARGS) {
899 com_err(whoami, 0, "A query has more args than QMAXARGS");
905 /* Generically do a SELECT, storing the results in the provided buffers */
910 int i, errcode=0, errlen;
912 EXEC SQL PREPARE inc_stmt FROM :stmt_buf;
913 if(sqlca.sqlcode) return;
914 EXEC SQL DECLARE inc_crs CURSOR FOR inc_stmt;
915 EXEC SQL OPEN inc_crs;
916 mr_sqlda->N = QMAXARGS;
917 EXEC SQL DESCRIBE SELECT LIST FOR inc_stmt INTO mr_sqlda;
918 mr_sqlda->N = mr_sqlda->F;
919 for(i=0; i<mr_sqlda->N; i++) {
920 mr_sqlda->V[i]=buffers[i];
922 mr_sqlda->L[i]=ARGLEN;
924 EXEC SQL FETCH inc_crs USING DESCRIPTOR mr_sqlda;
926 /* if we got an error from the FETCH, we have to preserve it or the
927 close will reset it and the caller will think nothing happened */
929 errcode=sqlca.sqlcode;
930 errlen=sqlca.sqlerrm.sqlerrml;
933 EXEC SQL CLOSE inc_crs;
935 sqlca.sqlcode=errcode;
936 sqlca.sqlerrm.sqlerrml=errlen;
940 int do_for_all_rows(query, count, action, actarg)
941 EXEC SQL BEGIN DECLARE SECTION;
943 EXEC SQL END DECLARE SECTION;
944 int count, (*action)(), actarg;
948 EXEC SQL PREPARE stmt FROM :query;
949 if(sqlca.sqlcode) return;
950 EXEC SQL DECLARE curs CURSOR FOR stmt;
953 EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO mr_sqlda;
954 mr_sqlda->N = mr_sqlda->F;
955 for(i=0; i<mr_sqlda->N; i++) {
956 mr_sqlda->V[i]=sqlbuffer[i];
958 mr_sqlda->L[i]=ARGLEN;
962 EXEC SQL FETCH curs USING DESCRIPTOR mr_sqlda;
963 if(sqlca.sqlcode != 0) break;
964 (*action)(count, sqlbuffer, actarg);
969 if (mr_errcode) return(mr_errcode);
970 return ((rowcount == 0) ? MR_NO_MATCH : MR_SUCCESS);