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] 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] 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] 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

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] 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] 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] 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

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] 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  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
> >Enter ".help" for usage hints.
> >Connected to a transient in-memory database.
> >Use ".open FILENAME" to reopen on a persistent database.
> >sqlite> .head on
> >sqlite> .mode col
> >sqlite> create table x(a int_date);
> >
> >sqlite> pragma table_info(x);
> >cid nametypenotnull dflt_value  pk
> >--  --  --  --  --  -
> >-
> >0   a   int_date0   0
> >
> >create view if not exists 

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] Why Unicode is difficult

[John G]

Fascinating article.
Thanks.
John Gillespie


On 4 December 2017 at 13:08, Simon Slavin  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:
>
> 
>
> 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] 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  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] 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] Which pragmas are persistent?

[John G]

PRAGMA foreign_keys=1   is  transient, but it would be nice if it were
persistent.

John G

On 13 April 2017 at 12:35, Tony Papadimitriou <to...@acm.org> wrote:

> -Original Message- From: no...@null.net
>
>> What would be useful (at least via the shell CLI) is a "list_pragmas"
>> pragma that shows for example something like this:
>>
>>sqlite> PRAGMA list_pragmas;
>>
>
> PRAGMA list;
>
> would be less redundant.
>
> ___
> 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] Tcl Interface - minor docs suggestion

[John G]

Having used the Tcl interface to SQLite for 10+ years I was caught out when
accessing someone else's DB. I don't see a satisfactory way to fix it, but
a warning would help.

Example to illustrate:

sqlite3 dbcmd grbg.db
package require sqlite3
dbcmd eval {create table a (Xyz text)}
dbcmd eval {insert into a (xyz) values ('z')}
dbcmd eval {insert into a (XYZ) values ('y')}
dbcmd eval {insert into a (XyZ) values ('x')}

dbcmd eval "select xyz from a"{  puts "$xyz"  };#== gives
error: can't read "xyz": no such variable
dbcmd eval "select xyz from a" v {  puts "$v(xyz)" } ;#== gives error:
can't read "v(xyz)": no such element in array

dbcmd eval "select xyz as xyz from a"  { puts $xyz };#== ouput is as
expected
z
y
z

Apparently the TCL variable names set by "eval' are in the same case as the
column names orginally defined in the CREATE statement* unless an alias is
used.*

In the docs ( http://www.sqlite.org/tclsqlite.html ) this section on "eval"
has a paragraph which begins:

If the array variable name is omitted or is the empty string, then the
> value of each column is stored in a variable with the same name as the
> column itself.
>

Perhaps a warning could be added like "The case of the variable is the same
as in the table definition unless a column alias is used."

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

Re: [sqlite] Command line not accepting multi-line statements.

[John G]

Thanks, that worked.

John Gillespie

On 8 November 2016 at 14:30, Richard Hipp <d...@sqlite.org> wrote:

> On 11/8/16, John G <rjkgilles...@gmail.com> wrote:
> > I normally use the 3.8.8.3 supplied with MacOS El Capitan.
> > I downloaded version 3.15.1 from the Download page - precompiled
> > command-line tools : (sqlite-tools-osx-x86-3150100.zip).
> >
> > When I tried copying and pasting multiple or multi-line statements from
> my
> > editor (jEdit) the command-line shell ignored everything after the first
> > line. This is not the case with 3.8.8.3.
> >
>
> This appears to be a bug in the "linenoise" library that we link
> against when building the precompiled shell - it has nothing to do
> with SQLite.  See the https://github.com/antirez/linenoise/issues/75
> bug report.  We first started linking precompiled SQLite binaries
> against linenoise with 3.8.9 (2015-04-08) and you are the first person
> to notice the difference.
>
> We'll see if we can't update the linenoise implementations on our
> build machines and upload new binaries for 3.15.1
>
> In the meantime, you can always build SQLite yourself from sources.
> On a mac, just type "./configure; make" (after installing Xcode, which
> is free on the apple store).
>
> --
> 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] Command line not accepting multi-line statements.

[John G]

I normally use the 3.8.8.3 supplied with MacOS El Capitan.
I downloaded version 3.15.1 from the Download page - precompiled
command-line tools : (sqlite-tools-osx-x86-3150100.zip).

When I tried copying and pasting multiple or multi-line statements from my
editor (jEdit) the command-line shell ignored everything after the first
line. This is not the case with 3.8.8.3.

If I put ALL the statements on a single long  line they are accepted.

Is this a bug? Am doing something wrong? Tongue in wrong position?

Thanks for any help.

John Gillespie


Text in my editor:

create table people (pid integer primary key,name text);
insert into people values(1, 'John Smith');
insert into people values(2, 'Alan Smith');
insert into people values(3, 'Elsie Jones');
select * from people
 where name like '%Smith';
    I copied this block of text and pasted to the
command-line shells  ...


Using 3.8.8.3 from bash :

sqlite 528 % sqlite3
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table people (pid integer primary key,name text);
sqlite> insert into people values(1, 'John Smith');
sqlite> insert into people values(2, 'Alan Smith');
sqlite> insert into people values(3, 'Elsie Jones');
sqlite> select * from people
   ...>  where name like '%Smith';
1|John Smith
2|Alan Smith
sqlite>
    as expected

Using 3.15.1 from bash :

sqlite 527 % ~/bin/sqlite3_15
SQLite version 3.15.1 2016-11-04 12:08: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 people (pid integer primary key,name text);
sqlite>
 - input truncated at first line
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual table

[John G]

1) Can't see the 'attachment' - this list does not accept them.
Can you copy/paste sample code into an email?

John G



On 31 August 2016 at 14:34, Maria de Jesus Philadelpho <
jesus.ph...@gmail.com> wrote:

> Hi,
>
> I implement the SQLite extension, virtcsv, which allows attaching a CSV
> file as a virtual table.  At a command line everything works just fine, why
> I can't see the result of a select * from table using the SQLitestudio
> 3.0.7?
> See the attached snapshot.
>
> regards,
>
> Maria Azevedo
> --
> ___
> 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] Version of the database

[John G]

I don't see  the '.dbinfo' command in the shell in the version supplied
with MacOS X 10.10 (Yosemite) - 3.8.8.3.
Was this introduced after this.
I know Apple is a bit slow in updating, but I can't use a personally
compiled verion.
John G



On 5 May 2016 at 23:42, R Smith <rsm...@rsweb.co.za> wrote:

> The file header contains the SQLite version that most recently modified
> the schema. You can see this using the cli, but not a pragma.
>
> If you are willing to dig a bit, you can retrieve it by reading the first
> 100 bytes or so from the file and examining the 4 bytes at offset 96. It's
> a big-endian 32-bit integer containing the version, and also another 32-bit
> integer (4-byte) value just prior at offset 92 is a counter of how many
> changes were made since using that library version. Together these can be
> quite useful information.
>
> The Integer value of the version would be a number like this: 3012034 -
> starting with a 3 always for SQLite3 and then the next 3 digits the minor
> version (12 in the example) and the last 3 the release (34 in the example).
>
> For more information on values stored in the header - see here:
> https://www.sqlite.org/fileformat2.html#database_header
>
> Cheers,
> Ryan
>
>
> On 2016/05/05 6:22 PM, Cecil Westerhof wrote:
>
>> 2016-05-05 18:15 GMT+02:00 Cecil Westerhof <cldwester...@gmail.com>:
>>
>> I know how to get the version of the running version of SQLite, but is
>>> there a way to get the version with which the database was created?
>>>
>>> ​With the command line program I can get it with:
>>  .dbinfo
>> one of the things it gives is:
>>  software version:3008010
>> so it is 3.8.10,but would it also be possible to get in my Java program?
>>
>>
> ___
> 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] Problem with sqldiff

