setup.sql

该源码为NOKIA公司提供的一个B2B的J2ME项目

### Create the database

CREATE DATABASE taskdb;
USE taskdb;

### Create the 'taskman' user into the database
### This is required to access and modify the database from code
GRANT SELECT, INSERT, UPDATE, DELETE ON taskdb.* TO taskman@'%'
IDENTIFIED BY 'namksat';


### Create tables

# Create user table
CREATE TABLE user
  ( id int unsigned not null auto_increment,
    login varchar(40) not null unique,
    password varchar(20) not null,
    mobile varchar(20),
    tasks_up_to_date enum('yes','no'),
    primary key(id)
  ) ENGINE=INNODB;


# Create task table
CREATE TABLE task
  ( id int unsigned not null auto_increment,
    owner int unsigned not null,
    description varchar(100),
    state enum('NOT_DONE','DONE') not null,
    primary key(id),
    foreign key(owner) references user(id) on delete cascade
  ) ENGINE=INNODB;


# Create roles table
CREATE TABLE roles
  ( id int unsigned not null auto_increment,
    name varchar(50) not null unique,
    primary key(id)
  ) ENGINE=INNODB;


# Create userrolemap table
CREATE TABLE userrolemap
  ( userid int unsigned not null, 
    roleid int unsigned not null,
    foreign key(userid) references user(id) on delete cascade,
    foreign key(roleid) references roles(id)
  ) ENGINE=INNODB; 


# Create the roles
INSERT INTO roles (name) VALUES ('administrator');
INSERT INTO roles (name) VALUES ('manager');
INSERT INTO roles (name) VALUES ('worker');


# Add "administrator" user and set a role for it
INSERT INTO user (login,password,mobile) VALUES ('administrator',OLD_PASSWORD('admin'),'-');
insert into userrolemap (userid, roleid) values
(
  ( select id from user where login='administrator' ),
  ( select id from roles where name='administrator' )
);

COMMIT;