##################
# create database
##################

DROP DATABASE IF EXISTS DBA;
CREATE DATABASE IF NOT EXISTS DBA;
USE DBA;

#######
# create tables
#######

CREATE TABLE A (
  ID int NOT NULL,
  BUSINESS_DATE date,
  ACCOUNT varchar(8),
  AMOUNT int,
  TRAD_TRADE_ID int,
  BOOK varchar(8),
  PRIMARY KEY (ID)
);

INSERT INTO A(ID, BUSINESS_DATE, ACCOUNT, AMOUNT, TRAD_TRADE_ID, BOOK) VALUES(1,'2012-07-05','account1', 100, 11, 'book1');
INSERT INTO A(ID, BUSINESS_DATE, ACCOUNT, AMOUNT, TRAD_TRADE_ID, BOOK) VALUES(2,'2012-07-05','account2', 100, 12, 'book2');
INSERT INTO A(ID, BUSINESS_DATE, ACCOUNT, AMOUNT, TRAD_TRADE_ID, BOOK) VALUES(3,'2012-07-05','account3', 100, 13, 'book3');

CREATE INDEX I1 ON A(ACCOUNT);
CREATE INDEX I2 ON A(AMOUNT);
CREATE INDEX I3 ON A(TRAD_TRADE_ID);
CREATE INDEX I4 ON A(BOOK);

COMMIT;

##################
# create database
##################

DROP DATABASE IF EXISTS DBB;
CREATE DATABASE IF NOT EXISTS DBB;
USE DBB;

CREATE TABLE B (
  ID int NOT NULL,
  ACCOUNT varchar(8),
  BOOK varchar(8),
  DESCRIPTION varchar(8),
  PRIMARY KEY (ID)
);

INSERT INTO B(ID, ACCOUNT, BOOK, DESCRIPTION) VALUES(1,'account1', 'book1', 'desccccc');
INSERT INTO B(ID, ACCOUNT, BOOK, DESCRIPTION) VALUES(2,'account2', 'book2', 'desccccc');
INSERT INTO B(ID, ACCOUNT, BOOK, DESCRIPTION) VALUES(3,'account3', 'book3', 'desccccc');
INSERT INTO B(ID, ACCOUNT, BOOK, DESCRIPTION) VALUES(4,'account4', 'book4', 'desccccc');
INSERT INTO B(ID, ACCOUNT, BOOK, DESCRIPTION) VALUES(5,'account5', 'book5', 'desccccc');
INSERT INTO B(ID, ACCOUNT, BOOK, DESCRIPTION) VALUES(6,'account6', 'book6', 'desccccc');

COMMIT;

GRANT ALL PRIVILEGES ON DBA.* TO 'root'@'localhost';
GRANT ALL PRIVILEGES ON DBA.* TO 'root'@'10.0.0.1';
GRANT ALL PRIVILEGES ON DBA.* TO 'root'@'10.0.0.4';
GRANT ALL PRIVILEGES ON DBA.* TO 'root'@'host04';

GRANT ALL PRIVILEGES ON DBB.* TO 'root'@'localhost';
GRANT ALL PRIVILEGES ON DBB.* TO 'root'@'10.0.0.1';
GRANT ALL PRIVILEGES ON DBB.* TO 'root'@'10.0.0.4';
GRANT ALL PRIVILEGES ON DBB.* TO 'root'@'host04';