[John G]

Richard
Thanks. That gives me what I would expect.

John Gillespie

sqlite-src-30902T 573 % ./sqldiff z1.db z2.db
ALTER TABLE A ADD COLUMN category;
UPDATE A SET category='' WHERE rowid=1;
UPDATE A SET category='' WHERE rowid=2;
UPDATE A SET category='' WHERE rowid=3;

On 7 November 2015 at 18:37, Richard Hipp  wrote:

> On 11/7/15, John G  wrote:
> > I saw sqldiff mentioned and decided to try it (compiled from
> > sqlite-src-3090200) and got an SQL error.
> >
>
> Please try the latest trunk version
> (https://www.sqlite.org/src/artifact/37ab2cd4f0c8b4f0) and let us know
> if that fails to solve your problem.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Problem with sqldiff

[John G]

I saw sqldiff mentioned and decided to try it (compiled from
sqlite-src-3090200) and got an SQL error.

./sqldiff: SQL statement error: near "1": syntax error
"SELECT B.rowid, 1, -- changed row
   A.id IS NOT B.id, B.id,
   A."desc" IS NOT B."desc", B."desc"
  FROM main.A A, aux.A B
 WHERE A.rowid=B.rowid
   AND (A.id IS NOT B.id
OR A."desc" IS NOT B."desc")
 UNION ALL
SELECT A.rowid, 2, -- deleted row
   NULL, NULL,
   NULL, NULL
  FROM main.A A
 WHERE NOT EXISTS(SELECT 1 FROM aux.A B
   WHERE A.rowid=B.rowid)
 UNION ALL
SELECT B.rowid, 3, -- inserted row
   1, B.id,
   1, B."desc"<= no comma
   1, B.,<== extra null
column
  FROM aux.A B
 WHERE NOT EXISTS(SELECT 1 FROM main.A A
   WHERE A.rowid=B.rowid)
 ORDER BY 1;
"

This occurred on a table to which I had added a column.
The erroneous part is obvious marked.

Is it a conflict between my command-line ( v3.8.8 ) and sqldiff (v3.9.2)?
Is this a bug?
if not, any suggestions?

Here is is the SQL to set up both databases :


sqlite-src-3090200 545 % sqlite3 z1.db
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
sqlite> create table A (id integer, desc text);
sqlite> insert into A values (1,'') , (2,''), (3,'');
sqlite> select * from A;
1|
2|
3|
sqlite> .exit

sqlite-src-3090200 546 % sqlite3 z2.db
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
sqlite> create table A (id integer, desc text);
sqlite> insert into A values (1,'') , (2,''), (3,'');
sqlite> alter table A add category text;
sqlite> update A set category='';
sqlite> select * from A;
1||
2||
3||
sqlite> .exit


Great database - ergo great development team!

John Gillespie

[sqlite] Fwd: OT: Oracle functions for SQlite

[John G]

On 13 September 2015 at 10:06, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> Hello !
>
> Due the way sqlite manages it's source code (with fossil-scm) I propose to
> anyone that has any extension/custom sqlite code fork this project on
> github:
>
>
> https://github.com/mackyle/sqlite
>
> And publish it there.
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Is there an equivalent collection of Tcl - sqlite functions anywhere?
I have my own 'initcap' and 'decode' functions.

John Gillespie

Re: [sqlite] Hints for the query planner

[John G]

Dr Hipp

I second Kyan's suggestion of a pseudo-comment hinting syntax:

... that the planner hint is not interleaved inside normal SQL
> syntax. Instead I propose a special comment-like syntax instead, as
> Oracle's /*+ */ or --+, but replacing "+" with another symbol, e.g. ">":
>

Having had to switch between databases a couple of times, I would prefer
something that keeps the SQL more portable.
As for names I would vote for the 'seldom()' option as the 'likely()'
option is already covered by the default action.

John Gillespie


On 10 September 2013 20:26, Richard Hipp  wrote:

> There is a survey question at the bottom of this message.  But first some
> context...
>
> Over on the sqlite-dev mailing list, a debate has been going on about the
> best way to provide some useful hints to the query planner.  The query
> under discussion looks like this:
>
> SELECT DISTINCT aname
>   FROM album, composer, track
>  WHERE cname LIKE '%bach%'
>AND composer.cid=track.cid
>AND album.aid=track.aid;
>
> Assuming that the schema has appropriate indices and ANALYZE has been run,
> SQLite does a good job of selecting an efficient query plan for the above.
> But the query planner lacks a key piece of information that could help it
> to do a better job.  In particular, the query planner does not know how
> often the subexpression "cname LIKE '%bach%'" will be true.  But, it turns
> out, the best query plan depends critically on this one fact.
>
> By default, the query planner (in SQLite 3.8.0) assumes that a
> subexpression that cannot use an index will always be true.  Probably this
> will be tweaked in 3.8.1 so that such subexpressions will be assumed to
> usually, but not always, be true.  Either way, it would be useful to be
> able to convey to the query planner the other extreme - that a
> subexpression is usually not true.
>
> (Pedantic detail:  "not true" is not the same as "false" in SQL because
> NULL is neither true nor false.)
>
> There is currently code in a branch that provides a hinting mechanism using
> a magic "unlikely()" function.  Subexpressions contained within
> "unlikely()" are assumed to usually not be true.  Other than this hint to
> the query planner, the unlikely() function is a complete no-op and
> optimized out of the VDBE code so that it does not consume any CPU cycles.
> The only purpose of the unlikely() function is to let the query planner
> know that the subexpression contained in its argument is not commonly
> true.  So, if an application developer knows that the string "bach" seldom
> occurs in composer names, then she might rewrite the query like this:
>
> SELECT DISTINCT aname
>   FROM album, composer, track
>  WHERE unlikely(cname LIKE '%bach%')
>AND composer.cid=track.cid
>AND album.aid=track.aid;
>
> The query planner might use this "likelihood" hint to choose a different
> query plan that works better when the subexpression is commonly false.  Or
> it might decide that the original query plan was good enough and ignore the
> hint.  The query planner gets to make that decision.  The application
> developer is not telling the query planner what to do. The application
> developer has merely provided a small amount of meta-information about the
> likelihood of the subexpression being true, meta-information which the
> query planner may or may not use.
>
> Note that the subexpression does not have to be a LIKE operator.
> PostgreSQL, to name one example, estimates how often a LIKE operator will
> be true based on the pattern on its right-hand side, and adjust query plans
> accordingly, and some have argued for this sort of thing in SQLite.  But I
> want a more general solution.  Suppose the subexpression involves one or
> more calls to application-defined functions about which the query planner
> cannot possible know anything.  A general mechanism for letting the query
> planner know that subexpressions are commonly not true is what is desired -
> not a technique for making LIKE operators more efficient.
>
> SURVEY QUESTION:
>
> The question for today is what to call this magic hint function:
>
> (1)  unlikely(EXPR)
> (2)  selective(EXPR)
> (3)  seldom(EXPR)
> (4)  seldom_true(EXPR)
> (5)  usually_not_true(EXPR)
>
> Please feel free to suggest other names if you think of any.
>
> ADDITIONAL INFORMATION:
>
> The current implementation allows a second argument which must be a
> floating point constant between 0.0 and 1.0, inclusive. The second argument
> is an estimate of the probability that the expression in the first argument
> will be true.  The default is 0.05.  Names like "unlikely" or "seldom" work
> well when this probability is small, but if the second argument is close to
> 1.0, then those names seem backwards.  I don't know if this matters.  The
> optional second argument is not guaranteed to make it into an actually
> release.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing