[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Paolo Bolzoni]

Scott you can still set it up using setenv(Posix) or
_putenv_s_(Windows). Can't you?

On Wed, Aug 19, 2015 at 10:23 AM, Scott Doctor  wrote:
>
> Another issue to consider is security. Some programs, such as mine, needs to
> carefully control temporary files. Unless the user selects a specific
> directory for temporary files, the files are put in a subdirectory of the
> program directory, created at run-time, then is security erased when done.
>
> Letting the operating system handle where to put the temporary files is a
> security issue for certain types of programs. My opinion is it is best to
> simply ask the user at install where to put temporary files and save that in
> a config file or in a table field, or simply create a temporary directory on
> the programs directory.
>
> 
> Scott Doctor
> scott at scottdoctor.com
> --
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ATTACH DATABASE statement speed

[Paolo Bolzoni]

On Wed, Aug 19, 2015 at 6:53 PM, Simon Slavin  wrote:
> On 19 Aug 2015, at 10:46am, Paolo Bolzoni  
> wrote:

> Just by itself, the above information is significant to the SQLite team.  
> Perhaps when you have had a chance to confirm it a new thread can be started 
> called 'SQLite database on ZFS is very slow'.  This should pull any ZFS 
> experts out of the woodwork.  Unfortunately I am not one of them.

I left running the pragma quick check during the night and finished in
2 hours and 46 minutes, so it is about 8 times slower than in ext4.
Zfs is an advanced filesystem plenty of features, but this speed
difference is too much I think.

However, tests on a plate disk need to wait the end of the month.

[sqlite] ATTACH DATABASE statement speed

[Paolo Bolzoni]

On Wed, Aug 19, 2015 at 11:44 AM, Simon Slavin  wrote:

> Hmm.  Would it be possible to format an external drive in ZFS and try the 
> operations on files stored on that ?

As you might have guessed from the timezone I am not at home atm, so I
do not have spare external disks.
However, I do have an expendable 16BG usb stick so I tried on that.

First I formatted it using zfs and I did the Pragma quick_check; I
killed it after 40 minutes.
Secondly I formatted it using ext4 and the Pragma quick_check; it
finished in about 25 minutes.

Now, I am trying again with zfs. But I think it already shows
something is indeed wrong.

[sqlite] ATTACH DATABASE statement speed

[Paolo Bolzoni]

I see. Thanks nameless person known as sqlite-mail (npkasm for short),
what you say makes sense.
However it does not explain why the pragma checks are so slow.

Anyhow, npkasm, I will keep in mind for the future. Good point indeed.

On Wed, Aug 19, 2015 at 3:59 PM, sqlite-mail  
wrote:
> Hello !
>
> The problem with foreign keys most of the time is not the the referenced
> table/field (normally primary key that do no need extra index) but the
> dependent table/field when they do not have a proper index, any time you
> update/delete a record on the referenced table a linear scan is performed on
> all dependent tables and that can be a lot time consuming depending on the
> number of records on then.
>
>   I've got this problem on a heavily foreign key constrained database and it
> took me a bit to realize that !
>
> Cheers !
>>  @nameless person known as sqlite-mail,
>> Yes, I do have foreign keys. But each relate to a primary key; there
>> are no explicit indexes on this primary keys, but they should not be
>> needed because primary keys are indexed automatically.
>> Or are they?
>>
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ATTACH DATABASE statement speed

[Paolo Bolzoni]

@Simon
I tried zpool scrub on both my disks and it returned nothing, I also
tried executing stress[1] on the disk and no error appeared in the log
or in stress itself.
However, coping the sqlite db on an external disk connected via usb3
and formatted with Ntfs actually does the pragma quick_check in little
more than 20 seconds and pragma integrity_check in 5 minutes.

So I think it is not an hardware problem, but Zfs messes up somewhat.
Is there any known disagreement between Zfs and sqlite? In fact I have
this feeling that my system is working fine in everything a part of
sqlite.
I read in the man that recordsize could be relevant.

Otherwise I have to reinstall the system. But it is of course time consuming.

[1]
http://people.seas.harvard.edu/~apw/stress/


@nameless person known as sqlite-mail,
Yes, I do have foreign keys. But each relate to a primary key; there
are no explicit indexes on this primary keys, but they should not be
needed because primary keys are indexed automatically.
Or are they?


@Marcus Grimm,
It seems have no effect actually in my disk. The test goes fast for a
while and slow down after.


On Tue, Aug 18, 2015 at 9:12 PM, Marcus Grimm  
wrote:
> Just another guess:
> Have you tried to increase the page chache drastically ?
> I can remeber that "PRAGMA quick_check" is pretty slow
> for bigger DBs without an increased page cache.
> Maybe something like:
> PRAGMA cache_size=50;
> PRAGMA quick_check;
>
> Marcus
>
>
> Am 18.08.2015 um 12:38 schrieb Paolo Bolzoni:
>>
>> It really seems something strange happens at filesystem level.
>>
>> This is a simple copy of slightly less than 1gb. It needs 9 seconds
>> including sync.
>> % date && sudo rsync -Pr italy-latest.osm.pbf / && sync && date
>> Tue Aug 18 19:22:23 JST 2015
>> sending incremental file list
>> italy-latest.osm.pbf
>>  946,976,283 100%  123.88MB/s0:00:07 (xfr#1, to-chk=0/1)
>> Tue Aug 18 19:22:32 JST 2015
>>
>>
>> However, when I start sqlite3 db 'PRAGMA quick_check;' the IO looks
>> normal for a while.
>> (I hope gmail don't mess up with the formatting...)
>>
>> 60, 90, 80 MB/s is kinda expected:
>> 08/18/2015 07:27:38 PM
>> Device: rrqm/s   wrqm/s r/s w/srMB/swMB/s
>> avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
>> encplate
>>0.00 0.00  820.00   13.0062.11 0.26
>> 153.34 1.872.271.14   73.46   1.20  99.80
>>0.00 0.00 1214.500.0094.58 0.00
>> 159.49 0.960.780.780.00   0.78  95.20
>>0.00 0.00 1008.50   22.0078.09 0.41
>> 155.99 1.501.460.96   24.16   0.93  95.80
>>
>> but after some seconds it drops terribly to less than 10MB/s
>> 08/18/2015 07:29:04 PM
>> Device: rrqm/s   wrqm/s r/s w/srMB/swMB/s
>> avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
>> encplate
>>0.00 0.00  124.003.50 9.88 0.12
>> 160.72 1.67   12.99   11.21   76.14   7.65  97.50
>>0.00 0.00   69.00   18.00 5.68 0.29
>> 140.55 1.81   20.92   14.15   46.86  11.38  99.00
>>0.00 0.00   86.000.00 7.05 0.00
>> 167.91 1.04   12.03   12.030.00  11.24  96.70
>>
>> And so, going to 10MB per second it can easily require few hours...
>>
>>
>> I am out of ideas, but thanks for all the support.
>>
>>
>>
>> On Tue, Aug 18, 2015 at 7:26 PM, Simon Slavin 
>> wrote:
>>>
>>>
>>> On 18 Aug 2015, at 7:30am, Paolo Bolzoni 
>>> wrote:
>>>
>>>> Any other idea of what can I try? Perhaps my filesystem is
>>>> misconfigured?
>>>
>>>
>>> The long time you quote is not standard for SQLite and I don't think
>>> anyone can help you solve it by knowing picky details of SQLite.  I'm even
>>> surprised that it changed with your -O0 compilation since this suggests
>>> features of your compiler I didn't know about.
>>>
>>> It's possible one of the developer team can help but they're reading this
>>> and can pitch in if they think so.
>>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Paolo Bolzoni]

Wouldn't be easier to simply add a parameter to sqlite3_initialize()?
E.g., a char const pointer to the tmp directory? That, if null,
defaults to something reasonable as James mentioned?

Maybe I am oversensitive, but I found strange I have to use setenv to
setup a command line option about "where to put tmp files" in my
program...


On Tue, Aug 18, 2015 at 11:02 PM, James K. Lowden
 wrote:
> On Sat, 15 Aug 2015 01:17:28 +0100
> Simon Slavin  wrote:
>
>> > BTW, Posix is almost silent on the question.  It says TMPDIR will
>> > define the location of a temporary store, but not how.
>>
>> I'm okay if the documentation simply says something like ...
>>
>> For Darwin (Mac), it's always /tmp/
>> For Linux, see the TMPDIR environment variable
>> For Windows see [whatever it is]
>
> Agreed, although IIUC it could be simpler than that, see next.
>
>> However I suspect things may be more complicated than that.  For
>> instance, does .NET respect the OS's choice of temporary directory no
>> matter which OS it's running under ?  I have no idea.
>
> What I'm suggesting is that there is no "OS's choice", really.
>
> There are a few functions in the C standard library, e.g. tmpfile(3),
> that may consult the environment.  The variable's name varies by
> implementation.  Some implementations, notably GNU's (if the
> documentation is correct), do not consult the environment.
>
> I would guess .NET is written atop the Win32 API and uses
> GetTempFileName or somesuch.  That uses GetTempPath, whose return value
> is affected by TMP and TEMP.
> (https://msdn.microsoft.com/en-us/library/windows/desktop/aa364992
> (v=vs.85).aspx).
>
> GetTempPath and tmpnam(3) on Windows both honor TMP, but the fallback
> policies differ.  So it's not really a question of what the OS's choice
> is, because the *OS* offers no "temporary file" function.  It's really a
> question of which library function is called, and how that function is
> implemented.
>
> But none of that matters unless those functions are used.  An
> application -- or library, as in SQLite's case -- need not use them,
> which in any case aren't all that helpful.  AIUI SQLite does *not* use
> those functions, but rather has its own way to determine where temporary
> files go.  In that case the rule could be quite simple and
> OS-independent.  For instance,
>
> 1.  Use "SQLITE_TMPDIR" if defined
> 2.  Use current working directory otherwise
>
> where the value is set by sqlite3_initialize and cannot be changed
> thereafter.
>
> --jkl
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ATTACH DATABASE statement speed

[Paolo Bolzoni]

It really seems something strange happens at filesystem level.

This is a simple copy of slightly less than 1gb. It needs 9 seconds
including sync.
% date && sudo rsync -Pr italy-latest.osm.pbf / && sync && date
Tue Aug 18 19:22:23 JST 2015
sending incremental file list
italy-latest.osm.pbf
946,976,283 100%  123.88MB/s0:00:07 (xfr#1, to-chk=0/1)
Tue Aug 18 19:22:32 JST 2015


However, when I start sqlite3 db 'PRAGMA quick_check;' the IO looks
normal for a while.
(I hope gmail don't mess up with the formatting...)

60, 90, 80 MB/s is kinda expected:
08/18/2015 07:27:38 PM
Device: rrqm/s   wrqm/s r/s w/srMB/swMB/s
avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
encplate
  0.00 0.00  820.00   13.0062.11 0.26
153.34 1.872.271.14   73.46   1.20  99.80
  0.00 0.00 1214.500.0094.58 0.00
159.49 0.960.780.780.00   0.78  95.20
  0.00 0.00 1008.50   22.0078.09 0.41
155.99 1.501.460.96   24.16   0.93  95.80

but after some seconds it drops terribly to less than 10MB/s
08/18/2015 07:29:04 PM
Device: rrqm/s   wrqm/s r/s w/srMB/swMB/s
avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
encplate
  0.00 0.00  124.003.50 9.88 0.12
160.72 1.67   12.99   11.21   76.14   7.65  97.50
  0.00 0.00   69.00   18.00 5.68 0.29
140.55 1.81   20.92   14.15   46.86  11.38  99.00
  0.00 0.00   86.000.00 7.05 0.00
167.91 1.04   12.03   12.030.00  11.24  96.70

And so, going to 10MB per second it can easily require few hours...


I am out of ideas, but thanks for all the support.



On Tue, Aug 18, 2015 at 7:26 PM, Simon Slavin  wrote:
>
> On 18 Aug 2015, at 7:30am, Paolo Bolzoni  
> wrote:
>
>> Any other idea of what can I try? Perhaps my filesystem is misconfigured?
>
> The long time you quote is not standard for SQLite and I don't think anyone 
> can help you solve it by knowing picky details of SQLite.  I'm even surprised 
> that it changed with your -O0 compilation since this suggests features of 
> your compiler I didn't know about.
>
> It's possible one of the developer team can help but they're reading this and 
> can pitch in if they think so.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ATTACH DATABASE statement speed

[Paolo Bolzoni]

It seems my system CPUs overheats regularly and so the system throttle
the CPU speed.
It is definitely not good, I need to disassemble my laptop, clean
everything, and probably replace the conductive cpu paste.

However, this does not explain anything. sqlite3 appears IO bound, not
CPU bound: in fact the programs seems to use 5% of the CPU tops.

Any other idea of what can I try? Perhaps my filesystem is misconfigured?

On Tue, Aug 18, 2015 at 3:02 PM, Paolo Bolzoni
 wrote:
> I think the ATTACH slowness was a misunderstanding caused by the
> optimization as it does not happen anymore now I compiled with -O0.
> The long time was actually deleting the table from the input db, this
> explains also why sqlite was making the journal file for the
> operation.
>
> I am aware it sounds implausible, but I really have not idea why the
> standard error message was not appearing before.
>
> Yes, I am using Linux
>
> % uname -a
> Linux slyrogue 4.1.4-1-ARCH #1 SMP PREEMPT Mon Aug 3 21:30:37 UTC 2015
> x86_64 GNU/Linux
>
>
> About hardware failures I do read some:
> mce: [Hardware Error]: Machine check events logged
> lines in dmesg. I try to investigate more.
>
> On Tue, Aug 18, 2015 at 12:28 PM, Simon Slavin  
> wrote:
>>
>> On 18 Aug 2015, at 4:14am, Paolo Bolzoni  
>> wrote:
>>
>>> In the input and output database I had a table of the same name and using:
>>>
>>> DROP TABLE IF EXISTS WaysNodes;
>>>
>>> sqlite3 was actually deleting the table of the input db, this was
>>> unexpected as I thought that
>>> without any prefix it deleted from the main database.
>>
>> So did I.  I'm glad you have a working solution but there is still something 
>> wrong.  Not only is your text above correct but it shouldn't take 13 hours 
>> to do an integrity check on a 13 Gig database with simple indexes.
>>
>> I'm still suspecting some kind of hardware problem.  I'll be interested to 
>> know what happens if you ATTACH your copy of the input database, on the 
>> other disk, rather than the original.  Does it still take a very long time ?
>>
>> Hmm.  What OS are you using again ?  Oh, you used iostat.  Linux.  So not 
>> the Windows pre-read caching bug.  Okay.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ATTACH DATABASE statement speed

[Paolo Bolzoni]

I think the ATTACH slowness was a misunderstanding caused by the
optimization as it does not happen anymore now I compiled with -O0.
The long time was actually deleting the table from the input db, this
explains also why sqlite was making the journal file for the
operation.

I am aware it sounds implausible, but I really have not idea why the
standard error message was not appearing before.

Yes, I am using Linux

% uname -a
Linux slyrogue 4.1.4-1-ARCH #1 SMP PREEMPT Mon Aug 3 21:30:37 UTC 2015
x86_64 GNU/Linux


About hardware failures I do read some:
mce: [Hardware Error]: Machine check events logged
lines in dmesg. I try to investigate more.

On Tue, Aug 18, 2015 at 12:28 PM, Simon Slavin  wrote:
>
> On 18 Aug 2015, at 4:14am, Paolo Bolzoni  
> wrote:
>
>> In the input and output database I had a table of the same name and using:
>>
>> DROP TABLE IF EXISTS WaysNodes;
>>
>> sqlite3 was actually deleting the table of the input db, this was
>> unexpected as I thought that
>> without any prefix it deleted from the main database.
>
> So did I.  I'm glad you have a working solution but there is still something 
> wrong.  Not only is your text above correct but it shouldn't take 13 hours to 
> do an integrity check on a 13 Gig database with simple indexes.
>
> I'm still suspecting some kind of hardware problem.  I'll be interested to 
> know what happens if you ATTACH your copy of the input database, on the other 
> disk, rather than the original.  Does it still take a very long time ?
>
> Hmm.  What OS are you using again ?  Oh, you used iostat.  Linux.  So not the 
> Windows pre-read caching bug.  Okay.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ATTACH DATABASE statement speed

[Paolo Bolzoni]

I think I got it. As often when something is really weird, it is was
not what I expected.

In the input and output database I had a table of the same name and using:

DROP TABLE IF EXISTS WaysNodes;

sqlite3 was actually deleting the table of the input db, this was
unexpected as I thought that
without any prefix it deleted from the main database.

Besides, I think the compiler optimization changed a bit the order I
was seeing the standard error messages as it appeared the time needed
was in the ATTACH, not in the cleaning and preparing the output db.

However, when I applied the Simon suggestion of vacuum after deleting
everything, but before re-create, and attached to the input db after
the problem apparently disappeared. I also compiled without
optimizations, it does not really matter in a program comprised mainly
by sql statements anyway.

About PRAGMA integrity_check, it returned OK for the db, but after
about 13 hours.

I also tried to copy the db in another disk, copy back and compare
with cmp. All fine.


Cheers,
Paolo

On Mon, Aug 17, 2015 at 9:26 PM, Simon Slavin  wrote:
>
> On 17 Aug 2015, at 9:22am, Paolo Bolzoni  
> wrote:
>
>> The pragma integrity_check is still running... Maybe my disk sucks for
>> some reason?
>
> I wonder whether the hard disk is faulty or the file is on a bad sector.
>
> If the other tests show nothing, can you duplicate the input database file ?  
> Do something that forces the OS to read the whole thing.  If you have a good 
> idea of how long reading and writing 13GB should take (ten minutes ?), this 
> may take an obviously unreasonable time.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ATTACH DATABASE statement speed

[Paolo Bolzoni]

The pragma integrity_check is still running... Maybe my disk sucks for
some reason?

The output of iostat looks normal to me though..

$ iostat -dmN  encplate 1 5
Linux 4.1.4-1-ARCH (slyrogue)   08/17/2015  _x86_64_(8 CPU)

Device:tpsMB_read/sMB_wrtn/sMB_readMB_wrtn
encplate151.63 6.93 4.35 225847 141971

Device:tpsMB_read/sMB_wrtn/sMB_readMB_wrtn
encplate111.00 7.80 0.07  7  0

Device:tpsMB_read/sMB_wrtn/sMB_readMB_wrtn
encplate158.0010.29 0.47 10  0

Device:tpsMB_read/sMB_wrtn/sMB_readMB_wrtn
encplate110.00 8.43 0.07  8  0

Device:tpsMB_read/sMB_wrtn/sMB_readMB_wrtn
encplate131.0011.36 0.00 11  0


On Mon, Aug 17, 2015 at 4:28 PM, Simon Slavin  wrote:
>
> On 17 Aug 2015, at 8:17am, Simon Slavin  wrote:
>
>> Also, out of interest, can you run "PRAGMA integrity_check" on all the 
>> database files involved ?
>
> Also out of interest, instead of just ATTACHing the input database,
>
> 1) Close the output database
> 2) Reopen the output database
> 3) ATTACH the input database
>
> Does it take the same amount of time ?  If so, which step takes the time ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Paolo Bolzoni]

If the problem is the multi-threading, it would be not enough to
simply document that the tmp directory should be set before spawning
any thread? e.g., in the main?

On Sat, Aug 15, 2015 at 9:17 AM, Simon Slavin  wrote:
>
>> On 15 Aug 2015, at 12:53am, James K. Lowden  
>> wrote:
>>
>> Simon Slavin  wrote:
>> ...
>>> If it's going to be documented, it would also be nice to see it on
>>>
>>> 
>>>
>>> which, according to my browser, doesn't mention the word
>>> 'environment'.
>>
>> I would be nice to have clearly documented the means by which the
>> location of temporary files is controlled.  I assumed some flavor of
>> tmpfile(3) was used, but evidently SQLite doesn't rely on that
>> function.
>>
>> BTW, Posix is almost silent on the question.  It says TMPDIR will
>> define the location of a temporary store, but not how.
>
> I'm okay if the documentation simply says something like ...
>
> For Darwin (Mac), it's always /tmp/
> For Linux, see the TMPDIR environment variable
> For Windows see [whatever it is]
>
> However I suspect things may be more complicated than that.  For instance, 
> does .NET respect the OS's choice of temporary directory no matter which OS 
> it's running under ?  I have no idea.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ATTACH DATABASE statement speed

[Paolo Bolzoni]

Dear list,

I have a program that materialize the subset of a database in a second
database for future use.

To do so I create the database object on the OUTPUT database, create
tables, create the indexes, and vacuum it just in the case I reused an
old file.

After I attach the INPUT db and I copy the useful lines.

The input db is about 13GB, so not really large, however the step on
this sql statement (where ? is of course binded to the db name)

ATTACH DATABASE ? AS indb;

requires several minutes (or more, it does not really seem to finish)!
Besides sqlite3 already created a 5GB journal file.


I am confused, why an attach database statement can be so slow?

I am not sure if useful, but sqlite3 --version returns:
3.8.11.1 2015-07-29 20:00:57 cf538e2783e468bbc25e7cb2a9ee64d3e0e80b2f

Your faithfully,
Paolo

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Paolo Bolzoni]

I guess it is not really a problem, but it means that a part of the
deprecated pragma there is no fully portable way?

On Tue, Jul 28, 2015 at 10:56 AM, Clemens Ladisch  wrote:
> Simon Slavin wrote:
>> both platforms use whatever the expected variable name was for that OS.
>
> Unix:
> 1. #pragma temp_store_directory
> 2. getenv("SQLITE_TMPDIR")
> 3. getenv("TMPDIR")
> 4. /var/tmp
> 5. /usr/tmp
> 6. /tmp
>
> Cygwin:
> 1. #pragma temp_store_directory
> 2. getenv("SQLITE_TMPDIR")
> 3. getenv("TMPDIR")
> 4. getenv("TMP")
> 5. getenv("TEMP")
> 6. getenv("USERPROFILE")
> 7. /var/tmp
> 8. /usr/tmp
> 9. /tmp
>
> Windows:
> 1. #pragma temp_store_directory
> 2. GetTempPath(), which is documented to return:
>a. getenv("TMP")
>b. getenv("TEMP")
>c. getenv("USERPROFILE")
>d. the Windows directory
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Paolo Bolzoni]

So, just be sure we are in the same page:

>From the C interface, the way to decide the directory is setting the
value of sqlite3_temp_directory char pointer. As explained here:
https://www.sqlite.org/c3ref/temp_directory.html
This is also the first place sqlite3 checks.

>From anywhere else sqlite3 checks the value of those env variables:
SQLITE_TMPDIR, TMPDIR, TMP, TEMP, USERPROFILE. And if everything
fails, sqlite3 goes in the standard system location for temporary
files (e.g., /tmp in Linux).
It is the case?

I agree with Zsb?n Ambrus that it should be documented.

On Mon, Jul 27, 2015 at 11:30 PM, Simon Slavin  wrote:
>
>> On 27 Jul 2015, at 10:18pm, Zsb?n Ambrus  wrote:
>>
>> On Mon, Jul 27, 2015 at 9:35 PM, Simon Slavin  
>> wrote:
>>> On 27 Jul 2015, at 8:03pm, Zsb?n Ambrus  wrote:
>>> I tried this once a couple of years ago, and both platforms use whatever 
>>> the expected variable name was for that OS.  In other words, a native 
>>> programmer to that OS would get whatever behaviour they expected.
>>
>> Hopefully that means TMPDIR on unix and TEMP on windows, which seem to
>> be the most widely used environment variables for this.
>>
>> [snip]
>>
>> From the source code, it seems that for at least some operations,
>> sqlite3 checks the following environment variables: SQLITE_TMPDIR,
>> TMPDIR, TMP, TEMP, USERPROFILE.
>
> Sorry, I got a detail wrong.  I tested Mac, Windows and Linux, not Unix.  On 
> all platforms I checked to see that changing the expected documented 
> environment variable had the right effect.  I didn't test what happened if 
> you changed another variable.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Paolo Bolzoni]

Dear list,

I have a somewhat large sqlite3 db (about 120GB) and I need to create
some indexes on it, but I get  "database or disk is full" while I have
still about 300GB of free disk space.

However the /tmp directory is only 5GB so I suspect that sqlite3 has
not enough space there. I found the temp_store_directory, but it is
deprecated. So I was wondering, what is the suggested alternative?
Why it is deprecated?


Your faithfully,
Paolo

[sqlite] step and reset or reset and step?

[Paolo Bolzoni]

Dear list,

The subject already says it all, I was wondering what is the best
practice for a prepared statement that need to be used an unknown
number of times.

It is better to reset and step (as many times as needed) or step
and reset after? Or there is no real difference?

Yours faithfully,
Paolo

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Paolo Bolzoni]

> I can't confirm that 100% off the top of my head but I'm uncoordinated
> enough to repeatedly confuse the bind and column value API calls and use
> 0-based indices for both and haven't noticed any really untoward behaviour
> (beyond my code not working and requiring fixing).

I know the feeling, I made it wrong so many times that I almost wanted
to "fix" the index in my thin C++ wrapper...

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Paolo Bolzoni]

I kinda get your point, but still we are speaking of the C interface
all the time. Sure in other contexts, like the TCL/SQL, the indexes
start from 1. However the bind and the column function are both in the
C interface.

Oh, well... I just have to wrap my mind around it. Definitely not a
real problem, I was merely curious.

On Mon, Mar 2, 2015 at 1:23 AM, Jay Kreibich  wrote:
>
> On Mar 1, 2015, at 5:33 PM, Richard Hipp  wrote:
>
>> On 3/1/15, Paolo Bolzoni  wrote:
>>> Dear everyone,
>>>
>>> I find strange and confusing that bind indexes start from 1 (docs in
>>> [1]) and instead column indexes start from 0 (doc in [2]). Is there
>>> any technical reason or it is just an unlucky legacy?
>>
>> Seems like there was a reason for this, 11 years ago when it went in,
>> but I cannot now call that reason to mind right this moment.  So lets
>> just call it unlucky legacy.
>
> Every database I?ve every used starts SQL parameter indexes from 1.  I?m not 
> sure it is part of the SQL standard, but it is more or less the defacto 
> standard of SQL APIs, and might be considered part of the SQL language.
>
> I assume column indexes start at 0 because of C.  Column indexes are used 
> within the context of the C language API, so it makes some sense to use C 
> conventions.
>
>
> I know they?re very easy to confuse (I still do it myself), but I can kind of 
> see why we ended up there, even if it isn?t a very strong reason.
>
>  -j
>
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but 
> showing it to the wrong people has the tendency to make them feel 
> uncomfortable." -- Angela Johnson
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why bind indexes start from 1 and column indexes start from 0?

[Paolo Bolzoni]

Dear everyone,

I find strange and confusing that bind indexes start from 1 (docs in
[1]) and instead column indexes start from 0 (doc in [2]). Is there
any technical reason or it is just an unlucky legacy?

Yours faithfully,
Paolo


[1] https://www.sqlite.org/c3ref/bind_blob.html
[2] https://www.sqlite.org/c3ref/column_blob.html

[sqlite] Sqlite3 tutorial and reference manuals?

[Paolo Bolzoni]

About SQL I like the w3c schools[1]; about sqlite how do you want to
use it? Via C interface? If so, check the website "sqlite in 5 minutes
or less"[2].

[1]http://www.w3schools.com/sql/
[2]https://www.sqlite.org/quickstart.html

On Sat, Feb 21, 2015 at 7:33 PM, russ lyttle  wrote:
> I'm new to sqlite and not that experienced with SQL in general. Are
> there any good sqlite tutorials or references better than the first few
> pages returned by a google search?
>
> I have reference and tutorial books for MySql.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] Problem with method numRows() in Sqlite3

[Paolo Bolzoni]

As far as I know the only way to know the number of returned
rows is scanning all the query result. Isn't it?

On Mon, Sep 23, 2013 at 5:48 PM, John McKown
 wrote:
> Basically, this is telling you that there is no such function as
> "Sqlite3::numRows". I did a fast scan of the sqlite3 documentation, and
> there does not appear to be a sqlite3 function which returns this type of
> information. The PHP interface appears to just be a wrapper for PHP around
> existing sqlite3 calls, and since sqlite3 does not implement this function,
> neither does the PHP interface.
>
> From my looking, such as it was, I would say that you need to either do a
> $result->fetcharray(), then see how big that array is. Or, if you don't
> really want the data, do a
> $result=$db->query("select count(*) as numRows from table");
>
> if you just want a count. Lastly, if you need to check the number of rows
> without fetching them, then you'll need to run both queries.
>
> Again, the above is "as best as I can tell". I am not a PHP expert.
>
>
> On Sun, Sep 22, 2013 at 10:50 PM, pisey phon  wrote:
>
>> Dear Sqlite Team
>>
>>
>> I have some problem with Sqlite3.
>>
>>
>> Would you mind if I want to ask you a question?
>>
>>
>> I got an error "Call to undefined method SQLite3Result::numRows()". and
>> here
>> is my code:
>> $db = new Sqlite3("sample.db");
>> $result = $db->query("select * from table");
>> $rows = $result->numRows();
>>
>>
>> Please help me. Thanks in advance.
>>
>>
>> Pisey Phon
>> Junior Web Developer.
>>
>>
>>
>> --
>> View this message in context:
>> http://sqlite.1065341.n5.nabble.com/Problem-with-method-numRows-in-Sqlite3-tp71420.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> As of next week, passwords will be entered in Morse code.
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with method numRows() in Sqlite3

[Paolo Bolzoni]

You are using a binding, right?
Please, can you show the problem with the plain C interface?

On Mon, Sep 23, 2013 at 5:50 AM, pisey phon  wrote:
> Dear Sqlite Team
>
>
> I have some problem with Sqlite3.
>
>
> Would you mind if I want to ask you a question?
>
>
> I got an error "Call to undefined method SQLite3Result::numRows()". and here
> is my code:
> $db = new Sqlite3("sample.db");
> $result = $db->query("select * from table");
> $rows = $result->numRows();
>
>
> Please help me. Thanks in advance.
>
>
> Pisey Phon
> Junior Web Developer.
>
>
>
> --
> View this message in context: 
> http://sqlite.1065341.n5.nabble.com/Problem-with-method-numRows-in-Sqlite3-tp71420.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EncFs + sqlite3

[Paolo Bolzoni]

What do you mean with "if you do not ATTTACH databases"?

I should not use the ATTACH DATABASE command to add
a second database to a connection or I cannot use a db at
all?

On Mon, Sep 23, 2013 at 3:43 PM, Clemens Ladisch <clem...@ladisch.de> wrote:
> Paolo Bolzoni wrote:
>> I was wondering, is using sqlite3 under EncFs safe?
>> For "safe" I mean is the db is strong against data corruption
>> as in a usual filesystem?
>
> EncFS implements the .fsync callback but not .fsyncdir, so the deletion
> of the master journal is not synchronized, so your data and transactions
> are safe in case of a crash only if you do not ATTTACH databases or if
> you are using a journaling file system.  (EncFS does not implement
> .lock, but it is not a network file system, so it can rely on the
> kernel's automatic handling of locking for local files.)
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] EncFs + sqlite3

[Paolo Bolzoni]

Dear list,
I was wondering, is using sqlite3 under EncFs safe?
For "safe" I mean is the db is strong against data corruption
as in a usual filesystem?

Do anyone has experience? It would be reasonably easy to
test?

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

Re: [sqlite] SQLite clusters?

[Paolo Bolzoni]

I did not know that, I will look more into it. Thanks!

On Tue, Sep 17, 2013 at 10:36 PM, Petite Abeille
<petite.abei...@gmail.com> wrote:
>
> On Sep 17, 2013, at 10:19 PM, Paolo Bolzoni <paolo.bolzoni.br...@gmail.com> 
> wrote:
>
>> Sorry for the out topic, but why you want to leave 0mq? We always found it 
>> great...
>
> Isn't nanomsg the successor of ZeroMQ? I.e. same guy, same project, mark 4 or 
> 5?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite clusters?

[Paolo Bolzoni]

> We have an app that uses SQLite, running in 4 servers, with 0mq (nanomsg 
> soon) to get locking exclusive on writes on all databases and pass data 
> sending raw sql inserts, updates and deletes. We don't have lot of nor big 
> writes, never use triggers that modify data or calculate secundary data nor 
> call now() on sql. We have not tried to run it on more servers or with bigger 
> write load.
Sorry for the out topic, but why you want to leave 0mq? We always
found it great...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Paolo Bolzoni]

Another weird sentence in the mailing list
of probably most used DB that is really free.

On Sun, Sep 1, 2013 at 6:10 PM, Etienne  wrote:
> On Sun, Sep 1, 2013, at 17:50, Clemens Ladisch wrote:
>> Ulrich Telle wrote:
>> > Am 31.08.2013 22:01, schrieb Etienne:
>> >> I simply wanted to warn the OP that wxSQLite, while free, does NOT use
>> >> salts:
>> >
>> > Well, that's not completely true. The encryption extension coming with
>> > wxSQLite3 uses a different IV (initial vector) for each database page.
>> > True is that the IVs are not random, but deduced from the page number.
>> > However, I don't see much difference between generating an IV
>> > algorithmic or using a random nonce which is stored at the end of each
>> > database page
>>
>>  says:
>> | Randomization is crucial for encryption schemes to achieve semantic
>> | security, a property whereby repeated usage of the scheme under the
>> | same key does not allow an attacker to infer relationships between
>> | segments of the encrypted message.
>>
>> Without a random IV/nonce, every page is guaranteed to encrypt to the
>> same data if the contents and the key have not changed.  Thus, wxSQLite3
>> gives an attacker the ability to determine whether any particular page
>> has changed, by comparing the old and new versions.  With SEE, rewriting
>> a page will encrypt to a different value because the IV changes even for
>> otherwise unchanged pages.
>>
>> > The weak point of probably all SQLite encryption methods is that the
>> > unencrypted content of the first 16 bytes of a SQLite database file is
>> > well known.
>>
>> Many file formats have fixed parts.  However, this is not a problem with
>> properly implemented encryption algorithms.
>>
>> Regards,
>> Clemens
>
> Amen.
>
> In this particular case, you get what you pay for.
>
> Regards,
> Etienne
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is SQLite a DBMS?

[Paolo Bolzoni]

Wait a second, this is a mailing list where you need
to register to write. Isn't it?

It means the OP actually registered but he did not
try to seek for wikipedia sqlite in google?

It is just me or it is quite weird?

On Sun, Sep 1, 2013 at 12:23 PM, Simon Slavin  wrote:
>
> On 1 Sep 2013, at 5:39am, kimtiago  wrote:
>
>> I need to know if SQLite is a DBMS and why.
>
> That's okay.  Just have your teacher post here and we'll tell them directly.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Paolo Bolzoni]

That last sentence is quite weird, a good encryption system should
give a random-like sequence even with very low-entropy input.

On the other hand removing patterns definitely cannot hurt.

On Sat, Aug 31, 2013 at 4:38 PM, Etienne  wrote:
>> Thank you for your quick response.
>>
>> I am looking for freeware. If freeware not available, I have to implement
>> encryption support for sqlite on winrt.
>>
>> What is the procedure to implement encryption support on winrt?
>>
>> Thanks,
>> dd
>
>  might help you.
>
> It does supply free AES128/256 encryption (look in /sqlite3/secure/src/).
>
> However, be aware that there is no compression involved here, making (any) 
> encryption significantly weaker.
>
> Regards,
> Etienne
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to encrypt sqlite db

[Paolo Bolzoni]

There is a non-free version of sqlite that
encrypt the db. If it is that you want then
you have to contact them directly.

Otherwise just use sqlite on a EncFs mounted
disk?

On Sat, Aug 31, 2013 at 2:25 PM, dd  wrote:
> Hi All,
>
>   I have to encrypt sqlite database on winrt.
>
>   What are all the necessary steps to do to encrypt sqlite database?
>
>   Thanks in advance.
>
> Regards,
> dd
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using VACUUM on an empty file will create a defaultdatabase

[Paolo Bolzoni]

I also have hard time follow...
My answer would be: "yes,"


What is the problem?

On Thu, Jul 25, 2013 at 8:51 AM,   wrote:
> I'm not sure what you want to say me. "echo 'garbage' > some/random/file"
> will also overwrite non-empty files which is more problematic.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Integer data type

[Paolo Bolzoni]

Or just use a BLOB.

On Fri, Jul 19, 2013 at 2:29 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 19 Jul 2013, at 1:20pm, Paolo Bolzoni <paolo.bolzoni.br...@gmail.com> 
> wrote:
>
>> True, I was thinking as follow up of what I
>> mentioned in the first message.
>> -11 is the result of the cast of 4294967285
>> from unsigned int to int in a machine
>> where int are 32 bits long.
>
> Then don't cast.  If you think your numbers are going to overflow 
> 9223372036854775807 and you need integer precision for numbers that big, I'm 
> afraid you're going to have to use another SQL engine.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Integer data type

[Paolo Bolzoni]

Interesting problem, can you add a new comparison
operator to sqlite3?

On Fri, Jul 19, 2013 at 2:19 PM, Hick Gunter <h...@scigames.at> wrote:
> It might change the sort order...
>
> -Ursprüngliche Nachricht-----
> Von: Paolo Bolzoni [mailto:paolo.bolzoni.br...@gmail.com]
> Gesendet: Freitag, 19. Juli 2013 14:11
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Integer data type
>
> After all do you really care if the unsigned int 4294967285 is stored as -11?
>
> On Fri, Jul 19, 2013 at 1:13 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>>
>> On 19 Jul 2013, at 11:02am, techi eth <techi...@gmail.com> wrote:
>>
>>> Definition of integer data type will talk for signed integer. What
>>> about unsigned integer ?Are they also be part of same data type.
>>
>> Yes.  SQLite has no special type for an unsigned integer.  Just store them 
>> as integers.
>>
>> The page you were looking at lists everything.  If it's not on that page it 
>> doesn't exist.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> Klitschgasse 2 – 4, A - 1130 Vienna, Austria
> FN 157284 a, HG Wien
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This e-mail is confidential and may well also be legally privileged. If you 
> have received it in error, you are on notice as to its status and accordingly 
> please notify us immediately by reply e-mail and then delete this message 
> from your system. Please do not copy it or use it for any purposes, or 
> disclose its contents to any person as to do so could be a breach of 
> confidence. Thank you for your cooperation.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Integer data type

[Paolo Bolzoni]

True, I was thinking as follow up of what I
mentioned in the first message.
-11 is the result of the cast of 4294967285
from unsigned int to int in a machine
where int are 32 bits long.


On Fri, Jul 19, 2013 at 2:16 PM, Richard Hipp <d...@sqlite.org> wrote:
> On Fri, Jul 19, 2013 at 8:11 AM, Paolo Bolzoni <
> paolo.bolzoni.br...@gmail.com> wrote:
>
>> After all do you really care if the unsigned
>> int 4294967285 is stored as -11?
>>
>
> To be pedantic:  SQLite stores 4294967285 as 4294967285.  It is
> 18446744073709551605 that gets stored as -11.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Integer data type

[Paolo Bolzoni]

After all do you really care if the unsigned
int 4294967285 is stored as -11?

On Fri, Jul 19, 2013 at 1:13 PM, Simon Slavin  wrote:
>
> On 19 Jul 2013, at 11:02am, techi eth  wrote:
>
>> Definition of integer data type will talk for signed integer. What about
>> unsigned integer ?Are they also be part of same data type.
>
> Yes.  SQLite has no special type for an unsigned integer.  Just store them as 
> integers.
>
> The page you were looking at lists everything.  If it's not on that page it 
> doesn't exist.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Integer data type

[Paolo Bolzoni]

As far as I know there is no unsigned integer in
sqlite3. If you need cast to a signed integer of the
same size before using sqlite3.

On Fri, Jul 19, 2013 at 12:02 PM, techi eth  wrote:
> Definition of integer data type will talk for signed integer. What about
> unsigned integer ?Are they also be part of same data type.
>
> *INTEGER*. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8
> bytes depending on the magnitude of the value.
> http://www.sqlite.org/datatype3.html
>
> Bye
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 : Corrupt/IO Error

[Paolo Bolzoni]

By default sqlite3 is fairly safe, so when you change a pragma
ensure that you are not disabling a safety feature.

To get an idea, check this thread where I had the opposite need
(more speed, less safety)
http://sqlite.1065341.n5.nabble.com/What-pragma-to-use-to-get-maximum-speed-at-expense-of-safety-td68488.html

About backups, here is the documentation:
http://www.sqlite.org/backup.html
I personally always used the historical way. And copied the backups in a
remote server.

On Fri, Jul 19, 2013 at 12:00 PM, techi eth <techi...@gmail.com> wrote:
> Thanks for answer.
>
> I can do integrity check by PRAGMA integrity_check.Please correct me if i
> wrong.
> Here backup means copying database file OR we can create query to SQLite
> for backup or copy.
>
> Cheers-
> Techi
>
>
>
> On Wed, Jul 17, 2013 at 4:42 PM, Paolo Bolzoni <
> paolo.bolzoni.br...@gmail.com> wrote:
>
>> On Wed, Jul 17, 2013 at 12:55 PM, techi eth <techi...@gmail.com> wrote:
>> > 2)  How do we make database safe from these error Or What is
>> Possibility to
>> > access database after error.
>> After error, as Stephan said you are out of luck.
>>
>> You should avoid this errors in the first place:
>> - make backup often,
>> - keep the safety features of sqlite3 on,
>> - use a error resilient filesystem (e.g., zfs),
>> - use ecc memory.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

> Some changes, if blob is bigger than a few bytes, you should normalize them. 
> If 2 blobs are equal, their id must be equal and you don't waste time 
> comparing nor memory joining blob content. So you get:
They are quite small (max ~70 bytes...)

>  DROP TABLE IF EXISTS tour_blob;
>  CREATE TABLE tour_blob (id INTEGER PRIMARY KEY,
>n_blob blob);
>
>  DROP TABLE IF EXISTS tour;
>  CREATE TABLE tour (id integer,
>score integer NOT NULL,
>cost integer NOT NULL,
>last_poi integer,
>FOREIGN KEY (unsorted_path) REFERENCES tour_blob(id) ON 
> DELETE CASACADE,
>FOREIGN KEY (unsorted_path_tmp) REFERENCES tour_blob(id) 
> ON DELETE CASACADE,
>PRIMARY KEY(id));
>
> You can replace tour_unsorted_path_idx with a new index too:
>
>> DROP INDEX IF EXISTS tour_unsorted_path_idx;
>> CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path);
>
>  DROP INDEX IF EXISTS tour_unsorted_path_last_poi_cost_idx;
>  CREATE INDEX tour_unsorted_path_last_poi_cost_idx ON tour (unsorted_path, 
> last_poi, cost);
>
> Take care and make cost the last one, because cost is compared with 
> inequality.
Very interesting, this sounds a very good point!

> You use '==' instead '=', take care too. I made the same error in a mail some 
> weeks ago.
Ops...

> For this query:
>
>>
>> SQL STATEMENT: SELECT id FROM tour ORDER BY id LIMIT ?1
>>  8< -
>> Query:   SELECT id FROM tour ORDER BY id LIMIT ?1
>> Explain: 0 0 0 SCAN TABLE tour USING INTEGER PRIMARY KEY (~100 rows)
>>  - >8
>
> I don't know why it doesn't use the primary index. Perhaps analyze statistics 
> before solves the problem.
It seems Explain does not consider the LIMIT, it should not be
something to worry about.

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

Re: [sqlite] SQLite3 : Corrupt/IO Error

[Paolo Bolzoni]

On Wed, Jul 17, 2013 at 12:55 PM, techi eth  wrote:
> 2)  How do we make database safe from these error Or What is Possibility 
> to
> access database after error.
After error, as Stephan said you are out of luck.

You should avoid this errors in the first place:
- make backup often,
- keep the safety features of sqlite3 on,
- use a error resilient filesystem (e.g., zfs),
- use ecc memory.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

On Tue, Jul 16, 2013 at 8:13 PM, Eduardo  wrote:

> Can you show us the query and/or schemas? If not:
Sure, I appended everything in the bottom of this email.
Unfortunately gmail will mess-up the layout, I hope it will be
readable.

(See here, it seems google does not know the mean of "should")
https://groups.google.com/forum/#!topic/google-mail-problem-solving-uk/p8KyYZR2e04

> a) Does it JOIN multiple big tables (in rows and/or columns)?
No, but I got a sub query is probably the culprit.

> e) Did you normalize the database?
Should be, yes.

> In both cases (if you can show us the query/schema or not) what do you really 
> want to ask to the database? (not to us but to sqlite3, perhaps the query can 
> be reformulated)


Here is everything... I think the problem is shown in the:
Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows)
that is part of a sub-query.


-- First statement (sets the pragmas):

PRAGMA foreign_keys = ON;
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
PRAGMA cache_size = -10240;
PRAGMA auto_vacuum = NONE;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA secure_delete = OFF;
PRAGMA temp_store = 0;

-- Second statement (creates/clean the schema):
BEGIN TRANSACTION;
DROP TABLE IF EXISTS tour;
CREATE TABLE tour (id integer,
  score integer NOT NULL,
  cost integer NOT NULL,
  last_poi integer,
  unsorted_path blob,
  unsorted_path_tmp blob,
  PRIMARY KEY(id));

DROP INDEX IF EXISTS tour_unsorted_path_idx;
CREATE INDEX tour_unsorted_path_idx ON tour (unsorted_path);

