Re: [sqlite] schema_version and Vacuum or Backup API

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

Re: [sqlite] Using Bitwise Logic In Query

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] Query time execution difference between my application and SQLiteBrowser

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

[sqlite] Using Bitwise Logic In Query

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

Re: [sqlite] schema_version and Vacuum or Backup API

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

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

Re: [sqlite] Using Bitwise Logic In Query

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

Re: [sqlite] Using Bitwise Logic In Query

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

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

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

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

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. (I tend to call the 3rd party tool SQLiteBrowser since it's the name of the package when you install it

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

> 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. ___

Re: [sqlite] schema_version and Vacuum or Backup API

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

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

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 >

Re: [sqlite] schema_version and Vacuum or Backup API

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

Re: [sqlite] schema_version and Vacuum or Backup API

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

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

Re: [sqlite] schema_version and Vacuum or Backup API

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

Re: [sqlite] schema_version and Vacuum or Backup API

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"

Re: [sqlite] Using Bitwise Logic In Query

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

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 >

Re: [sqlite] schema_version and Vacuum or Backup API

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

Re: [sqlite] schema_version and Vacuum or Backup API

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"

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

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

Re: [sqlite] schema_version and Vacuum or Backup API

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

Re: [sqlite] schema_version and Vacuum or Backup API

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

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

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

Re: [sqlite] schema_version and Vacuum or Backup API

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

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

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

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

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

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

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

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

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

Re: [sqlite] schema_version and Vacuum or Backup API

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"

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