Example usage for android.database.sqlite SQLiteStatement execute

List of usage examples for android.database.sqlite SQLiteStatement execute

  1. HOME
  2. Java
  3. android
  4. android.database.sqlite.*
  5. SQLiteStatement
  6. execute

Introduction

In this page you can find the example usage for android.database.sqlite SQLiteStatement execute.

Prototype

public void execute()

Source Link

Document

Execute this SQL statement, if it is not a SELECT / INSERT / DELETE / UPDATE, for example CREATE / DROP table, view, trigger, index etc.

Usage

From source file:com.ichi2.anki.SyncClient.java

private void updateDeck(JSONObject deckPayload) {
    try {//w w  w. j a  v a 2  s  .co  m
        JSONArray meta = deckPayload.getJSONArray("meta");

        // Update meta information
        String sqlMeta = "INSERT OR REPLACE INTO deckVars (key, value) VALUES(?,?)";
        SQLiteStatement statement = AnkiDatabaseManager.getDatabase(mDeck.getDeckPath()).getDatabase()
                .compileStatement(sqlMeta);
        int lenMeta = meta.length();
        for (int i = 0; i < lenMeta; i++) {
            JSONArray deckVar = meta.getJSONArray(i);

            // key
            statement.bindString(1, deckVar.getString(0));
            // value
            statement.bindString(2, deckVar.getString(1));

            statement.execute();
        }
        statement.close();

        // Update deck
        mDeck.updateFromJson(deckPayload);
    } catch (JSONException e) {
        Log.i(AnkiDroidApp.TAG, "JSONException = " + e.getMessage());
    }

}

From source file:com.ichi2.anki.SyncClient.java

private void updateSources(JSONArray sources) {
    String sql = "INSERT OR REPLACE INTO sources VALUES(?,?,?,?,?)";
    SQLiteStatement statement = AnkiDatabaseManager.getDatabase(mDeck.getDeckPath()).getDatabase()
            .compileStatement(sql);/*from  ww w.  j  a  v a 2 s.c o m*/
    int len = sources.length();
    for (int i = 0; i < len; i++) {
        try {
            JSONArray source = sources.getJSONArray(i);
            statement.bindLong(1, source.getLong(0));
            statement.bindString(2, source.getString(1));
            statement.bindDouble(3, source.getDouble(2));
            statement.bindDouble(4, source.getDouble(3));
            statement.bindString(5, source.getString(4));
            statement.execute();
        } catch (JSONException e) {
            Log.i(AnkiDroidApp.TAG, "JSONException = " + e.getMessage());
        }
    }
    statement.close();
}

From source file:com.ichi2.anki.SyncClient.java

private void updateMedia(JSONArray media) {
    AnkiDb ankiDB = AnkiDatabaseManager.getDatabase(mDeck.getDeckPath());
    ArrayList<String> mediaIds = new ArrayList<String>();

    String sql = "INSERT OR REPLACE INTO media (id, filename, size, created, originalPath, description) "
            + "VALUES(?,?,?,?,?,?)";
    SQLiteStatement statement = ankiDB.getDatabase().compileStatement(sql);
    int len = media.length();
    for (int i = 0; i < len; i++) {
        try {//from   w w  w  .j  av a  2  s.c o m
            JSONArray m = media.getJSONArray(i);

            // Grab media ids, to delete them later
            String id = m.getString(0);
            mediaIds.add(id);

            // id
            statement.bindString(1, id);
            // filename
            statement.bindString(2, m.getString(1));
            // size
            statement.bindString(3, m.getString(2));
            // created
            statement.bindDouble(4, m.getDouble(3));
            // originalPath
            statement.bindString(5, m.getString(4));
            // description
            statement.bindString(6, m.getString(5));

            statement.execute();
        } catch (JSONException e) {
            Log.i(AnkiDroidApp.TAG, "JSONException = " + e.getMessage());
        }
    }
    statement.close();

    ankiDB.getDatabase().execSQL("DELETE FROM mediaDeleted WHERE mediaId IN " + Utils.ids2str(mediaIds));
}

From source file:com.newsrob.EntryManager.java

private void deleteArticlesFromDb(final SyncJob job, final List<String> articleIdsToDeleteInDatabase) {
    if (articleIdsToDeleteInDatabase.isEmpty())
        return;/* w  w w. j  a va 2s.  c o  m*/

    Timing t2 = new Timing("Delete Articles From Db", ctx);

    job.setJobDescription("Cleaning up database");
    job.target = articleIdsToDeleteInDatabase.size();
    job.actual = 0;
    fireStatusUpdated();

    SQLiteDatabase db = databaseHelper.getDb();

    final String sql1 = "DELETE FROM " + Entries.TABLE_NAME + " WHERE " + Entries.__ID + "=?;";
    final String sql2 = "DELETE FROM " + EntryLabelAssociations.TABLE_NAME + " WHERE "
            + EntryLabelAssociations.ENTRY_ID + "=?;";

    final SQLiteStatement stmt1 = db.compileStatement(sql1);
    final SQLiteStatement stmt2 = db.compileStatement(sql2);

    try {

        // outter loop does the chunking and holds the transaction context
        while (!articleIdsToDeleteInDatabase.isEmpty()) {

            db.beginTransaction();

            while (!articleIdsToDeleteInDatabase.isEmpty()) {

                String id = articleIdsToDeleteInDatabase.remove(0);
                stmt1.bindString(1, id);
                stmt1.execute();
                stmt2.bindString(1, id);
                stmt2.execute();

                job.actual++;

                if (job.actual % 10 == 0)
                    fireStatusUpdated();

                // commit every 35 articles
                if (job.actual >= 35)
                    break;
            }

            db.setTransactionSuccessful();
            db.endTransaction();
        }
    } finally {
        stmt1.close();
        stmt2.close();
    }
    fireStatusUpdated();
    t2.stop();
}

From source file:com.ichi2.anki.SyncClient.java

private void updateFacts(JSONObject factsDict) {
    try {//from   w w  w  . jav  a  2  s .co  m
        AnkiDb ankiDB = AnkiDatabaseManager.getDatabase(mDeck.getDeckPath());
        JSONArray facts = factsDict.getJSONArray("facts");
        int lenFacts = facts.length();

        if (lenFacts > 0) {
            JSONArray fields = factsDict.getJSONArray("fields");
            int lenFields = fields.length();

            // Grab fact ids
            // They will be used later to recalculate the count of facts and to delete them from DB
            ArrayList<String> factIds = new ArrayList<String>();
            for (int i = 0; i < lenFacts; i++) {
                factIds.add(facts.getJSONArray(i).getString(0));
            }
            String factIdsString = Utils.ids2str(factIds);

            // Recalculate fact count
            mDeck.setFactCount((int) (mDeck.getFactCount() + (lenFacts
                    - ankiDB.queryScalar("SELECT COUNT(*) FROM facts WHERE id IN " + factIdsString))));

            // Update facts
            String sqlFact = "INSERT OR REPLACE INTO facts (id, modelId, created, modified, tags, spaceUntil, lastCardId)"
                    + " VALUES(?,?,?,?,?,?,?)";
            SQLiteStatement statement = ankiDB.getDatabase().compileStatement(sqlFact);
            for (int i = 0; i < lenFacts; i++) {
                JSONArray fact = facts.getJSONArray(i);

                // id
                statement.bindLong(1, fact.getLong(0));
                // modelId
                statement.bindLong(2, fact.getLong(1));
                // created
                statement.bindDouble(3, fact.getDouble(2));
                // modified
                statement.bindDouble(4, fact.getDouble(3));
                // tags
                statement.bindString(5, fact.getString(4));
                // spaceUntil
                statement.bindDouble(6, fact.getDouble(5));
                // lastCardId
                if (!fact.isNull(6)) {
                    statement.bindLong(7, fact.getLong(6));
                } else {
                    statement.bindNull(7);
                }

                statement.execute();
            }
            statement.close();

            // Update fields (and delete first the local ones, since ids may have changed)
            ankiDB.getDatabase().execSQL("DELETE FROM fields WHERE factId IN " + factIdsString);

            String sqlFields = "INSERT INTO fields (id, factId, fieldModelId, ordinal, value) VALUES(?,?,?,?,?)";
            statement = ankiDB.getDatabase().compileStatement(sqlFields);
            for (int i = 0; i < lenFields; i++) {
                JSONArray field = fields.getJSONArray(i);

                // id
                statement.bindLong(1, field.getLong(0));
                // factId
                statement.bindLong(2, field.getLong(1));
                // fieldModelId
                statement.bindLong(3, field.getLong(2));
                // ordinal
                statement.bindString(4, field.getString(3));
                // value
                statement.bindString(5, field.getString(4));

                statement.execute();
            }
            statement.close();

            // Delete inserted facts from deleted
            ankiDB.getDatabase().execSQL("DELETE FROM factsDeleted WHERE factId IN " + factIdsString);
        }
    } catch (JSONException e) {
        Log.i(AnkiDroidApp.TAG, "JSONException = " + e.getMessage());
    }
}

