Re: [sqlite] Trigger name missing

[John G]

Would it be possible to create an SQL verification program, which just like
'sqlite3_analyzer' and 'sqldiff' could be run separately?
It could *warn* about apparently incompletely defined triggers and other
possible pitfalls.

Then developers could use it before installing the next version of SQLite3.

John G

On Wed, 26 Feb 2020 at 19:09, Jean-Luc Hainaut 
wrote:

> On 26/02/2020 12:18, Richard Hipp wrote:
> > On 2/26/20, Jean-Luc Hainaut  wrote:
> >> Hi all,
> >>
> >> It seems that SQLite (version 31.1) accepts a trigger declaration in
> >> which the name is missing. When fired, this trigger doesn't crashes but
> >> exhibits a strange behaviour. In particular, while expression
> >> "new." in an "insert" trigger returns the correct value, the
> >> equivalent expression "select  from T where Id = new.Id" always
> >> returns null (column "Id" is the PK of table "T"). Similarly, "update T
> >> set  =   where Id = new.Id" (silently) fails.
> >>
> > What is the text of your trigger?
>
> This trigger belongs to a small experimental application I'm writting to
> study the extent to what application code (initially in Java, Python,
> etc.) can be integrated into SQL, notably through triggers. In short,
> can one convert a standard 3-tier business application into just a GUI +
> an active database, without the standard application program between them?
> The following trigger controls the registration of a customer order
> [insert into CUSTORDER(OrdID, CustID,ItemID,Qty,...) values (...)] when
> the available quantity (Qavail) of the requested item is sufficient.
> If the name 'CORD_INS1' is missing, this trigger (among others):
>- updates the ITEM table. [successfully]
>- completes the customer order (Price and State in CUSTORDER). [fails]
>- creates an invoice (in CUSTINVOICE) and prints it in a text file.
> [successfully]
>
> After reading all your explanations and comments, my interpretation is
> as follows:
> 1. The SQLite syntax tells me that the "before/after/instead of" keyword
> can be missing, in which case (I guess) "before" is assumed.
> 2. So, my "name-less" trigger is valid and must be read:
> create trigger "after" before insert on CUSTORDER ...
> 3. In a "before" trigger, the current row cannot be updated, since it
> doesn't exist yet (though several RDBMS have a specific syntax for that).
> 4. This explains why SQLite legitimely ignores the second update.
> Am I right?
> If I am, this behaviour is "not a bug but a feature". It could be useful
> to precise these facts in the documentation.
>
> Thanks to all
>
> Jean-Luc Hainaut
>
> create table CUSTOMER (CustID,Name,Address,City,Account,...);
> create table ITEM
> (ItemID,Description,Price,QonHand,Qord,Qavail,Qmargin,...);
> create table CUSTORDER (OrdID,DateOrd,CustID,ItemID,Price,Qty,State,...);
> create table CUSTINVOICE
> (InvID,DateInv,OrdID,CustID,ItemID,Price,Qty,Amount,State,...);
> create table SUPPLIER (SuppID,Name,City,...);
> create table OFFER (SuppID,ItemID,Price,Delay,...);
> create table SUPPORDER (OrdID,DateOrd,ItemID,SuppID,Price,Qty,State,...);
>
> create trigger CORD_INS1
> after insert on CUSTORDER
> for each row
> when new.Qty <= (select Qavail from ITEM where ItemID = new.ItemID)
> and  not exists (select * from CUSTINVOICE where OrdID = new.OrdID)
> begin
> --
> -- Subtract Qty from Qavail:
> update ITEM
> set   Qavail = Qavail - new.Qty
> where ItemID = new.ItemID;
> --
> --...
> -- Set CUSTORDER.State to 'invoiced' or 'pending'
> update CUSTORDER
> set   Price = (select Price from ITEM where ItemID = new.ItemID),
>   State = case when new.Qty <= (select QonHand from ITEM where
> ItemID = new.ItemID)
>then 'invoiced'
>else 'pending'
>   end
> where OrdID = new.OrdID;
> --
> -- Create an invoice and print it:
> insert into CUSTINVOICE(...);
> --
> end;
>
> ___
> 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] Fwd: inserting new data only

[John McKown]

On Tue, Feb 25, 2020 at 11:03 AM Przemek Klosowski <
przemek.klosowski+sql...@gmail.com> wrote:

> I am storing time series data arriving from a sensor into (time,value)
> records, like so:
> 10:32  12
> 10:35  15
> 10:37  15
> 10:39  13
> 10:43  13
> 10:46  18
>
> and I want to avoid storing repetitive data, so that the database should
> contain
> 10:32  12
> 10:35  15
> 10:39  13
> 10:46  18
> where only the earliest time with the unchanging value is stored.
>
> I don't see how INSERT could be conditional on e.g.  value != (select
> value from tbl order by time descending limit 1), so I thought I'd use
> triggers. The only way I could think of was to delete the new
> duplicate record after it has been inserted:
>
> create trigger cull after insert on tbl when
>  (select value-lead(value) over (order by time desc) from a limit 1) = 0
> begin
>delete from a where time like new.time;
> end;
>
> Is there a simpler way?
> ___
>


Why not:

CREATE TABLE ME (ATIME TIME, VALUE INTEGER PRIMARY KEY);

You can't INSERT duplicate numbers into the "VALUE" column, it will fail.


-- 
People in sleeping bags are the soft tacos of the bear world.
Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?

[John G]

You can do it in Tcl (reusing Jose's example tables) like this:

package require sqlite3
sqlite3 dbcmd ~/tmp/grbg.db

 dbcmd eval "create table table0 (id INTEGER PRIMARY KEY, t0a, t0b, t0c,
t0d)"
 dbcmd eval "insert into table0 (t0a, t0b, t0c, t0d) values ('text in
here', 'Shelby 2002', '2 plus 2 equals 4', 'I am going home soon')"
 dbcmd eval "create table table1 (id INTEGER PRIMARY KEY, t10,t11,t12)"
 dbcmd eval "insert into table1 (t10,t11,t12) values ('p001', 'Shelby
2002', '1 plus 1 equals 2')"
 dbcmd eval "create table table2 (id INTEGER PRIMARY KEY, t2a, t2b, t2c,
t2d)"
 dbcmd eval "insert into table2 (t2a, t2b, t2c, t2d) values ('in here',
'going home', '2020-02-11','Once upon a time...')"

 set searchstr "plus"
 set SQL ""
 set u ""
 set tables [dbcmd eval "select name from sqlite_master where type='table'"]
 foreach t $tables {
   dbcmd eval "pragma table_info($t)" {
# cid name type notnull dflt_value pk
if {[regexp -nocase -- {char|text} $type] || $type == ""} {
  append SQL "$u select '$t' as tn, '$name' as cn, $name as val from $t
\
 where $name like '%$searchstr%' "
   set u "union"
}
if {[string length $SQL] > 100} {
error "too many fields"
}
   }
 }
 dbcmd eval $SQL {
   puts [format "Field %-15s on %-15s has the string %-15s: %s"  $cn $tn
$searchstr $val]
 }
Field t0c     on table0  has the string plus   : 2
plus 2 equals 4
Field t12 on table1  has the string plus   : 1
plus 1 equals 2

John G


On Thu, 13 Feb 2020 at 15:24, Jose Isaias Cabrera 
wrote:

>
> Scott, on Thursday, February 13, 2020 09:01 AM, wrote...
> >
> > Can I search all tables and columns of SQLite database for a specific
> > text string? I'm sure this question has been asked many times, but I'm
> > having trouble finding a solid answer.
> > My problem: My clients SQLite database has 11 tables and multiple columns
> > (some designated and others not) and they want to be able to search the
> > entire database for a specific text or phrase.
> > What I have done: I've been searching a couple days and found the Full
> > Text search on SQLite home based upon using a virtual table, but I don't
> > think that will work. It appears that I may be able to search the
> > sqlite_master but it seems it may only contain table and column
> information
> > only minus the data.
> > What I'm working in: This is an Android app written in Java using the
> > SQLite
> > What I hope to do: Find a simple query statement or combination of
> > statements that may help to efficiently query for the string across
> tables
> > and columns before I resort to multiple queries and methods for all 11
> > tables.
> > I'm looking for any experienced thoughts or suggestions anyone may have
> > encountered resolving this kind of issue if available. I'm not expecting
> > anyone to solve it for me -- just some guidance would be helpful.
>
> This is a very wide open question.  It is a lot of work to create the
> query.
> I actually have to do this for some tables and some fields, but I know
> these
> tables and these fields. Here are some questions:
>
> 1. What are you going to do when you find a string match in a table field?
>
> 2. Do you need to know that table?  Do you need to know the field?
>
> 3. Do you need the whole content of that field if matched?
>
> There are just too many questions to help, but it is possible if you know
> what do you want to do. Here are some ideas:
> a. The command prompt has a .table option that will provide all the tables
> available on a DB
> b. The .schema [tablename] will give you the table's fields
>
> Imagine these three tables:
> create table table0 (id INTEGER PRIMARY KEY, t0a, t0b, t0c, t0d);
> insert into table0 (t0a, t0b, t0c, t0d) values ('text in here', 'Shelby
> 2002', '2 plus 2 equals 4', 'I am going home soon');
> create table table1 (id INTEGER PRIMARY KEY, t10,t11,t12);
> insert into table1 (t10,t11,t12) values ('p001', 'Shelby 2002', '1 plus 1
> equals 2');
> create table table2 (id INTEGER PRIMARY KEY, t2a, t2b, t2c, t2d);
> insert into table2 (t2a, t2b, t2c, t2d) values ('in here', 'going home',
> '2020-02-11','Once upon a time...');
>
> SELECT
> 'field t0a on table0 has the string [plus]: ', t0a from table0
> WHERE t0a LIKE '%plus%'
> UNION
> SELECT
> 'field t0b on table0 has the string [plus]: ', t0b from table0
> WHERE t0b LIKE '%plus%'
> UNION
> SELECT
> 'field t0c on table0 has the string [plus]: ', t0c from table0
> WHERE t0c LIKE '%plus%'
> UNION
> SELECT
> 'field t0

Re: [sqlite] New word to replace "serverless"

[John McMahon]

"When I use a word,' Humpty Dumpty said in rather a scornful
tone, 'it means just what I choose it to mean - neither more nor
less.'

'The question is,' said Alice, 'whether you can make words mean
so many different things.'

'The question is,' said Humpty Dumpty, 'which is to be master -
that's all."

- Lewis Carroll, Through the Looking Glass


On 28/01/2020 09:18, 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"?




--
Regards
   John McMahon
  li...@jspect.fastmail.fm

When people say "The climate has changed before,"
these are the kinds of changes they're talking about.
https://xkcd.com/1732/

___
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"

[John McMahon]

Define what "serverless" means to you in the SQLite context and provide 
a link or pop-up to that definition wherever "serverless" occurs in the 
documentation. Perhaps also include what it doesn't mean if you think 
this is becoming an issue.


How others choose to define "serverless" should not be your problem.

Just my pennies worth,
John


On 28/01/2020 09:18, 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"?




--
Regards
   John McMahon
  li...@jspect.fastmail.fm

When people say "The climate has changed before,"
these are the kinds of changes they're talking about.
https://xkcd.com/1732/

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

Re: [sqlite] Shell commands for controlling headers

[John McMahon]

On 26/11/2019 02:49, David Raymond wrote:

Dr Hipp replied to this 2 days ago with this:


Documentation fix https://www.sqlite.org/docsrc/info/a2762f031964e774
will appears in the next release.

".header" is an abbreviation for ".headers" and does exactly the same thing.


AFAIK all dot commands can be abbreviated to the shortest distinct 
partial word, thus ".headers on" can be shortened to ".hea on". This has 
been the case at least back to sqlite2 and back then, this was mentioned 
in the CLI documentation and is probably also somewhere in the current 
documentation. I leave finding it as an exercise for the reader.


John




-Original Message-
From: sqlite-users  On Behalf Of 
John McKown
Sent: Monday, November 25, 2019 9:51 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Shell commands for controlling headers

On Mon, Nov 25, 2019 at 8:42 AM Craig Maynard  wrote:


All,

Could someone clarify the difference between the two sqlite3 shell
commands .header and .headers?

The relevant documentation page: https://www.sqlite.org/cli.html

On the cli page, .header is discussed in section 5 but does not appear in
Section 3.

Thanks,
Craig

--
Craig H Maynard
Rhode Island, USA



In the sqlite cli itself, doing an ".help", I see:

.header(s)

So I am guessing that they are the same things, perhaps for compatibility
with something in the past.



--
Regards
   John McMahon
  li...@jspect.fastmail.fm

When people say "The climate has changed before,"
these are the kinds of changes they're talking about.
https://xkcd.com/1732/

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

Re: [sqlite] Shell commands for controlling headers

[John McKown]

On Mon, Nov 25, 2019 at 8:42 AM Craig Maynard  wrote:

> All,
>
> Could someone clarify the difference between the two sqlite3 shell
> commands .header and .headers?
>
> The relevant documentation page: https://www.sqlite.org/cli.html
>
> On the cli page, .header is discussed in section 5 but does not appear in
> Section 3.
>
> Thanks,
> Craig
>
> --
> Craig H Maynard
> Rhode Island, USA
>
>
In the sqlite cli itself, doing an ".help", I see:

.header(s)

So I am guessing that they are the same things, perhaps for compatibility
with something in the past.

-- 
People in sleeping bags are the soft tacos of the bear world.
Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Opposite of SQLite

[John McKown]

On Thu, Oct 10, 2019 at 1:55 PM Ned Fleming  wrote:

>
> > Someone asked:
> >
> >>> What the opposite of "Lite”?
> >
>
> SQLessLite
>

NoSQLHeavy?



>
> --
> Ned
>
>

-- 
I find television very educational. The minute somebody turns it on, I go
into the library and read a good book
-- Groucho Marx

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query for Many to Many

[John G]

Or without the added calories (syntactic sugar) :

select a.*, b.*
from author_books ab, author a, books b
where  a.author_id  = ab.author_id
  and  b.book_isbn = ab.book_isbn

On Tue, 27 Aug 2019 at 15:52, David Raymond 
wrote:

> It does support natural joins.  changes" comments here>
>
> USING needs parenthesis around the column list: ...using
> (author_id)...using (book_isbn)...
>
>
> -Original Message-
> From: sqlite-users  On
> Behalf Of Dominique Devienne
> Sent: Tuesday, August 27, 2019 10:08 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Query for Many to Many
>
> On Tue, Aug 27, 2019 at 4:00 PM Dominique Devienne 
> wrote:
>
> > select author.*, books.*
> >   from author_books
> >   join author on author.author_id  = author_books.author_id
> >   join books  on books.book_isbn   = author_books.book_isbn
> >
>
> Which can also be written:
>
> select author.*, books.*
>   from author_books
>   join author using author_id
>   join books  using book_isbn
>
> Or even:
>
> select author.*, books.*
>   from author_books
>   natural join author
>   natural join books
>
> All of the above untested of course :).
> Not even sure SQLite supports natural join or not (I'd guess it does). --DD
>
> https://stackoverflow.com/questions/45531762/sql-join-using-vs-join-on
>
> https://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join
> ___
> 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] Should SQLite distinguish between +0.0 and -0.0 on output?

[John McKown]

On Wed, Jun 12, 2019 at 8:35 AM Richard Hipp  wrote:

> IEEE754 floating point numbers have separate representations for +0.0
> and -0.0.  As currently implemented, SQLite always display both
> quantities as just "0.0".
>
> Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?  Or,
> would that create unnecessary confusion?
>

Is there any case where the display makes a difference? I cannot think of
any case where it is mathematically important. Actually the "0.0" is more
mathematically correct because zero is neither positive nor negative.

The IBM "mainframe" zSeries processors implement three floating points
formats: HFP (historic "hexadecimal Floating Point"), BFP (Binary Floating
Point -- IEEE754) and DFP (Decimal Floating Point -- IEEE754-2008). I am
not aware of any other architecture which does this.



>
> --
> D. Richard Hipp
> d...@sqlite.org


-- 
This is clearly another case of too many mad scientists, and not enough
hunchbacks.


Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] bug in sqlite3??

[John Brigham]

  I have an Arduino/Python experiment that generates lots of simple numerical 
data; about 7000 records a day.  I run it through Python into an sqlite3 
database.I have been running this for 8 months.  I start a new database about 
every six weeks so every database has about 260k rows.   I have one database 
that is missing the first three weeks. There is evidence that the records are 
in the file but not appearing:  The size of the file reflects the number of 
rows that should be present.  And furthermore, the size of the file nicely 
reflects the number of days. My objectives are twofold:  first, can you fix 
this faulty file and second, this appears to be a bug in your software and I 
want you informed. I use DBrowser and am satisfied with it. I have knowledge 
about CLI SQL, but am not presently using it. The software can be seen at  
https://github.com/mrphysh?tab=repositories I quickly check the link and see 
that the example data-base is the exact one.Notice that the file name is dec3.  
And notice that the database starts with dec 26. Trust me when I say that this 
file is way to big for the number of rows. JohnAsheville ncmrph...@juno.com

Sad News For Meghan Markle And Prince Harry
track.volutrk.com
http://thirdpartyoffers.juno.com/TGL3141/5ceb49d9e56fe49d939d6st03vuc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Option to control implicit casting

[John McMahon]

On 11/04/2019 00:28, Joshua Thomas Wise wrote:

This is not enough. Because of implicit casting, an integer (a precise value) 
could be passed through a series of operations that outputs an integer, 
satisfying the check constraint, but it still could’ve been converted to a 
floating point (imprecise value) at some intermediate step due to integer 
overflow, potentially resulting in an incorrect answer. There’s currently no 
way to guarantee that a value will always yield precise results in SQLite3.

Here’s an example:
CREATE TABLE squares (
x INTEGER NOT NULL DEFAULT 0 CHECK (typeof(x) = 'integer'),
y INTEGER NOT NULL DEFAULT 0 CHECK (typeof(y) = 'integer')
);
INSERT INTO squares VALUES (1 << 40, 1 << 40);
SELECT x * y & ~1 AS even_numbered_area FROM squares;


Suggestion: "Don't Do That", use database purely as a storage medium.

If the Integer values you want to store are greater than the 64bit 
values accepted by SQLite then store them as BLOBs.


If the mathematical manipulations you wish to apply in your queries are 
beyond the scope of the built-in functions, then just return the stored 
values to your external programming environment and manipulate them there.


You would seem to be working in an edge case environment, in which case 
it is your responsibility to make the adjustments.




In many cases, it’s better for the above SELECT statement to return an error or 
NULL, but currently it gives an incorrect answer. Checking its type won’t help 
either, because it does indeed return an integer.



On Apr 9, 2019, at 2:06 PM, James K. Lowden  wrote:

On Mon, 8 Apr 2019 23:08:18 -0400
Joshua Thomas Wise  wrote:


I propose there should be a compile-time option to disable all
implicit casting done within the SQL virtual machine.


You can use SQLite in a "strict" way: write a CHECK constraint for
every numerical column.

Just don't do that for tables that are loaded by the .import comand.
As I reported here not long ago, .import rejects numeric literals.
Apparently, the value is inserted as a string and rejected, instead of
being converted to a number first.

--jkl
___
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



--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

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

[John McMahon]

On 10/04/2019 18:28, Kees Nuyt wrote:

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


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


By this above, I meant the pre-compiled CLI.



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


Thank you Kees, that didn't work (as noted by Luuk, Graham and Shawn) 
but it got me looking in the Pragma document and this did:

sqlite> pragma compile_options:
compile_options

COMPILER=gcc-5.2.0
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS5
ENABLE_JSON1
ENABLE_RTREE
ENABLE_STMTVTAB
ENABLE_UNKNOWN_SQL_FUNCTION
THREADSAFE=0
sqlite>

also this:
sqlite> select * from pragma_compile_options;
compile_options

COMPILER=gcc-5.2.0
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
...
same thing, different method.




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


I don't think so.



--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

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

[John McMahon]

Hi,

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


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




John

--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

[sqlite] Are the 'sqlite3_snprintf()' family protected against SQL injection?

[John Smith]

For example, if I write function like:

void CreateSQL_SetName( char* buffer, int size, const char* szName, const 
char* szCondition)
{
sqlite3_snprintf( size, buffer, "UPDATE my_table SET name='%s' WHERE 
%s", szName, szCondition);
}

Does SQLite 'sqlite3_snprintf()' processes the strings 'szName' and 
'szCondition' to verify they do not contain escape sequence that may inject 
other SQL statements into this statement?

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

[sqlite] Is it possible to use sqlite online-backup system in an ongoing manner?

[John Smith]

I am working with IN-MEMORY database.
When my program starts I load data from file-system DB into my IN-MEMORY DB.
All other SQL operations are performed directly on my IN-MEMORY database.
This is in order to keep performance high.

However, I have a requirement that my original file-system database will remain 
updated with the program modifications every few seconds.
My idea to implement this was to have a worker-thread that will work as follows:

void WorkerThread()
{
// Initialize SQLite online-backup ONCE:
p = sqlite3_backup_init(...);

loop{
Sleep(5 seconds);

 // Save only intermediate changes (?)
 sqlite3_backup_step(p, -1); // Backup all modifications from last time

}  while( program is running);

// No program is exiting...
// Release object resources
sqlite3_backup_finish(p);
}

The problem is that I see that first time around all data is saved, but all 
follwing calls to 'sqlite3_backup_step()' do not save anything.

My question:
Is there a way to use this online-backup system in an incremental way: that it 
will save only difference from last time BUT ALL the difference from last time?

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

[sqlite] Can I assign negative value to the INTEGER PRIMARY KEY column?

[John Smith]

Hi,


I read in SQLite documentation that if I define column of type INTEGER PRIMARY 
KEY then this column will become an alias to SQLite internal 64-bit integer 
index that uniquely identifies the row (hence ‘rowid’).

I also read that the initial default value that will be used for such column is 
1.


My question:

I am migrating data from a previous schema to my new schema, which wants to use 
this INTEGER PRIMARY KEY capability.

The thing is that in my old schema the integer indexes, which needs to be 
mapped to the new INTEGER PRIMARY KEY column, starts with value -2 (minus two).

The values are unique and are going up, but always start at (-2).

Since my schema contains many relations I do not wish to modify these indexes.

My question – if I copy the indexes values as-is, so some are negative, will my 
INTEGER PRIMARY KEY column still be an alias to the SQLite internal ‘rowid’ 
column, or will such values break this alias connection?

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

[sqlite] Building SQLite DLL with Visual Studio 2015

[John Smith]

Hi,


I need to build a data-layer DLL for a large project.

My project is 64-bit built with Visual-Studio 2015.

I want to ask about  what would be the preferred way to build SQLite:

1. Build SQLite code as a separate DLL and use it from my data-layer DLL,

2. Use the ready-built binary of 64-bit SQLite DLL for Windows 
(sqlite-dll-win64-x64-326.zip),

3. Or, build my data-layer code with SQLite code as a single DLL.

Thanks in advanced,

John


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

Re: [sqlite] Simple way to import GPX file?

[John McMahon]

I know I am coming to this a couple of weeks late, but I have been doing 
this for several years and thought I would add my 2 cents worth. 
Probably too late for OP, but may be useful for someone else later.


I used gpsbabel like this initially (in a JPSoft 4nt/tcmd script),

   gpsbabel -i gpx ^
-f %fname ^
-x nuketypes,tracks,routes ^
-o xcsv,style=G7W-xcsv.style ^
-F "%@name[%fname].csv"

%fname - variable containing source file name
%@name[ ... ] - function to extract basename from full filename

with this style sheet to generate .csv files in my desired format.

# gpsbabel XCSV style file
#
# Format:   G7toWin csv format
# Author:   John McMahon
#   Date:   2005may24
# Update:   2006jun02jmcm
#

DESCRIPTION G7toWin csv file format
#
# FILE LAYOUT DEFINITIONS
#

FIELD_DELIMITER   COMMA
RECORD_DELIMITER  NEWLINE
BADCHARS  COMMA
SHORTLEN  10

PROLOGUE Version 2:CSV
PROLOGUE Datum:,WGS-84
PROLOGUE ZoneOffset:,0.00
PROLOGUE 
"Type","Name","Lat","Long","Month\#","Day#","Year","Hour","Min","Sec","Comment","Symbol#","SymbolColor","SymbolDisplay","Altitude 
(Meters)","Depth (Meters)","Ref Dist","Ref units"


#
# INDIVIDUAL DATA FIELDS, IN ORDER OF APPEARANCE
#

IFIELD   CONSTANT, "W", "%s"# "Type",
IFIELD   SHORTNAME, "", "%s"# "Name",
IFIELD   LAT_DECIMAL,   "", "%f"# "Lat",
IFIELD   LON_DECIMAL,   "", "%f"# "Long",
IFIELD   IGNORE,"", "%s"# "Month#",
IFIELD   IGNORE,"", "%s"# "Day#",
IFIELD   IGNORE,"", "%s"# "Year",
IFIELD   IGNORE,"", "%s"# "Hour",
IFIELD   IGNORE,"", "%s"# "Min",
IFIELD   IGNORE,"", "%s"# "Sec",
IFIELD   IGNORE,,   "", "%s"# "Comment",
IFIELD   IGNORE,"", "%s"# "Symbol#",
IFIELD   IGNORE,"", "%s"# "SymbolColor",
IFIELD   IGNORE,"", "%s"# "SymbolDisplay",
IFIELD   IGNORE,"", "%s"# "Altitude (Meters)",
IFIELD   IGNORE,"", "%s"# "Depth (Meters)",
IFIELD   IGNORE,"", "%s"# "Ref Dist",
IFIELD   IGNORE,"", "%s"# "Ref units"

However, I have recently replaced that with a perl script using the 
Geo::GPX module.


John


On 10/12/2018 10:17, no...@null.net wrote:

On Sun Dec 09, 2018 at 03:16:15PM -0700, Winfried wrote:

Good call, thank you.

For others' benefit:

1. Copy the file, open the copy in a text editor, use a regex to turn the
data into tab-separated columns


If you are running some kind of unix-like environment this is something
Perl can be quite useful for:

 grep '^(.*)!$1\t$2\t$3!' \
> waypoints.tsv


2. Create a new file, and create the table:
sqlite3 waypoints.sqlite

sqlite> CREATE TABLE waypoints (name text, latitude text, longitude text, id
INTEGER PRIMARY KEY);

3. Import data:
sqlite> .separator "\t"
sqlite> .import waypoints.tsv waypoints
select * from waypoints where id=1;




--
Regards
   John McMahon
j...@jspect.fastmail.com.au
04 2933 4203

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

Re: [sqlite] function named geopolyCosine is a misnomer

[John G]

If I missed i tin earlier posts, sorry. Is there any documentation on the
geopoly extension? With possible uses or examples?

John

On Thu, 29 Nov 2018 at 14:39, Richard Hipp  wrote:

> On 11/29/18, Thomas Kurz  wrote:
> > Could it be that the one angle is north-based, the other one east-based?
>
> Ha Ha.  No, Graham is right.  I started out writing a Cosine function,
> then I switched it over to be a Sine function but failed to change the
> name.  A rename has now been committed to trunk, is in the latest
> "prerelease snapshot", and will appear in the next official release
> (which will also be the first official release that includes the new
> capability).
>
> --
> 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

[sqlite] How to round to an Integer

[John Harney]

Recently figured this out.  Seems to work fine

trim(trim(round(1.111,0),'0'),'.')   = 1






Aviso de Privacidad y Confidencialidad // Privacy and Confidentiality Notice // 
Avis de confidentialit?: https://privacy.grupobimbo.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Filtering groups by non-grouped field.

[John Found]

On Fri, 12 Oct 2018 11:12:17 +0200
Clemens Ladisch  wrote:

> I wrote:
> > But you need to find some aggregate function that can do the filtering.
> 
> HAVING SUM(b = ?1)
> 
> (In a database with proper booleans: "HAVING SUM(CASE WHEN b = ? THEN 1 END) 
> > 0")

Hey, this really looks great. Thanks. Also, it seems max(b = ?1) will do the 
trick as well as count(b = ?1)

And here another question appears. What is more efficient? 
At first glance, max() looks better, because it does not need to scan all 
values from the group. 
But is it the case in SQLite implementation?

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


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

Re: [sqlite] Filtering groups by non-grouped field.

[John Found]

On Fri, 12 Oct 2018 08:27:10 +0200
Clemens Ladisch  wrote:

> John Found wrote:
> > i.e. how to select only the groups that contain
> > some value in the set of values in a column not
> > specified in group by clause.
> >
> > select
> >   (select group_concat(b) from t t1 where t1.a = t2.a) as list
> > from t t2
> > where b = ?1;
> 
> Similarly:
> 
>   select
> group_concat(b) as list
>   from t
>   where a in (select a
>   from t
>   where b = ?1)
>   group by a;
> 
> But you will not be able to avoid the subquery: the filter has to
> include all other rows of the group with matching b, and after the
> group_concat(), the result is no longer in a form useful for lookups.
> (And doing the filter after the grouping is probably not efficient.)

Hm, is sounds strange because when HAVING clause is processed, 
the aggregate functions should not be processed yet (for a performance 
reasons) i.e. the query still has access to all values from the field b
and theoretically should be able to search these values the same way
it searches them on executing min() or max() aggregate functions.

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


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

[sqlite] Filtering groups by non-grouped field.

[John Found]

The following code does not work, but gives an idea what I want to do:

create table t (a, b);

select 
  group_concat(b) as list 
from t 
group by a
having ?1 in (list);

i.e. how to select only the groups that contain
some value in the set of values in a column not
specified in group by clause.

The only way I was able to do it is by subquery.
Something like this:

select 
  (select group_concat(b) from t t1 where t1.a = t2.a) as list
from t t2
where b = ?1;


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

Re: [sqlite] Docs typo JSON1 @ 4.13

[John G]

In that same JSON page, in 1. Overview the text mentions '12 of 14 SQL
functions'  but the listing shows different numbers - 13 numbered items in
the first section,  2 in the second, numbered 1 - 15.

Should that be "twelve of the *fifteen* SQL functions" or "*thirteen* of
the *fifteen* SQL functions"?

Cheers
JG

On 19 September 2018 at 11:16, Peter Johnson 
wrote:

> Hi,
>
> The JSON1 docs at https://www.sqlite.org/json1.html have a minor typo:
>
> Section 4.13. The json_each() and json_tree() table-valued functions
>
> atom ANY, -- value for primitive types, null for array & object
> > id INTEGER -- integer ID for this element
> > parent INTEGER, -- integer ID for the parent of this element
>
>
> The "id INTEGER" column definition is missing a trailing comma.
>
> Cheers,
> -P
> ___
> 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] [EXTERNAL] The "natural" order of the query results.

[John Found]

On Mon, 17 Sep 2018 06:02:37 +
Hick Gunter  wrote:

> SQLite handles GROUP BY and ORDER BY in basically the same way. If there is 
> an apropirate index, then it will use this index and the rows will be 
> returned in visitation order of this index. If, for exmaple by adding a new 
> index or even an upgrade of the Query Planner, a different execution plan is 
> constructed, then the order of the returned rows will "change".

Yes, of course, but you forgot about INDEXED BY clause. It will force using 
particular index. So, the query planner will always use exactly this index, 
regardless of how optimal it is and
as long as GROUP BY and ORDER BY are working the same way, this gives some 
guarantee for the ordering without ORDER BY clause. Or my logic is wrong? 

Anyway, read my second response to the DRHs example. IMHO, there is some kind 
of misbehavior with the ORDER BY planning when ordering descending.

> 
> Please note that GROUP BY id,o1,o2 and ORDER BY o1,o2 are DIFFERENT 
> expressions and require reordering, which is the reason for an additional 
> BTree step.
> 
> If you need the rows to be returned in a specific order, then you must say so 
> explicitly with an ORDER BY clause; otherwise, SQLite is free to return rows 
> in any order that happens to be convenient for the DB Engine. Relying on the 
> "natural" order is a common way of creating code that breaks unexpectedly.
> 
> Similarly, if you need the result columns to have certain names, you must 
> provide these via AS clauses.
> 
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von John Found
> Gesendet: Sonntag, 16. September 2018 10:30
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] [sqlite] The "natural" order of the query results.
> 
> Is there some relation between the indexes used in the query, the GROUP BY 
> fields used and the order of the result rows, when no "ORDER BY" clause is 
> used?
> 
> I am asking, because I noticed, that on some queries, when I am using "ORDER 
> BY" the query always use temporary b-tree for ordering, but by including the 
> needed fields in the "GROUP BY" clause and removing the ORDER BY clause, the 
> query returns the rows in the proper order without temp b-tree.
> 
> So, is it safe to use this implicit ordering, or this behavior can be changed 
> in the future versions of SQLite?
> 
> Here is an example:
> 
> create table A (
>   id integer primary key autoincrement,
>   o1 integer,
>   o2 integer
> );
> 
> create table B (
>   Aid integer references A(id),
>   data text
> );
> 
> create index idxA on A(o1 desc, o2 desc);
> 
> insert into A(o1, o2) values (1, 100), (2, 50), (3, 200), (5, 300); insert 
> into B(Aid, data) values (1, "b"), (1, "a"), (2, "c"), (2, "d"), (2, "e"), 
> (3, "f"), (3, "g");
> 
> -- Always uses temp b-tree for order by
> select
>   group_concat(B.data), o1, o2
> from
>   A
> left join
>   B on A.id = B.Aid
> group by
>   A.id
> order by
>   A.o1 desc, A.o2 desc;
> 
> explain query plan:
> id  parent  notused detail
> 8   0   0   SCAN TABLE A
> 19  0   0   SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)
> 58  0   0   USE TEMP B-TREE FOR ORDER BY
> 
> 
> -- This one returns the rows in the needed order without ORDER BY select
>   group_concat(B.data), o1, o2
> from
>   A indexed by idxA
> left join B on A.id = B.Aid
> group by A.id, A.o1, A.o2;
> 
> explain query plan:
> id  parent  notused detail
> 7   0   0   SCAN TABLE A USING COVERING INDEX idxA
> 18  0   0   SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)
> 
> 
> -- But if I add ORDER BY it still begins to use temp b-tree
> -- regardless that it does not change the order.
> select
>   group_concat(B.data), o1, o2
> from
>   A indexed by idxA
> left join B on A.id = B.Aid
> group by A.id, A.o1, A.o2
> order by A.o1 desc, A.o2 desc;
> 
> explain query plan:
> 8   0   0   SCAN TABLE A
> 19  0   0   SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)
> 60  0   0   USE TEMP B-TREE FOR ORDER BY
> 
> 
> All the above queries, returns the same result rows in the same order:
> 
> group_concat(B.data)  o1   o2
> NULL   5   300
> f,g3   200
> c,d,e  2   50
> a,b1   100
> 
> 
> 
> 
> --
> John Found 
> ___
> sqlite-users maili

Re: [sqlite] The "natural" order of the query results.

[John Found]

On Sun, 16 Sep 2018 10:59:31 -0400
Richard Hipp  wrote:

> On 9/16/18, John Found  wrote:
> >
> > Is it means that in every query that uses GROUP BY and ORDER BY
> > simultaneously, one of the operations will always be provided by using
> > temporary b-tree?
> >
> 
> no.
> 
> CREATE TABLE t1(a,b,c, PRIMARY KEY(a,b)) WITHOUT ROWID;
> explain query plan
> SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY a, b;
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org

BTW, using your example and modifying it in order the GROUP BY to be needed
I got some not obvious results:

drop table t1;
CREATE TABLE t1(a,b,c);
insert into t1 values (1, 2, 3), (1,2,1), (1, 2, 2), (2, 2, 1), (2, 1, 2);
create index idxT1 on t1(a desc,b desc);

-- This one orders ascending by using descending index. Great.
explain query plan
SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY a, b;

id  parent  notused detail
8   0   0   SCAN TABLE t1 USING INDEX idxT1

SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY a, b;

a   b   sum(c)
1   2   6
2   1   2
2   2   1


-- The following can't order descending by using descending index... :?
explain query plan
SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY a desc, b desc;

id  parent  notused detail
8   0   0   SCAN TABLE t1 USING INDEX idxT1
41  0   0   USE TEMP B-TREE FOR ORDER BY

SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY a desc, b desc;

a   b   sum(c)
2   2   1
2   1   2
1   2   6


-- But this one orders properly descending, by using descending index, but 
without ORDER BY clause.
explain query plan
SELECT a, b, sum(c) FROM t1 GROUP BY a, b;

id  parent  notused detail
7   0   0   SCAN TABLE t1 USING INDEX idxT1

SELECT a, b, sum(c) FROM t1 GROUP BY a, b;

a   b   sum(c)
2   2   1
2   1   2
1   2   6


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

Re: [sqlite] The "natural" order of the query results.

[John Found]

I know that the answer is "no", but in the below example, group by clause
is meaningless, because (a,b) is primary key and there is no two rows with equal
(a, b) that to be grouped. 

Please, comment my example from the first email in the thread. 
Is it possible to make this query to group by and order by simultaneously
without using temp b-tree?

On Sun, 16 Sep 2018 10:59:31 -0400
Richard Hipp  wrote:

> On 9/16/18, John Found  wrote:
> >
> > Is it means that in every query that uses GROUP BY and ORDER BY
> > simultaneously, one of the operations will always be provided by using
> > temporary b-tree?
> >
> 
> no.
> 
> CREATE TABLE t1(a,b,c, PRIMARY KEY(a,b)) WITHOUT ROWID;
> explain query plan
> SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY a, b;
> 
> -- 
> 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


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

Re: [sqlite] The "natural" order of the query results.

[John Found]

On Sun, 16 Sep 2018 13:30:55 +0100
Simon Slavin  wrote:

> On 16 Sep 2018, at 9:29am, John Found  wrote:
> 
> > Is there some relation between the indexes used in the query, the GROUP BY 
> > fields used 
> > and the order of the result rows, when no "ORDER BY" clause is used?
> 
> When you ask for GROUP BY, SQLite internally does the same sort of thing as 
> it does when you ask for ORDER BY.  And if you have a useful index then it 
> will use that index.
> 
> > [...]. So, is it safe to use this implicit ordering, or this behavior can 
> > be changed in the future versions of SQLite?
> 
> It is not safe.  There is nothing in the documentation that says that groups 
> will be returned in COLLATE order.  As you suggest, a future version of 
> SQLite may choose not to do so.

Is it means that in every query that uses GROUP BY and ORDER BY simultaneously, 
one of the operations will always be provided by using temporary b-tree? 

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


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

Re: [sqlite] The "natural" order of the query results.

[John Found]

On Sun, 16 Sep 2018 19:27:40 +1000
Barry Smith  wrote:

> Without an order by, sqlite can return the rows in any order it pleases. 
> Likely whatever consumes the least resources. Although unlikely given your 
> indices, it might be possible - for instance if some future 
> micro-optimisation finds that it's quicker to read the index in reverse, then 
> sqlite would give things in the opposite order. If you leave out a necessary 
> order by you are very much exposing yourself to internal changes. So much so 
> that there is a pragma reverse_unordered_selects 
> (https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects) you can 
> use to specifically find if your app makes invalid assumptions about order.


Well, I was sure that the first answer will be this. But I asked the question 
in hope to get 
some deeper information about the relations between order by, group by and the 
indices used.

It is not a coincidence that in the example case I used "INDEXED BY" clause.

With PRAGMA reverse_unordered_selects=1; all the queries in the example return 
the rows in exactly the same proper order. 
I tryed to insert the values in random order in order to avoid the primary key 
influence on the ordering and the result 
is still the same.

IMO, this somehow proves that I can use such implicit ordering in this very 
case. Or not?

> 
> You might have found a missed optimisation opportunity here (although there's 
> always the tradeoff of library size & cycles to optimise vs execution cycles 
> saved to consider).
> 
> > On 16 Sep 2018, at 6:29 pm, John Found  wrote:
> > 
> > Is there some relation between the indexes used in the query, the GROUP BY 
> > fields used 
> > and the order of the result rows, when no "ORDER BY" clause is used?
> > 
> > I am asking, because I noticed, that on some queries, when I am using 
> > "ORDER BY" the query always 
> > use temporary b-tree for ordering, but by including the needed fields in 
> > the "GROUP BY" clause 
> > and removing the ORDER BY clause, the query returns the rows in the proper 
> > order without temp b-tree.
> > 
> > So, is it safe to use this implicit ordering, or this behavior can be 
> > changed in the future versions of SQLite?
> > 
> > Here is an example:
> > 
> > create table A (
> >  id integer primary key autoincrement,
> >  o1 integer,
> >  o2 integer
> > );
> > 
> > create table B (
> >  Aid integer references A(id),
> >  data text
> > );
> > 
> > create index idxA on A(o1 desc, o2 desc);
> > 
> > insert into A(o1, o2) values (1, 100), (2, 50), (3, 200), (5, 300);
> > insert into B(Aid, data) values (1, "b"), (1, "a"), (2, "c"), (2, "d"), (2, 
> > "e"), (3, "f"), (3, "g");
> > 
> > -- Always uses temp b-tree for order by
> > select 
> >  group_concat(B.data), o1, o2
> > from 
> >  A
> > left join 
> >  B on A.id = B.Aid
> > group by 
> >  A.id
> > order by 
> >  A.o1 desc, A.o2 desc;
> > 
> > explain query plan:
> > idparentnotuseddetail
> > 800SCAN TABLE A
> > 1900SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)
> > 5800USE TEMP B-TREE FOR ORDER BY
> > 
> > 
> > -- This one returns the rows in the needed order without ORDER BY
> > select 
> >  group_concat(B.data), o1, o2
> > from 
> >  A indexed by idxA
> > left join B on A.id = B.Aid
> > group by A.id, A.o1, A.o2;
> > 
> > explain query plan:
> > idparentnotuseddetail
> > 700SCAN TABLE A USING COVERING INDEX idxA
> > 1800SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)
> > 
> > 
> > -- But if I add ORDER BY it still begins to use temp b-tree 
> > -- regardless that it does not change the order.
> > select 
> >  group_concat(B.data), o1, o2
> > from 
> >  A indexed by idxA
> > left join B on A.id = B.Aid
> > group by A.id, A.o1, A.o2
> > order by A.o1 desc, A.o2 desc;
> > 
> > explain query plan:
> > 800SCAN TABLE A
> > 1900SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)
> > 6000USE TEMP B-TREE FOR ORDER BY
> > 
> > 
> > All the above queries, returns the same result rows in the same order:
> > 
> > group_concat(B.data)  o1   o2
> > NULL   5   300
> > f,g3   200
> > c,d,e  2   50
> > a,b1   100
> > 
> > 
> > 
> >
> > -- 
> > John Found 


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

[sqlite] The "natural" order of the query results.

[John Found]

Is there some relation between the indexes used in the query, the GROUP BY 
fields used 
and the order of the result rows, when no "ORDER BY" clause is used?

I am asking, because I noticed, that on some queries, when I am using "ORDER 
BY" the query always 
use temporary b-tree for ordering, but by including the needed fields in the 
"GROUP BY" clause 
and removing the ORDER BY clause, the query returns the rows in the proper 
order without temp b-tree.

So, is it safe to use this implicit ordering, or this behavior can be changed 
in the future versions of SQLite?

Here is an example:

create table A (
  id integer primary key autoincrement,
  o1 integer,
  o2 integer
);

create table B (
  Aid integer references A(id),
  data text
);

create index idxA on A(o1 desc, o2 desc);

insert into A(o1, o2) values (1, 100), (2, 50), (3, 200), (5, 300);
insert into B(Aid, data) values (1, "b"), (1, "a"), (2, "c"), (2, "d"), (2, 
"e"), (3, "f"), (3, "g");

-- Always uses temp b-tree for order by
select 
  group_concat(B.data), o1, o2
from 
  A
left join 
  B on A.id = B.Aid
group by 
  A.id
order by 
  A.o1 desc, A.o2 desc;

explain query plan:
id  parent  notused detail
8   0   0   SCAN TABLE A
19  0   0   SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)
58  0   0   USE TEMP B-TREE FOR ORDER BY


-- This one returns the rows in the needed order without ORDER BY
select 
  group_concat(B.data), o1, o2
from 
  A indexed by idxA
left join B on A.id = B.Aid
group by A.id, A.o1, A.o2;

explain query plan:
id  parent  notused detail
7   0   0   SCAN TABLE A USING COVERING INDEX idxA
18  0   0   SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)


-- But if I add ORDER BY it still begins to use temp b-tree 
-- regardless that it does not change the order.
select 
  group_concat(B.data), o1, o2
from 
  A indexed by idxA
left join B on A.id = B.Aid
group by A.id, A.o1, A.o2
order by A.o1 desc, A.o2 desc;

explain query plan:
8   0   0   SCAN TABLE A
19  0   0   SEARCH TABLE B USING AUTOMATIC COVERING INDEX (Aid=?)
60  0   0   USE TEMP B-TREE FOR ORDER BY


All the above queries, returns the same result rows in the same order:

group_concat(B.data)  o1   o2
NULL   5   300
f,g    3   200
c,d,e  2   50
a,b1   100




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

Re: [sqlite] Is there permanent link to the latest SQLite amalgamation source?

[John Found]

On Wed, 5 Sep 2018 10:42:04 +0200
"E.Pasma"  wrote:

> John Found wrote:
> > In order to write an autoupdater, I need to download the latest SQLite 
> > amalgamation.
> > Is there a permanent link to the subject, or the only way is to parse the 
> > download page
> > for links to "sqlite-amalgamation-*.zip" or to build it from the fossil 
> > checkout?
> 
> The apsw installer, setup.py, can parse the download page. This has likely 
> been tested very well:
> 
> # work out the version
> if self.version is None:
> write("  Getting download page to work out current SQLite 
> version")
> page=self.download("https://sqlite.org/download.html;, text=True, 
> checksum=False)
> 
> match=re.search(r'sqlite-amalgamation-3([0-9][0-9])([0-9][0-9])([0-9][0-9])\.zip',
>  page)
> if match:
> self.version="3.%d.%d.%d" % tuple([int(match.group(n)) for n 
> in range(1,4)])
> if self.version.endswith(".0"):
> self.version=self.version[:-len(".0")]
> else:
> write("Unable to determine current SQLite version.  Use 
> --version=VERSION", sys.stderr)
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Well, IMHO every solution that parses the html page in order to extract the 
links is bad solution by definition.
I would prefer another workaround - compiling from source - much more reliable 
IMHO:

echo "Downloading latest SQLite source archive..."
{ wget -q -O - 
https://www.sqlite.org/cgi/src/tarball/sqlite3.tar.gz?r=release | tar -xz 2> 
/dev/null; } || { echo >&2 "Error: Can't download SQLite sources."; exit 2;}

cd ./sqlite3

echo "Building the amalgamation sqlite3.c ..."
./configure > /dev/null 2>&1 || { echo >&2 "Error: Can't configure SQLite 
sources"; exit 4; }
make sqlite3.c > /dev/null 2>&1 || { echo >&2 "Error: Can't make 
sqlite3.c"; exit 4; }

cd ..
cp ./sqlite3/sqlite3.c ./
rm -rf ./sqlite3/ &

Unfortunately this process is pretty slow, especially on my low-end machine... 
That is why I asked about permanent link that needs much less CPU cycles and 
network bandwidth.

Regards

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

[sqlite] Is there permanent link to the latest SQLite amalgamation source?

[John Found]

In order to write an autoupdater, I need to download the latest SQLite 
amalgamation.
Is there a permanent link to the subject, or the only way is to parse the 
download page
for links to "sqlite-amalgamation-*.zip" or to build it from the fossil 
checkout?

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

Re: [sqlite] Problems with compiling SQLite for MUSL.

[John Found]

On Sun, 02 Sep 2018 22:25:16 -0600
"Keith Medcalf"  wrote:

> Interesting ... Sounds like the optimizer in the compiler is broken ... 
> unless someone has ideas about how to debug this.  Can you compile with no 
> optimization and SQLITE_DEBUG defined and see what happens?  Though, to me it 
> sounds like the compiler and/or the optimizer is just broken (or perhaps the 
> replacement standard library is broken and does not return properly aligned 
> memory allocations) ... 

Well, the problem is solved. The crashes was caused because gcc compiled sqlite 
with requirement for stack alignment on 16 bytes,
regardles of that it was compiled to 32bit code. On the other hand, my code 
aligns the stack on 4 bytes as an usual 32bit program.

The option -mpreferred-stack-boundary=2 fixed the issue.

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

Re: [sqlite] Problems with compiling SQLite for MUSL.

[John Found]

On Sun, 02 Sep 2018 14:18:50 -0600
"Keith Medcalf"  wrote:

> 
> This is a compiler issue and has nothing to do with SQLite3.  Anything you 
> compiled using that compiler would exhibit the same problems ...

Well, I was almost sure, but as long as here I can find people understanding 
gcc and I am compiling sqlite after all, 
considered a good place to ask. ;)

> However, that the compiler does not automatically use the correct alignment 
> for the cpu architecture in use is puzzling, since this is something that one 
> would normally expect to be correct by default.  You did not say what 
> Operating System or CPU you are using ... this is probably important 
> information.  As is likely the version of GCC (use gcc -v or musl-gcc -v to 
> get the compiler's compile configuration).

The OS is 64bit Linux (Manjaro/Arch), the CPU is AMD A4-1200 (supports almost 
all extensions), gcc version is 8.2.0;

BTW, compiling with -O1 sometimes produces working result (but pretty slow) 
which only entagles the puzzle.

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

[sqlite] Problems with compiling SQLite for MUSL.

[John Found]

I am trying to compile SQLite with 32bit MUSL, but probably mess some compiler 
options.

The command I am using is:

musl-gcc -O3 -shared -fno-stack-protector -DSQLITE_DEFAULT_MEMSTATUS=0 
-DSQLITE_LIKE_DOESNT_MATCH_BLOBS -DSQLITE_MAX_EXPR_DEPTH=0 
-DSQLITE_OMIT_DEPRECATED -D SQLITE_OMIT_PROGRESS_CALLBACK 
-DSQLITE_OMIT_SHARED_CACHE -DSQLITE_ENABLE_STAT4 -DSQLITE_ENABLE_FTS5 
-DSQLITE_OMIT_COMPLETE -DSQLITE_OMIT_GET_TABLE -DSQLITE_OMIT_UTF16 
-DSQLITE_THREADSAFE=1 ./sqlite3.c -o ./libsqlite3.so

The code compiles without errors. The problem is that the code is compiled with 
SSE instructions, but in runtime crashes on reads and writes to unaligned 
memory addresses. In most cases the crashes are random, but mostly in 
sqlite3_prepare_v2 function;

So, the question, how to make sqlite to compile with properly aligned variables 
in order to not crash. Is there some option about this (my gcc and generally 
C/C++ skills are pretty low) or I somehow messed the options elsewhere. Then 
what to check?


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

Re: [sqlite] sqlite on IBM z/OS Unix

[John McKown]

On Thu, Aug 30, 2018 at 11:12 AM Don V Nielsen 
wrote:

> "Having successfully ported sqlite to z/OS Unix as a 32 bit app"
>
> Totally Awesome! Do I have a solution? No. But I'll bet John McKown will. I
> believe he is a guru with the mainframe.
>

I don't know about being a "guru". Personally, all my HLASM for UNIX
program is LE based (starts up via a CEEENTRY macro) just so that I can use
C language subroutines. This doesn't seem to have any real drawbacks, other
than some learning and recoding the startup/return stuff. The parameter
passing is the same and an HLASM LE routine can do anything that a non-LE
can do, as best as I know.

If anyone is curious about a z/OS UNIX program written in LE HLASM, here:
https://github.com/JohnArchieMckown/utilities-1/blob/master/lsenq.s

This program also shows how to get to the UNIX arguments from the shell
command line. It is not as simple as in z/OS batch. More akin to a TSO
command processor, albeit different.



>
> It is not SqlLite. It is that communication mechanism between the non-LE
> program calling into the LE environment. Are you saying the LE is loading
> and not unloading. I would guess that it is, and it is that which is
> closing the connection. I'm sorry, but I'm 10+ years past working with
> mainframes. But I recall having to do something special when calling
> LE-Cobol from assembler. There was something that needed to be communicated
> to say "Get up and stay up until I tell you to close", otherwise, you are
> continually loading and unloaded LE with every call.
>
> Wish I could be more helpful,
> dvn
>
> On Wed, Aug 29, 2018 at 5:26 PM David Jackson 
> wrote:
>
> > Having successfully ported sqlite to z/OS Unix as a 32 bit app, I am now
> > looking at a c program to make SQL calls to this.
> > Starting with an Assembler routine that runs within z/OS (not Unix),
> which
> > is not LE (Language Environment)enabled, we then call a c routine
> (numerous
> > times) that is LE enabled. That is all working fine and making good SQL
> > calls to sqlite. the c program then returns back to the upper assembler
> > calling program. The problem is that the c routines is opening, issuing
> the
> > SQL and closing on each invocation.
> >
> > Now this may be a dumb question, so apologies up front.
> > Is there any way that the c program can open the sqlite db initially on
> the
> > first call and keep it open after it returns back to the calling program
> > until a final call at which point it will issue the sqlite3_close.
> >
> > Again - sorry if this was a dumb question.
> >
> > Thanks
> > ___
> > 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
>


-- 
People who frustrate us will be around for as long as we need them.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Common index for multiple databases

[John R. Sowden]

I have the xbase type of databases down tight, having been using them 
since I bought my copy of dBASE II from George Tate of Ashton-Tate at a 
West Coast Computer Faire in 1981.  I have been writing applications for 
my alarm company, now through Foxpro 2.6 in DOS.  I understand what an 
index is.  My concern in using Sqlite is since the index is embedded 
into the database file with various tables, if I am running multiple 
Sqlite database files, how do I use a common index for the different 
database files.  I won't provide technicians with accounts receivable 
databases, etc.


My thinking is along the line of all mission critical clocks take their 
accuracy from the US Naval Observatory in Fort Collins, CO, instead of 
thousands of free running clocks, each with what it thinks is the 
correct time.


John

On 08/03/2018 02:48 AM, Ling, Andy wrote:

another point that I did not make clear. The accounting programs are not 
associated with the technical programs, different people, different security 
access. The tech databases and programs are in portable computers that go out 
in the field, but not the accounting, etc. There indexes would have to be 
updated when the computers are back at the office.

I have a feeling that what the OP is calling an index isn’t really. I think 
what he is talking about is the list of customer IDs.
So customer data can get updated “back at the office” and when the portable 
computers get back they need to be updated
with the changes.

In sqlite terms, an index is a sorted list of the data in one or more columns 
of a table to help speed up access to the data in that table.
Once defined, the index is automatically updated by sqlite as the data in the 
table is changed.

Perhaps we could get some clarification from the OP about what is actually 
wanted.

Regards

Andy Ling





**
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If 
you are not the addressee of this message, you may not copy, use or deliver 
this message to anyone. In such event, you should destroy the message and 
kindly notify the sender by reply e-mail. It is understood that opinions or 
conclusions that do not relate to the official business of the company are 
neither given nor endorsed by the company. Thank 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] Common index for multiple databases

[John R. Sowden]

another point that I did not make clear.  The accounting programs are 
not associated with the technical programs, different people, different 
security access.  The tech databases and programs are in portable 
computers that go out in the field, but not the accounting, etc.  There 
indexes would have to be updated when the computers are back at the office.


John


On 08/02/2018 11:33 AM, Igor Korot wrote:

Hi,

On Thu, Aug 2, 2018 at 1:27 PM, John R. Sowden
 wrote:

I made a mistake.  I should have said table, not database.  My concern is if
I have 4 databases each with tables associated with a particular use, like
accounting, technical, etc., which may reside on different computers, how do
I keep the index in each database file current.  I assume that I have an
external database with the account number field, and its index that each
database connects to to "refresh" its account number index from the external
index.  Otherwise if the table with the accounting index is modified, the
tech table and its index would have to communicate with the master in order
to stay current.

Why do you need 4 databases in the first place?
If you client is designed to access all 4 databases then all tables
should be in 1 DB file.

Thank you.


I do this now because I have 1 account number index and the various foxpro
databases (tables) all open that one index when each is used.

John


On 08/02/2018 10:31 AM, Simon Slavin wrote:

On 2 Aug 2018, at 6:11pm, John R. Sowden 
wrote:


I do not want these databases to all reside in one sqlite file.  How do I
index each database on this customer account number when each database and
associated index are in separate files?  Is this what seems to be referred
to as an external file?  I assume that I would have to reindex each database
each time it is opened, since a record could have been edited, etc.

You have been misinformed.  In SQLite,

A) each table is stored one database file
B) each index indexes just one table
C) all indexes for a table are stored in the same file as that table.

An index is updated when its table is updated.  You never need to manually
reindex unless you changed the table structure or index structure.

It is normal to keep all tables related to one application in one big
database file.  So, for example, if you run a library you would normally
keep tables and indexes for books, borrowers, and current loans all in one
file.  And therefore all the indexes for those tables would be in that file
too.  SQLite is designed to handle things this way, and does it very
efficiently.

However, it is possible to keep different tables in different database
files.  So you might keep books (and all indexes on books) in one file, and
borrowers and current loans (and all the indexes on those tables) in another
file.

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

Re: [sqlite] Common index for multiple databases

[John R. Sowden]

I made a mistake.  I should have said table, not database.  My concern 
is if I have 4 databases each with tables associated with a particular 
use, like accounting, technical, etc., which may reside on different 
computers, how do I keep the index in each database file current.  I 
assume that I have an external database with the account number field, 
and its index that each database connects to to "refresh" its account 
number index from the external index.  Otherwise if the table with the 
accounting index is modified, the tech table and its index would have to 
communicate with the master in order to stay current.


I do this now because I have 1 account number index and the various 
foxpro databases (tables) all open that one index when each is used.


John


On 08/02/2018 10:31 AM, Simon Slavin wrote:

On 2 Aug 2018, at 6:11pm, John R. Sowden  wrote:


I do not want these databases to all reside in one sqlite file.  How do I index 
each database on this customer account number when each database and associated 
index are in separate files?  Is this what seems to be referred to as an 
external file?  I assume that I would have to reindex each database each time 
it is opened, since a record could have been edited, etc.

You have been misinformed.  In SQLite,

A) each table is stored one database file
B) each index indexes just one table
C) all indexes for a table are stored in the same file as that table.

An index is updated when its table is updated.  You never need to manually 
reindex unless you changed the table structure or index structure.

It is normal to keep all tables related to one application in one big database 
file.  So, for example, if you run a library you would normally keep tables and 
indexes for books, borrowers, and current loans all in one file.  And therefore 
all the indexes for those tables would be in that file too.  SQLite is designed 
to handle things this way, and does it very efficiently.

However, it is possible to keep different tables in different database files.  
So you might keep books (and all indexes on books) in one file, and borrowers 
and current loans (and all the indexes on those tables) in another file.

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] Common index for multiple databases

[John R. Sowden]

I have been reviewing sqlite for a couple of years, but still use 
foxpro.  I have a question regarding an index issue.


Currently I have several types of databases (in foxpro, one per file) 
that all point to an index of a common field, a customer account 
number.  The databases are for accounting, technical, general info 
lookup, etc.  \


I do not want these databases to all reside in one sqlite file.  How do 
I index each database on this customer account number when each database 
and associated index are in separate files?  Is this what seems to be 
referred to as an external file?  I assume that I would have to reindex 
each database each time it is opened, since a record could have been 
edited, etc.


tia,

John



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

Re: [sqlite] Very, very slow commits

[John Found]

SC, "category" COLLATE 
> > NOCASE ASC, "subCategory" COLLATE NOCASE ASC);
> > CREATE UNIQUE INDEX "Disruptions_Idx3" ON Disruptions ("version" 
> > COLLATE NOCASE ASC, "Disruption_id" COLLATE NOCASE ASC, "location" 
> > COLLATE NOCASE ASC);
> > CREATE INDEX Disruptions_Idx5 ON Disruptions ("status", 
> > "Disruption_id", "Severity", "levelOfInterest", "category", 
> > "subCategory", "version");
> >
> > We have checked that this schema is consistent across the databases.
> >
> > We're about to recreate the table to see if that makes a difference.
> >
> > Any help or advice welcomed.
> >
> > Thanks
> >
> > Rob
> > ___
> > 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


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

Re: [sqlite] Recursive references in subqueries

[John G]

Just to nitpick :

SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.

sqlite> with recursive count_down(v) as (
   ...> select 5
   ...> union all
   ...> select n - 1 from count_down where n > 0
   ...>   )
   ...>   select * from count_down;
Error: no such column: n


should have been  (replacing 'n' with 'v'):


sqlite> with recursive count_down(v) as (select 5 union all select v - 1
from count_down where v  > 0)
   ...>  select * from count_down;
5
4
3
2
1
0

John Gillespie

On 20 July 2018 at 14:14, Christian Duta 
wrote:

> R Smith-2 wrote
> > The query above is perfectly defined. In fact, it works in PostgreSQL.
> > PostgreSQL's manual also has a very nice explanation of how recursive
> > queries are evaluated.
>
> The way PostgreSQL handles recursive queries was one of my motivations to
> post about this.
> PostgreSQL allows for recursive references inside subqueries like the one I
> posted.
>
>
> R Smith-2 wrote
> >> That said, the query above can be simplified as follows:
> >>
> >>   with recursive count_down(v) as (
> >> select 5
> >> union all
> >> select n - 1 from count_down where n > 0
> >>   )
> >>   select * from count_down;
> >>
> >> which is what the OP has likely done.
> >
> > I think the OP tried something a bit more complex, and then tried to
> > reduce it to a simple example to post here, perhaps deceptively simple.
> > However, it's still possible that his actual complex query might be
> > refined into such a simpler form.
>
> You're right, the queries I have in mind are complex. And readability would
> greatly improve
> with a feature like recursive references inside subqueries.
> Recursive queries are often difficult to read anyway, so having a feature
> which improves
> readability is a *big* plus in my book.
>
> But: My questions have more of a technical background than a practical one.
> I try to figure out *why* SQLite decided to forbid a recursive reference be
> placed inside a subquery.
> Is there a technical reason? Or did the developer explicitly decide against
> recursive references inside
> subqueries because of the SQL standard?
>
> And of course: are there plans to allow for recursive references inside
> subqueries?
>
> Best regards,
> Christian Duta
>
>
>
> --
> 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] Mailing list shutting down...

[John Long]

On Wed, 2018-06-13 at 21:42 +1000, Gary R. Schmidt wrote:
> 
> 
> This is an increasing problem, and has been discussed on the Mailman 
> mailing list recently, you should join them and see what mitigation 
> strategies are available.

Well I'm sure he would like to, but subscriptions have probably been
suspended because of the attacks ;)

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

Re: [sqlite] Insert with an '

[John McKown]

Very good point. I think that everyone should do it that way. It is a bit
more work, but is vastly superior.

On Mon, Jun 11, 2018, 03:23 Olivier Mascia  wrote:

> > Le 11 juin 2018 à 10:07, Peter Nacken  a écrit :
> >
> > I try to insert email addresses into a table and get an error with
> addresses they have a single quotation mark ( na'm...@domain.ltd ).
>
> > Sorry I forgot I‘m using C#
>
> (Assuming: "create table T(E text);" for the following.)
>
> If you're building the text of your insert statement before executing it,
> you will have either to build the statement string as:
>
> insert into T values('na''m...@domain.tld');
> or
> insert into T values("na'm...@domain.ltd");
>
> Both of which you can test with the command-line sqlite3.exe.
>
> But it would be far more appropriate to use prepare:
>
> insert into T values(?);
>
> and then bind the parameter before executing. You won't have to alter your
> data for inserting and it will be much better for SQL code injection
> protection, depending where the email address comes from.
>
> I'm sure there is plenty of documentation with your language binding for
> SQLite on how to prepare, bind, execute. Instead of building a final
> statement as a complete string and then execute it.
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> 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] "missing" sqlite3 invocation option?

[John McKown]

The sqlite3 command has a input command to ".read" a file which "contains
SQL in FILENAME". I am wondering why there isn't an equivalent command line
argument to do this. That is, have something like:

sqlite3 -f FILENAME database.db3

which would do the same as:

sqlite3 database.db3
sqlite> .read FILENAME
sqlite> .quit

This would mirror the PostgreSQL supplied psql command. For whatever that
is worth.

-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This list is getting spammed again

[John Long]

On Tue, 2018-05-08 at 11:40 +0200, Olivier Mascia wrote:
> > Le 8 mai 2018 à 09:37, Domingo Alvarez Duarte 
> > a écrit :
> > 
> > Hello Richard !
> > 
> > Again this list is getting spammed, I just received spam after
> > publish.
> > 
> > Cheers !
> 
> Technically, it is not the list which gets spammed.
> It is someone or some process which reads the messages posted to the
> list 

So I guess it is up to all the list owners to obfuscate posters' email
addresses appropriately? Why aren't people doing a better job of this?

Oops, there goes another one. Cheap drugs, sunglasses, Luis Vitton
handbags here they come...

/jl

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

Re: [sqlite] Window functions?

[John McKown]

I see the point being made by many in this thread. I am not against
expanding SQLite's functionality. But, if I might, I will throw out some
contrarian ideas. First is that SQLite really is an embedded SQL data base.
It is meant to be combined into your application's main executable file. So
the larger you make the basic SQLite engine, the more "bloat" in the
applications which use it. Of course, a good developer is going to set up
all the proper SQLite #define variables to reduce SQLite's footprint to be
only as functional a possible. This is the proper thing to do. The problem,
if there is one, is that the more "options" that can be enabled/disabled
via #define variables, the more variants exist of SQLite. And the more
difficult it is to test every possible variant. Remember, adding just one
more #define for an "optional" function doubles the number of variants.
This is a quite a bit of work to ask of Dr. Hipp for a completely free
software product.

Another thing that I wonder is why people want all of this in an _embedded_
SQL engine? If somebody really needs a full blown relational database
management system, I really think that a client/server model is superior.
Yes, I agree, this is just my opinion. You can have a different one and
neither of us is really "wrong". I love SQLite for a number of things that
I do. But when I want a multi-gigabyte database used by multiple
applications, I go with PostgreSQL. And, yes, I'm aware that there are such
DBs based on SQLite. But I find the "extras" which come with the
client/server model RDBMS fill a great need. One which I must write myself
when I use SQLite, or find one already written by someone else that I can
adopt/adapt. I mentioned PostgreSQL instead of Oracle or MS SQL Server
mainly due to cost and licensing fees. PostgreSQL is FOSS and basically
allows you to "close source" not only your apps, but any mods to PostgreSQL
that you want to. Very similar to the 2-clause BSD and MIT licenses. In
addition, there is a commercial version, EDB, for people who want or need
professional maintenance, such as many larger companies like to have.
SQLite also has such paid support, if needed.

Well, I've just put up a couple of my thoughts. That's all they are. Maybe
some points for polite discussion. Or maybe I'm just more comfortable with
the way that I already do things. {shrug} What do you expect from a person
near retirement?

On Fri, May 4, 2018 at 2:33 PM, cherie <vg.vikasgu...@gmail.com> wrote:

>
> In 2008 I was part of a project which was mostly DB driven using Sybase
> 12.5. Sybase neither had support for user functions nor window functions &
> many other features, which other contemporary RDBMS had and same arguments
> was thrown why you need user functions or window functions if both can be
> accomplished by stored procedures. Version 1 release (early 2009) ended up
> with around 200 tables, 250 views and roughly 600 stored procs.
>
> Now after 10 years Sybase 15.7 has introduced user functions (and many
> other
> new features) saying it reduces TCO/time-to-market. Now my same application
> has grown to 500 tables, 410 views and 2600 stored procs. Most of these
> procs are repetitive codes with hard to read complex SQL written by
> developers mostly accomplishing reporting needs that grew in years (as
> application stabilized). And looking at those procs I can very clearly say
> that these are nothing but workarounds cooked to achieve what window
> functions provide in very easy to implement manner to developers. Every now
> and then we keep getting alerts that one of the proc is non performing or
> slow etc.
>
> I would love to see window function (before I die) being introduced in
> SQLite to reduce TCO because these output are anyhow being written in java,
> python or wherever if not in SQLite SQL, how its reducing the overall space
> footprint in android, I am not sure. 10 lines of window function can be
> achieved by 100 lines of CTE based SQL and if you do not have developers
> with SQL inclination they end up writing same in one language or the other.
> Think of testing effort that goes in verifying these codes.
>
> Everything in computer can done by machine/assembly codes, then why to use
> Java or C (its simplicity).
>
> -- Abstraction_(computer_science)
> <https://en.wikipedia.org/wiki/Abstraction_(computer_science)>
>
>
>
> --
> 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
>



-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Documentation Query/Correction

[John McMahon]

David

My point point was that in one section of the documentation 'Type 
Affinity' was changed from 'NONE' to 'BLOB' with an explanatory note as 
to why and in another section it was unchanged. AFAIK type affinity of 
'NONE' is the same as 'BLOB' as per the explanatory note. I was just 
bringing to attention what I thought was an inconsistency in the 
documentation.


John


On 05/04/2018 06:25, David Raymond wrote:

Looks like when it goes and makes the table it doesn't give it an explicit "blob" type, 
as you would think from the phrase "When an expression is a simple reference to a column of a 
real table (not a VIEW or subquery) then the expression has the same affinity as the table 
column." It gives it no explicit type at all. However, according to...

http://www.sqlite.org/datatype3.html#determination_of_column_affinity
"3. If the declared type for a column contains the string "BLOB" or if no type is 
specified then the column has affinity BLOB."

...that lack of any explicit column type will results in an implicit blob 
affinity. So I guess it still winds up as blob in the end, but in a roundabout 
way. Though it doesn't show up in things like pragma table_info.



SQLite version 3.23.0 2018-04-02 11:04:16
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table foo (i int, nu numeric, r real, t text, b blob, n);

sqlite> create table bar as select i, nu, r, t, b, n from foo;

sqlite> select * from sqlite_master;
type|name|tbl_name|rootpage|sql
table|foo|foo|2|CREATE TABLE foo (i int, nu numeric, r real, t text, b blob, n)
table|bar|bar|3|CREATE TABLE bar(
   i INT,
   nu NUM,
   r REAL,
   t TEXT,
   b,
   n
)

sqlite> pragma table_info(foo);
cid|name|type|notnull|dflt_value|pk
0|i|int|0||0
1|nu|numeric|0||0
2|r|real|0||0
3|t|text|0||0
4|b|blob|0||0
5|n||0||0

sqlite> pragma table_info(bar);
cid|name|type|notnull|dflt_value|pk
0|i|INT|0||0
1|nu|NUM|0||0
2|r|REAL|0||0
3|t|TEXT|0||0
4|b||0||0
5|n||0||0


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of John McMahon
Sent: Wednesday, April 04, 2018 3:54 PM
To: SQLite Users
Subject: [sqlite] Documentation Query/Correction


In documentation for version 3.21.0:

in datatypes3.html
...
3. Type Affinity
...
Each column in an SQLite 3 database is assigned one of the following
type affinities:

  TEXT
  NUMERIC
  INTEGER
  REAL
  BLOB

(Historical note: The "BLOB" type affinity used to be called "NONE". But
that term was easy to confuse with "no affinity" and so it was renamed.)

and in lang_createtable.html
...
CREATE TABLE ... AS SELECT Statements
...
   The declared type of each column is determined by the expression
affinity of the corresponding expression in the result set of the SELECT
statement, as follows:

Expression Affinity Column Declared Type
TEXT"TEXT"
NUMERIC "NUM"
INTEGER "INT"
REAL"REAL"
NONE"" (empty string)

In the Expression Affinity table above, should the Expression Affinity
'NONE' be updated to 'BLOB' possibly with the explanatory 'Historical
note:' as per section '3. Type Affinity' in datatypes.html above.

NOTE: I have checked the current on line documents and they match the above.

For consideration.

Regards,
John




--
Regards
   John McMahon
j...@jspect.fastmail.com.au
04 2933 4203

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

[sqlite] Documentation Query/Correction

[John McMahon]

In documentation for version 3.21.0:

in datatypes3.html
...
3. Type Affinity
...
Each column in an SQLite 3 database is assigned one of the following 
type affinities:


TEXT
NUMERIC
INTEGER
REAL
BLOB

(Historical note: The "BLOB" type affinity used to be called "NONE". But 
that term was easy to confuse with "no affinity" and so it was renamed.)


and in lang_createtable.html
...
CREATE TABLE ... AS SELECT Statements
...
 The declared type of each column is determined by the expression 
affinity of the corresponding expression in the result set of the SELECT 
statement, as follows:


Expression Affinity Column Declared Type
TEXT"TEXT"
NUMERIC "NUM"
INTEGER "INT"
REAL"REAL"
NONE"" (empty string)

In the Expression Affinity table above, should the Expression Affinity 
'NONE' be updated to 'BLOB' possibly with the explanatory 'Historical 
note:' as per section '3. Type Affinity' in datatypes.html above.


NOTE: I have checked the current on line documents and they match the above.

For consideration.

Regards,
John


--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[John G]

0

(Out of 3 databases. )

On 16 March 2018 at 15:37, Richard Hipp  wrote:

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>
> Private email to me is fine.  Thanks for participating in this survey!
> --
> 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] Is it a bug or "as designed"?

[John Found]

On Sat, 10 Mar 2018 01:17:38 +
Simon Slavin <slav...@bigfraud.org> wrote:

> On 9 Mar 2018, at 7:49pm, John Found <johnfo...@asm32.info> wrote:
> 
> > In the current implementation "insert or replace" behave as the foreign 
> > constraint is deferred.
> > But according to documentation, all foreign constraints in SQLite are 
> > immediate by default.
> 
> John,
> 
> The documentation suggests that in SQLite foreign keys are not deferred by 
> default.  Section 4.2 of
> 
> <https://sqlite.org/foreignkeys.html>
> 
> talks about this and shows how to set up a foreign key to be deferred:
> 
> create table B (
> aid references A(id) on delete cascade DEFERRABLE INITIALLY DEFERRED, 
> ulti_data)
> 

No difference at all. Because "insert or replace" always works as if the 
constraint is deferred. 
"insert or replace" always succeed to delete rows that are referenced by B and 
defers the constraint enforcement until
the end of the internal transaction. But at the end, a new row with the same ID 
is inserted, so there is no 
constraint violation anymore. 


> Assuming you are using atomic commit (not using BEGIN/COMMIT, so SQLite 
> "inserts" them around that single statement) I /think/ this should allow your 
> code to work the way you intended.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found <johnfo...@asm32.info>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it a bug or "as designed"?

[John Found]

On Fri, 9 Mar 2018 19:42:19 +
Simon Slavin <slav...@bigfraud.org> wrote:

> On 9 Mar 2018, at 7:11pm, John Found <johnfo...@asm32.info> wrote:
> 
> > "insert or replace" succeed without deleting the old rows from B.
> 
> "replace" means "delete the original row, then insert a new one".
> 
> In your code, figure out whether you need INSERT or UPDATE, and do the 
> appropriate one.
> 

You are right. And Jay Kreibich in his post above. But then the second solution 
from my post should be the correct behavior. 

In the current implementation "insert or replace" behave as the foreign 
constraint is deferred.
But according to documentation, all foreign constraints in SQLite are immediate 
by default.


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


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found <johnfo...@asm32.info>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it a bug or "as designed"?

[John Found]

I have two tables with foreign constraint:

create table A ( id primary key not null, single_data );
create table B ( aid references A(id) on delete cascade, multi_data);

Now I am periodically inserting data in A and B with the following queries:

insert or replace into A values (?1, ?2);
insert into B values (?1, ?2); 

Unfortunately, after replacing some row in A, all previously inserted rows in B 
got deleted, even if the value of ID does not changes. Here SQLite works 
exactly as it first deletes the conflicting row from A and then inserting new.

Now, if I define the table B without "on delete":

create table B ( aid references A(id), multi_data);

"insert or replace" succeed without deleting the old rows from B.

In my opinion this behaviour is not consistent. The consistent solutions IMHO 
are two:

1. "insert or replace" succeed both in the first and in the second case without 
deleting rows from B,

2. "insert or delete" succeed in the first case, deleting all constrained rows 
from B and fails in the second case with "FOREIGN KEY constraint failed".

The first case IMHO is more intuitive and natural. At least this was my 
expectation when writing the code.


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found <johnfo...@asm32.info>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about Practicality of Embedding SQLite on Cortex-M4 Processor

[Obrien, John J]

Hello,


My team is working on a project that involves transmitting sensor data from a 
data logger module to a mobile application via Bluetooth. I am interested in 
finding a relatively fast, reliable way to store the data that was collected by 
the data logger. Since we aren't guaranteed to always have a reliable Bluetooth 
connection between the mobile app and the data logger, we will need a way to 
mark which records are synchronized with the mobile application and which still 
haven't been sent up, so that the data logger can continue to collect data even 
when the mobile application is out of range. We collect data continuously at 
4Hz, so I anticipate that we will have far too much data to use a flat file and 
manually traverse each record prior whenever the mobile application requests 
data.


I am very new to embedded programming (almost no prior experience) but have 
prior mobile application development experience. My first thought is to store 
the data in a SQLite database table and include one column called 
"IsSynchronized" that can store a boolean value to indicate which datapoints 
have been synced. When the mobile application requests data, the records where 
"IsSynchronized == false" will be transmitted to the mobile app. Once 
successful, the mobile app will let the data logger know that the transmission 
succeeded and set the IsSynchronized column to true for each synchronized 
record. This is how I would do it if the data were traveling from a mobile app 
to a server, but I don't know if this is a good idea for an embedded database 
to a mobile application.


Our data logger uses an Atmel-SAM4S microprocessor. We have 8GB of flash 
memory, so storage isn't an issue. Our RAM is very limited; we only have 160KB. 
We are working with an external vendor to design the data logger. They have 
developed an custom, handrolled operating system, but since it contains no 
Virtual File System, we aren't sure if SQLite will be an option for us. I've 
heard of other folks using uClinux or other Unix based Operating Systems on the 
SAM4S to accommodate SQLite 
(http://sqlite.1065341.n5.nabble.com/VFS-for-an-MCU-internal-flash-td83079.html),
 but I don't know how practical this is.


My question is this:

  1.  First, am I on the wrong track by pursuing an embedded database to solve 
my data synchronization issue?
  2.  Are there other embedded database alternatives that might make more sense 
in my scenario. I've heard a lot about NoSQL DBs like BerkleyDB and UnQlite, 
but I don't know is they are practical for my case either.
  3.  If we do need to implement a VFS, does it make sense to use a unix-based 
embedded operating system? If so, are there any recommendations as to which one 
may be a good fit for my hardware
  4.  Last, are we wasting our time with the SAM4S processor to solve this 
problem? We would like to take advantage of existing code, the nice 
power-consumption characteristics of the SAM4S, and would rather not have to go 
through the time and expense of upgrading our processor, but, if our processor 
choice is a non-starter, it would be good to know sooner rather than later.

To summarize, my question is regarding what direction I should ask the hardware 
vendor to take. Does it make sense for them to spend time optimizing the SAM4S 
for SQLite or should we consider another approach?



Thank you,

JJ OBrien
___
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

[John McKown]

On Tue, Feb 20, 2018 at 11:44 AM, Jens Alfke <j...@mooseyard.com> wrote:

>
>
> > On Feb 19, 2018, at 7:49 PM, petern <peter.nichvolo...@gmail.com> wrote:
> >
> > 3. Why can't SQLite have the expected common static SQL functions for
> > getting rapid development done without external tools?
>
> Because its primary use case is as an embedded library for programs, not
> as a standalone tool or server. From that perspective, it’s wasteful for
> SQLite to include functionality that can be done as well or better by the
> program that calls it.
>

​I agree. Sometimes it seems to me that people are using SQLite as if it
were a "cheap" version of "MS SQL Server"​. And then wanting it to have all
the "bells and whistles" of a full fledged, multi-user, relational SQL data
base. I can even somewhat understand that because it is just so easy to
install and use. Much easier than MySQL, MariaDB, or PostgreSQL (or any POS
on MS Windows).



>
> It’s also very easy to add custom SQL functions to SQLite, so if you have
> a need for these, you can write them yourself and either link them into
> your app, or build them as a library that the sqlite3 tool can load.
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need some tips on using FTS5 with SQLite

[John Found]

On Wed, 14 Feb 2018 14:26:21 +
Wout Mertens <wout.mert...@gmail.com> wrote:

> I too am interested in this answer, I still have to start using fts5.
> 
> What would be interesting is to see the `EXPLAIN QUERY PLAN [query]` for
> each of your queries, so as to see what causes the slowness.
> 

It is clear what causes the slowness. For example here is one query:

select 
  PostFTS.rowid,
  PostFTS.user as UserName,
  P.userID,
  U.av_time as AVer,
  PostFTS.slug,
  strftime('%d.%m.%Y %H:%M:%S', P.postTime, 'unixepoch') as PostTime,
  P.ReadCount,
  snippet(PostFTS, 0, '*', '*', '...', 16) as Content,
  PostFTS.Caption,
  (select count() from UnreadPosts UP where UP.UserID = 2 and UP.PostID = 
PostFTS.rowid) as Unread
from
  PostFTS
  left join Posts P on P.id = PostFTS.rowid
  left join Users U on U.id = P.userID
where
  PostFTS match "user: s*"
order by SOME_CLAUSE
limit 20;

1. With SOME_CLAUSE=rank, the execution time is between 28ms and 40ms
2. With SOME_CLAUSE=P.PostTime, the execution time is approximately 500ms!
3. Without "order by" clause at all, the execution time is 1.1ms.

The respective EXPLAIN QUERY PLAN:

1. order by rank (28..40ms)
0   0   0   SCAN TABLE PostFTS VIRTUAL TABLE INDEX 327713:
0   1   1   SEARCH TABLE Posts AS P USING INTEGER PRIMARY KEY 
(rowid=?)
0   2   2   SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY 
(rowid=?)

2. order by P.PostTime (500ms)
0   0   0   SCAN TABLE PostFTS VIRTUAL TABLE INDEX 327681:
0   1   1   SEARCH TABLE Posts AS P USING INTEGER PRIMARY KEY 
(rowid=?)
0   2   2   SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY 
(rowid=?)
0   0   0   USE TEMP B-TREE FOR ORDER BY

3. without order by: (1.1ms)
0   0   0   SCAN TABLE PostFTS VIRTUAL TABLE INDEX 327681:
0   1   1   SEARCH TABLE Posts AS P USING INTEGER PRIMARY KEY 
(rowid=?)
0   2   2   SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY 
(rowid=?)

Obviously, the slow down is because of the "USE TEMP B-TREE FOR ORDER BY". 
Order by any field
other than "rank" and "rowid" makes this query very slow.


> On Thu, Feb 8, 2018, 7:14 PM John Found, <johnfo...@asm32.info> wrote:
> 
> >
> > I am using FTS5 for pretty complex search in my application, but recently,
> > trying to make it even more complex I faced some problems that are more
> > general than only FTS5.
> >
> > I have a forum engine where are several tables for the threads, for the
> > posts, for the users etc. At first I want to be able to search in the posts
> > text, but moreover, this search have to be limiter to some subset of the
> > posts, for example in the posts of a particular thread or posts of some
> > user. Also, there are cases where free-text search is not actually
> > necessary, for example when I am searching for all posts from a particular
> > user.
> >
> > At first, I tried to create a FTS5 table, containing only the text data
> > that need to be searched and then to access it by queries of the type:
> >
> > select
> >   some,
> >   fields
> > from
> >   fts
> >   left join posts p on p.id = fts.rowid
> >   left join threads t on t.id = p.threadid
> >   left join users u on u.id = p.userid
> > where
> >   fts match ?1 and u.nick = ?2 and t.id = ?3
> > order by ORDER
> >
> > Such queries are pretty fast when there is only fts match directive in the
> > where clause.
> > But any additional condition added ruins the performance, especially if
> > the fts match returns big amount of matches.
> >
> > Additional problem is the order by clause. If the ORDER BY term is "rank"
> > everything works great, but changing it to
> > other field (for example the post time in order to get first most recent
> > posts) causes huge slow down of the query.
> >
> > My second attempt was to sacrifice space for speed and to put all
> > searchable data in the fts table - post text, the thread titles and the
> > usernames. This way, building complex fts queries kind of:
> >
> >(content: ?1 OR caption: ?2) AND thread: ?3 AND user: ?4
> >
> > I can leave only the fts query in the WHERE clause. This way, the search
> > is pretty fast, but the huge problem remains
> > the ORDER BY clause. Again everything works fine with "rank", but attempts
> > to use any other field for sorting, causes
> > huge probems: slow downs up to tens of seconds (usual search time is few
> > milliseconds) and out of memory errors.
> >
> > Such problems with this second approach are even more serious tha

[sqlite] Need some tips on using FTS5 with SQLite

[John Found]

I am using FTS5 for pretty complex search in my application, but recently, 
trying to make it even more complex I faced some problems that are more general 
than only FTS5.

I have a forum engine where are several tables for the threads, for the posts, 
for the users etc. At first I want to be able to search in the posts text, but 
moreover, this search have to be limiter to some subset of the posts, for 
example in the posts of a particular thread or posts of some user. Also, there 
are cases where free-text search is not actually necessary, for example when I 
am searching for all posts from a particular user.

At first, I tried to create a FTS5 table, containing only the text data that 
need to be searched and then to access it by queries of the type:

select 
  some, 
  fields 
from 
  fts 
  left join posts p on p.id = fts.rowid
  left join threads t on t.id = p.threadid
  left join users u on u.id = p.userid
where
  fts match ?1 and u.nick = ?2 and t.id = ?3
order by ORDER

Such queries are pretty fast when there is only fts match directive in the 
where clause. 
But any additional condition added ruins the performance, especially if the fts 
match returns big amount of matches.

Additional problem is the order by clause. If the ORDER BY term is "rank" 
everything works great, but changing it to
other field (for example the post time in order to get first most recent posts) 
causes huge slow down of the query.

My second attempt was to sacrifice space for speed and to put all searchable 
data in the fts table - post text, the thread titles and the usernames. This 
way, building complex fts queries kind of:

   (content: ?1 OR caption: ?2) AND thread: ?3 AND user: ?4

I can leave only the fts query in the WHERE clause. This way, the search is 
pretty fast, but the huge problem remains
the ORDER BY clause. Again everything works fine with "rank", but attempts to 
use any other field for sorting, causes
huge probems: slow downs up to tens of seconds (usual search time is few 
milliseconds) and out of memory errors. 

Such problems with this second approach are even more serious than on the first 
approach. i.e. with the second approach everything works fine and quick with 
"rank" order by, and very, very slow and with errors, on any other "order by" 
option.

So, he main question follows:

What is the right way to design such complex search systems, based on FTS? How 
to properly approach the sorting of the search results in order to not have so 
big slowdowns and out of memory errors.

Any tips are highly welcome!

Regards
-- 
http://fresh.flatassembler.net
http://asm32.info
John Found <johnfo...@asm32.info>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vetting SQLite

[John Long]

On Mon, 2018-02-05 at 09:39 -0800, Jens Alfke wrote:
> > On Feb 5, 2018, at 9:21 AM, Drago, William @ CSG - NARDA-MITEQ  > liam.dr...@l3t.com> wrote:
> > 
> > The reliable part is easy because there is enough information on
> > the SQLite website about testing, but what about security?
> 
> Open source software is more secure than closed source, since the
> source code can be reviewed and audited.

It is considered more easy to verify, sure. But there are still some
big questions:

1. How do you know the source you're looking at is what you're running?

2. How do you know the source you're seeing is compiled correctly? Look
at the buglists for common (*cough* gcc *cough*) compilers.

3. How do you know the CPU you are running on is running the code
correctly and that it is secure? Common microprocessor vendors have
hundreds of errata for chips still being sold.

The only way to know what code is doing is to trace it on the target
hardware. We don't need source code for that. And even that could be
misleading if the hardware is broken or deliberately subverted.

>  (In the security field, closed-source cryptographic software isn’t
> even taken seriously since it’s not possible to verify its claims,
> just as scientific results need peer review and independent
> confirmation.)

That is true but perhaps closed-source cryptographic _algorithms_ are
the issue and not source code. And this is just for reference
implementations... you can still verify exactly what you have without
source code. It just takes more effort and personally I believe it's
more reliable.

I don't believe RSA or IBM or any of the other vendors have open
sourced any crypto code. I think what typically happens is when they
come up with a new standard they produce a reference implementation and
then after the contest is over they implement whatever they implement
and everybody just uses it. 

> I don’t know if this will convince your IT management though, because
> if they’re against open source they must be remarkably backward...

I don't think that is necessarily so. Many companies want/need to be
able to point fingers when something goes wrong. And they need to get
their systems working ASAP. The vast majority of open source projects
have no accountability, they're free as in beer and as long as it works
for the guys spending their time writing it they're done. Companies
(especially publicly owned and traded companies) really can not rely on
freebies and goodwill if they want to stay in business and keep their
executives out of jail. Open source quality is atrocious. Sure, a lot
of closed source quality is atrocious too. Free stuff should be
expected to be worth price paid and most of the time it is not even
that.

sqlite (and fossil!) are wonderful, wonderful projects. But there is a
sea of unsupported garbage out there and nobody who wants to keep their
job can feel safe wading through that. There is also the issue of viral
contamination of GPL, etc.

I think Dr. Hipp did everything right but even so, he is in the tiny
minority.

/jl



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

Re: [sqlite] Vetting SQLite

[John Found]

On Mon, 5 Feb 2018 17:21:53 +
"Drago, William @ CSG - NARDA-MITEQ" <william.dr...@l3t.com> wrote:

> All,
> 
> I've been using/loving SQLite for years, but the use of open source software 
> is highly discouraged where I work, and now I have to prove to our IT dept. 
> that SQLite is reliable and secure. The reliable part is easy because there 
> is enough information on the SQLite website about testing, but what about 
> security? How can I convince the auditors that SQLite is not stealing 
> corporate secrets and spreading viruses?
> 

The open code is actually the only code that can be proofed to be secure. The 
written guarantee is pointless actually because the malware is always 
introduced in secret. The procedure is following: 

1. Download the SQLite code from the official repository.
2. Audit the code in order to proof it does not contains 
malware/spyware/security flaws.
3. Compile the code and link it against the dependencies proofed to be secure! 
(this is important!)
4. You have SQLite proven to be secure.

The only problem is p.3, but if your company is so paranoid about security, you 
already have audited the standard 
C libraries. 


> Is there a statement somewhere on the website that guarantees that copies of 
> SQLIte downloaded from SQLite.org and System.Data.Sqlite.org are free of all 
> forms of spyware/malware/viruses/etc?
> 
> Thanks,
> --
> Bill Drago
> Staff Engineer
> L3 Narda-MITEQ<http://www.nardamiteq.com/>
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / william.dr...@l3t.com<mailto:william.dr...@l3t.com>
> 
> CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use 
> of the intended recipient and may contain material that is proprietary, 
> confidential, privileged or otherwise legally protected or restricted under 
> applicable government laws. Any review, disclosure, distributing or other use 
> without expressed permission of the sender is strictly prohibited. If you are 
> not the intended recipient, please contact the sender and delete all copies 
> without reading, printing, or saving..
> 
> Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of 
> all @L-3Com.com email addresses. To ensure delivery of your messages to this 
> recipient, please update your records to use william.dr...@l3t.com.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found <johnfo...@asm32.info>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Optimization - don't understand.

[John Found]

On Mon, 5 Feb 2018 15:08:33 +
Hick Gunter <h...@scigames.at> wrote:

> I think you are optimizing the performance of a conceptually inefficient 
> query.
> 
> If you are looking for a recipe that contains apples, do you read the entire 
> cook book, checking each recipe for apples? Maybe it is much more efficient 
> to look up apples in the index of ingredients and retrieve only the recipes 
> that actually contain them.

You are definitely right, but the things are a little bit more complex. 

The query I asked for is simplified in order to make the question more clear. 
It is part of a complex search, looking simultaneously in several fields: fts5 
search (removed for simplicity), T.Caption, TT.Tag and U.nick fields. 

I am trying to estimate how exactly to handle all these possible combinations 
and whether it is possible to be done with one fixed query or need specially 
synthesized query for every particular case.


> 
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von John Found
> Gesendet: Montag, 05. Februar 2018 15:55
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] Re: [sqlite] Optimization - don't understand.
> 
> It is clear now. But should I define an index that contains all fields used 
> in the query?
> 
> Something like:
> 
> create index idxPostsComplex on posts(threadid, userid, Content, 
> postTime, ReadCount);
> 
> Actually I tried and the query uses this index without problems (and the 
> performance seems to be good).
> 
> But what are the disadvantages of such approach? (except the bigger database 
> size, of course)
> 
> On Mon, 5 Feb 2018 09:24:51 -0500
> Richard Hipp <d...@sqlite.org> wrote:
> 
> > On 2/5/18, John Found <johnfo...@asm32.info> wrote:
> > > The following query:
> > >
> > > explain query plan
> > > select
> > >   U.nick,
> > >   U.id,
> > >   U.av_time,
> > >   T.Caption,
> > >   P.id,
> > > --  P.ReadCount,
> > > --  P.Content,
> > > --  P.postTime,l
> > >   T.Caption
> > > from Posts P
> > > left join Threads T on P.threadID = T.id
> > > left join ThreadTags TT on TT.threadID = T.id
> > > left join Users U on P.userID = U.id
> > > where TT.Tag = ?1;
> > >
> > > ...returns:
> > >
> > > 0 0 0 SCAN TABLE Posts AS P USING COVERING INDEX idxPostsThreadUser
> > > 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
> > > 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX
> > > idxThreadTagsUnique (ThreadID=? AND Tag=?)
> > > 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY
> > > (rowid=?)
> > >
> > > But uncommenting any of the commented fields, turns the result into:
> > >
> > > 0 0 0 SCAN TABLE Posts AS P
> > > 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
> > > 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX
> > > idxThreadTagsUnique (ThreadID=? AND Tag=?)
> > > 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY
> > > (rowid=?)
> > >
> > > ... and significantly degrades the performance.
> > >
> > > The index idxPostsThreadUser is defined following way:
> > >
> > > create index idxPostsThreadUser on Posts(threadid, userid);
> > >
> > > IMHO, the change of the selected columns should not affect the query
> > > plan, but maybe I am wrong somehow.
> > >
> > > What I am missing?
> > >
> >
> > SQLite prefers to scan the index rather than the original table,
> > because the index is usually smaller (since it contains less data) and
> > hence there is less I/O required to scan the whole thing.
> >
> > But the index only provides access to the id, threadid, and userid
> > columns.  If content of other columns is needed, then the whole table
> > must be scanned instead.
> > --
> > 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
> 
> 
> --
> http://fresh.flatassembler.net
> http://asm32.info
> John Found <johnfo...@asm32.info>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.

Re: [sqlite] Optimization - don't understand.

[John Found]

It is clear now. But should I define an index that contains all fields used in 
the query?

Something like:

create index idxPostsComplex on posts(threadid, userid, Content, postTime, 
ReadCount);

Actually I tried and the query uses this index without problems (and the 
performance seems to be good). 

But what are the disadvantages of such approach? (except the bigger database 
size, of course)

On Mon, 5 Feb 2018 09:24:51 -0500
Richard Hipp <d...@sqlite.org> wrote:

> On 2/5/18, John Found <johnfo...@asm32.info> wrote:
> > The following query:
> >
> > explain query plan
> > select
> >   U.nick,
> >   U.id,
> >   U.av_time,
> >   T.Caption,
> >   P.id,
> > --  P.ReadCount,
> > --  P.Content,
> > --  P.postTime,l
> >   T.Caption
> > from Posts P
> > left join Threads T on P.threadID = T.id
> > left join ThreadTags TT on TT.threadID = T.id
> > left join Users U on P.userID = U.id
> > where TT.Tag = ?1;
> >
> > ...returns:
> >
> > 0 0 0 SCAN TABLE Posts AS P USING COVERING INDEX idxPostsThreadUser
> > 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
> > 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX
> > idxThreadTagsUnique (ThreadID=? AND Tag=?)
> > 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)
> >
> > But uncommenting any of the commented fields, turns the result into:
> >
> > 0 0 0 SCAN TABLE Posts AS P
> > 0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
> > 0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX
> > idxThreadTagsUnique (ThreadID=? AND Tag=?)
> > 0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)
> >
> > ... and significantly degrades the performance.
> >
> > The index idxPostsThreadUser is defined following way:
> >
> > create index idxPostsThreadUser on Posts(threadid, userid);
> >
> > IMHO, the change of the selected columns should not affect the query plan,
> > but maybe I am wrong somehow.
> >
> > What I am missing?
> >
> 
> SQLite prefers to scan the index rather than the original table,
> because the index is usually smaller (since it contains less data) and
> hence there is less I/O required to scan the whole thing.
> 
> But the index only provides access to the id, threadid, and userid
> columns.  If content of other columns is needed, then the whole table
> must be scanned instead.
> -- 
> 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


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found <johnfo...@asm32.info>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Optimization - don't understand.

[John Found]

The following query:

explain query plan
select 
  U.nick,
  U.id,
  U.av_time,
  T.Caption,
  P.id,
--  P.ReadCount,
--  P.Content,
--  P.postTime,l
  T.Caption
from Posts P
left join Threads T on P.threadID = T.id
left join ThreadTags TT on TT.threadID = T.id
left join Users U on P.userID = U.id
where TT.Tag = ?1;

...returns:

0 0 0 SCAN TABLE Posts AS P USING COVERING INDEX idxPostsThreadUser
0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX 
idxThreadTagsUnique (ThreadID=? AND Tag=?)
0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)

But uncommenting any of the commented fields, turns the result into:

0 0 0 SCAN TABLE Posts AS P
0 1 1 SEARCH TABLE Threads AS T USING INTEGER PRIMARY KEY (rowid=?)
0 2 2 SEARCH TABLE ThreadTags AS TT USING COVERING INDEX 
idxThreadTagsUnique (ThreadID=? AND Tag=?)
0 3 3 SEARCH TABLE Users AS U USING INTEGER PRIMARY KEY (rowid=?)

... and significantly degrades the performance.

The index idxPostsThreadUser is defined following way:

create index idxPostsThreadUser on Posts(threadid, userid);

IMHO, the change of the selected columns should not affect the query plan, but 
maybe I am wrong somehow.

What I am missing?

-- 
http://fresh.flatassembler.net
http://asm32.info
John Found <johnfo...@asm32.info>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Easiest way to get day of week as a string (not a number)?

[John McKown]

On Thu, Feb 1, 2018 at 6:55 AM, Chris Green <c...@isbd.net> wrote:

> I want to get Sun, Mon, Tue, Wed etc. from a date, what's the easiest
> way of doing this in a sqlite select?
>
> I guess I can do something (horrible?) with the numeric day of week
> and substr() but is there not an easier way?
>

​-- horrible way
SELECT CASE strftime("%w",DateInRow)
   WHEN 0 THEN "Sun"
   WHEN 1 THEN "Mon"
   ...
   WHEN 6 THEN "Sat"
   END AS DayOfWeek



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



-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF8 and NUL

[John McKown]

On Fri, Jan 26, 2018 at 1:41 PM, Peter Da Silva <
peter.dasi...@flightaware.com> wr

> On 1/26/18, 1:37 PM, "sqlite-users on behalf of J Decker" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of d3c...@gmail.com>
> wrote:
> >doesn't get 26 either. 0x1a
>
> 26 isn't EOF, it's SUB (substitute). It was used to represent
> untranslatable characters when converting (for example) EBCDIC to ASCII.
>

​In the distant past (CP/M-80), the filesystem meta data did not include
the actual _length_ of the data for a text data file. The I/O was done in
sectors. The CP/M-80 system, by convention, used 0x1A (26) and an "logical
EOF" indication and the C routines would detect it and report EOF.​ MS-DOS
basically didthe same thing, for compatibility reasons. I am not sure, but
I think that Windows still does this. A quick test with the command "type
x.txt" where "x.txt" contained "abc~def" (where ~ is standing in for 0x1a)
resulted in my seeing "abc". But "notepad x.txt" shows "abc def". So I
guess it depends on how old the Windows app is.


-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can an SQL script be built from within sqlite?

[John G]

I've not tried it, but this article from OSXdaily says you can get the
command line (Terminal) in iOS.

http://osxdaily.com/2018/01/08/get-terminal-app-ios-command-line/

That probably does not solve the fork requirement, and I'm sure it is
sandboxed.

John G



On 15 January 2018 at 15:00, Richard Hipp <d...@sqlite.org> wrote:

> On 1/15/18, Shane Dev <devshan...@gmail.com> wrote:
> >
> > Did the Apple engineers tell you why it is not possible to compile and
> run
> > the SQLite shell on iOS?
> >
>
> You cannot get a command-line prompt on iOS, and the SQLite shell
> requires a command-line prompt (like bash).  If i understand
> correctly, iOS does not allow fork() as a security measure.
>
> The previous paragraph is not authoritative.  It is merely my
> recollection.  I have not researched the issue.
>
> --
> 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

[sqlite] Output/CSV

[John Gutierrez]

Sqlite Shell:

On Linux, using .output and .mode csv, sqlite produces a [dos] 1L, 161C 
file.  Is this a bug or is it a compile issue for my distribution to 
address?

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

Re: [sqlite] sqlite3_column_decltype and max and min

[John G]

Thanks Warren. Sorry about that, I had an old version in /opt/local/bin.

John Gillespie

On 11 January 2018 at 15:24, Warren Young <war...@etr-usa.com> wrote:

> On Jan 11, 2018, at 5:47 AM, John G <rjkgilles...@gmail.com> wrote:
> >
> > Is this because I am stuck with version 3.8.8.3 which is what MacOS
> Sierra
> > provides?
>
> I have sqlite3 version 3.16.0 in /usr/bin on this 10.12.6 (Sierra) system.
> ___
> 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 Application Server Concurrency

[John Found]

I have several such applications, running in industrial environment. 

I always use a TCP server on 'X' and TCP client on 'Y' with some simple, 
specialized http-like protocol on top.

All this can be implemented very easy, especially if you don't need a top 
performance and millions of clients. In addition such solution allows to avoid 
complex settings with web servers, back-front ends etc. The server and the 
client are built in the desktop application and the communication is 
transparent for the users.

On Thu, 11 Jan 2018 09:06:32 +
James Colehan <jcole...@gtt-training.com> wrote:

> Hi,
> 
> I am looking for some advice on using SQLite for my given situation.
> 
> My system involves a windows network. On PC 'X', I have an SQLite database 
> with a process that handles amendment's to the database ie INSERTS, DELETES 
> and UPDATES. Also, on PC 'X' I have some other processes running that are 
> requesting data (SELECT) form the database. I have the Journal Mode set to 
> WAL. This all appears to work fine.
> 
> My concern is that I have a requirement to query the database on PC 'X' from 
> other PC's. Basically, performing (SELECT) statements from stations 'Y' and 
> 'Z'. This creates a client/server situation that I appreciate SQLite is not 
> appropriate choice of database for. I hope doing the (SELECT) only from the 
> client will be a workable solution.
> 
> At the moment I have not encountered any problems. However, my concern is 
> that this maybe just luck and there is a potential of locking issues or 
> database corruption.
> 
> Any thoughts would be appreciated.
> 
> Regards,
> 
> James
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found <johnfo...@asm32.info>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_decltype and max and min

[John G]

Keith
Looks like a good idea but I get :

sqlite> create view if not exists SysColumns
   ...> as
   ...> select ObjectType collate nocase,
   ...>ObjectName collate nocase,
   ...>ColumnID collate nocase,
   ...>ColumnName collate nocase,
   ...>Affinity collate nocase,
   ...>IsNotNull,
   ...>DefaultValue,
   ...>IsPrimaryKey
   ...> from (
   ...> select ObjectType,
   ...>ObjectName,
   ...>cidas ColumnID,
   ...>name   as ColumnName,
   ...>type   as Affinity,
   ...>"notnull"  as IsNotNull,
   ...>dflt_value as DefaultValue,
   ...>pk as IsPrimaryKey
   ...>   from SysObjects
   ...>   join pragma_table_info(ObjectName)
   ...>  );
Error: near "(": syntax error

Is this because I am stuck with version 3.8.8.3 which is what MacOS Sierra
provides?

I see you were using 3.22.

John Gillespie

On 6 January 2018 at 20:02, Keith Medcalf <kmedc...@dessus.com> wrote:

> Full Schema Tables:
>
>
> -- Catalog Views using sqlite_master for SysObjects (Object Names)
> -- and the various pragma_(ObjectName) tables to retrieve schema
> data
> -- all TEXT columns in views have "collate nocase" attachmented to the
> output
> -- columns to ensure that where conditions on retrievals are not case
> sensitive
> -- Column Names in views defined so as to not conflict with keywords to
> ensure
> -- quoting when using views is not required
>
> drop view if exists SysIndexColumns;
> drop view if exists SysIndexes;
> drop view if exists SysColumns;
> drop view if exists SysObjects;
>
> create view if not exists SysObjects
> as
> select ObjectType collate nocase,
>ObjectName collate nocase
>   from (
> select type as ObjectType,
>name as ObjectName
>   from sqlite_master
>  where type in ('table', 'view', 'index')
>);
>
> create view if not exists SysColumns
> as
> select ObjectType collate nocase,
>ObjectName collate nocase,
>ColumnID collate nocase,
>ColumnName collate nocase,
>Affinity collate nocase,
>IsNotNull,
>DefaultValue,
>IsPrimaryKey
> from (
> select ObjectType,
>ObjectName,
>cidas ColumnID,
>name   as ColumnName,
>type   as Affinity,
>"notnull"  as IsNotNull,
>dflt_value as DefaultValue,
>pk as IsPrimaryKey
>   from SysObjects
>   join pragma_table_info(ObjectName)
>  );
>
> create view if not exists SysIndexes
> as
> select ObjectType collate nocase,
>ObjectName collate nocase,
>IndexName collate nocase,
>IndexID,
>IsUniqueIndex collate nocase,
>IndexOrigin collate nocase,
>IsPartialIndex
>   from (
> select ObjectType,
>ObjectName,
>name as IndexName,
>seq  as IndexID,
>"unique" as IsUniqueIndex,
>origin   as IndexOrigin,
>partial  as IsPartialIndex
>   from SysObjects
>   join pragma_index_list(ObjectName)
>);
>
> create view if not exists SysIndexColumns
> as
> select ObjectType collate nocase,
>ObjectName collate nocase,
>IndexName collate nocase,
>IndexColumnSequence,
>ColumnID,
>ColumnName collate nocase,
>IsDescendingOrder,
>Collation collate nocase,
>IsPartOfKey
>   from (
> select ObjectType,
>ObjectName,
>IndexName,
>seqno  as IndexColumnSequence,
>cidas ColumnID,
>name   as ColumnName,
>"desc" as IsDescendingOrder,
>coll   as Collation,
>keyas IsPartOfKey
>   from SysIndexes
>   join pragma_index_xinfo(IndexName)
>);
>
>
>
>
> ---
> 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: Keith Medcalf [mailto:kmedc...@dessus.com]
> >Sent: Saturday, 6 January, 2018 12:40
> >To: 'SQLite mailing list'
> >Subject: RE: [sqlite] sqlite3_column_decltype and max and min
> >
> >
> >SQLite version 3.22.0 2018-01-02 18:11:11

