whish list for 2016

classic Classic list List threaded Threaded
73 messages Options
1234
Reply | Threaded
Open this post in threaded view
|

whish list for 2016

big stone
Hi All,

To prepare for 2016 greetings moment, here is my personnal whish list
for sqlite:
- analytic functions (would fit the split/apply/combine data-science landscape),
- "generate_series" extension included in default sqlite.exe and
sqlite.dll for windows.

Best whishes for 2016!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

Domingo Alvarez Duarte-2
Let's add another one:  

- replace hard coded floating point type "double" by "sqlite3_double" to
allow user define alternatives like "_Decimal64" (see
https://github.com/mingodad/squilu/tree/master/SquiLu-ext sqlite3.c sqlite3.h
with "-DSQLITE_USE_DECIMAL")  

Best whishes for 2016!  

>  Sun Dec 20 2015 7:12:39 pm CET CET from "Big Stone"
><[hidden email]>  Subject: [sqlite] whish list for 2016
>
>  Hi All,
>
> To prepare for 2016 greetings moment, here is my personnal whish list
> for sqlite:
> - analytic functions (would fit the split/apply/combine data-science
>landscape),
> - "generate_series" extension included in default sqlite.exe and
> sqlite.dll for windows.
>
> Best whishes for 2016!
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>  

 

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

Petite Abeille-2
In reply to this post by big stone

> On Dec 20, 2015, at 7:12 PM, Big Stone <[hidden email]> wrote:
>
> To prepare for 2016 greetings moment, here is my personnal whish list
> for sqlite:

MERGE statement! Yeah! :)

https://en.wikipedia.org/wiki/Merge_(SQL)

 


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

Edward Lau
In reply to this post by big stone
A vote from me for OLAP analytical functions.



-----Original Message-----
From: Big Stone <[hidden email]>
To: sqlite-users <[hidden email]>
Sent: Sun, Dec 20, 2015 10:12 am
Subject: [sqlite] whish list for 2016

Hi All,

To prepare for 2016 greetings moment, here is my personnal whish list
for sqlite:
- analytic functions (would fit the split/apply/combine data-science landscape),
- "generate_series" extension included in default sqlite.exe and
sqlite.dll for windows.

Best whishes for 2016!
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

R Smith

PRAGMA strict_mode = 1;

:)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

Darren Duncan
In reply to this post by Petite Abeille-2
On 2015-12-20 10:25 AM, Petite Abeille wrote:
>> On Dec 20, 2015, at 7:12 PM, Big Stone <[hidden email]> wrote:
>>
>> To prepare for 2016 greetings moment, here is my personnal whish list
>> for sqlite:
>
> MERGE statement! Yeah! :)
>
> https://en.wikipedia.org/wiki/Merge_(SQL)

If you want that feature, instead do it the better way that Postgres 9.5 did,
which is as an extension to the INSERT statement in the form "ON CONFLICT DO
UPDATE/IGNORE".

Relevant url:

https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5#INSERT_..._ON_CONFLICT_DO_NOTHING.2FUPDATE_.28.22UPSERT.22.29

Example:

  INSERT INTO user_logins (username, logins)
  VALUES ('Naomi',1),('James',1)
  ON CONFLICT (username)
  DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;

-- Darren Duncan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

Petite Abeille-2

> On Dec 21, 2015, at 4:08 AM, Darren Duncan <[hidden email]> wrote:
>
> If you want that feature, instead do it the better way that Postgres 9.5 did, which is as an extension to the INSERT statement in the form "ON CONFLICT DO UPDATE/IGNORE”.

Please, enough of the comic act :P

MERGE it is.

Oh, yes, also, +1 for for Mr Smith PRAGMA strict_mode = 1; :))
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

Darren Duncan
On 2015-12-21 8:25 AM, Petite Abeille wrote:
>> On Dec 21, 2015, at 4:08 AM, Darren Duncan <[hidden email]> wrote:
>>
>> If you want that feature, instead do it the better way that Postgres 9.5 did, which is as an extension to the INSERT statement in the form "ON CONFLICT DO UPDATE/IGNORE”.
>
> Please, enough of the comic act :P
>
> MERGE it is.

Comic act?  Do you consider the MERGE defined in the SQL standard to be a better
designed feature than Postgres' alternative, or do you prefer the former soley
because it is in the SQL standard?  I recall that Postgres went with their
brand-new alternative because it was a better design, more useable in practice;
both simpler and more powerful, easier to express the user's intent and not have
arbitrary limitations. -- Darren Duncan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

Petite Abeille-2

> On Dec 21, 2015, at 5:43 PM, Darren Duncan <[hidden email]> wrote:
>
> Comic act?  Do you consider the MERGE defined in the SQL standard to be a better designed feature than Postgres' alternative,

Yes.

> or do you prefer the former soley because it is in the SQL standard?

Yes.

>  I recall that Postgres went with their brand-new alternative because it was a better design, more useable in practice; both simpler and more powerful, easier to express the user's intent and not have arbitrary limitations.

No.

But lets agree to disagree in the spirit of the Holiday Season! :)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

lchishol
In reply to this post by big stone
Joining the throng, here are my requests:
a) Either an ORDER BY clause/equivalent for GROUP BY, or an assurance that the
kludge of sorting a sub-query first and then grouping the result does and will
continue to work - I need this functionality.
b) A separate mailing address for documentation corrections - I see so many
typos but it doesn't seem worth spamming the whole list to request an apostrophe
be added or two letters be exchanged.
c) A vote for RANK, but I'm doing that in my application at present, post-SQL
but before printing. I know you can generate 1,2,3=,3=,5 type sequences from
self-joins but it seems a lot easier to do it in Delphi!

BTW I am having a great deal of enjoyment with SQLite in my application, which
is a scoring database for card tournaments in New Zealand. I know it's small
compared to some of the monsters I've seen described here, but I have 38 tables
and 54 views in my schema, with 400-ish players and over 2800 score records in
the national euchre tournament DB.

Len Chisholm.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

Darren Duncan
On 2015-12-21 3:46 PM, [hidden email] wrote:
> Joining the throng, here are my requests:
> a) Either an ORDER BY clause/equivalent for GROUP BY, or an assurance that the
> kludge of sorting a sub-query first and then grouping the result does and will
> continue to work - I need this functionality.

You need what to work exactly?  SQL tables are unordered by definition, and
ORDER BY is more of a display attribute at the end.  If you want to do something
order-sensitive in the middle of a query then RANK is the proper generalized way
to do it that SQL provides, that or, where applicable, order-insensitive
aggregates like min/max/etc which still tell you what value you'd get first if
you sorted a list without actually sorting it.

> c) A vote for RANK, but I'm doing that in my application at present, post-SQL
> but before printing. I know you can generate 1,2,3=,3=,5 type sequences from
> self-joins but it seems a lot easier to do it in Delphi!

I agree with adding RANK, it is very useful.

-- Darren Duncan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

lchishol
In reply to this post by big stone
Hi all,

What I meant to ask was:
a) Either an ORDER BY clause/equivalent for group_concat() [not GROUP BY as
originally posted], or an assurance that the kludge of sorting a sub-query first
and then grouping the result does and will continue to work - I need this
functionality.

This is what happens when you post when tired! My thanks to Darren Duncan who
questioned what I was asking for. Sorry I can't reply directly to Darren's
message as I get the list via digest.

I am using group_concat() to form lists of team members in a single report
field, and wish to control the order of the items concatenated. The
documentation explicitly states that "The order of the concatenated elements is
arbitrary", but searching the internet finds a number of answers along the lines of:
select a, group_concat(b) from (select a, b from db order by a, b desc) order by a;
where the ordering of the sub-select "b desc" is preserved in the group_concat()
result. This seems to work in practice, but I would really like to have surety
on a way to achieve this other than doing it in my application; other SQL
implementations have this feature.

Thanks, Len Chisholm.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

gunnar
In reply to this post by lchishol
I would like a less limited 'alter table' statement, to be able to drop
columns and to add columns at a position of my own choice instead of
always at the end.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

John McKown
On Thu, Dec 24, 2015 at 6:49 AM, gunnar <[hidden email]> wrote:

> I would like a less limited 'alter table' statement, to be able to drop
> columns and to add columns at a position of my own choice instead of always
> at the end.
>
>
​I'm curious as to why. Doing so would, most likely, require rewriting the
entire table. If you want a SELECT * to get the columns in a particular
order, just create a VIEW with the columns in the order in which you want
them.

The SQL standard, I'm fairly sure, doesn't even specify the "natural order"
of the column returned in a "SELECT *". Of course, they will likely always
be return in the "natural" order. But that depends on how the back end is
programmed. I could see a vendor (as unlikely as it would be) deciding to
return the column in a "SELECT *" in lexicographical order based on the
server's code page, or the table's default code page, or perhaps even in
the client's code page.

