Rewrite functions to avoid having SQL DECLARE SECTIONs around function

[moira.git] / server / qsupport.pc

/*
 *	$Source$
 *	$Author$
 *	$Header$
 *
 *	Copyright (C) 1987 by the Massachusetts Institute of Technology
 *	For copying and distribution information, please see the file
 *	<mit-copyright.h>.
 *
 */

#ifndef lint
static char *rcsid_qsupport_dc = "$Header$";
#endif lint

#include <mit-copyright.h>
#include "mr_server.h"
#include "query.h"
#include <ctype.h>
#include <string.h>
EXEC SQL INCLUDE sqlca;
#include "qrtn.h"

extern char *whoami, *table_name[];
extern int dbms_errno, mr_errcode;

EXEC SQL BEGIN DECLARE SECTION;
extern char stmt_buf[];
EXEC SQL END DECLARE SECTION;

EXEC SQL WHENEVER SQLERROR DO dbmserr();

int get_ace_internal(char *atypex, int aid, int (*action)(), int actarg);
int gmol_internal(struct query *q, char *argv[], client *cl,
		  int (*action)(), int actarg, int flag);
int qualified_get(struct query *q, char *argv[], int (*action)(), int actarg,
		  char *start, char *range, char *field, char *flags[]);


/* Special query routines */

/* set_pobox - this does all of the real work.
 *	 argv = user_id, type, box
 * if type is POP, then box should be a machine, and its ID should be put in
 * pop_id.  If type is SMTP, then box should be a string and its ID should
 * be put in box_id.  If type is NONE, then box doesn't matter.
 */


int set_pobox(q, argv, cl)
     struct query *q;
     char **argv;
     client *cl;
{
    EXEC SQL BEGIN DECLARE SECTION;
    int user, id;
    char *box, potype[9];
    EXEC SQL END DECLARE SECTION;
    int status;

    box = argv[2];
    user = *(int *)argv[0];

    EXEC SQL SELECT pop_id, potype INTO :id, :potype FROM users
      WHERE users_id = :user;
    if (dbms_errno) return(mr_errcode);
    if (!strcmp(strtrim(potype), "POP"))
      set_pop_usage(id, -1);

    if (!strcmp(argv[1], "POP")) {
	status = name_to_id(box, MACHINE_TABLE, &id);
	if (status == MR_NO_MATCH)
	  return(MR_MACHINE);
	else if (status)
	  return(status);
	EXEC SQL UPDATE users SET potype = 'POP', pop_id = :id
	  WHERE users_id = :user;
	set_pop_usage(id, 1);
    } else if (!strcmp(argv[1], "SMTP")) {
	if (strchr(box, '/') || strchr(box, '|'))
	  return(MR_BAD_CHAR);
	status = name_to_id(box, STRINGS_TABLE, &id);
	if (status == MR_NO_MATCH) {
	    id=add_string(box);
	} else if (status)
	  return(status);
	EXEC SQL UPDATE users SET potype='SMTP', box_id = :id
	  WHERE users_id = :user;
    } else /* argv[1] == "NONE" */ {
	EXEC SQL UPDATE users SET potype='NONE'
	  WHERE users_id = :user;
    }

    set_pobox_modtime(q, argv, cl);
    EXEC SQL UPDATE tblstats SET updates = updates+1, modtime=SYSDATE
      WHERE table_name='users';
    if (dbms_errno) return(mr_errcode);
    return(MR_SUCCESS);
}


/* get_list_info:  passed a wildcard list name, returns lots of stuff about
 * each list.  This is tricky:  first build a queue of all requested
 * data.  Rest of processing consists of fixing gid, ace_name, and modby.
 */

int get_list_info(q, aargv, cl, action, actarg)
     struct query *q;
     char **aargv;
     client *cl;
     int (*action)(), actarg;
{
    char *argv[13];
    EXEC SQL BEGIN DECLARE SECTION;
    char *name, acl_type[9], listname[33], active[5], public[5], hidden[5];
    char maillist[5], grouplist[5], gid_str[6], desc[256];
    char modtime[27], modwith[9];
    int id, rowcount, acl_id, hid, modby_id;
    EXEC SQL END DECLARE SECTION;
    int returned, status;
    struct save_queue *sq, *sq_create();

    returned = rowcount = 0;
    name = aargv[0];
    convert_wildcards(name);

    sq = sq_create();
    EXEC SQL DECLARE csr102 CURSOR FOR SELECT list_id FROM list
      WHERE name LIKE :name ESCAPE '*';
    if (dbms_errno)
	return(mr_errcode);
    EXEC SQL OPEN csr102;
    if (dbms_errno)
	return(mr_errcode);
    while(1)
    {
        EXEC SQL FETCH csr102 INTO :id;
	if(sqlca.sqlcode!=0) break;
	sq_save_data(sq, (char *)id);
        rowcount++;
    }
    EXEC SQL CLOSE csr102;

    if (dbms_errno) return(mr_errcode);
    if (rowcount == 0)
      return(MR_NO_MATCH);

    argv[0] = listname; argv[1] = active; argv[2] = public; argv[3] = hidden;
    argv[4] = maillist; argv[5] = grouplist; argv[6] = gid_str;
    argv[7] = acl_type; argv[9] = desc; argv[10] = modtime; argv[12] = modwith;

    while (sq_get_data(sq, &id)) {
	if (id == 0)
	  continue;
	argv[6] = gid_str;
	EXEC SQL SELECT name, active, publicflg,
	    hidden, hidden, maillist, grouplist, gid,
	    acl_type, acl_id, description,
	    TO_CHAR(modtime, 'DD-mon-YYYY HH24:MI:SS'), modby, modwith
	  INTO :listname, :active, :public, :hidden, :hid, :maillist,
            :grouplist, :gid_str, :acl_type, :acl_id, :desc,
	    :modtime, :modby_id, :modwith
          FROM list WHERE list_id = :id;

	if (dbms_errno) return(mr_errcode);
	strtrim(acl_type);

	if (atoi(gid_str) == -1)
	    argv[6] = UNIQUE_GID;

	argv[8] = malloc(0);
	if (!strcmp(acl_type, "LIST")) {
	    status = id_to_name(acl_id, LIST_TABLE, &argv[8]);
	} else if (!strcmp(acl_type, "USER")) {
	    status = id_to_name(acl_id, USERS_TABLE, &argv[8]);
	} else if (!strcmp(acl_type, "KERBEROS")) {
	    status = id_to_name(acl_id, STRINGS_TABLE, &argv[8]);
	} else if (!strcmp(acl_type, "NONE")) {
	    status = 0;
	    free(argv[8]);
	    argv[8] = strsave("NONE");
	} else {
	    status = 0;
	    free(argv[8]);
	    argv[8] = strsave("???");
	}
	if (status && status != MR_NO_MATCH) return(status);

	argv[11] = malloc(0);
	if (modby_id > 0)
	  status = id_to_name(modby_id, USERS_TABLE, &argv[11]);
	else
	  status = id_to_name(-modby_id, STRINGS_TABLE, &argv[11]);
	if (status && status != MR_NO_MATCH) return(status);

	mr_trim_args(q->vcnt, argv);
	returned++;
	(*action)(q->vcnt, argv, actarg);
	free(argv[8]);
	free(argv[11]);
    }

    sq_destroy(sq);
    if (dbms_errno) return(mr_errcode);
    return (MR_SUCCESS);
}


/* Add_member_to_list: do list flattening as we go!  MAXLISTDEPTH is
 * how many different ancestors a member is allowed to have.
 */

#define MAXLISTDEPTH	1024

int add_member_to_list(q, argv, cl)
     struct query *q;
     char **argv;
     client *cl;
{
    EXEC SQL BEGIN DECLARE SECTION;
    int id, lid, mid, error, who, ref, rowcnt;
    char *mtype, dtype[9], *entity;
    EXEC SQL END DECLARE SECTION;
    int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
    int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
    int status;
    char *dtypes[MAXLISTDEPTH];
    char *iargv[3], *buf;

    lid = *(int *)argv[0];
    mtype = argv[1];
    mid = *(int *)argv[2];
    /* if the member is already a direct member of the list, punt */
    EXEC SQL SELECT COUNT(list_id) INTO :rowcnt FROM imembers
      WHERE list_id = :lid AND member_id = :mid
	AND member_type = :mtype AND direct = 1;
    if (rowcnt > 0)
      return(MR_EXISTS);
    if (!strcasecmp(mtype, "STRING")) {
	buf = malloc(0);
	status = id_to_name(mid, STRINGS_TABLE, &buf);
	if (status) return(status);
	if (strchr(buf, '/') || strchr(buf, '|')) {
	    free(buf);
	    return(MR_BAD_CHAR);
	}
	free(buf);
    }

    ancestors[0] = lid;
    aref[0] = 1;
    acount = 1;
    EXEC SQL DECLARE csr103 CURSOR FOR
      SELECT list_id, ref_count	FROM imembers
	WHERE member_id = :lid AND member_type='LIST';
    if (dbms_errno)
	return(mr_errcode);
    EXEC SQL OPEN csr103;
    if (dbms_errno)
	return(mr_errcode);
    while(1) {
	EXEC SQL FETCH csr103 INTO :id, :ref;
	if(sqlca.sqlcode != 0) break;
	aref[acount] = ref;
	ancestors[acount++] = id;
	if (acount >= MAXLISTDEPTH) break;
    }
    EXEC SQL CLOSE csr103;
    if (dbms_errno) return(mr_errcode);
    if (acount >= MAXLISTDEPTH) {
	return(MR_INTERNAL);
    }
    descendants[0] = mid;
    dtypes[0] = mtype;
    dref[0] = 1;
    dcount = 1;
    error = 0;
    if (!strcmp(mtype, "LIST")) {
	EXEC SQL DECLARE csr104 CURSOR FOR
	  SELECT member_id, member_type, ref_count
	  FROM imembers
	  WHERE list_id = :mid;
	if (dbms_errno)
	    return(mr_errcode);
	EXEC SQL OPEN csr104;
	if (dbms_errno)
	    return(mr_errcode);
	while(1) {
	    EXEC SQL FETCH csr104 INTO :id, :dtype, :ref;
	    if(sqlca.sqlcode != 0) break;
	    switch (dtype[0]) {
	    case 'L':
		dtypes[dcount] = "LIST";
		break;
	    case 'U':
		dtypes[dcount] = "USER";
		break;
	    case 'S':
		dtypes[dcount] = "STRING";
		break;
	    case 'K':
		dtypes[dcount] = "KERBEROS";
		break;
	    default:
		error++;
		break;
	    }
	    dref[dcount] = ref;
	    descendants[dcount++] = id;
	    if (dcount >= MAXLISTDEPTH) {
		error++;
		break;
	    }
  	}
	EXEC SQL CLOSE csr104;
	if (dbms_errno) return(mr_errcode);
	if (error)
	  return(MR_INTERNAL);
    }
    for (a = 0; a < acount; a++) {
	lid = ancestors[a];
	for (d = 0; d < dcount; d++) {
	    mid = descendants[d];
	    mtype = dtypes[d];
	    if (mid == lid && !strcmp(mtype, "LIST")) {
		return(MR_LISTLOOP);
	    }
	    EXEC SQL SELECT COUNT(ref_count) INTO :rowcnt 
		FROM imembers
	      WHERE list_id = :lid AND member_id = :mid
		AND member_type = :mtype;
	    ref = aref[a] * dref[d];
	    if (rowcnt > 0) {
		if (a == 0 && d == 0) {
		    EXEC SQL UPDATE imembers
		      SET ref_count = ref_count+:ref, direct=1
		      WHERE list_id = :lid AND member_id = :mid
			AND member_type = :mtype;
		} else {
		    EXEC SQL UPDATE imembers
		      SET ref_count = ref_count+:ref
		      WHERE list_id = :lid AND member_id = :mid
			AND member_type = :mtype;
		}
	    } else {
		incremental_clear_before();
		if (a == 0 && d == 0) {
		  EXEC SQL INSERT INTO imembers
		    (list_id, member_id, direct, member_type, ref_count)
		    VALUES (:lid, :mid, 1, :mtype, 1);
		} else {
		  EXEC SQL INSERT INTO imembers
		    (list_id, member_id, direct, member_type, ref_count)
		    VALUES (:lid, :mid, 0, :mtype, 1);
		}
		iargv[0] = (char *)lid;
		iargv[1] = mtype;
		iargv[2] = (char *)mid;
		incremental_after(IMEMBERS_TABLE, 0, iargv);
	    }
	}
    }
    lid = *(int *)argv[0];
    entity = cl->entity;
    who = cl->client_id;
    EXEC SQL UPDATE list
      SET modtime=SYSDATE, modby = :who, modwith = :entity
      WHERE list_id = :lid;
    if (dbms_errno) return(mr_errcode);
    return(MR_SUCCESS);
}


