mirhagk / dbsync Goto Github PK
View Code? Open in Web Editor NEWA tool to synchronize database tables to XML files, for static and test data to be checked in
License: MIT License
A tool to synchronize database tables to XML files, for static and test data to be checked in
License: MIT License
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
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.
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
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);
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.
Especially mapping tables
When a conflict/error occurs while importing/exporting it should report the problem and give some additional information.
It could potentially go a step further and do things like prompt the user to resolve the conflict (if it's an interactive session)
Generate warnings when the target db has conflicting used foreign keys etc.
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
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
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.
Audit columns should be ignored when specified when export and set correctly when importing
With a column, make it so each value has it's own file. Have a folder for it
From #13
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.
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 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.
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)
With transaction scripts if anything fails the entire script rolls back, making it safer to use for deployments, maintaining ACID compliance.
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.
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)
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.
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
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
Performance tests are ad-hoc right now, performance tests should be codified and automated, so they can be run on each check-in
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.