Re: [sqlite] New word to replace "serverless"

[Roman Fleysher]

Dear Richard and SQLiters,

I would like to ask, why is it so important to indicate that SQLite, in 
reference to threads or client/server,  " does not work that way". I think this 
might help to find the words to describe it. Is it because some embedded 
systems do not support threads? Is it because some systems are inherently 
single-task and thus a separate server process can not run? It seems to me that 
server-free or daemon-free do not transmit this. Also adding "-free" or "less" 
indicates something that the object is not. More precisely would be to indicate 
what the object is. What is the word for that programming methodology that 
existed since the beginning when there were no threads and everything was 
single-task?

Roman

From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Tuesday, January 28, 2020 6:18 AM
To: SQLite mailing list 
Cc: Rowan Worth 
Subject: Re: [sqlite] New word to replace "serverless"

CAUTION: This email comes from an external source; the attachments and/or links 
may compromise our secure environment. Do not open or click on suspicious 
emails. Please click on the “Phish Alert” button on the top right of the 
Outlook dashboard to report any suspicious emails.

On 1/28/20, Howard Chu  wrote:
>
> Wait, really? AFAICS embedded means in-process, no IPC required to operate.
>

Things like MySQL-embedded and H2 run a "server" as a thread instead
of as a separate process.  Clients then use Inter-Thread Communication
rather than Inter-Process Communication to send their queries to, and
get their results from, the database thread.  So this is really the
same thing as a server using IPC except that the server runs in the
same address space as the client.  The point of using the term
"serverless" is to indicate that SQLite does not work that way.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einsteinmed.org%7C9d048c5952ba4b25c61f08d7a3e3dc39%7C9c01f0fd65e040c089a82dfd51e62025%7C0%7C0%7C637158071362442872sdata=mC%2F9%2Bc%2Bcn84%2Fvn66c8pTVksPDtzMGhRS5wOwU%2FrQe7w%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[Roman Fleysher]

Perhaps "server" is not the right emphasis? Maybe it is the client? Thus, 
"clientless"? This means that each SQlite session serves itself. 
Self-sufficient.

Roman

From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Monday, January 27, 2020 5:18 PM
To: General Discussion of SQLite Database 
Subject: [sqlite] New word to replace "serverless"

CAUTION: This email comes from an external source; the attachments and/or links 
may compromise our secure environment. Do not open or click on suspicious 
emails. Please click on the “Phish Alert” button on the top right of the 
Outlook dashboard to report any suspicious emails.

For many years I have described SQLite as being "serverless", as a way
to distinguish it from the more traditional client/server design of
RDBMSes.  "Serverless" seemed like the natural term to use, as it
seems to mean "without a server".

But more recently, "serverless" has become a popular buzz-word that
means "managed by my hosting provider rather than by me."  Many
readers have internalized this new marketing-driven meaning for
"serverless" and are hence confused when they see my claim that
"SQLite is serverless".

How can I fix this?  What alternative word can I use in place of
"serverless" to mean "without a server"?

Note that "in-process" and "embedded" are not adequate substitutes for
"serverless".  An RDBMS might be in-process or embedded but still be
running a server in a separate thread. In fact, that is how most
embedded RDBMSes other than SQLite work, if I am not much mistaken.

When I say "serverless" I mean that the application invokes a
function, that function performs some task on behalf of the
application, then the function returns, *and that is all*.  No threads
are left over, running in the background to do housekeeping.  The
function does send messages to some other thread or process.  The
function does not have an event loop.  The function does not have its
own stack. The function (with its subfunctions) does all the work
itself, using the callers stack, then returns control to the caller.

So what do I call this, if I can no longer use the word "serverless"
without confusing people?

"no-server"?
"sans-server"?
"stackless"?
"non-client/server"?


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einsteinmed.org%7Cacbff015160a4adadbf708d7a376e761%7C9c01f0fd65e040c089a82dfd51e62025%7C0%7C0%7C637157603390189642sdata=QLjXenXheo7mS3o3MEqmxWaD1aKv4oCUkkHJ4zwCYlM%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Test failures on GPFS

[Roman Fleysher]

I use SQLite over GPFS , but in DELETE (which I think is the default) mode. Not 
WAL mode. No issues with locking, except performance when accessing 
concurrently from multiple nodes. As others pointed out, this has to do with 
the overhead due to lock requests. GPFS must coordinate with many nodes. My 
observation is that when concurrent access is from a few nodes, the performance 
is OK even though number of nodes is always the same. Thus, GPFS coordinates in 
some smart way only between nodes actively involved.

One reason I do not use mySQL with its more efficient network access is that 
sys admin must set it up. With SQLite, I am independent. In addition, in my SQL 
there are authentication issues to be dealt with. I rely on GPFS file access 
permissions (access control list, ACL) to regulate access to database.

I heard about BeadrockDB, which internally uses SQLite and provides network 
access with replication. I have not tried it and do not know what is involved.


Roman



From: sqlite-users  on behalf of 
Richard Hipp 
Sent: Saturday, January 11, 2020 8:59 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Test failures on GPFS

CAUTION: This email comes from an external source; the attachments and/or links 
may compromise our secure environment. Do not open or click on suspicious 
emails. Please click on the “Phish Alert” button on the top right of the 
Outlook dashboard to report any suspicious emails.

On 1/11/20, J. King  wrote:
>
> WAL mode does not work over the network, so the test failures are presumably
> to be expected.
>

WAL mode should work on a network filesystem, as long as all of the
clients are on the same host computer, and as long as mmap()-ing the
*-shm file gives all the clients shared memory.  Dunno if GPFS does
that or not, though.  Maybe not.  Or, maybe not reliably.

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einsteinmed.org%7Cba1544a0f3584e8a077008d7970309d8%7C9c01f0fd65e040c089a82dfd51e62025%7C0%7C1%7C637143911624961155sdata=udLAzknx7zL4yHzQk8ZPQI8mAWltFusqvcb%2FW31XuaY%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?

[Roman Fleysher]

A side note about VACUUM:

If I remember correctly, tables which do not have INTEGER PRIMARY KEY will have 
their rowid column reassigned. Be careful if you are using rowid.

Roman


From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Thursday, December 5, 2019 2:03 PM
To: SQLite mailing list 
Subject: Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?

CAUTION: This email comes from an external source; the attachments and/or links 
may compromise our secure environment. Do not open or click on suspicious 
emails. Please click on the “Phish Alert” button on the top right of the 
Outlook dashboard to report any suspicious emails.

On 5 Dec 2019, at 6:31pm, Zhu, Liang [AUTOSOL/ASSY/US]  
wrote:

> If I do not do Vacuum,  my database size just keep raising,  eventually the 
> database size gets to over 90% of storage size,  I can save data to the 
> database any more.

VACUUM should not be saving you any space.  And VACUUM puts a lot of traffic 
through your storage device which will eventually kill it.

What journal mode are you using ?  In other words, what does the command
PRAGMA journal_mode
output ?

Does your application close the connection correctly ?  In other words, do you 
call sqlite3_close() or sqlite3_close_v2() and check the result code it returns 
?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einsteinmed.org%7C150f6e61d5e047dfe37e08d779b73fd1%7C9c01f0fd65e040c089a82dfd51e62025%7C0%7C0%7C637111700322791016sdata=x6sIZJFg33wns0NYU67N7cIyE%2FZsBC3N6Yp6P%2FuRFLo%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is empty but intact

[Roman Fleysher]

There are multiple programs working in parallel. 750 of them from several 
compute nodes.

I use command line sqlite3, so,  sqlite itself it not a suspect.

FLoM is like a file lock:

flom argumentsToFLoMIncludingTimeout  --  sqlite3 "sqlStatement;"

I am using linux, I think this analyzer is windows only.

There several things that can fail: FLoM mismanaged locks is my primary 
suspect. But I would think database would be corrupt, not empty.

Maybe I should ask a different question: Would knowing answer to my question 
(is it possible for DB to become empty?) help me single out FLoM as the cause 
of the problem?

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Simon Slavin [slav...@bigfraud.org]
Sent: Thursday, November 07, 2019 7:28 PM
To: SQLite mailing list
Subject: Re: [sqlite] database is empty but intact

On 8 Nov 2019, at 12:03am, Roman Fleysher  
wrote:

> I am using command line sqlite3 with -vfs unix-none. This disables locking 
> within SQLite. Instead, locking is provided externally by FLoM (distributed 
> file lock manager). I asked questions in a thread "disable file locking 
> mechanism over the network".

Is SQLite telling FLoM when it should place or remove a lock ?  If not, how 
does FLoM know ?

Do you have more than one process which might write to the database ?

Do all your processes which write to the database close their connections 
correctly ?  Do they check the result codes from their calls to see if SQlite 
successfully closed the database ?

> It is possible that FLoM has bugs and mismanaged locks. As a result, the 
> database is now empty. PRAGMA integrity check shows the database is intact. I 
> would expect it to be corrupt. The only DELETE operation in the queries was 
> to delete a single row:
>
> DELETE FROM jobs WHERE rowID = XXX;
>
> Could this DELETE actually delete entire content of a table if lock is 
> mismanaged?

It could, though it is unsual.  Have you run the analysis program on the file 
to find out more about it ?

<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Fsqlanalyze.htmldata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C885cd07bd2e8437eb7df08d763e29b88%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C637087697235291714sdata=Yq0C78yBQevXalcqKywwxOSV69EeRYLAG0755lCoMMY%3Dreserved=0>

It could allow you to distinguish between a table with no rows in, and a table 
with lots of deleted rows in.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C885cd07bd2e8437eb7df08d763e29b88%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C637087697235291714sdata=kCZumJpJuTsUpSw4wl2XncG7aATqbPFqDpaXXFc9ecs%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database is empty but intact

[Roman Fleysher]

Dear SQLiters,

I am using command line sqlite3 with -vfs unix-none. This disables locking 
within SQLite. Instead, locking is provided externally by FLoM (distributed 
file lock manager). I asked questions in a thread "disable file locking 
mechanism over the network".

It is possible that FLoM has bugs and mismanaged locks. As a result, the 
database is now empty. PRAGMA integrity check shows the database is intact. I 
would expect it to be corrupt. The only DELETE operation in the queries was to 
delete a single row:

DELETE FROM jobs WHERE rowID = XXX;

Could this DELETE actually delete entire content of a table if lock is 
mismanaged? I am trying to figure out of this is a disk related issue. Or some 
other issue.



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

Re: [sqlite] Network file system that support sqlite3 well

[Roman Fleysher]

I was raising/discussing similar question. Look through SQLite archive for :

disable file locking mechanism over the network

Client/server manager for SQLite is not enough. Internally, Sqlite will still 
request lock from the file system and the overhead will still be there. Once 
the client/server manager is working you have to disable SQLite's locking 
mechanism. Then, the overhead will be reduced but not gone. If do that, make 
sure you NEVER access the same database file bypassing your manager. This is a 
file, you might be tempted to access it using sqlite directly. 

The remaining overhead I can describe as this: If you create a file on one 
node, it takes time for this file to show up on another node. This delay 
depends on many things: number of nodes, network, load, configuration. etc. 
Critical here is that file system, in my case GPFS, will delay a command trying 
to access newly crated file on a different node until it shows up. You can 
think of it as locking on the folder level performed internally by the file 
system. So, even with client/server manager to regulate access to database, 
SQLIte will still be slower than you might expect. This now depends on the 
project requirements and throughput. 

The true solution is client / server  where server is the only process 
accessing database. Jobs from all the nodes ask the server to do the work.

mySQL and other exiting full featured DMS have one serious limitation: system 
administrator must install and maintain them. Sqlite is user level and simple 
thing. What is needed is what I called "mySQLite" a server based on SQLite. 
However, as soon as "server" and "network" enter the project, it becomes 
tremendously more complicated: threads, buffering, authentication, dropped 
connections... 

The problem is thus in distilling the basic feature set such mySQLite should 
have. This is what SQLite developers did for SQLite.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Graham Holden [sql...@aldurslair.com]
Sent: Wednesday, October 16, 2019 10:45 AM
To: SQLite mailing list
Subject: Re: [sqlite] Network file system that support sqlite3 well

Wednesday, October 16, 2019, 1:22:58 AM, Gary R. Schmidt  
wrote:

> On 16/10/2019 10:38, Jens Alfke wrote:
>>
>>> On Oct 15, 2019, at 3:47 PM, Peng Yu  wrote:
>>>
>>> I'd like to use sqlite3 db files on many compute nodes. But they
>>> should access the same storage device for the sqlite3 db files.
>>
>> Why not use an actual client-server database system like MySQL? It's
>> optimized for this use case, so it incurs a lot less disk (network) I/O.
>>
> To second what Jens has written - use the right tool for the job.

> SQLite is *not* the right tool for this sort of job.

> MySQL/MariaDB/PostGRESQL/Oracle/SQL Server/DB2/... are what you should
> be looking at.

Or, depending on the type and variety of the operations your "compute
nodes" need to do, write a pair of what could be relatively simple
client-server programs that police access to the SQLite DB (which the
server will be accessing as a local file).

For this to work (well, easily) you'd be looking to create "higher-
level" functions ("store this data set", "retieve this data set" etc.)
that the clients (on the compute nodes) can ask the server (next to
the SQLite file) to perform on their behalf.

Of course, if the need is for the compute nodes to have full access to
the SQLite API, this approach wouldn't be suitable -- you shouldn't
(IMHO) be trying to make the SQLite API itself work across the
network**.

Graham


** I believe someone has tried/succeeded in doing something like
   this, but I don't know the details off the top of my head.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C59fa1226b16042b1738108d752478d8c%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637068339589985989sdata=VltANrFLWIxmiNPNju3eYBqHDxy%2F%2BQIbfOgjARlnbkM%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Network file system that support sqlite3 well

[Roman Fleysher]

Transaction delays apply to read as well. SQLite places a lock while reading 
too, to ensure the database is intact during read. Otherwise tables will half 
half complete rows.

Read "begin transaction", difference between immediate and exclusive 
transactions.

Roman



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Peng Yu 
Date: 10/16/19 6:41 AM (GMT-05:00)
To: SQLite mailing list 
Subject: Re: [sqlite] Network file system that support sqlite3 well

> I know for sure that IBM's GPFS guarantees locking. I think GPFS is "global
> parallel file system". It is a distributed file system. But it will be
> rather slow. If only few jobs run in parallel,  all will be ok. Locking will
> always guarantee database integrity.
>
> With lots of jobs,  you will see you have to increase sqlite timeouts to
> hours. Waiting for a lock will be much longer than transaction,  obviously.
> If transaction takes 1 second,  with 1000 jobs,  you will need timeout of
> 1000 seconds. But locking adds very large overhead. Timeout will have to be
> 2 or 3 times that.
>
> If GPFS is loaded by other jobs (from other users,  not even sqlite users)
> the wait times will increase.

Although I have many compute nodes, there is most only one computer
node write to the DB file. There can be several processes to read the
same DB files from each compute node.

I guess the transaction delay only applies to write? Does it apply to read?

--
Regards,
Peng
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C1c9c57629ef043b5847d08d7522562fc%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637068192835821781sdata=bqetTp5ZAa4ZJEDJiv6MDIyWZ2IahgUHQ5gATgfg2u0%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Network file system that support sqlite3 well

[Roman Fleysher]

I know for sure that IBM's GPFS guarantees locking. I think GPFS is "global 
parallel file system". It is a distributed file system. But it will be rather 
slow. If only few jobs run in parallel,  all will be ok. Locking will always 
guarantee database integrity.

With lots of jobs,  you will see you have to increase sqlite timeouts to hours. 
Waiting for a lock will be much longer than transaction,  obviously. If 
transaction takes 1 second,  with 1000 jobs,  you will need timeout of 1000 
seconds. But locking adds very large overhead. Timeout will have to be 2 or 3 
times that.

If GPFS is loaded by other jobs (from other users,  not even sqlite users)  the 
wait times will increase.

Roman



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Simon Slavin 
Date: 10/16/19 12:51 AM (GMT-05:00)
To: SQLite mailing list 
Subject: Re: [sqlite] Network file system that support sqlite3 well

On 15 Oct 2019, at 11:47pm, Peng Yu  wrote:

> Is there a solution that are known to fill in this niche? Thanks.

Unfortunately, no.  Multiuser SQLite depends on locking being implemented 
correctly.  The developers haven't found any Network File Systems which do 
this.  Unless one of the readers of this list wants to tell me otherwise.

Jens' post suggesting that you use a proper client/server database system is my 
only solution.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C8e768b55426246b957cb08d751f481a2%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637067982900220847sdata=PXmUPthCG64C1kmJ1RmTySnsYV1GCaaz1LIoO7g3cQU%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite plus the works (was Re: Opposite of SQLite)

[Roman Fleysher]

It's ok that you don't believe in it, but the last statement, you know is not 
true.  I have been in this list since 2003 or so, and constantly there is one 
or two request to Dr. Hipp and the owners, per month, to add "stuff" to it.  
Or, things like, "I would love to have SQLite do...", etc.  First of all, there 
are already folks that have written pieces of code that they use themselves for 
their projects.  Imagine if these become available to the rest of the world, it 
would be a nice happy programming place. :-) But, I accept your preclusion of 
being the maintainer. :-)

Imagine a vehicle which has wheels to drive on a road. And wings to fly and a 
hull to float. How nice and happy world would be !? True? Of course true.

But, this vehicle would be bad as a car, bad as an airplane and bad a boat. 
Why? Because it is too hard to cram all the features and keep it small and 
light. But if you have to have one, you can.

The goal of SQLite is to be light. It is much easier to use and versatile 
thanks to its size.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite plus the works (was Re: Opposite of SQLite)

[Roman Fleysher]

With your brain excluded, who is "we"?

The beauty of SQLite is that SQL was distilled to the smallest and most 
reproducible on many platforms set. Over time, I see how developers expand 
functionality to make it more convenient while maintaining reproducibility 
across platforms. Time, date, math operations are extremely hard to make 
reproducible. SQLite is small in SQL already: It has LEFT JOIN and does not 
have RIGHT JOIN. Why? Because RIGHT can be made out of LEFT by swapping order 
of tables. That is how small it is. The new functionality should be more 
substantial than swapping the order of tables or adding extra SQL line of code 
for date manipulation.

More over, SQLIte already has extension mechanism and many libraries have been 
written:

https://www.sqlite.org/contrib/

The "we" can add to it date manipulation routines and many other.

SQLite is a beauty. Learn to see it!

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Jose Isaias Cabrera [jic...@outlook.com]
Sent: Thursday, October 10, 2019 4:03 PM
To: SQLite mailing list
Subject: [sqlite] SQLite plus the works (was Re:  Opposite of SQLite)

Simon Slavin, on Thursday, October 10, 2019 03:42 PM, wrote...
>
> On 10 Oct 2019, at 7:55pm, Ned Fleming, on
>
> > SQLessLite
>
> SQDietStartsMonday

All kidding aside, and naming continuing, :-), I believe the world will be a 
happier place with:
1. The normal SQLite snapshots (just like it's working right now)
2. The normal SQlite snapshots plus a series of libraries and functions that 
can easily be compiled with the original light SQLite.

I know this sounds simple, but, with all the brain in this group, excluding 
mine, I think we can manage to have that wonderful tool. ;-)  My thinking 
out-loud.  Thanks.

josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Cb2cee1314300447dc76908d74dbcdc26%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637063345849192991sdata=utw834l9YsRZp2ozCk7LQ2wfmedtnZB4aWNbP4nrYi0%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Roman Fleysher]

I had to deal with a similar conversion.

I think the answer to why date() does not take other formats is simple: SQLite 
is minimalistic. This string processing can be done outside SQLite library.

Minimalism of SQLite is one of the criteria for what gets implemented. It has 
few mathematical operations, few string manipulations, subset of SQL, etc. 
Sqlite developers are very good at choosing which few to implement to give 
maximum of versatility. Every time I notice such decision I get more impressed. 
I hope you will experience such joy as well. 

 


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Jose Isaias Cabrera [jic...@outlook.com]
Sent: Monday, October 07, 2019 9:17 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Date time input

Greetings.

I have to ask this question:  Why is it that the date function does not take a 
'4/5/2019' and returns '2019-04-05'?  This may have been asked before, and the 
answer may be some where in the internet, but, I could not find it.  The other 
thing is that it would be nice to have date take something like this,

date('2/15/2019','m/d/')

and return

2019-02-15

so I don't have to deal with the changes. :-)  Just wishing out-loud...  Thanks.

josé


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Cfa23548f95114997012508d74b28b25c%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637060510468196612sdata=kxiXOugWi%2FGEmGs%2FKi7FTyK4xpipJeCmK0EBv9qkbFc%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Copy-on-write VFS

[Roman Fleysher]

Maybe this for this scenario:

You have in-memory database, used mostly for reading and you want to save its 
copy to disk when update on in-memory is performed?
Otherwise, what is copied and what is to write in  "copy on write"?




From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Keith Medcalf [kmedc...@dessus.com]
Sent: Friday, October 04, 2019 12:19 PM
To: SQLite mailing list
Subject: Re: [sqlite] Copy-on-write VFS

On Friday, 4 October, 2019 05:18, Fredrik Larsen  wrote:

>A copy-on-write IO-path where data is split into static and dynamic parts
>(think snapshots for storage) would be very helpful for our project, .

What do you mean?  Useful how?

>This would simplify backups, testing, moving data around in a multinode
>environment, etc.

Since we still do not know what you are on about, then this is debatable.

>Does something like this exist for sqlite? In my head this sounds like an
>relative easy feature to add as IO-stuff is already centralized in the
>VFS layer. Maybe a new COW-VFS?

Still do not know exactly what you are on about.  Can you explain what you are 
on about?

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C9ef1d96d7e3840552f4208d748e6ad65%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637058027904564324sdata=8hS93ORYmBXeRHKt4bF4di3AOQswyKWSzvjIGPInDBY%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disable file locking mechanism over the network

[Roman Fleysher]

I like the idea of FLoM-based VFS !!

I am not sure I am up to actually doing it, but this should be as good a 
locking mechanism as any other.

Thank you, Keith.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Keith Medcalf [kmedc...@dessus.com]
Sent: Saturday, September 28, 2019 12:48 AM
To: SQLite mailing list
Subject: Re: [sqlite] disable file locking mechanism over the network

On Friday, 27 September, 2019 22:11, Roman Fleysher 
 wrote:

>Based on the link you provide, it looks like I need unix-none VFS and
>specify it as:

>ATTACH 'file:demo2.db?vfs=unix-none' AS demo2;

Yup.  I think you can also do that when opening the file on the sqlite3_open_v2 
call by using the same URI format.  I think using nolock=1 without specifying a 
VFS does the same thing ... ie, using a filename of 'file:demo2.db?nolock=1'

>FLoM will be ensuring sequential access. The journaling, as I understand,
>is independent of locking and will still work. That is robustness to
>crashes will remain.  unix-none just disables file lock requests, which
>are the cause of the overhead. Is this correct?

I haven't looked at the actual code for the VFS but that is my impression.  The 
description for using the nolock=1 URI parameter says that you must have the 
application "serialize writes" in order to maintain consistency and if you are 
using FLoM to synchronize application access to the sqlite3 api, I would think 
that would cover it.  The nolock=1 specifically says that it disables "file 
locking operations" only but maintains the change detection logic which should 
presumably maintain cache consistency (I think the purpose of the nolock=1 is 
simply to allow "generic" vfs selection, by appending -none to the default vfs 
name -- ie, so that if you are running on Windows it uses win32-none instead of 
win32, and on unix uses unix-none instead of unix).

As an aside, it might be interesting to derive another VFS from the standard 
"unix" VFS which just replaces the locking operations with synchronization 
using FLoM, so that nothing special at all is required at the application level 
other than selecting a different VFS layer.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C3e48038bb3254b2b636608d743cf2ce2%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637052429406868157sdata=iJc7whQWoukCo1QUL8yAL73j5wcG5e%2Bw8TFU2RkHY1U%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disable file locking mechanism over the network

[Roman Fleysher]

Based on the link you provide, it looks like I need unix-none VFS and specify 
it as:

ATTACH 'file:demo2.db?vfs=unix-none' AS demo2;

FLoM will be ensuring sequential access. The journaling, as I understand, is 
independent of locking and will still work. That is robustness to crashes will 
remain.  unix-none just disables file lock requests, which are the cause of the 
overhead. Is this correct? 

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Keith Medcalf [kmedc...@dessus.com]
Sent: Friday, September 27, 2019 11:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] disable file locking mechanism over the network

Yeah, WAL mode cannot work over a remote connection as the WAL index is a 
shared memory file -- which can only be accessed by processes on a single 
computer.

If you are using FLoM to co-ordinate your "application" use of SQLite3, then 
maybe you want to look at using the dotfile VFS which uses dotfiles to 
co-ordinate multi-access rather than file locks.

https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Fsearch%3Fs%3Dd%26q%3Ddotfiledata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C07b8076c03944d262b0808d743c6f9a6%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637052394192099402sdata=Jkew%2B%2FZJkF2GZbtfYYtHfbFsXJiv%2FMUHfwveVXYdvxI%3Dreserved=0

>-Original Message-
>From: sqlite-users  On
>Behalf Of Roman Fleysher
>Sent: Friday, 27 September, 2019 21:43
>To: SQLite mailing list 
>Subject: Re: [sqlite] disable file locking mechanism over the network
>
>The timeout is set to 50min !
>
>The database is used to synchronize / manage tasks. A kind of job
>manager. Thus each access is a quick search with small update or delete.
>
>I looked at the WALL mode. I do not exactly understand how it works, but
>SQLite developers warn not to use WALL over network in item 2 of the link
>you mention.
>
>I was not aware of the various synch option. I will study them.
>
>Thank you,
>
>Roman
>
>
>From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on
>behalf of Simon Slavin [slav...@bigfraud.org]
>Sent: Friday, September 27, 2019 7:53 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] disable file locking mechanism over the network
>
>On 27 Sep 2019, at 11:59pm, Roman Fleysher
> wrote:
>
>> From experience, it seems that because SQLite still requests file
>locks, the performance increase is not that big. I wonder if there is a
>way to disable SQLite's internal file locking mechanism. I know this
>seems strange to ask. But FLoM should be able to do it faster over many
>compute nodes.
>
>What busy_timeout did you set ?
>
>Do you process multiple operations inside a transaction, or do you let
>SQLite create its own transactions automatically ?
>
>SQLite always locks the entire database.  It does not implement table or
>row locking.
>
>SQLite has two (main) journalling modes.  The journalling made influences
>which operations lock out other operations.  The biggest change in
>lockouts occurs whether you do or do not have writes from multiple
>connections at once.  You might want to try executing this command just
>once:
>
>PRAGMA journal_mode = WAL
>
>This makes a change to the database so that all connections which open it
>know it's in WAL mode.  You don't have to change your software.  It might
>speed things up.  (You can do
>
>PRAGMA journal_mode = DELETE
>
>to set it back.)
>
>There are other other things you can do to disable various parts of the
>locking system, but you should try the above first.  You might want to
>play around with
>
><https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite
>.org%2Fpragma.html%23pragma_synchronousdata=02%7C01%7Croman.fleysher
>%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079
>934e02e89266ad0%7C1%7C0%7C637052252378056899sdata=a%2FTb%2Fr7kdca1Q4
>%2F1Le1azbaXR0jdmkDKm042RRMgNYM%3Dreserved=0>
>
>Refs:
>
><https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite
>.org%2Fpragma.html%23pragma_journal_modedata=02%7C01%7Croman.fleyshe
>r%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f264807
>9934e02e89266ad0%7C1%7C0%7C637052252378056899sdata=MuvGgmedEKY7QBfx3
>03DUcH4iWkpnRL1D3ADV51LeEg%3Dreserved=0>
><https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite
>.org%2Fwal.htmldata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26f
>5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%
>7C637052252378056899sdata=svGLhgL5mQkQfQ%2B7UwsKzaJ4krn8%2F%2FgnHTm3
>LG3UEPA%3Dreserved=0>
>_

Re: [sqlite] disable file locking mechanism over the network

[Roman Fleysher]

I agree that overhead might grow exponentially with number of nodes trying to 
access the database. But then I do not understand why FLoM can not provide 
solution. It can enforce sequential access thus reducing the overhead.

And yes, I was trying the KISS approach: rely on GPFS to do the synchronization 
instead of me programming client/server. 

Thank you,

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Keith Medcalf [kmedc...@dessus.com]
Sent: Friday, September 27, 2019 11:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] disable file locking mechanism over the network

On Friday, 27 September, 2019 17:00, Roman Fleysher wrote:

>I am using SQLite over GPFS distributed file system. I was told it
>honestly implements file locking. I never experienced corruption. But it
>is slow in the sense that when many jobs from many compute nodes try to
>access the same database things slow down considerably.

Yes.  This is how "correct" locking would be implemented.  If would be so slow 
as to be virtually unusable.

>I suspect, from the point of view of file system, there is lots of
>pressure to develop fast grabbing of a lock and slow release. I think
>this is because the key to fast network file system in general is making
>it as independent as possible, thus distributed.

No, it is because the filesystem server must be queried on every lock 
operation, and before that can return to the caller, each and every client must 
be contacted and "made consistent".  This means that as more clients "open" the 
same file, the overhead of each operation becomes orders of magnitude greater.  
In order to "get around" this issue most network filesystems use so-called 
"optimistic" locking protocols.  This basically means that the software prays 
and goes ahead anyway without ensuring consistency amongst clients and simply 
corrupts the file in cases where the prayer does not work.  For the normal case 
this usually workthe link you sent in item 2,s adequately.  For in-place record 
updates, however, it usually does not.

>To try to speed up locking I combined SQLite with FLoM, distributed file
>lock manager. It is client/server application.the link you sent in item 2,

I don't think FLoM does what you think it does.

>From experience, it seems that because SQLite still requests file locks,
>the performance increase is not that big. I wonder if there is a way to
>disable SQLite's internal file locking mechanism. I know this seems
>strange to ask. But FLoM should be able to do it faster over many compute
>nodes.

>Or, perhaps the right way is for me to combine SQLIte with simple
>client/server code to create light mySQL, mySQLite?

Yes, this is the correct way to do it.  If multi-access to a single shared file 
worked properly then there would never have been any need to invent 
client/server database architecture.  The fact that is was invented at the same 
time as shared filesystems indicates that the KISS solution (a shared 
filesystem) was not robust.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ca315faff6fdc45dfc70408d743c1a8b0%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637052371361975178sdata=dhrR%2FbKGqPEmk6sLv3JGouW%2BdO%2BaAK2WyP97LLYQlCU%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disable file locking mechanism over the network

[Roman Fleysher]

The timeout is set to 50min !

The database is used to synchronize / manage tasks. A kind of job manager. Thus 
each access is a quick search with small update or delete.

I looked at the WALL mode. I do not exactly understand how it works, but SQLite 
developers warn not to use WALL over network in item 2 of the link you mention.

I was not aware of the various synch option. I will study them.

Thank you,

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Simon Slavin [slav...@bigfraud.org]
Sent: Friday, September 27, 2019 7:53 PM
To: SQLite mailing list
Subject: Re: [sqlite] disable file locking mechanism over the network

On 27 Sep 2019, at 11:59pm, Roman Fleysher  
wrote:

> From experience, it seems that because SQLite still requests file locks, the 
> performance increase is not that big. I wonder if there is a way to disable 
> SQLite's internal file locking mechanism. I know this seems strange to ask. 
> But FLoM should be able to do it faster over many compute nodes.

What busy_timeout did you set ?

Do you process multiple operations inside a transaction, or do you let SQLite 
create its own transactions automatically ?

SQLite always locks the entire database.  It does not implement table or row 
locking.

SQLite has two (main) journalling modes.  The journalling made influences which 
operations lock out other operations.  The biggest change in lockouts occurs 
whether you do or do not have writes from multiple connections at once.  You 
might want to try executing this command just once:

PRAGMA journal_mode = WAL

This makes a change to the database so that all connections which open it know 
it's in WAL mode.  You don't have to change your software.  It might speed 
things up.  (You can do

PRAGMA journal_mode = DELETE

to set it back.)

There are other other things you can do to disable various parts of the locking 
system, but you should try the above first.  You might want to play around with

<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Fpragma.html%23pragma_synchronousdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C637052252378056899sdata=a%2FTb%2Fr7kdca1Q4%2F1Le1azbaXR0jdmkDKm042RRMgNYM%3Dreserved=0>

Refs:

<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Fpragma.html%23pragma_journal_modedata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C637052252378056899sdata=MuvGgmedEKY7QBfx303DUcH4iWkpnRL1D3ADV51LeEg%3Dreserved=0>
<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Fwal.htmldata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C637052252378056899sdata=svGLhgL5mQkQfQ%2B7UwsKzaJ4krn8%2F%2FgnHTm3LG3UEPA%3Dreserved=0>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C637052252378056899sdata=MV%2FomDHw0jFta70BSSxaKIzUP3mSqhdx%2BH3WY64UCZo%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] disable file locking mechanism over the network

[Roman Fleysher]

( somewhat related to Re: [sqlite] Safe to use SQLite over a sketchy network?)

Dear SQLiters,

I am using SQLite over GPFS distributed file system. I was told it honestly 
implements file locking. I never experienced corruption. But it is slow in the 
sense that when many jobs from many compute nodes try to access the same 
database things slow down considerably.

I suspect, from the point of view of file system, there is lots of pressure to 
develop fast grabbing of a lock and slow release. I think this is because the 
key to fast network file system in general is making it as independent as 
possible, thus distributed. Avoid bottlenecks. But locking is by definition a 
bottleneck. On purpose. I think code requiring file locking is a sign of the 
code not intended for concurrent access from multiple compute nodes.

SQLite uses file locking to ensure data integrity. This is fine for imbedded 
systems.

We use SQLite over 100 compute nodes, not as intended.

To try to speed up locking I combined SQLite with FLoM, distributed file lock 
manager. It is client/server application.

From experience, it seems that because SQLite still requests file locks, the 
performance increase is not that big. I wonder if there is a way to disable 
SQLite's internal file locking mechanism. I know this seems strange to ask. But 
FLoM should be able to do it faster over many compute nodes.

Or, perhaps the right way is for me to combine SQLIte with simple client/server 
code to create light mySQL, mySQLite?

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Simon Slavin [slav...@bigfraud.org]
Sent: Wednesday, September 25, 2019 12:58 AM
To: SQLite mailing list
Subject: Re: [sqlite] Safe to use SQLite over a sketchy network?

When I first learned the SQLite had problems with Network File Systems I read a 
ton of stuff to learn why there doesn't seem to be a Network File Systems that 
implements locking properly. I ended up with …

A) It slows access a lot. Even with clever hashing to check for collisions it 
takes time to figure out whether your range is already locked.

B) Different use-cases have different preferences for retry-and-timeout times. 
It's one more thing for admins to configure and many admins don't get it right.

C) It's hard to debug. There are numerous different orders in which different 
clients can lock and unlock ranges. You have to run a random simulator to try 
them all. The logic to deal with them properly is not as simple as you'd think. 
Consider, for example, ranges which are not identical but do overlap.

D) It's mostly a waste of time. Most client software doesn't care how to deal 
with a BUSY status and either crashes – which annoys the admin and user – or 
retries immediately – which makes the management CPU hot. After all, most 
client software just wants to read a whole file or write a whole file. And if 
two people save the same word processing document at nearly the same time, 
who's to say who was first ?

Still, I wonder why someone working on a Linux network file system, or APFS, or 
ZFS, hasn't done it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f95ef2f5d454df697b808d74174fdc3%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637049843046630883sdata=1jUmQCBsPi6VOToz%2Fx75E%2Fi9VLR%2Flj3Wbx6um5aAXnk%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a bug?

[Roman Fleysher]

Oh, Thank you Barry.

I am glad it is not a bug. Bug is in my head.

Thank you,

Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Barry [smith.bar...@gmail.com]
Sent: Tuesday, June 18, 2019 7:59 PM
To: SQLite mailing list
Subject: Re: [sqlite] Is this a bug?

Your nested select statement wants to return many rows. Technically I think
this is illegal SQL (?), but it looks like SQLite is being lax about this
as usual and doing what it normally does in this sort of situation: picks a
row at random from the inner select.

Your statement is more or less equivalent to:
SELECT DISTINCT refVolume FROM cosSquered WHERE (refVolumn
CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM cosSquared LIMIT 1)

The statement that will output the same same list of refVolumes is:
SELECT DISTINCT refVolume FROM cosSquared WHERE CAST(10 * cosSquared AS
INT) = 9

On Tue, 18 Jun 2019 at 16:44, Roman Fleysher 
wrote:

> Dear SQLiters,
>
>
> I can not figure out what I am doing wrong. In testing, I simplified to
> the following:
>
> CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL);
>
> SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY
> refVolume;
>
> refVolume   CAST(10*max(cosSquared) AS INT)
> --  ---
> 2   9
> 3   9
> 4   9
> 5   9
> .
> 31  9
> 32  9
> 33  9
>
> That is, we see that for refVolumes between 2 and 33, the value of the
> CAST() is always 9. Thus, I expect the following statement to output the
> same list of refVolumes. But it does not:
>
> SELECT DISTINCT refVolume FROM cosSquared
>  WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM
> cosSquared);
>
> refVolume
> --
> 2
>
> What am I doing wrong? I am using version 3.16.
>
> Thank you for your help,
>
> Roman
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3Dreserved=0
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this a bug?

[Roman Fleysher]

Dear SQLiters,


I can not figure out what I am doing wrong. In testing, I simplified to the 
following:

CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL);

SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY 
refVolume;

refVolume   CAST(10*max(cosSquared) AS INT)
--  ---
2   9
3   9
4   9
5   9
.
31  9
32  9
33  9

That is, we see that for refVolumes between 2 and 33, the value of the CAST() 
is always 9. Thus, I expect the following statement to output the same list of 
refVolumes. But it does not:

SELECT DISTINCT refVolume FROM cosSquared
 WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM 
cosSquared);

refVolume
--
2

What am I doing wrong? I am using version 3.16.

