Merge lp:~vthompson/music-app/fix-1511585-part-2 into lp:music-app

[Victor Thompson]

* Update canned mediastore.db schema version from 9 to 10.
* Add directions for updating and debugging schema version changes.

* Update canned mediastore.db schema version from 9 to 10.
* Add directions for updating and debugging schema version changes.

Please follow through the instructions to debug/upgrade against trunk to verify they make sense.

[Ubuntu Phone Apps Jenkins Bot (ubuntu-phone-apps-jenkins-bot)]

PASSED: Continuous integration, rev:936
http://91.189.93.70:8080/job/music-app-ci/1413/
Executed test runs:
    SUCCESS: http://91.189.93.70:8080/job/music-app-vivid-amd64-ci/265

Click here to trigger a rebuild:
http://91.189.93.70:8080/job/music-app-ci/1413/rebuild

review: Approve (continuous-integration)

[Andrew Hayzen (ahayzen)]

$ adt-run . ./com.ubuntu.music_2.2.936_all.click -o /tmp/music-app-output/ --- ssh -s adb -- -p <OMITTED>
autopilot PASS

\o/

Thanks for keeping up with the mediascanner2 schemas fun :-)

review: Approve

=== modified file 'README.autopilot'
--- README.autopilot	2015-07-23 01:47:34 +0000
+++ README.autopilot	2015-11-03 02:46:37 +0000
@@ -39,7 +39,6 @@
     $ autopilot3 launch -i Qt qmlscene app/music-app.qml
     $ autopilot3 vis
 
-
 ## Running tests on device or emulator:
 
 NOTE: Prior to running the tests on the device, you will need to do the following if there is music on the system:
@@ -60,3 +59,18 @@
 
 *  Run the tests on device (assumes only one click package in the directory)
     $ adt-run . *.click --- ssh -s adb -- -p <PASSWORD>
+
+# Resolving mediascanner2 schema issues when tests fail
+
+Occasionally the schema for the mediascanner2 (ms2) service is incremented and updates to the mocked ms2 database are required. The following steps should be taken to update the database and sql file when this happens.
+
+1. Dump a new schema file for the database for debugging:
+   $ sqlite3 ~/.cache/mediascanner-2.0/mediastore.db .sch > tests/autopilot/music_app/content/mediascanner-2.0/mediastore.sch
+
+2. Edit the SQL file to use the new schemaVersion number:
+   $ vi tests/autopilot/music_app/content/mediascanner-2.0/mediastore.sql
+
+3. If the tests still do not pass, execute the following bzr command to debug what has changed in the schema and update the SQL file to insert the values for each column in the 'media' table accordingly:
+   $ bzr diff tests/autopilot/music_app/content/mediascanner-2.0/mediastore.sch
+   $ vi tests/autopilot/music_app/content/mediascanner-2.0/mediastore.sql
+

