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 gmol_internal(struct query *q, char *argv[], client *cl,
35 int (*action)(int, char *[], void *), void *actarg,
37 int qualified_get(struct query *q, char *argv[],
38 int (*action)(int, char *[], void *), void *actarg,
39 char *start, char *range, char *field, char *flags[]);
42 /* set_pobox - this does all of the real work.
43 * argv = user_id, type, box
44 * if type is POP, then box should be a machine, and its ID should be put in
45 * pop_id. If type is SMTP, then box should be a string and its ID should
46 * be put in box_id. If type is NONE, then box doesn't matter.
50 int set_pobox(struct query *q, char **argv, client *cl)
52 EXEC SQL BEGIN DECLARE SECTION;
55 EXEC SQL END DECLARE SECTION;
59 user = *(int *)argv[0];
61 EXEC SQL SELECT pop_id, potype INTO :id, :potype FROM users
62 WHERE users_id = :user;
65 if (!strcmp(strtrim(potype), "POP"))
66 set_pop_usage(id, -1);
68 if (!strcmp(argv[1], "POP"))
70 status = name_to_id(box, MACHINE_TABLE, &id);
71 if (status == MR_NO_MATCH)
75 EXEC SQL UPDATE users SET potype = 'POP', pop_id = :id
76 WHERE users_id = :user;
79 else if (!strcmp(argv[1], "SMTP"))
81 if (strchr(box, '/') || strchr(box, '|'))
83 status = name_to_id(box, STRINGS_TABLE, &id);
84 if (status == MR_NO_MATCH)
88 EXEC SQL UPDATE users SET potype = 'SMTP', box_id = :id
89 WHERE users_id = :user;
91 else /* argv[1] == "NONE" */
93 EXEC SQL UPDATE users SET potype = 'NONE'
94 WHERE users_id = :user;
97 set_pobox_modtime(q, argv, cl);
98 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
99 WHERE table_name = 'users';
106 /* get_list_info: passed a wildcard list name, returns lots of stuff about
107 * each list. This is tricky: first build a queue of all requested
108 * data. Rest of processing consists of fixing gid, ace_name, and modby.
111 int get_list_info(struct query *q, char **aargv, client *cl,
112 int (*action)(int, char *[], void *), void *actarg)
115 EXEC SQL BEGIN DECLARE SECTION;
116 char *name, acl_type[9], listname[33], active[5], public[5], hidden[5];
117 char maillist[5], grouplist[5], gid_str[6], desc[256];
118 char modtime[27], modwith[9];
119 int id, rowcount, acl_id, hid, modby_id;
120 EXEC SQL END DECLARE SECTION;
121 int returned, status;
122 struct save_queue *sq;
124 returned = rowcount = 0;
126 convert_wildcards(name);
129 EXEC SQL DECLARE csr102 CURSOR FOR SELECT list_id FROM list
130 WHERE name LIKE :name ESCAPE '*';
133 EXEC SQL OPEN csr102;
138 EXEC SQL FETCH csr102 INTO :id;
141 sq_save_data(sq, (void *)id);
144 EXEC SQL CLOSE csr102;
151 argv[0] = listname; argv[1] = active; argv[2] = public; argv[3] = hidden;
152 argv[4] = maillist; argv[5] = grouplist; argv[6] = gid_str;
153 argv[7] = acl_type; argv[9] = desc; argv[10] = modtime; argv[12] = modwith;
155 while (sq_get_data(sq, &id))
160 EXEC SQL SELECT name, active, publicflg,
161 hidden, hidden, maillist, grouplist, gid,
162 acl_type, acl_id, description,
163 TO_CHAR(modtime, 'DD-mon-YYYY HH24:MI:SS'), modby, modwith
164 INTO :listname, :active, :public, :hidden, :hid, :maillist,
165 :grouplist, :gid_str, :acl_type, :acl_id, :desc,
166 :modtime, :modby_id, :modwith
167 FROM list WHERE list_id = :id;
173 if (atoi(gid_str) == -1)
174 argv[6] = UNIQUE_GID;
177 if (!strcmp(acl_type, "LIST"))
178 status = id_to_name(acl_id, LIST_TABLE, &argv[8]);
179 else if (!strcmp(acl_type, "USER"))
180 status = id_to_name(acl_id, USERS_TABLE, &argv[8]);
181 else if (!strcmp(acl_type, "KERBEROS"))
182 status = id_to_name(acl_id, STRINGS_TABLE, &argv[8]);
183 else if (!strcmp(acl_type, "NONE"))
187 argv[8] = strdup("NONE");
193 argv[8] = strdup("???");
195 if (status && status != MR_NO_MATCH)
198 argv[11] = malloc(0);
200 status = id_to_name(modby_id, USERS_TABLE, &argv[11]);
202 status = id_to_name(-modby_id, STRINGS_TABLE, &argv[11]);
203 if (status && status != MR_NO_MATCH)
206 mr_trim_args(q->vcnt, argv);
208 (*action)(q->vcnt, argv, actarg);
220 /* Add_member_to_list: do list flattening as we go! MAXLISTDEPTH is
221 * how many different ancestors a member is allowed to have.
224 #define MAXLISTDEPTH 1024
226 int add_member_to_list(struct query *q, char **argv, client *cl)
228 EXEC SQL BEGIN DECLARE SECTION;
229 int id, lid, mid, error, who, ref, rowcnt;
230 char *mtype, dtype[9], *entity;
231 EXEC SQL END DECLARE SECTION;
232 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
233 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
235 char *dtypes[MAXLISTDEPTH];
236 char *iargv[3], *buf;
238 lid = *(int *)argv[0];
240 mid = *(int *)argv[2];
241 /* if the member is already a direct member of the list, punt */
242 EXEC SQL SELECT COUNT(list_id) INTO :rowcnt FROM imembers
243 WHERE list_id = :lid AND member_id = :mid
244 AND member_type = :mtype AND direct = 1;
247 if (!strcasecmp(mtype, "STRING"))
250 status = id_to_name(mid, STRINGS_TABLE, &buf);
253 if (strchr(buf, '/') || strchr(buf, '|'))
264 EXEC SQL DECLARE csr103 CURSOR FOR
265 SELECT list_id, ref_count FROM imembers
266 WHERE member_id = :lid AND member_type = 'LIST';
269 EXEC SQL OPEN csr103;
274 EXEC SQL FETCH csr103 INTO :id, :ref;
278 ancestors[acount++] = id;
279 if (acount >= MAXLISTDEPTH)
282 EXEC SQL CLOSE csr103;
285 if (acount >= MAXLISTDEPTH)
287 descendants[0] = mid;
292 if (!strcmp(mtype, "LIST"))
294 EXEC SQL DECLARE csr104 CURSOR FOR
295 SELECT member_id, member_type, ref_count
297 WHERE list_id = :mid;
300 EXEC SQL OPEN csr104;
305 EXEC SQL FETCH csr104 INTO :id, :dtype, :ref;
311 dtypes[dcount] = "LIST";
314 dtypes[dcount] = "USER";
317 dtypes[dcount] = "STRING";
320 dtypes[dcount] = "KERBEROS";
327 descendants[dcount++] = id;
328 if (dcount >= MAXLISTDEPTH)
334 EXEC SQL CLOSE csr104;
340 for (a = 0; a < acount; a++)
343 for (d = 0; d < dcount; d++)
345 mid = descendants[d];
347 if (mid == lid && !strcmp(mtype, "LIST"))
349 EXEC SQL SELECT COUNT(ref_count) INTO :rowcnt
351 WHERE list_id = :lid AND member_id = :mid
352 AND member_type = :mtype;
353 ref = aref[a] * dref[d];
356 if (a == 0 && d == 0)
358 EXEC SQL UPDATE imembers
359 SET ref_count = ref_count + :ref, direct = 1
360 WHERE list_id = :lid AND member_id = :mid
361 AND member_type = :mtype;
365 EXEC SQL UPDATE imembers
366 SET ref_count = ref_count + :ref
367 WHERE list_id = :lid AND member_id = :mid
368 AND member_type = :mtype;
373 incremental_clear_before();
374 if (a == 0 && d == 0)
376 EXEC SQL INSERT INTO imembers
377 (list_id, member_id, direct, member_type, ref_count)
378 VALUES (:lid, :mid, 1, :mtype, 1);
382 EXEC SQL INSERT INTO imembers
383 (list_id, member_id, direct, member_type, ref_count)
384 VALUES (:lid, :mid, 0, :mtype, 1);
386 iargv[0] = (char *)lid;
388 iargv[2] = (char *)mid;
389 incremental_after(IMEMBERS_TABLE, 0, iargv);
393 lid = *(int *)argv[0];
397 SET modtime = SYSDATE, modby = :who, modwith = :entity
398 WHERE list_id = :lid;
405 /* Delete_member_from_list: do list flattening as we go!
408 int delete_member_from_list(struct query *q, char **argv, client *cl)
410 EXEC SQL BEGIN DECLARE SECTION;
411 int id, lid, mid, cnt, error, who, ref;
412 char *mtype, dtype[9], *entity;
413 EXEC SQL END DECLARE SECTION;
414 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
415 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
416 char *dtypes[MAXLISTDEPTH];
419 lid = *(int *)argv[0];
421 mid = *(int *)argv[2];
422 /* if the member is not a direct member of the list, punt */
423 EXEC SQL SELECT COUNT(list_id) INTO :cnt FROM imembers
424 WHERE list_id = :lid AND member_id = :mid
425 AND member_type = :mtype AND direct = 1;
433 EXEC SQL DECLARE csr105 CURSOR FOR
434 SELECT list_id, ref_count FROM imembers
435 WHERE member_id = :lid AND member_type = 'LIST';
438 EXEC SQL OPEN csr105;
443 EXEC SQL FETCH csr105 INTO :id, :ref;
447 ancestors[acount++] = id;
448 if (acount >= MAXLISTDEPTH)
451 EXEC SQL CLOSE csr105;
454 if (acount >= MAXLISTDEPTH)
456 descendants[0] = mid;
461 if (!strcmp(mtype, "LIST"))
463 EXEC SQL DECLARE csr106 CURSOR FOR
464 SELECT member_id, member_type, ref_count FROM imembers
465 WHERE list_id = :mid;
468 EXEC SQL OPEN csr106;
473 EXEC SQL FETCH csr106 INTO :id, :dtype, :ref;
479 dtypes[dcount] = "LIST";
482 dtypes[dcount] = "USER";
485 dtypes[dcount] = "STRING";
488 dtypes[dcount] = "KERBEROS";
495 descendants[dcount++] = id;
496 if (dcount >= MAXLISTDEPTH)
499 EXEC SQL CLOSE csr106;
505 for (a = 0; a < acount; a++)
508 for (d = 0; d < dcount; d++)
510 mid = descendants[d];
512 if (mid == lid && !strcmp(mtype, "LIST"))
514 EXEC SQL SELECT ref_count INTO :cnt FROM imembers
515 WHERE list_id = :lid AND member_id = :mid AND member_type = :mtype;
516 ref = aref[a] * dref[d];
519 iargv[0] = (char *)lid;
521 iargv[2] = (char *)mid;
522 incremental_before(IMEMBERS_TABLE, 0, iargv);
523 EXEC SQL DELETE FROM imembers
524 WHERE list_id = :lid AND member_id = :mid
525 AND member_type= :mtype;
526 incremental_clear_after();
528 else if (a == 0 && d == 0)
530 EXEC SQL UPDATE imembers
531 SET ref_count = ref_count - :ref, direct = 0
532 WHERE list_id = :lid AND member_id = :mid
533 AND member_type = :mtype;
537 EXEC SQL UPDATE imembers
538 SET ref_count = ref_count - :ref
539 WHERE list_id = :lid AND member_id = :mid
540 AND member_type = :mtype;
544 lid = *(int *)argv[0];
547 EXEC SQL UPDATE list SET modtime = SYSDATE, modby = :who, modwith = :entity
548 WHERE list_id = :lid;
555 /* get_ace_use - given a type and a name, return a type and a name.
556 * The ace_type is one of "LIST", "USER", "RLIST", or "RUSER" in argv[0],
557 * and argv[1] will contain the ID of the entity in question. The R*
558 * types mean to recursively look at every containing list, not just
559 * when the object in question is a direct member. On return, the
560 * usage type will be one of LIST, SERVICE, FILESYS, QUOTA, QUERY, or ZEPHYR.
563 int get_ace_use(struct query *q, char *argv[], client *cl,
564 int (*action)(int, char *[], void *), void *actarg)
567 EXEC SQL BEGIN DECLARE SECTION;
570 EXEC SQL END DECLARE SECTION;
571 struct save_queue *sq;
574 aid = *(int *)argv[1];
575 if (!strcmp(atype, "LIST") || !strcmp(atype, "USER") ||
576 !strcmp(atype, "KERBEROS"))
577 return get_ace_internal(atype, aid, action, actarg);
580 if (!strcmp(atype, "RLIST"))
582 sq_save_data(sq, (void *)aid);
583 /* get all the list_id's of containing lists */
584 EXEC SQL DECLARE csr107 CURSOR FOR
585 SELECT list_id FROM imembers
586 WHERE member_type = 'LIST' AND member_id = :aid;
589 EXEC SQL OPEN csr107;
594 EXEC SQL FETCH csr107 INTO :listid;
597 sq_save_unique_data(sq, (void *)listid);
599 EXEC SQL CLOSE csr107;
600 /* now process each one */
601 while (sq_get_data(sq, &id))
603 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
608 if (!strcmp(atype, "RUSER"))
610 EXEC SQL DECLARE csr108 CURSOR FOR
611 SELECT list_id FROM imembers
612 WHERE member_type = 'USER' AND member_id = :aid;
615 EXEC SQL OPEN csr108;
620 EXEC SQL FETCH csr108 INTO :listid;
623 sq_save_data(sq, (void *)listid);
625 EXEC SQL CLOSE csr108;
626 /* now process each one */
627 while (sq_get_data(sq, &id))
629 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
632 if (get_ace_internal("USER", aid, action, actarg) == MR_SUCCESS)
636 if (!strcmp(atype, "RKERBEROS"))
638 EXEC SQL DECLARE csr109 CURSOR FOR
639 SELECT list_id FROM imembers
640 WHERE member_type = 'KERBEROS' AND member_id = :aid;
643 EXEC SQL OPEN csr109;
648 EXEC SQL FETCH csr109 INTO :listid;
651 sq_save_data(sq, (void *)listid);
653 EXEC SQL CLOSE csr109;
654 /* now process each one */
655 while (sq_get_data(sq, &id))
657 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
660 if (get_ace_internal("KERBEROS", aid, action, actarg) == MR_SUCCESS)
673 /* This looks up a single list or user for ace use. atype must be "USER"
674 * or "LIST", and aid is the ID of the corresponding object. This is used
675 * by get_ace_use above.
678 int get_ace_internal(char *atype, int aid,
679 int (*action)(int, char *[], void *), void *actarg)
683 EXEC SQL BEGIN DECLARE SECTION;
684 char name[33], *type = atype;
686 EXEC SQL END DECLARE SECTION;
689 if (!strcmp(atype, "LIST"))
691 rargv[0] = "FILESYS";
692 EXEC SQL DECLARE csr110 CURSOR FOR
693 SELECT label FROM filesys
697 EXEC SQL OPEN csr110;
702 EXEC SQL FETCH csr110 INTO :name;
705 (*action)(2, rargv, actarg);
708 EXEC SQL CLOSE csr110;
711 EXEC SQL DECLARE csr111 CURSOR FOR
712 SELECT capability FROM capacls
716 EXEC SQL OPEN csr111;
721 EXEC SQL FETCH csr111 INTO :name;
724 (*action)(2, rargv, actarg);
727 EXEC SQL CLOSE csr111;
729 else if (!strcmp(atype, "USER"))
731 rargv[0] = "FILESYS";
732 EXEC SQL DECLARE csr112 CURSOR FOR
733 SELECT label FROM filesys
737 EXEC SQL OPEN csr112;
742 EXEC SQL FETCH csr112 INTO :name;
745 (*action)(2, rargv, actarg);
748 EXEC SQL CLOSE csr112;
752 EXEC SQL DECLARE csr113 CURSOR FOR
753 SELECT name FROM list
754 WHERE acl_type = :type AND acl_id = :id;
757 EXEC SQL OPEN csr113;
762 EXEC SQL FETCH csr113 INTO :name;
765 (*action)(2, rargv, actarg);
768 EXEC SQL CLOSE csr113;
770 rargv[0] = "SERVICE";
771 EXEC SQL DECLARE csr114 CURSOR FOR
772 SELECT name FROM servers
773 WHERE acl_type = :type AND acl_id = :id;
776 EXEC SQL OPEN csr114;
781 EXEC SQL FETCH csr114 INTO :name;
784 (*action)(2, rargv, actarg);
787 EXEC SQL CLOSE csr114;
789 rargv[0] = "HOSTACCESS";
790 EXEC SQL DECLARE csr115 CURSOR FOR
791 SELECT name FROM machine m, hostaccess ha
792 WHERE m.mach_id = ha.mach_id AND ha.acl_type = :type
796 EXEC SQL OPEN csr115;
801 EXEC SQL FETCH csr115 INTO :name;
804 (*action)(2, rargv, actarg);
807 EXEC SQL CLOSE csr115;
810 EXEC SQL DECLARE csr116 CURSOR FOR
811 SELECT class FROM zephyr z
812 WHERE z.xmt_type = :type AND z.xmt_id = :id
813 OR z.sub_type = :type AND z.sub_id = :id
814 OR z.iws_type = :type AND z.iws_id = :id
815 OR z.iui_type = :type AND z.iui_id = :id;
818 EXEC SQL OPEN csr116;
823 EXEC SQL FETCH csr116 INTO :name;
826 (*action)(2, rargv, actarg);
829 EXEC SQL CLOSE csr116;
837 /* get_lists_of_member - given a type and a name, return the name and flags
838 * of all of the lists of the given member. The member_type is one of
839 * "LIST", "USER", "STRING", "RLIST", "RUSER", or "RSTRING" in argv[0],
840 * and argv[1] will contain the ID of the entity in question. The R*
841 * types mean to recursively look at every containing list, not just
842 * when the object in question is a direct member.
845 int get_lists_of_member(struct query *q, char *argv[], client *cl,
846 int (*action)(int, char *[], void *), void *actarg)
848 int found = 0, direct = 1;
850 EXEC SQL BEGIN DECLARE SECTION;
853 char name[33], active[5], public[5], hidden[5], maillist[5], grouplist[5];
854 EXEC SQL END DECLARE SECTION;
857 aid = *(int *)argv[1];
858 if (!strcmp(atype, "RLIST"))
863 if (!strcmp(atype, "RUSER"))
868 if (!strcmp(atype, "RSTRING"))
873 if (!strcmp(atype, "RKERBEROS"))
884 rargv[5] = grouplist;
887 EXEC SQL DECLARE csr117a CURSOR FOR
888 SELECT l.name, l.active, l.publicflg, l.hidden, l.maillist, l.grouplist
889 FROM list l, imembers im
890 WHERE l.list_id = im.list_id AND im.direct = 1
891 AND im.member_type = :atype AND im.member_id = :aid;
894 EXEC SQL OPEN csr117a;
899 EXEC SQL FETCH csr117a
900 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
903 (*action)(6, rargv, actarg);
906 EXEC SQL CLOSE csr117a;
910 EXEC SQL DECLARE csr117b CURSOR FOR
911 SELECT l.name, l.active, l.publicflg, l.hidden, l.maillist, l.grouplist
912 FROM list l, imembers im
913 WHERE l.list_id = im.list_id
914 AND im.member_type = :atype AND im.member_id = :aid;
917 EXEC SQL OPEN csr117b;
922 EXEC SQL FETCH csr117b
923 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
926 (*action)(6, rargv, actarg);
929 EXEC SQL CLOSE csr117b;
940 /* qualified_get_lists: passed "TRUE", "FALSE", or "DONTCARE" for each of
941 * the five flags associated with each list. It will return the name of
942 * each list that meets the quailifications. It does this by building a
943 * where clause based on the arguments, then doing a retrieve.
946 static char *lflags[5] = { "active", "publicflg", "hidden", "maillist", "grouplist" };
948 int qualified_get_lists(struct query *q, char *argv[], client *cl,
949 int (*action)(int, char *[], void *), void *actarg)
951 return qualified_get(q, argv, action, actarg, "l.list_id != 0",
952 "l", "name", lflags);
956 /* get_members_of_list - this gets only direct members */
958 int get_members_of_list(struct query *q, char *argv[], client *cl,
959 int (*action)(int, char *[], void *), void *actarg)
961 return gmol_internal(q, argv, cl, action, actarg, 1);
964 /* get_end_members_of_list - this gets direct or indirect members */
966 int get_end_members_of_list(struct query *q, char *argv[], client *cl,
967 int (*action)(int, char *[], void *), void *actarg)
969 return gmol_internal(q, argv, cl, action, actarg, 0);
972 /** gmol_internal - optimized query for retrieval of list members
973 ** used by both get_members_of_list and get_end_members_of_list
979 ** - retrieve USER members, then LIST members, then STRING members
982 int gmol_internal(struct query *q, char *argv[], client *cl,
983 int (*action)(int, char *[], void *), void *actarg, int flag)
985 EXEC SQL BEGIN DECLARE SECTION;
987 char member_name[129];
988 EXEC SQL END DECLARE SECTION;
991 /* true/false flag indicates whether to display only direct members. */
997 list_id = *(int *)argv[0];
999 targv[1] = member_name;
1001 EXEC SQL DECLARE csr119 CURSOR FOR
1002 SELECT u.login FROM users u, imembers im
1003 WHERE im.list_id = :list_id AND im.member_type = 'USER'
1004 AND im.member_id = u.users_id AND im.direct > :direct
1008 EXEC SQL OPEN csr119;
1013 EXEC SQL FETCH csr119 INTO :member_name;
1016 (*action)(2, targv, actarg);
1018 EXEC SQL CLOSE csr119;
1023 EXEC SQL DECLARE csr120 CURSOR FOR
1024 SELECT l.name FROM list l, imembers im
1025 WHERE im.list_id = :list_id AND im.member_type = 'LIST'
1026 AND im.member_id = l.list_id AND im.direct > :direct
1030 EXEC SQL OPEN csr120;
1035 EXEC SQL FETCH csr120 INTO :member_name;
1038 (*action)(2, targv, actarg);
1040 EXEC SQL CLOSE csr120;
1044 targv[0] = "STRING";
1045 EXEC SQL DECLARE csr121 CURSOR FOR
1046 SELECT str.string FROM strings str, imembers im
1047 WHERE im.list_id = :list_id AND im.member_type = 'STRING'
1048 AND im.member_id = str.string_id AND im.direct > :direct
1052 EXEC SQL OPEN csr121;
1057 EXEC SQL FETCH csr121 INTO :member_name;
1060 (*action)(2, targv, actarg);
1062 EXEC SQL CLOSE csr121;
1066 targv[0] = "KERBEROS";
1067 EXEC SQL DECLARE csr122 CURSOR FOR
1068 SELECT str.string FROM strings str, imembers im
1069 WHERE im.list_id = :list_id AND im.member_type = 'KERBEROS'
1070 AND im.member_id = str.string_id
1071 AND im.direct > :direct
1075 EXEC SQL OPEN csr122;
1080 EXEC SQL FETCH csr122 INTO :member_name;
1083 (*action)(2, targv, actarg);
1085 EXEC SQL CLOSE csr122;
1093 /* count_members_of_list: this is a simple query, but it cannot be done
1094 * through the dispatch table.
1097 int count_members_of_list(struct query *q, char *argv[], client *cl,
1098 int (*action)(int, char *[], void *), void *actarg)
1100 EXEC SQL BEGIN DECLARE SECTION;
1102 EXEC SQL END DECLARE SECTION;
1103 char *rargv[1], countbuf[5];
1105 list = *(int *)argv[0];
1106 rargv[0] = countbuf;
1107 EXEC SQL SELECT count (*) INTO :ct FROM imembers
1108 WHERE list_id = :list AND direct = 1;
1111 sprintf(countbuf, "%d", ct);
1112 (*action)(1, rargv, actarg);
1117 /* qualified_get_server: passed "TRUE", "FALSE", or "DONTCARE" for each of
1118 * the three flags associated with each service. It will return the name of
1119 * each service that meets the quailifications. It does this by building a
1120 * where clause based on the arguments, then doing a retrieve.
1123 static char *sflags[3] = { "enable", "inprogress", "harderror" };
1125 int qualified_get_server(struct query *q, char *argv[], client *cl,
1126 int (*action)(int, char *[], void *), void *actarg)
1128 return qualified_get(q, argv, action, actarg, "s.name is not null",
1129 "s", "name", sflags);
1130 /* of course, name will never be null, but we need something there
1131 to make qualified_get happy */
1135 /* generic qualified get routine, used by qualified_get_lists,
1136 * qualified_get_server, and qualified_get_serverhost.
1138 * start - a simple where clause, must not be empty
1139 * range - the name of the range variable
1140 * field - the field to return
1141 * flags - an array of strings, names of the flag variables
1144 int qualified_get(struct query *q, char *argv[],
1145 int (*action)(int, char *[], void *), void *actarg,
1146 char *start, char *range, char *field, char *flags[])
1152 strcpy(qual, start);
1153 for (i = 0; i < q->argc; i++)
1155 if (!strcmp(argv[i], "TRUE"))
1157 sprintf(buf, " AND %s.%s != 0", range, flags[i]);
1160 else if (!strcmp(argv[i], "FALSE"))
1162 sprintf(buf, " AND %s.%s = 0", range, flags[i]);
1167 sprintf(stmt_buf, "SELECT %s.%s FROM %s %s WHERE %s", range, field,
1168 table_name[q->rtable], range, qual);
1169 return do_for_all_rows(stmt_buf, 1, action, actarg);
1173 /* qualified_get_serverhost: passed "TRUE", "FALSE", or "DONTCARE" for each of
1174 * the five flags associated with each serverhost. It will return the name of
1175 * each service and host that meets the quailifications. It does this by
1176 * building a where clause based on the arguments, then doing a retrieve.
1179 static char *shflags[6] = { "service", "enable", "override", "success",
1180 "inprogress", "hosterror" };
1182 int qualified_get_serverhost(struct query *q, char *argv[], client *cl,
1183 int (*action)(int, char *[], void *),
1186 char qual[256], buf[32];
1189 sprintf(qual, "m.mach_id = sh.mach_id AND sh.service = UPPER('%s')",
1191 for (i = 1; i < q->argc; i++)
1193 if (!strcmp(argv[i], "TRUE"))
1195 sprintf(buf, " AND sh.%s != 0", shflags[i]);
1198 else if (!strcmp(argv[i], "FALSE"))
1200 sprintf(buf, " AND sh.%s = 0", shflags[i]);
1205 sprintf(stmt_buf, "SELECT sh.service, m.name FROM serverhosts sh, "
1206 "machine m WHERE %s", qual);
1207 return do_for_all_rows(stmt_buf, 2, action, actarg);
1211 /* register_user - change user's login name and allocate a pobox, group,
1212 * filesystem, and quota for them. The user's status must start out as 0,
1213 * and is left as 2. Arguments are: user's UID, new login name, and user's
1214 * type for filesystem allocation (MR_FS_STUDENT, MR_FS_FACULTY,
1215 * MR_FS_STAFF, MR_FS_MISC).
1218 int register_user(struct query *q, char **argv, client *cl)
1220 EXEC SQL BEGIN DECLARE SECTION;
1221 char *login, *entity, directory[129], machname[33];
1222 int who, rowcount, mid, uid, users_id, utype, list_id;
1223 int ostatus, nstatus, fsidval;
1224 static int m_id = 0, def_quota = 0;
1225 EXEC SQL END DECLARE SECTION;
1226 char buffer[256], *aargv[3];
1228 entity = cl->entity;
1229 who = cl->client_id;
1231 uid = atoi(argv[0]);
1233 utype = atoi(argv[2]);
1236 EXEC SQL SELECT users_id, status INTO :users_id, :ostatus
1238 WHERE unix_uid = :uid AND (status = 0 OR status = 5 OR status = 6);
1240 if (sqlca.sqlerrd[2] == 0)
1242 if (sqlca.sqlerrd[2] > 1)
1243 return MR_NOT_UNIQUE;
1245 /* check new login name */
1246 EXEC SQL SELECT COUNT(login) INTO :rowcount FROM users
1247 WHERE login = :login AND users_id != :users_id;
1252 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM list
1253 WHERE name = :login;
1258 EXEC SQL SELECT COUNT(label) INTO :rowcount FROM filesys
1259 WHERE label = :login;
1264 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM alias
1265 WHERE name = :login AND type = 'FILESYS';
1270 com_err(whoami, 0, "login name OK");
1272 /* choose place for pobox, put in mid */
1273 EXEC SQL DECLARE csr130 CURSOR FOR
1274 SELECT sh.mach_id, m.name FROM serverhosts sh, machine m
1275 WHERE sh.service = 'POP' AND sh.mach_id = m.mach_id
1276 AND sh.value2 - sh.value1 = (SELECT MAX(value2 - value1) FROM serverhosts
1277 WHERE service = 'POP');
1280 EXEC SQL OPEN csr130;
1283 EXEC SQL FETCH csr130 INTO :mid, :machname;
1284 if (sqlca.sqlerrd[2] == 0)
1286 EXEC SQL CLOSE csr130;
1293 EXEC SQL CLOSE csr130;
1298 /* change login name, set pobox */
1299 sprintf(buffer, "u.users_id = %d", users_id);
1300 incremental_before(USERS_TABLE, buffer, 0);
1302 if (ostatus == 5 || ostatus == 6)
1304 EXEC SQL UPDATE users SET login = :login, status = :nstatus,
1305 modtime = SYSDATE, modby = :who, modwith = :entity, potype = 'POP',
1306 pop_id = :mid, pmodtime = SYSDATE, pmodby = :who, pmodwith = :entity
1307 WHERE users_id = :users_id;
1311 if (sqlca.sqlerrd[2] != 1)
1313 set_pop_usage(mid, 1);
1314 com_err(whoami, 0, "set login name to %s and pobox to %s", login,
1316 incremental_after(USERS_TABLE, buffer, 0);
1320 /* Cell Name (I know, it shouldn't be hard coded...) */
1321 strcpy(machname, "ATHENA.MIT.EDU");
1322 EXEC SQL SELECT mach_id INTO :m_id FROM machine
1323 WHERE name = :machname;
1326 EXEC SQL SELECT list_id INTO :list_id FROM list
1327 WHERE name = 'wheel';
1329 /* create filesystem */
1330 if (set_next_object_id("filsys_id", FILESYS_TABLE, 0))
1332 incremental_clear_before();
1333 if (islower(login[0]) && islower(login[1]))
1335 sprintf(directory, "/afs/athena.mit.edu/user/%c/%c/%s",
1336 login[0], login[1], login);
1339 sprintf(directory, "/afs/athena.mit.edu/user/other/%s", login);
1341 EXEC SQL SELECT value INTO :fsidval FROM numvalues
1342 WHERE numvalues.name = 'filsys_id';
1343 EXEC SQL INSERT INTO filesys
1344 (filsys_id, phys_id, label, type, mach_id, name,
1345 mount, rwaccess, comments, owner, owners, createflg,
1346 lockertype, modtime, modby, modwith)
1348 (:fsidval, 0, :login, 'AFS', :m_id, :directory,
1349 '/mit/' || :login, 'w', 'User Locker', :users_id, :list_id, 1,
1350 'HOMEDIR', SYSDATE, :who, :entity);
1354 if (sqlca.sqlerrd[2] != 1)
1356 sprintf(buffer, "fs.filsys_id = %d", fsidval);
1357 incremental_after(FILESYS_TABLE, buffer, 0);
1362 EXEC SQL SELECT value INTO :def_quota FROM numvalues
1363 WHERE name = 'def_quota';
1366 if (sqlca.sqlerrd[2] != 1)
1369 incremental_clear_before();
1370 EXEC SQL INSERT INTO quota
1371 (entity_id, filsys_id, type, quota, phys_id, modtime, modby, modwith)
1372 VALUES (0, :fsidval, 'ANY', :def_quota, 0, SYSDATE, :who, :entity);
1375 if (sqlca.sqlerrd[2] != 1)
1380 sprintf(buffer, "q.entity_id = 0 and q.filsys_id = %d and q.type = 'ANY'",
1382 incremental_after(QUOTA_TABLE, buffer, aargv);
1383 com_err(whoami, 0, "quota of %d assigned", def_quota);
1387 cache_entry(login, USERS_TABLE, users_id);
1389 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
1390 WHERE table_name = 'users';
1391 EXEC SQL UPDATE tblstats SET appends = appends + 1, modtime = SYSDATE
1392 WHERE table_name = 'filesys' OR table_name = 'quota';
1400 /** set_pop_usage - incr/decr usage count for pop server in serverhosts talbe
1404 ** delta (will be +/- 1)
1407 ** - incr/decr value field in serverhosts table for pop/mach_id
1411 int set_pop_usage(id, cnt)
1414 EXEC SQL BEGIN DECLARE SECTION;
1415 int iid = id, icnt = cnt;
1416 EXEC SQL END DECLARE SECTION;
1418 EXEC SQL UPDATE serverhosts SET value1 = value1 + :icnt
1419 WHERE serverhosts.service = 'POP' AND serverhosts.mach_id = :iid;