Re: [sqlite] [EXTERNAL] compressed sqlite3 database file?

On 10 Apr 2019, at 7:33am, Hick Gunter wrote: > Your comparison is unfair. A .gz file is COMPRESSED by gzip and not in any > way human readable without uncompressing it first. How big is the result if > you compress the sqlite file through gzip? Or how big is the TSV file you get if you

Re: [sqlite] Prepared Statement Without a Database Instance

On 5 Apr 2019, at 7:07pm, Lee, Jason wrote: > Is there any way to create a prepared statement without an associated > database instance? I have one fixed insert that I am running on many separate > databases that are inserting different data sets, and would like to amortize > the cost of

Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query

On 5 Apr 2019, at 2:45pm, Jose Isaias Cabrera wrote: > then do this, > > select > a.*, b.* from t as a join z as b on a.a = b.f > where a.a = 'p006' > AND > a.idate = (select max(idate) from t where a = a.a) > AND > b.idate = (select max(idate) from z where f = a.a) > ORDER BY a.a > ; For

Re: [sqlite] Remove row to insert new one on a full database

On 5 Apr 2019, at 1:45pm, Arthur Blondel wrote: > I'm limited in space so when the DB is full (when sqlite3_exec() returns > SQLITE_FULL when I try to insert a new row), I remove the oldest row If SQLite returns SQLITE_FULL you cannot reliably do anything else to the database. Because even

Re: [sqlite] Remove row to insert new one on a full database

On 5 Apr 2019, at 4:14am, Richard Damon wrote: > I think is logic is to attempt to insert a row, and if rather than > inserting it, the call returns the error condition, 'Database Full' Okay. So now we understand what OP meant by the database being full. SQLITE_FULL does not mean 'Database

Re: [sqlite] ANN: SQLite3 Decimal Extension

On 4 Apr 2019, at 10:12pm, Lifepillar wrote: > This is essentially a pragmatic choice, as the semantics of NULLs is > unspecified and ambiguous. The way SQL handles NULLs may sometimes appear inconsistent, but is the result of SQL handling rows as sets. Some of the behaviour is, carefully

[sqlite] How many digits do you need ?

Please allow me a little levity, spinning off an earlier discussion of how many digits a decimal number type needs to store. " Carbrook, for instance, is at -27.673862 153.25624 and at -27.673861999297635 153.25624388146. [...] those

Re: [sqlite] ANN: SQLite3 Decimal Extension

On 3 Apr 2019, at 6:51pm, Warren Young wrote: > On Apr 3, 2019, at 6:30 AM, Lifepillar wrote: > >> does SQLite support indexes on blobs? > > It claims to: Indeed. Be careful to verify whether, from the perspective of your programming language, it considers the first or the last byte to be

Re: [sqlite] ANN: SQLite3 Decimal Extension

On 3 Apr 2019, at 6:37pm, Warren Young wrote: > I once calculated that 30 digits was enough to give a precise location to > every particle the size of a grain of sand or larger in the known universe. > You just need to define as many columns in your database as is needed for > your physics:

Re: [sqlite] Can I get help with db design for SQLite use?

On 2 Apr 2019, at 3:48pm, Tom Browder wrote: > I need help with a db design to be modeled for use with SQLite. The design > uses foreign keys and I am just now using the foreign pragmas and other parts > of SQLite to help me make it all work together. > > Is this list appropriate for

Re: [sqlite] Database Encryption (System.Data.SQLite)

On 1 Apr 2019, at 3:30pm, Mattock Paul wrote: > Thanks, do we have any idea on what Algorithm is used? Since more than one algorithm is available, this would be something selected by the software you were using. So I can only suggest you read the source code for the bit of the software which

[sqlite] Documentation enhancement request: VACUUM

The documentation for VACUUM says "A VACUUM will fail if there is an open transaction" Could this statement be changed to make it clear whether this refers to other connections having an open transaction, and whether it means that VACUUM fill fail if it itself is inside a transaction ? I seem

Re: [sqlite] importing a large TSV file

On 1 Apr 2019, at 2:41pm, Simon Slavin wrote: > BEGIN; >... CREATE all your INDEXes ... >VACUUM; -- optional > END; Apologies. The documentation says "A VACUUM will fail if there is an open transaction" I'm not sure whether this means that VACUUM must be o

Re: [sqlite] importing a large TSV file

On 1 Apr 2019, at 12:14pm, Gert Van Assche wrote: > I need to create an SQLite db from a large TSV file. (30 GB) > Are there any setting I can give to the db so I can speed up the import? If you're doing it using the SQLite CLI tool, then just rely on the tool to do it in the most convenient

Re: [sqlite] Database Encryption (System.Data.SQLite)

On 1 Apr 2019, at 1:15pm, Mattock Paul wrote: > Just to confirm I am using the nuget package (System.Data.SQLite v1.0.109.2) This uses PCL Crypto, which in turn accesses crypto implemented in PCL itself rather than implmenting its own. A list of crypto methods it supports, tabled against OS,

Re: [sqlite] Is it possible to transpose a table using SQL?

On 1 Apr 2019, at 10:18am, Dominique Devienne wrote: > I can't seem to find that one, but I found another here: > https://metacpan.org/pod/SQLite::VirtualTable::Pivot I looked at that one, but it doesn't do what OP wants, which is to swap rows and columns without the programmer having to

Re: [sqlite] Is it possible to transpose a table using SQL?

On 31 Mar 2019, at 9:07pm, Shane Dev wrote: > Is it possible to create a view which switches rows and columns of a > dynamically changing table? Sorry, but no. A VIEW is just a saved SELECT statement. If you can't do it in a SELECT statement, you can't do it in a view. And you can't do

Re: [sqlite] How many concurrent sqlite3 connections are allowed?

On 29 Mar 2019, at 11:29pm, Peng Yu wrote: > I have many concurrent sqlite3 connections from many python scripts. I am not > sure this causes some deadlock condition, but my scripts do not show any > progress after some initial progress. There is no limit on how many connections can talk to

Re: [sqlite] is this possible

On 29 Mar 2019, at 1:55pm, Dan Kennedy wrote: > The error doesn't occur unless you actually query the view though, correct? My theory on SQLite was that the VIEW definition was only syntax-checked and not compiled in any way. So you could do CREATE VIEW v as select * from t ORDER BY a;

Re: [sqlite] Feature request, sqlite3_stmt_action

On 28 Mar 2019, at 10:25am, Dominique Devienne wrote: > Some info about the statement from EXPLAIN QUERY PLAN, that DRH would agree > to, and accept to "publicly document" and thus support would be nice, You want something like EXPLAIN EFFECTS OF and it should answer with zero or more

Re: [sqlite] UPSERT with multiple constraints

On 27 Mar 2019, at 10:59pm, Thomas Kurz wrote: > Now when inserting an item that already exists (according to the uniqueness > definition above), the existing item should be updated with the new name and > A, B, C properties. Mmm. Your database design doesn't work the way SQL does. Your

Re: [sqlite] Row locking sqlite3

On 27 Mar 2019, at 6:04pm, Thomas Kurz wrote: > Ok, well very interesting and I'd never have had this idea, but indeed it > works: within a transaction, it takes only a few seconds. This is very > surprising as to me, a single DELETE statement is nothing more than that: a > single atomic

Re: [sqlite] UPSERT with multiple constraints

On 27 Mar 2019, at 2:40pm, Thomas Kurz wrote: > So what I'm looking for is some kind of "ON CONFLICT (col1, col2) OR CONFLICT > (col1, col3, col4, col5) DO UPDATE". Can't be done in a single instruction. The nearest you can get to this would involve using triggers:

Re: [sqlite] Row locking sqlite3

On 27 Mar 2019, at 11:48am, Thomas Kurz wrote: > Imho quite simple: There are operations that take a long time. I observe this > behavior especially with DELETE in combination with ON CASCADE DELETE. Can > take half an hour, and meanwhile the database remains locked. A timing that long

Re: [sqlite] Default behavior for attached bases

