[SQL-CVS] [ sqlobject-Patches-1539325 ] fromDatabase and delColumn for sqlite
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: SourceForge.net <no...@so...> - 2006-08-27 16:09:45
|
Patches item #1539325, was opened at 2006-08-12 14:42 Message generated for change (Comment added) made by abelcodemonk You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540674&aid=1539325&group_id=74338 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: None Group: None Status: Open Resolution: None Priority: 5 Submitted By: Arlo Belshee (abelcodemonk) Assigned to: Oleg Broytmann (phd) Summary: fromDatabase and delColumn for sqlite Initial Comment: This patch implements both fromDatabase and delColumn for sqlite. I added sqlite to the appropriate existing tests; it passes them. fromDatabase is done by parsing the table creation sql from the sqlite_master table. This will work with any sqlite 3, and I think dates back into sqlite 2, but I'm not sure. delColumn is implemented by renaming the table, creating a new table without the one column, then copying all the data over. This is obviously not as efficient as on DBs that support drop column directly, but it works. This implementation of dropColumn does deal correctly with triggers, foreign keys, and indices that reference the table whose column is being dropped. It does not deal correctly with triggers & indices attached directly to the table being altered. Attached items will be lost. Extending the implementation to support re-creating those indices & triggers should be straightforward, but is beyond my needs. You are welcome to do so. Finally, this patch slightly improves the implementation of addColumn for sqlite. It performs a vacuum operation after each column add. See the sqlite documentation for why this is a good idea. ---------------------------------------------------------------------- >Comment By: Arlo Belshee (abelcodemonk) Date: 2006-08-27 09:09 Message: Logged In: YES user_id=1027257 Why vacuum after addColumn: According to SQLite's docs, alter table add column doesn't add the column to the table's main location in the file, but rather creates it at the end of the file and the DB then takes care of pretending that the column adjoins all the other columns. Unfortunately, older versions of SQLite don't do this pretending, so can't read a table that has had a column added. Vacuum re-creates the table as a single table, meaning that all versions can then read it. Delete column, being not supported directly by SQLite, doesn't do anything funny. Since my implementation is just to create a new table without the column being dropped, all the tables remain together without any weird extra space, and a vacuum is not needed. Why I added semicolons: No, they are not needed, if you are using pysqlite. However, they are required in the interactive SQLite interpreter. I added the semicolons so that I could quickly paste generated SQL statements into the interactive console while I was trying to get things working. Since the semicolons have no effect with pysqlite, I just left them in for the next time I wan't to do this. Feel free to take them out if you'd like. On the name of delColumn(): Feel free to name it delColumn. I gave the longer and clear name to the low-level implementation primarily to highlight the performance implications involved. The public-facing objects still expose delColumn; that is translated to a call to recreateTableWithoutColumn() if the sqliteconnection is being used. So, external programs use it as deleteColumn(), but my patch is easier to understand and the performance implications are clearer. Mostly, I was hoping that that would speed patch approval. ;) Feel free to name the function as you will. On transactions: Yeah, you can't vacuum in transactions. However, sqlite's support for transactions is already fairly problematic. I find I can't use transactions with SQLite on any real problems anyway, so I don't worry about it. When I need transactions, then I generally need a more full-featured DBMS than SQLite. If you have a different opinion on this, you could remove the call to vacuum in addColumn(). You are trading off read speed & the ability for older versions of sqlite to read such a modified table. Yer call. ---------------------------------------------------------------------- Comment By: Oleg Broytmann (phd) Date: 2006-08-15 06:08 Message: Logged In: YES user_id=4799 Thank you very much! recreateTableWithoutColumn() is an excellent solution! I have some questions about the patch. Why have you added semicolons? I think they are not needed. Why VACUUM after addColumn()? It is more logical to do VACUUM after delColumn(), I suppose. And, BTW, "This command will fail if there is an active transaction." (from the SQLite documentation). Your patch deletes .delColumn(). But I have a number of programs that relies on a presence of the method (even if it's empty). I think you could just call recreateTableWithoutColumn() from delColumn() or raise a warning or an exception. ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540674&aid=1539325&group_id=74338 |