[sqlite] Multi-table index ersatz?

Let's construct this example from the reverse. The object is to avoid the sort at the end (sorting "millions" to return 100 is a bad tradeoff), so the B table needs to be visited in B1 order. -> outer loop = B -> inner loop = A -> index B on (B1,...) The join is on A2 = B2 ->index A on

[sqlite] full table scan ignores PK sort order?

The subquery is the index access (partial table scan), which is performed once for each and every value in your IN list for the column a (in effect, the IN list is transformed into an ephemeral table and joined to your test table). Since you did not declare an index for your primary key

[sqlite] Characters corrupt after importing a CSV file

We do that here. Works well most of the time, but fails miserably if the first 10 columns are not representative of the data. Also requires each and every query to be run twice. Not good for queries that require sorting of the result set (i.e. the ORDER BY clause is not fulfilled automatically

[sqlite] Characters corrupt after importing a CSV file

Maybe you are falling into the character/byte trap. The SQL function length() returns the number of CHARACTERS in a string, which - for UTF encoded strings containing non-latin characters - is smaller than the number of BYTES required to represent them. Typically you will be losing bytes at

[sqlite] Regarding creating a mem object and copying contents to it in SQLite

] Regarding creating a mem object and copying contents to it in SQLite I am trying to optimize certain operations of SQLite internally, so i created mem object. On Wed, Feb 25, 2015 at 12:36 PM, Hick Gunter wrote: > The mem object is internal to sqlite, it is not intended to be > created/c

[sqlite] Regarding creating a mem object and copying contents to it in SQLite

The mem object is internal to sqlite, it is not intended to be created/changed by user code. What are you trying to do that makes you think you need to manipulate internal structures? -Urspr?ngliche Nachricht- Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com] Gesendet: Mittwoch,

[sqlite] Using incremental BLOB functions against a BLOB column in a virtual table

ions against a BLOB column in a virtual table Thanks to Richard Hipp and Hick Gunter for their replies on this topic. Given that support for support for BLOBs in virtual tables differs from that for BLOBs in physical tables, is there any method or function available to the sqlite3_x() caller that

[sqlite] Using incremental BLOB functions against a BLOB column in a virtual table

Incremental BLOB I/O makes certain assertions that cannot be guaranteed (much less verified at runtime) for virtual tables (e.g. 1: unique rowids that 2: can be used for access) and needs to know how to read/write them (which is under the control of the virtual table author).

[sqlite] Appropriate Uses For SQLite

We are using SQLite as the catch-all data access method (via custom extensions) for - Oracle tables and views - Faircom CTree files - Shared memory record stores ("Data Dictionary") - Log file access - Blob to record translation (TLV structures) - Partitioned data stores (CTree and Data

[sqlite] Can this be sorted?

You are attempting to compute 0/0 which is NULL and happens to be smaller than 0/47 which is 0. -Urspr?ngliche Nachricht- Von: Bart Smissaert [mailto:bart.smissaert at gmail.com] Gesendet: Montag, 16. Februar 2015 09:49 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Can

[sqlite] Sqlite Virtual Table Example with non-trivial xBestIndex (was: Porting SQLite to another operating system (not supported out of the box))

Having personally written about a dozen virtual table implementations I can confirm that those implementations needing a nontrivial xBestIndex function are all based on building an SQLite interface on substantial proprietary storage subsystems like an in-memory ISAM table (with configurable

Re: [sqlite] Multi-thread mode question

In serialized mode, SQLite will acquire the mutex when it detects you are "starting to use" the database handle (somewhere between entering sqlite3_prepare and the first sqlite3_step) and then HANG ON TO IT, NOT LETTING GO until the calling thread is "finished" (like when sqlite3_step returns

Re: [sqlite] regarding looping in vdbe for sqlite table joins!

SCAN in the query plan = Rewind...Next LOOP in opcodes SEARCH in the query plan = Column...Seek in opcodes SQLite has determined that creating an automatic index on the referenced tables should be faster than performing a full table scan for the general case. asql> explain query plan select *

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

[mailto:t...@clothears.org.uk] Gesendet: Montag, 26. Jänner 2015 13:00 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery On 26 Jan 2015 at 07:33, Hick Gunter <h...@scigames.at> wrote: > It is never a good ide

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

It is never a good idea to rely on automatically assigned column names. If you want reproducible, predictable, release independant column names then please assign them with the AS clause. -Ursprüngliche Nachricht- Von: Marcus Bergner [mailto:marcusberg...@gmail.com] Gesendet: Sonntag,

Re: [sqlite] database locked in PHP

Maybe you can use the (linux, c) code I posted recently to determine which process/thread is locking the database file. -Ursprüngliche Nachricht- Von: Lev [mailto:leventel...@gmail.com] Gesendet: Sonntag, 25. Jänner 2015 01:36 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] database

Re: [sqlite] System.Data.SQLite - Exception Calling SQLiteModule.DeclareTable

I have always wondered why people will insist on using human readable column names (with embedded spaces and special characters) in the implementation layer (SQL code) instead of the presentation layer (user interface). The clutter introduced into queries by having to quote the column names by

Re: [sqlite] Shell tool question

Yes. I'm using several layers of .read files to load the appropriate subset of extensions for each class of process (OLTP, user query, subsystem, specific tools,...) -Ursprüngliche Nachricht- Von: Simon Slavin [mailto:slav...@bigfraud.org] Gesendet: Mittwoch, 21. Jänner 2015 17:35 An:

Re: [sqlite] Update Statements using Sub query

You are requesting the field Column Name (with embedded whitestpace in the field name) from a query with a where clause that forces it to be the string 'Date'. Perhaps your are looking for SELECT Date -Ursprüngliche Nachricht- Von: MikeSnow [mailto:michael.sab...@gmail.com]

Re: [sqlite] Malformatted output by sqlite3

Works as specified. The .width command sets the output width of a column to a certain number of characters; output that is shorter is padded on the right (or the left, if the width is negative); output that is too long is truncated. Use the printf() function to define the format of real

Re: [sqlite] Page_size increase from 1k to 4k made my "REPLACE INTO" slower. why?

From what little you reveal I assume this is some kind of datalogging application. I also assume there will be a primary key (call ?) and also suspect that there are a number of secondary indices for data retrieval. Since you make no mention of transactions, I must infer that you are using

Re: [sqlite] Inserting/removing large number of rows with index

It depends in how you define "update the index". If you mean "write to disk" then this happens "once, at the end of the transaction" (the exact process differs depending on the journal mode). If you mean "change the index structure in memory" then (as already noted) the changes will happen

Re: [sqlite] database is locked

Use the following code snippet (add error checking, set v_file to the full path name of your SQLite db file) to check. It attempts to take the same locks as SQLite would, but prints the pid of the blocking process. It also prints the journal mode. #include #include #ifdef AIX64 #include

Re: [sqlite] help with query

Step 1: count the occurrences: SELECT data1,count() AS count FROM table GROUP BY data1; Step 2: get the rows with a count above the limit SELECT data1,count() AS count FROM table GROUP BY data1 HAVING count >= 3; Step 3: get the keys from the rows SELECT data1 FROM (SELECT data1,count() AS

Re: [sqlite] xBestIndex/XFilter and virtual tables

BTW: SQLite will also ask your virtual table about GROUP BY/ORDER BY capabilities if either of the clauses is included in the SELECT. -Ursprüngliche Nachricht- Von: Hick Gunter [mailto:h...@scigames.at] Gesendet: Montag, 12. Jänner 2015 08:02 An: 'General Discussion of SQLite Database

Re: [sqlite] xBestIndex/XFilter and virtual tables

SQLite is asking your virtual table questions: 1) what is the cost of a full table scan? (your answer: 1000) 2) what is the cost of a lookup by "id"? (your answer: 1) 3) when performing a key lookup, do you promise to return only rows matching the key? (your answer in returned in the "omit"

Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

Maybe you mean (assuming there is not more than one record in t2 for a given SSID-CELLID-SECTOR) UPDATE t1 ... -Ursprüngliche Nachricht- Von: MikeSnow [mailto:michael.sab...@gmail.com] Gesendet: Freitag, 09. Jänner 2015 00:12 An: sqlite-users@sqlite.org Betreff: [sqlite] Error while

Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

Ursprüngliche Nachricht- >Von: James K. Lowden [mailto:jklow...@schemamania.org] >Gesendet: Donnerstag, 08. Jänner 2015 03:56 >An: sqlite-users@sqlite.org >Betreff: Re: [sqlite] New column in select will not mask column of the same >name in having clause and sqlite won't warn

Re: [sqlite] New column in select will not mask column of the same name in having clause and sqlite won't warn

This is completely legal and well defined. HAVING is applied to the RESULT set of a SELECT. The select asks to count the "distinct kontrola" in each group of kvadrat and datum, the HAVING clause specifies returning only those records with pocet > 1. If there were no pocet column in table b,

Re: [sqlite] Suggestion for syntax enhancement for virtual tables

Temporary virtual tables sounds like an interesting concept. Does the xDestroy() function get called on such a beast (as opposed to xDisconnect() when the connection is closed)? Should that function delete the backing store (even if a non-temporary virtual table is still connected)?

Re: [sqlite] SQL newbie, how to implement a delete correctly.

table:child02.xxx_id = 2432... etc. the idea is without knowing all the names of all the tables, find all references to parent01.p01_id (where value is xxx)  From: Hick Gunter <h...@scigames.at> To: 'Jonathan Leslie' <j...@jonathanleslie.com>; 'General Discussion of SQLite Database' &

Re: [sqlite] SQL newbie, how to implement a delete correctly.

Select * from child01 where p01_id in (select rowid from parent01 where ...); Or Select c.* from parent01 p join child01 c on p.rowid=c.p01_id where ...; -Ursprüngliche Nachricht- Von: Jonathan Leslie [mailto:jlesli...@yahoo.com] Gesendet: Dienstag, 30. Dezember 2014 15:58 An:

Re: [sqlite] Partial index to find maximum

create the primary key index ordered properly CREATE TABLE t (..., PRIMARY KEY ( a ASC, b DESC)...); SELECT b FROM t WHERE a = ? LIMIT 1; If you insist on using a partial index for this (for example if each a has a lot of b entries) you could add a field b_is_max and keep it current using

Re: [sqlite] Row filtering prior to aggregate function execution

.@gmail.com] Gesendet: Freitag, 19. Dezember 2014 11:28 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Row filtering prior to aggregate function execution Would this work? SELECT SUM(...),COUNT(...), ... FROM ... WHERE ... GROUP BY ... HAVING security(...) ... Staffan On Fri,

Re: [sqlite] Row filtering prior to aggregate function execution

SELECT ...,sum(...),count() FROM ... WHERE security(...) ... With a user defined function security(). -Ursprüngliche Nachricht- Von: Roland Martin [mailto:rolandsmar...@gmail.com] Gesendet: Donnerstag, 18. Dezember 2014 17:09 An: sqlite-users@sqlite.org Betreff: [sqlite] Row filtering

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

I would concur in that SQLite is asking "which subset of the given constraints yields the most efficient access". The possible query plans are 1) A() -> B(ID) -> C(LINKID) 2) C() -> B(LINKID) -> A(ID) 3) B() -> A(ID) + C(LINKID) or B() -> C(LINKID) + A(ID) 4) A() -> C() -> B(ID,LINKID) or

Re: [sqlite] '.timer on' in the shell tool

Units are "CPU Seconds". "user" time is spent within user code, i.e. SQLite, "sys" time is spent within system calls, i.e. reading/writing files. The balance between the times depends on various parameters, including the state of the disc cache and the complexity of your INSERT...SELECT

Re: [sqlite] replace many rows with one

Both, I guess Insert into ... select a,b,sum(theCount) group by a,b; -Ursprüngliche Nachricht- Von: Simon Slavin [mailto:slav...@bigfraud.org] Gesendet: Mittwoch, 10. Dezember 2014 12:39 An: General Discussion of SQLite Database Betreff: [sqlite] replace many rows with one Dear folks,

Re: [sqlite] "database is locked" for SQLITE_BUSY

I think the error messages are distinct enough as is. SQLITE_BUSY means that some connection is BUSY with a write transaction and has locked the database file; presumably, it will be possible to write to the database when the current writer has finished, just not now or within the specified

