[sqlite] Pragma automatic_index and ROWID: which one is really responsible for automatic indexes?

On 2016/01/28 11:44 PM, Yannick Duch?ne wrote: > On Thu, 28 Jan 2016 22:08:02 +0200 > R Smith wrote: > > Indeed, I was misunderstanding. Are these indexes visible? Is there > any relation with the ones I see from sqlitebrowser? If you mean you are looking at the DB sc

[sqlite] Pragma automatic_index and ROWID: which one is really responsible for automatic indexes?

On 2016/01/28 10:08 PM, R Smith wrote: > > > -- Insert Loop start: > -- This will simply fail if the PK already exists, else start the line > with 0 count. > INSERT OR IGNORE INTO element_attribute_values VALUES (:element, > :attribute, :value, 0); > > -- This mi

[sqlite] Pragma automatic_index and ROWID: which one is really responsible for automatic indexes?

On 2016/01/28 8:17 PM, Yannick Duch?ne wrote: > Hi people, > > I though automatic index was controlled by the pragma `automatic_index`, > which seems to do nothing for me, unless I'm doing it the wrong way, while > `WITHOUT ROWID` on table creations, seems to effectively prevent automatic >

[sqlite] Updating a filed in table a with contents from table b

On 2016/01/24 10:15 PM, audio muze wrote: > I have a large table with ~350k records for which I'm in the process of > standardising data. 350k records is not exactly a "large" table, A table scan would complete in a couple of seconds on a normal system. 350 million rows are more substantial

[sqlite] Running Sqlite under Linux Terminal Server

On 2016/01/25 7:02 PM, Bernard McNeill wrote: > Hello, > > Anyone with any experiences with this (good or bad)? > > Idea to have Sqlite database on server, accessed from a few ultra-thin > Terminal clients. Terminal clients? You mean the kind where the terminal client is basically just

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

On 2016/01/25 5:08 PM, Matthias-Christian Ott wrote: > On 25/01/16 14:14, Richard Hipp wrote: >> On 1/19/16, Meinlschmidt Stefan wrote: >>> Shutting down power right after a successfully committed >>> transaction rolls back that transaction on next startup. >> Patches checked in: >> >>

[sqlite] Slight problem with sqlite3_compileoption_get

On 2016/01/23 9:41 PM, Bart Smissaert wrote: > Using a .tlb file to access the sqlite3 functions from VB6. > Sofar when the arguments of the sqlite3 function are declared as int I have > been using > long in the IDL source to compile the tlb and this has been fine. I'm not very familiar with

[sqlite] UPSERT

I think you could also just defer foreign key checking to the end of the transaction. On 2016/01/20 12:20 PM, chromedout64 at yahoo.com wrote: > The problem with REPLACE is: " the REPLACE algorithm deletes pre-existing > rows that are causing the constraint violation prior to inserting or

[sqlite] Getting a specific row

On 2016/01/19 8:14 AM, Ali Moradi wrote: > Hi, i am working on a python 2.x and Tkinter program. My table have 2 > fields. How can i get a specific row? Should i write : > SELECT * FROM (table-name) WHERE IDENTIFICATION == 1 > > How can i get the row id? Thanks. Adding to other replies - you

[sqlite] syntax error on SELECT with WHERE EXISTS

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 is near SELECT, but doesn't say which SELECT. > If I say either It works fine for me - What

[sqlite] Parsing the contents of a field

On 2016/01/16 4:53 PM, Simon Slavin wrote: > On 16 Jan 2016, at 2:31pm, R Smith wrote: > >> There is of course no SQL function to do this, but thanks to CTE we can >> achieve it easily (though not extremely efficiently). > I thought that WITH could be used only for SELE

[sqlite] Parsing the contents of a field

On 2016/01/16 7:02 AM, audio muze wrote: >> What do you mean by "parse" ? Just to separate a string into its delimited >> substrings ? Since SQLite has no array or list variable-type there's no way >> to do that because there's no way to return the result. Can you not just >> return the

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

On 2016/01/16 10:18 AM, audio muze wrote: > Thanks Keith > > That's what I'd tried with my first attempt but naturally it didn't work. > > Why is it that SQLite does not support a FROM clause in an update statement? Because of the intent expressed with the word "Lite" in SQLite and the fact

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

On 2016/01/14 5:54 PM, Olivier Mascia wrote: > Hello, > > 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? > Those two statements do very different things,

[sqlite] Database Corrupt While Disk Full

On 2016/01/14 6:46 PM, R Smith wrote: > ...// Out-of-memory and out-of-diskspace type errors are reported// Also note here that there is a case where SQLite will report DISK_FULL errors where the disks are seemingly fine - when you start a transaction that requires large amounts of sp

[sqlite] Database Corrupt While Disk Full

On 2016/01/14 3:02 PM, sanhua.zh wrote: > 1. Not all other codes except SQLITE_OK, SQLITE_ROW, SQLITE_DONE should be > treated as fatal errors. >As an example, SQLITE_BUSY indicates that this op is temporarily failed, > but it can be done later. (Note that sometimes you should not retry

[sqlite] hard links and SQLite

On 2016/01/11 9:37 PM, Warren Young wrote: > The OP was vague about that, but I think the point of his current > gymnastics is to prevent the other process from creating a rogue > schema, or to insert compromising data into a correct schema. To make > it concrete, you could probably write a

[sqlite] hard links and SQLite

So, if I understand correct, you create a file and then start to add a schema etc. Before the schema is committed (or before the exclusive transaction is started) you assume that other threads/processes /might/ be opening the file and trying to write some data to it (because reading would be

[sqlite] Bug in sqlite3_prepare16_v2

On 2016/01/09 6:07 PM, Bart Smissaert wrote: > Ah, OK, overlooked that. > It means that sqlite3_prepare can't be used to check the validity of pragma > statements, also because the pragma could actually run! > Thanks for pointing me to this. > Will need to add some parsing code. Aye, I do this

[sqlite] Bug in sqlite3_prepare16_v2

On 2016/01/09 5:22 PM, Bart Smissaert wrote: > If I run sqlite3_prepare16_v2 on this statement: > > PRAGMA compile_optionsXXX > > I get a return value of 0 and there is a non-zero statement handle. > I would expect a return value of 1 and a zero statement handle. > > But if I run instead

[sqlite] Can SQLite know from the statement string if it is row producing or not?

On 2016/01/09 1:30 PM, Clemens Ladisch wrote: > Bart Smissaert wrote: >> I am interested to know from the statement string if the statement is >> invalid, row producing (could produce rows) or non row producing. > says about >

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

On 2016/01/08 9:51 AM, Darren Duncan wrote: > Stephen, > > What you are arguing for (no shared libraries) is bad old days where > one had to recompile their programming language to add support for a > DBMS, rather than the DBMS support being a separately installable > library that one could

[sqlite] whish list for 2016

On 2016/01/04 6:11 PM, Bernardo Sulzbach wrote: > On Mon, Jan 4, 2016 at 1:50 PM, Stephen Chrzanowski > wrote: >> I did the test to compare SSD vs 5200rpm and 7200rpm drives. I should >> throw this at my 7200rpm hybrid at this app and see what happens. >> > Could you please share any

[sqlite] About vacuum

On 2016/01/04 11:26 AM, Simon Slavin wrote: > On 4 Jan 2016, at 9:24am, Rowan Worth wrote: > >> Of course this also mean that VACUUM takes even longer over NFS, so it's >> absolutely not worth running before every query. But we've seen benefit >> from using VACUUM as a maintenance step while

[sqlite] whish list for 2016

PRAGMA strict_mode = 1; :)

[sqlite] Problem with accumulating decimal values

On 2015/12/17 3:26 AM, James K. Lowden wrote: >> Calculated errors are fine because we can at any time revisit the >> calculation procedures, we can refine and perhaps opt for more >> significant digits - but we can ALWAYS guarantee the accuracy-level >> of the calculated result. However,

[sqlite] Problem with accumulating decimal values

On 2015/12/16 4:05 PM, 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 may get lucky and >>

[sqlite] Running Sqlite on 64-bit/Client-server data base

Hi Hamdan, These are some very basic questions (as others have mentioned), and may not be your only questions at this point. To fully understand how SQLite implements databasing and what it is best suited for (or what it isn't useful for), your best bet is to take a look at these pages:

[sqlite] Checking Foreign Keys

On 2015/12/13 2:17 PM, Cecil Westerhof wrote: > I am continuing with my exploration of SQLite. :-) > > At the moment I am working with Foreign Keys. They need to be enabled. When > you do not do this it is possible to enter records that break the Foreign > Key rules. Is there a way to check for

[sqlite] Set update time, but not always

On 2015/12/13 1:31 PM, Cecil Westerhof wrote: > I have a table where I would most of the time update a field lastChecked to > current_date when I update the record. But it is possible that I sometimes > want to update a record without updating lastChecked. Is this possible, or > should I update

[sqlite] Selecting total and not used in one query

or, more elegantly... WITH PC(t, u, f) AS ( SELECT 1, (P.used IS NOT NULL), (P.used IS NULL) FROM proverbs AS P ) SELECT SUM(PC.t) AS Total, SUM(PC.u) AS Used, SUM(PC.f) AS Free FROM PC; On 2015/12/12 4:18 PM, Cecil Westerhof wrote: > ?I have the following query: > SELECT >

[sqlite] Selecting total and not used in one query

On 2015/12/12 4:18 PM, Cecil Westerhof wrote: > ?I have the following query: > SELECT > (SELECT COUNT(*) FROM proverbs)AS Total > , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used > > But I want something like: > ?SELECT >

[sqlite] INSERT OR IGNORE consuming too many rowids

On 2015/12/09 10:35 PM, Wade, William wrote: > I may be reading different documentation (or perhaps just misreading it). > > https://www.sqlite.org/autoinc.html says (for the no AUTOINCREMENT case, > which applies to the original post) "If no ROWID is specified on the insert > ... [the] usual

[sqlite] Obscure peculiarity with 3.9.2

On 2015/12/09 10:55 PM, Richard Hipp wrote: > On 12/9/15, R Smith wrote: >> One of our systems suddenly started to play up after upgrading to 3.9.2 >> from 3.8.8 (so I am not sure exactly when the oddity was introduced). >> >> SQLite: >> v 3.9.2 linked on WIndo

[sqlite] Obscure peculiarity with 3.9.2

One of our systems suddenly started to play up after upgrading to 3.9.2 from 3.8.8 (so I am not sure exactly when the oddity was introduced). SQLite: v 3.9.2 linked on WIndows (various versions) in a 32 bit application via the exact 32bit DLL published on the downloads page on sqlite.org. The

[sqlite] INSERT OR IGNORE consuming too many rowids

On 2015/12/09 2:21 PM, David Baird wrote: > Looks like it's fixed, as long as I stick to new versions. *cross > fingers* Thanks! Just to add to my previous reply: It wasn't "fixed", since it was never before "wrong", it was merely changed in a way that happens to currently suit your needs,

[sqlite] INSERT OR IGNORE consuming too many rowids

On 2015/12/09 2:21 PM, David Baird wrote: > On Wed, Dec 9, 2015 at 7:14 AM, Clemens Ladisch wrote: > > Just tried two different versions: > > SQLite version 3.8.2 2013-12-06 14:53:30 (that is bundled with my OS): has > the undesired behavior > > SQLite version 3.9.2 2015-11-02 18:31:45 (that I

[sqlite] NOP INSERT still writes to the DB/journal

On 2015/12/08 12:16 PM, Domingo Alvarez Duarte wrote: > If I understood correctly when no transaction is specified an implicit > transaction is created so there is no point to create a transaction for only > one statement. Yes, but what Simon is trying to achieve is to have the transaction

[sqlite] TEXT columns with Excel/VBA

On 2015/12/03 8:49 PM, Scott Hess wrote: > On Thu, Dec 3, 2015 at 4:49 AM, R Smith wrote: > >> On 2015/12/03 3:04 AM, Scott Hess wrote: >> >> I posit that a column declared as: col VARCHAR(32) NOT NULL says a >> whole lot more about what is actually happening

[sqlite] TEXT columns with Excel/VBA

On 2015/12/03 3:04 AM, Scott Hess wrote: > I discourage this kind of usage because it means that in some distant > future when someone has to make things work with a different database > engine, they have to grind through and check every weirdo VARCHAR(73) and > MEDIUMBIGINT declaration someone

[sqlite] TEXT columns with Excel/VBA

On 2015/12/02 6:34 PM, Erwin Kalvelagen wrote: > Good morning. > > I wrote a little tool to dump certain data sets into a SQLite database. A > user suggested that I should not use type TEXT but rather type VARCHAR for > character columns, due to some issue with Excel/VBA. See the comments in: >

[sqlite] Warnings for non-deterministic queries?

On 2015/11/27 9:58 PM, Simon Slavin wrote: > On 27 Nov 2015, at 6:30pm, R Smith wrote: > >> Let me explain better, let's assume the query contains MAX(x)... The result >> will be from whichever row contains the max, and if the x was not in the >> aggregate function,

[sqlite] Warnings for non-deterministic queries?

On 2015/11/27 4:44 PM, Adam Devita wrote: > I think the OP meant to write: > "If the expression is an aggregate expression, it is evaluated across > all rows in the group. Otherwise, it is evaluated against a single > arbitrarily chosen row from within the group. " > > Is there a way I could

[sqlite] drop, create and copy a table

On 2015/11/26 4:08 AM, H?ctor Fiandor wrote: > Dear Mr. Hipp and other members: > > I have a table where I have introduced a lot of information during 2015. > > In December 31, I have to select the records to be continue in 2016. > Previously, I have obtained a copy of the 2015 table for any

[sqlite] Bug report for MAX()

Many thanks to all. I should have checked - That table was not supposed to be able to even get strings in there - this exposed a bug in an application of ours too. Adding check constraints right away. Thanks! On 2015/11/25 1:56 PM, Richard Hipp wrote: > On 11/25/15, Dave McKee wrote: >> I

[sqlite] Bug report for MAX()

It seems there are some instances where MAX() does not return a value. I will send such an offending DB direct, but the sqlite3.exe results as follows: F:\[BACKUP]>sqlite3.exe IPDB_ImptData.idb SQLite version 3.9.2 2015-11-02 18:31:45 Enter ".help" for usage hints. sqlite> SELECT max(UnitCost)

[sqlite] regular expression in check constraint?

On 2015/11/25 1:44 AM, Igor Tandetnik wrote: > On 11/24/2015 6:07 PM, Richard Hipp wrote: >> On 11/24/15, James Hartley wrote: >>> 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

[sqlite] INSERT DEFAULT literal-value

On 2015/11/24 9:20 PM, chromedout64 at yahoo.com wrote: > Thanks for the reply. It would be the case of specifying a default value > among other columns but not wishing to remove it from the inserted fields -- > not just because I'm lazy, but also to reuse that same INSERT statement for >

[sqlite] INSERT DEFAULT literal-value

On 2015/11/23 11:00 PM, chromedout64 at yahoo.com wrote: > Maybe there's a technical reason that this functionality wasn't added to > SQLite. Does anyone know? I am not sure exactly what you intend with this. Do you mean to Insert into a table a row with the default values? In that case you

[sqlite] 10 minute Avg

> Thanks, > Andrew S. > > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of R Smith > Sent: Thursday, November 19, 2015 7:40 AM > To: sqlite-users at mailinglists.sqlite.org &

[sqlite] 10 minute Avg

On 2015/11/19 4:49 PM, Andrew Stewart wrote: > Hi, > Had a question regarding what I am trying to do. One thing that I have > noticed is that it is slow to do this. I do not have any indexes created and > there is no primary index on this table. > I am using a 'DateTime' variable

[sqlite] 10 minute Avg

f that does what is needed, Cheers! Ryan > > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of R Smith > Sent: Wednesday, November 18, 2015 10:06 AM > To: sqlite-users at mailinglists.s

[sqlite] 10 minute Avg

ime >= TIV.startTime AND DSR.datetime < TIV.endTime AND fwParameterID = 1074 GROUP BY TIV.startTime ORDER BY TIV.startTime ASC ; On 2015/11/18 7:43 PM, R Smith wrote: > Quite easy to do with a CTE, like this: > > WITH BDT(startDateTime,endDateTime,IntervalSeconds) AS ( > SEL

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

On 2015/11/18 7:23 PM, Duquette, William H (393K) wrote: > On 11/17/15, 3:35 PM, "sqlite-users-bounces at mailinglists.sqlite.org on > behalf of Rolf Ade" behalf of rolf at pointsman.de> wrote: > > >> Richard Hipp writes: >>> On 11/17/15, Yuri wrote: This message always leaves the user

[sqlite] 10 minute Avg

Quite easy to do with a CTE, like this: WITH BDT(startDateTime,endDateTime,IntervalSeconds) AS ( SELECT '2015-11-17 00:00:00', '2015-11-18 00:00:00', '+600 seconds' ), TIV(startTime,endTime) AS ( SELECT startDateTime, datetime(startDateTime,IntervalSeconds) FROM BDT UNION ALL

[sqlite] Retrieving the table info fails

On 2015/11/16 7:59 PM, Igor Korot wrote: > Stephan, > > On Mon, Nov 16, 2015 at 12:42 PM, Stephan Beal > wrote: >> On Mon, Nov 16, 2015 at 6:11 PM, Igor Korot wrote: >> >>> The variables referenced are defined as "std::string" and the code is in >>> C++. >>> >> the std::string(char const *)

[sqlite] [AGAIN] SQLite on network share

On 2015/11/13 10:52 PM, A. Mannini wrote: >> Basically the decision is easy - If you require either of: >> - Network data >> - User control >> >> Then you should use a suited Network DB and not a file-based DB. Best >> free (without limitations) choices are (In no particular order): >> -

[sqlite] [AGAIN] SQLite on network share

On 2015/11/13 9:55 PM, A. Mannini wrote: > Ok, thanks for all your replies!!! > > First, i was asking to understand...before to start development in a > wrong direction. > > I don't have experience with SQLite and even less on a network share. I > would understand if corruption is a remote

[sqlite] Array or set type or some other indexable data?

On 2015/11/12 8:02 AM, J Decker wrote: > So I've used CTE to solve a simple problem... I'm tempted to use it to > fix more problems... but I'm wondering how to select different values > at different levels. I know there's like 'select * from table where > column in ( set,of,things) ' but can I

[sqlite] Random performance issues with SQLite

On 2015/11/09 6:33 PM, Brice Andr? wrote: > OK, but if this scenario was at the origin of my problem, I guess I would > have such a log each time a db query freezes ? > > If yes, then there is probably another problem, as I am sure I got such > freezes with no such logs from sqlite. > > By the

[sqlite] A little light reading

On 2015/11/08 6:11 PM, John McKown wrote: > I'm not a developer. So I guess that it's my ignorance as to why a program > would be confused by the string value of "null" or any variant thereof. I > do understand looking for a string of length 0. If I were to want a special > name for Some purpose,

[sqlite] Non-transitive numeric equality

On 2015/11/05 4:55 PM, Richard Hipp wrote: > On 11/5/15, Zsb?n Ambrus wrote: >> Dear SQLite, >> >> It seems that equality of numeric values isn't transitive, when both >> integers and reals are involved. Here's an example output from the >> shell, which shows that the numeric value in the 'c'

[sqlite] Fwd: Re: comma-separated string data

This message went to the wrong address, apologies, herewith the repost: On 2014/04/06 20:23, Dominique Devienne wrote: On Sat, Apr 5, 2014 at 11:46 AM, RSmith wrote: WITH csvrec(i,l,c,r) AS ( SELECT tmpcsv.ID, 1, colCSV||',', '' FROM tmpcsv UNION ALL

<    3   4   5   6   7   8