[sqlite] Second test of json and index expressions, not so good

[Domingo Alvarez Duarte]

Hello !  

That's awesome !  

We can write cleaner queries with this.  

Thank you so much !  

Cheers !  
>  Sun Sep 06 2015 12:39:03 am CEST CEST from "Richard Hipp"
>  Subject: Re: [sqlite] Second test of json and index
>expressions, not so good
>
>  On 9/4/15, Domingo Alvarez Duarte  wrote:
>  
>>select json_extract(json, '$.value') AS val
>> from json_tbl where val = 'the_value_1';
>> 

>  The latest trunk check-in now uses an index for this query
> 
> 
> -- 
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] BEGINNER - Transactions in shell script

[Luuk]

On 05-09-15 22:27, Simon Slavin wrote:
> On 5 Sep 2015, at 9:18pm, Petr L?z?ovsk?  wrote:
>
>> Have some shell scripts working with sqlite. Receiving incoming payments 
>> from bank via HTTP API and pushing it into database. This script will start 
>> periodically, every single hour.
>>
>> Want to prevent situation only few payments are written and script failed 
>> for some reason. Have read about sqlite transactions and understood so I 
>> should start every writing sequence with BEGIN; statement, than made all 
>> inserts and than COMMIT; statement. Is this all how it is working? Should I 
>> do something more to prevent such unwanted situation?
> You have it correct.  If you put multiple change commands in one transaction, 
> then either they are all executed or none of them are executed.  Even if your 
> program crashes in the middle of a command, when SQLite opens the file again 
> it works out what happened and restores a 'clean' database.
>
> Theoretically you would have to always open a transaction for SQL.  Issuing 
> INSERT before BEGIN would result in an error message.  But SQL is kind and 
> opens a one-command transaction if you forget.
>

Suppose i have 'test.sql':
.echo on
DELETE FROM test;
BEGIN;
INSERT INTO test VALUES(1,'test1');
INSERT INTO test VALUES(3,'test3',3);
INSERT INTO test VALUES(2,'test2');
COMMIT;
SELECT * FROM test;

And a database 'test.sqlite' with the following table:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(i int, t text);
COMMIT;

If i do:
type test.sql | sqlite3.exe test.sqlite

Than there is an error executing this line:
INSERT INTO test VALUES(3,'test3',3);

But the transaction is not stopped, or rolled back.
What am i missing?


Output of 'type test.sql | sqlite3.exe test.sqlite':
DELETE FROM test;
BEGIN;
INSERT INTO test VALUES(1,'test1');
Error: near line 5: table test has 2 columns but 3 values were supplied
INSERT INTO test VALUES(2,'test2');
COMMIT;
SELECT * FROM test;
1|test1
2|test2

[sqlite] MemoryBarrier compile error on WIndows MinGW gcc 4.8.1

[Luuk]

On 06-09-15 07:47, Scott Robison wrote:
> On Sat, Sep 5, 2015 at 9:58 PM, Keith Medcalf  wrote:
>
>> Trunk does not compile with MinGW [gcc version 4.8.1 (GCC)] on Windows.
>>
>> sqlite3x.c: In function 'sqlite3MemoryBarrier':
>> sqlite3x.c:20410:17: error: expected expression before ')' token
>> MemoryBarrier();
>>
>> /*
>> ** Try to provide a memory barrier operation, needed for initialization
>> only.
>> */
>> SQLITE_PRIVATE void sqlite3MemoryBarrier(void){
>>MemoryBarrier();
>> }
>>
>> This references a definition in winnt.h
>>
>> #if _WIN32_WINNT >= _WIN32_WINNT_VISTA
>> # if defined(_AMD64_) || defined(__X86_64)
>> #  define MemoryBarrier __faststorefence
>>
>> # elif defined(_IA64_)
>>
>> #  define MemoryBarrier __mf
>> # else
>>
>> void __mingworg_MemoryBarrier(void);
>> #  define MemoryBarrier __mingworg_MemoryBarrier
>> # endif
>>
>> #else
>>
>> # define MemoryBarrier
>> #endif
>>
>> If the _WIN32_WINNT value is less than Windows 6/Vista [0x600] then the
>> error is generated.  If the version is 0x600 or greater, it compiles just
>> fine.
>>
>> Of course, versions of WinNT less than 6 are "no longer diddled by
>> Microsoft" (meaning they are stable development targets).
>>
> My Commodore 64 is a stable development target! Sweet!
>
no, because

SELECT *
FROM StableDevelopment
WHERE WinNTVersion<=6 AND Platform LIKE 'Commodore%'

returns no results.
(because WinNTVersion should return NULL)

[sqlite] MemoryBarrier compile error on WIndows MinGW gcc 4.8.1

[Richard Hipp]

On 9/5/15, Keith Medcalf  wrote:
>
> Trunk does not compile with MinGW [gcc version 4.8.1 (GCC)] on Windows.
>

Please retry after the latest check-in and let us know whether or not
the problem has been resolved.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Third test of json and index expressions, now it works

[E.Pasma]

Op 6 sep 2015, om 03:17 heeft Scott Robison het volgende geschreven:
>
> select * from (select a as b from c) where b is something
>
Still column expressions are called mutiple times.

