Summary

The purpose of this article is to demonstrate how to configure a basic database export from MooD. In our example we will demonstrate exporting People and Pets, we assume one person can have many pets, so this is a one-to-many relationship.

Database Setup

Connect to the SQL server, and using SQL Server Management Studio, create a new database called TestDB and run the following SQL Statement (New Query), and run the below statements:

Note - the use of nvarchar is important, as the Database Export has known issues with nchar or char datatypes.

USE [TestDB]
GO

CREATE TABLE People(
id bigint IDENTITY(1,1) NOT NULL,
name nvarchar(255) NOT NULL,
CONSTRAINT PK_People PRIMARY KEY CLUSTERED ([id] ASC)
)
GO

CREATE TABLE Pets(
	id bigint IDENTITY(1,1) NOT NULL,
	name nvarchar(255) NOT NULL,
	ownedBy bigint NOT NULL,
 CONSTRAINT PK_Pets PRIMARY KEY CLUSTERED ([id] ASC),
 FOREIGN KEY(ownedBy) REFERENCES People(id)
)
GO

Repository setup 

• Create a new theme called People, and a new type called Person.
• Create a new theme called Pets and a new type called Pet
• Create a Standard Relationship type called OwnedBy, which relates to one Person.
• Add this relationship type to the field list of Pet.
• In the Repository Explorer window, create a few People, and a few Pets.

i.e. People: Sarah, David 

i.e. Pets: Fluffy, Fido, Bubbles

• Relate these pets to owners

i.e. Fluffy->Sarah, Fido->David, Bubbles->David

Configuring your data source

In the Synchronizers view add a new connection to your target database.

e.g. similar to the screen shot below, alter your server as appropriate (do not press the drop down unless you are unsure on the server name), and choose your TestDB in the database list. 

Test this connection to ensure it works.

Tip: If you have problems with this step, try executing the database scripts on your test MooD repository database, so you can easily use 'the current repository' in the next step.

Building the Synchronizer

Synchronizer Settings

You will now need to create the synchronizer and adjust a few settings to ensure a successful export to the SQL database. To begin create a new Database Export Synchronizer called Export. You can select this from the drop down in the top ribbon.

Double click on the export to edit. This will open a new window where you can specify the source and target and modify settings for both.

In the target panel towards the bottom of the screen click "Select the target database..." to add the data source that you created earlier.

From the source panel drag on the People and Pets elements that you created earlier into the Source window. Select "Root X in this theme" for both. 

Drag People and Pets from the Target panel into the Target window. Select the default options for these. Once complete the export should look like this:

Source / Target Settings

With this in place you will need to make few adjustments to the source settings and link up the source and target to successfully run the export. 

Begin by navigating to the Connect tab and select "Field Copy". Use this to link "Person Name" and "Pet Name" to their respective "name" in the Target window. Once complete it should look like this:

Next, click on the cog in the People node under the Source window and select Edit field options...

A pop up will appear. Select the "No empty values?" option for Person Name. Repeat the same step for Pets.

Click the cog on People for a second time and select Change the identifying field.

A pop up will appear. Select Fields>Strings>Name to change the identifying field to Name. Repeat the same steps for Pets.

Navigate back to the Connect tab and select Relationship Copy. Using this drag a line between Pets->OwnedBy folder icon and the foreign key folder icon to Owned By on Pets. Once complete it should look like this:

With this done you can now Validate the export using the Validate button located in the top ribbon. The export should now be valid but you may see a warning about single relationships which you can ignore: 

Execute the synchronizer.

See the results

In SQL Server Management studio, you can now view the data in the People and Pets tables and see that the identify columns were populated by SQL Server and automatically linked up to the People table.

USE [TestDB]
GO
SELECT * FROM People
SELECT * FROM Pets

SELECT Pets.name pet, People.name owner FROM Pets
INNER JOIN People ON Pets.ownedBy=People.id

You should see results as follows: