Database

From Dragon Age Toolset Wiki
Revision as of 22:48, 23 May 2010 by NErWOnek (Talk | contribs) (Creating a blank database from scratch)

Jump to: navigation, search

The toolset uses a named SQL instance, so you want to connect with your client using the format:

.\BWDATOOLSET

This is the default, but it's possible to install or configure the toolset to use a different database. To find out what your database server is called, go to control panel->Administrative Tools->Services and look for a service that is called something like: "SQL Server". The name of the server is the name you need to use for this.

The toolset uses a registry setting for the database connection. The toolset configuration tool normally edits this so you shouldn't ever need to do it manually, but it can be found at HKLM\Software\Bioware\Dragon Age\Toolset\Common\MSSQL_IO\ServerName (64 bit needs HKLM\Software\Wow6432Node\Bioware\Dragon Age\Toolset\Common\MSSQL_IO\ServerName ).

Backing up your database

Backing up a database is not as simple as just copying a file. You'll need to export the data from the database in a more portable form.

There are currently three approaches to backing up your database.

SQL Express Management Studio

Go to Microsoft and download the SQL Express Management Studio for free.

This will allow you to have a more friendly GUI interface into the toolset's database. It also means you can cause damage to the database as well, so be cautious with this tool. After registering your server you can backup your database to a file, in the same manner that the initial database has been packaged for the installer. In the future you can then restore that backup over top of the newly created database and then apply the migration scripts.

Batch file and command line

You can create a batch file with the backup command in in.

You can then run the batch files to backup your database and restore it, with you controlling the file names and such.

The toolset comes with a simple batch file for backups and restores, you can find it in the Dragon Age\tools\DatabaseUtilities\Backup_Restore directory. Edit the "config.ini" file to set the names and locations for database backup and restore files.

Builder to builder export

In the toolset you can go to the menu and select Tools->Export Database (Database Export) This will allow you to save out the entire source data into an xml file. When you reinstall you can then go to tools->Database Import and bring all the data back in. This one is a bit more work in that it will recreate everything and not put everything back the way it was. A small difference but still a difference.

Useful queries

Conversation Search

SELECT m.Name, uri.Folder, uri.ResRefName, st.Text, cl.Icon, st.StringID
FROM dbo.v_ModuleTree mt 
JOIN dbo.t_Module m ON m.ID = mt.AssociatedModuleID AND mt.ModuleID = @ModuleID
JOIN dbo.v_UserResourceInfo uri ON uri.ModuleID = mt.AssociatedModuleID 
JOIN dbo.t_ConversationLine cl ON cl.ModuleResRefVersionID = uri.ModuleResRefVersionID 
JOIN dbo.t_StringText st ON cl.TextStringID = st.StringID AND cl.ModuleResRefVersionID = st.ModuleResRefVersionID
WHERE st.Text LIKE '%' + @Search + '%'
AND ( cl.Icon = @Icon OR @Icon = -1 )
ORDER BY m.Name, uri.Folder, uri.ResRefName, cl.Icon 
OPTION ( force ORDER )

Script Search

SELECT m.Name, uri.Folder, uri.ResRefName, uri.resrefid, 
'... ' + substring( s.text, patindex('%' + @Search + '%', s.Text) -30, len(@Search)+60 ) + ' ...' as matched_on
FROM dbo.v_ModuleTree mt INNER JOIN
dbo.t_Module m ON m.ID = mt.AssociatedModuleID AND mt.ModuleID = @ModuleID INNER JOIN
dbo.v_UserResourceInfo uri ON uri.ModuleID = mt.AssociatedModuleID INNER JOIN
dbo.t_Script s ON s.ModuleResRefVersionID = uri.ModuleResRefVersionID
WHERE (s.Text LIKE '%' + @Search + '%')
ORDER BY m.Name, uri.Folder, uri.ResRefName OPTION (force ORDER)

Creating a blank database from scratch

To set up a blank new database (or if you'd like to examine the schema that an existing database is built on), there are two SQL scripts that need to be run.

  • Content Create Script.sql sets up the tables. If you have a slow connection note that this page is over half a megabyte in size.
  • Core Data Script.sql puts some basic core data into the tables that the toolset needs in order to run.
The interface for working with roles in your database

Before running them you'll have to ensure that your database has the following three roles:

  • bw_db_read
  • bw_db_write
  • bw_db_lock


You may get three warnings that begin "Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table...". These are not a problem, the script is creating stored procedures that refer to tables that only get added later in the SQL script.

Note: as of version 1.01 of the toolset, upon first being installed the toolset will default to the "Demo" module when it can't find the previously-open module in the database. This script doesn't create the Demo module, so the toolset will give an error message and fail to run. To fix this, open the DefaultSettings.xml file in Dragon Age\tools and change

<CampaignID>3</CampaignID>

to

<CampaignID>2</CampaignID>

(CampaignID 2 is the Single Player module.)

You'll then be able to start the toolset and change modules as you please.

If you wish to attempt to import the core and single player campaign resources you can find a DADBDATA file containing them at [1].


Also: For international users (If Your sql server 2005 uses different database collations by default):

When creating db_dragonage_content please remember to change collation from Your local one to:

SQL_Latin1_General_CP1_CI_AS

this is the only collation that guarantees You that those .sql batch scripts will work properly.

Setting up SQL Express 2005 manually

See Manual database installation.

Troubleshooting

Installation troubleshooting has information for troubleshooting database issues that arise during installation, and that might also be applicable post-installation.