[1689] in Moira
Re: sidhistory in moira
daemon@ATHENA.MIT.EDU (Qing Dong)
Wed Oct 25 14:21:57 2000
Message-Id: <200010251821.OAA13121@melbourne-city-street.MIT.EDU>
Date: Wed, 25 Oct 2000 14:23:33 -0400
To: Garry Zacheiss <zacheiss@mit.edu>
From: Qing Dong <dongq@MIT.EDU>
Cc: moiradev@mit.edu
In-Reply-To: <200010240131.VAA01227@riff-raff-w20.mit.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Thanks for the correction. SIDs are byte strings so I can't
think of any reason why we can not use VARCHAR to store them.
SIDs normally take up to 32 bytes. I doubled it in case we want to
store other info, such as length. Let me know if you disagree.
Thanks.
Here is the new schema:
Index: schema.sql
===================================================================
RCS file:
\\dongq-afs\all\athena.mit.edu\astaff\project\moiradev\repository/moira/db/s
chema.sql,v
retrieving revision 1.19
diff -c -r1.19 schema.sql
*** schema.sql 2000/09/25 22:51:55 1.19
--- schema.sql 2000/10/25 18:16:39
***************
*** 380,382 ****
--- 380,397 ----
table_name VARCHAR(16) DEFAULT CHR(0) NOT NULL,
service VARCHAR(16) DEFAULT CHR(0) NOT NULL
);
+
+ create table usersids
+ (
+ users_id INTEGER DEFAULT 0 NOT NULL,
+ sid VARCHAR(64) DEFAULT CHR(0) NOT NULL,
+ created DATE DEFAULT SYSDATE NOT NULL
+ );
+
+ create table listsids
+ (
+ list_id INTEGER DEFAULT 0 NOT NULL,
+ sid VARCHAR(64) DEFAULT CHR(0) NOT NULL,
+ created DATE DEFAULT SYSDATE NOT NULL
+ );
+
At 09:31 PM 10/23/2000 -0400, Garry Zacheiss wrote:
> A couple of comments on this. The first is that the 'get'
>queries should return a login name and a list name instead of a users_id
>and a list_id; I've updated your patches to do that, and attached them
>below.
>
> My other comment is the data type that you're declaring the sid
>columns as. The line:
>
>sid RAW(64) DEFAULT CHR(0) NOT NULL,
>
>isn't valid; something like
>
>sid RAW(64) DEFAULT HEXTORAW(0) NOT NULL,
>
>is, and may be what we want. However, we have in the past used VARCHARs
>for binary data (the signature field in the users table). Is this
>acceptable?
>
> If we do decide to use RAWs for this, some changes will be
>needed to the awk scripts used by mrbackup; I'll take care of writing
>these if necessary.
>
>Garry
>
>Index: queries2.c
>===================================================================
>RCS file:
/afs/athena.mit.edu/astaff/project/moiradev/repository/moira/server/queries2
.c,v
>retrieving revision 2.78
>diff -c -r2.78 queries2.c
>*** queries2.c 2000/10/18 19:20:44 2.78
>--- queries2.c 2000/10/24 00:04:49
>***************
>*** 2945,2950 ****
>--- 2945,3019 ----
> _sdl_followup,
> };
>
>+ static char *gusl_fields[] = {
>+ "login",
>+ "login", "sid", "created",
>+ };
>+
>+ static struct validate gusl_validate =
>+ {
>+ VOuser0,
>+ 1,
>+ NULL,
>+ NULL,
>+ 0,
>+ 0,
>+ 0,
>+ 0,
>+ 0,
>+ };
>+
>+ static char *glsn_fields[] = {
>+ "name",
>+ "name", "sid", "created",
>+ };
>+
>+ static struct validate glsn_validate =
>+ {
>+ VOlist0,
>+ 1,
>+ NULL,
>+ NULL,
>+ 0,
>+ 0,
>+ 0,
>+ 0,
>+ 0,
>+ };
>+
>+ static char *ausl_fields[] = {
>+ "login", "sid",
>+ };
>+
>+ static struct validate ausl_validate =
>+ {
>+ VOuser0,
>+ 1,
>+ "sid",
>+ "sid = '%s'",
>+ 1,
>+ 0,
>+ 0,
>+ 0,
>+ 0,
>+ };
>+
>+ static char *alsn_fields[] = {
>+ "name", "sid",
>+ };
>+
>+ static struct validate alsn_validate =
>+ {
>+ VOlist0,
>+ 1,
>+ "sid",
>+ "sid = '%s'",
>+ 1,
>+ 0,
>+ 0,
>+ 0,
>+ 0,
>+ };
>
>
> /* Generalized Query Definitions */
>***************
>*** 6109,6114 ****
>--- 6178,6251 ----
> 0,
> NULL,
> &_sdl_validate,
>+ },
>+
>+ {
>+ /* Q_GUSL - GET_USER_SIDS_BY_LOGIN, v4 */
>+ "get_user_sids_by_login",
>+ "gusl",
>+ 4,
>+ RETRIEVE,
>+ "s",
>+ USERSIDS_TABLE,
>+ "u.login, us.sid, TO_CHAR(us.created, 'YYYY-MM-DD HH24:MI:SS') FROM
users u, usersids us",
>+ gusl_fields,
>+ 3,
>+ "us.users_id = %d AND u.users_id = us.users_id",
>+ 1,
>+ "us.created",
>+ &gusl_validate,
>+ },
>+
>+ {
>+ /* Q_AUSL - ADD_USER_SID_BY_LOGIN, v4 */
>+ "add_user_sid_by_login",
>+ "ausl",
>+ 4,
>+ APPEND,
>+ "s",
>+ USERSIDS_TABLE,
>+ "INTO usersids (users_id, sid) VALUES (%d, '%s')",
>+ ausl_fields,
>+ 2,
>+ NULL,
>+ 0,
>+ NULL,
>+ &ausl_validate,
>+ },
>+
>+ {
>+ /* Q_GLSN - GET_LIST_SIDS_BY_NAME, v4 */
>+ "get_list_sids_by_name",
>+ "glsn",
>+ 4,
>+ RETRIEVE,
>+ "s",
>+ LISTSIDS_TABLE,
>+ "l.name, ls.sid, TO_CHAR(ls.created, 'YYYY-MM-DD HH24:MI:SS') FROM
list l, listsids ls",
>+ glsn_fields,
>+ 3,
>+ "ls.list_id = %d AND l.list_id = ls.list_id",
>+ 1,
>+ "created",
>+ &glsn_validate,
>+ },
>+
>+ {
>+ /* Q_ALSN - ADD_LIST_SID_BY_NAME, v4 */
>+ "add_list_sid_by_name",
>+ "alsn",
>+ 4,
>+ APPEND,
>+ "s",
>+ LISTSIDS_TABLE,
>+ "INTO listsids (list_id, sid) VALUES (%d, '%s')",
>+ alsn_fields,
>+ 2,
>+ NULL,
>+ 0,
>+ NULL,
>+ &alsn_validate,
> },
>
> };
>
