PageRenderTime 52ms CodeModel.GetById 22ms RepoModel.GetById 0ms app.codeStats 0ms

/ratbox-services-1.2.1/tools/dbupgrade.pl

#
Perl | 399 lines | 343 code | 39 blank | 17 comment | 25 complexity | 5124a8e345764d9ddfec28700c0584bf MD5 | raw file
Possible License(s): GPL-2.0, BSD-3-Clause
  1. #!/usr/bin/perl -w
  2. #
  3. # dbupgrade.pl
  4. # This script generates the SQL commands for the database alterations needed
  5. # when upgrading versions of ratbox-services.
  6. #
  7. # It takes the version of ratbox-services as an argument, eg:
  8. # ./dbupgrade.pl ratbox-services-1.0.3
  9. # You may leave off the "ratbox-services-" if you wish. You should NOT
  10. # however leave off extra version information like "rc1".
  11. #
  12. # Note, this script will only deal with actual releases, not svn copies.
  13. #
  14. # Copyright (C) 2006 Lee Hardy <lee -at- leeh.co.uk>
  15. # Copyright (C) 2006 ircd-ratbox development team
  16. #
  17. # $Id: dbupgrade.pl 25403 2008-05-20 19:10:18Z leeh $
  18. use strict;
  19. require "definetolength.pl";
  20. unless($ARGV[0] && $ARGV[1])
  21. {
  22. print "Usage: dbupgrade.pl <ratbox-services version> <sqlite|mysql|pgsql> [include_path]\n";
  23. print "Eg, ./dbupgrade.pl 1.0.3 sqlite\n";
  24. exit;
  25. }
  26. unless(-r "../include/setup.h")
  27. {
  28. print("Unable to read ../include/setup.h, please run configure first\n");
  29. exit();
  30. }
  31. my %versionlist = (
  32. "1.0.0" => 1,
  33. "1.0.1" => 1,
  34. "1.0.2" => 1,
  35. "1.0.3" => 1,
  36. "1.1.0beta1" => 2,
  37. "1.1.0beta2" => 2,
  38. "1.1.0beta3" => 2,
  39. "1.1.0beta4" => 3,
  40. "1.1.0rc1" => 4,
  41. "1.1.0rc2" => 5,
  42. "1.1.0rc3" => 5,
  43. "1.1.0" => 5,
  44. "1.1.1" => 5,
  45. "1.2.0beta1" => 6,
  46. "1.2.0beta2" => 6,
  47. "1.2.0rc1" => 6,
  48. "1.2.0rc2" => 6,
  49. "1.2.0" => 6
  50. );
  51. my $version = $ARGV[0];
  52. my $dbtype = $ARGV[1];
  53. my %vals;
  54. $version =~ s/^ircd-ratbox-//;
  55. my $currentver = $versionlist{"$version"};
  56. my $upgraded = 0;
  57. if(!$currentver)
  58. {
  59. print "Unknown version $version\n";
  60. exit;
  61. }
  62. if($dbtype ne "sqlite" && $dbtype ne "mysql" && $dbtype ne "pgsql")
  63. {
  64. print "Unknown database type $dbtype\n";
  65. exit;
  66. }
  67. if($ARGV[2])
  68. {
  69. %vals = &parse_includes("$ARGV[2]");
  70. }
  71. else
  72. {
  73. %vals = &parse_includes("../include");
  74. }
  75. while(my ($key, $value) = each(%vals))
  76. {
  77. if($value == 1)
  78. {
  79. print "Unable to set $key -- include path must be wrong.\n";
  80. exit;
  81. }
  82. }
  83. if($currentver < 2)
  84. {
  85. print "-- To version 1.1.0beta1\n";
  86. if($dbtype eq "sqlite")
  87. {
  88. print "CREATE TABLE users_resetpass (\n";
  89. print " username TEXT, token TEXT, time INTEGER,\n";
  90. print " PRIMARY KEY(username)\n";
  91. print ");\n";
  92. print "CREATE TABLE users_sync (\n";
  93. print " id INTEGER PRIMARY KEY, hook TEXT, data TEXT\n";
  94. print ");\n";
  95. print "ALTER TABLE users ADD COLUMN verify_token TEXT;\n";
  96. print "ALTER TABLE users ADD COLUMN suspend_reason TEXT;\n";
  97. print "ALTER TABLE channels ADD COLUMN suspend_reason TEXT;\n";
  98. }
  99. elsif($dbtype eq "mysql")
  100. {
  101. print "CREATE TABLE users_sync (\n";
  102. print " id INTEGER AUTO_INCREMENT, hook VARCHAR(50) NOT NULL, data TEXT,\n";
  103. print " PRIMARY KEY(id)\n";
  104. print ");\n";
  105. }
  106. else
  107. {
  108. print "CREATE TABLE users_sync (\n";
  109. print " id SERIAL, hook VARCHAR(50) NOT NULL, data TEXT,\n";
  110. print " PRIMARY KEY(id)\n";
  111. print ");\n";
  112. }
  113. if($dbtype eq "mysql" || $dbtype eq "pgsql")
  114. {
  115. print "CREATE TABLE users_resetpass (\n";
  116. print " username VARCHAR(".$vals{"USERREGNAME_LEN"}.") NOT NULL, token VARCHAR(10), time INTEGER,\n";
  117. print " PRIMARY KEY(username)\n";
  118. print ");\n";
  119. print "ALTER TABLE users ADD COLUMN verify_token VARCHAR(8);\n";
  120. print "ALTER TABLE users ADD COLUMN suspend_reason VARCHAR(".$vals{"SUSPENDREASONLEN"}.");\n";
  121. print "ALTER TABLE channels ADD COLUMN suspend_reason VARCHAR(".$vals{"SUSPENDREASONLEN"}.");\n";
  122. }
  123. print "CREATE TABLE global_welcome (\n";
  124. print " id INTEGER, text TEXT,\n";
  125. print " PRIMARY KEY(id)\n";
  126. print ");\n";
  127. print "\n";
  128. $upgraded = 1;
  129. }
  130. if($currentver < 3)
  131. {
  132. print "-- To version 1.1.0beta4\n";
  133. if($dbtype eq "mysql" || $dbtype eq "pgsql")
  134. {
  135. print "CREATE TABLE channels_dropowner (\n";
  136. print " chname VARCHAR(".$vals{"CHANNELLEN"}.") NOT NULL, token VARCHAR(10), time INTEGER,\n";
  137. print " PRIMARY KEY(chname)\n";
  138. print ");\n";
  139. print "CREATE TABLE users_resetemail (\n";
  140. print " username VARCHAR(".$vals{"USERREGNAME_LEN"}.") NOT NULL, token VARCHAR(10),\n";
  141. print " email VARCHAR(".$vals{"EMAILLEN"}.") DEFAULT NULL, time INTEGER,\n";
  142. print " PRIMARY KEY (username)\n";
  143. print ");\n";
  144. }
  145. else
  146. {
  147. print "CREATE TABLE channels_dropowner (\n";
  148. print " chname TEXT, token TEXT, time INTEGER,\n";
  149. print " PRIMARY KEY(chname)\n";
  150. print ");\n";
  151. print "CREATE TABLE users_resetemail (\n";
  152. print " username TEXT, token TEXT, email TEXT DEFAULT NULL, time INTEGER,\n";
  153. print " PRIMARY KEY(username)\n";
  154. print ");\n";
  155. }
  156. if($dbtype eq "mysql")
  157. {
  158. print "ALTER TABLE channels_dropowner ADD INDEX (time);\n";
  159. print "ALTER TABLE users_resetpass ADD INDEX (time);\n";
  160. print "ALTER TABLE users_resetemail ADD INDEX (time);\n";
  161. }
  162. elsif($dbtype eq "pgsql")
  163. {
  164. print "CREATE INDEX dropowner_time_idx ON channels_dropowner (time);\n";
  165. print "CREATE INDEX resetpass_time_idx ON users_resetpass (time);\n";
  166. print "CREATE INDEX resetemail_time_idx ON users_resetemail (time);\n";
  167. }
  168. print "UPDATE operserv SET chname=LOWER(chname);\n";
  169. print "UPDATE operbot SET chname=LOWER(chname);\n";
  170. print "UPDATE operbans SET mask=LOWER(mask);\n";
  171. print "\n";
  172. $upgraded = 1;
  173. }
  174. if($currentver < 4)
  175. {
  176. print "-- To version 1.1.0rc1\n";
  177. if($dbtype eq "mysql" || $dbtype eq "pgsql")
  178. {
  179. print "CREATE TABLE email_banned_domain (\n";
  180. print " domain VARCHAR(255) NOT NULL,\n";
  181. print " PRIMARY KEY(domain)\n";
  182. print ");\n";
  183. }
  184. else
  185. {
  186. print "CREATE TABLE email_banned_domain (\n";
  187. print " domain TEXT NOT NULL,\n";
  188. print " PRIMARY KEY(domain)\n";
  189. print ");\n";
  190. }
  191. print "\n";
  192. $upgraded = 1;
  193. }
  194. if($currentver < 5)
  195. {
  196. print "-- To version 1.1.0rc2\n";
  197. if($dbtype eq "mysql")
  198. {
  199. print "ALTER TABLE members ADD PRIMARY KEY(chname, username);\n";
  200. print "ALTER TABLE members ADD INDEX (chname);\n";
  201. print "ALTER TABLE members ADD INDEX (username);\n";
  202. print "ALTER TABLE bans ADD PRIMARY KEY(chname, mask);\n";
  203. print "ALTER TABLE bans ADD INDEX (chname);\n";
  204. print "UPDATE channels SET url=SUBSTR(url,1,".$vals{"URLLEN"}.");\n";
  205. print "ALTER TABLE channels CHANGE url url VARCHAR(".$vals{"URLLEN"}.");\n";
  206. }
  207. elsif($dbtype eq "pgsql")
  208. {
  209. print "ALTER TABLE members ADD PRIMARY KEY(chname, username);\n";
  210. print "CREATE INDEX members_chname_idx ON members (chname);\n";
  211. print "CREATE INDEX members_username_idx ON members (username);\n";
  212. print "ALTER TABLE bans ADD PRIMARY KEY(chname, mask);\n";
  213. print "CREATE INDEX bans_chname_idx ON bans (chname);\n";
  214. print "UPDATE channels SET url=SUBSTR(url,1,".$vals{"URLLEN"}.");\n";
  215. print "ALTER TABLE channels ALTER COLUMN url TYPE VARCHAR(".$vals{"URLLEN"}.");\n";
  216. }
  217. print "\n";
  218. $upgraded = 1;
  219. }
  220. if($currentver < 6)
  221. {
  222. print "-- To version 1.2.0beta1\n";
  223. print "ALTER TABLE users ADD COLUMN language VARCHAR(255) DEFAULT '';\n";
  224. if($dbtype eq "mysql")
  225. {
  226. print "ALTER TABLE users ADD COLUMN suspend_time INT UNSIGNED DEFAULT '0';\n";
  227. print "ALTER TABLE channels ADD COLUMN suspend_time INT UNSIGNED DEFAULT '0';\n";
  228. print "CREATE TABLE ignore_hosts (\n";
  229. print " hostname VARCHAR(255) NOT NULL,\n";
  230. print " oper VARCHAR(" . $vals{"OPERNAMELEN"} . ") NOT NULL,\n";
  231. print " reason VARCHAR(255) NOT NULL,\n";
  232. print " PRIMARY KEY(hostname)\n";
  233. print ");\n";
  234. print "CREATE TABLE operbans_regexp (\n";
  235. print " id INTEGER AUTO_INCREMENT,\n";
  236. print " regex VARCHAR(255) NOT NULL,\n";
  237. print " reason VARCHAR(" . $vals{"REASONLEN"} . ") NOT NULL,\n";
  238. print " hold INTEGER,\n";
  239. print " create_time INTEGER,\n";
  240. print " oper VARCHAR(" . $vals{"OPERNAMELEN"} . ") NOT NULL,\n";
  241. print " PRIMARY KEY(id)\n";
  242. print ");\n";
  243. print "CREATE TABLE operbans_regexp_neg (\n";
  244. print " id INTEGER AUTO_INCREMENT,\n";
  245. print " parent_id INTEGER NOT NULL,\n";
  246. print " regex VARCHAR(255) NOT NULL,\n";
  247. print " oper VARCHAR(" . $vals{"OPERNAMELEN"} . ") NOT NULL,\n";
  248. print " PRIMARY KEY(id)\n";
  249. print ");\n";
  250. print "ALTER TABLE users DROP PRIMARY KEY;\n";
  251. print "ALTER TABLE users ADD COLUMN id INTEGER AUTO_INCREMENT PRIMARY KEY FIRST;\n";
  252. print "ALTER TABLE users ADD UNIQUE(username);\n";
  253. print "CREATE TABLE memos(\n";
  254. print " id INTEGER AUTO_INCREMENT,\n";
  255. print " user_id INTEGER NOT NULL,\n";
  256. print " source_id INTEGER NOT NULL,\n";
  257. print " source VARCHAR(" . $vals{"USERREGNAME_LEN"} . ") NOT NULL,\n";
  258. print " timestamp INTEGER UNSIGNED DEFAULT '0',\n";
  259. print " flags INTEGER UNSIGNED DEFAULT '0',\n";
  260. print " text TEXT,\n";
  261. print " PRIMARY KEY(id)\n";
  262. print ");\n";
  263. }
  264. elsif($dbtype eq "pgsql")
  265. {
  266. print "ALTER TABLE users ADD COLUMN suspend_time INTEGER DEFAULT '0';\n";
  267. print "ALTER TABLE channels ADD COLUMN suspend_time INTEGER DEFAULT '0';\n";
  268. print "CREATE TABLE ignore_hosts (\n";
  269. print " hostname VARCHAR(255) NOT NULL,\n";
  270. print " oper VARCHAR(" . $vals{"OPERNAMELEN"} . ") NOT NULL,\n";
  271. print " reason VARCHAR(255) NOT NULL,\n";
  272. print " PRIMARY KEY(hostname)\n";
  273. print ");\n";
  274. print "CREATE TABLE operbans_regexp (\n";
  275. print " id SERIAL,\n";
  276. print " regex VARCHAR(255) NOT NULL,\n";
  277. print " reason VARCHAR(" . $vals{"REASONLEN"} . ") NOT NULL,\n";
  278. print " hold INTEGER,\n";
  279. print " create_time INTEGER,\n";
  280. print " oper VARCHAR(" . $vals{"OPERNAMELEN"} . ") NOT NULL,\n";
  281. print " PRIMARY KEY(id)\n";
  282. print ");\n";
  283. print "CREATE TABLE operbans_regexp_neg (\n";
  284. print " id SERIAL,\n";
  285. print " parent_id BIGINT NOT NULL,\n";
  286. print " regex VARCHAR(255) NOT NULL,\n";
  287. print " oper VARCHAR(" . $vals{"OPERNAMELEN"} . ") NOT NULL,\n";
  288. print " PRIMARY KEY(id)\n";
  289. print ");\n";
  290. print "ALTER TABLE users DROP CONSTRAINT users_pkey CASCADE;\n";
  291. print "ALTER TABLE users ADD UNIQUE(username);\n";
  292. print "ALTER TABLE members ADD FOREIGN KEY (username) REFERENCES users (username) MATCH FULL;\n";
  293. print "ALTER TABLE nicks ADD FOREIGN KEY (username) REFERENCES users (username) MATCH FULL;\n";
  294. print "ALTER TABLE users ADD COLUMN id SERIAL PRIMARY KEY;\n";
  295. print "CREATE TABLE memos (\n";
  296. print " id SERIAL,\n";
  297. print " user_id BIGINT NOT NULL,\n";
  298. print " source_id BIGINT NOT NULL,\n";
  299. print " source VARCHAR(" . $vals{"USERREGNAME_LEN"} . ") NOT NULL,\n";
  300. print " timestamp INTEGER DEFAULT '0',\n";
  301. print " flags INTEGER DEFAULT '0',\n";
  302. print " text TEXT,\n";
  303. print " PRIMARY KEY(id),\n";
  304. print " FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE\n";
  305. print ");\n";
  306. }
  307. else
  308. {
  309. print "ALTER TABLE users ADD COLUMN suspend_time INTEGER DEFAULT '0';\n";
  310. print "ALTER TABLE channels ADD COLUMN suspend_time INTEGER DEFAULT '0';\n";
  311. print "CREATE TABLE ignore_hosts (\n";
  312. print " hostname TEXT NOT NULL,\n";
  313. print " oper TEXT NOT NULL,\n";
  314. print " reason TEXT NOT NULL,\n";
  315. print " PRIMARY KEY(hostname)\n";
  316. print ");\n";
  317. print "CREATE TABLE operbans_regexp (\n";
  318. print " id INTEGER PRIMARY KEY,\n";
  319. print " regex TEXT NOT NULL,\n";
  320. print " reason TEXT NOT NULL,\n";
  321. print " hold INTEGER,\n";
  322. print " create_time INTEGER,\n";
  323. print " oper TEXT NOT NULL\n";
  324. print ");\n";
  325. print "CREATE TABLE operbans_regexp_neg (\n";
  326. print " id INTEGER PRIMARY KEY,\n";
  327. print " parent_id INTEGER NOT NULL,\n";
  328. print " regex TEXT NOT NULL,\n";
  329. print " oper TEXT NOT NULL\n";
  330. print ");\n";
  331. print "CREATE TABLE users_tmpmerge (\n";
  332. print " id INTEGER PRIMARY KEY, username TEXT, password TEXT, email TEXT, suspender TEXT,\n";
  333. print " suspend_reason TEXT, suspend_time INTEGER DEFAULT '0', reg_time INTEGER,\n";
  334. print " last_time INTEGER, flags INTEGER, verify_token TEXT, language TEXT DEFAULT ''\n";
  335. print ");\n";
  336. print "INSERT INTO users_tmpmerge (username, password, email, suspender, suspend_reason,\n";
  337. print "suspend_time, reg_time, last_time, flags, verify_token, language)\n";
  338. print "SELECT username, password, email, suspender, suspend_reason,\n";
  339. print "suspend_time, reg_time, last_time, flags, verify_token, language FROM users;\n";
  340. print "ALTER TABLE users RENAME TO users_pre_dbupgrade;\n";
  341. print "ALTER TABLE users_tmpmerge RENAME TO users;\n";
  342. print "CREATE UNIQUE INDEX users_username_unique ON users (username);\n";
  343. print "CREATE TABLE memos (\n";
  344. print " id INTEGER PRIMARY KEY,\n";
  345. print " user_id INTEGER NOT NULL,\n";
  346. print " source_id INTEGER NOT NULL,\n";
  347. print " source TEXT NOT NULL,\n";
  348. print " timestamp INTEGER DEFAULT '0',\n";
  349. print " flags INTEGER DEFAULT '0',\n";
  350. print " text TEXT\n";
  351. print ");\n";
  352. }
  353. print "\n";
  354. $upgraded = 1;
  355. }
  356. if($upgraded == 0)
  357. {
  358. print "No database modification required.\n";
  359. }
  360. exit;