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
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
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):
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.
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)
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
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.
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
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
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
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
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
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
> 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
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
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
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
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
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
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
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
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
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
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
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:
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
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,
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
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
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:
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
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
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
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
>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
> 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
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,
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
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(
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
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
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
[ 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(
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
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
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,
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
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
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 -
>
>
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.
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]
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
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
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
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
>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
56 matches
Mail list logo