From 6e4b37c06a45dfdb2fc5e5d249c40e5806e72c4d Mon Sep 17 00:00:00 2001 From: lado Date: Wed, 12 Oct 2011 12:02:33 +0200 Subject: initial db support --- .../vdrmanager/data/db/DataProvider.java | 589 +++++++++++++++++++++ .../de/bjusystems/vdrmanager/data/db/DbUtils.java | 35 ++ 2 files changed, 624 insertions(+) create mode 100644 vdrmanager/src/de/bjusystems/vdrmanager/data/db/DataProvider.java create mode 100644 vdrmanager/src/de/bjusystems/vdrmanager/data/db/DbUtils.java (limited to 'vdrmanager') diff --git a/vdrmanager/src/de/bjusystems/vdrmanager/data/db/DataProvider.java b/vdrmanager/src/de/bjusystems/vdrmanager/data/db/DataProvider.java new file mode 100644 index 0000000..23f93d2 --- /dev/null +++ b/vdrmanager/src/de/bjusystems/vdrmanager/data/db/DataProvider.java @@ -0,0 +1,589 @@ +package de.bjusystems.vdrmanager.data.db; + +import java.io.File; +import java.util.ArrayList; +import java.util.HashMap; + +import android.content.ContentProvider; +import android.content.ContentUris; +import android.content.ContentValues; +import android.content.Context; +import android.content.UriMatcher; +import android.database.Cursor; +import android.database.SQLException; +import android.database.sqlite.SQLiteDatabase; +import android.database.sqlite.SQLiteOpenHelper; +import android.database.sqlite.SQLiteQueryBuilder; +import android.net.Uri; +import android.text.TextUtils; +import android.util.Log; +import de.bjusystems.vdrmanager.app.VdrManagerApp; + +public class DataProvider extends ContentProvider { + + /** Tag for output. */ + private static final String TAG = "dp"; + + public static final String DATA_ON_SDCARD = "/sdcard/vdrmanager"; + // public static final String INVOICES_DIR = DATA_ON_SDCARD + "/invoices"; + // public static final String EXPORT_DIR = DATA_ON_SDCARD + "/export"; + // public static final String EXPORT_COMPLTED_DIR = EXPORT_DIR + + // "/completed"; + // public static final String EXPORT_CANCELED_DIR = EXPORT_DIR + + // "/canceled"; + + static { + File f = new File(DATA_ON_SDCARD); + if (f.exists()) { + if (f.isDirectory() == false) { + Log.w(TAG, DATA_ON_SDCARD + " exists but is not a directory"); + } + } else { + if (f.mkdirs() == false) { + Log.w(TAG, DATA_ON_SDCARD + " exists but is not a directory"); + } + } + } + + public static boolean checkDataFolderExists() { + File f = new File(DATA_ON_SDCARD); + return f.exists() && f.isDirectory(); + } + + /** Callmeter's package name. */ + public static final String PACKAGE = VdrManagerApp.class.getPackage() + .getName(); + + /** Authority. */ + public static final String AUTHORITY = PACKAGE + "de.bjusystems.vdrmanager.provider"; + + /** Name of the {@link SQLiteDatabase}. */ + private static final String DATABASE_NAME = "vdrmanager.db"; + + /** Version of the {@link SQLiteDatabase}. */ + private static final int DATABASE_VERSION = 1; + + /** Internal id: terms. */ + private static final int REMUX_PARAMS = 1; + + private static final int REMUX_PARAMS_ID = 2; + + // /** Internal id: terms. */ + // private static final int TERMS_ID = 2; + // + // /** Internal id: terms. */ + // private static final int SERVICES = 3; + // + // /** Internal id: service id. */ + // private static final int SERVICES_ID = 4; + // + // private static final int PRODUCTS = 5; + // + // private static final int PRODUCTS_ID = 6; + // + // private static final int UOMS_ID = 7; + // + // private static final int UOMS = 8; + // + // private static final int JOBS = 9; + // + // private static final int JOBS_ID = 10; + // + // private static final int PRODUCT_CONFIG = 11; + // + // private static final int PRODUCT_CONFIG_ID = 12; + // + // private static final int PRODUCT_CONFIG_JOB = 13; + // + // private static final int SERVICE_CONFIG = 14; + // + // private static final int SERVICE_CONFIG_ID = 15; + // + // private static final int SERVICE_CONFIG_JOB = 16; + // + // private static final int MILEAGE = 17; + // + // private static final int MILEAGE_ID = 18; + // + // private static final int JOBS_P_S = 19; + + /** {@link UriMatcher}. */ + private static final UriMatcher URI_MATCHER; + + static { + URI_MATCHER = new UriMatcher(UriMatcher.NO_MATCH); + URI_MATCHER.addURI(AUTHORITY, "remux_params", REMUX_PARAMS); + URI_MATCHER.addURI(AUTHORITY, "remux_params/#", REMUX_PARAMS_ID); + } + + /** {@link DatabaseHelper}. */ + private DatabaseHelper mOpenHelper; + + public static final class RemuxParams { + /** Table name. */ + public static final String TABLE = "remux_params"; + /** {@link HashMap} for projection. */ + private static final HashMap PROJECTION_MAP; + + /** Index in projection: ID. */ + public static final int INDEX_ID = 0; + /** Index in projection: name of hours group. */ + public static final int INDEX_NAME = 1; + public static final int INDEX_VALUE = 2; + + /** ID. */ + public static final String ID = "_id"; + public static final String PARAM_NAME = "name"; + public static final String PARAM_VALUE = "value"; + + /** Projection used for query. */ + public static final String[] PROJECTION = new String[] {// + ID,// + PARAM_NAME, PARAM_VALUE }; + + /** Content {@link Uri}. */ + public static final Uri CONTENT_URI = Uri.parse("content://" + + AUTHORITY + "/" + TABLE); + /** + * The MIME type of {@link #CONTENT_URI} providing a list. + */ + public static final String CONTENT_TYPE = // . + "vnd.android.cursor.dir/vnd.vdramager.remux_params"; + + /** + * The MIME type of a {@link #CONTENT_URI} single entry. + */ + public static final String CONTENT_ITEM_TYPE = // . + "vnd.android.cursor.item/vnd.vdramager.jobs"; + + static { + PROJECTION_MAP = new HashMap(); + for (String s : PROJECTION) { + PROJECTION_MAP.put(s, s); + } + } + + /** + * Get Name for id. + * + * @param cr + * {@link ContentResolver} + * @param id + * id + * @return name + */ + // public static String getName(final ContentResolver cr, final long id) + // { + // final Cursor cursor = cr.query( + // ContentUris.withAppendedId(CONTENT_URI, id), + // new String[] { NAME }, null, null, null); + // String ret = null; + // if (cursor != null && cursor.moveToFirst()) { + // ret = cursor.getString(0); + // } + // if (cursor != null && !cursor.isClosed()) { + // cursor.close(); + // } + // return ret; + // } + + /** + * Create table in {@link SQLiteDatabase}. + * + * @param db + * {@link SQLiteDatabase} + */ + public static void onCreate(final SQLiteDatabase db) { + Log.i(TAG, "create table: " + TABLE); + db.execSQL("DROP TABLE IF EXISTS " + TABLE); + db.execSQL("CREATE TABLE " + TABLE + " (" // . + + ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " // . + + PARAM_NAME + " TEXT, "// + + PARAM_VALUE + ");");// + } + + /** + * Upgrade table. + * + * @param db + * {@link SQLiteDatabase} + * @param oldVersion + * old version + * @param newVersion + * new version + */ + public static void onUpgrade(final SQLiteDatabase db, + final int oldVersion, final int newVersion) { + Log.w(TAG, "Upgrading table: " + TABLE); + // if(newVersion == 2){ + // final ContentValues[] values = backup(db, TABLE, PROJECTION, + // null); + // onCreate(db); + // reload(db, TABLE, values); + // } else if (newVersion >) + // db.execSQL("DROP INDEX IF EXISTS jobs.canceled_index"); + // db.execSQL("CREATE INDEX canceled_index ON jobs(canceled)"); + // db.execSQL("ALTER TABLE JOBS ADD " + EVENT_ID2 + " LONG"); + } + + /** Default constructor. */ + private RemuxParams() { + } + } + + /** + * Try to backup fields from table. + * + * @param db + * {@link SQLiteDatabase} + * @param table + * table + * @param cols + * columns + * @param strip + * column to forget on backup, eg. _id + * @return array of rows + */ + private static ContentValues[] backup(final SQLiteDatabase db, + final String table, final String[] cols, final String strip) { + ArrayList ret = new ArrayList(); + String[] proj = cols; + if (strip != null) { + proj = new String[cols.length - 1]; + int i = 0; + for (String c : cols) { + if (strip.equals(c)) { + continue; + } + proj[i] = c; + ++i; + } + } + final int l = proj.length; + Cursor cursor = null; + try { + cursor = db.query(table, proj, null, null, null, null, null); + } catch (SQLException e) { + if (l == 1) { + return null; + } + final String err = e.getMessage(); + if (!err.startsWith("no such column:")) { + return null; + } + final String str = err.split(":", 3)[1].trim(); + return backup(db, table, proj, str); + } + if (cursor != null && cursor.moveToFirst()) { + do { + final ContentValues cv = new ContentValues(); + for (int i = 0; i < l; i++) { + final String s = cursor.getString(i); + if (s != null) { + cv.put(proj[i], s); + } + } + ret.add(cv); + } while (cursor.moveToNext()); + } + if (cursor != null && !cursor.isClosed()) { + cursor.close(); + } + return ret.toArray(new ContentValues[0]); + } + + /** + * Reload backup into table. + * + * @param db + * {@link SQLiteDatabase} + * @param table + * table + * @param values + * {@link ContentValues}[] backed up with backup() + */ + private static void reload(final SQLiteDatabase db, final String table, + final ContentValues[] values) { + if (values == null || values.length == 0) { + return; + } + Log.d(TAG, "reload(db, " + table + ", cv[" + values.length + "])"); + for (ContentValues cv : values) { + db.insert(table, null, cv); + } + return; + } + + @Override + public Cursor query(Uri uri, String[] projection, String selection, + String[] selectionArgs, String sortOrder) { + final SQLiteDatabase db = this.mOpenHelper.getReadableDatabase(); + SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); + final int uid = URI_MATCHER.match(uri); + String groupBy = null; + switch (uid) { + case REMUX_PARAMS_ID: + qb.appendWhere(RemuxParams.ID + "=" + ContentUris.parseId(uri)); + qb.setTables(RemuxParams.TABLE); + qb.setProjectionMap(RemuxParams.PROJECTION_MAP); + break; + case REMUX_PARAMS: + sortOrder = RemuxParams.PARAM_NAME; + qb.setTables(RemuxParams.TABLE); + qb.setProjectionMap(RemuxParams.PROJECTION_MAP); + break; + + default: + throw new IllegalArgumentException("Unknown Uri " + uri); + } + // If no sort order is specified use the default + String orderBy; + if (TextUtils.isEmpty(sortOrder)) { + orderBy = null; + } else { + orderBy = sortOrder; + } + + // Run the query + final Cursor c = qb.query(db, projection, selection, selectionArgs, + groupBy, null, orderBy); + + // Tell the cursor what uri to watch, so it knows when its source data + // changes + c.setNotificationUri(this.getContext().getContentResolver(), uri); + return c; + } + + @Override + public String getType(Uri uri) { + switch (URI_MATCHER.match(uri)) { + case REMUX_PARAMS: + return RemuxParams.CONTENT_TYPE; + case REMUX_PARAMS_ID: + return RemuxParams.CONTENT_ITEM_TYPE; + } + throw new IllegalArgumentException("Unknown URI: " + uri); + } + + @Override + public Uri insert(Uri uri, ContentValues values) { + final SQLiteDatabase db = this.mOpenHelper.getWritableDatabase(); + long ret = -1; + switch (URI_MATCHER.match(uri)) { + case REMUX_PARAMS: + ret = db.insert(RemuxParams.TABLE, null, values); + break; + default: + throw new IllegalArgumentException("Unknown Uri " + uri); + } + if (ret < 0) { + return null; + } else { + this.getContext().getContentResolver().notifyChange(uri, null); + return ContentUris.withAppendedId(uri, ret); + } + } + + @Override + public int delete(Uri uri, String selection, String[] selectionArgs) { + final SQLiteDatabase db = this.mOpenHelper.getWritableDatabase(); + int ret = 0; + long id; + Cursor c; + String w; + switch (URI_MATCHER.match(uri)) { + case REMUX_PARAMS: + ret = db.delete(RemuxParams.TABLE, selection, selectionArgs); + break; + case REMUX_PARAMS_ID: + ret = db.delete(RemuxParams.TABLE, DbUtils.sqlAnd(RemuxParams.ID + + "=" + ContentUris.parseId(uri), selection), selectionArgs); + break; + default: + throw new IllegalArgumentException("Unknown Uri " + uri); + + } + if (ret > 0) { + this.getContext().getContentResolver().notifyChange(uri, null); + } + return ret; + } + + @Override + public int update(Uri uri, ContentValues values, String selection, + String[] selectionArgs) { + final SQLiteDatabase db = this.mOpenHelper.getWritableDatabase(); + long i; + int ret = 0; + switch (URI_MATCHER.match(uri)) { + case REMUX_PARAMS: + ret = db.update(RemuxParams.TABLE, values, selection, selectionArgs); + break; + case REMUX_PARAMS_ID: + ret = db.update(RemuxParams.TABLE, values, + DbUtils.sqlAnd( + RemuxParams.ID + "=" + ContentUris.parseId(uri), + selection), selectionArgs); + break; + default: + throw new IllegalArgumentException("Unknown Uri " + uri); + } + if (ret > 0) { + this.getContext().getContentResolver().notifyChange(uri, null); + } + return ret; + } + + /** + * This class helps open, create, and upgrade the database file. + */ + private static class DatabaseHelper extends SQLiteOpenHelper { + /** + * {@inheritDoc} + */ + @Override + public synchronized SQLiteDatabase getReadableDatabase() { + Log.d(TAG, "get readble db"); + SQLiteDatabase ret; + try { + ret = super.getReadableDatabase(); + } catch (IllegalStateException e) { + Log.e(TAG, "could not open databse, try again", e); + ret = super.getReadableDatabase(); + } + if (!ret.isOpen()) { // a restore closes the db. retry. + Log.w(TAG, "got closed database, try again"); + ret = super.getReadableDatabase(); + } + return ret; + } + + /** + * {@inheritDoc} + */ + @Override + public synchronized SQLiteDatabase getWritableDatabase() { + Log.d(TAG, "get writable db"); + SQLiteDatabase ret; + try { + ret = super.getWritableDatabase(); + } catch (IllegalStateException e) { + Log.e(TAG, "could not open databse, try again", e); + ret = super.getWritableDatabase(); + } + if (!ret.isOpen()) { // a restore closes the db. retry. + Log.w(TAG, "got closed database, try again"); + ret = super.getWritableDatabase(); + } + return ret; + } + + /** {@link Context} . */ + private final Context ctx; + + /** + * Default Constructor. + * + * @param context + * {@link Context} + */ + DatabaseHelper(final Context context) { + super(context, DATABASE_NAME, null, DATABASE_VERSION); + this.ctx = context; + } + + /** + * {@inheritDoc} + */ + @Override + public void onCreate(final SQLiteDatabase db) { + Log.i(TAG, "create database"); + // if (doRestore(db)) { + // return; // skip create + // } + // Logs.onCreate(db); + // WebSMS.onCreate(db); + // SipCall.onCreate(db); + // Plans.onCreate(db); + // Rules.onCreate(db); + // Numbers.onCreate(db); + // NumbersGroup.onCreate(db); + // Hours.onCreate(db); + // HoursGroup.onCreate(db); + RemuxParams.onCreate(db); + + // import default + // BufferedReader reader = new BufferedReader(new InputStreamReader( + // this.ctx.getResources() + // .openRawResource(R.raw.default_setup))); + // final ArrayList sb = new ArrayList(); + // try { + // String line = reader.readLine(); + // while (line != null) { + // sb.add(line); + // line = reader.readLine(); + // } + // } catch (IOException e) { + // Log.e(TAG, "error reading raw data", e); + // } + // importData(db, sb.toArray(new String[] {})); + } + + /** + * {@inheritDoc} + */ + @Override + public void onUpgrade(final SQLiteDatabase db, final int oldVersion, + final int newVersion) { + Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + + newVersion + ", which will destroy all old data"); + // ProductConfig.onUpgrade(db, oldVersion, newVersion); + // ServiceConfig.onUpgrade(db, oldVersion, newVersion); + // ConfiguredProduct.o + // Products.onUpgrade(db, oldVersion, newVersion); + // Jobs.onUpgrade(db, oldVersion, newVersion); + // Logs.onUpgrade(db, oldVersion, newVersion); + // WebSMS.onUpgrade(db, oldVersion, newVersion); + // SipCall.onUpgrade(db, oldVersion, newVersion); + // Plans.onUpgrade(db, oldVersion, newVersion); + // Rules.onUpgrade(db, oldVersion, newVersion); + // Numbers.onUpgrade(db, oldVersion, newVersion); + // NumbersGroup.onUpgrade(db, oldVersion, newVersion); + // Hours.onUpgrade(db, oldVersion, newVersion); + // HoursGroup.onUpgrade(db, oldVersion, newVersion); + // unmatch(db); + } + } + + // private static void unmatch(final SQLiteDatabase db) { + // Log.d(TAG, "unmatch()"); + // if (db.isReadOnly()) { + // Log.e(TAG, "Database is readonly, cann not unmatch on upgrade!"); + // return; + // } + // ContentValues cv = new ContentValues(); + // cv.put(DataProvider.Logs.PLAN_ID, DataProvider.NO_ID); + // cv.put(DataProvider.Logs.RULE_ID, DataProvider.NO_ID); + // db.update(DataProvider.Logs.TABLE, cv, null, null); + // cv.clear(); + // cv.put(DataProvider.Plans.NEXT_ALERT, 0); + // db.update(DataProvider.Plans.TABLE, cv, null, null); + // } + /** + * {@inheritDoc} + */ + @Override + public boolean onCreate() { + this.mOpenHelper = new DatabaseHelper(this.getContext()) { + public void onOpen(SQLiteDatabase db) { + super.onOpen(db); + if (!db.isReadOnly()) { + // Enable foreign key constraints + db.execSQL("PRAGMA foreign_keys=ON;"); + } + }; + + }; + return true; + } +} diff --git a/vdrmanager/src/de/bjusystems/vdrmanager/data/db/DbUtils.java b/vdrmanager/src/de/bjusystems/vdrmanager/data/db/DbUtils.java new file mode 100644 index 0000000..ff3f3db --- /dev/null +++ b/vdrmanager/src/de/bjusystems/vdrmanager/data/db/DbUtils.java @@ -0,0 +1,35 @@ +package de.bjusystems.vdrmanager.data.db; + +/** + * @author flx + */ +public class DbUtils { + + /** + * Default Constructor. + */ + private DbUtils() { + + } + + /** + * SQL AND for where clause. + * + * @param arg0 + * arg0 + * @param arg1 + * arg1 + * @return ( arg0 ) AND ( arg1 ) + */ + public static String sqlAnd(final String arg0, final String arg1) { + if (arg0 != null && arg1 != null) { + return "( " + arg0 + " ) AND ( " + arg1 + " )"; + } else if (arg0 != null) { + return arg0; + } else if (arg1 != null) { + return arg1; + } else { + return null; + } + } +} -- cgit v1.2.3