Re: [sqlite] Performance of writing blobs

[Clemens Ladisch]

Dominique Devienne wrote:
> My assumption
> was that after the zeroblob(N), there was enough room in the main DBs
> pages, such that the subsequent blob open+write+close did not need to
> generate any "page churn" (i.e. journal activity) and could write directly
> to the pages created on initial insert.

It does write to the same pages, but those pages must be copied to the
rollback journal so that they can be restored if the transaction is
rolled back.  (Or are the two passes inside the same transaction?)

Don't insert the zero blobs in the first pass; this still rewrites all
rows, but there is less data that might need to be rolled back.

Consider moving the blobs to a separate table.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance of writing blobs

[Clemens Ladisch]

Dominique Devienne wrote:
> On Mon, Jun 11, 2018 at 4:27 PM Clemens Ladisch  wrote:
>> It does write to the same pages, but those pages must be copied to the
>> rollback journal so that they can be restored if the transaction is
>> rolled back.  (Or are the two passes inside the same transaction?)
>
> They are part of the same transactions. So the no-rewrite pages should
> still apply, no?

Indeed.  I'd guess that just writing all those zero-filled pages is too
much overhead.

And the incremental blob I/O function are intended to access partial
blobs; when you write the entire value at once, just use SQL.

>> Don't insert the zero blobs in the first pass; this still rewrites all
>> rows, but there is less data that might need to be rolled back.
>>
>> Consider moving the blobs to a separate table.
>
>  Is that really/still relevant, considering the above?

Well, there's only one way to find out ...  ;-)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Check Constraint

[Clemens Ladisch]

Cecil Westerhof wrote:
> I want to create a field that only has values that consist of letters,
> numbers end '-'. So no spaces, quotes or special characters like: '@%$!'.
> What is the best way to write this check constraint?

The GLOB operator has inverted character classes.  So the field is valid
if its value does not contain any character that is not in the valid list:

CREATE TABLE [] (
  Field  CHECK(Field NOT GLOB '*[^0-9A-Za-z-]*')
);


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance of writing blobs

[Clemens Ladisch]

Dominique Devienne wrote:
> In JOURNAL mode, new data goes to DB file directly, and modified pages go to 
> the JOURNAL file.
> And since here this is INSERT-only, from empty tables, I assumed pages copied 
> to the JOURNAL
> file should be minimal.

Yes.  You can check the journal size with PRAGMA journal_mode = PERSIST.

> ... --blobs-as-hdf5=false --blobs-inline=false
> Write ROWs   =   4.358s (12.6%)
> Write BLOBs  =   3.130s ( 9.0%)
> Epilog   =  10.221s (29.5%)
>
> ... --blobs-as-hdf5=true --blobs-inline=false
> Write ROWs   =   4.368s (17.0%)
> Write BLOBs  =   4.179s (16.3%)
> Epilog   =   0.461s ( 1.8%)

This looks as if HDF5 does not do fsync().

Compare with PRAGMA synchronous = OFF, or run
https://docs.microsoft.com/en-us/sysinternals/downloads/sync
after you've finished with the HDF5 file.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database is malformed but no further information

[Clemens Ladisch]

Chris Brody wrote:
> My understanding is that mobile apps are not 100% predictable since
> they may be randomly suspended or terminated, at any point of time.

But SQLite is designed so that even killing the process will not
corrupt the database, as long as the storage device works correctly.

> On Tue, Jun 12, 2018 at 6:21 PM skywind mailing lists 
>  wrote:
>> This only happens when the app is terminated while running when the
>> iDevice switches off due to low battery issues.

Some flash devices corrupt not only the pages currently being written
to, but also other, unrelated data.  There's nothing software can do
to protect against that.

>> when I load my database into sqlite3 and run an integrity check
>> I only get the error message: Error: database disk image is malformed

Try the latest version of the sqlite3 shell.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected sqlite3_trace_v2 sqlite3_expanded_sql output.

[Clemens Ladisch]

Lodewijk Duymaer van Twist wrote:
> I'm tracing queries that my application makes using sqlite3_trace_v2 and
> sqlite3_expanded_sql in the callback. The application uses sqlite 3.22.0.
>
> At some point the software will call sqlite3_exec with the following 
> statement:
> BEGIN TRANSACTION;
> DELETE FROM call_lists WHERE id = 11;
> END TRANSACTION;
>
> The output of sqlite3_expanded_sql is:
> BEGIN TRANSACTION;
> DELETE FROM call_lists WHERE id = 11;
> DELETE FROM call_lists WHERE id = 11;
> DELETE FROM call_lists WHERE id = 11;
> DELETE FROM call_lists WHERE id = 11;
> END TRANSACTION;
>
> Is this normal explaiable behaviour or should I try to provide a repo?

Are you tracing multiple SQLITE_TRACE_xxx events?

And  says:
| An SQLITE_TRACE_STMT callback is invoked when a prepared statement first
| begins running and possibly at other times during the execution of the
| prepared statement, such as at the start of each trigger subprogram.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert or replace performance with self "references" column

[Clemens Ladisch]

Allen wrote:
> create table Transactions (Id integer primary key not null, Parent references 
> Transactions(id), Body varchar);
> create index Parent_Index on Transactions (Parent);
>
> EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, Body) 
> values (?1, ?2, ?3);
> 23 0 0 SCAN TABLE Transactions
> 43 0 0 SCAN TABLE Transactions
>
> - Is sqlite really doing one or two table scans to perform the "insert
> or replace" with a "references" self column?

It does the two scans for the foreign key constraint processing.
(The rowid processing is not mentioned in the EQP output.)

> - If so, is there a way to eliminate this (other than removing either
> the "references" or the "or replace")?

As mentioned by Keith, the index is not used because of the wrong
affinity of the Parent column.  With "Parent integer", both scans
are efficient.


Regars,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CASE and NULL

[Clemens Ladisch]

Andy Goth wrote:
> The expression "x = NULL" is meaningless since it will always evaluate
> to NULL, which CASE interprets as untruth, hence "WHEN NULL THEN" will
> never accomplish anything.
> [...]
> So I'm wondering: can we do better?

The expression "x = x" will fail for NULL, but succeed for everything
else.  So you can use that to implement a "not-NULL ELSE":

CASE x
WHEN 1 THEN ...
WHEN x THEN 'not NULL'
ELSE'NULL'
END


> [...]
> The next evolution in bloat is to also support AND, OR, NOT, and
> parentheses, allowing the LHS operand of any operator in a complex
> expression to be omitted

So you want to have your beloved COBOL features in SQL?  ;-)
http://www.3kranger.com/HP3000/mpeix/doc3k/B3150090013.11820/65.htm
http://www.csis.ul.ie/cobol/course/Selection.htm


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] To JSON or not to JSON

[Clemens Ladisch]

Cecil Westerhof wrote:
> my SQLite database contains for example:
>
> "5BF19111-9FD5-48CA-B919-A09411346A87""[
>   ""The journey of a thousand miles
> must begin with a single step.
>
> - Lao Tzu"",
>   ""Welke stap kun je vandaag zetten,
> om dat verre doel te bereiken?""
> ]""2018-07-07"
>
> Respectively quoteID, quote (with extra line(s)) and lastUsed.
>
> Is this an acceptable way to implement it, or would it be better to unravel
> the elements in different records?

Putting the quotes into a separate table, one per row, would allow you to
access them individually.

If you never need to access individual quotes, and if you know that this
will never happen in the future, you could just as well keep storing the
quotes as an array.  (And then please tell us how you manage to predict
the future.  ;-)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

[Clemens Ladisch]

rob.sql...@robertwillett.com wrote:
> gcc: internal compiler error: Killed (program cc1)

This is a compiler bug.  Check if updating gcc to a current version helps.

> Please submit a full bug report,
> with preprocessed source if appropriate.
> See  for instructions.

Otherwise, do this.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

[Clemens Ladisch]

