diff --git a/inittbl.sh b/inittbl.sh deleted file mode 100755 index 8f72bed..0000000 --- a/inittbl.sh +++ /dev/null @@ -1,36 +0,0 @@ -#!/bin/sh - -DB=${1:-users.sq3} -CSV=${2:-teams.csv} -sqlite3 ${DB} <<-EOF - .mode csv - PRAGMA foreign_keys=on; - CREATE TABLE IF NOT EXISTS teams(team primary key); - CREATE TEMPORARY TABLE guest(team); - INSERT INTO guest VALUES('guest'); - INSERT INTO teams SELECT team FROM GUEST - WHERE team NOT IN (SELECT team FROM teams); - - CREATE TABLE IF NOT EXISTS users( - user primary key, gecos, email, team, - FOREIGN KEY(team) REFERENCES teams(team) - ON DELETE CASCADE ON UPDATE CASCADE); - CREATE TEMPORARY TABLE teamcsv(teamno,teamname,uid,name); - .import $CSV teamcsv - DELETE FROM teamcsv WHERE teamno NOT GLOB '[0-9]*'; - INSERT INTO teams SELECT distinct teamname FROM teamcsv - WHERE teamname NOT IN (SELECT team FROM teams); - INSERT INTO users SELECT uid, name, uid||'@itl.koeki-u.ac.jp', teamname - FROM teamcsv WHERE uid NOT IN (SELECT user FROM users); - - CREATE TABLE IF NOT EXISTS skey( - user, skey, expire, - UNIQUE(user, skey), - FOREIGN KEY(user) REFERENCES users(user) - ON DELETE CASCADE ON UPDATE CASCADE); - CREATE TABLE IF NOT EXISTS tmpkey( - user, tmpkey, token, expire, - UNIQUE(user, tmpkey), - FOREIGN KEY(user) REFERENCES users(user) - ON DELETE CASCADE ON UPDATE CASCADE); -EOF diff --git a/jtserv/adduser.sh b/jtserv/adduser.sh new file mode 100755 index 0000000..e895938 --- /dev/null +++ b/jtserv/adduser.sh @@ -0,0 +1,14 @@ +#!/bin/sh + +DB=${1:-users.sq3} +CSV=${2:-users.csv} +sqlite3 ${DB} <<-EOF + .mode csv + PRAGMA foreign_keys=on; + CREATE TEMPORARY TABLE newuser(user, gecos, email, team); + .import $CSV newuser + INSERT INTO teams SELECT distinct team FROM newuser + WHERE team NOT IN (SELECT team FROM teams); + INSERT INTO users SELECT user, gecos, email, team + FROM newuser WHERE user NOT IN (SELECT user FROM users); +EOF diff --git a/jtserv/inittbl.sh b/jtserv/inittbl.sh new file mode 100755 index 0000000..8f72bed --- /dev/null +++ b/jtserv/inittbl.sh @@ -0,0 +1,36 @@ +#!/bin/sh + +DB=${1:-users.sq3} +CSV=${2:-teams.csv} +sqlite3 ${DB} <<-EOF + .mode csv + PRAGMA foreign_keys=on; + CREATE TABLE IF NOT EXISTS teams(team primary key); + CREATE TEMPORARY TABLE guest(team); + INSERT INTO guest VALUES('guest'); + INSERT INTO teams SELECT team FROM GUEST + WHERE team NOT IN (SELECT team FROM teams); + + CREATE TABLE IF NOT EXISTS users( + user primary key, gecos, email, team, + FOREIGN KEY(team) REFERENCES teams(team) + ON DELETE CASCADE ON UPDATE CASCADE); + CREATE TEMPORARY TABLE teamcsv(teamno,teamname,uid,name); + .import $CSV teamcsv + DELETE FROM teamcsv WHERE teamno NOT GLOB '[0-9]*'; + INSERT INTO teams SELECT distinct teamname FROM teamcsv + WHERE teamname NOT IN (SELECT team FROM teams); + INSERT INTO users SELECT uid, name, uid||'@itl.koeki-u.ac.jp', teamname + FROM teamcsv WHERE uid NOT IN (SELECT user FROM users); + + CREATE TABLE IF NOT EXISTS skey( + user, skey, expire, + UNIQUE(user, skey), + FOREIGN KEY(user) REFERENCES users(user) + ON DELETE CASCADE ON UPDATE CASCADE); + CREATE TABLE IF NOT EXISTS tmpkey( + user, tmpkey, token, expire, + UNIQUE(user, tmpkey), + FOREIGN KEY(user) REFERENCES users(user) + ON DELETE CASCADE ON UPDATE CASCADE); +EOF