Check sqlite db table columns

Just starting out? Need help? Post your questions and find answers here.
User avatar
doctorized
Addict
Addict
Posts: 813
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Check sqlite db table columns

Post by doctorized »

Not really sure if my question belongs here. I have a sqlite database and every now and then I make changes to the database, I add or alter a table’s column. So, I need to check that a user has the latest db schema. I have the following code that tries to do it but it is not working. It cannot delete the temp table and “database table is locked” error returns. Any suggestions?

Code: Select all

Enumeration
	#db
EndEnumeration

Procedure CheckDatabaseUpdate(Database, Query$)
   Result = DatabaseUpdate(Database, Query$)
   If Result = 0
   	MessageRequester("Error",DatabaseError(),#MB_ICONERROR)
   EndIf
   
   ProcedureReturn Result
EndProcedure

Procedure CheckDB()
	CheckDatabaseQuery(#db,"SELECT sql FROM sqlite_master WHERE tbl_name = 'Students' AND type = 'table'")
	NextDatabaseRow(#db)
	Debug GetDatabaseString(#db,0)
	If GetDatabaseString(#db,0) <> "CREATE TABLE Students (id CHAR(15) PRIMARY KEY, Lname CHAR(32), Fname CHAR(32), Class CHAR(4))"
		CheckDatabaseUpdate(#db,"PRAGMA foreign_keys = 0")
		CheckDatabaseUpdate(#db,"BEGIN TRANSACTION")
		CheckDatabaseUpdate(#db,"ALTER TABLE Lessons RENAME To sqlitemanager_temp_table_177354083607")
		CheckDatabaseUpdate(#db,"CREATE TABLE Lessons (id CHAR(13), LName CHAR(30), Class CHAR(4))")
		CheckDatabaseUpdate(#db,"INSERT INTO Lessons (id,LName,Class) Select id,LName,Class FROM sqlitemanager_temp_table_177354083607")
		CheckDatabaseUpdate(#db,"DROP TABLE sqlitemanager_temp_table_177354083607")
		CheckDatabaseUpdate(#db,"COMMIT")
		CheckDatabaseUpdate(#db,"PRAGMA foreign_keys = 1")
	EndIf
	FinishDatabaseQuery(#db)
EndProcedure

If OpenDatabase(#db, DatabaseFile$, "", "", #PB_Database_SQLite)
	CheckDB()
EndIf
Now, I get the followin errors:
there is already another table or index with this name: sqlitemanager_temp_table_177354083607
table Lessons already exists
database table is locked
Last edited by doctorized on Sun Sep 19, 2021 2:50 pm, edited 2 times in total.
User avatar
Fangbeast
PureBasic Protozoa
PureBasic Protozoa
Posts: 4673
Joined: Fri Apr 25, 2003 3:08 pm
Location: Not Sydney!!! (Bad water, no goats)

Re: Check sqlite db table columns

Post by Fangbeast »

Where are you actually querying the database? As far as I can see, CheckDatabaseQuery is NOT a valid database command in pb. Unless you are aliasing the DatabaseQuery() command in your code somewhere?
Amateur Radio, D-STAR/VK3HAF
User avatar
Fangbeast
PureBasic Protozoa
PureBasic Protozoa
Posts: 4673
Joined: Fri Apr 25, 2003 3:08 pm
Location: Not Sydney!!! (Bad water, no goats)

Re: Check sqlite db table columns

Post by Fangbeast »

Code: Select all

Enumeration
  #db
EndEnumeration

Procedure CheckDB()
  If DatabaseQuery(#db,"SELECT sql FROM sqlite_master WHERE tbl_name = 'Students' AND type = 'table'")
    While NextDatabaseRow(#db)
      If GetDatabaseString(#db,0) <> "CREATE TABLE Students (id CHAR(15) PRIMARY KEY, Lname CHAR(32), Fname CHAR(32), Class CHAR(4))"
        DatabaseUpdate(#db,"PRAGMA foreign_keys = 0")
        DatabaseUpdate(#db,"BEGIN TRANSACTION")
        DatabaseUpdate(#db,"ALTER TABLE Lessons RENAME To sqlitemanager_temp_table_177354083607")
        DatabaseUpdate(#db,"CREATE TABLE Lessons (id CHAR(13), LName CHAR(30), Class CHAR(4))")
        DatabaseUpdate(#db,"INSERT INTO Lessons (id,LName,Class) Select id,LName,Class FROM sqlitemanager_temp_table_177354083607")
        DatabaseUpdate(#db,"DROP TABLE sqlitemanager_temp_table_177354083607")
        DatabaseUpdate(#db,"COMMIT")
        DatabaseUpdate(#db,"PRAGMA foreign_keys = 1")
      EndIf
    Wend
    FinishDatabaseQuery(#db)
  Else
    Debug "Problem with query:: " + DatabaseError()
  EndIf
EndProcedure

If OpenDatabase(#db, DatabaseFile$, "", "", #PB_Database_SQLite)
  CheckDB()
EndIf

Amateur Radio, D-STAR/VK3HAF
User avatar
doctorized
Addict
Addict
Posts: 813
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Re: Check sqlite db table columns

Post by doctorized »

Fangbeast wrote: Sun Sep 19, 2021 2:18 pm Where are you actually querying the database? As far as I can see, CheckDatabaseQuery is NOT a valid database command in pb. Unless you are aliasing the DatabaseQuery() command in your code somewhere?
I forgot to copy and paste that procedure. I updated my initial code.
infratec
Always Here
Always Here
Posts: 5565
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Check sqlite db table columns

Post by infratec »

As Fangbeast already written:

You need DatabaseQuery() and not DatabaseUpdate() since you use NextDatabaseRow() to receive the results.
And don't forget FinishDatabase() after DatabaseQuery() :wink:
User avatar
doctorized
Addict
Addict
Posts: 813
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Re: Check sqlite db table columns

Post by doctorized »

Infratec, yes, my bad. I added FinishDatabaseQuery(#db), the code now runs with no errors but it not doing the job. With the below code, txt$ is different from the comparison string, all CheckDabaseUpdate() run with no effect to the table. If I change Update to Query, then I get multiple errors.

Code: Select all

Procedure CheckDB()
	CheckDatabaseQuery(#db,"SELECT sql FROM sqlite_master WHERE tbl_name = 'Students' AND type = 'table'")
	NextDatabaseRow(#db)
	txt$ = GetDatabaseString(#db,0)
	FinishDatabaseQuery(#db)
	Debug txt$
	If txt$ <> "CREATE TABLE Students (id CHAR(15) PRIMARY KEY, Lname CHAR(32), Fname CHAR(32), Class CHAR(4))"
		CheckDatabaseUpdate(#db,"PRAGMA foreign_keys = 0")
		CheckDatabaseUpdate(#db,"BEGIN TRANSACTION")
		CheckDatabaseUpdate(#db,"ALTER TABLE Lessons RENAME To sqlitemanager_temp_table_177354083607")
		CheckDatabaseUpdate(#db,"CREATE TABLE Lessons (id CHAR(13), LName CHAR(30), Class CHAR(4))")
		CheckDatabaseUpdate(#db,"INSERT INTO Lessons (id,LName,Class) Select id,LName,Class FROM sqlitemanager_temp_table_177354083607")
		CheckDatabaseUpdate(#db,"DROP TABLE sqlitemanager_temp_table_177354083607")
		CheckDatabaseUpdate(#db,"COMMIT")
		CheckDatabaseUpdate(#db,"PRAGMA foreign_keys = 1")
		Debug "DONE"
	EndIf
EndProcedure
infratec
Always Here
Always Here
Posts: 5565
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Check sqlite db table columns

Post by infratec »

This is not a code which I can run.
So I can not do any tests.

Add something arround that the call of this procedure makes sense and I will have a closer look.
User avatar
doctorized
Addict
Addict
Posts: 813
Joined: Fri Mar 27, 2009 9:41 am
Location: Athens, Greece

Re: Check sqlite db table columns

Post by doctorized »

infratec wrote: Sun Sep 19, 2021 6:46 pm This is not a code which I can run.
So I can not do any tests.

Add something arround that the call of this procedure makes sense and I will have a closer look.
After many tests I found out the issue with the sqlite syntax that solved the problem. Thank you for your time!
4otomax
New User
New User
Posts: 7
Joined: Tue Sep 21, 2021 2:00 am

Re: Check sqlite db table columns

Post by 4otomax »

I use DB Browser for SQLite for syntax checking. It's free and portable, can make SQL queries and have autocomplete.
collectordave
Addict
Addict
Posts: 1275
Joined: Fri Aug 28, 2015 6:10 pm
Location: Portugal

Re: Check sqlite db table columns

Post by collectordave »

Is it not a lot easier to create a table in the database called Version with two fields Major and Minor.

Then a simple query would fetch the database version for you to check.

Then when you change the database you can update the version table, you could even have a text field in the Version table detailing the changes made.
Any intelligent fool can make things bigger and more complex. It takes a touch of genius — and a lot of courage to move in the opposite direction.
Post Reply