Re: [sqlite] confused getting started

[J Decker]

On Mon, Mar 6, 2017 at 8:40 PM, Jens Alfke  wrote:

>
> > On Mar 6, 2017, at 7:57 PM, J Decker  wrote:
> >
> > Pretty silly since it is sqlite, and a mmap'ed database is pretty much
> > sync
>
> Not really. The data still has to be paged in off the disk, which can take
> arbitrarily long (seconds, if the system is under heavy load), and complex
> queries can end up doing a lot of CPU-intensive extra work that takes time
> to run. Although yes, if you’re not super concerned about performance, it’s
> overkill.
>
> (I spent a year or two once working on Chrome, which has a rule that no
> synchronous I/O may take place on the UI thread, because it can impair
> responsiveness. That applies to database queries too.)
>

yup; caveat developor.
and if you are concerned than there's other things at play than a job doing
what it needs to do; and doing nothing otherwise.  Some systems will even
spin-lock for you :)
give me enough rope and I can play cats  cradle with it.
not all things that happen synchronously are because a user did it; and
things that aren't shouldn't have to pretend they were.

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

Re: [sqlite] confused getting started

[Jens Alfke]

> On Mar 6, 2017, at 7:57 PM, J Decker  wrote:
> 
> Pretty silly since it is sqlite, and a mmap'ed database is pretty much
> sync

Not really. The data still has to be paged in off the disk, which can take 
arbitrarily long (seconds, if the system is under heavy load), and complex 
queries can end up doing a lot of CPU-intensive extra work that takes time to 
run. Although yes, if you’re not super concerned about performance, it’s 
overkill.

(I spent a year or two once working on Chrome, which has a rule that no 
synchronous I/O may take place on the UI thread, because it can impair 
responsiveness. That applies to database queries too.)

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

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

[Jacob Sylvia]

This is the bulk of what I needed thanks everyone for their help.  This
worked like a charm...  Now on to why FreeNAS re-encodes the private key
into something else...  Thought I had it... SO CLOSE.

Thanks all!!!


~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail: jacob.syl...@gmail.com
Web: http://www.jacobsylvia.com

On Mon, Mar 6, 2017 at 8:18 PM, Keith Medcalf  wrote:

>
> Get rid of the funny quotes.
>
> bash (at least, and others) interprets funny quotes (backticks) as
> delimiters for commands which are to be executed by calling bash and
> inserting the results into the command.  You only need delimiters around
> identifiers if you insist on using ill-conceived characters within the
> names for identifiers -- like embedded spaces or other characters with
> special meanings such as & | && || ^ etc.
>
> sqlite3 freenas-v1.db "UPDATE system_certificate SET
> cert_certificate=readfile('fullchain.pem'), 
> cert_privatekey=readfile('privkey.pem')
> WHERE ID=4;"
>
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Jacob Sylvia
> > Sent: Monday, 6 March, 2017 17:10
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Need some help running sqlite3 command line
> >
> > So apparently the version of sqlite3 that comes with FreeNAS (3.14.1
> > 2016-08-11 18:53:32 a12d8059770df4bca59e321c266410344242bf7b) AND the
> > (older) version that comes with Ubuntu 16.04 (3.11.0 2016-02-15 17:29:24
> > 3d862f207e3adc00f78066799ac5a8c282430a5f) don't support the readfile
> > method:
> >
> > ./update_certs.sh: line 32: system_certificate: command not found
> > Error: near "SET": syntax error
> >
> > Here is line 32:
> > sqlite3 freenas-v1.db "UPDATE `system_certificate` SET
> > cert_certificate=readfile('fullchain.pem'),
> > cert_privatekey=readfile('privkey.pem') WHERE ID=4;"
> >
> > I might have to resort to the "heredoc" version, even though I'd rather
> > not... Perhaps I can get a binary with the readfile function compiled in,
> > or perhaps I'm missing something?
> >
> > How can I check if that support is compiled in?
> >
> >
> >
> > ~Jake
> > --
> > Jacob D. Sylvia
> > Jake Sylvia's Consulting
> > 430 Cole St.
> > Seekonk, MA 02771
> > Cell: (508) - 954 - 2536
> > EMail: jacob.syl...@gmail.com
> > Web: http://www.jacobsylvia.com
> >
> > On Mon, Mar 6, 2017 at 11:28 AM, Jacob Sylvia 
> > wrote:
> >
> > > Keith, thanks!  I *think* this is exactly what I'm looking for.  I will
> > > try this as soon as I get home.
> > >
> > >
> > > ~Jake
> > > --
> > > Jacob D. Sylvia
> > > Jake Sylvia's Consulting
> > > 430 Cole St.
> > > Seekonk, MA 02771
> > > Cell: (508) - 954 - 2536 <(508)%20954-2536>
> > > EMail: jacob.syl...@gmail.com
> > > Web: http://www.jacobsylvia.com
> > >
> > > On Mon, Mar 6, 2017 at 9:18 AM, Keith Medcalf 
> > wrote:
> > >
> > >>
> > >> If you have a recent enough version of the SQLite3 command line shell:
> > >>
> > >> sqlite3 config.db "update table set value=readfile('pubkey.pem') where
> > >> key='pubkey';"
> > >> sqlite3 config.db "update table set value=readfile('privkey.pem')
> where
> > >> key='privkey';"
> > >>
> > >> or, create a file, called for example, updatekeys.sql containing:
> > >>
> > >> update table set value=readfile('pubkey.pem') where key='pubkey';
> > >> update table set value=readfile('privkey.pem') where key='privkey';
> > >>
> > >> then run in with:
> > >>
> > >> sqlite3 config.db < updatekeys.sql
> > >>
> > >> If you want to copy the existing fields to files you can do something
> > >> like this:
> > >>
> > >> select writefile('pubkey.bak', value) from table where key='pubkey';
> > >> select writefile('privkey.bak', value) from table where key='privkey';
> > >>
> > >>
> > >> This is documented in section 6.1 of the CLI page
> > >> https://www.sqlite.org/cli.html
> > >>
> > >>
> > >> > -Original Message-
> > >> > From: sqlite-users [mailto:sqlite-users-
> > boun...@mailinglists.sqlite.org
> > >> ]
> > >> > On Behalf Of Jacob Sylvia
> > >> > Sent: Sunday, 5 March, 2017 17:39
> > >> > To: sqlite-users@mailinglists.sqlite.org
> > >> > Subject: [sqlite] Need some help running sqlite3 command line
> > >> >
> > >> > Howdy all!
> > >> >
> > >> > I'm working on getting letsencrypt certificates into my FreeNAS
> > >> > installation...  I can't find anyone who can help me with the last
> > >> piece,
> > >> > the sqlite command line piece...
> > >> >
> > >> > The reference post is here:
> > >> > https://forums.freenas.org/index.php?threads/letsencrypt-web
> > >> gui-ssl-need-
> > >> > sqlite3-cli-guru.50678/
> > >> >
> > >> > Basically, I cant figure out how to update a text field in the
> sqlite
> > >> > config database from the command line...
> > >> >
> > >> > The FreeNAS Config file is a sqlite3 database, and the two
> > certificate
> > >> > entries (the certificate and the private key) are stored in "TEXT"
> > >> fields
> > >> > in a specific