DROP TABLE IF EXISTS categories;
CREATE TABLE categories (tour_id integer NOT NULL,
order_idx integer NOT NULL,
value integer NOT NULL,
FOREIGN KEY (tour_id) REFERENCES tour(id) ON
DELETE CASCADE);

DROP INDEX IF EXISTS cats_tour_id_idx;
CREATE INDEX cats_tour_id_idx ON categories (tour_id);

DROP TABLE IF EXISTS path;
CREATE TABLE path (tour_id integer NOT NULL,
  order_idx integer NOT NULL,
  node_id integer NOT NULL,
  FOREIGN KEY (tour_id) REFERENCES tour(id) ON DELETE CASCADE);

DROP INDEX IF EXISTS path_tour_id_idx;
CREATE INDEX path_tour_id_idx ON path (tour_id);

DROP TRIGGER IF EXISTS set_last_poi_trg;
CREATE TRIGGER set_last_poi_trg AFTER INSERT ON path BEGIN
  UPDATE tour SET last_poi = ( SELECT node_id
   FROM path
   WHERE tour_id == NEW.tour_id
   ORDER BY order_idx DESC LIMIT 1 )
  WHERE id == NEW.tour_id; END;
COMMIT;
VACUUM;



-- Finally the statement executed in a normal program
-- execution with the explain if appliable.

-- I prepare all this  statements and put in hash table
-- and finalize them at the end of the program.
-- This output comes from the first time, when I store them.


SQL STATEMENT: INSERT INTO path (tour_id, order_idx, node_id) VALUES
(?1, ?2, ?3)

SQL STATEMENT: INSERT INTO tour (score, cost) VALUES (?1, ?2)

SQL STATEMENT: UPDATE tour SET unsorted_path_tmp=?1 WHERE id=?2
 8< -
Query:   UPDATE tour SET unsorted_path_tmp=?1 WHERE id=?2
Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
 - >8

SQL STATEMENT: UPDATE tour SET unsorted_path=unsorted_path_tmp,
unsorted_path_tmp = NULL WHERE id = ?1;
 8< -
Query:   UPDATE tour SET unsorted_path=unsorted_path_tmp,
unsorted_path_tmp = NULL WHERE id = ?1;
Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
 - >8


SQL STATEMENT: INSERT INTO categories (tour_id, order_idx, value)
VALUES (?1, ?2, ?3)

SQL STATEMENT: DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi
AND t1.cost < t2.cost)
 8< -
Query:   DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi
AND t1.cost < t2.cost)
Explain: 0 0 0 SEARCH TABLE tour USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
 - >8

 8< -
Query:   DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi
AND t1.cost < t2.cost)
Explain: 0 0 0 EXECUTE LIST SUBQUERY 0
 - >8

 8< -
Query:   DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi == t2.last_poi
AND t1.cost < t2.cost)
Explain: 0 0 0 SCAN TABLE tour AS t1 (~100 rows)
 - >8

 8< -
Query:   DELETE FROM tour WHERE id IN
 (SELECT t2.id FROM tour AS t1 INNER JOIN tour AS t2 ON
 t1.unsorted_path == t2.unsorted_path AND t1.last_poi 

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

I tried the experiment again with -g3 -O0, I got less
information than expected (there are still many unknown
symbols in libsqlite3.so), but the function requiring all
this memory is sqlite3_step.

So maybe it is one complex query?
I would like to avoid excessive swapping on the
production server, maybe I should not worry at all?



On Tue, Jul 16, 2013 at 1:11 PM, Paolo Bolzoni
<paolo.bolzoni.br...@gmail.com> wrote:
> On Tue, Jul 16, 2013 at 1:00 PM, Dan Kennedy <danielk1...@gmail.com> wrote:
>> On 07/16/2013 01:49 AM, Paolo Bolzoni wrote:
>
>> A very large blob or string result?
> I would exclude this, I do use blobs... but they are at most
> few dozen of bytes...
>
>> Code allocates (or leaks)
>> tremendous numbers of sqlite3_stmt* handles?
> Thanks to RAII the code should not leak (also valgrind
> confirm this). I allocate statements, but I deallocate only
> at the end. So it cannot explain a peak in memory usage.
>
>> SQLite has various APIs for querying memory usage:
>>
>>   http://www.sqlite.org/c3ref/memory_highwater.html
>>   http://www.sqlite.org/c3ref/c_status_malloc_count.html
>>
>> Or, using the shell tool, the ".stats" command can be used
>> to access the same values.
> I guess I can see something.
>
> At the moment I am running the test again using a sqlite3
> version compiled with -g3 and -O0 so I hope I can get more
> insight...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

On Tue, Jul 16, 2013 at 1:00 PM, Dan Kennedy <danielk1...@gmail.com> wrote:
> On 07/16/2013 01:49 AM, Paolo Bolzoni wrote:

> A very large blob or string result?
I would exclude this, I do use blobs... but they are at most
few dozen of bytes...

> Code allocates (or leaks)
> tremendous numbers of sqlite3_stmt* handles?
Thanks to RAII the code should not leak (also valgrind
confirm this). I allocate statements, but I deallocate only
at the end. So it cannot explain a peak in memory usage.

> SQLite has various APIs for querying memory usage:
>
>   http://www.sqlite.org/c3ref/memory_highwater.html
>   http://www.sqlite.org/c3ref/c_status_malloc_count.html
>
> Or, using the shell tool, the ".stats" command can be used
> to access the same values.
I guess I can see something.

At the moment I am running the test again using a sqlite3
version compiled with -g3 and -O0 so I hope I can get more
insight...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

The test ended sometime during the night and setting temp_store to 0
the result is exactly the same. I suspect it was the default anyway.



On Mon, Jul 15, 2013 at 9:20 PM, Paolo Bolzoni
<paolo.bolzoni.br...@gmail.com> wrote:
> On Mon, Jul 15, 2013 at 9:08 PM, Eduardo Morras <emorr...@yahoo.es> wrote:
>> On Mon, 15 Jul 2013 20:49:52 +0200
>> Paolo Bolzoni <paolo.bolzoni.br...@gmail.com> wrote:
>>
>>> From 35-40MB to 940MB; I would put massif result but I think the
>>> list deletes attachments.
>>
>> What does PRAGMA temp_store show? Set it to 0 and recheck. Did you compile 
>> with SQLITE_TEMP_STORE set to 3?
> This ones, so no...
> -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_COLUMN_METADATA=1
> -DSQLITE_ENABLE_UNLOCK_NOTIFY -DSQLITE_SECURE_DELETE
> "Recheck" needs some time. The test lasted almost 7 hours...
> I start it now...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

On Mon, Jul 15, 2013 at 9:08 PM, Eduardo Morras <emorr...@yahoo.es> wrote:
> On Mon, 15 Jul 2013 20:49:52 +0200
> Paolo Bolzoni <paolo.bolzoni.br...@gmail.com> wrote:
>
>> From 35-40MB to 940MB; I would put massif result but I think the
>> list deletes attachments.
>
> What does PRAGMA temp_store show? Set it to 0 and recheck. Did you compile 
> with SQLITE_TEMP_STORE set to 3?
This ones, so no...
-DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_COLUMN_METADATA=1
-DSQLITE_ENABLE_UNLOCK_NOTIFY -DSQLITE_SECURE_DELETE
"Recheck" needs some time. The test lasted almost 7 hours...
I start it now...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

On Mon, Jul 15, 2013 at 8:59 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 15 Jul 2013, at 7:49pm, Paolo Bolzoni <paolo.bolzoni.br...@gmail.com> 
> wrote:
>
>> From 35-40MB to 940MB; I would put massif result but I think the
>> list deletes attachments.
>
> Do you have in-memory tables ?
No.

> Do you use sqlite3_exec() ?
Twice, to activate the pragma and to create the db.

> Do you have SELECTs for which there is no good index, forcing sqlite3 to make 
> up its own ?
I checked with EXPLAIN and no. Seems not.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

>From 35-40MB to 940MB; I would put massif result but I think the
list deletes attachments.

On Mon, Jul 15, 2013 at 8:41 PM, Stephan Beal <sgb...@googlemail.com> wrote:
> On Mon, Jul 15, 2013 at 8:39 PM, Paolo Bolzoni <
> paolo.bolzoni.br...@gmail.com> wrote:
>
>> So, sorry if the question sounds very vague. But what can
>> cause high memory usage in sqlite?  A large transaction
>> maybe?
>>
>
> What is "high"? In my apps sqlite tends to use 200-400kb or so, which i
> don't consider to be all that high considering what it's capable of doing
> for me.
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Memory usage of sqlite3

[Paolo Bolzoni]

I wrote an C++ application that uses sqlite3 to save part
of the data when it become larger than a known threshold.

The idea is to use at most a known quantity of memory;
to check if it is working I executed a relevant test
using valgrind's massif.

It worked fairly well most of the time, but in two cases
there is a peak of memory usage from sqlite.

So, sorry if the question sounds very vague. But what can
cause high memory usage in sqlite?  A large transaction
maybe?

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

[sqlite] Calling sqlite3_column_int on a column with a too large number?

[Paolo Bolzoni]

The subject pretty much says it all.
I am curious to know what sqlite3 does when asking for the result value of
a column passing a type that is too small.

For example in my system int are 32 bits as result of a query
I got a 10 billions: what happens using sqlite3_column_int to get
that result?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What number(2) means?

[Paolo Bolzoni]

Yes, I think it is possible to put only for
compatibility reasons. Maybe in some
other db systems you can set the magnitude?

On Wed, Jul 10, 2013 at 10:06 AM, Woody Wu <narkewo...@gmail.com> wrote:
> On Wed, Jul 10, 2013 at 09:53:41AM +0200, Paolo Bolzoni wrote:
>> See here:
>> http://www.sqlite.org/datatype3.html
>>
>> I think  it just means Integer. And its
>> size depends on the magnitude of the
>> number stored.
>>
>
> I've read the doc, it's not so easy to understand.
>
> Did you mean, in number(N), N will not make difference, right?
>
> Thanks!
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What number(2) means?

[Paolo Bolzoni]

See here:
http://www.sqlite.org/datatype3.html

I think  it just means Integer. And its
size depends on the magnitude of the
number stored.

On Wed, Jul 10, 2013 at 9:44 AM, Woody Wu  wrote:
> I have an old dabase, some integer columns were defined as type of
> number(2).  What does this mean in sqlite3?  What's the data ragne it
> can represent, and how much bytes it will consume when stored?
>
> Thanks in advance.
>
> --
> I can't go back to yesterday - because I was a different person then
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Literature on the information theory behind SQL(lite)?

[Paolo Bolzoni]

On Wed, Jul 10, 2013 at 3:27 AM, Jay A. Kreibich  wrote:
>   really *know* OOP.  Similarly, even if you're an expert C++ developer,
>   if C++ is you're only OOP language, you still don't really get what
>   clean OOP is all about (because C++ sure as heck isn't that, even if
>   it is a darn useful language).
C++ is a multi-paradigm language; and it is kinda an important point.
It supports. mainly:
- Procedural programming;
- Data abstraction;
- Object-oriented programming; and
- Generic programming.
The best to write good code (good means easy-to-read, efficient,
easy-to-maintain, small...) is usually a combination of all this paradigms.
So, if you say C++ is not a clean OOP language it is true. But it is
actually a design decision that bring better code... if used well.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Literature on the information theory behind SQL(lite)?

[Paolo Bolzoni]

I am sorry the part that look Greek to you is actually fairly
important base of the theory behind SQL, the relational algebra.

A possible answer to your question is the classic book: Database
Systems, The complete book of Ullman et. all.
It is comprehensive, so it should satisfy all your curiosities.


On Tue, Jul 9, 2013 at 7:17 PM, Stephan Beal  wrote:
> Hi, all,
>
> i am looking for literature which describes the data/information
> theory/formalisms behind sqlite and similar projects. Google has so far led
> me to the extremes of "introduction to SQL" (don't need it) and articles
> which start using Greek symbols in the 3rd paragraph (and which point my
> brain shuts down). Can anyone suggest materials somewhere between these two
> extremes? i'm not so much interested in implementation details as i am
> about learning the theory behind the implementation details (after which
> the implementation details will make more sense to me).
>
> :-?
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> http://gplus.to/sgbeal
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GUI for SQLite

[Paolo Bolzoni]

So... no? It is gratis, but not open. thanks.

On Wed, Jun 26, 2013 at 5:28 PM, Michael Black <mdblac...@yahoo.com> wrote:
> Free doesn't necessarily mean open source
>
> http://www.valentina-db.com/en/get-free-studio
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Bolzoni
> Sent: Wednesday, June 26, 2013 10:26 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] GUI for SQLite
>
> Are you sure it is free? I cannot find the code...
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GUI for SQLite

[Paolo Bolzoni]

Are you sure it is free? I cannot find the code...

On Wed, Jun 26, 2013 at 6:28 AM, jorje  wrote:
>  Take a look on a free gui tool -- Valentina Studio. Amazing product!  IMO
> this is the best manager for SQLite for all platforms.
> http://www.valentina-db.com/en/valentina-studio-overview
>
>
>
>
> --
> View this message in context: 
> http://sqlite.1065341.n5.nabble.com/GUI-for-SQLite-tp11673p69626.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiprocess accessing SQLite connection

[Paolo Bolzoni]

Yes... but it should be clear that each process will have
only access to the data it has written and it won't have
access to the data written by other processes.

On Mon, Jun 17, 2013 at 3:08 PM, Igor Tandetnik  wrote:
> On 6/17/2013 1:01 AM, Vijay Khurdiya wrote:
>>
>> In that case can I have separate DB file associated with each process.
>
>
> Of course. Just pass different file names to sqlite3_open or similar.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Serialize an in-memory database

[Paolo Bolzoni]

If you accept the slowdown of .dump you can directly use the backup option...

On Thu, Jun 6, 2013 at 2:10 PM, Philip Bennefall  wrote:
>
> - Original Message - From: "Simon Slavin" 
>
> To: ; "General Discussion of SQLite Database"
> 
> Sent: Thursday, June 06, 2013 1:45 PM
>
> Subject: Re: [sqlite] Serialize an in-memory database
>
>
>
> On 6 Jun 2013, at 10:45am, Philip Bennefall  wrote:
>
>> I have a bunch of data structures in memory that I am looking to replace
>> with an SqLite database, primarily for the purpose of avoiding reinventing
>> the wheel with various sorts etc. I would then like to serialize the data
>> into a memory buffer and do additional processing before finally rendering
>> it to disk. The additional processing might include compression, encryption,
>> and a few other things specific to my application.
>
>
> Two problems:
>
> Unlike the SQLite file format, the format SQLite uses when it keeps things
> in memory is not published, and changes from version to version.  Because
> the writers of SQLite expect the in-memory format to be accessed only by
> things built into the SQLite API, you have to read the source code to know
> what's going on.  So any routines you come up will have to just deal with
> whatever they find rather than trying to understand its structure.  Also
> your data will be able to restored only back to versions of SQLite where the
> internal data format hasn't changed.
>
> SQLite does not, by its nature, keep everything in one long block of memory.
> It allocates and frees smaller blocks of memory as data is stored or
> deleted, and also as it needs to create temporary structures such as indexes
> needed to speed up a specific command.  So turning a stored database into
> one stream of octets takes more than just reading a section of memory.
>
> Rather than try to mess with the internals of SQLite I suspect you would be
> better served by doing the following:
>
> 1) Using SQLite's existing in-memory databases to keep your data in memory
> while your app executes.
>
> 2) Writing your own routine in your preferred programming language to dump
> your data into text or octets in memory or disk in whatever format you want.
> One standard way to do this is to generate the SQL commands needed to
> reproduce your database.  Since these are very repetitive standard ASCII
> commands they compress down extremely well and you can do encryption at the
> same time using any of a number of standard libraries.  Data in this format
> has the added advantages that it is human-readable (after decompression) and
> can be passed straight to sqlite3_exec() to rebuild the database.  However,
> you might prefer to invent your own format, perhaps more like CSV, that
> makes implicit use of your data structures.
>
> Simon.=
>
> Hi Simon,
>
> Oh I never intended to attempt to rip the data right out of an SqLite memory
> database. I realize that it is not at all the same as the disk file that I
> could create with, say, the backup API. I am considering two options:
>
> 1. Writing a memory vfs that I use when I want to save my data, backing up
> the existing in-memory database to a new database that uses this memory vfs
> and then taking the data from the resulting block where SqLite writes what
> it thinks is the database file.
>
> 2. Doing something like .dump in the shell, but writing the output to memory
> and then processing that. This seems to be the simplest approach, but would
> waste a lot of space and import/export would be slower as far as I can
> judge. This would primarily be the case if I export as SQL, as I would then
> not be able to reuse prepared statements with parameters but would have to
> use sqlite3_exec.
>
> The memory vfs seems like the most appealing choice in the longterm, but the
> second approach is much more straightforward.
>
> Kind regards,
>
> Philip Bennefall
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Serialize an in-memory database

