6 * Copyright (C) 1987 by the Massachusetts Institute of Technology
7 * For copying and distribution information, please see the file
13 static char *rcsid_qsupport_dc = "$Header$";
16 #include <mit-copyright.h>
17 #include "mr_server.h"
21 EXEC SQL INCLUDE sqlca;
24 extern char *whoami, *table_name[];
25 extern int dbms_errno, mr_errcode;
27 EXEC SQL BEGIN DECLARE SECTION;
28 extern char stmt_buf[];
29 EXEC SQL END DECLARE SECTION;
31 EXEC SQL WHENEVER SQLERROR DO dbmserr();
33 int get_ace_internal(char *atypex, int aid, int (*action)(), int actarg);
34 int gmol_internal(struct query *q, char *argv[], client *cl,
35 int (*action)(), int actarg, int flag);
36 int qualified_get(struct query *q, char *argv[], int (*action)(), int actarg,
37 char *start, char *range, char *field, char *flags[]);
40 /* Special query routines */
42 /* set_pobox - this does all of the real work.
43 * argv = user_id, type, box
44 * if type is POP, then box should be a machine, and its ID should be put in
45 * pop_id. If type is SMTP, then box should be a string and its ID should
46 * be put in box_id. If type is NONE, then box doesn't matter.
50 int set_pobox(q, argv, cl)
55 EXEC SQL BEGIN DECLARE SECTION;
58 EXEC SQL END DECLARE SECTION;
62 user = *(int *)argv[0];
64 EXEC SQL SELECT pop_id, potype INTO :id, :potype FROM users
65 WHERE users_id = :user;
66 if (dbms_errno) return(mr_errcode);
67 if (!strcmp(strtrim(potype), "POP"))
68 set_pop_usage(id, -1);
70 if (!strcmp(argv[1], "POP")) {
71 status = name_to_id(box, MACHINE_TABLE, &id);
72 if (status == MR_NO_MATCH)
76 EXEC SQL UPDATE users SET potype = 'POP', pop_id = :id
77 WHERE users_id = :user;
79 } else if (!strcmp(argv[1], "SMTP")) {
80 if (strchr(box, '/') || strchr(box, '|'))
82 status = name_to_id(box, STRINGS_TABLE, &id);
83 if (status == MR_NO_MATCH) {
87 EXEC SQL UPDATE users SET potype='SMTP', box_id = :id
88 WHERE users_id = :user;
89 } else /* argv[1] == "NONE" */ {
90 EXEC SQL UPDATE users SET potype='NONE'
91 WHERE users_id = :user;
94 set_pobox_modtime(q, argv, cl);
95 EXEC SQL UPDATE tblstats SET updates = updates+1, modtime=SYSDATE
96 WHERE table_name='users';
97 if (dbms_errno) return(mr_errcode);
102 /* get_list_info: passed a wildcard list name, returns lots of stuff about
103 * each list. This is tricky: first build a queue of all requested
104 * data. Rest of processing consists of fixing gid, ace_name, and modby.
107 int get_list_info(q, aargv, cl, action, actarg)
111 int (*action)(), actarg;
113 char *argv[13], *s, *d;
114 EXEC SQL BEGIN DECLARE SECTION;
115 char *name, acl_type[9], listname[33], active[5], public[5], hidden[5];
116 char maillist[5], grouplist[5], gid_str[6], desc[256];
117 char modtime[27], modwith[9];
118 int id, rowcount, acl_id, hid, modby_id;
119 EXEC SQL END DECLARE SECTION;
120 int returned, status;
121 struct save_queue *sq, *sq_create();
123 returned = rowcount = 0;
125 /* copy list name and fix wildcards */
126 name = malloc(2 * strlen(aargv[0]) + 1);
127 for (s = aargv[0], d = name; *s; s++, d++)
129 case '*': *d = '%'; break;
130 case '?': *d = '_'; break;
132 case '_': *d++ = '*'; /* fall through */
133 default: *d = *s; break;
138 EXEC SQL DECLARE csr102 CURSOR FOR SELECT list_id FROM list
139 WHERE name LIKE :name ESCAPE '*';
143 EXEC SQL OPEN csr102;
148 EXEC SQL FETCH csr102 INTO :id;
149 if(sqlca.sqlcode!=0) break;
150 sq_save_data(sq, (char *)id);
153 EXEC SQL CLOSE csr102;
155 if (dbms_errno) return(mr_errcode);
159 argv[0] = listname; argv[1] = active; argv[2] = public; argv[3] = hidden;
160 argv[4] = maillist; argv[5] = grouplist; argv[6] = gid_str;
161 argv[7] = acl_type; argv[9] = desc; argv[10] = modtime; argv[12] = modwith;
163 while (sq_get_data(sq, &id)) {
167 EXEC SQL SELECT name, active, publicflg,
168 hidden, hidden, maillist, grouplist, gid,
169 acl_type, acl_id, description,
170 TO_CHAR(modtime, 'DD-mon-YYYY HH24:MI:SS'), modby, modwith
171 INTO :listname, :active, :public, :hidden, :hid, :maillist,
172 :grouplist, :gid_str, :acl_type, :acl_id, :desc,
173 :modtime, :modby_id, :modwith
174 FROM list WHERE list_id = :id;
176 if (dbms_errno) return(mr_errcode);
179 if (atoi(gid_str) == -1)
180 argv[6] = UNIQUE_GID;
183 if (!strcmp(acl_type, "LIST")) {
184 status = id_to_name(acl_id, LIST_TABLE, &argv[8]);
185 } else if (!strcmp(acl_type, "USER")) {
186 status = id_to_name(acl_id, USERS_TABLE, &argv[8]);
187 } else if (!strcmp(acl_type, "KERBEROS")) {
188 status = id_to_name(acl_id, STRINGS_TABLE, &argv[8]);
189 } else if (!strcmp(acl_type, "NONE")) {
192 argv[8] = strsave("NONE");
196 argv[8] = strsave("???");
198 if (status && status != MR_NO_MATCH) return(status);
200 argv[11] = malloc(0);
202 status = id_to_name(modby_id, USERS_TABLE, &argv[11]);
204 status = id_to_name(-modby_id, STRINGS_TABLE, &argv[11]);
205 if (status && status != MR_NO_MATCH) return(status);
207 mr_trim_args(q->vcnt, argv);
209 (*action)(q->vcnt, argv, actarg);
215 if (dbms_errno) return(mr_errcode);
220 /* Add_member_to_list: do list flattening as we go! MAXLISTDEPTH is
221 * how many different ancestors a member is allowed to have.
224 #define MAXLISTDEPTH 1024
226 int add_member_to_list(q, argv, cl)
231 EXEC SQL BEGIN DECLARE SECTION;
232 int id, lid, mid, error, who, ref, rowcnt;
233 char *mtype, dtype[9], *entity;
234 EXEC SQL END DECLARE SECTION;
235 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
236 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
238 char *dtypes[MAXLISTDEPTH];
239 char *iargv[3], *buf;
241 lid = *(int *)argv[0];
243 mid = *(int *)argv[2];
244 /* if the member is already a direct member of the list, punt */
245 EXEC SQL SELECT COUNT(list_id) INTO :rowcnt FROM imembers
246 WHERE list_id = :lid AND member_id = :mid
247 AND member_type = :mtype AND direct = 1;
250 if (!strcasecmp(mtype, "STRING")) {
252 status = id_to_name(mid, STRINGS_TABLE, &buf);
253 if (status) return(status);
254 if (strchr(buf, '/') || strchr(buf, '|')) {
264 EXEC SQL DECLARE csr103 CURSOR FOR
265 SELECT list_id, ref_count FROM imembers
266 WHERE member_id = :lid AND member_type='LIST';
269 EXEC SQL OPEN csr103;
273 EXEC SQL FETCH csr103 INTO :id, :ref;
274 if(sqlca.sqlcode != 0) break;
276 ancestors[acount++] = id;
277 if (acount >= MAXLISTDEPTH) break;
279 EXEC SQL CLOSE csr103;
280 if (dbms_errno) return(mr_errcode);
281 if (acount >= MAXLISTDEPTH) {
284 descendants[0] = mid;
289 if (!strcmp(mtype, "LIST")) {
290 EXEC SQL DECLARE csr104 CURSOR FOR
291 SELECT member_id, member_type, ref_count
293 WHERE list_id = :mid;
296 EXEC SQL OPEN csr104;
300 EXEC SQL FETCH csr104 INTO :id, :dtype, :ref;
301 if(sqlca.sqlcode != 0) break;
304 dtypes[dcount] = "LIST";
307 dtypes[dcount] = "USER";
310 dtypes[dcount] = "STRING";
313 dtypes[dcount] = "KERBEROS";
320 descendants[dcount++] = id;
321 if (dcount >= MAXLISTDEPTH) {
326 EXEC SQL CLOSE csr104;
327 if (dbms_errno) return(mr_errcode);
331 for (a = 0; a < acount; a++) {
333 for (d = 0; d < dcount; d++) {
334 mid = descendants[d];
336 if (mid == lid && !strcmp(mtype, "LIST")) {
339 EXEC SQL SELECT COUNT(ref_count) INTO :rowcnt
341 WHERE list_id = :lid AND member_id = :mid
342 AND member_type = :mtype;
343 ref = aref[a] * dref[d];
345 if (a == 0 && d == 0) {
346 EXEC SQL UPDATE imembers
347 SET ref_count = ref_count+:ref, direct=1
348 WHERE list_id = :lid AND member_id = :mid
349 AND member_type = :mtype;
351 EXEC SQL UPDATE imembers
352 SET ref_count = ref_count+:ref
353 WHERE list_id = :lid AND member_id = :mid
354 AND member_type = :mtype;
357 incremental_clear_before();
358 if (a == 0 && d == 0) {
359 EXEC SQL INSERT INTO imembers
360 (list_id, member_id, direct, member_type, ref_count)
361 VALUES (:lid, :mid, 1, :mtype, 1);
363 EXEC SQL INSERT INTO imembers
364 (list_id, member_id, direct, member_type, ref_count)
365 VALUES (:lid, :mid, 0, :mtype, 1);
367 iargv[0] = (char *)lid;
369 iargv[2] = (char *)mid;
370 incremental_after(IMEMBERS_TABLE, 0, iargv);
374 lid = *(int *)argv[0];
378 SET modtime=SYSDATE, modby = :who, modwith = :entity
379 WHERE list_id = :lid;
380 if (dbms_errno) return(mr_errcode);
385 /* Delete_member_from_list: do list flattening as we go!
388 int delete_member_from_list(q, argv, cl)
393 EXEC SQL BEGIN DECLARE SECTION;
394 int id, lid, mid, cnt, error, who, ref;
395 char *mtype, dtype[9], *entity;
396 EXEC SQL END DECLARE SECTION;
397 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
398 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
399 char *dtypes[MAXLISTDEPTH];
402 lid = *(int *)argv[0];
404 mid = *(int *)argv[2];
405 /* if the member is not a direct member of the list, punt */
406 EXEC SQL SELECT COUNT(list_id) INTO :cnt FROM imembers
407 WHERE list_id = :lid AND member_id = :mid
408 AND member_type = :mtype AND direct = 1;
409 if (dbms_errno) return(mr_errcode);
415 EXEC SQL DECLARE csr105 CURSOR FOR
416 SELECT list_id, ref_count FROM imembers
417 WHERE member_id = :lid AND member_type = 'LIST';
420 EXEC SQL OPEN csr105;
424 EXEC SQL FETCH csr105 INTO :id, :ref;
425 if(sqlca.sqlcode!=0) break;
427 ancestors[acount++] = id;
428 if (acount >= MAXLISTDEPTH) break;
430 EXEC SQL CLOSE csr105;
433 if (acount >= MAXLISTDEPTH)
435 descendants[0] = mid;
440 if (!strcmp(mtype, "LIST")) {
441 EXEC SQL DECLARE csr106 CURSOR FOR
442 SELECT member_id, member_type, ref_count FROM imembers
443 WHERE list_id = :mid;
446 EXEC SQL OPEN csr106;
450 EXEC SQL FETCH csr106 INTO :id, :dtype, :ref;
451 if(sqlca.sqlcode!=0) break;
454 dtypes[dcount] = "LIST";
457 dtypes[dcount] = "USER";
460 dtypes[dcount] = "STRING";
463 dtypes[dcount] = "KERBEROS";
470 descendants[dcount++] = id;
471 if (dcount >= MAXLISTDEPTH) break;
473 EXEC SQL CLOSE csr106;
479 for (a = 0; a < acount; a++) {
481 for (d = 0; d < dcount; d++) {
482 mid = descendants[d];
484 if (mid == lid && !strcmp(mtype, "LIST")) {
487 EXEC SQL SELECT ref_count INTO :cnt FROM imembers
488 WHERE list_id = :lid AND member_id = :mid AND member_type = :mtype;
489 ref = aref[a] * dref[d];
491 iargv[0] = (char *)lid;
493 iargv[2] = (char *)mid;
494 incremental_before(IMEMBERS_TABLE, 0, iargv);
495 EXEC SQL DELETE FROM imembers
496 WHERE list_id = :lid AND member_id = :mid
497 AND member_type= :mtype;
498 incremental_clear_after();
499 } else if (a == 0 && d == 0) {
500 EXEC SQL UPDATE imembers
501 SET ref_count = ref_count - :ref, direct = 0
502 WHERE list_id = :lid AND member_id = :mid
503 AND member_type = :mtype;
505 EXEC SQL UPDATE imembers
506 SET ref_count = ref_count - :ref
507 WHERE list_id = :lid AND member_id = :mid
508 AND member_type = :mtype;
512 lid = *(int *)argv[0];
515 EXEC SQL UPDATE list SET modtime = SYSDATE, modby = :who, modwith = :entity
516 WHERE list_id = :lid;
517 if (dbms_errno) return(mr_errcode);
522 /* get_ace_use - given a type and a name, return a type and a name.
523 * The ace_type is one of "LIST", "USER", "RLIST", or "RUSER" in argv[0],
524 * and argv[1] will contain the ID of the entity in question. The R*
525 * types mean to recursively look at every containing list, not just
526 * when the object in question is a direct member. On return, the
527 * usage type will be one of LIST, SERVICE, FILESYS, QUOTA, QUERY, or ZEPHYR.
530 int get_ace_use(q, argv, cl, action, actarg)
534 int (*action)(), actarg;
537 EXEC SQL BEGIN DECLARE SECTION;
540 EXEC SQL END DECLARE SECTION;
541 struct save_queue *sq, *sq_create();
544 aid = *(int *)argv[1];
545 if (!strcmp(atype, "LIST") || !strcmp(atype, "USER") ||
546 !strcmp(atype, "KERBEROS")) {
547 return(get_ace_internal(atype, aid, action, actarg));
551 if (!strcmp(atype, "RLIST")) {
552 sq_save_data(sq, (char *)aid);
553 /* get all the list_id's of containing lists */
554 EXEC SQL DECLARE csr107 CURSOR FOR
555 SELECT list_id FROM imembers
556 WHERE member_type='LIST' AND member_id = :aid;
559 EXEC SQL OPEN csr107;
563 EXEC SQL FETCH csr107 INTO :listid;
564 if(sqlca.sqlcode != 0) break;
565 sq_save_unique_data(sq, (char *)listid);
567 EXEC SQL CLOSE csr107;
568 /* now process each one */
569 while (sq_get_data(sq, &id)) {
570 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
575 if (!strcmp(atype, "RUSER")) {
576 EXEC SQL DECLARE csr108 CURSOR FOR
577 SELECT list_id FROM imembers
578 WHERE member_type='USER' AND member_id = :aid;
581 EXEC SQL OPEN csr108;
585 EXEC SQL FETCH csr108 INTO :listid;
586 if(sqlca.sqlcode != 0) break;
587 sq_save_data(sq, (char *)listid);
589 EXEC SQL CLOSE csr108;
590 /* now process each one */
591 while (sq_get_data(sq, &id)) {
592 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
595 if (get_ace_internal("USER", aid, action, actarg) == MR_SUCCESS)
599 if (!strcmp(atype, "RKERBEROS")) {
600 EXEC SQL DECLARE csr109 CURSOR FOR
601 SELECT list_id FROM imembers
602 WHERE member_type='KERBEROS' AND member_id = :aid;
605 EXEC SQL OPEN csr109;
609 EXEC SQL FETCH csr109 INTO :listid;
610 if(sqlca.sqlcode != 0) break;
611 sq_save_data(sq, (char*)listid);
613 EXEC SQL CLOSE csr109;
614 /* now process each one */
615 while (sq_get_data(sq, &id)) {
616 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
619 if (get_ace_internal("KERBEROS", aid, action, actarg) == MR_SUCCESS)
624 if (dbms_errno) return(mr_errcode);
625 if (!found) return(MR_NO_MATCH);
630 /* This looks up a single list or user for ace use. atype must be "USER"
631 * or "LIST", and aid is the ID of the corresponding object. This is used
632 * by get_ace_use above.
635 int get_ace_internal(atype, aid, action, actarg)
636 EXEC SQL BEGIN DECLARE SECTION;
638 EXEC SQL END DECLARE SECTION;
639 int aid, (*action)(), actarg;
643 EXEC SQL BEGIN DECLARE SECTION;
645 EXEC SQL END DECLARE SECTION;
648 if (!strcmp(atype, "LIST")) {
649 rargv[0] = "FILESYS";
650 EXEC SQL DECLARE csr110 CURSOR FOR
651 SELECT label FROM filesys
655 EXEC SQL OPEN csr110;
659 EXEC SQL FETCH csr110 INTO :name;
660 if(sqlca.sqlcode != 0) break;
661 (*action)(2, rargv, actarg);
664 EXEC SQL CLOSE csr110;
667 EXEC SQL DECLARE csr111 CURSOR FOR
668 SELECT capability FROM capacls
669 WHERE list_id = :aid ;
672 EXEC SQL OPEN csr111;
676 EXEC SQL FETCH csr111 INTO :name ;
677 if(sqlca.sqlcode != 0) break;
678 (*action)(2, rargv, actarg);
681 EXEC SQL CLOSE csr111;
682 } else if (!strcmp(atype, "USER")) {
683 rargv[0] = "FILESYS";
684 EXEC SQL DECLARE csr112 CURSOR FOR
685 SELECT label FROM filesys
689 EXEC SQL OPEN csr112;
693 EXEC SQL FETCH csr112 INTO :name ;
694 if(sqlca.sqlcode != 0) break;
695 (*action)(2, rargv, actarg);
698 EXEC SQL CLOSE csr112;
702 EXEC SQL DECLARE csr113 CURSOR FOR
703 SELECT name FROM list
704 WHERE acl_type = :atype AND acl_id = :aid;
707 EXEC SQL OPEN csr113;
711 EXEC SQL FETCH csr113 INTO :name;
712 if(sqlca.sqlcode != 0) break;
713 (*action)(2, rargv, actarg);
716 EXEC SQL CLOSE csr113;
718 rargv[0] = "SERVICE";
719 EXEC SQL DECLARE csr114 CURSOR FOR
720 SELECT name FROM servers
721 WHERE acl_type = :atype AND acl_id = :aid;
724 EXEC SQL OPEN csr114;
728 EXEC SQL FETCH csr114 INTO :name;
729 if(sqlca.sqlcode != 0) break;
730 (*action)(2, rargv, actarg);
733 EXEC SQL CLOSE csr114;
735 rargv[0] = "HOSTACCESS";
736 EXEC SQL DECLARE csr115 CURSOR FOR
737 SELECT name FROM machine m, hostaccess ha
738 WHERE m.mach_id = ha.mach_id AND ha.acl_type = :atype
739 AND ha.acl_id = :aid;
742 EXEC SQL OPEN csr115;
746 EXEC SQL FETCH csr115 INTO :name;
747 if(sqlca.sqlcode != 0) break;
748 (*action)(2, rargv, actarg);
751 EXEC SQL CLOSE csr115;
754 EXEC SQL DECLARE csr116 CURSOR FOR
755 SELECT class FROM zephyr z
756 WHERE z.xmt_type = :atype AND z.xmt_id = :aid
757 OR z.sub_type = :atype AND z.sub_id = :aid
758 OR z.iws_type = :atype AND z.iws_id = :aid
759 OR z.iui_type = :atype AND z.iui_id = :aid;
762 EXEC SQL OPEN csr116;
766 EXEC SQL FETCH csr116 INTO :name;
767 if(sqlca.sqlcode != 0) break;
768 (*action)(2, rargv, actarg);
771 EXEC SQL CLOSE csr116;
773 if (!found) return(MR_NO_MATCH);
778 /* get_lists_of_member - given a type and a name, return the name and flags
779 * of all of the lists of the given member. The member_type is one of
780 * "LIST", "USER", "STRING", "RLIST", "RUSER", or "RSTRING" in argv[0],
781 * and argv[1] will contain the ID of the entity in question. The R*
782 * types mean to recursively look at every containing list, not just
783 * when the object in question is a direct member.
786 int get_lists_of_member(q, argv, cl, action, actarg)
790 int (*action)(), actarg;
792 int found = 0, direct = 1;
794 EXEC SQL BEGIN DECLARE SECTION;
797 char name[33], active[5], public[5], hidden[5], maillist[5], grouplist[5];
798 EXEC SQL END DECLARE SECTION;
801 aid = *(int *)argv[1];
802 if (!strcmp(atype, "RLIST")) {
806 if (!strcmp(atype, "RUSER")) {
810 if (!strcmp(atype, "RSTRING")) {
814 if (!strcmp(atype, "RKERBEROS")) {
824 rargv[5] = grouplist;
826 EXEC SQL DECLARE csr117a CURSOR FOR
827 SELECT l.name, l.active, l.publicflg, l.hidden,
828 l.maillist, l.grouplist
829 FROM list l, imembers im
830 WHERE l.list_id = im.list_id AND im.direct = 1
831 AND im.member_type = :atype AND im.member_id = :aid;
834 EXEC SQL OPEN csr117a;
838 EXEC SQL FETCH csr117a
839 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
840 if(sqlca.sqlcode != 0) break;
841 (*action)(6, rargv, actarg);
844 EXEC SQL CLOSE csr117a;
846 EXEC SQL DECLARE csr117b CURSOR FOR
847 SELECT l.name, l.active, l.publicflg, l.hidden,
848 l.maillist, l.grouplist
849 FROM list l, imembers im
850 WHERE l.list_id = im.list_id
851 AND im.member_type = :atype AND im.member_id = :aid;
854 EXEC SQL OPEN csr117b;
858 EXEC SQL FETCH csr117b
859 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
860 if(sqlca.sqlcode != 0) break;
861 (*action)(6, rargv, actarg);
864 EXEC SQL CLOSE csr117b;
867 if (dbms_errno) return(mr_errcode);
868 if (!found) return(MR_NO_MATCH);
873 /* qualified_get_lists: passed "TRUE", "FALSE", or "DONTCARE" for each of
874 * the five flags associated with each list. It will return the name of
875 * each list that meets the quailifications. It does this by building a
876 * where clause based on the arguments, then doing a retrieve.
879 static char *lflags[5] = { "active", "publicflg", "hidden", "maillist", "grouplist" };
881 int qualified_get_lists(q, argv, cl, action, actarg)
885 int (*action)(), actarg;
887 return(qualified_get(q, argv, action, actarg, "l.list_id != 0",
888 "l", "name", lflags));
892 /* get_members_of_list - this gets only direct members */
894 int get_members_of_list(q, argv, cl, action, actarg)
898 int (*action)(), actarg;
900 return(gmol_internal(q, argv, cl, action, actarg, 1));
903 /* get_end_members_of_list - this gets direct or indirect members */
905 int get_end_members_of_list(q, argv, cl, action, actarg)
909 int (*action)(), actarg;
911 return(gmol_internal(q, argv, cl, action, actarg, 0));
914 /** gmol_internal - optimized query for retrieval of list members
915 ** used by both get_members_of_list and get_end_members_of_list
921 ** - retrieve USER members, then LIST members, then STRING members
924 int gmol_internal(q, argv, cl, action, actarg, flag)
928 int (*action)(), actarg, flag;
930 EXEC SQL BEGIN DECLARE SECTION;
931 int list_id, member_id, direct;
932 char member_name[129], member_type[9];
933 EXEC SQL END DECLARE SECTION;
937 /* true/false flag indicates whether to display only direct members. */
943 list_id = *(int *)argv[0];
945 targv[1] = member_name;
947 EXEC SQL DECLARE csr119 CURSOR FOR
948 SELECT u.login FROM users u, imembers im
949 WHERE im.list_id = :list_id AND im.member_type = 'USER'
950 AND im.member_id = u.users_id AND im.direct > :direct
954 EXEC SQL OPEN csr119;
958 EXEC SQL FETCH csr119 INTO :member_name;
959 if(sqlca.sqlcode != 0) break;
960 (*action)(2, targv, actarg);
962 EXEC SQL CLOSE csr119;
963 if (dbms_errno) return(mr_errcode);
966 EXEC SQL DECLARE csr120 CURSOR FOR
967 SELECT l.name FROM list l, imembers im
968 WHERE im.list_id = :list_id AND im.member_type='LIST'
969 AND im.member_id = l.list_id AND im.direct > :direct
973 EXEC SQL OPEN csr120;
977 EXEC SQL FETCH csr120 INTO :member_name;
978 if(sqlca.sqlcode != 0) break;
979 (*action)(2, targv, actarg);
981 EXEC SQL CLOSE csr120;
982 if (dbms_errno) return(mr_errcode);
985 EXEC SQL DECLARE csr121 CURSOR FOR
986 SELECT str.string FROM strings str, imembers im
987 WHERE im.list_id = :list_id AND im.member_type='STRING'
988 AND im.member_id = str.string_id AND im.direct > :direct
992 EXEC SQL OPEN csr121;
996 EXEC SQL FETCH csr121 INTO :member_name;
997 if(sqlca.sqlcode != 0) break;
998 (*action)(2, targv, actarg);
1000 EXEC SQL CLOSE csr121;
1001 if (dbms_errno) return(mr_errcode);
1003 targv[0] = "KERBEROS";
1004 EXEC SQL DECLARE csr122 CURSOR FOR
1005 SELECT str.string FROM strings str, imembers im
1006 WHERE im.list_id = :list_id AND im.member_type='KERBEROS'
1007 AND im.member_id = str.string_id
1008 AND im.direct > :direct
1012 EXEC SQL OPEN csr122;
1016 EXEC SQL FETCH csr122 INTO :member_name;
1017 if(sqlca.sqlcode != 0) break;
1018 (*action)(2, targv, actarg);
1020 EXEC SQL CLOSE csr122;
1021 if (dbms_errno) return(mr_errcode);
1027 /* count_members_of_list: this is a simple query, but it cannot be done
1028 * through the dispatch table.
1031 int count_members_of_list(q, argv, cl, action, actarg)
1035 int (*action)(), actarg;
1037 EXEC SQL BEGIN DECLARE SECTION;
1039 EXEC SQL END DECLARE SECTION;
1040 char *rargv[1], countbuf[5];
1042 list = *(int *)argv[0];
1043 rargv[0] = countbuf;
1044 EXEC SQL SELECT count (*) INTO :ct FROM imembers
1045 WHERE list_id = :list AND direct=1;
1046 if (dbms_errno) return(mr_errcode);
1047 sprintf(countbuf, "%d", ct);
1048 (*action)(1, rargv, actarg);
1053 /* qualified_get_server: passed "TRUE", "FALSE", or "DONTCARE" for each of
1054 * the three flags associated with each service. It will return the name of
1055 * each service that meets the quailifications. It does this by building a
1056 * where clause based on the arguments, then doing a retrieve.
1059 static char *sflags[3] = { "enable", "inprogress", "harderror" };
1061 int qualified_get_server(q, argv, cl, action, actarg)
1065 int (*action)(), actarg;
1067 return(qualified_get(q, argv, action, actarg, "s.name is not null",
1068 "s", "name", sflags));
1069 /* of course, name will never be null, but we need something there
1070 to make qualified_get happy */
1074 /* generic qualified get routine, used by qualified_get_lists,
1075 * qualified_get_server, and qualified_get_serverhost.
1077 * start - a simple where clause, must not be empty
1078 * range - the name of the range variable
1079 * field - the field to return
1080 * flags - an array of strings, names of the flag variables
1083 int qualified_get(q, argv, action, actarg, start, range, field, flags)
1085 char *argv[], *start, *range, *field, *flags[];
1086 int (*action)(), actarg;
1092 strcpy(qual, start);
1093 for (i = 0; i < q->argc; i++) {
1094 if (!strcmp(argv[i], "TRUE")) {
1095 sprintf(buf, " AND %s.%s != 0", range, flags[i]);
1096 (void) strcat(qual, buf);
1097 } else if (!strcmp(argv[i], "FALSE")) {
1098 sprintf(buf, " AND %s.%s = 0", range, flags[i]);
1099 (void) strcat(qual, buf);
1103 sprintf(stmt_buf,"SELECT %s.%s FROM %s %s WHERE %s",range,field,
1104 table_name[q->rtable],range,qual);
1105 return do_for_all_rows(stmt_buf, 1, action, actarg);
1109 /* qualified_get_serverhost: passed "TRUE", "FALSE", or "DONTCARE" for each of
1110 * the five flags associated with each serverhost. It will return the name of
1111 * each service and host that meets the quailifications. It does this by
1112 * building a where clause based on the arguments, then doing a retrieve.
1115 static char *shflags[6] = { "service", "enable", "override", "success",
1116 "inprogress", "hosterror" };
1118 int qualified_get_serverhost(q, argv, cl, action, actarg)
1122 int (*action)(), actarg;
1124 char qual[256], buf[32];
1127 sprintf(qual, "m.mach_id = sh.mach_id AND sh.service = UPPER('%s')",
1129 for (i = 1; i < q->argc; i++) {
1130 if (!strcmp(argv[i], "TRUE")) {
1131 sprintf(buf, " AND sh.%s != 0", shflags[i]);
1133 } else if (!strcmp(argv[i], "FALSE")) {
1134 sprintf(buf, " AND sh.%s = 0", shflags[i]);
1139 sprintf(stmt_buf, "SELECT sh.service, m.name FROM serverhosts sh, "
1140 "machine m WHERE %s", qual);
1141 return do_for_all_rows(stmt_buf, 2, action, actarg);
1145 /* register_user - change user's login name and allocate a pobox, group,
1146 * filesystem, and quota for them. The user's status must start out as 0,
1147 * and is left as 2. Arguments are: user's UID, new login name, and user's
1148 * type for filesystem allocation (MR_FS_STUDENT, MR_FS_FACULTY,
1149 * MR_FS_STAFF, MR_FS_MISC).
1152 int register_user(q, argv, cl)
1157 EXEC SQL BEGIN DECLARE SECTION;
1158 char *login, *entity, directory[129], machname[33];
1159 int who, rowcount, mid, uid, users_id, utype, list_id;
1160 int ostatus, nstatus, gidval, fsidval;
1161 static int m_id = 0, def_quota = 0;
1162 EXEC SQL END DECLARE SECTION;
1163 char buffer[256], *aargv[3];
1165 entity = cl->entity;
1166 who = cl->client_id;
1168 uid = atoi(argv[0]);
1170 utype = atoi(argv[2]);
1173 EXEC SQL SELECT users_id, status INTO :users_id, :ostatus
1175 WHERE unix_uid = :uid AND (status=0 OR status=5 OR status=6);
1177 if (sqlca.sqlerrd[2] == 0)
1178 return(MR_NO_MATCH);
1179 if (sqlca.sqlerrd[2] > 1)
1180 return(MR_NOT_UNIQUE);
1182 /* check new login name */
1183 EXEC SQL SELECT COUNT(login) INTO :rowcount FROM users
1184 WHERE login = :login AND users_id != :users_id;
1185 if (dbms_errno) return(mr_errcode);
1186 if (rowcount > 0) return(MR_IN_USE);
1187 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM list
1188 WHERE name = :login;
1189 if (dbms_errno) return(mr_errcode);
1190 if (rowcount > 0) return(MR_IN_USE);
1191 EXEC SQL SELECT COUNT(label) INTO :rowcount FROM filesys
1192 WHERE label = :login;
1193 if (dbms_errno) return(mr_errcode);
1194 if (rowcount > 0) return(MR_IN_USE);
1195 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM alias
1196 WHERE name = :login AND type='FILESYS';
1197 if (dbms_errno) return(mr_errcode);
1198 if (rowcount > 0) return(MR_IN_USE);
1199 com_err(whoami, 0, "login name OK");
1201 /* choose place for pobox, put in mid */
1202 EXEC SQL DECLARE csr130 CURSOR FOR
1203 SELECT sh.mach_id, m.name FROM serverhosts sh, machine m
1204 WHERE sh.service='POP' AND sh.mach_id=m.mach_id
1205 AND sh.value2 - sh.value1 =
1206 (SELECT MAX(value2 - value1) FROM serverhosts
1207 WHERE service = 'POP');
1210 EXEC SQL OPEN csr130;
1213 EXEC SQL FETCH csr130 INTO :mid, :machname;
1214 if (sqlca.sqlerrd[2] == 0) {
1215 EXEC SQL CLOSE csr130;
1216 if (dbms_errno) return(mr_errcode);
1217 return(MR_NO_POBOX);
1219 EXEC SQL CLOSE csr130;
1220 if (dbms_errno) return(mr_errcode);
1223 /* change login name, set pobox */
1224 sprintf(buffer, "u.users_id = %d", users_id);
1225 incremental_before(USERS_TABLE, buffer, 0);
1227 if (ostatus == 5 || ostatus == 6)
1229 EXEC SQL UPDATE users SET login = :login, status = :nstatus,
1230 modtime=SYSDATE, modby = :who, modwith = :entity, potype='POP',
1231 pop_id = :mid, pmodtime=SYSDATE, pmodby = :who, pmodwith = :entity
1232 WHERE users_id = :users_id;
1234 if (dbms_errno) return(mr_errcode);
1235 if (sqlca.sqlerrd[2] != 1)
1236 return(MR_INTERNAL);
1237 set_pop_usage(mid, 1);
1238 com_err(whoami, 0, "set login name to %s and pobox to %s", login,
1240 incremental_after(USERS_TABLE, buffer, 0);
1243 /* Cell Name (I know, it shouldn't be hard coded...) */
1244 strcpy(machname, "ATHENA.MIT.EDU");
1245 EXEC SQL SELECT mach_id INTO :m_id FROM machine
1246 WHERE name = :machname;
1249 EXEC SQL SELECT list_id INTO :list_id FROM list
1252 /* create filesystem */
1253 if (set_next_object_id("filsys_id", FILESYS_TABLE, 0))
1255 incremental_clear_before();
1256 if (islower(login[0]) && islower(login[1])) {
1257 sprintf(directory, "/afs/athena.mit.edu/user/%c/%c/%s",
1258 login[0], login[1], login);
1260 sprintf(directory, "/afs/athena.mit.edu/user/other/%s", login);
1263 EXEC SQL SELECT value INTO :fsidval FROM numvalues
1264 WHERE numvalues.name='filsys_id';
1265 EXEC SQL INSERT INTO filesys
1266 (filsys_id, phys_id, label, type, mach_id, name,
1267 mount, rwaccess, comments, owner, owners, createflg,
1268 lockertype, modtime, modby, modwith)
1270 (:fsidval, 0, :login, 'AFS', :m_id, :directory,
1271 '/mit/' || :login, 'w', 'User Locker', :users_id, :list_id, 1,
1272 'HOMEDIR', SYSDATE, :who, :entity);
1274 if (dbms_errno) return(mr_errcode);
1275 if (sqlca.sqlerrd[2] != 1)
1276 return(MR_INTERNAL);
1277 sprintf(buffer,"fs.filsys_id = %d",fsidval);
1278 incremental_after(FILESYS_TABLE, buffer, 0);
1281 if (def_quota == 0) {
1282 EXEC SQL SELECT value INTO :def_quota FROM numvalues
1283 WHERE name='def_quota';
1284 if (dbms_errno) return(mr_errcode);
1285 if (sqlca.sqlerrd[2] != 1)
1286 return(MR_NO_QUOTA);
1289 incremental_clear_before();
1290 EXEC SQL INSERT INTO quota
1291 (entity_id, filsys_id, type, quota, phys_id, modtime, modby, modwith)
1293 (0, :fsidval, 'ANY', :def_quota, 0, SYSDATE, :who, :entity);
1294 if (dbms_errno) return(mr_errcode);
1295 if (sqlca.sqlerrd[2] != 1)
1296 return(MR_INTERNAL);
1300 sprintf(buffer, "q.entity_id = 0 and q.filsys_id = %d and q.type = 'ANY'", fsidval);
1301 incremental_after(QUOTA_TABLE, buffer, aargv);
1302 com_err(whoami, 0, "quota of %d assigned", def_quota);
1303 if (dbms_errno) return(mr_errcode);
1305 cache_entry(login, USERS_TABLE, users_id);
1307 EXEC SQL UPDATE tblstats SET updates=updates+1, modtime=SYSDATE
1308 WHERE table_name='users';
1309 EXEC SQL UPDATE tblstats SET appends=appends+1, modtime=SYSDATE
1310 WHERE table_name='filesys' OR table_name='quota';
1311 if (dbms_errno) return(mr_errcode);
1317 /** set_pop_usage - incr/decr usage count for pop server in serverhosts talbe
1321 ** delta (will be +/- 1)
1324 ** - incr/decr value field in serverhosts table for pop/mach_id
1328 int set_pop_usage(id, cnt)
1329 EXEC SQL BEGIN DECLARE SECTION;
1331 EXEC SQL END DECLARE SECTION;
1334 EXEC SQL UPDATE serverhosts SET value1 = value1 + :cnt
1335 WHERE serverhosts.service = 'POP' AND serverhosts.mach_id = :id;
1337 if (dbms_errno) return(mr_errcode);