Re: [sqlite] Help with left joins

[x]

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, November 20, 2017 3:55:42 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: 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 the SELECT variety) can have multiple
redundancies or  indeed omit-able specified values. An optimization to
ensure non-used left-joins on Primary keys are not used is probably a
negligible improvement.

To answer your question: You are correct to assume the same output -
There is no difference in the SET-Theory/Algebra producing those
specific rows whether you omit the left joins or not, BUT that only
holds true while the Indexes are indeed all PRIMARY (or at least Unique)
and the joins are of the "LEFT" variety. Change any of those and the
query output may look very different.

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

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

[Jens Alfke]

> 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 attractive but not very scaleable. All you have to do is add 60 records one 
at a time after record 1, and you’ll exceed the precision of double-precision 
floating point and get duplicate values that don’t have a stable sort order.

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

Re: [sqlite] journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

[Simon Slavin]

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 not sure that my change doesn't 
> break anything.

Do not delete the WAL file unless all connections to the database have been 
closed properly — without crashes, and without an error code being returned to 
SQLite.  Having an empty WAL file does not do exactly the same thing as having 
no WAL file.

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

Re: [sqlite] Energy consumption of SQLite queries

[Simon Slavin]

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 energy
> consumed from my host, i.e., my laptop which has both the SQLite and the
> program, from the time I generated the query till the query is finished and
> control returns back to my program.

This is a hardware question, not anything to do with a particular piece of 
software.

If you have a desktop computer, get one of those gadgets that you plug into the 
power socket and monitors how much power is passed to things that plug into 
them:



On a laptop, since the power is taken from an internal battery, and mains power 
is used to recharge it inconsistently, monitoring power usage from the mains is 
pointless.  See if the firmware provides a display or an API function which 
shows how much is going out.

Then set up side-by-side comparisons, one with your computer doing those things 
in SQLite and one without.  The differences between the two power consumptions 
is how much power SQLite is using.  Unless you have really detailed power 
measurement, the results will be small and probably meaningless.

Since you mention doing side-by-side comparisons with other databases, your 
setup should probably be comparing the same computer doing things in different 
DBMSs.  Maybe set up some procedure for doing something 10,000 times and see 
how much power is used in total.

Worth noting that power consumption from SQLite will be terribly inconsistent, 
based on what data is cached, how many database pages need to be accessed, and 
the state of the journal files.  This pales into insignificance, however, with 
the inconsistency of most other DBMSs, which perform far more internal caching 
and indexing.  You will get very different results from the same setup 
depending on how long the DBMS server has been running, not just on how long 
the computer has been turned on.

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

Re: [sqlite] Energy consumption of SQLite queries

[Keith Medcalf]

>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 energy back into matter.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

Re: [sqlite] Energy consumption of SQLite queries

[petern]

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
the time of the experiment.  Do you have a time recording power meter to
connect between the computer and power source?  A sensitive Coulomb
counting power meter such as those used for characterizing batteries would
be ideal.  Often such meters will have a direct readout of the integrated
energy in Joules or Watt Hours.

Considering how total energy of computation is entirely liberated as heat
from the computer's electronics, another technique would be to put the
computer inside a well insulated and air tight cabinet for which you've
determined the specific heat of the system.  Specific heat might be
experimentally determined by measuring the temperature rise of the system
relative to the energy expended from a precise energy standard.   Such a
standard is merely a carefully measured amount of pure chemicals which
produce a well defined exothermic reaction that doesn't destroy the
apparatus.

Generally I would suggest finding a small DC powered single board computer
which runs only the SQLite program during the experiment.  That would
eliminate controlling for energy use of programs and irrelevant system
components like power supply, graphics card, and so on. You will also have
to design your experiment around the choice for secondary storage.  For
example, a solid state disk will have different energy characteristics
compared to a mechanical disk.  Separating the factors of energy for
storage versus energy from computation would be an important dimension for
an energy study of DBMS's.

Peter

On Mon, Nov 20, 2017 at 2:54 PM, Ali Dorri  wrote:

> 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 and the
> program, from the time I generated the query till the query is finished and
> control returns back to my program.
>
> Any help is highly appreciated.
>
> Regards
> Ali
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

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:


logicalrow = 0
prepare('SELECT fruit from fruits order by fruit;')
while True:
  if step() == NO MORE ROWS
 break
  fruitname = getcolumn(1)
  logicalrecord++
  playWithFruitAndLogicalRecordNumber()
wend


There is very little use (if any at all) for a "logical row number" is 
Relational Data ...
  

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Shane Dev
>Sent: Monday, 20 November, 2017 14:31
>To: SQLite mailing list
>Subject: 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 disappear after many
>inserts
>between existing records. I suppose the gaps could be reset by
>writing
>program to call sqlite3_exec with
>
>select * from fruit order by SortOrder;
>
>and increment a RowCount variable each time the callback was
>triggered,
>then update fruit with something like
>
>update fruit set SortOrder = RowCount*x where id = [id of the row
>when it's
>callback was triggered];
>
>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?
>
>
>On 20 November 2017 at 17:12, 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 artificially bestow order to the data
>itself (as
>> opposed to the eventual output) then you are doing something that's
>very
>> bad in database design.
>>
>> To be specific, if the fruit in your DB needs ORDER as a property,
>best is
>> to add a column called  SortOrder or FruitOrder or the like. In
>this column
>> you can then assign the values automatically in steps of 10 or 100,
>so you
>> end up with a table like:
>> id  |  fruit  | SortOrder
>> 1  |  Apple  |  100
>> 2  |  Pear  |  200
>> 3  |  Kiwi  |  300  etc...
>>
>>
>> Then inserting:
>> INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150);
>>
>> is simply trivial. (The 150 can be computed from splitting the
>difference
>> between the precedent and decedent). Non-Integer is best.
>>
>> Eventually though, you might need to do maintenance and reset the
>gaps or
>> such.
>>
>> Anyway, enough preaching - this query script will fix your
>Situation in
>> SQLite very fast:
>>
>> UPDATE fruit SET id = -id-1 WHERE id >= 2;
>> UPDATE fruit SET id = -id WHERE id < 0;
>> INSERT INTO fruit (2,'Banana');
>>
>>
>> Another way:
>>
>> UPDATE fruit SET id = -(id * 100);
>> UPDATE fruit SET id = -id WHERE id < 0;
>> INSERT INTO fruit (150,'Banana');
>>
>>
>> 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 has the fruit's primary keys
>shuffled,
>> the next day will see some really weird recipes when Banana ends up
>where
>> Pear was intended.  Next you'll want to insert Watermelon...  :)
>>
>> Cheers,
>> Ryan
>>
>>
>> On 2017/11/19 10:37 PM, Shane Dev wrote:
>>
>>> 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 of the ID field?
>>>
>>> desired result -
>>>
>>> sqlite> select * from fruit;
>>> 1|apple
>>> 2|banana
>>> 3|pear
>>> 4|kiwi
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

[Kniep Stefan (CM/ESN3)]

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

This works fine as long as TRUNCATE checkpoints are issued from within my code. 
However, since the amount of data that is written into the DB at each shutdown 
varies, it is suboptimal to just trigger the checkpoint at each n-th shutdown. 
To get a better trade-off between the amount of written data and WAL size, it 
would be nice to trigger the checkpoints based on the size of the WAL. It seems 
that PRAGMA journal_size_limit doesn't help here: the WAL file grows infinitely 
when no checkpoints are issued from within my code. Is this the expected 
behavior (I guess so) or a configuration problem?

Note that PRAGMA wal_autocheckpoint also doesn't help, but this is pretty 
obvious because it only triggers a PASSIVE checkpoint. I would rather not 
access the WAL file directly (bypassing SQLite) for checking it's size. Does 
anybody have a proper solution for this problem?

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 not sure that my change doesn't break 
anything.
--
Stefan Kniep

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

Re: [sqlite] Energy consumption of SQLite queries

[Ali Dorri]

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 method incurred on the host node that is
running the program. I measured the time consumed by measuring the time
difference before and after running the query and I need to do the same
(somehow) for the energy consumption. I just need to give a rough
estimation. Of course, the exact values depend on many options, but I need
to show something.

Hope this helps.

Many thanks
Ali

On Tue, Nov 21, 2017 at 10:26 AM, R Smith  wrote:

> 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 and the
>> program, from the time I generated the query till the query is finished
>> and
>> control returns back to my program.
>>
>> Any help is highly appreciated.
>>
>
> I'm sure you have a good reason to want to do this, but I think it's not a
> worthy pursuit.
>
> It's a bit like trying to measure the amount of Energy a road uses. The
> answer is simple: It depends very much on which vehicles drive on it. The
> road doesn't use energy, it's just a venue or method used by machines who
> themselves consume the energy based on how efficient THEY are. Sure a
> longer or more curvy road will require more energy than a shorter road, so
> you can get COMPARATIVE or scaled energy ratios for different length roads
> for similar vehicles perhaps, but in the end quantifying that "road A uses
> X energy" will be patently false.
>
> Similarly, SQLite's energy usage depends on the TDP and energy
> coefficients of the machine it is running on.
>
> If you want to know how SQLite stacks up against another database or
> storage mechanism (possibly in terms of who uses more battery power from
> the phone it runs on) then you can simply measure the cycles/FPO's of both
> test subjects and there's programs out there to do that with for every
> platform. (SQLite will likely beat anything else by a mile for equal data
> handling ops).
>
> This will give you a great ratio of energy usage against whatever else it
> is measured, but measuring quantified physical energy usage is merely an
> arbitration of the machine it is running on, it has no intrinsic value as a
> stand-alone figure.
>
>
> Tell us more of your specific goals, maybe someone here has a way...
>
> Cheers,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Energy consumption of SQLite queries

[R Smith]

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 and the
program, from the time I generated the query till the query is finished and
control returns back to my program.

Any help is highly appreciated.


I'm sure you have a good reason to want to do this, but I think it's not 
a worthy pursuit.


It's a bit like trying to measure the amount of Energy a road uses. The 
answer is simple: It depends very much on which vehicles drive on it. 
The road doesn't use energy, it's just a venue or method used by 
machines who themselves consume the energy based on how efficient THEY 
are. Sure a longer or more curvy road will require more energy than a 
shorter road, so you can get COMPARATIVE or scaled energy ratios for 
different length roads for similar vehicles perhaps, but in the end 
quantifying that "road A uses X energy" will be patently false.


Similarly, SQLite's energy usage depends on the TDP and energy 
coefficients of the machine it is running on.


If you want to know how SQLite stacks up against another database or 
storage mechanism (possibly in terms of who uses more battery power from 
the phone it runs on) then you can simply measure the cycles/FPO's of 
both test subjects and there's programs out there to do that with for 
every platform. (SQLite will likely beat anything else by a mile for 
equal data handling ops).


This will give you a great ratio of energy usage against whatever else 
it is measured, but measuring quantified physical energy usage is merely 
an arbitration of the machine it is running on, it has no intrinsic 
value as a stand-alone figure.



Tell us more of your specific goals, maybe someone here has a way...

Cheers,
Ryan

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

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

[petern]

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 fruit_ins
BEGIN
  UPDATE fruit SET id = -id-1 WHERE id >= NEW.id;
  UPDATE fruit SET id = -id WHERE id < 0;
  INSERT INTO fruit VALUES (NEW.id,NEW.fruit);
END;

INSERT INTO fruit_ins VALUES (2,'Banana');

[Caveat: don't use intentional negative Id's in production without revising
this code first!]



On Mon, Nov 20, 2017 at 1: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,
> as you suggested, the gaps would start to disappear after many inserts
> between existing records. I suppose the gaps could be reset by writing
> program to call sqlite3_exec with
>
> select * from fruit order by SortOrder;
>
> and increment a RowCount variable each time the callback was triggered,
> then update fruit with something like
>
> update fruit set SortOrder = RowCount*x where id = [id of the row when it's
> callback was triggered];
>
> 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?
>
>
> On 20 November 2017 at 17:12, 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 artificially bestow order to the data itself
> (as
> > opposed to the eventual output) then you are doing something that's very
> > bad in database design.
> >
> > To be specific, if the fruit in your DB needs ORDER as a property, best
> is
> > to add a column called  SortOrder or FruitOrder or the like. In this
> column
> > you can then assign the values automatically in steps of 10 or 100, so
> you
> > end up with a table like:
> > id  |  fruit  | SortOrder
> > 1  |  Apple  |  100
> > 2  |  Pear  |  200
> > 3  |  Kiwi  |  300  etc...
> >
> >
> > Then inserting:
> > INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150);
> >
> > is simply trivial. (The 150 can be computed from splitting the difference
> > between the precedent and decedent). Non-Integer is best.
> >
> > Eventually though, you might need to do maintenance and reset the gaps or
> > such.
> >
> > Anyway, enough preaching - this query script will fix your Situation in
> > SQLite very fast:
> >
> > UPDATE fruit SET id = -id-1 WHERE id >= 2;
> > UPDATE fruit SET id = -id WHERE id < 0;
> > INSERT INTO fruit (2,'Banana');
> >
> >
> > Another way:
> >
> > UPDATE fruit SET id = -(id * 100);
> > UPDATE fruit SET id = -id WHERE id < 0;
> > INSERT INTO fruit (150,'Banana');
> >
> >
> > 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 has the fruit's primary keys
> shuffled,
> > the next day will see some really weird recipes when Banana ends up where
> > Pear was intended.  Next you'll want to insert Watermelon...  :)
> >
> > Cheers,
> > Ryan
> >
> >
> > On 2017/11/19 10:37 PM, Shane Dev wrote:
> >
> >> 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 of the ID field?
> >>
> >> desired result -
> >>
> >> sqlite> select * from fruit;
> >> 1|apple
> >> 2|banana
> >> 3|pear
> >> 4|kiwi
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Energy consumption of SQLite queries

[Ali Dorri]

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 and the
program, from the time I generated the query till the query is finished and
control returns back to my program.

Any help is highly appreciated.

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

Re: [sqlite] WAL mode with readers and writers

[Simon Slavin]

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 access to a database must be done 
inside a  transaction.  If SQLite was being stuffy and uncooperative if you 
tried

SELECT * FROM table

without declaring a transaction, you would get an error back telling you you 
did it wrong.  What you would be expected to do would be

BEGIN;
SELECT * FROM table;
COMMIT;

Operations on the database by commands like SELECT and INSERT are minimal.  
It’s the COMMIT which does all the hard stuff.  Locks start off as a READ lock, 
and are promoted to a WRITE lock the first time they try to do any writing.

However, SQLite is polite and convenient and if it sees you’ve forgotten to 
open a transaction, it opens one for you, and closes it as soon as possible, to 
let other connections get a lock as soon as possible.  Within your code above 
this would be

sqlite3_exec("BEGIN");
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);
}
sqlite3_exec("COMMIT");

The COMMIT cannot go earlier because until you have received your SQLITE_DONE 
from the loop you are still executing your SELECT statement.

Reviewing your proposed procedure knowing the above you can see that you are 
still maintaining a write lock on the database even between UPDATEs, because 
the transaction which contains them all is still running.

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

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

[R Smith]

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 
subquery

-- to not recompute and causes a temp index on id.
)
UPDATE fruit SET SortOrder = (SELECT norder FROM NewOrder WHERE nid = 
fruit.id);




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

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

[R Smith]

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,
as you suggested, the gaps would start to disappear after many inserts
between existing records. I suppose the gaps could be reset by writing
program to call sqlite3_exec with

select * from fruit order by SortOrder;

and increment a RowCount variable each time the callback was triggered,
then update fruit with something like

update fruit set SortOrder = RowCount*x where id = [id of the row when it's
callback was triggered];


Well this would not work because the id is no longer in-step with the 
ordering, in stead the SortOrder column controls ordering, but it may be 
completely different to the id (which is precisely why we needed it to 
start with, since we don't wish to jiggle the id around to adjust the 
order).



I would to prefer to avoid this solution because it involves mutable state
(the RowCount variable) which is the "root of all evil" (bugs).


Agreed.


  Is there an
SQL statement which could reset the gaps back to x?


It just so happens there is. :)

UPDATE fruit SET SortOrder = (SELECT COUNT(*)*100 FROM fruit AS F WHERE 
F.id < fruit.id);


This will reset the SortOrder indices in steps of 100 from 0 to (n*100) 
where n is the last record ordinal in the list.


I just picked 100 as a thumbsuck, you can of course use anything from 1 
to approaching the 64-bit integer limit, but probably 100, 1000 or 1 
will do, depending on how often you foresee ordered inserts happening. 
Also, it's perfectly OK to use Floating point values here, so you can 
keep inserting even after exhausting the integer divisible limit.



Cheers!
Ryan


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

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

[Simon Slavin]

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 written, the thing you’re trying to do is difficult.  This is 
because there is not advantage to doing it in SQL.

Why store integers at all ?  If you want to maintain your own order using an in 
insertion list maintain a REAL field instead and do

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

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.  When 
you want your fruit list produced in the right order, do

SELECT name FROM fruits ORDER BY orderNumber

and you’ll get your list in the right order.  It doesn’t matter that the 
integers aren’t stored anywhere.

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

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

[Shane Dev]

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 disappear after many inserts
between existing records. I suppose the gaps could be reset by writing
program to call sqlite3_exec with

select * from fruit order by SortOrder;

and increment a RowCount variable each time the callback was triggered,
then update fruit with something like

update fruit set SortOrder = RowCount*x where id = [id of the row when it's
callback was triggered];

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?


On 20 November 2017 at 17:12, 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 artificially bestow order to the data itself (as
> opposed to the eventual output) then you are doing something that's very
> bad in database design.
>
> To be specific, if the fruit in your DB needs ORDER as a property, best is
> to add a column called  SortOrder or FruitOrder or the like. In this column
> you can then assign the values automatically in steps of 10 or 100, so you
> end up with a table like:
> id  |  fruit  | SortOrder
> 1  |  Apple  |  100
> 2  |  Pear  |  200
> 3  |  Kiwi  |  300  etc...
>
>
> Then inserting:
> INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150);
>
> is simply trivial. (The 150 can be computed from splitting the difference
> between the precedent and decedent). Non-Integer is best.
>
> Eventually though, you might need to do maintenance and reset the gaps or
> such.
>
> Anyway, enough preaching - this query script will fix your Situation in
> SQLite very fast:
>
> UPDATE fruit SET id = -id-1 WHERE id >= 2;
> UPDATE fruit SET id = -id WHERE id < 0;
> INSERT INTO fruit (2,'Banana');
>
>
> Another way:
>
> UPDATE fruit SET id = -(id * 100);
> UPDATE fruit SET id = -id WHERE id < 0;
> INSERT INTO fruit (150,'Banana');
>
>
> 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 has the fruit's primary keys shuffled,
> the next day will see some really weird recipes when Banana ends up where
> Pear was intended.  Next you'll want to insert Watermelon...  :)
>
> Cheers,
> Ryan
>
>
> On 2017/11/19 10:37 PM, Shane Dev wrote:
>
>> 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 of the ID field?
>>
>> desired result -
>>
>> sqlite> select * from fruit;
>> 1|apple
>> 2|banana
>> 3|pear
>> 4|kiwi
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Tony Papadimitriou]

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 into fruit values(2,'banana');

select * from fruit order by id;

-Original Message- 
From: Shane Dev

Sent: Sunday, November 19, 2017 10:37 PM
To: SQLite mailing list
Subject: [sqlite] how into insert row into middle of table with 
integerprimary 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 of the ID field?

desired result -

sqlite> select * from fruit;
1|apple
2|banana
3|pear
4|kiwi
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 


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

Re: [sqlite] WAL mode with readers and writers

[Keith Medcalf]

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

The issue with the test you have designed is that the first process that runs 
is within a transaction, and that transaction does not end until the "select" 
is finalized.

When the "select" is processed, the connection acquires a "read" lock on the 
database for the ENTIRE DURATION of the processing of the select statement 
(that is, until "no more rows" is returned or the statement otherwise reset).  
When you process the "update" statement on THIS VERY SELF-SAME CONNECTION, you 
have upgraded the existing lock from a READ lock to a WRITE lock.  Only ONE 
connection may have a write lock at any given time.  Since the lock is being 
held until the "select" statement is completed, NO OTHER CONNECTION can acquire 
a "write" lock on the database.

If you wish to test concurrency of updates then you must put the update in a 
DIFFERENT CONNECTION than the one that contains the select.  And specify a busy 
timeout since it is probable that your two processes will still attempt to 
acquire write locks at the same time.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Jim Dossey
>Sent: Monday, 20 November, 2017 12:37
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [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:
>
>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);
>}
>
>Basically it does a SELECT, then for each row found it does an UPDATE
>on
>that row to set some value.  I know there are better ways to do this
>in
>SQL - this is just a test of locking in WAL mode.  But it is a real
>situation that could happen in my application.
>
>If I run this process twice, the first instance will start generating
>SQLITE_BUSY errors on the UPDATE when the second instance starts
>up.  I
>thought in WAL mode you could intermix readers and writers.  Is this
>not
>the case?  The 2 read loops seem to work just fine.  It's just that
>the
>first process can no longer do UPDATE's when the second process
>starts.
>I tried compiling with -DSQLITE_THREADSAFE=1 but that did not
>help.  I
>suspect that in WAL mode you can have multiple readers but only 1
>writer.
>
>Broadcasters General Store, Inc. Disclaimer  -  This message contains
>confidential information and is intended only for the individual(s)
>named.  If you are not the named addressee you should not
>disseminate, distribute or copy this e-mail.  Please notify the
>sender immediately by e-mail if you have received this e-mail by
>mistake and delete this e-mail from your system.  If you are not the
>intended recipient you are notified that disclosing, copying,
>distributing or taking any action in reliance on the contents of this
>information is strictly prohibited.
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

[sqlite] WAL mode with readers and writers

[Jim Dossey]

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:


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

Basically it does a SELECT, then for each row found it does an UPDATE on 
that row to set some value.  I know there are better ways to do this in 
SQL - this is just a test of locking in WAL mode.  But it is a real 
situation that could happen in my application.


If I run this process twice, the first instance will start generating 
SQLITE_BUSY errors on the UPDATE when the second instance starts up.  I 
thought in WAL mode you could intermix readers and writers.  Is this not 
the case?  The 2 read loops seem to work just fine.  It's just that the 
first process can no longer do UPDATE's when the second process starts.  
I tried compiling with -DSQLITE_THREADSAFE=1 but that did not help.  I 
suspect that in WAL mode you can have multiple readers but only 1 writer.


Broadcasters General Store, Inc. Disclaimer  -  This message contains 
confidential information and is intended only for the individual(s) named.  If 
you are not the named addressee you should not disseminate, distribute or copy 
this e-mail.  Please notify the sender immediately by e-mail if you have 
received this e-mail by mistake and delete this e-mail from your system.  If 
you are not the intended recipient you are notified that disclosing, copying, 
distributing or taking any action in reliance on the contents of this 
information is strictly prohibited.

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

Re: [sqlite] "PRAGMA data_version" documentation wrong

[Dan Kennedy]

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 documentation 
makes it sound like this value changes every time a COMMIT is made to 
the database.  It could be that I just don't understand what it is 
supposed to do.  I'm new to sqlite and SQL in general, so I've been 
trying different things to see how they work.


Broadcasters General Store, Inc. Disclaimer  -  This message contains 
confidential information and is intended only for the individual(s) 
named.  If you are not the named addressee you should not disseminate, 
distribute or copy this e-mail.  Please notify the sender immediately 
by e-mail if you have received this e-mail by mistake and delete this 
e-mail from your system.  If you are not the intended recipient you 
are notified that disclosing, copying, distributing or taking any 
action in reliance on the contents of this information is strictly 
prohibited.


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



As well as Simon's answer, you might be interested in this thread from 
earlier this month:


http://sqlite.1065341.n5.nabble.com/Most-efficient-way-to-detect-on-disk-change-td98413.html

Dan.




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

Re: [sqlite] "PRAGMA data_version" documentation wrong

[Simon Slavin]

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 in WAL mode.  The documentation makes it sound like this 
> value changes every time a COMMIT is made to the database.

Not quite:



" The "PRAGMA data_version" value is unchanged for commits made on the same 
database connection. The behavior of "PRAGMA data_version" is the same for all 
database connections, including database connections in separate processes and 
shared cache database connections. "

The idea is that the version is not incremented every time a change is made.  
It is incremented one every time a particular connection makes any changes.

To test it out you would need to maintain two connections to the database file 
and alternate which one writes to the database.  You should be able to test 
this using two copies of the SQLite command-line tool running at the same time.

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

[sqlite] "PRAGMA data_version" documentation wrong

[Jim Dossey]

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 changes every time a COMMIT is made to the database.  It 
could be that I just don't understand what it is supposed to do.  I'm 
new to sqlite and SQL in general, so I've been trying different things 
to see how they work.


Broadcasters General Store, Inc. Disclaimer  -  This message contains 
confidential information and is intended only for the individual(s) named.  If 
you are not the named addressee you should not disseminate, distribute or copy 
this e-mail.  Please notify the sender immediately by e-mail if you have 
received this e-mail by mistake and delete this e-mail from your system.  If 
you are not the intended recipient you are notified that disclosing, copying, 
distributing or taking any action in reliance on the contents of this 
information is strictly prohibited.

