[sqlite] What software is deployed more than SQLite?

[Justin Clift]

On 3 May 2015, at 19:26, Eric Sink  wrote:
> Last time I asked myself this question, I ended up in the same place you
> did:  zlib, libpng and libjpeg may be the only candidates in the same
> ballpark as SQLite.

Likely libxml2 as well.  Seems to get embedded by just about everything.

Some of the crypto libraries are in lots of things too, but not as much
as libxml2.

+ Justin

[sqlite] Integrating sqlite with Core Data and iCloud

[Justin Clift]

On 25 Apr 2015, at 20:41, Jeff M  wrote:
>> On Apr 24, 2015, at 2:44 AM, Simon Slavin  wrote:
>> 
>> On 24 Apr 2015, at 6:59am, Jeff M  wrote:
>> 
>>> I don't need to map SQLite to iCloud -- I only need to map SQLite to Core 
>>> Data.  Core Data then takes care of the iCloud issues.
>> 
>> I imagine you'd do that by writing a VFS which used Core Data for storage.  
>> Core Data could store your data in any of the formats it has drivers for, 
>> including plaintext files and SQLite.  And Core Data could store your data 
>> in any medium it has drivers for, including local storage and iCloud.
>> 
>> The result might be slow and inefficient, since you're building a DBMS 
>> (SQLite) on top of a DBMS (Core Data) on top of a DBMS (SQLite).
>> 
>> If you use Core Data the resulting file wouldn't look like a normal SQLite 
>> database.  Core Data stores objects.  It doesn't store the rows and columns 
>> you refer to with SQL commands.  Maybe your objects would be table rows.
>> 
>> Simon.
> 
> Originally, I wanted to map my tables, rows, and columns to similarly-named 
> Core Data entities and attributes so I could continue to use the SQL 
> language.  I was hoping to hook into the SQL parser to get the benefits of 
> where.c, but I realize now that's impractical.
> 
> Your suggestion of working at the file system level is interesting, but my 
> objects would be disk blocks.  I could use a simple Core Data model: one 
> entity (representing the entire database file) and create one object per 
> block (each having a binary attribute containing one block of data).  It 
> would be easy to map each file system read() and write() to the corresponding 
> objects.  Using Core Data as a memory array would earn me the Kludge of The 
> Year Award.  But, I see data corruption in my future.
> 
> Can you point me to some sample source code (outside of SQLite itself) that 
> implements sqlite3_vfs_register()?

Hmmm, searching for that function name across GitHub returns about 27k results.

16.6k+ for just C:

  https://github.com/search?l=c=sqlite3_vfs_register=Code=?

6k+ for C++:

  https://github.com/search?l=cpp=sqlite3_vfs_register=Code=?

You'll probably not be short of example stuff to drawn from... :D

Regards and best wishes,

Justin Clift

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Gerald Bauer]

Hello,

> what is the difference between major and minor?

  To quote from the pull request [1]:

The idea is major are kind of "A" league and minor "B" league. For
something more concrete - the tool is "A" league if it is open source
(and works ;-)). Another is how popular (hard to say). Not sure if
that makes sense.

  Cheers.

[1] https://github.com/planetopendata/awesome-sqlite/pull/1

[sqlite] Multiple instances of the same program accessing the same db file

[Keith Medcalf]

> > There is no difference between a file opened over the network and one
> opened locally, except that in the network open case the network
> filesystem does not maintain consistency between the client view of the
> file and the server view of that same file, because doing so takes time
> and the network filesystem designer decided to trade off in favour of
> speed over consistency, usually without provision of a knob to change this
> decision.

> Keith, I think you're ignoring the fact that when opening a file across a
> network the file system knows it's operating across the network and does
> not make the assumption that its cache is valid.  If things operated the
> way you describe almost all shared access operations would lead to
> corruption.  Operations when opening a file across a network are slower
> because the NFS has to check whether the file has been updated since its
> local copy was cached.

> The various teams which develop network file systems like NFS SMB and AFP
> all worried about these things and got them right.  Individual clients
> won't write out-of-date caches back to a centrally held file unless you've
> explicitly turned off the network-savvy routines.

I believe you are incorrect, Simon.  

To my knowledge there are *no* network filesystems which properly arbitrate 
multiple shared-read and shared-write client access to files opened across the 
network and provide a consistent view (similar to the view of the file that one 
would obtain if all the clients were operating on a local file with no network 
filesystem involved).  Moreover, as more and more time progresses there is less 
and less ability for things to work properly.  Except in very rare cases it has 
always been my experience that concurrent updates (as in record updates) leads 
to file corruption.

If shared network access worked properly then Client/Server computing would 
never have needed to be invented.

[sqlite] howto shrink files in wal mode

[Zaumseil René]

Hi,

After running the following script in sqlite 3.8.7.1:

set dbfile {c:/temp/db.sqlite3}
file delete -force $dbfile
sqlite3 ::db $dbfile
::db eval {
  PRAGMA journal_mode = WAL;
  create table test2 (id, txt)
}
for {set i 0} {$i < 1} {incr i} {
  set t txt$i
  ::db eval {insert into test2 values($i,$t)}
}
::db eval {PRAGMA wal_checkpoint(FULL)}
puts a=[file size $dbfile]
::db eval {
  delete from test2;
  PRAGMA wal_checkpoint(FULL);
  vacuum;
}
puts b=[file size $dbfile]
::db eval {vacuum}
puts c=[file size $dbfile]
::db close
puts d=[file size $dbfile]


I got as result:

a=177152
b=177152
c=177152
d=2048

Is there a way to shrink the file when it is still open in WAL mode?


Thank you

Ren?


Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder 
gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist 
(sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese 
Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese 
unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu 
benachrichtigen. Besten Dank.

[sqlite] sqlite3 and Unicode

[Teg]

Hello Peter,

I use UTF-8 exclusively in the engine parts my windows applications. I
just translate to UTF-16 for the GUI layer. In that way, no matter
what platform I support, the DB's remain the same.

C



Tuesday, May 5, 2015, 3:29:54 PM, you wrote:

PH> Thanks for all the replies.  I'm still confused on this.  It sounds like
PH> maybe UTF8 is the answer on OSX but UTF-16LE on Windows.

PH> Whatever the answer to that, it seems there isn't a way to control the
PH> encoding within sqlite3 so I guess I won't worry about it.

PH> Pete
PH> lcSQL Software 
PH> Home of lcStackBrowser  and
PH> SQLiteAdmin 
PH> ___
PH> sqlite-users mailing list
PH> sqlite-users at mailinglists.sqlite.org
PH> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
 Tegmailto:Teg at djii.com

[sqlite] Multiple instances of the same program accessing the same db file

[Simon Slavin]

On 5 May 2015, at 1:57pm, Keith Medcalf  wrote:

> There is no difference between a file opened over the network and one opened 
> locally, except that in the network open case the network filesystem does not 
> maintain consistency between the client view of the file and the server view 
> of that same file, because doing so takes time and the network filesystem 
> designer decided to trade off in favour of speed over consistency, usually 
> without provision of a knob to change this decision.

Keith, I think you're ignoring the fact that when opening a file across a 
network the file system knows it's operating across the network and does not 
make the assumption that its cache is valid.  If things operated the way you 
describe almost all shared access operations would lead to corruption.  
Operations when opening a file across a network are slower because the NFS has 
to check whether the file has been updated since its local copy was cached.

The various teams which develop network file systems like NFS SMB and AFP all 
worried about these things and got them right.  Individual clients won't write 
out-of-date caches back to a centrally held file unless you've explicitly 
turned off the network-savvy routines.

Simon.

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Drago, William @ CSG - NARDA-MITEQ]

Under SQLite Admin Tools what is the difference between major and minor?

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com



> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Gerald Bauer
> Sent: Tuesday, May 05, 2015 7:05 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Awesome SQLite List - Collection of SQLite
> Goodies Started - Contributions Welcome
>
> Hello,
> >   It seems to me that it would be useful to include them, flagged as
> > "commercial only".
>
>   Due to popular demand I started a "commercial only" awesome sqlite
> list [1]. For now entries are free (as in beer)  ;-)  Cheers.
>
> [1] https://github.com/planetopendata/awesome-
> sqlite/blob/master/COMMERCIAL.md
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] What software is deployed more than SQLite?

[Stephen Chrzanowski]

No... Skype doesn't come with ALL versions of Win 7 or later.  I've
purchased three copies of 8.1 since Dec and none of them come with Skype.
Two Notebooks with the OS installed as well as an OEM I've downloaded from
MSoft.

I'm not saying that MSoft doesn't have an application or two that uses
SQLite, but, Skype absolutely does NOT come with all copies of 7+.

On Mon, May 4, 2015 at 5:26 PM, R.Smith  wrote:

>
>
> On 2015-05-03 08:18 PM, Richard Hipp wrote:
>
>> SQLite is not in default Windows installations (historically - that is
>> about to change with Windows 10 which uses SQLite as a core OS
>> component) but many Windows desktops will include secondary software
>> such as Firefox or Chrome or iTunes or Skype or Dropbox or something
>> else that contains SQLite.//... snip
>>
>
> Actually, ALL Windows installations since 7 comes with SKYPE as default
> which uses SQLite extensively.  I know this having made add-ons for skype.
> (I think however in Win 7 you could still remove it if you wanted to).
>
> If anyone is interested, simply go to your
> \Users\yourname\AppData\Roaming\Skype\ folder and find "main.db" in there
> in your favourite SQLite tool. Open it in read-only mode if skype is
> running. Encryption is only for sensitive data, so you could poke around
> your normal call/message data for fun - quite interesting.
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Multiple instances of the same program accessing the same db file

[Simon Slavin]

On 5 May 2015, at 11:44am, James K. Lowden  wrote:

> Simon Slavin  wrote:
> 
>> In contrast to NFS both SMB and AFP are designed to support networks
>> properly, with caches only on the computer which hosts the file and
>> locking correctly implemented.  
> 
> Are you saying that SMB clients have no filebuffer cache?  Or that
> it exists, but is reliably invalidated by writes from other clients?

SMB keeps its cache on the computer which hosts the file.  So if a file is 
opened locally the cache is on the only computer concerned.  If computer A 
opens a file on computer B, the file-system cache is on computer B, where all 
file requests pass through it.

Of course a badly written program could keep a local cache too.  But that's the 
programmer's fault and SQLite certainly doesn't have its own cache at that 
level.

Simon.

[sqlite] Windows 10 and UTF8 [Re: sqlite3 and Unicode]

[Luuk]

On 5-5-2015 12:40, Luuk wrote:

>
> now it becomes time that windows will do some things with UTF-8
> (sigh) ;-)
>
> /me currently watching
> "Your PC will restart serveral times. Sit back and relax"
> while installing updates for technical preview to Windows 10
>
>



C:\temp>sqlite3.exe
SQLite version 3.8.9 2015-04-08 12:16:33
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select hex('??n');
82826E
sqlite> .quit

C:\temp>chcp 65001
Active code page: 65001

C:\temp>sqlite3.exe
SQLite version 3.8.9 2015-04-08 12:16:33
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select hex('??n');


C:\temp>ver

Microsoft Windows [Version 10.0.10074]

C:\temp>

YES, IT CRASHED (silently.)

[sqlite] howto shrink files in wal mode

[Richard Hipp]

On 5/5/15, Zaumseil Ren?  wrote:
>
> Is there a way to shrink the file when it is still open in WAL mode?
>

Run "PRAGMA wal_checkpoint=TRUNCATE;" in SQLite 3.8.8.2 or later.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Gerald Bauer]

Hello,
>   It seems to me that it would be useful to include them, flagged as
>  "commercial only".

  Due to popular demand I started a "commercial only" awesome sqlite
list [1]. For now entries are free (as in beer)  ;-)  Cheers.

[1] https://github.com/planetopendata/awesome-sqlite/blob/master/COMMERCIAL.md

[sqlite] sqlite3 and Unicode

[Luuk]