Rob Willett wrote:
> I removed two instances of -O2 from the Makefile and, lo and behold, it 
> compiles.
>
> Sadly my database to check is approx 80GB which could be interesting.

The bottleneck is I/O speed; it does not matter whether sqlite3_analyzer uses 
ten
or twenty microseconds before waiting for the next batch of data from the disk.
There will be no noticeable difference.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table

[Clemens Ladisch]

Bram Peeters wrote:
> He reads a page from the file in sqlite3PagerSharedLock, but the file is 
> still 0 so the page is all zeros.

An empty file and a file filled with zeros are two different things.

Does the file system return SQLITE_IOERR_SHORT_READ?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error: foreign key mismatch - "loan" referencing "user"

[Clemens Ladisch]

Markos wrote:
> CREATE TABLE user (
> id_user integer PRIMARY KEY,
> ...
> CREATE TABLE loan (
> ...
> FOREIGN KEY(id_admin_loan, id_admin_devolution) REFERENCES user(id_user, 
> id_user)

I do not understand what this is trying to accomplish.
Why not two single-column FK constraints?

> Error: foreign key mismatch - "loan" referencing "user"

Because there is no unique index on (id_user,id_user).


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key error

[Clemens Ladisch]

J Decker wrote:
>  CREATE TABLE `option4_name` (`name_id` char(36) NOT NULL,`name`
> varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE)

> FOREIGN KEY (`name_id`) REFERENCES `option4_name`(`name_id`)

> foreign key mismatch - "option4_map" referencing "option4_name"

name_id must be the primary key, or at least have a unique
constraint/index.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe sqlite over remote filesystem?

[Clemens Ladisch]

Wout Mertens wrote:
> I know that multi-writer sqlite and NFS don't play well with each other.
>
> However, I wonder if some constraints could be added that would make this
> situation safe.

NFS locking implementations tend to be bad.  However, there are other file
operations that are properly synchronized, e.g., it is not possible for two
NFS clients to create a directory with the same name.  So as long as all
programs that access the database cooperate, they can switch to a different
locking implementation, such as the unix-dotfile VFS:

https://www.sqlite.org/vfs.html#standard_unix_vfses

Note: this makes all accesses, even reads, take an exclusive lock.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Safe sqlite over remote filesystem?

[Clemens Ladisch]

Simon Slavin wrote:
> On 14 Aug 2018, at 3:09pm, Clemens Ladisch  wrote:
>> However, there are other file
>> operations that are properly synchronized, e.g., it is not possible for two
>> NFS clients to create a directory with the same name.
>
> You are correct.  But there's still a problem with simulating a mutex system.
>
> Suppose two computers try to create a remove folder with the same name at the
> same time.  The request which is processed first gets "completed 
> successfully".
> If everything is done correctly, the second request would get "folder with 
> that
> name already exists".  But since the job has been done (a folder with that 
> name
> does exist, so the task must have been completed, so the programmer should be
> happy) the second request is quite likely to get "completed successfully" too.

In practice, NFS implementations do report this error correctly.

> It takes a very fussy filesystem programmer to make their code report "the
> thing you asked for is complete as you requested, but I didn't do it".

EEXIST is just one of many error codes that is simply passed through from the
real file system.  An NFS server would have to go out of its way to change this
error into something else.

And while trying to be 'clever' with locking could give performance gains, no
such benefit exists for mangling the mkdir() result.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA case_sensitive_like

[Clemens Ladisch]

Chris Locke wrote:
> Is there a reason for it being write only?

This pragma just installs a different LIKE() function, and there is no easy
mechanism to read the function pointer back.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Invalid Blob Length Error Message

[Clemens Ladisch]

Casey Rodarmor wrote:> Hi all,
> unrecognized token: "x'01234'
>
> I know now that the problem was that the blob's length was not a
> multiple of two. However, the error message didn't give me an
> indication of that. Would it be possible to make the error message
> more explicit, to help users debug this error?

Blob literals are parsed in sqlite3GetToken():

case CC_X: {
#ifndef SQLITE_OMIT_BLOB_LITERAL
  testcase( z[0]=='x' ); testcase( z[0]=='X' );
  if( z[1]=='\'' ){
*tokenType = TK_BLOB;
for(i=2; sqlite3Isxdigit(z[i]); i++){}
if( z[i]!='\'' || i%2 ){
  *tokenType = TK_ILLEGAL;
  while( z[i] && z[i]!='\'' ){ i++; }
}
if( z[i] ) i++;
return i;
  }
#endif

At the moment, this function has no mechanism to return an error message;
the only return value is TK_ILLEGAL (resulting in "unrecognized token").

It would be possible to add another parameter, or to introduce several
different TK_ILLEGAL_xxx codes.  However, blob literals are almost never
written by hand, so I doubt that the additional code and maintenance
effort are deemed worth the effort.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why trigger (UPDATE) does not work?

[Clemens Ladisch]

Csányi Pál wrote:
> CREATE TRIGGER YearsRemainingMonthsDays AFTER UPDATE OF EndDate ON 
> MyLengthOfService BEGIN UPDATE MyLengthOfService SET Years = (  with ...

 says:
| The WITH clause cannot be used within a CREATE TRIGGER.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strategies to reduce page-loads?

[Clemens Ladisch]

t...@qvgps.com wrote:
> The larger the db, the slower is the fetching!
>
> My assumption is, that in the big db, these 1000 lines are just spread over a 
> much higher count of pages.
> So more page-loads resulting in more time.

Correct.

> We changed page_size to the maximum value of 64k and it became much better, 
> but still I would lke to improve it.

Store the table entries so that nearby objects are stored nearby.  One
way to do that would be to insert the ways ordered first by Z, then by
the position on a space-filling curve (e.g., Morton order
).


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_* with error handling

[Clemens Ladisch]

Keith Medcalf wrote:
> 6)  If the column type is SQLITE_BLOB
> a)  Retrieve the column value pointer using column_blob
> b)  If the returned pointer is NULL, then an error has occurred

"The return value from sqlite3_column_blob() for a zero-length BLOB is a NULL 
pointer."


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_* with error handling

[Clemens Ladisch]

Keith Medcalf wrote:
> In the case of a BLOB if a NULL pointer is returned the error code must
> be retrieved and then if and only if the bytes counter is greater than
> 0 is the error valid.
>
> Does this mean that if you are retrieving the value of a blob via the
> colmn_blob interface you should ask for the column_bytes first and only
> expect a NULL pointer if the bytes count is zero?

For detecting errors, it does not really matter whether you call _blob
or _bytes first.  (A zeroblob might need to be expanded in the _blob
call, but it does not matter when exactly this error happens.)

In many cases, your code will copy the value elsewhere, so it will be
necessary to call _bytes first to allocate the buffer.

If you do not check the type beforehand, _blob must be called first
because the possible type conversion might change the size.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign_keys = 0N with Entity Framework 6.2.0

[Clemens Ladisch]

Urs Wagner wrote:
> Simon Slavin:
>> On 12 Sep 2018, at 2:04pm, Urs Wagner  wrote:
>>> The following code is returning 0. Why?
>>>
>>> ExecuteStoreQuery("PRAGMA foreign_keys;").First();
>>
>> You coerce the result of the call into an integer.  Can you make the call 
>> and display (or use a debugger to see) exactly what it's returning ?
>
> The result cannot be coerced into a string (compiler error).

How exactly did you try that?

Anyway, how many rows does the corresponding EXPLAIN return, two or three?

 sqlite> explain pragma foreign_keys = on;
 addr  opcode p1p2p3p4 p5  comment
   -        -  --  -
 0 Init   0 1 000  Start at 1
 1 Expire 0 0 000
 2 Halt   0 0 000
 sqlite> explain pragma do_what_i_want;
 addr  opcode p1p2p3p4 p5  comment
   -        -  --  -
 0 Init   0 1 000  Start at 1
 1 Halt   0 0 000


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign_keys = 0N with Entity Framework 6.2.0

[Clemens Ladisch]

Urs Wagner wrote:
> I get three lines

With some other tool, or executed through the EF?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to import TSV table with double quote in it without having to escape double quote

[Clemens Ladisch]

Peng Yu wrote:
> I don't want to escape the quote charaters in the input. Is there still
> a way to import quote characters into a sqlite3 table?

The CSV import hardcodes " as quote.

You could try the ASCII import instead.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: Window functions in VIEWs broken in 3.25.1

[Clemens Ladisch]

Bjoern Hoehrmann wrote:
>   Using the sqlite-tools-linux-x86-3250100 Linux binaries I find that
> Window functions in VIEWS behave differently from PostgreSQL 9.6 and
> from what I expect.
>
>   DROP TABLE IF EXISTS example;
>   CREATE TABLE example(t INT, total INT);
>   INSERT INTO example VALUES(0,2);
>   INSERT INTO example VALUES(5,1);
>   INSERT INTO example VALUES(10,1);
>
>   DROP VIEW IF EXISTS view_example;
>   CREATE VIEW view_example AS
>   SELECT
> NTILE(256) OVER (ORDER BY total) - 1 AS nt
>   FROM
> example
>   ;
>
>   SELECT * FROM view_example;
>
> In SQLite 3.25.1 I get 0, 0, 0

The EXPLAIN output shows that the optimizer ended up generating
a program for "SELECT 1 - 1 FROM example".

> while PostgreSQL 9.6 gives 0, 1, 2.

And the same query outside a view gives the correct ouput.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite 3.25.1 windows function. So it should be?

[Clemens Ladisch]

Djelf wrote:
> INSERT INTO t (v1,v2,v3,v4) VALUES
> (1,1,1,0),(1,1,0,1),(2,1,0,10),(3,1,0,100),(3,1,0,1000);
>
> SELECT
>   v1,v2,sum(v3+v4) OVER (PARTITION BY v2 ORDER BY v1) as val
> FROM t
> GROUP BY v1,v2;
>
> v1v2  val
> 1 1   1
> 2 1   10010
> 3 1   10110
>
> Yes, I know that v1 is duplicated, but whether the result should disappear
> 10011?

Looks correct.  The Postgres manual explains it this way:
> If the query contains any window functions, these functions are
> evaluated after any grouping, aggregation, and HAVING filtering is
> performed. That is, if the query uses any aggregates, GROUP BY, or
> HAVING, then the rows seen by the window functions are the group rows
> instead of the original table rows from FROM/WHERE.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite 3.25.1 windows function. So it should be?

[Clemens Ladisch]

Djelf wrote:
> SQLSTATE[42803]: Grouping error: 7 ERROR:  column "t.v3" must appear in the 
> GROUP BY clause or be used in an aggregate function
>
> It seems to me that sqlite should issue a similar message.

This is allowed for compatibility with MySQL.
And there is a case with min()/max() where this is actually useful:



Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_get_table() failed with error "database is locked"

[Clemens Ladisch]

ldl wrote:
> Multiple applications called sqlite3_get_table() to read data in a same db 
> file at the same time, got error "database is locked"

Somebody has a write lock.

> Why read-only access is locked?

Because one access is not read only.

> How to resolve the issue?

Find out who locked it.
Do you know which processess could access the DB file?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Calling sqlite3_create_module from a DLL

[Clemens Ladisch]

Deon Brewis wrote:
> I have a DLL that makes a series of sqlite3_create_function_v2 calls.
> It all works fine and the DLL is usable.
>
> I've tried adding a sqlite3_create_module into the same DLL, but I get
> an assert in:
>
> sqlite3_mutex_try
> over here:
> assert( sqlite3GlobalConfig.mutex.xMutexTry );
>
> xMutexTry (really all the .mutex callbacks) are null.
>
> I'm trying to call create_module during my sqlite3_extension_init
> export, directly after my call to SQLITE_EXTENSION_INIT2(pApi);

Apparently, you did not call sqlite3_initialize().
 says:
| For maximum portability, it is recommended that applications always
| invoke sqlite3_initialize() directly prior to using any other SQLite
| interface.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .separator and .mode commands interactions

[Clemens Ladisch]

Luc Charansonney wrote:
> sqlite> .separator tabs

  sqlite> select 1, 2;
  1tabs2

> sqlite> .import mydata.txt mytable
> Error: multi-character column separators not allowed for import

You should have used ".separator \t".

> So I fall back on my feet by using .mode instead of .separator:
> sqlite> .mode tabs
> sqlite> .import mydata.txt mytable

The .mode command expects a name, but .separator the actual characters.

The modes have more differences than the separators, but that's mostly
for output.  As far as .import is concerned, the only mode with special
behaviour is "ascii" (which does not use CSV quoting rules).


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Filtering groups by non-grouped field.

[Clemens Ladisch]

John Found wrote:
> i.e. how to select only the groups that contain
> some value in the set of values in a column not
> specified in group by clause.
>
> select
>   (select group_concat(b) from t t1 where t1.a = t2.a) as list
> from t t2
> where b = ?1;

Similarly:

  select
group_concat(b) as list
  from t
  where a in (select a
  from t
  where b = ?1)
  group by a;

But you will not be able to avoid the subquery: the filter has to
include all other rows of the group with matching b, and after the
group_concat(), the result is no longer in a form useful for lookups.
(And doing the filter after the grouping is probably not efficient.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Filtering groups by non-grouped field.

[Clemens Ladisch]

John Found wrote:
> Clemens Ladisch  wrote:
>>   select
>> group_concat(b) as list
>>   from t
>>   where a in (select a
>>   from t
>>   where b = ?1)
>>   group by a;
>>
>> But you will not be able to avoid the subquery: the filter has to
>> include all other rows of the group with matching b, and after the
>> group_concat(), the result is no longer in a form useful for lookups.
>
> Hm, is sounds strange because when HAVING clause is processed,
> the aggregate functions should not be processed yet (for a performance
> reasons) i.e. the query still has access to all values from the field b
> and theoretically should be able to search these values the same way
> it searches them on executing min() or max() aggregate functions.

Yes, the HAVING clause can run any aggregate function.  But you need to
find some aggregate function that can do the filtering.  PostgreSQL
would have "HAVING array_position(array_agg(b), $1)", but I have not
been able to get SQLite's json_group_array() to work with this.

And "',' || group_concat(b) || ',' LIKE '%,' || ?1 || ',%'" would be
horrible.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Filtering groups by non-grouped field.

[Clemens Ladisch]

I wrote:
> But you need to find some aggregate function that can do the filtering.

HAVING SUM(b = ?1)

(In a database with proper booleans: "HAVING SUM(CASE WHEN b = ? THEN 1 END) > 
0")


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Filtering groups by non-grouped field.

[Clemens Ladisch]

E.Pasma wrote:
> select group_concat(b) as list
> from t
> group by a
> having count(b=?1)
> ;

In SQLite, a boolean expression returns 0 when false, and count(0) is 1.
You have to generate a NULL for failed matches, or use another function
like sum() or max() that can filter out zeros.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Filtering groups by non-grouped field.

[Clemens Ladisch]

John Found wrote:
> Also, it seems max(b = ?1) will do the trick as well as count(b = ?1)
>
> And here another question appears. What is more efficient?

In SQLite, both are equally efficient.

Use whatever makes the query easier to understand.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why operations with in-memory DB are much faster than with on-disk DB?

[Clemens Ladisch]

Yuri wrote:
> I noticed that my DB import process is much slower when run on the DB on 
> disk, vs. in memory.

It's possible that you forgot to wrap a single transaction around all changes.
Otherwise, you get an automatic transaction for every command, which requires
disk synchronization every time.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exception handling during create table from a virtual one

[Clemens Ladisch]

Max Vlasov wrote:
> I have a virtual table that raises an unhandled exception during a create
> table

SQLite is written in C.  The C language does not have exceptions.

> Аfter the exception is thrown 
>  If I continue the execution

You must return from the callback function normally, or abort the process.
Anything else will corrupt SQLite's internal state.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help!

[Clemens Ladisch]

am...@juno.com wrote:
> how do I make a field wrap the text. In other words, rather than having
> the text in a field keep going from right to left, when I hit the right
> hand margin, how do I make the text automatically go to the next line.

The purpose of SQLite is to store data, and to give it back to you
unchanged.  How the data is formatted for displaying is not the database's
concern.

It is likely that the software that you're using to display fields has an
option for automatic word wrapping, but nobody here knows what software
that is.

> how do I simultaneously nest two or more fields. For instance, if I have
> four fields: name, department, clock number, department, how do I nest so
> that I alphabetically, sort by the clock number, then last name within
> each department, and ten clock number within in each name?

Just put the column names in this order in the ORDER BY clause when doing
the SQL query.  (Assuming that whatever software you're using allows you
to customize the query.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] curious discovery about geopoly module

[Clemens Ladisch]

Graham Hardman wrote:
> SQLiteForExcel [1] https://github.com/govert/SQLiteForExcel

"sqlite3.dll is a copy of SQLite version 3.11.1"

> "no such module: geopoly"
>
> I guess that makes sense in one way, but it begs the question of why the
> shell and my version of SQLiteExpert find a way to understand what is
> required.

Because they updated their copy of SQLite more recently than three years ago.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Displaying row count

[Clemens Ladisch]

David Fletcher wrote:> Hi all,
> Is there a mode in the sqlite shell, or some fancy extension, that will 
> display a row
> number when outputting results?

No.  You'd have to modify the shell, or add the row_number() window function to 
the
query.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] forming sqlite3_statements using stored sql

[Clemens Ladisch]

David Fletcher wrote:
> create table if not exists StmtSQL (
> StmtNameTEXT NOT NULL UNIQUE,
> SQL  TEXT NOT NULL);
>
> The GetPreparedStmt() function retrieves the SQL from table, creates a new
> sqlite3_statement object (or retrieves this from a cache).
>
> It  strikes me that this would be nicer if sqlite offered this as an 
> intrinsic capability.

How would SQLite know what the table and column names are?  How would that API
be different from exec("SELECT SQL From StmtSQL WHERE StmtName = ?")+prepare?

> people might find it easier to share collections of SQL statements designed
> for various tasks.

This would require all databases to have the same schema, or some automatic
mechanism to adjust table/column names.  And getting the table/column names
right is the smallest problem when writing a query (or choosing which one to
copy).

I don't see how this would be an improvement over the NoSQL database commonly
known as Stack Overflow.  :)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help!

[Clemens Ladisch]

am...@juno.com wrote:
> how to take a database in database (not structure) form--and copy and paste 
> it into an e-mail

A database file is binary; the easiest way would be to attach it.

If you want to paste it into the e-mail itself, you have to convert it to text 
somehow.
Either create a bunch of SQL commands (which includes all the data):

  $ sqlite3 test.db .dump
  PRAGMA foreign_keys=OFF;
  BEGIN TRANSACTION;
  CREATE TABLE t(x);
  INSERT INTO t VALUES('hello');
  INSERT INTO t VALUES('world');
  CREATE INDEX tx on t(x);
  COMMIT;

Or create a hexdump of the entire file:

  $ od -Ax -tx1 < test.db
  00 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00
  10 10 00 01 01 00 40 20 20 00 00 00 04 00 00 00 03
  20 00 00 00 00 00 00 00 00 00 00 00 02 00 00 00 04
  30 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00
  40 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
  50 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 04
  60 00 2e 28 6a 0d 00 00 00 02 0f b7 00 0f df 0f b7
  70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
  *
  000fb0 00 00 00 00 00 00 00 26 02 06 17 11 0f 01 3b 69
  000fc0 6e 64 65 78 74 78 74 03 43 52 45 41 54 45 20 49
  000fd0 4e 44 45 58 20 74 78 20 6f 6e 20 74 28 78 29 1f
  000fe0 01 06 17 0f 0f 01 2f 74 61 62 6c 65 74 74 02 43
  000ff0 52 45 41 54 45 20 54 41 42 4c 45 20 74 28 78 29
  001000 0d 00 00 00 02 0f ee 00 0f f7 0f ee 00 00 00 00
  001010 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
  *
  001fe0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 07 02
  001ff0 02 17 77 6f 72 6c 64 07 01 02 17 68 65 6c 6c 6f
  002000 0a 00 00 00 02 0f ed 00 0f f7 0f ed 00 00 00 00
  002010 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
  *
  002fe0 00 00 00 00 00 00 00 00 00 00 00 00 00 09 03 17
  002ff0 01 77 6f 72 6c 64 02 08 03 17 09 68 65 6c 6c 6f
  003000

The biggest problem is that the recipient has to know how to turn that back
into a database.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] HELP!

[Clemens Ladisch]

am...@juno.com wrote:
> I work for a company that has a many locations with more than one
> person in every location. I want to share the databases I have built
> using SQLITE with some of the people in each location. Do any of you
> good people know is SQLITE on the cloud?

It's not; SQLite is file based.  The only way to share this would be to
make a file share in the company-wide network, i.e., to make the file
\\COMPANYSERVER\SomeShare\MyLittleDB.sqlite directly accessible from
everywhere.  (This is likely to be inefficient.)

Consider replacing SQLite with a client/server database:



Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Library Files on my Computer

[Clemens Ladisch]

Giovanni Giusti wrote:
> they are offsite

What do you mean with that?  Do you get an error message when you try to access 
them?

> and end in .sqlite-wal

If there is no corresponding .sqlite file (without the "-wal"), then those 
files are
not actual SQLite database files.


Regards,
Clemens

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Clemens Ladisch]

Dominique Devienne wrote:
> I'd like an official stance on SQLite itself please.




Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] i Know i should use 'AS', but ....

[Clemens Ladisch]

Luuk wrote:
>sqlite> .mode column
>sqlite> .headers on
>sqlite> select 1 as X,date() as d union all select 2,date() union all 
>select 3,datetime();
>X   d
>--  --
>1   2018-12-25
>2   2018-12-25
>3   2018-12-25

The value is longer than the column with (which is probably based on the
values in the first line).

Try ".width 10 20".


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ROLLBACK TO stmt within committed transaction cause write operation to the WAL file.

[Clemens Ladisch]

sanhua.zh wrote:
>BEGIN IMMEDIATE;
>SAVEPOINT s1;
>INSERT INTO t VALUES(2);
>ROLLBACK TO SAVEPOINT s1;
>COMMIT;

>SOMETHING appended into the end of WAL file.
>BUT why? We should have nothing to write.

It's information about the WAL file itself.

The WAL file must be initialized at some time, so it might as well be
done at the start of a write transaction.
(I guess a write transaction that does not actually write anything does
not happen often enough to be worth optimizing for.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite trig/geometry error

[Clemens Ladisch]

Brent Wood wrote:
> Is there an easy way to tell where the sqlite math functions are coming from?

Are you using the sqlite3 command-line shell, or something else?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE 3.26.0 compiler warning

[Clemens Ladisch]

Gary Sanders wrote:
> shell.c(16466): warning C4996: 'strdup': The POSIX name for this item is 
> deprecated.

strdup() is now in the dynamic memory TR:
.

The 'correct' way to get it is:

 #ifdef __STDC_ALLOC_LIB__
 #define __STDC_WANT_LIB_EXT2__ 1
 #else
 #define _POSIX_C_SOURCE 200809L
 #endif
 #include 
 ...


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Planning Knowledge

[Clemens Ladisch]

Andy Bennett wrote:
> I could use the inner join for the "entrys" join and the "items" join
> but not the "entry-items" join because each entry can have more than
> one item.

  WITH a(id, name) AS (VALUES (1, 'A')),
   b(id, name) AS (VALUES (1, 'B1'), (1, 'B2'))
  SELECT * FROM a INNER JOIN b USING (id);

  1|A|B1
  1|A|B2

The only difference between inner and outer joins is how rows without
any match are handled.

> I started with an OUTER JOIN as I find it easier to show that it's
> doing the correct thing because I can search the output for errant
> NULLs. Trying to detect missing rows in an INNER JOIN is harder.

If the join columns have the same name, using USING is easier.

And it would be a good idea to enforce the relationships between the
tables with foreign key constraints: 
(However, constraints do not affect how you have to write your queries.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Planning Knowledge

[Clemens Ladisch]

Andy Bennett wrote:
>> foreign key constraints
>
> my experience with other engines taught me that it makes experimenting at the 
> monitor harder.

Then don't use them. :)  But do you actually want 'wrong' data?

> Are there any efficiency benefits or is it just there to enforce data 
> integrity?

Constraints just are additional checks.
(FKs require certain indexes, but you would want to have those anyway.)

> It looks like they have to be enabled on a per connection basis. In this case 
> I (currently)
> control all the client code but is it possible for the foreign key 
> relationships to get out
> of sync if one of the connections omits to apply the pragma?

Yes.  You could run PRAGMA foreign_key_check afterwards.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite linked to plugin binary

[Clemens Ladisch]

Carsten Müncheberg wrote:
> A host application (which is a black box for me I cannot change) is loading
> my plugin binaries which each have SQLite linked statically. When two
> plugins now access the same database file from the process of the host
> application none of the serialization mechanisms (file lock, mutex) works,

Not even unix-dotfile?

> my question is whether anyone of you can think of a solution for this
> unfortunate problem I am stuck with.

You could write your own VFS, with some other locking mechanism.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode readonly errors to SELECT statements

[Clemens Ladisch]

Robert Searle wrote:
> We have recently started trying to provide read-only access to the database
> (service run as user with group/other read access permissions under Linux,
> service not database owner) and occasionally get either
> SQLITE_READONLY_RECOVERY or SQLITE_READONLY_CANTINIT responses

 says:
| The SQLITE_READONLY_RECOVERY error code indicates that a WAL mode
| database cannot be opened because the database file needs to be
| recovered and recovery requires write access but only read access is
| available.

| The SQLITE_READONLY_CANTINIT result code originates in the xShmMap
| method of a VFS to indicate that the shared memory region used by WAL
| mode exists buts its content is unreliable and unusable by the current
| process since the current process does not have write permission on
| the shared memory region.

> 1) Should we treat these responses as an invitation to retry later rather
> than asserts?

Waiting might work if some other process opens the database and actually
does the recovery.

> 2) Do these responses indicate that the variable(s) requested in the select
> have not been returned?

Error codes indicate that the call failed.  The query did not even begin
to execute.

> 3) Are there any configuration settings on the database that might reduce
> the probability of occurrence?

Open the database with write access (so that recovery can be done), but set
PRAGMA query_only.

> 4) If there aren't any configuration settings, are there any usage patterns
> to avoid or to embrace?

Don't corrupt the database in the first place.  ;-)
You aren't using WAL over a network, or across a VM boundary, are you?

Normally, recovery is needed if some writer crashes.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Displaying hierarchical structure

[Clemens Ladisch]

Bart Smissaert wrote:
> ID PARENT_ID FOLDER RANK
> ---
> 1   0   Main1
> 2   1   CC   1-02
> 3   1   BB   1-03
> 4   1   AA   1-04
> 5   2   B 1-02-05
> 6   2   A 1-02-06
>
> What SQL should I use to update the field RANK if the first row is known to
> be 01, but all the next rows are null? I tried with a non-recursive 
> query,
> but couldn't work it out.

You want to append an entry's rank to its parent's rank, but only for
entries whose rank is still empty, and whose parent has a rank:

   UPDATE MyTable
   SET Rank = (SELECT Rank
   FROM MyTable AS Parent
   WHERE MyTable.Parent_ID = Parent.ID
  ) || printf('-%09d', ID)
   WHERE Rank IS NULL
 AND Parent_ID IN (SELECT ID
   FROM MyTable
   WHERE Rank IS NOT NULL);

Repeat until no empty rows are left.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Checking differences in tables

[Clemens Ladisch]

Jose Isaias Cabrera wrote:
> t_20190208 (a PRIMARY KEY, b, c, d, e).
>
> I create a new table,
>
> t (a PRIMARY KEY, b, c, d, e)
>
> and insert a set of "new data", which contains changes that happened since 
> yesterday
> after the new set of data was created.  Right now, I bring the data out into 
> two arrays
> and check for the data outside SQLite by iterating through the fields and 
> checking for
> differences, one record at a time, but is there an easier or simpler way 
> using SQLite
> commands?

This query returns all rows that are new or changed:

  SELECT * FROM t
  EXCEPT
  SELECT * FROM t_20190208;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite 3.37.1: void function returns value

[Clemens Ladisch]

Peter da Silva wrote:
> I am pretty sure that the code is not legal C

Indeed; C99 and C11 say in 6.3.2.2:
| The (nonexistent) value of a void expression (an expression that has
| type void) shall not be used in any way [...]
and in 6.8.6.4:
| A return statement with an expression shall not appear in a function
| whose return type is void.

(And it has already been fixed two hours ago.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Choosing one value from the group with priority

[Clemens Ladisch]

Ivan Krylov wrote:
> select * from test where id in (1,2) group by id;

Please note that this is not standard SQL; SQLite allows to SELECT
columns that are not mentioned in the GROUP BY clause, but they get
their values from a random row in the group.


> but then I don't get to control which source I'm obtaining the values
> from (when there is more than one). Let's assume for now that I prefer
> to choose values with a particular source_id, but if those are not
> present, I would take what's available.

There is another SQLite extension which allows to select a row in the
group by using MAX() or MIN():

  select *, min(abs(source_id - 3)) from test where id in (1,2) group by id;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve tables accessed by query

[Clemens Ladisch]

Zach Wasserman wrote:
> Is anyone aware of an API I can use to determine which tables are accessed
> by a given query?

https://www.sqlite.org/c3ref/set_authorizer.html


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Clemens Ladisch]

Rocky Ji wrote:
> But everyone advices against nested select statements.

Who?

I've heard rumors that older version of the Oracle query optimizer did
worse with subqueries than with joins, but such advice is not necessarily
correct for SQLite.

SQL is set-based language, and queries that are written this way are
often easier to understand and maintain.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Clemens Ladisch]

Rocky Ji wrote:
> CREATE TABLE Aliases (
>   alias_id INTEGER PRIMARY KEY AUTOINCREMENT,
>   real_name TEXT NOT NULL,
>   aka TEXT NOT NULL,
>   CONSTRAINT xyz UNIQUE (real_name, aka),
>   CONSTRAINT noCircularRef_A CHECK (
> real_name NOT IN (SELECT aka FROM Aliases)
>   ),
>   CONSTRAINT noCircularRef_B CHECK (
> aka NOT IN (SELECT real_name FROM Aliases)
>   )
> );
>
> Error: no such table: Aliases

 says:
| The expression of a CHECK constraint may not contain a subquery.

You'd have to write triggers to check this:

CREATE TRIGGER noCircularRef_insert
AFTER INSERT ON Aliases
FOR EACH ROW
WHEN NEW.real_name IN (SELECT aka FROM Aliases)
  OR NEW.aka IN (SELECT real_name FROM Aliases)
BEGIN
  SELECT RAISE(FAIL, "circular reference");
END;
-- same for AFTER UPDATE OF real_name, aka


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BigInt loss accuracy

[Clemens Ladisch]

Derek Wang wrote:
> sqlite biggest int is supposedly 9,223,372,036,854,775,807 (9 and a bit
> Quintillion), but when the number is larger than 1E+17, it loses some
> accuracy when retrieving.

In plain SQL, everything works fine up to the limit:

  create table t(i notoriously big integer);
  with recursive b(i) as (values (9223372036854775803) union all select i+1 
from b limit 10) insert into t select i from b;
  select i from t;

  9223372036854775803
  9223372036854775804
  9223372036854775805
  9223372036854775806
  9223372036854775807
  9.22337203685478e+18
  9.22337203685478e+18
  9.22337203685478e+18
  9.22337203685478e+18
  9.22337203685478e+18

Same in plain Python, when using %s formatting:

  import sqlite3
  db=sqlite3.connect("':memory:")
  db.execute("create table t(i notoriously big integer)")
  for i in [10**17, 10**17+3, 10**18, 10**18+3, 10**19, 10**19+3]:
db.execute("insert into t values(%s)" % (i,))
  for row in db.execute("select i from t"):
print(row[0])

  10
  13
  100
  103
  1e+19
  1e+19

In any case, when using properly parameterized commands, you will not be
able to insert values that are too large:

  db.execute("select ?", (9223372036854775807,)).fetchall()

  [(9223372036854775807,)]

  db.execute("select ?", (9223372036854775808,)).fetchall()

  Traceback (most recent call last):
File "", line 1, in 
  OverflowError: Python int too large to convert to SQLite INTEGER


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key constraint fails due to unrelated field

[Clemens Ladisch]

Jonathan Moules wrote:
> UPDATE lookups set error_code=3 and exp_content_type='ogc_except' WHERE 
> content_hash = '0027f2c9b80002a6';

This fails because "3 and exp_content_type='ogc_except'" is interpreted as
a boolean expression.

To update multiple fields, separate them with commas:

  UPDATE lookups set error_code=3, exp_content_type='ogc_except' WHERE ...
 ^

> UPDATE lookups set error_code=3 and WHERE content_hash = '0027f2c9b80002a6';

This is not valid SQL ("and WHERE").


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive CTE on tree with doubly linked items

[Clemens Ladisch]

heribert wrote:
> I've a tree with doubly linked items. I want to get all siblings of a tree 
> node.

If you want them in order, you have to walk through the linked list:

WITH SiblingsOf3 AS (
  SELECT *
  FROM Tree
  WHERE ParentIDX = (SELECT ParentIDX
 FROM Tree
 WHERE ID = 3)
AND PrevIDX IS NULL

  UNION ALL

  SELECT Tree.*
  FROM Tree
  JOIN SiblingsOf3 ON SiblingsOf3.NextIDX = Tree.ID
)
SELECT * FROM SiblingsOf3;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Developer questions about the Online Backup API

[Clemens Ladisch]

Simon Slavin wrote:
> If the source database is changed while the Online Backup API is
> running, it returns to the beginning of the database and starts again.

The backup API must create a consistent snapshot of the source database,
i.e., the result must be the exact state at some point in time when no
write transaction was active.

> 1) Suppose the first page of the source database which is modified is
>after the point that the backup has reached.  Is it necessary to
>restart ?
> 2) Suppose the first page of the source database which is modified is
>before the point that the backup has reached.  Could the backup not
>return just to that point rather than to the very beginning ?

In the general case, it is not possible to detect which pages have been
changed.

> ... if the database is changed only by the same connection as it
> performing the backup

This would require additional code to track changed pages, and a lock to
prevent other connections from making changes.


If all connections are on the same machine, it should be possible to use
WAL mode.  You can then do the entire backup in a single step without
blocking writers.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug report: UPSERT / INSERT ON CONFLICT PK Autoincrement

[Clemens Ladisch]

Stanislav Zabka wrote:
> When conflict occurs, no import performs, but PK is incremented nevertheless.

 says:
| Note that "monotonically increasing" does not imply that the ROWID
| always increases by exactly one. One is the usual increment. However,
| if an insert fails due to (for example) a uniqueness constraint, the
| ROWID of the failed insertion attempt might not be reused on
| subsequent inserts, resulting in gaps in the ROWID sequence.
| AUTOINCREMENT guarantees that automatically chosen ROWIDs will be
| increasing but not that they will be sequential.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] filling a key/value table

[Clemens Ladisch]

Simon Slavin wrote:
> I wanted to speak against including a BLOB field in a compound PRIMARY KEY.

That depends on the size of the blob.  If it contains 'normal'-sized values,
it's just as efficient as other types.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Clemens Ladisch]

Peng Yu wrote:
> There are cid's for each table. Is there a way to use "select" with
> cid's instead of their names?
>
> select * from pragma_table_info('test');
> cid nametypenotnull dflt_value  pk
> --  --  --  --  --  --
> 0   id  integer 0   1
> 1   value   text0   0

This cid value is generated on the fly by table_info, and not used anywhere
else.

You would have to run "SELECT name FROM pragma_table_info(...) WHERE cid = ?"
and then construct the actual SQL query with the returned column name.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clear sqlite3 terminal enhancement

[Clemens Ladisch]

Jeffrey Walton wrote:
> When working in the Linux terminal we can clear the scrollback with
> the 'clear' command; and we can delete all history and scrollback with
> the 'reset' command. I am not able to do the same within the sqlite3
> terminal.

Those are programs run from the shell.  So you can use ".shell clear" or
".shell reset".


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] export tables

[Clemens Ladisch]

Mohsen Pahlevanzadeh wrote:
> I need to export some tables with dot command, How I do it?

https://www.sqlite.org/cli.html#csv_export


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use ORDER BY on FTS5 table ?

[Clemens Ladisch]