___
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

[curmudgeon]

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] Get result of 'pragma foreign_keys' in c programme

[Jens Alfke]

> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[nomad]

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 now see the below explain output with its two Function0 optcodes
where I believe there should only be one.


 140   Insert 0 26   15bifcodes
05  
 141   Program12142  56program 
00  
 142   ResetCount 0 00 
00  
 143   OpenWrite  4 124  0 3   
00  
 144   OpenWrite  5 125  0 k(2,,)  
00  
 145   String80 68   0 deltas  
00  
 146   Function0  1 68   64nextval(1)  
01  
 147   NotNull64149  0 
00  
 148   Integer-164   0 
00  
 149   MustBeInt  6400 
00  
 150   String80 69   0 deltas  
00  
 151   Function0  1 69   65nextval(1)  
01  
 152   Copy   6570   0 
00  
 153   Param  8 66   0 
00  
 154   Copy   6671   0 
00  
 155   String80 67   0 update_project  
00  
 156   Copy   6772   0 
00  
 157   Affinity   6530 DDB 
00  
 158   Program60247  73program 
00  

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

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

[x]

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] SELECT result different after ANALYZE

[R Smith]

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_stat1 table of 
course), the Query works again.


Also note: When you leave the stat1 table be, and only close and re-open 
the DB (with a new connection instance), the Query still does not work, 
so the stat1 table's presence does have an effect.




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

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

[R Smith]

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 has the fruit's primary keys shuffled,
the next day will see some really weird recipes when Banana ends up where
Pear was intended.  Next you'll want to insert Watermelon...  :)

For this specific problem you have a FOREIGN KEY... ON UPDATE CASCADE.

But yes - this problem is very weird. Unless its some kind of
educational/home work

Thank you.


Oh, yes!, good thing Igor mentioned this.

IF you do have foreign keys on that primary key that CASCADEs updates or 
revert the children to NULL or such, that given query can be very 
devastating and/or Painfully slow.

Something to keep in mind.


___
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

[R Smith]

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.



Hope the point is useful,
Cheers


On 2017/11/20 5:39 PM, Ralf Junker wrote:
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):


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.


Assuming that SQL SELECTs should always return the same results 
regardless of optimization, I assume that this might be a bug in SQLite.


Tested with the SQLite 3.21.0 CLI on Windows.

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


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

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

[Igor Korot]

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 artificially bestow order to the data itself (as
> opposed to the eventual output) then you are doing something that's very bad
> in database design.
>
> To be specific, if the fruit in your DB needs ORDER as a property, best is
> to add a column called  SortOrder or FruitOrder or the like. In this column
> you can then assign the values automatically in steps of 10 or 100, so you
> end up with a table like:
> id  |  fruit  | SortOrder
> 1  |  Apple  |  100
> 2  |  Pear  |  200
> 3  |  Kiwi  |  300  etc...
>
>
> Then inserting:
> INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150);
>
> is simply trivial. (The 150 can be computed from splitting the difference
> between the precedent and decedent). Non-Integer is best.
>
> Eventually though, you might need to do maintenance and reset the gaps or
> such.
>
> Anyway, enough preaching - this query script will fix your Situation in
> SQLite very fast:
>
> UPDATE fruit SET id = -id-1 WHERE id >= 2;
> UPDATE fruit SET id = -id WHERE id < 0;
> INSERT INTO fruit (2,'Banana');
>
>
> Another way:
>
> UPDATE fruit SET id = -(id * 100);
> UPDATE fruit SET id = -id WHERE id < 0;
> INSERT INTO fruit (150,'Banana');
>
>
> 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 has the fruit's primary keys shuffled,
> the next day will see some really weird recipes when Banana ends up where
> Pear was intended.  Next you'll want to insert Watermelon...  :)

For this specific problem you have a FOREIGN KEY... ON UPDATE CASCADE.

But yes - this problem is very weird. Unless its some kind of
educational/home work

Thank you.

>
> Cheers,
> Ryan
>
>
> On 2017/11/19 10:37 PM, Shane Dev wrote:
>>
>> 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 of the ID field?
>>
>> desired result -
>>
>> sqlite> select * from fruit;
>> 1|apple
>> 2|banana
>> 3|pear
>> 4|kiwi
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT result different after ANALYZE

[David Raymond]

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

sqlite> .timer off

sqlite> .eqp off

sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|t1|t1|2|CREATE TABLE t1 (id integer primary key, t2_id integer)
index|t1_1|t1|738|CREATE INDEX t1_1 on t1  (t2_id asc)
table|t2|t2|1409|CREATE TABLE t2 (id integer primary key, a integer)
index|t2_1|t2|1423|CREATE INDEX t2_1 on t2 (a asc)

sqlite> pragma integrity_check;
integrity_check
ok

sqlite> .eqp full

sqlite> select distinct t2.a from t1 inner join t2 on t1.t2_id = t2.id where 
t1.t2_id <> -1;
--EQP-- 0,0,1,SCAN TABLE t2 USING COVERING INDEX t2_1
--EQP-- 0,1,0,SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?)
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 19000  Start at 19
1 Null   1 5 008  r[5]=NULL
2 OpenRead   3 1423  0 k(2,,) 00  root=1423 iDb=0; t2_1
3 OpenRead   4 738   0 k(2,,) 00  root=738 iDb=0; t1_1
4 Explain0 0 1 SCAN TABLE t2 USING COVERING INDEX t2_1  
00
5 Rewind 3 181 0  00
6   Explain0 1 0 SEARCH TABLE t1 USING COVERING INDEX 
t1_1 (t2_id=?)  00
7   IdxRowid   3 1 000  r[1]=rowid
8   SeekGE 4 171 1  00  key=r[1]
9 IdxGT  4 171 1  00  key=r[1]
10Column 4 0 200  r[2]=t1.t2_id
11Eq 3 162 (BINARY)   54  if r[2]==r[3] 
goto 16
12Column 3 0 400  r[4]=t2.a
13Eq 4 165 (BINARY)   80  if r[5]==r[4] 
goto 16
14Copy   4 5 000  r[5]=r[4]
15ResultRow  4 1 000  output=r[4]
16  Next   4 9 100
17Next   3 6 001
18Halt   0 0 000
19Transaction0 0 4 0  01  usesStmtJournal=0
20Integer-13 000  r[3]=-1
21Goto   0 1 000
1

sqlite> .eqp off

sqlite> analyze;

sqlite> select * from sqlite_stat1;
tbl|idx|stat
t2|t2_1|6152 6152
t1|t1_1|248503 41

sqlite> pragma integrity_check;
integrity_check
ok

sqlite> .eqp full

sqlite> select distinct t2.a from t1 inner join t2 on t1.t2_id = t2.id where 
t1.t2_id <> -1;
--EQP-- 0,0,1,SCAN TABLE t2 USING COVERING INDEX t2_1
--EQP-- 0,1,0,SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?)
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 22000  Start at 22
1 Null   1 5 008  r[5]=NULL
2 OpenRead   3 1423  0 k(2,,) 00  root=1423 iDb=0; t2_1
3 OpenRead   4 738   0 k(2,,) 00  root=738 iDb=0; t1_1
4 Explain0 0 1 SCAN TABLE t2 USING COVERING INDEX t2_1  
00
5 Rewind 3 211 0  00
6 Explain0 1 0 SEARCH TABLE t1 USING COVERING INDEX 
t1_1 (t2_id=?)  00
7 IdxRowid   3 1 000  r[1]=rowid
8 SeekGE 4 171 1  00  key=r[1]
9   IdxGT  4 171 1  00  key=r[1]
10  Column 4 0 200  r[2]=t1.t2_id
11  Eq 3 162 (BINARY)   54  if r[2]==r[3] 
goto 16
12  Column 3 0 400  r[4]=t2.a
13  Eq 4 165 (BINARY)   80  if r[5]==r[4] 
goto 16
14  Copy   4 5 000  r[5]=r[4]
15  ResultRow  4 1 000  output=r[4]
16Next   4 9 100
17Column 3 0 600  r[6]=
18SeekGT 3 216 1  00  key=r[6]
19  Goto   1 6 000
20Next   3 6 001
21Halt   0 

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

[R Smith]

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) then you are doing something 
that's very bad in database design.


To be specific, if the fruit in your DB needs ORDER as a property, best 
is to add a column called  SortOrder or FruitOrder or the like. In this 
column you can then assign the values automatically in steps of 10 or 
100, so you end up with a table like:

id  |  fruit  | SortOrder
1  |  Apple  |  100
2  |  Pear  |  200
3  |  Kiwi  |  300  etc...


Then inserting:
INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150);

is simply trivial. (The 150 can be computed from splitting the 
difference between the precedent and decedent). Non-Integer is best.


Eventually though, you might need to do maintenance and reset the gaps 
or such.


Anyway, enough preaching - this query script will fix your Situation in 
SQLite very fast:


UPDATE fruit SET id = -id-1 WHERE id >= 2;
UPDATE fruit SET id = -id WHERE id < 0;
INSERT INTO fruit (2,'Banana');


Another way:

UPDATE fruit SET id = -(id * 100);
UPDATE fruit SET id = -id WHERE id < 0;
INSERT INTO fruit (150,'Banana');


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 has the fruit's primary keys 
shuffled, the next day will see some really weird recipes when Banana 
ends up where Pear was intended.  Next you'll want to insert 
Watermelon...  :)


Cheers,
Ryan


On 2017/11/19 10:37 PM, Shane Dev wrote:

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 of the ID field?

desired result -

sqlite> select * from fruit;
1|apple
2|banana
3|pear
4|kiwi
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] SELECT result different after ANALYZE

[David Raymond]

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.

sqlite> .timer off

sqlite> .eqp off

sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|t1|t1|2|CREATE TABLE t1 (id integer primary key, t2_id integer)
index|t1_1|t1|738|CREATE INDEX t1_1 on t1  (t2_id asc)
table|t2|t2|1409|CREATE TABLE t2 (id integer primary key, a integer)
index|t2_1|t2|1423|CREATE INDEX t2_1 on t2 (a asc)

sqlite> pragma integrity_check;
integrity_check
ok

sqlite> .eqp full

sqlite> select distinct t2.a from t1 inner join t2 on t1.t2_id = t2.id where 
t1.t2_id <> -1;
--EQP-- 0,0,1,SCAN TABLE t2 USING COVERING INDEX t2_1
--EQP-- 0,1,0,SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?)
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 19000  Start at 19
1 Null   1 5 008  r[5]=NULL
2 OpenRead   3 1423  0 k(2,,) 00  root=1423 iDb=0; t2_1
3 OpenRead   4 738   0 k(2,,) 00  root=738 iDb=0; t1_1
4 Explain0 0 1 SCAN TABLE t2 USING COVERING INDEX t2_1  
00
5 Rewind 3 181 0  00
6   Explain0 1 0 SEARCH TABLE t1 USING COVERING INDEX 
t1_1 (t2_id=?)  00
7   IdxRowid   3 1 000  r[1]=rowid
8   SeekGE 4 171 1  00  key=r[1]
9 IdxGT  4 171 1  00  key=r[1]
10Column 4 0 200  r[2]=t1.t2_id
11Eq 3 162 (BINARY)   54  if r[2]==r[3] 
goto 16
12Column 3 0 400  r[4]=t2.a
13Eq 4 165 (BINARY)   80  if r[5]==r[4] 
goto 16
14Copy   4 5 000  r[5]=r[4]
15ResultRow  4 1 000  output=r[4]
16  Next   4 9 100
17Next   3 6 001
18Halt   0 0 000
19Transaction0 0 4 0  01  usesStmtJournal=0
20Integer-13 000  r[3]=-1
21Goto   0 1 000
1

sqlite> .eqp off

sqlite> analyze;

sqlite> select * from sqlite_stat1;
tbl|idx|stat
t2|t2_1|6152 6152
t1|t1_1|248503 41

sqlite> pragma integrity_check;
integrity_check
ok

sqlite> .eqp full

