3 * Special query routines
5 * Copyright (C) 1987-1998 by the Massachusetts Institute of Technology
6 * For copying and distribution information, please see the file
10 #include <mit-copyright.h>
11 #include "mr_server.h"
19 EXEC SQL INCLUDE sqlca;
23 extern char *whoami, *table_name[];
24 extern int dbms_errno, mr_errcode;
26 EXEC SQL BEGIN DECLARE SECTION;
27 extern char stmt_buf[];
28 EXEC SQL END DECLARE SECTION;
30 EXEC SQL WHENEVER SQLERROR DO dbmserr();
32 int get_ace_internal(char *atypex, int aid,
33 int (*action)(int, char *[], void *), void *actarg);
34 int qualified_get(struct query *q, char *argv[],
35 int (*action)(int, char *[], void *), void *actarg,
36 char *start, char *range, char *field, char *flags[]);
39 /* set_pobox - this does all of the real work.
40 * argv = user_id, type, box
41 * if type is POP, then box should be a machine, and its ID should be put in
42 * pop_id. If type is IMAP, then box should be a filesys, and its ID should
43 * be put in pop_id. If type is SMTP, then box should be a string and its
44 * ID should be put in box_id. If type is NONE, then box doesn't matter.
47 int set_pobox(struct query *q, char **argv, client *cl)
49 EXEC SQL BEGIN DECLARE SECTION;
51 char *box, potype[USERS_POTYPE_SIZE];
52 EXEC SQL END DECLARE SECTION;
56 user = *(int *)argv[0];
58 EXEC SQL SELECT pop_id, potype INTO :id, :potype FROM users
59 WHERE users_id = :user;
62 if (!strcmp(strtrim(potype), "POP") ||
63 (!strcmp(strtrim(potype), "SPLIT") && id))
64 set_pop_usage(id, -1);
66 if (!strcmp(argv[1], "POP"))
68 status = name_to_id(box, MACHINE_TABLE, &id);
69 if (status == MR_NO_MATCH)
73 EXEC SQL UPDATE users SET potype = 'POP', pop_id = :id, imap_id = 0
74 WHERE users_id = :user;
77 else if (!strcmp(argv[1], "SMTP") || !strcmp(argv[1], "SPLIT"))
79 if (strchr(box, '/') || strchr(box, '|'))
81 status = name_to_id(box, STRINGS_TABLE, &id);
82 if (status == MR_NO_MATCH)
87 /* If going from SMTP or NONE to SPLIT, make sure we have a valid
90 if ((!strcmp(potype, "SMTP") || !strcmp(potype, "NONE")) &&
91 !strcmp(argv[1], "SPLIT"))
93 status = set_pobox_pop(q, argv, cl);
97 strlcpy(potype, argv[1], sizeof(potype));
98 EXEC SQL UPDATE users SET potype = :potype, box_id = :id
99 WHERE users_id = :user;
101 else if (!strcmp(argv[1], "IMAP"))
103 EXEC SQL SELECT filsys_id INTO :id FROM filesys
104 WHERE label = :box AND type = 'IMAP';
107 EXEC SQL UPDATE users SET potype = 'IMAP', imap_id = :id, pop_id = 0
108 WHERE users_id = :user;
110 else /* argv[1] == "NONE" */
112 EXEC SQL UPDATE users SET potype = 'NONE'
113 WHERE users_id = :user;
116 set_pobox_modtime(q, argv, cl);
117 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
118 WHERE table_name = 'users';
124 /* set_pobox_pop: Revert to existing POP or IMAP pobox.
125 * Also take care of keeping track of the post office usage.
127 int set_pobox_pop(struct query *q, char **argv, client *cl)
129 EXEC SQL BEGIN DECLARE SECTION;
130 int id, pid, iid, mid;
131 char type[USERS_POTYPE_SIZE];
132 EXEC SQL END DECLARE SECTION;
134 id = *(int *)argv[0];
135 EXEC SQL SELECT potype, pop_id, imap_id INTO :type, :pid, :iid
136 FROM users WHERE users_id = :id;
137 if (sqlca.sqlerrd[2] == 0 || (pid == 0 && iid == 0))
142 EXEC SQL SELECT mach_id INTO :mid FROM machine
143 WHERE mach_id = :pid;
144 if (sqlca.sqlerrd[2] == 0)
146 EXEC SQL UPDATE users SET potype = 'POP' WHERE users_id = :id;
147 if (!strcmp(strtrim(type), "POP"))
148 set_pop_usage(mid, 1);
152 EXEC SQL SELECT filsys_id INTO :mid FROM filesys
153 WHERE filsys_id = :iid;
154 if (sqlca.sqlerrd[2] == 0)
156 EXEC SQL UPDATE users SET potype = 'IMAP' WHERE users_id = :id;
159 set_pobox_modtime(q, argv, cl);
160 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
161 WHERE table_name = 'users';
168 /* Add_member_to_list: do list flattening as we go! MAXLISTDEPTH is
169 * how many different ancestors a member is allowed to have.
172 #define MAXLISTDEPTH 1024
174 int add_member_to_list(struct query *q, char **argv, client *cl)
176 EXEC SQL BEGIN DECLARE SECTION;
177 int id, lid, mid, tag, error, who, ref, rowcnt;
178 char *mtype, dtype[IMEMBERS_MEMBER_TYPE_SIZE], *entity;
179 EXEC SQL END DECLARE SECTION;
180 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
181 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
183 char *dtypes[MAXLISTDEPTH];
184 char *iargv[3], *buf;
186 lid = *(int *)argv[0];
188 mid = *(int *)argv[2];
189 tag = !strcmp(q->shortname, "atml") ? *(int *)argv[3] : 0;
191 if (acl_access_check(lid, cl))
194 /* if the member is already a direct member of the list, punt */
195 EXEC SQL SELECT COUNT(list_id) INTO :rowcnt FROM imembers
196 WHERE list_id = :lid AND member_id = :mid
197 AND member_type = :mtype AND direct = 1;
200 if (!strcasecmp(mtype, "STRING"))
203 status = id_to_name(mid, STRINGS_TABLE, &buf);
206 if (strchr(buf, '/') || strchr(buf, '|'))
217 EXEC SQL DECLARE csr103 CURSOR FOR
218 SELECT list_id, ref_count FROM imembers
219 WHERE member_id = :lid AND member_type = 'LIST';
222 EXEC SQL OPEN csr103;
227 EXEC SQL FETCH csr103 INTO :id, :ref;
231 ancestors[acount++] = id;
232 if (acount >= MAXLISTDEPTH)
235 EXEC SQL CLOSE csr103;
238 if (acount >= MAXLISTDEPTH)
240 descendants[0] = mid;
245 if (!strcmp(mtype, "LIST"))
247 EXEC SQL DECLARE csr104 CURSOR FOR
248 SELECT member_id, member_type, ref_count
250 WHERE list_id = :mid;
253 EXEC SQL OPEN csr104;
258 EXEC SQL FETCH csr104 INTO :id, :dtype, :ref;
264 dtypes[dcount] = "LIST";
267 dtypes[dcount] = "USER";
270 dtypes[dcount] = "STRING";
273 dtypes[dcount] = "KERBEROS";
280 descendants[dcount++] = id;
281 if (dcount >= MAXLISTDEPTH)
287 EXEC SQL CLOSE csr104;
293 for (a = 0; a < acount; a++)
296 for (d = 0; d < dcount; d++)
298 mid = descendants[d];
300 if (mid == lid && !strcmp(mtype, "LIST"))
302 EXEC SQL SELECT COUNT(ref_count) INTO :rowcnt
304 WHERE list_id = :lid AND member_id = :mid
305 AND member_type = :mtype;
306 ref = aref[a] * dref[d];
309 if (a == 0 && d == 0)
311 EXEC SQL UPDATE imembers
312 SET ref_count = ref_count + :ref, direct = 1, tag = :tag
313 WHERE list_id = :lid AND member_id = :mid
314 AND member_type = :mtype;
318 EXEC SQL UPDATE imembers
319 SET ref_count = ref_count + :ref
320 WHERE list_id = :lid AND member_id = :mid
321 AND member_type = :mtype;
326 incremental_clear_before();
327 if (a == 0 && d == 0)
329 EXEC SQL INSERT INTO imembers
330 (list_id, member_type, member_id, tag, direct, ref_count)
331 VALUES (:lid, :mtype, :mid, :tag, 1, :ref);
335 EXEC SQL INSERT INTO imembers
336 (list_id, member_type, member_id, tag, direct, ref_count)
337 VALUES (:lid, :mtype, :mid, :tag, 0, :ref);
339 iargv[0] = (char *)lid;
341 iargv[2] = (char *)mid;
342 incremental_after(IMEMBERS_TABLE, 0, iargv);
346 lid = *(int *)argv[0];
350 SET modtime = SYSDATE, modby = :who, modwith = :entity
351 WHERE list_id = :lid;
358 /* Delete_member_from_list: do list flattening as we go!
361 int delete_member_from_list(struct query *q, char **argv, client *cl)
363 EXEC SQL BEGIN DECLARE SECTION;
364 int id, lid, mid, cnt, error, who, ref;
365 char *mtype, dtype[IMEMBERS_MEMBER_TYPE_SIZE], *entity;
366 EXEC SQL END DECLARE SECTION;
367 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
368 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
369 char *dtypes[MAXLISTDEPTH];
372 lid = *(int *)argv[0];
374 mid = *(int *)argv[2];
376 if (acl_access_check(lid, cl))
379 /* if the member is not a direct member of the list, punt */
380 EXEC SQL SELECT COUNT(list_id) INTO :cnt FROM imembers
381 WHERE list_id = :lid AND member_id = :mid
382 AND member_type = :mtype AND direct = 1;
390 EXEC SQL DECLARE csr105 CURSOR FOR
391 SELECT list_id, ref_count FROM imembers
392 WHERE member_id = :lid AND member_type = 'LIST';
395 EXEC SQL OPEN csr105;
400 EXEC SQL FETCH csr105 INTO :id, :ref;
404 ancestors[acount++] = id;
405 if (acount >= MAXLISTDEPTH)
408 EXEC SQL CLOSE csr105;
411 if (acount >= MAXLISTDEPTH)
413 descendants[0] = mid;
418 if (!strcmp(mtype, "LIST"))
420 EXEC SQL DECLARE csr106 CURSOR FOR
421 SELECT member_id, member_type, ref_count FROM imembers
422 WHERE list_id = :mid;
425 EXEC SQL OPEN csr106;
430 EXEC SQL FETCH csr106 INTO :id, :dtype, :ref;
436 dtypes[dcount] = "LIST";
439 dtypes[dcount] = "USER";
442 dtypes[dcount] = "STRING";
445 dtypes[dcount] = "KERBEROS";
452 descendants[dcount++] = id;
453 if (dcount >= MAXLISTDEPTH)
456 EXEC SQL CLOSE csr106;
462 for (a = 0; a < acount; a++)
465 for (d = 0; d < dcount; d++)
467 mid = descendants[d];
469 if (mid == lid && !strcmp(mtype, "LIST"))
471 EXEC SQL SELECT ref_count INTO :cnt FROM imembers
472 WHERE list_id = :lid AND member_id = :mid AND member_type = :mtype;
473 ref = aref[a] * dref[d];
476 iargv[0] = (char *)lid;
478 iargv[2] = (char *)mid;
479 incremental_before(IMEMBERS_TABLE, 0, iargv);
480 EXEC SQL DELETE FROM imembers
481 WHERE list_id = :lid AND member_id = :mid
482 AND member_type= :mtype;
483 incremental_clear_after();
485 else if (a == 0 && d == 0)
487 EXEC SQL UPDATE imembers
488 SET ref_count = ref_count - :ref, direct = 0
489 WHERE list_id = :lid AND member_id = :mid
490 AND member_type = :mtype;
494 EXEC SQL UPDATE imembers
495 SET ref_count = ref_count - :ref
496 WHERE list_id = :lid AND member_id = :mid
497 AND member_type = :mtype;
501 lid = *(int *)argv[0];
504 EXEC SQL UPDATE list SET modtime = SYSDATE, modby = :who, modwith = :entity
505 WHERE list_id = :lid;
511 int tag_member_of_list(struct query *q, char **argv, client *cl)
513 EXEC SQL BEGIN DECLARE SECTION;
514 int lid, mid, cnt, tag;
516 EXEC SQL END DECLARE SECTION;
519 lid = *(int *)argv[0];
521 mid = *(int *)argv[2];
522 tag = *(int *)argv[3];
524 EXEC SQL SELECT COUNT(member_id) INTO :cnt FROM imembers
525 WHERE member_id = :mid AND member_type = :mtype AND
532 incremental_clear_before();
533 EXEC SQL UPDATE imembers SET tag = :tag WHERE list_id = :lid
534 AND member_type = :mtype AND member_id = :mid;
538 iargv[0] = (char *)lid;
540 iargv[2] = (char *)mid;
541 incremental_after(IMEMBERS_TABLE, 0, iargv);
546 /* Don't allow someone to add someone to a list which is the acl of a
547 * query unless they're on the list acl, even if they're on the amtl
548 * query acl! Also, don't allow someone proxying to add someone to a
551 int acl_access_check(int list_id, client *cl)
553 EXEC SQL BEGIN DECLARE SECTION;
554 int c1, c2, lid = list_id, acl_id, memacl_id;
555 char acl_type[LIST_ACL_TYPE_SIZE], memacl_type[LIST_ACL_TYPE_SIZE];
556 EXEC SQL END DECLARE SECTION;
558 /* Check if the list is directly a capacl */
559 EXEC SQL SELECT COUNT(list_id) INTO :c1 FROM capacls WHERE list_id=:lid;
561 /* Check if the list is a member (direct or indirect) of a list that
563 EXEC SQL SELECT COUNT(l1.list_id) INTO :c2 FROM list l1, list l2,
564 imembers im, capacls c WHERE c.list_id = l2.list_id AND
565 im.list_id = l2.list_id AND im.member_type = 'LIST' AND
566 im.member_id = l1.list_id AND l1.list_id = :lid;
568 if (c1 == 0 && c2 == 0)
574 EXEC SQL SELECT acl_type, acl_id, memacl_type, memacl_id
575 INTO :acl_type, :acl_id, :memacl_type, :memacl_id
576 FROM list WHERE list_id=:lid;
578 if (!find_member(acl_type, acl_id, cl))
580 if (!find_member(memacl_type, memacl_id, cl))
588 /* get_ace_use - given a type and a name, return a type and a name.
589 * The ace_type is one of "LIST", "USER", "RLIST", or "RUSER" in argv[0],
590 * and argv[1] will contain the ID of the entity in question. The R*
591 * types mean to recursively look at every containing list, not just
592 * when the object in question is a direct member. On return, the
593 * usage type will be one of LIST, SERVICE, FILESYS, QUOTA, QUERY, or ZEPHYR.
596 int get_ace_use(struct query *q, char *argv[], client *cl,
597 int (*action)(int, char *[], void *), void *actarg)
600 EXEC SQL BEGIN DECLARE SECTION;
603 EXEC SQL END DECLARE SECTION;
604 struct save_queue *sq;
607 aid = *(int *)argv[1];
608 if (!strcmp(atype, "LIST") || !strcmp(atype, "USER") ||
609 !strcmp(atype, "KERBEROS"))
610 return get_ace_internal(atype, aid, action, actarg);
613 if (!strcmp(atype, "RLIST"))
615 sq_save_data(sq, (void *)aid);
616 /* get all the list_id's of containing lists */
617 EXEC SQL DECLARE csr107 CURSOR FOR
618 SELECT list_id FROM imembers
619 WHERE member_type = 'LIST' AND member_id = :aid;
622 EXEC SQL OPEN csr107;
627 EXEC SQL FETCH csr107 INTO :listid;
630 sq_save_unique_data(sq, (void *)listid);
632 EXEC SQL CLOSE csr107;
633 /* now process each one */
634 while (sq_get_data(sq, &id))
636 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
641 if (!strcmp(atype, "RUSER"))
643 EXEC SQL DECLARE csr108 CURSOR FOR
644 SELECT list_id FROM imembers
645 WHERE member_type = 'USER' AND member_id = :aid;
648 EXEC SQL OPEN csr108;
653 EXEC SQL FETCH csr108 INTO :listid;
656 sq_save_data(sq, (void *)listid);
658 EXEC SQL CLOSE csr108;
659 /* now process each one */
660 while (sq_get_data(sq, &id))
662 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
665 if (get_ace_internal("USER", aid, action, actarg) == MR_SUCCESS)
669 if (!strcmp(atype, "RKERBEROS"))
671 EXEC SQL DECLARE csr109 CURSOR FOR
672 SELECT list_id FROM imembers
673 WHERE member_type = 'KERBEROS' AND member_id = :aid;
676 EXEC SQL OPEN csr109;
681 EXEC SQL FETCH csr109 INTO :listid;
684 sq_save_data(sq, (void *)listid);
686 EXEC SQL CLOSE csr109;
687 /* now process each one */
688 while (sq_get_data(sq, &id))
690 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
693 if (get_ace_internal("KERBEROS", aid, action, actarg) == MR_SUCCESS)
706 /* This looks up a single list or user for ace use. atype must be "USER"
707 * or "LIST", and aid is the ID of the corresponding object. This is used
708 * by get_ace_use above.
711 int get_ace_internal(char *atype, int aid,
712 int (*action)(int, char *[], void *), void *actarg)
716 EXEC SQL BEGIN DECLARE SECTION;
717 char name[MAX_FIELD_WIDTH], *type = atype;
719 EXEC SQL END DECLARE SECTION;
722 if (!strcmp(atype, "LIST"))
724 rargv[0] = "FILESYS";
725 EXEC SQL DECLARE csr110 CURSOR FOR
726 SELECT label FROM filesys
730 EXEC SQL OPEN csr110;
735 EXEC SQL FETCH csr110 INTO :name;
738 (*action)(2, rargv, actarg);
741 EXEC SQL CLOSE csr110;
744 EXEC SQL DECLARE csr111 CURSOR FOR
745 SELECT capability FROM capacls
749 EXEC SQL OPEN csr111;
754 EXEC SQL FETCH csr111 INTO :name;
757 (*action)(2, rargv, actarg);
760 EXEC SQL CLOSE csr111;
762 else if (!strcmp(atype, "USER"))
764 rargv[0] = "FILESYS";
765 EXEC SQL DECLARE csr112 CURSOR FOR
766 SELECT label FROM filesys
770 EXEC SQL OPEN csr112;
775 EXEC SQL FETCH csr112 INTO :name;
778 (*action)(2, rargv, actarg);
781 EXEC SQL CLOSE csr112;
785 EXEC SQL DECLARE csr113 CURSOR FOR
786 SELECT name FROM list
787 WHERE (acl_type = :type AND acl_id = :id)
788 OR (memacl_type = :type AND memacl_id = :id);
791 EXEC SQL OPEN csr113;
796 EXEC SQL FETCH csr113 INTO :name;
799 (*action)(2, rargv, actarg);
802 EXEC SQL CLOSE csr113;
804 rargv[0] = "SERVICE";
805 EXEC SQL DECLARE csr114 CURSOR FOR
806 SELECT name FROM servers
807 WHERE acl_type = :type AND acl_id = :id;
810 EXEC SQL OPEN csr114;
815 EXEC SQL FETCH csr114 INTO :name;
818 (*action)(2, rargv, actarg);
821 EXEC SQL CLOSE csr114;
823 rargv[0] = "HOSTACCESS";
824 EXEC SQL DECLARE csr115 CURSOR FOR
825 SELECT name FROM machine m, hostaccess ha
826 WHERE m.mach_id = ha.mach_id AND ha.acl_type = :type
830 EXEC SQL OPEN csr115;
835 EXEC SQL FETCH csr115 INTO :name;
838 (*action)(2, rargv, actarg);
841 EXEC SQL CLOSE csr115;
843 rargv[0] = "MACHINE";
844 EXEC SQL DECLARE csr115a CURSOR FOR
845 SELECT name FROM machine m
846 WHERE m.owner_type = :type
847 AND m.owner_id = :id;
850 EXEC SQL OPEN csr115a;
855 EXEC SQL FETCH csr115a INTO :name;
858 (*action)(2, rargv, actarg);
861 EXEC SQL CLOSE csr115a;
864 EXEC SQL DECLARE csr116 CURSOR FOR
865 SELECT class FROM zephyr z
866 WHERE z.xmt_type = :type AND z.xmt_id = :id
867 OR z.sub_type = :type AND z.sub_id = :id
868 OR z.iws_type = :type AND z.iws_id = :id
869 OR z.iui_type = :type AND z.iui_id = :id;
872 EXEC SQL OPEN csr116;
877 EXEC SQL FETCH csr116 INTO :name;
880 (*action)(2, rargv, actarg);
883 EXEC SQL CLOSE csr116;
891 int ghbo_internal(char *atype, int aid,
892 int (*action)(int, char *[], void *), void *actarg)
896 EXEC SQL BEGIN DECLARE SECTION;
897 char name[MACHINE_NAME_SIZE], *type = atype;
899 EXEC SQL END DECLARE SECTION;
902 EXEC SQL DECLARE csr115b CURSOR FOR
903 SELECT name FROM machine m
904 WHERE m.owner_type = :type
905 AND m.owner_id = :id;
908 EXEC SQL OPEN csr115b;
913 EXEC SQL FETCH csr115b INTO :name;
916 (*action)(1, rargv, actarg);
919 EXEC SQL CLOSE csr115b;
926 /* get_host_by_owner - like gaus but limited to hosts */
927 int get_host_by_owner(struct query *q, char *argv[], client *cl,
928 int (*action)(int, char *[], void *), void *actarg)
931 EXEC SQL BEGIN DECLARE SECTION;
934 EXEC SQL END DECLARE SECTION;
935 struct save_queue *sq;
938 aid = *(int *)argv[1];
939 if (!strcmp(atype, "LIST") || !strcmp(atype, "USER") ||
940 !strcmp(atype, "KERBEROS"))
941 return ghbo_internal(atype, aid, action, actarg);
944 if (!strcmp(atype, "RLIST"))
946 sq_save_data(sq, (void *)aid);
947 /* get all the list_id's of containing lists */
948 EXEC SQL DECLARE csr107q CURSOR FOR
949 SELECT list_id FROM imembers
950 WHERE member_type = 'LIST' AND member_id = :aid;
953 EXEC SQL OPEN csr107q;
958 EXEC SQL FETCH csr107q INTO :listid;
961 sq_save_unique_data(sq, (void *)listid);
963 EXEC SQL CLOSE csr107q;
964 /* now process each one */
965 while (sq_get_data(sq, &id))
967 if (ghbo_internal("LIST", id, action, actarg) == MR_SUCCESS)
972 if (!strcmp(atype, "RUSER"))
974 EXEC SQL DECLARE csr108q CURSOR FOR
975 SELECT list_id FROM imembers
976 WHERE member_type = 'USER' AND member_id = :aid;
979 EXEC SQL OPEN csr108q;
984 EXEC SQL FETCH csr108q INTO :listid;
987 sq_save_data(sq, (void *)listid);
989 EXEC SQL CLOSE csr108q;
990 /* now process each one */
991 while (sq_get_data(sq, &id))
993 if (ghbo_internal("LIST", id, action, actarg) == MR_SUCCESS)
996 if (ghbo_internal("USER", aid, action, actarg) == MR_SUCCESS)
1000 if (!strcmp(atype, "RKERBEROS"))
1002 EXEC SQL DECLARE csr109q CURSOR FOR
1003 SELECT list_id FROM imembers
1004 WHERE member_type = 'KERBEROS' AND member_id = :aid;
1007 EXEC SQL OPEN csr109q;
1012 EXEC SQL FETCH csr109q INTO :listid;
1015 sq_save_data(sq, (void *)listid);
1017 EXEC SQL CLOSE csr109q;
1018 /* now process each one */
1019 while (sq_get_data(sq, &id))
1021 if (ghbo_internal("LIST", id, action, actarg) == MR_SUCCESS)
1024 if (ghbo_internal("KERBEROS", aid, action, actarg) == MR_SUCCESS)
1036 /* get_lists_of_member - given a type and a name, return the name and flags
1037 * of all of the lists of the given member. The member_type is one of
1038 * "LIST", "USER", "STRING", "RLIST", "RUSER", or "RSTRING" in argv[0],
1039 * and argv[1] will contain the ID of the entity in question. The R*
1040 * types mean to recursively look at every containing list, not just
1041 * when the object in question is a direct member.
1044 int get_lists_of_member(struct query *q, char *argv[], client *cl,
1045 int (*action)(int, char *[], void *), void *actarg)
1047 int found = 0, direct = 1;
1049 EXEC SQL BEGIN DECLARE SECTION;
1052 char name[LIST_NAME_SIZE];
1053 char active[5], public[5], hidden[5], maillist[5], grouplist[5];
1054 EXEC SQL END DECLARE SECTION;
1057 aid = *(int *)argv[1];
1058 if (!strcmp(atype, "RLIST"))
1063 if (!strcmp(atype, "RUSER"))
1068 if (!strcmp(atype, "RSTRING"))
1073 if (!strcmp(atype, "RKERBEROS"))
1083 rargv[4] = maillist;
1084 rargv[5] = grouplist;
1087 EXEC SQL DECLARE csr117a CURSOR FOR
1088 SELECT l.name, l.active, l.publicflg, l.hidden, l.maillist, l.grouplist
1089 FROM list l, imembers im
1090 WHERE l.list_id = im.list_id AND im.direct = 1
1091 AND im.member_type = :atype AND im.member_id = :aid;
1094 EXEC SQL OPEN csr117a;
1099 EXEC SQL FETCH csr117a
1100 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
1103 (*action)(6, rargv, actarg);
1106 EXEC SQL CLOSE csr117a;
1110 EXEC SQL DECLARE csr117b CURSOR FOR
1111 SELECT l.name, l.active, l.publicflg, l.hidden, l.maillist, l.grouplist
1112 FROM list l, imembers im
1113 WHERE l.list_id = im.list_id
1114 AND im.member_type = :atype AND im.member_id = :aid;
1117 EXEC SQL OPEN csr117b;
1122 EXEC SQL FETCH csr117b
1123 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
1126 (*action)(6, rargv, actarg);
1129 EXEC SQL CLOSE csr117b;
1140 /* qualified_get_lists: passed "TRUE", "FALSE", or "DONTCARE" for each of
1141 * the five flags associated with each list. It will return the name of
1142 * each list that meets the quailifications. It does this by building a
1143 * where clause based on the arguments, then doing a retrieve.
1146 static char *lflags[5] = { "active", "publicflg", "hidden", "maillist", "grouplist" };
1148 int qualified_get_lists(struct query *q, char *argv[], client *cl,
1149 int (*action)(int, char *[], void *), void *actarg)
1151 return qualified_get(q, argv, action, actarg, "l.list_id != 0",
1152 "l", "name", lflags);
1156 int get_members_of_list(struct query *q, char *argv[], client *cl,
1157 int (*action)(int, char *[], void *), void *actarg)
1159 EXEC SQL BEGIN DECLARE SECTION;
1160 int list_id, direct;
1161 char member_name[MAX_FIELD_WIDTH], tag[STRINGS_STRING_SIZE];
1162 EXEC SQL END DECLARE SECTION;
1166 /* For gmol or gtml, only get direct members. For geml, get all. */
1167 if (!strcmp(q->shortname, "geml"))
1172 /* For gmol or geml, only return type and name. For gtml, return tag too. */
1173 if (!strcmp(q->shortname, "gtml"))
1178 list_id = *(int *)argv[0];
1180 targv[1] = member_name;
1184 EXEC SQL DECLARE csr119 CURSOR FOR
1185 SELECT u.login, s.string FROM users u, imembers im, strings s
1186 WHERE im.list_id = :list_id AND im.member_type = 'USER'
1187 AND im.member_id = u.users_id AND im.direct > :direct
1188 AND s.string_id = im.tag ORDER BY 1;
1191 EXEC SQL OPEN csr119;
1196 EXEC SQL FETCH csr119 INTO :member_name, :tag;
1199 (*action)(targc, targv, actarg);
1201 EXEC SQL CLOSE csr119;
1206 EXEC SQL DECLARE csr120 CURSOR FOR
1207 SELECT l.name, s.string FROM list l, imembers im, strings s
1208 WHERE im.list_id = :list_id AND im.member_type = 'LIST'
1209 AND im.member_id = l.list_id AND im.direct > :direct
1210 AND s.string_id = im.tag ORDER BY 1;
1213 EXEC SQL OPEN csr120;
1218 EXEC SQL FETCH csr120 INTO :member_name, :tag;
1221 (*action)(targc, targv, actarg);
1223 EXEC SQL CLOSE csr120;
1227 targv[0] = "STRING";
1228 EXEC SQL DECLARE csr121 CURSOR FOR
1229 SELECT str.string, s.string FROM strings str, imembers im, strings s
1230 WHERE im.list_id = :list_id AND im.member_type = 'STRING'
1231 AND im.member_id = str.string_id AND im.direct > :direct
1232 AND s.string_id = im.tag ORDER BY 1;
1235 EXEC SQL OPEN csr121;
1240 EXEC SQL FETCH csr121 INTO :member_name, :tag;
1243 (*action)(targc, targv, actarg);
1245 EXEC SQL CLOSE csr121;
1249 targv[0] = "KERBEROS";
1250 EXEC SQL DECLARE csr122 CURSOR FOR
1251 SELECT str.string, s.string FROM strings str, imembers im, strings s
1252 WHERE im.list_id = :list_id AND im.member_type = 'KERBEROS'
1253 AND im.member_id = str.string_id AND im.direct > :direct
1254 AND s.string_id = im.tag ORDER BY 1;
1257 EXEC SQL OPEN csr122;
1262 EXEC SQL FETCH csr122 INTO :member_name, :tag;
1265 (*action)(targc, targv, actarg);
1267 EXEC SQL CLOSE csr122;
1275 /* count_members_of_list: this is a simple query, but it cannot be done
1276 * through the dispatch table.
1279 int count_members_of_list(struct query *q, char *argv[], client *cl,
1280 int (*action)(int, char *[], void *), void *actarg)
1282 EXEC SQL BEGIN DECLARE SECTION;
1284 EXEC SQL END DECLARE SECTION;
1285 char *rargv[1], countbuf[5];
1287 list = *(int *)argv[0];
1288 rargv[0] = countbuf;
1289 EXEC SQL SELECT count (*) INTO :ct FROM imembers
1290 WHERE list_id = :list AND direct = 1;
1293 sprintf(countbuf, "%d", ct);
1294 (*action)(1, rargv, actarg);
1299 /* qualified_get_server: passed "TRUE", "FALSE", or "DONTCARE" for each of
1300 * the three flags associated with each service. It will return the name of
1301 * each service that meets the quailifications. It does this by building a
1302 * where clause based on the arguments, then doing a retrieve.
1305 static char *sflags[3] = { "enable", "inprogress", "harderror" };
1307 int qualified_get_server(struct query *q, char *argv[], client *cl,
1308 int (*action)(int, char *[], void *), void *actarg)
1310 return qualified_get(q, argv, action, actarg, "s.name is not null",
1311 "s", "name", sflags);
1312 /* of course, name will never be null, but we need something there
1313 to make qualified_get happy */
1317 /* generic qualified get routine, used by qualified_get_lists,
1318 * qualified_get_server, and qualified_get_serverhost.
1320 * start - a simple where clause, must not be empty
1321 * range - the name of the range variable
1322 * field - the field to return
1323 * flags - an array of strings, names of the flag variables
1326 int qualified_get(struct query *q, char *argv[],
1327 int (*action)(int, char *[], void *), void *actarg,
1328 char *start, char *range, char *field, char *flags[])
1334 strcpy(qual, start);
1335 for (i = 0; i < q->argc; i++)
1337 if (!strcmp(argv[i], "TRUE"))
1339 sprintf(buf, " AND %s.%s != 0", range, flags[i]);
1342 else if (!strcmp(argv[i], "FALSE"))
1344 sprintf(buf, " AND %s.%s = 0", range, flags[i]);
1349 sprintf(stmt_buf, "SELECT %s.%s FROM %s %s WHERE %s", range, field,
1350 table_name[q->rtable], range, qual);
1351 return do_for_all_rows(stmt_buf, 1, action, actarg);
1355 /* qualified_get_serverhost: passed "TRUE", "FALSE", or "DONTCARE" for each of
1356 * the five flags associated with each serverhost. It will return the name of
1357 * each service and host that meets the quailifications. It does this by
1358 * building a where clause based on the arguments, then doing a retrieve.
1361 static char *shflags[6] = { "service", "enable", "override", "success",
1362 "inprogress", "hosterror" };
1364 int qualified_get_serverhost(struct query *q, char *argv[], client *cl,
1365 int (*action)(int, char *[], void *),
1368 char qual[256], buf[32];
1371 sprintf(qual, "m.mach_id = sh.mach_id AND sh.service = UPPER('%s')",
1373 for (i = 1; i < q->argc; i++)
1375 if (!strcmp(argv[i], "TRUE"))
1377 sprintf(buf, " AND sh.%s != 0", shflags[i]);
1380 else if (!strcmp(argv[i], "FALSE"))
1382 sprintf(buf, " AND sh.%s = 0", shflags[i]);
1387 sprintf(stmt_buf, "SELECT sh.service, m.name FROM serverhosts sh, "
1388 "machine m WHERE %s", qual);
1389 return do_for_all_rows(stmt_buf, 2, action, actarg);
1393 /* register_user - change user's login name and allocate a pobox, group,
1394 * filesystem, and quota for them. The user's status must start out as 0,
1395 * and is left as 2. Arguments are: user's UID, new login name, and
1396 * pobox type ("POP" = POP, "IMAP" or numeric = IMAP)
1399 int register_user(struct query *q, char **argv, client *cl)
1401 EXEC SQL BEGIN DECLARE SECTION;
1402 char *login, *entity;
1403 char directory[FILESYS_NAME_SIZE], machname[MACHINE_NAME_SIZE];
1404 char dir[NFSPHYS_DIR_SIZE], *potype;
1405 int who, rowcount, mid, uid, users_id;
1406 int ostatus, nstatus, fsidval, popid;
1409 static int m_id, def_quota, def_imap_quota, list_id;
1410 EXEC SQL END DECLARE SECTION;
1411 char buffer[256], *aargv[3];
1415 EXEC SQL SELECT list_id INTO :list_id FROM list
1416 WHERE name = 'wheel';
1418 EXEC SQL SELECT mach_id INTO :m_id FROM machine
1419 WHERE name = 'ATHENA.MIT.EDU';
1421 EXEC SQL SELECT value INTO :def_quota FROM numvalues
1422 WHERE name = 'def_quota';
1423 if (sqlca.sqlerrd[2] != 1)
1426 EXEC SQL SELECT value INTO :def_imap_quota FROM numvalues
1427 WHERE name = 'def_imap_quota';
1428 if (sqlca.sqlerrd[2] != 1)
1432 entity = cl->entity;
1433 who = cl->client_id;
1435 uid = atoi(argv[0]);
1440 EXEC SQL SELECT users_id, status INTO :users_id, :ostatus
1442 WHERE unix_uid = :uid AND (status = 0 OR status = 5 OR status = 6);
1444 if (sqlca.sqlerrd[2] == 0)
1446 if (sqlca.sqlerrd[2] > 1)
1447 return MR_NOT_UNIQUE;
1449 /* check new login name */
1450 EXEC SQL SELECT COUNT(login) INTO :rowcount FROM users
1451 WHERE login = :login AND users_id != :users_id;
1456 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM list
1457 WHERE LOWER(name) = :login;
1462 EXEC SQL SELECT COUNT(label) INTO :rowcount FROM filesys
1463 WHERE label = :login;
1468 EXEC SQL SELECT COUNT(label) INTO :rowcount
1469 FROM filesys WHERE label = :login || '.po';
1474 EXEC SQL SELECT owner INTO :tmp FROM filesys
1475 WHERE label = :login || '.po';
1478 if ((ostatus == 0) || (tmp != users_id))
1480 EXEC SQL SELECT count(potype) INTO :rowcount FROM users WHERE
1481 login = :login AND potype = 'IMAP';
1487 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM alias
1488 WHERE ( name = :login OR name = :login || '.po' )
1489 AND type = 'FILESYS';
1494 com_err(whoami, 0, "login name OK");
1496 EXEC SQL SELECT COUNT(potype) INTO :rowcount FROM users WHERE
1497 login = :login AND potype = 'POP';
1503 /* choose type and location for pobox */
1506 if (!strcmp(potype, "POP"))
1509 EXEC SQL DECLARE csr130 CURSOR FOR
1510 SELECT sh.mach_id, m.name FROM serverhosts sh, machine m
1511 WHERE sh.service = 'POP' AND sh.mach_id = m.mach_id
1512 AND sh.value2 - sh.value1 =
1513 (SELECT MAX(value2 - value1) FROM serverhosts WHERE service = 'POP');
1516 EXEC SQL OPEN csr130;
1519 EXEC SQL FETCH csr130 INTO :popid, :machname;
1520 if (sqlca.sqlerrd[2] == 0)
1522 EXEC SQL CLOSE csr130;
1529 EXEC SQL CLOSE csr130;
1534 EXEC SQL UPDATE users SET potype = 'POP', pop_id = :popid
1535 WHERE users_id = :users_id;
1536 com_err(whoami, 0, "pobox set to POP:%s", strtrim(machname));
1540 /* Select all IMAP nfsphys entries in order of increasing
1541 * (allocated - partsize). The partitions will almost always be
1542 * overallocated, but we choose the one that is the least
1547 EXEC SQL DECLARE csr_rusr_imap CURSOR FOR
1548 SELECT np.allocated - np.partsize, np.nfsphys_id, np.mach_id,
1549 np.dir, m.name FROM serverhosts sh, nfsphys np, machine m
1550 WHERE sh.service = 'POSTOFFICE' AND sh.mach_id = np.mach_id
1551 AND m.mach_id = np.mach_id
1555 EXEC SQL OPEN csr_rusr_imap;
1558 EXEC SQL FETCH csr_rusr_imap INTO :tmp, :npid, :mid, :dir, :machname;
1559 if (sqlca.sqlerrd[2] == 0)
1561 EXEC SQL CLOSE csr_rusr_imap;
1568 EXEC SQL CLOSE csr_rusr_imap;
1573 /* create filesystem */
1574 if (set_next_object_id("filsys_id", FILESYS_TABLE, 0))
1576 incremental_clear_before();
1578 EXEC SQL SELECT value INTO :popid FROM numvalues
1579 WHERE numvalues.name = 'filsys_id';
1580 EXEC SQL INSERT INTO filesys
1581 (filsys_id, phys_id, label, type, mach_id, name,
1582 mount, rwaccess, comments, owner, owners, createflg,
1583 lockertype, modtime, modby, modwith)
1585 (:popid, :npid, :login || '.po', 'IMAP', :mid, :dir,
1586 CHR(0), 'w', 'IMAP box', :users_id, :list_id, 1,
1587 'USER', SYSDATE, :who, :entity);
1591 if (sqlca.sqlerrd[2] != 1)
1593 sprintf(buffer, "fs.filsys_id = %d", popid);
1594 incremental_after(FILESYS_TABLE, buffer, 0);
1597 incremental_clear_before();
1598 EXEC SQL INSERT INTO quota
1599 (entity_id, filsys_id, type, quota, phys_id, modtime, modby, modwith)
1600 VALUES (:users_id, :popid, 'USER', :def_imap_quota, :npid,
1601 SYSDATE, :who, :entity);
1604 if (sqlca.sqlerrd[2] != 1)
1609 sprintf(buffer, "q.entity_id = %d and q.filsys_id = %d and "
1610 "q.type = 'USER'", users_id, popid);
1611 incremental_after(QUOTA_TABLE, buffer, aargv);
1615 EXEC SQL UPDATE nfsphys SET allocated = allocated + :def_imap_quota
1616 WHERE nfsphys_id = :npid;
1618 EXEC SQL UPDATE users SET potype = 'IMAP', imap_id = :popid
1619 WHERE users_id = :users_id;
1620 com_err(whoami, 0, "pobox set to IMAP:%s:%s", strtrim(machname),
1625 /* change login name, set pobox */
1626 sprintf(buffer, "u.users_id = %d", users_id);
1627 incremental_before(USERS_TABLE, buffer, 0);
1629 if (ostatus == 5 || ostatus == 6)
1631 EXEC SQL UPDATE users SET login = :login, status = :nstatus,
1632 modtime = SYSDATE, modby = :who, modwith = :entity,
1633 pmodtime = SYSDATE, pmodby = :who, pmodwith = :entity
1634 WHERE users_id = :users_id;
1638 if (sqlca.sqlerrd[2] != 1)
1641 /* Only update usage count if we created a POP pobox. */
1642 if (!strcmp(potype, "POP") && !po_exists)
1643 set_pop_usage(mid, 1);
1645 com_err(whoami, 0, "set login name to %s", login);
1646 incremental_after(USERS_TABLE, buffer, 0);
1648 /* We've just changed the login name in the DB; recache it in case
1649 the wrong thing got into the cache earlier. */
1650 cache_entry(login, USERS_TABLE, users_id);
1652 /* create filesystem */
1653 if (set_next_object_id("filsys_id", FILESYS_TABLE, 0))
1655 incremental_clear_before();
1656 if (islower(login[0]) && islower(login[1]))
1658 sprintf(directory, "/afs/athena.mit.edu/user/%c/%c/%s",
1659 login[0], login[1], login);
1662 sprintf(directory, "/afs/athena.mit.edu/user/other/%s", login);
1664 EXEC SQL SELECT value INTO :fsidval FROM numvalues
1665 WHERE numvalues.name = 'filsys_id';
1666 EXEC SQL INSERT INTO filesys
1667 (filsys_id, phys_id, label, type, mach_id, name,
1668 mount, rwaccess, comments, owner, owners, createflg,
1669 lockertype, modtime, modby, modwith)
1671 (:fsidval, 0, :login, 'AFS', :m_id, :directory,
1672 '/mit/' || :login, 'w', 'User Locker', :users_id, :list_id, 1,
1673 'HOMEDIR', SYSDATE, :who, :entity);
1677 if (sqlca.sqlerrd[2] != 1)
1679 sprintf(buffer, "fs.filsys_id = %d", fsidval);
1680 incremental_after(FILESYS_TABLE, buffer, 0);
1683 incremental_clear_before();
1684 EXEC SQL INSERT INTO quota
1685 (entity_id, filsys_id, type, quota, phys_id, modtime, modby, modwith)
1686 VALUES (0, :fsidval, 'ANY', :def_quota, 0, SYSDATE, :who, :entity);
1689 if (sqlca.sqlerrd[2] != 1)
1694 sprintf(buffer, "q.entity_id = 0 and q.filsys_id = %d and q.type = 'ANY'",
1696 incremental_after(QUOTA_TABLE, buffer, aargv);
1697 com_err(whoami, 0, "quota of %d assigned", def_quota);
1701 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
1702 WHERE table_name = 'users';
1703 EXEC SQL UPDATE tblstats SET appends = appends + 1, modtime = SYSDATE
1704 WHERE table_name = 'filesys' OR table_name = 'quota';
1710 /** set_pop_usage - incr/decr usage count for pop server in serverhosts talbe
1714 ** delta (will be +/- 1)
1717 ** - incr/decr value field in serverhosts table for pop/mach_id
1721 int set_pop_usage(id, cnt)
1724 EXEC SQL BEGIN DECLARE SECTION;
1725 int iid = id, icnt = cnt;
1726 EXEC SQL END DECLARE SECTION;
1728 EXEC SQL UPDATE serverhosts SET value1 = value1 + :icnt
1729 WHERE serverhosts.service = 'POP' AND serverhosts.mach_id = :iid;
1737 int do_user_reservation(struct query *q, char *argv[], client *cl)
1739 EXEC SQL BEGIN DECLARE SECTION;
1740 char resv[USERS_RESERVATIONS_SIZE];
1741 char *trans, name[ALIAS_NAME_SIZE];
1743 EXEC SQL END DECLARE SECTION;
1745 uid = *(int *)argv[0];
1748 EXEC SQL SELECT UPPER(name) INTO :name FROM alias
1749 WHERE type = 'RESERVE' AND LOWER(trans) = LOWER(:trans);
1752 if (sqlca.sqlerrd[2] != 1)
1756 EXEC SQL SELECT reservations INTO :resv FROM users
1757 WHERE users_id = :uid;
1762 if (!strcmp(q->shortname, "aurv"))
1764 if (strchr(resv, *name))
1766 if (strlen(resv) == USERS_RESERVATIONS_SIZE - 1)
1767 return MR_ARG_TOO_LONG;
1773 char *p = strchr(resv, *name);
1776 memmove(p, p + 1, strlen(p));
1779 EXEC SQL UPDATE users SET reservations = NVL(:resv, CHR(0))
1780 WHERE users_id = :uid;
1784 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
1785 WHERE table_name = 'users';
1786 return set_modtime_by_id(q, argv, cl);
1789 int get_user_reservations(struct query *q, char **argv, client *cl,
1790 int (*action)(int, char *[], void *), void *actarg)
1792 EXEC SQL BEGIN DECLARE SECTION;
1793 char resv[USERS_RESERVATIONS_SIZE], *p;
1794 char trans[ALIAS_TRANS_SIZE], name[2] = { 0, 0 };
1796 EXEC SQL END DECLARE SECTION;
1799 uid = *(int *)argv[0];
1801 EXEC SQL SELECT reservations INTO :resv FROM users
1802 WHERE users_id = :uid;
1808 while (*p && !isspace(*p))
1810 name[0] = toupper(*p);
1811 EXEC SQL SELECT trans INTO :trans FROM alias
1812 WHERE type = 'RESERVE' AND UPPER(name) = :name;
1815 if (sqlca.sqlerrd[2] != 1)
1816 sprintf(trans, "Unknown (%s)", name);
1817 (*action)(1, targv, actarg);
1823 int get_user_by_reservation(struct query *q, char **argv, client *cl,
1824 int (*action)(int, char *[], void *), void *actarg)
1826 EXEC SQL BEGIN DECLARE SECTION;
1827 char resv[USERS_RESERVATIONS_SIZE], login[USERS_LOGIN_SIZE];
1828 char *trans, name[ALIAS_NAME_SIZE];
1830 EXEC SQL END DECLARE SECTION;
1835 EXEC SQL SELECT UPPER(name) INTO :name FROM alias
1836 WHERE type = 'RESERVE' AND LOWER(trans) = LOWER(:trans);
1839 if (sqlca.sqlerrd[2] != 1)
1843 EXEC SQL DECLARE csr_gubr CURSOR FOR
1844 SELECT login FROM users WHERE reservations LIKE '%' || :name || '%';
1845 EXEC SQL OPEN csr_gubr;
1852 EXEC SQL FETCH csr_gubr INTO :login;
1855 (*action)(1, targv, actarg);
1857 EXEC SQL CLOSE csr_gubr;