Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Dan Kennedy
On 09/09/2016 12:06 PM, Keith Medcalf wrote: Richard, Can a single sqlite3_create_function call define a function which is both a scalar function and an aggregate function, or are two calls to create_function required, one defining the scalar and the other the aggregate version? (using the s

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Keith Medcalf
Richard, Can a single sqlite3_create_function call define a function which is both a scalar function and an aggregate function, or are two calls to create_function required, one defining the scalar and the other the aggregate version? (using the same function name) On Thursday, 8 September,

Re: [sqlite] Clarification on "No Isolation On Same Db Connection"

2016-09-08 Thread Igor Tandetnik
On 9/8/2016 1:15 PM, Stephan Mueller wrote: " > I'd prefer to commit after each update " You can't commit on a single connection either, while there's an " unfinalized SELECT statement traversal going on. So you aren't gaining " anything by trying to interleave SELECT and updates on the same conn

Re: [sqlite] Clarification on "No Isolation On Same Db Connection"

2016-09-08 Thread Stephan Mueller
Thanks Keith, for the detailed response. My plan now is to update SQLite to a WAL-capable version, enable WAL mode, use two connections instead of one and wrap the SELECT in a transaction. With luck, I improve overall performance (because of WAL mode) as well as gaining correctness. Many thanks,

Re: [sqlite] Clarification on "No Isolation On Same Db Connection"

2016-09-08 Thread Stephan Mueller
Thanks Kevin. I had considered the temporary table approach, but with potentially millions of updates, I am concerned about the time spent writing the 100MB+ of temp data, spoiling the interactive user experience. However, I may try it (as you say, it should be simple) to see the actual performan

Re: [sqlite] Clarification on "No Isolation On Same Db Connection"

2016-09-08 Thread Stephan Mueller
Igor wrote: " On 9/7/2016 6:11 PM, Stephan Mueller wrote: " > I understand that a way to ensure "SELECT is unperturbed" semantics is to " > use separate connections for SELECT and updates. " If you go down that route, make sure you are using WAL journaling mode; " it won't work otherwise. I see t

Re: [sqlite] Clarification on "No Isolation On Same Db Connection"

2016-09-08 Thread Stephan Mueller
I sent this long-winded question. thanks, stephan(); -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Bob McFarlane Sent: Wednesday, September 07, 2016 5:10 PM To: 'SQLite mailing list' Subject: Re: [sqlite] Clarification on "No Iso

Re: [sqlite] Clarification on "No Isolation On Same Db Connection"

2016-09-08 Thread Kevin O'Gorman
It seems to me that the simplest, most portable approach for this sort of thing would to be having the SELECT create a temporary table of the desired actions, and not apply them until after the select has concluded. This would work in any database -- it does not depend on precise semantics of WAL,

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Scott Robison
Darn. Oh well. On Sep 8, 2016 9:34 AM, "Stephen Chrzanowski" wrote: > Apparently, no. Error is "cannot create trigger on system table". > > On Thu, Sep 8, 2016 at 11:19 AM, Stephen Chrzanowski > wrote: > > > Interesting idea. I'll try that on a scratch DB when I get the > > chance. (Spen

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Stephen Chrzanowski
Apparently, no. Error is "cannot create trigger on system table". On Thu, Sep 8, 2016 at 11:19 AM, Stephen Chrzanowski wrote: > Interesting idea. I'll try that on a scratch DB when I get the > chance. (Spent too much time in this thread, rather than doing work I'm > being paid to do. heh)

Re: [sqlite] Best way to "split" a large DB into two DBs