SQLite 3.8.11 2015-07-27 13:49:41  
b8e92227a469de677a66da62e4361f099c0b79d0
create table c(a)
;
explain query plan
select 1 from (select (select a from c) as b) where b>0
;
1|0|0|EXECUTE SCALAR SUBQUERY 2
2|0|0|SCAN TABLE c
1|0|0|EXECUTE SCALAR SUBQUERY 3
3|0|0|SCAN TABLE c
0|0|0|SCAN SUBQUERY 1

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Markus Weiland]

Hi,

I've discovered a potential bug in handling of SQLite database files on 
gvfs mounted network shares.

Steps to reproduce:
1. Under vanilla Ubuntu 15.04 with latest official patches and SQLite 
version 2.8.17, mount a Windows / SMB network share via Nautilus file 
manager. The share can be via SMBv2 or v3.
2. Assert that the share is accessible on console via 
`/var/run/user/USERID/gvfs/smb-share:server=SERVERADDRESS,share=SHARENAME/`
3. In a terminal `cd` to the share
4. `touch test.txt` to verify the share is writeable
5. Open a new SQLite database in the same folder with `sqlite test.sqlite`
6. Execute `create table tbl1(one varchar(10), two smallint);` to create 
a table
7. Insert data with `insert into tbl1 values('hello!',10);`

Actual result:
* Inserting data fails with `SQL error: database disk image is malformed`

Expected result:
* Data is inserted and correctly persisted to disk

Notes:
* After the above steps, the database file exists and is about 3kb large.
* I originally noticed the issue with Calibre, which is unable to 
maintain its SQLite ebook database file on a network share because of 
this issue.

I'd appreciate if someone on the list could verify the above findings 
and open an official bug report as needed.

Thank you.

[sqlite] BEGINNER - Transactions in shell script

[Yuriy M. Kaminskiy]

Luuk  writes:

> On 05-09-15 22:27, Simon Slavin wrote:
>> On 5 Sep 2015, at 9:18pm, Petr L?z?ovsk?  wrote:
>>
>>> Have some shell scripts working with sqlite. Receiving incoming
>>> payments from bank via HTTP API and pushing it into database. This
>>> script will start periodically, every single hour.
>>>
>>> Want to prevent situation only few payments are written and script
>>> failed for some reason. Have read about sqlite transactions and
>>> understood so I should start every writing sequence with BEGIN;
>>> statement, than made all inserts and than COMMIT; statement. Is
>>> this all how it is working? Should I do something more to prevent
>>> such unwanted situation?
>> You have it correct.  If you put multiple change commands in one
>> transaction, then either they are all executed or none of them are
>> executed.

Not quite. Even if some statement failed, if you COMMIT in the end, it
will succeed and database will be (partially) modified.

It's application developer responsibility to check for errors and issue
ROLLBACK instead of COMMIT, if desirable (BTW, in case of some
errors^[], sqlite3 may cancel [and automatically ROLLBACK] transaction,
it is application developer responsibility to check if transaction is
still active after errors [with sqlite3_get_autocommit()]).

^[] IIRC, exact conditions are not documented, this is
implementation-defined behavior.

>> Even if your program crashes in the middle of a command,
>> when SQLite opens the file again it works out what happened and
>> restores a 'clean' database.

(yes, if program crashes (or closes connection) before COMMIT was
executed, it means "implicit ROLLBACK").

>> Theoretically you would have to always open a transaction for SQL.
>> Issuing INSERT before BEGIN would result in an error message.  But
>> SQL is kind and opens a one-command transaction if you forget.
>>
>
> Suppose i have 'test.sql':
> .echo on

Add
  .bail on
here.

> DELETE FROM test;
> BEGIN;
> INSERT INTO test VALUES(1,'test1');
> INSERT INTO test VALUES(3,'test3',3);
> INSERT INTO test VALUES(2,'test2');
> COMMIT;
> SELECT * FROM test;

(and normally I'd expect to see DELETE *inside* transaction);

> And a database 'test.sqlite' with the following table:
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE test(i int, t text);
> COMMIT;
>
> If i do:
> type test.sql | sqlite3.exe test.sqlite
>
> Than there is an error executing this line:
> INSERT INTO test VALUES(3,'test3',3);
>
> But the transaction is not stopped, or rolled back.
> What am i missing?



> Output of 'type test.sql | sqlite3.exe test.sqlite':
> DELETE FROM test;
> BEGIN;
> INSERT INTO test VALUES(1,'test1');
> Error: near line 5: table test has 2 columns but 3 values were supplied
> INSERT INTO test VALUES(2,'test2');
> COMMIT;
> SELECT * FROM test;
> 1|test1
> 2|test2

[sqlite] BEGINNER - Transactions in shell script

[Rowan Worth]

On 6 September 2015 at 18:26, Luuk  wrote:
>
> Suppose i have 'test.sql':
> .echo on
> DELETE FROM test;
> BEGIN;
> INSERT INTO test VALUES(1,'test1');
> INSERT INTO test VALUES(3,'test3',3);
> INSERT INTO test VALUES(2,'test2');
> COMMIT;
> SELECT * FROM test;
>
> And a database 'test.sqlite' with the following table:
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE test(i int, t text);
> COMMIT;
>
> If i do:
> type test.sql | sqlite3.exe test.sqlite
>
> Than there is an error executing this line:
> INSERT INTO test VALUES(3,'test3',3);
>
> But the transaction is not stopped, or rolled back.
> What am i missing?
>

The default behaviour of the sqlite3 shell when a statement encounters an
error is to continue executing the remaining statements. Try sqlite3.exe
-bail and instead it will stop at the first error. In that case the COMMIT
statement is never executed so the statements within the half-finished
transaction don't take effect.

The DELETE of course remains effective - it is done and dusted by the time
the transaction starts.

-Rowan

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/06/2015 06:16 AM, Markus Weiland wrote:
> I've discovered a potential bug in handling of SQLite database
> files on gvfs mounted network shares.

SQLite doesn't support being stored on the network for several
reasons, including that network file protocols don't implement fully
and correctly various access and locking, and that some journaling
modes like WAL require shared memory and hence can't be networked.
This is covered in the FAQ:

  https://www.sqlite.org/faq.html#q5

Or if you want to be pedantic, it is gvfs and networked filesystems
that have the bug.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iEYEARECAAYFAlXsTFcACgkQmOOfHg372QQyeQCeJW2PjkZmQQ5jGjAhkI464TTg
zEAAn3mG2H9VjACQHRN8lxQ70itB4FcD
=tl3B
-END PGP SIGNATURE-

[sqlite] Third test of json and index expressions, now it works

[Scott Robison]

On Sun, Sep 6, 2015 at 4:36 AM, E.Pasma  wrote:

>
> Op 6 sep 2015, om 03:17 heeft Scott Robison het volgende geschreven:
>
>>
>> select * from (select a as b from c) where b is something
>>
>> Still column expressions are called mutiple times.
>
> SQLite 3.8.11 2015-07-27 13:49:41 b8e92227a469de677a66da62e4361f099c0b79d0
> create table c(a)
> ;
> explain query plan
> select 1 from (select (select a from c) as b) where b>0
> ;
> 1|0|0|EXECUTE SCALAR SUBQUERY 2
> 2|0|0|SCAN TABLE c
> 1|0|0|EXECUTE SCALAR SUBQUERY 3
> 3|0|0|SCAN TABLE c
> 0|0|0|SCAN SUBQUERY 1



One, the argument wasn't whether or not column expressions were called
multiple times. My understanding of the debate was that using aliases in
place of their definitions made code more understandable and more easily
maintained than gratuitous repetition of the definitions.

Two, you used a different query. Looking at my query again (fleshed out
since I'm on a computer vs my phone):

sqlite> create table c(a);
sqlite> insert into c values(1);
sqlite> insert into c values(2);
sqlite> insert into c values(3);
sqlite> insert into c values(4);
sqlite> select * from (select a as b from c) where b > 2;
3
4
sqlite> explain query plan select * from (select a as b from c) where b > 2;
0|0|0|SCAN TABLE c

sqlite> select * from (select abs(a-a-a) as b from c) where b > 2;
3
4
sqlite> explain query plan select * from (select abs(a-a-a) as b from c)
where b > 2;
0|0|0|SCAN TABLE c

Clearly, this gives you an opportunity to replace definitions with aliases,
since the aliases in the inner query become the column names of the outer
query.

Your example doesn't do the same thing at all (ignoring the difference
between your condition of > 0 and mine of > 2). Your query is made of
scalar subqueries and you will never get more than a single row back.

The following two queries are not the same:

select 1 from (select (select a from c) as b) where b > 0;

select 1 from (select a as b from c) where b > 0;

In any case, this is a perfectly good option to query building that don't
require non-standard behavior (as far as I can tell) while still giving the
benefit of DRY (don't repeat yourself) as someone noted previously. It's
more verbose. It may not be as intuitive. But it works.

I am not a SQL guru. If I am wrong about my alternative invoking only
standard behavior, my apologies.


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



-- 
Scott Robison

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Dan Kennedy]

On 09/06/2015 09:23 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 09/06/2015 06:16 AM, Markus Weiland wrote:
>> I've discovered a potential bug in handling of SQLite database
>> files on gvfs mounted network shares.
> SQLite doesn't support being stored on the network for several
> reasons, including that network file protocols don't implement fully
> and correctly various access and locking, and that some journaling
> modes like WAL require shared memory and hence can't be networked.
> This is covered in the FAQ:
>
>https://www.sqlite.org/faq.html#q5
>
> Or if you want to be pedantic, it is gvfs and networked filesystems
> that have the bug.

The other problem is that it's 2.8.17. I think the most recent bugfix on 
the 2.* line was in 2007. 2.8.17 was the last release, and that was in 
December 2005.

Dan.

[sqlite] BEGINNER - Transactions in shell script

[Luuk]

On 06-09-15 15:26, Yuriy M. Kaminskiy wrote:
> Luuk  writes:
>
>> DELETE FROM test;
>> BEGIN;
>> INSERT INTO test VALUES(1,'test1');
>> INSERT INTO test VALUES(3,'test3',3);
>> INSERT INTO test VALUES(2,'test2');
>> COMMIT;
>> SELECT * FROM test;
> (and normally I'd expect to see DELETE *inside* transaction);

to quoot Simon:

"But SQL is kind and opens a one-command transaction if you forget."

;)

This was intentionally before the BEGIN, because i 'wanted' it to be in 
it's own transaction.

[sqlite] sqlite-users Digest, Vol 93, Issue 5

[sherry.um...@gmail.com]

Hi,

I have a general data modeling question:

I have the data in this format

Description(1..n)
  Name
  Desc
  Records (0..n)
Number
Type
Values(1..n)

Now its higly imposible to change this format because of legacy reason and
we use some proprietary single file database.

Now we want to use new db for example sqlite but not sure how to model this
in sql mainly becaues "Records" have "Values" this is also an array of some
values depending on the Type (Int,String,Double).

And "Description" can be sometimes of size 100Mb

Could someone suggest what would be a good way to model this as SQL tables?

Thank

-Sherry

On 5 September 2015 at 19:00, 
wrote:

> Send sqlite-users mailing list submissions to
> sqlite-users at mailinglists.sqlite.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> or, via email, send a message with subject or body 'help' to
> sqlite-users-request at mailinglists.sqlite.org
>
> You can reach the person managing the list at
> sqlite-users-owner at mailinglists.sqlite.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of sqlite-users digest..."
>
>
> Today's Topics:
>
>1. Re: sqlite-users Digest, Vol 93, Issue 4 (Peter Haworth)
>2. Re: sqlite-users Digest, Vol 93, Issue 4 (R.Smith)
>3. Re: Stable way to SELECT from SQLite db using WAL on  network
>   share? (Richard Hipp)
>4. Re: Changes to create index on trunk is breaking old code
>   (Domingo Alvarez Duarte)
>5. Re: Using |DataDirectory| in connection string (.NET with
>   System.Data.SQLite) (Joe Mistachkin)
>6. First test of json and index expressions, not so good
>   (Domingo Alvarez Duarte)
>7. Re: First test of json and index expressions, not so good
>   (Domingo Alvarez Duarte)
>8. Re: First test of json and index expressions, not so good
>   (Domingo Alvarez Duarte)
>9. Second test of json and index expressions, not so good
>   (Domingo Alvarez Duarte)
>   10. Re: Using |DataDirectory| in connection string (.NET  with
>   System.Data.SQLite) (Lee Gray)
>   11. Third test of json and index expressions, now it works
>   (Domingo Alvarez Duarte)
>   12. Re: Third test of json and index expressions, now it works
>   (Richard Hipp)
>   13. Re: Third test of json and index expressions, now it works
>   (Simon Slavin)
>   14. Re: Third test of json and index expressions, now it works
>   (Domingo Alvarez Duarte)
>   15. Re: Third test of json and index expressions, now it works
>   (Simon Slavin)
>   16. Re: Third test of json and index expressions, now it works
>   (Domingo Alvarez Duarte)
>   17. Re: Third test of json and index expressions, now it works
>   (Domingo Alvarez Duarte)
>   18. Re: Third test of json and index expressions, now it works
>   (Domingo Alvarez Duarte)
>   19. Re: Third test of json and index expressions, now it works
>   (Domingo Alvarez Duarte)
>   20. Re: First test of json and index expressions, not so good
>   (Luiz Am?rico)
>   21. UNQL as an extension to sqlite3 and now could use index
>   expressions (Domingo Alvarez Duarte)
>   22. Re: First test of json and index expressions, not so good
>   (Domingo Alvarez Duarte)
>   23. Re: UNQL as an extension to sqlite3 and now could use index
>   expressions (Domingo Alvarez Duarte)
>   24. Re: First test of json and index expressions, not so good
>   (Richard Hipp)
>   25. Re: Third test of json and index expressions, now it works
>   (Darko Volaric)
>   26. Re: Third test of json and index expressions, now it works
>   (Domingo Alvarez Duarte)
>   27. Re: Third test of json and index expressions, now it works
>   (Simon Slavin)
>   28. Re: Third test of json and index expressions, now it works
>   (Keith Medcalf)
>
>
> --
>
> Message: 1
> Date: Fri, 04 Sep 2015 16:09:20 +
> From: Peter Haworth 
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] sqlite-users Digest, Vol 93, Issue 4
> Message-ID:
> <
> CAGDT7eNA-yrNzi4q_LxyGOLhEsMRbYzRNcs2fhBE0Zi8UfWEwQ at mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> Thanks for the info.
>
> Could I get round the single value limitation with something like:
>
> IN ((SELECT 'abc','def' FROM myTable))
>
> Also, in the LIKE example, looks like I don't need single quote delimiters
> around the pattern?
>
>
> >
> > Message: 4
> > Date: Thu, 3 Sep 2015 12:54:46 -0400
> > From: Richard Hipp 
> > To: General Discussion of SQLite Database
> > 
> > Subject: Re: [sqlite] Variables in statements
> > Message-ID:
> >  > Mwy6Z07bWxUNACk6uAv2d6NmM8s4ETGcNbULw3jXZHH3g at mail.gmail.com>
> > Content-Type: text/plain; charset=UTF-8
> >
> > On 9/3/15, Peter Haworth  wrote:
> > >
> > > SELECT * FROM m