On 27 Mar 2019, at 6:46am, raanan barzel wrote: > I would like to suggest a different default behavior (potentially as an > option): "when the schema-name prefix is not used, then *all* the tables are > accessed, potentially with a qualifier for distinctiveness." This would break numerous

Re: [sqlite] Row locking sqlite3

The other aspect of this is to wonder why OP wants row locking. They might think that it's faster than locking the whole database, or that it will allow their program to work without any pauses. Neither of these are necessarily true. ___

Re: [sqlite] Get SQLite Database from React Native mobile app

On 25 Mar 2019, at 6:45am, Ashu Thakkar wrote: > How to get Database created from react native mobile app. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Diagnosing stale readers

On 24 Mar 2019, at 12:45pm, Gerlando Falauto wrote: > if there's a way > of getting a list of the current transactions or locks. You can iterate through all the current statements for a connection: However many wrappers do not implement this function

Re: [sqlite] Is there a way to select using cid?

On 22 Mar 2019, at 9:34pm, Peng Yu wrote: > There are cid's for each table. Is there a way to use "select" with > cid's instead of their names? In recent versions of SQLite you can use the result from pragma_table_info('test') as if it is a table. So you can do SELECT select * from

Re: [sqlite] Incorrect result from INNER JOIN when column has a value of "WHITE"

To quote a string in SQLite use apostrophes. This refers to a five character string: 'black' If you surround something with double quotes SQLite understands it as an entity name. Entities are things like tables and columns. So this is understood to be a column name: "black" . Because

Re: [sqlite] cmd line question on import

On 22 Mar 2019, at 3:06pm, Jose Isaias Cabrera wrote: > I have a db in c:\temp\sqliteDB.sqlite3 which has the Project_List table. I > can use, > > sqlite3 "c:\temp\sqliteDB.sqlite3" > > and copy and paste the content of the file, and it works fine. But I am > trying to do this from the

Re: [sqlite] filling a key/value table

On 21 Mar 2019, at 4:04pm, Hick Gunter wrote: > CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER, primary > key (keyid, value, location) ) WITHOUT ROWID; Can't answer the question you posed and I'm happy to see Igor could. But I wanted to speak against including a BLOB field

[sqlite] Do SQLITE_LOCKED situations call the busy-handler ?

(For those playing along at home, SQLITE_LOCKED is a special-case version of SQLITE_BUSY. You get it only when the competing access attempts come from the same connection (multi-tasking) or different connections sharing the same cache.) Suppose I'm in a situation where I would get

Re: [sqlite] Distinguish type of statements

On 19 Mar 2019, at 8:53pm, siscia wrote: > Do you think it would be possible to add a function that does what I need? No. I've not part of the development team but I think it's quite complicated. Also, the fact that there's already a function which does what you want makes it unlikely

Re: [sqlite] Distinguish type of statements

On 19 Mar 2019, at 6:40pm, siscia wrote: > Given a statement, is there any way to know if it is an UPDATE, DELETE, > INSERT, SELECT or something else? It's not simple but if you are actually trying to execute that statement, you can register an authorizer function.

Re: [sqlite] Backing up a SQLite database without the CLI

On 18 Mar 2019, at 3:21pm, Jonathan Moules wrote: > At this point I'm starting to think that the best option is to create a new > database with the requisite structure and copy the data across via an ATTACH > (there are only two tables and one will almost always be empty at this point). That

Re: [sqlite] Backing up a SQLite database without the CLI

On 18 Mar 2019, at 1:10pm, Jonathan Moules wrote: > I was wondering if there was a good way of backing up an SQLite database if > you do *not* have access to the SQLite command line tool (which I know has > .backup - https://stackoverflow.com/a/25684912). [snip] > I've considered simply

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

On 17 Mar 2019, at 5:19pm, niklas wrote: > Is this a recent change in Sqlite or have I misunderstood something? The > Sqlite documentation still does not seem to say that column aliases can be > used in the WHERE clause at least. You are correct in two things. Column aliases cannot be relied

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

On 15 Mar 2019, at 7:02pm, niklas wrote: > The data used for sqlite_stat1 in create.txt is taken from the real data, > it's copied from the sql-dump generated just after running ANALYZE. Okay. I should have guessed that. Sorry for doubting you. You seem to have figured out a work-around for

Re: [sqlite] Malformed schema (orphan index) on particular db with the version 3.27.2

On 16 Mar 2019, at 9:34am, Max Vlasov wrote: > So I suspect that the file is a very rare example of inconsistency of data > not compatible with recent version, but working with older ones. Integrity-checking has been improved since 3.26.0. Newer versions of SQLite check for types of

Re: [sqlite] Query planner: Scanning subqueries vs using automatic covering index

On 15 Mar 2019, at 7:35am, niklas wrote: > Dropping analyze (sqlite_stat1) information from the database made > the problem go away, i.e. made sqlite use index instead of scanning. Instead of dropping ANALYZE information, did you try updating it instead, by running ANALYZE again ? Ah ... >

Re: [sqlite] difference between *.db *.db3 *.sqlie2 *.slite3

On 15 Mar 2019, at 10:29am, Mohsen Pahlevanzadeh wrote: > What differ between *.db *.db3 *.sqlie2 *.slite3 files? Be very careful of .sqlite2 files. The name suggests an obsolete version of SQLite that is incompatible with everything from the last decade. Apart from that, SQLite databases

Re: [sqlite] SEE

On 13 Mar 2019, at 10:50pm, Williams, Kevin wrote: > Any articles out there giving some hint how we use the SEE version of SQLite > we compiled with our C# program that references System.Data.SQLite? Try this: and come back if you have

[sqlite] Developer questions about the Online Backup API

If the source database is changed while the Online Backup API is running, it returns to the beginning of the database and starts again. I have a couple of questions which might be useful, especially if the database is changed only by the same connection as it performing the backup. There's a

Re: [sqlite] Is it possible to use sqlite online-backup system in an ongoing manner?

On 13 Mar 2019, at 2:31pm, John Smith wrote: > I am working with IN-MEMORY database. > When my program starts I load data from file-system DB into my IN-MEMORY DB. > All other SQL operations are performed directly on my IN-MEMORY database. > This is in order to keep performance high. First,

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

On 12 Mar 2019, at 9:41pm, Ted Goldblatt wrote: > SQLITE_OMIT_xxx, SQLITE_DEFAULT_CACHE_SIZE, > SQLITE_THREADSAFE None of these are a problem, assuming your embedded app does not do multi-threading. > One of these is SQLITE_TEMP_STORE, which is set to > "Always use memory" which strikes me a

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

I may have missed this already being discussed. Will you have access to a copy of the database as it was before corruption testing ? Can you use SQLite to see whether it is already corrupt ? Or can the test run on a brand new, freshly-created database ? If neither of those, your test won't

Re: [sqlite] Apparent power fail data loss in embedded use - SQLite newbie

Other posters have taken care of very important aspects of your circumstances, but I wanted to mention one I didn't see anyone mention. Settings. If you compile SQLite without changing compilation settings, and use it without changing defaults, SQLite is extremely good at avoiding corruption,

Re: [sqlite] Maximum result set size

On 12 Mar 2019, at 6:38am, niklas wrote: > Surely that works the same way as sqlite3_step in respect to memory use since > it's using step internally and just forwards the results to the callback > function of exec. > > Or did I miss something? You missed nothing. _exec() returns only the

Re: [sqlite] Maximum result set size

On 11 Mar 2019, at 8:32pm, Wout Mertens wrote: > Don't listen to me, Simon's answer is way better :) Your answer was absolutely correct. I just answered some other stuff too. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Maximum result set size

On 11 Mar 2019, at 7:30pm, Tim Streater wrote: > What is the maximum size in bytes that a result set may be? And what happens > if that size were to be exceeded? [The following is simplified for clarity. I discuss only worst cases and ignore caching.] SQLite does not prepare an entire

Re: [sqlite] Import data into a temporary table

On 7 Mar 2019, at 9:45pm, Eric Tsau wrote: > Is it possible to add the option of importing data into a temporary table? Can you rephrase your request ? You can import data into a temporary table > .import dump.csv temp.table Does this command not work correctly ? Simon.

Re: [sqlite] Segmentation fault running a query

On 5 Mar 2019, at 8:06pm, Peter Hardman wrote: > If i run the following query from Python 3.7 using the sqlite3 DBAPI adapter > or from sqlitebrowser I get a segmentation fault. Please find the SQLite command-line tool on your computer. If you don't already have one you can download one for

Re: [sqlite] Equiv stmts, different explain plans

On 5 Mar 2019, at 2:06am, kk wrote: >>> select * from t1 >>>where c=1 and d in (select d from t2 where c=1); >>> select * from t1 >>>where c=1 and d in (select d from t2 where t2.c=t1.c); > DRH, many thanks for your reply, I was expecting same output because I > believe stmts to be

Re: [sqlite] Handling ROLLBACK

On 5 Mar 2019, at 3:22am, Rowan Worth wrote: > Wait what? If I've understood correctly you're describing a situation where > statements outlive their transaction context? The documentation on transactions covers this possibility. This includes continuing to do _step() for one statement while

Re: [sqlite] Handling ROLLBACK

On 3 Mar 2019, at 2:29pm, Luuk wrote: > Conclusion: RESTORE does not end TRANSACTION ? Your statement is correct. However, RESTORE is a partner of SAVEPOINT. My question does not consider SAVEPOINTs. Simon. ___ sqlite-users mailing list

[sqlite] Documentation change request

talks about PRAGMA read_uncommitted = boolean; Explicitly stating that the argument should be boolean. However the explanation of the effect of the PRAGMA talks about its argument as if it is SERIALIZABLE. No boolean value to indicate

Re: [sqlite] Handling ROLLBACK

To summarize, the list feels that this is an incorrect model BEGIN; ... first set of commands ROLLBACK; ... second set of commands END; whereas this is how things are meant to work: BEGIN; ... first set of commands ROLLBACK; BEGIN; ... second set of

[sqlite] Handling ROLLBACK

I have run experiments in the shell tool, using different journal modes, but I would like to know whether my observations are cannon or just one-offs. Contributions welcome from all, especially those who have read SQLite source code, and SQL language-lawyers whether or not you're familiar with

Re: [sqlite] Deserialize a WAL database file

On 1 Mar 2019, at 10:27am, Lloyd wrote: > I am able to serialize, deserialize and prepare a SQL query against the > rollback database. When I do the same against the WAL database file, the > 'prepare' statement fails with code '1'. It's possible that some of the data for the WAL database is

Re: [sqlite] TCL API: substitution of NULL for non-existent variables

On 28 Feb 2019, at 4:33pm, Donald Allen wrote: > The difficulty with this is that there are fields in my database that *can* > legitimately be NULL under certain circumstances and not NULL in others. Is it possible to tell from the other fields in that row whether the NULL is legit or not ?

Re: [sqlite] Database backup with writers present?

On 27 Feb 2019, at 4:16pm, Richard Hipp wrote: > On 2/27/19, Stephen Chrzanowski wrote: >> Does write blocking still come into play when using "vaccum into", > > The VACUUM INTO command is a reader. So (in WAL mode) some other > process can continue writing while the VACUUM INTO is running.

Re: [sqlite] fyi, suspected typos in documentation: https://sqlite.org/c3ref/total_changes.html

On 25 Feb 2019, at 2:12pm, Donald Griggs wrote: > https://sqlite.org/c3ref/total_changes.html > > This the sqlite3_total_changes(D) interface only reports the number of rows > that changed due to SQL statement ... > > Perhaps should be: > [[Thus]] the sqlite3_total_changes(D) interface only

Re: [sqlite] Disk I/O errors

On 23 Feb 2019, at 6:41pm, Tim Streater wrote: > The PHP interface to SQLite doesn't appear to give me access to the extended > codes, unfortunately. I used to do SQLite from PHP myself. Hold on ... Bah. I agree with you. There doesn't seem to be any way to do it. You can't even extend

Re: [sqlite] Disk I/O errors

