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

[Peter da Silva]

What do you mean there have been "just a few threads" in the mailing list?
I can barely keep up with it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Peter da Silva]

> If I change IS NOT FALSE to IS TRUE, the results will be different. I
> assume they should perform in the same way?
> if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should
> also be always true.

"NULL IS NOT FALSE" is true because NULL is not a value therefor it is not 
FALSE, because FALSE is a value.
"NULL IS TRUE" is false because NULL is not a value so it's not TRUE.

You can't compare NULL with anything. All you can do is tell if it "IS NULL" or 
"IS NOT NULL".

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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Peter da Silva]

Change the "(t1.textid = null)" to "(t1.textid IS NULL)". Null has no value, 
you have to check for it explicitly.

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

Re: [sqlite] WITHOUT ROWID tables

[Peter da Silva]

If you have control over the schema you can put a schema version in some
table.

On Sat, Feb 15, 2020, 13:21 J. King  wrote:

> On February 15, 2020 2:14:30 p.m. EST, Thomas Kurz 
> wrote:
> >Wouldn't be something like
> >
> >SELECT sql FROM sqlite_master WHERE tbl_name='?' AND type='table'
> >contains "WITHOUT ROWID"
> >
> >be sufficient?
> >
> >Just being curious.
> >
> >- Original Message -
> >From: sky5w...@gmail.com 
> >To: SQLite mailing list 
> >Sent: Saturday, February 15, 2020, 18:06:47
> >Subject: [sqlite] WITHOUT ROWID tables
> >
> >Ok, not ideal. Still confusing, but I see the difference.
> >For my code, I know the schemas. I guess a SQL builder could offer up
> >query
> >options to the user browsing new databases.
> >
> >On Sat, Feb 15, 2020 at 11:26 AM Simon Slavin 
> >wrote:
> >
> >> On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:
> >
> >> >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
> >> >> index_info('XYZ');".  If you get back one or more rows, then XYZ
> >is a
> >> >> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
> >> >> table.
> >> >>
> >> > Confused...What if I made an index on a ROWID table?
> >> > CREATE INDEX "Z" ON "DOC" ("n_id");
> >
> >> The parameter in index_info() is normally the name of an index.  So
> >if you
> >> create an index "Z" and ask for index_info("Z") you will get
> >information on
> >> that index.
> >
> >> If you create a WITHOUT ROWID table with name 'Y", and ask for
> >> index_info("Y") you will get information on the primary key of that
> >table.
> >
> >> If both exist, you get information about the index.
> >
> >> Simon
> >> ___
> >> 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
>
> CREATE TABLE t(
> c TEXT DEFAULT 'WITHOUT ROWID'
> -- this comment mentions something about WITHOUT ROWID
> );
>
>
> Contrived, no question, but possible.
> --
> J. King
> ___
> 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] What is a "journal"?

[Peter da Silva]

In programming a journal is a file or other data structure containing a
series of change records but can be replayed to reconstruct an operation.

On Thu, 30 Jan 2020, 05:29 Peng Yu,  wrote:

> Hi,
>
> https://www.sqlite.org/lockingv3.html#rollback
>
> "When a process wants to change a database file (and it is not in WAL
> mode), it first records the original unchanged database content in a
> rollback journal. The rollback journal is an ordinary disk file that
> is always located in the same directory or folder as the database file
> and has the same name as the database file with the addition of a
> -journal suffix. The rollback journal also records the initial size of
> the database so that if the database file grows it can be truncated
> back to its original size on a rollback."
>
> I got confused about the above statement about the rollback journal.
> The sentence "The rollback journal ..." appears to be a definition.
> But it is not.
>
> I think that the following can be considered as a definition. Given
> the above sentence appears in a section named "4.0 The Rollback
> Journal". I think it should be changed to a definition to avoid
> confusion.
>
> https://www.sqlite.org/tempfiles.html
>
> "A rollback journal is a temporary file used to implement atomic
> commit and rollback capabilities in SQLite."
>
> Regarding the choice of the word "journal", the only relevant entry
> about "journal" in the Oxford dictionary is this. Is "journal" just a
> fancy way of saying "record file"? Thanks.
>
> """
> 2 a daily record of news and events of a personal nature; a diary.
> • Nautical a logbook.
> • (the Journals) a record of the daily proceedings in the British
> Houses of Parliament.
> • (in bookkeeping) a daily record of business transactions with a
> statement of the accounts to which each is to be debited and credited.
> """
>
> --
> Regards,
> Peng
> ___
> 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] New word to replace "serverless"

[Peter da Silva]

Server-free sounds good. Standalone too. Integrated maybe?

On Mon, Jan 27, 2020, 17:54 Donald Shepherd 
wrote:

> On Tue, 28 Jan 2020 at 10:19 am, Richard Hipp  wrote:
>
> > daemon-less?
> > --
> > D. Richard Hipp
> > d...@sqlite.org
>
>
> In-process? Same concept but defining it by what it is rather than what it
> isn't.
>
> Regards,
> Donald Shepherd.
>
> > 
> ___
> 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] New word to replace "serverless"

[Peter da Silva]

Local?

On Mon, 27 Jan 2020, 16:19 Richard Hipp,  wrote:

> For many years I have described SQLite as being "serverless", as a way
> to distinguish it from the more traditional client/server design of
> RDBMSes.  "Serverless" seemed like the natural term to use, as it
> seems to mean "without a server".
>
> But more recently, "serverless" has become a popular buzz-word that
> means "managed by my hosting provider rather than by me."  Many
> readers have internalized this new marketing-driven meaning for
> "serverless" and are hence confused when they see my claim that
> "SQLite is serverless".
>
> How can I fix this?  What alternative word can I use in place of
> "serverless" to mean "without a server"?
>
> Note that "in-process" and "embedded" are not adequate substitutes for
> "serverless".  An RDBMS might be in-process or embedded but still be
> running a server in a separate thread. In fact, that is how most
> embedded RDBMSes other than SQLite work, if I am not much mistaken.
>
> When I say "serverless" I mean that the application invokes a
> function, that function performs some task on behalf of the
> application, then the function returns, *and that is all*.  No threads
> are left over, running in the background to do housekeeping.  The
> function does send messages to some other thread or process.  The
> function does not have an event loop.  The function does not have its
> own stack. The function (with its subfunctions) does all the work
> itself, using the callers stack, then returns control to the caller.
>
> So what do I call this, if I can no longer use the word "serverless"
> without confusing people?
>
> "no-server"?
> "sans-server"?
> "stackless"?
> "non-client/server"?
>
>
> --
> 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] Best way to store key,value pairs

[Peter da Silva]

Another thing to consider is that RFC-822/1036 (et seq) headers are not
inherently unique and some are repeated, especially since there's software
that treats Usenet and Mail headers interchangeably. Also, debugging may
require being able to see the exact layout of the headers as received. The
safest solution is to add columns for the specific instances of the
specific headers that you need to index, and then store the original
headers unchanged as a blob or a big text column. Converting to json and
back without potentially losing data (even if you don't think you will need
that data) takes some care.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Causal profiling

[Peter da Silva]

Also spaghetti inheritance is a thing.

On Wed, Jan 1, 2020, 19:19 D Burgess  wrote:

> > I’ve spent too much time lately trying to figure out or debug hellacious
> C spaghetti code
>
> And I’ve spent too much time lately trying to figure out or debug
> hellacious C++ spaghetti code
>
> Someone who writes bad C,   will write even worse C++
> ___
> 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] Regarding the whole C89/C90 language compliance debacle ...

[Peter da Silva]

Strictly compliant code strictly doesn't need to do anything at all,
conforming to strict standards instead of what compilers actually do is an
interesting intellectual exercise but is not necessarily useful.

On Sat, Nov 23, 2019, 16:27 Dennis Clarke  wrote:

>
> I may be the only person that does this sort of testing for my own
> reasons and perhaps for sanity checking also. I tend to think that if
> something is written to be compliant with C89/C90 then I should be able
> to run the most strict compliance compiler flags in creation and be
> perfectly happy.
>
> However that is not the case here wwith gcc 9.2.0 on RHEL 7.4 :
>
> ../sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009.compile.log
> /opt/bw/gcc9/bin/gcc  -std=iso9899:1990 -pedantic -Wpedantic
> -pedantic-errors -O0 -m64 -g -march=k8 -mtune=k8
> -Wl,-rpath=/opt/bw/lib,--enable-new-dtags -fno-builtin -malign-double -o
> mksourceid
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c:23:25:
>
> error: ISO C90 does not support 'long long' [-Wlong-long]
> 23 |   typedef unsigned long long int u64;
>| ^~~~
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c:
>
> In function 'KeccakF1600Step':
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c:74:5:
>
> error: use of C99 long long integer constant [-Wlong-long]
> 74 | 0x0001ULL,  0x8082ULL,
>| ^
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c:74:29:
>
> error: use of C99 long long integer constant [-Wlong-long]
> 74 | 0x0001ULL,  0x8082ULL,
>| ^
>
> etc etc
>
> If we look :
>
> boe13$ head -80 tool/mksourceid.c | tail
>u64 C0, C1, C2, C3, C4;
>u64 D0, D1, D2, D3, D4;
>static const u64 RC[] = {
>  0x0001ULL,  0x8082ULL,
>  0x8000808aULL,  0x800080008000ULL,
>  0x808bULL,  0x8001ULL,
>  0x800080008081ULL,  0x80008009ULL,
>  0x008aULL,  0x0088ULL,
>  0x80008009ULL,  0x800aULL,
>  0x8000808bULL,  0x808bULL,
> boe13$
>
> Here I am using some very strict flags and note the -fno-builtin :
>
> boe13$ echo $CFLAGS
> -std=iso9899:1990 -pedantic -Wpedantic -pedantic-errors -O0 -m64
>   -g -march=k8 -mtune=k8 -Wl,-rpath=/opt/bw/lib,--enable-new-dtags
>   -fno-builtin -malign-double
> boe13$
>
> So that clearly isn't going to work here.
>
> So let's try -std=iso9899:1999 and see a different type of failure :
>
> /opt/bw/gcc9/bin/gcc  -std=iso9899:1999 -O0 -m64 -g -pedantic
> -pedantic-errors -Wpedantic -march=k8 -mtune=k8
> -Wl,-rpath=/opt/bw/lib,--enable-new-dtags -fno-builtin -malign-double -o
> mksourceid
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksourceid.c
> tclsh8.7
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mksqlite3h.tcl
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009
>  >sqlite3.h
> /opt/bw/gcc9/bin/gcc  -std=iso9899:1999 -O0 -m64 -g -pedantic
> -pedantic-errors -Wpedantic -march=k8 -mtune=k8
> -Wl,-rpath=/opt/bw/lib,--enable-new-dtags -fno-builtin -malign-double -o
> mkkeywordhash
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/mkkeywordhash.c
> ./mkkeywordhash >keywordhash.h
> /opt/bw/gcc9/bin/gcc  -std=iso9899:1999 -O0 -m64 -g -pedantic
> -pedantic-errors -Wpedantic -march=k8 -mtune=k8
> -Wl,-rpath=/opt/bw/lib,--enable-new-dtags -fno-builtin -malign-double -o
> lemon
>
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c:
>
> In function 'main':
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c:1639:21:
>
> error: ISO C forbids conversion of function pointer to object pointer
> type [-Wpedantic]
>   1639 | {OPT_FSTR, "d", (char*)_d_option, "Output directory.
>   Default '.'"},
>| ^
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c:1640:21:
>
> error: ISO C forbids conversion of function pointer to object pointer
> type [-Wpedantic]
>   1640 | {OPT_FSTR, "D", (char*)handle_D_option, "Define an %ifdef
> macro."},
>| ^
> /opt/bw/build/sqlite_20191121213415_rhel_74_3.10.0-693.el7.x86_64.009/tool/lemon.c:1654:21:
>
> error: ISO C forbids conversion of function pointer to object pointer
> type [-Wpedantic]
>   1654 | {OPT_FSTR, "T", (char*)handle_T_option, "Specify a template
> file."},
>| ^
>
> 

Re: [sqlite] Question about: Adding a record to a table with select failure

[Peter da Silva]

Assuming I'm understanding what the original message was about.

Isn't this what BEGIN; INSERT OR IGNORE; UPDATE; COMMIT is the right tool for?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using application_id

[Peter da Silva]

> If you stick to lower or upper case letters, could encode up to 6 chars in
> the app_id. --DD

The return of RADIX-50.

https://en.wikipedia.org/wiki/DEC_Radix-50

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

Re: [sqlite] database disk image is malformed

[Peter da Silva]

I have been bitten by this a couple of times, so now I'm super-conservative
about how I deal with this.

What I do is have any parent database setup done by having the parent spawn
a child process to do the actual database work, and return any data the
parent needs in the status or (if more than a success status is needed)
through a pipe.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Things you shouldn't assume when you store names

[Peter da Silva]

It just drew a red box and told me to fill out all fields.

Then I enter daSilva.

Worse are the ones that don't say anything and attach the 'da' somewhere
random without telling me.

Then I show up and the nurse wastes time looking me up some other way, and
tells me I'm Peterda Silva.

On Wed, 13 Nov 2019, 15:56 Jose Isaias Cabrera,  wrote:

>
> Peter da Silva, on Wednesday, November 13, 2019 04:37 PM, wrote...
> >
> > My last name has a space in it. It's been less than a month since the
> last
> > time it was rejected by a form. One of my oldest online friends has only
> > one name. Assume nothing, permit everything.
>
> We still need to know that it is your last name and not your first name,
> or 2nd name, or... :-)  By the way, what do you do when the form says,
>
> "Please fix your last name: No spaces allowed!"
>
> Do you just type daSilva?
>
> josé
> ___
> 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] Things you shouldn't assume when you store names

[Peter da Silva]

My last name has a space in it. It's been less than a month since the last
time it was rejected by a form. One of my oldest online friends has only
one name. Assume nothing, permit everything.

On Wed, 13 Nov 2019, 15:23 Swithun Crowe, 
wrote:

> Hello
>
> SS> Those are all excellent examples of why you should /not/ split up a
> SS> name into components.  For artists of all sorts (including the author
> SS> I used) what you care about is their publishing name.  Plus, how do
> SS> you identify the part of a name which you would call 'surname' ?
>
> One needs domain knowledge - knowing that Arouet is the surname of the
> artist known as Voltaire. In my use cases, this is possible (the
> researchers do it, not me). And by encoding one's domain knowledge, one is
> preserving it for future users who may not have it.
>
> Of course people are most interested in the publishing name, so Voltaire
> comes under V. But that isn't the only use that people might have for the
> data. So splitting the names and adding extra logic to display them in
> different ways in different contexts is, I think, the optimal solution.
>
> SS> Why would you want to sort by surname ?  Why is it important that
> SS> "Harris' is shown below "Harrington" ?  Don't substring searches help
> SS> you more than sorted lists ?  Wouldn't you miss the 'Parkes' family
> SS> name from John Wyndham and the 'Ruiz' from Picasso if you were
> SS> searching a sorted list ?
>
> If one had a million names from many different cultures (e.g. a customer
> database), then there may be no need/possibility to maximise reuse of the
> data, and a substring search would be the only way to find the name you
> want. But for a database of 200 French playwrights, a suitably sorted list
> is what users want.
>
> Swithun.
> ___
> 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] Why do these 2 updates give different results?

[Peter da Silva]

>
> This almost seems like a job for a view.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table was deleted on macOS

[Peter da Silva]

Check the local time machine backups? Even if you don't have a TM backup
drive Mojave maintains on-drive backups.

On Tue, 15 Oct 2019, 16:54 Simon Slavin,  wrote:

> Sorry, I have no other ideas.  There is no reason for a table to disappear.
> ___
> 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] rationale for private archives of the SQLite mailing list and suppressed reply-to

[Peter da Silva]

I think you're conflating things. A mail server speaks SMTP for both
inbound and outbound, IMAP/POP/webmail is all part of the user interface
stack... as would be the webforum component in any mailing list/web forum
scheme.

On Mon, 14 Oct 2019, 20:45 Warren Young,  wrote:

> On Oct 14, 2019, at 3:04 PM, Keith Medcalf  wrote:
> >
> > On Monday, 14 October, 2019 14:18, Warren Young 
> wrote:
> >
> >> Fossil Forums allow you to subscribe to email notifications.  From the
> >> reader’s perspective, it’s really very little different from the current
> >> Mailman based scheme.
> >
> > The preceding paragraph is completely at odds with the following
> paragraph, and taken together, they are completely illogical and
> inconsistent.
>
> You’re conflating inbound and outbound paths.  The ability to send email
> implies but does not require the ability to receive email.
>
> ...Which is why they’re often entirely different stacks, speaking
> different protocols!  E.g. SMTP outbound via Postfix, IMAP inbound via
> Dovecot.
>
> > All it needs is to be able to "read and process" RFC-2822 formatted
> message files that are found in an "inbound for me” directory
>
> That’s certainly one way that some email servers work.  The most common
> such scheme is called Maildir.
>
> But there’s probably at least half a dozen other ways it can work: mbox,
> MySQL store, PostgreSQL store, whatever it is that MS Exchange does that’s
> incompatible with the rest of the world…
>
> There are currently four supported outbound email setups in Fossil, and a
> stub for a fifth:
>
> https://fossil-scm.org/home/doc/trunk/www/alerts.md#advanced
>
> Why would inbound be different?
>
> Fossil isn’t in a position where it can require a specific SMTP server.
> It has to run on pretty much every common desktop and server platform.  You
> have to get pretty far down the long tail of OSes before you find one that
> Fossil doesn’t get used on daily by someone.  Therefore, we have to support
> approximately everything.
>
> On top of integrating with all common SMTP stacks, drh long ago stated a
> wish to write his own SMTP server.  (The latter being why Fossil has the
> start of one included!)  This should not surprise you if you’ve followed
> his career. :)
>
> The last time I counted up the pages of RFCs you have to implement to
> speak to a large fraction of the Internet email infrastructure — which was
> one of the times this argument came up on this mailing list! — it was
> something like 500 pages of standardese.  It is not just RFC-2822.  Getting
> to something useful will take time, which comes out of the time budget for
> SQLite, Fossil, etc.
>
> There is the option of writing glue software between Fossil and whatever
> SMTP infrastructure you already have, but no one’s bothered to do that in
> the year or so that Fossil Forums have been in steady use.  To me, that
> speaks more of the desirability of inbound email submission than about its
> inherent difficulty.
> ___
> 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] Attached databases and union view.

[Peter da Silva]

Looks interesting, but if I'm reading the descriptions right I don't think
those would help: my partitioning can be on a hash of a unique text ID or
on a geographic area, and both of those seem to be based on unique rowid
ranges.

On Thu, Aug 22, 2019 at 1:23 PM Keith Medcalf  wrote:

>
> Have you looked at the swarmvtab or unionvtab extension?
>
> https://www.sqlite.org/unionvtab.html
> https://www.sqlite.org/swarmvtab.html
>
> Which can "avoid" having to write your own unions.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Peter da Silva
> >Sent: Thursday, 22 August, 2019 11:57
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Attached databases and union view.
> >
> >Still a bit over 3x slower on queries but that's a 7x performance
> >improvement.
> >
> >On Thu, Aug 22, 2019 at 11:40 AM Peter da Silva 
> >wrote:
> >
> >> Legit. I'll try that.
> >>
> >> On Thu, Aug 22, 2019 at 11:33 AM David Raymond
> >
> >> wrote:
> >>
> >>> I don't know how smart the planner is, but as a thought, would
> >UNION ALL
> >>> make any improvement over just UNION? With just UNION it has to
> >>> de-duplicate all the subquery results whereas with UNION ALL it
> >would be
> >>> free to separate all the various subqueries from each other.
> >>>
> >>> Or do you actually need the UNION to de-dupe stuff?
> >>>
> >>>
> >>> -Original Message-
> >>> From: sqlite-users 
> >On
> >>> Behalf Of Peter da Silva
> >>> Sent: Thursday, August 22, 2019 11:28 AM
> >>> To: SQLite mailing list 
> >>> Subject: [sqlite] Attached databases and union view.
> >>>
> >>> Have an existing application that's pushing the limit on how fast
> >it can
> >>> read data and add it to the database, and thinking of sharding the
> >>> database
> >>> file so I can have multiple writers writing to shards of the main
> >tables.
> >>>
> >>> ATTACH DATABASE 'shard0.sqlite' as shard0;
> >>> ATTACH DATABASE 'shard1.sqlite' as shard1;
> >>> ...
> >>>
> >>> CREATE TEMPORARY VIEW sharded_main_table AS
> >>> SELECT col,col,col...,all_columns_basically FROM
> >shard0.main_table
> >>> UNION
> >>> SELECT col,col,col...,all_columns_basically FROM
> >shard1.main_table
> >>> ...;
> >>>
> >>> What's the best way to construct this union view so the query
> >optimizer
> >>> won't be horribly confused? If I run something like "SELECT
> >count(*) FROM
> >>> sharded_main_table WHERE ident LIKE 'pattern';" it's about 20
> >times slower
> >>> than the same query against the original main_table. Running the
> >query
> >>> against each shardN.main_table it's actually faster (in total time
> >for all
> >>> queries in sequence) than running it against the original table.
> >>>
> >>> Is there a better way to construct the view, or am I going to get
> >best
> >>> query performance by making my code shard-aware?
> >>>
> >>> All the original indexes on main_table have been copied to the
> >shard
> >>> databases.
> >>> ___
> >>> 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
>
>
>
> ___
> 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] Attached databases and union view.

[Peter da Silva]

Still a bit over 3x slower on queries but that's a 7x performance
improvement.

On Thu, Aug 22, 2019 at 11:40 AM Peter da Silva  wrote:

> Legit. I'll try that.
>
> On Thu, Aug 22, 2019 at 11:33 AM David Raymond 
> wrote:
>
>> I don't know how smart the planner is, but as a thought, would UNION ALL
>> make any improvement over just UNION? With just UNION it has to
>> de-duplicate all the subquery results whereas with UNION ALL it would be
>> free to separate all the various subqueries from each other.
>>
>> Or do you actually need the UNION to de-dupe stuff?
>>
>>
>> -----Original Message-
>> From: sqlite-users  On
>> Behalf Of Peter da Silva
>> Sent: Thursday, August 22, 2019 11:28 AM
>> To: SQLite mailing list 
>> Subject: [sqlite] Attached databases and union view.
>>
>> Have an existing application that's pushing the limit on how fast it can
>> read data and add it to the database, and thinking of sharding the
>> database
>> file so I can have multiple writers writing to shards of the main tables.
>>
>> ATTACH DATABASE 'shard0.sqlite' as shard0;
>> ATTACH DATABASE 'shard1.sqlite' as shard1;
>> ...
>>
>> CREATE TEMPORARY VIEW sharded_main_table AS
>> SELECT col,col,col...,all_columns_basically FROM shard0.main_table
>> UNION
>> SELECT col,col,col...,all_columns_basically FROM shard1.main_table
>> ...;
>>
>> What's the best way to construct this union view so the query optimizer
>> won't be horribly confused? If I run something like "SELECT count(*) FROM
>> sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower
>> than the same query against the original main_table. Running the query
>> against each shardN.main_table it's actually faster (in total time for all
>> queries in sequence) than running it against the original table.
>>
>> Is there a better way to construct the view, or am I going to get best
>> query performance by making my code shard-aware?
>>
>> All the original indexes on main_table have been copied to the shard
>> databases.
>> ___
>> 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] Attached databases and union view.

[Peter da Silva]

Originally Tcl/native Tcl binding, now a C++ extension calling the
C-binding that's a Tcl extension itself.

On Thu, Aug 22, 2019 at 11:17 AM test user 
wrote:

> What language/binding library are you using?
>
> On Thu, 22 Aug 2019 at 16:45, Peter da Silva  wrote:
>
> > Database is on tmpfs and periodically snapshotted to SSD. There are
> > bottlenecks upstream of sqlite that we can see in traces.
> >
> > On Thu, Aug 22, 2019 at 10:36 AM Warren Young 
> wrote:
> >
> > > On Aug 22, 2019, at 9:27 AM, Peter da Silva  wrote:
> > > >
> > > > Have an existing application that's pushing the limit
> > >
> > > If the limit is in hardware, shards won’t help.
> > >
> > > For example, a SQLite DB on a 7200 RPM spinning disk is limited to
> about
> > > 60 transactions per second under the stock SQLite fsync logic, since
> each
> > > takes 2 revolutions to commit.  (One to write to the journal, and one
> to
> > > commit the journal entry.)  Writes to multiple shards only get to
> share a
> > > platter rotation if there is no seek between writes.
> > >
> > > The limits are higher for SSDs, but there’s still a low limit on the
> > > number of parallel writes.
> > > ___
> > > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attached databases and union view.

[Peter da Silva]

Legit. I'll try that.

On Thu, Aug 22, 2019 at 11:33 AM David Raymond 
wrote:

> I don't know how smart the planner is, but as a thought, would UNION ALL
> make any improvement over just UNION? With just UNION it has to
> de-duplicate all the subquery results whereas with UNION ALL it would be
> free to separate all the various subqueries from each other.
>
> Or do you actually need the UNION to de-dupe stuff?
>
>
> -Original Message-
> From: sqlite-users  On
> Behalf Of Peter da Silva
> Sent: Thursday, August 22, 2019 11:28 AM
> To: SQLite mailing list 
> Subject: [sqlite] Attached databases and union view.
>
> Have an existing application that's pushing the limit on how fast it can
> read data and add it to the database, and thinking of sharding the database
> file so I can have multiple writers writing to shards of the main tables.
>
> ATTACH DATABASE 'shard0.sqlite' as shard0;
> ATTACH DATABASE 'shard1.sqlite' as shard1;
> ...
>
> CREATE TEMPORARY VIEW sharded_main_table AS
> SELECT col,col,col...,all_columns_basically FROM shard0.main_table
> UNION
> SELECT col,col,col...,all_columns_basically FROM shard1.main_table
> ...;
>
> What's the best way to construct this union view so the query optimizer
> won't be horribly confused? If I run something like "SELECT count(*) FROM
> sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower
> than the same query against the original main_table. Running the query
> against each shardN.main_table it's actually faster (in total time for all
> queries in sequence) than running it against the original table.
>
> Is there a better way to construct the view, or am I going to get best
> query performance by making my code shard-aware?
>
> All the original indexes on main_table have been copied to the shard
> databases.
> ___
> 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] Attached databases and union view.

[Peter da Silva]

Database is on tmpfs and periodically snapshotted to SSD. There are
bottlenecks upstream of sqlite that we can see in traces.

On Thu, Aug 22, 2019 at 10:36 AM Warren Young  wrote:

> On Aug 22, 2019, at 9:27 AM, Peter da Silva  wrote:
> >
> > Have an existing application that's pushing the limit
>
> If the limit is in hardware, shards won’t help.
>
> For example, a SQLite DB on a 7200 RPM spinning disk is limited to about
> 60 transactions per second under the stock SQLite fsync logic, since each
> takes 2 revolutions to commit.  (One to write to the journal, and one to
> commit the journal entry.)  Writes to multiple shards only get to share a
> platter rotation if there is no seek between writes.
>
> The limits are higher for SSDs, but there’s still a low limit on the
> number of parallel writes.
> ___
> 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] Attached databases and union view.

[Peter da Silva]

Have an existing application that's pushing the limit on how fast it can
read data and add it to the database, and thinking of sharding the database
file so I can have multiple writers writing to shards of the main tables.

ATTACH DATABASE 'shard0.sqlite' as shard0;
ATTACH DATABASE 'shard1.sqlite' as shard1;
...

CREATE TEMPORARY VIEW sharded_main_table AS
SELECT col,col,col...,all_columns_basically FROM shard0.main_table
UNION
SELECT col,col,col...,all_columns_basically FROM shard1.main_table
...;

What's the best way to construct this union view so the query optimizer
won't be horribly confused? If I run something like "SELECT count(*) FROM
sharded_main_table WHERE ident LIKE 'pattern';" it's about 20 times slower
than the same query against the original main_table. Running the query
against each shardN.main_table it's actually faster (in total time for all
queries in sequence) than running it against the original table.

Is there a better way to construct the view, or am I going to get best
query performance by making my code shard-aware?

All the original indexes on main_table have been copied to the shard
databases.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

[Peter da Silva]

If the datr/time is stored internally as utc iso8601 text then it will
remain compatible with old versions and can implement whatever new behavior
is needed on new versions. The bigger question is 'what new behavior'? The
only nee behavior seems to be 'let this third party package see it as a
date', which it should be able to figure out by looking at the schema.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unreachable Breaks with ICC on Windows

[Peter da Silva]

I would argue that an unreachable break should not be a warning, since
leaving out the break is just asking for some beggar to come in and make it
reachable again and summon the fallthrough fairy without noticing it. :)

On Sat, Jul 13, 2019 at 9:32 AM André Borchert <0xa...@gmail.com> wrote:

> Hello,
>
> ICC reports two unreachable "break;" statements as warnings:
>
> SQLite 3.29
>
> Windows 2019
> Visual Studio 16.2.0 Preview 3.0
> Intel Parallel Studio XE 2019 Update 4
>
>
> 1>-- Rebuild All started: Project: SQLite, Configuration: Release x64
> --
> 1>sqlite3.c
> 1>D:\Dropbox\Chaos\SQLite Test\SQLite\sqlite3.c(87093): message #111:
> statement is unreachable
> 1>break;
> 1>^
> 1>
> 1>D:\Dropbox\Chaos\SQLite Test\SQLite\sqlite3.c(87850): message #111:
> statement is unreachable
> 1>break;
> 1>^
> 1>
> ___
> 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] How to set access permissions to protect a database file?

[Peter da Silva]

I don't believe you are providing any protection against editing the file,
just deleting.

On Sun, Jun 9, 2019, 4:38 PM Markos  wrote:

> Many thanks to Luuk, Adrian, Graham, James, Simon, Richard and Peter,
>
> To guarantee "some" protection to the files containing the database I
> decided to use the following strategy:
>
> I created, as root, the directory /home/reading_room
>
> And activated the "sticky bit" of the reading_room directory with the
> command:
>
> chmod +t /home/reading_room/
>
> And transferred, the files to the new directory with the following
> access permissions:
>
> reading_room.tcl  rwxr--r-x  (owner markos)
>
> reading_room.db rw-r--rw- (owner markos)
>
>
> This way other users can run the reading_room.tcl program but can't  but
> not edit it.
>
> And can't delete the files (.tcl or .db)
>
> Trying to protect against Murphy, but not Machiavelli. (As Richard said.)
>
> Thank you,
> Markos
>
> Em 26-05-2019 23:33, Adrian Ho escreveu:
> > On 27/5/19 12:43 AM, Luuk wrote:
> >> On 26-5-2019 13:52, Adrian Ho wrote:
> >>> On 26/5/19 7:49 AM, Markos wrote:
>  I made a program (reading_room.tcl), with Sqlite running on Debian 9,
>  to control the books of a reading room.
> 
>  I implemented an authentication system for common users and
>  administrator users in the reading_room.tcl program.
> 
>  Now I want that any user logged in the Linux be able to run the
>  program reading_room.tcl, which will access the database (books.db)
> 
>  But I want to protect the file books.db so that only the the program
>  reading_room.tcl can access the books.db file (to read or write). But
>  that no user could delete or write to the file books.db (only the
>  program reading_room.tcl)
> >>> The standard Unix permissions/ACLs architecture doesn't support this
> use
> >>> case directly.
> >> Can you give some more information on this, because it seems to work
> >> as i excpect it to:
> >>
> >> Database is 'owned' by user 'luuk', trying to access via 'luuk2', both
> >> users are in the group 'users':
> >>
> >> luuk2@opensuse1:/home/luuk/temp> whoami
> >> luuk2
> >> luuk2@opensuse1:/home/luuk/temp> ls -l test.sqlite
> >> -r--r--r-- 1 luuk users 8192 May 26 18:34 test.sqlite
> >> luuk2@opensuse1:/home/luuk/temp> sqlite3 test.sqlite
> >> SQLite version 3.28.0 2019-04-16 19:49:53
> >> Enter ".help" for usage hints.
> >> sqlite> select * from test;
> >> 1
> >> 2
> >> sqlite> insert into test values(3);
> >> Error: attempt to write a readonly database
> >> sqlite> .q
> >> luuk2@opensuse1:/home/luuk/temp>
> >>
> > The OP wants *all users* to be able to update (write) the DB via the Tcl
> > script reading_room.tcl, but *not* by (say) running the SQLite shell or
> > something else. In your setup, as long as a specific user has write
> > permissions, *every program* the user runs can write to the DB.
> > ___
> > 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] How to set access permissions to protect a database file?

[Peter da Silva]

This is what the UNIX group ID and the set-group-id capability is for.

You make the files readable (or read and write) by a group (mode 640 or
660).

You make the program that controls access to the files set-group-id to that
group.

You may need a small "C" wrapper program to run the script set-group-id
depending on the version of UNIX and security settings. Allowing scripts to
run set-group-id has at times been violently controversial, and I have not
bothered to track teh state of teh art.

On Mon, May 27, 2019 at 8:37 AM Richard Damon 
wrote:

> On 5/27/19 9:16 AM, Simon Slavin wrote:
> > On 27 May 2019, at 3:33am, Adrian Ho  wrote:
> >
> >> The OP wants *all users* to be able to update (write) the DB via the
> Tcl script reading_room.tcl, but *not* by (say) running the SQLite shell or
> something else. In your setup, as long as a specific user has write
> permissions, *every program* the user runs can write to the DB.
> > Some work in this thread has been because the file is a SQLite
> database.  But it's not really a SQLite question.  It's more about the
> access/permissions model of Debian 9.  What OP wants can be reduced to a
> simpler situation:
> >
> > "I have a text file.  It's on a computer running Debian 9.  I want to
> make sure that this text file can be read/written by multiple people, but
> that it can be read/written only using this program I wrote."
> >
> > I've never used Debian so I can't solve the problem.  But from what
> little I remember of Linux, one solution is to create a special account for
> that one operation, and set up that account in a non-standard way.
>
> Actually, It can be an SQLite question, as another way to solve the base
> problem is to do something to the database so that only 'authorized'
> applications can access/modify it. One way to do that is to use the SEE
> extension and encrypt the database. If you do that then you no longer
> have the equivalent of a 'text file', so unauthorized applications can't
> access the file.
>
> It isn't perfect protection, because someone still will have the ability
> to delete/overwrite the file, to protect from that seems to need the
> protection method you describe, but if you are only trying to protect
> against Murphy, and not Machiavelli (as the saying goes) it may be a
> viable, and portable, solution.
>
> --
> Richard Damon
>
> ___
> 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] Have SQLite handle values of my own type

[Peter da Silva]

Windows TCP/IP userland used to be a port of the BSD networking tools (I
think via Lachman Associates) so that's not surprising. Allegedly they
reimplemented it at some point.

On Thu, May 23, 2019 at 3:57 PM Keith Medcalf  wrote:

> On Thursday, 23 May, 2019 08:35, Dominique Devienne 
> wrote:
>
> >On Thu, May 23, 2019 at 3:50 PM Jose Isaias Cabrera
> > wrote:
> >
> >> I have been working network for a long time, and I have never seen
> >> any application that takes "zeroed left-filled" IP addresses.  Just
> >> sharing...
> >> Thanks.
>
> > Works for me with a .001 at least, as shown below. But that wasn't
> > really the point I was making, FWIW. --DD
>
> Well, actually, that depends on how the code in the network stack converts
> from presentation format to network format.  Windows 10 1903 (at least)
> treats each component as an input number which means that a number that
> starts with a 0 is an octal number ... (ie, the BSD way)
>
> >ping 172.217.14.227
>
> Pinging 172.217.14.227 with 32 bytes of data:
> Reply from 172.217.14.227: bytes=32 time=26ms TTL=56
> Reply from 172.217.14.227: bytes=32 time=26ms TTL=56
>
> >ping 172.217.014.227
>
> Pinging 172.217.12.227 with 32 bytes of data:
> Reply from 172.217.12.227: bytes=32 time=73ms TTL=45
> Reply from 172.217.12.227: bytes=32 time=72ms TTL=45
>
> Other parsers may see the input as invalid:
>
> sqlite> select ipblobaddr(ipaddrblob('172.217.014.227'));
>
> sqlite> select ipblobaddr(ipaddrblob('172.217.14.227'));
> 172.217.14.227
>
> So really, what you get depends on who wrote the code that is doing the
> translation.  Since the code that I used to implement ipblobaddr and
> ipaddrblob is taken from the ISC DNS Bind code based on code written by
> Paul Vixie in 1996, it is quite possible that many things will see leading
> 0's as invalid input.  Some things (for example Cisco IOS) may also choose
> to just ignore the extra 0's.  Other things may take it as an indicator
> that the value is base-8 rather than base-10.
>
> See
> https://tools.ietf.org/html/draft-main-ipaddr-text-rep-00
> https://en.wikipedia.org/wiki/Dot-decimal_notation
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> 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] althttpd.c check-in: efdc1b8e66

[Peter da Silva]

> A middle ground is Docker for Windows.  I hesitate to speculate on its speed 
> compared to the other three, but I think there’s reason to hope it could be a 
> good option.

I would expect that running a Linux image inside Microsoft's hypervisor 
environment and running a Linux image in Docker's or other third party's 
hypervisor environment would provide roughly similar performance... the 
technology is well understood and mature. The main reason to pick one over 
another is the VM management environment you prefer.

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

Re: [sqlite] Use cases for sqlite3_value_frombind()?

[Peter da Silva]

I don't think I would use this as a security indicator, whether it's a
bound parameter or a constant in the query string doesn't tell you if it's
from a trusted source or not. This is more an indicator that this value is
likely to change in subsequent queries.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Peter da Silva]

Oy. I've worked on safety-critical systems with hard real-time constraints
too. For the most part they didn't *have* file systems or the file systems
were basically read-only in production. Sticking a relational database any
closer than the SCADA monitoring node would not be a thing that happens,
let alone using a compressing file system to hold that database. But
there's a whole spectrum of embedded systems between that and arcade games.

On Wed, Apr 10, 2019 at 5:17 PM Keith Medcalf  wrote:

>
> On Wednesday, 10 April, 2019 14:21, Peter da Silva 
> wrote:
>
> >On Wed, Apr 10, 2019 at 3:12 PM Keith Medcalf 
> wrote:
>
> >> Why would anyone fart about with added complication and the
> >> concomittant increased unreliability when storage is so damn cheap?
>
> >Embedded systems and mobile devices.
>
> You mean "play things", for the most part.
>
> By their very definitions "play things" do not require reliability and as
> such the added complication and inherent increase in unreliability due to
> that increased complexity is of no real effect.
>
> I am used to dealing with "important shit".  That means that if it stops
> working, even for a minute, it might entail costs of millions of dollars
> and perhaps a few deaths or cripplings as well.
>
> There is a very great difference between the "streaming media crap" not
> working for a bit and you have to (heavens forbid) read a book, or the mail
> server going down for a day or two, which are really nothing more than
> minor inconveniences by comparison.  The streaming box screws up?  Throw it
> out and buy another.  In the "play things" world adding complexity to
> increase unreliability and save a few pennies is often a reasonable
> trade-off.  After all, nothing of any real significance will be lost -- it
> is merely a bit of inconvenience to suffer through with no real lasting
> impact.
>
> On the other hand if the consequence of failure is certain death of 10
> people, then I would much rather be spending more money on reliable
> hardware to maintain the designed level of reliability than to save a few
> shekels by tossing "compression" into the mix thereby reducing reliability
> and increasing the probability (through an increase in unpredictable
> failure modes) of those 10 people dying.  I think if you were one of those
> 10 people with your life at risk you would see things the same way.
>
> >But of course those probably don't apply here. :)
>
> It is all a matter of perspective.  Lets imaging that the problem with the
> 747MAX was not that the new control system was designed by an idiot and
> that insufficient training on the detection and correction of the "we know
> this is going to be a problem" so intruduced were not the issue.  Lets say
> instead that the files were merely a bit too big for the hard drives they
> decided to use.  They have the option of (a) spending an additional $100
> and getting larger storage and not changing the failure scenario's at all;
> or, (b) not spending any money and instead adding yet another layer of
> software to perform "compression" instead (thus changing the failure
> scenario's because now you have a whole whack of new failure modes).
>
> The "Play Things" people consider that the crash of the airliner and the
> loss of equipment and all life aboard is merely an "inconvenience" and will
> choose option (b) because hey, the software always works, right?  The
> "Important Shit" people will consider that the *possible* increase in risk
> of loss of equipment and life due to the addition of yet more complexity
> cannot be tolerated and will chose option (a) because it is far more cost
> effective than the analysis that will be required to *prove* option (a) has
> not increased the risk.
>
> I simply happen to fall into the "Important Shit" category of people by
> default.  I am somewhat risk-adverse as they say.  If the risk associated
> with a thing is significant, then spend as much as required to reduce that
> risk to an acceptable level.  If the risk associated with a thing is
> negligible, then get the cheapest shit available and when it "breaks" throw
> it out and get another.
>
> This does not mean that the "Play Things" outlook is incorrect.  It merely
> depends on the garden in which you are playing and in to which category the
> product falls.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> 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] compressed sqlite3 database file?

[Peter da Silva]

On Wed, Apr 10, 2019 at 3:12 PM Keith Medcalf  wrote:

> Why would anyone fart about with added complication and the concomittant
> increased unreliability when storage is so damn cheap?
>

Embedded systems and mobile devices.

But of course those probably don't apply here. :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compressed sqlite3 database file?

[Peter da Silva]

A database trades off space for performance and functionality. It is
expected that a database with indexes that you can randomly access is going
to take more space than the raw data, let alone a compressed version of the
raw data.

On Wed, Apr 10, 2019 at 12:39 AM Peng Yu  wrote:

> I have some TSV table in .gz format of only 278MB. But the
> corresponding sqlite3 database exceeds 1.58GB (without any index). Is
> there a way to make the database file of a size comparable (at least
> not over 5 times) to the original TSV table in the .gz file? Thanks.
>
> --
> Regards,
> Peng
> ___
> 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 with type mismatch in Tcl interface

[Peter da Silva]

One nasty thing you might be able to do in the interim is to shimmer the
object to the type you want by doing something like [set x [expr {double
$x}]] to make it double.

On Wed, Feb 27, 2019 at 9:31 AM Richard Hipp  wrote:

> On 2/27/19, Jean-Baptiste Gardette  wrote:
> > have you any explanation why the typof() output of
> > "SELECT DerefDict_SQL('A'), typeof(DerefDict_SQL('A'))"
> > gives "text" in Sequence A and "real" in Sequence B ?
> >
>
> No.
>
> The code that determines the return type of a TCL UDF is here:
> https://www.sqlite.org/src/artifact/6b19e7562195aaf8?ln=993-1018
>
> As you can see, the interface is looking at the Tcl_Obj.typePtr value
> to try to guess an appropriate return type.  This is considered bad
> form in TCL since TCL makes no guarantees about the value of typePtr
> for a Tcl_Obj.  But the technique has worked well for nearly two
> decades.
>
> What if we were to add a new option to the "db function" method of the
> TCL interface that let you specify the return type of the
> TCL-implemented UDF?  If the optional argument is omitted, then the
> current behavior of trying to guess the return type based on typePtr
> is used. But if the "-returntype TYPE" argument is present, it
> determines which return type to use regardless of the value in the
> typePtr.  Would that help your situation?
>
> Note that my tests were run after compiling both TCL and SQLite from
> sources.  There is no telling what non-standard changes may have been
> added by people who assembled your pre-compiled binaries.
>
>
>
> --
> 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] How to refer to `this` table?

[Peter da Silva]

As an aside, this schema seems to be violating 
https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/
 particularly rule 21.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Peter da Silva]

I am pretty sure that the code is not legal C because it's using the return
value of a void function, as well as returning a value from a void
function. Compilers that "do what I mean" and accept it are in error. It's
certainly possible that some obscure clause in some C standard blesses it
but I can't imagine why they would.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite slow when lots of tables

[Peter da Silva]

On Tue, Jan 29, 2019, 7:46 PM Igor Korot  You can install mySQL/MariaDB for free and use it for your needs.
> I believe that if you pay to Oracle/MariaDB Foundation, it will be
> just for support. (I may be wrong though).
>

Or better, PostgreSQL. We have a system that uses SQLite as an optional
cache for PostgreSQL and takes advantage of the similarity of their SQL
variants to get the best of both worlds. I modified the Pgtcl library to
even get the variable binding for PostgreSQL to match SQLite syntax and
semantics.

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

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

[Peter da Silva]

The sensible permissions in this case would probably be rw-rw-r-- with the
same group as the service and owned by the service group. That is how group
permissions were designed to work.


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

Re: [sqlite] sqlite-src-3260000 tests throw Error: couldn't fork child process: not enough memory

[Peter da Silva]

Sounds like something is using fork when it should be using vfork?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Peter da Silva]

That wasn't "endian" argument, this is an arithmetic operation question,
avoiding extra operations in the common case of small (mag < 2^56)
operands. Since I posted that I've figured out some other optimizations
that work better the way they laid it out, and it makes more sense now.



On Thu, Jan 3, 2019 at 5:54 AM Gary R. Schmidt  wrote:

> On 03/01/2019 22:22, Peter da Silva wrote:
> > Why is the exponent in the low bits, since it forces unnecessary shifts
> for
> > integer operations?
> >
> That's easy, because the high bits are closer to the barrel shifter, so
> it takes less time for the electron to get there![1][2]
>
> Cheers,
> GaryB-)
>
> 1 - Oh ghod, bit-order arguments, worse than the shell wars and the
> editor wars combined!!!
>
> 2 - Sarcasm intended, for those who may not be certain.
> ___
> 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] Question about floating point

[Peter da Silva]

Why is the exponent in the low bits, since it forces unnecessary shifts for
integer operations?

