Re: [sqlite] Can we create a table where the value of one particular column <> another column?

And of course you will need an index on edges.parent and one on edges.child since you need indexes on foreign keys. (unless you do not intend to enforce them and they are merely for "documentation" of intent to perhaps have a consistent database mayhaps perchance). You will probably also want

Re: [sqlite] Can we create a table where the value of one particular column <> another column?

On 20 Dec 2017, at 6:30am, Shane Dev wrote: > Let's say we have nodes and edges tables - > > sqlite> .sch nodes > CREATE TABLE nodes(id integer primary key, description text); > sqlite> .sch edges > CREATE TABLE edges(parent references nodes, child references nodes); > > Can we restrict the e

Re: [sqlite] [EXTERNAL] Can we create a table where the value of one particular column <> another column?

Use a trigger and make it raise an exception, something like (not tested, just an example of how it might work): CREATE TRIGGER BEFORE INSERT ON WHEN NEW.parent == NEW.child BEGIN RAISE(FAIL, 'connecting to self not allowed') END; You may also require a similar BEFORE UPDATE trigger to avoid

Re: [sqlite] [EXTERNAL] Re: printf() with UTF-8 and \n \t format

The most common "problem" with UTF-8 and string lengths is that multibyte UTF-8 characters (most often characters with diacritical marks, e.g. german umlaut or special characters like the EUR sign) get truncated in between their constituent bytes. This leads to invalid byte sequences at the "end

[sqlite] Can we create a table where the value of one particular column <> another column?

Let's say we have nodes and edges tables - sqlite> .sch nodes CREATE TABLE nodes(id integer primary key, description text); sqlite> .sch edges CREATE TABLE edges(parent references nodes, child references nodes); Can we restrict the edges table so that inserting or updating a row where edges.paren

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

>>Until the underlying system memory allocator fails and then it go >boom. How >much goes boom is OS dependent. Some OSes will only allow the errant >process go boom. Others (such as those from Microsoft) the entire OS >go >boom if the out of memory condition encompases the entire V=V address >s

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

On 19 Dec 2017, at 9:22pm, curmudgeon wrote: > Thanks Keith. So sqlite does look to increase the temp table's cache size if > it's not big enough? No. Memory allocated to that cache is memory which can’t be used by anything else. There’s probably a far better use for that memory than using it

Re: [sqlite] same code produces a different database file on different computers

Karl Forner wrote: > On Tue, Dec 19, 2017 at 3:12 PM, Clemens Ladisch wrote: >> Where does the hex number in "excluded_mice_temp2b5036f270" come from? >> Is it random? > > Yes, thanks, I think I got it. In order to alter tables, we first move them > to a new table with a temp name, copy them, then

Re: [sqlite] printf() with UTF-8 and \n \t format

On 2017/12/19 11:13 PM, Tony Papadimitriou wrote: Great! Didn't think of the char() function at all.  (Although I would prefer a platform independent \n) "\n" is NOT platform independent.  Char(10) on the other hand IS platform independent. That's perhaps the root of the misunderstanding. I

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

>Until the underlying system memory allocator fails and then it go boom. How much goes boom is OS dependent. Some OSes will only allow the errant process go boom. Others (such as those from Microsoft) the entire OS go boom if the out of memory condition encompases the entire V=V address space.

Re: [sqlite] printf() with UTF-8 and \n \t format

Great! Didn't think of the char() function at all. (Although I would prefer a platform independent \n) Thanks. -Original Message- From: Keith Medcalf So, is there any way to advance to next line from a command line printf()? sqlite> select printf('%s%s%s', 'line 1', char(10), 'li

Re: [sqlite] printf() with UTF-8 and \n \t format

On 2017/12/19 10:24 PM, Tony Papadimitriou wrote: 2. Does it understand \n and \t?  I put actual line breaks inside the string >which is OK if run from script file but it won’t work with one-liners on the >command-line.> The \n, \t, \r etc. are really dependent on some factors (OS etc.).

Re: [sqlite] printf() with UTF-8 and \n \t format

>So, is there any way to advance to next line from a command line >printf()? print a linefeed. That is how you tell a computer output device to advance to the beginning of the next line. sqlite> select printf('%s%s%s', 'line 1', char(10), 'line 2'); line 1 line 2 sqlite> --- The fact that the

Re: [sqlite] printf() with UTF-8 and \n \t format

-Original Message- From: R Smith On 2017/12/19 8:37 PM, Tony Papadimitriou wrote: A couple of questions about printf 1. Does it work with UTF-8? If so, how? - Yes. - Very nicely. I'm using SQL v3.21 and UTF-8 does not work correctly. (Not from the command line.) I tried with la

Re: [sqlite] printf() with UTF-8 and \n \t format

