PageRenderTime 29ms CodeModel.GetById 3ms app.highlight 15ms RepoModel.GetById 1ms app.codeStats 1ms

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