[sqlite] Checkin 5d9a369301 Cannot Link - sqlite3Fts3Corrupt undefined without SQLITE_DEBUG
fts3_write.c line 1241 calls sqlite3Fts3Corrupt but function is only defined if SQLITE_DEBUG is defined when fts3.c is compiled. -- 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 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 2019-11-25 04:15:33 says 0 errors out of 250197 tests
I felt it was time to change the subject line to something more useful. Also everything works flawlessly here on Red Hat Enterprise Linux 7.4 : . . . SQLite 2019-11-25 04:15:33 b0b655625cf491c832a259d29a67660b8d5943c201617900a83d0660b2673377 0 errors out of 250197 tests on boe13.genunix.com Linux 64-bit little-endian All memory allocations freed - no leaks Maximum memory usage: 9267208 bytes Current memory usage: 0 bytes Number of malloc() : -1 calls Excellent. -- Dennis Clarke RISC-V/SPARC/PPC/ARM/CISC UNIX and Linux spoken GreyBeard and suspenders optional Forwarded Message Subject: Re: [sqlite] What is the C language standard to which sqlite conforms ? Date: Mon, 25 Nov 2019 11:17:32 +0700 From: Dan Kennedy <.> Reply-To: SQLite mailing list To: sqlite-users@mailinglists.sqlite.org On 24/11/62 06:18, Dennis Clarke wrote: On 11/23/19 4:46 PM, Dan Kennedy wrote: Some follow up and thank you all for looking at this. Using this mornings trunk/current/head I do see the tests running well with these little exceptions : boe13$ pwd /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.006 ... build clean as usual :-) tests run nicely now until ... Can you run: ./testfixture test/journal3.test and post the output? It would be my pleasure to get some light tossed on this ... so here is a very clean compile ( no -std in CFLAGS at all on gcc 9.2.0 ) and the tests look like so : This is a test script error. Should now be fixed here: https://sqlite.org/src/info/b0b655625cf491c8 . . . ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] wrong timestamp using strftime('%s')
The result of datetime(0, 'unixepoch') is '1970-01-01 00:00:00'. This is what
does not have milliseconds (the output). It is a text string in the format
-MM-DD HH:MM:SS. Similarly datetime(0.123, 'unixepoch') is also
'1970-01-01 00:00:00'.
The result of datetime(0) is, of course, '-471-11-24 12:00:00' (actually
-4713-11-24 12:00:00) since this the proleptic Gregorian date corresponding to
julian day number 0 where all date strings are UT1 AD and the year is limited
to 4 positions, one of which is taken up by the - sign.
If looking at the result of the datetime() function execution shows
milliseconds then something is interpreting the output of the datetime function
before you see it, and it is likely that this processing that is what is
causing the issue you are observing.
To get output of a unixepoch x with milliseconds in the string you would need
to use the function strftime('%Y-%m-%d %H:%M:%f', x, 'unixepoch')
--
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 On
>Behalf Of Dominik Ohnezeit
>Sent: Monday, 25 November, 2019 14:49
>To: 'SQLite mailing list'
>Subject: Re: [sqlite] wrong timestamp using strftime('%s')
>
>the type of CreationDate is integer.
>The CreationDate is inserted with strftime('%s', '1970-01-01
>00:00:00.000')
>
>need to check which value is written to the database.
>I also tried without milliseconds - same result.
>
>
>-Ursprüngliche Nachricht-
>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>Im Auftrag von Keith Medcalf
>Gesendet: Montag, 25. November 2019 22:41
>An: SQLite mailing list
>Betreff: Re: [sqlite] wrong timestamp using strftime('%s')
>
>
>
>
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>On Sunday, 24 November, 2019 13:21, Dominik Ohnezeit
> wrote:
>
>>I am trying to convert a date to timestamp, but after the conversion
>>with
>>strftime('%s') the integer result is wrong
>
>>Example:
>
>>I insert a integer timestamp into a integer table column named
>>CreationDate with strftime('%s', '1970-01-01 00:00:00.000')
>
>>After getting it from the table with
>
>>datetime(CreationDate, 'unixepoch')
>
>>or
>
>>datetime(CreationDate)
>
>>the date I get back is not 1970-01-01 00:00:00.000 but 1969-12-31
>>22:29:11.000
>
>This corresponds to Unixepoch time -5449
>
>However, the builtin datetime function does not return milliseconds, only
>seconds, so it cannot return a text string ending in .000
>
>>Does anyone know why?
>
>Your wrapper is probably mucking about with the ISO timestring.
>
>What is the value and type of CreationDate?
>
>
>
>___
>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] Multiple Unique column missing in System.Data.DataTable.Constraints after Schema Filling
On 25 Nov 2019, at 11:02am, Jaroslav Homisin wrote:
> I have expected a list of the two constraints: of Primary key and of Unique.
> But I can find only Primary Key Constraint.
Where are you getting your list of constraints from ?
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE XRelations (
...> XRID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,
...> XID1 TEXT (100) NOT NULL ,
...> XID2 TEXT (100) NOT NULL ,
...> RelationType TEXT (100) NULL ,
...> CONSTRAINT unq UNIQUE ( XID1, XID2)
...> );
sqlite> INSERT INTO XRelations VALUES (1,'a','n','first');
sqlite> INSERT INTO XRelations VALUES (2,'a','n','first');
Error: UNIQUE constraint failed: XRelations.XID1, XRelations.XID2
sqlite> INSERT INTO XRelations VALUES (1,'b','m','first');
Error: UNIQUE constraint failed: XRelations.XRID
sqlite> .mode column
sqlite> PRAGMA index_list('XRelations');
0 sqlite_autoindex_XRelations_1 1 u 0
sqlite> PRAGMA index_info('sqlite_autoindex_XRelations_1');
0 1 XID1
1 2 XID2
sqlite> PRAGMA index_xinfo('sqlite_autoindex_XRelations_1');
0 1 XID10 BINARY 1
1 2 XID20 BINARY 1
2 -1 0 BINARY 0
This shows details of the index automatically made by SQLite so it can quickly
find violations of CONSTRAINT unq .
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE IN
Thanks for clarifying this, David. Learned something new today!
On Mon, 25 Nov 2019 at 15:25, David Raymond
wrote:
> There'll be a few differences.
>
> The JOIN version will return 1 row for every item in queries which
> matches, and it will test every single one every time. So if you have in
> the queries table both 'Alex' and 'Alexand' then 'Alexander' and
> 'Alexandra' will each show up twice, once for 'Alex' and once for
> 'Alexand'. Depending on what you're doing this may be what you want.
>
> The EXISTS version will only ever return one row for each record in the
> names table, and it will stop checking other patterns once it finds one
> that matches.
>
> So if you want any info from the queries table then go with the join
> route, if you only care if yes/no there's anything at all that matches,
> then go with exists.
>
>
>
> -Original Message-
> From: sqlite-users On
> Behalf Of Gert Van Assche
> Sent: Saturday, November 23, 2019 5:43 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] LIKE IN
>
> Both queries will work like this:
>
> DROP TABLE names;
> CREATE TABLE names (name TEXT);
> INSERT INTO names VALUES ('Alex');
> INSERT INTO names VALUES ('Alexander');
> INSERT INTO names VALUES ('Alexandra');
> INSERT INTO names VALUES ('Rob');
> INSERT INTO names VALUES ('Rhobin'); -- should not match
> INSERT INTO names VALUES ('Robert');
>
> CREATE TABLE queries (query TEXT);
> INSERT INTO queries VALUES ('Alex');
> INSERT INTO queries VALUES ('Rob');
>
> SELECT name from names t JOIN queries q ON t.name LIKE '%'||q.query||'%';
>
> SELECT name from names
> where exists (
> select query from queries
> where names.name like '%'||query||'%'
> );
>
>
> On Sat, 23 Nov 2019 at 11:34, Gert Van Assche wrote:
>
> > I think this will work:
> >
> > INSERT INTO queries VALUES ('Alex');
> > INSERT INTO queries VALUES ('Rob');
> >
> > select * from names
> > where exists (
> > select query from queries
> > where names.name like '%'||query||'%'
> > );
> >
> > On Fri, 22 Nov 2019 at 15:19, David Raymond
> > wrote:
> >
> >> Or alternatively something like:
> >>
> >> select * from table
> >> where exists (
> >> select query from queries
> >> where table.name like query
> >> );
> >>
> >> ___
> >> 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] wrong timestamp using strftime('%s')
the type of CreationDate is integer.
The CreationDate is inserted with strftime('%s', '1970-01-01 00:00:00.000')
need to check which value is written to the database.
I also tried without milliseconds - same result.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Keith Medcalf
Gesendet: Montag, 25. November 2019 22:41
An: SQLite mailing list
Betreff: Re: [sqlite] wrong timestamp using strftime('%s')
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
On Sunday, 24 November, 2019 13:21, Dominik Ohnezeit
wrote:
>I am trying to convert a date to timestamp, but after the conversion
>with
>strftime('%s') the integer result is wrong
>Example:
>I insert a integer timestamp into a integer table column named
>CreationDate with strftime('%s', '1970-01-01 00:00:00.000')
>After getting it from the table with
>datetime(CreationDate, 'unixepoch')
>or
>datetime(CreationDate)
>the date I get back is not 1970-01-01 00:00:00.000 but 1969-12-31
>22:29:11.000
This corresponds to Unixepoch time -5449
However, the builtin datetime function does not return milliseconds, only
seconds, so it cannot return a text string ending in .000
>Does anyone know why?
Your wrapper is probably mucking about with the ISO timestring.
What is the value and type of CreationDate?
___
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] [FireDAC][Phys][SQLite]-326. Cannot perform the action, because the previous action is in progress.
This is FireDAC error message not SQLite specific. I cannot reproduce it
when
using default FireDAC settings. But I think you must check Database
ResourceOptions
CmdExecMode. Change to am_NonBlocking. But I do not have to change that
by default.
Regards Radovan
On 25.11.2019 18:34, Edson wrote:
Hi everyone!
I am using SQLite, with Delphi XE Rio and Firedac and I don't know why, a
cited error message appeared.
I've tried everything to unlock the database: I renamed the .db file,
changed the folder file, turned off the notebook, etc.,
but I can't unlock the .db database.
The error occurs when trying to open the query a second time, for example:
1) first execution - OK
qry.active: = False;
qry.SQL.Clear;
qry.SQL.Add ('DELETE FROM TAB_USERS');
qry.ExecSQL;
2) second execution - ERROR
qry.active: = False;
qry.SQL.Clear;
qry.SQL.Add ('INSERT INTO TAB_USERS ... ... ...');
qry.ExecSQL;
The error is fired at the line "qry.SQL.Clear;"
I don't know what else to do ... Does anyone have any tips?
Thankful!
___
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] [FireDAC][Phys][SQLite]-326. Cannot perform the action, because the previous action is in progress.
Hi everyone!
I am using SQLite, with Delphi XE Rio and Firedac and I don't know why, a
cited error message appeared.
I've tried everything to unlock the database: I renamed the .db file,
changed the folder file, turned off the notebook, etc.,
but I can't unlock the .db database.
The error occurs when trying to open the query a second time, for example:
1) first execution - OK
qry.active: = False;
qry.SQL.Clear;
qry.SQL.Add ('DELETE FROM TAB_USERS');
qry.ExecSQL;
2) second execution - ERROR
qry.active: = False;
qry.SQL.Clear;
qry.SQL.Add ('INSERT INTO TAB_USERS ... ... ...');
qry.ExecSQL;
The error is fired at the line "qry.SQL.Clear;"
I don't know what else to do ... Does anyone have any tips?
Thankful!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple Unique column missing in System.Data.DataTable.Constraints after Schema Filling
Dear SQLite support, Develop informations: Windows 7 64bit Visual StudioProfessional 2015 (Version 14.0.25431.01 Update 3); .NET Build Platform 4.5.1 System.Data.SQLite.dll used from Installation sqlite-netFx451-setup-bundle-x64-2013-1.0.112.0.exe I am using followed Table: CREATE TABLE XRelations ( XRID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , XID1 TEXT (100) NOT NULL , XID2 TEXT (100) NOT NULL , RelationType TEXT (100) NULL , CONSTRAINT unq UNIQUE ( XID1, XID2) ) In C# .NET Source Code after filling of Schema this table, I am missing the Unique constraint. I have expected a list of the two constraints: of Primary key and of Unique. But I can find only Primary Key Constraint. Note: the Unique Constraint works on the Database fine, if I try to insert redundant row, occurs Exception: UNIQUE constraint failed. DataTable pDt = new DataTable(ptTabellenName); string ltSQL = "select * from " + ptTabellenName + " where 1=0"; System.Data.SQLite.SQLiteDataAdapter lSQLiteDataAdapterSchema = new System.Data.SQLite.SQLiteDataAdapter(); lSQLiteDataAdapterSchema.FillSchema(pDt, SchemaType.Mapped); I have tried some other syntax, but withou effect: CREATE TABLE XRelations ( XRID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , XID1 TEXT (100) NOT NULL , XID2 TEXT (100) NOT NULL , RelationType TEXT (100) NULL , UNIQUE ( XID1, XID2) ) Or via two SQL commands: CREATE TABLE XRelations ( XRID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , XID1 TEXT (100) NOT NULL , XID2 TEXT (100) NOT NULL , RelationType TEXT (100) NULL ); CREATE UNIQUE INDEX unq ON XRelations (XID1, XID2); / If think the Problem is only by multiple column for Unique Key. If I reduce the Unique constrain for single column, then it works: CREATE TABLE XRelations ( XRID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , XID1 TEXT (100) NOT NULL , XID2 TEXT (100) NOT NULL , RelationType TEXT (100) NULL , CONSTRAINT unq UNIQUE (XID2) ) I can find in DataTable.Constrints also Constraint for multiple Primary Key column, but never Constraint for multiple Unique column. Do you can help me or explain me why it doesn't work? Regards, Jaroslav Homisin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] wrong timestamp using strftime('%s')
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
On Sunday, 24 November, 2019 13:21, Dominik Ohnezeit
wrote:
>I am trying to convert a date to timestamp, but after the conversion with
>strftime('%s') the integer result is wrong
>Example:
>I insert a integer timestamp into a integer table column named CreationDate
>with
>strftime('%s', '1970-01-01 00:00:00.000')
>After getting it from the table with
>datetime(CreationDate, 'unixepoch')
>or
>datetime(CreationDate)
>the date I get back is not 1970-01-01 00:00:00.000 but 1969-12-31 22:29:11.000
This corresponds to Unixepoch time -5449
However, the builtin datetime function does not return milliseconds, only
seconds, so it cannot return a text string ending in .000
>Does anyone know why?
Your wrapper is probably mucking about with the ISO timestring.
What is the value and type of CreationDate?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [FireDAC][Phys][SQLite]-326. Cannot perform the action, because the previous action is in progress.
This is FireDAC error message not SQLite specific. I cannot reproduce it
when
using default FireDAC settings. But I think you must check Database
ResourceOptions
CmdExecMode. Change to am_NonBlocking. But I do not have to change that
by default.
Regards Radovan
On 25.11.2019 18:34, Edson wrote:
Hi everyone!
I am using SQLite, with Delphi XE Rio and Firedac and I don't know why, a
cited error message appeared.
I've tried everything to unlock the database: I renamed the .db file,
changed the folder file, turned off the notebook, etc.,
but I can't unlock the .db database.
The error occurs when trying to open the query a second time, for example:
1) first execution - OK
qry.active: = False;
qry.SQL.Clear;
qry.SQL.Add ('DELETE FROM TAB_USERS');
qry.ExecSQL;
2) second execution - ERROR
qry.active: = False;
qry.SQL.Clear;
qry.SQL.Add ('INSERT INTO TAB_USERS ... ... ...');
qry.ExecSQL;
The error is fired at the line "qry.SQL.Clear;"
I don't know what else to do ... Does anyone have any tips?
Thankful!
___
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] What is the C language standard to which sqlite conforms ?
It would be my pleasure to get some light tossed on this ... so here is a very clean compile ( no -std in CFLAGS at all on gcc 9.2.0 ) and the tests look like so : This is a test script error. Should now be fixed here: https://sqlite.org/src/info/b0b655625cf491c8 What version of Tcl are you using? 8.7a1 which tests clean here : Tests ended at Thu Nov 07 03:24:35 GMT 2019 all.tcl:Total 31405 Passed 30187 Skipped 1218Failed 0 Sourced 148 Test Files. Number of tests skipped for each constraint: 9 !ieeeFloatingPoint 3 asyncPipeChan 76 bigEndian 5 bug-3057639 49 dde 4 dontCopyLinks 63 emptyTest 5 fullutf 2 hasIsoLocale 1 knownBadTest 39 knownBug 100 localeRegexp 48 longIs32bit 14 macosxFileAttr 45 nonPortable 5 notNetworkFilesystem 1 obsolete 4 readonlyAttr 3 singleTestInterp 1 testexprparser && !ieeeFloatingPoint 7 testpreferstable 1 testwinclock 21 testwordend 189 thread 2 unthreaded 2 wideBiggerThanInt 504 win 4 winVista I'll take a look at https://sqlite.org/src/info/b0b655625cf491c8 and get a build going here shortly. Thank you very much Sir. -- Dennis Clarke RISC-V/SPARC/PPC/ARM/CISC UNIX and Linux spoken GreyBeard and suspenders optional ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [FireDAC][Phys][SQLite]-326. Cannot perform the action, because the previous action is in progress.
Hi everyone!
I am using SQLite, with Delphi XE Rio and Firedac and I don't know why, a
cited error message appeared.
I've tried everything to unlock the database: I renamed the .db file,
changed the folder file, turned off the notebook, etc.,
but I can't unlock the .db database.
The error occurs when trying to open the query a second time, for example:
1) first execution - OK
qry.active: = False;
qry.SQL.Clear;
qry.SQL.Add ('DELETE FROM TAB_USERS');
qry.ExecSQL;
2) second execution - ERROR
qry.active: = False;
qry.SQL.Clear;
qry.SQL.Add ('INSERT INTO TAB_USERS ... ... ...');
qry.ExecSQL;
The error is fired at the line "qry.SQL.Clear;"
I don't know what else to do ... Does anyone have any tips?
Thankful!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 回复： stack-overflow issue in fts4 module
On 11/25/19, OBones wrote: > Maybe I'm completely wrong, but using t0 both as the name of the virtual > table and the source for its content seems to me like the perfect > condition to create a infinite recursion. You are exactly correct in diagnosing the problem. This is an attack that we didn't think of. The recursion is detected and blocked by check-in https://www.sqlite.org/src/info/2eb997327c2c369c from last week. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 回复： stack-overflow issue in fts4 module
林性伟(林以) wrote: Hi, Sorry to make you inconvenient. poc, test.sql: CREATE VIRTUAL TABLE t0 USING fts4(content=t0,0); SELECT count() FROM t0(0); Maybe I'm completely wrong, but using t0 both as the name of the virtual table and the source for its content seems to me like the perfect condition to create a infinite recursion. The example in the documentation uses two tables: https://www.sqlite.org/fts3.html#_external_content_fts4_tables_ Regards ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shell commands for controlling headers
Dr Hipp replied to this 2 days ago with this: Documentation fix https://www.sqlite.org/docsrc/info/a2762f031964e774 will appears in the next release. ".header" is an abbreviation for ".headers" and does exactly the same thing. -Original Message- From: sqlite-users On Behalf Of John McKown Sent: Monday, November 25, 2019 9:51 AM To: SQLite mailing list Subject: Re: [sqlite] Shell commands for controlling headers On Mon, Nov 25, 2019 at 8:42 AM Craig Maynard wrote: > All, > > Could someone clarify the difference between the two sqlite3 shell > commands .header and .headers? > > The relevant documentation page: https://www.sqlite.org/cli.html > > On the cli page, .header is discussed in section 5 but does not appear in > Section 3. > > Thanks, > Craig > > -- > Craig H Maynard > Rhode Island, USA > > In the sqlite cli itself, doing an ".help", I see: .header(s) So I am guessing that they are the same things, perhaps for compatibility with something in the past. -- People in sleeping bags are the soft tacos of the bear world. Maranatha! <>< John McKown ___ 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] Shell commands for controlling headers
On Mon, Nov 25, 2019 at 8:42 AM Craig Maynard wrote: > All, > > Could someone clarify the difference between the two sqlite3 shell > commands .header and .headers? > > The relevant documentation page: https://www.sqlite.org/cli.html > > On the cli page, .header is discussed in section 5 but does not appear in > Section 3. > > Thanks, > Craig > > -- > Craig H Maynard > Rhode Island, USA > > In the sqlite cli itself, doing an ".help", I see: .header(s) So I am guessing that they are the same things, perhaps for compatibility with something in the past. -- People in sleeping bags are the soft tacos of the bear world. Maranatha! <>< John McKown ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Shell commands for controlling headers
All, Could someone clarify the difference between the two sqlite3 shell commands .header and .headers? The relevant documentation page: https://www.sqlite.org/cli.html On the cli page, .header is discussed in section 5 but does not appear in Section 3. Thanks, Craig -- Craig H Maynard Rhode Island, USA ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE IN
There'll be a few differences.
The JOIN version will return 1 row for every item in queries which matches, and
it will test every single one every time. So if you have in the queries table
both 'Alex' and 'Alexand' then 'Alexander' and 'Alexandra' will each show up
twice, once for 'Alex' and once for 'Alexand'. Depending on what you're doing
this may be what you want.
The EXISTS version will only ever return one row for each record in the names
table, and it will stop checking other patterns once it finds one that matches.
So if you want any info from the queries table then go with the join route, if
you only care if yes/no there's anything at all that matches, then go with
exists.
-Original Message-
From: sqlite-users On Behalf Of
Gert Van Assche
Sent: Saturday, November 23, 2019 5:43 AM
To: SQLite mailing list
Subject: Re: [sqlite] LIKE IN
Both queries will work like this:
DROP TABLE names;
CREATE TABLE names (name TEXT);
INSERT INTO names VALUES ('Alex');
INSERT INTO names VALUES ('Alexander');
INSERT INTO names VALUES ('Alexandra');
INSERT INTO names VALUES ('Rob');
INSERT INTO names VALUES ('Rhobin'); -- should not match
INSERT INTO names VALUES ('Robert');
CREATE TABLE queries (query TEXT);
INSERT INTO queries VALUES ('Alex');
INSERT INTO queries VALUES ('Rob');
SELECT name from names t JOIN queries q ON t.name LIKE '%'||q.query||'%';
SELECT name from names
where exists (
select query from queries
where names.name like '%'||query||'%'
);
On Sat, 23 Nov 2019 at 11:34, Gert Van Assche wrote:
> I think this will work:
>
> INSERT INTO queries VALUES ('Alex');
> INSERT INTO queries VALUES ('Rob');
>
> select * from names
> where exists (
> select query from queries
> where names.name like '%'||query||'%'
> );
>
> On Fri, 22 Nov 2019 at 15:19, David Raymond
> wrote:
>
>> Or alternatively something like:
>>
>> select * from table
>> where exists (
>> select query from queries
>> where table.name like query
>> );
>>
>> ___
>> 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] wrong timestamp using strftime('%s')
Dominik Ohnezeit, on Sunday, November 24, 2019 03:21 PM, wrote...
> strftime('%s', '1970-01-01 00:00:00.000')
[clip]
> the date I get back is not 1970-01-01 00:00:00.000 but 1969-12-31
> 22:29:11.000
>
> Does anyone know why?
Here are some samples run...
sqlite> select strftime('%s', '1970-01-01 00:00:00.000');
0
sqlite> select strftime('%s', '1969-12-31 23:59:59');
-1
sqlite> select datetime(0, 'unixepoch');
1970-01-01 00:00:00
sqlite> select datetime(-1, 'unixepoch');
1969-12-31 23:59:59
You are probably subtracting 1 from 0 which is sending a -1 to datetime. Just
a thought... Thanks.
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] wrong timestamp using strftime('%s')
On 11/24/19, Dominik Ohnezeit wrote:
>
> strftime('%s', '1970-01-01 00:00:00.000')
Returns '0'.
>
> datetime(CreationDate, 'unixepoch')
Assuming CreationDate is 0, this returns '1970-01-01 00:00:00'.
>
> Does anyone know why?
>
Dunno why you might be getting anything different.
--
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] wrong timestamp using strftime('%s')
Hello,
I am trying to convert a date to timestamp, but after the conversion with
strftime('%s') the integer result is wrong
Example:
I insert a integer timestamp into a integer table column named CreationDate
with
strftime('%s', '1970-01-01 00:00:00.000')
After getting it from the table with
datetime(CreationDate, 'unixepoch')
or
datetime(CreationDate)
the date I get back is not 1970-01-01 00:00:00.000 but 1969-12-31
22:29:11.000
Does anyone know why?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] edit() function should be disabled in batch mode
With recent versions of sqlite3 (not seen with OSX Mojave 10.14.6 stock SQLite version 3.16.2 or before), a convenient function edit() was introduced. However it is not disabled for batch mode where it is never intended. For batch mode it is preferable to keep backward compatible behavior to ease script maintenance. Pre-existence behavior $ sqlite3 testdb SQLite version 3.16.2 2017-01-06 16:32:41 Enter ".help" for usage hints. sqlite> UPDATE docs SET body=edit(body) WHERE name='report-15'; Error: no such function: edit sqlite> ^D $ sqlite3 -batch testdb UPDATE docs SET body=edit(body) WHERE name='report-15'; Error: near line 1: no such function: edit $ Current behavior: $ sqlite3 SQLite version 3.28.0 2019-04-16 19:49:53 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table docs(name, body); sqlite> UPDATE docs SET body=edit(body) WHERE name='report-15'; sqlite> ^C sqlite> $ sqlite3 -batch create table docs(name, body); .tables docs UPDATE docs SET body=edit(body) WHERE name='report-15'; (hang again and has to be killed by ^C) This issue also impact certain ssh/telnet sessions. -Liyu PS: a minor and separate issue is there's no way to reset line number for error reporting. As a result one has to keep track of all the sql text lines in the session to pinpoint error. It is better to allow reset between queries. For example $ sqlite3 -batch testdb UPDATE docs SET body=edit(body) WHERE name='report-15'; Error: near line 1: no such function: edit UPDATE docs SET body=edit(body) WHERE name='report-15'; Error: near line 2: no such function: edit UPDATE docs SET body=edit(body) WHERE name='report-15'; Error: near line 3: no such function: edit ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency Question
On Sat, Nov 23, 2019 at 4:17 PM Dan Kennedy wrote: > > [...] Why is thread_B blocked when doing a read just because SQLite is > writing to another table? [...] > > Is this the expected behavior or am I doing something stupid in my code. > And if so, what to check? > > This should only happen if you are using shared-cache mode. Don't use > shared-cache mode. > But I'm forced to use shared-cache for multiple connections to an in-memory database [1]. This is an important use-case IMHO, and the fact in-memory DBs can't use WAL-mode, and benefit from the added concurrency in the face of updates, is a real bummer IMHO. [1] https://www.sqlite.org/sharedcache.html#inmemsharedcache ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
