Hi there,
I have created a new dataase and populated it via code (as opposed to using the menu). This means that I have dont have the database listed in the contents. As I am wanting to add more tables etc, how do I go about clearing the existing data easily (not via code) or remove the database completely so that the db creation will take place again.
Is there a way to get the internal (SQLite) db to show in the contents like it does if you insert via the menu.
Thanks
Adam
Never have inserted a database that way (didn’t realize you could until just now, and not really sure WHY you would want to), as it would seem to be more difficult to “manage it” .
I always create my databases in code, populate them in code, and manage them in code (SQL to be precise)
This way you can delete the DB file to destroy it , or just use SQL “DROP TABLE”, or “DELETE FROM” as appropriate
Yes I agree that it is better to have in initiated in code. However, my initiation looks for the db and if it doesnt exist then creates and and creates the necessary tables etc.
So what I am really asking, is: Is there an easy way to run updates so that as I add functionality I don’t lose existing data. I know there are a number of ways to achieve this but hoping there might be a preferred or recommended way to do so with xojo.
For now I guess I will write a method to delete the file and retrigger the process to recreate.
Thanks
Certainly.
a) You App Starts
b) does database FILE exist? if YES then GOTO D
c) Create Database File
d) Check a database table called “DB_VERSION” against a program CONSTANT holding current version supported
e) If DB_VERSION matches APP’s db version then GOTO G
f) For each table that needs to be altered… perform an ALTER TABLE (note this may require building a new table, moving data, deleting the old table, and renaming the new one, if using SQLite,)
g) Database is up to date… move on
The Application does not contain the database, it resides (as it rightfully should) as a file external to the application, this database is created using “CREATE DATABASE”, and its tables are created using “CREATE TABLE”
You also have the ability to control if that database is encrypted or not
This is exactly the process I use in DS_LOCKBOX, which has a database with over 50 tables. It can be completely build via SQL code in XOJO in about 10 seconds, and once built takes a fraction of a second to open and connect
Thanks Dave. The steps I have done in my app are much the same as yours except for points D & E.
The only thing I havent seen is the db file being created as a file alongside my app - it obviously is as it is all working… but cant see it in filesystem.
If could add to what Dave has replied, if the database is not the current version, then a new blank temporary DB is created and the data from their table is inserted into it using a number of SQL insert statements.
Using this procedure we can add new tables, new fields, delete fields, delete tables, whatever is necessary. Naturally, it may be necessary to add some code to populate new fields or altered fields.
We’ve gone the extra step of creating our own database manager that will generate a MASTER.DB in a special format that all our applications can use perform the “update” process. Yes, this took quite a bit of time to perfect, but now it is much easier to print out application file structures, make changes to it, and push out a new updated DB.
If your plan is to develop multiple applications, spending the time doing this is well worth it.
You won’t see it in the filesystem unless someplace in you app you told it WHERE to put it,other wise it very well could be creating an IN-MEMORY database… This is why I always do this kind of stuff in code… that way I know exactly what is happening, when it is happening and where it is happening.
I probably didn’t go to the extent that John did, but I have a Database Module that I created a few years ago, and that is included in all my projects… It contains the connection code, error handling, basic table functions etc., basically all the “grunt work” stuff
This is my db creation script:
MentorDBFile = SpecialFolder.ApplicationData.Child("MentorDB")
If MentorDBFile <> Nil Then
MentorDatabase = New SQLiteDatabase
MentorDatabase.DatabaseFile = MentorDBFile
If MentorDatabase.CreateDatabaseFile() Then
If MentorDatabase.Connect Then
CreateSchema
Return True
Else
Return False
End If
Else
Return False
End If
End If
MentorDBFile is a property (FolderItem)… so I guess it should be file based rather than in-memory. Sorry all new to me - on day 2 
Then you should see MentorDB as a file in
“/Users//Library/Applications Support”
nope no file there. and search doesnt show up anywhere… could it be an in-memory until I “Build”. At the moment I am just "run"ning the app in debug mode.
Put a breakpoint after setting the folder item the inspect it’s contents during your debug run looking at the native path value. This will show you exactly where the db is being created. You should also use the mentordb.exists value to determine if the file already exists & use connect rather than create to reuse the file.
Thanks Wayne. Found it using breakpoint. Also, I have another method called openDatabase which does the checking for existance before creating:
Function OpenDatabase() As Boolean
MentorDBFile = SpecialFolder.ApplicationData.Child("MentorDB")
If MentorDBFile.Exists Then
MentorDatabase = New SQLiteDatabase
MentorDatabase.DatabaseFile = MentorDBFile
If MentorDatabase.Connect() Then
Return True
End If
Else
Return CreateDatabase
End If
End Function