But then, I admit that I am "anal" about my SELECT statements; at least
when embedded in a program. IMO, "SELECT *" is a very bad idea in any
programming language. As is making any assumption about the order of rows
returned when an ORDER BY is not specified. "Assuming _nothing_, other than
the worst." is my programming motto. Or the Russian: "Trust, but verify!".
Especially if it is coming in from "meatware" (people), or some other
organization. We have a process at work which consistently blows up because
the end user sends us junk. E.g. the cost is q.97 dollars, instead of 1.97
(q is below 1 and user is typing fast).

===
Hoping you have a nice Christmas, Hanukkah, Fetivus, Kwanza, or at least a
3 day week end.


--
Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

Bernardo Sulzbach
On Thu, Dec 24, 2015 at 1:12 PM, John McKown
<[hidden email]> wrote:

>
> On Thu, Dec 24, 2015 at 6:49 AM, gunnar <[hidden email]> wrote:
>
> > I would like a less limited 'alter table' statement, to be able to drop
> > columns and to add columns at a position of my own choice instead of always
> > at the end.
> >
> >
> I'm curious as to why. Doing so would, most likely, require rewriting the
> entire table. If you want a SELECT * to get the columns in a particular
> order, just create a VIEW with the columns in the order in which you want
> them.
>

I think you focused too much on the ordering issue. He or she may just
want a more versatile alter table for convenience. However, I don't
know if alter table is used at all in production anywhere (why would
it be? the column names and ordering should not be part of the data).
They are useful for prototyping because by having a stronger alter
table command you don't need to drop and create so many times. In the
end, if you spent enough time in the design phase to prepare all your
schemas, you should not have to drop (or alter) any of the tables at
all.

It is a feature, a nice one, but too far from necessary (from my
standpoint) to be worthy of the developers' time.



--
Bernardo Sulzbach
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

Simon Slavin-3
In reply to this post by John McKown

On 24 Dec 2015, at 3:12pm, John McKown <[hidden email]> wrote:

> ​I'm curious as to why. Doing so would, most likely, require rewriting the
> entire table. If you want a SELECT * to get the columns in a particular
> order, just create a VIEW with the columns in the order in which you want
> them.

I think Gunnar just wants forms such as

ALTER TABLE CREATE COLUMN ...
ALTER TABLE DROP COLUMN ...

to aid with making small changes to the schema.  These are available in most SQL engines but the way SQLite3 is written makes it difficult or inefficient to implement them.

I have a database where one table takes up more than 30 Gigabytes of space.  While developing the software I needed several times to change a column definition and since SQLite lacks these facilities I had to move 30 Gig of data around every time I did it.  Annoying.  But it's not normally that much of a problem for me.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

John McKown
On Thu, Dec 24, 2015 at 10:14 AM, Simon Slavin <[hidden email]> wrote:

>
> On 24 Dec 2015, at 3:12pm, John McKown <[hidden email]>
> wrote:
>
> > ​I'm curious as to why. Doing so would, most likely, require rewriting
> the
> > entire table. If you want a SELECT * to get the columns in a particular
> > order, just create a VIEW with the columns in the order in which you want
> > them.
>
> I think Gunnar just wants forms such as
>
> ALTER TABLE CREATE COLUMN ...
> ALTER TABLE DROP COLUMN ...
>
> to aid with making small changes to the schema.  These are available in
> most SQL engines but the way SQLite3 is written makes it difficult or
> inefficient to implement them.
>
> I have a database where one table takes up more than 30 Gigabytes of
> space.  While developing the software I needed several times to change a
> column definition and since SQLite lacks these facilities I had to move 30
> Gig of data around every time I did it.  Annoying.  But it's not normally
> that much of a problem for me.
>
> Simon.
>
>
​I did overlook the DROP COLUMN request. I guess I got "shocked" by the OP
wanting something (I think) like: ALTER TABLE ADD COLUMN newcol TEXT AFTER
oldcol. Where "oldcol" is an existing column name which is not the "last"
one. I sometimes have an unusual take on things due to having read about
relational algebra _before_ doing SQL work. So I think of tables as
relationships, as a "set" for "attributes" which have no inherent order.
Read a bit too much by Dr. Codd. Oh, an Joe Celko too, for that matter. And
_no_ actual professional experience. Makes me a bit of a theoretician.

It really would be nice to be able to have a column defined as, say
VARCHAR(20) to be "redefined" as TEXT or VARCHAR(n) (where n>=20) with a
simple ALTER. That would be a simple change to the schema with no data
alteration. If one allowed to change a VARCHAR new length to be _less_ than
the old length, then it would be necessary to verify that all current rows
were still compliant with the new length. In that case, I guess it would be
"best" if the back end were do to the equivalent of a DELETE and ADD on the
now-invalid data, truncating the larger value to its new max size. That
would save some I/O by not rewriting compliant rows. I don't see any way to
avoid I/O if you want to change an INTEGER (1,2,3,4,6 or 8 bytes) to a
FLOAT​ (always 8 bytes). You'd need to rewrite every row, either "in place"
(8 byte INTEGER to FLOAT) or with a DELETE / ADD to the "end". I would
really need to examine the internals to see how much I/O this might be.

