Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Kees Nuyt]

On Fri, 13 Mar 2020 10:09:38 +, you wrote:

>On 13 Mar 2020, at 09:35, Peter da Silva  wrote:
>
>> What do you mean there have been "just a few threads" in the mailing list?
>> I can barely keep up with it.
>
> As a lurker I can say that this mailing list suits just
> fine. Another forum means another username/password I have
> to manage. 

I'm mostly a lurker myself. I lurk many mailing lists, all
require a username/password. One more doesn't matter much.
My experience with the fossil-forum is positive, it has not
changed my reading habits. I just subscribed to its email
notifications, and I get everything threaded in the same way
as a mailing list.

> On a list, the contributions come in date order
> which is easier to manage than, rather than as with a forum,
> having them grouped by thread. In fact, with this list,
> there's no management at all required. An advantage of
> writing one's own email client is extra added features which
> are just right for me: I can limit the size of a mailbox to
> a certain number of mails. For the sqlite list, it's 10k
> mails. More than that and the oldest get moved to the Trash
> automatically.
> It's all I need.

With a subscription to email notifications of the sqlite-forum
you can lurk in just the same way as the sqlite-users mailing list.
Threaded or in date order, whatever your email client supports.
Just try it, your worries will vanish soon.

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

Re: [sqlite] notify all processes of database modification

[Kees Nuyt]

On Fri, 7 Feb 2020 00:13:00 -0700 (MST), you wrote:

> The documents are no longer available, can you kindly share it to my mail.
> I'll be extremely thankful to you. Mail is aliahmadqureshi...@gmail.com

You are probably following up a posting of about 10 years ago.
Most of us do not have that message anymore, so it is not totally clear what you
need.

Anyway, SQLite doesn't have such a mechanism by itself.
Maybe inotify is useful to you :

https://en.wikipedia.org/wiki/Inotify
http://man7.org/linux/man-pages/man7/inotify.7.html

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

Re: [sqlite] csv extension/feature requests: new options for field separator different from default ', ' and skip=N to skip first N lines

[Kees Nuyt]

On Tue, 04 Feb 2020 13:18:30 +0100, you wrote:

>On 3 Dec 2019, at 16:10, Jannick wrote:
>
>> would it be possible to add to the csv extension the following parameter
>> options (with syntax along the lines of):
>>
>> - sep=';': field separator character (different from default ',')
>> - skip=N: skip the first N lines
>>
>> Both options would be very helpful for information of fixed format
>> downloaded from information providers.
>
> Hi, those would be very useful enhancements.

I don't see the need, the feature is available in another form

$ cat test.csv

"a";"b"
"c";"d"

$ sqlite3 test.db \
"DROP TABLE IF EXISTS tbl1" \
".mode csv" \
".separator ;" \
".import test.csv tbl1" \
". mode column" \
".headers on" \
"SELECT * FROM tbl1"

a   b
--  --
c   d


-- 
Regards,
Kees Nuyt

___
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 yield an error when opening a nonexistent dbfile?

[Kees Nuyt]

On Fri, 31 Jan 2020 09:02:10 -0600, Peng wrote:

> Hi,
>
> By default the command sqlite3 will just open a dbfile if it does not exist.
>
> Suppose that I just want to perform read-only operations in a sqlite3
> session, I will not need to create a non-exsitent file. Rather, I want
> the sqlite3 to fail when the dbfile does not exist. Is there a way to
> achieve this?

$ sqlite3 -readonly testx.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tables
Error: unable to open database "testx.db": unable to open database file

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

Re: [sqlite] Obtaining rowid of an updated row in UPSERT

[Kees Nuyt]

On Sun, 19 Jan 2020 17:07:38 +0100, you wrote:

>On 16.01.2020 14:02, Daniel Janus wrote:
>> Dear SQLiters,
>> 
>> If an INSERT ... ON CONFLICT DO UPDATE statement detects that a row
>> already exists and needs to be updated, it doesn't seem to set lastRowid
>> to the rowid of that row. Observe (sqlite 3.30.1):
>> 
>>    > create table users (id integer primary key, firstname text, lastname 
>> text, phonenumber text);
>>    > create unique index idx_users_firstname_lastname on users (firstname, 
>> lastname);
>>    > insert into users (firstname, lastname, phonenumber) values ('John', 
>> 'Doe', '1'),
>>    ('Donald', 'Covfefe', '2');
>>    > insert into users (firstname, lastname, phonenumber) values ('John', 
>> 'Doe', '3')
>>    on conflict (firstname, lastname) do update set phonenumber = 
>> excluded.phonenumber;
>>    > select last_insert_rowid();
>>    2
>> 
>> I'd like to have obtained 1 instead, the rowid for John Doe.
>> 
>> I imagine the reasoning behind this is that if there was no inserted row,
>> then last_*insert*_rowid should remain as it was... but is there any way
>> to obtain this information, other than making a subsequent SELECT?
>
> Replying to myself: I ended up doing exactly this – a subsequent SELECT after 
> the
> upsert; while this approach is working, it does feel suboptimal.

On the source code side, yes, but you have to wrap the UPSERT and subsequent
SELECT in a transaction anyway, so you can be sure the relavant index and table
pages are still valid in the cache. So, performancewise, it doesn't matter much.

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

Re: [sqlite] how to pass -Dxxx compile option

[Kees Nuyt]

On Thu, 9 Jan 2020 14:47:44 +0800, you wrote:

> Hi,
>
> How can I pass -Dxxx compile option when I build sqlite? Such as, -
> DSQLITE_ENABLE_INTERNAL_FUNCTIONS.

I don't consider myself an expoert, but the script
below works for me on a Raspberry Pi, Raspbian Jessie.
Note: instead of readline I use the linenoise lib 
that is shipped with fossil. Also, I built a few
of the tools at the same time.
You may have to tweak this a little for your preferences and your platform.

cd ~/src/sqlite
test ! -z "$opt_p" && fossil pull --verbose
test ! -z "$opt_u" && fossil update trunk

OPTS=-DHAVE_LINENOISE
export CPPFLAGS="-DSQLITE_ENABLE_API_ARMOR \
-DSQLITE_ENABLE_COLUMN_METADATA \
-DSQLITE_ENABLE_DBPAGE_VTAB \
-DSQLITE_ENABLE_DBSTAT_VTAB \
-DSQLITE_ENABLE_DESERIALIZE \
-DSQLITE_ENABLE_EXPLAIN_COMMENTS \
-DSQLITE_ENABLE_FTS5 \
-DSQLITE_ENABLE_HIDDEN_COLUMNS \
-DSQLITE_ENABLE_JSON1 \
-DSQLITE_ENABLE_MEMSYS5 \
-DSQLITE_ENABLE_NORMALIZE \
-DSQLITE_ENABLE_OFFSET_SQL_FUNC \
-DSQLITE_ENABLE_PREUPDATE_HOOK \
-DSQLITE_ENABLE_RBU \
-DSQLITE_ENABLE_RTREE \
-DSQLITE_ENABLE_GEOPOLY \
-DSQLITE_ENABLE_STMT_SCANSTATUS \
-DSQLITE_ENABLE_STMTVTAB \
-DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION \
-DSQLITE_ENABLE_UNLOCK_NOTIFY \
-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \
-DSQLITE_INTROSPECTION_PRAGMAS \
-DSQLITE_SOUNDEX \
-DSQLITE_USE_URI \
-DSQLITE_SECURE_DELETE \
-DSQLITE_DQS=0 \
-DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1"
./configure \
--prefix=/usr/local \
--libdir=/usr/lib/arm-linux-gnueabihf \
--enable-load-extension \
--enable-threadsafe \
--with-readline-lib=auto \
--with-pic \
--with-gnu-ld \
--enable-json1 \
--enable-fts5 \
--enable-rtree \
--enable-session \
--enable-update-limit \
--enable-geopoly \
--enable-tcl \
--disable-debug \
--disable-static \
&& make clean
&& make sqlite3.c \
&& make shell.c \
&& gcc $OPTS $CPPFLAGS \
-I ../fossil/src \
-L /usr/lib/arm-linux-gnueabihf -ltcl8.6 -lm -ldl -lz -lpthread \
shell.c ../fossil/src/linenoise.c sqlite3.c -o sqlite3 \
&& make sqlite3_analyzer \
&& make sqldiff \
&& make scrub \
&& make showdb \
&& make showwal \
&& make showshm \
&& make wordcount


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

Re: [sqlite] Bug report

[Kees Nuyt]

On Thu, 21 Nov 2019 21:02:57 +, Jose Isaias Cabrera wrote:

>Kees Nuyt, on Thursday, November 21, 2019 03:48 PM, wrote...
[...]
>>
>> I see no CVE entered by the OP, but maybe I missed something.
>
> Yes, you are right.  After pasting it, I went through the top 5
> and none of these aren't/weren't the one. Apologies. 
> I thought that by searching on sqlite the top 5 or so
> would be the one that was just opened, but for some reason,
> it was not.  Sorry about that.  Fast fingers Jose.

No problem!
We'll wait for more input from the OP.

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

Re: [sqlite] Bug report

[Kees Nuyt]

Thanks, Jose.

I see no CVE entered by the OP, but maybe I missed something.

A quick look to your list :

> NameDescription
> CVE-2019-9937 <http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-9937>
> In SQLite 3.27.2, interleaving reads and writes in a single transaction with
> an fts5 virtual table will lead to a NULL Pointer Dereference in
> fts5ChunkIterate in sqlite3.c. This is related to ext/fts5/fts5_hash.c and
> ext/fts5/fts5_index.c.

Resolved 2019-03-18


> CVE-2019-9936 <http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-9936>
> In SQLite 3.27.2, running fts5 prefix queries inside a transaction could
> trigger a heap-based buffer over-read in fts5HashEntrySort in sqlite3.c, which
> may lead to an information leak. This is related to ext/fts5/fts5_hash.c.

Resolved 2019-03-18


> CVE-2019-5827 <http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-5827>
> Integer overflow in SQLite via WebSQL in Google Chrome prior to 74.0.3729.131
> allowed a remote attacker to potentially exploit heap corruption via a crafted
> HTML page.

Resolved 2019-04-13


> CVE-2019-3784 <http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-3784>
> Cloud Foundry Stratos, versions prior to 2.3.0, contains an insecure session
> that can be spoofed. When deployed on cloud foundry with multiple instances
> using the default embedded SQLite database, a remote authenticated malicious
> user can switch sessions to another user with the same session id.

Application error


> CVE-2019-1616 8<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-16168>
> In SQLite through 3.29.0, whereLoopAddBtreeIndex in sqlite3.c can crash a
> browser or other application because of missing validation of a sqlite_stat1
> sz field, aka a "severe division by zero in the query planner."

Resolved 2019-08-15


> CVE-2019-1075 2<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-10752>
> Sequelize, all versions prior to version 4.44.3 and 5.15.1, is vulnerable to
> SQL Injection due to sequelize.json() helper function not escaping values
> properly when formatting sub paths for JSON queries for MySQL, MariaDB and
> SQLite.

Application error


> CVE-2018-8740 <http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2018-8740>
> In SQLite through 3.22.0, databases whose schema is corrupted using a CREATE
> TABLE AS statement could cause a NULL pointer dereference, related to build.c
> and prepare.c.

Resolved 2018-03-16


> CVE-2018-7774 <http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2018-7774>
> The vulnerability exists within processing of localize.php in Schneider
> Electric U.motion Builder software versions prior to v1.3.4. The underlying
> SQLite database query is subject to SQL injection on the username input
> parameter.

Application error


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

Re: [sqlite] Bug report

[Kees Nuyt]

On Tue, 19 Nov 2019 00:19:13 -0500, you wrote:

> Hi,
>
> This is Yongheng Chen from Gatech and Rui Zhong from PSU.
> We found 7 crashes for sqlite of  the newest commit
> 3842e8f166e23a1ed6e6094105e7a23502d414da. 
> We have attached the samples that crash sqlite in the email. 

The mailing list strips attachemnts. Please insert them in the body text of your
message, or mail them to Richard Hipp.

> FYI, we have also reported the bugs for CVE
> at cve.mitre.org <http://cve.mitre.org/>. 

Can you tell us the CVE nunber?


-- 
Regards,

Kees Nuyt

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

Re: [sqlite] Getting "chunked" output from a large SELECT operation.

[Kees Nuyt]

On Wed, 16 Oct 2019 17:38:28 +, you wrote:

> I'm having a situation where the results of a large
> SELECT operation are apparently too big to fit in memory.
>
> Obviously I could jerry-rig something to work around
> this, but I have a vague recollection that SQLite
> provides a nice way to get the results of a query in
> "chunks" so that the memory demands can be reduced as
> much as needed by going back to the well a (potentially
> large) number of times.
>
> Am I remembering this right?  Can anyone refresh my
> memory on how to do it if so?
>
> Randall.

Maybe Scrolling window queries?
 https://www.sqlite.org/rowvalue.html#scrolling_window_queries 

Or you may refer to replacing sqlite3_get_table(), which retrieves a whole
result set in a memory data structure, by a loop which retrieves row by row:

sqlite3_prepare()
loop while not SQLITE_DONE
sqlite3_step()
do-something-with-the-row

https://www.sqlite.org/c3ref/free_table.html
https://www.sqlite.org/c3ref/step.html

-- 
Regards,
Kees Nuyt

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

Re: [sqlite] Hello everyone, I found some strange behavior when using the Command Line Shell For SQLite.

[Kees Nuyt]

On Thu, 3 Oct 2019 09:46:27 +, suan...@outlook.com wrote:

> In column mode, each record is shown on a separate
> line with the data aligned in columns. For example:
> sqlite> .mode column
> sqlite> select * from tbl1;
> one   two   
> --  --
> hello 10
> goodbye   20
> sqlite>
>
> But I found,only when columns text is ANSI,it can normal work.
> sqlite> .mode column
> sqlite> select * from ?;
>                         
> --  --  --  --
> 1           0           1           0
> 2           0           2           0
>
> If the columns text is utf-8,it can't work.
> What should I do for it?


It works for me, using UTF-8 language settings for the terminal,
and a font that contains enough of UTF-8.

~ $ uname -a
Linux f7p3 4.19.66-v7+ #1253 SMP Thu Aug 15 11:49:46 BST 2019 armv7l GNU/Linux

~ $ set|grep -E '(LC_|LANG)'
LANG=en_US.utf-8
LC_ALL=
LC_COLLATE=en_US.utf-8
LC_CTYPE=en_US.utf-8
LC_MESSAGES=en_US.utf-8
LC_MONETARY=en_US.utf-8
LC_NUMERIC=en_US.utf-8
LC_TIME=en_US.utf-8

Column names used: e-acute and a-umlaut

~ $ sqlite3 test.db
SQLite version 3.30.0 2019-09-25 18:44:49
Enter ".help" for usage hints.
sqlite> CREATE TABLE x (é integer primary key,ä TEXT);
sqlite> INSERT INTO x VALUES (1,'téxt');
sqlite> . mode column
sqlite> . head on
sqlite> select * FROM x;
é   ä
--  --
1   téxt
sqlite>.q

~ $ sqlite3 test.db \
".mode column" ".head on" ".width 1 4" "SELECT * FROM x" \
| hexdump -C

 c3 a9 20 20 c3 a4 20 20  20 0a 2d 20 20 2d 2d 2d |..  ..   .-  ---|
0010 2d 0a 31 20 20 74 c3 a9  78 74 0a|-.1  t..xt.|
001b

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

Re: [sqlite] Endless loop possible with simultaneous SELECT and UPDATE?

[Kees Nuyt]

On Tue, 3 Sep 2019 18:26:01 +0100, you wrote:

>> // do SELECT on db1
>> // do UPDATE on db2
>
> Do you expect the SELECT to see the results of the previous
> UPDATE ?  It won't, until the transaction has ended
> (unless you arrange this explicitly).

That's the nice thing about this construct: 
isolation between the SELECT and the UPDATE, 
the pattern is indeed:

* Iterate over unchanged rows,
  without seeing DELETEs, UPDATEs and INSERTs 
  on the tables in the select, 

* Be free to act upon the original rows
  without disturbance of the read.

I think it only works for WAL journal mode.

-- 
Regards,
Kees Nuyt

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

Re: [sqlite] sqlite: see: encryption

[Kees Nuyt]

On Tue, 3 Sep 2019 12:11:32 +0530, Vadiraj Villivalam wrote:

> Hi,
>
> Our client software uses sqlite for persistence and db is currently
> encrypted by passing a app generated key to SEE.
> With the open os like android providing keystore and key generation
> mechanism, we want to switch to this secure key generation mechanism and
> avoid generating  key ourselves. As the key store does not allow the key
> itself to be exported out, I would like to know if sqlite has a mechanism
> to leverage the key store way of en/decrypting it (could be with a callback
> implemented by app that interfaces with Android keystore)? Any insight will
> help. Thanks.

This article may be of help. It also talks about limitations,
e.g. "The Keystore itself is encrypted using the user’s own
lockscreen pin/password, hence, when the device screen is locked
the Keystore is unavailable. Keep this in mind if you have a
background service that could need to access your application
secrets."

<https://www.androidauthority.com/use-android-keystore-store-passwords-sensitive-information-623779/>

It is the first hit in a search on "android keystore api
tutorial".

Hope this helps.

-- 
Regards,
Kees Nuyt

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

Re: [sqlite] sqlite3 unable to open network db file in readwrite mode

[Kees Nuyt]

On Wed, 3 Jul 2019 20:48:42 -0500, Lewis Balentine wrote:

> *First, version numbers:*
> My desktop is running: Linux Mint 19.1 with the MATE 1.20.1 user interface,
> Kernel 4.15.0-52 Generic x86_64
> The network machine is running: Linux Mint 18.3 with the MATE 1.18.0 user
> interface, Kernel 4.4.0-141 Generic x86_64
> SQLite Version 3.22.0
> DB Browser for SQLite Version 3.10.1
> Python 3.6.8
> 
> *Second, never assume:*
> It may not be 'recommended' to access a sqlite database file on a network
> share but that makes a lot of assumptions.
> 
> *Problem:*
> The target sqlite3 database file is on a samba network share and mounted on
> desktop with the following command:
> sudo mount -o username=[myname],password=[mypw] -t cifs //[ipaddress]/kwpvr
> /mnt/kwpvr
> The file is marked readwrite for owner, group and others: -rw-rw-rw- 1
> [myname] [myname] 40960 Jul 3 19:25 kwpvr3.db
> The samba share is marked to allow create, delete and guest access
> In Caja I can create, copy, delete, rename any file on that share.
> 
> When I open the file in DB Browser for SQLite I can browse the tables but I
> can not change anything.
> The error is: "attempt to write a readonly database"
> 
> In python I use the following line to open a sqlite connection:
> sql3conn = sqlite3.connect("file:" + cfg["dbfile"] + "?mode=rw", uri=True)
> That opens and works on a local file but not on the mounted file system.
> I get the same error as above when trying to update a record.
> 
> So I fell back to the lowest common denominator: the SQLite3 command line
> utility
> Works fine locally but on the mounted file system I get the same error.
> sqlite3 "file:/mnt/kwpvr/kwpvr3.db?mode=rw" "UPDATE Configuration SET
> KeyValue='test' WHERE key='Active';"
> 
> *Question:*
> Is this the intended behavior of sqlite3 or is this something unique to the
> linux platform?

Thanks for the detailed description.
My first thought: Is the directory writable? 
It has to be so SQLite can create a journal file.
https://www.sqlite.org/atomiccommit.html#section_3_5

Apart from that, SQLite has to be able to create
files in its TMPDIR.
https://www.sqlite.org/tempfiles.html#temporary_file_storage_locations

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

Re: [sqlite] Read/Write cycle

[Kees Nuyt]

Hi,

On Tue, 23 Apr 2019 11:54:07 +0530, you wrote:

> Hi All,
>
> I'm new to the Database, In my application i want to update a particular
> data in a database for every 10 sec. What is the maximum read/write cycle
> for the above scenario?

Nobody can give an answer to that, there are many dependencies
and even more unknowns.

A few of the things that matter:
- whether you keep the connection to the database open or not
- the size of the page cache
- if any keys are changed by such an update
- the number of indexes involved (modified) when that data
  is updated
- the probabilty that an update will cause the BTrees of
  the table and its indexes
- depth of the BTrees
- the size of the OS cache for file blocks
- pressure on evicting the file block cache by other
  programs
- etc.

The best way to find out is to build it, populate the database
with production-size data, let it run and measure the read/write
cycles as they occur.

Hope this helps.

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

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Kees Nuyt]

On Wed, 10 Apr 2019 11:05:59 +0100, you wrote:

>Wednesday, April 10, 2019, 10:28:55 AM, Luuk  wrote:
>
>> On 10-4-2019 10:28, Kees Nuyt wrote:
>
>>> sqlite> select * from pragma_function_list;
>
>> sqlite> select * from pragma_function_list;
>> Error: no such table: pragma_function_list
>
> From the help page (https://www.sqlite.org/pragma.html#pragma_function_list)
> this option is only available "...if SQLite is built using the
> -DSQLITE_INTROSPECTION_PRAGMAS compile-time option.". My (standard) 
> copy of 3.27.2 doesn't support this either, so I'm guessing Keet must
> have a custom-built version.


My mistake, I have that option in all my builds, I forgot about
it.


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

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Kees Nuyt]

On Wed, 10 Apr 2019 13:17:23 +1000, John wrote:

> I have not used extensions before. I understand that some are included 
> in the amalgamation source file and that some of these are enabled by 
> default. So, which ones are built-in and which of those are enabled in 
> the standard downloadable Win32 SQLite CLI?

sqlite> .mode column
sqlite> .header on
sqlite> .width 28 8
sqlite> select * from pragma_function_list;

> If an extension is built-in and enabled, what do I need to do to use it. 
> The instructions seem to be for the case where an extension is built as 
> an external library (.dll) to be loaded by eg. .load ./csv where csv 
> would be csv.dll in the current directory. If the csv extension was 
> built-in, would I still need to load it to activate it?

I don't think so.

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

[sqlite] Documentation error: USE_ZLIB versus HAVE_ZLIB

[Kees Nuyt]

Good morning!

Both
https://sqlite.org/cli.html#_do_it_yourself_builds_
and
https://sqlite.org/compile.html#use_zlib
mention option SQLITE_USE_ZLIB to enable _zipfile and _sqlar.
However, shell.c.in uses SQLITE_HAVE_ZLIB for this.
I think this is a documentation error.

checkout:
7a0a26ed380dd0bdda50b0204b30b53c70f2
2019-03-22 13:53:25 UTC
tags:
trunk

 $ grep -n _USE_ZLIB src/*
(nothing)
 $ grep -n _HAVE_ZLIB src/*
src/shell.c.in:944:#ifdef SQLITE_HAVE_ZLIB
src/shell.c.in:3451:#if defined(SQLITE_HAVE_ZLIB) &&
!defined(SQLITE_OMIT_VIRTUALTABLE)
src/shell.c.in:4000:#ifdef SQLITE_HAVE_ZLIB
src/shell.c.in:5264:#if !defined(SQLITE_OMIT_VIRTUALTABLE) &&
defined(SQLITE_HAVE_ZLIB)
src/shell.c.in:5995:#endif /* !defined(SQLITE_OMIT_VIRTUALTABLE)
&& defined(SQLITE_HAVE_ZLIB) */
src/shell.c.in:6064:#if !defined(SQLITE_OMIT_VIRTUALTABLE) &&
defined(SQLITE_HAVE_ZLIB)
src/shell.c.in:6989:#ifdef SQLITE_HAVE_ZLIB
src/shell.c.in:8428:#if SQLITE_HAVE_ZLIB
src/shell.c.in:8835:#if defined(SQLITE_HAVE_ZLIB) &&
!defined(SQLITE_OMIT_VIRTUALTABLE)
src/shell.c.in:8883:#ifdef SQLITE_HAVE_ZLIB
src/shell.c.in:9165:#ifdef SQLITE_HAVE_ZLIB
src/shell.c.in:9179:#if !defined(SQLITE_OMIT_VIRTUALTABLE) &&
defined(SQLITE_HAVE_ZLIB)
src/shell.c.in:9268:#ifdef SQLITE_HAVE_ZLIB
src/shell.c.in:9374:#if !defined(SQLITE_OMIT_VIRTUALTABLE) &&
defined(SQLITE_HAVE_ZLIB)
src/test1.c:7181:#ifdef SQLITE_HAVE_ZLIB
src/test1.c:7209:#ifdef SQLITE_HAVE_ZLIB

-- 
Have a nice day,
Regards,

Kees Nuyt
___
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

[Kees Nuyt]

On Tue, 12 Feb 2019 21:03:47 +, you wrote:

>
>
>
> David Raymond, on Tuesday, February 12, 2019 03:48 PM, wrote...
>> Not sure if this will fix your specific issue, but if you're using a query 
>> as a single
>> value it needs to be in parenthesis, so something like
>>
>> insert into t (a, b, c, d, e, idate) values ('p006', 1, 2, (SELECT d FROM t 
>> WHERE a = 'p006'
>> AND max(idate)), 4, '2019-02-12');
>
> I get,
> sqlite> insert into t (a, b, c, d, e, idate) 
> values ('p001', 1, 2, 
>  (SELECT d FROM t WHERE a = 'p001' AND max(idate)),
> 4, '2019-02-12');
> Error: misuse of aggregate function max()

Try:
insert into t (a, b, c, d, e, idate) 
 values ('p001', 1, 2, 
  (SELECT d FROM t
WHERE a = 'p001'
  AND idate = (SELECT max(idate) FROM t WHERE a = 'p001')
  ),
  4, '2019-02-12');

-- 
Regards,
Kees Nuyt


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

Re: [sqlite] FOREIGN KEY constraint not respected during bulk insert using TRANSACTION

[Kees Nuyt]

On Fri, 14 Dec 2018 15:05:29 +, Morten Joergensen
 wrote:

> Hi,
>
> I have two tables, the second with a FOREIGN KEY - ON DELETE
> CASCADE to the primary key on the first table. When I do a
> transaction inserting a lot of records into the second table
> at once, I am allowed to insert records that do not fulfill
> the constraint, i.e. having values in the foreign key column
> that are not present in the primary key column of the first
> table. When I insert or edit a single record using SQLCipher's
> DB Browser for SQLite, I am correctly being rejected. I can
> insert a record with NULL (so I need a NOT NULL on the foreign
> key column, actually), but not with a value that do not exist
> in the first table.
> 
> I can find nothing in the documentation about foreign key
> constraints not being respected for bulk inserts like this,
> but perhaps there is such a rule anyway? I found an old bug
> report, 29ab7be99f2cfe0b04466dd95b80ace87e9fc1b2, "Hitting NOT
> NULL constraint does not roll back statement transaction",
> that resembles it a bit on the title at least, but it is not
> the same.
> 
> Are constraints disabled for bulk inserts? It does execute
> very fast, so... - or have I found a bug? - or am doing
> something wrong?

Did you enable foreign key checking with
PRAGMA foreign_keys=on;
? It is a per-connection setting, not retained in the database.
It is off by default. Indeed bulk inserts are fast without it.

https://sqlite.org/pragma.html#pragma_foreign_keys
https://sqlite.org/pragma.html#pragma_foreign_key_check

-- 
Regards,

Kees Nuyt


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

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Kees Nuyt]

On Sun, 07 Oct 2018 15:25:26 -0600, "Keith Medcalf"
 wrote:

> Many people do not "do" web forums.  I am one of them. 

So am I. But:

> If there is not a mailing list then it does not exist.

The fossil forum sends notification mails, with the full text of
the forum posts, complete with references headers, so it threads
correctly, just like a mailing list.
For lurking mode, there is nothing else you have to do.

Once you decide you want to follow-up on a posting, the
procedure is simple: Each message contains a hyperlink to that
exact posting in the webforum. If you don't want to be
anonymous, you only have to login. 
Click the reply button to reply. That's all.
No further navigation or browsing required.

For me, this is a very acceptable alternative for a mailing
list. YMMV, I know you post  more often than I do.

-- 
Regards,
Kees Nuyt

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

Re: [sqlite] .exit in script is not working

[Kees Nuyt]

On Fri, 31 Aug 2018 19:29:47 +0200, Stéphane AULERY
 wrote:

> When I use the -init option the .exit statement
> at the end of my script is never executed.
>
> I launch sqlite from a batch file on Windows Server 2012 and Windows 7 64 bit.
>
> Command line :
> sqlite3.exe -init myscript.scr

If you want a script to exit, use redirection:

sqlite3.exe http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Cheating" at making common tables

[Kees Nuyt]

On Sat, 4 Aug 2018 21:40:53 -0400, Stephen Chrzanowski
 wrote:

> I'm making a small database for a game, and a bunch of the tables follow
> the same kind of naming convention due to normalization, like
>
> {Name_Of_Information}s
>  {Name_Of_Information}ID as Integer
>  {Name_Of_Information}Name as Char
>
> So for example:
>
> Resources
>  ResourceID as Integer
>  ResourceName as Integer
>
> (Plural name on the table name, singulars on the field names)
>
> Would there be a way within SQLite via CTE or whatever other magic there
> is, to create tables based on this structure, and setup the PK?

I guess would use good old m4 for that. Or awk.

[...]

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] Using CTE with date comparison

[Kees Nuyt]

On Fri, 3 Aug 2018 15:11:06 +0200, Csányi Pál
 wrote:

>The database is so far with only one table:
>CREATE TABLE MyLengthOfService (
>id  INT  PRIMARY KEY
> UNIQUE,
>WorkPlaceName   TEXT,

You shouldn't use UNIQUE for the PRIMARY KEY.
Any PRIMARY KEY is implicitly unique by itself.
Adding the UNIQUE keyword might create an extra, redundant,
index, which will eat file space and processing time without
having any added value. And it may confuse the query optimizer.

The idea is that any set (table) may have more than one key to
uniquely identify a tuple (row).
Each key is called a candidate key.

Only one of those keys can be choosen to be the PRIMARY KEY.
All other candidate keys can get honored by adding the UNIQUE
clause, to recognize them as alternative keys.

-- 
Regards,
Kees Nuyt

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

Re: [sqlite] crash dropping table

[Kees Nuyt]

On Wed, 18 Apr 2018 15:47:47 +, Mark Wagner <m...@google.com>
wrote:

> Sorry for my ignorance but is there a procedure for
> submitting bugs for things like this?

The procedure is to mail bugs to this mailing list, including
version info, so you did exactly the right thing.

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

Re: [sqlite] CLI thoughts

[Kees Nuyt]

On Sun, 15 Apr 2018 14:38:33 +0100, Martin
<mdransfield...@btinternet.com> wrote:

>
>  I am a relatively new user of sqlite.  My current use is to learn
> sqlite sql to solve solitaire battleship puzzles. I am not a programmer,
> so use the command line interface on a Mac.
>
>  As a result I wonder whether it is worth considering a few
> amendments to the commands in the CLI tool. My first thoughts being:
>
>  1. adding an escape option to  where it is available; such as
> sha3sum. (Naively I have defined some tables with names begining
> '_'. I know I can rename them, but I prefer not to.)

https://www.sqlite.org/lang_expr.html#like
, the ESCAPE keyword.

>  2. adding a new command to allow a simple variable to be set.
> For example,
>
>   .let a ...
>
> where ... is a bona fide dot command (less the dot) that makes
> usage sense.  For example, I find the .cd command tedious in
> practice. Global exported variables are not interned so such as
> .cd $HOME fails.

Use a temp table (local to the database session):
CREATE TEMP TABLE vars (
var TEXT PRIMARY KEY NOT NULL
,   val 
) WITHOUT ROWID;

Assign a new value:
INSERT OR REPLACE INTO vars (var,val) VALUES ('var1','value1');
Retrieve:
SELECT val FROM vars WHERE var='var1');

For more complex cases, people usually use a host language, like
php, perl, python, tcl, lua, C, dotnet, or even a shell script.
SQLite originally was a database extension to tcl, and that is
powerful.

> Perhaps a sequence like the following:
>
>   .let a system echo $dev
>   .cd -let a
>
> Here, -let as an option was my initail (no thought) idea, but
> thinking of an alternative begins to break the association
> between setting (.let) and using.  In one's mind read '.let a' as
> 'let the variable a be', and '-let a' as 'use the value of letter
> a'.
>
> Further, I thought restricted permissible variable names 'a'
> through to 'z' would be plenty. Certainly better than none as
> seems to be the case currently.  Similarly, they should be
> limited in size to a pathname. Or possibly set the size using
> .limit?
>
> Alternative usage as a dot command option could be
>   .cd -val a
> or
>   .cd $a
> or
>   .cd *a
>
> Obviously these variable names do not exist unless set.
>
> Example (maybe via .read):
>   .once .dat
>   select date('now');
>   .let f system echo "words-`cat .dat`.txt"
>   .once -let f
>   select word from words order by 1;
>
> The system call argument maybe just 'todaysfile' which executes.

You could write and load an extension function or a virtual
table function in C to retrieve exported environment variables.
Somebody probably has done that already. However, those will
only work within SQL, not in dot commands.

The problem with expanding the dot command intterface to a full
scripting language is that there is no end to it, people will
ask for more, like conditional statements, control structures
etc.. 

>  3. the .load command does not have a complement .loaded; how does
> one check?

I guess the easiest way is to just load the extension again, if
you are not sure it is loaded at some point.
If it fails to load, an exception is raised.

> Well, just thinking out loud.

No problem, so do I ;)

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

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

[Kees Nuyt]

On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux
<petermich...@gmail.com> wrote:

> You are right that my purpose does seem kind of confusing.
>
> What I really want is UPSERT to avoid doing an UPDATE, checking if the
> number of rows affected is zero, then doing an INSERT. The lack of UPSERT
> leads to a lot more application code using the UPDATE/INSERT combination.
> UPSERT doesn't exist in SQLite so I was trying to work around that by using
> INSERT OR REPLACE which is not the same thing. I can see from another
> recent thread that some others also think that UPSERT would be a valuable
> addition to SQLite.

I fail to see the problem in
BEGIN;
INSERT OR IGNORE ... ;
UPDATE  ;
COMMIT;
Simple code, no need to test number of affected rows, and pretty
fast because the relevant pages will be in cache.

Or use an updatable view with an INSTEAD OF INSERT trigger.

I did notice that attempts to define a proper UPSERT syntax
opened a can of worms by itself because it (also) has to provide
two colum lists, one for a full INSERT if the row with that PK
doesn't exist, and another one for the columns to be updated
when the row already exists. So, I don't see a big advantage in
UPSERT.

My humble two cents,

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

Re: [sqlite] Bug in fsdir

[Kees Nuyt]

On Thu, 15 Mar 2018 15:55:05 +1100, Jake Thaw
<jaket...@gmail.com> wrote:

>The following query causes a crash in fsdirNext on Windows 10.
>
>SQLite version 3.22.0 2018-01-22 18:45:57
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> SELECT name FROM fsdir('.') JOIN (VALUES(1),(2));
>.
>./sqlite3.exe
>./sqlite3_analyzer.exe
>./sqldiff.exe
>.
>
>Adding an ORDER BY clause seems to provide workaround for this particular
>case.
>

Fixed by Richard Hipp on trunk:
https://www.sqlite.org/src/timeline 

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

Re: [sqlite] Dealing with SQLITE_BUSY

[Kees Nuyt]

On Thu, 22 Feb 2018 14:36:07 +, Simon Slavin
<slav...@bigfraud.org> wrote:

> On 22 Feb 2018, at 11:24am, Clemens Ladisch <clem...@ladisch.de> wrote:
>
>> Use "BEGIN IMMEDIATE" instead to tell the DB that you intend to write.
>
> Depending on how you want your locks to work, BEGIN EXCLUSIVE may work better.

Yes, but note:  "After a BEGIN IMMEDIATE, no other database
connection will be able to write to the database or do a BEGIN
IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to
read from the database, however. 
An exclusive transaction causes EXCLUSIVE locks to be acquired
on all databases. After a BEGIN EXCLUSIVE, no other database
connection except for read_uncommitted connections will be able
to read the database and no other connection without exception
will be able to write the database until the transaction is
complete."
<https://sqlite.org/lang_transaction.html>

> Do remember when setting your timeout, that you have to set it in both 
> connections.

Exeactly.

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

Re: [sqlite] Question about threadsafe

[Kees Nuyt]

On Tue, 6 Feb 2018 19:33:10 -0700 (MST), Nick
<haveagoodtime2...@gmail.com> wrote:

>>  (a) an error result of some kind or (b) a corrupt database. 
>
> I did not see any info about errmsg.

Your code doesn't check the returncode of the sqlite3_* calls.

>>  Are your processes using the same database connection or does each one
>> have its own ? 
>
> Two processes have two sqlite3_open(). So each one has its own.
>
>>  Are you checking the result codes returned by all the API calls ? 
>
> Yes. I use speedtest1.c as model code. 
>  speedtest1_exec("BEGIN");
>  speedtest1_prepare("INSERT INTO t1 VALUES(?1,?2,?3); --  %d times",
> n);
>  for(i=1; i<=n; i++){
>rc = sqlite3_bind_int64(g.pStmt, 1, i);
>rc = sqlite3_bind_int(g.pStmt, 2, i);
>rc = sqlite3_bind_text(g.pStmt, 3, "hello", -1, SQLITE_STATIC);
>speedtest1_run();
>  }
>  speedtest1_exec("COMMIT");
>
> And I have checked rc = SQLITE_OK.

I assume speedtest1_run() calls sqlite3_step(), but does it
check the return code? I suspect _step() returns SQLITE_BUSY
sometimes.

>
>>  Can you reliably get less than 2 rows ? 
>
> Yes, always less than 2.
> Process A inserts 1-1 and process B inserts 10001-2. I found that
> the first few rows is missing in the result. I mean there is no 10001-10xxx.
>
>>  Does the problem go away if you use threadsafe = 2 ? 
>
> The problem is still here.

Because the test uses two processes, every process is
single-threaded, no risc of concurrency between threads.

PRAGMA busy_timeout might be helpful.


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

Re: [sqlite] Documentation update

[Kees Nuyt]

On Wed, 31 Jan 2018 13:07:17 +, Simon Slavin
<slav...@bigfraud.org> wrote:

>
>
>On 30 Jan 2018, at 6:51pm, William Entriken <fulldec...@gmail.com> wrote:
>
>> Hello. Regarding https://www.sqlite.org/howtocorrupt.html, item 8.3.
>> Perhaps this should be
>> 
>>> 8.3. I/O error while obtaining a lock leads to corruption
>
> Can you clarify your correction ?  I don't see a difference.

It used to be:
8.3. I/O while obtaining a lock leads to corruption

(the word error is missing)

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

Re: [sqlite] Groups in C API

[Kees Nuyt]

On Tue, 30 Jan 2018 12:16:32 +0100, Stephan Buchert
<stephanb...@gmail.com> wrote:

> Thanks for the replies.
>
> Allowing non-aggregate columns in aggregate queries is very useful, as
> shown with the min/max functions.

It is forbidden in most SQL dialects, only supported by SQLite
as a dirty shortcut.

> Probably with this feature comes that SQLite even allows all non-aggregate
> columns in SELECTs with GROUP BY. Perhaps the documentation should warn
> more clearly, that in this case only one arbitrary row in each group is
> returned, not all the rows that the WHERE filter lets through.

IMHO it is pretty clear in https://sqlite.org/lang_select.html 
"3. Generation of the set of result rows"
and its "Side note: Bare columns".

Also, just above "1. Determination of input data" it states:
There are two types of simple SELECT statement - aggregate and
non-aggregate queries. A simple SELECT statement is an aggregate
query if it contains either a GROUP BY clause or one or more
aggregate functions in the result-set. Otherwise, if a simple
SELECT contains no aggregate functions or a GROUP BY clause, it
is a non-aggregate query. 


> More useful would perhaps be, to return in this case (only non-aggregate
> columns but a GROUP BY)  all rows, just grouped together is indicated by
> the GROUP BY. This would have a similar effect as an ORDER BY, but they are
> somewhat different if I look at the syntax diagrams. I have no idea how
> feasible it would be to get SQLite doing this.

ORDER BY does the job just fine, there's no need to overload
GROUP BY with that functionality. 

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

Re: [sqlite] Odd question

[Kees Nuyt]

On Sat, 16 Dec 2017 21:43:44 +, "Nelson, Erik - 2"
<erik.l.nel...@bankofamerica.com> wrote:

> I'm using an application that I can't change. 
> I can give it multiple queries to run but the
> application assumes that each query will produce
> at least one row and causes an error if that's
> not the case.  I want to cause some rows to be
> inserted into a table but plain insert queries
> don't work because they violate the application's
> assumption that a result will be returned.

Something like:
https://sqlite.org/pragma.html#pragma_count_changes
(deprecated, but might still work)

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

Re: [sqlite] Unable to store 500MB size of row data even after define macro SQLITE_MAX_LENGTH

[Kees Nuyt]

On Thu, 7 Dec 2017 23:59:02 -0700 (MST), Durgesh
<durgeshnmah...@gmail.com> wrote:

> getting error "QSqlError("18", "Unable to bind parameters", "string or blob
> too big")" while inserting a row data of size 500MB.
>
> However insertion of 450 MB is successful.
>
> Tried define the macro SQLITE_MAX_LENGTH with value of
> SQLITE_MAX_LENGTH=20 , larger than 500MB.
>
> pls suggest a way insertion can be done for more than 450 MB of data.
>
> I am using Qt SQL , for interfacing with sqlite db.

What is the databases page size?
How big is your page cache? 
Is cache spill enabled?

See: 
PRAGMA schema.cache_size;
https://sqlite.org/pragma.html#pragma_cache_size
PRAGMA cache_spill; 
https://sqlite.org/pragma.html#pragma_cache_spill
PRAGMA schema.page_size; 
https://sqlite.org/pragma.html#pragma_page_size
PRAGMA schema.cache_size;

Other things to look at:
PRAGMA schema.journal_size_limit;
https://sqlite.org/pragma.html#pragma_journal_size_limit
PRAGMA soft_heap_limit;
https://sqlite.org/pragma.html#pragma_soft_heap_limit

HTH

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

Re: [sqlite] finding the number of records until a value is different

[Kees Nuyt]

On Thu, 07 Dec 2017 19:46:21 +0100, nitpi...@arcor.de wrote:

> Hi all,
>
> I have a DB i.e. like this:
>
> table values
> bctemp
> 35123
> 35124
> 35123
> 20123
> 12123
> 12123
> 16123
> 35123
> 35123
> 35123
> 35123
> 35123
>
> The value in temp (or all the other columns) is not of interest.
>
> Now I a looking for the basic concept how to count the number of rows where 
> bc=bc_of_last_row after the last different bc.
>
> SELECT COUNT(bc) FROM values WHERE bc=35;
>
> gives me the number of all rows with bc=35, so here 8.
> But I am looking for 5, the number of rows after the last change of bc. 

I recently had a similar but different requirement:
Store a series of CPU temperatures over time, but if the
temperature stays the same, just increment a count.
Again, not the same as your problem, but my solution might
inspire you with yours.

CREATE TABLE cputemp (
epoch  INTEGER PRIMARY KEY NOT NULL -- "epochfr"
,   epochto  INTEGER DEFAULT NULL
,   ctempINTEGER NOT NULL
);

CREATE TRIGGER before_ins_cputemp BEFORE INSERT ON cputemp
FOR EACH ROW
WHEN NEW.ctemp == (SELECT ctemp FROM cputemp WHERE epoch ==
(SELECT max(epoch) FROM cputemp))
BEGIN
UPDATE cputemp set epochto = NEW.epoch
WHERE epoch == (SELECT max(epoch) FROM cputemp);
SELECT RAISE(IGNORE); -- do not insert a new row
END;

-- Optional trigger: set epochto in new rows,
-- could be done in other ways.
CREATE TRIGGER after_ins_cputemp AFTER INSERT ON cputemp
FOR EACH ROW
WHEN NEW.epochto IS NULL
BEGIN
UPDATE cputemp SET epochto = NEW.epoch
WHERE epoch == NEW.epoch;
END;

The INSERT looks like (awk code):

printf "INSERT INTO cputemp (epoch,ctemp) " \
"VALUES ( 0 + strftime('%%s','now'),%s);\n",cputemp

This can also be done in other ways of course.

Hope this helps.

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Kees Nuyt]

On Thu, 23 Nov 2017 08:45:25 +, Tim Streater
<t...@clothears.org.uk> wrote:

>On 22 Nov 2017, at 19:49, "Niall O'Reilly" <niall.orei...@ucd.ie> wrote:
>
>> On 21 Nov 2017, at 16:27, Drago, William @ CSG - NARDA-MITEQ wrote:
>>
>>>  Please, not a forum. The email list is instant, dynamic, and convenient. I
>>> don't think checking into a forum to stay current with the brisk activity
>>> here is very practical or appealing.
>>
>> I agree with Bill on this.
>>
>> It seems to me that the idea of re-architecting such a useful communications
>> channel as this mailing list on account of a cluster of false positives 
>> raised
>> by a single provider's triage system would best be characterized as an 
>> example
>> of "the tail wagging the dog".
>
> Well quite. My advice to anyone whose ISP or gmail is
> doing unwanted spam filtering is to switch elsewhere.
>
> And there's no need for 'likes' and related nonsense on a technical list.

+1

I prefer mailing lists and usenet groups. With a proper
threading reader program I just press the spacebar to page
through a message and to the next unread message.
No other medium allows me to consume this kind of info at such a
high speed without getting RSI problems.

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

Re: [sqlite] Unexpected echo when setting locking_mode

[Kees Nuyt]

On Sat, 18 Nov 2017 22:53:26 +0100, Yannick Duchêne
<yannick_duch...@yahoo.fr> wrote:

>I believe I found a tiny bug, nothing bad, but may pollute some output.
>
>Using SQLite version 3.20.1, if I do this:
>
>PRAGMA locking_mode=exclusive;
>
>It writes an unexpected reply:
>
>exclusive

The same happens for 
pragma journal_mode=wal;

, although it's less intrusive than locking_mode , because
journal_mode will typically only be issued at database creation.
-- 
Regards,
Kees Nuyt

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

Re: [sqlite] Valid characters for indentifiers

[Kees Nuyt]

On Sun, 12 Nov 2017 10:19:03 +0100, Clemens Ladisch
<clem...@ladisch.de> wrote:

> Kees Nuyt wrote:
>> It conforms to the SQL standard, you can use the Postgresql docs
>> as a reference.
>
> Actually, neither SQLite nor PostgreSQL conform to the SQL standard.
> [...]

Thanks for the heads-up!

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

Re: [sqlite] Valid characters for indentifiers

[Kees Nuyt]

On Sat, 11 Nov 2017 11:04:37 +, Bart Smissaert
<bart.smissa...@gmail.com> wrote:

> What are the exact rules for valid identifier names (tables, columns and
> indexes)?
> This is both for names enclosed in square brackets ([]) or double quotes
> (") and also for names
> that are not enclosed within square brackets or double quotes.
> Had a good look for this, but couldn't find a clear answer.

It conforms to the SQL standard, you can use the Postgresql docs
as a reference.

https://www.postgresql.org/docs/7.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS

If you want to use a keyword (or an otherwise invalid word) as
an identifier, you have to quote it. SQLite has its own lost of
keywords.
https://sqlite.org/lang_keywords.html

-- 
Regards,

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

Re: [sqlite] shell.c no longer in SCM [WAS: [sqlite-announce] Version 3.21.0]

[Kees Nuyt]

On Wed, 25 Oct 2017 10:09:57 +0200, Dominique Devienne
<ddevie...@gmail.com> wrote:

> About: 18. The src/shell.c source code to the command-line shell is no
> longer under version control. That file is now generated as part of the
> build process.
>
> Could you please briefly describe what sources shell.c is now generated
> from?

That is ./src/shell.c.in .
Have a look at  ./Makefile.in for the dependencies.

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

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

[Kees Nuyt]

On Sun, 15 Oct 2017 18:36:56 -0700 (MST), Fiona
 wrote:

> Thanks for noticing that problem! Follow your instructions, now I'm sure
> it's all because my db file is corrupted.  Is there anything I can do to fix
> it?
>
> Integrity check result:
>  

I can think of three options:

1- Rebuild the database from the original input, 
   with the schema improvements suggested in
   this thread

2- Restore a recent backup, then import the data into
   a new database with the correct schema [*].

3- the recipe that Simon gave to retrieve as much of 
   the contents as possible using the .dump command
   and build a new database from the dump file,
   then import the data into a new database with
   the correct schema [*].


[*] The script for importing data from a database with the old
schema into a database with a better schema loks like this:

sqlite3 newdb.sqite http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

[Kees Nuyt]

On Sun, 15 Oct 2017 17:45:03 -0700 (MST), Fiona
<cxfhn1...@gmail.com> wrote:

>>> Swapping the columns tile_data and tile_id may improve performance
> significantly, especially if the BLOB can get bigger than a database page. 
>
> Thanks for your advice, could you please explain more why is that? 
> The primary key is not change at all, what exectly causes the improvement?

In SQLite, columns are stored in database pages in the order
they are defined. The primary key of any table is usually
accessed more often than any other column, becuase it is either
used by a WHERE clause or the ON clause of a JOIN.

If a BLOB is bigger than a page, SDQLite has to read one or more
so called overflow paged to get to the primary key.

So, it is best practice put keys and all columns with small
contents up front, and all big TEXT and BLOB columns at the end
of the column list, in ascending order of expected size.

HTH

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

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

[Kees Nuyt]

On Thu, 12 Oct 2017 19:32:53 -0700 (MST), Fiona
<cxfhn1...@gmail.com> wrote:

> schema:
> <http://sqlite.1065341.n5.nabble.com/file/t8403/schema.jpg> 

Not related to your problem, just a hint:
Swapping the columns tile_data and tile_id may improve
performance significantly, especially if the BLOB can get bigger
than a database page.

Original:
CREATE TABLE images (
tile_data BLOB
,   tile_id VARCHAR(256) NOT NULL
,   PRIMARY KEY (tile_id)); -- might be slow

Improved:
CREATE TABLE images (
tile_id VARCHAR(256) NOT NULL
,   tile_data BLOB
,   PRIMARY KEY (tile_id)); -- could be faster


-- 
Regards,
Kees Nuyt

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

Re: [sqlite] XOR operator

[Kees Nuyt]

On Mon, 9 Oct 2017 13:35:28 +0200, R Smith <rsm...@rsweb.co.za>
wrote:

>On 2017/10/09 3:07 AM, Richard Hipp wrote:
>> Then you want: a = ~b 
>
> Wow, I missed this, and it works already.  Thank you kindly!
>
> May I suggest adding a small section to the binary/unary operators in 
> the documentation that names each operator and provide a short function 
> description (at least for those that are not covered already, even the 
> "obvious" ones)?

There's the obsolete wiki FAQ:
https://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq
but it misses the ~ operator.

> I never associated the ~ with NOT, and from the replies to this thread 
> it seems this knowledge may be useful to many.
>
>Thanks,
>Ryan
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] Need help with SQL query

[Kees Nuyt]

On Fri, 22 Sep 2017 10:54:21 +0100, John G
<rjkgilles...@gmail.com> wrote:

>I know this is an older thread, but shouldn't that reference be on the ITEM
>table ?  So ...
>
>CREATE TABLE ATTRIBUTES (
>ITEM_ID INTEGER REFERENCES ITEM(ID) ON DELETE CASCADE,
>   KEY   TEXT,
>VALUE TEXT,
>PRIMARY KEY (ITEM_ID,KEY)
>  ) WITHOUT ROWID;
>
>John G

Yes, you are right. I didn't pay enough attention typing that
code. My apologies for any confusion that may have caused.

-- 
Regards,
Kees Nuyt



>On 11 September 2017 at 13:11, Kees Nuyt <k.n...@zonnet.nl> wrote:
>
>> On Mon, 11 Sep 2017 14:05:25 +0200, Kees Nuyt <k.n...@zonnet.nl>
>> wrote:
>>
>> > CREATE TABLE ATTRIBUTES (
>> >  ITEM_ID INTEGER REFERENCES ATTRIBUTES(ID) ON DELETE CASCADE,
>> >   KEY   TEXT,
>> >   VALUE TEXT,
>> >   PRIMARY KEY (ITEM_ID,KEY)
>> > ) WITHOUT ROWID;
>> > CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);
>>
>> Correction:
>> In this construct, it makes no sense to create the index
>> attr_item_id, because the ITEM_ID is the first column
>> of the primary key (which is indexed implicitly).
>>
>> So, you can leave out the CREATE INDEX attr_item_id
>> statement in this case.
>>
>> --
>> Regards,
>>
>> Kees Nuyt
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create table - Error code: 21

[Kees Nuyt]

On Thu, 21 Sep 2017 04:07:36 -0400, 
Papa <p...@arbolone.ca> wrote:

Just a side-note:

>     wapstr += L"id INT PRIMARY KEY NOT NULL, ";

"INT PRIMARY KEY" is not enough to create an alias for ROWID,
that only happenes with "INTEGER PRIMARY KEY" :

~ $ sqlite3 test.sqlite
SQLite version 3.21.0 2017-08-14 01:33:07
Enter ".help" for usage hints.
sqlite> create table t1 (id INT PRIMARY KEY NOT NULL, tx
TEXT);
sqlite> create table t2 (id INTEGER PRIMARY KEY NOT NULL, tx
TEXT);
sqlite> pragma table_info(t1);
0|id|INT|1||1
1|tx|TEXT|0||0
sqlite> pragma table_info(t2); -- looks the same, but:
0|id|INTEGER|1||1
1|tx|TEXT|0||0
sqlite> insert into t1 (id,tx) values (3,'t1');
sqlite> insert into t2 (id,tx) values (3,'t2');
sqlite> select ROWID,id,tx from t1; -- ROWID not aliased
1|3|t1
sqlite> select ROWID,id,tx from t2; -- aliased as intended.
3|3|t2
sqlite>

I'm afraid I don't have an answer to your original question.

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

Re: [sqlite] 'database disk image is malformed' only on the mac

[Kees Nuyt]

On Wed, 20 Sep 2017 06:41:05 -0700 (MST), Fahad
<fa...@2doapp.com> wrote:

[...]
> 1) Thread A: Create a new connection, if one already does not exist for the
> thread. Store it's 'reference count' in the thread storage (I close a
> connection when the count becomes zero).
> 2) Thread A: Get a previously stored statement for that connection against a
> name (using a dictionary for this) from the statement cache (again, from the
> thread local storage) - if one doesn't exist, call sqlite3_prepare_v2 on a
> new statement and save it in the statement cache. Since a single thread can
> always only have a single db connection, the statement is thread-safe.
>
> ... assume some nested asynchronous calls
>
> 3) Thread A: Grab a cached connection, and then grab an existing statement.
> If found, re-use it by first calling sqlite3_clear_bindings(pStmt).

I'm not an expert, but: _clear_bindings() is not enough to clear
the statement context data. To reuse a statement, you'd have to
_reset() it.

> 4) Thread A: Close connection (i.e. decrement the reference count, if it's
> zero first clear the statement cache by calling sqlite3_reset and
> sqlite3_finalize on all the cached statements). In case the reference count
> is't 0, the connection is kept alive. 
[...]

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

Re: [sqlite] Need help with SQL query

[Kees Nuyt]

On Mon, 11 Sep 2017 14:05:25 +0200, Kees Nuyt <k.n...@zonnet.nl>
wrote:

> CREATE TABLE ATTRIBUTES (
>  ITEM_ID INTEGER REFERENCES ATTRIBUTES(ID) ON DELETE CASCADE,
>   KEY   TEXT,
>   VALUE TEXT,
>   PRIMARY KEY (ITEM_ID,KEY)
> ) WITHOUT ROWID;
> CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);

Correction:
In this construct, it makes no sense to create the index
attr_item_id, because the ITEM_ID is the first column
of the primary key (which is indexed implicitly).

So, you can leave out the CREATE INDEX attr_item_id 
statement in this case.

-- 
Regards,

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

Re: [sqlite] Need help with SQL query

[Kees Nuyt]

On Sun, 10 Sep 2017 20:35:16 -0700, Vikas Aditya
<vikas.adi...@quikfynd.com> wrote:

> Hi All,
>
> I need some help in figuring our right query syntax for querying
> items from two tables. We have two tables. One of the table has
> list of items. And Second table has additional attributes.

Adding to the suggestions of Ryan Smith, I would suggest a few
table definition optimizations:

> CREATE TABLE ITEM (
> ID INTEGER,
> FIELD0 TEXT
> FIELD1 TEXT,
> FIELD3 TEXT,
> FIELD4 TEXT
> );

By adding 'PRIMARY KEY to the ÍD column, it becomes an alias for
the internal ROWID column, saving space. Also, to reference a
parent table, the reference needs to point to a unique column. 
A primary key fulfills that requirement.

CREATE TABLE ITEM (
ID INTEGER PRIMARY KEY,
FIELD0 TEXT
FIELD1 TEXT,
FIELD3 TEXT,
FIELD4 TEXT
);

> CREATE TABLE ATTRIBUTES (
> ID INTEGER PRIMARY KEY,
> ITEM_ID INTEGER,
> KEY TEXT,
> VALUE TEXT
> );

Do the same for the primary key of the attributes table.
Add a foreign key constraint to formalize the relation
between the parent and child table. This provides 
"referential integrity".
The index will often speed up JOIN operations.

CREATE TABLE ATTRIBUTES (
  ID INTEGER PRIMARY KEY,
  ITEM_ID INTEGER REFERENCES ATTRIBUTES ON DELETE CASCADE,
  KEY   TEXT,
  VALUE TEXT
);
CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);

Even better, the ID column can be left out, and a different
primary key will enforce that the same KEY can only be used once
for any ITEM_ID:
 
CREATE TABLE ATTRIBUTES (
  ITEM_ID INTEGER REFERENCES ATTRIBUTES(ID) ON DELETE CASCADE,
  KEY   TEXT,
  VALUE TEXT,
  PRIMARY KEY (ITEM_ID,KEY)
) WITHOUT ROWID;
CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID);

Note: 
Foreign key constraints are only enforced when 
   PRAGMA foreign_keys=on;
is executed after opening the database, before any
INSERT/UPDATE/DELETE statement is issued.

HTH

-- 
Regards,

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

Re: [sqlite] acf3b9cc9c3932431979995a1dceacc06c659ab400fad95ce3728ff8895a022b : Off by one on the column names

[Kees Nuyt]

On Sun, 09 Jul 2017 12:12:15 -0600, "Keith Medcalf"
<kmedc...@dessus.com> wrote:

>
>Off by one on the names:
>
>sqlite> values (1,2), (2,3);
>column1|column2
>1|2
>2|3
>
>sqlite> select * from (values (1,2), (2,3));
>column0|column1
>1|2
>2|3

Solved in
=== 2017-07-09 ===
18:55:29 [70096c505d] *CURRENT* Always make "column%d"
column-names 1-based, never 0-based. (user: drh tags: trunk)

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

Re: [sqlite] table-naming-expression impact on sqlite3_prepare

[Kees Nuyt]

On Sun, 26 Mar 2017 15:34:22 -0700, petern <peter.nichvolo...@gmail.com>
wrote:

> Here is your suggestion with matched brackets and quotes and assuming
> mytable has a column [tablename]:
>
> select eval(printf('create table %s (a,b,c)',tablename)) from mytables;

I think you mean:

eval(printf('create table %s (a,b,c)',(select tablename from mytables)));

It's something that can be done by any host language. No need to implement
that in SQL.

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

Re: [sqlite] foreign key cardinality

[Kees Nuyt]

On Tue, 28 Feb 2017 11:42:23 -0500, "James K. Lowden"
<jklow...@schemamania.org> wrote:

> I have always thought af a foreign key as an existence test.  Looking
> around, I see that other implementations also often require the
> referent to be unique.  I'm not convinced that's justified
> theoretically, but at the moment I can't check against my usual
> resources.  

Here you go:

Information Technology - Database Language SQL
(Proposed revised text of DIS 9075)
(Second Informal Review Draft) ISO/IEC 9075:1992, 
Database Language SQL- July 30, 1992
which is pretty close to the final text.

X3H2-92-154/DBL CBR-002  (page 274 or thereabout)

11.8  

Function

Specify a referential constraint.

Format

 ::=
 FOREIGN KEY   
   

 ::=
 REFERENCES 
   [ MATCH  ]
   [  ]

 ::=
   FULL
 | PARTIAL

 ::=
 

 ::=
  []

 ::= 

 ::=
[  ]
 |  [  ]

 ::= ON UPDATE 

 ::= ON DELETE 

 ::=
   CASCADE
 | SET NULL
 | SET DEFAULT
 | NO ACTION


Syntax Rules

1) Let referencing table be the table identified by the containing
or . Let referenced
   table be the table identified by the  in the . Let referencing columns be the column
   or columns identified by the  in the
and let referencing column be one such
   column.

2) Case:

   a) If the  specifies a , then the set of column names of that  shall be equal to the set of column names
 in the unique columns of a unique constraint of the refer-
 enced table. Let referenced columns be the column or columns
 identified by that  and let refer-
 enced column be one such column. Each referenced column shall
 identify a column of the referenced table and the same column
 shall not be identified more than once.

   b) If the  does not specify a
 , then the table descriptor of the
 referenced table shall include a unique constraint that spec-
 ifies PRIMARY KEY. Let referenced columns be the column or
 columns identified by the unique columns in that unique con-
 straint and let referenced column be one such column. The
  shall be considered to implic-
 itly specify a  that is identical to
 that .

3) The table constraint descriptor describing the  whose  identifies the
   referenced columns shall indicate that the unique constraint is
   not deferrable.


-- 
Regards,

Kees Nuyt

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

Re: [sqlite] How to circumvent UNIQUE constraint

[Kees Nuyt]

On Sat, 21 Jan 2017 11:54:57 +0100, Cecil Westerhof
<cldwester...@gmail.com> wrote:

>I have the following (work in progress) table:
>CREATE  TABLE desktops(
>nameTEXTNOT NULL PRIMARY KEY,
>indexNo INTEGER NOT NULL UNIQUE,
>value   TEXTNOT NULL UNIQUE,
>waitSeconds INTEGER NOT NULL
>);
>
>?I want to insert a record in front of? the others, so indexNo has to be
>increased with one for all records. I would think that this would work:
>UPDATE desktops
>SET indexNo = indexNo  + 1
>
>But it does not, it gives:
>Error: UNIQUE constraint failed: desktops.indexNo
>
>?How can I make this work?

Considering there is no constraint on indexNo with respect to
negative or zero values, I would suggest:

INSERT INTO desktops (name,indexNo,value,waitSeconds) 
VALUES ('thename',(SELECT min(indexNo) FROM desktops) - 1,
'thevalue',thewaitseconds);

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] Insert into with Id...

[Kees Nuyt]

Dear Héctor, 

On Fri, 13 Jan 2017 21:38:04 -0500, "hfiandor"
<hfian...@ceniai.inf.cu> wrote:

> I have implemented the lectura of a csv file in my program as
> "import". I have followed yours instructions (the last one and
> others previous) . Thanks for your instructions.
> 
> The program works fine with csv files of one or two rows, but
> when I try to read a file with 9 rows, it fails.

In what way does it fail?
Does it fail silently or are there any error messages?
After the failure, are there any rows inserted, or none at all?
Does the program check and interpret the status of each sqlite
API call?
 
> I have reviewed the routines and not found the errors.
> 
> Please, I suspect that I have missed something. If you have
> any suggestion, I will appreciate very much.
> 
> If you need the procedure "import" I can send to you.

That depends on the answers to my questions above.

> Thanks in advance,
> 
> Ing. Héctor F. Fiandor Rosario

Hope this helps,

-- 
Regards,
Kees Nuyt

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

Re: [sqlite] Exiting SQLite3 from within a (.bat) file

[Kees Nuyt]

On Fri, 13 Jan 2017 08:12:33 + (UTC),
"tbuck...@frontier.com" <tbuck...@frontier.com> wrote:

> Hello,I have a batch (.bat) file that runs a C program to
> pre-process a text file to a clean .csv file. 
> Then the batch file runs SQLite3 -init to execute dot (.)
> commands.  Everything works great except the last dot
> command ".exit" or ".quit" to close the SQLite3 program
> out.  I always have to manually enter .exit to get back
> to the command prompt.  What can I do to have SQLite3
> automatically exit?

> The batch file has the following commands:
>   cls
>   SOE_sort.exe %1
>   sqlite3.exe -init SOE_sortRC.sqliterc

Try:
   cls
   SOE_sort.exe %1
   sqlite3.exe https://www.sqlite.org/src/artifact?name=6095531aa900decd=5634-5650

A script as redirected input _does_ execute .quit, it even exits
without .quit when the stream reaches end of file.

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] Foreign key error...

[Kees Nuyt]

On Sun, 8 Jan 2017 04:21:16 -0600, Ken Wagner
<beauco...@gmail.com> wrote:

>Hello SQLusers,
>
> The error below occurs even though the
>
> CREATE TABLE track(
>
>   trackid INTEGER,
>   trackname   TEXT,
>   trackartist INTEGER,
>   *FOREIGN KEY(trackartist) REFERENCES artist(artistid)*
> );
>
> statement at https://sqlite.org/foreignkeys.html was observed.
>
> It appears that 'trackerartist' should be named 'artistid'.
>
> SQLite3 CLI results using version SQLite 3.15.2 2016-11-28 19:13:37 
> bbd85d235f7037c6a033a9690534391ffeacecc8
>
> sqlite> select artistname, trackname from artist inner join track using 
> (trackartist);
> Error:\ cannot join using column trackartist - column not present in 
> both tables
> sqlite> .tables track
> track
> sqlite> .schema track
> CREATE TABLE track(
>   trackid INTEGER,
>   trackname   TEXT,
>   trackartist INTEGER,
>   FOREIGN KEY(trackartist) REFERENCES artist(artistid)
> );
> sqlite> .schema artist
> CREATE TABLE artist(
>   artistidINTEGER PRIMARY KEY,
>   artistname  TEXT
>
> Am I missing something important here?

The error message is quite informative: the artist table does
not have a column trackartist.

Try:
select artistname, trackname from artist inner join track on
trackartist = artistid;

HTH

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

Re: [sqlite] Simplify multiple use of value in a trigger

[Kees Nuyt]

On Sat, 7 Jan 2017 09:27:57 -0500, Gan Uesli Starling
<g...@starling.us> wrote:

> So I'm trying to accumulate data for state/prov inside of USA, Canada 
> and Mexico, and country for the rest of the world.
>
> Since country and state from the same update are factors in incrementing 
> each of four tables, I was hoping to simplify it with a single select 
> with 'AS' and then re-use the AS-value in every case. This as opposed to 
> the redundancy of selecting the same thing anew every time. See as 
> below. It passes syntax but fails on exectute, saying there is no column 
> 's'. Is there some way to do this? To hold the 'state' and 'country' and 
> reuse them like a variable from one query to the next for sequential 
> comparisons?

SQLite doesn't support variables in the way you try to use here.

You can try one of the following:

1- Replace every c and s in the UPDATE statements 
   by (SELECT ...)

2- prefix every UPDATE with a common tabel expression 
   that delivers the reuired values, and use them with
   a (SELECT ...) as above

3- perhaps do something smart with an updateble view using an 
   INSTEAD OF trigger, and supply the values once to the 
   UPDATE myview statement.

> -- Trigger for incrementing country & region counts for 160m band, 
> QSO-by-QSO.
> DROP TRIGGER IF EXISTS _160m_Increment;
> CREATE TRIGGER _160m_Increment
> AFTER UPDATE ON qso WHEN
> NEW.freq LIKE '1.%'
> BEGIN
> SELECT country AS c FROM qth WHERE rowid = NEW.qth_other_id;
> SELECT state   AS s FROM qth WHERE rowid = NEW.qth_other_id;
> UPDATE usa_state SET _160m = 1 + (SELECT _160m FROM usa_state WHERE 
> state = s)   WHERE state = s AND c = 'USA';
> UPDATE mx_state  SET _160m = 1 + (SELECT _160m FROM mx_state WHERE 
> state = s)   WHERE state = s AND c = 'Mexico';
> UPDATE ca_prov   SET _160m = 1 + (SELECT _160m FROM ca_prov WHERE 
> prov  = s)   WHERE prov  = s AND c = 'Canada';
> UPDATE world SET _160m = 1 + (SELECT _160m FROM world WHERE 
> country = c) WHERE country = c;
> END;

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

[Kees Nuyt]

On Mon, 17 Oct 2016 15:40:44 -0500, Ben Newberg
<ben.newb...@gmail.com> wrote:

> But I've seen some strange things when messing with pragma writable_schema.
> It appears all bets are off?

Yes. that's why there's a warning:
"Warning: misuse of this pragma can easily result in
 a corrupt database file."

When changing anything in the sqlite_master table you are
circumventing all mechanisms SQLite has to keep the database
consistent. 
What you did in your example is creating a table description in
sqlite_master that doesn't match the physical table in the
database, so yes, all bets are off.

-- 
Regards,

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

Re: [sqlite] Trigger WHEN condition, comparing with null

[Kees Nuyt]

On Thu, 13 Oct 2016 11:45:14 +0200, Daniel Polski
<dan...@agelektronik.se> wrote:

That's because the expression in the WHEN clause
(new.value != old.value)
is not valid when one of them is NULL.

HTH

-- 
Regards,
Kees Nuyt



>Hello,
>I guess there is something I'm missing when trying to synchronize some 
>data with a trigger.
>How do I get the trigger to fire when the comparison in either new.x or 
>old.x is null?
>
>The below tested with 3.8.6 sqlite command line shell:
>
>CREATE TABLE table1(
> idINTEGER PRIMARY KEY,
> valueINT
>);
>
>CREATE TABLE table1_mirror(
> idINT,
> valueINT
>);
>
>CREATE TRIGGER trigger_1
>AFTER UPDATE OF value ON table1
>WHEN (new.value != old.value)
>BEGIN
> UPDATE table1_mirror
> SET value = new.value
> WHERE id = new.id
> ;
>END;
>
>INSERT INTO table1 VALUES(1, 1);
>INSERT INTO table1_mirror VALUES(1,1);
>
>UPDATE table1 SET value = 2 WHERE id = 1;
>SELECT value from table1; -- 2
>SELECT value from table1_mirror; -- 2
>
>UPDATE table1 SET value = 3 WHERE id = 1;
>SELECT value from table1; -- 3
>SELECT value from table1_mirror; -- 3
>
>UPDATE table1 SET value = null WHERE id = 1;
>SELECT value from table1; -- null
>SELECT value from table1_mirror; -- still 3 , why not null?
>
>UPDATE table1 SET value = 4 WHERE id = 1;
>SELECT value from table1; -- 4
>SELECT value from table1_mirror; -- still 3 , why not 4?
>
>UPDATE table1 SET value = 5 WHERE id = 1;
>SELECT value from table1; --5
>SELECT value from table1_mirror; --5
>
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CREATE FUNCTION

[Kees Nuyt]

On Mon, 22 Aug 2016 18:46:30 +, "Rousselot, Richard A"
<richard.a.rousse...@centurylink.com> wrote:

> Are there any plans to add a CREATE FUNCTION function for SQLite? 
> Is this an SQLite4 type thing?

Many things you would define a SQL function for can also be
accomplished with Common Table Expressions (CTE, WITH) and/or
triggers (especially INSTEAD OF triggers on views).

> Something similar PostgreSQL?
> https://www.postgresql.org/docs/9.5/static/sql-createfunction.html
>
> This particular class of function is not mentioned in "SQL Features
> That SQLite Does Not Implement" and it is not in "SQL As Understood
> By SQLite"; kind of a grey area, in my mind, for future support.

Well,SQLite is supposed to be "lightweight", so I wouldn't count
on future support.
Plus what Clemens Ladisch and Richard Hipp said.

-- 
Regards,
Kees Nuyt

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

Re: [sqlite] Locking databases - Possibly (probably?) a dumb question

[Kees Nuyt]

One minor optional addition below

On Sat, 20 Aug 2016 18:01:46 +0100, Simon Slavin
<slav...@bigfraud.org> wrote:

>
>On 20 Aug 2016, at 5:56pm, Lev <leventel...@gmail.com> wrote:
[...] 
>> So this 'setting' is stored in the database file? Is it enough to do the
>> PRAGMA when the database is created?
>
> Yes and yes, but do it this way.
>
> 1) Create the database file by opening it.

1a) If you need it, before creating any tables:
PRAGMA page_size=;

> 2) Do something that makes the file non-blank,
>like creating a table.
> 3) Issue "PRAGMA journal_mode=WAL"
> 4) Close the file
>
> From that point onwards that database is in WAL mode
> and everything opening it automatically knows that. 
> You do the above sequence using your own software
> or the SQLite command-line shell tool.
>
> Simon.

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

Re: [sqlite] Conversion failure

[Kees Nuyt]

On Thu, 23 Jun 2016 17:49:04 -0400, Igor Korot
<ikoro...@gmail.com> wrote:

> Yes, it is a PC (a laptop with Windows).
> OK, I understand and its unfortunate, but that's life.
>
> Now the question is: what is the best way to fix it?
> From the link I posted it sounds like there is a byte sequence before
> the string, which identifies the encoding.

The unicode character for "LATIN SMALL LETTER SHARP S" (the
es-zet ligature) is U+00DF, which is UTF-8 sequence c3 9f (hex).

Ref: 
http://www.utf8-chartable.de/
http://www.fileformat.info/info/charset/UTF-8/list.htm
http://dev.networkerror.org/utf8/

> Will this help?

Hope this helps.

-- 
Regards,

Kees Nuyt

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

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Kees Nuyt]

On Wed, 18 May 2016 11:39:28 +0200, Cecil Westerhof
 wrote:

> I would be interested what you find wrong about Git and is better in your
> version control system.

Check the archives of the fossil-users mailing list
fossil-users at lists.fossil-scm.org
http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users

Several mailing list archives carry this list, e.g.:
http://www.mail-archive.com/fossil-users at lists.fossil-scm.org/

-- 
Regards,
Kees Nuyt

[sqlite] Podcast with Dr Hipp: SQLite history, success and funding

[Kees Nuyt]

On Mon, 16 May 2016 19:14:01 +, Objective C
 wrote:

> Hi sir,
> In fact, i have an issue with SQLite database Restore using c#

That's not what this discussion thread is about, it would have
been better if you sterted a new thread, but anyway.

> i'm coding a button which can restore an existing 
> SQLite database with '*.db*' extension.

> i wonder if you can guide me please,

Can you describe what you already tried, what happened, and what
made you think you didn't succeed?

There are (at least) two methods:
1- use the SQLite backup API
  See: www.sqlite.org/c3ref/backup_finish.html

2- copy the backup database over the database
  using file system operations

In the latter case you'll have to make sure 
- the backup database is consistent and does not 
  have a 'hot' journal
- the database you are going to overwrite is not 
  used by any databse connection
- the journal of the database you are overwriting 
  (if any) is removed.

> i'll ber very grateful
> Sincerely yours
> Hashim

-- 
Regards,

Kees Nuyt

[sqlite] How to get 1 row with no null columns

[Kees Nuyt]

On Wed, 11 May 2016 06:26:23 -0400, William Drago
 wrote:

> All,
>
> Is there a simple way to find a row in a table where none of 
> columns contain a null value? For example:
>
> SELECT * FROM AnyTable WHERE (all columns IS NOT NULL) LIMIT 1;
>
> Or do I have to do this manually in my application scanning 
> every column in every row until I find a row with no nulls?

You can express your criteria in the WHERE clause, by naming all
columns:

SELECT * FROM AnyTable 
WHERE col1 IS NOT NULL
  AND col3 IS NOT NULL
  AND col3 IS NOT NULL
:
: (etc)
:
LIMIT 1;

Your application could compose the WHERE clause using the output
from PRAGMA table_info(AnyTable) to iterate over all columns.

> Thanks,
> -Bill

-- 
Regards,

Kees Nuyt

[sqlite] output from sqlite3 .dump not going where I expected

[Kees Nuyt]

On Sun, 08 May 2016 15:42:29 +0100, Jeremy Nicoll
 wrote:

> This doesn't (or does it?) explain why   .output
> "C:\a\valid\path\file.txt"   doesn't work, though.  
> Why would sqlite3 decide that this meant an unpathed file named
> "avalidpathfile.txt" ?

The backslash '\' is an escape character, and would cause the
next character to have a special meaning if it belongs to a
specific set of characters.

Since many years you can safely use forward slashes '/' as a
path separator in MS Windows command lines, except in legacy
utilities that expect the forward slash as an option character
(copy /b ..., dir /s ...  etc.).

"C:/a/valid/path/file.txt" will do fine.

-- 
Kind Regards,

Kees Nuyt

[sqlite] .DUMP output compatibility

[Kees Nuyt]

On Fri, 6 May 2016 01:56:47 +0300, "Tony Papadimitriou"
 wrote:

> Windows!

There's gawk for windows, for this purpose almost the same as
sed. And apart form cygwin, there is a unch of unix utilities
under the name of UnxUtils.
See https://en.wikipedia.org/wiki/UnxUtils
and https://sourceforge.net/projects/unxutils/


-- 
Regards,
Kees Nuyt



>-Original Message- 
>From: Richard Hipp
>Sent: Friday, May 06, 2016 1:55 AM
>To: SQLite mailing list
>Subject: Re: [sqlite] .DUMP output compatibility
>
>On 5/5/16, Tony Papadimitriou  wrote:
>> Is it possible for .DUMP to produce table/field names quoted with `
>> (backquote) instead of ? (double quote) for compatibility with MySQL?
>> Or is this already adjustable by some setting I missed?
>
>Pipe the output through sed (https://en.wikipedia.org/wiki/Sed)

[sqlite] autoincrement

[Kees Nuyt]

On Wed, 13 Apr 2016 10:58:54 -0400, Igor Korot
 wrote:

> Hi,,
>
>On Wed, Apr 13, 2016 at 10:54 AM, J Decker  wrote:
>> Yes, you can get the create statement from sqlite_master table
>
> I was kind of hoping for a simpler solution so that not to
> parse "CREATE TABLE" statement...
>
> Well, I guess I will have to.

With AUTOINCREMENT, the last autoincremented primary key value
is tracked in a table called sqlite_sequence.

$ sqlite3 test2.db
SQLite version 3.8.12 2015-10-07 00:35:18
Enter ".help" for usage hints.
sqlite> create table t1 (id INTEGER PRIMARY KEY, tx TEXT);
sqlite> create table t2 (id INTEGER PRIMARY KEY AUTOINCREMENT,
tx TEXT);
sqlite> INSERT INTO t1 (tx) VALUES ('one'),('two');
sqlite> INSERT INTO t2 (tx) VALUES ('one'),('two');
sqlite> SELECT name FROM sqlite_master WHERE type='table';
t1
t2
sqlite_sequence
sqlite> SELECT * FROM sqlite_sequence;
t2|2
sqlite>

Hope this helps


>>
>> On Wed, Apr 13, 2016 at 4:54 AM, Igor Korot  wrote:
>>> Hi,
>>> Is it possible to get whether the column is set to autoincrement or not?
>>>
>>> PRAGMA table_info() does not give such info...
>>>
>>> Thank you.

-- 
Regards,
Kees Nuyt

[sqlite] "Circular" order by

[Kees Nuyt]

On Fri, 11 Mar 2016 09:05:27 +0200, R Smith  wrote:

>
>On 2016/03/11 5:52 AM, Stephen Chrzanowski wrote:
>> On Thu, Mar 10, 2016 at 2:16 PM, R Smith  wrote:
>>
>>
>>> I do this kind of thing so often when filling a selection box for instance:
>>>SELECT 'None'
>>> UNION ALL
>>>SELECT City FROM Countrylist WHERE Country = :1
>>> UNION ALL
>>> SELECT City FROM Countrylist WHERE Country <> :1 ORDER BY City
>>>
>>> Which, as you can deduce, adds a 'None' to the option list, then the
>>> selected country's capital city, then the other cities in alphabetical
>>> order. I now think I need a more sophisticated method to ensure that output
>>> doesn't get mangled. If SQLite ever changes this behaviour, lots of things
>>> will break for me, but, that's life, I will start fixing them all.
>>>
>>> Heh, assumptions... that'll teach me! :)
>>> Ryan
>>>
>> Not that I want to hijack the thread, but with the country list I got from
>> here:
>> https://raw.githubusercontent.com/umpirsky/country-list/master/data/en/country.sqlite.sql
>>
>> I came up with this simple modification to your query:
>>
>> SELECT 'None',0 as OrderNum
>> UNION ALL
>>SELECT Value,1  FROM List WHERE Value = :1
>> UNION ALL
>>SELECT Value,2 FROM List WHERE Value <> :1 ORDER BY OrderNum,Value
>
>Yeah, though I think this one might still be in danger of not ending up 
>in the correct order since the UNIONed section, although able to order, 
>doesn't guarantee order, nor influences the order in the other UNIONed 
>sections and output as a whole... a bit weird, but understandable. So 
>the foolproof way I think would be adapting your advice into this:
>
>SELECT V FROM (
> SELECT 'None' AS V, 0 as Ord
>   UNION ALL
> SELECT Value,1 FROM List WHERE SomeIndex == :1
>   UNION ALL
> SELECT Value,2 FROM List WHERE SomeIndex <> :1
>) ORDER BY Ord, V

That is not needed, as http://www.sqlite.org/lang_select.html#orderby
says:

"The ORDER BY clause

[]

In a compound SELECT statement, only the last or right-most simple
SELECT may have an ORDER BY clause. That ORDER BY clause will apply
across all elements of the compound. If the right-most element of a
compound SELECT is a VALUES clause, then no ORDER BY clause is allowed
on that statement."

> Easy enough, but alas!, the amount of places I have to go and change... :)

HTH ;)

-- 
Regards,
Kees Nuyt

[sqlite] Multithreaded SQLite

[Kees Nuyt]

On Wed, 9 Mar 2016 17:18:15 -0500, Philippe Riand 
wrote:

> Yes, I?m using prepare(), step and finalize(). The 2 threads should
> actually have no interaction between them, but isolated.
> I mean the 2 threads should be able to do very different
> things (read data, write data?), within separated transactions.

You'd have to give every thread its own connection to make them
independent with respect to transaction context.

-- 
Regards,
Kees Nuyt

[sqlite] Encrypt the SQL query

[Kees Nuyt]

On Fri, 26 Feb 2016 14:39:50 +0800,  wrote:

> To encrypt the SQLite database, I can only find the following extension:
>
>  https://www.sqlite.org/see/doc/trunk/www/readme.wiki
>
> So I must recompile and enable the extension to encrypt
> the database, is that correct?

That's almost correct, you also have to buy a perpetual license for the
proprietary SEE extension:
http://www.sqlite.org/support.html 
http://www.hwaci.com/sw/sqlite/see.html

-- 
Regards,

Kees Nuyt

[sqlite] NuGet problem

[Kees Nuyt]

On Thu, 18 Feb 2016 13:37:59 +1100,  wrote:

> Thanks for the answer. At least now I know why.
>
> I have already written all the P/Invoke code. It's written, tested, working
> fine, using the sqlite3.dll size 658,797. Having the header and EXE is
> convenient too, in a development context.
>
> I was able to install the package you suggest, but there is nothing in it
> that looks like the DLL I've been using. If it's a drop-in replacement then
> I'd happily go with it, but if it means rewriting low level code, I'd rather
> not. I checked the documentation, and they really look rather different. 

If all you need is the sqlite3 C API, the Precompiled Binaries for
Windows on the download page provide just that:
http://www.sqlite.org/download.html


-- 
Regards,

Kees Nuyt

[sqlite] a sqlite database error

[Kees Nuyt]

On Wed, 3 Feb 2016 14:22:40 +0800, Frank wrote:

> Hello, my name is Frank. Two days ago, I operated sqlite
> database, and got an error, I don't known what the error
> means, so I hope if you see the email, please respond me.
> Thank you very much!

To get an answer, your question has to be more specific.
- What program did you use? SQLite command line tool?
- What SQL statement did you execute when the error occured?
- What was the exact error message?
- What is the schema of the database?
- Any other related circumstances you can think of.

-- 
Regards,

Kees Nuyt

[sqlite] Performance issue with CTE

[Kees Nuyt]

On Thu, 1 Oct 2015 13:40:23 +0200, Clemens Ladisch
 wrote:

> OFFSET is inefficient because the database still has to compute all the
> rows before skipping over them.
>
> To do paging, remember the first and last date values on the page, and
> for the previous/next page, just continue from there:
>
>  SELECT ...
>  FROM MyTable
>  WHERE date > :LastDateOnPreviousPage
>  ORDER BY date
>  LIMIT 25;
>
> (If dates are not unique, you have to use more columns.)

Indeed. More info on this technique:
http://sqlite.org/cvstrac/wiki?p=ScrollingCursor 
(page is obsolete but still works)

-- 
Regards,

Kees Nuyt

[sqlite] Odd download file names

[Kees Nuyt]

On Tue, 29 Sep 2015 04:01:39 -0400, Stephen Chrzanowski
 wrote:

> Thanks, it does.
>
> I'm working under the Win7/64 environment doing the builds using the C++
> Builder from Bloodshed, but I do speak Linux, so I can follow along with
> what you're saying here.
>
> I've found "fuel" and playing around with that to see if I can go back into
> history instead of downloading the packages.  This repo is a little bit
> different from what I'm used to (But so is git and svn, and I'm just
> beginning to wrap my head around those two) so I'm seeing if I can get fuel
> to put those two files back in history so I can recompile.  If I come up
> with a procedure, I can turn off the script. :]

A fossil checkout can go back to any point in time on any
branch. 

fossil open FILENAME ?VERSION? ?OPTIONS?
fossil update ?OPTIONS? ?VERSION? ?FILES...?

In fact you can have multiple checkouts at the same time, each
in a dedicated directory, each for a different point on the
timeline, selected by the ?VERSION? (sha1-hash or symbolic name)
of the checkin/commit you want to follow.


-- 
Regards,
Kees Nuyt

[sqlite] Odd download file names

[Kees Nuyt]

On Tue, 29 Sep 2015 02:01:03 -0400, Stephen Chrzanowski
 wrote:

> For the past year, I've had a script that runs daily that reads all the
> links off of http://sqlite.org/download.html and downloads anything that is
> missing.  It has been a long while since I've looked at this particular
> section of my NAS, but thought I'd bring my repositories up to snuff, and
> build my own DLLs against each revision posted again.  (So, yes, Richard,
> as a prank, you could slap a 1tb garbage file up and my script would
> dutifully download it  My ISP would LOVE more of my money)
> 
> First, I noticed that there hasn't been anything posted new for the
> amalgamation a long while (July 30 for 3.08.11.01 according to my file
> systems time stamp), so thinking that my script broke for whatever reason,
> curiosity bit me.  I checked the download site and no, things seem to be
> working.  So a testament to stability, both for the code, and surprisingly
> for my script!!
> 
> Second, I noticed that on the download page itself TODAY has no reference
> to files named "sqlite-amalgamation-MMDDHHmm.zip" yet I have a bunch of
> them in my archives.  Are these the files put up for the purpose of the
> pre-releases of a finalized build and my script is working better than
> expected??? (I might get the script to filter those files out, but being a
> digital file packrat)
> 
> Third, I don't know if it is something that can be done now, but I've
> noticed that on a very few of these downloads, I'm getting file sizes in
> the 5kb range.  Looking at the raw bytes of the zip file in a text editor
> is a bit strange, but it looks like the .zip file was downloaded as an HTML
> file.  (As in, rename the zip file to .TXT and open up in notepad, kinda
> raw content).  I'm thinking that the web server didn't find the file when I
> requested so just spit out the page.
> 
> Fourth, a little bit of hand-holding might be needed, but do the zipped
> archives of the released amalgamations (Not the dated files) exist on the
> web server somewhere, or can I get the zip from the SQlite repo?  If only
> from the repo, where can I get either the .c/.h or .zip file for older
> versions?  (Just in case I miss a revision in the future because of that
> 5kb thing) -- Maybe because of that 5kb thing I should write into my script
> that any zip file that is less than 100kb should be just flat out deleted.
> I'm a somewhat interested in getting older versions for the sake of just
> having them, and going back to them in case I want/need them.

By far the easiest way to follow revisions and build older
versions is to follow the repository, which is managed by
fossil.
http://fossil-scm.org/index.html/doc/trunk/www/quickstart.wiki

Clone the repository using fossil:
  fossil clone  http://www.sqlite.org/cgi/src \
 ~/var/fossil/repo/sqlite3.fossil

Open a checkout in a dedicated directory:
  mkdir -p ~/src/sqlite3# once
  cd ~/src/sqlite3
  fossil open ~/var/fossil/repo/sqlite3.fossil trunk

Update it periodically:
  cd ~/src/sqlite3
  fossil pull

and update the checkout:
  cd ~/src/sqlite3
  fossil update trunk # or any other point on the timeline

Building the amalgamation is a matter of:
  mkdir -p ~/bld/sqlite3# once
  cd ~/bld/sqlite3
  ./configure [ options ]

Then you can i.e. build the command line tool
and sqlite3_analyzer:
  cd ~/bld/sqlite3
  make clean
  make sqlite3.c
  make tclsqlite3.c
  gcc ${CFLAGS} -o sqlite3 sqlite3.c \
 ../../src/sqlite3/src/shell.c
  make sqlite3_analyzer

Your build can grab the version in the
generated sqlite3.h in the build directory:
#define SQLITE_VERSION"3.8.12"
#define SQLITE_VERSION_NUMBER 3008012
#define SQLITE_SOURCE_ID  "2015-09-26 17:44:59
33404b2029120d4aabe1e25d484871810777e934"

Hope this helps.

-- 
Regards,
Kees Nuyt

[sqlite] Any database unique ID across multiple connections ?

[Kees Nuyt]

On Thu, 24 Sep 2015 11:42:27 +0200, ALBERT Aur?lien
 wrote:

>Hi,
>
>I'm using SQLite C API and my application use multiple databases, each 
>database having multiple connections.
>I need to identify which connection use the same database as another 
>connection.
>For the moment, I use the absolute database filename as an "unique database 
>identifier", and I store this information with my "sqlite3*" connection handle.
>Is there any simpler/safer way to get a unique database identifier across 
>multiple connections ? (for example, if one day I need the same about 
>":memory:" databases, bad things are gonna to happen)
>Maybe there is already a database identifier in the SQLite API and I missed it 
>?

I think 
 https://www.sqlite.org/pragma.html#pragma_application_id
is applicable.

-- 
Regards,

Kees Nuyt

[sqlite] vdbeUnbind return occasionally Error

[Kees Nuyt]

On Mon, 21 Sep 2015 14:26:54 +0200, Shuhrat Rahimov
 wrote:

> [...] I think I have found the problem. I do the
> following: call sqlite3_bind_text() and then if successful I call
> sqlite3_step() on the prepared statement and then call sqlite3_reset().
> This sequence is called 20 times as one SQL transaction. I have noticed
> that the problem occurs after about 20 transactions since power on. I have
> noticed that before sqlite3_bind_text() returns Error 21, the
> sqlite3_step() returns NO_MEM error. So, here maybe I am simply running out
> of RAM. I have only 160 K of RAM. What could I do here in order to free
> memory after sqlite3_step() for further operations.

The simplest way to reduce memory footprint is to tune page_size
and cache_size with the apropriate PRAGMAs.

-- 
Regards,

Kees Nuyt

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

[Kees Nuyt]

On Sat, 19 Sep 2015 11:29:37 +0200, gwenn 
wrote:

>Hello,
>Is there any way to know when a prepared statement is recompiled ?
>For example:
>
>rc = sqlite3_prepare_v2(db, "SELECT * FROM test", -1, , NULL);
>...
>cc = sqlite3_column_count(stmt);
>...
>rc = sqlite3_exec(db, "ALTER TABLE test ADD COLUMN data DEFAULT
>'missing'", NULL, NULL, NULL);
>...
>rc = sqlite3_step(stmt);
>// how to know that the column count is not good anymore ?

http://www.sqlite.org/rescode.html#schema

-- 
Regards,

Kees Nuyt

[sqlite] Query

[Kees Nuyt]

On Thu, 3 Sep 2015 15:03:27 +0530, manash b
 wrote:

>Hello,
>
>
>I am using SQLite version 3.8.11.1 and PHP for my web application.
>
>Table *employee* has 4 columns. Last two columns have default NULL values.
>
>Insert query -
>
>
>
>*INSERT INTO employee SELECT 3 AS 'eid','manash' AS 'name'
>   UNION SELECT 2,'ram'UNION** SELECT 1,'rahim'*
>
>
>Getting error -
>
>["SQLSTATE[HY000]: General error: 1 table employee has 4 columns but 2
>values were supplied
>
>
>How can i handle this? Please let me know.

I think you are looking for:

INSERT INTO employee (eid,name) 
VALUES (3,'manash'),(2.'ram'),(1,'rahim');

http://www.sqlite.org/lang_insert.html

-- 
Regards,

Kees Nuyt

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

[Kees Nuyt]

On Fri, 28 Aug 2015 14:45:26 +, "Rousselot, Richard A"
 wrote:

> I have noticed that SQLite Query Browser is running slower
> than other IDEs, including SQLitespeed, for some reason. 
> Even when each IDE is set to using similar versions of the
> SQLite3.dll.  We had a recursive query in SQB take 6 min,
> on other IDEs it would be less than 2 min.
>
> My $0.02

For exactly that reason I would advise everyone to use the
sqlite3 command line tool for performance comparisons in this
mailing list. 

-- 
Regards, Cordialement, Groet,

Kees Nuyt

[sqlite] Is this a bug ? How to rename a table and all dependencies ?

[Kees Nuyt]

On Fri, 21 Aug 2015 22:58:51 +0200, "sqlite-mail"
 wrote:

> That's why I think that expose a basic SQL ANSI catalog would make this kind
> of work and others a lot easier.  

That would be nice to have in some development environment (you
could develop one, if nobody else has done it), but in my
opinion it does not belong in the core engine that SQLite is.
It's lite for good reasons.

It even would be fine to me if all ALTER TABLE features would be
removed.

-- 
Regards, 

Kees Nuyt

[sqlite] Enhance the SELECT statement?

[Kees Nuyt]

On Mon, 17 Aug 2015 20:08:23 +0200, "R.Smith"
 wrote:

> CORRECTION: It seems one of the two options I've mentioned earlier, 
> namely the CREATE TABLE AS SELECT... does not actually work on the back 
> of a WITH clause. The other option still do, but this request has more 
> appeal now.

CREATE TABLE works if you swap the order of CREATE and WITH:

$ cat ~/sql/test.sql
.head on
.echo on
-- syntax error
WITH RECURSIVE generate AS
( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate
WHERE seqno < 10)
CREATE TABLE table_of_i1 AS SELECT seqno FROM generate ORDER BY
seqno;

-- accepted
CREATE TABLE table_of_i2 AS
 WITH RECURSIVE generate AS
( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate
  WHERE seqno < 10)
 SELECT seqno FROM generate ORDER BY seqno;
PRAGMA table_info(table_of_i2);

-- alternative syntax
CREATE TABLE table_of_i3 (seqno INTEGER PRIMARY KEY NOT NULL);
WITH RECURSIVE generate AS
( SELECT 1 AS seqno UNION ALL SELECT seqno+1 FROM generate
WHERE seqno < 10)
INSERT INTO table_of_i3 (seqno) SELECT seqno FROM generate ORDER
BY seqno;
PRAGMA table_info(table_of_i3);

.dump

-- 
Regards, Cordialement, Groet,

Kees Nuyt

[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?

[Kees Nuyt]

On Thu, 6 Aug 2015 10:17:43 -0500, John McKown
 wrote:

> Too bad that SQLite does not implement updatable views.

Sometimes a similar construct as updatable views can be obtained
with an INSTEAD OF trigger. 

-- 
Regards,

Kees Nuyt

[sqlite] Schema-less JSON SQLite DB?

[Kees Nuyt]

On Tue, 14 Jul 2015 10:22:02 -0700, Hayden Livingston
 wrote:

> Wow, this is the killer feature no-one told me about.
> I can get rid of types? How does this work under-the-hood?
>
> SELECT * FROM TABLE WHERE FooColumn > 50
>
> And I've stored "Something" in that column in some row.
> What's the behavior?

That's easy to test by yourself.
By the way, you can enforce datatype to some extend with
constraints:
CREATE TABLE T1 (
  id INTEGER PRIMARY KEY NOT NULL
, anint INTEGER 
  CONSTRAINT int_anint CHECK (typeof(anint) == 'integer')
);
INSERT INTO T1 (anint) VALUES (1);
INSERT INTO T1 (anint) VALUES ('two');


> I don't want to take up your time, so if there's a document I can read
> about it'd be great.

https://sqlite.org/datatype3.html 

All docs:
https://sqlite.org/docs.html


-- 
Regards, Cordialement, Groet,

Kees Nuyt

[sqlite] Compiling sqlite as as library

[Kees Nuyt]

On Wed, 1 Jul 2015 14:28:37 +0530, Prakash Premkumar
 wrote:

>Hi,
>
>Can you please tell me how to compile sqlite as a library and link with
>other applications using the "-L" flag ?

That depends on the operating system you want to use it on.
For many common platforms a precompiled binary is available on
the download page:
http://www.sqlite.org/download.html

The link recipe depends on your platform and toolchain.
Oftentimes, is it sufficent to add the sqlite3 amalgamation
source (which you can make with 'make sqlite3.c') to your list
of sources. For example the sqlite3 command line tool is build
using:
gcc ${CFLAGS} -o sqlite3 sqlite3.c src/shell.c

More info:
http://www.sqlite.org/howtocompile.html

I hope this helps. If you need more detailed info, please reply
with the specifications of your platform.

-- 
Regards,

Kees Nuyt

[sqlite] Fault on transaction begin, end, and rollback statements

[Kees Nuyt]

On Thu, 25 Jun 2015 16:41:25 + (UTC), Ch?
 wrote:

>After using sqlite3_prepare_v2 to prepare "BEGIN TRANSACTION", "END 
>TRANSACTION", and "ROLLBACK TRANSACTION", the system will fault at a delayed 
>time when calling sqlite3_step.  If I call prepare and step right away, there 
>is no fault.  It runs fine.  Thus, if I just execute "BEGIN TRANSACTION", I 
>have no problem.  If I prepare a statement and execute it in the same 
>function, there is no problem too.  The problem occurs when I store a 
>prepared statement and access it at a later time.  In debugging, I checked if 
>anything was changing the sqlite3_stmt* pointer in-between. There was no 
>tampering in the program.  In fact, I created a SQLiteStatement class that 
>wrapped the sqlite3_stmt*, initialized it to 0, finalized it when done, and 
>prevented it from being overwritten with operator overloading the '=' sign.  
>I did this to ensure the safety of the prepared sqlite3_stmt*.

Is there any chance the statement is _step()ped, _finalize()d
and then re-used? To be able to use a statement multiple times,
it should not be finalized but rather _reset().

>Thank you for your help,
>Ch?

HTH
-- 
Regards,
Kees Nuyt

[sqlite] Problems in reading a record containing a blob

[Kees Nuyt]

On Wed, 17 Jun 2015 16:59:02 +, Sivananda Nyayapathi
 wrote:

> I have created a table to store images.  The columns are
> file name and file content.  FileContent field is the blob.
> I am using Samsung Tablet with Android Version 4.4.2. 
> Most of the tablets that I tested the software in, it works fine.
> However, on one specific Android table I am having problems. 
> I have given the description of the problem below:
>
> I am able to store the image in the blob field. 
> However, while attempting to read, I get the
> following error two times (as seen in the LogCat entries):
> W/CursorWindow(26737): Window is full: requested allocation
> 3184896 bytes, free space 2096605 bytes, window size 2097152 bytes
>
> Then the following error:
> E/CursorWindow(26737): Failed to read row 0, column 0
> from a CursorWindow which has 0 rows, 7 columns.
>
> NOTES:
>
>*  A walkthrough has been done on the code to ensure that there are no issues 
>in closing the cursors.  All cursors are getting closed.  (Used StrictMode 
>also to ensure this.)
>*  There is enough memory.  The tablet with the problem is a 32GB Samsung 
>tablet.  There is a large amount of free available memory at runtime.

Nope, there probably isn't enough free RAM. 
The 32 GByte refers to sdcard storage size, not RAM.
In Android speak, memory and storage size are confused.

> *  The preference is to store the image in the database as a BLOB instead of 
> storing the image in the file system and storing the path in the database 
> record (which would, probably, resolve the issue as far as retrieving the 
> image is concerned)..
> *   Android details
>
> o   Samsung Galaxy Note 10.1, 2014 Edition
> o   Model Number: SM-P605V
> o   Kernel Version 3.4.0
> o   Hardware Version: P605V.02
> o   Device Memory: Total space - 32 GB; Available space - 21.94GB
>
> Questions:
> * Could you please help me understand why the application works fine 
> on six Samsung tablets that I have tested the app on, but it does not work on 
> the seventh tablet with the same OS version?
> * What is the solution to this problem?

Check free RAM before extracting an image.
Maybe: run garbage collection and/or push other apps to
background.

-- 
Regards, Cordialement, Groet,

Kees Nuyt

[sqlite] in Linux system, 2 threads can access 2 separate databases (created by sqlite3) through sqlite3?

[Kees Nuyt]

On Thu, 21 May 2015 17:54:50 +0800 (CST), "miles zhang"
 wrote:

>dear,
>thanks for your reading.
>I have 2 different databases ,  and I create 2 threads
>to access such 2 databases with sqlite3 sql statement??

You do not have to create a thread for each database connection,
but it is possible. You would sqlite3_open() one database in
each thread, each database gets its own handle.

>   can it be done successfully like this using sqlite3 lib?

Yes.

>   is it need to control access of such 2 thread for competition ?
> for example , locker.

No, database connections are independent. Synchronization
between them is done with file locking that sqlite provides
automatically.

You would only have to synchronize between the threads (with a
mutex) if they both use the same database connection.


>   thx,
>   hope your reply soon.
>
>   miles zhang
>   2015-05-21

-- 
Regards,

Kees Nuyt

[sqlite] ALTER TABLE ADD COLUMN

[Kees Nuyt]

On Thu, 21 May 2015 12:45:41 +0200, "Christoph P.U. Kukulies"
 wrote:

>Am 21.05.2015 um 10:00 schrieb Kees Nuyt:
>> On Thu, 21 May 2015 09:40:53 +0200, "Christoph P.U. Kukulies"
>>  wrote:
>>
>>> Am 21.05.2015 um 09:25 schrieb Hick Gunter:
>>>> alter table [database] add column [real_length] numeric;
>>> Thanks, that did it right. Almost. Still I'm wondering why
>>> the output is
>>> not as "smooth" as the
>>> .fullschema listing before the ALTER ADD COLUMN:
>>> (comma at line start,
>> Alter table just adds ",your-row-spec" in front of ");".
>> If you want it to be more consistent, use comma at line start
>> for all column specs in the original schema.
>>
>>> lower case 'numeric')
>> That can be fixed by using
>> alter table [database] add column [real_length] NUMERIC;
>>
>> You can patch the layout of the CREATE TABLE statement in
>> sqlite_master by using
>> http://my.domain/sqlite.org/pragma.html#pragma_writable_schema

I forgot to edit that link, should be
http://www.sqlite.org/pragma.html#pragma_writable_schema

>> and replacing the contents of the sql column with your preferred
>> layout.
>> It is dangerous, so read the warning, make backups first and
>> test the result thoroughly!
>
>
> Thanks. So you mean to use the pragma at run time in sqlite3, like:
> sqlite> PRAGMA writable_schema = true;
> sqlite>

Yes, that.

>or at compile time and recompile it?

No, no compile time options required, the default sqlite3
command line will do.

> To me it would probably far easier, to rebuild the schema
> from ground up in sqlite3.exe since I'm still in a design phase.

That's much better.
For most of my projects I maintain the schema source in my
project source tree, together with a few scripts with test data.
The database is automaticaly created from that schema when it is
missing (e.g. in  make clean; make). 

-- 
Regards, 

Kees Nuyt

[sqlite] AUTOINC vs. UUIDs

[Kees Nuyt]

On Wed, 20 May 2015 12:36:43 -0700, Scott Doctor
 wrote:

> Given a field that is a primary key with auto-increment, does sqlite 
> store an integer that gets incremented, or does it look at the last row 
> and increment its value?

The autoincrement clause causes an entry in the sqlite_sequence
table.

$ sqlite3 t2.sqlite
SQLite version 3.8.11 2015-05-20 00:15:27
Enter ".help" for usage hints.
sqlite> create table t(id integer primary key autoincrement, tx
text);
sqlite> insert into t (tx) values ('one'),('two');
sqlite> select * from sqlite_sequence;
t|2
sqlite>

-- 
Regards, 

Kees Nuyt

[sqlite] Limit size of write-ahead log file

[Kees Nuyt]

On Mon, 18 May 2015 19:00:28 +0200, Zsb?n Ambrus
 wrote:

>The PRAGMA max_page_count statement lets me set a limit on the size
>the database is allowed to grow.  This is useful to protect myself
>against accidental errors in my program, where I fill the file system
>with a huge database file, which could disrupt other processes that
>are trying to write the same filesystem.
>
>However, I don't see a setting that would let me limit the size of the
>write-ahead log file, or of other temporary files listed in
>"http://sqlite.org/tempfiles.html;.  Is there a setting for that?
>Also, is there perhaps a way to limit the size of temporary files
>opened for a database handle?
>
>I expect that SQLite itself should be able to handle such a limit,
>because SQLite promises that it can handle failed filesystem
>operations gracefully in all cases.
>
>The PRAGMA journal_size_limit does not do this, at least according to
>the documentation.  The WAL file may still grow limitlessly regardless
>that pragma as long as that file is still needed by active database
>connections.

Did you consider
PRAGMA wal_autocheckpoint=N; and 
PRAGMA database.wal_checkpoint(); ?

http://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint

-- 
Regards, 

Kees Nuyt

[sqlite] Multiple Prepared Statements

[Kees Nuyt]

On Fri, 08 May 2015 14:49:54 -0700, Scott Doctor
 wrote:

> Can I prepare multiple statements then implement them in 
> arbitrary order (based on some logic)?

Yes.

> Or do the statements need to be prepared, stepped, finalized 
> serially?

No. You even don't have to _finalize() the statement, you can
just _reset() it to finish the _step() loop and keep the
statement around for reuse (with new bindings) later.

_reset() will free the resources that are allocated at the first
_step().

-- 
Regards,

Kees Nuyt

[sqlite] sqlite3 and Unicode

[Kees Nuyt]

On Tue, 05 May 2015 10:50:00 +0200, Clemens Ladisch  
wrote:

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

I totally agree. It depends on the shell what representation is used,
some MS Windows codepage or UTF-8. 

The example below is done in bash on MS Windows 7 (using Mobaxterm), 
with a (outdated) Windows sqlite3 executable.
The result of .dump contains UTF-8.

> sqlite3 t.sqlite
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t1 (id INTEGER PRIMARY KEY NOT NULL, tx TEXT);
sqlite> insert into t1 values (1,'??n');
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1 (id INTEGER PRIMARY KEY NOT NULL, tx TEXT);
INSERT INTO "t1" VALUES(1,'??n');
COMMIT;
sqlite> .q

> sqlite3 t.sqlite .dump| grep INSERT | od -t x1
000 49 4e 53 45 52 54 20 49 4e 54 4f 20 22 74 31 22
020 20 56 41 4c 55 45 53 28 31 2c 27 c3 a9 c3 a9 6e
040 27 29 3b 0a
044

-- 
Regards,
Kees Nuyt

[sqlite] Did A Recent Windows & Update Affect SQLite???

[Kees Nuyt]

On Wed, 1 Apr 2015 14:26:15 -0700, Mark Romero  wrote:

> Thank you all for all your help.
>
> I have been soliciting advice from all sorts of Lightroom forums / adobe
> forums / Dell computer forums and other forums more specific to hardware
> and Lightroom, and they all ended up in a dead end.
>
> But seeing as though it DOESN'T look like a recent Win 7 update has
> affected SQLite performance, I guess I have reached a dead end, too. (You
> were my last remaining hope, guys!!!)
>
> Anyway, thanks all for all your help and suggestions. Looks like I might
> just have to stick with Adobe Camera Raw and Photoshop for now until I can
> get a new computer.

Other things you can try:

* Reduce the number of files in directories (folders) 
  with images  that are under control of Lightroom

* If possible, disable automatic (re-)cataloging in
  Lightroom

* Exclude directories with sqlite databases / 
  Lightroom catalogs from all viruscanners. 
  If that is not possible, (temporarily) disable
  all real-time virusscanners


-- 
Regards,

Kees Nuyt

[sqlite] Releasing a read (SHARED) lock

[Kees Nuyt]

On Fri, 13 Mar 2015 08:17:26 +0800, Barry  wrote:

>On 13 March 2015 at 01:21, Dinu Marina  wrote:
>
>> You should be also aware of a more common pitfall: unclosed result sets.
>> Any lock is held until you read PAST the last row or you call stmt_finalize
>> (or the equivalent abstraction in your DBAL). Always close select
>> statements.
>
>
>Hi Dinu,
>
>Am I correct in my understanding then that it is not enough to call
>sqlite3_reset, I must call sqlite3_finalize after I am done with a
>statement?

sqlite3_reset() is enough to release the state/context of a statement.
Aditionally, any BEGIN TRANSACTION should be paired with a COMMIT or ROLLBACK.

>Cheers,
>
> - Barry

-- 
Regards, Cordialement, Groet,

Kees Nuyt

Re: [sqlite] Bug in SQLite FLOAT values

[Kees Nuyt]

On Sat, 7 Feb 2015 11:27:59 +0530, Abdul Aziz <abduldblog...@gmail.com> wrote:

> Hi there!
> I hope you are well!
>
> Recently I was working on project based on Android Sensors and encountered
> a bug in sqlite db, situation was this:
> I was setting there three values x,y,z as FLOAT, android inbuilt sensors
> were receiving values as float upto 8 decimal places, but I wanted to store
> value only upto 6 decimal place, so in android this is the way that first
> you will have to convert that value into String , as* String sLongitude =
> String.format("%.6f", x);*

As others have said, you shouldn't confuse the storage 
format (how a value is stored in the database) with
the presentation (how data is displayed on output).

Luckily, recently sqlite got a printf() function.

Demo:

$ sqlite3 test.db
SQLite version 3.8.8 2015-01-30 20:59:27
Enter ".help" for usage hints.
sqlite> create table t3 (id INTEGER PRIMARY KEY, lat REAL, lon REAL);
sqlite> insert into t3 (id,lat,lon) VALUES (1,1.234567890123,5.6789012345678);
sqlite> select printf('id:%3d, latitude: %9.6f, longitude: %9.6f',id,lat,lon) 
from t3;
id:  1, latitude:  1.234568, longitude:  5.678901
sqlite>

Hope this helps.

-- 
Regards, 

Kees Nuyt

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

Re: [sqlite] Uncached performance

[Kees Nuyt]

On Thu, 8 Jan 2015 15:04:28 +0500, ?? ??? <mgume...@gmail.com>
wrote:

> CREATE TABLE global (
> [key] VARCHAR (1024),
> value BLOB,
> level INTEGER NOT NULL,
> original_name VARCHAR (1024),
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> parent_id REFERENCES global (id)
> );

The order of columns looks suboptimal.
It's better to put smaller columns first, so all PK and key info is in
the first page, and is not pushed to an overflow page when the values of
text or blob columns have a biggish length().

Other remarks:
* VARCHAR() translates to TEXT in SQLite.
* parent_id missed a type definition.

Typically, your table would look like:

CREATE TABLE global (
id INTEGER PRIMARY KEY AUTOINCREMENT,
parent_id INTEGER REFERENCES global (id),
level INTEGER NOT NULL,
[key] TEXT,
original_name TEXT,
value BLOB
);

Other things to consider:
PRAGMA page_size=something_larger_than_default;
PRAGMA cache_size= .. ;

-- 
Regards,

Kees Nuyt

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

Re: [sqlite] querying with BLOB in WHERE clause, possible?

[Kees Nuyt]

On Fri, 14 Nov 2014 04:59:58 -0700 (MST), bjdodo <bjd...@hotmail.com>
wrote:

>Hi
>
>Sorry for resurrecting an old thread. I got the where clause working for
>queries with byte array arguments based on this discussion. I cannot find
>the way to use byte arrays in where clauses for update and delete
>statements. I know it is terrible to use byte arrays as query arguments, I
>need this because of some 3rd party library problems. I also know that I can
>query for the row and do the update using some other column in the where
>clause I just find that "hacky". So if there is a way to do this trick for
>deletes and updates please let me know.


$ sqlite3 test.sqlite
SQLite version 3.8.8 2014-11-13 14:30:56
Enter ".help" for usage hints.
sqlite> CREATE TABLE t (id INTEGER PRIMARY KEY NOT NULL, bl BLOB);
sqlite> INSERT INTO T VALUES (1,x'313131');
sqlite> INSERT INTO T VALUES (2,x'323232');
sqlite> UPDATE T SET bl=x'33' WHERE bl=x'323232';
sqlite> SELECT * FROM t;
1|111
2|333
sqlite> DELETE FROM t WHERE bl=x'313131';
sqlite> SELECT * FROM t;
2|333
sqlite>

>Thank you,
>Jozsef

Hope this helps.

-- 
Regards,
Kees Nuyt


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

Re: [sqlite] Access Temp Tables/Views in background thread

[Kees Nuyt]

On Mon, 27 Oct 2014 21:53:59 -0700, Balance On I
<balance...@gmail.com> wrote:

> Hi All,
>
> I have an application where I create a temp table/view in one thread. I
> then try to perform some operations in background and I don’t have access
> to those temp objects. I thought that those were process scoped. Is it
> possible to access temp tables/views from across threads?

Temp objects are connection scoped.


> I am currently doing this in Objective-C and am using blocks to open a
> connection, perform operations, then close the connection in the background
> on a concurrent dispatch queue.

Are you sure the temp objects are accessed via the same
connection (db object) as where they were created?


> Is there a better multi-threaded approach?

I'll leave that to someone more experienced in threading.

-- 
Regards,

Kees Nuyt

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