Re: [sqlite] 'replace ... do update...' crash

[Richard Hipp]

Thanks for the bug report.  Now fixed on trunk and in the latest
pre-release snapshot.

https://www.sqlite.org/src/info/359725ab36339b44

On 4/20/18, Abroży Nieprzełoży  wrote:
> sqlite> .version
> SQLite 3.24.0 2018-04-20 18:01:31
> 279c48f6061f766f5437edd6964c0dd1e10399314eb17b6e5ee34df925a7alt1
> zlib version 1.2.11
> msvc-1912
>
>
> CREATE TABLE abc(
> a TEXT UNIQUE,
> b TEXT UNIQUE
> );
> CREATE TABLE tt(
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> x TEXT
> );
> CREATE TRIGGER bef_ins BEFORE INSERT ON abc BEGIN INSERT INTO tt(x)
> VALUES('bi'); END;
> INSERT INTO abc(a, b) VALUES('X', 'Y'),('1', '2');
>
> REPLACE INTO abc(a, b) VALUES('X', 'Z'),('9', '2') ON CONFLICT(b) DO
> UPDATE SET b='8'; --crash
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] 'replace ... do update...' crash

[Abroży Nieprzełoży]

sqlite> .version
SQLite 3.24.0 2018-04-20 18:01:31
279c48f6061f766f5437edd6964c0dd1e10399314eb17b6e5ee34df925a7alt1
zlib version 1.2.11
msvc-1912


CREATE TABLE abc(
a TEXT UNIQUE,
b TEXT UNIQUE
);
CREATE TABLE tt(
id INTEGER PRIMARY KEY AUTOINCREMENT,
x TEXT
);
CREATE TRIGGER bef_ins BEFORE INSERT ON abc BEGIN INSERT INTO tt(x)
VALUES('bi'); END;
INSERT INTO abc(a, b) VALUES('X', 'Y'),('1', '2');

REPLACE INTO abc(a, b) VALUES('X', 'Z'),('9', '2') ON CONFLICT(b) DO
UPDATE SET b='8'; --crash
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Tagging" SQLite tables with attributes for introspection.

[J Decker]

You could make a extra table that has a foreign key on sqlite_master and
add extra attributes...
You could add comments  ( -- ) in the create statemtents; and parse those
back out ...


On Fri, Apr 20, 2018 at 3:52 PM, Randall Smith 
wrote:

> I'm writing some code to do processing of some (but not quite all) of the
> tables in my SQLite database.  I could make this a lot easier and more
> general purpose if I could somehow attach an attribute to the various
> tables that indicated whether that table should be processed.
>
> Other than, say, keying off something in the table name or something which
> is a pain because all the table names are already set in stone, is there
> any way good of doing this?  I was hoping there was something in, say, the
> sqlite_master table I could intentionally affect and then use later.  But
> nothing jumps out.  Maybe define some kind of bogus index for the table I
> could check for?  But I'm hoping there's something with more grace and
> beauty.
>
> Thanks for any ideas or suggestions.
>
> Randall.
>
> ___
> 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] "Tagging" SQLite tables with attributes for introspection.

[Randall Smith]

I'm writing some code to do processing of some (but not quite all) of the 
tables in my SQLite database.  I could make this a lot easier and more general 
purpose if I could somehow attach an attribute to the various tables that 
indicated whether that table should be processed.

Other than, say, keying off something in the table name or something which is a 
pain because all the table names are already set in stone, is there any way 
good of doing this?  I was hoping there was something in, say, the 
sqlite_master table I could intentionally affect and then use later.  But 
nothing jumps out.  Maybe define some kind of bogus index for the table I could 
check for?  But I'm hoping there's something with more grace and beauty.

Thanks for any ideas or suggestions.

Randall.

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

[sqlite] Patch for Lemon that adds a command line option to allow specifying the output directory

[Adrian Perez de Castro]

Hi everybody,

(Please keep CC'd in replies, as I am not subscribed to the mailing list.)

As part of a personal project I made a small patch for Lemon which adds
support for a "-d" command line option, which allows specifying the
directory where the output files are to be placed. You can grab the patch
here:

  https://people.igalia.com/aperez/files/lemon-add-outdir-flag.patch

This addition was motivated by the usage of the Meson [1] build system,
which always does out-of-tree builds, with all the generated source files
being places inside the build directory.

Once the patch above is applied, it is possible to use Lemon as follows
inside a "meson.build" file:

  lemon_exe = executable('lemon', 'lemon.c',
  install: false, build_by_default: false)

  lemon = generator(lemon_exe, output: ['@BASENAME@.c', '@BASENAME.h@],
  arguments: [ '-T' + join_paths(meson.current_source_dir(), 'lempar.c'),
  '-d@BUILD_DIR@', @INPUT@ ])

  exe = executable('a.out', 'main.c', lemon.process('parse.y'))

I imagine the "-d" option may be useful for others using Lemon, and
it would be great if you could include the changes proposed in the patch
(or something similar).

Last but not least, thanks for publishing the source code for Lemon along
with the rest of SQLite — they are very fine pieces of software.

Best regards,

--
 Adrián 

[1] http://mesonbuild.com/


pgpbtIbWxh83O.pgp
Description: PGP signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPSERT available in pre-release

[Sylvain Pointeau]

I full agree with Petite Abeille.
This upsert is quite limited, we can only insert one row on conflict update
one row.
Even so it is a big improvement versus the insert or replace, this is far
from the merge from the SQL standard where we can insert or update multiple
rows in one query.

I am already super mega happy to think about using this new upsert for
sure, but could you implement the merge from the SQL standard?

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

Re: [sqlite] UPSERT available in pre-release

[Richard Hipp]

Please try again with the latest pre-release snapshot.

On 4/19/18, Quan Yong Zhai  wrote:
> Dear Richard,
>
> I modified the wordcount.c in SQLite/test directory, to use the new upsert
> command:
>
>INSERT INTO wordcount(word,cnt) VALUES(?1,1) ON CONFLICT(word) DO UPDATE
> SET cnt=cnt+1
>
> Before:
>
>wordcount --all :memory: sqlite3.c
>
>   2.406 wordcount --insert
>
>   2.296 wordcount --insert --without-rowid
>
> After:
>
> wordcount --all :memory: sqlite3.c
>
>   1.701 wordcount --insert
>
>   3.547 wordcount --insert --without-rowid
>
>
>
> As you can see, it’s very strangely ,in the table with rowid, the upsert
> improved a lot, but in the table without rowidd, it’s slower than the origin
> sql.
>
>
>
>
>
> Sent from Mail for Windows
> 10
>
>
>
> 
> From: sqlite-users  on behalf
> of Richard Hipp 
> Sent: Thursday, April 19, 2018 6:29:55 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] UPSERT available in pre-release
>
> The latest pre-release snapshot [1] contains support for UPSERT
> following the PostgreSQL syntax.  The documentation is still pending.
> Nevertheless, early feedback is welcomed.  You can respond either to
> this mailing list, or directly to me.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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