[geeklog-cvs] Auth_Enterprise/data/sql mysql.sql,NONE,1.1

[jellybob at iowaoutdoors.org]

Update of /var/cvs/Auth_Enterprise/data/sql
In directory www:/tmp/cvs-serv31358/data/sql

Added Files:
	mysql.sql 
Log Message:
There's always something forgotten, today it was the sql dump.


--- NEW FILE: mysql.sql ---
CREATE TABLE ae_application (
  app_id varchar(30) NOT NULL default '',
  app_name varchar(50) NOT NULL default '',
  app_contact_email varchar(50) default NULL,
  PRIMARY KEY  (app_id)
) TYPE=INNODB;

INSERT INTO ae_application VALUES ('Auth_Enterprise','Auth_Enteprise Service','tony at geeklog.net');
INSERT INTO ae_application VALUES ('TEST_PEAR_DB_APP','Test PEAR::DB Provider App', 'tony at geeklog.net');

CREATE TABLE ae_app_privileges (
  ap_app_id varchar(30) NOT NULL default '',
  ap_priv_cd varchar(20) NOT NULL default '',
  ap_priv_desc varchar(128) NOT NULL default '',
  PRIMARY KEY (ap_app_id,ap_priv_cd)
) TYPE=INNODB;
ALTER TABLE ae_app_privileges ADD FOREIGN KEY (ap_app_id) REFERENCES ae_application(app_id);

INSERT INTO ae_app_privileges VALUES ('Auth_Enterprise','AE_ADMIN_PRIV','Auth_Enterprise Administrator');
INSERT INTO ae_app_privileges VALUES ('TEST_PEAR_DB_APP', 'AE_ACCOUNT_MGR', 'Account Manager for Test PEAR::DB Provider App');
INSERT INTO ae_app_privileges VALUES ('TEST_PEAR_DB_APP', 'AE_APP_OWNER', 'Application Owner for Test PEAR::DB Provider App');

CREATE TABLE ae_user (
  user_name varchar(75) NOT NULL default '',
  user_password varchar(20) NOT NULL default '',
  user_account_locked tinyint(1) NOT NULL default '0',
  user_failed_attempts tinyint(1) unsigned NOT NULL default '0',
  user_pwd_last_set int(10) unsigned NOT NULL default '0',
  user_creation_date int(10) unsigned NOT NULL default '0',
  user_change_date int(10) unsigned NOT NULL default '0',
  user_change_user_name varchar(75) default NULL,
  INDEX user_change_user_name (user_change_user_name),
  PRIMARY KEY  (user_name)
) TYPE=INNODB;
ALTER TABLE ae_user ADD FOREIGN KEY (user_change_user_name) REFERENCES ae_user(user_name);

INSERT INTO ae_user VALUES ('ROOT at LOCALHOST','foo',0,0,1038344984,1037738177,1037738177,NULL);
INSERT INTO ae_user VALUES ('WEBMASTER at LOCALHOST','1038344984',0,0,1038344985,1037738177,1037738177,NULL);

CREATE TABLE ae_user_old_password (
  uop_user_name varchar(75) NOT NULL default '',
  uop_password varchar(20) NOT NULL default '',
  uop_insert_date int(10) NOT NULL default '',
  PRIMARY KEY  (uop_user_name,uop_password)  
) TYPE=INNODB;
ALTER TABLE ae_user_old_password ADD FOREIGN KEY (uop_user_name) REFERENCES ae_user(user_name);

CREATE TABLE ae_group (
  grp_id mediumint(10) NOT NULL auto_increment,
  grp_app_id varchar(30) NOT NULL default '',
  grp_logical_name varchar(50) NOT NULL default '',
  grp_display_name varchar(50) NOT NULL default '',
  grp_descr varchar(255) NOT NULL default '',
  grp_create_date int(10) unsigned NOT NULL default '0',
  grp_change_date int(10) unsigned default NULL,
  grp_change_user_name varchar(75) default NULL,
  PRIMARY KEY  (grp_id),
  INDEX grp_app_id (grp_app_id),
  INDEX grp_change_user_name (grp_change_user_name)
) TYPE=INNODB;
ALTER TABLE ae_group ADD FOREIGN KEY (grp_app_id) REFERENCES ae_application(app_id);
ALTER TABLE ae_group ADD FOREIGN KEY (grp_change_user_name) REFERENCES ae_user(user_name);

INSERT INTO ae_group VALUES (1,'Auth_Enterprise','AE_ADMIN','Auth_Enterprise Administrator','This user can modify anything within the  Auth_Enterprise application',1037738177,NULL,NULL);

CREATE TABLE ae_group_assignment (
  ga_main_grp_id mediumint(10) NOT NULL default '0',
  ga_assigned_user_name varchar(75) default NULL,
  ga_assigned_grp_id mediumint(10) default NULL,
  INDEX ga_main_grp_id (ga_main_grp_id),
  INDEX ga_assigned_user_name (ga_assigned_user_name),
  INDEX ga_assigned_grp_id (ga_assigned_grp_id)
) TYPE=INNODB;
ALTER TABLE ae_group_assignment ADD FOREIGN KEY (ga_assigned_user_name) REFERENCES ae_user(user_name);
ALTER TABLE ae_group_assignment ADD FOREIGN KEY (ga_main_grp_id) REFERENCES ae_group(grp_id);
ALTER TABLE ae_group_assignment ADD FOREIGN KEY (ga_assigned_grp_id) REFERENCES ae_group(grp_id);

INSERT INTO ae_group_assignment VALUES (1,'ROOT at LOCALHOST',NULL);

CREATE TABLE ae_privilege_access (
  pa_priv_cd varchar(20) NOT NULL default '',
  pa_app_id varchar(30) NOT NULL default '',
  pa_user_name varchar(75) default NULL,
  pa_grp_id mediumint(10) default NULL,
  UNIQUE INDEX (pa_priv_cd, pa_app_id, pa_user_name, pa_grp_id),
  INDEX pa_app_id (pa_app_id),
  INDEX pa_grp_id (pa_grp_id),
  INDEX pa_user_name (pa_user_name)
) TYPE=INNODB;
ALTER TABLE ae_privilege_access ADD FOREIGN KEY (pa_app_id) REFERENCES ae_application(app_id);
ALTER TABLE ae_privilege_access ADD FOREIGN KEY (pa_grp_id) REFERENCES ae_group(grp_id);
ALTER TABLE ae_privilege_access ADD FOREIGN KEY (pa_user_name) REFERENCES ae_user(user_name);

INSERT INTO ae_privilege_access VALUES ('AE_ADMIN_PRIV','Auth_Enterprise',NULL,1);
INSERT INTO ae_privilege_access VALUES ('AE_ACCOUNT_MGR','TEST_PEAR_DB_APP','WEBMASTER at LOCALHOST',NULL);

CREATE TABLE ae_log_message (
  lm_msg_id mediumint(10) NOT NULL auto_increment,
  lm_code string varchar(30),
  lm_message string varchar(128) NOT NULL,
  lm_file string varchar(50) NOT NULL,
  lm_line int(5) unsigned NOT NULL,
  lm_trace text,
  PRIMARY KEY (lm_msg_id),
  INDEX lm_code (lm_code)
) TYPE=INNODB;