*** 1,22 ****
! There is a list of features that SQLite does *not* support at http://www.sqlite.org/omitted.html. If you find additional
features that SQLite does not support, you may want to list them
below.
----
! *: Extended POSIX regular expressions (should be easy, man 3 regcomp, or http://mirbsd.bsdadvocacy.org/man3/regcomp.htm for reference)
SELECT * FROM table WHERE name REGEX '[a-zA-Z]+_{0,3}';
! *: The EXISTS keyword is not supported (IN is, but IN is only a special case of EXISTS). And what about corelated subqueries ?
_:Both supported as of 3.1.
! *: Multiple databases are not supported. For example, the following construct for creating a table in a database db1 based on a table in database db2 won't work:
create table db1.table1 as select * from db2.table1;
_: But I often need this. So, it should looks like a schemas in ORACLE database.
*: Hierarchical Queries.
START WITH <conditions> CONNECT BY [PRIOR]<conditions> (ORACLE)
--- 1,28 ----
! There is a list of features that SQLite does *not* support at http://www.sqlite.org/omitted.html. If you
! find additional
features that SQLite does not support, you may want to list them
below.
----
! *: Extended POSIX regular expressions (should be easy, man 3 regcomp, or http://
! mirbsd.bsdadvocacy.org/man3/regcomp.htm for reference)
SELECT * FROM table WHERE name REGEX '[a-zA-Z]+_{0,3}';
! *: The EXISTS keyword is not supported (IN is, but IN is only a special case of EXISTS). And what about
! corelated subqueries ?
_:Both supported as of 3.1.
! *: Multiple databases are not supported. For example, the following construct for creating a table in a
! database db1 based on a table in database db2 won't work:
create table db1.table1 as select * from db2.table1;
_: But I often need this. So, it should looks like a schemas in ORACLE database.
+ _:isn't this supported by ATTACH DATABASE?
+
*: Hierarchical Queries.
START WITH <conditions> CONNECT BY [PRIOR]<conditions> (ORACLE)
***************
*** 29,40 ****
*: Rollup and Cube - _Who can tell me what this means?_
! _::: I don't know much about it myself, but a quick google on the subject gives me... http://www.winnetmag.com/SQLServer/Article/ArticleID/5104/5104.html and http://databases.about.com/library/weekly/aa070101a.htm
!
! _::: both of these imply that the CUBE operator causes new rows to be generated to give a wildcard value to non-numeric columns and summing the numeric columns which match those wildcards. The potential for generating a huge amount of data with cube is implicit, I think - hence its name. ROLLUP appears to be related but removes some of the wildcards; I couldn't determine what from the limited information in the articles. I could not find, on brief examination any more definitive reference. Anyone got something more definitive than those articles ? It seems to me that you can do with sum() everything you can do with CUBE.
!
! _::: CUBE an ROLLUP provide addition subtotal rows. Lets say you are doing a query "SELECT x, y, SUM(z) FROM t GROUP BY x, y" lets also say x and y each have two values. This query will give you the sums for all records with x1 y1, x1 y2, x2 y1, and x2 y2. Rollup and cube both provide addition subtotals. Rollup adds 3 new sums: for all x1, for all x2, and the grand total. You can imagine that the GROUP BY list is being rolled up, so that it goes from being x, y; to being just x; to being empty. The result of the select for the column that is rolled up becomes NULL. CUBE will do all combinations of sums in the group by list: sum of all x1, all x2, all y1, all y2, and grand total. No idea what that has to do with a cube, though I do sort of picture a hyper-cube in my mind for no good reason. If you ever add ROLLUP and CUBE, I also recommend adding the GROUPING() function so that you can filter out the additional computations you don't want, or do somthing like SELECT CASE WHEN GROUPING(name) THEN
! 'Total' ELSE name END, hours FROM timesheets GROUP BY name. I've used the feature plenty doing reports, but then I'm a chronic SQL abuser.
*: INSERT INTO with multiple rows (ie. INSERT INTO table VALUES (...), (...). etc)
--- 35,65 ----
*: Rollup and Cube - _Who can tell me what this means?_
! _::: I don't know much about it myself, but a quick google on the subject gives me... http://
! www.winnetmag.com/SQLServer/Article/ArticleID/5104/5104.html and http://databases.about.com/
! library/weekly/aa070101a.htm
!
! _::: both of these imply that the CUBE operator causes new rows to be generated to give a wildcard
! value to non-numeric columns and summing the numeric columns which match those wildcards. The
! potential for generating a huge amount of data with cube is implicit, I think - hence its name. ROLLUP
! appears to be related but removes some of the wildcards; I couldn't determine what from the limited
! information in the articles. I could not find, on brief examination any more definitive reference. Anyone
! got something more definitive than those articles ? It seems to me that you can do with sum()
! everything you can do with CUBE.
!
! _::: CUBE an ROLLUP provide addition subtotal rows. Lets say you are doing a query "SELECT x, y,
! SUM(z) FROM t GROUP BY x, y" lets also say x and y each have two values. This query will give you the
! sums for all records with x1 y1, x1 y2, x2 y1, and x2 y2. Rollup and cube both provide addition
! subtotals. Rollup adds 3 new sums: for all x1, for all x2, and the grand total. You can imagine that the
! GROUP BY list is being rolled up, so that it goes from being x, y; to being just x; to being empty. The
! result of the select for the column that is rolled up becomes NULL. CUBE will do all combinations of
! sums in the group by list: sum of all x1, all x2, all y1, all y2, and grand total. No idea what that has to
! do with a cube, though I do sort of picture a hyper-cube in my mind for no good reason. If you ever
! add ROLLUP and CUBE, I also recommend adding the GROUPING() function so that you can filter out the
! additional computations you don't want, or do somthing like SELECT CASE WHEN GROUPING(name)
! THEN
! 'Total' ELSE name END, hours FROM timesheets GROUP BY name. I've used the feature plenty doing
! reports, but then I'm a chronic SQL abuser.
*: INSERT INTO with multiple rows (ie. INSERT INTO table VALUES (...), (...). etc)
***************
*** 48,59 ****
_::: The idea is that multiple users using the same database can cleanly
separate their tables, views (stored procs, etc) by prefixing them with
! their login, so jack's jack.importantTable is distinct from jill's jill.importantTable. There are administrative benefits ('Jack left and we don't like his work; can we kill everything he did?' Ans: 'Yes, let me just
! drop his schema..', with aliases, jill.importantTable can be made available to everybody as 'importantTable', permissions can be hung off schemas). The common notation (jill.importantTable) would map to databasename.tablename in the current sqlite arrangement.
_:::: This doesn't really make a lot of sense for an embedded database.
! *: TRUNCATE (MySQL, Postgresql and Oracle have it... but I dont know if this is a standard command) - _SQLite does this automatically when you do a DELETE
without a WHERE clause. You can use also VACUUM command_
*: ORDER BY myfield ASC NULLS LAST (Oracle)
--- 73,89 ----
_::: The idea is that multiple users using the same database can cleanly
separate their tables, views (stored procs, etc) by prefixing them with
! their login, so jack's jack.importantTable is distinct from jill's jill.importantTable. There are
! administrative benefits ('Jack left and we don't like his work; can we kill everything he did?' Ans: 'Yes, let
! me just
! drop his schema..', with aliases, jill.importantTable can be made available to everybody as
! 'importantTable', permissions can be hung off schemas). The common notation (jill.importantTable)
! would map to databasename.tablename in the current sqlite arrangement.
_:::: This doesn't really make a lot of sense for an embedded database.
! *: TRUNCATE (MySQL, Postgresql and Oracle have it... but I dont know if this is a standard command) -
! _SQLite does this automatically when you do a DELETE
without a WHERE clause. You can use also VACUUM command_
*: ORDER BY myfield ASC NULLS LAST (Oracle)
***************
*** 64,70 ****
_:: Postgres also allows "UPDATE ... FROM ... ", BTW.
! _:: I was working on something where I really wanted to use this construct with SQLite, so I came up with the following hack:
--
-- SQLite does not allow "UPDATE ... FROM"
--- 94,101 ----
_:: Postgres also allows "UPDATE ... FROM ... ", BTW.
! _:: I was working on something where I really wanted to use this construct with SQLite, so I came up
! with the following hack:
--
-- SQLite does not allow "UPDATE ... FROM"
***************
*** 99,113 ****
t2.key = t1.key
;
! _:: Since that works, maybe SQLite could be made to support the "UPDATE ... FROM" construct directly, so we would not have to rely on conflict resolution to do essentially the same thing (not exactly the same, since REPLACE is DELETE and INSERT, but sometimes close enough). *< gifford hesketh::2004-Oct-26*
*: Multi-column IN clause (ie. SELECT * FROM tab WHERE (key1, key2) IN (SELECT...)
! *: CURRENT-Functions like CURRENT_DATE, CURRENT_TIME are missing _Try "SELECT date('now');" or "SELECT datetime('now','localtime');"_
_:Added as of 3.1
! *: INSERTing less values than columns does not fill the missing columns with the default values; if less values than columns in the table are supplied, all columns filled have to be named before the keyword values
*: INSERTing one record with all VALUES to DEFAULT: INSERT INTO example () VALUES ();
--- 130,150 ----
t2.key = t1.key
;
! _:: Since that works, maybe SQLite could be made to support the "UPDATE ... FROM" construct directly,
! so we would not have to rely on conflict resolution to do essentially the same thing (not exactly the
! same, since REPLACE is DELETE and INSERT, but sometimes close enough). *< gifford hesketh::2004-
! Oct-26*
*: Multi-column IN clause (ie. SELECT * FROM tab WHERE (key1, key2) IN (SELECT...)
! *: CURRENT-Functions like CURRENT_DATE, CURRENT_TIME are missing _Try "SELECT date('now');" or
! "SELECT datetime('now','localtime');"_
_:Added as of 3.1
! *: INSERTing less values than columns does not fill the missing columns with the default values; if less
! values than columns in the table are supplied, all columns filled have to be named before the keyword
! values
*: INSERTing one record with all VALUES to DEFAULT: INSERT INTO example () VALUES ();
***************
*** 115,126 ****
_:Added as of 3.1
! *: DISTINCT ON (expr,...) - this is from Postgres, where expr,... must be the leftmost expressions from the ORDER BY clause
! *: MEDIAN and standard deviation... are they standard? Essential for sqlite standalone executable for shell script users.
! _:_MEDIAN is difficult because it cannot be done "on-line," i.e., on a stream of data. Following is a solution to MEDIAN credited to David Rozenshtein, Anatoly Abramovich, and Eugene Birger; it is explained_
! <HTML><A href="http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html">here</A></HTML>
SELECT x.Hours median
FROM BulbLife x, BulbLife y
--- 152,168 ----
_:Added as of 3.1
! *: DISTINCT ON (expr,...) - this is from Postgres, where expr,... must be the leftmost expressions from
! the ORDER BY clause
! *: MEDIAN and standard deviation... are they standard? Essential for sqlite standalone executable for
! shell script users.
! _:_MEDIAN is difficult because it cannot be done "on-line," i.e., on a stream of data. Following is a
! solution to MEDIAN credited to David Rozenshtein, Anatoly Abramovich, and Eugene Birger; it is
! explained_
! <HTML><A href="http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html">here</A></
! HTML>
SELECT x.Hours median
FROM BulbLife x, BulbLife y
***************
*** 140,152 ****
*:IF EXISTS function, e.g. "DROP TABLE IF EXISTS temp;"
! *:a password('') function to mask some values (as used in MySQL) would be fine, I need it, if I give the db out of the house, or is there something I didn't find? Or a simple MD5 function to obscure data using a one way hash. See the MySQL function MD5 or Password for examples.
!
! *:AUTO_INCREMENT field type. SQLite supports auto_incrementing fields but only if that field is set as "INTEGER PRIMARY KEY".
!
! _:Oh god no! Stop the evil from spreading! AUTO_INCREMENT is possibly the worst way of doing unique ids for tables. It requires cached per-connection-handle last_insert_id() values. And you're probably already familiar with how much of a hack THAT is.
!
! _:A much better solution would be to give SQLite proper SEQUENCE support. You already have a private table namespace, so using sqlite_sequences to store these wouldn't be such a big deal. This is created when the database is created, and looks something like this, taken from a perl MySQL sequence emulation module.
create table mysql_sequences (
sequence_name char(32) not null primary key,
--- 182,202 ----
*:IF EXISTS function, e.g. "DROP TABLE IF EXISTS temp;"
! *:a password('') function to mask some values (as used in MySQL) would be fine, I need it, if I give the
! db out of the house, or is there something I didn't find? Or a simple MD5 function to obscure data
! using a one way hash. See the MySQL function MD5 or Password for examples.
!
! *:AUTO_INCREMENT field type. SQLite supports auto_incrementing fields but only if that field is set as
! "INTEGER PRIMARY KEY".
!
! _:Oh god no! Stop the evil from spreading! AUTO_INCREMENT is possibly the worst way of doing unique
! ids for tables. It requires cached per-connection-handle last_insert_id() values. And you're probably
! already familiar with how much of a hack THAT is.
!
! _:A much better solution would be to give SQLite proper SEQUENCE support. You already have a private
! table namespace, so using sqlite_sequences to store these wouldn't be such a big deal. This is created
! when the database is created, and looks something like this, taken from a perl MySQL sequence
! emulation module.
create table mysql_sequences (
sequence_name char(32) not null primary key,
***************
*** 155,167 ****
sequence_value bigint not null default 1
)
! _:In fact, why don't you just take a look at the original module <HTML><A href="http://search.cpan.org/~adamk/DBIx-MySQLSequence-0.1/MySQLSequence.pm">DBIx::MySQLSequence</A></HTML>. In fact, why don't you just copy that module, and rewrite using code inside the database.
!
! _:The main reason for doing this, is that if you want to insert multiple records which reference each other, and these references are not null, you cannot insert one record until you have inserted the one to which it refers, then fetched the last_insert_id(), added it to the other record, then insert that, and so in. In trivial cases this isn't too bad, but imagine the cases where you have circular references, or don't know the structure of the data in advance at all.
!
! _:With sequence support and access to ids before inserting, there are algorithms to resolve these cases. Without it, you are left with things like just outright suspending contraints checking, inserting everything incorrectly, then hoping you can find all the cases of broken values, and fixing them. Which sucks if you don't know the structure beforehand.
! _:To resolve compatibility issues, just do what you do now with the INTEGER PRIMARY_KEY fields with no default, but allow a DEFAULT SEQUENCENAME.NEXTVAL() or something...
_::For better or worse, the requested feature was added in 3.1
--- 205,227 ----
sequence_value bigint not null default 1
)
! _:In fact, why don't you just take a look at the original module <HTML><A href="http://
! search.cpan.org/~adamk/DBIx-MySQLSequence-0.1/MySQLSequence.pm">DBIx::MySQLSequence</
! A></HTML>. In fact, why don't you just copy that module, and rewrite using code inside the database.
!
! _:The main reason for doing this, is that if you want to insert multiple records which reference each
! other, and these references are not null, you cannot insert one record until you have inserted the one to
! which it refers, then fetched the last_insert_id(), added it to the other record, then insert that, and so in.
! In trivial cases this isn't too bad, but imagine the cases where you have circular references, or don't
! know the structure of the data in advance at all.
!
! _:With sequence support and access to ids before inserting, there are algorithms to resolve these cases.
! Without it, you are left with things like just outright suspending contraints checking, inserting
! everything incorrectly, then hoping you can find all the cases of broken values, and fixing them. Which
! sucks if you don't know the structure beforehand.
! _:To resolve compatibility issues, just do what you do now with the INTEGER PRIMARY_KEY fields with
! no default, but allow a DEFAULT SEQUENCENAME.NEXTVAL() or something...
_::For better or worse, the requested feature was added in 3.1
***************
*** 170,187 ****
_::Now supported as of 3.1
! *:More than one primary key per table, I can specify this with MySQL for example and SQLite returns me an error: more than one primary key specified...
(Hey, the formatting of this page was screwed up, let's see if it's better now...)
*:UPDATE t1, t2 SET t1.f1 = value WHERE t1.f2 = t2.fa
! *:SHOW TABLES and DESCRIBE [tablename] would be nice - not sure if they're standard, but they are a rather nice feature of MySQL...
===========
REMARK
===========
! Sqlite is finally a database product that values performance and minimal footprint (disk and memory) above a trashcan strategy that would add whatever feature to make the result so-called 'feature rich', say, a bloated piece of software. Therefore, I would vehemently reject all additions listed above, except for one. It's quite difficult to obtain the result for a correlated 'NOT EXISTS' subquery in any alternative way; which is the choice way to determine a subset of data that doesn't satisfy criteria contained in another table.
! -- Are you calling Oracle 'a bloated piece of software'?. LOL. I would love to see a comparison of Oracle and SQLite (latest stable or bleeding edge SQLite version Vs Oracle 10g). I would love it. [This comparison idea is as valid as comparing a novel to a short story.] Anyway, SQLite seems a lil' database engine for lil' works. Sorry, not enough for me :).
--- 230,257 ----
_::Now supported as of 3.1
! *:More than one primary key per table, I can specify this with MySQL for example and SQLite returns me
! an error: more than one primary key specified...
(Hey, the formatting of this page was screwed up, let's see if it's better now...)
*:UPDATE t1, t2 SET t1.f1 = value WHERE t1.f2 = t2.fa
! *:SHOW TABLES and DESCRIBE [tablename] would be nice - not sure if they're standard, but they are a
! rather nice feature of MySQL...
===========
REMARK
===========
! Sqlite is finally a database product that values performance and minimal footprint (disk and memory)
! above a trashcan strategy that would add whatever feature to make the result so-called 'feature rich',
! say, a bloated piece of software. Therefore, I would vehemently reject all additions listed above, except
! for one. It's quite difficult to obtain the result for a correlated 'NOT EXISTS' subquery in any alternative
! way; which is the choice way to determine a subset of data that doesn't satisfy criteria contained in
! another table.
! -- Are you calling Oracle 'a bloated piece of software'?. LOL. I would love to see a comparison of Oracle
! and SQLite (latest stable or bleeding edge SQLite version Vs Oracle 10g). I would love it. [This
! comparison idea is as valid as comparing a novel to a short story.] Anyway, SQLite seems a lil' database
! engine for lil' works. Sorry, not enough for me :).
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|