=== modified file 'debian/changelog'
--- debian/changelog	2015-11-02 14:59:24 +0000
+++ debian/changelog	2015-11-03 02:46:37 +0000
@@ -7,6 +7,8 @@
   * Remove some deprecated code for the UbuntuShape image property.
   * Update app to use the Ubuntu Components version 1.3 (LP: #1508363)
   * Update canned mediastore.db schema version from 8 to 9 (LP: #1511585).
+  * Update canned mediastore.db schema version from 9 to 10.
+  * Add directions for updating and debugging schema version changes.
 
   [ Andrew Hayzen ]
   * Switch to using the new uc1.3 listitems within the SDK

=== added file 'tests/autopilot/music_app/content/mediascanner-2.0/mediastore.sch'
--- tests/autopilot/music_app/content/mediascanner-2.0/mediastore.sch	1970-01-01 00:00:00 +0000
+++ tests/autopilot/music_app/content/mediascanner-2.0/mediastore.sch	2015-11-03 02:46:37 +0000
@@ -0,0 +1,71 @@
+CREATE TABLE schemaVersion (version INTEGER);
+CREATE TABLE media (
+    id INTEGER PRIMARY KEY,
+    filename TEXT UNIQUE NOT NULL CHECK (filename LIKE '/%'),
+    content_type TEXT,
+    etag TEXT,
+    title TEXT,
+    date TEXT,
+    artist TEXT,          -- Only relevant to audio
+    album TEXT,           -- Only relevant to audio
+    album_artist TEXT,    -- Only relevant to audio
+    genre TEXT,           -- Only relevant to audio
+    disc_number INTEGER,  -- Only relevant to audio
+    track_number INTEGER, -- Only relevant to audio
+    duration INTEGER,
+    width INTEGER,        -- Only relevant to video/images
+    height INTEGER,       -- Only relevant to video/images
+    latitude DOUBLE,
+    longitude DOUBLE,
+    has_thumbnail INTEGER CHECK (has_thumbnail IN (0, 1)),
+    mtime INTEGER,
+    type INTEGER CHECK (type IN (1, 2, 3)) -- MediaType enum
+);
+CREATE INDEX media_type_idx ON media(type);
+CREATE INDEX media_song_info_idx ON media(type, album_artist, album, disc_number, track_number, title) WHERE type = 1;
+CREATE INDEX media_artist_idx ON media(type, artist) WHERE type = 1;
+CREATE INDEX media_genre_idx ON media(type, genre) WHERE type = 1;
+CREATE INDEX media_mtime_idx ON media(type, mtime);
+CREATE TABLE media_attic (
+    filename TEXT UNIQUE NOT NULL,
+    content_type TEXT,
+    etag TEXT,
+    title TEXT,
+    date TEXT,
+    artist TEXT,          -- Only relevant to audio
+    album TEXT,           -- Only relevant to audio
+    album_artist TEXT,    -- Only relevant to audio
+    genre TEXT,           -- Only relevant to audio
+    disc_number INTEGER,  -- Only relevant to audio
+    track_number INTEGER, -- Only relevant to audio
+    duration INTEGER,
+    width INTEGER,        -- Only relevant to video/images
+    height INTEGER,       -- Only relevant to video/images
+    latitude DOUBLE,
+    longitude DOUBLE,
+    has_thumbnail INTEGER,
+    mtime INTEGER,
+    type INTEGER   -- 0=Audio, 1=Video
+);
+CREATE VIRTUAL TABLE media_fts
+USING fts4(content='media', title, artist, album, tokenize=mozporter);
+CREATE TABLE 'media_fts_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
+CREATE TABLE 'media_fts_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
+CREATE TABLE 'media_fts_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
+CREATE TABLE 'media_fts_stat'(id INTEGER PRIMARY KEY, value BLOB);
+CREATE TRIGGER media_bu BEFORE UPDATE ON media BEGIN
+  DELETE FROM media_fts WHERE docid=old.id;
+END;
+CREATE TRIGGER media_au AFTER UPDATE ON media BEGIN
+  INSERT INTO media_fts(docid, title, artist, album) VALUES (new.id, new.title, new.artist, new.album);
+END;
+CREATE TRIGGER media_bd BEFORE DELETE ON media BEGIN
+  DELETE FROM media_fts WHERE docid=old.id;
+END;
+CREATE TRIGGER media_ai AFTER INSERT ON media BEGIN
+  INSERT INTO media_fts(docid, title, artist, album) VALUES (new.id, new.title, new.artist, new.album);
+END;
+CREATE TABLE broken_files (
+    filename TEXT PRIMARY KEY NOT NULL,
+    etag TEXT NOT NULL
+);

=== modified file 'tests/autopilot/music_app/content/mediascanner-2.0/mediastore.sql'
--- tests/autopilot/music_app/content/mediascanner-2.0/mediastore.sql	2015-11-01 16:17:33 +0000
+++ tests/autopilot/music_app/content/mediascanner-2.0/mediastore.sql	2015-11-03 02:46:37 +0000
@@ -1,4 +1,7 @@
 BEGIN TRANSACTION;
+DELETE FROM `schemaVersion`;
+INSERT INTO `schemaVersion` VALUES(10);
+
 DROP TABLE media;
 CREATE TABLE media (
     filename TEXT PRIMARY KEY NOT NULL CHECK (filename LIKE '/%'),