Which printf? There are a lot of them. Assuming that you mean the SQLite3 built-in function printf() (as in SELECT PRINTF(...);) that function does not interpret backslash escape sequences. Interpretation of such things are a user I/O feature, not a data storage/retrieval feature. As for

Re: [sqlite] printf() with UTF-8 and \n \t format

On 2017/12/19 8:37 PM, Tony Papadimitriou wrote: A couple of questions about printf 1. Does it work with UTF-8? If so, how? - Yes. - Very nicely. 2. Does it understand \n and \t? I put actual line breaks inside the string which is OK if run from script file but it won’t work with one-line

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

Until the underlying system memory allocator fails and then it go boom. How much goes boom is OS dependent. Some OSes will only allow the errant process go boom. Others (such as those from Microsoft) the entire OS go boom if the out of memory condition encompases the entire V=V address space

Re: [sqlite] Atomic DELETE index optimisation?

On 2017/12/19 6:15 PM, Dinu wrote: 2) Structure alterations; either drop table, or drop indexes : I am reluctant to do this; my evangelical instinct tells me hacking the semantics of life might lead to implosion of Earth :) Oh the irony Your instinct (like most such instincts) is simply w

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

>> 3) Similar to the original question, if you set temp_store = 2 (memory) and >> there isn't enough memory for the table what happens? >By 'memory' that web page is referring to whatever your operating system thinks is memory. So the >same thing happens as would happen to any application which

[sqlite] printf() with UTF-8 and \n \t format

A couple of questions about printf 1. Does it work with UTF-8? If so, how? 2. Does it understand \n and \t? I put actual line breaks inside the string which is OK if run from script file but it won’t work with one-liners on the command-line. Thank you _

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

On 19 Dec 2017, at 6:01pm, curmudgeon wrote: > 1) I read in those links that each temp table is given (by default) its own > page cache of 500 pages. Is this a separately created page cache or is it > 500 pages from THEE page chache? If it's the latter that will explain the > slowdown for cache

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

Thanks Simon/Gunter. I thought those sections cleared things up until I tried a few tests. I opened the DB, set temp_store to STORE, cache_size to CACHE and then calculated the average secs taken (over 2 runs) to run the following [Tbl has integer primary key ID, contains 10,570 records & is cross

Re: [sqlite] Atomic DELETE index optimisation?

Dinu Option 2, dropping and recreating the index with the transaction, seems to be the way forward - I would suggest that if the author of SQlite (Dr Hipp) has put this forward as a solution, as he did earlier in this thread, then it is probably a safe option and will not lead to an implosion of

Re: [sqlite] Atomic DELETE index optimisation?

On 19 Dec 2017, at 4:15pm, Dinu wrote: > 3) "Deleted" bit field - presumably the "soft delete" as you call it; If you do try this, the 'bit' column should be declared as INTEGER and the values stored should be 0 and 1. SQLite is extremely efficient at storing/sorting these values. Includin

Re: [sqlite] Atomic DELETE index optimisation?

Hi sub sk79, I have so far from this thread the following suggestions: 1) Copy table -> TRUNCATE -> copy back; this doesn't work, what was supposed to be TRUNCATE semantics (DELETE FROM without WHERE) has the same performance as with WHERE. 2) Structure alterations; either drop table, or drop index

Re: [sqlite] Atomic DELETE index optimisation?

On Dec 17, 2017, at 3:53 PM, Dinu Marina Is there any way to fix/improve this in userland? I think the thread already has a viable solution but still if userland fix is an option: would a soft-delete based design work for you? https://dba.stackexchange.com/questions/125431/hard-delete-vs-soft

Re: [sqlite] same code produces a different database file on different computers

On Tue, Dec 19, 2017 at 3:12 PM, Clemens Ladisch wrote: > Karl Forner wrote: > > Here's a screenshot of some of the diffs using vbindiff > > > > https://ibb.co/kNm0X6 > > SQLite uses hash tables for schema objects, so different names might > result in such differences. > > Where does the hex numb

Re: [sqlite] same code produces a different database file on different computers

> > PRAGMA integrity_check; > all ok > > > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite

Re: [sqlite] Odd question

Mark Sent: Tuesday, December 19, 2017 1:32 AM On Mon Dec 18, 2017 at 04:48:27PM +, Nelson, Erik - 2 wrote: > Nomad Sent: Sunday, December 17, 2017 4:11 PM > >On Sat Dec 16, 2017 at 07:53:22PM +, Nelson, Erik - 2 wrote: > > >> Select 1 as value from (insert into table1 values(a, b, c)) I'

Re: [sqlite] same code produces a different database file on different computers

Karl Forner wrote: > Here's a screenshot of some of the diffs using vbindiff > > https://ibb.co/kNm0X6 SQLite uses hash tables for schema objects, so different names might result in such differences. Where does the hex number in "excluded_mice_temp2b5036f270" come from? Is it random? Regards, C

