Re: [sqlite] Building SQLite DLL with Visual Studio 2015

2019-01-21 Thread Simon Slavin
On 21 Jan 2019, at 11:16am, J Decker wrote: > 4. Statically linked to and compiled with your datalayer code. What he said. Also, you should be using the 'Amalgamation' download to do this, unless you need some compiler switches which are not supported by that source-set. Simon.

Re: [sqlite] Database locking problems

2019-01-20 Thread Simon Slavin
On 20 Jan 2019, at 11:54pm, Richard Damon wrote: > If the issue is the dead lock, you get similar issues with all DBMSes. I'm not perfectly sure of my logic here, but OP posted elsewhere that replacing BEGIN with BEGIN IMMEDIATE cures the problem. I think this indicates that his problem

[sqlite] Request for documentation improvement: transactions

2019-01-19 Thread Simon Slavin
concerns me. I suspect it started out small and grew, without ever being rewritten. (1) It explains four complicated issues and has no sections. I see two possibilities: either improve that page, or split it into (A) a short page which

Re: [sqlite] Database locking problems

2019-01-19 Thread Simon Slavin
On 19 Jan 2019, at 4:43pm, wrote: > I'm thinking one possible sequence might be: It's the right suggestion, but you're still in diagnostic stage, so this is a faster test: 1) Make sure your code still sets timeouts on every connection. 5000 ms should be fine. 2) Replace every BEGIN with

Re: [sqlite] Variable Declaration

2019-01-19 Thread Simon Slavin
On 19 Jan 2019, at 4:49am, Stephen Chrzanowski wrote: > I know about the bindings. I don't know about all languages supporting it. Bindings are part of the SQLite API. Any language which can make SQLite calls should be supporting binding. Using binding means you can have the variables you

Re: [sqlite] Variable Declaration

2019-01-18 Thread Simon Slavin
On 19 Jan 2019, at 3:57am, Stephen Chrzanowski wrote: > I was going to have three variable set with > three different strings I could just copy/paste to generate that > comparative list. You know SQLite does binding, right ? You don't have to construct your command like command$ = "INSERT

Re: [sqlite] Confusion re UPSERT syntax error

2019-01-16 Thread Simon Slavin
On 16 Jan 2019, at 4:59pm, David Raymond wrote: > Also note on quotes, single is for a text literal, double is for identifiers. > So it should be > insert into person ("name") values ('hello')... > or just plain > insert into person (name) values ('hello')... though if you're going to quote

Re: [sqlite] Link errors with SQLITE_OMIT_VIRTUALTABLE

2019-01-15 Thread Simon Slavin
On 15 Jan 2019, at 8:06am, Yehiel Zohar wrote: > A fix patch is attached to this mail. Sorry, but this mailing list automatically strips attachments. You can paste the contents of your patch file into a followup, or upload it to a server and post a link. Simon.

Re: [sqlite] SQLite error (5): database is locked

2019-01-14 Thread Simon Slavin
On 14 Jan 2019, at 7:04pm, Tim Streater wrote: > Hmm, from my attempt at interpreting the source code I gained the impression > that the handler used exponential backoff for the first few attempts, but > then used a constant period for the rest (up to the set limit). Is this not > the case?

Re: [sqlite] SQLite error (5): database is locked

2019-01-14 Thread Simon Slavin
On 14 Jan 2019, at 6:34pm, Thomas Kurz wrote: >> pragma_busy_timeout > > Does setting the busy_timeout retry periodically (e.g. every x milliseconds), > or is there some automatism that ensures that the requested operation is done > just-in-time as soon as the previous/blocking operation is

Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

2019-01-14 Thread Simon Slavin
On 14 Jan 2019, at 11:14am, Urs Wagner wrote: >ProviderConnectionString = @"data source=" + _dataBase + > ";PRAGMA foreign_keys = ON;PRAGMA locking_mode = EXCLUSIVE;PRAGMA > schema.synchronous = NORMAL; PRAGMA schema.journal_mode = DELETE; PRAGMA > busy_timeout = 10" I am

Re: [sqlite] SQLite error (5): database is locked

2019-01-14 Thread Simon Slavin
On 14 Jan 2019, at 9:23am, Urs Wagner wrote: > I use several tasks in C# to call Sqlite queries. > No I get the error SQLite error (5): database is locked. > Is it not possible to use more than one tasks with Sqlite? Please set a timeout for all connections to your database:

Re: [sqlite] SQlite.NET.chm

2019-01-09 Thread Simon Slavin
On 9 Jan 2019, at 9:33pm, Don V Nielsen wrote: > Is the chm file on a server? I remember this problem from years ago...I It's a long time ago, but I seem to remember being told that if I wanted to distribute a .chm file I should Zip it first. Simon.

Re: [sqlite] Arrangement of VACUUM, INTEGRITY_CHECK and OPTIMIZE

2019-01-09 Thread Simon Slavin
On 9 Jan 2019, at 2:01pm, spamh...@bluewin.ch wrote: > In which order should VACUUM, INTEGRITY_CHECK and OPTIMIZE be executed? If you are in any situation where INTEGRITY_CHECK should be run, do not make any changes to the database (e.g. ANALYZE) first. Those changes might overwrite parts of

Re: [sqlite] Feedback on article about SQLITE_BUSY

2019-01-09 Thread Simon Slavin
On 8 Jan 2019, at 3:48pm, Rahul Jayaraman wrote: > https://www.activesphere.com/blog/2018/12/24/understanding-sqlite-busy > > I’d appreciate feedback on the article. I read your article, though I did not go through all the logic and check that it is correct. I like your choice of sections,

[sqlite] LiteCLI

2019-01-05 Thread Simon Slavin
LiteCLI is a user-friendly command line client for SQLite with syntax highlighting and command-line completion. It's written in Python, and tested on Linux, MacOS and Windows. Simon. ___ sqlite-users

Re: [sqlite] Optimisation opportunity on subquery?

2019-01-02 Thread Simon Slavin
On 2 Jan 2019, at 9:50pm, Jonathan Moules wrote: > Sorry, but which column is ambiguous? The users.item_id is a foreign key to > the item_info.item_id - that's why it's a "REFERENCES" - why would I want to > change it to be something else? Isn't the convention for FK's to have the > same name

Re: [sqlite] Optimisation opportunity on subquery?

2019-01-02 Thread Simon Slavin
On 2 Jan 2019, at 4:44pm, Jonathan Moules wrote: > SELECT > * > FROM > item_info > JOIN (select count(1) from users group by item_id) > USING (item_id) > where item_id = ?; You have an ambiguous column name, and I don't think SQLite is doing what you think

Re: [sqlite] Using sqlite3_interrupt with a timeout

2018-12-31 Thread Simon Slavin
On 31 Dec 2018, at 10:44pm, Jesse Rittner wrote: > Simon Slavin-3 wrote > >> it can be long only if you're a bad SQL programmer > > To be fair, the query in question might not get run frequently enough to > warrant the overhead of maintaining an index

Re: [sqlite] Using sqlite3_interrupt with a timeout

2018-12-31 Thread Simon Slavin
On 31 Dec 2018, at 9:10pm, Jesse Rittner wrote: > Simon Slavin-3 wrote > >> You can calculate an "end time" yourself, check it each time you're ready to >> call sqlite3_step(), and jump straight to sqlite3_finalize() if your time is >> up. > >

Re: [sqlite] Using sqlite3_interrupt with a timeout

2018-12-31 Thread Simon Slavin
On 31 Dec 2018, at 8:18pm, Jesse Rittner wrote: > I'm trying to write a function to run a query with a timeout. If the timeout > expires, the query must stop execution within a "reasonable" amount of time. There is no rule that you must continue to call sqlite3_step() until it runs out of

[sqlite] Seasonal syntax

2018-12-24 Thread Simon Slavin
Some SQL terminology: Selection Clause: WHERE Sort Clause: ORDER BY Sublist Clause: LIMIT OFFSET Subsort Clause: GROUP BY HAVING Santa Clause: SELECT name,hobbies,address FROM people WHERE behaviour='nice’ Season’s greetings and best wishes to all

Re: [sqlite] Sample Employee database ported to SQLite from MySQL

2018-12-21 Thread Simon Slavin
On 21 Dec 2018, at 5:54pm, James K. Lowden wrote: > If the assigning body didn't intend the "number" as a quantity, it's > not. Treating it as such will often come to tears. I've seen this argument phrased as "Are you going to do maths on it ? If not, don't store it as a number.". And yes,

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

2018-12-20 Thread Simon Slavin
On 21 Dec 2018, at 12:40am, Jens Alfke wrote: > From what I’ve read, it sounds like any code using FTS3 was vulnerable to > maliciously crafted SQL statements messing with the shadow tables. Which would make it do what ? I can imagine "crash with a memory fault". I find it much harder to

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

2018-12-20 Thread Simon Slavin
On 20 Dec 2018, at 5:27pm, Jens Alfke wrote: > On Dec 19, 2018, at 10:32 AM, Simon Slavin wrote: > >> I'm not sure how you would do that purely inside a trigger. You can't just >> specially craft a BLOB with bad content. I think it would need >> participation

Re: [sqlite] Exclusive transactions and Select statements

2018-12-20 Thread Simon Slavin
On 20 Dec 2018, at 11:19am, Carsten Müncheberg wrote: > I am experimenting with EXCLUSIVE transactions today, and was confused that > after one connection began an EXCLUSIVE transaction, another one could still > execute a SELECT statement Your database is probably in WAL mode:

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

2018-12-19 Thread Simon Slavin
On 19 Dec 2018, at 6:19pm, Jens Alfke wrote: > 2. Mallory uses something like the ’sqlite3’ tool to open the database and > execute a CREATE TRIGGER statement whose trigger SQL exploits a vulnerability > to do something nasty like remote code execution. I'm not sure how you would do that

Re: [sqlite] SQLite Application Question

2018-12-19 Thread Simon Slavin
On 19 Dec 2018, at 6:02pm, Jens Alfke wrote: > Of course you can save the JSON in the database file. Just create a ‘prefs’ > table with one blob column for the JSON. > > A related solution is to store each named pref as a row in the ‘prefs’ table, > identified by a ‘key’ column. Or you could

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

2018-12-18 Thread Simon Slavin
On 18 Dec 2018, at 9:00pm, Peter da Silva wrote: > I have to say I'm pretty boggled that Chrome allows hostile users to feed > code directly into an SQL interpreter that wasn't written from the ground up > to be secure. Chrome has problems far more serious than that. And one can do all sorts

Re: [sqlite] Question about floating point

2018-12-17 Thread Simon Slavin
On 17 Dec 2018, at 5:16pm, James K. Lowden wrote: > IEEE > double-precision floating point is accurate to within 15 decimal > digits. First, the problem is not storage it's calculation. Second, the thread was started because a floating point calculation in SQLite, exactly as it is run today,

Re: [sqlite] Sqlite3 Data Base write failure cases.

2018-12-17 Thread Simon Slavin
On 17 Dec 2018, at 9:01am, Prajeesh Prakash wrote: > What are the possible failure cases of DB write in case of single connection. > Because in my application i needs to handle those cases. Can anyone help me > for the same. You state that you're using a single connection. I will assume

Re: [sqlite] Question about floating point

2018-12-16 Thread Simon Slavin
On 16 Dec 2018, at 2:54pm, Wout Mertens wrote: > imagine having to handle the Zimbabwean Dollar, which ended up having 100 > trillion dollar notes. Good way to overflow your integers. Indeed. But when the crisis started in the early 2000s, the currency was devalued by 1000. Then ten zeros

Re: [sqlite] Question about floating point

2018-12-15 Thread Simon Slavin
On 15 Dec 2018, at 5:35pm, Keith Medcalf wrote: > Fast forward 25 years and you would these days be hard pressed to find a > computer that DOES NOT use proper IEEE-754 floating point and that DOES NOT > default to a minimum of double precision representation and that DOES NOT use > extended

Re: [sqlite] Question about floating point

2018-12-15 Thread Simon Slavin
On 15 Dec 2018, at 10:15am, Frank Millman wrote: > Simon Slavin says ‘Currency amounts should be stored as integers’. Does this > apply to sqlite3 specifically, or is that your recommendation for all > databases? For anything that goes near a computer. I used to work with inte

Re: [sqlite] Question about floating point

2018-12-15 Thread Simon Slavin
On 15 Dec 2018, at 9:24am, Darren Duncan wrote: > If yours is a financial application then you should be using exact numeric > types only, such as integers that represent multiples of whatever quantum you > are using, such as cents; fractional numbers are a display or user input > format

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

2018-12-14 Thread Simon Slavin
On 14 Dec 2018, at 10:41am, Luuk wrote: > When SQLite realises it can't use that index, should it revert to a full > table scan? That's the bigger question. My theory is that SQLite doesn't want to touch that database unless it has all the collations it will need. So it errors out the

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

2018-12-14 Thread Simon Slavin
On 14 Dec 2018, at 10:15am, Luuk wrote: > why do i get 'no collation sequence' with this statement: > > sqlite> select Folder_Path from Folder; > Error: no such collation sequence: NoCaseUnicode There's a UNIQUE index for Folder_Path COLLATE NoCaseUnicode. Iterating through that index would

Re: [sqlite] add constant to INTEGER PRIMARY KEY

2018-12-13 Thread Simon Slavin
On 13 Dec 2018, at 10:39pm, Roman Fleysher wrote: > UPDATE table1 SET t = t + 8000; > > > Error: UNIQUE constraint failed: table1.t This means there's an actual example where one value for t is 8000 greater than another. Use a larger constant. Simon.

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

2018-12-13 Thread Simon Slavin
On 13 Dec 2018, at 2:57pm, Carlo capaldo wrote: > UPDATE folder > >SET Folder_Path = 'E:\Photos' > > would change the directory references in all existing 16 rows currently > containing Folder_Path references to the wrong locations to the correct > location ‘E:\Photos’ however this also

Re: [sqlite] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

2018-12-12 Thread Simon Slavin
On 12 Dec 2018, at 2:59pm, Olivier Mascia wrote: > When TimeMachine makes copies of the files, the database file and -wal file > will be copied at different points in time, albeit they should be copied from > a filesystem snapshot, so should be consistent to each other. But this should > be

Re: [sqlite] [EXTERNAL] Transaction commits hangs up

2018-12-11 Thread Simon Slavin
On 11 Dec 2018, at 1:21pm, Aydin Ozgur Yagmur wrote: > No, I did not use it. Then use it. By default, timeout is not set. If you do not set a timeout, any clash will cause the error you are seeing. Simon.

Re: [sqlite] [EXTERNAL] Transaction commits hangs up

2018-12-11 Thread Simon Slavin
On 11 Dec 2018, at 12:42pm, Aydin Ozgur Yagmur wrote: > I am trying to edit the database using command shell. > for instance i try to execute analyze commad i get "database is locked" > message Have you used the timeout PRAGMA inside the command shell ? If not, it's not retrying. Simon.

Re: [sqlite] Simple way to import GPX file?

2018-12-09 Thread Simon Slavin
On 9 Dec 2018, at 10:16pm, Winfried wrote: > sqlite> CREATE TABLE waypoints (name text, latitude text, longitude text, id > INTEGER PRIMARY KEY); If you expect to use this table in the long term, rather than just extract data from it, then define latitude and longitude as REAL. This will make

Re: [sqlite] Simple way to import GPX file?

2018-12-09 Thread Simon Slavin
On 9 Dec 2018, at 9:44pm, Winfried wrote: > I need to importe a GPX file that contains a few thousand waypoints, eg. > Some name Take a copy of the file, then edit the copy in a text editing program. Use global search/replace to turn it into csv format. For example, the above line would

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

2018-12-06 Thread Simon Slavin
On 6 Dec 2018, at 9:01pm, Carlo capaldo wrote: > UNIQUE constraint failed: Folder.Folder_Path: You seem to have two rows in the table: one with the correct Folder_Path and one with the incorrect Folder_Path. So instead of an UPDATE you just need a DELETE command like DELETE FROM folder

Re: [sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order

2018-12-05 Thread Simon Slavin
On 5 Dec 2018, at 12:22pm, Ryan Schmidt wrote: > Since I don't know what else may have gone wrong with the contents of the > registry by this point, it seems safer to erase the MacPorts installation and > start fresh. This will take a bit longer as every port has to be re-fetched > and

Re: [sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order

2018-12-04 Thread Simon Slavin
On 5 Dec 2018, at 5:16am, Ryan Schmidt wrote: > https://kb.vmware.com/s/article/1008542 > > "VMware ESX acknowledges a write or read to a guest operating system only > after that write or read is acknowledged by the hardware controller to ESX. > Applications running inside virtual machines on

Re: [sqlite] Corrupted database: On tree page 76852 cell 303: Rowid 18741471 out of order

2018-12-04 Thread Simon Slavin
On 5 Dec 2018, at 3:20am, Ryan Schmidt wrote: > $ sqlite3 /opt/local/var/macports/registry/registry.db > SQLite version 3.25.2 2018-09-25 19:08:10 > Enter ".help" for usage hints. > sqlite> .load /tmp/macports.sqlext > sqlite> pragma integrity_check; > *** in database main *** > On tree page

Re: [sqlite] [EXTERNAL] sqlite3_exec()

2018-12-03 Thread Simon Slavin
On 4 Dec 2018, at 4:57am, Prajeesh Prakash wrote: > How we can find the number of record that newly added to the table once after > the new recorded added(Just for a verification). Either as an API function or as a SQL function:

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

2018-12-02 Thread Simon Slavin
On 2 Dec 2018, at 7:29pm, E.Pasma wrote: > drop table x; > create table x(value INTEGER PRIMARY KEY) WITHOUT ROWID; > > insert into x select random() from generate_series where start=1 and > stop=1000; > Run Time: real 88.759 user 36.276227 sys 44.190566 Realtime is 88.759 > create table

Re: [sqlite] sqlite3_exec()

2018-12-01 Thread Simon Slavin
On 1 Dec 2018, at 1:50pm, Prajeesh Prakash wrote: > I have two thread one is for reading and other is for writing the DB. Both > thread have the same DB connection One connection can only execute one operation at one time. If you want simultaneous operations, use two connections. Simon.

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

2018-11-30 Thread Simon Slavin
On 29 Nov 2018, at 7:32pm, Carlo capaldo wrote: > select folder.Folder_Path, > replace(Folder_Path,'C:\Users\carlo\Pictures','E:\Photos') > from Folder > where folder.Folder_Id = 1 > > Could someone kindly educate me on how to update the folder references? My guess is that you actually want

Re: [sqlite] SQLITE_LOCKED and SQLITE_BUSY

2018-11-29 Thread Simon Slavin
On 29 Nov 2018, at 11:35am, Prajeesh Prakash wrote: > I am writing to a table 1 and reading from table 2 both operation are from > different DB connection i am getting SQLITE_LOCKED SQLite locks the entire database. It does not lock each table independently. > and when i try to read and

Re: [sqlite] how do i unsubscibe (eom)

2018-11-28 Thread Simon Slavin
Click the link at the bottom of every post to the list, including this one. Look near the bottom of that web page. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

2018-11-28 Thread Simon Slavin
Ignore multiprocessing for the inserting. You'll just get contention when accessing the database. And I think you are already trying the right PRAGMAs. I think you've done this already, but just in case ... Insert rows in batches. Experiment with the batch size: maybe a thousand INSERTs per

Re: [sqlite] SQLITE_OPEN_FULLMUTEX vs SQLITE_OPEN_NOMUTEX

2018-11-28 Thread Simon Slavin
On 28 Nov 2018, at 3:26pm, Prajeesh Prakash wrote: > Then is that SQLITE_LOCKED error will happen because of a conflict within the > same database connection. Or in case of two connection two separate thread > trying to do operation? Your software should never make two simultaneous API calls

Re: [sqlite] [EXTERNAL] SQLITE_OPEN_FULLMUTEX

2018-11-28 Thread Simon Slavin
On 28 Nov 2018, at 8:03am, Prajeesh Prakash wrote: > That means (with SQLITE_OPEN_FULLMUTEX) if two threads are trying to do write > and read to the table only one will get the chance to do the operation other > thread needs to wait until the first thread finish its job . Am i correct Are

Re: [sqlite] sqlite3_exec()

2018-11-27 Thread Simon Slavin
On 27 Nov 2018, at 2:43pm, Prajeesh Prakash wrote: > I have one doubt, Is that sqlite3_exec function is working under the main > application thread (Thread which calls the sqlite3_exec) or in a separate > thread (If i am trying to INSERT the data in to DB without registering the > Async

Re: [sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data

2018-11-26 Thread Simon Slavin
On 26 Nov 2018, at 9:09pm, Scott Perry wrote: > For Bill's purposes—investigating a copied, non-corrupt database—it would > probably be easiest to just convert from the Cocoa epoch to the Unix epoch by > updating all the columns that currently store Cocoa timestamps. Something > like: > >

Re: [sqlite] Group by counting 2 different sub-groups

2018-11-24 Thread Simon Slavin
On 25 Nov 2018, at 12:59am, Bart Smissaert wrote: > Could I post a little demo SQLite file? Not sure now if this is allowed as an > attachment. This mailing list strips attachments. You could use the SQLite CLI tool to .dump the database as a text file, and paste it into a message. If you

Re: [sqlite] Optimizing aggregation queries

2018-11-23 Thread Simon Slavin
On 23 Nov 2018, at 3:44pm, Wout Mertens wrote: > CREATE TABLE log(type TEXT, amount INTEGER) > SELECT type, SUM(amount), COUNT(*) FROM log GROUP BY type > > What would be good approaches to make the query fast? Create an index for "log(type, amount)". Simon.

Re: [sqlite] Is it possible to concatenate an arbitrary number of columns into one?

2018-11-20 Thread Simon Slavin
On 20 Nov 2018, at 11:54pm, Shane Dev wrote: > Is there an SQL statement to concatenate all columns into a single column > without explicitly naming them? No. And I can't think of any short-cut way to do what you want. Simon. ___ sqlite-users

Re: [sqlite] Creating and dropping tables with multiple connections

2018-11-20 Thread Simon Slavin
On 20 Nov 2018, at 4:32pm, Dominique Devienne wrote: > Hmmm, DDL is transactional in SQLite, AFAIK... --DD I was wrong. Thanks for the correction. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Creating and dropping tables with multiple connections

2018-11-20 Thread Simon Slavin
On 20 Nov 2018, at 3:34pm, Albert Banaszkiewicz wrote: > ExecuteInTransaction(writeDb1, KCreateTable); I can't answer your question but the above line shows a misunderstanding of SQL. Transactions are for commands which modify tables: INSERT, UPDATE, DELETE. Commands which modify the

Re: [sqlite] Suitability for Macintosh OS 9.2?

2018-11-19 Thread Simon Slavin
On 19 Nov 2018, at 1:58pm, Charles Hudson wrote: > I come from an ANSI SQL client / server background (Oracle, MS SQL) but am > interested in finding a SQL database to install on an old Macintosh G3 Power > PC that is running OS 9.2. OS 9 is dead dead dead. The last release was in 1999 and

Re: [sqlite] WAL file size increase

2018-11-13 Thread Simon Slavin
On 14 Nov 2018, at 4:20am, Sharma, Tanuj [AUTOSOL/FMP/IN] wrote: > I want to know why auto_checkpoint is not working when there is no Primary > Key in the table. If, during your testing, you're using existing database files, please create new ones each time. Can you please publish your

Re: [sqlite] Connect Failure: too many open files

2018-11-10 Thread Simon Slavin
On 10 Nov 2018, at 1:57pm, Simon Slavin wrote: > Do you test the code returned by sqlite_close() to make sure it is returning > SQLITE_OK and not an error ? Sorry, I forgot a possibility. If your application has a statement which is not terminated, it cannot close the database. For e

Re: [sqlite] Connect Failure: too many open files

2018-11-10 Thread Simon Slavin
On 10 Nov 2018, at 12:52pm, Paresh Sukhiya wrote: > 9. Whenever the app goes in background we Shut Down the SQLite connection and > on Activation we Initialize the SQLite again in Serialized mode. If the problem really is with SQLite, and not some other thing which is opening files, then my

Re: [sqlite] Segmentation Fault When Using Window Function

2018-11-09 Thread Simon Slavin
On 9 Nov 2018, at 3:35pm, Dennis Clarke wrote: > The list mail server sends this out about thirty times. I saw just one copy, so I think the problem is nearer your end of the route. Simon. ___ sqlite-users mailing list

Re: [sqlite] [EXTERNAL] found a glitch in ALTER TABLE RENAME (3.25.x)

2018-11-08 Thread Simon Slavin
On 9 Nov 2018, at 7:11am, Hick Gunter wrote: > Foreign keys are ignored by default and need to be explicitly enabled. I > would expect this to include everything that relates to foreign keys. > > -Ursprüngliche Nachricht- > > I've casually discovered that the behavior of ALTER TABLE

Re: [sqlite] sharing in memory db through threads

2018-11-05 Thread Simon Slavin
On 5 Nov 2018, at 3:28pm, Wasilios Goutas wrote: > To speed up pre-processing and importing of data I would like to grand > several threads access to the same in memory database I did not look at your source code, but you may not get the results you want. Using many threads/processes to

Re: [sqlite] curious discovery about geopoly module

2018-11-01 Thread Simon Slavin
On 1 Nov 2018, at 10:40am, R Smith wrote: > Most applications play nice and do not install their greasy little DLLs to > the Widows DLL common area, but just hug them locally (in the app folder). > But some, thinking probably they were doing the right thing, do install DLLs > to the common

Re: [sqlite] use column alias in same SELECT

2018-10-31 Thread Simon Slavin
On 31 Oct 2018, at 7:14pm, Thomas Kurz wrote: > may I ask whether there is a deeper reason why using a column alias in the > same SELECT query doesn't work, e.g. > > SELECT column1 AS a, 5*column2 AS b, a+b FROM... The canonical answer to this is that there's nothing in the SQL

[sqlite] Parallel reading can be slow on APFS

2018-10-29 Thread Simon Slavin
This post is about a problem with Apple's new APFS file system. The problem will affect you only if you have multiple reads/writes happening at the same time. The problem involves merely slowing of performance, not corruption of databases or incorrect answers being returned by SQLite.

Re: [sqlite] SQLite for datalogging - best practices

2018-10-28 Thread Simon Slavin
On 28 Oct 2018, at 11:47pm, Gerlando Falauto wrote: > On an SSD, I have no clue what's going on -- will writing a 2 MB block one > page at a time be much worse than writing an entire block at once? You do indeed have no clue. And so do I unless I use a utility to look at the low-level

Re: [sqlite] SQLite for datalogging - best practices

2018-10-28 Thread Simon Slavin
On 28 Oct 2018, at 2:06pm, Gerlando Falauto wrote: > There are two (apparently) opposing requirements: > - in case of a power outage, all data collected up to at most N seconds > prior to the power failure should be readable. Ideally N=0, but what's > important is that the database never gets

Re: [sqlite] Optmize queries on ranges

2018-10-25 Thread Simon Slavin
On 25 Oct 2018, at 5:13pm, siscia wrote: > CREATE TABLE ranges ( >start int, >end int, >value int, > ); > > The query that I am interested in optimizing is > "select value from ranges > where (? between start and end)" First, "END" is a reserved keyword in SQLite. Your use of

Re: [sqlite] Regarding CoC

2018-10-24 Thread Simon Slavin
On 25 Oct 2018, at 12:16am, Philip Warner wrote: > t leads to the question: have you ever seen one that works better than, say, > "Don't be an arsehole/dick/evil"? In most jurisdictions sexual harassment, > murder etc are already illegal...so repeating them in a CoE/C seems redundant. The

Re: [sqlite] Index help...

2018-10-23 Thread Simon Slavin
On 23 Oct 2018, at 11:45pm, Hamesh Shah wrote: > CREATE INDEX `detected_model_id_confidence_ts` ON `detected` ( > `model_id`, > `confidence` ASC, > `ts` ASC > ); Create another index with the fields in this order > `model_id` ASC, > `ts` ASC, > `confidence` ASC and try it again. By the way,

Re: [sqlite] Help!

2018-10-22 Thread Simon Slavin
On 22 Oct 2018, at 10:43pm, Petite Abeille wrote: > You could try the following perhaps: > > Import a CSV File Into an SQLite Table > http://www.sqlitetutorial.net/sqlite-import-csv/ That is a good page. Documentation for the CLI tool it refers to can be found in chapter 8 of

Re: [sqlite] Regarding CoC

2018-10-22 Thread Simon Slavin
On 22 Oct 2018, at 10:05pm, Richard Hipp wrote: > It even made TheRegister: > https://www.theregister.co.uk/2018/10/22/sqlite_code_of_conduct/ And now has reached the summit of Slashdot's front page:

Re: [sqlite] Custom serialization/deserialization of in-memory DB

2018-10-22 Thread Simon Slavin
On 22 Oct 2018, at 7:30pm, Zoltan Demeter wrote: > Or if I could tell SQLite to use custom file I/O routines ... I could mock > fwrite() to just write to memory and then grab the data. You can tell indeed SQLite to use custom file I/O routines ... by writing your own virtual filesystem (VFS):

Re: [sqlite] Regarding CoC

2018-10-22 Thread Simon Slavin
On 22 Oct 2018, at 5:32pm, someone wrote: > I would vote for a major simplification There's no point. The point of the code a posted is that it is a direct copy of the code of St. Benedict, the rules (allowing for translation from the Latin) which have been accepted by Benedictine monks for

Re: [sqlite] Regarding CoC

2018-10-22 Thread Simon Slavin
On 22 Oct 2018, at 1:19pm, Richard Hipp wrote: > Looks like that happened this morning. > https://news.ycombinator.com/item?id=18273530 It also hit Reddit, in /r/programming. Currently 239 comments:

Re: [sqlite] SQLITE_OPEN_READONLY in PHP

2018-10-20 Thread Simon Slavin
On 20 Oct 2018, at 5:05pm, p...@geniais.com wrote: > I'm using a C compiled program to write exclusively to a database, and a php > script for reading only (that communicates via socket). > I know I can use sqlite3_open_v2() with SQLITE_OPEN_READONLY in C, but there > is a way to open a

Re: [sqlite] database is locked

2018-10-20 Thread Simon Slavin
On 20 Oct 2018, at 1:19pm, p...@geniais.com wrote: > I'm using exec() to simply get true/false result (just reading) and I'm > receiving this error, even in WAL mode: > SQLite3::exec(): database is locked in ... on line 47 > > Any help will be apreciated I'm assuming that you are aware that

Re: [sqlite] Regarding CoC

2018-10-19 Thread Simon Slavin
On 19 Oct 2018, at 6:26pm, Andrew Brown wrote: > I looked it up, wondering what it would say, and I have to say, I love it. Yeah, that's gonna magically appear on Hacker News within the month. For those curious ... Simon.

Re: [sqlite] Upsert syntax question

2018-10-19 Thread Simon Slavin
On 19 Oct 2018, at 5:24pm, David Raymond wrote: > sqlite> insert into ids (id, in_new) select distinct id, 1 from gc_new on > conflict (id) do update set in_new = 1; You can't supply a statement for ON CONFLICT. It can only trigger IGNORE / FAIL / ABORT / ROLLBACK / REPLACE.

Re: [sqlite] A couple of questions about prepared statements

2018-10-19 Thread Simon Slavin
On 19 Oct 2018, at 3:43pm, Tim Streater wrote: > is it OK to generate the myid and goodtext parts using the usual string > methods from my host language, leaving only badtext as a bound variable, so > that my prepared statement looks like this: > > select somecol from mytable where myid=3 and

Re: [sqlite] Why operations with in-memory DB are much faster than with on-disk DB?

2018-10-18 Thread Simon Slavin
On 19 Oct 2018, at 5:55am, Yuri wrote: > I noticed that my DB import process is much slower when run on the DB on > disk, vs. in memory. It reads files and runs a massive amount of > inserts/updates. Memory access is much faster than disk access. If you're using actual spinning hard disks,

Re: [sqlite] Using SQLite in C# without System.Data.SQLite

2018-10-18 Thread Simon Slavin
On 19 Oct 2018, at 2:23am, Larry Brasfield wrote: > Jordy Deweer asks about alternatives to System.Data.SQLite because: “I run > into errors a lot, using the System.Data.SQLite libraries. It easily crashes, > trhows exceptions and similar issues...”. System.Data.SQLite is generally considered

Re: [sqlite] Hot-Journal with VFS

2018-10-18 Thread Simon Slavin
On 18 Oct 2018, at 3:27pm, Bob schwanzer wrote: > DB is > opened by 10-20 processes each of which can have multiple threads. What OS are you using ? What programming language are you using ? Are you calling the SQLite C library directly or using a shim ? Does your program close each database

Re: [sqlite] Bug report: bug in datetime conversion function

2018-10-18 Thread Simon Slavin
On 18 Oct 2018, at 2:28pm, Fábio Pfeifer wrote: > When working with Apple iOS databases, I found something strange when dealing > with dates. I suspect you are you are referring to a library routine which is used by lots of iOS software, but can you give us a specific App which exhibits this

Re: [sqlite] Strip leading "The" in alphabetical TOC?

2018-10-18 Thread Simon Slavin
On 18 Oct 2018, at 7:17pm, Charles Leifer wrote: > In the documentation alphabetical listing, it threw me off when I was lookup > up the JSON1 docs and didn't find them under "J", due to the title being "The > JSON1 Extension". Some years ago I wrote a TITLE Collating Sequence for SQLite. I

Re: [sqlite] Fill empty space with random

2018-10-14 Thread Simon Slavin
On 14 Oct 2018, at 3:24pm, J Decker wrote: > B and C would apply if there was a vacuum also; adding data, and deleteting > data, the db ends up with lots of zeros > Also between non-integral pages; messages that are say 700 bytes; so 4096% > 700 is 596; which is all filled with zeros...

Re: [sqlite] Fill empty space with random

2018-10-14 Thread Simon Slavin
On 14 Oct 2018, at 12:56pm, J Decker wrote: > Is there maybe a compile option for sqlite to fill empty space in a db with > random data rather than 0 ? There is not. But (A) It may be an easy change to the source code (B) Your operating system may have a setting to do this automatically to

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

2018-10-09 Thread Simon Slavin
On 9 Oct 2018, at 10:38pm, Keith Medcalf wrote: > And just what is NSFW spam? Messages purporting to come from young women. Some including images purporting to be them in various states of undress. Simon. ___ sqlite-users mailing list

Re: [sqlite] SQLite mailing list

2018-10-09 Thread Simon Slavin
On 9 Oct 2018, at 9:49pm, Warren Young wrote: > Also: This list may be an unusually juicy target, given the number of places > SQLite is deployed. The minute SQLite gains any sort of internet connectivity, a hundred thousand man-hours of cracking attempts will be launched. Which is why it's

Re: [sqlite] Question about a query

2018-10-09 Thread Simon Slavin
On 9 Oct 2018, at 2:47pm, Leonardo Inácio de Freitas wrote: > Using SQLite, can you use masks (or regex) (like '% str%') inside > instr / substr, to delimit the output of a select, instead of me > determining the beginning and end of the substring? No. Sorry. You have to use string core

Re: [sqlite] SQLite Windows GUI alternative to Excel?

2018-10-06 Thread Simon Slavin
On 6 Oct 2018, at 8:04pm, Luuk wrote: > Even a simple program (or (gui?)-interface on top of sqlite will not > stop those Excel-whizz-kids from crafting things in Excel. Excel ate the financial business world because companies use Excel to solve a simple problem, then add a feature, then add

  1   2   3   4   5   6   7   8   9   10   >