date:20170721

On 21 Jul 2017 at 09:58, Simon Slavin  wrote: 

> On 21 Jul 2017, at 7:33am, Edmondo Borasio  wrote:
>
>> $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');
>
> Those are the wrong quotes.  SQLite requires single quotes around literal
> strings, and expects no quote around numbers.  And PHP doesn’t care which
> quotes you use as long as they match.

Actually PHP does care. It will not look inside single quoted strings for 
special escaped characters or variables for which to substitute values. It 
*will* do so for double-quoted strings.

So:

   echo 'Hello\n';

will not do the same thing as:

   echo "Hello\n";

The latter will put out a newline at the end of Hello whereas the former will 
put out two characters (\ and n).

Similarly, variable substitution will not happen here:

   $sql = 'UPDATE Movies SET name = $newname where id=$newid';

but will do here:

   $sql = "UPDATE Movies SET name = $newname where id=$newid";

So PHP is happy, but SQLite is not - you'd need:

   $sql = "UPDATE Movies SET name = '$newname' where id=$newid";

Personally I don't like forcing PHP to scan strings so I tend to use 
concatentation, rewriting the last of these as:

   $sql = 'UPDATE Movies SET name = \'' . $newname . '\' where id=' . $newid;

but that's just a personal style preference.

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

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Edmondo Borasio

Hi and thanks for your email.

I am using PHP with SQLite on an Apache server.
That statement was taken from some advice I got from a forum.  I wasn't
aware it was MySQL.
I am new to SQLite and this is my first database.

*"Table/column names cannot use parameters.  You have to put it directly*
*into the string:"*

I guess however there must be a way, because for example with SELECT it
works.
The query below works perfectly using variables:

$results = $db->query("SELECT \"$DbItemName\" FROM Anagrafica WHERE
hID=\"$hId\"")->fetchArray();

Cheers

Edmondo

On Fri, 21 Jul 2017 at 12:24, Clemens Ladisch  wrote:

> Edmondo Borasio wrote:
> > $stmt->bind_param($p_name,$bind_value);
>
> This looks like PHP's MySQL driver.  Which DB are you actually using?
>
> Anyway, I recommend you start with the examples from the manual, e.g.,
> :
>
>   $stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
>   $stmt->bindValue(':id', 1, SQLITE3_INTEGER);
>   $result = $stmt->execute();
>
> > I would also need to take the parameter "name" of "SET name" from a
> variable
>
> Table/column names cannot use parameters.  You have to put it directly
> into the string:
>
>   $sql = "UPDATE Anagrafica SET ".$col." = ..."
>
>
> 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] UPDATE database using parameters

2017-07-21 Thread Edmondo Borasio

Hi Thanks but it doesn't work.

It's weird because the following works:

$db->exec('UPDATE Anagrafica SET name = "new name" WHERE hID="1"');
//WORKS

But this doesn't:

$p_name ="new name";
$bind_value = "1";
$stmt = $conn->prepare('UPDATE Anagrafica SET name=? WHERE hID=?');
$stmt->bind_param($p_name,$bind_value);
$stmt->exec();

To make things more complex, once this is working, then I would also need
to take the parameter "name" of "SET name" from a variable..

Thanks

On 21 July 2017 at 10:45,  wrote:

> On Fri Jul 21, 2017 at 06:33:55AM +, Edmondo Borasio wrote:
> > I am updating a record of a SQLite database as follows:
> >
> > $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');
> >
> > but instead of using name and ID I want to use some variables,
> $NewItemName
> >  and $hId.
> >
> > Entering the variables as they are won't work. Neither using escape
> > characters like \"$DbItemName\" and \"$hId\".
> >
> > Any idea of the right syntax pls?
>
> It appears that you are using something like Perl or Ruby to access
> your database?  If that is the case, then you will find that the
> concept of "placeholders" is what you are looking for.
>
> Typically you specify a "?" (without the quotes) where you want your
> variables to be, and then "bind" the variables to those positions
> either before or during the exec call.
>
> Here is the Perl documentation on that topic:
>
> https://metacpan.org/pod/DBI#Placeholders-and-Bind-Values
>
> I suggest you look up the equivalent in whatever language you are
> using.
>
> --
> Mark Lawrence
> ___
> 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] UPDATE database using parameters

2017-07-21 Thread Clemens Ladisch

Edmondo Borasio wrote:
> $stmt->bind_param($p_name,$bind_value);

This looks like PHP's MySQL driver.  Which DB are you actually using?

Anyway, I recommend you start with the examples from the manual, e.g.,
:

  $stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
  $stmt->bindValue(':id', 1, SQLITE3_INTEGER);
  $result = $stmt->execute();

> I would also need to take the parameter "name" of "SET name" from a variable

Table/column names cannot use parameters.  You have to put it directly
into the string:

  $sql = "UPDATE Anagrafica SET ".$col." = ..."

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

Re: [sqlite] UPDATE database using parameters

On 21 Jul 2017 at 07:33, Edmondo Borasio  wrote: 

> I am updating a record of a SQLite database as follows:
>
> $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');
>
> but instead of using name and ID I want to use some variables, $NewItemName
> and $hId.
>
> Entering the variables as they are won't work. Neither using escape
> characters like \"$DbItemName\" and \"$hId\".

It would help if you said what language you are using.

In PHP, one could do this:

  $db->exec ('UPDATE Movies SET name = \'' . $DbItemName . '\' WHERE ID=' . 
$hId);

or perhaps:

  $db->exec ("UPDATE Movies SET name = '$DbItemName' WHERE ID=$hId");


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

Re: [sqlite] UPDATE database using parameters

On 21 Jul 2017, at 7:33am, Edmondo Borasio  wrote:

> $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');

Those are the wrong quotes.  SQLite requires single quotes around literal 
strings, and expects no quote around numbers.  And PHP doesn’t care which 
quotes you use as long as they match.  Correct line should be as follows:

$db->exec("UPDATE Movies SET name = 'new movie' WHERE ID=4");

>   $stmt = $conn->prepare('UPDATE Anagrafica SET name=? WHERE hID=?');

Use named parameters.  See the example on this page instead:

so something like

$stmt = $conn->prepare("UPDATE Anagrafica SET name=:name WHERE 
hID=:hid");
$stmt->bindValue(":name", $DbItemName, SQLITE3_TEXT);
$stmt->bindValue(":hid", $hId, SQLITE3_INTEGER);

If speed is an issue then you can use numbers for the first parameter, but that 
makes your code less easy to read.

Also, make sure you are using the SQLite3 driver, not the standard PDO one.  So 
you should be doing

$conn = new SQLite3("path goes here");

You can get away with using the wrong quotes in PHP in some circumstances, but 
not others, which makes debugging the wrong case extremely difficult.  Better 
to use the right quotes throughout your code even when the wrong ones work.

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

Re: [sqlite] A Descending Auto Increment Key

2017-07-21 Thread cindy estepp

i have a file could you look at it and explain it to me please thank you

On Thu, Jul 20, 2017 at 8:47 AM, Don V Nielsen 
wrote:

> On the heel of this is a grammatical error: "declared type "INTEGER"
> includes an "PRIMARY KEY DESC" clause". It should read "includes a
> "primary...".
>
> On Wed, Jul 19, 2017 at 11:03 PM, Keith Medcalf 
> wrote:
>
> >
> > AUTOINCREMENT can only be used with INTEGER PRIMAY KEY columns in a rowid
> > table definition to declare an alias for the rowid.  Such columns must
> not
> > have the DESC ordering (the rowid is ascending).  If it does, then
> "PRIMARY
> > KEY" is syntactic sugar for "UNIQUE" (ie, creating a unique index) and
> the
> > AUTOINCREMENT keyword cannot be applied.
> >
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of sanhua.zh
> > Sent: Wednesday, 19 July, 2017 20:07
> > To: sqlite-users
> > Subject: [sqlite] A Descending Auto Increment Key
> >
> > When I call `CREATE TABLE test(i INTEGER PRIMARY KEY DESC AUTO
> > INCREMENT)`, SQLite comes to an error "AUTOINCREMENT is only allowed on
> an
> > INTEGER PRIMARY KEY".
> >
> >
> > But as the document http://www.sqlite.org/lang_createtable.htmlsays, in
> > the column-constraint, it shows that `PRIMARY DESC AUTOINCREMENT` is a
> > valid syntax.
> >
> >
> > Why the original SQL failed? Is that really not supported?
> > ___
> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE database using parameters

2017-07-21 Thread Edmondo Borasio

Hi Tim.

It *almost* works..

$DbItemNameTest = "new name";
$hIdTest = "1";

$db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest .
'\' WHERE hospital_ID="1"'); //IT WORKS
   $db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest . '\'