Re: [sqlite] confused getting started

[J Decker]

If Python is appealing then 

Node.js + sqlite adapter makes sqlite very easy.
I have my own https://www.npmjs.com/package/sack.vfs
Needs better docs on the Sqlite output I guess

A more proper package that's all promises and async stuff...
https://www.npmjs.com/package/sqlite

Pretty silly since it is sqlite, and a mmap'ed database is pretty much
sync
and array of arbitrarily shaped objects;

But javascript in the browser maybe?  Well there's electron which is chrome
with node.js support also; which gives you all the support of command line
node.js.

For a general editor there's things like VIsual Studio Code and Atom (
atom.io) (and electron is electron.atom.io ) .  Although Notepad++ is still
pretty good :).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[jose isaias cabrera]

Richard Hipp wrote...

On 3/6/17, jose isaias cabrera  wrote:


Richard Hipp wrote...


Have you ever wondered when you should run ANALYZE on an SQLite
database?  It is tricky to figure out when that is appropriate.  The

Thanks for this.  I actually run this ANALYZE weekly with a script.  This
will be better.  I can run it everyday don't do any harm.  Thanks.



Thanks for letting me know.  I don't know if this applies in your case
or not, but reading your note made me realize that the documentation
might be misleading and/or unclear as written.



The "PRAGMA optimize" command should be run from the same database
connection that is doing the heavy queries.  The reason for this is
that the database connection remembers (in RAM) specifically which
tables and indexes it has considered for use and will only run ANALYZE
on those tables for which some prior query would have benefited from
having good sqlite_stat1 numbers during the current session.  That is
why "PRAGMA optimize" should be run as the database connection is
closing, rather than when it is first opened.



So it is not (currently) helpful to run "PRAGMA optimize" from a
separate connection, or a connection that is mostly idle.  It needs to
be the connection that is actually doing the interesting queries so
that SQLite can know which tables need to be analyzed.


This is exactly how it's going to be used...

josé 


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

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Richard Hipp]

On 3/6/17, jose isaias cabrera  wrote:
>
> Richard Hipp wrote...
>
>>Have you ever wondered when you should run ANALYZE on an SQLite
>>database?  It is tricky to figure out when that is appropriate.  The
> Thanks for this.  I actually run this ANALYZE weekly with a script.  This
> will be better.  I can run it everyday don't do any harm.  Thanks.

Thanks for letting me know.  I don't know if this applies in your case
or not, but reading your note made me realize that the documentation
might be misleading and/or unclear as written.

The "PRAGMA optimize" command should be run from the same database
connection that is doing the heavy queries.  The reason for this is
that the database connection remembers (in RAM) specifically which
tables and indexes it has considered for use and will only run ANALYZE
on those tables for which some prior query would have benefited from
having good sqlite_stat1 numbers during the current session.  That is
why "PRAGMA optimize" should be run as the database connection is
closing, rather than when it is first opened.

So it is not (currently) helpful to run "PRAGMA optimize" from a
separate connection, or a connection that is mostly idle.  It needs to
be the connection that is actually doing the interesting queries so
that SQLite can know which tables need to be analyzed.

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

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[jose isaias cabrera]

Richard Hipp wrote...


Have you ever wondered when you should run ANALYZE on an SQLite
database?  It is tricky to figure out when that is appropriate.  The
Thanks for this.  I actually run this ANALYZE weekly with a script.  This 
will be better.  I can run it everyday don't do any harm.  Thanks.


josé 


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

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

[Keith Medcalf]

Get rid of the funny quotes.  

bash (at least, and others) interprets funny quotes (backticks) as delimiters 
for commands which are to be executed by calling bash and inserting the results 
into the command.  You only need delimiters around identifiers if you insist on 
using ill-conceived characters within the names for identifiers -- like 
embedded spaces or other characters with special meanings such as & | && || ^ 
etc.

sqlite3 freenas-v1.db "UPDATE system_certificate SET 
cert_certificate=readfile('fullchain.pem'), 
cert_privatekey=readfile('privkey.pem') WHERE ID=4;"

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jacob Sylvia
> Sent: Monday, 6 March, 2017 17:10
> To: SQLite mailing list
> Subject: Re: [sqlite] Need some help running sqlite3 command line
> 
> So apparently the version of sqlite3 that comes with FreeNAS (3.14.1
> 2016-08-11 18:53:32 a12d8059770df4bca59e321c266410344242bf7b) AND the
> (older) version that comes with Ubuntu 16.04 (3.11.0 2016-02-15 17:29:24
> 3d862f207e3adc00f78066799ac5a8c282430a5f) don't support the readfile
> method:
> 
> ./update_certs.sh: line 32: system_certificate: command not found
> Error: near "SET": syntax error
> 
> Here is line 32:
> sqlite3 freenas-v1.db "UPDATE `system_certificate` SET
> cert_certificate=readfile('fullchain.pem'),
> cert_privatekey=readfile('privkey.pem') WHERE ID=4;"
> 
> I might have to resort to the "heredoc" version, even though I'd rather
> not... Perhaps I can get a binary with the readfile function compiled in,
> or perhaps I'm missing something?
> 
> How can I check if that support is compiled in?
> 
> 
> 
> ~Jake
> --
> Jacob D. Sylvia
> Jake Sylvia's Consulting
> 430 Cole St.
> Seekonk, MA 02771
> Cell: (508) - 954 - 2536
> EMail: jacob.syl...@gmail.com
> Web: http://www.jacobsylvia.com
> 
> On Mon, Mar 6, 2017 at 11:28 AM, Jacob Sylvia 
> wrote:
> 
> > Keith, thanks!  I *think* this is exactly what I'm looking for.  I will
> > try this as soon as I get home.
> >
> >
> > ~Jake
> > --
> > Jacob D. Sylvia
> > Jake Sylvia's Consulting
> > 430 Cole St.
> > Seekonk, MA 02771
> > Cell: (508) - 954 - 2536 <(508)%20954-2536>
> > EMail: jacob.syl...@gmail.com
> > Web: http://www.jacobsylvia.com
> >
> > On Mon, Mar 6, 2017 at 9:18 AM, Keith Medcalf 
> wrote:
> >
> >>
> >> If you have a recent enough version of the SQLite3 command line shell:
> >>
> >> sqlite3 config.db "update table set value=readfile('pubkey.pem') where
> >> key='pubkey';"
> >> sqlite3 config.db "update table set value=readfile('privkey.pem') where
> >> key='privkey';"
> >>
> >> or, create a file, called for example, updatekeys.sql containing:
> >>
> >> update table set value=readfile('pubkey.pem') where key='pubkey';
> >> update table set value=readfile('privkey.pem') where key='privkey';
> >>
> >> then run in with:
> >>
> >> sqlite3 config.db < updatekeys.sql
> >>
> >> If you want to copy the existing fields to files you can do something
> >> like this:
> >>
> >> select writefile('pubkey.bak', value) from table where key='pubkey';
> >> select writefile('privkey.bak', value) from table where key='privkey';
> >>
> >>
> >> This is documented in section 6.1 of the CLI page
> >> https://www.sqlite.org/cli.html
> >>
> >>
> >> > -Original Message-
> >> > From: sqlite-users [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org
> >> ]
> >> > On Behalf Of Jacob Sylvia
> >> > Sent: Sunday, 5 March, 2017 17:39
> >> > To: sqlite-users@mailinglists.sqlite.org
> >> > Subject: [sqlite] Need some help running sqlite3 command line
> >> >
> >> > Howdy all!
> >> >
> >> > I'm working on getting letsencrypt certificates into my FreeNAS
> >> > installation...  I can't find anyone who can help me with the last
> >> piece,
> >> > the sqlite command line piece...
> >> >
> >> > The reference post is here:
> >> > https://forums.freenas.org/index.php?threads/letsencrypt-web
> >> gui-ssl-need-
> >> > sqlite3-cli-guru.50678/
> >> >
> >> > Basically, I cant figure out how to update a text field in the sqlite
> >> > config database from the command line...
> >> >
> >> > The FreeNAS Config file is a sqlite3 database, and the two
> certificate
> >> > entries (the certificate and the private key) are stored in "TEXT"
> >> fields
> >> > in a specific table.  What I need to do, is take the text from the
> two
> >> PEM
> >> > certificate/key files, and, via the command line, update the
> >> > appropriate fields...  I can do "simple" fields, but I can't figure
> out
> >> > how
> >> > to do "TEXT" fields, given the fact that the text has line-breaks,
> etc.
> >> >
> >> > Any help would be greatly appreciated.
> >> >
> >> > Thanks!!!
> >> >
> >> > ~Jake
> >> > --
> >> > Jacob D. Sylvia
> >> > Jake Sylvia's Consulting
> >> > 430 Cole St.
> >> > Seekonk, MA 02771
> >> > Cell: (508) - 954 - 2536
> >> > EMail:jacob.syl...@gmail.com
> >> > Web: http://www.jacobsylvia.com
> >> > ___
> >> 

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

[Jacob Sylvia]

I know what the problem was... bash was interpreting the `table_name` piece
as a command.  I had to escape the backticks...

This looks like it's going to work like a charm!!!

I'll report back!


~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail: jacob.syl...@gmail.com
Web: http://www.jacobsylvia.com

On Mon, Mar 6, 2017 at 7:39 PM, Richard Hipp  wrote:

> On 3/6/17, Jacob Sylvia  wrote:
> >
> > How can I check if that support is compiled in?
> >
>
> The readfile() SQL function was add to the command-line shell in
> SQLite version 3.8.6 released on 2014-08-15.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

On 3/6/17, Jacob Sylvia  wrote:
>
> How can I check if that support is compiled in?
>

The readfile() SQL function was add to the command-line shell in
SQLite version 3.8.6 released on 2014-08-15.

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

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

[Jacob Sylvia]

So apparently the version of sqlite3 that comes with FreeNAS (3.14.1
2016-08-11 18:53:32 a12d8059770df4bca59e321c266410344242bf7b) AND the
(older) version that comes with Ubuntu 16.04 (3.11.0 2016-02-15 17:29:24
3d862f207e3adc00f78066799ac5a8c282430a5f) don't support the readfile method:

./update_certs.sh: line 32: system_certificate: command not found
Error: near "SET": syntax error

Here is line 32:
sqlite3 freenas-v1.db "UPDATE `system_certificate` SET
cert_certificate=readfile('fullchain.pem'),
cert_privatekey=readfile('privkey.pem') WHERE ID=4;"

I might have to resort to the "heredoc" version, even though I'd rather
not... Perhaps I can get a binary with the readfile function compiled in,
or perhaps I'm missing something?

How can I check if that support is compiled in?



~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail: jacob.syl...@gmail.com
Web: http://www.jacobsylvia.com

On Mon, Mar 6, 2017 at 11:28 AM, Jacob Sylvia 
wrote:

> Keith, thanks!  I *think* this is exactly what I'm looking for.  I will
> try this as soon as I get home.
>
>
> ~Jake
> --
> Jacob D. Sylvia
> Jake Sylvia's Consulting
> 430 Cole St.
> Seekonk, MA 02771
> Cell: (508) - 954 - 2536 <(508)%20954-2536>
> EMail: jacob.syl...@gmail.com
> Web: http://www.jacobsylvia.com
>
> On Mon, Mar 6, 2017 at 9:18 AM, Keith Medcalf  wrote:
>
>>
>> If you have a recent enough version of the SQLite3 command line shell:
>>
>> sqlite3 config.db "update table set value=readfile('pubkey.pem') where
>> key='pubkey';"
>> sqlite3 config.db "update table set value=readfile('privkey.pem') where
>> key='privkey';"
>>
>> or, create a file, called for example, updatekeys.sql containing:
>>
>> update table set value=readfile('pubkey.pem') where key='pubkey';
>> update table set value=readfile('privkey.pem') where key='privkey';
>>
>> then run in with:
>>
>> sqlite3 config.db < updatekeys.sql
>>
>> If you want to copy the existing fields to files you can do something
>> like this:
>>
>> select writefile('pubkey.bak', value) from table where key='pubkey';
>> select writefile('privkey.bak', value) from table where key='privkey';
>>
>>
>> This is documented in section 6.1 of the CLI page
>> https://www.sqlite.org/cli.html
>>
>>
>> > -Original Message-
>> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org
>> ]
>> > On Behalf Of Jacob Sylvia
>> > Sent: Sunday, 5 March, 2017 17:39
>> > To: sqlite-users@mailinglists.sqlite.org
>> > Subject: [sqlite] Need some help running sqlite3 command line
>> >
>> > Howdy all!
>> >
>> > I'm working on getting letsencrypt certificates into my FreeNAS
>> > installation...  I can't find anyone who can help me with the last
>> piece,
>> > the sqlite command line piece...
>> >
>> > The reference post is here:
>> > https://forums.freenas.org/index.php?threads/letsencrypt-web
>> gui-ssl-need-
>> > sqlite3-cli-guru.50678/
>> >
>> > Basically, I cant figure out how to update a text field in the sqlite
>> > config database from the command line...
>> >
>> > The FreeNAS Config file is a sqlite3 database, and the two certificate
>> > entries (the certificate and the private key) are stored in "TEXT"
>> fields
>> > in a specific table.  What I need to do, is take the text from the two
>> PEM
>> > certificate/key files, and, via the command line, update the
>> > appropriate fields...  I can do "simple" fields, but I can't figure out
>> > how
>> > to do "TEXT" fields, given the fact that the text has line-breaks, etc.
>> >
>> > Any help would be greatly appreciated.
>> >
>> > Thanks!!!
>> >
>> > ~Jake
>> > --
>> > Jacob D. Sylvia
>> > Jake Sylvia's Consulting
>> > 430 Cole St.
>> > Seekonk, MA 02771
>> > Cell: (508) - 954 - 2536
>> > EMail:jacob.syl...@gmail.com
>> > Web: http://www.jacobsylvia.com
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Richard Hipp]

On 3/6/17, Simon Slavin  wrote:
>
>> See
>> https://www.sqlite.org/draft/pragma.html#pragma_optimize for
>> additional information.
>
> I’m sure this is extremely far-future-looking, but a default mask of 254
> (0xfe) might be better than the stated default of 14 (0x0e).

Default mask changed to 0xfffe, which allows for up to 14 new
default-on optimizations and up to 48 new default-off optimizations.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 feature or regression

[Richard Hipp]

For the benefit of those of us who do not do Ruby, perhaps you could
explain in words what you think it is that SQLite is doing
incorrectly?

On 3/6/17, Vermes Mátyás  wrote:
> Hi,
> I resend my earlier mail, now with an URL instead of attachement:
>
> http://comfirm.hu/pub/sqlite3-regression.rb
>
> The linked ruby script demonstrates a feature of the newer sqlite3
> libraries, which may be a regression.
> --
> Vermes Mátyás  
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] sqlite3 feature or regression

[Vermes Mátyás]

Hi,
I resend my earlier mail, now with an URL instead of attachement:

http://comfirm.hu/pub/sqlite3-regression.rb

The linked ruby script demonstrates a feature of the newer sqlite3 libraries, 
which may be a regression.
-- 
Vermes Mátyás  
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Simon Slavin]

On 6 Mar 2017, at 9:30pm, Richard Hipp  wrote:

> See
> https://www.sqlite.org/draft/pragma.html#pragma_optimize for
> additional information.

I’m sure this is extremely far-future-looking, but a default mask of 254 (0xfe) 
might be better than the stated default of 14 (0x0e).

---



Would it be possible to improve the description of this check to make it clear 
whether all violations, or only the first violation, of each FOREIGN KEY is 
reported ?

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

Re: [sqlite] SQLite 3.18.0 pre-release snapshot

[Jens Alfke]

> On Mar 6, 2017, at 1:30 PM, Richard Hipp  wrote:
> 
> Have you ever wondered when you should run ANALYZE on an SQLite
> database?  It is tricky to figure out when that is appropriate.  The
> new "PRAGMA optimize" command tries to automate the decision for you.

Very nice!! Thanks for implementing this.

I noticed one typo in the docs of this pragma:
_For_ achieve the best long-term query performance…
Should be something like
_To_ achieve the best long-term query performance…

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

Re: [sqlite] .timer command missing from CLI ?

[Simon Slavin]

On 15 Feb 2017, at 7:22pm, Simon Slavin  wrote:

> sqlite> .changes on
> sqlite> .timer on
> Error: unknown command or invalid arguments:  "timer". Enter ".help" for help

Fix now committed and distributed to beta-testers.  The commands '.timer on' 
and '.timer off' once again work in the latest beta version of macOS.

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

Re: [sqlite] sqlite3_set_last_insert_rowid()

[Simon Slavin]

On 6 Mar 2017, at 10:01pm, Richard Hipp  wrote:

> On 3/6/17, Simon Slavin  wrote:
>> 
>> This means that each TRIGGER is its own little story, right ?  Triggers can
>> make their own inserts.  Which may themselves have triggers.  SQLite
>> maintains a happy stack of values for last_insert_rowid(), pushing and
>> popping them each time you enter or exit a trigger ?
> 
> Correct

Thank you.  I wrote one piece of code which assumed this but wasn’t sure 
whether it was certain in future versions.  What you wrote assures me that I 
can assume future versions of SQLite3 will work the same way.

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

Re: [sqlite] sqlite3_set_last_insert_rowid()

[Richard Hipp]

On 3/6/17, Simon Slavin  wrote:
>
> This means that each TRIGGER is its own little story, right ?  Triggers can
> make their own inserts.  Which may themselves have triggers.  SQLite
> maintains a happy stack of values for last_insert_rowid(), pushing and
> popping them each time you enter or exit a trigger ?

Correct
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_set_last_insert_rowid()

[Simon Slavin]

On 6 Mar 2017, at 9:30pm, Richard Hipp  wrote:

>https://www.sqlite.org/draft/releaselog/3_18_0.html   <--- Change log

The mention of sqlite3_set_last_insert_rowid() reminds me of a question I have:

Here’s the documentation for last_insert_rowid():

"If an INSERT occurs within a trigger or within a virtual table method, then 
this routine will return the rowid of the inserted row as long as the trigger 
or virtual table method is running. But once the trigger or virtual table 
method ends, the value returned by this routine reverts to what it was before 
the trigger or virtual table method began."

This means that each TRIGGER is its own little story, right ?  Triggers can 
make their own inserts.  Which may themselves have triggers.  SQLite maintains 
a happy stack of values for last_insert_rowid(), pushing and popping them each 
time you enter or exit a trigger ?

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

[sqlite] SQLite 3.18.0 pre-release snapshot

[Richard Hipp]

There is a fresh source-code snapshot of the unreleased 3.18.0 version
of SQLite up on the download page:

https://www.sqlite.org/download.html
https://www.sqlite.org/draft/releaselog/3_18_0.html   <--- Change log

Have you ever wondered when you should run ANALYZE on an SQLite
database?  It is tricky to figure out when that is appropriate.  The
new "PRAGMA optimize" command tries to automate the decision for you.
Simply run "PRAGMA optimize" just prior to closing your database
connection, and SQLite willl automatically run ANALYZE if it thinks
doing so will improve query performance.  Usually, the "PRAGMA
optimize" will be a no-op.  But every now and then it will reanalyze
important indexes and thereby help you queries to run optimally.  See
https://www.sqlite.org/draft/pragma.html#pragma_optimize for
additional information.  Feedback on this new feature is encouraged.

NOTE TO FOSSIL USERS:

If you access the SQLite sources directly using the Fossil version
control system (https://www.fossil-scm.org/fossil/doc/trunk/www/index.wiki)
then you should upgrade to the latest version of Fossil - probably the
latest unreleased trunk version.  Newer versions of Fossil understand
SHA3-256 hash names on artifacts in addition to the older SHA1 names.
Older versions of Fossil only understand SHA1 names.  We expect to
start using SHA3-256 hash names in the SQLite repository soon. If you
fail to upgrade, your older "fossil" application will give an error
the first time you try to "fossil update" after SHA3 content is added.
See https://www.fossil-scm.org/fossil/doc/trunk/www/hashpolicy.wiki
fpr additional information.



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

Re: [sqlite] confused getting started

[R Smith]

On 2017/03/05 1:03 PM, NTrewartha T-Online wrote:

I too am new to DB's and Sqllite.


Welcome!

There is a GUI tool - which I think is better for learners which ought 
to be mentioned.

http://sqlitebrowser.org/ The "DBBrowser for sqllite".


DBBrowser for SQlite is great, and cross-platform. If you are 
Windows-specific then there are also SQLite Expert 
(www.sqliteexpert.com) and SQLitespeed (www.sqlc.rifin.co.za) for some 
variety and more "Windowsy" interfaces, including some concept-teaching 
sqlite scripts.




Any examples of a C,C++,C# or Python usage for sqllite.?


This question is hard to answer. The simple answer is "Yes, there are 
examples."  You probably would like to know where to find them though, 
and the answer sadly is that Google is much better at that than anyone 
on this forum. All we can accurately say is that SQLite is the most 
widely used of all DB engines, so you should by implication find more 
examples posted for it than for any other DB (and they all have plenty).




I would like sqllite on my raspberry pi 3 after I have gained 
experience under Windows 10.


Sounds like fun!



Judging what the replies to questions, the very new beginners are left 
a bit out in the cold.

Perhaps the documentation ought to cover the need of pure beginners.


Sorry you feel that way, we are in no way averse to beginners and indeed 
welcome them with enthusiasm, we always love seeing new people get into 
the wonderland that is Databasing and SQLite in particular.
That said, this forum essentially answers questions about SQLite, 
whether about the C API, the SQL syntax used, the command-line tool etc. 
Ironically, most questions we answer are about actual SQL and SQL 
queries (whether specific to SQLite or SQL in general). Questions we ask 
(and answers we give) usually deal with a specific subject and item. If 
you say that you are new and want some general ideas on "How to use 
SQLite..." - then that's a really wide question and some people study 
courses lasting a long time to learn that kind of thing - It's not 
something we can just answer in a few sentences.


You could read a book (http://www.sqlite.org/books.html), or follow 
simple web instructions (such as: http://www.sqlite.org/quickstart.html) 
on how to begin with SQLite, if you then try all that and perhaps get 
stuck on a specific item or have a specific question, we'd be happy to 
answer - but we can't really type up an SQL / SQLite manual for you. 
(Though some of my answers feel sufficiently long). :)


We could also point you to some great learning resources on the web, but 
they are already everywhere. This listing specifically includes Python 
references:

http://charlesleifer.com/blog/my-list-of-python-and-sqlite-resources/

TLDR: The people here love beginners, but they offer problem-assistance 
more than actual syllabus tutoring.



Hope this helps!
Ryan

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

Re: [sqlite] Unable to run SQLite programs: error 0xC000007b

[R Smith]

On 2017/03/06 5:34 PM, Rob Richardson wrote:

Greetings!

I used SQLite in a successful program a few years ago.  I am trying to 
resurrect that program for a new customer, but it isn't working, and I'm having 
a hellish time trying to debug it.

I have a Windows 7 XP1 OS, and I'm using Visual Studio 2008, which is what the 
original program was built with.

The original program builds an ActiveX control.  I have a web page (HTML file) 
that includes the control.  I can build the control in debug mode, load the web 
page in Internet Explorer, and the control works.  It opens an SQLite database, 
tries to prepare a statement, and throws an error complaining that a table does 
not exist, even though it does.  The SQL statement and the SQLite file are 
unchanged from the version that is running at the old customer site.

I have stripped out the SQLite code into a separate project so that I can run 
it by itself, and hopefully step through it in a debugger.  However, when I try 
to run it, I get the following message:

"The application was unable to start correctly (0xc07b).  Click OK to close the 
application."

I have a feeling this is related to 32-bit vs 64-bit applications, but I'm not 
sure.  What do I need to do to get this little test program to run?


I have a feeling that your feeling might be wrong. Did you compile the 
second project for a different target bit-ness than the first?

If so, why?
If not, then that cannot be the fault.

Many things could be wrong - My bet would be a DLL that is required and 
isn't in the exe's path (or other path accessible to the exe).


More to the point, show us the line of SQL that gets passed to the 
SQLite interface exactly (the one that fails with that error message 
when your initial program was still working), and then also show us the 
Schema of the table. (In the sqlite3,exe CLI tool, open the database and 
simply do .fullschema and send us the output, or post the DB file 
somewhere). We should be able to help with figuring out why you get the 
error.


Cheers,
Ryan

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

Re: [sqlite] Multithread

[Jens Alfke]

> On Mar 6, 2017, at 7:10 AM, Наиль Шигапов  wrote:
> 
> Today for fetch or write information from different thread, i lock thread if 
> database is lock(write, read,e.t.c) But i don’t want lock my thread. I read 
> from documentation about «vfs» technology in sqlite. Does it make sense to 
> create vfs object and write information there, while database lock, and then 
> write information if the database unlock.

No, the VFS API is for implementing your own storage layer underneath SQLite. 
It wouldn’t help with multithreading.

If you want multiple threads to be able to read from the database 
simultaneously, then your best bet is to create a small pool of SQLite 
connections and have the thread grab a connection from the pool, do its work, 
then return the connection.

Also, you should enable WAL mode, so you’ll be able to read from the database 
while another connection is writing.

There’s nothing you can do about multiple simultaneous writes, though — SQLite 
allows only a single connection to have a write-lock on the db at a time.

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

Re: [sqlite] Unable to run SQLite programs: error 0xC000007b

[Stephen Chrzanowski]

Use explicit paths to where your database is opened from.  Depending on
where the application is run from, from the debugger even, it may launch
the application from one place, but set its running directory to another.
So your application may be trying to read the database file from somewhere
you're not expecting.


On Mon, Mar 6, 2017 at 10:34 AM, Rob Richardson 
wrote:

> Greetings!
>
> I used SQLite in a successful program a few years ago.  I am trying to
> resurrect that program for a new customer, but it isn't working, and I'm
> having a hellish time trying to debug it.
>
> I have a Windows 7 XP1 OS, and I'm using Visual Studio 2008, which is what
> the original program was built with.
>
> The original program builds an ActiveX control.  I have a web page (HTML
> file) that includes the control.  I can build the control in debug mode,
> load the web page in Internet Explorer, and the control works.  It opens an
> SQLite database, tries to prepare a statement, and throws an error
> complaining that a table does not exist, even though it does.  The SQL
> statement and the SQLite file are unchanged from the version that is
> running at the old customer site.
>
> I have stripped out the SQLite code into a separate project so that I can
> run it by itself, and hopefully step through it in a debugger.  However,
> when I try to run it, I get the following message:
>
> "The application was unable to start correctly (0xc07b).  Click OK to
> close the application."
>
> I have a feeling this is related to 32-bit vs 64-bit applications, but I'm
> not sure.  What do I need to do to get this little test program to run?
>
> Thank you very much.
>
> Rob Richardson
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] confused getting started

[Chris Locke]

NT- I write a lot of vb.net programs that use sqlite databases, so will be
happy to run though a beginners guide.
It would be painless to convert from vb.net to c#

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

[sqlite] Unable to run SQLite programs: error 0xC000007b

[Rob Richardson]

Greetings!

I used SQLite in a successful program a few years ago.  I am trying to 
resurrect that program for a new customer, but it isn't working, and I'm having 
a hellish time trying to debug it.

I have a Windows 7 XP1 OS, and I'm using Visual Studio 2008, which is what the 
original program was built with.

The original program builds an ActiveX control.  I have a web page (HTML file) 
that includes the control.  I can build the control in debug mode, load the web 
page in Internet Explorer, and the control works.  It opens an SQLite database, 
tries to prepare a statement, and throws an error complaining that a table does 
not exist, even though it does.  The SQL statement and the SQLite file are 
unchanged from the version that is running at the old customer site.  

I have stripped out the SQLite code into a separate project so that I can run 
it by itself, and hopefully step through it in a debugger.  However, when I try 
to run it, I get the following message:

"The application was unable to start correctly (0xc07b).  Click OK to close 
the application."

I have a feeling this is related to 32-bit vs 64-bit applications, but I'm not 
sure.  What do I need to do to get this little test program to run?

Thank you very much.

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

[sqlite] Multithread

[Наиль Шигапов]

Hellow!

I write small orm based on sqlite database from ios - application! I have one 
or more question about multi-thread in sqlite. 
Today for fetch or write information from different thread, i lock thread if 
database is lock(write, read,e.t.c) But i don’t want lock my thread. I read 
from documentation about «vfs» technology in sqlite. Does it make sense to 
create vfs object and write information there, while database lock, and then 
write information if the database unlock.

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

Re: [sqlite] SQLite3.dll for x64

[Chris Locke]

Or user error.

"Thanks. Also found out where I was going wrong. While creating the .lib
file, weshould be using the following command: lib /def:sqlite3.def
/machine:X64 /out:sqlite3.lib I was skipping the /machine:X64 option
before."

On Mon, Mar 6, 2017 at 10:48 AM, Anick Saha  wrote:

> Hi,
>
> Please look into this issue:
> http://stackoverflow.com/questions/42623284/sqlite3-dll-for-x64
>
> My guess would be that the download link might be directing to the wrong
> set of files.
>
> Thanks,
> Anick
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3.dll for x64

[Scott Robison]

On Mon, Mar 6, 2017 at 3:48 AM, Anick Saha  wrote:

> Hi,
>
> Please look into this issue:
> http://stackoverflow.com/questions/42623284/sqlite3-dll-for-x64
>
> My guess would be that the download link might be directing to the wrong
> set of files.
>

As you've already discovered (based on the posted answer), you just needed
an extra command line option passed to the lib tool.

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

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

[Scott Robison]

On Mon, Mar 6, 2017 at 3:39 AM, Yuri  wrote:

> On 03/06/2017 01:00, Dominique Devienne wrote:
>
>> This is clearly documented inhttps://www.sqlite.org/c3ref/blob_open.html
>> though, so I'm afraid this is "by design". --DD
>>
>
>
> Even though this is documented, parts of this limitation don't appear to
> be reasonable. Updating an integer field in the same row shouldn't affect
> the blob field. Rows can be very large and shouldn't move when individual
> fields are updated.


The potential difficulty here is that integers are not encoded as fixed
sized fields. Depending on the magnitude of the integer that is being
written, it could change sizes from 0 to 9 bytes. Thus the offset of the
blob in the row may change.

Once the fix for the "any table same rowid" problem is available to you,
your best bet is the separate blob table (which I think I read you've
already tried).
-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jacob Sylvia]