On 23 Feb 2019, at 5:55pm, Tim Streater wrote: > I have a hosted web site using the SQLite functions from PHP. The page where > PHP is used was failing, and on investigation this is because an SQLite > function called from within PHP is now returning: > > Code: 10 (SQLITE_IOERR) > Msg: disk

Re: [sqlite] How to refer to `this` table?

On 23 Feb 2019, at 6:43am, Rocky Ji wrote: > CREATE TABLE Aliases ( > alias_id INTEGER PRIMARY KEY AUTOINCREMENT, > real_name TEXT NOT NULL, > aka TEXT NOT NULL, > CONSTRAINT xyz UNIQUE (real_name, aka), > CONSTRAINT noCircularRef_A CHECK ( >real_name NOT IN (SELECT aka FROM Aliases) >

Re: [sqlite] Find if an upsert operation did an insert or an update.

On 22 Feb 2019, at 5:18pm, David Raymond wrote: > Also I think total_changes will just tell you that the statement changed > "something", but you still won't know if it was an insert or an update, or > how many of each. As OP wrote, you do it before and after "INSERT OR IGNORE" OR "UPDATE ...

Re: [sqlite] Find if an upsert operation did an insert or an update.

On 22 Feb 2019, at 4:08pm, Constantine Yannakopoulos wrote: > I would like to find whether an upsert operation actually did an insert or an > update, preferably without having to execute extra SQL statements before or > after it. Try int

Re: [sqlite] What is the recommended way to write to views?

On 20 Feb 2019, at 11:14am, Rocky Ji wrote: > create view summary > as >select >a.a_attribute, >b.b_attribute, >c.c_attribute >from >m2mAlphaBeta m >inner join Alpha a on a.a_id = m.FK_a_id >inner join Beta b on b.b_id = m.FK_b_id >

Re: [sqlite] ON CONFLICT with partial indexes

On 18 Feb 2019, at 4:41pm, Charles Leifer wrote: > Simon, I appreciate that, but your comment seems to contradict the example I > provided, as well as the example Dr. Hipp provided. Am I misunderstanding? SQLite can compare two comparators. In Dr. Hipp's example the index says "extra IS NOT

Re: [sqlite] Reading a table from inside a scalar function

On 18 Feb 2019, at 4:22pm, Arun - Siara Logics (cc) wrote: > Do you mean to say SQLite might keep function results across queries? It depends where you use it. See section 4 of However, the documentation should prepare you for the idea that you cannot

Re: [sqlite] ON CONFLICT with partial indexes

On 18 Feb 2019, at 3:23pm, Charles Leifer wrote: > At some point before actually executing the query I'm calling > sqlite3_bind...() to set the value of the parameter placeholder. Presumably > SQLite would know at that point that a certain index is usable (or in this > case, that a certain

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

On 18 Feb 2019, at 1:33pm, Rocky Ji wrote: > Here's the new thing: https://pastebin.com/raw/pSqjvJdZ > > Again, can we get rid of them sub-query? The "sum()" with the "group by" in the subquery make it difficult. There's nothing wrong with that query just as it is. In that form SQLite will

Re: [sqlite] Reading a table from inside a scalar function

On 18 Feb 2019, at 1:15pm, Arun - Siara Logics (cc) wrote: > Thank you, for the detailed advice, info and the pointer. Is there a faster > way to query the table using row id, that is, skip the query parsing and > planner? No. For fastest queries, use "WHERE rowid = ", and list the columns

Re: [sqlite] ON CONFLICT with partial indexes

On 17 Feb 2019, at 8:51pm, Charles Leifer wrote: > Is this a bug? Just to make things easier, which version of SQLite are you using in your Python SQLite library ? If you don't know, you can find this out using SELECT sqlite_version(); SELECT sqlite_source_id(); The second may give

Re: [sqlite] LIKE operator and collations

You can write your own LIKE function and use that: If you have the source for "collate TURKISH_CI" then you might be able to use it in your own function. Simon. ___ sqlite-users mailing list

Re: [sqlite] Tips for index creation.

On 14 Feb 2019, at 6:06pm, Randall Smith wrote: > One feature that would be useful for the sqlite3 command shell would be a > mode where the duration of the operation is reported after the operation runs > (".timing"?). .timer ON Simon. ___

Re: [sqlite] Building Amalgamation

On 13 Feb 2019, at 6:09am, D Burgess wrote: > 3.6.20 is a loong time ago. Which led me to think that maybe that list is no > longer be accurate. > Is there an easy way to extract the available options for the latest release? This is not something I normally worry about, but is the list here

Re: [sqlite] Building Amalgamation

On 13 Feb 2019, at 5:56am, D Burgess wrote: > Is there a definitive list of "defines" for building the amalgamation. You might mean this: If it's something else you want, post again and we'll try to help. Simon.

Re: [sqlite] Why Error: ambiguous column name: ProjID

On 11 Feb 2019, at 4:51pm, Jose Isaias Cabrera wrote: > Thanks. Yes, sometimes I should revise 3 or 4 times before asking. :-) As > soon as I sent it, I figured it out. It wouldn't help. You can read it ten time, rewrite it three times, and explain it to your pet duck. You still won't

Re: [sqlite] Checking differences in tables

On 9 Feb 2019, at 3:49pm, Jose Isaias Cabrera wrote: > Imagine this scenario:I have this table, > > t (a PRIMARY KEY, b, c, d, e) > > that contains yesterday's data. Today, I rename that table to, > > t_20190208 (a PRIMARY KEY, b, c, d, e). That's not how you use tables. Your software

Re: [sqlite] Was there an announcement of 3.27?

On 8 Feb 2019, at 6:25pm, David Raymond wrote: > https://sqlite.org/releaselog/3_27_1.html "Add options "--expanded", "--normalized", "--plain", "--profile", "--row", "--stmt", and "--close" to the ".trace" command." Is there a discussion of these anywhere ? Simon.

Re: [sqlite] Problem to delete all data with a LIKE

On 8 Feb 2019, at 1:49pm, Ludovic Gasc Lemaire wrote: > Thanks for your tip, this command has found problems, see below. > It should explain this strange behavior with DELETE ? Yes. The corruption you have found in your database can explain lots of weird and non-standard behaviour. In fact,

Re: [sqlite] Committing changes to the database without releasing a writer lock

On 7 Feb 2019, at 9:53pm, Theodore Dubois wrote: > I'd like to essentially commit changes to disk in the middle of the > transaction, resulting in a transaction that is atomic with respect to other > database connections but is two atomic transactions with respect to the > filesystem. Would

Re: [sqlite] GROUP BY and ICU collation

On 8 Feb 2019, at 5:47am, li...@herger.net wrote: > In sorting it seems to consider Š "the same" as S, but it doesn't in > grouping. That may be a bug. Can you construct a small test database, where your data features, say, R, S, T, and Š, and show how ORDER BY behaves differently to GROUP

Re: [sqlite] GROUP BY and ICU collation

On 7 Feb 2019, at 5:02pm, li...@herger.net wrote: >> If you don't want to have to keep specifying the COLLATE, put it in the >> table definition: >> CREATE TABLE ... ( ... artist TEXT COLLATE de_DE ...) > > Thanks for the hint. But the application is localized to the user's language. >

Re: [sqlite] Problem to delete all data with a LIKE

On 7 Feb 2019, at 3:51pm, Ludovic Gasc Lemaire wrote: > When I launch this SQL query: > DELETE FROM astdb WHERE key LIKE '%SIP%'; > > It deletes between 3 and 40 lines, but not all lines. > And each time I launch this SQL query, it removes between 3 and 40 other > lines, but not all lines.

Re: [sqlite] GROUP BY and ICU collation

On 7 Feb 2019, at 12:12pm, li...@herger.net wrote: > TBH: I didn't even know about this. I thought the COLLATE at the end of the > statement would do it for all. If you don't want to have to keep specifying the COLLATE, put it in the table definition: CREATE TABLE ... ( ... artist TEXT

Re: [sqlite] Multiple SELECTs in one call

On 7 Feb 2019, at 4:21am, Jose Isaias Cabrera wrote: > want to use the result of (SELECT a from t where e != 1); to run another > select (SELECT a from t where d > 3); and then, one more select (SELECT a > from t where c != 1 AND b != 1); How are these related to each other ? Do you want

Re: [sqlite] Min/Max and skip-scan optimizations

On 5 Feb 2019, at 10:12pm, Gerlando Falauto wrote: > I actually started off with source1,source2,ts as the primary key and for > some reason (which I no longer remember) I thought it would be wise to use a > ROWID and add an index instead. That is probably the right solution. There are

Re: [sqlite] My TimeStamp field keeps populating though I don't write anything to it

On 5 Feb 2019, at 4:58pm, Shawn Wagner wrote: > Reread the rules at the start of 3.1. TIME doesn't have any of the > substrings used to indicate a particular other affinity, so it's treated as > NUMERIC. You're right. I somehow remembered the default as TEXT, not NUMERIC. I was wrong.

Re: [sqlite] My TimeStamp field keeps populating though I don't write anything to it

On 5 Feb 2019, at 4:45pm, Scott wrote: > I think I understand now and I'm using SQLite Studio. It appears SQLite > Studio is acting as a enhanced wrapper around SQLite3. That makes a lot more sense. Well done for figuring it out. You have lots of options on how to store your dates. You can

Re: [sqlite] My TimeStamp field keeps populating though I don't write anything to it

On 5 Feb 2019, at 4:11pm, David Raymond wrote: > "Time(8)" ends up as numeric affinity for that field. The table in 3.1.1 shows how SQLite recognises the type you supply and turns it into a type it can use. The lowest row of the table shows that "DATETIME" is recognised as NUMERIC.

Re: [sqlite] My TimeStamp field keeps populating though I don't write anything to it

On 5 Feb 2019, at 3:29pm, Scott wrote: > Thanks for the timely response. I provided below the details. "Time(8)" is > the generated description of the structure defined by SQLite3 based upon Data > Type options. So I apologize if I don't understand you comment. SQLite didn't generate

Re: [sqlite] Min/Max and skip-scan optimizations

On 5 Feb 2019, at 8:59am, Rowan Worth wrote: > SELECT source1, source2, ts, value > FROM rolling > WHERE source1 = 'aaa' > AND ts > 1 AND ts < 1 > ORDER BY source1, source2, ts; > > And this index: > > CREATE INDEX `sources` ON `rolling` ( >`source1`, >`source2`, >`ts` >

Re: [sqlite] Min/Max and skip-scan optimizations

On 5 Feb 2019, at 8:00am, Gerlando Falauto wrote: > Thank you for your explanations guys. All this makes perfect sense. > I still can't find a solution to my problem though -- write a query that is > guaranteed to return sorted results, in some optimal way. Please state your table definition,

Re: [sqlite] Min/Max and skip-scan optimizations

On 4 Feb 2019, at 9:14pm, James K. Lowden wrote: > As Keith said, SQLite allows ORDER BY in subqueries. The SQL standard does > not. True. But SQLite does not guarantee that the outer query will preserve the inner query's ORDER BY, even if the outer query doesn't have its own ORDER BY.

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

On 4 Feb 2019, at 3:15pm, Urs Wagner wrote: > SQLite error (5): database is locked occurs? Can't answer your question, but … If you're getting unexpected locks, have you set a timeout on every connection to that database ? That gets rid of most locks. Simon.

Re: [sqlite] Min/Max and skip-scan optimizations

On 4 Feb 2019, at 1:55pm, Gerlando Falauto wrote: > Or (most likely) my understanding of how data is retrieved is plain wrong... Or your understanding how the current version of SQLite is correct, but a later version of SQLite will have different optimizations and do things differently. So

Re: [sqlite] Min/Max and skip-scan optimizations

On 3 Feb 2019, at 9:52am, Gerlando Falauto wrote: > I do want them sorted, and I also want the whole (huge) dataset to be > processable without having to store it all in memory or temp files. > Sounds like the whole purpose of an index, doesn't it? > I do know SQL is all about the result, not

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