If you develop a portal on top of your own database, you can script the whole database using the Template Manager. Including all tables, views, functions, triggers and procedures. So if you deploy or update that Template, the target databases will be modified to your latest version automatically. If you develop a portal on an existing database, you don't need to. Given that you can deploy the template on a similar database.
But what if you add views, functions, triggers and/or procedures to that existing database? How do you make sure that your additions and changes are also applied in your target database?
Introducing the Script Tool
Let's say you have an Exact Synergy database for your configuration. In your development environment you've added some views, created a trigger on a table that executes a stored procedure. If you deploy your configuration on the production database, it might not work since the added views, trigger and procedure are not present in the production database or might be different than in your development environment.
What you would need to do is drop these objects in the production database, and replace them with the most recent version from your development environment.
To make this easier, we are introducing the Script Tool.
Specify what should be scripted
First, specify the names of the Views, Procedures, Triggers and Functions that need to be scripted. Then click the "Generate Scripts" button. The scripts to drop and create the specified objects will be generated in the text area below. Copy and run this script on your target database after you made a backup, of course! When you open the Script Tool, we will automatically put in the last selection you used for this Identifier so you wont have to write them down every time.
Use Drop Object before Create
If one of the specified objects is already present in the target database, it will first be removed (dropped) and then re-created. It is the best way to make sure that the objects in your target database are exactly the same as in your development environment. You can choose however not to add the drop-statement if you are sure none of the objects exist in the target database. In that case we will still add it, but the line will be commented out. That way you have the statement ready if an object does already exist. Just remove the dashes to uncomment the line.
Log the results
Optionally, if you work on a local environment, you can log the results. When generating the scripts, the results will then also be saved to a text file in your Essence\APP_DATA\<Solution>\ScriptTool folder. You can use this option if you want to generate different versions. Or you might want to create one for debugging purposes.
Warning when objects are missing
If one or more of the objects you have specified cannot be found in the selected database, we will show a warning and a counter at the bottom. Just search in the generated script for "warning" and you will find a line like this:
-- Warning: The object specified does not exist in this database: non_existing_object
In that case, do check if you have selected the right identifier and check for typos in the name of the object. If we cannot find it, we are pretty sure it is not there!
So what about tables?
As you can see, we don't drop and re-create tables. For the obvious reason of course that data would be lost in the action. We don't alter tables either. That is because we don't support making permanent, irreversible changes to databases from other manufacturers.
And how about deployment?
You can execute directly on the database you want. But you could also save and upload the generated script to your template for the correct identifier. When updating a Solution that is linked to this Template, the script will automatically be executed for the correct database.