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"))
92 status = set_pobox_pop(q, argv, cl);
96 strlcpy(potype, argv[1], sizeof(potype));
97 EXEC SQL UPDATE users SET potype = :potype, box_id = :id
98 WHERE users_id = :user;
100 else if (!strcmp(argv[1], "IMAP"))
102 EXEC SQL SELECT filsys_id INTO :id FROM filesys
103 WHERE label = :box AND type = 'IMAP';
106 EXEC SQL UPDATE users SET potype = 'IMAP', imap_id = :id, pop_id = 0
107 WHERE users_id = :user;
109 else /* argv[1] == "NONE" */
111 EXEC SQL UPDATE users SET potype = 'NONE'
112 WHERE users_id = :user;
115 set_pobox_modtime(q, argv, cl);
116 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
117 WHERE table_name = 'users';
123 /* set_pobox_pop: Revert to existing POP or IMAP pobox.
124 * Also take care of keeping track of the post office usage.
126 int set_pobox_pop(struct query *q, char **argv, client *cl)
128 EXEC SQL BEGIN DECLARE SECTION;
129 int id, pid, iid, mid;
130 char type[USERS_POTYPE_SIZE];
131 EXEC SQL END DECLARE SECTION;
133 id = *(int *)argv[0];
134 EXEC SQL SELECT potype, pop_id, imap_id INTO :type, :pid, :iid
135 FROM users WHERE users_id = :id;
136 if (sqlca.sqlerrd[2] == 0 || (pid == 0 && iid == 0))
141 EXEC SQL SELECT mach_id INTO :mid FROM machine
142 WHERE mach_id = :pid;
143 if (sqlca.sqlerrd[2] == 0)
145 EXEC SQL UPDATE users SET potype = 'POP' WHERE users_id = :id;
146 if (!strcmp(strtrim(type), "POP"))
147 set_pop_usage(mid, 1);
151 EXEC SQL SELECT filsys_id INTO :mid FROM filesys
152 WHERE filsys_id = :iid;
153 if (sqlca.sqlerrd[2] == 0)
155 EXEC SQL UPDATE users SET potype = 'IMAP' WHERE users_id = :id;
158 set_pobox_modtime(q, argv, cl);
159 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
160 WHERE table_name = 'users';
167 /* Add_member_to_list: do list flattening as we go! MAXLISTDEPTH is
168 * how many different ancestors a member is allowed to have.
171 #define MAXLISTDEPTH 1024
173 int add_member_to_list(struct query *q, char **argv, client *cl)
175 EXEC SQL BEGIN DECLARE SECTION;
176 int id, lid, mid, tag, error, who, ref, rowcnt;
177 char *mtype, dtype[IMEMBERS_MEMBER_TYPE_SIZE], *entity;
178 EXEC SQL END DECLARE SECTION;
179 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
180 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
182 char *dtypes[MAXLISTDEPTH];
183 char *iargv[3], *buf;
185 lid = *(int *)argv[0];
187 mid = *(int *)argv[2];
188 tag = !strcmp(q->shortname, "atml") ? *(int *)argv[3] : 0;
190 if (acl_access_check(lid, cl))
193 /* if the member is already a direct member of the list, punt */
194 EXEC SQL SELECT COUNT(list_id) INTO :rowcnt FROM imembers
195 WHERE list_id = :lid AND member_id = :mid
196 AND member_type = :mtype AND direct = 1;
199 if (!strcasecmp(mtype, "STRING"))
202 status = id_to_name(mid, STRINGS_TABLE, &buf);
205 if (strchr(buf, '/') || strchr(buf, '|'))
216 EXEC SQL DECLARE csr103 CURSOR FOR
217 SELECT list_id, ref_count FROM imembers
218 WHERE member_id = :lid AND member_type = 'LIST';
221 EXEC SQL OPEN csr103;
226 EXEC SQL FETCH csr103 INTO :id, :ref;
230 ancestors[acount++] = id;
231 if (acount >= MAXLISTDEPTH)
234 EXEC SQL CLOSE csr103;
237 if (acount >= MAXLISTDEPTH)
239 descendants[0] = mid;
244 if (!strcmp(mtype, "LIST"))
246 EXEC SQL DECLARE csr104 CURSOR FOR
247 SELECT member_id, member_type, ref_count
249 WHERE list_id = :mid;
252 EXEC SQL OPEN csr104;
257 EXEC SQL FETCH csr104 INTO :id, :dtype, :ref;
263 dtypes[dcount] = "LIST";
266 dtypes[dcount] = "USER";
269 dtypes[dcount] = "STRING";
272 dtypes[dcount] = "KERBEROS";
279 descendants[dcount++] = id;
280 if (dcount >= MAXLISTDEPTH)
286 EXEC SQL CLOSE csr104;
292 for (a = 0; a < acount; a++)
295 for (d = 0; d < dcount; d++)
297 mid = descendants[d];
299 if (mid == lid && !strcmp(mtype, "LIST"))
301 EXEC SQL SELECT COUNT(ref_count) INTO :rowcnt
303 WHERE list_id = :lid AND member_id = :mid
304 AND member_type = :mtype;
305 ref = aref[a] * dref[d];
308 if (a == 0 && d == 0)
310 EXEC SQL UPDATE imembers
311 SET ref_count = ref_count + :ref, direct = 1, tag = :tag
312 WHERE list_id = :lid AND member_id = :mid
313 AND member_type = :mtype;
317 EXEC SQL UPDATE imembers
318 SET ref_count = ref_count + :ref
319 WHERE list_id = :lid AND member_id = :mid
320 AND member_type = :mtype;
325 incremental_clear_before();
326 if (a == 0 && d == 0)
328 EXEC SQL INSERT INTO imembers
329 (list_id, member_type, member_id, tag, direct, ref_count)
330 VALUES (:lid, :mtype, :mid, :tag, 1, 1);
334 EXEC SQL INSERT INTO imembers
335 (list_id, member_type, member_id, tag, direct, ref_count)
336 VALUES (:lid, :mtype, :mid, :tag, 0, 1);
338 iargv[0] = (char *)lid;
340 iargv[2] = (char *)mid;
341 incremental_after(IMEMBERS_TABLE, 0, iargv);
345 lid = *(int *)argv[0];
349 SET modtime = SYSDATE, modby = :who, modwith = :entity
350 WHERE list_id = :lid;
357 /* Delete_member_from_list: do list flattening as we go!
360 int delete_member_from_list(struct query *q, char **argv, client *cl)
362 EXEC SQL BEGIN DECLARE SECTION;
363 int id, lid, mid, cnt, error, who, ref;
364 char *mtype, dtype[IMEMBERS_MEMBER_TYPE_SIZE], *entity;
365 EXEC SQL END DECLARE SECTION;
366 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
367 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
368 char *dtypes[MAXLISTDEPTH];
371 lid = *(int *)argv[0];
373 mid = *(int *)argv[2];
375 if (acl_access_check(lid, cl))
378 /* if the member is not a direct member of the list, punt */
379 EXEC SQL SELECT COUNT(list_id) INTO :cnt FROM imembers
380 WHERE list_id = :lid AND member_id = :mid
381 AND member_type = :mtype AND direct = 1;
389 EXEC SQL DECLARE csr105 CURSOR FOR
390 SELECT list_id, ref_count FROM imembers
391 WHERE member_id = :lid AND member_type = 'LIST';
394 EXEC SQL OPEN csr105;
399 EXEC SQL FETCH csr105 INTO :id, :ref;
403 ancestors[acount++] = id;
404 if (acount >= MAXLISTDEPTH)
407 EXEC SQL CLOSE csr105;
410 if (acount >= MAXLISTDEPTH)
412 descendants[0] = mid;
417 if (!strcmp(mtype, "LIST"))
419 EXEC SQL DECLARE csr106 CURSOR FOR
420 SELECT member_id, member_type, ref_count FROM imembers
421 WHERE list_id = :mid;
424 EXEC SQL OPEN csr106;
429 EXEC SQL FETCH csr106 INTO :id, :dtype, :ref;
435 dtypes[dcount] = "LIST";
438 dtypes[dcount] = "USER";
441 dtypes[dcount] = "STRING";
444 dtypes[dcount] = "KERBEROS";
451 descendants[dcount++] = id;
452 if (dcount >= MAXLISTDEPTH)
455 EXEC SQL CLOSE csr106;
461 for (a = 0; a < acount; a++)
464 for (d = 0; d < dcount; d++)
466 mid = descendants[d];
468 if (mid == lid && !strcmp(mtype, "LIST"))
470 EXEC SQL SELECT ref_count INTO :cnt FROM imembers
471 WHERE list_id = :lid AND member_id = :mid AND member_type = :mtype;
472 ref = aref[a] * dref[d];
475 iargv[0] = (char *)lid;
477 iargv[2] = (char *)mid;
478 incremental_before(IMEMBERS_TABLE, 0, iargv);
479 EXEC SQL DELETE FROM imembers
480 WHERE list_id = :lid AND member_id = :mid
481 AND member_type= :mtype;
482 incremental_clear_after();
484 else if (a == 0 && d == 0)
486 EXEC SQL UPDATE imembers
487 SET ref_count = ref_count - :ref, direct = 0
488 WHERE list_id = :lid AND member_id = :mid
489 AND member_type = :mtype;
493 EXEC SQL UPDATE imembers
494 SET ref_count = ref_count - :ref
495 WHERE list_id = :lid AND member_id = :mid
496 AND member_type = :mtype;
500 lid = *(int *)argv[0];
503 EXEC SQL UPDATE list SET modtime = SYSDATE, modby = :who, modwith = :entity
504 WHERE list_id = :lid;
511 /* Don't allow someone to add someone to a list which is the acl of a
512 * query unless they're on the list acl, even if they're on the amtl
513 * query acl! Also, don't allow someone proxying to add someone to a
516 int acl_access_check(int list_id, client *cl)
518 EXEC SQL BEGIN DECLARE SECTION;
519 int c1, c2, lid = list_id, acl_id;
520 char acl_type[LIST_ACL_TYPE_SIZE];
521 EXEC SQL END DECLARE SECTION;
523 /* Check if the list is directly a capacl */
524 EXEC SQL SELECT COUNT(list_id) INTO :c1 FROM capacls WHERE list_id=:lid;
526 /* Check if the list is a member (direct or indirect) of a list that
528 EXEC SQL SELECT COUNT(l1.list_id) INTO :c2 FROM list l1, list l2,
529 imembers im, capacls c WHERE c.list_id = l2.list_id AND
530 im.list_id = l2.list_id AND im.member_type = 'LIST' AND
531 im.member_id = l1.list_id AND l1.list_id = :lid;
533 if (c1 == 0 && c2 == 0)
539 EXEC SQL SELECT acl_type, acl_id INTO :acl_type, :acl_id
540 FROM list WHERE list_id=:lid;
541 return !find_member(acl_type, acl_id, cl);
545 /* get_ace_use - given a type and a name, return a type and a name.
546 * The ace_type is one of "LIST", "USER", "RLIST", or "RUSER" in argv[0],
547 * and argv[1] will contain the ID of the entity in question. The R*
548 * types mean to recursively look at every containing list, not just
549 * when the object in question is a direct member. On return, the
550 * usage type will be one of LIST, SERVICE, FILESYS, QUOTA, QUERY, or ZEPHYR.
553 int get_ace_use(struct query *q, char *argv[], client *cl,
554 int (*action)(int, char *[], void *), void *actarg)
557 EXEC SQL BEGIN DECLARE SECTION;
560 EXEC SQL END DECLARE SECTION;
561 struct save_queue *sq;
564 aid = *(int *)argv[1];
565 if (!strcmp(atype, "LIST") || !strcmp(atype, "USER") ||
566 !strcmp(atype, "KERBEROS"))
567 return get_ace_internal(atype, aid, action, actarg);
570 if (!strcmp(atype, "RLIST"))
572 sq_save_data(sq, (void *)aid);
573 /* get all the list_id's of containing lists */
574 EXEC SQL DECLARE csr107 CURSOR FOR
575 SELECT list_id FROM imembers
576 WHERE member_type = 'LIST' AND member_id = :aid;
579 EXEC SQL OPEN csr107;
584 EXEC SQL FETCH csr107 INTO :listid;
587 sq_save_unique_data(sq, (void *)listid);
589 EXEC SQL CLOSE csr107;
590 /* now process each one */
591 while (sq_get_data(sq, &id))
593 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
598 if (!strcmp(atype, "RUSER"))
600 EXEC SQL DECLARE csr108 CURSOR FOR
601 SELECT list_id FROM imembers
602 WHERE member_type = 'USER' AND member_id = :aid;
605 EXEC SQL OPEN csr108;
610 EXEC SQL FETCH csr108 INTO :listid;
613 sq_save_data(sq, (void *)listid);
615 EXEC SQL CLOSE csr108;
616 /* now process each one */
617 while (sq_get_data(sq, &id))
619 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
622 if (get_ace_internal("USER", aid, action, actarg) == MR_SUCCESS)
626 if (!strcmp(atype, "RKERBEROS"))
628 EXEC SQL DECLARE csr109 CURSOR FOR
629 SELECT list_id FROM imembers
630 WHERE member_type = 'KERBEROS' AND member_id = :aid;
633 EXEC SQL OPEN csr109;
638 EXEC SQL FETCH csr109 INTO :listid;
641 sq_save_data(sq, (void *)listid);
643 EXEC SQL CLOSE csr109;
644 /* now process each one */
645 while (sq_get_data(sq, &id))
647 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
650 if (get_ace_internal("KERBEROS", aid, action, actarg) == MR_SUCCESS)
663 /* This looks up a single list or user for ace use. atype must be "USER"
664 * or "LIST", and aid is the ID of the corresponding object. This is used
665 * by get_ace_use above.
668 int get_ace_internal(char *atype, int aid,
669 int (*action)(int, char *[], void *), void *actarg)
673 EXEC SQL BEGIN DECLARE SECTION;
674 char name[LIST_NAME_SIZE], *type = atype;
676 EXEC SQL END DECLARE SECTION;
679 if (!strcmp(atype, "LIST"))
681 rargv[0] = "FILESYS";
682 EXEC SQL DECLARE csr110 CURSOR FOR
683 SELECT label FROM filesys
687 EXEC SQL OPEN csr110;
692 EXEC SQL FETCH csr110 INTO :name;
695 (*action)(2, rargv, actarg);
698 EXEC SQL CLOSE csr110;
701 EXEC SQL DECLARE csr111 CURSOR FOR
702 SELECT capability FROM capacls
706 EXEC SQL OPEN csr111;
711 EXEC SQL FETCH csr111 INTO :name;
714 (*action)(2, rargv, actarg);
717 EXEC SQL CLOSE csr111;
719 else if (!strcmp(atype, "USER"))
721 rargv[0] = "FILESYS";
722 EXEC SQL DECLARE csr112 CURSOR FOR
723 SELECT label FROM filesys
727 EXEC SQL OPEN csr112;
732 EXEC SQL FETCH csr112 INTO :name;
735 (*action)(2, rargv, actarg);
738 EXEC SQL CLOSE csr112;
742 EXEC SQL DECLARE csr113 CURSOR FOR
743 SELECT name FROM list
744 WHERE acl_type = :type AND acl_id = :id;
747 EXEC SQL OPEN csr113;
752 EXEC SQL FETCH csr113 INTO :name;
755 (*action)(2, rargv, actarg);
758 EXEC SQL CLOSE csr113;
760 rargv[0] = "SERVICE";
761 EXEC SQL DECLARE csr114 CURSOR FOR
762 SELECT name FROM servers
763 WHERE acl_type = :type AND acl_id = :id;
766 EXEC SQL OPEN csr114;
771 EXEC SQL FETCH csr114 INTO :name;
774 (*action)(2, rargv, actarg);
777 EXEC SQL CLOSE csr114;
779 rargv[0] = "HOSTACCESS";
780 EXEC SQL DECLARE csr115 CURSOR FOR
781 SELECT name FROM machine m, hostaccess ha
782 WHERE m.mach_id = ha.mach_id AND ha.acl_type = :type
786 EXEC SQL OPEN csr115;
791 EXEC SQL FETCH csr115 INTO :name;
794 (*action)(2, rargv, actarg);
797 EXEC SQL CLOSE csr115;
800 EXEC SQL DECLARE csr116 CURSOR FOR
801 SELECT class FROM zephyr z
802 WHERE z.xmt_type = :type AND z.xmt_id = :id
803 OR z.sub_type = :type AND z.sub_id = :id
804 OR z.iws_type = :type AND z.iws_id = :id
805 OR z.iui_type = :type AND z.iui_id = :id;
808 EXEC SQL OPEN csr116;
813 EXEC SQL FETCH csr116 INTO :name;
816 (*action)(2, rargv, actarg);
819 EXEC SQL CLOSE csr116;
827 /* get_lists_of_member - given a type and a name, return the name and flags
828 * of all of the lists of the given member. The member_type is one of
829 * "LIST", "USER", "STRING", "RLIST", "RUSER", or "RSTRING" in argv[0],
830 * and argv[1] will contain the ID of the entity in question. The R*
831 * types mean to recursively look at every containing list, not just
832 * when the object in question is a direct member.
835 int get_lists_of_member(struct query *q, char *argv[], client *cl,
836 int (*action)(int, char *[], void *), void *actarg)
838 int found = 0, direct = 1;
840 EXEC SQL BEGIN DECLARE SECTION;
843 char name[LIST_NAME_SIZE];
844 char active[5], public[5], hidden[5], maillist[5], grouplist[5];
845 EXEC SQL END DECLARE SECTION;
848 aid = *(int *)argv[1];
849 if (!strcmp(atype, "RLIST"))
854 if (!strcmp(atype, "RUSER"))
859 if (!strcmp(atype, "RSTRING"))
864 if (!strcmp(atype, "RKERBEROS"))
875 rargv[5] = grouplist;
878 EXEC SQL DECLARE csr117a CURSOR FOR
879 SELECT l.name, l.active, l.publicflg, l.hidden, l.maillist, l.grouplist
880 FROM list l, imembers im
881 WHERE l.list_id = im.list_id AND im.direct = 1
882 AND im.member_type = :atype AND im.member_id = :aid;
885 EXEC SQL OPEN csr117a;
890 EXEC SQL FETCH csr117a
891 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
894 (*action)(6, rargv, actarg);
897 EXEC SQL CLOSE csr117a;
901 EXEC SQL DECLARE csr117b CURSOR FOR
902 SELECT l.name, l.active, l.publicflg, l.hidden, l.maillist, l.grouplist
903 FROM list l, imembers im
904 WHERE l.list_id = im.list_id
905 AND im.member_type = :atype AND im.member_id = :aid;
908 EXEC SQL OPEN csr117b;
913 EXEC SQL FETCH csr117b
914 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
917 (*action)(6, rargv, actarg);
920 EXEC SQL CLOSE csr117b;
931 /* qualified_get_lists: passed "TRUE", "FALSE", or "DONTCARE" for each of
932 * the five flags associated with each list. It will return the name of
933 * each list that meets the quailifications. It does this by building a
934 * where clause based on the arguments, then doing a retrieve.
937 static char *lflags[5] = { "active", "publicflg", "hidden", "maillist", "grouplist" };
939 int qualified_get_lists(struct query *q, char *argv[], client *cl,
940 int (*action)(int, char *[], void *), void *actarg)
942 return qualified_get(q, argv, action, actarg, "l.list_id != 0",
943 "l", "name", lflags);
947 int get_members_of_list(struct query *q, char *argv[], client *cl,
948 int (*action)(int, char *[], void *), void *actarg)
950 EXEC SQL BEGIN DECLARE SECTION;
952 char member_name[MAX_FIELD_WIDTH], tag[STRINGS_STRING_SIZE];
953 EXEC SQL END DECLARE SECTION;
957 /* For gmol or gtml, only get direct members. For geml, get all. */
958 if (!strcmp(q->shortname, "geml"))
963 /* For gmol or geml, only return type and name. For gtml, return tag too. */
964 if (!strcmp(q->shortname, "gtml"))
969 list_id = *(int *)argv[0];
971 targv[1] = member_name;
975 EXEC SQL DECLARE csr119 CURSOR FOR
976 SELECT u.login, s.string FROM users u, imembers im, strings s
977 WHERE im.list_id = :list_id AND im.member_type = 'USER'
978 AND im.member_id = u.users_id AND im.direct > :direct
979 AND s.string_id = im.tag ORDER BY 1;
982 EXEC SQL OPEN csr119;
987 EXEC SQL FETCH csr119 INTO :member_name, :tag;
990 (*action)(targc, targv, actarg);
992 EXEC SQL CLOSE csr119;
997 EXEC SQL DECLARE csr120 CURSOR FOR
998 SELECT l.name, s.string FROM list l, imembers im, strings s
999 WHERE im.list_id = :list_id AND im.member_type = 'LIST'
1000 AND im.member_id = l.list_id AND im.direct > :direct
1001 AND s.string_id = im.tag ORDER BY 1;
1004 EXEC SQL OPEN csr120;
1009 EXEC SQL FETCH csr120 INTO :member_name, :tag;
1012 (*action)(targc, targv, actarg);
1014 EXEC SQL CLOSE csr120;
1018 targv[0] = "STRING";
1019 EXEC SQL DECLARE csr121 CURSOR FOR
1020 SELECT str.string, s.string FROM strings str, imembers im, strings s
1021 WHERE im.list_id = :list_id AND im.member_type = 'STRING'
1022 AND im.member_id = str.string_id AND im.direct > :direct
1023 AND s.string_id = im.tag ORDER BY 1;
1026 EXEC SQL OPEN csr121;
1031 EXEC SQL FETCH csr121 INTO :member_name, :tag;
1034 (*action)(targc, targv, actarg);
1036 EXEC SQL CLOSE csr121;
1040 targv[0] = "KERBEROS";
1041 EXEC SQL DECLARE csr122 CURSOR FOR
1042 SELECT str.string, s.string FROM strings str, imembers im, strings s
1043 WHERE im.list_id = :list_id AND im.member_type = 'KERBEROS'
1044 AND im.member_id = str.string_id AND im.direct > :direct
1045 AND s.string_id = im.tag ORDER BY 1;
1048 EXEC SQL OPEN csr122;
1053 EXEC SQL FETCH csr122 INTO :member_name, :tag;
1056 (*action)(targc, targv, actarg);
1058 EXEC SQL CLOSE csr122;
1066 /* count_members_of_list: this is a simple query, but it cannot be done
1067 * through the dispatch table.
1070 int count_members_of_list(struct query *q, char *argv[], client *cl,
1071 int (*action)(int, char *[], void *), void *actarg)
1073 EXEC SQL BEGIN DECLARE SECTION;
1075 EXEC SQL END DECLARE SECTION;
1076 char *rargv[1], countbuf[5];
1078 list = *(int *)argv[0];
1079 rargv[0] = countbuf;
1080 EXEC SQL SELECT count (*) INTO :ct FROM imembers
1081 WHERE list_id = :list AND direct = 1;
1084 sprintf(countbuf, "%d", ct);
1085 (*action)(1, rargv, actarg);
1090 /* qualified_get_server: passed "TRUE", "FALSE", or "DONTCARE" for each of
1091 * the three flags associated with each service. It will return the name of
1092 * each service that meets the quailifications. It does this by building a
1093 * where clause based on the arguments, then doing a retrieve.
1096 static char *sflags[3] = { "enable", "inprogress", "harderror" };
1098 int qualified_get_server(struct query *q, char *argv[], client *cl,
1099 int (*action)(int, char *[], void *), void *actarg)
1101 return qualified_get(q, argv, action, actarg, "s.name is not null",
1102 "s", "name", sflags);
1103 /* of course, name will never be null, but we need something there
1104 to make qualified_get happy */
1108 /* generic qualified get routine, used by qualified_get_lists,
1109 * qualified_get_server, and qualified_get_serverhost.
1111 * start - a simple where clause, must not be empty
1112 * range - the name of the range variable
1113 * field - the field to return
1114 * flags - an array of strings, names of the flag variables
1117 int qualified_get(struct query *q, char *argv[],
1118 int (*action)(int, char *[], void *), void *actarg,
1119 char *start, char *range, char *field, char *flags[])
1125 strcpy(qual, start);
1126 for (i = 0; i < q->argc; i++)
1128 if (!strcmp(argv[i], "TRUE"))
1130 sprintf(buf, " AND %s.%s != 0", range, flags[i]);
1133 else if (!strcmp(argv[i], "FALSE"))
1135 sprintf(buf, " AND %s.%s = 0", range, flags[i]);
1140 sprintf(stmt_buf, "SELECT %s.%s FROM %s %s WHERE %s", range, field,
1141 table_name[q->rtable], range, qual);
1142 return do_for_all_rows(stmt_buf, 1, action, actarg);
1146 /* qualified_get_serverhost: passed "TRUE", "FALSE", or "DONTCARE" for each of
1147 * the five flags associated with each serverhost. It will return the name of
1148 * each service and host that meets the quailifications. It does this by
1149 * building a where clause based on the arguments, then doing a retrieve.
1152 static char *shflags[6] = { "service", "enable", "override", "success",
1153 "inprogress", "hosterror" };
1155 int qualified_get_serverhost(struct query *q, char *argv[], client *cl,
1156 int (*action)(int, char *[], void *),
1159 char qual[256], buf[32];
1162 sprintf(qual, "m.mach_id = sh.mach_id AND sh.service = UPPER('%s')",
1164 for (i = 1; i < q->argc; i++)
1166 if (!strcmp(argv[i], "TRUE"))
1168 sprintf(buf, " AND sh.%s != 0", shflags[i]);
1171 else if (!strcmp(argv[i], "FALSE"))
1173 sprintf(buf, " AND sh.%s = 0", shflags[i]);
1178 sprintf(stmt_buf, "SELECT sh.service, m.name FROM serverhosts sh, "
1179 "machine m WHERE %s", qual);
1180 return do_for_all_rows(stmt_buf, 2, action, actarg);
1184 /* register_user - change user's login name and allocate a pobox, group,
1185 * filesystem, and quota for them. The user's status must start out as 0,
1186 * and is left as 2. Arguments are: user's UID, new login name, and
1187 * pobox type ("POP" or numeric = POP, "IMAP" = IMAP)
1190 int register_user(struct query *q, char **argv, client *cl)
1192 EXEC SQL BEGIN DECLARE SECTION;
1193 char *login, *entity;
1194 char directory[FILESYS_NAME_SIZE], machname[MACHINE_NAME_SIZE];
1195 char dir[NFSPHYS_DIR_SIZE], *potype;
1196 int who, rowcount, mid, uid, users_id;
1197 int ostatus, nstatus, fsidval, popid;
1199 static int m_id, def_quota, def_imap_quota, list_id;
1200 EXEC SQL END DECLARE SECTION;
1201 char buffer[256], *aargv[3];
1205 EXEC SQL SELECT list_id INTO :list_id FROM list
1206 WHERE name = 'wheel';
1208 EXEC SQL SELECT mach_id INTO :m_id FROM machine
1209 WHERE name = 'ATHENA.MIT.EDU';
1211 EXEC SQL SELECT value INTO :def_quota FROM numvalues
1212 WHERE name = 'def_quota';
1213 if (sqlca.sqlerrd[2] != 1)
1216 EXEC SQL SELECT value INTO :def_imap_quota FROM numvalues
1217 WHERE name = 'def_imap_quota';
1218 if (sqlca.sqlerrd[2] != 1)
1222 entity = cl->entity;
1223 who = cl->client_id;
1225 uid = atoi(argv[0]);
1230 EXEC SQL SELECT users_id, status INTO :users_id, :ostatus
1232 WHERE unix_uid = :uid AND (status = 0 OR status = 5 OR status = 6);
1234 if (sqlca.sqlerrd[2] == 0)
1236 if (sqlca.sqlerrd[2] > 1)
1237 return MR_NOT_UNIQUE;
1239 /* check new login name */
1240 EXEC SQL SELECT COUNT(login) INTO :rowcount FROM users
1241 WHERE login = :login AND users_id != :users_id;
1246 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM list
1247 WHERE LOWER(name) = :login;
1252 EXEC SQL SELECT COUNT(label) INTO :rowcount FROM filesys
1253 WHERE label = :login OR label = :login || '.po';
1258 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM alias
1259 WHERE ( name = :login OR name = :login || '.po' )
1260 AND type = 'FILESYS';
1265 com_err(whoami, 0, "login name OK");
1267 /* choose type and location for pobox */
1268 if (!strcmp(potype, "IMAP"))
1270 /* Select all IMAP nfsphys entries in order of increasing
1271 * (allocated - partsize). Since partsize will always be larger
1272 * than allocated, this number will always be negative, and
1273 * the first row fetched (which is the only one we take)
1274 * will be the partition with the most unallocated space.
1276 EXEC SQL DECLARE csr_rusr_imap CURSOR FOR
1277 SELECT np.allocated - np.partsize, np.nfsphys_id, np.mach_id,
1278 np.dir, m.name FROM serverhosts sh, nfsphys np, machine m
1279 WHERE sh.service = 'POSTOFFICE' AND sh.mach_id = np.mach_id
1280 AND m.mach_id = np.mach_id
1284 EXEC SQL OPEN csr_rusr_imap;
1287 EXEC SQL FETCH csr_rusr_imap INTO :tmp, :npid, :mid, :dir, :machname;
1288 if (sqlca.sqlerrd[2] == 0 || tmp + def_imap_quota > 0)
1290 EXEC SQL CLOSE csr_rusr_imap;
1297 EXEC SQL CLOSE csr_rusr_imap;
1302 /* create filesystem */
1303 if (set_next_object_id("filsys_id", FILESYS_TABLE, 0))
1305 incremental_clear_before();
1307 EXEC SQL SELECT value INTO :popid FROM numvalues
1308 WHERE numvalues.name = 'filsys_id';
1309 EXEC SQL INSERT INTO filesys
1310 (filsys_id, phys_id, label, type, mach_id, name,
1311 mount, rwaccess, comments, owner, owners, createflg,
1312 lockertype, modtime, modby, modwith)
1314 (:popid, :npid, :login || '.po', 'IMAP', :mid, :dir,
1315 CHR(0), 'w', 'IMAP box', :users_id, :list_id, 1,
1316 'IMAP', SYSDATE, :who, :entity);
1320 if (sqlca.sqlerrd[2] != 1)
1322 sprintf(buffer, "fs.filsys_id = %d", popid);
1323 incremental_after(FILESYS_TABLE, buffer, 0);
1326 incremental_clear_before();
1327 EXEC SQL INSERT INTO quota
1328 (entity_id, filsys_id, type, quota, phys_id, modtime, modby, modwith)
1329 VALUES (:users_id, :popid, 'USER', :def_imap_quota, :npid,
1330 SYSDATE, :who, :entity);
1333 if (sqlca.sqlerrd[2] != 1)
1338 sprintf(buffer, "q.entity_id = %d and q.filsys_id = %d and "
1339 "q.type = 'USER'", users_id, popid);
1340 incremental_after(QUOTA_TABLE, buffer, aargv);
1344 EXEC SQL UPDATE nfsphys SET allocated = allocated + :def_imap_quota
1345 WHERE nfsphys_id = :npid;
1347 EXEC SQL UPDATE users SET potype = 'IMAP', imap_id = :popid
1348 WHERE users_id = :users_id;
1349 com_err(whoami, 0, "pobox set to IMAP:%s:%s", strtrim(machname),
1356 EXEC SQL DECLARE csr130 CURSOR FOR
1357 SELECT sh.mach_id, m.name FROM serverhosts sh, machine m
1358 WHERE sh.service = 'POP' AND sh.mach_id = m.mach_id
1359 AND sh.value2 - sh.value1 =
1360 (SELECT MAX(value2 - value1) FROM serverhosts WHERE service = 'POP');
1363 EXEC SQL OPEN csr130;
1366 EXEC SQL FETCH csr130 INTO :popid, :machname;
1367 if (sqlca.sqlerrd[2] == 0)
1369 EXEC SQL CLOSE csr130;
1376 EXEC SQL CLOSE csr130;
1381 EXEC SQL UPDATE users SET potype = 'POP', pop_id = :popid
1382 WHERE users_id = :users_id;
1383 com_err(whoami, 0, "pobox set to POP:%s", strtrim(machname));
1386 /* change login name, set pobox */
1387 sprintf(buffer, "u.users_id = %d", users_id);
1388 incremental_before(USERS_TABLE, buffer, 0);
1390 if (ostatus == 5 || ostatus == 6)
1392 EXEC SQL UPDATE users SET login = :login, status = :nstatus,
1393 modtime = SYSDATE, modby = :who, modwith = :entity,
1394 pmodtime = SYSDATE, pmodby = :who, pmodwith = :entity
1395 WHERE users_id = :users_id;
1399 if (sqlca.sqlerrd[2] != 1)
1401 set_pop_usage(mid, 1);
1402 com_err(whoami, 0, "set login name to %s", login);
1403 incremental_after(USERS_TABLE, buffer, 0);
1405 /* create filesystem */
1406 if (set_next_object_id("filsys_id", FILESYS_TABLE, 0))
1408 incremental_clear_before();
1409 if (islower(login[0]) && islower(login[1]))
1411 sprintf(directory, "/afs/athena.mit.edu/user/%c/%c/%s",
1412 login[0], login[1], login);
1415 sprintf(directory, "/afs/athena.mit.edu/user/other/%s", login);
1417 EXEC SQL SELECT value INTO :fsidval FROM numvalues
1418 WHERE numvalues.name = 'filsys_id';
1419 EXEC SQL INSERT INTO filesys
1420 (filsys_id, phys_id, label, type, mach_id, name,
1421 mount, rwaccess, comments, owner, owners, createflg,
1422 lockertype, modtime, modby, modwith)
1424 (:fsidval, 0, :login, 'AFS', :m_id, :directory,
1425 '/mit/' || :login, 'w', 'User Locker', :users_id, :list_id, 1,
1426 'HOMEDIR', SYSDATE, :who, :entity);
1430 if (sqlca.sqlerrd[2] != 1)
1432 sprintf(buffer, "fs.filsys_id = %d", fsidval);
1433 incremental_after(FILESYS_TABLE, buffer, 0);
1436 incremental_clear_before();
1437 EXEC SQL INSERT INTO quota
1438 (entity_id, filsys_id, type, quota, phys_id, modtime, modby, modwith)
1439 VALUES (0, :fsidval, 'ANY', :def_quota, 0, SYSDATE, :who, :entity);
1442 if (sqlca.sqlerrd[2] != 1)
1447 sprintf(buffer, "q.entity_id = 0 and q.filsys_id = %d and q.type = 'ANY'",
1449 incremental_after(QUOTA_TABLE, buffer, aargv);
1450 com_err(whoami, 0, "quota of %d assigned", def_quota);
1454 cache_entry(login, USERS_TABLE, users_id);
1456 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
1457 WHERE table_name = 'users';
1458 EXEC SQL UPDATE tblstats SET appends = appends + 1, modtime = SYSDATE
1459 WHERE table_name = 'filesys' OR table_name = 'quota';
1467 /** set_pop_usage - incr/decr usage count for pop server in serverhosts talbe
1471 ** delta (will be +/- 1)
1474 ** - incr/decr value field in serverhosts table for pop/mach_id
1478 int set_pop_usage(id, cnt)
1481 EXEC SQL BEGIN DECLARE SECTION;
1482 int iid = id, icnt = cnt;
1483 EXEC SQL END DECLARE SECTION;
1485 EXEC SQL UPDATE serverhosts SET value1 = value1 + :icnt
1486 WHERE serverhosts.service = 'POP' AND serverhosts.mach_id = :iid;
1494 int do_user_reservation(struct query *q, char *argv[], client *cl)
1496 EXEC SQL BEGIN DECLARE SECTION;
1497 char resv[USERS_RESERVATIONS_SIZE];
1498 char *trans, name[ALIAS_NAME_SIZE];
1500 EXEC SQL END DECLARE SECTION;
1502 uid = *(int *)argv[0];
1505 EXEC SQL SELECT UPPER(name) INTO :name FROM alias
1506 WHERE type = 'RESERVE' AND LOWER(trans) = LOWER(:trans);
1509 if (sqlca.sqlerrd[2] != 1)
1513 EXEC SQL SELECT reservations INTO :resv FROM users
1514 WHERE users_id = :uid;
1519 if (!strcmp(q->shortname, "aurv"))
1521 if (strchr(resv, *name))
1523 if (strlen(resv) == USERS_RESERVATIONS_SIZE - 1)
1524 return MR_ARG_TOO_LONG;
1530 char *p = strchr(resv, *name);
1533 memmove(p, p + 1, strlen(p));
1536 EXEC SQL UPDATE users SET reservations = NVL(:resv, CHR(0))
1537 WHERE users_id = :uid;
1541 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
1542 WHERE table_name = 'users';
1543 return set_modtime_by_id(q, argv, cl);
1546 int get_user_reservations(struct query *q, char **argv, client *cl,
1547 int (*action)(int, char *[], void *), void *actarg)
1549 EXEC SQL BEGIN DECLARE SECTION;
1550 char resv[USERS_RESERVATIONS_SIZE], *p;
1551 char trans[ALIAS_TRANS_SIZE], name[2] = { 0, 0 };
1553 EXEC SQL END DECLARE SECTION;
1556 uid = *(int *)argv[0];
1558 EXEC SQL SELECT reservations INTO :resv FROM users
1559 WHERE users_id = :uid;
1565 while (*p && !isspace(*p))
1567 name[0] = toupper(*p);
1568 EXEC SQL SELECT trans INTO :trans FROM alias
1569 WHERE type = 'RESERVE' AND UPPER(name) = :name;
1572 if (sqlca.sqlerrd[2] != 1)
1573 sprintf(trans, "Unknown (%s)", name);
1574 (*action)(1, targv, actarg);
1580 int get_user_by_reservation(struct query *q, char **argv, client *cl,
1581 int (*action)(int, char *[], void *), void *actarg)
1583 EXEC SQL BEGIN DECLARE SECTION;
1584 char resv[USERS_RESERVATIONS_SIZE], login[USERS_LOGIN_SIZE];
1585 char *trans, name[ALIAS_NAME_SIZE];
1587 EXEC SQL END DECLARE SECTION;
1592 EXEC SQL SELECT UPPER(name) INTO :name FROM alias
1593 WHERE type = 'RESERVE' AND LOWER(trans) = LOWER(:trans);
1596 if (sqlca.sqlerrd[2] != 1)
1600 EXEC SQL DECLARE csr_gubr CURSOR FOR
1601 SELECT login FROM users WHERE reservations LIKE '%' || :name || '%';
1602 EXEC SQL OPEN csr_gubr;
1609 EXEC SQL FETCH csr_gubr INTO :login;
1612 (*action)(1, targv, actarg);
1614 EXEC SQL CLOSE csr_gubr;