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.
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.
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) )
- 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
In the Synchronizer view,
- create a new Database Export Synchronizer.
- Call it Export.
- Double click it to edit.
- In the Add panel at the bottom of the view, click Select the target database... and add your data source created.
- Drag on People from the lower Source panel, and drop into the upper Source panel and select Root People in the theme.
- Do the same for Pets.
- Also drag on People and Pets from the lower Target panel, into the upper Target panel and choose the default options.
- In the Connect tab, make a field copy which links Person names in the source to Person names in the target and Pet names in the source to Pet names in the target.
- In the Source, click the cog on the People node, and Edit field options...
- Tick the box No empty values? for Person Name, press OK.
- Do the same for Pets and Pet name.
- Click the cog again on People and Change the identifying field.. to the name. (Under, Fields->Strings->Name).
- Do the same for Pets.
- In the Connect tab, using the Relationship Copy tool, drag a line between Pets->OwnedBy folder icon and the foreign key folder icon to Owned By on Pets.
- Validate the map via the ribbon button - it should now be valid but you may see a warning about single relationships (which you can ignore). Your map should look like this:
- 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.
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: