Update a database

Top  Previous  Next

To explain this option, let's imagine a common scenario. Your pet programmer or software supplier gives you a lovely behavioural task. The task knows that it should store its data in a database (as well as in text files, just to make sure no data ever get lost). The task knows the names of the tables and the fields (columns) in those tables. For example, it might want to store the total number of responses in a task involving simple schedules of reinforcement, and it might know that its database should contain the SimpleSchedules_ResultSummary.TotalResponses field (that is, the field named TotalResponses in the table named SimpleSchedules_ResultSummary). All well and good - and with the task itself is supplied a prototype database - i.e. one that contains no data, but contains the table structure that the task needs. You are not recommended to store data in the prototype database, in case you accidentally delete it while cleaning out your C:\Program Files directory, but you are recommended to make your own copy of the prototype, or several copies for different experiments, and use those copies.


But now suppose you receive a software upgrade. Let's suppose you use the MonkeyCantab test battery. Now, suppose you or somebody else requests a new feature (for example, the ability to rotate target stimuli in the delayed matching/nonmatching to sample or DMTS task). And then you receive a new version of MonkeyCantab, with a brand new prototype database. There may be a new field in the prototype database, such as DMTS_Config.UseRotation.


If you use the old task and your current working copy of the (old version of the) database, all is fine.
If you use the old task and a copy of the new prototype database, all is fine (since a field has been added, not deleted, in the new version).
If you use the new task and a copy of the new prototype database, all is fine.
But suppose you want to use the new task, but continue adding data to the same database as before? Well, you need to add this new field, DMTS_Config.UseRotation, because the new task will be expecting it. The Whisker Database Manager does this for you.


To use this feature, the new prototype database and the old (working) database must both be registered with ODBC. Then, simply choose the Data Source Names (DSNs) of the new (source) database, and the destination database (the one you want to upgrade):




Then click Proceed.




The differences between the databases will then be analysed, and commands generated in a universal database language called SQL (Structured Query Language) to make the destination database compatible with the source database. Various tick boxes are provided to alter the SQL slightly, since some databases (notably MS Access 97) don't accept standard SQL exactly. In the example below, there are no CREATE TABLE commands (so there are no whole tables that are present in the source but not the destination), but there are eight fields (columns) that are present in the source database's DMTS_Config table that aren't yet in the destination database's version.




It may be that a field exists in both the databases but the field has a different type in each database. For example, if Table1.Field1 in the source database is of type VARCHAR(50) (meaning variable-length character data, or text, up to 50 characters long) and Table1.Field1 in the destination database is of type INTEGER (i.e. whole numbers), then there is an incompatibility. The Whisker Database Manager highlights these in the bottom edit box, and will not attempt to alter those fields (because that might cause loss of data). It's up to you to deal with this problem manually.


Click OK to make the proposed changes. If any errors are generated, a window will pop up showing the SQL command that failed, so you can work out what the problem might be.