On 5-5-2015 12:25, Clemens Ladisch wrote:
> Staffan Tylen wrote:
>> I must admit that I'm a bit confused here. If I'm not wrong UTF-8 differs
>> from ascii when the value is higher than '7f'x, but storing data in sqlite
>> as text with character values beteen 'x80'x and 'ff'x seems to be no
>> problem. I previously thought that this could only be done in blob format.
>>
>> create table t (a);
>> insert into t values(cast(x'ff' as text));
>> select a,length(a),hex(a) from t;
>>   |1|FF
>>
>> My conclusion is that storing single-byte characters of any value is
>> allowed, is this true?
>
> SQLite assumes that all strings you give it are encoded in UTF-8, and
> does not actually check the encoding.  It gives you the same string
> back, so you could, in theory, use a different encoding, as long as you
> do not use any database string processing functions.  In practice, I
> would not recommend this.
>
>> At the bottom it says: *Note to Windows users:* The encoding used for the
>> filename argument of sqlite3_open() and sqlite3_open_v2() must be UTF-8,
>> not whatever codepage is currently defined. Filenames containing
>> international characters must be converted to UTF-8 prior to passing them
>> into sqlite3_open() or sqlite3_open_v2().
>>
>> So what does "must be converted" mean? I don't know how sqlite3.exe works
>> here but if I do
>>
>> sqlite3  ?.db
>>
>> where '?' as we've seen is '82'x it happily creates a file with a
>> non-displayable character in the first position that seems to be 1 byte
>> long.
>
> The shell assumes that its arguments are UTF-8, and gives the filename
> unchanged to sqlite3_open*().  When you've entered the filename in the
> Windows console with the default settings, it is not encoded in UTF-8.
>
>

now it becomes time that windows will do some things with UTF-8 
(sigh) ;-)

/me currently watching
"Your PC will restart serveral times. Sit back and relax"
while installing updates for technical preview to Windows 10

[sqlite] sqlite3 and Unicode

[Luuk]

On 5-5-2015 11:25, Kees Nuyt wrote:
> 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
>

OK, thanks

[sqlite] sqlite3 and Unicode

[Peter Haworth]

Thanks for all the replies.  I'm still confused on this.  It sounds like
maybe UTF8 is the answer on OSX but UTF-16LE on Windows.

Whatever the answer to that, it seems there isn't a way to control the
encoding within sqlite3 so I guess I won't worry about it.

Pete
lcSQL Software 
Home of lcStackBrowser  and
SQLiteAdmin 

[sqlite] sqlite3 and Unicode

[Clemens Ladisch]

Staffan Tylen wrote:
> I must admit that I'm a bit confused here. If I'm not wrong UTF-8 differs
> from ascii when the value is higher than '7f'x, but storing data in sqlite
> as text with character values beteen 'x80'x and 'ff'x seems to be no
> problem. I previously thought that this could only be done in blob format.
>
> create table t (a);
> insert into t values(cast(x'ff' as text));
> select a,length(a),hex(a) from t;
>  |1|FF
>
> My conclusion is that storing single-byte characters of any value is
> allowed, is this true?

SQLite assumes that all strings you give it are encoded in UTF-8, and
does not actually check the encoding.  It gives you the same string
back, so you could, in theory, use a different encoding, as long as you
do not use any database string processing functions.  In practice, I
would not recommend this.

> At the bottom it says: *Note to Windows users:* The encoding used for the
> filename argument of sqlite3_open() and sqlite3_open_v2() must be UTF-8,
> not whatever codepage is currently defined. Filenames containing
> international characters must be converted to UTF-8 prior to passing them
> into sqlite3_open() or sqlite3_open_v2().
>
> So what does "must be converted" mean? I don't know how sqlite3.exe works
> here but if I do
>
> sqlite3  ?.db
>
> where '?' as we've seen is '82'x it happily creates a file with a
> non-displayable character in the first position that seems to be 1 byte
> long.

The shell assumes that its arguments are UTF-8, and gives the filename
unchanged to sqlite3_open*().  When you've entered the filename in the
Windows console with the default settings, it is not encoded in UTF-8.


Regards,
Clemens

[sqlite] sqlite3 and Unicode

[Staffan Tylen]

>
>
>>> I think it assumes UTF8 in both cases.
>>>
>>
>> when using '.dump', it does not create a UTF8 file on Windows 7 (sQlite
>> version 3.8.8.3)
>>
>
> It's UTF16-LE data.
>
>
>
I must admit that I'm a bit confused here. If I'm not wrong UTF-8 differs
from ascii when the value is higher than '7f'x, but storing data in sqlite
as text with character values beteen 'x80'x and 'ff'x seems to be no
problem. I previously thought that this could only be done in blob format.

create table t (a);
insert into t values(cast(x'ff' as text));
select a,length(a),hex(a) from t;
 |1|FF

My conclusion is that storing single-byte characters of any value is
allowed, is this true?

Trying to find more info in the sqlite doc I found this page:
https://www.sqlite.org/c3ref/open.html

At the bottom it says: *Note to Windows users:* The encoding used for the
filename argument of sqlite3_open() and sqlite3_open_v2() must be UTF-8,
not whatever codepage is currently defined. Filenames containing
international characters must be converted to UTF-8 prior to passing them
into sqlite3_open() or sqlite3_open_v2().

So what does "must be converted" mean? I don't know how sqlite3.exe works
here but if I do

sqlite3  ?.db

where '?' as we've seen is '82'x it happily creates a file with a
non-displayable character in the first position that seems to be 1 byte
long.

Still confused.
Staffan

[sqlite] Signal handling and mmap

[Charles Munger]

At https://www.sqlite.org/mmap.html, the documentation mentions:

"An I/O error on a memory-mapped file cannot be caught and dealt with by
SQLite. Instead, the I/O error causes a signal which, if not caught by the
application, results in a program crash."

It seems to me that a naively implemented handler might introduce database
coherency bugs and other issues. Is there anywhere where I can read about
how one might implement such a signal handler correctly, to gracefully
recover the same way any as other sqlite I/O error?

[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] sqlite3 and Unicode

[Clemens Ladisch]

Luuk wrote:
> on Windows 7:
> C:\temp>sqlite3.exe encoding.sqlite
> sqlite> select * from test;
> ??n

The Windows console does not support UTF-8 with the default settings and
the C stdio functions.  Any data you entered in the console is not
encoded correctly.


Regards,
Clemens

[sqlite] sqlite3 and Unicode

[Jean-Christophe Deschamps]

At 09:22 05/05/2015, you wrote:

>On 4-5-2015 20:54, Richard Hipp wrote:
>>On 5/4/15, Peter Haworth  wrote:
>>>When using the .dump command with .output to a filename, what 
>>>encoding does
>>>sqlite3 for the file? Same as the database encoding?  Is it possible to
>>>change whatever encoding is used?
>>>
>>>Similarly, when using the .import command, does sqlite3 assume the input
>>>file is the same encoding as the database?
>>
>>I think it assumes UTF8 in both cases.
>
>when using '.dump', it does not create a UTF8 file on Windows 7 
>(sQlite version 3.8.8.3)

It's UTF16-LE data.


--
jcd at antichoc.net  

[sqlite] sqlite3 and Unicode

[Luuk]

On 5-5-2015 09:22, Luuk wrote:
> On 4-5-2015 20:54, Richard Hipp wrote:
>> On 5/4/15, Peter Haworth  wrote:
>>> When using the .dump command with .output to a filename, what
>>> encoding does
>>> sqlite3 for the file? Same as the database encoding?  Is it possible to
>>> change whatever encoding is used?
>>>
>>> Similarly, when using the .import command, does sqlite3 assume the input
>>> file is the same encoding as the database?
>>>
>>
>> I think it assumes UTF8 in both cases.
>>
>
> when using '.dump', it does not create a UTF8 file on Windows 7 (sQlite
> version 3.8.8.3)

or am i doing something wrong?

on Windows 7:
C:\temp>sqlite3.exe encoding.sqlite
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
sqlite> select * from test;
een
??n
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (x varchar(20));
INSERT INTO "test" VALUES('een');
INSERT INTO "test" VALUES('??n');
COMMIT;
sqlite> select x, length(x), hex(x) from test;
een|3|65656E
??n|3|82826E
sqlite> PRAGMA encoding;
UTF-8
sqlite>

The '82' is not a UTF-8 representation of the '?' (length=3 is correct)

