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 EXCHANGE, then box should be a
45 * machine, and its ID should be put in exchange_id. If type is NONE, then
49 int set_pobox(struct query *q, char **argv, client *cl)
51 EXEC SQL BEGIN DECLARE SECTION;
53 char *box, potype[USERS_POTYPE_SIZE];
54 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 (!strcmp(strtrim(potype), "SPLIT") && id))
67 set_pop_usage(id, -1);
69 sprintf(buffer, "u.users_id = %d", user);
70 incremental_before(USERS_TABLE, buffer, 0);
72 if (!strcmp(argv[1], "POP"))
74 status = name_to_id(box, MACHINE_TABLE, &id);
75 if (status == MR_NO_MATCH)
79 EXEC SQL UPDATE users SET potype = 'POP', pop_id = :id, imap_id = 0,
80 exchange_id = 0 WHERE users_id = :user;
83 else if (!strcmp(argv[1], "EXCHANGE"))
85 status = name_to_id(box, MACHINE_TABLE, &id);
86 if (status == MR_NO_MATCH)
90 EXEC SQL UPDATE users SET POTYPE = 'EXCHANGE', exchange_id = :id,
91 pop_id = 0, imap_id = 0 WHERE users_id = :user;
93 else if (!strcmp(argv[1], "SMTP") || !strcmp(argv[1], "SPLIT"))
95 if (strchr(box, '/') || strchr(box, '|'))
97 status = name_to_id(box, STRINGS_TABLE, &id);
98 if (status == MR_NO_MATCH)
103 /* If going from SMTP or NONE to SPLIT, make sure we have a valid
106 if ((!strcmp(potype, "SMTP") || !strcmp(potype, "NONE")) &&
107 !strcmp(argv[1], "SPLIT"))
109 status = set_pobox_pop(q, argv, cl);
113 strlcpy(potype, argv[1], sizeof(potype));
114 EXEC SQL UPDATE users SET potype = :potype, box_id = :id
115 WHERE users_id = :user;
117 else if (!strcmp(argv[1], "IMAP"))
119 EXEC SQL SELECT filsys_id INTO :id FROM filesys
120 WHERE label = :box AND type = 'IMAP';
123 EXEC SQL UPDATE users SET potype = 'IMAP', imap_id = :id, pop_id = 0,
124 exchange_id = 0 WHERE users_id = :user;
126 else /* argv[1] == "NONE" */
128 EXEC SQL UPDATE users SET potype = 'NONE'
129 WHERE users_id = :user;
132 incremental_after(USERS_TABLE, buffer, 0);
134 set_pobox_modtime(q, argv, cl);
135 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
136 WHERE table_name = 'users';
142 /* set_pobox_pop: Revert to existing POP, IMAP, or EXCHANGE pobox.
143 * Also take care of keeping track of the post office usage.
145 int set_pobox_pop(struct query *q, char **argv, client *cl)
147 EXEC SQL BEGIN DECLARE SECTION;
148 int id, pid, iid, mid, eid;
149 char type[USERS_POTYPE_SIZE];
150 EXEC SQL END DECLARE SECTION;
153 id = *(int *)argv[0];
154 EXEC SQL SELECT potype, pop_id, imap_id, exchange_id
155 INTO :type, :pid, :iid, :eid
156 FROM users WHERE users_id = :id;
157 if (sqlca.sqlerrd[2] == 0 || (pid == 0 && iid == 0 && eid == 0))
160 sprintf(buffer, "u.users_id = %d", id);
161 incremental_before(USERS_TABLE, buffer, 0);
165 EXEC SQL SELECT mach_id INTO :mid FROM machine
166 WHERE mach_id = :pid;
167 if (sqlca.sqlerrd[2] == 0)
169 EXEC SQL UPDATE users SET potype = 'POP' WHERE users_id = :id;
170 if (!strcmp(strtrim(type), "POP"))
171 set_pop_usage(mid, 1);
175 EXEC SQL SELECT filsys_id INTO :mid FROM filesys
176 WHERE filsys_id = :iid;
177 if (sqlca.sqlerrd[2] == 0)
179 EXEC SQL UPDATE users SET potype = 'IMAP' WHERE users_id = :id;
183 EXEC SQL SELECT mach_id INTO :mid FROM machine
184 WHERE mach_id = :eid;
185 if (sqlca.sqlerrd[2] == 0)
187 EXEC SQL UPDATE users SET potype = 'EXCHANGE' WHERE users_id = :id;
190 incremental_after(USERS_TABLE, buffer, 0);
192 set_pobox_modtime(q, argv, cl);
193 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
194 WHERE table_name = 'users';
201 /* Add_member_to_list: do list flattening as we go! MAXLISTDEPTH is
202 * how many different ancestors a member is allowed to have.
205 #define MAXLISTDEPTH 3072
207 int add_member_to_list(struct query *q, char **argv, client *cl)
209 EXEC SQL BEGIN DECLARE SECTION;
210 int id, lid, mid, tag, error, who, ref, rowcnt;
211 char *mtype, dtype[IMEMBERS_MEMBER_TYPE_SIZE], *entity;
212 EXEC SQL END DECLARE SECTION;
213 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
214 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
216 char *dtypes[MAXLISTDEPTH];
217 char *iargv[3], *buf;
219 lid = *(int *)argv[0];
221 mid = *(int *)argv[2];
222 tag = !strcmp(q->shortname, "atml") ? *(int *)argv[3] : 0;
224 if (acl_access_check(lid, cl))
227 /* if the member is already a direct member of the list, punt */
228 EXEC SQL SELECT COUNT(list_id) INTO :rowcnt FROM imembers
229 WHERE list_id = :lid AND member_id = :mid
230 AND member_type = :mtype AND direct = 1;
233 if (!strcasecmp(mtype, "STRING"))
236 status = id_to_name(mid, STRINGS_TABLE, &buf);
239 if (strchr(buf, '/') || strchr(buf, '|') || strchr(buf, ','))
250 EXEC SQL DECLARE csr103 CURSOR FOR
251 SELECT list_id, ref_count FROM imembers
252 WHERE member_id = :lid AND member_type = 'LIST';
255 EXEC SQL OPEN csr103;
260 EXEC SQL FETCH csr103 INTO :id, :ref;
264 ancestors[acount++] = id;
265 if (acount >= MAXLISTDEPTH)
268 EXEC SQL CLOSE csr103;
271 if (acount >= MAXLISTDEPTH)
273 descendants[0] = mid;
278 if (!strcmp(mtype, "LIST"))
280 EXEC SQL DECLARE csr104 CURSOR FOR
281 SELECT member_id, member_type, ref_count
283 WHERE list_id = :mid;
286 EXEC SQL OPEN csr104;
291 EXEC SQL FETCH csr104 INTO :id, :dtype, :ref;
297 dtypes[dcount] = "LIST";
300 dtypes[dcount] = "USER";
303 dtypes[dcount] = "STRING";
306 dtypes[dcount] = "KERBEROS";
309 dtypes[dcount] = "MACHINE";
316 descendants[dcount++] = id;
317 if (dcount >= MAXLISTDEPTH)
323 EXEC SQL CLOSE csr104;
329 for (a = 0; a < acount; a++)
332 for (d = 0; d < dcount; d++)
334 mid = descendants[d];
336 if (mid == lid && !strcmp(mtype, "LIST"))
338 EXEC SQL SELECT COUNT(ref_count) INTO :rowcnt
340 WHERE list_id = :lid AND member_id = :mid
341 AND member_type = :mtype;
342 ref = aref[a] * dref[d];
345 if (a == 0 && d == 0)
347 EXEC SQL UPDATE imembers
348 SET ref_count = ref_count + :ref, direct = 1, tag = :tag
349 WHERE list_id = :lid AND member_id = :mid
350 AND member_type = :mtype;
354 EXEC SQL UPDATE imembers
355 SET ref_count = ref_count + :ref
356 WHERE list_id = :lid AND member_id = :mid
357 AND member_type = :mtype;
362 incremental_clear_before();
363 if (a == 0 && d == 0)
365 EXEC SQL INSERT INTO imembers
366 (list_id, member_type, member_id, tag, direct, ref_count)
367 VALUES (:lid, :mtype, :mid, :tag, 1, :ref);
371 EXEC SQL INSERT INTO imembers
372 (list_id, member_type, member_id, tag, direct, ref_count)
373 VALUES (:lid, :mtype, :mid, :tag, 0, :ref);
375 iargv[0] = (char *)lid;
377 iargv[2] = (char *)mid;
378 incremental_after(IMEMBERS_TABLE, 0, iargv);
382 lid = *(int *)argv[0];
386 SET modtime = SYSDATE, modby = :who, modwith = :entity
387 WHERE list_id = :lid;
394 /* Delete_member_from_list: do list flattening as we go!
397 int delete_member_from_list(struct query *q, char **argv, client *cl)
399 EXEC SQL BEGIN DECLARE SECTION;
400 int id, lid, mid, cnt, error, who, ref;
401 char *mtype, dtype[IMEMBERS_MEMBER_TYPE_SIZE], *entity;
402 EXEC SQL END DECLARE SECTION;
403 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
404 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
405 char *dtypes[MAXLISTDEPTH];
408 lid = *(int *)argv[0];
410 mid = *(int *)argv[2];
412 if (acl_access_check(lid, cl))
415 /* if the member is not a direct member of the list, punt */
416 EXEC SQL SELECT COUNT(list_id) INTO :cnt FROM imembers
417 WHERE list_id = :lid AND member_id = :mid
418 AND member_type = :mtype AND direct = 1;
426 EXEC SQL DECLARE csr105 CURSOR FOR
427 SELECT list_id, ref_count FROM imembers
428 WHERE member_id = :lid AND member_type = 'LIST';
431 EXEC SQL OPEN csr105;
436 EXEC SQL FETCH csr105 INTO :id, :ref;
440 ancestors[acount++] = id;
441 if (acount >= MAXLISTDEPTH)
444 EXEC SQL CLOSE csr105;
447 if (acount >= MAXLISTDEPTH)
449 descendants[0] = mid;
454 if (!strcmp(mtype, "LIST"))
456 EXEC SQL DECLARE csr106 CURSOR FOR
457 SELECT member_id, member_type, ref_count FROM imembers
458 WHERE list_id = :mid;
461 EXEC SQL OPEN csr106;
466 EXEC SQL FETCH csr106 INTO :id, :dtype, :ref;
472 dtypes[dcount] = "LIST";
475 dtypes[dcount] = "USER";
478 dtypes[dcount] = "STRING";
481 dtypes[dcount] = "KERBEROS";
484 dtypes[dcount] = "MACHINE";
491 descendants[dcount++] = id;
492 if (dcount >= MAXLISTDEPTH)
495 EXEC SQL CLOSE csr106;
501 for (a = 0; a < acount; a++)
504 for (d = 0; d < dcount; d++)
506 mid = descendants[d];
508 if (mid == lid && !strcmp(mtype, "LIST"))
510 EXEC SQL SELECT ref_count INTO :cnt FROM imembers
511 WHERE list_id = :lid AND member_id = :mid AND member_type = :mtype;
512 ref = aref[a] * dref[d];
515 iargv[0] = (char *)lid;
517 iargv[2] = (char *)mid;
518 incremental_before(IMEMBERS_TABLE, 0, iargv);
519 EXEC SQL DELETE FROM imembers
520 WHERE list_id = :lid AND member_id = :mid
521 AND member_type= :mtype;
522 incremental_clear_after();
524 else if (a == 0 && d == 0)
526 EXEC SQL UPDATE imembers
527 SET ref_count = ref_count - :ref, direct = 0
528 WHERE list_id = :lid AND member_id = :mid
529 AND member_type = :mtype;
533 EXEC SQL UPDATE imembers
534 SET ref_count = ref_count - :ref
535 WHERE list_id = :lid AND member_id = :mid
536 AND member_type = :mtype;
540 lid = *(int *)argv[0];
543 EXEC SQL UPDATE list SET modtime = SYSDATE, modby = :who, modwith = :entity
544 WHERE list_id = :lid;
550 int tag_member_of_list(struct query *q, char **argv, client *cl)
552 EXEC SQL BEGIN DECLARE SECTION;
553 int lid, mid, cnt, tag;
555 EXEC SQL END DECLARE SECTION;
558 lid = *(int *)argv[0];
560 mid = *(int *)argv[2];
561 tag = *(int *)argv[3];
563 EXEC SQL SELECT COUNT(member_id) INTO :cnt FROM imembers
564 WHERE member_id = :mid AND member_type = :mtype AND
571 incremental_clear_before();
572 EXEC SQL UPDATE imembers SET tag = :tag WHERE list_id = :lid
573 AND member_type = :mtype AND member_id = :mid;
577 iargv[0] = (char *)lid;
579 iargv[2] = (char *)mid;
580 incremental_after(IMEMBERS_TABLE, 0, iargv);
585 /* Don't allow someone to add someone to a list which is the acl of a
586 * query unless they're on the list acl, even if they're on the amtl
587 * query acl! Also, don't allow someone proxying to add someone to a
590 int acl_access_check(int list_id, client *cl)
592 EXEC SQL BEGIN DECLARE SECTION;
593 int c1, c2, lid = list_id, acl_id, memacl_id;
594 char acl_type[LIST_ACL_TYPE_SIZE], memacl_type[LIST_ACL_TYPE_SIZE];
595 EXEC SQL END DECLARE SECTION;
597 /* Check if the list is directly a capacl */
598 EXEC SQL SELECT COUNT(list_id) INTO :c1 FROM capacls WHERE list_id=:lid;
600 /* Check if the list is a member (direct or indirect) of a list that
602 EXEC SQL SELECT COUNT(l1.list_id) INTO :c2 FROM list l1, list l2,
603 imembers im, capacls c WHERE c.list_id = l2.list_id AND
604 im.list_id = l2.list_id AND im.member_type = 'LIST' AND
605 im.member_id = l1.list_id AND l1.list_id = :lid;
607 if (c1 == 0 && c2 == 0)
613 EXEC SQL SELECT acl_type, acl_id, memacl_type, memacl_id
614 INTO :acl_type, :acl_id, :memacl_type, :memacl_id
615 FROM list WHERE list_id=:lid;
617 if (!find_member(acl_type, acl_id, cl))
619 if (!find_member(memacl_type, memacl_id, cl))
627 /* get_ace_use - given a type and a name, return a type and a name.
628 * The ace_type is one of "LIST", "USER", "RLIST", or "RUSER" in argv[0],
629 * and argv[1] will contain the ID of the entity in question. The R*
630 * types mean to recursively look at every containing list, not just
631 * when the object in question is a direct member. On return, the
632 * usage type will be one of LIST, SERVICE, FILESYS, QUOTA, QUERY, or ZEPHYR.
635 int get_ace_use(struct query *q, char *argv[], client *cl,
636 int (*action)(int, char *[], void *), void *actarg)
639 EXEC SQL BEGIN DECLARE SECTION;
642 EXEC SQL END DECLARE SECTION;
643 struct save_queue *sq;
646 aid = *(int *)argv[1];
647 if (!strcmp(atype, "LIST") || !strcmp(atype, "USER") ||
648 !strcmp(atype, "KERBEROS"))
649 return get_ace_internal(atype, aid, action, actarg);
652 if (!strcmp(atype, "RLIST"))
654 sq_save_data(sq, (void *)aid);
655 /* get all the list_id's of containing lists */
656 EXEC SQL DECLARE csr107 CURSOR FOR
657 SELECT list_id FROM imembers
658 WHERE member_type = 'LIST' AND member_id = :aid;
661 EXEC SQL OPEN csr107;
666 EXEC SQL FETCH csr107 INTO :listid;
669 sq_save_unique_data(sq, (void *)listid);
671 EXEC SQL CLOSE csr107;
672 /* now process each one */
673 while (sq_get_data(sq, &id))
675 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
680 if (!strcmp(atype, "RUSER"))
682 EXEC SQL DECLARE csr108 CURSOR FOR
683 SELECT list_id FROM imembers
684 WHERE member_type = 'USER' AND member_id = :aid;
687 EXEC SQL OPEN csr108;
692 EXEC SQL FETCH csr108 INTO :listid;
695 sq_save_data(sq, (void *)listid);
697 EXEC SQL CLOSE csr108;
698 /* now process each one */
699 while (sq_get_data(sq, &id))
701 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
704 if (get_ace_internal("USER", aid, action, actarg) == MR_SUCCESS)
708 if (!strcmp(atype, "RKERBEROS"))
710 EXEC SQL DECLARE csr109 CURSOR FOR
711 SELECT list_id FROM imembers
712 WHERE member_type = 'KERBEROS' AND member_id = :aid;
715 EXEC SQL OPEN csr109;
720 EXEC SQL FETCH csr109 INTO :listid;
723 sq_save_data(sq, (void *)listid);
725 EXEC SQL CLOSE csr109;
726 /* now process each one */
727 while (sq_get_data(sq, &id))
729 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
732 if (get_ace_internal("KERBEROS", aid, action, actarg) == MR_SUCCESS)
745 /* This looks up a single list or user for ace use. atype must be "USER"
746 * or "LIST", and aid is the ID of the corresponding object. This is used
747 * by get_ace_use above.
750 int get_ace_internal(char *atype, int aid,
751 int (*action)(int, char *[], void *), void *actarg)
755 EXEC SQL BEGIN DECLARE SECTION;
756 char name[MAX_FIELD_WIDTH], *type = atype;
758 EXEC SQL END DECLARE SECTION;
761 if (!strcmp(atype, "LIST"))
763 rargv[0] = "FILESYS";
764 EXEC SQL DECLARE csr110 CURSOR FOR
765 SELECT label FROM filesys
769 EXEC SQL OPEN csr110;
774 EXEC SQL FETCH csr110 INTO :name;
777 (*action)(2, rargv, actarg);
780 EXEC SQL CLOSE csr110;
783 EXEC SQL DECLARE csr111 CURSOR FOR
784 SELECT capability FROM capacls
788 EXEC SQL OPEN csr111;
793 EXEC SQL FETCH csr111 INTO :name;
796 (*action)(2, rargv, actarg);
799 EXEC SQL CLOSE csr111;
801 else if (!strcmp(atype, "USER"))
803 rargv[0] = "FILESYS";
804 EXEC SQL DECLARE csr112 CURSOR FOR
805 SELECT label FROM filesys
809 EXEC SQL OPEN csr112;
814 EXEC SQL FETCH csr112 INTO :name;
817 (*action)(2, rargv, actarg);
820 EXEC SQL CLOSE csr112;
824 EXEC SQL DECLARE csr113 CURSOR FOR
825 SELECT name FROM list
826 WHERE (acl_type = :type AND acl_id = :id)
827 OR (memacl_type = :type AND memacl_id = :id);
830 EXEC SQL OPEN csr113;
835 EXEC SQL FETCH csr113 INTO :name;
838 (*action)(2, rargv, actarg);
841 EXEC SQL CLOSE csr113;
843 rargv[0] = "SERVICE";
844 EXEC SQL DECLARE csr114 CURSOR FOR
845 SELECT name FROM servers
846 WHERE acl_type = :type AND acl_id = :id;
849 EXEC SQL OPEN csr114;
854 EXEC SQL FETCH csr114 INTO :name;
857 (*action)(2, rargv, actarg);
860 EXEC SQL CLOSE csr114;
862 rargv[0] = "HOSTACCESS";
863 EXEC SQL DECLARE csr115 CURSOR FOR
864 SELECT name FROM machine m, hostaccess ha
865 WHERE m.mach_id = ha.mach_id AND ha.acl_type = :type
869 EXEC SQL OPEN csr115;
874 EXEC SQL FETCH csr115 INTO :name;
877 (*action)(2, rargv, actarg);
880 EXEC SQL CLOSE csr115;
882 rargv[0] = "MACHINE";
883 EXEC SQL DECLARE csr115a CURSOR FOR
884 SELECT name FROM machine m
885 WHERE m.owner_type = :type
886 AND m.owner_id = :id;
889 EXEC SQL OPEN csr115a;
894 EXEC SQL FETCH csr115a INTO :name;
897 (*action)(2, rargv, actarg);
900 EXEC SQL CLOSE csr115a;
903 EXEC SQL DECLARE csr116 CURSOR FOR
904 SELECT class FROM zephyr z
905 WHERE z.xmt_type = :type AND z.xmt_id = :id
906 OR z.sub_type = :type AND z.sub_id = :id
907 OR z.iws_type = :type AND z.iws_id = :id
908 OR z.iui_type = :type AND z.iui_id = :id
909 OR z.owner_type = :type AND z.owner_id = :id;
912 EXEC SQL OPEN csr116;
917 EXEC SQL FETCH csr116 INTO :name;
920 (*action)(2, rargv, actarg);
923 EXEC SQL CLOSE csr116;
925 rargv[0] = "CONTAINER";
926 EXEC SQL DECLARE csr117c CURSOR FOR
927 SELECT name FROM containers c
928 WHERE c.acl_type = :type AND c.acl_id = :id;
931 EXEC SQL OPEN csr117c;
934 EXEC SQL FETCH csr117c INTO :name;
937 (*action)(2, rargv, actarg);
940 EXEC SQL CLOSE csr117c;
942 rargv[0] = "CONTAINER-MEMACL";
943 EXEC SQL DECLARE csr117d CURSOR FOR
944 SELECT name FROM containers c
945 WHERE c.memacl_type = :type AND c.memacl_id = :id;
948 EXEC SQL OPEN csr117d;
951 EXEC SQL FETCH csr117d INTO :name;
954 (*action)(2, rargv, actarg);
957 EXEC SQL CLOSE csr117d;
965 int ghbo_internal(char *atype, int aid,
966 int (*action)(int, char *[], void *), void *actarg)
970 EXEC SQL BEGIN DECLARE SECTION;
971 char name[MACHINE_NAME_SIZE], *type = atype;
973 EXEC SQL END DECLARE SECTION;
976 EXEC SQL DECLARE csr115b CURSOR FOR
977 SELECT name FROM machine m
978 WHERE m.owner_type = :type
979 AND m.owner_id = :id;
982 EXEC SQL OPEN csr115b;
987 EXEC SQL FETCH csr115b INTO :name;
990 (*action)(1, rargv, actarg);
993 EXEC SQL CLOSE csr115b;
1000 /* get_host_by_owner - like gaus but limited to hosts */
1001 int get_host_by_owner(struct query *q, char *argv[], client *cl,
1002 int (*action)(int, char *[], void *), void *actarg)
1005 EXEC SQL BEGIN DECLARE SECTION;
1007 int aid, listid, id;
1008 EXEC SQL END DECLARE SECTION;
1009 struct save_queue *sq;
1012 aid = *(int *)argv[1];
1013 if (!strcmp(atype, "LIST") || !strcmp(atype, "USER") ||
1014 !strcmp(atype, "KERBEROS"))
1015 return ghbo_internal(atype, aid, action, actarg);
1018 if (!strcmp(atype, "RLIST"))
1020 sq_save_data(sq, (void *)aid);
1021 /* get all the list_id's of containing lists */
1022 EXEC SQL DECLARE csr107q CURSOR FOR
1023 SELECT list_id FROM imembers
1024 WHERE member_type = 'LIST' AND member_id = :aid;
1027 EXEC SQL OPEN csr107q;
1032 EXEC SQL FETCH csr107q INTO :listid;
1035 sq_save_unique_data(sq, (void *)listid);
1037 EXEC SQL CLOSE csr107q;
1038 /* now process each one */
1039 while (sq_get_data(sq, &id))
1041 if (ghbo_internal("LIST", id, action, actarg) == MR_SUCCESS)
1046 if (!strcmp(atype, "RUSER"))
1048 EXEC SQL DECLARE csr108q CURSOR FOR
1049 SELECT list_id FROM imembers
1050 WHERE member_type = 'USER' AND member_id = :aid;
1053 EXEC SQL OPEN csr108q;
1058 EXEC SQL FETCH csr108q INTO :listid;
1061 sq_save_data(sq, (void *)listid);
1063 EXEC SQL CLOSE csr108q;
1064 /* now process each one */
1065 while (sq_get_data(sq, &id))
1067 if (ghbo_internal("LIST", id, action, actarg) == MR_SUCCESS)
1070 if (ghbo_internal("USER", aid, action, actarg) == MR_SUCCESS)
1074 if (!strcmp(atype, "RKERBEROS"))
1076 EXEC SQL DECLARE csr109q CURSOR FOR
1077 SELECT list_id FROM imembers
1078 WHERE member_type = 'KERBEROS' AND member_id = :aid;
1081 EXEC SQL OPEN csr109q;
1086 EXEC SQL FETCH csr109q INTO :listid;
1089 sq_save_data(sq, (void *)listid);
1091 EXEC SQL CLOSE csr109q;
1092 /* now process each one */
1093 while (sq_get_data(sq, &id))
1095 if (ghbo_internal("LIST", id, action, actarg) == MR_SUCCESS)
1098 if (ghbo_internal("KERBEROS", aid, action, actarg) == MR_SUCCESS)
1110 int guas_internal(char *atype, int aid,
1111 int (*action)(int, char *[], void *), void *actarg)
1115 EXEC SQL BEGIN DECLARE SECTION;
1116 char login[USERS_LOGIN_SIZE], *type = atype;
1118 EXEC SQL END DECLARE SECTION;
1121 EXEC SQL DECLARE csr115sp CURSOR FOR
1122 SELECT login FROM users u
1123 WHERE u.sponsor_type = :type
1124 AND u.sponsor_id = :id;
1127 EXEC SQL OPEN csr115sp;
1132 EXEC SQL FETCH csr115sp INTO :login;
1135 (*action)(1, rargv, actarg);
1138 EXEC SQL CLOSE csr115sp;
1145 /* get_user_account_by_sponsor - like gaus but limited to user accounts */
1146 int get_user_account_by_sponsor(struct query *q, char *argv[], client *cl,
1147 int (*action)(int, char *[], void *),
1151 EXEC SQL BEGIN DECLARE SECTION;
1153 int aid, listid, id;
1154 EXEC SQL END DECLARE SECTION;
1155 struct save_queue *sq;
1158 aid = *(int *)argv[1];
1159 if (!strcmp(atype, "LIST") || !strcmp(atype, "USER") ||
1160 !strcmp(atype, "KERBEROS"))
1161 return guas_internal(atype, aid, action, actarg);
1164 if (!strcmp(atype, "RLIST"))
1166 sq_save_data(sq, (void *)aid);
1167 /* get all the list_id's of containing lists */
1168 EXEC SQL DECLARE csr107sp CURSOR FOR
1169 SELECT list_id FROM imembers
1170 WHERE member_type = 'LIST' AND member_id = :aid;
1173 EXEC SQL OPEN csr107sp;
1178 EXEC SQL FETCH csr107sp INTO :listid;
1181 sq_save_unique_data(sq, (void *)listid);
1183 EXEC SQL CLOSE csr107sp;
1184 /* now process each one */
1185 while (sq_get_data(sq, &id))
1187 if (guas_internal("LIST", id, action, actarg) == MR_SUCCESS)
1192 if (!strcmp(atype, "RUSER"))
1194 EXEC SQL DECLARE csr108sp CURSOR FOR
1195 SELECT list_id FROM imembers
1196 WHERE member_type = 'USER' AND member_id = :aid;
1199 EXEC SQL OPEN csr108sp;
1204 EXEC SQL FETCH csr108sp INTO :listid;
1207 sq_save_data(sq, (void *)listid);
1209 EXEC SQL CLOSE csr108sp;
1210 /* now process each one */
1211 while (sq_get_data(sq, &id))
1213 if (guas_internal("LIST", id, action, actarg) == MR_SUCCESS)
1216 if (guas_internal("USER", aid, action, actarg) == MR_SUCCESS)
1220 if (!strcmp(atype, "RKERBEROS"))
1222 EXEC SQL DECLARE csr109sp CURSOR FOR
1223 SELECT list_id FROM imembers
1224 WHERE member_type = 'KERBEROS' AND member_id = :aid;
1227 EXEC SQL OPEN csr109sp;
1232 EXEC SQL FETCH csr109sp INTO :listid;
1235 sq_save_data(sq, (void *)listid);
1237 EXEC SQL CLOSE csr109sp;
1238 /* now process each one */
1239 while (sq_get_data(sq, &id))
1241 if (guas_internal("LIST", id, action, actarg) == MR_SUCCESS)
1244 if (guas_internal("KERBEROS", aid, action, actarg) == MR_SUCCESS)
1256 /* get_lists_of_member - given a type and a name, return the name and flags
1257 * of all of the lists of the given member. The member_type is one of
1258 * "LIST", "USER", "STRING", "KERBEROS", "MACHINE", "RLIST", "RUSER",
1259 * "RSTRING", "RKERBEROS", or "RMACHINE" in argv[0], and argv[1] will contain
1260 * the ID of the entity in question. The R* types mean to recursively look
1261 * at every containing list, not just when the object in question is a direct
1265 int get_lists_of_member(struct query *q, char *argv[], client *cl,
1266 int (*action)(int, char *[], void *), void *actarg)
1268 int found = 0, direct = 1;
1270 EXEC SQL BEGIN DECLARE SECTION;
1273 char name[LIST_NAME_SIZE];
1274 char active[5], public[5], hidden[5], maillist[5], grouplist[5];
1275 EXEC SQL END DECLARE SECTION;
1278 aid = *(int *)argv[1];
1279 if (!strcmp(atype, "RLIST"))
1284 if (!strcmp(atype, "RUSER"))
1289 if (!strcmp(atype, "RSTRING"))
1294 if (!strcmp(atype, "RKERBEROS"))
1299 if (!strcmp(atype, "RMACHINE"))
1309 rargv[4] = maillist;
1310 rargv[5] = grouplist;
1313 EXEC SQL DECLARE csr117a CURSOR FOR
1314 SELECT l.name, l.active, l.publicflg, l.hidden, l.maillist, l.grouplist
1315 FROM list l, imembers im
1316 WHERE l.list_id = im.list_id AND im.direct = 1
1317 AND im.member_type = :atype AND im.member_id = :aid;
1320 EXEC SQL OPEN csr117a;
1325 EXEC SQL FETCH csr117a
1326 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
1329 (*action)(6, rargv, actarg);
1332 EXEC SQL CLOSE csr117a;
1336 EXEC SQL DECLARE csr117b CURSOR FOR
1337 SELECT l.name, l.active, l.publicflg, l.hidden, l.maillist, l.grouplist
1338 FROM list l, imembers im
1339 WHERE l.list_id = im.list_id
1340 AND im.member_type = :atype AND im.member_id = :aid;
1343 EXEC SQL OPEN csr117b;
1348 EXEC SQL FETCH csr117b
1349 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
1352 (*action)(6, rargv, actarg);
1355 EXEC SQL CLOSE csr117b;
1366 /* qualified_get_lists: passed "TRUE", "FALSE", or "DONTCARE" for each of
1367 * the five flags associated with each list. It will return the name of
1368 * each list that meets the quailifications. It does this by building a
1369 * where clause based on the arguments, then doing a retrieve.
1372 static char *lflags[5] = { "active", "publicflg", "hidden", "maillist", "grouplist" };
1374 int qualified_get_lists(struct query *q, char *argv[], client *cl,
1375 int (*action)(int, char *[], void *), void *actarg)
1377 return qualified_get(q, argv, action, actarg, "l.list_id != 0",
1378 "l", "name", lflags);
1382 int get_members_of_list(struct query *q, char *argv[], client *cl,
1383 int (*action)(int, char *[], void *), void *actarg)
1385 EXEC SQL BEGIN DECLARE SECTION;
1386 int list_id, direct;
1387 char member_name[MAX_FIELD_WIDTH], tag[STRINGS_STRING_SIZE];
1388 EXEC SQL END DECLARE SECTION;
1392 /* For gmol or gtml, only get direct members. For geml, get all. */
1393 if (!strcmp(q->shortname, "geml"))
1398 /* For gmol or geml, only return type and name. For gtml, return tag too. */
1399 if (!strcmp(q->shortname, "gtml"))
1404 list_id = *(int *)argv[0];
1406 targv[1] = member_name;
1410 EXEC SQL DECLARE csr119 CURSOR FOR
1411 SELECT u.login, s.string FROM users u, imembers im, strings s
1412 WHERE im.list_id = :list_id AND im.member_type = 'USER'
1413 AND im.member_id = u.users_id AND im.direct > :direct
1414 AND s.string_id = im.tag ORDER BY 1;
1417 EXEC SQL OPEN csr119;
1422 EXEC SQL FETCH csr119 INTO :member_name, :tag;
1425 (*action)(targc, targv, actarg);
1427 EXEC SQL CLOSE csr119;
1432 EXEC SQL DECLARE csr120 CURSOR FOR
1433 SELECT l.name, s.string FROM list l, imembers im, strings s
1434 WHERE im.list_id = :list_id AND im.member_type = 'LIST'
1435 AND im.member_id = l.list_id AND im.direct > :direct
1436 AND s.string_id = im.tag ORDER BY 1;
1439 EXEC SQL OPEN csr120;
1444 EXEC SQL FETCH csr120 INTO :member_name, :tag;
1447 (*action)(targc, targv, actarg);
1449 EXEC SQL CLOSE csr120;
1453 targv[0] = "STRING";
1454 EXEC SQL DECLARE csr121 CURSOR FOR
1455 SELECT str.string, s.string FROM strings str, imembers im, strings s
1456 WHERE im.list_id = :list_id AND im.member_type = 'STRING'
1457 AND im.member_id = str.string_id AND im.direct > :direct
1458 AND s.string_id = im.tag ORDER BY 1;
1461 EXEC SQL OPEN csr121;
1466 EXEC SQL FETCH csr121 INTO :member_name, :tag;
1469 (*action)(targc, targv, actarg);
1471 EXEC SQL CLOSE csr121;
1475 targv[0] = "KERBEROS";
1476 EXEC SQL DECLARE csr122 CURSOR FOR
1477 SELECT str.string, s.string FROM strings str, imembers im, strings s
1478 WHERE im.list_id = :list_id AND im.member_type = 'KERBEROS'
1479 AND im.member_id = str.string_id AND im.direct > :direct
1480 AND s.string_id = im.tag ORDER BY 1;
1483 EXEC SQL OPEN csr122;
1488 EXEC SQL FETCH csr122 INTO :member_name, :tag;
1491 (*action)(targc, targv, actarg);
1493 EXEC SQL CLOSE csr122;
1497 targv[0] = "MACHINE";
1498 EXEC SQL DECLARE csr123 CURSOR FOR
1499 SELECT m.name, s.string FROM machine m, imembers im, strings s
1500 WHERE im.list_id = :list_id AND im.member_type = 'MACHINE'
1501 AND im.member_id = m.mach_id AND im.direct > :direct
1502 AND s.string_id = im.tag ORDER BY 1;
1505 EXEC SQL OPEN csr123;
1510 EXEC SQL FETCH csr123 INTO :member_name, :tag;
1513 (*action)(targc, targv, actarg);
1515 EXEC SQL CLOSE csr123;
1523 /* count_members_of_list: this is a simple query, but it cannot be done
1524 * through the dispatch table.
1527 int count_members_of_list(struct query *q, char *argv[], client *cl,
1528 int (*action)(int, char *[], void *), void *actarg)
1530 EXEC SQL BEGIN DECLARE SECTION;
1532 EXEC SQL END DECLARE SECTION;
1533 char *rargv[1], countbuf[5];
1535 list = *(int *)argv[0];
1536 rargv[0] = countbuf;
1537 EXEC SQL SELECT count (*) INTO :ct FROM imembers
1538 WHERE list_id = :list AND direct = 1;
1541 sprintf(countbuf, "%d", ct);
1542 (*action)(1, rargv, actarg);
1547 /* qualified_get_server: passed "TRUE", "FALSE", or "DONTCARE" for each of
1548 * the three flags associated with each service. It will return the name of
1549 * each service that meets the quailifications. It does this by building a
1550 * where clause based on the arguments, then doing a retrieve.
1553 static char *sflags[3] = { "enable", "inprogress", "harderror" };
1555 int qualified_get_server(struct query *q, char *argv[], client *cl,
1556 int (*action)(int, char *[], void *), void *actarg)
1558 return qualified_get(q, argv, action, actarg, "s.name is not null",
1559 "s", "name", sflags);
1560 /* of course, name will never be null, but we need something there
1561 to make qualified_get happy */
1565 /* generic qualified get routine, used by qualified_get_lists,
1566 * qualified_get_server, and qualified_get_serverhost.
1568 * start - a simple where clause, must not be empty
1569 * range - the name of the range variable
1570 * field - the field to return
1571 * flags - an array of strings, names of the flag variables
1574 int qualified_get(struct query *q, char *argv[],
1575 int (*action)(int, char *[], void *), void *actarg,
1576 char *start, char *range, char *field, char *flags[])
1582 strcpy(qual, start);
1583 for (i = 0; i < q->argc; i++)
1585 if (!strcmp(argv[i], "TRUE"))
1587 sprintf(buf, " AND %s.%s != 0", range, flags[i]);
1590 else if (!strcmp(argv[i], "FALSE"))
1592 sprintf(buf, " AND %s.%s = 0", range, flags[i]);
1597 sprintf(stmt_buf, "SELECT %s.%s FROM %s %s WHERE %s", range, field,
1598 table_name[q->rtable], range, qual);
1599 return do_for_all_rows(stmt_buf, 1, action, actarg);
1603 /* qualified_get_serverhost: passed "TRUE", "FALSE", or "DONTCARE" for each of
1604 * the five flags associated with each serverhost. It will return the name of
1605 * each service and host that meets the quailifications. It does this by
1606 * building a where clause based on the arguments, then doing a retrieve.
1609 static char *shflags[6] = { "service", "enable", "override", "success",
1610 "inprogress", "hosterror" };
1612 int qualified_get_serverhost(struct query *q, char *argv[], client *cl,
1613 int (*action)(int, char *[], void *),
1616 char qual[256], buf[32];
1619 sprintf(qual, "m.mach_id = sh.mach_id AND sh.service = UPPER('%s')",
1621 for (i = 1; i < q->argc; i++)
1623 if (!strcmp(argv[i], "TRUE"))
1625 sprintf(buf, " AND sh.%s != 0", shflags[i]);
1628 else if (!strcmp(argv[i], "FALSE"))
1630 sprintf(buf, " AND sh.%s = 0", shflags[i]);
1635 sprintf(stmt_buf, "SELECT sh.service, m.name FROM serverhosts sh, "
1636 "machine m WHERE %s", qual);
1637 return do_for_all_rows(stmt_buf, 2, action, actarg);
1641 /* register_user - change user's login name and allocate a pobox, group,
1642 * filesystem, and quota for them. The user's status must start out as 0,
1643 * and is left as 2. Arguments are: user's UID, new login name, and
1644 * pobox type ("POP" = POP, "IMAP" or numeric = IMAP, "EXCHANGE" = EXCHANGE)
1647 int register_user(struct query *q, char **argv, client *cl)
1649 EXEC SQL BEGIN DECLARE SECTION;
1650 char *login, *entity;
1651 char directory[FILESYS_NAME_SIZE], machname[MACHINE_NAME_SIZE];
1652 char dir[NFSPHYS_DIR_SIZE], *potype;
1653 int who, rowcount, mid, uid, users_id;
1654 int ostatus, nstatus, fsidval, popid;
1657 static int m_id, def_quota, def_imap_quota, list_id, exchange_id;
1658 EXEC SQL END DECLARE SECTION;
1659 char buffer[256], *aargv[3];
1663 EXEC SQL SELECT list_id INTO :list_id FROM list
1664 WHERE name = 'wheel';
1666 EXEC SQL SELECT mach_id INTO :m_id FROM machine
1667 WHERE name = 'ATHENA.MIT.EDU';
1669 EXEC SQL SELECT mach_id INTO :exchange_id FROM machine
1670 WHERE name = 'EXCHANGE.MIT.EDU';
1673 EXEC SQL SELECT value INTO :def_quota FROM numvalues
1674 WHERE name = 'def_quota';
1675 if (sqlca.sqlerrd[2] != 1)
1678 EXEC SQL SELECT value INTO :def_imap_quota FROM numvalues
1679 WHERE name = 'def_imap_quota';
1680 if (sqlca.sqlerrd[2] != 1)
1683 entity = cl->entity;
1684 who = cl->client_id;
1686 uid = atoi(argv[0]);
1691 EXEC SQL SELECT users_id, status INTO :users_id, :ostatus
1693 WHERE unix_uid = :uid AND
1694 (status = 0 OR status = 5 OR status = 6 OR status = 9);
1696 if (sqlca.sqlerrd[2] == 0)
1698 if (sqlca.sqlerrd[2] > 1)
1699 return MR_NOT_UNIQUE;
1701 /* check new login name */
1702 EXEC SQL SELECT COUNT(login) INTO :rowcount FROM users
1703 WHERE login = :login AND users_id != :users_id;
1709 /* Remove this check when we're allowing username reuse again. */
1710 EXEC SQL SELECT COUNT(login) INTO :rowcount FROM userhistory
1711 WHERE login = :login;
1717 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM list
1718 WHERE LOWER(name) = :login;
1723 EXEC SQL SELECT COUNT(label) INTO :rowcount FROM filesys
1724 WHERE label = :login;
1729 EXEC SQL SELECT COUNT(label) INTO :rowcount
1730 FROM filesys WHERE label = :login || '.po';
1735 EXEC SQL SELECT owner INTO :tmp FROM filesys
1736 WHERE label = :login || '.po';
1739 if ((ostatus == 0 || ostatus == 9) || (tmp != users_id))
1744 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM alias
1745 WHERE ( name = :login OR name = :login || '.po' )
1746 AND type = 'FILESYS';
1751 com_err(whoami, 0, "login name OK");
1753 EXEC SQL SELECT COUNT(potype) INTO :rowcount FROM users WHERE
1754 login = :login AND potype = 'POP';
1760 /* choose type and location for pobox */
1763 if (!strcmp(potype, "POP"))
1766 EXEC SQL DECLARE csr130 CURSOR FOR
1767 SELECT sh.mach_id, m.name FROM serverhosts sh, machine m
1768 WHERE sh.service = 'POP' AND sh.mach_id = m.mach_id
1769 AND sh.value2 - sh.value1 =
1770 (SELECT MAX(value2 - value1) FROM serverhosts WHERE service = 'POP');
1773 EXEC SQL OPEN csr130;
1776 EXEC SQL FETCH csr130 INTO :popid, :machname;
1777 if (sqlca.sqlerrd[2] == 0)
1779 EXEC SQL CLOSE csr130;
1786 EXEC SQL CLOSE csr130;
1791 EXEC SQL UPDATE users SET potype = 'POP', pop_id = :popid
1792 WHERE users_id = :users_id;
1793 com_err(whoami, 0, "pobox set to POP:%s", strtrim(machname));
1795 else if (!strcmp(potype, "EXCHANGE"))
1797 EXEC SQL UPDATE users SET potype = 'EXCHANGE',
1798 exchange_id = :exchange_id
1799 WHERE users_id = :users_id;
1800 com_err(whoami, 0, "pobox set to EXCHANGE:EXCHANGE.MIT.EDU");
1804 /* Select all IMAP nfsphys entries in order of increasing
1805 * (allocated - partsize). The partitions will almost always be
1806 * overallocated, but we choose the one that is the least
1811 EXEC SQL DECLARE csr_rusr_imap CURSOR FOR
1812 SELECT np.allocated - np.partsize, np.nfsphys_id, np.mach_id,
1813 np.dir, m.name FROM serverhosts sh, nfsphys np, machine m
1814 WHERE sh.service = 'POSTOFFICE' AND sh.mach_id = np.mach_id
1815 AND m.mach_id = np.mach_id
1819 EXEC SQL OPEN csr_rusr_imap;
1822 EXEC SQL FETCH csr_rusr_imap INTO :tmp, :npid, :mid, :dir, :machname;
1823 if (sqlca.sqlerrd[2] == 0)
1825 EXEC SQL CLOSE csr_rusr_imap;
1832 EXEC SQL CLOSE csr_rusr_imap;
1837 /* create filesystem */
1838 if (set_next_object_id("filsys_id", FILESYS_TABLE, 0))
1840 incremental_clear_before();
1842 EXEC SQL SELECT value INTO :popid FROM numvalues
1843 WHERE numvalues.name = 'filsys_id';
1844 EXEC SQL INSERT INTO filesys
1845 (filsys_id, phys_id, label, type, mach_id, name,
1846 mount, rwaccess, comments, owner, owners, createflg,
1847 lockertype, modtime, modby, modwith)
1849 (:popid, :npid, :login || '.po', 'IMAP', :mid, :dir,
1850 CHR(0), 'w', 'IMAP box', :users_id, :list_id, 1,
1851 'USER', SYSDATE, :who, :entity);
1855 if (sqlca.sqlerrd[2] != 1)
1857 sprintf(buffer, "fs.filsys_id = %d", popid);
1858 incremental_after(FILESYS_TABLE, buffer, 0);
1861 incremental_clear_before();
1862 EXEC SQL INSERT INTO quota
1863 (entity_id, filsys_id, type, quota, phys_id, modtime, modby, modwith)
1864 VALUES (:users_id, :popid, 'USER', :def_imap_quota, :npid,
1865 SYSDATE, :who, :entity);
1868 if (sqlca.sqlerrd[2] != 1)
1873 sprintf(buffer, "q.entity_id = %d and q.filsys_id = %d and "
1874 "q.type = 'USER'", users_id, popid);
1875 incremental_after(QUOTA_TABLE, buffer, aargv);
1879 EXEC SQL UPDATE nfsphys SET allocated = allocated + :def_imap_quota
1880 WHERE nfsphys_id = :npid;
1882 EXEC SQL UPDATE users SET potype = 'IMAP', imap_id = :popid
1883 WHERE users_id = :users_id;
1884 com_err(whoami, 0, "pobox set to IMAP:%s:%s", strtrim(machname),
1889 /* change login name, set pobox */
1890 sprintf(buffer, "u.users_id = %d", users_id);
1891 incremental_before(USERS_TABLE, buffer, 0);
1893 if (ostatus == 5 || ostatus == 6 || ostatus == 9)
1895 EXEC SQL UPDATE users SET login = :login, status = :nstatus,
1896 modtime = SYSDATE, modby = :who, modwith = :entity,
1897 pmodtime = SYSDATE, pmodby = :who, pmodwith = :entity,
1898 created = SYSDATE, creator = :who
1899 WHERE users_id = :users_id;
1903 if (sqlca.sqlerrd[2] != 1)
1906 /* Only update usage count if we created a POP pobox. */
1907 if (!strcmp(potype, "POP") && !po_exists)
1908 set_pop_usage(mid, 1);
1910 com_err(whoami, 0, "set login name to %s", login);
1911 incremental_after(USERS_TABLE, buffer, 0);
1913 /* create filesystem */
1914 if (set_next_object_id("filsys_id", FILESYS_TABLE, 0))
1916 incremental_clear_before();
1917 if (islower(login[0]) && islower(login[1]))
1919 sprintf(directory, "/afs/athena.mit.edu/user/%c/%c/%s",
1920 login[0], login[1], login);
1923 sprintf(directory, "/afs/athena.mit.edu/user/other/%s", login);
1925 EXEC SQL SELECT value INTO :fsidval FROM numvalues
1926 WHERE numvalues.name = 'filsys_id';
1927 EXEC SQL INSERT INTO filesys
1928 (filsys_id, phys_id, label, type, mach_id, name,
1929 mount, rwaccess, comments, owner, owners, createflg,
1930 lockertype, modtime, modby, modwith)
1932 (:fsidval, 0, :login, 'AFS', :m_id, :directory,
1933 '/mit/' || :login, 'w', 'User Locker', :users_id, :list_id, 1,
1934 'HOMEDIR', SYSDATE, :who, :entity);
1938 if (sqlca.sqlerrd[2] != 1)
1940 sprintf(buffer, "fs.filsys_id = %d", fsidval);
1941 incremental_after(FILESYS_TABLE, buffer, 0);
1944 incremental_clear_before();
1945 EXEC SQL INSERT INTO quota
1946 (entity_id, filsys_id, type, quota, phys_id, modtime, modby, modwith)
1947 VALUES (0, :fsidval, 'ANY', :def_quota, 0, SYSDATE, :who, :entity);
1950 if (sqlca.sqlerrd[2] != 1)
1955 sprintf(buffer, "q.entity_id = 0 and q.filsys_id = %d and q.type = 'ANY'",
1957 incremental_after(QUOTA_TABLE, buffer, aargv);
1958 com_err(whoami, 0, "quota of %d assigned", def_quota);
1962 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
1963 WHERE table_name = 'users';
1964 EXEC SQL UPDATE tblstats SET appends = appends + 1, modtime = SYSDATE
1965 WHERE table_name = 'filesys' OR table_name = 'quota';
1971 /** set_pop_usage - incr/decr usage count for pop server in serverhosts talbe
1975 ** delta (will be +/- 1)
1978 ** - incr/decr value field in serverhosts table for pop/mach_id
1982 int set_pop_usage(id, cnt)
1985 EXEC SQL BEGIN DECLARE SECTION;
1986 int iid = id, icnt = cnt;
1987 EXEC SQL END DECLARE SECTION;
1989 EXEC SQL UPDATE serverhosts SET value1 = value1 + :icnt
1990 WHERE serverhosts.service = 'POP' AND serverhosts.mach_id = :iid;
1998 int do_user_reservation(struct query *q, char *argv[], client *cl)
2000 EXEC SQL BEGIN DECLARE SECTION;
2001 char resv[USERS_RESERVATIONS_SIZE];
2002 char *trans, name[ALIAS_NAME_SIZE];
2004 EXEC SQL END DECLARE SECTION;
2006 uid = *(int *)argv[0];
2009 EXEC SQL SELECT UPPER(name) INTO :name FROM alias
2010 WHERE type = 'RESERVE' AND LOWER(trans) = LOWER(:trans);
2013 if (sqlca.sqlerrd[2] != 1)
2017 EXEC SQL SELECT reservations INTO :resv FROM users
2018 WHERE users_id = :uid;
2023 if (!strcmp(q->shortname, "aurv"))
2025 if (strchr(resv, *name))
2027 if (strlen(resv) == USERS_RESERVATIONS_SIZE - 1)
2028 return MR_ARG_TOO_LONG;
2034 char *p = strchr(resv, *name);
2037 memmove(p, p + 1, strlen(p));
2040 EXEC SQL UPDATE users SET reservations = NVL(:resv, CHR(0))
2041 WHERE users_id = :uid;
2045 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
2046 WHERE table_name = 'users';
2047 return set_modtime_by_id(q, argv, cl);
2050 int get_user_reservations(struct query *q, char **argv, client *cl,
2051 int (*action)(int, char *[], void *), void *actarg)
2053 EXEC SQL BEGIN DECLARE SECTION;
2054 char resv[USERS_RESERVATIONS_SIZE], *p;
2055 char trans[ALIAS_TRANS_SIZE], name[2] = { 0, 0 };
2057 EXEC SQL END DECLARE SECTION;
2060 uid = *(int *)argv[0];
2062 EXEC SQL SELECT reservations INTO :resv FROM users
2063 WHERE users_id = :uid;
2069 while (*p && !isspace(*p))
2071 name[0] = toupper(*p);
2072 EXEC SQL SELECT trans INTO :trans FROM alias
2073 WHERE type = 'RESERVE' AND UPPER(name) = :name;
2076 if (sqlca.sqlerrd[2] != 1)
2077 sprintf(trans, "Unknown (%s)", name);
2078 (*action)(1, targv, actarg);
2084 int get_user_by_reservation(struct query *q, char **argv, client *cl,
2085 int (*action)(int, char *[], void *), void *actarg)
2087 EXEC SQL BEGIN DECLARE SECTION;
2088 char resv[USERS_RESERVATIONS_SIZE], login[USERS_LOGIN_SIZE];
2089 char *trans, name[ALIAS_NAME_SIZE];
2091 EXEC SQL END DECLARE SECTION;
2096 EXEC SQL SELECT UPPER(name) INTO :name FROM alias
2097 WHERE type = 'RESERVE' AND LOWER(trans) = LOWER(:trans);
2100 if (sqlca.sqlerrd[2] != 1)
2104 EXEC SQL DECLARE csr_gubr CURSOR FOR
2105 SELECT login FROM users WHERE reservations LIKE '%' || :name || '%';
2106 EXEC SQL OPEN csr_gubr;
2113 EXEC SQL FETCH csr_gubr INTO :login;
2116 (*action)(1, targv, actarg);
2118 EXEC SQL CLOSE csr_gubr;
2123 int update_container(struct query *q, char *argv[], client *cl)
2125 EXEC SQL BEGIN DECLARE SECTION;
2126 int cnt_id, acl_id, memacl_id, who, flag;
2127 char name[CONTAINERS_NAME_SIZE], newchildname[CONTAINERS_NAME_SIZE];
2128 char* newname, *entity, *description, *location, *contact, *acl_type, *memacl_type;
2129 EXEC SQL END DECLARE SECTION;
2132 char childname[CONTAINERS_NAME_SIZE];
2136 cnt_id = *(int *)argv[index++];
2137 newname = argv[index++];
2139 if (q->version >= 9)
2140 flag = atoi(argv[index++]);
2142 description = argv[index++];
2143 location = argv[index++];
2144 contact = argv[index++];
2145 acl_type = argv[index++];
2146 acl_id = *(int *)argv[index++];
2147 memacl_type = argv[index++];
2148 memacl_id = *(int *)argv[index++];
2149 entity = cl->entity;
2150 who = cl->client_id;
2152 EXEC SQL SELECT name INTO :name
2154 WHERE cnt_id = :cnt_id;
2156 /* trim off the trailing spaces */
2157 strcpy(name, strtrim(name));
2159 qual = xmalloc(MAX_FIELD_WIDTH);
2160 sprintf(qual, "name = '%s'", name);
2161 incremental_before(CONTAINERS_TABLE, qual, argv);
2163 /* if we are renaming the container */
2164 if (strcmp(name, newname))
2166 /* make sure that only the name part of the name has been changed */
2167 tmpchar = strrchr(name, '/');
2168 /* not a top-level name */
2171 cnt = tmpchar - name + 1;
2172 /* the parent part of the old and new name should be identical */
2173 if (strncmp(name, newname, cnt))
2174 return MR_NEW_CONTAINER_NAME;
2176 /* top level name, new name should be a top level name too */
2179 if (strrchr(newname, '/'))
2180 return MR_NEW_CONTAINER_NAME;
2183 /* update the name for this container */
2184 EXEC SQL UPDATE containers
2186 WHERE cnt_id = :cnt_id;
2191 /* get names for its child containers */
2192 EXEC SQL DECLARE csr_ucon CURSOR FOR
2193 SELECT name, cnt_id FROM containers WHERE name LIKE :name || '/' || '%';
2195 EXEC SQL OPEN csr_ucon;
2201 EXEC SQL FETCH csr_ucon INTO :childname, :childid;
2205 strcpy(childname, strtrim(childname));
2206 /* concatenate the new parent name with the existing sub-container name
2207 * we get the sub-containers new name */
2208 tmpchar = childname + strlen(name);
2209 strcpy(newchildname, newname);
2210 strcat(newchildname, tmpchar);
2212 /* update the name */
2213 EXEC SQL UPDATE containers
2214 SET name = :newchildname, modtime = SYSDATE, modby = :who, modwith = :entity
2215 WHERE cnt_id = :childid;
2221 EXEC SQL CLOSE csr_ucon;
2226 /* update the remaining fields */
2227 if (q->version >= 9)
2229 EXEC SQL UPDATE containers
2230 SET publicflg= :flag, description = NVL(:description, CHR(0)), location = NVL(:location, CHR(0)),
2231 contact = NVL(:contact, CHR(0)), acl_type = :acl_type, acl_id = :acl_id,
2232 memacl_type = :memacl_type, memacl_id = :memacl_id,
2233 modtime = SYSDATE, modby = :who, modwith = :entity
2234 WHERE cnt_id = :cnt_id;
2238 EXEC SQL UPDATE containers
2239 SET publicflg= :flag, description = NVL(:description, CHR(0)), location = NVL(:location, CHR(0)),
2240 contact = NVL(:contact, CHR(0)), acl_type = :acl_type, acl_id = :acl_id,
2241 memacl_type = :memacl_type, memacl_id = :memacl_id,
2242 modtime = SYSDATE, modby = :who, modwith = :entity
2243 WHERE cnt_id = :cnt_id;
2249 sprintf(qual, "name = '%s'", newname);
2250 incremental_after(CONTAINERS_TABLE, qual, argv);
2255 int get_machines_of_container(struct query *q, char *argv[], client *cl,
2256 int (*action)(int, char *[], void *), void *actarg)
2258 EXEC SQL BEGIN DECLARE SECTION;
2259 int cnt_id, isrecursive;
2260 char machinename[MACHINE_NAME_SIZE], containername[CONTAINERS_NAME_SIZE];
2262 EXEC SQL END DECLARE SECTION;
2264 char querystring[512], tmp [256];
2268 rargv[0] = machinename;
2269 rargv[1] = containername;
2271 cnt_id = *(int *)argv[0];
2272 isrecursive = atoi(argv[1]);
2274 /* get the container name */
2276 EXEC SQL SELECT name INTO :containername
2278 WHERE cnt_id = :cnt_id;
2280 /* trim off the trailing spaces */
2281 strcpy(containername, strtrim(containername));
2283 strcpy(querystring, "SELECT a.name, b.name FROM machine a, containers b, mcntmap c ");
2284 strcat(querystring, "WHERE a.mach_id = c.mach_id AND b.cnt_id = c.cnt_id ");
2287 sprintf(tmp, "AND b.cnt_id = %d ", cnt_id);
2289 sprintf(tmp, "AND (b.cnt_id = %d OR LOWER(b.name) LIKE LOWER('%s/%%')) ",
2290 cnt_id, containername);
2292 strcat(querystring, tmp);
2293 strcat(querystring, "ORDER BY b.name, a.name");
2297 EXEC SQL PREPARE stmt FROM :qs;
2300 EXEC SQL DECLARE curs_gmnm CURSOR FOR stmt;
2301 EXEC SQL OPEN curs_gmnm;
2305 EXEC SQL FETCH curs_gmnm INTO :machinename, :containername;
2308 (*action)(2, rargv, actarg);
2312 EXEC SQL CLOSE curs_gmnm;
2318 int get_subcontainers_of_container(struct query *q, char *argv[], client *cl,
2319 int (*action)(int, char *[], void *), void *actarg)
2321 EXEC SQL BEGIN DECLARE SECTION;
2322 int cnt_id, isrecursive;
2323 char containername[CONTAINERS_NAME_SIZE], subcontainername[CONTAINERS_NAME_SIZE];
2325 EXEC SQL END DECLARE SECTION;
2327 char querystring[2048], tmp [1024];
2331 rargv[0] = subcontainername;
2333 cnt_id = *(int *)argv[0];
2334 isrecursive = atoi(argv[1]);
2336 /* get the container name */
2338 EXEC SQL SELECT name INTO :containername
2340 WHERE cnt_id = :cnt_id;
2342 /* trim off the trailing spaces */
2343 strcpy(containername, strtrim(containername));
2345 strcpy(querystring, "SELECT name FROM containers ");
2348 sprintf(tmp, "WHERE LOWER(name) LIKE LOWER('%s/%%') and LOWER(name) NOT LIKE LOWER('%s/%%/%%') ",
2349 containername, containername);
2351 sprintf(tmp, "WHERE LOWER(name) LIKE LOWER('%s/%%') ", containername);
2353 strcat(querystring, tmp);
2354 strcat(querystring, "ORDER BY name");
2358 EXEC SQL PREPARE stmt FROM :qs;
2361 EXEC SQL DECLARE curs_gsoc CURSOR FOR stmt;
2362 EXEC SQL OPEN curs_gsoc;
2366 EXEC SQL FETCH curs_gsoc INTO :subcontainername;
2369 (*action)(1, rargv, actarg);
2373 EXEC SQL CLOSE curs_gsoc;
2379 int set_container_list(struct query *q, char *argv[], client *cl)
2381 EXEC SQL BEGIN DECLARE SECTION;
2382 int cnt_id, list_id;
2383 EXEC SQL END DECLARE SECTION;
2385 cnt_id = *(int *)argv[0];
2386 list_id = *(int *)argv[1];
2388 EXEC SQL UPDATE containers SET list_id = :list_id WHERE cnt_id = :cnt_id;