Re: [sqlite] UPDATE statement without FROM clause

On 2016/06/04 11:34 PM, Simon Slavin wrote: On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps wrote: Can't the same update be done more efficiently with a CTE? The command inside the WITH has to be a SELECT command. I wonder if there's a good reason for that. If the

Re: [sqlite] View workarounds

On 2016/05/24 2:17 AM, Balaji Ramanathan wrote: Thank you for continuing with this thread, Ryan. I don't have nuclear launch codes in my database, but it is over 4MB in size. But the data in it is not that important. Let me post the view I am interested in: select Trip.TripID as 'Trip

Re: [sqlite] View workarounds

On 2016/05/25 5:42 PM, Balaji Ramanathan wrote: Thanks again, Ryan. The options right now come down to either expanding the view with all the raw columns so that I can filter and sort directly using a select * from view. Or I can use the view for unfiltered, unsorted look at my data, and use

Re: [sqlite] SQL / SQLite for Beginners

On 2016/05/25 6:29 PM, Cousin Stanley wrote: R.A. Nagy wrote: All, A recent convert to using SQLite, I have put together a series of videos on learning SQL, as well as covering the basics of using SQLite. https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy Would

Re: [sqlite] sql

On 2016/05/26 8:29 AM, Jim Wang wrote: hi all a table as follow: id score 210 3 20 5 10 3 20 2 30 2 30 how could I select the table as follow and the count can tell

Re: [sqlite] Finding a single row

On 2016/05/26 1:17 PM, Ertan Küçükoğlu wrote: Hi Simon, Two or more rows return is an error on user definition. I will popup a message in this case. Thanks for the example, I didn't know <= can be used also for strings. I think Simon's example said LIMIT 1 at the end, did you use that? The

Re: [sqlite] Messages posted on Nabble not getting to list

On 2016/05/27 1:48 AM, Balaji Ramanathan wrote: Thank you, Simon and Igor. I have put in a support request at Nabble, let us see what happens. Why does SQLite use a mailing list instead of a proper web-hosted forum/bulletin board type setup? That way it would be possible to set up

Re: [sqlite] DB Partitioning and Performance

On 2016/05/27 4:17 AM, Michael Hari wrote: I have a database that contains 2014,2015 and 2016’s worth of data broken down by year and quarter. In total, this db was 2.36 GB. Because of a replication requirement where the DB has to be under 2 GB, I’ve split the DB into 3 smaller databases by

Re: [sqlite] Messages posted on Nabble not getting to list

On 2016/05/27 6:00 AM, Balaji Ramanathan wrote: Interesting. I have no idea what a facebook-style interface looks like since I don't have a facebook account. The main advantage of forums, and I follow a bunch of them, is that I choose when I want to stop my regular day job and be distracted

[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] 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/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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] whish list for 2016

PRAGMA strict_mode = 1; :)

[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

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

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

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

> 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] 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] 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] 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] 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] 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] 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] 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] 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] How to order by absolute value ?

On 2016/04/05 10:47 AM, Domingo Alvarez Duarte wrote: > Hello ! > > I want to achieve this: > > select 1 as num > union > select 3 as num > union > select -2 as num > > order by abs(num) -- result [1, -2, 3] > > But it doesn't work this works but doesn't show what I want: > > select abs(num) >

[sqlite] How to order by absolute value ?

On 2016/04/05 11:48 AM, Niall O'Reilly wrote: > On 5 Apr 2016, at 10:06, Graham Holden wrote: > >> Change "select abs(num)" in your second example to "select num" and >> you should be good. I'm guessing in your first example the ORDER BY >> only applies to the last SELECT. > > I expect

[sqlite] Using index in sqlite

On 2016/04/05 12:01 PM, jose.campos.romero at andaluciajunta.es wrote: > Hello > > If I create an index for a table, when an application accesses the table, > Sqlite automatically will use the index when necessary? > > Thanks in advance Yes, whenever possible.

[sqlite] FOREIGN KEY constraint failed

On 2016/04/04 10:43 PM, Domingo Alvarez Duarte wrote: > Thanks for reply ! > > I already sent a proposal to Richard to add a pragma "PRAGMA DEBUG_MODE" and > when set throw any kind of error to stderr/sqlite3_(hook) this way ther is no > need to store temporary conditions to show later. > > And

[sqlite] FOREIGN KEY constraint failed

On 2016/04/05 11:15 PM, Keith Medcalf wrote: > Are we confusing immediate constraints (checked per statement) with DEFERRED > constraints (checked at COMMIT time) again? > We might be - though I assume the OP implicated only deferred constraints - since immediate constraints will fail on

[sqlite] Why SQLite use busy-retry but not lock-and-wait?

On 2016/04/06 6:03 AM, sanhua.zh wrote: > Recently, I am reading the source code of SQLite. > I found that on OS level, SQLite use file lock to solve multi-processes > problem and use VFS to solve multi-threads problem. But all of them might > failed with racing and SQLite will return a

[sqlite] Expecting syntax error on delete

On 2016/04/09 5:20 PM, Richard Williams wrote: > I have a PHP program where I have the equivalent of the following code. The > code was not deleting the expected rows ('abc' & 'def') because of the bad > syntax. However the error did not throw an exception. Is this what I should > expect? > > $p

[sqlite] Last time analyze was ran

On 2016/04/12 3:56 AM, Jose I. Cabrera wrote: > > > Maybe this should be something to think about, and perhaps add it as part of > the results or reported items of .schema. Also, only update the date if > completion successful. The reason why it's important is that I have a script > that

[sqlite] FTS5 query that to match all rows.

On 2016/04/13 6:44 PM, John Found wrote: > On Wed, 13 Apr 2016 23:38:04 +0700 > Dan Kennedy wrote: > >> On 04/13/2016 11:24 PM, John Found wrote: >>> What FTS5 query should I use in order to match all rows in the table? >>> >> Can you use "SELECT * FROM fts_table;"? >> > *I* can. But the users

[sqlite] autoincrement

On 2016/04/13 4:58 PM, Igor Korot wrote: > Hi,, > > On Wed, Apr 13, 2016 at 10:54 AM, J Decker wrote: >> Yes, you can get the create statement from sqlite_master table > I was kind of hoping for a simpler solution so that not to parse "CREATE > TABLE" > statement... > > Well, I guess I will

[sqlite] Strange behaviour of select.

On 2016/04/14 10:46 AM, John Found wrote: > Playing with optimization of my queries, I stuck on very strange (for me) > behaviour of > sqlite. > > The query is pretty complex, but for the experiment I simplified it to: > > select a, b from t order by a desc, b desc; > > The result of "explain

[sqlite] Primary key values can be NULL

On 2016/04/14 10:23 PM, Cecil Westerhof wrote: > 2016-04-14 22:10 GMT+02:00 Richard Hipp : > >> On 4/14/16, Cecil Westerhof wrote: >>> ?Yes that makes sense. But could not a type of PRAGMA be used? So if the >>> PRAGMA is not defined the old functionality and your historical data is >>> save.

[sqlite] Using SQLite for storing photo's

On 2016/04/14 10:18 PM, Cecil Westerhof wrote: > 2016-04-14 22:07 GMT+02:00 Clemens Ladisch : > >> Cecil Westerhof wrote: >>> what is the best way to make a backup? >> With the backup API: . >> (Also available as .backup in the shell.) >> > ?I should be more

[sqlite] Primary key values can be NULL

On 2016/04/15 2:09 PM, Cecil Westerhof wrote: > >> What makes you "expect" this? Nothing in the SQLite documentation I hope. >>> Is it perhaps habit based on how some other DBs do it? >>> >>> How will "those who don't know about NOT NULL" be better serviced by a >>> pragma which they also don't

[sqlite] Primary key values can be NULL

On 2016/04/15 8:00 PM, Cecil Westerhof wrote: > 2016-04-15 16:43 GMT+02:00 R Smith : > > > ?Do you want me to tell you that you always read all the documentation of > all the software before you use it? I surely do not. And I think most > people do not. To be honest I

[sqlite] Caveat entry

On 2016/04/15 11:53 PM, Simon Slavin wrote: I'm with Simon in response to Cecil on the idea of adding a shortlist of known "bite" possibilities, something like the whentouse.html or the howtocorrupt.html. Perhaps named commonmistakes.html or the like. Maybe we could ask for contributions

[sqlite] Avoid duplicate sets with join table

I would simply use a fourth table that is essentially itself just an Index, say: CREATE TABLE records_features_u ( id_r INT NOT NULL, combo_features TEXT NOT NULL, PRIMARY KEY (id_r, combo_features) ) WITHOUT ROWID; IF SUCCEED( INSERT INTO combo_features_u 10, "20,21,22"; ) THEN {

[sqlite] Avoid duplicate sets with join table

Actually, this would achieve the same, without the extra table: IF NO_ROWS_ARE_RETURNED_FOR ( SELECT 1 FROM ( SELECT id_r, GROUP_CONCAT(id_f) AS combo FROM records_features WHERE id_r = 10 GROUP BY id_r ) WHERE combo = '20,21,22' ) THEN { INSERT INTO

[sqlite] Avoid duplicate sets with join table

On 2016/04/16 3:03 AM, R Smith wrote: > Actually, this would achieve the same, without the extra table: No it won't work this way at all, I misjudged the outcome. That's what I get for not testing it - Apologies!

[sqlite] Is it possible that dropping a big table takes very long

On 2016/04/16 11:59 AM, Cecil Westerhof wrote: > I am playing a bit with SQLite. I first had a table with 1E8 elements. When > trying to drop this it looked like SQLite got hung. I tried it from DB > Browser and a Java program. > I just tried it with a table of 1E7 elements. That was dropped in

[sqlite] Is it possible that dropping a big table takes very long

On 2016/04/16 4:09 PM, Cecil Westerhof wrote: > One strange thing the commandline and DB Browser are using ?3.8.10.2 > while Java is using 3.8.11. Your command-line is simply outdated - you can download the newest from http://sqlite.org/download/ DB-Browser might have a newer version also,

[sqlite] Is it possible that dropping a big table takes very long

On 2016/04/16 3:39 PM, Cecil Westerhof wrote: > 2016-04-16 14:52 GMT+02:00 R Smith : > >> Let me try the 100 million rows, this may take some time - I will post >> again when it is done. >> > ?I am curious. Well, here it is then, 100-million rows: The INSERT took a

[sqlite] Is it possible that dropping a big table takes very long

On 2016/04/16 9:25 PM, Cecil Westerhof wrote: > 2016-04-16 20:36 GMT+02:00 R Smith : > > I am filling the database again, but now with text UUID instead of blob > UUID. That takes a ?little? more time. When it is filled I try again. Don't forget to copy the DB file once it

[sqlite] Is it possible that dropping a big table takes very long

On 2016/04/19 2:49 PM, Cecil Westerhof wrote: > 2016-04-18 12:47 GMT+02:00 Olivier Mascia : > >> PRAGMA compile_options; (has SQLITE_SECURE_DELETE been set for compiling >> the library?) >> > ?sqlite3 checkUUID.sqlite > SQLite version 3.8.7.1 2014-10-29 13:59:56 > Enter ".help" for usage hints.

[sqlite] Multiple in-memory database table query

On 2016/04/19 8:34 PM, Jarred Ford wrote: > Is it possible to create multiple in-memory databases and be able to access > tables with a single query between them? For example, select * from > db1.dbo.table1 db1 join db2.dbo.table1 db2 on db1.x = db2.x. No. An in-memory database has no

[sqlite] Is it possible that dropping a big table takes very long

On 2016/04/20 10:50 AM, Cecil Westerhof wrote: > 2016-04-20 10:44 GMT+02:00 Dominique Devienne : > >> On Wed, Apr 20, 2016 at 10:36 AM, Cecil Westerhof >> wrote: >> >>> I am baffled. Still DELETE before DROP is a lot more efficient. And it >>> looks that it is not bothered when other programs

[sqlite] Multiple in-memory database table query

On 2016/04/19 10:48 PM, R Smith wrote: > > > On 2016/04/19 8:34 PM, Jarred Ford wrote: >> Is it possible to create multiple in-memory databases and be able to >> access tables with a single query between them? For example, select >> * from db1.dbo.table1 db1 join

[sqlite] Is it possible that dropping a big table takes very long

On 2016/04/20 3:31 PM, Cecil Westerhof wrote: > 2016-04-20 12:35 GMT+02:00 R Smith : > >> >> On 2016/04/20 10:50 AM, Cecil Westerhof wrote: >> >> The Devs do read the list, and often post, and they will be very >> interested in what you have discovered if i

[sqlite] Field FOREIGN KEY when it is a pointer

On 2016/04/20 6:04 PM, Cecil Westerhof wrote: > I am thinking about creating a table where a certain field will be a > description, or a key to the description. Would it be possible to have a > constraint on this field that it is an id, that this id points to an > existing record in a

[sqlite] Is it possible that dropping a big table takes very long

On 2016/04/20 5:56 PM, Cecil Westerhof wrote: > 2016-04-20 16:07 GMT+02:00 R Smith : > >> >> On 2016/04/20 3:31 PM, Cecil Westerhof wrote: >> >> ?It is still 411 MB. When I am home I will try it on another system also to >> look if there the timing is diff

[sqlite] Is there something like PHPAdmin for SQLite

There is ample PHP support, and even Python and the like (see the APSW suite by Roger Binns for instance). PHP of course supports also MSSQL and PostGres and the like, there is no need to be "stuck with MySQL". As for phpMyAdmin - that's a different story - the strength of phpMyAdmin is of

[sqlite] Field FOREIGN KEY when it is a pointer

On 2016/04/20 6:21 PM, Cecil Westerhof wrote: > 2016-04-20 18:07 GMT+02:00 R Smith : > >> >> On 2016/04/20 6:04 PM, Cecil Westerhof wrote: >> >>> I am thinking about creating a table where a certain field will be a >>> description, or a key to the

[sqlite] In-Memory database PRAGMA read_uncommitted

On 2016/04/23 10:20 AM, Michele Pradella wrote: > I have an In-Memory DB that is written and read from connections of the > same process. All good with shared cache, but I found that TableLock > occur more often on In-Memory than on disk DB, probably because in > memory we can't use WAL. > >

[sqlite] Working with blob

On 2016/04/28 8:27 PM, deltagamma1 at gmx.net wrote: > What is a convenient way to store the path from a external blob (jpg, > pdf) ? > How can I retrieve this blob ? > Is there a Frontend which opens the jpg directly from the listed > table-content ? > > If I store the blob directly in the

[sqlite] Documentation comment: VFS

On 2016/02/01 1:55 PM, Simon Slavin wrote: > Nowhere on the page > > > > 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". And some people

[sqlite] json_group_array

On 2016/02/05 6:34 AM, TJ O'Donnell wrote: > I can't argue for the correctness of including nulls in aggregate functions > or not. > It truly is an arbitrary decision meant for standards-makers. Yet, most > aggregate > function do not include nulls. Interestingly, some SQL's do include them >

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

On 2016/02/04 3:30 PM, Keith Medcalf wrote: > You conclusion about the Windows design goals are correct. > > Hardware Destroyer (power saving) was invented for the same reason (to > maximize the rate of hardware failure through imposition of unnecessary > thermal and mechanical stresses on all

[sqlite] union, order by, and a phantom record

Definitely a bug: I distilled the OP's code into an easy repeatable test case - --- create table t(id integer primary key autoincrement, a, b, c); insert into t values (3,1 ,'name','Imogen') ,(5,1 ,'gender' ,'female') ,(6,1 ,'son'

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

On 2016/02/09 1:30 PM, Chris Prakoso wrote: > Hi Clemens, > > Thanks for your reply. I've tried to use raw SQL but it didn't work > either. Do you have any SQLite front-end that you use? If I may suggest, try SQLitespeed (http://sqlc.rifin.co.za/) and add your DB file, open it and then use

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

That's not an SQLitespeed feature but indeed a backwards-compatible SQLite feature. (I had this wrong too at some point) You probably already know, but to be clear: In SQL standard, double-quotes indicate identifiers and single quotes indicate string values. While the single quotes are used

[sqlite] Turkish character problem

On 2016/02/09 5:57 PM, Salih Y?cel wrote: > Hi, > I have windowns phone project but one problem . Sqli te select result data > type text column turkish characters encoding problem > But Android operation system no problem turkish characters > > > Salih Y?cel > Mobil Grup Lideri /Mobile Group

[sqlite] dizzying error on my part

So let me get this straight... You opened an sqlite database file in a word processor, then did find and replace, and saved it? I hope you have a backup of that database file If you do have a backup, we can teach you how to search and replace using an actual DB tool and SQL, else yes, you

[sqlite] Windows A and W APIs dual support

On 2016/02/13 6:52 AM, J Decker wrote: > On Fri, Feb 12, 2016 at 8:00 PM, Igor Tandetnik wrote: >> On 2/12/2016 10:44 PM, J Decker wrote: >>> >>> I expect it to take any string >> >> What is the basis of this expectation, other than wishful thinking? > I don't think expectation and wishful

[sqlite] query Benchmark

On 2016/02/12 6:38 PM, Michele Pradella wrote: > Already solved with UNION of SELECT > It isn't solved, it is circumvented by trial and error without understanding why. I'm sure that works ok for you in this case, but the point is if you do study those documents a bit more, you may grasp the

[sqlite] Temporary table in SQLite

Yes, you can do: CREATE TEMP TABLE t (a, b, etc); and it will only persist the duration of the current connection. i.e. Once you Close the DB connection, that table is no more, and even while the connection is open, it is only visible to the connection which created it. There are even ways

[sqlite] Performance comparison between SQLite and SQL Server?

On 2016/02/15 5:21 AM, admin at shuling.net wrote: > Hi, > > I am just curious whether there is a performance comparison between SQLite > and SQL Server? Surely SQL Server will perform better on huge database with > thousands of tables(more than 10GB size). But whether SQLite will perform >

[sqlite] Correlated subquery throwing an error

On 2016/02/15 3:01 AM, Gary Briggs wrote: > > This is the bit that doesn't seem to work; having > distance(a.x,a.y,b.x,b.y) in the ORDER BY clause on the inner query is > what appears to cause the error that it can't find a.x, from the outer query. > > It's not tied to it being a custom

[sqlite] applyng schema changes to several databases

On 2016/02/15 11:01 AM, Luca Ferrari wrote: > Hi all, > this could sound trivial but I've got a few hundreds SQLite 3 database > files, all with the same schema, that I need to alter adding a few > columns here and there. > While I'm pretty sure a simple sheel script that will execute, file >

[sqlite] Behavior When Comparing NULL Values

Others have explained the technicality of why you get the result - I would just like to point out that you should be careful of influencing your own expectation with the words you use in these statements. SQL only pretends to understand English, it really is a mathematical/logical language and

[sqlite] MIN/MAX query

Let me see if I can unpuzzle this question - kindly say if it is not correctly assumed: - I have a table: I L V 1 1 A 1 2 A 1 3 A 1 4 B 1 5 B 1 6 A 2 7 A 2 1 C 2 2 C I want to return the minimal and maximum L for each "group" of V in a given I.

[sqlite] MIN/MAX query

On 2016/02/18 4:59 PM, Igor Tandetnik wrote: > On 2/18/2016 4:55 AM, R Smith wrote: >> First of, your intended results require a fundamentally wrong assumption >> about Sets. (SQL is essentially operating on SETs and sets have no >> order). >> You should reall

[sqlite] MIN/MAX query

On 2016/02/18 7:24 PM, David Bicking wrote: > Whatever mangled the text must have put a 2 in front of the 7, cause the copy > in my sent mail box has a 1 in front of the 7. I never got a copy of my > email from the mailing list, so I never saw the mangled version, just quotes > of it. I

  1   2   3   4   5   6   7   8   >