[sqlite] Third test of json and index expressions, now it works

[E.Pasma]

Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven:
>
> One, the argument wasn't whether or not column expressions were called
> multiple times. My understanding of the debate was that using  
> aliases in
> place of their definitions made code more understandable and more  
> easily
> maintained than gratuitous repetition of the definitions.
>
> Two, you used a different query. Looking at my query again (fleshed  
> out
> since I'm on a computer vs my phone):
>
> sqlite> create table c(a);
> sqlite> insert into c values(1);
> sqlite> insert into c values(2);
> sqlite> insert into c values(3);
> sqlite> insert into c values(4);
> sqlite> select * from (select a as b from c) where b > 2;
> 3
> 4
> sqlite> explain query plan select * from (select a as b from c)  
> where b > 2;
> 0|0|0|SCAN TABLE c
>
> sqlite> select * from (select abs(a-a-a) as b from c) where b > 2;
> 3
> 4
> sqlite> explain query plan select * from (select abs(a-a-a) as b  
> from c)
> where b > 2;
> 0|0|0|SCAN TABLE c
>
> Clearly, this gives you an opportunity to replace definitions with  
> aliases,
> since the aliases in the inner query become the column names of the  
> outer
> query.
>
> Your example doesn't do the same thing at all (ignoring the difference
> between your condition of > 0 and mine of > 2). Your query is made of
> scalar subqueries and you will never get more than a single row back.
>
> The following two queries are not the same:
>
> select 1 from (select (select a from c) as b) where b > 0;
>
> select 1 from (select a as b from c) where b > 0;
>
> In any case, this is a perfectly good option to query building that  
> don't
> require non-standard behavior (as far as I can tell) while still  
> giving the
> benefit of DRY (don't repeat yourself) as someone noted previously.  
> It's
> more verbose. It may not be as intuitive. But it works.
>
> I am not a SQL guru. If I am wrong about my alternative invoking only
> standard behavior, my apologies.
>

Yes. the debate is about column aliases.
Still the OP also wished to not call column expressions multiple times.
And I had to change your query to make that appear in the query plan.
But here is an example very close to yours where b is an alias for  
random().
I changed thea original where clause (b>2) to b <> b abd I hope it is  
obvious
that the function is caled multiple times for each row.

create table c(a);
insert into c values(1);
insert into c values(2);
insert into c values(3);
insert into c values(4);
select * from (select random() as b from c) where b <> b;
232218896271007264
-1043354911054439855
5116834959932449572
7115658816317887453

By the way I really like your coding style and it had no impact on  
speed thanks
to "subquery flattening" by the optimizer. 

[sqlite] sqlite-users Digest, Vol 93, Issue 5

[Simon Slavin]

On 6 Sep 2015, at 9:30am, sherry.ummen at gmail.com wrote:

> Description(1..n)
>  Name
>  Desc
>  Records (0..n)
>Number
>Type
>Values(1..n)
> 
> Now its higly imposible to change this format because of legacy reason and
> we use some proprietary single file database.
> 
> Now we want to use new db for example sqlite but not sure how to model this
> in sql mainly becaues "Records" have "Values" this is also an array of some
> values depending on the Type (Int,String,Double).
> 
> And "Description" can be sometimes of size 100Mb
> 
> Could someone suggest what would be a good way to model this as SQL tables?

Three tables: Descriptions, Records, Values with relations between them.  Check 
out FOREIGN KEYs:



I recommend that your key values (the ones which related one table to another) 
are rowids, not Name or record number.  Like 'artistid' and 'trackid' in the 
examples on the above page.

Don't worry about 100Mb in a field, or many of them in a table or database.  
SQLite has no problem with that.

Simon.

[sqlite] BEGINNER - Transactions in shell script

[Simon Slavin]

On 6 Sep 2015, at 2:26pm, Yuriy M. Kaminskiy  wrote:

> Not quite. Even if some statement failed, if you COMMIT in the end, it
> will succeed and database will be (partially) modified.
> 
> It's application developer responsibility to check for errors and issue
> ROLLBACK instead of COMMIT, if desirable (BTW, in case of some
> errors^[], sqlite3 may cancel [and automatically ROLLBACK] transaction,
> it is application developer responsibility to check if transaction is
> still active after errors [with sqlite3_get_autocommit()]).

This is a very important part of SQLite and I keep forgetting it.  It has 
always seemed weird to me.  Thank you to Yuriy for correcting me.

Simon.

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Markus Weiland]