sqlite> select distinct t2.a from t1 inner join t2 on t1.t2_id = t2.id where 
t1.t2_id <> -1;
--EQP-- 0,0,1,SCAN TABLE t2 USING COVERING INDEX t2_1
--EQP-- 0,1,0,SEARCH TABLE t1 USING COVERING INDEX t1_1 (t2_id=?)
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 22000  Start at 22
1 Null   1 5 008  r[5]=NULL
2 OpenRead   3 1423  0 k(2,,) 00  root=1423 iDb=0; t2_1
3 OpenRead   4 738   0 k(2,,) 00  root=738 iDb=0; t1_1
4 Explain0 0 1 SCAN TABLE t2 USING COVERING INDEX t2_1  
00
5 Rewind 3 211 0  00
6 Explain0 1 0 SEARCH TABLE t1 USING COVERING INDEX 
t1_1 (t2_id=?)  00
7 IdxRowid   3 1 000  r[1]=rowid
8 SeekGE 4 171 1  00  key=r[1]
9   IdxGT  4 171 1  00  key=r[1]
10  Column 4 0 200  r[2]=t1.t2_id
11  Eq 3 162 (BINARY)   54  if r[2]==r[3] 
goto 16
12  Column 3 0 400  r[4]=t2.a
13  Eq 4 165 (BINARY)   80  if r[5]==r[4] 
goto 16
14  Copy   4 5 000  r[5]=r[4]
15  ResultRow  4 1 000  output=r[4]
16Next   4 9 100
17Column 3 0 600  r[6]=
18SeekGT 3 216 1  00  key=r[6]
19  Goto   1 6 000
20Next   3 6 001
21Halt   0 0 000
22Transaction0 0 5 0  01  usesStmtJournal=0
23Integer-13 000  r[3]=-1
24Goto   0 1 0   

Re: [sqlite] Help with left joins

[R Smith]

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 the SELECT variety) can have multiple 
redundancies or  indeed omit-able specified values. An optimization to  
ensure non-used left-joins on Primary keys are not used is probably a 
negligible improvement.


To answer your question: You are correct to assume the same output - 
There is no difference in the SET-Theory/Algebra producing those 
specific rows whether you omit the left joins or not, BUT that only 
holds true while the Indexes are indeed all PRIMARY (or at least Unique) 
and the joins are of the "LEFT" variety. Change any of those and the 
query output may look very different.


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

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

[rene]

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 values(@c1)}
set r [db last_insert_rowid]
# read data
set fd [db incrblob img b $r]
fconfigure $fd -translation binary
set c2 [read $fd]
close $fd




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

[sqlite] SELECT result different after ANALYZE

[Ralf Junker]

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


  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.


Assuming that SQL SELECTs should always return the same results 
regardless of optimization, I assume that this might be a bug in SQLite.


Tested with the SQLite 3.21.0 CLI on Windows.

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

[x]

>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 list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

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 proper to have 
constraint checks only at the end of an instruction, or only at the end of a 
transaction.

However you are right with respect to SQLite.  Which only increases the 
importantce of the second part of my post: why does OP want to do this ?  
Renumbering SQL id’s is rare.

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

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

[Peter Da Silva]

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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[David Raymond]

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 0.00 sys 0.00
Error: UNIQUE constraint failed: fruit.id


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Monday, November 20, 2017 9:39 AM
To: SQLite mailing list
Subject: Re: [sqlite] how into insert row into middle of table with integer 
primary key



UPDATE fruit SET id = id+1 WHERE id >=2;
INSERT …

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

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

[Clemens Ladisch]

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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] read/write binary data via tcl

[rene]

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]
close $fd
db eval {insert into img values(@c)}

2. read the image:

db eval {select * from img}

--> here I get back a string and not the original binary data?


Thank you
rene



--
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] how into insert row into middle of table with integer primary key

[Simon Slavin]

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 -
> 
> sqlite> select * from fruit;
> 1|apple
> 2|banana
> 3|pear
> 4|kiwi

UPDATE fruit SET id = id+1 WHERE id >=2;
INSERT …

But the real question you need to ask yourself is why you’re doing this.  ID 
numbers in a table are meant to be seen by computers, never humans.  Why does 
an ID number matter to you ?  Why aren’t you just inserting your new fruit 
after the end of the exiting fruits ?

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

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

[Simon Slavin]

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.



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

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

[Hick Gunter]

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 of the ids bordering the desired insert position, but this 
would still only allow a certain number of in-between inserts (more if they are 
truly random, with the extreme case being ordered inserts after an initial 
load).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Shane Dev
Gesendet: Sonntag, 19. November 2017 21:37
An: SQLite mailing list 
Betreff: [EXTERNAL] [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 of the ID field?

desired result -

sqlite> select * from fruit;
1|apple
2|banana
3|pear
4|kiwi
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Clemens Ladisch]

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

Why don't you compute the order dynamically?


Regards,
Clemens
___
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

[David Raymond]

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, November 20, 2017 8:22 AM
To: SQLite mailing list
Subject: 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 joins. Put it this way 
will



select ColA from TblA
left join TblB using (ColB)
left join TblC using (ColC)
left join TblD using (ColD)
where ColCX=?;



return the same result set as



select ColA from TblA
left join TblC using (ColC)
where ColCX=?;



even though the explains are different?




From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Monday, November 20, 2017 12:34:29 PM
To: SQLite mailing list
Subject: 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 asked for columns from the table TblC, you have specified 
the lookup.  Perhaps you want to check that an appropriate row appears in TblC, 
and get an error message otherwise.  (Or should SQL return NIL ?)

There is an optimization opportunity for SQLite.  Maybe it should check to see 
that a JOIN is needed before it does it.  But at the moment this command is 
just a little slower than it should be, not exactly wrong.

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

Re: [sqlite] Help with left joins

[x]

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 joins. Put it this way 
will



select ColA from TblA
left join TblB using (ColB)
left join TblC using (ColC)
left join TblD using (ColD)
where ColCX=?;



return the same result set as



select ColA from TblA
left join TblC using (ColC)
where ColCX=?;



even though the explains are different?




From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Monday, November 20, 2017 12:34:29 PM
To: SQLite mailing list
Subject: 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 asked for columns from the table TblC, you have specified 
the lookup.  Perhaps you want to check that an appropriate row appears in TblC, 
and get an error message otherwise.  (Or should SQL return NIL ?)

There is an optimization opportunity for SQLite.  Maybe it should check to see 
that a JOIN is needed before it does it.  But at the moment this command is 
just a little slower than it should be, not exactly wrong.

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

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

[nomad]

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(
change_id INTEGER NOT NULL,
id INTEGER NOT NULL,
name VARCHAR(40),
parent_id INTEGER,
project_status_id INTEGER,
title VARCHAR
);

CREATE TRIGGER
func_update_project_ai_1
AFTER INSERT ON
func_update_project
FOR EACH ROW
BEGIN