On Thu., 3 Jan. 2019, 03:34 Pavlos Christoforou  Happy new year all,
>
> Have not followed the full thread, in case it has not been mentioned
> already:
>
> http://dec64.com/
>
>
> Cheers
>
>
> On Tue, 18 Dec 2018 at 02:42, Keith Medcalf  wrote:
>
> > >This thread is getting out of hand. Firstly there is no such binary
> > >representation ( in this universe ) for a trivial decimal number such
> > >as one tenth ( 0.10 ) and really folks should refer to the text book
> > >recently published ( 2nd Edition actually ) where all this is covered
> > >:
> > > Handbook of Floating-Point Arithmetic
> > > Authors: Muller, J.-M., Brunie, N., de Dinechin, F.,
> > >  Jeannerod, C.-P., Joldes, M., Lefèvre, V.,
> > >  Melquiond, G., Revol, N., Torres, S.
> > >
> > > This handbook is a definitive guide to the effective use of
> > > modern floating-point arithmetic, which has considerably
> > > evolved, from the frequently inconsistent floating-point number
> > > systems of early computing to the recent IEEE 754-2008 standard.
> >
> >
> >
> https://doc.lagout.org/science/0_Computer%20Science/3_Theory/Handbook%20of%20Floating%20Point%20Arithmetic.pdf
> >
> > While it is true there is no exact representation of 1/10th in binary
> > floating point, at double precision the epsilon is 1.3877787807814457e-17
> > which means that for all intents and purposes 1/10th is exact to 16.9
> > decimal places.  Which is pretty damn good for a format that is only
> > purported to be accurate to 15 decimal digits.
> >
> > ---
> > The fact that there's a Highway to Hell but only a Stairway to Heaven
> says
> > a lot about anticipated traffic volume.
> >
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> Pavlos Christoforou
>
> Point Nine Limited
> Mobile: +357 99 160960
>
> pavlos.christofo...@p9ft.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] Claimed vulnerability in SQLite: Info or Intox?

[Peter da Silva]

Programs that were written defensively from the start are more likely to
survive attack. Programs that have successfully been exposed to attack can
be treated as more secure. Sqlite is explicitly not designed to be secure
against untrusted input or corrupt .

This is OK. It's not a program that would be expected to be designed to be
secure against these kinds of attacks. It just means that you don't expose
it to untrusted input, like it's a shell or compiler or something.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Peter da Silva]

Fuzz testing would be extremely unlikely to have caught the original
attack. Nor would fuzz testing on input be likely to hit all corrupt
database attacks. Fuzz testing using fuzzed corrupted databases might.

On Thu., 20 Dec. 2018, 11:26 Jens Alfke 
>
> > On Dec 19, 2018, at 4:03 PM, Peter da Silva  wrote:
> >
> > sqlite is not immune to wandering through bad pointers, because code
> > coverage tests don't test for malicious data..
>
> Fuzz testing does, though [implicitly].
>
> https://www.sqlite.org/testing.html#sql_fuzz_using_the_american_fuzzy_lop_fuzzer
>
> —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] Claimed vulnerability in SQLite: Info or Intox?

[Peter da Silva]

sqlite is not immune to wandering through bad pointers, because code
coverage tests don't test for malicious data... I found a null pointer
crash in sqlite earlier this year. I could see Mallory crafting a database
that had carefully corrupted structures in it that smashed the stack.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Peter da Silva]

On Tue, Dec 18, 2018 at 3:49 PM Nathan Green  wrote:

> Except the problem isn't just in Chrome. Apparently, any system that allows
> SQL injection is vulnerable.
>

That's kind of a tautology isn't it? Isn't there some kind of Godwin's Law
variant for XKCD 327?

I notice that the 12 points on https://www.sqlite.org/appfileformat.html
don't include "secure".

I mean, sure, we used to distribute software on Usenet as shell scripts
(look up "shar archive") but it's not 1984 any more.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Peter da Silva]

Javascript was designed from the start to safely execute malicious code.
That doesn't mean it is safe, it just means it might be. There have been
all kinds of javascript-based exploits, after all.

But an interpreter that was not originally designed to be safe in the face
of malicious code? I can't understand the confusion in the mind that would
lead one to expect miracles of it. This is not a criticism of sqlite, by
any means. Safe languages are rare.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Peter da Silva]

I have to say I'm pretty boggled that Chrome allows hostile users to feed
code directly into an SQL interpreter that wasn't written from the ground
up to be secure. Secure interpreters are *hard* even when you're designing
them from scratch (see also, the whole history of web-based
vulnerabilities). That seems to be dancing with the screwup fairy to me.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Question] Non-EXCLUSIVE modes in dot-file locking strategy

[Peter da Silva]

The only way I can think of to have a large counter in a lock is to lock
access to a file containing a counter and I don't know if not-a-file-system
semantics support that.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[Peter da Silva]

Apple uses Sqlite in a number of applications, including Apple Mail, so
they have to have some kind of accommodation for saving sqlite databases.

The Time Machine patent does not describe using file system snapshots:


*"An algorithm or other monitoring can be used to detect changes that occur
during the backup operation in order to maintain consistency between
related data in the backup. The back up can be performed again for related
data that was modified during prior backup operation. *

*"In general, in one aspect, a method is provided. A backup operation of
data including a plurality of related items is initiated. Modifications to
one or more items of the plurality of related items are monitored for
during the backup operation. The backup operation is completed. If a
modification occurred to one or more items, a second backup operation is
performed for the modified items."*

This does not seem to authoritatively state that multiple files will be
backed up consistently.

On Wed, Dec 12, 2018 at 9:06 AM Keith Medcalf  wrote:

>
> I know nothing about "Time Machine", but does it copy the entire
> filesystem in (at least) "crash consistent" state?
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Daniel Alm
> >Sent: Tuesday, 11 December, 2018 05:02
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: [sqlite] Mac: Users receive "database disk image is
> >malformed" errors after restoring database from Time Machine backup
> >
> >Hi,
> >
> >For the past half year we’ve been receiving reports from users who
> >had restored their SQLite-based databases from a Time Machine backup.
> >Afterwards, they would receive "database disk image is malformed”
> >errors. The app also backs up the user’s data “manually” to a ZIP
> >file every week; those backups seem to be working fine. We also
> >haven’t received reports from other backup tools causing issues. I
> >have also suspected a bug in Time Machine, but it is striking that
> >the issues did seem to start occurring after an update to the app
> >(luckily, in fact, with the same update that also introduced the
> >“manual” backups).
> >
> >Changes that we made to our setup in the update that coincided with
> >the errors occurring:
> >- Upgraded SQLite from 3.21 to 3.24 (we have since reverted to 3.23.1
> >in another update; no improvement).
> >- Used memory mapping for read accesses via “PRAGMA mmap_size =
> >1073741824;” (we have since reverted to “PRAGMA mmap_size = 0;” after
> >reading http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-
> >and-PRAGMA-fullfsync-on-macOS-td95366.html
> > >PRAGMA-fullfsync-on-macOS-td95366.html>; no improvement).
> >- Using a secondary database via [ATTACH
> >DATABASE](https://www.sqlite.org/lang_attach.html
> >) (although this also seems
> >to occur for users without such a database).
> >
> >At this point, I am at a loss, especially given that SQLite should be
> >fairly robust against database corruption. While our app is running
> >in the background all the time, it is not very write-heavy (~ one
> >transaction per minute taking just a few milliseconds). Also, the app
> >had been running fine before the update for a long time without any
> >reports of this issue. I might be doing something wrong or have
> >changed anything else, but I don’t know what; if you have any ideas,
> >let me know.
> >
> >Any suggestions on what could be the culprit or what else I could try
> >besides downgrading all the way to SQLite 3.21 would be appreciated.
> >
> >Thanks,
> >Daniel Alm
> >
> >P.S.: Our database currently uses the following PRAGMAs:
> >
> >PRAGMA mmap_size = 0;
> >PRAGMA page_size = 4096;
> >PRAGMA cache_size = -10240;
> >PRAGMA foreign_keys = ON;
> >PRAGMA journal_size_limit = 8388608;
> >PRAGMA checkpoint_fullfsync = 1;
> >PRAGMA wal_autocheckpoint = 2048;
> >PRAGMA journal_mode = WAL;
> >
> >Happy to provide any more details as needed.
> >___
> >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] Windows app to read SQLite DB, and launch web browser when dbl-clicking a record?

[Peter da Silva]

On 2018-10-19, at 09:02, Winfried  wrote:
> Before I build a GUI, is there a Windows SQLite database manager that can do
> this?
> 
> I tried DB Browser for SQLite, SQLitespeed, and SQLiteStudio, but none seems
> to support this feature.

SQLite's closest thing to a native scripting language is Tcl, with a Tk 
graphical toolkit. If you write your app in Tcl/Tk it will run on Windows, Mac, 
and UNIX.

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

Re: [sqlite] forming sqlite3_statements using stored sql

[Peter da Silva]

I don't care for the sql variable syntax they're using, since it appears to
conflict with the native Tcl bindings for sqlite3.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] curious discovery about geopoly module

[Peter da Silva]

On 2018-11-01, at 02:51, Graham Hardman  wrote:
> I am pleased about this, although still puzzled by the fact that the windows 
> LoadLibrary call returned a non null pointer and windows help file implies 
> that it is ok for multiple copies of same name libraries to be loaded 
> together.

This has been a problem in Windows for years. Microsoft themselves used to 
include version numbers in library base names (eg, MSVCRT40.DLL) and other 
vendors use similar tricks to keep "DLL hell" at bay. I suspect that your 
solution of using a different library name is actually best practice. :(
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Displaying row count

[Peter da Silva]

If you're going to change the sqlite3 command shell, add a ".count" and
maybe ".changes" display option.

On Wed, Oct 31, 2018 at 11:23 AM Don V Nielsen 
wrote:

> I really enjoy using JetBrains DataGrip. It connects to everything and has
> great intellisense, find and replace tools, sql templates, all the goodies
> a big IDE brings to the table.
>
> On Wed, Oct 31, 2018 at 11:05 AM Dominique Devienne 
> wrote:
>
> > On Wed, Oct 31, 2018 at 3:55 PM Clemens Ladisch 
> > wrote:
> >
> > > David Fletcher wrote:> Hi all,
> > > > Is there a mode in the sqlite shell, or some fancy extension, that
> will
> > > display a row
> > > > number when outputting results?
> > >
> > > No.  You'd have to modify the shell, or add the row_number() window
> > > function to the query.
> >
> >
> > Unless you need to use bleeding edge new features, use some GUI SQLite
> tool
> > instead.
> >
> > I use SQliteSpy myself for that. Gives me the time to process the query,
> > how many rows,
> > how many VM steps, Sort steps, that sort of thing. And shows me the
> result
> > in tabular fashion,
> > with color-coding based on value types (useful for a ducktyping DB like
> > SQLite).
> >
> > Windows only, very simple, but very fast. Uses SQLite 3.21, but updated
> > once in a while.
> >
> > I typically keep several queries I'm playing with, and CTRL-F9 the
> selected
> > one to run it.
> >
> > Don't get me wrong, the CLI shell is great, but for many rows, or wide
> > rows,
> > or plain convenience, a GUI tool is often more practical IMHO. My $0.02.
> > --DD
> > ___
> > 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] Regarding CoC

[Peter da Silva]

You would have had more luck with "Be excellent to each other".
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] segmentation fault in sqlite api call

[Peter da Silva]

You're passing a char * to a routine that expects a char **, and then
immediately trying to indirect through it, which means it's taking the
text, treating it as a pointer, and passing the random data it's pointing
to as a string to sqlite.

On Sun., 21 Oct. 2018, 11:55 Ratheendran R,  wrote:

> Hi,
>
> I am a embedded engineer and new to sqlite,we want to use sqlite for our
> local storage instead of file i/o.
>
> I have created a table with key and value records of char type,now if I try
> store a value with string length more than 50 char I get segmentation
> fault,please see the code below and let me know if I can improve it.
>
> sqlite3 *mod_init() {
>
> /* Open database */
> //rc = sqlite3_open("test.db", >db);
> lastError = sqlite3_open_v2("test.db", , SQLITE_OPEN_READWRITE |
> SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE|SQLITE_OPEN_NOMUTEX , NULL);
> if( lastError ) {
> fprintf(stderr, "Can't open database: %s\n",
> sqlite3_errmsg(dbObj->db));
> free(query);
> return(0);
> } else {
> fprintf(stdout, "Opened database successfully\n");
> }
> memset(query,0,200);
> strcpy(query,"CREATE TABLE IF NOT EXISTS cosmos_db("  \
> "key   TEXT PRIMARY KEY   NOT NULL," \
> "valueVARCHAR(100));");
>
> /* Execute SQL statement */
> lastError = sqlite3_exec(db, query, 0, 0, );
>
>
> if( lastError != SQLITE_OK ){
> fprintf(stderr, "SQL error: %s\n", zErrMsg);
> sqlite3_free(zErrMsg);
> } else {
> fprintf(stdout, "Table created successfully\n");
> }
> return db;
> }
>
>
>
> int mydef_set(cf_db_t *dbObj,char *key, char **value)
> {
> char *zErrMsg = 0;
> int rc;
> sprintf(query,"INSERT OR REPLACE INTO cosmos_db (key,value) values
> ('%s', '%s');",key,*value);
> /* Execute SQL statement */
> lastError = sqlite3_exec(db, query, 0, 0, );
> if( lastError != SQLITE_OK ) {
>   fprintf(stderr, "SQL error: %s\n", zErrMsg);
>   sqlite3_free(zErrMsg);
>} else {
>   fprintf(stdout, "Update done successfully\n");
>}
> return lastError;
> }
>
>
> int main()
> {
> sqlite3 *db;
> db=mod_init();
> mydef_set(db,"sssi","Hitjkahzdsdhdjksdhjsdhsjfhjsdhfjhsjd bcn
> bsdbgfhjsdgcsdfcbscbshdfgchdsfbbsdfcsfg");
> }
>
> Thanks,
> Ratheendran
> ___
> 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]ignoring syntax errors during search for sth include "symbols"

[Peter da Silva]

Don't put raw user input where code is expected. Match strongs are code.
You need to encapsulate (eg escape) or filter (delete bad characters) match
strings outside sqlite.

On Thu., 18 Oct. 2018, 13:50 Maziar Parsijani, 
wrote:

> It will crash or exit the program.
>
> On Thu, Oct 18, 2018 at 2:27 PM Jens Alfke  wrote:
>
> >
> >
> > > On Oct 18, 2018, at 11:17 AM, Maziar Parsijani <
> > maziar.parsij...@gmail.com> wrote:
> > >
> > > I just search for words an alphabets in different languages with python
> > and
> > > my database is sqlite but I need to do something to not getting error
> > when
> > > user input a wrong character like the ones that I told before.I can ban
> > > user to not input these characters but I am curious to find a way on
> > sqlite.
> >
> > What’s wrong with getting an error? If the error code or message is
> > specific enough, you can detect it and tell the user they’ve entered
> > invalid characters.
> >
> > —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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_complete and comments

[Peter da Silva]

Comments have no effect on the completeness of a statement, you will have
to look for leading '--' explicitly.

On Sun., 14 Oct. 2018, 13:38 Roger Binns,  wrote:

> I use sqlite3_complete in my shell in order to determine when a complete
> statement has been input and can be run.  (Otherwise a continuation
> "sqlite> " prompt is shown.)
>
> If the line entered is:
>
>-- hello
>
> Then the sqlite shell does not issue a continuation and "executes" the
> text.  However sqlite3_complete does not say that line is complete so
> the command line shell has extra logic to figure this out.  What is the
> right way of considering line comment complete as the SQLite shell?
>
> These lines get True from sqlite3_complete:
>
>   select 3; --
>   select 3 /* */ ;
>
> And these get False:
>
>   select 3
>   --
>   -- ;
>
>
>
> Roger
>
> ___
> 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 Windows GUI alternative to Excel?

[Peter da Silva]

There's a couple of Tcl/Tk SQLITE database managers that could be more easily 
turned into something like the Access GUI than starting from scratch. And they 
wouldn't be limited to Windows.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A SQL statement reformatter

[Peter da Silva]

Oh, nice!

On Fri, Sep 28, 2018 at 9:50 AM Chris Brody  wrote:

> I found https://github.com/mjibson/sqlfmt through their about page. No
> license though, just raised
> https://github.com/mjibson/sqlfmt/issues/33.
>
> GitHub fork-me ribbon would also be nice I think.
>
> Someone should have make this tool 20-30 years ago!
> On Fri, Sep 28, 2018 at 10:44 AM Peter da Silva  wrote:
> >
> > Pity they aren't making the code available. Not sure I want to send
> company
> > SQL to a random site.
> >
> > On Fri, Sep 28, 2018 at 7:48 AM Don V Nielsen 
> wrote:
> >
> > > Slick. That line width slider feature is something we don't get using
> Poor
> > > Man's Sql Formatter.
> > >
> > > Thanks!
> > >
> > > On Thu, Sep 27, 2018 at 7:03 PM Simon Slavin 
> wrote:
> > >
> > > > For those times when you have to understand a poorly-formatted SQL
> > > > statement:
> > > >
> > > > <https://sqlfum.pt>
> > > >
> > > > I seem to prefer 'full' mode.
> > > >
> > > > Simon.
> > > > ___
> > > > 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
> ___
> 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] A SQL statement reformatter

[Peter da Silva]

Pity they aren't making the code available. Not sure I want to send company
SQL to a random site.

On Fri, Sep 28, 2018 at 7:48 AM Don V Nielsen  wrote:

> Slick. That line width slider feature is something we don't get using Poor
> Man's Sql Formatter.
>
> Thanks!
>
> On Thu, Sep 27, 2018 at 7:03 PM Simon Slavin  wrote:
>
> > For those times when you have to understand a poorly-formatted SQL
> > statement:
> >
> > 
> >
> > I seem to prefer 'full' mode.
> >
> > Simon.
> > ___
> > 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

[sqlite] Core dump in sqlite 3.23 through 3.24 (at least)

[Peter Da Silva]

In tclsqlite.c, function DbMain()... somewhere between 3.19 and 3.23 there was 
a re-write of the argument parsing code for the sqlite command, and following 
code was removed and not completely replaced with a new equivalent:


  if( objc<3 || (objc&1)!=1 ){

Tcl_WrongNumArgs(interp, 1, objv,

  "HANDLE FILENAME ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN?"

  " ?-nomutex BOOLEAN? ?-fullmutex BOOLEAN? ?-uri BOOLEAN?"

#if defined(SQLITE_HAS_CODEC) && !defined(SQLITE_OMIT_CODEC_FROM_TCL)

  " ?-key CODECKEY?"

#endif

);

return TCL_ERROR;

  }

The result is that if you use the sqlite command with no arguments it core 
dumps on the code following, where it tries to use objv[1], instead of 
returning an error.


$ tclsh

% package require sqlite3

3.24.0

% sqlite3

Segmentation fault (core dumped)

It looks like there is an attempt to handle missing arguments at the top of the 
“for(i=2; ihttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In tea/configure.ac why isn't --with-system-sqlite default on linux?

[Peter Da Silva]

This is also a problem on FreeBSD, I just haven’t noticed it because the ports 
guys are on top of keeping their sqlite3 build up to date, and they do the 
smart thing and build --with-system-sqlite.

If you’re building sqlite-autoconf-3xx/tea, then why would the “system 
sqlite” ever be anything but current? Why would you install the Tcl extension 
but not the library itself?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In tea/configure.ac why isn't --with-system-sqlite default on linux?

[Peter Da Silva]

> You may be building on a system where they’ve still got SQLite 3.7.mumble 
> installed

In this case there are not two implementations present. Both libsqlite3.24.0.so 
and libsqlite3.so.0.8.6 were built from sqlite-autoconf-324. This is the 
only copy of libsqlite3 anywhere on the system.

> You may have noticed the continual pressure on this mailing list to be using 
> recent versions of SQLite.  One way to ensure that is to always statically 
> link.

That’s not an option, Tcl extensions are inherently dynamically loaded. If you 
use two Tcl extensions that call sqlite you need to ensure that both 
dynamically link to the same copy of the library. Not just the same version, 
the same copy, because they need to be operating on the same memory allocation 
arena and other globals.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] In tea/configure.ac why isn't --with-system-sqlite default on linux?

[Peter Da Silva]

So I was doing some work with another Tcl extension (Pgtcl) that was linking to 
libsqlite3. It worked fine on FreeBSD but on Linux (Ubuntu 4.13.0-27 generic) 
it crashed. We traced that down to it having two copies of libsqlite linked in, 
the one in the sqlite3 extension (libsqlite3.24.0.so) and the system sqlite 
that pgtcl found in /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6...

Forcing it to make libsqlite3.24.0.so just the Tcl glue to libsqlite3.so.0.8.6 
with ./configure --with-system-sqlite did the trick, but I’m just wondering why 
that wouldn’t be the normal way to do things.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_prepare_v2 with an empty statement.

[Peter Da Silva]

In the documentation for sqlite3_prepare_v2, it says:

*ppStmt is left pointing to a compiled prepared 
statement that can be executed using 
sqlite3_step(). If there is an error, 
*ppStmt is set to NULL. If the input text contains no SQL (if the input is an 
empty string or a comment) then *ppStmt is set to NULL. The calling procedure 
is responsible for deleting the compiled SQL statement using 
sqlite3_finalize() after it has 
finished with it. ppStmt may not be NULL.

Is an empty SQL statement always an error, i.e. is it possible for 
sqlite3_prepare_v2 to return SQLITE_OK while leaving *ppStmt NULL? I have been 
assuming not, but I’m tracking down a hard-to-find bug and double-checking all 
my assumptions.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

On 5/15/18, 1:25 AM, "Howard Chu" <h...@symas.com> wrote:

    Peter Da Silva wrote:
> On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of h...@symas.com> 
wrote:
> 
>  SQLightning
> 
> Do you have a link to the currently updated version of this? Google gives 
me projects that haven't been updated since 2015.

That's the most recent version. So far I haven't seen any compelling new 
features in subsequent SQLite versions to warrant a resync of the code.

There's been a couple of changes in syntax recently (UPSERT and IS TRUE/IS 
FALSE) that you might want to pick up for compatibility. They make it easier to 
have the same program switch between an SQLITE and PGSQL back end, which we're 
wont to do.

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

On 5/14/18, 9:17 AM, "sqlite-users on behalf of Bernard Ertl" 
 wrote:

Apologies if I muddled the waters here.  I read the "SQLightning" response 
below as SQLitening.  I didn't know there was a similarly named project out 
there.  I also can't see the beginning of this discussion to have context on 
what was originally asked, so I don't know which project was actually intended.

Ah, OK. Here's more context, don't know if it'll help: 
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2018-May/079224.html

Clemens Ladisch wrote:
> Techno Magos wrote:
>> So, memory sqlite is not really usable with multiple threads (readers).
>> While one might expect  that multiple readers of *memory *content could
>> scale even better than with file content.
> 
> Concurrent accesses to the same in-memory data structures must be
> serialized.  In shared-cache mode, the connections share the cache, while
> on-disk connections each have their own cache.
> 
>> Is there some special mode possible to achieve scaling up throughput with
>> multiple threads for memory sqlite content?
> 
> Put a DB file on a RAM disk.  Or on a normal disk (with looser synchronous
> and journal_mode settings), and rely on the OS file cache.

Or just use SQLightning, which has no scalability limits for readers.
 

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

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

Now I'm even more confused, there's "sqlightning" and "sqlitening". Are these 
different names for the same project? It doesn't seem so. Which was intended?

https://github.com/LMDB/sqlightning

On 5/14/18, 8:47 AM, "sqlite-users on behalf of Bernard Ertl" 
 wrote:

This is the latest:

http://www.sqlitening.planetsquires.com/index.php?topic=9427.0

I contributed to the last SQLitening update.  No one has reported any 
issues that need fixing or updating since that update.  It seems to be working 
quite well/stable. 



> On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
 
wrote:

> SQLightning

> Do you have a link to the currently updated version of this? Google gives 
me projects that haven't been updated since 2015.

> ___
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] shell edit quoting

[Peter Da Silva]

I think you're in "csv mode":

sqlite> select sql from sql_procs where name = 'a';
select * from "mytable" ;

sqlite> .header on
sqlite> .mode csv
sqlite> select sql from sql_procs where name = 'a';
sql
"select * from ""mytable"" ;
"

On 5/13/18, 7:04 PM, "sqlite-users on behalf of David Burgess" 
 wrote:

> And it works for me:

I'm pleased for you.
___
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] Multi threaded readers on memory sqlite cannot scale

[Peter Da Silva]

On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" 
 
wrote:

SQLightning

Do you have a link to the currently updated version of this? Google gives me 
projects that haven't been updated since 2015.

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

Re: [sqlite] Always call a value-quoting routine

[Peter Da Silva]

Nicely retro-feel website too:

https://droptablecompanies.co.uk/

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

Re: [sqlite] Always call a value-quoting routine

[Peter Da Silva]

On 5/7/18, 2:14 AM, "sqlite-users on behalf of Scott Robison" 
 wrote:
It could just indicate someone with a sense of humor who crafted a
name that looks like an injection attack for their company.

Most likely, or else it's part of an honor system exploit.

http://humorix.org/10277

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

Re: [sqlite] Fossil Delta Compression in SqLite

[Peter Da Silva]

On 5/6/18, 11:23 AM, "sqlite-users on behalf of Philip Bennefall" 
 
wrote:
Only the requirement for attribution in binaries. That can be 
significant in certain use cases.

One line of text in the documentation provided with the distribution doesn't 
seem burdensome. It's not like the advertising clause in the original BSD 
license... is that what you're thinking of?
 

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

[Peter Da Silva]

On 5/1/18, 1:42 PM, "sqlite-users on behalf of R Smith" 
 wrote:
My point is that CSV was not necessarily "meant" to be what you say. Who 
exactly "meant" for it to be that? Because the official stuff makes no 
such claim or mention.

Bah. Existential shenanigans. There's probably some pseudo-Latin or German term 
for what I meant.

Your point is however more towards the WHY question, and no, I am not 
sure. But, you know, it's Microsoft - after they made "locales" they 
probably wanted someone to use it, so they bullied Excel division into 
it. :)

From what I've heard the Excel division is the least bulliable part of the 
company. Didn't they maintain their own C compiler for a while to cut down on 
dependencies on the rest of Microsoft? 

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

[Peter Da Silva]

On 5/1/18, 1:15 PM, "sqlite-users on behalf of R Smith" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
ryansmit...@gmail.com> wrote:

On 1 May 2018, at 6:43pm, Peter Da Silva<peter.dasi...@flightaware.com>  
wrote:

> CSV is an interchange format, it's for software to communicate with other 
software, so the syntax needs to be independent of the locale since you don't 
know if the sender and recipient are in the same locale. Field separator is 
syntax, so the locale settings should not have any effect on it.

That is a very bold and idealistic claim.

I'm not claiming what you think I'm claiming. All I'm saying is that CSV is 
meant to communicate with software, not humans. Locales are there for humans. 
Using "the locale says list separates are semicolons" as a justification for 
sometimes using semicolons in an interchange format instead of commas doesn't 
make sense. Do you actually have a reference for that being the reason for 
Excel using semicolons?

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

[Peter Da Silva]

To another post hating on Excel - Excel has many flaws, but this is not 
one of them, it's a fault of the list-separator setting in the Windows 
OS on which the Excel runs.

CSV is an interchange format, it's for software to communicate with other 
software, so the syntax needs to be independent of the locale since you don't 
know if the sender and recipient are in the same locale. Field separator is 
syntax, so the locale settings should not have any effect on it.

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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

[Peter Da Silva]

Having tried to write a generic clean HANDLES ALL CSV reader for speedtables, I 
kind of want to burn Excel with nuclear fire, but that's a side issue. :)

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

Re: [sqlite] Header corruption

[Peter Da Silva]

On 4/17/18, 3:08 PM, "sqlite-users on behalf of Deon Brewis" 
 
wrote:
> So this was a special case of re-using the File handle as per the corruption 
> guide. One just has to be in the mindset that on unix based platforms, a 
> socket is a file handle. (Not instinctive if you're coming from a Windows 
> background).

Even on Windows, wouldn't doing an ssl_shutdown on a socket you'd already 
closed still have a risk of unexpected behavior?
 


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

Re: [sqlite] memory leak?

[Peter Da Silva]

It is normal in all modern operating systems for unused memory to be allocated 
to buffer cache, so over time the "free" memory Is expected to go down unless 
disk activity is near quiescent.  Some operating systems will combine "cache" 
and "free" memory to hide this from the casual user, but I consider this 
deceptive.

On 4/12/18, 9:34 PM, "sqlite-users on behalf of king3306" 
 
wrote:

first thanks you reply 

After running for some time  i found sqlite3_memory_used return value keep
constant about 2M,but linux free memory is fewer and fewer,i make sure no
other places are leaked,why?

this whether or not a normal behavior?

if not ,how can i to analysis this problem?  

Looking forward to your answer



--
Sent from: http://sqlite.1065341.n5.nabble.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] SELECT with CASE

[Peter Da Silva]

Maybe something like: SELECT MIN(thedate) FROM dates WHERE thedate >= 
date('now');

On 4/12/18, 11:05 AM, "sqlite-users on behalf of Peter Da Silva" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
peter.dasi...@flightaware.com> wrote:

Ah, so if there's two days in a row that aren't school days, you need to be 
able to select a day two or more days in the future.

On 4/12/18, 11:02 AM, "sqlite-users on behalf of Csányi Pál" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of csanyi...@gmail.com> 
wrote:
So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.
___
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] SELECT with CASE

[Peter Da Silva]

Ah, so if there's two days in a row that aren't school days, you need to be 
able to select a day two or more days in the future.

On 4/12/18, 11:02 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:
So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.
___
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] SELECT with CASE

[Peter Da Silva]

One of the lines of the output does indeed have '2018-04-12' as expected.

On 4/12/18, 9:59 AM, "sqlite-users on behalf of Csányi Pál" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of csanyi...@gmail.com> 
wrote:

2018-04-12 16:51 GMT+02:00 Peter Da Silva <peter.dasi...@flightaware.com>:
> You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which 
is what you're getting, no?

Yes, indeed.
But I thought the first part would be done:
CASE TheDate WHEN date('now') THEN TheDate

that is, if the TheDate is = date('now') THEN
it should select that TheDate which is equal to the current date:
date('now'), no?
___
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] SELECT with CASE

[Peter Da Silva]

You're asking for "ELSE date('now','+1 day')" which is 2018-04-13 which is what 
you're getting, no?

On 4/12/18, 9:47 AM, "sqlite-users on behalf of Csányi Pál" 
 
wrote:

Hi Simon,

2018-04-12 14:32 GMT+02:00 Simon Slavin :
> On 12 Apr 2018, at 1:25pm, Csányi Pál  wrote:
>
>> SELECT CASE TheDate = date('now') WHEN TheDate ...
>
> I don't think that's what you wanted.  Perhaps
>
> SELECT CASE TheDate WHEN date('now') ...
>
> But you should test the output of "date('now')" to make sure it is in the 
format you want.

The output of "date('now') is in format I want:

SELECT date('now');
2018-04-12

I tried this:

SELECT CASE TheDate WHEN date('now') THEN TheDate ELSE date('now','+1
day') END TheDate FROM Dates;
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-13
2018-04-12
2018-04-13
2018-04-13
2018-04-13

but I expect the output like this:
2018-04-12

because today date is:
2018-04-12

What am I missing here?

-- 
Best, Pali
___
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] Help using 'attach database' or Why is this an error?

[Peter Da Silva]

Try this:

attach database 'test.db' as con2;

On 4/11/18, 9:51 AM, "sqlite-users on behalf of J Decker" 
 
wrote:

When I attempt to use attach database I get an error about 'no such column'


M:\sqlite3\sqlite3\build>sqlite3 test.db

SQLite version 3.23.0 2018-04-02 11:04:16es
Enter ".help" for usage hints.
sqlite> attach database test.db as con2;
Error: no such column: test.db
sqlite>


I was trying to debug this set of commands, which is actually run on two
connections within the same process... (oh I had misspelled foreign as
foriegn)




PRAGMA foreign_keys=on;

create table `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);

create table `option4_map` (`option_id` char(36) NOT
NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id` char(36)
NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2` UNIQUE
(`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
CASCADE ON DELETE CASCADE);

create table `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id`
UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE);select tbl_name,sql from sqlite_master where type='table'
and

BEGIN TRANSACTION;

insert into option4_name (name_id,name) values(
'82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );

insert into option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
);

#SQLITE ERROR:Result of prepare failed? foreign key mismatch -
"option4_map" referencing "option4_name" at char 185[] in [insert into
option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
)]
___
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] In memory only WAL file

[Peter Da Silva]

You could put the WAL in a tmpfs/ramfs so the DB would only get corrupted if 
the OS crashed, it'd still be there for recovering from application crashes.
 

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

Re: [sqlite] MIN() and MAX() of set of row values

[Peter Da Silva]

On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" 
 wrote:
> can think of to do it is to devise a metric to turn a pair (a, b) into one 
> number.

Problem is you can't uniquely order pairs of points. Is (1, 2) greater or 
lesser than (2, 1)?

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

Re: [sqlite] How to convert a datetime column to a date?

[Peter Da Silva]

On 3/22/18, 9:43 AM, "sqlite-users on behalf of R Smith" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
ryansmit...@gmail.com> wrote:
> On 2018/03/22 4:22 PM, Peter Da Silva wrote:
> > Don't actually need to convert it to datettime if it's already in the right 
> > format, do you, or does datetime() do some grooming the source needs?
>  
> Yes indeed, it's down to "vetting" and in no way needed for the  conversion. 
> If datetime(x) understand x and doesn't produce any NULLs,  then x is a valid 
> ISO8601 date (or valid-enough for SQLite) and all is fine.

Sure, but should you do that there? Because it won't tell you you had bad data, 
it'll just lose it.
 

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

Re: [sqlite] How to convert a datetime column to a date?

[Peter Da Silva]

On 3/22/18, 9:22 AM, "sqlite-users on behalf of Ron Watkins" 
 
wrote:
> Is there an easy way to  "fix" the data already in the table?

The string trim trick David and Ryan suggested would work. To avoid banging on 
rows already converted, maybe:

UPDATE foo SET dttm = trim(dttm,) WHERE dttm like '''%''';

Note that's 4 single quotes in the first string, and two sets of three single 
quotes in the second.

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

Re: [sqlite] How to convert a datetime column to a date?

[Peter Da Silva]

Don't actually need to convert it to datettime if it's already in the right 
format, do you, or does datetime() do some grooming the source needs?

On 3/22/18, 9:19 AM, "sqlite-users on behalf of David Raymond" 
 wrote:

Use datetime there instead of date or you'll lose the time part of it.

UPDATE foo SET dttm = datetime(trim(dttm,));


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

Re: [sqlite] How to convert a datetime column to a date?

[Peter Da Silva]

SQLITE3 datetime fields are actually text.

Fix the file that you're importing so it doesn't have quotes around the date. 
It looks like you're using some kind of modified CSV exporter to create the 
file.

