Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

Interesting, because you cannot have two rows (two tile_id) for the same combination of tile_zoom / tile_row / tile_column since the latter are a required to be unique primary keys. That is, the map table is declared as if it were the dictionary: map[tile_zoom, tile_row, tile_column] = tile_i

Re: [sqlite] Foreign keys - one to one, one to many relationship

On Saturday, 14 October, 2017 04:37, Richard Damon , wrote: >One way to make them build one to at-most-one relationships is too >add a unique index on the foreign key, then it can only occur once. >A True one-one relationship by the normailization rules says that >they should all be in the same

Re: [sqlite] Grouping and grabbing one item

Since you are eventually going to come to the part where you need to include the quote for the correct target, lets include that too, just to skip ahead: select (SELECT ed from Tasks as I where I.task = 'QUOTE' and I.Pid = O.Pid and I.target l

Re: [sqlite] very sqlite3 noobie error

There are a number of syntax errors. Line 1 - You have an / that should not be there. Line 6 - You cannot call a module, only a function in a module (not strictly true, but in the case of pysqlite2, it is) Line 12 - You are missing the closing quote on the SQL string Line 17 - You have not inden

Re: [sqlite] very sqlite3 noobie error

>-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of John R. Sowden >Sent: Sunday, 22 October, 2017 22:24 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] very sqlite3 noobie error > >thank you.  i think th

Re: [sqlite] Article about using sqlite3 in Python

On Monday, 23 October, 2017 08:36, David Raymond wrote: NB: References to pysqlite2 refer to the builtin sqlite3 wrapper in python. This wrapper was called pysqlite2 long before it was included in the standard library. It is still updated to fix bugs and accommodate changes in the SQL DDL

Re: [sqlite] Article about using sqlite3 in Python

in__": >print "Python version: {0}".format(sys.version) >print "Python sqlite3 module version: >{0}".format(sqlite3.version) >print "sqlite3.dll version: {0}".format(sqlite3.sqlite_version) >print "executemany takes: {0:,.3f}

Re: [sqlite] C# pragma integrity_check call throwing exception

Have you run "PRAGMA integrity_check;" from the command line shell against the same database and does it return rows or just throw the same exception? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message--

Re: [sqlite] Small Performance Regression (3.21.0)

I don't see any difference in the runtimes, at least not with the current head of trunk ... sqlite> .once x sqlite> select * from invoices; Run Time: real 0.032 user 0.031250 sys 0.00 sqlite> .once y sqlite> SELECT ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry, Or

Re: [sqlite] calculation with the result of two select results

You should probably declare your table thusly: create table werte ( minutestamp integer default (strftime('%s', 'now') / 60), ... ); create index werte_minutestamp on werte (minutestamp); then your "minutestamp" is in minutes since the Unix epoch and you can create an index on it. (and

Re: [sqlite] Segfault when query again in-memory db

>I tested the in-memory with multi-thread (but inserts/deletes are in >a lock) What does "with multi-thread" mean (to you)? --- 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 [ma

Re: [sqlite] SQLite DB - DateTime field values are not consistent

Fascinating. What is the Operating System and Version (eg Linux or Windows ); SQLite version; interface (C or .snot)? Does this only affect trips through the platform localtime function within SQLite (that is, if you store GMT/UTC by leaving out the 'localtime' modifier) do you still

Re: [sqlite] Missing data on SELECT

I think your query is in error. Amongst other things, tou have the same condition listed twice: SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON ( cl.id = ls.id AND cl.login = 'id0' AND cl.id = ls.id AND DUPLICATE cl.XtraB != ls.XtraB ); You cannot

Re: [sqlite] deadlock between query and insert in-memory db

Please clarify (for the second time): >I tested the in-memory with multi-thread (but inserts/deletes are in >a lock) What does "with multi-thread" mean? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Mess

Re: [sqlite] Missing data on SELECT

e that tells me which ProjID exists in ProjectsMine, but >donot >exists in ProjectsAll. These must be INSERTED into ProjectsALL. And >then >do the SELECT above to get which have changed the XtraB date. I hope >this >explains everything. Thanks for the help. > >-Original Messag

Re: [sqlite] [sqlite-dev] SQLite 3.16.2-3 - Incomplete support for ISO8601 strings with time zone

As explained on the linked page, right near the top: "The date and time functions use a subset of IS0-8601 date and time formats." SUBSET means some of, but not all of. ISO-8601 permits 24 hours, 60 minutes, and 61 seconds in a day. However, this is not part of the "supported subset" either.

Re: [sqlite] How not to let random be calculated again and again and

The easiest way is likely to make the query so that it cannot be flattened by adding an ORDER BY (that does not reference the column containing the non-deterministic function by name -- ie, use only OUTPUT column position indicators (ordinals), not names or aliases). This will require the quer

Re: [sqlite] Most efficient way to detect on-disk change

So you are caching data at the application level that is cached at the database page cache level which is cached in the Operating System file cache that lives in a file residing on disk -- effectively storing three copies of the data in memory. What advantage does your third-level (application

Re: [sqlite] Running sums and averages

EXPLAIN QUERY PLAN is the first step. --- 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 Balaji Ramanathan >

Re: [sqlite] Is this error expected?

Not really. Table TAB does not contain a column named rowid. tab.rowid refers to the non-column representing the row number of a row in the table. If you declared table TAB to actually have a column called rowid then it would work just fine, even if that column rowid still contained the row n

Re: [sqlite] Is this error expected?

y should (IMO) also be found by USING as these are >logically >equivalent. > >The only difference of the two forms is that the result of a SELECT * >will >include this column once with USING, and twice with ON ... = ... > >Thank you for your response. > >-Original Mess

Re: [sqlite] Simple SQL question?

UPDATE table_a SET issue_date = (SELECT GetAgeAtDate(dob, issue_date) FROM table_p WHERE table_p.id = id); --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Messa

Re: [sqlite] Simple SQL question?

That is not possible since there is only one column called issue_date in all the tables mentioned in the query ... --- 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:sq

Re: [sqlite] Simple SQL question?

Re: [sqlite] Simple SQL question? > >They end up in the wrong row. > >RBS > >On Wed, Nov 15, 2017 at 9:59 PM, Keith Medcalf >wrote: > >> >> That is not possible since there is only one column called >issue_date in >> all the tables mentioned in the query ..

Re: [sqlite] Confusion about DISTINCT keyword

Neither. It has nothing to do with the DISTINCT keyword, which causes only DISTINCT rows to be returned (duplicates are removed). You misunderstanding is on the nature of a SCALAR. A Scalar means ONE value. A correlated SCALAR subquery (a correlated subquery embedded as a column in a select

Re: [sqlite] Update the SQLite package in Tcl

On 19 November, 2017 10:50, Joseph R. Justice asked: >I'd think that dropping a newer version of SQLite, compiled as a run- >time linkable library, into a pre-existing binary compiled to use a >previous version of SQLite would require the ABI for SQLite (compiled >for use >as a linkable library

Re: [sqlite] Update the SQLite package in Tcl

On Sunday, 19 November, 2017 20:46, Joseph R. Justice wrote: >On Sun, Nov 19, 2017 at 4:49 PM, Keith Medcalf >wrote: >> On 19 November, 2017 10:50, Joseph R. Justice >asked: >> The same applies for SQLite and anything else that has a stable >> interface. >Fa

Re: [sqlite] WAL mode with readers and writers

WAL mode permits 'reading' by multiple connections while 1 connection is writing. Never ever is more than a single writer permitted. It does this by creating "cursor stability" when a read transaction is commenced (that is, changes to the database made on a DIFFERENT CONNECTION will not be vi

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

If you want the fruits sorted (and not duplicated), why not just declare that when defining the table? create table fruits (id integer primary key, fruit text collate nocase unique); and if you want to know the "relative row number" of the fruit simply have your application count them: logic

Re: [sqlite] Energy consumption of SQLite queries

>Considering how total energy of computation is entirely liberated as heat Human technology at its current primitive state converts ALL energy usage into heat. At scale you call this is called "global warming" and it is an inherent process -- at least until humans figure out how to convert e

Re: [sqlite] Many ML emails going to GMail's SPAM

In my opinion it is the beginning of the end of crappy freemail providers and their overzealous spam filtering. And it is about time. If you run an RFC complaint MTA then there is really very little problem with SPAM at all -- I have many connections per second rejected for RFC non-complian

Re: [sqlite] Many ML emails going to GMail's SPAM

If by JS you mean JavaScript, then this is a non-starter. Many people (myself included) do not permit remote code to be executed on our computers. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message---

Re: [sqlite] Many ML emails going to GMail's SPAM

--- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. On Tuesday, 21 November, 2017 09:06, Peter Da Silva wrote: >On 11/21/17, 9:59 AM, "sqlite-users on behalf of Keith Medcalf" >kmedc...@dessus.com&g

Re: [sqlite] Many ML emails going to GMail's SPAM

Strict RFC compliance is very simple: (1) When a remote MTA connects it MUST NOT speak until spoken to. (2) A remote MTA MUST NOT violate the command/response protocol. (3) The IP Address of the remote host MUST resolve (in the in-addr.arpa domain) to a name that forward resolves to a set of IP

Re: [sqlite] Many ML emails going to GMail's SPAM

And checking SPF is pretty useful as well. Once you have enforced strict compliance, however, the effect of SPF is negligible (less than 1/1000%). DKIM/DMARC generally causes more trouble than it solves (it was designed by a committee of idiots after all) and should be mostly ignored other t

Re: [sqlite] Many ML emails going to GMail's SPAM

On Wednesday, 22 November, 2017 04:47, Richard Damon wrote: >There is a fundamental problem with the email system that it goes >back to a kinder and gentler time, and it is trivial to spoof most >mail. Including good old-fashioned snail mail of course. There is also no way to determine the

Re: [sqlite] Many ML emails going to GMail's SPAM

>> There is nothing wrong with email - but there is an awful lot wrong >> with gnail and Google's ideas on how email is done. (Not to >> mention Yahoo, but it seems that MS have the sense to leave the >> underpinnings of hotmail as they were.) >> To put it simply - friends don't let friends use

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

>> Hi, Shane, >> What I don't understand is why do you need to do that? >Imagine I have a GUI element with a drop down list of fruit. The >source of >the list is my fruit table and it may have many entries. It might >more >convenient to list the popular fruit near the top. In that case the >fruit

Re: [sqlite] Using computed column once in SELECT

>BTW, is ‘now’ value locked during the query execution to avoid the >possibility (however small) of two columns ending up with different >age calculations (e.g., running during date crossover on someone’s >birthday)? By default, 'now' is step stable (that is, it will return the same value for al

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

That is correct. You cannot spell "INTEGER PRIMARY KEY", which is an explicit alias for the rowid, as anything other than that one particular correct spelling. Declaring "bigint primary key" (ie, using an incorrect spelling) defines a column that IS NOT an explicit alias for the rowid. It is

Re: [sqlite] Need SQLite datafiles to be locked on OS level (Windows)

No. The Windows Search Indexer or Windows Defender only processes files when they are "closed" (that is, the act of closing a file adds it to the queue of files to be processed). As long as they are "open" they are not enqueued for processing. There are a few exceptions, but they all requi

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

You are missing the point. The rowid is assigned automatically if it is not specified (that is, if it is null on insert). This is the behaviour of the rowid. In all databases and filesystems ever invented anywhere in the multiverse by any carbon (even non-carbon) based lifeform, whether an

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

Actually a UUID or a GUID has a 100% certainty of a collision, not just a possibility of a collision. Just as all hash algorithms which take something and generate a shorter "hash" or "checksum" will always have collisions. Without exception and as an absolute 100% certainty. There is no wa

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

qlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Richard Damon >Sent: Friday, 24 November, 2017 14:39 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] "BIGINT" doesn't act like INTEGER on table >creation [Bug] &g

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

Only if you assume a monotonic clock ... --- 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 J. King >Sent: F

Re: [sqlite] Getting an advance list of RowIDs for a query result set

This is fixed in the current head of trunk. Although the implementation may change, it will appear in the next release. https://www.sqlite.org/src/timeline?n=50 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Origin

Re: [sqlite] How to use sqlite3_table_column_metadata?

Not an answer to what is wrong with your call to get the metadata, however, the information is all available from SQL. Data Catalog Views: drop view SysIndexColumns; drop view SysIndexes; drop view SysColumns; drop view SysObjects; create view if not exists SysObjects as select type as ObjectTy

Re: [sqlite] How to use sqlite3_table_column_metadata?

IsPartOfKey from ( select ObjectType, ObjectName, IndexName, seqno as IndexColumnSequence, cidas ColumnID, name as ColumnName, "desc" as IsDescendingOrder, coll as Collation,

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

>Can you not deduce what you want using sqlite_sequence, as described >in section 2.6.1 of > >? This allows you to distinguish a primary key which happens to be >an integer from an |NTEGER PRIMARY KEY. No it does not. It only ide

Re: [sqlite] Foreign key help

And of course in the command line shell you can and should use .lint fkey-indexes to let you know if you are missing any indexes required for efficient foreign-key enforcement operations. It will report missing indexes on the PARENT (table/columns referred to) and on CHILDREN (tables/columns

Re: [sqlite] Datetime / Transactions / CLI

Datetime functions (that is, what constitutes "now") was, by default, step-stable. The value is cached within the VDBE (statement object) on its first use per-step and retains the same value until the VDBE code yields a row. Re-entry on the next step used to reset "now". This was changed to

Re: [sqlite] Sqlite and docker performance question

In addition to the execution time of the SQL you are also measuring time to load and link the sqlite3 command. A perhaps more realistic test would be to change the f.sql to contain: .timer on CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER, virtual INTEGER, pfid VARCHAR

Re: [sqlite] Article on AUTOINC vs. UUIDs

Well, in my opinion the guy is an idiot. The way to avoid the issues he is describing as the problems with serial IDs (or using the RowID) are simple to avoid: DO NOT DO THOSE THINGS! If you know that disclosing the RowID in a URL is ungood, then DO NOT DO THAT. Sheesh. As my good buddy Bu

Re: [sqlite] Article on AUTOINC vs. UUIDs

On Thursday, 30 November, 2017 09:27, J Decker wrote: >> UUIDs are nice though if you don't have a natural key available and >> you are generating keys on multiple systems. Sadly, they are strings >> in sqlite, and it would be more efficient to store and compare them >> as their 128-bit repres

Re: [sqlite] Simple read-only program very slow

Is there an index on pos where ppos is the left-most field (or the only field) in the index? What is the column affinity of ppos? Of the fiold you are passing as a parameter? Is ppos unique? If you CREATE [UNIQUE] INDEX searchindex ON pos (ppos, pnum); then your query can be satisfied only us

[sqlite] Compile Error: SQLITE_ENABLE_MULTITHREADED_CHECKS and SQLITE_ENABLE_API_ARMOR defined concurrently

Both are fine when either is defined independently (or neither is defined) but when both are defined concurrently there are compiler errors in function checkMutexFree: sqlite3x.c: In function 'checkMutexFree': sqlite3x.c:23521:8: error: dereferencing pointer to incomplete type 'sqlite3_mutex {

Re: [sqlite] Simple read-only program very slow

kernel. >On Thu, Nov 30, 2017 at 7:01 PM Keith Medcalf >wrote: > >> >> Is there an index on pos where ppos is the left-most field (or the >only >> field) in the index? >> What is the column affinity of ppos? Of the fiold you are passing >as a >> par

Re: [sqlite] Good resources for TCL/TK

CPython can calculate the factorial of 108000 in less than 4 seconds :) --- 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]

Re: [sqlite] Why Unicode is difficult

That depends if the value of the table column called "1" is 1 or not ... --- 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

Re: [sqlite] Cannot initialize statically linked extension

You should only be defining SQLITE_CORE if in fact the extension is part of the core -- that is compiled and included (statically linked) to the core sqlite3.c compilation unit. In this case, the extension makes direct calls to the sqlite3 entry points and shares the same runtime as the sqlite

Re: [sqlite] Cannot initialize statically linked extension

On Monday, 4 December, 2017 15:03, Jens Alfke wrote: >> On Dec 4, 2017, at 11:59 AM, Keith Medcalf >>wrote: >> You should only be defining SQLITE_CORE if in fact the extension is >>part of the core -- that is compiled and included (statically linked) >>to the c

Re: [sqlite] Cannot initialize statically linked extension

On Monday, 4 December, 2017 15:44, Jens Alfke wrote: >> If one object is using, for example, the multithreaded runtime and >>the others are using the single threaded runtime (for example), and >>the third perhaps the subsystem runtime >From the OP’s other thread here it looks like they’re develo

Re: [sqlite] Simple read-only program very slow

>All of my machines are 64-bit, and run 64-bit Linux kernels. Python3 >is a 64-bit executable according to 'file'. >I re-ran the whole thing (slightly modified to also do a test on >pmain) on my big dual-Xeon (32 hyperthreads, 256GB! RAM ) from >System76. In spite of having about half the CPU

Re: [sqlite] StepSqlite: SuperPowers for SQLite and BerkeleyDB

Uses an expired SSL certificate ... --- 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 sub sk79 >Sent: Tuesda

Re: [sqlite] Emulate right-join

You want a LEFT JOIN not a RIGHT JOIN (these are of course just lazy spellings for LEFT OUTER JOIN and RIGHT OUTER JOIN respectively), assuming that you want all selected rows from the table of the LEFT and only the matching values (else NULL) for the table(s) on the right, which is how you des

Re: [sqlite] finding the number of records until a value is different

Have your application read the table in reverse order. Then when the value of BC changes, stop incrementing a counter and close the select. You can use whatever ordering you like just so long as it is "descending" (that is reading backwards). ((Code example in Python -- you can use wordier a

Re: [sqlite] DateTime to bigint

That is: UPDATE Table SET Datum = (StrfTime('%s', Datum) + 62135596800) * 1000 WHERE typeof(Datum) == 'text'; There is no such thing as a datatype of "bigint". This declaration will give you a column affinity of "integer". There is no such thing as a datatype of "DateTime". This decl

Re: [sqlite] Retrieving constraint name

After stripping out comments and so forth of course ... --- 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 pe

Re: [sqlite] How to index data based on custom comparisons?

On Wednesday, 13 December, 2017 13:35, Lifepillar wrote: >I am implementing an extension for manipulating IEEE754 decimal >numbers. Numbers are stored as blobs using a standard encoding. >Numbers that are mathematically equal may have different >representations, (e.g., 1.0 may have mantissa 10

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

You will get an "Out of Memory" error, since you have run out of V=V memory to allocate ... Depending on the vagaries of the OS, all sorts of other hell may break loose as well, if your "process" out-of-memory condition corresponds to a general OS out-of-memory condition. That is to say that

Re: [sqlite] Odd question

Your method of achieving a solution to whatever problem you are trying to solve will not work. Perhaps you can state the problem you are trying to solve without making an assumption as to how to solve that problem, someone may be able to solve the problem with a workable solution rather than a

Re: [sqlite] Odd question

ginal Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Nelson, Erik - 2 >Sent: Saturday, 16 December, 2017 14:44 >To: SQLite mailing list >Subject: Re: [sqlite] Odd question > >Keith Medcalf wrote on Sent: Saturday, December 16, 2017

Re: [sqlite] Atomic DELETE index optimisation?

You page cache is probably too small to contain the working set of database pages that require modification. There should be no difference between "deleting" and "inserting". Both require changes to all applicable B+ and B- Trees. Though of course, it can be VERY slow if you have SECURE DELE

Re: [sqlite] Atomic DELETE index optimisation?

(1) Since you are in WAL, have you set pragma SYNCHRONOUS=1 (NORMAL)? (Don't know if it will make any difference in your particular case). (2) The page_cache size makes a HUGE difference. Modified pages are ONLY flushed to the WAL file when they overflow the cache. The fewer modified pages t

Re: [sqlite] Atomic DELETE index optimisation?

a lot all at once. --- 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 Keith Medcalf >Sent: S

Re: [sqlite] Atomic DELETE index optimisation?

affic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Dinu >Sent: Sunday, 17 December, 2017 15:58 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Atomic DELETE index optimisation? >

Re: [sqlite] Atomic DELETE index optimisation?

Hmmm. 4.6 GB in the Filesystem cache (the Level 2 cache), and only 2 pages (78 MB or thereabouts) in the process pagecache (the Level 1 cache). And with only 4.6 GB in the Level 2 cache, the I/O rate drops to almost zero. If you "moved" that memory (or some of it) from Level 2 to Level 1

Re: [sqlite] Odd question

>I investigated a further while exploring some of the list >suggestions. The app halts with an error unless >sqlite3_column_count() > 0. That means I misspoke when I mentioned >that the sql statement needed to return at least one row. I’m not >sure if that makes a difference, though. That makes

Re: [sqlite] Odd question

On Monday, 18 December, 2017 11:35 >Keith Medcalf Sent: Monday, December 18, 2017 1:07 PM >To: SQLite mailing list >Subject: Re: [sqlite] Odd question >>>I investigated a further while exploring some of the list >>>suggestions. The app halts with an error unless &g

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

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

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

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 i

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 SQLITE_EX

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 function

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 wh

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

>I thought I had posted this earlier but I don't see it. >Earlier I said the ideal solution would be something that uses memory >and defaults to disc if it runs out of memory. In response Richard's >suggested using a temp database with a blank name as that would use >memory but parts of it would

Re: [sqlite] Suspected bug: parse error depending on platform

>The simple example below works as expected on Ubuntu, but fails on >Windows 7. Both using Python3.5 and Sqlite 2.6.0, so that’s not a >version issue. 2.6.0 is not the version of SQLite3, it is the version of the pysqlite2 wrapper module. When that third party package was incorporated into the

Re: [sqlite] Modify the sqlite database with DB Browser to update a new field

This sounds like a "DB Browser" issue, whatever a "DB Browser" is. You should direct questions about how to enter SQL commands into "DB Browser" to the "DB Browser" support channel. This is especially true since it appears that you have solved the problem satisfactorily when using the SQLit

Re: [sqlite] Move to Github!!?

Heavens forbid! --- 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 Shekhar Reddy >Sent: Monday, 25 December,

Re: [sqlite] Move to Github!!?

Isn't GitHub a place for Gits to hang out, sort of like Twitter is a place for Twits to hang out? --- 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..

Re: [sqlite] Move to Github!!?

On Tuesday, 26 December, 2017 13:28, J Decker wrote: >On Tue, Dec 26, 2017 at 12:25 PM, Keith Medcalf >wrote: >> Isn't GitHub a place for Gits to hang out, sort of like Twitter is >> a place for Twits to hang out? >Nope that's gitter.im (related, and integrate

Re: [sqlite] Move to Github!!?

Heavens forbid! How is it possible to work if not from a Command-Line window? That is, YUCK. Seriously how can anyone get any work done at all without having a command prompt at which one, how to put this, types commands? I happen to be one of those that holds Ashton-Tate's dBase II (or was it

Re: [sqlite] fsync on -wal still happening

One presumes that you mayhaps read the documentation? "In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized before each checkpoint and the database file is synchronized after each completed checkpoint and the WAL file header is synchronized when a WAL file begins to be reus

Re: [sqlite] fsync on -wal still happening

>Naturally, and of course. The point of my initial post was that I was >still seeing sync operations with synch=NORMAL when I shouldn't have >according to the below docs (now debatable whether they were actually >written to disk or just the os cache). I purposely configured synch >to >NORMAL to av

Re: [sqlite] Can i check database corruption by select statement instead of quick_check?

Of course you can: select * from pragma_quick_check; or select * from pragma_integrity_check; --- 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...@

<    1   2   3   4   5   6   7   8   9   10   >