I see. Since this was working under Ubuntu 14.04, I assume this is a 
regression with gvfs. I'll check over there.


On 2015-09-06 06:00 PM, sqlite-users-request at mailinglists.sqlite.org wrote:
> On 09/06/2015 06:16 AM, Markus Weiland wrote:
>> >I've discovered a potential bug in handling of SQLite database
>> >files on gvfs mounted network shares.
> SQLite doesn't support being stored on the network for several
> reasons, including that network file protocols don't implement fully
> and correctly various access and locking, and that some journaling
> modes like WAL require shared memory and hence can't be networked.
> This is covered in the FAQ:
>
>https://www.sqlite.org/faq.html#q5
>
> Or if you want to be pedantic, it is gvfs and networked filesystems
> that have the bug.
>
> Roger

[sqlite] MemoryBarrier compile error on WIndows MinGW gcc 4.8.1

[Keith Medcalf]

On Sunday, 6 September, 2015 04:32, Richard Hipp  said:

> On 9/5/15, Keith Medcalf  wrote:

> > Trunk does not compile with MinGW [gcc version 4.8.1 (GCC)] on Windows.

> Please retry after the latest check-in and let us know whether or not
> the problem has been resolved.

Confirmed that the compilation is now successful, both with _WIN32_WINTNT 0x501 
and 0x600

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Florian Weimer]

