upgrade_2-0_postgresql.sql 42 KB

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