[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

Olivier Mascia wrote: > even if using two distinct sqlite3_stmt* handles (linked to a same > sqlite3* handle), two competing threads would get unusable answers > from both these API. Yes. Please note that transactions work on the connection level. > should the design of competing threads

[sqlite] ABOUT ROWID

Hick Gunter wrote: > 2) run each query in a new process (so each one will need to read the data > from disk) This does not help against the file cache of the OS. Regards, Clemens -- > This communication (including any attachments) is intended for the use > of the intended recipient(s) only

[sqlite] ABOUT ROWID

??? wrote: > For example, tableA contains two columns: implicit rowid, A_id. > we create index on A_id. > firstly, we used rowid to select the row, cost about 400 seconds; > secondly, we used A_id to select the row, cost about 200 seconds; > thirdly, we also used rowid to

[sqlite] INSERT OR IGNORE consuming too many rowids

David Baird wrote: > # INSERT INTO "StringIntern" VALUES(5,'c'); <-- undesired behavior > # INSERT INTO "StringIntern" VALUES(3,'c'); <-- desired behavior Works for me. Are you sure you actually executed this actual code? guarantees that a newly inserted row

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

Kirill M?ller wrote: > I see no reason why the following two queries can't be executed with the same > plans: > > ... t1 LEFT JOIN t2 ... > ... t1 LEFT JOIN (SELECT * FROM t2) ... In this case, the queries are identical. But SQLite's query optimizer does not try to optimize this because such

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

Kirill M?ller wrote: > On 25.11.2015 16:32, Clemens Ladisch wrote: >> Kirill M?ller wrote: >>> For a left join with a subquery on the right-hand side, that subquery >>> doesn't seem to be flattened. >> >> This is rule 3 of <http://www.sqlite.org/

[sqlite] Query flattening for left joins involving subqueries on the right-hand side

Kirill M?ller wrote: > For a left join with a subquery on the right-hand side, that subquery > doesn't seem to be flattened. This is rule 3 of . Regards, Clemens

[sqlite] Select values from a time series spaced at least a mininum distance apart

E.Pasma wrote: > An aggregate function can still be used in a sub-query for a column value. > For the example with integers: > > WITH RECURSIVE > breaks(t) AS ( >SELECT 1 >UNION >SELECT (SELECT min(x) FROM test WHERE x > t + 2 AND x < 10) >FROM breaks > ) > SELECT t FROM

[sqlite] INSERT DEFAULT literal-value

chromedout64 at yahoo.com wrote: > Is it possible to implement a DEFAULT literal-value in SQLite, which > would allow an individual column to be populated with a DEFAULT, similar > to how a NULL or CURRENT_TIMESTAMP is currently used? What is this needed for, when it is already possible to omit

[sqlite] Recursive WITH attempting UPDATE wishing to recalculate SET clause

Brian Burleigh wrote: > One response to my post on Stack Overflow, but no solution Thank you very much for keeping the link a secret. > Studied documentation without being able to de-optimize SET from single > Scalar evaluation. Are you saying "de-optimize" because your query is too fast? If

[sqlite] Easiest way to pass SQL query parameters in command line?

Yuri wrote: > I am looking for a way to have a parametrized query, when parameter is passed > through the command line. Isn't the query itself passed through the command line? Show some example. Regards, Clemens

[sqlite] Backing up SQLite DB with unbuffered I/O

Eric Grange wrote: > Is there a way to perform an online backup with unbuffered I/O? A tool like ionice changes the I/O priority, which is something different. You would have to modify SQLite, or write your own VFS. Regards, Clemens

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

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

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

Marco Turco wrote: > could you please give me the full Bcc32 string you are using ? That would not be useful for you; I'm using an incompatible calling convention. Just replace "-tW" with "-tWM". Regards, Clemens

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