[Paolo Bolzoni]

What is you use case? Why do you need this?
I am asking because maybe it helps thinking alternate solutions...

On Thu, Jun 6, 2013 at 10:05 AM, Philip Bennefall <phi...@blastbay.com> wrote:
>
> - Original Message - From: "Paolo Bolzoni"
> <paolo.bolzoni.br...@gmail.com>
>
> To: <phi...@blastbay.com>; "General Discussion of SQLite Database"
> <sqlite-users@sqlite.org>
> Sent: Thursday, June 06, 2013 10:02 AM
>
> Subject: Re: [sqlite] Serialize an in-memory database
>
>
>> Sorry I am missing a bit,
>> What is the problem of using sqlite3_backup again?
>>
>
> Hi Paolo,
>
> I would like to avoid using a temporary file, but rather just save and load
> the database as a memory block. Serialize to and from memory, in other
> words.
>
>
> Kind regards,
>
> Philip Bennefall
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Serialize an in-memory database

[Paolo Bolzoni]

Sorry I am missing a bit,
What is the problem of using sqlite3_backup again?

On Thu, Jun 6, 2013 at 10:01 AM, Philip Bennefall  wrote:
>
> - Original Message - From: "Simon Slavin" 
> To: ; "General Discussion of SQLite Database"
> 
> Sent: Thursday, June 06, 2013 12:15 AM
>
> Subject: Re: [sqlite] Serialize an in-memory database
>
>
>
> On 5 Jun 2013, at 8:38pm, Philip Bennefall  wrote:
>
>> On 5 Jun 2013, at 8:32pm, Petite Abeille  wrote:
>>
>>> write to tmpfs… read the file into byte[]… do what you meant to do… to
>>> reload…  write byte[] do tmpfs… open db… and be merry… or something along
>>> these lines...
>>
>>
>> I don't want it in a file, however. I want it in a memory block.
>
>
> That's why you read from tmpfs (or any other file stored in any other file
> system) into byte[].  Once your data is in byte[] you will have entire
> SQLite database in one run of memory.
>
> You can't usefully store a memory database of SQLite because SQLite's data
> in memory isn't all in one big run of memory.  The data is stored in various
> little chunks, some here, some there. If you tried to read the data directly
> out of those chunks you would have to read lots of little chunks, not one
> big run of continuous memory.  And you'd be storing lots of pointers to
> various locations in memory.  When you 'restore' the data back into memory
> you're not going to be allocated the same locations in memory so those
> pointers won't mean anything any more.
>
> A database stored in a file, however, has pointers to locations in that file
> instead of pointers to locations in memory.  If you save and restore the
> whole file in one big run, those pointers will become valid again: the same
> bits of data will be at the same offsets of the file.
>
> Doesn't have to be tmpfs.  You can use any file system that SQLite thinks is
> file storage rather than memory storage.
>
> Simon.
>
> Hi Simon,
>
> Since I don't believe that Windows for example has tmpfs (seems to be a Unix
> thing), would the idea of constructing a vfs that just reads and writes a
> huge memory block be doable? If so, how difficult of a task do you estimate
> that this might be? I want to reuse as much of the existing vfs code as
> possible (e.g. I don't want to reimplement randomness, date etc). Could you
> possibly give me some pointers? I read the chapter about the virtual file
> systems, but it seems incomplete.
>
> Kind regards,
>
> Philip Bennefall
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Force use SMALLINT datatype

[Paolo Bolzoni]

And double check that you actually VACUUM the db
(manually or via auto vacuum).

On Tue, May 28, 2013 at 4:23 PM, Richard Hipp  wrote:
> On Tue, May 28, 2013 at 10:15 AM, Wang, Gao  wrote:
>
>> Dear all,
>>
>> I use sqlite to store, query and perform simple math operations on large
>> datasets of small integers -- all values I have in the dataset are -1, 0, 1
>> and 2. These integers are stored as INTEGER (4 byte). I desperately need a
>> way to reduce the size of my database because I have dozens of databases
>> like this with size ~100G which takes too much of my storage. I'd like some
>> other integer type for {-1,0,1,2} such as "SMALLINT" (
>> http://www.sqlite.org/datatype3.html). I understand sqlite data types are
>> dynamic and it does not make a difference to create a field of type
>> SMALLINT. I wonder if there are something I can do to fulfill my need.
>> Anyway to hack into the sqlite.c codes?
>>
>>
> SQLite does not allocate 4 bytes to every integer.  It uses a variable
> number of bytes (between 1 and 9) depending on the magnitude of the
> integer.  See http://www.sqlite.org/fileformat2.html#record_format for
> additional information.  Values of 0 and 1 take 1 bytes.  Values of -1 and
> 2 take two bytes.
>
> See also the sqlite3_analyzer.exe utility.  Running sqlite3_analyzer.exe on
> one of your database files might give you a better idea of where space is
> being used.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?

[Paolo Bolzoni]

To get answers in a similar situation I found useful the EXPLAIN QUERY
PLAN command.

On Fri, May 24, 2013 at 3:56 PM, kyan  wrote:
> On Fri, May 24, 2013 at 4:46 PM, Marc L. Allen
> wrote:
>
>> It's exhaustive in that it absolutely verifies if the key exists or not.
>>  However, it doesn't necessarily do a full database scan.  I assume it uses
>> available indexes and does a standard lookup on the key.
>>
>> So, it still might be fast enough for what you want (though I missed the
>> beginning of the thread).
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:
>> sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
>>
>> On Fri, May 24, 2013 at 7:07 AM, kyan  wrote:
>>
>> It is exhaustive
>>
>
> Thank you both for your answers.
>
> Since I am writing code for an application server that connects to
> databases of different database vendors used by other development teams I
> have no way of knowing anything about the underlying database, so I will
> not take any chances.
>
> --
> Constantine Yannakopoulos
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Savepoint and Begin Transaction, performance-wise

[Paolo Bolzoni]

In my programs I often use savepoints so in case
of errors I can rollback leaving the db untouched.

I use savepoint instead of begin transaction because
it fits more naturally in the nested structure of a C
program.
When I write a function I do not need to recall if the
caller already  opened the transaction.
When the last savepoint is released the data is safely
written in the db.

First of all, did I understand correctly?
Secondly, is there a significant difference of performance
to use nested savepoints instead of a single transaction?

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

Re: [sqlite] sqlite shell: arrow keys

[Paolo Bolzoni]

maybe rlwrap can help?

On Fri, May 17, 2013 at 4:27 AM, Fehmi Noyan ISI  wrote:
> Depends on your system I think. Here is what is contained in shell.c
> readline.h is the thing making all those history and similar things you use 
> in your linux command line shell available.
>
> #ifdef HAVE_EDITLINE
> # include 
> #endif
> #if defined(HAVE_READLINE) && HAVE_READLINE==1
> # include 
> # include 
> #endif
>
>
>
>
> 
>  From: Roman Fleysher 
> To: General Discussion of SQLite Database 
> Sent: Friday, May 17, 2013 11:49 AM
> Subject: [sqlite] sqlite shell: arrow keys
>
>
> Dear SQLiters,
>
> I am new to SQLite and learning it (and SQL) using shell. It would make life 
> easier if arrow keys on keyboard could be used to scroll through command 
> history and along command for editing. Is there a way to enable this?
>
> Thank you,
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Scan table in explain query. Question.

[Paolo Bolzoni]

At the moment EXPLAIN gives me a very small number of rows
for all the queries (thanks to indexes) a part of this one, so if I
understand correctly ANALYZE is not needed anymore.

Or I can get a speed up executing once in a while?

Regards,
Paolo


On Sat, May 11, 2013 at 1:06 PM, Clemens Ladisch <clem...@ladisch.de> wrote:
> Paolo Bolzoni wrote:
>> sqlite> explain query plan SELECT id FROM tour LIMIT 1;
>> 0|0|0|SCAN TABLE tour USING COVERING INDEX tour_unsorted_path_idx
>> (~100 rows)
>>
>> I am not still sure I understand completely the output of explain
>> query plan.
>
> It means that SQLite estimates that the table (or its index) contains
> about 100 rows.
>
> The LIMIT clause is *not* used for this estimate.
>
>> To get this simple result, sqlite3 actually scans the whole
>> table?
>
> No.  Without the LIMIT, the query would scan the entire index.  With the
> LIMIT, the scan is stopped after the first row.
>
>> I just have a question about query written in the begin, in my
>> application I use it to know if a table is empty. I execute it
>> and just use sqlite3_step return value. Errors a part, if it is
>> SQLITE_DONE the table is empty, if it is SQLITE_ROW it is not.
>
> This is more a documentation than an optimization improvement, but if
> you are not interested in the value of any particular column, you should
> not request it in the first place; i.e., use something like:
>   SELECT 0 FROM tour LIMIT 1;
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Scan table in explain query. Question.

[Paolo Bolzoni]

sqlite> explain query plan SELECT id FROM tour LIMIT 1;
0|0|0|SCAN TABLE tour USING COVERING INDEX tour_unsorted_path_idx
(~100 rows)

I am not still sure I understand completely the output of explain
query plan. But for sure, a small number in the end sounds a good
thing. In my application after working with indexes and removing
the big numbers of rows I got a great speed up.

I just have a question about query written in the begin, in my
application I use it to know if a table is empty. I execute it
and just use sqlite3_step return value. Errors a part, if it is
SQLITE_DONE the table is empty, if it is SQLITE_ROW it is not.

To get this simple result, sqlite3 actually scans the whole
table? If it is so, can be avoided?

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

Re: [sqlite] SQL join with "or" condition gives unexpected results

[Paolo Bolzoni]

Seriously? Care to explain?

On Thu, May 9, 2013 at 4:48 PM, Petite Abeille  wrote:
>
> On May 9, 2013, at 3:30 PM, Romulo Ceccon  wrote:
>
>> But my application is (so far) database agnostic
>
> Reconsider. Agnosticism is not a feature. It's a bug.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index question

[Paolo Bolzoni]

Interesting, so sqlite3 is smart enough to actually move the blob instead
of copying and deleting? If it is the case it is indeed great.

On Sun, Apr 28, 2013 at 5:12 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 28 Apr 2013, at 3:51pm, Paolo Bolzoni <paolo.bolzoni.br...@gmail.com> 
> wrote:
>
>> So I should write my BLOB in another (not-indexed) table, UPDATE the
>> indexed table copying from the other,
>> and finally delete the line in the first table? All in one transaction?
>
> That would work and would be a good solution if you change one BLOB at a time.
>
> Or you can assemble the BLOB in memory and then write it all in one UPDATE 
> command.
>
> Or you can have another BLOB column, an unindexed one, in the existing table 
> and do your editing to the value in that column:
>
> BEGIN
> build up the BLOB
> UPDATE myTable SET permBLOB = tempBLOB, tempBLOB = 0 WHERE recID = 123456
> END
>
> This assumes that your BLOB is a long one which takes a lot of space.  If it 
> isn't, you can just leave the value there rather than zeroing it out.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index question

[Paolo Bolzoni]

I use only the C API. The function causing it in my program is:
  fprintf(stderr, "%s\n", sqlite3_errmsg(db));

and I think it comes out from:
  sqlite3.c:70718:  zErr = sqlite3MPrintf(db, "cannot open %s column
for writing", zFault);



On Sun, Apr 28, 2013 at 6:05 PM, Richard Hipp <d...@sqlite.org> wrote:
> On Sun, Apr 28, 2013 at 10:02 AM, Paolo Bolzoni <
> paolo.bolzoni.br...@gmail.com> wrote:
>
>> I get this error: "cannot
>> open indexed column for writing."
>>
>> What does it mean?
>>
>>
> That error is not coming from SQLite.  Are you using a wrapper program of
> some kind - or perhaps a third-party query tool?
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index question

[Paolo Bolzoni]

So I should write my BLOB in another (not-indexed) table, UPDATE the
indexed table copying from the other,
and finally delete the line in the first table? All in one transaction?

On Sun, Apr 28, 2013 at 4:23 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 28 Apr 2013, at 3:02pm, Paolo Bolzoni <paolo.bolzoni.br...@gmail.com> 
> wrote:
>
>> And it seems quite an improvement, alas now I get this error: "cannot
>> open indexed column for writing."
>
> You have a column of type BLOB.
> It is now an indexed column.
> You are trying to use the BLOB editing routines to write into that BLOB 
> rather than just replacing the value in one operation.
>
> SQLite won't let you open an indexed BLOB value in editing mode because it 
> doesn't know what you want the index to reflect while the value is being 
> edited.
>
> You can replace the BLOB value in a single operation (using UPDATE).  Or you 
> can remove any index from that column. Sorry.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index question

[Paolo Bolzoni]

Sorry, it seems gmail messed up the layout just before sending.

On Sun, Apr 28, 2013 at 4:02 PM, Paolo Bolzoni
<paolo.bolzoni.br...@gmail.com> wrote:
> I was playing with indexes, I started checking one my queries with
> EXPLAIN QUERY PLAN and
> I got this result:
> selectidorder   fromdetail
> --  --  --
> -
> 0   0   1   SEARCH TABLE tour AS t2 USING
> INTEGER PRIMARY KEY (rowid=?) (~1 rows)
> 0   1   0   SCAN TABLE tour AS t1 (~5000 rows)
>
> Adding an index on the column unsorted_path it becomes:
> selectidorder   fromdetail
> --  --  --
> -
> 0   0   1   SEARCH TABLE tour AS t2 USING
> INTEGER PRIMARY KEY (rowid=?) (~1 rows)
> 0   1   0   SEARCH TABLE tour AS t1 USING
> INDEX t (unsorted_path=?) (~2 rows)
>
> And it seems quite an improvement, alas now I get this error: "cannot
> open indexed column for writing."
>
> What does it mean?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index question

[Paolo Bolzoni]

I was playing with indexes, I started checking one my queries with
EXPLAIN QUERY PLAN and
I got this result:
selectidorder   fromdetail
--  --  --
-
0   0   1   SEARCH TABLE tour AS t2 USING
INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0   1   0   SCAN TABLE tour AS t1 (~5000 rows)

Adding an index on the column unsorted_path it becomes:
selectidorder   fromdetail
--  --  --
-
0   0   1   SEARCH TABLE tour AS t2 USING
INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0   1   0   SEARCH TABLE tour AS t1 USING
INDEX t (unsorted_path=?) (~2 rows)

And it seems quite an improvement, alas now I get this error: "cannot
open indexed column for writing."

What does it mean?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What pragma to use to get maximum speed (at expense of safety)?

[Paolo Bolzoni]

Thanks everyone, I cannot apply all the suggestions (for example I do
use foreign keys), but probably I can improve the performance of the
calculations.

On Sat, Apr 27, 2013 at 6:48 AM, David King <dk...@ketralnis.com> wrote:
>> The idea of temporary tables in-memory is nice, but I do not know how
>> to apply it.
>> I see in the documentation I can use the TEMP in CREATE TABLE, but I am not
>> sure of the effect.
>> Does it mean that the table is created in memory and it is lost in
>> sqlite3_close?
>
>
>
> There are a few things at work here. You can have your entire database in 
> memory (and is lost on close) by giving sqlite_open the filename :memory:
>
> Additionally and unrelatedly, you can create temporary tables (CREATE TEMP 
> TABLE, as you say). These are tables that disappear on close. They are 
> associated with the database connection, not the database file itself. They 
> are usually stored in temporary files in a different on-disk location from 
> your main database file.
>
> With the pragma temp_store set to MEMORY, the temporary tables can be 
> entirely in memory, even if the rest of your database isn't. Using this, you 
> can mix on-disk tables (in your regular database) and in-memory tables 
> (temporary tables, when temp_store=MEMORY). That lets you easily keep some 
> intermediate stuff in memory and only flush out the disk tables when you're 
> ready. Sometimes (depending on your usage patterns of course) this batching 
> can help speed things up.
>
>
>>
>> On Fri, Apr 26, 2013 at 8:07 PM, David King <dk...@ketralnis.com 
>> (mailto:dk...@ketralnis.com)> wrote:
>> > auto_vacuum Turn off autovacuum and just run it yourself when you're idle
>> > foreign_keys Turn off foreign keys checks (or just don't use foreign keys)
>> > ignore_check_constraints Same
>> > journal_mode OFF might actually be faster than MEMORY, but disables 
>> > rollback support
>> > locking_mode EXCLUSIVE can be mildly faster in some cases
>> >
>> >
>> > secure_delete OFF
>> >
>> >
>> > synchronous OFF as you said
>> >
>> >
>> > cache_size beef up as you said. this won't always make everything faster 
>> > though, since it can starve the other processes on your machine for memory 
>> > even for rarely-used sqlite data when they could potentially make better 
>> > use of the OS page cache.
>> > temp_store set to MEMORY, this will help if you have queries that create 
>> > temporary tables, even if you're not doing so yourself (e.g. unindexed 
>> > group bys)
>> >
>> >
>> >
>> > If you can, use an entirely :memory: database. This may not work for you, 
>> > but if it does, great.
>> >
>> > Since you're not using journal_mode = WAL this is moot for you, but if you 
>> > were I'd say turn off wal_autocheckpoint and wal_checkpoint yourself when 
>> > you're idle
>> >
>> > For my somewhat-similar use case I find that writing intermediate changes 
>> > to an in-memory table (insert into my_temp_table) and periodically 
>> > flushing those to disk (insert into real_table select from my_temp_table; 
>> > delete from my_temp_table) can help speed things up if a lot of 
>> > index-updating is involved in the on-disk table.
>> >
>> > Make sure you're doing all of your inserts in a transaction. inserting is 
>> > pretty cheap, but committing a transaction is expensive, and if you're not 
>> > in a transaction each insert is its own transaction
>> >
>> > Make sure you're re-using your prepared statements
>> >
>> > Play with page_size to get it right for your write patterns
>> >
>> > Don't use a connection concurrently, it's doing internal locking anyway. 
>> > If you must, use the shared page cache. If you're doing it from multiple 
>> > processes, use WAL mode.
>> >
>> >
>> >
>> >
>> > On Friday, 26 April, 2013 at 10:44, Paolo Bolzoni wrote:
>> >
>> > > The subject pretty much says it all, I use sqlite3 as a way to save
>> > > temporary results from a calculation.
>> > >
>> > > In this context I do not care about safety of the data. If the program
>> > > fails or there is a blackout I will just delete the sqlite3 file, 
>> > > eventually
>> > > fix the bug, and restart.
>> > >
>> > > At the moment I use this pragmas:
>> > >
>> > > PRAGMA synchronous = OFF;
>> > > PRAGMA journal_mo

Re: [sqlite] What pragma to use to get maximum speed (at expense of safety)?

[Paolo Bolzoni]

The idea of temporary tables in-memory is nice, but I do not know how
to apply it.
I see in the documentation I can use the TEMP in CREATE TABLE, but I am not
sure of the effect.
Does it mean that the table is created in memory and it is lost in
sqlite3_close?

On Fri, Apr 26, 2013 at 8:07 PM, David King <dk...@ketralnis.com> wrote:
> auto_vacuum Turn off autovacuum and just run it yourself when you're idle
> foreign_keys Turn off foreign keys checks (or just don't use foreign keys)
> ignore_check_constraints Same
> journal_mode OFF might actually be faster than MEMORY, but disables rollback 
> support
> locking_mode EXCLUSIVE can be mildly faster in some cases
>
>
> secure_delete OFF
>
>
> synchronous OFF as you said
>
>
> cache_size beef up as you said. this won't always make everything faster 
> though, since it can starve the other processes on your machine for memory 
> even for rarely-used sqlite data when they could potentially make better use 
> of the OS page cache.
> temp_store set to MEMORY, this will help if you have queries that create 
> temporary tables, even if you're not doing so yourself (e.g. unindexed group 
> bys)
>
>
>
> If you can, use an entirely :memory: database. This may not work for you, but 
> if it does, great.
>
> Since you're not using journal_mode = WAL this is moot for you, but if you 
> were I'd say turn off wal_autocheckpoint and wal_checkpoint yourself when 
> you're idle
>
> For my somewhat-similar use case I find that writing intermediate changes to 
> an in-memory table (insert into my_temp_table) and periodically flushing 
> those to disk (insert into real_table select from my_temp_table; delete from 
> my_temp_table) can help speed things up if a lot of index-updating is 
> involved in the on-disk table.
>
> Make sure you're doing all of your inserts in a transaction. inserting is 
> pretty cheap, but committing a transaction is expensive, and if you're not in 
> a transaction each insert is its own transaction
>
> Make sure you're re-using your prepared statements
>
> Play with page_size to get it right for your write patterns
>
> Don't use a connection concurrently, it's doing internal locking anyway. If 
> you must, use the shared page cache. If you're doing it from multiple 
> processes, use WAL mode.
>
>
>
>
> On Friday, 26 April, 2013 at 10:44, Paolo Bolzoni wrote:
>
>> The subject pretty much says it all, I use sqlite3 as a way to save
>> temporary results from a calculation.
>>
>> In this context I do not care about safety of the data. If the program
>> fails or there is a blackout I will just delete the sqlite3 file, eventually
>> fix the bug, and restart.
>>
>> At the moment I use this pragmas:
>>
>> PRAGMA synchronous = OFF;
>> PRAGMA journal_mode = MEMORY;
>> PRAGMA cache_size = -10240;
>>
>> Is there anything other I can do to speed-up sqlite3 at expenses of
>> safety?
>>
>> Thanks,
>> Paolo
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org (mailto:sqlite-users@sqlite.org)
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What pragma to use to get maximum speed (at expense of safety)?

[Paolo Bolzoni]

The subject pretty much says it all, I use sqlite3 as a way to save
temporary results from a calculation.

In this context I do not care about safety of the data. If the program
fails or there is a blackout I will just delete the sqlite3 file, eventually
fix the bug, and restart.

At the moment I use this pragmas:

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
PRAGMA cache_size = -10240;

Is there anything other I can do to speed-up sqlite3 at expenses of
safety?

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

Re: [sqlite] Writing in a blob

[Paolo Bolzoni]

Writing a FS as sqlite3 as backend sounds an interesting challenge.

But I would like recalling everyone, that the question was about
writing an arbitrary precision integer in the DB considering that
the library writes the representation in a FILE*.

At the end I wrote a little FILE* wrapper around blob_open
handles that support only the modes "r" or "w". And I applied to
an existing blob or to newly created zeroblob.
It seems working fine.

Paolo

On Fri, Apr 26, 2013 at 5:30 PM, Roland Hughes
<rol...@logikalsolutions.com> wrote:
> Speaking as an IT professional with 20+ years in the field, I would have
> to say adding any kind of "file system" support to SQLite would be a
> horrible thing.  Yes, I've used Oracle.  I've also used the only real
> product Oracle has, RDB on OpenVMS.  I've written books covering MySQL,
> PostgreSQL, and Object databases like POET.
>
> Lite is called lite for a reason.  Bare functionality with a lot of
> speed.
>
> The architects for this product need to take a lesson from the old DOS
> xBase systems.  Blobs should not be handled as one big unit.   They need
> to be given unique ID's and stored in fixed size chunks off in a hidden
> table much like a "memo" field was back in the day.  The "hidden" or
> child table supporting the blob column would have a key of ID +
> sequence.  The actual row should be ID, Sequence, BytesUsed, Chunk.
> They BytesUsed allows you to keep exact byte sizes.  All Chunk data
> types should be a raw byte data type.  There should be multiple chunk
> column types:  chunk24, chunkM, and chunkXM for 1024 bytes, 1Meg, and
> 10Meg chunk column types.
>
>
> On Tue, 2013-04-23 at 09:50 +0200, Dominique Devienne wrote:
>
>> On Mon, Apr 22, 2013 at 2:10 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>>
>> > On 22 Apr 2013, at 12:39pm, Paolo Bolzoni wrote:
>> > > But I noticed that sqlite3_blob_write cannot increase the size of the
>> > pointed
>> > > open blob. So I ask, there is a way to treat a blob as a stream so I can
>> > write
>> > > or read values in it with ease?
>> >
>> > Unfortunately the size (length) of the BLOB is very significant to the
>> > larger space-handling aspects of SQLite's file format.  Making a BLOB
>> > longer could force SQLite to move the data from page to page and do lots of
>> > other internal reorganisation.  So you can reserve extra space when you
>> > write the BLOB, and you can read whatever you want, but the documentation
>> > is accurate.
>> >
>>
>> I also really wish SQLite blobs would map directly to the usual FILE*
>> semantic, both in being able to grow a blob via writing (and truncate it
>> too), but also and more importantly not rewriting the whole row or blob
>> when modifying only a few bytes of the blob, but only affected pages.
>> Basically another level of indirection, where the row holds only a blob
>> locator (like in Oracle), and the blob value is in separate, not
>> necessarily contiguous pages, as described here for example:
>> http://jonathanlewis.wordpress.com/2013/03/22/lob-update/. That way only
>> modified blob pages would need to participate in the transaction. SQLite is
>> not MVCC like Oracle, but the ability to at least not overwrite the whole
>> blob when changing 1 byte would be great. (I'm assuming there isn't, but
>> I'm no SQLite expert). My $0.02. --DD
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
> Roland Hughes, President
> Logikal Solutions
> (630)-205-1593
>
> http://www.theminimumyouneedtoknow.com
> http://www.infiniteexposure.net
>
> No U.S. troops have ever lost their lives defending our ethanol
> reserves.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Writing in a blob

[Paolo Bolzoni]

It won't be as easy, but I guess I need to get the size of the gmp integer
before so to allocate the blob and later writing in it.

On Mon, Apr 22, 2013 at 2:10 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> On 22 Apr 2013, at 12:39pm, Paolo Bolzoni wrote:
>
>> But I noticed that sqlite3_blob_write cannot increase the size of the pointed
>> open blob. So I ask, there is a way to treat a blob as a stream so I can 
>> write
>> or read values in it with ease?
>
> Unfortunately the size (length) of the BLOB is very significant to the larger 
> space-handling aspects of SQLite's file format.  Making a BLOB longer could 
> force SQLite to move the data from page to page and do lots of other internal 
> reorganisation.  So you can reserve extra space when you write the BLOB, and 
> you can read whatever you want, but the documentation is accurate.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Writing in a blob

[Paolo Bolzoni]

I have a table with a column of type BLOB, and I use a library that
exports or imports
its data structure writing or reading from a FILE*.
(see the bottom of this page:
http://gmplib.org/manual/I_002fO-of-Integers.html#I_002fO-of-Integers )

So my idea was creating a FILE* wrapper around sqlite3_blob_*
functions using fopencookie (in linux) and funopen (in osx).

But I noticed that sqlite3_blob_write cannot increase the size of the pointed
open blob. So I ask, there is a way to treat a blob as a stream so I can write
or read values in it with ease?

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

Re: [sqlite] sqlite abnormal IO writing

[Paolo Bolzoni]

FAQ 19 applies also to SAVEPOINT/RELEASE, not only to BEGIN/COMMIT, right?

On Sun, Apr 21, 2013 at 9:35 AM, Kees Nuyt  wrote:
> On Sun, 21 Apr 2013 11:15:23 +0800 (CST), ?? 
> wrote:
>>
>> Ok,I do not make my means clearly. I mean 60 seconds after my
>> program started,not token 60 seconds to load database file.
>>
>> Now, I got the reason of sqlite abnormal IO writing,it about
>> batch insert. Here is my usecase: One table about 4 column and
>> 500 row,the content of every row no exceed 100 byte, every time
>> I update the whole table using batch query.
>>
>> It should take about one second and 100k IO writing on
>> estimate,BUT it sustained about 20 second and wrote about 2.5M
>> actually.
>>
>> Now,I modify the implement of batch query, it take about one
>> second and 70k IO writing.So there are abnormal something in
>> batch query indeed,Sqlite or Qt SQL module.
>
> Is this still about bulk INSERT or about a SEELCT query?
> In which way did you modify it?
> Perhaps http://sqlite.org/faq.html#q19 helps?
>
> --
> Groet, Cordialement, Pozdrawiam, Regards,
>
> Kees Nuyt
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_column_blob does not return the size of the blob?

[Paolo Bolzoni]

I need to save some blob (arbitrary precision numbers representations) on my
sqlite3 database.

And confused by the function:

const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);

I would expect an output parameter (example type size_t*) to get the size of
the blob, but it is not there.

How can I know the length of the blob to read it correctly?

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

[sqlite] sqlite3 or sqlite4 for a new project?

[Paolo Bolzoni]

Dear list,
The subject pretty much says it all. I want to use sqlite in a small
software I am writing,
and I would like to know what are the reason of using sqlite3 or 4.
The newer version seems quite an improvement, but as far as I can tell
it not very used
yet. What is the reason? It is not stable? Simply too many project
continues with the
version they alway used?

Any insight is welcome,
Paolo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users