Re: [sqlite] Column name as a variable

SELECT table_name FROM sqlite_master; And then, in your programming language of choice, execute SELECT count() FROM For each received table name. You cannot use a variable instead of a table name in SQL. -Ursprüngliche Nachricht- Von: Paul Sanderson

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

I would agree with the suspicion that your data is "changing shape" i.e. the cardinality of index fields is becoming very different from what ANALYZE stored. As for bypassing the query planner/code generator you might want to contact Prakash Premkumar who is apparently

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

IIRC there was a programmer working for a bank that managed to siphon off the sub-unit fractions that the interest calculating software generated (how much interest is owed for $1 at 0,25% p.a. for 2 days*) onto his own account and temporarily got rich quick. $1 * 0,25% = $25 (interest

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

Data types are 64bit integer (~18 decimal digits) and 64 Bit IEEE Float(11 bit exponent, 52 bit fraction), so no. Store the numbers as TEXT (human readable) or BLOB (e.g. 128Bit binary) and write user-defined functions to manipulate them. -Ursprüngliche Nachricht- Von: Dinesh Navsupe

Re: [sqlite] Multiple threads reading different data

This is the I (Isolation) in ACID. WAL mode allows the writer to pretend that no transactions are outstanding and begin and even commit a write transaction. This change sits in the Wal file until all prior transactions have been completed and the change can be copied to the db. As long as your

Re: [sqlite] Does using e.g. LTRIM function remove collation?

11. November 2014 00:05 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Does using e.g. LTRIM function remove collation? On Mon, 10 Nov 2014 08:43:24 + Hick Gunter <h...@scigames.at> wrote: > I get the following results for the second select: > > A > B > a (lowercase!!!) > &g

Re: [sqlite] SQLite where clause tree

Your tree is wrong. I would expect that operator precedence is handled in the parser. The code generator will happily implement any tree, regardless of how insane it may be. -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Montag, 10. November

Re: [sqlite] Does using e.g. LTRIM function remove collation?

I get the following results for the second select: A B a (lowercase!!!) Are you sure you ran the exact query stated? -Ursprüngliche Nachricht- Von: James K. Lowden [mailto:jklow...@schemamania.org] Gesendet: Samstag, 08. November 2014 01:52 An: sqlite-users@sqlite.org Betreff: Re:

Re: [sqlite] SQLite as a meta database

ive me an idea of what a minimal xBestIndex/xFilter skeleton might look like? I walked though "ext/misc/amatch.c" from the www.sqlite.org/src/artifact tree, but I'm a little lost. Thanks, Mike Beddo -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-user

Re: [sqlite] SQLite as a meta database

Hi, we have extensive experience with respect to the use of virtual tables in SQLite. In fact, the only native SQLite tables we use are in a configuration checking tool. We have "providers" from in-memory indexed tables, CTree (r) files, Oracle tables (read only), structured disk files,

Re: [sqlite] Index without backing table

Can you give an example of what such a beast would look like? CREATE TABLE not_exist (A, B, C, D, E);-- for illustrative purposes CREATE INDEX no_table ON not_exist (A,B,C,D,E); -- has to be covering index The only efficient order to return and/or select rows is by {A}, {A,B}, {A,B,C}, ...

[sqlite] Table qualification not supported in some cases (was: quasi-bug related to locking, and attached databases)

Betreff: Re: [sqlite] quasi-bug related to locking, and attached databases On Mon, Oct 27, 2014 at 11:59 AM, Stephan Beal <sgb...@googlemail.com> wrote: > On Mon, Oct 27, 2014 at 11:53 AM, Hick Gunter <h...@scigames.at> wrote: > >> TEMP tables get created in database

Re: [sqlite] quasi-bug related to locking, and attached databases....

TEMP tables get created in database temp; which is located in "a file" or "in memory" depending on the SQLITE_TEMP_STORE preprocessor symbol and the pragma temp_store. -Ursprüngliche Nachricht- Von: Stephan Beal [mailto:sgb...@googlemail.com] Gesendet: Montag, 27. Oktober 2014 11:44 An:

Re: [sqlite] quasi-bug related to locking, and attached databases....

2014 09:43 An: General Discussion of SQLite Database Betreff: Re: [sqlite] quasi-bug related to locking, and attached databases On Mon, Oct 27, 2014 at 9:23 AM, Hick Gunter <h...@scigames.at> wrote: > SQLite treats each attached database as a separate entity. Attaching > the same fil

Re: [sqlite] quasi-bug related to locking, and attached databases....

SQLite treats each attached database as a separate entity. Attaching the same file twice is just asking for problems. The query specifies that the destination db be locked for write and the source db for read; which translates to two locks that cannot coexist on one underlying db file.

Re: [sqlite] Unbinding parameters

http://www.sqlite.org/c3ref/clear_bindings.html -Ursprüngliche Nachricht- Von: Baruch Burstein [mailto:bmburst...@gmail.com] Gesendet: Donnerstag, 23. Oktober 2014 13:47 An: General Discussion of SQLite Database Betreff: [sqlite] Unbinding parameters It says here

Re: [sqlite] struct SrcList

I estimate that you have about a 1 : 2^^32 chance of assigning the correct value. I guess it is an index into a table of cursors required for processing the statement and there will be assertions to satisfy. -Ursprüngliche Nachricht- Von: Prakash Premkumar

Re: [sqlite] Division accuracy

Floating point values are represented as * 2 ^^ The egde cases are inserting in sorted order. Descending: The first row is tagged with 1.0 Each new first row is tagged with 1/2 the previous. This will either lose 1 bit of mantissa or decrement the exponent. This means you will run out of

Re: [sqlite] Crash when binding default column value

My guess would be that finalizing the create table statement makes the bound value go out of scope and thus be unavailable to the insert statement. Bound values reside somewhere in the internal prepared statement structure and do not get copied into the database file, even if they happen to be

Re: [sqlite] Results of Joins in sqlite

, Sep 24, 2014 at 3:34 PM, Hick Gunter <h...@scigames.at> wrote: > You are free to build your own result conversion routine on top of the > SQLite Api. > > > May I suggest selecting the rowids of the tables too i.e. > > SELECT t1.rowid, t2.rowid, t3.rowid, <.. more fi

Re: [sqlite] Results of Joins in sqlite

You are free to build your own result conversion routine on top of the SQLite Api. May I suggest selecting the rowids of the tables too i.e. SELECT t1.rowid, t2.rowid, t3.rowid, <.. more fields ...> FROM <...your join...>; When you first come across a new rowid you can create your memory

Re: [sqlite] Expose struct Mem and struct Vdbe to other application

application along with sqlite source code. Thanks Prakash On Tue, Sep 23, 2014 at 11:54 AM, Hick Gunter <h...@scigames.at> wrote: > IMHO you are going down a dark and dangerous passage. If your approach > really does require severe hacking of SQLite internals then maybe that > i

Re: [sqlite] Expose struct Mem and struct Vdbe to other application

struct sqlite3,which is exposed that way ? Thanks Prakash On Mon, Sep 22, 2014 at 8:38 PM, Hick Gunter <h...@scigames.at> wrote: > Use the sqlite3_column_ functions to return result fields... > > Or you need to use the non-amalgamation sources and integrate them > into your

Re: [sqlite] Expose struct Mem and struct Vdbe to other application

Use the sqlite3_column_ functions to return result fields... Or you need to use the non-amalgamation sources and integrate them into your build environment. Such use is probably strongly discouraged by SQLite developers, as the internal structures are subject to change without notice. Also, a

Re: [sqlite] Definition of struct sqlite3_stmt

It is at the very end of vdbeint.h -Ursprüngliche Nachricht- Von: Prakash Premkumar [mailto:prakash.p...@gmail.com] Gesendet: Montag, 22. September 2014 07:29 An: General Discussion of SQLite Database Betreff: [sqlite] Definition of struct sqlite3_stmt Hi, Can you please tell me where

Re: [sqlite] SQL Query to Vdbe Instructions

The sqlite3_prepare() functions convert the SQL statement into an executable VDBE program. You can view the results in the Sqlite shell by typing: .explain explain ; which will show the VDBE opcodes generated for the query (the .explain switches the output format to something suitable for

Re: [sqlite] SET (x,y) = (x1,y1)?

Maybe you can reformulate the query to fit INSERT OR UPDATE INTO t SELECT t.a,t.b,...,s.x,s.y FROM t, s ... -Ursprüngliche Nachricht- Von: Mark Lawrence [mailto:no...@null.net] Gesendet: Montag, 15. September 2014 10:51 An: sqlite-users@sqlite.org Betreff: [sqlite] SET (x,y) = (x1,y1)?

Re: [sqlite] Cannot retrieve SQLite Db Data Immediately After Application Startup

Maybe you are mixing C malloc/free with sqlite3 memory allocation routines? Like allocating from sqlite and then freeing to C or vice versa? -Ursprüngliche Nachricht- Von: Bob Moran [mailto:bmo...@cicaccess.com] Gesendet: Mittwoch, 03. September 2014 06:25 An: General Discussion of

Re: [sqlite] Problem with Update Statement

Obviously the problem was caused by incorrectly cobbling together theSQLite statement. AFAICT the original code produces UPDATE RecordGrid SET LineNumber='',self_empty_info_gender_PRect=',,,' WHERE RecordGridID=' Which is clearly invalid (the RHS of the WHERE condition is not

Re: [sqlite] How to control cpu usage while transaction in progress

SQLite is supposed to process queries as fast as possible. Run your heavyweight queries in a dedicated thread and use your OS' way of prioritizing threads to lessen the "felt impact" on "interactive" threads (at the cost of increasing elapsed time). -Ursprüngliche Nachricht- Von: dd

Re: [sqlite] Producing RFC4180-compliant CSV output

How about piping your csv file through unix2dos? -Ursprüngliche Nachricht- Von: Peter Waller [mailto:pe...@scraperwiki.com] Gesendet: Donnerstag, 24. Juli 2014 11:27 An: sqlite-users@sqlite.org Cc: developers Betreff: [sqlite] Producing RFC4180-compliant CSV output Hi All, We're using

Re: [sqlite] Preferred cast in C#

Why is the column nullable if you require a default value to be returned? -Ursprüngliche Nachricht- Von: Random Coder [mailto:random.co...@gmail.com] Gesendet: Dienstag, 15. Juli 2014 03:50 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Preferred cast in C# Could you not

Re: [sqlite] sqlite-3.8.5: query takes quite a while to execute although there is a "limit 0" clause

1 000 Bye. -- Reinhard Nißl, TB3, -198 -Ursprüngliche Nachricht- Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] Im Auftrag von Hick Gunter Gesendet: Dienstag, 8. Juli 2014 11:58 An: General Discussion of SQLite Database Betreff: Re: [sqlite] sqlite-3.

Re: [sqlite] sqlite-3.8.5: query takes quite a while to execute although there is a "limit 0" clause

Output from .explain explain query plan select... explain select... would be interesting -Ursprüngliche Nachricht- Von: Nissl Reinhard [mailto:reinhard.ni...@fee.de] Gesendet: Dienstag, 08. Juli 2014 11:46 An: sqlite-users@sqlite.org Betreff: [sqlite] sqlite-3.8.5: query takes quite a

Re: [sqlite] CRUD Statistics

You can use the sqlite3_update_hook() interface to supply a callback that is invoked for (most, see documentation) INSERT, UPDATE and DELETE operations (database name, table name and rowid are passed). You can use the sqlite3_commit_hook() and sqlite3_rollback_hook() functions to determine

Re: [sqlite] sqlite & INNER JOIN & vtable

The type of join is unaffected by the type of table (native or virtual). Pretend all tables are native SQLite tables. You must return correct results from your xBestIndex function for the cost-based optimiser to select an efficient plan. -Ursprüngliche Nachricht- Von: Micka

Re: [sqlite] Error xBestIndex returned an invalid plan

An: General Discussion of SQLite Database Betreff: Re: [sqlite] Error xBestIndex returned an invalid plan On Mon, Jul 7, 2014 at 10:25 AM, Hick Gunter <h...@scigames.at> wrote: > "fred" is only known if your select statement references only that one table > and the value is

Re: [sqlite] Error xBestIndex returned an invalid plan

well after the sqlite3_prepare() call. -Ursprüngliche Nachricht- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Freitag, 04. Juli 2014 16:22 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Error xBestIndex returned an invalid plan On Fri, Jul 4, 2014 at 2:50 PM, Hick

Re: [sqlite] Error xBestIndex returned an invalid plan

_vtab_cursor *cur, int idxNum, const char *idxStr, int argc, sqlite3_value **argv ){ that argv[0] is column index 4 by example ? Micka, On Fri, Jul 4, 2014 at 12:17 PM, Hick Gunter <h...@scigames.at> wrote: > AFAIKT you currently have two supported operation modes: > > a) full tab

Re: [sqlite] Bug? sqlite3_column_name returns name of PK for rowid

Of course it does. The good news is that SQLite tends to stick with whatever it comes up with first unless there is a significant change to the query. -Ursprüngliche Nachricht- Von: Tim Streater [mailto:t...@clothears.org.uk] Gesendet: Freitag, 04. Juli 2014 13:28 An: General Discussion

Re: [sqlite] Error xBestIndex returned an invalid plan

the field aOrderBy, which is documented to be an input field. thx, I've deleted it ! Micka, On Fri, Jul 4, 2014 at 11:20 AM, Hick Gunter <h...@scigames.at> wrote: > As you noticed, you were asking to have the values of unusable > constraints passed to your vt_filter function. > > Bu

Re: [sqlite] Error xBestIndex returned an invalid plan

As you noticed, you were asking to have the values of unusable constraints passed to your vt_filter function. But how are you telling your vt_filter function which fields the passed values belong to? I don't see how the column number of the first constraint - usable or not- is going to be

Re: [sqlite] evaluate expression stored in a column

Not possible in SQL. "b.answer || a.epxr" is a string. It does not get evaluated. Unless you write your own eval() function that executes its parameter as an SQL statement, which is going to be very slow as each result row needs to prepare, step and finalize its very own statement.

