SQLite won't Alter Table

[pjborg]

I would like to be able to rename the SQLite tables in my project, but it seems the AMS SQLite plugin does not support the Alter Table function. I can copy data from one table to another, then drop the first table, but that is awfully slow. Does anyone know of a better/faster way? If not I will have to forget about having that as an option in my project.

It seems odd that the SQLite Help file should point to the version 3 syntax. I couldn't find a comparable site for version 2. It says you can get the documentation from the source files. Has anyone done that? I don't understand their instructions frankly. Maybe I need more knowledge or different software.

Thanks.

[bule]

SQLite version in APMS SQLite plugin is version 2.1, therefore there is no ALTER TABLE functionality.

[pjborg]

The plugin I have is version 2.8.6, but as you say, it does not support Alter Table. So I guess that means there is no way - other than the very slow roundabout way I described - to rename a table.

[bule]

Are you using transactions when converting manually?

If not, before your loop, add following:

SQLite.Query(db, "BEGIN TRANSACTION;", nil);

And after the loop, add following:

SQLite.Query(db, "COMMIT TRANSACTION;", nil);

This should increase speed tremendously.

[Intrigued]

BEGIN COMMIT TRANSACTION SQLite loop Table

Are you using transactions when converting manually?

If not, before your loop, add following:

SQLite.Query(db, "BEGIN TRANSACTION;", nil);

And after the loop, add following:

SQLite.Query(db, "COMMIT TRANSACTION;", nil);

This should increase speed tremendously.

Thanks for that tip bule.

[bule]

You should always use transactions when doing a lot of changes on the database, and not only for performance issues. For example, if the power to the computer is lost while the database is being changed, it will be rolled back to the state before the transaction was started when you open it next time (note that I haven't really tested this, but that is the word, since SQLite is ACID compilant). Also, if your program hangs in the middle of the database change, the partial made changes won't be commited, therefore your data structure will be intact.

To cut the long story short, if you ever do more than one change to the database one after other (or withing the loop), put the begin and commit around those statements (but not within the loop itself).

Also, there is a command:

SQLite.Query(db, "ROLLBACK TRANSACTION;", nil);

which you can use programmatically to rollback any changes made to the database since the begin tranaction statement.

[bule]

The plugin I have is version 2.8.6, but as you say, it does not support Alter Table. So I guess that means there is no way - other than the very slow roundabout way I described - to rename a table.

Create an empty table using that plugin an then open the newly created database file in notepad.

It should read ** This file contains an SQLite 2.1 database ** at the begging of the file.

Anyone have an alter table APMS function laying around?

[tableknome]

This may or may not help in relation to your particular project, but if you are looking to do just a one time change of your database, and do not necessarily need AMS to do it, there is a 3rd party program up on Sourceforge called sqllitebrowser. The older versions (1.2.9?) support SQLLite 2, the newer versions they drop support for it in favor or SQLLite 3.

I use them for modifying my DB, and use AMS more just for reading after the fact. They are available for both Windows and Linux as well.

[bule]

When you modify a table (add a column) with SQLiteBrowser 1.1, your primary key becomes an ordinary integer fields and you lose auto-increment functionality.

[RizlaUK]

You should always use transactions when doing a lot of changes on the database, and not only for performance issues. For example, if the power to the computer is lost while the database is being changed, it will be rolled back to the state before the transaction was started when you open it next time (note that I haven't really tested this, but that is the word, since SQLite is ACID compilant). Also, if your program hangs in the middle of the database change, the partial made changes won't be commited, therefore your data structure will be intact.

To cut the long story short, if you ever do more than one change to the database one after other (or withing the loop), put the begin and commit around those statements (but not within the loop itself).

Also, there is a command:
Code:

SQLite.Query(db, "ROLLBACK TRANSACTION;", nil);

which you can use programmatically to rollback any changes made to the database since the begin tranaction statement.

thanks blue, really good info there, solves a isseu im having with my app locking up on slower machines, this will save the database :yes

When you modify a table (add a column) with SQLiteBrowser 1.1, your primary key becomes an ordinary integer fields and you lose auto-increment functionality.

AH HA, i stopped editing my db with sqlite browser after i was getting some *unexplainable* errors, guess this explains all

[bule]

I wonder if there are any plans at IR to move to a bit newer SQLite, just look at the release dates here: http://www.sqlite.org/changes.html

Many things have been improved in SQLite during the last five years... It is worth noticing (from the SQLite page):
It is known that Google uses SQLite in their Desktop for Mac, in Google Gears, and in the Android cell-phone operating system. People are suspicious that Google uses SQLite for lots of other things that we do not know about yet. Engineers at Google have made extensive contributions to the full-text search subsystem within SQLite.

P.S. Indeed, the APMS SQLite engine is 2.8.6, ignore my remark about 2.1 being written in the database file, AFAIK it's by default for the 2.1 < x < 3.0 builds...