/* Delete_member_from_list: do list flattening as we go!
 */

int delete_member_from_list(q, argv, cl)
     struct query *q;
     char **argv;
     client *cl;
{
    EXEC SQL BEGIN DECLARE SECTION;
    int id, lid, mid, cnt, error, who, ref;
    char *mtype, dtype[9], *entity;
    EXEC SQL END DECLARE SECTION;
    int ancestors[MAXLISTDEPTH], aref[MAXLISTDEPTH], acount, a;
    int descendants[MAXLISTDEPTH], dref[MAXLISTDEPTH], dcount, d;
    char *dtypes[MAXLISTDEPTH];
    char *iargv[3];

    lid = *(int *)argv[0];
    mtype = argv[1];
    mid = *(int *)argv[2];
    /* if the member is not a direct member of the list, punt */
    EXEC SQL SELECT COUNT(list_id) INTO :cnt FROM imembers
      WHERE list_id = :lid AND member_id = :mid
	AND member_type = :mtype AND direct = 1;
    if (dbms_errno) return(mr_errcode);
    if (cnt == 0)
      return(MR_NO_MATCH);
    ancestors[0] = lid;
    aref[0] = 1;
    acount = 1;
    EXEC SQL DECLARE csr105 CURSOR FOR
      SELECT list_id, ref_count FROM imembers
	WHERE member_id = :lid AND member_type = 'LIST';
    if (dbms_errno)
	return(mr_errcode);
    EXEC SQL OPEN csr105;
    if (dbms_errno)
	return(mr_errcode);
    while(1) {
	EXEC SQL FETCH csr105 INTO :id, :ref;
	if(sqlca.sqlcode!=0) break;
	aref[acount] = ref;
	ancestors[acount++] = id;
	if (acount >= MAXLISTDEPTH) break;
    }
    EXEC SQL CLOSE csr105;
    if (dbms_errno)
      return(mr_errcode);
    if (acount >= MAXLISTDEPTH)
      return(MR_INTERNAL);
    descendants[0] = mid;
    dtypes[0] = mtype;
    dref[0] = 1;
    dcount = 1;
    error = 0;
    if (!strcmp(mtype, "LIST")) {
	EXEC SQL DECLARE csr106 CURSOR FOR
	  SELECT member_id, member_type, ref_count FROM imembers
	    WHERE list_id = :mid;
	if (dbms_errno)
	    return(mr_errcode);
	EXEC SQL OPEN csr106;
	if (dbms_errno)
	    return(mr_errcode);
	while(1) {
	    EXEC SQL FETCH csr106 INTO :id, :dtype, :ref;
	    if(sqlca.sqlcode!=0) break;
	    switch (dtype[0]) {
	    case 'L':
		dtypes[dcount] = "LIST";
		break;
	    case 'U':
		dtypes[dcount] = "USER";
		break;
	    case 'S':
		dtypes[dcount] = "STRING";
		break;
	    case 'K':
		dtypes[dcount] = "KERBEROS";
		break;
	    default:
		error++;
		break;
	    }
	    dref[dcount] = ref;
	    descendants[dcount++] = id;
	    if (dcount >= MAXLISTDEPTH) break;
	}
	EXEC SQL CLOSE csr106;
	if (dbms_errno)
	  return(mr_errcode);
	if (error)
	  return(MR_INTERNAL);
    }
    for (a = 0; a < acount; a++) {
	lid = ancestors[a];
	for (d = 0; d < dcount; d++) {
	    mid = descendants[d];
	    mtype = dtypes[d];
	    if (mid == lid && !strcmp(mtype, "LIST")) {
		return(MR_LISTLOOP);
	    }
	    EXEC SQL SELECT ref_count INTO :cnt FROM imembers
	      WHERE list_id = :lid AND member_id = :mid	AND member_type = :mtype;
	    ref = aref[a] * dref[d];
	    if (cnt <= ref) {
		iargv[0] = (char *)lid;
		iargv[1] = mtype;
		iargv[2] = (char *)mid;
		incremental_before(IMEMBERS_TABLE, 0, iargv);
		EXEC SQL DELETE FROM imembers
		  WHERE list_id = :lid AND member_id = :mid
		    AND member_type= :mtype;
		incremental_clear_after();
	    } else if (a == 0 && d == 0) {
		EXEC SQL UPDATE imembers
		  SET ref_count = ref_count - :ref, direct = 0
		  WHERE list_id = :lid AND member_id = :mid
		    AND member_type = :mtype;
	    } else {
		EXEC SQL UPDATE imembers
		  SET ref_count = ref_count - :ref
		  WHERE list_id = :lid AND member_id = :mid
		    AND member_type = :mtype;
	    }
	}
    }
    lid = *(int *)argv[0];
    entity = cl->entity;
    who = cl->client_id;
    EXEC SQL UPDATE list SET modtime = SYSDATE, modby = :who, modwith = :entity
      WHERE list_id = :lid;
    if (dbms_errno) return(mr_errcode);
    return(MR_SUCCESS);
}


