1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
<?php
$res = array();
$res[] = tableCreate('callback', "
`taskid` varchar(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`dateline` int(10) unsigned NOT NULL,
`cbfunction` varchar(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`args` text NOT NULL,
PRIMARY KEY (`taskid`,`cbfunction`),
KEY `dateline` (`dateline`)
");
$res[] = tableCreate('permission', "
`mask` int(10) unsigned NOT NULL,
`identifier` varchar(32) NOT NULL,
PRIMARY KEY (`mask`),
UNIQUE KEY `identifier` (`identifier`)
");
$res[] = tableCreate('property', "
`name` varchar(50) NOT NULL,
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`value` mediumblob NOT NULL,
PRIMARY KEY (`name`),
KEY `dateline` (`dateline`)
");
$res[] = tableCreate('property_list', "
`name` varchar(50) NOT NULL,
`subkey` int(10) unsigned NOT NULL AUTO_INCREMENT,
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`value` mediumblob NOT NULL,
KEY (`name`),
KEY `dateline` (`dateline`),
KEY (`subkey`),
UNIQUE KEY `compound` (`name`, `subkey`)
");
$res[] = tableCreate('user', "
`userid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`login` varchar(100) NOT NULL,
`passwd` varchar(150) NOT NULL,
`fullname` varchar(100) DEFAULT NULL,
`phone` varchar(100) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`permissions` int(10) unsigned NOT NULL,
`lasteventid` int(10) unsigned NOT NULL DEFAULT '0',
`serverid` int(10) unsigned NULL DEFAULT NULL,
PRIMARY KEY (`userid`),
UNIQUE KEY `login` (`login`)
");
$res[] = tableCreate('session', "
`sid` char(50) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`userid` int(10) unsigned NOT NULL,
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`lastip` varchar(45) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`fixedip` tinyint(1) unsigned NOT NULL DEFAULT '0',
`data` blob NOT NULL,
PRIMARY KEY (`sid`),
KEY `dateline` (`dateline`)
");
$res[] = tableCreate('mail_queue', "
`mailid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`rcpt` varchar(200) NOT NULL,
`subject` varchar(500) NOT NULL,
`body` blob NOT NULL,
`dateline` int(10) unsigned NOT NULL,
`configid` int(10) unsigned NOT NULL,
`nexttry` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`mailid`),
KEY (`configid`),
KEY (`nexttry`)
");
$res[] = tableCreate('mail_config', "
`configid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`host` varchar(100) NOT NULL,
`port` smallint(5) UNSIGNED NOT NULL,
`ssl` ENUM('FORCE_NONE', 'NONE', 'IMPLICIT', 'EXPLICIT') NOT NULL,
`senderaddress` varchar(100) NOT NULL,
`replyto` varchar(100) NOT NULL,
`username` varchar(100) NOT NULL,
`password` varchar(100) NOT NULL,
PRIMARY KEY (`configid`)
");
// Update path
// #######################
// ##### 2014-05-28
// Add dateline field to property table
if (!tableHasColumn('property', 'dateline')) {
Database::exec("ALTER TABLE `property` ADD `dateline` INT( 10 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `name`,
ADD INDEX ( `dateline` )");
}
// #######################
// ##### 2014-08-18
// Remove description column from permission table
tableDropColumn('permission', 'description');
// Add details column to eventlog table
if (!tableHasColumn('user', 'lasteventid')) {
Database::exec("ALTER TABLE `user` ADD `lasteventid` INT(10) UNSIGNED NOT NULL DEFAULT '0'");
}
// #######################
// ##### 2015-01-16
// Extend config module db table, add argument feature to callbacks
if (!tableHasColumn('callback', 'args')) {
Database::exec("ALTER TABLE `callback` ADD `args` TEXT NOT NULL DEFAULT ''");
}
// #######################
// ##### 2018-03-19
// In preparation for LDAP/AD auth: Column to remember origin server
if (!tableHasColumn('user', 'serverid')) {
Database::exec("ALTER TABLE `user` ADD `serverid` int(10) unsigned NULL DEFAULT NULL");
}
// #######################
// ##### 2022-07-04
// Add subkey to property_list, make value mediumblob instead of text
if (!tableHasColumn('property_list', 'subkey')) {
$ret = Database::exec("ALTER TABLE property_list
ADD COLUMN `subkey` int(10) unsigned NOT NULL AUTO_INCREMENT AFTER `name`,
ADD KEY (`subkey`),
ADD UNIQUE KEY `compound` (`name`, `subkey`)");
if ($ret === false) {
finalResponse(UPDATE_FAILED, 'Cannot add subkey to property_list: ' . Database::lastError());
}
$res[] = UPDATE_DONE;
}
foreach (['property', 'property_list'] as $table) {
if (stripos(tableColumnType($table, 'value'), 'mediumblob') === false) {
$ret = Database::exec("ALTER TABLE `$table` MODIFY `value` mediumblob NOT NULL");
if ($ret === false) {
finalResponse(UPDATE_FAILED, "Cannot change value column of $table to mediumblob: " . Database::lastError());
}
$res[] = UPDATE_DONE;
}
}
// Make sure that if any users exist, one of the has UID=1, otherwise if the permission module is
// used we'd lock out everyone
$someUser = Database::queryFirst('SELECT userid FROM user ORDER BY userid ASC LIMIT 1');
if ($someUser !== false && (int)$someUser['userid'] !== 1) {
Database::exec('UPDATE user SET userid = 1 WHERE userid = :oldid', ['oldid' => $someUser['userid']]);
}
$res[] = tableAddConstraint('mail_queue', 'configid', 'mail_config', 'configid',
'ON UPDATE CASCADE ON DELETE CASCADE');
// Create response for browser
if (in_array(UPDATE_DONE, $res)) {
finalResponse(UPDATE_DONE, 'Tables created successfully');
}
finalResponse(UPDATE_NOOP, 'Everything already up to date');
|