Re: [sqlite] same code produces a different database file on different computers

On 19 Dec 2017, at 1:30pm, Karl Forner wrote: > and the dumps usign the .dump sqlite3.exe command are also identical. Paul has a better chance of understanding the hex dump than I do, but what I think I’m seeing is trivial differences in file organisation. In other words some pieces of data a

Re: [sqlite] same code produces a different database file on different computers

So the "used bytes" are the same, but the "unused bytes" are not. That would seem to indicate the possibility that the "empty page" initialization is the difference. Some OSes will return zero'd out "new" sectors while some are perfectly happy to return whatever data happened to reside in the

Re: [sqlite] same code produces a different database file on different computers

and the dumps usign the .dump sqlite3.exe command are also identical. On Tue, Dec 19, 2017 at 2:19 PM, Karl Forner wrote: > > Using the sqlite3 shell tool please give the command > > SELECT * FROM sqlite_master; > > the results are identical > > table|_meta|_meta|2|CREATE TABLE "_meta"("TABL

Re: [sqlite] same code produces a different database file on different computers

> Using the sqlite3 shell tool please give the command > SELECT * FROM sqlite_master; the results are identical table|_meta|_meta|2|CREATE TABLE "_meta"("TABLENAME" TEXT NOT NULL , "COLNAME" TEXT NOT NULL , "VARNAMES" TEXT , "LABELS" TEXT , "PREFS" TEXT , PRIMARY KEY(TABLENAME,COLNAME))

Re: [sqlite] [EXTERNAL] Re: same code produces a different database file on different computers

The internal table sqlite_master has a field named sql that contains the text of the create statements. This may be what you are looking at. Does the difference show up in the sqlite_master table contents? -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailingli

Re: [sqlite] same code produces a different database file on different computers

On 19 Dec 2017, at 12:43pm, Karl Forner wrote: > All the software in the docker container. so it is exactly the same version > of all software and libraries, except the linux kernel. Moreover the > differences are not in the header, rather interspersed with what looks like > CREATE statements.

Re: [sqlite] same code produces a different database file on different computers

Thanks for your reply. All the software in the docker container. so it is exactly the same version of all software and libraries, except the linux kernel. Moreover the differences are not in the header, rather interspersed with what looks like CREATE statements. On Tue, Dec 19, 2017 at 1:40 PM,

Re: [sqlite] same code produces a different database file on different computers

SQLite stores the verison number of the librrary in the database header. Different SQlite libraries on different computers would cause this error. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-S

[sqlite] same code produces a different database file on different computers

Hello, I encountered a weird behavior recently. The exact same code (executed from a docker container using the same image) produced a different database file on two computers, as verified by a MD5 or sha256 hash of the two files. But using the* .sha3sum* of sqlite3.exe I could check that the cont

Re: [sqlite] Does sqlite have official development testing tool?

Thanks for all your suggestions. Is it correct to add my compile-options(such as -DSQLITE_DEFAULT_MMAP_SIZE=0x7fff) just after CFLAGS in Makefile? As I found some weird bugs if I do so. For example, sometimes wal2.test can pass but sometimes it reports "cannot open savepoint - SQL statements

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

Pragma temp_store and the preprocessor macro SQLITE_TEMP_STORE determine where temp tables and indices are stored. See http://sqlite.org/pragma.html#pragma_temp_store -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von curmud

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

On 19 Dec 2017, at 11:40am, Simon Slavin wrote: > See section 3 of > > Sorry. See section 4 too. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/m

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

On 19 Dec 2017, at 9:33am, curmudgeon wrote: > Running the same tests again using > an actual table (TEMP keyword omitted) shows the in memory INSERT (and > DELETE) to be twice as fast on the database loaded into memory compared to > when it's accessed from the SSD.The timings in the original t

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

My apologies. The inserts in the above tests were made on a TEMP table which I assumed was being created in the existing database. I've since discovered that isn't the case. *Tables created using the "CREATE TEMP TABLE" syntax are only visible to the database connection in which the "CREATE TEMP T

Re: [sqlite] Macro expansion of B0 on Linux PPC system

On 16 December 2017 at 11:21, Richard Hipp wrote: > On 12/15/17, Lee, Greg wrote: > > I never got a reply on this issue and someone else tripped up on it: > > > > https://github.com/spack/spack/issues/6698 > > > > Any help or even acknowledgement of this message would be appreciated. > > > > I b

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

My apologies. The inserts in the above tests were made on a TEMP table which I assumed was being created in the existing database. I've since discovered that isn't the case.*Tables created using the "CREATE TEMP TABLE" syntax are only visible to the database connection in which the "CREATE TEMP TAB