Re: [sqlite] Problem with many connections

: General Discussion of SQLite Database Betreff: Re: [sqlite] Problem with many connections On 3 Jul 2014, at 8:24am, Hick Gunter <h...@scigames.at> wrote: > How about this? > > > sqlite3_stmt *sqlite3_next_stmt(sqlite3 *pDb, sqlite3_stmt *pStmt); > > This interface returns a

Re: [sqlite] Problem with many connections

How about this? sqlite3_stmt *sqlite3_next_stmt(sqlite3 *pDb, sqlite3_stmt *pStmt); This interface returns a pointer to the next prepared statement after pStmt associated with the database connection pDb. If pStmt is NULL then this interface returns a pointer to the first prepared statement

Re: [sqlite] Problem with many connections

What is your sequence of calls? What do you mean by "one connection for the application lifetime" and "others on demand for each transaction"? A "connection" is created/destroyed (aka opened/closed) with sqlite3_open resp. sqlite3_close calls. This opens/closes the underlying file handles. A

Re: [sqlite] Sequential numbers

> >-Ursprüngliche Nachricht- >Von: RSmith [mailto:rsm...@rsweb.co.za] >Gesendet: Mittwoch, 25. Juni 2014 21:54 >An: sqlite-users@sqlite.org >Betreff: Re: [sqlite] Sequential numbers > > >On 2014/06/25 21:38, Dave Wellman wrote: >> Hi Petite, >> Many thanks fo rthsuggestion, it works a

Re: [sqlite] Like and percent character

Probably you are using a variant of the printf() function to generate your statement and it is interpreting the %m as strerror(errno) (see man 3 printf), whereas it is ignoring %' (thousands separator for decimal conversions) either because it does not support this conversion or it is missing

Re: [sqlite] Multiple reads and writes to a single DB connection from multiple threads

If you compile with SQLITE_THREADSAFE=1 then multiple calls from different threads will be serialized by SQLite. "Serialized" means that only one thread at a time will be allowed to run within SQLite; API calls from other threads will block until the currently running thread returns. If your

Re: [sqlite] Storing opaque data, but would like sorted indexes on some fields

create table mytable (f1, f2, f3, ..., data blob); -- duplicate the data OR create table mytable (f1, f2, f3, ..., dref integer); -- reference the data (e.g. record position in file) and providing the values of the internal fields on insert -Ursprüngliche Nachricht- Von: Hayden

Re: [sqlite] Simple Select from IN - from a newbie.

select p.id,p.name from TeamPersonTable tp join PersonTable p on (p.id = tp.personId) where tp.teamId = 1; -Ursprüngliche Nachricht- Von: Humblebee [mailto:fantasia.d...@gmail.com] Gesendet: Donnerstag, 22. Mai 2014 13:40 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Simple Select

