20.0.0 Generating SQL Datasource Schemas Offline
This article reviews how to build SQL Schemas offline.
Auto Schemas vs. Build SQL Schema
When you connect to a datasource the first time, it reads the schema to that datasource and saves it on your computer. On any subsequent connection to that datasource, it uses the saved schema so you connect instantly without having to wait for it to read the schema from the datasource again (details here...)
On this first connection, when it is reading the schema, your copy of Office is unavailable as this is running as part of the designer. If reading the schema takes 20 seconds, minor annoyance. But if it takes 45 minutes, that's a major problem.
There's a solution to this - Build Sql Schema is a program that is included with the designer as a console application.
Build the Schema
Run BuildSchema with no parameters to get all the options. The following instructions here will show how to do this to hit Windward's Sql Server sample, reading the schema for Northwind.
Build the schema with the following command:
BuildSqlSchema SqlServer server=mssql.windward.net database=northwind username=demo password=demo schema.xml
This will generate the file schema.xml
Auto-generated SQL Schemas
Connect to the same SQL database in the designer, naming the connection SqlServer so it auto-creates the schema file for the same database. After the connect is complete go to the folder C:\Users\%USERNAME%\AppData\Roaming\WindwardStudios\AutoTag\data-source-library. In that folder you will find the file SqlSchema.xml.
This is the schema file for this connection. It is read in directly for all subsequent connections to that datasource.
Setting a BuildSchema schema as the connection schema
To use the BuildSchema schema.xml file as the datasource connection schema you need to do the following 8 steps. It will help to have both of these sample xml files to view as you do this.
- Copy the BuildSchema schema.xml file to the data-source-library folder.
- Rename it to the name you want to use for this datasource.
- Remove from schema.xml the root node "windward-sql-schema" and the node "database"
<windward-sql-schema version="7.1"> <!--type= DB2, MySql, ODBC, OleDb, Oracle, SQL Server--> <!--credentials='identity' OR username= and password=--> <database type="Sql Server" server="mssql.windward.net" database="northwind" username="demo" password="demo"> <ado-connection class="System.Data.SqlClient">Data Source=mssql.windward.net;Initial Catalog=northwind;User ID=demo;Password=demo</ado-connection> <!--place jdbc-connection node here--> </database>
- Replace it with the nodes you see in the top of the auto-generated SqlSchema.xml.
- Critical - the file name, <name> node and name= attribute must all be the same name.
<LibraryItem xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/WindwardArrow.net.windward.arrow.library"> <Name>SqlServer</Name> <ProfileXml> <profile name="SqlServer" root-path="" xmlns=""> <vendor full-type="AdoDataSourceInfo" provider-class="System.Data.SqlClient" provider-name="Sql Server" table-owner="241" server="mssql.windward.net" database="Northwind" username="demo" password="demo" security-token="N/A" jdbc-connection-string="jdbc:sqlserver://mssql.windward.net;DatabaseName=Northwind" ado-connection-string="Data Source=mssql.windward.net;Initial Catalog=Northwind;User ID=demo;Password=demo"/> <sql display="True">
- Under the <views> node you will have a series of <view> elements. Those must be renamed <table>
- For each <stored-procedure> you must surround the set of <parameter> nodes inside a <parameters> node.
- Each <parameter> node needs to be renamed <param>.
We apologize for the changes required. BuildSqlSchema was designed for SQL datasources only. The new system is designed to handle any datasource type and that led to some changes in the schema structure.