Re: [sqlite] Packing integer primary key with field bits

For the sake of the argument, let's try to devise a workable scheme for such an undertaking: Lets assume you have a 32-bit "real rowid" and four 8-bit "value" fields. How to distribute these in the 64-bit rowid? Rrid low = MSB | v1 | v2 | v3 | v4 | r1r2r3r4 | LSB or Rrid high = MSB | r1r2r3r4

Re: [sqlite] Packing integer primary key with field bits

Simple answer: Don't! This sounds like a misguided attempt to save space in the disk image by messing around with the rowid of a table (which is what "integer primary key" declares the column to be an alias of). Whatever you stuff in there needs to be unique and, if you intend to use foreign

Re: [sqlite] Using multi-value indexes for subset queries

Your experiments are not reproducible unless you provide at least an indication of the schema. Most probably, something you have not yet considered/revealed makes using the index to look up a irrelevant. Maybe a is declared as "integer primary key", making it an alias of the rowid.

Re: [sqlite] Optimizing searches across several indexes

An index is only usable for that subset of a queries' equality constraints that forms a leading subset of the fields handled by the index. E.g.if you are looking at equality constraints for fields a, b and c in one query, then you need an index whose first three fields are a, b and c (in any

Re: [sqlite] hex and char functions

The sqlite char() function returns unicode. Apparently, the encoding for code point 133 is two characters, namely c2 85. You seem to be expecting char() to return ISO characters, which it does not do. Calling sqlite3_value_text16 instructs SQLite to convert the contents of the field into utf16

Re: [sqlite] TEXT shows as (WIDEMEMO) in DBGrid

It would be so much easier if you could provide hex dumps of the strings involved. Maybe just a few characters and a verbal description of what you think you are storing (greek lowercase alpha, ...). From appearances it seems that your text objects are locale aware, which would suggest a

Re: [sqlite] TEXT shows as (WIDEMEMO) in DBGrid

Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Lars Frederiksen Gesendet: Montag, 07. August 2017 09:00 An: 'SQLite mailing list' Betreff: Re: [sqlite] TEXT shows as (WIDEMEMO) in DBGrid ... But I also realized that

Re: [sqlite] sequencer

A "temp table" would only be visible in the session that creates it and would not live past the end of the session. Sequences should be persistent... -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Nico Williams Gesendet:

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im >Auftrag von Nico Williams > >On Tue, Aug 01, 2017 at 10:56:47AM -0700, Matt Chambers wrote: >> load_extension() has the very sensible behavior of: >> > So for example, if "samplelib" cannot be loaded, then names like >>

Re: [sqlite] sequencer

A sequence is very easily implemented as a virtual table that keeps the current values in a separate table my_sequences (name text primary key, initial integer, current integer, increment integer). CREATE VIRTUAL TABLE seq_1 USING sequence ([[,]]); -- defaults 1, 1 The xCreate/xConnect

Re: [sqlite] New draft document on the new pointer-passing interfaces

Naming conventions (if strictly adhered to) are moderately good at avoiding conflicts, but take the guesswork out of "faking a pointer". It also assumes that pointers for "whatever" are interchangeable between different queries in a process, i.e. passing a "whatever" pointer from statement A to

Re: [sqlite] VALUES clause quirk or bug?

You should NOT be relying on column names UNLESS you set them yourself. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von petern Gesendet: Samstag, 08. Juli 2017 21:37 An: SQLite mailing list

Re: [sqlite] Concurrent reads for VTs with in-memory data structures

We are using a shared memory segment (created during application startup) to contain the data records, but you could also use a memory mapped file. This will keep the static data identical across all connections and processes. When writing your VT module, consider giving it a readonly/readwrite

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

Double quotes is specifically for building identifiers that "look strange" (i.e. embedded spaces, keywords, ...) which IMHO should be avoided because it tends to clutter up the statement. Single quotes is for building strings. Integer is a keyword, "integer" is an identifier and 'integer' a

Re: [sqlite] How to search for fields with accents in UTF-8 data?

