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

[Michael Falconer]

Ahthe inevitable becomes reality. I knew when this stuff surfaced a few
years back that it was not going to go away, and this outcome was just a
matter of time. Not a great a great shock as a life in programming has seen
so much change. I don't need to Fossil, because I am one. Also a bit of a
lurker who has enjoyed lurking here for years because of the excellent
standard of technical replies and the interesting banter that evolves from
differing technical opinions. So I'll take the opportunity now to thank
(too many to single out) the many contributors to this list who have made
it unique, and well...just a nice place to be.

So while you are all off playing with your interface, javascript forum
thingy's I guess I'll have to find somewhere else to go. You'll notice I
did not mention any specific technical objections and that's because
sometimes (even in I.T.) it's just not about the tech! Can't help feeling
that someone just shot my dog!



On Sat, 14 Mar 2020 at 07:01, Jim Dodgen  wrote:

> Another lurker here ...
> I have always preferred mailing lists and found forums to be too fat.  That
> said, I like the fossil forum.  It was referred to bring a "enginerish"
> style which is fine given the audience
> Kudos for its speed and light weight,  No fluff just data.
>
> Jim "Jed" Dodgen
> j...@dodgen.us
>
>
> On Fri, Mar 13, 2020 at 12:24 PM Simon Slavin 
> wrote:
>
> > On 13 Mar 2020, at 6:50pm, Thomas Kurz  wrote:
> >
> > > Why can't the forum just forward all new postings to this mailing list
> > and vice versa? Then everyone could chose what to use ;)
> >
> > I think one of the objectives of moving away from email is to prevent
> > email spam.
> > ___
> > 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,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cascaded delete unexpectedly triggered by upsert

[Michael Kappert]

On 8.01.20 01:04, Simon Slavin wrote:
> On 8 Jan 2020, at 12:00am, Michael Kappert  wrote:
>
>> REPLACE INTO
>
> REPLACE INTO is an alias for INSERT OR REPLACE.  So you should assume that 
> the command will do either an INSERT or a REPLACE.
>
> See the notes about REPLACE on this page:
>
> <https://sqlite.org/lang_conflict.html>
>
> Is the behaviour you're seeing consistent with that documentation ?

Argh, I somehow managed to skip the pointer to that page when reading
this <https://sqlite.org/lang_insert.html>.
So I need to provide an upsert clause to make the replace behave as an
UPDATE.



> ___
> 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] Cascaded delete unexpectedly triggered by upsert

[Michael Kappert]

Hi,

as this is my first posting to this list, let me first say thank you for
providing such a great library. It's fun to work with it. I'm intending
to use SQLite in a game engine.

I've encountered some unexpected behavior of foreign keys w.r.t an upsert:
When an entry of the parent table is 'touched' but not deleted or
modified, the child table entry is nevertheless deleted.
Although the foreign key is declared 'ON DELETE CASCADE ON UPDATE
RESTRICT', I did not expect the child entry to be deleted, because the
parent entry was not deleted.

Here is a script that shows the behavior:
-
PRAGMA foreign_keys = ON;

-- Create tables
CREATE TABLE t1 (id char(36), name varchar(40), CONSTRAINT pk_t1 PRIMARY
KEY (id));
CREATE TABLE t2 (id char(36), t1_id char(36), data char(36), CONSTRAINT
pk_t2 PRIMARY KEY (id), CONSTRAINT fk_t1_id FOREIGN KEY (t1_id)
REFERENCES t1(id) ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE
INITIALLY DEFERRED);

-- Create table content
INSERT INTO T1 (ID, NAME) VALUES ('A', 'line 1');
INSERT INTO T1 (ID, NAME) VALUES ('B', 'line 2');
INSERT INTO T2 (ID, T1_ID, DATA) VALUES ('T2-A', 'A', 'abc');
INSERT INTO T2 (ID, T1_ID, DATA) VALUES ('T2-B', 'B', 'xyz');

-- Modify one parent entry of foreign key fk_t1_id
REPLACE INTO T1 (ID, NAME) VALUES ('A', 'line 1-new');

-- Although the parent field was not modified, a cascading delete is
triggered:
SELECT *  FROM T2;

-- The cascading delete happens even if the upsert is a no-op:
REPLACE INTO T1 (ID, NAME) VALUES ('B', 'line 2');
SELECT *  FROM T2;
--------

If I understand correctly, the upsert should behave like UPDATE in the
examples above, but it behaves like a DELETE followed by INSERT instead?


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

Re: [sqlite] 18 minutes 41 seconds

[Michael Falconer]

>
> There is no "year 0" between 1 BC and 1 AD.  This is perhaps the most
> common fencepost problem in existance.  The "great renaming" of AD to CE
> and doing away with BC by replacing them with "off by one" numbers less
> than 1 does not change the fact that there was, in fact, no year 0.

Obviously the character(s) responsible  for dates etc were NOT C
programmers!

On Tue, 31 Dec 2019 at 14:45, Richard Damon 
wrote:

> On 12/30/19 10:10 PM, Pierpaolo Bernardi wrote:
> > On Tue, Dec 31, 2019 at 4:07 AM Keith Medcalf 
> wrote:
> >>
> >> On Monday, 30 December, 2019 19:29, Michael Falconer <
> michael.j.falco...@gmail.com> wrote:
> >>
> >>> As we approach the end of yet another year ( and indeed decade ).
> >> Technically, every year is the end of a decade, if one means the
> immediately preceding ten years.
> >>
> >> However, if you mean the end of the second decade of the 21st century,
> you will have to wait another year for that.  January 1st, 0001 AD was the
> first day of the year 1.  The first decade ended at the end of December
> 31st 0011 AD, not December 31st, 0010 AD. (if following the proleptic
> Gregorian calendar).
> > Languages don't work like this.
> >
> > https://www.collinsdictionary.com/dictionary/english/decade
> >
> > Cheers
>
> Its a difference between ordinals and numerals. The 20th century was
> from the beginning of 1901 to the end of 2000. We also have the century
> called the 1900's which went from 1900 to the end of 1999.
>
> Decade would work the same way, the 202st decade goes from 2011 to end
> of 2020, but the 2010s go from 2010 to end of 2019.
>
> --
> Richard Damon
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] 18 minutes 41 seconds

[Michael Falconer]

Great work but pretty much what we have come to expect from DRH and the
SQLite team. As we approach the end of yet another year ( and indeed decade
) can I indulge the list in a simple congratulations to all involved and to
the outstanding support on offer when a member of this email list. SQLite
is personally my favourite software release  and IMHO right up there as the
most useful and reliable software of all time! Thanks to all who have made
it possible.


On Tue, 31 Dec 2019 at 11:13, Richard Hipp  wrote:

> That's the total elapse time from me checking in a bug (check-in
> https://www.sqlite.org/src/info/40d10e7aad5b8992) until Manuel
> Rigger's fuzzer had located the bug and issued a ticket against it:
> (ticket https://www.sqlite.org/src/info/892575cdba4e1e36).
>
> Well, at least the bisect didn't take very long!
>
> --
> 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
>


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

Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

[Michael Walker (barrucadu)]

Hi Shawn,

Thanks for your response.  Though that doesn't seem to be the case:

sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn = 
"9781496030825";
9781496030825|text
sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn = 
"9780099477310";
9780099477310|text

The column is a VARCHAR:

CREATE TABLE `books` (
   `bookIsbn` VARCHAR NOT NULL PRIMARY KEY,
   `bookTitle` VARCHAR NOT NULL,
   `bookSubtitle` VARCHAR NOT NULL,
   `bookCover` VARCHAR NULL,
   `bookVolume` VARCHAR NOT NULL,
   `bookFascicle` VARCHAR NOT NULL,
   `bookVoltitle` VARCHAR NOT NULL,
   `bookAuthor` VARCHAR NOT NULL,
   `bookTranslator` VARCHAR NULL,
   `bookEditor` VARCHAR NULL,
   `bookSorting` VARCHAR NULL,
   `bookRead` BOOLEAN NOT NULL,
   `bookLastRead` TIMESTAMP NULL,
   `bookNowReading` BOOLEAN NOT NULL,
   `bookLocation` VARCHAR NOT NULL,
   `bookBorrower` VARCHAR NOT NULL,
   `bookCategoryCode` VARCHAR NOT NULL,
   FOREIGN KEY(`bookCategoryCode`) REFERENCES 
`book_categories`(`categoryCode`)
);

I'm not sure the attachment to my first email got through, so here's the 
database: 
https://misc.barrucadu.co.uk/forever/82e5584a-e4a8-4804-8abe-8f00be73f725/bookdb.sqlite


‐‐‐ Original Message ‐‐‐
On Saturday, 21 December 2019 21:37, Shawn Wagner  
wrote:

> Without seeing your table definition, this is just a guess, but maybe the
> duplicate keys are stored as different types, with the primary key column
> having an affinity that doesn't force one particular storage class:
>
> sqlite> CREATE TABLE test(id PRIMARY KEY);
> sqlite> INSERT INTO test VALUES('12345');
> sqlite> INSERT INTO test VALUES(12345);
> sqlite> SELECT id, typeof(id) FROM test;
> id typeof(id)
>
> 12345 text
> 12345 integer
> sqlite> SELECT id, typeof(id) FROM test WHERE id = '12345';
> id typeof(id)
>
> 12345 text
>
> On Sat, Dec 21, 2019 at 1:26 PM Michael Walker (barrucadu) <
> m...@barrucadu.co.uk> wrote:
>
> > Hi,
> > I've somehow ended up with a table which contains two records for the same
> > primary key - well actually I've got two primary keys like that, so I have
> > four records with two primary keys between them.
> > I've been unable to reproduce this from a clean database, so I attach my
> > database file to this email.
> > Here are some oddities:
> >
> > $ sqlite3 bookdb.sqlite
> > SQLite version 3.28.0 2019-04-16 19:49:53
> > Enter ".help" for usage hints.
> > sqlite> select * from books where bookIsbn = "9781496030825";
> > 9781496030825|Can Such Things Be?||9781496030825.jpgBierce,
> > Ambrose0||0|London||F
> > sqlite> select * from books where bookIsbn = "9780099477310";
> > 9780099477310|Catch-22||9780099477310.jpgHeller,
> > Joseph0||0|London||F
> > sqlite> .output books_issue
> > sqlite> .dump books
> > sqlite> .quit
> >
> > $ grep "9781496030825" < books_issue
> > INSERT INTO books VALUES('9781496030825','Can Such Things
> > Be?','','9781496030825.jpg','','','','Bierce,
> > Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > INSERT INTO books VALUES('9781496030825','Can Such Things
> > Be?','','9781496030825.jpg','','','','Bierce,
> > Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> >
> > $ grep "9780099477310" < books_issue
> > INSERT INTO books
> > 
> > VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> > Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > INSERT INTO books
> > 
> > VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> > Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> >
> > $ sqlite3 bookdb.sqlite
> > SQLite version 3.28.0 2019-04-16 19:49:53
> > Enter ".help" for usage hints.
> > sqlite> drop table books;
> > sqlite>
> >
> > $ sqlite3 bookdb.sqlite < books_issue
> > Error: near line 697: UNIQUE constraint failed: books.bookIsbn
> > Erro

[sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

[Michael Walker (barrucadu)]

Hi,

I've somehow ended up with a table which contains two records for the same 
primary key - well actually I've got two primary keys like that, so I have four 
records with two primary keys between them.

I've been unable to reproduce this from a clean database, so I attach my 
database file to this email.

Here are some oddities:

```
$ sqlite3 bookdb.sqlite
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> select * from books where bookIsbn = "9781496030825";
9781496030825|Can Such Things Be?||9781496030825.jpgBierce, 
Ambrose0||0|London||F
sqlite> select * from books where bookIsbn = "9780099477310";
9780099477310|Catch-22||9780099477310.jpgHeller, Joseph0||0|London||F
sqlite> .output books_issue
sqlite> .dump books
sqlite> .quit

$ grep "9781496030825" < books_issue
INSERT INTO books VALUES('9781496030825','Can Such Things 
Be?','','9781496030825.jpg','','','','Bierce, 
Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
INSERT INTO books VALUES('9781496030825','Can Such Things 
Be?','','9781496030825.jpg','','','','Bierce, 
Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');

$ grep "9780099477310" < books_issue
INSERT INTO books 
VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller, 
Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
INSERT INTO books 
VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller, 
Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');

$ sqlite3 bookdb.sqlite
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> drop table books;
sqlite>

$ sqlite3 bookdb.sqlite < books_issue
Error: near line 697: UNIQUE constraint failed: books.bookIsbn
Error: near line 698: UNIQUE constraint failed: books.bookIsbn
```

Updating either affected record results in the second copy in the .dump output 
being updated, the first copy has the original state.

The table has always had a primary key constraint, so I'm not sure how it's 
ended up in its current state.  However, even if there were not a primary key 
constraint, there do seem to be two very real bugs here: SELECT gives different 
results to .dump, and .dump is producing output which can't be restored.

I'm not sure if you'll be able to make anything of this, as I say I haven't 
been able to reproduce it from a blank database, but I figure you'll be better 
at debugging this than me.

Thanks

--
Michael Walker (http://www.barrucadu.co.uk)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Michael Tiernan]

On 11/10/19 1:21 AM, Gary R. Schmidt wrote:
So what happens when someone from a family who only uses first- and 
last-names moves to Kansas?


Do they have to make up a middle-name so that he idiots can fill out 
the forms? 


I am most definitely not going to take one side or the other. My only 
suggestion is for anyone to see the depth and complexity of the problem, 
get involved in genealogy. You'll want to scream very quickly. :)


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

Re: [sqlite] OT!!!!! Understanding the WITH clause

[Michael Falconer]

SELECT peace FROM disaster WHERE disaster.cause = 'Windows';

Returns: *nix


On Thu, 20 Jun 2019 at 14:26, Sam Carleton  wrote:

> James,
>
> That is a really, really interesting quote.  it really got me thinking
> about peace and the definition of it.  It got me thinking so much I wanted
> to know the context of the sentence, so I went looking for it in Letter
> from the Birmingham Jail, this is my source link (
> http://web.cn.edu/kwheeler/documents/letter_birmingham_jail.pdf).  I am
> not
> able to find that sentence in the PDF.
>
> Ah, hold the press, I searched a bit more and discovered that what you
> quote is not a direct quote but a paraphrased version of this sentence:
>
> I have almost reached the regrettable conclusion that the Negro's great
> stumbling block in the stride toward freedom is not the White Citizens
> Councillor or the Ku Klux Klanner but the white moderate who is more
> devoted to order than to justice; who prefers a negative *peace* which is
> the *absence of tension* to a positive peace which i*s the presence of
> justice*; who constantly says, "I agree with you in the goal you seek, but
> I can't agree with your methods of direct action"; who paternalistically
> feels that he can set the timetable for another man's freedom; who lives by
> the myth of time; and who constantly advises the Negro to wait until a
> "more convenient season."
>
> Here is what I learned from this.  I was WRONG!  I made a very arrogant
> statement that everyone knows what peace is, as if there is only one
> meaning of peace!
>
> MLK is talking about a very different type of peace then what I am talking
> about.  The way I read what MLK is talking about, I get the impression he
> is talking about civil peace, aka everyone following the laws the civil
> athorities have put in place.  As I think most folks get, today, many of
> these laws were not so good and thus denied folks justice.
>
> I was referring to a feeling of inner peace.  For example:  Today I learned
> my company is terminating my employment as of Oct 1.  I'm at "inner" peace
> with that.
>
> James, thank you so much for pointing out that quote and modivating me to
> dig into it, I learned a lot tonight, which helps me continue to maintain
> that inner peace after such an "interesting" day.
>
>
> Pax vobiscum,
> Sam Carleton
>
>
> On Tue, Jun 18, 2019 at 2:57 PM James K. Lowden 
> wrote:
>
> > On Mon, 17 Jun 2019 20:46:41 -0400
> > Sam Carleton  wrote:
> >
> > > It is my view that peace is not something that can be defined with
> > > some words, rather it is a universal experience.
> >
> > "But peace is not merely the absence of this tension, but the
> > presence of justice."
> > -- MLK, Letter from the Birmingham Jail
> >
> > --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,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How many digits do you need ?

[Michael Falconer]

Nice one Simon,

as a resident Australian (and a lover of levity) I liked your link, which
led me on to the data source. I took a pair of those 15 digit coordinates
and pumped them into good old Google maps but sadly I was unable to zoom in
far enough to see any chlorine atoms. :-(. Shame that.

On Thu, 4 Apr 2019 at 16:50, Simon Slavin  wrote:

> Please allow me a little levity, spinning off an earlier discussion of how
> many digits a decimal number type needs to store.
>
> 
>
> " Carbrook, for instance, is at -27.673862 153.25624 and at
> -27.673861999297635 153.25624388146.
>
> [...] those 15-place figures locate the suburb's latitude to the nearest
> tenth of a nanometre, about half the diameter of a chlorine atom. "
>
> First, spot the '999' and '' suggesting a problem.  Second wonder
> whether anyone read the data.
>
> Apart from that, the article is complimentary about the format used for
> making a lot of data easily searchable.  So it's a nice example to use when
> talking about care with data preparation.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] GROUP BY and ICU collation

[Michael Herger]

Remember that fancy collations don't just look at 1 character at a time, they look 
at the whole thing, and can do surprising stuff based on that. In this case the 
order of preference for the collation looks like "when it's part of a larger 
word, then treating 'S' and 'Š' the same is more important than separating them. But 
when it's just 1 character then they're different.


Uh... oh... that might explain things... Then I'll have to re-think 
things considerably.


Thank you all for your input!

Michael




So 'S' might be before 'Š', but in words it might go

'Sam'
'Šam'
'Skunk'
'Škunk'
'Sudden'
'Šudden'

rather than a simple character-at-a-time order of

'Sam'
'Skunk'
'Sudden'
'Šam'
'Škunk'
'Šudden'


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Michael Herger
Sent: Friday, February 08, 2019 4:13 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] GROUP BY and ICU collation

Hi Keith,

thanks for your response (which partly goes beyond my understanding, but
I'm learning :-)).


But you are not using the same "expression" for selecting, sorting, and 
grouping.  That is, you need to specify:

SELECT expression, count(distinct id)
  FROM artists
GROUP BY expression
ORDER BY expression;

where expression is the expression that you want to use

SELECT substr(name collate de_DE, 1, 1), count(distinct id)
  FROM artists
GROUP BY substr(name collate de_DE, 1, 1)
ORDER BY substr(name collate de_DE, 1, 1);

If you do not do so then you cannot expect the "GROUP BY" to be using the same ordering 
as the "ORDER BY" nor the select to be returning the same value that was used to do the 
grouping and sorting.


Ok, tried that:

SELECT SUBSTR(contributors.namesort COLLATE de_DE , 1, 1)
FROM contributors
GROUP BY SUBSTR(contributors.namesort COLLATE de_DE , 1, 1)
ORDER BY SUBSTR(contributors.namesort COLLATE de_DE , 1, 1)

Resulted in:

"A"
"C"
"D"
"P"
"R"
"S"
"Š"
"T"
"W"

Whereas this:

SELECT contributors.name, contributors.namesort COLLATE de_DE
FROM contributors
ORDER BY contributors.namesort COLLATE de_DE

...resulted in this:

...
["Roddy Woomble & Band", "RODDY WOOMBLE BAND"],
["Samuel Yirga", "SAMUEL YIRGA"],
["Stephin Merritt", "STEPHIN MERRITT"],
["Šuma Čovjek", "ŠUMA ČOVJEK"],
["Syriana", "SYRIANA"],
["Tom Griesgraber", "TOM GRIESGRABER"],
...

So despite my using the same expression for the GROUP as for the ORDER,
the grouping seems to ignore the collation, whereas the sorting alone
would not.

I'm using the ICU extension (otherwise the sorting wouldn't work either,
right?). Could that extension be responsible for this odd behaviour?

I've put a small sample .db file in my dropbox:
https://www.dropbox.com/s/w4h6n3cyamt6yc0/library.db?dl=0




(This substitutes the collation NOCASE for the de_DE since I no have a de_DE 
collation:

sqlite> select substr(name collate nocase, 1, 1), count(distinct id)
 ...>   FROM artists
 ...> group by substr(name collate nocase, 1, 1)
 ...> order by substr(name collate nocase, 1, 1);
QUERY PLAN
|--SCAN TABLE artists (~1048576 rows)
`--USE TEMP B-TREE FOR GROUP BY
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 57000  Start at 57
1 Noop   1 4 000
2 SorterOpen 3 3 0 k(1,NOCASE)00
3 Integer0 5 000  r[5]=0; clear abort 
flag
4 Null   0 8 800  r[8..8]=NULL
5 Gosub  7 52000
6 OpenRead   0 3 0 2  00  root=3 iDb=0; artists
7 ColumnsUsed0 0 0 3  00
8 Explain8 0 0 SCAN TABLE artists (~1048576 rows)  00
9 Noop   0 0 000  Begin WHERE-loop0: 
artists
10Rewind 0 20000
11  Noop   0 0 000  Begin WHERE-core
12  Column 0 1 13   00  r[13]=artists.name
13  Function0  6 1310substr(3)  03  
r[10]=func(r[13..15])
14  Column 0 1 11   00  r[11]=artists.name
15  Column 0 0 12   00  r[12]=artists.id
16  MakeRecord 103 16  

Re: [sqlite] GROUP BY and ICU collation

[Michael Herger]

 36171  00  key=r[17]
33  MakeRecord 171 18   00  r[18]=mkrec(r[17])
34  IdxInsert  2 18171  10  key=r[18]
35  AggStep0 172 count(1)   01  accum=r[2] 
step(r[17])
36  If 4 38000
37  Column 4 1 100  r[1]=
38  Integer1 4 000  r[4]=1; indicate 
data in accumulator
39SorterNext 3 23000
40Gosub  6 44000  output final row
41Goto   0 56000
42Integer1 5 000  r[5]=1; set abort flag
43Return 6 0 000
44IfPos  4 46000  if r[4]>0 then 
r[4]-=0, goto 46; Groupby result generator entry point
45Return 6 0 000
46AggFinal   2 1 0 count(1)   00  accum=r[2] N=1
47Copy   1 21000  r[21]=r[1]
48Function0  6 2119substr(3)  03  r[19]=func(r[21..23])
49Copy   2 20000  r[20]=r[2]
50ResultRow  192 000  output=r[19..20]
51Return 6 0 000  end groupby result 
generator
52Null   0 1 300  r[1..3]=NULL
53OpenEphemeral  2 0 0 k(1,B) 00  nColumn=0
54Integer0 4 000  r[4]=0; indicate 
accumulator empty
55Return 7 0 000
56Halt   0 0 000
57Transaction0 0 2 0  01  usesStmtJournal=0
58Integer1 140        00  r[14]=1
59Integer1 15000  r[15]=1
60Integer1 22000  r[22]=1
61Integer1 23000  r[23]=1
62Goto   0 1 000
sqlite>



Michael




That is

select name collate nocase, count(distinct id) from x group by name

collate nocase order by name collate nocase


whill produce cased output not the value that was used for the

sorting.



select lower(name collate nocase), count(distinct id) from x group

by name collate nocase order by name collate nocase;


to transmorgificate name into a "caseless" representation.  So you

would need to do something like this:


select de_DE(substr(name collate de_DE,1,1)), count(distinct id)

from artists

group by substr(name collate de_DE,1,1)
order by by substr(name collate de_DE,1,1)

and the function de_DE would have to transmorgificate its value to

the result you want to see.


---
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 li...@herger.net
Sent: Thursday, 7 February, 2019 05:12
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] GROUP BY and ICU collation


Hi there,

I'm trying to create a list with an index list. Eg. I have

artists:


Sting
Šuma Čovjek
Suzanne Vega

That's the sort order I'd get using an ICU collation. "Šuma

Čovjek"

would be sorted as "Suma..." as expected.

Now I'd like to create an index bar by providing groups of the

first

character:

SELECT SUBSTR(name,1,1), count(distinct id) FROM artists GROUP

BY

SUBSTR(name,1,1) ORDER BY name COLLATE de_DE>>

Aren't you missing a COLLATE clause after the GROUP BY term?

   ... GROUP BY SUBSTR(name,1,1) COLLATE de_DE ...


TBH: I didn't even know about this. I thought the COLLATE at the

end

of
the statement would do it for all.

Alas, tried again to no avail. No matter whether I add it after

the

GROUP BY or not, the result is the same.

I should probably have added some version information: I'm using

the

Perl DBD::SQLite 1.58 (sqlite 3.22.0). I didn't see any mention of
related changes in the changelog for SQLite. What would be the
easiest
(and most reliable) way to try to reproduce this without Perl? Is
there
a HowTo use collations with the CLI sqlite?

--

Michael


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




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


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

Re: [sqlite] Is it possible to concatenate an arbitrary number of columns into one?

[Michael Falconer]

As Simon points out there is no SQL solution to your issue. Some sort of
external utility processing with things like awk, sed or even cut may
assist or for a quick and dirty method you could set the sqlite3 command
line utility .separator value to a blank string which may (or may not)
provide a temporary method. Not in raw SQL though.

On Wed, 21 Nov 2018 at 11:32, Simon Slavin  wrote:

> On 20 Nov 2018, at 11:54pm, Shane Dev  wrote:
>
> > Is there an SQL statement to concatenate all columns into a single
> column without explicitly naming them?
>
> No.  And I can't think of any short-cut way to do what you want.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Regarding CoC

[Michael Falconer]

Oh dear,

it's all gone...while my devout atheism is generally pleased my
somewhat annoying 'free will, free speech ' ethic has rust on it! Richard,
it's your joint and it's such a good place, friendly and mostly respectful.
My atheism was NOT offended in any way by all that God speak and I do
support the notion that you are perfectly entitled to have a CoC and for it
to take whatever form you feel appropriate. But I'm an honest guy and will
unsub if my un-godliness is just totally unacceptable, but I'll still be
using SQLite!

On Thu, 25 Oct 2018 at 06:10, Mantas Gridinas  wrote:

> Or a capture card.
>
> On Wed, Oct 24, 2018, 21:28 Brian Chrzanowski  wrote:
>
> > Probably a virtual machine.
> >
> > On Wed, Oct 24, 2018, 2:27 PM R Smith  wrote:
> >
> > >
> > > On 2018/10/24 8:19 PM, Stephen Chrzanowski wrote:
> > > > ..// without users consent. ... unlike...
> > > >
> > >
> >
> https://www.extremetech.com/wp-content/uploads/2016/08/Windows10-BSOD-640x353.jpg
> > >
> > > How did you take a screenshot while Windows was hanging/recovering?
> > >
> > > I call foul!
> > >
> > > (Or is that a new Windows 10 feature?)
> > >
> > >
> > > ___
> > > 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
>


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

Re: [sqlite] Regarding CoC

[Michael Falconer]

>
> I found code of conduct in documentation and I was wondering if it were
> true. Checking the version history it appears to have been added on
> 2018-02-22.
>

Sure that publishing date wasn't 2018-04-01?

On Wed, 24 Oct 2018 at 08:02, Stefan Evert  wrote:

>
> > On 23 Oct 2018, at 07:04, Paul  wrote:
> >
> > If my opinion has any value, even though being atheist, I prefer this
> CoC 100 times over
> > the CoC that is being currently pushed onto the many open-source
> communities, that was
> > created by some purple-headed feminist with political motives. This one
> does not have
> > any hidden intentions (at least, it seems so to me, knowing that you're
> honest person).
>
> Exactly my feelings.
>
> – Stefan
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Regards,
 Michael.j.Falconer.
___
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...

[Michael Tiernan]

May I respectfully suggest to everyone that offering solutions, while 
valuable and helpful, may not be as valuable as the offer of assistance 
to our listmaster.

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

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

[Michael Tiernan]

On 2/15/18 2:55 PM, Simon Slavin wrote:

By the way, your use of backticks to identify column names
That's a quirk of the "schema" command that I used to export the sample 
that I'm playing with.


However, thanks for the reminder.

And thanks to everyone for the advice on how to do this.

I'm going to dive in now.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Michael Tiernan]

This might be a general RDBMS question but since I'm using sqlite 
specifically, I hope it passes basic relevancy tests.


I have a table defined as:

CREATE TABLE "CPUModelDictionary" (
`vendor_id` TEXT,
`cpu_family`INTEGER,
`cpu_model` INTEGER,
`cpuid_level`   INTEGER,
`cpu_model_name`TEXT,
`cpu_cores` INTEGER,
`cpu_MHz`   INTEGER
);
CREATE INDEX `idx_CPUModels` ON `CPUModelDictionary` (
`cpu_family`,
`cpu_model`,
`cpuid_level`,
`cpu_cores`,
`cpu_MHz`
);

that contains rows like this:

'GenuineIntel',6,62,13,'Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz',8,2620
'GenuineIntel',6,63,15,'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz',8,2600
'GenuineIntel',6,79,20,'Intel(R) Xeon(R) CPU E5-2620 v4 @ 2.10GHz',8,1200

The five fields used in the index create a unique identifier.

I want to reference these rows via a single reference field but I'm not 
sure if there's a "smarter" way to do it. I considered just creating a 
field that's a concatenation of the five fields and using that as the 
unique link to the rows but I'm sure that it's not the best way to do it.


(The objective is to have a single field in another table that 
identifies the CPU used in a system. It will be a many-to-one reference 
to this CPUModel.)


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

Thanks for everyone's time.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor bug reports during build.

[Michael Tiernan]

On Fri, Dec 22, 2017 at 10:26 AM, Warren Young  wrote:

> On Dec 22, 2017, at 7:07 AM, Michael Tiernan 
> wrote:
> >
> > "Working as advertised" Okay, that's just funny. That it doesn't build is
> > correct?
>
> It does build.


Okay, it does build the binaries. Valid point.

  It just doesn’t install to a directory it can’t write to, because you
> told it to install system-level things.



Not going to hash it out here but I didn't tell it to install system-level
things, I told it to compile and install everything locally. Just like I do
with lots of other source packages especially when I'm not very familiar
with the software and wish to make sure of what I'm doing before committing
it to the system.



> > Just as an FYI, it builds correctly on MacOSX and doesn't complain about
> > things it can't control.
> I’m guessing you’re using Homebrew,


Nope, not at all. It doesn't seem to try and touch the things it has no
right to touch.

-- 
<< MCT >>   Michael C Tiernan.http://www.linkedin.com/in/mtiernan

Non Impediti Ratione Cogatationis
Women and cats will do as they please, and
men and dogs should relax and get used to the idea. -Robert A. Heinlein
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor bug reports during build.

[Michael Tiernan]

"Working as advertised" Okay, that's just funny. That it doesn't build is
correct?

Also, despite the "disable-tcl" flag, the tests all fail because of
something involving tcl. Don't think I read that in the advertisement.

Just as an FYI, it builds correctly on MacOSX and doesn't complain about
things it can't control.
-- 
<< MCT >>   Michael C Tiernan.http://www.linkedin.com/in/mtiernan

Non Impediti Ratione Cogatationis
Women and cats will do as they please, and
men and dogs should relax and get used to the idea. -Robert A. Heinlein


On Dec 21, 2017 10:36 PM, "Rowan Worth"  wrote:

Seems to be working as advertised.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor bug reports during build.

[Michael Tiernan]

I found that if I use "disable-tcl" it builds correctly.

(Testing is an issue but that's separate right now.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor bug reports during build.

[Michael Tiernan]

Sorry for the top post. Sadly the android client forces it.

In short, I'm building two copies of sqlite3, one of which works fine, the
attempt to build it on the Linux host (using the prefix flag of course)
causes the build to begin but it to fail when it runs into the attempt at
modifying the (non-existent) file /usr/share/tcl8.5/sqlite3

All the other warnings and considerations are secondary to the point that
the makefile is attempting to change the permissions on an external (to the
user) tool which it neither built or should be able to modify.

Thanks for everyone's time!
-- 
<< MCT >>   Michael C Tiernan.http://www.linkedin.com/in/mtiernan

Non Impediti Ratione Cogatationis
Women and cats will do as they please, and
men and dogs should relax and get used to the idea. -Robert A. Heinlein

On Dec 21, 2017 2:26 PM, "Warren Young"  wrote:

On Dec 21, 2017, at 11:37 AM, Michael Tiernan 
wrote:
>
> I'm trying to build two copes of sqlite3 in a shared dropbox folder.

Do you intend to use SQLite inside the Dropbox folder once you’ve got it
working?  That’s only safe if only one person is using the database at a
time, and you wait for the sync to finish before trying to use the DB on
another machine.

If you need a networked DBMS, SQLite is generally not what you want, at
least not as-stock.  There are add-ons and alternatives that work far
better for this.  Google “SQLite Dropbox”.  It’s come up many times before.

> On a "Scientific Linux 6.7" (RHEL 6.7) system I did a built then build
> install *as a user* and not as root. Looking to create a localized copy
> specifically.

Try this:

$ ./configure --prefix="$HOME/sqlite3"

That will allow the “make install” to work without root privileges.  The
sqlite3 binary would land in $HOME/sqlite3/bin, with that configuration
option.

You can set the prefix to somewhere under your Dropbox folder if you’re
willing to take the risks to data safety that that entails.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor bug reports during build.

[Michael Tiernan]

e -Wl,libsqlite3.so.0 -o .libs/libsqlite3.so.0.8.6
> libtool: link: (cd ".libs" && rm -f "libsqlite3.so.0" && ln -s
> "libsqlite3.so.0.8.6" "libsqlite3.so.0")
> libtool: link: (cd ".libs" && rm -f "libsqlite3.so" && ln -s
> "libsqlite3.so.0.8.6" "libsqlite3.so")
> libtool: link: ar cru .libs/libsqlite3.a  sqlite3.o
> libtool: link: ranlib .libs/libsqlite3.a
> libtool: link: ( cd ".libs" && rm -f "libsqlite3.la" && ln -s "../
> libsqlite3.la" "libsqlite3.la" )
> /usr/bin/install -c -d /home/mct/Dropbox-MIT/CMDB/Linux/lib
> ./libtool --mode=install /usr/bin/install -c libsqlite3.la
> /home/mct/Dropbox-MIT/CMDB/Linux/lib
> libtool: install: /usr/bin/install -c .libs/libsqlite3.so.0.8.6
> /home/mct/Dropbox-MIT/CMDB/Linux/lib/libsqlite3.so.0.8.6
> libtool: install: (cd /home/mct/Dropbox-MIT/CMDB/Linux/lib && { ln -s -f
> libsqlite3.so.0.8.6 libsqlite3.so.0 || { rm -f libsqlite3.so.0 && ln -s
> libsqlite3.so.0.8.6 libsqlite3.so.0; }; })
> libtool: install: (cd /home/mct/Dropbox-MIT/CMDB/Linux/lib && { ln -s -f
> libsqlite3.so.0.8.6 libsqlite3.so || { rm -f libsqlite3.so && ln -s
> libsqlite3.so.0.8.6 libsqlite3.so; }; })
> libtool: install: /usr/bin/install -c .libs/libsqlite3.lai
> /home/mct/Dropbox-MIT/CMDB/Linux/lib/libsqlite3.la
> libtool: install: /usr/bin/install -c .libs/libsqlite3.a
> /home/mct/Dropbox-MIT/CMDB/Linux/lib/libsqlite3.a
> libtool: install: chmod 644
> /home/mct/Dropbox-MIT/CMDB/Linux/lib/libsqlite3.a
> libtool: install: ranlib /home/mct/Dropbox-MIT/CMDB/Linux/lib/libsqlite3.a
> libtool: finish:
> PATH="/usr/lib64/qt-3.3/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/NX/bin:/opt/dell/srvadmin/bin:/home/mct/bin:/sbin"
> ldconfig -n /home/mct/Dropbox-MIT/CMDB/Linux/lib
> --
> Libraries have been installed in:
>/home/mct/Dropbox-MIT/CMDB/Linux/lib
>
> If you ever happen to want to link against installed libraries
> in a given directory, LIBDIR, you must either use libtool, and
> specify the full pathname of the library, or use the `-LLIBDIR'
> flag during linking and do at least one of the following:
>- add LIBDIR to the `LD_LIBRARY_PATH' environment variable
>  during execution
>- add LIBDIR to the `LD_RUN_PATH' environment variable
>  during linking
>- use the `-Wl,-rpath -Wl,LIBDIR' linker flag
>- have your system administrator add LIBDIR to `/etc/ld.so.conf'
>
> See any operating system documentation about shared libraries for
> more information, such as the ld(1) and ld.so(8) manual pages.
> --
> ./libtool --mode=link gcc -g -O2 -DSQLITE_OS_UNIX=1 -I.
> -I/home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/src
> -I/home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/ext/rtree
> -I/home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/ext/icu
> -I/home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/ext/fts3
> -I/home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/ext/async
> -I/home/mct/Dropbox-MIT/Sqlite3/Linux/../sqlite/ext/session
> -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DNDEBUG -I/usr/include
> -DSQLITE_THREADSAFE=1  -no-undefined -o libtclsqlite3.la tclsqlite.lo
> \
> libsqlite3.la -L/usr/lib64 -ltclstub8.5 -ldl -lpthread   \
> -rpath "/usr/share/tcl8.5/sqlite3" \
> -version-info "8:6:8" \
> -avoid-version
> libtool: link: rm -fr  .libs/libtclsqlite3.a .libs/libtclsqlite3.la
> .libs/libtclsqlite3.lai .libs/libtclsqlite3.so
> libtool: link: gcc -shared  .libs/tclsqlite.o   -Wl,-rpath
> -Wl,/home/mct/Dropbox-MIT/Sqlite3/Linux/.libs -Wl,-rpath
> -Wl,/home/mct/Dropbox-MIT/CMDB/Linux/lib ./.libs/libsqlite3.so -L/usr/lib64
> -ltclstub8.5 -ldl -lpthread-Wl,-soname -Wl,libtclsqlite3.so -o
> .libs/libtclsqlite3.so
> libtool: link: ar cru .libs/libtclsqlite3.a  tclsqlite.o
> libtool: link: ranlib .libs/libtclsqlite3.a
> libtool: link: ( cd ".libs" && rm -f "libtclsqlite3.la" && ln -s "../
> libtclsqlite3.la" "libtclsqlite3.la" )
> echo 'package ifneeded sqlite3 3.21.0 [list load
> /usr/share/tcl8.5/sqlite3/libtclsqlite3.so sqlite3]' > pkgIndex.tcl
>
>
> */usr/bin/install -c -d /usr/share/tcl8.5/sqlite3/usr/bin/install: cannot
> change permissions of `/usr/share/tcl8.5/sqlite3': No such file or
> directorymake: *** [tcl_install] Error 1*
>

-- 
<< MCT >>   Michael C Tiernan.http://www.linkedin.com/in/mtiernan

Non Impediti Ratione Cogatationis
Women and cats will do as they please, and
men and dogs should relax and get used to the idea. -Robert A. Heinlein
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minor bug reports during build.

[Michael Tiernan]

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

[sqlite] Minor bug reports during build.

[Michael Tiernan]

Is there a route for reporting an error for an average user without
creating an account and all the overhead of the ticket system?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Michael Stephenson]

In the past, I've used the pager to secure data.  This involved encrypting the 
data before writing the data to disk and decrypting when loading from disk but 
also optionally hashing the page and storing the hash in extra data reserved 
for each page.  If anyone tampered with the data, the hash would indicate this 
and an error could be thrown.  

Also encrypting the page data makes it more difficult to tamper with the data.

Products like sqlcipher do things like this (encryption, hashing), and it's 
fairly easy to see how it's done by pulling the sqlite source (not the 
amalgamation) and diffing it with the sqlcipher source.

~Mike

> On Sep 7, 2017, at 6:34 PM, Jens Alfke  wrote:
> 
> 
> 
>> On Sep 7, 2017, at 2:47 PM, Keith Medcalf  wrote:
>> 
>> Again, this is a detection for changed data and does nothing to prevent 
>> changes being made.
> 
> The OP did not require that it be impossible to make changes (which is 
> clearly impossible without locking down write access to the file.) He 
> specifically said that detection of changed data was OK:
> 
>>> For security reasons, a customer wants to be sure that a database line 
>>> cannot be modified after its initial insertion (or unmodified without being 
>>> visible, with proof that the line has been modified).
> 
> The procedures I described provide detection that a row has been modified.  
> The first one doesn't make it evident that a row has been deleted, though the 
> second one does.
> 
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SPAM-LOW: sqlite-users Digest, Vol 114, Issue 3

[Michael]

I'll take a look. 
  
 Thanks - 
  
 Michael Condon
Mission Critical Solutions 
(503)726-4685
  
  Original Message 
> From: sqlite-users-requ...@mailinglists.sqlite.org
> Sent: Saturday, June 03, 2017 5:00 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: SPAM-LOW: sqlite-users Digest, Vol 114, Issue 3
>
> Send sqlite-users mailing list submissions to 
sqlite-users@mailinglists.sqlite.org To subscribe or unsubscribe via the 
World Wide Web, visit 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users or, 
via email, send a message with subject or body 'help' to 
sqlite-users-requ...@mailinglists.sqlite.org You can reach the person 
managing the list at sqlite-users-ow...@mailinglists.sqlite.org When 
replying, please edit your Subject line so it is more specific than "Re: 
Contents of sqlite-users digest..." Today's Topics: 1. WAL checkpoint 
starved? (Daniel Polski) 2. Re: WAL checkpoint starved? (Richard Hipp) 3. 
Re: WAL checkpoint starved? (Clemens Ladisch) 4. memory usage after close 
database on linux arm (Stephane Guibert) 5. Re: memory usage after close 
database on linux arm (Clemens Ladisch) 
-- 
Message: 1 Date: Fri, 2 Jun 2017 16:00:38 +0200 From: Daniel Polski 
 To: sqlite-users@mailinglists.sqlite.org Subject: 
[sqlite] WAL checkpoint starved? Message-ID: 
<40d767cb-9f0f-7e06-78af-9a95a4ab6...@agelektronik.se> Content-Type: 
text/plain; charset=windows-1252; format=flowed I've found something weird 
in a log from a client. Normally our WAL files are < 100kB, but in this log 
I noticed the file was >40MB. This was totally unexpected since we run this 
call every minute: int val = 
sqlite3_wal_checkpoint_v2(myDB->getDbPointer(), NULL, 
SQLITE_CHECKPOINT_TRUNCATE, &wal_size, &wal_checkpointed); and I thought 
that SQLITE_CHECKPOINT_TRUNCATE would force the checkpoint to completion. 
We haven't turned off automatic checkpoints, just added the above to make 
them happen more often than default, and be called from a specific thread. 
Some more background information: The sqlite version in the client is 
3.17.0. 1. Operation - We normally run ~18 threads with "own" connections 
open to the sqlite database. They read concurrently, but all writes are 
protected by a mutex and all insert/update/deletes starts with "begin 
immediate transaction". We do -not- ensure there are "reader gaps", since I 
believe that shouldn't be necessary if using truncate mode. 2. Backup - 
About every hour we run a separate process, which uses the backup api to 
create a backup (this process is not obeying the mutex described above and 
runs in parallell with the normal operation). 3. GUI/API - Uses one 
consistent database connection in PHP - Serveral instances access the one 
database connection - Mostly read operations. The write operations are not 
manually wrapped inside begin & commit since they're all single commands. 
First I suspected the main thread to hang, so the call to 
sqlite3_wal_checkpoint_v2 stopped executing every minute. But then the the 
auto checkpointing should take over since that's not turned off. So now I'm 
thinking that something is blocking the checkpoint to be able to complete, 
but can't figure out what that could be or how to find what it is, if it is 
so. Any ideas why I can end up with that large WAL file, except the 
information in the chapter "Avoiding Excessively Large WAL Files" 
describes? (Or if I have misunderstood the information there..?) 
-- Message: 2 Date: Fri, 2 Jun 2017 10:07:38 
-0400 From: Richard Hipp  To: SQLite mailing list 
 Subject: Re: [sqlite] WAL checkpoint 
starved? Message-ID: 
 
Content-Type: text/plain; charset="UTF-8" On 6/2/17, Daniel Polski 
 wrote: > I've found something weird in a log from 
a client. > Normally our WAL files are < 100kB, but in this log I noticed 
the file > was >40MB. This was totally unexpected since we run this call 
every minute: > > int val = sqlite3_wal_checkpoint_v2(myDB->getDbPointer(), 
NULL, > SQLITE_CHECKPOINT_TRUNCATE, &wal_size, &wal_checkpointed); > > and 
I thought that SQLITE_CHECKPOINT_TRUNCATE would force the checkpoint > to 
completion. We haven't turned off automatic checkpoints, just added > the 
above to make them happen more often than default, and be called > from a 
specific thread. Do you have a busy callback handler registered 
(https://sqlite.org/c3ref/busy_handler.html) and are you checking the 
return code from sqlite3_wal_callback_v2()? -- D. Richard Hipp 
d...@sqlite.org -- Message: 3 Date: Fri, 2 Jun 
2017 16:07:44 +0200 From: Clemens Ladisch  To: 
sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] WAL checkpoint 
starv

Re: [sqlite] SQLite CVE-2015-6607 (Escalation of privilege issue )

[Michael Falconer]

Just amusing, and found while looking for above.

Researchers have disclosed a vulnerability in *Android's* SQLite that can
> leak sensitive information without an application having adequate
> privileges.
>
​So when did DRH sell out to Android? :-)

From an old (2012) blog report ​HERE


Getting a sense that it's more about Android than sqlite, just as DRH
suggested.


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

Re: [sqlite] SQLite CVE-2015-6607 (Escalation of privilege issue )

[Michael Falconer]

These may enlighten a little..at least it appears to be related?

 http://www.cvedetails.com/cve/CVE-2015-6607/

IBM report 




On 17 April 2017 at 22:09, Simon Slavin  wrote:

>
> On 17 Apr 2017, at 10:35am, Saurav Sarkar 
> wrote:
>
> > Can you please also let me know how this bug can be exploited in an
> > application.
>
> The problem was apparently spotted as a theoretical vulnerability and no
> demonstration code was submitted.  It was never reported to the SQLite
> development team, so the team has no record of what was wrong, what it did,
> or how to exploit it.
>
> From what I can see, it affected only versions of Android before Android
> 5.1.  It allowed an application with exploit code in, if given sufficient
> privilages, to modify certain system files.  I don’t know which files it
> could modify or what damage could be done that way.  If you can find
> discussion of the problem or demonstration code I think it would be welcome
> here.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Why isn't my time formatting working?

[Michael Falconer]

UmI'm one of those sad old hacks who doesn't store dates at all. Just
Years, Months, and Days. You can do just about anything with them stored
that way, and in sqlite it seems to matter little whether they are string
or numeric columns. Dates are a rubbery concept not well suited to db
storage IMHO. ;-)


On 11 March 2017 at 05:35, Jens Alfke  wrote:

>
> > On Mar 8, 2017, at 12:52 PM, R Smith  wrote:
> >
> >> Interestingly I rarely see dates stored in ISO8601 format/text
> >
> > Because every programmer is a self-proclaimed optimization genius!
>
> In this case it often makes sense to optimize in advance. In multiple
> situations over the years I’ve seen date-string parsing be a major
> bottleneck, in operations like database indexing and file reading. It’s
> surprisingly expensive; some of that is due to handling the weirdnesses of
> human date systems, but a lot seems to be because the typical functions
> have to handle arbitrary formats and decipher the format string as well as
> the input. (I’ve found you can do a lot better with a function that’s
> hardcoded to parse a specific date format.)
>
> > If speed/space isn't critical, I always advise ISO8601 dates, typically
> stored (in SQLite anyway) in a NUMERIC typed column.
>
> I basically agree, it’s just that the speed seems to be critical more
> often than one would think :)
>
> At least some date formats, including ISO-8601 with times in UTC, have the
> feature that you can compare dates as strings without having to parse them.
> That makes sorting by date a lot faster.
>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

[Michael Tiernan]

On Mar 7, 2017 6:56 AM, "Brian Curley"  wrote:
> I have successfully coupled shell scripts and the CLI

I'd love to see examples of this sort of use case and I suspect that
there's others who would benefit from seeing how others approach solving
some of the common problems.

Does anyone know where knowledge like this is shared? (Specifically aimed
towards users of SQLite?)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] thousand separator for printing large numbers

[Michael Falconer]

Congrats to all contributors to this thread. Robust discussions like this
make this my absolute favourite list. For the record I like the OP's
suggestion which was more about the features of the printf() function than
anything else. Everybody wins though, because of the great discussion and
the differing viewpoints brought to light. Another great SQLite thread! ;-)

On 12 February 2017 at 05:38, R Smith  wrote:

>
>
> On 2017/02/11 6:50 PM, Clemens Ladisch wrote:
>
>> James K. Lowden wrote:
>>
>>> I doubt you'll win that argument.
>>>
>> You should have checked before writing this.  ;-)
>> http://www.sqlite.org/cgi/src/info/064445b12f99f76e
>>
>
> Pfff, my subsequent points all made moot. Well done and thanks for this!
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Simple web query tool

[Michael Falconer]

>
> Running the sqlite3 command-line shell via cgi works way better than you
> may expect.
>

​Yay verily, and that is really not doing a great tool justice. I've done a
lot of similar things to what Lindsay outlines above both with web and
application targets, Often these procedures are set up as proof of concept
and in a sort of testing mode. I have found that the so-called test setup
actually functioned more reliably and consistently than the application
based code we eventually hacked up. Simple, reliable and very, very
flexible. Sqlite just does it's thing with a minimum amount of fuss and
minimal impact on system resources, can't rave enough. :-)​


On 3 February 2017 at 18:29, Lindsay Lawrence  wrote:

> Running the sqlite3 command-line shell via cgi works way better than you
> may expect.
> The command-line shell has a small footprint and works well with stdio in
> batch mode.
>
> You can run a shell script that runs an instance of the cli shell and reads
> and runs a .sql file.  The sql file and bash can be as complex as it needs
> to be.  You can pass in params on the command-line by inserting env values
> into a temp table and then using that table as necessary in subsequent sql.
>
> For example:
> Configure your httpd for cgi then have a cgi script, say "*report.cgi*":
>
> #!/bin/bash
> /path/to/sqlite3 -bail -batch "/path/to/my.s3db" ".read
> /path/to/report.sql"
>
> and in *"/path/to/report.sql*"
>
> .mode html
> .headers on
> .print Content-Type: text/html
> .print
> .print 
> select * from from report_view;
> .print 
>
> For large datasets, or something you just want to import conveniently into
> a spreadsheet, or another db, for further munging you could set csv mode
> and/or force a download. As a note, unless you are sorting a very large
> dataset the resource usage of all of this is quite low as sqlite just pipes
> the dataset out over the http response as it is generated.
>
> /Lindsay
>
>
> On Wed, Feb 1, 2017 at 8:10 AM, Jay Kreibich  wrote:
>
> > I'm looking for an *extremely* simple web tool that will allow me to
> > configure a dozen or so stored queries, which people can then select and
> > run on an internal server.  If the system supports a query variable or
> two,
> > that would be fantastic, but I don't even need that.  Any thoughts?  Or
> do
> > I dust off the PHP tutorials and spend an afternoon throwing something
> > together?
> >
> >  -j
> >
> >
> > --
> > Jay A. Kreibich < J A Y @ K R E I B I.C H >
> >
> > "Intelligence is like underwear: it is important that you have it, but
> > showing it to the wrong people has the tendency to make them feel
> > uncomfortable." -- Angela Johnson
> > ___
> > sqlite-users mailing list
> > sqlite-users@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,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Replicate SQLite and keep sync (every half hour) from PostgreSQL table

[Michael Nielsen]

I'm able to access a remote PostgreSQL table from my server.
However, the PostgreSQL table contains around 50 mio. records, and I have a
certain column ID which I only need.

I would like to replicate the PostgreSQL table (including a WHERE clause)
to a in-memory SQLite database, which will sync/update every 30 minutes (or
so).

In reboot etc. the replication may start over.

How would be the best approach to such a challenge?

I've actually never worked with SQLite before.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation clarification request... (re: Triggers)

[Michael Tiernan]

In the online documentation: https://sqlite.org/lang_createtrigger.html

It says:

   At this time SQLite supports only FOR EACH ROW triggers, not FOR
   EACH STATEMENT triggers. Hence explicitly specifying FOR EACH ROW is
   optional. FOR EACH ROW implies that the SQL statements specified in
   the trigger may be executed (depending on the WHEN clause) for each
   database row being inserted, updated or deleted by the statement
   causing the trigger to fire.

So that makes sense (I think) and isn't causing a problem for me but 
then, a little further on it says:


   If a WHEN clause is supplied, the SQL statements specified are only
   executed for rows for which the WHEN clause is true. If no WHEN
   clause is supplied, the SQL statements are executed for all rows.

My quandary comes from the last part of that statement "executed for all 
rows". Does that mean "for all rows in the table" or does it mean "for 
all rows /modified/ in the table" or "all rows previously mentioned as 
being inserted, updated, or deleted by the statement causing the trigger 
to fire"?


I know I can "ass-u-me" what the correct answer should be but I really 
don't have the bandwidth to go and clean up the mess I might make if I'm 
wrong.


Thanks for everyone's time!

--
  << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan
  Non Impediti Ratione Cogatationis
  Women and cats will do as they please, and men and dogs
   should relax and get used to the idea. -Robert A. Heinlein

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

Re: [sqlite] SQLite output mode

[Michael Falconer]

Just from some quick command line tool playing around, a dirt quick fix
presented itself. .mode csv followed by .mode column (following the .mode
ascii operation) seems to return the .column mode to normal. This on SQLite
3.16.2 on Linux command line. I'm not suggesting this as a fix as Dr Hipp
has already attended to that but I found it interesting anyway.



On 27 January 2017 at 12:54, Richard Hipp  wrote:

> On 1/26/17, Nikolas Manes  wrote:
> > Hello,
> > I am Nikolas and facing an issue with sqlite, could you please help?
> > You will find more details on the link bellow.
> > http://stackoverflow.com/q/41730574/6293866
>
> The ".mode ascii" command changes the default row and column separators.
>
> Check-in https://www.sqlite.org/src/info/58f02e6eae8fc9e2 enhances the
> command-line shell to change the row and column separator strings back
> to the default when you do ".mode column".  This enhancement will
> appear in the next release.  Or you can recompile using the code on
> trunk, which is stable.
>
> --
> 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
>



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

Re: [sqlite] Possible bug when creating a calculated index

[Michael Falconer]

Thank you Simon,

I do see the inconsistency and thanks for those examples. I had answered
previously before I saw your explanation and I now see why there is
concern. It certainly appears to be inconsistent given such use cases.

On 27 January 2017 at 10:26, Michael Falconer 
wrote:

> Ersin,
>
> apologies if I seem to be suffering from terminal thickness, but I still
> don't get it. Why would I expect anything other than column interpretation
> from a single quoted argument. I *want to be told* that my column does
> not exist, I don't want a calculated index so why should I be expecting
> one. On the other hand if I choose double quotes I'm probably doing
> something different. Maybe someone else should weigh in and the penny will
> finally drop if I am missing the point, but I'm still not seeing crawly
> things. :-)
>
>
> On 27 January 2017 at 10:01, Ersin Akinci  wrote:
>
>> Michael,
>>
>> If I understood DRH and Simon correctly, I think the cause for concern
>> is that SQLite should be interpreting the single quotes as a string
>> literal, yet it interprets it as a column. Perhaps it's a strange
>> example (i.e., why would you want to index a string literal?), but
>> still, the behavior deviates from what's expected, the expected
>> behavior being that we should get a calculated index.
>>
>> Best,
>> Ersin
>>
>> On Thu, Jan 26, 2017 at 2:56 PM, Michael Falconer
>>  wrote:
>> > Simon,
>> >
>> > as I see it there is no problem here. Explicit quoting regardless, the
>> > column does not exist and an error is returned, isn't this the expected
>> > outcome? In the DRH quoted section a reason is presented as to why no
>> error
>> > is returned due to a built in default action. This may or may not be a
>> > point for further analysis (ie. is this an appropriate default) but I'm
>> not
>> > seeing obvious crawly things. Perhaps it's me missing something Simon
>> but
>> > I'm not overly concerned about the above. Don't use double quotes
>> (always
>> > single) and it would appear things are just fine. You'll get told if
>> your
>> > column is non-existent.
>> >
>> >
>> > On 27 January 2017 at 00:12, Simon Slavin  wrote:
>> >
>> >>
>> >> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
>> >>
>> >> > Bug is as follows:
>> >>
>> >> Anyone ?  Did I miss something and you’re all too polite to point it
>> out ?
>> >>
>> >> Simon.
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> >
>> >
>> >
>> > --
>> > Regards,
>> >  Michael.j.Falconer.
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> --
>> Ersin Y. Akinci -- ersinakinci.com
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
>



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

Re: [sqlite] Possible bug when creating a calculated index

[Michael Falconer]

Ersin,

apologies if I seem to be suffering from terminal thickness, but I still
don't get it. Why would I expect anything other than column interpretation
from a single quoted argument. I *want to be told* that my column does not
exist, I don't want a calculated index so why should I be expecting one. On
the other hand if I choose double quotes I'm probably doing something
different. Maybe someone else should weigh in and the penny will finally
drop if I am missing the point, but I'm still not seeing crawly things. :-)


On 27 January 2017 at 10:01, Ersin Akinci  wrote:

> Michael,
>
> If I understood DRH and Simon correctly, I think the cause for concern
> is that SQLite should be interpreting the single quotes as a string
> literal, yet it interprets it as a column. Perhaps it's a strange
> example (i.e., why would you want to index a string literal?), but
> still, the behavior deviates from what's expected, the expected
> behavior being that we should get a calculated index.
>
> Best,
> Ersin
>
> On Thu, Jan 26, 2017 at 2:56 PM, Michael Falconer
>  wrote:
> > Simon,
> >
> > as I see it there is no problem here. Explicit quoting regardless, the
> > column does not exist and an error is returned, isn't this the expected
> > outcome? In the DRH quoted section a reason is presented as to why no
> error
> > is returned due to a built in default action. This may or may not be a
> > point for further analysis (ie. is this an appropriate default) but I'm
> not
> > seeing obvious crawly things. Perhaps it's me missing something Simon but
> > I'm not overly concerned about the above. Don't use double quotes (always
> > single) and it would appear things are just fine. You'll get told if your
> > column is non-existent.
> >
> >
> > On 27 January 2017 at 00:12, Simon Slavin  wrote:
> >
> >>
> >> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
> >>
> >> > Bug is as follows:
> >>
> >> Anyone ?  Did I miss something and you’re all too polite to point it
> out ?
> >>
> >> Simon.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > Regards,
> >  Michael.j.Falconer.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Ersin Y. Akinci -- ersinakinci.com
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Possible bug when creating a calculated index

[Michael Falconer]

Simon,

as I see it there is no problem here. Explicit quoting regardless, the
column does not exist and an error is returned, isn't this the expected
outcome? In the DRH quoted section a reason is presented as to why no error
is returned due to a built in default action. This may or may not be a
point for further analysis (ie. is this an appropriate default) but I'm not
seeing obvious crawly things. Perhaps it's me missing something Simon but
I'm not overly concerned about the above. Don't use double quotes (always
single) and it would appear things are just fine. You'll get told if your
column is non-existent.


On 27 January 2017 at 00:12, Simon Slavin  wrote:

>
> On 25 Jan 2017, at 12:50pm, Simon Slavin  wrote:
>
> > Bug is as follows:
>
> Anyone ?  Did I miss something and you’re all too polite to point it out ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Foreign key integrity checking.

[Michael Tiernan]

I'm going to assume this has come up before so instead of asking for 
help, I'll simply ask for pointers to FAQs about some of the more 
mundane things such as ensuring foreign key integrity and checking for it.


Thanks for everyone's time.

--
  << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan
  Non Impediti Ratione Cogatationis
  Women and cats will do as they please, and men and dogs
   should relax and get used to the idea. -Robert A. Heinlein

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

Re: [sqlite] SQLite3 Tutorial error

[Michael Falconer]

UmSnobol?

Nope absolutely deny all knowledge. Well.actually, didn't cut any code
in it but did hear about Spitbol. No I kid you not, sort of a super Snobol
I was led to believe. I was cutting Cobol at the time, eons ago. :-) A
colleague told me about it and I took a look but not a close one.



On 5 January 2017 at 16:35, Ken Wagner  wrote:

> Keith,
>
> Thanks for the further explanation and the ll alias.
>
> What is so hard to digest is that:
>
> 1. SQLiteMan - a Linux GUI does the *[^1-9]* pattern and omits rows
> with digits 1-9 in them. Using 3.11.0.
> 2. DBBrowser - Linux GUI does the same using 3.9.2 and 3.11.0 in
> different versions, one older.
> 3. SQLite Tutorial @ www.sqlitetutorial.net - Has GLOB lesson page.
> Uses version @ https://www.sqlite.org/download.html. I downloaded
> sqlite-tools-linux-x86-3160100.zip  qlite-tools-linux-x86-3160100.zip>(1.77 MiB), also 3.15.0. Worked as in
> #1 above in both cases in the SQLite Tutorial, but oppositely in the
> SQLite3 CLI.
> 4. The FireFox 50 SQLite Manager add-on. This one is quite good. Good
> layouts, easy to use and detailed. Works as in #1 above. Uses SQLite 3.13.0.
>
> Yet the SQLite3 CLI does the OPPOSITE and _includes_ any names with digits
> 1-9 in them with vv 3.15.0 and 3.16.0.
>
> Do you have any idea why? Is it really the dim sum??
>
> This will have to be explained to my intended customers because some will
> use the SQLite3 CLI. Most will use a GUI as they are not very computer
> literate. I will be offering them training and also directing them to
> training at a good web SQLite tutorial.
>
> All of the above are using the 'chinook.db'. My system is Linux/Ubuntu
> 16.04, SQLite3 3.15.0 and 3.16.0 CLIs and the above programs. I use the CLI
> in both the Terminator and Gnome-Terminal. Some minor differences with
> encoding.
>
>
> Ken
>
>
>
> On 01/04/2017 10:49 PM, Keith Medcalf wrote:
>
> Yes.  The GLOB was invented on Unix.  I posted an example of the Unix
>> filename globbing (which has not changed, to my knowledge, since the 60's),
>> which works exactly the same as the GLOB operator in SQLite 3.9.0 through
>> the current head of trunk.  Perhaps there were minor changes, but nothing
>> that affects the output of the *[1-9]* or *[^1-9]* patterns when applied to
>> the same data used in the Linux demonstration.  However, I did not try and
>> build every single version of SQLite between 3.9.0 to 3.17.0 to see if one
>> of them happened to be broken.  The two ends and a sampling from the middle
>> all worked the same.
>>
>> And by the way, GLOB predates REGEX by about 15 years.  REGEX borrowed
>> (and modified) GLOB syntax.
>>
>> (in case you have never used a Linux/Unix system with an ll command
>> alias, the command to create it is:  alias ll='ls -l')
>>
>> Are you ABSOLUTELY SURE that the authors of the third-party tools have
>> not provided their own GLOB function that works differently, perhaps in
>> accordance with their Dim Sum because their little hearts did not desire
>> the built in one?
>>
>> -Original Message-
>>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>>> On Behalf Of Ken Wagner
>>> Sent: Wednesday, 4 January, 2017 21:24
>>> To: SQLite mailing list
>>> Subject: Re: [sqlite] SQLite3 Tutorial error
>>>
>>> Aha! GLOB is an implementation of the UNIX Glob function. It just
>>> borrows the regex character notation of [^1-9].
>>>
>>> I have 3.15 and 3.16 CLIs installed. Trying any other CLI versions at
>>> this point won't help for the customer. They will be using a SQLite3
>>> GUI. I will explain the difference between the CLI and the GUI versions.
>>>
>>> Where is this UNIX Glob notation spelled out? My system is Linux. I
>>> expect the UNIX version will be somewhat different.
>>>
>>> The Linux GLOB is used to find patterns in pathnames (not databases.) Is
>>> the Unix version the same?
>>>
>>> Thanks,
>>>
>>> Ken
>>>
>>> On 01/04/2017 11:51 AM, R Smith wrote:
>>>

 On 2017/01/04 7:01 PM, Jens Alfke wrote:

> On Jan 4, 2017, at 5:57 AM, R Smith  wrote:
>>
>> As I have it (and as is implemented by SQLite) the GLOB operator
>> implements a REGEXP that matches against a regexp pattern
>>
> No, these are NOT regular expressions in the usual sense of the word.
> GLOB's syntax is incompatible with what are commonly called “regular
> expressions”, and its feature set is a lot more limited. (It may
> technically implement a type of regular expression in the underlying
> algorithmic sense, but I think using the term is misleading.)
>
 Quite correct, I meant REGEXP as an internal function of the
 Regular-expression type, not the official "regular expression" syntax
 - So a "misleading term" then in your words. Allow me to be more clear
 then: GLOB in SQLite specifically matches Unix file globbing syntax
 (which is very different to official RegEx). 3rd party utili

Re: [sqlite] Inspect WAL file?

[Michael Schlenker]

Hi,

have a look at the 'showwal.c' file in the sources.

https://www.sqlite.org/src/artifact/ec79959834f7b21f

It allows you to look at the WAL.., but not sure if it fits your problem.



Am 05.10.2016 um 10:44 schrieb Daniel Polski:
> Do you have any suggestion about how to inspect the contents of a wal file?
> 
> The reason for the examination is that I suspect my application might
> have a bug which makes one thread very occationally not see data in the
> WAL file another thread has added.

Hi,

have a look at the 'showwal.c' file in the sources.

https://www.sqlite.org/src/artifact/ec79959834f7b21f

It allows you to look at the WAL.., but not sure if it fits your problem.

Michael

-- 
Michael Schlenker
Senior Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
E-Mail: michael.schlen...@contact-software.com
http://www.contact-software.com/

Registered office: Bremen, Germany
Managing directors: Karl Heinz Zachries, Ralf Holtgrefe
Court of register: Amtsgericht Bremen HRB 1321
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] importing csv

[Michael Falconer]

Alan,

if you are new to sqlite3 then let me give you a little heads up on
something that tripped me up a couple of times in early days. I am assuming
you are the command line sqlite3 interface which will require you to set
the separator for data imports.

.separator ,

...is likely what you want if your data came from Excel or the like. The
default '|' is probably not what you will want. After that

.import 'datafile' 'table' should have a fighting chance given a good match
between column types and data fields.


On 25 August 2016 at 10:26, Brian Curley  wrote:

> Windows requires that you escape the \ in the path, effectively doubling
> them up.
>
> Regards.
>
> Brian P Curley
>
>
> On Aug 24, 2016 8:24 PM, "Simon Slavin"  wrote:
>
> > I hope someone else can help.
> >
> > Does that table already exist in the database file ?
> >
> > Is the first line of the csv file a line of data or a line of column
> names
> > ?
> >
> > 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
>



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

Re: [sqlite] 64-bit SQLite3.exe

[Michael Falconer]

Yes Richard,

in my experience your quoted time benefits are very much on target. I have
rewritten Excel/Access (VBA) which I originally developed (and was quite
proud of). Re-writes using C/C++ and SQLite indeed delivered the
performance gains you experienced and relieved me of the pain associated
with VBA development. A sort of win-win, and if you throw in moving away
from Windoze to something sensible you can get even more benefit. But that
is another tale...and sadly not always under our control.


On 19 August 2016 at 00:12, Rousselot, Richard A <
richard.a.rousse...@centurylink.com> wrote:

> Yes, it is much faster.  The process was done in Excel/Access before and
> took ages.  I have had processes go from 8 hours before to 30 min now using
> SQLite.
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Michael Gratton
> Sent: Wednesday, August 17, 2016 8:05 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] 64-bit SQLite3.exe
>
>
> Richard,
>
> On Thu, Aug 18, 2016 at 2:23 AM, Rousselot, Richard A <
> richard.a.rousse...@centurylink.com> wrote:
> > I was only interested in doing these calculations quickly; that is my
> > real-world.  The SQL scripts I am using were built with multiple steps
> > with intermediate temp tables that are used to create a final result
> > table.
> >
> > I understand my needs are not generally how others use SQLite.  I am
> > using it as a platform for fast calculations that happens to store
> > results in a way that can be quarried.  Every time, my process begins
> > all prior results are deleted, only the input tables stay the same.
>
>
> Out of curiosity, are you using these in an end-user application, i.e.
> as an interactive response to user actions? I would be curious to know if
> building intermediate temporary tables is fast enough for returning results
> for such uses.
>
> Thanks,
> //Mike
>
> --
> ⊨ Michael Gratton, Percept Wrangler.
> ⚙ <http://mjog.vee.net/>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> This communication is the property of CenturyLink and may contain
> confidential or privileged information. Unauthorized use of this
> communication is strictly prohibited and may be unlawful. If you have
> received this communication in error, please immediately notify the sender
> by reply e-mail and destroy all copies of the communication and any
> attachments.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] SpeedTest1 Comparison of 32 vs 64 bit on Windows 10 13483.15

[Michael Falconer]

Nice Keith,

and very topical as well as being informative. Note a couple of things that
got my curiosity chip activating:

*subquery in result set* test produces interesting outcome with the 64 bit
version bucking the trend. Any ideas there?
Also it would appear *select* clauses demonstrate an above average gain
from 64 bit. Lack of disk activity sprung to my mind but interested to hear
any thoughts there.

excellent information and thanks for sharing it.


On 18 August 2016 at 14:38, Keith Medcalf  wrote:

>
> Same code, same compile options, same compiler version
> options -s -O3 -pipe -march=native -mtune=native -falign-functions=16
> -falign-loops=16 -flto
>
> 32-bit GCC 4.9.3
>
> >speedtest1 --size 1000
> -- Speedtest1 for SQLite 3.15.0 2016-08-17 11:14:39
> a861713cc6a3868a1c89240e8340bc
>  100 - 50 INSERTs into table with no index.
> 0.781s
>  110 - 50 ordered INSERTS with one index/PK
> 1.266s
>  120 - 50 unordered INSERTS with one index/PK..
> 1.672s
>  130 - 25 SELECTS, numeric BETWEEN, unindexed..
> 1.281s
>  140 - 10 SELECTS, LIKE, unindexed.
> 3.031s
>  142 - 10 SELECTS w/ORDER BY, unindexed
> 3.032s
>  145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..
> 3.063s
>  150 - CREATE INDEX five times.
> 2.641s
>  160 - 10 SELECTS, numeric BETWEEN, indexed
> 8.814s
>  161 - 10 SELECTS, numeric BETWEEN, PK.
> 9.282s
>  170 - 10 SELECTS, text BETWEEN, indexed...
> 3.241s
>  180 - 50 INSERTS with three indexes...
> 2.781s
>  190 - DELETE and REFILL one table.
> 3.111s
>  200 - VACUUM..
> 2.282s
>  210 - ALTER TABLE ADD COLUMN, and query...
> 0.094s
>  230 - 10 UPDATES, numeric BETWEEN, indexed
> 8.969s
>  240 - 50 UPDATES of individual rows...
> 1.641s
>  250 - One big UPDATE of the whole 50-row table
> 0.453s
>  260 - Query added column after filling
> 0.078s
>  270 - 10 DELETEs, numeric BETWEEN, indexed
> 2.235s
>  280 - 50 DELETEs of individual rows...
> 2.078s
>  290 - Refill two 50-row tables using REPLACE..
> 5.110s
>  300 - Refill a 50-row table using (b&1)==(a&1)
> 2.798s
>  310 - 10 four-ways joins..
> 5.320s
>  320 - subquery in result set..
>  22.936s
>  980 - PRAGMA integrity_check..
> 4.969s
>  990 - ANALYZE.
> 1.657s
>TOTAL...
> 104.616s
>
> 64-bit GCC 4.9.3
>
> >speedtest1 --size 1000
> -- Speedtest1 for SQLite 3.15.0 2016-08-17 11:14:39
> a861713cc6a3868a1c89240e8340bc
>  100 - 50 INSERTs into table with no index.
> 0.797s
>  110 - 50 ordered INSERTS with one index/PK
> 1.250s
>  120 - 50 unordered INSERTS with one index/PK..
> 1.609s
>  130 - 25 SELECTS, numeric BETWEEN, unindexed..
> 0.969s
>  140 - 10 SELECTS, LIKE, unindexed.
> 2.859s
>  142 - 10 SELECTS w/ORDER BY, unindexed
> 2.860s
>  145 - 10 SELECTS w/ORDER BY and LIMIT, unindexed..
> 2.813s
>  150 - CREATE INDEX five times.
> 2.219s
>  160 - 10 SELECTS, numeric BETWEEN, indexed
> 6.751s
>  161 - 10 SELECTS, numeric BETWEEN, PK.
> 7.229s
>  170 - 10 SELECTS, text BETWEEN, indexed...
> 2.719s
>  180 - 50 INSERTS with three indexes...
> 2.266s
>  190 - DELETE and REFILL one table.
> 2.266s
>  200 - VACUUM..
> 1.735s
>  210 - ALTER TABLE ADD COLUMN, and query...
> 0.062s
>  230 - 10 UPDATES, numeric BETWEEN, indexed
> 7.329s
>  240 - 50 UPDATES of individual rows...
> 1.516s
>  250 - One big UPDATE of the whole 50-row table
> 0.437s
>  260 - Query added column after filling
> 0.047s
>  270 - 10 DELETEs, numeric BETWEEN, indexed
> 2.047s
>  280 - 50 DELETEs of individual rows...
> 1.938s
>  290 - Refill two 50-row tables using REPLACE..
> 4.438s
>  300 - Refill a 50-row table using (b&1)==(a&1)
> 2.360s
>  310 - 10 four-way

Re: [sqlite] 64-bit SQLite3.exe

[Michael Gratton]

Richard,

On Thu, Aug 18, 2016 at 2:23 AM, Rousselot, Richard A 
 wrote:
I was only interested in doing these calculations quickly; that is my 
real-world.  The SQL scripts I am using were built with multiple 
steps with intermediate temp tables that are used to create a final 
result table.


I understand my needs are not generally how others use SQLite.  I am 
using it as a platform for fast calculations that happens to store 
results in a way that can be quarried.  Every time, my process begins 
all prior results are deleted, only the input tables stay the same.



Out of curiosity, are you using these in an end-user application, i.e. 
as an interactive response to user actions? I would be curious to know 
if building intermediate temporary tables is fast enough for returning 
results for such uses.


Thanks,
//Mike

--
⊨ Michael Gratton, Percept Wrangler.
⚙ <http://mjog.vee.net/>


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

Re: [sqlite] Exec vs Prepare, step, finalize.

[Michael Falconer]

...as for Stephen, Mr Beal you need to get out more LOL! Little Johnny
Tables indeed. Rub it in, why not? LOL


On 12 August 2016 at 09:38, Michael Falconer 
wrote:

> Thanks all,
>
> must admit to being around db's for years but I never did get my head
> around the whole injection thing, sad but true. Keith summed it up in usual
> succinct fashion which when read by one old hack cause much reddening of
> the facial features. Bugger, says I, that speaks my language and it's
> saying you are a goose! I'm admitting to no more!
>
> Thanks all for opening my eyes, at long last, and excuse me while I grep
> my code for sqlite3_exec()grr...damnetc.
>
>
> On 11 August 2016 at 23:40, Quan Yong Zhai  wrote:
>
>> > From: michael.j.falco...@gmail.com
>> > Date: Thu, 11 Aug 2016 15:53:39 +1000
>> > To: sqlite-users@mailinglists.sqlite.org
>> > Subject: Re: [sqlite] Exec vs Prepare, step, finalize.
>> >
>> > I have a self styled routine (similar to the glibc manual example) for
>> > concatenating the strings values that make up the sql statement. It uses
>> > memcpy rather than the built in strcat etc.
>> sqlite3_mprintf  http://www.sqlite.org/c3ref/mprintf.html provide some
>> formattingoptions to defending SQL injection. '%Q' to quote string
>> parameters, '%w' to quote table name or column name..
>> >So what exactly is the issue
>> > with the string building if it does  not include sql derived from user
>> > input? I'm not quite seeing that bit, sorry or the vagueness
>> >
>> > It does however sound like it would just be better to adopt the three
>> step
>> > functions as the preferred method in all cases, which is probably what
>> I'm
>> > trying to come to grips with. I do see the prepare/step/finalize process
>> > with bound parameters etc is very much preferred in most cases, but
>> > wondered if those cases where SQL is application provided were an
>> > exception. I'm leaning towards a no on that now. Thanks for your input
>> and
>> > in advance or any additional insight.
>> >
>>
>> I am not a security expert, but I think the culprit of SQL injection
>> vulnerability in SQLite is not sqlite3_exec(). It's the way  how the SQL
>> command text constructed. if you look into the SQLite source code, there
>> are many places used sqlite3_exec(), and  theparameters are carefully
>> quoted by '%Q', '%q' or '%w'.
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Regards,
>  Michael.j.Falconer.
>



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

Re: [sqlite] Exec vs Prepare, step, finalize.

[Michael Falconer]

Thanks all,

must admit to being around db's for years but I never did get my head
around the whole injection thing, sad but true. Keith summed it up in usual
succinct fashion which when read by one old hack cause much reddening of
the facial features. Bugger, says I, that speaks my language and it's
saying you are a goose! I'm admitting to no more!

Thanks all for opening my eyes, at long last, and excuse me while I grep my
code for sqlite3_exec()grr...damnetc.


On 11 August 2016 at 23:40, Quan Yong Zhai  wrote:

> > From: michael.j.falco...@gmail.com
> > Date: Thu, 11 Aug 2016 15:53:39 +1000
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: Re: [sqlite] Exec vs Prepare, step, finalize.
> >
> > I have a self styled routine (similar to the glibc manual example) for
> > concatenating the strings values that make up the sql statement. It uses
> > memcpy rather than the built in strcat etc.
> sqlite3_mprintf  http://www.sqlite.org/c3ref/mprintf.html provide some
> formattingoptions to defending SQL injection. '%Q' to quote string
> parameters, '%w' to quote table name or column name..
> >So what exactly is the issue
> > with the string building if it does  not include sql derived from user
> > input? I'm not quite seeing that bit, sorry or the vagueness
> >
> > It does however sound like it would just be better to adopt the three
> step
> > functions as the preferred method in all cases, which is probably what
> I'm
> > trying to come to grips with. I do see the prepare/step/finalize process
> > with bound parameters etc is very much preferred in most cases, but
> > wondered if those cases where SQL is application provided were an
> > exception. I'm leaning towards a no on that now. Thanks for your input
> and
> > in advance or any additional insight.
> >
>
> I am not a security expert, but I think the culprit of SQL injection
> vulnerability in SQLite is not sqlite3_exec(). It's the way  how the SQL
> command text constructed. if you look into the SQLite source code, there
> are many places used sqlite3_exec(), and  theparameters are carefully
> quoted by '%Q', '%q' or '%w'.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Exec vs Prepare, step, finalize.

[Michael Falconer]

Thanks Jay,

excellent response. I'll ask for clarity on one statement though.

That’s the basic theory, but even knowing that, most people get it wrong.
> In short, if you’re using string manipulation functions to build your query
> string, you’re very very very very likely doing it wrong.
>

I have a self styled routine (similar to the glibc manual example) for
concatenating the strings values that make up the sql statement. It uses
memcpy rather than the built in strcat etc. So what exactly is the issue
with the string building if it does  not include sql derived from user
input? I'm not quite seeing that bit, sorry or the vagueness.

It does however sound like it would just be better to adopt the three step
functions as the preferred method in all cases, which is probably what I'm
trying to come to grips with. I do see the prepare/step/finalize process
with bound parameters etc is very much preferred in most cases, but
wondered if those cases where SQL is application provided were an
exception. I'm leaning towards a no on that now. Thanks for your input and
in advance or any additional insight.


On 11 August 2016 at 14:32, Jay Kreibich  wrote:

>
> On Aug 10, 2016, at 9:21 PM, Michael Falconer <
> michael.j.falco...@gmail.com> wrote:
>
> > Hi all,
> >
> > just seeking some opinions, and perhaps some dev indications about
> > deprecation, in relation to the sqlite3_exec facility. I kind of like the
> > callback functionality in certain cases as it is convenient in some
> > circumstances where the sql injection problem is not an issue.
> >
> > Ok I say it is not an issue, but am I right. I am no security expert and
> > have often been surprised at some of the hack techniques used over the
> > years. The sql injection issue as far as I can tell depends on where the
> > offending sql originates, but don't hesitate to contradict that
> assumption
> > if you believe it is wrong.
>
> That’s the basic theory, but even knowing that, most people get it wrong.
> In short, if you’re using string manipulation functions to build your query
> string, you’re very very very very likely doing it wrong.
>
> > In a scenario where the sql supplied to the callback routine is
> application
> > generated or indeed application constant based does the sql injection
> > threat disappear?
>
> Yes.  The threat is when you start to use sprintf() to built your query
> strings.
> Even more so if some of those inputs can trace their origin to user
> generated
> values.
>
> > user supplied sql via arguments, with only database name and table name
> > required from the user. This would appear to be immune to that technique
> or
> > am I misguided? I'm never certain when it comes to security stuff, I hate
> > it.
>
> You are misguided.  The whole idea behind injections is that you can alter
> that
> database name so that it is much more than a database name.  If you can’t
> understand the whys of it, you can’t defend against it.  And that’s
> important
> in this case, since you can’t use bound parameters for database names or
> table names.  Switching to _prepare() won’t help in this specific case
> because
> you have no choice but to build the query from string primitives.
>
>
> Another issue with sqlite_exec() is that all the values are returned as
> strings.
> You have no idea what the type of the field is, and if it is a non-string
> value,
> it is converted to a string before the callback is called.  This can be a
> big issue
> for many designs.
>
> > In a similar vein I noted in an O'Reilly publication it mentioned that
> the
> > exec method was semi depracated and should be avoided. I wondered what
> the
> > view of the SQLite dev crew was. and if there were any plans in the
> future
> > to drop the exec function? In light of the teams focus on backward
> > compatibility I suspect there are no such plans but I thought I'd ask
> > anyway just to be sure. Thanks in advance for any helpful comments.
>
>   -j  (Author, Using SQLite).
>
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but
> showing it to the wrong people has the tendency to make them feel
> uncomfortable." -- Angela Johnson
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Exec vs Prepare, step, finalize.

[Michael Falconer]

Hi all,

just seeking some opinions, and perhaps some dev indications about
deprecation, in relation to the sqlite3_exec facility. I kind of like the
callback functionality in certain cases as it is convenient in some
circumstances where the sql injection problem is not an issue.

Ok I say it is not an issue, but am I right. I am no security expert and
have often been surprised at some of the hack techniques used over the
years. The sql injection issue as far as I can tell depends on where the
offending sql originates, but don't hesitate to contradict that assumption
if you believe it is wrong.

In a scenario where the sql supplied to the callback routine is application
generated or indeed application constant based does the sql injection
threat disappear? Is this a valid assumption? In other words there is no
user supplied sql via arguments, with only database name and table name
required from the user. This would appear to be immune to that technique or
am I misguided? I'm never certain when it comes to security stuff, I hate
it.

In a similar vein I noted in an O'Reilly publication it mentioned that the
exec method was semi depracated and should be avoided. I wondered what the
view of the SQLite dev crew was. and if there were any plans in the future
to drop the exec function? In light of the teams focus on backward
compatibility I suspect there are no such plans but I thought I'd ask
anyway just to be sure. Thanks in advance for any helpful comments.


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

Re: [sqlite] 64-bit SQLite3.exe

[Michael Falconer]

Thanks Ryan,

and apologies for singling your comment out from the many that suggested a
roll-your-own CLI. That last statement pretty much says it all IMHO but I
would go even further. For many it will not even be choice in future times.
There will always be those who can make their own arrangements and hedge
around the inevitable but for most it will be 64bit or nothing one day. An
above post regarding Android 32 bit platform support is one indication that
the choices will not last for much longer. I should add that it is not
entirely high on my personal wish list (64bit CLI) because I do take the
valid points made earlier that the current CLI works just fine in a 32 bit
environment.

All of that being said, I have ultimate faith in the core SQLite
development team to make the right call about when it would be appropriate
to go the 64 bit path. There would be much to consider and it should be
considered carefully as always, I'm in no rush but apparently some others
have more pressing needs. Just as I am not in a rush, I also have no
problem with the OP's request being implemented. I'm with Ryan on the warm
and cuddly approach, we might as well.


On 11 August 2016 at 10:58, R Smith  wrote:

>
> On 2016/08/10 11:35 PM, Michael Falconer wrote:
>
>> ...// Ryan is very
>> right about the relative ease of such exercises but IMHO wrong that it
>> suits all, or even most cases.
>>
>
> Just to be clear - if my post wasn't, I never advocated against the 64-bit
> CLI or that making it suits all, I very much supported the OP in the
> request (and still do) for some of the reasons Michael mentions here. All I
> said was that right now (until it is supplied), if he needs it as much as
> claimed, rolling his own is not all that hard (and certainly not a useless
> skill).
> I do not agree with the notion that the 64-bit CLI "shouldn't be
> pre-compiled because it's easy to do it yourself" - if that was true, why
> supply the 32-bit even?, or indeed ANYthing pre-compiled?
>
> To reiterate: 64-bit is the new thing - let's embrace it!
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] 64-bit SQLite3.exe

[Michael Falconer]

Great discussion, if somewhat amusing (with a hint of deja vu) for an old
hack like myself.

I remember discussions like this back in CP/M days when it was a move rom 8
to 16bit. Oh, and it all happened again when 32bit came along and yes now
we are into 64bit and it carries on.

Simple really, the OP is completely correct to be requesting a 64bit CLI as
we are now very much in that world. Of course a legacy 32bit version is
appropriate but surely the mainstream dev must move on to 64bit at  some
stage? Not for me to say when this should occur but surely it is inevitable
or am I just an old dinosaur?

Some excellent side points made (single out DD here) and one o the
standouts is the quite ridiculous stance many developers take regarding
user requests such as the one made in the OP. There is absolutely no
requirement at all for end users to be software developers/engineers, that
would be absurd and I doubt this was ever the intent of sqlite development.
Roll your own compilations in some cases are not an option and quite
possibly for multiple reasons. I'm not going to bore you by expanding the
obvious, but the OP identified one such case as a regulated corporate
environment, a beast I am unfortunately all too familiar with. Ryan is very
right about the relative ease of such exercises but IMHO wrong that it
suits all, or even most cases.

I may be old, but I'm a progressive dinosaur. We have to move on, and in
this biz, quickly if we want to remain in any way current. We can make
choices as developers about whether we embrace such things as OO or R or
whatever new methodology comes along but we don't get that same choice
about base system changes such as 16/32/64 bit processors. They are like
death and taxes, inevitable.

So when the 64bit CLI arrives, as it surely will do, the OP will be
vindicated and all the rest will be historic fluff. Just my opinion, but as
I said great discussion from my very favourite technical list.


On 11 August 2016 at 01:11, Jim Callahan 
wrote:

> The issue is not simply recompiling the CLI for 64-bit; I assume you want
> the tested reliability of SQLite.
>
> "The [SQLite] project has 787 times as much test code and test scripts" as
> it does source code for the executable.
> https://www.sqlite.org/testing.html
>
>
> Running the exact same tests would not be enough, because you (and I) would
> want special tests for larger than 4 GB
> RAM. So, additional tests would have to be developed.
>
> So, I see three alternate solutions:
>
> 1. Find a corporate sponsor to fund development of 64-bit SQLite CLI
>
> 2. Remove some tables from memory (though it sounds like your difficulties
> are caused
> by recursion rather than the size of the raw data)
>
> 3. Use SQLite for persistent storage but move the in memory (tree
> navigation) operations to another (open source)  language
> such as C, Java, Python or R (or the new Julia language that is approaching
> version 1.0)  that has interfaces for SQLite
> and a 64-bit build for Windows.  You will probably need another language to
> display the output anyway why not take
> advantage of Python, R or Julia?
>
> Even if you move to another language, you may find that your problem is
> recursion.
> In my experience, computer science textbooks give elegant examples using
> recursion,
> but then say the solution is not scale-able and give a less elegant
> solution using iterative techniques.
>
> Jim Callahan
> Data Scientist
> Orlando, FL
>
>
> On Tue, Aug 9, 2016 at 10:31 AM, Rousselot, Richard A <
> richard.a.rousse...@centurylink.com> wrote:
>
> > I would like to request a SQLite official 64-bit SQLite3.exe CLI (not
> DLL)
> > be created.
> >
> > I have reviewed the prior discussions regarding 64-bit SQLite3 and the
> > reasoning for which why creating a 64-bit version is denied are "it does
> > not make a real difference", "you can just use ram disks", etc., etc.
> >
> > Here is my plea...  I am using a set of complicated CTEs to crawl through
> > a network (tree) to aggregate and calculate formulas.  I don't have
> > exceptionally large datasets but my CTEs result in a ton of memory usage.
> > The process works well from disk, in Windows, but using a smaller test
> > sample I get about a 30% to 40% increase in processing time if I set the
> > PRAGMA to temp_store = 2.  If I use a normal dataset, not a small test, I
> > hit an approximate 2G limit and get a "out of memory" message, which I
> > understand is due to SQLite3.exe being 32-bit.  I have found some 3rd
> party
> > 64-bit builds for SQLite3 (best found is 3.8.5) but they are out of date
> > and don't allow all functionality that I am using.  So, I do have a use
> > case that requires 64-bit and I would see a significant increase in
> speed.
> >
> > As to RAM disks, I work in a corporate environment that locks down user
> > rights which precludes me from distributing a tool that requires the
> > creation of a tool that needs administrator rights.  I also, would like
> to
> > avoid ha

Re: [sqlite] DB Partitioning and Performance

[Michael Hari]

Thanks for the reply Simon. 

We have database pooling since our databases are read only and we wanted to 
take advantage of multithreading. Each database connection had the temp view 
defined, but I saw a huge performance drop when it came to queries.

I’m going to try sub-selects for now with the suggestion from R Smith that I 
filter those sub-selects. Hopefully it works. 



Thanks Simon and R Smith!

On 5/27/16, 5:29 AM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
Simon Slavin"  wrote:

>
>On 27 May 2016, at 3:17am, Michael Hari  wrote:
>
>> I have a database that contains 2014,2015 and 2016’s worth of data broken 
>> down by year and quarter. In total, this db was 2.36 GB. Because of a 
>> replication requirement where the DB has to be under 2 GB, I’ve split the DB 
>> into 3 smaller databases by year (2014.db,2015.db,2016.db).
>> 
>> I would need to access at most two databases in one query for a year over 
>> year calculation over 6 quarters. Do I have the same performance as having 
>> one table with all the data if I write my sql this way?
>> 
>> Attach 2014.db as 2014
>> Attach 2015.db as 2015
>> 
>> (..Calculation..) FROM (SELECT * FROM 2014.datatable  UNION ALL SELECT * 
>> FROM 2015.datatable)
>> 
>> You can assume the separate databases have the same indexing as the single 
>> db, where it’s indexed by year and quarter.
>
>Thank you for your very clear explanation of your setup and your question, 
>which has saved lots of back-and-forth.  Your performance will be a little 
>slower than it would be if all the data was in one database file, but not 
>much.  Given what you're doing and why you're doing it I think you have found 
>a good solution and would not do it any other way.  A couple of notes:
>
>A) Using a database alias that starts with a digit makes me uncomfortable.  I 
>can't point at any documentation or particular problem this will cause but if 
>I was doing it I'd put at least a 'Y' for 'year' in front of the database 
>name, as in "ATTACH 2014.db AS Y2014".
>
>B) For equal treatment you may want to create an empty database and use that 
>as the one you connect to.  Then attach the yearly databases to this 
>connection including the one for the current year.  This may simplify what 
>needs to be done at year-end.
>
>C) Once you have attached all your databases you can create a view which will 
>simplify your access to the data:
>
>CREATE TEMPORARY VIEW datatableall AS SELECT * FROM Y2014.datatable UNION ALL 
>SELECT * FROM Y2015.datatable UNION ALL SELECT * FROM Y2016.datatable
>
>From then on you don't need to mention the separate tables in code which 
>consults the tables, just treat datatableall as if it's one table.
>
>D) VACUUM databases which will no longer change.  VACUUM the current year's 
>database at year-end.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=CwIGaQ&c=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4&r=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg&m=Iv0ThknGqZ6jlx5nm2YCXf3G5PvjrMtxSMJCSiOMmcI&s=T4x-97XOSoM43DLhZGThEZOOht0kLdrxKTkHQv6_Sd0&e=
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DB Partitioning and Performance

[Michael Hari]

I have a database that contains 2014,2015 and 2016’s worth of data broken down 
by year and quarter. In total, this db was 2.36 GB. Because of a replication 
requirement where the DB has to be under 2 GB, I’ve split the DB into 3 smaller 
databases by year (2014.db,2015.db,2016.db).

I would need to access at most two databases in one query for a year over year 
calculation over 6 quarters. Do I have the same performance as having one table 
with all the data if I write my sql this way?

Attach 2014.db as 2014
Attach 2015.db as 2015

(..Calculation..) FROM (SELECT * FROM 2014.datatable  UNION ALL SELECT * FROM 
2015.datatable)

You can assume the separate databases have the same indexing as the single db, 
where it’s indexed by year and quarter.

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

Re: [sqlite] Attach Database and Database pooling

[Michael Hari]

Never mind, there was an error in my code.




On 5/26/16, 11:52 AM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf 
of Michael Hari"  wrote:

>Hi All,
>
>
>
>I’m trying to preallocate about 20 sqlite connections each with the same 
>“ATTACH DB” command. I’m getting “database x already in use” errors. Is this 
>not possible to do? Can each independent X connection not have the same 
>attached DB command? If not, what’s the preferred approach?
>
>
>
>Thanks,
>
>Michael
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=CwIGaQ&c=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4&r=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg&m=HhCYpnNqJbSVCUsukMrpMTa_qXf1gNz3onTzaC3UKsQ&s=7BA0OPqKASv6CXn2c9tmtmY8AXKQ3Ye2cuwdz6tIYR0&e=
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Attach Database and Database pooling

[Michael Hari]

Hi All,

I’m trying to preallocate about 20 sqlite connections each with the same 
“ATTACH DB” command. I’m getting “database x already in use” errors. Is this 
not possible to do? Can each independent X connection not have the same 
attached DB command? If not, what’s the preferred approach?

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

Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?

[Michael Hari]

What about if we shard the tables in the database across multiple databases? Is 
that possible to do? Does SQLite support the concept of sharding?

Thanks,
Michael


On 5/24/16, 10:20 AM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf 
of Simon Slavin"  wrote:

>
>On 24 May 2016, at 6:04pm, Michael Hari  wrote:
>
>> I’ve come to the conclusion that I would need to “split the file into two 
>> databases” as well. The challenge here is that the larger db will have to 
>> have a limit of 2 GB and will need to make a new db when it hits that limit. 
>> It would have to split it into multiple Dbs as the data grows (so 
>> db1.sqlite, db2.sqlite, etc.). Would you happen to know any strategies to 
>> accomplish this in sqlite? Either through SQL or scripting?
>
>Create a versioning system for your database which is not the same as the 
>version of your application.  Continue to use Sparkle for your application but 
>not for the database file.
>
>Upgrading your database from one version to another can be done in three ways:
>
>A) Distribute an additional .sqlite file.  To update the local database have 
>the application read the data from this file and add it to the local copy of 
>the database.
>
>B) Distribute a .zipped text file containing the SQL commands needed to update 
>the local copy of the database.  To update the local database have the 
>application read the commands from this file and execute them.
>
>C) Distribute a .zip containing a .csv file for each table.  To update the 
>local database have the application read the text from this file and convert 
>it to INSERT commands.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=CwIGaQ&c=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4&r=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg&m=ahSUBK1T7j-1qWdH9sKUO09_FAWT0Eeb1s32vYabAg0&s=9lhqAP5mAfvcMjMuMHs6L0HxVgtTW0uXHq79XI8KeYg&e=
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?

[Michael Hari]

Hi Simon,

We don’t want to zip up the entire db unfortunately. It would be around 250 MB 
zipped versus having Sparkle update with a 38 MB patch. (Bandwidth costs are 
important for us)

I’ve come to the conclusion that I would need to “split the file into two 
databases” as well. The challenge here is that the larger db will have to have 
a limit of 2 GB and will need to make a new db when it hits that limit. It 
would have to split it into multiple Dbs as the data grows (so db1.sqlite, 
db2.sqlite, etc.). Would you happen to know any strategies to accomplish this 
in sqlite? Either through SQL or scripting?

Thanks,
Michael


On 5/23/16, 1:46 PM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
Simon Slavin"  wrote:

>
>On 23 May 2016, at 9:08pm, Piyush Shah  wrote:
>
>> We know about sqldiff 
>> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.sqlite.org_sqldiff.html&d=CwICAg&c=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4&r=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg&m=H8MM4zcxx8e1CF2D98PKVDGp8SvCTyw-dv7XoQ5KPw4&s=8qlz1JEuXvYSnt4FvMj1V_UVQ2pQqaCG78j0tbSf3pU&e=
>>and would use it to generate diffs and patch them but we were hoping 
>> there was a way to do do a binary diff of the database because otherwise we 
>> will have to figure out a way for the client to run the sqldiff utility to 
>> patch the database.
>
>There's no easy way to integrate Sparkle and sqldiff so that they work 
>together like that.  The amount of work it would take would be equivalent to 
>writing your own tool which updated the database across the internet.
>
>If the database file is small, just let Sparkle update it with the other 
>components of the application.  If the database file is big, it may be worth 
>doing work on it.
>
>Does the database file compress well as a ZIP file ?  Then distribute it as a 
>.zip.  On startup, detect if the decompressed version is not present, or has a 
>datestamp before the .zip version.
>
>Do you have roughly equal parts of the file taken up with tables which never 
>change and tables which change with each update ?  Then split the file up into 
>two database files and use ATTACH to access them both via one connection.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=CwICAg&c=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4&r=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg&m=H8MM4zcxx8e1CF2D98PKVDGp8SvCTyw-dv7XoQ5KPw4&s=deDylKqCP4ECaSOv3XoqH0xfznS3YHz3ZlsPDbzxbXE&e=
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS offsets() reporting SQLITE_CORRUPT with empty string, redux

[Michael Gratton]

Hi all,

I'm running into a bug where the FTS offsets() function is returning a 
null string, causing SQLITE_CORRUPT to be reported for SELECT query 
results in both SQLite 3.11 and 3.12.2.

This is similar to a previously reported issue with SQLite from a while 
back - <http://www.sqlite.org/src/info/9861b74ab9> 
<http://sqlite.1065341.n5.nabble.com/regression-in-FTS3-offsets-function-in-3-6-23-td15216.html>,
 
however the minimal example from that report does not trigger any error 
in 3.11/3.12.2.

Unfortunately, I haven't been able to produce a minimal example for 
this new instance of the bug. Taking problematic rows from the existing 
DB, inserting them into a new FTS4 table, then running the same query 
is not triggering it. I can however reproduce it with a freshly 
constructed copy of the application's database, so it may not be an 
actual corrupt database. I cannot post the database file to a public 
forum however, since it contains confidential information.

A minimal query on the database in question that produces the error is 
simply of the form:

 > SELECT offsets(FTS4Table) FROM FTS4Table WHERE FTS4Table MATCH 
'somestr';

I can say that it is the offsets() function that is causing it - 
removing/replacing it in the SELECT statement makes the error go away. 
The form of the MATCH string doesn't seem to matter, but only some 
strings will cause the error to appear. Also, a custom tokeniser is in 
use - unicodesn from here: 
<https://git.gnome.org/browse/geary/tree/src/sqlite3-unicodesn>

For context, the application's bug report is here: 
<https://bugzilla.gnome.org/show_bug.cgi?id=765515>

What can I do to help resolve this bug?

//Mike

-- 
? Michael Gratton, Percept Wrangler.
? <http://mjog.vee.net/>

[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing

[Michael Schlenker]

Hi,

Am 06.04.2016 um 15:00 schrieb Cezary H. Noweta:
> Hello,
> 
> On 2016-04-06 09:43, Darren Duncan wrote:
>> On 2016-04-05 10:19 AM, Richard Hipp wrote:
> 
>>> It seems to me that the most consistent answer is that the "type" of
>>> columns in a VIEW should always be an empty string.
> 
>> That's only consistent if you do the same thing with base tables.
> 
> Non--consistency:
[snip]
> 
> Column's affinity is still propagated:
> 
> sqlite> CREATE TABLE b2 AS SELECT * FROM bv;
> sqlite> PRAGMA table_info(b2);
> 0|a|NUM|0||0
> 
> so the point is that ``PRAGMA table_info'' stopped displaying column's
> affinity in case of views.
> 
> On the other side, views are not tables so a consistency does not
> require to preserve that info. In 3.11.x ``PRAGMA table_info'' was not
> displaying an affinity in case of expressions, too:
> 
> === SHELL 3.11.x ===
> sqlite> CREATE VIEW av2 AS SELECT CAST(+a AS NUMERIC) FROM a;
> sqlite> PRAGMA table_info(av2);
> 0|CAST(+a AS NUMERIC)||0||0
> 
> so the decision was to remove that view's info at all.

we have been bitten by this case, e.g. aggregate and expression not
giving any meaningful info for views.

But this change to PRAGMA table_info() throws out the child with the
bathwater in a way.

E.g. we currently try to find the types for a view in a multistage process:

1. Inspect PRAGMA table_info()
2. Look in a special hint table that states the type explicitly via
configuration (e.g. for views with aggregate functions)
3. Select a row with LIMIT 1 and look at the result (fails if the view
is empty).

With the old behaviour, the case 1 would find a reliable result 99% of
the time (for our schema). Now we either have to add additional
configuration just for SQLite (because similar code works fine for
Oracle and MS SQL Server), or hope that case 3 works.

btw. would be nice to have an INFORMATION SCHEMA style view for this
info instead of a pragma.

Michael

-- 
Michael Schlenker
Senior Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Stra?e 1-3   Fax:+49 (421) 20153-41
28359 Bremen
E-Mail: michael.schlenker at contact-software.com
http://www.contact-software.com/

Registered office: Bremen, Germany
Managing directors: Karl Heinz Zachries, Ralf Holtgrefe
Court of register: Amtsgericht Bremen HRB 1321

-- 
Michael Schlenker
Senior Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Stra?e 1-3   Fax:+49 (421) 20153-41
28359 Bremen
E-Mail: michael.schlenker at contact-software.com
http://www.contact-software.com/

Registered office: Bremen, Germany
Managing directors: Karl Heinz Zachries, Ralf Holtgrefe
Court of register: Amtsgericht Bremen HRB 1321

[sqlite] Get count of unique values?

[Michael Falconer]

Both Igor's and Simon's solutions work on my test system.

The only additional comment is that Simon's works in Sqlite as expected.
However running the same on PostgreSQL bombs with an error complaining
about an sub-query with no name! Interesting? Perhaps watch out for
portability with that one. Igor's 'select count(distinct)' worked on both
without problem and returned the same results.


On 18 February 2016 at 22:14, Simon Slavin  wrote:

>
> On 18 Feb 2016, at 5:42am,   
> wrote:
>
> > Then I want to obtain the total count of unique values for F1 field. In
> the
> > above sample, since unique F1 field values are 1, 2, 3 and 4, so the
> count
> > is 4. How to write SQL query to obtain the value?
>
> First, get a list of the unique values:
>
> SELECT F1 FROM MyTable GROUP BY F1
>
> then count them
>
> SELECT count(*) FROM (SELECT F1 FROM MyTable GROUP BY F1)
>
> The above code is just from my head.  I have not tested it.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.

[sqlite] Performance comparison between SQLite and SQL Server?

[Michael Falconer]

Good thread,

which absolutely nails the point 'dev decisions for app cases' make a
developers world go round. I personally couldn't think of a greater waste
of time than a benchmark comparison between client server rdbms's and
sqlite. Do what benefits your case most. The above from Jim pretty much
encapsulates my thoughts:

"SQLite is not directly comparable to client/server SQL database engines
> such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to
> solve a different problem.   Client/server SQL database engines strive to
> implement a shared repository of enterprise data. ...SQLite strives to
> provide local data storage for individual applications and devices."
>

I could bang on about my own preferences and decisions I've made but they'd
only be reiterating the points made above. They were based on system
requirement specs and where local storage was involved it was a blindingly
obvious decision to go with sqlite. Rob above made another excellent point
often overlooked (usually an afterthought for many dev's):

4. The support is top notch. I have brought and paid for govt scale
> databases for governments and to be honest the support for SQLite is just
> as good, and to be honest I would say better than Big Red or Big Blue (and
> I used to work for Big Blue).
>

It is another unique property of a great product. Support is not just
sqlite specific either (a cop out on many a tech forum) and particularly on
this list the topics can be rather broad. There is plenty of good quality
feedback and many a good general SQL solution which just adds to the sqlite
package as a whole.


On 16 February 2016 at 09:42, Jim Callahan 
wrote:

> SQLite would be most comparable to *SQL Server Express LocalDB* edition
> which is introduced in this July 2011 blog post
>
> https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/
>
> More uptodate information about *SQL Server Express LocalDB* edition
> is in this 2016 Microsoft Developer's Network (MSDN) article
> https://msdn.microsoft.com/en-us/library/hh510202.aspx
>
> This page "*Appropriate Uses for SQLite*" (whentouse.html) describes BOTH
> "*Situations Where SQLite Works Well*"
>
> and
>
> "*Situations Where A Client/Server RDBMS May Work Better*"
> http://sqlite.org/whentouse.html
>
>
> Opening lines of whentouse.html:
>
> "SQLite is not directly comparable to client/server SQL database engines
> such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to
> solve a different problem.   Client/server SQL database engines strive to
> implement a shared repository of enterprise data. ...SQLite strives to
> provide local data storage for individual applications and devices."
>
> Even Microsoft has adopted SQLite for some limited tasks (such as storing
> state) within every shipping copy of Windows 10.
> "SQLite is a unique case: it is an open source, externally developed
> software that is used by core system components, and our flagship apps like
> Cortana and Skype.  ...After shipping SQLite as a system component in July,
> we wanted to include it in our SDK for November. With more than 20,000
> Windows Apps and more than half of our top apps using SQLite, it made sense
> to just make expose the system SQLite to app developers."
> http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/
>
>
> There is a historical and unfair (specially compiled version of SQLite
> against default settings of PostgreSQL) benchmark
> available on this page, but now that you understand the use cases, this
> particular benchmark is not that useful in addition
> to being out of date and unfair.
> https://www.sqlite.org/speed.html
>
> Jim Callahan
> Data Scientist
> https://www.linkedin.com/in/jamesbcallahan
> Orlando, FL
>
> On Mon, Feb 15, 2016 at 4:54 PM, Simon Slavin 
> wrote:
>
> >
> > On 15 Feb 2016, at 9:41pm, James K. Lowden 
> > wrote:
> >
> > > SQL Server has none of those restrictions, and probably keeps pace with
> > > SQLite even on its home turf.  But the administration of SQL Server is
> > > nontrivial.  For that reason alone, I would never use it in situations
> > > where SQLite would do.
> >
> > That's the fella.  Major advantage of SQLite: zero admin.  Not even a
> > background task.
> >
> > Second advantage: you know exactly where you data is.  Better still, it's
> > simple: one database == one file, and the file has the same name as the
> > database.  I remember trying to reconstruct a MySQL database from a dead
> > server.  One folder with a confusing mass of files in.  Your database is
> > part of some of those files, but the files may be huge even if the one
> > database you care about is tiny.  That was not a fun time.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> 

[sqlite] json_group_array

[Michael Falconer]

I just love this list and the contained discussions. Doffing my cap to
Keith and his succinct and very useful summary of the subtle differences
regarding aggregate type functions. Especially like the nested stuff, which
I must admit had not really occurred to me. Nice stuff Keith and thanks for
sharing that info. It also clarifies the underlying fact that these
functions are often slightly misunderstood even by experienced SQL hacks.


On 6 February 2016 at 07:24, Keith Medcalf  wrote:

>
> count(*) counts the rows of the result set selected
> count(column) counts the NOT NULL values in the column of the result set
> selected
> count(DISTINCT column) counts the number of distinct values (excluding
> NULLs) in the column of the result set selected
>
> count(column IS NULL) is equivalent to count(*) (the expression always
> returns true (1) or false(0)
> count(column IS NOT NULL) is equivalent to count(*)  for every row so
> therefore all rows are counted)
>
> sum(column IS NULL) returns the count of the number of rows in which the
> column is null
> sum(column IS NOT NULL) returns the count of the number of rows in which
> the column is not null
>
> These can be combined, so for example count(DISTINCT column) + sum(column
> IS NULL) returns the number of unique values in the column where NULLs are
> distinct.
> and count(DISTINCT column) + (sum(column IS NULL) > 0) returns the number
> of unique values where NULLs are not distinct.
>
> and so on and so forth
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of R Smith
> > Sent: Friday, 5 February, 2016 00:55
> > To: sqlite-users at mailinglists.sqlite.org
> > Subject: Re: [sqlite] json_group_array
> >
> >
> >
> > On 2016/02/05 6:34 AM, TJ O'Donnell wrote:
> > > I can't argue for the correctness of including nulls in aggregate
> > functions
> > > or not.
> > > It truly is an arbitrary decision meant for standards-makers.  Yet,
> most
> > > aggregate
> > > function do not include nulls.  Interestingly, some SQL's do include
> > them
> > > in count()
> > > but sqlite does not.  In my example table, select count(a) from x
> > returns 3,
> > > but select count(*) from x returns 4 even though a is the only column.
> > > I haven't tried every sqlite agg, but I think they all exclude null,
> > except
> > > json_group_array
> > > and json_group_object.
> >
> > I think you are mistaken in your understanding. While JSON has some
> > rules and some conventions, when used inside an SQL engine, the rules of
> > SQL needs to be adhered to before any "convention" of JSON.
> > Not showing Null values in JSON is a convention, not a rule. (Else, why
> > else would json even need the 'NULL' construct?)
> >
> > Further to this, in SQL, how would you know how many elements are
> > present in a json array and which of them are null if there is no way to
> > output them? Agreed, sometimes it isn't needed to know, but then you are
> > welcome to exclude them via the WHERE clause.
> >
> > You are also mistaken about the SQL convention and SQLite-specific
> > operations re. Nulls - If I have a table t with one single column "a"
> > with 3 rows (2 text values and one null value) then doing SELECT
> > COUNT(a) FROM t; will show 2 and SELECT COUNT(*) FROM t; will show 3, as
> > it should - yes, even though a is the only column. The * doesn't mean
> > "a", even if the only column is "a". It means "all the DB rows" and so
> > include nulls. (The standard might be hazy on this, I didn't check, but
> > this is definitely how SQLite works, and not as you suggested).
> >
> > This is also very important. Sometimes we'd want to know how many rows
> > are in the DB, not JUST which non-null rows are in the only column in
> > the DB - that is why we can decide to use either COUNT(a) or COUNT(*),
> > or more deliberate with an explicit GROUP BY clause. I would never want
> > this convention to be altered.
> >
> >
> > > As a side issue here, but important still I think, what should
> > json(null)
> > > mean?
> > > In my table x, select json(a) from x returns valid json integers for
> > > non-null rows,
> > > but return a sql null (a blank from command-llne sqlite) not a json
> null
> > > (which would
> > > be the string null) when a is null.  In other words, json(null) returns
> > > null,
> > > not 'null'.
> >
> > Here I'm with you - the null should output 'null'
> > (Devs: I'm guessing this might be an oversight in the CLI rather than
> > the SQL engine?)
> >
> > > I know the json stuff is new in sqlite, but I think it's worth getting
> > > these issues worked
> > > out, considering how useful json has become.
> >
> > Right you are, but first the issues need discovery - which is what is
> > happening in this very thread. :)
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqli

[sqlite] In the case of ZIPVFS

[Michael Stephenson]

Having just glanced at the documentation for ZIPVFS (didn't even know it
existed)...

1) Perhaps using a real-time compression algorithm, something like lz4.
It's not clear to me which algorithm ZIPVFS uses by default.

2) Perhaps increasing the cache settings to cache more pages in memory.

3) Perhaps increasing the page size.

4) Perhaps ensure that WAL mode is used.

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of ???
Sent: Wednesday, December 23, 2015 4:05 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: [sqlite] In the case of ZIPVFS

HI,all
SQLite retrieves the compressed records is slower than the uncompressed
records about 30%.
How can improve the problem?  and anyone any suggustion?

best regards
wqg
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] maybe bug in regexp and replace with newlines?

[michael]

>/> The statement: />/> select distinct text from v_term_item where
replace(lower(text),0x0A,'') REGEXP('.*/some_text/.*'); />/> doesn't work like 
that: /
> Try x'0A' instead (that's a BLOB literal, and should be converted to a 
> string). 0x0A is an integer, I suspect it gets converted to the string '10'
> -- 
>Igor Tandetnik

thanks but sadly it also doesn't work

[sqlite] Missing documentation about BLOB encoding conversions

[Michael Kaufmann]

> <http://www.sqlite.org/lang_expr.html#castexpr> says:
>> To cast a BLOB value to TEXT, the sequence of bytes that make up the
>> BLOB is interpreted as text encoded using the database encoding.
>
> (The database encoding must be set when the DB file is created.)

Thank you! That's the information that I was looking for.

Regards,
Michael

[sqlite] Missing documentation about BLOB encoding conversions

[Michael Kaufmann]

The sequence of calls is:

1. sqlite3_step()
2. sqlite3_column_text16()
3. sqlite3_column_bytes()

Please see the attached example program.

Regards,
Michael


> What is the exact sequence of calls?
>
> If you call sqlite3_column_text() on a blob value, the new type will  
> be text and a subsequent call to sqlite_column_text16() must by  
> definition perform transcoding.
>
> -Urspr?ngliche Nachricht-
> Von: sqlite-users-bounces at mailinglists.sqlite.org  
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von  
> Michael Kaufmann
> Gesendet: Donnerstag, 17. Dezember 2015 10:41
> An: sqlite-users at mailinglists.sqlite.org
> Betreff: [sqlite] Missing documentation about BLOB encoding conversions
>
> Hi,
>
> I expected that BLOB data is returned unchanged by  
> sqlite3_column_blob(), sqlite3_column_text() and  
> sqlite3_column_text16(). The documentation at  
> https://www.sqlite.org/c3ref/column_blob.html says: "Type  
> conversions and pointer invalidations might occur in the following  
> cases: ... The initial content is a BLOB and sqlite3_column_text() or
> sqlite3_column_text16() is called. A zero-terminator might need to  
> be added to the string."
>
> I have found out that SQLite does more than just adding a zero  
> terminator. It seems that BLOBs are converted from UTF-8 to UTF-16  
> when sqlite3_column_text16() is called.
>
> This is quite unexpected, and it would be nice if the rules for BLOB  
> encodings and BLOB encoding conversions were pointed out in the  
> documentation.
>
> Regards,
> Michael
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the  
> use of the intended recipient(s) only and may contain information  
> that is confidential, privileged or legally protected. Any  
> unauthorized use or dissemination of this communication is strictly  
> prohibited. If you have received this communication in error, please  
> immediately notify the sender by return e-mail message and delete  
> all copies of the original communication. Thank you for your  
> cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Missing documentation about BLOB encoding conversions

[Michael Kaufmann]

Hi,

I expected that BLOB data is returned unchanged by  
sqlite3_column_blob(), sqlite3_column_text() and  
sqlite3_column_text16(). The documentation at  
https://www.sqlite.org/c3ref/column_blob.html says: "Type conversions  
and pointer invalidations might occur in the following cases: ... The  
initial content is a BLOB and sqlite3_column_text() or  
sqlite3_column_text16() is called. A zero-terminator might need to be  
added to the string."

I have found out that SQLite does more than just adding a zero  
terminator. It seems that BLOBs are converted from UTF-8 to UTF-16  
when sqlite3_column_text16() is called.

This is quite unexpected, and it would be nice if the rules for BLOB  
encodings and BLOB encoding conversions were pointed out in the  
documentation.

Regards,
Michael

[sqlite] maybe bug in regexp and replace with newlines?

[michael]

On 12/2/2015 7:54 AM, michael wrote:
>>/sqlite3 -header flexsql.db "select distinct replace(lower(text),0x0A,'') 
>>/>>/from v_term_item where lower(text) REGEXP('.*some_text.*') limit 1;" /
>You are removing newlines in the wrong spot. Do it on the left-hand side 
>of REGEXP, not in SELECT clause.

>The reason it doesn't work is that, with most regular expression 
>engines, unless certain flags are used, "." (period) doesn't match 
>newline characters.
>-- 
>Igor Tandetnik

Thanks for your answers. But I only pasted wrong line here.
The statement:
select distinct text from v_term_item where replace(lower(text),0x0A,'') 
REGEXP('.*/some_text/.*');
doesn't work like that:
select distinct text from v_term_item where replace(lower(text),'
,'') REGEXP('.*/some_text/.*');

with debian and /usr/lib/sqlite3/pcre.so there is one more problem. 
REGEXP(/'//some_text'/) works like REGEXP(/'//some_text/.*')

thanks
Michael

[sqlite] maybe bug in regexp and replace with newlines?

[michael]

>> sqlite3 -header flexsql.db "select distinct text from v_term_item where
>> name='text' and lower(text) REGEXP('.*some_text.*');"
>> which doesn't works

>REGEXP is not compiled into SQLite by default.

>Simon.

Yes but in gentoo it is compiled. Only a problem with new lines

[sqlite] maybe bug in regexp and replace with newlines?

[michael]

I tried
sqlite3 -header flexsql.db "select distinct text from v_term_item where
name='text' and lower(text) REGEXP('.*some_text.*');"
which doesn't works

sqlite3 -header flexsql.db "select distinct text from v_term_item where
name='text' and lower(text) like '%some_text%';"
works

so I tried a work-around.

sqlite3 -header flexsql.db "select distinct replace(lower(text),0x0A,'')
from v_term_item where lower(text) REGEXP('.*some_text.*') limit 1;"

also no luck

with
sqlite3 -header flexsql.db "select distinct hex(replace(text,0x0A,''))
from v_term_item where lower(text) like '%some_text%' limit 1;"
I saw the new lines (0A) still exist

I used:
sqlite 3.8.10.2 compiled on gentoo linux

[sqlite] Casting ctype functions' arguments

[Michael McConville]

Richard Hipp wrote:
> On 11/13/15, Michael McConville  wrote:
> > Hi, everyone.
> >
> > I've been auditing the OpenBSD codebase for calls to ctype functions
> > with potentially signed chars. This is undefined on some platforms.
> > I found a number of instances in Sqlite, so I ran my Coccinelle
> > script on the repo.
> 
> Thank you.  You've already told us this once before.

I'm not sure if the first send ever got approved by a list moderator. It
never made it through to marc.info. I should have checked the commit
history before resending, though.

> All of your findings are either in test programs, programs used as
> part of the build process, or obsolete code that we keep around for
> historical reference but which is never in fact used.  None of your
> findings are in the SQLite core.  There are no security implications
> here. Nevertheless, I went through and fixed all of these cases (even
> the ones in code that is *never compiled*) a couple of weeks ago, and
> checked the changes into trunk:
> 
> https://www.sqlite.org/src/info/34eb6911afee09e7

I wasn't trying to point fingers or start an argument, and I never
implied a significant security risk. Just pointing out undefined
behavior where I found it. When the change is this simple, it's easier
to just fix 'em all rather than reflect on the significance of each.

> I suppose it is too much to ask of Coccinelle to recognize that the
> following suggestion is pointless:
> 
> >  static int safe_isspace(char c){
> > -  return (c&0x80)==0 ? isspace(c) : 0;
> > +  return (c&0x80)==0 ? isspace((unsigned char)c) : 0;
> >  }

Yup, too much to ask. Coccinelle does "semantic patching" - you script
the transformation you want done. It can parse C, so it's
syntax-agnostic, but it's restricted to the current transformation
you're doing. A completely different tool from general-purpose static
analyzers.

Here's the simple script I wrote:


@@
char x;
@@
(
isupper
|
isalnum
|
isalpha
|
isascii
|
isblank
|
iscntrl
|
isdigit
|
isgraph
|
islower
|
isprint
|
ispunct
|
isspace
|
isxdigit
|
toupper
|
tolower
)
- (x)
+ ((unsigned char)x)



@@
signed char x;
@@
(
isupper
|
isalnum
|
isalpha
|
isascii
|
isblank
|
iscntrl
|
isdigit
|
isgraph
|
islower
|
isprint
|
ispunct
|
isspace
|
isxdigit
|
toupper
|
tolower
)
- (x)
+ ((unsigned char)x)

[sqlite] Casting ctype functions' arguments

[Michael McConville]

Hi, everyone.

I've been auditing the OpenBSD codebase for calls to ctype functions
with potentially signed chars. This is undefined on some platforms. I
found a number of instances in Sqlite, so I ran my Coccinelle script on
the repo.

The below diff was generated automatically, so formatting may be changed
and mistakes are possible (though unlikely).

Here's the relevant CERT entry:


https://www.securecoding.cert.org/confluence/display/c/STR37-C.+Arguments+to+character-handling+functions+must+be+representable+as+an+unsigned+char

Let me know what you think.

Thanks,
Michael


Index: autoconf/tea/win/nmakehlp.c
==
--- autoconf/tea/win/nmakehlp.c
+++ autoconf/tea/win/nmakehlp.c
@@ -603,15 +603,15 @@
sp = fopen(substitutions, "rt");
if (sp != NULL) {
while (fgets(szBuffer, cbBuffer, sp) != NULL) {
char *ks, *ke, *vs, *ve;
ks = szBuffer;
-   while (ks && *ks && isspace(*ks)) ++ks;
+   while (ks && *ks && isspace((unsigned char)*ks)) ++ks;
ke = ks;
-   while (ke && *ke && !isspace(*ke)) ++ke;
+   while (ke && *ke && !isspace((unsigned char)*ke)) ++ke;
vs = ke;
-   while (vs && *vs && isspace(*vs)) ++vs;
+   while (vs && *vs && isspace((unsigned char)*vs)) ++vs;
ve = vs;
while (ve && *ve && !(*ve == '\r' || *ve == '\n')) ++ve;
*ke = 0, *ve = 0;
list_insert(&substPtr, ks, vs);
}

Index: ext/fts1/fts1.c
==
--- ext/fts1/fts1.c
+++ ext/fts1/fts1.c
@@ -203,17 +203,17 @@
 ** tokenizer-generated tokens rather than doing its own local
 ** tokenization.
 */
 /* TODO(shess) Is __isascii() a portable version of (c&0x80)==0? */
 static int safe_isspace(char c){
-  return (c&0x80)==0 ? isspace(c) : 0;
+  return (c&0x80)==0 ? isspace((unsigned char)c) : 0;
 }
 static int safe_tolower(char c){
-  return (c&0x80)==0 ? tolower(c) : c;
+  return (c&0x80)==0 ? tolower((unsigned char)c) : c;
 }
 static int safe_isalnum(char c){
-  return (c&0x80)==0 ? isalnum(c) : 0;
+  return (c&0x80)==0 ? isalnum((unsigned char)c) : 0;
 }

 typedef enum DocListType {
   DL_DOCIDS,  /* docids only */
   DL_POSITIONS,   /* docids + positions */

Index: ext/fts1/simple_tokenizer.c
==
--- ext/fts1/simple_tokenizer.c
+++ ext/fts1/simple_tokenizer.c
@@ -136,11 +136,11 @@
   for(ii=0; iipCurrent[ii];
-c->zToken[ii] = (unsigned char)ch<0x80 ? tolower(ch) : ch;
+c->zToken[ii] = (unsigned char)ch<0x80 ? tolower((unsigned char)ch) : 
ch;
   }
   c->zToken[n] = '\0';
   *ppToken = c->zToken;
   *pnBytes = n;
   *piStartOffset = (int) (c->pCurrent-c->pInput);

Index: ext/misc/amatch.c
==
--- ext/misc/amatch.c
+++ ext/misc/amatch.c
@@ -814,14 +814,14 @@
   int nKey = (int)strlen(zKey);
   int nStr = (int)strlen(zStr);
   int i;
   if( nStr0 && isspace(zOut[i-1]) ){ i--; }

Index: mptest/mptest.c
==
--- mptest/mptest.c
+++ mptest/mptest.c
@@ -185,14 +185,14 @@
 }
 c2 = *(zGlob++);
   }
   if( c2==0 || (seen ^ invert)==0 ) return 0;
 }else if( c=='#' ){
-  if( (z[0]=='-' || z[0]=='+') && isdigit(z[1]) ) z++;
-  if( !isdigit(z[0]) ) return 0;
+  if( (z[0]=='-' || z[0]=='+') && isdigit((unsigned char)z[1]) ) z++;
+  if( !isdigit((unsigned char)z[0]) ) return 0;
   z++;
-  while( isdigit(z[0]) ){ z++; }
+  while( isdigit((unsigned char)z[0]) ){ z++; }
 }else{
   if( c!=(*(z++)) ) return 0;
 }
   }
   return *z==0;
@@ -287,11 +287,11 @@
 /*
 ** Return the length of a string omitting trailing whitespace
 */
 static int clipLength(const char *z){
   int n = (int)strlen(z);
-  while( n>0 && isspace(z[n-1]) ){ n--; }
+  while( n>0 && isspace((unsigned char)z[n - 1]) ){ n--; }
   return n;
 }

 /*
 ** Auxiliary SQL function to return the name of the VFS
@@ -442,11 +442,11 @@
   if( p->n ) stringAppend(p, " ", 1);
   if( z==0 ){
 stringAppend(p, "nil", 3);
 return;
   }
-  for(i=0; z[i] && !isspace(z[i]); i++){}
+  for(i=0; z[i] && !isspace((unsigned char)z[i]); i++){}
   if( i>0 && z[i]==0 ){
 stringAppend(p, z, i);
 return;
   }
   stringAppend(p, "'", 1);
@@ -697,11 +697,11 @@
 /*
 ** Return the

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

[Michael Falconer]

Outstanding!

it is kudos which is so richly deserved, and IMHO, an example of REAL
integrity earned. Over the years I have watched as SQLite developed,
learned much from this list, and was impressed by Dr Hipps hands-on
leadership in directing development in what he and the team deemed an
appropriate direction. Many contentious issues have been raised over the
years but they have been consistently handled with a core ethic in mind.
Make it better, more efficient, scalable and usable on multiple platforms.

It is not often that you truly see such a development process remain true
to it's founding principles, we humans have a powerful capability to
corrupt and in IT we tend to excel at that. In SQLite we have a shining
example of what can be achieved when a development process is well handled,
and it is sadly all to rare. So well done to all involved in developing a
truly great system and to all on this list who have made so many good
recommendations and offered their seasoned technical opinions so openly and
in collaborative spirit. It is a genuine open source triumph of which you
should all be proud. Thanks.


On 12 November 2015 at 04:51, Jay Kreibich  wrote:

>
> On Nov 10, 2015, at 6:38 PM, Richard Hipp  wrote:
>
> > On 11/10/15, Jay Kreibich  wrote:
> >>
> >> I want to be a Certified SQLite Professional.
> >>
> >
> > The inventors and developers of the SQLite database engine to all to
> > whom these presents may come, Greetings:  Whereas Mr. Jay Kreibich has
> > exhibited detailed knowledge of the use and inner workings of SQLite
> > by publishing an outstanding textbook on that subject, we do hereby
> > confirm unto him the title of
> >
> >Certified SQLite Professional
> >
> > with all the rights, honors, and duties thereunto appertaining.  In
> > witness whereof, the electronic signature of the BDLF of SQLite is
> > hereto subscribed.  Given in Charlotte on this the tenth day of
> > November and in the year of our Lord two thousand and fifteen.
>
>
> w00t!  Time to update my resume!
>
>  -j
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it, but
> showing it to the wrong people has the tendency to make them feel
> uncomfortable." -- Angela Johnson
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.

[sqlite] factbook.sql World Factbook Country Profiles in SQL (Incl. factbook.db - Single-File SQLite Distro)

[Michael Falconer]

Gerald,

thanks for this, it looks most interesting on both levels. i.e. The data
release and your project work. Thank you for the information and your
project.

On 2 November 2015 at 01:49, Gerald Bauer  wrote:

> Hello,
>
>   I've started a new project, that is, /factbook.sql [1] that offers
> an SQL schema for the World Factbook and also includes a pre-built
> single-file SQLite database, that is, factbook.db [2] for download.
>
>   What's the World Factbook?
>
>   The World Factbook [3] published by the Central Intelligence Agency (CIA)
>   offers free 260+ country profiles in the public domain
>   (that is, no copyright(s), no rights reserved).
>
>   Anyways, what's it good for? For example, to find the ten largest
> countries by area, try:
>
> SELECT name, area FROM facts ORDER BY area DESC LIMIT 10;
>
>   Resulting in:
>
>   Russia | 17_098_242
>   Canada |  9_984_670
>   United States  |  9_826_675
>   China  |  9_596_960
>   Brazil |  8_515_770
>   Australia  |  7_741_220
>   European Union |  4_324_782
>   India  |  3_287_263
>   Argentina  |  2_780_400
>   Kazakhstan |  2_724_900
>
>Or to find the ten largest countries by population, try:
>
>   SELECT name, population FROM facts ORDER BY population DESC LIMIT 10;
>
>Resulting in:
>
>World  | 7_256_490_011
>China  | 1_367_485_388
>India  | 1_251_695_584
>European Union |   513_949_445
>United States  |   321_368_864
>Indonesia  |   255_993_674
>Brazil |   204_259_812
>Pakistan   |   199_085_847
>Nigeria|   181_562_056
>Bangladesh |   168_957_745
>
>And so on. Note: Using the factbook command line tool and scripts
> you can build yourself an up-to-date copy.
>
>Questions? Comments? Welcome. Enjoy. Cheers.
>
> [1] https://github.com/factbook/factbook.sql
> [2] https://github.com/factbook/factbook.sql/releases
> [3] https://www.cia.gov/library/publications/the-world-factbook
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.

[sqlite] Encrypted or Corrupt File

[Michael Horn]

I am new to this and an amateur to say the least. I'm trying to recover
online some chat files. I'm using SQLite manager to read files and the
files I was just using yesterday are not opening. file error: either the
file is encrypted or corrupt component returned failure code 0x8052000b.
Can anyone give me some pointers? Thanks.

[sqlite] Detect if db is already opened by another process?

[Michael Schlenker]

Am 23.09.2015 um 18:09 schrieb Richard Hipp:
> On 9/23/15, Michael Schlenker  wrote:
>> Hi,
>>
>> i just wondered if there is an API to detect if a sqlite database file
>> is already opened by another process.
> 
> Maybe try to change in or out of WAL mode?  That only works if there
> is a single connection to the database file.
> 
Good idea. Yes, seems to work nicely for my usecase.

Thank you,
   Michael

-- 
Michael Schlenker
Senior Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Stra?e 1-3   Fax:+49 (421) 20153-41
28359 Bremen
E-Mail: michael.schlenker at contact-software.com
http://www.contact-software.com/

Registered office: Bremen, Germany
Managing directors: Karl Heinz Zachries, Ralf Holtgrefe
Court of register: Amtsgericht Bremen HRB 1321

[sqlite] Detect if db is already opened by another process?

[Michael Schlenker]

Hi,

i just wondered if there is an API to detect if a sqlite database file
is already opened by another process.

I can make the assumptions that:

1. WAL mode is in use
2. Linux and Windows only
3. No network filesystems
4. I only care if the access is done by another SQLite library,
   not simple open() calls.

I didn't see any explicit API to check for this, but assumed there might
be some way to find out via the .shm files?

Usecase is a server process that keeps an SQLite DB open while it is
running and a commandline tool that manipulates the same DB file for
maintenance tasks. The maintenance tool should not change the DB if the
server is running.

I could of course do explicit locking via other means to mediate access
(actually i do that now, but it is not really elegant), but if there is
an SQLite API way to do it, it would be nicer.

Any good hints?

Michael

-- 
Michael Schlenker
Senior Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Stra?e 1-3   Fax:+49 (421) 20153-41
28359 Bremen
E-Mail: michael.schlenker at contact-software.com
http://www.contact-software.com/

Registered office: Bremen, Germany
Managing directors: Karl Heinz Zachries, Ralf Holtgrefe
Court of register: Amtsgericht Bremen HRB 1321

-- 
Michael Schlenker
Senior Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Stra?e 1-3   Fax:+49 (421) 20153-41
28359 Bremen
E-Mail: michael.schlenker at contact-software.com
http://www.contact-software.com/

Registered office: Bremen, Germany
Managing directors: Karl Heinz Zachries, Ralf Holtgrefe
Court of register: Amtsgericht Bremen HRB 1321

[sqlite] pragma temp_store = 2 ignored in WinRT App

[Michael Geier]

Hi,

I would like to report that "PRAGMA temp_store = 2;" seems to be ignored 
in WinRT applications (Windows 8.1 App).

In a .NET 4.5 application this works fine.

We wanted to add this pragma because of the issue reported here:
https://github.com/praeclarum/sqlite-net/issues/78

Used SQLite version: sqlite-winrt81-3081002.vsix

Thanks,
Michael Geier
Graz, Austria

[sqlite] pragma temp_store_directory problem with UTF-8 characters

[Michael Geier]

Hi,

I would like to file a bug report:

Using "PRAGMA temp_store_directory = 'directory-name';" with paths which 
contain non-ASCII characters doesn't work because SQLite reports the 
directory as not writable (SQLite.Net.SQLiteException: "not a writable 
directory").

We are setting the temp_store_directory to Windows' temp path which is 
by default the "C:\Users\(Username)\AppData\Temp" folder. So if the user 
name of the logged-in user contains UTF-8 characters (e.g. "S?mez") 
executing the command "PRAGMA temp_store_directory = 
'C:\Users\S?mez\AppData\Temp'; fails.


When we took a look at the SQLite source code, this is where we suspect 
the issue:
* pragma.c: case PragTyp_TEMP_STORE_DIRECTORY: / call to sqlite3OsAccess()
* sqlite3OsAccess() calls xAccess()
* xAccess() (used in rc = pVfs->xAccess(pVfs, zName, flags, &ret);) maps 
to winAccess on Windows which checks the file attributes for the 
readonly flag.
* winAccess() calls winConvertFromUtf8Filename(), which seems to have an 
issue with UTF-8 characters in paths.

Used SQLite version: sqlite-winrt81-3081002.vsix
Applies to both WinRT (Windows 8.1 App) and .NET 4.5

Thanks,
Michael Geier
Graz, Austria

[sqlite] Destroy all evidence of a database

[Michael Stephenson]

Simon, if the data in the database is sensitive, could you encrypt the database 
(ala something like https://www.zetetic.net/sqlcipher/)?  

That way if the file is left around for some reason, it's much less of a 
concern.  Your app could generate a new (random) key each time it creates a new 
database.  When the program exits, the key is not saved anywhere so getting at 
the data means breaking the encryption if the file is left hanging around for 
some reason.

If sqlcipher itself is not that palatable or won't work on your mystery OS, 
it's pretty easy to write your own pager-based encryption based on the hooks 
that are provided when SQLITE_HAS_CODEC is defined. 

I did something like this.  I got started by diffing the sqlite source with the 
sqlcipher source which showed me what they had done.  I wasn't pleased that 
sqlcipher linked in the full OpenSSL library due mostly to the size (around 
500K packed) and complexity, neither of which I needed. 

It was pretty easy to just create my own encryption based on the same general 
approach as sqlcipher and used OpenSSL's AES implementation directly for the 
encryption and decryption.  Using the optimized ASM version of the OpenSSL AES 
code, all of this came in at around 10K packed (including some small libs for 
key derivation, HMAC, etc.) and the performance is almost identical to plain 
vanilla sqlite.  

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of John McKown
Sent: Wednesday, April 22, 2015 11:12 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Destroy all evidence of a database

On Wed, Apr 22, 2015 at 9:34 AM, Simon Slavin  wrote:

>
> On 22 Apr 2015, at 3:23pm, John McKown 
> wrote:
>
> > If it is
> > a POSIX compliant, perhaps what you could do is create a "temporary"
> > (mktemp) file of "appropriate" size.
>
> I had never considered that idea.  Thank you very much.  Unfortunately 
> it won't work in this situation because the people in control of the 
> system would either say "No virtual file systems" or leap at the idea 
> and insist that everyone uses virtual encrypted file systems for all 
> data files at all times.  I'm not sure which would be worse.
>
>
?Oh, my condolences. I've had that type of management too. If a teaspoon of 
medicine is good, then a tablespoon is better, but let's just take the entire 
bottle and be done with it.?


--
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

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


---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

[sqlite] Did A Recent Windows & Update Affect SQLite???

[Michael Stephenson]

Some suggestions:

1) Check your temp folder and remove chaff.

2) Download CCleaner and check your registry integrity.

3) Review updates that have been applied and try removing them one at a time
or in a targeted fashion.  You should be able to see what updates were
applied around the time you think Lightroom went bad.  (At the start menu
type Update in the search box and select "View installed updates").

4) Have a look at Lightroom using Process Monitor and perhaps Performance
Monitor.  Maybe the process has a high number of threads, or files in use,
or handles or something like that.

4) Install Windows Performance Toolkit and try xperf or Windows Performance
Analyzer to profile Lightroom and try to see what is taking up time.

5) Turn off Avast shields for a while and see if that makes a difference.

6) Stop/Disable the Windows Search service temporarily and see if that makes
a difference.

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Mark
Romero
Sent: Wednesday, April 1, 2015 3:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Did A Recent Windows & Update Affect SQLite???

Hello everyone and thanks for your replies (I am new to using a mailing list
so I hope that everyone gets this response).

- Viruses and Antivirus software:

I use Avast in real time and I have scanned my system several times and it
came up clean.

I installed malwarebytes shortly after Lightroom began having problems, and
the scans came up clean, too. (I have since uninstalled malware bytes.)

Have not tried any of the native Microsoft virus tools.

- Software Updates:

I am using the latest version of Lightroom (5.7.1)

I am using Win 7 Pro 64-bit Service Pack 1 and I am pretty sure it is up to
date since I do automatic updates with it.

- CPU Usage:

In resource monitor and task manager, it is ONLY lightroom that is using up
all the CPU and is "spiking" the cpu (it repeatedly and rhythmically goes
from 50% use to 100% use over and over again. It spikes approximately every
5 seconds or so.

No other program or process is using an abnormally large percentage of CPU
(or memory)

Please also note that when Lightroom IS spiking the CPU, I am still able to
open other programs (like Photoshop, which is something of a resource hog),
and work in those programs, WHILE Lightroom is spiking the CPU. To rephrase
this, when lightroom stops responding I can go ahead and open photoshop and
work on a different photo, with only minimal lag compared to when Lightroom
is not spiking the CPU.

- Hard Drive (and other hardware):

I have used the Dell diagnostic tests and the Western Digital tests and they
have all been fine for the hard drive as well as ALL other hardware.

- Disk Performance / File corruption

Yes, I have seen NTFS errors in the event manager. But they only seem to
happen when Lightroom has stopped responding and I chose to do a
ctrl-alt-del instead of doing a ctrl-q and waiting for the program to
eventually close. (I know that ctrl-alt-delete is bad... will try to avoid
again!)

I have done another chkdsk and it came out fine, and I did a chkntfs and it
came out saying C: is not dirty.

- Tech Support from Adobe:

I have had THREE separate Adobe technicians login remotely to my computer
and make changes to different settings, and unfortunately it has not made a
difference.

Because the program stops responding sporadically, they will make a few
changes and then ask me to try it. If the problem doesn't happen within five
minutes, they end the chat and say, "It's probably ok, let  us know if you
have problems in the future."

Then five minutes after they end their support chat and log out of my
system, it will stop responding again.

The changes they have made include increasing the cache, giving adobe
programs "resource priority" . they also tried to update my drivers but I
don't know if they were able to update or not.

- Reinstallation:

I have deleted and reinstalled Lightroom 5.7.1 once already after the
problem started a few months back, and unfortunately it did not make a
difference.

- Lightroom versus Adobe Camera Raw (ACR)

Someone pointed out that Lightroom is different than ACR, and that is true.
However, I spend most of my time in the Development module of Lightroom,
which is (as far as I understand) extremely similar to ACR.

Just so you know, I can use ACR ALL DAY LONG without any problems.

My understanding is that Lightroom saves info to its SQlite database, while
ACR saves info to a separate file.

One Other Fun Fact:

A lot of people are dismissive of my problem because I have an older
computer (core 2 duo E8400 with 8 gigs DDR3 RAM).

"Just buy an i7" they like to chant.

However, Lightroom worked fine for over 8 months until just about two months
ago, when the problems started, and...

My sons computer has the same problem, which developed at the same time.
Again, worked fine for 8 months, 

[sqlite] System.Data.SQLite NuGet

[Michael Quinlan]

The current NuGet package seems to include SQLite version 3.8.6. Are there
plans to update to version 3.8.7?

-- 
Michael Quinlan
mquin...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Serializing an object's vector or array using sqlite3 in c++

[Thane Michael]

Many of the answers I came across online implied that it wouldn't be as
straightforward as serializing ints and strings.

On Fri, Nov 21, 2014 at 1:04 AM, Igor Tandetnik  wrote:

> On 11/21/2014 12:52 AM, Thane Michael wrote:
>
>> I've been searching for a way to serialize an object's vector using
>> sqlite3
>>
>
> There's nothing in sqlite3 that would help (or hinder) this task. What
> made you believe otherwise?
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Serializiing an object's vector or array in c++ using sqlite3

[Thane Michael]

Hi,
I previously sent out out an email that was not clear, this email is meant
to fix that.

---previous message
I've been searching for a way to serialize an object's vector using sqlite3
but are yet find a working solution. How do I go about making it happen, an
example would be of great help.

A second question I have is whether it is possible to serialize an object
which doesn't have attributes. An answer has been provided, which is that
it's not possible but I would like to be sure.

Here's an example; TRAY.h


 #ifndef TRAY_H
 #define TRAY_H
 #include 
 #include "EGG.h";
 #include "FLOUR.h";
 class TRAY
 {
double price;
std::vector dozen;
 public:
TRAY(int number); ~TRAY();
double getTotalPrice();
std::vector getEggs();
 };
 #endif

In the above example, how, if it is possible, do I serialize a TRAY object?

Regards,

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

[sqlite] Serializing an object's vector or array using sqlite3 in c++

[Thane Michael]

Hi,
I've been searching for a way to serialize an object's vector using sqlite3
but are yet find a working solution. How do I go about making it happen, an
example would be of great help.

A second question I have is whether it is possible to serialize an object
which doesn't have attributes. An answer has been provided, which is that
it's not possible but I would like to be sure.

Here's an example;


   1. #ifndef BREAD_H
   2. #define BREAD_H
   3. #include "EGGS.h";
   4. #include "FLOUR.h";
   5. class BREAD
   6. {
   7. public:
   8. BREAD();~BREAD();
   9. //lame example
   10. std::vector selectIngredient(std::vectorgrocery);
   11.
   12. };
   13. #endif

In the above example, how, if it is possible, do I serialize a BREAD object?

Regards,

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

[sqlite] Does using e.g. LTRIM function remove collation?

[Michael Suodenjoki]

Hi,

I'm a bit disoriented by the following result - here using SQLite command shell:

sqlite> .version
SQLite 3.8.6 2014-08-15 11:46:33 9491ba7d738528f168657adb43a198238abde19e
sqlite> CREATE TABLE test( col TEXT COLLATE NOCASE );
sqlite> INSERT INTO test VALUES ('b'), ('A'), ('B'), ('a');
sqlite> SELECT * FROM test;
b
A
B
a
sqlite> SELECT * FROM test WHERE col<'b';
A
a
sqlite> SELECT * FROM test WHERE LTRIM(col)<'b';
A
B
A

To me this result of the last SELECT statement is a bit unexpected. There are 
no space in the values, but using LTRIM function somehow removes collation (and 
uses the default BINARY collation). I would have expected the same result in 
the last two SELECT statements.

Is this an error or something I haven't understood?

/Mike

PS. Is there a way to see the resulting/deduced collation of a SELECT statement?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search query alternatives.

[Michael Falconer]

I am glad I posted the question.

Yes James, there is little I can disagree with in your excellent summary.
Even the critique of my perhaps poorly framed question is indeed valid. I
take your point regarding spec vs implementation, and in my experience
across different rdbms's I have frequently seen evidence which supports
your assertions regarding db/os system influence on implementation
approaches.

Application code too has it's part to play. How do we plan to access the
data? There's a strong case too, IMHO, to have flexibility in the design,
perhaps leading to differing approaches with the variable types of data we
may be storing.

The original design decisions were made by someone who was, and still is,
essentially a hobby programmer. I don't think he'd ever heard of of Chris
Date or Mr Codd at that time and like all novice application programmers,
he had little understanding about the effect db design could have on his
application and it's source code. His design choices were initially made on
the basis of what he could easily understand and what was (as it appeared
to him then) easy to program with. It is some time ago, and we who have
lived with rdbms's for years get to say, 'that is a horrible design!'. I
think R. Smith hit on a point above, regarding code overhead. Yep, plenty
of that. And so the lesson is learned the hard way for someone who until
recently had viewed normalisation as a way to make coding harder and to
slow down the execution of queries.

So with that perspective you can perhaps come some way to understanding the
why component. On analysis, I agree with suggested design changes at the
higher level. i.e. Dynamic tables are at the root of issues going forward.
They are requiring tedious application code gymnastics, more difficult
query analysis and poorer query performance.  I suppose the upside is that
it will be a challenge to see what improvements can be made, and that is
always fun and games. I kind of like Mr Smith's other suggestion about an
SQLITE testbed or prototype. So easy to work with SQLITE, and probably
perfect for this task. Thanks all for your contributions.

Just FYI James, the application is coded in php and connects to a mysql
database. It can be installed either as a browser based, stand alone or
client server app. It's common implementation is on low end shared hosts,
even free hosting services. So this limits us somewhat to what is commonly
allowed on such platforms. Things like Stored Procedures are unfortunately
outside our scope when it come to design considerations.

Thanks all.


On 18 October 2014 02:24, James K. Lowden  wrote:

> On Thu, 16 Oct 2014 09:05:51 +1100
> Michael Falconer  wrote:
>
> > we just wonder if there is a better way to perform this search in
> > SQL. Is there a general technique which is superior either in speed,
> > efficiency or load bearing contexts?
>
> The simple answer is No, because SQL is a specification, not an
> implementation.  Different systems implement it differently and
> therefore perform differently.  Any "general technique" affecting
> performance belongs to the implementation per se, not the SQL, which is
> a logical construction. SQLite itself has changed its performance
> characteristics over the course of its development.
>
> For that reason, any question of performance has to be answered in
> terms of a particular implementation, even its specific version, and
> the OS and hardware it's running on.
>
> That said, there is reason to suppose that a single-table design would
> be more efficient.  If the queries can be expressed with recursion and
> the indexes lead to efficient searches, the query optimizer has less
> work to do.  It has fewer permutations to consider, and the search is
> apt to touch fewer pages.  The analysis tools of the system you're
> using should be able to confirm or deny that supposition.
>
> I would remind your fellows, though, that efficiency is not all.  The
> utility of a model (that is, the database design) is measured by how
> well, to its purpose, it describes the real world.  Any model that must
> be changed as that reality changes in predictable ways isn't really
> much of a model; it turns the designer into a component of the model.
> By recognizing all trees as one, you generalize your model and make it
> do work you are now doing yourself (manually, or in application
> logic).  By any measure, that makes it a better model.
>
> HTH.
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Thanks Eduardo,

a most interesting link.

On 17 October 2014 05:41, Eduardo Morras  wrote:

> On Thu, 16 Oct 2014 09:05:51 +1100
> Michael Falconer  wrote:
>
> > Hi all,
> >
> > first off I must start with an apology. I know I'm sort of doing the
> > wrong thing here as this question is NOT related to sqlite. It is a
> > general SQL question but I ask it here because I have great respect
> > for the answers and discussions I have seen on this forum over many
> > years. I rarely post myself as there are always several contributors
> > who beat me to the answer and often their response is far better than
> > mine would have been. I'm not a code leper, I don't need the actual
> > SQL just the method really, though a short code example would be well
> > received for illustration.
> >
> > Any thoughts? Suggestions? Missiles? Good approach, bad approach, or
> > completely off the grid? I do use sqlite quite a bit, but not on this
> > particular project.
>
> I point you to sqlite closure extension. It may shows you some ideas for
> tree implementation and parent/child relations under sql/sqlite.
>
> http://www.sqlite.org/src/artifact/636024302cde41b2bf0c542f81c40c624cfb7012
> http://www.sqlite.org/src/finfo?name=ext/misc/closure.c
>
> HTH
>
> >
> > --
> > Regards,
> >  Michael.j.Falconer.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ---   ---
> Eduardo Morras 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Thanks Stephen,

good point, I was just after some general results, I do take your point
about caching etc. However it is logical to apply UNION ALL if appropriate
in preference to UNION which in this context is a bit lazy. I'm a bit
annoyed I didn't pick up on it myself, but thankful to Igor for reminding
me of the difference in the two statements.


On 16 October 2014 11:14, Stephen Chrzanowski  wrote:

> Careful with the timing.  You may be looking at OS memory caching the
> result set instead of pulling from the drive.  For best bets, either re-run
> both queries several times, ditch the longest and shortest times, then take
> the mean or average times and do the comparison that way.
>
> On Wed, Oct 15, 2014 at 7:54 PM, Michael Falconer <
> michael.j.falco...@gmail.com> wrote:
>
> > Igor,
> >
> > Nice one. A quick test using 10 lookup trees and the same search
> criteria:
> > *Showing rows 0 - 29 (30 total, Query took 0.4838 sec)*
> >
> > Now with UNION ALL replacing UNION:
> > *Showing rows 0 - 29 (30 total, Query took 0.2050 sec)*
> >
> > You weren't kidding about cheaper were you? LOL
> >
> >
> > On 16 October 2014 10:30, Michael Falconer  >
> > wrote:
> >
> > > Yes, I agree with the general sentiment. It is not exactly the design I
> > > would have chosen either, but it is what exists. Design change is
> > occurring
> > > but for the moment we are stuck with the current schema.
> > >
> > > If you cannot change the schemata to be more suitable, then your
> > >> demonstrated queries are very near as good as it gets. I can think of
> > other
> > >> ways to achieve the same, but nothing that would be more efficient to
> a
> > >> tangible level. Maybe someone else here can come up with something a
> bit
> > >> more tight.
> > >>
> > > You are preaching to the converted I'm afraid, but I appreciate the
> > points
> > > you made. The quoted paragraph probably answers most of my questions
> > > really, except for this from Igor:
> > >
> > > Unless you do expect duplicates and need to eliminate them, use UNION
> ALL
> > >> - it's much cheaper (this is assuming you insist on keeping multiple
> > >> tables).
> > >>
> > > Cheaper because it drops the operation to remove the duplicate records
> > > from the result set? I'm assuming. I'll give that a try.
> > >
> > > On 16 October 2014 10:05, RSmith  wrote:
> > >
> > >>
> > >> On 2014/10/16 00:05, Michael Falconer wrote:
> > >>
> > >>> Hi all,
> > >>>
> > >>> first off I must start with an apology. I know I'm sort of doing the
> > >>> wrong//...
> > >>>
> > >>
> > >> No need to apologise, this flies quite close to the central theme.
> > >> Whether you are using SQLite or any other SQL RDBMS, this is horrible
> DB
> > >> design and it is so for precisely the reasons you are asking the
> > question.
> > >> Tables should not be dynamic: The simple rule of thumb being - if you
> > >> cannot tell (or at least accurately predict) before-hand exactly how
> > many
> > >> tables will be in the final system, then you are doing it wrong.  Why
> > not
> > >> have one set of tables and in stead of preceding each of them with a
> > >> , simply add a column that can host the  as a simple
> > value,
> > >> which will immediately make your life very much easier and get the SQL
> > >> engine to do the work you are now trying to compensate for in a
> > lop-sided
> > >> manual kind of way.
> > >>
> > >> I can only imagine the amount of code you invested into your system to
> > >> track and deal with these dynamic sets of tables, so I know changing
> it
> > >> will seem like a rather large undertaking, but really it will mostly
> > >> involve removing loads of code to end up with a small set of simple
> > queries
> > >> that does all the work for you.
> > >>
> > >> The only reason I can imagine this sort of breakdown useful is if your
> > >> tree tables are all really really huge, like Gigabytes, and so there
> > might
> > >> be some efficiency to be gained from splitting it up, but I doubt this
> > is
> > >> the case.
> > >>
> > >> Just imagine the ease of those search queries you demonstrated... one
> > >

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Igor,

Nice one. A quick test using 10 lookup trees and the same search criteria:
*Showing rows 0 - 29 (30 total, Query took 0.4838 sec)*

Now with UNION ALL replacing UNION:
*Showing rows 0 - 29 (30 total, Query took 0.2050 sec)*

You weren't kidding about cheaper were you? LOL


On 16 October 2014 10:30, Michael Falconer 
wrote:

> Yes, I agree with the general sentiment. It is not exactly the design I
> would have chosen either, but it is what exists. Design change is occurring
> but for the moment we are stuck with the current schema.
>
> If you cannot change the schemata to be more suitable, then your
>> demonstrated queries are very near as good as it gets. I can think of other
>> ways to achieve the same, but nothing that would be more efficient to a
>> tangible level. Maybe someone else here can come up with something a bit
>> more tight.
>>
> You are preaching to the converted I'm afraid, but I appreciate the points
> you made. The quoted paragraph probably answers most of my questions
> really, except for this from Igor:
>
> Unless you do expect duplicates and need to eliminate them, use UNION ALL
>> - it's much cheaper (this is assuming you insist on keeping multiple
>> tables).
>>
> Cheaper because it drops the operation to remove the duplicate records
> from the result set? I'm assuming. I'll give that a try.
>
> On 16 October 2014 10:05, RSmith  wrote:
>
>>
>> On 2014/10/16 00:05, Michael Falconer wrote:
>>
>>> Hi all,
>>>
>>> first off I must start with an apology. I know I'm sort of doing the
>>> wrong//...
>>>
>>
>> No need to apologise, this flies quite close to the central theme.
>> Whether you are using SQLite or any other SQL RDBMS, this is horrible DB
>> design and it is so for precisely the reasons you are asking the question.
>> Tables should not be dynamic: The simple rule of thumb being - if you
>> cannot tell (or at least accurately predict) before-hand exactly how many
>> tables will be in the final system, then you are doing it wrong.  Why not
>> have one set of tables and in stead of preceding each of them with a
>> , simply add a column that can host the  as a simple value,
>> which will immediately make your life very much easier and get the SQL
>> engine to do the work you are now trying to compensate for in a lop-sided
>> manual kind of way.
>>
>> I can only imagine the amount of code you invested into your system to
>> track and deal with these dynamic sets of tables, so I know changing it
>> will seem like a rather large undertaking, but really it will mostly
>> involve removing loads of code to end up with a small set of simple queries
>> that does all the work for you.
>>
>> The only reason I can imagine this sort of breakdown useful is if your
>> tree tables are all really really huge, like Gigabytes, and so there might
>> be some efficiency to be gained from splitting it up, but I doubt this is
>> the case.
>>
>> Just imagine the ease of those search queries you demonstrated... one
>> query, no UNIONs, a single set of joins and an extra where-clause check...
>> Add to that increased efficiency at finding results (run one query in stead
>> of 50).  Easy-mode. As a proof-of-concept, just make an SQLite DB, one set
>> of tables as per your list, add column(s) for prefixing as needed, import
>> all the tables from some large set of them (specifying additionally the
>> column for the prefix) and ten run search queries on them. I am willing to
>> bet on the fact it will be much quicker in addition to being much simpler.
>>
>> If you cannot change the schemata to be more suitable, then your
>> demonstrated queries are very near as good as it gets. I can think of other
>> ways to achieve the same, but nothing that would be more efficient to a
>> tangible level. Maybe someone else here can come up with something a bit
>> more tight.
>>
>>
>>  thing here as this question is NOT related to sqlite. It is a general SQL
>>> question but I ask it here because I have great respect for the answers
>>> and
>>> discussions I have seen on this forum over many years. I rarely post
>>> myself
>>> as there are always several contributors who beat me to the answer and
>>> often their response is far better than mine would have been. I'm not a
>>> code leper, I don't need the actual SQL just the method really, though a
>>> short code example would be well received for illustration.
>>>
>>> It's about a search performed on multiple tables. However the structure
&g

Re: [sqlite] Search query alternatives.

[Michael Falconer]

Yes, I agree with the general sentiment. It is not exactly the design I
would have chosen either, but it is what exists. Design change is occurring
but for the moment we are stuck with the current schema.

If you cannot change the schemata to be more suitable, then your
> demonstrated queries are very near as good as it gets. I can think of other
> ways to achieve the same, but nothing that would be more efficient to a
> tangible level. Maybe someone else here can come up with something a bit
> more tight.
>
You are preaching to the converted I'm afraid, but I appreciate the points
you made. The quoted paragraph probably answers most of my questions
really, except for this from Igor:

Unless you do expect duplicates and need to eliminate them, use UNION ALL -
> it's much cheaper (this is assuming you insist on keeping multiple tables).
>
Cheaper because it drops the operation to remove the duplicate records from
the result set? I'm assuming. I'll give that a try.

On 16 October 2014 10:05, RSmith  wrote:

>
> On 2014/10/16 00:05, Michael Falconer wrote:
>
>> Hi all,
>>
>> first off I must start with an apology. I know I'm sort of doing the
>> wrong//...
>>
>
> No need to apologise, this flies quite close to the central theme. Whether
> you are using SQLite or any other SQL RDBMS, this is horrible DB design and
> it is so for precisely the reasons you are asking the question.  Tables
> should not be dynamic: The simple rule of thumb being - if you cannot tell
> (or at least accurately predict) before-hand exactly how many tables will
> be in the final system, then you are doing it wrong.  Why not have one set
> of tables and in stead of preceding each of them with a , simply
> add a column that can host the  as a simple value, which will
> immediately make your life very much easier and get the SQL engine to do
> the work you are now trying to compensate for in a lop-sided manual kind of
> way.
>
> I can only imagine the amount of code you invested into your system to
> track and deal with these dynamic sets of tables, so I know changing it
> will seem like a rather large undertaking, but really it will mostly
> involve removing loads of code to end up with a small set of simple queries
> that does all the work for you.
>
> The only reason I can imagine this sort of breakdown useful is if your
> tree tables are all really really huge, like Gigabytes, and so there might
> be some efficiency to be gained from splitting it up, but I doubt this is
> the case.
>
> Just imagine the ease of those search queries you demonstrated... one
> query, no UNIONs, a single set of joins and an extra where-clause check...
> Add to that increased efficiency at finding results (run one query in stead
> of 50).  Easy-mode. As a proof-of-concept, just make an SQLite DB, one set
> of tables as per your list, add column(s) for prefixing as needed, import
> all the tables from some large set of them (specifying additionally the
> column for the prefix) and ten run search queries on them. I am willing to
> bet on the fact it will be much quicker in addition to being much simpler.
>
> If you cannot change the schemata to be more suitable, then your
> demonstrated queries are very near as good as it gets. I can think of other
> ways to achieve the same, but nothing that would be more efficient to a
> tangible level. Maybe someone else here can come up with something a bit
> more tight.
>
>
>  thing here as this question is NOT related to sqlite. It is a general SQL
>> question but I ask it here because I have great respect for the answers
>> and
>> discussions I have seen on this forum over many years. I rarely post
>> myself
>> as there are always several contributors who beat me to the answer and
>> often their response is far better than mine would have been. I'm not a
>> code leper, I don't need the actual SQL just the method really, though a
>> short code example would be well received for illustration.
>>
>> It's about a search performed on multiple tables. However the structure of
>> this database is somewhat unorthodox. It contains genealogical data and
>> this is clustered into trees. Each tree has 8 tables, there can be as many
>> as 100 trees but most installations of the associated application software
>> contain between 5 - 50 trees. These 8 tables contain a family trees data
>> and are named:
>> addresses
>> connections
>> events
>> family
>> person
>> repositories
>> sources
>> texts
>>
>> The  changes and is unique for each tree.
>>
>> There are extensive search options offered to users as well as simple name
>> searching and it is on

[sqlite] Search query alternatives.

[Michael Falconer]

Hi all,

first off I must start with an apology. I know I'm sort of doing the wrong
thing here as this question is NOT related to sqlite. It is a general SQL
question but I ask it here because I have great respect for the answers and
discussions I have seen on this forum over many years. I rarely post myself
as there are always several contributors who beat me to the answer and
often their response is far better than mine would have been. I'm not a
code leper, I don't need the actual SQL just the method really, though a
short code example would be well received for illustration.

It's about a search performed on multiple tables. However the structure of
this database is somewhat unorthodox. It contains genealogical data and
this is clustered into trees. Each tree has 8 tables, there can be as many
as 100 trees but most installations of the associated application software
contain between 5 - 50 trees. These 8 tables contain a family trees data
and are named:
addresses
connections
events
family
person
repositories
sources
texts

The  changes and is unique for each tree.

There are extensive search options offered to users as well as simple name
searching and it is one of the best features of the app. It works pretty
well, so it ain't broke in any sense, we just wonder if there is a better
way to perform this search in SQL. Is there a general technique which is
superior either in speed, efficiency or load bearing contexts?

I am sure you can see one of the pitfalls here is the exponential growth of
such a search query as both total number of trees and indeed user search
criteria increase. For each criteria component, and there are quite a few,
the appropriate tables must be queried and results joined.

Searches return records of individuals meeting the entered search criteria
so the query focuses on the person table as it's anchor, performs searches
on required other tables in the tree and joins the results to the person
data. The results from each tree search are then UNION'ed to provide the
result set. Here is a contrived example of the SQL query code which should
make things clearer.

The user enters simple search criteria - any person with a last name
containing 'mac' and a first name containing the character 'a'. This is the
resulting query (generated by php code), which searches a small 4 family
tree installation.

(SELECT humo1_person.*, event_kind, event_event, address_place, address_zip
FROM humo1_person
LEFT JOIN humo1_events ON pers_gedcomnumber=event_person_id
LEFT JOIN humo1_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%')
  )
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo2_person.*, event_kind, event_event, address_place,
address_zip
FROM humo2_person LEFT JOIN humo2_events ON
pers_gedcomnumber=event_person_id
LEFT JOIN humo2_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%') )
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo3_person.*, event_kind, event_event, address_place,
address_zip
FROM humo3_person LEFT JOIN humo3_events ON
pers_gedcomnumber=event_person_id
LEFT JOIN humo3_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%')
  )
GROUP BY pers_gedcomnumber
)
UNION
(SELECT humo4_person.*, event_kind, event_event, address_place,
address_zip
FROM humo4_person
LEFT JOIN humo4_events ON pers_gedcomnumber=event_person_id
LEFT JOIN humo4_addresses ON pers_gedcomnumber=address_person_id
WHERE pers_lastname LIKE '%mac%'
  AND (pers_firstname LIKE '%a%'
  OR (event_kind='name' AND event_event LIKE '%a%')
  )
GROUP BY pers_gedcomnumber
)
ORDER BY pers_lastname ASC , pers_firstname ASC LIMIT 0,30

Any thoughts? Suggestions? Missiles? Good approach, bad approach, or
completely off the grid? I do use sqlite quite a bit, but not on this
particular project.


-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Deleting columns in SQLite

[Michael Leung]

Hi,

I posted a question on stackoverflow and like to forward it to the sqlite 
community.

java - SQLite Ways to delete column: Using temporary table vs Renaming using 
ALTER TABLE - Stack Overflow

  
  
java - SQLite Ways to delete column: Using temporary table vs Renaming using 
ALTER TABLE - Sta...
I am trying to find the best way to delete a column in SQLite. Here are the two 
ways I found: From the official website http://www.sqlite.org/faq.html#q11: 
BEGIN TRANSACTION;   
View on stackoverflow.com Preview by Yahoo  
  

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

Re: [sqlite] New wrapper library: QUINCE

[Michael Shepanski]

Hello,

I've just found the page 
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers , which says "If you 
know of a driver or wrapper for SQLite that is not listed below, please 
feel free to add it to the list."


I would like to add an entry for quince, but I need to log in. Log in to 
what? How do I register? Or do I need to ask an authorized person to do it?


Thanks,
--- Michael


On 19/08/2014 8:18 AM, Michael Shepanski wrote:

Hi Sqliters,

I've released an open-source library called quince (QUeries In C++ 
Expressions) that lets you access sqlite3 easily from C++.  I know, I 
know: you've got plenty of those already, but this one is different I 
promise...


It's an EDSL (Embedded Domain-Specific Language), which lets you build 
sophisticated SQL features in C++ syntax with C++ data types, and it's 
also an ORM, which figures out how to represent your C++ 
structs/classes/tuples as multiple columns. It's a plain old library, 
so no special compiler and no code generation step. And fwiw it works 
with PostgreSQL too (and maybe other DBMSes in the future -- it's a 
matter of adding backend libraries).


It's all explained at http://quince-lib.com .

Cheers,
--- Michael Shepanski

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


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

[sqlite] New wrapper library: QUINCE

[Michael Shepanski]

Hi Sqliters,

I've released an open-source library called quince (QUeries In C++ 
Expressions) that lets you access sqlite3 easily from C++.  I know, I 
know: you've got plenty of those already, but this one is different I 
promise...


It's an EDSL (Embedded Domain-Specific Language), which lets you build 
sophisticated SQL features in C++ syntax with C++ data types, and it's 
also an ORM, which figures out how to represent your C++ 
structs/classes/tuples as multiple columns. It's a plain old library, so 
no special compiler and no code generation step. And fwiw it works with 
PostgreSQL too (and maybe other DBMSes in the future -- it's a matter of 
adding backend libraries).


It's all explained at http://quince-lib.com .

Cheers,
--- Michael Shepanski

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

Re: [sqlite] parser stack overflow in view

[Michael]

Finally it works and fast again :-)

It was possible to execute some parts before.

Thanks to all helpers

> Gesendet: Montag, 04. August 2014 um 14:26 Uhr
> Von: "Clemens Ladisch" 
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] parser stack overflow in view
>
> Michael wrote:
> >> Von: "Richard Hipp" 
> >> The LALR(1) parser stack is limited to a depth of 100, by default.
> >
> > Ok thanks. I reduced it by one subquery to be conform with standard
builds.
>
> The parser has different limits than the SQL execution engine.
> You could simply move some part(s) of the query into a (temporary) view.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parser stack overflow in view

[Michael]

Ok thanks. I reduced it by one subquery to be conform with standard builds.

My querys are auto generated and are not often needed to read by human.
For my porpose it was the only way build a correct structure for
searching hirarchy with needed options.
category/subcat1/subcat2/subcat3

Anyway. I have one problem left. If I do "select count(*) from (... 
UNION ALL ...   );" I still get stack overflow. Ok I know why but don't
know to avoid that.

Is there a better way to count the results of big query before?


> Gesendet: Freitag, 01. August 2014 um 13:41 Uhr
> Von: "Richard Hipp" 
> An: "General Discussion of SQLite Database" 
> Betreff: Re: [sqlite] parser stack overflow in view
>
> On Tue, Jul 29, 2014 at 5:00 AM, Michael 
wrote:
>
> > I have a view with about 6 Unions and a depth of about 6 subselects in
> > each select.
> > Shouldn't be a big thing and it was no problem with sqlite 3.7.17.
> > Since 3.8 (3.8.4.3) I get "parser stack overflow". I have many queries
> > with this problem now...
> >
>
>
> The LALR(1) parser stack is limited to a depth of 100, by default. 
You can
> change that at compile-time using -DYYSTACKDEPTH=nnn  where "nnn" is some
> number.  If you make "nnn" equal to zero, then the LALR(1) parser stack is
> obtained from sqlite3_realloc() and it can grow without bound.  In your
> particular case, you can get your query to run successfully by increasing
> the default stack size by just one to -DYYSTACKDEPTH=101.
>
> We have discussed making the stack unlimited depth by default.  But there
> is a small performance and size penalty for doing that.  And, honestly, if
> your query needs more than 100 levels of LALR(1) stack, it is going to be
> difficult for a human to read anyhow, and probably needs to be refactored.
> So I think we will keep the default 100-level limit for the time being and
> let individual applications extend the limit at compile-time, if they need
> to.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Long execution time since sqlite 3.8

[Michael]

I also tried to remove the views of some queries and create index. But
nothing worked so far.
Anyway it couldn't be the reason for the "parser stack overflow" of my
other question.
Is the information I sent sufficient?

*Gesendet:* Dienstag, 29. Juli 2014 um 15:17 Uhr
*Von:* Michael 
*An:* sqlite-users@sqlite.org
*Betreff:* Re: [sqlite] Long execution time since sqlite 3.8
CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE tbl_lib_filters(UUID TEXT NOT NULL PRIMARY KEY, template
TEXT NOT NULL, library TEXT NOT NULL, rules TEXT);
CREATE TABLE tbl_library(UUID TEXT NOT NULL PRIMARY KEY, TITLE TEXT,
ICON_URI TEXT, DESCCRIPTION TEXT, REMOVED INTEGER NOT NULL,
sortTemplateUUID TEXT, groupTemplateUUID TEXT, sortDirection INTEGER,
templateVersion INTEGER, googleDocId TEXT, googleDocWorksheet TEXT,
googleDocNeedSync INTEGER, googleDocTemplateVersion INTEGER,
picasaAlbumId TEXT, public_id TEXT, lib_alias TEXT, allowed_users TEXT,
private_lib INTEGER, need_update_pub_template INTEGER, type INTEGER,
group_id INTEGER, google_sync_time INTEGER, public_time INTEGER,
protected INTEGER, encripted INTEGER, lib_order INTEGER, tile_columns
INTEGER, tile_color INTEGER, edit_time INTEGER, filter_uuid TEXT,
tile_text_color INTEGER, cloud_storage TEXT, cloud_folder_id TEXT,
lock_edit INTEGER, template_gd_file_id TEXT, entry_pages TEXT,
sort_options TEXT);
CREATE TABLE tbl_library_item(UUID TEXT NOT NULL PRIMARY KEY, LIB_UUID
TEXT, REMOVED INTEGER NOT NULL, creation_date INTEGER, REMOVED_TIME
INTEGER, VIEW_TIME INTEGER, FAVORITE INTEGER, EDIT_TIME INTEGER, FTS3_ID
INTEGER);
CREATE TABLE tbl_flex_content2(id INTEGER PRIMARY KEY AUTOINCREMENT,
stringContent TEXT, realContent REAL, intContent INTEGER, ownerUUID TEXT
NOT NULL, templateUUID TEXT NOT NULL);
CREATE TABLE tbl_filter_profiles(UUID TEXT NOT NULL PRIMARY KEY, library
TEXT NOT NULL, filter_name TEXT);
CREATE TABLE tbl_flex_template(UUID TEXT NOT NULL PRIMARY KEY, title
TEXT, type_code TEXT NOT NULL, usage INTEGER NOT NULL, number INTEGER
NOT NULL, encripted INTEGER, req INTEGER, LIB_UUID TEXT, stats TEXT,
hint TEXT, depends TEXT, display_title INTEGER);
CREATE TABLE tbl_gdocs_item_handlers(item_uuid TEXT NOT NULL PRIMARY
KEY, lib_uuid TEXT NOT NULL, gdocs_record_id TEXT NOT NULL, remove_flag
INTEGER, etag TEXT NOT NULL, edited INTEGER);
CREATE TABLE tbl_picasa_images(picasa_url TEXT NOT NULL, local_uri TEXT
NOT NULL, lib_uuid TEXT NOT NULL);
CREATE TABLE tbl_pub_item_handlers(item_uuid TEXT NOT NULL PRIMARY KEY,
lib_uuid TEXT NOT NULL, pub_id TEXT NOT NULL, remove_flag INTEGER,
version INTEGER, edited INTEGER);
CREATE TABLE tbl_groups(id INTEGER NOT NULL PRIMARY KEY, title TEXT NOT
NULL, sys_code TEXT);
CREATE TABLE tbl_master_hash (pass_hash TEXT NOT NULL PRIMARY KEY);
CREATE TABLE tbl_icon_storage(id INTEGER PRIMARY KEY AUTOINCREMENT, icon
BLOB);
CREATE TABLE tbl_field_email_format_2 (template_uuid TEXT NOT NULL,
lib_uuid TEXT NOT NULL, new_line INTEGER, ex_options TEXT,
colon_after_name INTEGER, email_template INTEGER, send_by_email INTEGER);
CREATE TABLE tbl_reminders3(id INTEGER PRIMARY KEY AUTOINCREMENT,
rem_minutes INTEGER, rem_time INTEGER);
CREATE TABLE tbl_email_templ (id INTEGER PRIMARY KEY
AUTOINCREMENT,lib_uuid TEXT NOT NULL,title TEXT);
CREATE TABLE tbl_cloud_files(cloud_url TEXT NOT NULL, local_uri TEXT NOT
NULL, cloud_id TEXT NOT NULL, lib_uuid TEXT NOT NULL);
CREATE VIRTUAL TABLE library_fts3 USING fts3();
CREATE TABLE 'library_fts3_content'(docid INTEGER PRIMARY KEY, 'c0content');
CREATE TABLE 'library_fts3_segments'(blockid INTEGER PRIMARY KEY, block
BLOB);
CREATE TABLE 'library_fts3_segdir'(level INTEGER,idx INTEGER,start_block
INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY
KEY(level, idx));
CREATE TABLE category_alias (id integer PRIMARY KEY, category_id
integer, name string, Unique (name));
CREATE TABLE disks_bez (id integer PRIMARY KEY, name string, bez string,
Unique (name));
CREATE TABLE item_ref(id integer PRIMARY KEY,item1_id integer,
item1_type_id integer, item2_id integer, item2_type_id integer, unique
(item1_id, item1_type_id, item2_id, item2_type_id));
CREATE TABLE links(id integer PRIMARY KEY,name text,mediatype
text,source text,mirror text,dateAdded datetime,zone integer default 3,
pid integer, fid integer, votes integer default 0 not null, rating
integer default 0 not null, unique (source));
CREATE TABLE zone_bez (id integer PRIMARY KEY, zone integer, bez string,
Unique (zone));
CREATE TABLE zones (id integer PRIMARY KEY, zones integer, zone integer,
Unique (zones,zone));
CREATE TABLE category_item(id integer PRIMARY KEY,category_id integer
not null,item_id integer not null,item_type_id integer not null,UNIQUE
(category_id,item_id,item_type_id));
CREATE TABLE history(item_id integer, item_type_id integer, search_term
text, categories text, ex_categories text, zones integer, timestamp
datetime, command text, parameters text, output text);
CREATE TABLE categor

Re: [sqlite] Long execution time since sqlite 3.8

[Michael]

ULL PRIMARY KEY,item_id
integer not null,item_type_id integer not null);
CREATE TABLE infos(id integer PRIMARY KEY,name text,text text,zone
integer default 3, dateAdded datetime, lastModified datetime, votes
integer default 0 not null, rating integer default 0 not null,
expiration datetime, exp_action integer, date dateTime);
CREATE TABLE tbl_charts(id INTEGER PRIMARY KEY AUTOINCREMENT, title
TEXT, libraryUUID TEXT, options TEXT, type TEXT, filterUUID TEXT);
CREATE INDEX idx_library_removed ON tbl_library ( REMOVED );
CREATE INDEX idx_library_item_lib ON tbl_library_item ( LIB_UUID );
CREATE INDEX idx_library_item_removed ON tbl_library_item ( REMOVED );
CREATE INDEX idx_library_item_fts3 ON tbl_library_item ( FTS3_ID );
CREATE INDEX idx_flex_content_owner ON tbl_flex_content2 ( ownerUUID );
CREATE INDEX idx_flex_content_temp ON tbl_flex_content2 ( templateUUID );
CREATE INDEX idx_filters2_library ON tbl_filter_profiles ( library );
CREATE INDEX idx_flex_template_lib ON tbl_flex_template ( LIB_UUID );
CREATE INDEX idx_picasa_lib_uuid ON tbl_picasa_images ( lib_uuid );
CREATE INDEX idx_cloud_files_lib_uuid ON tbl_cloud_files ( lib_uuid );
CREATE INDEX idx_cloud_files_local_uri ON tbl_cloud_files ( local_uri );
CREATE VIEW category_link as select category_id, item_id from
category_item where item_type_id=2;
CREATE VIEW category_info as select category_id, item_id from
category_item where item_type_id=1;
CREATE VIEW v_infos as select * from infos;
CREATE VIEW category_file as select category_id, item_id from
category_item where item_type_id=3;
CREATE VIEW v_links as select *,dateAdded as date from links;
CREATE VIEW mv_name AS
select FTS3_ID,stringContent as name from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='name';
CREATE VIEW mv_text AS
select FTS3_ID,stringContent as text from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='text';
CREATE VIEW mv_zone AS
select FTS3_ID,intContent as zone from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='zone';
CREATE VIEW mv_dateAdded AS
select FTS3_ID, datetime(substr(stringContent,1,10), 'unixepoch',
'localtime') as dateAdded from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='dateAdded';
CREATE VIEW mv_date AS
select FTS3_ID, datetime(substr(stringContent,1,10), 'unixepoch',
'localtime') as date from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='date';
CREATE VIEW mv_expiration AS
select FTS3_ID, datetime(substr(stringContent,1,10), 'unixepoch',
'localtime') as expiration from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='expiration';
CREATE VIEW mv_exp_action AS
select FTS3_ID, intContent as exp_action from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='exp_action';
CREATE VIEW mv_lastModified AS
select FTS3_ID, datetime(substr(EDIT_TIME,1,10), 'unixepoch',
'localtime') as lastModified from tbl_library_item;
CREATE VIEW mv_votes AS
select FTS3_ID,intContent as votes from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='votes';
CREATE VIEW mv_rating AS
select FTS3_ID,intContent as rating from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='rating';

> Gesendet: Dienstag, 29. Juli 2014 um 15:12 Uhr
> Von: Michael 
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] Long execution time since sqlite 3.8
>
> Ok that's the output of sqlite_stat1:
>
> tbl|idx|stat
> android_metadata||1
> tbl_flex_template|idx_flex_template_lib|10 10
> tbl_flex_template|sqlite_autoindex_tbl_flex_template_1|10 1
> zones|sqlite_autoindex_zones_1|84 6 1
> history||5870
> tbl_flex_content2|idx_flex_content_temp|20010 2001
> tbl_flex_content2|idx_flex_content_owner|20010 10
> tbl_library|idx_library_removed|1 1
> tbl_library|sqlite_autoindex_tbl_library_1|1 1
> tbl_lib_filters|sqlite_autoindex_tbl_lib_filters_1|4 1
> tbl_filter_profiles|idx_filters2_library|4 4
> tbl_filter_profiles|sqlite_autoindex_tbl_filter_profiles_1|4 1
> category_item|sqlite_autoindex_category_item_1|6827 20 2