Re: [sqlite] Simple Select from IN - from a newbie.

No. The internal table stores only unique keys. -Ursprüngliche Nachricht- Von: Humblebee [mailto:fantasia.d...@gmail.com] Gesendet: Donnerstag, 22. Mai 2014 11:39 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Simple Select from IN - from a newbie. Thank you everyone for your kind

Re: [sqlite] Simple Select from IN - from a newbie.

You show a field parId in your TeamTable, but select it from the PersonTable. Maybe you mean SELECT * FROM PersonTable WHERE id IN (SELECT personIDs FROM TeamTable WHERE parId = 4); -Ursprüngliche Nachricht- Von: fantasia dosa [mailto:fantasia.d...@gmail.com] Gesendet: Mittwoch, 21.

Re: [sqlite] LIKE operator and collations

14, 2014 at 8:30 AM, Hick Gunter <h...@scigames.at> wrote: > Actually SQLite does support X'...' literals for creating blobs. Note sure how that's relevant Hick. We don't need a blob, but a integer for char(). I was obviously talking about *number* literals (prefixed with 0b, 0, 0x

Re: [sqlite] LIKE operator and collations

Actually SQLite does support X'...' literals for creating blobs. -Ursprüngliche Nachricht- Von: Dominique Devienne [mailto:ddevie...@gmail.com] Gesendet: Dienstag, 13. Mai 2014 18:19 An: General Discussion of SQLite Database Betreff: Re: [sqlite] LIKE operator and collations On Tue,

Re: [sqlite] WITHOUT ROWID option

Judging from the documentation there is not (optional methods may have a NULL pointer in the method table). Maybe this will/has change(d) 2.12 The xRowid Method int (*xRowid)(sqlite3_vtab_cursor *pCur, sqlite_int64 *pRowid); A successful invocation of this method will cause *pRowid to be

Re: [sqlite] How do I know the python functions registered on SQLite ?

We register our defined functions in a linked list in memory for the same reason (and have also implemented a .func in the shell to list all/matching registered functions) -Ursprüngliche Nachricht- Von: big stone [mailto:stonebi...@gmail.com] Gesendet: Donnerstag, 08. Mai 2014 18:35

Re: [sqlite] Storing amount?

SQLite does not care about (or enforce) magnitude and precision hints. "numeric" alone is just as good, or even "integer". Textual values that look like numbers will be stored as integer (if they evaluate to a whole number) or real (if not). Increasing a salary of 5000 by 3% will result in a

Re: [sqlite] duplicate row in sqlite3 database

Look into the ON CONFLICT clause -Ursprüngliche Nachricht- Von: techi eth [mailto:techi...@gmail.com] Gesendet: Donnerstag, 08. Mai 2014 11:14 An: General Discussion of SQLite Database Betreff: [sqlite] duplicate row in sqlite3 database Hi, SQlite3 have any method where it can avoid

Re: [sqlite] sqlite3_bind_text issue

>-Ursprüngliche Nachricht- >Von: lyx [mailto:sdu...@163.com] >Gesendet: Montag, 05. Mai 2014 05:00 >An: sqlite-users@sqlite.org >Betreff: Re: [sqlite] sqlite3_bind_text issue > >I have tried to use SQL_TRANSIENT instead of SQLITE_STATIC in >sqlite3_bind_text. But the result is still not

Re: [sqlite] Is it better that sqlite support share One Database Connection In Multithreads?

Imagine the following sequence on a "multi thread shared" connection. Thread A prepares a SELECT statement Thread A steps the statement a couple of times to retrieve some data Thread B comes along an finalizes the statement What do you propose should happen when thread A tries to step the

<    3   4   5   6   7   8   9   10   >