Announcement Announcement Module
Collapse
No announcement yet.
MySQL DDL Page Title Module
Move Remove Collapse
This topic is closed
X
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL DDL

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

  • #2
    Done

    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.

    Comment


    • #3
      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:

      Comment


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

        Code:
                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', '[email protected]');"); // marissa
            template.execute(
                "INSERT INTO contacts VALUES (2, 'Michael Citizen', '[email protected]');"); // marissa
            template.execute(
                "INSERT INTO contacts VALUES (3, 'Joe Bloggs', '[email protected]');"); // marissa
            template.execute(
                "INSERT INTO contacts VALUES (4, 'Karen Sutherland', '[email protected]');"); // marissa + dianne + scott
            template.execute(
                "INSERT INTO contacts VALUES (5, 'Mitchell Howard', '[email protected]');"); // dianne
            template.execute(
                "INSERT INTO contacts VALUES (6, 'Rose Costas', '[email protected]');"); // dianne + scott
            template.execute(
                "INSERT INTO contacts VALUES (7, 'Amanda Smith', '[email protected]');"); // scott
            template.execute(
                "INSERT INTO contacts VALUES (8, 'Cindy Smith', '[email protected]');"); // dianne + scott
            template.execute(
                "INSERT INTO contacts VALUES (9, 'Jonathan Citizen', '[email protected]');"); // 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');");

        Comment


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

          Code:
          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', '[email protected]'); /* marissa */
          INSERT INTO contacts VALUES (2, 'Michael Citizen', '[email protected]'); /* marissa */
          INSERT INTO contacts VALUES (3, 'Joe Bloggs', '[email protected]'); /* marissa */
          INSERT INTO contacts VALUES (4, 'Karen Sutherland', '[email protected]'); /* marissa + dianne + scott */
          INSERT INTO contacts VALUES (5, 'Mitchell Howard', '[email protected]'); /* dianne */
          INSERT INTO contacts VALUES (6, 'Rose Costas', '[email protected]'); /* dianne + scott */
          INSERT INTO contacts VALUES (7, 'Amanda Smith', '[email protected]'); /* scott */
          INSERT INTO contacts VALUES (8, 'Cindy Smith', '[email protected]'); /* dianne + scott */
          INSERT INTO contacts VALUES (9, 'Jonathan Citizen', '[email protected]'); /* 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 */

          Comment


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

            Comment

            Working...
            X