[sqlite] "PRAGMA data_version" documentation wrong

I think the documentation for "PRAGMA data_version" at https://www.sqlite.org/pragma.html is incorrect.  I've been testing this pragma and I've found that it only returns '1' for a standard database or '2' if the database is in WAL mode.  The documentation makes it sound like this value

Re: [sqlite] how into insert row into middle of table with integer primary key

Hi, On Mon, Nov 20, 2017 at 10:12 AM, R Smith wrote: > This question pops up from time to time. > > I will show a correct query script to achieve this below, but I want to > emphasize what others have said: Data in an RDBMS has no intrinsic order, > it's all SETs, and if you

[sqlite] SELECT result different after ANALYZE

I am presenting a scenario where a SELECT produces a different result after running ANALYZE. To reproduce, download this database file (5.6MB, SHA1 12d1295d06327ee19ed2453517b0dd83233c6829, available for two days from now):

Re: [sqlite] SELECT result different after ANALYZE

Confirming it's doing the same thing for me. Taking out the distinct keyword will return a bunch of 1's, adding it in doesn't show them. Definitely something buggy here. D:\Temp>sqlite3 "analyze_problem - Copy.db" SQLite version 3.21.0 2017-10-24 18:55:49 Enter ".help" for usage hints.

Re: [sqlite] how into insert row into middle of table with integer primary key

This question pops up from time to time. I will show a correct query script to achieve this below, but I want to emphasize what others have said: Data in an RDBMS has no intrinsic order, it's all SETs, and if you artificially bestow order to the data itself (as opposed to the eventual output)

Re: [sqlite] SELECT result different after ANALYZE

3.18.0 gets it correct, 3.19.0 gets it wrong. -Original Message- From: David Raymond Sent: Monday, November 20, 2017 11:03 AM To: 'SQLite mailing list' Subject: RE: [sqlite] SELECT result different after ANALYZE Confirming it's doing the same thing for me. Taking out the distinct

Re: [sqlite] SELECT result different after ANALYZE

Just to Add to what Ralf and David already pointed out: Works for me on 3.18, not in 3.20.1 and more importantly,  the sqlite_stat1 table itself seems to have zero impact, once Analyze is run, the query always does not work, even if you drop the sqlite_stat1 table or mess with its values.

Re: [sqlite] "PRAGMA data_version" documentation wrong

On 20 Nov 2017, at 6:20pm, Jim Dossey wrote: > I think the documentation for "PRAGMA data_version" at > https://www.sqlite.org/pragma.html is incorrect. I've been testing this > pragma and I've found that it only returns '1' for a standard database or '2' > if the database is

Re: [sqlite] read/write binary data via tcl

Found a solution:) May be it could be included in the tcl interface specification as well. package req sqlite3 sqlite3 db :memory: db eval {create tanle img(b blob)} # save data set fd [open test.png r] fconfigure $fd -translation binary set c1 [read $fd] close $fd db eval {insert into img

Re: [sqlite] SELECT result different after ANALYZE

Apologies for the Spam, and this may be of no importance whatsoever, but just in case it is useful... I already mentioned that dropping/messing with the sqlite_stat1 table doesn't help - BUT it seems if you close the connection and re-open in a new connection (after you have dropped the

[sqlite] Get result of 'pragma foreign_keys' in c programme

I thought sqlite3_prepare16_v2(DB,"pragma foreign_keys",-1,,NULL) would work but it doesn’t return SQLITE_OK. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Get result of 'pragma foreign_keys' in c programme

Thanks Jens, working now. -- 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

Re: [sqlite] Help with left joins

On 2017/11/20 5:33 PM, x wrote: Only if ColB, ColC and ColD are unique in their tables. Otherwise each join has the potential of returning multiple rows, which will carry over to the next joins. Thanks David, I did say ColB, ColC & ColD were primary keys. Any kind of Query (especially of

Re: [sqlite] Get result of 'pragma foreign_keys' in c programme

> On Nov 20, 2017, at 9:08 AM, x wrote: > > sqlite3_prepare16_v2(DB,"pragma foreign_keys",-1,,NULL) That function expects a UTF-16 encoded string (hence the “16” in the name), but you’re giving it an 8-bit C string. Just call sqlite3_prepare_v2 instead. —Jens

Re: [sqlite] how into insert row into middle of table with integer primary key

On 2017/11/20 6:33 PM, Igor Korot wrote: On Mon, Nov 20, 2017 at 10:12 AM, R Smith wrote: The reason why this is bad? Mostly a primary Key serves as a lookup for other tables linking to a very specific record. Imagine your query that added fruit to recipes where needed

Re: [sqlite] [EXTERNAL] Possible User Defined Function (UDF) Bug?

My previous explain outputs were probably not quite right. With the following code inside the previously posted trigger: INSERT INTO deltas( id, change_id, function ) VALUES( nextval('deltas'), NEW.change_id, 'update_project' ); I

[sqlite] Energy consumption of SQLite queries

Dear All, I am doing a research on the energy consumed by a query in SQLite. I have a program which fills a database with blocks of data. Then, it attempts to remove some data from the database. I don't know how to measure the energy consumed from my host, i.e., my laptop which has both the

Re: [sqlite] Energy consumption of SQLite queries

Dear Ryan, Thanks for your response. As you said the energy completely depends on the machine, but that is fine for our experiment. We are not comparing SQLite with other databases. I am a researcher and I've developed a system which uses a database. My aim is to see how much time and energy my

Re: [sqlite] how into insert row into middle of table with integer primary key

Shane. If you're dead set on paying the cost for brute force mid table id insertion, take a look at INSTEAD OF triggers: https://sqlite.org/lang_createtrigger.html Your example would look like this: CREATE VIEW fruit_ins AS SELECT * FROM fruit; CREATE TRIGGER fruit_ins INSTEAD OF INSERT ON

Re: [sqlite] how into insert row into middle of table with integer primary key

On 20 Nov 2017, at 9:31pm, Shane Dev wrote: > I would to prefer to avoid this solution because it involves mutable state > (the RowCount variable) which is the "root of all evil" (bugs). Is there an > SQL statement which could reset the gaps back to x? As others have

Re: [sqlite] how into insert row into middle of table with integer primary key

Oops, didn't concentrate, that query should best be: WITH NewOrder(nid,norder) AS (     SELECT F1.id, (SELECT COUNT(*) * 100 FROM fruit AS F2 WHERE F2.SortOrder < F1.SortOrder) FROM fruit AS F1     ORDER BY F1.id -- This last ORDER BY is important as it forces the above correlated

Re: [sqlite] WAL mode with readers and writers

On 20 Nov 2017, at 7:37pm, Jim Dossey wrote: > sqlite3_prepare("SELECT * FROM table;"); > while (sqlite3_step() == SQLITE_ROW) { > x = current_rowid(); > sqlite3_exec("UPDATE table SET value=1 WHERE rowid=x;"); > sleep(1); > } In SQLite, as in other SQL engines, all

Re: [sqlite] Energy consumption of SQLite queries

Dear All, I am doing a research on the energy consumed by a query in SQLite. I have a program which fills a database with blocks of data. Then, it attempts to remove some data from the database. I don't know how to measure the energy consumed from my host, i.e., my laptop which has both the

[sqlite] journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

I'm working on a database application that is shut down quite often. At each shutdown, only a small amount of data is written to the DB, which is in WAL mode. Due to HW restrictions, I had to reduce the total amount of data written to disk, which is why I have activated

Re: [sqlite] how into insert row into middle of table with integer primary key

If you want the fruits sorted (and not duplicated), why not just declare that when defining the table? create table fruits (id integer primary key, fruit text collate nocase unique); and if you want to know the "relative row number" of the fruit simply have your application count them:

Re: [sqlite] how into insert row into middle of table with integer primary key

Hi Ryan, Nice trick - changing the sign of ID. I agree that changing an existing record's ID value would cause problems for any other field / table that referenced this key. If I used your idea of adding a SortOrder column incremented in steps of x, as you suggested, the gaps would start to

Re: [sqlite] how into insert row into middle of table with integer primary key

On 2017/11/20 11:31 PM, Shane Dev wrote: Hi Ryan, Nice trick - changing the sign of ID. I agree that changing an existing record's ID value would cause problems for any other field / table that referenced this key. If I used your idea of adding a SortOrder column incremented in steps of x,

Re: [sqlite] how into insert row into middle of table with integerprimary key

Possible something like this may work: create table fruit(id integer primary key, name text); insert into fruit values (1,'apple'), (2,'pear'), (3,'kiwi'); select * from fruit order by id; begin; update fruit set id = -id where id > 1; update fruit set id = 1-id where id < 0; end; insert

Re: [sqlite] "PRAGMA data_version" documentation wrong

On 11/21/2017 01:20 AM, Jim Dossey wrote: I think the documentation for "PRAGMA data_version" at https://www.sqlite.org/pragma.html is incorrect. I've been testing this pragma and I've found that it only returns '1' for a standard database or '2' if the database is in WAL mode. The

[sqlite] WAL mode with readers and writers

Thanks to feedback from Simon Slavin, I now understand how data_version works and have it working in my code.  But in my testing, I tried another situation to see what would happen with locking in WAL mode.  I have a process that does the following pseudo-code with a table:

Re: [sqlite] WAL mode with readers and writers

WAL mode permits 'reading' by multiple connections while 1 connection is writing. Never ever is more than a single writer permitted. It does this by creating "cursor stability" when a read transaction is commenced (that is, changes to the database made on a DIFFERENT CONNECTION will not be

Re: [sqlite] Energy consumption of SQLite queries

Energy measurement can be carried out with a recording electric power meter provided the power otherwise consumed by the operating system and other programs can be controlled for. Total energy used by the computer during one trial is computed by the integral of the recorded power reading over

Re: [sqlite] Energy consumption of SQLite queries

On 20 Nov 2017, at 10:54pm, Ali Dorri wrote: > I am doing a research on the energy consumed by a query in SQLite. I have a > program which fills a database with blocks of data. Then, it attempts to > remove some data from the database. I don't know how to measure the

Re: [sqlite] journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

On 20 Nov 2017, at 3:41pm, Kniep Stefan (CM/ESN3) wrote: > P.S.: As a test, I have modified sqlite3WalClose() (and the place where it is > called) to delete/truncate the WAL when journal_size_limit is reached. I had > to change only two lines, but of course I am

Re: [sqlite] Energy consumption of SQLite queries

>Considering how total energy of computation is entirely liberated as heat Human technology at its current primitive state converts ALL energy usage into heat. At scale you call this is called "global warming" and it is an inherent process -- at least until humans figure out how to convert

Re: [sqlite] how into insert row into middle of table with integer primary key

> On Nov 20, 2017, at 2:05 PM, Simon Slavin wrote: > > INSERT INTO fruit VALUES ((1 + 2) / 2), 'banana') > > This gives you a value of 1.5, and puts the new entry in the right place. This solution (which comes up every time this problem is discussed, it seems) is

Re: [sqlite] Help with left joins

Thanks Ryan. When I saw the redundant table still in the explain I was worried I had misunderstood something about left joins. Tom From: sqlite-users on behalf of R Smith Sent: Monday,

Re: [sqlite] Help with left joins

I’m not sure what you’re saying Simon or maybe you’re not sure what I’m asking. I’m not complaining about the fact SQLite fails to drop what I think is a redundant table in the second explain, I’m merely wanting to check that it is a redundant table in case I’ve got a wrong idea about left

Re: [sqlite] [EXTERNAL] Possible User Defined Function (UDF) Bug?

On Mon Nov 20, 2017 at 11:04:01AM +, Hick Gunter wrote: > > Can you provide the original SQL (both for the INSERT and the CREATE > TRIGGER) and the explain output (SQLite byte code, i.e .explain > followed by explain )? Here is the trigger code: CREATE TABLE func_update_project(

Re: [sqlite] Help with left joins

On 20 Nov 2017, at 11:09am, x wrote: > explain > select ColA from TblA > left join TblB using (ColB) > left join TblC using (ColC) > left join TblD using (ColD) > where ColBX=?; > > there will be no trace of TblC or TblD as they're redundant. Although you have not

[sqlite] how into insert row into middle of table with integer primary key

Let's say I have a table of fruit - sqlite> .sch fruit CREATE TABLE fruit(id integer primary key, name text); with some entries - sqlite> select * from fruit; id|name 1|apple 2|pear 3|kiwi Is there an easy way to insert 'banana' between apple and pear while still maintaining a consistent order

[sqlite] [OT] Updating sqlite in PHP windows

Hello and sorry for the light offtopic, but can't find useful answers. Firstly, the decision of use windows server and php 5.6 (with wampserver) is not mine, I must use them. As Unix developer windows is harsh to me. Current PHP5.6.32 (26 Oct 2017) has sqlite 3.8 version, but I want to use JSON

[sqlite] Possible User Defined Function (UDF) Bug?

[ version: sqlite-snapshot-201711181730.tar.gz embedded in Perl's DBD::SQLite module. ] I have a user-defined function used as follows: CREATE TRIGGER after_insert_x AFTER INSERT ON x FOR EACH ROW BEGIN INSERT INTO y(id) VALUES(

Re: [sqlite] [EXTERNAL] Possible User Defined Function (UDF) Bug?

I was unable to replicate a double call of the udf using the sql you provided. Can you provide the original SQL (both for the INSERT and the CREATE TRIGGER) and the explain output (SQLite byte code, i.e .explain followed by explain )? -Ursprüngliche Nachricht- Von: sqlite-users

[sqlite] Help with left joins

Suppose TblB has primary key ColB and contains a column ColBX TblC has primary key ColC and contains a column ColCX TblD has primary key ColD and contains a column ColDX TblA has primary key ColA and also contains columns ColB, ColC and ColD (i.e. TblB, TblC and TblD are effectively lookup

Re: [sqlite] Help with left joins

Only if ColB, ColC and ColD are unique in their tables. Otherwise each join has the potential of returning multiple rows, which will carry over to the next joins. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of x Sent: Monday,

Re: [sqlite] [EXTERNAL] how into insert row into middle of table with integer primary key

Not without deleting and reinserting (or alternatively, updating) every single row past the desired insert position, since you have declared that the id field is a synonym for the internal rowid. Had you declared "id real primary key" you could have gotten away with using the arithmetic mean

Re: [sqlite] how into insert row into middle of table with integer primary key

Shane Dev wrote: > CREATE TABLE fruit(id integer primary key, name text); > > id|name > 1|apple > 2|pear > 3|kiwi > > Is there an easy way to insert 'banana' between apple and pear while still > maintaining a consistent order of the ID field? It would be possible, but not easy, especially not in

Re: [sqlite] how into insert row into middle of table with integer primary key

On 19 Nov 2017, at 8:37pm, Shane Dev wrote: > sqlite> select * from fruit; > id|name > 1|apple > 2|pear > 3|kiwi > > Is there an easy way to insert 'banana' between apple and pear while still > maintaining a consistent order of the ID field? > > desired result - > >

Re: [sqlite] [OT] Updating sqlite in PHP windows

On 20 Nov 2017, at 11:06am, Eduardo wrote: > Or better, a recipe that works to compile sqlite3 on php5.6.x? This is the best-looking page I’ve found, but I have never tried it on Windows.

[sqlite] read/write binary data via tcl

Hi all, I try to read/write image files in tcl with sqlite. I would like to keep it simple and not convert to/from base64. 1. write the image: package req sqlite3 sqlite3 db test db eval {create table img(data blob)} set fd [open t.png r] fconfigure $fd -translation binary set c [read $fd]

Re: [sqlite] how into insert row into middle of table with integer primary key

Simon Slavin wrote: > UPDATE fruit SET id = id+1 WHERE id >=2; This is unlikely to work because some ID values can conflict in the middle of the execution. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] how into insert row into middle of table with integer primary key

That actually doesn't work in SQLite as it checks the primary key uniqueness after every row change, not after all updates have been completed. sqlite> update fruit set id = id + 1 where id >= 2; --EQP-- 0,0,0,SEARCH TABLE fruit USING INTEGER PRIMARY KEY (rowid>?) Run Time: real 0.000 user

Re: [sqlite] how into insert row into middle of table with integer primary key

If you want to maintain something like a user-selected display order, I would suggest adding an explicit “display order” column. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] how into insert row into middle of table with integer primary key

On 20 Nov 2017, at 2:57pm, Clemens Ladisch wrote: > Simon Slavin wrote: >> UPDATE fruit SET id = id+1 WHERE id >=2; > > This is unlikely to work because some ID values can conflict in the > middle of the execution. Which in fact violates formal requirements. Im SQL it is

Re: [sqlite] Help with left joins

>Only if ColB, ColC and ColD are unique in their tables. Otherwise each join >has the potential of returning multiple rows, which will carry over to the >next joins. Thanks David, I did say ColB, ColC & ColD were primary keys. ___ sqlite-users mailing