Thank you for your help,

Roman

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

Re: [sqlite] select within transaction

[Roman Fleysher]

Thank you,  Adrian. I think this is reason changes() exist.

Roman



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Adrian Ho 
Date: 6/15/19 12:25 AM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] select within transaction

On 15/6/19 2:22 AM, Roman Fleysher wrote:
> I have a transaction consisting of two commands: update and select. The idea 
> is to get new state after update:
>
> PRAGMA busy_timeout = 50;
> BEGIN EXCLUSIVE;
> UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
> SELECT  d FROM t WHERE c = 5 AND ...;
> COMMIT;
>
> Is this what will happen:
>
> 1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
> 2. If lock obtained, attempt to update table t to set c=5.
> 3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain 
> d. If update failed, then c will not be 5 (it will be old value, different 
> from 5) and output of SEELCT will be empty.
>
> Since ROLLBACK is not an error, I want SELECT to be executed only will update 
> actually happened (not rollback). Because of EXCLUSIVE, I want it to be in 
> one transaction and thus I need some indicator if SELECT was after successful 
> update, not rollback.
>
> Is this what changes() is for?

"Successful update" is rather vague. In some contexts, "no rows changed,
but no error thrown either" might be considered successful.

So there are actually *three* scenarios for your existing code here:

1. UPDATE touches zero rows (WHERE clause matches nothing) -- SELECT
happens.

2. UPDATE touches one or more rows (WHERE clause matches something) --
SELECT happens.

3. UPDATE touches one or more rows, but triggers a constraint violation
in the process -- ROLLBACK kicks in, SELECT doesn't happen.

If you actually want the SELECT to *not* happen in scenario 1, and you
*must* use the SQLite shell instead of a proper language binding like
the Tcl API 
(https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Ftclsqlite.htmldata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26da762da6ec42631c3b08d6f14973d9%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961695133290069sdata=QRTKRQ1%2F4kqnHPzXv3mr8o%2BiDPoW9fQWcDraMCl7W%2Fk%3Dreserved=0),
 then I think you're
stuck. You can sorta get what you want by changing your SELECT statement
as follows:

SELECT  d FROM t WHERE changes() > 0 AND c = 5;

which still runs the SELECT, but returns nothing in scenario 1. It's
just not very efficient, especially for large tables.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26da762da6ec42631c3b08d6f14973d9%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961695133290069sdata=PO4P1VXub%2FA6isCptXd4rHPUbw1UywudAs0WJkFmiPM%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select within transaction

[Roman Fleysher]

Thank you! I did not know (or forgot) about ".bail on"

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
David Raymond [david.raym...@tomtom.com]
Sent: Friday, June 14, 2019 3:05 PM
To: SQLite mailing list
Subject: Re: [sqlite] select within transaction

How are you sending the commands to the cli?

If you're doing...

sqlite3 myfile.sqlite ".read somefile.sql"

...then you can start the sql file with...

.bail on

...and as soon as it hits an error it will stop there and not continue 
processing lines. So if you get rid of the "or rollback" then you'll get the 
error message and won't have to worry about it continuing on to the next lines 
in the input file despite there having been an error. And since you explicitly 
started a transaction it will leave the transaction open, and then when the CLI 
closes it will rollback the uncommitted transaction.


-Original Message-
From: sqlite-users  On Behalf Of 
Roman Fleysher
Sent: Friday, June 14, 2019 2:23 PM
To: General Discussion of SQLite Database 
Subject: [sqlite] select within transaction

Dear SQLiters,

I am using sqlite3 shell.

I have a transaction consisting of two commands: update and select. The idea is 
to get new state after update:

PRAGMA busy_timeout = 50;
BEGIN EXCLUSIVE;
UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
SELECT  d FROM t WHERE c = 5 AND ...;
COMMIT;

Is this what will happen:

1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
2. If lock obtained, attempt to update table t to set c=5.
3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. 
If update failed, then c will not be 5 (it will be old value, different from 5) 
and output of SEELCT will be empty.

Since ROLLBACK is not an error, I want SELECT to be executed only will update 
actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one 
transaction and thus I need some indicator if SELECT was after successful 
update, not rollback.

Is this what changes() is for?

Thank you,

Roman
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ca0e391ba075f446ff99e08d6f0fb5884%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961359675816752sdata=R2QsP5EZEMtjFyMMJc3xLCeLztLVGohigXa4PLLDBBA%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ca0e391ba075f446ff99e08d6f0fb5884%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636961359675816752sdata=R2QsP5EZEMtjFyMMJc3xLCeLztLVGohigXa4PLLDBBA%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] select within transaction

[Roman Fleysher]

Dear SQLiters,

I am using sqlite3 shell.

I have a transaction consisting of two commands: update and select. The idea is 
to get new state after update:

PRAGMA busy_timeout = 50;
BEGIN EXCLUSIVE;
UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
SELECT  d FROM t WHERE c = 5 AND ...;
COMMIT;

Is this what will happen:

1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
2. If lock obtained, attempt to update table t to set c=5.
3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain d. 
If update failed, then c will not be 5 (it will be old value, different from 5) 
and output of SEELCT will be empty.

Since ROLLBACK is not an error, I want SELECT to be executed only will update 
actually happened (not rollback). Because of EXCLUSIVE, I want it to be in one 
transaction and thus I need some indicator if SELECT was after successful 
update, not rollback.

Is this what changes() is for?

Thank you,

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

Re: [sqlite] add constant to INTEGER PRIMARY KEY

[Roman Fleysher]

Thank you, Keith, for suggestion.

The "t" is time index, with respect to the start of a clock. Since clock can 
only move forward, it is unique. I need to shift the start of the clock 

My clock is on raster of 10 milliseconds, so are the shifts. Thus I can use 
your method to shift twice: by (constant - 5) then by 5.

Thank you,

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Keith Medcalf [kmedc...@dessus.com]
Sent: Thursday, December 13, 2018 6:06 PM
To: SQLite mailing list
Subject: Re: [sqlite] add constant to INTEGER PRIMARY KEY

You cannot do that.  The PRIMARY KEY is required to be unique at each "step" 
along the way, especially as this is the parent in a foreign key relationship.  
Letting alone why anyone would want to do such a thing (which is beyond my 
ken), you simply have to make sure that your values are unique.

constant = select (max(t)-min(t))*47 from table1
update table1 set t = t + constant;
update table1 set t = t - constant + (my modification);

which will work provided that (max(t)-min(t))*47 + max(t) is not greater than 
the value that can be stored in a 64-bit signed integer and
(max(t)-min(t))*47 + min(t) is not less that the value that can be stored in a 
64-bit unsigned integer
and that your new t's will all fit in a 64-bit unsigned integer.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Roman Fleysher
>Sent: Thursday, 13 December, 2018 15:39
>To: General Discussion of SQLite Database
>Subject: [sqlite] add constant to INTEGER PRIMARY KEY
>
>Dear SQLiters,
>
>I would like to update a column declared as INTEGER PRIMARY KEY. This
>column is parent to a column of another table:
>
>table1 ( t INTEGER PRIMARY KEY NOT NULL);
>
>table2 (t INTEGER PRIMARY KEY NOT NULL REFERENCES table1(t) ON DELETE
>CASCADE ON UPDATE CASCADE);
>
>I keep PRAGMA foreign_keys = 'yes'; so that when I update table1,
>table2 also gets updated. Values in column t are positive and
>negative integers. When I try to add a constant I get constraint
>violation:
>
>UPDATE table1 SET t =  t + 8000;
>
>
>Error: UNIQUE constraint failed: table1.t
>
>
>But, if I subtract a positive constant, the update succeeds.
>
>
>I understand that as I add or subtract a constant, numbers become
>those that already exist. However, by the end of the UPDATE, all
>numbers will still be unique.
>
>
>It looks like the UPDATE happens in some order and if I can force the
>order of update depending on the sign of the constant, it will always
>succeed as I need. But UPDATE has no ordering mechanism because it
>makes no sense to have one.
>
>
>Does it mean I am doing it incorrectly? How to do I update?
>Bracketing with BEGIN .. COMMIT did not help.
>
>
>
>Thank you,
>
>
>Roman
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C6c83fdc6d5434b2192d008d6614f91cd%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636803391712215292sdata=pl14VeTl%2FTVBtEOD1azIWzSspGJDv25VndQwblaiQjM%3Dreserved=0



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C6c83fdc6d5434b2192d008d6614f91cd%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636803391712225300sdata=k%2BFBzAh8eaHmNC66MKK1BDyTPzJGBunVXhji5CJKy0M%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] add constant to INTEGER PRIMARY KEY

[Roman Fleysher]

Dear SQLiters,

I would like to update a column declared as INTEGER PRIMARY KEY. This column is 
parent to a column of another table:

table1 ( t INTEGER PRIMARY KEY NOT NULL);

table2 (t INTEGER PRIMARY KEY NOT NULL REFERENCES table1(t) ON DELETE CASCADE 
ON UPDATE CASCADE);

I keep PRAGMA foreign_keys = 'yes'; so that when I update table1, table2 also 
gets updated. Values in column t are positive and negative integers. When I try 
to add a constant I get constraint violation:

UPDATE table1 SET t =  t + 8000;


Error: UNIQUE constraint failed: table1.t


But, if I subtract a positive constant, the update succeeds.


I understand that as I add or subtract a constant, numbers become those that 
already exist. However, by the end of the UPDATE, all numbers will still be 
unique.


It looks like the UPDATE happens in some order and if I can force the order of 
update depending on the sign of the constant, it will always succeed as I need. 
But UPDATE has no ordering mechanism because it makes no sense to have one.


Does it mean I am doing it incorrectly? How to do I update? Bracketing with 
BEGIN .. COMMIT did not help.



Thank you,


Roman

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

Re: [sqlite] Grouping guidance

[Roman Fleysher]

Hypothesis can never be proven. It can only be rejected with data contradicting 
it at hand.

"..the quickest way ..." implies someone else corrects you. 


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Don V Nielsen [donvniel...@gmail.com]
Sent: Thursday, December 13, 2018 4:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Grouping guidance

Two thumbs up on Igor. They say the quickest way to the correct answer is
to post the wrong answer on the internet. Hypothesis just proven.

On Thu, Dec 13, 2018 at 3:00 PM Igor Tandetnik  wrote:

> On 12/13/2018 3:41 PM, Igor Tandetnik wrote:
> > On 12/13/2018 3:27 PM, Don V Nielsen wrote:
> >> Making a mountain out of a mole hill, but isn't the solution more
> complex
> >> that that? The description has to be Foo & Bar. But if given the
> following,
> >> then the simple answer dies.
> >
> > select Request from MyTable group by Request
> > having sum(Description='Foo') > 0 and sum(Description='Bar') > 0;
>
> ... and sum(Description not in ['Foo', 'Bar']) = 0
>
> Add conditions to taste.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ce637cb97a64741ad769808d6614148f0%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636803330358231811sdata=6j1x7YK3TEkbcyOpMGvLS3MXEj5jtJUtuU8CRvE%2FmhQ%3Dreserved=0
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ce637cb97a64741ad769808d6614148f0%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636803330358231811sdata=6j1x7YK3TEkbcyOpMGvLS3MXEj5jtJUtuU8CRvE%2FmhQ%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] nested foreign keys

[Roman Fleysher]

I found the cause of my issue.

I have two columns as foreign key in child, which reference corresponding pair 
in parent. But, I was setting up the references separately, not as a pair. And 
it looked like it can not work.

It works because the pair of columns in parent is PRIMARY KEY and thus has 
unique index -- the only requirement for foreign keys to work.

Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Keith Medcalf [kmedc...@dessus.com]
Sent: Thursday, October 25, 2018 1:03 AM
To: SQLite mailing list
Subject: Re: [sqlite] nested foreign keys

No, it means that you did not specify the whatisness of grandParent, parent, or 
child; and/or, you have not enabled foreign_keys.

https://sqlite.org/lang_createtable.html
https://sqlite.org/pragma.html#pragma_foreign_keys

NB:  I have compiled the CLI with foreign key enforcement ON be default.  The 
default distributions usually have foreign keys enforcement turned off, 
because, well, who wants a database that enforces referential integrity?  (All 
kidding aside, the reason that foreign key enforcement is OFF by default is to 
maintain backward compatibility with older versions of SQLite that "parsed" 
such constraints but did not allow for enforcement of them).

SQLite version 3.26.0 2018-10-23 13:48:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table grandParent (id PRIMARY KEY );
sqlite> create table parent (id PRIMARY KEY REFERENCES grandParent(id));
sqlite> create table child (id PRIMARY KEY REFERENCES parent(id));
sqlite> insert into parent values (1);
Error: FOREIGN KEY constraint failed
sqlite> insert into child values (1);
Error: FOREIGN KEY constraint failed
sqlite> insert into grandparent values (1);
sqlite> insert into parent values (1);
sqlite> insert into child values (1);
sqlite> delete from parent where id=1;
Error: FOREIGN KEY constraint failed
sqlite> delete from grandparent where id=1;
Error: FOREIGN KEY constraint failed
sqlite> delete from child where id=1;
sqlite> delete from grandparent where id=1;
Error: FOREIGN KEY constraint failed
sqlite> delete from parent where id=1;
sqlite> delete from grandparent where id=1;
sqlite>

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Roman Fleysher
>Sent: Wednesday, 24 October, 2018 22:30
>To: General Discussion of SQLite Database
>Subject: [sqlite] nested foreign keys
>
>Dear SQLiters,
>
>I am trying to set up what I would call "nested foreign keys":
>
>create grandParent (id PRIMARY KEY )
>create parent (id PRIMARY KEY REFERENCES grandParent(id))
>create child (id PRIMARY KEY REFERENCES parent(id))
>
>SQLite complains. Does it mean that grand children are not allowed?
>
>Thank you,
>
>Roman
>___
>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] nested foreign keys

[Roman Fleysher]

The statements work. Insertion fails.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
David Yip [dw...@peach-bun.com]
Sent: Thursday, October 25, 2018 12:37 AM
To: SQLite mailing list
Subject: Re: [sqlite] nested foreign keys

These statements worked for me:


CREATE TABLE grandparent (id INTEGER PRIMARY KEY);

CREATE TABLE parent (id INTEGER PRIMARY KEY REFERENCES grandparent(id));

CREATE TABLE child (id INTEGER PRIMARY KEY REFERENCES parent(id));


The foreign key constraints work as you'd expect also.


What are you doing and what error are you seeing?


- David

On Wed, Oct 24, 2018 at 11:30 PM Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
> I am trying to set up what I would call "nested foreign keys":
>
> create grandParent (id PRIMARY KEY )
> create parent (id PRIMARY KEY REFERENCES grandParent(id))
> create child (id PRIMARY KEY REFERENCES parent(id))
>
> SQLite complains. Does it mean that grand children are not allowed?
>
> Thank you,
>
> Roman
> ___
> 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] nested foreign keys

[Roman Fleysher]

Dear SQLIters,

I am trying to set up what I would call "nested foreign keys":

create grandParent( id PRIMARY KEY)
create parent (id PRIMARY KEY REFERENCES grandParent(id))

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

[sqlite] nested foreign keys

[Roman Fleysher]

Dear SQLiters,

I am trying to set up what I would call "nested foreign keys":

create grandParent (id PRIMARY KEY )
create parent (id PRIMARY KEY REFERENCES grandParent(id))
create child (id PRIMARY KEY REFERENCES parent(id))

SQLite complains. Does it mean that grand children are not allowed?

Thank you,

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

[sqlite] INSERT OR REPLACE and foreign keys

[Roman Fleysher]

Dear SQLiters,

I am using INSERT OR REPLACE to update a table which holds a column which 
servers as a foreign key. But I noticed a strange behavior: If the parent 
record existed, then replace mechanism replaces it, but the records from 
children tables are deleted. The foreign key is set up to cascade on delete, as 
I think it should. So it seems that "replace" is implemented as "delete then 
insert" rather than "update". Is that normal and expected? Am I doing something 
wrong?

Thank you,

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

Re: [sqlite] Find key,value duplicates but with differing values

[Roman Fleysher]

 You are correct. Value should/ could be inside count(), but not in group.




Sent from my T-Mobile 4G LTE Device


 Original message 
From: R Smith 
Date: 10/11/18 4:29 PM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Find key,value duplicates but with differing values

On 2018/10/11 9:53 PM, Roman Fleysher wrote:
> It is hard for me to tell which is index, which is value and so forth in your 
> example, but how about this single select:
>
> SELECT DISTINCT key, value FROM theTable;
>
> This lists all distinct key-value possibilities. Or,
>
> SELECT key, value FROM (SELECT DISTINCT key, value FROM theTable)
> GROUP BY key, value HAVING count() > 1;
>
> This lists all key-value pairs with more than one value for the key.

If I may - this won't work directly as-is since the query:

SELECT key, value FROM (SELECT DISTINCT key, value FROM theTable)
GROUP BY key, value HAVING count() > 1;

will group by Key,Value and then output which Key-value pairs exhibit
more than one instance (count) - which is physically impossible since
counting the duplicate values from a distinct set is like asking the
number of genders among American presidents before 2018

The answer is always 1.


If, in the outer query, you Select for (and group by) Key only, then it
works.


>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of David Raymond [david.raym...@tomtom.com]
> Sent: Thursday, October 11, 2018 12:23 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Find key,value duplicates but with differing values
>
> Maybe
>
> ...
> group by partId, name
> having count(distinct xmd.value) > 1;
>
> ?
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Dominique Devienne
> Sent: Thursday, October 11, 2018 12:00 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Find key,value duplicates but with differing values
>
> I can find duplicates fine:
>
> select xmd.partId, parts.title, xmd.name,
> count(*) "#dupplicates",
> group_concat(xmd.value) "values",
> group_concat(xmd.idx) "indexes"
>from extra_meta_data xmd
>join parts on parts.id = xmd.partId
>group by partId, name
>   having "#dupplicates" > 1;
>
> but most actual duplicates have the same value, so are harmless.
> so I'd like to select only the xmd.name rows which have differing values.
>
> Knowing that there can be more than 2 duplicates, i.e. a slef-join is not
> enough I think.
> Any hints on how to go about this problem please? Thanks, --DD
> ___
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Find key,value duplicates but with differing values

[Roman Fleysher]

It is hard for me to tell which is index, which is value and so forth in your 
example, but how about this single select:

SELECT DISTINCT key, value FROM theTable;

This lists all distinct key-value possibilities. Or,

SELECT key, value FROM (SELECT DISTINCT key, value FROM theTable) 
GROUP BY key, value HAVING count() > 1;

This lists all key-value pairs with more than one value for the key. 

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
David Raymond [david.raym...@tomtom.com]
Sent: Thursday, October 11, 2018 12:23 PM
To: SQLite mailing list
Subject: Re: [sqlite] Find key,value duplicates but with differing values

Maybe

...
group by partId, name
having count(distinct xmd.value) > 1;

?


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Dominique Devienne
Sent: Thursday, October 11, 2018 12:00 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Find key,value duplicates but with differing values

I can find duplicates fine:

select xmd.partId, parts.title, xmd.name,
   count(*) "#dupplicates",
   group_concat(xmd.value) "values",
   group_concat(xmd.idx) "indexes"
  from extra_meta_data xmd
  join parts on parts.id = xmd.partId
  group by partId, name
 having "#dupplicates" > 1;

but most actual duplicates have the same value, so are harmless.
so I'd like to select only the xmd.name rows which have differing values.

Knowing that there can be more than 2 duplicates, i.e. a slef-join is not
enough I think.
Any hints on how to go about this problem please? Thanks, --DD
___
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] clocks in SQLite

[Roman Fleysher]

Thank you for pointing the 24 hours. I did not notice the day change. 

Now, I have no idea how this can happen. I will investigate more. 

Roman


From: Graham Holden [sql...@aldurslair.com]
Sent: Tuesday, May 15, 2018 3:39 PM
To: Roman Fleysher
Cc: General Discussion of SQLite Database
Subject: Re: [sqlite] clocks in SQLite

Tuesday, May 15, 2018, 7:49:32 PM, Roman wrote:

> Job 1847 ends at 16:44:11 and job 1852 starts at 18:47:46 (and
> actually subsequently dies as evidenced by its stop being NULL).

How do you KNOW that your program didn't spend 2 hours 3 minutes
either not noticing job 1847 had finished, or deciding what to do next
once it had noticed? Do you KNOW, say, that THAT process didn't die
and was restarted?

> Next job, 2283, is started BEFORE job 1852. RunID is INTEGER PRIMARY
> KEY and for this purpose is auto incrementing. Jobs are also running
> on other nodes, therefore runID is not contiguous for this node.

Job 2283 starts NEARLY 24 hours AFTER job 1852!

> runID  hostname   start   stop
> -- -- --- ---
> 1841   loginnode4 2018-05-14 07:53:42 2018-05-14 10:05:41
> 1843   loginnode4 2018-05-14 10:05:41 2018-05-14 12:18:05
> 1845   loginnode4 2018-05-14 12:18:05 2018-05-14 14:30:50
> 1847   loginnode4 2018-05-14 14:30:50 2018-05-14 16:44:11
> 1852   loginnode4 2018-05-14 18:47:56
> 2283   loginnode4 2018-05-15 18:18:59

> Could it indicate other issues than the clock itself? It is highly
> unlikely that clock happened to jump forward at the time when 1852
> was finishing (at 16:44:11). Time of start of 2283 looks
> correct, agrees with my watch, because I started this job manually.

> Roman

Regards,
Graham Holden


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

[sqlite] clocks in SQLite

[Roman Fleysher]

Dear SQLIters,

I use datetime('now') to record when a job gets started and stopped. As soon as 
a job stops, new is started. Thus stop of the previous should be within a 
second of the start of the next, as you see in the first few lines. But then 
the clock jumps.

Job 1847 ends at 16:44:11 and job 1852 starts at 18:47:46 (and actually 
subsequently dies as evidenced by its stop being NULL). Next job, 2283, is 
started BEFORE job 1852. RunID is INTEGER PRIMARY KEY and for this purpose is 
auto incrementing. Jobs are also running on other nodes, therefore runID is not 
contiguous for this node.


runID  hostname   start   stop
-- -- --- ---
1841   loginnode4 2018-05-14 07:53:42 2018-05-14 10:05:41
1843   loginnode4 2018-05-14 10:05:41 2018-05-14 12:18:05
1845   loginnode4 2018-05-14 12:18:05 2018-05-14 14:30:50
1847   loginnode4 2018-05-14 14:30:50 2018-05-14 16:44:11
1852   loginnode4 2018-05-14 18:47:56
2283   loginnode4 2018-05-15 18:18:59

Could it indicate other issues than the clock itself? It is highly unlikely 
that clock happened to jump forward at the time when 1852 was finishing (at 
16:44:11). Time of start of 2283 looks correct, agrees with my watch, because I 
started this job manually.

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

Re: [sqlite] 3.24 draft - upsert

[Roman Fleysher]

Why does SQLite have to follow what PostgreSQL does? I thought SQLite is the 
leader.

Roman



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Richard Hipp 
Date: 5/9/18 5:48 AM (GMT-05:00)
To: SQLite mailing list 
Subject: Re: [sqlite] 3.24 draft - upsert

On 5/9/18, Olivier Mascia  wrote:
> About:
>
> "Column names in the expressions of a DO UPDATE refer to the original
> unchanged value of the column, before the attempted INSERT. To use the value
> that would have been inserted had the constraint not failed, add the special
> "excluded." table qualifier to the column name."
>
> Why using 'excluded' wording for this?

Because that is what PostgreSQL does.  I also thought that "new" would
have been a better choice, but they didn't consult me.  :-)

--
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] probably recursive?

[Roman Fleysher]

Thank you Cezary and others who commented.

For some reason, I did not receive email from Cezary, only comments on it.

I was under impression that RECURSIVE can not be used in sub-query. I see that 
it can.

But, most importantly, could you elaborate more on how it works. I agree it is 
n-to-n problem. But the solution merges all data into a single cell with all 
pairs, which is counter to relational solution.

I ask for details, if possible, because the actual problem  that I have to 
solve is a bit more complicated: I have two of such lists good(x,y) and 
bad(x,y) with a coupling condition that if x is removed from one list, it must 
be removed from the other. This is easy to add for ones who understand how it 
works. 

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
E.Pasma [pasm...@concepts.nl]
Sent: Friday, May 04, 2018 10:35 AM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

Cezary H. Noweta wrote:
>>
>>> At the beginning I would like to agree with that the problem is
>>> iterative rather then recursive one. However
R. Smith wrote:
>
>> LOL, that might be the hackiest query I ever seen, but kudos mate,
>> that's bloody marvellous!

Cezary, thanks for the diverting solution. I've been looking into
solving sudokus along the same lines. A function GROUP_SPLIT as an
inverse of GROUP_CONCAT would be handy here. Below is the query as it
looks when there was such a function, or actually a (function like)
virtual table with column elem. It is exactly half the size.
Nevertheless the fun is to achieve the goal purely in SQL.
Thanks, E Pasma.

CREATE TABLE points AS WITH cte(x,y,n) AS (SELECT (random() % 10 + 10)
% 10 + 1, (random() % 10 + 10) % 10 + 1, 1 UNION ALL SELECT (random()
% 10 + 10) % 10 + 1, (random() % 10 + 10) % 10 + 1, n + 1 FROM cte
WHERE n < 100) SELECT x, y FROM cte;

WITH
   params(nx, ny) AS (SELECT 6, 8),
   main(elem, rest) AS (
 SELECT NULL, (
   WITH
 state(clock, points, xaxis, yaxis, nxmin, nxmax, nymin,
nymax) AS (
   SELECT
 0,
 (SELECT GROUP_CONCAT(x || ' ' || y) FROM points),
(SELECT GROUP_CONCAT(x || ' ' || n) FROM (SELECT x, COUNT(x) n FROM
points GROUP BY x)), (SELECT GROUP_CONCAT(y || ' ' || n) FROM (SELECT
y, COUNT(y) n FROM points GROUP BY y)), (SELECT MIN(n) FROM (SELECT x,
COUNT(x) n FROM points GROUP BY x)), (SELECT MAX(n) FROM (SELECT x,
COUNT(x) n FROM points GROUP BY x)), (SELECT MIN(n) FROM (SELECT y,
COUNT(y) n FROM points GROUP BY y)), (SELECT MAX(n) FROM (SELECT y,
COUNT(y) n FROM points GROUP BY y))
   UNION ALL
   SELECT
 (clock + 1) % 3,
 CASE clock WHEN 0 THEN
 (SELECT GROUP_CONCAT(x || ' ' || y) FROM (
   SELECT
 CAST(elem AS INTEGER) x,
 CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
   FROM GROUP_SPLIT (state.points)
 )
 WHERE (x NOT IN (
   SELECT x FROM (
 SELECT
   CAST(elem AS INTEGER) x,
   CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
   FROM GROUP_SPLIT (state.xaxis)
   ) WHERE n < (SELECT nx FROM params)
 )) AND (y NOT IN (
   SELECT y FROM (
 SELECT
   CAST(elem AS INTEGER) y,
   CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
   FROM GROUP_SPLIT (state.yaxis)
   ) WHERE n < (SELECT ny FROM params)
 ))) ELSE points END,
 CASE clock WHEN 1 THEN
(SELECT GROUP_CONCAT(x || ' ' || n) FROM (SELECT x, COUNT(x) n FROM (
   SELECT
 CAST(elem AS INTEGER) x
   FROM GROUP_SPLIT (state.points)
 ) GROUP BY x)) ELSE xaxis END,
 CASE clock WHEN 1 THEN
(SELECT GROUP_CONCAT(y || ' ' || n) FROM (SELECT y, COUNT(y) n FROM (
   SELECT
 CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) y
   FROM GROUP_SPLIT (state.points)
 ) GROUP BY y)) ELSE yaxis END,
 CASE clock WHEN 2 THEN
 (SELECT MIN(n) FROM (
   SELECT
 CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
   FROM GROUP_SPLIT (state.xaxis)
 )) ELSE nxmin END,
 CASE clock WHEN 2 THEN
 (SELECT MAX(n) FROM (
   SELECT
 CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
   FROM GROUP_SPLIT (state.xaxis)
 )) ELSE nxmax END,
 CASE clock WHEN 2 THEN
 (SELECT MIN(n) FROM (
   SELECT
 CAST(SUBSTR(elem, INSTR(elem, ' ')) AS INTEGER) n
   FROM GROUP_SPLIT (state.yaxis)
 )) ELSE nymin END,
 CASE clock WHEN 2 THEN
 (SELECT MAX(n) FROM (
   SELECT
 

Re: [sqlite] probably recursive?

[Roman Fleysher]

nX is a number, the smallest allowed count. There are two conditions,  count of 
dots along horizontal line and count of dots along verticals.



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Barry Smith <smith.bar...@gmail.com>
Date: 5/1/18 7:40 PM (GMT-05:00)
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] probably recursive?

Ah my bad, I misunderstood the initial condition. nX is a function of X. My 
statements were only true if nX=X. Well, sorry about the noise.

> On 2 May 2018, at 8:20 am, Roman Fleysher <roman.fleys...@einstein.yu.edu> 
> wrote:
>
> Dear Barry,
>
> The statement about the square is not obvious to me. The requirements on 
> counts in x and y are different.
>
> I also imagine answer could be two or several non-overlapping  "rectangles". 
> "Rectangles" will not be densely filled with dots, they might have empty 
> spots either because the points were never on the list or were eliminated.
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 6:12 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
>
> Well those constraints simplify your problem.
>
> In the resultant dataset, the largest X and Y values will be equal, and the 
> largest X will have and entry for every coordinate from (X, 1) to (X, X). 
> Likewise the largest Y will have an entry for every coordinate from (1, Y) to 
> (Y, Y). Basically you'll have two lines from the axes, drawing a square. All 
> points outside that square will be culled, all points on and inside the 
> square will be kept.
>
> Since you know that, you now have a one dimensional problem to solve. It 
> still seems a little recursive to me, but it should be easier because you 
> only need to find a single number (which you can then plug into a delete 
> statement).
>
> If my statement about the square is not obvious to prove in your head I can 
> try write a proof for that but I'm not much good at proofs.
>
>> On 2 May 2018, at 7:27 am, Roman Fleysher <roman.fleys...@einstein.yu.edu> 
>> wrote:
>>
>> Pairs (x,y) do not repeat.
>>
>> Actual x and y are positive integers, but I do not see how being positive 
>> can be relevant. Integer is important for sorting/comparison.
>>
>>
>> Roman
>>
>> 
>> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
>> of Barry Smith [smith.bar...@gmail.com]
>> Sent: Tuesday, May 01, 2018 5:23 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] probably recursive?
>>
>> Is there a uniqueness constraint on your initial data? Can the same 
>> coordinate be listed multiple times?
>>
>> Is there a requirement that X > 0 and Y > 0?
>>
>>>> On 2 May 2018, at 3:35 am, Simon Slavin <slav...@bigfraud.org> wrote:
>>>>
>>>> On 1 May 2018, at 6:28pm, Simon Slavin <slav...@bigfraud.org> wrote:
>>>>
>>>> I just realised that
>>>
>>> That was intended to be personal email.  Apologies, everyone.
>>>
>>> Simon.
>>> ___
>>> 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
> ___
> 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] probably recursive?

[Roman Fleysher]

Dear Barry,

The statement about the square is not obvious to me. The requirements on counts 
in x and y are different.

I also imagine answer could be two or several non-overlapping  "rectangles". 
"Rectangles" will not be densely filled with dots, they might have empty spots 
either because the points were never on the list or were eliminated.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Barry Smith [smith.bar...@gmail.com]
Sent: Tuesday, May 01, 2018 6:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

Well those constraints simplify your problem.

In the resultant dataset, the largest X and Y values will be equal, and the 
largest X will have and entry for every coordinate from (X, 1) to (X, X). 
Likewise the largest Y will have an entry for every coordinate from (1, Y) to 
(Y, Y). Basically you'll have two lines from the axes, drawing a square. All 
points outside that square will be culled, all points on and inside the square 
will be kept.

Since you know that, you now have a one dimensional problem to solve. It still 
seems a little recursive to me, but it should be easier because you only need 
to find a single number (which you can then plug into a delete statement).

If my statement about the square is not obvious to prove in your head I can try 
write a proof for that but I'm not much good at proofs.

> On 2 May 2018, at 7:27 am, Roman Fleysher <roman.fleys...@einstein.yu.edu> 
> wrote:
>
> Pairs (x,y) do not repeat.
>
> Actual x and y are positive integers, but I do not see how being positive can 
> be relevant. Integer is important for sorting/comparison.
>
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 5:23 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
>
> Is there a uniqueness constraint on your initial data? Can the same 
> coordinate be listed multiple times?
>
> Is there a requirement that X > 0 and Y > 0?
>
>>> On 2 May 2018, at 3:35 am, Simon Slavin <slav...@bigfraud.org> wrote:
>>>
>>> On 1 May 2018, at 6:28pm, Simon Slavin <slav...@bigfraud.org> wrote:
>>>
>>> I just realised that
>>
>> That was intended to be personal email.  Apologies, everyone.
>>
>> Simon.
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] probably recursive?

[Roman Fleysher]

Pairs (x,y) do not repeat.

Actual x and y are positive integers, but I do not see how being positive can 
be relevant. Integer is important for sorting/comparison.


Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Barry Smith [smith.bar...@gmail.com]
Sent: Tuesday, May 01, 2018 5:23 PM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

Is there a uniqueness constraint on your initial data? Can the same coordinate 
be listed multiple times?

Is there a requirement that X > 0 and Y > 0?

> On 2 May 2018, at 3:35 am, Simon Slavin  wrote:
>
>> On 1 May 2018, at 6:28pm, Simon Slavin  wrote:
>>
>> I just realised that
>
> That was intended to be personal email.  Apologies, everyone.
>
> Simon.
> ___
> 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] probably recursive?

[Roman Fleysher]

Agree. Thank you.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Simon Slavin [slav...@bigfraud.org]
Sent: Tuesday, May 01, 2018 12:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

On 1 May 2018, at 5:34pm, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:

> With recursive route, I am thinking I need to build deleteList(x,y).

Rather than actually delete rows, if you can, insert a new column in the table 
of all points.  It starts with every row set to TRUE.  When you decide a row 
doesn't count the value gets set to FALSE.

This will be faster than doing the processing and file handling involved in 
deleting rows.

Simon.
___
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] probably recursive?

[Roman Fleysher]

With recursive route, I am thinking I need to build deleteList(x,y). But I can 
not come up with a way to use deleteList only once in the FROM after UNION and 
not in subqueries , as required by WITH RECURSIVE. Assuming pairsTable(x,y) is 
the input table: 

WITH RECURSIVE deleteList(x, y) AS 
( SELECT NULL, NULL
  UNION
  SELECT x, y FROM pairsTable 
  WHERE x IN (SELECT x FROM (SELECT x, y FROM pairsTable 
   EXCEPT 
   SELECT x, y FROM 
deleteList WHERE x IS NOT NULL)
   GROUP BY x HAVING count(x) < 25)
)
SELECT x, y FROM deleteList;



Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
David Raymond [david.raym...@tomtom.com]
Sent: Tuesday, May 01, 2018 10:27 AM
To: SQLite mailing list
Subject: Re: [sqlite] probably recursive?

My initial thought on this would be recursive on delete triggers. You're 
limited then to SQLITE_MAX_TRIGGER_DEPTH (defaults to 1,000) though, so really 
big cascades wouldn't fully complete. You can raise the limit, but 
mathematically speaking there's still going to be a limit then.

Will have to think about the recursive CTE route later.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Tuesday, May 01, 2018 8:16 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] probably recursive?

That depends on what you mean by "Could this be achieved in SQLite?".

There is no query (in any SQL engine) that can depend on a sub-query
that is itself dependent on the outcome of the main query. This is what
makes recursion beautiful, but then there is also no CTE (or other query
in any SQL engine) that can recurse over multiple states of data (i.e.
query data in one single query to reflect results from both before and
after a delete in the source table), nor can a CTE be updated or deleted
from, its data must persist atomically (with some exceptions when using
non-deterministic functions, like random).

These are not so much "inabilities" of SQL engines, but more due to
explicit SQL and set-algebra rules.

So this is not possible in a single query.

You can of course "achieve" it using any SQL engine by constructing a
temporary table, and then repeatedly run a DELETE query for all x values
where COUNT(y) is less than nY, then DELETE all y values where COUNT(x)
< nX, rinse, repeat until  both SELECT y HAVING COUNT(x) < nX and SELECT
x HAVING COUNT(y) < nY aggregate queries return empty sets - but this
would be painfully slow next to a simple software algorithm that
prunes/resolves a 2-dimensional array - exponentially worse so for
larger grid sizes.


On 2018/05/01 2:45 AM, Roman Fleysher wrote:
> Dear SQLiters,
>
> I have trouble solving this problem, maybe it is impossible?
>
> I have a table with two columns x and y, both integers. Imagine they are 
> coordinates on X-Y plane, dots. I need to find all x's that have more than nX 
> dots, and all y's that have more than nY dots. Both conditions must be 
> simultaneous in the following sense:
>
> If x=10 has less than nX dots, all dots with x=10 are deleted. Because of 
> deletion, y=3 which previously had more than nY dots no longer passes the 
> threshold and thus y=3 must be deleted too. This could cause deletion of some 
> other x, etc. At the end, number of dots on all vertical lines must be more 
> than nX and number of dots on all horizontal lines must be more than nY.
>
> Could this be achieved with SQLite?
>
> Roman
> ___
> 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

[sqlite] probably recursive?

[Roman Fleysher]

Dear SQLiters,

I have trouble solving this problem, maybe it is impossible?

I have a table with two columns x and y, both integers. Imagine they are 
coordinates on X-Y plane, dots. I need to find all x's that have more than nX 
dots, and all y's that have more than nY dots. Both conditions must be 
simultaneous in the following sense:

If x=10 has less than nX dots, all dots with x=10 are deleted. Because of 
deletion, y=3 which previously had more than nY dots no longer passes the 
threshold and thus y=3 must be deleted too. This could cause deletion of some 
other x, etc. At the end, number of dots on all vertical lines must be more 
than nX and number of dots on all horizontal lines must be more than nY.

Could this be achieved with SQLite?

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

Re: [sqlite] exit status of command shell

[Roman Fleysher]

Thank you, Peter, for confirming. I am using somewhat outdated version.

I think this is a relatively minor issue. It surfaced for me because I mostly 
use bash shell to access database and rely on error codes to report status. As 
a work around I now always add ".exit" at the end of the SQL statement.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
petern [peter.nichvolo...@gmail.com]
Sent: Thursday, April 05, 2018 3:25 AM
To: SQLite mailing list
Subject: Re: [sqlite] exit status of command shell

Roman.  That's a good one.  It affects the command status of well formed
SQL as well:

sqlite-src-323/bld$ echo 'SELECT * FROM sqlite_monster' |
./sqlite3;echo $?
Error: near line 1: no such table: sqlite_monster
0

sqlite-src-323/bld$ echo 'SELECT * FROM sqlite_monster;' |
./sqlite3;echo $?
Error: near line 1: no such table: sqlite_monster
1

Piped SQL lacking a trailing semicolon does indeed cause the shell to
report the wrong last command status.

Peter

On Wed, Apr 4, 2018 at 4:46 PM, Roman Fleysher <
roman.fleys...@einstein.yu.edu> wrote:

> Dear SQLiters,
>
>
> I am using sqlite3 shell from bash scripts and I stumbled on what I think
> is incorrect exit code on error. In the first scenario, on error the exit
> code is 1 -- expected, in the second it is 0 -- unexpected. The error
> message is the same in both. Is that normal?
>
>
> echo -e "ww; \n.exit" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 1
>
>
> echo -e "ww" | sqlite3
>
> Error: near line 1: near "ww": syntax error
>
> echo $?
>
> 0
>
> Thank you,
>
> Roman
>
> ___
> 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] exit status of command shell

[Roman Fleysher]

Dear SQLiters,


I am using sqlite3 shell from bash scripts and I stumbled on what I think is 
incorrect exit code on error. In the first scenario, on error the exit code is 
1 -- expected, in the second it is 0 -- unexpected. The error message is the 
same in both. Is that normal?


echo -e "ww; \n.exit" | sqlite3

Error: near line 1: near "ww": syntax error

echo $?

1


echo -e "ww" | sqlite3

Error: near line 1: near "ww": syntax error

echo $?

0

Thank you,

Roman

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

Re: [sqlite] primary key in another column

[Roman Fleysher]

You are right,  Igor. Clear case of XY problem. I will remove trigger.


Roman


Sent from my T-Mobile 4G LTE Device


 Original message 
From: Igor Tandetnik <i...@tandetnik.org>
Date: 1/26/18 9:03 PM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

On 1/26/2018 6:20 PM, Roman Fleysher wrote:
> I think I effectively did as you suggested using triggers. I insert NULL into 
> the ID column to create a row. This triggers the trigger to run update on the 
> table to populate the columns based on the just created ID. Is this what you 
> suggested?

Roughly, though running a single statement at the end seems simpler, and likely 
goes faster, than setting up a trigger.
--
Igor Tandetnik

___
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] primary key in another column

[Roman Fleysher]

yes, I can use a view. 

forEachRow also records what failed. Updating a view requires a trigger, but I 
can compose one with the view.

Thank you for suggestion!

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Damon [rich...@damon-family.org]
Sent: Friday, January 26, 2018 6:50 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

Couldn't you have it access a view which adds the columns by calculation
rather than the raw table? (and if you have some tables that don't need
such a view, create a simple pass through view).

On 1/26/18 6:30 PM, Roman Fleysher wrote:
> No, I can not compute inside forEachRow. ForEachRow is now universal, can be 
> applied to any table. If I modify SELECT inside it to fit specific purpose, 
> forEachRow will use universality.
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Richard Damon [rich...@damon-family.org]
> Sent: Friday, January 26, 2018 6:26 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] primary key in another column
>
> One question I have, couldn't you just omit the fileName column from the
> able, and compute it in the select query that is getting the data?
>
> On 1/26/18 6:03 PM, Roman Fleysher wrote:
>> My implementation of  "for Each row" requires all columns to be populated. 
>> It is a dumb thing:
>>
>> forEachRow  commandToBeExecuted  itsArgumentsWhichReferToColumns
>>
>> The files are images. Example:
>>
>> forEachRow  addImages outputColumn column1 column2
>>
>> ForEachRow will loop over the rows (in parallel batches if it can) and apply 
>> the command given to it with its arguments. Image processing is then a 
>> sequence of these "forEach" commands.
>>
>>
>> Roman
>>
>> On 1/26/2018 5:47 PM, Roman Fleysher wrote:
>>> I will use this table as a manager. There will be multiple columns holding 
>>> various file names. The names can be random, but I want humans to be able 
>>> to easily inspect. After table is filled, an operation "for each row"  will 
>>> get files in some columns and produce files in other columns. This is done 
>>> outside of SQLite. "For each row" will process several rows in parallel 
>>> because they are independent. Some operations might fail and will be 
>>> recored in the proper columns. After all the work is done, the manager 
>>> table is discarded.
>> I'm still not sure I understand, but: while you are building out this 
>> manager table, can't you leave fileName column blank, and then right before 
>> processing, run UPDATE A SET fileName='prefix_'||ID;  on it?
>> --
>> Igor Tandetnik
> --
> Richard Damon
>
> ___
> 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


--
Richard Damon

___
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] primary key in another column

[Roman Fleysher]

No, I can not compute inside forEachRow. ForEachRow is now universal, can be 
applied to any table. If I modify SELECT inside it to fit specific purpose, 
forEachRow will use universality.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Damon [rich...@damon-family.org]
Sent: Friday, January 26, 2018 6:26 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

One question I have, couldn't you just omit the fileName column from the
able, and compute it in the select query that is getting the data?

On 1/26/18 6:03 PM, Roman Fleysher wrote:
> My implementation of  "for Each row" requires all columns to be populated. It 
> is a dumb thing:
>
> forEachRow  commandToBeExecuted  itsArgumentsWhichReferToColumns
>
> The files are images. Example:
>
> forEachRow  addImages outputColumn column1 column2
>
> ForEachRow will loop over the rows (in parallel batches if it can) and apply 
> the command given to it with its arguments. Image processing is then a 
> sequence of these "forEach" commands.
>
>
> Roman
>
> On 1/26/2018 5:47 PM, Roman Fleysher wrote:
>> I will use this table as a manager. There will be multiple columns holding 
>> various file names. The names can be random, but I want humans to be able to 
>> easily inspect. After table is filled, an operation "for each row"  will get 
>> files in some columns and produce files in other columns. This is done 
>> outside of SQLite. "For each row" will process several rows in parallel 
>> because they are independent. Some operations might fail and will be recored 
>> in the proper columns. After all the work is done, the manager table is 
>> discarded.
> I'm still not sure I understand, but: while you are building out this manager 
> table, can't you leave fileName column blank, and then right before 
> processing, run UPDATE A SET fileName='prefix_'||ID;  on it?
> --
> Igor Tandetnik

--
Richard Damon

___
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] primary key in another column

[Roman Fleysher]

I think I effectively did as you suggested using triggers. I insert NULL into 
the ID column to create a row. This triggers the trigger to run update on the 
table to populate the columns based on the just created ID. Is this what you 
suggested?

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Igor Tandetnik [i...@tandetnik.org]
Sent: Friday, January 26, 2018 6:10 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

On 1/26/2018 6:03 PM, Roman Fleysher wrote:
> My implementation of  "for Each row" requires all columns to be populated. It 
> is a dumb thing:

You said: After table is filled, an operation "for each row"  will... I suggest 
running this UPDATE statement at the end of "table is filled", before "an 
operation will..." part.
--
Igor Tandetnik

___
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] primary key in another column

[Roman Fleysher]

My implementation of  "for Each row" requires all columns to be populated. It 
is a dumb thing:

forEachRow  commandToBeExecuted  itsArgumentsWhichReferToColumns

The files are images. Example:

forEachRow  addImages outputColumn column1 column2 

ForEachRow will loop over the rows (in parallel batches if it can) and apply 
the command given to it with its arguments. Image processing is then a sequence 
of these "forEach" commands.


Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Igor Tandetnik [i...@tandetnik.org]
Sent: Friday, January 26, 2018 5:56 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

On 1/26/2018 5:47 PM, Roman Fleysher wrote:
> I will use this table as a manager. There will be multiple columns holding 
> various file names. The names can be random, but I want humans to be able to 
> easily inspect. After table is filled, an operation "for each row"  will get 
> files in some columns and produce files in other columns. This is done 
> outside of SQLite. "For each row" will process several rows in parallel 
> because they are independent. Some operations might fail and will be recored 
> in the proper columns. After all the work is done, the manager table is 
> discarded.

I'm still not sure I understand, but: while you are building out this manager 
table, can't you leave fileName column blank, and then right before processing, 
run UPDATE A SET fileName='prefix_'||ID;  on it?
--
Igor Tandetnik


___
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] primary key in another column

[Roman Fleysher]

Igor, you are absolutely right.

But

I will use this table as a manager. There will be multiple columns holding 
various file names. The names can be random, but I want humans to be able to 
easily inspect. After table is filled, an operation "for each row"  will get 
files in some columns and produce files in other columns. This is done outside 
of SQLite. "For each row" will process several rows in parallel because they 
are independent. Some operations might fail and will be recored in the proper 
columns. After all the work is done, the manager table is discarded.

"For each row" is equivalent to SELECT, but it operates on the files 
themselves. This can be implemented within SQLIte by loading extension. I 
investigated this route (and even asked questions on this list) and eventually 
concluded that it is better to do outside because of the way parallel execution 
is done (sometimes sent to a compute cluster grid engine for queueing.) 

This makes no sense from the database point of view: No reason to hold 
redundant data with such a simple algorithm to generate it.


Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Igor Tandetnik [i...@tandetnik.org]
Sent: Friday, January 26, 2018 5:33 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] primary key in another column

On 1/26/2018 4:43 PM, Roman Fleysher wrote:
> I would like to use primary key as a way to create unique column entry:
>
> CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL)
>
> such that file name is always prefix followed by the ID for the content to be:
>
> ID  fileName
>
> 1   prefix_1
> 2   prefix_2

Why do you want to store redundant data? What's the actual problem this is 
supposed to help you solve? As stated, this looks like an XY problem ( 
http://xyproblem.info/ )

--
Igor Tandetnik

___
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] primary key in another column

[Roman Fleysher]

Solved with trigger, but I can not use NOT NULL for the fileName column:

CREATE TRIGGER AAA AFTER INSERT ON A 
BEGIN
  UPDATE A SET fileName = 'prefix'||NEW.id WHERE id=NEW.id;
END;

INSERT INTO A (id) VALUES (NULL);
INSERT INTO A (id) VALUES (NULL);
INSERT INTO A (id) VALUES (NULL);
...

Is that a right solution?

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Roman Fleysher [roman.fleys...@einstein.yu.edu]
Sent: Friday, January 26, 2018 4:43 PM
To: General Discussion of SQLite Database
Subject: [sqlite] primary key in another column

Dear SQLiters,

I would like to use primary key as a way to create unique column entry:

CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL)

such that file name is always prefix followed by the ID for the content to be:

ID  fileName

1   prefix_1
2   prefix_2


That is when I insert a row into the table, id is already auto generated by 
SQLite. I want the filename to be auto generated too. This idea looks strange 
to me because then I do not have to insert anything, everything will be auto 
filled:

INSERT INTO A;
INSERT INTO A;

 will insert first two rows.

Roman

___
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] primary key in another column

[Roman Fleysher]

Dear SQLiters,

I would like to use primary key as a way to create unique column entry:

CREATE TABLE A( id INTEGER PRIMARY KEY, fileName TEXT NOT NULL)

such that file name is always prefix followed by the ID for the content to be:

ID  fileName

1   prefix_1
2   prefix_2


That is when I insert a row into the table, id is already auto generated by 
SQLite. I want the filename to be auto generated too. This idea looks strange 
to me because then I do not have to insert anything, everything will be auto 
filled:

INSERT INTO A;
INSERT INTO A;

 will insert first two rows.

Roman

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

Re: [sqlite] Energy consumption of SQLite queries

[Roman Fleysher]

Dear Ali,

A couple of comments. Indeed lots of energy is transferred into heat, but not 
all. Therefore, using temperature (after calibrating specific heat coefficient 
of the device ) is not a good method. Some energy is radiated as visible and 
invisible light and hard to catch it all. Some as vibration. Some energy is 
used to flip the bits on the disk. So to speak internal energy. 

Thus, monitoring input power is the only way. However, since many jobs are 
running, the results will be indeed inconsistent. Some people, therefore, 
erroneously conclude that the question is not answerable. This is not true.

A properly crafted research proposal could get funding needed to accomplish 
this fine goal. I expect 1 million US dollars for 5 years should be close to 
sufficient. Make sure you measure how long SQLIte performs the task, record 
power consumption in that period. Then run machine for the same period without 
SQLIte. Difference in power consumption is what was due to  SQLite, controlling 
for the other processes. Obviously, caching and other things already mentioned, 
will affect the numbers. Thus, you need to properly randomize these trials, 
playing with their durations. You will have to perform many of these (therefore 
5 year long project) to average out all fluctuations. 

Given complexity of the project, you should consider getting initial funding to 
design it in the first place and obtain preliminary data (and necessary 
equipment) to justify and ensure future success. It appears, given your initial 
email, that such funding is well underway towards being secured. Be sure to 
control temperature and humanity in the room, because cooling fans also consume 
energy, which depends on their speed and viscosity of the air. The tidal forces 
(of the moon) will affect friction in bearings of all moving parts (fans, 
disks). Be sure to either co-vary for them or randomize experiments for 
different phases of the moon. DO NOT MOVE computer while experiment is running. 
Coriolis force will affect friction in all rotating parts as well.

In summary, this is a perfectly doable experiment, if carefully planned and 
executed. Radio astronomy easily reaches sensitivities of 10^{-9}. You can do 
it too!

At conclusion of the 5 year research period, SQLite will be much different from 
what it is today. So will kernels of operating systems, hardware etc. 
Therefore, at conclusion of the research, you will have answered how much power 
was consumed by SQLite 5 years ago. I am sure this will be very valuable piece 
of information then, after all the money and efforts are spent. Because of this 
short delay (5 years is short on the astronomical time scale) and because of 
the experience you gained by conclusion of the project, I am rather certain you 
will be able to obtain additional funding to continue and refine the answer to 
the newer version of SQLite available then. The future is in your hands!


Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Ali Dorri [alidorri...@gmail.com]
Sent: Tuesday, November 21, 2017 4:49 PM
To: Robert Oeffner
Cc: SQLite mailing list
Subject: Re: [sqlite] Energy consumption of SQLite queries

Dear All,

Thanks for your comments. That was really helpful.

Regards
Ali

On Tue, Nov 21, 2017 at 11:41 PM, Robert Oeffner  wrote:

> This is an interesting topic more belonging to the realms of information
> theory and statistical physics.
>
> I am not an expert in this area but from what I recall from undergraduate
> physics the moment you create order in one corner of the universe entropy
> rises in another place of the universe. If you loosely speaking equate
> information gathering such as an SQL query as creating order then that must
> have a cost in terms of increasing the entropy (heat in this case)
> elsewhere. There is a lower bound on how little entropy is generated during
> this process which comes down to the efficiency of the process (hardware
> and software in your case).
>
> One could get philosophical here and question whether mankinds computer
> modeling of climate change in itself causes the excess heat leading to
> global warming.
>
>
> Regards,
>
> Robert
>
>
> --
> Robert Oeffner, Ph.D.
> Research Associate,
> The Read Group, Department of Haematology,
> Cambridge Institute for Medical Research
> University of Cambridge
> Cambridge Biomedical Campus
> Wellcome Trust/MRC Building
> Hills Road
> Cambridge CB2 0XY
> www.cimr.cam.ac.uk/investigators/read/index.html
>
>
>
> Date: Tue, 21 Nov 2017 09:54:25 +1100
>> From: Ali Dorri 
>> To: SQLite mailing list 
>> Subject: [sqlite] Energy consumption of SQLite queries
>> Message-ID:
>> 

Re: [sqlite] XOR operator

[Roman Fleysher]

The point is that terminology is chosen for a reason and can not be dismissed. 
"Flexibly typed" means it is typed. It means SQLite knows how many bytes: 
without knowing it would not be able to establish equality "IS".  Flexibly 
means columns can contain values of mixed types,  but each value still has a 
type. And this is a very very big advantage of SQLite.

Perhaps longer term is "flexibly strongly typed". Perhaps because "typed" 
implies "strongly" (what is a weak type?), strongly is redundant.

Roman




 Original message 
From: R Smith 
Date: 10/8/17 9:38 AM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] XOR operator

On 2017/10/06 6:03 PM, Richard Hipp wrote:
> On 10/6/17, R Smith  wrote:
>> I'd also like to see a Unary NOT operator, such that you can say: a = !b
> In SQL and SQLite that would be:  a = NOT b

Apologies, I thought it obvious from the context that I meant a binary
operation, not a Boolean operation NOT.

i.e. 0xA (base16) = 1010 (base2) so that NOT 0xA = 0101 = 0x5... so if a
= 0xA then !a = 0x5, but that only works IF we are restricted to "a"
being 1 byte in size, which brings us to the following point:

>
>> But, I guess that's only feasible in a strongly typed language.
> (1) I object to the characterization of SQLite not being "strongly
> typed".  SQLite is "flexibly typed" in the sense that it provides the
> application with a lot of flexibility with regard to what datatypes
> are allowed to be stored in a particular column or participate in an
> operation.  Other SQL database engines are "rigidly typed".  Those
> other SQL implementations are much more judgmental about what you can
> and cannot do with your data.
>
> (2) Why is rigid typing required in order to implement boolean negation?

Answering (2): A strongly typed language that defines
INT/UINT/WORD/INT64/etc. as specifically a 32-bit or 64-bit
signed/unsigned representation, or "Byte" as a 8-bit unsigned
representation will be sensible to say a = not b; where a and b are both
typed as BYTE values. but if you don't know how many bits are "meant" to
be in "a", how to determine how many bits must be negated / "notted" /
changed to produce the result of "NOT b" in the way described up there.

If for example a = 0xA then !a might be 0x5 for a nibble, but it will be
0xF5 for a byte, 0xFFF5 for a WORD, 0xFF5 for a 32bit INT, etc. etc.

It's often used in masking bit flag sequences. a = (a & !0x3) would see
"a" being switched so that it's LSB's 0 and 1 gets switched off while
leaving the others in tact. Yes, I could have just said a = (a & (0xFF -
0x03)) or even work out what that result is and go a = (a & 0xFC), but
if the bits that get switched off lives in a variable (b), then a = (a &
!b) is just so much more sensible / elegant. I'm even ok with syntax
like a = (a & (not b))... but that's not how SQLite works, or can work,
unless it becomes strongly typed.


As to (1)... Cool, call it flexibly typed then, I'm ambivalent to the
terminology, my point is about the variable sizes not being set in stone.


___
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] PRAGMA optimize; == no busy handler?

[Roman Fleysher]

Dear SQLiters,

Vacuuming seems to belong to a different thread, but let me say that it is not 
always warranted. Vacuuming may change/reassign ROWIDs. If you have two 
databases (backup and production?) that used to be linked via such a key, it 
will break.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Jens Alfke [j...@mooseyard.com]
Sent: Wednesday, October 04, 2017 6:29 PM
To: SQLite mailing list
Subject: Re: [sqlite] PRAGMA optimize; == no busy handler?

> On Oct 4, 2017, at 10:30 AM, Richard Hipp  wrote:
>
> The PRAGMA optimize command is our effort to move further in the
> direction of a "smart" SQLite that always automatically "does the
> right thing" with respect to gathering and using database statistics.

That’s a great move. Along the same lines, it would be nice if SQLite could 
vacuum once in a while without being asked (like a good housemate ;) What I’m 
doing now is based on some advice I read in a blog post*:

// If this fraction of the database is composed of free pages, vacuum it
static const float kVacuumFractionThreshold = 0.25;
// If the database has many bytes of free space, vacuum it
static const int64_t kVacuumSizeThreshold = 50 * MB;

// After creating a new database:
exec("PRAGMA auto_vacuum=incremental”);

// Just before closing a database:
exec("PRAGMA optimize");
int64_t pageCount = intQuery("PRAGMA page_count");
int64_t freePages = intQuery("PRAGMA freelist_count");
if ((pageCount > 0 && (float)freePages / pageCount >= 
kVacuumFractionThreshold)
|| (freePages * kPageSize >= kVacuumSizeThreshold)) {
exec("PRAGMA incremental_vacuum");
}

(To forestall any retorts that “you don’t need to vacuum because SQLite will 
reuse the free space later”: Yes, you do need to, on a space-constrained device 
like a phone. Otherwise your app never reclaims any storage back to the OS for 
use by other apps, and you get customer issues like “I deleted all my old junk 
from the app but it’s still using 10GB of storage, please help my phone is out 
of space”…)

—Jens

* https://blogs.gnome.org/jnelson/2015/01/06/sqlite-vacuum-and-auto_vacuum/
___
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] Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers

[Roman Fleysher]

Dear Richard,
Dear SQLiters,

This is not clear to me as well.

If I have two databases, db1 and db2. Both have table t. db1.t and bd2.t. I 
want to create a TEMPORARY trigger that upon insert in db1.t does something 
with db2.t.  Because:

TEMP triggers are not subject to the same-database rule. A TEMP trigger is 
allowed to query or modify any table in any ATTACH-ed database.

I need to be able to specify db2.t in the body of the trigger. But this is not 
allowed, right? Then how could it work? I mean it does not. Should it work?

Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Friday, June 09, 2017 9:23 AM
To: SQLite mailing list
Subject: Re: [sqlite] Syntax Restrictions On UPDATE, DELETE, and INSERT 
Statements Within Triggers

On 6/9/17, Mark Brand  wrote:
>
>
> On 09/06/17 14:47, Richard Hipp wrote:
>> The documentation has been updated to clarify the ambiguity and to
>> hopefully make it easier to understand.
>
> Thanks. The exception for non-TEMP triggers is something I was hoping
> for too:
>
>> For non-TEMP triggers, the table to be modified or queried must exist
>> in the same database as the table or view to which the trigger is
>> attached. TEMP triggers are not subject to the same-database rule. A
>> TEMP trigger is allowed to query or modify any table in any ATTACH
>> -ed database.
>
> But it doesn't seem to work this way yet. (3.19.3). Is this a coming change?

The . syntax restriction (the fact that you can only
have  and not . instead of triggers) is
universal.  It applies to both TEMP trigger and non-TEMP triggers.

Only the semantic rule that the referenced  must be within the
 schema is relaxed for TEMP triggers.

>
>
> create table t (x);
> insert into t values (0);
> create temp view v as select * from t;
>
> create temp trigger trg1 instead of update on temp.v
> begin
>  update temp.t set x = new.x;
> end;
>
> Error: near line 5: qualified table names are not allowed on INSERT,
> UPDATE, and DELETE statements within triggers
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] transfer records with foreign key

[Roman Fleysher]

Dear SQLiters,

I have two tables linked by a foreign key, linkID. I need to transfer content 
of these two tables into two corresponding tables in another database 
preserving the link. However, the second database already has records and 
numeric value of linkID can not be preserved. Nor its value is important 
because it simply links the tables. How do I INSERT into two tables, preserving 
the link while allowing for value of linkID to change. LinkID is defined as 
INTEGER PRIMARY KEY.

It looks to me that I should create a temporary view on the both tables in the 
second database and try to use INSTEAD OF trigger. Is that the right way? Or I 
have to create a temp table that maps old linkID to new linkID, somehow.

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

Re: [sqlite] command shell .timeout

[Roman Fleysher]

Thank you, Richard.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Thursday, August 03, 2017 3:38 PM
To: SQLite mailing list
Subject: Re: [sqlite] command shell .timeout

On 8/3/17, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
> Dear SQLiters,
>
> I am using sqlit3 command shell. It has ".timeout" command. What is the
> difference between:
>
> .timeout MS
> PRAGMA busy_timeout = milliseconds;

They accomplish the same thing.  The ".timeout" command (which I had
forgotten about) existed long before the "PRAGMA busy_timeout" command
was created.  Both commands invoke sqlite3_busy_timeout() for you.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] command shell .timeout

[Roman Fleysher]

Dear SQLiters,

I am using sqlit3 command shell. It has ".timeout" command. What is the 
difference between:

.timeout MS
PRAGMA busy_timeout = milliseconds;

I am getting "database is locked" when accessing the same file from multiple 
concurrent shells and trying to set timeouts to avoid this.

(By the way, PRAGMA busy_timeout = milliseconds; prints new timeout to screen. 
I would expect printing only if new value is not provided.)

Thank you

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

Re: [sqlite] rowid as foreign key

[Roman Fleysher]

Thank you, Keith. I realize this. I have a (parent) table that does not 
explicitly define primary key --- a unique identifier of the row. I need to 
temporarily create a child table that "extends" the row, adds columns to it. I 
wanted to use foreign keys on internal rowid column to link them. Perhaps I 
will use ALTER TABLE mechanism.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Keith Medcalf [kmedc...@dessus.com]
Sent: Monday, July 24, 2017 6:58 PM
To: SQLite mailing list
Subject: Re: [sqlite] rowid as foreign key

Yes, but to use the rowid or a table in a foreign key declaration you need to 
specify it explicitly in the table definition declaring it as the INTEGER 
PRIMARY KEY.  (and exactly INTEGER PRIMARY KEY, although you can also add the 
AUTOINCREMENT keyword if you need to ensure unique values for some reason).

eg:  CREATE TABLE test (rowid INTEGER PRIMARY KEY, data text);

You need to do this because if you do not then the rowid is merely an internal 
identifier for the row in the underlying table b-tree structure and is not 
"stable" across vacuum or dump/load operations.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Roman Fleysher
>Sent: Monday, 24 July, 2017 12:58
>To: General Discussion of SQLite Database
>Subject: [sqlite] rowid as foreign key
>
>Dear SQLiters,
>
>Is it possible to link two tables using rowid, the implicit column? I
>tried and it did not work, so I presume the answer to my question is
>"no".
>
>Thank you,
>
>Roman
>___
>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] rowid as foreign key

[Roman Fleysher]

I am sorry, I did not ask the question correctly. I omitted "... link two 
tables, using foreign key...". 

I now see last sentence on http://sqlite.org/lang_createtable.html which states 
that it is not possible.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Gwendal Roué [gwendal.r...@gmail.com]
Sent: Monday, July 24, 2017 3:17 PM
To: SQLite mailing list
Subject: Re: [sqlite] rowid as foreign key

> Le 24 juil. 2017 à 20:58, Roman Fleysher <roman.fleys...@einstein.yu.edu> a 
> écrit :
>
> Dear SQLiters,
>
> Is it possible to link two tables using rowid, the implicit column? I tried 
> and it did not work, so I presume the answer to my question is "no".

Hello Roman,

The answer is yes. For example :

$ sqlite3 /tmp/db.sqlite
sqlite> CREATE TABLE foo (c);
sqlite> CREATE TABLE bar (c);
sqlite> INSERT INTO foo (c) VALUES ('foo');
sqlite> INSERT INTO bar (c) VALUES ('bar');
sqlite> SELECT foo.rowid, foo.c, bar.rowid, bar.c FROM foo, bar WHERE foo.rowid 
= bar.rowid;
1|foo|1|bar

Gwendal Roué

___
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] rowid as foreign key

[Roman Fleysher]

Dear SQLiters,

Is it possible to link two tables using rowid, the implicit column? I tried and 
it did not work, so I presume the answer to my question is "no".

Thank you,

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

Re: [sqlite] SAVEPOINT with multiple databases

[Roman Fleysher]

Thank you, David. Now it totally makes sense to me. I realize this is SQL not 
SQLite question. 

Thank you,

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
David Raymond [david.raym...@tomtom.com]
Sent: Wednesday, May 17, 2017 12:38 PM
To: SQLite mailing list
Subject: Re: [sqlite] SAVEPOINT with multiple databases

One other minor thing of note is that attaching and detaching can only happen 
outside of a transaction. So you can't add or remove attached databases in the 
middle of a transaction, and transaction commit/rollback, or savepoint 
release/rollback will never leave you with a different set of attached 
databases than before that statement.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Roman Fleysher
Sent: Wednesday, May 17, 2017 12:25 AM
To: SQLite mailing list
Subject: Re: [sqlite] SAVEPOINT with multiple databases

Oh,  now i understand. Thank you,  Simon.


Roman



 Original message 
From: Simon Slavin <slav...@bigfraud.org>
Date: 5/16/17 5:35 PM (GMT-05:00)
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] SAVEPOINT with multiple databases


On 16 May 2017, at 10:09pm, Roman Fleysher <roman.fleys...@einstein.yu.edu> 
wrote:

> I think I came to a point where I need to learn SAVEPOINTs.
>
> I am trying to understand documentation if creation and release of save 
> points covers all presently attached databases, that is those before save 
> point is created? Is attaching a database just a command that will sit on the 
> transaction stack as any other, INSERT/UPDATE/DELETE?

It doesn’t matter when a database is attached to a connection.  A savepoint is 
something which affects the current transaction for a connection, which is 
comprised of the INSERT/UPDATE/DELETE operations you mentioned.

One SQLite connection:
Has access to at least one database, maybe more.
Has none or one current transaction.

One transaction:
Has zero or more savepoints.

Simon.
___
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] SAVEPOINT with multiple databases

[Roman Fleysher]

Oh,  now i understand. Thank you,  Simon.


Roman



 Original message 
From: Simon Slavin <slav...@bigfraud.org>
Date: 5/16/17 5:35 PM (GMT-05:00)
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] SAVEPOINT with multiple databases


On 16 May 2017, at 10:09pm, Roman Fleysher <roman.fleys...@einstein.yu.edu> 
wrote:

> I think I came to a point where I need to learn SAVEPOINTs.
>
> I am trying to understand documentation if creation and release of save 
> points covers all presently attached databases, that is those before save 
> point is created? Is attaching a database just a command that will sit on the 
> transaction stack as any other, INSERT/UPDATE/DELETE?

It doesn’t matter when a database is attached to a connection.  A savepoint is 
something which affects the current transaction for a connection, which is 
comprised of the INSERT/UPDATE/DELETE operations you mentioned.

One SQLite connection:
Has access to at least one database, maybe more.
Has none or one current transaction.

One transaction:
Has zero or more savepoints.

Simon.
___
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] SAVEPOINT with multiple databases

[Roman Fleysher]

Dear SQLiters,

I think I came to a point where I need to learn SAVEPOINTs.

I am trying to understand documentation if creation and release of save points 
covers all presently attached databases, that is those before save point is 
created? Is attaching a database just a command that will sit on the 
transaction stack as any other, INSERT/UPDATE/DELETE?

Thank you,

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

Re: [sqlite] WITH inside trigger in 3.16.2

[Roman Fleysher]

My apology, I can not read. http://sqlite.org/lang_createtrigger.html clearly 
states that CTE is not supported in triggers.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Roman Fleysher [roman.fleys...@einstein.yu.edu]
Sent: Tuesday, May 09, 2017 4:59 PM
To: General Discussion of SQLite Database
Subject: [sqlite] WITH inside trigger in 3.16.2

[This sender failed our fraud detection checks and may not be who they appear 
to be. Learn about spoofing at http://aka.ms/LearnAboutSpoofing]

Dear SQLiters,

I am trying to create a trigger with body:

WITH ...
DELETE FROM ...

and it does not seem to work (Error: near "DELETE": syntax error). But I can 
execute the body itself without errors.

Does it mean that WITH clause is not supported within trigger? I use SQLite 
version 3.16.22.

Thank you,

Roman


___
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] WITH inside trigger in 3.16.2

[Roman Fleysher]

Dear SQLiters,

I am trying to create a trigger with body:

WITH ...
DELETE FROM ...

and it does not seem to work (Error: near "DELETE": syntax error). But I can 
execute the body itself without errors.

Does it mean that WITH clause is not supported within trigger? I use SQLite 
version 3.16.22.

Thank you,

Roman


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

Re: [sqlite] Developing a SQLite3 DB remotely

[Roman Fleysher]

No. I was not aware of these tools. Are any of them good? Maintained?

I am mostly using sqlite3 shell from bash scripts. Do you know if some of them 
are suitable replacements?

Is this off the topic of the  original  question?

Thank you,

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Joshua J. Kugler [jos...@azariah.com]
Sent: Thursday, March 23, 2017 6:16 PM
To: SQLite mailing list
Subject: Re: [sqlite] Developing a SQLite3 DB remotely

Are you aware options for true network access?

https://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork

Just thought I'd throw that out there.

j

--
Joshua J. Kugler - Fairbanks, Alaska
Azariah Enterprises - Programming and Website Design
jos...@azariah.com - Jabber: pedah...@gmail.com
PGP Key: http://pgp.mit.edu/  ID 0x73B13B6A
___
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] Developing a SQLite3 DB remotely

[Roman Fleysher]

I do not have big experience in the area, but have some.

I think that light weight use is not the right thing to ask. I have seen NFS 
delays of 20 seconds: file was created on one machine and showed up on another 
after 20 seconds. This depends on how heavy OTHER things are, not how heavy 
SQLite access is.

GFS2 and GPFS supposedly solve file synchronization issue (by sharing disk 
inodes rather than files ). I never tested this (we have GPFS) and do not know 
about other file systems.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Simon Slavin [slav...@bigfraud.org]
Sent: Thursday, March 23, 2017 2:14 PM
To: SQLite mailing list
Subject: Re: [sqlite] Developing a SQLite3 DB remotely

On 23 Mar 2017, at 5:45pm, Stephen Chrzanowski  wrote:

> The remote
> system is a Linux based OS. […]

How 'remote' is this ?  What protocol is used to do the remote access ?

> What would be a recommended way to setup the
> connections for a DEV-only arena where the below paragraph describes?

… or is that what you’re asking for advice on here ?

> By EXTREMELY LIGHT WEIGHT use, I mean I *DO* guarantee that although I have
> one permanent open file handle to the database via SEP, and that Linux OS
> will only open a handle  periodically while I'm writing the script,
> multiple accesses of reading or writing to the DB at the exact same time
> just will not happen.

Set a timeout of at least 10 seconds on all connections to the database.  Apart 
from that I can’t think of anything you haven’t mentioned.  I do more 
complicated things by using SQLite as a back end to a web-facing system without 
problems.

Simon.
___
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] 3.17.0 does not read updated DB

[Roman Fleysher]

My mistake: I do not update DB. I rename (unix mv) the DB.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Roman Fleysher [roman.fleys...@einstein.yu.edu]
Sent: Thursday, February 16, 2017 12:33 AM
To: General Discussion of SQLite Database
Subject: [sqlite] 3.17.0 does not read updated DB

Dear SQLiters,

Is it possible that sqlite3 version 3.17.0 (command shell) does not pick up 
updated content of a DB file? I updated DB while it was attached in sqlite3 and 
SELECT did not produce new results. Instead it printed the same output as 
before DB file was updated.

Update was performed using sqlite3 version 3.8.11 (also command shell). I seem 
to be able to replicate that.

Roman
___
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] 3.17.0 does not read updated DB

[Roman Fleysher]

Dear SQLiters,

Is it possible that sqlite3 version 3.17.0 (command shell) does not pick up 
updated content of a DB file? I updated DB while it was attached in sqlite3 and 
SELECT did not produce new results. Instead it printed the same output as 
before DB file was updated.

Update was performed using sqlite3 version 3.8.11 (also command shell). I seem 
to be able to replicate that.

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

Re: [sqlite] extension to run bash

[Roman Fleysher]

I am not even sure myself this is the right path.

I have table with file names and need operations to be performed on columns  
(i.e. on files). Results, numeric or new file names,  are to be recorded in a 
column. I see two ways:

From bash script, make list of rows, run commands, load results to sqlite. Or

From sqlite, use extension to run commands for each row.

Both have issues.

Roman




 Original message 
From: Richard Hipp 
Date: 1/11/17 7:23 PM (GMT-05:00)
To: SQLite mailing list 
Subject: Re: [sqlite] extension to run bash

On 1/11/17, Bob Friesenhahn  wrote:
>
> For anyone thinking that it is a good idea to embed shell
> functionality in the SQL interpreter, it makes the SQL interpreter
> much less secure for untrusted inputs.

Nobody is suggesting that this become a standard feature of the
language.  Roman wants a "loadable extension", a separate shared
library that will only be used by Roman himself, and then under
carefully controlled circumstances.
--
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] extension to run bash

[Roman Fleysher]

I know it is not magic. It is SQLite developers' brain and effort. But it looks 
like magic to me.

Yes, this is example I see how I could use.

I am still thinking if this is what I really need. I am working with images and 
need, among other things, some summary measures to be placed into tables for 
analysis.

Thank you,

Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 11, 2017 4:44 PM
To: SQLite mailing list
Subject: Re: [sqlite] extension to run bash

On 1/11/17, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
> Yes, Richard, this is exactly what I mean.
>

Then maybe use the https://www.sqlite.org/src/file/ext/misc/rot13.c
extension as a prototype from which to develop yours.

--
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] extension to run bash

[Roman Fleysher]

Yes, Richard, this is exactly what I mean.

Roman

From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 11, 2017 4:34 PM
To: SQLite mailing list
Subject: Re: [sqlite] extension to run bash

On 1/11/17, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote:
> Dear SQLites,
>
> I am using exclusively sqlite3 shell for all the processing and may need
> ability to run bash commands and assign result to a column. For example:
>
> UPDATE  result SET nRows =` wc -l fileNames` ;
>
> Here I used `` as would be in bash for command substitution. This would run
> wc command (word count), count number of lines in each file listed in column
> fileNames and update the row correspondingly.
>
> As far as I understand I should be able to write loadable extension to
> accomplish this.

No, You cannot do exactly what you describe with a loadable extension.

But you could, perhaps, create a loadable extension that implements a
new system() SQL function like this:

   UPDATE result SET nRows = system('wc -l ' || fileNames);

Note that || is the SQL string concatenation operator.  You didn't
say, but I'm guessing that fileNames is a column in the result table.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] extension to run bash

[Roman Fleysher]

Dear SQLites,

I am using exclusively sqlite3 shell for all the processing and may need 
ability to run bash commands and assign result to a column. For example:

UPDATE  result SET nRows =` wc -l fileNames` ;

Here I used `` as would be in bash for command substitution. This would run wc 
command (word count), count number of lines in each file listed in column 
fileNames and update the row correspondingly.

As far as I understand I should be able to write loadable extension to 
accomplish this. My questions are:

1. Given that I use sqlite3 shell exclusively, does this path makes sense? If 
path should be different, what is it?
2. If loadable extension is good way to go, is there an example that I could 
use given that I have zero knowledge of sqlite's internals?

3. Maybe mixing SQL and shell commands (different syntaxes) is linguistically 
inappropriate and thus difficult?

Thank you,

Roman


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

Re: [sqlite] SQLite binary with Math Functions for OS-X?

[Roman Fleysher]

Can't you count how many rows there are and then sort by the variable of 
interest, limiting output to half the count, all within SQL?

Roman



Sent from my T-Mobile 4G LTE Device


 Original message 
From: Ronald Gombach 
Date: 11/21/16 7:12 AM (GMT-05:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] SQLite binary with Math Functions for OS-X?

Is there a pre-c binary of SQLIte available for down load that includes a math 
library. I particularly need the “median” function.

If not, can someone point me to instructions on compilation command line to 
include the math library (OS-X).

Thanks for any info you can share.

Ron Gombach
ron...@gombach.com 
The Gombach Group
Morrisville, PA
215-295-6555

___
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] replace "\n" with nothing

[Roman Fleysher]

Thank you! Worked!

Roman

From: sqlite-users-boun...@mailinglists.sqlite.org 
[sqlite-users-boun...@mailinglists.sqlite.org] on behalf of R Smith 
[rsm...@rsweb.co.za]
Sent: Tuesday, July 05, 2016 3:13 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] replace "\n" with nothing

replace(columname, char(10), '');

Sometimes, depending on your OS's interpretation of '\n', it might
actually be char(13)+char(10) or such (that's hex 0x0D and 0x0A). Get
the HEX() from such a line to be sure.


On 2016/07/05 9:00 PM, Roman Fleysher wrote:
> Dear SQLiters,
>
> I made a mistake and inserted a new line char, "\n" in the middle of a text. 
> I now would like to replace it with nothing. Something like:
>
> replace(columnName, '\n','')
>
> But this will interpret "\n" literally, as two symbols. How do I do it?
>
> Thank you,
>
> Roman
> ___
> 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] replace "\n" with nothing

[Roman Fleysher]

Dear SQLiters,

I made a mistake and inserted a new line char, "\n" in the middle of a text. I 
now would like to replace it with nothing. Something like:

replace(columnName, '\n','')

But this will interpret "\n" literally, as two symbols. How do I do it?

Thank you,

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

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Roman Fleysher]

Dear Richard,
Dear SQLiters,

Thank you, Simon, for sending the link. I would like to offer several comments 
on the podcast. 

1. Why SQLite is popular.

Instead of describing how I selected SQLite to solve our DB needs, I will 
recount story of Sony, its introduction of transistor radio that I read in 
Innovator's Dilemma by Clayton Christensen. (Very good book and author, I 
recommend.)

First transistor radios were poor in sound quality compared to those based on 
vacuum tubes. But they were lite (misspelled intentionally), and small. They 
were bought by teenagers, because they were cheap and portable. The big radio 
manufacturers did not even consider transistor radios as competitors because 
traditional competition was based on sound quality, not portability. Over the 
years, transistor technology improved and all vacuum radio manufacturers 
disappeared.

Richard said: "We do not compete against Oracle, we compete against fopen()." 
This is true, just like transistors. But SQLite displaced many big DBs and now 
Oracle etc have smaller market share. If I apply ideas of Innovator's Dilemma, 
their market share will continue to shrink. (I am not an MBA, I am a physicist, 
could be wrong but looks reasonable.)

2. Job to do

This is related to 1, and to ideas I read in Clayton Christensen books. 

Many SQL databases are very similar in what they can do, performance etc. Thus 
SQLite wins, just like Sony's first transistors, because it does NOT compete 
with them. It can not handle huge write concurrency or optimize for similar 
requests over history. Instead, it is easy to install and use. Its column 
types, affinity, makes SQLite suitable for both relational and 
entity?attribute?value models. 

It turns out that many "customers" simply do not need the functionality and 
optimization offered by big DBs. Instead, like teenagers, they need 
portability, ease of use and set up. This solves the job. Big DBs are overkill 
for such "small" jobs, requiring a lot of learning and expense. But there are a 
LOT of these small jobs and SQLite solves them admirably.

3. Code rewrite, robustness, licensing

Code rewrite or static linking make the final product more robust. Robustness 
simplifies support and debugging. Robustness attracts users. We all want OUR 
thing to work and if our thing depends on SQLite, we want SQLite to be robust. 
And thus, the SQLite licensing.

4. Fossil and other in-house software

Writing your own code is driven by the lack of needed features in available 
products. In the beginning, Ford had to build its own metallurgy plant to 
ensure quality of metal. This and 3 above are integration of what is not good 
enough to make it good together. Over the years, metallurgy industry matured 
and Ford closed this division. 

There are many other aspects in the podcast that I would like to comment. Even 
when Richard tells the story and many elements look accidental, they all fit 
into the timeline of unfolding disruptive innovation. 

SQLite was and is a disruptive innovation. SQLite is not a toy. 

Thank you for making it.


Roman



From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Saturday, May 14, 2016 4:17 PM
To: SQLite mailing list
Subject: [sqlite] Podcast with Dr Hipp: SQLite history, success and funding

Those interested in SQLite might like to listen to



Play on the page or download as an MP3.

Unusual information on Dr Hipp's early career, SQLite history, HWACI, and how 
come SQLite is free but the developers still manage to afford food and 
somewhere to sleep.

Question to ponder before you listen: Many of you know about tiny devices which 
incorporate SQLite but what do you think the biggest one is ?

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

[sqlite] datetime in CHECK

[Roman Fleysher]

Thank you. I did not notice loss of fractional seconds. I now see in the manual 
that datetime()  is equivalent to strftime('%Y-%m-%d %H:%M:%S', ...), with 
capital "S" rather than lower "f" at the end that I expected.

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Cecil Westerhof [cldwester...@gmail.com]
Sent: Wednesday, May 04, 2016 5:37 PM
To: SQLite mailing list
Subject: Re: [sqlite] datetime in CHECK

2016-05-04 22:43 GMT+02:00 Roman Fleysher :

> Dear SQLiters,
>
> I am trying to use CHECK constraint is column of a table to enforce
> datetime format and this works:
>
> AcquisitionDateTEXT
> ??
> CHECK (AcquisitionDate IS date(AcquisitionDate))
>
> when I insert '2015-08-10'. But this
>
> AcquisitionDateTime  TEXT CHECK (AcquisitionDateTime IS
> datetime(AcquisitionDateTime))
>
> when I insert '2015-08-10T17:19:37.670' or '2015-08-10 17:19:37.670'
> fails. Why?


?That is because datetime is not more precise as seconds. When you execute:
SELECT datetime('2015-08-10T17:19:37.670')
you get:
2015-08-10 17:19:37


How to do it properly?
>

?Do not enter the part after seconds (.670).

If that is important, executing:
SELECT strftime('%Y-%m-%d %H:%M:%f', '2015-08-10T17:19:37.670')
gives:
2015-08-10 17:19:37.670

So you could use:
?

??CHECK (AcquisitionDate IS strftime('%Y-%m-%d %H:%M:%f',
AcquisitionDate))?

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

[sqlite] datetime in CHECK

[Roman Fleysher]

Dear SQLiters,

I am trying to use CHECK constraint is column of a table to enforce datetime 
format and this works:

AcquisitionDateTEXT CHECK (AcquisitionDate IS date(AcquisitionDate))

when I insert '2015-08-10'. But this

AcquisitionDateTime  TEXT CHECK (AcquisitionDateTime IS 
datetime(AcquisitionDateTime))

when I insert '2015-08-10T17:19:37.670' or '2015-08-10 17:19:37.670' fails. 
Why? How to do it properly?

Thank you,

Roman

[sqlite] bug in PRAGMA ignore_check_constraints?

[Roman Fleysher]

I just downloaded and tested using 3.8.11.1.  It and 3.8.8.3 have the same 
behavior -- do not disable.

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Thursday, September 10, 2015 7:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] bug in PRAGMA ignore_check_constraints?

On 9/10/15, Richard Hipp  wrote:
> On 9/10/15, Roman Fleysher  wrote:
>> Dear SQLiters,
>>
>> I am trying to temporarily disable CHECK constraint given in columns of
>> table definition. As far as I understand,
>>
>> PRAGMA ignore_check_constraints='yes';
>
> PRAGMA ignore_check_constraints=YES;  -- no quotes.
>

I say that - turns out quotes don't matter.  PRAGMA
ignore_check_constraints='yes'; works too.  What version of SQLite are
you running?

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

[Roman Fleysher]

Thank you Richard!

Roman

From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Thursday, September 10, 2015 7:50 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PRAGMA integrity_check

On 9/10/15, Roman Fleysher  wrote:
> Dear SQLiters,
>
> PRAGMA integrity_check is described to check UNIQUE and NOT NULL
> constraints. Does it check other CHECK constraints specified in the column
> definition?
>

Apparently it does not.  I thought it did.  I'll add that to my list
of things to do.
--
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] bug in PRAGMA ignore_check_constraints?

[Roman Fleysher]

Dear SQLiters,

I am trying to temporarily disable CHECK constraint given in columns of table 
definition. As far as I understand, 

PRAGMA ignore_check_constraints='yes';

should do it. However this example demonstrates that it is not:

CREATE TABLE subject(
  subjectID  INT,
  gender TEXT NOT NULL
);


CREATE TABLE input(
  subjectID  INT,
  gender TEXT
);

INSERT INTO input(subjectID, gender) VALUES (1, 'female');
INSERT INTO input(subjectID, gender) VALUES (2, 'male');
INSERT INTO input(subjectID) VALUES (3);

PRAGMA ignore_check_constraints='yes';
INSERT INTO subject (rowid) SELECT rowid FROM input;

Error: NOT NULL constraint failed: subject.gender

I am using SQLite 3.8.11.1. Am I misusing the PRAGMA?

Thank you,

Roman

[sqlite] PRAGMA integrity_check

[Roman Fleysher]

OK, Thank you.

Meanwhile (again) I check that PRAGMA integrity_check='yes' did not disable 
TEXT NOT NULL. Is that a bug in 3.8.8.3?

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Thursday, September 10, 2015 7:35 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PRAGMA integrity_check

On 11 Sep 2015, at 12:32am, Roman Fleysher  
wrote:

> Meanwhile, I tested if PRAGMA integrity_check checks column constraints. You 
> can bump up 90% of being sure it does not to 100%. It does not. Is there a 
> way to do it, other than export the data out and try to re-insert it?

I can't think of one.  I would argue that in SQLite4 (or some future version of 
SQLite3) "PRAGMA integrity_check" should check constraints as well as 
consistency.

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

[sqlite] PRAGMA integrity_check

[Roman Fleysher]

Thank you, Simon.

Meanwhile, I tested if PRAGMA integrity_check checks column constraints. You 
can bump up 90% of being sure it does not to 100%. It does not. Is there a way 
to do it, other than export the data out and try to re-insert it?

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Thursday, September 10, 2015 7:28 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PRAGMA integrity_check

On 11 Sep 2015, at 12:04am, Roman Fleysher  
wrote:

> I wanted to check the behavior and set up a test database. I use (for now) 
> SQLite 3.8.8.3 and discovered that setting ignore_check_constraints = 'yes' 
> did not disable INT PRIMARY KEY NOT NULL constraint on a column. Is that 
> expected?

Yeah.  That's expected.  The INT PRIMARY KEY is a special case.  It makes up 
its own number.  I forget what it does if you explicitly supply NULL as a value 
but it is a special case.

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

[sqlite] PRAGMA integrity_check

[Roman Fleysher]

Thank you, Simon. I saw that foreign keys must be checked separately. 

I wanted to check the behavior and set up a test database. I use (for now) 
SQLite 3.8.8.3 and discovered that setting ignore_check_constraints = 'yes' did 
not disable INT PRIMARY KEY NOT NULL constraint on a column. Is that expected?

Thank you,

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Thursday, September 10, 2015 6:39 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] PRAGMA integrity_check

On 10 Sep 2015, at 11:06pm, Roman Fleysher  
wrote:

> PRAGMA integrity_check is described to check UNIQUE and NOT NULL constraints. 
> Does it check other CHECK constraints specified in the column definition?

I'm 90% sure it does not.  You should see this one though:

<https://www.sqlite.org/pragma.html#pragma_foreign_key_check>

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

[sqlite] PRAGMA integrity_check

[Roman Fleysher]

Dear SQLiters,

PRAGMA integrity_check is described to check UNIQUE and NOT NULL constraints. 
Does it check other CHECK constraints specified in the column definition?

Thank you,

Roman

[sqlite] Thanks SQLite

[Roman Fleysher]

I dare to add my thanks here, with a much simpler example. Initially, for me, 
CTE was another thing to learn. Then I wanted SQLite to compute statistics 
on a simple two-column table. Not a big deal, I typed the equation and was 
done. Next day, I needed the same equation to be applied to two different 
columns, but these columns were to be produced from some other tables. Maybe 
there was a way to do it without, but I did it with WITH ... . Now, the 
equation stays put all the time and I change the WITH clause to redefine the 
two columns when I need equation to be applied to new data.

I was elated when I implemented first equation. This is like stored procedure.

Roman 



From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of R.Smith [rsm...@rsweb.co.za]
Sent: Wednesday, July 29, 2015 10:08 PM
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [sqlite] Thanks SQLite

On 2015-07-30 12:41 AM, Simon Slavin wrote:
> On 29 Jul 2015, at 11:32pm, Richard Hipp  wrote:
>
>> I'm looking for real-world (open-source) use cases for CTEs.
> I would like to see a demonstration of how non-recursive CTEs can be useful 
> in simplifying a SQL command.  Ideally in a plausible example rather than 
> something obviously made up with no real-world equivalent.  It seems that 
> everyone who mentions CTEs jumps straight to recursion, as if that's the only 
> thing they're useful for.

Well, it's hard to show a quick and simple example when the question
specifically presupposes complexity, but if you forgive the verbosity,
here is an example of non-recursive CTE which I don't think is even
possible without CTE (or pre-set-up temporary tables) in a very
real-World situation:

Basic manufacturing company has a data system comprising of stock items
and stockkeeping units (SKU's) aka manufactured products. The method of
turning stock into a manufactured product is commonly referred to as a Job.

Jobs have Bills of material (BOM) that basically is a stock item (sku)
with a parent-child relationship to several other stock items which it
"uses" or consumes to get made.

A stock item can belong to very many Bills of material, or can be used
outside of any (such as surgical gloves that makes part of the
consumables for a job, but isn't part of the manufactured product,
etc.). BOMs can be sub-BOMs of other BOMs, in the way that your car's
Engine is its own manufactured BOM unit but also a sub-unit of the Car's
total BOM. (Recursive CTE's help us a lot in this regard for working out
other things)

Now at any point, in your store there is an amount of stock that can be
used, but the amount of a specific finished product that can be produced
from it will vary widely according to BOM consumption figures. So let's
say you have 10 of Stock X1,  20 of X2 and  5 of X3, but the product you
want to make consumes 8 of each. You will need to buy 3 more X3 to be
able to make even 1 of this product.

That sounds simple enough, but consider that nobody will sell you 3 more
X3, those X3 items comes in minimum orders of 25 (we say MOQ for
minimum-order-quantity). So if I buy a pack of X3, I can now make one
product - but, I will now be left over with 2 of X1, 12 of X2 and 22 of
X3 - that is more stock than I started with!

Of course the MOQ for X1 and X2 are different, I might need to buy 30 of
X1's and 10 of X2's.  (There is also a thing called Pan-Size / Pack-Size
that dictates the minimum set to buy - you might need to buy a minimum
of 30, but after that you still need to buy sets of 6, so you can buy 36
or 42, but not 35 or 37, etc. I will ignore this bit for this example,
but note how the complexity grows).

Then, every item has a cost. Some items are very expensive, and some are
very cheap. I won't mind buying 50 extra of cheap item A as long as I
use up expensive item B completely.

One common question is: How much do I need to buy of everything so that
I can manufacture my products and have the least amount of wasted value
left over?.

A final complication to mention is that, we only really care about
balancing after a point - I mean it's all fine and dandy if we find that
making 7235 jobs will use up the stock so that there is zero of
everything left. We can't make that many jobs, we don't have the
capacity and even if we did, nobody will buy them all. In fact, for this
exercise, we don't really wish to make more than 20 jobs at any time.

By now I hopefully don't need to explain any more how this has become a
really complex problem to solve. To answer the question, some CTEs can
be called into service.

First, let's build a table with job counts in the range that we are
comfortable with (so 0 to 20 in this case).

WITH JCount(c) AS (
   SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 21
),


(Ignore the recursion here, it only builds a quick little table, it
isn't recursing the main dataset in any way - just another 

[sqlite] how to cite SQLite

[Roman Fleysher]

Thank you Simon and Richard. I will use this form.

What I also meant is that software often impements unique algorithms, which I 
think SQLite does. These algorithms may be presented at conferences/journals. 
In turn, these publications cite the URL. Thus, I was looking for such a 
presentation, if it exists. Obviously, software may also impement other 
algorithms developed by other people, not directly the software developers.  I 
would expect the "main" SQLite publication to mention them.

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Monday, July 13, 2015 2:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to cite SQLite

On 7/13/15, Simon Slavin  wrote:
>
> On 13 Jul 2015, at 4:51pm, Roman Fleysher 
> wrote:
>
>> I am writing a scientific paper to describe our research. To manage data,
>> we use SQLite. I would like to acknowledge SQLite and cite it properly in
>> the paper. Is there a suggested way of doing it? A conference
>> presentation? A paper, a book? In the simplest form I will use URL.
>
> Roman,
>
> SQLite must be cited as software rather than an academic presentation.  I'm
> afraid that the correct citation form for software will depend on the style
> guide of your publication.  I see you're working in medicine in the USA.
> The closest citation forms I know of for US medicine are as follows.  The
> long form conforms to the one used by the APA which, for SQLite, would be
> something like
>
> Hipp, R, et. al. (2015). SQLite (Version 3.8.10.2) [Computer software].
>   SQLite Development Team.  Retrieved June 15, 2015.
>   Available from <https://www.sqlite.org/download.html>

Hipp, D. R., Kennedy, D., Mistachkin, J., (2015) SQLite (Version 3.8.10.2)
[Computer software].  SQLite Development Team.  Retrieved 2015-06-15.
Available from <https://www.sqlite.org/src/info/2ef4f3a5b1d1d0c4>

Note that the "download.html" link is volatile and will change (to
version 3.8.11) in about 3 weeks, whereas the /src/info/2ef4f link is
persistent and is intended to be accessible for at least 35 years.

>
> You should substitute your own version and dates.  The other is the short
> form the AMA uses which, for SQLite, would be
>
> Hipp R et. al.. SQLite. North Carolina: SQLite Development Team; 2015.
>
> If you find the form of this which suits your publication better, please
> post it to this thread and I'll add it to the list.
>
> Richard, the 'author' requirements require the name of at least one specific
> person.  I don't know who else is on the development team so I've taken the
> liberty of using "Richard Hipp et. al." and "SQLite Development Team" which
> I guessed were closest to what you'd all want.  If you don't like them
> please don't hesitate to correct them.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
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] how to cite SQLite

[Roman Fleysher]

Thank you, Richard. I will.

Our research has nothing to do with databases or computer science. We use 
SQLite as pure users, to help manage our data analysis. Nevertheless, it made a 
huge difference for us already and more to come as we replace text-file 
based/manual management. I am very grateful to all SQLiters for making SQLite 
available.

Roman


From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Monday, July 13, 2015 11:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to cite SQLite

On 7/13/15, Roman Fleysher  wrote:
> Dear SQLiters,
>
> I am writing a scientific paper to describe our research. To manage data, we
> use SQLite. I would like to acknowledge SQLite and cite it properly in the
> paper. Is there a suggested way of doing it? A conference presentation? A
> paper, a book? In the simplest form I will use URL.
>

I think just the URL:  https://www.sqlite.org/

If possible, please provide a link to your paper when it becomes available.  :-)

