Re: [sqlite] Roadmap?

[Jens Alfke]

> On Oct 27, 2019, at 1:50 PM, Thomas Kurz  wrote:
> 
> But not compatible to standards from Open Geospatial Consortium, as far as I 
> know. 

That's the nice thing about standards: there are so many of them.

> Which requires additional conversions, and considering that geodata usually 
> have sizes of a GB or more, this is not an option at all.

Yes, you might have to buy one of those new 4GB hard disks, or upgrade your 486 
box to a Pentium… ;-)

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

Re: [sqlite] Roadmap?

[D Burgess]

Number one on my wishlist.
UNSIGNED
Wanted for ordering more than anything else
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Roadmap?

[Thomas Kurz]

> SQLite has had geospatial support for years via the r-tree extension, and 
> more recently GeoJSON.

But not compatible to standards from Open Geospatial Consortium, as far as I 
know. Which requires additional conversions, and considering that geodata 
usually have sizes of a GB or more, this is not an option at all.

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

Re: [sqlite] Roadmap?

[Keith Medcalf]

On Sunday, 27 October, 2019 13:30, Richard Damon  
wrote:

>On 10/27/19 10:24 AM, Simon Slavin wrote:

>> On 27 Oct 2019, at 9:12am, Thomas Kurz  wrote:

>>> the discussion standard deviation has shown that at least STDEV and
>>> POWER would be very helpful if they part of SQLite core.

>> These are presentation issues.  Not database issues.   The results of
>> such calculations are unlikely to be used to decide on the continuation
>> of a SQL statement.

> Doing a group by summary query to get the mean and stdev of a sample in
> the database, and then using those to construct a where clause to
> get/exclude data points that are outliers would seem to be a very
> reasonable sort of query that is really a database issue. This could
> normally be done as two distinct queries (maybe within a read
> transaction so we get consistent data) and the stdev and mean built by
> building a count of records, and the sum of the value and value**2, and
> computing the mean and stdev from that in code, as was shown earlier in
> the thread, but having STDEV as a built-in summary function could be
> useful.

Defining an aggregate function that computes the standard deviation (whether of 
the sample or the population) depending of the values fed into the aggregate is 
rather trivial to do, if you need to do that.  That there are some programming 
languages and platforms that do not permit you to do this "easily" speaks more 
to the deficiencies of those programming languages or platforms than it does to 
SQLite3.  You still have to decide whether you want to do it with inaccurate 
arithmetic methods that may be compatible with how window functions work, or 
using more accurate numerical methods that are not compatible with how window 
functions work, or perhaps by implementing something that is both at the same 
time (and uses more memory and cpu (complexity) in order to allow utilization 
of better numerical methods yet still maintain window function compatibity).

Or you could simply execute a query to get the samples you want and do the 
calculation in your code, then pass that "answer" back as a parameter to your 
subsequent SQL that needs that value.  The need to write "one long monster SQL 
statement to do everything all at once" is usually adopted because one is used 
to using client/server systems where such things take excessive amounts of time 
due to client/server turnarounds.

The point is that how you accomplish this is up to you, and however you decide 
to do it is up to you and your requirements.  If SQLite3 had a built-in stdev 
aggregate/window function, you can be sure that someone would determine that 
the built-in was insufficient or overkill to their requirement and would end up 
overriding that function with one which they, in their sole discretion, thought 
was a better fit to their requirement.  This would end up bloating the size of 
the engine for no gain -- or adding yet another configuration parameter to 
exclude the extraneous built-in.  Multiply this by the thousands of possible 
functions and aggregates and you get an idea of the complexity involved.

As an example, I always compile in a proper version of the ROUND() function 
because the builtin function does elementary school 4/5 rounding and not 
half-even rounding.  Even the builtin SUM and TOTAL are rather simplistic 
implementations that can be wildly inaccurate in certain circumstances.  
However, it appears that most "coders" only graduated elementary school and 
would be confused by proper rounding, just as they do not know how binary 
digital computers work and are thus confused by floating-point numbers not 
being the same as they learned in elementary school.

I also always override a pile of the builtin datetime functions to overcome 
some often encountered shortcomings there as well (though that one does require 
a minor modification to the as-distributed code to add some more information 
that would otherwise be lost to the internal DateTime struct, and to the 
parsing routines in order to maintain that extra data).

This can be done because SQLite3 has chosen extensibility over "Do as I have 
Ordained, and If You Do Not Like That Which I Have Ordained, then Too Bad For 
You" route used by most other RDBMS engines.  And even there, on the few other 
RDBMS where such overrides of internal functionality or addition of "extra" 
functionality is available, it is almost always far more complicated (almost, 
becase there are a few exceptions), and often with poorer results overall (such 
as added complication simply for complication sake or slower performance or, in 
many cases, both).

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

Re: [sqlite] Roadmap?

[Simon Slavin]

On 27 Oct 2019, at 7:43pm, Richard Damon  wrote:

> My one thought about fuller ALTER TABLE support would be that since
> SQLite stores the schema as the simple CREATE TABLE command, that ALTER
> TABLE needs to know how to edit that string to match the changes, where
> simple adds are much easier than other forms of editing.

There are two ways to do it.  One is to try to edit the original CREATE TABLE 
command.  The other is to make the change, then write a new CREATE TABLE 
command from the internal record SQLite maintains of the columns of that table.

The second method would be easier to make crash-free, and far easier to debug.  
It would lose formatting and comments from the original command.  But I'm cool 
with that.

The other problem is deleted data.  Suppose you had a table with five columns 
and someone dropped the third column.  First, you need to check that your 
entire schema, including triggers, to make sure nothing refers to that column.  
I don't know how difficult that would be given how SQLite works.

Then you need to deal with the values that had been stored in that column.  
Again, there are two ways to handle it.  One is to rewrite each row of the 
table without the third value, which would be slow and incorporate a ton of 
I/O.  The other is to just note that column 3 must be ignored.  (Change its 
name and give it affinity IGNORE_ME ?)  That would be extremely fast.  If the 
programmer cares about filespace they can do their own VACUUM at a convenient 
time.

Other ALTER TABLE commands have their own problems.  I don't see anything that 
can't be done, but some of them might be difficult.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Roadmap?

[Richard Damon]

On 10/26/19 8:07 PM, Thomas Kurz wrote:
>> Feel free to make suggestions. Which missing feature or features causes 
> you the most bother?
>
> Thanks, Dan.
>
> To me, the most puzzling thing is the lack of full ALTER TABLE support (DROP 
> COLUMN, MODIFY COLUMN, ADD CONSTRAINT, DROP CONSTRAINT). Modifying tables is 
> some kind of science in SQLite, and thus, very error-prone. I'd be willing to 
> donate for that but as a private user I cannot affort 85 k$ ;-)
>
My one thought about fuller ALTER TABLE support would be that since
SQLite stores the schema as the simple CREATE TABLE command, that ALTER
TABLE needs to know how to edit that string to match the changes, where
simple adds are much easier than other forms of editing.

Also, due to the way SQLite handles rows, adding columns will
automatically assume default values for all existing rows. Anything much
more complicated  could easily require that SQLite update the whole
table row by row, and that likely would require making sure that nothing
else was looking at the database, as it might not be possible to keep
the view consistent.

-- 
Richard Damon

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

Re: [sqlite] Roadmap?

[Richard Damon]

On 10/27/19 10:24 AM, Simon Slavin wrote:
> On 27 Oct 2019, at 9:12am, Thomas Kurz  wrote:
>
>> the discussion standard deviation has shown that at least STDEV and POWER 
>> would be very helpful if they part of SQLite core.
> These are presentation issues.  Not database issues.   The results of such 
> calculations are unlikely to be used to decide on the continuation of a SQL 
> statement.

Doing a group by summary query to get the mean and stdev of a sample in
the database, and then using those to construct a where clause to
get/exclude data points that are outliers would seem to be a very
reasonable sort of query that is really a database issue. This could
normally be done as two distinct queries (maybe within a read
transaction so we get consistent data) and the stdev and mean built by
building a count of records, and the sum of the value and value**2, and
computing the mean and stdev from that in code, as was shown earlier in
the thread, but having STDEV as a built-in summary function could be useful.

-- 
Richard Damon

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

Re: [sqlite] Roadmap?

[František Kučera]

Dne 27. 10. 19 v 15:24 Simon Slavin napsal(a):
> And I agree with you on RIGHT JOIN: it may duplicate what can be done with 
> LEFT JOIN but many SQL facilities are duplicates.  It's in SQL92 and people 
> expect to see it.

+1 This would increase portability of SQL scripts – it would be easier
to have a single set SQL queries that run on both SQLite and e.g.
PostgreSQL.

Franta

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

Re: [sqlite] Roadmap? - modular design

[František Kučera]

Dne 27. 10. 19 v 10:12 Thomas Kurz napsal(a):
> at least some basic math would be very helpful as well. I don't want to 
> suggest a complete math support, that would really be far away from liteness

Generally speaking, it might be difficult to say where is the line
between „light“ and „heavy“. But this issue might be resolved by modular
design (and SQLite already support modules). There might be an extension
full of mathematical functions. It might be „too heavy“ for someone, but
it would not be a problem, because you would be able to use just the
light core without this module and enjoy less complexity.

Franta


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

Re: [sqlite] Roadmap?

[Jean-Christophe Deschamps]

Hi Simon,

Especially since it can't do something more fundamental than STDEV: 
return all surnames starting with the Unicode character 'Å'.


Reconsider as this works fine.

JcD 


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

Re: [sqlite] Roadmap?

[Simon Slavin]

On 27 Oct 2019, at 9:12am, Thomas Kurz  wrote:

> the discussion standard deviation has shown that at least STDEV and POWER 
> would be very helpful if they part of SQLite core.

