Changeset 20933

Timestamp:

Dec 7, 2008, 2:36:19 PM (17 years ago)

Author:

eugene

Message:

adding auto_incr fields for table ids; adding upgrade mode

File:

• trunk/ippTools/scripts/ippadmin (modified) (5 diffs)

trunk/ippTools/scripts/ippadmin

@@ -1 +1 @@
 #!/bin/csh -f
-## this script creates the tables needed by ippMonitor for user administration
+## this script creates the top-level tables needed to manage the IPP databases
+set IPPADMIN = "ippadmin"
 
-if ($#argv < 4) goto usage;
+if ($#argv < 3) goto usage;
 
 if ("$1" == "init") then
@@ -11 +12 @@
 
   mysql -h $dbserver -u root -p <<EOF
-  create database ippadmin;
-  use ippadmin;
-  create table users (username char(20) not null, password varchar(255));
-  create table cookies (username char(20) not null, cookie varchar(255));
-  create table projects (projname char(20) not null);
+  create database $IPPADMIN;
+  use $IPPADMIN;
+  create table users (user_id BIGINT AUTO_INCREMENT, username char(255) not null, password varchar(255), primary key (user_id));
+  create table cookies (cookie_id BIGINT AUTO_INCREMENT, username char(255) not null, cookie varchar(255), primary key (cookie_id));
+  create table projects (proj_id BIGINT AUTO_INCREMENT, projname char(255) not null, primary key (proj_id));
   grant all on *.* to $dbuser@localhost identified by '$dbpass';
 EOF
@@ -41 +42 @@
   set pass = $5
 
-  mysql -h $dbserver -u $dbuser -p ippadmin <<EOF
+  mysql -h $dbserver -u $dbuser -p $IPPADMIN <<EOF
   insert into users (username, password) values ('$user', '$pass');
 EOF
@@ -53 +54 @@
   set dbname = $4
 
-  mysql -h $dbserver -u $dbuser -p ippadmin <<EOF
+  mysql -h $dbserver -u $dbuser -p $IPPADMIN <<EOF
    insert into projects (projname) values ('$dbname');
    create database $dbname;
 EOF
     exit 0;
+endif
+
+if ("$1" == "upgrade") then
+  echo "$#argv"
+  
+  if ($#argv != 3) goto usage;
+  set dbserver = $2
+  set stage = $3
+
+  if ("$stage" == "1") then
+    mysql -h $dbserver -u root -p $IPPADMIN <<EOF
+    ALTER TABLE users add column user_id BIGINT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (user_id);
+    ALTER TABLE users change column username username varchar(255);
+
+    ALTER TABLE cookies add column cookie_id BIGINT AUTO_INCREMENT FIRST, ADD PRIMARY KEY (cookie_id);
+    ALTER TABLE cookies change column username username varchar(255);
+
+    ALTER TABLE projects add column proj_id BIGINT AUTO_INCREMENT FIRST, ADD PRIMARY KEY (proj_id);
+    ALTER TABLE projects change column projname projname varchar(255);
+EOF
+      exit 0;
+  endif
+
+  echo "unknown upgrade stage $stage"
+  exit 1;
 endif
 
@@ -65 +91 @@
   echo "  ippadmin init (dbserver) (dbuser) (dbpassword)"
   echo "      creates admin tables, basic db user -- requires root password"
+  echo ""
+  echo "  ippadmin upgrade (dbserver) (stage)"
+  echo "      upgrade existing database for given stage [1] -- requires root password"
   echo ""
   echo "  ippadmin client (dbserver) (dbuser) (dbpassword) (client)"