upgrade_2-0_postgresql.sql 42 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368136913701371137213731374137513761377137813791380
  1. /* ATTENTION: You don't need to run or use this file! The upgrade.php script does everything for you! */
  2. /******************************************************************************/
  3. --- Adding Open ID support.
  4. /******************************************************************************/
  5. ---# Adding Open ID Assocation table...
  6. CREATE TABLE {$db_prefix}openid_assoc (
  7. server_url text NOT NULL,
  8. handle varchar(255) NOT NULL,
  9. secret text NOT NULL,
  10. issued int NOT NULL,
  11. expires int NOT NULL,
  12. assoc_type varchar(64) NOT NULL,
  13. PRIMARY KEY (server_url, handle)
  14. );
  15. ---#
  16. /******************************************************************************/
  17. --- Updating custom fields.
  18. /******************************************************************************/
  19. ---# Adding search ability to custom fields.
  20. ---{
  21. if ($smcFunc['db_server_info'] < 8.0)
  22. {
  23. upgrade_query("
  24. ALTER TABLE {$db_prefix}custom_fields
  25. ADD COLUMN can_search smallint");
  26. upgrade_query("
  27. UPDATE {$db_prefix}custom_fields
  28. SET can_search = 0");
  29. upgrade_query("
  30. ALTER TABLE {$db_prefix}custom_fields
  31. ALTER COLUMN can_search SET NOT NULL");
  32. upgrade_query("
  33. ALTER TABLE {$db_prefix}custom_fields
  34. ALTER COLUMN can_search SET default '0'");
  35. }
  36. else
  37. {
  38. upgrade_query("
  39. ALTER TABLE {$db_prefix}custom_fields
  40. ADD COLUMN can_search smallint NOT NULL default '0'");
  41. }
  42. ---}
  43. ---#
  44. ---# Enhancing privacy settings for custom fields.
  45. ---{
  46. if (isset($modSettings['smfVersion']) && $modSettings['smfVersion'] <= '2.0 Beta 1')
  47. {
  48. upgrade_query("
  49. UPDATE {$db_prefix}custom_fields
  50. SET private = 2
  51. WHERE private = 1");
  52. }
  53. if (isset($modSettings['smfVersion']) && $modSettings['smfVersion'] < '2.0 Beta 4')
  54. {
  55. upgrade_query("
  56. UPDATE {$db_prefix}custom_fields
  57. SET private = 3
  58. WHERE private = 2");
  59. }
  60. ---}
  61. ---#
  62. ---# Changing default_values column to a larger field type...
  63. ALTER TABLE {$db_prefix}custom_fields
  64. ALTER COLUMN default_value TYPE varchar(255);
  65. ---#
  66. ---# Adding new custom fields columns.
  67. ALTER TABLE {$db_prefix}custom_fields
  68. ADD enclose text NOT NULL;
  69. ALTER TABLE {$db_prefix}custom_fields
  70. ADD placement smallint NOT NULL default '0';
  71. ---#
  72. ---# Fixing default value for the "show_profile" column
  73. ALTER TABLE {$db_prefix}custom_fields
  74. ALTER COLUMN show_profile SET DEFAULT 'forumprofile';
  75. UPDATE {$db_prefix}custom_fields
  76. SET show_profile='forumprofile' WHERE show_profile='forumProfile';
  77. ---#
  78. /******************************************************************************/
  79. --- Adding new board specific features.
  80. /******************************************************************************/
  81. ---# Implementing board redirects.
  82. ---{
  83. if ($db_type == 'postgresql' && $smcFunc['db_server_info'] < 8.0)
  84. {
  85. upgrade_query("
  86. ALTER TABLE {$db_prefix}boards
  87. ADD COLUMN redirect varchar(255)");
  88. upgrade_query("
  89. UPDATE {$db_prefix}boards
  90. SET redirect = ''");
  91. upgrade_query("
  92. ALTER TABLE {$db_prefix}boards
  93. ALTER COLUMN redirect SET NOT NULL");
  94. upgrade_query("
  95. ALTER TABLE {$db_prefix}boards
  96. ALTER COLUMN redirect SET default ''");
  97. }
  98. else
  99. {
  100. upgrade_query("
  101. ALTER TABLE {$db_prefix}boards
  102. ADD COLUMN redirect varchar(255) NOT NULL DEFAULT ''");
  103. }
  104. ---}
  105. ---#
  106. /******************************************************************************/
  107. --- Adding search engine tracking.
  108. /******************************************************************************/
  109. ---# Creating spider sequence.
  110. CREATE SEQUENCE {$db_prefix}spiders_seq;
  111. ---#
  112. ---# Creating spider table.
  113. CREATE TABLE {$db_prefix}spiders (
  114. id_spider smallint NOT NULL default nextval('{$db_prefix}spiders_seq'),
  115. spider_name varchar(255) NOT NULL,
  116. user_agent varchar(255) NOT NULL,
  117. ip_info varchar(255) NOT NULL,
  118. PRIMARY KEY (id_spider)
  119. );
  120. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (1, 'Google', 'googlebot', '');
  121. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (2, 'Yahoo!', 'slurp', '');
  122. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (3, 'MSN', 'msnbot', '');
  123. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (4, 'Google (Mobile)', 'Googlebot-Mobile', '');
  124. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (5, 'Google (Image)', 'Googlebot-Image', '');
  125. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (6, 'Google (AdSense)', 'Mediapartners-Google', '');
  126. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (7, 'Google (Adwords)', 'AdsBot-Google', '');
  127. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (8, 'Yahoo! (Mobile)', 'YahooSeeker/M1A1-R2D2', '');
  128. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (9, 'Yahoo! (Image)', 'Yahoo-MMCrawler', '');
  129. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (10, 'MSN (Mobile)', 'MSNBOT_Mobile', '');
  130. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (11, 'MSN (Media)', 'msnbot-media', '');
  131. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (12, 'Cuil', 'twiceler', '');
  132. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (13, 'Ask', 'Teoma', '');
  133. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (14, 'Baidu', 'Baiduspider', '');
  134. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (15, 'Gigablast', 'Gigabot', '');
  135. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (16, 'InternetArchive', 'ia_archiver-web.archive.org', '');
  136. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (17, 'Alexa', 'ia_archiver', '');
  137. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (18, 'Omgili', 'omgilibot', '');
  138. INSERT INTO {$db_prefix}spiders (id_spider, spider_name, user_agent, ip_info) VALUES (19, 'EntireWeb', 'Speedy Spider', '');
  139. ---#
  140. ---# Removing a spider.
  141. ---{
  142. upgrade_query("
  143. DELETE FROM {$db_prefix}spiders
  144. WHERE user_agent = 'yahoo'
  145. AND spider_name = 'Yahoo! (Publisher)'
  146. ");
  147. ---}
  148. ---#
  149. ---# Sequence for table log_spider_hits.
  150. CREATE SEQUENCE {$db_prefix}log_spider_hits_seq;
  151. ---#
  152. ---# Creating spider hit tracking table.
  153. CREATE TABLE {$db_prefix}log_spider_hits (
  154. id_hit int default nextval('{$db_prefix}log_spider_hits_seq'),
  155. id_spider smallint NOT NULL default '0',
  156. log_time int NOT NULL,
  157. url varchar(255) NOT NULL,
  158. processed smallint NOT NULL default '0'
  159. );
  160. CREATE INDEX {$db_prefix}log_spider_hits_id_spider ON {$db_prefix}log_spider_hits (id_spider);
  161. CREATE INDEX {$db_prefix}log_spider_hits_log_time ON {$db_prefix}log_spider_hits (log_time);
  162. CREATE INDEX {$db_prefix}log_spider_hits_processed ON {$db_prefix}log_spider_hits (processed);
  163. ---#
  164. ---# Creating spider statistic table.
  165. CREATE TABLE {$db_prefix}log_spider_stats (
  166. id_spider smallint NOT NULL default '0',
  167. page_hits smallint NOT NULL default '0',
  168. last_seen int NOT NULL default '0',
  169. stat_date date NOT NULL default '0001-01-01',
  170. PRIMARY KEY (stat_date, id_spider)
  171. );
  172. ---#
  173. /******************************************************************************/
  174. --- Adding new forum settings.
  175. /******************************************************************************/
  176. ---# Enable cache if upgrading from 2.0 beta 1 and lower.
  177. ---{
  178. if (isset($modSettings['smfVersion']) && $modSettings['smfVersion'] <= '2.0 Beta 1')
  179. {
  180. $request = upgrade_query("
  181. SELECT value
  182. FROM {$db_prefix}settings
  183. WHERE variable = 'cache_enable'");
  184. list ($cache_enable) = $smcFunc['db_fetch_row']($request);
  185. // No cache before
  186. if ($smcFunc['db_num_rows']($request) == 0)
  187. upgrade_query("
  188. INSERT INTO {$db_prefix}settings
  189. (variable, value)
  190. VALUES ('cache_enable', '1')");
  191. elseif (empty($cache_enable))
  192. upgrade_query("
  193. UPDATE {$db_prefix}settings
  194. SET value = '1'
  195. WHERE variable = 'cache_enable'");
  196. }
  197. ---}
  198. ---#
  199. ---# Ensuring forum width setting present...
  200. ---{
  201. // Don't do this twice!
  202. $smcFunc['db_insert']('ignore',
  203. '{db_prefix}themes',
  204. array('id_theme' => 'int', 'variable' => 'string-255', 'value' => 'string-255'),
  205. array(1, 'forum_width', '90%'),
  206. array('id_theme', 'variable', 'value')
  207. );
  208. ---}
  209. ---#
  210. /******************************************************************************/
  211. --- Adding misc functionality.
  212. /******************************************************************************/
  213. ---# Converting "log_online".
  214. ALTER TABLE {$db_prefix}log_online DROP CONSTRAINT {$db_prefix}log_online_log_time;
  215. ALTER TABLE {$db_prefix}log_online DROP CONSTRAINT {$db_prefix}log_online_id_member;
  216. DROP TABLE {$db_prefix}log_online;
  217. CREATE TABLE {$db_prefix}log_online (
  218. session varchar(32) NOT NULL default '',
  219. log_time int NOT NULL default '0',
  220. id_member int NOT NULL default '0',
  221. id_spider smallint NOT NULL default '0',
  222. ip int NOT NULL default '0',
  223. url text NOT NULL,
  224. PRIMARY KEY (session)
  225. );
  226. CREATE INDEX {$db_prefix}log_online_log_time ON {$db_prefix}log_online (log_time);
  227. CREATE INDEX {$db_prefix}log_online_id_member ON {$db_prefix}log_online (id_member);
  228. ---#
  229. ---# Adding guest voting - part 1...
  230. ---{
  231. if ($smcFunc['db_server_info'] < 8.0)
  232. {
  233. upgrade_query("
  234. ALTER TABLE {$db_prefix}polls
  235. ADD COLUMN guest_vote smallint");
  236. upgrade_query("
  237. UPDATE {$db_prefix}polls
  238. SET guest_vote = 0");
  239. upgrade_query("
  240. ALTER TABLE {$db_prefix}polls
  241. ALTER COLUMN guest_vote SET NOT NULL");
  242. upgrade_query("
  243. ALTER TABLE {$db_prefix}polls
  244. ALTER COLUMN guest_vote SET default '0'");
  245. }
  246. else
  247. {
  248. upgrade_query("
  249. ALTER TABLE {$db_prefix}polls
  250. ADD COLUMN guest_vote smallint NOT NULL default '0'");
  251. }
  252. ---}
  253. ---#
  254. ---# Adding guest voting - part 2...
  255. DELETE FROM {$db_prefix}log_polls
  256. WHERE id_member < 0;
  257. ALTER TABLE {$db_prefix}log_polls DROP CONSTRAINT {$db_prefix}log_polls_pkey;
  258. CREATE INDEX {$db_prefix}log_polls_id_poll ON {$db_prefix}log_polls (id_poll, id_member, id_choice);
  259. ---#
  260. ---# Adding admin log...
  261. ---{
  262. if ($db_type == 'postgresql' && $smcFunc['db_server_info'] < 8.0)
  263. {
  264. upgrade_query("
  265. ALTER TABLE {$db_prefix}log_actions
  266. ADD COLUMN id_log smallint");
  267. upgrade_query("
  268. UPDATE {$db_prefix}log_actions
  269. SET id_log = 1");
  270. upgrade_query("
  271. ALTER TABLE {$db_prefix}log_actions
  272. ALTER COLUMN id_log SET NOT NULL");
  273. upgrade_query("
  274. ALTER TABLE {$db_prefix}log_actions
  275. ALTER COLUMN id_log SET default '1'");
  276. }
  277. else
  278. {
  279. upgrade_query("
  280. ALTER TABLE {$db_prefix}log_actions
  281. ADD COLUMN id_log smallint NOT NULL default '1'");
  282. }
  283. ---}
  284. ---#
  285. ---# Adding search ability to custom fields.
  286. ---{
  287. if ($smcFunc['db_server_info'] < 8.0)
  288. {
  289. upgrade_query("
  290. ALTER TABLE {$db_prefix}members
  291. ADD COLUMN passwd_flood varchar(12)");
  292. upgrade_query("
  293. UPDATE {$db_prefix}members
  294. SET passwd_flood = ''");
  295. upgrade_query("
  296. ALTER TABLE {$db_prefix}members
  297. ALTER COLUMN passwd_flood SET NOT NULL");
  298. upgrade_query("
  299. ALTER TABLE {$db_prefix}members
  300. ALTER COLUMN passwd_flood SET default ''");
  301. }
  302. else
  303. {
  304. upgrade_query("
  305. ALTER TABLE {$db_prefix}members
  306. ADD COLUMN passwd_flood varchar(12) NOT NULL default ''");
  307. }
  308. ---}
  309. ---#
  310. /******************************************************************************/
  311. --- Adding weekly maintenance task.
  312. /******************************************************************************/
  313. ---# Adding weekly maintenance task...
  314. INSERT INTO {$db_prefix}scheduled_tasks (next_time, time_offset, time_regularity, time_unit, disabled, task) VALUES (0, 0, 1, 'w', 0, 'weekly_maintenance');
  315. ---#
  316. ---# Setting the birthday email template if not set...
  317. ---{
  318. if (!isset($modSettings['birthday_email']))
  319. {
  320. upgrade_query("
  321. INSERT INTO {$db_prefix}settings
  322. (variable, value)
  323. VALUES
  324. ('birthday_email', 'happy_birthday')");
  325. }
  326. ---}
  327. ---#
  328. /******************************************************************************/
  329. --- Adding log pruning.
  330. /******************************************************************************/
  331. ---# Adding pruning option...
  332. INSERT INTO {$db_prefix}settings (variable, value) VALUES ('pruningOptions', '30,180,180,180,30');
  333. ---#
  334. /******************************************************************************/
  335. --- Updating mail queue functionality.
  336. /******************************************************************************/
  337. ---# Adding private to mail queue...
  338. ---{
  339. if ($smcFunc['db_server_info'] < 8.0)
  340. {
  341. upgrade_query("
  342. ALTER TABLE {$db_prefix}mail_queue
  343. ADD COLUMN private smallint");
  344. upgrade_query("
  345. UPDATE {$db_prefix}mail_queue
  346. SET private = 0");
  347. upgrade_query("
  348. ALTER TABLE {$db_prefix}mail_queue
  349. ALTER COLUMN private SET NOT NULL");
  350. upgrade_query("
  351. ALTER TABLE {$db_prefix}mail_queue
  352. ALTER COLUMN private SET default '0'");
  353. }
  354. else
  355. {
  356. upgrade_query("
  357. ALTER TABLE {$db_prefix}mail_queue
  358. ADD COLUMN private smallint NOT NULL default '0'");
  359. }
  360. ---}
  361. ---#
  362. /******************************************************************************/
  363. --- Updating attachments.
  364. /******************************************************************************/
  365. ---# Adding multiple attachment path functionality.
  366. ---{
  367. if ($smcFunc['db_server_info'] < 8.0)
  368. {
  369. upgrade_query("
  370. ALTER TABLE {$db_prefix}attachments
  371. ADD COLUMN id_folder smallint");
  372. upgrade_query("
  373. UPDATE {$db_prefix}attachments
  374. SET id_folder = 1");
  375. upgrade_query("
  376. ALTER TABLE {$db_prefix}attachments
  377. ALTER COLUMN id_folder SET NOT NULL");
  378. upgrade_query("
  379. ALTER TABLE {$db_prefix}attachments
  380. ALTER COLUMN id_folder SET default '1'");
  381. }
  382. else
  383. {
  384. upgrade_query("
  385. ALTER TABLE {$db_prefix}attachments
  386. ADD COLUMN id_folder smallint NOT NULL default '1'");
  387. }
  388. ---}
  389. ---#
  390. ---# Adding file hash.
  391. ---{
  392. upgrade_query("
  393. ALTER TABLE {$db_prefix}attachments
  394. ADD COLUMN file_hash varchar(40) NOT NULL default ''");
  395. ---}
  396. ---#
  397. /******************************************************************************/
  398. --- Adding restore topic from recycle.
  399. /******************************************************************************/
  400. ---# Adding restore topic from recycle feature...
  401. ---{
  402. if ($db_type == 'postgresql' && $smcFunc['db_server_info'] < 8.0)
  403. {
  404. upgrade_query("
  405. ALTER TABLE {$db_prefix}topics
  406. ADD COLUMN id_previous_board smallint");
  407. upgrade_query("
  408. ALTER TABLE {$db_prefix}topics
  409. ADD COLUMN id_previous_topic int");
  410. upgrade_query("
  411. UPDATE {$db_prefix}topics
  412. SET
  413. id_previous_board = 0,
  414. id_previous_topic = 0");
  415. upgrade_query("
  416. ALTER TABLE {$db_prefix}topics
  417. ALTER COLUMN id_previous_board SET NOT NULL");
  418. upgrade_query("
  419. ALTER TABLE {$db_prefix}topics
  420. ALTER COLUMN id_previous_topic SET NOT NULL");
  421. upgrade_query("
  422. ALTER TABLE {$db_prefix}topics
  423. ALTER COLUMN id_previous_board SET default '0'");
  424. upgrade_query("
  425. ALTER TABLE {$db_prefix}topics
  426. ALTER COLUMN id_previous_topic SET default '0'");
  427. }
  428. else
  429. {
  430. upgrade_query("
  431. ALTER TABLE {$db_prefix}topics
  432. ADD COLUMN id_previous_board smallint NOT NULL default '0'");
  433. upgrade_query("
  434. ALTER TABLE {$db_prefix}topics
  435. ADD COLUMN id_previous_topic int NOT NULL default '0'");
  436. }
  437. ---}
  438. ---#
  439. /******************************************************************************/
  440. --- Making changes to the package manager.
  441. /******************************************************************************/
  442. ---# Changing URL to SMF package server...
  443. UPDATE {$db_prefix}package_servers
  444. SET url = 'http://custom.simplemachines.org/packages/mods'
  445. WHERE url = 'http://mods.simplemachines.org';
  446. ---#
  447. /******************************************************************************/
  448. --- Adding new indexes to the topics table.
  449. /******************************************************************************/
  450. ---# Adding index member_started...
  451. CREATE INDEX {$db_prefix}topics_member_started ON {$db_prefix}topics (id_member_started, id_board);
  452. ---#
  453. ---# Adding index last_message_sticky...
  454. CREATE INDEX {$db_prefix}topics_last_message_sticky ON {$db_prefix}topics (id_board, is_sticky, id_last_msg);
  455. ---#
  456. ---# Adding index board_news...
  457. CREATE INDEX {$db_prefix}topics_board_news ON {$db_prefix}topics (id_board, id_first_msg);
  458. ---#
  459. /******************************************************************************/
  460. --- Adding new indexes to members table.
  461. /******************************************************************************/
  462. ---# Adding index on total_time_logged_in...
  463. CREATE INDEX {$db_prefix}members_total_time_logged_in ON {$db_prefix}members (total_time_logged_in);
  464. ---#
  465. ---# Adding index on id_theme...
  466. CREATE INDEX {$db_prefix}members_id_theme ON {$db_prefix}members (id_theme);
  467. ---#
  468. ---# Adding index on real_name...
  469. CREATE INDEX {$db_prefix}members_real_name ON {$db_prefix}members (real_name);
  470. ---#
  471. /******************************************************************************/
  472. --- Adding new indexes to messages table.
  473. /******************************************************************************/
  474. ---# Adding index id_member_msg...
  475. CREATE INDEX {$db_prefix}messages_id_member_msg ON {$db_prefix}messages (id_member, approved, id_msg);
  476. ---#
  477. ---# Adding index current_topic...
  478. CREATE INDEX {$db_prefix}messages_current_topic ON {$db_prefix}messages (id_topic, id_msg, id_member, approved);
  479. ---#
  480. ---# Adding index related_ip...
  481. CREATE INDEX {$db_prefix}messages_related_ip ON {$db_prefix}messages (id_member, poster_ip, id_msg);
  482. ---#
  483. /******************************************************************************/
  484. --- Adding new indexes to attachments table.
  485. /******************************************************************************/
  486. ---# Adding index on attachment_type...
  487. CREATE INDEX {$db_prefix}attachments_attachment_type ON {$db_prefix}attachments (attachment_type);
  488. ---#
  489. /******************************************************************************/
  490. --- Providing more room for ignoring boards.
  491. /******************************************************************************/
  492. ---# Changing ignore_boards column to a larger field type...
  493. ALTER TABLE {$db_prefix}members
  494. ALTER COLUMN ignore_boards TYPE text;
  495. ---#
  496. /******************************************************************************/
  497. --- Adding default values to a couple of columns in log_subscribed
  498. /******************************************************************************/
  499. ---# Adding default value for pending_details column
  500. ALTER TABLE {$db_prefix}log_subscribed
  501. ALTER COLUMN pending_details
  502. SET DEFAULT '';
  503. ---#
  504. ---# Adding default value for vendor_ref column
  505. ALTER TABLE {$db_prefix}log_subscribed
  506. ALTER COLUMN vendor_ref
  507. SET DEFAULT '';
  508. ---#
  509. /*****************************************************************************/
  510. --- Fixing aim on members for longer nicks.
  511. /*****************************************************************************/
  512. ---# Changing 'aim' to varchar to allow using email...
  513. ALTER TABLE {$db_prefix}members
  514. ALTER COLUMN aim TYPE varchar(255);
  515. ALTER TABLE {$db_prefix}members
  516. ALTER COLUMN aim SET DEFAULT '';
  517. ---#
  518. /*****************************************************************************/
  519. --- Fixing column types in log_errors
  520. /*****************************************************************************/
  521. ---# Changing 'ip' from char to varchar
  522. ALTER TABLE {$db_prefix}log_errors
  523. ALTER COLUMN ip TYPE varchar(16);
  524. ALTER TABLE {$db_prefix}log_errors
  525. ALTER COLUMN ip SET DEFAULT '';
  526. ---#
  527. ---# Changing 'error_type' from char to varchar
  528. ALTER TABLE {$db_prefix}log_errors
  529. ALTER COLUMN error_type TYPE varchar(15);
  530. ---#
  531. /******************************************************************************/
  532. --- Allow for longer calendar event/holiday titles.
  533. /******************************************************************************/
  534. ---# Changing event title column to a larger field type...
  535. ALTER TABLE {$db_prefix}calendar
  536. ALTER COLUMN title TYPE varchar(255);
  537. ---#
  538. ---# Changing holiday title column to a larger field type...
  539. ALTER TABLE {$db_prefix}calendar_holidays
  540. ALTER COLUMN title TYPE varchar(255);
  541. ---#
  542. /******************************************************************************/
  543. --- Providing more room for apf options.
  544. /******************************************************************************/
  545. ---# Changing field_options column to a larger field type...
  546. ALTER TABLE {$db_prefix}custom_fields
  547. ALTER COLUMN field_options TYPE text;
  548. ---#
  549. /******************************************************************************/
  550. --- Adding extra columns to polls.
  551. /******************************************************************************/
  552. ---# Adding reset poll timestamp and guest voters counter.
  553. ---{
  554. if ($smcFunc['db_server_info'] < 8.0)
  555. {
  556. upgrade_query("
  557. ALTER TABLE {$db_prefix}polls
  558. ADD COLUMN reset_poll int");
  559. upgrade_query("
  560. UPDATE {$db_prefix}polls
  561. SET reset_poll = '0'
  562. WHERE reset_poll < 1");
  563. upgrade_query("
  564. ALTER TABLE {$db_prefix}polls
  565. ALTER COLUMN reset_poll SET NOT NULL");
  566. upgrade_query("
  567. ALTER TABLE {$db_prefix}polls
  568. ALTER COLUMN reset_poll SET default '0'");
  569. upgrade_query("
  570. ALTER TABLE {$db_prefix}polls
  571. ADD COLUMN num_guest_voters int");
  572. upgrade_query("
  573. UPDATE {$db_prefix}polls
  574. SET num_guest_voters = '0'
  575. WHERE num_guest_voters < 1");
  576. upgrade_query("
  577. ALTER TABLE {$db_prefix}polls
  578. ALTER COLUMN num_guest_voters SET NOT NULL");
  579. upgrade_query("
  580. ALTER TABLE {$db_prefix}polls
  581. ALTER COLUMN num_guest_voters SET default '0'");
  582. }
  583. else
  584. {
  585. upgrade_query("
  586. ALTER TABLE {$db_prefix}polls
  587. ADD COLUMN reset_poll int NOT NULL default '0'");
  588. upgrade_query("
  589. ALTER TABLE {$db_prefix}polls
  590. ADD COLUMN num_guest_voters int NOT NULL default '0'");
  591. }
  592. ---}
  593. ---#
  594. ---# Fixing guest voter tallys on existing polls...
  595. ---{
  596. $request = upgrade_query("
  597. SELECT p.id_poll, count(lp.id_member) as guest_voters
  598. FROM {$db_prefix}polls AS p
  599. LEFT JOIN {$db_prefix}log_polls AS lp ON (lp.id_poll = p.id_poll AND lp.id_member = 0)
  600. WHERE lp.id_member = 0
  601. AND p.num_guest_voters = 0
  602. GROUP BY p.id_poll");
  603. while ($request && $row = $smcFunc['db_fetch_assoc']($request))
  604. upgrade_query("
  605. UPDATE {$db_prefix}polls
  606. SET num_guest_voters = ". $row['guest_voters']. "
  607. WHERE id_poll = " . $row['id_poll'] . "
  608. AND num_guest_voters = 0");
  609. ---}
  610. ---#
  611. /*****************************************************************************/
  612. --- Fixing a bug with the inet_aton() function.
  613. /*****************************************************************************/
  614. ---# Changing inet_aton function to use bigint instead of int...
  615. CREATE OR REPLACE FUNCTION INET_ATON(text) RETURNS bigint AS
  616. 'SELECT
  617. CASE WHEN
  618. $1 !~ ''^[0-9]?[0-9]?[0-9]?\.[0-9]?[0-9]?[0-9]?\.[0-9]?[0-9]?[0-9]?\.[0-9]?[0-9]?[0-9]?$'' THEN 0
  619. ELSE
  620. split_part($1, ''.'', 1)::int8 * (256 * 256 * 256) +
  621. split_part($1, ''.'', 2)::int8 * (256 * 256) +
  622. split_part($1, ''.'', 3)::int8 * 256 +
  623. split_part($1, ''.'', 4)::int8
  624. END AS result'
  625. LANGUAGE 'sql';
  626. ---#
  627. /*****************************************************************************/
  628. --- Making additional changes to handle results from fixed inet_aton().
  629. /*****************************************************************************/
  630. ---# Adding an IFNULL to handle 8-bit integers returned by inet_aton
  631. CREATE OR REPLACE FUNCTION IFNULL(int8, int8) RETURNS int8 AS
  632. 'SELECT COALESCE($1, $2) AS result'
  633. LANGUAGE 'sql';
  634. ---#
  635. ---# Changing ip column in log_online to int8
  636. ALTER TABLE {$db_prefix}log_online
  637. ALTER COLUMN ip TYPE int8;
  638. ---#
  639. /******************************************************************************/
  640. --- Dropping unnecessary indexes...
  641. /******************************************************************************/
  642. ---# Removing index on hits...
  643. ---{
  644. $smcFunc['db_remove_index']($db_prefix . 'log_activity', $db_prefix . 'log_activity_hits');
  645. ---}
  646. ---#
  647. /******************************************************************************/
  648. --- Adding new personal message setting.
  649. /******************************************************************************/
  650. ---# Adding column that stores the PM receiving setting...
  651. ---{
  652. upgrade_query("
  653. ALTER TABLE {$db_prefix}members
  654. ADD COLUMN pm_receive_from smallint NOT NULL default '1'");
  655. ---}
  656. ---#
  657. ---# Enable the buddy and ignore lists if we have not done so thus far...
  658. ---{
  659. // Don't do this if we've done this already.
  660. if (empty($modSettings['dont_repeat_buddylists']))
  661. {
  662. // Make sure the pm_receive_from column has the right default value - early adoptors might have a '0' set here.
  663. upgrade_query("
  664. ALTER TABLE {$db_prefix}members
  665. ALTER COLUMN pm_receive_from SET DEFAULT '1'");
  666. // Update previous ignore lists if they're set to ignore all.
  667. upgrade_query("
  668. UPDATE {$db_prefix}members
  669. SET pm_receive_from = 3, pm_ignore_list = ''
  670. WHERE pm_ignore_list = '*'");
  671. // Enable buddy and ignore lists.
  672. $smcFunc['db_insert']('replace',
  673. '{db_prefix}settings',
  674. array('variable' => 'string-255', 'value' => 'string-255'),
  675. array('enable_buddylist', '1'),
  676. array('variable', 'value')
  677. );
  678. // Ignore posts made by ignored users by default, too.
  679. $smcFunc['db_insert']('replace',
  680. '{db_prefix}themes',
  681. array('id_member' => 'int', 'id_theme' => 'int', 'variable' => 'string-255', 'value' => 'string-255'),
  682. array(-1, 1, 'posts_apply_ignore_list', '1'),
  683. array('id_member', 'id_theme', 'variable', 'value')
  684. );
  685. // Make sure not to skip this step next time we run this.
  686. $smcFunc['db_insert']('replace',
  687. '{db_prefix}settings',
  688. array('variable' => 'string-255', 'value' => 'string-255'),
  689. array('dont_repeat_buddylists', '1'),
  690. array('variable', 'value')
  691. );
  692. }
  693. // And yet, and yet... We might have a small hiccup here...
  694. if (!empty($modSettings['dont_repeat_buddylists']) && !isset($modSettings['enable_buddylist']))
  695. {
  696. // Correct RC3 adopters setting here...
  697. if (isset($modSettings['enable_buddylists']))
  698. {
  699. $smcFunc['db_insert']('replace',
  700. '{db_prefix}settings',
  701. array('variable' => 'string-255', 'value' => 'string-255'),
  702. array('enable_buddylist', $modSettings['enable_buddylists']),
  703. array('variable', 'value')
  704. );
  705. }
  706. else
  707. {
  708. // This should never happen :)
  709. $smcFunc['db_insert']('replace',
  710. '{db_prefix}settings',
  711. array('variable' => 'string-255', 'value' => 'string-255'),
  712. array('enable_buddylist', '1'),
  713. array('variable', 'value')
  714. );
  715. }
  716. }
  717. ---}
  718. ---#
  719. /******************************************************************************/
  720. --- Adding settings for attachments and avatars.
  721. /******************************************************************************/
  722. ---# Add new security settings for attachments and avatars...
  723. ---{
  724. // Don't do this if we've done this already.
  725. if (!isset($modSettings['attachment_image_reencode']))
  726. {
  727. // Enable image re-encoding by default.
  728. $smcFunc['db_insert']('replace',
  729. '{db_prefix}settings',
  730. array('variable' => 'string-255', 'value' => 'string-255'),
  731. array('attachment_image_reencode', '1'),
  732. array('variable', 'value')
  733. );
  734. }
  735. if (!isset($modSettings['attachment_image_paranoid']))
  736. {
  737. // Disable draconic checks by default.
  738. $smcFunc['db_insert']('replace',
  739. '{db_prefix}settings',
  740. array('variable' => 'string-255', 'value' => 'string-255'),
  741. array('attachment_image_paranoid', '0'),
  742. array('variable', 'value')
  743. );
  744. }
  745. if (!isset($modSettings['avatar_reencode']))
  746. {
  747. // Enable image re-encoding by default.
  748. $smcFunc['db_insert']('replace',
  749. '{db_prefix}settings',
  750. array('variable' => 'string-255', 'value' => 'string-255'),
  751. array('avatar_reencode', '1'),
  752. array('variable', 'value')
  753. );
  754. }
  755. if (!isset($modSettings['avatar_paranoid']))
  756. {
  757. // Disable draconic checks by default.
  758. $smcFunc['db_insert']('replace',
  759. '{db_prefix}settings',
  760. array('variable' => 'string-255', 'value' => 'string-255'),
  761. array('avatar_paranoid', '0'),
  762. array('variable', 'value')
  763. );
  764. }
  765. ---}
  766. ---#
  767. ---# Add other attachment settings...
  768. ---{
  769. if (!isset($modSettings['attachment_thumb_png']))
  770. {
  771. // Make image attachment thumbnail as PNG by default.
  772. $smcFunc['db_insert']('replace',
  773. '{db_prefix}settings',
  774. array('variable' => 'string-255', 'value' => 'string-255'),
  775. array('attachment_thumb_png', '1'),
  776. array('variable', 'value')
  777. );
  778. }
  779. ---}
  780. ---#
  781. /******************************************************************************/
  782. --- Installing new default theme...
  783. /******************************************************************************/
  784. ---# Installing theme settings...
  785. ---{
  786. // This is Grudge's secret "I'm not a developer" theme install code - keep this quiet ;)
  787. // Firstly, I'm going out of my way to not do this twice!
  788. if ((!isset($modSettings['smfVersion']) || $modSettings['smfVersion'] <= '2.0 RC2') && empty($modSettings['dont_repeat_theme_core']))
  789. {
  790. // Check it's not already here, just in case.
  791. $theme_request = upgrade_query("
  792. SELECT id_theme
  793. FROM {$db_prefix}themes
  794. WHERE variable = 'theme_dir'
  795. AND value LIKE '%core'");
  796. // Only do the upgrade if it doesn't find the theme already.
  797. if ($smcFunc['db_num_rows']($theme_request) == 0)
  798. {
  799. // Try to get some settings from the current default theme.
  800. $request = upgrade_query("
  801. SELECT t1.value AS theme_dir, t2.value AS theme_url, t3.value AS images_url
  802. FROM ({$db_prefix}themes AS t1, {$db_prefix}themes AS t2, {$db_prefix}themes AS t3)
  803. WHERE t1.id_theme = 1
  804. AND t1.id_member = 0
  805. AND t1.variable = 'theme_dir'
  806. AND t2.id_theme = 1
  807. AND t2.id_member = 0
  808. AND t2.variable = 'theme_url'
  809. AND t3.id_theme = 1
  810. AND t3.id_member = 0
  811. AND t3.variable = 'images_url'
  812. LIMIT 1");
  813. if ($smcFunc['db_num_rows']($request) != 0)
  814. {
  815. $curve = $smcFunc['db_fetch_assoc']($request);
  816. if (substr_count($curve['theme_dir'], 'default') === 1)
  817. $core['theme_dir'] = strtr($curve['theme_dir'], array('default' => 'core'));
  818. if (substr_count($curve['theme_url'], 'default') === 1)
  819. $core['theme_url'] = strtr($curve['theme_url'], array('default' => 'core'));
  820. if (substr_count($curve['images_url'], 'default') === 1)
  821. $core['images_url'] = strtr($curve['images_url'], array('default' => 'core'));
  822. }
  823. $smcFunc['db_free_result']($request);
  824. if (!isset($core['theme_dir']))
  825. $core['theme_dir'] = addslashes($GLOBALS['boarddir']) . '/Themes/core';
  826. if (!isset($core['theme_url']))
  827. $core['theme_url'] = $GLOBALS['boardurl'] . '/Themes/core';
  828. if (!isset($core['images_url']))
  829. $core['images_url'] = $GLOBALS['boardurl'] . '/Themes/core/images';
  830. // Get an available id_theme first...
  831. $request = upgrade_query("
  832. SELECT MAX(id_theme) + 1
  833. FROM {$db_prefix}themes");
  834. list ($id_core_theme) = $smcFunc['db_fetch_row']($request);
  835. $smcFunc['db_free_result']($request);
  836. // Insert the core theme into the tables.
  837. $smcFunc['db_insert']('ignore',
  838. '{db_prefix}themes',
  839. array('id_member' => 'int', 'id_theme' => 'int', 'variable' => 'string-255', 'value' => 'string-255'),
  840. array(
  841. array(0, $id_core_theme, 'name', 'Core Theme'),
  842. array(0, $id_core_theme, 'theme_url', $core['theme_url']),
  843. array(0, $id_core_theme, 'images_url', $core['images_url']),
  844. array(0, $id_core_theme, 'theme_dir', $core['theme_dir'])
  845. ),
  846. array()
  847. );
  848. // Update the name of the default theme in the database.
  849. upgrade_query("
  850. UPDATE {$db_prefix}themes
  851. SET value = 'SMF Default Theme - Curve'
  852. WHERE id_theme = 1
  853. AND variable = 'name'");
  854. $newSettings = array();
  855. // Now that we have the old theme details - switch anyone who used the default to it (Make sense?!)
  856. if (!empty($modSettings['theme_default']) && $modSettings['theme_default'] == 1)
  857. $newSettings[] = "('theme_default', $id_core_theme)";
  858. // Did guests use to use the default?
  859. if (!empty($modSettings['theme_guests']) && $modSettings['theme_guests'] == 1)
  860. $newSettings[] = "('theme_guests', $id_core_theme)";
  861. // If known themes aren't set, let's just pick all themes available.
  862. if (empty($modSettings['knownThemes']))
  863. {
  864. $request = upgrade_query("
  865. SELECT DISTINCT id_theme
  866. FROM {$db_prefix}themes");
  867. $themes = array();
  868. while ($row = $smcFunc['db_fetch_assoc']($request))
  869. $themes[] = $row['id_theme'];
  870. $modSettings['knownThemes'] = implode(',', $themes);
  871. upgrade_query("
  872. UPDATE {$db_prefix}settings
  873. SET value = '$modSettings[knownThemes]'
  874. WHERE variable = 'knownThemes'");
  875. }
  876. // Known themes.
  877. $allThemes = explode(',', $modSettings['knownThemes']);
  878. $allThemes[] = $id_core_theme;
  879. $newSettings[] = "('knownThemes', '" . implode(',', $allThemes) . "')";
  880. // Since we want to do a replace, just delete the old settings and re-insert them
  881. upgrade_query("
  882. DELETE FROM {$db_prefix}settings
  883. WHERE variable IN ('theme_default', 'theme_guests', 'knownThemes')");
  884. foreach ($new_settings AS $a_new_setting)
  885. {
  886. upgrade_query("
  887. INSERT INTO {$db_prefix}settings
  888. (variable, value)
  889. VALUES " . implode(', ', $a_new_setting));
  890. }
  891. // What about members?
  892. upgrade_query("
  893. UPDATE {$db_prefix}members
  894. SET id_theme = $id_core_theme
  895. WHERE id_theme = 1");
  896. // Boards?
  897. upgrade_query("
  898. UPDATE {$db_prefix}boards
  899. SET id_theme = $id_core_theme
  900. WHERE id_theme = 1");
  901. // The other themes used to use core as their base theme.
  902. if (isset($core['theme_dir']) && isset($core['theme_url']))
  903. {
  904. $coreBasedThemes = array_diff($allThemes, array(1));
  905. // Exclude the themes that already have a base_theme_dir.
  906. $request = upgrade_query("
  907. SELECT DISTINCT id_theme
  908. FROM {$db_prefix}themes
  909. WHERE variable = 'base_theme_dir'");
  910. while ($row = $smcFunc['db_fetch_assoc']($request))
  911. $coreBasedThemes = array_diff($coreBasedThemes, array($row['id_theme']));
  912. $smcFunc['db_free_result']($request);
  913. // Only base themes if there are templates that need a fall-back.
  914. $insertRows = array();
  915. $request = upgrade_query("
  916. SELECT id_theme, value AS theme_dir
  917. FROM {$db_prefix}themes
  918. WHERE id_theme IN (" . implode(', ', $coreBasedThemes) . ")
  919. AND id_member = 0
  920. AND variable = 'theme_dir'");
  921. while ($row = $smcFunc['db_fetch_assoc']($request))
  922. {
  923. if (!file_exists($row['theme_dir'] . '/BoardIndex.template.php') || !file_exists($row['theme_dir'] . '/Display.template.php') || !file_exists($row['theme_dir'] . '/index.template.php') || !file_exists($row['theme_dir'] . '/MessageIndex.template.php') || !file_exists($row['theme_dir'] . '/Settings.template.php'))
  924. {
  925. $insertRows[] = "(0, $row[id_theme], 'base_theme_dir', '" . addslashes($core['theme_dir']) . "')";
  926. $insertRows[] = "(0, $row[id_theme], 'base_theme_url', '" . addslashes($core['theme_url']) . "')";
  927. }
  928. }
  929. $smcFunc['db_free_result']($request);
  930. if (!empty($insertRows))
  931. upgrade_query("
  932. INSERT IGNORE INTO {$db_prefix}themes
  933. (id_member, id_theme, variable, value)
  934. VALUES
  935. " . implode(',
  936. ', $insertRows));
  937. }
  938. }
  939. $smcFunc['db_free_result']($theme_request);
  940. // This ain't running twice either - not with the risk of log_tables timing us all out!
  941. $smcFunc['db_insert']('replace',
  942. '{db_prefix}settings',
  943. array('variable' => 'string-255', 'value' => 'string-255'),
  944. array('dont_repeat_theme_core', '1'),
  945. array('variable', 'value')
  946. );
  947. }
  948. ---}
  949. ---#
  950. /******************************************************************************/
  951. --- Installing new smileys sets...
  952. /******************************************************************************/
  953. ---# Installing new smiley sets...
  954. ---{
  955. // Don't do this twice!
  956. if (empty($modSettings['installed_new_smiley_sets_20']))
  957. {
  958. // First, the entries.
  959. upgrade_query("
  960. UPDATE {$db_prefix}settings
  961. SET value = CONCAT(value, ',aaron,akyhne')
  962. WHERE variable = 'smiley_sets_known'");
  963. // Second, the names.
  964. upgrade_query("
  965. UPDATE {$db_prefix}settings
  966. SET value = CONCAT(value, '\nAaron\nAkyhne')
  967. WHERE variable = 'smiley_sets_names'");
  968. // This ain't running twice either.
  969. $smcFunc['db_insert']('replace',
  970. '{db_prefix}settings',
  971. array('variable' => 'string-255', 'value' => 'string-255'),
  972. array('installed_new_smiley_sets_20', '1'),
  973. array('variable', 'value')
  974. );
  975. }
  976. ---}
  977. ---#
  978. /*****************************************************************************/
  979. --- Adding additional functions
  980. /*****************************************************************************/
  981. ---# Adding instr()
  982. ---{
  983. if ($smcFunc['db_server_info'] < 8.2)
  984. {
  985. $request = upgrade_query("
  986. SELECT type_udt_name
  987. FROM information_schema.routines
  988. WHERE routine_name = 'inet_aton'
  989. ");
  990. // Assume there's only one such function called inet_aton()
  991. $return_type = $smcFunc['db_fetch_assoc']($request);
  992. // No point in dropping and recreating it if it's already what we want
  993. if ($return_type['type_udt_name'] != 'int4')
  994. {
  995. upgrade_query("
  996. DROP FUNCTION INSTR(text, text)");
  997. }
  998. }
  999. else
  1000. {
  1001. upgrade_query("
  1002. DROP FUNCTION IF EXISTS INSTR(text, text)");
  1003. }
  1004. ---}
  1005. CREATE OR REPLACE FUNCTION INSTR(text, text) RETURNS integer AS
  1006. 'SELECT POSITION($2 IN $1) AS result'
  1007. LANGUAGE 'sql';
  1008. ---#
  1009. ---# Adding date_format()
  1010. CREATE OR REPLACE FUNCTION DATE_FORMAT (timestamp, text) RETURNS text AS
  1011. 'SELECT
  1012. REPLACE(
  1013. REPLACE($2, ''%m'', to_char($1, ''MM'')),
  1014. ''%d'', to_char($1, ''DD'')) AS result'
  1015. LANGUAGE 'sql';
  1016. ---#
  1017. ---# Adding day()
  1018. CREATE OR REPLACE FUNCTION day(date) RETURNS integer AS
  1019. 'SELECT EXTRACT(DAY FROM DATE($1))::integer AS result'
  1020. LANGUAGE 'sql';
  1021. ---#
  1022. ---# Adding IFNULL(varying, varying)
  1023. CREATE OR REPLACE FUNCTION IFNULL (character varying, character varying) RETURNS character varying AS
  1024. 'SELECT COALESCE($1, $2) AS result'
  1025. LANGUAGE 'sql';
  1026. ---#
  1027. ---# Adding IFNULL(varying, bool)
  1028. CREATE OR REPLACE FUNCTION IFNULL(character varying, boolean) RETURNS character varying AS
  1029. 'SELECT COALESCE($1, CAST(CAST($2 AS int) AS varchar)) AS result'
  1030. LANGUAGE 'sql';
  1031. ---#
  1032. ---# Adding IFNULL(int, bool)
  1033. CREATE OR REPLACE FUNCTION IFNULL(int, boolean) RETURNS int AS
  1034. 'SELECT COALESCE($1, CAST($2 AS int)) AS result'
  1035. LANGUAGE 'sql';
  1036. ---#
  1037. ---# Adding bool_not_eq_int()
  1038. CREATE OR REPLACE FUNCTION bool_not_eq_int (boolean, integer) RETURNS boolean AS
  1039. 'SELECT CAST($1 AS integer) != $2 AS result'
  1040. LANGUAGE 'sql';
  1041. ---#
  1042. ---# Creating operator bool_not_eq_int()
  1043. ---{
  1044. $result = upgrade_query("SELECT oprname FROM pg_operator WHERE oprcode='bool_not_eq_int'::regproc");
  1045. if($smcFunc['db_num_rows']($result) == 0)
  1046. {
  1047. upgrade_query("
  1048. CREATE OPERATOR != (PROCEDURE = bool_not_eq_int, LEFTARG = boolean, RIGHTARG = integer)");
  1049. }
  1050. ---}
  1051. ---#
  1052. ---# Recreating function FIND_IN_SET()
  1053. ---{
  1054. if ($smcFunc['db_server_info'] < 8.2)
  1055. {
  1056. $query = upgrade_query("SELECT * FROM pg_proc WHERE proname = 'find_in_set' AND proargtypes = '25 25'");
  1057. if ($smcFunc['db_num_rows']($query) != 0)
  1058. {
  1059. upgrade_query("DROP FUNCTION FIND_IN_SET(text, text)");
  1060. }
  1061. $query = upgrade_query("SELECT * FROM pg_proc WHERE proname = 'find_in_set' AND proargtypes = '23 1043'");
  1062. if ($smcFunc['db_num_rows']($query) != 0)
  1063. {
  1064. upgrade_query("DROP FUNCTION FIND_IN_SET(integer, character varying)");
  1065. }
  1066. }
  1067. else
  1068. {
  1069. upgrade_query("DROP FUNCTION IF EXISTS FIND_IN_SET(text, text)");
  1070. upgrade_query("DROP FUNCTION IF EXISTS FIND_IN_SET(integer, character varying)");
  1071. }
  1072. ---}
  1073. CREATE OR REPLACE FUNCTION FIND_IN_SET(needle text, haystack text) RETURNS integer AS '
  1074. SELECT i AS result
  1075. FROM generate_series(1, array_upper(string_to_array($2,'',''), 1)) AS g(i)
  1076. WHERE (string_to_array($2,'',''))[i] = $1
  1077. UNION ALL
  1078. SELECT 0
  1079. LIMIT 1'
  1080. LANGUAGE 'sql';
  1081. CREATE OR REPLACE FUNCTION FIND_IN_SET(needle integer, haystack text) RETURNS integer AS '
  1082. SELECT i AS result
  1083. FROM generate_series(1, array_upper(string_to_array($2,'',''), 1)) AS g(i)
  1084. WHERE (string_to_array($2,'',''))[i] = CAST($1 AS text)
  1085. UNION ALL
  1086. SELECT 0
  1087. LIMIT 1'
  1088. LANGUAGE 'sql';
  1089. ---#
  1090. CREATE OR REPLACE FUNCTION DATE_FORMAT (timestamp, text) RETURNS text AS
  1091. 'SELECT
  1092. REPLACE(
  1093. REPLACE($2, ''%m'', to_char($1, ''MM'')),
  1094. ''%d'', to_char($1, ''DD'')) AS result'
  1095. LANGUAGE 'sql';
  1096. ---# Updating TO_DAYS()
  1097. CREATE OR REPLACE FUNCTION TO_DAYS (timestamp) RETURNS integer AS
  1098. 'SELECT DATE_PART(''DAY'', $1 - ''0001-01-01bc'')::integer AS result'
  1099. LANGUAGE 'sql';
  1100. ---#
  1101. /******************************************************************************/
  1102. --- Adding extra columns to reported post comments
  1103. /******************************************************************************/
  1104. ---# Adding email address and member ip columns...
  1105. ---{
  1106. if ($smcFunc['db_server_info'] < 8.0)
  1107. {
  1108. upgrade_query("
  1109. ALTER TABLE {$db_prefix}log_reported_comments
  1110. ADD COLUMN email_address varchar(255)");
  1111. upgrade_query("
  1112. UPDATE {$db_prefix}log_reported_comments
  1113. SET email_address = ''");
  1114. upgrade_query("
  1115. ALTER TABLE {$db_prefix}log_reported_comments
  1116. ALTER COLUMN email_address SET NOT NULL");
  1117. upgrade_query("
  1118. ALTER TABLE {$db_prefix}log_reported_comments
  1119. ALTER COLUMN email_address SET default ''");
  1120. upgrade_query("
  1121. ALTER TABLE {$db_prefix}log_reported_comments
  1122. ADD COLUMN member_ip varchar(255)");
  1123. upgrade_query("
  1124. UPDATE {$db_prefix}log_reported_comments
  1125. SET member_ip = ''");
  1126. upgrade_query("
  1127. ALTER TABLE {$db_prefix}log_reported_comments
  1128. ALTER COLUMN member_ip SET NOT NULL");
  1129. upgrade_query("
  1130. ALTER TABLE {$db_prefix}log_reported_comments
  1131. ALTER COLUMN member_ip SET default ''");
  1132. }
  1133. else
  1134. {
  1135. upgrade_query("
  1136. ALTER TABLE {$db_prefix}log_reported_comments
  1137. ADD COLUMN email_address varchar(255) NOT NULL default ''");
  1138. upgrade_query("
  1139. ALTER TABLE {$db_prefix}log_reported_comments
  1140. ADD COLUMN member_ip varchar(255) NOT NULL default ''");
  1141. }
  1142. ---}
  1143. ---#
  1144. /******************************************************************************/
  1145. --- Adjusting group types.
  1146. /******************************************************************************/
  1147. ---# Fixing the group types.
  1148. ---{
  1149. // Get the admin group type.
  1150. $request = upgrade_query("
  1151. SELECT group_type
  1152. FROM {$db_prefix}membergroups
  1153. WHERE id_group = 1
  1154. LIMIT 1");
  1155. list ($admin_group_type) = pg_fetch_row($request);
  1156. pg_free_result($request);
  1157. // Not protected means we haven't updated yet!
  1158. if ($admin_group_type != 1)
  1159. {
  1160. // Increase by one.
  1161. upgrade_query("
  1162. UPDATE {$db_prefix}membergroups
  1163. SET group_type = group_type + 1
  1164. WHERE group_type > 0");
  1165. }
  1166. ---}
  1167. ---#
  1168. ---# Changing the group type for Administrator group.
  1169. UPDATE {$db_prefix}membergroups
  1170. SET group_type = 1
  1171. WHERE id_group = 1;
  1172. ---#
  1173. /******************************************************************************/
  1174. --- Adjusting calendar maximum year.
  1175. /******************************************************************************/
  1176. ---# Adjusting calendar maximum year.
  1177. ---{
  1178. if (!isset($modSettings['cal_maxyear']) || $modSettings['cal_maxyear'] == '2010')
  1179. {
  1180. $smcFunc['db_insert']('replace',
  1181. '{db_prefix}settings',
  1182. array('variable' => 'string-255', 'value' => 'string-255'),
  1183. array('cal_maxyear', '2020'),
  1184. array('variable', 'value')
  1185. );
  1186. }
  1187. ---}
  1188. ---#