/* get_ace_use - given a type and a name, return a type and a name.
 * The ace_type is one of "LIST", "USER", "RLIST", or "RUSER" in argv[0],
 * and argv[1] will contain the ID of the entity in question.  The R*
 * types mean to recursively look at every containing list, not just
 * when the object in question is a direct member.  On return, the
 * usage type will be one of LIST, SERVICE, FILESYS, QUOTA, QUERY, or ZEPHYR.
 */

int get_ace_use(q, argv, cl, action, actarg)
     struct query *q;
     char *argv[];
     client *cl;
     int (*action)(), actarg;
{
    int found = 0;
    EXEC SQL BEGIN DECLARE SECTION;
    char *atype;
    int aid, listid, id;
    EXEC SQL END DECLARE SECTION;
    struct save_queue *sq, *sq_create();

    atype = argv[0];
    aid = *(int *)argv[1];
    if (!strcmp(atype, "LIST") || !strcmp(atype, "USER") ||
	!strcmp(atype, "KERBEROS")) {
	return(get_ace_internal(atype, aid, action, actarg));
    }

    sq = sq_create();
    if (!strcmp(atype, "RLIST")) {
	sq_save_data(sq, (char *)aid);
	/* get all the list_id's of containing lists */
	EXEC SQL DECLARE csr107 CURSOR FOR
	  SELECT list_id FROM imembers
	    WHERE member_type='LIST' AND member_id = :aid;
	if (dbms_errno)
	    return(mr_errcode);
	EXEC SQL OPEN csr107;
	if (dbms_errno)
	    return(mr_errcode);
	while(1) {
	    EXEC SQL FETCH csr107 INTO :listid;
	    if(sqlca.sqlcode != 0) break;
	    sq_save_unique_data(sq, (char *)listid);
	}
	EXEC SQL CLOSE csr107;
	/* now process each one */
	while (sq_get_data(sq, &id)) {
	    if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
	      found++;
	}
    }

    if (!strcmp(atype, "RUSER")) {
	EXEC SQL DECLARE csr108 CURSOR FOR
	  SELECT list_id FROM imembers
	    WHERE member_type='USER' AND member_id = :aid;
	if (dbms_errno)
	    return(mr_errcode);
	EXEC SQL OPEN csr108;
	if (dbms_errno)
	    return(mr_errcode);
	while(1) {
	    EXEC SQL FETCH csr108 INTO :listid;
	    if(sqlca.sqlcode != 0) break;
	    sq_save_data(sq, (char *)listid);
	}
	EXEC SQL CLOSE csr108;
	/* now process each one */
	while (sq_get_data(sq, &id)) {
	    if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
	      found++;
	}
	if (get_ace_internal("USER", aid, action, actarg) == MR_SUCCESS)
	  found++;
    }

    if (!strcmp(atype, "RKERBEROS")) {
	EXEC SQL DECLARE csr109 CURSOR FOR
	  SELECT list_id FROM imembers
	    WHERE member_type='KERBEROS' AND member_id = :aid;
	if (dbms_errno)
	    return(mr_errcode);
	EXEC SQL OPEN csr109;
	if (dbms_errno)
	    return(mr_errcode);
	while(1) {
	    EXEC SQL FETCH csr109 INTO :listid;
	    if(sqlca.sqlcode != 0) break;
	    sq_save_data(sq, (char*)listid);
	}
	EXEC SQL CLOSE csr109;
	/* now process each one */
	while (sq_get_data(sq, &id)) {
	    if (get_ace_internal("LIST", id, action, actarg) == MR_SUCCESS)
	      found++;
	}
	if (get_ace_internal("KERBEROS", aid, action, actarg) == MR_SUCCESS)
	  found++;
    }

    sq_destroy(sq);
    if (dbms_errno) return(mr_errcode);
    if (!found) return(MR_NO_MATCH);
    return(MR_SUCCESS);
}


/* This looks up a single list or user for ace use.  atype must be "USER"
 * or "LIST", and aid is the ID of the corresponding object.  This is used
 * by get_ace_use above.
 */

