Archive

Posts Tagged ‘sql server’

Magiq: BulkCopy support

February 19, 2010 Leave a comment

With all the behavior stuff, I have normalized the design in favor of new Magiq providers. Now there’s a MagiqToDatabases abstract class that only needs a few methods to implement. Also, there is a normalization in the operations: Now Magiq have the classes DatabaseInsert, DatabaseUpdate, DatabaseQuery and DatabaseDelete, which contains properties like the columns to update, the expressions of the values, etc.

For implementing BuilkCopy, there was only two things needed to override:

  • The way Magiq-to-objects inserts into a SqlServer based destination: For example, for a Linq-to-Sql table, the way Magiq-to-objects inserts is using the Insert method of Table. The idea is to override that with a SqlBulkCopy. Note: this “way Magiq-to-objects inserts” is called IInsertDestination.
  • Use magiq-to-objects instead any SqlServer insert strategy defined: For using BulkCopy when the source belongs to the same provider, avoiding the regular insert…select… statement

So, following the behavior schema, Magiq provides two interfaces for all this: IObjectsInsertBehavior and IInsertBehavior.

  • IObjectsInsertBehavior provides the way for overriding the IInsertDestination.
  • IInsertBehavior provides the way for overriding the strategy to use, so we can avoid using the default strategy

Also, it needed a way to know if the destination is a SqlServer. Well… all the database operations and strategies implements an interface called IDatabaseBasedExecution that provides a IDatabaseAdapter. And this last interface give us a IDialect that could be a SqlServerDialect.

Ok, ok, but show me some code!!

public class BulkCopy : MagiqBehavior, IObjectsInsertBehavior, IInsertBehavior
{
	public int BatchSize { get; set; }
	public int Timeout { get; set; }

	public BulkCopy()
	{
		BatchSize = 0;
		Timeout = 30;
	}

	private static bool Applies(IDatabaseBasedExecution execution)
	{
		return execution != null && execution.DatabaseAdapter.Dialect is SqlServerDialect;
	}
	
	public IInsertDestination<TNew> GetInsertDestination<TSource, TDestination, TNew>(IInsert<TSource, TDestination, TNew> insert)
		where TDestination : class
		where TNew : class, TDestination, new()
	{
		var insertDestination = Next<IObjectsInsertBehavior>().Call(x => x.GetInsertDestination(insert));
		var databaseOperation = insertDestination as IDatabaseBasedExecution;

		if (Applies(databaseOperation))
			return new BulkCopyDestination<TSource, TDestination, TNew>(insert, databaseOperation.DatabaseAdapter)
					   {
						   BatchSize = BatchSize,
						   Timeout = Timeout
					   };

		return insertDestination;
	}

	public IInsertStrategy GetInsertStrategy<TSource, TDestination, TNew>(IInsert<TSource, TDestination, TNew> insert) where TDestination : class where TNew : class, TDestination, new()
	{
		var strategy = Next<IInsertBehavior>().Call(x => x.GetInsertStrategy(insert));
		var databaseOperation = strategy as IDatabaseBasedExecution;

		if (Applies(databaseOperation))
			return new InsertStrategy<TSource, TDestination, TNew>(insert);

		//overrides the strategy with magiq-to-objects
		return strategy;
	}

	public int Execute(IInsertStrategy strategy)
	{
		//default behavior
		return Next<IInsertBehavior>().Call(x => x.Execute(strategy));
	}
}

And the way to use it:


var sellsFromSomeRepository = ...;

records.From(sells)
       .Using<BulkCopy>()
       .Insert(x => new Record
                     {
                        Type = RecordType.Sell,
                        Description = "Sell from " + x.Date,
                        Amount = x.Total
                     });

And of course, if you use BulkCopy for inserting into a non SqlServer repository, the plugin continues without changes, so it could be used in a testing environment in which the repositories are setted to a List.

Magiq: Updating associations in batch challenge result

December 17, 2009 3 comments

In a the post Magiq: Updating associations in batch challenge I tried to solve an issue with mssql. Basically, I wanted to update 2 tables in the same query, because the update itself changes some columns used in the where clause. Greatfully, it had several answers that led me to a solution:

There are two kind of property sets: the ones that invalidates the where clause and whe ones that doesn’t. Since there’s a way to find out if a property expression is used in another expression (visiting the expression tree or just manipulating strings), it’s easy to mark them. Magiq should execute first the ones that doesn’t invalidate the where clause. For the invalidating ones, I will use a table variable but if there’s only one of them, then it will execute a single update (because there’s nothing else to update). I’m not worried about using table variables, because in other databases the feature is fully supported.

Thanks to everyone that commented yesterday, it was very helpful!

Magiq: Updating associations in batch challenge

December 16, 2009 20 comments

Today I started working in a new Magiq feature, batch update of associations.
The idea is to be able to run this:

contacts.Where( x => x.FirstName == "Ivo" )
        .Set( x => x.City.HasPeopleNamedIvo, true ).Update();

I know the example doesn’t make any sense, but it’s fine for the explanation purpose.
It was easy to implement in Magiq-to-objects (it needed some expression manipulation, but harmless). The problem begun when I tried to update a property used in the where condition using magiq-to-sql. Basically, for this magiq sentence:

contacts.Where( x => x.FirstName == "Ivo" && !x.City.HasPeopleNamedIvo )
        .Set( x => x.FirstName, "IvoWiblo" );
        .Set( x => x.City.HasPeopleNamedIvo, true )
        .Update();

it’s generating these queries:

UPDATE [Contacts] SET [t0].FirstName = @p1
                FROM [Contacts] AS [t0]
                LEFT OUTER JOIN [Cities] AS [t1] ON [t0].CityId = [t1].Id
                WHERE [t0].FirstName == @p0 AND [t1].HasPeopleNamedIvo = @2
UPDATE [Cities] SET [t1].HasPeopleNamedIvo = @p1
                FROM [Contacts] AS [t0]
                LEFT OUTER JOIN [Cities] AS [t1] ON [t0].CityId = [t1].Id
                WHERE [t0].FirstName == @p0 AND [t1].HasPeopleNamedIvo = @2

EDIT: I changed a little bit the queries just to reflect that “changing the order of the sets” is not the solution.

As you can see, once the first SQL is executed, the second one updates nothing, because there’s no more people with that FirstName.

I have not idea how to solve it, do you?

HasPeopleNamedIvo