Keith, thanks!  I *think* this is exactly what I'm looking for.  I will try
this as soon as I get home.


~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail: jacob.syl...@gmail.com
Web: http://www.jacobsylvia.com

On Mon, Mar 6, 2017 at 9:18 AM, Keith Medcalf  wrote:

>
> If you have a recent enough version of the SQLite3 command line shell:
>
> sqlite3 config.db "update table set value=readfile('pubkey.pem') where
> key='pubkey';"
> sqlite3 config.db "update table set value=readfile('privkey.pem') where
> key='privkey';"
>
> or, create a file, called for example, updatekeys.sql containing:
>
> update table set value=readfile('pubkey.pem') where key='pubkey';
> update table set value=readfile('privkey.pem') where key='privkey';
>
> then run in with:
>
> sqlite3 config.db < updatekeys.sql
>
> If you want to copy the existing fields to files you can do something like
> this:
>
> select writefile('pubkey.bak', value) from table where key='pubkey';
> select writefile('privkey.bak', value) from table where key='privkey';
>
>
> This is documented in section 6.1 of the CLI page
> https://www.sqlite.org/cli.html
>
>
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Jacob Sylvia
> > Sent: Sunday, 5 March, 2017 17:39
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: [sqlite] Need some help running sqlite3 command line
> >
> > Howdy all!
> >
> > I'm working on getting letsencrypt certificates into my FreeNAS
> > installation...  I can't find anyone who can help me with the last piece,
> > the sqlite command line piece...
> >
> > The reference post is here:
> > https://forums.freenas.org/index.php?threads/letsencrypt-
> webgui-ssl-need-
> > sqlite3-cli-guru.50678/
> >
> > Basically, I cant figure out how to update a text field in the sqlite
> > config database from the command line...
> >
> > The FreeNAS Config file is a sqlite3 database, and the two certificate
> > entries (the certificate and the private key) are stored in "TEXT" fields
> > in a specific table.  What I need to do, is take the text from the two
> PEM
> > certificate/key files, and, via the command line, update the
> > appropriate fields...  I can do "simple" fields, but I can't figure out
> > how
> > to do "TEXT" fields, given the fact that the text has line-breaks, etc.
> >
> > Any help would be greatly appreciated.
> >
> > Thanks!!!
> >
> > ~Jake
> > --
> > Jacob D. Sylvia
> > Jake Sylvia's Consulting
> > 430 Cole St.
> > Seekonk, MA 02771
> > Cell: (508) - 954 - 2536
> > EMail:jacob.syl...@gmail.com
> > Web: http://www.jacobsylvia.com
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

[James K. Lowden]

On Sun, 5 Mar 2017 08:28:44 -0800
Yuri  wrote:

> > I think you?re better off reading the existing BLOB value into
> > memory using SELECT, editing it using standard memory-manipulation
> > routines, then writing it back with an UPDATE when you?re ready.
> 
> This is very slow.

Can you quantify that?  How big is the blob? How big is the edit?  How
many edits?  How fast are N edits to the blob?  How fast to read &
write the whole blob?  How much RAM available to the process?  

My design question is, why a blob in the first place?  If you are
making frequent incremental changes to a blob, that suggests the blob
has some structure.  If you decomposed the blob into rows, perhaps the
edit-the-blob problem would go away, and speed/complexity issues with
it.  

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

Re: [sqlite] confused getting started

[Bob Friesenhahn]

On Sun, 5 Mar 2017, Jens Alfke wrote:

No offense intended, but SQLite isn’t an especially 
beginner-friendly tool. It’s a powerful relational database with a 
ton of configurable options, and a somewhat tricky C API, not to 
mention a sophisticated query language that you also need to master 
to make effective use of it. (However, using it from Python should 
be somewhat easier, since the API is a bit higher level and you 
don’t have to worry about things like memory management.)


Previously, I developed a complex database based on SQLite, with a 
Python script (based on the excellent Python APSW extension, which 
incorporates SQLite in its build) to update it.  While the development 
work was done under Linux, the deployment environment was Microsoft 
Windows.


After installing Python and APSW for Windows, not one line of the 
script needed to be changed in order to work perfectly under Windows.


Based on my experience, Python with the APSW extension is an excellent 
way to create "write once" code as long as you are able to use Python.


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

If you have a recent enough version of the SQLite3 command line shell:

sqlite3 config.db "update table set value=readfile('pubkey.pem') where 
key='pubkey';"
sqlite3 config.db "update table set value=readfile('privkey.pem') where 
key='privkey';"

or, create a file, called for example, updatekeys.sql containing:

update table set value=readfile('pubkey.pem') where key='pubkey';
update table set value=readfile('privkey.pem') where key='privkey';

then run in with:

sqlite3 config.db < updatekeys.sql

If you want to copy the existing fields to files you can do something like this:

select writefile('pubkey.bak', value) from table where key='pubkey';
select writefile('privkey.bak', value) from table where key='privkey';


