Change table designation in sqlite

[doctorized]

I have a sqlite db with tables like:
Settings (Var Char(32), Value Char(32)) and I want to change it to Settings (Var TEXT PRIMARY KEY, Value TEXT, Level TINYINT).
The table has already data. How can I do it without loosing the data?
I am trying:

Code: Select all

DatabaseUpdate(#db, "PRAGMA foreign_keys = 0")
DatabaseUpdate(#db, "BEGIN TRANSACTION")
DatabaseUpdate(#db, "ALTER TABLE Settings RENAME To sqlitemanager_temp_table_177354083607")
DatabaseUpdate(#db, "CREATE TABLE Settings (Var TEXT PRIMARY KEY, Value TEXT, Level TINYINT)")
DatabaseUpdate(#db, "INSERT INTO Settings (Var, Value) Select Var, Value FROM sqlitemanager_temp_table_177354083607")
DatabaseUpdate(#db, "DROP TABLE sqlitemanager_temp_table_177354083607")
DatabaseUpdate(#db, "COMMIT")
DatabaseUpdate(#db, "PRAGMA foreign_keys = 1")

But it seems that it is not working every time and the table looses all values. Also, I do not know if the existing table has all columns to insert to the new one, for example, the above example takes for granted that columns Var and Value do exist. Any suggestions?

[skywalk]

Is this a one-time fix?
Meaning, can you load the database in DB Browser for SQLite and make your edits manually?

Else, the programmatic approach is to create your desired DB and tables, then walk through the original DB AS ATTACHED with SELECT's and UPDATE's into new DB.

Your example is confusing since no mention of ROWID or WITHOUT ROWID Table type?

[doctorized]

I got these lines from SqliteManager aftes a small test. I have users with the older version of the table so the program checks all the tables at startup and does the changes where needed, if needed. For Example:

Code: Select all

	DB_Table_Settings = "CREATE TABLE Settings (Var TEXT PRIMARY KEY, Value TEXT, Level TINYINT)"
	DB_Correct_Settings = "INSERT INTO Settings (Var,Value) Select Var,Value FROM sqlitemanager_temp_table_177354083607"
	DatabaseQuery(#db,"SELECT sql FROM sqlite_master WHERE tbl_name = 'Settings' AND type = 'table'")
	NextDatabaseRow(#db)
	txt$ = GetDatabaseString(#db,0)
	FinishDatabaseQuery(#db)
	If txt$ = ""; no table
		DatabaseUpdate(#db,DB_Table_Settings)
		AddDBSettingsValues(); procedure to add default values
	ElseIf txt$ <> DB_Table_Settings
		DatabaseUpdate(#db,"PRAGMA foreign_keys = 0")
		DatabaseUpdate(#db,"BEGIN TRANSACTION")
		DatabaseUpdate(#db,"ALTER TABLE Settings RENAME To sqlitemanager_temp_table_177354083607")
		DatabaseUpdate(#db,DB_Table_Settings)
		DatabaseUpdate(#db,DB_Correct_Settings)
		DatabaseUpdate(#db,"DROP TABLE sqlitemanager_temp_table_177354083607")
		DatabaseUpdate(#db,"COMMIT")
		DatabaseUpdate(#db,"PRAGMA foreign_keys = 1")
	EndIf

I thing I should check if every column exists and if it does, add it in the INSERT query.