Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM
On 22 Oct 2012, at 8:35pm, Paul van Helden wrote: >> It would be possible to implement TRUNCATE TABLE on top of that, but >> this would be only syntactic sugar. > > ..or better portability. TRUNCATE TABLE works (since only a few years) > nearly everywhere. So when writing portable applications it seems a bit > silly to make an exception for SQLite if the solution is that simple and > won't break anything. The problem is that TRUNCATE TABLE means different things in different SQL implementations. Yes, they do all delete all the rows of a table, but some will do it even in violation of foreign key requirements and some won't; some allow it to be an element of a transaction whereas some consider it to be a schema-changing operation which can violate transaction structure; some obey all TRIGGERs involved in deleting rows, others just execute it ignoring TRIGGERs. On 22 Oct 2012, at 9:44pm, Guillaume Saumure wrote: > Personally, I have ask the question why TRUNCATE TABLE don't work because > this command is not listed on http://www.sqlite.org/omitted.html Because TRUNCATE isn't in SQL92, the version of SQL that this page starts from, as clearly stated on that page. It was introduced as a standard only in 2008, and since, as I wrote above, different engines do different things about it, anyone writing for portability across different SQL engines never uses it because they can never be sure what it does. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM
On Mon, 22 Oct 2012 16:44:12 -0400, Guillaume Saumure wrote: >. Anyway Something I can >do with >PureBasic is : > >Procedure.b TruncateDatabaseTable(DataBaseID.l, TableName.s) > > If DatabaseUpdate(DatabaseID, "TRUNCATE TABLE " + TableName) > Protected Success.b = #True > Else > If DatabaseUpdate(DatabaseID, "DELETE * FROM " + TableName) > Success = #True > Else > If DatabaseUpdate(DatabaseID, "DELETE FROM " + TableName) > Success = #True > Else > Success = #False > EndIf > EndIf > EndIf > > ProcedureReturn Success >EndProcedure That would work, but you don't need the "DELETE * FROM " + TableName , it's not valid SQL syntax, although some platforms might accept it. SQLite version 3.7.15 2012-10-07 05:34:39 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table x (i integer primary key, t text); sqlite> insert into x (i,t) values (1,'texts here'); sqlite> delete * from x; Error: near "*": syntax error sqlite> Also, take care with foreign key relationships with ON DELETE CASCADE clauses. If TRUNCATE bypasses them, you may end up with destroyed referential integrity. Faster is not always better. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM
On 10/22/2012 4:44 PM, Guillaume Saumure wrote: Personally, I have ask the question why TRUNCATE TABLE don't work because this command is not listed on http://www.sqlite.org/omitted.html This site list the non-supported feature of SQL92 standard and TRUNCATE TABLE is not listed so it should work. TRUNCATE TABLE is not a feature of SQL92. It was first standardized in SQL:2008, if Wikipedia (http://en.wikipedia.org/wiki/SQL:2008) is to be believed. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM
On 10/22/12 1:44 PM, "Guillaume Saumure" wrote: >Le 2012-10-22 15:35, Paul van Helden a écrit : >>> It would be possible to implement TRUNCATE TABLE on top of that, but >>> this would be only syntactic sugar. >>> >> ..or better portability. TRUNCATE TABLE works (since only a few years) >> nearly everywhere. So when writing portable applications it seems a bit >> silly to make an exception for SQLite if the solution is that simple and >> won't break anything. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >Personally, I have ask the question why TRUNCATE TABLE don't work >because this command is not listed on http://www.sqlite.org/omitted.html >This site list the non-supported feature of SQL92 standard and TRUNCATE >TABLE is not listed so it should work. SQLite is based on the 1992 SQL standard. TRUNCATE TABLE is from the 2008 SQL standard, so of course it isn't included in a list of commands from the SQL 92 standard that SQLite doesn't implement. Will >Even if I built programs since 2003, >the use of database has never been necessary until last week. In one >week I have figure out enough of functionality to use them but what >command is part of this standard and what is not it's little bit >difficult to know for a database newbie like me. Anyway Something I can >do with >PureBasic is : > >Procedure.b TruncateDatabaseTable(DataBaseID.l, TableName.s) > > If DatabaseUpdate(DatabaseID, "TRUNCATE TABLE " + TableName) > Protected Success.b = #True > Else > If DatabaseUpdate(DatabaseID, "DELETE * FROM " + TableName) > Success = #True > Else > If DatabaseUpdate(DatabaseID, "DELETE FROM " + TableName) > Success = #True > Else > Success = #False > EndIf > EndIf > EndIf > > ProcedureReturn Success >EndProcedure > >If "TRUNCATE TABLE" fail the procedure fall back to "DELETE * FROM" and >if this fail the procedure try "DELETE FROM" then if this also fail give >up and return #false. Little bit intense for a simple database operation >but as long as it work, I'm happy. > >Best regards >Guillaume > >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM
Le 2012-10-22 15:35, Paul van Helden a écrit : It would be possible to implement TRUNCATE TABLE on top of that, but this would be only syntactic sugar. ..or better portability. TRUNCATE TABLE works (since only a few years) nearly everywhere. So when writing portable applications it seems a bit silly to make an exception for SQLite if the solution is that simple and won't break anything. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Personally, I have ask the question why TRUNCATE TABLE don't work because this command is not listed on http://www.sqlite.org/omitted.html This site list the non-supported feature of SQL92 standard and TRUNCATE TABLE is not listed so it should work. Even if I built programs since 2003, the use of database has never been necessary until last week. In one week I have figure out enough of functionality to use them but what command is part of this standard and what is not it's little bit difficult to know for a database newbie like me. Anyway Something I can do with PureBasic is : Procedure.b TruncateDatabaseTable(DataBaseID.l, TableName.s) If DatabaseUpdate(DatabaseID, "TRUNCATE TABLE " + TableName) Protected Success.b = #True Else If DatabaseUpdate(DatabaseID, "DELETE * FROM " + TableName) Success = #True Else If DatabaseUpdate(DatabaseID, "DELETE FROM " + TableName) Success = #True Else Success = #False EndIf EndIf EndIf ProcedureReturn Success EndProcedure If "TRUNCATE TABLE" fail the procedure fall back to "DELETE * FROM" and if this fail the procedure try "DELETE FROM" then if this also fail give up and return #false. Little bit intense for a simple database operation but as long as it work, I'm happy. Best regards Guillaume ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM
> > It would be possible to implement TRUNCATE TABLE on top of that, but > this would be only syntactic sugar. > ..or better portability. TRUNCATE TABLE works (since only a few years) nearly everywhere. So when writing portable applications it seems a bit silly to make an exception for SQLite if the solution is that simple and won't break anything. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM
Thanks. Good to know. From: Clemens Ladisch To: sqlite-users@sqlite.org Sent: Monday, October 22, 2012 1:02 PM Subject: Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM giris wrote: > Rather, the implementation/internals of SQLite need to change to allow this > very low overhead form of DELETE. Actually, SQLite already implements the low-overhead from of DELETE. src/delete.c has in sqlite3DeleteFrom(): #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION /* Special case: A DELETE without a WHERE clause deletes everything. ** It is easier just to erase the whole table. Prior to version 3.6.5, ** this optimization caused the row change count (the value returned by ** API function sqlite3_count_changes) to be set incorrectly. */ if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) && 0==sqlite3FkRequired(pParse, pTab, 0, 0) It would be possible to implement TRUNCATE TABLE on top of that, but this would be only syntactic sugar. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM
giris wrote: > Rather, the implementation/internals of SQLite need to change to allow this > very low overhead form of DELETE. Actually, SQLite already implements the low-overhead from of DELETE. src/delete.c has in sqlite3DeleteFrom(): #ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION /* Special case: A DELETE without a WHERE clause deletes everything. ** It is easier just to erase the whole table. Prior to version 3.6.5, ** this optimization caused the row change count (the value returned by ** API function sqlite3_count_changes) to be set incorrectly. */ if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) && 0==sqlite3FkRequired(pParse, pTab, 0, 0) It would be possible to implement TRUNCATE TABLE on top of that, but this would be only syntactic sugar. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM
Hi: Mapping TRUNCATE table to DELETE * is not going to help and seems like a bad idea. TRUNCATE table is an artifact implemented in many SQL implementations, and can make a world of difference in performance when deleting all rows in a table. It avoids all the overhead of transaction logging, deallocation, and related record keeping. Rather, the implementation/internals of SQLite need to change to allow this very low overhead form of DELETE. If you do need to delete all rows in a table AND have the DDL to create the table handy, simply drop the table and re-create it. If you need to delete most of the rows, you can use a technique often performed in Data Warehousing, known as CTAS (CREATE TABLE AS SELECT . . .) wherein you select the rows you want to keep into a new table, drop the old one, and rename the new table to the old name, if RENAME is supported by your version of SQLite. HTH. Thanks. From: Paul van Helden To: General Discussion of SQLite Database Sent: Monday, October 22, 2012 11:10 AM Subject: [sqlite] TRUNCATE TABLE alias for DELETE FROM Hi, TRUNCATE TABLE is now in the SQL:2008 standard. http://en.wikipedia.org/wiki/Truncate_(SQL) It would make portability easier if SQLite understood TRUNCATE TABLE to be the same as DELETE FROM without WHERE. Yes? No? Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TRUNCATE TABLE alias for DELETE FROM
Hi, TRUNCATE TABLE is now in the SQL:2008 standard. http://en.wikipedia.org/wiki/Truncate_(SQL) It would make portability easier if SQLite understood TRUNCATE TABLE to be the same as DELETE FROM without WHERE. Yes? No? Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
