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. ---- *: 2006.03.06 : select without left join with the *= operator SELECT t1.code, t2.code FROM table1 t1, table2 t2 WHERE t1.t2_ref_id *= t2.id This is Sybase ASE syntax. Related to "Oracle's join syntax" mentioned below *: 2005.12.27 : Oracle's Named Parameter output syntax. In Oracle, one can declare parameters and select into them as such Select A1, A2, A3 into (:p1, :p2, :p3) from TableA *: 2005.10.06 : This appears to be unsupported: updating multiple columns with subselect update T1 set (theUpdatedValue, theOtherValue) = (select theTop, theValue from T2 where T2.theKey = T1.theID) *: 2005.09.25 : free text search capabilities in select statements: Mysql does free text search Match(field_list) Against(keyword) *: 2004.11.17 : INSERTing one record with all VALUES to DEFAULT: INSERT INTO example () VALUES (); *: 2004.08.13 : Oracle's join syntax using (+) and (-): SELECT a1.a, a1.b, a2.a, a2.b FROM a1 LEFT JOIN a2 ON a2.b = a1.a _:...can be written in Oracle as: SELECT a1.a, a1.b, a2.a, a2.b FROM a1, a2 WHERE a1.a = a2.b(+); *: 2004.04.25 : 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. *: 2004.04.07 : 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. 2005.02.07 : isn't this supported by ATTACH DATABASE? *: 2004.03.26 : Hierarchical Queries. START WITH CONNECT BY [PRIOR] (ORACLE) *: 2004.03.17 : FLOOR and CEILING functions, e.g. "SELECT FLOOR(salary) FROM personnel;" *: 2004.01.08 : 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 here: http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html SELECT x.Hours median FROM BulbLife x, BulbLife y GROUP BY x.Hours HAVING SUM(CASE WHEN y.Hours <= x.Hours THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND SUM(CASE WHEN y.Hours >= x.Hours THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1 *: 2004.01.01 : DISTINCT ON (expr,...) - this is from Postgres, where expr,... must be the leftmost expressions from the ORDER BY clause *: 2003.10.30 : INSERTing fewer values than columns does not fill the missing columns with the default values; if fewer values than columns in the table are supplied, all columns filled have to be named before the keyword values *: 2003.09.04 : Multi-column IN clause (ie. SELECT * FROM tab WHERE (key1, key2) IN (SELECT...) *: 2003.08.29 : UPDATE with a FROM clause (not sure if this is standard, Sybase and Microsoft have it). -> UnsupportedSqlUpdateWithFrom *: 2003.08.06 : ALTER VIEW, ALTER TRIGGER, ALTER TABLE *: 2003.08.06 : CREATE DATABASE, DROP DATABASE - _Does not seem meaningful for an embedded database engine like SQLite. To create a new database, just do sqlite_open(). To drop a database, delete the file._ *: 2003.08.06 : Schemas - See: http://www.postgresql.org/docs/8.1/static/ddl-schemas.html -> UnsupportedSqlSchemas *: 2003.08.06 : 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_ *: 2003.08.06 : ORDER BY myfield ASC NULLS LAST (Oracle) *: 2003.08.06 : CREATE TRIGGER [BEFORE | AFTER | INSTEAD OF] (Oracle) *: 2003.07.28 : Stored Procedures *: 2003.07.28 : Rollup and Cube -> UnsupportedSqlRollupAndCube *: name columns in views (i.e. CREATE VIEW (foo, bar) AS SELECT qux, quo FROM baz;) *: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... _: "More than one primary key" is an oxymoron when you're talking about the relational data model. By definition, a primary key uniquely identfies a row. What's the real problem you're trying to solve? _:: A combined primary key is possible in SQLite, for example: CREATE TABLE strings ( string_id INTEGER NOT NULL, language_id INTEGER NOT NULL, string TEXT, PRIMARY KEY (string_id, language_id) ); *: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... -------- No, it's not standard. The standard says it should be a special database called INFORMATION_SCHEMA, wich contains info about all databases, tables, columns, index, views, stored procedures, etc. Can someone tell me how to fake describe until something like this is implemented? Sorry, I'm too dependent on Oracle apparently :( *:SELECT ... FOR UPDATE OF ... is not supported. This is understandable considering the mechanics of SQLite in that row locking is redundant as the entire database is locked when updating any bit of it. However, it would be good if a future version of SQLite supports it for SQL interchageability reasons if nothing else. The only functionality required is to ensure a "RESERVED" lock is placed on the database if not already there. *:DELETE from table ORDER BY column LIMIT x,y is not supported. I worked around it by using a second query and deleting, eg: SELECT timestamp from table LIMIT x,1; DELETE from table where timestamp < ..... *:The corollary to above, UPDATE table SET x WHERE y ORDER BY z is not supported. Haven't tried the LIMIT addition to that form. *:Named parts of natural joins. For example: SELECT a.c1 FROM T1 a NATURAL JOIN T1 b. Because sqlite reduces the number of columns kept, the name is lost. *:The ALL and ANY quantifiers for comparisons with subquery results aren't supported. *:create table wg ( cpf numeric not null, id numeric not null, nome varchar(25), primary key (cpf) foreign key (id) ); the foreign key (id) generate an error, a chance to be supported in the future? foreign key dont supported in sqlite? or generater automaticaly or ? That's not a legal FOREIGN KEY clause; you have to specify what the foreign key references. SQLite parses, but does not enforce, syntactically-legal FOREIGN KEY specifications; there's a PRAGMA that will retrieve foreign-key information from table definitions, allowing you to enforce such constraints with application code. *: Analytical functions -> UnsupportedSqlAnalyticalFunctions *: 2003.07.02 : SQL92 Character sets, collations, coercibility. ==== FEATURES ADDED IN RECENT VERSIONS ==== *:IF EXISTS function, e.g. "DROP TABLE IF EXISTS temp;" _:Added in 3.3 *: 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 infrastructure for this syntax now exists, but you have to create a user-defined regex matching function. *: 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. *: Inserting blob using X'AABBCCDD' syntax. (note: supported in Sqlite3) *: CURRENT-Functions like CURRENT_DATE, CURRENT_TIME are missing _Try "SELECT date('now');" or "SELECT datetime('now','localtime');"_ _:Added as of 3.1 *: ESCAPE clause for LIKE _:Added as of 3.1 *: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, sequence_start bigint not null default 1, sequence_increment bigint not null default 1, sequence_value bigint not null default 1 ) _:In fact, why don't you just take a look at the original module (DBIx::MySQLSequence): http:// search.cpan.org/~adamk/DBIx-MySQLSequence-0.1/MySQLSequence.pm. 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 *:SELECT t1.ID, (SELECT COUNT(*) FROM t2 WHERE t2.ID=t1.ID) FROM t1{linebreak} _:In other words, in a subselect backreferencing to a field in its parent select. _::Now supported as of 3.1 =========== REMARK =========== NOT EXISTS remarks (off topic) -> UnsupportedSqlRemarkOffTopic What about Apache Derby? It uses the Apache 2.0 license and is easy to embed in Java applications (http://db.apache.org/derby/). -- See SqliteVersusDerby ---- Tcl related ---- *: Tcl variable bindings for list types? i.e.: set values [list a b c] db eval { SELECT * FROM table WHERE x IN ($values) } SQLite does its own variable interpolation which avoids the (messy) need to do value quoting/escaping (to protect against SQL injection attacks, etc.) but in the case where it's an "IN ($variable)" clause, it treats $variable as a single value instead of a Tcl list of values. Or, maybe I'm doing something wrong. If I am, please let me know: dossy@panoptic.com.