3 * Special query routines
5 * Copyright (C) 1987-1998 by the Massachusetts Institute of Technology
6 * For copying and distribution information, please see the file
10 #include <mit-copyright.h>
11 #include "mr_server.h"
19 EXEC SQL INCLUDE sqlca;
23 extern char *whoami, *table_name[];
24 extern int dbms_errno, mr_errcode;
26 EXEC SQL BEGIN DECLARE SECTION;
27 extern char stmt_buf[];
28 EXEC SQL END DECLARE SECTION;
30 EXEC SQL WHENEVER SQLERROR DO dbmserr();
32 int get_ace_internal(char *atypex, int aid,
33 int (*action)(int, char *[], void *), void *actarg);
34 int qualified_get(struct query *q, char *argv[],
35 int (*action)(int, char *[], void *), void *actarg,
36 char *start, char *range, char *field, char *flags[]);
39 /* set_pobox - this does all of the real work.
40 * argv = user_id, type, box
41 * if type is POP, then box should be a machine, and its ID should be put in
42 * pop_id. If type is IMAP, then box should be a filesys, and its ID should
43 * be put in pop_id. If type is SMTP, then box should be a string and its
44 * ID should be put in box_id. If type is NONE, then box doesn't matter.
47 int set_pobox(struct query *q, char **argv, client *cl)
49 EXEC SQL BEGIN DECLARE SECTION;
51 char *box, potype[USERS_POTYPE_SIZE];
52 EXEC SQL END DECLARE SECTION;
56 user = *(int *)argv[0];
58 EXEC SQL SELECT pop_id, potype INTO :id, :potype FROM users
59 WHERE users_id = :user;
62 if (!strcmp(strtrim(potype), "POP") ||
63 (!strcmp(strtrim(potype), "SPLIT") && id))
64 set_pop_usage(id, -1);
66 if (!strcmp(argv[1], "POP"))
68 status = name_to_id(box, MACHINE_TABLE, &id);
69 if (status == MR_NO_MATCH)
73 EXEC SQL UPDATE users SET potype = 'POP', pop_id = :id, imap_id = 0
74 WHERE users_id = :user;
77 else if (!strcmp(argv[1], "SMTP") || !strcmp(argv[1], "SPLIT"))
79 if (strchr(box, '/') || strchr(box, '|'))
81 status = name_to_id(box, STRINGS_TABLE, &id);
82 if (status == MR_NO_MATCH)
87 /* If going from SMTP or NONE to SPLIT, make sure we have a valid
90 if ((!strcmp(potype, "SMTP") || !strcmp(potype, "NONE")) &&
91 !strcmp(argv[1], "SPLIT"))
93 status = set_pobox_pop(q, argv, cl);
97 strlcpy(potype, argv[1], sizeof(potype));
98 EXEC SQL UPDATE users SET potype = :potype, box_id = :id
99 WHERE users_id = :user;
101 else if (!strcmp(argv[1], "IMAP"))
103 EXEC SQL SELECT filsys_id INTO :id FROM filesys
104 WHERE label = :box AND type = 'IMAP';
107 EXEC SQL UPDATE users SET potype = 'IMAP', imap_id = :id, pop_id = 0
108 WHERE users_id = :user;
110 else /* argv[1] == "NONE" */
112 EXEC SQL UPDATE users SET potype = 'NONE'
113 WHERE users_id = :user;
116 set_pobox_modtime(q, argv, cl);
117 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
118 WHERE table_name = 'users';
124 /* set_pobox_pop: Revert to existing POP or IMAP pobox.
125 * Also take care of keeping track of the post office usage.
127 int set_pobox_pop(struct query *q, char **argv, client *cl)
129 EXEC SQL BEGIN DECLARE SECTION;
130 int id, pid, iid, mid;
131 char type[USERS_POTYPE_SIZE];
132 EXEC SQL END DECLARE SECTION;
134 id = *(int *)argv[0];
135 EXEC SQL SELECT potype, pop_id, imap_id INTO :type, :pid, :iid
136 FROM users WHERE users_id = :id;
137 if (sqlca.sqlerrd[2] == 0 || (pid == 0 && iid == 0))
142 EXEC SQL SELECT mach_id INTO :mid FROM machine
143 WHERE mach_id = :pid;
144 if (sqlca.sqlerrd[2] == 0)
146 EXEC SQL UPDATE users SET potype = 'POP' WHERE users_id = :id;
147 if (!strcmp(strtrim(type), "POP"))
148 set_pop_usage(mid, 1);
152 EXEC SQL SELECT filsys_id INTO :mid FROM filesys
153 WHERE filsys_id = :iid;
154 if (sqlca.sqlerrd[2] == 0)
156 EXEC SQL UPDATE users SET potype = 'IMAP' WHERE users_id = :id;
159 set_pobox_modtime(q, argv, cl);
160 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
161 WHERE table_name = 'users';
168 /* Add_member_to_list: do list flattening as we go! MAXLISTDEPTH is
169 * how many different ancestors a member is allowed to have.
172 #define MAXLISTDEPTH 3072
174 int add_member_to_list(struct query *q, char **argv, client *cl)
176 EXEC SQL BEGIN DECLARE SECTION;
177 int id, lid, mid, tag, error, who, ref, rowcnt;
178 char *mtype, dtype[IMEMBERS_MEMBER_TYPE_SIZE], *entity;
179 EXEC SQL END DECLARE SECTION;
180 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
181 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
183 char *dtypes[MAXLISTDEPTH];
184 char *iargv[3], *buf;
186 lid = *(int *)argv[0];
188 mid = *(int *)argv[2];
189 tag = !strcmp(q->shortname, "atml") ? *(int *)argv[3] : 0;
191 if (acl_access_check(lid, cl))
194 /* if the member is already a direct member of the list, punt */
195 EXEC SQL SELECT COUNT(list_id) INTO :rowcnt FROM imembers
196 WHERE list_id = :lid AND member_id = :mid
197 AND member_type = :mtype AND direct = 1;
200 if (!strcasecmp(mtype, "STRING"))
203 status = id_to_name(mid, STRINGS_TABLE, &buf);
206 if (strchr(buf, '/') || strchr(buf, '|') || strchr(buf, ','))
217 EXEC SQL DECLARE csr103 CURSOR FOR
218 SELECT list_id, ref_count FROM imembers
219 WHERE member_id = :lid AND member_type = 'LIST';
222 EXEC SQL OPEN csr103;
227 EXEC SQL FETCH csr103 INTO :id, :ref;
231 ancestors[acount++] = id;
232 if (acount >= MAXLISTDEPTH)
235 EXEC SQL CLOSE csr103;
238 if (acount >= MAXLISTDEPTH)
240 descendants[0] = mid;
245 if (!strcmp(mtype, "LIST"))
247 EXEC SQL DECLARE csr104 CURSOR FOR
248 SELECT member_id, member_type, ref_count
250 WHERE list_id = :mid;
253 EXEC SQL OPEN csr104;
258 EXEC SQL FETCH csr104 INTO :id, :dtype, :ref;
264 dtypes[dcount] = "LIST";
267 dtypes[dcount] = "USER";
270 dtypes[dcount] = "STRING";
273 dtypes[dcount] = "KERBEROS";
276 dtypes[dcount] = "MACHINE";
283 descendants[dcount++] = id;
284 if (dcount >= MAXLISTDEPTH)
290 EXEC SQL CLOSE csr104;
296 for (a = 0; a < acount; a++)
299 for (d = 0; d < dcount; d++)
301 mid = descendants[d];
303 if (mid == lid && !strcmp(mtype, "LIST"))
305 EXEC SQL SELECT COUNT(ref_count) INTO :rowcnt
307 WHERE list_id = :lid AND member_id = :mid
308 AND member_type = :mtype;
309 ref = aref[a] * dref[d];
312 if (a == 0 && d == 0)
314 EXEC SQL UPDATE imembers
315 SET ref_count = ref_count + :ref, direct = 1, tag = :tag
316 WHERE list_id = :lid AND member_id = :mid
317 AND member_type = :mtype;
321 EXEC SQL UPDATE imembers
322 SET ref_count = ref_count + :ref
323 WHERE list_id = :lid AND member_id = :mid
324 AND member_type = :mtype;
329 incremental_clear_before();
330 if (a == 0 && d == 0)
332 EXEC SQL INSERT INTO imembers
333 (list_id, member_type, member_id, tag, direct, ref_count)
334 VALUES (:lid, :mtype, :mid, :tag, 1, :ref);
338 EXEC SQL INSERT INTO imembers
339 (list_id, member_type, member_id, tag, direct, ref_count)
340 VALUES (:lid, :mtype, :mid, :tag, 0, :ref);
342 iargv[0] = (char *)lid;
344 iargv[2] = (char *)mid;
345 incremental_after(IMEMBERS_TABLE, 0, iargv);
349 lid = *(int *)argv[0];
353 SET modtime = SYSDATE, modby = :who, modwith = :entity
354 WHERE list_id = :lid;
361 /* Delete_member_from_list: do list flattening as we go!
364 int delete_member_from_list(struct query *q, char **argv, client *cl)
366 EXEC SQL BEGIN DECLARE SECTION;
367 int id, lid, mid, cnt, error, who, ref;
368 char *mtype, dtype[IMEMBERS_MEMBER_TYPE_SIZE], *entity;
369 EXEC SQL END DECLARE SECTION;
370 int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
371 int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
372 char *dtypes[MAXLISTDEPTH];
375 lid = *(int *)argv[0];
377 mid = *(int *)argv[2];
379 if (acl_access_check(lid, cl))
382 /* if the member is not a direct member of the list, punt */
383 EXEC SQL SELECT COUNT(list_id) INTO :cnt FROM imembers
384 WHERE list_id = :lid AND member_id = :mid
385 AND member_type = :mtype AND direct = 1;
393 EXEC SQL DECLARE csr105 CURSOR FOR
394 SELECT list_id, ref_count FROM imembers
395 WHERE member_id = :lid AND member_type = 'LIST';
398 EXEC SQL OPEN csr105;
403 EXEC SQL FETCH csr105 INTO :id, :ref;
407 ancestors[acount++] = id;
408 if (acount >= MAXLISTDEPTH)
411 EXEC SQL CLOSE csr105;
414 if (acount >= MAXLISTDEPTH)
416 descendants[0] = mid;
421 if (!strcmp(mtype, "LIST"))
423 EXEC SQL DECLARE csr106 CURSOR FOR
424 SELECT member_id, member_type, ref_count FROM imembers
425 WHERE list_id = :mid;
428 EXEC SQL OPEN csr106;
433 EXEC SQL FETCH csr106 INTO :id, :dtype, :ref;
439 dtypes[dcount] = "LIST";
442 dtypes[dcount] = "USER";
445 dtypes[dcount] = "STRING";
448 dtypes[dcount] = "KERBEROS";
451 dtypes[dcount] = "MACHINE";
458 descendants[dcount++] = id;
459 if (dcount >= MAXLISTDEPTH)
462 EXEC SQL CLOSE csr106;
468 for (a = 0; a < acount; a++)
471 for (d = 0; d < dcount; d++)
473 mid = descendants[d];
475 if (mid == lid && !strcmp(mtype, "LIST"))
477 EXEC SQL SELECT ref_count INTO :cnt FROM imembers
478 WHERE list_id = :lid AND member_id = :mid AND member_type = :mtype;
479 ref = aref[a] * dref[d];
482 iargv[0] = (char *)lid;
484 iargv[2] = (char *)mid;
485 incremental_before(IMEMBERS_TABLE, 0, iargv);
486 EXEC SQL DELETE FROM imembers
487 WHERE list_id = :lid AND member_id = :mid
488 AND member_type= :mtype;
489 incremental_clear_after();
491 else if (a == 0 && d == 0)
493 EXEC SQL UPDATE imembers
494 SET ref_count = ref_count - :ref, direct = 0
495 WHERE list_id = :lid AND member_id = :mid
496 AND member_type = :mtype;
500 EXEC SQL UPDATE imembers
501 SET ref_count = ref_count - :ref
502 WHERE list_id = :lid AND member_id = :mid
503 AND member_type = :mtype;
507 lid = *(int *)argv[0];
510 EXEC SQL UPDATE list SET modtime = SYSDATE, modby = :who, modwith = :entity
511 WHERE list_id = :lid;
517 int tag_member_of_list(struct query *q, char **argv, client *cl)
519 EXEC SQL BEGIN DECLARE SECTION;
520 int lid, mid, cnt, tag;
522 EXEC SQL END DECLARE SECTION;
525 lid = *(int *)argv[0];
527 mid = *(int *)argv[2];
528 tag = *(int *)argv[3];
530 EXEC SQL SELECT COUNT(member_id) INTO :cnt FROM imembers
531 WHERE member_id = :mid AND member_type = :mtype AND
538 incremental_clear_before();
539 EXEC SQL UPDATE imembers SET tag = :tag WHERE list_id = :lid
540 AND member_type = :mtype AND member_id = :mid;
544 iargv[0] = (char *)lid;
546 iargv[2] = (char *)mid;
547 incremental_after(IMEMBERS_TABLE, 0, iargv);
552 /* Don't allow someone to add someone to a list which is the acl of a
553 * query unless they're on the list acl, even if they're on the amtl
554 * query acl! Also, don't allow someone proxying to add someone to a
557 int acl_access_check(int list_id, client *cl)
559 EXEC SQL BEGIN DECLARE SECTION;
560 int c1, c2, lid = list_id, acl_id, memacl_id;
561 char acl_type[LIST_ACL_TYPE_SIZE], memacl_type[LIST_ACL_TYPE_SIZE];
562 EXEC SQL END DECLARE SECTION;
564 /* Check if the list is directly a capacl */
565 EXEC SQL SELECT COUNT(list_id) INTO :c1 FROM capacls WHERE list_id=:lid;
567 /* Check if the list is a member (direct or indirect) of a list that
569 EXEC SQL SELECT COUNT(l1.list_id) INTO :c2 FROM list l1, list l2,
570 imembers im, capacls c WHERE c.list_id = l2.list_id AND
571 im.list_id = l2.list_id AND im.member_type = 'LIST' AND
572 im.member_id = l1.list_id AND l1.list_id = :lid;
574 if (c1 == 0 && c2 == 0)
580 EXEC SQL SELECT acl_type, acl_id, memacl_type, memacl_id
581 INTO :acl_type, :acl_id, :memacl_type, :memacl_id
582 FROM list WHERE list_id=:lid;
584 if (!find_member(acl_type, acl_id, cl))
586 if (!find_member(memacl_type, memacl_id, cl))
594 /* get_ace_use - given a type and a name, return a type and a name.
595 * The ace_type is one of "LIST", "USER", "RLIST", or "RUSER" in argv[0],
596 * and argv[1] will contain the ID of the entity in question. The R*
597 * types mean to recursively look at every containing list, not just
598 * when the object in question is a direct member. On return, the
599 * usage type will be one of LIST, SERVICE, FILESYS, QUOTA, QUERY, or ZEPHYR.
602 int get_ace_use(struct query *q, char *argv[], client *cl,
603 int (*action)(int, char *[], void *), void *actarg)
606 EXEC SQL BEGIN DECLARE SECTION;
609 EXEC SQL END DECLARE SECTION;
610 struct save_queue *sq;
613 aid = *(int *)argv[1];
614 if (!strcmp(atype, "LIST") || !strcmp(atype, "USER") ||
615 !strcmp(atype, "KERBEROS"))
616 return get_ace_internal(atype, aid, action, actarg);
619 if (!strcmp(atype, "RLIST"))
621 sq_save_data(sq, (void *)aid);
622 /* get all the list_id's of containing lists */
623 EXEC SQL DECLARE csr107 CURSOR FOR
624 SELECT list_id FROM imembers
625 WHERE member_type = 'LIST' AND member_id = :aid;
628 EXEC SQL OPEN csr107;
633 EXEC SQL FETCH csr107 INTO :listid;
636 sq_save_unique_data(sq, (void *)listid);
638 EXEC SQL CLOSE csr107;
639 /* now process each one */
640 while (sq_get_data(sq, &id))
642 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
647 if (!strcmp(atype, "RUSER"))
649 EXEC SQL DECLARE csr108 CURSOR FOR
650 SELECT list_id FROM imembers
651 WHERE member_type = 'USER' AND member_id = :aid;
654 EXEC SQL OPEN csr108;
659 EXEC SQL FETCH csr108 INTO :listid;
662 sq_save_data(sq, (void *)listid);
664 EXEC SQL CLOSE csr108;
665 /* now process each one */
666 while (sq_get_data(sq, &id))
668 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
671 if (get_ace_internal("USER", aid, action, actarg) == MR_SUCCESS)
675 if (!strcmp(atype, "RKERBEROS"))
677 EXEC SQL DECLARE csr109 CURSOR FOR
678 SELECT list_id FROM imembers
679 WHERE member_type = 'KERBEROS' AND member_id = :aid;
682 EXEC SQL OPEN csr109;
687 EXEC SQL FETCH csr109 INTO :listid;
690 sq_save_data(sq, (void *)listid);
692 EXEC SQL CLOSE csr109;
693 /* now process each one */
694 while (sq_get_data(sq, &id))
696 if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
699 if (get_ace_internal("KERBEROS", aid, action, actarg) == MR_SUCCESS)
712 /* This looks up a single list or user for ace use. atype must be "USER"
713 * or "LIST", and aid is the ID of the corresponding object. This is used
714 * by get_ace_use above.
717 int get_ace_internal(char *atype, int aid,
718 int (*action)(int, char *[], void *), void *actarg)
722 EXEC SQL BEGIN DECLARE SECTION;
723 char name[MAX_FIELD_WIDTH], *type = atype;
725 EXEC SQL END DECLARE SECTION;
728 if (!strcmp(atype, "LIST"))
730 rargv[0] = "FILESYS";
731 EXEC SQL DECLARE csr110 CURSOR FOR
732 SELECT label FROM filesys
736 EXEC SQL OPEN csr110;
741 EXEC SQL FETCH csr110 INTO :name;
744 (*action)(2, rargv, actarg);
747 EXEC SQL CLOSE csr110;
750 EXEC SQL DECLARE csr111 CURSOR FOR
751 SELECT capability FROM capacls
755 EXEC SQL OPEN csr111;
760 EXEC SQL FETCH csr111 INTO :name;
763 (*action)(2, rargv, actarg);
766 EXEC SQL CLOSE csr111;
768 else if (!strcmp(atype, "USER"))
770 rargv[0] = "FILESYS";
771 EXEC SQL DECLARE csr112 CURSOR FOR
772 SELECT label FROM filesys
776 EXEC SQL OPEN csr112;
781 EXEC SQL FETCH csr112 INTO :name;
784 (*action)(2, rargv, actarg);
787 EXEC SQL CLOSE csr112;
791 EXEC SQL DECLARE csr113 CURSOR FOR
792 SELECT name FROM list
793 WHERE (acl_type = :type AND acl_id = :id)
794 OR (memacl_type = :type AND memacl_id = :id);
797 EXEC SQL OPEN csr113;
802 EXEC SQL FETCH csr113 INTO :name;
805 (*action)(2, rargv, actarg);
808 EXEC SQL CLOSE csr113;
810 rargv[0] = "SERVICE";
811 EXEC SQL DECLARE csr114 CURSOR FOR
812 SELECT name FROM servers
813 WHERE acl_type = :type AND acl_id = :id;
816 EXEC SQL OPEN csr114;
821 EXEC SQL FETCH csr114 INTO :name;
824 (*action)(2, rargv, actarg);
827 EXEC SQL CLOSE csr114;
829 rargv[0] = "HOSTACCESS";
830 EXEC SQL DECLARE csr115 CURSOR FOR
831 SELECT name FROM machine m, hostaccess ha
832 WHERE m.mach_id = ha.mach_id AND ha.acl_type = :type
836 EXEC SQL OPEN csr115;
841 EXEC SQL FETCH csr115 INTO :name;
844 (*action)(2, rargv, actarg);
847 EXEC SQL CLOSE csr115;
849 rargv[0] = "MACHINE";
850 EXEC SQL DECLARE csr115a CURSOR FOR
851 SELECT name FROM machine m
852 WHERE m.owner_type = :type
853 AND m.owner_id = :id;
856 EXEC SQL OPEN csr115a;
861 EXEC SQL FETCH csr115a INTO :name;
864 (*action)(2, rargv, actarg);
867 EXEC SQL CLOSE csr115a;
870 EXEC SQL DECLARE csr116 CURSOR FOR
871 SELECT class FROM zephyr z
872 WHERE z.xmt_type = :type AND z.xmt_id = :id
873 OR z.sub_type = :type AND z.sub_id = :id
874 OR z.iws_type = :type AND z.iws_id = :id
875 OR z.iui_type = :type AND z.iui_id = :id
876 OR z.owner_type = :type AND z.owner_id = :id;
879 EXEC SQL OPEN csr116;
884 EXEC SQL FETCH csr116 INTO :name;
887 (*action)(2, rargv, actarg);
890 EXEC SQL CLOSE csr116;
892 rargv[0] = "CONTAINER";
893 EXEC SQL DECLARE csr117c CURSOR FOR
894 SELECT name FROM containers c
895 WHERE c.acl_type = :type AND c.acl_id = :id;
898 EXEC SQL OPEN csr117c;
901 EXEC SQL FETCH csr117c INTO :name;
904 (*action)(2, rargv, actarg);
907 EXEC SQL CLOSE csr117c;
909 rargv[0] = "CONTAINER-MEMACL";
910 EXEC SQL DECLARE csr117d CURSOR FOR
911 SELECT name FROM containers c
912 WHERE c.memacl_type = :type AND c.memacl_id = :id;
915 EXEC SQL OPEN csr117d;
918 EXEC SQL FETCH csr117d INTO :name;
921 (*action)(2, rargv, actarg);
924 EXEC SQL CLOSE csr117d;
932 int ghbo_internal(char *atype, int aid,
933 int (*action)(int, char *[], void *), void *actarg)
937 EXEC SQL BEGIN DECLARE SECTION;
938 char name[MACHINE_NAME_SIZE], *type = atype;
940 EXEC SQL END DECLARE SECTION;
943 EXEC SQL DECLARE csr115b CURSOR FOR
944 SELECT name FROM machine m
945 WHERE m.owner_type = :type
946 AND m.owner_id = :id;
949 EXEC SQL OPEN csr115b;
954 EXEC SQL FETCH csr115b INTO :name;
957 (*action)(1, rargv, actarg);
960 EXEC SQL CLOSE csr115b;
967 /* get_host_by_owner - like gaus but limited to hosts */
968 int get_host_by_owner(struct query *q, char *argv[], client *cl,
969 int (*action)(int, char *[], void *), void *actarg)
972 EXEC SQL BEGIN DECLARE SECTION;
975 EXEC SQL END DECLARE SECTION;
976 struct save_queue *sq;
979 aid = *(int *)argv[1];
980 if (!strcmp(atype, "LIST") || !strcmp(atype, "USER") ||
981 !strcmp(atype, "KERBEROS"))
982 return ghbo_internal(atype, aid, action, actarg);
985 if (!strcmp(atype, "RLIST"))
987 sq_save_data(sq, (void *)aid);
988 /* get all the list_id's of containing lists */
989 EXEC SQL DECLARE csr107q CURSOR FOR
990 SELECT list_id FROM imembers
991 WHERE member_type = 'LIST' AND member_id = :aid;
994 EXEC SQL OPEN csr107q;
999 EXEC SQL FETCH csr107q INTO :listid;
1002 sq_save_unique_data(sq, (void *)listid);
1004 EXEC SQL CLOSE csr107q;
1005 /* now process each one */
1006 while (sq_get_data(sq, &id))
1008 if (ghbo_internal("LIST", id, action, actarg) == MR_SUCCESS)
1013 if (!strcmp(atype, "RUSER"))
1015 EXEC SQL DECLARE csr108q CURSOR FOR
1016 SELECT list_id FROM imembers
1017 WHERE member_type = 'USER' AND member_id = :aid;
1020 EXEC SQL OPEN csr108q;
1025 EXEC SQL FETCH csr108q INTO :listid;
1028 sq_save_data(sq, (void *)listid);
1030 EXEC SQL CLOSE csr108q;
1031 /* now process each one */
1032 while (sq_get_data(sq, &id))
1034 if (ghbo_internal("LIST", id, action, actarg) == MR_SUCCESS)
1037 if (ghbo_internal("USER", aid, action, actarg) == MR_SUCCESS)
1041 if (!strcmp(atype, "RKERBEROS"))
1043 EXEC SQL DECLARE csr109q CURSOR FOR
1044 SELECT list_id FROM imembers
1045 WHERE member_type = 'KERBEROS' AND member_id = :aid;
1048 EXEC SQL OPEN csr109q;
1053 EXEC SQL FETCH csr109q INTO :listid;
1056 sq_save_data(sq, (void *)listid);
1058 EXEC SQL CLOSE csr109q;
1059 /* now process each one */
1060 while (sq_get_data(sq, &id))
1062 if (ghbo_internal("LIST", id, action, actarg) == MR_SUCCESS)
1065 if (ghbo_internal("KERBEROS", aid, action, actarg) == MR_SUCCESS)
1077 int guas_internal(char *atype, int aid,
1078 int (*action)(int, char *[], void *), void *actarg)
1082 EXEC SQL BEGIN DECLARE SECTION;
1083 char login[USERS_LOGIN_SIZE], *type = atype;
1085 EXEC SQL END DECLARE SECTION;
1088 EXEC SQL DECLARE csr115sp CURSOR FOR
1089 SELECT login FROM users u
1090 WHERE u.sponsor_type = :type
1091 AND u.sponsor_id = :id;
1094 EXEC SQL OPEN csr115sp;
1099 EXEC SQL FETCH csr115sp INTO :login;
1102 (*action)(1, rargv, actarg);
1105 EXEC SQL CLOSE csr115sp;
1112 /* get_user_account_by_sponsor - like gaus but limited to user accounts */
1113 int get_user_account_by_sponsor(struct query *q, char *argv[], client *cl,
1114 int (*action)(int, char *[], void *),
1118 EXEC SQL BEGIN DECLARE SECTION;
1120 int aid, listid, id;
1121 EXEC SQL END DECLARE SECTION;
1122 struct save_queue *sq;
1125 aid = *(int *)argv[1];
1126 if (!strcmp(atype, "LIST") || !strcmp(atype, "USER") ||
1127 !strcmp(atype, "KERBEROS"))
1128 return guas_internal(atype, aid, action, actarg);
1131 if (!strcmp(atype, "RLIST"))
1133 sq_save_data(sq, (void *)aid);
1134 /* get all the list_id's of containing lists */
1135 EXEC SQL DECLARE csr107sp CURSOR FOR
1136 SELECT list_id FROM imembers
1137 WHERE member_type = 'LIST' AND member_id = :aid;
1140 EXEC SQL OPEN csr107sp;
1145 EXEC SQL FETCH csr107sp INTO :listid;
1148 sq_save_unique_data(sq, (void *)listid);
1150 EXEC SQL CLOSE csr107sp;
1151 /* now process each one */
1152 while (sq_get_data(sq, &id))
1154 if (guas_internal("LIST", id, action, actarg) == MR_SUCCESS)
1159 if (!strcmp(atype, "RUSER"))
1161 EXEC SQL DECLARE csr108sp CURSOR FOR
1162 SELECT list_id FROM imembers
1163 WHERE member_type = 'USER' AND member_id = :aid;
1166 EXEC SQL OPEN csr108sp;
1171 EXEC SQL FETCH csr108sp INTO :listid;
1174 sq_save_data(sq, (void *)listid);
1176 EXEC SQL CLOSE csr108sp;
1177 /* now process each one */
1178 while (sq_get_data(sq, &id))
1180 if (guas_internal("LIST", id, action, actarg) == MR_SUCCESS)
1183 if (guas_internal("USER", aid, action, actarg) == MR_SUCCESS)
1187 if (!strcmp(atype, "RKERBEROS"))
1189 EXEC SQL DECLARE csr109sp CURSOR FOR
1190 SELECT list_id FROM imembers
1191 WHERE member_type = 'KERBEROS' AND member_id = :aid;
1194 EXEC SQL OPEN csr109sp;
1199 EXEC SQL FETCH csr109sp INTO :listid;
1202 sq_save_data(sq, (void *)listid);
1204 EXEC SQL CLOSE csr109sp;
1205 /* now process each one */
1206 while (sq_get_data(sq, &id))
1208 if (guas_internal("LIST", id, action, actarg) == MR_SUCCESS)
1211 if (guas_internal("KERBEROS", aid, action, actarg) == MR_SUCCESS)
1223 /* get_lists_of_member - given a type and a name, return the name and flags
1224 * of all of the lists of the given member. The member_type is one of
1225 * "LIST", "USER", "STRING", "KERBEROS", "MACHINE", "RLIST", "RUSER",
1226 * "RSTRING", "RKERBEROS", or "RMACHINE" in argv[0], and argv[1] will contain
1227 * the ID of the entity in question. The R* types mean to recursively look
1228 * at every containing list, not just when the object in question is a direct
1232 int get_lists_of_member(struct query *q, char *argv[], client *cl,
1233 int (*action)(int, char *[], void *), void *actarg)
1235 int found = 0, direct = 1;
1237 EXEC SQL BEGIN DECLARE SECTION;
1240 char name[LIST_NAME_SIZE];
1241 char active[5], public[5], hidden[5], maillist[5], grouplist[5];
1242 EXEC SQL END DECLARE SECTION;
1245 aid = *(int *)argv[1];
1246 if (!strcmp(atype, "RLIST"))
1251 if (!strcmp(atype, "RUSER"))
1256 if (!strcmp(atype, "RSTRING"))
1261 if (!strcmp(atype, "RKERBEROS"))
1266 if (!strcmp(atype, "RMACHINE"))
1276 rargv[4] = maillist;
1277 rargv[5] = grouplist;
1280 EXEC SQL DECLARE csr117a CURSOR FOR
1281 SELECT l.name, l.active, l.publicflg, l.hidden, l.maillist, l.grouplist
1282 FROM list l, imembers im
1283 WHERE l.list_id = im.list_id AND im.direct = 1
1284 AND im.member_type = :atype AND im.member_id = :aid;
1287 EXEC SQL OPEN csr117a;
1292 EXEC SQL FETCH csr117a
1293 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
1296 (*action)(6, rargv, actarg);
1299 EXEC SQL CLOSE csr117a;
1303 EXEC SQL DECLARE csr117b CURSOR FOR
1304 SELECT l.name, l.active, l.publicflg, l.hidden, l.maillist, l.grouplist
1305 FROM list l, imembers im
1306 WHERE l.list_id = im.list_id
1307 AND im.member_type = :atype AND im.member_id = :aid;
1310 EXEC SQL OPEN csr117b;
1315 EXEC SQL FETCH csr117b
1316 INTO :name, :active, :public, :hidden, :maillist, :grouplist;
1319 (*action)(6, rargv, actarg);
1322 EXEC SQL CLOSE csr117b;
1333 /* qualified_get_lists: passed "TRUE", "FALSE", or "DONTCARE" for each of
1334 * the five flags associated with each list. It will return the name of
1335 * each list that meets the quailifications. It does this by building a
1336 * where clause based on the arguments, then doing a retrieve.
1339 static char *lflags[5] = { "active", "publicflg", "hidden", "maillist", "grouplist" };
1341 int qualified_get_lists(struct query *q, char *argv[], client *cl,
1342 int (*action)(int, char *[], void *), void *actarg)
1344 return qualified_get(q, argv, action, actarg, "l.list_id != 0",
1345 "l", "name", lflags);
1349 int get_members_of_list(struct query *q, char *argv[], client *cl,
1350 int (*action)(int, char *[], void *), void *actarg)
1352 EXEC SQL BEGIN DECLARE SECTION;
1353 int list_id, direct;
1354 char member_name[MAX_FIELD_WIDTH], tag[STRINGS_STRING_SIZE];
1355 EXEC SQL END DECLARE SECTION;
1359 /* For gmol or gtml, only get direct members. For geml, get all. */
1360 if (!strcmp(q->shortname, "geml"))
1365 /* For gmol or geml, only return type and name. For gtml, return tag too. */
1366 if (!strcmp(q->shortname, "gtml"))
1371 list_id = *(int *)argv[0];
1373 targv[1] = member_name;
1377 EXEC SQL DECLARE csr119 CURSOR FOR
1378 SELECT u.login, s.string FROM users u, imembers im, strings s
1379 WHERE im.list_id = :list_id AND im.member_type = 'USER'
1380 AND im.member_id = u.users_id AND im.direct > :direct
1381 AND s.string_id = im.tag ORDER BY 1;
1384 EXEC SQL OPEN csr119;
1389 EXEC SQL FETCH csr119 INTO :member_name, :tag;
1392 (*action)(targc, targv, actarg);
1394 EXEC SQL CLOSE csr119;
1399 EXEC SQL DECLARE csr120 CURSOR FOR
1400 SELECT l.name, s.string FROM list l, imembers im, strings s
1401 WHERE im.list_id = :list_id AND im.member_type = 'LIST'
1402 AND im.member_id = l.list_id AND im.direct > :direct
1403 AND s.string_id = im.tag ORDER BY 1;
1406 EXEC SQL OPEN csr120;
1411 EXEC SQL FETCH csr120 INTO :member_name, :tag;
1414 (*action)(targc, targv, actarg);
1416 EXEC SQL CLOSE csr120;
1420 targv[0] = "STRING";
1421 EXEC SQL DECLARE csr121 CURSOR FOR
1422 SELECT str.string, s.string FROM strings str, imembers im, strings s
1423 WHERE im.list_id = :list_id AND im.member_type = 'STRING'
1424 AND im.member_id = str.string_id AND im.direct > :direct
1425 AND s.string_id = im.tag ORDER BY 1;
1428 EXEC SQL OPEN csr121;
1433 EXEC SQL FETCH csr121 INTO :member_name, :tag;
1436 (*action)(targc, targv, actarg);
1438 EXEC SQL CLOSE csr121;
1442 targv[0] = "KERBEROS";
1443 EXEC SQL DECLARE csr122 CURSOR FOR
1444 SELECT str.string, s.string FROM strings str, imembers im, strings s
1445 WHERE im.list_id = :list_id AND im.member_type = 'KERBEROS'
1446 AND im.member_id = str.string_id AND im.direct > :direct
1447 AND s.string_id = im.tag ORDER BY 1;
1450 EXEC SQL OPEN csr122;
1455 EXEC SQL FETCH csr122 INTO :member_name, :tag;
1458 (*action)(targc, targv, actarg);
1460 EXEC SQL CLOSE csr122;
1464 targv[0] = "MACHINE";
1465 EXEC SQL DECLARE csr123 CURSOR FOR
1466 SELECT m.name, s.string FROM machine m, imembers im, strings s
1467 WHERE im.list_id = :list_id AND im.member_type = 'MACHINE'
1468 AND im.member_id = m.mach_id AND im.direct > :direct
1469 AND s.string_id = im.tag ORDER BY 1;
1472 EXEC SQL OPEN csr123;
1477 EXEC SQL FETCH csr123 INTO :member_name, :tag;
1480 (*action)(targc, targv, actarg);
1482 EXEC SQL CLOSE csr123;
1490 /* count_members_of_list: this is a simple query, but it cannot be done
1491 * through the dispatch table.
1494 int count_members_of_list(struct query *q, char *argv[], client *cl,
1495 int (*action)(int, char *[], void *), void *actarg)
1497 EXEC SQL BEGIN DECLARE SECTION;
1499 EXEC SQL END DECLARE SECTION;
1500 char *rargv[1], countbuf[5];
1502 list = *(int *)argv[0];
1503 rargv[0] = countbuf;
1504 EXEC SQL SELECT count (*) INTO :ct FROM imembers
1505 WHERE list_id = :list AND direct = 1;
1508 sprintf(countbuf, "%d", ct);
1509 (*action)(1, rargv, actarg);
1514 /* qualified_get_server: passed "TRUE", "FALSE", or "DONTCARE" for each of
1515 * the three flags associated with each service. It will return the name of
1516 * each service that meets the quailifications. It does this by building a
1517 * where clause based on the arguments, then doing a retrieve.
1520 static char *sflags[3] = { "enable", "inprogress", "harderror" };
1522 int qualified_get_server(struct query *q, char *argv[], client *cl,
1523 int (*action)(int, char *[], void *), void *actarg)
1525 return qualified_get(q, argv, action, actarg, "s.name is not null",
1526 "s", "name", sflags);
1527 /* of course, name will never be null, but we need something there
1528 to make qualified_get happy */
1532 /* generic qualified get routine, used by qualified_get_lists,
1533 * qualified_get_server, and qualified_get_serverhost.
1535 * start - a simple where clause, must not be empty
1536 * range - the name of the range variable
1537 * field - the field to return
1538 * flags - an array of strings, names of the flag variables
1541 int qualified_get(struct query *q, char *argv[],
1542 int (*action)(int, char *[], void *), void *actarg,
1543 char *start, char *range, char *field, char *flags[])
1549 strcpy(qual, start);
1550 for (i = 0; i < q->argc; i++)
1552 if (!strcmp(argv[i], "TRUE"))
1554 sprintf(buf, " AND %s.%s != 0", range, flags[i]);
1557 else if (!strcmp(argv[i], "FALSE"))
1559 sprintf(buf, " AND %s.%s = 0", range, flags[i]);
1564 sprintf(stmt_buf, "SELECT %s.%s FROM %s %s WHERE %s", range, field,
1565 table_name[q->rtable], range, qual);
1566 return do_for_all_rows(stmt_buf, 1, action, actarg);
1570 /* qualified_get_serverhost: passed "TRUE", "FALSE", or "DONTCARE" for each of
1571 * the five flags associated with each serverhost. It will return the name of
1572 * each service and host that meets the quailifications. It does this by
1573 * building a where clause based on the arguments, then doing a retrieve.
1576 static char *shflags[6] = { "service", "enable", "override", "success",
1577 "inprogress", "hosterror" };
1579 int qualified_get_serverhost(struct query *q, char *argv[], client *cl,
1580 int (*action)(int, char *[], void *),
1583 char qual[256], buf[32];
1586 sprintf(qual, "m.mach_id = sh.mach_id AND sh.service = UPPER('%s')",
1588 for (i = 1; i < q->argc; i++)
1590 if (!strcmp(argv[i], "TRUE"))
1592 sprintf(buf, " AND sh.%s != 0", shflags[i]);
1595 else if (!strcmp(argv[i], "FALSE"))
1597 sprintf(buf, " AND sh.%s = 0", shflags[i]);
1602 sprintf(stmt_buf, "SELECT sh.service, m.name FROM serverhosts sh, "
1603 "machine m WHERE %s", qual);
1604 return do_for_all_rows(stmt_buf, 2, action, actarg);
1608 /* register_user - change user's login name and allocate a pobox, group,
1609 * filesystem, and quota for them. The user's status must start out as 0,
1610 * and is left as 2. Arguments are: user's UID, new login name, and
1611 * pobox type ("POP" = POP, "IMAP" or numeric = IMAP)
1614 int register_user(struct query *q, char **argv, client *cl)
1616 EXEC SQL BEGIN DECLARE SECTION;
1617 char *login, *entity;
1618 char directory[FILESYS_NAME_SIZE], machname[MACHINE_NAME_SIZE];
1619 char dir[NFSPHYS_DIR_SIZE], *potype;
1620 int who, rowcount, mid, uid, users_id;
1621 int ostatus, nstatus, fsidval, popid;
1624 static int m_id, def_quota, def_imap_quota, list_id;
1625 EXEC SQL END DECLARE SECTION;
1626 char buffer[256], *aargv[3];
1630 EXEC SQL SELECT list_id INTO :list_id FROM list
1631 WHERE name = 'wheel';
1633 EXEC SQL SELECT mach_id INTO :m_id FROM machine
1634 WHERE name = 'ATHENA.MIT.EDU';
1637 EXEC SQL SELECT value INTO :def_quota FROM numvalues
1638 WHERE name = 'def_quota';
1639 if (sqlca.sqlerrd[2] != 1)
1642 EXEC SQL SELECT value INTO :def_imap_quota FROM numvalues
1643 WHERE name = 'def_imap_quota';
1644 if (sqlca.sqlerrd[2] != 1)
1647 entity = cl->entity;
1648 who = cl->client_id;
1650 uid = atoi(argv[0]);
1655 EXEC SQL SELECT users_id, status INTO :users_id, :ostatus
1657 WHERE unix_uid = :uid AND
1658 (status = 0 OR status = 5 OR status = 6 OR status = 9);
1660 if (sqlca.sqlerrd[2] == 0)
1662 if (sqlca.sqlerrd[2] > 1)
1663 return MR_NOT_UNIQUE;
1665 /* check new login name */
1666 EXEC SQL SELECT COUNT(login) INTO :rowcount FROM users
1667 WHERE login = :login AND users_id != :users_id;
1673 /* Remove this check when we're allowing username reuse again. */
1674 EXEC SQL SELECT COUNT(login) INTO :rowcount FROM userhistory
1675 WHERE login = :login;
1681 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM list
1682 WHERE LOWER(name) = :login;
1687 EXEC SQL SELECT COUNT(label) INTO :rowcount FROM filesys
1688 WHERE label = :login;
1693 EXEC SQL SELECT COUNT(label) INTO :rowcount
1694 FROM filesys WHERE label = :login || '.po';
1699 EXEC SQL SELECT owner INTO :tmp FROM filesys
1700 WHERE label = :login || '.po';
1703 if ((ostatus == 0 || ostatus == 9) || (tmp != users_id))
1708 EXEC SQL SELECT COUNT(name) INTO :rowcount FROM alias
1709 WHERE ( name = :login OR name = :login || '.po' )
1710 AND type = 'FILESYS';
1715 com_err(whoami, 0, "login name OK");
1717 EXEC SQL SELECT COUNT(potype) INTO :rowcount FROM users WHERE
1718 login = :login AND potype = 'POP';
1724 /* choose type and location for pobox */
1727 if (!strcmp(potype, "POP"))
1730 EXEC SQL DECLARE csr130 CURSOR FOR
1731 SELECT sh.mach_id, m.name FROM serverhosts sh, machine m
1732 WHERE sh.service = 'POP' AND sh.mach_id = m.mach_id
1733 AND sh.value2 - sh.value1 =
1734 (SELECT MAX(value2 - value1) FROM serverhosts WHERE service = 'POP');
1737 EXEC SQL OPEN csr130;
1740 EXEC SQL FETCH csr130 INTO :popid, :machname;
1741 if (sqlca.sqlerrd[2] == 0)
1743 EXEC SQL CLOSE csr130;
1750 EXEC SQL CLOSE csr130;
1755 EXEC SQL UPDATE users SET potype = 'POP', pop_id = :popid
1756 WHERE users_id = :users_id;
1757 com_err(whoami, 0, "pobox set to POP:%s", strtrim(machname));
1761 /* Select all IMAP nfsphys entries in order of increasing
1762 * (allocated - partsize). The partitions will almost always be
1763 * overallocated, but we choose the one that is the least
1768 EXEC SQL DECLARE csr_rusr_imap CURSOR FOR
1769 SELECT np.allocated - np.partsize, np.nfsphys_id, np.mach_id,
1770 np.dir, m.name FROM serverhosts sh, nfsphys np, machine m
1771 WHERE sh.service = 'POSTOFFICE' AND sh.mach_id = np.mach_id
1772 AND m.mach_id = np.mach_id
1776 EXEC SQL OPEN csr_rusr_imap;
1779 EXEC SQL FETCH csr_rusr_imap INTO :tmp, :npid, :mid, :dir, :machname;
1780 if (sqlca.sqlerrd[2] == 0)
1782 EXEC SQL CLOSE csr_rusr_imap;
1789 EXEC SQL CLOSE csr_rusr_imap;
1794 /* create filesystem */
1795 if (set_next_object_id("filsys_id", FILESYS_TABLE, 0))
1797 incremental_clear_before();
1799 EXEC SQL SELECT value INTO :popid FROM numvalues
1800 WHERE numvalues.name = 'filsys_id';
1801 EXEC SQL INSERT INTO filesys
1802 (filsys_id, phys_id, label, type, mach_id, name,
1803 mount, rwaccess, comments, owner, owners, createflg,
1804 lockertype, modtime, modby, modwith)
1806 (:popid, :npid, :login || '.po', 'IMAP', :mid, :dir,
1807 CHR(0), 'w', 'IMAP box', :users_id, :list_id, 1,
1808 'USER', SYSDATE, :who, :entity);
1812 if (sqlca.sqlerrd[2] != 1)
1814 sprintf(buffer, "fs.filsys_id = %d", popid);
1815 incremental_after(FILESYS_TABLE, buffer, 0);
1818 incremental_clear_before();
1819 EXEC SQL INSERT INTO quota
1820 (entity_id, filsys_id, type, quota, phys_id, modtime, modby, modwith)
1821 VALUES (:users_id, :popid, 'USER', :def_imap_quota, :npid,
1822 SYSDATE, :who, :entity);
1825 if (sqlca.sqlerrd[2] != 1)
1830 sprintf(buffer, "q.entity_id = %d and q.filsys_id = %d and "
1831 "q.type = 'USER'", users_id, popid);
1832 incremental_after(QUOTA_TABLE, buffer, aargv);
1836 EXEC SQL UPDATE nfsphys SET allocated = allocated + :def_imap_quota
1837 WHERE nfsphys_id = :npid;
1839 EXEC SQL UPDATE users SET potype = 'IMAP', imap_id = :popid
1840 WHERE users_id = :users_id;
1841 com_err(whoami, 0, "pobox set to IMAP:%s:%s", strtrim(machname),
1846 /* change login name, set pobox */
1847 sprintf(buffer, "u.users_id = %d", users_id);
1848 incremental_before(USERS_TABLE, buffer, 0);
1850 if (ostatus == 5 || ostatus == 6 || ostatus == 9)
1852 EXEC SQL UPDATE users SET login = :login, status = :nstatus,
1853 modtime = SYSDATE, modby = :who, modwith = :entity,
1854 pmodtime = SYSDATE, pmodby = :who, pmodwith = :entity,
1855 created = SYSDATE, creator = :who
1856 WHERE users_id = :users_id;
1860 if (sqlca.sqlerrd[2] != 1)
1863 /* Only update usage count if we created a POP pobox. */
1864 if (!strcmp(potype, "POP") && !po_exists)
1865 set_pop_usage(mid, 1);
1867 com_err(whoami, 0, "set login name to %s", login);
1868 incremental_after(USERS_TABLE, buffer, 0);
1870 /* create filesystem */
1871 if (set_next_object_id("filsys_id", FILESYS_TABLE, 0))
1873 incremental_clear_before();
1874 if (islower(login[0]) && islower(login[1]))
1876 sprintf(directory, "/afs/athena.mit.edu/user/%c/%c/%s",
1877 login[0], login[1], login);
1880 sprintf(directory, "/afs/athena.mit.edu/user/other/%s", login);
1882 EXEC SQL SELECT value INTO :fsidval FROM numvalues
1883 WHERE numvalues.name = 'filsys_id';
1884 EXEC SQL INSERT INTO filesys
1885 (filsys_id, phys_id, label, type, mach_id, name,
1886 mount, rwaccess, comments, owner, owners, createflg,
1887 lockertype, modtime, modby, modwith)
1889 (:fsidval, 0, :login, 'AFS', :m_id, :directory,
1890 '/mit/' || :login, 'w', 'User Locker', :users_id, :list_id, 1,
1891 'HOMEDIR', SYSDATE, :who, :entity);
1895 if (sqlca.sqlerrd[2] != 1)
1897 sprintf(buffer, "fs.filsys_id = %d", fsidval);
1898 incremental_after(FILESYS_TABLE, buffer, 0);
1901 incremental_clear_before();
1902 EXEC SQL INSERT INTO quota
1903 (entity_id, filsys_id, type, quota, phys_id, modtime, modby, modwith)
1904 VALUES (0, :fsidval, 'ANY', :def_quota, 0, SYSDATE, :who, :entity);
1907 if (sqlca.sqlerrd[2] != 1)
1912 sprintf(buffer, "q.entity_id = 0 and q.filsys_id = %d and q.type = 'ANY'",
1914 incremental_after(QUOTA_TABLE, buffer, aargv);
1915 com_err(whoami, 0, "quota of %d assigned", def_quota);
1919 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
1920 WHERE table_name = 'users';
1921 EXEC SQL UPDATE tblstats SET appends = appends + 1, modtime = SYSDATE
1922 WHERE table_name = 'filesys' OR table_name = 'quota';
1928 /** set_pop_usage - incr/decr usage count for pop server in serverhosts talbe
1932 ** delta (will be +/- 1)
1935 ** - incr/decr value field in serverhosts table for pop/mach_id
1939 int set_pop_usage(id, cnt)
1942 EXEC SQL BEGIN DECLARE SECTION;
1943 int iid = id, icnt = cnt;
1944 EXEC SQL END DECLARE SECTION;
1946 EXEC SQL UPDATE serverhosts SET value1 = value1 + :icnt
1947 WHERE serverhosts.service = 'POP' AND serverhosts.mach_id = :iid;
1955 int do_user_reservation(struct query *q, char *argv[], client *cl)
1957 EXEC SQL BEGIN DECLARE SECTION;
1958 char resv[USERS_RESERVATIONS_SIZE];
1959 char *trans, name[ALIAS_NAME_SIZE];
1961 EXEC SQL END DECLARE SECTION;
1963 uid = *(int *)argv[0];
1966 EXEC SQL SELECT UPPER(name) INTO :name FROM alias
1967 WHERE type = 'RESERVE' AND LOWER(trans) = LOWER(:trans);
1970 if (sqlca.sqlerrd[2] != 1)
1974 EXEC SQL SELECT reservations INTO :resv FROM users
1975 WHERE users_id = :uid;
1980 if (!strcmp(q->shortname, "aurv"))
1982 if (strchr(resv, *name))
1984 if (strlen(resv) == USERS_RESERVATIONS_SIZE - 1)
1985 return MR_ARG_TOO_LONG;
1991 char *p = strchr(resv, *name);
1994 memmove(p, p + 1, strlen(p));
1997 EXEC SQL UPDATE users SET reservations = NVL(:resv, CHR(0))
1998 WHERE users_id = :uid;
2002 EXEC SQL UPDATE tblstats SET updates = updates + 1, modtime = SYSDATE
2003 WHERE table_name = 'users';
2004 return set_modtime_by_id(q, argv, cl);
2007 int get_user_reservations(struct query *q, char **argv, client *cl,
2008 int (*action)(int, char *[], void *), void *actarg)
2010 EXEC SQL BEGIN DECLARE SECTION;
2011 char resv[USERS_RESERVATIONS_SIZE], *p;
2012 char trans[ALIAS_TRANS_SIZE], name[2] = { 0, 0 };
2014 EXEC SQL END DECLARE SECTION;
2017 uid = *(int *)argv[0];
2019 EXEC SQL SELECT reservations INTO :resv FROM users
2020 WHERE users_id = :uid;
2026 while (*p && !isspace(*p))
2028 name[0] = toupper(*p);
2029 EXEC SQL SELECT trans INTO :trans FROM alias
2030 WHERE type = 'RESERVE' AND UPPER(name) = :name;
2033 if (sqlca.sqlerrd[2] != 1)
2034 sprintf(trans, "Unknown (%s)", name);
2035 (*action)(1, targv, actarg);
2041 int get_user_by_reservation(struct query *q, char **argv, client *cl,
2042 int (*action)(int, char *[], void *), void *actarg)
2044 EXEC SQL BEGIN DECLARE SECTION;
2045 char resv[USERS_RESERVATIONS_SIZE], login[USERS_LOGIN_SIZE];
2046 char *trans, name[ALIAS_NAME_SIZE];
2048 EXEC SQL END DECLARE SECTION;
2053 EXEC SQL SELECT UPPER(name) INTO :name FROM alias
2054 WHERE type = 'RESERVE' AND LOWER(trans) = LOWER(:trans);
2057 if (sqlca.sqlerrd[2] != 1)
2061 EXEC SQL DECLARE csr_gubr CURSOR FOR
2062 SELECT login FROM users WHERE reservations LIKE '%' || :name || '%';
2063 EXEC SQL OPEN csr_gubr;
2070 EXEC SQL FETCH csr_gubr INTO :login;
2073 (*action)(1, targv, actarg);
2075 EXEC SQL CLOSE csr_gubr;
2080 int update_container(struct query *q, char *argv[], client *cl)
2082 EXEC SQL BEGIN DECLARE SECTION;
2083 int cnt_id, acl_id, memacl_id, who, flag;
2084 char name[CONTAINERS_NAME_SIZE], newchildname[CONTAINERS_NAME_SIZE];
2085 char* newname, *entity, *description, *location, *contact, *acl_type, *memacl_type;
2086 EXEC SQL END DECLARE SECTION;
2089 char childname[CONTAINERS_NAME_SIZE];
2093 cnt_id = *(int *)argv[index++];
2094 newname = argv[index++];
2096 if (q->version >= 9)
2097 flag = atoi(argv[index++]);
2099 description = argv[index++];
2100 location = argv[index++];
2101 contact = argv[index++];
2102 acl_type = argv[index++];
2103 acl_id = *(int *)argv[index++];
2104 memacl_type = argv[index++];
2105 memacl_id = *(int *)argv[index++];
2106 entity = cl->entity;
2107 who = cl->client_id;
2109 EXEC SQL SELECT name INTO :name
2111 WHERE cnt_id = :cnt_id;
2113 /* trim off the trailing spaces */
2114 strcpy(name, strtrim(name));
2116 qual = xmalloc(MAX_FIELD_WIDTH);
2117 sprintf(qual, "name = '%s'", name);
2118 incremental_before(CONTAINERS_TABLE, qual, argv);
2120 /* if we are renaming the container */
2121 if (strcmp(name, newname))
2123 /* make sure that only the name part of the name has been changed */
2124 tmpchar = strrchr(name, '/');
2125 /* not a top-level name */
2128 cnt = tmpchar - name + 1;
2129 /* the parent part of the old and new name should be identical */
2130 if (strncmp(name, newname, cnt))
2131 return MR_NEW_CONTAINER_NAME;
2133 /* top level name, new name should be a top level name too */
2136 if (strrchr(newname, '/'))
2137 return MR_NEW_CONTAINER_NAME;
2140 /* update the name for this container */
2141 EXEC SQL UPDATE containers
2143 WHERE cnt_id = :cnt_id;
2148 /* get names for its child containers */
2149 EXEC SQL DECLARE csr_ucon CURSOR FOR
2150 SELECT name, cnt_id FROM containers WHERE name LIKE :name || '/' || '%';
2152 EXEC SQL OPEN csr_ucon;
2158 EXEC SQL FETCH csr_ucon INTO :childname, :childid;
2162 strcpy(childname, strtrim(childname));
2163 /* concatenate the new parent name with the existing sub-container name
2164 * we get the sub-containers new name */
2165 tmpchar = childname + strlen(name);
2166 strcpy(newchildname, newname);
2167 strcat(newchildname, tmpchar);
2169 /* update the name */
2170 EXEC SQL UPDATE containers
2171 SET name = :newchildname, modtime = SYSDATE, modby = :who, modwith = :entity
2172 WHERE cnt_id = :childid;
2178 EXEC SQL CLOSE csr_ucon;
2183 /* update the remaining fields */
2184 if (q->version >= 9)
2186 EXEC SQL UPDATE containers
2187 SET publicflg= :flag, description = NVL(:description, CHR(0)), location = NVL(:location, CHR(0)),
2188 contact = NVL(:contact, CHR(0)), acl_type = :acl_type, acl_id = :acl_id,
2189 memacl_type = :memacl_type, memacl_id = :memacl_id,
2190 modtime = SYSDATE, modby = :who, modwith = :entity
2191 WHERE cnt_id = :cnt_id;
2195 EXEC SQL UPDATE containers
2196 SET publicflg= :flag, description = NVL(:description, CHR(0)), location = NVL(:location, CHR(0)),
2197 contact = NVL(:contact, CHR(0)), acl_type = :acl_type, acl_id = :acl_id,
2198 memacl_type = :memacl_type, memacl_id = :memacl_id,
2199 modtime = SYSDATE, modby = :who, modwith = :entity
2200 WHERE cnt_id = :cnt_id;
2206 sprintf(qual, "name = '%s'", newname);
2207 incremental_after(CONTAINERS_TABLE, qual, argv);
2212 int get_machines_of_container(struct query *q, char *argv[], client *cl,
2213 int (*action)(int, char *[], void *), void *actarg)
2215 EXEC SQL BEGIN DECLARE SECTION;
2216 int cnt_id, isrecursive;
2217 char machinename[MACHINE_NAME_SIZE], containername[CONTAINERS_NAME_SIZE];
2219 EXEC SQL END DECLARE SECTION;
2221 char querystring[512], tmp [256];
2225 rargv[0] = machinename;
2226 rargv[1] = containername;
2228 cnt_id = *(int *)argv[0];
2229 isrecursive = atoi(argv[1]);
2231 /* get the container name */
2233 EXEC SQL SELECT name INTO :containername
2235 WHERE cnt_id = :cnt_id;
2237 /* trim off the trailing spaces */
2238 strcpy(containername, strtrim(containername));
2240 strcpy(querystring, "SELECT a.name, b.name FROM machine a, containers b, mcntmap c ");
2241 strcat(querystring, "WHERE a.mach_id = c.mach_id AND b.cnt_id = c.cnt_id ");
2244 sprintf(tmp, "AND b.cnt_id = %d ", cnt_id);
2246 sprintf(tmp, "AND (b.cnt_id = %d OR LOWER(b.name) LIKE LOWER('%s/%%')) ",
2247 cnt_id, containername);
2249 strcat(querystring, tmp);
2250 strcat(querystring, "ORDER BY b.name, a.name");
2254 EXEC SQL PREPARE stmt FROM :qs;
2257 EXEC SQL DECLARE curs_gmnm CURSOR FOR stmt;
2258 EXEC SQL OPEN curs_gmnm;
2262 EXEC SQL FETCH curs_gmnm INTO :machinename, :containername;
2265 (*action)(2, rargv, actarg);
2269 EXEC SQL CLOSE curs_gmnm;
2275 int get_subcontainers_of_container(struct query *q, char *argv[], client *cl,
2276 int (*action)(int, char *[], void *), void *actarg)
2278 EXEC SQL BEGIN DECLARE SECTION;
2279 int cnt_id, isrecursive;
2280 char containername[CONTAINERS_NAME_SIZE], subcontainername[CONTAINERS_NAME_SIZE];
2282 EXEC SQL END DECLARE SECTION;
2284 char querystring[2048], tmp [1024];
2288 rargv[0] = subcontainername;
2290 cnt_id = *(int *)argv[0];
2291 isrecursive = atoi(argv[1]);
2293 /* get the container name */
2295 EXEC SQL SELECT name INTO :containername
2297 WHERE cnt_id = :cnt_id;
2299 /* trim off the trailing spaces */
2300 strcpy(containername, strtrim(containername));
2302 strcpy(querystring, "SELECT name FROM containers ");
2305 sprintf(tmp, "WHERE LOWER(name) LIKE LOWER('%s/%%') and LOWER(name) NOT LIKE LOWER('%s/%%/%%') ",
2306 containername, containername);
2308 sprintf(tmp, "WHERE LOWER(name) LIKE LOWER('%s/%%') ", containername);
2310 strcat(querystring, tmp);
2311 strcat(querystring, "ORDER BY name");
2315 EXEC SQL PREPARE stmt FROM :qs;
2318 EXEC SQL DECLARE curs_gsoc CURSOR FOR stmt;
2319 EXEC SQL OPEN curs_gsoc;
2323 EXEC SQL FETCH curs_gsoc INTO :subcontainername;
2326 (*action)(1, rargv, actarg);
2330 EXEC SQL CLOSE curs_gsoc;
2336 int set_container_list(struct query *q, char *argv[], client *cl)
2338 EXEC SQL BEGIN DECLARE SECTION;
2339 int cnt_id, list_id;
2340 EXEC SQL END DECLARE SECTION;
2342 cnt_id = *(int *)argv[0];
2343 list_id = *(int *)argv[1];
2345 EXEC SQL UPDATE containers SET list_id = :list_id WHERE cnt_id = :cnt_id;