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

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

Re: [sqlite] Btree page corruption

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

Re: [sqlite] Minor bug reports during build.

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

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

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

[sqlite] Importing Text to Create a Table

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

Re: [sqlite] Btree page corruption

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

Re: [sqlite] Btree page corruption

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

[sqlite] Btree page corruption

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

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

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

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

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

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

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

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

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

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

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

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

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

Re: [sqlite] Minor bug reports during build.

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

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

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

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

Re: [sqlite] Minor bug reports during build.

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

Re: [sqlite] Minor bug reports during build.

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

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

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

Re: [sqlite] Minor bug reports during build.

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

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

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

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

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

[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

Re: [sqlite] Minor bug reports during build.

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

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

Re: [sqlite] Minor bug reports during build.

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] Minor bug reports during build.

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

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

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

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

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

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.

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

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 ?

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

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

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

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

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

>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

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

>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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[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

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

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

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

The behavior change is a bug fix. See http://sqlite.org/src/info/de3403bf5ae for details. On 12/21/17, Radovan Antloga wrote: > I have table (create statement): > > CREATE TABLE SOPP1 ( >STAT varchar(1) collate systemnocase, >RID varchar(2) collate

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

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

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

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)

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

[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

Re: [sqlite] DateTime kind stored as undefined

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,

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

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

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

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