Re: [sqlite] [EXTERNAL] sqlite_master structure

If an entry refers to an index, the field "name" will contain the name of the index, while the field "tbl_name" contains the name of the table the index refers to. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Igor Korot

[sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

Hello, I built TCl 8.6 with SQLite extension (3.2.0) which I embedded into a C++ application. Because I need SQLite in both TCL and C++ I defined the SQLite macro of (the extension) to export all the SQLite C API's. So the the built SQLite DLL library TCL extension exports both the TCL interface as

[sqlite] SQLite DB - DateTime field values are not consistent

Hi Team, We have identified an issue with SQLite database which seems to be a potential bug. In SQLite database, All the values of datetime fields are saved as text without any format check. We can save any text value in the datetime column. This behavior is creating problem for us becau

Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not consistent

There is no "date" datatype in SQLite, an das you yourself attest, SQLite is returning exactly whatever was originally inserted. It is up to your application to define the allowed format for storing datetimes and to provide conversion between the chosen storage format and the presentation to th

Re: [sqlite] SQLite DB - DateTime field values are not consistent

As Hick mentioned, you could get your application to deal with this, or, if you want it at the SQLite level, you could get into triggers to analyze the data before its being inserted or updated. For me, it depends on how you want to look at the issue as far as what part of the entire application s

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

Hello, I have found the cause of the weird behaviour. The in Shared Cache In-Memory DB isn't persisted, rather the problem is due to a temp file named "file" that isn't deleted after my application closes. Whenever I delete this file then everything works fine. From my search this file ought to be

Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not consistent

For basic level you can use a check constraint create table Table1 ( TestDate DATETIME check (TestDate like '-__-__ __:__:__' and datetime(TestDate) is not null) ); That should check that it's in the correct format, and the second part should check that there's no garbage in

Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not consistent

On 11/2/17, David Raymond wrote: > For basic level you can use a check constraint > > create table Table1 ( > TestDate DATETIME > check (TestDate like '-__-__ __:__:__' >and datetime(TestDate) is not null) > ); > > That should check that it's in the correct format, and the se

Re: [sqlite] [EXTERNAL] Re: SQLite DB - DateTime field values are not consistent

My point is that there is no datetime magic performed for TEXT fields. If you feel the database should handle it, go ahead and write triggers/check constraints. If you feel the application should handle it, make it convert to and from just one single format (we use 64bit numerical timestamps in

Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not consistent

On Thu Nov 02, 2017 at 10:18:41AM -0400, Richard Hipp wrote: > On 11/2/17, David Raymond wrote: > > For basic level you can use a check constraint > > > > create table Table1 ( > > TestDate DATETIME > > check (TestDate like '-__-__ __:__:__' > >and datetime(TestDate) is not n

Re: [sqlite] [EXTERNAL] SQLite DB - DateTime field values are not consistent

I think the combo is needed as with just the time function not null constraint you can just insert in plain ol' integer values. sqlite> select julianday(7); julianday(7) 7.0 sqlite> select datetime(2); datetime(2) -4713-11-26 12:00:00 -Original Message- From: sqlite-users [mailto:sqlite

Re: [sqlite] SQLite DB - DateTime field values are not consistent

On 02 Nov 2017, at 12:23, Rajat Pal wrote: > In SQLite database, All the values of datetime fields are saved as text > without any format check. We can save any text value in the datetime column. > This behavior is creating problem for us because for different regional > settings, datetime value

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

yaro wrote: > the problem is due to a temp file named "file" that isn't deleted after my > application closes. SQLite does not create temp files named "file". Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://m

[sqlite] How to do rollback & rollforward?

Hi, following scenario: Our app has a very exhaustive undo system. There you can, as long as you don't change anything, go back in time, take a look at your data and than go forward again and get back all changes you have done. I think the VERSION extension might help us here. On the other hand

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

On 11/02/2017 08:50 PM, yaro wrote: Hello, I have found the cause of the weird behaviour. The in Shared Cache In-Memory DB isn't persisted, rather the problem is due to a temp file named "file" that isn't deleted after my application closes. Whenever I delete this file then everything works fine.

Re: [sqlite] How to do rollback & rollforward?

On 11/2/17, Robert M. Münch wrote: > Hi, following scenario: > > Our app has a very exhaustive undo system. There you can, as long as you > don't change anything, go back in time, take a look at your data and than go > forward again and get back all changes you have done. > > I think the VERSION e

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

Dan, Your question was well composed and illuminating. After going through my code I realised that I didn't enable URI Filename in any of the ways you mentioned, my guide to using URI Filename is https://www.sqlite.org/inmemorydb.html May be that is the

Re: [sqlite] SQLite DB - DateTime field values are not consistent

Fascinating. What is the Operating System and Version (eg Linux or Windows ); SQLite version; interface (C or .snot)? Does this only affect trips through the platform localtime function within SQLite (that is, if you store GMT/UTC by leaving out the 'localtime' modifier) do you still

Re: [sqlite] SQLIte Shared Cache In-Memory DB Persists After Application Shutdown

Thanks Clemens and Dan, The issue is resolved, it was caused by not setting the URI Filename flag. Yaro -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.or

[sqlite] Typo on VFS webpage

On https://www.sqlite.org/vfs.html in the paragraph about test_quota.c, in one instance it says "quote groups" instead of "quota groups" ^ ^ -- Happy hacking, ~ Luke Shumaker ___ sqlite-users mailing list sqlite-user

[sqlite] Missing data on SELECT

Greetings. Apologies for the long email, or long set of data. I have these two tables in two different database files, but for easy setup, I have place them in the same DB. Having these data, CREATE TABLE ProjectsALL ( id integer primary key, ProjID integer, login, cust, proj, XtraB ); IN

Re: [sqlite] [EXTERNAL] sqlite_master structure

Don't forget about this handy pragma: PRAGMA schema.table_info(table-name); This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The "pk" column in

Re: [sqlite] Missing data on SELECT

I think your query is in error. Amongst other things, tou have the same condition listed twice: SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON ( cl.id = ls.id AND cl.login = 'id0' AND cl.id = ls.id AND DUPLICATE cl.XtraB != ls.XtraB ); You cannot

[sqlite] deadlock between query and insert in-memory db

Can anyone help me on this? Thanks Peter From: Wang, Peter (Xu) Sent: Tuesday, October 31, 2017 4:19 PM To: 'sqlite-users@mailinglists.sqlite.org' Cc: wangxu198...@gmail.com Subject: Segfault when query again in-memory db Hi I am trying to provide a asychnonous queue based on sqlite3 in Python

Re: [sqlite] deadlock between query and insert in-memory db

Please clarify (for the second time): >I tested the in-memory with multi-thread (but inserts/deletes are in >a lock) What does "with multi-thread" mean? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Mess

Re: [sqlite] Missing data on SELECT

You are inserting rows with ProjID but not the primary key "id" which is rowid and automatically created. I think it is not a good practice to use rowid's like you are doing. If you had inserted the id it would be a different story. *Jim Dodgen* On Thu, Nov 2, 2017 at 5:26 PM, Keith Medca

Re: [sqlite] Missing data on SELECT

You're right. Apologies. The right SELECT would be, SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON ( cl.id = ls.id AND cl.login = 'id0' AND cl.ProjID > 3 AND cl.XtraB != ls.XtraB ); To answer your question, and a long story made short, this is a SELECT to see which item

Re: [sqlite] Missing data on SELECT

It's a long story, but what I would like to SELECT is the id or ids that have different XTraB between ProjectsMine and ProjectsALL, and also list the id, or ids, that exists in ProjectsMine and not on ProjectsALL. This is do do a push to ProjectsAll of those ids from ProjectsMine. -Origi

Re: [sqlite] Missing data on SELECT

On 3 Nov 2017, at 4:32am, jose isaias cabrera wrote: > It's a long story, but what I would like to SELECT is the id or ids that have > different XTraB between ProjectsMine and ProjectsALL, and also list the id, > or ids, that exists in ProjectsMine and not on ProjectsALL. Do it as two separa

Re: [sqlite] Missing data on SELECT

Well that is a totally different thing. Of course, in your sample data id-10 DOES NOT exist in ProjectMine, so you will never see id=10 no matter what you do. select id from ProjectsMine where id not in (select id from ProjectsALL); or select id from ProjectsMine except select id from Projec