Real Software Forums
http://forums.realsoftware.com/

Couple of database questions
http://forums.realsoftware.com/viewtopic.php?f=3&t=47761
Page 1 of 1

Author:  WonkeyDonkey999 [ Thu May 02, 2013 5:58 am ]
Post subject:  Couple of database questions

Ok, so after a very long time away Im getting back into Realbasic on my mac.

First question is about opening a database in code. I've tried to narrow it down from the many examples I have found. The code I currently have is this :

dim db as new REALSQLDatabase
db.databaseFile = getFolderItem( "Availability.rsd" )

If db.databaseFile.exists then
If db.connect then
MsgBox "Found database, connected ok"
Else
MsgBox "Found database, but unable to connect"
End
Else
MsgBox "Database not found"
End


So, is the code above considered acceptable ? It certainly works here in the way I expect it to and if I rename the database file or change the filename in the code it also reacts as expected. Eventually I will remove the MsgBox's and add more appropriate code to display the results somewhere in my main window.

The next question is regarding an actual database table I want to create, but is equally appropriate for any table I believe.

The system I am working on first presents the user with a username & password box. Once credentials are entered they will eventually be checked against a table containing that information. Another action from this is to enable me to log certain actions carried out by that user, a kind of audit trail of activity.

The audit trail table will be quite simple; it will contain four fields which represent the following:

Username
Date
Time
Occurence

Having read about the way tables are created, a field called 'rowid' is added internally at creation time. The sqlite3 website suggests that the contents of this field can not be guaranteed if it is created that way, and that the better solution would be to add a field called rowid, where its contents would then be guaranteed.

So, is it better to add 'rowid' myself, and if so what parameters need to be set for it to work correctly ?

At the moment Im thinking PRIMARY KEY, MANDATORY, INDEXED and AUTOINCREMENT with a type of INTEGER, although something lingering in my mind thinks by setting PRIMARY KEY it is automatically indexed anyway, but Im not sure. Are these correct or too much ?

Finally, the Username mentioned above, Im thinking the best way is to insert that into a small global array, so that whenever an occurence takes place that needs to be logged, the username can be grabbed from the array and added to the record that is created in the audit table. Would that be a reasonable way to do this ?

Im kind of just finding my feet again with this stuff so please be gentle :wink:

Author:  timhare [ Thu May 02, 2013 12:50 pm ]
Post subject:  Re: Couple of database questions

WonkeyDonkey999 wrote:
So, is the code above considered acceptable ?

The structure of the code is correct. The only possible issue would be accessing a database inside the application's folder. That may or may not be allowed by the OS. You might consider installing the database elsewhere, such as SpecialFolder.ApplicationData.

Quote:
So, is it better to add 'rowid' myself, and if so what parameters need to be set for it to work correctly ?

At the moment Im thinking PRIMARY KEY, MANDATORY, INDEXED and AUTOINCREMENT with a type of INTEGER, although something lingering in my mind thinks by setting PRIMARY KEY it is automatically indexed anyway, but Im not sure. Are these correct or too much ?

It is better to add the column yourself. "rowid AUTOINCREMENT PRIMARY KEY" is sufficient.

Quote:
Finally, the Username mentioned above, Im thinking the best way is to insert that into a small global array, so that whenever an occurence takes place that needs to be logged, the username can be grabbed from the array and added to the record that is created in the audit table. Would that be a reasonable way to do this ?

I don't think an array is necessary. Use a globally accessible string property.

Welcome back, and happy coding.

Author:  WonkeyDonkey999 [ Thu May 02, 2013 5:12 pm ]
Post subject:  Re: Couple of database questions

Many thanks Tim, I'm slowly getting the hang of errm, bits of it :lol:

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/