Marco Turco wrote: > I'm trying to generate the sqlite3 library but there is no way with > Embercadero C++ 7.00. > > I always receive some warnings and the first two related to the > _endthreadex' and '_beginthreadex' cannot permit to link me the library. > > k:\BCC70\Bin\Bcc32.Exe

[sqlite] printf with thousands separator

Bruce Hohl wrote: > Is printf with thousands separator working for anyone? No. SQLite's printf is designed for database-internal processing (e.g., sorting), and does not support locales. Formatting values for display is the responsibility of the application. (The sqlite3 command-line shell has

[sqlite] SQLite list user phishing Alexa

Stephan Beal wrote: > It didn't appear to come directly from the list - i suspect someone is > scraping the ML archives. As far as I can see, the archives do not show the senders' actual email addresses. I think it is more likely that someone is scraping somebody's mailbox. This would be

[sqlite] Using CTE with INSERT

Don V Nielsen wrote: > I'm struggling to implement an INSERT statement that get's is values from a > CTE. > > insert into vo_crrt_pieces (recid) > values ( > with > pkg_controls AS ( > SELECT * FROM d_pkg WHERE pkg_level = 'CRD' > ) > -- select pieces that meeting pkg and pkg_level

[sqlite] Ideas on how to use backup API for remote, incremental backup?

David Barrett wrote: > sqlite has a cool "online backup" API: https://www.sqlite.org/backup.html > However, it only backs up to a local disk. It backs up to any disk that you can access. Do you have a network file system? > how to use this API to do an incremental backup This API is not

[sqlite] bug in R-tree table syntax checking

Hi, creating an R-tree table with what looks like a table constraint results in an inconsistent number of columns, with funny results: > create virtual table t using rtree(id, x1, x2, y1, check(1)); > insert into t default values; > select * from t; 1|0.0|0.0|1.74906711200709e-38 Regards,

[sqlite] incorrect R-tree documentation

Hi, the R-tree documentation says: | Attempts to insert something other than an integer into the first | column, or something other than a numeric value into the other | columns, will result in an error. This is not actually true: > create virtual table t using rtree(id, x1, x2); > insert into

[sqlite] Performance issue with CTE

Philippe Riand wrote: > I have a table with 500,000+ records. The table has a date column, > that I?m using to sort my queries (the columns has an index). Simple > queries on the table work very well, using ORDER BY, LIMIT & OFFSET. > I?m actually extracting ?pages? of rows that I?m displaying in

[sqlite] Query containing correlated subquery gets "stuck"

gunnar wrote: > select distinct(server_order_id), count(*) from ... group by server_order_id > ... Please note that DISTINCT does not work this way; it always applies to all expressions in the SELECT clause, and "(server_order_id)" is just the same as "server_order_id". The query actually works

[sqlite] Query containing correlated subquery gets "stuck"

gunnar wrote: > Perhaps someone knows a way how I can speed up the original query. Did you try a covering index (add cb_seq_num to ordercallback_index3)? Regards, Clemens

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

Rowan Worth wrote: > if you want to know the latest prices, is this legal? > > SELECT currency, price FROM Prices GROUP BY currency HAVING time = MAX(time); It is not legal ANSI SQL, and most other databases will complain. While the WHERE clause allows to filter out rows from the table, the

[sqlite] Query containing correlated subquery gets "stuck"

gunnar wrote: > What do you exactly mean with "But in any case, as others have already > said, it is not possible for a write transaction to lock out a read > transaction _in the middle_."? I do see that records are being inserted > while I made those stack traces. The inserted records are

[sqlite] Query containing correlated subquery gets "stuck"

gunnar wrote: > (select uuid from session where date = (select max(date) from session)) This can be optimized to (select uuid from session order by date desc limit 1) but the speed of this subquery does not matter. > (SELECT max(cb_seq_num) FROM ordercallback WHERE >

[sqlite] Query containing correlated subquery gets "stuck"

gunnar wrote: > the sqlite client is stuck with the following stack traces According to these stack traces, the client is not stuck but is busy searching and reading data from the database. What is the EXPLAIN QUERY PLAN output for this query? > Disk is not busy And the CPU? Regards, Clemens

[sqlite] Feature Suggestions

Allen wrote: > 1. It would be nice to have read-only transactions for use with WAL > databases. When a read-only transaction was started, it would take a > "lock" on the WAL and then not advance further into the WAL until the > transaction ended. Thus, all select statements issued while the read

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

gwenn wrote: > If there is no way to know that the statement has been recompiled, I > guess that the column count should not be cached... SQLite already caches it for you (and properly changes it when recompiling): SQLITE_API int SQLITE_STDCALL sqlite3_column_count(sqlite3_stmt *pStmt){ Vdbe

[sqlite] Parser Grammar for MAX,MIN,SUM,COUNT,AVG functions

Prakash Premkumar wrote: > Can you please tell me which grammar rule in parse.y file parses aggregate > function ? As you already were told, there are rules that parse _all_ functions: expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP(E). expr(A) ::= id(X) LP STAR RP(E). Due to the sharing of

[sqlite] Online restore?

Moritz Angermann wrote: > with the online backup capabilities, I was wondering if anyone > hand ome insight into performing an online restore, e.g. restore > data from a backup into an active instance. says: | The online backup API allows the contents of one

[sqlite] sqlite3 file as database

Tim Streater wrote: > I don't use any extension at all for SQLite databases. With SQLite's habit of appending "-journal" (or "-wal"/"-shm") to the end of the file name, the extension would look weird. For this reason, I tend to use names like "some-data". (I also prefer to use

[sqlite] What is the best page cache size when the database is larger than system RAM?

David Barrett wrote: > If I have a database that is larger than the system's physical RAM, > am I correct in thinking I should actually set a very *small* page cache so > as to avoid "double caching" the same pages in both sqlite and the file > cache? The default setting (2048 pages) already is

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

James K. Lowden wrote: > select a, r from ( > SELECT a, random() as r FROM a > ) as R > WHERE r <> r; > > will yield zero rows, every time. $ sqlite3 SQLite version 3.8.12 2015-09-12 19:50:58 ... sqlite> create table a(a); sqlite> insert into a values

[sqlite] Feedback request: JSON support in SQLite

Domingo Alvarez Duarte wrote: > it seems that the json* functions ignore the collation completely, > is that by design or is it a forgotten implementation ? The JSON functions use JSON rules. RFC 7159 section 8.3 does not mention collations. Regards, Clemens

[sqlite] Escape pathname to URL suitable for ATTACH

Zsb?n Ambrus wrote: > The documentation explains how to quote a filename to be suitable as > an URI path. Is there a C function in the public API that I can call > to do this quoting for me automatically? No. > It's also not clear to me how to quote filenames on unix if they > contain non-ascii

[sqlite] explain query plan showing SEARCH instead of SCAN for min/max operations

Isaac Goldberg wrote: > I'm seeing that EXPLAIN QUERY PLAN returns SEARCH instead of SCAN when > using min() or max(). For example: > > sqlite> create table t(a int, b int); > sqlite> explain query plan select min(b) from t; > 0|0|0|SEARCH TABLE t > > Why is this a SEARCH instead of a SCAN?

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

Dominique Devienne wrote: > One annoyance with the authorizer approach is that you cannot "stack them" Write your own authorizer that implements its own callback list. But the only sure way to prevent anyone else from installing its own authorizer is to change the function name in the SQLite

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

Yahoo! Mail wrote: > sqlite> drop table if exists t1; create table t1(a datetime); begin; with > recursive c(x) as (values(1) union all select x + 1 from c where x < > 10) insert into t1(a) select datetime('now') from c; commit; > > It would take ages to finish and that is logical; it's

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

Jean-Christophe Deschamps wrote: > At 21:11 27/08/2015, you wrote: >> I think it still comes back to my earlier comment: Would changing it to >> behave more like the most common / expected outcome above be a breaking >> change? > > Looks like "How many zillion devices are going to misbehave if

[sqlite] Insertion into the large DB drastically slows down at some point

Yuri wrote: > I followed every advise I could find: > * Database is written into memory > * Whole insertion happens in one transaction in one giant BEGIN TRANSACTON; > ... END TRANSACTION; block. > * Foreign keys are deferred: PRAGMA defer_foreign_keys=ON; > * Journal is disabled: PRAGMA

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

Dominique Devienne wrote: > how can we programatically reliably discover which (v)tables a view > accesses, staying in documented behavior land? With an authorizer callback: http://www.sqlite.org/c3ref/set_authorizer.html Regards, Clemens

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

Domingo Alvarez Duarte wrote: > This assumption is a bit naive : > >> In SQLite, this cannot happen because queries execute infinitely fast >> (as far as the built-in date/time functions are concerned). Nonetheless it's true. says: | the current time

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

Graham Holden wrote: > And while "SELECT random() AS rr ORDER BY rr" is slightly contrived, the > example from J Decker: > > select ItemName,SoldDate, date('now','-1 month') as z from > SoldItemDetails order by > SoldDate > if it were to show the same behaviour (I haven't tested it) might break

[sqlite] WITH () AS (SELECT ) help

jose isaias cabrera wrote: > WITH EmailData (name,contact,dstamp) AS > ( > SELECT > 'last, first', > 'first.last at xerox.com', > '2015-08-25 11:11:11' > ) > UPDATE LSOpenProjects SET XtraB = EmailData.dstamp, pmuk = EmailData.contact > WHERE pmuk = EmailData.name; > > But, it's not working.

[sqlite] : it seems to be only orber by and group

afriendandmore wrote: > The table shall be ordered according to A. (Order by A) > But if C ist true and to the value B1 in this Record, there exists another > Record with B2 and B2 = B1, then B1 should be ordered in the row above B2. Do those two records have the same A values? Regards,

[sqlite] Getting row number in a sorted list.

Simon Slavin wrote: > SELECT rowid FROM NameTable > WHERE name BETWEEN 'P' AND 'P' > > This will execute faster if you have an index on 'name' in NameTable. > > [Yes I know 'P' is lazy. Until you find someone with that name > (presumably Polish) with that name bite me.] If the

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

Richard Brinkman wrote: > When a perform the following query: > select random() as x from some_non_empty_table order by x desc limit 20; > I get something like: > -4348240540797173967 > -8823092517172356709 > 4237024158005380173 > 897958093325532613 > -6349939216731113298 > ... > which clearly is

[sqlite] Strange behaviour of sqlite3_stmt_busy

Stephan Beal wrote: > On Thu, Jul 30, 2015 at 11:35 PM, Clemens Ladisch > wrote: >> gwenn wrote: >>> sqlite3_stmt_busy returns true after sqlite3_step returns DONE. >> >> The documentation says: >> | The sqlite3_stmt_busy(S) interface returns true (non-zer

[sqlite] Strange behaviour of sqlite3_stmt_busy

gwenn wrote: > sqlite3_stmt_busy returns true after sqlite3_step returns DONE. The documentation says: | The sqlite3_stmt_busy(S) interface returns true (non-zero) if the | prepared statement S has been stepped at least once using | sqlite3_step(S) but has not run to completion and/or has not

[sqlite] Query tooooo sloooooooooow

James Qian Wang wrote: > 0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2 > 0|1|1|SEARCH TABLE history AS h USING COVERING INDEX elid (elid=?) There is no more efficient way to execute this query. > both very slow What file system? What disks? Any network? Regards, Clemens

[sqlite] Query tooooo sloooooooooow

James Qian Wang wrote: > select count(*) from contact c left join history h on (h.elid=c.elid); Please show the output of EXPLAIN QUERY PLAN for this query. If it does not look like this: 0|0|0|SCAN TABLE contact AS c USING COVERING INDEX elid2 0|1|1|SEARCH TABLE history AS h USING COVERING

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

Simon Slavin wrote: > both platforms use whatever the expected variable name was for that OS. Unix: 1. #pragma temp_store_directory 2. getenv("SQLITE_TMPDIR") 3. getenv("TMPDIR") 4. /var/tmp 5. /usr/tmp 6. /tmp Cygwin: 1. #pragma temp_store_directory 2. getenv("SQLITE_TMPDIR") 3.

[sqlite] Sqlite open with SQLITE_OPEN_READWRITE mode, but sometimes write failed with SQLITE_READONLY

?? wrote: > sometimes step(3) failed in some mobile phone, the return code is > "SQLITE_READONLY" Does sqlite3_extended_errcode() return a more specific error code? Regards, Clemens

[sqlite] journal file and its size

Mayank Kumar (mayankum) wrote: > Sometimes we see the journal file not present on the system although > active transactions are ongoing. How is that possible since the > documentation says the journal file is always created in exclusive > mode ? The journal file is created only when it is needed.

[sqlite] How to correctly a add string to a Mem?

Sairam Gaddam wrote: > I want to make some changes to the result set The SQLite API does not allow making changes to the data in a sqlite3_stmt. > and I need to add an extra column. This can be done in SQL: SELECT Column1, ColumnA, 'some value' FROM ...; Regards, Clemens

[sqlite] How to correctly a add string to a Mem?

Sairam Gaddam wrote: > I have tried a method to create a Mem and add a string to it, which is as > below > > sqlite3VdbeMemSetStr(&(p->custom_aMem[0]), zColumn , > strlen(zColumn)*sizeof(char), SQLITE_UTF8, SQLITE_STATIC); This is an internal function that applications are not supposed to know

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

Simon Slavin wrote: > On 3 Jul 2015, at 1:39pm, Rob Willett wrote: >> is Vacuum the same as doing the .dump and restore or is it different? > > It's the same thing (more or less). The implementation is completely different. From the outside, the main difference is that VACUUM works inside

[sqlite] error during bind

Kumar Suraj wrote: > BEGIN TRANSACTION; INSERT INTO TBL (dn) VALUES (?); SELECT > last_insert_rowid(); COMMIT; Please note that the value returned by the SQL function "last_insert_rowid()" is also available with the C API function "sqlite3_last_insert_rowid(db)". When you have SQL commands

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

Rob Willett wrote: >> Do you ever depend on any indexing on the "Text" column which is not COLLATE >> NOCASE ASC ? If not, then you should be able to speed up your search by >> defining the column the way you think of it. So in your table definition use >> >> "Text" TEXT COLLATE NOCASE

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

GB wrote: > For curiosity you may try to force it to use "Rag_Idx1" by executing > > "select Id,Calculation,Peak,Red,Amber,Green from RAG INDEXED BY Rag_Idx1 > where text = ?" > > and see if it makes any difference. "Error: no query solution" Rag_Idx1 index cannot be used because the collations

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

Simon Slavin wrote: > On 2 Jul 2015, at 11:16pm, Rob Willett > wrote: >> ?select Id,Calculation,Peak,Red,Amber,Green from RAG where text = ?? >> >> CREATE TABLE "RAG" ( >> ... >> "Text" TEXT, >> ... >> ); >> >> It has four indexes on it >> >> CREATE UNIQUE INDEX "RAG_Idx1" ON

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

Ward Willats wrote: > It may or may not apply to your situation, but after doing lots of inserts, > running ANALYZE can sometimes work wonders. As a rule of thumb, ANALYZE can help only if there are multiple ways of using indexes, and if SQLite's heuristics happen to choose the wrong one. (Also

[sqlite] Will SQLite break a join query?

Sairam Gaddam wrote: > I know that SQLite will perform some internal select statements when > executing a join query but will it break a join query and execute in parts > ??? SQLite executes all joins in parts, i.e., it is not possible to do more than one lookup of a joined field. (SQLite

[sqlite] What's the best way to pass function information to virtual table?

Jerry wrote: > Assuming I want to compute the average value of all keys, how to write the > query for this using match? > > For example, if I write SQL in this way > >> SELECT avg(key) From table WHERE key MATCH('avg'); > > with MATCH info, the virtual table is able to know the query is looking

[sqlite] What's the best way to pass function information to virtual table?

Jerry wrote: > 2015-06-17 1:23 GMT-07:00 Clemens Ladisch : >> Jerry wrote: >>> But it seems only general functions can be override -- it has not effect on >>> aggregate functions. >> >> The virtual table interface does not allow access to all the internal

[sqlite] What's the best way to pass function information to virtual table?

Jerry wrote: > With xBestIndex and xFilter, we can pass the constraint information (e.g., > those from WHERE clause) to virtual table (through struct > sqlite3_index_info), so that we can locate the cursor to narrow the search > space. > However, it does not provide information about functions

[sqlite] implicit vs explicit joins

Paul Sanderson wrote: > I have just been asked whether implicit and explicit join are the same > > select text, handle.id from message,handle where handle_id = handle.ROWID; This is an implicit *inner* join. > SELECT message."text", handle.id FROM message LEFT JOIN handle ON >

[sqlite] Transactions/locks in WAL mode

Olivier Vidal wrote: > In terms of pure performance, is that there is a difference between: > > - SELECT > - SELECT > (so two read transactions) > > AND > > - BEGIN > - SELECT > - SELECT > - END The documentation talks about writing as the primary reason for locks, but read-only transactions need

[sqlite] Transactions/locks in WAL mode

Olivier Vidal wrote: > UPDATE > Internally, is the same thing that: BEGIN DEFERRED - UPDATE -COMMIT? Yes. > SELECT > UPDATE > In this example, the set of these commands is serialized? Assuming that you finalize the SELECT before executing the UPDATE, this is the same as BEGIN SELECT COMMIT

[sqlite] NOT EXISTS and LEFT JOIN Performance problem

ShadowMarta at yahoo.de wrote: > ok, understand but I can't see why "NOT IN" would not force a full table scan > but "LEFT JOIN" would ? In a query like "SELECT ... WHERE ID NOT IN (SELECT ...)", the subquery is executed _once_, the results are put into a temporary table/index, and the

[sqlite] NOT EXISTS and LEFT JOIN Performance problem

ShadowMarta at yahoo.de wrote: > Do you have some explanation of the terrible performace of "NOT EXIST" & > "LEFT JOIN" versus "NOT IN" as well ? FTS tables can do two types of queries efficiently: - lookups by rowid/docid; - searches with MATCH. Anything else (such as your "WHERE id = ?") ends

[sqlite] NOT EXISTS and LEFT JOIN Performance problem

ShadowMarta at yahoo.de wrote: >> You should drop the ID column, and in your queries use the docid instead. > > Not possible. > I fill the rows in a parallel loop, the IDs are in disorder and they are the > link to table ?ART.ID?. INSERT INTO OCR(docid, FullOCR) VALUES (42, 'xxx'); Regards,

[sqlite] NOT EXISTS and LEFT JOIN Performance problem

ShadowMarta at yahoo.de wrote: > CREATE VIRTUAL TABLE `OCR` using fts4 ( > `ID`integer primary key NOT NULL, This is not how FTS tables work. SQLite ignores pretty much anything except the column names; it does not matter whether you write PRIMARY KEY or NO KEY PLEASE. All

[sqlite] SQLite not using primary key index anymore

Eric Grange wrote: > I recently added a field and index to an existing table, and now SQLite > seems to be using that index in place of the primary key, even on simple > queries > > CREATE TABLE vin ( >id INTEGER PRIMARY KEY AUTOINCREMENT, >tx_id INTEGER NOT NULL, >from_vout_id

[sqlite] Multiple connections and page cache(s) reusability

Milan K??? wrote: > My understanding was that each connection has its own page cache. This is correct. > [...] Does SQLite have yet some other global cache? No. That other global cache is part of the OS. It might be possible that opening a file two times changes the OS's caching algorithm.

[sqlite] sqlite3 and Unicode

Staffan Tylen wrote: > I must admit that I'm a bit confused here. If I'm not wrong UTF-8 differs > from ascii when the value is higher than '7f'x, but storing data in sqlite > as text with character values beteen 'x80'x and 'ff'x seems to be no > problem. I previously thought that this could only

[sqlite] sqlite3 and Unicode

Luuk wrote: > on Windows 7: > C:\temp>sqlite3.exe encoding.sqlite > sqlite> select * from test; > ??n The Windows console does not support UTF-8 with the default settings and the C stdio functions. Any data you entered in the console is not encoded correctly. Regards, Clemens

[sqlite] What software is deployed more than SQLite?

Yahoo! Mail wrote: > How about curl? This is taken directly from the official website: > > " curl is used in command lines or scripts to transfer data. It is also > used in cars, television sets, routers, printers, audio equipment, mobile > phones, tablets, settop boxes, media players and is the

[sqlite] System.Data.SQLite, Need alternate way to get field length

William Drago wrote: > I am trying to determine the number of bytes in a blob. According to the help > file under SQLiteDataReader.GetBytes Method in the Remarks section: > > "To determine the number of bytes in the column, pass a null value for the > buffer. The total length will be returned."

[sqlite] dropping a constraint

Scott Doctor wrote: > On 5/1/2015 12:10 AM, Clemens Ladisch wrote: >> Scott Doctor wrote: >>> I noticed that the sqlite documentation does not show the CONSTRAINT keyword >> >> http://www.sqlite.org/syntax/column-constraint.html >> http://www.sqlite.org/syntax/

[sqlite] dropping a constraint

Scott Doctor wrote: > I noticed that the sqlite documentation does not show the CONSTRAINT keyword http://www.sqlite.org/syntax/column-constraint.html http://www.sqlite.org/syntax/table-constraint.html > Also it appears that sqlite does not support DROP CONSTRAINT Indeed:

[sqlite] Hot journal file won't go away

Drago, William @ CSG - NARDA-MITEQ wrote: > What is the recommended procedure for clearing a hot journal file? Just opening the database should be enough. > I used to do a "SELECT..." but since I upgraded from System.Data.SQLite > 1.0.93.0 to 1.0.96.0 that doesn't work anymore. If the rollback

[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints

Nicolas Boullis wrote: > I have a program that does mostly? SELECT requests, and it is very > slow. But I then figured out that, if I rebuild my SQLite database > without PRIMARY KEY/UNIQUE constraints, the program runs much faster > (no measurement yet, but I?d say at least 10? faster). SQLite

[sqlite] Problems with pragma journal_mode

Janke, Julian wrote: > PRAGMA journal_mode=WAL; > > Unfortunately, after these lines, the journal mode is not changed. > > -DSQLITE_OS_OTHER=1 Do you have mmap support? says: | WAL normally requires that the VFS support shared-memory primitives. | The built-in

[sqlite] Select query becomes distinct on where matches unique with null value

Mike Gladysch wrote: > Select col1, col2, col3, col4, col5, col6 > From table > Where col3 is null and col4 ='test' That is not valid SQL. > 3.8.4.3: 3 rows (expected, ok) > 3.8.7.2: 1 row (wrong) How to reproduce: create table t(x, y, unique(x, y)); insert into t values(null, 1); insert

[sqlite] Regarding SeekGe and Next opcodes in VDBE

Sairam Gaddam wrote: > On Thu, Apr 9, 2015 at 1:04 PM, Clemens Ladisch wrote: >> Sairam Gaddam wrote: >>> sql="create table em(name text primary key,age text,pts text);"\ >>> "create table l(name text primary key,fame text);"; >&g

[sqlite] Regarding SeekGe and Next opcodes in VDBE

Sairam Gaddam wrote: > sql="create table em(name text primary key,age text,pts text);"\ > "create table l(name text primary key,fame text);"; > > sql = "select * from em,l where l.fame=em.age"; > >4 Once 0 130 00 >5 OpenAutoindex230

[sqlite] windows network and wal mode

Zaumseil Ren? wrote: > I have a separate table for each parameter with time stamp and value. > The time stamp is used as "integer primary key asc". > [...] > It is also possible to go back in time and then start from there. > Currently I remove all values from the tables after the specified time.

[sqlite] fix rowid's of fts table

Rael Bauer wrote: > Now I have restructured the normal table (i.e. delete column) using the > algorithm mentioned here a couple of times (insert into normal select > (columns) from normal_OLD, etc..) > > This "resets" the rowid's of the normal table. If you rely on the rowid values, you should

[sqlite] fts4 support in distributions of SQLite?

Darren Spruell wrote: > My few test systems show that support for fts4 is present, but > I'm trying to learn if it's a reasonable assumption that the usual > SQLite build/deployment "out there" includes support for the feature. The recommended way to use SQLite is to compile sqlite3.c directly

[sqlite] Multi-table index ersatz?

Eric Grange wrote: > select ...some fields of A & B... > from A join B on A.A2 = B.B2 > where A.A1 = ?1 > order by B.B1 > limit 100 > > Without the limit, there can be tens of thousandths resulting rows, Even with the limit, all the tens of thousands rows must be sorted. > without the A1

[sqlite] Characters corrupt after importing a CSV file

Richard Hipp wrote: > On 2/26/15, Adam Podstawczy?ski wrote: >> Also, to provide more input, I have now noticed that even if the column >> width is wider than the offending string, this issue still creates problems >> ? while nothing gets truncated, the position of the next column is >>

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

gunnar wrote: > The subquery will always return one result or no result. So I only > have to UNION it ALL with the SELECT NULL part. >>> Clemens Ladisch wrote: >>>> ... WHERE cb_seq_num > ( >>>>SELECT cb_seq_num >>>>

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

Eduardo Morras wrote: > Clemens Ladisch wrote: >> ... WHERE cb_seq_num > ( >> SELECT cb_seq_num >> FROM ordercallback >> WHERE cb_uuid=@CBUUID >> UNION ALL >> SELECT NULL -- at least one result >> LIMIT 1)

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

gunnar wrote: > When I execute a query that has a subquery behind a comparison > operator in its where-clause and the subquery returns an empty result, > then the result of the complete query is exactly how I want it: also > an empty result. Can I assume that this behaviour will stay the same > in

Re: [sqlite] BLOB type not showing in table description after using create table as

Tiemogo, Idrissa wrote: > When I derive a table from another table containing blob type. > The describing the new table doesn’t show “blob". > > sqlite> create table t1 (p_id int, geometry blob); > sqlite> pragma table_info(t1); > 0|p_id|int|0||0 > 1|geometry|blob|0||0 > sqlite> create table t2 as

Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)

Janke, Julian wrote: > In my opinion, this means, we must ""only"" write a VFS implementation for > our target platform. What file API is there? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] ordinary CTE containing sum()

James K. Lowden wrote: > 1. Last I checked, SELECT in a column position in the SELECT clause as > in > > select foo (select ...) > > is not permitted by the SQL standard. This example indeed is not valid SQL syntax. However, SELECT in a column position is allowed: select (select 42);

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