PageRenderTime 58ms CodeModel.GetById 25ms RepoModel.GetById 1ms app.codeStats 0ms

/dbupgrade.php

http://showslow.googlecode.com/
PHP | 520 lines | 387 code | 42 blank | 91 comment | 0 complexity | e8d30894253f31c1801932605926b7cd MD5 | raw file
  1. <?php
  2. /*
  3. * Copy this script to the folder above and populate $versions array with your migrations
  4. * For more info see: http://www.dbupgrade.org/Main_Page#Migrations_($versions_array)
  5. *
  6. * Note: this script should be versioned in your code repository so it always reflects current code's
  7. * requirements for the database structure.
  8. */
  9. require_once(dirname(__FILE__).'/dbupgrade/lib.php');
  10. $versions = array();
  11. // Add new migrations on top, right below this line.
  12. /* -------------------------------------------------------------------------------------------------------
  13. * VERSION 19
  14. * Adding har link parameter
  15. */
  16. $versions[19]['up'][] = "ALTER TABLE `har` CHANGE `har` `har` LONGBLOB NULL COMMENT 'HAR contents'";
  17. $versions[19]['up'][] = "ALTER TABLE `har` ADD `link` BLOB NULL COMMENT 'URL of HAR file'";
  18. $versions[19]['down'][] = "ALTER TABLE `har` DROP `link`";
  19. $versions[19]['down'][] = "ALTER TABLE `har` CHANGE `har` `har` LONGBLOB NOT NULL COMMENT 'HAR contents'";
  20. /* -------------------------------------------------------------------------------------------------------
  21. * VERSION 18
  22. * IP is not required anymore
  23. */
  24. $versions[18]['up'][] = "ALTER TABLE `pagespeed` CHANGE `ip` `ip` INT(4) UNSIGNED NULL DEFAULT '0'";
  25. $versions[18]['up'][] = "ALTER TABLE `yslow2` CHANGE `ip` `ip` INT(4) UNSIGNED NULL DEFAULT '0' COMMENT 'IP address of the agent'";
  26. $versions[18]['down'][] = "ALTER TABLE `yslow2` CHANGE `ip` `ip` INT(4) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'IP address of the agent'";
  27. $versions[18]['down'][] = "ALTER TABLE `pagespeed` CHANGE `ip` `ip` INT(4) UNSIGNED NOT NULL DEFAULT '0'";
  28. /* -------------------------------------------------------------------------------------------------------
  29. * VERSION 17
  30. * Adding "Avoid Empty Image src" rule to yslow
  31. * Making all scores unsigned types
  32. */
  33. $versions[17]['up'][] = "ALTER TABLE `yslow2`
  34. ADD `yemptysrc` SMALLINT(3) UNSIGNED COMMENT 'Avoid Empty Image src' AFTER `yexpires`,
  35. MODIFY `ynumreq` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Make fewer HTTP requests',
  36. MODIFY `ycdn` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Using CDN',
  37. MODIFY `yexpires` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Expires Headers',
  38. MODIFY `ycompress` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Gzip components',
  39. MODIFY `ycsstop` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'CSS at the top',
  40. MODIFY `yjsbottom` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'JS at the bottom',
  41. MODIFY `yexpressions` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'CSS expressions',
  42. MODIFY `yexternal` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Make JavaScript and CSS external',
  43. MODIFY `ydns` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Reduce DNS lookups',
  44. MODIFY `yminify` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Minify JavaScript and CSS',
  45. MODIFY `yredirects` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Avoid URL redirects',
  46. MODIFY `ydupes` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Remove duplicate JavaScript and CSS',
  47. MODIFY `yetags` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Configure entity tags (ETags)',
  48. MODIFY `yxhr` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Make AJAX cacheable',
  49. MODIFY `yxhrmethod` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Use GET for AJAX requests',
  50. MODIFY `ymindom` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Reduce the number of DOM elements',
  51. MODIFY `yno404` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Avoid HTTP 404 (Not Found) error',
  52. MODIFY `ymincookie` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Reduce cookie size',
  53. MODIFY `ycookiefree` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Use cookie-free domains',
  54. MODIFY `ynofilter` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Avoid AlphaImageLoader filter',
  55. MODIFY `yimgnoscale` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Do not scale images in HTML',
  56. MODIFY `yfavicon` smallint(3) UNSIGNED DEFAULT NULL COMMENT 'Make favicon small and cacheable'";
  57. $versions[17]['down'][] = "ALTER TABLE `yslow2`
  58. DROP yemptysrc,
  59. MODIFY `ynumreq` smallint(6) DEFAULT NULL COMMENT 'Make fewer HTTP requests',
  60. MODIFY `ycdn` smallint(6) DEFAULT NULL COMMENT 'Using CDN',
  61. MODIFY `yexpires` smallint(6) DEFAULT NULL COMMENT 'Expires Headers',
  62. MODIFY `ycompress` smallint(6) DEFAULT NULL COMMENT 'Gzip components',
  63. MODIFY `ycsstop` smallint(6) DEFAULT NULL COMMENT 'CSS at the top',
  64. MODIFY `yjsbottom` smallint(6) DEFAULT NULL COMMENT 'JS at the bottom',
  65. MODIFY `yexpressions` smallint(6) DEFAULT NULL COMMENT 'CSS expressions',
  66. MODIFY `yexternal` smallint(6) DEFAULT NULL COMMENT 'Make JavaScript and CSS external',
  67. MODIFY `ydns` smallint(6) DEFAULT NULL COMMENT 'Reduce DNS lookups',
  68. MODIFY `yminify` smallint(6) DEFAULT NULL COMMENT 'Minify JavaScript and CSS',
  69. MODIFY `yredirects` smallint(6) DEFAULT NULL COMMENT 'Avoid URL redirects',
  70. MODIFY `ydupes` smallint(6) DEFAULT NULL COMMENT 'Remove duplicate JavaScript and CSS',
  71. MODIFY `yetags` smallint(6) DEFAULT NULL COMMENT 'Configure entity tags (ETags)',
  72. MODIFY `yxhr` smallint(6) DEFAULT NULL COMMENT 'Make AJAX cacheable',
  73. MODIFY `yxhrmethod` smallint(6) DEFAULT NULL COMMENT 'Use GET for AJAX requests',
  74. MODIFY `ymindom` smallint(6) DEFAULT NULL COMMENT 'Reduce the number of DOM elements',
  75. MODIFY `yno404` smallint(6) DEFAULT NULL COMMENT 'Avoid HTTP 404 (Not Found) error',
  76. MODIFY `ymincookie` smallint(6) DEFAULT NULL COMMENT 'Reduce cookie size',
  77. MODIFY `ycookiefree` smallint(6) DEFAULT NULL COMMENT 'Use cookie-free domains',
  78. MODIFY `ynofilter` smallint(6) DEFAULT NULL COMMENT 'Avoid AlphaImageLoader filter',
  79. MODIFY `yimgnoscale` smallint(6) DEFAULT NULL COMMENT 'Do not scale images in HTML',
  80. MODIFY `yfavicon` smallint(6) DEFAULT NULL COMMENT 'Make favicon small and cacheable'";
  81. /* -------------------------------------------------------------------------------------------------------
  82. * VERSION 16
  83. * Adding mroe details for PageTest
  84. */
  85. $versions[16]['up'][] = "ALTER TABLE `pagetest`
  86. ADD `f_loadTime` MEDIUMINT(3) UNSIGNED COMMENT '[first view] Load Time (ms)',
  87. ADD `r_loadTime` MEDIUMINT(3) UNSIGNED COMMENT '[repeat view] Load Time (ms)',
  88. ADD `f_TTFB` MEDIUMINT(3) UNSIGNED COMMENT '[first view] Time to First Byte (ms)',
  89. ADD `r_TTFB` MEDIUMINT(3) UNSIGNED COMMENT '[repeat view] Time to First Byte (ms)',
  90. ADD `f_bytesIn` INT(4) UNSIGNED COMMENT '[first view] Bytes In',
  91. ADD `r_bytesIn` INT(4) UNSIGNED COMMENT '[repeat view] Bytes In',
  92. ADD `f_bytesInDoc` INT(4) UNSIGNED COMMENT '[first view] Bytes In (Document)',
  93. ADD `r_bytesInDoc` INT(4) UNSIGNED COMMENT '[repeat view] Bytes In (Document)',
  94. ADD `f_requests` SMALLINT(2) UNSIGNED COMMENT '[first view] Number of Requests',
  95. ADD `r_requests` SMALLINT(2) UNSIGNED COMMENT '[repeat view] Number of Requests',
  96. ADD `f_requestsDoc` SMALLINT(2) UNSIGNED COMMENT '[first view] Number of Requests (Document)',
  97. ADD `r_requestsDoc` SMALLINT(2) UNSIGNED COMMENT '[repeat view] Number of Requests (Document)',
  98. ADD `f_render` MEDIUMINT(3) UNSIGNED COMMENT '[first view] Time to Start Render (ms)',
  99. ADD `r_render` MEDIUMINT(3) UNSIGNED COMMENT '[repeat view] Time to Start Render (ms)',
  100. ADD `f_fullyLoaded` MEDIUMINT(3) UNSIGNED COMMENT '[first view] Time to Fully Loaded (ms)',
  101. ADD `r_fullyLoaded` MEDIUMINT(3) UNSIGNED COMMENT '[repeat view] Time to Fully Loaded (ms)',
  102. ADD `f_docTime` MEDIUMINT(3) UNSIGNED COMMENT '[first view] Document Complete Time (ms)',
  103. ADD `r_docTime` MEDIUMINT(3) UNSIGNED COMMENT '[repeat view] Document Complete Time (ms)',
  104. ADD `f_domTime` MEDIUMINT(3) UNSIGNED COMMENT '[first view] DOM Element Time (ms)',
  105. ADD `r_domTime` MEDIUMINT(3) UNSIGNED COMMENT '[repeat view] DOM Element Time (ms)'";
  106. $versions[16]['down'][] = "ALTER TABLE `pagetest`
  107. DROP `f_loadTime`,
  108. DROP `r_loadTime`,
  109. DROP `f_TTFB`,
  110. DROP `r_TTFB`,
  111. DROP `f_bytesIn`,
  112. DROP `r_bytesIn`,
  113. DROP `f_bytesInDoc`,
  114. DROP `r_bytesInDoc`,
  115. DROP `f_requests`,
  116. DROP `r_requests`,
  117. DROP `f_requestsDoc`,
  118. DROP `r_requestsDoc`,
  119. DROP `f_render`,
  120. DROP `r_render`,
  121. DROP `f_fullyLoaded`,
  122. DROP `r_fullyLoaded`,
  123. DROP `f_docTime`,
  124. DROP `r_docTime`,
  125. DROP `f_domTime`,
  126. DROP `r_domTime`;";
  127. /* -------------------------------------------------------------------------------------------------------
  128. * VERSION 15
  129. * UserBase will now use it's own DBUpgrade instance
  130. * let's create base version for it since all tables were maintained here
  131. */
  132. $versions[15]['up'][] = "CREATE TABLE `3f7f6ece338d68f7fbd069377de434e0_db_version` (
  133. `version` int(10) unsigned NOT NULL DEFAULT '1',
  134. PRIMARY KEY (`version`)
  135. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  136. ";
  137. $versions[15]['down'][] = "DROP TABLE `3f7f6ece338d68f7fbd069377de434e0_db_version`";
  138. /* -------------------------------------------------------------------------------------------------------
  139. * VERSION 14
  140. * Added basic UserBase activity tracking
  141. */
  142. $versions[14]['up'][] = "CREATE TABLE `u_activity` (
  143. `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time of activity',
  144. `user_id` int(10) unsigned NOT NULL COMMENT 'User ID',
  145. `activity_id` int(2) unsigned NOT NULL COMMENT 'Activity ID',
  146. KEY `time` (`time`),
  147. KEY `user_id` (`user_id`),
  148. KEY `activity_id` (`activity_id`)
  149. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Stores user activities'
  150. ";
  151. $versions[14]['down'][] = "DROP TABLE `u_activity`";
  152. /* version 13
  153. *
  154. * PageSpeed 1.9 support
  155. */
  156. // up
  157. $versions[13]['up'][] = "ALTER TABLE `pagespeed` CHANGE `pSpecifyCharsetEarly` `pCharsetEarly` FLOAT UNSIGNED NOT NULL DEFAULT '0';";
  158. $versions[13]['up'][] = "ALTER TABLE `pagespeed` CHANGE `pProxyCache` `pCacheValid` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  159. $versions[13]['up'][] = "ALTER TABLE `pagespeed` CHANGE `pPutCssInTheDocumentHead` `pCssInHead` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  160. $versions[13]['up'][] = "ALTER TABLE `pagespeed` CHANGE `pOptimizeTheOrderOfStylesAndScripts` `pCssJsOrder` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  161. $versions[13]['up'][] = "ALTER TABLE `pagespeed` CHANGE `pMinimizeRequestSize` `pMinReqSize` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  162. $versions[13]['up'][] = "ALTER TABLE `pagespeed` ADD COLUMN `pBadReqs` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  163. $versions[13]['up'][] = "ALTER TABLE `pagespeed` ADD COLUMN `pCssImport` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  164. $versions[13]['up'][] = "ALTER TABLE `pagespeed` ADD COLUMN `pDocWrite` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  165. $versions[13]['up'][] = "ALTER TABLE `pagespeed` ADD COLUMN `pPreferAsync` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  166. $versions[13]['up'][] = "ALTER TABLE `pagespeed` ADD COLUMN `pRemoveQuery` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  167. $versions[13]['up'][] = "ALTER TABLE `pagespeed` ADD COLUMN `pVaryAE` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  168. $versions[13]['up'][] = "ALTER TABLE `pagespeed` ADD COLUMN `pSprite` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  169. // down
  170. $versions[13]['down'][] = "ALTER TABLE `pagespeed` CHANGE `pCharsetEarly` `pSpecifyCharsetEarly` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  171. $versions[13]['down'][] = "ALTER TABLE `pagespeed` CHANGE `pCacheValid` `pProxyCache` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  172. $versions[13]['down'][] = "ALTER TABLE `pagespeed` CHANGE `pCssInHead` `pPutCssInTheDocumentHead` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  173. $versions[13]['down'][] = "ALTER TABLE `pagespeed` CHANGE `pCssJsOrder` `pOptimizeTheOrderOfStylesAndScripts` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  174. $versions[13]['down'][] = "ALTER TABLE `pagespeed` CHANGE `pMinReqSize` `pMinimizeRequestSize` FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  175. $versions[13]['down'][] = "ALTER TABLE `pagespeed` DROP COLUMN `pBadReqs`";
  176. $versions[13]['down'][] = "ALTER TABLE `pagespeed` DROP COLUMN `pCssImport`";
  177. $versions[13]['down'][] = "ALTER TABLE `pagespeed` DROP COLUMN `pDocWrite`";
  178. $versions[13]['down'][] = "ALTER TABLE `pagespeed` DROP COLUMN `pPreferAsync`";
  179. $versions[13]['down'][] = "ALTER TABLE `pagespeed` DROP COLUMN `pRemoveQuery`";
  180. $versions[13]['down'][] = "ALTER TABLE `pagespeed` DROP COLUMN `pVaryAE`";
  181. $versions[13]['down'][] = "ALTER TABLE `pagespeed` DROP COLUMN `pSprite`";
  182. /* version 12
  183. *
  184. * timestamps don't need to be updatable
  185. */
  186. $versions[12]['up'][] = "ALTER TABLE `yslow2` CHANGE `timestamp` `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Measurement timestamp'";
  187. $versions[12]['down'][] = "ALTER TABLE `yslow2` CHANGE `timestamp` `timestamp` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Measurement timestamp'";
  188. /* version 11
  189. *
  190. * Storing PageTest locations
  191. */
  192. $versions[11]['up'][] = "ALTER TABLE pagetest ADD location TEXT DEFAULT NULL COMMENT 'Test location'";
  193. $versions[11]['down'][] = "ALTER TABLE pagetest DROP location";
  194. /* version 10
  195. *
  196. * Adding PageTest history
  197. */
  198. $versions[10] = array(
  199. 'up' => "CREATE TABLE `pagetest` (
  200. `id` BIGINT( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique id',
  201. `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  202. `url_id` BIGINT( 20 ) UNSIGNED NOT NULL COMMENT 'URL id',
  203. `test_id` varchar(255) NOT NULL COMMENT 'PageTest test id',
  204. `test_url` BLOB NOT NULL COMMENT 'PageTest result URL to redirect to'
  205. ) ENGINE=MyISAM;",
  206. 'down' => 'DROP TABLE pagetest',
  207. );
  208. /* version 9
  209. *
  210. * Adding dynaTrace beacon's details
  211. */
  212. $versions[9]['up'][] = "ALTER TABLE dynatrace ADD details TEXT DEFAULT NULL COMMENT 'Beacon details'";
  213. $versions[9]['down'][] = "ALTER TABLE dynatrace DROP details";
  214. // Add new migrations on top, right below this line.
  215. /* version 8
  216. *
  217. * Adding dynaTrace beacon
  218. */
  219. $versions[8]['up'][] = "ALTER TABLE urls ADD dynatrace_last_id BIGINT(20) UNSIGNED NULL DEFAULT NULL COMMENT 'Last measurement ID for dynaTrace beacon'";
  220. $versions[8]['up'][] = "CREATE TABLE `dynatrace` (
  221. `id` bigint(20) unsigned NOT NULL auto_increment COMMENT 'Measurement ID',
  222. `version` varchar(255) default NULL COMMENT 'Version of the format used',
  223. `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'Measurement time',
  224. `url_id` bigint(20) unsigned NOT NULL COMMENT 'URL ID',
  225. `rank` smallint(5) unsigned NOT NULL COMMENT 'verall Page Rank (1-100)',
  226. `cache` smallint(5) unsigned default NULL COMMENT 'Page Rank on Caching Best Practices (1-100)',
  227. `net` smallint(5) unsigned default NULL COMMENT 'Page Rank on Network Requests (1-100)',
  228. `server` smallint(5) unsigned default NULL COMMENT 'Page Rank on Server-Side Execution Time (1-100)',
  229. `js` smallint(5) unsigned default NULL COMMENT 'Page Rank on JavaScript executions (1-100)',
  230. `timetoimpression` bigint(20) unsigned default NULL COMMENT 'Time to First Impression [ms]',
  231. `timetoonload` bigint(20) unsigned default NULL COMMENT 'Time to onLoad [ms]',
  232. `timetofullload` bigint(20) unsigned default NULL COMMENT 'Time to Full Page Load [ms]',
  233. `reqnumber` smallint(6) unsigned default NULL COMMENT '# of Requests [Count]',
  234. `xhrnumber` smallint(6) unsigned default NULL COMMENT '# of XHR Requests [Count]',
  235. `pagesize` bigint(20) unsigned default NULL COMMENT 'Total Page Size [bytes]',
  236. `cachablesize` bigint(20) unsigned default NULL COMMENT 'Total Cachable Size [bytes]',
  237. `noncachablesize` bigint(20) unsigned default NULL COMMENT 'Total Non-Cachable Size [bytes]',
  238. `timeonnetwork` bigint(20) unsigned default NULL COMMENT 'Total Time on Network [ms]',
  239. `timeinjs` bigint(20) unsigned default NULL COMMENT 'Total Time in JavaScript [ms]',
  240. `timeinrendering` bigint(20) unsigned default NULL COMMENT 'Total Time in Rendering [ms]',
  241. PRIMARY KEY (`id`)
  242. ) ENGINE=MyISAM";
  243. $versions[8]['down'][] = "ALTER TABLE urls DROP dynatrace_last_id";
  244. $versions[8]['down'][] = "DROP TABLE `dynatrace`;";
  245. /* version 7
  246. *
  247. * Adding URL creation time to be able to monitor new URLs quickly
  248. */
  249. $versions[7]['up'][] = "ALTER TABLE urls MODIFY last_update TIMESTAMP NULL DEFAULT NULL";
  250. $versions[7]['up'][] = "ALTER TABLE urls ADD added TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Time when URL was added to the table' AFTER `url`";
  251. $versions[7]['down'][] = "ALTER TABLE urls DROP added";
  252. $versions[7]['down'][] = "ALTER TABLE urls MODIFY last_update TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NULL DEFAULT NULL";
  253. /* version 6
  254. *
  255. * Adding userbase instance
  256. */
  257. $versions[6]['up'][] = "CREATE TABLE `u_users` (
  258. `id` int(10) unsigned NOT NULL auto_increment,
  259. `regtime` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'Time of registration',
  260. `name` text NOT NULL,
  261. `username` varchar(25) default NULL,
  262. `email` varchar(320) default NULL,
  263. `pass` varchar(40) NOT NULL COMMENT 'Password digest',
  264. `salt` varchar(13) NOT NULL COMMENT 'Salt',
  265. `temppass` varchar(13) default NULL COMMENT 'Temporary password used for password recovery',
  266. `temppasstime` timestamp NULL default NULL COMMENT 'Temporary password generation time',
  267. `requirespassreset` tinyint(1) NOT NULL default '0' COMMENT 'Flag indicating that user must reset their password before using the site',
  268. `fb_id` bigint(20) unsigned default NULL COMMENT 'Facebook user ID',
  269. PRIMARY KEY (`id`),
  270. UNIQUE KEY `username` (`username`),
  271. UNIQUE KEY `email` (`email`),
  272. UNIQUE KEY `fb_id` (`fb_id`)
  273. ) ENGINE=InnoDB;";
  274. $versions[6]['up'][] = "CREATE TABLE `u_googlefriendconnect` (
  275. `user_id` int(10) unsigned NOT NULL COMMENT 'User ID',
  276. `google_id` varchar(255) NOT NULL COMMENT 'Google Friend Connect ID',
  277. `userpic` text NOT NULL COMMENT 'Google Friend Connect User picture',
  278. PRIMARY KEY (`user_id`,`google_id`),
  279. CONSTRAINT `gfc_user` FOREIGN KEY (`user_id`) REFERENCES `u_users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  280. ) ENGINE=InnoDB;";
  281. $versions[6]['up'][] = "CREATE TABLE `u_invitation` (
  282. `code` char(10) NOT NULL COMMENT 'Code',
  283. `created` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'When invitation was created',
  284. `issuedby` bigint(10) unsigned NOT NULL default '1' COMMENT 'User who issued the invitation. Default is Sergey.',
  285. `sentto` text COMMENT 'Note about who this invitation was sent to',
  286. `user` bigint(10) unsigned default NULL COMMENT 'User name',
  287. PRIMARY KEY (`code`)
  288. ) ENGINE=InnoDB;";
  289. $versions[6]['up'][] = "CREATE TABLE `user_urls` (
  290. `user_id` int(10) unsigned NOT NULL COMMENT 'User ID',
  291. `url_id` bigint(20) unsigned NOT NULL COMMENT 'URL ID to measure',
  292. PRIMARY KEY (`user_id`,`url_id`)
  293. ) ENGINE=MyISAM;";
  294. $versions[6]['down'][] = "DROP TABLE IF EXISTS `user_urls`";
  295. $versions[6]['down'][] = "DROP TABLE IF EXISTS `u_googlefriendconnect`";
  296. $versions[6]['down'][] = "DROP TABLE IF EXISTS `u_invitation`";
  297. $versions[6]['down'][] = "DROP TABLE IF EXISTS `u_users`";
  298. /* version 5
  299. *
  300. * Making last_update NULL unless actually updated
  301. */
  302. $versions[5]['up'][] = "ALTER TABLE urls MODIFY last_update TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP";
  303. $versions[5]['down'][] = "ALTER TABLE urls MODIFY last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP";
  304. /* version 4
  305. *
  306. * Combining renamed PageSpeed's metrics
  307. */
  308. $versions[4]['up'][] = "UPDATE pagespeed SET pOptimizeTheOrderOfStylesAndScripts = pCssJsOrder WHERE pCssJsOrder > pOptimizeTheOrderOfStylesAndScripts";
  309. $versions[4]['up'][] = "ALTER TABLE pagespeed DROP COLUMN pCssJsOrder";
  310. $versions[4]['up'][] = "UPDATE pagespeed SET pPutCssInTheDocumentHead = pCssInHead WHERE pCssInHead > pPutCssInTheDocumentHead";
  311. $versions[4]['up'][] = "ALTER TABLE pagespeed DROP COLUMN pCssInHead";
  312. $versions[4]['up'][] = "UPDATE pagespeed SET pMinimizeRequestSize = pCookieSize WHERE pCookieSize > pMinimizeRequestSize";
  313. $versions[4]['up'][] = "ALTER TABLE pagespeed DROP COLUMN pCookieSize";
  314. $versions[4]['down'][] = "ALTER TABLE pagespeed ADD pCssJsOrder FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  315. $versions[4]['down'][] = "ALTER TABLE pagespeed ADD pCssInHead FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  316. $versions[4]['down'][] = "ALTER TABLE pagespeed ADD pCookieSize FLOAT UNSIGNED NOT NULL DEFAULT '0'";
  317. /* version 3
  318. *
  319. * Adding last measurement ID to the urls table for faster retrieval by primary key
  320. */
  321. $versions[3]['up'][] = "ALTER TABLE urls ADD yslow2_last_id BIGINT(20) UNSIGNED NULL COMMENT 'Last measurement ID for YSlow beacon'";
  322. $versions[3]['up'][] = "ALTER TABLE urls ADD pagespeed_last_id BIGINT(20) UNSIGNED NULL COMMENT 'Last measurement ID for PageSpeed beacon'";
  323. $versions[3]['up'][] = "ALTER TABLE urls DROP w, DROP o, DROP r, DROP ps_w, DROP ps_o, DROP ps_l, DROP ps_r, DROP ps_t";
  324. // migrating data
  325. $versions[3]['up'][] = 'CREATE TEMPORARY TABLE yslow_max_ids SELECT url_id, max(id) as max_id FROM yslow2 GROUP BY url_id';
  326. $versions[3]['up'][] = 'CREATE TEMPORARY TABLE pagespeed_max_ids SELECT url_id, max(id) as max_id FROM pagespeed GROUP BY url_id';
  327. $versions[3]['up'][] = 'UPDATE urls LEFT JOIN yslow_max_ids ON urls.id = yslow_max_ids.url_id LEFT JOIN pagespeed_max_ids ON urls.id = pagespeed_max_ids.url_id SET urls.yslow2_last_id = yslow_max_ids.max_id, urls.pagespeed_last_id = pagespeed_max_ids.max_id';
  328. // downgrading
  329. $versions[3]['down'][] = 'ALTER TABLE urls DROP COLUMN yslow2_last_id';
  330. $versions[3]['down'][] = 'ALTER TABLE urls DROP COLUMN pagespeed_last_id';
  331. // restoring aggregates (no data backporting - lazy)
  332. $versions[3]['down'][] = "ALTER TABLE urls ADD COLUMN w bigint(20) unsigned NOT NULL default '0' COMMENT 'latest size of the page in bytes'";
  333. $versions[3]['down'][] = "ALTER TABLE urls ADD COLUMN o smallint(6) unsigned default NULL COMMENT 'latest overall YSlow grade calculated for this profile'";
  334. $versions[3]['down'][] = "ALTER TABLE urls ADD COLUMN r smallint(6) unsigned NOT NULL default '0' COMMENT 'latest amount of requests with empty cache'";
  335. $versions[3]['down'][] = "ALTER TABLE urls ADD COLUMN ps_w bigint(20) unsigned NOT NULL default '0'";
  336. $versions[3]['down'][] = "ALTER TABLE urls ADD COLUMN ps_o float unsigned default NULL";
  337. $versions[3]['down'][] = "ALTER TABLE urls ADD COLUMN ps_l bigint(20) unsigned NOT NULL default '0'";
  338. $versions[3]['down'][] = "ALTER TABLE urls ADD COLUMN ps_r smallint(6) unsigned NOT NULL default '0'";
  339. $versions[3]['down'][] = "ALTER TABLE urls ADD COLUMN ps_t bigint(20) unsigned NOT NULL default '0'";
  340. /* version 2
  341. *
  342. * Adding HAR beacon
  343. */
  344. $versions[2] = array(
  345. 'up' => "CREATE TABLE `har` (
  346. `id` BIGINT( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique HAR id',
  347. `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  348. `url_id` BIGINT( 20 ) UNSIGNED NOT NULL COMMENT 'URL id',
  349. `har` LONGBLOB NOT NULL COMMENT 'HAR contents',
  350. `compressed` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Indicates that HAR data is stored compressed'
  351. ) ENGINE = MYISAM",
  352. 'down' => 'DROP TABLE har',
  353. );
  354. // version 1
  355. // moved tables.sql here to unify upgrade and install process
  356. $versions[1]['up'][] = "CREATE TABLE `event` (
  357. `id` bigint(20) unsigned NOT NULL auto_increment,
  358. `type` varchar(25) default NULL COMMENT 'string representing type of the event',
  359. `url_prefix` blob NOT NULL COMMENT 'URL prefix to match the urls - usually protocol and host name',
  360. `title` text NOT NULL COMMENT 'event message',
  361. `start` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'start date of event',
  362. `end` timestamp NULL default NULL COMMENT 'end date of event (if null, start is the same as end)',
  363. `resource_url` blob COMMENT 'additional URL to resource related to the event.',
  364. PRIMARY KEY (`id`),
  365. KEY `start` (`start`)
  366. ) ENGINE=MyISAM";
  367. $versions[1]['up'][] = "CREATE TABLE `metric` (
  368. `id` bigint(20) unsigned NOT NULL auto_increment,
  369. `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  370. `url_id` bigint(20) unsigned NOT NULL default '0',
  371. `metric_id` mediumint(8) unsigned NOT NULL default '0',
  372. `value` float NOT NULL default '0',
  373. PRIMARY KEY (`id`)
  374. ) ENGINE=MyISAM";
  375. $versions[1]['up'][] = "CREATE TABLE `pagespeed` (
  376. `id` bigint(20) unsigned NOT NULL auto_increment,
  377. `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  378. `ip` int(4) unsigned NOT NULL default '0',
  379. `user_agent` text NOT NULL,
  380. `url_id` bigint(20) unsigned NOT NULL default '0',
  381. `w` bigint(20) unsigned NOT NULL default '0',
  382. `o` float unsigned NOT NULL default '0',
  383. `l` bigint(20) unsigned NOT NULL default '0',
  384. `r` smallint(6) unsigned NOT NULL default '0',
  385. `t` bigint(20) unsigned NOT NULL default '0',
  386. `v` text NOT NULL,
  387. `pMinifyCSS` float unsigned NOT NULL default '0',
  388. `pMinifyJS` float unsigned NOT NULL default '0',
  389. `pOptImgs` float unsigned NOT NULL default '0',
  390. `pImgDims` float unsigned NOT NULL default '0',
  391. `pCombineJS` float unsigned NOT NULL default '0',
  392. `pCombineCSS` float unsigned NOT NULL default '0',
  393. `pCssInHead` float unsigned NOT NULL default '0',
  394. `pBrowserCache` float unsigned NOT NULL default '0',
  395. `pProxyCache` float unsigned NOT NULL default '0',
  396. `pNoCookie` float unsigned NOT NULL default '0',
  397. `pCookieSize` float unsigned NOT NULL default '0',
  398. `pParallelDl` float unsigned NOT NULL default '0',
  399. `pCssSelect` float unsigned NOT NULL default '0',
  400. `pCssJsOrder` float unsigned NOT NULL default '0',
  401. `pDeferJS` float unsigned NOT NULL default '0',
  402. `pGzip` float unsigned NOT NULL default '0',
  403. `pMinRedirect` float unsigned NOT NULL default '0',
  404. `pCssExpr` float unsigned NOT NULL default '0',
  405. `pUnusedCSS` float unsigned NOT NULL default '0',
  406. `pMinDns` float unsigned NOT NULL default '0',
  407. `pDupeRsrc` float unsigned NOT NULL default '0',
  408. `pScaleImgs` float unsigned NOT NULL default '0' COMMENT 'Scale Images',
  409. `pMinifyHTML` float unsigned NOT NULL default '0',
  410. `pMinimizeRequestSize` float unsigned NOT NULL default '0',
  411. `pOptimizeTheOrderOfStylesAndScripts` float unsigned NOT NULL default '0',
  412. `pPutCssInTheDocumentHead` float unsigned NOT NULL default '0',
  413. `pSpecifyCharsetEarly` float unsigned NOT NULL default '0',
  414. PRIMARY KEY (`id`),
  415. KEY `url_id` (`url_id`)
  416. ) ENGINE=MyISAM";
  417. $versions[1]['up'][] = "CREATE TABLE `urls` (
  418. `id` bigint(20) unsigned NOT NULL auto_increment COMMENT 'id to reference',
  419. `url` blob NOT NULL COMMENT 'url',
  420. `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP,
  421. `last_event_update` timestamp NOT NULL default '0000-00-00 00:00:00' COMMENT 'Last time events were updated for this URL',
  422. `w` bigint(20) unsigned NOT NULL default '0' COMMENT 'latest size of the page in bytes',
  423. `o` smallint(6) unsigned default NULL COMMENT 'latest overall YSlow grade calculated for this profile',
  424. `r` smallint(6) unsigned NOT NULL default '0' COMMENT 'latest amount of requests with empty cache',
  425. `ps_w` bigint(20) unsigned NOT NULL default '0',
  426. `ps_o` float unsigned default NULL,
  427. `ps_l` bigint(20) unsigned NOT NULL default '0',
  428. `ps_r` smallint(6) unsigned NOT NULL default '0',
  429. `ps_t` bigint(20) unsigned NOT NULL default '0',
  430. PRIMARY KEY (`id`),
  431. KEY `last_update` (`last_update`)
  432. ) ENGINE=MyISAM";
  433. $versions[1]['up'][] = "CREATE TABLE `yslow2` (
  434. `id` bigint(20) unsigned NOT NULL auto_increment COMMENT 'Entry id',
  435. `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Measurement timestamp',
  436. `ip` int(4) unsigned NOT NULL default '0' COMMENT 'IP address of the agent',
  437. `user_agent` text NOT NULL COMMENT 'User agent string',
  438. `url_id` bigint(20) unsigned NOT NULL default '0',
  439. `w` bigint(20) unsigned NOT NULL default '0' COMMENT 'size of the page in bytes',
  440. `o` smallint(5) unsigned NOT NULL default '0' COMMENT 'overall YSlow grade calculated for this profile',
  441. `r` smallint(6) unsigned NOT NULL default '0' COMMENT 'total amount of requests with empty cache',
  442. `i` text NOT NULL COMMENT 'testing profile used',
  443. `lt` bigint(20) unsigned NOT NULL default '0' COMMENT 'page load time',
  444. `ynumreq` smallint(6) default NULL COMMENT 'Make fewer HTTP requests',
  445. `ycdn` smallint(6) default NULL COMMENT 'Using CDN',
  446. `yexpires` smallint(6) default NULL COMMENT 'Expires Headers',
  447. `ycompress` smallint(6) default NULL COMMENT 'Gzip components',
  448. `ycsstop` smallint(6) default NULL COMMENT 'CSS at the top',
  449. `yjsbottom` smallint(6) default NULL COMMENT 'JS at the bottom',
  450. `yexpressions` smallint(6) default NULL COMMENT 'CSS expressions',
  451. `yexternal` smallint(6) default NULL COMMENT 'Make JavaScript and CSS external',
  452. `ydns` smallint(6) default NULL COMMENT 'Reduce DNS lookups',
  453. `yminify` smallint(6) default NULL COMMENT 'Minify JavaScript and CSS',
  454. `yredirects` smallint(6) default NULL COMMENT 'Avoid URL redirects',
  455. `ydupes` smallint(6) default NULL COMMENT 'Remove duplicate JavaScript and CSS',
  456. `yetags` smallint(6) default NULL COMMENT 'Configure entity tags (ETags)',
  457. `yxhr` smallint(6) default NULL COMMENT 'Make AJAX cacheable',
  458. `yxhrmethod` smallint(6) default NULL COMMENT 'Use GET for AJAX requests',
  459. `ymindom` smallint(6) default NULL COMMENT 'Reduce the number of DOM elements',
  460. `yno404` smallint(6) default NULL COMMENT 'Avoid HTTP 404 (Not Found) error',
  461. `ymincookie` smallint(6) default NULL COMMENT 'Reduce cookie size',
  462. `ycookiefree` smallint(6) default NULL COMMENT 'Use cookie-free domains',
  463. `ynofilter` smallint(6) default NULL COMMENT 'Avoid AlphaImageLoader filter',
  464. `yimgnoscale` smallint(6) default NULL COMMENT 'Do not scale images in HTML',
  465. `yfavicon` smallint(6) default NULL COMMENT 'Make favicon small and cacheable',
  466. `details` text COMMENT 'Beacon details',
  467. PRIMARY KEY (`id`),
  468. KEY `url_id` (`url_id`)
  469. ) ENGINE=MyISAM";
  470. $versions[1]['down'][] = "DROP TABLE event";
  471. $versions[1]['down'][] = "DROP TABLE metric";
  472. $versions[1]['down'][] = "DROP TABLE pagespeed";
  473. $versions[1]['down'][] = "DROP TABLE urls";
  474. $versions[1]['down'][] = "DROP TABLE yslow2";
  475. require_once(dirname(__FILE__).'/global.php');
  476. // creating DBUpgrade object with your database credentials and $versions defined above
  477. $dbupgrade = new DBUpgrade(new mysqli( $host, $user, $pass, $db), $versions);
  478. require_once(dirname(__FILE__).'/dbupgrade/client.php');