From source file:com.grazerss.EntryManager.java

private void deleteArticlesFromDb(final SyncJob job, final List<String> articleIdsToDeleteInDatabase) {
    if (articleIdsToDeleteInDatabase.isEmpty()) {
        return;//from  w w  w  .ja  v  a2s.c  o m
    }

    Timing t2 = new Timing("Delete Articles From Db", ctx);

    job.setJobDescription("Cleaning up database");
    job.target = articleIdsToDeleteInDatabase.size();
    job.actual = 0;
    fireStatusUpdated();

    SQLiteDatabase db = databaseHelper.getDb();

    final String sql1 = "DELETE FROM " + Entries.TABLE_NAME + " WHERE " + Entries.__ID + "=?;";
    final String sql2 = "DELETE FROM " + EntryLabelAssociations.TABLE_NAME + " WHERE "
            + EntryLabelAssociations.ENTRY_ID + "=?;";

    final SQLiteStatement stmt1 = db.compileStatement(sql1);
    final SQLiteStatement stmt2 = db.compileStatement(sql2);

    try {

        // outter loop does the chunking and holds the transaction context
        while (!articleIdsToDeleteInDatabase.isEmpty()) {

            db.beginTransaction();

            while (!articleIdsToDeleteInDatabase.isEmpty()) {

                String id = articleIdsToDeleteInDatabase.remove(0);
                stmt1.bindString(1, id);
                stmt1.execute();
                stmt2.bindString(1, id);
                stmt2.execute();

                job.actual++;

                if ((job.actual % 10) == 0) {
                    fireStatusUpdated();
                }

                // commit every 35 articles
                if (job.actual >= 35) {
                    break;
                }
            }

            db.setTransactionSuccessful();
            db.endTransaction();
        }
    } finally {
        stmt1.close();
        stmt2.close();
    }
    fireStatusUpdated();
    t2.stop();
}

From source file:com.ichi2.anki.SyncClient.java

private void updateHistory(JSONArray history) {
    String sql = "INSERT OR IGNORE INTO reviewHistory (cardId, time, lastInterval, nextInterval, ease, delay, "
            + "lastFactor, nextFactor, reps, thinkingTime, yesCount, noCount) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
    SQLiteStatement statement = AnkiDatabaseManager.getDatabase(mDeck.getDeckPath()).getDatabase()
            .compileStatement(sql);/*from   w  ww.  j  av  a2s  .  c  om*/
    int len = history.length();
    for (int i = 0; i < len; i++) {
        try {
            JSONArray h = history.getJSONArray(i);

            // cardId
            statement.bindLong(1, h.getLong(0));
            // time
            statement.bindDouble(2, h.getDouble(1));
            // lastInterval
            statement.bindDouble(3, h.getDouble(2));
            // nextInterval
            statement.bindDouble(4, h.getDouble(3));
            // ease
            statement.bindString(5, h.getString(4));
            // delay
            statement.bindDouble(6, h.getDouble(5));
            // lastFactor
            statement.bindDouble(7, h.getDouble(6));
            // nextFactor
            statement.bindDouble(8, h.getDouble(7));
            // reps
            statement.bindDouble(9, h.getDouble(8));
            // thinkingTime
            statement.bindDouble(10, h.getDouble(9));
            // yesCount
            statement.bindDouble(11, h.getDouble(10));
            // noCount
            statement.bindDouble(12, h.getDouble(11));

            statement.execute();
        } catch (JSONException e) {
            Log.i(AnkiDroidApp.TAG, "JSONException = " + e.getMessage());
        }
    }
    statement.close();
}

From source file:com.ichi2.anki.SyncClient.java