int get_ace_internal(atype, aid, action, actarg)
     char *atype;
     int aid, (*action)(), actarg;
{
    char *rargv[2];
    int found = 0;
    EXEC SQL BEGIN DECLARE SECTION;
    char name[33], *type = atype;
    int id = aid;
    EXEC SQL END DECLARE SECTION;

    rargv[1] = name;
    if (!strcmp(atype, "LIST")) {
	rargv[0] = "FILESYS";
	EXEC SQL DECLARE csr110 CURSOR FOR
	  SELECT label FROM filesys
	    WHERE owners = :id;
	if (dbms_errno)
	    return(mr_errcode);
	EXEC SQL OPEN csr110;
	if (dbms_errno)
	    return(mr_errcode);
	while(1) {
	    EXEC SQL FETCH csr110 INTO :name;
	    if(sqlca.sqlcode != 0) break;
	    (*action)(2, rargv, actarg);
	    found++;
	}
	EXEC SQL CLOSE csr110;

	rargv[0] = "QUERY";
	EXEC SQL DECLARE csr111 CURSOR FOR
	  SELECT capability FROM capacls
	    WHERE list_id = :id ;
	if (dbms_errno)
	    return(mr_errcode);
	EXEC SQL OPEN csr111;
	if (dbms_errno)
	    return(mr_errcode);
	while(1) {
	    EXEC SQL FETCH csr111 INTO :name ;
	    if(sqlca.sqlcode != 0) break;
	    (*action)(2, rargv, actarg);
	    found++;
	}
	EXEC SQL CLOSE csr111;
    } else if (!strcmp(atype, "USER")) {
	rargv[0] = "FILESYS";
	EXEC SQL DECLARE csr112 CURSOR FOR
	  SELECT label FROM filesys
	    WHERE owner = :id;
	if (dbms_errno)
	    return(mr_errcode);
	EXEC SQL OPEN csr112;
	if (dbms_errno)
	    return(mr_errcode);
	while(1) {
	    EXEC SQL FETCH csr112 INTO :name ;
	    if(sqlca.sqlcode != 0) break;
	    (*action)(2, rargv, actarg);
	    found++;
	}
	EXEC SQL CLOSE csr112;
    }

    rargv[0] = "LIST";
    EXEC SQL DECLARE csr113 CURSOR FOR
      SELECT name FROM list
	WHERE acl_type = :type AND acl_id = :id;
    if (dbms_errno)
	return(mr_errcode);
    EXEC SQL OPEN csr113;
    if (dbms_errno)
	return(mr_errcode);
    while(1) {
	EXEC SQL FETCH csr113 INTO :name;
	if(sqlca.sqlcode != 0) break;
	(*action)(2, rargv, actarg);
	found++;
    }
    EXEC SQL CLOSE csr113;

    rargv[0] = "SERVICE";
    EXEC SQL DECLARE csr114 CURSOR FOR
      SELECT name FROM servers
	WHERE acl_type = :type AND acl_id = :id;
    if (dbms_errno)
	return(mr_errcode);
    EXEC SQL OPEN csr114;
    if (dbms_errno)
	return(mr_errcode);
    while(1) {
	EXEC SQL FETCH csr114 INTO :name;
	if(sqlca.sqlcode != 0) break;
	(*action)(2, rargv, actarg);
	found++;
    }
    EXEC SQL CLOSE csr114;

    rargv[0] = "HOSTACCESS";
    EXEC SQL DECLARE csr115 CURSOR FOR
      SELECT name FROM machine m, hostaccess ha
	WHERE m.mach_id = ha.mach_id AND ha.acl_type = :type
	  AND ha.acl_id = :id;
    if (dbms_errno)
	return(mr_errcode);
    EXEC SQL OPEN csr115;
    if (dbms_errno)
	return(mr_errcode);
    while(1) {
	EXEC SQL FETCH csr115 INTO :name;
	if(sqlca.sqlcode != 0) break;
	(*action)(2, rargv, actarg);
	found++;
    }
    EXEC SQL CLOSE csr115;

    rargv[0] = "ZEPHYR";
    EXEC SQL DECLARE csr116 CURSOR FOR
      SELECT class FROM zephyr z
	WHERE z.xmt_type = :type AND z.xmt_id = :id
	  OR z.sub_type = :type AND z.sub_id = :id
	  OR z.iws_type = :type AND z.iws_id = :id
          OR z.iui_type = :type AND z.iui_id = :id;
    if (dbms_errno)
	return(mr_errcode);
    EXEC SQL OPEN csr116;
    if (dbms_errno)
	return(mr_errcode);
    while(1) {
	EXEC SQL FETCH csr116 INTO :name;
	if(sqlca.sqlcode != 0) break;
	(*action)(2, rargv, actarg);
	found++;
    }
    EXEC SQL CLOSE csr116;

    if (!found) return(MR_NO_MATCH);
    return(MR_SUCCESS);
}


/* get_lists_of_member - given a type and a name, return the name and flags
 * of all of the lists of the given member.  The member_type is one of
 * "LIST", "USER", "STRING", "RLIST", "RUSER", or "RSTRING" in argv[0],
 * and argv[1] will contain the ID of the entity in question.  The R*
 * types mean to recursively look at every containing list, not just
 * when the object in question is a direct member.
 */

int get_lists_of_member(q, argv, cl, action, actarg)
     struct query *q;
     char *argv[];
     client *cl;
     int (*action)(), actarg;
{
    int found = 0, direct = 1;
    char *rargv[6];
    EXEC SQL BEGIN DECLARE SECTION;
    char *atype;
    int aid;
    char name[33], active[5], public[5], hidden[5], maillist[5], grouplist[5];
    EXEC SQL END DECLARE SECTION;

    atype = argv[0];
    aid = *(int *)argv[1];
    if (!strcmp(atype, "RLIST")) {
	atype = "LIST";
	direct = 0;
    }
    if (!strcmp(atype, "RUSER")) {
	atype = "USER";
	direct = 0;
    }
    if (!strcmp(atype, "RSTRING")) {
	atype = "STRING";
	direct = 0;
    }
    if (!strcmp(atype, "RKERBEROS")) {
	atype = "KERBEROS";
	direct = 0;
    }

    rargv[0] = name;
    rargv[1] = active;
    rargv[2] = public;
    rargv[3] = hidden;
    rargv[4] = maillist;
    rargv[5] = grouplist;
    if (direct) {
	EXEC SQL DECLARE csr117a CURSOR FOR
	  SELECT l.name, l.active, l.publicflg, l.hidden,
  	      l.maillist, l.grouplist
	    FROM list l, imembers im
	    WHERE l.list_id = im.list_id AND im.direct = 1
	      AND im.member_type = :atype AND im.member_id = :aid;
	if (dbms_errno)
	    return(mr_errcode);
	EXEC SQL OPEN csr117a;
	if (dbms_errno)
	    return(mr_errcode);
	while(1) {
	    EXEC SQL FETCH csr117a
	      INTO :name, :active, :public, :hidden, :maillist, :grouplist;
	    if(sqlca.sqlcode != 0) break;
	    (*action)(6, rargv, actarg);
	    found++;
	}
	EXEC SQL CLOSE csr117a;
    } else {
	EXEC SQL DECLARE csr117b CURSOR FOR
	  SELECT l.name, l.active, l.publicflg, l.hidden,
	      l.maillist, l.grouplist
	    FROM list l, imembers im
	    WHERE l.list_id = im.list_id
	      AND im.member_type = :atype AND im.member_id = :aid;
	if (dbms_errno)
	    return(mr_errcode);
	EXEC SQL OPEN csr117b;
	if (dbms_errno)
	    return(mr_errcode);
	while(1) {
	    EXEC SQL FETCH csr117b
	      INTO :name, :active, :public, :hidden, :maillist, :grouplist;
	    if(sqlca.sqlcode != 0) break;
	    (*action)(6, rargv, actarg);
	    found++;
	}
	EXEC SQL CLOSE csr117b;
    }

    if (dbms_errno) return(mr_errcode);
    if (!found) return(MR_NO_MATCH);
    return(MR_SUCCESS);
}