* Roger Binns:

> On 09/06/2015 06:16 AM, Markus Weiland wrote:
>> I've discovered a potential bug in handling of SQLite database
>> files on gvfs mounted network shares.
>
> SQLite doesn't support being stored on the network for several
> reasons, including that network file protocols don't implement fully
> and correctly various access and locking, and that some journaling
> modes like WAL require shared memory and hence can't be networked.
> This is covered in the FAQ:
>
>   https://www.sqlite.org/faq.html#q5
>
> Or if you want to be pedantic, it is gvfs and networked filesystems
> that have the bug.

Surely that's not true, and NFS and SMB are fine as long as there is
no concurrent access?

[sqlite] BEGINNER - Transactions in shell script

[Petr Lázňovský]

Thanks to all answers. Are principles of such script OK, or I miss something?


set "_error="
sqlite3.exe %db% BEGIN;
for /f "tokens=2,3 delims=," %%a in (data.csv) do (
sqlite3 %db% "INSERT INTO payments(id,amount) VALUES 
('%%a','%%b')";
if errorlevel 1 set "_error=y"
)
if not defined _error (
sqlite3.exe %db% COMMIT;
) else (
sqlite3.exe %db% ROLLBACK;
call stop_perform_another_scheduled_writings.cmd
call notify_admin_payment_writing_failed.cmd
)


L.

[sqlite] BEGINNER - Transactions in shell script

[Simon Slavin]

On 6 Sep 2015, at 7:46pm, Petr L?z?ovsk?  wrote:

> Thanks to all answers. Are principles of such script OK, or I miss something?

Sorry you cannot script sqlite.exe like that.  Each time you run it is a 
separate session.  your BEGIN has no useful effect.

Simon.

[sqlite] BEGINNER - Transactions in shell script

[Luuk]

On 06-09-15 20:46, Petr L?z?ovsk? wrote:
> Thanks to all answers. Are principles of such script OK, or I miss something?
>
>
> set "_error="
> sqlite3.exe %db% BEGIN;
> for /f "tokens=2,3 delims=," %%a in (data.csv) do (
>   sqlite3 %db% "INSERT INTO payments(id,amount) VALUES 
> ('%%a','%%b')";
>   if errorlevel 1 set "_error=y"
>   )
> if not defined _error (
>   sqlite3.exe %db% COMMIT;
>   ) else (
>   sqlite3.exe %db% ROLLBACK;
>   call stop_perform_another_scheduled_writings.cmd
>   call notify_admin_payment_writing_failed.cmd
>   )
>
>
> L.
>
>
>

no, because this script:

sqlite3.exe %db% BEGIN;
sqlite3.exe %db% COMMIT;

will end with: "Error: cannot commit - no transaction is active"
Every time sqlite3.exe is started a new transaction will begin (and END wehn 
sqlite3.exe stops)


while:
echo BEGIN; >file
echo COMMIT; >>file
cat file | sqlite3.exe %db%

works, creating 1 transaction.

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/06/2015 10:20 AM, Markus Weiland wrote:
> I see. Since this was working under Ubuntu 14.04, I assume this is
> a regression with gvfs. I'll check over there.

Nope.  SQLite can not maintain data integrity when used with *any*
network filesystem.  Sometimes it is able to detect problems, and give
an error message.  And other times you will end up with database
corruption.  Then you'll post to this list, and we'll tell you not to
use network filesystems.  The only variable is how long it takes
before you eventually find out about the corruption.

Sometimes people also come up with various schemes that appear to
work, but they won't always. Even something as simple as opening the
database for reading only can be very complex - such as if the
database wasn't cleanly committed (eg program crashed) - because
journals have to be examined and played back/undone as appropriate.
That involves two or more files, various forms of locking etc - just
the things that are problems with networked filesystems.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iEYEARECAAYFAlXsncIACgkQmOOfHg372QTweACeORy+Jpo6V4LDY3NXvU0iZ7G4
nW4AnA1ugTztFbRyQOHzhMJsQ8b54F7T
=uuQw
-END PGP SIGNATURE-

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/06/2015 11:13 AM, Florian Weimer wrote:
> Surely that's not true, and NFS and SMB are fine as long as there
> is no concurrent access?

And no program crashes, no network glitches, no optimisation in the
protocols to deal with latency, nothing else futzing with the files,
no programs futzing with them (backup agents, virus scanners etc), the
protocols are 100% complete compared to local file access, the
implementation of client and server for the protocol is 100% complete
and bug free, the operating systems don't treat network filesystems
sufficiently different to cause problems, you aren't using WAL, and
the list goes on.

In other words it can superficially appear to work.  But one day
you'll eventually notice corruption, post to this list, and be told
not to use network filesystems.  The only variable is how long it
takes before you make that post.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iEYEARECAAYFAlXsnwUACgkQmOOfHg372QQczQCffHo7JLJtQw4EcJQAVemsTPEN
/CUAoKpoIz3RudoRWM5qc2ac98dTVa18
=3Yx3
-END PGP SIGNATURE-

[sqlite] Third test of json and index expressions, now it works

[Scott Robison]

On Sun, Sep 6, 2015 at 10:54 AM, E.Pasma  wrote:

>
> Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven:
>
>>
>> One, the argument wasn't whether or not column expressions were called
>> multiple times. My understanding of the debate was that using aliases in
>> place of their definitions made code more understandable and more easily
>> maintained than gratuitous repetition of the definitions.
>>
>> Two, you used a different query. Looking at my query again (fleshed out
>> since I'm on a computer vs my phone):
>>
>> sqlite> create table c(a);
>> sqlite> insert into c values(1);
>> sqlite> insert into c values(2);
>> sqlite> insert into c values(3);
>> sqlite> insert into c values(4);
>> sqlite> select * from (select a as b from c) where b > 2;
>> 3
>> 4
>> sqlite> explain query plan select * from (select a as b from c) where b >
>> 2;
>> 0|0|0|SCAN TABLE c
>>
>> sqlite> select * from (select abs(a-a-a) as b from c) where b > 2;
>> 3
>> 4
>> sqlite> explain query plan select * from (select abs(a-a-a) as b from c)
>> where b > 2;
>> 0|0|0|SCAN TABLE c
>>
>> Clearly, this gives you an opportunity to replace definitions with
>> aliases,
>> since the aliases in the inner query become the column names of the outer
>> query.
>>
>> Your example doesn't do the same thing at all (ignoring the difference
>> between your condition of > 0 and mine of > 2). Your query is made of
>> scalar subqueries and you will never get more than a single row back.
>>
>> The following two queries are not the same:
>>
>> select 1 from (select (select a from c) as b) where b > 0;
>>
>> select 1 from (select a as b from c) where b > 0;
>>
>> In any case, this is a perfectly good option to query building that don't
>> require non-standard behavior (as far as I can tell) while still giving
>> the
>> benefit of DRY (don't repeat yourself) as someone noted previously. It's
>> more verbose. It may not be as intuitive. But it works.
>>
>> I am not a SQL guru. If I am wrong about my alternative invoking only
>> standard behavior, my apologies.
>>
>>
> Yes. the debate is about column aliases.
> Still the OP also wished to not call column expressions multiple times.
> And I had to change your query to make that appear in the query plan.
> But here is an example very close to yours where b is an alias for
> random().
> I changed thea original where clause (b>2) to b <> b abd I hope it is
> obvious
> that the function is caled multiple times for each row.
>
> create table c(a);
> insert into c values(1);
> insert into c values(2);
> insert into c values(3);
> insert into c values(4);
> select * from (select random() as b from c) where b <> b;
> 232218896271007264
> -1043354911054439855
> 5116834959932449572
> 7115658816317887453
>

Interesting query. So it would seem then that a temp table would be
required for something like this to avoid calling random three times per
row. I would have expected *this* version to return no rows. I guess this
is why we test software. :)


-- 
Scott Robison

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Simon Slavin]

On 6 Sep 2015, at 9:16pm, Roger Binns  wrote:

> no programs futzing with them (backup agents, virus scanners etc)

Reminds me of my most annoying SQLite problem.  They were running a virus 
scanner which delayed temp file deletion and SQLite could not manage its 
journal files properly.  Took me a few hours to figure that out.

Simon.

[sqlite] Third test of json and index expressions, now it works

[pasma10]

op 06-09-2015 22:42 schreef Scott Robison op scott at casaderobison.com:

> On Sun, Sep 6, 2015 at 10:54 AM, E.Pasma  wrote:
> 
>> 
>> Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven:
>> 
>>> 
>>> One, the argument wasn't whether or not column expressions were called
>>> multiple times. My understanding of the debate was that using aliases in
>>> place of their definitions made code more understandable and more easily
>>> maintained than gratuitous repetition of the definitions.
>>> 
>>> Two, you used a different query. Looking at my query again (fleshed out
>>> since I'm on a computer vs my phone):
>>> 
>>> sqlite> create table c(a);
>>> sqlite> insert into c values(1);
>>> sqlite> insert into c values(2);
>>> sqlite> insert into c values(3);
>>> sqlite> insert into c values(4);
>>> sqlite> select * from (select a as b from c) where b > 2;
>>> 3
>>> 4
>>> sqlite> explain query plan select * from (select a as b from c) where b >
>>> 2;
>>> 0|0|0|SCAN TABLE c
>>> 
>>> sqlite> select * from (select abs(a-a-a) as b from c) where b > 2;
>>> 3
>>> 4
>>> sqlite> explain query plan select * from (select abs(a-a-a) as b from c)
>>> where b > 2;
>>> 0|0|0|SCAN TABLE c
>>> 
>>> Clearly, this gives you an opportunity to replace definitions with
>>> aliases,
>>> since the aliases in the inner query become the column names of the outer
>>> query.
>>> 
>>> Your example doesn't do the same thing at all (ignoring the difference
>>> between your condition of > 0 and mine of > 2). Your query is made of
>>> scalar subqueries and you will never get more than a single row back.
>>> 
>>> The following two queries are not the same:
>>> 
>>> select 1 from (select (select a from c) as b) where b > 0;
>>> 
>>> select 1 from (select a as b from c) where b > 0;
>>> 
>>> In any case, this is a perfectly good option to query building that don't
>>> require non-standard behavior (as far as I can tell) while still giving
>>> the
>>> benefit of DRY (don't repeat yourself) as someone noted previously. It's
>>> more verbose. It may not be as intuitive. But it works.
>>> 
>>> I am not a SQL guru. If I am wrong about my alternative invoking only
>>> standard behavior, my apologies.
>>> 
>>> 
>> Yes. the debate is about column aliases.
>> Still the OP also wished to not call column expressions multiple times.
>> And I had to change your query to make that appear in the query plan.
>> But here is an example very close to yours where b is an alias for
>> random().
>> I changed thea original where clause (b>2) to b <> b abd I hope it is
>> obvious
>> that the function is caled multiple times for each row.
>> 
>> create table c(a);
>> insert into c values(1);
>> insert into c values(2);
>> insert into c values(3);
>> insert into c values(4);
>> select * from (select random() as b from c) where b <> b;
>> 232218896271007264
>> -1043354911054439855
>> 5116834959932449572
>> 7115658816317887453
>> 
> 
> Interesting query. So it would seem then that a temp table would be
> required for something like this to avoid calling random three times per
> row. I would have expected *this* version to return no rows. I guess this
> is why we test software. :)
> 
A temp table is not required. Another feature of subqueries (apart from
sticking to standard use of aliases) is that you can make them behave as
temp tables. Just by adding some "hint" for the optimizer such as LIMIT
99 or -1.

