[sqlite] Maybe a bug

[zmmffff]

Running "testfixture.exe veryquick.test --verbose=file
--output=test-out.txt" compiled with ICU support gives

:

Time: fts3ao.test 320 ms

fts3atoken-3.3...

Error: error in xOpen()

.\testfixture.exe: error in xOpen()

while executing

"ifcapable icu {

 

  proc do_icu_test {name locale input output} {

set ::out [db eval { SELECT fts3_tokenizer_test('icu', $locale, $input)
}]

do..."

(file "SQLite/test/fts3atoken.test" line 134)

invoked from within

"source SQLite/test/fts3atoken.test"

invoked from within

"interp eval tinterp $script"

(procedure "slave_test_script" line 30)

invoked from within

"slave_test_script [list source $zFile] "

invoked from within

"time { slave_test_script [list source $zFile] }"

(procedure "slave_test_file" line 23)

invoked from within

"slave_test_file $file"

(procedure "run_tests" line 13)

invoked from within

"run_tests veryquick -presql {} -files {shared3.test vacuum5.test func4.test
tkt3731.test SQLite/test/../ext/fts5..."

("uplevel" body line 1)

invoked from within

"uplevel run_tests $name $::testspec($name)"

(procedure "run_test_suite" line 5)

invoked from within

"run_test_suite veryquick"

(file "SQLite\test\veryquick.test" line 16)

NMAKE : fatal error U1077: '.\testfixture.exe' : return code '0x1'

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

[sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[PICCORO McKAY Lenz]

an important feature in a DB its the column field that gives to developers
metadata info INDEPENDENT of the tecnologies used, due by this way with a
simple text editor in generated script developer can read and use minimal
info for understanding structure ...

its a minimal feature need in a database, for many developers that make
GOOD documentation!


Lenz McKAY Gerardo (PICCORO)
http://qgqlochekone.blogspot.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Application is crashing while performing sqlite3_prepare_v2 with sqlite version 3.15.2 (intermittently)

[ROCHAK GUPTA]

Hi All,

I am using sqlite as library in multi-threaded environment where multiple
applications use sqlite queries to perform get/set operations on attributes
available in sqlite db tables. Intermittently i am seeing applications
crashing when get operation is performed which intern call
sqlite3_prepare_v2.
Crash is not frequent. One thing to notice that, I never hit this issue
when i was using 3.8.10.2 for couple of months. First time i hit the issue
only after updating 3.15.2 recently. Could anyone please help me with this?
Following is the GDB back trace for the same. Please let me know if this is
a known issue and if its fixed in latest sqlite version.
#0 0x4ffc9494 in __aeabi_memcpy8 () at ../sysdeps/arm/armv7/
multiarch/memcpy_impl.S:352
#1  0x4194d514 in
whereLoopXfer (pFrom=0x19a560, pTo=0x1c14d0, db=) at
/usr/src/debug/sqlite3/3_3.15.2.0-r0/sqlite-autoconf-
3150200/sqlite3.c:129646
#2  whereLoopInsert
(pTemplate=pTemplate@entry=0x19a560, pBuilder=0x1c2858, pBuilder=0x1c2858)
at /usr/src/debug/sqlite3/3_3.15.2.0-r0/sqlite-autoconf-
3150200/sqlite3.c:64414
#3  0x419700f0 in
whereLoopAddBtree (pBuilder=0x1c2858, pBuilder@entry=0x0,
mPrereq=7281790634533488) at /usr/src/debug/sqlite3/3_3.15.
2.0-r0/sqlite-autoconf-3150200/sqlite3.c:130653
#4  0x419892fc in
whereLoopAddAll (pBuilder=0x0) at /usr/src/debug/sqlite3/3_3.15.
2.0-r0/sqlite-autoconf-3150200/sqlite3.c:131229
#5  sqlite3WhereBegin
(pParse=pParse@entry=0xbec43e68, pTabList=0x0, pTabList@entry=0x1c2850,
pWhere=pWhere@entry=0xbec43cd0, pOrderBy=, pDistinctSet=0x1c2e50,
wctrlFlags=48760, iAuxArg=320) at /usr/src/debug/sqlite3/3_3.15.
2.0-r0/sqlite-autoconf-3150200/sqlite3.c:1283
#6  0x4198d8e0 in
sqlite3Select (pParse=pParse@entry=0xbec43e68, p=, pDest=0x0, pDest@entry
=0xbec43e08)
at /usr/src/debug/sqlite3/3_3.15.2.0-r0/sqlite-autoconf-
3150200/sqlite3.c:119546
#7  0x419b8358 in
yy_reduce (yyruleno=, yypParser=0x1b2990) at /usr/src/debug/sqlite3/3_3.15.
2.0-r0/sqlite-autoconf-3150200/sqlite3.c:135340
#8  sqlite3Parser
(pParse=0xbec43e68, yyminor=..., yymajor=, yyp=) at
/usr/src/debug/sqlite3/3_3.15.2.0-r0/sqlite-autoconf-3150200/sqlite3.c:5377
#9  sqlite3RunParser
(pParse=pParse@entry=0xbec43e68, zSql=0xe8 ,
zSql@entry=0x198248 "SELECT  FROM  WHERE
"..., pzErrMsg=0x1c1dd0, pzErrMsg@entry=0xbec43e58) at
/usr/src/debug/sqlite3/3_3.15.2.0-r0/sqlite-autoconf-3150200/sqlite3.c:6324
#10  0x419bdae8
in sqlite3Prepare (db=db@entry=0x19be78,
zSql=zSql@entry=0x198248 "SELECT  FROM  WHERE
 "..., nBytes=nBytes@entry=233, saveSqlFlag=-1094435176,
saveSqlFlag@entry=1, pReprepare=pReprepare@entry=0x0, ppStmt=ppStmt@entry=
0xbec440d8, pzTail=pzTail@entry=0x0) at /usr/src/debug/sqlite3/3_3.15.
2.0-r0/sqlite-autoconf-3150200/sqlite3.c:114126
#11  0x419bdfb4
in sqlite3LockAndPrepare (db=0x19be78,
zSql=0x198248 "SELECT  FROM  WHERE "...,
nBytes=233, saveSqlFlag=1, pOld=0x0, ppStmt=0xbec440d8, pzTail=0x0) at
/usr/src/debug/sqlite3/3_3.15.2.0-r0/sqlite-autoconf-
3150200/sqlite3.c:114217
#12  0x419be27c
in sqlite3_prepare_v2 (db=,
zSql=zSql@entry=0x198248 "SELECT  FROM  WHERE
 "..., nBytes=, ppStmt=ppStmt@entry=0xbec440d8,
pzTail=pzTail@entry=0x0) at /usr/src/debug/sqlite3/3_3.15.
2.0-r0/sqlite-autoconf-3150200/sqlite3.c:114293

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

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Bart Smissaert]

Can't you add it to the field name?
For example for a field holding date of birth: DOB_INT or DOB_TXT.

RBS



On Tue, Mar 14, 2017 at 12:54 PM, PICCORO McKAY Lenz  wrote:

> an important feature in a DB its the column field that gives to developers
> metadata info INDEPENDENT of the tecnologies used, due by this way with a
> simple text editor in generated script developer can read and use minimal
> info for understanding structure ...
>
> its a minimal feature need in a database, for many developers that make
> GOOD documentation!
>
>
> Lenz McKAY Gerardo (PICCORO)
> http://qgqlochekone.blogspot.com
> ___
> 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] bounded id

[Jan Danielsson]

Hello,

   I have a wire-protocol which uses an uint32_t to identify transfers,
and each of the transfers is represented by a row in a table in an
sqlite database.

   I can't make the rowid a uint32_t, but that's essentially the
behavior I'm looking for -- I'd like to be able to insert a new row into
the table, then ask for the last inserted row id and use that identifier
in the wire-protocol.  Are there any good options to accomplish this
[limit the auto-assigned identifier]?

   The obvious solution is to record-keep identifiers outside of the
database, but the sqlite database is responsible for assigning other
other identifiers; it would be nice not to break that abstraction if
possible.

-- 
Kind regards,
Jan Danielsson

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

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[R Smith]

On 2017/03/14 2:54 PM, PICCORO McKAY Lenz wrote:

an important feature in a DB its the column field that gives to developers
metadata info INDEPENDENT of the tecnologies used, due by this way with a
simple text editor in generated script developer can read and use minimal
info for understanding structure ...

its a minimal feature need in a database, for many developers that make
GOOD documentation!


I know this is not implementing the feature, but may I suggest some way 
to achieve the same.


What we do (typically), since SQLite supports C-type comment blocks /* 
... */, is to add comment lines to the schema and they are preserved 
correctly. For example:


CREATE TABLE "test" (
  "ID" INTEGER /* Here we add column comments */,
  "Name" TEXT /* Note the comma is AFTER the comment */,
  "EMail" TEXT COLLATE NOCASE /* Username (Unique Key) */,
CONSTRAINT UI_test_EMail UNIQUE (EMail) /* This is an Index comment */
) /* and this is a Table comment, before the final semi-colon  */;

This will be kept exactly as-is in the SQL field of the schema table 
(main.sqlite_master) and is easy to parse out later, or use a standard 
tool that already does it. This is an example of the auto-generated 
schema documentation from sqlitespeed (www.sqlc.rifin.co.za) using 
exactly this method of commenting. It includes the actual SQL blocks so 
it's easy to see how the commenting gets parsed:

http://www.sqlc.rifin.co.za/SchemaDocExample1.html

Go directly to the "Cities" table to see the idea also applied to FK 
constraint and Index items.


I know this is not strictly what you need, but I understand the 
frustration of not having comments, so this is how we solved it, maybe 
something similar will work for you.


Cheers,
Ryan

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

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Gert Van Assche]

I usually add a table with comments to other tables and fields for this.
That does the trick for me.
Is there another way to do it?

2017-03-14 13:54 GMT+01:00 PICCORO McKAY Lenz :

> an important feature in a DB its the column field that gives to developers
> metadata info INDEPENDENT of the tecnologies used, due by this way with a
> simple text editor in generated script developer can read and use minimal
> info for understanding structure ...
>
> its a minimal feature need in a database, for many developers that make
> GOOD documentation!
>
>
> Lenz McKAY Gerardo (PICCORO)
> http://qgqlochekone.blogspot.com
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Gert Van Assche
Skype: gertva -- Mobile: +32 498 84 44 75
datamundi.be -- fairtradetranslation.com -- delifteducation.be
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Application is crashing while performing sqlite3_prepare_v2 with sqlite version 3.15.2 (intermittently)

[Daniel Anderson]

Hi Roshak,

Looks like your top function in that thread is sqlite3_prepare_v2

So I can assume 2 things:

1 - you started a thread directly on sqlite3_prepare_v2
2- you erase part of the stack trace.

if you did 1, I can only say this is not good!

if you did 2, then you are keeping information, which is bad, as missing
info prevent helping.

if you have multiple threads, the other threads info (stack trace) could be
useful for debugging, I would suggest attaching a debugger
and looking at the other threads when the crash happens

You are using multi thread application, you are probably  victim of:
 - a race condition.
- uninitialise thread local variable
- a bug in your code
- running out of memory
- etc...

look at your logs and pay attention to things like using same handle in
more than one thread
or warning messages.
also if you can reproduce the bug with a simple program, it will then be
easier to debug.





2017-03-14 22:37 GMT-04:00 ROCHAK GUPTA :

> Hi All,
>
> I am using sqlite as library in multi-threaded environment where multiple
> applications use sqlite queries to perform get/set operations on attributes
> available in sqlite db tables. Intermittently i am seeing applications
> crashing when get operation is performed which intern call
> sqlite3_prepare_v2.
> Crash is not frequent. One thing to notice that, I never hit this issue
> when i was using 3.8.10.2 for couple of months. First time i hit the issue
> only after updating 3.15.2 recently. Could anyone please help me with this?
> Following is the GDB back trace for the same. Please let me know if this is
> a known issue and if its fixed in latest sqlite version.
> #0 0x4ffc9494 in __aeabi_memcpy8 () at ../sysdeps/arm/armv7/
> multiarch/memcpy_impl.S:352
> #1  0x4194d514 in
> whereLoopXfer (pFrom=0x19a560, pTo=0x1c14d0, db=) at
> /usr/src/debug/sqlite3/3_3.15.2.0-r0/sqlite-autoconf-
> 3150200/sqlite3.c:129646
> #2 
> whereLoopInsert
> (pTemplate=pTemplate@entry=0x19a560, pBuilder=0x1c2858, pBuilder=0x1c2858)
> at /usr/src/debug/sqlite3/3_3.15.2.0-r0/sqlite-autoconf-
> 3150200/sqlite3.c:64414
> #3  0x419700f0 in
> whereLoopAddBtree (pBuilder=0x1c2858, pBuilder@entry=0x0,
> mPrereq=7281790634533488) at /usr/src/debug/sqlite3/3_3.15.
> 2.0-r0/sqlite-autoconf-3150200/sqlite3.c:130653
> #4  0x419892fc in
> whereLoopAddAll (pBuilder=0x0) at /usr/src/debug/sqlite3/3_3.15.
> 2.0-r0/sqlite-autoconf-3150200/sqlite3.c:131229
> #5 
> sqlite3WhereBegin
> (pParse=pParse@entry=0xbec43e68, pTabList=0x0, pTabList@entry=0x1c2850,
> pWhere=pWhere@entry=0xbec43cd0, pOrderBy=, pDistinctSet=0x1c2e50,
> wctrlFlags=48760, iAuxArg=320) at /usr/src/debug/sqlite3/3_3.15.
> 2.0-r0/sqlite-autoconf-3150200/sqlite3.c:1283
> #6  0x4198d8e0 in
> sqlite3Select (pParse=pParse@entry=0xbec43e68, p=, pDest=0x0, pDest@entry
> =0xbec43e08)
> at /usr/src/debug/sqlite3/3_3.15.2.0-r0/sqlite-autoconf-
> 3150200/sqlite3.c:119546
> #7  0x419b8358 in
> yy_reduce (yyruleno=, yypParser=0x1b2990) at /usr/src/debug/sqlite3/3_3.15.
> 2.0-r0/sqlite-autoconf-3150200/sqlite3.c:135340
> #8  sqlite3Parser
> (pParse=0xbec43e68, yyminor=..., yymajor=, yyp=) at
> /usr/src/debug/sqlite3/3_3.15.2.0-r0/sqlite-autoconf-
> 3150200/sqlite3.c:5377
> #9 
> sqlite3RunParser
> (pParse=pParse@entry=0xbec43e68, zSql=0xe8  address 0xe8>,
> zSql@entry=0x198248 "SELECT  FROM  WHERE
> "..., pzErrMsg=0x1c1dd0, pzErrMsg@entry=0xbec43e58) at
> /usr/src/debug/sqlite3/3_3.15.2.0-r0/sqlite-autoconf-
> 3150200/sqlite3.c:6324
> #10  0x419bdae8
> in sqlite3Prepare (db=db@entry=0x19be78,
> zSql=zSql@entry=0x198248 "SELECT  FROM  WHERE
>  "..., nBytes=nBytes@entry=233, saveSqlFlag=-1094435176,
> saveSqlFlag@entry=1, pReprepare=pReprepare@entry=0x0, ppStmt=ppStmt@entry=
> 0xbec440d8, pzTail=pzTail@entry=0x0) at /usr/src/debug/sqlite3/3_3.15.
> 2.0-r0/sqlite-autoconf-3150200/sqlite3.c:114126
> #11  0x419bdfb4
> in sqlite3LockAndPrepare (db=0x19be78,
> zSql=0x198248 "SELECT  FROM  WHERE "...,
> nBytes=233, saveSqlFlag=1, pOld=0x0, ppStmt=0xbec440d8, pzTail=0x0) at
> /usr/src/debug/sqlite3/3_3.15.2.0-r0/sqlite-autoconf-
> 3150200/sqlite3.c:114217
> #12  0x419be27c
> in sqlite3_prepare_v2 (db=,
> zSql=zSql@entry=0x198248 "SELECT  FROM  WHERE
>  "..., nBytes=, ppStmt=ppStmt@entry=0xbec440d8,
> pzTail=pzTail@entry=0x0) at /usr/src/debug/sqlite3/3_3.15.
> 2.0-r0

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Clemens Ladisch]

PICCORO McKAY Lenz wrote:
> an important feature in a DB its the column field that gives to developers
> metadata info INDEPENDENT of the tecnologies used, due by this way with a
> simple text editor in generated script developer can read and use minimal
> info for understanding structure ...

There is no widely accepted standard for comments in SQL, except /* actual
comments */, and neither is there one for metadata, except as actual data
in your own metadata table(s).  Adding some non-standard mechanism would
not allow anything that isn't already possible.


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

Re: [sqlite] bounded id

[Clemens Ladisch]

Jan Danielsson wrote:
> I can't make the rowid a uint32_t, but that's essentially the
> behavior I'm looking for

CREATE TABLE transfers (
ID  INTEGER PRIMARY KEY  CHECK (ID BETWEEN 0 AND 4294967295),
[...]
);

But if you want the values to wrap around after old ones have been
deleted, you have to implement it yourself.  (A random search is
efficient as long as the set of actually used IDs is sparse.)


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

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Chris Locke]

Just add a 'comments' table.  Seems a lot of extra work and 'extra tools'
needed to read the comments, which could potentially be missed.
Add a 'comments' table with a 'comment' field which you can even add dates,
usernames, etc, to.

Thanks,
Chris

On Wed, Mar 15, 2017 at 11:12 AM, Clemens Ladisch 
wrote:

> PICCORO McKAY Lenz wrote:
> > an important feature in a DB its the column field that gives to
> developers
> > metadata info INDEPENDENT of the tecnologies used, due by this way with a
> > simple text editor in generated script developer can read and use minimal
> > info for understanding structure ...
>
> There is no widely accepted standard for comments in SQL, except /* actual
> comments */, and neither is there one for metadata, except as actual data
> in your own metadata table(s).  Adding some non-standard mechanism would
> not allow anything that isn't already possible.
>
>
> Regards,
> Clemens
> ___
> 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] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Brian Curley]

The sqlite_master table will always preserve any comments embedded between
the "CREATE" and ";" keywords for a given table definition. Is this not
sufficient?

You can parse the sql for a table's record to retrieve comments, in
whichever format you're using. I know that SQLite supports both single line
("-- ..") and multi-line ("/* .. */") forms of comments, so your parsing
might need to handle either/both according to your style book.

Regards.

Brian P Curley



On Wed, Mar 15, 2017 at 7:40 AM, Chris Locke 
wrote:

> Just add a 'comments' table.  Seems a lot of extra work and 'extra tools'
> needed to read the comments, which could potentially be missed.
> Add a 'comments' table with a 'comment' field which you can even add dates,
> usernames, etc, to.
>
> Thanks,
> Chris
>
> On Wed, Mar 15, 2017 at 11:12 AM, Clemens Ladisch 
> wrote:
>
> > PICCORO McKAY Lenz wrote:
> > > an important feature in a DB its the column field that gives to
> > developers
> > > metadata info INDEPENDENT of the tecnologies used, due by this way
> with a
> > > simple text editor in generated script developer can read and use
> minimal
> > > info for understanding structure ...
> >
> > There is no widely accepted standard for comments in SQL, except /*
> actual
> > comments */, and neither is there one for metadata, except as actual data
> > in your own metadata table(s).  Adding some non-standard mechanism would
> > not allow anything that isn't already possible.
> >
> >
> > Regards,
> > Clemens
> > ___
> > 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] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Simon Slavin]

On 15 Mar 2017, at 1:27pm, Brian Curley  wrote:

> The sqlite_master table will always preserve any comments embedded between
> the "CREATE" and ";" keywords for a given table definition. Is this not
> sufficient?

I always found that interesting.  I would have thought that the parser for 
SQLite would strip out those comments before the stage at which the CREATE 
commands were stored.  However, it’s not true.

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

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[PICCORO McKAY Lenz]

hey their'e WIDELY USED keyword and practice in all mayor DBMS

so can introduce a feature that converts all parts that have COMMENT '(\*)'
to /* COMMENT */ and stored? in the master part of the Database?

and NOTED THAT no many users comes here due the complicated behavior of
report an issue...

so then some bugs have been her for years due that..

this feature are widelly need for many developers, (make a search in
google) but due the asking for feature request are so complicated.. same
for bug reports

sorry guys but must be better in this behavior

Lenz McKAY Gerardo (PICCORO)
http://qgqlochekone.blogspot.com

2017-03-15 10:27 GMT-04:00 Simon Slavin :

>
> On 15 Mar 2017, at 1:27pm, Brian Curley  wrote:
>
> > The sqlite_master table will always preserve any comments embedded
> between
> > the "CREATE" and ";" keywords for a given table definition. Is this not
> > sufficient?
>
> I always found that interesting.  I would have thought that the parser for
> SQLite would strip out those comments before the stage at which the CREATE
> commands were stored.  However, it’s not true.
>
> 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] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Simon Slavin]

On 15 Mar 2017, at 2:30pm, PICCORO McKAY Lenz  wrote:

> so can introduce a feature that converts all parts that have COMMENT '(\*)'
> to /* COMMENT */ and stored? in the master part of the Database?

It is unlikely that this will happen in SQLite3.  What Brian is telling you is 
that /you/ can recall the command made to create the table, and parse comments 
in it yourself.  To get a list of all tables in the database, use the following 
SELECT command:

SELECT name,sql FROM sqlite_master
WHERE type='table'
ORDER BY name;

To get the CREATE command for a particular table use

SELECT sql FROM sqlite_master
WHERE name='MyTable';

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

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Bart Smissaert]

Maybe it is simpler (no parsing needed) to have an extra table with a
unique column holding tablename_fieldname
and a second column holding the comment for that field.

RBS



On 15 Mar 2017 10:35, "R Smith"  wrote:

>
> On 2017/03/14 2:54 PM, PICCORO McKAY Lenz wrote:
>
>> an important feature in a DB its the column field that gives to developers
>> metadata info INDEPENDENT of the tecnologies used, due by this way with a
>> simple text editor in generated script developer can read and use minimal
>> info for understanding structure ...
>>
>> its a minimal feature need in a database, for many developers that make
>> GOOD documentation!
>>
>
> I know this is not implementing the feature, but may I suggest some way to
> achieve the same.
>
> What we do (typically), since SQLite supports C-type comment blocks /* ...
> */, is to add comment lines to the schema and they are preserved correctly.
> For example:
>
> CREATE TABLE "test" (
>   "ID" INTEGER /* Here we add column comments */,
>   "Name" TEXT /* Note the comma is AFTER the comment */,
>   "EMail" TEXT COLLATE NOCASE /* Username (Unique Key) */,
> CONSTRAINT UI_test_EMail UNIQUE (EMail) /* This is an Index comment */
> ) /* and this is a Table comment, before the final semi-colon  */;
>
> This will be kept exactly as-is in the SQL field of the schema table
> (main.sqlite_master) and is easy to parse out later, or use a standard tool
> that already does it. This is an example of the auto-generated schema
> documentation from sqlitespeed (www.sqlc.rifin.co.za) using exactly this
> method of commenting. It includes the actual SQL blocks so it's easy to see
> how the commenting gets parsed:
> http://www.sqlc.rifin.co.za/SchemaDocExample1.html
>
> Go directly to the "Cities" table to see the idea also applied to FK
> constraint and Index items.
>
> I know this is not strictly what you need, but I understand the
> frustration of not having comments, so this is how we solved it, maybe
> something similar will work for you.
>
> Cheers,
> Ryan
>
> ___
> 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] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Simon Slavin]

On 15 Mar 2017, at 2:45pm, Bart Smissaert  wrote:

> Maybe it is simpler (no parsing needed) to have an extra table with a
> unique column holding tablename_fieldname
> and a second column holding the comment for that field.

It’s common to see a four column table, with the columns being

entityType
theTable
theName
theComment

(I picked column names that suit me.  I don’t know of a standard.  Use of 'the' 
is to make sure you aren’t using reserved words as entity names.)

This allows you to comment things like indexes and triggers as well as columns.

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

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Richard Hipp]

On 3/15/17, Simon Slavin  wrote:
>
> It’s common to see a four column table, with the columns being
>
> entityType
> theTable
> theName
> theComment
>

This approach has the advantage of being portable across *all* SQL
database engines, whereas the COMMENT ON command is (as far as I could
discern from Google) only available on Oracle and PostgreSQL.   This
approach also makes the comments easy to introspect from applications,
and update using general-purpose query tools.
-- 
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] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[PICCORO McKAY Lenz]

the idea its that many tools generated (due portability and compability)
sql script with a optional keyword COMMENT on each column definition..

this its xxtremely usefull for selft container documentation for many
console users and rapid development

so manual comment using C-like cannot do that, if i have a large set of
DB's with minimal of 20 tables, its widelly tedious manage comments in that
way that some here sugest me.. and later joint the db files for work?

.. and stop of recomend me stupid guindows tools like sqlitespeed, does not
sqlite are Public Domain thanks god and aliens, but i hate all the
protected breaking-portability like all the guindows progs, the main reason
that's why we have problems porting apps

Lenz McKAY Gerardo (PICCORO)
http://qgqlochekone.blogspot.com

2017-03-15 11:05 GMT-04:00 Richard Hipp :

> On 3/15/17, Simon Slavin  wrote:
> >
> > It’s common to see a four column table, with the columns being
> >
> > entityType
> > theTable
> > theName
> > theComment
> >
>
> This approach has the advantage of being portable across *all* SQL
> database engines, whereas the COMMENT ON command is (as far as I could
> discern from Google) only available on Oracle and PostgreSQL.   This
> approach also makes the comments easy to introspect from applications,
> and update using general-purpose query tools.
> --
> 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] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Bob Friesenhahn]

On Wed, 15 Mar 2017, R Smith wrote:


What we do (typically), since SQLite supports C-type comment blocks /* ... 
*/, is to add comment lines to the schema and they are preserved correctly. 
For example:


CREATE TABLE "test" (
 "ID" INTEGER /* Here we add column comments */,
 "Name" TEXT /* Note the comma is AFTER the comment */,
 "EMail" TEXT COLLATE NOCASE /* Username (Unique Key) */,
CONSTRAINT UI_test_EMail UNIQUE (EMail) /* This is an Index comment */
) /* and this is a Table comment, before the final semi-colon  */;

This will be kept exactly as-is in the SQL field of the schema table 
(main.sqlite_master) and is easy to parse out later, or use a standard tool


Are these comments loaded into memory used by each program which 
connects to the database?  If so, more resources are consumed.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Richard Hipp]

On 3/15/17, Bob Friesenhahn  wrote:
> On Wed, 15 Mar 2017, R Smith wrote:
>>
>> CREATE TABLE "test" (
>>  "ID" INTEGER /* Here we add column comments */,
>>  "Name" TEXT /* Note the comma is AFTER the comment */,
>>  "EMail" TEXT COLLATE NOCASE /* Username (Unique Key) */,
>> CONSTRAINT UI_test_EMail UNIQUE (EMail) /* This is an Index comment */
>> ) /* and this is a Table comment, before the final semi-colon  */;
>>
>> This will be kept exactly as-is in the SQL field of the schema table
>> (main.sqlite_master) and is easy to parse out later, or use a standard
>> tool
>
> Are these comments loaded into memory used by each program which
> connects to the database?  If so, more resources are consumed.

The comments are loaded into memory temporarily while the CREATE TABLE
statement is being parsed when the connection is first opened, but
they are not held in memory long-term.  The text of the CREATE TABLE
is freed as soon as the schema parse completes.  So there is no extra
long-term memory usage.
-- 
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] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[R Smith]

On 2017/03/15 5:15 PM, PICCORO McKAY Lenz wrote:


so manual comment using C-like cannot do that, if i have a large set of
DB's with minimal of 20 tables, its widelly tedious manage comments in that
way that some here sugest me.. and later joint the db files for work?


Well, the advantage of having comments in DB tables (as some suggested) 
is also that you have the entire SQL language functions available to 
manipulate the comments - which you don't have when they are treated 
like Meta-data.
That said, only some DBs include comment in-schema specifiers, like 
Postgres and MySQL as Richard pointed out, in MSSQL you have to add a 
comment by a whole other mechanism. There is no standard for it. And 
even where these DBs do keep comments, they are all in large SCHEMA 
tables kept with per-column entries and the like. SQLite doesn't keep a 
per-column schema table, but you may.




.. and stop of recomend me stupid guindows tools like sqlitespeed, does not
sqlite are Public Domain thanks god and aliens, but i hate all the
protected breaking-portability like all the guindows progs, the main reason
that's why we have problems porting apps


Easy sailor... There is no need for you use that specific "stupid 
guindows tool", it's simply that the tool had solved the comment problem 
by parsing the schema, and you could do something like it.


Also, neither God nor any Aliens had anything to do with the development 
of SQLite. I suppose if they did we would have had this before the 
atomic bomb - and how much you "hate" anything is of no concern. If you 
can provide a good reasoned approach to solving the problem, people will 
listen, and even now the devs will probably be able to say:


Your request has been noted, Thank you for the suggestion.

Have a great day,
Ryan


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

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[R Smith]

On 2017/03/15 5:36 PM, Richard Hipp wrote:

On 3/15/17, Bob Friesenhahn  wrote:

On Wed, 15 Mar 2017, R Smith wrote:

CREATE TABLE "test" (
  "ID" INTEGER /* Here we add column comments */,
  "Name" TEXT /* Note the comma is AFTER the comment */,
  "EMail" TEXT COLLATE NOCASE /* Username (Unique Key) */,
CONSTRAINT UI_test_EMail UNIQUE (EMail) /* This is an Index comment */
) /* and this is a Table comment, before the final semi-colon  */;

This will be kept exactly as-is in the SQL field of the schema table
(main.sqlite_master) and is easy to parse out later, or use a standard
tool

Are these comments loaded into memory used by each program which
connects to the database?  If so, more resources are consumed.

The comments are loaded into memory temporarily while the CREATE TABLE
statement is being parsed when the connection is first opened, but
they are not held in memory long-term.  The text of the CREATE TABLE
is freed as soon as the schema parse completes.  So there is no extra
long-term memory usage.


I wonder, sqlite Devs, if a pragma or other adaption (such as the 
current pragma table_info()) or such could produce the same exact data 
but with an added field called "Comment" that simply gives the parsed 
comment from after each column definition (if any) like the above table 
example. This would probably be a very small adaptation, be completely 
backwards compatible, doesn't break any standard (since there isn't any) 
and answer the need expressed by this thread and others before it.



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

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Dominique Devienne]

On Wed, Mar 15, 2017 at 4:57 PM, R Smith  wrote:

> I wonder, sqlite Devs, if a pragma or other adaption (such as the current
> pragma table_info()) or such could produce the same exact data but with an
> added field called "Comment" that simply gives the parsed comment from
> after each column definition (if any) like the above table example. This
> would probably be a very small adaptation, be completely backwards
> compatible, doesn't break any standard (since there isn't any) and answer
> the need expressed by this thread and others before it.


That's one way to solve it, in a mostly BC (Backward Compatible) way.
(modulo the output from table_info() changing, which could be opt-in to
make it fully BC).

But given the HIDDEN key in vtables [1] "precedent", could also be an
explicit COMMENT 'some text' in the create table DDL itself, w/o resorting
to "significant" comments. --DD

[1] https://sqlite.org/vtab.html#hidden_columns_in_virtual_tables
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[Simon Slavin]

On 15 Mar 2017, at 4:09pm, Dominique Devienne  wrote:

> On Wed, Mar 15, 2017 at 4:57 PM, R Smith  wrote:
> 
>> I wonder, sqlite Devs, if a pragma or other adaption (such as the current
>> pragma table_info()) or such could produce the same exact data but with an
>> added field called "Comment" that simply gives the parsed comment from
>> after each column definition (if any) like the above table example. This
>> would probably be a very small adaptation, be completely backwards
>> compatible, doesn't break any standard (since there isn't any) and answer
>> the need expressed by this thread and others before it.
> 
> That's one way to solve it, in a mostly BC (Backward Compatible) way.
> (modulo the output from table_info() changing, which could be opt-in to
> make it fully BC).

Problem is, it requires parsing the CREATE command looking for comments in a 
certain format.  Notoriously difficult, considering that they can contain CR, 
LF, tab, and unforeseen Unicode characters.

I’m utterly against anything that tries to read C-style comments.  Comments are 
comments.  Computers are meant to ignore them to the point that they don’t even 
know they exist.

On the other hand, if we establish a standard for storing comments in database 
tables — which would require a consistent table name, column names, and values 
— it might take too much extra time to show those comments as an extra column 
in the response to PRAGMA tale_info() and similar PRAGMAs.  But I think it’s 
overkill.  Anyone who would want that would know how to retrieve the 
information.

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

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[PICCORO McKAY Lenz]

2017-03-15 11:49 GMT-04:00 R Smith :

> Well, the advantage of having comments in DB tables (as some suggested) is
> also that you have the entire SQL language functions available to
> manipulate the comments

that's one firts reason that rely on the second, comment can handle (with
the DBMS) the build in necesary documentation selft conained inside on the
DB, that its helfull for all, (very)


> - which you don't have when they are treated like Meta-data.
> That said, only some DBs include comment in-schema specifiers, like
> Postgres and MySQL as Richard pointed out,

make this in master db can make obvously incompatible and can made grow the
size depends of the comment size and amount of the tables and columns..
but, taking in considerations that today nobody of us take care about that
(machines are so powerfully right?) so makin a extra schema its enought


> Your request has been noted, Thank you for the suggestion.
>
many thanks! really appreciated!

this request was made prevously by another person, see archives.. many many
years ago
also was widelly requested in internet network by many years, but due the
very complicated behavior of the request / bugtraking system for sqlite..
no much was received here


> in MSSQL you have to add a comment by a whole other mechanism.

??? in my job we have complete SQL SERVER 2014 SP1 and performance need a
complete dell r600 and usage of the compelte 1T of RAM need extra
licences.. and more extra and extra "integrations" and then u mention that:

> Easy sailor... There is no need for you use that specific "stupid guindows
> tool", it's simply that the tool had solved the comment problem by parsing
> the schema, and you could do something like it.
>
we use (again) MS-like soft due "recomended", these king of
"recomendations" was the reason of the problem.. we have to paid, mid-usage
of a software we cannot migrate easy now! and we cannot use "complete"
today without a "grow-deep" dependence of the MS ...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

[PICCORO McKAY Lenz]

2017-03-15 12:24 GMT-04:00 Simon Slavin :

> Problem is, it requires parsing the CREATE command looking for comments in
> a certain format.  Notoriously difficult, considering that they can contain
> CR, LF, tab, and unforeseen Unicode characters.
>
well limit the comment to 255 chars and if any other non valid were found,
ignore it! like tabs, newlines, etc.. truncate


> I’m utterly against anything that tries to read C-style comments.
> Comments are comments.  Computers are meant to ignore them to the point
> that they don’t even know they exist.
>
> On the other hand, if we establish a standard for storing comments in
> database tables — which would require a consistent table name, column
> names, and values — it might take too much extra time to show those
> comments as an extra column in the response to PRAGMA tale_info() and
> similar PRAGMAs.  But I think it’s overkill.  Anyone who would want that
> would know how to retrieve the information.
>
and if db's configure it to by default do not show this "extra" information?


>
> Simon.
> _default do not show this "extra" _
> _
> 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] A CTE to count the records (rows) for each table

[Donald Griggs]

> >  Does anyone knows a Common Table Expression (CTE) to be used with the
> >  sqlite_master table so we can count for each table how many rows it
> >  has.

I wonder if it's always accurate to piggyback on the work of ANALYZE and
obtain row counts as of the last ANALYZE via:

   select tbl, max(substr(stat, 1, instr((stat || ' '), ' ') -1 )) from
sqlite_stat1 group by tbl order by tbl;

Equivalently, if one relies on CAST to obtain the first integer:

select tbl, max(cast (stat AS NUMERIC)) from sqlite_stat1 group by
tbl order by tbl;


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

Re: [sqlite] A CTE to count the records (rows) for each table

[Richard Hipp]

On 3/15/17, Donald Griggs  wrote:
>> >  Does anyone knows a Common Table Expression (CTE) to be used with the
>> >  sqlite_master table so we can count for each table how many rows it
>> >  has.
>
> I wonder if it's always accurate to piggyback on the work of ANALYZE and
> obtain row counts as of the last ANALYZE via:
>
>select tbl, max(substr(stat, 1, instr((stat || ' '), ' ') -1 )) from
> sqlite_stat1 group by tbl order by tbl;
>
> Equivalently, if one relies on CAST to obtain the first integer:
>
> select tbl, max(cast (stat AS NUMERIC)) from sqlite_stat1 group by
> tbl order by tbl;

The current ANALYZE always makes an exact row-count.  But there is
code on a branch
(https://www.sqlite.org/src/timeline?r=est_count_pragma) that only
does an approximation.  And that "approximate" ANALYZE may land on
trunk within the next release or two.

So, no, I would not trust the sqlite_stat1 data if you need an accurate count.

-- 
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] last_insert_rowid and FTS in 3.17

[Hugo Beauzée-Luyssen]

Hi,


Hi, I'm having some issues with last_insert_rowid starting with 3.17
Basically it seems to be returning the row inserted by a trigger,
instead of the explicitly inserted row.
As far as I understand, this contradicts the last_insert_rowid()
documentation.

I wrote a small test case to demonstrate the issue:

chouquette@nibbler cat /tmp/test.sql
CREATE TABLE IF NOT EXISTS Foo(
id_foo INTEGER PRIMARY KEY AUTOINCREMENT,
bar TEXT
);

CREATE VIRTUAL TABLE IF NOT EXISTS FooFts
USING FTS4(bar);

CREATE TRIGGER IF NOT EXISTS insert_fts
AFTER INSERT ON Foo
WHEN new.bar IS NOT NULL
BEGIN
INSERT INTO FooFts(rowid, bar) VALUES(new.id_foo, new.bar);
END;


BEGIN;
INSERT INTO Foo(id_foo) VALUES(NULL);
SELECT last_insert_rowid();
INSERT INTO Foo(id_foo) VALUES(NULL);
SELECT last_insert_rowid();
COMMIT;
SELECT last_insert_rowid();

BEGIN;
INSERT INTO Foo(id_foo, bar) VALUES(NULL, "otter");
COMMIT;
SELECT last_insert_rowid();

SELECT * FROM sqlite_sequence;

chouquette@nibbler ./sqlite3 --version
3.16.2 2017-01-06 16:32:41 a65a62893ca8319e89e48b8a38cf8a59c69a8209
chouquette@nibbler ./sqlite3 < /tmp/test.sql
1
2
2
3
Foo|3

chouquette@nibbler ~/dev/prefix/bin/sqlite3 --version
3.17.0 2017-02-13 16:02:40 ada05cfa86ad7f5645450ac7a2a21c9aa6e57d2c
chouquette@nibbler ~/dev/prefix/bin/sqlite3 < /tmp/test.sql
1
2
2
1
Foo|3

Has the behavior changed without being documented (or did I miss the
change?), or is this indeed a bug?

Thanks a lot in advance,

Regards,


-- 
  Hugo Beauzée-Luyssen
  h...@beauzee.fr
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] last_insert_rowid and FTS in 3.17

[Richard Hipp]

On 3/15/17, Hugo Beauzée-Luyssen  wrote:
> Hi, I'm having some issues with last_insert_rowid starting with 3.17
> Basically it seems to be returning the row inserted by a trigger,
> instead of the explicitly inserted row.

Please try the latest pre-release snapshot at
https://www.sqlite.org/download.html and let us know whether or not it
fixes your problem.
-- 
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] last_insert_rowid and FTS in 3.17

[Keith Medcalf]

Head of trunk certainly fixes it ...

SQLite version 3.18.0 2017-03-15 19:11:29
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select sqlite_source_id();
2017-03-15 19:11:29 
b1b1aa8b69aa80c83aec3380565f0b4ec0b6a6e033537becee098872da362e9a
sqlite> CREATE TABLE IF NOT EXISTS Foo(
   ...> id_foo INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> bar TEXT
   ...> );
sqlite>
sqlite> CREATE VIRTUAL TABLE IF NOT EXISTS FooFts
   ...> USING FTS4(bar);
sqlite>
sqlite> CREATE TRIGGER IF NOT EXISTS insert_fts
   ...> AFTER INSERT ON Foo
   ...> WHEN new.bar IS NOT NULL
   ...> BEGIN
   ...> INSERT INTO FooFts(rowid, bar) VALUES(new.id_foo, new.bar);
   ...> END;
sqlite>
sqlite>
sqlite> BEGIN;
sqlite> INSERT INTO Foo(id_foo) VALUES(NULL);
sqlite> SELECT last_insert_rowid();
1
sqlite> INSERT INTO Foo(id_foo) VALUES(NULL);
sqlite> SELECT last_insert_rowid();
2
sqlite> COMMIT;
sqlite> SELECT last_insert_rowid();
2
sqlite>
sqlite> BEGIN;
sqlite> INSERT INTO Foo(id_foo, bar) VALUES(NULL, "otter");
sqlite> COMMIT;
sqlite> SELECT last_insert_rowid();
3
sqlite>
sqlite> SELECT * FROM sqlite_sequence;
Foo|3
sqlite>
sqlite>

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Richard Hipp
> Sent: Wednesday, 15 March, 2017 18:01
> To: SQLite mailing list
> Subject: Re: [sqlite] last_insert_rowid and FTS in 3.17
> 
> On 3/15/17, Hugo Beauzée-Luyssen  wrote:
> > Hi, I'm having some issues with last_insert_rowid starting with 3.17
> > Basically it seems to be returning the row inserted by a trigger,
> > instead of the explicitly inserted row.
> 
> Please try the latest pre-release snapshot at
> https://www.sqlite.org/download.html and let us know whether or not it
> fixes your problem.
> --
> 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] Is it possible to change "synchronous" flag of ZIPVFS pager?

[Tomasz Maj]

Hi,


According to my observations, "PRAGMA synchronous=...;" query affects only the 
standard sqlite pager. But for zipped databases sqlite uses additional ZIPVFS 
pager layer which actually decide whether and when to sync content of files 
associated with a database. It looks like "PRAGMA synchronous=...;" query 
doesn't have any effect on zipped databases. Are my observations right? If so, 
is it possible to manipulate "synchronous" flag of ZIPVFS pager?


Cheers,

Tomasz M

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