PM Nik Jain wrote:
> A SCAN is being performed on a fts5 table. I am not sure but I
> think that means no index.
>
> sqlite>  explain query plan  select * from productsfts p where p.attributes 
> match '50'limit 6;
> `--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1:

Everything except "INDEX 0" means that it is not a plain table scan,
but that the virtual table module does its own filtering.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with sqlite3_value_text

[Clemens Ladisch]

x wrote:
>> As long as you use _value_bytes after _text you're fine... so if any
>> conversion did take place the value will be right of the last returned
>> string type.
>
> Could you explain that to me? I’m not sure why any conversion takes place
> and, on reading the text below, I would’ve thought it would be better to
> call sqlite3_value_bytes first

As shown in the table, conversion from TEXT to BLOB does not change anything.
However, conversion from BLOB to TEXT might require appending a zero terminator.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row values with IN

[Clemens Ladisch]

Simon Slavin wrote:
> I think that the documentation doesn't sufficiently explain the problem.
> Or maybe the use of IN or lists deserves its own page rather than being
> buried in the extremely long page on expressions.

 says:
| For a row-value IN operator, the left-hand side … can be either
| a parenthesized list of values or a subquery with multiple columns. But
| the right-hand side … must be a subquery expression.

I'd guess this restriction makes parsing easier.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Read/Write cycle

[Clemens Ladisch]

manojkumar schnell wrote:
> What is the maximum read/write cycle?

The database puts no limit on how often you can read or update data.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with single writer on Windows network share

[Clemens Ladisch]

Simon Slavin wrote:
> setting the journal mode of the database to WAL will

... certainly lead to data corruption; WAL requires shared memory, which
cannot work over a network filesystem.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with single writer on Windows network share

[Clemens Ladisch]

Andrew Moss wrote:
> ... an SQLite database hosted on a windows network share (using server
> 2012 R2 or later). We are well aware this is not advisable

There are three possible sources of network filesystem data corruption:

1) Bad locking implementations.  Some Unix-y network filesystems
   products allow to disable locking (in a misguided attempt to increase
   performance), or do not implement locking at all.

   This can happen not only for pure Unix implementations, but also for
   NFS-on-Windows products, and SMB-on-Unix products.  Pure Windows,
   however, is safe from this.  (Please note that most NAS devices are
   not pure Windows.)

2) Bugs.  Older Windows versions have had some bugs, but those were
   fixed, so if you're running the latest updates, you are safe.

3) Intermittent network faults.  Windows can use oplocks (opportunistic
   locks), where the server allows the clients to cache changed data
   locally, even after the lock has been released, and asks for that
   data only when some other client want to read it.  This breaks if
   a network fault prevents the client from sending the changed data to
   the server, and, after a timeout, the server assumes that the client
   has crashed.  In that case, the data could be inconsistent because
   other changed data did make it, or because the changed data will be
   sent to the server too late (when the network works again).

   TCP/IP is more robust than the NetBEUI/NBF protocol uses by older
   Windows versions, so this problem should be much less likely nowadays.

   Oplocks will not bring a performance improvement if changed data is
   likely to be read by other clients anyway.  So in this case, you
   might want to disable them:
   
https://support.microsoft.com/en-us/help/296264/configuring-opportunistic-locking-in-windows

> My question is, if we limit the application (through other means) to a
> single writer, but allow multiple readers, does that remove the risk of
> database corruption from multiple SQLite processes?

Locks are also used to coordinate between readers and writers, so this
does not actually reduce the risk by much.

> Any notes from other users who had to do something similar in the past?

We have one customer with network-shared SQLite DBs on Windows.  There
are no known problems, but there is very little actual concurrency.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in table_info pragma

[Clemens Ladisch]

Simon Slavin wrote:
> If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to
> evaluate CURRENT_TIMESTAMP, find a string value like
> '2019-05-17 12:10:43', and store that string in the schema.

This keyword behaves magically.  ANSI SQL-92 says:
| The default value inserted in the column descriptor ... is as
| follows:
| Case:
| a) If the  contains NULL, then the null value.
| b) If the  contains a , then
|Case:
|i) If the subject data type is numeric, then the numeric value
|   of the .
| [...]
| d) If the  contains a ,
|then the value of an implicit reference to the .


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in table_info pragma

[Clemens Ladisch]

Simon Slavin wrote:
> On 17 May 2019, at 1:33pm, Clemens Ladisch  wrote:
>> This keyword behaves magically.

... as far as the SQL standard is concerned.

> Mmmm.  In that case, to implement this properly you need to store
> a default-type flag alongside the default value.

The SQLite syntax diagrams treat it as "literal-value":
<https://www.sqlite.org/syntax/column-constraint.html>

SQLite's actual in-memory representation of default values is an expression
tree; _all_ DEFAULT expressions are evaluated lazily.

(SQL-92 does not allow arbitrary expressions as default values.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RFE: allow parameters in PRAGMA statements

[Clemens Ladisch]

Wout Mertens wrote:
> I am using the user_version pragma for implementing an event-handling
> database. I'd like to prepare the statement to update it, e.g. `PRAGMA
> user_version = ?`.
>
> However, sqlite3 won't let me do that, so I just run the text query every
> time with the number embedded.

 says:
| Some pragmas take effect during the SQL compilation stage, not the
| execution stage. This means if using the C-language sqlite3_prepare(),
| sqlite3_step(), sqlite3_finalize() API (or similar in a wrapper
| interface), the pragma may run during the sqlite3_prepare() call, not
| during the sqlite3_step() call as normal SQL statements do. Or the
| pragma might run during sqlite3_step() just like normal SQL statements.
| Whether or not the pragma runs during sqlite3_prepare() or
| sqlite3_step() depends on the pragma and on the specific release of
| SQLite.

This implies that parameters are not available when some pragmas are
executed.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "unable to use function highlight in the requested context" in group by

[Clemens Ladisch]

Damian Adrian wrote:
> While using the FTS5 highlight() function in a group by query like this one:
>
> SELECT
> group_concat(highlight(entries, 1, '>', '<'))
> FROM entries
> WHERE entries MATCH 'an*'
> GROUP BY id;
>
> I get "Error: unable to use function highlight in the requested context".
>
> I have tried various sub-query combinations with the same result;

Because SQLite does subquery flattening and ends up with the same query.

Try this:

SELECT group_concat(details)
FROM (
SELECT
id,
highlight(entries, 1, '>', '<') as details
FROM entries
WHERE entries MATCH 'an*'
LIMIT -1 OFFSET 0-- rule 14 of 
https://www.sqlite.org/optoverview.html#subquery_flattening
)
GROUP BY id;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BLOB and TEXT comparisons

[Clemens Ladisch]

Charles Leifer wrote:
> SELECT SUBSTR(?, 1, 3) == ?
>
> However, if I mix the types, e.g. sqlite3_bind_text("abcde") and
> sqlite3_bind_blob("abc") then the comparison returns False.
>
> Fom a byte-to-byte perspective, this comparison should always return True.
>
> What's going on?

Apparently, not only a byte-to-byte comparison.

Withou affinity, only integer and real values can compare equal:

sqlite> select 1=1.0, 1='1', '1'=x'31';
1|0|0

See .


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determining valid statement placeholders.

[Clemens Ladisch]

test user wrote:
> I want my library to be able to detect the problem programatically.
>
> I think SQLite internally knows how many placeholders are in the query at
> parse time.
>
> My question is how can I get the data via the API

At the moment, there is no such mechanism in the API.

You could parse the output of EXPLAIN (look at the p1 values for
opcode = 'Variable'), but that is not guaranteed to work in future
versions.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does it make sense to COMMIT/ROLLBACK?

[Clemens Ladisch]

Igor Korot wrote:
> If I execute "BEGIN TRANSACTION" and for whatever reason the call will fail
> will I get an error on COMMIT/ROLLBACK execution?

 sqlite> begin immediate;
 Error: database is locked
 sqlite> rollback;
 Error: cannot rollback - no transaction is active


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Poll: Include the recent sqlite3_column_name() fix in the upcoming 3.20.0 release?

[Clemens Ladisch]

Richard Hipp wrote:
> Cons: (5) The change might cause breakage for legacy applications that
> depend on the older (arguably buggy) behavior.  (6) This seems like a
> big change to receive so little beta exposure prior to the official
> release.

I doubt that there are many applications that both depend on the old
behaviour and go to the effort to test SQLite beta versions.

In other words: if some breakage is found, it's very likely to be with
a release anyway.  (In 2018, when Android switches to 3.25.0 ...)

(But I don't know the pre-release snapshot download numbers.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with the new pointer-passing interface

[Clemens Ladisch]

Ulrich Telle wrote:
> In the comment of the sqlite3_bind_pointer() routine I found this note:
>
> The T parameter should be a static string, preferably a string literal.
>
> In my case this is quite cumbersome, since in my wrapper I have to extract
> the value of parameter T from a temporary string object.

 says:
| The "pointer type" string which is the last parameter to each of the
| pointer-passing interfaces should be a distinct, application-specific
| string literal that appears directly in the API call. The pointer type
| should not be a parameter passed in from a higher-level function.

If you think that you really need to get this string from a higher-level
function, offer a mechanism to 'register' this string, i.e., make
a permanent copy that can be referenced by later calls.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE PHP syntax issues - INSERT INTO db

[Clemens Ladisch]

Edmondo Borasio wrote:
> //If I input the $NewID manually as a string (for example '6', *it works
> fine* and updates the db correctly)
> *$query1="INSERT INTO
> Table"."(ID,name,surname)"."VALUES('6','newName','newSurname');"; *
>
> //However if I try to use the $NewID variable from above *it does't
> work*
>
> *$query1="INSERT INTO Table"."(ID,name,surname)"."VALUES(\' ' . $NewID .
> '\','newName','newSurname');"; *

What is the value of $query1?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Clemens Ladisch]

Lars Frederiksen wrote:
> CREATE TABLE gms(
> gms_id INTEGER PRIMARY KEY,
> gms_verb TEXT NOT NULL
> );
>
> FDTable1.Append;
> FDTable1.FieldByName('gms_verb').AsString:= Edit1.Text;
> FDTable1.Post;
>
> But when I put a string in the table 'gms_verb' I only get the primary key
> number - the string is shown like (WIDEMEMO).

In SQLite, all text values are Unicode and can have an arbitrary size, so
dtWideMemo is what FireDAC thinks is the best match.

You could use a different type name, or set up a type mapping:
http://docwiki.embarcadero.com/RADStudio/XE7/en/Using_SQLite_with_FireDAC#SQLite_Data_Types


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Clemens Ladisch]

Lars Frederiksen wrote:
> I have followed 2 tutorials about SQLite, and none of these mentioned the 
> VARCHAR() possibility

Because SQLite pretty much ignores column types.
Interpreting "VARCHAR" this way is how FireDAC does things; you have to look
into the FireDAC documentation.

> But I also realized that it is not possible just to put some greek (unicode) 
> characters into a field. It ends up with a mix of latin chars and 
> questionmarks.
> Is there a (simple) solution on this problem?

Yes: fix the bugs in your code (which you have not shown).


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing searches across several indexes

[Clemens Ladisch]

Wout Mertens wrote:
> I have a table with a bunch of data (in json). I want to search on several
> values, each one is indexed. However, if I search for COND1 AND COND2, the
> query plan is simply
>
> SEARCH TABLE data USING INDEX cond1Index (cond1=?)
>
> Is this normal?

Yes.  A query can use only a single index per table.

> Should I be creating indexes differently to cover two dimensions at once?

Create an index on both colums:

  CREATE INDEX cond1and2Index ON data(cond1, cond2);

If you are not using equality comparisons on all but the last columns in
the index, you might need an expression index.  (With JSON, I guess you
are already doing this.)

> Perhaps with r*tree?

That would be useful mainly for multidimensional, numeric range queries
(e.g., "xColumn BETWEEN ? AND ? AND yColumn BETWEEN ? AND ?").


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Clemens Ladisch]

x wrote:
> I’m thinking about this more from the gain in speed rather than saving space.

Database performance is usually limited by I/O, i.e., you gain speed by
saving space.

> I have done my homework on this

So what are the results of your measurements?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What's the level of B+-Tree ?

[Clemens Ladisch]

ni james wrote:
> In the "SQLite File Format" document, the BTree layout is described,
> but now I want to know how to get the BTree level (which is the 'K'
> value mentioned in the Documentation)?

At the end of section 1.5, a "K" is defined.  But I don't think that is
the same K.

Anyway, the document also says:
| The cell pointer array of a b-tree page immediately follows the b-tree
| page header. Let K be the number of cells on the btree. The cell
| pointer array consists of K 2-byte integer offsets to the cell
| contents.

The number of cells in the page is found at offset 3 in the b-tree page
header.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What's the level of B+-Tree ?

[Clemens Ladisch]

james ni wrote:
> As in the example that I provided, there are 4 cells in a single btree
> page. So there must be some mechanism to determine hoe many keys that
> one cell can own.

One key per cell:
| Within an interior b-tree page, each key and the pointer to its
| immediate left are combined into a structure called a "cell". The
| right-most pointer is held separately.

> I want to know exactly the very value and just how to change the value
> to a larger one, for example, 256, 512, or even larger.

Keys (and values) can have arbitrary size, so to change how many can fit
into a page, make them smaller.  :)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What's the level of B+-Tree ?

[Clemens Ladisch]

james ni wrote:
> the INSERT speed is becoming slower and slower;
>
> the number of syscalls are increasing quickly;

Insert the largest values last.

Increase the cache size: .

Decrease the amount of data stored in the index.  (This is unlikely to
be possible.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: How can I make this faster?

[Clemens Ladisch]

J Decker wrote:
> So I have this sql script that inserts into a single table, and it is VERY
> slow.

Wrap everything into a single transaction.



Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

[Clemens Ladisch]

sanhua.zh wrote:
> All 1. 2. 3. steps are run sequentially, which means that the step 2
> runs after step 1 finished and step 3 runs after step 2 finished
> theoretically .
> Also, I can make sure the memory order between threads.
>
> Then, is it a safe way to use sqlite connection ?

Yes.

Multi-threading problems are caused by multiple threads accessing the
same data at the same time.  If code in multiple threads is serialized,
it is, for practical purpose, identical to single-threaded code.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

[Clemens Ladisch]

Gwendal Roué wrote:
> Serialized accesses from multiple threads is OK when the connection is
> in the "Multi-thread" or "Serialized" threading modes, but not in the
> "Single-thread" threading mode.

 says:
| 1. *Single-thread*. In this mode, all mutexes are disabled and SQLite
|is unsafe to use in more than a single thread at once.

When the accesses are serialized, they are not _at once_.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

[Clemens Ladisch]

Gwendal Roué wrote:
>> Le 16 août 2017 à 08:38, Clemens Ladisch  a écrit :
>> Gwendal Roué wrote:
>>> Serialized accesses from multiple threads is OK when the connection is
>>> in the "Multi-thread" or "Serialized" threading modes, but not in the
>>> "Single-thread" threading mode.
>>
>> <http://www.sqlite.org/threadsafe.html> says:
>> | 1. *Single-thread*. In this mode, all mutexes are disabled and SQLite
>> |is unsafe to use in more than a single thread at once.
>>
>> When the accesses are serialized, they are not _at once_.
>
> According to your interpretation, "Single-thread" and "Multi-thread" modes 
> are equivalent:
>
>> Multi-thread. In this mode, SQLite can be safely used by multiple threads
>> provided that no single database connection is used simultaneously in two
>> or more threads.

"Multi-thread" mode allows concurrent accesses to different connections
(which might end up accessing the same internal, global variables).

When all accesses are serialized, there are not concurrent accesses at all.

> Finally, we don't know which threading mode the OP is using.

Serializing everything, as the OP is doing, is safe in any mode.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Clemens Ladisch]

sanhua.zh wrote:
> 1. Conn A: Open, PRAGMA journal_mode=WAL
> 2.ConnB: Open, PRAGMA journal_mode=WAL
> 3.ConnA: CREATE TABLE sample (i INTEGER);
> 4.ConnB: PRAGMA table_info('sample')
>
> Firstly, both thread 1 and 2 do initialization for their own conn, which is 
> to read to schema into memory.
> Then, Conn A creates a table with Conn A.
> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it 
> returns nothing.
>
> I do know the reason should be the expired in-memory-schema.

No, SQLite automatically detects schema changes.

It's likely that the second connection started its transaction before
the first connection committed its own, so it still sees the old state
of the database.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Clemens Ladisch]

Jay Kreibich wrote:
> On Aug 18, 2017, at 5:33 AM, Clemens Ladisch  wrote:
>> sanhua.zh wrote:
>>> 1. Conn A: Open, PRAGMA journal_mode=WAL
>>> 2.ConnB: Open, PRAGMA journal_mode=WAL
>>> 3.ConnA: CREATE TABLE sample (i INTEGER);
>>> 4.ConnB: PRAGMA table_info('sample')
>>>
>>> Firstly, both thread 1 and 2 do initialization for their own conn, which is 
>>> to read to schema into memory.
>>> Then, Conn A creates a table with Conn A.
>>> Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and 
>>> it returns nothing.
>>>
>>> I do know the reason should be the expired in-memory-schema.
>>
>> No, SQLite automatically detects schema changes.
>
> …but only automatically re-prepares the expired statements if the statement
> was originally prepared using sqlite3_prepare*_v2 or _v3.

The word "expired" in the OP is misleading; this problem has nothing to do
with schema expiration (that would result in an SQLITE_SCHEMA error).


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] offset of file position is beyond EOF

[Clemens Ladisch]

Jacky Lam wrote:
> I recently meet a case that the file position offset of a pager is beyond
> EOF position of db.

 says:
| The lseek() function shall allow the file offset to be set beyond the end
| of the existing data in the file. If data is later written at this point,
| subsequent reads of data in the gap shall return bytes with the value 0
| until data is actually written into the gap.
| [...]
| Although lseek() may position the file offset beyond the end of the file,
| this function does not itself extend the size of the file. [...] the only
| function in POSIX.1-2008 that may directly extend the size of the file is
| write(), truncate(), and ftruncate()
| [...]
| An invalid file offset that would cause [EINVAL] to be returned may be
| both implementation-defined and device-dependent

The sqlite3_file object does not have a concept of "current file position";
the xRead/xWrite/xTruncate functions have explicit offset/size parameters.

But the Unix VFS internally takes advantage of this behaviour and writes
only the last byte of a newly allocated page.

> I would like to know if ... it is a bug makes from my OS

What VFS are you using?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users