1 /*
2  * Copyright (C) 2013 The Android Open Source Project
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  *      http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */
16 package com.android.photos.data;
17 
18 import android.content.Context;
19 import android.database.sqlite.SQLiteDatabase;
20 import android.database.sqlite.SQLiteOpenHelper;
21 
22 import com.android.photos.data.PhotoProvider.Accounts;
23 import com.android.photos.data.PhotoProvider.Albums;
24 import com.android.photos.data.PhotoProvider.Metadata;
25 import com.android.photos.data.PhotoProvider.Photos;
26 
27 import java.util.ArrayList;
28 import java.util.List;
29 
30 /**
31  * Used in PhotoProvider to create and access the database containing
32  * information about photo and video information stored on the server.
33  */
34 public class PhotoDatabase extends SQLiteOpenHelper {
35     @SuppressWarnings("unused")
36     private static final String TAG = PhotoDatabase.class.getSimpleName();
37     static final int DB_VERSION = 3;
38 
39     private static final String SQL_CREATE_TABLE = "CREATE TABLE ";
40 
41     private static final String[][] CREATE_PHOTO = {
42         { Photos._ID, "INTEGER PRIMARY KEY AUTOINCREMENT" },
43         // Photos.ACCOUNT_ID is a foreign key to Accounts._ID
44         { Photos.ACCOUNT_ID, "INTEGER NOT NULL" },
45         { Photos.WIDTH, "INTEGER NOT NULL" },
46         { Photos.HEIGHT, "INTEGER NOT NULL" },
47         { Photos.DATE_TAKEN, "INTEGER NOT NULL" },
48         // Photos.ALBUM_ID is a foreign key to Albums._ID
49         { Photos.ALBUM_ID, "INTEGER" },
50         { Photos.MIME_TYPE, "TEXT NOT NULL" },
51         { Photos.TITLE, "TEXT" },
52         { Photos.DATE_MODIFIED, "INTEGER" },
53         { Photos.ROTATION, "INTEGER" },
54     };
55 
56     private static final String[][] CREATE_ALBUM = {
57         { Albums._ID, "INTEGER PRIMARY KEY AUTOINCREMENT" },
58         // Albums.ACCOUNT_ID is a foreign key to Accounts._ID
59         { Albums.ACCOUNT_ID, "INTEGER NOT NULL" },
60         // Albums.PARENT_ID is a foreign key to Albums._ID
61         { Albums.PARENT_ID, "INTEGER" },
62         { Albums.ALBUM_TYPE, "TEXT" },
63         { Albums.VISIBILITY, "INTEGER NOT NULL" },
64         { Albums.LOCATION_STRING, "TEXT" },
65         { Albums.TITLE, "TEXT NOT NULL" },
66         { Albums.SUMMARY, "TEXT" },
67         { Albums.DATE_PUBLISHED, "INTEGER" },
68         { Albums.DATE_MODIFIED, "INTEGER" },
69         createUniqueConstraint(Albums.PARENT_ID, Albums.TITLE),
70     };
71 
72     private static final String[][] CREATE_METADATA = {
73         { Metadata._ID, "INTEGER PRIMARY KEY AUTOINCREMENT" },
74         // Metadata.PHOTO_ID is a foreign key to Photos._ID
75         { Metadata.PHOTO_ID, "INTEGER NOT NULL" },
76         { Metadata.KEY, "TEXT NOT NULL" },
77         { Metadata.VALUE, "TEXT NOT NULL" },
78         createUniqueConstraint(Metadata.PHOTO_ID, Metadata.KEY),
79     };
80 
81     private static final String[][] CREATE_ACCOUNT = {
82         { Accounts._ID, "INTEGER PRIMARY KEY AUTOINCREMENT" },
83         { Accounts.ACCOUNT_NAME, "TEXT UNIQUE NOT NULL" },
84     };
85 
86     @Override
onCreate(SQLiteDatabase db)87     public void onCreate(SQLiteDatabase db) {
88         createTable(db, Accounts.TABLE, getAccountTableDefinition());
89         createTable(db, Albums.TABLE, getAlbumTableDefinition());
90         createTable(db, Photos.TABLE, getPhotoTableDefinition());
91         createTable(db, Metadata.TABLE, getMetadataTableDefinition());
92     }
93 
PhotoDatabase(Context context, String dbName, int dbVersion)94     public PhotoDatabase(Context context, String dbName, int dbVersion) {
95         super(context, dbName, null, dbVersion);
96     }
97 
PhotoDatabase(Context context, String dbName)98     public PhotoDatabase(Context context, String dbName) {
99         super(context, dbName, null, DB_VERSION);
100     }
101 
102     @Override
onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)103     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
104         recreate(db);
105     }
106 
107     @Override
onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)108     public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
109         recreate(db);
110     }
111 
recreate(SQLiteDatabase db)112     private void recreate(SQLiteDatabase db) {
113         dropTable(db, Metadata.TABLE);
114         dropTable(db, Photos.TABLE);
115         dropTable(db, Albums.TABLE);
116         dropTable(db, Accounts.TABLE);
117         onCreate(db);
118     }
119 
getAlbumTableDefinition()120     protected List<String[]> getAlbumTableDefinition() {
121         return tableCreationStrings(CREATE_ALBUM);
122     }
123 
getPhotoTableDefinition()124     protected List<String[]> getPhotoTableDefinition() {
125         return tableCreationStrings(CREATE_PHOTO);
126     }
127 
getMetadataTableDefinition()128     protected List<String[]> getMetadataTableDefinition() {
129         return tableCreationStrings(CREATE_METADATA);
130     }
131 
getAccountTableDefinition()132     protected List<String[]> getAccountTableDefinition() {
133         return tableCreationStrings(CREATE_ACCOUNT);
134     }
135 
createTable(SQLiteDatabase db, String table, List<String[]> columns)136     protected static void createTable(SQLiteDatabase db, String table, List<String[]> columns) {
137         StringBuilder create = new StringBuilder(SQL_CREATE_TABLE);
138         create.append(table).append('(');
139         boolean first = true;
140         for (String[] column : columns) {
141             if (!first) {
142                 create.append(',');
143             }
144             first = false;
145             for (String val: column) {
146                 create.append(val).append(' ');
147             }
148         }
149         create.append(')');
150         db.beginTransaction();
151         try {
152             db.execSQL(create.toString());
153             db.setTransactionSuccessful();
154         } finally {
155             db.endTransaction();
156         }
157     }
158 
createUniqueConstraint(String column1, String column2)159     protected static String[] createUniqueConstraint(String column1, String column2) {
160         return new String[] {
161                 "UNIQUE(", column1, ",", column2, ")"
162         };
163     }
164 
tableCreationStrings(String[][] createTable)165     protected static List<String[]> tableCreationStrings(String[][] createTable) {
166         ArrayList<String[]> create = new ArrayList<String[]>(createTable.length);
167         for (String[] line: createTable) {
168             create.add(line);
169         }
170         return create;
171     }
172 
addToTable(List<String[]> createTable, String[][] columns, String[][] constraints)173     protected static void addToTable(List<String[]> createTable, String[][] columns, String[][] constraints) {
174         if (columns != null) {
175             for (String[] column: columns) {
176                 createTable.add(0, column);
177             }
178         }
179         if (constraints != null) {
180             for (String[] constraint: constraints) {
181                 createTable.add(constraint);
182             }
183         }
184     }
185 
dropTable(SQLiteDatabase db, String table)186     protected static void dropTable(SQLiteDatabase db, String table) {
187         db.beginTransaction();
188         try {
189             db.execSQL("drop table if exists " + table);
190             db.setTransactionSuccessful();
191         } finally {
192             db.endTransaction();
193         }
194     }
195 }
196