--
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] how to cite SQLite

[Roman Fleysher]

Dear SQLiters,

I am writing a scientific paper to describe our research. To manage data, we 
use SQLite. I would like to acknowledge SQLite and cite it properly in the 
paper. Is there a suggested way of doing it? A conference presentation? A 
paper, a book? In the simplest form I will use URL.


Thank you,

Roman

[sqlite] index broken by insert

[Roman Fleysher]

Dear Richard, Simon, SQLiters,

I hope you did not take my comment to mean that my stupid solution outsmarts 
your smart solution. I wanted to say that my work is not delayed and that I am 
trying within the tools/knowledge I have to solve my problems.

Substantially, I would like to understand if CAST() modification that you 
propose should actually be the correct SQL code that I should adopt permanetly. 
We like implicit conversions, but as C++ is more strict than C with regard to 
casting, should it be my better practice to always explicitly cast?

Thank you,

Roman


From: Roman Fleysher
Sent: Tuesday, May 19, 2015 3:48 PM
To: General Discussion of SQLite Database
Subject: RE: [sqlite] index broken by insert

Dear Richard,
Dear Simon,
Dear SQLiters,

It is such a pleasure to deal with smart people. Pure joy. How quickly Simon 
figured out the problem and how Richard narrowed it down and fixing it.

Thank you for the work around. I implemented my own,  stupid,  work around: 
execute offending  insert using 5 year old version of sqlite that we have on 
our system.

How will i know the patch/new version is available? By checking ticket link?

Thank you,

Roman


Sent from my T-Mobile 4G LTE Device


 Original message 
From: Richard Hipp <d...@sqlite.org>
Date: 05/19/2015 3:37 PM (GMT-05:00)
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] index broken by insert

On 5/19/15, Roman Fleysher  wrote:
> CREATE TRIGGER demographicInsert AFTER INSERT ON subject FOR EACH ROW BEGIN
>   INSERT INTO Exam (subjectID, examID, examType) VALUES (NEW.subjectID,
> NEW.subjectID, 'demographicExam');

Your workaround is to change the insert into examID to cast it to type
TEXT.  Like this:

.. VALUES(New.subjectID, CAST(New.subjectID AS TEXT), 'demographicExam');

This should be automatic.  We are testing the bug fix now.  The
work-around is simply to get you going until we can get a patch
release out.

>   INSERT INTO wave (subjectID, examType, waveID, examID) VALUES
> (NEW.subjectID, 'demographicExam', 'time1', NEW.subjectID);
> END;
>

--
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] index broken by insert

[Roman Fleysher]

Dear Richard,
Dear Simon,
Dear SQLiters,

It is such a pleasure to deal with smart people. Pure joy. How quickly Simon 
figured out the problem and how Richard narrowed it down and fixing it.

Thank you for the work around. I implemented my own,  stupid,  work around: 
execute offending  insert using 5 year old version of sqlite that we have on 
our system.

How will i know the patch/new version is available? By checking ticket link?

Thank you,

Roman


Sent from my T-Mobile 4G LTE Device


 Original message 
From: Richard Hipp <d...@sqlite.org>
Date: 05/19/2015 3:37 PM (GMT-05:00)
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] index broken by insert

On 5/19/15, Roman Fleysher  wrote:
> CREATE TRIGGER demographicInsert AFTER INSERT ON subject FOR EACH ROW BEGIN
>   INSERT INTO Exam (subjectID, examID, examType) VALUES (NEW.subjectID,
> NEW.subjectID, 'demographicExam');

Your workaround is to change the insert into examID to cast it to type
TEXT.  Like this:

.. VALUES(New.subjectID, CAST(New.subjectID AS TEXT), 'demographicExam');

This should be automatic.  We are testing the bug fix now.  The
work-around is simply to get you going until we can get a patch
release out.

>   INSERT INTO wave (subjectID, examType, waveID, examID) VALUES
> (NEW.subjectID, 'demographicExam', 'time1', NEW.subjectID);
> END;
>

--
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] index broken by insert

[Roman Fleysher]

-- Dear SQLiters,

-- Here is schema first, table is below, 
-- followed by offending statement.
-- you can copy and paste the entire body
-- my comments are SQL compatible

-
-- STEP 1 --
-- create gender and handedness tables to fix possible values
-- then create subject table
-
CREATE TABLE gender(
  gender  TEXT PRIMARY KEY NOT NULL,
  description TEXT
);

INSERT OR IGNORE INTO gender (gender, description) VALUES ('Male', 'male 
gender');
INSERT OR IGNORE INTO gender (gender, description) VALUES ('Female', 'female 
gender');

CREATE TABLE handedness(
  handedness  TEXT PRIMARY KEY NOT NULL,
  description TEXT
);

-- force dob -mm-dd or NULL
CREATE TABLE subject(
  subjectID  INT PRIMARY KEY NOT NULL,
  dobTEXT CHECK (dob IS date(dob)),
  gender TEXT REFERENCES gender(gender) ON UPDATE CASCADE,
  race   TEXT,
  handedness TEXT REFERENCES handedness(handedness) ON UPDATE CASCADE
);


-- STEP 2 --
CREATE TABLE MRIMetric(
  metricName  TEXT PRIMARY KEY NOT NULL,
  description TEXT,
  units   TEXT
);


-- STEP 3 --
-- examType.examType = name of the table where
-- to find exam specifics
CREATE TABLE ExamType(
  examTypeTEXT PRIMARY KEY NOT NULL,
  description TEXT
);

INSERT OR IGNORE INTO ExamType (examType, description) VALUES ('MRIExam', 'any 
MRI exam');
INSERT OR IGNORE INTO ExamType (examType, description) VALUES 
('demographicExam', 'view on subject table');
INSERT OR IGNORE INTO ExamType (examType)  VALUES ('educationExam');

-- assume date -mm-dd 
CREATE TABLE Exam(
  examID  TEXT PRIMARY KEY NOT NULL,
  subjectID   INTEGER NOT NULL REFERENCES subject(subjectID) ON UPDATE CASCADE 
ON DELETE CASCADE,
  examTypeTEXT NOT NULL REFERENCES examType(examType) ON UPDATE CASCADE,
  commentsTEXT,
  dateTEXT CHECK (date IS date(date)),
  age INTEGER
);

-
-- create triggers to compute age on various updates and inserts
-
-- automatically compute age at the time of exam when exam is inserted
CREATE TRIGGER ageCalculatorOnExamInsert AFTER INSERT ON Exam FOR EACH ROW BEGIN
  UPDATE OR IGNORE Exam SET age = (
SELECT coalesce(
 strftime('%Y', NEW.date) - strftime('%Y', subject.dob) -1
  + (strftime('%m-%d', NEW.date) >= strftime('%m-%d', subject.dob)),
NEW.age)
FROM subject
WHERE subject.subjectID = NEW.subjectID
) -- this ends select statement that joins exam and subject tables
  WHERE Exam.examID = NEW.examID;
END;

-- automatically compute age at the time of exam when exam is updated
CREATE TRIGGER ageCalculatorOnExamUpdate AFTER UPDATE ON Exam FOR EACH ROW BEGIN
  UPDATE OR IGNORE Exam SET age = (
SELECT coalesce(
 strftime('%Y', NEW.date) - strftime('%Y', subject.dob) -1
  + (strftime('%m-%d', NEW.date) >= strftime('%m-%d', subject.dob)),
NEW.age)
FROM subject
WHERE subject.subjectID = NEW.subjectID
) -- this ends select statement that joins exam and subject tables
  WHERE Exam.examID = NEW.examID;
END;

-- automatically compute age at the time of exam when subject.DOB is updated
CREATE TRIGGER ageCalculatorOnSubjectUpdate AFTER UPDATE ON subject FOR EACH 
ROW BEGIN
  UPDATE OR IGNORE Exam SET age = (
SELECT coalesce(
 strftime('%Y', e.date) - strftime('%Y', NEW.dob) -1
  + (strftime('%m-%d', e.date) >= strftime('%m-%d', NEW.dob)),
e.age)
FROM Exam e
WHERE Exam.examID = e.examID
) -- this ends select statement that joins exam and subject tables
  WHERE Exam.subjectID = NEW.subjectID;
END;

-- STEP 4 --
CREATE TABLE MRIExam(
  examID   TEXT NOT NULL REFERENCES Exam(examID) ON UPDATE CASCADE ON 
DELETE CASCADE,
  ResearchName TEXT,
  MRN  INTEGER,
  pipeline TEXT,
  DICOMFolder  TEXT,
  PRIMARY KEY (examID)
);

CREATE TABLE MRIExamAccession(
  examID  TEXT NOT NULL REFERENCES MRIExam(examID) ON UPDATE CASCADE ON 
DELETE CASCADE,
  accession   INTEGER,
  UNIQUE (examID, accession)
);

CREATE INDEX MRIExamAccessionIndex ON MRIExamAccession(examID);

-- STEP 5 --
CREATE TABLE badMRIMetric(
  examID  TEXT NOT NULL REFERENCES MRIExam(examID) ON UPDATE CASCADE ON 
DELETE CASCADE,
  metricName  TEXT NOT NULL REFERENCES MRIMetric(metricName) ON UPDATE CASCADE,
  reason  TEXT, 
  PRIMARY KEY (examID, metricName)
);

-
-- STEP 6 --
-- create wave table
-
CREATE TABLE aboutWave(
  waveID  TEXT PRIMARY KEY NOT NULL,
  description TEXT
);

INSERT OR IGNORE INTO aboutWave (waveID, description) VALUES ('time1', 'first 
scan');

-- force uniqueness of subject+examType+wave.
-- can not have several instances of one exam type within a wave
-- given examID can be assigned to several waves
CREATE TABLE wave(
  subjectID   INT  NOT NULL REFERENCES 

[sqlite] index broken by insert

[Roman Fleysher]

Yes, I can provide full schema and data set. As far as I remember this mailing 
list does not accept attachments. Would that be OK to send in body of email? It 
is not that big.

Roman



From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Igor Korot [ikoro...@gmail.com]
Sent: Tuesday, May 19, 2015 1:35 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] index broken by insert

Hi, Roman,

On Tue, May 19, 2015 at 1:23 PM, Roman Fleysher
 wrote:
> "Confirmation" of a bug:
>
> When I use old SQLite version 3.7.2, the offending INSERT does NOT lead to 
> integrity failure. This is indicative of either bug in old integrity check or 
> new insert. Please let me know what useful info/contribution I can make.
>
> Another addition. The output of PRAGMA integrity_check is 31 lines (31 rows 
> are being inserted), all identical  with first and last reading:
>
> row 1 missing from index sqlite_autoindex_Exam_1
> ...
> row 31 missing from index sqlite_autoindex_Exam_1
>
> In addition, I have a trigger that updates Exam table when subject table is 
> updated (not on insert). I presume that trigger should not be triggered. Not 
> knowing what sqlite_autoindex_Exam_1 means, and seeing "Exam" I conclude the 
> relationship between my subject() and Exam() tables might be relevant for the 
> problem.

Can you post a complete schema of you database? What tables are made?
What indexes? Some test data to use?

Thank you.

>
> Roman
>
>
> 
> From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces 
> at mailinglists.sqlite.org] on behalf of Roman Fleysher [roman.fleysher at 
> einstein.yu.edu]
> Sent: Tuesday, May 19, 2015 12:25 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] index broken by insert
>
> Dear SQLiters,
>
> I do  not really know what info to provide for sufficient information. I use 
> SQLite shell only for all create/insert manipulations. This insert below 
> causes PRAGMA integrity_check; to report missing index (what appears to be on 
> every inserted row):
>
> SQLite version 3.8.8.3 2015-02-25 13:29:11
>
> PRAGMA foreign_keys=ON;
> ATTACH DATABASE 'demographics.sqlite' AS demo;
> INSERT OR IGNORE INTO subject(subjectID, dob, gender)
>SELECT subjectID, dob, gender FROM demo.demographics;
>
>
> The subject table is defined as:
>
> CREATE TABLE gender(
>   gender  TEXT PRIMARY KEY NOT NULL,
>   description TEXT
> );
>
> INSERT OR IGNORE INTO gender (gender, description) VALUES ('Male', 'male 
> gender');
> INSERT OR IGNORE INTO gender (gender, description) VALUES ('Female', 'female 
> gender');
>
> -- force dob -mm-dd or NULL
> CREATE TABLE subject(
>   subjectID  INT PRIMARY KEY NOT NULL,
>   dobTEXT CHECK (dob IS date(dob)),
>   gender TEXT REFERENCES gender(gender) ON UPDATE CASCADE,
>   race   TEXT,
>   handedness TEXT REFERENCES handedness(handedness) ON UPDATE CASCADE
> );
>
>
> Please let me know what other info might be useful to debug, including 
> debugging on my end! Integrity check on demographics.sqlite is OK.
>
> Thank you for your help,
>
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] index broken by insert

[Roman Fleysher]

"Confirmation" of a bug:

When I use old SQLite version 3.7.2, the offending INSERT does NOT lead to 
integrity failure. This is indicative of either bug in old integrity check or 
new insert. Please let me know what useful info/contribution I can make.

Another addition. The output of PRAGMA integrity_check is 31 lines (31 rows are 
being inserted), all identical  with first and last reading:

row 1 missing from index sqlite_autoindex_Exam_1
...
row 31 missing from index sqlite_autoindex_Exam_1

In addition, I have a trigger that updates Exam table when subject table is 
updated (not on insert). I presume that trigger should not be triggered. Not 
knowing what sqlite_autoindex_Exam_1 means, and seeing "Exam" I conclude the 
relationship between my subject() and Exam() tables might be relevant for the 
problem.

Roman



From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Roman Fleysher 
[roman.fleys...@einstein.yu.edu]
Sent: Tuesday, May 19, 2015 12:25 PM
To: General Discussion of SQLite Database
Subject: [sqlite] index broken by insert

Dear SQLiters,

I do  not really know what info to provide for sufficient information. I use 
SQLite shell only for all create/insert manipulations. This insert below causes 
PRAGMA integrity_check; to report missing index (what appears to be on every 
inserted row):

SQLite version 3.8.8.3 2015-02-25 13:29:11

PRAGMA foreign_keys=ON;
ATTACH DATABASE 'demographics.sqlite' AS demo;
INSERT OR IGNORE INTO subject(subjectID, dob, gender)
   SELECT subjectID, dob, gender FROM demo.demographics;


The subject table is defined as:

CREATE TABLE gender(
  gender  TEXT PRIMARY KEY NOT NULL,
  description TEXT
);

INSERT OR IGNORE INTO gender (gender, description) VALUES ('Male', 'male 
gender');
INSERT OR IGNORE INTO gender (gender, description) VALUES ('Female', 'female 
gender');

-- force dob -mm-dd or NULL
CREATE TABLE subject(
  subjectID  INT PRIMARY KEY NOT NULL,
  dobTEXT CHECK (dob IS date(dob)),
  gender TEXT REFERENCES gender(gender) ON UPDATE CASCADE,
  race   TEXT,
  handedness TEXT REFERENCES handedness(handedness) ON UPDATE CASCADE
);


Please let me know what other info might be useful to debug, including 
debugging on my end! Integrity check on demographics.sqlite is OK.

Thank you for your help,


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

[sqlite] index broken by insert

[Roman Fleysher]

Dear SQLiters,

I do  not really know what info to provide for sufficient information. I use 
SQLite shell only for all create/insert manipulations. This insert below causes 
PRAGMA integrity_check; to report missing index (what appears to be on every 
inserted row):

SQLite version 3.8.8.3 2015-02-25 13:29:11

PRAGMA foreign_keys=ON; 
ATTACH DATABASE 'demographics.sqlite' AS demo; 
INSERT OR IGNORE INTO subject(subjectID, dob, gender) 
   SELECT subjectID, dob, gender FROM demo.demographics;


The subject table is defined as:

CREATE TABLE gender(
  gender  TEXT PRIMARY KEY NOT NULL,
  description TEXT
);

INSERT OR IGNORE INTO gender (gender, description) VALUES ('Male', 'male 
gender');
INSERT OR IGNORE INTO gender (gender, description) VALUES ('Female', 'female 
gender');

-- force dob -mm-dd or NULL
CREATE TABLE subject(
  subjectID  INT PRIMARY KEY NOT NULL,
  dobTEXT CHECK (dob IS date(dob)),
  gender TEXT REFERENCES gender(gender) ON UPDATE CASCADE,
  race   TEXT,
  handedness TEXT REFERENCES handedness(handedness) ON UPDATE CASCADE
);


Please let me know what other info might be useful to debug, including 
debugging on my end! Integrity check on demographics.sqlite is OK.

Thank you for your help,


Roman

[sqlite] select * where is/like ?

[Roman Fleysher]

On 19 May 2015, at 4:43pm, Roman Fleysher  
wrote:

> Now I have two questions:
>
> 1. I created database from scratch using new version of SQLITE and PRAGMA 
> integrity_check; produces "missing index" as before.

Are you telling us that you have a sequence of commands which, done entirely 
inside the SQLite shell tool, produce a corrupt database ?  If so, please 
please please try to find a short set of commands which produce the error and 
post them here.


I am only using shell for all my SQLite "programming". Therefore, yes, shell 
commands produce corrupt database. I guess, I am changing subject of the email. 
But last "news" on the old subject is that "IN" comparison also fails. I will 
try to come up with a short set of commands.

Thank you,

Roman