Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Domingo Alvarez Duarte]

Hello James !

Definitely you've made a very good point !

Thumbs up !

Cheers !

On 16/3/20 17:37, James K. Lowden wrote:

On Fri, 13 Mar 2020 11:22:46 -0400
Richard Hipp  wrote:


4.  Your email address is never displayed, even to subscribers.

While I have no illusions of altering the online trajectory of this ML,
I'd like to point out that email addresses foster community.

Studies and experience both show that the incidental availability of
email addresses invites the formation of new relationships through
off-list communication. Speaking for myself, that opportunity has
manifested several new connections and even professional
opportunities.

I value those relationships.  I'm sorry this forum will cease to be a
place where new ones will develop.

--jkl
___
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] New SQLite Forum established - this mailing list is deprecated

[Domingo Alvarez Duarte]

Hello Richard !

I like the concept of fossil including the forum functionality !

With that said we are in a database forum and most of us know that one 
common problem/task with databases is migration, there is any 
plan/attempt to migrate the actual mailing list to the new fossil forum ?


That would be a good way to see how the fossil forum would perform with 
some non trivial amount of data and would be an example that can attract 
more users/conversions from other forum platforms.


Cheers !

On 12/3/20 21:17, Richard Hipp wrote:

I have set up an on-line forum as a replacement for this mailing list:

 https://sqlite.org/forum
 https://www.sqlite.org/forum/forumpost/a6a27d79ac

Please consider subscribing to the new Forum.  The intent is that the
forum will eventually replace this mailing list.

The Forum is powered by Fossil.  It has been in active use in the
Fossil community for a couple of years, and has worked well.  See the
second link above for more information.


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

Re: [sqlite] Compiling on AIX OS

[Domingo Alvarez Duarte]

Hello and thank you for reply !

As I said it's an exercise to know the peculiarities of compiling on 
this platform and I'm exposing this experience here in hope it can help 
others and improve sqlite build system.


If what you mention is correct about requiring tcl 8.5 or newer then the 
configure procedure probably should check this and report the problem.


Cheers !

On 26/2/20 10:27, Shawn Wagner wrote:

I'm pretty sure the full build scripts require tcl 8.5 or newer. Maybe try
the -amalgamation or -autoconf versions?

On Wed, Feb 26, 2020 at 1:21 AM Domingo Alvarez Duarte 
wrote:


Hello !



$ uname -a
AIX minimal 1 7 00C63E504B00
$ tclsh
% puts $tcl_patchLevel
8.4.7
%


On 26/2/20 10:19, Shawn Wagner wrote:

puts $tcl_patchLevel

___
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] Compiling on AIX OS

[Domingo Alvarez Duarte]

Hello !



$ uname -a
AIX minimal 1 7 00C63E504B00
$ tclsh
% puts $tcl_patchLevel
8.4.7
%


On 26/2/20 10:19, Shawn Wagner wrote:

puts $tcl_patchLevel

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

Re: [sqlite] Compiling on AIX OS

[Domingo Alvarez Duarte]

Going forward after fixing the access mode from "rb" to "r" we get this 
error (tclsh8.4):




tclsh /home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl >shell.c
cp /home/mingo/dev/sqlite-src-3310100/ext/fts5/fts5parse.y .
rm -f fts5parse.h
./lemon  -S fts5parse.y
tclsh /home/mingo/dev/sqlite-src-3310100/ext/fts5/tool/mkfts5c.tcl
bad index "19-1": must be integer or end?-integer?
    while executing
"string range $date 0 [string last . $date]-1"
    (procedure "fts5_source_id" line 7)
    invoked from within
"fts5_source_id $::srcdir"
    (procedure "fts5c_printfile" line 7)
    invoked from within
"fts5c_printfile $f "
    ("foreach" body line 1)
    invoked from within
"foreach f $G(src) { fts5c_printfile $f }"
    (file 
"/home/mingo/dev/sqlite-src-3310100/ext/fts5/tool/mkfts5c.tcl" line 112)

gmake: *** [Makefile:1188: fts5.c] Error 1


On 26/2/20 10:05, Domingo Alvarez Duarte wrote:

Hello !

Trying to compile https://sqlite.org/2020/sqlite-src-3310100.zip on 
AIX 7.1 operating system for exercise and to know the peculiarities of 
building software on this OS and I'm getting this error, somehow tclsh 
that is installed on this OS do not wrap/hide OS incompatibilities.




tclsh /home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl >shell.c
illegal access mode "rb"
    while executing
"open $topdir/src/shell.c.in rb"
    invoked from within
"set in [open $topdir/src/shell.c.in rb]"
    (file "/home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl" line 32)
gmake: *** [Makefile:1079: shell.c] Error 1



Cheers !


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

[sqlite] Compiling on AIX OS

[Domingo Alvarez Duarte]

Hello !

Trying to compile https://sqlite.org/2020/sqlite-src-3310100.zip on AIX 
7.1 operating system for exercise and to know the peculiarities of 
building software on this OS and I'm getting this error, somehow tclsh 
that is installed on this OS do not wrap/hide OS incompatibilities.




tclsh /home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl >shell.c
illegal access mode "rb"
    while executing
"open $topdir/src/shell.c.in rb"
    invoked from within
"set in [open $topdir/src/shell.c.in rb]"
    (file "/home/mingo/dev/sqlite-src-3310100/tool/mkshellc.tcl" line 32)
gmake: *** [Makefile:1079: shell.c] Error 1



Cheers !

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

[sqlite] www3.sqlite.org 500 server malfunction

[Domingo Alvarez Duarte]

Hello Richard !

I'm still getting this error:



fossil clone http://www3.sqlite.org/cgi/src sqlite.fossil
server says: 500 Server Malfunction
Clone done, sent: 256  received: 217  ip: 64.225.41.2
server returned an error - clone aborted



Cheers !

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

Re: [sqlite] SSL: cannot connect to host www3.sqlite.org:443 (Connection refused)

[Domingo Alvarez Duarte]

Hello Richard !

I'm no getting this:



sqlite3$ fossil update
Autosync:  https://www3.sqlite.org/cgi/src
server says: 500 Server Malfunction
Pull done, sent: 1295  received: 217  ip: 64.225.41.2
Autosync failed.
continue in spite of sync failure (y/N)? n
update abandoned due to sync failure



Cheers !

On 25/1/20 21:53, Richard Hipp wrote:

On 1/25/20, Richard Hipp  wrote:

On 1/25/20, Domingo Alvarez Duarte  wrote:

Hello Richard !

Since yesterday I'm getting this message when trying to use fossil for
sqlite3.

Yeah.  That machine went completely bonkers and I had to rebuild it
from scratch, using a new IP address.  And because it used a new IP
address, I have to wait 24 hours for the new DNS information to
propagate before LetsEncrypt will give me a new cert.

Both https://www3.sqlite.org/ and https://www3.fossil-scm.org/ should
be back up now.


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

[sqlite] SSL: cannot connect to host www3.sqlite.org:443 (Connection refused)

[Domingo Alvarez Duarte]

Hello Richard !

Since yesterday I'm getting this message when trying to use fossil for 
sqlite3.


Autosync:  https://www3.sqlite.org/cgi/src
SSL: cannot connect to host www3.sqlite.org:443 (Connection refused)

Cheers !

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

Re: [sqlite] A hang in Sqlite

[Domingo Alvarez Duarte]

Hello !

I understand the original point of view of this thread, but I'm glad 
that we have those submissions here because it makes me aware of 
people/tools/technics/patterns that can help in other projects.


Cheers !

On 6/1/20 15:44, Simon Slavin wrote:

On 6 Jan 2020, at 2:40pm, Yongheng Chen  wrote:


I am sorry if I was polluting the mail list. As nobody mentioned that before 
and reporting bugs to this mail list is what is said in the official website, I 
just keep doing this.

You are going what we told you to do.  It is our fault.  You are doing very 
well.

DRH has changed things and you may now send you email messages to 
b...@sqlite.org.  This should make more people more happy.
___
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] Patch that remove static/global variables from lemon

[Domingo Alvarez Duarte]

Hello Richard !

After you last changes to lemon to also generate an sql output form with 
the parser tables, I did a code refactoring in lemon moving all 
static/global variables to the "struct lemon" now it's a bit more closer 
to be usable as a library, still need verify memory leaks due to not 
calling deallocation on then.


See attached patch that I release with the same license as sqlite3 (free 
for any usage).


Cheers !

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

Re: [sqlite] sqlite syntax auto suggest

[Domingo Alvarez Duarte]

Hello Dominique !

Here are two views that can bring that info to a query:

==

CREATE VIEW rule_list_view AS
SELECT
    a.ruleid,
    b.name,
    a.lhs
FROM rule AS a
LEFT JOIN symbol AS b ON a.lhs = b.id;

CREATE VIEW rulerhs_list_view AS
SELECT
    a.ruleid,
    b.name as symbol_name,
    c.name as rule_name,
    a.pos,
    a.sym
FROM rulerhs AS a
LEFT JOIN symbol AS b ON a.sym = b.id
LEFT JOIN rule_list_view AS c ON a.ruleid = c.ruleid;

==

Maybe others can contribute with other views to make the info there 
easier to use !


Cheers !

On 29/11/19 14:56, Dominique Devienne wrote:

On Fri, Nov 29, 2019 at 2:10 PM Richard Hipp  wrote:


On 11/29/19, Laurent  wrote:

Could you perhaps explain what pos means and what the [isTerminal] =

0 rows mean.

New check-in enhances the output to include a comment in the SQL
before the encoding of each production rule.  This should help make it
clear what the SQL is trying to represent.


Hi. Why not add a column and write it to the SQL insert?

Rather than as a comment in the SQL "dump"? The first thing I'd do with
that new output file,
would be to run it to generate the DB, then look at the DB in a graphical
client, to side and dice
the data. As it stands, those useful comments would be lost in that
scenario. My $0.02. --DD
___
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] sqlite syntax auto suggest

[Domingo Alvarez Duarte]

Hello Richard !

Sorry by split my reply in so many small ones !

When trying to use the generate "parse.sql" I'm getting this:



sqlite3 parse.db < parse.sql
Error: near line 10: FOREIGN KEY constraint failed
Error: near line 11: FOREIGN KEY constraint failed
Error: near line 12: FOREIGN KEY constraint failed
Error: near line 13: FOREIGN KEY constraint failed



I have "foreign key" ON by default and added "BEGIN; ... COMMIT;" around 
the generated SQL.




BEGIN;
CREATE TABLE symbol(
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  isTerminal BOOLEAN NOT NULL,
  fallback INTEGER REFERENCES symbol
);
INSERT INTO symbol(id,name,isTerminal,fallback)VALUES(0,'$',TRUE,NULL);

...

INSERT INTO rule(ruleid,lhs)VALUES(384,261);
COMMIT;



Cheers !

On 28/11/19 23:54, Richard Hipp wrote:

On 11/28/19, Laurent Dhont  wrote:

is there an API to
get this information in a format that is not an image?

By coincidence, I checked in a change two days ago that might be
helpful.  See https://www.sqlite.org/src/timeline?c=4dbd398d640852d4
for the specific check-in.  If you now build SQLite from canonical
sources, the file "parse.sql" will be left in the build directory.
That file contains SQL text that initializes three SQL tables, the
content of which describe the context-free language grammar that
SQLite uses to parse its SQL input.

There is no documentation of this, but if you have some familiarity
with grammars and parsing and tools like Yacc/Bison or Lemon, then you
should be able to figure it out.

To be clear, I do not expect that the parse.sql file is directly
usable by your application in its current form.  But it is a
machine-readable grammar description, that you can perhaps transform
into a useful format using a script.

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

Re: [sqlite] sqlite syntax auto suggest

[Domingo Alvarez Duarte]

Hello Richard !

Again after rereading your reply and trying to use the "parse.sql" I 
noticed that it doesn't have a "begin;../commit;" wrapper as it's 
recommended to not having "fsync" calls on each insert.


Cheers !

On 28/11/19 23:54, Richard Hipp wrote:

On 11/28/19, Laurent Dhont  wrote:

is there an API to
get this information in a format that is not an image?

By coincidence, I checked in a change two days ago that might be
helpful.  See https://www.sqlite.org/src/timeline?c=4dbd398d640852d4
for the specific check-in.  If you now build SQLite from canonical
sources, the file "parse.sql" will be left in the build directory.
That file contains SQL text that initializes three SQL tables, the
content of which describe the context-free language grammar that
SQLite uses to parse its SQL input.

There is no documentation of this, but if you have some familiarity
with grammars and parsing and tools like Yacc/Bison or Lemon, then you
should be able to figure it out.

To be clear, I do not expect that the parse.sql file is directly
usable by your application in its current form.  But it is a
machine-readable grammar description, that you can perhaps transform
into a useful format using a script.

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

Re: [sqlite] sqlite syntax auto suggest

[Domingo Alvarez Duarte]

Hello Richard !

My bad after writing the last reply I found that "EXPLAIN" and "explain" 
are different symbols there.


Could a small comment be generated at the begin of the generated 
"parse.sql" ?


Something like: (dummy example)



---

-- This is a generated output from lemon parse generator

-- The symbol table can hold terminals and no terminals denoted by ...

-- ...



Cheers !

On 28/11/19 23:54, Richard Hipp wrote:

On 11/28/19, Laurent Dhont  wrote:

is there an API to
get this information in a format that is not an image?

By coincidence, I checked in a change two days ago that might be
helpful.  See https://www.sqlite.org/src/timeline?c=4dbd398d640852d4
for the specific check-in.  If you now build SQLite from canonical
sources, the file "parse.sql" will be left in the build directory.
That file contains SQL text that initializes three SQL tables, the
content of which describe the context-free language grammar that
SQLite uses to parse its SQL input.

There is no documentation of this, but if you have some familiarity
with grammars and parsing and tools like Yacc/Bison or Lemon, then you
should be able to figure it out.

To be clear, I do not expect that the parse.sql file is directly
usable by your application in its current form.  But it is a
machine-readable grammar description, that you can perhaps transform
into a useful format using a script.

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

Re: [sqlite] sqlite syntax auto suggest

[Domingo Alvarez Duarte]

Hello Richard !

I just looked at it and I have some doubts about the generated 
parse.sql, as I see it there is this table:




CREATE TABLE symbol(
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  isTerminal BOOLEAN NOT NULL,
  fallback INTEGER REFERENCES symbol
);

--and this entries

--...

INSERT INTO symbol(id,name,isTerminal,fallback)VALUES(2,'EXPLAIN',TRUE,59);

--...

INSERT INTO 
symbol(id,name,isTerminal,fallback)VALUES(185,'explain',FALSE,NULL);




Wouldn't it be better to have:



CREATE TABLE symbol(
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL COLLATE NOCASE UNIQUE,
  isTerminal BOOLEAN NOT NULL,
  fallback INTEGER REFERENCES symbol
);

--and this entries

--...

INSERT INTO 
symbol(id,name,isTerminal,fallback)VALUES(2,'EXPLAIN',TRUE,FALSE);




This way we can search for any combination of individual letter cases 
and find a unique match ?


Cheers !

On 28/11/19 23:54, Richard Hipp wrote:

On 11/28/19, Laurent Dhont  wrote:

is there an API to
get this information in a format that is not an image?

By coincidence, I checked in a change two days ago that might be
helpful.  See https://www.sqlite.org/src/timeline?c=4dbd398d640852d4
for the specific check-in.  If you now build SQLite from canonical
sources, the file "parse.sql" will be left in the build directory.
That file contains SQL text that initializes three SQL tables, the
content of which describe the context-free language grammar that
SQLite uses to parse its SQL input.

There is no documentation of this, but if you have some familiarity
with grammars and parsing and tools like Yacc/Bison or Lemon, then you
should be able to figure it out.

To be clear, I do not expect that the parse.sql file is directly
usable by your application in its current form.  But it is a
machine-readable grammar description, that you can perhaps transform
into a useful format using a script.

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

[sqlite] Does someone know any project like nsbase ?

[Domingo Alvarez Duarte]

Hello !

Looking at a thread on https://news.ycombinator.com/item?id=21401198 I 
saw a mention to http://www.nsbase.neuts.fr/en/ but could not find the 
project source code repository or even if it has one.


So my question is, does anyone know other projects like that (access 
like using sqlite) ?


Cheers !

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

[sqlite] SSL: cannot connect to host www3.sqlite.org:443 (Connection refused)

[Domingo Alvarez Duarte]

Hello Richard !

It's the second day that this is happening !

Cheers !

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

Re: [sqlite] importing a large TSV file

[Domingo Alvarez Duarte]

Hello Gert !

I normally do this (be aware that if there is a power outage the 
database is screwed):


===

PRAGMA synchronous = OFF;

begin;

--processing here

commit;

PRAGMA synchronous = ON;

===

Cheers !

On 1/4/19 13:14, Gert Van Assche wrote:

Hi all,

I need to create an SQLite db from a large TSV file. (30 GB)
Are there any setting I can give to the db so I can speed up the import?

thank you

Gert
___
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] Drop table error with existing views

[Domingo Alvarez Duarte]

Hello Graham !

Thank you so much !

With this pragma I can continue to solve my problem as before.

Cheers !

On 1/4/19 12:42, Graham Holden wrote:

PRAGMA legacy_alter_table=ON

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

[sqlite] Drop table error with existing views

[Domingo Alvarez Duarte]

Hello !

I'm using sqlite3 for a long time and I used to be able to drop tables 
that have views without error before but now it seems that it is not 
possible anymore.


I mainly use it to restructure tables (add/move/remove fields) with 
something like this:




PRAGMA foreign_keys=OFF;

BEGIN;

CREATE TABLE "banks___new"(
    id integer primary key,
    account_id integer references ledger_accounts(id),
    name varchar not null collate nocase unique,
    new_field integer, -- for example here I'm adding a new 
field

    notes text collate nocase
);

INSERT INTO "banks___new"(
    "id",
    "account_id",
    "name",
    "notes"
    )
SELECT
    "id",
    "account_id",
    "name",
    "notes"
FROM "banks";

DROP TABLE "banks";

ALTER TABLE "banks___new" RENAME TO "banks";

PRAGMA foreign_key_check;

COMMIT;

PRAGMA foreign_keys=ON;



As I said before it use to work in previous versions of sqlite3 but now 
it seems to not allow to drop tables that has views on then.


How can something like the above be achieved now without need to drop 
and recreate the views (can be several views), any new pragma that I'm 
not aware off ?


Cheers !

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

[sqlite] Fossil new mailing list doesn't seen to work

[Domingo Alvarez Duarte]

Hello !

I'm writing here because I've tried to create an account on 
fossil-scm.org and it doesn't seems to work.


I fill in all the fields and then send the form, it refresh the screen 
and gives no feedback about what happened, it created the acount ?


Then I try to login it says invalid userid/password.

Then I create another account again with same data it behaves the same, 
refresh the screen and gives no feedback, user already exists ? It 
create the account ?


Cheers !

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

[sqlite] Fossil problem ?

[Domingo Alvarez Duarte]

Hello !

I follow the fossil and sqlite3 repository frequently and times to times 
this thing happen again:


Suddenly the my repositories stop following trunk with no clear reason, 
like now the sqlite3 repository stopped following trunk for some days 
and I've tried several times merge the trunk in hope it'll be up to date 
and continue following trunk but no way to achieve it.


===

lack both primary and secondary files

===

And when executing "fossil server/ui" I can not see through the web 
interface where my repository got stuck, I'm not aware of any way to 
clearly/easily see where my repository state is in respect to trunk.


Someone know how to fix this and how to achieve see through the web 
interface where the repository is in respect to the timeline ?



Cheers !

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

Re: [sqlite] Ideas or Guide on SQLite Search Engine for a relational database?

[Domingo Alvarez Duarte]

Hello Scoott !

I have done some work with a prototyping tool for data applications 
using sqlite https://github.com/mingodad/db-api-server and use for 
navigate several opendata sources like (sorry it is in Spainsh) 
https://meimporta.eu look here 
https://borme.w.meimporta.eu/bormeMalaga-lm.app .


With this tool we can create links that make easy navigate/filter/search 
non trivial amounts of relational data.


I used this repository https://github.com/mingodad/db-api-server for a 
talk about prototyping database applications, I hope it can be helpful 
for you or other people with similar needs.


Cheers !

On 20/01/2019 18:02, Andy Goth wrote:

You can try reading the Fossil source code to see how it handles full-text
searching across multiple tables.

https://fossil-scm.org/index.html/artifact?fn=src/search.c=trunk

On Sat, Jan 19, 2019, 06:10 Scott 
I apologize, I sent this from a different email than I registered
accidentally.
Hi Everyone!
The Ask and what I’ve done:
I'm building a research database with an embedded SQLite DB using Intellij
for development. I've created the data entry point for the users; however,
I'm looking for anything anyone might want to share or suggest as a code,
link, or design, whatever it may be, on developing a search engine for a
relational database. I’ve been reading through tutorials on
SQLiteTutorials.net to learn a little more about indexing and searching.
I’ve come across the FTS5 (“Full Text Search”) tutorial, but I’m having
difficulty finding anything that may assist with a multi-table search. I
guess it would nice to have an example to follow. I wouldn’t think this
would be the first-time for this, but I’m a little naïve too.

I'm not asking for someone to solve the problem but just provide a
direction that may save a lot of research time. I don’t know if my images
will post or be available through this email, but I've provided some images
below of the GUI and the SQLite DB which has 8 tables all linking back to
the primary table called "Source". This is a new task for me and my
development skills in Java.

The Goal:
This Research DB users want to be able to search the database either by
Source, Topic, Question, Comments, Quotes, Authors, which most are a one to
many with one a many to many relationship. A single source, for example a
book entered in the database, could be linked to multiple topics,
questions, Comments, Quotes, etc. So the users may want to search each of
these separately on a particular subject, or possibly all the tables for a
particular subject. Topic will help the mostly, but a topic may be
addressed in a comment or quote, etc, that may not be associated directly
with a particular topic. So there is a need to search a number of fields
over 8 tables. My thoughts are that the search criteria should probably
return into a table list, then when the user selects a particular row, this
data would populate into corresponding fields for readability similar to
the "Entry" tab (see below).

The only thing I suspect may make this easier than expected is that no
matter whether the user searches by Topic, or Question, or Comment, etc,
the search will always join all the tables and return all the fields for
anything linked back to a source. However, the source will be associated to
many of each of those.

Thanks,

Scott





___
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] Again https://www3.sqlite.org is down

[Domingo Alvarez Duarte]

Hello Richard !

It seems that https://www3.sqlite.org is down for a few days.

Cheers !

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

[sqlite] Problem with https://www3.sqlite.org

[Domingo Alvarez Duarte]

Hello Richard !

It seems that you have a problem again with https://www3.sqlite.org for 
several days now.


It can't be reached.

Cheers !

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

Re: [sqlite] Compression for ft5

[Domingo Alvarez Duarte]

Hello !

Yes you are right the compression need to be defined by each field that 
you want to be compressed, I did it because I need some fields that the 
general size do not justify the overhead of the compression.


Cheers !

On 25/09/2018 14:29, Wout Mertens wrote:

This is really cool, thanks for sharing!

I wonder though, is the compression done per field? I read the source but I
couldn't figure it out quickly (not really used to the sqlite codebase).
What are the compression ratios you achieve?


Wout.


On Mon, Sep 24, 2018 at 3:58 PM Domingo Alvarez Duarte 
wrote:


Hello !

After looking at how compression is implemented in fts3 and wanting the
same for fts5 I managed to get a working implementation that I'm sharing
here with the same license as sqlite3 in hope it can be useful to others
and maybe be added to sqlite3.

Cheers !


Here is on implementation of optional compression and min_word_size for
columns in fts5:

===

create virtual table if not exists docs_fts using fts5(
  doc_fname unindexed, doc_data compressed,
  compress=compress, uncompress=uncompress,
  tokenize = 'unicode61 min_word_size=3'
);

===

https://gist.github.com/mingodad/7fdec8eebdde70ee388db60855760c72


And here is an implementation of optional compression for columns in
fts3/4:

===

create virtual table if not exists docs_fts using fts4(
  doc_fname, doc_data,
  tokenize = 'unicode61',
  notindexed=doc_fname, notcompressed=doc_fname,
  compress=compress, uncompress=uncompress
);

===

https://gist.github.com/mingodad/2f05cd1280d58f93f89133b2a2011a4d

___
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] Compression for ft5

[Domingo Alvarez Duarte]

Hello !

After looking at how compression is implemented in fts3 and wanting the 
same for fts5 I managed to get a working implementation that I'm sharing 
here with the same license as sqlite3 in hope it can be useful to others 
and maybe be added to sqlite3.


Cheers !


Here is on implementation of optional compression and min_word_size for 
columns in fts5:


===

create virtual table if not exists docs_fts using fts5(
    doc_fname unindexed, doc_data compressed,
    compress=compress, uncompress=uncompress,
    tokenize = 'unicode61 min_word_size=3'
);

===

https://gist.github.com/mingodad/7fdec8eebdde70ee388db60855760c72


And here is an implementation of optional compression for columns in fts3/4:

===

create virtual table if not exists docs_fts using fts4(
    doc_fname, doc_data,
    tokenize = 'unicode61',
    notindexed=doc_fname, notcompressed=doc_fname,
    compress=compress, uncompress=uncompress
);

===

https://gist.github.com/mingodad/2f05cd1280d58f93f89133b2a2011a4d

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

[sqlite] FTS5 min_word_size patch small error

[Domingo Alvarez Duarte]

Hello !

On my last post about a patch to fts5 to add an option "min_word_size" 
there is a small mistake on the comparison:


Original with mistake:

if(p->nMinWordSize && p->nMinWordSize >= wsz) continue;

New with mistake fixed (it should be ">" instead of ">="):

if(p->nMinWordSize && p->nMinWordSize > wsz) continue;


Cheers !

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

[sqlite] FTS5 min_word_size patch

[Domingo Alvarez Duarte]

Hello !

After reporting here previously about this issue I've got a working 
implementation of "min_word_size" option to Unicode61Tokenizer see patch 
bellow.


With it here is the result of a simple test:



./sqlite3
SQLite version 3.26.0 2018-09-20 20:43:28
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create virtual table tfts using fts5(data, tokenize = 'unicode61 
min_word_size 3');
sqlite> create virtual table if not exists tfts_vocab_row USING 
fts5vocab('tfts', 'row');
sqlite> insert into tfts(data) values('A new way to tokenize using fts5 
from sqlite, we can discard n letters word');

sqlite> select * from tfts_vocab_row;
discard|1|1
from|1|1
fts5|1|1
letters|1|1
sqlite|1|1
tokenize|1|1
using|1|1
word|1|1





fossil diff fts5_tokenize.c
Index: ext/fts5/fts5_tokenize.c
==
--- ext/fts5/fts5_tokenize.c
+++ ext/fts5/fts5_tokenize.c
@@ -233,10 +233,11 @@
 struct Unicode61Tokenizer {
   unsigned char aTokenChar[128];  /* ASCII range token characters */
   char *aFold;    /* Buffer to fold text into */
   int nFold;  /* Size of aFold[] in bytes */
   int bRemoveDiacritic;   /* True if remove_diacritics=1 is set */
+  int nMinWordSize;   /* Min size of a word to be indexed */
   int nException;
   int *aiException;

   unsigned char aCategory[32];    /* True for token char categories */
 };
@@ -360,10 +361,11 @@
   const char *zCat = "L* N* Co";
   int i;
   memset(p, 0, sizeof(Unicode61Tokenizer));

   p->bRemoveDiacritic = 1;
+  p->nMinWordSize = 0;
   p->nFold = 64;
   p->aFold = sqlite3_malloc(p->nFold * sizeof(char));
   if( p->aFold==0 ){
 rc = SQLITE_NOMEM;
   }
@@ -393,10 +395,14 @@
 if( 0==sqlite3_stricmp(azArg[i], "separators") ){
   rc = fts5UnicodeAddExceptions(p, zArg, 0);
 }else
 if( 0==sqlite3_stricmp(azArg[i], "categories") ){
   /* no-op */
+    }else
+    if( 0==sqlite3_stricmp(azArg[i], "min_word_size") ){
+  int mwsz;
+  if( sqlite3GetInt32(zArg, ) ) p->nMinWordSize = mwsz;
 }else{
   rc = SQLITE_ERROR;
 }
   }

@@ -450,10 +456,11 @@
   while( rc==SQLITE_OK ){
 int iCode;    /* non-ASCII codepoint read from 
input */

 char *zOut = aFold;
 int is;
 int ie;
+    int wsz;

 /* Skip any separator characters. */
 while( 1 ){
   if( zCsr>=zTerm ) goto tokenize_done;
   if( *zCsr & 0x80 ) {
@@ -517,12 +524,15 @@
 zCsr++;
   }
   ie = zCsr - (unsigned char*)pText;
 }

+    wsz = zOut-aFold;
+    /* Check min word size */
+    if(p->nMinWordSize && p->nMinWordSize >= wsz) continue;
 /* Invoke the token callback */
-    rc = xToken(pCtx, 0, aFold, zOut-aFold, is, ie);
+    rc = xToken(pCtx, 0, aFold, wsz, is, ie);
   }

  tokenize_done:
   if( rc==SQLITE_DONE ) rc = SQLITE_OK;
   return rc;



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

[sqlite] FTS5 minimum number of characters to index ?

[Domingo Alvarez Duarte]

Hello !

I'm looking in the documentation and it doesn't seem to mention any 
option to specify a minimum number of characters to index, looking at 
some fts5 tables it seems that an option to limit the minimum number of 
characters to at least 2 or 3 would be a good shot as stopwords, another 
interest option would be a regex like black/white list of sequence of 
characters to be indexed.


Something like:

create virtual table if not exists pdfs_fts using fts5(pdf_name 
UNINDEXED, data,


    tokenize = 'unicode61 remove_diacritics 1 min_word_size 3 
word_black_list [\d\.\d\d\w \a\d\d\d] word_white_list [\(\d+\) 
\d\d\.\d\d\d\.\d\d\a]');


The idea is to allow/disallow some specific domain sequences to be 
included/excluded from indexing.


Any idea on how to obtain that ?

Cheers !

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

[sqlite] The server https://www3.sqlite.org/ is not working

[Domingo Alvarez Duarte]

Hello Richard !

Today the server https://www3.sqlite.org/ is not working, I usually 
follow the sqlite3 repository daily and I think that could be a good 
idea to add an option to fossil update command to allow override the url 
for the repository so in cases like today I could do:


fossil updata --url https://www1.sqlite.org/cgi/src

Cheers !

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

[sqlite] The upcoming alter table rename column

[Domingo Alvarez Duarte]

Hello Richard !

I'm following the changes you are making to a add "alter table rename 
column" capability to sqlite and I think that it's a good moment to add 
a new system table for the columns (I know that we can somehow get this 
info now with "pragmas") this way we can get/use this info using "SQL" 
aka "data dictionary". Probably if sqlite already provided this facility 
less low level changes would be needed to this task ("rename column") 
and others.


A simplified version of 
https://www.postgresql.org/docs/10/static/catalog-pg-attribute.html 
would be nice.


Cheers !

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

[sqlite] Typo in window function docs

[Domingo Alvarez Duarte]

Hello !

This sql example on the new window function docs is missing the 
parenthesis on the "rank" function:


===

SELECT x, y, row_number() OVER*win1*, rank OVER*win2*  FROM t0  --< here the rank 
function is missing "()"
WINDOW*win1*  AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
   *win2*  AS (PARTITION BY y ORDER BY x)
ORDER BY x;

===

Cheers !

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

[sqlite] www3.sqlite.org ssl certificate expired

[Domingo Alvarez Duarte]

Hello Richard !

There is another problem with sqlite3 servers, this time it's the ssl 
certificate that has expired.


Cheers !

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

[sqlite] Again sqlite repository servers out of sync ?

[Domingo Alvarez Duarte]

Hello Richard !

It's now several days that I'm seeing one thing on 
http://www.sqlite.org/src/timeline but when I try to update my cloned 
repository I get stuck with this:




fossil update
Autosync:  https://www3.sqlite.org/cgi/src
Round-trips: 1   Artifacts sent: 0  received: 0
Pull done, sent: 548  received: 2864  ip: 165.227.20.123
---
checkout: 5d61e75f32de09c81dbe83209f063cccb005 2018-05-05 
16:50:35 UTC

tags: trunk
comment:  In an ORDER BY LIMIT, make sure the ORDER BY expression 
evaluator
  does not try to reuse values from the result set if the 
result

  set has not yet be computed. This fixes a bug in the recent
  deferred-row loading optimization, check-in
  [c381f0ea57002a264fd958b28e]. OSSFuzz discovered the problem.
  (user: drh)
changes:  None. Already up-to-date



I suspect that your servers sync command is not working properly !

Cheers !

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

[sqlite] This list is getting spammed again

[Domingo Alvarez Duarte]

Hello Richard !

Again this list is getting spammed, I just received spam after publish.

Cheers !

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

[sqlite] SQLite3 fossil repository problem solved

[Domingo Alvarez Duarte]

Hello !

I just retried again and now it got updated to the same content I can 
see on the web.



Still it seems that there is more than one fossil server and they seem 
to lag from the main one.



Cheers !

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

[sqlite] SQLite3 fossil repository not working ?

[Domingo Alvarez Duarte]

Hello !

Today I tried to update my sqlite3 repository but somehow it seems not 
working properly, I execute "fossil update" as usually and it contacts 
the server and exchange info with it but it only see till this commit 
http://www.sqlite.org/src/info/c381f0ea57002a264fd958b28e .


There is more than one fossil server and they are not synchronized ?

Cheers !

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

[sqlite] When trying to compile with gcc is giving kind of infinit loop

[Domingo Alvarez Duarte]

Hello !

I'm experiencing a kind of compiler infinte loop when trying to compile 
sha1 extension (it seems like the macros problem for shathree).



The compiler seems to enter an infinite loop. If I avoid using "asm" it 
compiles fine.


#if __GNUC__ && (defined(__i386__) || defined(__x86_64__)) && 
!defined(NO_ASM) ///add && !defined(NO_ASM)


Has someone experiencing this problem ?

=

/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c: In function 
'SHA1Transform':
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218953:5: 
warning: implicit declaration of function 'asm' 
[-Wimplicit-function-declaration]
 Rl0(a,b,c,d,e, 0); Rl0(e,a,b,c,d, 1); Rl0(d,e,a,b,c, 2); 
Rl0(c,d,e,a,b, 3);

 ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218889:44: 
error: expected ')' before ':' token
 ({ unsigned int y; asm(op " %1,%0" : "=r" (y) : "I" (k), "0" 
(x)); y; })

    ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218891:18: 
note: in expansion of macro 'SHA_ROT'

 #define ror(x,k) SHA_ROT("rorl", x, k)
  ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218901:32: 
note: in expansion of macro 'ror'

 #define blk0le(i) (block[i] = (ror(block[i],8)&0xFF00FF00) \
    ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218914:22: 
note: in expansion of macro 'blk0le'

z+=((w&(x^y))^y)+blk0le(i)+0x5A827999+rol(v,5);w=ror(w,2);
  ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218953:5: 
note: in expansion of macro 'Rl0'
 Rl0(a,b,c,d,e, 0); Rl0(e,a,b,c,d, 1); Rl0(d,e,a,b,c, 2); 
Rl0(c,d,e,a,b, 3);

 ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218889:44: 
error: expected ')' before ':' token
 ({ unsigned int y; asm(op " %1,%0" : "=r" (y) : "I" (k), "0" 
(x)); y; })

    ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218890:18: 
note: in expansion of macro 'SHA_ROT'

 #define rol(x,k) SHA_ROT("roll", x, k)
  ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218902:7: 
note: in expansion of macro 'rol'

 |(rol(block[i],8)&0x00FF00FF))
   ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218914:22: 
note: in expansion of macro 'blk0le'

z+=((w&(x^y))^y)+blk0le(i)+0x5A827999+rol(v,5);w=ror(w,2);
  ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218953:5: 
note: in expansion of macro 'Rl0'
 Rl0(a,b,c,d,e, 0); Rl0(e,a,b,c,d, 1); Rl0(d,e,a,b,c, 2); 
Rl0(c,d,e,a,b, 3);

 ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218889:44: 
error: expected ')' before ':' token
 ({ unsigned int y; asm(op " %1,%0" : "=r" (y) : "I" (k), "0" 
(x)); y; })

    ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218890:18: 
note: in expansion of macro 'SHA_ROT'

 #define rol(x,k) SHA_ROT("roll", x, k)
  ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218914:43: 
note: in expansion of macro 'rol'

z+=((w&(x^y))^y)+blk0le(i)+0x5A827999+rol(v,5);w=ror(w,2);
   ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218953:5: 
note: in expansion of macro 'Rl0'
 Rl0(a,b,c,d,e, 0); Rl0(e,a,b,c,d, 1); Rl0(d,e,a,b,c, 2); 
Rl0(c,d,e,a,b, 3);

 ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218889:44: 
error: expected ')' before ':' token
 ({ unsigned int y; asm(op " %1,%0" : "=r" (y) : "I" (k), "0" 
(x)); y; })

    ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218891:18: 
note: in expansion of macro 'SHA_ROT'

 #define ror(x,k) SHA_ROT("rorl", x, k)
  ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218914:54: 
note: in expansion of macro 'ror'

z+=((w&(x^y))^y)+blk0le(i)+0x5A827999+rol(v,5);w=ror(w,2);
  ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218953:5: 
note: in expansion of macro 'Rl0'
 Rl0(a,b,c,d,e, 0); Rl0(e,a,b,c,d, 1); Rl0(d,e,a,b,c, 2); 
Rl0(c,d,e,a,b, 3);

 ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218889:44: 
error: expected ')' before ':' token
 ({ unsigned int y; asm(op " %1,%0" : "=r" (y) : "I" (k), "0" 
(x)); y; })

    ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218891:18: 
note: in expansion of macro 'SHA_ROT'

 #define ror(x,k) SHA_ROT("rorl", x, k)
  ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218901:32: 
note: in expansion of macro 'ror'

 #define blk0le(i) (block[i] = (ror(block[i],8)&0xFF00FF00) \
    ^
/vdata/db-api-server-presentation/../SquiLu-ext/sqlite3.c:218914:22: 
note: in expansion of macro 'blk0le'

[sqlite] Changes on sqlite3 parser and why not ?

[Domingo Alvarez Duarte]

Hello Richard !

Now that you are making changes on sqlite3 parser could you please add 
the table alias to delete/insert/update ?


I already have it working and got conflicts with the latest changes, 
they are not big or complicated but it helps make some queries easier.


Cheers !

fossil diff parse.y
Index: src/parse.y
==
--- src/parse.y
+++ src/parse.y
@@ -779,20 +779,20 @@
  {A = sqlite3PExpr(pParse,TK_LIMIT,Y,X);}

 /// The DELETE statement 
/

 //
 %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with DELETE FROM fullname(X) indexed_opt(I) where_opt(W)
+cmd ::= with DELETE FROM fullname(X) as(Z) indexed_opt(I) where_opt(W)
 orderby_opt(O) limit_opt(L). {
   sqlite3SrcListIndexedBy(pParse, X, );
-  sqlite3DeleteFrom(pParse,X,W,O,L);
+  sqlite3DeleteFrom(pParse,X,,W,O,L);
 }
 %endif
 %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
+cmd ::= with DELETE FROM fullname(X) as(Z) indexed_opt(I) where_opt(W). {
   sqlite3SrcListIndexedBy(pParse, X, );
-  sqlite3DeleteFrom(pParse,X,W,0,0);
+  sqlite3DeleteFrom(pParse,X,,W,0,0);
 }
 %endif

 %type where_opt {Expr*}
 %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}
@@ -801,23 +801,23 @@
 where_opt(A) ::= WHERE expr(X).   {A = X;}

 // The UPDATE command 


 //
 %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
+cmd ::= with UPDATE orconf(R) fullname(X) as(Z) indexed_opt(I) SET 
setlist(Y)

 where_opt(W) orderby_opt(O) limit_opt(L).  {
   sqlite3SrcListIndexedBy(pParse, X, );
   sqlite3ExprListCheckLength(pParse,Y,"set list");
-  sqlite3Update(pParse,X,Y,W,R,O,L);
+  sqlite3Update(pParse,X,,Y,W,R,O,L);
 }
 %endif
 %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
-cmd ::= with UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
+cmd ::= with UPDATE orconf(R) fullname(X) as(Z) indexed_opt(I) SET 
setlist(Y)

 where_opt(W).  {
   sqlite3SrcListIndexedBy(pParse, X, );
   sqlite3ExprListCheckLength(pParse,Y,"set list");
-  sqlite3Update(pParse,X,Y,W,R,0,0);
+  sqlite3Update(pParse,X,,Y,W,R,0,0);
 }
 %endif

 %type setlist {ExprList*}
 %destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}


fossil diff update.c
Index: src/update.c
==
--- src/update.c
+++ src/update.c
@@ -87,10 +87,11 @@
 *    onError   pTabList  pChanges pWhere
 */
 void sqlite3Update(
   Parse *pParse, /* The parser context */
   SrcList *pTabList, /* The table in which we should change things */
+  Token *pAlias,  /* The right-hand side of the AS subexpression */
   ExprList *pChanges,    /* Things to be changed */
   Expr *pWhere,  /* The WHERE clause.  May be null */
   int onError,   /* How to handle constraint errors */
   ExprList *pOrderBy,    /* ORDER BY clause. May be null */
   Expr *pLimit   /* LIMIT clause. May be null */
@@ -123,10 +124,11 @@
   int hasFK; /* True if foreign key processing is required */
   int labelBreak;    /* Jump here to break out of UPDATE loop */
   int labelContinue; /* Jump here to continue next step of UPDATE 
loop */

   int flags; /* Flags for sqlite3WhereBegin() */

+  struct SrcList_item *pItem; /*To namage table alias*/
 #ifndef SQLITE_OMIT_TRIGGER
   int isView;    /* True when updating a view (INSTEAD OF 
trigger) */

   Trigger *pTrigger; /* List of triggers on pTab, if required */
   int tmask; /* Mask of TRIGGER_BEFORE|TRIGGER_AFTER */
 #endif
@@ -153,10 +155,16 @@
   if( pParse->nErr || db->mallocFailed ){
 goto update_cleanup;
   }
   assert( pTabList->nSrc==1 );

+  /*Manage table alias*/
+  pItem = >a[pTabList->nSrc-1];
+  if( pAlias && pAlias->n ){
+    pItem->zAlias = sqlite3NameFromToken(db, pAlias);
+  }
+
   /* Locate the table which we want to update.
   */
   pTab = sqlite3SrcListLookup(pParse, pTabList);
   if( pTab==0 ) goto update_cleanup;
   iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema);

fossil diff delete.c
Index: src/delete.c
==
--- src/delete.c
+++ src/delete.c
@@ -219,10 +219,11 @@
 **  pTabList  pWhere
 */
 void sqlite3DeleteFrom(
   Parse *pParse, /* The parser context */
   SrcList *pTabList, /* The table from which we should delete 
things */

+  Token *pAlias,  /* The right-hand side of the AS subexpression */
   Expr *pWhere,  /* The WHERE clause.  May be null */
   ExprList *pOrderBy,    /* ORDER BY clause. May be null */
   Expr *pLimit   /* LIMIT clause. May be null */
 ){
   Vdbe *v;   /* The virtual database engine */
@@ 

[sqlite] cannot connect to host www3.sqlite.org:443 (Connection refused)

[Domingo Alvarez Duarte]

Hello !

I'm getting this error when trying to update my sqlite3 repository, this 
message began some days ago and I was thinking that it was a temporary 
problem, but it seems it's not.


Maybe some configuration changed on the sqlite servers ?

Cheers !

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

[sqlite] WISH LIST: macros on internal commands in sqlite3 executable

[Domingo Alvarez Duarte]

Hello !

While trying to abuse sqlite3 executable to write a complete application 
( https://github.com/mingodad/sqlite3-hashcode-2018 ) I found that if 
the sqlite3 executable could allow the use of simple macros on it's 
internal commands would make it a lot more useful and easy.


What I mean by "macros on internal commands" ?

The ability to use/reuse some defined/used pieces of data form previous 
invocations or command line.


Example:

sqlite>.import 'some.txt' sometable

sqlite>...

sqlite>.output $(last_import_file_name).result.txt

sqlite>.backup main $(current_dbname)$(curr_time).bak.db


Cheers !

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

[sqlite] HASHCODE 2018 in sqlite3

[Domingo Alvarez Duarte]

Hello !

I've been looking ath the HASHCODE 2018 competition and got interested 
on the problem proposition:


Task
Given a list of pre-booked rides in a city and a fleet of self-driving 
vehicles, assign the rides to vehicles, so

that riders get to their destinations on time.

For every ride that finishes on time (or early), you will earn points 
proportional to the distance of that ride;

plus an additional bonus if the ride also started precisely on time.

It took me a while to get something working and at end I saw that I was 
basically using sqlite3 capabilities to do the data manipulation tasks 
and only needed a few calculations for the rest.


So I tried to see if I also could make those calculations inside sqlite3 
and after a while I could get it to work:


https://github.com/mingodad/sqlite3-hashcode-2018/blob/master/hashcode2018.sql

As I stated there:

By no means I claim that solutions like this (stretching sqlite3) are 
good practice. Take it as an example that demonstrates several 
capabilities of sqlite3 in a hack/compact way.


So I'm showing it here just in case it can help/invite other people to 
find imaginative ways where sqlite3 can be used to solve problems.


Cheers !

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

[sqlite] Bug in unique index

[Domingo Alvarez Duarte]

Hello !

Maybe I found a bug in sqlite3 unique index, see example bellow:

bug-unique.sql

drop table if exists test;
create table test(
    id integer primary key,
    v1 integer not null,
    v2 integer not null constraint not_equal check(v1 != v2),
    unique(v1, v2),
    unique(v2, v1)
);

insert into test values(1, 1, 2);
insert into test values(2, 2, 1);
select * from test;



sqlite3 < bug-unique.sql

output

1|1|2
2|2|1


I was expecting to have an error trying to insert the second row but 
sqlite3 accepted the duplicated index without error.


Cheers !


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

[sqlite] Why there is duplicated sha1 functions in sqlite3 sources ?

[Domingo Alvarez Duarte]

Hello !

I'm making a build of sqlite3 that includes sha1 and shathree and other 
extensions, then made some changes to makefiles (Makefile.in, main.mk) 
then when trying to run the tests I got an error "multiple definition of 
'SHA1Transform'" when trying to link dbhash.c .


Why is sha1 duplicated on dbhash.c instead of reusing the one in 
ext/misc/sha1.c ?


Also I'm still waiting to know why sha1.c sqlite_extension return a hex 
string but shathree return a binary string ?


It seems that some cleanup can be done here !

Cheers !

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

[sqlite] Fossil comunication and or test problems

[Domingo Alvarez Duarte]

Hello !

I'm writing this here on sqlite lists because I already sent a direct 
mail to Richard Hipp on 21/10/2017 and to fossil-scm lists on 23/10/2017 
and no reply or action so far.


Maybe there is a comunication problem preventing those emails reach the 
developers.


The test problem is that if we get the source/clone of fossil-scm, 
compile and run the tests we can see several failures that can be traced 
to specific commits but they seem to be ignored by the developers or not 
run at all.


Cheers !

Here is a copy of the message:

===

Hello Richard !

I found 2 other small bugs in fossil while trying to make it full 
reentrant, attached is the fixes.


- One of then is a malformed setting "diff-binary" comment that prevents 
it to be discovered by makeindex.


- The other was causing segfaults because the code on db.c:3267 that is 
expecting to find a match or a NULL mark to stop looping but there was 
no explicit NULL mark on the generated settings. The attached patch 
change makeindex to generate the NULL mark and also decrement the return 
value of  "setting_info" to allow the binary search work properly 
(although a comment in code explaining why would be great too).




  while( pSetting->name ){ here is assuming there is a 
last NULL value mark

    if( exactFlag ){
  if( fossil_strcmp(pSetting->name,zName)!=0 ) break;
    }else{
  if( fossil_strncmp(pSetting->name,zName,n)!=0 ) break;
    }
    print_setting(pSetting);
    pSetting++; /advancing the pointer to check for a NULL 
pointer mark

  }



It right now pass all tests the same way the official fossil does.



* End of wiki: 2 errors so far **
* Final results: 2 errors out of 35952 tests
* Considered failures: json-ROrepo-2-1 unversioned-14
* Ignored results: 8 ignored errors out of 35952 tests
* Ignored failures: json-cap-POSTenv-name json-RC-1103 json-RC-4103 
merge5-sqlite3-issue stash-1-diff stash-WY-1-CODE stash-3-2 
stash-3-2-show-1



Also running the tests I noticed that for some of then to pass we need 
to run then outside an open repository and probably the test.tcl should 
be modified to change to a folder outside the current build/open source 
tree or be documented to do it manually before run the tests.


The two tests that do not pass I briefly looked at then and on 
json-ROrepo-2-1 I could not grasp what was the expected result that 
failed, the unversioned-14 fail on "filenames with spaces" and I'm not 
sure if the report failing is a missing implementation on fossil to 
manage it or a warning stating that fossil will not implement it.


Now I'll try to see how to solve the memory/resources leaks that need to 
be addressed to make it possible to create a library with the core 
fossil functionality.


Cheers !


bugs.diff


Index: src/db.c
==
--- src/db.c
+++ src/db.c
@@ -2871,11 +2871,12 @@
 ** SETTING: default-perms   width=16 default=u
 ** Permissions given automatically to new users.  For more
 ** information on permissions see the Users page in Server
 ** Administration of the HTTP UI.
 */
-/* SETTING: diff-binary boolean default=on
+/*
+** SETTING: diff-binary boolean default=on
 ** If enabled, permit files that may be binary
 ** or that match the "binary-glob" setting to be used with
 ** external diff programs.  If disabled, skip these files.
 */
 /*

Index: src/dispatch.c
==
--- src/dispatch.c
+++ src/dispatch.c
@@ -653,8 +653,8 @@
 **
 ** This routine provides access to the aSetting2[] array which is created
 ** by the mkindex utility program and included with .
 */
 const Setting *setting_info(int *pnCount){
-  if( pnCount ) *pnCount = (int)(sizeof(aSetting)/sizeof(aSetting[0]));
+  if( pnCount ) *pnCount = ((int)(sizeof(aSetting)/sizeof(aSetting[0])))-1;
   return aSetting;
 }

Index: src/mkindex.c
==
--- src/mkindex.c
+++ src/mkindex.c
@@ -480,10 +480,11 @@
 );
 if( aEntry[i].zIf ){
   printf("#endif\n");
 }
   }
+  printf("  {NULL, 0, 0, 0, 0, NULL},\n");
   printf("};\n");
 
 }
 
 /*


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

[sqlite] Inconsistency between sha1 and sha3 extensions

[Domingo Alvarez Duarte]

Hello !

I've tried to use both ext/misc/sha1.c and ext/misc/shathree.c static 
linked with sqlite3 and noticed that instead of using an unique prefix 
for hash_step_vformat like (sha1_step_vformat / sha3_step_vformat) 
giving an error "duplicated symbol" at link time.


Also the return of sha1 is converted to hexadecimal but the sha3 is not.

Another problem with sha3 is that it clashes when compiled for ARM 
Android whit a CPU register B0 and I did a dirty patch to allow it 
compile see bellow:


 /*
 ** A single step of the Keccak mixing function for a 1600-bit state
 */
 static void KeccakF1600Step(SHA3Context *p){
   int i;
+#ifdef B0
+#undef B0
+#endif
   u64 B0, B1, B2, B3, B4;
   u64 C0, C1, C2, C3, C4;
   u64 D0, D1, D2, D3, D4;
   static const u64 RC[] = {
 0x0001ULL,  0x8082ULL,

Cheers !

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

[sqlite] Patch to allow table alias for delete/update on sqlite3

[Domingo Alvarez Duarte]

Hello !

Here is a patch that can be applied to the sqlite3 trunk to allow the 
usage of table alias for delete/update operations.


https://gist.github.com/mingodad/768b47903e54715e919e08b94c05804f

Cheers !

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

[sqlite] Fossil: ERROR: SQL error: no such table: ticketchng

[Domingo Alvarez Duarte]

Hello !

I'm using fossil and went through it's pages to have an overview of it 
and when viewing a ticket it shows this error message:


ERROR: SQL error: no such table: ticketchng

I had a problem before and Richard told me to issue a command that 
somehow upgraded the fossil database, this time I tried "fossil 
reconstruct" and some errors where solved but this one remains.


Looking at "fossil help -a" I couldn't find a command by name that 
suggested an upgrade.


Cheers !

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

Re: [sqlite] syntax error near AS

[Domingo Alvarez Duarte]

I already did this before but it was not accepted.

For myself I did a modification on sqlite3 to allow the use of "AS" on 
delete/update statements.


You can see the parser part here 
https://github.com/mingodad/sqlite/blob/decimal64/src/parse.y .


Cheers !


On 06/07/17 05:16, John McMahon wrote:

Hi

Wondering if someone else can spot the syntax error in the following 
statement. "locns" is an attached database. There are four "AS" terms 
in the statement, they all alias tables.


Ok, found it. It seems that an alias for an "UPDATE" table name is not 
permitted. Is there a particular reason for this?
I would think it a convenience especially when using long table names 
and attached databases.


John

sqlite> UPDATE locns.xxx_last_delivery AS tgt
  ... >  SET
  ... > tgt.del_date =  (
  ... >  SELECT src.last_del_d
  ... >  FROM   main.updates AS src
  ... >  WHERE  src.custnum = tgt.custnum),
  ... > tgt.del_qty = (
  ... >  SELECT src.last_del_q
  ... >  FROM   main.updates AS src
  ... >  WHERE  src.custnum = tgt.custnum)
  ... >  WHERE
  ... > tgt.custnum  = (
  ... >  SELECT src.custnum
  ... >  FROM   main.updates AS src
  ... >  WHERE  src.last_del_d IS NOT NULL
  ... >  ANDsrc.last_del_d > tgt.del_date)
  ... >  ;
Error: near "AS": syntax error



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

[sqlite] Any reason for sqlite3changeset_concat not using const parameters ?

[Domingo Alvarez Duarte]

Hello !

I'm trying to use sqlite3 session extension with C++ and I'm getting 
errors because sqlite3changeset_concat aren't using const parameters for 
input parameters like sq_sqlite3_session_invert does, looking at the 
code it doesn't seem that it modifies it's input parameters.


Cheers !

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

Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

[Domingo Alvarez Duarte]

Hello !

What I understood looking at the sqlite3 sources is that an update is 
always 3 operations:


1- Read old row

2- Delete old row

3- Insert updated row

So I seems that using "insert" would be less work.

Cheers !


On 19/04/17 16:27, Simon Slavin wrote:

On 19 Apr 2017, at 7:47pm, no...@null.net wrote:


I use
triggers quite heavily as a kind of stored procedure.

Instead of basing them on views however I use real tables and AFTER
INSERT triggers whose final statement deletes the NEW row just
inserted.

I see two benefits to the use of AFTER INSERT triggers:

* Constraints are enforced so SQLite catches invalid
"procedure calls."
* Default values for columns (or "arguments") can be defined. This
is very useful if you want to use the incoming value in multiple
statements - you don't have to hardcode a bunch of
COALESCE(NEW.col, $DEFAULT) values everywhere.

Had you considered doing UPDATE instead of INSERT ?  Leave one row in the table 
and issue an UPDATE command when you want to trigger a trigger.  If the column 
you’re changing isn’t indexed it’s a little faster.  And just like INSERT you 
can use the value you set, using CASE … END, to set what you want to happen.

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] Is it possible to create the Stored Procedure (SP) in Sqlite?

[Domingo Alvarez Duarte]

Hello Philip !

There was this attempt https://www.sqliteconcepts.org/PL_index.html and 
I tried to adapt to sqlite3 but the change on the sqlite3 vm compared to 
sqlite2 made it a lot harder.


The vm of sqlite3 is not well documented and is changing all the time.

But I also agreed with you if we could have "@variables" at connection 
level, query level, trigger level and also be able to write triggers in 
"C" (or another glue language), simple stored procedures (queries with 
parameters at sql level) life would be a bit easier.


Cheers !

On 19/04/17 08:34, Philip Warner wrote:
There is another reason to have stored procedures: encapsulating logic 
across apps/clients.


A great deal can be done in triggers, but not much in terms of queries 
or complex parameterized updates.


It would be great, imo, if triggers could have durable local storage 
(ie. variables) and if this were built upon to allow stored 
procedures, life would be much more fun.


Parameterized multi-query SQL statements returning event just a single 
row set would be fine.




On 16/04/2017 2:18 AM, Richard Hipp wrote:

On 4/15/17, Manoj Sengottuvel  wrote:

Hi Richard,

Is it possible to create the Stored Procedure (SP) in Sqlite?

if not , is there any alternate way for SP?

Short answer:  No.

Longer answer:  With SQLite, your application is the stored procedure.
In a traditional client/server database like PostgreSQL or Oracle or
SQL Server, every SQL statement involves a round-trip to the server.
So there is a lot of latency with each command.  The way applications
overcome this latency is to put many queries into a stored procedure,
so that only the stored procedure invocation needs to travel over the
wire and latency is reduced to a single server round-trip.

But with SQLite, each statement is just a procedure call.  There is no
network traffic, not IPC, and hence very little latency. Applications
that use SQLite can be very "chatty" with the database and that is not
a problem.  For example, the SQLite website is backed by SQLite (duh!)
and a typical page request involves 200 to 300 separate queries.  That
would be a performance killer with a client/server database, but with
SQLite it is not a problem and the pages render in about 5
milliseconds.


___
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] Magic numbers on sqlite source code

[Domingo Alvarez Duarte]

Hello Richard !

Time to time I look at some commits that call my attention like this:

[e47fdb49]  Leaf: 
Refactor the fts3ColumnMethod() function so that all branches can be 
covered. (user: dan 
, 
tags: trunk 
)


And I can see that there is a usage of magic numbers instead of an enum 
on named macro, this at large makes understanding of the source a bit 
harder.


Some comments following the magic number could be removed if instead of 
magic numbers a named constant was used, with the benefit that a named 
constant can be searched to see all places where it is used.




switch( iCol-p->nColumn ){
case 0: //>> Instead of '0' a named constant would make 
following the code a bit easier

/* The special 'table-name' column */
sqlite3_result_blob(pCtx, , sizeof(Fts3Cursor*), SQLITE_TRANSIENT);
sqlite3_result_subtype(pCtx, SQLITE_BLOB);
break;

case 1:  //> Magic number
/* The docid column */
sqlite3_result_int64(pCtx, pCsr->iPrevId);
break;

case 2:  // >>> Magic number
if( pCsr->pExpr ){
sqlite3_result_int64(pCtx, pCsr->iLangid);
break;
}else if( p->zLanguageid==0 ){
sqlite3_result_int(pCtx, 0);
break;
}else{
iCol = p->nColumn;
/* fall-through */
}

default:
/* A user column. Or, if this is a full-table scan, possibly the
** language-id column. Seek the cursor. */
rc = fts3CursorSeek(0, pCsr);
if( rc==SQLITE_OK && sqlite3_data_count(pCsr->pStmt)-1>iCol ){
sqlite3_result_value(pCtx, sqlite3_column_value(pCsr->pStmt, iCol+1));
}
break;
  }


Cheers !

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

Re: [sqlite] Is this a foreign key bug ?

[Domingo Alvarez Duarte]

Hello Dan !

Thank you for the reply !

It was my fault of knowledge here, thanks !


On 15/04/17 10:11, Dan Kennedy wrote:

On 04/15/2017 07:16 PM, Domingo Alvarez Duarte wrote:

Hello !

I'm getting an error that seems to be a bug in sqlite3 or a change of 
behavior.


===

CREATE TABLE a(
keyval varchar(25)  NOT NULL,
testid integer  NOT NULL,
PRIMARY KEY ("keyval","testid")
);

CREATE TABLE b(
sampleid integer  NOT NULL,
prodspeckey varchar(25)  NOT NULL,
PRIMARY KEY ("sampleid"),
CONSTRAINT "qasamples_ibfk_1" FOREIGN KEY ("prodspeckey") 
REFERENCES "a" ("keyval")

);

PRAGMA foreign_key_check;
===

output

===

sqlite3 < bug-fk.sql
Error: near line 14: foreign key mismatch - "b" referencing "a"


It's not a bug. There is no unique constraint on the parent key 
"a.keyval". "a.keyval" is part of a PRIMARY KEY, but is not by itself 
guaranteed to be unique. Hence the error.


This is the same case as the last example ("CREATE TABLE child7...") 
in the first block of code here:


  http://sqlite.org/foreignkeys.html#fk_indexes

Dan.


___
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 this a foreign key bug ?

[Domingo Alvarez Duarte]

Hello !

I'm getting an error that seems to be a bug in sqlite3 or a change of 
behavior.


===

CREATE TABLE a(
keyval varchar(25)  NOT NULL,
testid integer  NOT NULL,
PRIMARY KEY ("keyval","testid")
);

CREATE TABLE b(
sampleid integer  NOT NULL,
prodspeckey varchar(25)  NOT NULL,
PRIMARY KEY ("sampleid"),
CONSTRAINT "qasamples_ibfk_1" FOREIGN KEY ("prodspeckey") 
REFERENCES "a" ("keyval")

);

PRAGMA foreign_key_check;
===

output

===

sqlite3 < bug-fk.sql
Error: near line 14: foreign key mismatch - "b" referencing "a"

===

Cheers !

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

Re: [sqlite] Optimization opportunity

[Domingo Alvarez Duarte]

Hello !

Maybe this problem would be the reason of getting bad query plans when 
joining views too.


Cheers !


On 14/04/17 08:03, Wolfgang Enzinger wrote:

Hello,

given the following:



CREATE TABLE x(
   pk INTEGER PRIMARY KEY,
   description TEXT
);

CREATE TABLE y(
   fk INTEGER REFERENCES x(pk),
   flags INTEGER
);

CREATE INDEX yy ON y(fk);

CREATE VIEW z AS SELECT
   fk,
   (flags&1) AS odd,
   (flags&2)>>1 AS even,
   (flags&4)>>2 AS prime
   FROM y;

INSERT INTO x(pk,description) VALUES
   (1,'one'),(2,'two'),(3,'three'),(4,'four');

INSERT INTO y(fk,flags) VALUES (1,1|0|0),(2,0|2|4),(3,1|0|4),(4,0|2|0);



Now using the VIEW z in a JOIN results in a full table scan on TABLE y
despite a WHERE clause and an appropriate INDEX:

EXPLAIN QUERY PLAN SELECT x.pk,z.odd,z.even,z.prime
FROM x LEFT JOIN z ON x.pk=z.fk WHERE x.pk=2;
1|0|0|SCAN TABLE y
0|0|0|SEARCH TABLE x USING INTEGER PRIMARY KEY (rowid=?)
0|1|1|SCAN SUBQUERY 1

Bypassing the VIEW however uses INDEX yy:

EXPLAIN QUERY PLAN
SELECT x.pk,(y.flags&1) AS odd,(y.flags&2)>>1 AS even,(y.flags&4)>>2 AS
prime FROM x LEFT JOIN y ON x.pk=y.fk WHERE x.pk=2;
0|0|0|SEARCH TABLE x USING INTEGER PRIMARY KEY (rowid=?)
0|1|1|SEARCH TABLE y USING INDEX yy (fk=?)

Unless I'm missing something, I think there is a potential optimization
opportunity.

Identical results with SQLite versions 3.13, 3.17 and 3.18.

Cheers, Wolfgang

___
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] Fossil error: Table plink has no column named baseid

[Domingo Alvarez Duarte]

Hello Warren !

Thanks for reply !

I only did it here because I need to create/recover an account on fossil 
mailing lists (at times is a pain isolated silos).


And yes I was thinking on something like what is described on the link 
you've passed, on mobile devices only the tip of the repository would 
enough most of the time.


Cheers !


On 06/04/17 21:32, Warren Young wrote:

On Apr 6, 2017, at 6:28 PM, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:

How could we clone/sync a fossil repository with "n" depth commits ?

That’s called a shallow clone, and it comes up on the Fossil Users’ mailing 
list from time to time:

 
https://www.mail-archive.com/search?q=shallow+clone=fossil-users%40lists.fossil-scm.org

Such things are probably better discussed there, rather than on the SQLite 
mailing list.
___
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] Fossil error: Table plink has no column named baseid

[Domingo Alvarez Duarte]

Hello Richard !

Thanks for the reply, after issuing a rebuild it's now syncing (not 
finished yet).


Doing this port to android one of it's usage case is to have a portable 
offline repository on the go, and for some big repositories probably 
having the full repository is not needed, git has an option "git clone 
some_git_url --depth 1" and looking at fossil it doesn't seem to provide 
anything like that.


How could we clone/sync a fossil repository with "n" depth commits ?

Cheers !


On 06/04/17 21:01, Richard Hipp wrote:

Normally it does, but your repository might be too old.  Try running
"fossil rebuild" first and that will likely fix it.


On 4/6/17, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:

Hello Richard !

I have done a port/adaptation of fossil for android but haven't updated
it for some time, today I updated it to the latest fossil and when
trying to sync the original fossil clone on android I'm getting this error:

Table plink has no column named baseid:

INSERT OR IGNORE INTO plink(pid, cid, isprim, mtime, baseid)

VALUES(19502, 28178, 1, 2456451.609110463, NULL);

I was expecting that fossil would detect and update the schema by itself.

Cheers !

___
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] Fossil error: Table plink has no column named baseid

[Domingo Alvarez Duarte]

Hello Richard !

I have done a port/adaptation of fossil for android but haven't updated 
it for some time, today I updated it to the latest fossil and when 
trying to sync the original fossil clone on android I'm getting this error:


Table plink has no column named baseid:

INSERT OR IGNORE INTO plink(pid, cid, isprim, mtime, baseid)

VALUES(19502, 28178, 1, 2456451.609110463, NULL);

I was expecting that fossil would detect and update the schema by itself.

Cheers !

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

Re: [sqlite] BUG?: operation unexpectedly applied to both main and temp

[Domingo Alvarez Duarte]

Hello !

I repeated your simple case and could see that only the temp.t tabale is 
populated/updates.


It seems sqlite look first if there is a temp.table before main.table 
and without qualification temp.table is used.


Cheers !


On 28/03/17 21:12, Mark Brand wrote:



On 29/03/17 01:35, Simon Slavin wrote:

On 28 Mar 2017, at 11:02pm, Mark Brand  wrote:


create temp table t (db, val);
insert into t select 'temp', 'original';

create table t (db, val);
insert into t select 'main', 'original';

Here’s your problem:

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
sqlite> create temp table t (db, val);
sqlite> insert into t select 'temp', 'original';
sqlite> .schema
CREATE TABLE t (db, val);
sqlite> create table t (db, val);
sqlite> insert into t select 'main', 'original';
sqlite> .schema
CREATE TABLE t (db, val);
CREATE TABLE t (db, val);
sqlite> SELECT * FROM t;
temp|original
main|original
sqlite> SELECT * FROM temp.t;
temp|original
main|original
sqlite> DROP TABLE temp.t;
sqlite> SELECT * FROM t;
sqlite>

While temp.t exists it gets in the way of main.t.  When you refer to 
"t" you’re talking about temp.t, not main.t.




The point isn't about which table one expects to receive the update, 
it's that *both* tables get updated. In fact, now I realize that the 
effect can be demonstrated with a simpler demo than my original:


create temp table t (db, val);
insert into t select 'temp', 'original';

create table main.t (db, val);
insert into t select 'main', 'original';

update t set val = 'touched';

select * from temp.t;
select * from main.t;

/*  output
temp|default table
main|default table
*/

___
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] Drop view “automatically” when its associated table is dropped?

[Domingo Alvarez Duarte]

Hello !

It's not that complicated, sqlite already do all work right now but it 
doesn't store that information in a structured way "data dictionary".


Cheers !


On 23/03/17 21:17, Simon Slavin wrote:

On 23 Mar 2017, at 10:52pm, Daniel Anderson  wrote:


Sybase has one.

there is even a way to get objects dependencies

Okay, thanks for the answers.  The only way to do this involves quite a 
complicated set of dependencies.  Consider, for example, having to parse a 
TRIGGER to figure out all the TABLEs and VIEWs mentioned inside the stored 
procedure, and also all the columns of those tables and views mentioned in the 
TRIGGER.

The only way I can see it happening involved a significant modification of 
SQLite so that as it parses the schema stored in the database it constructs 
this table of dependencies.  Nothing simple.

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] Memoization in sqlite json1 functions

[Domingo Alvarez Duarte]

Hello Jens !

Nice to know this project, I'll look at it.

Cheers !


On 23/03/17 15:05, Jens Alfke wrote:

On Mar 23, 2017, at 4:30 AM, Richard Hipp  wrote:

BLOBs are reserved for a future enhancement in which BLOBs will store the 
binary encoding for JSON.

I’ve been down this road. There are a number of JSON-compatible binary encoding 
formats, but most of them don’t save much time, because (a) they’re not _that_ 
much faster to parse, (b) reading JSON tends to be dominated by allocating an 
object tree, not by the actual parsing, and (c) usually you have to parse the 
entire data even if you only want to use one piece of it [as in a query].

I ended up designing and implementing a new format, called Fleece*. Its 
advantage is that it doesn’t require parsing or even memory allocation. The 
internal structure is already an object tree, except that it uses compressed 
relative offsets instead of pointers. This means that internal pointers into 
Fleece data can be used directly as the data objects.

In my current project** we’re storing Fleece in SQLite instead of JSON, with a 
modified version of the json1 extension to make it accessible in queries. It 
works very well. The Fleece equivalent of json_extract( ) just does some 
pointer manipulation to find the root object in the blob, then further lookups 
to jump to each nested object in the path.

—Jens

* https://github.com/couchbaselabs/fleece 

** https://github.com/couchbase/couchbase-lite-core
___
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] Memoization in sqlite json1 functions

[Domingo Alvarez Duarte]

Hello Richard !

Yes I do see "because the implementation parsing the JSON anew each 
time" and this is a recurring pattern in some sqlite functions, would be 
nice if we could have a "session/query/row" storage space to store query 
information that can be  reused, for example on the json functions we 
could reuse an already parsed json field several times, another example 
we could have session/query/row variables.


We could have something like "sqlite3_set_auxdata" but with granularity 
for row/query/session and as a bonus would be nice to have session 
variables like https://dev.mysql.com/doc/refman/5.7/en/user-variables.html .


Cheers !

On 23/03/17 08:30, Richard Hipp wrote:

On 3/22/17, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:

Hello Richard !

I noticed that sqlite do not use any memoization in json1 functions.

For example jsonExtractFunc and others parse the json string every time
it's called even when the json string is the same.

minimal example : "select json_extract(json, '$.name') name,
json_extract(json, '$.address') name from some_table;"

Could be possible to have some kind of memoization as a general option
for any sqlite functions ?'

In paragraph 3.0 of https://www.sqlite.org/json1.html:  "All json1
functions currently throw an error if any of their arguments are BLOBs
because BLOBs are reserved for a future enhancement in which BLOBs
will store the binary encoding for JSON."

But let me ask this:  Have you actually measured a performance
problem?  Or are you just assuming that because the implementation
parses the JSON anew each time it see it that it must therefore be
inefficient?



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

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

[Domingo Alvarez Duarte]

Hello !

This problem and other related with the lack of a "data dictionary" in 
sqlite, would be nice to have the sql parser exposed that way we could 
get the database structure on the fly and solve problems like the one 
discussed on this thread.


Cheers !

On 23/03/17 00:59, 邱朗 wrote:

“Much safer to have a habit to name views like the table names they derive from 
(when they derive from specific tables)”

That is what I am doing right now. So thanks for all the answers I got to 
confirm that there is no easy way to do it.
Qiulang


At 2017-03-22 19:52:59, "R Smith"  wrote:


On 2017/03/22 12:37 PM, Richard Hipp wrote:

On 3/22/17, 邱朗  wrote:

Hi,


Is there any way to drop view “automatically” when its associated table is
dropped?
It seems no way to do. Then if I drop a table, is there any (easy) way to
find views created based on it and I can drop view manually ?


There is no easy way to figure out what tables a view refers to.
Furthermore, some applications might want to drop a table and then
recreate that table (perhaps after adding a new constraint or
reordering the columns) and would like to keep using the VIEW.  Those
applications would not want the VIEW to be dropped automatically.


May I add further that views are tricky things. They can refer to
multiple tables, or other views which in turn refer to multiple tables
or yet other views (and have obscured the names of the original tables
via "AS" aliasing). There is no way to easily implement an automatic
view-of-table dropper. (I've tried to do something like this for an
sqlite tool long ago).

You could even look for the table name referenced in the view sql (by
simply doing something like:
SELECT name FROM sqlite_master WHERE type='view' AND sql LIKE
'%MyTableName%';
and drop all of those views that show up in the result - BUT, then you
have to be absolutely sure no View would ever use a field, refer to a
field, use another table or view, or have inferred / aliased fields or
use tables in a sub-query of any kind that will ever be the same (in
full or in part) as the table name you are trying to drop for.

It's a slippery slope. Much safer to have a habit to name views like the
table names they derive from (when they derive from specific tables),
such as
CREATE VIEW cities_view_someref AS SELECT ... FROM cities WHERE  ;
CREATE VIEW cities_view_someotherref AS SELECT ... FROM cities WHERE  ;

Then later when you drop everything, either in your code do:
DROP TABLE cities;
namelist = (SELECT name FROM sqlite_master WHERE type='view' AND name
LIKE 'cities_view_%';)
Then DROP every view in namelist via code.

--- or by creating an extended function in sqlite3, do this (pseudo coded):

FUNCTION  nmDROPTV(@arg1):
BEGIN
   DROP TABLE @arg1;
   For each name in (SELECT name FROM sqlite_master WHERE type='view'
AND name LIKE '[@arg1]_view_%';)
 do DROP VIEW name;
END;
Maybe also return the failure/success status of those functions, and
perhaps executing them in a transaction, etc.

So then executing:
DROPTV cities;
Should have the same effect as the first example.


___
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] Memoization in sqlite json1 functions

[Domingo Alvarez Duarte]

Hello Richard !

I noticed that sqlite do not use any memoization in json1 functions.

For example jsonExtractFunc and others parse the json string every time 
it's called even when the json string is the same.


minimal example : "select json_extract(json, '$.name') name, 
json_extract(json, '$.address') name from some_table;"


Could be possible to have some kind of memoization as a general option 
for any sqlite functions ?


Cheers !


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

[sqlite] FTS5 not working MY MISTAKE FORGET

[Domingo Alvarez Duarte]

Hello !

Sorry by my previous message, it was my mistake when quering the fts5.

I was quering like normal sql referring to a specific column in the 
where clause instead of using the table name.


Cheers !

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

[sqlite] FTS5 not working

[Domingo Alvarez Duarte]

Hello !

I just downloaded the 
http://www.sqlite.org/snapshot/sqlite-snapshot-201703062044.tar.gz 
compiled it with fts5 enabled and then tested it with this:


===

CREATE VIRTUAL TABLE email USING fts5(body);
insert into email(body) values('hello over there');
select rowid, body from email where body match 'over';

===

And get this:

===

sqlite3 < test-fts.sql
Error: near line 3: unable to use function MATCH in the requested context

===

Cheers !

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

Re: [sqlite] Strange - query returned duplicates, issue fixed after executing 'VACUUM'

[Domingo Alvarez Duarte]

Hello Edwin!

One thing that bugs me is how the indexes could becoming corrupt ?

- Sqlite bug ?

- Hard drive fault ?

Cheers !


On 01/03/17 09:28, Edwin Yip wrote:

Update 1 after another hours of checking:

A - ' seems that the issue is related to indexes - "pragma integrity_check"
would result in errors like:

row 23465 missing from index IndexRetailItemLastModTime
row 24187 missing from index IndexRetailItemLastModTime

B - The "VACCUM" only fix the issue for a table, but the very same issue
retains for another table.

C - Re-creating the indexes seem to have fixed the problem, so far...


On Wed, Mar 1, 2017 at 5:59 PM, Edwin Yip 
wrote:


Hello,

I need some insights for an issue I spent hours finding out - was it DB
file corruption or anything else? Details below:

The table is called ProductType, "select count(*) from ProductType"
returns 47 rows, which is correct.

There is a column called "LastModTime" and the  COLLATE is ISO8601.
"select count(*) from ProductType where (LastModTime >
"1899/12/30T09:23:21")" would return 60 rows, which is **wrong**.

After hours trying, I executed "VACUUM", now everything backs to normal.

This is wired, is it just an occasional DB file corruption or anything
worth mentioning, like any possible mistakes  might have done to the DB?

Table schema:
CREATE TABLE ProductType (
 ID  INTEGER PRIMARY KEY AUTOINCREMENT,
 MachineId   TEXTCOLLATE SYSTEMNOCASE,
 _ModificationTime   INTEGER,
 _ServerId   INTEGER,
 _UserId INTEGER,
 ParentIdINTEGER,
 TypeNr  TEXTCOLLATE SYSTEMNOCASE,
 TypeNameTEXTCOLLATE SYSTEMNOCASE,
 LastModTime TEXTCOLLATE ISO8601,
 PendingTransferTargetServer TEXTCOLLATE SYSTEMNOCASE
);


Thanks.


--
Best Regards,
Edwin Yip






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

Re: [sqlite] SQLite 3 locking

[Domingo Alvarez Duarte]

Hello !

On this snippet you can play with the parameters and find a value that 
can give a good result for a workload:


https://gist.github.com/mingodad/79225c88f8dce0f174f5

I did it to test sqlite3 wall mode but it also work without it and with 
disk/memory databases.


Cheers !


On 28/02/17 21:56, Matthew Ceroni wrote:

Appreciate it. Have a much better picture now.

Thanks

On Feb 28, 2017 4:48 PM, "Simon Slavin"  wrote:


On 1 Mar 2017, at 12:40am, Matthew Ceroni  wrote:


Once PENDING is obtained, what time out value controls how long to wait

to

get EXCLUSIVE?

There is only the one timeout value for each connection: the one you set.
Each attempt to escalate the lock level can take up to the timeout value
before it returns an error code.  I think.  You’re at the limits of my
knowledge here.

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


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

Re: [sqlite] OS X/Xcode build error: use of unknown builtin

[Domingo Alvarez Duarte]

Hello Anthony !

I also got those errors and did a dirty change to sqlite3 to compile, 
your proposal makes an all or nothing use of builtins, probably a one by 
one check/enable could give better result.


Cheers !


On 14/02/17 21:38, Anthony Chan (antchan2) wrote:

Hello,

I tried building SQLite 3.17.0 with OSX/Xcode and got the following errors:

-
sqlite3.c:28836:10: error: use of unknown builtin '__builtin_add_overflow' 
[-Wimplicit-function-declaration]
   return __builtin_add_overflow(*pA, iB, pA);
  ^
sqlite3.c:28856:10: error: use of unknown builtin '__builtin_sub_overflow' 
[-Wimplicit-function-declaration]
   return __builtin_sub_overflow(*pA, iB, pA);
  ^
sqlite3.c:28856:10: note: did you mean '__builtin_add_overflow'?
sqlite3.c:28836:10: note: '__builtin_add_overflow' declared here
   return __builtin_add_overflow(*pA, iB, pA);
  ^
sqlite3.c:28871:10: error: use of unknown builtin '__builtin_mul_overflow' 
[-Wimplicit-function-declaration]
   return __builtin_mul_overflow(*pA, iB, pA);
  ^
sqlite3.c:28871:10: note: did you mean '__builtin_sub_overflow'?
sqlite3.c:28856:10: note: '__builtin_sub_overflow' declared here
   return __builtin_sub_overflow(*pA, iB, pA);
  ^
3 errors generated.
-

I believe this is related to the recent change “Cleanup the usage of the 
SQLITE_DISABLE_INTRINSIC compile-time option…” 
(http://www.sqlite.org/src/info/798fb9d70d2e5f95) and the use of CLANG_VERSION 
to decide whether to use builtin functions:

#if defined(__clang__) && !defined(_WIN32) && !defined(SQLITE_DISABLE_INTRINSIC)
# define CLANG_VERSION \
 (__clang_major__*100+__clang_minor__*1000+__clang_patchlevel__)
#else
# define CLANG_VERSION 0
#endif

…

#elif SQLITE_BYTEORDER==1234 && (GCC_VERSION>=4003000 || CLANG_VERSION>=300)
   u32 x;
   memcpy(,p,4);
   return __builtin_bswap32(x);

According to Clang documentation 
(http://clang.llvm.org/docs/LanguageExtensions.html): “marketing version 
numbers should not be used to check for language features, as different vendors 
use different numbering schemes. Instead, use the Feature Checking Macros.”

With this in mind, I suggest creating a new macro that uses feature checking 
macros.  For example:

#if defined(__clang__) && !defined(_WIN32) && !defined(SQLITE_DISABLE_INTRINSIC)
# if __has_builtin(__builtin_add_overflow) && \
  __has_builtin(__builtin_sub_overflow) && \
  __has_builtin(__builtin_mul_overflow) && \
  __has_builtin(__builtin_bswap32) && \
  __has_builtin(__builtin_bswap64)
#  define CLANG_USE_INTRINSIC 1
# else
#  define CLANG_USE_INTRINSIC 0
# endif
#else
# define CLANG_USE_INTRINSIC 0
#endif

The tests would look like this:

#elif SQLITE_BYTEORDER==1234 && (GCC_VERSION>=4003000 || CLANG_USE_INTRINSIC!=0)
   u32 x;
   memcpy(,p,4);
   return __builtin_bswap32(x);

Your comments are welcome.

Thanks,

Anthony
antch...@cisco.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

Re: [sqlite] pragma_table_info column name is interpreted as reserved work?

[Domingo Alvarez Duarte]

Hello Keith !

You can try this that works:

select m.name   as TableName,
   i.cidas ColumnID,
   i.name   as ColumnName,
   i.type   as Affinity,
   i."notnull"as CanBeNull,
   i.dflt_value as DefaultValue,
   i.pk as PrimaryKeySeq
  from sqlite_master as m,
   pragma_table_info(m.name) as i
 where m.type='table';


On 12/02/17 15:24, Keith Medcalf wrote:

select m.name   as TableName,
i.cidas ColumnID,
i.name   as ColumnName,
i.type   as Affinity,
i.notnullas CanBeNull,
i.dflt_value as DefaultValue,
i.pk as PrimaryKeySeq
   from sqlite_master as m,
pragma_table_info(m.name) as i
  where m.type='table';


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

Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

[Domingo Alvarez Duarte]

Hello again !

I did some changes and got a better plan but it takes for ever, it seems 
that sqlite3 can not merge the where clause before the "group by" scan 
the whole tables:


===

CREATE VIEW despesas_municipio_orgao_list_view AS
SELECT
a."municipio_id" rowid,
a."ano_exercicio",
c.name as municipio,
b.name as orgao,
count(*) count,
sum(vl_despesa) total,
a."municipio_id",
a."orgao_id"
FROM main."despesas_master" AS a
JOIN municipios_orgaos b ON a.orgao_id=b.id
JOIN municipios c ON a.municipio_id=c.id
LEFT JOIN despesas_detalhe d ON a.id=d.despesa_id
JOIN tipos_despesa e ON d.tp_despesa_id=e.id and e.name='Valor Pago'
GROUP BY municipio_id, orgao_id;

explain query plan
select *
from despesas_municipio_orgao_list_view
where municipio_id=2;

===

Output:

===

selectid|order|from|detail
0|0|0|SCAN TABLE despesas_master AS a USING INDEX 
despesas_master_municipio_idx
0|1|1|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY 
(rowid=?)

0|2|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE despesas_detalhe AS d USING INDEX 
despesas_detalhe_despesa_idx (despesa_id=?)

0|4|4|SEARCH TABLE tipos_despesa AS e USING INTEGER PRIMARY KEY (rowid=?)

===

Executing this takes 3.10414 seconds (the result includes municipio_id=2)

===

select *
from despesas_municipio_orgao_list_view

limit 50

===

Executing this takes 111.839 seconds

===

select *
from despesas_municipio_orgao_list_view
where municipio_id=2;

===

Is this expected to be ok ?


On 11/01/17 19:50, Domingo Alvarez Duarte wrote:

Hello Richard !

It seems that sqlite query plan is having trouble with this query too:

===

create table if not exists municipios(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists municipios_orgaos(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists tipos_despesa(
id integer primary key,
name varchar not null unique collate nocase_slna
);

CREATE TABLE if not exists despesas_master(
id integer primary key,
ano_exercicio integer not null,
municipio_id integer not null,
orgao_id integer not null,
unique(municipio_id, orgao_id)
);

CREATE TABLE if not exists despesas_detalhe(
id integer primary key,
despesa_id integer not null,
tp_despesa_id integer not null
);
create index if not exists despesas_detalhe_despesa_idx on 
despesas_detalhe(despesa_id);


select 'expected good plan';
select '===';
explain query plan
select * from (
SELECT
a."municipio_id",
a."ano_exercicio",
c.name as municipio,
b.name as orgao,
--count(*) count,
a."orgao_id"
FROM despesas_master AS a
JOIN despesas_detalhe d ON a.id=d.despesa_id
and d.tp_despesa_id=(select id from tipos_despesa where name='any')
JOIN municipios c ON a.municipio_id=c.id
JOIN municipios_orgaos b ON a.orgao_id=b.id
--GROUP BY municipio_id, orgao_id
) tbl
WHERE municipio_id=2;

select '';
select 'unexpected bad plan';
select '===';
explain query plan
select * from (
SELECT
a.municipio_id,
a.ano_exercicio,
c.name as municipio,
b.name as orgao,
count(*) count,
a.orgao_id
FROM despesas_master AS a
JOIN despesas_detalhe d ON a.id=d.despesa_id
and d.tp_despesa_id=(select id from tipos_despesa where name='any')
JOIN municipios c ON a.municipio_id=c.id
JOIN municipios_orgaos b ON a.orgao_id=b.id
GROUP BY municipio_id, orgao_id
) tbl
WHERE municipio_id=2;

===

sqlite3 < the_above_sql_file

===

expected good plan
===
0|0|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|1|0|SEARCH TABLE despesas_master AS a USING INDEX 
sqlite_autoindex_despesas_master_1 (municipio_id=?)
0|2|3|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY 
(rowid=?)
0|3|1|SEARCH TABLE despesas_detalhe AS d USING INDEX 
despesas_detalhe_despesa_idx (despesa_id=?)

0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE tipos_despesa USING COVERING INDEX 
sqlite_autoindex_tipos_despesa_1 (name=?)


unexpected bad plan
===
0|0|1|SCAN TABLE despesas_detalhe AS d
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE tipos_despesa USING COVERING INDEX 
sqlite_autoindex_tipos_despesa_1 (name=?)
0|1|0|SEARCH TABLE despesas_master AS a USING INTEGER PRIMARY KEY 
(rowid=?)

0|2|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY 
(rowid=?)

0|0|0|USE TEMP B-TREE FOR GROUP BY

===

On 05/01/17 23:16, Richard Hipp wrote:

On 1/5/17, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:

Hello !

Today I found this unexpected behavior when using sqlite3 trunk:

When using views with joins sqlite3 is choosing expected plans except
for "LEFT JOIN", bellow is the snippet that shows this unexpected 
behavior.


===

create table

[sqlite] Now memory usage for update of big tables works fine

[Domingo Alvarez Duarte]

Hello Richard !

I just tested the latest sqlite3 with this "Changes to allow some 
multi-row UPDATE statements to avoid the two-pass approach." and before 
it was blowing up my machine (swapping) and now the memory usage remains 
basically the same as the startup without doing nothing.


Thank you !

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

Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

[Domingo Alvarez Duarte]

Hello Richard !

It seems that sqlite query plan is having trouble with this query too:

===

create table if not exists municipios(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists municipios_orgaos(
id integer primary key,
name varchar not null unique collate nocase_slna
);

create table if not exists tipos_despesa(
id integer primary key,
name varchar not null unique collate nocase_slna
);

CREATE TABLE if not exists despesas_master(
id integer primary key,
ano_exercicio integer not null,
municipio_id integer not null,
orgao_id integer not null,
unique(municipio_id, orgao_id)
);

CREATE TABLE if not exists despesas_detalhe(
id integer primary key,
despesa_id integer not null,
tp_despesa_id integer not null
);
create index if not exists despesas_detalhe_despesa_idx on 
despesas_detalhe(despesa_id);


select 'expected good plan';
select '===';
explain query plan
select * from (
SELECT
a."municipio_id",
a."ano_exercicio",
c.name as municipio,
b.name as orgao,
--count(*) count,
a."orgao_id"
FROM despesas_master AS a
JOIN despesas_detalhe d ON a.id=d.despesa_id
and d.tp_despesa_id=(select id from tipos_despesa where name='any')
JOIN municipios c ON a.municipio_id=c.id
JOIN municipios_orgaos b ON a.orgao_id=b.id
--GROUP BY municipio_id, orgao_id
) tbl
WHERE municipio_id=2;

select '';
select 'unexpected bad plan';
select '===';
explain query plan
select * from (
SELECT
a.municipio_id,
a.ano_exercicio,
c.name as municipio,
b.name as orgao,
count(*) count,
a.orgao_id
FROM despesas_master AS a
JOIN despesas_detalhe d ON a.id=d.despesa_id
and d.tp_despesa_id=(select id from tipos_despesa where name='any')
JOIN municipios c ON a.municipio_id=c.id
JOIN municipios_orgaos b ON a.orgao_id=b.id
GROUP BY municipio_id, orgao_id
) tbl
WHERE municipio_id=2;

===

sqlite3 < the_above_sql_file

===

expected good plan
===
0|0|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|1|0|SEARCH TABLE despesas_master AS a USING INDEX 
sqlite_autoindex_despesas_master_1 (municipio_id=?)
0|2|3|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY 
(rowid=?)
0|3|1|SEARCH TABLE despesas_detalhe AS d USING INDEX 
despesas_detalhe_despesa_idx (despesa_id=?)

0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE tipos_despesa USING COVERING INDEX 
sqlite_autoindex_tipos_despesa_1 (name=?)


unexpected bad plan
===
0|0|1|SCAN TABLE despesas_detalhe AS d
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE tipos_despesa USING COVERING INDEX 
sqlite_autoindex_tipos_despesa_1 (name=?)

0|1|0|SEARCH TABLE despesas_master AS a USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY 
(rowid=?)

0|0|0|USE TEMP B-TREE FOR GROUP BY

===

On 05/01/17 23:16, Richard Hipp wrote:

On 1/5/17, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:

Hello !

Today I found this unexpected behavior when using sqlite3 trunk:

When using views with joins sqlite3 is choosing expected plans except
for "LEFT JOIN", bellow is the snippet that shows this unexpected behavior.

===

create table if not exists a(id integer primary key, val text);
create table if not exists b(id integer primary key, a_id integer not
null, val text);
create view if not exists b_view as select b.*, a.* from b left join a
on b.a_id=a.id;
create table if not exists c(id integer primary key, b_id integer not
null, val text);

select 'bad unexpected plan';
explain query plan select c.*, b_view.* from c left join b_view on
c.b_id=b_view.id;

Can you rewrite your query as:

   SELECT *
FROM c LEFT JOIN b ON c.b_id=b.id
 LEFT JOIN a ON b.id=a.id;


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

[sqlite] About ticket c92ecff2ec5f1784 LEFT JOIN problem

[Domingo Alvarez Duarte]

Hello Richard !

Now that you are dealing with this ticket 
http://www.sqlite.org/src/info/c92ecff2ec5f1784 could be a good moment 
to acknowledge the problem reported before (see bellow), I'm not sure 
why you have answered this way without acknowledge any further action on 
it. I was expecting a bug ticket for it or at minimum a documentation of 
this weird behavior of the sqlite3 planner so other people could be 
aware of it.


Cheers !

===

On 1/5/17, Domingo Alvarez Duarte<mingo...@gmail.com>  wrote:


Hello !

Today I found this unexpected behavior when using sqlite3 trunk:

When using views with joins sqlite3 is choosing expected plans except
for "LEFT JOIN", bellow is the snippet that shows this unexpected behavior.

===

create table if not exists a(id integer primary key, val text);
create table if not exists b(id integer primary key, a_id integer not
null, val text);
create view if not exists b_view as select b.*, a.* from b left join a
on b.a_id=a.id;
create table if not exists c(id integer primary key, b_id integer not
null, val text);

select 'bad unexpected plan';
explain query plan select c.*, b_view.* from c left join b_view on
c.b_id=b_view.id;


Can you rewrite your query as:

  SELECT *
   FROM c LEFT JOIN b ON c.b_id=b.id
LEFT JOIN a ON b.id=a.id;
-- D. Richard Hipp

===

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

Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

[Domingo Alvarez Duarte]

Hello Don !

Thanks for reply !

It's my fault I wrote it without actually testing it for the purpose of 
show my use case:


===

--
-- Ideally I want to write the next query using the previous view
--
CREATE VIEW if not exists "despesas_master_list_view_tidy" AS
SELECT
a.*,
b.name as modalidade_licitacao,
c.name as credor,
d.*
FROM despesas_master AS a  fixed missing table ???
LEFT JOIN licitacao_modalidades AS b ON a.modalidade_lic=b.id
LEFT JOIN credores AS c ON a.credor_id=c.id
LEFT JOIN empenhos_list_view AS d ON a.empenho_id=d.id;

===


On 06/01/17 11:35, Don V Nielsen wrote:

In the below view, what is "a"? A FROM is not defined.

--
-- Ideally I want to write the next query using the previous view
--
CREATE VIEW if not exists "despesas_master_list_view_tidy" AS
SELECT
 a.*,
 b.name as modalidade_licitacao,
 c.name as credor,
 d.*
LEFT JOIN licitacao_modalidades AS b ON a.modalidade_lic=b.id
LEFT JOIN credores AS c ON a.credor_id=c.id
LEFT JOIN empenhos_list_view AS d ON a.empenho_id=d.id;


On Fri, Jan 6, 2017 at 3:27 AM, Domingo Alvarez Duarte <mingo...@gmail.com>
wrote:


Hello Richard !

The simple example I've sent is the minimal to show the problem, the real
database schema where I found this problem has a lot of small tables joined
and I was using the views to simplify (not duplicate) code, so on that case
it'll result in bloat and repetition. see bellow:

===

 create table if not exists municipios(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists municipios_orgaos(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists municipios_poder(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists credores(
 id integer primary key,
 cnpj_cpf varchar collate nocase_slna,
 name varchar not null collate nocase_slna,
 unique(cnpj_cpf, name)
 );

 create table if not exists tipos_despesa(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists funcoes_governo(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists subfuncoes_governo(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists programas_governo(
 id integer primary key,
 code integer,
 name varchar not null collate nocase_slna,
 unique(code, name)
 );

 create table if not exists acoes_governo(
 id integer primary key,
 code integer,
 name varchar not null collate nocase_slna,
 unique(code, name)
 );

 create table if not exists fontes_recursos(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists aplicacoes_fixo(
 id integer primary key,
 code integer,
 name varchar not null collate nocase_slna,
 unique(code, name)
 );

 create table if not exists aplicacoes_variavel(
 id integer primary key,
 code integer,
 name varchar not null collate nocase_slna,
 unique(code, name)
 );

 create table if not exists licitacao_modalidades(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

 create table if not exists elementos_despesa(
 id integer primary key,
 name varchar not null unique collate nocase_slna
 );

--
-- the table bellow has 6M records
--
 CREATE TABLE if not exists empenhos(
 id integer primary key,
 ano_exercicio integer not null,
 nr_empenho varchar not null,
 valor decimal,
 municipio_id integer not null,
 orgao_id integer not null,
 funcao_governo_id integer,
 subfuncao_governo_id integer,
 cd_programa integer,
 cd_acao integer,
 fonte_recurso_id integer,
 cd_aplicacao_fixo integer,
 elemento_id integer,
 unique(municipio_id, nr_empenho)
 );


--
-- the table bellow has 6M records
--
 CREATE TABLE if not exists despesas_master(
 id integer primary key,
 empenho_id integer not null,
 modalidade_lic integer not null,
 credor_id integer not null,
 historico_despesa varchar collate nocase_slna,
 unique(credor_id, empenho_id)
 );

--
-- the table bellow has 24M records
--
 CREATE TABLE if not exists despesas_detalhe(
 id integer primary key,
 id_despesa_detalhe integer not null,
 despesa_id integer not null,
 mes_referencia integer not 

Re: [sqlite] Quest for "scratch table" implementation in SQLite.

[Domingo Alvarez Duarte]

Hello Simon !

Thanks for reply !

I already raised this point here before and asked people using sqlite 
with multi million records and they said that they use sqlite mostly as 
append/read only on this case.


I raised one example when sqlite uses too much memory and even Richard 
Hipp recognized it.


I understand that sqlite is a good piece of software and indeed I use it 
a lot and care about it, but that doesn't mean it actually doesn't have 
limitations and room for improvement, I'm not detracting sqlite I'm 
pointing areas where it doesn't perform well.


Cheers !

==

On 10/2/16, Domingo Alvarez Duarte<mingo...@gmail.com>  wrote:


Hello !

I'm still fighting to use sqlite with a 20GB database and now I'm trying
to update a table with 980M rows but sqlite is eating all my memory
(2GB) and making blocking my computer (too much swap).

I'm in doubt if sqlite can really manage databases above 1GB.


For some (many) UPDATE statements, SQLite has to make two passes.  On
the first pass, it determines the ROWID of every row in the table that
needs to be changed, and then on the second pass it updates those
rows.

You are trying to updates 900 million rows all in one go, and SQLite
requires 8 bytes for each row, so that is 7.2 GB of RAM right there,
just to hold all of the rowids.

For your specific UPDATE, it seems like SQLite ought to be able to do
it all in one pass, and thus avoid using all that memory to store the
rowids.  This looks like an opportunity to improve the query planner.
-- D. Richard Hipp d...@sqlite.org

==


On 06/01/17 10:00, Simon Slavin wrote:

On 6 Jan 2017, at 10:10am, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:


Why I want this ?

To overcome sqlite3 limitations with one writer per database and difficulty to 
manage multi million records tables spreading then in individual databases.

SQLite does not have a limitation for one writer per database.  SQLite does not 
have any problem with multi-million records in a database.  I have a database 
with a table which has over a billion rows in it.

You may have other reasons for designing your database structure this way, but 
you are not doing it because of limitations in SQLite.

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] Quest for "scratch table" implementation in SQLite.

[Domingo Alvarez Duarte]

Hello !

I'm also looking for a way to achieve something similar but using 
attached databases, I mean:


Open a local database that automatically attach other databases and have 
views/triggers that use/apply to the attached databases.


Why I want this ?

To overcome sqlite3 limitations with one writer per database and 
difficulty to manage multi million records tables spreading then in 
individual databases.


Something that the "temp" database can already partially do without 
persistence.


Cheers !


On 06/01/17 06:09, Dominique Devienne wrote:

On Thu, Jan 5, 2017 at 11:40 PM, Simon Slavin  wrote:


On 5 Jan 2017, at 7:26pm, Smith, Randall  wrote:

I'm still trying to figure out a graceful way to implement a "scratch"

database table that has the following properties:

o Can be created on demand while a database connection is open.
o Can include references to other material in the database to

ensure integrity.

o Will be automatically deleted when the database is closed.

It’s not possible to combine the second and third requirements.  SQLite is
designed as a multi-process multi-user database.


Not quite though. SQLite does support multi-process multi-user databases of
course,
but one of its primary use case is as an application format too, and often
in that
configuration, there's 1 process and 1 user. Or during an EXCLUSIVE
transaction too.

FWIW. --DD
___
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] Bad query plan selection only with "LEFT JOIN"

[Domingo Alvarez Duarte]

cacao_fixo=l.id
LEFT JOIN elementos_despesa AS n ON a.elemento_id=n.id;
--
-- Ideally I want to write the next query using the previous view
--
CREATE VIEW if not exists "despesas_master_list_view_tidy" AS
SELECT
a.*,
b.name as modalidade_licitacao,
c.name as credor,
d.*
LEFT JOIN licitacao_modalidades AS b ON a.modalidade_lic=b.id
LEFT JOIN credores AS c ON a.credor_id=c.id
LEFT JOIN empenhos_list_view AS d ON a.empenho_id=d.id;

--
-- But to overcome the sqlite3 planer bug you suggest to do this
--
CREATE VIEW if not exists "despesas_master_list_view_dirty" AS
SELECT
a."id",
d."ano_exercicio",
d."nr_empenho",
d."valor",
e.name as municipio,
f.name as orgao,
g.name as funcao,
h.name as subfuncao,
i.name as programa,
k.name as acao,
l.name as fonte_recurso,
m.name as aplicacao_fixo,
n.name as elemento,
a."historico_despesa",
a."credor_id",
a."modalidade_lic",
a.empenho_id,
d."municipio_id",
d."orgao_id",
d."funcao_governo_id",
d."subfuncao_governo_id",
d."cd_programa",
d."cd_acao",
d."fonte_recurso_id",
d."cd_aplicacao_fixo",
d."elemento_id"
FROM "despesas_master" AS a
LEFT JOIN licitacao_modalidades AS b ON a.modalidade_lic=b.id
LEFT JOIN credores AS c ON a.credor_id=c.id
LEFT JOIN empenhos AS d ON a.empenho_id=d.id
LEFT JOIN municipios AS e ON d.municipio_id=e.id
LEFT JOIN municipios_orgaos AS f ON d.orgao_id=f.id
LEFT JOIN funcoes_governo AS g ON d.funcao_governo_id=g.id
LEFT JOIN subfuncoes_governo AS h ON d.subfuncao_governo_id=h.id
LEFT JOIN programas_governo AS i ON d.cd_programa=i.id
LEFT JOIN acoes_governo AS k ON d.cd_acao=k.id
LEFT JOIN fontes_recursos AS l ON d.fonte_recurso_id=l.id
LEFT JOIN aplicacoes_fixo AS m ON d.cd_aplicacao_fixo=m.id
LEFT JOIN elementos_despesa AS n ON d.elemento_id=n.id;

--
-- Ideally I want to write the next query using the previous view
--
CREATE VIEW if not exists "despesas_detalhe_list_view_tidy" AS
SELECT
a.*,
ab.name AS despesa_tipo,
b.*
FROM "despesas_detalhe" AS a
LEFT JOIN tipos_despesa AS ab ON a.tp_despesa_id=ab.id
LEFT JOIN despesas_master_list_view AS b ON a.despesa_id=b.id;

--
-- But to overcome the sqlite3 planer bug you suggest to do this
--
CREATE VIEW if not exists "despesas_detalhe_list_view_dirty" AS
SELECT
a.id,
a.id_despesa_detalhe,
ab.name as tp_despesa,
ac.name as modalidade_lic,
d."ano_exercicio",
a.mes_referencia,
d."nr_empenho",
a.dt_emissao_despesa,
a.vl_despesa,
d."valor",
e.name as municipio,
f.name as orgao,
g.name as funcao,
h.name as subfuncao,
i.name as programa,
k.name as acao,
l.name as fonte_recurso,
m.name as aplicacao_fixo,
n.name as elemento,
b."historico_despesa",
a.despesa_id,
a.tp_despesa_id,
b."credor_id",
b."modalidade_lic",
b.empenho_id,
d."municipio_id",
d."orgao_id",
d."funcao_governo_id",
d."subfuncao_governo_id",
d."cd_programa",
d."cd_acao",
d."fonte_recurso_id",
d."cd_aplicacao_fixo",
d."elemento_id"
FROM "despesas_detalhe" AS a
LEFT JOIN tipos_despesa AS ab ON a.tp_despesa_id=ab.id
LEFT JOIN despesas_master AS b ON a.despesa_id=b.id
LEFT JOIN licitacao_modalidades AS ac ON b.modalidade_lic=ac.id
LEFT JOIN credores AS c ON b.credor_id=c.id
LEFT JOIN empenhos AS d ON b.empenho_id=d.id
LEFT JOIN municipios AS e ON d.municipio_id=e.id
LEFT JOIN municipios_orgaos AS f ON d.orgao_id=f.id
LEFT JOIN funcoes_governo AS g ON d.funcao_governo_id=g.id
LEFT JOIN subfuncoes_governo AS h ON d.subfuncao_governo_id=h.id
LEFT JOIN programas_governo AS i ON d.cd_programa=i.id
LEFT JOIN acoes_governo AS k ON d.cd_acao=k.id
LEFT JOIN fontes_recursos AS l ON d.fonte_recurso_id=l.id
LEFT JOIN aplicacoes_fixo AS m ON d.cd_aplicacao_fixo=m.id
LEFT JOIN elementos_despesa AS n ON d.elemento_id=n.id;
===

Cheers !

On 05/01/17 23:16, Richard Hipp wrote:

On 1/5/17, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:

Hello !

Today I found this unexpected behavior when using sqlite3 trunk:

When using views with joins sqlite3 is choosing expected plans except
for "LEFT JOIN", bellow is the snippet that shows this unexpected behavior.

===

create table if not exists a(id integer primary key, val text);
create table if not exists b(id integer primary key, a_id integer not
null, val text);
create view if not exists b_view as select b.*, a.* from b left join a
on b.a_id=a.id;
create table if not exists c(id integer primary key, b_id integer not
null, val text);

select 'bad unexpected plan';
explain query plan select c.*, b_view.* from c left join b_view on
c.b_id=b_view.id;

Can you rewrite your query as:

   SELECT *
FROM c LEFT JOIN b ON c.b_id=b.id
 LEFT JOIN a ON b.id=a.id;


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

[sqlite] Bad query plan selection only with "LEFT JOIN"

[Domingo Alvarez Duarte]

Hello !

Today I found this unexpected behavior when using sqlite3 trunk:

When using views with joins sqlite3 is choosing expected plans except 
for "LEFT JOIN", bellow is the snippet that shows this unexpected behavior.


===

create table if not exists a(id integer primary key, val text);
create table if not exists b(id integer primary key, a_id integer not 
null, val text);
create view if not exists b_view as select b.*, a.* from b left join a 
on b.a_id=a.id;
create table if not exists c(id integer primary key, b_id integer not 
null, val text);


select 'good expected plan';
explain query plan select c.*, b_view.* from c, b_view where 
c.b_id=b_view.id;


select 'good expected plan';
explain query plan select c.*, b_view.* from c join b_view on 
c.b_id=b_view.id;


select 'bad unexpected plan';
explain query plan select c.*, b_view.* from c left join b_view on 
c.b_id=b_view.id;


===

Output of sqlite3 < test-sqlte-bad-plan.sql :

===

good expected plan
0|0|0|SCAN TABLE c
0|1|1|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?)
good expected plan
0|0|0|SCAN TABLE c
0|1|1|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?)
bad unexpected plan
1|0|0|SCAN TABLE b
1|1|1|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|SCAN TABLE c
0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (id=?)

===

Cheers !

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

Re: [sqlite] Parallel access to read only in memory database

[Domingo Alvarez Duarte]

Hello !

But SQLightning is a dead project, isn't it ?

version 3.7.17 
 



hyc  
committed on 12 Sep 2013


Cheers !

On 09/10/16 12:15, Howard Chu wrote:

Daniel Meyer wrote:

We are interested in using sqlite as a read only, in memory, parallel
access database.  We have database files that are on the order of 100GB
that we are loading into memory.  We have found great performance when
reading from a single thread.  We need to scale up to have many parallel
reader threads.  Once the DB is created it never needs to be 
modified.  How

can we allow many reader threads on an in memory, write once read many
times database and achieve multi-core performance?  Is this possible 
with

sqlite?


Use SQLightning, it's designed specifically for write once read many 
workloads.




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

[sqlite] Error trying to make sqlite3 documentation

[Domingo Alvarez Duarte]

Hello !

I'm trying to make sqlite3 documentation to use locally and got this error:



make all

...

Processing ./pages/btreemodule.in
./tclsh: missing close-brace
while executing
"hd_puts {"
("eval" body line 590)
invoked from within
"eval "hd_puts \173$text\175""
(procedure "hd_resolve" line 5)
invoked from within
"hd_resolve [subst {

$PREAMBLE


style="font-size:2em;text-align:center;color:#80a796">$zTitle


This docu..."
("eval" body line 38)
invoked from within
"eval {

hd_keywords {btree design}
source [file join $::DOC pages fancyformat.tcl]

foreach header {btree.h sqliteInt.h} {
  set fd [open [file join $..."
("eval" body line 2)
invoked from within
"eval "hd_resolve \173$in\175""
("foreach" body line 16)
invoked from within
"foreach infile [lrange $argv 3 end] {
  cd $HOMEDIR
  puts "Processing $infile"
  set fd [open $infile r]
  set in [read $fd]
  close $fd
  if {[strin..."
(file "./wrap.tcl" line 783)
make: *** [base] Error 1



Has someone build the latest documentation ?

Cheers !

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

Re: [sqlite] How/Where to check if a table exists and read it ?

[Domingo Alvarez Duarte]

Hello Richard !

Looking at the documentation, code and code comments I found that what 
I'm looking for somehow already exists in sqlite3, I mean I want the 
functionality of "temp" database as permanent let's call this database 
as "meta" anything created inside "meta" database would persist on disk 
and can be read again.


Can this be done ?

Cheers !


On 05/10/16 16:44, Richard Hipp wrote:

On 10/5/16, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:

I just found that the changes I made to sqlite3 to allow reference
objects on attached databases does not work properly,

Where in the view execution path the table/view qualifiers could be
discarded ?


I'm not sure exactly what you are looking for, perhaps you are seeking
the "sqlite3Fix()" routines found in attach.c.



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

Re: [sqlite] How/Where to check if a table exists and read it ?

[Domingo Alvarez Duarte]

Hello Richard !

I just found that the changes I made to sqlite3 to allow reference 
objects on attached databases does not work properly, it does work when 
the tables/views from more than one database are not equal but if they 
are equal even with qualification it seems to always try to find first 
in the "main" database without qualification or always discard 
qualification.


Where in the view execution path the table/view qualifiers could be 
discarded ?


Cheers !

Example:



.open db1.db
create table if not exists t(id integer, count1 integer, count2 integer);
insert or ignore into t values(1, 5, 5);
create table if not exists t1(id integer, count1 integer, count2 integer);
insert or ignore into t1 values(1, 1, 1);

.open db2.db
create table if not exists t(id integer, count1 integer, count2 integer);
insert or ignore into t values(1, 10, 10);
create table if not exists t2(id integer, count1 integer, count2 integer);
insert or ignore into t2 values(1, 2, 2);

.open db1.db
pragma use_attached_dbs=ON;
attach database 'db2.db' as db2;

select a.count1+b.count1, a.count2+b.count2
from t1 a
join t2 b on a.id=b.id;
--3|3

create view if not exists v1 as
select a.count1+b.count1, a.count2+b.count2
from t1 a
join t2 b on a.id=b.id;

select * from v1;
--3|3

select a.count1+b.count1, a.count2+b.count2
from t a
join db2.t b on a.id=b.id;
--15|15

create view if not exists v2 as
select a.count1+b.count1, a.count2+b.count2
from t a
join db2.t b on a.id=b.id;

select * from v2;
--10|10


On 04/10/16 17:30, Richard Hipp wrote:

On 10/4/16, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:

The problem is I didn't found yet the point where I should intercept the
"openDatabase" where I plan to check if there is a table named for
example "use_attached_dbs" and then attach the databases on that table
and then reread the schema to make the views work.

The place to do so probably is at the end of "openDatabase", can someone
shed some light here ?


The sqlite3Init() routine found at
https://www.sqlite.org/src/artifact/b1140c3d0cf59bc8?ln=355



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

[sqlite] How to build the sqlite3 documentation locally ?

[Domingo Alvarez Duarte]

Hello !

I just trying to build the sqlite3 documentation to run locally but it 
seems to depend on "../bld/tclsqlite3.c" amalgamation but it doesn't 
exists in the repository or in the sqlite3 repository.


How to generate it or where to get it ?

Cheers !

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

Re: [sqlite] How/Where to check if a table exists and read it ?

[Domingo Alvarez Duarte]

Hello Richard !

Thanks for reply !

I found the second point that was also controlling the restriction of 
referencing objects in other databases.


Now it seems to work and I'll leave the initialization to the user level 
code for now, when the usage normalize I'll revisit it again to see if 
is worth move that code to my custom sqlite3.


Cheers !

 my changes

  /*
  **   PRAGMA use_attached_dbs
  **   PRAGMA use_attached_dbs = ON/OFF
  **
  ** The first form reports the current setting for the
  ** use_attached_dbs flag.  The second form changes the use_attached_dbs
  ** flag setting and reports thenew value.
  */
  case PragTyp_USE_ATTACHED_DBS: {
int b = -1;
if( zRight ){
  b = sqlite3GetBoolean(zRight, 0);
  sqlite3_limit(db, SQLITE_LIMIT_USE_ATTACHED_DBS, b);
}
b = sqlite3_limit(db, SQLITE_LIMIT_USE_ATTACHED_DBS, -1);
returnSingleInt(v, "use_attached_dbs", b);
break;
  }



Table *sqlite3LocateTableItem(
  Parse *pParse,
  u32 flags,
  struct SrcList_item *p
){
  const char *zDb;
  int use_attached_dbs = sqlite3_limit(pParse->db, 
SQLITE_LIMIT_USE_ATTACHED_DBS, -1);  my changes

  assert( p->pSchema==0 || p->zDatabase==0 );
  if( p->pSchema && !use_attached_dbs){ my changes
int iDb = sqlite3SchemaToIndex(pParse->db, p->pSchema);
zDb = pParse->db->aDb[iDb].zDbSName;
  }else{
zDb = p->zDatabase;
  }
  return sqlite3LocateTable(pParse, flags, p->zName, zDb);
}


int sqlite3FixSrcList(
  DbFixer *pFix,   /* Context of the fixation */
  SrcList *pList   /* The Source list to check and modify */
){
  int i, use_attached_dbs;
  const char *zDb;
  struct SrcList_item *pItem;

  use_attached_dbs = sqlite3_limit(pFix->pParse->db, 
SQLITE_LIMIT_USE_ATTACHED_DBS, -1); my changes

  if( NEVER(pList==0) ) return 0;
  zDb = pFix->zDb;
  for(i=0, pItem=pList->a; inSrc; i++, pItem++){
if( pFix->bVarOnly==0 ){
  if( pItem->zDatabase && !use_attached_dbs && 
sqlite3StrICmp(pItem->zDatabase, zDb) ){ my changes

sqlite3ErrorMsg(pFix->pParse,
"%s %T cannot reference objects in database %s",
pFix->zType, pFix->pName, pItem->zDatabase);
return 1;
  }
  sqlite3DbFree(pFix->pParse->db, pItem->zDatabase);
  pItem->zDatabase = 0;
  pItem->pSchema = pFix->pSchema;
}
#if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
if( sqlite3FixSelect(pFix, pItem->pSelect) ) return 1;
if( sqlite3FixExpr(pFix, pItem->pOn) ) return 1;
#endif
  }
  return 0;
}



On 04/10/16 17:30, Richard Hipp wrote:

On 10/4/16, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:

The problem is I didn't found yet the point where I should intercept the
"openDatabase" where I plan to check if there is a table named for
example "use_attached_dbs" and then attach the databases on that table
and then reread the schema to make the views work.

The place to do so probably is at the end of "openDatabase", can someone
shed some light here ?


The sqlite3Init() routine found at
https://www.sqlite.org/src/artifact/b1140c3d0cf59bc8?ln=355



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

[sqlite] How/Where to check if a table exists and read it ?

[Domingo Alvarez Duarte]

Hello !

I'm trying to make changes to sqlite to allow multi-databases databases 
(I mean have a database that is onl used to anchor attached databases 
and store mutli-database views/triggers).


I already managed to add a new pragma "use_attached_databases=ON/OFF" to 
allow views/triggers to have references to attached databases and have 
it working (I mean I can create views/triggers that references other 
databases and it's accepted).


The problem is when I open a database that contains those views/triggers 
it shows errors saying:


Error: malformed database schema (v1) - view v1 cannot reference objects 
in database db1


The problem is I didn't found yet the point where I should intercept the 
"openDatabase" where I plan to check if there is a table named for 
example "use_attached_dbs" and then attach the databases on that table 
and then reread the schema to make the views work.


The place to do so probably is at the end of "openDatabase", can someone 
shed some light here ?


P.S.: This is for a custom use of sqlite3 so please don't bother to 
reply to say why I should not been doing this.


Cheers !

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

Re: [sqlite] Fastest way to backup a "live" database

[Domingo Alvarez Duarte]

Hello Eric !

Have you looked at the ext/session extension ?

It seems that it can be the answer for your problem. The basic idea is 
you'll create a function that will receive the changes made to the 
database and then you can incrementally apply then on the backup database.


This way the memory usage will be low (as long you do not have millions 
changes in a short period of time).


Cheers !


On 04/10/16 10:53, Eric Grange wrote:

Hi,

Given a fairly large database (dozens of gigabytes), which uses WAL, and is
being accessed continuously (mostly read transactions, but regular write
transactions as well), what are the fastest and less disruptive ways to
back it up?

A basic ".backup" from the CLI can occasionnally take hours, as it is
thrown thrown off whenever large write transactions occur.

I have found the following approaches to seem to work, but would like a
confirmation:
- using temp_store = MEMORY
- using synchronous = 0
- using a cache_size as large a possible (as high as possible without
running out of memory)

I am going on the assumption that if something fails during backup, the
backup itself will be toast anyway, but is that safe otherwise?

Also the precompiled CLI for Windows (sqlite3.exe) of 3.14.2 fails at
around 2 GB RAM, which in my case is about 500,000 pages, is that already
too high or could it be worth going with a 64bits CLI?

Thanks!

Eric
___
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] smartest way to exchange a sqlite3 database with another empty

[Domingo Alvarez Duarte]

Hello Richard !

Ok I missed this point, but still while fighting to use sqlite3 with big 
databases I was thinking on some custom changes to allow sqlite relax 
some restrictions:
1- Create a new sqlite reserved table for register attached databases, 
this way every time a program try to open an sqlite3 database it will 
look if this table exists and will automatically attach then, also will 
allow views and triggers between attached databases.
2- For operations like "vacuum" and to force processes to reopen the 
database another sqlite reserved table could be created like 
"sqlite_open_instead" if that table exists it indicates anyone trying to 
open that database to open the one indicated there instead, this would 
work on any operating system (maybe not too elegant but it should work, 
or anything else using a similar mechanism).


Cheers !

On 03/10/16 09:53, Richard Hipp wrote:

On 10/3/16, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:

Hello !

Thinking about this and the problem I'm experiencing with big databases
with sqlite3 "vacuum" probably could be a good idea to use a flag in the
sqlite3 header to inform other processes to reopen the database.


That might work on unix.  But on Windows, the operating system
prohibits files from being renamed while the file is open.



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

Re: [sqlite] smartest way to exchange a sqlite3 database with another empty

[Domingo Alvarez Duarte]

Hello !

Thinking about this and the problem I'm experiencing with big databases 
with sqlite3 "vacuum" probably could be a good idea to use a flag in the 
sqlite3 header to inform other processes to reopen the database.


Right now every time sqlite3 would perform an operation on a database it 
aquires a lock on the file and check to see if the schema has changed, 
at the same time it can check if it needs to reopen the database if a 
flag is set, this way it can work in any operating system.


Cheers !


On 03/10/16 06:51, Richard Hipp wrote:

On 10/3/16, Luca Ferrari  wrote:

Hi all,
in one of my application I use a sqlite3 database as a log of
activity. As you can imagine the file grows as time goes by, so I'm
figuring I've to substitute it with an empty one once a good size is
reached.
What is the right way to do it without having to stop the application
(and therefore without knowing when a new I/O operation will be
issued)?
Does sqlite3 provide some facility that could come into help (e.g.,
connected databases)?

Safe way:  In a separate process, use the backup API
(https://www.sqlite.org/backup.html) to copy the content of the main
DB over to a separate DB, then "DELETE FROM log;" on the main DB.
This will work without any cooperation on the part of the application.
But it does involving a lot of I/O.

Alternative:  Modify the application so that it automatically detects
when the database is getting to large (perhaps using PRAGMA page_count
- https://www.sqlite.org/pragma.html#pragma_page_count) and then (1)
closes the database connection, (2) renames the database file to a
backup, and (3) reopens the main DB and reinitializes the schema.

You cannot rename a database file while another process has that
database open.  Windows simply will not allow that.  If you do it on
Unix, then the process that has the file open will not know that the
file has been renamed and will continue to write to the original file.



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

Re: [sqlite] Using too much memory to execute an update query

[Domingo Alvarez Duarte]

Hello Rob !

How do you use those big databases ?

Append only ? Read only ? Constant read write ? Foreign keys ?

Could you show the schema to talk about it ?

Cheers !


On 02/10/16 17:44, Rob Willett wrote:
We have production databases with 30-50GB and have no issues with 
managing them. Mind you we have more memory, though we only have 8GB 
as thats all our VM provider allows us.


After saying that we have never seen any performance issues that 
weren't due to our schemas or our bad design. Our working assumption 
is that if there's a problem, its our fault before we blame SQLite. So 
far that working assumption had held true :)


We did some tests (just for the hell of it) to 200GB with dummy data 
and had no issues I can recall.


I know that other people on this list have far, far bigger production 
databases than us.


I'm not a SQLite expert but I look on machines with 2GB of main memory 
as rather small for this sort of thing.


Rob

On 2 Oct 2016, at 21:35, Domingo Alvarez Duarte wrote:


Hello !

I'm still fighting to use sqlite with a 20GB database and now I'm 
trying to update a table with 980M rows but sqlite is eating all my 
memory (2GB) and making blocking my computer (too much swap).


I'm in doubt if sqlite can really manage databases above 1GB.

Have someone any experience with big databases ?

Cheers !

The query:

--explain query plan
update bolsas_familia set favorecido_id=(select id from favorecidos 
where nis=favorecido_id);


Query plan:

selectid|order|from|detail
0|0|0|SCAN TABLE bolsas_familia
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 0
0|0|0|SEARCH TABLE favorecidos USING COVERING INDEX 
favorecidos_nis_idx (nis=?)


The schema is:

CREATE TABLE bolsas_familia(
id integer primary key,
favorecido_id integer not null,
valor_parcela numeric,
mes_competencia integer
); //980M rows

CREATE TABLE "favorecidos"(
id integer primary key,
nis integer,
nome varchar collate nocase_slna,
municipio_id integer,
programa_id integer not null,
fonte_finalidade_id integer not null,
first_ano_mes integer
); //14M rows

CREATE UNIQUE INDEX favorecidos_nis_idx ON favorecidos(nis);

Cheers !

___
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] Using too much memory to execute an update query

[Domingo Alvarez Duarte]

Hello Richard !

On 02/10/16 18:02, Richard Hipp wrote:

For your specific UPDATE, it seems like SQLite ought to be able to do
it all in one pass, and thus avoid using all that memory to store the
rowids.  This looks like an opportunity to improve the query planner.


That would be nice, also I noticed that sqlite seem to do a delete then 
an insert for updates even when it would be possible to update in place, 
with tables with several fields and indices seems to be a waste of work.


Cheers !

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

[sqlite] Using too much memory to execute an update query

[Domingo Alvarez Duarte]

Hello !

I'm still fighting to use sqlite with a 20GB database and now I'm trying 
to update a table with 980M rows but sqlite is eating all my memory 
(2GB) and making blocking my computer (too much swap).


I'm in doubt if sqlite can really manage databases above 1GB.

Have someone any experience with big databases ?

Cheers !

The query:

--explain query plan
update bolsas_familia set favorecido_id=(select id from favorecidos 
where nis=favorecido_id);


Query plan:

selectid|order|from|detail
0|0|0|SCAN TABLE bolsas_familia
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 0
0|0|0|SEARCH TABLE favorecidos USING COVERING INDEX favorecidos_nis_idx 
(nis=?)


The schema is:

CREATE TABLE bolsas_familia(
id integer primary key,
favorecido_id integer not null,
valor_parcela numeric,
mes_competencia integer
); //980M rows

CREATE TABLE "favorecidos"(
id integer primary key,
nis integer,
nome varchar collate nocase_slna,
municipio_id integer,
programa_id integer not null,
fonte_finalidade_id integer not null,
first_ano_mes integer
); //14M rows

CREATE UNIQUE INDEX favorecidos_nis_idx ON favorecidos(nis);


Cheers !

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

Re: [sqlite] Why so much I/O ? Can sqlite be improved ?

[Domingo Alvarez Duarte]

Hello Bob !

I'm using the default sqlite page size, but I also did a try with 32KB 
page size and I've got a bi smaller overall database size but no visible 
perfomance gain in terms of time and I/O.


Also the memory usage skyrocked, also forcing memory swap.

The OS was OS X yosemite, I also posted before a small program with a 
sample of the problematic data only which end with a database of around 
340MB and the same poor perfomance.


Cheers !


On 01/10/16 19:34, Bob Friesenhahn wrote:

On Sat, 1 Oct 2016, Domingo Alvarez Duarte wrote:


Hello !

I'm using sqlite (trunk) for a database (see bellow) and for a final 
database file of 22GB a "vacuum" was executed and doing so it  made a 
lot of I/O ( 134GB reads and 117GB writes in 2h:30min).


What means are you using the evaluate the total amount of I/O?

At what level (e.g. OS system call, individual disk I/O) are you 
measuring the I/O?


If the problem is more physical disk I/O than expected then is it 
possible that the underlying filesystem blocksize does not match the 
blocksize that SQLite is using?  You may have an issue with write 
amplification at the filesystem level.


Bob


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

Re: [sqlite] Why so much I/O ? Can sqlite be improved ?

[Domingo Alvarez Duarte]

Hello Keith !

Probably have you seem in the code attached that also tried that once 
and even the mmap but it doesn't had any visible improvement. Also due 
to the data distribution 80% of the data inserts took 20% of the total 
time and the other 20% of data insert took 80% of the total time.


The final database is has a poor overal performance for what I'm used 
when using sqlite for small databases, I also tried to do it with mysql 
and postgresql but the performance for a so simple database is terrible.


Cheers !

db.exec_dml("PRAGMA synchronous = 0;");
db.exec_dml("PRAGMA journal_mode = WAL");
//db.exec_dml("PRAGMA journal_mode = MEMORY;");
//db.exec_dml("PRAGMA journal_mode = OFF;");
//db.exec_dml("PRAGMA locking_mode = EXCLUSIVE;");
db.exec_dml("PRAGMA temp_store = MEMORY;");
//db.exec_dml("PRAGMA threads = 4;");
//db.exec_dml("PRAGMA mmap_size = 6400;");
auto gigabyte = 1024*1024*1024;
db.exec_dml("PRAGMA mmap_size=" + (gigabyte*16));
//print("mmap_size", db.exec_get_one("PRAGMA mmap_size;"));

//db.exec_dml("PRAGMA cache_size = -64000");
//print("cache_size", db.exec_get_one("PRAGMA cache_size;"));


On 01/10/16 19:21, Keith Medcalf wrote:

Did you change the cache size?  The default is rather small for a database of 
22 GB.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
On Behalf Of Domingo Alvarez Duarte
Sent: Saturday, 1 October, 2016 15:19
To: SQLite mailing list
Subject: Re: [sqlite] Why so much I/O ? Can sqlite be improved ?

Hello Simon !

Thanks for reply !

I already know your suggestions and if you look at the database schema
and the program used to insert data you can see that  there is no
unnecessary indices active and all inside transactions.

About the vacuum I also understand the need to rewrite the whole
database but I'm not sure if it's really necessary to do almost 5 times
the database size in both reads and writes (also an equivalent amount of
I/O happened during insertions).

Cheers !


On 01/10/16 18:12, Simon Slavin wrote:

On 1 Oct 2016, at 9:27pm, Domingo Alvarez Duarte <mingo...@gmail.com>

wrote:

I'm using sqlite (trunk) for a database (see bellow) and for a final

database file of 22GB a "vacuum" was executed and doing so it  made a lot
of I/O ( 134GB reads and 117GB writes in 2h:30min).

Can something be improved on sqlite to achieve a better performance ?

VACUUM rewrites the entire database.  It will always do a lot of IO.

You should never need to use VACUUM in a production setting.  Perhaps in a
once-a-year maintenance utility but not in normal use.

The fastest way to do lots of insertion is

DROP all INDEXes
DELETE FROM all TABLEs
Do your insertions, bundling up each thousand (ten thousand ?
depends on your system) uses of INSERT in a transaction
if you really want to do VACUUM, do it here
reCREATE all your INDEXes
ANALYZE

(the ANALYZE will also do lots of IO, not as much as VACUUM, but it may

speed up all WHERE / ORDER BY clauses).

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



___
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] Why so much I/O ? Can sqlite be improved ?

[Domingo Alvarez Duarte]

Hello Simon !

I already did it without using "wal" and the result was the same.

And even for my surprise in one try I stopped at the middle performed an 
"analyze" and the performance deteriorated a lot to a point that I 
needed to delete the stats tables to get the better performance without 
"analyze".


I also tried with the lsm module and got a bit better performance but 
with an irregular timing and a bigger disk usage (20%).


Also tested with lmdb with an astonishing insertion rate but with a lot 
more disk usage and irregular timing.


Also tested with leveldb with a worse performance and almost twice disk 
space usage.


The data distribution on some tables seem to fall into the worst corner 
cases for btrees.


Cheers !


On 01/10/16 18:26, Simon Slavin wrote:

On 1 Oct 2016, at 10:18pm, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:


About the vacuum I also understand the need to rewrite the whole database but 
I'm not sure if it's really necessary to do almost 5 times the database size in 
both reads and writes (also an equivalent amount of I/O happened during 
insertions).

Can you try it without

db.exec_dml("PRAGMA wal_checkpoint(FULL);");

and see if that improves time ?

That's the only thing I can see.  You're using a nested INSERT OR IGNORE 
command I'm not familiar with.

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] Why so much I/O ? Can sqlite be improved ?

[Domingo Alvarez Duarte]

Hello Simon !

Thanks for reply !

I already know your suggestions and if you look at the database schema 
and the program used to insert data you can see that  there is no 
unnecessary indices active and all inside transactions.


About the vacuum I also understand the need to rewrite the whole 
database but I'm not sure if it's really necessary to do almost 5 times 
the database size in both reads and writes (also an equivalent amount of 
I/O happened during insertions).


Cheers !


On 01/10/16 18:12, Simon Slavin wrote:

On 1 Oct 2016, at 9:27pm, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:


I'm using sqlite (trunk) for a database (see bellow) and for a final database file of 
22GB a "vacuum" was executed and doing so it  made a lot of I/O ( 134GB reads 
and 117GB writes in 2h:30min).

Can something be improved on sqlite to achieve a better performance ?

VACUUM rewrites the entire database.  It will always do a lot of IO.  You 
should never need to use VACUUM in a production setting.  Perhaps in a 
once-a-year maintenance utility but not in normal use.

The fastest way to do lots of insertion is

DROP all INDEXes
DELETE FROM all TABLEs
Do your insertions, bundling up each thousand (ten thousand ?
depends on your system) uses of INSERT in a transaction
if you really want to do VACUUM, do it here
reCREATE all your INDEXes
ANALYZE

(the ANALYZE will also do lots of IO, not as much as VACUUM, but it may speed 
up all WHERE / ORDER BY clauses).

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

[sqlite] Why so much I/O ? Can sqlite be improved ?

[Domingo Alvarez Duarte]

Hello !

I'm using sqlite (trunk) for a database (see bellow) and for a final 
database file of 22GB a "vacuum" was executed and doing so it  made a 
lot of I/O ( 134GB reads and 117GB writes in 2h:30min).


Can something be improved on sqlite to achieve a better performance ?

The data is public available just in case it can be useful to perform 
tests.


Cheers !


After 12 hours inserting of:

934,135,285 records on bolsas_familia

22,711,259 records in favorecidos

5,570 records in municipios

...

All that on mac-mini with i5 cpu 4GB memory:

1GB read 26MB write before vacuum

time sqlite3 bolsa_familia3.db "vacuum;"

real147m6.252s
user10m53.790s
sys3m43.663s

ls -l bolsa_familia3.db
-rw-r--r--  1   staff  22772744192 Oct  1 14:58 bolsa_familia3.db

MemRegions: 5656 total, 74M resident, 8904K private, 43M shared.
PhysMem: 650M used (471M wired), 3444M unused.
VM: 167G vsize, 1063M framework vsize, 18421751(0) swapins, 19671240(0) 
swapouts.

Disks: 414062/135G read, 369485/118G written.

time sqlite3_analyzer bolsa_familia3.db > bolsa_familia3.db.analyze.txt

real5m7.607s
user2m48.184s
sys0m56.512s

filefrag bolsa_familia3.db
bolsa_familia3.db: 29 extents found

===

year_month|records_inserted|start_time|end_time|minutes_spent

201101|12851338|2016-09-30 20:55:26|2016-09-30 20:59:50|4.4
201102|12946306|2016-09-30 20:59:51|2016-09-30 21:03:26|3.58
201103|12944677|2016-09-30 21:03:26|2016-09-30 21:06:55|3.48
201104|13058478|2016-09-30 21:06:55|2016-09-30 21:10:52|3.95
201105|12986870|2016-09-30 21:10:53|2016-09-30 21:14:49|3.93
201106|12999562|2016-09-30 21:14:49|2016-09-30 21:18:30|3.68
201107|12952040|2016-09-30 21:18:33|2016-09-30 21:22:26|3.88
201108|12805039|2016-09-30 21:22:29|2016-09-30 21:26:15|3.77
201109|13179472|2016-09-30 21:26:16|2016-09-30 21:30:15|3.98
201110|13171810|2016-09-30 21:30:15|2016-09-30 21:34:34|4.32
20|13306920|2016-09-30 21:34:48|2016-09-30 21:40:26|5.63
201112|13352307|2016-09-30 21:40:36|2016-09-30 21:45:06|4.5
201201|13330714|2016-09-30 21:45:11|2016-09-30 21:58:05|12.9
201202|13407291|2016-09-30 21:58:06|2016-09-30 22:03:35|5.48
201203|13394893|2016-09-30 22:03:47|2016-09-30 22:08:52|5.08
201204|13462104|2016-09-30 22:09:05|2016-09-30 22:14:53|5.8
201205|13530036|2016-09-30 22:15:05|2016-09-30 22:25:13|10.13
201206|13462659|2016-09-30 22:25:38|2016-09-30 22:29:24|3.77
201207|13524123|2016-09-30 22:29:32|2016-09-30 22:35:55|6.38
201208|13770339|2016-09-30 22:36:11|2016-09-30 22:42:23|6.2
201209|13724590|2016-09-30 22:42:38|2016-09-30 22:46:39|4.02
201210|13758254|2016-09-30 22:46:54|2016-09-30 22:51:21|4.45
201211|13834007|2016-09-30 22:51:32|2016-09-30 22:56:25|4.88
201212|13672501|2016-09-30 22:56:35|2016-09-30 23:00:56|4.35
201301|13874422|2016-09-30 23:01:11|2016-09-30 23:05:21|4.17
201302|13602566|2016-09-30 23:05:21|2016-09-30 23:09:54|4.55
201303|13942944|2016-09-30 23:09:58|2016-09-30 23:18:32|8.57
201304|13722930|2016-09-30 23:18:56|2016-09-30 23:28:06|9.17
201305|13837042|2016-09-30 23:28:27|2016-09-30 23:45:38|17.18
201306|13717464|2016-09-30 23:46:00|2016-09-30 23:53:20|7.33
201307|13887105|2016-09-30 23:53:25|2016-10-01 00:00:37|7.2
201308|13893436|2016-10-01 00:00:55|2016-10-01 00:07:39|6.73
201309|13978918|2016-10-01 00:08:03|2016-10-01 00:13:59|5.93
201310|13964596|2016-10-01 00:14:07|2016-10-01 00:17:58|3.85
201311|13966149|2016-10-01 00:18:18|2016-10-01 00:24:28|6.17
201312|14211619|2016-10-01 00:24:47|2016-10-01 00:33:52|9.08
201401|14164022|2016-10-01 00:34:12|2016-10-01 00:44:48|10.6
201402|14228956|2016-10-01 00:44:49|2016-10-01 00:52:26|7.62
201403|14160545|2016-10-01 00:52:32|2016-10-01 01:05:17|12.75
201404|14270028|2016-10-01 01:05:40|2016-10-01 01:14:33|8.88
201405|14042255|2016-10-01 01:14:58|2016-10-01 01:21:38|6.67
201406|14134906|2016-10-01 01:22:04|2016-10-01 01:29:19|7.25
201407|14389582|2016-10-01 01:29:44|2016-10-01 01:45:08|15.4
201408|14131123|2016-10-01 01:45:32|2016-10-01 01:51:07|5.58
201409|14143630|2016-10-01 01:51:09|2016-10-01 01:55:50|4.68
201410|14076919|2016-10-01 01:56:04|2016-10-01 02:00:57|4.88
201411|14109947|2016-10-01 02:00:59|2016-10-01 02:07:17|6.3
201412|14054243|2016-10-01 02:07:29|2016-10-01 02:12:55|5.43
201501|14026988|2016-10-01 02:13:05|2016-10-01 02:17:41|4.6
201502|14042558|2016-10-01 02:17:42|2016-10-01 02:22:26|4.73
201503|14004026|2016-10-01 02:22:40|2016-10-01 02:27:15|4.58
201504|13787678|2016-10-01 02:27:20|2016-10-01 02:35:33|8.22
201505|13779988|2016-10-01 02:35:51|2016-10-01 02:40:51|5.0
201506|13753665|2016-10-01 02:40:58|2016-10-01 02:46:40|5.7
201507|13861879|2016-10-01 02:46:53|2016-10-01 02:53:12|6.32
201508|13823829|2016-10-01 02:53:35|2016-10-01 02:58:28|4.88
201509|13912767|2016-10-01 02:58:55|2016-10-01 03:11:22|12.45
201510|14002752|2016-10-01 03:11:49|2016-10-01 03:27:47|15.97
201511|13815096|2016-10-01 03:28:04|2016-10-01 03:37:37|9.55
201512|13980491|2016-10-01 03:38:03|2016-10-01 03:47:26|9.38
201601|14020581|2016-10-01 03:47:41|2016-10-01