Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I can understand the perceived notion that it is best for performance to do all this logic in SQL, but perhaps the simplest (least code) solution is the best one in this case? Why not attempt the update first, and then use <code>insertWithOnConflict()</code> with <code>CONFLICT_IGNORE</code> to do the insert (if necessary) and get the row id you need:</p> <pre><code>public Uri insert(Uri uri, ContentValues values) { final SQLiteDatabase db = mOpenHelper.getWritableDatabase(); String selection = "latitude=? AND longitude=?"; String[] selectionArgs = new String[] {values.getAsString("latitude"), values.getAsString("longitude")}; //Do an update if the constraints match db.update(DatabaseProperties.TABLE_NAME, values, selection, null); //This will return the id of the newly inserted row if no conflict //It will also return the offending row without modifying it if in conflict long id = db.insertWithOnConflict(DatabaseProperties.TABLE_NAME, null, values, CONFLICT_IGNORE); return ContentUris.withAppendedId(uri, id); } </code></pre> <p>A simpler solution would be to check the return value of <code>update()</code> and only do the insert if the affected count was zero, but then there would be a case where you could not obtain the id of the existing row without an additional select. This form of insert will always return to you the correct id to pass back in the <code>Uri</code>, and won't modify the database more than necessary.</p> <p>If you want to do a large number of these at once, you might look at the <code>bulkInsert()</code> method on your provider, where you can run multiple inserts inside a single transaction. In this case, since you don't need to return the <code>id</code> of the updated record, the "simpler" solution should work just fine:</p> <pre><code>public int bulkInsert(Uri uri, ContentValues[] values) { final SQLiteDatabase db = mOpenHelper.getWritableDatabase(); String selection = "latitude=? AND longitude=?"; String[] selectionArgs = null; int rowsAdded = 0; long rowId; db.beginTransaction(); try { for (ContentValues cv : values) { selectionArgs = new String[] {cv.getAsString("latitude"), cv.getAsString("longitude")}; int affected = db.update(DatabaseProperties.TABLE_NAME, cv, selection, selectionArgs); if (affected == 0) { rowId = db.insert(DatabaseProperties.TABLE_NAME, null, cv); if (rowId &gt; 0) rowsAdded++; } } db.setTransactionSuccessful(); } catch (SQLException ex) { Log.w(TAG, ex); } finally { db.endTransaction(); } return rowsAdded; } </code></pre> <p>In truth, the transaction code is what makes things faster by minimizing the number of times the database memory is written to the file, <code>bulkInsert()</code> just allows multiple <code>ContentValues</code> to be passed in with a single call to the provider.</p>
    singulars
    1. This table or related slice is empty.
    1. POAndroid/SQLite: Insert-Update table columns to keep the identifier
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USNathan Perrier
    1. USDevunwired
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POAndroid/SQLite: Insert-Update table columns to keep the identifier
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COI appreciate your idea but I am concerned that a frontend operation will take to long on a large number of items. Maybe, my fear is without reason...
      singulars
      1. PO
      1. USJJD
    2. COIf you are doing this over a large collection, you should implement the same logic inside of the `ContentProvider.bulkInsert()` method and use transactions to minimize the load on the actual database file I/O
      singulars
      1. PO
      1. USDevunwired
    3. COMeanwhile, I realized that the sequential `INSERT` yields a large performance problem (read [here](http://stackoverflow.com/questions/11818828/sqlite-how-to-limit-the-number-of-rows-based-on-the-timestamp)). How would you design the bulk `INSERT`?
      singulars
      1. PO
      1. USJJD
 

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