mirhagk/dbsync

A tool to synchronize database tables to XML files, for static and test data to be checked in

dbsync's Issues

Make sure sync works with conflicting constraints

The target table may have records with unique constraints that conflict with new records from the source, but the source may not be conflicting (the target records will be deleted). This should handle those cases without trouble, perhaps this means making sure to delete first.

As an example of the problem, imagine a table with two columns

1. A - PK, int
2. B - UK, int

The source table is

A	B
1	2

and the target table has

A	B
1	1
2	2
3	3

Here if you update record A=1 then you'll have a unique constraint violation if record A=2 isn't removed first.

Order export by primary key

Need to determine what the primary key is and force the export to order by it. This way the export is an idempotent process.

Also the foreign key needs to be determined before the import can be reliable trusted

Encode multiline strings as multiline strings.

The raw xml is difficult to read with multline strings, as the strings get encoded as hideous xml escapes.

Use "OmitXmlDeclaration = true," in XmlWriterSettings to correct.

Unit test demonstrating loopback in this case:

var teststr = @"
this
<is>
  a

    test
    .
";
var ms = new MemoryStream();
var writer = XmlWriter.Create(ms, new XmlWriterSettings()
{
    NewLineOnAttributes = true,
    Indent = true,
    IndentChars = "  ",
    NewLineChars = Environment.NewLine,
    OmitXmlDeclaration = true,
    NewLineHandling = NewLineHandling.None
});
writer.WriteStartElement("root");
writer.WriteAttributeString("multilineTest", teststr);
writer.WriteEndElement();
writer.Flush();
var asStr = System.Text.Encoding.UTF8.GetString(ms.GetBuffer());
asStr.Dump();

XmlReader reader = new XmlTextReader(new MemoryStream(Encoding.UTF8.GetBytes(asStr)));
reader.Read();
Assert.AreEqual(reader.GetAttribute("multilineTest"), teststr);

When importing, pull columns from file, or at least check for matching schema

Support "syncing"

With proper auditing columns and file timestamps (maybe perhaps source control info) it should be possible to determine what has changed in each since the last synchronize, and to synchronize the database with the files, exporting or importing as necessary.

Come up with solution for tables that are logically grouped together

Especially mapping tables

When conflicts/errors occur print helpful messages

When a conflict/error occurs while importing/exporting it should report the problem and give some additional information.

• Primary keys - So the user can see which record failed
• File locations - The path of the files involved
• Line numbers - The line numbers of the problems (see http://stackoverflow.com/questions/621059/current-line-number-from-a-system-xml-xmlreader-c-net)

It could potentially go a step further and do things like prompt the user to resolve the conflict (if it's an interactive session)

Give warnings when merging strategy would cause conflicts

Generate warnings when the target db has conflicting used foreign keys etc.

Timeouts cause error

Use this database:

    create database test
    go
    use test
    go
    create table test(
        ID int not null primary key clustered identity(1,1),
        test nvarchar(max)
    )
    go
    create trigger test_trigger
    on test
    for insert, update
    as waitfor delay '00:02:00'
    go

and this config file (in its own folder)

<?xml version="1.0" encoding="utf-8"?>
<Settings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ConnectionString>server=.;database=test;Integrated Security=True; Connection Timeout=300;</ConnectionString>
  <MergeStrategy>MergeWithDelete</MergeStrategy>
  <AuditColumns>
      <CreatedDate>CreatedDate</CreatedDate>
      <CreatedUser>CreatedUser</CreatedUser>
      <ModifiedDate>ModifiedDate</ModifiedDate>
      <ModifiedUser>ModifiedUser</ModifiedUser>
  </AuditColumns>
  <IgnoreAuditColumnsOnExport>true</IgnoreAuditColumnsOnExport>
  <UseAuditColumnsOnImport>false</UseAuditColumnsOnImport>
  <Job>
    <Name>Config Data</Name>
    <ConnectionString>server=.;database=test;Integrated Security=True; Connection Timeout=300;</ConnectionString>
    <Tables>
      <Table>dbo.test</Table>
    </Tables>
    <Path>.</Path>
    <MergeStrategy>MergeWithDelete</MergeStrategy>
    <AuditColumns>
      <CreatedDate>CreatedDate</CreatedDate>
      <CreatedUser>CreatedUser</CreatedUser>
      <ModifiedDate>ModifiedDate</ModifiedDate>
      <ModifiedUser>ModifiedUser</ModifiedUser>
    </AuditColumns>
    <IgnoreAuditColumnsOnExport>true</IgnoreAuditColumnsOnExport>
    <UseAuditColumnsOnImport>false</UseAuditColumnsOnImport>
  </Job>
</Settings>

and then

dbsync -config config.xml -job "Config Data" --export

and

dbsync -config config.xml -job "Config Data" --import

and you get

DbSync.exe : Error: Error while importing dbo.test: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.At line:1 char:1
        + dbsync -config config.xml -job "Config Data" --import
        + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            + CategoryInfo          : NotSpecified: (Error: Error wh...not responding.:String) [], RemoteException
            + FullyQualifiedErrorId : NativeCommandError

Allow override files

It'd be nice if it was possible to use a file just as an "override' so that you can have a base table shared by different jobs and an override table used just for that job

Add ability to ignore tables on export (or perhaps import)

This is probably a stop-gap solution where DbSync is handling something poorly, but it's a useful thing to have in those situations and it's a quick change.

Treat audit columns properly on import/export

Audit columns should be ignored when specified when export and set correctly when importing

Partition by column

With a column, make it so each value has it's own file. Have a folder for it

Allow specifying merge strategy at the table level

From #13

Load Settings from file

The settings need to be loaded from a file, which will be specified from the command line. The config file will be XML (to match the actual generated files). It will support having a single "job" with one set of settings, as well as having multiple "jobs" with different settings for each. Then the config file can be used to run all jobs, or only certain ones.

Add ByEnvironment table attribute

From #13

This will basically make sure the environment section of the file matches the environment passed in. This is similar to #17 where multiple tables are used (however only one table will be selected rather than all of them)

Make it work with large tables in import script

Have the import script generator work with quotes

Add ability to generate a diff script

For situations where you want to analyze what is being run before it's run, you should be able to generate a script that updates and inserts only what is has to. It should also be able to create a rollback script that reverses the changes.

Make config files combinable

Make it possible to specify multiple config files in a sort of heirarchy. Perhaps a way for jobs to depend on each other, even across config files, would be nice.

Import Script generation doesn't work with overrides

When you specify multiple jobs each of the jobs runs and overwrites the previous script. Even if they appended correctly the override ones don't run under the assumption that the other one already run (so it puts lots of nulls in)

Add ability to generate transaction scripts

With transaction scripts if anything fails the entire script rolls back, making it safer to use for deployments, maintaining ACID compliance.

Allow parallel updating/generation

If the user requests it then allow the tables to be exported or imported in parallel. This may take advantage of using the file system while the database is busy etc, and may also avoid waiting on locks or other things with the database. This may or may not increase performance so testing should be done, and it should not be default.

Make different casing work

Generate import script

If the import does not want to be done immediately it should be possible to generate a sql script that does the import (a self-sufficient sql script)

Change environment specific files to override values in the main file

Currently if the row with an Id exists in the main data file and in the environment specific file, it results in a duplicate id error. It would be nice if the main file had all the values and the environment specific values just overrode them.

Gracefully handle situations where the auditing columns don't exist

The program crashes if the auditing columns don't exist, it should handle this more gracefully, and in general handle missing columns better.

With auditing columns in particular, and missing columns in general, it might be possible to have a switch that allows you to update anyways and have it ignore missing stuff

Add option to ignore environment specific variables

These variables are meant to be per-environment, so if they are modified they might not want to be imported or exported. Add an option to ignore updated values of them on import, and ignore on export.

Also perhaps an option that'll prompt the user to fill in those variables, but that might be better handled by the application itself

Formalize Performance Tests

Performance tests are ad-hoc right now, performance tests should be codified and automated, so they can be run on each check-in