[sqlite] whish list for 2016

> > DROP COLUMN and RENAME COLUMN are relatively easy for tables that lack > indexes, triggers, views, foreign key references, CHECK constraints, > and other constructs that might reference the dropped or renamed > column. Reliably finding every use of a column name and changing it > can be

[sqlite] whish list for 2016

On Tue, 12 Jan 2016 21:58:01 +0100 Christian Schmitz wrote: > > > Am 20.12.2015 um 19:12 schrieb Big Stone : > > > > Hi All, > > > > To prepare for 2016 greetings moment, here is my personnal whish > > list > > Unless I missed something, I may suggest > > * moveprev > * movefirst > *

[sqlite] whish list for 2016

At 08:28 13/01/2016, you wrote: >On Wed, Jan 13, 2016 at 2:39 AM, Simon Slavin >wrote: > > > On 12 Jan 2016, at 11:56pm, Scott Hess wrote: > > > > > If I am writing a client that can read SQLite databases, then I > probably > > > don't want your database to be injecting a bunch of arbitrary

[sqlite] whish list for 2016

On 13 Jan 2016, at 7:28am, Dominique Devienne wrote: > You have in my opinion taken this out of context, and are assuming the > important part is the application, and not the data (i.e. database file). I apologise. I didn't read back down the thread before replying. Sorry. Simon.

[sqlite] whish list for 2016

On Wed, Jan 13, 2016 at 2:39 AM, Simon Slavin wrote: > On 12 Jan 2016, at 11:56pm, Scott Hess wrote: > > > If I am writing a client that can read SQLite databases, then I probably > > don't want your database to be injecting a bunch of arbitrary PRAGMA > calls > > into my client. > > It is,

[sqlite] whish list for 2016

On Wed, Jan 13, 2016 at 12:42 AM, Jean-Christophe Deschamps < jcd at antichoc.net> wrote: > At 08:28 13/01/2016, you wrote: > >> On Wed, Jan 13, 2016 at 2:39 AM, Simon Slavin >> wrote: >> > On 12 Jan 2016, at 11:56pm, Scott Hess wrote: >> > > If I am writing a client that can read SQLite

[sqlite] whish list for 2016

On 12 Jan 2016, at 11:56pm, Scott Hess wrote: > If I am writing a client that can read SQLite databases, then I probably > don't want your database to be injecting a bunch of arbitrary PRAGMA calls > into my client. It is, after all, the equivalent of an autoexecute macro. And we all know how

[sqlite] whish list for 2016

> Am 20.12.2015 um 19:12 schrieb Big Stone : > > Hi All, > > To prepare for 2016 greetings moment, here is my personnal whish list Unless I missed something, I may suggest * moveprev * movefirst * movelast * move to record in cursor with given index Looping over a recordset twice is often

[sqlite] whish list for 2016

I think this is a very good feature to have, thumbs up ! ? > Tue Jan 12 2016 9:50:33 pm CET CET from "James K. Lowden" > Subject: Re: [sqlite] whish list for 2016 > > On Fri, 8 Jan 2016 08:28:29 +0100 > Dominique Devienne wrote: > > >> &g

[sqlite] whish list for 2016

On Tuesday, 12 January, 2016 13:58, Christian Schmitz said: > Unless I missed something, I may suggest > * moveprev > * movefirst > * movelast > * move to record in cursor with given index There are no relational database engines on the market that can do this. Those that *appear* to be

[sqlite] whish list for 2016

On Tuesday, 12 January, 2016 13:51, James K. Lowden said: > On Fri, 8 Jan 2016 08:28:29 +0100 > Dominique Devienne wrote: > > > One way to do that would be to honor a special user-created table, > > > say "PRAGMAS", with name-value pairs that are automatically applied > > > when the database

[sqlite] whish list for 2016

On 1/12/16, Christian Schmitz wrote: > > Unless I missed something, I may suggest > > * moveprev > * movefirst > * movelast > * move to record in cursor with given index Not possible, at least not in general. Something like this could be done for the simpliest queries against a single real

[sqlite] whish list for 2016

On Tue, Jan 12, 2016 at 3:43 PM, Keith Medcalf wrote: > On Tuesday, 12 January, 2016 13:51, James K. Lowden < > jklowden at schemamania.org> said: > > On Fri, 8 Jan 2016 08:28:29 +0100 > > Dominique Devienne wrote: > > > > One way to do that would be to honor a special user-created table, > > >