select * from (select random() as b from c limit -1) where b <> b;

Much easier than a temp table but not guaranteed to ever remain to work. 

[sqlite] Third test of json and index expressions, now it works

[Scott Robison]

On Sun, Sep 6, 2015 at 3:47 PM, pasma10  wrote:

> op 06-09-2015 22:42 schreef Scott Robison op scott at casaderobison.com:
>
> > On Sun, Sep 6, 2015 at 10:54 AM, E.Pasma  wrote:
> >
> >>
> >> Op 6 sep 2015, om 16:44 heeft Scott Robison het volgende geschreven:
> >>
> >>>
> >>> One, the argument wasn't whether or not column expressions were called
> >>> multiple times. My understanding of the debate was that using aliases
> in
> >>> place of their definitions made code more understandable and more
> easily
> >>> maintained than gratuitous repetition of the definitions.
> >>>
> >>> Two, you used a different query. Looking at my query again (fleshed out
> >>> since I'm on a computer vs my phone):
> >>>
> >>> sqlite> create table c(a);
> >>> sqlite> insert into c values(1);
> >>> sqlite> insert into c values(2);
> >>> sqlite> insert into c values(3);
> >>> sqlite> insert into c values(4);
> >>> sqlite> select * from (select a as b from c) where b > 2;
> >>> 3
> >>> 4
> >>> sqlite> explain query plan select * from (select a as b from c) where
> b >
> >>> 2;
> >>> 0|0|0|SCAN TABLE c
> >>>
> >>> sqlite> select * from (select abs(a-a-a) as b from c) where b > 2;
> >>> 3
> >>> 4
> >>> sqlite> explain query plan select * from (select abs(a-a-a) as b from
> c)
> >>> where b > 2;
> >>> 0|0|0|SCAN TABLE c
> >>>
> >>> Clearly, this gives you an opportunity to replace definitions with
> >>> aliases,
> >>> since the aliases in the inner query become the column names of the
> outer
> >>> query.
> >>>
> >>> Your example doesn't do the same thing at all (ignoring the difference
> >>> between your condition of > 0 and mine of > 2). Your query is made of
> >>> scalar subqueries and you will never get more than a single row back.
> >>>
> >>> The following two queries are not the same:
> >>>
> >>> select 1 from (select (select a from c) as b) where b > 0;
> >>>
> >>> select 1 from (select a as b from c) where b > 0;
> >>>
> >>> In any case, this is a perfectly good option to query building that
> don't
> >>> require non-standard behavior (as far as I can tell) while still giving
> >>> the
> >>> benefit of DRY (don't repeat yourself) as someone noted previously.
> It's
> >>> more verbose. It may not be as intuitive. But it works.
> >>>
> >>> I am not a SQL guru. If I am wrong about my alternative invoking only
> >>> standard behavior, my apologies.
> >>>
> >>>
> >> Yes. the debate is about column aliases.
> >> Still the OP also wished to not call column expressions multiple times.
> >> And I had to change your query to make that appear in the query plan.
> >> But here is an example very close to yours where b is an alias for
> >> random().
> >> I changed thea original where clause (b>2) to b <> b abd I hope it is
> >> obvious
> >> that the function is caled multiple times for each row.
> >>
> >> create table c(a);
> >> insert into c values(1);
> >> insert into c values(2);
> >> insert into c values(3);
> >> insert into c values(4);
> >> select * from (select random() as b from c) where b <> b;
> >> 232218896271007264
> >> -1043354911054439855
> >> 5116834959932449572
> >> 7115658816317887453
> >>
> >
> > Interesting query. So it would seem then that a temp table would be
> > required for something like this to avoid calling random three times per
> > row. I would have expected *this* version to return no rows. I guess this
> > is why we test software. :)
> >
> A temp table is not required. Another feature of subqueries (apart from
> sticking to standard use of aliases) is that you can make them behave as
> temp tables. Just by adding some "hint" for the optimizer such as LIMIT
> 99 or -1.
>
> select * from (select random() as b from c limit -1) where b <> b;
>
> Much easier than a temp table but not guaranteed to ever remain to work.


Thanks for that pointer.


-- 
Scott Robison