#122703 Fix upgrade if db_prefix is in use
[project/privatemsg.git] / privatemsg.install
1 <?php
2 // $Id$
3
4 function privatemsg_install() {
5 switch ($GLOBALS['db_type']) {
6 case 'mysql':
7 case 'mysqli':
8 db_query("CREATE TABLE {privatemsg} (
9 id int(10) unsigned NOT NULL primary key,
10 author int(10) unsigned NOT NULL,
11 recipient int(10) unsigned NOT NULL,
12 subject varchar(64) NOT NULL,
13 message text NOT NULL,
14 timestamp int(11) unsigned NOT NULL,
15 newmsg tinyint unsigned NOT NULL,
16 hostname varchar(255) NOT NULL,
17 folder int(10) unsigned NOT NULL DEFAULT '0',
18 author_del tinyint unsigned NOT NULL DEFAULT '0',
19 recipient_del tinyint unsigned NOT NULL DEFAULT '0',
20 format int(4) NOT NULL DEFAULT '0',
21 key (recipient),
22 key (folder)
23 ) /*!40100 DEFAULT CHARACTER SET utf8 */;");
24 db_query("CREATE TABLE {privatemsg_folder} (
25 fid int(10) unsigned NOT NULL primary key,
26 uid int(10) unsigned NOT NULL,
27 name varchar(255) NOT NULL
28 ) /*!40100 DEFAULT CHARACTER SET utf8 */;");
29 db_query("CREATE TABLE {privatemsg_archive} (
30 id int(10) unsigned NOT NULL primary key,
31 author int(10) unsigned NOT NULL,
32 recipient int(10) unsigned NOT NULL,
33 subject VARCHAR(64) NOT NULL,
34 message text NOT NULL,
35 timestamp int(11) unsigned NOT NULL,
36 hostname varchar(255) NOT NULL,
37 folder int(10) unsigned NOT NULL,
38 format int(4) NOT NULL DEFAULT '0',
39 key (recipient)
40 ) /*!40100 DEFAULT CHARACTER SET utf8 */;");
41 break;
42 case 'pgsql':
43 db_query("CREATE TABLE {privatemsg} (
44 id integer NOT NULL,
45 author integer NOT NULL,
46 recipient integer NOT NULL,
47 subject varchar(64) NOT NULL,
48 message text NOT NULL,
49 timestamp integer NOT NULL,
50 newmsg smallint NOT NULL,
51 hostname varchar(255) NOT NULL,
52 format smallint NOT NULL DEFAULT '0',
53 folder integer NOT NULL DEFAULT '0',
54 author_del smallint NOT NULL DEFAULT '0',
55 recipient_del smallint NOT NULL DEFAULT '0',
56 PRIMARY KEY (id)
57 )");
58 db_query("CREATE INDEX {privatemsg_folder_index} ON {privatemsg}(folder)");
59 db_query("CREATE TABLE {privatemsg_folder} (
60 fid integer NOT NULL,
61 uid integer NOT NULL,
62 name varchar(255) not null,
63 PRIMARY KEY (fid)
64 )");
65 db_query("CREATE TABLE {privatemsg_archive} (
66 id integer NOT NULL,
67 author integer NOT NULL,
68 recipient integer NOT NULL,
69 subject varchar(64) NOT NULL,
70 message text NOT NULL,
71 timestamp integer NOT NULL,
72 hostname varchar(255) NOT NULL,
73 format smallint NOT NULL DEFAULT '0',
74 folder integer NOT NULL,
75 PRIMARY KEY (id)
76 )");
77 db_query("CREATE INDEX {privatemsg_archive_recipient} ON {privatemsg_archive}(recipient)");
78 db_query("create or replace function unix_timestamp(timestamp with time zone)
79 returns int as '
80 declare
81 date alias for " .'$1'. ";
82 timezero timestamp;
83 offset interval;
84 begin
85 timezero := timestamp ''1970-1-1 00:00'' at time zone ''utc'';
86 offset := date-timezero;
87
88 return (extract(''days'' from offset)*86400+
89 extract(''hours'' from offset)*3600+
90 extract(''minutes'' from offset)*60+
91 extract(''seconds'' from offset))::int;
92 end;
93 ' language 'plpgsql'");
94 db_query("create or replace function unix_timestamp(timestamp without time zone)
95 returns int as '
96 declare
97 date alias for " .'$1'. ";
98 timezero timestamp;
99 offset interval;
100 begin
101 timezero := timestamp ''1970-1-1 00:00'' at time zone ''utc'';
102 offset := date-timezero;
103
104 return (extract(''days'' from offset)*86400+
105 extract(''hours'' from offset)*3600+
106 extract(''minutes'' from offset)*60+
107 extract(''seconds'' from offset))::int;
108 end;
109 ' language 'plpgsql'");
110 break;
111 }
112 // Sent messages folder
113 db_query("INSERT INTO {privatemsg_folder} (fid, uid, name) VALUES (1, 0, 'Sent')");
114 do {
115 $i = db_next_id('{privatemsg_folder}_fid');
116 }
117 while ($i < 1); // In case this api ever changes to start at zero..
118 }
119
120 function privatemsg_uninstall() {
121 db_query("DROP TABLE {privatemsg}");
122 db_query("DROP TABLE {privatemsg_folder}");
123 db_query("DROP TABLE {privatemsg_archive}");
124 db_query("DELETE FROM {variable} WHERE name LIKE 'privatemsg_%'");
125 cache_clear_all('variables', 'cache');
126 }
127
128 /* Upgrade on mysql from versions before 22-May-2003:
129 Create privatemsg_archive/privatemsg_folder tables and insert one row, shown above^
130 ALTER TABLE privatemsg ADD folder int(10) unsigned NOT NULL;
131 ALTER TABLE privatemsg ADD author_del tinyint unsigned NOT NULL;
132 ALTER TABLE privatemsg ADD recipient_del tinyint unsigned NOT NULL;
133 ALTER TABLE privatemsg ADD INDEX(folder);
134 ALTER TABLE privatemsg CHANGE hostname hostname varchar(255) NOT NULL;
135 Continue with steps below, but skip ALTER line for privatemsg_archive..
136 *
137 * Upgrade on mysql from versions before 29-Apr-2005:
138 ALTER TABLE privatemsg CHANGE new newmsg tinyint UNSIGNED NOT NULL;
139 ALTER TABLE privatemsg ADD format int(4) NOT NULL DEFAULT '0';
140 ALTER TABLE privatemsg_archive ADD format int(4) NOT NULL DEFAULT '0';
141 UPDATE privatemsg SET format=1;
142 UPDATE privatemsg_archive SET format=1;
143 */
144
145 function privatemsg_update_1() {
146 return _system_update_utf8(array('privatemsg', 'privatemsg_archive', 'privatemsg_folder'));
147 }
148
149 function privatemsg_update_2() {
150 $ret = array();
151 switch ($GLOBALS['db_type']) {
152 case 'mysql':
153 case 'mysqli':
154 $sql = 'ALTER TABLE {%s} MODIFY %s int(10) unsigned NOT NULL';
155 $seq = "INSERT INTO {sequences} (name, id) VALUES ('%s', %d)";
156 break;
157 case 'pgsql':
158 $sql = 'ALTER TABLE {%s} ALTER COLUMN %s SET DEFAULT NULL';
159 $seq = 'CREATE SEQUENCE %s_seq INCREMENT 1 START %d';
160 break;
161 default:
162 return $ret;
163 }
164 foreach (array('privatemsg' => 'id', 'privatemsg_folder' => 'fid') as $table => $id) {
165 $ret[] = update_sql(sprintf($sql, $table, $id));
166 $max = db_result(db_query('SELECT max('. $id .') FROM {'. $table .'}'));
167 if ($table == 'privatemsg') {
168 $max = max($max, db_result(db_query('SELECT max(id) FROM {privatemsg_archive}')));
169 }
170 $ret[] = update_sql(sprintf($seq, '{'. $table .'}_'. $id, $max));
171 }
172 $ret[] = update_sql(sprintf($sql, 'privatemsg_archive', 'id'));
173 return $ret;
174 }
175 ?>