I'm not aware of any RDMS which allows someone to alter the "type" (e.g.
INTEGER to FLOAT) of an existing column. What I have done, in PostgreSQL,
is something like:

ALTER TABLE table ADD COLUMN new-column FLOAT;
UPDATE table SET new-column=old-column;
ALTER TABLE table DROP COLUMN old-column;
UPDATE table ALTER COLUMN new-column RENAME TO old-name;

But the above would "move" those 30 Gig of data round, just more easily
from the standpoint of the user. What might be interesting in this type of
case would be a "column-oriented DBMS" (
https://en.wikipedia.org/wiki/Column-oriented_DBMS) Depending on how it was
implemented, it could be made to do the above operation easily. But that
would be a _major_ rewrite of SQLite internally. Hum, it could complicate
things, but this might be more easily possible if each column were placed
in a different OS file. When you add a new column, just create a new file
initialized with the same number of rows which contain NULL or the DEFAULT
value. When you drop a column, it would simply delete the row-containing
file. Maybe a VFS could be written to do this. But SQLite would need to be
enhanced to add the ALTER TABLE ... DROP COLUMN operation.



--
Computer Science is the only discipline in which we view adding a new wing
to a building as being maintenance -- Jim Horning

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

Richard Damon
In reply to this post by Bernardo Sulzbach
On 12/24/15 10:26 AM, Bernardo Sulzbach wrote:
> I think you focused too much on the ordering issue. He or she may  > just want a more versatile alter table for convenience. However, I >
don't know if alter table is used at all in production anywhere (why >
would it be? the column names and ordering should not be part of the >
data). They are useful for prototyping because by having a stronger >
alter table command you don't need to drop and create so many times. >
In the end, if you spent enough time in the design phase to prepare >
all your schemas, you should not have to drop (or alter) any of the >
tables at all. It is a feature, a nice one, but too far from > necessary
(from my standpoint) to be worthy of the developers' time. >

I am in the process of building an application that has the need to be
able to add columns to tables in response to user actions. It is
primarily in the stage where the user is customizing the program to
their needs, but such customizations might happen after the program has
been in use for awhile. The main case is to be able to add a 'Flag' to
records to allow the filter records or save the set of records found in
a search. For now, the current method of create new, drop and rename,
isn't unworkable (and mostly hidden in an abstraction layer), but being
able to directly add a field would be nice.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

Simon Slavin-3

On 24 Dec 2015, at 5:10pm, Richard Damon <[hidden email]> wrote:

> being able to directly add a field would be nice.

You can directly add a field.

<https://www.sqlite.org/lang_altertable.html>

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: whish list for 2016

Bernardo Sulzbach
In reply to this post by Richard Damon
On Thu, Dec 24, 2015 at 3:10 PM, Richard Damon <[hidden email]> wrote:

> On 12/24/15 10:26 AM, Bernardo Sulzbach wrote:
>>
>> I think you focused too much on the ordering issue. He or she may  > just
>> want a more versatile alter table for convenience. However, I >
>
> don't know if alter table is used at all in production anywhere (why > would
> it be? the column names and ordering should not be part of the > data). They
> are useful for prototyping because by having a stronger > alter table
> command you don't need to drop and create so many times. > In the end, if
> you spent enough time in the design phase to prepare > all your schemas, you
> should not have to drop (or alter) any of the > tables at all. It is a
> feature, a nice one, but too far from > necessary (from my standpoint) to be
> worthy of the developers' time. >
>
> I am in the process of building an application that has the need to be able
> to add columns to tables in response to user actions. It is primarily in the
> stage where the user is customizing the program to their needs, but such
> customizations might happen after the program has been in use for awhile.
> The main case is to be able to add a 'Flag' to records to allow the filter
> records or save the set of records found in a search. For now, the current
> method of create new, drop and rename, isn't unworkable (and mostly hidden
> in an abstraction layer), but being able to directly add a field would be
> nice.
>
> --
> Richard Damon
>
>

Interesting. I suppose that most of this "table editing" should rely
on an abstraction layer anyway: think about supporting other RDBMS in
the future. However, I recognize that in your case a more capable
alter table may simplify things a lot.

--
Bernardo Sulzbach
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
1234