/* qualified_get_lists: passed "TRUE", "FALSE", or "DONTCARE" for each of
 * the five flags associated with each list.  It will return the name of
 * each list that meets the quailifications.  It does this by building a
 * where clause based on the arguments, then doing a retrieve.
 */

static char *lflags[5] = { "active", "publicflg", "hidden", "maillist", "grouplist" };

int qualified_get_lists(q, argv, cl, action, actarg)
     struct query *q;
     char *argv[];
     client *cl;
     int (*action)(), actarg;
{
    return(qualified_get(q, argv, action, actarg, "l.list_id != 0",
			 "l", "name", lflags));
}


/* get_members_of_list - this gets only direct members */
 
int get_members_of_list(q, argv, cl, action, actarg)
     struct query *q;
     char *argv[];
     client *cl;
     int (*action)(), actarg;
{
    return(gmol_internal(q, argv, cl, action, actarg, 1));
}
 
/* get_end_members_of_list - this gets direct or indirect members */
 
int get_end_members_of_list(q, argv, cl, action, actarg)
     struct query *q;
     char *argv[];
     client *cl;
     int (*action)(), actarg;
{
    return(gmol_internal(q, argv, cl, action, actarg, 0));
}
 
/** gmol_internal - optimized query for retrieval of list members
 **   used by both get_members_of_list and get_end_members_of_list
 **
 ** Inputs:
 **   argv[0] - list_id
 **
 ** Description:
 **   - retrieve USER members, then LIST members, then STRING members
 **/

int gmol_internal(q, argv, cl, action, actarg, flag)
     struct query *q;
     char *argv[];
     client *cl;
     int (*action)(), actarg, flag;
{
    EXEC SQL BEGIN DECLARE SECTION;
    int list_id, member_id, direct;
    char member_name[129], member_type[9];
    EXEC SQL END DECLARE SECTION;
    char *targv[2];
    int members;

    /* true/false flag indicates whether to display only direct members. */
    if (flag)
      direct = 0;
    else
      direct = -1;

    list_id = *(int *)argv[0];

    targv[1] = member_name;
    targv[0] = "USER";
    EXEC SQL DECLARE csr119 CURSOR FOR
      SELECT u.login FROM users u, imembers im
	WHERE im.list_id = :list_id AND im.member_type = 'USER'
	  AND im.member_id = u.users_id AND im.direct > :direct
	ORDER BY 1;
    if (dbms_errno)
	return(mr_errcode);
    EXEC SQL OPEN csr119;
    if (dbms_errno)
	return(mr_errcode);
    while(1) {
	EXEC SQL FETCH csr119 INTO :member_name;
	if(sqlca.sqlcode != 0) break;
	(*action)(2, targv, actarg);
    }
    EXEC SQL CLOSE csr119;
    if (dbms_errno) return(mr_errcode);

    targv[0] = "LIST";
    EXEC SQL DECLARE csr120 CURSOR FOR
      SELECT l.name FROM list l, imembers im
	WHERE im.list_id = :list_id AND im.member_type='LIST'
	  AND im.member_id = l.list_id AND im.direct > :direct
        ORDER BY 1;
    if (dbms_errno)
	return(mr_errcode);
    EXEC SQL OPEN csr120;
    if (dbms_errno)
	return(mr_errcode);
    while(1) {
	EXEC SQL FETCH csr120 INTO :member_name;
	if(sqlca.sqlcode != 0) break;
	(*action)(2, targv, actarg);
    }
    EXEC SQL CLOSE csr120;
    if (dbms_errno) return(mr_errcode);

    targv[0] = "STRING";
    EXEC SQL DECLARE csr121 CURSOR FOR
      SELECT str.string FROM strings str, imembers im
	WHERE im.list_id = :list_id AND im.member_type='STRING'
	  AND im.member_id = str.string_id AND im.direct > :direct
	ORDER BY 1;
    if (dbms_errno)
	return(mr_errcode);
    EXEC SQL OPEN csr121;
    if (dbms_errno)
	return(mr_errcode);
    while(1) {
	EXEC SQL FETCH csr121 INTO :member_name;
	if(sqlca.sqlcode != 0) break;
	(*action)(2, targv, actarg);
    }
    EXEC SQL CLOSE csr121;
    if (dbms_errno) return(mr_errcode);

    targv[0] = "KERBEROS";
    EXEC SQL DECLARE csr122 CURSOR FOR
      SELECT str.string FROM strings str, imembers im
	WHERE im.list_id = :list_id AND im.member_type='KERBEROS'
	  AND im.member_id = str.string_id 
	  AND im.direct > :direct
	ORDER BY 1;
    if (dbms_errno)
	return(mr_errcode);
    EXEC SQL OPEN csr122;
    if (dbms_errno)
	return(mr_errcode);
    while(1) {
	EXEC SQL FETCH csr122 INTO :member_name;
	if(sqlca.sqlcode != 0) break;
	(*action)(2, targv, actarg);
    }
    EXEC SQL CLOSE csr122;
    if (dbms_errno) return(mr_errcode);

    return(MR_SUCCESS);
}


/* count_members_of_list: this is a simple query, but it cannot be done
 * through the dispatch table.
 */

int count_members_of_list(q, argv, cl, action, actarg)
     struct query *q;
     char *argv[];
     client *cl;
     int (*action)(), actarg;
{
    EXEC SQL BEGIN DECLARE SECTION;
    int  list, ct = 0;
    EXEC SQL END DECLARE SECTION;
    char *rargv[1], countbuf[5];

    list = *(int *)argv[0];
    rargv[0] = countbuf;
    EXEC SQL SELECT count (*) INTO :ct FROM imembers
      WHERE list_id = :list AND direct=1;
    if (dbms_errno) return(mr_errcode);
    sprintf(countbuf, "%d", ct);
    (*action)(1, rargv, actarg);
    return(MR_SUCCESS);
}


/* qualified_get_server: passed "TRUE", "FALSE", or "DONTCARE" for each of
 * the three flags associated with each service.  It will return the name of
 * each service that meets the quailifications.  It does this by building a
 * where clause based on the arguments, then doing a retrieve.
 */

static char *sflags[3] = { "enable", "inprogress", "harderror" };

int qualified_get_server(q, argv, cl, action, actarg)
     struct query *q;
     char *argv[];
     client *cl;
     int (*action)(), actarg;
{
    return(qualified_get(q, argv, action, actarg, "s.name is not null",
			 "s", "name", sflags));
    /* of course, name will never be null, but we need something there
       to make qualified_get happy */
}


/* generic qualified get routine, used by qualified_get_lists,
 * qualified_get_server, and qualified_get_serverhost.
 *   Args:
 *	start - a simple where clause, must not be empty
 *	range - the name of the range variable
 *	field - the field to return
 *	flags - an array of strings, names of the flag variables
 */

int qualified_get(q, argv, action, actarg, start, range, field, flags)
     struct query *q;
     char *argv[], *start, *range, *field, *flags[];
     int (*action)(), actarg;		  
{
    char qual[256];
    int i;
    char buf[32];

    strcpy(qual, start);
    for (i = 0; i < q->argc; i++) {
	if (!strcmp(argv[i], "TRUE")) {
	    sprintf(buf, " AND %s.%s != 0", range, flags[i]);
	    (void) strcat(qual, buf);
	} else if (!strcmp(argv[i], "FALSE")) {
	    sprintf(buf, " AND %s.%s = 0", range, flags[i]);
	    (void) strcat(qual, buf);
	}
    }

    sprintf(stmt_buf,"SELECT %s.%s FROM %s %s WHERE %s",range,field,
	    table_name[q->rtable],range,qual);
    return do_for_all_rows(stmt_buf, 1, action, actarg);
}


/* qualified_get_serverhost: passed "TRUE", "FALSE", or "DONTCARE" for each of
 * the five flags associated with each serverhost.  It will return the name of
 * each service and host that meets the quailifications.  It does this by
 * building a where clause based on the arguments, then doing a retrieve.
 */

static char *shflags[6] = { "service", "enable", "override", "success",
			    "inprogress", "hosterror" };

int qualified_get_serverhost(q, argv, cl, action, actarg)
     struct query *q;
     char *argv[];
     client *cl;
     int (*action)(), actarg;
{
    char qual[256], buf[32];
    int i;

    sprintf(qual, "m.mach_id = sh.mach_id AND sh.service = UPPER('%s')",
	    argv[0]);
    for (i = 1; i < q->argc; i++) {
	if (!strcmp(argv[i], "TRUE")) {
	    sprintf(buf, " AND sh.%s != 0", shflags[i]);
	    strcat(qual, buf);
	} else if (!strcmp(argv[i], "FALSE")) {
	    sprintf(buf, " AND sh.%s = 0", shflags[i]);
	    strcat(qual, buf);
	}
    }

    sprintf(stmt_buf, "SELECT sh.service, m.name FROM serverhosts sh, "
	    "machine m WHERE %s", qual);
    return do_for_all_rows(stmt_buf, 2, action, actarg);
}


/* register_user - change user's login name and allocate a pobox, group,
 * filesystem, and quota for them.  The user's status must start out as 0,
 * and is left as 2.  Arguments are: user's UID, new login name, and user's
 * type for filesystem allocation (MR_FS_STUDENT, MR_FS_FACULTY,
 * MR_FS_STAFF, MR_FS_MISC).
 */

