Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0

[Hick Gunter]

The behaviour does not need to match what you think of as consistent.

The only way to force a certain column name is with the AS clause *on the 
outermost statement*. Otherwise, the column name is implementation defined and 
may change between releases. You should not be relying on column names other 
than those you explicitly set using the AS clause. This is a common mistake.

If you really need the column names, then just CREATE TABLE first (this gives 
you defined column names AND declared data types) and the INSERT INTO ... 
SELECT later (which ignores the generated column names from the select 
statement).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Radovan Antloga
Gesendet: Donnerstag, 21. Dezember 2017 16:35
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field alias in 
3.21.0

Behaviour is not consistent when using
create table as
or just select statement.
Try this simple test.

create table test(a int, b int);
insert into test values (1, 1);

select d from (select c as d from (select a as c from test));

you get column name d as expected
but when you have create table as statement

create table test2 as
select d from (select c as d from (select a as c from test));

you get table test2 with column name a.

If you change to this

create table test2 as
select d as d from (select c as d from (select a as c from test));

you will get name correct. I think it should be the same as when just using 
select statement.

Best Regards
Radovan


select a from (select b from (select c from test)))

Richard Hipp je 21.12.2017 ob 14:52 napisal:
> The behavior change is a bug fix.  See
> http://sqlite.org/src/info/de3403bf5ae for details.
>
> On 12/21/17, Radovan Antloga  wrote:
>> I have table (create statement):
>>
>> CREATE TABLE SOPP1 (
>> STAT  varchar(1) collate systemnocase,
>> RID  varchar(2) collate systemnocase,
>> VP  integer,
>> BLANK  varchar(6) collate systemnocase,
>> NAZIV  varchar(24) collate systemnocase,
>> KN  varchar(12) collate systemnocase,
>> A  varchar(1) collate systemnocase,
>> B  varchar(1) collate systemnocase,
>> RACUN  varchar(1) collate systemnocase,
>> URE  varchar(1) collate systemnocase,
>> ZN  varchar(1) collate systemnocase,
>> TOCKE  varchar(1) collate systemnocase,
>> PRC  varchar(1) collate systemnocase,
>> UP  varchar(1) collate systemnocase,
>> IZPIS  varchar(1) collate systemnocase,
>> D  varchar(1) collate systemnocase,
>> F2U  varchar(1) collate systemnocase,
>> F2O  varchar(1) collate systemnocase,
>> F2T  varchar(1) collate systemnocase,
>> F2Z  varchar(1) collate systemnocase,
>> F2P_1  integer,
>> F2P_2  integer,
>> F2P_3  integer,
>> F5  varchar(1) collate systemnocase,
>> AJPES  varchar(1) collate systemnocase,
>> ZZ  integer,
>> VD  integer,
>> NS  integer,
>> MES  integer,
>> NORURE  varchar(1) collate systemnocase,
>> G  varchar(1) collate systemnocase,
>> E  varchar(1) collate systemnocase,
>> H  varchar(1) collate systemnocase,
>> I  varchar(1) collate systemnocase,
>> J  varchar(1) collate systemnocase,
>> SM  varchar(1) collate systemnocase,
>> NO  varchar(1) collate systemnocase,
>> PRIO  varchar(1) collate systemnocase,
>> V_1  varchar(1) collate systemnocase,
>> V_2  varchar(1) collate systemnocase,
>> V_3  varchar(1) collate systemnocase,
>> V_4  varchar(1) collate systemnocase,
>> V_5  varchar(1) collate systemnocase,
>> V_6  varchar(1) collate systemnocase,
>> V_7  varchar(1) collate systemnocase,
>> V_8  varchar(1) collate systemnocase,
>> V_9  varchar(1) collate systemnocase,
>> V_10  varchar(1) collate systemnocase,
>> V_11  varchar(1) collate systemnocase,
>> V_12  varchar(1) collate systemnocase,
>> FOR  integer,
>> P_1  integer,
>> P_2  integer,
>> P_3  integer,
>> P_4  integer,
>> P_5  integer,
>> P_6  integer,
>> primary key (RID, VP, BLANK));
>>
>> When I create new table using this sql:
>>
>> drop table if exists WM4P;
>> create table WM4P as
>> select
>> P, A, B, AB, U, H, ZZ,
>> case
>>   when AB in ('7') then 99
>>   when AB in ('57', '58', '59', '5M') then null
>>   when AB = '56' and ZZ = 12 then 01
>>   when AB = '56' then 02
>>   when A = '3' then 03
>>   when AB in ('1M') then 08
>>   when AB in ('10') then 07
>>   when AB in ('12') then null
>>   when A in ('1', '5') and H = '1' then 02
>>   when A in ('5') then 02
>>   when A in ('1') then 01
>> end as M4_OP
>> from (
>> select
>>   VP as P, ifnull(A,'') as A, ifnull(B,'') as B,
>> ifnull(A,'')||ifnull(B,'') as AB,
>>   ifnull(URE,'') as U, ifnull(H,'') as H, ZZ
>> from SOPP1
>> );
>>
>> You will see that first column 

Re: [sqlite] Btree page corruption

[Rowan Worth]

Does either process take backups of the DB? If so, how is that implemented?
-Rowan

On 22 December 2017 at 05:47, Nikhil Deshpande 
wrote:

> Hi,
>
> We have an application that in a Linux VM that's running into
> SQLite DB corruption (after weeks and months of running,
> 4 such instances yet in different VMs).
>
> We would appreciate some help in debugging this further to identify
> source of corruption!
>
> Symptom is btree page corruption, e.g.
>
> > $ sqlite3 stats.sqlite "pragma integrity_check;"
> > *** in database main ***
> > Page 3818: btreeInitPage() returns error code 11
> > Page 46: btreeInitPage() returns error code 11
> > Error: database disk image is malformed
> (Same error is raised for SELECT queries too.)
>
> There were no power-off or reboots in near time vicinity when the
> corruption was detected. We have poured over this document
> https://sqlite.org/howtocorrupt.html
> many times to check if any of the conditions could apply,
> but so far no leads yet.
>
> We have also been unable to reproduce the corruption by stressing
> application's SQLite DB read/write code paths for a week.
>
> I'm attaching showdb output for the DB header and 2 corrupt pages
> if it's of any hint.
>
> ---
>
> A bit more application setup context/information:
>
> - Linux kernel 4.4.41
> - glibc 2.22
> - Ext4 file system, mounted as (rw,relatime,data=ordered).
>
> - Writer C++ process: sqlite-3.17
>   - Creates a set of "time series" tables, each table has 2 numeric
> columns (timestamp, int) during initialization.
>   - Every 1 minute, 2 threads will do total 15 writes. (using "INSERT OR
> REPLACE ... (timestamp, int)" SQL into 15 tables).
>   - SQLite DB opened with SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
> flags, initialized with "PRAGMA journal_mode=wal;", threading mode
> is Serialized for the libsqlite build, uses default VFS ("unix").
> All other config params are default (e.g. autovacuum is disabled
> etc.).
>   - A separate thread runs "PRAGMA quick_check;" periodically every 5
> minutes, in its own separate DB connection.
> - Reader process: sqlite-3.11 + Python 2.7.11
>   - Periodically reads time series tables for a given timestamp range
> (usually latest 5 minutes) using SELECT queries (no INSERT/UPDATE/
> DELETE from this process).
>   - Uses same same "PRAGMA journal_mode=wal", uses the sqlite3 DBAPI
> module from Python standard library.
> Apart from above 2, no other processes are accessing the SQLite DB file.
>
> We have updated both the reader and writer to use latest SQLite 3.21,
> but without understanding the cause of corruption, we are unable to
> say if this update to latest 3.21 would indeed prevent further
> occurrences.
>
> Thanks,
>  Nikhil
>
> ___
> 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] Minor bug reports during build.

[Rowan Worth]

Seems to be working as advertised. Unless you specify --disable-tcl, the
configure script defaults to building an sqlite extension for TCL.

The extension can't go in $PREFIX, since tcl wouldn't be able to find it.
So your options for a non-root install are:

1) --disable-tcl
2) set the environment variable TCLLIBDIR to somewhere you have write
access (presumably you'll also need to configure tcl to look here for
extensions)
3) install your own tcl to $PREFIX and make sure its tclsh is the first one
in your $PATH

-Rowan

On 22 December 2017 at 03:41, Michael Tiernan 
wrote:

> Sorry for the top post. Sadly the android client forces it.
>
> In short, I'm building two copies of sqlite3, one of which works fine, the
> attempt to build it on the Linux host (using the prefix flag of course)
> causes the build to begin but it to fail when it runs into the attempt at
> modifying the (non-existent) file /usr/share/tcl8.5/sqlite3
>
> All the other warnings and considerations are secondary to the point that
> the makefile is attempting to change the permissions on an external (to the
> user) tool which it neither built or should be able to modify.
>
> Thanks for everyone's time!
> --
> << MCT >>   Michael C Tiernan.http://www.linkedin.com/in/mtiernan
>
> Non Impediti Ratione Cogatationis
> Women and cats will do as they please, and
> men and dogs should relax and get used to the idea. -Robert A. Heinlein
>
> On Dec 21, 2017 2:26 PM, "Warren Young"  wrote:
>
> On Dec 21, 2017, at 11:37 AM, Michael Tiernan 
> wrote:
> >
> > I'm trying to build two copes of sqlite3 in a shared dropbox folder.
>
> Do you intend to use SQLite inside the Dropbox folder once you’ve got it
> working?  That’s only safe if only one person is using the database at a
> time, and you wait for the sync to finish before trying to use the DB on
> another machine.
>
> If you need a networked DBMS, SQLite is generally not what you want, at
> least not as-stock.  There are add-ons and alternatives that work far
> better for this.  Google “SQLite Dropbox”.  It’s come up many times before.
>
> > On a "Scientific Linux 6.7" (RHEL 6.7) system I did a built then build
> > install *as a user* and not as root. Looking to create a localized copy
> > specifically.
>
> Try this:
>
> $ ./configure --prefix="$HOME/sqlite3"
>
> That will allow the “make install” to work without root privileges.  The
> sqlite3 binary would land in $HOME/sqlite3/bin, with that configuration
> option.
>
> You can set the prefix to somewhere under your Dropbox folder if you’re
> willing to take the risks to data safety that that entails.
> ___
> 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] generic advice for insert performance in-memory database optimization

[Keith Medcalf]

Seems constant to me.  I'd show you, but Windows has no protection for the OS 
when system memory is exhausted so the whole computer go kaboom.

However, memory size growth was constant, and insert time was pretty constant 
(which includes the overhead of generating random values etc).  Go boom when 
all RAM is full at 31 GB (about 140,000,000 records).  Running it again and 
getting it to stop at 120,000,000 inserts showed as follows:

>testinsert.py
sys.version_info(major=3, minor=6, micro=3, releaselevel='final', serial=0)
Row(journal_mode='off')
Row(journal_mode='off')
Row(synchronous=0)
insert into data values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);
   100 9.751389100 9.751389
   20019.518461100 9.767072
   30029.59586510010.077404
   40040.99131610011.395450
   50052.54307710011.551762
   60064.17803710011.634959
   70075.34782410011.169787
   80086.43735810011.089534
   90097.77255110011.335193
  1000   109.15486010011.382308
  1100   120.13210310010.977243
  1200   131.22160710011.089504
  1300   142.55583110011.334224
  1400   153.59523310011.039402
  1500   164.74991510011.154682
  1600   175.28296910010.533054
  1700   186.27226810010.989299
  1800   197.27456710011.002299
  1900   208.60876010011.334193
  2000   219.67623910011.067479
  2100   230.99538610011.319148
  2200   242.52007410011.524688
  2300   254.02981410011.509740
  2400   265.07122310011.041409
  2500   276.09455310011.023329
  2600   286.88632810010.791775
  2700   298.17556110011.289233
  2800   308.89110310010.715541
  2900   319.56553510010.674432
  3000   330.20086610010.635331
  3100   340.76500110010.564136
  3200   351.55374010010.788738
  3300   362.22115110010.667412
  3400   373.16830910010.947158
  3500   383.73308910010.564780
  3600   394.12501910010.391931
  3700   403.954467100 9.829448
  3800   414.15877110010.204304
  3900   424.097607100 9.938836
  4000   434.16162310010.064016
  4100   444.116177100 9.954554
  4200   454.41441410010.298238
  4300   464.47846810010.064053
  4400   474.77695610010.298488
  4500   485.00161010010.224653
  4600   495.14374810010.142138
  4700   505.129763100 9.986015
  4800   515.27182410010.142061
  4900   525.55441110010.282587
  5000   536.14961610010.595204
  5100   546.135350100 9.985735
  5200   556.40248110010.267131
  5300   566.91972010010.517239
  5400   578.04636810011.126648
  5500   588.99027410010.943906
  5600   599.98753610010.997262
  5700   610.82842410010.840888
  5800   621.32431910010.495896
  5900   632.00968810010.685369
  6000   642.56376910010.554081
  6100   653.04963910010.485870
  6200   664.20684110011.157203
  6300   674.79702410010.590183
  6400   685.52379710010.726773
  6500   696.85556210011.331765
  6600   708.15691810011.301356
  6700   718.99499910010.838081
  6800   729.72378810010.728789
  6900   740.64308610010.919299
  7000   751.14025310010.497167
  7100   761.66449110010.524239
  7200   772.48285810010.818367
  7300   783.01412110010.531263
  7400   794.43848610011.424365
  7500   805.44495110011.006465
  7600   816.03229310010.587342
  7700   826.51134710010.479054
  7800   837.07659910010.565252
  7900   847.52861310010.452014
  8000   857.83119410010.302581
  8100   868.28617810010.454984
  8200   878.76523710010.479059
  8300   889.11472410010.349487
  8400   900.05388610010.939162
  8500   909.930211100 9.876325
  8600   919.790808100 9.860597
  8700   929.651311100 9.860503
  8800   939.527506100 9.876195
  8900   949.356789100 9.829282
  9000   959.186170100 9.829381
  

[sqlite] Importing Text to Create a Table

[Lawrence Murphy]

I am supporting a website which aims to protect a forest from development.
The website produces an email for supporters to mail out. A copy of the
email is sent to our Gmail address and we wish to capture the supporters
return email address. Google provides an archive of our Gmail account which
is 458Mbs in size and contains a lot of superfluous data.

I have tried making a table with one column, text 255c in size and doing
the import but it takes more than overnight and is still running. Is there
a quicker way to import the data?

Warm Regards,
Lawrence

Mb: 0408 403 324
PO Box 263 Cherrybrook NSW 2126

Lose Weight, Gain Health & Prevent Disease
Find out how here
http://lwghpd.blogspot.com.au/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Btree page corruption

[Richard Hipp]

On 12/21/17, Nikhil Deshpande  wrote:
>
> There were no power-off or reboots in near time vicinity when the
> corruption was detected.

(1) Might the corruption have been sitting dormant due to some far
away power-off or reboot and was only recently discovered?  How much
do you trust the fsync() system call and the filesystems ability to
honor that system call on the VM on which this code is running?  Do
you run "PRAGMA quick_check" on initial power-up?

(2) Is this the only corruption instance you have seen?

(3) The showdb outputs you sent only shows me the first 8 bytes of the
page.  From those 8 bytes, it looks to be an overflow page that is
being used as a btree page.  Are there any further clues in the hex
dump of the bad pages?

(4) What does "showdb pgidx" look like?

(5) How many threads are accessing the database?  Are you sure that
you have the database set in a thread-safe mode?


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

Re: [sqlite] Btree page corruption

[Simon Slavin]

On 21 Dec 2017, at 9:47pm, Nikhil Deshpande  wrote:

> We have an application that in a Linux VM that's running into
> SQLite DB corruption (after weeks and months of running,
> 4 such instances yet in different VMs).
> 
> [snip]
> 
> There were no power-off or reboots in near time vicinity when the
> corruption was detected. We have poured over this document
> https://sqlite.org/howtocorrupt.html
> many times to check if any of the conditions could apply,
> but so far no leads yet.
> 
> We have also been unable to reproduce the corruption by stressing
> application's SQLite DB read/write code paths for a week.

Well done for finding the appropriate web page.  You’ve done all the easy stuff 
which leaves us with just the hard questions.  These won’t necessarily give us 
an answer, but they might remind one of your readers of something.

When "pragma integrity_check" detects an error, does "PRAGMA quick_check" do 
too ?

Is your database file really about 65 Meg in size ?  Just roughly.

How is your application terminated ?  User action or self-determination ?

Does your application call sqlite3_close() ?  Does it cbeck the code returned ?

Does your application call sqlite3_shutdown() ?  Does it cbeck the code 
returned ?

How does your VM get shutdown ?  Does it correctly shut down your application 
before it quits ?

Can you include a "pragma integrity_check" at startup ?
Can you include a "pragma integrity_check" executed at regular intervals ?

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

[sqlite] Btree page corruption

[Nikhil Deshpande]

Hi,

We have an application that in a Linux VM that's running into
SQLite DB corruption (after weeks and months of running,
4 such instances yet in different VMs).

We would appreciate some help in debugging this further to identify
source of corruption!

Symptom is btree page corruption, e.g.

> $ sqlite3 stats.sqlite "pragma integrity_check;"
> *** in database main ***
> Page 3818: btreeInitPage() returns error code 11
> Page 46: btreeInitPage() returns error code 11
> Error: database disk image is malformed
(Same error is raised for SELECT queries too.)

There were no power-off or reboots in near time vicinity when the
corruption was detected. We have poured over this document
https://sqlite.org/howtocorrupt.html
many times to check if any of the conditions could apply,
but so far no leads yet.

We have also been unable to reproduce the corruption by stressing
application's SQLite DB read/write code paths for a week.

I'm attaching showdb output for the DB header and 2 corrupt pages
if it's of any hint.

---

A bit more application setup context/information:

- Linux kernel 4.4.41
- glibc 2.22
- Ext4 file system, mounted as (rw,relatime,data=ordered).

- Writer C++ process: sqlite-3.17
  - Creates a set of "time series" tables, each table has 2 numeric
