Databases
StackOverflow2013
Postsdbo
POAndroid/SQLite: Insert-Update table columns to keep the identifier
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAndroid/SQLite: Insert-Update table columns to keep the identifier
    primarykey
    data
    text
    <p>Currently, I am using the following statement to create a table in an SQLite database on an Android device.</p> <pre><code>CREATE TABLE IF NOT EXISTS 'locations' ( '_id' INTEGER PRIMARY KEY AUTOINCREMENT, 'name' TEXT, 'latitude' REAL, 'longitude' REAL, UNIQUE ( 'latitude', 'longitude' ) ON CONFLICT REPLACE ); </code></pre> <p>The conflict-clause at the end causes that rows are <strong>dropped</strong> when new inserts are done that come with the same coordinates. The <a href="http://www.sqlite.org/lang_conflict.html" rel="nofollow noreferrer">SQLite documentation</a> contains further information about the conflict-clause.</p> <p>Instead, I would like to <strong>keep</strong> the former rows and just <strong>update</strong> their columns. What is the most efficient way to do this in a Android/SQLite environment?</p> <ul> <li>As a conflict-clause in the <code>CREATE TABLE</code> statement.</li> <li>As an <code>INSERT</code> trigger.</li> <li>As a conditional clause in the <code>ContentProvider#insert</code> method.</li> <li>... any better you can think off</li> </ul> <p>I would think it is more performant to handle such conflicts within the database. Also, I find it hard to rewrite the <code>ContentProvider#insert</code> method to consider the <em>insert-update</em> scenario. Here is code of the <code>insert</code> method:</p> <pre><code>public Uri insert(Uri uri, ContentValues values) { final SQLiteDatabase db = mOpenHelper.getWritableDatabase(); long id = db.insert(DatabaseProperties.TABLE_NAME, null, values); return ContentUris.withAppendedId(uri, id); } </code></pre> <p>When data arrives from the backend all I do is inserting the data as follows.</p> <pre><code>getContentResolver.insert(CustomContract.Locations.CONTENT_URI, contentValues); </code></pre> <p>I have problems figuring out how to apply an alternative call to <code>ContentProvider#update</code> here. Additionally, this is not my favored solution anyways.</p> <hr> <p><strong>Edit:</strong></p> <p>@CommonsWare: I tried to implement your suggestion to use <code>INSERT OR REPLACE</code>. I came up with this ugly piece of code.</p> <pre><code>private static long insertOrReplace(SQLiteDatabase db, ContentValues values, String tableName) { final String COMMA_SPACE = ", "; StringBuilder columnsBuilder = new StringBuilder(); StringBuilder placeholdersBuilder = new StringBuilder(); List&lt;Object&gt; pureValues = new ArrayList&lt;Object&gt;(values.size()); Iterator&lt;Entry&lt;String, Object&gt;&gt; iterator = values.valueSet().iterator(); while (iterator.hasNext()) { Entry&lt;String, Object&gt; pair = iterator.next(); String column = pair.getKey(); columnsBuilder.append(column).append(COMMA_SPACE); placeholdersBuilder.append("?").append(COMMA_SPACE); Object value = pair.getValue(); pureValues.add(value); } final String columns = columnsBuilder.substring(0, columnsBuilder.length() - COMMA_SPACE.length()); final String placeholders = placeholderBuilder.substring(0, placeholdersBuilder.length() - COMMA_SPACE.length()); db.execSQL("INSERT OR REPLACE INTO " + tableName + "(" + columns + ") VALUES (" + placeholders + ")", pureValues.toArray()); // The last insert id retrieved here is not safe. Some other inserts can happen inbetween. Cursor cursor = db.rawQuery("SELECT * from SQLITE_SEQUENCE;", null); long lastId = INVALID_LAST_ID; if (cursor != null &amp;&amp; cursor.getCount() &gt; 0 &amp;&amp; cursor.moveToFirst()) { lastId = cursor.getLong(cursor.getColumnIndex("seq")); } cursor.close(); return lastId; } </code></pre> <p>When I check the SQLite database, however, equal columns are <strong>still removed and inserted with new ids</strong>. I do not understand why this happens and thought the reason is my conflict-clause. But the <a href="http://sqlite.org/lang_conflict.html" rel="nofollow noreferrer">documentation</a> states the opposite.</p> <blockquote> <p>The algorithm specified in the OR clause of an INSERT or UPDATE <strong>overrides</strong> any algorithm specified in a CREATE TABLE. If no algorithm is specified anywhere, the ABORT algorithm is used.</p> </blockquote> <p>Another disadvantage of this attempt is that you loose the value of the id which is return by an insert statement. To compensate this, I finally found an option to ask for the <code>last_insert_rowid</code>. It is as explained <a href="https://stackoverflow.com/a/4018593/356895">in the posts of dtmilano and swiz</a>. I am, however, not sure if this is safe since another insert can happen inbetween.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJJD
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAndroid/SQLite: Insert-Update table columns to keep the identifier
      1. USJJD
      1. VTBountyStart
    2. VO
      singulars
      1. POAndroid/SQLite: Insert-Update table columns to keep the identifier
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POAndroid/SQLite: Insert-Update table columns to keep the identifier
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHave you tried `INSERT OR REPLACE`?
      singulars
      1. POAndroid/SQLite: Insert-Update table columns to keep the identifier
      1. USCommonsWare
    2. COI guess you mean adding `INSERT OR REPLACE` on the `CREATE` statement. No, I have not since it is not written in the documentation. Could you please clarify what exactly do you recommend.
      singulars
      1. POAndroid/SQLite: Insert-Update table columns to keep the identifier
      1. USJJD
    3. CONo, I mean `INSERT OR REPLACE`. http://sqlite.org/lang_insert.html
      singulars
      1. POAndroid/SQLite: Insert-Update table columns to keep the identifier
      1. USCommonsWare
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload