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

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 tr

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

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

Re: [sqlite] Compiling on AIX OS

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

Re: [sqlite] Compiling on AIX OS

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

Re: [sqlite] Compiling on AIX OS

cdir"     (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/too

[sqlite] Compiling on AIX OS

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.

[sqlite] www3.sqlite.org 500 server malfunction

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

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

ilure 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

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

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 s

Re: [sqlite] A hang in Sqlite

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

[sqlite] Patch that remove static/global variables from lemon

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

Re: [sqlite] sqlite syntax auto suggest

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

Re: [sqlite] sqlite syntax auto suggest

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

Re: [sqlite] sqlite syntax auto suggest

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:

Re: [sqlite] sqlite syntax auto suggest

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 gen

Re: [sqlite] sqlite syntax auto suggest

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

[sqlite] Does someone know any project like nsbase ?

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)

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

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

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

Re: [sqlite] Drop table error with existing views

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

[sqlite] Drop table error with existing views

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

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

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 user

[sqlite] Fossil problem ?

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

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

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

[sqlite] Again https://www3.sqlite.org is down

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

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

Re: [sqlite] Compression for ft5

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 !

[sqlite] Compression for ft5

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 implementatio

[sqlite] FTS5 min_word_size patch small error

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

[sqlite] FTS5 min_word_size patch

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

[sqlite] FTS5 minimum number of characters to index ?

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 in

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

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 updat

[sqlite] The upcoming alter table rename column

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

[sqlite] Typo in window function docs

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 CU

[sqlite] www3.sqlite.org ssl certificate expired

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

[sqlite] Again sqlite repository servers out of sync ?

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,

[sqlite] This list is getting spammed again

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

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

[sqlite] SQLite3 fossil repository not working ?

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 mor

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

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

[sqlite] Changes on sqlite3 parser and why not ?

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

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

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] WISH LIST: macros on internal commands in sqlite3 executable

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 b

[sqlite] HASHCODE 2018 in sqlite3

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 tha

[sqlite] Bug in unique index

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

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

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 duplicat

[sqlite] Fossil comunication and or test problems

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

[sqlite] Inconsistency between sha1 and sha3 extensions

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

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

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

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

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

Re: [sqlite] syntax error near AS

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 wro

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

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

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

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

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

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 w

[sqlite] Magic numbers on sqlite source code

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

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

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 TA

[sqlite] Is this a foreign key bug ?

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

Re: [sqlite] Optimization opportunity

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(

Re: [sqlite] Fossil error: Table plink has no column named baseid

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

Re: [sqlite] Fossil error: Table plink has no column named baseid

r repository might be too old. Try running "fossil rebuild" first and that will likely fix it. On 4/6/17, Domingo Alvarez Duarte 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

[sqlite] Fossil error: Table plink has no column named baseid

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

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

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

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

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

Re: [sqlite] Memoization in sqlite json1 functions

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

Re: [sqlite] Memoization in sqlite json1 functions

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 wrote: Hello Richard ! I noticed that sqlite do not use any memoization in json1 functions. For example jsonExtractFunc and others pa

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

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

[sqlite] Memoization in sqlite json1 functions

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, '$.a

[sqlite] FTS5 not working MY MISTAKE FORGET

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

[sqlite] FTS5 not working

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 wher

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

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 resul

Re: [sqlite] SQLite 3 locking

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

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

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 SQLi

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

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 s

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

9 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

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

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 yo

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

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

[sqlite] About ticket c92ecff2ec5f1784 LEFT JOIN problem

cting 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 wrote: Hello ! Today I found this unexpected behavior when using sqlite3 trunk: When using views with

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

acao, 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 wrote: Hello Richard ! The simple example I

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

Cheers ! == On 10/2/16, 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 sqli

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

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

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

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

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

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); creat

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

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

[sqlite] Error trying to make sqlite3 documentation

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

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

uot;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 wrote: I just found that the changes I made to sqlite3 to allow reference objects on attached databases does not wor

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

b.id; select * from v2; --10|10 On 04/10/16 17:30, Richard Hipp wrote: On 10/4/16, Domingo Alvarez Duarte 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_att

[sqlite] How to build the sqlite3 documentation locally ?

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

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

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

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

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 hav

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

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

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

, Richard Hipp wrote: On 10/3/16, Domingo Alvarez Duarte 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.

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

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 a

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

chines 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 (2G

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

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 no

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

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 experienc

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

te, 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 sqlit

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

uot;); //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...@mailingl

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

sted 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 wrote: About the vacuum

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

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 wr

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

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

  1   2   3   >