2016-09-08 Thread Dominique Devienne
On Wed, Sep 7, 2016 at 7:08 PM, Dan Kennedy wrote: > On 09/07/2016 11:08 PM, Dominique Devienne wrote: > >> Initial design was to copy the DB file (app is "shutdown", so no >> connection >> to that DB file). >> Drop the 5 big tables. Vaccum it (the new DB). Add the new tables. >> But the 25GB cop

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Stephen Chrzanowski
Interesting idea. I'll try that on a scratch DB when I get the chance. (Spent too much time in this thread, rather than doing work I'm being paid to do. heh) On Thu, Sep 8, 2016 at 11:07 AM, Scott Robison wrote: > On Sep 8, 2016 8:16 AM, "Richard Hipp" wrote: > > > > On 9/8/16, Stephen Ch

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread David Raymond
Roger. Since I don't deal with pre-release versions much I guess my brain automatically skipped over the timestamp and other number, as it's used to the (3.15.0) part being unique on its own. (Which it obviously can't be while in development) Don't mind me folks, nothing to see here, nothing t

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Scott Robison
On Sep 8, 2016 8:16 AM, "Richard Hipp" wrote: > > On 9/8/16, Stephen Chrzanowski wrote: > > > > However, the rabbit I was hoping to pull out of the hat was that the change > > in version numbers be done automatically when I make a change in the 3rd > > party DB management tool. I don't know if t

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Stephen Chrzanowski
That is kind of the line I was thinking of going. I don't want to rely on MD5 as an ID, as by chance I might get the same numbers back, especially if I revert back to an already bit-exact existing schema. (For instance, I do a schema change, apply the update, but then find I have to revert to the

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Richard Hipp
On 9/8/16, David Raymond wrote: > Random question from when I just loaded up that URL: Is it supposed to have > a futuristic SQLite version? > > SQLite Version: 2016-08-22 20:10:01 [7839519349] (3.15.0) Yes. That's called "dogfooding" (https://en.wikipedia.org/wiki/Eating_your_own_dog_food

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread David Raymond
Random question from when I just loaded up that URL: Is it supposed to have a futuristic SQLite version? SQLite Version: 2016-08-22 20:10:01 [7839519349] (3.15.0) -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sen

Re: [sqlite] Using Bitwise Logic In Query

2016-09-08 Thread Matthias-Christian Ott
On 2016-09-08 12:17, Paul Sanderson wrote: > How does this work for you to get all the even rows > > SELECT ROWID > FROM table > WHERE ROWID & 0x01 = 0x00 I think you should be able to create an expression index as well: https://www.sqlite.org/lang_createtable.html#rowid - Matthias-Christian _

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Richard Hipp
On 9/8/16, Stephen Chrzanowski wrote: > > However, the rabbit I was hoping to pull out of the hat was that the change > in version numbers be done automatically when I make a change in the 3rd > party DB management tool. Perhaps make your application schema-version number a hash of the SQL for th

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Stephen Chrzanowski
Suggestion noted, and accepted, but, implementation of database table vs user_schema, I'm not sure yet. I know your point isn't about where the data is, but reference to another mechanism that is available to me. Thanks. However, the rabbit I was hoping to pull out of the hat was that the change

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Richard Hipp
On 9/8/16, Stephen Chrzanowski wrote: > That said, since I know that the backup API will make the schema version > change, is it safe to read the schema_version at the beginning of the > application, retain it for the life time of the application, then after the > backup is written, rewrite the sc

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Stephen Chrzanowski
I build my queries usually via binding within the function I'm calling, which I don't think changes the schema version, since I'm only seeing a bump of +1 at application close during either a vacuum or the backup API call. That said, since I know that the backup API will make the schema version ch

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

2016-09-08 Thread Igor Korot
Laura, On Thu, Sep 8, 2016 at 5:22 AM, Laura BERGOENS wrote: > Hi everyone, > > So I've put indexes myself on the most used tables in my program, then > ANALYZE the db, and now it flies. > The automatic creation of index probably took a lot of time, and it affects > even more performance when it'

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

2016-09-08 Thread Stefan Evert
> On 6 Sep 2016, at 10:19, Stephan Beal wrote: > > A counter-opinion, though apparently in the small minority: i _absolutely > despise_ fixed-width web site layouts. +1, including layouts with fixed maximum width. ___ sqlite-users mailing list sqlit

Re: [sqlite] Using Bitwise Logic In Query

2016-09-08 Thread Clemens Ladisch
Simon Slavin wrote: > ... if your table has lots of rows and/or you do lots of queries like > this, you should consider keeping a copy of the lower 16 bits as > another integer column. In these case, you should consider using an expression index: sqlite> create table t(x,y); sqlite> create index

Re: [sqlite] Using Bitwise Logic In Query

2016-09-08 Thread Paul Sanderson
How does this work for you to get all the even rows SELECT ROWID FROM table WHERE ROWID & 0x01 = 0x00 Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit fo

Re: [sqlite] Using Bitwise Logic In Query

2016-09-08 Thread Simon Slavin
On 8 Sep 2016, at 11:07am, Dave Blake wrote: > Looking for the best way to query a table with an integer column by value > of the lower 16 bits of the data in that column. Does SQLite support > bitwise logic? Yes. You can use the following & | ~ << >> AND OR NOT SHIFTLEFT SHIFTRIGHT However

[sqlite] Using Bitwise Logic In Query

2016-09-08 Thread Dave Blake
Looking for the best way to query a table with an integer column by value of the lower 16 bits of the data in that column. Does SQLite support bitwise logic? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Richard Hipp
On 9/8/16, Simon Slavin wrote: > > On 8 Sep 2016, at 10:43am, Richard Hipp wrote: > >> On 9/7/16, David Empson wrote: >>> >>> every ATTACH DATABASE or >>> DETACH DATABASE triggers a schema change... >> >> That's because the set of tables and indexes available to the query >> planner changes, and

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Simon Slavin
On 8 Sep 2016, at 10:43am, Richard Hipp wrote: > On 9/7/16, David Empson wrote: >> >> every ATTACH DATABASE or >> DETACH DATABASE triggers a schema change... > > That's because the set of tables and indexes available to the query > planner changes, and so all of the SQL statements need to be

Re: [sqlite] schema_version and Vacuum or Backup API

2016-09-08 Thread Richard Hipp
On 9/7/16, David Empson wrote: > > every ATTACH DATABASE or > DETACH DATABASE triggers a schema change... That's because the set of tables and indexes available to the query planner changes, and so all of the SQL statements need to be reparsed and replanned, to take into account those changes. --

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

2016-09-08 Thread Laura BERGOENS
Hi everyone, So I've put indexes myself on the most used tables in my program, then ANALYZE the db, and now it flies. The automatic creation of index probably took a lot of time, and it affects even more performance when it's always same queries on same tables that are repeated over and over again

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

2016-09-08 Thread Simon Slavin
On 8 Sep 2016, at 7:27am, Laura BERGOENS wrote: > I took notes of everything you guys said, and I'll spend my morning doing > this : > > Running ANALYZE once per DB I'mUsing, Create indexes myself and contact DB > Browser to share them the thing. Do those two steps the other way around. ANALY