columns (timestamp, int) during initialization.
  - Every 1 minute, 2 threads will do total 15 writes. (using "INSERT OR
REPLACE ... (timestamp, int)" SQL into 15 tables).
  - SQLite DB opened with SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
flags, initialized with "PRAGMA journal_mode=wal;", threading mode
is Serialized for the libsqlite build, uses default VFS ("unix").
All other config params are default (e.g. autovacuum is disabled
etc.).
  - A separate thread runs "PRAGMA quick_check;" periodically every 5
minutes, in its own separate DB connection.
- Reader process: sqlite-3.11 + Python 2.7.11
  - Periodically reads time series tables for a given timestamp range
(usually latest 5 minutes) using SELECT queries (no INSERT/UPDATE/
DELETE from this process).
  - Uses same same "PRAGMA journal_mode=wal", uses the sqlite3 DBAPI
module from Python standard library.
Apart from above 2, no other processes are accessing the SQLite DB file.

We have updated both the reader and writer to use latest SQLite 3.21,
but without understanding the cause of corruption, we are unable to
say if this update to latest 3.21 would indeed prevent further
occurrences.

Thanks,
 Nikhil
Pagesize: 4096
Available pages: 1..16865
 000: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 SQLite format 3.
 010: 10 00 02 02 00 40 20 20 00 00 00 06 00 00 41 e1 .@  ..A.
 020: 00 00 00 00 00 00 00 00 00 00 00 56 00 00 00 04 ...V
 030: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00 
 040: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 
 050: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 06 
 060: 00 2e 09 28 00  ...(.   
Decoded:
 010: 10 00  4096  Database page size
 012: 022  File format write version
 013: 022  File format read version
 014: 000  Reserved space at end of page
 018: 00 00 00 06   6  File change counter
 01c: 00 00 41 e1   16865  Size of database in pages
 020: 00 00 00 00   0  Page number of first freelist page
 024: 00 00 00 00   0  Number of freelist pages
 028: 00 00 00 56  86  Schema cookie
 02c: 00 00 00 04   4  Schema format version
 030: 00 00 00 00   0  Default page cache size
 034: 00 00 00 00   0  Largest auto-vac root page
 038: 00 00 00 01   1  Text encoding
 03c: 00 00 00 00   0  User version
 040: 00 00 00 00   0  Incremental-vacuum mode
 044: 00 00 00 00   0  Application ID
 048: 00 00 00 00   0  meta[8]
 04c: 00 00 00 00   0  meta[9]
 050: 00 00 00 00   0  meta[10]
 054: 00 00 00 00   0  meta[11]
 058: 00 00 00 00   0  meta[12]
 05c: 00 00 00 06   6  Change counter for version number
 060: 00 2e 09 28 3017000  SQLite version number
   46: corrupt node [sm_stats_metadata], child 2 of page 2
 3818: root corrupt node [sm_stat_disk_latency_rate_dm-9]
Pagesize: 4096
Available pages: 1..16865
Header on btree page 46:
 000: 000  unknown
 001: 00 00 0  Offset to first freeblock
 003: 00 0b11  Number of cells on this page
 005: 01 25   293  Offset to cell content area
 007: 000  Fragmented byte count
 key: lx=left-child n=payload-size r=rowid
 ea4: cell[0] lx: -2108128674 n: 5 
 d43: cell[1] lx: -2107800993 n: 5 
 bdd: cell[2] lx: -2107473312 n: 5 
 ab0: cell[3] lx: -2111208863 n: 5 
 97f: cell[4] lx: -2110946718 n: 5 
 813: cell[5] lx: -2107080093 n: 5 
 6bc: cell[6] lx: -2108456348 n: 5 
 573: cell[7] lx: -2109373851 n: 5 
 

Re: [sqlite] Cost of function call in a WHERE clause

[Keith Medcalf]

If your function is "CONSTANT" or "DETERMINISTIC" and the argument is a 
constant then it will only be called once per statement.  If all the above 
conditions are not met then it will be called for each use on each row (where 
use includes alias expansions).

So for example if you have a function sin() that is deterministic, then:

select ... from table where table.value < sin(1.24345);
will call the sin() function once.

select ... from table where sin(table.value) < .5;
will call the sin() function once per row.

select sin(x), sin(y) from table where sin(x) < sin(1.234);
will call the sin() function either 1 or three times per row, plus one time to 
generate the constant.

If the sin() function is not deterministic, then it will be called every time 
it needs to be computed. even if the computation is a duplicate.  This is 
because the (!deterministic) => volatile and subject to change based on, well, 
just cause it feels like changing.

---
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 Mario Bezzi
>Sent: Thursday, 21 December, 2017 11:28
>To: SQLite mailing list
>Subject: [sqlite] Cost of function call in a WHERE clause
>
>Hello,
>
>I would like to use a user defined scalar function in a where clause,
>something like:
>
>SELECT ColumnA, ColumnB, ColumnC FROM Table1 WHERE ColumnA =
>myfunct(X);
>
>X is constant.  I am concerned by the performance of such a query as
>I
>wonder if myfunct is called just once or once for every row.
>
>I know I may probably determine it by reading SQLite generated pseudo
>code, but I am not fluent at it.
>
>Thank you in advance.
>
>mario
>
>___
>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] Compiling / building SQLite to include regex

[Keith Medcalf]

Compile and load the regexp extension (ext/misc/regexp.c in the full source 
distribution or the commit tracker).  Or append the extension to SQLite3.c and 
load it, or also create a routine that does the initialization for you and 
aappend that to the SQLite3.c code as well, and use the SQLITE_EXTRA_INIT 
define so that the extra initialization routine is executed for each connection 
that is created.

In other words, you just define a function with the correct name and cause it 
to be linked and loaded, and then you will have a REGEXP function and operator.

---
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 Paul Hoffman
>Sent: Thursday, 21 December, 2017 10:57
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Compiling / building SQLite to include regex
>
>Greetings. I understand that SQLite doesn't come natively with regex
>support, but that it can be added. My question is how to do so when I
>install. I'm building from source from
>.
>Is there a simple recipe for "make REGEX work after installation"?
>___
>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] INSERT OR IGNORE ignores constraints. Bug ?

[Keith Medcalf]

Simon,

Policy is being enforced.  You specifically declared in the table definition 
that rows must have (a > 10) in order to be "in the table".  The IGNORE as in 
INSERT OR IGNORE means exactly and precisely what it says:  INSERT the record 
if it is valid and IGNORE it oherwise.  You tried to insert a "bad" row and 
specified to ignore the error causing rows so that operation was ignored and 
the record was not inserted.

This is working as designed and as documented.

---
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 Simon Slavin
>Sent: Thursday, 21 December, 2017 10:50
>To: SQLite mailing list
>Subject: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?
>
>
>
>On 21 Dec 2017, at 3:46pm, David Raymond 
>wrote:
>
>> The only potential problem with "insert or ignore into" is that it
>will ignore any constraint violation for that record insert
>
>Wait.  What ?
>
>SQLite version 3.19.3 2017-06-27 16:48:08
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a >
>10));
>sqlite> INSERT INTO MyTable VALUES (15);
>sqlite> INSERT INTO MyTable VALUES (5);
>Error: CHECK constraint failed: noless
>sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
>sqlite> SELECT * FROM MyTable;
>15
>sqlite>
>
>What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did
>you discover it ?
>
>My understanding is that using INSERT OR IGNORE meant that bad
>inserts would fail, but they would do so silently, without triggering
>an error result.
>
>
>
>"When an applicable constraint violation occurs, the IGNORE
>resolution algorithm skips the one row that contains the constraint
>violation and continues processing subsequent rows of the SQL
>statement as if nothing went wrong. Other rows before and after the
>row that contained the constraint violation are inserted or updated
>normally."
>
>If I understand correctly, "the IGNORE resolution algorithm skips the
>one row that contains the constraint violation and continues
>processing subsequent rows of the SQL statement as if nothing went
>wrong" means that a row that violates constraints will not be
>inserted.
>
>I thought I could enforce policy by setting constraints.  Apparently
>not.
>
>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] Compile fails with '-Wall -Werror'

[Richard Hipp]

On 12/21/17, Richard Hipp  wrote:
>
> If you are unwilling to work-around this problem
> by omitting -Werror, then you might try upgrading to GCC 5.4.
>

You can also apply this patch:  https://www.sqlite.org/src/info/64487d658cb3b6c8

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

Re: [sqlite] Compile fails with '-Wall -Werror'

[Richard Hipp]

On 12/21/17, Brian Kambach  wrote:
> We recently upgraded sqlite from 3.19.3 to 3.21.0 and now building with
> '-Wall -Werror' fails (when it previously succeeded):
>
> $ gcc -O3 -Wall -Werror -fno-delete-null-pointer-checks -c -o sqlite3.o
> sqlite3.c
> sqlite3.c: In function 'exprAnalyze':
> sqlite3.c:131526:37: error: 'pLeft' may be used uninitialized in this
> function [-Werror=maybe-uninitialized]
>  pNewTerm->leftCursor = pLeft->iTable;
>  ^
> sqlite3.c:131529:28: error: 'eOp2' may be used uninitialized in this
> function [-Werror=maybe-uninitialized]
>  pNewTerm->eMatchOp = eOp2;
> ^
> cc1: all warnings being treated as errors
>
> I also noticed that sqlite3.c compiles successfully with -O0, but fails with
> -O1, -O2, or -O3.
>
> We compile everything with -Werror, so I'm hoping the answer is not "don't
> use -Werror".

Both of these warnings are false-positives that arise due to
limitations in the is-initialized theorem prover logic of GCC 4.8.
So, in other words, it is not possible for either pLeft or eOp2 to be
used uninitialized - it is just that GCC 4.8 is unable to construct a
proof of that fact.  If you are unwilling to work-around this problem
by omitting -Werror, then you might try upgrading to GCC 5.4.

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

[sqlite] Compile fails with '-Wall -Werror'

[Brian Kambach]

We recently upgraded sqlite from 3.19.3 to 3.21.0 and now building with '-Wall 
-Werror' fails (when it previously succeeded):

$ gcc -O3 -Wall -Werror -fno-delete-null-pointer-checks -c -o sqlite3.o 
sqlite3.c
sqlite3.c: In function 'exprAnalyze':
sqlite3.c:131526:37: error: 'pLeft' may be used uninitialized in this function 
[-Werror=maybe-uninitialized]
 pNewTerm->leftCursor = pLeft->iTable;
 ^
sqlite3.c:131529:28: error: 'eOp2' may be used uninitialized in this function 
[-Werror=maybe-uninitialized]
 pNewTerm->eMatchOp = eOp2;
^
cc1: all warnings being treated as errors

I also noticed that sqlite3.c compiles successfully with -O0, but fails with 
-O1, -O2, or -O3.

We compile everything with -Werror, so I'm hoping the answer is not "don't use 
-Werror".

Environment:
OS: CentOS 7
gcc: 4.8.5 20150623 (Red Hat 4.8.5-16)
sqlite: 3.21.0 (SQLITE_SOURCE_ID = "2017-10-24 18:55:49 
1a584e499906b5c87ec7d43d4abce641fdf017c42125b083109bc77c4de48827")

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

Re: [sqlite] Minor bug reports during build.

[Michael Tiernan]

I found that if I use "disable-tcl" it builds correctly.

(Testing is an issue but that's separate right now.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] generic advice for insert performance in-memory database optimization

[Simon Slavin]

On 21 Dec 2017, at 7:06pm, Nelson, Erik - 2  
wrote:

> I'm running on linux with the OS page size configured to 4096 and ~380 GB of 
> ram (much more than required for the table so I think I'm not swapping) and 
> haven't altered the sqlite page size.  I am using sqlite version 3.13.0 and 
> these pragmas immediately after database creation.
> 
> pragma temp_store = MEMORY
> pragma journal_mode = off
> 
> With these settings I'm seeing nonlinear (in a bad way) times for the insert. 
> Is that expected? 

Thank you for your detailed description of your setup, which saved a lot of 
back-and-forth.  Your blog page shows a good understanding of SQLite and I can 
find no obvious errors in your code or your reasoning.  Your Blog post includes 
a very nice report of comparative testing.

The inconsistency you’re seeing is the result of caching.  Once the amount of 
data busts the amount of cache memory available, the operations have to wait 
for storage access, which takes a lot longer than RAM access.  I’m not talking 
about the cache under control of SQLite, but the amount of memory allocated to 
the program by the OS.  The differences you show for the various PRAGMA changes 
are not unexpected and don’t suggest any fault with your setup.

I’m not familiar with Windows as a dev platform, but from what I see there, 
Windows is busting a cache (OS process memory ? storage ? both ?) near the 
upper end of your testing range.

As you say in your comments, your macOS and Windows platforms are not 
comparable because the hardware isn’t identical.  I thought it worth repeating 
that here before someone gets hooked up on the faster figures for the Mac.

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

Re: [sqlite] How to detect cycles in a hierarchical table?

[Lifepillar]

On 21/12/2017 17:13, E.Pasma wrote:
Now I see the difference between UNION and UNION ALL in 
recursion. It is documented as below. Although it needs careful reading 
to understand that UNION effectively eliminates loops.


Having a PostgreSQL background, I cannot recommend its SQL documentation
enough. The section on CTE explains how they are evaluated very well.
While it contains a few PostgreSQL-specific stuff (such as arrays), it
may be inspiring for users of other SQL products as well:

   https://www.postgresql.org/docs/current/static/queries-with.html

There are, in particular, specific examples on how to deal with cycles.

Life.

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

Re: [sqlite] Minor bug reports during build.

[Michael Tiernan]

Sorry for the top post. Sadly the android client forces it.

In short, I'm building two copies of sqlite3, one of which works fine, the
attempt to build it on the Linux host (using the prefix flag of course)
causes the build to begin but it to fail when it runs into the attempt at
modifying the (non-existent) file /usr/share/tcl8.5/sqlite3

All the other warnings and considerations are secondary to the point that
the makefile is attempting to change the permissions on an external (to the
user) tool which it neither built or should be able to modify.

Thanks for everyone's time!
-- 
<< MCT >>   Michael C Tiernan.http://www.linkedin.com/in/mtiernan

Non Impediti Ratione Cogatationis
Women and cats will do as they please, and
men and dogs should relax and get used to the idea. -Robert A. Heinlein

On Dec 21, 2017 2:26 PM, "Warren Young"  wrote:

On Dec 21, 2017, at 11:37 AM, Michael Tiernan 
wrote:
>
> I'm trying to build two copes of sqlite3 in a shared dropbox folder.

Do you intend to use SQLite inside the Dropbox folder once you’ve got it
working?  That’s only safe if only one person is using the database at a
time, and you wait for the sync to finish before trying to use the DB on
another machine.

If you need a networked DBMS, SQLite is generally not what you want, at
least not as-stock.  There are add-ons and alternatives that work far
better for this.  Google “SQLite Dropbox”.  It’s come up many times before.

> On a "Scientific Linux 6.7" (RHEL 6.7) system I did a built then build
> install *as a user* and not as root. Looking to create a localized copy
> specifically.

Try this:

$ ./configure --prefix="$HOME/sqlite3"

That will allow the “make install” to work without root privileges.  The
sqlite3 binary would land in $HOME/sqlite3/bin, with that configuration
option.

You can set the prefix to somewhere under your Dropbox folder if you’re
willing to take the risks to data safety that that entails.
___
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] Minor bug reports during build.

[Warren Young]

On Dec 21, 2017, at 11:37 AM, Michael Tiernan  wrote:
> 
> I'm trying to build two copes of sqlite3 in a shared dropbox folder.

Do you intend to use SQLite inside the Dropbox folder once you’ve got it 
working?  That’s only safe if only one person is using the database at a time, 
and you wait for the sync to finish before trying to use the DB on another 
machine.

If you need a networked DBMS, SQLite is generally not what you want, at least 
not as-stock.  There are add-ons and alternatives that work far better for 
this.  Google “SQLite Dropbox”.  It’s come up many times before.

> On a "Scientific Linux 6.7" (RHEL 6.7) system I did a built then build
> install *as a user* and not as root. Looking to create a localized copy
> specifically.

Try this:

$ ./configure --prefix="$HOME/sqlite3"

That will allow the “make install” to work without root privileges.  The 
sqlite3 binary would land in $HOME/sqlite3/bin, with that configuration option.

You can set the prefix to somewhere under your Dropbox folder if you’re willing 
to take the risks to data safety that that entails.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] generic advice for insert performance in-memory database optimization

[Nelson, Erik - 2]

I've got an in-memory database with a single table that I need to fill with 
~500 million rows.  There are no indexes and the table definitions is

create table data(id int, path int, month int, val1 double, val2 double, val3 
double... val20 double)

I'm running on linux with the OS page size configured to 4096 and ~380 GB of 
ram (much more than required for the table so I think I'm not swapping) and 
haven't altered the sqlite page size.  I am using sqlite version 3.13.0 and 
these pragmas immediately after database creation.

pragma temp_store = MEMORY
pragma journal_mode = off

With these settings I'm seeing nonlinear (in a bad way) times for the insert. 
Is that expected?  I've fiddled about with various performance-related settings 
like described on

https://blog.devart.com/increasing-sqlite-performance.html

with varying results but haven't managed to arrive at fairly-linear insert 
behavior.  It's a single-threaded insert on a prepared query with bound 
arguments in a tight loop.

Is linear-ish insert time a reasonable goal for an in-memory database?

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor bug reports during build.

[Michael Tiernan]

Here's my error report.
I'm trying to build two copes of sqlite3 in a shared dropbox folder. It's
crude but I'm doing it to prove the ability I want.

On a "Scientific Linux 6.7" (RHEL 6.7) system I did a built then build
install *as a user* and not as root. Looking to create a localized copy
specifically. At the bottom is the error, the make is attempting to modify
a system tool instead of a local one.

make install
> rm -rf tsrc
> mkdir tsrc
> cp -f /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/alter.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/analyze.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/attach.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/auth.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/backup.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/bitvec.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/btmutex.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/btree.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/btree.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/btreeInt.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/build.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/callback.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/complete.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/ctime.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/date.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/dbpage.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/dbstat.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/delete.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/expr.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/fault.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/fkey.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/func.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/global.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/hash.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/hash.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/hwtime.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/insert.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/legacy.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/loadext.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/main.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/malloc.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/mem0.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/mem1.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/mem2.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/mem3.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/mem5.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/memjournal.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/msvc.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/mutex.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/mutex.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/mutex_noop.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/mutex_unix.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/mutex_w32.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/notify.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/os.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/os.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/os_common.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/os_setup.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/os_unix.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/os_win.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/os_win.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/pager.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/pager.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/parse.y
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/pcache.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/pcache.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/pcache1.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/pragma.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/pragma.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/prepare.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/printf.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/random.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/resolve.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/rowset.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/select.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/status.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/shell.c.in
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/sqlite.h.in
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/sqlite3ext.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/sqliteInt.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/sqliteLimit.h
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/table.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/tclsqlite.c
> /home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src/threads.c
> 

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

Keith / Simon, thanks to both of you for those detailed replies but I'll need
a bit of time to digest them.

It may seem I'm a bit OCD trying to save a few microseconds here and there
but this is to do with a thread I started a while back regarding getting a
list of RowIDs pointing to the query results where the query consists of a
BaseTbl linked to lookup tables. As I said on that thread I consider the
time taken to run a query to be equal to the time taken to compile that list
of RowIDs. Thereafter a page of results can be obtained pretty much
instantaneously by linking the appropriate RowIDs with a subsection of the
original query. In that thread I was storing the RowIDs in a vector but this
thread is about testing storing them in a temp table. It isn't as fast as
the vector but there isn't a great deal of difference until you get into the
millions of records and (I'm hoping) to remove any dependency on RAM.

I have a well known sqlite browser on my laptop. If I view my largest table
(2.4 million recs) in a grid in this browser the top page appears with the
counter showing 'record 1 of at least x' alongside a vertical scrollbar that
doesn't work properly (it can't because it doesn't have the record count).
If I click 'Last record' in the navigation bar it takes almost 3 secs for
the last page to appear. Even then the vertical scrollbar doesn't work
properly. It's all pretty ugly. In contrast I can get a list of all the
RowIDs for that table in approx. 0.6 secs, show an accurate record counter /
vertical scrollbar and navigate to any point in the table in microseconds.
That's in win64 with an SSD based DB but it's still well worth it in win32
with a hard drive.



--
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] [EXTERNAL] How do I insert a record in an SQLite table only if the row does not already exist?

[David Raymond]

What I meant was it'll ignore the error message, not ignore the constraint.

So where you're inserting 5 there it'd be wrong to think "well, there was no 
error, so 5 is in the database from either before or now"

Say you have imported resource X for November, then you get December's version 
and want to import only the new stuff. If you do "insert or ignore" then yes, 
you won't waste time replacing the old stuff, but you also won't get the error 
notification that there was an issue with the new copy of the resource.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Thursday, December 21, 2017 1:15 PM
To: SQLite mailing list
Subject: Re: [sqlite] [EXTERNAL] How do I insert a record in an SQLite table 
only if the row does not already exist?



On 21 Dec 2017, at 3:46pm, David Raymond  wrote:

> The only potential problem with "insert or ignore into" is that it will 
> ignore any constraint violation for that record insert

Not true.

sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite> 

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] Cost of function call in a WHERE clause

[Mario Bezzi]

Hello,

I would like to use a user defined scalar function in a where clause, 
something like:


SELECT ColumnA, ColumnB, ColumnC FROM Table1 WHERE ColumnA = myfunct(X);

X is constant.  I am concerned by the performance of such a query as I 
wonder if myfunct is called just once or once for every row.


I know I may probably determine it by reading SQLite generated pseudo 
code, but I am not fluent at it.


Thank you in advance.

mario

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

Re: [sqlite] Minor bug reports during build.

[Michael Tiernan]

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

Re: [sqlite] Compiling / building SQLite to include regex

[jungle Boogie]

On 21 December 2017 at 10:18, Richard Hipp  wrote:
> On 12/21/17, Paul Hoffman  wrote:
>> Greetings. I understand that SQLite doesn't come natively with regex
>> support, but that it can be added. My question is how to do so when I
>> install. I'm building from source from
>> .
>> Is there a simple recipe for "make REGEX work after installation"?
>
> There is a loadable REGEXP extension here:
> https://www.sqlite.org/src/artifact/a68d25c659bd2d89
>

And information to compile here:
https://www.sqlite.org/loadext.html

> --
> D. Richard Hipp
> d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor bug reports during build.

[Richard Hipp]

On 12/21/17, Michael Tiernan  wrote:
> Is there a route for reporting an error for an average user without
> creating an account and all the overhead of the ticket system?

Send an email to this mailing list, or directly to me.

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

[sqlite] Minor bug reports during build.

[Michael Tiernan]

Is there a route for reporting an error for an average user without
creating an account and all the overhead of the ticket system?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling / building SQLite to include regex

[Richard Hipp]

On 12/21/17, Paul Hoffman  wrote:
> Greetings. I understand that SQLite doesn't come natively with regex
> support, but that it can be added. My question is how to do so when I
> install. I'm building from source from
> .
> Is there a simple recipe for "make REGEX work after installation"?

There is a loadable REGEXP extension here:
https://www.sqlite.org/src/artifact/a68d25c659bd2d89

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

Re: [sqlite] [EXTERNAL] How do I insert a record in an SQLite table only if the row does not already exist?

[Simon Slavin]

On 21 Dec 2017, at 3:46pm, David Raymond  wrote:

> The only potential problem with "insert or ignore into" is that it will 
> ignore any constraint violation for that record insert

Not true.

sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite> 

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

Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Simon Slavin]

On 21 Dec 2017, at 5:58pm, Igor Tandetnik  wrote:

> Isn't that precisely what happened in your example? Inserting 6 failed 
> silently. What again seems to be the problem?

I’m sorry.  You and Scott are quite right.  I have no idea what I was thinking.

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

Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Scott Robison]

On Dec 21, 2017 10:50 AM, "Simon Slavin"  wrote:



On 21 Dec 2017, at 3:46pm, David Raymond  wrote:

> The only potential problem with "insert or ignore into" is that it will
ignore any constraint violation for that record insert

Wait.  What ?

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite>

What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you
discover it ?

My understanding is that using INSERT OR IGNORE meant that bad inserts
would fail, but they would do so silently, without triggering an error
result.


Insert 15 succeeded, 5 failed with error, 6 failed without error. Seems
exactly as advertised. What am I missing?

Note that or ignore is statement specific, not transaction specific, if I
understand correctly.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Igor Tandetnik]

On 12/21/2017 12:50 PM, Simon Slavin wrote:

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite>

What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you 
discover it ?

My understanding is that using INSERT OR IGNORE meant that bad inserts would 
fail, but they would do so silently, without triggering an error result.


Isn't that precisely what happened in your example? Inserting 6 failed 
silently. What again seems to be the problem?


If I understand correctly, "the IGNORE resolution algorithm skips the one row that 
contains the constraint violation and continues processing subsequent rows of the SQL 
statement as if nothing went wrong" means that a row that violates constraints will 
not be inserted.


And in your example, it was not.
--
Igor Tandetnik

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

[sqlite] Compiling / building SQLite to include regex

[Paul Hoffman]

Greetings. I understand that SQLite doesn't come natively with regex
support, but that it can be added. My question is how to do so when I
install. I'm building from source from .
Is there a simple recipe for "make REGEX work after installation"?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Simon Slavin]

On 21 Dec 2017, at 3:46pm, David Raymond  wrote:

> The only potential problem with "insert or ignore into" is that it will 
> ignore any constraint violation for that record insert

Wait.  What ?

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite> 

What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you 
discover it ?

My understanding is that using INSERT OR IGNORE meant that bad inserts would 
fail, but they would do so silently, without triggering an error result.



"When an applicable constraint violation occurs, the IGNORE resolution 
algorithm skips the one row that contains the constraint violation and 
continues processing subsequent rows of the SQL statement as if nothing went 
wrong. Other rows before and after the row that contained the constraint 
violation are inserted or updated normally."

If I understand correctly, "the IGNORE resolution algorithm skips the one row 
that contains the constraint violation and continues processing subsequent rows 
of the SQL statement as if nothing went wrong" means that a row that violates 
constraints will not be inserted.

I thought I could enforce policy by setting constraints.  Apparently not.

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

Re: [sqlite] What happens if an in memory database runs out of memory

[Simon Slavin]

On 21 Dec 2017, at 4:34pm, curmudgeon  wrote:

> Put it this way, if I create n temp tables will there be n_+ 1 page caches
> or just the main cache containing a further  n temporary caches?

Try it and see.  But unless you intentionally make us strange numbers the 
problem you’ll run into is not set by SQLite, it’s down to the amount of memory 
your OS is allowing your application to use.  The SQLite settings you use are 
unlikely to have any effect on what actually happens.  This is one of the 
reasons I think you’re wasting your time worrying about this stuff.

> I would've liked to have known if sqlite took care of it
> all for me or if it was down to the OS.

SQLite has no low-level access to your computer memory.  If the documentation 
says it is keeping some data in memory, it means that SQLite allocates that 
memory by asking your OS for it, just the same as if you’d done it yourself in 
C.  And your OS will manage that memory using virtual memory the same as it 
would for any other memory your application uses.  If the operating system 
doesn’t use virtual memory then you’ll get an "out of memory" error.

> All I was able to determine was that setting temp_store = 2 (MEMORY) did
> speed up the queries but I've no idea if using that setting is risky on a
> lower spec pc.

SQLite can’t do anything with its memory that the rest of your program can't 
do.  So if you allocate too much your OS will step in and handle things for 
you, outside of SQLite’s control.

Also you should be aware that virtual memory is a very efficient way of letting 
your computer handle RAM and disk.  By artificially allocating RAM to specific 
resources you are preventing the OS using that RAM in what /it/ thinks is the 
most efficient way.  And generally computers are better at this stuff than 
humans are.

Lastly, don’t confuse your development environment with your runtime 
environment.  The amounts of memory you have free, and the memory your 
application will be allowed to use when it’s in real life use may be totally 
unlike what’s available in your IDE on your dev computer.  So don’t bother 
picking numbers for making things run really fast on your development platform.

The recommendation in this case is to leave the configuration to its default 
settings, leave the journal_mode at its default setting, and just go ahead and 
write your software the way your programming language and SQL suggest to you.  
Rely on the OS and SQLite to manage memory properly, which is usually very 
well.  Once you have a ton of experience about how your program runs in real 
life, /then/ you can start thinking about optimization.

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

Re: [sqlite] What happens if an in memory database runs out of memory

[Keith Medcalf]

>All I was able to determine was that setting temp_store = 2 (MEMORY)
>did speed up the queries but I've no idea if using that setting is risky
>on a lower spec pc.

I "tested" this and setting temp_store to MEMORY is the same as placing the 
database itself in :memory:.  That is, the page_cache and temp_page_cache size 
(as approriate) has zero effect.

That is, the size and memory consumption of both temp objects and objects in 
the database are limited by the ability of system to allocate virtual storage 
and once that is used up, an "OutOfMemory" error occurs with whatever 
consequences may ensure from that thus ensue.

If however the database is a file, or the temp_store is a file, then the 
applicable page cache size specifications provide a best effort limitation 
(note that it is best effort, not a guarantee) on the amount of memory consumed 
in the page_cache for each object type (database or temp).

So yes, if you run with temp_store = 2 on a computer with 4K of RAM you will 
have a limitation of being able to use a maximum of 4K of RAM.  On the other 
hand, if you run on a computer that has RAM in excess of the supported process 
virtual size, then you will keep consuming RAM until the process has allocated 
all the arena that it is allowed by the OS to allocate before that process gets 
an out of memory error.  Whether or not the "process" memory allocator runs out 
of allocable arena before the OS runs out of storage is defined by the OS (and 
the amount of virtual storage actually installed in the computer).

So "risky" is a questionable concept.  Everything is "risky".  I believe what 
you are trying to express is the probability of an out of memory condition, and 
that is an entirely different thing.  If your application is controlling a 
life-and-death process then it is quite likely that your CONSEQUENCE of failure 
is HIGH (immediate death of one of more persons).  

IF you have the temp_store on disk, then the probability of running out of 
memory is equal to the probability of running out of disk space, which is a 
separate question.  On the other hand, if you have temp_store in memory and 
only have 4K of virtual storage, then the probability that you will experience 
out-of-memory is high.  Alternatively, if you are using a computer than has 4 
Petabytes of RAM and you have temp_store in memory AND your software is written 
and running such that it can use all that RAM, then the probability of running 
out of RAM is remote.