Re: [sqlite] C++ compiler

[John McKown]

On Sat, Dec 30, 2017 at 5:35 AM, eli <net...@gmail.com> wrote:

> Hello,
>
> It would be awesome if SQLite could compile as a part of bigger C++
> project.
> Right now there is a bunch of pointer casting errors, that can be fixed in
> a matter of hour IMHO.
>

​I'm not a very knowledgeable C++ programmer, but wouldn't a simple:

extern C {
...
... SQLite definitions
...
}

be a way to do it?

Or maybe I'm simplifying your question too much (wouldn't be the first time
for me).​



>
> Cheers,
>


-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Move to Github!!?

[John McKown]

On Tue, Dec 26, 2017 at 3:31 PM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> Heavens forbid!
>
> How is it possible to work if not from a Command-Line window?  That is,
> YUCK.
> Seriously how can anyone get any work done at all without having a command
> prompt at which one, how to put this, types commands?
>
> I happen to be one of those that holds Ashton-Tate's dBase II (or was it
> III, anyway, one of them) had the absolute best interface ever designed --
> a completely blank screen with a . in the upper left corner as a prompt at
> which you entered commands.  Absolutely beautiful design with absolutely no
> useless crap to get in the way!
>
> The first thing be consigned to the bitbucket on *ANY* Operating System
> worth using is the "Graphical User Interface".  And if you cannot get rid
> of it (like windows) its sole purpose is to open a command prompt to, you
> know, prompt for commands.
>
> Graphical User Interfaces are mostly designed by maroons for use by other
> maroons.  There are a few (very few) things which a GUI is any good for.
> Issuing commands and software development is not one of them.  (I have
> never seen a Graphical Editor that works worth a pinch of coon-poo, and
> so-called IDE's are useless steaming turds as well, most dependent on
> completely unusable editors -- the absolute worst abomination being, of
> course, Visual Studio).
>

​Well, this is very off-topic for this forum. But I'll interject that I
generally agree with you. However, I do think that some things are better
in a graphical environment. Such as, say, a paint program? {grin}. Now, for
an interactive SQL interface, I like using SQLite's command program; or
PostgreSQL's psql. I haven't really tried any SQL GUI interfaces other than
Oracle's. I use it mainly because it's all that I was given on the Windows
desktop at work. I haven't seen very many "character mode" applications any
more for things such as Word Processing or Spreadsheets. I did use Word
Perfect and​ Lotus 1-2-3 back in the MS-DOS days. But today's users want a
word process which is WYSIWYG and want their spreadsheets to create pretty
*π* charts.



>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>

-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Move to Github!!?

[John McKown]

On Tue, Dec 26, 2017 at 9:45 AM, Don V Nielsen <donvniel...@gmail.com>
wrote:

> > What's not so easy to replace is the Git integration in my editor (Visual
> Studio Code)
>
> Same here, but I use JetBrains products. I put a bug in the ear of
> JetBrains. At least its something.
>
> Have a good one, all
>

​I don't know either of those products. Mainly because I'm not a Windows'
developer. Do they have a publicly documented interface between their
product and a source maintenance system (e.g. git, cvs, ...)? If not, then
I guess they are dependent on writing a "one off" for every SCM that they
want to support. IMO, that would be very short sighted. ​

-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does sqlite have official development testing tool?

[John G]

If you don't have Tcl/Tk ... if you are using MacOS or Linux you already
have it.
On Windows you can download it from https://www.activestate.com/activetcl

John G

On 14 December 2017 at 12:19, advancenOO <haveagoodtime2...@gmail.com>
wrote:

> Hello Richard,
>
> I hope to run some tests by myself and I think TCL tests in your link are
> what I want.
> There are so many .tcl and .test in Sqlite source tree.
> Could someone share what commands I need to run to start all TCL tests?
>
> Thanks.
>
>
>
> --
> 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] values ?

[John McKown]

On Tue, Dec 12, 2017 at 12:34 PM, Mark Wagner  wrote:

> My reading of https://sqlite.org/syntax/select-core.html makes me think
> that I should be able to issue something like values('foo'); and get a row
> with a single column whose value is 'foo'.  But I get a syntax error.
>
> Probably obvious to the right people but what am I missing?
>
> sqlite> values('foo', 'bar');
> Error: near "values": syntax error
>

​Works for me too:

$ sqlite3
SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> values(1,2)
   ...> ;
1|2
sqlite> values('foo','bar');
foo|bar
sqlite>
​
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] random value get re-generated too often in SQLite

[John McKown]

On Fri, Dec 8, 2017 at 12:54 PM, John Mount <jmo...@win-vector.com> wrote:

> I am seeing an issue where a random value in a sub-query gets re-generated
> each time the value is used in later queries.  Below is an example query:
>
> SELECT r AS r1, r AS r2 FROM ( SELECT random() AS r FROM ( SELECT * from (
> VALUES(1),(2) ) ) a ) b
>
> One would expect r1 == r2.
>
> ---
> John Mount
>

​Interesting. I duplicated your example using PostgreSQL and it does what
you said you expected: R1==R2. In SQLite3, the .eqp full shows:

sqlite> .eqp full
sqlite> SELECT r AS r1, r AS r2 FROM ( SELECT random() AS r FROM ( SELECT *
from ( VALUES(1),(2) ) ) a ) b;
--EQP-- 0,0,0,SCAN SUBQUERY 1
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 0 000  Start at 0
1 InitCoroutine  1 7 200
sqlite_sq_5619D81F9BF8
2 Integer1 2 000  r[2]=1
3 Yield  1 0 000
4 Integer2 2 000  r[2]=2
5 Yield  1 0 000
6 EndCoroutine   1 0 000
7 InitCoroutine  1 0 200
8   Yield  1 13000  next row of
"sqlite_sq_5619D81F9BF8"
9   Function0  0 0 4 random(0)  00  r[4]=func(r[0])
10  Function0  0 0 5 random(0)  00  r[5]=func(r[0])
11  ResultRow  4 2 000  output=r[4..5]
12Goto   0 8 000
13Halt   0 0 000
4548137244590923354  8821858240296964415
761559492082550893  2723588653195689097
​
I think this is being done due to the "subquery flattening" as described on
http://sqlite.org/optoverview.html, which says:

To overcome this problem, SQLite attempts to flatten subqueries in the FROM
clause of a SELECT. This involves inserting the FROM clause of the subquery
into the FROM clause of the outer query and rewriting expressions in the
outer query that refer to the result set of the subquery. For example:

So your "SELECT r AS r1, r AS r2 FROM ( SELECT random() AS r FROM ( SELECT
* from ( VALUES(1),(2) ) ) a ) b;" ​be​comes

"SELECT random() AS r1, random() AS r2 FROM ( SELECT * from (values(1),(2))
a) b;"

I think the above is born out if you put the word DISTINCT in front of the
random() in the original example. This forces SQLite to _not_ use subquery
flattening. Example:

sqlite> SELECT  r AS r1, r AS r2 FROM ( SELECT distinct random() AS r FROM
( SELECT * from ( VALUES(1),(2) ) c ) a ) b;
--EQP-- 1,0,0,SCAN SUBQUERY 2 AS c
--EQP-- 1,0,0,USE TEMP B-TREE FOR DISTINCT
--EQP-- 0,0,0,SCAN SUBQUERY 1 AS b
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 0 000  Start at 0
1 InitCoroutine  1 18200
sqlite_sq_55E270A58EA8
2 InitCoroutine  2 8 300
sqlite_sq_55E270A58AA8
3 Integer1 3 000  r[3]=1
4 Yield  2 0 000
5 Integer2 3 000  r[3]=2
6 Yield  2 0 000
7 EndCoroutine   2 0 000
8 OpenEphemeral  3 0 0 k(1,B) 08  nColumn=0
9 InitCoroutine  2 0 300
10  Yield  2 17000  next row of
"sqlite_sq_55E270A58AA8"
11  Function0  0 0 5 random(0)  00  r[5]=func(r[0])
12  Found  3 165 1  00  key=r[5]
13  MakeRecord 5 1 600  r[6]=mkrec(r[5])
14  IdxInsert  3 6 000  key=r[6]
15  Yield  1 0 000
16Goto   0 10000
17EndCoroutine   1 0 000
18InitCoroutine  1 0 200
19  Yield  1 24000  next row of
"sqlite_sq_55E270A58EA8"
20  Copy   5 7 000  r[7]=r[5];
sqlite_sq_55E270A58EA8.r
21  Copy   7 8 000  r[8]=r[7]
22  ResultRow  7 2 000  output=r[7..8]
23Goto   0 19000
24Halt   0 0 000
920225462863128947|920225462863128947
-723158119245037038|-723158119245037038

​Hope this at least explains what is happening. I 

[sqlite] random value get re-generated too often in SQLite

[John Mount]

I am seeing an issue where a random value in a sub-query gets re-generated each 
time the value is used in later queries.  Below is an example query:

SELECT r AS r1, r AS r2 FROM ( SELECT random() AS r FROM ( SELECT * from ( 
VALUES(1),(2) ) ) a ) b

One would expect r1 == r2.

---
John Mount
http://www.win-vector.com/ <http://www.win-vector.com/> 
Our book: Practical Data Science with R http://www.manning.com/zumel/ 
<http://www.manning.com/zumel/>



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

Re: [sqlite] Why Unicode is difficult

[John G]

Fascinating article.
Thanks.
John Gillespie


On 4 December 2017 at 13:08, Simon Slavin <slav...@bigfraud.org> wrote:

> Every so often someone asks on this list for Unicode to be handled
> properly.  I did it myself.  Then other people have to explain how hard
> this is.  So here’s an article which, after introductory material,
> discusses the hard questions in Unicode:
>
> <https://norasandler.com/2017/11/02/Around-the-with-Unicode.html>
>
> Are two strings the same?
> How long is a string?
> How do you sort things in alphabetical order?
>
> The first and third questions are requirements for implementing COLLATE in
> SQLite.  And the fact that the second question is a difficult one
> emphasises that one shouldn’t take Unicode as simple.
>
> 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

Re: [sqlite] Emulate right-join

[John McKown]

; What I want to do is essentially:
> select EventID,ContactInfo,TicketID,Priority,PriorityText,CreateDate from
> Events *RIGHT JOIN* ColorScheme on ColorScheme.PriorityLevel =
> Events.EventID order by {SomeSoftwareDefinedOrder}
>
> What this did in MSSQL2000 days, if I remember correctly, give me all
> results in the Events table even if the relevant info isn't in ColorScheme
> table.  The PriorityLevel and PriorityText would be returned as NULL.
>
> Does anyone have any working theories on how I can get ALL results in the
> Events table regardless if the Events.Priority isn't in
> ColorScheme.PriorityLevel?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[John G]

I agree - keep the list on email. Simple, convenient.

John Gillespie

On 22 November 2017 at 19:49, Niall O'Reilly <niall.orei...@ucd.ie> wrote:

> On 21 Nov 2017, at 16:27, Drago, William @ CSG - NARDA-MITEQ wrote:
>
> > Please, not a forum. The email list is instant, dynamic, and convenient.
> I don't think checking into a forum to stay current with the brisk activity
> here is very practical or appealing.
>
> I agree with Bill on this.
>
> It seems to me that the idea of re-architecting such a useful
> communications
> channel as this mailing list on account of a cluster of false positives
> raised
> by a single provider's triage system would best be characterized as an
> example
> of "the tail wagging the dog".
>
> I use this provider's service for the major bulk of my e-mail because the
> university where I used to work, which provides a continued e-mail service
> to retirees, long ago outsourced its previously in-house e-mail system,
> which I once had a hand in running, to Google.
>
> In my experience, this provider's triage system does a pretty good job,
> with very few false positives.  I see the current high incidence of
> mis-classification of messages received through the SQLite mailing list
> as an aberration.
>
> Since the triage system is open to tuning by each recipient for their own
> incoming mail, I suggest that all that is needed is for each subscriber to
> this list who depends (as I do) on GMail for their mail feed, to apply this
> tuning for themselves.
>
> I found instructions here: https://support.google.com/mail/answer/6579 and
> have now set up the following filter:
>
>   Matches: to:(sqlite-users@mailinglists.sqlite.org)
>   Do this: Never send it to Spam
>
>
> Best regards,
>
> Niall O'Reilly
>
> ___
> 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] Many ML emails going to GMail's SPAM

[John McKown]

On Tue, Nov 21, 2017 at 10:27 AM, Drago, William @ CSG - NARDA-MITEQ <
william.dr...@l3t.com> wrote:

> > I really need to come up with an alternative to the mailing list.
> > Perhaps some kind of forum system.  Suggestions are welcomed.
> > --
> > D. Richard Hipp
> > d...@sqlite.org
>
> Please, not a forum. The email list is instant, dynamic, and convenient. I
> don't think checking into a forum to stay current with the brisk activity
> here is very practical or appealing.
>

​I completely agree. The problem with a forum is mainly that it is not _a_
forum. It is a forum per list. Which means I spend way too much time
"polling" 8 to 10 web "forums" during the day just to see if anybody has
said anything of interest.


>
> --
> Bill Drago
> Staff Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / william.dr...@l3t.com
>

-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] make install tries to update /usr/share/tcl8.6 regardless of --prefix= value

[John McKown]

Just as background: I updated my sqlite3 directory using the "fossil pull
&& fossil checkout branch-3.21 --force" commands. I then did a "autoconf",
followed by an
"./configure --prefix=$PWD/bld ...". I then did a "make" followed by a
"make install". The first "make" ran just fine. But the "make install"
failed on the command:

/usr/bin/install -c -d /usr/share/tcl8.6/sqlite3

I may be totally off my bird, but I would have hope that a "make install",
in my case, would have tried to install the tcl portions into, perhaps:

$PWD/bld/tcl8.6/sqlite3

I don't know if the action that I saw is intentional or not.

-- 
I have a theory that it's impossible to prove anything, but I can't prove
it.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] very sqlite3 noobie error

[John R. Sowden]

thank you.  i think there are too many errors in this guide for me to 
use it.


John


On 10/22/2017 09:03 PM, Keith Medcalf wrote:

There are a number of syntax errors.

Line 1 - You have an / that should not be there.
Line 6 - You cannot call a module, only a function in a module (not strictly 
true, but in the case of pysqlite2, it is)
Line 12 - You are missing the closing quote on the SQL string
Line 17 - You have not indented the line

As for your syntax error, you probably have crap on a line that you think is 
empty.  Or your editor is not putting in line endings properly and you have all 
one long continuation line.  The syntax error was detected at the opening 
bracket on line 6 but your actual error occurred long before this.

You may need to use a better text editor, or one that you are more familiar 
with.

---
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 John R. Sowden
Sent: Sunday, 22 October, 2017 20:59
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] very sqlite3 noobie error

Since I am trying to learn sqlite3 (unlearning foxpro) I find that
python is the simpleist language, wfich allows me to focus on sqlite,
I
amtrying the =guide just sent to the list.

I am getting a syntax error on line 5.  below is the program copied
by
hand from the guide:

--
#/!python
# sqlite3_test, a test of python and sqlite

import sqlite3

connection = sqlite3(':memory:')

# create a table
connection.execute('create table events(ts, msg)')

# insert values
connection.execute('insert into events values(?,?),
    [(1, 'foo'), (2,'bar'), (3,'baz') ] )

# print inserted rows
for row in connection.execute('select * from events'):
print(row)
---

can anyone tell me where i am missing something?

John

___
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] very sqlite3 noobie error

[John R. Sowden]

^C./sqlite3_test.py: line 7: syntax error near unexpected token `('
./sqlite3_test.py: line 7: `connection = sqlite3.connect(':memory:')'
john@sentry35:~$

On 10/22/2017 08:44 PM, Simon Slavin wrote:

connection = sqlite3.connect(':memory:')


same
john



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

Re: [sqlite] very sqlite3 noobie error

[John R. Sowden]

I just checked my file with a hex  editor (ghex) and found they are all 
hex 27.


John


On 10/22/2017 08:27 PM, Simon Slavin wrote:

On 23 Oct 2017, at 4:13am, John R. Sowden <jsow...@americansentry.net> wrote:


error from terminal program:
./sqlite3_test.py: line 6: syntax error near unexpected token `('
./sqlite3_test.py: line 6: `connection = sqlite3(':memory:')'
john@sentry35:~$

Please make absolutely sure that you are using the quote characters python 
expects.  Your text editor may have turned apostrophes into smart quotes.

If that’s not the problem then please try replacing that line with

connection = sqlite3.connect(':memory:')

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

Re: [sqlite] very sqlite3 noobie error

[John R. Sowden]

i just switched to python3 - no difference in error


On 10/22/2017 08:13 PM, John R. Sowden wrote:

error from terminal program:
./sqlite3_test.py: line 6: syntax error near unexpected token `('
./sqlite3_test.py: line 6: `connection = sqlite3(':memory:')'
john@sentry35:~$

version 2.7.12 (ubuntu 16,04 lts)

John




On 10/22/2017 08:07 PM, Igor Korot wrote:

Hi, John,

On Sun, Oct 22, 2017 at 9:59 PM, John R. Sowden
<jsow...@americansentry.net> wrote:
Since I am trying to learn sqlite3 (unlearning foxpro) I find that 
python is
the simpleist language, wfich allows me to focus on sqlite, I 
amtrying the

=guide just sent to the list.

I am getting a syntax error on line 5.  below is the program copied 
by hand

from the guide:

What is the exact error message you received?
Please copy and paste it here in reply?

Also, what python version do you have?

Thank you.


--
#/!python
# sqlite3_test, a test of python and sqlite

