faster sqlite_query?

[gcue]

hmm too bad it doesnt auto adjust the ROW_IDs - can look weird

so if i display a sqlite db in a listview... and the sorting is different - and user wants to delete a record - i want to make sure i delete the right record... so i can display the ROW_ID in one of the columns i guess so i can delete record by row_id

[jchd]

Beware that hidden or explicit row id (which can be explicitely referred to with either "rowid", "oid" or "_rowid_") won't alias the name "row_id". Moreover since your table doesn't declare types nor primary key, the actual unique row identifier ("rowid", "oid" or "_rowid_") already exists but is hidden from "select * ..." statements.

You really should declare types in your create statement as a "best practice" recipe. As it seems to log apps usage, it's a good idea to default the timestamp (use ISO format for storage!). ID will allias rowid because it is declared integer primary key (int wouldn't work here) and can be used as a unique automatic identifier. All other columns are of type char (= text) since it seem to be suitable and none is mandatory (follows your own definition).

Recommended schema:

CREATE TABLE "aLog" (
  "ID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "Initials" CHAR,
  "Application" CHAR,
  "Date_Time" CHAR DEFAULT (datetime('now')),
  "Script_Dir" CHAR,
  "Asset" CHAR,
  "IP_Address_1" CHAR,
  "IP_Address_2" CHAR,
  "IP_Address_3" CHAR,
  "IP_Address_4" CHAR,
  "Physical_Location" CHAR);

Placing the ID in the first column is optional but it is traditional. Of course an index is automatically created on id as it is declared primary key, hence your queries will be way faster. If you wish you can name ID as row_id as before, it doesn't matter.

To convert your DB to this improved schema, the simplest is to use a good SQLite DB manager and edit the design accordingly. Don't forget to declare the index on ID as INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT (where bold is important).

[gcue]

thank you all for your help!

[jchd]

Make a copy of your DB first, so if anything doesn't work like you intend, you have a backup!

[gcue]

yep - ahha thanks again

[gcue]

is there a way to get a return RowID value when inserting a record?

[jchd]

Yes, by issuing _SQLite_LastInsertRowID right after insertion.

[gcue]

so convenient!

thanks again for your valuable help!

[jchd]

Not me: SQLite is the boss!

[jchd]

Note that if indeed you want to store the current timestamp whenever you insert a log entry and using my declaration of column date_time, then you can leave it alone completely (not listing it in the columns to insert into) and the current GMT timestamp will be stored automagically for you in the ISO format e.g. 2014-09-05 21:06:18 as string (SQLite doesn't have a datetime type). This is only an example.

This format is suitable for time queries (make an index on that column if you need that often) and is easily reformatted to the (weird) US or Klingon format with 2 lines of code, for display/print purposes.

[gcue]

pretty sick - thanks again

have a good weekend =)