sqlite - Unsupported Sql

Not logged in

• Honeypot
• Browse
• Home
• Login
• Reports
• Search
• Ticket
• Timeline
• Wiki

• Contents
• Diff
• Text

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.

---

• The EXISTS keyword is not supported (IN is, but IN is only a special case of EXISTS). And what about corelated subqueries ?

• 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.
• Hierarhical Queries.

  START WITH <conditions> CONNECT BY [PRIOR]<conditions> (ORACLE)

• SQL92 Character sets, collations, coercibility.

• Inserting blob using X'AABBCCDD' syntax.

• Stored Procedures

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

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

• ALTER VIEW, ALTER TRIGGER, ALTER TABLE

• Schemas - Who can tell me what this means?
  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.

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

• CREATE TRIGGER [BEFORE | AFTER | INSTEAD OF] (Oracle)

• UPDATE with a FROM clause (not sure if this is standard, Sybase and Microsoft have it).

• 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');"

• 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

• ESCAPE clause for LIKE

• 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

  here

      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

• join syntax (+) (-) like oracle has - SQLite used to have this but it was removed because it is not standard SQL. RETURN IT BACK AGAIN, PLEASE!!!

• name columns in views (i.e. CREATE VIEW (foo, bar) AS SELECT qux, quo FROM baz;)

• FLOOR and CEILING functions, e.g. "SELECT FLOOR(salary) FROM personnel;"

• 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?
  My understanding of this request is that you want to be able to export non-public related data to external parties, thus hiding confidential information from them. I have a similar situation and it is relatively simple to export the database - just export the bits you want using the sqlite command line tool in 'insert' mode. For example :

  sqlite info.db
  .mode insert
  SELECT version, date, name, summary FROM logs;

Which would hide other fields that 'logs' might contain - such information as the details of the change, any private annotations or whatever you might store in other fields.

This form of reconstructing the database rather than exporting it with some form of encryption ensures that no data that you didn't intend to release is given away. If the data is there, even if it's behind the best encryption in the world, it might still be got at. If it isn't even present, you can't reconstruct it :-)

• AUTO_INCREMENT would be nice.. hope i didnt miss something now.. didnt do "alot" of checking.. : SQLite has integer primary key which means auto_increment integer primary key, Actually I prefer 'serial' term.

(Hey, the formatting of this page was screwed up, let's see if it's better now...)

• Honesty. On the Front Page it says SQLite is about 2x as fast as PostgreSQL or MySQL for common operations, then the linked page shows default configurations (i.e. not real world) for those databases.

That's not marketing, that's dishonest when you've been told better. Please excuse us if we recommend people to not use SQLite any more, nor embed it in their systems. If you can't be trusted, you're not worth counting on.