Re: [sqlite] Is sqlite3_auto_extension() same compilation unit ruled out?

[petern]

Thanks David.  That works great for my immediate needs.  Per below, shell.c
doesn't even bother checking return code of sqlite3_create_function().
What could be simpler?

--from shell.c--
static void open_db(ShellState *p, int keepAlive){
  if( p->db==0 ){
sqlite3_initialize();
sqlite3_open(p->zDbFilename, &p->db);
globalDb = p->db;sqlite3_create_function
if( p->db && sqlite3_errcode(p->db)==SQLITE_OK ){
  sqlite3_create_function(p->db, "shellstatic", 0, SQLITE_UTF8, 0,
  shellstaticFunc, 0, 0);
}
if( p->db==0 || SQLITE_OK!=sqlite3_errcode(p->db) ){
  utf8_printf(stderr,"Error: unable to open database \"%s\": %s\n",
  p->zDbFilename, sqlite3_errmsg(p->db));
  if( keepAlive ) return;
  exit(1);
}
#ifndef SQLITE_OMIT_LOAD_EXTENSION
sqlite3_enable_load_extension(p->db, 1);
#endif
sqlite3_create_function(p->db, "readfile", 1, SQLITE_UTF8, 0,
readfileFunc, 0, 0);
sqlite3_create_function(p->db, "writefile", 2, SQLITE_UTF8, 0,
writefileFunc, 0, 0);
  }
}
---



On Mon, Jun 12, 2017 at 5:57 PM, David Burgess  wrote:

> Have a look at the way readfile() and writefile() is implemented in the
> sqlite interpreter.
>
> On Tue, Jun 13, 2017 at 10:38 AM, petern 
> wrote:
>
> > I have a situation where it would be convenient to locate externally
> > loadable SQLite extension code in the same compilation unit as the server
> > code.  Is there a way for server main() to load those extensions located
> > within its own compilation unit? Does the necessity of #including both
> > sqlite3.h and sqlite3ext.h with SQLITE_EXTENSION_INIT1 macro rule this
> out?
> >
> > Documentation suggests sqlite3_auto_extension() loads a statically linked
> > extension entrypoint but trying this on same compilation unit's
> > sqlite3_extension_init() entrypoint results in segfault.
> >
> > FYI, environment is Linux.
> > ___
> > 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

[sqlite] Query on bug fix for ticket 7ffd1ca1d2ad4ec

[Mohandas G Nayak]

Hi All,

We are using SQLite 3.7.16.1 in our product which has been released to market 
sometime back. 
We have been analysing releases of SQLite from 3.7.16 to 3.19.2 to see if we 
need to upgrade the SQLite 
for the software that is already deployed in the field. For this purpose we use 
release history of 
SQLite, go through the bug fixes done and check if we need to upgrade SQLite 
depending on the bug fix 
done.
While doing this analysis we came across this fix: "Use the correct affinity 
for columns of automatic 
indexes. Ticket 
7ffd1ca1d2ad4ec(https://www.sqlite.org/src/info/7ffd1ca1d2ad4ec)." which was 
fixed in 
3.17.0 The description of the bug fix is not detailed and hence we don't know 
if we need to upgrade 
SQLite version.
We use simple queries like 'select * from abc where col1=123 and col2=456'. We 
dont use any JOINs, 
VIEWs, nested queries. The tables we use do have primary keys and foreign keys. 
Please let us know in 
which cases the bug will impact and whether we may be affected.

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

Re: [sqlite] Outputting to CSV - row is not quoted when there are no spaces

[Richard Hipp]

SQLite does not provide that capability, that I recall.

But surely it would not be too difficult for you to do your own custom
patch, or even to write a short program to output the data in the
precise format you desire?

On 6/12/17, d...@dan.bz  wrote:
> Hi,
>
> When outputting to CSV with '.mode csv' is there a way that all rows can
> be quoted even if there are no spaces? For example, here is a 1 line
> from the output:
>
> spotify:track:5vlDIGBTQmlyfERBnJOnbJ,Kiso,Circles,100.019
>
> I would like it to output:
>
> "spotify:track:5vlDIGBTQmlyfERBnJOnbJ","Kiso","Circles",100.019
>
> Thanks!
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] INTEGER PRIMARY KEY

[David Burgess]

> But we have to preserve backwards compatibility - even with bugs
> like this.

​How about a new release? i.e. sqlite4
No backward compatibilty issues.​
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

[Scott Robison]

On Jun 12, 2017 8:26 PM, "Keith Medcalf"  wrote:


Additionally, declaring NOT NULL or NULL is ignored.  CHECK constraints are
honoured.  DEFAULT values are ignored.

so CREATE TABLE x(id INTEGER NULL PRIMARY KEY CHECK (id>1000) DEFAULT (-1));
&  CREATE TABLE x(id INTEGER NULL PRIMARY KEY CHECK (id>1000));
is CREATE TABLE x(id INTEGER PRIMARY KEY CHECK(id>1000));

This is because an "INTEGER PRIMARY KEY" has a computed default that you
cannot override, and the rowid must always be always NOT NULL ...


Thanks for the info. I'm assuming that the order of constraints is
unimportant so check could come before PK.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

[Scott Robison]

On Jun 12, 2017 5:43 PM, "Richard Hipp"  wrote:

On 6/13/17, Scott Robison  wrote:
>
> Is it fair to say that the rowid aliasing behavior does not require
> (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
> that order as the "type") as long as the type is INTEGER and the
> constraint PRIMARY KEY appears somewhere in the column's constraint
> list?

See https://www.sqlite.org/lang_createtable.html#rowid

   CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);

Columns x *not* a ROWID.  Life is complicated.  Sorry for the mess.
But we have to preserve backwards compatibility - even with bugs like
this.


One,  no problem.

Two, is "INTEGER PRIMARY KEY ASC" the same? I will try it tomorrow.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

[Keith Medcalf]

Additionally, declaring NOT NULL or NULL is ignored.  CHECK constraints are 
honoured.  DEFAULT values are ignored.

so CREATE TABLE x(id INTEGER NULL PRIMARY KEY CHECK (id>1000) DEFAULT (-1));
&  CREATE TABLE x(id INTEGER NULL PRIMARY KEY CHECK (id>1000));
is CREATE TABLE x(id INTEGER PRIMARY KEY CHECK(id>1000));

This is because an "INTEGER PRIMARY KEY" has a computed default that you cannot 
override, and the rowid must always be always NOT NULL ...

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Richard Hipp
> Sent: Monday, 12 June, 2017 17:44
> To: SQLite mailing list
> Subject: Re: [sqlite] INTEGER PRIMARY KEY
> 
> On 6/13/17, Scott Robison  wrote:
> >
> > Is it fair to say that the rowid aliasing behavior does not require
> > (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
> > that order as the "type") as long as the type is INTEGER and the
> > constraint PRIMARY KEY appears somewhere in the column's constraint
> > list?
> 
> See https://www.sqlite.org/lang_createtable.html#rowid
> 
>CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);
> 
> Columns x *not* a ROWID.  Life is complicated.  Sorry for the mess.
> But we have to preserve backwards compatibility - even with bugs like
> this.
> 
> 
> > --
> > Scott Robison
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> 
> 
> --
> 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] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?

[Simon Slavin]

On 12 Jun 2017, at 4:20pm, Simon Slavin  wrote:

> Please add the ANALYZE command after your existing VACUUM.

Before.  Not after.  Do ANALYZE, then VACUUM.  It might make no difference but 
technically it may yield a faster result.  Or a smaller file.  Something good.

> This can noticeably speed up any future operations on your database.

Yup.  Any big change or conversion of data should end up with ANALYZE; VACUUM .

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

Re: [sqlite] Is sqlite3_auto_extension() same compilation unit ruled out?

[David Burgess]

Have a look at the way readfile() and writefile() is implemented in the
sqlite interpreter.

On Tue, Jun 13, 2017 at 10:38 AM, petern 
wrote:

> I have a situation where it would be convenient to locate externally
> loadable SQLite extension code in the same compilation unit as the server
> code.  Is there a way for server main() to load those extensions located
> within its own compilation unit? Does the necessity of #including both
> sqlite3.h and sqlite3ext.h with SQLITE_EXTENSION_INIT1 macro rule this out?
>
> Documentation suggests sqlite3_auto_extension() loads a statically linked
> extension entrypoint but trying this on same compilation unit's
> sqlite3_extension_init() entrypoint results in segfault.
>
> FYI, environment is Linux.
> ___
> 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] Is sqlite3_auto_extension() same compilation unit ruled out?

[petern]

I have a situation where it would be convenient to locate externally
loadable SQLite extension code in the same compilation unit as the server
code.  Is there a way for server main() to load those extensions located
within its own compilation unit? Does the necessity of #including both
sqlite3.h and sqlite3ext.h with SQLITE_EXTENSION_INIT1 macro rule this out?

Documentation suggests sqlite3_auto_extension() loads a statically linked
extension entrypoint but trying this on same compilation unit's
sqlite3_extension_init() entrypoint results in segfault.

FYI, environment is Linux.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

[Richard Hipp]

On 6/13/17, Scott Robison  wrote:
>
> Is it fair to say that the rowid aliasing behavior does not require
> (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
> that order as the "type") as long as the type is INTEGER and the
> constraint PRIMARY KEY appears somewhere in the column's constraint
> list?

See https://www.sqlite.org/lang_createtable.html#rowid

   CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);

Columns x *not* a ROWID.  Life is complicated.  Sorry for the mess.
But we have to preserve backwards compatibility - even with bugs like
this.


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


-- 
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] INTEGER PRIMARY KEY

[Scott Robison]

On Mon, Jun 12, 2017 at 4:20 PM, Simon Slavin  wrote:
>
>
> On 12 Jun 2017, at 11:01pm, Scott Robison  wrote:
>
>> Is it fair to say that the rowid aliasing behavior does not require
>> (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
>> that order as the "type") as long as the type is INTEGER and the
>> constraint PRIMARY KEY appears somewhere in the column's constraint
>> list?
>
> FAQ #1 is specifically worded …
>
> 
>
> But you’re being very specific.  I think you’re right but someone who has 
> read the source code might know otherwise.

The question was raised at work so I got curious about it. It makes
sense to me that the rowid aliasing behavior would only be determined
after lexing and parsing the SQL statement, but the documentation
statements of "INTEGER PRIMARY KEY" being necessary led some people
(understandably) down a path thinking you needed those three words in
that order (perhaps).

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

Re: [sqlite] INTEGER PRIMARY KEY

[Simon Slavin]

On 12 Jun 2017, at 11:01pm, Scott Robison  wrote:

> Is it fair to say that the rowid aliasing behavior does not require
> (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
> that order as the "type") as long as the type is INTEGER and the
> constraint PRIMARY KEY appears somewhere in the column's constraint
> list?

FAQ #1 is specifically worded …



But you’re being very specific.  I think you’re right but someone who has read 
the source code might know otherwise.

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

[sqlite] INTEGER PRIMARY KEY

[Scott Robison]

This is as much out of curiosity as anything. I know that to get the
rowid aliasing behavior for a table one must define the column type as
INTEGER and using the constraint PRIMARY KEY. Something like:

CREATE TABLE A(B INTEGER PRIMARY KEY);

In testing this afternoon I was curious if I could give the constraint a name:

CREATE TABLE A(B INTEGER CONSTRAINT B_PK PRIMARY KEY);

I can, and it is still an alias of the rowid. If I change the type to
INT then it is not an alias. This all makes sense.

If I insert NOT NULL between the type and PK constraint, it still is
an alias (as long as the type is INTEGER).

Is it fair to say that the rowid aliasing behavior does not require
(by design) the incantation "INTEGER PRIMARY KEY" (all three words in
that order as the "type") as long as the type is INTEGER and the
constraint PRIMARY KEY appears somewhere in the column's constraint
list?
-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] invalid column constraint ignored

[Richard Hipp]

On 6/12/17, gwenn  wrote:
> invalid column constraints are ignored.

This is a consequence of designing SQLite according to Postel's Law
(https://en.wikipedia.org/wiki/Robustness_principle) which was very
popular 17 years ago when SQLite was being designed, but nowadays has
fallen out of favor.  It cannot be changed now because doing so might
break some of the millions of applications that were written against
SQLite over the past nearly two decades and which make use of SQLite's
liberal parsing policies.

-- 
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] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?

[Keith Medcalf]

On Monday, 12 June, 2017 08:53, Венцислав Русев  wrote:

> I am using sqlite C API  to migrate a database. Migration consists of
> many SQL statements that are known in advance.
 
> To migrate a DB from version 3 to version 7 the C program does the
> following:
 
>  1. disable foreign_keys (PRAGMA foreign_keys = OFF);
>  2. open transaction (BEGIN TRANSACTION);
>  3. execute bunch of statements that migrates the DB to the next version
> using *sqlite3_exec(db, migrate[version], NULL, NULL, &errMsg)*;
> migrate[version] is consisting of many (sometimes several thousand)
> statements;
>  4. check for foreign_keys inconsistencies (PRAGMA foreign_key_check);
>  5. commit transaction (COMMIT TRANSACTION);
>  6. enable foreign_keys again (PRAGMA foreign_keys = ON);
>  7. vacuums db file (vacuum);
 
> I've realized that using the command line tool the migration takes
> around 8 minutes, but the C program takes around 20 minutes. This time
> is consumed in point number 3 in the previous list.

Interesting.  Is the command line tool and the C program using the *same* 
version of SQLite?
 
> How can I increase the performance of my program so that it reaches the
> performance of the command line tool?

Depends what the problem is.  I suppose that the migration SQL statements are 
all simple static SQL since your call to sqlite3_exec does not have a callback 
function.  This means that the sqlite3_exec is in effect nothing more than a 
loop which does: 

while (statements to execute)
{
 sqlite3_prepare_v2()
 while (sqlite3_step() == SQLITE_ROW);
 sqlite3_finalize()
}

since if you do not provide a callback pointer all the result processing is 
skipped.  The library sqlite3_exec is quite similar to the processing loop 
(execute_prepared_stmt) contained in the shell, so I don't see what the 
difference in processing time would be, if the versions of the sqlite3 shell 
and the sqlite3 engine included in the application are the same.

How are you processing the command batch with the shell?  Are you piping in the 
input (sqlite3 database.db < commandfile.sql) or reading it with the .read 
command?  Is the input to the shell only "one sql statement per line" or is it 
"all mushed together into a single line"?  How about when the application 
processes the same commands?  Are you passing one command per sqlite3_exec() 
invocation or are you passing it the whole multi-line block in one go?  Unless 
the "input formats" are the same, the comparison is not exactly meaningful ...

If the shell is processing a file which has multiple lines, can you remove all 
the line endings and pass it as a single block and see if that takes a 
comparable time to the time taken when using sqlite3_exec()?

> My first bet is to prepare each individual statement and then execute
> it. Should it be faster than sqlite3_exec?

Only if the difference is in having sqlite3_prepare_v2 "disassemble" the huge 
block of statements which *does* copy the entire passed statement block 
multiple times during parsing.  Since the command line shell reads "a line at a 
time" it does not have to do this, and perhaps this is where the time is being 
taken.

 
> ___
> 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] invalid column constraint ignored

[gwenn]

Hello,
I suppose it is a trade-off to make the parser light and fast.
But invalid column constraints are ignored.

sqlite> create table tbl (data text constraint x);

postgres=# create table tbl (data text constraint x);
ERROR:  syntax error at or near ")" at character 41

sqlite> create table tbl (data text deferrable initially deferred);

postgres=# create table tbl (data text deferrable initially deferred);
ERROR:  misplaced DEFERRABLE clause at character 29

sqlite> create table tbl (data text not null constraint x);

postgres=# create table tbl (data text not null constraint x);
ERROR:  syntax error at or near ")" at character 50

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

Re: [sqlite] Unable to create index on attached database

[Chris Peachment]

Thank you - the only combination that I did not try works :(


On Mon, 12 Jun 2017 08:17:01 +
Hick Gunter  wrote:

> Try
> 
> Create index t2.idx on link (...)
> 
> Which is what the syntax diagram would recommend. If you ask SQLite
> to create an index in t2, it will figure out that the table needs to
> be in t2 too.
> 
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
> Chris Peachment Gesendet: Sonntag, 11. Juni 2017 17:22 An: SQLite
> mailing list  Betreff: [sqlite]
> Unable to create index on attached database
> 
> Hello All:
> 
> Is this a bug or am I doing something wrong?
> 
> Note: neither test.db nor t2.db exist prior to this test.
> 
> $ sqlite3 test.db
> SQLite version 3.19.2 2017-05-25 16:50:27 Enter ".help" for usage
> hints.
> sqlite> create table account (id integer primary key, idParent
> sqlite> integer); attach database 't2.db' as t2; create table t2.link
> sqlite> (idParent int, idChild int); create index t2.idx on t2.link
> sqlite> (idChild,idParent);  
> Error: near ".": syntax error
> sqlite> create index idx on t2.link (idChild,idParent);  
> Error: near ".": syntax error
> sqlite> create index idx on link (idChild,idParent);  
> Error: no such table: main.link
> sqlite>  
> 
> Regards,
> 
> Chris Peachment
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
> 
> This communication (including any attachments) is intended for the
> use of the intended recipient(s) only and may contain information
> that is confidential, privileged or legally protected. Any
> unauthorized use or dissemination of this communication is strictly
> prohibited. If you have received this communication in error, please
> immediately notify the sender by return e-mail message and delete all
> copies of the original communication. Thank you for your cooperation.
> 
> 
> ___
> 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] How-to and best practice for session extension

[Simon Slavin]

On 12 Jun 2017, at 2:48pm, Robert M. Münch  wrote:

> Hi, we want to use the Session extension to implement an UNDO system for our 
> application. We did some first steps using it and now a couple of questions 
> came up:
> 
> 1. Is it correct that schema changes are not tracked and can't be part of a 
> changeset? So any ALTER TABLE command needs to be taken care about separately?

Correct.  Changing the schema means that changesets are no longer valid.  You 
will either need to make a system which can handle both, or regard schema 
changes as points you cannot UNDO past.

> 2. Changes to the VIEW query are not tracked too? So, when a view query 
> changes, this is not part of the changeset.

VIEWs, like INDEXes, are part of the schema.  Changing a VIEW is changing the 
schema.

> 3. We are thinking about supporting UNDO not only for the current session but 
> for the live-time of a document. In this case we need to store the changeset 
> blob into the database as well.

But storing something in the database is changing the database !  You would get 
problems with recursion.  Instead, you would have to store the blobs in another 
database.

> 4. We haven't thought through all combinations but is the oder of UNDOs in a 
> changeset relevant? So if I have a changeset do I have to apply it strictly 
> in revers order in some cases to avoid constraint violations etc?

Absolutely.  Consider this sequence:

INSERT a row
DELETE that row

Now try to UNDO that sequence in the wrong order.  You end up with a row which 
wasn’t there.

I cannot comment on using sessions to do this but I can suggest an alternative 
which is compatible with more systems.  This is that for every SQL command you 
execute you provide one or more commands which would reverse the effect.  This 
can be slower in use, but it’s far more easy to debug when things go wrong, and 
it provides an excellent log in case you ever need to do forensic investigation 
on your database.

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

Re: [sqlite] sqlite.org port 80 - can't connect

[jungle Boogie]

On 12 June 2017 at 01:03, Richard Hipp  wrote:
> On 6/12/17, jungle boogie  wrote:
>> Hi All,
>>
>> curl: (7) Failed to connect to www.sqlite.org port 80: Connection refused
>
> xinetd keeps crashing.  Dunno why.  I've restarted it.

I think it died again.

>
> Port 443 was still up.  Also ports 80 and 443 on www2.sqlite.org and
> www3.sqlite.org.

Yeah, I think I'll have fossil switch over to https.

> --
> D. Richard Hipp
> d...@sqlite.org



-- 
---
inum: 883510009027723
sip: jungleboo...@sip2sip.info
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?

[Simon Slavin]

On 12 Jun 2017, at 3:53pm, Венцислав Русев  wrote:

> To migrate a DB from version 3 to version 7 the C program does the following:

This migration is a one-time process, right ?  Each customer has to do it only 
once, then never again.  It not like they have to wait through it every day.  
You could just put up a "This will take a long time but just once." message.

A lot of this comes down to how unacceptably slow your current method is.  If 
it’s almost fast enough, then it’s probably not worth doing much work on it.  
If it’s so slow your customers are complaining, then it’s worth putting 
programming time into it.

> 1. disable foreign_keys (PRAGMA foreign_keys = OFF);
> 2. open transaction (BEGIN TRANSACTION);
> 3. execute bunch of statements that migrates the DB to the next version
>   using *sqlite3_exec(db, migrate[version], NULL, NULL, &errMsg)*;
>   migrate[version] is consisting of many (sometimes several thousand)
>   statements;
> 4. check for foreign_keys inconsistencies (PRAGMA foreign_key_check);
> 5. commit transaction (COMMIT TRANSACTION);
> 6. enable foreign_keys again (PRAGMA foreign_keys = ON);
> 7. vacuums db file (vacuum);

Speedup 1:

If "several thousand" is more than ten thousand, try using several transactions 
limited to ten thousand rows each.

Do you have lots of indexes ?  When importing data it’s faster to do

CREATE the tables (or copy a database which has empty tables in)
INSERT the rows
CREATE the indexes

Than to do the INSERTing last.

Please add the ANALYZE command after your existing VACUUM.  This can noticeably 
speed up any future operations on your database.

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

[sqlite] System.Data.SQLite version 1.0.105.2 released

[Joe Mistachkin]

System.Data.SQLite version 1.0.105.2 (with SQLite 3.19.3) is now available
on the System.Data.SQLite website:

 https://system.data.sqlite.org/

Further information about this release can be seen at:

 https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you
encounter any problems with this release.

--
Joe Mistachkin @ https://urn.to/r/mistachkin

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

[sqlite] How-to and best practice for session extension

[Robert M. Münch]

Hi, we want to use the Session extension to implement an UNDO system for our 
application. We did some first steps using it and now a couple of questions 
came up:

1. Is it correct that schema changes are not tracked and can't be part of a 
changeset? So any ALTER TABLE command needs to be taken care about separately?

2. Changes to the VIEW query are not tracked too? So, when a view query 
changes, this is not part of the changeset.

3. We are thinking about supporting UNDO not only for the current session but 
for the live-time of a document. In this case we need to store the changeset 
blob into the database as well. We want to track changes of all tables but of 
course not the table getting the changesets. There is a call for which tables 
to track changes, but not one for which tables not-to-track. Is there a simpler 
way than listing all tables for which tracking should happen explicitly?

4. We haven't thought through all combinations but is the oder of UNDOs in a 
changeset relevant? So if I have a changeset do I have to apply it strictly in 
revers order in some cases to avoid constraint violations etc? Or are 
constraints put on hold until all changes were applied?

Viele Grüsse.

-- 

Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster

http://www.saphirion.com
http://www.nlpp.ch


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?

[Hick Gunter]

"many (sometimes several thousand) statments" sounds like it could be heavy on 
memory requirements.

Are you inserting one row per statement or all rows in one statement? The 
latter would be really hard on memory because SQLite will have to parse the 
whole statement and generate a gigantic SQL program all in one go, instead of 
row by row.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von ? ?
Gesendet: Montag, 12. Juni 2017 16:53
An: SQLite mailing list 
Betreff: [sqlite] Can I increase the performance of sqlite3_exec() or maybe 
change it to prepare+step?

Hello,

I am using sqlite C API  to migrate a database. Migration consists of many SQL 
statements that are known in advance.

To migrate a DB from version 3 to version 7 the C program does the
following:

 1. disable foreign_keys (PRAGMA foreign_keys = OFF);  2. open transaction 
(BEGIN TRANSACTION);  3. execute bunch of statements that migrates the DB to 
the next version
using *sqlite3_exec(db, migrate[version], NULL, NULL, &errMsg)*;
migrate[version] is consisting of many (sometimes several thousand)
statements;
 4. check for foreign_keys inconsistencies (PRAGMA foreign_key_check);  5. 
commit transaction (COMMIT TRANSACTION);  6. enable foreign_keys again (PRAGMA 
foreign_keys = ON);  7. vacuums db file (vacuum);


I've realized that using the command line tool the migration takes around 8 
minutes, but the C program takes around 20 minutes. This time is consumed in 
point number 3 in the previous list.

How can I increase the performance of my program so that it reaches the 
performance of the command line tool?

My first bet is to prepare each individual statement and then execute it. 
Should it be faster than sqlite3_exec?

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Something broke between version 3.15 and 3.19.3

[Balaji Ramanathan]

I am glad you were able to fix it quickly.  I assume the next version of
SQLite (3.19.4 or 3.20 or whatever) will include the fix?  Thank you.

Balaji Ramanathan

On 6/11/17, Balaji Ramanathan  wrote:
>
> Everything was working fine under 3.15.  I just use the commandline
> tool (sqlite.exe) to interact with my database.  I am not a programmer and
> don't have a need for programmatic access to this database.  I recently
> updated to 3.19.3, and now when I select from that summary view, I get no
> results - the query just runs for minutes on end, and I eventually lose
> patience and kill the process with a ctrl-c.

SQLite is still working.  It is just picking an inefficient query plan.

The fix is here:  https://www.sqlite.org/src/timeline?c=87aceb417a813a29

--
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] Something broke between version 3.15 and 3.19.3

[Balaji Ramanathan]

Thank you very much, Simon.  I was able to downgrade to 3.18 using your
instructions and everything seems to work now.

Balaji Ramanathan

On 11 Jun 2017, at 2:56pm, Balaji Ramanathan 
wrote:

> This is going to sound stupid, but I would like to know how to
> download older versions of sqlite.

Although the download page gives links only for the current version of
SQLite, several older versions are still on the server and you can download
them by making up the URL yourself.  So, for instance, one of the links
currently on the Download page is



If you want the version before 3.18.0 you can look it up in



and find that it is 3.17.0 which was released in 2017.  Then you can make
up the appropriate URL:



and it works !  Hope this helps.

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

[sqlite] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?

[Венцислав Русев]

Hello,

I am using sqlite C API  to migrate a database. Migration consists of 
many SQL statements that are known in advance.


To migrate a DB from version 3 to version 7 the C program does the 
following:


1. disable foreign_keys (PRAGMA foreign_keys = OFF);
2. open transaction (BEGIN TRANSACTION);
3. execute bunch of statements that migrates the DB to the next version
   using *sqlite3_exec(db, migrate[version], NULL, NULL, &errMsg)*;
   migrate[version] is consisting of many (sometimes several thousand)
   statements;
