3 * Load data into Moira from Registrar's Office data file
5 * Copyright (C) 1990-1998 by the Massachusetts Institute of Technology
6 * For copying and distribution information, please see the file
10 #include <mit-copyright.h>
12 #include <moira_site.h>
13 #include <moira_schema.h>
21 EXEC SQL INCLUDE sqlca;
22 extern void sqlglm(char *, unsigned int *, unsigned int *);
26 #define WHO 11859 /* root */
27 #define PROG "stu-tape"
29 #define MAX_ID_VALUE 31999
30 #define MIN_ID_VALUE 101
52 #define LOC_ADDRESS 80
53 #define LOC_DORM_ROOM 110
56 #define LOC_DPHONE 155
57 #define LOC_MPHONE 243
63 #define LEN_ADDRESS 30
64 #define LEN_DORM_ROOM 15
92 struct entry *get_next_entry(FILE *in);
93 void process_entry(struct entry *e);
94 void newuser(struct entry *e);
95 int set_next_users_id(int limit);
96 int set_next_uid(int limit);
98 void dbmserr(char *where, int what);
100 #define SQL_DUPLICATE -2112
101 #define sqlfail() (sqlca.sqlcode && sqlca.sqlcode != 1403)
103 int main(int argc, char **argv)
108 char buf[80], *file = NULL;
109 EXEC SQL BEGIN DECLARE SECTION;
111 EXEC SQL END DECLARE SECTION;
113 whoami = strrchr(argv[0], '/');
119 setvbuf(stdout, NULL, _IOLBF, BUFSIZ);
120 setvbuf(stderr, NULL, _IOLBF, BUFSIZ);
122 for (i = 1; i < argc; i++)
124 if (!strcmp(argv[i], "-w"))
126 else if (!strcmp(argv[i], "-n"))
129 fprintf(stderr, "Usage: %s [-w] [-D] [-n] inputfile\n", whoami);
134 in = fopen(file, "r");
137 fprintf(stderr, "Unable to open %s for input\n", file);
141 initialize_sms_error_table();
143 EXEC SQL CONNECT :db IDENTIFIED BY :db;
146 dbmserr("connecting", sqlca.sqlcode);
150 while ((e = get_next_entry(in)))
153 EXEC SQL COMMIT WORK;
156 dbmserr("committing work", sqlca.sqlcode);
163 fgets(buf, sizeof(buf), stdin);
171 struct entry *get_next_entry(FILE *in)
173 static struct entry e;
174 static char buf[BUFSIZ], eid[16], classbuf[10];
175 static char name[LEN_NAME + 1], id[LEN_ID + 1], course[LEN_COURSE + 1];
176 static char year[LEN_YEAR + 1], address[LEN_ADDRESS + 1];
177 static char dorm_room[LEN_DORM_ROOM + 1], city[LEN_CITY + 1];
178 static char state[LEN_STATE + 1], dphone[LEN_DPHONE + 1];
179 static char mphone[LEN_MPHONE + 1], sname[LEN_NAME + 1], title[128];
180 static int nyear = 0;
181 int ends_jr, ends_iii, ends_iv, ends_sr, ends_ii, ends_v;
189 gettimeofday(&tv, NULL);
190 tm = localtime(&tv.tv_sec);
196 if (!fgets(buf, sizeof(buf), in))
199 strncpy(name, &buf[LOC_NAME], LEN_NAME);
200 name[LEN_NAME] = '\0';
201 strncpy(id, &buf[LOC_ID], LEN_ID);
203 strncpy(course, &buf[LOC_COURSE], LEN_COURSE);
204 course[LEN_COURSE] = '\0';
205 strncpy(year, &buf[LOC_YEAR], LEN_YEAR);
206 year[LEN_YEAR] = '\0';
207 strncpy(address, &buf[LOC_ADDRESS], LEN_ADDRESS);
208 address[LEN_ADDRESS] = '\0';
209 strncpy(dorm_room, &buf[LOC_DORM_ROOM], LEN_DORM_ROOM);
210 dorm_room[LEN_DORM_ROOM] = '\0';
211 strncpy(city, &buf[LOC_CITY], LEN_CITY);
212 city[LEN_CITY] = '\0';
213 strncpy(state, &buf[LOC_STATE], LEN_STATE);
214 state[LEN_STATE] = '\0';
215 strncpy(dphone, &buf[LOC_DPHONE], LEN_DPHONE);
216 dphone[LEN_DPHONE] = '\0';
217 strncpy(mphone, &buf[LOC_MPHONE], LEN_MPHONE);
218 mphone[LEN_MPHONE] = '\0';
221 e.name = strtrim(sname);
222 p = strchr(name, ',');
225 e.last = strtrim(name);
232 if ((p = strchr(e.first, ' ')))
235 e.first = strtrim(e.first);
236 e.middle = strtrim(p + 1);
240 e.first = strtrim(e.first);
249 ends_jr = ends_iii = ends_iv = ends_sr = ends_ii = ends_v = 0;
252 LookForJrAndIII(e.last, &ends_sr, &ends_jr, &ends_iii, &ends_iv,
254 LookForJrAndIII(e.first, &ends_sr, &ends_jr, &ends_iii, &ends_iv,
263 EncryptID(e.eid, e.id, e.first, e.last);
265 e.year = strtrim(year);
267 if (e.year[0] == 'G')
270 sprintf(title, "Grad Student");
275 sprintf(classbuf, "%d", nyear + 4 - atoi(e.year) + 1900);
276 sprintf(title, "Undergrad (class of %s)", classbuf);
279 e.course = strtrim(course);
280 e.address = strtrim(address);
281 e.dorm = strtrim(dorm_room);
282 e.city = strtrim(city);
283 e.state = strtrim(state);
284 e.dphone = strtrim(dphone);
285 e.mphone = strtrim(mphone);
290 void process_entry(struct entry *e)
292 int changed, nochange, encrypted;
293 char buf[MAX_FIELD_WIDTH], *from, *to;
294 EXEC SQL BEGIN DECLARE SECTION;
295 char *first, *last, *middle, *eid, *sid, *name, *rdept;
296 char *rtitle, *rophone, *rhphone, *prog;
297 char class[USERS_TYPE_SIZE], haddr[USERS_HOME_ADDR_SIZE];
298 char hphone[USERS_HOME_PHONE_SIZE], ophone[USERS_OFFICE_PHONE_SIZE];
299 char dept[USERS_DEPARTMENT_SIZE], raddr[USERS_XADDRESS_SIZE];
300 char dfirst[USERS_FIRST_SIZE], dlast[USERS_LAST_SIZE];
301 char dmiddle[USERS_MIDDLE_SIZE];
303 EXEC SQL END DECLARE SECTION;
308 if (strlen(first) > USERS_FIRST_SIZE - 1)
309 first[USERS_FIRST_SIZE - 1] = '\0';
311 if (strlen(last) > USERS_LAST_SIZE - 1)
312 last[USERS_LAST_SIZE - 1] = '\0';
314 if (strlen(middle) > USERS_MIDDLE_SIZE - 1)
315 middle[USERS_MIDDLE_SIZE - 1] = '\0';
322 EXEC SQL SELECT users_id, first, last, middle, type, home_addr, home_phone,
323 office_phone, status, department
324 INTO :id, :dfirst, :dlast, :dmiddle, :class, :haddr, :hphone,
325 :ophone, :status, :dept
327 WHERE clearid = :sid;
330 if (sqlca.sqlcode == SQL_DUPLICATE)
332 com_err(whoami, 0, "duplicate ID number %s on user %s %s",
341 EXEC SQL SELECT users_id, first, last, middle, type, home_addr,
342 home_phone, office_phone, status, department
343 INTO :id, :dfirst, :dlast, :dmiddle, :class, :haddr,
344 :hphone, :ophone, :status, :dept
346 WHERE last = :last and first = :first and clearid = :eid;
357 /* See if class changed: if it's different, and the value in the database
358 * is not STAFF or SIPBMEM, and the value from the tape is actually
359 * meaningful, then update the database. Since they were on the
360 * students tape, make the account usable.
362 if (strcmp(e->class, strtrim(class)) &&
363 strcmp(class, "STAFF") && strcmp(class, "SIPBMEM") &&
366 com_err(whoami, 0, "updating class for user %s %s from %s to %s",
367 first, last, class, e->class);
368 if (status == US_NOT_ALLOWED)
369 status = US_NO_LOGIN_YET;
370 if (status == US_ENROLL_NOT_ALLOWED)
371 status = US_ENROLLED;
372 strcpy(class, e->class);
373 EXEC SQL UPDATE users
374 SET type = NVL(:class, CHR(0)), status = :status, modtime = SYSDATE,
375 modby = :who, modwith = :prog
376 WHERE users_id = :id;
379 dbmserr("updating class", sqlca.sqlcode);
384 /* Update name if necessary */
385 if (strcmp(first, strtrim(dfirst)) ||
386 strcmp(last, strtrim(dlast)) ||
387 strcmp(middle, strtrim(dmiddle)))
389 com_err(whoami, 0, "updating real name for %s %s", first, last);
390 EXEC SQL UPDATE users
391 SET first = NVL(:first, CHR(0)), last = NVL(:last, CHR(0)),
392 middle = NVL(:middle, CHR(0)), modby = :who, modwith = :prog,
394 WHERE users_id = :id;
395 if (sqlca.sqlcode != 0)
397 dbmserr("updating name", sqlca.sqlcode);
402 /* Deal with updating the finger info if necessary */
403 changed = nochange = 0;
406 strcpy(buf, e->address);
410 strcat(buf, e->dorm);
415 strcat(buf, e->city);
421 strcat(buf, e->state);
423 while ((to = strchr(buf, ',')))
425 while ((to = strchr(buf, ':')))
431 strncpy(haddr, buf, USERS_HOME_ADDR_SIZE - 1);
432 haddr[USERS_HOME_ADDR_SIZE - 1] = '\0';
435 else if (strncmp(strtrim(haddr), buf, USERS_HOME_ADDR_SIZE - 1))
440 if (strncmp(strtrim(haddr), buf, USERS_HOME_ADDR_SIZE - 1))
442 strncpy(haddr, buf, USERS_HOME_ADDR_SIZE - 1);
443 haddr[USERS_HOME_ADDR_SIZE - 1] = '\0';
456 if (hphone[0] == ' ')
458 strncpy(hphone, buf, USERS_HOME_PHONE_SIZE - 1);
459 hphone[USERS_HOME_PHONE_SIZE - 1] = '\0';
461 else if (strncmp(strtrim(hphone), buf, USERS_HOME_PHONE_SIZE - 1))
466 if (strncmp(strtrim(hphone), buf, USERS_HOME_PHONE_SIZE - 1))
468 strncpy(hphone, buf, USERS_HOME_PHONE_SIZE - 1);
469 hphone[USERS_HOME_PHONE_SIZE - 1] = '\0';
482 if (ophone[0] == ' ')
484 strncpy(ophone, buf, USERS_OFFICE_PHONE_SIZE - 1);
485 ophone[USERS_OFFICE_PHONE_SIZE - 1] = '\0';
487 else if (strncmp(strtrim(ophone), buf, USERS_OFFICE_PHONE_SIZE - 1))
492 if (strncmp(strtrim(ophone), buf, USERS_OFFICE_PHONE_SIZE - 1))
494 strncpy(ophone, buf, USERS_OFFICE_PHONE_SIZE - 1);
495 ophone[USERS_OFFICE_PHONE_SIZE - 1] = '\0';
497 e->course = e->course;
502 strncpy(dept, e->course, USERS_DEPARTMENT_SIZE - 1);
503 dept[USERS_DEPARTMENT_SIZE - 1] = '\0';
505 else if (strncmp(strtrim(dept), e->course, USERS_DEPARTMENT_SIZE - 1))
510 if (strncmp(strtrim(dept), e->course, USERS_DEPARTMENT_SIZE - 1))
512 strncpy(dept, e->course, USERS_DEPARTMENT_SIZE - 1);
513 dept[USERS_DEPARTMENT_SIZE - 1] = '\0';
521 strcpy(raddr, e->address);
525 strcat(raddr, e->dorm);
530 strcat(raddr, e->city);
535 if (isupper(e->state[0]) && isupper(e->state[1]) &&
536 isdigit(e->state[2]) && isdigit(e->state[3]) &&
537 isdigit(e->state[4]) && isdigit(e->state[5]) &&
538 isdigit(e->state[6]))
540 sprintf(buf, "%c%c %s", e->state[0], e->state[1],
545 strcat(raddr, e->state);
551 strcat(raddr, "MIT INTERDEPARTMENTAL MAIL");
555 com_err(whoami, 0, "updating finger for %s %s", first, last);
556 EXEC SQL UPDATE users
557 SET home_addr = NVL(:haddr, CHR(0)), home_phone = NVL(:hphone, CHR(0)),
558 office_phone = NVL(:ophone, CHR(0)), department = NVL(:dept, CHR(0)),
559 fmodtime = SYSDATE, fmodby = :who, fmodwith = :prog,
560 xname = NVL(:name, CHR(0)), xdept = NVL(:rdept, CHR(0)),
561 xtitle = NVL(:rtitle, CHR(0)), xaddress = NVL(:raddr, CHR(0)),
562 xphone1 = NVL(:rhphone, CHR(0)), xphone2 = NVL(:rophone, CHR(0)),
563 xmodtime = SYSDATE, clearid = NVL(:sid, CHR(0))
564 WHERE users_id = :id;
567 dbmserr(NULL, sqlca.sqlcode);
573 EXEC SQL UPDATE users
574 SET xname = NVL(:name, CHR(0)), xdept = NVL(:rdept, CHR(0)),
575 xtitle = NVL(:rtitle, CHR(0)), xaddress = NVL(:raddr, CHR(0)),
576 xphone1 = NVL(:rhphone, CHR(0)), xphone2 = NVL(:rophone, CHR(0)),
577 xmodtime = SYSDATE, clearid = NVL(:sid, CHR(0))
578 WHERE users_id = :id;
581 dbmserr(NULL, sqlca.sqlcode);
587 void newuser(struct entry *e)
589 char buf[512], *from, *to;
590 EXEC SQL BEGIN DECLARE SECTION;
592 char *last, *first, *class, *middle, login[USERS_LOGIN_SIZE], *sid;
593 char fullname[USERS_FULLNAME_SIZE], *prog;
594 char haddr[USERS_HOME_ADDR_SIZE], hphone[USERS_HOME_PHONE_SIZE];
595 char ophone[USERS_OFFICE_PHONE_SIZE], dept[USERS_DEPARTMENT_SIZE];
596 char *title, raddr[USERS_XADDRESS_SIZE], *name;
597 EXEC SQL END DECLARE SECTION;
601 strcpy(buf, e->address);
605 strcat(buf, e->dorm);
610 strcat(buf, e->city);
615 strcat(buf, e->state);
617 strncpy(haddr, buf, USERS_HOME_ADDR_SIZE - 1);
618 haddr[USERS_HOME_ADDR_SIZE - 1] = '\0';
628 strncpy(hphone, buf, USERS_HOME_PHONE_SIZE - 1);
629 hphone[USERS_HOME_PHONE_SIZE - 1] = '\0';
639 strncpy(ophone, buf, USERS_OFFICE_PHONE_SIZE - 1);
640 ophone[USERS_OFFICE_PHONE_SIZE - 1] = '\0';
641 strncpy(dept, e->course, USERS_DEPARTMENT_SIZE - 1);
642 dept[USERS_DEPARTMENT_SIZE - 1] = '\0';
644 id = set_next_users_id(0);
645 uid = set_next_uid(1);
646 sprintf(login, "#%d", uid);
654 sprintf(fullname, "%s %s %s", first, middle, last);
656 sprintf(fullname, "%s %s", first, last);
658 strcpy(raddr, e->address);
662 strcat(raddr, e->dorm);
667 strcat(raddr, e->city);
672 if (isupper(e->state[0]) && isupper(e->state[1]) &&
673 isdigit(e->state[2]) && isdigit(e->state[3]) &&
674 isdigit(e->state[4]) && isdigit(e->state[5]) &&
675 isdigit(e->state[6]))
677 sprintf(buf, "%c%c %s", e->state[0], e->state[1],
682 strcat(raddr, e->state);
688 strcat(raddr, "MIT INTERDEPARTMENTAL MAIL");
690 EXEC SQL INSERT INTO users
691 (login, users_id, unix_uid, shell, last, first, middle, status,
692 clearid, type, modtime, modby, modwith, fullname, home_addr,
693 home_phone, office_phone, department, fmodtime, fmodby, fmodwith,
694 potype, xname, xdept, xtitle, xaddress, xphone1, xphone2, xmodtime)
695 VALUES (:login, :id, :uid, '/bin/athena/tcsh', NVL(:last, CHR(0)),
696 NVL(:first, CHR(0)), NVL(:middle, CHR(0)), 0, NVL(:sid, CHR(0)),
697 NVL(:class, CHR(0)), SYSDATE, :who, :prog, NVL(:fullname, CHR(0)),
698 NVL(:haddr, CHR(0)), NVL(:hphone, CHR(0)), NVL(:ophone, CHR(0)),
699 NVL(:dept, CHR(0)), SYSDATE, :who, :prog, 'NONE',
700 NVL(:name, CHR(0)), NVL(:dept, CHR(0)), NVL(:title, CHR(0)),
701 NVL(:raddr, CHR(0)), NVL(:hphone, CHR(0)), NVL(:ophone, CHR(0)),
705 dbmserr("adding user", sqlca.sqlcode);
709 com_err(whoami, 0, "adding user %s %s", e->first, e->last);
713 int set_next_users_id(int limit)
715 EXEC SQL BEGIN DECLARE SECTION;
716 int flag, value, retval;
717 EXEC SQL END DECLARE SECTION;
719 EXEC SQL SELECT value INTO :value FROM numvalues
720 WHERE name = 'users_id';
723 if (sqlca.sqlerrd[2] != 1)
726 com_err(whoami, MR_INTERNAL, "values table inconsistancy");
731 EXEC SQL SELECT users_id INTO :flag FROM users
732 WHERE users_id = :value;
735 if (sqlca.sqlerrd[2] == 0)
740 if (limit && value > MAX_ID_VALUE)
741 value = MIN_ID_VALUE;
743 EXEC SQL SELECT users_id INTO :flag FROM users
744 WHERE users_id = :value;
747 if (sqlca.sqlerrd[2] == 0)
752 if (limit && value > MAX_ID_VALUE)
753 value = MIN_ID_VALUE;
754 EXEC SQL UPDATE numvalues SET value = :value
755 WHERE name = 'users_id';
758 dbmserr("updating numvalues", sqlca.sqlcode);
764 int set_next_uid(int limit)
766 EXEC SQL BEGIN DECLARE SECTION;
767 int flag, value, retval;
768 EXEC SQL END DECLARE SECTION;
770 EXEC SQL SELECT value INTO :value FROM numvalues
771 WHERE name = 'unix_uid';
774 if (sqlca.sqlerrd[2] != 1)
777 com_err(whoami, MR_INTERNAL, "values table inconsistancy");
782 EXEC SQL SELECT unix_uid INTO :flag FROM users WHERE unix_uid = :value;
785 if (sqlca.sqlerrd[2] == 0)
790 if (limit && value > MAX_ID_VALUE)
791 value = MIN_ID_VALUE;
793 EXEC SQL SELECT unix_uid INTO :flag FROM users WHERE unix_uid = :value;
796 if (sqlca.sqlerrd[2] == 0)
801 if (limit && value > MAX_ID_VALUE)
802 value = MIN_ID_VALUE;
803 EXEC SQL UPDATE numvalues SET value = :value WHERE name = 'unix_uid';
806 dbmserr("updating numvalues", sqlca.sqlcode);
815 dbmserr(NULL, sqlca.sqlcode);
816 EXEC SQL ROLLBACK WORK;
820 void dbmserr(char *where, int what)
823 int bufsize = 256, msglength = 0;
825 sqlglm(err_msg, &bufsize, &msglength);
826 err_msg[msglength] = '\0';
829 com_err(whoami, 0, "DBMS error %swhile %s", err_msg, where);
831 com_err(whoami, 0, "DBMS error %s", err_msg);