Re: [sqlite] schema_version and Vacuum or Backup API

Not what I expected, but this can of work for my dual purpose. I'll have to tune it just a little bit, but this will work both as a check for differences between prior and current, and give me a "one-liner transactionable" SQL command to give a FILE>NEW command. Appreciated. On Fri, Sep 9, 2016

Re: [sqlite] schema_version and Vacuum or Backup API

On 2016/09/08 4:59 PM, Stephen Chrzanowski wrote: Is there a way that I can get your sub-query (Reordered to have tables, then indexes, then views) to come out as one row? I can then have the application just do an easy string comparison, AND have a method to include the resource string to

Re: [sqlite] schema_version and Vacuum or Backup API

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

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] 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] 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

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] schema_version and Vacuum or Backup API

to see here... -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, September 08, 2016 10:47 AM To: SQLite mailing list Subject: Re: [sqlite] schema_version and Vacuum or Backup API On 9/8/16, David Raymond

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

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

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] schema_version and Vacuum or Backup API

Sent: Thursday, September 08, 2016 8:47 AM To: SQLite mailing list Subject: Re: [sqlite] schema_version and Vacuum or Backup API You can see this "Schema Version" on the antepenultimate status line at http://www.sqlite.org/src/stat for example. Fossil uses a date for the schema vers

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

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: > 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

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] 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] 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/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] schema_version and Vacuum or Backup API

> On 8/09/2016, at 3:36 PM, David Empson wrote: > > >> On 8/09/2016, at 3:23 PM, Rowan Worth wrote: >> >> We recently made sqlite's logging more visible in our application and we're >> seeing a lot more "schema has changed" warnings than I'd expect,

Re: [sqlite] schema_version and Vacuum or Backup API

> On 8/09/2016, at 3:23 PM, Rowan Worth wrote: > > We recently made sqlite's logging more visible in our application and we're > seeing a lot more "schema has changed" warnings than I'd expect, since our > app pretty much doesn't touch the schema except during DB creation. I

Re: [sqlite] schema_version and Vacuum or Backup API

On 6 September 2016 at 20:04, Clemens Ladisch wrote: > Stephen Chrzanowski wrote: > > [...] I'm relying on the results from "pragma > > schema_version". I've noticed that this bumps up every time I run a > vacuum > > or use the backup API against the database. Is this

Re: [sqlite] schema_version and Vacuum or Backup API

So by design then. Alright, thanks. Guess its plan B with user_schema. On Tue, Sep 6, 2016 at 8:04 AM, Clemens Ladisch wrote: > Stephen Chrzanowski wrote: > > [...] I'm relying on the results from "pragma > > schema_version". I've noticed that this bumps up every time I

Re: [sqlite] schema_version and Vacuum or Backup API

Stephen Chrzanowski wrote: > [...] I'm relying on the results from "pragma > schema_version". I've noticed that this bumps up every time I run a vacuum > or use the backup API against the database. Is this supposed to happen? The documentation says that the schema version | is incremented by