4. check for foreign_keys inconsistencies (PRAGMA foreign_key_check);
5. commit transaction (COMMIT TRANSACTION);
6. enable foreign_keys again (PRAGMA foreign_keys = ON);
7. vacuums db file (vacuum);


I've realized that using the command line tool the migration takes 
around 8 minutes, but the C program takes around 20 minutes. This time 
is consumed in point number 3 in the previous list.


How can I increase the performance of my program so that it reaches the 
performance of the command line tool?


My first bet is to prepare each individual statement and then execute 
it. Should it be faster than sqlite3_exec?


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

Re: [sqlite] Unable to create index on attached database

[Hick Gunter]

Try

Create index t2.idx on link (...)

Which is what the syntax diagram would recommend. If you ask SQLite to create 
an index in t2, it will figure out that the table needs to be in t2 too.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Chris Peachment
Gesendet: Sonntag, 11. Juni 2017 17:22
An: SQLite mailing list 
Betreff: [sqlite] Unable to create index on attached database

Hello All:

Is this a bug or am I doing something wrong?

Note: neither test.db nor t2.db exist prior to this test.

$ sqlite3 test.db
SQLite version 3.19.2 2017-05-25 16:50:27 Enter ".help" for usage hints.
sqlite> create table account (id integer primary key, idParent integer);
sqlite> attach database 't2.db' as t2; create table t2.link (idParent
sqlite> int, idChild int); create index t2.idx on t2.link
sqlite> (idChild,idParent);
Error: near ".": syntax error
sqlite> create index idx on t2.link (idChild,idParent);
Error: near ".": syntax error
sqlite> create index idx on link (idChild,idParent);
Error: no such table: main.link
sqlite>

Regards,

Chris Peachment




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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] Unable to create index on attached database

[Chris Peachment]

Hello All:

Is this a bug or am I doing something wrong?

Note: neither test.db nor t2.db exist prior to this test.

$ sqlite3 test.db
SQLite version 3.19.2 2017-05-25 16:50:27
Enter ".help" for usage hints.
sqlite> create table account (id integer primary key, idParent integer);
sqlite> attach database 't2.db' as t2;
sqlite> create table t2.link (idParent int, idChild int);
sqlite> create index t2.idx on t2.link (idChild,idParent);
Error: near ".": syntax error
sqlite> create index idx on t2.link (idChild,idParent);
Error: near ".": syntax error
sqlite> create index idx on link (idChild,idParent);
Error: no such table: main.link
sqlite>

Regards,

Chris Peachment




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

[sqlite] Outputting to CSV - row is not quoted when there are no spaces

[dan]

Hi,

When outputting to CSV with '.mode csv' is there a way that all rows can 
be quoted even if there are no spaces? For example, here is a 1 line 
from the output:


spotify:track:5vlDIGBTQmlyfERBnJOnbJ,Kiso,Circles,100.019

I would like it to output:

"spotify:track:5vlDIGBTQmlyfERBnJOnbJ","Kiso","Circles",100.019

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

Re: [sqlite] sqlite.org port 80 - can't connect

[Richard Hipp]

On 6/12/17, jungle boogie  wrote:
> Hi All,
>
> curl: (7) Failed to connect to www.sqlite.org port 80: Connection refused

xinetd keeps crashing.  Dunno why.  I've restarted it.

Port 443 was still up.  Also ports 80 and 443 on www2.sqlite.org and
www3.sqlite.org.
-- 
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] sqlite.org port 80 - can't connect

[jungle boogie]

Hi All,

curl: (7) Failed to connect to www.sqlite.org port 80: Connection refused

https is working fine:
curl --head https://www.sqlite.org
HTTP/1.1 200 OK
Connection: keep-alive
Date: Mon, 12 Jun 2017 07:37:35 +
Last-Modified: Fri, 09 Jun 2017 14:05:33 +
Content-type: text/html; charset=utf-8
Content-length: 6977




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