#!/bin/sh
P=
DB=authen

function drop() {
	echo "drop table $1"
	echo "drop table $1" | mysql $P $DB
}

drop users
drop groups
drop roles
drop group_members
drop role_members

echo "create users"
mysql $P $DB << EOF

CREATE TABLE users(
	idx	INT(11)	DEFAULT '0' NOT NULL AUTO_INCREMENT,
	ts      TIMESTAMP(14)       NOT NULL,
	user	VARCHAR(100)	    NOT NULL,
	passwd	VARCHAR(100)           NOT NULL,
	PRIMARY KEY (idx),
    	UNIQUE (user)
);

EOF



echo "create groups"
mysql $P $DB << EOF

CREATE TABLE groups(
        idx     INT(11) DEFAULT '0' NOT NULL AUTO_INCREMENT,
        ts      TIMESTAMP(14)       NOT NULL,
        groupname   VARCHAR(200)        NOT NULL,
        PRIMARY KEY (idx),
        UNIQUE (groupname)
);

EOF




echo "create roles"
mysql $P $DB << EOF

CREATE TABLE roles(
        idx     INT(11) DEFAULT '0' NOT NULL AUTO_INCREMENT,
        ts      TIMESTAMP(14)       NOT NULL,
        rolename    VARCHAR(200)           NOT NULL,
        PRIMARY KEY (idx),
        UNIQUE (rolename)
);

EOF


echo "create group_members"
mysql $P $DB << EOF

CREATE TABLE group_members(
        idx     INT(16) DEFAULT '0' NOT NULL AUTO_INCREMENT,
        ts      TIMESTAMP(14)       NOT NULL,
	idx_user INT(11)	    NOT NULL,
	idx_group INT(11)           NOT NULL,
	PRIMARY KEY (idx),
        UNIQUE (idx_user, idx_group)
);

EOF



echo "create role_members"
mysql $P $DB << EOF

CREATE TABLE role_members(
        idx     INT(16) DEFAULT '0' NOT NULL AUTO_INCREMENT,
        ts      TIMESTAMP(14)       NOT NULL,
        idx_group INT(11)           NOT NULL,
	idx_role  INT(11)           NOT NULL,
        PRIMARY KEY (idx),
        UNIQUE (idx_group, idx_role)
);

EOF
