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 set_pop_usage(id, -1);
65 if (!strcmp(argv[1], "POP"))
67 status = name_to_id(box, MACHINE_TABLE, &id);
68 if (status == MR_NO_MATCH)
72 EXEC SQL UPDATE users SET potype = 'POP', pop_id = :id, imap_id = 0
73 WHERE users_id = :user;
76 else if (!strcmp(argv[1], "SMTP"))
78 if (strchr(box, '/') || strchr(box, '|'))
80 status = name_to_id(box, STRINGS_TABLE, &id);
81 if (status == MR_NO_MATCH)
85 EXEC SQL UPDATE users SET potype = 'SMTP', box_id = :id
86 WHERE users_id = :user;
88 else if (!strcmp(argv[1], "IMAP"))
90 EXEC SQL SELECT filsys_id INTO :id FROM filesys
91 WHERE label = :box AND type = 'IMAP';
94 EXEC SQL UPDATE users SET potype = 'IMAP', imap_id = :id, pop_id = 0
95 WHERE users_id = :user;
97 else /* argv[1] == "NONE" */
99 EXEC SQL UPDATE users SET potype = 'NONE'
100 WHERE users_id = :user;
103 set_pobox_modtime(q, argv, cl);
104 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
105 WHERE table_name = 'users';
111 /* set_pobox_pop: Revert to existing POP or IMAP pobox.
112 * Also take care of keeping track of the post office usage.
114 int set_pobox_pop(struct query *q, char **argv, client *cl)
116 EXEC SQL BEGIN DECLARE SECTION;
117 int id, pid, iid, mid;
118 char type[USERS_POTYPE_SIZE];
119 EXEC SQL END DECLARE SECTION;
121 id = *(int *)argv[0];
122 EXEC SQL SELECT potype, pop_id, imap_id INTO :type, :pid, :iid
123 FROM users WHERE users_id = :id;
124 if (sqlca.sqlerrd[2] == 0 || (pid == 0 && iid == 0))
129 EXEC SQL SELECT mach_id INTO :mid FROM machine
130 WHERE mach_id = :pid;
131 if (sqlca.sqlerrd[2] == 0)
133 EXEC SQL UPDATE users SET potype = 'POP' WHERE users_id = :id;
134 if (strcmp(strtrim(type), "POP"))
135 set_pop_usage(mid, 1);
139 EXEC SQL SELECT filsys_id INTO :mid FROM filesys
140 WHERE filsys_id = :iid;
141 if (sqlca.sqlerrd[2] == 0)
143 EXEC SQL UPDATE users SET potype = 'IMAP' WHERE users_id = :id;
146 set_pobox_modtime(q, argv, cl);
147 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
148 WHERE table_name = 'users';
155 /* Add_member_to_list: do list flattening as we go! MAXLISTDEPTH is
156 * how many different ancestors a member is allowed to have.
159 #define MAXLISTDEPTH 1024
161 int add_member_to_list(struct query *q, char **argv, client *cl)
163 EXEC SQL BEGIN DECLARE SECTION;
164 int id, lid, mid, tag, error, who, ref, rowcnt;
165 char *mtype, dtype[IMEMBERS_MEMBER_TYPE_SIZE], *entity;
166 EXEC SQL END DECLARE SECTION;
167 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
168 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
170 char *dtypes[MAXLISTDEPTH];
171 char *iargv[3], *buf;
173 lid = *(int *)argv[0];
175 mid = *(int *)argv[2];
176 tag = !strcmp(q->shortname, "atml") ? *(int *)argv[3] : 0;
178 if (acl_access_check(lid, cl))
181 /* if the member is already a direct member of the list, punt */
182 EXEC SQL SELECT COUNT(list_id) INTO :rowcnt FROM imembers
183 WHERE list_id = :lid AND member_id = :mid
184 AND member_type = :mtype AND direct = 1;
187 if (!strcasecmp(mtype, "STRING"))
190 status = id_to_name(mid, STRINGS_TABLE, &buf);
193 if (strchr(buf, '/') || strchr(buf, '|'))
204 EXEC SQL DECLARE csr103 CURSOR FOR
205 SELECT list_id, ref_count FROM imembers
206 WHERE member_id = :lid AND member_type = 'LIST';
209 EXEC SQL OPEN csr103;
214 EXEC SQL FETCH csr103 INTO :id, :ref;
218 ancestors[acount++] = id;
219 if (acount >= MAXLISTDEPTH)
222 EXEC SQL CLOSE csr103;
225 if (acount >= MAXLISTDEPTH)
227 descendants[0] = mid;
232 if (!strcmp(mtype, "LIST"))
234 EXEC SQL DECLARE csr104 CURSOR FOR
235 SELECT member_id, member_type, ref_count
237 WHERE list_id = :mid;
240 EXEC SQL OPEN csr104;
245 EXEC SQL FETCH csr104 INTO :id, :dtype, :ref;
251 dtypes[dcount] = "LIST";
254 dtypes[dcount] = "USER";
257 dtypes[dcount] = "STRING";
260 dtypes[dcount] = "KERBEROS";
267 descendants[dcount++] = id;
268 if (dcount >= MAXLISTDEPTH)
274 EXEC SQL CLOSE csr104;
280 for (a = 0; a < acount; a++)
283 for (d = 0; d < dcount; d++)
285 mid = descendants[d];
287 if (mid == lid && !strcmp(mtype, "LIST"))
289 EXEC SQL SELECT COUNT(ref_count) INTO :rowcnt
291 WHERE list_id = :lid AND member_id = :mid
292 AND member_type = :mtype;
293 ref = aref[a] * dref[d];
296 if (a == 0 && d == 0)
298 EXEC SQL UPDATE imembers
299 SET ref_count = ref_count + :ref, direct = 1, tag = :tag
300 WHERE list_id = :lid AND member_id = :mid
301 AND member_type = :mtype;
305 EXEC SQL UPDATE imembers
306 SET ref_count = ref_count + :ref
307 WHERE list_id = :lid AND member_id = :mid
308 AND member_type = :mtype;
313 incremental_clear_before();
314 if (a == 0 && d == 0)
316 EXEC SQL INSERT INTO imembers
317 (list_id, member_type, member_id, tag, direct, ref_count)
318 VALUES (:lid, :mtype, :mid, :tag, 1, 1);
322 EXEC SQL INSERT INTO imembers
323 (list_id, member_type, member_id, tag, direct, ref_count)
324 VALUES (:lid, :mtype, :mid, :tag, 0, 1);
326 iargv[0] = (char *)lid;
328 iargv[2] = (char *)mid;
329 incremental_after(IMEMBERS_TABLE, 0, iargv);
333 lid = *(int *)argv[0];
337 SET modtime = SYSDATE, modby = :who, modwith = :entity
338 WHERE list_id = :lid;
345 /* Delete_member_from_list: do list flattening as we go!
348 int delete_member_from_list(struct query *q, char **argv, client *cl)
350 EXEC SQL BEGIN DECLARE SECTION;
351 int id, lid, mid, cnt, error, who, ref;
352 char *mtype, dtype[IMEMBERS_MEMBER_TYPE_SIZE], *entity;
353 EXEC SQL END DECLARE SECTION;
354 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
355 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
356 char *dtypes[MAXLISTDEPTH];
359 lid = *(int *)argv[0];
361 mid = *(int *)argv[2];
363 if (acl_access_check(lid, cl))
366 /* if the member is not a direct member of the list, punt */
367 EXEC SQL SELECT COUNT(list_id) INTO :cnt FROM imembers
368 WHERE list_id = :lid AND member_id = :mid
369 AND member_type = :mtype AND direct = 1;
377 EXEC SQL DECLARE csr105 CURSOR FOR
378 SELECT list_id, ref_count FROM imembers
379 WHERE member_id = :lid AND member_type = 'LIST';
382 EXEC SQL OPEN csr105;
387 EXEC SQL FETCH csr105 INTO :id, :ref;
391 ancestors[acount++] = id;
392 if (acount >= MAXLISTDEPTH)
395 EXEC SQL CLOSE csr105;
398 if (acount >= MAXLISTDEPTH)
400 descendants[0] = mid;
405 if (!strcmp(mtype, "LIST"))
407 EXEC SQL DECLARE csr106 CURSOR FOR
408 SELECT member_id, member_type, ref_count FROM imembers
409 WHERE list_id = :mid;
412 EXEC SQL OPEN csr106;
417 EXEC SQL FETCH csr106 INTO :id, :dtype, :ref;
423 dtypes[dcount] = "LIST";
426 dtypes[dcount] = "USER";
429 dtypes[dcount] = "STRING";
432 dtypes[dcount] = "KERBEROS";
439 descendants[dcount++] = id;
440 if (dcount >= MAXLISTDEPTH)
443 EXEC SQL CLOSE csr106;
449 for (a = 0; a < acount; a++)
452 for (d = 0; d < dcount; d++)
454 mid = descendants[d];
456 if (mid == lid && !strcmp(mtype, "LIST"))
458 EXEC SQL SELECT ref_count INTO :cnt FROM imembers
459 WHERE list_id = :lid AND member_id = :mid AND member_type = :mtype;
460 ref = aref[a] * dref[d];
463 iargv[0] = (char *)lid;
465 iargv[2] = (char *)mid;
466 incremental_before(IMEMBERS_TABLE, 0, iargv);
467 EXEC SQL DELETE FROM imembers
468 WHERE list_id = :lid AND member_id = :mid
469 AND member_type= :mtype;
470 incremental_clear_after();
472 else if (a == 0 && d == 0)
474 EXEC SQL UPDATE imembers
475 SET ref_count = ref_count - :ref, direct = 0
476 WHERE list_id = :lid AND member_id = :mid
477 AND member_type = :mtype;
481 EXEC SQL UPDATE imembers
482 SET ref_count = ref_count - :ref
483 WHERE list_id = :lid AND member_id = :mid
484 AND member_type = :mtype;
488 lid = *(int *)argv[0];
491 EXEC SQL UPDATE list SET modtime = SYSDATE, modby = :who, modwith = :entity
492 WHERE list_id = :lid;
499 /* Don't allow someone to add someone to a list which is the acl of a
500 * query unless they're on the list acl, even if they're on the amtl
501 * query acl! Also, don't allow someone proxying to add someone to a
504 int acl_access_check(int list_id, client *cl)
506 EXEC SQL BEGIN DECLARE SECTION;
507 int c1, c2, lid = list_id, acl_id;
508 char acl_type[LIST_ACL_TYPE_SIZE];
509 EXEC SQL END DECLARE SECTION;
511 /* Check if the list is directly a capacl */
512 EXEC SQL SELECT COUNT(list_id) INTO :c1 FROM capacls WHERE list_id=:lid;
514 /* Check if the list is a member (direct or indirect) of a list that
516 EXEC SQL SELECT COUNT(l1.list_id) INTO :c2 FROM list l1, list l2,
517 imembers im, capacls c WHERE c.list_id = l2.list_id AND
518 im.list_id = l2.list_id AND im.member_type = 'LIST' AND
519 im.member_id = l1.list_id AND l1.list_id = :lid;
521 if (c1 == 0 && c2 == 0)
527 EXEC SQL SELECT acl_type, acl_id INTO :acl_type, :acl_id
528 FROM list WHERE list_id=:lid;
529 return !find_member(acl_type, acl_id, cl);
533 /* get_ace_use - given a type and a name, return a type and a name.
534 * The ace_type is one of "LIST", "USER", "RLIST", or "RUSER" in argv[0],
535 * and argv[1] will contain the ID of the entity in question. The R*
536 * types mean to recursively look at every containing list, not just
537 * when the object in question is a direct member. On return, the
538 * usage type will be one of LIST, SERVICE, FILESYS, QUOTA, QUERY, or ZEPHYR.
541 int get_ace_use(struct query *q, char *argv[], client *cl,
542 int (*action)(int, char *[], void *), void *actarg)
545 EXEC SQL BEGIN DECLARE SECTION;
548 EXEC SQL END DECLARE SECTION;
549 struct save_queue *sq;
552 aid = *(int *)argv[1];
553 if (!strcmp(atype, "LIST") || !strcmp(atype, "USER") ||
554 !strcmp(atype, "KERBEROS"))
555 return get_ace_internal(atype, aid, action, actarg);
558 if (!strcmp(atype, "RLIST"))
560 sq_save_data(sq, (void *)aid);
561 /* get all the list_id's of containing lists */
562 EXEC SQL DECLARE csr107 CURSOR FOR
563 SELECT list_id FROM imembers
564 WHERE member_type = 'LIST' AND member_id = :aid;
567 EXEC SQL OPEN csr107;
572 EXEC SQL FETCH csr107 INTO :listid;
575 sq_save_unique_data(sq, (void *)listid);
577 EXEC SQL CLOSE csr107;
578 /* now process each one */
579 while (sq_get_data(sq, &id))
581 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
586 if (!strcmp(atype, "RUSER"))
588 EXEC SQL DECLARE csr108 CURSOR FOR
589 SELECT list_id FROM imembers
590 WHERE member_type = 'USER' AND member_id = :aid;
593 EXEC SQL OPEN csr108;
598 EXEC SQL FETCH csr108 INTO :listid;
601 sq_save_data(sq, (void *)listid);
603 EXEC SQL CLOSE csr108;
604 /* now process each one */
605 while (sq_get_data(sq, &id))
607 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
610 if (get_ace_internal("USER", aid, action, actarg) == MR_SUCCESS)
614 if (!strcmp(atype, "RKERBEROS"))
616 EXEC SQL DECLARE csr109 CURSOR FOR
617 SELECT list_id FROM imembers
618 WHERE member_type = 'KERBEROS' AND member_id = :aid;
621 EXEC SQL OPEN csr109;
626 EXEC SQL FETCH csr109 INTO :listid;
629 sq_save_data(sq, (void *)listid);
631 EXEC SQL CLOSE csr109;
632 /* now process each one */
633 while (sq_get_data(sq, &id))
635 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
638 if (get_ace_internal("KERBEROS", aid, action, actarg) == MR_SUCCESS)
651 /* This looks up a single list or user for ace use. atype must be "USER"
652 * or "LIST", and aid is the ID of the corresponding object. This is used
653 * by get_ace_use above.
656 int get_ace_internal(char *atype, int aid,
657 int (*action)(int, char *[], void *), void *actarg)
661 EXEC SQL BEGIN DECLARE SECTION;
662 char name[LIST_NAME_SIZE], *type = atype;
664 EXEC SQL END DECLARE SECTION;
667 if (!strcmp(atype, "LIST"))
669 rargv[0] = "FILESYS";
670 EXEC SQL DECLARE csr110 CURSOR FOR
671 SELECT label FROM filesys
675 EXEC SQL OPEN csr110;
680 EXEC SQL FETCH csr110 INTO :name;
683 (*action)(2, rargv, actarg);
686 EXEC SQL CLOSE csr110;
689 EXEC SQL DECLARE csr111 CURSOR FOR
690 SELECT capability FROM capacls
694 EXEC SQL OPEN csr111;
699 EXEC SQL FETCH csr111 INTO :name;
702 (*action)(2, rargv, actarg);
705 EXEC SQL CLOSE csr111;
707 else if (!strcmp(atype, "USER"))
709 rargv[0] = "FILESYS";
710 EXEC SQL DECLARE csr112 CURSOR FOR
711 SELECT label FROM filesys
715 EXEC SQL OPEN csr112;
720 EXEC SQL FETCH csr112 INTO :name;
723 (*action)(2, rargv, actarg);
726 EXEC SQL CLOSE csr112;
730 EXEC SQL DECLARE csr113 CURSOR FOR
731 SELECT name FROM list
732 WHERE acl_type = :type AND acl_id = :id;
735 EXEC SQL OPEN csr113;
740 EXEC SQL FETCH csr113 INTO :name;
743 (*action)(2, rargv, actarg);
746 EXEC SQL CLOSE csr113;
748 rargv[0] = "SERVICE";
749 EXEC SQL DECLARE csr114 CURSOR FOR
750 SELECT name FROM servers
751 WHERE acl_type = :type AND acl_id = :id;
754 EXEC SQL OPEN csr114;
759 EXEC SQL FETCH csr114 INTO :name;
762 (*action)(2, rargv, actarg);
765 EXEC SQL CLOSE csr114;
767 rargv[0] = "HOSTACCESS";
768 EXEC SQL DECLARE csr115 CURSOR FOR
769 SELECT name FROM machine m, hostaccess ha
770 WHERE m.mach_id = ha.mach_id AND ha.acl_type = :type
774 EXEC SQL OPEN csr115;
779 EXEC SQL FETCH csr115 INTO :name;
782 (*action)(2, rargv, actarg);
785 EXEC SQL CLOSE csr115;
788 EXEC SQL DECLARE csr116 CURSOR FOR
789 SELECT class FROM zephyr z
790 WHERE z.xmt_type = :type AND z.xmt_id = :id
791 OR z.sub_type = :type AND z.sub_id = :id
792 OR z.iws_type = :type AND z.iws_id = :id
793 OR z.iui_type = :type AND z.iui_id = :id;
796 EXEC SQL OPEN csr116;
801 EXEC SQL FETCH csr116 INTO :name;
804 (*action)(2, rargv, actarg);
807 EXEC SQL CLOSE csr116;
815 /* get_lists_of_member - given a type and a name, return the name and flags
816 * of all of the lists of the given member. The member_type is one of
817 * "LIST", "USER", "STRING", "RLIST", "RUSER", or "RSTRING" in argv[0],
818 * and argv[1] will contain the ID of the entity in question. The R*
819 * types mean to recursively look at every containing list, not just
820 * when the object in question is a direct member.
823 int get_lists_of_member(struct query *q, char *argv[], client *cl,
824 int (*action)(int, char *[], void *), void *actarg)
826 int found = 0, direct = 1;
828 EXEC SQL BEGIN DECLARE SECTION;
831 char name[LIST_NAME_SIZE];
832 char active[5], public[5], hidden[5], maillist[5], grouplist[5];
833 EXEC SQL END DECLARE SECTION;
836 aid = *(int *)argv[1];
837 if (!strcmp(atype, "RLIST"))
842 if (!strcmp(atype, "RUSER"))
847 if (!strcmp(atype, "RSTRING"))
852 if (!strcmp(atype, "RKERBEROS"))
863 rargv[5] = grouplist;
866 EXEC SQL DECLARE csr117a CURSOR FOR
867 SELECT l.name, l.active, l.publicflg, l.hidden, l.maillist, l.grouplist
868 FROM list l, imembers im
869 WHERE l.list_id = im.list_id AND im.direct = 1
870 AND im.member_type = :atype AND im.member_id = :aid;
873 EXEC SQL OPEN csr117a;
878 EXEC SQL FETCH csr117a
879 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
882 (*action)(6, rargv, actarg);
885 EXEC SQL CLOSE csr117a;
889 EXEC SQL DECLARE csr117b CURSOR FOR
890 SELECT l.name, l.active, l.publicflg, l.hidden, l.maillist, l.grouplist
891 FROM list l, imembers im
892 WHERE l.list_id = im.list_id
893 AND im.member_type = :atype AND im.member_id = :aid;
896 EXEC SQL OPEN csr117b;
901 EXEC SQL FETCH csr117b
902 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
905 (*action)(6, rargv, actarg);
908 EXEC SQL CLOSE csr117b;
919 /* qualified_get_lists: passed "TRUE", "FALSE", or "DONTCARE" for each of
920 * the five flags associated with each list. It will return the name of
921 * each list that meets the quailifications. It does this by building a
922 * where clause based on the arguments, then doing a retrieve.
925 static char *lflags[5] = { "active", "publicflg", "hidden", "maillist", "grouplist" };
927 int qualified_get_lists(struct query *q, char *argv[], client *cl,
928 int (*action)(int, char *[], void *), void *actarg)
930 return qualified_get(q, argv, action, actarg, "l.list_id != 0",
931 "l", "name", lflags);
935 int get_members_of_list(struct query *q, char *argv[], client *cl,
936 int (*action)(int, char *[], void *), void *actarg)
938 EXEC SQL BEGIN DECLARE SECTION;
940 char member_name[MAX_FIELD_WIDTH], tag[STRINGS_STRING_SIZE];
941 EXEC SQL END DECLARE SECTION;
945 /* For gmol or gtml, only get direct members. For geml, get all. */
946 if (!strcmp(q->shortname, "geml"))
951 /* For gmol or geml, only return type and name. For gtml, return tag too. */
952 if (!strcmp(q->shortname, "gtml"))
957 list_id = *(int *)argv[0];
959 targv[1] = member_name;
963 EXEC SQL DECLARE csr119 CURSOR FOR
964 SELECT u.login, s.string FROM users u, imembers im, strings s
965 WHERE im.list_id = :list_id AND im.member_type = 'USER'
966 AND im.member_id = u.users_id AND im.direct > :direct
967 AND s.string_id = im.tag ORDER BY 1;
970 EXEC SQL OPEN csr119;
975 EXEC SQL FETCH csr119 INTO :member_name, :tag;
978 (*action)(targc, targv, actarg);
980 EXEC SQL CLOSE csr119;
985 EXEC SQL DECLARE csr120 CURSOR FOR
986 SELECT l.name, s.string FROM list l, imembers im, strings s
987 WHERE im.list_id = :list_id AND im.member_type = 'LIST'
988 AND im.member_id = l.list_id AND im.direct > :direct
989 AND s.string_id = im.tag ORDER BY 1;
992 EXEC SQL OPEN csr120;
997 EXEC SQL FETCH csr120 INTO :member_name, :tag;
1000 (*action)(targc, targv, actarg);
1002 EXEC SQL CLOSE csr120;
1006 targv[0] = "STRING";
1007 EXEC SQL DECLARE csr121 CURSOR FOR
1008 SELECT str.string, s.string FROM strings str, imembers im, strings s
1009 WHERE im.list_id = :list_id AND im.member_type = 'STRING'
1010 AND im.member_id = str.string_id AND im.direct > :direct
1011 AND s.string_id = im.tag ORDER BY 1;
1014 EXEC SQL OPEN csr121;
1019 EXEC SQL FETCH csr121 INTO :member_name, :tag;
1022 (*action)(targc, targv, actarg);
1024 EXEC SQL CLOSE csr121;
1028 targv[0] = "KERBEROS";
1029 EXEC SQL DECLARE csr122 CURSOR FOR
1030 SELECT str.string, s.string FROM strings str, imembers im, strings s
1031 WHERE im.list_id = :list_id AND im.member_type = 'KERBEROS'
1032 AND im.member_id = str.string_id AND im.direct > :direct
1033 AND s.string_id = im.tag ORDER BY 1;
1036 EXEC SQL OPEN csr122;
1041 EXEC SQL FETCH csr122 INTO :member_name, :tag;
1044 (*action)(targc, targv, actarg);
1046 EXEC SQL CLOSE csr122;
1054 /* count_members_of_list: this is a simple query, but it cannot be done
1055 * through the dispatch table.
1058 int count_members_of_list(struct query *q, char *argv[], client *cl,
1059 int (*action)(int, char *[], void *), void *actarg)
1061 EXEC SQL BEGIN DECLARE SECTION;
1063 EXEC SQL END DECLARE SECTION;
1064 char *rargv[1], countbuf[5];
1066 list = *(int *)argv[0];
1067 rargv[0] = countbuf;
1068 EXEC SQL SELECT count (*) INTO :ct FROM imembers
1069 WHERE list_id = :list AND direct = 1;
1072 sprintf(countbuf, "%d", ct);
1073 (*action)(1, rargv, actarg);
1078 /* qualified_get_server: passed "TRUE", "FALSE", or "DONTCARE" for each of
1079 * the three flags associated with each service. It will return the name of
1080 * each service that meets the quailifications. It does this by building a
1081 * where clause based on the arguments, then doing a retrieve.
1084 static char *sflags[3] = { "enable", "inprogress", "harderror" };
1086 int qualified_get_server(struct query *q, char *argv[], client *cl,
1087 int (*action)(int, char *[], void *), void *actarg)
1089 return qualified_get(q, argv, action, actarg, "s.name is not null",
1090 "s", "name", sflags);
1091 /* of course, name will never be null, but we need something there
1092 to make qualified_get happy */
1096 /* generic qualified get routine, used by qualified_get_lists,
1097 * qualified_get_server, and qualified_get_serverhost.
1099 * start - a simple where clause, must not be empty
1100 * range - the name of the range variable
1101 * field - the field to return
1102 * flags - an array of strings, names of the flag variables
1105 int qualified_get(struct query *q, char *argv[],
1106 int (*action)(int, char *[], void *), void *actarg,
1107 char *start, char *range, char *field, char *flags[])
1113 strcpy(qual, start);
1114 for (i = 0; i < q->argc; i++)
1116 if (!strcmp(argv[i], "TRUE"))
1118 sprintf(buf, " AND %s.%s != 0", range, flags[i]);
1121 else if (!strcmp(argv[i], "FALSE"))
1123 sprintf(buf, " AND %s.%s = 0", range, flags[i]);
1128 sprintf(stmt_buf, "SELECT %s.%s FROM %s %s WHERE %s", range, field,
1129 table_name[q->rtable], range, qual);
1130 return do_for_all_rows(stmt_buf, 1, action, actarg);
1134 /* qualified_get_serverhost: passed "TRUE", "FALSE", or "DONTCARE" for each of
1135 * the five flags associated with each serverhost. It will return the name of
1136 * each service and host that meets the quailifications. It does this by
1137 * building a where clause based on the arguments, then doing a retrieve.
1140 static char *shflags[6] = { "service", "enable", "override", "success",
1141 "inprogress", "hosterror" };
1143 int qualified_get_serverhost(struct query *q, char *argv[], client *cl,
1144 int (*action)(int, char *[], void *),
1147 char qual[256], buf[32];
1150 sprintf(qual, "m.mach_id = sh.mach_id AND sh.service = UPPER('%s')",
1152 for (i = 1; i < q->argc; i++)
1154 if (!strcmp(argv[i], "TRUE"))
1156 sprintf(buf, " AND sh.%s != 0", shflags[i]);
1159 else if (!strcmp(argv[i], "FALSE"))
1161 sprintf(buf, " AND sh.%s = 0", shflags[i]);
1166 sprintf(stmt_buf, "SELECT sh.service, m.name FROM serverhosts sh, "
1167 "machine m WHERE %s", qual);
1168 return do_for_all_rows(stmt_buf, 2, action, actarg);
1172 /* register_user - change user's login name and allocate a pobox, group,
1173 * filesystem, and quota for them. The user's status must start out as 0,
1174 * and is left as 2. Arguments are: user's UID, new login name, and user's
1175 * type for filesystem allocation (MR_FS_STUDENT, MR_FS_FACULTY,
1176 * MR_FS_STAFF, MR_FS_MISC).
1179 int register_user(struct query *q, char **argv, client *cl)
1181 EXEC SQL BEGIN DECLARE SECTION;
1182 char *login, *entity;
1183 char directory[FILESYS_NAME_SIZE], machname[MACHINE_NAME_SIZE];
1184 int who, rowcount, mid, uid, users_id, utype, list_id;
1185 int ostatus, nstatus, fsidval;
1186 static int m_id = 0, def_quota = 0;
1187 EXEC SQL END DECLARE SECTION;
1188 char buffer[256], *aargv[3];
1190 entity = cl->entity;
1191 who = cl->client_id;
1193 uid = atoi(argv[0]);
1195 utype = atoi(argv[2]);
1198 EXEC SQL SELECT users_id, status INTO :users_id, :ostatus
1200 WHERE unix_uid = :uid AND (status = 0 OR status = 5 OR status = 6);
1202 if (sqlca.sqlerrd[2] == 0)
1204 if (sqlca.sqlerrd[2] > 1)
1205 return MR_NOT_UNIQUE;
1207 /* check new login name */
1208 EXEC SQL SELECT COUNT(login) INTO :rowcount FROM users
1209 WHERE login = :login AND users_id != :users_id;
1214 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM list
1215 WHERE LOWER(name) = :login;
1220 EXEC SQL SELECT COUNT(label) INTO :rowcount FROM filesys
1221 WHERE label = :login;
1226 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM alias
1227 WHERE name = :login AND type = 'FILESYS';
1232 com_err(whoami, 0, "login name OK");
1234 /* choose place for pobox, put in mid */
1235 EXEC SQL DECLARE csr130 CURSOR FOR
1236 SELECT sh.mach_id, m.name FROM serverhosts sh, machine m
1237 WHERE sh.service = 'POP' AND sh.mach_id = m.mach_id
1238 AND sh.value2 - sh.value1 = (SELECT MAX(value2 - value1) FROM serverhosts
1239 WHERE service = 'POP');
1242 EXEC SQL OPEN csr130;
1245 EXEC SQL FETCH csr130 INTO :mid, :machname;
1246 if (sqlca.sqlerrd[2] == 0)
1248 EXEC SQL CLOSE csr130;
1255 EXEC SQL CLOSE csr130;
1260 /* change login name, set pobox */
1261 sprintf(buffer, "u.users_id = %d", users_id);
1262 incremental_before(USERS_TABLE, buffer, 0);
1264 if (ostatus == 5 || ostatus == 6)
1266 EXEC SQL UPDATE users SET login = :login, status = :nstatus,
1267 modtime = SYSDATE, modby = :who, modwith = :entity, potype = 'POP',
1268 pop_id = :mid, pmodtime = SYSDATE, pmodby = :who, pmodwith = :entity
1269 WHERE users_id = :users_id;
1273 if (sqlca.sqlerrd[2] != 1)
1275 set_pop_usage(mid, 1);
1276 com_err(whoami, 0, "set login name to %s and pobox to %s", login,
1278 incremental_after(USERS_TABLE, buffer, 0);
1282 /* Cell Name (I know, it shouldn't be hard coded...) */
1283 strcpy(machname, "ATHENA.MIT.EDU");
1284 EXEC SQL SELECT mach_id INTO :m_id FROM machine
1285 WHERE name = :machname;
1288 EXEC SQL SELECT list_id INTO :list_id FROM list
1289 WHERE name = 'wheel';
1291 /* create filesystem */
1292 if (set_next_object_id("filsys_id", FILESYS_TABLE, 0))
1294 incremental_clear_before();
1295 if (islower(login[0]) && islower(login[1]))
1297 sprintf(directory, "/afs/athena.mit.edu/user/%c/%c/%s",
1298 login[0], login[1], login);
1301 sprintf(directory, "/afs/athena.mit.edu/user/other/%s", login);
1303 EXEC SQL SELECT value INTO :fsidval FROM numvalues
1304 WHERE numvalues.name = 'filsys_id';
1305 EXEC SQL INSERT INTO filesys
1306 (filsys_id, phys_id, label, type, mach_id, name,
1307 mount, rwaccess, comments, owner, owners, createflg,
1308 lockertype, modtime, modby, modwith)
1310 (:fsidval, 0, :login, 'AFS', :m_id, :directory,
1311 '/mit/' || :login, 'w', 'User Locker', :users_id, :list_id, 1,
1312 'HOMEDIR', SYSDATE, :who, :entity);
1316 if (sqlca.sqlerrd[2] != 1)
1318 sprintf(buffer, "fs.filsys_id = %d", fsidval);
1319 incremental_after(FILESYS_TABLE, buffer, 0);
1324 EXEC SQL SELECT value INTO :def_quota FROM numvalues
1325 WHERE name = 'def_quota';
1328 if (sqlca.sqlerrd[2] != 1)
1331 incremental_clear_before();
1332 EXEC SQL INSERT INTO quota
1333 (entity_id, filsys_id, type, quota, phys_id, modtime, modby, modwith)
1334 VALUES (0, :fsidval, 'ANY', :def_quota, 0, SYSDATE, :who, :entity);
1337 if (sqlca.sqlerrd[2] != 1)
1342 sprintf(buffer, "q.entity_id = 0 and q.filsys_id = %d and q.type = 'ANY'",
1344 incremental_after(QUOTA_TABLE, buffer, aargv);
1345 com_err(whoami, 0, "quota of %d assigned", def_quota);
1349 cache_entry(login, USERS_TABLE, users_id);
1351 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
1352 WHERE table_name = 'users';
1353 EXEC SQL UPDATE tblstats SET appends = appends + 1, modtime = SYSDATE
1354 WHERE table_name = 'filesys' OR table_name = 'quota';
1362 /** set_pop_usage - incr/decr usage count for pop server in serverhosts talbe
1366 ** delta (will be +/- 1)
1369 ** - incr/decr value field in serverhosts table for pop/mach_id
1373 int set_pop_usage(id, cnt)
1376 EXEC SQL BEGIN DECLARE SECTION;
1377 int iid = id, icnt = cnt;
1378 EXEC SQL END DECLARE SECTION;
1380 EXEC SQL UPDATE serverhosts SET value1 = value1 + :icnt
1381 WHERE serverhosts.service = 'POP' AND serverhosts.mach_id = :iid;