Re: [sqlite] Foreign Key errors

On Tue, May 6, 2014 at 11:17 PM, Richard Hipp wrote: > On Tue, May 6, 2014 at 5:12 PM, Peter Haworth wrote: >> It seems that foreign key errors on columns where the foreign key >> definition has a constraint name don't include the constraint name in the >> error

Re: [sqlite] group_concat(distinct) with empty strings

Hinrichsen, John wrote: > Are the results below expected? > sqlite> SELECT (SELECT GROUP_CONCAT(DISTINCT COALESCE(a,'')) FROM z) IS NULL; > 1 You don't need DISTINCT: sqlite> select typeof(group_concat('')); null The documentation says: | The group_concat() function returns a string which is

Re: [sqlite] LSM Leaks memory

On 05/07/2014 04:51 AM, sql...@charles.derkarl.org wrote: I In a more complex program, lsm seems to leak memory to no bounds, causing my application. Are bug reports against LSM even helpful? I think they are. Thanks for the report. Now fixed here:

Re: [sqlite] Very slow when query max(col) on empty set?

On 2014年5月7日 GMT+08:00AM3:16:35, Clemens Ladisch wrote: >Woody Wu wrote: >> The following query statement executed very slow, it took 15 secs on >my ARM device, >> >> 1. select max(time) from mytable where time < and id1 = k1 >and id2 = n. >> >> However, if I replace

[sqlite] WITHOUT ROWID option

What is the best way to know if a table has been created with the WITHOUT ROWID option? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] WITHOUT ROWID option

SELECT instr(upper(sql),'WITHOUT ROWID')>1 FROM sqlite_master WHERE type='table' AND tbl_name='YourTableName' Returns 1 for tables made without rowid, 0 for the rest. On 2014/05/07 15:00, Marco Bambini wrote: What is the best way to know if a table has been created with the WITHOUT ROWID

Re: [sqlite] WITHOUT ROWID option

On 5/7/2014 9:40 AM, RSmith wrote: SELECT instr(upper(sql),'WITHOUT ROWID')>1 FROM sqlite_master WHERE type='table' AND tbl_name='YourTableName' Returns 1 for tables made without rowid, 0 for the rest. CREATE TABLE t(x text default 'WITHOUT ROWID'); -- Igor Tandetnik

Re: [sqlite] WITHOUT ROWID option

Apologies, my answer reads more like a "How to" than a "What is the best way" type answer, so to just elaborate on the brevity - SQLite stores no special pointer or memory or setting or even file value anywhere that can give you any clue apart from the actual words "Without RowID" which are

Re: [sqlite] WITHOUT ROWID option

