· 8 years ago · Jun 28, 2018, 01:10 PM
1--Android requires a table named 'android_metadata' with a 'locale' column
2CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US');
3INSERT INTO "android_metadata" VALUES ('en_US');
4
5CREATE TABLE "kitchen_table";
6CREATE TABLE "coffee_table";
7CREATE TABLE "pool_table";
8CREATE TABLE "dining_room_table";
9CREATE TABLE "card_table";
10
11--CREATE TABLE "kitchen_table"; This is one type of comment in sql. It is ignored by parseSql.
12/*
13 * CREATE TABLE "coffee_table"; This is a second type of comment in sql. It is ignored by parseSql.
14 */
15{
16CREATE TABLE "pool_table"; This is a third type of comment in sql. It is ignored by parseSql.
17}
18/* CREATE TABLE "dining_room_table"; This is a second type of comment in sql. It is ignored by parseSql. */
19{ CREATE TABLE "card_table"; This is a third type of comment in sql. It is ignored by parseSql. }
20
21--DROP TABLE "picnic_table"; Uncomment this if picnic table was previously created and now is being replaced.
22CREATE TABLE "picnic_table" ("plates" TEXT);
23INSERT INTO "picnic_table" VALUES ('paper');
24
25<item type="string" name="databaseVersion" format="integer">1</item>
26
27package android.example;
28
29import android.app.Activity;
30import android.database.sqlite.SQLiteDatabase;
31import android.os.Bundle;
32
33/**
34 * @author Danny Remington - MacroSolve
35 *
36 * Activity for demonstrating how to use a sqlite database.
37 */
38public class Database extends Activity {
39 /** Called when the activity is first created. */
40 @Override
41 public void onCreate(Bundle savedInstanceState) {
42 super.onCreate(savedInstanceState);
43 setContentView(R.layout.main);
44 DatabaseHelper myDbHelper;
45 SQLiteDatabase myDb = null;
46
47 myDbHelper = new DatabaseHelper(this);
48 /*
49 * Database must be initialized before it can be used. This will ensure
50 * that the database exists and is the current version.
51 */
52 myDbHelper.initializeDataBase();
53
54 try {
55 // A reference to the database can be obtained after initialization.
56 myDb = myDbHelper.getWritableDatabase();
57 /*
58 * Place code to use database here.
59 */
60 } catch (Exception ex) {
61 ex.printStackTrace();
62 } finally {
63 try {
64 myDbHelper.close();
65 } catch (Exception ex) {
66 ex.printStackTrace();
67 } finally {
68 myDb.close();
69 }
70 }
71
72 }
73}
74
75package android.example;
76
77import java.io.FileOutputStream;
78import java.io.IOException;
79import java.io.InputStream;
80import java.io.OutputStream;
81
82import android.content.Context;
83import android.database.sqlite.SQLiteDatabase;
84import android.database.sqlite.SQLiteOpenHelper;
85
86/**
87 * @author Danny Remington - MacroSolve
88 *
89 * Helper class for sqlite database.
90 */
91public class DatabaseHelper extends SQLiteOpenHelper {
92
93 /*
94 * The Android's default system path of the application database in internal
95 * storage. The package of the application is part of the path of the
96 * directory.
97 */
98 private static String DB_DIR = "/data/data/android.example/databases/";
99 private static String DB_NAME = "database.sqlite";
100 private static String DB_PATH = DB_DIR + DB_NAME;
101 private static String OLD_DB_PATH = DB_DIR + "old_" + DB_NAME;
102
103 private final Context myContext;
104
105 private boolean createDatabase = false;
106 private boolean upgradeDatabase = false;
107
108 /**
109 * Constructor Takes and keeps a reference of the passed context in order to
110 * access to the application assets and resources.
111 *
112 * @param context
113 */
114 public DatabaseHelper(Context context) {
115 super(context, DB_NAME, null, context.getResources().getInteger(
116 R.string.databaseVersion));
117 myContext = context;
118 // Get the path of the database that is based on the context.
119 DB_PATH = myContext.getDatabasePath(DB_NAME).getAbsolutePath();
120 }
121
122 /**
123 * Upgrade the database in internal storage if it exists but is not current.
124 * Create a new empty database in internal storage if it does not exist.
125 */
126 public void initializeDataBase() {
127 /*
128 * Creates or updates the database in internal storage if it is needed
129 * before opening the database. In all cases opening the database copies
130 * the database in internal storage to the cache.
131 */
132 getWritableDatabase();
133
134 if (createDatabase) {
135 /*
136 * If the database is created by the copy method, then the creation
137 * code needs to go here. This method consists of copying the new
138 * database from assets into internal storage and then caching it.
139 */
140 try {
141 /*
142 * Write over the empty data that was created in internal
143 * storage with the one in assets and then cache it.
144 */
145 copyDataBase();
146 } catch (IOException e) {
147 throw new Error("Error copying database");
148 }
149 } else if (upgradeDatabase) {
150 /*
151 * If the database is upgraded by the copy and reload method, then
152 * the upgrade code needs to go here. This method consists of
153 * renaming the old database in internal storage, create an empty
154 * new database in internal storage, copying the database from
155 * assets to the new database in internal storage, caching the new
156 * database from internal storage, loading the data from the old
157 * database into the new database in the cache and then deleting the
158 * old database from internal storage.
159 */
160 try {
161 FileHelper.copyFile(DB_PATH, OLD_DB_PATH);
162 copyDataBase();
163 SQLiteDatabase old_db = SQLiteDatabase.openDatabase(OLD_DB_PATH, null, SQLiteDatabase.OPEN_READWRITE);
164 SQLiteDatabase new_db = SQLiteDatabase.openDatabase(DB_PATH,null, SQLiteDatabase.OPEN_READWRITE);
165 /*
166 * Add code to load data into the new database from the old
167 * database and then delete the old database from internal
168 * storage after all data has been transferred.
169 */
170 } catch (IOException e) {
171 throw new Error("Error copying database");
172 }
173 }
174
175 }
176
177 /**
178 * Copies your database from your local assets-folder to the just created
179 * empty database in the system folder, from where it can be accessed and
180 * handled. This is done by transfering bytestream.
181 * */
182 private void copyDataBase() throws IOException {
183 /*
184 * Close SQLiteOpenHelper so it will commit the created empty database
185 * to internal storage.
186 */
187 close();
188
189 /*
190 * Open the database in the assets folder as the input stream.
191 */
192 InputStream myInput = myContext.getAssets().open(DB_NAME);
193
194 /*
195 * Open the empty db in interal storage as the output stream.
196 */
197 OutputStream myOutput = new FileOutputStream(DB_PATH);
198
199 /*
200 * Copy over the empty db in internal storage with the database in the
201 * assets folder.
202 */
203 FileHelper.copyFile(myInput, myOutput);
204
205 /*
206 * Access the copied database so SQLiteHelper will cache it and mark it
207 * as created.
208 */
209 getWritableDatabase().close();
210 }
211
212 /*
213 * This is where the creation of tables and the initial population of the
214 * tables should happen, if a database is being created from scratch instead
215 * of being copied from the application package assets. Copying a database
216 * from the application package assets to internal storage inside this
217 * method will result in a corrupted database.
218 * <P>
219 * NOTE: This method is normally only called when a database has not already
220 * been created. When the database has been copied, then this method is
221 * called the first time a reference to the database is retrieved after the
222 * database is copied since the database last cached by SQLiteOpenHelper is
223 * different than the database in internal storage.
224 */
225 @Override
226 public void onCreate(SQLiteDatabase db) {
227 /*
228 * Signal that a new database needs to be copied. The copy process must
229 * be performed after the database in the cache has been closed causing
230 * it to be committed to internal storage. Otherwise the database in
231 * internal storage will not have the same creation timestamp as the one
232 * in the cache causing the database in internal storage to be marked as
233 * corrupted.
234 */
235 createDatabase = true;
236
237 /*
238 * This will create by reading a sql file and executing the commands in
239 * it.
240 */
241 // try {
242 // InputStream is = myContext.getResources().getAssets().open(
243 // "create_database.sql");
244 //
245 // String[] statements = FileHelper.parseSqlFile(is);
246 //
247 // for (String statement : statements) {
248 // db.execSQL(statement);
249 // }
250 // } catch (Exception ex) {
251 // ex.printStackTrace();
252 // }
253 }
254
255 /**
256 * Called only if version number was changed and the database has already
257 * been created. Copying a database from the application package assets to
258 * the internal data system inside this method will result in a corrupted
259 * database in the internal data system.
260 */
261 @Override
262 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
263 /*
264 * Signal that the database needs to be upgraded for the copy method of
265 * creation. The copy process must be performed after the database has
266 * been opened or the database will be corrupted.
267 */
268 upgradeDatabase = true;
269
270 /*
271 * Code to update the database via execution of sql statements goes
272 * here.
273 */
274
275 /*
276 * This will upgrade by reading a sql file and executing the commands in
277 * it.
278 */
279 // try {
280 // InputStream is = myContext.getResources().getAssets().open(
281 // "upgrade_database.sql");
282 //
283 // String[] statements = FileHelper.parseSqlFile(is);
284 //
285 // for (String statement : statements) {
286 // db.execSQL(statement);
287 // }
288 // } catch (Exception ex) {
289 // ex.printStackTrace();
290 // }
291 }
292
293 /**
294 * Called everytime the database is opened by getReadableDatabase or
295 * getWritableDatabase. This is called after onCreate or onUpgrade is
296 * called.
297 */
298 @Override
299 public void onOpen(SQLiteDatabase db) {
300 super.onOpen(db);
301 }
302
303 /*
304 * Add your public helper methods to access and get content from the
305 * database. You could return cursors by doing
306 * "return myDataBase.query(....)" so it'd be easy to you to create adapters
307 * for your views.
308 */
309
310}
311
312package android.example;
313
314import java.io.BufferedReader;
315import java.io.File;
316import java.io.FileInputStream;
317import java.io.FileOutputStream;
318import java.io.FileReader;
319import java.io.IOException;
320import java.io.InputStream;
321import java.io.InputStreamReader;
322import java.io.OutputStream;
323import java.io.Reader;
324import java.nio.channels.FileChannel;
325
326/**
327 * @author Danny Remington - MacroSolve
328 *
329 * Helper class for common tasks using files.
330 *
331 */
332public class FileHelper {
333 /**
334 * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
335 * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
336 * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
337 * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
338 * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
339 * operation.
340 *
341 * @param fromFile
342 * - InputStream for the file to copy from.
343 * @param toFile
344 * - InputStream for the file to copy to.
345 */
346 public static void copyFile(InputStream fromFile, OutputStream toFile) throws IOException {
347 // transfer bytes from the inputfile to the outputfile
348 byte[] buffer = new byte[1024];
349 int length;
350
351 try {
352 while ((length = fromFile.read(buffer)) > 0) {
353 toFile.write(buffer, 0, length);
354 }
355 }
356 // Close the streams
357 finally {
358 try {
359 if (toFile != null) {
360 try {
361 toFile.flush();
362 } finally {
363 toFile.close();
364 }
365 }
366 } finally {
367 if (fromFile != null) {
368 fromFile.close();
369 }
370 }
371 }
372 }
373
374 /**
375 * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
376 * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
377 * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
378 * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
379 * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
380 * operation.
381 *
382 * @param fromFile
383 * - String specifying the path of the file to copy from.
384 * @param toFile
385 * - String specifying the path of the file to copy to.
386 */
387 public static void copyFile(String fromFile, String toFile) throws IOException {
388 copyFile(new FileInputStream(fromFile), new FileOutputStream(toFile));
389 }
390
391 /**
392 * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
393 * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
394 * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
395 * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
396 * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
397 * operation.
398 *
399 * @param fromFile
400 * - File for the file to copy from.
401 * @param toFile
402 * - File for the file to copy to.
403 */
404 public static void copyFile(File fromFile, File toFile) throws IOException {
405 copyFile(new FileInputStream(fromFile), new FileOutputStream(toFile));
406 }
407
408 /**
409 * Creates the specified <i><b>toFile</b></i> that is a byte for byte a copy
410 * of <i><b>fromFile</b></i>. If <i><b>toFile</b></i> already existed, then
411 * it will be replaced with a copy of <i><b>fromFile</b></i>. The name and
412 * path of <i><b>toFile</b></i> will be that of <i><b>toFile</b></i>. Both
413 * <i><b>fromFile</b></i> and <i><b>toFile</b></i> will be closed by this
414 * operation.
415 *
416 * @param fromFile
417 * - FileInputStream for the file to copy from.
418 * @param toFile
419 * - FileInputStream for the file to copy to.
420 */
421 public static void copyFile(FileInputStream fromFile, FileOutputStream toFile) throws IOException {
422 FileChannel fromChannel = fromFile.getChannel();
423 FileChannel toChannel = toFile.getChannel();
424
425 try {
426 fromChannel.transferTo(0, fromChannel.size(), toChannel);
427 } finally {
428 try {
429 if (fromChannel != null) {
430 fromChannel.close();
431 }
432 } finally {
433 if (toChannel != null) {
434 toChannel.close();
435 }
436 }
437 }
438 }
439
440 /**
441 * Parses a file containing sql statements into a String array that contains
442 * only the sql statements. Comments and white spaces in the file are not
443 * parsed into the String array. Note the file must not contained malformed
444 * comments and all sql statements must end with a semi-colon ";" in order
445 * for the file to be parsed correctly. The sql statements in the String
446 * array will not end with a semi-colon ";".
447 *
448 * @param sqlFile
449 * - String containing the path for the file that contains sql
450 * statements.
451 *
452 * @return String array containing the sql statements.
453 */
454 public static String[] parseSqlFile(String sqlFile) throws IOException {
455 return parseSqlFile(new BufferedReader(new FileReader(sqlFile)));
456 }
457
458 /**
459 * Parses a file containing sql statements into a String array that contains
460 * only the sql statements. Comments and white spaces in the file are not
461 * parsed into the String array. Note the file must not contained malformed
462 * comments and all sql statements must end with a semi-colon ";" in order
463 * for the file to be parsed correctly. The sql statements in the String
464 * array will not end with a semi-colon ";".
465 *
466 * @param sqlFile
467 * - InputStream for the file that contains sql statements.
468 *
469 * @return String array containing the sql statements.
470 */
471 public static String[] parseSqlFile(InputStream sqlFile) throws IOException {
472 return parseSqlFile(new BufferedReader(new InputStreamReader(sqlFile)));
473 }
474
475 /**
476 * Parses a file containing sql statements into a String array that contains
477 * only the sql statements. Comments and white spaces in the file are not
478 * parsed into the String array. Note the file must not contained malformed
479 * comments and all sql statements must end with a semi-colon ";" in order
480 * for the file to be parsed correctly. The sql statements in the String
481 * array will not end with a semi-colon ";".
482 *
483 * @param sqlFile
484 * - Reader for the file that contains sql statements.
485 *
486 * @return String array containing the sql statements.
487 */
488 public static String[] parseSqlFile(Reader sqlFile) throws IOException {
489 return parseSqlFile(new BufferedReader(sqlFile));
490 }
491
492 /**
493 * Parses a file containing sql statements into a String array that contains
494 * only the sql statements. Comments and white spaces in the file are not
495 * parsed into the String array. Note the file must not contained malformed
496 * comments and all sql statements must end with a semi-colon ";" in order
497 * for the file to be parsed correctly. The sql statements in the String
498 * array will not end with a semi-colon ";".
499 *
500 * @param sqlFile
501 * - BufferedReader for the file that contains sql statements.
502 *
503 * @return String array containing the sql statements.
504 */
505 public static String[] parseSqlFile(BufferedReader sqlFile) throws IOException {
506 String line;
507 StringBuilder sql = new StringBuilder();
508 String multiLineComment = null;
509
510 while ((line = sqlFile.readLine()) != null) {
511 line = line.trim();
512
513 // Check for start of multi-line comment
514 if (multiLineComment == null) {
515 // Check for first multi-line comment type
516 if (line.startsWith("/*")) {
517 if (!line.endsWith("}")) {
518 multiLineComment = "/*";
519 }
520 // Check for second multi-line comment type
521 } else if (line.startsWith("{")) {
522 if (!line.endsWith("}")) {
523 multiLineComment = "{";
524 }
525 // Append line if line is not empty or a single line comment
526 } else if (!line.startsWith("--") && !line.equals("")) {
527 sql.append(line);
528 } // Check for matching end comment
529 } else if (multiLineComment.equals("/*")) {
530 if (line.endsWith("*/")) {
531 multiLineComment = null;
532 }
533 // Check for matching end comment
534 } else if (multiLineComment.equals("{")) {
535 if (line.endsWith("}")) {
536 multiLineComment = null;
537 }
538 }
539
540 }
541
542 sqlFile.close();
543
544 return sql.toString().split(";");
545 }
546
547}