On 3/22/18, 9:06 AM, "sqlite-users on behalf of Ron Watkins" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of rwa...@gmail.com> 
wrote:

The file that I use ".import" on contains records like this:

 

'2018-03-22 07:01:01'|2533268

 

I had assumed the value was being treated as a datetime, but it looks like 
it may be treated as a string?

Im a bit confused because the column definition sais "datetime", not 
"varchar", so if it's being treated as a string what can I do to fix it to be 
treated as a actual datetime datatype?

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:49 AM
To: SQLite mailing list
Subject: Re: [sqlite] How to convert a datetime column to a date?

 

1. It looks like you have superfluous quotes around the dates. That's 
probably your issue.

 

2. Try "select distinct dttm from foo LIMIT 10;"

 

On 3/22/18, 8:47 AM, "sqlite-users on behalf of Ron Watkins" < 
<mailto:sqlite-users-boun...@mailinglists.sqlite.org%20on%20behalf%20of%20rwa...@gmail.com>
 sqlite-users-boun...@mailinglists.sqlite.org on behalf of rwa...@gmail.com> 
wrote:

sqlite> select distinct dttm from foo;



…  (lots of records)



'2018-03-22 06:25:01'



'2018-03-22 06:26:01'



'2018-03-22 06:27:01'



'2018-03-22 06:28:01'



'2018-03-22 06:29:01'



'2018-03-22 06:30:01'



'2018-03-22 06:31:01'



'2018-03-22 06:32:02'



'2018-03-22 06:33:01'



'2018-03-22 06:34:01'



'2018-03-22 06:35:01'



'2018-03-22 06:36:01'



'2018-03-22 06:37:01'



'2018-03-22 06:38:01'



'2018-03-22 06:39:01'



'2018-03-22 06:40:01'



'2018-03-22 06:41:01'



'2018-03-22 06:42:01'



'2018-03-22 06:43:01'



'2018-03-22 06:44:01'



sqlite>



 



BTW, is there some equilivant to “select top 10 * from foo;” style to 
reduce the number of records? I tried this “top 10” but it’s apparently  not 
supported.



 



-Original Message-

From: sqlite-users [ 
<mailto:sqlite-users-boun...@mailinglists.sqlite.org> 
mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Da Silva

Sent: Thursday, March 22, 2018 6:33 AM

To: SQLite mailing list

Subject: Re: [sqlite] How to convert a datetime column to a date?



 



It might be helpful to provide some examples of what you have in those 
DATETIME columns.



 



___



sqlite-users mailing list



 < <mailto:sqlite-users@mailinglists.sqlite.org> 
mailto:sqlite-users@mailinglists.sqlite.org>  
<mailto:sqlite-users@mailinglists.sqlite.org> 
sqlite-users@mailinglists.sqlite.org



 < 
<http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>  
<http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___

sqlite-users mailing list

 <mailto:sqlite-users@mailinglists.sqlite.org> 
sqlite-users@mailinglists.sqlite.org

 <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



 

___

sqlite-users mailing list

 <mailto:sqlite-users@mailinglists.sqlite.org> 
sqlite-users@mailinglists.sqlite.org

 <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo

Re: [sqlite] How to convert a datetime column to a date?

[Peter Da Silva]

1. It looks like you have superfluous quotes around the dates. That's probably 
your issue.

2. Try "select distinct dttm from foo LIMIT 10;"

On 3/22/18, 8:47 AM, "sqlite-users on behalf of Ron Watkins" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of rwa...@gmail.com> 
wrote:
sqlite> select distinct dttm from foo;

…  (lots of records)

'2018-03-22 06:25:01'

'2018-03-22 06:26:01'

'2018-03-22 06:27:01'

'2018-03-22 06:28:01'

'2018-03-22 06:29:01'

'2018-03-22 06:30:01'

'2018-03-22 06:31:01'

'2018-03-22 06:32:02'

'2018-03-22 06:33:01'

'2018-03-22 06:34:01'

'2018-03-22 06:35:01'

'2018-03-22 06:36:01'

'2018-03-22 06:37:01'

'2018-03-22 06:38:01'

'2018-03-22 06:39:01'

'2018-03-22 06:40:01'

'2018-03-22 06:41:01'

'2018-03-22 06:42:01'

'2018-03-22 06:43:01'

'2018-03-22 06:44:01'

sqlite>

 

BTW, is there some equilivant to “select top 10 * from foo;” style to 
reduce the number of records? I tried this “top 10” but it’s apparently  not 
supported.

 

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Peter Da Silva
Sent: Thursday, March 22, 2018 6:33 AM
To: SQLite mailing list
Subject: Re: [sqlite] How to convert a datetime column to a date?

 

It might be helpful to provide some examples of what you have in those 
DATETIME columns.

 

___

sqlite-users mailing list

 <mailto:sqlite-users@mailinglists.sqlite.org> 
sqlite-users@mailinglists.sqlite.org

 <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> 
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] How to convert a datetime column to a date?

[Peter Da Silva]

It might be helpful to provide some examples of what you have in those DATETIME 
columns.

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

Re: [sqlite] UPSERT

[Peter Da Silva]

On 3/19/18, 3:37 AM, "sqlite-users on behalf of Paul" 
 wrote:
> Fort me personally, the most sad thing is an annoyance. Because I have to 
> maintain two almost identical queries and manually tweak strategies.

I almost always generate queries dynamically if they're "almost identical". 
Only one-offs get handcrafted queries.

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

Re: [sqlite] Format of sqlite-users digests.

[Peter Da Silva]

On 2/26/18, 12:24 PM, "Richard Hipp"  wrote:
> We use a 3rd party mailing list manager:  GNU MailMan.  You'll need to take 
> up your concerns with them, I'm afraid.  I know nothing about the MailMan 
> code.

Mailman supports mime-style digests, Randall may prefer to get those rather 
than plaintext? It would depend on how their email software handles 
multipart/digest.

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

Re: [sqlite] printf() problem padding multi-byte UTF-8 code points

[Peter Da Silva]

On 2018-02-17, at 17:36, Richard Hipp  wrote:
> The current behavior of the printf() function in SQLite, goofy though
> it may be, exactly mirrors the behavior of the printf() C function in
> the standard library in this regard.
> 
> So I'm not sure whether or not this is something that ought to be "fixed".

Printf's handling of unicode is inconsistent in other ways, too. I suspect that 
there's still undefined behavior floating around in there too. Even wprintf 
isn't entirely unsurprising:

% env
...
LANG=en_US.UTF-8
...
% cat localized.c
#include 
#include 

int main() {
wprintf (L"'%4ls'\n", L"äöü");
}
% cc localized.c
% ./a.out
' ???'
% cat delocalized.c
#include 
#include 
#include 

int main() {
setlocale(LC_ALL, "");
wprintf (L"'%4ls'\n", L"äöü");
}
% cc delocalized.c
% ./a.out
' äöü'
% uname -a
Darwin Stonehenge.local 16.7.0 Darwin Kernel Version 16.7.0: Thu Jan 11 
22:59:40 PST 2018; root:xnu-3789.73.8~1/RELEASE_X86_64 x86_64

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

Re: [sqlite] Advice for a "how-to" situation.

[Peter Da Silva]

Seems like you'd want to create a rowid primary key (internal_id integer 
primary key) that's the foreign key for the other table.

On 2/15/18, 1:19 PM, "sqlite-users on behalf of Michael Tiernan" 
 wrote:
(The objective is to have a single field in another table that 
identifies the CPU used in a system. It will be a many-to-one reference 
to this CPUModel.)

Any advice? Pointers to documentation offering advice will help too.

Thanks for everyone's time.
___
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] Tee to a table

[Peter Da Silva]

Switch -glob and switch -regexp are also handy for this kind of code.

On 2/1/18, 3:07 PM, "sqlite-users on behalf of Peter Da Silva" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
peter.dasi...@flightaware.com> wrote:

You can do something like this, for one record per file:

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
lappend content $line
}
set content [join $content "\n"; # or otherwise manipulate the text you got 
back.
db eval {INSERT INTO whatever (index, content) VALUES ($index, $content);}
db close

Or anything in between:

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
if {[matches_end_of_content $line]} {
   db eval {INSERT INTO whatever (index, content) VALUES ($index, 
$content);}
   set content {}
   } elseif {[matches_start_of_content $line]} {
  get_index_from_content $content index
   } else {
  add_line_to_content $line content
   } 
}
db close

On 2/1/18, 3:00 PM, "sqlite-users on behalf of Cecil Westerhof" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
cldwester...@gmail.com> wrote:
    
    2018-02-01 21:49 GMT+01:00 Peter Da Silva 
<peter.dasi...@flightaware.com>:

> It's pretty easy in Tcl
>
> Sqlite3 db file.sqlite
> while {[gets stdin line] > 0} {
> parse_line_into index content; # or whatever you do to extract 
content
> from the line
> db eval {INSERT INTO whatever (index, content) VALUES ($index,
> $content);}
> }
> db close
>

​Looks promising.​ The 'problem' is that I get a record pro line. But 
that
is not a big problem I think. On the plus side it is easy to make a GUI
instead of a command line version.

Thanks.



> On 2/1/18, 2:25 PM, "sqlite-users on behalf of Cecil Westerhof" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> cldwester...@gmail.com> wrote:
>
> At the moment I have a script where I send the output of a ffmpeg
> command
> to the terminal and a file. Is it possible to send the output to a
> SQLite
> table. I like to use tcl for this.
>

-- 
Cecil Westerhof
___
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] Tee to a table

[Peter Da Silva]

You can do something like this, for one record per file:

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
lappend content $line
}
set content [join $content "\n"; # or otherwise manipulate the text you got 
back.
db eval {INSERT INTO whatever (index, content) VALUES ($index, $content);}
db close

Or anything in between:

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
if {[matches_end_of_content $line]} {
   db eval {INSERT INTO whatever (index, content) VALUES ($index, 
$content);}
   set content {}
   } elseif {[matches_start_of_content $line]} {
  get_index_from_content $content index
   } else {
  add_line_to_content $line content
   } 
}
db close

On 2/1/18, 3:00 PM, "sqlite-users on behalf of Cecil Westerhof" 
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
cldwester...@gmail.com> wrote:

2018-02-01 21:49 GMT+01:00 Peter Da Silva <peter.dasi...@flightaware.com>:

> It's pretty easy in Tcl
>
> Sqlite3 db file.sqlite
> while {[gets stdin line] > 0} {
> parse_line_into index content; # or whatever you do to extract content
> from the line
> db eval {INSERT INTO whatever (index, content) VALUES ($index,
> $content);}
> }
> db close
>

​Looks promising.​ The 'problem' is that I get a record pro line. But that
is not a big problem I think. On the plus side it is easy to make a GUI
instead of a command line version.

Thanks.



> On 2/1/18, 2:25 PM, "sqlite-users on behalf of Cecil Westerhof" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> cldwester...@gmail.com> wrote:
>
> At the moment I have a script where I send the output of a ffmpeg
> command
> to the terminal and a file. Is it possible to send the output to a
> SQLite
> table. I like to use tcl for this.
>

-- 
Cecil Westerhof
___
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] Tee to a table

[Peter Da Silva]

It's pretty easy in Tcl

Sqlite3 db file.sqlite
while {[gets stdin line] > 0} {
parse_line_into index content; # or whatever you do to extract content from 
the line
db eval {INSERT INTO whatever (index, content) VALUES ($index, $content);}
}
db close

On 2/1/18, 2:25 PM, "sqlite-users on behalf of Cecil Westerhof" 
 wrote:

At the moment I have a script where I send the output of a ffmpeg command
to the terminal and a file. Is it possible to send the output to a SQLite
table. I like to use tcl for this.

-- 
Cecil Westerhof
___
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