MySQL DDL

[analogueboy]

Has anybody create the MySQL DDL? If I create it, would anybody else be interested in a copy?

[analogueboy]

I've created the DDL and DML if anyone wants a copy. Not too many changes, tested using the quick start app and everything is running fine.

[Ben Alex]

Feel free to post them to this thread. That way people can search and find them. It's something I don't think belongs in CVS, as it would mean I need to keep them current. Besides, I use Postgres :wink:

[aaron8tang]

MySQL script embedded in contact example snapshot code check out from cvs, sorry for the little bit messy post :wink:



template.execute(
"DROP TABLE IF EXISTS USERS;");
template.execute(
"DROP TABLE IF EXISTS AUTHORITIES;");
template.execute(
"DROP TABLE IF EXISTS ACL_OBJECT_IDENTITY;");
template.execute(
"DROP TABLE IF EXISTS ACL_PERMISSION;");
template.execute(
"DROP TABLE IF EXISTS CONTACTS;");
template.execute(
"CREATE TABLE CONTACTS(ID INT NOT NULL PRIMARY KEY, CONTACT_NAME VARCHAR(50) NOT NULL, EMAIL VARCHAR(50) NOT NULL)");
template.execute(
"INSERT INTO contacts VALUES (1, 'John Smith', 'john@somewhere.com');"); // marissa
template.execute(
"INSERT INTO contacts VALUES (2, 'Michael Citizen', 'michael@xyz.com');"); // marissa
template.execute(
"INSERT INTO contacts VALUES (3, 'Joe Bloggs', 'joe@demo.com');"); // marissa
template.execute(
"INSERT INTO contacts VALUES (4, 'Karen Sutherland', 'karen@sutherland.com');"); // marissa + dianne + scott
template.execute(
"INSERT INTO contacts VALUES (5, 'Mitchell Howard', 'mitchell@abcdef.com');"); // dianne
template.execute(
"INSERT INTO contacts VALUES (6, 'Rose Costas', 'rose@xyz.com');"); // dianne + scott
template.execute(
"INSERT INTO contacts VALUES (7, 'Amanda Smith', 'amanda@abcdef.com');"); // scott
template.execute(
"INSERT INTO contacts VALUES (8, 'Cindy Smith', 'cindy@smith.com');"); // dianne + scott
template.execute(
"INSERT INTO contacts VALUES (9, 'Jonathan Citizen', 'jonathan@xyz.com');"); // scott
template.execute(
"CREATE TABLE ACL_OBJECT_IDENTITY(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, OBJECT_IDENTITY VARCHAR(250) NOT NULL UNIQUE,PARENT_OBJECT INT,ACL_CLASS VARCHAR(250) NOT NULL,FOREIGN KEY(PARENT_OBJECT) REFERENCES ACL_OBJECT_IDENTITY(ID));");
template.execute(
"ALTER TABLE ACL_OBJECT_IDENTITY AUTO_INCREMENT = 100;");
template.execute(
"INSERT INTO acl_object_identity VALUES (1, 'sample.contact.Contact:1', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
template.execute(
"INSERT INTO acl_object_identity VALUES (2, 'sample.contact.Contact:2', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
template.execute(
"INSERT INTO acl_object_identity VALUES (3, 'sample.contact.Contact:3', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
template.execute(
"INSERT INTO acl_object_identity VALUES (4, 'sample.contact.Contact:4', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
template.execute(
"INSERT INTO acl_object_identity VALUES (5, 'sample.contact.Contact:5', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
template.execute(
"INSERT INTO acl_object_identity VALUES (6, 'sample.contact.Contact:6', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
template.execute(
"INSERT INTO acl_object_identity VALUES (7, 'sample.contact.Contact:7', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
template.execute(
"INSERT INTO acl_object_identity VALUES (8, 'sample.contact.Contact:8', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
template.execute(
"INSERT INTO acl_object_identity VALUES (9, 'sample.contact.Contact:9', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');");
template.execute(
"CREATE TABLE ACL_PERMISSION(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,ACL_OBJECT_IDENTITY INT NOT NULL,RECIPIENT VARCHAR(100) NOT NULL,MASK INT NOT NULL,FOREIGN KEY(ACL_OBJECT_IDENTITY) REFERENCES ACL_OBJECT_IDENTITY(ID));");
template.execute(
"ALTER TABLE ACL_PERMISSION AUTO_INCREMENT = 100;");
template.execute(
"INSERT INTO acl_permission VALUES (null, 1, 'marissa', 1);"); // administer
template.execute(
"INSERT INTO acl_permission VALUES (null, 2, 'marissa', 2);"); // read
template.execute(
"INSERT INTO acl_permission VALUES (null, 3, 'marissa', 22);"); // read+write+delete
template.execute(
"INSERT INTO acl_permission VALUES (null, 4, 'marissa', 1);"); // administer
template.execute(
"INSERT INTO acl_permission VALUES (null, 4, 'dianne', 1);"); // administer
template.execute(
"INSERT INTO acl_permission VALUES (null, 4, 'scott', 2);"); // read
template.execute(
"INSERT INTO acl_permission VALUES (null, 5, 'dianne', 2);"); // read
template.execute(
"INSERT INTO acl_permission VALUES (null, 6, 'dianne', 22);"); // read+write+delete
template.execute(
"INSERT INTO acl_permission VALUES (null, 6, 'scott', 2);"); // read
template.execute(
"INSERT INTO acl_permission VALUES (null, 7, 'scott', 1);"); // administer
template.execute(
"INSERT INTO acl_permission VALUES (null, 8, 'dianne', 2);"); // read
template.execute(
"INSERT INTO acl_permission VALUES (null, 8, 'scott', 2);"); // read
template.execute(
"INSERT INTO acl_permission VALUES (null, 9, 'scott', 22);"); // read+write+delete
template.execute(
"CREATE TABLE USERS(USERNAME VARCHAR(50) NOT NULL PRIMARY KEY,PASSWORD VARCHAR(50) NOT NULL,ENABLED BOOL NOT NULL);");
template.execute(
"CREATE TABLE AUTHORITIES(USERNAME VARCHAR(50) NOT NULL,AUTHORITY VARCHAR(50) NOT NULL,FOREIGN KEY (USERNAME) REFERENCES USERS(USERNAME));");
template.execute(
"CREATE UNIQUE INDEX IX_AUTH_USERNAME ON AUTHORITIES(USERNAME,AUTHORITY);");

/*
Passwords encoded using MD5, NOT in Base64 format, with null as salt
Encoded password for marissa is "koala"
Encoded password for dianne is "emu"
Encoded password for scott is "wombat"
Encoded password for peter is "opal" (but user is disabled)

*/
template.execute(
"INSERT INTO USERS VALUES('marissa','a564de63c2d0da68cf47586ee05984d7 ',1);");
template.execute(
"INSERT INTO USERS VALUES('dianne','65d15fe9156f9c4bbffd98085992a44e' ,1);");
template.execute(
"INSERT INTO USERS VALUES('scott','2b58af6dddbd072ed27ffc86725d7d3a', 1);");
template.execute(
"INSERT INTO USERS VALUES('peter','22b5c9accc6e1ba628cedc63a72d57f8', 0);");
template.execute(
"INSERT INTO AUTHORITIES VALUES('marissa','ROLE_USER');");
template.execute(
"INSERT INTO AUTHORITIES VALUES('marissa','ROLE_SUPERVISOR');");
template.execute(
"INSERT INTO AUTHORITIES VALUES('dianne','ROLE_USER');");
template.execute("INSERT INTO AUTHORITIES VALUES('scott','ROLE_USER');");
template.execute("INSERT INTO AUTHORITIES VALUES('peter','ROLE_USER');");

[jdoklovic]

here's a pretty version you can just copy and save to a file.....


DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS authorities;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS acl_permission;
DROP TABLE IF EXISTS acl_object_identity;

CREATE TABLE users (
username varchar(50) NOT NULL,
password varchar(50) NOT NULL,
enabled bool NOT NULL,

constraint PK_Username primary key (username)
) TYPE=InnoDB;


CREATE TABLE authorities (
username varchar(50) NOT NULL,
authority varchar(50) NOT NULL,

constraint FK_Username foreign key (username) references users(username),
constraint IX_AuthUsername unique key (username, authority)
) TYPE=InnoDB;


CREATE TABLE acl_object_identity (
id int NOT NULL auto_increment,
object_identity varchar(250) NOT NULL,
parent_object int,
acl_class varchar(250) NOT NULL,

index (parent_object),

constraint PK_Id primary key (id),
constraint FK_ParentObject foreign key (parent_object) references acl_object_identity(id)

) TYPE=InnoDB;


CREATE TABLE acl_permission (
id int NOT NULL auto_increment,
acl_object_identity int NOT NULL,
recipient varchar(100) NOT NULL,
mask int NOT NULL,

constraint PK_Id primary key (id),
constraint FK_ACLIdentity foreign key (acl_object_identity) references acl_object_identity(id),
constraint IX_URecipient unique key (acl_object_identity, recipient)

) TYPE=InnoDB;

CREATE TABLE contacts (
id int NOT NULL auto_increment,
contact_name varchar(50) NOT NULL,
email varchar(50) NOT NULL,

constraint PK_Id primary key (id)
) TYPE=InnoDB;

INSERT INTO users VALUES('marissa','a564de63c2d0da68cf47586ee05984d7 ',1); /* Encoded password for marissa is "koala" */
INSERT INTO users VALUES('dianne','65d15fe9156f9c4bbffd98085992a44e' ,1); /* Encoded password for dianne is "emu" */
INSERT INTO users VALUES('scott','2b58af6dddbd072ed27ffc86725d7d3a', 1); /* Encoded password for scott is "wombat" */
INSERT INTO users VALUES('peter','22b5c9accc6e1ba628cedc63a72d57f8', 0); /* Encoded password for peter is "opal" (but user is disabled) */

INSERT INTO contacts VALUES (1, 'John Smith', 'john@somewhere.com'); /* marissa */
INSERT INTO contacts VALUES (2, 'Michael Citizen', 'michael@xyz.com'); /* marissa */
INSERT INTO contacts VALUES (3, 'Joe Bloggs', 'joe@demo.com'); /* marissa */
INSERT INTO contacts VALUES (4, 'Karen Sutherland', 'karen@sutherland.com'); /* marissa + dianne + scott */
INSERT INTO contacts VALUES (5, 'Mitchell Howard', 'mitchell@abcdef.com'); /* dianne */
INSERT INTO contacts VALUES (6, 'Rose Costas', 'rose@xyz.com'); /* dianne + scott */
INSERT INTO contacts VALUES (7, 'Amanda Smith', 'amanda@abcdef.com'); /* scott */
INSERT INTO contacts VALUES (8, 'Cindy Smith', 'cindy@smith.com'); /* dianne + scott */
INSERT INTO contacts VALUES (9, 'Jonathan Citizen', 'jonathan@xyz.com'); /* scott */

INSERT INTO authorities VALUES('marissa','ROLE_USER');
INSERT INTO authorities VALUES('marissa','ROLE_SUPERVISOR');
INSERT INTO authorities VALUES('dianne','ROLE_USER');
INSERT INTO authorities VALUES('scott','ROLE_USER');
INSERT INTO authorities VALUES('peter','ROLE_USER');

INSERT INTO acl_object_identity VALUES (1, 'sample.contact.Contact:1', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (2, 'sample.contact.Contact:2', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (3, 'sample.contact.Contact:3', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (4, 'sample.contact.Contact:4', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (5, 'sample.contact.Contact:5', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (6, 'sample.contact.Contact:6', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (7, 'sample.contact.Contact:7', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (8, 'sample.contact.Contact:8', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');
INSERT INTO acl_object_identity VALUES (9, 'sample.contact.Contact:9', null, 'net.sf.acegisecurity.acl.basic.SimpleAclEntry');

INSERT INTO acl_permission VALUES (null, 1, 'marissa', 1); /* administer */
INSERT INTO acl_permission VALUES (null, 2, 'marissa', 2); /* read */
INSERT INTO acl_permission VALUES (null, 3, 'marissa', 22); /* read+write+delete */
INSERT INTO acl_permission VALUES (null, 4, 'marissa', 1); /* administer */
INSERT INTO acl_permission VALUES (null, 4, 'dianne', 1); /* administer */
INSERT INTO acl_permission VALUES (null, 4, 'scott', 2); /* read */
INSERT INTO acl_permission VALUES (null, 5, 'dianne', 2); /* read */
INSERT INTO acl_permission VALUES (null, 6, 'dianne', 22); /* read+write+delete */
INSERT INTO acl_permission VALUES (null, 6, 'scott', 2); /* read */
INSERT INTO acl_permission VALUES (null, 7, 'scott', 1); /* administer */
INSERT INTO acl_permission VALUES (null, 8, 'dianne', 2); /* read */
INSERT INTO acl_permission VALUES (null, 8, 'scott', 2); /* read */
INSERT INTO acl_permission VALUES (null, 9, 'scott', 22); /* read+write+delete */

[filot]

Hey, thanks a lot man! I was exactly looking for this!