1c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount/* 2c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * Copyright (C) 2013 The Android Open Source Project 3c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * 4c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * Licensed under the Apache License, Version 2.0 (the "License"); 5c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * you may not use this file except in compliance with the License. 6c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * You may obtain a copy of the License at 7c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * 8c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * http://www.apache.org/licenses/LICENSE-2.0 9c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * 10c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * Unless required by applicable law or agreed to in writing, software 11c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * distributed under the License is distributed on an "AS IS" BASIS, 12c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * See the License for the specific language governing permissions and 14c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * limitations under the License. 15c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount */ 16c8419b4e5e3302f2efc7ea629891041a14219aa7George Mountpackage com.android.photos.data; 17c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount 18c8419b4e5e3302f2efc7ea629891041a14219aa7George Mountimport android.content.Context; 19c8419b4e5e3302f2efc7ea629891041a14219aa7George Mountimport android.database.sqlite.SQLiteDatabase; 20c8419b4e5e3302f2efc7ea629891041a14219aa7George Mountimport android.database.sqlite.SQLiteOpenHelper; 21c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount 222f5a064980ad79e88616ba124268e4d7298c68a5George Mountimport com.android.photos.data.PhotoProvider.Accounts; 23c8419b4e5e3302f2efc7ea629891041a14219aa7George Mountimport com.android.photos.data.PhotoProvider.Albums; 24c8419b4e5e3302f2efc7ea629891041a14219aa7George Mountimport com.android.photos.data.PhotoProvider.Metadata; 25c8419b4e5e3302f2efc7ea629891041a14219aa7George Mountimport com.android.photos.data.PhotoProvider.Photos; 26c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount 272f5a064980ad79e88616ba124268e4d7298c68a5George Mountimport java.util.ArrayList; 282f5a064980ad79e88616ba124268e4d7298c68a5George Mountimport java.util.List; 292f5a064980ad79e88616ba124268e4d7298c68a5George Mount 30c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount/** 31c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * Used in PhotoProvider to create and access the database containing 32c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount * information about photo and video information stored on the server. 33c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount */ 34c8419b4e5e3302f2efc7ea629891041a14219aa7George Mountpublic class PhotoDatabase extends SQLiteOpenHelper { 35c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount @SuppressWarnings("unused") 36c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount private static final String TAG = PhotoDatabase.class.getSimpleName(); 37452f74622c9b6fdd357b94ab74ae81dd03cf3582George Mount static final int DB_VERSION = 3; 38c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount 39c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount private static final String SQL_CREATE_TABLE = "CREATE TABLE "; 40c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount 41c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount private static final String[][] CREATE_PHOTO = { 42c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount { Photos._ID, "INTEGER PRIMARY KEY AUTOINCREMENT" }, 432f5a064980ad79e88616ba124268e4d7298c68a5George Mount // Photos.ACCOUNT_ID is a foreign key to Accounts._ID 442f5a064980ad79e88616ba124268e4d7298c68a5George Mount { Photos.ACCOUNT_ID, "INTEGER NOT NULL" }, 45c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount { Photos.WIDTH, "INTEGER NOT NULL" }, 46c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount { Photos.HEIGHT, "INTEGER NOT NULL" }, 47c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount { Photos.DATE_TAKEN, "INTEGER NOT NULL" }, 48c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount // Photos.ALBUM_ID is a foreign key to Albums._ID 49c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount { Photos.ALBUM_ID, "INTEGER" }, 50c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount { Photos.MIME_TYPE, "TEXT NOT NULL" }, 512f5a064980ad79e88616ba124268e4d7298c68a5George Mount { Photos.TITLE, "TEXT" }, 522f5a064980ad79e88616ba124268e4d7298c68a5George Mount { Photos.DATE_MODIFIED, "INTEGER" }, 532f5a064980ad79e88616ba124268e4d7298c68a5George Mount { Photos.ROTATION, "INTEGER" }, 54c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount }; 55c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount 56c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount private static final String[][] CREATE_ALBUM = { 57c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount { Albums._ID, "INTEGER PRIMARY KEY AUTOINCREMENT" }, 582f5a064980ad79e88616ba124268e4d7298c68a5George Mount // Albums.ACCOUNT_ID is a foreign key to Accounts._ID 592f5a064980ad79e88616ba124268e4d7298c68a5George Mount { Albums.ACCOUNT_ID, "INTEGER NOT NULL" }, 602f5a064980ad79e88616ba124268e4d7298c68a5George Mount // Albums.PARENT_ID is a foreign key to Albums._ID 61c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount { Albums.PARENT_ID, "INTEGER" }, 62b2a646658c87378b681d85a130db705a39d07171Mangesh Ghiware { Albums.ALBUM_TYPE, "TEXT" }, 63c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount { Albums.VISIBILITY, "INTEGER NOT NULL" }, 642f5a064980ad79e88616ba124268e4d7298c68a5George Mount { Albums.LOCATION_STRING, "TEXT" }, 6517362f811fa92ff0d9f92b3c733f08704de2f135Mangesh Ghiware { Albums.TITLE, "TEXT NOT NULL" }, 662f5a064980ad79e88616ba124268e4d7298c68a5George Mount { Albums.SUMMARY, "TEXT" }, 672f5a064980ad79e88616ba124268e4d7298c68a5George Mount { Albums.DATE_PUBLISHED, "INTEGER" }, 682f5a064980ad79e88616ba124268e4d7298c68a5George Mount { Albums.DATE_MODIFIED, "INTEGER" }, 6917362f811fa92ff0d9f92b3c733f08704de2f135Mangesh Ghiware createUniqueConstraint(Albums.PARENT_ID, Albums.TITLE), 70c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount }; 71c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount 72c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount private static final String[][] CREATE_METADATA = { 73c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount { Metadata._ID, "INTEGER PRIMARY KEY AUTOINCREMENT" }, 74c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount // Metadata.PHOTO_ID is a foreign key to Photos._ID 75c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount { Metadata.PHOTO_ID, "INTEGER NOT NULL" }, 76c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount { Metadata.KEY, "TEXT NOT NULL" }, 77c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount { Metadata.VALUE, "TEXT NOT NULL" }, 78c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount createUniqueConstraint(Metadata.PHOTO_ID, Metadata.KEY), 79c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount }; 80c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount 812f5a064980ad79e88616ba124268e4d7298c68a5George Mount private static final String[][] CREATE_ACCOUNT = { 822f5a064980ad79e88616ba124268e4d7298c68a5George Mount { Accounts._ID, "INTEGER PRIMARY KEY AUTOINCREMENT" }, 83452f74622c9b6fdd357b94ab74ae81dd03cf3582George Mount { Accounts.ACCOUNT_NAME, "TEXT UNIQUE NOT NULL" }, 842f5a064980ad79e88616ba124268e4d7298c68a5George Mount }; 852f5a064980ad79e88616ba124268e4d7298c68a5George Mount 86c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount @Override 87c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount public void onCreate(SQLiteDatabase db) { 882f5a064980ad79e88616ba124268e4d7298c68a5George Mount createTable(db, Accounts.TABLE, getAccountTableDefinition()); 892f5a064980ad79e88616ba124268e4d7298c68a5George Mount createTable(db, Albums.TABLE, getAlbumTableDefinition()); 902f5a064980ad79e88616ba124268e4d7298c68a5George Mount createTable(db, Photos.TABLE, getPhotoTableDefinition()); 912f5a064980ad79e88616ba124268e4d7298c68a5George Mount createTable(db, Metadata.TABLE, getMetadataTableDefinition()); 92c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount } 93c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount 94b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount public PhotoDatabase(Context context, String dbName, int dbVersion) { 95b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount super(context, dbName, null, dbVersion); 96b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount } 97b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount 98135c2e576f3dfea954ba628942c55adcb35a7cf6George Mount public PhotoDatabase(Context context, String dbName) { 99135c2e576f3dfea954ba628942c55adcb35a7cf6George Mount super(context, dbName, null, DB_VERSION); 100c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount } 101c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount 102c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount @Override 103c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 104fe804d11f398b3c03b4a6ccffcfdac56e1728de4Mangesh Ghiware recreate(db); 105fe804d11f398b3c03b4a6ccffcfdac56e1728de4Mangesh Ghiware } 106fe804d11f398b3c03b4a6ccffcfdac56e1728de4Mangesh Ghiware 107fe804d11f398b3c03b4a6ccffcfdac56e1728de4Mangesh Ghiware @Override 108fe804d11f398b3c03b4a6ccffcfdac56e1728de4Mangesh Ghiware public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) { 109fe804d11f398b3c03b4a6ccffcfdac56e1728de4Mangesh Ghiware recreate(db); 110fe804d11f398b3c03b4a6ccffcfdac56e1728de4Mangesh Ghiware } 111fe804d11f398b3c03b4a6ccffcfdac56e1728de4Mangesh Ghiware 112fe804d11f398b3c03b4a6ccffcfdac56e1728de4Mangesh Ghiware private void recreate(SQLiteDatabase db) { 113b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount dropTable(db, Metadata.TABLE); 114b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount dropTable(db, Photos.TABLE); 115b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount dropTable(db, Albums.TABLE); 116b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount dropTable(db, Accounts.TABLE); 117b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount onCreate(db); 118c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount } 119c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount 1202f5a064980ad79e88616ba124268e4d7298c68a5George Mount protected List<String[]> getAlbumTableDefinition() { 1212f5a064980ad79e88616ba124268e4d7298c68a5George Mount return tableCreationStrings(CREATE_ALBUM); 1222f5a064980ad79e88616ba124268e4d7298c68a5George Mount } 1232f5a064980ad79e88616ba124268e4d7298c68a5George Mount 1242f5a064980ad79e88616ba124268e4d7298c68a5George Mount protected List<String[]> getPhotoTableDefinition() { 1252f5a064980ad79e88616ba124268e4d7298c68a5George Mount return tableCreationStrings(CREATE_PHOTO); 1262f5a064980ad79e88616ba124268e4d7298c68a5George Mount } 1272f5a064980ad79e88616ba124268e4d7298c68a5George Mount 1282f5a064980ad79e88616ba124268e4d7298c68a5George Mount protected List<String[]> getMetadataTableDefinition() { 1292f5a064980ad79e88616ba124268e4d7298c68a5George Mount return tableCreationStrings(CREATE_METADATA); 1302f5a064980ad79e88616ba124268e4d7298c68a5George Mount } 1312f5a064980ad79e88616ba124268e4d7298c68a5George Mount 1322f5a064980ad79e88616ba124268e4d7298c68a5George Mount protected List<String[]> getAccountTableDefinition() { 1332f5a064980ad79e88616ba124268e4d7298c68a5George Mount return tableCreationStrings(CREATE_ACCOUNT); 1342f5a064980ad79e88616ba124268e4d7298c68a5George Mount } 1352f5a064980ad79e88616ba124268e4d7298c68a5George Mount 1362f5a064980ad79e88616ba124268e4d7298c68a5George Mount protected static void createTable(SQLiteDatabase db, String table, List<String[]> columns) { 137c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount StringBuilder create = new StringBuilder(SQL_CREATE_TABLE); 138c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount create.append(table).append('('); 139c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount boolean first = true; 140c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount for (String[] column : columns) { 141c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount if (!first) { 142c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount create.append(','); 143c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount } 144c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount first = false; 145c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount for (String val: column) { 146c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount create.append(val).append(' '); 147c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount } 148c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount } 149c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount create.append(')'); 150c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount db.beginTransaction(); 151c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount try { 152c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount db.execSQL(create.toString()); 153c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount db.setTransactionSuccessful(); 154c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount } finally { 155c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount db.endTransaction(); 156c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount } 157c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount } 158c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount 159c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount protected static String[] createUniqueConstraint(String column1, String column2) { 160c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount return new String[] { 161c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount "UNIQUE(", column1, ",", column2, ")" 162c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount }; 163c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount } 1642f5a064980ad79e88616ba124268e4d7298c68a5George Mount 1652f5a064980ad79e88616ba124268e4d7298c68a5George Mount protected static List<String[]> tableCreationStrings(String[][] createTable) { 1662f5a064980ad79e88616ba124268e4d7298c68a5George Mount ArrayList<String[]> create = new ArrayList<String[]>(createTable.length); 1672f5a064980ad79e88616ba124268e4d7298c68a5George Mount for (String[] line: createTable) { 1682f5a064980ad79e88616ba124268e4d7298c68a5George Mount create.add(line); 1692f5a064980ad79e88616ba124268e4d7298c68a5George Mount } 1702f5a064980ad79e88616ba124268e4d7298c68a5George Mount return create; 1712f5a064980ad79e88616ba124268e4d7298c68a5George Mount } 1722f5a064980ad79e88616ba124268e4d7298c68a5George Mount 1732f5a064980ad79e88616ba124268e4d7298c68a5George Mount protected static void addToTable(List<String[]> createTable, String[][] columns, String[][] constraints) { 1742f5a064980ad79e88616ba124268e4d7298c68a5George Mount if (columns != null) { 1752f5a064980ad79e88616ba124268e4d7298c68a5George Mount for (String[] column: columns) { 1762f5a064980ad79e88616ba124268e4d7298c68a5George Mount createTable.add(0, column); 1772f5a064980ad79e88616ba124268e4d7298c68a5George Mount } 1782f5a064980ad79e88616ba124268e4d7298c68a5George Mount } 1792f5a064980ad79e88616ba124268e4d7298c68a5George Mount if (constraints != null) { 1802f5a064980ad79e88616ba124268e4d7298c68a5George Mount for (String[] constraint: constraints) { 1812f5a064980ad79e88616ba124268e4d7298c68a5George Mount createTable.add(constraint); 1822f5a064980ad79e88616ba124268e4d7298c68a5George Mount } 1832f5a064980ad79e88616ba124268e4d7298c68a5George Mount } 1842f5a064980ad79e88616ba124268e4d7298c68a5George Mount } 185b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount 186b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount protected static void dropTable(SQLiteDatabase db, String table) { 187b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount db.beginTransaction(); 188b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount try { 189fcdcdd7627bcebe97284e200daee9e8e284aa7f4George Mount db.execSQL("drop table if exists " + table); 190b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount db.setTransactionSuccessful(); 191b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount } finally { 192b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount db.endTransaction(); 193b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount } 194b5ab491c5461df7fedb9c83f42e80acef4fd8a8eGeorge Mount } 195c8419b4e5e3302f2efc7ea629891041a14219aa7George Mount} 196