import sqlite3

connection = sqlite3(':memory:')

# create a table
connection.execute('create table events(ts, msg)')

# insert values
connection.execute('insert into events values(?,?),
    [(1, 'foo'), (2,'bar'), (3,'baz') ] )

# print inserted rows
for row in connection.execute('select * from events'):
print(row)
---

can anyone tell me where i am missing something?

John

___
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] very sqlite3 noobie error

[John R. Sowden]

error from terminal program:
./sqlite3_test.py: line 6: syntax error near unexpected token `('
./sqlite3_test.py: line 6: `connection = sqlite3(':memory:')'
john@sentry35:~$

version 2.7.12 (ubuntu 16,04 lts)

John




On 10/22/2017 08:07 PM, Igor Korot wrote:

Hi, John,

On Sun, Oct 22, 2017 at 9:59 PM, John R. Sowden
<jsow...@americansentry.net> wrote:

Since I am trying to learn sqlite3 (unlearning foxpro) I find that python is
the simpleist language, wfich allows me to focus on sqlite, I amtrying the
=guide just sent to the list.

I am getting a syntax error on line 5.  below is the program copied by hand
from the guide:

What is the exact error message you received?
Please copy and paste it here in reply?

Also, what python version do you have?

Thank you.


--
#/!python
# sqlite3_test, a test of python and sqlite

import sqlite3

connection = sqlite3(':memory:')

# create a table
connection.execute('create table events(ts, msg)')

# insert values
connection.execute('insert into events values(?,?),
[(1, 'foo'), (2,'bar'), (3,'baz') ] )

# print inserted rows
for row in connection.execute('select * from events'):
print(row)
---

can anyone tell me where i am missing something?

John

___
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] very sqlite3 noobie error

[John R. Sowden]

Since I am trying to learn sqlite3 (unlearning foxpro) I find that 
python is the simpleist language, wfich allows me to focus on sqlite, I 
amtrying the =guide just sent to the list.


I am getting a syntax error on line 5.  below is the program copied by 
hand from the guide:


--
#/!python
# sqlite3_test, a test of python and sqlite

import sqlite3

connection = sqlite3(':memory:')

# create a table
connection.execute('create table events(ts, msg)')

# insert values
connection.execute('insert into events values(?,?),
   [(1, 'foo'), (2,'bar'), (3,'baz') ] )

# print inserted rows
for row in connection.execute('select * from events'):
print(row)
---

can anyone tell me where i am missing something?

John

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

Re: [sqlite] checking if a table exists

[John McMahon]

On 23/09/2017 05:36, mikeegg1 wrote:

I’m using the statement:

select count(*) from sqlite_master where type = 'table' and name = ‘$NAME’;

This statement works fine in the sqlite3 shell. This statement does not work in 
my API.
Is there a PRAGMA I need to issue so I can check for table existence?

TIA

Mike

PERL code to check for table existence:


#> sub tableexists($$) {
prototyping depricated practice carried over from perl 4

sub tableexists {


 my $dbh = shift;
 my $name = shift;
 my $tableexists = 0;


#>  $dbh->do("pragma writable_schema = 'on';");
# neither recommended nor needed



#>  my $sql = "select count(*) from sqlite_master where type = 
'table' and name = '$name';";
my $sql = "select count(*) from sqlite_master where type = 'table' and 
name = ?;";



 my $stmt = $dbh->prepare($sql);
#>  $stmt->execute or die "$0: verifying table name failed: 
$DBI::errstr";

$stmt->execute($name)
or die "$0: verifying table name failed: $DBI::errstr";


 while(my @row = $stmt->fetchrow_array) {
 $tableexists = $row[0];
 }
 $stmt->finish;


#>  $dbh->do("pragma writable_schema = 'off';");


 return $tableexists;
}


Alternatively (not tested)

sub tableexists {
my $dbh = shift;
my $name = shift;

my $sql = "select count(*) from sqlite_master where type = 'table' 
and name = '$name';";

# stmt only executed once, $name only evaluated once

my ($tableexists) = $dbh->selectrow_array($sql);
# selectrow returns 1 row, the stmt returns 1 element  in list context

return $tableexists;
}

my $check_table = tableexists($dbh, $name);

John

--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

Re: [sqlite] Need help with SQL query

[John G]

I know this is an older thread, but shouldn't that reference be on the ITEM
table ?  So ...

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

John G

On 11 September 2017 at 13:11, Kees Nuyt <k.n...@zonnet.nl> wrote:

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

Re: [sqlite] Wanting to return REAL as formatted TEXT

[John McKown]

On Wed, Sep 20, 2017 at 11:01 AM, Phoenix <rwm.phoe...@btinternet.com>
wrote:

> I am trying to retrieve some numeric data from a table using COBOL (not
> a 'C' programmer).
>
> sqlite3_column_type says its type '2' so I am using
> sqlite3_column_double to return the data. The problem is that the
>

​SQLite3 is different from many other RDMS systems. In something like
PostgreSQL, I would do an SQL similar to " SELECT REAL_COLUMN::TEXT FROM
TABLE;" to cas​t the real data to a text value. In SQLite3, you can simply
sue the "sqlite3_column_text" to return the data. The SQLite3 engine will
"cast" the real data to a textual form.

On page: http://sqlite.org/c3ref/column_blob.html , it states:

The first six interfaces (_blob, _double, _int, _int64, _text, and _text16)
each return the value of a result column in a specific data format. If the
result column is not initially in the requested format (for example, if the
query returns an integer but the sqlite3_column_text() interface is used to
extract the value) then an automatic type conversion is performed.



> language I am using does not seem to like REAL numbers as I'm getting a
> zero value.
>
> From what I have been able to workout it should be possible to return a
> REAL number as a formatted text string, which would make things easier
> for me, but am not sure of the details to do it.
>
> I am not looking for somebody to do this, just some breadcrumbs to point
> me in the correct direction.
>
> regards, Robert
>
>
-- 
*L'Shanah Tovah Tikatevu*

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a performance difference between COUNT(*) and COUNT(name)

[John Found]

On Tue, 5 Sep 2017 23:31:32 +0200
Cecil Westerhof <cldwester...@gmail.com> wrote:

> 2017-09-05 23:11 GMT+02:00 Simon Slavin <slav...@bigfraud.org>:
> 
> >
> >
> > On 5 Sep 2017, at 9:21pm, Cecil Westerhof <cldwester...@gmail.com> wrote:
> >
> > > I want to know the number of teas I have in stock. For this I use:
> > > SELECT COUNT(Tea)
> > > FROM   teaInStock
> > >
> > > Tea cannot be NULL, so this is the same as:
> > > SELECT COUNT(*)
> > > FROM   teaInStock
> > >
> > > ​But I find the first more clear.
> > > I almost always see the second variant. Is this because it is more
> > > efficient, or are people just ‘lazy’?​
> >
> > Your guess is right !
> >
> > To do COUNT(*) SQLite has to retrieve all the rows.
> > To do COUNT(value) has to retrieve all the rows and test the value of each
> > row to make sure it is not NULL.
> >
> > Also, SQLite has a specific piece of code which makes COUNT(*) more
> > efficient than counting the values.  However, unless you have a big
> > database, the difference for your case may be small.  If you find
> > COUNT(Tea) easier to understand perhaps you should use that one.
> >
> 
> ​I will keep using COUNT(Tea) then, but keep in the back of my mind that I
> maybe should change that if a table becomes big.

In my tests even on small tables count(colName) is at least 2 times slower than
count(*), even if both queries uses covering indexes. So, using count(colName) 
has
meaning only if you really want to count only not null rows.

Making exception for columns that "never contain NULL" in the name of "source 
clearness" actually is hard for detection hidden bug that can strike after long 
time on the database schema change.


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


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found <johnfo...@asm32.info>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a performance difference between COUNT(*) and COUNT(name)

[John McKown]

   2 0 data   00  iDb=0 root=2
write=0
14Goto   0 1 000
sqlite>

​
​NOT NULL doesn't make a difference. The EXPLAIN shows the operations, and
the first seems to be much more efficient; fewer steps & no loop.​



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



-- 
Caution! The OP is an hyperpolysyllabicsesquipedalianist and this email may
cause stress to those with hippopotomonstrosesquipedaliophobia.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting number of rows with NULL

[John McKown]

On Tue, Sep 5, 2017 at 3:00 PM, Stephen Chrzanowski <pontia...@gmail.com>
wrote:

> On behalf of Cecil, the fault in that logic is that count(*) returns the
> number of rows in that table, not whether there is a hole "somewhere:  Your
> query will either return 1, or, 0.
>
>
​I either don't understand you, or I am doing something wrong. I used "a"
instead of "Last Used" in my example because I'm just plain lazy.

QLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x (a date,b integer);
sqlite> insert into x values(NULL, 1);
sqlite> insert into x values(NULL, 2);
sqlite> insert into x values('2017-09-01', 2);
sqlite> insert into x values('2017-09-02', 3);
sqlite> insert into x values('2017-09-04', 4);
sqlite> select count(*) from x;
5
sqlite> select count(*) from x where a is NULL;
2
sqlite> select count(*) from x where a is NOT NULL;
3
sqlite>
​


-- 
Caution! The OP is an hyperpolysyllabicsesquipedalianist and this email may
cause stress to those with hippopotomonstrosesquipedaliophobia.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question of Table/Indices common to multiple Databases

[John R. Sowden]

Thank you all for your feedback.  I now have a lot more to digest.  I 
will investigate the attach command.  I am concerned about keeping all 
of my company's data in 1 file, as if something happened to that file, I 
would have data entry, programming, etc. to on all systems since the 
last backup, not just 1.  That might cause a day's worth of  'business 
interruption'.


On 08/31/2017 07:15 PM, Keith Medcalf wrote:

Now, there are some unusual situations where you might want to hold
your customers in a different database from your invoices and use
SQLite to hold both databases open at once using ATTACH.  I’ll let
other people argue about that.

But of course if you do that, then you cannot have the database enforce 
referential integrity and you have to do it yourself.




___
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] Question of Table/Indices common to multiple Databases

[John R. Sowden]

I have been using the xbase language (dbase, foxpro, etc.) for about 36 
years, writing applicatios for my alarm company, so each database is a 
separate file for me.  For the last 21 years, I have been using Linux, 
and have found that sqlite is my best match for Linux database use.


What I fail to understand is how I set up my files/databases.  I have 
categories that I write for: accounting, dispatching, service, billing, 
etc.  Some (most) of these use customer data, so when I am writing code 
for the billing program, and I want to reference the customers, is that 
a separate file, so I only have 1 customer file to update (the 
relational model)?  Having a customer table, with indices,  in each 
category's database (file) breaks the relational model.  I have been on 
this list for about a year and see no reference to this issue.  I am 
reading now about sqlite in _The Definitive Guide to SQLite_ by Michael 
Owens, but I'm early in the book.


Help?

John

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

[sqlite] pragma statement not parsing argument as 'numeric-literal'

[john brzustowski]

Hi folks,
The syntax diagram for the PRAGMA statement here:

   http://www.sqlite.org/syntaxdiagrams.html#pragma-stmt

reaches the "numeric-literal" production for its argument, but the
parser doesn't seem to do so:

   sqlite> PRAGMA busy_timeout=3e+5;
   3

   ## platform / version:

   $ uname --all
   Linux sgdata 3.16.0-4-amd64 #1 SMP Debian 3.16.36-1+deb8u2 (2016-10-19) 
x86_64 GNU/Linux
   $ sqlite3 --version
   3.16.2 2017-01-06 16:32:41 a65a62893ca8319e89e48b8a38cf8a59c69a8209

So a user who isn't being careful enough (i.e. me) and supplies a
calculated milliseconds value that happens to be formatted in
exponential notation as above is getting a 3 millisecond (not 5
minute) busy_timeout.  And that same careless user is likely
(i.e. was) not checking what the PRAGMA statement prints.

I'm only reporting this because it (i.e. user carelessness) turned out
to be the cause of a long-sought bug in some of my own code.
Hopefully, just this report might save someone else similar grief.

Key phrases: 
interprocess database-locking, pragma busy_timeout, database is locked

Thanks!

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

Re: [sqlite] My 1st C++ SQLite3 program

[John McKown]

On Mon, Aug 28, 2017 at 1:11 PM, Papa <p...@arbolone.ca> wrote:

> Thanks everyone.
>
> The problem was resolved by rebooting the computer, strange ain't it?
>

​Ah, yes, the main "solution" to most Windows problems. It's what our
desktop ask first - have you rebooted? Yes, and it didn't work? Did you
power cycle the PC? The best that I've gotten so far: Unplug the network
cable; turn off the PC; unplug the power cable; wait 10 minutes; reattach
the network cable; plug the PC back in; power up. I guess the next step is
"shoot either the machine or yourself".



>
>
> On 2017-08-27 10:55 AM, Tim Streater wrote:
>
>> On 27 Aug 2017, at 15:35, Papa <p...@arbolone.ca> wrote:
>>
>> First and foremost, I'd like to thank everybody for your replies.
>>> Although I have sound knowledge and understanding of C++ [ that can be
>>> debatable ] and still remember a little how C works, I have no Idea how
>>> to utilize the API of SQLitle3. Therefore, it is very difficult for me
>>> to apply the advice given by you all, so, please, using the code I have
>>> provided in the OP, how can I implement your advice?
>>>
>>> Again, thanks ya'll for all the help.
>>>
>>>
>>> On 2017-08-26 11:28 PM, Simon Slavin wrote:
>>>
>>>> On 27 Aug 2017, at 2:34am, Papa <p...@arbolone.ca> wrote:
>>>>
>>>> Why do I get this error message?
>>>>>
>>>>   Try having the program using file calls to open a simple text file in
>>>> the
>>>> same directory.  Does that succeed ?
>>>>
>>> Do what Simon suggests here. It does not require the SQLite3 API.
>>
>>
>> --
>> Cheers  --  Tim
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> --
> ArbolOne.ca
> Using Fire Fox and Thunderbird.
> ArbolOne is composed of students and volunteers dedicated to providing
> free services to charitable organizations.
> ArbolOne on Java Development in progress [ í ]
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
If you look around the poker table & don't see an obvious sucker, it's you.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Update With CTE

[John McMahon]

Thanks Keith


On 23/08/2017 00:06, Keith Medcalf wrote:


You could also -- if using a version of SQLite3 that supports row values 
(3.15.0 and later) -- do something like this:
SQLite version 3.15.1 2016-11-04 12:08:49, I usually update near the end 
of year unless I see something particularly interesting, eg. CTEs when 
they were introduced.




UPDATE CUSTOMERS
SET (cust1, cust2, street, town, postcode) = (SELECT customer, NULL, 
address, town, postcode
FROM test
   WHERE custnum = 
customers.custnum)
  WHERE custnum in (select custnum from test);

It will get all the updates in a single correlated subquery rather than four ...
Thank you, I just compared the drawings in "lang_update.html" for 
versions 3.10 and 3.15, I had missed that and it is functionality I was 
wishing for.




---
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 John McMahon
Sent: Monday, 21 August, 2017 22:25
To: SQLite Users
Subject: [sqlite] SQLite Update With CTE

Hi

I am rewriting an old Perl script that selectively updates data from
one
table to another using this statement:

UPDATE CUSTOMERS
SET
 cust1= ?,
 cust2= NULL,
 street   = ?,
 town = ?,
 postcode = ?
  WHERE custnum = ?

I am intending to replace it with something like this where 'test' is
the CTE:

UPDATE CUSTOMERS as c
SET
 cust1= (select customer from test where custnum =
c.custnum),
 cust2= NULL,
 street   = (select address  from test where custnum =
c.custnum),
 town = (select town from test where custnum =
c.custnum),
 postcode = (select postcode from test where custnum = c.custnum)
  WHERE custnum = (select custnum from test where custnum =
c.custnum)

My question is, do I need this part of the statement:
  WHERE custnum = (select custnum from test where custnum =
c.custnum)

when I have the other 'where custnum = c.custnum' clauses.

I came across some Web examples that suggest that I might not. I
haven't
tested yet and am a little unsure.

Any guidance would be appreciated.

John

--
Regards
John McMahon
   li...@jspect.fastmail.fm


___
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



--
Regards
   John McMahon
  li...@jspect.fastmail.fm


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

Re: [sqlite] SQLite Update With CTE

[John McMahon]

On 22/08/2017 16:41, Clemens Ladisch wrote:

John McMahon wrote:

UPDATE CUSTOMERS as c
SET
 cust1= (select customer from test where custnum = c.custnum),
 cust2= NULL,
 street   = (select address  from test where custnum = c.custnum),
 town = (select town from test where custnum = c.custnum),
 postcode = (select postcode from test where custnum = c.custnum)
  WHERE custnum = (select custnum from test where custnum = c.custnum)

My question is, do I need this part of the statement:
  WHERE custnum = (select custnum from test where custnum = c.custnum)

when I have the other 'where custnum = c.custnum' clauses.


The WHERE clause on the UPDATE itself filters the rows that will be
updated.

If you know that "test" contains new values for all customers, you do
not need the WHERE. But if you (might) update only a subset of
customers, you need it.


it doesn't




And that last subquery is not used for assignment, so writing it in
a different form might be clearer:
   WHERE EXISTS (SELECT * FROM test WHERE custnum = c.custnum)
or
   WHERE custnum IN (SELECT custnum FROM test)


And UPDATE does not support AS.


So this
UPDATE CUSTOMERS as c
SET
cust1= (select customer from test where custnum = c.custnum),
cust2= NULL,
...
should be
UPDATE CUSTOMERS -- remove 'as c'
SET
cust1= (select customer from test where custnum = c.custnum),
change to
cust1= (select customer from test where custnum = 
CUSTOMERS.custnum), -- excuse the line wrap


or perhaps
cust1= (select customer from test as t where t.custnum = custnum),

cust2= NULL,
...

and end with
 WHERE custnum IN (SELECT custnum FROM test)

Thank you,
John




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



--
Regards
   John McMahon
j...@jspect.fastmail.com.au
04 2933 4203

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