int register_user(q, argv, cl)
     struct query *q;
     char **argv;
     client *cl;
{
    EXEC SQL BEGIN DECLARE SECTION;
    char *login, *entity, directory[129], machname[33];
    int who, rowcount, mid, uid, users_id, utype, list_id;
    int ostatus, nstatus, gidval, fsidval;
    static int m_id = 0, def_quota = 0;
    EXEC SQL END DECLARE SECTION;
    char buffer[256], *aargv[3];

    entity = cl->entity;
    who = cl->client_id;

    uid = atoi(argv[0]);
    login = argv[1];
    utype = atoi(argv[2]);

    /* find user */
    EXEC SQL SELECT users_id, status INTO :users_id, :ostatus
      FROM users
      WHERE unix_uid = :uid AND (status=0 OR status=5 OR status=6);

    if (sqlca.sqlerrd[2] == 0)
      return(MR_NO_MATCH);
    if (sqlca.sqlerrd[2] > 1)
      return(MR_NOT_UNIQUE);

    /* check new login name */
    EXEC SQL SELECT COUNT(login) INTO :rowcount FROM users
      WHERE login = :login AND users_id != :users_id;
    if (dbms_errno) return(mr_errcode);
    if (rowcount > 0) return(MR_IN_USE);
    EXEC SQL SELECT COUNT(name) INTO :rowcount FROM list
      WHERE name = :login;
    if (dbms_errno) return(mr_errcode);
    if (rowcount > 0) return(MR_IN_USE);
    EXEC SQL SELECT COUNT(label) INTO :rowcount FROM filesys
      WHERE label = :login;
    if (dbms_errno) return(mr_errcode);
    if (rowcount > 0) return(MR_IN_USE);
    EXEC SQL SELECT COUNT(name) INTO :rowcount FROM alias
      WHERE name = :login AND type='FILESYS';
    if (dbms_errno) return(mr_errcode);
    if (rowcount > 0) return(MR_IN_USE);
    com_err(whoami, 0, "login name OK");

    /* choose place for pobox, put in mid */
    EXEC SQL DECLARE csr130 CURSOR FOR
      SELECT sh.mach_id, m.name FROM serverhosts sh, machine m
      WHERE sh.service='POP' AND sh.mach_id=m.mach_id
	AND sh.value2 - sh.value1 =
	  (SELECT MAX(value2 - value1) FROM serverhosts
	    WHERE service = 'POP');
    if (dbms_errno)
	return(mr_errcode);
    EXEC SQL OPEN csr130;
    if (dbms_errno)
	return(mr_errcode);
    EXEC SQL FETCH csr130 INTO :mid, :machname;
    if (sqlca.sqlerrd[2] == 0) {
	EXEC SQL CLOSE csr130;
	if (dbms_errno) return(mr_errcode);
	return(MR_NO_POBOX);
    } else {
	EXEC SQL CLOSE csr130;
	if (dbms_errno) return(mr_errcode);
    }

    /* change login name, set pobox */
    sprintf(buffer, "u.users_id = %d", users_id);
    incremental_before(USERS_TABLE, buffer, 0);
    nstatus = 2;
    if (ostatus == 5 || ostatus == 6)
      nstatus = 1;
    EXEC SQL UPDATE users SET login = :login, status = :nstatus,
        modtime=SYSDATE, modby = :who, modwith = :entity, potype='POP',
        pop_id = :mid, pmodtime=SYSDATE, pmodby = :who, pmodwith = :entity
      WHERE users_id = :users_id;

    if (dbms_errno) return(mr_errcode);
    if (sqlca.sqlerrd[2] != 1)
      return(MR_INTERNAL);
    set_pop_usage(mid, 1);
    com_err(whoami, 0, "set login name to %s and pobox to %s", login,
	    strtrim(machname));
    incremental_after(USERS_TABLE, buffer, 0);

    if (m_id == 0) {
        /* Cell Name (I know, it shouldn't be hard coded...) */
        strcpy(machname, "ATHENA.MIT.EDU");
	EXEC SQL SELECT mach_id INTO :m_id FROM machine 
	  WHERE name = :machname;
    }

    EXEC SQL SELECT list_id INTO :list_id FROM list
	WHERE name='wheel';

    /* create filesystem */
    if (set_next_object_id("filsys_id", FILESYS_TABLE, 0))
      return(MR_NO_ID);
    incremental_clear_before();
    if (islower(login[0]) && islower(login[1])) {
        sprintf(directory, "/afs/athena.mit.edu/user/%c/%c/%s",
                login[0], login[1], login);
    } else {
        sprintf(directory, "/afs/athena.mit.edu/user/other/%s", login);
    }
 
    EXEC SQL SELECT value INTO :fsidval FROM numvalues
      WHERE numvalues.name='filsys_id';
    EXEC SQL INSERT INTO filesys
        (filsys_id, phys_id, label, type, mach_id, name,
	 mount, rwaccess, comments, owner, owners, createflg,
	 lockertype, modtime, modby, modwith)
      VALUES
	(:fsidval, 0, :login, 'AFS', :m_id, :directory,
	 '/mit/' || :login, 'w', 'User Locker', :users_id, :list_id, 1,
	 'HOMEDIR', SYSDATE, :who, :entity);

    if (dbms_errno) return(mr_errcode);
    if (sqlca.sqlerrd[2]  != 1)
      return(MR_INTERNAL);
    sprintf(buffer,"fs.filsys_id = %d",fsidval);
    incremental_after(FILESYS_TABLE, buffer, 0);

    /* set quota */
    if (def_quota == 0) {
	EXEC SQL SELECT value INTO :def_quota FROM numvalues
	  WHERE name='def_quota';
	if (dbms_errno) return(mr_errcode);
	if (sqlca.sqlerrd[2] != 1)
	  return(MR_NO_QUOTA);
	
    }
    incremental_clear_before();
    EXEC SQL INSERT INTO quota
        (entity_id, filsys_id, type, quota, phys_id, modtime, modby, modwith)
      VALUES
        (0, :fsidval, 'ANY', :def_quota, 0, SYSDATE, :who, :entity);
    if (dbms_errno) return(mr_errcode);
    if (sqlca.sqlerrd[2] != 1)
      return(MR_INTERNAL);
    aargv[0] = login;
    aargv[1] = "ANY";
    aargv[2] = login;
    sprintf(buffer, "q.entity_id = 0 and q.filsys_id = %d and q.type = 'ANY'", fsidval);
    incremental_after(QUOTA_TABLE, buffer, aargv);
    com_err(whoami, 0, "quota of %d assigned", def_quota);
    if (dbms_errno) return(mr_errcode);

    cache_entry(login, USERS_TABLE, users_id);

    EXEC SQL UPDATE tblstats SET updates=updates+1, modtime=SYSDATE
      WHERE table_name='users';
    EXEC SQL UPDATE tblstats SET appends=appends+1, modtime=SYSDATE
      WHERE table_name='filesys' OR table_name='quota';
    if (dbms_errno) return(mr_errcode);
    return(MR_SUCCESS);
}



/** set_pop_usage - incr/decr usage count for pop server in serverhosts talbe
 **
 ** Inputs:
 **   id of machine
 **   delta (will be +/- 1)
 **
 ** Description:
 **   - incr/decr value field in serverhosts table for pop/mach_id
 **
 **/

int set_pop_usage(id, cnt)
    int id, cnt;
{
    EXEC SQL BEGIN DECLARE SECTION;
    int iid = id, icnt = cnt;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL UPDATE serverhosts SET value1 = value1 + :icnt
      WHERE serverhosts.service = 'POP' AND serverhosts.mach_id = :iid;

    if (dbms_errno) return(mr_errcode);
    return(MR_SUCCESS);
}