--SELECT debug(
--' change_id: ' || NEW.change_id,
--' id: ' || NEW.id,
--' name: ' || COALESCE(NEW.name,''),
--' parent_id: ' || COALESCE(NEW.parent_id,''),
--' project_status_id: ' || COALESCE(NEW.project_status_id,''),
--' title: ' || COALESCE(NEW.title,'')
--);

SELECT
RAISE(ABORT, 'Bif::Error::InvalidName')
WHERE
CAST(NEW.name AS INTEGER) = NEW.name
;

UPDATE
bifcodes
SET
bifcode = bifcode || (
SELECT
'{U1:_,U14:update_project,'
|| 'U4:name,' ||
CASE WHEN
NEW.name IS NOT NULL
THEN
printf('U%d:%s,', LENGTH(CAST(NEW.name AS BLOB)),
CAST(NEW.name AS BLOB))
ELSE
'~'
END
|| 'U11:parent_uuid,' ||
CASE WHEN
pp.uuid IS NOT NULL
THEN
printf('U%d:%s,', LENGTH(CAST(pp.uuid AS BLOB)),
CAST(pp.uuid AS BLOB))
ELSE
'~'
END
|| 'U19:project_status_uuid,' ||
CASE WHEN
ps.uuid IS NOT NULL
THEN
printf('U%d:%s,', LENGTH(CAST(ps.uuid AS BLOB)),
CAST(ps.uuid AS BLOB))
ELSE
'~'
END
|| 'U5:title,' ||
CASE WHEN
NEW.title IS NOT NULL
THEN
printf('U%d:%s,', LENGTH(CAST(NEW.title AS BLOB)),
CAST(NEW.title AS BLOB))
ELSE
'~'
END
|| 'U4:uuid,'
|| printf('U%d:%s,', LENGTH(p.uuid), p.uuid)
|| '}'
FROM
nodes p
LEFT JOIN
nodes pp
ON
pp.id = NEW.parent_id
LEFT JOIN
nodes ps
ON
ps.id = NEW.project_status_id
WHERE
p.id = NEW.id
)
WHERE
change_id = NEW.change_id
;


INSERT INTO deltas(
id,
change_id,
function
)
SELECT
nextval('deltas'),
NEW.change_id,
'update_project'
;


INSERT INTO
node_deltas(
delta_id,
change_id,
node_id,
parent_id,
name
)
SELECT
currval('deltas'),
NEW.change_id,
NEW.id,
NEW.parent_id,
NEW.name
;


INSERT INTO
project_deltas(
delta_id,
change_id,
project_id,
title,
project_status_id
)
SELECT
currval('deltas'),
NEW.change_id,
NEW.id,
NEW.title,
NEW.project_status_id
WHERE
COALESCE(NEW.title,NEW.project_status_id) IS NOT NULL
;

DELETE FROM func_update_project;
END;

The udf that is called twice is the "nextval()" function.

The insert is straightfoward:

INSERT INTO
func_update_project(
change_id,
id,
project_status_id,
title
)
VALUES
(
49,
3,
NULL,
NULL
)
;

I can't use .explain from the sqlite command-line on my database
because of the UDFs, but I can print the formatted output of the
explain command. The VALUES case and the SELECT 

[sqlite] [OT] Updating sqlite in PHP windows

[Eduardo]

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
and FTS5 extensions. Almost all answers I get on other sources are, "don't use
sqlite3, use this 'other cool technology (TM)' ". The only valid answer I found
is recompile PHP with last sqlite3 version, using VC11 compiler (others won't
work). 

I tried to compile with VS2015 but the dll don't work with PHP5.6 (php5.6
doesn't detect/show sqlite3 in phpinfo). The dll works with other apps dynamic
linking to it.

cl sqlite3.c -O2 -DDSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -link -dll 
-out:sqlite3.dll

Does anyone have a modern sqlite3.dll for PHP 5.6 with JSON and FTS5 extensions
on? Is there any paraoficial safe site where I can download it? 

Or better, a recipe that works to compile sqlite3 on php5.6.x?

Thanks 

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

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

[Shane Dev]

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 of the ID field?

desired result -

sqlite> select * from fruit;
1|apple
2|banana
3|pear
4|kiwi
___
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

[Simon Slavin]

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 asked for columns from the table TblC, you have specified 
the lookup.  Perhaps you want to check that an appropriate row appears in TblC, 
and get an error message otherwise.  (Or should SQL return NIL ?)

There is an optimization opportunity for SQLite.  Maybe it should check to see 
that a JOIN is needed before it does it.  But at the moment this command is 
just a little slower than it should be, not exactly wrong.

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

[sqlite] Help with left joins

[x]

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 tables for TblA).

If I run the following query

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.

If i run the following query

explain
select ColA from TblA
left join TblB using (ColB)
left join TblC using (ColC)
left join TblD using (ColD)
where ColCX=?;

there will be no trace of TblD as it's redundant but what I need explaining is 
why is TblB included in the explain.

I'm guessing that the sqlite query optimiser finds it easy to ignore trailing 
redundant tables but checking for other redundant tables is too expensive. Is 
that the case or am I missing something and the inclusion of TblB is necessary 
to get the correct result?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

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 [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von no...@null.net
Gesendet: Montag, 20. November 2017 11:53
An: SQLite mailing list 
Betreff: [EXTERNAL] [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(
udf()
);

END;

What I am seeing is that for a row inserted into table y the udf() is called 
*twice*. This behaviour only seems to occur with the INSERT/VALUES combination. 
If I change the trigger contents to be the following then the udf is only 
called once:

INSERT INTO
y(id)
SELECT
udf()
;

I'm having a hard time duplicating this with a standalone test case, and I'm 
also having difficulty with EXPLAIN QUERY PLAN returning no data for some 
statements... so I have a bit of a rabbit hole to explore before I can come 
back with more information. But perhaps someone else knows what might be going 
on?

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[nomad]

[ 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(
udf()
);

END;

What I am seeing is that for a row inserted into table y the udf() is
called *twice*. This behaviour only seems to occur with the
INSERT/VALUES combination. If I change the trigger contents to be the
following then the udf is only called once:

INSERT INTO
y(id)
SELECT
udf()
;

I'm having a hard time duplicating this with a standalone test case,
and I'm also having difficulty with EXPLAIN QUERY PLAN returning no
data for some statements... so I have a bit of a rabbit hole to explore
before I can come back with more information. But perhaps someone else 
knows what might be going on?

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