private void updateModels(JSONArray models) {
    ArrayList<String> insertedModelsIds = new ArrayList<String>();
    AnkiDb ankiDB = AnkiDatabaseManager.getDatabase(mDeck.getDeckPath());

    String sql = "INSERT OR REPLACE INTO models"
            + " (id, deckId, created, modified, tags, name, description, features, spacing, initialSpacing, source)"
            + " VALUES(?,?,?,?,?,?,?,?,?,?,?)";
    SQLiteStatement statement = ankiDB.getDatabase().compileStatement(sql);
    int len = models.length();
    for (int i = 0; i < len; i++) {
        try {//w  w  w . jav  a2  s . c om
            JSONObject model = models.getJSONObject(i);

            // id
            String id = model.getString("id");
            statement.bindString(1, id);
            // deckId
            statement.bindLong(2, model.getLong("deckId"));
            // created
            statement.bindDouble(3, model.getDouble("created"));
            // modified
            statement.bindDouble(4, model.getDouble("modified"));
            // tags
            statement.bindString(5, model.getString("tags"));
            // name
            statement.bindString(6, model.getString("name"));
            // description
            statement.bindString(7, model.getString("name"));
            // features
            statement.bindString(8, model.getString("features"));
            // spacing
            statement.bindDouble(9, model.getDouble("spacing"));
            // initialSpacing
            statement.bindDouble(10, model.getDouble("initialSpacing"));
            // source
            statement.bindLong(11, model.getLong("source"));

            statement.execute();

            insertedModelsIds.add(id);

            mergeFieldModels(id, model.getJSONArray("fieldModels"));
            mergeCardModels(id, model.getJSONArray("cardModels"));
        } catch (JSONException e) {
            Log.i(AnkiDroidApp.TAG, "JSONException = " + e.getMessage());
        }
    }
    statement.close();

    // Delete inserted models from modelsDeleted
    ankiDB.getDatabase()
            .execSQL("DELETE FROM modelsDeleted WHERE modelId IN " + Utils.ids2str(insertedModelsIds));
}

From source file:com.ichi2.anki.SyncClient.java

private void mergeFieldModels(String modelId, JSONArray fieldModels) {
    ArrayList<String> ids = new ArrayList<String>();
    AnkiDb ankiDB = AnkiDatabaseManager.getDatabase(mDeck.getDeckPath());

    String sql = "INSERT OR REPLACE INTO fieldModels VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    SQLiteStatement statement = ankiDB.getDatabase().compileStatement(sql);
    int len = fieldModels.length();
    for (int i = 0; i < len; i++) {
        try {/*w  w  w.  j a v  a 2  s.  co m*/
            JSONObject fieldModel = fieldModels.getJSONObject(i);

            // id
            String id = fieldModel.getString("id");
            statement.bindString(1, id);
            // ordinal
            statement.bindString(2, fieldModel.getString("ordinal"));
            // modelId
            statement.bindLong(3, fieldModel.getLong("modelId"));
            // name
            statement.bindString(4, fieldModel.getString("name"));
            // description
            statement.bindString(5, fieldModel.getString("description"));
            // features
            statement.bindString(6, fieldModel.getString("features"));
            // required
            statement.bindLong(7, Utils.booleanToInt(fieldModel.getBoolean("required")));
            // unique
            statement.bindLong(8, Utils.booleanToInt(fieldModel.getBoolean("unique")));
            // numeric
            statement.bindLong(9, Utils.booleanToInt(fieldModel.getBoolean("numeric")));
            // quizFontFamily
            if (fieldModel.isNull("quizFontFamily")) {
                statement.bindNull(10);
            } else {
                statement.bindString(10, fieldModel.getString("quizFontFamily"));
            }
            // quizFontSize
            if (fieldModel.isNull("quizFontSize")) {
                statement.bindNull(11);
            } else {
                statement.bindString(11, fieldModel.getString("quizFontSize"));
            }
            // quizFontColour
            if (fieldModel.isNull("quizFontColour")) {
                statement.bindNull(12);
            } else {
                statement.bindString(12, fieldModel.getString("quizFontColour"));
            }
            // editFontFamily
            if (fieldModel.isNull("editFontFamily")) {
                statement.bindNull(13);
            } else {
                statement.bindString(13, fieldModel.getString("editFontFamily"));
            }
            // editFontSize
            statement.bindString(14, fieldModel.getString("editFontSize"));

            statement.execute();

            ids.add(id);
        } catch (JSONException e) {
            Log.i(AnkiDroidApp.TAG, "JSONException");
        }
    }
    statement.close();

    // Delete field models that were not returned by the server
    ArrayList<String> fieldModelsIds = ankiDB.queryColumn(String.class,
            "SELECT id FROM fieldModels WHERE modelId = " + modelId, 0);

    for (String fieldModelId : fieldModelsIds) {
        if (!ids.contains(fieldModelId)) {
            mDeck.deleteFieldModel(modelId, fieldModelId);
        }
    }
}

From source file:com.ichi2.anki.SyncClient.java

private void updateCards(JSONArray cards) {
    int len = cards.length();
    if (len > 0) {
        AnkiDb ankiDB = AnkiDatabaseManager.getDatabase(mDeck.getDeckPath());
        ArrayList<String> ids = new ArrayList<String>();
        for (int i = 0; i < len; i++) {
            try {
                ids.add(cards.getJSONArray(i).getString(0));
            } catch (JSONException e) {
                Log.i(AnkiDroidApp.TAG, "JSONException = " + e.getMessage());
            }/* ww  w  .jav  a2 s  .c  om*/
        }
        String idsString = Utils.ids2str(ids);

        mDeck.setCardCount((int) (mDeck.getCardCount()
                + (len - ankiDB.queryScalar("SELECT COUNT(*) FROM cards WHERE id IN " + idsString))));

        String sql = "INSERT OR REPLACE INTO cards (id, factId, cardModelId, created, modified, tags, ordinal, "
                + "priority, interval, lastInterval, due, lastDue, factor, firstAnswered, reps, successive, "
                + "averageTime, reviewTime, youngEase0, youngEase1, youngEase2, youngEase3, youngEase4, "
                + "matureEase0, matureEase1, matureEase2, matureEase3, matureEase4, yesCount, noCount, question, "
                + "answer, lastFactor, spaceUntil, type, combinedDue, relativeDelay, isDue) "
                + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, 0, 0)";
        SQLiteStatement statement = ankiDB.getDatabase().compileStatement(sql);
        for (int i = 0; i < len; i++) {
            try {
                JSONArray card = cards.getJSONArray(i);

                // id
                statement.bindLong(1, card.getLong(0));
                // factId
                statement.bindLong(2, card.getLong(1));
                // cardModelId
                statement.bindLong(3, card.getLong(2));
                // created
                statement.bindDouble(4, card.getDouble(3));
                // modified
                statement.bindDouble(5, card.getDouble(4));
                // tags
                statement.bindString(6, card.getString(5));
                // ordinal
                statement.bindString(7, card.getString(6));
                // priority
                statement.bindString(8, card.getString(7));
                // interval
                statement.bindDouble(9, card.getDouble(8));
                // lastInterval
                statement.bindDouble(10, card.getDouble(9));
                // due
                statement.bindDouble(11, card.getDouble(10));
                // lastDue
                statement.bindDouble(12, card.getDouble(11));
                // factor
                statement.bindDouble(13, card.getDouble(12));
                // firstAnswered
                statement.bindDouble(14, card.getDouble(13));
                // reps
                statement.bindString(15, card.getString(14));
                // successive
                statement.bindString(16, card.getString(15));
                // averageTime
                statement.bindDouble(17, card.getDouble(16));
                // reviewTime
                statement.bindDouble(18, card.getDouble(17));
                // youngEase0
                statement.bindString(19, card.getString(18));
                // youngEase1
                statement.bindString(20, card.getString(19));
                // youngEase2
                statement.bindString(21, card.getString(20));
                // youngEase3
                statement.bindString(22, card.getString(21));
                // youngEase4
                statement.bindString(23, card.getString(22));
                // matureEase0
                statement.bindString(24, card.getString(23));
                // matureEase1
                statement.bindString(25, card.getString(24));
                // matureEase2
                statement.bindString(26, card.getString(25));
                // matureEase3
                statement.bindString(27, card.getString(26));
                // matureEase4
                statement.bindString(28, card.getString(27));
                // yesCount
                statement.bindString(29, card.getString(28));
                // noCount
                statement.bindString(30, card.getString(29));
                // question
                statement.bindString(31, card.getString(30));
                // answer
                statement.bindString(32, card.getString(31));
                // lastFactor
                statement.bindDouble(33, card.getDouble(32));
                // spaceUntil
                statement.bindDouble(34, card.getDouble(33));
                // type
                statement.bindString(35, card.getString(34));
                // combinedDue
                statement.bindString(36, card.getString(35));

                statement.execute();
            } catch (JSONException e) {
                Log.i(AnkiDroidApp.TAG, "JSONException = " + e.getMessage());
            }
        }
        statement.close();

        ankiDB.getDatabase().execSQL("DELETE FROM cardsDeleted WHERE cardId IN " + idsString);
    }
}