Telerik OpenAccess ORM

A database schema is simply a repository that contains information about the structure and content of the database. It has to be generated before you can store any data.

The VSchema Tool

OpenAccess ORM provides you with a schema generation tool named VSchema. The VSchema tool is used for creating or migrating a relational schema. During the schema creation phase, VSchema reads the assembly file and uses the information it contains to determine which classes have related tables. The information as to how the schema is to be built is taken from the OpenAccess ORM attributes in the assembly (Persistent, ItemType, etc.) and from the application's config file (App.config), which contains the complete mapping definition.

VSchema needs an existing database, for creating or migrating a relational schema.

To generate the database schema, you first compile your .NET source files as you normally would do for any application and enhance the assembly using VEnhance (refer to The OpenAccess ORM Enhancer for more information). The next step is to call the VSchema tool in order to generate the schema files from the assembly files that you have compiled. The following shows a typical migration command line call to VSchema:

	Copy Code
VSchema -assembly:AssemblyFileName -migrate

Command Line Switches for VSchema

Here is the command line syntax for the VSchema tool:

	Copy Code
VSchema [options]

General options:

	Copy Code
-assembly:filename -create -migrate -executeSql -databaseCreate -help -reference:filelist

Schema options:

	Copy Code
-config:filename -connectionId:connectionId -outputDir:directory_name -direct[+|-]

Options for '-create':

	Copy Code
-dropTables[+|-] -validate[+|-]

Options for '-migrate':

	Copy Code
-checkConstraint[+|-] -checkExtraColumns[+|-] -checkIndex[+|-] -checkLength[+|-] -checkNulls[+|-] -checkPK[+|-] -checkScale[+|-] -checkType[+|-]

Options for '-databaseCreate':

	Copy Code
-checkOnly:[+|-]

Options for '-executeSql':

	Copy Code
-SQLScriptFile:filename

The switches are described below:

General options

Schema Options

To maintain the database schema either -create or -migrate must be selected.

Schema Creation

Use the -create switch to create and/or validate the database tables for your model. The VSchema tool will create an SQL script and also optionally drop and optionally recreate all the tables in each datastore, using the connection properties specified in your config file. This will destroy any data in those tables.

Parameters for schema creation:

Schema Migration

Executing SQL Scripts

Executes a referenced SQL statement script file against an existing database, whose schema in an old state; and the script contains the necessary modifications to that schema like altering/creating/deleting a table or modifying columns.

This can to be useful in case of deployment scenarios, where the user wants some special modifications made to the database on the end customers site using a modified script.

Database Creation

This option generates a new database, when it is used along with -direct:+ option. The -databaseCreate generates a sql script for creating a database and the -direct:+ option runs the generated script and creates the database. It is necessary to specify the configuration file (-config) and the connectionid (-connectionid) options along with the -databaseCreate option as shown below:

	Copy Code
VSchema -assembly:MyApp.exe -databaseCreate -direct:+ -config:App.config -connectionId:MssqlConnection1

The above command will generate a database with the name specified in the App.config file (refer to Configuration File for more information). A sql script file using the default naming convention, i.e., Database_Creation_Backend_DateTime (for e.g. Database_Creation_mssql_2006-08-30_15-24-28.sql) will also be generated in the runtime directory of your OpenAccess ORM installation. This file contains the SQL DDL command: CREATE DATABASE DatabaseName.

Usage Examples

Assume, that some persistence capable classes are located in the assembly MyApp.exe, or are reachable from there.

The following call of VSchema can be used to create a new database schema for all persistence capable classes:

	Copy Code
VSchema -assembly:MyApp.exe -create -direct:+ -outputDir:sqlscripts -dropTables -config:App.config -connectionId:MssqlConnection1

Existing tables in the database will be dropped and new tables will be created. Since, the -droptables option has been used, in this case it is necessary that -direct option also must be used, as shown in the example above. The reason for this, is that you cannot drop tables if you are not working directly with the database (i.e. you cannot drop tables if you are just creating a script).

Additionally, the SQL script file, with its default naming convention of Schema_Action_Backend_DateTime, for e.g. (Schema_Creation_mssql_2006-04-05_15-05-05.sql), will be created in the "sqlscripts" directory. The sql script file, contains DDL statements, which can be used for table creation. The connection settings are taken from the application configuration file, App.config, which must contain a connection element, whose "id" is equal to MssqlConnection1.

Dropping all tables is not desirable if there is already data stored for an existing schema. The following call can be used to migrate an existing schema:

	Copy Code
VSchema -assembly:MyApp.exe -migrate -config:App.config -connectionId:MssqlConnection1

This call of VSchema performs all checks on the schema and migrates the existing schema for the database specified in the application configuration file App.config as MssqlConnection1. Any alterations in the schema are written to a SQL script file, containing the SQL DDL commands. The script file in this case will be named using the default naming convention, i.e. Schema_Action_Backend_DateTime (for e.g., Schema_Migration_mssql_2006-04-05_15-05-05.sql).

The create or update sql script file is created in the assembly output directory (bin\Debug) of your project, during Building / Rebuilding your project, if the update database property is set to "True". The script file name will start with Schema_Creation or Schema_Migration followed by the backend and the timestamp (for e.g., Schema_Migration_mssql_2006-04-10_12-41-39.sql).

If it is the first time that you are Building / Rebuilding your project, with the database property set to "True", then another .sql file using the default naming convention, i.e, Database_Creation_Backend_DateTime (for e.g., Database_Creation_mssql_2006-05-31_16-45-46.sql) will also be generated, which will contain the SQL DDL command: CREATE DATABASE DatabaseName.

© 2002-2009 Telerik. All Rights Reserved.