From 43de9af71f7f4ca5731b94a06d688ae8412ba427 Mon Sep 17 00:00:00 2001 From: M.Gergo Date: Fri, 6 Jul 2018 11:14:41 +0200 Subject: 2018/Feb/28 -i állapot hozzáadva, mint a módosítások kiindulási állapota --- mayor-orig/install/base/mysql/mayor-auth.sql | 54 ++++++++ mayor-orig/install/base/mysql/mayor-login.sql | 173 ++++++++++++++++++++++++ mayor-orig/install/base/mysql/private-users.sql | 12 ++ mayor-orig/install/base/mysql/utf8.cnf | 5 + 4 files changed, 244 insertions(+) create mode 100644 mayor-orig/install/base/mysql/mayor-auth.sql create mode 100644 mayor-orig/install/base/mysql/mayor-login.sql create mode 100644 mayor-orig/install/base/mysql/private-users.sql create mode 100644 mayor-orig/install/base/mysql/utf8.cnf (limited to 'mayor-orig/install/base/mysql') diff --git a/mayor-orig/install/base/mysql/mayor-auth.sql b/mayor-orig/install/base/mysql/mayor-auth.sql new file mode 100644 index 00000000..7e0b1731 --- /dev/null +++ b/mayor-orig/install/base/mysql/mayor-auth.sql @@ -0,0 +1,54 @@ + +DROP DATABASE IF EXISTS %MYSQL_AUTH_DB%; +CREATE DATABASE %MYSQL_AUTH_DB% CHARACTER SET utf8 DEFAULT COLLATE utf8_hungarian_ci; + +GRANT ALL ON %MYSQL_AUTH_DB%.* TO '%MYSQL_AUTH_USER%'@'localhost' IDENTIFIED BY '%MYSQL_AUTH_PW%'; + +USE %MYSQL_AUTH_DB%; + +CREATE TABLE `mayorUpdateLog` ( + `scriptFile` varchar(255) COLLATE utf8_hungarian_ci NOT NULL DEFAULT '', + `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`scriptFile`,`dt`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci; + +CREATE TABLE accounts ( + uid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL, + policy ENUM('private','parent','public') NOT NULL, + userAccount VARCHAR(32) NOT NULL, + userCn VARCHAR(64), + userPassword VARBINARY(40) DEFAULT NULL, + studyId VARCHAR(12), + mail VARCHAR(64), + telephoneNumber VARCHAR(16), + shadowLastChange INT UNSIGNED, + shadowMin TINYINT UNSIGNED, + shadowMax TINYINT UNSIGNED, + shadowWarning TINYINT UNSIGNED, + shadowInactive TINYINT UNSIGNED, + shadowExpire INT UNSIGNED, + UNIQUE KEY (userAccount,policy) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_hungarian_ci; + +CREATE TABLE groups ( + gid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL, + groupCn VARCHAR(32), + groupDesc VARCHAR(64), + policy VARCHAR(10) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_hungarian_ci; + +CREATE TABLE members ( + uid INT UNSIGNED NOT NULL, + gid INT UNSIGNED NOT NULL, + INDEX members_uid(uid), + INDEX members_gid(gid), + FOREIGN KEY(uid) + REFERENCES accounts(uid) + ON DELETE CASCADE + ON UPDATE CASCADE, + FOREIGN KEY(gid) + REFERENCES groups(gid) + ON DELETE CASCADE + ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + diff --git a/mayor-orig/install/base/mysql/mayor-login.sql b/mayor-orig/install/base/mysql/mayor-login.sql new file mode 100644 index 00000000..556b28cc --- /dev/null +++ b/mayor-orig/install/base/mysql/mayor-login.sql @@ -0,0 +1,173 @@ + +DROP DATABASE IF EXISTS %MYSQL_LOGIN_DB%; +CREATE DATABASE %MYSQL_LOGIN_DB% CHARACTER SET utf8 DEFAULT COLLATE utf8_hungarian_ci; +GRANT ALL ON %MYSQL_LOGIN_DB%.* TO '%MYSQL_LOGIN_USER%'@'localhost' IDENTIFIED BY '%MYSQL_LOGIN_PW%'; +USE %MYSQL_LOGIN_DB%; + +-- +-- Table structure for table 'login_log' +-- + +CREATE TABLE `mayorUpdateLog` ( + `scriptFile` varchar(255) COLLATE utf8_hungarian_ci NOT NULL DEFAULT '', + `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`scriptFile`,`dt`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci; + +CREATE TABLE loginLog ( + logId int(10) unsigned NOT NULL auto_increment, + policy varchar(10) NOT NULL default 'private', + userAccount varchar(50) default NULL, + ip varchar(15) default NULL, + dt datetime default NULL, + flag tinyint(3) unsigned default NULL, + PRIMARY KEY (logId) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_hungarian_ci; + +-- +-- Table structure for table 'session' +-- + +CREATE TABLE session ( + sessionID char(40) COLLATE utf8_hungarian_ci NOT NULL, + userPassword varchar(32) character set latin1 default NULL, + userAccount varchar(50) default NULL, + userCn varchar(50) default NULL, + studyId BIGINT UNSIGNED NULL, + dt datetime default NULL, + policy varchar(10) COLLATE utf8_hungarian_ci NOT NULL default 'private', + skin varchar(16) default NULL, + lang varchar(5) default NULL, + jsLevel tinyint(3) unsigned default 2, + activity datetime default NULL, + sessionCookie char(40) NOT NULL, + PRIMARY KEY (sessionID,policy) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_hungarian_ci; + +-- +-- Table structure for table 'settings' +-- + +CREATE TABLE settings ( + userAccount varchar(50) NOT NULL, + policy varchar(10) NOT NULL default 'private', + skin varchar(20) default 'default', + lang varchar(5) default 'hu_HU', + jsLevel tinyint(3) unsigned default 2, + lastlogin datetime NOT NULL default '0000-00-00 00:00:00', + PRIMARY KEY (userAccount,policy) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_hungarian_ci; + +-- +-- Table structure for table 'start' +-- + +CREATE TABLE start ( + userAccount varchar(50) NOT NULL, + type varchar(10) NOT NULL default 'column', + name varchar(20) default NULL, + idx tinyint unsigned default 0, + PRIMARY KEY (userAccount,type,name), + KEY (userAccount) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_hungarian_ci; + +-- +-- Table structure for table 'stat' +-- + +CREATE TABLE stat ( + dt datetime default NULL, + policy varchar(20) default NULL, + page varchar(20) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_hungarian_ci; + +CREATE TABLE `cache` ( + `sessionID` char(40), + `policy` varchar(10), + `kulcs` varchar(32) NOT NULL, + `ertek` varchar(100), dt DATETIME, + PRIMARY KEY (sessionID,policy,kulcs) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_hungarian_ci; + +CREATE TABLE `mayorSsl` ( + `sslId` tinyint(1) unsigned NOT NULL AUTO_INCREMENT, + `nodeId` mediumint(8) unsigned zerofill NOT NULL DEFAULT '00000000', + `privateKey` text COLLATE utf8_hungarian_ci NOT NULL, + `publicKey` text COLLATE utf8_hungarian_ci NOT NULL, + `secret` varchar(40) COLLATE utf8_hungarian_ci DEFAULT NULL, + PRIMARY KEY (`sslId`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci; + +CREATE TABLE `mayorKeychain` ( + `nodeId` mediumint(8) unsigned zerofill NOT NULL DEFAULT '00000000', + `nodeTipus` enum('intézmény','fenntartó','backup','fejlesztői','controller') COLLATE utf8_hungarian_ci DEFAULT 'intézmény', + `dt` date DEFAULT NULL, + `OMKod` mediumint(8) unsigned zerofill NOT NULL DEFAULT '00000000', + `publicKey` text COLLATE utf8_hungarian_ci NOT NULL, + `valid` tinyint(1) unsigned NOT NULL DEFAULT '1', + `url` varchar(255) COLLATE utf8_hungarian_ci NOT NULL, + `nev` varchar(128) COLLATE utf8_hungarian_ci DEFAULT NULL, + `rovidNev` varchar(16) COLLATE utf8_hungarian_ci DEFAULT NULL, + `cimHelyseg` varchar(32) COLLATE utf8_hungarian_ci DEFAULT NULL, + `cimIrsz` varchar(8) COLLATE utf8_hungarian_ci DEFAULT NULL, + `cimKozteruletNev` varchar(32) COLLATE utf8_hungarian_ci DEFAULT NULL, + `cimKozteruletJelleg` varchar(32) COLLATE utf8_hungarian_ci DEFAULT NULL, + `cimHazszam` varchar(20) COLLATE utf8_hungarian_ci DEFAULT NULL, + `telefon` varchar(64) COLLATE utf8_hungarian_ci DEFAULT NULL, + `fax` varchar(64) COLLATE utf8_hungarian_ci DEFAULT NULL, + `email` varchar(96) COLLATE utf8_hungarian_ci DEFAULT NULL, + `honlap` varchar(96) COLLATE utf8_hungarian_ci DEFAULT NULL, + `kapcsolatNev` varchar(128) COLLATE utf8_hungarian_ci DEFAULT NULL, + `kapcsolatEmail` varchar(96) COLLATE utf8_hungarian_ci DEFAULT NULL, + `kapcsolatTelefon` varchar(64) COLLATE utf8_hungarian_ci DEFAULT NULL, + PRIMARY KEY (`nodeId`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci; + +INSERT IGNORE INTO mayorKeychain (dt,nodeId,nodeTipus,publicKey,url,valid) +VALUES (CURDATE(), '09862967','controller','-----BEGIN PUBLIC KEY----- +MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDbMFUWy0Juy/7uHROaLOMPSHMI +Vw/jSmEAKW6lCeIOH2oUVsAQkgsZCFiKqQhH3WbtLrAzSmOA7+rEU3RtgXtPZpHN +2UUPQqoHWoMOkumfIS5oM0sQgSQ738TC0X9yxZlNqZtdpdCa0zjVnGLxqVVhS3KD ++O8uuA7jQwczlSqWJwIDAQAB +-----END PUBLIC KEY-----','https://www.mayor.hu',1); + +CREATE TABLE `accountRecovery` ( + `recoveryId` int(11) unsigned NOT NULL AUTO_INCREMENT, + `policy` enum('private','parent','public') COLLATE utf8_hungarian_ci NOT NULL, + `userAccount` varchar(32) COLLATE utf8_hungarian_ci NOT NULL, + `selector` char(16) COLLATE utf8_hungarian_ci DEFAULT NULL, + `token` char(64) COLLATE utf8_hungarian_ci DEFAULT NULL, + `expires` datetime DEFAULT NULL, + PRIMARY KEY (`recoveryId`), + KEY `selector` (`selector`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci; + +CREATE TABLE `facebookConnect` ( + `fbUserId` bigint(20) NOT NULL, + `userAccount` varchar(64) COLLATE utf8_hungarian_ci NOT NULL, + `policy` enum('public','parent','private') COLLATE utf8_hungarian_ci DEFAULT NULL, + `fbUserCn` varchar(64) COLLATE utf8_hungarian_ci DEFAULT NULL, + `fbUserEmail` varchar(64) COLLATE utf8_hungarian_ci DEFAULT NULL, + `studyId` varchar(11) COLLATE utf8_hungarian_ci DEFAULT NULL, + UNIQUE KEY `fbUserId` (`fbUserId`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci; + +CREATE TABLE `googleConnect` ( + `googleSub` varchar(32) COLLATE utf8_hungarian_ci DEFAULT NULL, + `userAccount` varchar(64) COLLATE utf8_hungarian_ci NOT NULL, + `policy` enum('public','parent','private') COLLATE utf8_hungarian_ci DEFAULT NULL, + `googleUserCn` varchar(64) COLLATE utf8_hungarian_ci DEFAULT NULL, + `googleUserEmail` varchar(64) COLLATE utf8_hungarian_ci DEFAULT NULL, + `studyId` varchar(12) COLLATE utf8_hungarian_ci DEFAULT NULL, + UNIQUE KEY `googleSub` (`googleSub`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci; + +CREATE TABLE loginLog_a ( + logId int(10) unsigned NOT NULL, + policy varchar(10) NOT NULL default 'private', + userAccount varchar(50) default NULL, + ip varchar(15) default NULL, + dt datetime default NULL, + flag tinyint(3) unsigned default NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_hungarian_ci; + diff --git a/mayor-orig/install/base/mysql/private-users.sql b/mayor-orig/install/base/mysql/private-users.sql new file mode 100644 index 00000000..101188f5 --- /dev/null +++ b/mayor-orig/install/base/mysql/private-users.sql @@ -0,0 +1,12 @@ +USE %MYSQL_PRIVATE_DB%; + +INSERT INTO accounts + (policy, userAccount, userCn, userPassword, shadowLastChange, shadowMin, shadowMax, shadowWarning, shadowInactive, shadowExpire) + VALUES ('private','mayoradmin','MaYoR Adminisztrátor',SHA('jelszo'),(TO_DAYS(now())-TO_DAYS("1970-01-01"))-80,2,80,10,0,NULL); +SET @uid = (SELECT uid FROM accounts WHERE userAccount='mayoradmin'); +INSERT INTO groups (groupCn,groupDesc,policy) VALUES ('useradmin','Adminisztrátor','private'),('tanar','Tanárok','private'), + ('diak','Diákok','private'),('titkarsag','Titkárság','private'),('egyeb','Egyéb','private'); +SET @gid = (SELECT gid FROM groups WHERE groupCn='useradmin'); +INSERT INTO members (uid,gid) VALUES (@uid,@gid); +SET @gid = (SELECT gid FROM groups WHERE groupCn='egyeb'); +INSERT INTO members (uid,gid) VALUES (@uid,@gid); diff --git a/mayor-orig/install/base/mysql/utf8.cnf b/mayor-orig/install/base/mysql/utf8.cnf new file mode 100644 index 00000000..e5927257 --- /dev/null +++ b/mayor-orig/install/base/mysql/utf8.cnf @@ -0,0 +1,5 @@ + +[mysqld] + character-set-server = utf8 + collation-server = utf8_hungarian_ci + skip-character-set-client-handshake -- cgit v1.2.3