These are presentation issues.  Not database issues.   The results of such 
calculations are unlikely to be used to decide on the continuation of a SQL 
statement.  Yes, "WHERE a < (b POWER c)" exists, but how often would you need 
it in real life ?

SQLite is a database system.  We shouldn't start to extend it to a presentation 
layer.  Especially since it can't do something more fundamental than STDEV: 
return all surnames starting with the Unicode character 'Å'.

I'm not entirely against adding facilities to SQLite.  But I feel that they 
should be database things, not presentation things.  For example, I think 
supporting more ALTER TABLE would be worthwhile.  And I agree with you on RIGHT 
JOIN: it may duplicate what can be done with LEFT JOIN but many SQL facilities 
are duplicates.  It's in SQL92 and people expect to see it.  I am sometimes 
also in favour of adding a BOOLEAN type, even though it does not appear in 
SQL92.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Roadmap?

[Thomas Kurz]

> What do you mean by "SQL-basics"?

I forgot to mention that at least some basic math would be very helpful as 
well. I don't want to suggest a complete math support, that would really be far 
away from liteness, but the discussion standard deviation has shown that at 
least STDEV and POWER would be very helpful if they part of SQLite core.

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

Re: [sqlite] Roadmap?

[Thomas Kurz]

> Omitting RIGHT JOIN is good, that's a misfeature and LEFT JOIN does 
> everything 
useful it does.

With all dear respect, but I don't think that it is up to you to define what a 
"feature" and a "misfeature" is. iirc, RIGHT JOIN is declared in SQL92, it is 
part of the SQL standard, and therefore it is one of the "SQL basics" I 
mentioned. And it's not a big thing either.

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

Re: [sqlite] Roadmap?

[Darren Duncan]

On 2019-10-26 4:38 p.m., Thomas Kurz wrote:

The features you name don't take away from the "liteness", they are all quite

small and useful.

Yes of course they are useful, I wouldn't deny that. But they are prioritized 
over SQL-basics, that's what I'm confused about.


What do you mean by "SQL-basics"?

If you mean the list from your next post, there's very little basic about those 
and many are quite complicated.


I agree with adding more ALTER TABLE options but that's about it.

Omitting RIGHT JOIN is good, that's a misfeature and LEFT JOIN does everything 
useful it does.


Omitting SHOW TABLES or similar MySQL-only things is good, those are 
misfeatures, and querying INFORMATION_SCHEMA does everything better and in a 
more standard and composable way.


Anything to do with temporal and spatial is actually quite complicated, both 
data types and constraints, and omitting those is quite reasonable.


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

Re: [sqlite] Roadmap?

[Warren Young]

On Oct 26, 2019, at 6:28 PM, J. King  wrote:
> 
> a good designer will choose a good schema from the start and thus rarely need 
> to change it

When you add new features to a long-lived application, you may occasionally 
have to ALTER the table(s) the app uses to allow the new feature.  My company’s 
primary product has seen roughly two such DB alterations per year over its 
history, on average.

(The alterations are probably slowing down in truth, as we asymptotically 
approach perfection.  Hah!)

SQLite has you covered when the alteration is just to add a new column or 
rename an existing one, but that’s not every case I’ve run into over my time 
writing DB-based software:

1. We added some DB columns in version N of our software to support new 
features required by clients, but then something like 5 years later, that 
technology dropped out of current use, so we dropped the feature and thus 
dropped those supporting DB columns.

2. We extended an existing feature of the software to allow new functionality, 
requiring DB table additions, then several major versions later we replaced 
that sidecar’d feature with a wholly new tech stack, including its own DB 
tables, so we dropped those intermediate-form DB columns after migrating the 
data.

3. An existing DB table had to have a column added to make the PRIMARY KEY 
unique again after we added a feature that would have allowed that column to 
hold duplicate data; the second column disambiguated the cases.  SQLite lets 
you add the new column, but not change a table’s primary key without creating a 
new table and copying the data.

SQLite avoids some of the need for this with its dynamic typing and its 
unwillingness to enforce length limits.  We have several historical cases where 
we were using another DBMS and needed ALTER TABLE calls not allowed under 
SQLite to change column types or to widen fields.

For example, we had a table that started out using an 8-bit data type to hold a 
TV channel number, since the highest channel number at the time was 125, so 8 
bits was actually one more than we really required.  Then digital TV happened.  
Then IPTV happened.  Then OTT happened.  Now we just have a string column and a 
bunch of logic to figure out whether the string is an analog TV channel number, 
a digital TV channel number, an IP address, a URL…  If we’d been using SQLite 
from the start, we could have just kept using that “TINYINT” column to hold all 
of this, but ick.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Roadmap?

[Jens Alfke]

> On Oct 26, 2019, at 5:12 PM, Thomas Kurz  wrote:
> 
> Geospatial support would be one of the features I would *LOVE* to see in 
> SQLite :-)

SQLite has had geospatial support for years via the r-tree extension, and more 
recently GeoJSON.

As for time stamps ... I’ve been using SQLite since 2005 and have never felt 
the need to have it do more with dates than store a numeric timestamp in 
seconds-since-Unix-epoch. I have access to a lot of powerful platform APIs to 
do stuff with dates, so I don’t feel a need to have the database do similar 
things for me.

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

Re: [sqlite] Roadmap?

[J. King]

On October 26, 2019 8:07:57 p.m. EDT, Thomas Kurz  
wrote:

>To me, the most puzzling thing is the lack of full ALTER TABLE support
>(DROP COLUMN, MODIFY COLUMN, ADD CONSTRAINT, DROP CONSTRAINT).
>Modifying tables is some kind of science in SQLite, and thus, very
>error-prone. 

I'd second this (that altering the schema is error-prone). I'm not puzzled by 
SQLite's omission of most table altering (a good designer will choose a good 
schema from the start and thus rarely need to change it, making schema 
alteration a bit more of a niche feature), but it has been a point of 
difficulty in getting people used to other databases to take SQLite seriously. 

Earlier this year I spent some time implementing support for SQLite in Movim, a 
Web-based XMPP client and social media platform. All went smoothly until it was 
discovered that neither database schema migration library Movim uses actually 
correctly handles SQLite (despite both claiming to do so). I started fixing up 
one of them (Phinx), but got bogged down half-way because, of course, the whole 
thing assumes the database is capable of arbitrary ALTER TABLE statements. 

For now Movim no longer supports SQLite, despite there being demand for it, for 
want of good tooling to perform schema alterations. 


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

Re: [sqlite] Roadmap?

[Thomas Kurz]

> I suspect you are used to database servers, and haven’t used SQLite as an 
> embedded library inside an app

Yes and no ;-)

I have used database servers, and I am currently (for about 2 years) using (and 
appreciating!) SQLite library.

> Full text search is very common

Yes, of course. I didn't mean to deny that. I was just wondering why it's got 
priority over SQL-standards (because, as far as I know, but I might be wrong 
here, FTS is not part of one the SQL-standards).

> You didn’t mention geo-queries

Geospatial support would be one of the features I would *LOVE* to see in SQLite 
:-)

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

Re: [sqlite] Roadmap?

[Thomas Kurz]

> Feel free to make suggestions. Which missing feature or features causes 
you the most bother?

Thanks, Dan.

To me, the most puzzling thing is the lack of full ALTER TABLE support (DROP 
COLUMN, MODIFY COLUMN, ADD CONSTRAINT, DROP CONSTRAINT). Modifying tables is 
some kind of science in SQLite, and thus, very error-prone. I'd be willing to 
donate for that but as a private user I cannot affort 85 k$ ;-)

If you are collecting suggestions, here's some ideas:

- RIGHT JOIN
- Time periods, temporal referential integrity, temporal predicates from 
SQL:2011
- native geospatial support (storage using well-known binary representation 
from Open Geospatial Consortium); I know there's Spatialite, but there are 
massive bugs in Spatialite that imho arise only due to the lack of basic native 
geo support

Some non-standard but very useful behaviors from other RDBMs:

- ON UPDATE CURRENT_TIMESTAMP (from MySQL)
- SHOW TABLES, SHOW COLUMNS, etc. (from MySQL)
- RETURNING (from Postgres)

(I have left out some things I know about that they have already been discussed 
recently, like DATETIME.)

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

Re: [sqlite] Roadmap?

[Thomas Kurz]

> The features you name don't take away from the "liteness", they are all quite 
small and useful.

Yes of course they are useful, I wouldn't deny that. But they are prioritized 
over SQL-basics, that's what I'm confused about.


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

Re: [sqlite] Roadmap?

[Richard Damon]

On 10/20/19 11:03 PM, Rowan Worth wrote:
> On Sun, 20 Oct 2019 at 17:04, Simon Slavin  wrote:
>
>> Another common request is full support for Unicode (searching, sorting,
>> length()).  But even just the tables required to identify character
>> boundaries are huge.
>>
> Nitpick: there are no tables required to identify character boundaries. For
> utf-8 you know if there's another byte to come which is part of the current
> codepoint based on whether the current byte's high bit is set, and
> furthermore you know how many bytes to expect based on the initial byte.
>
> I'm less familiar with utf-16 which SQLite has some support for, but a
> quick read suggests there are exactly two reserved bit patterns you need to
> care about to identify surrogate pairs and thus codepoint boundaries.
>
> Tables relating to collation order, character case, and similar codepoint
> data can of course get huge, so your point stands.
> -Rowan

My memory is that Unicode is somewhat careful NOT to define what is a
'character' because that can really get complicated, and often
application specific about what it wants.

You have code-units, which for utf-8 are basically bytes.

You have code-points, which is what most people think of as a
'character' which has a single Unicode Codepoint number.

Then you have Graphemes, which are clusters of code-points that tend to
be expressed in a single glyph in output. (and some code-points don't
generate any output).

Dealing with Graphemes gets complicated, and that is where you run into
the need for lots of tables. Code-points them selves are fairly simple
to deal with, the problem is that in some langauges just dealing with
code-points doesn't let you fully handle some of the 'simple' operations
like sorting, or case folding with 100% accuracy, that sometimes
requires dealing with code-point clusters.

But, you also run into the issue (as I understand it) that Unicode
doesn't really define a universal ordering for all characters, that this
can be a language specific problem, and Unicode can't really solve that
issue. (Two langauges might use some of the same characters, but treat
them differently for sorting).

-- 
Richard Damon

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

Re: [sqlite] Roadmap?

[Warren Young]

On Oct 20, 2019, at 9:20 PM, Darren Duncan  wrote:
> 
> Rowan, you're talking about Unicode codepoints; however, Unicode graphemes, 
> what typical humans consider to be characters, are sequences of 1..N 
> codepoints, example a letter plus an accent that get composed together, and 
> this is what takes those large tables; this is related to Unicode Normal 
> Forms, eg NFD vs NFC, and its not about codepoint encodings like UTF-8 vs 
> UTF-16 etc. -- Darren Duncan

+1.  strlen() and character indexing on UTF-8 text is nontrivial:

https://stackoverflow.com/q/6162484/142454

Points 10, 27, 46, 47, and 48 under “Assume Brokenness” are relevant here.

If you’re not seeing that Perl’s handling of Unicode and thus that the accepted 
answer by one of Perl’s best practitioners matters here on the SQLite mailing 
list (which is to say, not Perl) the point is that Perl’s implementation of 
Unicode is one of the best available among the set of languages designed before 
Unicode became popular, so if you want a model to learn lessons from, it’s one 
of the best you could pick.

If you read this answer and don’t learn anything, you’re either too ignorant to 
understand what you’ve read and need to shore up the basics first, too arrogant 
to learn, or one of a very small number of people who truly understand Unicode. 
 I re-learn something every time I read this answer, because I’m not so deeply 
steeped in Unicode arcana to retain it all long-term.

And it’s a *summary*!  More weirdness awaits!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Roadmap?

[Darren Duncan]

Rowan, you're talking about Unicode codepoints; however, Unicode graphemes, what 
typical humans consider to be characters, are sequences of 1..N codepoints, 
example a letter plus an accent that get composed together, and this is what 
takes those large tables; this is related to Unicode Normal Forms, eg NFD vs 
NFC, and its not about codepoint encodings like UTF-8 vs UTF-16 etc. -- Darren 
Duncan


On 2019-10-20 8:03 p.m., Rowan Worth wrote:

On Sun, 20 Oct 2019 at 17:04, Simon Slavin  wrote:


Another common request is full support for Unicode (searching, sorting,
length()).  But even just the tables required to identify character
boundaries are huge.


Nitpick: there are no tables required to identify character boundaries. For
utf-8 you know if there's another byte to come which is part of the current
codepoint based on whether the current byte's high bit is set, and
furthermore you know how many bytes to expect based on the initial byte.

I'm less familiar with utf-16 which SQLite has some support for, but a
quick read suggests there are exactly two reserved bit patterns you need to
care about to identify surrogate pairs and thus codepoint boundaries.

Tables relating to collation order, character case, and similar codepoint
data can of course get huge, so your point stands.
-Rowan

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

Re: [sqlite] Roadmap?

[Rowan Worth]

On Sun, 20 Oct 2019 at 17:04, Simon Slavin  wrote:

> Another common request is full support for Unicode (searching, sorting,
> length()).  But even just the tables required to identify character
> boundaries are huge.
>

Nitpick: there are no tables required to identify character boundaries. For
utf-8 you know if there's another byte to come which is part of the current
codepoint based on whether the current byte's high bit is set, and
furthermore you know how many bytes to expect based on the initial byte.

I'm less familiar with utf-16 which SQLite has some support for, but a
quick read suggests there are exactly two reserved bit patterns you need to
care about to identify surrogate pairs and thus codepoint boundaries.

Tables relating to collation order, character case, and similar codepoint
data can of course get huge, so your point stands.
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Roadmap?

[Jens Alfke]


> On Oct 20, 2019, at 12:53 AM, Thomas Kurz  wrote:
> 
> many "playground" gadgets keep being implemented (like virtual columns, 
> virtual tables, FTS3/4/5, ...),

I suspect you are used to database servers, and haven’t used SQLite as an 
embedded library inside an app (its primary use case.) Virtual tables and 
table-valued functions are extremely useful there, as are a bunch of other 
features that probably seem like “playground” to you, like C functions and 
pointer types. The product I work on would be impossible without these.

As for FTS, I can’t see how anyone would consider it frivolous! Full text 
search is very common, something many apps with structured storage eventually 
need, and found in a lot of big database servers. (You didn’t mention 
geo-queries, but the same goes for those. And for obvious reasons they’re 
especially good to have in mobile apps.)

I’m curious which extra SQL features you think should be added? And what your 
use case is?

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

Re: [sqlite] Roadmap?

[Dan Kennedy]

On 20/10/62 14:53, Thomas Kurz wrote:

I'd kindly ask whether there is some sort of roadmap for SQLite development?

Someone recently pointed out how much he loves the "lite" and well-thought features. I cannot see 
that: I observe that many "playground" gadgets keep being implemented (like virtual columns, 
virtual tables, FTS3/4/5, ...), where one might wonder about their relationship to "Liteness",
whereas other features, essential basics of the SQL standards, are still 
missing and there is no indication they are to be added.


Feel free to make suggestions. Which missing feature or features causes 
you the most bother?


Dan.


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

Re: [sqlite] Roadmap?

[Simon Slavin]

On 20 Oct 2019, at 8:53am, Thomas Kurz  wrote:

> I'd kindly ask whether there is some sort of roadmap for SQLite development?

Only private to the developers, probably just mentioning whatever they're 
worried about at the moment.  Nothing public.

> Someone recently pointed out how much he loves the "lite" and well-thought 
> features. I cannot see that: I observe that many "playground" gadgets keep 
> being implemented (like virtual columns, virtual tables, FTS3/4/5, ...), 
> where one might wonder about their relationship to "Liteness", whereas other 
> features, essential basics of the SQL standards, are still missing and there 
> is no indication they are to be added.

It's worth remembering that almost all of the literally billions of 
installations of SQLite are in mobile phones or embedded controllers inside 
single-purpose machinery (e.g. your TV recorder, a crane balancing management 
system, a car-park ticketing system).  These devices don't need complete 
implementations of SQL, they need fast response, low memory use, and low power 
use.  That's why SQLite stays 'Lite'.

You are never going to see a full implementation of SQL in SQLite.  Here's why: 
the SQL standard is set out in ISO/IEC 9075-1:2016.  It has 9 parts, and over 
half a megabyte of text.  It's copyrighted, huge, difficult to understand, and 
almost nobody cares about more than three chapters of it.  One copy costs 
around €162.  Which would arguably be wasted since it gets reviewed and changed 
every two or three years, including right now.

But you – yes, you ! – can get your features included in SQLite.  Just join the 
SQLite consortium for US$85K/year.



Those are the people who pay for SQLite.  They get personal support and their 
own requirements catered for, up to and including new features, instant bug 
fixes, code examples and customised test suites for their chosen compilation 
options.  The recent addition of support for the "WITH" construction was added 
because a consortium member requested a way to implement something, and that's 
how SQL does it.

Alternatively make a suggestion that

(a) lots of people want
(b) will be simple to explain in the documentation
(c) doesn't require significant work
(d) doesn't make the source code or object code much bigger
(e) won't break backward compatibility
(f) doesn't add significantly to the test suite
(g) doesn't violate the SQL way of doing things
(h) the developers like

(I think that's everything I've seen discussed here.)  For example, BOOLEAN 
affinity has been frequently suggested, but the developers said adding tests 
for BOOLEAN type values to the test suite would be a lot of work, and make it 
take far longer to run because everything that handles values would have to be 
tested with BOOLEAN values.  Another common request is full support for Unicode 
(searching, sorting, length()).  But even just the tables required to identify 
character boundaries are huge.

You are, of course, free to fork SQLite and add in all the features you want.

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

Re: [sqlite] Roadmap?

[Darren Duncan]

On 2019-10-20 12:53 a.m., Thomas Kurz wrote:

I'd kindly ask whether there is some sort of roadmap for SQLite development?

Someone recently pointed out how much he loves the "lite" and well-thought features. I cannot see 
that: I observe that many "playground" gadgets keep being implemented (like virtual columns, 
virtual tables, FTS3/4/5, ...), where one might wonder about their relationship to "Liteness", 
whereas other features, essential basics of the SQL standards, are still missing and there is no indication 
they are to be added.

Without wanting to offend someone, I cannot see the logic in development, so: 
Is there some kind of roadmap?


The features you name don't take away from the "liteness", they are all quite 
small and useful.


The main thing that provides the liteness is that SQLite is a single-user DBMS 
implemented as an embedded library, in contrast to being a server with multiple 
concurrent users.


What are the main missing "essential basics of the SQL standards" that you think 
can be added without compromising the "liteness"?


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

[sqlite] Roadmap?

[Thomas Kurz]

I'd kindly ask whether there is some sort of roadmap for SQLite development?

Someone recently pointed out how much he loves the "lite" and well-thought 
features. I cannot see that: I observe that many "playground" gadgets keep 
being implemented (like virtual columns, virtual tables, FTS3/4/5, ...), where 
one might wonder about their relationship to "Liteness", whereas other 
features, essential basics of the SQL standards, are still missing and there is 
no indication they are to be added.

Without wanting to offend someone, I cannot see the logic in development, so: 
Is there some kind of roadmap?

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

Re: [sqlite] Roadmap for SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Shaun Seckman (Firaxis) wrote:
> The SQLite Consortium membership would be great but so far this
> newsgroup has answered every single one of my questions within a 2 hour
> window of posting..for free!  

The consortium membership is not about answering mailing list questions.  It
makes the SQLite team become virtual members of your development team.
Buying support is in the same kind of vein but to a lesser extent.

> So SQLite
> development is purely guided by requests from consortium members? 

No, they just get highest priority.  Look carefully at the links to get the
full list of benefits.  Think of scenarios like hitting a critical bug two
days before a product release and not being able to tell if it is your code
or SQLite.  Or having a new feature come out in a new SQLite version and
wanting it backported to an older version.  Or desperately needing some
feature/functionality right now because some other component has gone over
CPU/memory/disk bandwidth budgets.  Or maybe you want the test suites ported
to some new platform.

> As for buying the extension, I remember seeing it in the list
> of professional support a few months ago but it currently isn't listed
> anymore.

Contact them - HWACI is DRH's company - http://www.hwaci.com/contact.html

> Another potential feature that could benefit my usage and possibly
> others is if prepared statements could use memory allocated from the
> stack rather than the heap

That sounds rather tricky unless it is stack higher up in the call chain.
Again I'm sure DRH can discuss that with you.

> if memory allocated for one statement
> could be reused for another one (assuming that previous statement has
> been completely finalized).

All memory from a statement is freed when you finalize.  Do you mean when
you reset?

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrveJcACgkQmOOfHg372QQgJwCgqJJY/n5e5KQe999VZj6TGQ97
F+sAnRg0krgfDqGq7t6x5+5jG/30ofCJ
=kOVo
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Roadmap for SQLite

[P Kishor]

On Mon, Nov 2, 2009 at 5:26 PM, Scott Hess  wrote:
> And always remember that it's cheap to ask
> - maybe the feature is in there, you just mis-understood something

Whereby "it's cheap to ask" implies that it is "inexpensive to ask"
and not "to ask is cowardly, impolite or deserving of contempt."

Very sound advice.


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Roadmap for SQLite

[Scott Hess]

On Mon, Nov 2, 2009 at 3:56 PM, Shaun Seckman (Firaxis)
 wrote:
> The SQLite Consortium membership would be great but so far this
> newsgroup has answered every single one of my questions within a 2 hour
> window of posting..for free!  (you guys frickin rock.)  So SQLite
> development is purely guided by requests from consortium members?  There
> aren't any planned features that the developers wish to include just for
> maintenance or to add to already amazing feature set and polish of
> SQLite?

Speaking from past experience with open-source projects, often enough
if you have an explicit roadmap, people continually pester you with
messages like "Is it done yet?" and "Why isn't it done, yet?" and "My
gosh, how long could it possibly take you to implement such a trivial
feature?".  Also, it discourages outside developers from doing it
themselves and submitting patches.  If your project has a really big
development team, you need such a roadmap just to keep everyone
somewhat on-topic, but I think the set of core SQLite developers isn't
large enough to require that level of formality.

Realistically, if SQLite doesn't have the features you need right now,
it's probably risky to assume that it will acquire the features before
you need them.  Either budget to work around the lacking features, or
budget to implement them.  [And always remember that it's cheap to ask
- maybe the feature is in there, you just mis-understood something.]

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

Re: [sqlite] Roadmap for SQLite

[Shaun Seckman (Firaxis)]

The SQLite Consortium membership would be great but so far this
newsgroup has answered every single one of my questions within a 2 hour
window of posting..for free!  (you guys frickin rock.)  So SQLite
development is purely guided by requests from consortium members?  There
aren't any planned features that the developers wish to include just for
maintenance or to add to already amazing feature set and polish of
SQLite?  As for buying the extension, I remember seeing it in the list
of professional support a few months ago but it currently isn't listed
anymore.

The limitations of the prepared byte code you mention are pretty
acceptable in our use case.  As you mentioned, my main reasons for
wanting this sort of feature is to reduce memory consumption and cpu
time from preparing statements and as an added benefit to have a
singular point where all prepared statements are located and could be
tested prior to use (granted, this could be done already via some
architecture and build process tweaks to my project). As for the byte
code not working with future versions of SQLite, we typically generate
the database file on the fly the first time the application is run, then
load it from disk for all subsequent runs.  This cached database is
destroyed whenever a newer version of the exe is built (so new version
of SQLite would just require us to invalidate our cache and regenerate
it).  The schema changes are a bit of a drag but would be rare and in
that case any prepared statement that is invalidated by them could just
be re-prepared.

Another potential feature that could benefit my usage and possibly
others is if prepared statements could use memory allocated from the
stack rather than the heap or if memory allocated for one statement
could be reused for another one (assuming that previous statement has
been completely finalized).  I can understand this being very tricky to
implement since you must clearly define what items have the same
lifetime as the statement and which ones may be passed on later (ie.
strings).

It's not an absolute requirement for me right now though, just a nice to
have.  So far I've really been impressed with the performance of SQLite
and the ability to bind parameters to prepared statements has met many
of my needs.  In fact, the only major problem I've had with SQLite is
that the amount of polish has spoiled me and often makes me disgusted
when using other third party libraries which I won't name. (If only
other libraries had 100% branch coverage..)

-Shaun


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns
Sent: Monday, November 02, 2009 4:14 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Roadmap for SQLite

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Shaun Seckman (Firaxis) wrote:
> I'm really interested in finding out what sort of major features or
> tweaks are planned to be in upcoming releases (within the next 6
> months).  

You get what you ask for :-)  Have a look at these two pages:

http://www.sqlite.org/consortium.html
http://www.hwaci.com/sw/sqlite/prosupport.html

> I'm also hoping
> that storing prepared statements inside of a database is a planned
> feature as well since having a bunch of SQL queries embedded in my C++
> leaves me nervous :)

You can buy an extension that does that (as has been pointed out several
times on this list).  However what is wrong with storing the unprepared
SQL
queries?

The prepared queries are byte code for the SQLite virtual machine (aka
VDBE).  The generated byte code changes with each release (that is how
many
features are added and bugs fixed), so you wouldn't want to keep them
over
SQLite version changes.  The normal use case is for limited environments
where you don't want the memory consumption of the SQLite parser.  The
parsing is done ahead of time on the build workstation before putting
the
database containing them onto the embedded device.  Note that you also
can't
change the database schema in any way since that invalidates the
pre-generated byte code.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrvS4IACgkQmOOfHg372QTAEgCfRjO4Go5GrLeoB2Jj1as6sWwl
iI8An0Zinzh/Bt8X1GWMr4agWGSnTdGN
=vU0u
-END PGP SIGNATURE-
___
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] Roadmap for SQLite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Shaun Seckman (Firaxis) wrote:
> I'm really interested in finding out what sort of major features or
> tweaks are planned to be in upcoming releases (within the next 6
> months).  

You get what you ask for :-)  Have a look at these two pages:

http://www.sqlite.org/consortium.html
http://www.hwaci.com/sw/sqlite/prosupport.html

> I'm also hoping
> that storing prepared statements inside of a database is a planned
> feature as well since having a bunch of SQL queries embedded in my C++
> leaves me nervous :)

You can buy an extension that does that (as has been pointed out several
times on this list).  However what is wrong with storing the unprepared SQL
queries?

The prepared queries are byte code for the SQLite virtual machine (aka
VDBE).  The generated byte code changes with each release (that is how many
features are added and bugs fixed), so you wouldn't want to keep them over
SQLite version changes.  The normal use case is for limited environments
where you don't want the memory consumption of the SQLite parser.  The
parsing is done ahead of time on the build workstation before putting the
database containing them onto the embedded device.  Note that you also can't
change the database schema in any way since that invalidates the
pre-generated byte code.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrvS4IACgkQmOOfHg372QTAEgCfRjO4Go5GrLeoB2Jj1as6sWwl
iI8An0Zinzh/Bt8X1GWMr4agWGSnTdGN
=vU0u
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Roadmap for SQLite

[Shaun Seckman (Firaxis)]

Hello,

Is there any sort of development road map for SQLite?
I'm really interested in finding out what sort of major features or
tweaks are planned to be in upcoming releases (within the next 6
months).  The foreign key constraint feature came as a pleasant surprise
but ended up invalidating a good chunk of code written to work around
the lack of checking (lots of trigger generation code).  I'm also hoping
that storing prepared statements inside of a database is a planned
feature as well since having a bunch of SQL queries embedded in my C++
leaves me nervous :)

 

-Shaun

 

 

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

[sqlite] SQLite roadmap?

[Samuel R. Neff]

Is there a roadmap of major planned features in upcoming releases?  I didn't
see anything on the wiki or site..

Thanks,

Sam 


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Darren Duncan [mailto:[EMAIL PROTECTED] 
Sent: Thursday, August 02, 2007 8:44 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Implementation of ANSI SQL-92 FOREIGN KEY and
referential integrity

At 2:11 PM -0700 8/2/07, Mikey C wrote:
>Does anyone know if there is a plan to implement the enforcement of the
>SQL-92 FOREIGN KEY constraints?
>
>Seems to me the No.1 missing feature.  After all, data integrity, even in
an
>embedded DB is very important and bugs in client code can easily mess up
the
>referential integrity.

I believe that this is planned; however, some other planned upates 
have higher priority and are being done first.  Note also that 
traditional foreign key constraints can only implement some kinds of 
business rules, and there are lots of others whose violation could 
cause problems, that foreign keys wouldn't help with; in theory, the 
more generic format of triggers is a more complete solution, or more 
specifically, free-form check constraints that can be comprised of 
any query are a more complete solution. -- Darren Duncan


-
To unsubscribe, send email to [EMAIL PROTECTED]
-