[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome

[Niall O'Reilly]

On Mon, 04 May 2015 17:15:17 +0100,
Gerald Bauer wrote:
> 
>   Note: For now commercial only tools (e.g. SQLite Analyzer) will NOT
> get include - sorry. If you want to get it included, buy a sponsored
> link or a beer for everyone on the mailing list! Just kidding ;-)

  It seems to me that it would be useful to include them, flagged as
  "commercial only".

  Best regards
  Niall O'Reilly

[sqlite] sqlite3 and Unicode

[Luuk]

On 4-5-2015 20:54, Richard Hipp wrote:
> On 5/4/15, Peter Haworth  wrote:
>> When using the .dump command with .output to a filename, what encoding does
>> sqlite3 for the file? Same as the database encoding?  Is it possible to
>> change whatever encoding is used?
>>
>> Similarly, when using the .import command, does sqlite3 assume the input
>> file is the same encoding as the database?
>>
>
> I think it assumes UTF8 in both cases.
>

when using '.dump', it does not create a UTF8 file on Windows 7 (sQlite 
version 3.8.8.3)

[sqlite] Looking for Ready-To-Use (Instant) SQLite Schema (and Sample Data) Scripts

[Gerald Bauer]

Hello,

  As posted before I've started an awesome collection about all things
SQLite [1].

  One section collections ready-to-use (instant) SQLite schemas (and
sample data) scripts. So far listed are:

  - football.db  - teams, competitions, seasons, matches, goals,
rounds, groups, etc.
  - beer.db  - beer, brand, brewery, etc.
  - Chinook Database  - online music store with sample data script
(e.g. chinook.sqlite) - inspired by the "classic" Microsoft Northwind
example


   If you know any others, let us know. Thanks. Cheers.

[1] https://github.com/planetopendata/awesome-sqlite

[sqlite] Multiple instances of the same program accessing the same db file

[Keith Medcalf]

> SMB keeps its cache on the computer which hosts the file.  So if a file is
> opened locally the cache is on the only computer concerned.  If computer A
> opens a file on computer B, the file-system cache is on computer B, where
> all file requests pass through it.
> 
> Of course a badly written program could keep a local cache too.  But
> that's the programmer's fault and SQLite certainly doesn't have its own
> cache at that level.

The SQLite page cache is local to the application running SQLite, obviously.  
It cannot be located on the remote filesystem server since the remote server is 
not running SQLite.  Of course, what you *really* mean is that client's view of 
the dataset must be consistent with the servers' view for the dataset and that 
the mechanisms to maintain that consistency must work across the network, just 
as they work for a local file on a local filesystem.

There is no difference between a file opened over the network and one opened 
locally, except that in the network open case the network filesystem does not 
maintain consistency between the client view of the file and the server view of 
that same file, because doing so takes time and the network filesystem designer 
decided to trade off in favour of speed over consistency, usually without 
provision of a knob to change this decision.

[sqlite] Multiple instances of the same program accessing the same db file

[James K. Lowden]

On Mon, 4 May 2015 02:05:54 +0100
Simon Slavin  wrote:

> On 4 May 2015, at 1:30am, James K. Lowden 
> wrote:
> 
> > That is the way most remote filesystems are designed and implemented
> > and documented.  Cf. http://www.ietf.org/rfc/rfc1813.txt:
> > 
> >   4.11 Caching policies
> > 
> >   The NFS version 3 protocol does not define a policy for
> >   caching on the client or server. In particular, there is no
> >   support for strict cache consistency between a client and
> >   server, nor between different clients. See [Kazar] for a
> >   discussion of the issues of cache synchronization and
> >   mechanisms in several distributed file systems.
> > 
> > If you can find documentation for cache semantics for CIFS, I'd be
> > interested to see it.  
> 
> In contrast to NFS both SMB and AFP are designed to support networks
> properly, with caches only on the computer which hosts the file and
> locking correctly implemented.  

Are you saying that SMB clients have no filebuffer cache?  Or that
it exists, but is reliably invalidated by writes from other clients?  

>From what I remember from configuring Samba, SMB is 1) very
complicated (lots of versions and knobs) and 2) undocumented in
numerous ways. The prototocol is better documented these days, thanks
to the EU, but I've never seen anything resembling semantic
guarantees.  So I'm skeptical of any assertion that it Just Works, even
subject to the constraints you mention.  

--jkl

[sqlite] What software is deployed more than SQLite?

[R.Smith]

On 2015-05-03 08:18 PM, Richard Hipp wrote:
> SQLite is not in default Windows installations (historically - that is
> about to change with Windows 10 which uses SQLite as a core OS
> component) but many Windows desktops will include secondary software
> such as Firefox or Chrome or iTunes or Skype or Dropbox or something
> else that contains SQLite.//... snip

Actually, ALL Windows installations since 7 comes with SKYPE as default 
which uses SQLite extensively.  I know this having made add-ons for 
skype. (I think however in Win 7 you could still remove it if you wanted 
to).

If anyone is interested, simply go to your 
\Users\yourname\AppData\Roaming\Skype\ folder and find "main.db" in 
there in your favourite SQLite tool. Open it in read-only mode if skype 
is running. Encryption is only for sensitive data, so you could poke 
around your normal call/message data for fun - quite interesting.