aboutsummaryrefslogtreecommitdiffstats
path: root/mayor-orig/install/base/mysql
diff options
context:
space:
mode:
authorM.Gergo2018-07-06 11:14:41 +0200
committerM.Gergo2018-07-06 11:14:41 +0200
commit43de9af71f7f4ca5731b94a06d688ae8412ba427 (patch)
tree54835de1dfcda504c02da261f0dc26885aed2e89 /mayor-orig/install/base/mysql
parent50310b0e4513ee3fcce67351ae61e8fff851130e (diff)
downloadmayor-43de9af71f7f4ca5731b94a06d688ae8412ba427.tar.gz
mayor-43de9af71f7f4ca5731b94a06d688ae8412ba427.zip
2018/Feb/28 -i állapot hozzáadva, mint a módosítások kiindulási állapota
Diffstat (limited to 'mayor-orig/install/base/mysql')
-rw-r--r--mayor-orig/install/base/mysql/mayor-auth.sql54
-rw-r--r--mayor-orig/install/base/mysql/mayor-login.sql173
-rw-r--r--mayor-orig/install/base/mysql/private-users.sql12
-rw-r--r--mayor-orig/install/base/mysql/utf8.cnf5
4 files changed, 244 insertions, 0 deletions
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