List of usage examples for android.database.sqlite SQLiteDatabase insert
public long insert(String table, String nullColumnHack, ContentValues values)
From source file:eu.inmite.apps.smsjizdenka.service.UpdateService.java
@Override protected void onHandleIntent(Intent intent) { if (intent == null) { return;/* w w w . ja v a 2 s . co m*/ } final boolean force = intent.getBooleanExtra("force", false); try { Locale loc = Locale.getDefault(); String lang = loc.getISO3Language(); // http://www.loc.gov/standards/iso639-2/php/code_list.php; T-values if present both T and B if (lang == null || lang.length() == 0) { lang = ""; } int serverVersion = intent.getIntExtra("serverVersion", -1); boolean fromPush = serverVersion != -1; JSONObject versionJson = null; if (!fromPush) { versionJson = getVersion(true); serverVersion = versionJson.getInt("version"); } int localVersion = Preferences.getInt(c, Preferences.DATA_VERSION, -1); final String localLanguage = Preferences.getString(c, Preferences.DATA_LANGUAGE, ""); if (serverVersion <= localVersion && !force && lang.equals(localLanguage) && !LOCAL_DEFINITION_TESTING) { // don't update DebugLog.i("Nothing new, not updating"); return; } // update but don't notify about it. boolean firstLaunchNoUpdate = ((localVersion == -1 && getVersion(false).getInt("version") == serverVersion) || !lang.equals(localLanguage)); if (!firstLaunchNoUpdate) { DebugLog.i("There are new definitions available!"); } handleAuthorMessage(versionJson, lang, intent, fromPush); InputStream is = getIS(URL_TICKETS_ID); try { String json = readResult(is); JSONObject o = new JSONObject(json); JSONArray array = o.getJSONArray("tickets"); final SQLiteDatabase db = DatabaseHelper.get(this).getWritableDatabase(); for (int i = 0; i < array.length(); i++) { final JSONObject city = array.getJSONObject(i); try { final ContentValues cv = new ContentValues(); cv.put(Cities._ID, city.getInt("id")); cv.put(Cities.CITY, getStringLocValue(city, lang, "city")); if (city.has("city_pubtran")) { cv.put(Cities.CITY_PUBTRAN, city.getString("city_pubtran")); } cv.put(Cities.COUNTRY, city.getString("country")); cv.put(Cities.CURRENCY, city.getString("currency")); cv.put(Cities.DATE_FORMAT, city.getString("dateFormat")); cv.put(Cities.IDENTIFICATION, city.getString("identification")); cv.put(Cities.LAT, city.getDouble("lat")); cv.put(Cities.LON, city.getDouble("lon")); cv.put(Cities.NOTE, getStringLocValue(city, lang, "note")); cv.put(Cities.NUMBER, city.getString("number")); cv.put(Cities.P_DATE_FROM, city.getString("pDateFrom")); cv.put(Cities.P_DATE_TO, city.getString("pDateTo")); cv.put(Cities.P_HASH, city.getString("pHash")); cv.put(Cities.PRICE, city.getString("price")); cv.put(Cities.PRICE_NOTE, getStringLocValue(city, lang, "priceNote")); cv.put(Cities.REQUEST, city.getString("request")); cv.put(Cities.VALIDITY, city.getInt("validity")); if (city.has("confirmReq")) { cv.put(Cities.CONFIRM_REQ, city.getString("confirmReq")); } if (city.has("confirm")) { cv.put(Cities.CONFIRM, city.getString("confirm")); } final JSONArray additionalNumbers = city.getJSONArray("additionalNumbers"); for (int j = 0; j < additionalNumbers.length() && j < 3; j++) { cv.put("ADDITIONAL_NUMBER_" + (j + 1), additionalNumbers.getString(j)); } db.beginTransaction(); int count = db.update(DatabaseHelper.CITY_TABLE_NAME, cv, Cities._ID + " = " + cv.getAsInteger(Cities._ID), null); if (count == 0) { db.insert(DatabaseHelper.CITY_TABLE_NAME, null, cv); } db.setTransactionSuccessful(); getContentResolver().notifyChange(Cities.CONTENT_URI, null); } finally { if (db.inTransaction()) { db.endTransaction(); } } } Preferences.set(c, Preferences.DATA_VERSION, serverVersion); Preferences.set(c, Preferences.DATA_LANGUAGE, lang); if (!firstLaunchNoUpdate && !fromPush) { final int finalServerVersion = serverVersion; mHandler.post(new Runnable() { @Override public void run() { Toast.makeText(UpdateService.this, getString(R.string.cities_update_completed, finalServerVersion), Toast.LENGTH_LONG).show(); } }); } if (LOCAL_DEFINITION_TESTING) { DebugLog.w( "Local definition testing - data updated from assets - must be removed in production!"); } } finally { is.close(); } } catch (IOException e) { DebugLog.e("IOException when calling update: " + e.getMessage(), e); } catch (JSONException e) { DebugLog.e("JSONException when calling update: " + e.getMessage(), e); } }
From source file:org.frc836.database.DBSyncService.java
private void processMatches(JSONArray matches) { // TODO could be abstracted further try {// w ww . j a v a 2s . co m for (int i = 0; i < matches.length(); i++) { JSONObject row = matches.getJSONObject(i); Action action = Action.UPDATE; if (row.getInt(MatchStatsStruct.COLUMN_NAME_INVALID) != 0) { action = Action.DELETE; } ContentValues vals = MatchStatsStruct.getNewMatchStats().jsonToCV(row); // check if this entry exists already String[] projection = { MatchStatsStruct.COLUMN_NAME_ID, MatchStatsStruct.COLUMN_NAME_INVALID }; String[] where = { vals.getAsString(MatchStatsStruct.COLUMN_NAME_EVENT_ID), vals.getAsString(MatchStatsStruct.COLUMN_NAME_MATCH_ID), vals.getAsString(MatchStatsStruct.COLUMN_NAME_TEAM_ID), vals.getAsString(MatchStatsStruct.COLUMN_NAME_PRACTICE_MATCH) }; synchronized (ScoutingDBHelper.lock) { SQLiteDatabase db = ScoutingDBHelper.getInstance().getWritableDatabase(); Cursor c = db.query(MatchStatsStruct.TABLE_NAME, projection, // select MatchStatsStruct.COLUMN_NAME_EVENT_ID + "=? AND " + MatchStatsStruct.COLUMN_NAME_MATCH_ID + "=? AND " + MatchStatsStruct.COLUMN_NAME_TEAM_ID + "=? AND " + MatchStatsStruct.COLUMN_NAME_PRACTICE_MATCH + "=?", where, null, // don't // group null, // don't filter null, // don't order "0,1"); // limit to 1 try { int id = 0, invalid = 0; if (!c.moveToFirst()) { if (action == Action.UPDATE) action = Action.INSERT; else if (action == Action.DELETE) action = Action.NOTHING; } else { id = c.getInt(c.getColumnIndexOrThrow(MatchStatsStruct.COLUMN_NAME_ID)); invalid = c.getInt(c.getColumnIndexOrThrow(MatchStatsStruct.COLUMN_NAME_INVALID)); if (invalid > 0) // this field has not been sent to // server yet. action = Action.NOTHING; } String[] where2 = { String.valueOf(id) }; switch (action) { case UPDATE: db.update(MatchStatsStruct.TABLE_NAME, vals, MatchStatsStruct.COLUMN_NAME_ID + " = ?", where2); break; case INSERT: db.insert(MatchStatsStruct.TABLE_NAME, null, vals); break; case DELETE: db.delete(MatchStatsStruct.TABLE_NAME, MatchStatsStruct.COLUMN_NAME_ID + " = ?", where2); break; default: } } finally { if (c != null) c.close(); ScoutingDBHelper.getInstance().close(); } } } } catch (JSONException e) { // TODO handle error } }
From source file:com.rener.sea.DBHelper.java
public long createUser(String userName, String passwd, int pID, String salt) { SQLiteDatabase db = getWritableDatabase(); ContentValues values = new ContentValues(); values.put(DBSchema.USER_USERNAME, userName); values.put(DBSchema.USER_PASSHASH, passwd); values.put(DBSchema.USER_PERSON_ID, pID); values.put(DBSchema.USER_SALT, salt); long id = db.insert(DBSchema.TABLE_USERS, null, values); db.close();//from www.j ava2 s .c om return id;// if -1 error during insertion }
From source file:com.example.android.lightcontrol.MainActivity.java
public void query_shortaddr() { helper = new DBHelper(getApplicationContext()); cursor = helper.select(TABLE_NAME_SHORT_ADDRESS); int i;//w w w .j av a 2 s . c o m if (GlobalVariable.query_short_packet == null) { Toast.makeText(getApplicationContext(), "can't get the short address", Toast.LENGTH_SHORT).show(); } else { SQLiteDatabase db = helper.getWritableDatabase(); if (cursor.getCount() > 0) { db.delete(TABLE_NAME_SHORT_ADDRESS, null, null); } for (i = 14; i < GlobalVariable.query_short_packet.length(); i = i + 22) { ContentValues values = new ContentValues(); values.put(FEILD_SHORTADDRESS, GlobalVariable.query_short_packet.substring(i, i + 4)); db.insert(TABLE_NAME_SHORT_ADDRESS, null, values); } cursor.requery(); //query_daliaddr(); new Query_dali_Async().execute(); } if (D) Log.e(TAG, "in querylight " + list); }
From source file:com.rener.sea.DBHelper.java
public boolean fillDB() { Person person = new Person(1, "Temporal", null, "User", null, "temporal.user@rener.com", null, this); new Person(2, "Nelson", null, "Reyes", null, "nelson.reyes@upr.edu", null, this); new Person(3, "Enrique", null, "Rodriguez", null, "enrique.rodriguez2@upr.edu", null, this); new Person(4, "Ricardo", null, "Fuentes", null, "ricardo.fuentes@upr.edu", null, this); new Person(5, "Ramn", null, "Saldaa", null, "ramon.saldana@upr.edu", null, this); User blank = new User(1, 1, "", "", this); User nelson = new User(2, 2, "nelson.reyes", "iamnelson", this); User enrique = new User(3, 3, "enrique.rodriguez2", "iamenrique", this); User rick = new User(4, 4, "ricardo.fuentes", "iamricardo", this); User ramon = new User(5, 5, "ramon.saldana", "iamramon", this); //Dummy flowchart Flowchart fc = new Flowchart(1, 1, 10, 3, "Flowchart Test", "0", this); //Dummy items new Item(1, fc.getId(), "Is the cow sick?", Item.BOOLEAN, this); new Item(2, fc.getId(), "How would you categorize this problem?", Item.MULTIPLE_CHOICE, this); new Item(3, fc.getId(), "Record a description of the milk coloring, texture, and smell", Item.OPEN, this); new Item(4, fc.getId(), "Input amount of times cow eats a day", Item.CONDITIONAL, this); new Item(5, fc.getId(), "Recommendation 1", Item.RECOMMENDATION, this); new Item(6, fc.getId(), "Recommendation 2", Item.RECOMMENDATION, this); new Item(7, fc.getId(), "Recommendation 3", Item.RECOMMENDATION, this); new Item(8, fc.getId(), "Recommendation 4", Item.RECOMMENDATION, this); new Item(9, fc.getId(), "Recommendation 5", Item.RECOMMENDATION, this); new Item(10, fc.getId(), "End of flowchart test", Item.END, this); //Dummy options Option o1 = new Option(1, 1, 2, "Yes", this); Option o2 = new Option(2, 1, 5, "No", this); Option o3 = new Option(3, 2, 3, "Milk is discolored", this); Option o4 = new Option(4, 2, 6, "Injured leg", this); Option o5 = new Option(5, 2, 4, "Eating problems", this); Option o6 = new Option(6, 4, 8, "lt3", this); Option o7 = new Option(7, 4, 9, "ge3", this); Option o8 = new Option(8, 3, 7, "[user input that is a description]", this); Option o9 = new Option(9, 7, 10, "End", this); Option o10 = new Option(10, 6, 10, "End", this); Option o11 = new Option(11, 8, 10, "End", this); Option o12 = new Option(12, 5, 10, "End", this); Option o13 = new Option(13, 9, 10, "End", this); //Dummy location SQLiteDatabase db = getReadableDatabase(); ContentValues values = new ContentValues(); values.put(DBSchema.ADDRESS_ID, 0);//ww w . j a v a2 s . c o m values.put(DBSchema.ADDRESS_LINE1, "Terace"); values.put(DBSchema.ADDRESS_LINE2, "apt 1028"); values.put(DBSchema.ADDRESS_CITY, "Mayagez"); values.put(DBSchema.ADDRESS_ZIPCODE, 682); long id = db.insert(DBSchema.TABLE_ADDRESS, null, values); Location loc = new Location(1, "El platanal", id, 1, 3, "jhagfljfdsg", 2, this); //Dummy report Report report = new Report(this, nelson); report.setName("Dummy Report"); // report.setSubject(person); report.setLocation(loc); report.setFlowchart(fc); report.setNotes("these are some report notes"); Path path = new Path(report.getId(), this); path.addEntry(o1); path.addEntry(o3); path.addEntry(o8, "description of something"); path.addEntry(o9); return true; }
From source file:org.totschnig.myexpenses.provider.TransactionDatabase.java
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { try {/* w w w . ja va 2 s. c o m*/ Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + "."); if (oldVersion < 17) { db.execSQL("drop table accounts"); db.execSQL("CREATE TABLE accounts (_id integer primary key autoincrement, label text not null, " + "opening_balance integer, description text, currency text not null);"); //db.execSQL("ALTER TABLE expenses add column account_id integer"); } if (oldVersion < 18) { db.execSQL( "CREATE TABLE payee (_id integer primary key autoincrement, name text unique not null);"); db.execSQL("ALTER TABLE expenses add column payee text"); } if (oldVersion < 19) { db.execSQL("ALTER TABLE expenses add column transfer_peer text"); } if (oldVersion < 20) { db.execSQL( "CREATE TABLE transactions ( _id integer primary key autoincrement, comment text not null, " + "date datetime not null, amount integer not null, cat_id integer, account_id integer, " + "payee text, transfer_peer integer default null);"); db.execSQL("INSERT INTO transactions (comment,date,amount,cat_id,account_id,payee,transfer_peer)" + " SELECT comment,date,CAST(ROUND(amount*100) AS INTEGER),cat_id,account_id,payee,transfer_peer FROM expenses"); db.execSQL("DROP TABLE expenses"); db.execSQL("ALTER TABLE accounts RENAME to accounts_old"); db.execSQL("CREATE TABLE accounts (_id integer primary key autoincrement, label text not null, " + "opening_balance integer, description text, currency text not null);"); db.execSQL("INSERT INTO accounts (label,opening_balance,description,currency)" + " SELECT label,CAST(ROUND(opening_balance*100) AS INTEGER),description,currency FROM accounts_old"); db.execSQL("DROP TABLE accounts_old"); } if (oldVersion < 21) { db.execSQL( "CREATE TABLE paymentmethods (_id integer primary key autoincrement, label text not null, type integer default 0);"); db.execSQL( "CREATE TABLE accounttype_paymentmethod (type text, method_id integer, primary key (type,method_id));"); ContentValues initialValues; long _id; for (PaymentMethod.PreDefined pm : PaymentMethod.PreDefined.values()) { initialValues = new ContentValues(); initialValues.put("label", pm.name()); initialValues.put("type", pm.paymentType); _id = db.insert("paymentmethods", null, initialValues); initialValues = new ContentValues(); initialValues.put("method_id", _id); initialValues.put("type", "BANK"); db.insert("accounttype_paymentmethod", null, initialValues); } db.execSQL("ALTER TABLE transactions add column payment_method_id integer"); db.execSQL("ALTER TABLE accounts add column type text default 'CASH'"); } if (oldVersion < 22) { db.execSQL("CREATE TABLE templates ( _id integer primary key autoincrement, comment text not null, " + "amount integer not null, cat_id integer, account_id integer, payee text, transfer_peer integer default null, " + "payment_method_id integer, title text not null);"); } if (oldVersion < 23) { db.execSQL("ALTER TABLE templates RENAME to templates_old"); db.execSQL("CREATE TABLE templates ( _id integer primary key autoincrement, comment text not null, " + "amount integer not null, cat_id integer, account_id integer, payee text, transfer_peer integer default null, " + "payment_method_id integer, title text not null, unique(account_id, title));"); try { db.execSQL( "INSERT INTO templates(comment,amount,cat_id,account_id,payee,transfer_peer,payment_method_id,title)" + " SELECT comment,amount,cat_id,account_id,payee,transfer_peer,payment_method_id,title FROM templates_old"); } catch (SQLiteConstraintException e) { Log.e(TAG, e.getLocalizedMessage()); //theoretically we could have entered duplicate titles for one account //we silently give up in that case (since this concerns only a narrowly distributed alpha version) } db.execSQL("DROP TABLE templates_old"); } if (oldVersion < 24) { db.execSQL("ALTER TABLE templates add column usages integer default 0"); } if (oldVersion < 25) { //for transactions that were not transfers, transfer_peer was set to null in transactions, but to 0 in templates db.execSQL("update transactions set transfer_peer=0 WHERE transfer_peer is null;"); } if (oldVersion < 26) { db.execSQL("alter table accounts add column color integer default -6697984"); } if (oldVersion < 27) { db.execSQL("CREATE TABLE feature_used (feature text not null);"); } if (oldVersion < 28) { db.execSQL("ALTER TABLE transactions RENAME to transactions_old"); db.execSQL( "CREATE TABLE transactions(_id integer primary key autoincrement, comment text, date datetime not null, amount integer not null, " + "cat_id integer references categories(_id), account_id integer not null references accounts(_id),payee text, " + "transfer_peer integer references transactions(_id), transfer_account integer references accounts(_id), " + "method_id integer references paymentmethods(_id));"); db.execSQL( "INSERT INTO transactions (_id,comment,date,amount,cat_id,account_id,payee,transfer_peer,transfer_account,method_id) " + "SELECT _id,comment,date,amount, " + "CASE WHEN transfer_peer THEN null ELSE CASE WHEN cat_id THEN cat_id ELSE null END END, " + "account_id,payee, " + "CASE WHEN transfer_peer THEN transfer_peer ELSE null END, " + "CASE WHEN transfer_peer THEN cat_id ELSE null END, " + "CASE WHEN payment_method_id THEN payment_method_id ELSE null END " + "FROM transactions_old"); db.execSQL("ALTER TABLE accounts RENAME to accounts_old"); db.execSQL( "CREATE TABLE accounts (_id integer primary key autoincrement, label text not null, opening_balance integer, description text, " + "currency text not null, type text not null check (type in ('CASH','BANK','CCARD','ASSET','LIABILITY')) default 'CASH', color integer default -3355444);"); db.execSQL("INSERT INTO accounts (_id,label,opening_balance,description,currency,type,color) " + "SELECT _id,label,opening_balance,description,currency,type,color FROM accounts_old"); //previously templates where not deleted if referred to accounts were deleted db.execSQL( "DELETE FROM templates where account_id not in (SELECT _id FROM accounts) or (cat_id != 0 and transfer_peer = 1 and cat_id not in (SELECT _id from accounts))"); db.execSQL("ALTER TABLE templates RENAME to templates_old"); db.execSQL( "CREATE TABLE templates ( _id integer primary key autoincrement, comment text not null, amount integer not null, " + "cat_id integer references categories(_id), account_id integer not null references accounts(_id),payee text, " + "transfer_peer boolean default false, transfer_account integer references accounts(_id),method_id integer references paymentmethods(_id), " + "title text not null, usages integer default 0, unique(account_id,title));"); db.execSQL( "INSERT INTO templates (_id,comment,amount,cat_id,account_id,payee,transfer_peer,transfer_account,method_id,title,usages) " + "SELECT _id,comment,amount," + "CASE WHEN transfer_peer THEN null ELSE CASE WHEN cat_id THEN cat_id ELSE null END END, " + "account_id,payee, " + "CASE WHEN transfer_peer THEN 1 ELSE 0 END, " + "CASE WHEN transfer_peer THEN cat_id ELSE null END, " + "CASE WHEN payment_method_id THEN payment_method_id ELSE null END, " + "title,usages FROM templates_old"); db.execSQL("ALTER TABLE categories RENAME to categories_old"); db.execSQL( "CREATE TABLE categories (_id integer primary key autoincrement, label text not null, parent_id integer references categories(_id), " + "usages integer default 0, unique (label,parent_id));"); db.execSQL("INSERT INTO categories (_id,label,parent_id,usages) " + "SELECT _id,label,CASE WHEN parent_id THEN parent_id ELSE null END,usages FROM categories_old"); db.execSQL("ALTER TABLE paymentmethods RENAME to paymentmethods_old"); db.execSQL( "CREATE TABLE paymentmethods (_id integer primary key autoincrement, label text not null, type integer check (type in (-1,0,1)) default 0);"); db.execSQL( "INSERT INTO paymentmethods (_id,label,type) SELECT _id,label,type FROM paymentmethods_old"); db.execSQL("ALTER TABLE accounttype_paymentmethod RENAME to accounttype_paymentmethod_old"); db.execSQL( "CREATE TABLE accounttype_paymentmethod (type text not null check (type in ('CASH','BANK','CCARD','ASSET','LIABILITY')), method_id integer references paymentmethods (_id), primary key (type,method_id));"); db.execSQL( "INSERT INTO accounttype_paymentmethod (type,method_id) SELECT type,method_id FROM accounttype_paymentmethod_old"); db.execSQL("DROP TABLE transactions_old"); db.execSQL("DROP TABLE accounts_old"); db.execSQL("DROP TABLE templates_old"); db.execSQL("DROP TABLE categories_old"); db.execSQL("DROP TABLE paymentmethods_old"); db.execSQL("DROP TABLE accounttype_paymentmethod_old"); //Changes to handle //1) Transfer account no longer stored as cat_id but in transfer_account (in transactions and templates) //2) parent_id for categories uses foreign key on itself, hence root categories have null instead of 0 as parent_id //3) catId etc now need to be null instead of 0 //4) transactions payment_method_id renamed to method_id } if (oldVersion < 29) { db.execSQL("ALTER TABLE transactions add column status integer default 0"); } if (oldVersion < 30) { db.execSQL("ALTER TABLE transactions add column parent_id integer references transactions (_id)"); // db.execSQL("CREATE VIEW committed AS SELECT * FROM transactions WHERE status != 2;"); // db.execSQL("CREATE VIEW uncommitted AS SELECT * FROM transactions WHERE status = 2;"); ContentValues initialValues = new ContentValues(); initialValues.put("_id", 0); initialValues.put("parent_id", 0); initialValues.put("label", "__SPLIT_TRANSACTION__"); db.insert("categories", null, initialValues); } if (oldVersion < 31) { //in an alpha version distributed on Google Play, we had SPLIT_CATID as -1 ContentValues initialValues = new ContentValues(); initialValues.put("_id", 0); initialValues.put("parent_id", 0); db.update("categories", initialValues, "_id=-1", null); } if (oldVersion < 32) { db.execSQL("ALTER TABLE accounts add column grouping text not null check (grouping in " + "('NONE','DAY','WEEK','MONTH','YEAR')) default 'NONE'"); } if (oldVersion < 33) { db.execSQL("ALTER TABLE accounts add column usages integer default 0"); db.execSQL( "UPDATE accounts SET usages = (SELECT count(*) FROM transactions WHERE account_id = accounts._id AND parent_id IS null)"); } if (oldVersion < 34) { //fix for https://github.com/mtotschnig/MyExpenses/issues/69 db.execSQL( "UPDATE transactions set date = (SELECT date from transactions parent WHERE parent._id = transactions.parent_id) WHERE parent_id IS NOT null"); } if (oldVersion < 35) { db.execSQL( "ALTER TABLE transactions add column cr_status text not null check (cr_status in ('UNRECONCILED','CLEARED','RECONCILED')) default 'UNRECONCILED'"); } if (oldVersion < 36) { //move payee field in transactions from text to foreign key db.execSQL("ALTER TABLE transactions RENAME to transactions_old"); db.execSQL("CREATE TABLE transactions (" + " _id integer primary key autoincrement," + " comment text, date datetime not null," + " amount integer not null," + " cat_id integer references categories(_id)," + " account_id integer not null references accounts(_id)," + " payee_id integer references payee(_id)," + " transfer_peer integer references transactions(_id)," + " transfer_account integer references accounts(_id)," + " method_id integer references paymentmethods(_id)," + " parent_id integer references transactions(_id)," + " status integer default 0," + " cr_status text not null check (cr_status in ('UNRECONCILED','CLEARED','RECONCILED')) default 'RECONCILED')"); //insert all payees that are stored in transactions, but are not in payee db.execSQL( "INSERT INTO payee (name) SELECT DISTINCT payee FROM transactions_old WHERE payee != '' AND NOT exists (SELECT 1 FROM payee WHERE name=transactions_old.payee)"); db.execSQL("INSERT INTO transactions " + "(_id,comment,date,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,parent_id,status,cr_status) " + "SELECT " + "_id, " + "comment, " + "date, " + "amount, " + "cat_id, " + "account_id, " + "(SELECT _id from payee WHERE name = payee), " + "transfer_peer, " + "transfer_account, " + "method_id," + "parent_id," + "status," + "cr_status " + "FROM transactions_old"); db.execSQL("DROP TABLE transactions_old"); //move payee field in templates from text to foreign key db.execSQL("ALTER TABLE templates RENAME to templates_old"); db.execSQL("CREATE TABLE templates (" + " _id integer primary key autoincrement," + " comment text," + " amount integer not null," + " cat_id integer references categories(_id)," + " account_id integer not null references accounts(_id)," + " payee_id integer references payee(_id)," + " transfer_peer boolean default false," + " transfer_account integer references accounts(_id)," + " method_id integer references paymentmethods(_id)," + " title text not null," + " usages integer default 0," + " unique(account_id,title));"); //insert all payees that are stored in templates, but are not in payee db.execSQL( "INSERT INTO payee (name) SELECT DISTINCT payee FROM templates_old WHERE payee != '' AND NOT exists (SELECT 1 FROM payee WHERE name=templates_old.payee)"); db.execSQL("INSERT INTO templates " + "(_id,comment,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,title,usages) " + "SELECT " + "_id, " + "comment, " + "amount, " + "cat_id, " + "account_id, " + "(SELECT _id from payee WHERE name = payee), " + "transfer_peer, " + "transfer_account, " + "method_id," + "title," + "usages " + "FROM templates_old"); db.execSQL("DROP TABLE templates_old"); db.execSQL("DROP VIEW IF EXISTS committed"); db.execSQL("DROP VIEW IF EXISTS uncommitted"); //for the definition of the view, it is safe to rely on the constants, //since we will not alter the view, but drop it, and recreate it, if needed // String viewTransactions = VIEW_DEFINITION(TABLE_TRANSACTIONS); // db.execSQL("CREATE VIEW transactions_committed " + viewTransactions + " WHERE " + KEY_STATUS + " != " + STATUS_UNCOMMITTED + ";"); // db.execSQL("CREATE VIEW transactions_uncommitted" + viewTransactions + " WHERE " + KEY_STATUS + " = " + STATUS_UNCOMMITTED + ";"); // db.execSQL("CREATE VIEW transactions_all" + viewTransactions); // db.execSQL("CREATE VIEW templates_all" + VIEW_DEFINITION(TABLE_TEMPLATES)); } if (oldVersion < 37) { db.execSQL("ALTER TABLE transactions add column number text"); db.execSQL("ALTER TABLE paymentmethods add column is_numbered boolean default 0"); ContentValues initialValues = new ContentValues(); initialValues.put("is_numbered", true); db.update("paymentmethods", initialValues, "label = ?", new String[] { "CHEQUE" }); } if (oldVersion < 38) { db.execSQL("ALTER TABLE templates add column plan_id integer"); db.execSQL("ALTER TABLE templates add column plan_execution boolean default 0"); } if (oldVersion < 39) { // db.execSQL("CREATE VIEW transactions_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TRANSACTIONS) + " WHERE " + KEY_STATUS + " != " + STATUS_UNCOMMITTED + ";"); // db.execSQL("CREATE VIEW templates_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TEMPLATES)); db.execSQL( "CREATE TABLE currency (_id integer primary key autoincrement, code text unique not null);"); insertCurrencies(db); } if (oldVersion < 40) { //added currency to extended view db.execSQL("DROP VIEW IF EXISTS transactions_extended"); db.execSQL("DROP VIEW IF EXISTS templates_extended"); // db.execSQL("CREATE VIEW transactions_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TRANSACTIONS) + " WHERE " + KEY_STATUS + " != " + STATUS_UNCOMMITTED + ";"); // db.execSQL("CREATE VIEW templates_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TEMPLATES)); } if (oldVersion < 41) { db.execSQL("CREATE TABLE planinstance_transaction " + "(template_id integer references templates(_id), " + "instance_id integer, " + "transaction_id integer references transactions(_id), " + "primary key (instance_id,transaction_id));"); } if (oldVersion < 42) { //migrate date field to unix time stamp (UTC) db.execSQL("ALTER TABLE transactions RENAME to transactions_old"); db.execSQL("CREATE TABLE transactions (" + " _id integer primary key autoincrement," + " comment text, date datetime not null," + " amount integer not null," + " cat_id integer references categories(_id)," + " account_id integer not null references accounts(_id)," + " payee_id integer references payee(_id)," + " transfer_peer integer references transactions(_id)," + " transfer_account integer references accounts(_id)," + " method_id integer references paymentmethods(_id)," + " parent_id integer references transactions(_id)," + " status integer default 0," + " cr_status text not null check (cr_status in ('UNRECONCILED','CLEARED','RECONCILED')) default 'RECONCILED'," + " number text)"); db.execSQL("INSERT INTO transactions " + "(_id,comment,date,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,parent_id,status,cr_status,number) " + "SELECT " + "_id, " + "comment, " + "strftime('%s',date,'utc'), " + "amount, " + "cat_id, " + "account_id, " + "payee_id, " + "transfer_peer, " + "transfer_account, " + "method_id," + "parent_id," + "status," + "cr_status, " + "number " + "FROM transactions_old"); db.execSQL("DROP TABLE transactions_old"); } if (oldVersion < 43) { db.execSQL("UPDATE accounts set currency = 'ZMW' WHERE currency = 'ZMK'"); db.execSQL("UPDATE currency set code = 'ZMW' WHERE code = 'ZMK'"); } if (oldVersion < 44) { //add ON DELETE CASCADE //accounts table sort_key column db.execSQL("ALTER TABLE planinstance_transaction RENAME to planinstance_transaction_old"); db.execSQL("CREATE TABLE planinstance_transaction " + "(template_id integer references templates(_id) ON DELETE CASCADE, " + "instance_id integer, " + "transaction_id integer references transactions(_id) ON DELETE CASCADE, " + "primary key (instance_id,transaction_id));"); db.execSQL("INSERT INTO planinstance_transaction " + "(template_id,instance_id,transaction_id)" + "SELECT " + "template_id,instance_id,transaction_id FROM planinstance_transaction_old"); db.execSQL("DROP TABLE planinstance_transaction_old"); db.execSQL("ALTER TABLE transactions RENAME to transactions_old"); db.execSQL("CREATE TABLE transactions (" + " _id integer primary key autoincrement," + " comment text, date datetime not null," + " amount integer not null," + " cat_id integer references categories(_id)," + " account_id integer not null references accounts(_id) ON DELETE CASCADE," + " payee_id integer references payee(_id)," + " transfer_peer integer references transactions(_id)," + " transfer_account integer references accounts(_id)," + " method_id integer references paymentmethods(_id)," + " parent_id integer references transactions(_id) ON DELETE CASCADE," + " status integer default 0," + " cr_status text not null check (cr_status in ('UNRECONCILED','CLEARED','RECONCILED')) default 'RECONCILED'," + " number text)"); db.execSQL("INSERT INTO transactions " + "(_id,comment,date,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,parent_id,status,cr_status,number) " + "SELECT " + "_id, " + "comment, " + "date, " + "amount, " + "cat_id, " + "account_id, " + "payee_id, " + "transfer_peer, " + "transfer_account, " + "method_id," + "parent_id," + "status," + "cr_status, " + "number " + "FROM transactions_old"); db.execSQL("DROP TABLE transactions_old"); db.execSQL("ALTER TABLE templates RENAME to templates_old"); db.execSQL("CREATE TABLE templates (" + " _id integer primary key autoincrement," + " comment text," + " amount integer not null," + " cat_id integer references categories(_id)," + " account_id integer not null references accounts(_id) ON DELETE CASCADE," + " payee_id integer references payee(_id)," + " transfer_peer boolean default 0," + " transfer_account integer references accounts(_id) ON DELETE CASCADE," + " method_id integer references paymentmethods(_id)," + " title text not null," + " usages integer default 0," + " plan_id integer, " + " plan_execution boolean default 0, " + " unique(account_id,title));"); db.execSQL("INSERT INTO templates " + "(_id,comment,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,title,usages,plan_id,plan_execution) " + "SELECT " + "_id, " + "comment, " + "amount, " + "cat_id, " + "account_id, " + "payee_id, " + "transfer_peer, " + "transfer_account, " + "method_id," + "title," + "usages, " + "plan_id, " + "plan_execution " + "FROM templates_old"); db.execSQL("ALTER TABLE accounts add column sort_key integer"); } if (oldVersion < 45) { db.execSQL("ALTER TABLE accounts add column exclude_from_totals boolean default 0"); //added to extended view db.execSQL("DROP VIEW IF EXISTS transactions_extended"); db.execSQL("DROP VIEW IF EXISTS templates_extended"); // db.execSQL("CREATE VIEW transactions_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TRANSACTIONS) + " WHERE " + KEY_STATUS + " != " + STATUS_UNCOMMITTED + ";"); // db.execSQL("CREATE VIEW templates_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TEMPLATES)); } if (oldVersion < 46) { db.execSQL("ALTER TABLE payee add column name_normalized text"); Cursor c = db.query("payee", new String[] { "_id", "name" }, null, null, null, null, null); if (c != null) { if (c.moveToFirst()) { ContentValues v = new ContentValues(); while (c.getPosition() < c.getCount()) { v.put("name_normalized", Utils.normalize(c.getString(1))); db.update("payee", v, "_id = " + c.getLong(0), null); c.moveToNext(); } } c.close(); } } if (oldVersion < 47) { db.execSQL("ALTER TABLE templates add column uuid text"); db.execSQL(EVENT_CACHE_CREATE); } if (oldVersion < 48) { //added method_label to extended view //do not comment out, since it is needed by the uuid update refreshViews(db); //need to inline to protect against later renames if (oldVersion < 47) { String[] projection = new String[] { "templates._id", "amount", "comment", "cat_id", "CASE WHEN " + " " + "transfer_peer" + " " + " THEN " + " (SELECT " + "label" + " FROM " + "accounts" + " WHERE " + "_id" + " = " + "transfer_account" + ") " + " ELSE " + " CASE WHEN " + " (SELECT " + "parent_id" + " FROM " + "categories" + " WHERE " + "_id" + " = " + "cat_id" + ") " + " THEN " + " (SELECT " + "label" + " FROM " + "categories" + " WHERE " + "_id" + " = " + " (SELECT " + "parent_id" + " FROM " + "categories" + " WHERE " + "_id" + " = " + "cat_id" + ")) " + " || ' : ' || " + " (SELECT " + "label" + " FROM " + "categories" + " WHERE " + "_id" + " = " + "cat_id" + ") " + " ELSE" + " (SELECT " + "label" + " FROM " + "categories" + " WHERE " + "_id" + " = " + "cat_id" + ") " + " END " + " END AS " + "label", "name", "transfer_peer", "transfer_account", "account_id", "method_id", "paymentmethods.label AS method_label", "title", "plan_id", "plan_execution", "uuid", "currency" }; SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); qb.setTables("templates LEFT JOIN payee ON payee_id = payee._id" + " LEFT JOIN accounts ON account_id = accounts._id" + " LEFT JOIN paymentmethods ON method_id = paymentmethods._id"); Cursor c = qb.query(db, projection, null, null, null, null, null); if (c != null) { if (c.moveToFirst()) { ContentValues templateValues = new ContentValues(), eventValues = new ContentValues(); String planCalendarId = MyApplication.getInstance().checkPlanner(); while (c.getPosition() < c.getCount()) { Template t = new Template(c); templateValues.put(DatabaseConstants.KEY_UUID, t.getUuid()); long templateId = c.getLong(c.getColumnIndex("_id")); long planId = c.getLong(c.getColumnIndex("plan_id")); eventValues.put(Events.DESCRIPTION, t.compileDescription(mCtx)); db.update("templates", templateValues, "_id = " + templateId, null); if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.ICE_CREAM_SANDWICH) { try { mCtx.getContentResolver().update(Events.CONTENT_URI, eventValues, Events._ID + "= ? AND " + Events.CALENDAR_ID + " = ?", new String[] { String.valueOf(planId), planCalendarId }); } catch (Exception e) { //fails with IllegalArgumentException on 2.x devices, //since the same uri works for inserting and querying //but also on HUAWEI Y530-U00 with 4.3 //probably SecurityException could arise here } } c.moveToNext(); } } c.close(); } } } if (oldVersion < 49) { //forgotten to drop in previous upgrade db.execSQL("DROP TABLE IF EXISTS templates_old"); } if (oldVersion < 50) { db.execSQL("ALTER TABLE transactions add column picture_id text"); db.execSQL("DROP TABLE IF EXISTS feature_used"); } if (oldVersion < 51) { File pictureDir = Utils.getPictureDir(false); //fallback if not mounted if (pictureDir == null) { pictureDir = new File( Environment.getExternalStorageDirectory().getPath() + "/Android/data/" + MyApplication.getInstance().getPackageName() + "/files", Environment.DIRECTORY_PICTURES); } if (!pictureDir.exists()) { AcraHelper.report(new Exception("Unable to calculate pictureDir during upgrade")); } //if pictureDir does not exist, we use its URI nonetheless, in order to have the data around //for potential trouble handling String prefix = Uri.fromFile(pictureDir).toString() + "/"; String postfix = ".jpg"; //if picture_id concat expression will also be null db.execSQL("UPDATE transactions set picture_id = '" + prefix + "'||picture_id||'" + postfix + "'"); db.execSQL("CREATE TABLE stale_uris ( picture_id text);"); db.execSQL( "CREATE TRIGGER cache_stale_uri BEFORE DELETE ON transactions WHEN old.picture_id NOT NULL " + " BEGIN INSERT INTO stale_uris VALUES (old.picture_id); END"); } if (oldVersion < 52) { db.execSQL("CREATE INDEX transactions_cat_id_index on transactions(cat_id)"); db.execSQL("CREATE INDEX templates_cat_id_index on templates(cat_id)"); } if (oldVersion < 53) { //add VOID status db.execSQL("ALTER TABLE transactions RENAME to transactions_old"); db.execSQL("CREATE TABLE " + "transactions" + "( " + "_id" + " integer primary key autoincrement, " + "comment" + " text, " + "date" + " datetime not null, " + "amount" + " integer not null, " + "cat_id" + " integer references " + "categories" + "(" + "_id" + "), " + "account_id" + " integer not null references " + "accounts" + "(" + "_id" + ") ON DELETE CASCADE," + "payee_id" + " integer references " + "payee" + "(" + "_id" + "), " + "transfer_peer" + " integer references " + "transactions" + "(" + "_id" + "), " + "transfer_account" + " integer references " + "accounts" + "(" + "_id" + ")," + "method_id" + " integer references " + "paymentmethods" + "(" + "_id" + ")," + "parent_id" + " integer references " + "transactions" + "(" + "_id" + ") ON DELETE CASCADE, " + "status" + " integer default 0, " + "cr_status" + " text not null check (" + "cr_status" + " in ('UNRECONCILED','CLEARED','RECONCILED','VOID')) default 'RECONCILED', " + "number" + " text, " + "picture_id" + " text);"); db.execSQL("INSERT INTO transactions " + "(_id,comment,date,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,parent_id,status,cr_status,number,picture_id) " + "SELECT " + "_id, " + "comment, " + "date, " + "amount, " + "cat_id, " + "account_id, " + "payee_id, " + "transfer_peer, " + "transfer_account, " + "method_id," + "parent_id," + "status," + "cr_status, " + "number, " + "picture_id " + "FROM transactions_old"); db.execSQL("DROP TABLE transactions_old"); db.execSQL( "CREATE TRIGGER cache_stale_uri BEFORE DELETE ON transactions WHEN old.picture_id NOT NULL " + " BEGIN INSERT INTO stale_uris VALUES (old.picture_id); END"); db.execSQL("CREATE INDEX transactions_cat_id_index on transactions(cat_id)"); } if (oldVersion < 54) { db.execSQL("DROP TRIGGER cache_stale_uri"); db.execSQL("CREATE TRIGGER cache_stale_uri " + "AFTER DELETE ON " + "transactions" + " " + "WHEN old." + "picture_id" + " NOT NULL " + "AND NOT EXISTS " + "(SELECT 1 FROM " + "transactions" + " " + "WHERE " + "picture_id" + " = old." + "picture_id" + ") " + "BEGIN INSERT INTO " + "stale_uris" + " VALUES (old." + "picture_id" + "); END"); //all Accounts with old default color are updated to the new one db.execSQL(String.format(Locale.US, "UPDATE accounts set color = %d WHERE color = %d", 0xff009688, 0xff99CC00)); } if (oldVersion < 55) { db.execSQL("ALTER TABLE categories add column label_normalized text"); Cursor c = db.query("categories", new String[] { "_id", "label" }, null, null, null, null, null); if (c != null) { if (c.moveToFirst()) { ContentValues v = new ContentValues(); while (c.getPosition() < c.getCount()) { v.put("label_normalized", Utils.normalize(c.getString(1))); db.update("categories", v, "_id = " + c.getLong(0), null); c.moveToNext(); } } c.close(); } } if (oldVersion < 56) { db.execSQL("ALTER TABLE templates add column last_used datetime"); db.execSQL("ALTER TABLE categories add column last_used datetime"); db.execSQL("ALTER TABLE accounts add column last_used datetime"); db.execSQL("CREATE TRIGGER sort_key_default AFTER INSERT ON accounts " + "BEGIN UPDATE accounts SET sort_key = (SELECT coalesce(max(sort_key),0) FROM accounts) + 1 " + "WHERE _id = NEW._id; END"); //The sort key could be set by user in previous versions, now it is handled internally Cursor c = db.query("accounts", new String[] { "_id", "sort_key" }, null, null, null, null, "sort_key ASC"); boolean hasAccountSortKeySet = false; if (c != null) { if (c.moveToFirst()) { ContentValues v = new ContentValues(); while (c.getPosition() < c.getCount()) { v.put("sort_key", c.getPosition() + 1); db.update("accounts", v, "_id = ?", new String[] { c.getString(0) }); if (c.getInt(1) != 0) hasAccountSortKeySet = true; c.moveToNext(); } } c.close(); } String legacy = PrefKey.SORT_ORDER_LEGACY.getString("USAGES"); PrefKey.SORT_ORDER_TEMPLATES.putString(legacy); PrefKey.SORT_ORDER_CATEGORIES.putString(legacy); PrefKey.SORT_ORDER_ACCOUNTS.putString(hasAccountSortKeySet ? "CUSTOM" : legacy); PrefKey.SORT_ORDER_LEGACY.remove(); } } catch (SQLException e) { throw Utils.hasApiLevel(Build.VERSION_CODES.JELLY_BEAN) ? new SQLiteUpgradeFailedException("Database upgrade failed", e) : e; } if (oldVersion < 57) { //fix custom app uris if (ContextCompat.checkSelfPermission(mCtx, Manifest.permission.WRITE_CALENDAR) == PackageManager.PERMISSION_GRANTED) { Cursor c = db.query("templates", new String[] { "_id", "plan_id" }, "plan_id IS NOT null", null, null, null, null); if (c != null) { if (c.moveToFirst()) { while (!c.isAfterLast()) { Plan.updateCustomAppUri(c.getLong(1), Template.buildCustomAppUri(c.getLong(0))); c.moveToNext(); } } c.close(); } } //Drop unique constraint on templates db.execSQL("ALTER TABLE templates RENAME to templates_old"); db.execSQL("CREATE TABLE templates (" + " _id integer primary key autoincrement," + " comment text," + " amount integer not null," + " cat_id integer references categories(_id)," + " account_id integer not null references accounts(_id) ON DELETE CASCADE," + " payee_id integer references payee(_id)," + " transfer_peer boolean default 0," + " transfer_account integer references accounts(_id) ON DELETE CASCADE," + " method_id integer references paymentmethods(_id)," + " title text not null," + " usages integer default 0," + " plan_id integer, " + " plan_execution boolean default 0, " + " uuid text, " + " last_used datetime);"); db.execSQL("INSERT INTO templates " + "(_id,comment,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,title,usages,plan_id,plan_execution,uuid,last_used) " + "SELECT " + "_id, " + "comment, " + "amount, " + "cat_id, " + "account_id, " + "payee_id, " + "transfer_peer, " + "transfer_account, " + "method_id," + "title," + "usages, " + "plan_id, " + "plan_execution, uuid, last_used " + "FROM templates_old"); db.execSQL("DROP TABLE templates_old"); //Recreate changed views refreshViews(db); } if (oldVersion < 58) { //cache fraction digits Cursor c = db.rawQuery("SELECT distinct currency from accounts", null); if (c != null) { if (c.moveToFirst()) { while (!c.isAfterLast()) { Money.ensureFractionDigitsAreCached(Utils.getSaveInstance(c.getString(0))); c.moveToNext(); } } c.close(); } } }
From source file:it.bradipao.berengar.DbTool.java
public static int gson2db(SQLiteDatabase mDB, File jsonFile) { // vars/*from w ww . j a v a 2s .c om*/ int iTableNum = 0; FileReader fr = null; BufferedReader br = null; JsonReader jr = null; String name = null; String val = null; String mTable = null; String mTableSql = null; ArrayList<String> aFields = null; ArrayList<String> aValues = null; ContentValues cv = null; // file readers try { fr = new FileReader(jsonFile); br = new BufferedReader(fr); jr = new JsonReader(br); } catch (FileNotFoundException e) { Log.e(LOGTAG, "error in gson2db file readers", e); } // parsing try { // start database transaction mDB.beginTransaction(); // open root { jr.beginObject(); // iterate through root objects while (jr.hasNext()) { name = jr.nextName(); if (jr.peek() == JsonToken.NULL) jr.skipValue(); // number of tables else if (name.equals("tables_num")) { val = jr.nextString(); iTableNum = Integer.parseInt(val); if (GOLOG) Log.d(LOGTAG, "TABLE NUM : " + iTableNum); } // iterate through tables array else if (name.equals("tables")) { jr.beginArray(); while (jr.hasNext()) { // start table mTable = null; aFields = null; jr.beginObject(); while (jr.hasNext()) { name = jr.nextName(); if (jr.peek() == JsonToken.NULL) jr.skipValue(); // table name else if (name.equals("table_name")) { mTable = jr.nextString(); } // table sql else if (name.equals("table_sql")) { mTableSql = jr.nextString(); if ((mTable != null) && (mTableSql != null)) { mDB.execSQL("DROP TABLE IF EXISTS " + mTable); mDB.execSQL(mTableSql); if (GOLOG) Log.d(LOGTAG, "DROPPED AND CREATED TABLE : " + mTable); } } // iterate through columns name else if (name.equals("cols_name")) { jr.beginArray(); while (jr.hasNext()) { val = jr.nextString(); if (aFields == null) aFields = new ArrayList<String>(); aFields.add(val); } jr.endArray(); if (GOLOG) Log.d(LOGTAG, "COLUMN NAME : " + aFields.toString()); } // iterate through rows else if (name.equals("rows")) { jr.beginArray(); while (jr.hasNext()) { jr.beginArray(); // iterate through values in row aValues = null; cv = null; while (jr.hasNext()) { val = jr.nextString(); if (aValues == null) aValues = new ArrayList<String>(); aValues.add(val); } jr.endArray(); // add to database cv = new ContentValues(); for (int j = 0; j < aFields.size(); j++) cv.put(aFields.get(j), aValues.get(j)); mDB.insert(mTable, null, cv); if (GOLOG) Log.d(LOGTAG, "INSERT IN " + mTable + " : " + aValues.toString()); } jr.endArray(); } else jr.skipValue(); } // end table jr.endObject(); } jr.endArray(); } else jr.skipValue(); } // close root } jr.endObject(); jr.close(); // successfull transaction mDB.setTransactionSuccessful(); } catch (IOException e) { Log.e(LOGTAG, "error in gson2db gson parsing", e); } finally { mDB.endTransaction(); } return iTableNum; }
From source file:com.onesignal.GenerateNotification.java
static void createSummaryNotification(Context inContext, boolean updateSummary, JSONObject gcmBundle) { if (updateSummary) setStatics(inContext);// w ww . j a v a2 s.c o m String group = null; try { group = gcmBundle.getString("grp"); } catch (Throwable t) { } Random random = new Random(); PendingIntent summaryDeleteIntent = getNewActionPendingIntent(random.nextInt(), getNewBaseDeleteIntent(0).putExtra("summary", group)); OneSignalDbHelper dbHelper = new OneSignalDbHelper(currentContext); SQLiteDatabase writableDb = dbHelper.getWritableDatabase(); String[] retColumn = { NotificationTable.COLUMN_NAME_ANDROID_NOTIFICATION_ID, NotificationTable.COLUMN_NAME_FULL_DATA, NotificationTable.COLUMN_NAME_IS_SUMMARY, NotificationTable.COLUMN_NAME_TITLE, NotificationTable.COLUMN_NAME_MESSAGE }; String[] whereArgs = { group }; Cursor cursor = writableDb.query(NotificationTable.TABLE_NAME, retColumn, NotificationTable.COLUMN_NAME_GROUP_ID + " = ? AND " + // Where String NotificationTable.COLUMN_NAME_DISMISSED + " = 0 AND " + NotificationTable.COLUMN_NAME_OPENED + " = 0", whereArgs, null, // group by null, // filter by row groups NotificationTable._ID + " DESC" // sort order, new to old ); Notification summaryNotification; int summaryNotificationId = random.nextInt(); String firstFullData = null; Collection<SpannableString> summeryList = null; if (cursor.moveToFirst()) { SpannableString spannableString; summeryList = new ArrayList<SpannableString>(); do { if (cursor.getInt(cursor.getColumnIndex(NotificationTable.COLUMN_NAME_IS_SUMMARY)) == 1) summaryNotificationId = cursor .getInt(cursor.getColumnIndex(NotificationTable.COLUMN_NAME_ANDROID_NOTIFICATION_ID)); else { String title = cursor.getString(cursor.getColumnIndex(NotificationTable.COLUMN_NAME_TITLE)); if (title == null) title = ""; else title += " "; // Html.fromHtml("<strong>" + line1Title + "</strong> " + gcmBundle.getString("alert")); String msg = cursor.getString(cursor.getColumnIndex(NotificationTable.COLUMN_NAME_MESSAGE)); spannableString = new SpannableString(title + msg); if (title.length() > 0) spannableString.setSpan(new StyleSpan(android.graphics.Typeface.BOLD), 0, title.length(), 0); summeryList.add(spannableString); if (firstFullData == null) firstFullData = cursor .getString(cursor.getColumnIndex(NotificationTable.COLUMN_NAME_FULL_DATA)); } } while (cursor.moveToNext()); if (updateSummary) { try { gcmBundle = new JSONObject(firstFullData); } catch (JSONException e) { e.printStackTrace(); } } } if (summeryList != null && (!updateSummary || summeryList.size() > 1)) { int notificationCount = summeryList.size() + (updateSummary ? 0 : 1); String summaryMessage = null; if (gcmBundle.has("grp_msg")) { try { summaryMessage = gcmBundle.getString("grp_msg").replace("$[notif_count]", "" + notificationCount); } catch (Throwable t) { } } if (summaryMessage == null) summaryMessage = notificationCount + " new messages"; JSONObject summaryDataBundle = new JSONObject(); try { summaryDataBundle.put("alert", summaryMessage); } catch (JSONException e) { e.printStackTrace(); } Intent summaryIntent = getNewBaseIntent(summaryNotificationId).putExtra("summary", group) .putExtra("onesignal_data", summaryDataBundle.toString()); PendingIntent summaryContentIntent = getNewActionPendingIntent(random.nextInt(), summaryIntent); NotificationCompat.Builder summeryBuilder = getBaseNotificationCompatBuilder(gcmBundle, !updateSummary); summeryBuilder.setContentIntent(summaryContentIntent).setDeleteIntent(summaryDeleteIntent) .setContentTitle(currentContext.getPackageManager() .getApplicationLabel(currentContext.getApplicationInfo())) .setContentText(summaryMessage).setNumber(notificationCount).setOnlyAlertOnce(updateSummary) .setGroup(group).setGroupSummary(true); if (!updateSummary) summeryBuilder.setTicker(summaryMessage); NotificationCompat.InboxStyle inboxStyle = new NotificationCompat.InboxStyle(); String line1Title = null; // Add the latest notification to the summary if (!updateSummary) { try { line1Title = gcmBundle.getString("title"); } catch (Throwable t) { } if (line1Title == null) line1Title = ""; else line1Title += " "; String message = ""; try { message = gcmBundle.getString("alert"); } catch (Throwable t) { } SpannableString spannableString = new SpannableString(line1Title + message); if (line1Title.length() > 0) spannableString.setSpan(new StyleSpan(android.graphics.Typeface.BOLD), 0, line1Title.length(), 0); inboxStyle.addLine(spannableString); } for (SpannableString line : summeryList) inboxStyle.addLine(line); inboxStyle.setBigContentTitle(summaryMessage); summeryBuilder.setStyle(inboxStyle); summaryNotification = summeryBuilder.build(); } else { // There currently isn't a visible notification from this group, save the group summary notification id and post it so it looks like a normal notification. ContentValues values = new ContentValues(); values.put(NotificationTable.COLUMN_NAME_ANDROID_NOTIFICATION_ID, summaryNotificationId); values.put(NotificationTable.COLUMN_NAME_GROUP_ID, group); values.put(NotificationTable.COLUMN_NAME_IS_SUMMARY, 1); writableDb.insert(NotificationTable.TABLE_NAME, null, values); NotificationCompat.Builder notifBuilder = getBaseNotificationCompatBuilder(gcmBundle, !updateSummary); PendingIntent summaryContentIntent = getNewActionPendingIntent(random.nextInt(), getNewBaseIntent(summaryNotificationId).putExtra("onesignal_data", gcmBundle.toString()) .putExtra("summary", group)); addNotificationActionButtons(gcmBundle, notifBuilder, summaryNotificationId, group); notifBuilder.setContentIntent(summaryContentIntent).setDeleteIntent(summaryDeleteIntent) .setOnlyAlertOnce(updateSummary).setGroup(group).setGroupSummary(true); summaryNotification = notifBuilder.build(); } NotificationManagerCompat.from(currentContext).notify(summaryNotificationId, summaryNotification); cursor.close(); writableDb.close(); }
From source file:org.ohmage.db.DbHelper.java
/** * Utility method that populates the Survey and SurveyPrompt tables for the * campaign identified by campaignUrn and containing the given xml as * campaignXML.//from w ww . ja v a2 s . c om * * Note that this method takes a db handle so that it can be used in a * transaction. * * @param db * a handle to an existing writable db * @param campaignUrn * the urn of the campaign for which we're populating subtables * @param campaignXML * the XML for the campaign (not validated by this method) * @return * */ public boolean populateSurveysFromCampaignXML(SQLiteDatabase db, String campaignUrn, String campaignXML) { try { // dump all the surveys (and consequently survey prompts) before we // do anything // this is (perhaps surprisingly) desired behavior, as the surveys + // survey prompts // should always reflect the state of the campaign XML, valid or not db.delete(Tables.SURVEYS, Surveys.CAMPAIGN_URN + "=?", new String[] { campaignUrn }); // do a pass over the XML to gather surveys and survey prompts XmlPullParser xpp = Xml.newPullParser(); xpp.setInput(new ByteArrayInputStream(campaignXML.getBytes("UTF-8")), "UTF-8"); int eventType = xpp.getEventType(); String tagName; // various stacks to maintain state while walking through the xml // tree Stack<String> tagStack = new Stack<String>(); Survey curSurvey = null; // valid only within a survey, null // otherwise Vector<SurveyPrompt> prompts = new Vector<SurveyPrompt>(); // valid // only // within // a // survey, // empty // otherwise Vector<JSONObject> properties = new Vector<JSONObject>(); // valid // only // within // a // prompt, // empty // otherwise // iterate through the xml, paying attention only to surveys and // prompts // note that this does no validation outside of preventing itself // from crashing catastrophically while (eventType != XmlPullParser.END_DOCUMENT) { if (eventType == XmlPullParser.START_TAG) { tagName = xpp.getName(); tagStack.push(tagName); if (tagName.equalsIgnoreCase("survey")) { if (curSurvey != null) throw new XmlPullParserException("encountered a survey tag inside another survey tag"); curSurvey = new Survey(); curSurvey.mCampaignUrn = campaignUrn; } else if (tagName.equalsIgnoreCase("prompt")) { SurveyPrompt sp = new SurveyPrompt(); // FIXME: add the campaign + survey ID to make lookups // easier? prompts.add(sp); } else if (tagName.equalsIgnoreCase("property")) { properties.add(new JSONObject()); } } else if (eventType == XmlPullParser.TEXT) { if (tagStack.size() >= 2) { // we may be in an entity>property situation, so check // and assign accordingly if (tagStack.get(tagStack.size() - 2).equalsIgnoreCase("survey")) { // populating the current survey object with its // properties here if (tagStack.peek().equalsIgnoreCase("id")) curSurvey.mSurveyID = xpp.getText(); else if (tagStack.peek().equalsIgnoreCase("title")) curSurvey.mTitle = xpp.getText(); else if (tagStack.peek().equalsIgnoreCase("description")) curSurvey.mDescription = xpp.getText(); else if (tagStack.peek().equalsIgnoreCase("submitText")) curSurvey.mSubmitText = xpp.getText(); else if (tagStack.peek().equalsIgnoreCase("showSummary")) curSurvey.mShowSummary = xpp.getText().equals("true") ? true : false; else if (tagStack.peek().equalsIgnoreCase("editSummary")) curSurvey.mEditSummary = xpp.getText().equals("true") ? true : false; else if (tagStack.peek().equalsIgnoreCase("summaryText")) curSurvey.mSummaryText = xpp.getText(); else if (tagStack.peek().equalsIgnoreCase("introText")) curSurvey.mIntroText = xpp.getText(); else if (tagStack.peek().equalsIgnoreCase("anytime")) curSurvey.mAnytime = xpp.getText().equals("true") ? true : false; } else if (tagStack.get(tagStack.size() - 2).equalsIgnoreCase("prompt")) { SurveyPrompt sp = prompts.lastElement(); // populating the last encountered survey prompt // with its properties here if (tagStack.peek().equalsIgnoreCase("id")) sp.mPromptID = xpp.getText(); else if (tagStack.peek().equalsIgnoreCase("promptText")) sp.mPromptText = xpp.getText(); else if (tagStack.peek().equalsIgnoreCase("promptType")) sp.mPromptType = xpp.getText(); } else if (tagStack.get(tagStack.size() - 2).equalsIgnoreCase("property")) { JSONObject curProperty = properties.lastElement(); // populating the last encountered property if (tagStack.peek().equalsIgnoreCase("key")) curProperty.put("key", xpp.getText()); else if (tagStack.peek().equalsIgnoreCase("label")) curProperty.put("label", xpp.getText()); else if (tagStack.peek().equalsIgnoreCase("value")) curProperty.put("value", xpp.getText()); } } } else if (eventType == XmlPullParser.END_TAG) { tagName = xpp.getName(); tagStack.pop(); if (tagName.equalsIgnoreCase("survey")) { // store the current survey to the database long surveyPID = db.insert(Tables.SURVEYS, null, curSurvey.toCV()); // also store all the prompts we accumulated for it for (SurveyPrompt sp : prompts) { sp.mSurveyID = curSurvey.mSurveyID; sp.mSurveyPID = surveyPID; sp.mCompositeID = curSurvey.mCampaignUrn + ":" + curSurvey.mSurveyID; db.insert(Tables.SURVEY_PROMPTS, null, sp.toCV()); } // flush the prompts we've stored up so far prompts.clear(); // Create Streams here OhmagePDVManager.getInstance().createStreamForSurvey(campaignUrn, curSurvey.mSurveyID); // and clear us from being in any survey curSurvey = null; } else if (tagName.equalsIgnoreCase("prompt")) { SurveyPrompt sp = prompts.lastElement(); // update the current prompt with the collected // properties JSONArray propertyArray = new JSONArray(); for (JSONObject property : properties) propertyArray.put(property); // encode it as json and stuff it in the surveyprompt sp.mProperties = propertyArray.toString(); // and wipe the properties properties.clear(); } } eventType = xpp.next(); } } catch (UnsupportedEncodingException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } catch (XmlPullParserException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } catch (JSONException e) { // TODO Auto-generated catch block e.printStackTrace(); return false; } return true; }
From source file:org.ohmage.db.DbHelper.java
public boolean populatePromptsFromResponseJSON(SQLiteDatabase db, long responseRowID, String response, String campaignUrn, String surveyId) { try {/*from w w w.j a va 2 s. com*/ // create a list of metadata for this survey from the surveyprompts table // this will help in remapping values for single and multichoice prompts, etc. HashMap<String, SurveyPrompt> promptsMap = new HashMap<String, SurveyPrompt>(); List<SurveyPrompt> promptsList = SurveyPrompt.fromCursor(db.query(Tables.SURVEY_PROMPTS, null, SurveyPrompts.COMPOSITE_ID + "='" + campaignUrn + ":" + surveyId + "'", null, null, null, null)); // remap list to hashmap; i know this looks crazy, but it'll make lookups slightly faster and doesn't take much memory for (SurveyPrompt sp : promptsList) promptsMap.put(sp.mPromptID, sp); // convert response data to JSON for parsing JSONArray responseData = new JSONArray(response); // iterate through the responses and add them to the prompt table one by one for (int i = 0; i < responseData.length(); ++i) { // nab the jsonobject, which contains "prompt_id" and "value" JSONObject item = responseData.getJSONObject(i); // if the entry we're looking at doesn't include prompt_id or value, continue if (!item.has("prompt_id") || !item.has("value")) continue; // look up the metadata for this prompt SurveyPrompt promptData = promptsMap.get(item.getString("prompt_id")); // construct a new PromptResponse object to populate PromptResponse p = new PromptResponse(); p.mCompositeID = campaignUrn + ":" + surveyId; p.mResponseID = responseRowID; p.mPromptID = item.getString("prompt_id"); // determine too if we have to remap the value from a number to text // if custom_choices is included, then we do try { // before we determine what to do for this prompt, we need to see if we // have metadata. if we don't, we have to go for the default behavior. if (promptData == null) throw new NoMetadataException(); if (item.has("custom_choices")) { // build a hashmap of ID->label so we can do the remapping JSONArray choicesArray = item.getJSONArray("custom_choices"); HashMap<String, String> glossary = new HashMap<String, String>(); for (int iv = 0; iv < choicesArray.length(); ++iv) { JSONObject choiceObject = choicesArray.getJSONObject(iv); glossary.put(choiceObject.getString("choice_id"), choiceObject.getString("choice_value")); } // determine if the value is singular or an array // if it's an array, we need to remap each element try { JSONArray remapper = item.getJSONArray("value"); for (int ir = 0; ir < remapper.length(); ++ir) remapper.put(ir, glossary.get(remapper.getString(ir))); p.mValue = remapper.toString(); } catch (JSONException e) { // it wasn't a json array, so just remap the single value p.mValue = glossary.get(item.getString("value")); } } else if (promptData.mPromptType.equalsIgnoreCase("single_choice")) { // unload the json properties JSONArray values = new JSONArray(promptData.mProperties); // set the explicit value as the default; if we don't find a match, it'll end up as this p.mValue = item.getString("value"); // search for a key that matches the given value for (int ir = 0; ir < values.length(); ++ir) { JSONObject entry = values.getJSONObject(ir); if (entry.getString("key").equals(p.mValue)) { p.mValue = entry.getString("label"); p.mExtraValue = item.getString("value"); break; } } } else if (promptData.mPromptType.equalsIgnoreCase("multi_choice")) { // same procedure as above, except that we need to remap every value try { // unload the json properties JSONArray values = new JSONArray(promptData.mProperties); // set the explicit value as the default; if we don't find a match, it'll end up as this JSONArray newValues = new JSONArray(item.getString("value")); // for each entry in newValues... for (int io = 0; io < newValues.length(); ++io) { // search for a key that matches the given value for (int ir = 0; ir < values.length(); ++ir) { JSONObject entry = values.getJSONObject(ir); if (entry.getString("key").equals(newValues.getString(io))) { // assign the remapped value to this index newValues.put(io, entry.getString("label")); break; } } } // and reassign mValue here p.mValue = newValues.toString(); p.mExtraValue = item.getString("value"); } catch (JSONException e) { // it wasn't a json array, so just remap the value p.mValue = item.getString("value"); } } else { p.mValue = item.getString("value"); } } catch (NoMetadataException e) { Log.e(TAG, "Couldn't find the associated metadata for prompt ID " + (i + 1) + ", assigning default value"); p.mValue = item.getString("value"); } // and insert this into prompts db.insert(Tables.PROMPT_RESPONSES, null, p.toCV()); } } catch (JSONException e) { e.printStackTrace(); return false; } return true; }