Of course, these probabilities do not take into account the probability of 
"slow lingering death of one or more persons" because, since you cannot afford 
actual RAM, you instead use spinning-rust backed "Virtual Storage" which 
introduces "random latency" into your systems.


---
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] What happens if an in memory database runs out of memory

[curmudgeon]

>According to the documentation you quoted, 

>"Each temporary table and index is given its own page cache" 

>every temporary table gets its own cache.  Each cache can grow to a maximum
size of SQLITE_DEFAULT_TEMP_CACHE_SIZE .

Yeah, but it also says

"SQLite uses a page cache of recently read and written database pages. This
page cache is used not just for the main database file but also for
transient indices and tables stored in temporary files"

Put it this way, if I create n temp tables will there be n_+ 1 page caches
or just the main cache containing a further  n temporary caches?

>Be aware that you cannot magically create more RAM in your computer by
defining enough temporary tables. 

I don't know how I managed to give you the impression I ever thought that
Simon. All I was trying to determine was how I could best manage available
memory while hoping sqlite would do it for me. From the tests I've done
increasing the cache_size or SQLITE_DEFAULT_TEMP_CACHE_SIZE has made no
difference yet the default sizes are hopelessly inadequate to hold a temp
table of that size. I would've liked to have known if sqlite took care of it
all for me or if it was down to the OS.

All I was able to determine was that setting temp_store = 2 (MEMORY) did
speed up the queries but I've no idea if using that setting is risky on a
lower spec pc.




--
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 to detect cycles in a hierarchical table?

[Shane Dev]

I always followed the advice on https://sqlite.org/lang_with.html and use
UNION ALL in the compound select statement. This is why cycles trigger
infinite looping. In the case of my edges table, it does not make sense to
have cycles so my goal is to develop INSERT and UPDATE triggers that
prevent this possibility.

On 21 December 2017 at 12:11, Lifepillar  wrote:

> On 20/12/2017 22:31, Shane Dev wrote:
>
>> Hello,
>>
>> I have an edges table -
>>
>> sqlite> .sch edges
>> CREATE TABLE edges(parent, child);
>>
>> sqlite> select * from edges;
>> parent  child
>> 1   2
>> 1   3
>> 2   4
>> 3   1
>> 4   5
>> 5   2
>>
>> Here we have two cycles -
>>
>> 1) 1 => 3 => 1 (length 1)
>> 2) 2 => 4 => 5 => 2 (length 3)
>>
>> Cycles cause recursive common table expression queries to become infinite
>> loops.
>>
> Maybe you could show an example of such queries? This:
>
>   with recursive Visit(node) as (
> select parent from Edges where parent = 1
> union
> select child from Edges join Visit on parent = node
>   )
>   select node from Visit;
>
> returns a finite result (note that use of 'union' rather than 'union
> all').
>
> Life.
>
> ___
> 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 to detect cycles in a hierarchical table?

[E.Pasma]

Lifepillar wrote:


On 20/12/2017 22:31, Shane Dev wrote:

Hello,
I have an edges table -
sqlite> .sch edges
CREATE TABLE edges(parent, child);
sqlite> select * from edges;
parent  child
1   2
1   3
2   4
3   1
4   5
5   2
Here we have two cycles -
1) 1 => 3 => 1 (length 1)
2) 2 => 4 => 5 => 2 (length 3)
Cycles cause recursive common table expression queries to become  
infinite

loops.

Maybe you could show an example of such queries? This:

 with recursive Visit(node) as (
   select parent from Edges where parent = 1
   union
   select child from Edges join Visit on parent = node
 )
 select node from Visit;

returns a finite result (note that use of 'union' rather than 'union
all').

Life.


Brilliant. Now I see the difference between UNION and UNION ALL in  
recursion. It is documented as below. Although it needs careful  
reading to understand that UNION effectively eliminates loops.


https://www.sqlite.org/lang_with.html#recursivecte
If a UNION operator connects the initial-select with the recursive- 
select, then only add rows to the queue if no identical row has been  
previously added to the queue. Repeated rows are discarded before  
being added to the queue even if the repeated rows have already been  
extracted from the queue by the recursion step. If the operator is  
UNION ALL, then all rows generated by both the initial-select and the  
recursive-select are always added to the queue even if they are  
repeats. When determining if a row is repeated, NULL values compare  
equal to one another and not equal to any other value.

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

Re: [sqlite] [EXTERNAL] How do I insert a record in an SQLite table only if the row does not already exist?

[David Raymond]

Try surrounding the VALUES bit with a SELECT

insert into networklocks (...)
select * from (values(?,?,?,?))
where not exists...;

As to the other suggestions:
The only potential problem with "insert or ignore into" is that it will ignore 
any constraint violation for that record insert, be it the primary key, a 
unique constraint, a check constraint etc. So if you want it to ignore only 
primary key violations, but still yell on check constraint violations then that 
doesn't work.

Another possible option is to include the ignore in the column definition 
itself:

create table n1 (pk_Id int primary key on conflict ignore, foo text check 
(length(foo) > 0));

This will ignore a primary key issue, but still report a check constraint if 
you try to insert an empty string for foo. The potential issue with this is 
that  insert will silently ignore primary key errors, not just from your 
one specific query.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Thursday, December 21, 2017 7:17 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] [EXTERNAL] How do I insert a record in an SQLite table 
only if the row does not already exist?

INSERT OR IGNORE ...

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Patrick Skelton
Gesendet: Donnerstag, 21. Dezember 2017 12:56
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] How do I insert a record in an SQLite table only 
if the row does not already exist?

Hi,

I am wanting to create an 'atomic' SQL script that will insert a record into a 
table only if the supplied record's primary key does not already exist, thus 
avoiding the constraint exception that occurs if the insert goes ahead.

I have the following script which is wrong. I get an error saying the syntax is 
wrong near the 'WHERE'.


*BEGIN EXCLUSIVE TRANSACTION;*

*INSERT INTO NetworkLocks*
*(*
* PK_id,*
* owner_username,*
* unique_identifier,*
* creation_time*
*)*
*VALUES*
*(*
* @ID,*
* @owner_username,*
* @unique_identifier,*
* @creation_time*
*)*
*WHERE NOT EXISTS*
* ( SELECT * FROM NetworkLocks nl WHERE nl.PK_id = @ID );*

*END TRANSACTION;*


Currently, my code works by relying on the uniqueness of the primary key
(PK_id) and catch any exception. This is, however, a foreseeable error. It 
happens in normal operation. I'm not fond of exceptions for this situation.
I was hoping to change the SQL script so that it always runs to completion but 
somehow returns a result. Perhaps the number of rows affected might be zero 
instead of one?

Any help or advice would be very much appreciated. Also, as a newbie to SQLite, 
I am finding it difficult to get a handle on how to put together more complex 
queries.  There are plenty of example on the internet of simple scripts, but it 
is difficult to know how to move beyond the basics.
Any pointers to good sources of learning for this would be great.


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

Re: [sqlite] Can a SELECT statement be used within a trigger?

[David Raymond]

That's there for use of the raise() function. For example...

create trigger tbl_stop_deletes
before delete on tbl
begin
select raise(abort, 'Not allowing Delete''s from this table');
end;



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Shane Dev
Sent: Thursday, December 21, 2017 10:12 AM
To: SQLite mailing list
Subject: [sqlite] Can a SELECT statement be used within a trigger?

Hello

The syntax diagram at the top of
https://www.sqlite.org/lang_createtrigger.html implies a SELECT statement
can be used between the BEGIN and END key words.

For example -

sqlite> CREATE TABLE stuff(thing text);
sqlite> CREATE VIEW vstuff as select * from stuff;
sqlite> CREATE TRIGGER tstuff instead of insert on vstuff begin insert into
stuff select new.thing; select * from stuff; end;
sqlite> insert into vstuff select 'object';
-- no output
sqlite> select * from stuff;
thing
object

Here we see the INSERT statement was triggered but not the SELECT. Have I
misunderstood the syntax diagram?
___
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] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Behaviour is not consistent when using
create table as
or just select statement.
Try this simple test.

create table test(a int, b int);
insert into test values (1, 1);

select d from (select c as d from (select a as c from test));

you get column name d as expected
but when you have create table as statement

create table test2 as
select d from (select c as d from (select a as c from test));

you get table test2 with column name a.

If you change to this

create table test2 as
select d as d from (select c as d from (select a as c from test));

you will get name correct. I think it should
be the same as when just using select statement.

Best Regards
Radovan


select a from (select b from (select c from test)))

Richard Hipp je 21.12.2017 ob 14:52 napisal:

The behavior change is a bug fix.  See
http://sqlite.org/src/info/de3403bf5ae for details.

On 12/21/17, Radovan Antloga  wrote:

I have table (create statement):

CREATE TABLE SOPP1 (
STAT  varchar(1) collate systemnocase,
RID  varchar(2) collate systemnocase,
VP  integer,
BLANK  varchar(6) collate systemnocase,
NAZIV  varchar(24) collate systemnocase,
KN  varchar(12) collate systemnocase,
A  varchar(1) collate systemnocase,
B  varchar(1) collate systemnocase,
RACUN  varchar(1) collate systemnocase,
URE  varchar(1) collate systemnocase,
ZN  varchar(1) collate systemnocase,
TOCKE  varchar(1) collate systemnocase,
PRC  varchar(1) collate systemnocase,
UP  varchar(1) collate systemnocase,
IZPIS  varchar(1) collate systemnocase,
D  varchar(1) collate systemnocase,
F2U  varchar(1) collate systemnocase,
F2O  varchar(1) collate systemnocase,
F2T  varchar(1) collate systemnocase,
F2Z  varchar(1) collate systemnocase,
F2P_1  integer,
F2P_2  integer,
F2P_3  integer,
F5  varchar(1) collate systemnocase,
AJPES  varchar(1) collate systemnocase,
ZZ  integer,
VD  integer,
NS  integer,
MES  integer,
NORURE  varchar(1) collate systemnocase,
G  varchar(1) collate systemnocase,
E  varchar(1) collate systemnocase,
H  varchar(1) collate systemnocase,
I  varchar(1) collate systemnocase,
J  varchar(1) collate systemnocase,
SM  varchar(1) collate systemnocase,
NO  varchar(1) collate systemnocase,
PRIO  varchar(1) collate systemnocase,
V_1  varchar(1) collate systemnocase,
V_2  varchar(1) collate systemnocase,
V_3  varchar(1) collate systemnocase,
V_4  varchar(1) collate systemnocase,
V_5  varchar(1) collate systemnocase,
V_6  varchar(1) collate systemnocase,
V_7  varchar(1) collate systemnocase,
V_8  varchar(1) collate systemnocase,
V_9  varchar(1) collate systemnocase,
V_10  varchar(1) collate systemnocase,
V_11  varchar(1) collate systemnocase,
V_12  varchar(1) collate systemnocase,
FOR  integer,
P_1  integer,
P_2  integer,
P_3  integer,
P_4  integer,
P_5  integer,
P_6  integer,
primary key (RID, VP, BLANK));

When I create new table using this sql:

drop table if exists WM4P;
create table WM4P as
select
P, A, B, AB, U, H, ZZ,
case
  when AB in ('7') then 99
  when AB in ('57', '58', '59', '5M') then null
  when AB = '56' and ZZ = 12 then 01
  when AB = '56' then 02
  when A = '3' then 03
  when AB in ('1M') then 08
  when AB in ('10') then 07
  when AB in ('12') then null
  when A in ('1', '5') and H = '1' then 02
  when A in ('5') then 02
  when A in ('1') then 01
end as M4_OP
from (
select
  VP as P, ifnull(A,'') as A, ifnull(B,'') as B,
ifnull(A,'')||ifnull(B,'') as AB,
  ifnull(URE,'') as U, ifnull(H,'') as H, ZZ
from SOPP1
);

You will see that first column name is VP instead of P.
In previous versions name was P not VP.

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





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

Re: [sqlite] Can a SELECT statement be used within a trigger?

[Simon Slavin]

On 21 Dec 2017, at 3:11pm, Shane Dev  wrote:

> Here we see the INSERT statement was triggered but not the SELECT. Have I
> misunderstood the syntax diagram?

It’s possible that the SELECT is being processed.  However, since the INSERT 
command returns no data it still can return no data.

It should be possible to find out if the SELECT is being processed by defining 
an external function which includes a log statement.  But this information is 
probably useless to you.

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

Re: [sqlite] [EXTERNAL] Can a SELECT statement be used within a trigger?

[Hick Gunter]

INSERT and INSTEAD OF INSERT triggers have no result set. The select will be 
performed, but it's result set is discarded. Same for UPDATE and INSTEAD OF 
UPDATE triggers.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Shane Dev
Gesendet: Donnerstag, 21. Dezember 2017 16:12
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Can a SELECT statement be used within a trigger?

Hello

The syntax diagram at the top of
https://www.sqlite.org/lang_createtrigger.html implies a SELECT statement can 
be used between the BEGIN and END key words.

For example -

sqlite> CREATE TABLE stuff(thing text);
sqlite> CREATE VIEW vstuff as select * from stuff; CREATE TRIGGER tstuff
sqlite> instead of insert on vstuff begin insert into
stuff select new.thing; select * from stuff; end;
sqlite> insert into vstuff select 'object';
-- no output
sqlite> select * from stuff;
thing
object

Here we see the INSERT statement was triggered but not the SELECT. Have I 
misunderstood the syntax diagram?
___
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] What happens if an in memory database runs out of memory

[Simon Slavin]

On 21 Dec 2017, at 2:15pm, curmudgeon  wrote:

> Can someone tell me, if I create a temporary table does is its 'separate
> cache' created within the cache_size cache or is it completely separate from
> that?

According to the documentation you quoted,

"Each temporary table and index is given its own page cache"

every temporary table gets its own cache.  Each cache can grow to a maximum 
size of SQLITE_DEFAULT_TEMP_CACHE_SIZE .

Be aware that you cannot magically create more RAM in your computer by defining 
enough temporary tables.

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

[sqlite] Can a SELECT statement be used within a trigger?

[Shane Dev]

Hello

The syntax diagram at the top of
https://www.sqlite.org/lang_createtrigger.html implies a SELECT statement
can be used between the BEGIN and END key words.

For example -

sqlite> CREATE TABLE stuff(thing text);
sqlite> CREATE VIEW vstuff as select * from stuff;
sqlite> CREATE TRIGGER tstuff instead of insert on vstuff begin insert into
stuff select new.thing; select * from stuff; end;
sqlite> insert into vstuff select 'object';
-- no output
sqlite> select * from stuff;
thing
object

Here we see the INSERT statement was triggered but not the SELECT. Have I
misunderstood the syntax diagram?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What happens if an in memory database runs out of memory

[curmudgeon]

*"SQLite uses a page cache of recently read and written database pages. This
page cache is used not just for the main database file but also for
transient indices and tables stored in temporary files. If SQLite needs to
use a temporary index or table and the SQLITE_TEMP_STORE compile-time
parameter and the temp_store pragma are set to store temporary tables and
index on disk, the information is still initially stored in memory in the
page cache. The temporary file is not opened and the information is not
truly written to disk until the page cache is full. 
This means that for many common cases where the temporary tables and indices
are small (small enough to fit into the page cache) no temporary files are
created and no disk I/O occurs. Only when the temporary data becomes too
large to fit in RAM does the information spill to disk. 
Each temporary table and index is given its own page cache which can store a
maximum number of database pages determined by the
SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time parameter. (The default value is
500 pages.) The maximum number of database pages in the page cache is the
same for every temporary table and index. The value cannot be changed at
run-time or on a per-table or per-index basis. Each temporary file gets its
own private page cache with its own SQLITE_DEFAULT_TEMP_CACHE_SIZE page
limit." *

Can someone tell me, if I create a temporary table does is its 'separate
cache' created within the cache_size cache or is it completely separate from
that?




--
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] Changed behaviour or bug using field alias in 3.21.0

[Richard Hipp]

The behavior change is a bug fix.  See
http://sqlite.org/src/info/de3403bf5ae for details.

On 12/21/17, Radovan Antloga  wrote:
> I have table (create statement):
>
> CREATE TABLE SOPP1 (
>STAT  varchar(1) collate systemnocase,
>RID  varchar(2) collate systemnocase,
>VP  integer,
>BLANK  varchar(6) collate systemnocase,
>NAZIV  varchar(24) collate systemnocase,
>KN  varchar(12) collate systemnocase,
>A  varchar(1) collate systemnocase,
>B  varchar(1) collate systemnocase,
>RACUN  varchar(1) collate systemnocase,
>URE  varchar(1) collate systemnocase,
>ZN  varchar(1) collate systemnocase,
>TOCKE  varchar(1) collate systemnocase,
>PRC  varchar(1) collate systemnocase,
>UP  varchar(1) collate systemnocase,
>IZPIS  varchar(1) collate systemnocase,
>D  varchar(1) collate systemnocase,
>F2U  varchar(1) collate systemnocase,
>F2O  varchar(1) collate systemnocase,
>F2T  varchar(1) collate systemnocase,
>F2Z  varchar(1) collate systemnocase,
>F2P_1  integer,
>F2P_2  integer,
>F2P_3  integer,
>F5  varchar(1) collate systemnocase,
>AJPES  varchar(1) collate systemnocase,
>ZZ  integer,
>VD  integer,
>NS  integer,
>MES  integer,
>NORURE  varchar(1) collate systemnocase,
>G  varchar(1) collate systemnocase,
>E  varchar(1) collate systemnocase,
>H  varchar(1) collate systemnocase,
>I  varchar(1) collate systemnocase,
>J  varchar(1) collate systemnocase,
>SM  varchar(1) collate systemnocase,
>NO  varchar(1) collate systemnocase,
>PRIO  varchar(1) collate systemnocase,
>V_1  varchar(1) collate systemnocase,
>V_2  varchar(1) collate systemnocase,
>V_3  varchar(1) collate systemnocase,
>V_4  varchar(1) collate systemnocase,
>V_5  varchar(1) collate systemnocase,
>V_6  varchar(1) collate systemnocase,
>V_7  varchar(1) collate systemnocase,
>V_8  varchar(1) collate systemnocase,
>V_9  varchar(1) collate systemnocase,
>V_10  varchar(1) collate systemnocase,
>V_11  varchar(1) collate systemnocase,
>V_12  varchar(1) collate systemnocase,
>FOR  integer,
>P_1  integer,
>P_2  integer,
>P_3  integer,
>P_4  integer,
>P_5  integer,
>P_6  integer,
>primary key (RID, VP, BLANK));
>
> When I create new table using this sql:
>
> drop table if exists WM4P;
> create table WM4P as
> select
>P, A, B, AB, U, H, ZZ,
>case
>  when AB in ('7') then 99
>  when AB in ('57', '58', '59', '5M') then null
>  when AB = '56' and ZZ = 12 then 01
>  when AB = '56' then 02
>  when A = '3' then 03
>  when AB in ('1M') then 08
>  when AB in ('10') then 07
>  when AB in ('12') then null
>  when A in ('1', '5') and H = '1' then 02
>  when A in ('5') then 02
>  when A in ('1') then 01
>end as M4_OP
> from (
>select
>  VP as P, ifnull(A,'') as A, ifnull(B,'') as B,
> ifnull(A,'')||ifnull(B,'') as AB,
>  ifnull(URE,'') as U, ifnull(H,'') as H, ZZ
>from SOPP1
>);
>
> You will see that first column name is VP instead of P.
> In previous versions name was P not VP.
>
> Best Regards
> Radovan Antloga
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] How do I insert a record in an SQLite table only if the row does not already exist?

[R Smith]

On 2017/12/21 1:56 PM, Patrick Skelton wrote:

Hi,

I am wanting to create an 'atomic' SQL script that will insert a record
into a table only if the supplied record's primary key does not already
exist, thus avoiding the constraint exception that occurs if the insert
goes ahead.

I have the following script which is wrong. I get an error saying the
syntax is wrong near the 'WHERE'///


Two ways to achieve this. INSERT a Query[1] (in stead of Values) or 
Ignore conflicts with existing Keys[2]


Example [1]:

*INSERT INTO NetworkLocks*
*(*
* PK_id,*
* owner_username,*
* unique_identifier,*
* creation_time*
*)*
*SELECT*
* @ID,*
* @owner_username,*
* @unique_identifier,*
* @creation_time*
*WHERE NOT EXISTS*
* ( SELECT * FROM NetworkLocks nl WHERE nl.PK_id = @ID );*


Example [2]:

*INSERT OR IGNORE INTO NetworkLocks*
*(*
* PK_id,*
* owner_username,*
* unique_identifier,*
* creation_time*
*)*
*VALUES*
*(*
* @ID,*
* @owner_username,*
* @unique_identifier,*
* @creation_time*
*)*
*WHERE NOT EXISTS*
* ( SELECT * FROM NetworkLocks nl WHERE nl.PK_id = @ID );*


HTH,
Ryan

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

[sqlite] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

I have table (create statement):

CREATE TABLE SOPP1 (
  STAT  varchar(1) collate systemnocase,
  RID  varchar(2) collate systemnocase,
  VP  integer,
  BLANK  varchar(6) collate systemnocase,
  NAZIV  varchar(24) collate systemnocase,
  KN  varchar(12) collate systemnocase,
  A  varchar(1) collate systemnocase,
  B  varchar(1) collate systemnocase,
  RACUN  varchar(1) collate systemnocase,
  URE  varchar(1) collate systemnocase,
  ZN  varchar(1) collate systemnocase,
  TOCKE  varchar(1) collate systemnocase,
  PRC  varchar(1) collate systemnocase,
  UP  varchar(1) collate systemnocase,
  IZPIS  varchar(1) collate systemnocase,
  D  varchar(1) collate systemnocase,
  F2U  varchar(1) collate systemnocase,
  F2O  varchar(1) collate systemnocase,
  F2T  varchar(1) collate systemnocase,
  F2Z  varchar(1) collate systemnocase,
  F2P_1  integer,
  F2P_2  integer,
  F2P_3  integer,
  F5  varchar(1) collate systemnocase,
  AJPES  varchar(1) collate systemnocase,
  ZZ  integer,
  VD  integer,
  NS  integer,
  MES  integer,
  NORURE  varchar(1) collate systemnocase,
  G  varchar(1) collate systemnocase,
  E  varchar(1) collate systemnocase,
  H  varchar(1) collate systemnocase,
  I  varchar(1) collate systemnocase,
  J  varchar(1) collate systemnocase,
  SM  varchar(1) collate systemnocase,
  NO  varchar(1) collate systemnocase,
  PRIO  varchar(1) collate systemnocase,
  V_1  varchar(1) collate systemnocase,
  V_2  varchar(1) collate systemnocase,
  V_3  varchar(1) collate systemnocase,
  V_4  varchar(1) collate systemnocase,
  V_5  varchar(1) collate systemnocase,
  V_6  varchar(1) collate systemnocase,
  V_7  varchar(1) collate systemnocase,
  V_8  varchar(1) collate systemnocase,
  V_9  varchar(1) collate systemnocase,
  V_10  varchar(1) collate systemnocase,
  V_11  varchar(1) collate systemnocase,
  V_12  varchar(1) collate systemnocase,
  FOR  integer,
  P_1  integer,
  P_2  integer,
  P_3  integer,
  P_4  integer,
  P_5  integer,
  P_6  integer,
  primary key (RID, VP, BLANK));

When I create new table using this sql:

drop table if exists WM4P;
create table WM4P as
select
  P, A, B, AB, U, H, ZZ,
  case
    when AB in ('7') then 99
    when AB in ('57', '58', '59', '5M') then null
    when AB = '56' and ZZ = 12 then 01
    when AB = '56' then 02
    when A = '3' then 03
    when AB in ('1M') then 08
    when AB in ('10') then 07
    when AB in ('12') then null
    when A in ('1', '5') and H = '1' then 02
    when A in ('5') then 02
    when A in ('1') then 01
  end as M4_OP
from (
  select
    VP as P, ifnull(A,'') as A, ifnull(B,'') as B, 
ifnull(A,'')||ifnull(B,'') as AB,

    ifnull(URE,'') as U, ifnull(H,'') as H, ZZ
  from SOPP1
  );

You will see that first column name is VP instead of P.
In previous versions name was P not VP.

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

Re: [sqlite] [EXTERNAL] How do I insert a record in an SQLite table only if the row does not already exist?

[Hick Gunter]

INSERT OR IGNORE ...

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Patrick Skelton
Gesendet: Donnerstag, 21. Dezember 2017 12:56
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] How do I insert a record in an SQLite table only 
if the row does not already exist?

Hi,

I am wanting to create an 'atomic' SQL script that will insert a record into a 
table only if the supplied record's primary key does not already exist, thus 
avoiding the constraint exception that occurs if the insert goes ahead.

I have the following script which is wrong. I get an error saying the syntax is 
wrong near the 'WHERE'.


*BEGIN EXCLUSIVE TRANSACTION;*

*INSERT INTO NetworkLocks*
*(*
* PK_id,*
* owner_username,*
* unique_identifier,*
* creation_time*
*)*
*VALUES*
*(*
* @ID,*
* @owner_username,*
* @unique_identifier,*
* @creation_time*
*)*
*WHERE NOT EXISTS*
* ( SELECT * FROM NetworkLocks nl WHERE nl.PK_id = @ID );*

*END TRANSACTION;*


Currently, my code works by relying on the uniqueness of the primary key
(PK_id) and catch any exception. This is, however, a foreseeable error. It 
happens in normal operation. I'm not fond of exceptions for this situation.
I was hoping to change the SQL script so that it always runs to completion but 
somehow returns a result. Perhaps the number of rows affected might be zero 
instead of one?

Any help or advice would be very much appreciated. Also, as a newbie to SQLite, 
I am finding it difficult to get a handle on how to put together more complex 
queries.  There are plenty of example on the internet of simple scripts, but it 
is difficult to know how to move beyond the basics.
Any pointers to good sources of learning for this would be great.


Kind wishes ~ Patrick
___
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] How do I insert a record in an SQLite table only if the row does not already exist?

[Patrick Skelton]

Hi,

I am wanting to create an 'atomic' SQL script that will insert a record
into a table only if the supplied record's primary key does not already
exist, thus avoiding the constraint exception that occurs if the insert
goes ahead.

I have the following script which is wrong. I get an error saying the
syntax is wrong near the 'WHERE'.


*BEGIN EXCLUSIVE TRANSACTION;*

*INSERT INTO NetworkLocks*
*(*
* PK_id,*
* owner_username,*
* unique_identifier,*
* creation_time*
*)*
*VALUES*
*(*
* @ID,*
* @owner_username,*
* @unique_identifier,*
* @creation_time*
*)*
*WHERE NOT EXISTS*
* ( SELECT * FROM NetworkLocks nl WHERE nl.PK_id = @ID );*

*END TRANSACTION;*


Currently, my code works by relying on the uniqueness of the primary key
(PK_id) and catch any exception. This is, however, a foreseeable error. It
happens in normal operation. I'm not fond of exceptions for this situation.
I was hoping to change the SQL script so that it always runs to completion
but somehow returns a result. Perhaps the number of rows affected might be
zero instead of one?

Any help or advice would be very much appreciated. Also, as a newbie to
SQLite, I am finding it difficult to get a handle on how to put together
more complex queries.  There are plenty of example on the internet of
simple scripts, but it is difficult to know how to move beyond the basics.
Any pointers to good sources of learning for this would be great.


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

Re: [sqlite] DateTime kind stored as undefined

[mnie]

Hi Cezary,

indeed I try your piece of code (to retrieve data) and it works as expected,
kind is stored in db. It seems that it is a problem with dapper instead of
SqLite. So I will bump issue in Dapper
(https://github.com/StackExchange/Dapper/issues/571). Many thanks for help!

Best regards,
Michał



--
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 to detect cycles in a hierarchical table?

[Lifepillar]

On 20/12/2017 22:31, Shane Dev wrote:

Is there a query which can detect all cycles regardless of length?


Not.. cough... particularly efficient, but simple:

with recursive Paths(s,t) as (
  select parent, child from Edges
  union
  select parent, t from Edges join Paths on child = s
)
select count(*) > 0 from Paths where s = t;

This finds all pairs of nodes reachable from each other,
then checks whether there are nodes reachable from themselves.
It returns 1 if there are cycles, 0 otherwise.

A better way would probably be to write a function that performs a
dfs.

Life.

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

Re: [sqlite] How to detect cycles in a hierarchical table?

[Lifepillar]

On 20/12/2017 22:31, Shane Dev wrote:

Hello,

I have an edges table -

sqlite> .sch edges
CREATE TABLE edges(parent, child);

sqlite> select * from edges;
parent  child
1   2
1   3
2   4
3   1
4   5
5   2

Here we have two cycles -

1) 1 => 3 => 1 (length 1)
2) 2 => 4 => 5 => 2 (length 3)

Cycles cause recursive common table expression queries to become infinite
loops.

Maybe you could show an example of such queries? This:

  with recursive Visit(node) as (
select parent from Edges where parent = 1
union
select child from Edges join Visit on parent = node
  )
  select node from Visit;

returns a finite result (note that use of 'union' rather than 'union
all').

Life.

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