Re: [sqlite] schema_version and Vacuum or Backup API

[Stephen Chrzanowski]

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 at 11:33 AM, R Smith  wrote:

> 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 create a new DB (FILE>NEW) in one go.
>>
>
> Bundle into one string while removing all white-space and case-only
> differences - this is almost like a hash in SQL terms:
>
> SELECT GROUP_CONCAT(upper(replace(replace(replace(replace(S.sql,'
> ',''),CHAR(09),''),CHAR(10),''),CHAR(13),'')),' ')
>   FROM (
> SELECT sql
>   FROM sqlite_master
>  WHERE sql IS NOT NULL
>  ORDER BY sql
>   ) AS S
> ;
>
> A schema would need to have a real change to look different to this query.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[R Smith]

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 create a new DB (FILE>NEW) in one go.


Bundle into one string while removing all white-space and case-only 
differences - this is almost like a hash in SQL terms:


SELECT GROUP_CONCAT(upper(replace(replace(replace(replace(S.sql,' 
',''),CHAR(09),''),CHAR(10),''),CHAR(13),'')),' ')

  FROM (
SELECT sql
  FROM sqlite_master
 WHERE sql IS NOT NULL
 ORDER BY sql
  ) AS S
;

A schema would need to have a real change to look different to this query.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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 same function name)


Two calls.

Dan.







On Thursday, 8 September, 2016 08:17, Richard Hipp said:


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 the schema, like this:

SELECT md5sum(sql) FROM (
   SELECT sql FROM sqlite_master
WHERE sql IS NOT NULL
ORDER BY sql
);

You will need to add the md5sum() aggregate function yourself.  A
sample implementation can be found in the SQLite source tree (used for
testing) here: https://www.sqlite.org/src/artifact/bdae822f2?ln=3894-3932

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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, 2016 08:17, Richard Hipp said:

> 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 the schema, like this:
> 
>SELECT md5sum(sql) FROM (
>   SELECT sql FROM sqlite_master
>WHERE sql IS NOT NULL
>ORDER BY sql
>);
> 
> You will need to add the md5sum() aggregate function yourself.  A
> sample implementation can be found in the SQLite source tree (used for
> testing) here: https://www.sqlite.org/src/artifact/bdae822f2?ln=3894-3932
> 
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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.  (Spent too much time in this thread, rather than doing work I'm
> > being paid to do. heh)
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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)
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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 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 this would work, but maybe one could add a trigger to
> sqlite_master that would automatically update the integer whenever
> something happens to literally change the schema. That way third party
> tools would keep the number up to date as well as your own custom code
> without needing a user defined function.
>
> >
> > Perhaps make your application schema-version number a hash of the SQL
> > for the schema, like this:
> >
> >SELECT md5sum(sql) FROM (
> >   SELECT sql FROM sqlite_master
> >WHERE sql IS NOT NULL
> >ORDER BY sql
> >);
> >
> > You will need to add the md5sum() aggregate function yourself.  A
> > sample implementation can be found in the SQLite source tree (used for
> > testing) here: https://www.sqlite.org/src/artifact/bdae822f2?ln=3894-
> 3932
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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 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 <david.raym...@tomtom.com> 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) and we do it
extensively.  Both the SQLite and the Fossil projects routinely use
unreleased trunk versions of the software for internal
mission-critical processes.

I'm typing this reply inside a Firefox nightly that is recompiled with
the latest trunk version of SQLite (or nearly the latest - I think I
rebuilt it yesterday).

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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 this would work, but maybe one could add a trigger to
sqlite_master that would automatically update the integer whenever
something happens to literally change the schema. That way third party
tools would keep the number up to date as well as your own custom code
without needing a user defined function.

>
> Perhaps make your application schema-version number a hash of the SQL
> for the schema, like this:
>
>SELECT md5sum(sql) FROM (
>   SELECT sql FROM sqlite_master
>WHERE sql IS NOT NULL
>ORDER BY sql
>);
>
> You will need to add the md5sum() aggregate function yourself.  A
> sample implementation can be found in the SQLite source tree (used for
> testing) here: https://www.sqlite.org/src/artifact/bdae822f2?ln=3894-3932
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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
previous version of the schema then reapply).  As a test for differences
between previous version and current, that'd probably work, yes, and
probably would use it, but I don't think I'd store the MD5 result.  I'd use
the result from two queries (One against previous, the other against
current) and make the decision from there.

Unfortunately I'm using the amalgamation DLL right now, and based on the
code you've highlighted, I don't think that code gets included.  Since I'm
not using C, I'd have to rebuild the Amalagamation to get my DLL, which
isn't a huge deal, but that means I've just introduced another dependency,
which is something I'd like to avoid.

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 create a new DB (FILE>NEW) in one go.

On Thu, Sep 8, 2016 at 10: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.
>
> Perhaps make your application schema-version number a hash of the SQL
> for the schema, like this:
>
>SELECT md5sum(sql) FROM (
>   SELECT sql FROM sqlite_master
>WHERE sql IS NOT NULL
>ORDER BY sql
>);
>
> You will need to add the md5sum() aggregate function yourself.  A
> sample implementation can be found in the SQLite source tree (used for
> testing) here: https://www.sqlite.org/src/artifact/bdae822f2?ln=3894-3932
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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) and we do it
extensively.  Both the SQLite and the Fossil projects routinely use
unreleased trunk versions of the software for internal
mission-critical processes.

I'm typing this reply inside a Firefox nightly that is recompiled with
the latest trunk version of SQLite (or nearly the latest - I think I
rebuilt it yesterday).

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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
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 version, since the schema changes infrequently.  You could
use sequential numbers, if you prefer.  The key point is that the
"schema version" used by the application should be separate and
distinct from the "schema version" used by SQLite internally.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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 the schema, like this:

   SELECT md5sum(sql) FROM (
  SELECT sql FROM sqlite_master
   WHERE sql IS NOT NULL
   ORDER BY sql
   );

You will need to add the md5sum() aggregate function yourself.  A
sample implementation can be found in the SQLite source tree (used for
testing) here: https://www.sqlite.org/src/artifact/bdae822f2?ln=3894-3932

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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
in version numbers be done automatically when I make a change in the 3rd
party DB management tool.  But after taking in the entire thread, I
obviously cannot rely on schema_version at all since too many things can
change it mid-application use, or my manipulating it could cause damage.

The only difference on my plate now is that instead of relying on
schema_version with a simple query and compare a previous number to the
current number, I need to do a comparison between "what was", and "what
is", and if found different, alert me that I might have to construct a SQL
statement to update.  I still have to come up with a way to effectively
alert me that a schema change was done, and I need to come up with a SQL
statement to correct the problem.


On Thu, Sep 8, 2016 at 8:47 AM, Richard Hipp  wrote:

> On 9/8/16, Stephen Chrzanowski  wrote:
>
> If you want to manually keep track of your schema version, I suggest
> creating a separate table in the database schema specifically for this
> purpose.
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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 schema version to the backup?

That might work.  But really, when you manually change "PRAGMA
schema_version" you always run the risk that some reader will miss the
fact that the schema has changed, fail to recompile a prepared
statement, then run the obsolete prepared statement in a way that
corrupts the database file.  It is difficult to construct a scenario
where that happens, but it is possible.  Hence, while this is likely
to work during testing, you could get occasional, unreproducible
failures after deployment.

If you want to manually keep track of your schema version, I suggest
creating a separate table in the database schema specifically for this
purpose.

In the Fossil application (http://fossil-scm.org/), the database
always contains a CONFIG table like this:

  CREATE TABLE config(
name TEXT PRIMARY KEY,
value CLOB,
mtime INTEGER
  ) WITHOUT ROWID;

This CONFIG table stores various configuration parameters, among which
is a schema version number.  Every time Fossil opens a new database
file, it checks the schema version number to know how to deal with
that particular database file and whether or not the schema needs to
be upgraded.

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 version, since the schema changes infrequently.  You could
use sequential numbers, if you prefer.  The key point is that the
"schema version" used by the application should be separate and
distinct from the "schema version" used by SQLite internally.

Note also that the application-level "schema version" only needs to
change when there is an application-relevant change to the schema.
So, for example, if the WITHOUT ROWID clause were omitted from the
CONFIG table above, that would not require an application-level schema
change, since the two table definitions are equivalent from the point
of view of all queries.  Likewise, the addition, modification, or
deletion of indexes does not change the application-level schema
version, since queries get the same result regardless of the available
indexes.  (Indexes do not change the result of queries, they only help
to obtain that result faster.)  Only schema changes that make a
difference to application queries should change the application-level
schema version.  In contrast, the SQLite internal schema version needs
to change whenever the database is VACUUM-ed or backed up, for
technical reasons, as discussed in previous emails.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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
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 schema version to the backup?

In my case, and for the application I'm tuning for this initial venture
into SQL revision control, I'm using the term "backup" incorrectly.  I use
the "backup" API to load the database into memory, and then at application
termination, I use the "backup" API to overwrite the database I just loaded
from disk.  I don't think I maintain a file handle to the file, but I'd
have to check.

On Thu, Sep 8, 2016 at 6:01 AM, Richard Hipp  wrote:

>
>
> There are no writes to the database files that result from an ATTACH
> or DETACH, so the "schema version" numbers do not change in that case.
> But all existing prepared statements do automatically get reprepared
> upon next use, which triggers SQLITE_SCHEMA messages in the error and
> warning log.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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 so all of the SQL statements need to be reparsed
>> and replanned, to take into account those changes.
>
> In other words, the "schema version number" value isn't there to count the
> number of times the schema has changed.  It's there to signal the query
> planner that it needs to replan everything.  The signal is used by other
> things than changes to the schema saved in the database.
>

There are no writes to the database files that result from an ATTACH
or DETACH, so the "schema version" numbers do not change in that case.
But all existing prepared statements do automatically get reprepared
upon next use, which triggers SQLITE_SCHEMA messages in the error and
warning log.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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 reparsed
> and replanned, to take into account those changes.

In other words, the "schema version number" value isn't there to count the 
number of times the schema has changed.  It's there to signal the query planner 
that it needs to replan everything.  The signal is used by other things than 
changes to the schema saved in the database.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[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.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[David Empson]

> 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, since our
>> app pretty much doesn't touch the schema except during DB creation. I know
>> the warnings are harmless, just curious where they are coming from :)
> 
> I found the same thing, with an obvious cause: every ATTACH DATABASE or 
> DETACH DATABASE triggers a schema change which requires recompiling every 
> prepared statement. SQLite does this automatically, 

[Oops, bumped the send button mid-sentence.]

SQLite does this automatically, but it outputs a message to the error log 
(which is not returned via the API as an error).

I ended up suppressing that specific error in my log handler as our application 
is regularly doing a lot of attach/detach operations and has a lot of prepared 
statements.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[David Empson]

> 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 know
> the warnings are harmless, just curious where they are coming from :)

I found the same thing, with an obvious cause: every ATTACH DATABASE or DETACH 
DATABASE triggers a schema change which requires recompiling every prepared 
statement. SQLite does this automatically, 
-- 
David Empson
demp...@emptech.co.nz
Snail mail: P.O. Box 27-103, Wellington 6141, New Zealand

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[Rowan Worth]

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 supposed to happen?
>
> The documentation says that the schema version
> | is incremented by SQLite whenever the database schema is modified (by
> | creating or dropping a table or index). The schema version is used by
> | SQLite each time a query is executed to ensure that the internal cache
> | of the schema used when compiling the SQL query matches the schema of
> | the database against which the compiled query is actually executed.
>
> Inside a compiled statement, tables and indexes are identified by their
> root page number.  When running VACUUM, this is likely to change.
>

Interesting! Is there other non-schema modifying operations that bump the
version? What about ANALYZE? Obviously the first time it is run on a DB it
*does* modify the schema, but what about subsequent runs?

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 know
the warnings are harmless, just curious where they are coming from :)

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[Stephen Chrzanowski]

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 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 SQLite whenever the database schema is modified (by
> | creating or dropping a table or index). The schema version is used by
> | SQLite each time a query is executed to ensure that the internal cache
> | of the schema used when compiling the SQL query matches the schema of
> | the database against which the compiled query is actually executed.
>
> Inside a compiled statement, tables and indexes are identified by their
> root page number.  When running VACUUM, this is likely to change.
>
> The backup code has this comment:
> /* Update the schema version field in the destination database. This
> ** is to make sure that the schema-version really does change in
> ** the case where the source and destination databases have the
> ** same schema version.
> */
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] schema_version and Vacuum or Backup API

[Clemens Ladisch]

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 SQLite whenever the database schema is modified (by
| creating or dropping a table or index). The schema version is used by
| SQLite each time a query is executed to ensure that the internal cache
| of the schema used when compiling the SQL query matches the schema of
| the database against which the compiled query is actually executed.

Inside a compiled statement, tables and indexes are identified by their
root page number.  When running VACUUM, this is likely to change.

The backup code has this comment:
/* Update the schema version field in the destination database. This
** is to make sure that the schema-version really does change in
** the case where the source and destination databases have the
** same schema version.
*/


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] schema_version and Vacuum or Backup API

[Stephen Chrzanowski]

I'm updating one of my units in Delphi to automatically output the schema
of any database I'm attempting to open while in the debugger for two
purposes.  One reason that when a user does a FILE> NEW, the most recent
database is created based on the SQL commands found in sqlite_master, and
the schema to create said database is stored in a resource file generated
pre-build and that resource file is included in the EXE.  The second
reason, at every launch, any database accessed with this unit gets its
current schema exported to a text file.  That way, while I'm messing around
with the database schema during development time, I have a history on what
was changed, and any changes I can check into my VCS.

To do all of this, 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?

I assumed (bad thing I know) that with vacuum, I'm not changing the actual
schema of the database, just reorganizing data within.  I understand that
the schema would be how the data is to be structured, not where the data is
to be stored.

I also assumed that the backup API would retain the schema_version since it
also isn't changing the schema... sorta.  It could be recreating the
database from scratch (Granted a schema change is going from nothing to
something), but then I'd assume schema_version would be current, 0 or 1,
not current+1.

This behavior seems to at least be in SQLite.DLL is 3.7.17.  I've upgraded
to 3.14.1 and I'm still seeing the same thing.

If this *IS* intended behavior, may I ask why it is?  Is it because I'm not
understanding something correctly?

I'd rather not rely on user_version.  Doing so would mean that, while in
development, I'd have to always get the current schema in text, compare it
against the previous schema if one exists, then do an operational WRITE on
the user_schema if something changes.  I'd rather rely on using my existing
external tools to design and maintain the database schema and have it bump
up schema_version, while the programs I'm writing just use the structures I
provided.  I'd also prefer to NOT force my applications to keep to the
platter for data access.  The volume of data I'm playing with isn't
significant, so RAM is nice, and saving access to my SSDs for writes is a
bonus.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users