[sqlite] whish list for 2016

On Fri, 8 Jan 2016 08:28:29 +0100 Dominique Devienne wrote: > > One way to do that would be to honor a special user-created table, > > say "PRAGMAS", with name-value pairs that are automatically applied > > when the database is opened. > > > > Interesting idea. A "special"-to-SQLite

[sqlite] whish list for 2016

On Fri, Jan 8, 2016 at 12:14 AM, James K. Lowden wrote: > I would like to see a strict mode, too. +1 > I would also like to be able to make "strictness" a property of the > database, not the connection. > +1 similarly I'd like enforcing FKs to be per database, not per connection. > One way

[sqlite] whish list for 2016

On Sun, 20 Dec 2015 19:12:39 +0100 Big Stone wrote: > Best whishes for 2016! If it's not too late to join the party... 1. create table T (t primary key); update T set t = t+1; for consecutive values of t. 2. Specific constraint references in error messages. 3. Correct

[sqlite] whish list for 2016

Hi Scott, It is not such a big deal for us but since I saw the subject "whish list" I thought to add something that I was surprised that it isn't there in sqlite (since it is so complete in many other ways), but that i use on regular basis. I agree that SELECT * in production code is bad, but

[sqlite] whish list for 2016

Indeed those two possibilities! And perhaps also to MODIFY a column. We alter tables all the time :) The possibility to specify at which place an added column should appear when I do a "SELECT * ..." would be a nice extra, in MySQL it is done like: ALTER TABLE table_name ADD COLUMN col_name1

[sqlite] whish list for 2016

> On Tuesday, 5 January, 2016 08:33, gunnar said: > Regarding views, I don't know how sqlite handles them but I know that > they can drastically slow down queries in MySQL for example. While this is true in many cases, using a view rather than a select on the underlying table should not

[sqlite] whish list for 2016

Maybe one option would be to add a layer to affect that explicitly, so that instead of the problem being that the existing rows can't be reordered without re-writing the entire table, the problem is to just change the schema to indicate where the columns should appear in "SELECT *" statements.

[sqlite] whish list for 2016

On 2016/01/04 6:11 PM, Bernardo Sulzbach wrote: > On Mon, Jan 4, 2016 at 1:50 PM, Stephen Chrzanowski > wrote: >> I did the test to compare SSD vs 5200rpm and 7200rpm drives. I should >> throw this at my 7200rpm hybrid at this app and see what happens. >> > Could you please share any

[sqlite] whish list for 2016

Thanks a lot for this, Stephen. It will very likely help me make a decision in the future.

[sqlite] whish list for 2016

On 28 Dec 2015, at 10:13am, Simon Slavin wrote: > Well I have a 43 Gigabyte database at work. I bet it doesn't take more than > a few hours to check. But I can't do it from home so it'll have to wait > until I get back to work next week for me to test that theory. Got back to work today

[sqlite] whish list for 2016

On Mon, Jan 4, 2016 at 1:50 PM, Stephen Chrzanowski wrote: > > I did the test to compare SSD vs 5200rpm and 7200rpm drives. I should > throw this at my 7200rpm hybrid at this app and see what happens. > Could you please share any performance data you still have? Thank you. -- Bernardo

[sqlite] whish list for 2016

On Mon, Jan 4, 2016 at 12:28 PM, Simon Slavin wrote: > > That's 3 hours 23 minutes. For a 38 Gigabyte database including a table with > half a billion rows. > > Details: Running in the SQLite 3.8.5 shell tool on a four year old iMac with > a spinning rust storage system. VACUUM was running in

[sqlite] whish list for 2016

I'll see if I can get it to recompile over the next few days. It has been a couple years since I've even looked at it, and I've changed from Delphi to Free Pascal. The 5200 drive is out since I don't have IDE on this board, and I'm not about to go out and buy any Green HDD. ;) I'm also at work

[sqlite] whish list for 2016

I once faked a database, created random data, deleted random data, re-inserted random data of random sizes, got the database to a couple gigs in size, I noticed that the temp file SQLite made when running vacuum accounted for about the same size of what the actual database was. I guess I had the

[sqlite] whish list for 2016

I left the list some days and find this subject discussed and finished!! Happy New Year 2016 to every colister and sqlite developer. I add my list and a tiny explanation. - Lock tables/indexes on cache (make them unevictable) When a query use a big size table, other tables/index used in the

[sqlite] whish list for 2016

On Dec 24, 2015, at 7:49 PM, Simon Slavin wrote: > > What makes "ALTER TABLE table-name DROP ?COLUMN" hard is checking the schema > to make sure that nothing in the schema refers to the dropped column. Given that the current alternative to this feature is hand-rolled code like I gave earlier

[sqlite] whish list for 2016

On 12/25/15, Valentin Davydov wrote: > > It would be desirable to improve algorithm of INTEGRITY_CHECK pragma. > Presently it is generally useless for indexed databases which don't fit > entirely in RAM (and which usually need checking much strongly than > smaller ones). > For each row of each

[sqlite] whish list for 2016

On Fri, Dec 25, 2015 at 12:51:55PM +, Simon Slavin wrote: > > On 25 Dec 2015, at 12:39pm, Valentin Davydov > wrote: > > > It would be desirable to improve algorithm of INTEGRITY_CHECK pragma. > > Presently it is generally useless for indexed databases which don't fit > > entirely in RAM

[sqlite] whish list for 2016

On 28 Dec 2015, at 9:01am, Valentin Davydov wrote: > As far as I understand, INTEGRITY_CHECK simply iterates over the records > (of tables and indices) one by one in some arbitrary order. So, if the > database is too big to fit in the available memory (sqlite's own cache, > system file cache

[sqlite] whish list for 2016

On 2015-12-24 6:21 PM, Stephen Chrzanowski wrote: > One thing I would suggest, if you're looking to add and delete columns > dynamically is not to worry about the order of the columns in the database, > but, have a second table hanging around that remembers the specified order > the user wants to

[sqlite] whish list for 2016

Hi, All! It would be desirable to improve algorithm of INTEGRITY_CHECK pragma. Presently it is generally useless for indexed databases which don't fit entirely in RAM (and which usually need checking much strongly than smaller ones). Valentin Davydov.

[sqlite] whish list for 2016

> On Dec 25, 2015, at 12:51 PM, Stephen Chrzanowski > wrote: > > *Part 2;* > > More along with your application style, but a complete database schema > overhaul, think of a contact form that allows for multiple methods of > communication. Multiple email addresses, multiple phone or fax

[sqlite] whish list for 2016

On 25 Dec 2015, at 12:39pm, Valentin Davydov wrote: > It would be desirable to improve algorithm of INTEGRITY_CHECK pragma. > Presently it is generally useless for indexed databases which don't fit > entirely in RAM (and which usually need checking much strongly than > smaller ones). Valentin,

[sqlite] whish list for 2016

On 25 Dec 2015, at 2:24am, John McKown wrote: > DELETE is normally done as: ALTER TABLE table-name DROP ?COLUMN > column_file_name; and would be a very nice addition. I hadn't noticed that > it is missing. I wonder why. SQlite stores all the data for a row together in column order. col1,

[sqlite] whish list for 2016

On 25 Dec 2015, at 2:35am, Bernardo Sulzbach wrote: >> ALTER TABLE table-name RENAME COLUMN column_field_name TO >> new_column_field_name; > > Are you sure? The documentation does not have anything about this and > I get a syntax error using 3.9.2 (a bit outdated, I know). John's confused.

[sqlite] whish list for 2016

On Fri, Dec 25, 2015 at 12:24 AM, John McKown wrote: > On Thu, Dec 24, 2015 at 4:09 PM, Christian Schmitz < > realbasiclists at monkeybreadsoftware.de> wrote: > >> Hi, >> >> better ALTER command would be very welcome. >> >> e.g. RENAME/DELETE column or field. >> > > RENAME exists. > > ALTER TABLE

[sqlite] whish list for 2016

Hi, better ALTER command would be very welcome. e.g. RENAME/DELETE column or field. Instead of us writing code to do it, it could be a command where SQLite does things right. Sincerely Christian -- Read our blog about news on our plugins: http://www.mbsplugins.de/

[sqlite] whish list for 2016

On Thu, Dec 24, 2015 at 8:39 PM, Simon Slavin wrote: > > On 25 Dec 2015, at 2:35am, Bernardo Sulzbach > wrote: > > >> ALTER TABLE table-name RENAME COLUMN column_field_name TO > >> new_column_field_name; > > > > Are you sure? The documentation does not have anything about this and > > I get a

[sqlite] whish list for 2016

On 12/24/15, John McKown wrote: > > DELETE is normally done as: ALTER TABLE table-name DROP ?COLUMN > column_file_name; and would be a very nice addition. I hadn't noticed that > it is missing. I wonder why. Perhaps Dr. Hipp will comment after the > holidays. > Merry Christmas. DROP COLUMN and

[sqlite] whish list for 2016

Two parter; *Part 1;* One thing I would suggest, if you're looking to add and delete columns dynamically is not to worry about the order of the columns in the database, but, have a second table hanging around that remembers the specified order the user wants to see the columns in. Doing your

[sqlite] whish list for 2016

On Thu, Dec 24, 2015 at 4:09 PM, Christian Schmitz < realbasiclists at monkeybreadsoftware.de> wrote: > Hi, > > better ALTER command would be very welcome. > > e.g. RENAME/DELETE column or field. > ?RENAME exists.? ?ALTER TABLE table-name RENAME COLUMN column_field_name TO

[sqlite] whish list for 2016

On 24 Dec 2015, at 5:10pm, Richard Damon wrote: > being able to directly add a field would be nice. You can directly add a field. Simon.

[sqlite] whish list for 2016

On 24 Dec 2015, at 3:12pm, John McKown 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

[sqlite] whish list for 2016

On Thu, Dec 24, 2015 at 3:16 PM, Simon Slavin wrote: > > On 24 Dec 2015, at 5:10pm, Richard Damon wrote: > >> being able to directly add a field would be nice. > > You can directly add a field. > > > > Simon. Good catch, you cannot place it wherever

[sqlite] whish list for 2016

On Thu, Dec 24, 2015 at 3:10 PM, Richard Damon 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

[sqlite] whish list for 2016

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] whish list for 2016

On Thu, Dec 24, 2015 at 1:12 PM, John McKown wrote: > > On Thu, Dec 24, 2015 at 6:49 AM, gunnar 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

[sqlite] whish list for 2016

On Dec 24, 2015, at 11:17 AM, Warren Young wrote: > > BEGIN TRANSACTION; > ALTER TABLE Foo RENAME TO oldFoo; > CREATE TABLE Foo ? a bunch of repeated stuff >? almost identical to the initial >? DBMS creation code with just one >?

[sqlite] whish list for 2016

On 12/24/15 12:17 PM, Bernardo Sulzbach wrote: > 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

[sqlite] whish list for 2016

On Dec 24, 2015, at 8:26 AM, Bernardo Sulzbach wrote: > > 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). Requirements change. In the past dozen years, the database schema I?m working on

[sqlite] whish list for 2016

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

[sqlite] whish list for 2016

On Dec 24, 2015, at 9:14 AM, Simon Slavin wrote: > > ALTER TABLE DROP COLUMN ... > > ...the way SQLite3 is written makes it difficult or inefficient to implement > them. I wouldn?t mind if SQLite did nothing more than the recommended manual process for emulating the ALTER TABLE affordances

[sqlite] whish list for 2016

On Thu, Dec 24, 2015 at 10:14 AM, Simon Slavin wrote: > > On 24 Dec 2015, at 3:12pm, John McKown > 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

[sqlite] whish list for 2016

On Thu, Dec 24, 2015 at 6:49 AM, gunnar 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

[sqlite] whish list for 2016

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

[sqlite] whish list for 2016

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

[sqlite] whish list for 2016

On 2015-12-21 3:46 PM, lchishol at paradise.net.nz 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

[sqlite] whish list for 2016

> On Dec 21, 2015, at 5:43 PM, Darren Duncan 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

[sqlite] whish list for 2016

> On Dec 21, 2015, at 4:08 AM, Darren Duncan 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,

[sqlite] whish list for 2016

On 2015-12-21 8:25 AM, Petite Abeille wrote: >> On Dec 21, 2015, at 4:08 AM, Darren Duncan >> 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?. > >

[sqlite] whish list for 2016

PRAGMA strict_mode = 1; :)

[sqlite] whish list for 2016

> On Dec 20, 2015, at 7:12 PM, Big Stone 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] whish list for 2016

) Best whishes for 2016! > Sun Dec 20 2015 7:12:39 pm CET CET from "Big Stone" > 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

[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] whish list for 2016

On 2015-12-20 10:25 AM, Petite Abeille wrote: >> On Dec 20, 2015, at 7:12 PM, Big Stone 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

[sqlite] whish list for 2016

A vote from me for OLAP analytical functions. -Original Message- From: Big Stone <stonebi...@gmail.com> To: sqlite-users 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