[sqlite] Sqlite incompatibility with Postgres

On 2016/05/13 5:24 AM, dandl wrote: >>> Richard Hipp seems to be on record as saying: >>> "SQLite can be thought of as a derivative of PostgreSQL. SQLite was >>> originally written from PostgreSQL 6.5 documentation, and the SQLite >>> developers still use PostgreSQL as a reference platform to

[sqlite] Summing values by date, returning 0 for missing dates

I should add... The initial CTE setting up minDT and maxDT can be ANY dates, it doesn't have to come from the MIN/MAX in my_table, for example: WITH dtRange(minDT,maxDT) AS ( SELECT '2015-12-30 00:00:00', datetime(date('now','localtime','-3 months')) -- 30 December 2015 to today 3

[sqlite] Summing values by date, returning 0 for missing dates

On 2016/05/12 12:20 AM, Jonathan wrote: > Hi List, > Let's say I have a table with a simplified structure like this: > /create table my_table(// > //time_date DATE,// > //num INTEGER// > //);/ > > My data has three rows and looks like this: > /2016-01-01; 3//

[sqlite] Good way for CEIL, or is there a better way

On 2016/05/10 11:05 PM, Cecil Westerhof wrote: > 2016-05-10 22:06 GMT+02:00 Darren Duncan : > >> On 2016-05-10 12:03 AM, Cecil Westerhof wrote: >> >>> But it looks like that the following is also acceptable: >>> ? >>> CAST(ROUND(100.0 * rank / outOf + 0.499) AS int) AS percentage >>>

[sqlite] output from sqlite3 .dump not going where I expected

On 2016/05/08 10:57 AM, Jeremy Nicoll wrote: > I'm using Windows 8.1, 64bit, and yesterday installed the V3.12.02 > 64-bit DLL along with > the 32-bit SQLite tools. > > I was investigating the contents of a logs file created by my antivirus > & firewall package, > which keeps that file in > >

[sqlite] Version of the database

The file header contains the SQLite version that most recently modified the schema. You can see this using the cli, but not a pragma. If you are willing to dig a bit, you can retrieve it by reading the first 100 bytes or so from the file and examining the 4 bytes at offset 96. It's a

[sqlite] strftime accepts an illegal time string

On 2016/05/05 4:26 AM, Cecil Westerhof wrote: > The statement: > SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-04-31 17:19:59.670') > gives: > 2016-04-31 17:19:59.670 > > Should that not be NULL? > > It does with: > SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-04-32 17:19:59.670') > >

[sqlite] Fastest way to backup/copy database?

On 2016/05/04 8:38 PM, Dominique Devienne wrote: > On Wed, May 4, 2016 at 5:51 PM, Scott Robison > wrote: > This is going to become a bigger problem for us as the database will only get bigger so any advice welcomed. >> Perhaps, rather than backing up the live data, you create an

[sqlite] Fastest way to backup/copy database?

On 2016/05/04 2:35 PM, Rob Willett wrote: > Dominque, > > We put together a quick C program to try out the C API a few weeks > ago, it worked but it was very slow, from memory not much different to > the sqlite command line backup system. We put it on the back burner as > it wasn?t anywhere

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

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] 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] 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] 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] 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/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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] Sqlite do not use index collate nocase

On 2016/03/17 8:06 PM, Domingo Alvarez Duarte wrote: > Hello ! > > In one database I created an index with collate nocase but it seems that > sqlite do not recognize it as a candidate index for a select. > > Bellow is the schema, the real database has more than a million rows. If I > alter the

[sqlite] .import command/NULL values

On 2016/03/16 11:53 AM, Bernard McNeill wrote: > Any particular reason why the shell '.import' command cannot interpret || > as a NULL for the field? The reason the import mechanism doesn't cope with NULLs is not because it can't, but because it shouldn't - until the TEXT standard changes in

[sqlite] "Circular" order by

On 2016/03/11 5:52 AM, Stephen Chrzanowski wrote: > On Thu, Mar 10, 2016 at 2:16 PM, R Smith wrote: > > >> I do this kind of thing so often when filling a selection box for instance: >>SELECT 'None' >> UNION ALL >>SELECT City FROM Countrylist WHERE Co

[sqlite] backup memory database not working

On 2016/03/10 10:41 PM, asdf asdf wrote: > Hello, > what do you mean, please ? What code is not shown:my own code (and what > could be the cause then) using the example or what i posted in > stackoverflow. > > > I would be happy to solve it. Any information appreciated- He means that there

[sqlite] "Circular" order by

On 2016/03/10 8:37 PM, James K. Lowden wrote: > On Thu, 10 Mar 2016 10:17:57 +0100 > Alberto Wu wrote: > >> On 03/09/16 23:30, James K. Lowden wrote: SELECT P.id FROM ( SELECT 0 AS sect, id FROM t WHERE id >= 'pen' UNION ALL SELECT 1, id FROM t WHERE id < 'pen'

[sqlite] CTE for a noob

On 2016/03/09 10:01 PM, David Raymond wrote: > For my own clarification, the statements quoted way down below aren't exactly > equivalent, correct? > > "For each pair of columns identified by a USING clause, the column from the > right-hand dataset is omitted from the joined dataset. This is

[sqlite] "Circular" order by

On 2016/03/09 8:08 PM, Alberto Wu wrote: > On 03/09/16 17:26, R Smith wrote: >> Firstly, this is the best method - one I would use. UNION ALL is quite >> efficient. >> Secondly, the order by will be honoured - you can refer to the SQL >> standard for that even,

[sqlite] "Circular" order by

On 2016/03/09 6:07 PM, Alberto Wu wrote: > Hi all, > > I'm looking for suggestions... > What I want to achieve is to "roll" the result set of a query around by > a certain amount (as in offset + wrap around). > > For example, given that: > CREATE TABLE t (id TEXT NOT NULL PRIMARY KEY); > INSERT

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 2016/03/09 5:39 PM, Jean-Christophe Deschamps wrote: > > Sorry guys, I don't know why I wrote that. In fact I know: I shouldn't > be talking over the phone while reading the list. > > Of course I use correlated subqueries a lot, but never had to > re-select a column from the enclosing

[sqlite] Insert or Replace Trouble

ndex on id ); CREATE TABLE messages ( id numeric, ); CREATE UNIQUE INDEX UIdxMEssages ON messages(id); -- Same as above but outside of Table dec On 2016/03/09 5:10 PM, R Smith wrote: > > > On 2016/03/09 5:03 PM, Tilsley, Jerry M. wrote: >> All, >> >> I'm trying t

[sqlite] Insert or Replace Trouble

On 2016/03/09 5:03 PM, Tilsley, Jerry M. wrote: > All, > > I'm trying to use the INSERT OR REPLACE syntax so that I don't have to worry > about duplicate entries without creating a trigger. Using the schema: > CREATE TABLE messages (id numeric); > > and using the SQL: > insert or replace into

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 2016/03/09 4:35 PM, Igor Tandetnik wrote: > On 3/9/2016 9:32 AM, Jean-Christophe Deschamps wrote: >> At 15:16 09/03/2016, you wrote: >>> select id from a where id not in (select a.id from b); >> >> Shouldn't the engine bark on this, like it does on the modified version: >> >> select id from

[sqlite] CTE for a noob

On 2016/03/08 5:02 AM, Stephen Chrzanowski wrote: > Now I'll have to use USING a bit more often to get the drift and get out of > this multi-call thing. I sort of see what is going on here, but practice > is whats needed. "USING" has three uses in SQLite, first to enlist a virtual table,

[sqlite] Fastest way to find whether at least one row has a certain column value

On 2016/03/06 1:39 PM, Paul Sanderson wrote: > I understand this - but, there always a but, I still would like to do > something. Applying the limit anyway and then telling them the query > has been limited might be a solution. > > Time is usually not an issue but as the results are loaded into a

[sqlite] Changing the default page_size in 3.12.0

On 2016/03/06 10:00 AM, Keith Medcalf wrote: > On Saturday, 5 March, 2016 12:12, Richard Hipp said: > >> A key point of this email thread is that SQLite performance seems to >> be maximized when the database page size matches the CPU memory page >> size and the disk sector size - currently 4096

[sqlite] How does your sqlite script binding handle aggregate UDFs?

On 2016/03/06 1:24 AM, Stephan Beal wrote: > On Sat, Mar 5, 2016 at 11:58 PM, Richard Hipp wrote: > >> On 3/5/16, Stephan Beal wrote: >>> On Sat, Mar 5, 2016 at 10:43 PM, Domingo Alvarez Duarte < >>> >>> The scenario i'm concerned about is that sqlite calls my aggregate N >> times, >>> then an

[sqlite] Fastest way to find whether at least one row has a certain column value

On 2016/03/05 6:21 PM, Paul Sanderson wrote: > Thanks Richard > > so suppose I have two tables table1 and table2 each with 1000 rows and > say 100 columns some containing large blobs. > > My user choose a query "select * from table1, table2" > > I can modify the query and do a "select count(*)

[sqlite] Understanding conditional triggers

On 2016/03/03 2:55 AM, Sam Carleton wrote: > I am working on a trigger that only needs to be fired when the value for > the column 'Selected' is 1. How exactly do I write that trigger? Here is > what I have come up with: > > CREATE TRIGGER IF NOT EXISTS tu_Favorite_add_to_SlideShowImage AFTER

[sqlite] Mixing text and numeric values in comparisons

On 2016/03/04 4:29 PM, Paul van Helden wrote: > On Fri, Mar 4, 2016 at 4:23 PM, Igor Tandetnik wrote: > >> On 3/4/2016 9:15 AM, Paul van Helden wrote: >> >>> So I have to detect integers in order to avoid the .0 >>> >> WHERE CAST(A as integer) = 1 >> >> > Not quite going to work either. I

[sqlite] Can I implement a scrolling window using LIMIT and OFFSET ?

On 2016/03/04 10:35 AM, Darren Duncan wrote: > On 2016-03-03 11:27 AM, James K. Lowden wrote: >> >> You say, "record ... the condition ... to scroll [the table]". I'm >> sure I don't know what "condition" you mean. >> >> You appear to be doing something like: >> >> offset = 0 >> do >>

[sqlite] Can I implement a scrolling window using LIMIT and OFFSET ?

On 2016/03/03 5:10 AM, ?? wrote: > So according to your answers and others, this limitation is always there even > the document said "obsolete" ? Just want to double confirm. > > > Thanks! To add to what Clemens already mentioned - this problem is not an SQLite problem, this problem is true

[sqlite] Are BLOB stored inline with the rest of the record fields?

On 2016/03/02 6:47 PM, Eric Grange wrote: > Hi, > > I am wondering if very small BLOBs are stored inline with the record fields > (in the same page) or in another page? > > So far I had not worried about it, but now I have a usage case where I need > to store 128 and 256 bit integers (numbers,

[sqlite] CREATE TABLE parser

On 2016/03/02 9:51 AM, Marco Bambini wrote: > Hi all, > I developed a CREATE TABLE parser in C that is able to extract every details > about an sqlite table (table and columns constraints, foreign keys, flags and > so on). > So far it seems to work pretty well but I would like to stress test

[sqlite] Random-access sequences

On 2016/03/02 2:26 AM, James K. Lowden wrote: > On Tue, 1 Mar 2016 08:15:25 -0500 > Richard Damon wrote: > >>> The theoretical maximum number of rows in a table is 264 >>> (18446744073709551616 or about 1.8e+19). This limit is unreachable >>> since the maximum database size of 140 terabytes

[sqlite] Using My Own Encryption Possible ?

On 2016/02/29 3:28 PM, Dominique Devienne wrote: > On Mon, Feb 29, 2016 at 2:18 PM, Simon Slavin wrote: > >> Another way would be to write your own authorizer which, among other >> things, did some encryption of the data involved. >> > > It's

[sqlite] Possible bug in the SQL parser

On 2016/02/29 12:49 PM, Jo?o Ramos wrote: > Maybe this has been fixed then? This is what I'm getting: > > select sqlite_version(); -- 3.8.10 > > select sqlite_source_id(); -- 2015-05-04 19:13:25 > 850c11866686a7b39d7b163fb60898c11283688e > > > WITH > > tA(id, name) AS > > ( > > SELECT 1, "a"

[sqlite] How to modify page_size

On 2016/02/25 9:40 AM, Jim Wang wrote: > hi,all > How could I modify the page_size of the Data base? I use the following > method, but could not change the page_size. >nRet = sqlite3_exec(m_pDB, "PRAGMA page_size=8192;", 0,0,); > Why? Because the Pragma specifies the ideal,

[sqlite] Why is a separate journal file needed ?

On 2016/02/24 3:49 PM, Richard Hipp wrote: > On 2/24/16, Simon Slavin wrote: >> Why can't the information which SQLite >> stores in a journal file be put in the database file ? > Doing so would double the size of the database file. Every database > file would contain extra space (normally

[sqlite] SQLite I/O tuning redux

On 2016/02/21 1:49 AM, Richard Hipp wrote: > On 2/20/16, Dave Baggett wrote: >> Question: can I force SQLite to keep an index purely in memory, > No. Because if you did, other processes updating the table would have > no way of also updating the index. > Of course - but how about being able

[sqlite] Process duplicate field values

On 2016/02/19 8:00 AM, admin at shuling.net wrote: > Hi, > > > > I create a table as follows: > > > > CREATE TABLE MyTable (F1 INTEGER, F2 INTEGER); > > > > Then add the following records: > > > > INSERT INTO MyTable (F1, F2) Values (1, 2); > > INSERT INTO MyTable (F1, F2) Values (1,

[sqlite] determining is-leap-year in sqlite

On 2016/02/18 10:38 PM, Stephan Beal wrote: > On Thu, Feb 18, 2016 at 9:16 PM, Stephan Beal > wrote: > >> Okay, i've hit a small stump and i'm looking for a hint without giving it >> away: >> >> January and February 2016: >> >> [stephan at host:~/tmp]$ sqlite3 < cal.sql >>1 2

[sqlite] determining is-leap-year in sqlite

On 2016/02/18 10:16 PM, Stephan Beal wrote: > On Thu, Feb 18, 2016 at 8:59 PM, R Smith wrote: > >> etc. >> Nice job on the calendar and good luck with the presentation! > > Okay, i've hit a small stump and i'm looking for a hint without giving it > away: &g

[sqlite] determining is-leap-year in sqlite

On 2016/02/18 9:31 PM, Stephan Beal wrote: >> The first CTE sets up some parameters in the first 3 fields used to draw >> the graph - play with those parameters for fun. >> > i wouldn't even know what to do with them :/. Change them of course! :) - to be specific, the first 3 values from the

[sqlite] determining is-leap-year in sqlite

On 2016/02/18 8:34 PM, Stephan Beal wrote: > Every calendar known to man sucks rocks in some regard or other, so > i'm not gonna sweat it. This is just a demo, and i've got a few hours > of budget left on it, so i'm working on this as the finale. (The > Mandelbrot CTE will be first, just to

[sqlite] determining is-leap-year in sqlite

On 2016/02/18 7:46 PM, Stephan Beal wrote: > Hi, all, > > i just found a useful trick i thought someone else might be able to use... > > As part of a presentation i'm preparing to introduce colleagues to CTEs, > i'm attempting to build a calendar (with output similar to the Unix 'cal' >

[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

[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

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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] 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] Is the first column of a composite primary key, special?

On 2016/01/31 3:54 PM, Yannick Duch?ne wrote: > Hi all, > > Another mystery to me. Given this test table: > > CREATE TABLE t (a TEXT, b TEXT, c INTEGER, PRIMARY KEY (a, b, c)) > > ??this query: > > SELECT Sum(c) FROM t GROUP BY a > > ??executes faster than any of these two: > >

[sqlite] Problem with distinct select query

On 2016/01/29 4:51 PM, Gary Baranzini wrote: > Hi, > > I have a query where I select 20 random distinct rows. > > SELECT DISTINCT formulas.pinyin, formulas.majorcategory, > majorfcategory.item_name > FROM formulas > JOIN majorfcategory ON majorfcategory.id=formulas.majorcategory > ORDER BY

[sqlite] Best way to store only date

On 2016/01/30 3:22 PM, E.Pasma wrote: > The diagram got broken in my email and here is another try: > > Needs to be light | Needs to be| Needs to do | > (small footprint) | Human-Readable | calculations | > - | ---| | > YES |

[sqlite] Best way to store only date

On 2016/01/29 5:23 PM, Igor Tandetnik wrote: > > Personally, I prefer cast(strftime('%Y%m%d', 'now') as int) - in other > words, storing calendar dates as integers like 20160129. The main advantage of this format is that it is of course human-readable, even as an integer. The important

<    2   3   4   5   6   7   8   >