This is documented in section 6.1 of the CLI page 
https://www.sqlite.org/cli.html


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jacob Sylvia
> Sent: Sunday, 5 March, 2017 17:39
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Need some help running sqlite3 command line
> 
> Howdy all!
> 
> I'm working on getting letsencrypt certificates into my FreeNAS
> installation...  I can't find anyone who can help me with the last piece,
> the sqlite command line piece...
> 
> The reference post is here:
> https://forums.freenas.org/index.php?threads/letsencrypt-webgui-ssl-need-
> sqlite3-cli-guru.50678/
> 
> Basically, I cant figure out how to update a text field in the sqlite
> config database from the command line...
> 
> The FreeNAS Config file is a sqlite3 database, and the two certificate
> entries (the certificate and the private key) are stored in "TEXT" fields
> in a specific table.  What I need to do, is take the text from the two PEM
> certificate/key files, and, via the command line, update the
> appropriate fields...  I can do "simple" fields, but I can't figure out
> how
> to do "TEXT" fields, given the fact that the text has line-breaks, etc.
> 
> Any help would be greatly appreciated.
> 
> Thanks!!!
> 
> ~Jake
> --
> Jacob D. Sylvia
> Jake Sylvia's Consulting
> 430 Cole St.
> Seekonk, MA 02771
> Cell: (508) - 954 - 2536
> EMail:jacob.syl...@gmail.com
> Web: http://www.jacobsylvia.com
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

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

[Hick Gunter]

Try using the "here document" syntax.

In .sh

sqlite3 < set ='first line
second line
third line' where ;
TheEnd

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jacob Sylvia
Gesendet: Montag, 06. März 2017 13:25
An: SQLite mailing list 
Betreff: Re: [sqlite] Need some help running sqlite3 command line

I guess I should have mentioned that I'm trying to do this from a script, so 
that makes the command shell difficult.

~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail: jacob.syl...@gmail.com
Web: http://www.jacobsylvia.com

On Mar 6, 2017 7:23 AM, "Hick Gunter"  wrote:

> From sql shell (from a file works the same way):
>
> asql> create temp table test (a text); insert into test values('a
>...> b
>...> c
>...> d');
> rows inserted
> -
> 1
> asql> select * from test;
> a
> --
> a
> b
> c
> d
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Jacob Sylvia
> Gesendet: Montag, 06. März 2017 13:02
> An: SQLite mailing list 
> Betreff: Re: [sqlite] Need some help running sqlite3 command line
>
> Right...  I know the syntax. The problem lies in the 'value' part.
> Because the text has line breaks / carriage returns, I can't figure
> out how to do it appropriately from the command line.
>
> I've tried setting the value in a bash variable, but the breaks get
> interpreted and the syntax fails.
>
> ~Jake
> --
> Jacob D. Sylvia
> Jake Sylvia's Consulting
> 430 Cole St.
> Seekonk, MA 02771
> Cell: (508) - 954 - 2536
> EMail: jacob.syl...@gmail.com
> Web: http://www.jacobsylvia.com
>
> On Mar 6, 2017 6:28 AM, "Clemens Ladisch"  wrote:
>
> > Jacob Sylvia wrote:
> > > Basically, I cant figure out how to update a text field in the
> > > sqlite config database from the command line...
> >
> > sqlite3 config.db "UPDATE SomeTable SET SomeColumn = 'value' WHERE
> > ID =
> 42"
> >
> > You need to know the database file name, the table name, the column
> > name(s), the new value(s), and how to identify the row(s) to update.
> >
> >
> > Regards,
> > Clemens
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use
> of the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you
> have received this communication in error, please immediately notify
> the sender by return e-mail message and delete all copies of the
> original communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

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

[Clemens Ladisch]

Jacob Sylvia wrote:
> The problem lies in the 'value' part. Because the text has line breaks /
> carriage returns, I can't figure out how to do it appropriately from the
> command line.

Just use line breaks:

  $ sqlite3 ":memory:" "select 'first line
  > second line';"
  first line
  second line

What did you try?


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

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

[Jacob Sylvia]

I guess I should have mentioned that I'm trying to do this from a script,
so that makes the command shell difficult.

~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail: jacob.syl...@gmail.com
Web: http://www.jacobsylvia.com

On Mar 6, 2017 7:23 AM, "Hick Gunter"  wrote:

> From sql shell (from a file works the same way):
>
> asql> create temp table test (a text);
> asql> insert into test values('a
>...> b
>...> c
>...> d');
> rows inserted
> -
> 1
> asql> select * from test;
> a
> --
> a
> b
> c
> d
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Jacob Sylvia
> Gesendet: Montag, 06. März 2017 13:02
> An: SQLite mailing list 
> Betreff: Re: [sqlite] Need some help running sqlite3 command line
>
> Right...  I know the syntax. The problem lies in the 'value' part. Because
> the text has line breaks / carriage returns, I can't figure out how to do
> it appropriately from the command line.
>
> I've tried setting the value in a bash variable, but the breaks get
> interpreted and the syntax fails.
>
> ~Jake
> --
> Jacob D. Sylvia
> Jake Sylvia's Consulting
> 430 Cole St.
> Seekonk, MA 02771
> Cell: (508) - 954 - 2536
> EMail: jacob.syl...@gmail.com
> Web: http://www.jacobsylvia.com
>
> On Mar 6, 2017 6:28 AM, "Clemens Ladisch"  wrote:
>
> > Jacob Sylvia wrote:
> > > Basically, I cant figure out how to update a text field in the
> > > sqlite config database from the command line...
> >
> > sqlite3 config.db "UPDATE SomeTable SET SomeColumn = 'value' WHERE ID =
> 42"
> >
> > You need to know the database file name, the table name, the column
> > name(s), the new value(s), and how to identify the row(s) to update.
> >
> >
> > Regards,
> > Clemens
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

From sql shell (from a file works the same way):

asql> create temp table test (a text);
asql> insert into test values('a
   ...> b
   ...> c
   ...> d');
rows inserted
-
1
asql> select * from test;
a
--
a
b
c
d

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jacob Sylvia
Gesendet: Montag, 06. März 2017 13:02
An: SQLite mailing list 
Betreff: Re: [sqlite] Need some help running sqlite3 command line

Right...  I know the syntax. The problem lies in the 'value' part. Because the 
text has line breaks / carriage returns, I can't figure out how to do it 
appropriately from the command line.

I've tried setting the value in a bash variable, but the breaks get interpreted 
and the syntax fails.

~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail: jacob.syl...@gmail.com
Web: http://www.jacobsylvia.com

On Mar 6, 2017 6:28 AM, "Clemens Ladisch"  wrote:

> Jacob Sylvia wrote:
> > Basically, I cant figure out how to update a text field in the
> > sqlite config database from the command line...
>
> sqlite3 config.db "UPDATE SomeTable SET SomeColumn = 'value' WHERE ID = 42"
>
> You need to know the database file name, the table name, the column
> name(s), the new value(s), and how to identify the row(s) to update.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

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

[Jacob Sylvia]

Right...  I know the syntax. The problem lies in the 'value' part. Because
the text has line breaks / carriage returns, I can't figure out how to do
it appropriately from the command line.

I've tried setting the value in a bash variable, but the breaks get
interpreted and the syntax fails.

~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail: jacob.syl...@gmail.com
Web: http://www.jacobsylvia.com

On Mar 6, 2017 6:28 AM, "Clemens Ladisch"  wrote:

> Jacob Sylvia wrote:
> > Basically, I cant figure out how to update a text field in the sqlite
> > config database from the command line...
>
> sqlite3 config.db "UPDATE SomeTable SET SomeColumn = 'value' WHERE ID = 42"
>
> You need to know the database file name, the table name, the column
> name(s), the new value(s), and how to identify the row(s) to update.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor doc patches

[Kim Gräsman]

On Mon, Mar 6, 2017 at 12:36 PM, Richard Hipp  wrote:
> On 3/6/17, Kim Gräsman  wrote:
>> Hi all,
>>
>> I'm reading the documentation on memory
>> (https://www.sqlite.org/malloc.html) again and stumbled on some typos:
>
> Thanks.  Fixed now.

Thanks! Already live on the website, nice.

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

Re: [sqlite] Minor doc patches

[Richard Hipp]

On 3/6/17, Kim Gräsman  wrote:
> Hi all,
>
> I'm reading the documentation on memory
> (https://www.sqlite.org/malloc.html) again and stumbled on some typos:

Thanks.  Fixed now.

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

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

[Clemens Ladisch]

Yuri wrote:
> Updating an integer field in the same row shouldn't affect the blob field.

Integer fields have a variable size (0 to 8 bytes):
http://www.sqlite.org/fileformat2.html#record_format


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

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

[Clemens Ladisch]

Jacob Sylvia wrote:
> Basically, I cant figure out how to update a text field in the sqlite
> config database from the command line...

sqlite3 config.db "UPDATE SomeTable SET SomeColumn = 'value' WHERE ID = 42"

You need to know the database file name, the table name, the column
name(s), the new value(s), and how to identify the row(s) to update.


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

[sqlite] Minor doc patches

[Kim Gräsman]

Hi all,

I'm reading the documentation on memory
(https://www.sqlite.org/malloc.html) again and stumbled on some typos:

> 3.3 Page cache memory
> [... ] It is not unusual to see _the database page cache consumes_ over 10 
> times more memory than the rest of SQLite combined.

This should read "the database page cache _consume_"

> 3.6. Setting memory usage limits
> [...]
> If attempts are made to _allocate more memory that specified_ by the soft 
> heap limit

Should read "allocate more memory _than_ specified"

> [...]
> If SQLite is not able to free up enough auxiliary memory to stay below the 
> limit, _it goes ahead and allocations_ the extra memory

Should read "it goes ahead and _allocates_ the extra memory"

For what it's worth,
- Kim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

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

You might like to read up on SQLite database and record format to gain insight 
into this.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Yuri
Gesendet: Montag, 06. März 2017 11:39
An: SQLite mailing list 
Betreff: Re: [sqlite] [BUG REPORT] Open blob is invalidated by the update to 
unrelated fields in the same row

On 03/06/2017 01:00, Dominique Devienne wrote:
> This is clearly documented
> inhttps://www.sqlite.org/c3ref/blob_open.html
> though, so I'm afraid this is "by design". --DD


Even though this is documented, parts of this limitation don't appear to be 
reasonable. Updating an integer field in the same row shouldn't affect the blob 
field. Rows can be very large and shouldn't move when individual fields are 
updated.


Yuri

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] SQLite3.dll for x64

[Anick Saha]

Hi,

Please look into this issue:
http://stackoverflow.com/questions/42623284/sqlite3-dll-for-x64

My guess would be that the download link might be directing to the wrong
set of files.

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

[sqlite] Need some help running sqlite3 command line

[Jacob Sylvia]

Howdy all!

I'm working on getting letsencrypt certificates into my FreeNAS
installation...  I can't find anyone who can help me with the last piece,
the sqlite command line piece...

The reference post is here:
https://forums.freenas.org/index.php?threads/letsencrypt-webgui-ssl-need-sqlite3-cli-guru.50678/

Basically, I cant figure out how to update a text field in the sqlite
config database from the command line...

The FreeNAS Config file is a sqlite3 database, and the two certificate
entries (the certificate and the private key) are stored in "TEXT" fields
in a specific table.  What I need to do, is take the text from the two PEM
certificate/key files, and, via the command line, update the
appropriate fields...  I can do "simple" fields, but I can't figure out how
to do "TEXT" fields, given the fact that the text has line-breaks, etc.

Any help would be greatly appreciated.

Thanks!!!

~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail:jacob.syl...@gmail.com
Web: http://www.jacobsylvia.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Dominique Devienne]

On Mon, Mar 6, 2017 at 11:39 AM, Yuri  wrote:

> On 03/06/2017 01:00, Dominique Devienne wrote:
>
>> This is clearly documented inhttps://www.sqlite.org/c3ref/blob_open.html
>> though, so I'm afraid this is "by design". --DD
>>
>
> Even though this is documented, parts of this limitation don't appear to
> be reasonable. Updating an integer field in the same row shouldn't affect
> the blob field. Rows can be very large and shouldn't move when individual
> fields are updated.


Don't get me wrong, I'm not fond of SQLite blobs as they stand, and I've
said so before on this list.
But modulo the bug you found (mixup of rowids across tables, as mentioned
by Clemens), there's
not much any of us can do, this is in DRH's hands, and from my past threads
here, not much is likely
to happen in this regard.

Changing the way blobs work in SQLite would probably require a format
change (I think),
and that alone is a big enough change that makes it rather unlikely I'm
afraid.

FWIW, there are two main issues with blobs IMHO:
1) they are always stored "in-row". Oracle for example only stores the blob
"index" in-row,
  i.e. which "blob" pages make up the blob. This makes accessing columns
(notably later added columns)
  after the blobs very expensive with large blobs. But makes schema
evolution more difficult than it should be.
  And the advice to putting blobs in separate tables is just a workaround,
requires joins, and makes it more
  difficult to ensure 1-to-1 relationship between the "meta" row and
"bulk-data" row, and associated lifetime management.
2) they can't be updated incrementally generally, except in very limited
cases, and incurring more IO than should be.
  with "dual stage" blobs (or the proverbial level of indirection), if you
want to change a few bytes in the middle, the
  IO cost if updating the blob index and update that one "blob" page that
needs updating. Growing or shrinking can
  similarly be supported transactionally, w/o having to copy the whole blob.

SQLite works well with blobs, as long as they stay small, or they are never
updated.
Outside these use cases, things start the break down and you must either
resort to work-arounds,
and stop using SQLite for those bulk-data. And that's a pity/shame IMHO. My
$0.02 :)  --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Yuri]

On 03/06/2017 01:00, Dominique Devienne wrote:

This is clearly documented inhttps://www.sqlite.org/c3ref/blob_open.html
though, so I'm afraid this is "by design". --DD



Even though this is documented, parts of this limitation don't appear to 
be reasonable. Updating an integer field in the same row shouldn't 
affect the blob field. Rows can be very large and shouldn't move when 
individual fields are updated.



Yuri

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

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

[Dominique Devienne]

On Sat, Mar 4, 2017 at 8:21 AM, Yuri  wrote:

> The write operation using the open sqlite3_blob object fails after some
> other field in the same row is updated.
>

This is clearly documented in https://www.sqlite.org/c3ref/blob_open.html
though, so I'm afraid this is "by design". --DD

If the row that a BLOB handle points to is modified by an UPDATE, DELETE,
> or by ON CONFLICT side-effects then the BLOB handle is marked as "expired".
> This is true if any column of the row is changed, even a column other than
> the one the BLOB handle is open on.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users