Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"
On 1/17/18, petern <peter.nichvolo...@gmail.com> wrote: > Take a look at the function shell_callback for hints. If the goal is to create a TSV reader/writer, it seems like the CVS reader/writer might be a better starting point, as it is unencumbered by lots of other unrelated features as is the shell. You might be able to get cvs.c to work simply by changing a single instance of a ',' literal into '\t'. Probably a little more work than that will be involved, but not too much more. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"
On 1/16/18, Shane Dev <devshan...@gmail.com> wrote: > I tried - > > sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename='test.tsv'); > > where test.tsv is a tab separated table. However > > select count(*) from t1; > > goes into an infinite loop. Do you how to specify a separator other than > ","? The "C" in CSV stands for "Comma". That is the only separator supported. But, you can probably edit the source code to do something different. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects
On 1/16/18, Dominique Devienne <ddevie...@gmail.com> wrote: > You're not compiling in C++11 mode, are you? > Probably use -std=c++11 or perhaps -std=gnu++11 on your g++ command line. Did that. It compiles now. But it also just works. There is no slowdown. Everything is very fast, regardless of which SQLite version I use. I added a line: std::cout << "SQLite version: " << sqlite3_libversion() << " " << sqlite3_sourceid() << std::endl; Just to verify that I was testing against the particular versions of SQLite that allegedly do not work. I think it is now back to Matthew to come up with a new test case that actually demonstrates his problem. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] About sql parametrized queries
On 1/16/18, Enrique Mesa <enriquemesa8...@gmail.com> wrote: > I am writing this message because i need a bit of help performing this kind > of query in sqlite engine. My wish to run the following SQL statement: > "SELECT * FROM users WHERE username = ? ;"; Suggestion you number your parameters for clarity: "SELECT * FROM users WHERE username = ?1;" Also, suggestion you spell out the columns you are requesting, rather than using the "*". "SELECT id, user_hash, user_salt FROM users WHERE username = ?1;" Generate the prepared statement using sqlite3_parepare_v2(). Then bind the value to ?1 using sqlite3_bind_text(pStmt, 1, zUserName, -1, SQLITE_TRANSIENT); > > I am not using wrappers. I am using just plain SQLite C Library from my > program wirtten in C++. I don't know how to face this. > > It is expected to get back just one record, because it is a uses login > system. It must return the userhash, and user salt. > > CREATE TABLE users( > id INTEGER PRIMARY KEY, > username TEXT, > user_hash INTEGER, > user_salt TEXT); > > Please help me. I am looking also for developers whom want to test my > software. Test means clients whom want to use it. > > I am a newbie on the programming world. > > Thanks > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects
On 1/16/18, Matthew Towler <tow...@ccdc.cam.ac.uk> wrote:
>
> Firstly, here is a C++11 example application.
Does not compile. These are the errors:
x2.cpp:53:2: warning: missing terminating " character
R"(SELECT AbundanceId FROM[ABUNDANCE]
^
x2.cpp:53:1: error: missing terminating " character
R"(SELECT AbundanceId FROM[ABUNDANCE]
^
x2.cpp:128:6: warning: missing terminating " character
)";
^
x2.cpp:128:5: error: missing terminating " character
)";
^
x2.cpp:246:38: warning: missing terminating " character
std::string main_table_insert = R"(INSERT INTO
ABUNDANCE(AbundanceId0, AbundanceId1, AbundanceId2, AbundanceId3,
AbundanceId4, AbundanceId5, AbundanceId6, AbundanceId7, AbundanceId8,
SetAbundanceId0, SetAbundanceId1, SetAbundanceId2)
^
x2.cpp:246:5: error: missing terminating " character
std::string main_table_insert = R"(INSERT INTO
ABUNDANCE(AbundanceId0, AbundanceId1, AbundanceId2, AbundanceId3,
AbundanceId4, AbundanceId5, AbundanceId6, AbundanceId7, AbundanceId8,
SetAbundanceId0, SetAbundanceId1, SetAbundanceId2)
^
x2.cpp:247:72: warning: missing terminating " character
VALUES(?,?,?,?,?,?,?,?,?,?,?,?) )";
^
x2.cpp:247:39: error: missing terminating " character
VALUES(?,?,?,?,?,?,?,?,?,?,?,?) )";
^
x2.cpp:7:1: error: ‘R’ was not declared in this scope
R"(CREATE TABLE ABUNDANCE(AbundanceId INTEGER PRIMARY KEY NOT NULL,
AbundanceId0 INTEGER, AbundanceId1 INTEGER, AbundanceId2 INTEGER,
AbundanceId3 INTEGER, AbundanceId4 INTEGER, AbundanceId5 INTEGER,
AbundanceId6 INTEGER, AbundanceId7 INTEGER, AbundanceId8 INTEGER,
SetAbundanceId0 INTEGER, SetAbundanceId1 INTEGER, SetAbundanceId2
INTEGER))",
^
x2.cpp:7:2: error: expected ‘}’ before string constant
R"(CREATE TABLE ABUNDANCE(AbundanceId INTEGER PRIMARY KEY NOT NULL,
AbundanceId0 INTEGER, AbundanceId1 INTEGER, AbundanceId2 INTEGER,
AbundanceId3 INTEGER, AbundanceId4 INTEGER, AbundanceId5 INTEGER,
AbundanceId6 INTEGER, AbundanceId7 INTEGER, AbundanceId8 INTEGER,
SetAbundanceId0 INTEGER, SetAbundanceId1 INTEGER, SetAbundanceId2
INTEGER))",
^
x2.cpp:7:2: error: in C++98 ‘query_texts’ must be initialized by
constructor, not by ‘{...}’
x2.cpp:7:2: error: could not convert ‘{}’ from
‘’ to ‘const
std::vector<std::__cxx11::basic_string >’
x2.cpp:7:2: error: expected ‘,’ or ‘;’ before string constant
x2.cpp:50:1: error: expected declaration before ‘}’ token
};
^
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I create a stealth index?
On 1/16/18, Deon Brewis <de...@outlook.com> wrote:
>
> I have seen a few cases where a newly added index would start showing up
> uninvited in old, previously tested queries and bring performance down by an
> order of magnitude. ('analyze' doesn't fix it).
We would welcome the opportunity to try to fix such problems. If you
can send us (even by private email) the output of ".fullschema" and
the query in question, identify the offending index, and tell us what
kind of timing discrepancy you are seeing, that would probably be
sufficient to address the problem.
>
> The new indexes would only be needed for new queries, so I don't mind
> forcing an 'indexed by' in for those, but I would like to avoid retrofitting
> every previous query we've ever written with an 'indexed by'.
The following commands will probably prevent 'newindex' from being
used by legacy queries:
DELETE FROM sqlite_stat1 WHERE idx='newidx';
INSERT INTO sqlite_stat1(tbl,idx,stats)
VALUES('thetable','newindex',100 100 100');
In the second line, there should be N+1 copies of '100' if there
are N columns in the index. And, of course, 'thetable' must be the
name of the table that 'newindex' is indexing.
You will need to either close and reopen the database connection, or
else run "ANALYZE sqlite_master;" after making the changes above in
order for the changes to go into effect.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can an SQL script be built from within sqlite?
On 1/16/18, Paul Sanderson <sandersonforens...@gmail.com> wrote: > That terminal app is still sandboxed. AFAIAA you essentially get access to > the applictaios data folder and you can add, create, delete, etc files > within it. Right. And so it is apparently not possible to compile a command-line application (like "sqlite3" or "kvtest"), download that application to the phone, then run it from the terminal app. If I am underestimating the capabilities of the terminal app, please correct me. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Infinite loop when updating indexed with case and where clause
On 1/16/18, Don V Nielsen <donvniel...@gmail.com> wrote: > Off topic question regarding fix: "0!=(wctrlFlags & WHERE_ONEPASS_MULTIROW)" > > Is there a performance bonus or compiler optimization if one compares a > target constant to a source condition versus comparing a target condition > to a source constant, as in "(wctrlFlags & WHERE_ONEPASS_MULTIROW)!=0"? Without actually checking, I'm guessing that both forms generating identical machine code. The difference is purely a stylistic thing. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Infinite loop when updating indexed with case and where clause
On 1/16/18, Harald Klimach <har...@klimachs.de> wrote: > > here is a weird behavior I observe with the following kind of setup: > Bug fix is in. You can download the latest snapshot from https://sqlite.org/download.html. Alternatively, you can apply the patch at https://www.sqlite.org/src/info/feb2c2b6f66b0f45 to whatever prior version of SQLite that you happen to be using. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Infinite loop when updating indexed with case and where clause
Thanks for the bug report!
A ticket for this issue is here:
https://www.sqlite.org/src/tktview/47b2581aa9bfececa7d95b2ef2aa433418c7a583
I will post another message as soon as we have it fixed.
On 1/16/18, Harald Klimach <har...@klimachs.de> wrote:
> Hi there,
>
> here is a weird behavior I observe with the following kind of setup:
>
> BEGIN TRANSACTION;
> CREATE TABLE simple("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "lft"
> INTEGER, "rgt" INTEGER);
> INSERT INTO simple VALUES(1,78,79);
> CREATE INDEX "index_on_lft" ON "simple" ("lft");
> CREATE INDEX "index_on_rgt" ON "simple" ("rgt");
> COMMIT;
>
> Now, if you try to run the following query, sqlite never returns:
>
> UPDATE "simple" SET lft = CASE WHEN lft >= 36 THEN lft + 2 ELSE lft END
> WHERE (lft >= 36 OR rgt >= 36);
>
> I encountered this when running Redmine (http://www.redmine.org).
> When creating projects it uses this rails code:
> http://www.redmine.org/projects/redmine/repository/entry/trunk/lib/redmine/nested_set/project_nested_set.rb#L48
> def add_to_nested_set(lock=true)
> lock_nested_set if lock
> self.lft = target_lft
> self.rgt = lft + 1
> self.class.where("lft >= ? OR rgt >= ?", lft, lft).update_all([
> "lft = CASE WHEN lft >= :lft THEN lft + 2 ELSE lft END, " +
> "rgt = CASE WHEN rgt >= :lft THEN rgt + 2 ELSE rgt END",
> {:lft => lft}
> ])
> end
>
> Which actually results in the following query (that gets stuck):
> SQL UPDATE "projects" SET lft = CASE WHEN lft >= 36 THEN lft + 2 ELSE lft
> END, rgt = CASE WHEN rgt >= 36 THEN rgt + 2 ELSE rgt END WHERE (lft >= 36 OR
> rgt >= 36);
>
> There is no problem when leaving out the where clause, which I did now as a
> workaround.
> The above is the minimal example I found to reproduce this behavior.
> I’ve tested it on OpenBSD 6.2 with SQLite version 3.20.1 2017-08-24
> 16:21:36,
> on MacOSX 10.10.5 with SQLite version 3.21.0 2017-10-24 18:55:49
> and on ArchLinux 4.14.12-1-ARCH with SQLite version 3.21.0 2017-10-24
> 18:55:49
> (all x86_64).
> The process consumes memory and cpu-cycles stuck in sqlite3VdbeExec.
>
> Best regards,
> Harald
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can an SQL script be built from within sqlite?
On 1/15/18, Shane Dev <devshan...@gmail.com> wrote: > > Did the Apple engineers tell you why it is not possible to compile and run > the SQLite shell on iOS? > You cannot get a command-line prompt on iOS, and the SQLite shell requires a command-line prompt (like bash). If i understand correctly, iOS does not allow fork() as a security measure. The previous paragraph is not authoritative. It is merely my recollection. I have not researched the issue. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can an SQL script be built from within sqlite?
On 1/14/18, Simon Slavin <slav...@bigfraud.org> wrote: > > I’ve never seen anyone compile sqlite3 (the shell tool) for iOS. Android I > have even less idea about. Can you run command-line tools on Android ? Has > anyone compiled sqlite3 for it ? > You can easily compile and run all of the SQLite command-line tools for Android. There are simple instructions at the bottom of the https://www.sqlite.org/fasterthanfs.html document. The engineers at Apple tell me this is not possible on iOS. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Output/CSV
On 1/12/18, John Gutierrez <jtgut...@earthlink.net> wrote: > Sqlite Shell: > > On Linux, using .output and .mode csv, sqlite produces a [dos] 1L, 161C > file. Is this a bug or is it a compile issue for my distribution to > address? I don't know what a "[dos] 1L, 161C" file is? Can you explain? Perhaps you are concerned that the CSV output uses \r\n line terminators instead of the more succinct \n favored by unix. That is intentional, and it is because that is what RFC4180 (https://www.ietf.org/rfc/rfc4180.txt) says should happen. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pcache1FetchStage2 CRASH
On 1/12/18, zheng xiaojin <zhengxiaojin_...@outlook.com> wrote: > I am using the 3.15.2, and currently a problem comes to me that, > pcache1FetchStage2 CRASH when access the pgroup->lru.pLRUPrev->isAnchor, the > crash info is sigsegv access 0x16 on IOS. > And a little frequently about0.1percent. Do you still have the problem with the latest version of SQLite? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] os_unix.c:36136: (2)
On 1/11/18, wei1.z <wezhao1...@hotmail.com> wrote: > What is the meaning of this line? > > 01-11 14:40:59.733 10011 2864 2877 E SQLiteLog: (14) os_unix.c:36136: (2) > open() - > > db file cannot be found, or permission issue ? It means that SQLite could not open a file either because that file does not exist or because you do not have appropriate permissions to open that file. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.22.0 coming soon
On 1/12/18, petern <peter.nichvolo...@gmail.com> wrote: > Is adding arg_count and > is_aggregate columns to PRAGMA function_list() on the roadmap? How would that work with functions like coalesce() and max() that take an arbitrary number of arguments, or like max() that is an aggregate with one argument and a scalar with two or more arguments? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is foreign key support disabled within triggers with raise functions?
On 1/12/18, Shane Dev <devshan...@gmail.com> wrote: > Does that mean FK constraints are only checked > if processing reaches the end of the trigger? FKs are checked at the end of the entire statement. If multiple triggers fire, then they all run to completion before any FKs are checked. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is foreign key support disabled within triggers with raise functions?
Test 1 makes the changes, the checks the FK constraints, sees that the FK constraints are violated and hence runs ABORT, which backs out the change. Test 2 makes the change, then runs FAIL, which stops all further processing. The FK constraints are never checked, and the changes are not backed out. On 1/12/18, Shane Dev <devshan...@gmail.com> wrote: > Hello, > > Perhaps it would be clearer if I ask the question in a different way. Why > does the following statement - > > insert into vtrig select 5; > > fail to insert a record in Test 1 below (as expected) but succeeds in Test > 2 (despite the foreign key constraint)? > > sqlite> CREATE TABLE reftab(id integer primary key); > sqlite> CREATE TABLE deptab(id integer primary key, ref int references > reftab); > sqlite> pragma foreign_keys; > foreign_keys > 1 > sqlite> CREATE VIEW vtrig as select 1; > > Test 1 > = > > sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin >...> insert into deptab(ref) select 2; >...> end; > sqlite> insert into vtrig select 5; > Error: FOREIGN KEY constraint failed > sqlite> select * from deptab; > sqlite> > > > Test 2 > = > > sqlite> drop trigger ttrig; > sqlite> CREATE TRIGGER ttrig instead of insert on vtrig begin >...> insert into deptab(ref) select 2; >...> select raise(FAIL, 'this statement seems to temporarily disable > foreign >...> support'); >...> end; > sqlite> insert into vtrig select 5; > Error: this statement seems to temporarily disable foreign > support > sqlite> select * from deptab; > id ref > 1 2 > > > On 12 January 2018 at 00:48, Richard Hipp <d...@sqlite.org> wrote: > >> On 1/11/18, Shane Dev <devshan...@gmail.com> wrote: >> > >> > CREATE VIEW vtrig as select 1; >> > CREATE TRIGGER ttrig instead of insert on vtrig begin >> > delete from deptab; >> > delete from reftab; >> > insert into deptab(ref) select 2; >> > select raise(FAIL, 'this statement seems to temporarily disable >> > foreign >> > support') where (select count(*) > 0 from deptab); >> > end; >> > >> > sqlite> insert into vtrig select 5; >> > Error: this statement seems to temporarily disable foreign support >> > sqlite> select * from reftab; >> > sqlite> select * from deptab; >> > id ref >> > 1 2 >> > sqlite> pragma foreign_keys; >> > foreign_keys >> > 1 >> > >> > Can we conclude foreign key support is disabled within triggers with >> raise >> > functions? >> >> I don't what you mean by "disabled". When you hit a raise(FAIL) >> SQLite stops whatever it was doing and leave the database in whatever >> half-way completed state it was in at the moment. That's what >> raise(FAIL) is suppose to do. >> >> Did you mean to do "raise(ABORT)" instead, which should do what I >> think you are trying to accomplish. >> >> -- >> D. Richard Hipp >> d...@sqlite.org >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is foreign key support disabled within triggers with raise functions?
On 1/11/18, Shane Dev <devshan...@gmail.com> wrote: > > CREATE VIEW vtrig as select 1; > CREATE TRIGGER ttrig instead of insert on vtrig begin > delete from deptab; > delete from reftab; > insert into deptab(ref) select 2; > select raise(FAIL, 'this statement seems to temporarily disable foreign > support') where (select count(*) > 0 from deptab); > end; > > sqlite> insert into vtrig select 5; > Error: this statement seems to temporarily disable foreign support > sqlite> select * from reftab; > sqlite> select * from deptab; > id ref > 1 2 > sqlite> pragma foreign_keys; > foreign_keys > 1 > > Can we conclude foreign key support is disabled within triggers with raise > functions? I don't what you mean by "disabled". When you hit a raise(FAIL) SQLite stops whatever it was doing and leave the database in whatever half-way completed state it was in at the moment. That's what raise(FAIL) is suppose to do. Did you mean to do "raise(ABORT)" instead, which should do what I think you are trying to accomplish. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects
On 1/11/18, Matthew Towler <tow...@ccdc.cam.ac.uk> wrote: > for the > volumes of data I have sqlite hangs Is it hanging, or is it crashing? Your statement of the problem is unclear on this point. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_column_decltype and max and min
On 1/11/18, John G <rjkgilles...@gmail.com> wrote: > > Is this because I am stuck with version 3.8.8.3 which is what MacOS Sierra > provides? > You are not stuck with the software provided by Sierra. You can download and/or compile your own up-to-date SQLite that is twice as fast and has all the latest features. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can error messages be concatenated in the raise function?
On 1/10/18, Shane Dev <devshan...@gmail.com> wrote: > Hello, > > From the documentation https://www.sqlite.org/syntax/raise-function.html, > it is not clear to me if the 'error-message' must be a fixed string. The current implementation requires that the 2nd argument to RAISE() be a fixed string. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 3.22.0 coming soon
All of the minor issues mentioned by prior emails in this thread should now be fixed. Thanks to everybody for proof-reading and testing! Fresh source code is now available on trunk (https://sqlite.org/src/info/trunk) and from the "Prerelease Snapshot" link on the https://sqlite.org/download.html page if you would like to continue testing. We are not yet at "pencils down". More enhancements may yet go into this release before we start our final release testing. Nevertheless, the community testing you have conducted so far has found several real issues, and is greatly appreciated. Please continue the good work! -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.22.0 coming soon
On 1/9/18, E.Pasma <pasm...@concepts.nl> wrote: > Dear SQLite developers and eventual PowerPC users, > using a likely outdated compiler: > powerpc-apple-darwin9-gcc-4.0.1 > a compile error occors: > shell.c:10062: error: conflicting types for ‘integerValue’ > shell.c:9169: error: previous implicit declaration of ‘integerValue’ This seems like a compiler bug. But it is easy enough to work around, and that is what I have done. Please verify that the latest trunk code (or the latest "prerelease-snapshot") is now working for you on your old PowerPC system. Thanks! This is the kind of thing is very important. We SQLite developers can test as much as we want to, but we will never find strange interactions with old compilers that we don't own. This is exactly the kind of thing we need your help with. Thanks for all your testing! -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.22.0 coming soon
On 1/9/18, Gwendal Roué <gwendal.r...@gmail.com> wrote:
> 1. For statements made of parameters, such as `SELECT ?, ?` or `SELECT :a,
> :b`, the sqlite3_column_name used to return the parameter name ("?" or ":a"
> etc.) for all parameters, the last one included. The latest version returns
> an empty string for the last parameter.
I have not been able to reproduce this problem using the command-line
shell. I tried the following script:
.header on
SELECT ?, ?;
SELECT :a, :b;
It shows me the correct column names for all columns. Do you have a
reproducible test case that I can work from?
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite 3.22.0 coming soon
Version 3.22.0 will probably be released soon. Your help in beta-testing is appreciated. Please download the latest "trunk" sources (from https://sqlite.org/src/info/trunk) or a pre-release snapshot (the top-most link at https://sqlite.org/download.html) and verify that you are able to build and use the latest code in your applications. Report any issues to this mailing list, or directly to me. The latest change summary can be seen at https://www.sqlite.org/draft/releaselog/3_22_0.html and the draft 3.22.0 webpage is at https://www.sqlite.org/draft Detailed log of all check-ins since the last release: https://www.sqlite.org/src/timeline?from=release=trunk -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tip of Trunk does not compile under 64-bit MingW compiler
Please try again with the latest trunk of SQLite (https://www.sqlite.org/src/timeline?c=2018-01-07+21:58:17 or later) and let me know whether or not your a still having problems. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tip of Trunk does not compile under 64-bit MingW compiler
On 1/6/18, Keith Medcalf <kmedc...@dessus.com> wrote: > > fileio.c attempts to #include a file "test_windirent" which does not exist. > (Perhaps it does somewhere, but it is not part of amalgamation files nor the > generated shell.c). What (exact) sources are you using, and what steps are you taking to build? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The performance of indexed select
On 1/6/18, Dinu <dinumar...@gmail.com> wrote: > > I think b-trees can store the counts of descendant nodes for every node to > solve this issue in O(log n), but I don't see anything like it in the SQLite > format. They can do that, but it also means that all the parent b-tree pages must be updated whenever an entry is added or removed from a leaf page, which increases the amount of I/O needed for operations like INSERT, DELETE, or UPDATE. It seemed to me that INSERT, DELETE, and UPDATE are rather more common than SELECT count(*), so I decided not to keep a child count in the b-tree pages when I first designed the current b-tree format for SQLite. back in 2003. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Preventing a slow, determinstic function from running twice
On 1/5/18, r...@hoelz.ro <r...@hoelz.ro> wrote: > Hi SQLite users and devs, > > I have an application using SQLite which stores XZ-compressed blobs of JSON > from the Twitter API to minimize disk usage. My unxz function is a little > slow, and I've noticed that if I specify the function several times in a > query, it gets run multiple times, even though it's deterministic. For > example: > Your example did not go through. Please resend. Use plain-text email for best results. > > > In the above query, unxz is run three times, even though content doesn't > change within the same row. Is there a way to tell SQLite to only run a > function once per row of results? I looked into > https://sqlite.org/c3ref/get_auxdata.html, but it appears that metadata is > only stored for compile-time constants. > > Thanks, > Rob > > > > > -- > Sent from: http://sqlite.1065341.n5.nabble.com/ > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LEFT JOIN + WHERE / OR optimisation
On 1/4/18, Dinu <dinumar...@gmail.com> wrote: > I think it's every DB's intention to optimize as best possible > a query into an execution plan. None does it perfectly, but all try to, very > hard. There are trade-offs here. How much slower are you willing for sqlite3_prepare() to run in order to get a better query plan? How much extra memory and disk space are you willing to allocation to libsqlite3.so in order to get a better query plan? Are you willing to impose these costs on (literally) billions of other users that don't really need the more advanced query planning? These are hard questions with no easy answers. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer and recursive common table expressions
On 1/3/18, Shane Dev <devshan...@gmail.com> wrote: > > sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt) > select * from cnt where x <= 3; > [no sqlite> prompt, CPU utilization 25%] > > I assume sqlite is recursively adding rows to the queue without considering > that the subsequent SELECT only needs the first 3 of them. No, it is continuing to search for rows for which x<=3. The query planner does not know enough algebra to figure out that that will never happen again after the first three rows. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loadable extension with shared state
On 1/3/18, Lifepillar <lifepil...@lifepillar.me> wrote: > Consider an extension that has some shared state, say a global `context` > struct, whose value is used by a few user-defined SQL functions. > Besides, assume that there are other SQL functions that can act on the > global context. > > The question is: how do I turn this into a thread-safe extension? > > Should I use SQLite3 mutex functions to guarantee exclusive access to > shared state? Or should I define my own locks? Either approach will work. Which is easiest for you? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] pragma table_info return column delimiters?
On 1/3/18, Bart Smissaert <bart.smissa...@gmail.com> wrote: > Is there a way with pragma table_info or otherwise (other than parsing the > table create statement from SQLite_master) to get the column names > including the column delimiters, eg double quotes or square brackets? So I > would get eg: [column1] [column2] etc. if indeed the column names were > delimited like that. No. SQLite does not retain that information in its internal symbol tables. You'll have to parse out the original CREATE TABLE statements to figure that out. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Capturing groups for regexp.c Check-in [3d6fba62] ?
On 1/1/18, petern <peter.nichvolo...@gmail.com> wrote: > Richard. Please consider adding capturing groups during your upgrade of > the regexp.c matching capability. I did consider that. It seems hard to do in linear time. I also notice that neither JavaScript nor AWK support that capability. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] fsync on -wal still happening
On 12/28/17, Ian Freeman <i...@ifreeman.net> wrote: > I have configured journal_mode=WAL, synchronous=NORMAL, and disabled > autocheckpointing, and yet the -wal file is modified after every update > operation... That is correct. "sync" means forcing the data from the internal operating-system buffers out to persistent storage on your disk drive, where it will survive and OS reset or power loss. The WAL is written on every transaction, in the sense that the content is moved into the operating system. But it is only synced to persistent storage on a checkpoint. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3AtoF()
On 12/25/17, Cezary H. Noweta <c...@poczta.onet.pl> wrote: > > Could you consider an exponentiation by squaring (in the main release) > instead of current n-multiplication of exponents? Please test the latest trunk version (or any version after check-in https://www.sqlite.org/src/timeline?c=fd2e0e7a) and confirm that the modifications work for you. Thanks for the suggested algorithm improvement. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Move to Github!!?
On 12/25/17, Shekhar Reddy <shekharreddy.k...@gmail.com> wrote: > > Is there any particular reason that the source is not moved to GitHub? I > think that would reach more number of people there. > There is a mirror of the SQLite repository on GitHub at https://github.com/mackyle/sqlite (maintained by GitHub user "mackyle" whom I do not know, but whose efforts I do appreciate). SQLite uses a different version control system called Fossil. See https://www.fossil-scm.org/ for more information about Fossil. Fossil is superior to Git+GitHub in many respects. You can easily see this by doing a side-by-side comparison of the SQLite Fossil repository against the GitHub mirror. For example, here is the GitHub view of the "dbpage" branch of SQLite: https://github.com/mackyle/sqlite/commits/dbpage Compare the above against the equivalent Fossil view: https://www.sqlite.org/src/timeline?p=dfdebd12bfc80b91 The Fossil view clearly shows that the head of "dbpage" is the merger of two other branches, and Fossil shows clearly where the branch diverged from trunk. That information is very difficult to discern from the GitHub view. Fossil also has the ability to show the complete context of an individual branch. For the "dbpage" branch, the context is shown here: https://www.sqlite.org/src/timeline?r=dbpage Note in particular that Fossil clearly shows that the "dbpage" branch was ultimately merged back into trunk. GitHub does not provide that information, as far as I can tell. The basic problem with Git (apart from its notoriously convoluted user interface) is that it is based on a (bespoke) key/value database - the "packfile". Fossil, on the other hand, is based on the most widely used relational database in the world. This make information much easier to extract from Fossil than from Git. For example, given a commit in Git (perhaps one reported by a customer or one found via bisect) there is no easy way in Git to find out what comes next - what commits were entered using your commit as a baseline. Git shows ancestors, but not descendants. Fossil, on the other hand, easily shows both descendants and ancestors of a check-in. You see this in the "Context" section of any Fossil commit page, such as https://www.sqlite.org/src/info/dfdebd12bfc80b91 The fact that Git/GitHub does not show the descendants of a commit is a show-stopper for me. Finally, the use of GitHub would create a reliance on an outside company over which we have no influence. The people who run GitHub today seem like great folks. But the company might be sold or fall under new management tomorrow, and the friendly and open policies that govern GitHub today might change in an instant. Fossil, on the other hand, is very simple to self-host on a $5/month VPS. (SQLite uses https://www.linode.com/ for its main servers and https://www.digitalocean.com for the https://www3.sqlite.org/ backup. There are lots of others.) So, given that Fossil is freer than Git (BSD vs. GPL), that Fossil embodies all of the functionality of both Git and GitHub, that Fossil is more capable than Git/GitHub, that Fossil has a friendly user interface than Git, and that Fossil is very easy to self-host and thus frees you of any dependencies on third-party companies, the question becomes: Why aren't you moving all of your GitHub projects over to Fossil! -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .load fails
On 12/23/17, Richard Hipp <d...@sqlite.org> wrote: > On 12/23/17, Joop! <bur...@xs4all.nl> wrote: >> sqlite> *.load /usr/lib/sqlite3/pcre.so* >> *Error: /usr/lib/sqlite3/pcre.so.so: cannot open shared object file: No >> such file or directory* >> > > What error message do you get if you omit the ".so" at the end of the > shared library name? Or, apply this patch (https://www.sqlite.org/src/info/05fee1a21ea398f1) to get a better error message regardless of whether or not you include the ".so" file suffix. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .load fails
On 12/23/17, Joop! <bur...@xs4all.nl> wrote: > sqlite> *.load /usr/lib/sqlite3/pcre.so* > *Error: /usr/lib/sqlite3/pcre.so.so: cannot open shared object file: No > such file or directory* > What error message do you get if you omit the ".so" at the end of the shared library name? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 related crash (SIGSEGV) in GNOME tracker-store
On 12/22/17, Paul Wise <pa...@bonedaddy.net> wrote: > Hi all, > > I got a couple of random crashes GNOME's tracker-store daemon that > appear to be related to sqlite3. I use 3.21.0-1 from Debian buster. > > Is this a bug in sqlite3 or is it caused by data corruption? > There are no known issues like this with any recent version of SQLite. Usually these kinds of things end up being heap corruption in the application. Nevertheless, we will investigate from the SQLite side, just in case. "SQLite 3.21.0-1" is not an official SQLite product. It must be a version of SQLite that has been customized by Debian. Where can we get a copy of Debian's customized code? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
Your trouble ticket is here: https://sqlite.org/src/tktview/3b4450072511 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
On 12/22/17, Radovan Antloga <radovan.antl...@siol.net> wrote: > My point is you do not have to change anything regards > to how select works. SELECT statement is working just > great or OK or fine. > > CREATE TABLE AS > SELECT > > does not give same name as SELECT does. > > SELECT give name d > > CREATE TABLE AS SELECT > create table with name a instead of d. Can you provide a simple test case for this behavior? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
On 12/22/17, Radovan Antloga <radovan.antl...@siol.net> wrote: > In my example I have AS clause so rule 1. You did not understand my question. I think what you are asking for is that we should enhance rule 1 so that it applies even if the AS clause is contained within a subquery. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
These are the rules that SQLite uses to name a column in the result set: (1) If there is an AS clause, use it. (2) If the result-set value comes from a table column (even indirectly, such as through a subquery, but not if the value is altered by an expression) then use the name of the column as it appears in the original CREATE TABLE statement for the table. (3) Otherwise, name the result-set column using the SQL text in the SELECT statement. What set of rules are you proposing that we should use instead? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Btree page corruption
On 12/21/17, Nikhil Deshpande <ndeshpa...@vmware.com> wrote: > > There were no power-off or reboots in near time vicinity when the > corruption was detected. (1) Might the corruption have been sitting dormant due to some far away power-off or reboot and was only recently discovered? How much do you trust the fsync() system call and the filesystems ability to honor that system call on the VM on which this code is running? Do you run "PRAGMA quick_check" on initial power-up? (2) Is this the only corruption instance you have seen? (3) The showdb outputs you sent only shows me the first 8 bytes of the page. From those 8 bytes, it looks to be an overflow page that is being used as a btree page. Are there any further clues in the hex dump of the bad pages? (4) What does "showdb pgidx" look like? (5) How many threads are accessing the database? Are you sure that you have the database set in a thread-safe mode? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile fails with '-Wall -Werror'
On 12/21/17, Richard Hipp <d...@sqlite.org> wrote: > > If you are unwilling to work-around this problem > by omitting -Werror, then you might try upgrading to GCC 5.4. > You can also apply this patch: https://www.sqlite.org/src/info/64487d658cb3b6c8 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compile fails with '-Wall -Werror'
On 12/21/17, Brian Kambach <bkamb...@cryptonitenxt.com> wrote: > We recently upgraded sqlite from 3.19.3 to 3.21.0 and now building with > '-Wall -Werror' fails (when it previously succeeded): > > $ gcc -O3 -Wall -Werror -fno-delete-null-pointer-checks -c -o sqlite3.o > sqlite3.c > sqlite3.c: In function 'exprAnalyze': > sqlite3.c:131526:37: error: 'pLeft' may be used uninitialized in this > function [-Werror=maybe-uninitialized] > pNewTerm->leftCursor = pLeft->iTable; > ^ > sqlite3.c:131529:28: error: 'eOp2' may be used uninitialized in this > function [-Werror=maybe-uninitialized] > pNewTerm->eMatchOp = eOp2; > ^ > cc1: all warnings being treated as errors > > I also noticed that sqlite3.c compiles successfully with -O0, but fails with > -O1, -O2, or -O3. > > We compile everything with -Werror, so I'm hoping the answer is not "don't > use -Werror". Both of these warnings are false-positives that arise due to limitations in the is-initialized theorem prover logic of GCC 4.8. So, in other words, it is not possible for either pLeft or eOp2 to be used uninitialized - it is just that GCC 4.8 is unable to construct a proof of that fact. If you are unwilling to work-around this problem by omitting -Werror, then you might try upgrading to GCC 5.4. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Minor bug reports during build.
On 12/21/17, Michael Tiernan <michael.tier...@gmail.com> wrote: > Is there a route for reporting an error for an average user without > creating an account and all the overhead of the ticket system? Send an email to this mailing list, or directly to me. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling / building SQLite to include regex
On 12/21/17, Paul Hoffman <paul.hoff...@gmail.com> wrote: > Greetings. I understand that SQLite doesn't come natively with regex > support, but that it can be added. My question is how to do so when I > install. I'm building from source from > <http://www.sqlite.org/download.html>. > Is there a simple recipe for "make REGEX work after installation"? There is a loadable REGEXP extension here: https://www.sqlite.org/src/artifact/a68d25c659bd2d89 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
The behavior change is a bug fix. See
http://sqlite.org/src/info/de3403bf5ae for details.
On 12/21/17, Radovan Antloga <radovan.antl...@siol.net> wrote:
> I have table (create statement):
>
> CREATE TABLE SOPP1 (
>STAT varchar(1) collate systemnocase,
>RID varchar(2) collate systemnocase,
>VP integer,
>BLANK varchar(6) collate systemnocase,
>NAZIV varchar(24) collate systemnocase,
>KN varchar(12) collate systemnocase,
>A varchar(1) collate systemnocase,
>B varchar(1) collate systemnocase,
>RACUN varchar(1) collate systemnocase,
>URE varchar(1) collate systemnocase,
>ZN varchar(1) collate systemnocase,
>TOCKE varchar(1) collate systemnocase,
>PRC varchar(1) collate systemnocase,
>UP varchar(1) collate systemnocase,
>IZPIS varchar(1) collate systemnocase,
>D varchar(1) collate systemnocase,
>F2U varchar(1) collate systemnocase,
>F2O varchar(1) collate systemnocase,
>F2T varchar(1) collate systemnocase,
>F2Z varchar(1) collate systemnocase,
>F2P_1 integer,
>F2P_2 integer,
>F2P_3 integer,
>F5 varchar(1) collate systemnocase,
>AJPES varchar(1) collate systemnocase,
>ZZ integer,
>VD integer,
>NS integer,
>MES integer,
>NORURE varchar(1) collate systemnocase,
>G varchar(1) collate systemnocase,
>E varchar(1) collate systemnocase,
>H varchar(1) collate systemnocase,
>I varchar(1) collate systemnocase,
>J varchar(1) collate systemnocase,
>SM varchar(1) collate systemnocase,
>NO varchar(1) collate systemnocase,
>PRIO varchar(1) collate systemnocase,
>V_1 varchar(1) collate systemnocase,
>V_2 varchar(1) collate systemnocase,
>V_3 varchar(1) collate systemnocase,
>V_4 varchar(1) collate systemnocase,
>V_5 varchar(1) collate systemnocase,
>V_6 varchar(1) collate systemnocase,
>V_7 varchar(1) collate systemnocase,
>V_8 varchar(1) collate systemnocase,
>V_9 varchar(1) collate systemnocase,
>V_10 varchar(1) collate systemnocase,
>V_11 varchar(1) collate systemnocase,
>V_12 varchar(1) collate systemnocase,
>FOR integer,
>P_1 integer,
>P_2 integer,
>P_3 integer,
>P_4 integer,
>P_5 integer,
>P_6 integer,
>primary key (RID, VP, BLANK));
>
> When I create new table using this sql:
>
> drop table if exists WM4P;
> create table WM4P as
> select
>P, A, B, AB, U, H, ZZ,
>case
> when AB in ('7') then 99
> when AB in ('57', '58', '59', '5M') then null
> when AB = '56' and ZZ = 12 then 01
> when AB = '56' then 02
> when A = '3' then 03
> when AB in ('1M') then 08
> when AB in ('10') then 07
> when AB in ('12') then null
> when A in ('1', '5') and H = '1' then 02
> when A in ('5') then 02
> when A in ('1') then 01
>end as M4_OP
> from (
>select
> VP as P, ifnull(A,'') as A, ifnull(B,'') as B,
> ifnull(A,'')||ifnull(B,'') as AB,
> ifnull(URE,'') as U, ifnull(H,'') as H, ZZ
>from SOPP1
>);
>
> You will see that first column name is VP instead of P.
> In previous versions name was P not VP.
>
> Best Regards
> Radovan Antloga
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create index implies analyze?
On 12/20/17, R Smith <ryansmit...@gmail.com> wrote: > I've never known Analyze to consume significant time ANALYZE runs in O(N) steps, where N is the number of rows in the table. It does a single scan through each index being analyzed, from beginning to end. CREATE INDEX, on the other hand, requires O(NlogN) steps. ANALYZE is normally fast, but can take significant time on a large (terabyte-sized) database. Earlier this year, we worked on an experimental utility program that does a reasonable approximation of ANALYZE in constant time. See the "faststat1" utility on the "est_count_pragma" branch: https://www.sqlite.org/src/timeline?r=est_count_pragma -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Macro expansion of B0 on Linux PPC system
On 5/3/17, Lee, Greg <lee...@llnl.gov> wrote: > When I try to build sqlite 3.18.0 on a Linux PPC system, I get a > compile-time error. Can you please try again using the latest trunk version of SQLite and let me know if you are still encountering problems. You can download a tarball of the latest trunk checkin from https://sqlite.org/src/tarball/sqlite.tar.gz?r=trunk We are dependent on you to run this test since we do not have access to a Linux PPC system. --- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Macro expansion of B0 on Linux PPC system
On 12/18/17, Lee, Greg <lee...@llnl.gov> wrote: > I am still seeing the problem on Power 8 and others report the problem > persists on Power 9. Please see the spack github issue. I also attached a > configure/make output if that helps. So, what you are saying, then, is that "B0" is a reserved word on Power 8 and Power 9 systems, and can never be used as a local variable name? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Atomic DELETE index optimisation?
On 12/18/17, Dinu <dinumar...@gmail.com> wrote: > Rowan Worth-2 wrote >> The entire point of SQL transactions is to implement this magic! > > I beg to differ; you proposed: >> (1) BEGIN TRANSACTION >> > (2) DROP all indexes from the table > This is by no means valid SQL semantics; in all RDBBMS I've worked with, > structure changes' relation to a transaction is undefined at best. DROP, CREATE, and ALTER are transactional in SQLite, just like DELETE, INSERT, and UPDATE. This has always been the case, and always shall be. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Atomic DELETE index optimisation?
On 12/17/17, Dinu <dinumar...@gmail.com> wrote: > > We have ~32M records out of which we are deleting about ~24M. Can you try this: (1) BEGIN TRANSACTION (2) DROP all indexes from the table (3) Do the DELETE operation (4) CREATE all indexes again (5) COMMIT -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possibly uninitialized variables...compile time error
On 12/15/17, Edwards, Mark C. <mark.c.edwa...@leidos.com> wrote: > sqlite3.c(167291): error C4703: potentially uninitialized local pointer > variable 'pNode' used > sqlite3.c(168154): error C4703: potentially uninitialized local pointer > variable 'pRoot' used > sqlite3.c(168160): error C4703: potentially uninitialized local pointer > variable 'pChild' used All these warnings are false positives. The variables are initialized by prior calls to the nodeAcquire() function. The problem is that nodeAcquire() does not necessarily initialize the variables, but if it does not, it returns a result code other than SQLITE_OK, and in those cases, the code that uses the variables that nodeAcquire() was suppose to have initialized is never reached. Thus there is no possibility of using an uninitialized variable. However, the code in question is not on a critical path. Hence, I have now added extra (unnecessary) local variable initializations in the relevant RTree indexing routines so that these warnings should now be suppressed. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Macro expansion of B0 on Linux PPC system
On 12/15/17, Lee, Greg <lee...@llnl.gov> wrote: > I never got a reply on this issue and someone else tripped up on it: > > https://github.com/spack/spack/issues/6698 > > Any help or even acknowledgement of this message would be appreciated. > I believe the problem was fixed on the highlighted check-in here: https://www.sqlite.org/src/timeline?y=ci=b9a58daca=9 That fix should have appeared in SQLite version 3.19.0. Version 3.21.0 is the latest. Are you still having issues? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What happens if an in memory database runs out of memory
On 12/15/17, x <tam118...@hotmail.com> wrote: > > Is there any easy way of creating a table that will use mem for speed but > revert to disc for backup if memory runs out? > Make the database filename be an empty string. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What happens if an in memory database runs out of memory
On 12/15/17, x <tam118...@hotmail.com> wrote: > Suppose I execute “attach :memory: as mem” and then create a table in mem > that requires more space than the available RAM can hold what will happen? You will get an SQLITE_NOMEM error from SQLite. This is well-tested behavior. You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5 and then starting the "sqlite3.exe" command-line shell with the "--heap" argument to tell it how much memory to use. Give it a few megabytes. Then start up your in-memory database and fill it up to see what happens. sqlite3 --heap 5MB The --heap option causes the shell to do a single 5MB memory allocation and then divy up that one allocation for all its memory needs. When the 5MB is gone, SQLite is out of memory and will start reporting SQLITE_NOMEM errors. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug with expression evaluation?
On 12/14/17, Tony Papadimitriou <to...@acm.org> wrote: > > MySQL example: > mysql> select 1/2; > ++ > | 1/2| > ++ > | 0.5000 | > ++ > 1 row in set (0.13 sec) MySQL is the only database engine that behaves this way. All others do integer arithmetic on integer values. This is probably the reason that MySQL has the separate "DIV" operator for integer division, whereas everybody else makes due with the standard "/" operator. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite have official development testing tool?
On 12/14/17, advancenOO <haveagoodtime2...@gmail.com> wrote: > Hello Richard, > > I hope to run some tests by myself and I think TCL tests in your link are > what I want. > There are so many .tcl and .test in Sqlite source tree. > Could someone share what commands I need to run to start all TCL tests? > make test -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] the timing to truncate WAL file to journal_size_limit
On 12/14/17, advancenOO <haveagoodtime2...@gmail.com> wrote: > I noticed that, > “The journal_size_limit pragma may be used to limit the size of WAL files > left in the file-system after transactions or checkpoints. Each time a WAL > file resets, SQLite compares the size of the WAL file left in the > file-system to the size limit.” > > But I think only when the first transaction commits AFTER a checkpoint will > WAL file truncate to the limit. As the src code is, > if( isCommit && pWal->truncateOnCommit && pWal->mxWalSize>=0 ). > > Which means WAL file may still consume a large amount of space after > checkpoints, unless a new transaction commits. Is that right? Correct. The reset happens on the next transaction commit. There is also "PRAGMA wal_checkpoint(TRUNCATE);" -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to index data based on custom comparisons?
On 12/14/17, Lifepillar <lifepil...@lifepillar.me> wrote: > I am not familiar with virtual tables yet, but I see that they are used, > for example, to implement Rtree indexes. Would it be feasible to > implement my own index structure as a virtual table and use it to index > a blob column in a standard table (or even just in the virtual table > itself)? That would be complicated. A different idea. Suppose you have two new UDFs: ieee754dec(X): Converts IEEE754-binary number X into IEEE754-decimal. In other words it takes a "double" input and returns a "blob" output. ieee754bin(Y): Converts IEEE754-decimal blob Y and converts it into IEEE754-binary. Both routines are approximate because most IEEE754-binary values do not have an exact equivalent IEEE754-decimal representation and vice versa. Your UDFs would need to find something very close. Given these routines, you could then index your IEEE754-decimal columns by doing an index on an expression using the new iee754bin() function. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to index data based on custom comparisons?
On 12/13/17, Lifepillar <lifepil...@lifepillar.me> wrote: > > if I index the blob column directly, comparisons are > based on memcpy(), which in my case is not what I want. Is it > possible to create an index that somehow uses a custom comparison > function instead? No. SQLite always uses memcmp() to compare BLOBs. You can add a collating function for strings, but not for BLOBs. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2D query
On 12/13/17, Valentin Davydov <sqlite-u...@soi.spb.ru> wrote:
> Given the following table:
>
> CREATE TABLE people(name,sex);
> INSERT INTO people VALUES("Alex","F");
> INSERT INTO people VALUES("Alex","M");
> INSERT INTO people VALUES("Jane","F");
> INSERT INTO people VALUES("John","M");
>
> How to construct a query which returns coalesced sex but individual names,
> such as "F: Alex, Jane. M: Alex, John."?
SELECT sex || ': ' || group_concat(name, ", ") FROM people GROUP BY sex;
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does sqlite have official development testing tool?
On 12/13/17, advancenOO <haveagoodtime2...@gmail.com> wrote: > I am interested in the source code of sqlite and I want to make some change > to it. > I wondering if sqlite has official development testing to do a full > functional check or evaluate performance lose of my code? > > I have found sqlspeedtest1,8 and sqlthreadtest3,4. Is there any other > testing I should make? https://www.sqlite.org/testing.html -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] values ?
On 12/12/17, Mark Wagner <m...@google.com> wrote:
> My reading of https://sqlite.org/syntax/select-core.html makes me think
> that I should be able to issue something like values('foo'); and get a row
> with a single column whose value is 'foo'. But I get a syntax error.
>
> Probably obvious to the right people but what am I missing?
It probably means you are using an older version of SQLite. The
syntax you describe as introduced in version 3.8.3 (2014-02-03).
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Enabling MMAP in Android
On 12/12/17, advancenOO <haveagoodtime2...@gmail.com> wrote: > Um, stray pointer or array overrun in upper applications will be treated as > BUG in my system. And I believe all these bugs could be fixed before using > sqlite. You have a very optimistic view of application software :-) Look - if you are interested in maximizing performance, you need to use the latest Linux kernel with the latest F2FS filesystem, then compile SQLite with the -DSQLITE_ENABLE_BATCH_ATOMIC_WRITE. That combination will get you way more performance boost than you will ever find by playing games with mmap(). -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Enabling MMAP in Android
On 12/11/17, advancenOO <haveagoodtime2...@gmail.com> wrote: > > At the same time, Mmap() is only used for reading by default, not writing. > But why? If the database is memory-mapped writable, then a stray pointer or array overrun in the application (NB: not in SQLite - in the application that uses SQLite) could easily overwrite part of the database file and corrupt the database file. It is also possible for this to happen without the use of writable memory-maps, if the application corrupts internal SQLite data structures and a subsequent COMMIT occurs. But that is much less likely, as SQLite would probably detect the situation before the COMMIT completes, thus leaving the database intact. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE SET using column-name-list Syntax error
On 12/10/17, javaj1...@elxala.com <javaj1...@elxala.com> wrote:
>
> According to the documentation UPDATE SET admits column-name-list as
> argument
> but I cannot get it working. Here some tries
>
> DROP TABLE IF EXISTS test; CREATE TABLE test(a,b,c);
>
> -- ok
> UPDATE test SET a = "vala", b = "valb" ;
> UPDATE test SET a = (SELECT "vala"), b = (SELECT "valb") ;
>
> -- not ok
> UPDATE test SET (a, b) = "vala", "valb" ;
> Error: near "(": syntax error
> UPDATE test SET (a, b) = (SELECT "vala", "valb") ;
> Error: near "(": syntax error
>
> What am I doing wrong ? or is this syntax really supported ?
It is supported beginning with SQLite 3.15.0 (2016-10-14). What
version of SQLite are you running?
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Timing issue with min, max and count
On 12/8/17, x <tam118...@hotmail.com> wrote: > > I have a table with 2.4 million records. It’s a without rowid table (I don’t > know if that’s significant) with an integer primary key (ID) and several > secondary indexes of the form (OtherCol, ID). If I run (1) If you are using INTEGER PRIMARY KEY, you should *not* be using a WITHOUT ROWID. You can. It will get the correct answer. But it will be unnecessarily slower. (2) You should never include a single-column PRIMARY KEY as part of an index. The database engine will do that for you automatically. > > select min(ID), max(ID), count(*) from BigTbl; > > It takes 0.67 secs > > If I run the three commands separately > > select min(ID) from BigTbl; > select max(ID) from BigTbl; > select count(*) from BigTbl; SQLite internally performs special optimizations for the first two cases, transforming them as follows: (A) SELECT id FROM bittbl ORDER BY id LIMIT 1; (B) SELECT id FROM bigtbl ORDER BY id DESC LIMIT 1; And isolated "count(*)" without a WHERE uses a separate special optimization in the storage engine that allows it to count the number of entries in the btree very quick. But these special optimizations only apply when min(), max(), and count(*) are used in isolation. Hence, they do not work for the first query above that uses all three functions at one. > > the TOTAL time to run all 3 is around 0.1 secs. > > explain query plan select min(ID) from BigTbl; suggests the primary key is > used > explain query plan select max(ID) from BigTbl; suggests the primary key is > used > explain query plan select count(*) from BigTbl; suggests a secondary index > (call it Ndx) is used > > Any combo also seems to use secondary index Ndx e.g. > > explain query plan select min(ID), max(ID) from BigTbl; > and > explain query plan select min(ID), max(ID), count(*) from BigTbl; > > both use secondary index Ndx. > > All come up with the correct answer but obviously when Ndx is used min and > max require checking all values of ID rather than obtaining the result from > first and last entries in primary key. > > Tom > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] getting error "string or blob too big" for 500 MB data insertion
On 12/8/17, Durgesh <durgeshnmah...@gmail.com> wrote: > I am trying to insert 500 MB of row data using Qt SQL into sqlite db. How are you measuring the row size? > > Insertion is successful up to 450 MB. > > defined macro SQLITE_MAX_LENGTH to larger value than 500 MB, as mentioned in > http://www.sqlite.org/limits.html > > Still getting above error while insertion. > > > > > > -- > Sent from: http://sqlite.1065341.n5.nabble.com/ > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Errors in "julianday" function in SqLite
On 12/7/17, David Simmons <d...@smallscript.com> wrote:
> There are many julian-day website sources, but this one is correct
> present-day all the way back to 4712 B.C. (not including what should be
> 46BC to AD8 leap year anomalies due to early priests LY every 3 year
> error corrections).
> SqLite:
>
> SELECT julianday('0001-01-01 12:00') AS jd
> 1721426correct answer is 1721424
> ---
> SELECT julianday('1582-10-04 12:00') AS jd
> 2299150 correct answer is 2299160
There are three separate systems in common use for naming days:
(1) The Julian Calendar named after the Caesar
(2) The Gregorian Calendar named after a Pope
(3) The julian day number, named after some guy named Julius (or maybe
it was his father's name that was Julius. Or Julian. I forget...)
One confusing aspect of this is even though (1) and (3) have similar
names, they are completely unrelated.
The ancient world use the Julian calendar. But around 1582, folks
began switching over to the Gregorian calendar. This happened slowly
over a span of more than 300 years. Several countries (Russia,
Turkey) have only switched within living memory. There was a time
when, as you traveled about the world, you would not only have to
adjust your watch, but you'd also have to adjust your calendar! The
conversion from Julian to Gregorian in most English-speaking countries
occurred on 1752-09-02 (Julian) which was followed immediately by
1752-09-14 (Gregorian). On your unix system, type "cal 1752" and see
that the month of September only had 19 days that year.
SQLite does all date computations using the Gregorian calendar. It
does this even for dates that predate the invention of the Gregorian
calendar. That way it does not have to worry with locale-specific
transitions from Julian to Gregorian.
The USNO uses the Gregorian calendar for modern dates and the Julian
calendar for earlier dates. I do not know what they use as a
transition date.
The julianday() function of SQLite converts Gregorian calendar dates
(type 2) into a julian day number (type 3).
The USNO website converts "Calendar dates" (a mixture of Gregorian and
Julian dates) into the corresponding Julian day number.
These are different computations. SQLite gets the same answer as the
USNO as long as the USNO is using the Gregorian calendar. When the
USNO switches to the Julian calendar, then the answers diverge.
This is not a bug in SQLite's date function. SQLite is computing
exactly the correct julian day number given an input gregorian date.
That is what the julianday() function in SQLite is defined to do. The
date/time functions in SQLite do not know anything about Julian
calendar dates. SQLite does not have the capability of converting
Julian calendar dates into other systems such as the Gregorian
calendar or the julian-day number.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cross-compiling fails.
On 12/6/17, Alastair Growcott <agrowc...@h-scientific.co.uk> wrote: > > To avoid confusion I recommend that you remove the option to > cross-compile I'm cool with that approach. Can you suggest a specific edit to the configure.ac file that will accomplish what you describe? > > Also, I am not sure your suggestion of generating sqlite3.c natively and > then cross-compiling it separately will work. The generated sqlite3.c > has some platform dependent stuff (selective header inclusion as per > above) in it. > The generated sqlite3.c file should be byte-for-byte identical on all host platforms. We test for that. And we publish the SHA3 hash of the generated sqlite3.c file in our release notes. If you find a pair of host platforms that build different "sqlite3.c" files from the same sources, then that is a bug. Please report it. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cross-compiling fails.
On 12/4/17, Alastair Growcott <agrowc...@h-scientific.co.uk> wrote: > Cross-compiling sqlite3 fails due to the use of binaries in the build > process that are generated during the build process. I recommend that you do ./configure; make sqlite3.c or nmake /f makefile.msc sqlite3.c depending on whether your host system is unix or windows. Then manually compile the resulting sqlite3.c for your target platform. What are the host and target systems for the build you are trying to do? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?
On 12/5/17, no...@null.net <no...@null.net> wrote: > On Tue Nov 28, 2017 at 03:30:54PM +, David Raymond wrote: > > SQLite developers, do you recognise this thread as an issue? Not a serious issue, no. I might look into it when I have time, but I'm neck-deep in other issues at the moment. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Assert failure line 72132, function: valueFromExpr
On 11/27/17, no...@null.net <no...@null.net> wrote: > > assertion "0" failed: file "sqlite3.c", line 72132, function: > valueFromExpr > This should be fixed in the latest Pre-release Snapshot, uploaded moments ago. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Assert failure line 72132, function: valueFromExpr
On 11/27/17, Richard Hipp <d...@sqlite.org> wrote: > I am unable to reproduce the problem. Dan suggested that I needed to enable foreign keys, and that did indeed enable me to repro the problem. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Assert failure line 72132, function: valueFromExpr
On 11/27/17, no...@null.net <no...@null.net> wrote: > [version sqlite-snapshot-201711181730] > > I am seeing an error when attempting to insert a row with > SQLITE_DEBUG enabled: > > assertion "0" failed: file "sqlite3.c", line 72132, function: > valueFromExpr > I am unable to reproduce the problem. The database and INSERT statement you sent via private email work fine for me. No errors or warnings. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Assert failure line 72132, function: valueFromExpr
90 00 affinity(r[12..30]) > 107 Null 0 310 00 r[31]=NULL > 108 String8 0 410 00 r[41]='' > 109 Eq 41 -12 13 00 if r[13]==r[41] goto -12 > 110 SCopy 13 320 00 r[32]=r[13] > 111 IntCopy 11 330 00 r[33]=r[11] > 112 MakeRecord 322 31 00 r[31]=mkrec(r[32..33]) > 113 NoConflict 1 -12 32 00 key=r[32] > 114 Halt 206720 00 > 115 IsNull 14 -130 00 if r[14]==NULL goto -13 > 116 SCopy 14 410 00 r[41]=r[14] > 117 MustBeInt 4100 00 > 118 Eq 11 -13 41 00 if r[41]==r[11] goto -13 > 119 OpenRead 2 1110 00 root=111 iDb=0 > 120 NotExists20 41 00 intkey=r[41] > 121 Goto 0 -130 00 > 122 FkCounter010 00 fkctr[0]+=1 > 123 Close200 00 > 124 IsNull 20 -140 00 if r[20]==NULL goto -14 > 125 SCopy 20 410 00 r[41]=r[20] > 126 MustBeInt 4100 00 > 127 OpenRead 3 1530 00 root=153 iDb=0 > 128 NotExists30 41 00 intkey=r[41] > 129 Goto 0 -140 00 > 130 FkCounter110 00 fkctr[1]+=1 > 131 Close300 00 > 132 FkIfZero 000 00 if fkctr[0]==0 goto 0 > > The error looks like it occurs inside a trigger statement. The database > schema is perhaps a bit large for the mailing list, but I'm happy to > email the database directly to the developers if that is useful. > > Anything else I can provide to troubleshoot this further? > > -- > Mark Lawrence > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Wrong mailing list..... Was: More timeline changes.
Please ignore. This post was intended for fossil-us...@lists.fossli-scm.org, not for the SQLite users mailing list. My appologies.. On 11/25/17, Richard Hipp <d...@sqlite.org> wrote: > In the latest code on https://www.fossil-scm.org/fossil/timeline and > at https://sqlite.org/srcx/timeline has a "Declutter" button on the > sub-menu bar to simplify the screen. In the simplified timeline, > there is a "Details" button to get all the details back again. > > I'm not done with this interface improvement push. Here are my > short-term plans: > > (1) Right now, pressing "Declutter" or "Details" is a server > round-trip. I think it would better to handle this using javascript. > > (2) Decluttered should be the default. Currently Details is the > default. I spent a lot of time experimenting last night, and what I > found myself doing every time I encountered a timeline was immediately > pressing the "Declutter" button to get a high-level overview of the > graph, then clicking on "Details" if I wanted to see more. From that > experience, I think coming up in Decluttered mode would be a much > better approach. > > (3) Details/Declutter for the entire graph is good and should be kept. > But it would be even better to be able to see the details of > individual timeline entries. I'm thinking that timelines come up in > decluttered mode (showing only the check-in comment for each entry) > but with ellipses or some other small icon at the end of each comment > that you can click on to expand the details. > > You can help! Send me your ideas of what you think timelines should > look like. Even better: send me mock-ups. Static HTML pages that > you have hand-edited will be fine - just be sure to include the CSS, > or better, put all the CSS in-line on your hand-edited mockup. > > You can also keep experimenting with the code I have on-line and send > me your complaints and suggestions for improvement. > > Web developers - help me with this: For item (3) above, how can I > make the ellipsis or icon to "show more detail" configurable using > CSS? What's the best way to handle that so that people can customize > the look for various skins? > > -- > D. Richard Hipp > d...@sqlite.org > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] More timeline changes.
In the latest code on https://www.fossil-scm.org/fossil/timeline and at https://sqlite.org/srcx/timeline has a "Declutter" button on the sub-menu bar to simplify the screen. In the simplified timeline, there is a "Details" button to get all the details back again. I'm not done with this interface improvement push. Here are my short-term plans: (1) Right now, pressing "Declutter" or "Details" is a server round-trip. I think it would better to handle this using javascript. (2) Decluttered should be the default. Currently Details is the default. I spent a lot of time experimenting last night, and what I found myself doing every time I encountered a timeline was immediately pressing the "Declutter" button to get a high-level overview of the graph, then clicking on "Details" if I wanted to see more. From that experience, I think coming up in Decluttered mode would be a much better approach. (3) Details/Declutter for the entire graph is good and should be kept. But it would be even better to be able to see the details of individual timeline entries. I'm thinking that timelines come up in decluttered mode (showing only the check-in comment for each entry) but with ellipses or some other small icon at the end of each comment that you can click on to expand the details. You can help! Send me your ideas of what you think timelines should look like. Even better: send me mock-ups. Static HTML pages that you have hand-edited will be fine - just be sure to include the CSS, or better, put all the CSS in-line on your hand-edited mockup. You can also keep experimenting with the code I have on-line and send me your complaints and suggestions for improvement. Web developers - help me with this: For item (3) above, how can I make the ellipsis or icon to "show more detail" configurable using CSS? What's the best way to handle that so that people can customize the look for various skins? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite website A/B comparison
Which is the better "timeline" display for the SQLite project status, in your opinion? A: https://sqlite.org/src/timeline B: https://sqlite.org/b/timeline Since this question is really more about the version-control system than SQLite itself, please reply directly to me rather responding to the mailing list. Or, join the discussion of this question over on the fossil-us...@lists.fossil-scm.org mailing list. Further suggestions for the improvement of the timeline display (or anything else on the website) are welcomed. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exit value of "PRAGMA integrity_check" in command line
On 11/22/17, 林自均 <johnl...@gmail.com> wrote: > > $ sqlite3 other-db.sqlite 'PRAGMA integrity_check' > Error: database disk image is malformed > $ echo $? > 11 > > Is that normal? > Yes. The error is occurring as SQLite is trying to parse the schema, not while running "PRAGMA integrity_chck". -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exit value of "PRAGMA integrity_check" in command line
On 11/22/17, 林自均 <johnl...@gmail.com> wrote: > > Usually, if the database corrupts, the exit value is non-zero. However, I > recently bumped into a corrupted sqlite file that makes the previous > command exit with zero. > The behavior of PRAGMA integrity_check changed with release 3.21.0. It should now always return error message text and an SQLITE_OK return code. See item 9 on the relase notes: https://www.sqlite.org/releaselog/3_21_0.html -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT result different after ANALYZE
On 11/21/17, Richard Hipp <d...@sqlite.org> wrote: > > To work around this problem, please DROP all indexes on the INTEGER > PRIMARY KEY columns. Except, you don't have any indexes on INTEGER PRIMARY KEY columns. I misread the schema. I'll be working on some other solution for you. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Many ML emails going to GMail's SPAM
On 11/21/17, Paul Sanderson <sandersonforens...@gmail.com> wrote: > Coincidence! I have just been in my gmail folder marking a load of SQLite > email as 'not spam' I've been seeing mailing list emails go to spam for a while now. Nothing has changed with MailMan. I think what we are seeing is the beginning of the end of email as a viable communication medium. I really need to come up with an alternative to the mailing list. Perhaps some kind of forum system. Suggestions are welcomed. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT result different after ANALYZE
On 11/20/17, David Raymond <david.raym...@tomtom.com> wrote: > > To reproduce, download this database file (5.6MB, SHA1 > 12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from now): > >https://expirebox.com/download/328baafe26688579fccd55debfc54ad3.html > > This SQL returns a single result row with a value of 1: > > SELECT DISTINCT t2.a FROM t1 > INNER JOIN t2 ON t1.t2_id = t2.id > WHERE t1.t2_id <> -1; > > Then run ANALYZE and run the above select again. This time I receive no > result. Thank you for the bug report. To work around this problem, please DROP all indexes on the INTEGER PRIMARY KEY columns. Such indexes are accomplish nothing (they will never be used on a real query - they are just slow down inserts and updates and take up extra space on disk) except in this case they do appear to be confusing the query planner. This problem will be corrected in the next release. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] View is not flattened when inside an IN sub-query
On 11/18/17, E.Pasma <pasm...@concepts.nl> wrote: > I found that in the > SQLite3 timeline Thanks for watching the timeline! -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update the SQLite package in Tcl
On 11/18/17, Balaji Ramanathan <balaji.ramanat...@gmail.com> wrote: > Hi, > > I have installed Tcl/Tk from a couple of places on the web (activetcl > and magicsplat), and I find that neither of them has the latest version of > sqlite3 as the standard sqlite3 package. ActiveTcl seems to be linked to > sqlite 3.13 while magicsplat's version comes with sqlite 3.20. > > What do I need to do to get them both up to sqlite 3.21? On the SQLite download page, you will find both a Pre-release Snapshot and a "sqlite-autoconf" tarball. Download either of these. (I suggest the Pre-release Snapshot so that you can help us beta test!) Untar, and cd into the "tea" subdirectory. Then type: "./configure; make install". That is suppose to install the latest SQLite for TCL. "TEA" is the "Tcl Extension Architecture". -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
On 11/17/17, Peter Da Silva <peter.dasi...@flightaware.com> wrote:
>
> $db eval {
> SELECT Tea
> FROM teaInStock
> ORDER BY LastUsed DESC
> LIMIT $nrToFetch;
> } {
> ... do something with $Tea ...
> }
>
> This latter case works because the query is surrounded by {} so Tcl won’t
> substitute the variable, it will be seen and securely inserted into the
> query by SQLite.
FWIW, this is the reason that SQLite, uniquely among all modern SQL
database engines, understands $nrToFetch as a bound parameter.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
On 11/16/17, Cecil Westerhof <cldwester...@gmail.com> wrote: > Is it possible to get the library version before connecting to a database? puts [sqlite -version] -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Starting with TCL
On 11/16/17, Cecil Westerhof <cldwester...@gmail.com> wrote:
> puts $Tea, $Location
Everything in TCL is a function. The syntax is "FUNCTIONNAME ARG1
ARG2 ARG3 ..." where the arguments are separated by white space. The
"puts" function takes either one or two arguments. The one-argument
form of "puts" outputs ARG1 to standard-output. The two-argument form
of "puts" sends ARG2 to output stream specified by ARG1.
Your code above tries to invoke the two-argument form of "puts".
Equivalent javascript code would be:
puts(Tea + ",", Location)
The error arises because there is no output channel named by the
result of Tea+",". What you want is the one-argument form, equivalent
to this JS:
puts(Tea + ", " + Location)
To get that using TCL syntax, you can use quoting to make the two
separate arguments into one:
puts "$Tea, $Location"
The key point is that everything in TCL is of the format FUNCTION ARG1
ARG2 The processing steps are like this:
(1) Identify arguments separated by whitespace. Note that all text
within "..." and within nested {...} is a single argument.
(2) Resolve quotes. This means remove the outermost {...} from
arguments quoted using {...}. Remove the "..." around double-quoted
argments, and also resolve any $variable name within the double
quotes. The $variable name resolution does not happen with {...}
(3) Invoke the function with its arguments.
Note that *everything* is a function. Even "control" statements. In
Tcl when you see:
if {$i<10} {
puts "yes"
} else {
puts "no"
}
That really is invoking the "if" function with 4 arguments. Since
everything is a function, everything follows exactly the same quoting
rules. This is an important feature of Tcl that programmers whose
prior experience has been exclusively using Algol-derived languages
such as C, Java, Javascript, and Python may have difficulty getting
their heads around. But once you do "get it", it starts to seem very
natural.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Bytecode
On 11/15/17, aUser <sqli...@byom.de> wrote: > Is it possible (and useful), to generate SQLite bytecode instead of a SQL > statement? It is neither possible nor useful. The bytecode changes from one release to the next - it is not stable. We treasure this design freedom and will not yield it. In the current bytecode design, if arbitrary bytecode (that is to say, bytecode not generated by SQLite itself) is supplied, then there are many paths that could result in memory corruption, database corruption, assertion faults, segfaults, memory leaks, and other bad things. Adding the extra logic to defend the bytecode against such problems would slow down SQLite for everybody, which we are not willing to do. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Bytecode
On 11/15/17, aUser <sqli...@byom.de> wrote: > Hello > > I developed an application-specific query language. Currently, I am > converting a query to a rather difficult SQL statement. > > Is it possible (and useful), to generate SQLite bytecode instead of a SQL > statement? > If yes, could someone share some C code as an example, how it could be done? > (F.i. Do I need to hold some locks?) > > Best Regards > > > > > -- > Sent from: http://sqlite.1065341.n5.nabble.com/ > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to develop a GUI front-end
On 11/15/17, Peter Da Silva <peter.dasi...@flightaware.com> wrote: > Tk is platform independent, so long as you don’t do UNIX-specific stuff (eg, > assume UNIX file paths and stuff) any Tk app should work just fine on > Windows. A majority of the SQLite source code and also the Fossil SCM source code (those parts written by me, which is the majority in both cases) was composed in a cross-platform text editor that is written in Tcl/Tk, based on the text widget. The same TclTk source file runs on linux, mac, and windows. I've used that same editor for two decades, now and then updating it for this or that new feature that I find useful. SQLite began life as a Tcl extension that only later escaped into the wild. That's why most of the test cases for SQLite are written in Tcl, why "tclsh" is required to build from canonical sources, why the Tcl language bindings are built in, why SQLite accept parameters that look like Tcl language variables, and why SQLite uses flexible typing and does not require explicit data types on columns. There is a new book out on Tcl by Ashok Nadkarni. See http://wiki.tcl.tk/48868 for details. I have not read it myself, but people I trust tell me that it is good. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 1TB limit on encrypted database
On 11/15/17, Andrew Stewart <astew...@arguscontrols.com> wrote: > Thanks. This has worked. Temporarily. You cannot increase the max_page_count above 2147483646, so if your database continues to grow, you are going to need to look into increasing the page size from 1024 to something larger like 8192. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 1TB limit on encrypted database
On 11/14/17, Andrew Stewart <astew...@arguscontrols.com> wrote: > Richard, > If I am trying to change the max_page_count use the SEE.EXE program, > how do I get the value to be updated in the database. Every time I try > changing this and then exit see.exe, the value has not changed. Do I need > the updated encryption extension first? The PRAGMA only changes the one database connection in which it runs. To make the setting persistent, recompile with -DSQLITE_MAX_PAGE_COUNT=20 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 1TB limit on encrypted database
On 11/14/17, Andrew Stewart <astew...@arguscontrols.com> wrote: > Hi, > I appear to have reached an issue with a 1TB limit on an > encrypted database. The page_size is 1024. The max_page_count is > 1073741823. I don't appear to be able to change either of these numbers. > Any assistance would be appreciated. You can try: PRAGMA max_page_count=20; Probably you should also look into increasing the page size: PRAGMA page_size=8192; VACUUM; For that last step, make sure you have upgraded to a recent version of the encryption extension as some of the older versions had issues. Also, note that the VACUUM will take some time and will require a couple TB of temporary disk space while it is running. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