CP1252 = Windows-1252 = ISO 8859-1 aka Latin-1, an extension of ASCII -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Gilles Gesendet: Montag, 19. Juni 2017 16:23 An: SQLite Maillist

Re: [sqlite] unusual but trivially reproducible bug

Limit2 is taken to be the alias of table foo. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Robert Cousins Gesendet: Sonntag, 18. Juni 2017 21:19 An: sqlite-users@mailinglists.sqlite.org Betreff: [sqlite] unusual but

Re: [sqlite] How to search for fields with accents in UTF-8 data?

What do the following statements return, when run in sqlite3.exe (Please note that single quotes are SQLite3 string delimiters): SELECT hex('Île-de-France'); SELECT hex(region) FROM MyTable WHERE LIBREG like '%le-de-France' LIMIT 1; I expect one of them is ISO (lead character > 7F) and the

Re: [sqlite] Outputting to CSV - row is not quoted when there are no spaces

I have found it much simpler and more robust to write a CSV virtual table to handle the formatting. e.g. CREATE VIRTUAL TABLE csv_input USING csv ( ); Opens the named file, reads the first line and interprets the contents as a list of field names. You can then INSERT INTO SELECT FROM

Re: [sqlite] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?

"many (sometimes several thousand) statments" sounds like it could be heavy on memory requirements. Are you inserting one row per statement or all rows in one statement? The latter would be really hard on memory because SQLite will have to parse the whole statement and generate a gigantic SQL

Re: [sqlite] Unable to create index on attached database

Try Create index t2.idx on link (...) Which is what the syntax diagram would recommend. If you ask SQLite to create an index in t2, it will figure out that the table needs to be in t2 too. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

Looking at the generated program for mulit-tuple INSERT INTO ... VALUE yields the following structure: Only 1 tuple (standard case) 2 tuples Each further tuple adds a segment (1 opcode per field + data) and a yield opcode to the generated VDBE program, and an equivalent amount

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

Just like any other join, but with tables in the desired order and the word CROSS added SELECT ... FROM CROSS JOIN ... -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Thomas Flemming Gesendet: Dienstag, 30. Mai 2017

Re: [sqlite] SELECT WHERE with RTREE and second condition slow

INTEGER and LONG can both store 64 bits in SQlite. The difference is that "INTEGER PRIMARY KEY" makes the column an alias for the rowid, whereas "LONG PRIMARY KEY" defines a second, possibly redundant index. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

If you stuff all 18MB of your data into a single INSERT, then SQlite will need to generate a single program that contains all 18MB of your data (plus code to build rows aout of that). This will put a heavy strain on memory requirements and offset any speed you hope to gain. The SOP is to put

Re: [sqlite] Does prepare do arithmetic?

The expression is calculated as given in each case. The difference being that a constant constraint is recognized in the query planner (and thus calculated once with the result being saved), whereas a constant expression as a return value is not. -Ursprüngliche Nachricht- Von:

Re: [sqlite] Does prepare do arithmetic?

Try for yourself. .mode explain explain select 2 +2; explain select 4; BTW: The "from Tbl" clause will only affect the number of result rows (1 for every row in Tbl). -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von xTom

Re: [sqlite] No check for invalid constraints

There is no "underscore notation" for multiword constraints. "NOT_NULL" is not "NOT NULL" and "PRIMARY KEY" is not "PRIMARY_KEY". As long as whatever you provide can be split up into tokens in a way that matches the grammar there will be no syntax error. It will just not be matching what you

Re: [sqlite] Group by Literals

The number 1 references the first column of the result set, 2 the second, and 3 is an error because there are only 2 columns. If the expression is a constant, then there is only 1 group. You are missing any meaningful information because you do not have an aggregate expression in your select

Re: [sqlite] SQLite3.Step fast forward / skipping rows

The trick is to have a way to identify the first/current row and use that in the WHERE clause. e.g. SELECT ... FROM customers WHERE customer_id >= last_displayed LIMIT window_size If your select statement is a complex join without any usable key, you will have to resort to storing the results

Re: [sqlite] vtable and usable constraint

haps I missed something in the C API. Is there something that can be done with the DB handle after calling one of the _prepare or _step functions which reveals the query cost already calculated by SQLite? On Mon, May 22, 2017 at 1:35 AM, Hick Gunter <h...@scigames.at> wrote: >

Re: [sqlite] vtable and usable constraint

SQLite will determine the set of constraints that are possible against yout virtual table from the query you are preparing. It will then call your xBestIndex function a number of times with different subsets of the constraints marked aus "usable". Your xBestIndex function needs to return (among

Re: [sqlite] The cli truncates values containing null chars

This is well documented. The result of expressions involving strings with embedded NUL characters is undefined. The conversion BLOB -> TEXT will check for and stop at the first embedded NUL; in none occurs, a terminating NUL is added. You may store any sequence of bytes in a BLOB, but please do

Re: [sqlite] SQLite automatic backup database on Windows Server

SQLite is a library, not a server. There is no automatic backup. You can copy the .db file when the application is inactive, or have the application call the SQLite backup API to perform the backup whenever it feels the urge to do so. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

Maybe Prakash Premkumar or Sairam Gaddam , who seemed hell bent on implementing stored procedures (or at least storing generated bytecode) about two years ago, have made progress in the meantime? -Ursprüngliche Nachricht- Von:

Re: [sqlite] strange behaviour on sqlite shell output…

org> Betreff: Re: [sqlite] strange behaviour on sqlite shell output… On 18 April 2017 at 16:28, Hick Gunter <h...@scigames.at> wrote: > Richard Hipp wrote: > >I think the OP is referring to a problem that comes up because the > >field > width and precision of a printf(

Re: [sqlite] strange behaviour on sqlite shell output…

>On 4/18/17, Hick Gunter <h...@scigames.at> wrote: >> I don't see any calls to sqlite3() functions in your "isolated test case". >> Maybe you are having problems with character encoding outside of sqlite? > >I think the OP is referring to a problem

Re: [sqlite] strange behaviour on sqlite shell output…

I don't see any calls to sqlite3() functions in your "isolated test case". Maybe you are having problems with character encoding outside of sqlite? -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von aotto Gesendet: Montag,

Re: [sqlite] performing an UPDATE on a table-valued function

A TVF is just a "calling convention" for abbreviating a specific SELECT on a virtual table. It implies neither mutability of the returned values, nor persistence beyond the scope of the statement. Indeed the carray() example you give later is an eponymous ephemeral table, i.e. it does not

Re: [sqlite] Unexpected results from <, <= and BETWEEN with a CTE in 3.18.0

My guess ist hat random() is being called once for each expression containing Y. Using constant values from a regular table works as expected. It is exactly the bytecode output by EXPLAIN that could help to determine what happens, but unfortunately you did not post it. asql> insert into t

Re: [sqlite] Search semantics with a virtual table?

The SQLITE_INDEX_SCAN_UNIQUE flag is a hint for the query planner. It does not affect query execution mechanics. You should be returning TRUE from xEOF after the first call to your xNext function (provided indeed that there is only 1 row that matches the value). Or you need to add a LIMIT 1

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

", find me the most recent "d" (in my example, the "b" I gave was 1). R Smith's approach does the trick nicely! Thanks, Hamish On 3 April 2017 at 10:48, Hick Gunter <h...@scigames.at> wrote: > I am not sure I correctly understand what you want. > > This is t

Re: [sqlite] Indexing WHERE with GROUP BY and ORDER BY

I am not sure I correctly understand what you want. This is the value of d associated with a randomly chosen record from the group of records having the highest value of a tha also fulfills b == 1; SELECT MAX(a),d FROM x WHERE b=1; If you want the value of d within each group of records

Re: [sqlite] VT table behavior change between 3.10 and 3.17

The Close (1) in the second program is closing (=deleting) the ephemeral table. I have no idea if this would also be handled in Halt or not. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Cezary H. Noweta Gesendet:

Re: [sqlite] VT table behavior change between 3.10 and 3.17

ed, 29 Mar 2017, Hick Gunter wrote: > Can you provide an example of the bytecode produced? sqlite> .explain sqlite> explain delete from device_cfgrecord where name == 'bar'; addr opcode p1p2p3p4

Re: [sqlite] VT table behavior change between 3.10 and 3.17

change between 3.10 and 3.17 On Wed, 29 Mar 2017, Hick Gunter wrote: > > To avoid anomalies when changing "key fields", SQLite will scan through the > whole cursor first, saving the rowids and new contents of the record(s) > satisfying the WHERE clause. It will then close

Re: [sqlite] VT table behavior change between 3.10 and 3.17

> [...] Why xOpen-ed cursor cannot be used to write to a table? > The xUpdate method alone is used to perform updates to virtual tables. > It does not take a cursor argument. > Also, there is no method taking a cursor argument that allows data to be > changed. > The documentation is correct

Re: [sqlite] VT table behavior change between 3.10 and 3.17

etween 3.10 and 3.17 Hello, On 2017-03-29 10:48, Hick Gunter wrote: > According to the documentation of Virtual Tables and Opcodes: > [...] > xOpen( table, cursor) is called to announce that SQLite intends to > read from a table. A cursor cannot be used to write to a table. Do >

Re: [sqlite] VT table behavior change between 3.10 and 3.17

According to the documentation of Virtual Tables and Opcodes: xBegin( table ) is called to announce that SQlite intends to write to the table. There is no cursor involved. Do whatever is necessary to write to the backing store and set any VT implementation specific information in the table

Re: [sqlite] Goto's in .explain output

The section at the end, which is executed first, contains the necessary database locking and schema checking opcodes. These cannot be generated before the rest of the program has analysed which operations are performed on which tables and therefore which of the attached database(s) needs to be

Re: [sqlite] operation unexpectedly applied to both main and temp?

Ignore the first note. Table main.t is persistent, whrereas temp.t is automatically dropped on closing the connection. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hick Gunter Gesendet: Mittwoch, 29. März 2017 08:40

Re: [sqlite] operation unexpectedly applied to both main and temp?

I find the .explain/explain functionality very helpful in clearing up what happens and why. Note the error when creating main.t. Note the database number 1 (for temp) vs. 0 (for main) in the OpenWrite and TableLock instructions. asql> create temp table t (db, val); asql> create table main.t

Re: [sqlite] VT table behavior change between 3.10 and 3.17

Can you provide an example of the bytecode produced? In the SQLite shell type: .explain explain ; Typical output (with SQLite version 3.7.14.1): asql> .explain asql> explain update mytable set myfield=2 where myconst=7; addr opcode p1p2p3p4 p5 comment

Re: [sqlite] All versions compatible "very big" estimatedCost (Virtual tables)

ddevie...@gmail.com> wrote: > On Tue, Mar 28, 2017 at 11:00 AM, Hick Gunter <h...@scigames.at> wrote: > > > > > The "estimated cost" is described as "how many disk IO operations > > are expected". Version higher than 3.8.2 allow setting an "e

Re: [sqlite] All versions compatible "very big" estimatedCost (Virtual tables)

>FWIW, I've often wondered about the cost estimates of real tables versus >virtual tables, especially since many vtables implementations don't involve >real IO but pure in-memory computations. There's >very little advice or >documentation on this important subject, and Max's email reveals that

Re: [sqlite] What are the rules for defining names for tables and columns?

>-Ursprüngliche Nachricht- >em...@n0code.net wrote: >> I’ve scoured the archives and the sqlite documentation but can’t find >> the definitive rules for defining table and column names. > >Everything is allowed, except names beginning with "sqlite_". > This calls for a (not quite) OT

Re: [sqlite] How does one block a reader connection?

Hey, neat idea! To expand on my previous post: CREATE TRIGGER wakeup AFTER INSERT ON cmd BEGIN SELECT cond_broadcast('cmd_ready'); END; -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Richard Damon Gesendet: Sonntag, 26.

Re: [sqlite] How does one block a reader connection?

I think this kind of problem (transfer of information between cooperating processes) is best solved using the tools designed for inter-process communication and not attempting to abuse a DB system designed to isolate processes from unfinished changes. Have the processes share a condition

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

If I Interpret your text correctly, you are claiming that your mpxMalloc/mpxRealloc is issuing the same address twice, without mpxFree being called in between? Are you tracking mpxRealloc calls correctly? If the area pointed to is not large enough, a new area will be allocated, the data copied

Re: [sqlite] Still running 3.7.x. Was: RIGHT JOIN! still not supported?

rag von Richard Hipp Gesendet: Donnerstag, 23. März 2017 12:06 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [sqlite] Still running 3.7.x. Was: RIGHT JOIN! still not supported? On 3/23/17, Hick Gunter <h...@scigames.at> wrote: > I am still runnning SQLIte &g

Re: [sqlite] RIGHT JOIN! still not supported?

After some thinking I came up with this: First, set up the example (note: no CTE as I am still runnning SQLIte 3.7.14.1): CREATE temp TABLE stock(id, cid, sid); CREATE temp TABLE clients(id,name); CREATE temp TABLE suppliers(id,name); insert into stock (id) values (1),(2),(3),(4),(5),(6),(7);

Re: [sqlite] RIGHT JOIN! still not supported?

Since LEFT JOIN and RIGHT JOIN while also swapping the tables are interchangeable, why should this not work? SELECT ... FROM clients LEFT JOIN ON ... ( suppliers LEFT JOIN stock ON ...) WHERE ...; -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

Ad 4) not quite,but close. If the index used for a join also contains all the other fields of that table that are referenced in the query, SQLite can use those values to avoid reading in the corresponding table row. This saves memory (no storage for table row consumed), CPU cycles (no going

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

n artifact of the way Navcat for SQLite works. I suspect we need to be more careful about how we use the tool. We'll now time the results of each query and run them twice to see the affect. No idea how long this will take but suspect a few hours :) I will post back the results as other people may

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

>On 17 Mar 2017, at 10:20am, Rob Willett wrote: > >> CREATE INDEX "Disruptions_idx4" ON Disruptions ("status" COLLATE NOCASE ASC); >> >> […] >> >> As part of the larger more complex query, we are executing the query >> >> ``` >> select * from Disruptions where status

Re: [sqlite] How to use parameterized queries in SQLite.Net

A parameterized query enables you to run a fixed query with arbitrary data that is unknown during compile time, multiple times (once for each set of parameters), without re-preparing the statement (which is costly) in between. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] sqlite3 feature or regression

Perhaps an analogy will help: Imagine you are given a piano. Pressing keys on the piano will cause the corresponding tones to be played. If you hit the keys on the piano with a hammer, then this too will cause tones to be played; however, it will also most likely cause mechanical failure (i.e.

Re: [sqlite] SQL help: by-range aggregation

Alternatively create a "range" table, insert your defined ranges and join/subquery to the original query. Create table range (label text, from integer, to integer); -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Simon

Re: [sqlite] Need some help running sqlite3 command line

sulting 430 Cole St. Seekonk, MA 02771 Cell: (508) - 954 - 2536 EMail: jacob.syl...@gmail.com Web: http://www.jacobsylvia.com On Mar 6, 2017 7:23 AM, "Hick Gunter" <h...@scigames.at> wrote: > From sql shell (from a file works the same way): > > asql> create te

Re: [sqlite] Need some help running sqlite3 command line

From sql shell (from a file works the same way): asql> create temp table test (a text); asql> insert into test values('a ...> b ...> c ...> d'); rows inserted - 1 asql> select * from test; a -- a b c d -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

AFAICT It is not the new value of the integer but rather the new contents of the blob field that causes the record image to grow and exceed the previously allocated space, which means the row has to move. Rewriting the record and thus expiring the blob handle is triggered by the update of the

Re: [sqlite] Index usefulness for GROUP BY

YES. AFAIK if SQLite detects that the rows are/can be made to be returned in GROUP BY order it can use internal variables to accumulate the group results. This is expected to be significantly faster than locating and updating a temporary BTree row for each record scanned. -Ursprüngliche

Re: [sqlite] Non-unique columns in unique index

qlite-users-boun...@mailinglists.sqlite.org] Im >Auftrag von R Smith >Gesendet: Donnerstag, 02. März 2017 16:10 >An: sqlite-users@mailinglists.sqlite.org >Betreff: Re: [sqlite] Non-unique columns in unique index > > >On 2017/03/02 4:44 PM, Keith Medcalf wrote: >> On Thursday, 2 March

Re: [sqlite] Non-unique columns in unique index

I tried to create a test table and two indices thus: >create temp table test (id integer primary key, name text unique, bs integer, >data text); >create unique index plain on test(name); >create unique index cover on test(name,bs); NB: The field name has a unique constraint As long as the

Re: [sqlite] Database is locked

Not directly, but you can query the locks on the file as per documentation: struct flockv_pending; // = { F_WRLCK, SEEK_SET, PENDING_BYTE , 1, 0}; struct flockv_reserved; // = { F_WRLCK, SEEK_SET, RESERVED_BYTE , 1, 0}; struct flockv_shared;//

Re: [sqlite] UNION

You are literally mixing apples and oranges without creating a superclass "fruit" that contains a field to tell them apart and a set of attributes (=fields) merged from the attributes of the component tables. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] foreign key cardinality

sprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von James K. Lowden Gesendet: Dienstag, 28. Februar 2017 22:41 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] foreign key cardinality On Tue, 28 Feb 2017 17:13:30 + Hick Gu

Re: [sqlite] Crash on Android

Looks like attempting to dereference a NULL pointer. Without a backtrace of the call stack this is not of much use. Most commonly this is caused by an uninitialized variable on the stack, accessing a structure that has been freed/garbage collected or memory being clobbered by a rogue write

Re: [sqlite] foreign key cardinality

Your assumption does not correspond with the documentation, see http://sqlite.org/foreignkeys.html : "The parent key is the column or set of columns in the parent table that the foreign key constraint refers to. This is normally, but not always, the primary key of the parent table. The parent

Re: [sqlite] No way to check for CHECK constraint violations a posteriori

A CHECK constraint is just a special trigger that allows you to raise an error and only runs on INSERT and UPDATE. It has nothing to do with internal database structure (pragma integrity_check) or with foreign keys (pragma foreign_key_check; you have to enable foreign key checking first

Re: [sqlite] Linux top command and sqlite

Definitely 'D' as in "Disk I/O", if you type "1" while runing top, the results for each CPU are displayed separately und you will find at least 1 line (not necessarily always the same line) with a significant percentage of "wa" (I/O wait state). Apparently your transactions involve very much

Re: [sqlite] 3.17.0 does not read updated DB

The unix mv command does not affect currently open file handles, only the directory entry or entries (atomically, unless it is a cross-filesystem mv, which is accomplished by pretrending you said cp and is in no way atomic). As long as the first command shell is running, it will continue to see

Re: [sqlite] sqlite3 hangs on query

Maybe the original intent was to count all the rows in all the tables separately and return a vector of record counts, as a poor man's integrity check to make sure no rows got lost. Like Select (select count() from t1) as t1,(select count() from t2) as t2, ...; -Ursprüngliche

Re: [sqlite] thousand separator for printing large numbers

The beauty of SQLite lies in its user extensability. If you want a presentation layer function in SQLite you can always write your own custom function, without forcing your specific needs on the community as a whole. e.g. print_money( [, ]) with a check that is actually an integer (and not a

Re: [sqlite] Virtual table vs real table query performance

sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Mittwoch, 08. Februar 2017 16:42 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Virtual table vs real table query performance On Wed, Feb 8, 2017 at 4:30 PM, Hick Gunter <h...@scigames.a

Re: [sqlite] Virtual table vs real table query performance

Auftrag von Bob Friesenhahn Gesendet: Mittwoch, 08. Februar 2017 15:39 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Virtual table vs real table query performance On Wed, 8 Feb 2017, Hick Gunter wrote: > Having imlemented a memory-based virt

Re: [sqlite] Virtual table vs real table query performance

Having imlemented a memory-based virtual table complete with indices, full table scan and direct access via rowid (which happens to be the memory address of the row) I can do a batch delete of 100.000 rows (in a table with 1 composite index) in about 2 seconds (3.7 seconds with the condition)

Re: [sqlite] FOREIGN KEY question

Did you look at the syntax diagrams? If you mean giving names to foreign key clauses, then no. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Igor Korot Gesendet: Mittwoch, 08. Februar 2017 06:04 An: Discussion of SQLite

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

alence to OLE DB ... oledbCmd.CommandText = "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()? On Mon, Feb 6, 2017 at 1:55 AM, Hick Gunter <h...@scigames.at> wrote: > But only if you can guarantee that your statement inserts exactly one record > and that

Re: [sqlite] Transactions

Yes, putting a large number of inserts that affect the same table(s) into ona bulk transaction can be a huge speedup, because the operations can take place in memory without having to reach the disk surface until commit time. The optimal number of inserts/transaction depends on your hardware

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

But only if you can guarantee that your statement inserts exactly one record and that nothing is executed on your connection between the insert and the call. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Chris Locke

Re: [sqlite] "DISTINCT" makes a query take 37 times as long

ask. I just wanted to give feedback in case it would be useful. That's how i say thanks for a really useful product. Thanks. Mr. Hipp, and anyone else that has contributed to this product. ++ kevin On Thu, Feb 2, 2017 at 12:27 AM, Hick Gunter <h...@scigames.at> wrote: > DISTINCT forces the q

Re: [sqlite] IS NULL and IS NOT NULL constraints not passed into virtual table's xBestIndex

The supported constraints are: #define SQLITE_INDEX_CONSTRAINT_EQ 2 #define SQLITE_INDEX_CONSTRAINT_GT 4 #define SQLITE_INDEX_CONSTRAINT_LE 8 #define SQLITE_INDEX_CONSTRAINT_LT 16 #define SQLITE_INDEX_CONSTRAINT_GE 32 #define SQLITE_INDEX_CONSTRAINT_MATCH 64 #define

Re: [sqlite] Does SQLite use field definitions?

It will if you add AUTOINCREMENT -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Clyde Eisenbeis Gesendet: Donnerstag, 02. Februar 2017 15:12 An: SQLite mailing list Betreff: Re:

Re: [sqlite] Help with custom collation

Sorry misread that you are attempting to write a custom collation. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hick Gunter Gesendet: Donnerstag, 02. Februar 2017 09:06 An: 'SQLite mailing list' <sqlite-us

Re: [sqlite] "DISTINCT" makes a query take 37 times as long

DISTINCT forces the query optimizer to create an intermediate table to hold the results and compare each row of the non-distinct result set with an automatically created index. It may also affect the query plan in a way that chooses inefficient indices, which is more likely if you have not run

Re: [sqlite] Help with custom collation

The interface your (simple) function must support is: void xFunc(sqlite3_context*,int,sqlite3_value**) with the first parameter being the sqlite3_context, the second parameter being the number of arguments passed in, and the third parameter being an array of pointers to unprotected sqlite3_value

Re: [sqlite] Does SQLite use field definitions?

Yes. See http://sqlite.org/lang_createtable.html for details. I also suggest you look at http://sqlite.org/datatype3.html too -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Clyde Eisenbeis Gesendet: Dienstag, 31. Jänner

Re: [sqlite] Bitten by lack of isolation between SELECT and UPDATE on the same connection

Maybe adding "order by rowid" to your select statement can help avoid "sawing off the branch you are sitting on". Unless you need to update rowids... -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jens Alfke Gesendet:

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

alization in icu.c : sqlite3IcuInit > >On Thu, 26 Jan 2017 08:19:02 + >Hick Gunter <h...@scigames.at> wrote: > >> On LP_64 architactures, the integer 0 is 32 bits while (void *)0 is >> 64 bits, which makes more than a bit of a difference. A 64 bit integer >> 0 would be d

Re: [sqlite] text/numeric comparison confusion

The constant '1' (of storage class TEXT) has no affinity, neither has the constant 1 (of storage class INTEGER), nor the result of an expression - with documented exceptions, notably CAST( AS ). Consider: 1 = '1' --- 0 (1=1) = '1' --- 0 cast((1=1) as integer) = '1'

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