...or indeed this malicious-but-valid table-schema design would prove problematic. Other that might cause similar headaches are: CREATE TABLE t(x text // )WITHOUT ROWID; ); or CREATE TABLE t(x text); // )WITHOUT ROWID; etc. It would require a rather convoluted check to be very

Re: [sqlite] WITHOUT ROWID option

So, is there an official recommended way? or that check should require a manual sql parsing? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs On 07 May 2014, at 15:51, Igor Tandetnik wrote: > On 5/7/2014 9:40 AM, RSmith wrote:

Re: [sqlite] WITHOUT ROWID option

On Wed, May 7, 2014 at 9:00 AM, Marco Bambini wrote: > What is the best way to know if a table has been created with the WITHOUT > ROWID option? > (1) You could send "SELECT rowid FROM table" to sqlite3_prepare() and see if it returns an error. This might fail on a table

Re: [sqlite] Very slow when query max(col) on empty set?

On 7 May 2014, at 1:29pm, Woody Wu wrote: > The 'explain query plan' gives same result for the first and the second query: > > 0|0|0|SEARCH TAB mp USING COVERING INDEX sqlite_auto_index_mp_1 (ntimereq (~1 rows) > > BTW: I dont understand what the (~1 rows) mean.

Re: [sqlite] WITHOUT ROWID option

Thanks a lot Richard, I really appreciate. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs On 07 May 2014, at 16:31, Richard Hipp wrote: > On Wed, May 7, 2014 at 9:00 AM, Marco Bambini wrote: > >> What is the

Re: [sqlite] WITHOUT ROWID option

On 7 May 2014, at 3:31pm, Richard Hipp wrote: > (2) Run both "PRAGMA index_list(table)" and "SELECT name FROM sqlite_master > WHERE tbl_name='table'". If the PRAGMA mentions an > "sqlite_autoindex_table_1" which is not mentioned by the SELECT, then you > have a WITHOUT ROWID

Re: [sqlite] WITHOUT ROWID option

On Wed, May 7, 2014 at 4:57 PM, Simon Slavin wrote: > somehow ? Perhaps the ROWID field of a table might have its own > particular indication, and if you don't see any rows marked like that you > could deduce that the table had no ROWID column. I'm sure there are better >

Re: [sqlite] Is there a single file version of System.Data.SQLite?

Thank you for the suggestion. There are 2 issues: 1: I tried: Assembly SampleAssembly = Assembly.Load("System.Data.SQLite, Version=1.0.92.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139") The assembly loads, but I couldn't figure out how to use it. None of the SQLite classes are in

[sqlite] Could not open registry key

All, I tried running the installer in sqlite-netFx20-binary-bundle-Win32-2005-1.0.92.0.zip and received the following error: Installer.exe: #047 @ 2014.05.07T16:29:59.4744965: TraceOps.ShowMessage: could not open registry key:

Re: [sqlite] group_concat(distinct) with empty strings

http://www.sqlite.org/src/info/0deac8737545a020d344be96fff16660a7977ab8 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a single file version of System.Data.SQLite?

Drago, William @ MWG - NARDAEAST wrote: > > The assembly loads, but I couldn't figure out how to use it. > Yeah, using types in an assembly via reflection is always a bit tricky. > > None of the SQLite classes are in SampleAssembly, just a handful of things > that have nothing to do with

Re: [sqlite] Could not open registry key

Drago, William @ MWG - NARDAEAST wrote: > > I tried running the installer in sqlite-netFx20-binary-bundle-Win32-2005-1.0.92.0.zip and received the following error: > > Installer.exe: #047 @ 2014.05.07T16:29:59.4744965: TraceOps.ShowMessage: could not > open registry key:

Re: [sqlite] Could not open registry key

You almost certainly need to be running with full admin permissions. If it's an MSI in the ZIP, just being logged into an admin account won't be sufficient on Windiws 7+  Open a command prompt by right-clicking and selecting "Run as administrator" and run the MSI from there. Sent from Samsung

Re: [sqlite] Very slow when query max(col) on empty set?

Simon Slavin wrote: > On 7 May 2014, at 1:29pm, Woody Wu wrote: >> The 'explain query plan' gives same result for the first and the second >> query: >> >> 0|0|0|SEARCH TAB mp USING COVERING INDEX sqlite_auto_index_mp_1 (ntimereq> (~1 rows) >> >> BTW: I dont understand what

[sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition

$ sqlite3 SQLite version 3.7.17 2013-05-20 00:56:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE x AS SELECT 1 AS a, 1 AS b; sqlite> CREATE INDEX ix ON x (a); sqlite> CREATE TABLE y AS SELECT 1 AS b; sqlite> EXPLAIN QUERY PLAN SELECT * FROM x INNER

Re: [sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition

On Wed, May 7, 2014 at 4:51 PM, Hinrichsen, John wrote: > $ sqlite3 > SQLite version 3.7.17 2013-05-20 00:56:22 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> CREATE TABLE x AS SELECT 1 AS a, 1 AS b; > sqlite> CREATE INDEX ix ON x

Re: [sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition

On Wed, May 7, 2014 at 5:21 PM, Richard Hipp wrote: > > Do you have a database file where the 3.8.4.3 query plan really is slower? > Can you please run ANALYZE on that database and send us the content of the > "sqlite_stat1" table? > > It is true that if we add the analyze, the

[sqlite] More LSM leak

This leak cursor leak can be consistently reproduced by my test program, but it doesn't occur every time you create and delete the cursor. The files you'll need are: http://www.derkarl.org/~charles/lsm/smaller.trace.bz2 http://www.derkarl.org/~charles/lsm/runlsm.cpp (The latter of which has

Re: [sqlite] performance regression: sqlite-3.8.4.3 is not using an automatic covering index when joining with a where condition

On Wed, May 7, 2014 at 6:58 PM, Hinrichsen, John wrote: > On Wed, May 7, 2014 at 5:21 PM, Richard Hipp wrote: > > > > > Do you have a database file where the 3.8.4.3 query plan really is > slower? > > Can you please run ANALYZE on that database and send us

Re: [sqlite] Foreign Key errors

I'd vote for having this as a future enhancement under the control of a pragma or some other way of making it optional. Some of my tables have more than 1 foreign key and without the constraint name I have to write application code to pre-check for foreign key errors since I can't translate the