[sqlite] Customizing the location of the .sqlite_history

On Sun, 31 Jan 2016 21:46:18 -0800 Jes Slow wrote: > Many applications do this by allowing the user to set an environment > variable to customize the location, altho personally I would prefer > another way since environment variables are also global. Global? Environment variables are

[sqlite] Documentation comment: VFS

On Mon, 1 Feb 2016 14:24:28 +0200 R Smith wrote: > > does it say what "VFS" stands for. Please don't tell me...// > > I'm going to ignore the request and tell you anyway: It clearly > stands for "Variably F-Syncing Software". Oh, come now. In the tradition of RTMF, surely it's Very

[sqlite] Bug: Successfully committed transaction rolled back after power failure

On Sun, 31 Jan 2016 20:27:56 -0700 Scott Robison wrote: > On Sun, Jan 31, 2016 at 7:35 PM, Rowan Worth wrote: > > > On 31 January 2016 at 03:56, James K. Lowden > > wrote: > > > > > Surely SQLite does both -- fsync on file and directory -- as part >

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

On Sat, 30 Jan 2016 23:03:29 + Simon Slavin wrote: > On 30 Jan 2016, at 8:13pm, Yannick Duch?ne > wrote: > > > In my opinion (which some others share), OO is a bag of > > miscellaneous things which are better tools and better understood > > when accosted individually. Just trying to define

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

On Sat, 30 Jan 2016 20:50:17 -0500 Jim Callahan wrote: > I am not interested in a complete ORM; what I am interested is when > the object-oriented language supports a SQL-R-like object. In R, the > object is called a data.frame and the package "Pandas" supplies a > similar data frame object to

[sqlite] Bug: Successfully committed transaction rolled back after power failure

On Sat, 30 Jan 2016 20:00:19 + Simon Slavin wrote: > On 30 Jan 2016, at 7:56pm, James K. Lowden > wrote: > > > Given that the fsync has returned successfully, I don't know of any > > hardware that then will take 1000 ms to complete the write. That's > >

[sqlite] Find out how many times does SQLite hit the disk?

On Sun, 31 Jan 2016 03:01:30 +0530 dpb wrote: > This will help me in deciding if moving to In-memory SQLite will > improve my application performance. I am done with adding indexes to > my tables in SQLite DB. I second Simon's question. If SQLite isn't fast enough, a good starting assumption

[sqlite] IS a SQLite db of small size as good as reliable cache?

On Sat, 30 Jan 2016 22:23:24 + Howard Chu wrote: > Note that the cache eviction runs quite frequently - once every 5 > seconds or so, and evicts pages regardless of whether there's any > memory pressure in the system. It's quite possibly the stupidest > cache manager ever written. Any

[sqlite] Is the first column of a composite primary key, special?

On Sun, 31 Jan 2016 17:39:28 +0100 Yannick Duch?ne wrote: > I saw a page (can't retrieve the URL) suggesting to order table > columns by names. It was strange to me, as I had the idea of a > hierarchical access for tables access. But I though ?there must be a > good reason for them to say this?.

[sqlite] Is the first column of a composite primary key, special?

On Sun, 31 Jan 2016 10:45:59 -0700 "Keith Medcalf" wrote: > Hierarchical, Network, and Network Extended database models use > pointers in sets rather than duplicating the data. This makes them > orders of magnitude faster (when properly designed) than a Relational > Model database, I was

[sqlite] Wish List for 2016: High Level API for Object Oriented Interactive Languages

On Thu, 28 Jan 2016 16:47:40 -0500 Jim Callahan wrote: > I am hopeful this new JDBC based interface will provide as > satisfactory high level channel between SQLite3 and Python. As someone who's written a couple of OO DBMS libraries and uses the Python SQLIte module, I wonder what you're hoping

[sqlite] Bug: Successfully committed transaction rolled back after power failure

On Thu, 28 Jan 2016 08:00:08 + Meinlschmidt Stefan wrote: > > But I ask you, what action could the application possibly take, in > > that subsecond interval, that it matters? > > Under the QNX OS using a QNX6 filesystem with default configuration, > that ?subsecond interval? is actually up

[sqlite] Bug: Successfully committed transaction rolled back after power failure

On Wed, 27 Jan 2016 08:51:16 + Bernard McNeill wrote: > Using the standard defaults (which avoid WAL), is there any > possibility whatsoever of that last SQL transaction being lost? I have an unusual answer: Yes, and it doesn't matter. Let's suppose, as you did, that the application got

[sqlite] Slight problem with sqlite3_compileoption_get

On Sun, 24 Jan 2016 21:26:41 + Bart Smissaert wrote: > http://www.cdecl.org/ > > It doesn't work though with complex arguments like this: > void (*xFunc)(sqlite3_context*,int,sqlite3_value**) In isolation, that's a syntax error because e.g. sqlite3_value is not defined (on that line,

[sqlite] Find SQLITE_BUSY reason?

On Fri, 22 Jan 2016 06:24:08 + Simon Slavin wrote: > > On 22 Jan 2016, at 4:01am, Rowan Worth wrote: > > > To a point I agree, but in reality there's a fixed amount of work > > involved with each write transaction. I recently profiled an > > operation involving ~75,000 rows that took ~8.5

[sqlite] Improving performance of my windows service using SQLite DB to save settings

On Thu, 21 Jan 2016 22:21:31 +0530 Deepak wrote: > How can improve the service performance here? Keeping in mind, > > >- There will be no new inserts (ignoring addition of few hundred > URLs once a day while overwriting the older ones). Size of the DB > will be more or less constant in

[sqlite] syntax error on SELECT with WHERE EXISTS

On 1/18/2016 1:59 PM, R Smith wrote: > > > On 2016/01/18 11:42 PM, James Walker wrote: >> Why do I get a syntax error from this SQL? >> >> SELECT * FROM SnappedFrames WHERE EXISTS (SELECT 1 FROM >> FilmSizeOverrides); >> >> SQLite says the error

[sqlite] syntax error on SELECT with WHERE EXISTS

Why do I get a syntax error from this SQL? SELECT * FROM SnappedFrames WHERE EXISTS (SELECT 1 FROM FilmSizeOverrides); SQLite says the error is near SELECT, but doesn't say which SELECT. If I say either SELECT * FROM SnappedFrames WHERE 1; or SELECT 1 FROM FilmSizeOverrides; then there's

[sqlite] POSIX and MMU (was Re: out of the void: xDlSym)

On Sat, 16 Jan 2016 14:21:26 -0700 Scott Robison wrote: > > Huh. An example of which is the "medium model" of the Intel 8086: > > 20-bit code pointers and 16-bit data pointers. A machine for which > > C compilers existed, and on which no Posix system will ever run > > (because it lacks an

[sqlite] Updating the contents of a field in table A with the value of the same field in table B

On Sat, 16 Jan 2016 14:26:20 -0700 "Keith Medcalf" wrote: > > the result is nondeterministic if more than one row in S matches. > > The update applies all rows in S matching T. Of course, only the > > last one is preserved. Of course, because order is nonsemantic, > > there's no way to know

[sqlite] Updating the contents of a field in table A with the value of the same field in table B

On Sat, 16 Jan 2016 10:18:28 +0200 audio muze wrote: > Why is it that SQLite does not support a FROM clause in an update > statement? I can't answer why, but I can tell you it's fraught with potential error. Inventing syntax runs the risk of supporting undesirable behavior. SQL Server has

[sqlite] out of the void: xDlSym

On Fri, 15 Jan 2016 21:41:41 -0500 Richard Damon wrote: > there are machines where it doesn't work (you just need a larger > program space than data space). Huh. An example of which is the "medium model" of the Intel 8086: 20-bit code pointers and 16-bit data pointers. A machine for which C

[sqlite] out of the void: xDlSym

I spent a fair number of hours scrutinizing xDlSym today, and I'd just like to confirm my understanding. Despite having worked with C on and off since the Reagan administration, I was unprepared for void (*(*xDlSym)(sqlite3_vfs*,void*, const char *zSymbol))(void); IIUC xDlSym is a pointer

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

On Fri, 15 Jan 2016 20:39:15 +0100 Olivier Mascia wrote: > write transactions committed after the read transaction began, are > not seen by that read transaction. Allowing it to have a stable view > on the whole database. As Keith correctly surmised, you need repeatable read. Never having

[sqlite] Best way to terminate a dead-transaction: commit or rollback?

On Thu, 14 Jan 2016 16:54:04 +0100 Olivier Mascia wrote: > Let a transaction (started with BEGIN TRANSACTION) which did only > reads. Is it any better to end it by COMMIT TRANSACTION or ROLLBACK > TRANSACTION, or is it completely insignificant? Why not do the right thing and remove the begin &

[sqlite] whish list for 2016

On Fri, 8 Jan 2016 08:28:29 +0100 Dominique Devienne wrote: > > One way to do that would be to honor a special user-created table, > > say "PRAGMAS", with name-value pairs that are automatically applied > > when the database is opened. > > > > Interesting idea. A "special"-to-SQLite

[sqlite] whish list for 2016

On Sun, 20 Dec 2015 19:12:39 +0100 Big Stone wrote: > Best whishes for 2016! If it's not too late to join the party... 1. create table T (t primary key); update T set t = t+1; for consecutive values of t. 2. Specific constraint references in error messages. 3. Correct

[sqlite] Problem with accumulating decimal values

On Wed, 16 Dec 2015 15:05:34 +0100 "E.Pasma" wrote: > 16 dec 2015, Keith Medcalf: > >> BEGIN; > >> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45; > >> (repeat a 1.000.001 times > >> END; > >> SELECT bal FROM fmtemp; > >> 123450123.45 > > > > You should NEVER round as you have done above. You

[sqlite] Problem with accumulating decimal values

On Wed, 16 Dec 2015 20:33:40 +0200 R Smith wrote: > > Ok this does not work of any scale of numbers. But a solution with > > integers neither does > > I think the bit that Keith tried to highlight is that we should > always refrain from storing errors. Keith recommended against storing

[sqlite] Bug with DATETIME('localtime')

On Sun, 13 Dec 2015 20:11:32 -0700 Scott Robison wrote: > > It's not fixed, although gacial progress is being made. Even though > > we've had the TZ database & Posix datetime functions since 1986, 30 > > years later we're still struggling with it, and not only on Windows. > > The problem would

[sqlite] Problem with accumulating decimal values

On Fri, 11 Dec 2015 16:21:30 +0200 "Frank Millman" wrote: > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 5925.599 To a question like that you'll receive a lot of answers about numerical accuracy. And it's true that there are ways to "do the

[sqlite] Bug with DATETIME('localtime')

On Thu, 10 Dec 2015 06:34:44 -0700 "Keith Medcalf" wrote: > The only way to convert datetime data on windows is to use a > third-party package that does it properly, or write it yourself. > Using the WinAPI functions is equivalent to "writing it yourself" > because they do not actually do

[sqlite] ABOUT ROWID

On Fri, 11 Dec 2015 05:14:24 -0700 "Keith Medcalf" wrote: > Far better is to run the queries multiple times in succession (say, > 1000) and then average the result. Good advice. Sometimes it seems like caching is "cheating": we don't know the performance of something if we're using the cache.

[sqlite] regular expression in check constraint?

I would like to add a check constraint which determines if a string contains all digits, ie. sqlite> select zip_code from zip_codes where regexp(zip_code, '^[[:digit:]]+$'); However, this generates the error: Error: no such function: regexp Searching through sqlite.org points that this

[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?

On Tue, 17 Nov 2015 22:29:10 -0500 Richard Hipp wrote: > With CHECK, and UNIQUE, and NOT NULL constraints, you do know that the > constraint has failed right away. And for those constraints, SQLite > does provide more detail about exactly which constraint failed. But > for FK constraints, you

[sqlite] [AGAIN] SQLite on network share

On Fri, 13 Nov 2015 13:19:33 -0800 Roger Binns wrote: > On talking to sites that had the competitor devices, we'd find they > did notice increases in programs crashing and data file issues, but > had written it off as the kind of thing that happens with Windows. Q: Why doesn't Microsoft

[sqlite] sql programming help

I'm doing a select like this: select e.name, e.release from execution as e where run_id in (select substr(status,7,5) from resource where status like "Busy (%"); I want to get to get a column from each row of the subselect (from resource) to match each result row. resource table: name status

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

On Tue, 10 Nov 2015 22:20:59 +0100 "Christian Werner" wrote: > When SQLite is the spoon, will they give us Linux binary > compatibility for the fork? Fork? Isn't that what CreateProcess is for? --jkl

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

On Tue, 10 Nov 2015 13:45:52 -0700 Warren Young wrote: > This from the same company that gave us ODBC, ESQL, OLE DB, MDAC/Jet, > DAO, RDO, ADO, ADO.NET, ADO Entity Framework, LINQ, the registry, > Access, SQL Server Express? The real irony is that every one of those technologies had (I'll bet)

[sqlite] Simple Math Question

On Fri, 06 Nov 2015 22:16:57 -0700 "Keith Medcalf" wrote: > I wrote a function called "ulps" which can be used as an extension to > SQLite3 Bravo, Keith! One suggestion, if I may. If you expect "ulps" to be used to test for equality, perhaps it would be more convenient to ignore the sign.

[sqlite] Question about Style

On Fri, 30 Oct 2015 14:08:16 -0400 Ramar Collins wrote: > I'm using C and a small library to get the majority of the work > done. My question is, do you have any suggestions or know where to > find more lore on how to nicely embed SQL in a program like this? I store my SQL in separate files,

[sqlite] Simple Math Question

On Fri, 23 Oct 2015 10:43:44 -0700 Scott Hess wrote: > You're right, any base-2 representation right of the decimal should be > precise to represent in base-10. But it's the kind of thing where if > you find yourself counting on it, you probably made a grave error > earlier in your design :-).

[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

On Tue, 27 Oct 2015 12:24:03 +0100 Clemens Ladisch wrote: > > You've solved the problem by compiling for a single-threaded > > environment. > > No; the single-threaded environment is the problem. That's a matter of opinion. Another way to look at it: threads set back computing by 20 years.

[sqlite] Problems making sqlite.lib using Embarcadero C++ 7.00

On Thu, 22 Oct 2015 15:23:38 +0200 "Marco Turco" wrote: > The problem is when I link the generated library. I receive the > following error related to the first two warnings so I'm unable to > generate the executable file. You've solved the problem by compiling for a single-threaded

[sqlite] Is it legal SQL to select a specific row of a group via a HAVING clause?

On Tue, 29 Sep 2015 15:05:42 +0800 Rowan Worth wrote: > Imagine a DB storing a history of currency exchange rate info. > > CREATE TABLE Prices ( > id INTEGER PRIMARY KEY, > day INTEGER, > currency TEXT, > price FLOAT); Better for your purpose would be: CREATE TABLE Prices (

[sqlite] UPDATE silently failing

On Mon, 21 Sep 2015 17:44:13 -0400 Hugues Bruant wrote: > UPDATE cv SET cv_t=? where cv_s=? and cv_o=?; > > Most of the time the row is updated as expected but in some rare cases > we've seen this statement fail silently, as in: > - the row exists > - the row it is not updated > - step

[sqlite] How to know when a sqlite3_stmt has been recompiled ?

On Mon, 21 Sep 2015 11:19:16 +0200 "R.Smith" wrote: > On 2015-09-21 01:53 AM, Nicolas J?ger wrote: > > hi, > > > > > > Scott Robison wrote: > > > >> 3. Some time passes and some external process may alter the schema. > >> > > is it really possible ? if the answer is yes (I thought when a > >

[sqlite] disable update's & delete's via triggers?

...which states that INSTEAD OF triggers are to be used with views, not tables. Is this still true? I'm using SQlite 3.8.9. Thanks, again. On Sun, Sep 20, 2015 at 6:34 PM, Simon Slavin wrote: > > On 21 Sep 2015, at 12:21am, James Hartley wrote: > > > I am assuming

[sqlite] I don't understand how to use NOT EXISTS

On Sun, 20 Sep 2015 19:33:35 +0100 Simon Slavin wrote: > On 20 Sep 2015, at 7:15pm, James K. Lowden > wrote: > > > Simon Slavin wrote: > > > >> Constructions like this > >> > >>> INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) > >>

[sqlite] disable update's & delete's via triggers?

On Sun, Sep 20, 2015 at 5:20 PM, Simon Slavin wrote: > > Then your next concern is over how to zero-out the log if you really > needed to do that. > > That is a valid concern, however, I am assuming that dropping the trigger will re-enable any action which had been disabled by creating the

[sqlite] Handling the whole select query from an index

On Sat, 19 Sep 2015 14:24:24 +0100 Rob Willett wrote: > Thanks. We?ve got 100 requests a second which may be enough. We?ll > keep looking though for any time. I suppose you know that 100/sec is about 0.1% of what the machine is capable of. You spoke of read-only data that changes

[sqlite] disable update's & delete's via triggers?

As I look at the flowchart for triggers at the following: https://www.sqlite.org/lang_createtrigger.html It appears that the body of a trigger must specify some kind of statement. I ask because I am wanting to log all activity on another table. I can successfully do this with triggers. What I

[sqlite] I don't understand how to use NOT EXISTS

On Wed, 16 Sep 2015 08:23:04 +1000 Barry Smith wrote: > As for your original query: think about just the select clause (you > can run it independently). This will return ('magnetohydrodynamics', > 1) for each row in the table. It took me a bit to understand what you meant. I also think

[sqlite] I don't understand how to use NOT EXISTS

On Wed, 16 Sep 2015 19:40:23 +0100 Simon Slavin wrote: > Constructions like this > > > INSERT INTO _TAG_EXISTS_RESULT_ ( NAME, BOOL ) > > SELECT 'evil little sister' > > should be rewritten so that you are not trying to do a SELECT in the > middle of your INSERT. Why in the world would you

[sqlite] bug in PRAGMA ignore_check_constraints?

On Fri, 11 Sep 2015 02:02:05 +0100 Simon Slavin wrote: > Looking at > > > > maybe the 'constraints' that the documentation refers to are the ones > specifically declared using CHECK in the table definition. Perhaps > NOT NULL and UNIQUE

[sqlite] Third test of json and index expressions, now it works

On Sun, 13 Sep 2015 10:46:21 +0200 Clemens Ladisch wrote: > James K. Lowden wrote: > > select a, r from ( > > SELECT a, random() as r FROM a > > ) as R > > WHERE r <> r; > > > > will yield zero rows, every time. > > $

[sqlite] Third test of json and index expressions, now it works

On Wed, 09 Sep 2015 09:56:12 +0200 "Domingo Alvarez Duarte" wrote: > With your knowledge could you give your view about how evaluation of > calculated/function columns should be done to have correct results. ... > CREATE TABLE a(b); ... > SELECT a, random() as r FROM a WHERE r <> r;

[sqlite] Third test of json and index expressions, now it works

On Sat, 5 Sep 2015 09:07:11 -0700 Darko Volaric wrote: > I'm asking why the SQL standard restricts the use of aliases in this > way and what the benefit of this restriction is. Rationales in SQL are hard to come by. The language was promulgated by a private firm, and the standard evolved under

[sqlite] Variables in statements

On Thu, 03 Sep 2015 16:44:50 + Peter Haworth wrote: > The statement I'm using is: > > SELECT * FROM myTable WHERE myKey IN (:1) > > If the value I supply to be used as :1 is a single integer, the SELECT > finds the correct rows. If the value is a comma separated list of > integers, e.g

[sqlite] order by not working in combination with random()

On Wed, 26 Aug 2015 13:39:09 +0100 Simon Slavin wrote: > > On 26 Aug 2015, at 11:27am, tonyp at acm.org wrote: > > > Plus, it apparently recognizes if the random() expression in the > > ORDER BY is the same as the SELECT one and again sort correctly > > (without re-evaluating) and without

[sqlite] order by not working in combination with random()

On Mon, 17 Aug 2015 12:01:58 +0200 Clemens Ladisch wrote: > Just because the ORDER BY clause refers to a column of the > SELECT clause does not mean that the value is not computed > a second time. Let's at least recognize that as a bug. ORDER BY shouldn't interpret SQL or invoke functions.

[sqlite] Boolean constraint regression between 3.7.7.1 and 3.8.6?

On Mon, 17 Aug 2015 17:19:49 +0200 Olivier Barthelemy wrote: > CREATE TABLE VariableDetails (dtl_id INTEGER PRIMARY KEY ASC > AUTOINCREMENT, storage_implicit BOOLEANCHECK (storage_implicit = > 'true' OR storage_implicit = 'false'), storage_type TEXT NOT NULL); > > Insert statement > INSERT INTO

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

On Sat, 15 Aug 2015 01:17:28 +0100 Simon Slavin wrote: > > BTW, Posix is almost silent on the question. It says TMPDIR will > > define the location of a temporary store, but not how. > > I'm okay if the documentation simply says something like ... > > For Darwin (Mac), it's always /tmp/ >

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

On Mon, 27 Jul 2015 20:35:30 +0100 Simon Slavin wrote: > On 27 Jul 2015, at 8:03pm, Zsb?n Ambrus wrote: > > > Does this work with the same environment variable name on both unix > > and windows? I'm asking because unix and windows programs each use > > different conventions for what

[sqlite] Tomcat 6 consumes too much memorys

The same query and database are fine if running from the command line sqlite3 or from java command line (via jdbc). Any one has the same issue please? Thanks a lot in advance -- James Qian Wang Mobile: 44 7986 099 233

[sqlite] Query tooooo sloooooooooow

I agree. Thank you all. On Thu, Jul 30, 2015 at 3:44 PM, Clemens Ladisch wrote: > James Qian Wang wrote: > > 0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2 > > 0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?) > > There is no more eff

[sqlite] Query tooooo sloooooooooow

, 2015 at 11:03 AM, Clemens Ladisch wrote: > James Qian Wang wrote: > > select count(*) from contact c left join history h on (h.elid=c.elid); > > Please show the output of EXPLAIN QUERY PLAN for this query. > > If it does not look like this: > 0|0|0|SCAN TABLE contact A

[sqlite] Query tooooo sloooooooooow

ontact CREATE TABLE contact (name varchar(32), email varchar(128) unique , elid int(12)); CREATE INDEX elid2 on contact (elid); Table size: contact 20m history 10m elid integer and each elid has about 20k records Ideas please? Thanks a lot in advance -- James Qian Wang Mobile: 44 7986 099 233

[sqlite] Suggestions for Fast Set Logic?

On Sat, 11 Jul 2015 19:02:59 -0600 Scott Robison wrote: > > I don't follow you. A complex query is an assemblage of clauses. > > Whether or not broken down "to individual sub queries", the search > > arguments are the same. They are few in kind, even though they may > > be large in number.

[sqlite] Suggestions for Fast Set Logic?

On Fri, 10 Jul 2015 09:54:27 -0600 Scott Robison wrote: > As described, the user will be able to create arbitrarily > complex queries. Since it is impossible to know in advance what > indexes might be required, breaking it down to individual sub queries > with simple where clauses that can be

[sqlite] Suggestions for Fast Set Logic?

On Thu, 9 Jul 2015 22:28:04 +0100 Simon Slavin wrote: > If you're going to do it properly you have a keywords column which > contains strings like > > , > ,animal, > ,animal,predator, > ,animal,predator,bird, Why do it that way? I would recommend a schema like Keith's, with one keyword per

[sqlite] Slow real world performance - Any suggestions please (warning long)

On Fri, 3 Jul 2015 11:35:21 +0100 Rob Willett wrote: > It basically has taken no time to run 10,551 selects. This implies > that we had issues on the database before with either fragmentation > or indexes being poorly setup. Perhaps you said so and I missed it, but to me it's clear that

[sqlite] Could frequent 'database is locked' errors mean SQLite is not a good fit for my application?

On Thu, 2 Jul 2015 10:09:12 -0400 Kathleen Alexander wrote: > Essentially, I have written an application in C++ that interfaces > (reads and writes) with a SQLite database, and I am getting lots of > 'database is locked' errors. [...] > > My application runs on Linux (ubuntu 13.10), and is

[sqlite] Question about the list

On Fri, 19 Jun 2015 12:14:22 +1000 wrote: > Some of the messages I receive have been cross-posted to two lists > (eg from jkl): > > sqlite-users at mailinglists.sqlite.org > sqlite-users at sqlite.org Oy, sorry for the confusion. There were old settings in my mailer, thanks for setting me

[sqlite] Mozilla wiki 'avoid SQLite'

On Wed, 17 Jun 2015 22:05:12 -0700 Darren Duncan wrote: > I also believe the world is ripe to have SQL alternatives, its just > a matter of ones appearing that are compelling to users for real work > and not just an academic exercise. The fact we're still generally > with SQL means this hasn't

[sqlite] Is recursive CTE fully capable?

On Mon, 15 Jun 2015 11:03:17 +1000 wrote: > >>>Unless the recursion is circular, I don't see how an SQL query > >>>over a finite database could fail to terminate. > > What does this mean? It is trivial to write a recursive CTE that does > not terminate, and the property of "circularity" is

[sqlite] Mozilla wiki 'avoid SQLite'

On Tue, 16 Jun 2015 09:56:38 +1000 wrote: > The question is: what should a database language do? Andl can already > match or surpass SQL on database programming tasks, but is that > interesting enough? > > What would make a database programming better, or best? Two things I've often pointed to

[sqlite] Mozilla wiki 'avoid SQLite'

On Tue, 16 Jun 2015 10:36:45 +0200 Marco Bonardo wrote: > The contents of the page came out from actual bugs and misuses we hit > in years of use and experience with it and were discussed with > attention. Most also have workarounds or suggested fixes. While I'm sure that's true, the page is

[sqlite] Is recursive CTE fully capable?

On Fri, 12 Jun 2015 01:45:50 +0100 Simon Slavin wrote: > There are plenty of queries which can be expressed in a SQL database > but can't be answered without a computer which can reprogram itself Are there? Do you mean there are SQL queries like that? Or do you mean there are such queries

[sqlite] DROP statement on Virtual Tables

On Tue, 9 Jun 2015 15:13:47 + Hick Gunter wrote: > xConnect is called whenever SQLite decides it needs to do something > with the existing virtual table. There must have been a successful > xCreate call (in another session, another thread or another process) > before. xConnect is not allowed

[sqlite] User-defined types

On Thu, 4 Jun 2015 15:11:55 -0700 Darko Volaric wrote: > Are you seriously saying that that SQL syntax is friendly? How can you > defend SQL syntax other than on grounds of history or > standardization? The first and best defense of SQL is that it has at least some basis in the relational

[sqlite] User-defined types

On Fri, 5 Jun 2015 13:07:59 -0400 Stephen Chrzanowski wrote: > If N-Tier software development is 'annoying' and you are not happy, > either get help by other members of your team, or, find a different > hobby, because anything less than 3-tier programming dealing with > multiple languages,

[sqlite] AUTOINC vs. UUIDs

On Thu, 21 May 2015 13:44:15 +0200 Mark Straver wrote: > > Oh and the other feature! of UUIDs is merging databases... if I > > have a database deployed to 15 customers, each with their own ORGID > > I can easily throw them in the same database without worrying about > > fixing up primary keys

[sqlite] AUTOINC vs. UUIDs

On Wed, 20 May 2015 19:05:29 +0100 Simon Slavin wrote: > Posting this not because I agree with it but because the subject has > come up here a couple of times. > > > > > "Today, I?ll talk about

[sqlite] Virtual Table query - why isn't SQLite using my indexes?

On Tue, 19 May 2015 20:44:17 + Eric Hill wrote: > But then what about a query like this: > > SELECT * FROM T1 > LEFT OUTER JOIN T2 ON ( T2.a = T1.a ) AND ( T2.b = > T1.b ) AND ( T2.c = T1.c ); > > xBestIndex will get called here for T1 with 3 constraints, c, b, and > a,

[sqlite] Suggestion for .import improvement

On Mon, 11 May 2015 22:24:53 +0300 wrote: > Current .import behavior is that when the imported table does not > exist, it will be created using the first row from the data file as > column names for the new table. Huh, somehow I'd forgotten that! > Once the initial table is created by the

[sqlite] Signal handling and mmap

On Tue, 5 May 2015 12:05:51 -0700 Charles Munger wrote: > At https://www.sqlite.org/mmap.html, the documentation mentions: > > "An I/O error on a memory-mapped file cannot be caught and dealt with > by SQLite. Instead, the I/O error causes a signal which, if not > caught by the application,

[sqlite] Multiple instances of the same program accessing the same db file

On Mon, 4 May 2015 02:05:54 +0100 Simon Slavin wrote: > On 4 May 2015, at 1:30am, James K. Lowden > wrote: > > > That is the way most remote filesystems are designed and implemented > > and documented. Cf. http://www.ietf.org/rfc/rfc1813.txt: > >

[sqlite] Multiple instances of the same program accessing the same db file

On Sun, 03 May 2015 13:09:43 -0700 Scott Doctor wrote: > I wonder why the network developers have not appropriately addressed > this issue. Looks like I need to research this problem more before > implementing. >From one perspective -- a common use case -- they *have* addressed this issue.

[sqlite] Segfault during FTS index creation from huge data

On Thu, 30 Apr 2015 12:47:57 -0600 Scott Robison wrote: > Perhaps you are correct and "sigsegv" is not the literal signal that > is triggered in this case. I don't care, really. The fact is that an > apparently valid pointer was returned from a memory allocation > function yet can result in an

[sqlite] Multiple instances of the same program accessing the same db file

On Sat, 02 May 2015 19:24:19 -0700 Scott Doctor wrote: > Each computer has its own copy of the program, but may > share the database file which may be located on any of the computers. > So I am wondering whether I should implement my own locking logic in > my program. Yes, you need to arrange

[sqlite] Does column order matter for performance?

On Thu, 30 Apr 2015 09:45:14 -0700 Pol-Online wrote: > I wasn?t able to find the answer to this question online: does the > column order matter for SQLite performance? E.g. should you put fixed > width columns like INTEGER before TEXT or BLOB? I'm going to check that box as No. SQLite

[sqlite] Segfault during FTS index creation from huge data

On Wed, 29 Apr 2015 20:29:07 -0600 Scott Robison wrote: > > That code can fail on a system configured to overcommit memory. By > > that standard, the pointer is invalid. > > > > Accidentally sent before I was finished. In any case, by "invalid > pointer" I did not mean to imply "it returns a

[sqlite] Segfault during FTS index creation from huge data

On Wed, 29 Apr 2015 01:27:03 +0300 Artem wrote: > >> Error: near line 1: out of memory > > > That's not a segfault, though, is it. > > When I did the same in linux version of SQLite - I saw > the "Segmentation Fault" error. The above message indicates that SQLite trapped an errno of ENOMEM

[sqlite] Segfault during FTS index creation from huge data

On Wed, 29 Apr 2015 02:39:50 -0600 Scott Robison wrote: > On linux, malloc may return a non null yet invalid pointer and only > fail when the memory is accessed because it wasn't really available. Citation needed. I believe SIGSEGV is possible with anonymous mmap and overcommitted memory.

[sqlite] SQLite and Scientific Computing: Arrays and Complex Numbers

On Tue, 28 Apr 2015 09:24:56 +0200 Dominique Devienne wrote: > On Tue, Apr 28, 2015 at 4:16 AM, James K. Lowden > wrote: > > > A major hurdle is the memory model: because array-programming > > libraries normally mandate the data be in contiguous memory, > > there's a

[sqlite] SQLite and Scientific Computing: Arrays and Complex Numbers

On Mon, 27 Apr 2015 16:35:11 -0400 Jim Callahan wrote: > So, the application of arrays and complex numbers go far beyond the > one question posed to this list with arrays of integers and reals > being far more common than arrays of complex numbers. > > Complex numbers are included as types in

[sqlite] Thoughts on storing arrays of complex numbers

On Fri, 24 Apr 2015 13:37:40 + "Drago, William @ CSG - NARDA-MITEQ" wrote: > I'm trying to avoid re-inventing the wheel. Is there a best or > generally accept way to store arrays of complex numbers? A table in First Normal Form has no repeating groups. That means no row has an array of

[sqlite] json_* functions in sqlite

On Tue, 21 Apr 2015 18:09:33 -0700 Ashish Sharma wrote: > Many times I store JSON data in sqlite. It will be useful if sqlite > came with functions which understand JSON. Presto has a nice set > https://prestodb.io/docs/current/functions/json.html In case you don't know, you could implement

[sqlite] Destroy all evidence of a database

On Wed, 22 Apr 2015 16:56:07 +0100 Simon Slavin wrote: > You have made me realise, however, that a nice attack against > encrypted SQLite databases might be to crash a SQLite application > while it's processing and examine any journal files, shared memory > file and temporary index files. It

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