WHERE hospital_ID=' . $hIdTest); //IT DOESN'T WORK

The second one doesn't work even if I change hIdTest to integer.

$hIdTest = 1;

Any suggestions pls?

Cheers



On 21 July 2017 at 12:35, Tim Streater  wrote:

> On 21 Jul 2017 at 07:33, Edmondo Borasio  wrote:
>
> > I am updating a record of a SQLite database as follows:
> >
> > $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');
> >
> > but instead of using name and ID I want to use some variables,
> $NewItemName
> > and $hId.
> >
> > Entering the variables as they are won't work. Neither using escape
> > characters like \"$DbItemName\" and \"$hId\".
>
> It would help if you said what language you are using.
>
> In PHP, one could do this:
>
>   $db->exec ('UPDATE Movies SET name = \'' . $DbItemName . '\' WHERE ID='
> . $hId);
>
> or perhaps:
>
>   $db->exec ("UPDATE Movies SET name = '$DbItemName' WHERE ID=$hId");
>
>
> --
> Cheers  --  Tim
> ___
> 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] Database version

On 7/21/17, Peter Da Silva  wrote:
> I assume BEGIN, COMMIT, and ROLLBACK are safe too :)

Yes, of course.

-- 
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] SQLite 3.20.0 postponed

On Friday, 21 July, 2017 10:51, Richard Hipp  wrote:

> But sqlite3_result_pointer() and sqlite3_bind_pointer() are not 
> invoked from those languages.  The _pointer() routines are invoked 
> from C, and C does easily support string literals that are static 
> strings.

> A C-language wrapper around sqlite3_result_pointer() and
> sqlite3_bind_pointer() that interfaces to the non-static-string 
> language can simply insert the required static string.

Well, yes.  Technically.  Almost everything everywhere is called from code 
written in C.  Very little meaningful anything is written in any other 
language.  So although you may be writing something in Java (heavens forbid), 
Java was written in C.  So in the end, all calls are made from software written 
in C.  This is the way of the world.  Some people just like to add lots of 
syntactical sugar and overhead which eventually ends up being C code -- either 
translated by a preprocessor (as in the case of C++), or by a compiler written 
in C (or written in some other language that was written in C ... repeat for as 
deep down the rabbit hole as you need to go).  There are a few self-hosted 
language compilers that are exceptions, but not very many.  And they all 
started as C before they were re-written in themself so they could become 
self-hosted.

However, the change does create a problem when one is using a sugary language 
(for example Python).  It does not know how to bind pointers, nor is there any 
way to pass in a variable static "string" as an extra parameter.  So for 
example, the carray extension *used* to be able to be passed a pointer which 
was bound as an int (you obtain the pointer by allocating a ctypes array and 
asking for the pointer) -- of course this is unsafe if you do something stupid 
-- but then almost everything including making a nice cuppa tea is unsafe if 
you do something stupid.

I have fiddled with defining a new type for the python interface that can 
contain a string and a reference to a ctypes object (so that the string stays 
in scope and the ctypes array stays in scope as long as the outer object stays 
in scope), and then adding an addition bind detection for this type of object.  
It works but it is far more complicated than just modifying the carray 
extension so that it will accept integers for the pointer (as well as pointer 
types).  Plus of course (sending an integer) works with all interfaces that can 
get a pointer rather than having to update every single possible sugary 
extension.

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

Re: [sqlite] UPDATE database using parameters

> On Jul 21, 2017, at 9:48 AM, John McKown  wrote:
> 
> And, just to interject a politically incorrect statement, any "programmer"
> who does not use the safer interface is either __extremely__ ignorant,  or
> arrogantly stupid

I wouldn’t put it that harshly, but I agree in spirit. If you’re just learning 
an API and experimenting on your own, it’s understandable that you’ll overlook 
parts of it, or miss best practices, and make mistakes. If that causes crashes 
or problems on your computer, well, that’s part of learning. We all do that.

But anyone writing software that runs in a web server, or that otherwise 
interacts with untrusted data, has to pay attention to basic security 
practices. And a fundamental one is that you don’t run code that some untrusted 
person sent you. SQL statements are code. So incorporating user input into a 
string and then telling a SQL database to evaluate that string is incredibly 
unsafe. 

Anyone who doesn’t hear alarm bells going off when they see code like “UPDATE 
students set name=$FORM_DATA …” really shouldn’t be writing this sort of 
software. (And it gets worse than this. A major attack on Wordpress and other 
PHP apps about ten years ago, that caused a lot of damage worldwide, was 
triggered by some bozo using PHP’s “eval()” function inside an XMLRPC library.)

It’s like: if you want to fool around with gears and pulleys and learn 
mechanics in your home workshop, awesome! But if you’re going to build an 
elevator for my 20-story apartment building, I really want you to have a solid 
knowledge of structural engineering and safety practices, so you don’t do 
something ignorant and get people killed.

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

Re: [sqlite] Database version

I assume BEGIN, COMMIT, and ROLLBACK are safe too :)

On 7/21/17, 1:08 PM, "sqlite-users on behalf of Richard Hipp" 
 
wrote:

On 7/21/17, Simon Slavin  wrote:
>
> The new features introduced by SQLite since it started using file format 3
> all require explicit commands to use.  Adding columns to existing tables,
> AUTOVACUUM mode, WAL journals, DESC indexes, all require you to execute a
> specific SQL command to use them.

More precisely, the all require a schema change.

If you have a database created by old-application-X using a really old
version of SQLite.  Then you run DML statements using
brand-new-application-Y, those DML statement will never change the
database in any that make it unreadable by X.

"DML statements" = SELECT, INSERT, UPDATE, DELETE.
-- 
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] Database version

On 21 Jul 2017, at 6:42pm, Warren Young  wrote:

> Now to complicate that, macOS also ships a /usr/bin/sqlite3, which happens to 
> be 3.16.0 in macOS 10.12, so my superficial question is, can I safely open 
> *and modify* the Lightroom catalog file with the macOS version of SQLite, or 
> even some newer version?

The new features introduced by SQLite since it started using file format 3 all 
require explicit commands to use.  Adding columns to existing tables, 
AUTOVACUUM mode, WAL journals, DESC indexes, all require you to execute a 
specific SQL command to use them.

Avoid those commands and you’re fine.  You can use any other SQLite3 calls or 
SQL commands without changing the database file so that Lightroom can’t use it.

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

Re: [sqlite] Database version

On 7/21/17, Simon Slavin  wrote:
>
> The new features introduced by SQLite since it started using file format 3
> all require explicit commands to use.  Adding columns to existing tables,
> AUTOVACUUM mode, WAL journals, DESC indexes, all require you to execute a
> specific SQL command to use them.

More precisely, the all require a schema change.

If you have a database created by old-application-X using a really old
version of SQLite.  Then you run DML statements using
brand-new-application-Y, those DML statement will never change the
database in any that make it unreadable by X.

"DML statements" = SELECT, INSERT, UPDATE, DELETE.
-- 
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] Database version

Richard, et al,

On Fri, Jul 21, 2017 at 2:08 PM, Richard Hipp  wrote:
> On 7/21/17, Simon Slavin  wrote:
>>
>> The new features introduced by SQLite since it started using file format 3
>> all require explicit commands to use.  Adding columns to existing tables,
>> AUTOVACUUM mode, WAL journals, DESC indexes, all require you to execute a
>> specific SQL command to use them.
>
> More precisely, the all require a schema change.
>
> If you have a database created by old-application-X using a really old
> version of SQLite.  Then you run DML statements using
> brand-new-application-Y, those DML statement will never change the
> database in any that make it unreadable by X.

The only trouble is - how do I know what version were used.
My application may use some features that is available right now and
I want to use them because they are great.
But if I open the DB from the old version I will simply get an error.

And if I don't know the DB file version - I will be clueless what is
going on. (Well, not exactly, but it will be less head scratching).

Now, I'm not saying "Hey some unused bytes in the header should
be used for that". I'm just pointing out that in some cases it is useful
information. And it is up to you and you team as a core devs to
consider it or not.

Thank you.

>
> "DML statements" = SELECT, INSERT, UPDATE, DELETE.
> --
> 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] UPDATE database using parameters

On Friday, 21 July, 2017 11:37, Jens Alfke  wrote:

> But anyone writing software that runs in a web server, 
> or that otherwise interacts with untrusted data, has to 
> pay attention to basic security practices. 

> And a fundamental one is that you don’t run code that 
> some untrusted person sent you. 

But most people do this all the time.  Just using a web browser has your 
machine executing god only knows what code generated by god only knows who 
doing god only knows what to your computer.  Unless you have disabled that, of 
course.  But that makes the web almost completely unuseable because it is full 
of stupid sluggard Johhny-cum-lately web designers who pull in third-party crap 
from god only knows where (since only their victims run it, they do not run it 
themselves).  There is a very small subset of people who take action against 
such stupidity.  I used to complain but these people are utter morons with 
abysmal IQs and do not grok the problem -- so there is not much point in that.  
Now I simply refuse to deal with companies that pull such shenanigans and tell 
them why I will never do business with them.

> Anyone who doesn’t hear alarm bells going off when 
> they see code like 
> “UPDATE students set name=$FORM_DATA …” 
> really shouldn’t be writing this sort of software. 

And people who use squirrily quotes should fix their email client ...

> (And it gets worse than this. A major attack on Wordpress 
> and other PHP apps about ten years ago, that caused a lot 
> of damage worldwide, was triggered by some bozo using PHP’s 
> “eval()” function inside an XMLRPC library.)

You don't need to look that far.  I am sure there was at least ten new 
vulnerabilities discovered yesterday that fall into this category.  And just 
for WordPress.

—Jens

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

Re: [sqlite] SQLite 3.20.0 postponed

Yes.  Good point.  Before seeing Richard's last commits for pseudo-null
pointer passing with static string type name, I proposed that the
pseudo-null pointer API be extended with function signature forms which
pass by value a user defined integer field for type information.

After further contemplation, I think it would be better to simply add the
BLOB API function signature forms to pass optional user defined type
information (by value) to achieve two factor pointer type identification.
One can already test BLOB byte length.  Adding an optional field to
communicate explicit type id would truly rule out the possibility of
dereferencing a nonsense BLOB which happens to be the same length in bytes
as the expected host object.

On Fri, Jul 21, 2017 at 9:03 AM, Gwendal Roué 
wrote:

>
> > Le 21 juil. 2017 à 17:55, Gwendal Roué  a écrit
> :
> >
> > First, this strcmp() give a lot of work to languages that wrap SQLite
> and lack support for "static strings". Building a global \0-terminated
> buffer that never gets deallocated is not always that easy :-)
>
> For the record, here is the commit that brings support for SQLite 3.20.0 :
> https://github.com/groue/GRDB.swift/commit/0a603f1be3966d478b505373af95d2
> 57224ce5b0  0a603f1be3966d478b505373af95d257224ce5b0>
>
> The context is custom FTS5 tokenisers for Swift:
> https://github.com/groue/GRDB.swift/blob/v1.2.2/
> Documentation/FTS5Tokenizers.md  GRDB.swift/blob/v1.2.2/Documentation/FTS5Tokenizers.md>
>
> Gwendal
>
> ___
> 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] Database version

> On Jul 21, 2017, at 11:17 AM, Igor Korot  wrote:
> 
> The only trouble is - how do I know what version were used.
> My application may use some features that is available right now and
> I want to use them because they are great.
> But if I open the DB from the old version I will simply get an error.

Use ‘pragma user_version’ to store a version number of your own choosing. 
Increment the user number when you make changes to the schema, whether you’re 
using new SQLite features or just altering tables. Then when you open a 
database, check the user_version and decide whether you need to run some sort 
of schema migration (like an ALTER TABLE), or just fail because the database is 
incompatibly too old. And the same goes in reverse — if the user_version is 
greater than what you support, give up because it’s a database from “the 
future”.

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

Re: [sqlite] UPDATE database using parameters

On 21 Jul 2017 at 10:04, Edmondo Borasio  wrote: 

> Hi Tim.
>
> It *almost* works..
>
>$DbItemNameTest = "new name";
>$hIdTest = "1";
>
>$db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest .
> '\' WHERE hospital_ID="1"'); //IT WORKS
>   $db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest . '\'
> WHERE hospital_ID=' . $hIdTest); //IT DOESN'T WORK
>
> The second one doesn't work even if I change hIdTest to integer.
>
>$hIdTest = 1;

Well, is hospital_ID stored in your database as a string or as an integer?

If it's an integer, then having $hIdTest as an integer should work. If it's a 
string you would leave $hIdTest as a string and change the sql to:

   ... WHERE hospital_ID=\'' . $hIdTest . '\'');

(I know SQLite can be cleverer than that in doing conversions but I've tried 
never to rely on that so am unfamiliar with it)

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

Re: [sqlite] SQLite 3.20.0 postponed

Indeed.  Or Type(T) of long, or sqlite_int64, or whatever is most universal
where porting other architectures is concerned. It would be a shame if the
API had to be changed later because the integer type chosen couldn't hold a
pointer.

From there, in a closed compiled host C/C++ setting with the discipline of
header files, one may simply perform the stricmp("CARRAY",(const char*) T)
cast to T string pointer comparison as desired and leave the API out of
that part.  This leaves open the more conventional test of T with enum or
#define compile time constant.

Now it remains, on the surface, that this new pseudo null pointer API
appears redundant in light of the existing BLOB interface and the
possibility of simply adding Type(T) versions of those BLOB function
signatures to the API.

How was adding to the BLOB API ruled out?  Is there a fundamental problem
with slightly broadening the BLOB API to deal with typed host objects?
Surely returning zero for the Type(T) would be backward compatible, would
it not?  Just asking.









On Fri, Jul 21, 2017 at 12:48 PM, Keith Medcalf  wrote:

> On Friday, 21 July, 2017 10:51, Richard Hipp  wrote:
>
> > But sqlite3_result_pointer() and sqlite3_bind_pointer() are not
> > invoked from those languages.  The _pointer() routines are invoked
> > from C, and C does easily support string literals that are static
> > strings.
>
> > A C-language wrapper around sqlite3_result_pointer() and
> > sqlite3_bind_pointer() that interfaces to the non-static-string
> > language can simply insert the required static string.
>
> Well, yes.  Technically.  Almost everything everywhere is called from code
> written in C.  Very little meaningful anything is written in any other
> language.  So although you may be writing something in Java (heavens
> forbid), Java was written in C.  So in the end, all calls are made from
> software written in C.  This is the way of the world.  Some people just
> like to add lots of syntactical sugar and overhead which eventually ends up
> being C code -- either translated by a preprocessor (as in the case of
> C++), or by a compiler written in C (or written in some other language that
> was written in C ... repeat for as deep down the rabbit hole as you need to
> go).  There are a few self-hosted language compilers that are exceptions,
> but not very many.  And they all started as C before they were re-written
> in themself so they could become self-hosted.
>
> However, the change does create a problem when one is using a sugary
> language (for example Python).  It does not know how to bind pointers, nor
> is there any way to pass in a variable static "string" as an extra
> parameter.  So for example, the carray extension *used* to be able to be
> passed a pointer which was bound as an int (you obtain the pointer by
> allocating a ctypes array and asking for the pointer) -- of course this is
> unsafe if you do something stupid -- but then almost everything including
> making a nice cuppa tea is unsafe if you do something stupid.
>
> I have fiddled with defining a new type for the python interface that can
> contain a string and a reference to a ctypes object (so that the string
> stays in scope and the ctypes array stays in scope as long as the outer
> object stays in scope), and then adding an addition bind detection for this
> type of object.  It works but it is far more complicated than just
> modifying the carray extension so that it will accept integers for the
> pointer (as well as pointer types).  Plus of course (sending an integer)
> works with all interfaces that can get a pointer rather than having to
> update every single possible sugary extension.
>
>
>
>
>
>
> ___
> 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] Database version

On Friday, 21 July, 2017 14:24, Igor Korot  wrote:

>I presume the same is true for the DB created with 3.0.0 and opened
>with 3.20.0.

>As long as I will use the standard SQL command and don't use any new
>feature introduced in 3.20.0.

>The only trouble is - I may not know in advance what version of
>SQLite was used and my program - which is based on the C API - 
>will use all those new features available,
>because it makes the program better/faster/more robust/etc.

>But then I will get an error, right? Because the feature I want is
>not supported by the database.

>Or ma I missing something?

The features are not the C API nor are they DML (SELECT INSERT UPDATE DELETE -- 
the CRUD operations).

The features are DDL (Data Definition Language)/
 - conditional indexes
 - WAL journal mode
 - indexes on functions
 - without-rowid tables

So, if you (with any version of SQLite on any database created by any version 
of SQLite that you can open), create a conditional index on functions then that 
database cannot be opened (will be corrupt) in all versions of SQLite prior to 
the version at which both conditional indexes and index on functions became 
available.

Similarly, if you set a database to WAL it cannot be opened with a version of 
SQLite that does not do WAL.

However, if you delete the features you added above, you WILL be able to open 
the database in versions of all versions of SQLite that have the necessary DDL 
included.

Using a feature of the C API that does not permanently change the database 
structure does not change the compatibility of the database.  That is to say 
that you can use the sqlite3_blob_twiddler interface to play with blobs even 
though that interface did not appear until version 3.45.0, and the database can 
still be opened and used perfectly fine in SQLite 3.0.0 (but the program which 
calls the sqlite3_blob_twiddler C API obviously will not be able to link with 
that version of SQLite -- which is an entirely different thing).

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

Re: [sqlite] SQLite 3.20.0 postponed


Pointers cannot be stored in the database.  They are simply value objects that 
get passed around between bits and pieces -- such as passing a pointer from an 
SQL statement into an extension function or virtual table all within the same 
linkage unit (that is locally within a single process).  The whole point, I 
believe, was to prevent pointers from being persisted to the database.  And if 
you foolishly try to store them in the database, then the value stored is NULL. 
 Storing pointers in BLOBs would defeat this purpose since you would not be 
preventing natural selection.

And if I have the purpose wrong, then I really don't understand why there needs 
to be a separate value type for pointers at all ...


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of petern
>Sent: Friday, 21 July, 2017 14:49
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLite 3.20.0 postponed
>
>Indeed.  Or Type(T) of long, or sqlite_int64, or whatever is most
>universal
>where porting other architectures is concerned. It would be a shame
>if the
>API had to be changed later because the integer type chosen couldn't
>hold a
>pointer.
>
>From there, in a closed compiled host C/C++ setting with the
>discipline of
>header files, one may simply perform the stricmp("CARRAY",(const
>char*) T)
>cast to T string pointer comparison as desired and leave the API out
>of
>that part.  This leaves open the more conventional test of T with
>enum or
>#define compile time constant.
>
>Now it remains, on the surface, that this new pseudo null pointer API
>appears redundant in light of the existing BLOB interface and the
>possibility of simply adding Type(T) versions of those BLOB function
>signatures to the API.
>
>How was adding to the BLOB API ruled out?  Is there a fundamental
>problem
>with slightly broadening the BLOB API to deal with typed host
>objects?
>Surely returning zero for the Type(T) would be backward compatible,
>would
>it not?  Just asking.
>
>
>
>
>
>
>
>
>
>On Fri, Jul 21, 2017 at 12:48 PM, Keith Medcalf 
>wrote:
>
>> On Friday, 21 July, 2017 10:51, Richard Hipp 
>wrote:
>>
>> > But sqlite3_result_pointer() and sqlite3_bind_pointer() are not
>> > invoked from those languages.  The _pointer() routines are
>invoked
>> > from C, and C does easily support string literals that are static
>> > strings.
>>
>> > A C-language wrapper around sqlite3_result_pointer() and
>> > sqlite3_bind_pointer() that interfaces to the non-static-string
>> > language can simply insert the required static string.
>>
>> Well, yes.  Technically.  Almost everything everywhere is called
>from code
>> written in C.  Very little meaningful anything is written in any
>other
>> language.  So although you may be writing something in Java
>(heavens
>> forbid), Java was written in C.  So in the end, all calls are made
>from
>> software written in C.  This is the way of the world.  Some people
>just
>> like to add lots of syntactical sugar and overhead which eventually
>ends up
>> being C code -- either translated by a preprocessor (as in the case
>of
>> C++), or by a compiler written in C (or written in some other
>language that
>> was written in C ... repeat for as deep down the rabbit hole as you
>need to
>> go).  There are a few self-hosted language compilers that are
>exceptions,
>> but not very many.  And they all started as C before they were re-
>written
>> in themself so they could become self-hosted.
>>
>> However, the change does create a problem when one is using a
>sugary
>> language (for example Python).  It does not know how to bind
>pointers, nor
>> is there any way to pass in a variable static "string" as an extra
>> parameter.  So for example, the carray extension *used* to be able
>to be
>> passed a pointer which was bound as an int (you obtain the pointer
>by
>> allocating a ctypes array and asking for the pointer) -- of course
>this is
>> unsafe if you do something stupid -- but then almost everything
>including
>> making a nice cuppa tea is unsafe if you do something stupid.
>>
>> I have fiddled with defining a new type for the python interface
>that can
>> contain a string and a reference to a ctypes object (so that the
>string
>> stays in scope and the ctypes array stays in scope as long as the
>outer
>> object stays in scope), and then adding an addition bind detection
>for this
>> type of object.  It works but it is far more complicated than just
>> modifying the carray extension so that it will accept integers for
>the
>> pointer (as well as pointer types).  Plus of course (sending an
>integer)
>> works with all interfaces that can get a pointer rather than having
>to
>> update every single possible sugary extension.
>>
>>
>>
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>

Re: [sqlite] SQLite 3.20.0 postponed

On 7/21/17, petern  wrote:
>
> How was adding to the BLOB API ruled out?

It uses extra memory and CPU cycles, even on the vast majority of
applications that do not use it.

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

https://www.sqlite.org/c3ref/c_source_id.html

This is also stored at offset 96 in the db file:

https://www.sqlite.org/fileformat.html

I don’t think there’s a pragma for extracting it from within SQLite code.

On 7/21/17, 9:58 AM, "sqlite-users on behalf of Igor Korot" 
 
wrote:

 Hi, ALL,
Is there a way to know the version of the .db file I am using?

I'd like to issue some kind of SELECT statement to get it.

Looks like there is an interface to get the library version, but I don't
see anything for a db file.

Thank you.
___
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] UPDATE database using parameters

Have a look at prepared statements and statement parameters.

Also, if you’re quoting strings you should use single quotes rather than double 
quotes. Double quotes just override the tokenizer, the result is still untyped 
and technically an identifier (the tokenizer actually marks it as TK_ID) until 
the parser disambiguates it. This can lead to unexpected results.

On 7/21/17, 1:34 AM, "sqlite-users on behalf of Edmondo Borasio" 
 wrote:

I am updating a record of a SQLite database as follows:

$db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"');

but instead of using name and ID I want to use some variables, $NewItemName
 and $hId.

Entering the variables as they are won't work. Neither using escape
characters like \"$DbItemName\" and \"$hId\".

Any idea of the right syntax pls?
-- 
Dr Edmondo Borasio, MedC BQ Ophth, FEBO
Consultant Ophthalmologist
Specialised in Cornea, Cataract & Laser Refractive Surgery

Head of Corneal and Refractive Surgery Department
Burjeel Hospital
Abu Dhabi, UAE
___
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] Database version

 Hi, ALL,
Is there a way to know the version of the .db file I am using?

I'd like to issue some kind of SELECT statement to get it.

Looks like there is an interface to get the library version, but I don't
see anything for a db file.

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

Re: [sqlite] UPDATE database using parameters

Using a straight PHP-level substitution like that performs the substitution 
before the SQL parser sees it. It’s also super dangerous if you’re not 
absolutely sure there’s no path for an untrusted agent to inject the name 
you’re selecting on.

https://xkcd.com/327/

On 7/21/17, 3:42 AM, "sqlite-users on behalf of Edmondo Borasio" 
 wrote:

Hi and thanks for your email.

I am using PHP with SQLite on an Apache server.
That statement was taken from some advice I got from a forum.  I wasn't
aware it was MySQL.
I am new to SQLite and this is my first database.

*"Table/column names cannot use parameters.  You have to put it directly*
*into the string:"*

I guess however there must be a way, because for example with SELECT it
works.
The query below works perfectly using variables:

$results = $db->query("SELECT \"$DbItemName\" FROM Anagrafica WHERE
hID=\"$hId\"")->fetchArray();

Cheers

Edmondo

On Fri, 21 Jul 2017 at 12:24, Clemens Ladisch  wrote:

> Edmondo Borasio wrote:
> > $stmt->bind_param($p_name,$bind_value);
>
> This looks like PHP's MySQL driver.  Which DB are you actually using?
>
> Anyway, I recommend you start with the examples from the manual, e.g.,
> :
>
>   $stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');
>   $stmt->bindValue(':id', 1, SQLITE3_INTEGER);
>   $result = $stmt->execute();
>
> > I would also need to take the parameter "name" of "SET name" from a
> variable
>
> Table/column names cannot use parameters.  You have to put it directly
> into the string:
>
>   $sql = "UPDATE Anagrafica SET ".$col." = ..."
>
>
> 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

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

Re: [sqlite] UPDATE database using parameters

> On Jul 21, 2017, at 1:01 PM, Keith Medcalf  wrote:
> 
> Just using a web browser has your machine executing god only knows what code 
> generated by god only knows who doing god only knows what to your computer.  
> Unless you have disabled that, of course.  But that makes the web almost 
> completely unuseable

Well, JavaScript is sandboxed. And I think most people would take issue with 
the assertion that the web is unusable.

> And people who use squirrily quotes should fix their email client …

“These”?  They're true quotation marks. The straight kind was only invented 
later, for typewriters, just to save a key; they’ve never been acceptable in 
anything but typewritten documents. (The early typewriters also didn’t have a 
“1” because you could use a lowercase “l”, or a “0” because you could use an 
“O”. That got remedied later, but they kept the ugly quotes.)

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

Re: [sqlite] SQLite 3.20.0 postponed

Nobody is storing pointers.  Where do you get that idea?

If struct pointer is passed through sqlite3_result_blob() with destructor
callback disposition instead of SQLITE_TRANSIENT, the value received is a
BLOB result pointed to the (void*) memory location of that exact supplied
struct's memory.   That's how it works now and independently of the 3.20.00
proposal.

Now, if that BLOB is persisted you get a copy of those bits onto the disk.
So what?  Maybe that makes sense or maybe not, but the pointer isn't
stored.  The pointer is only available at runtime which is what we're
talking about here.  The question is not about storage.  The question is
how to make the API useful for embedded applications with awareness of the
host's transient objects.   We also learned just now by forum email from
Richard that adding and carrying a native INT type field throughout the
chain of the existing BLOB API is too expensive.  So he went with the
lightweight pseudo-nulls idea.

Take a look at my sample SQL a few emails back if you want to see an
application.  Most of the time the operational result of passing pointers
to native objects through the DB API with respect to storage is null.  The
desired operation is often a side effect like sending something over the
network or efficiently keeping track of whatever summarized results in a
memory vtable.  The important part from my perspective is that the DB can
store precise details about embedded object initializations and
interactions without having to invent a storage format for every little
setting or add serialization methods to third party library objects lacking
serialization operations.

On Fri, Jul 21, 2017 at 3:14 PM, Keith Medcalf  wrote:

>
> Pointers cannot be stored in the database.  They are simply value objects
> that get passed around between bits and pieces -- such as passing a pointer
> from an SQL statement into an extension function or virtual table all
> within the same linkage unit (that is locally within a single process).
> The whole point, I believe, was to prevent pointers from being persisted to
> the database.  And if you foolishly try to store them in the database, then
> the value stored is NULL.  Storing pointers in BLOBs would defeat this
> purpose since you would not be preventing natural selection.
>
> And if I have the purpose wrong, then I really don't understand why there
> needs to be a separate value type for pointers at all ...
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of petern
> >Sent: Friday, 21 July, 2017 14:49
> >To: SQLite mailing list
> >Subject: Re: [sqlite] SQLite 3.20.0 postponed
> >
> >Indeed.  Or Type(T) of long, or sqlite_int64, or whatever is most
> >universal
> >where porting other architectures is concerned. It would be a shame
> >if the
> >API had to be changed later because the integer type chosen couldn't
> >hold a
> >pointer.
> >
> >From there, in a closed compiled host C/C++ setting with the
> >discipline of
> >header files, one may simply perform the stricmp("CARRAY",(const
> >char*) T)
> >cast to T string pointer comparison as desired and leave the API out
> >of
> >that part.  This leaves open the more conventional test of T with
> >enum or
> >#define compile time constant.
> >
> >Now it remains, on the surface, that this new pseudo null pointer API
> >appears redundant in light of the existing BLOB interface and the
> >possibility of simply adding Type(T) versions of those BLOB function
> >signatures to the API.
> >
> >How was adding to the BLOB API ruled out?  Is there a fundamental
> >problem
> >with slightly broadening the BLOB API to deal with typed host
> >objects?
> >Surely returning zero for the Type(T) would be backward compatible,
> >would
> >it not?  Just asking.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >On Fri, Jul 21, 2017 at 12:48 PM, Keith Medcalf 
> >wrote:
> >
> >> On Friday, 21 July, 2017 10:51, Richard Hipp 
> >wrote:
> >>
> >> > But sqlite3_result_pointer() and sqlite3_bind_pointer() are not
> >> > invoked from those languages.  The _pointer() routines are
> >invoked
> >> > from C, and C does easily support string literals that are static
> >> > strings.
> >>
> >> > A C-language wrapper around sqlite3_result_pointer() and
> >> > sqlite3_bind_pointer() that interfaces to the non-static-string
> >> > language can simply insert the required static string.
> >>
> >> Well, yes.  Technically.  Almost everything everywhere is called
> >from code
> >> written in C.  Very little meaningful anything is written in any
> >other
> >> language.  So although you may be writing something in Java
> >(heavens
> >> forbid), Java was written in C.  So in the end, all calls are made
> >from
> >> software written in C.  This is the way of the world.  Some people
> >just
> >> like to add lots of syntactical sugar and overhead which eventually
> >ends up
> >> being C code

Re: [sqlite] Database version

>The only trouble is - how do I know what version were used.
>My application may use some features that is available right now and
>I want to use them because they are great.
>But if I open the DB from the old version I will simply get an error.

No, you are incorrect.  If you open a DB file that was created in *any* version 
of SQLite 3 with the "current" version of SQLite3 it will *ALWAYS* work just 
fine.  When you add new features to the database (such as create a conditional 
index), you will not be able to open that DB file with versions of SQLite 3 
prior to the introduction of that feature (you will get an error).

That is, you can always open things made by older versions in a later version 
of the software.  What you cannot do is use a DB that contains "features" in a 
version of SQLite 3 prior to that features introduction (because, since that 
feature is not understood, it is indistinguishable from database corruption).

So, I can create a DB file with version 3.20.0 that will work perfectly fine if 
opened with SQLite 3 version 3.0.0, and go back and forth and use any version 
of SQLite 3 at all, even concurrently on the same database.  Until I add a 
feature that is only supported as of a certain version.  From that point forth 
(until you undo that feature usage) you can only use version of SQLite 3 that 
understand the feature you are using.




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

Re: [sqlite] Database version

Hi, Keith,

On Fri, Jul 21, 2017 at 4:17 PM, Keith Medcalf  wrote:
>>The only trouble is - how do I know what version were used.
>>My application may use some features that is available right now and
>>I want to use them because they are great.
>>But if I open the DB from the old version I will simply get an error.
>
> No, you are incorrect.  If you open a DB file that was created in *any* 
> version of SQLite 3 with the "current" version of SQLite3 it will *ALWAYS* 
> work just fine.  When you add new features to the database (such as create a 
> conditional index), you will not be able to open that DB file with versions 
> of SQLite 3 prior to the introduction of that feature (you will get an error).
>
> That is, you can always open things made by older versions in a later version 
> of the software.  What you cannot do is use a DB that contains "features" in 
> a version of SQLite 3 prior to that features introduction (because, since 
> that feature is not understood, it is indistinguishable from database 
> corruption).
>
> So, I can create a DB file with version 3.20.0 that will work perfectly fine 
> if opened with SQLite 3 version 3.0.0, and go back and forth and use any 
> version of SQLite 3 at all, even concurrently on the same database.  Until I 
> add a feature that is only supported as of a certain version.  From that 
> point forth (until you undo that feature usage) you can only use version of 
> SQLite 3 that understand the feature you are using.

I presume the same is true for the DB created with 3.0.0 and opened with 3.20.0.
As long as I will use the standard SQL command and don't use any new
feature introduced in
3.20.0.

The only trouble is - I may not know in advance what version of SQLite was used
and my program - which is based on the C API - will use all those new
features available,
because it makes the program better/faster/more robust/etc.

But then I will get an error, right? Because the feature I want is not
supported by the database.

Or ma I missing something?

Thank you.

>
>
>
>
> ___
> 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] Database version

2017-07-21 Thread Warren Young

On Jul 21, 2017, at 10:11 AM, Jens Alfke  wrote:
> 
>> On Jul 21, 2017, at 8:25 AM, Igor Korot  wrote:
>> 
>> "Using SQLite library version X.Y.Z connecting to the DB version A.B.C"
> 
> But why should your user care? As a developer I’ve been working with SQLite 
> since 2004 and I’ve never needed to know the internal file-format ID in a 
> database. 

While the base DB file format hasn’t changed, there are features like WAL mode 
that only work in newer versions.

Concrete example:

Adobe Lightroom 6 — which is based on SQLite — hasn’t had a major new version 
in over 2 years, but they shipped a new point release just days ago.  From an 
outsider’s perspective, we can therefore guess that either:

1. They’re still shipping some version of SQLite from prior to late April 2015 
(thus, no newer than SQLite 3.8.9) so that a Lightroom 6.12 catalog file can 
still be opened in 6.0 and vice versa; or

2. Adobe is updating their embedded copy of SQLite with each new point release 
and thus shipped SQLite 3.19.3 in Lightroom 6.12, and they’re relying on 
SQLite’s compatibility to allow the catalog to still be opened in Lightroom 6.0.

That gives us a spread of 12 different major versions of SQLite that could be 
in use here.

Now to complicate that, macOS also ships a /usr/bin/sqlite3, which happens to 
be 3.16.0 in macOS 10.12, so my superficial question is, can I safely open *and 
modify* the Lightroom catalog file with the macOS version of SQLite, or even 
some newer version?

The real question, though, is how can I know the answer for any combination of 
versions?  I want to point some tool at a SQLite DB file and know which version 
of SQLite wrote it, and what options were set by that other program.  That in 
turn lets me know which versions of SQLite I can safely use with that DB file.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database version

Hi, Peter,

On Fri, Jul 21, 2017 at 11:06 AM, Peter Da Silva
 wrote:
> https://www.sqlite.org/c3ref/c_source_id.html

Those are for the library.
But...

Let's say I made some database files 2 years ago.
Now I want the current SQLite code to open them and performs some queries
from the C interface.

>
> This is also stored at offset 96 in the db file:
>
> https://www.sqlite.org/fileformat.html

Is this the number I'm after?

Thank you.

>
> I don’t think there’s a pragma for extracting it from within SQLite code.
>
> On 7/21/17, 9:58 AM, "sqlite-users on behalf of Igor Korot" 
>  ikoro...@gmail.com> wrote:
>
>  Hi, ALL,
> Is there a way to know the version of the .db file I am using?
>
> I'd like to issue some kind of SELECT statement to get it.
>
> Looks like there is an interface to get the library version, but I don't
> see anything for a db file.
>
> Thank you.
> ___
> 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] Database version

On 7/21/17, 10:14 AM, "sqlite-users on behalf of Igor Korot" 

wrote:
> This is also stored at offset 96 in the db file:
>
> https://www.sqlite.org/fileformat.html

Is this the number I'm after?

That’s the version of SQLite that most recently wrote to the file. The word 
before it is the transaction number when it was updated. 

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

Re: [sqlite] SQLite 3.20.0 postponed

2017-07-21 Thread Gwendal Roué


> Le 21 juil. 2017 à 17:55, Gwendal Roué  a écrit :
> 
> First, this strcmp() give a lot of work to languages that wrap SQLite and 
> lack support for "static strings". Building a global \0-terminated buffer 
> that never gets deallocated is not always that easy :-)

For the record, here is the commit that brings support for SQLite 3.20.0 : 
https://github.com/groue/GRDB.swift/commit/0a603f1be3966d478b505373af95d257224ce5b0
 


The context is custom FTS5 tokenisers for Swift: 
https://github.com/groue/GRDB.swift/blob/v1.2.2/Documentation/FTS5Tokenizers.md 


Gwendal

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

Re: [sqlite] Trigger firing order

On 21 Jul 2017, at 5:52pm, Simon Slavin  wrote:

> On 21 Jul 2017, at 4:27pm, Jean-Luc Hainaut  
> wrote:
> 
>> Hence my modest proposal: wouldn't it be a nice idea to make this unofficial 
>> order a feature of SQLite (just like DB2)? This would make the multiple 
>> triggers of a kind much more useful as it currently are.
> 
> I’m minded to leave things as they are, with the order undefined.  If you 
> really want to trigger a number of different operations in a specific order, 
> put those operations all in one trigger, one after another.

Alternatively, instead of having

Operation A
Trigger A1 on operation A performs operation B
Trigger A2 on operation A performs operation C
Trigger A3 on operation A performs operation D

have

Operation A
Trigger A1 on operation A performs operation B
Trigger B1 on operation B performs operation C
Trigger A3 on operation A performs operation D

That way you’ll know that you’ll get either ABCD or ABDC, but either way C will 
be executed after B.

> I have a question.  Would you expect to see depth-first or width-first 
> recursion, and why ?

Still interested in this.

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

Re: [sqlite] Trigger firing order

On 21 Jul 2017, at 4:27pm, Jean-Luc Hainaut  wrote:

> Hence my modest proposal: wouldn't it be a nice idea to make this unofficial 
> order a feature of SQLite (just like DB2)? This would make the multiple 
> triggers of a kind much more useful as it currently are.

I’m minded to leave things as they are, with the order undefined.  If you 
really want to trigger a number of different operations in a specific order, 
put those operations all in one trigger, one after another.

I have a question.  Would you expect to see depth-first or width-first 
recursion, and why ?

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

Re: [sqlite] Database version

2017-07-21 Thread Andy Ling

>Let's say I made some database files 2 years ago.
>Now I want the current SQLite code to open them and performs some queries
>from the C interface.

I would ask why do you care? Sqlite will read old databases without any problem.

What you may be interested in is what schema version and that's up to you to 
code.
There is a PRAGMA user_version you might want to use or you can create your
own table with stuff like schema_version in it.

Regards

Andy Ling
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

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

Re: [sqlite] Database version

On 21 Jul 2017, at 4:14pm, Igor Korot  wrote:
> 
> Let's say I made some database files 2 years ago.
> Now I want the current SQLite code to open them and performs some queries
> from the C interface.

SQLite file format hasn’t changed in over 10 years.  Your current code and 
current version of SQLite will open that database perfectly.

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

Re: [sqlite] Database version

Hi, Andy,

On Fri, Jul 21, 2017 at 11:19 AM, Andy Ling  wrote:
>>Let's say I made some database files 2 years ago.
>>Now I want the current SQLite code to open them and performs some queries
>>from the C interface.
>
> I would ask why do you care? Sqlite will read old databases without any 
> problem.

In my "Help -> About..." I'd like to say something like:

"Using SQLite library version X.Y.Z connecting to the DB version A.B.C"

>
> What you may be interested in is what schema version and that's up to you to 
> code.
> There is a PRAGMA user_version you might want to use or you can create your
> own table with stuff like schema_version in it.

Thank you.

>
> Regards
>
> Andy Ling
> ---
> This email has been scanned for email related threats and delivered safely by 
> Mimecast.
> For more information please visit http://www.mimecast.com
> ---
>
> ___
> 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] Trigger firing order

2017-07-21 Thread Jean-Luc Hainaut

Hello all,

My question concerns the order (deterministic or not) in which triggers
of the same kind (same table, same event, same position) fire.

My application is a temporal database in which each table stores the
history of one attribute of a set of entities. A view collects the last
value of each table to build the current state of these entities.

Let us simplify the problem as follows:
1. The DB comprises base tables T1(K,C1,..), T2(K,C2,..), T3(K,C3,..).
Each table comprises primary key K + column Ci + some system data.
2. SQL view T(K,C1,C2,C3) collects, for each value of K, the last value
from each base table.
3. Users update data through view T with such queries as "update T set
C1='f', C3='g' where K=12"
4. "instead of" triggers translate this update into operations on those
base tables that are affected by the update, here T1 and T3.

5. More precisely, the translation of this update proceeds in two steps:
5.1 a value "v" is computed and stored in a reference table
(typically the current_date). This operation must be performed first.
5.2 for each base table affected, an "update" then an " insert" are
performed, using value "v". These operations can be performed in any order.

Each operation is controlled by a trigger "instead of update on T". The
trigger of the first step is unconditional (no "when" clause). Each
operation of step 2 is controlled by a trigger with a filter like "when
new.C1 <> old.C1".

The problem is that the operation of step 1 MUST be performed BEFORE the
operations of step 2. So, technically, the trigger of step 1 must fire
before those of step 2.
In most DBMS (notably Oracle, DB2, PostgreSQL, SQL Server, InterBase),
firing order of similar triggers can be specified, either explicitly or
according to naming or creation time rules (no problem in MySQL: only
one trigger of a kind is allowed!).
SQLite allows multiple triggers of the same kind, which is a very good
thing, but its documentation tells nothing on the firing order issue,
which seems to be interpreted as: "firing order is arbitrary". This
considerably limits its usefulness.

The usual responses to trigger ordering problems in forums are of two kinds:
- "Your schema probably is flawed. Fix it."
- "Gather all your triggers into a single one."

No, my schema is not flawed and gathering my triggers into a single one
is impossible since it would require a programming pattern that is
missing in SQLite trigger body: "if (new.C1 <> old.C1) then statement>". It can be simulated for updates ("update ... where ... and
new.C1 <> old.C1") but not for inserts ("insert" has no "where" clause).

It seems that, in current SQLite implementations, trigger firing order
is not quite arbitrary. In several tests I (and others) performed,
triggers appear to always fire in "inverse creation time" order.

Hence my modest proposal: wouldn't it be a nice idea to make this
unofficial order a feature of SQLite (just like DB2)? This would make
the multiple triggers of a kind much more useful as it currently are.

Thanks for your attention

Have a nice day

Jean-Luc Hainaut

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

Re: [sqlite] Database version

2017-07-21 Thread Bob Friesenhahn


On Fri, 21 Jul 2017, Igor Korot wrote:


In my "Help -> About..." I'd like to say something like:

"Using SQLite library version X.Y.Z connecting to the DB version A.B.C"


Is this a sensible expectation?  Several programs may access the 
database at once, and all could be using a different sqlite version.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database version

On Fri, Jul 21, 2017 at 11:55 AM, Peter Da Silva
 wrote:
> If SQLite3 can open the file at all, the first 16 characters will be "SQLite 
> format 3\000".

Or "SQLite format 4\000" soon.
Or "SQLite format 3\000" for an ancient db file.

Thank you.

>
> On 7/21/17, 10:46 AM, "sqlite-users on behalf of Igor Korot" 
>  ikoro...@gmail.com> wrote:
>
> Hi, Peter et al,
>
> On Fri, Jul 21, 2017 at 11:35 AM, Peter Da Silva
>  wrote:
> > The problem is that SQLITE_VERSION_NUMBER is not “the database 
> version”, it’s something like “the last version of SQLite that committed a 
> transaction”.
> >
> > The database version number is “3”.
>
> I guess it is not stored anywhere.
>
> That's OK. I will just read and parse first 16 characters of the file...
>
> Thank you.
>
> >
> >
> > ___
> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE database using parameters

> On Jul 21, 2017, at 6:45 AM, Peter Da Silva  
> wrote:
> 
> Have a look at prepared statements and statement parameters.

Agreed.

PLEASE, PLEASE, do not try to splice parameters into SQL strings! Any mistakes 
in this code leave you vulnerable to SQL Injection Attacks, which are 
depressingly common and cause great damage in the real world. (Some of the 
examples already posted in this thread contain such mistakes…)

SQLite, like every other SQL database I know of, has APIs that let you safely 
plug runtime parameters into statements without having to do string 
concatenation or quoting or sanitization. The resulting code is safe, more 
readable, and as a bonus it’s faster because the database only has to compile 
and optimize the statement once, no matter how many times it runs.

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

Re: [sqlite] Trigger firing order

> a programming pattern that is missing in SQLite trigger body: "if (new.C1
<> old.C1) then ".  It can be simulated for updates ("update
... where ... and new.C1 <> old.C1") but not for inserts ("insert" has no
"where" clause).

Maybe so.  But, INSERT can accept data rows from a SELECT statement which
both does have a WHERE clause and is aware of the trigger body variables.

https://sqlite.org/lang_insert.html

Taking your example NEW.C1 and OLD.C1,  statements like  INSERT INTO 
SELECT  [FROM ] WHERE   AND/OR 
are perfectly well formed.

SELECT statements without FROM clause are also perfectly well formed:

INSERT INTO   SELECT NEW.C1 WHERE NEW.C1<>OLD.C1;

I've often read complaints on this forum about how SQLite's
non-deterministic trigger firing order is somehow "deliberately crazy".
The truth is the documentation is simply telling you that race conditions
are possible due to idiosyncratic query plans or lock acquisition order.
Yes, I too would prefer to have conditional statements better than SELECT
RAISE(IGNORE) in the trigger body.  It would save a lot of duplicate code
in terms of triggers that differ only by a few characters in the body and
WHEN clause.  But, one learns to make do.











On Fri, Jul 21, 2017 at 8:27 AM, Jean-Luc Hainaut <
jean-luc.hain...@unamur.be> wrote:

> Hello all,
>
> My question concerns the order (deterministic or not) in which triggers of
> the same kind (same table, same event, same position) fire.
>
> My application is a temporal database in which each table stores the
> history of one attribute of a set of entities. A view collects the last
> value of each table to build the current state of these entities.
>
> Let us simplify the problem as follows:
> 1. The DB comprises base tables T1(K,C1,..), T2(K,C2,..), T3(K,C3,..).
> Each table comprises primary key K + column Ci + some system data.
> 2. SQL view T(K,C1,C2,C3) collects, for each value of K, the last value
> from each base table.
> 3. Users update data through view T with such queries as "update T set
> C1='f', C3='g' where K=12"
> 4. "instead of" triggers translate this update into operations on those
> base tables that are affected by the update, here T1 and T3.
> 5. More precisely, the translation of this update proceeds in two steps:
> 5.1 a value "v" is computed and stored in a reference table (typically
> the current_date). This operation must be performed first.
> 5.2 for each base table affected, an "update" then an " insert" are
> performed, using value "v". These operations can be performed in any order.
>
> Each operation is controlled by a trigger "instead of update on T".  The
> trigger of the first step is unconditional (no "when" clause). Each
> operation of step 2 is controlled by a trigger with a filter like "when
> new.C1 <> old.C1".
>
> The problem is that the operation of step 1 MUST be performed BEFORE the
> operations of step 2. So, technically, the trigger of step 1 must fire
> before those of step 2.
> In most DBMS (notably Oracle, DB2, PostgreSQL, SQL Server, InterBase),
> firing order of similar triggers can be specified, either explicitly or
> according to naming or creation time rules (no problem in MySQL: only one
> trigger of a kind is allowed!).
> SQLite allows multiple triggers of the same kind, which is a very good
> thing, but its documentation tells nothing on the firing order issue, which
> seems to be interpreted as: "firing order is arbitrary".  This considerably
> limits its usefulness.
>
> The usual responses to trigger ordering problems in forums are of two
> kinds:
> - "Your schema probably is flawed. Fix it."
> - "Gather all your triggers into a single one."
>
> No, my schema is not flawed and gathering my triggers into a single one is
> impossible since it would require a programming pattern that is missing in
> SQLite trigger body: "if (new.C1 <> old.C1) then ".  It can
> be simulated for updates ("update ... where ... and new.C1 <> old.C1") but
> not for inserts ("insert" has no "where" clause).
>
> It seems that, in current SQLite implementations, trigger firing order is
> not quite arbitrary. In several tests I (and others) performed, triggers
> appear to always fire in "inverse creation time" order.
>
> Hence my modest proposal: wouldn't it be a nice idea to make this
> unofficial order a feature of SQLite (just like DB2)? This would make the
> multiple triggers of a kind much more useful as it currently are.
>
> Thanks for your attention
>
> Have a nice day
>
> Jean-Luc Hainaut
>
> ___
> 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] SQLite 3.20.0 postponed

On 7/21/17, Gwendal Roué  wrote:
>
> First, this strcmp() give a lot of work to languages that wrap SQLite and
> lack support for "static strings".

But sqlite3_result_pointer() and sqlite3_bind_pointer() are not
invoked from those languages.  The _pointer() routines are invoked
from C, and C does easily support string literals that are static
strings.

A C-language wrapper around sqlite3_result_pointer() and
sqlite3_bind_pointer() that interfaces to the non-static-string
language can simply insert the required static string.

We do not want the static string to be a parameter to a generic
higher-level interface.  That defeats the purpose of the static
string.  Remember, the string is a "pointer type".  We do not want to
support interfaces that provide access to pointers of any type the
user wants.  We are not trying to recreate C++ templates or other
interfaces that work with arbitrary types.  Each use of _pointer() is
intended to be used for a single narrowly defined purpose.

-- 
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] UPDATE database using parameters

2017-07-21 Thread Rowan Worth

On 21 July 2017 at 17:50, Tim Streater  wrote:

>$sql = "UPDATE Movies SET name = '$newname' where id=$newid";
>
> Personally I don't like forcing PHP to scan strings so I tend to use
> concatentation, rewriting the last of these as:
>
>$sql = 'UPDATE Movies SET name = \'' . $newname . '\' where id=' .
> $newid;
>
> but that's just a personal style preference.
>

Either way, I hope for your sake no one releases a movie called:
Avengers!'; Drop Table Movies; --

;)

As Simon says, better to use something like $sql = 'UPDATE Movies SET name
= ? WHERE id = ?' and bind your parameters in.
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE database using parameters

On 21 Jul 2017, at 11:14, Rowan Worth  wrote:

> On 21 July 2017 at 17:50, Tim Streater  wrote:
>
>>$sql = "UPDATE Movies SET name = '$newname' where id=$newid";
>>
>> Personally I don't like forcing PHP to scan strings so I tend to use
>> concatentation, rewriting the last of these as:
>>
>>$sql = 'UPDATE Movies SET name = '' . $newname . '' where id=' .
>> $newid;
>>
>> but that's just a personal style preference.
>>
>
> Either way, I hope for your sake no one releases a movie called:
> Avengers!'; Drop Table Movies; --

That’s going to be a problem anyway if your strings can legally contain 
single-quotes, never mind little Bobby Tables. Sanitisation is called for.

> As Simon says, better to use something like $sql = 'UPDATE Movies SET name
> = ? WHERE id = ?' and bind your parameters in.

Not disagreeing, but I was operating on the first-things-first principle.


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

Re: [sqlite] Database version

Hi, Bob,

On Fri, Jul 21, 2017 at 11:27 AM, Bob Friesenhahn
 wrote:
> On Fri, 21 Jul 2017, Igor Korot wrote:
>>
>>
>> In my "Help -> About..." I'd like to say something like:
>>
>> "Using SQLite library version X.Y.Z connecting to the DB version A.B.C"
>
>
> Is this a sensible expectation?  Several programs may access the database at
> once, and all could be using a different sqlite version.

And that is OK.
They will just display different numbers as X.Y.Z.

Thank you.

>
> Bob
> --
> Bob Friesenhahn
> bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
> GraphicsMagick Maintainer,http://www.GraphicsMagick.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] Database version

The problem is that SQLITE_VERSION_NUMBER is not “the database version”, it’s 
something like “the last version of SQLite that committed a transaction”.

The database version number is “3”.
 

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

Re: [sqlite] Database version

Hi, Peter et al,

On Fri, Jul 21, 2017 at 11:35 AM, Peter Da Silva
 wrote:
> The problem is that SQLITE_VERSION_NUMBER is not “the database version”, it’s 
> something like “the last version of SQLite that committed a transaction”.
>
> The database version number is “3”.

I guess it is not stored anywhere.

That's OK. I will just read and parse first 16 characters of the file...

Thank you.

>
>
> ___
> 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] SQLite 3.20.0 postponed

2017-07-21 Thread Gwendal Roué


> Le 17 juil. 2017 à 20:54, Richard Hipp  a écrit :
> 
> The 3.20.0 release will be delayed.  Some concerns came up over the
> new sqlite3_value_pointer() interface.  Interface chagnes were made
> over the weekend.  But there are still concerns.  So the decision has
> been made to back off and give the current design a few weeks to soak
> before trying to press forward with a release which will commit us to
> a particular design.
> 
> The draft website is still up at https://sqlite.org/draft - note that
> the change log at https://sqlite.org/draft/releaselog/3_20_0.html now
> identifies three (obscure) backwards compatibility breaks.  Your input
> on these changes is requested.

Hello,

When I read the documentation for sqlite3_bind_pointer, I read:

> The T parameter should be a static string

The reason is pretty clear: this T parameter will be used later by 
sqlite3_value_pointer, for a string comparison with strcmp(). It hence has to 
remain is memory forever - and static strings are good at that.

I could test it and make it work reliably in Swift for custom FTS5 tokenisers.

Here is my comment: I wonder if the key comparison with strcmp() is really 
necessary.

First, this strcmp() give a lot of work to languages that wrap SQLite and lack 
support for "static strings". Building a global \0-terminated buffer that never 
gets deallocated is not always that easy :-)

Next, there are techniques for building unique "keys" that hold in a machine 
word, and can simply be compared with ==. For example:

typedef void *sqlite3_pointer_key_t;   // defined in sqlite3.h
sqlite3_pointer_key_t key1 = "my_key"; // nice for debugging
sqlite3_pointer_key_t key2 = // hard core but still valid

Maybe this is considered awful practices - I'm certainly not a C expert.

And this would also force functions that use the new pointer APIs to expose 
those keys in some header (such as FTS5()). You may have chosen the current 
technique precisely because you don't want such API pollution.

What were your rationale behind this choice?

Thanks in advance,
Gwendal Roué

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

Re: [sqlite] Database version

If SQLite3 can open the file at all, the first 16 characters will be "SQLite 
format 3\000".

On 7/21/17, 10:46 AM, "sqlite-users on behalf of Igor Korot" 
 
wrote:

Hi, Peter et al,

On Fri, Jul 21, 2017 at 11:35 AM, Peter Da Silva
 wrote:
> The problem is that SQLITE_VERSION_NUMBER is not “the database version”, 
it’s something like “the last version of SQLite that committed a transaction”.
>
> The database version number is “3”.

I guess it is not stored anywhere.

That's OK. I will just read and parse first 16 characters of the file...

Thank you.

>
>
> ___
> 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] Database version