Archives for July, 2009
Shortly: Very.
I hate the idea of instead of triggers and I made a couple of blog posts several times. And even worse is the implementation behavior on MS SQL. And the cascade constraints are bad too.
Let’s suppose this simple definition.
create table master(id int primary key, foo nvarchar(20));
create table detail(id int primary key, id_master int not null, bar nvarchar(20));
alter table detail add foreign key (id_master) references master(id) on delete cascade;
Nothing special. Works fine, no problems expected. Until you try to define instead of delete trigger on detail table. I.e.
create trigger tr_test on detail
instead of delete as
begin
select 1;
-- now the fun begins
end
You get an nice error: Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'tr_test' on table
'detail'. This is because the table has a FOREIGN KEY with cascading DELETE or
UPDATE.. What the hell??? 
Why?
Again I got a direct proof, that in this area the MS SQL is wrong, very wrong. I would be willing to accept the limitation when I’ll be doing there some master table manipulation (yes, as I said, cascade constraints are bad too). But this? I can hardly believe my eyes. The trigger is almost empty. Or am I missing someting on backround that limits this to work?
Another “feature” that makes me love Firebird more.
I hate transactions, ACID and all the stuff. Really, I do. No, I don’t, it’s a great to have challenges with this kind of thinking. But in last two projects (in a row) I had similar problem and transactions were making it even more challenging.
The simple version is that I needed to have data constraint in database (did I mention that I’m constraint freak?) based on not the row itself, but the data in the table and related tables. If you’ve play with transactions you know this is a problem. Either you may run in read uncommitted mode, see everything and if somebody will do something violating the constraint, even if it will be rolled back, you will start screaming and throwing exceptions or you will use serializable and, to be sure, explicit locking in every piece of trigger or stored procedure code, which is good for you but not for performance. Choose whatever fits your needs better, but right now, for this particular scenario I wish the transactions gone and pretending there’s no isolation from others at all (and with decent performance too).
I know it’s impossible, because interactions in database are making creating some general purpose “command/constraint” tough. And what’s good for one case is definitely not suitable for other.
Anyway maybe with more work done on distributed systems and clouds, where even the simple constraints are interesting to enforce, we’ll find some good way how to solve this. 
Astoria so called offline has been released while ago. But until now I have no time and yen for playing with it. When I first heard about offline support for Astoria I was thinking, hmm that’s going to be cool and though that it will be build inside the client itself, with an option to store this information and do offline work when no connection is available – something like “we suppose the connection is mostly available, hence this is only for the few cases when not”.
But not. Astoria offline (at least the preview is based on, of course, regular Astoria and Entity Framework and then Sync Framework.So finally it works like this. You’re doing queries using Entity Framework (local model) and local SQLCE database and if you need to get fresh data and/or push data back, you simply synchronize this source, using Sync Framework, with the webservice. That’s a little bit different from what I was expecting. 
I was playing with MS SQL Server as the source for webservice (but I’m also planing to use Firebird, but that needs some more hand work). Simple master-detail scenario for start (for conflicts and ordering testing it’s enough).
create table sync_master(id int primary key, foo nvarchar(20) not null);
create table sync_detail(id int primary key, id_master int not null, bar nvarchar(20) not null);
alter table sync_detail add foreign key (id_master) references sync_master(id);
If you have your database ready, you’ll add new model and the service. Currently the alpha preview generates you change script, to add some tracking columns into your tables and you’ll simply run it. Good news is that you’ll also get the script with drops and removes, thus after playing you can put tables back into original shape (but I still prefer playing on separate tables, as during testing I screw a lot of stuff). Configuration of service is more or less the same. Only change you have to do, is to allow synchronization.
(config as IDataServiceConfiguration2).AllowSynchronization = true;
That’s all. The service works as classic Astoria (I still get not used to ADO.NET Data Service name). Now the fun stuff comes. You can add any type of application to use the Astoria offline. I started with simple console application, to introduce as less as possible external screwing inputs.
First problem you may encounter is not working “auto setup” for offline work. I did dozen of apps and didn’t find reliable way to make it work always. Close to this I was with these steps (but still not 100%):
- recompile the service
- view it in browser
- access one entity set
- hope for the best
Now when you add service reference to you project the additional process kicks in (should) and generates local SQLCE database, the model and some classes. When I was able to make this work, every time I reached the Adding new database file to project... step I get error message:
An error occurred while processing the local data file:
Exception has been thrown by the target of an invocation.
Clicking OK and ignoring it worked and introduced none (as far as I’m aware of) problems later. No problem, remember it’s alpha preview. With finally all set up, you can start playing.
static void Main(string[] args)
{
using (testovaciEntities ent = new testovaciEntities())
{
sync_master m = ent.sync_master.FirstOrDefault();
if (m != null)
{
Console.WriteLine("Old: {0}", m.foo);
m.foo = "b";
Console.WriteLine("New: {0}", m.foo);
ent.SaveChanges();
}
else
{
Console.WriteLine("No item");
}
}
using (testovaciEntities ent = new testovaciEntities())
{
foreach (var item in ent.sync_master)
{
Console.WriteLine("ID: {0} t Foo: {1}", item.id, item.foo);
}
}
Console.WriteLine("Syncing");
Sync();
Console.WriteLine("Done");
using (testovaciEntities ent = new testovaciEntities())
{
foreach (var item in ent.sync_master)
{
Console.WriteLine("ID: {0} t Foo: {1}", item.id, item.foo);
}
}
}
static void Sync()
{
var serviceSync = new DataServiceSyncProvider(new Uri("http://localhost:1744/WebDataService1.svc"), "global");
var localSync = new ObjectContextSyncProvider(() => new testovaciEntities());
localSync.ConflictHandler =
(ISyncRecord sourceChange, ISyncRecord destinationChange) =>
{
Console.WriteLine("Conflict");
return SyncConflictResolutionAction.SourceWins;
//return SyncConflictResolutionAction.DestinationWins;
};
var so = new SyncOrchestrator();
so.RemoteProvider = serviceSync;
so.LocalProvider = localSync;
so.Direction = SyncDirectionOrder.UploadAndDownload;
so.StateChanged +=
(object sender, SyncOrchestratorStateChangedEventArgs e) =>
{
Console.WriteLine("From {0} to {1}", e.OldState, e.NewState);
};
try
{
SyncOperationStatistics stats = so.Synchronize();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
I was expecting the synchronization to just work, and yep, it does. Anyway I was more interested in some conflicts, ordering etc. Because I was playing with Sync Framework when it was introduced and also done couple of presentations, I wasn’t expecting some problems with ordering.
The conflict resolution is little bit different than in pure Sync Framework, but the idea behind is the same. Simply check SyncConflictResolutionAction enum (there’s no MSDN doc for it right now). The synchronization worked for me as well, with some minor problems. Sometimes the conflict was resolved, but one source kept the old data. Maybe I’m doing something wrong, maybe it needs some support in ResolveSyncConflict on server side too. Never mind, I think in beta it will shine.
OK, that’s pretty much all. It works, have some bugs (but remember it’s alpha preview) and in some scenarios could be really useful. Let’s try the Firebird – Entity Framework (and Astoria) works with Firebird, the Sync Framework too, so how hard can it be …
Nevím, jestli je to jen můj problém/pocit, ale v standardním balíku komponent v .NETu není nic co se by se podobalo něčemu jako „shell tree“, prostě to co má Explorer v okně nalevo. Hledal jsem i před časem na různých místech, ale nic pořádného.
Právě něco jako shell tree používám v ID3 renameru a trochu se tam mixuje přístup, kdy fakticky je třeba pracovat přímo s tím co je na filesystému bez ohledu na všelijaké libraries a packages a speciální složky, na stranu druhou běžný uživatel pracuje s pojmy jako „Plocha“ a „Tento počítač“ (pokud se to jmenuje jinak, omlouvám se, nepoužívám český OS). A tohle všechno výběr komponenty jenom zesložiťuje. Nehledě na to, že XP-Vista-W7, každý systém trochu jiné figurky. To pak aby se tvůrce komponenty zbláznil, když to má transparentně pokrýt.
Přemýšlím proto o jiném přístupu k výběru složky (ID3 renamer se zaměřuje na dávkové zpracování, takže jednotlivé soubory nejsou ve většině případů pro výběr důležité). Stejně rozklikávat postupně strom nemusí být vždy nejrychlejší. Bohužel mě nic nenapadá – koukám na různé programy a nic. Je jasné, že vybraná cesta musí být viditelná, a to dobře a musí jít rychle a lehce změnit, hlavně takové ty změny „okolo“ (jedna složka před a za, …).
Neřešil někdo podobný problém? Možná chytrá cesta využití standardních dialogů … Možná něco vlastního, ale ultra jednoduchého, rychlého a účelného …
Pozn.: Drag’n’Drop samozřejmě ID3 renamer podporuje, ale domnívám se, že to není všelék – musí být i možnost změny složky, se kterou chci pracovat i přímo z programu.
About a month ago there was a question in Firebird (CZ) group how to find whether the user is able to use particular role or to get all roles for user.
As you probably know, a lot of stuff (almost everything) is in system catalog (system tables). The only problem is to figure out what’s the right set of parameters to use. To get all roles with users able to use it, you can use:
select rdb$relation_name as "Role", rdb$user as "User" from rdb$user_privileges
where rdb$privilege = 'M' and rdb$user_type = 8 and rdb$object_type = 13;
The rdb$privilege = 'M' is to get all member of privileges, the rdb$user_type = 8 is about getting records for users and finally rdb$object_type = 13 filters only for roles records.
The rdb$user_privileges table contains all privileges defined for the database – tables, stored procedures, triggers, roles, …, but also take into account that current versions of Firebird (<3.0) are storing users (only, not roles) in system wide security database.
There’re two kinds of people. 1) people doing almost everything in code; 2) people doing everything on database side. I’m in neither of these buckets. I like doing a lot of stuff on database side, because sometimes expressing something in set operations (these are good for RDBMS) is really challenging. On the other way, when it’s easy and fast to do it in code, why bother…
From this few sentences you can guess that I like writing stored procedures. Because of this in project I’m currently working on I created some stored procedures for difficult and expensive searching and I’m mapping results back to entities in Entity Framework. The problem is that this particular entity has a lot of associations. Thus it’s more than likely somebody will need the related entities too. Sadly there’s no Include (stored procedures are not composable by default, so you cannot create left join to fetch the related data). But that was a problem, because loading – using Load method – x related entries for even small with i.e. 20 items results in 20×x calls to database. Although these queries are in most cases cheap, it’s not good for performance.
So I started creating extension methods to get this solved in a little bit better way. My goal was to have one query for one related end for all items in result. Hence there will be only x additional queries. The result is here:
Disclaimer: The code is not general purpose and contains some assumptions based on my conditions and rules.
public static IEnumerable<T> LoadRelated<T>(this ObjectResult<T> result, MergeOption mergeOption, params Func<T, IRelatedEnd>[] relatedEnds)
where T : EntityObject
{
return LoadRelatedStarter(result, mergeOption, relatedEnds);
}
public static IEnumerable<T> LoadRelated<T>(this ObjectResult<T> result, params Func<T, IRelatedEnd>[] relatedEnds)
where T : EntityObject
{
return LoadRelatedStarter(result, MergeOption.AppendOnly, relatedEnds);
}
private static IEnumerable<TEntity> LoadRelatedStarter<TEntity>(ObjectResult<TEntity> result, MergeOption mergeOption, params Func<TEntity, IRelatedEnd>[] relatedEnds)
where TEntity : EntityObject
{
result.EnsureNotNull();
TEntity[] tmp = result.ToArray();
relatedEnds.EnsureNotNull();
relatedEnds.EnsureEachNotNull();
if (tmp.Any())
{
for (int i = 0; i < relatedEnds.Length; i++)
{
ObjectQuery query = relatedEnds[i](tmp[0]).CreateSourceQuery() as ObjectQuery;
Type related = query.GetType().GetGenericArguments()[0];
typeof(Extensions)
.GetMethod("LoadRelatedHelper", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Static)
.MakeGenericMethod(typeof(TEntity), related)
.Invoke(null, new object[] { tmp, query, mergeOption });
if (i == relatedEnds.Length - 1)
{
FixAssociations(query.Context, tmp);
}
}
}
foreach (TEntity item in tmp)
{
yield return item;
}
}
private static void FixAssociations<TEntity>(ObjectContext context, IEnumerable<TEntity> entities)
{
// this is a workaround to make associations wire up properly
context.Refresh(RefreshMode.ClientWins, entities);
}
private static void LoadRelatedHelper<TEntity, TRelated>(ICollection<TEntity> entities, ObjectQuery<TRelated> query, MergeOption mergeOption)
where TEntity : EntityObject
where TRelated : EntityObject
{
string separator = string.Format("{0}) union ({0}", Environment.NewLine);
// In general this may produce wrong results
string queryTemplate = query.CommandText.Replace(query.Parameters.First().Name, "{0}");
StringBuilder newQuery = new StringBuilder();
newQuery.AppendLine("(");
for (int i = 0; i < entities.Count; i++)
{
if (i > 0)
newQuery.Append(separator);
newQuery.Append(string.Format(queryTemplate, string.Format("p{0}", i)));
}
newQuery.AppendLine();
newQuery.Append(")");
// I know (from my design rules) that there's only one (key) param
ObjectParameter[] parameters = entities.Select((x, i) => new ObjectParameter(string.Format("p{0}", i), x.EntityKey.EntityKeyValues[0].Value)).ToArray();
query.Context.CreateQuery<TRelated>(newQuery.ToString(), parameters).Execute(mergeOption).ToArray();
}
The idea is pretty simple. For each related end, grab the query and instead of using one parameter, add there all the value for all items in result – the primary key columns. I’m actually parsing the Entity SQL query returned to me, although with MetadataWorkspace one should be able to create it yourself (you can do it as a homework ). Then I modify the query, fill the parameters and execute it. Again, the parsing isn’t perfect, as well as the work with keys for parameters – there’s a simplification based on my conditions and rules.
With the automatic association wiring (used also in this trick) this should work nicely. It works like a charm for ObjectQuery, but not for ObjectResult (in EFv1). I don’t know, maybe it’s a problem on my side – anyway it’s reported in EF forum, so far without reply. If you read the second post there from me, you’ll find the workaround I found. This idea is captured in FixAssociations method (and may result in a huge or clause for big results).
These methods are taking array/params of related ends to get all in one method. And finally you get the result back and you can start processing it. The result is, for me, acceptable, I ended up with x+1 queries (with i.e. EFExtensions, I think, one can be able to do this with even lower number of queries).
Anyway, hope this helps and hope I find why the associations are not wired up properly. Maybe some considerations to improve this scenario are worth to discuss for post-EF4, as as far as I know, there’s no improvement on this in EF4.
Note: EnsureXxx are my runtime validation extension methods, similar to Code Contracts.
Today I was writing some code, where I needed create initially empty array. I wondered if the array[0] vs. array[] { } is the same. Alike with the string concatenation.
Without writing further text. Lines:
private static void Test1()
{
string[] s1 = new string[0];
Console.WriteLine(s1);
}
private static void Test2()
{
string[] s2 = new string[] { };
Console.WriteLine(s2);
}
resulted in this IL:
.method private hidebysig static void Test1() cil managed
{
// Code size 14 (0xe)
.maxstack 1
.locals init ([0] string[] s1)
IL_0000: ldc.i4.0
IL_0001: newarr [mscorlib]System.String
IL_0006: stloc.0
IL_0007: ldloc.0
IL_0008: call void [mscorlib]System.Console::WriteLine(object)
IL_000d: ret
} // end of method Program::Test1
.method private hidebysig static void Test2() cil managed
{
// Code size 14 (0xe)
.maxstack 1
.locals init ([0] string[] s2)
IL_0000: ldc.i4.0
IL_0001: newarr [mscorlib]System.String
IL_0006: stloc.0
IL_0007: ldloc.0
IL_0008: call void [mscorlib]System.Console::WriteLine(object)
IL_000d: ret
} // end of method Program::Test2
As you (and I) can see, the code is the same (I was kind of expecting that – but what’s better proof than IL?). So you don’t need to worry using the first or the other syntax.
While reading Mike Taulty’s posts about playing with Windows 7 and multitouch some words like deceleration, inertia etc. started some process in my head and I recalled some very old program everybody was creating. I don’t know if everybody, but at least 99,9% of Delphi developers.
The idea behind program was pretty simple. No window, somehow hook the mouse (I don’t remember whether it was classic hook, because it was in Win98/95 era). And everytime you put the mouse to the top, it started falling down, like if the gravitation worker for mouse cursor too. I remember too some modification, that if you done some horizontal move with mouse it had some inertia and decelerated. So it was fun try to click on some button or similar stuff. And if you put the mouse to the top of the screen with this move, it was falling down with parabola path.
Sure, the code and/or program was absolutely useless, but it was a great rest during long day at work. I wish I found the program somewhere now and look at it.
.NET Framework 4 comes with (among others) with two new interfaces. IStructuralEquatable and IStructuralComparable. These are implemented (right now in Beta 1) by Array and Tuple(s).
With this new implementations and StructuralComparisons you can check arrays and tuples for structural equality (or compare these).
object[] o1 = new object[] { 1, "2" };
object[] o2 = new object[] { 1, "2" };
Console.WriteLine(o1.Equals(o2));
Console.WriteLine(o1.Equals(o2, StructuralComparisons.StructuralEqualityComparer));
The code above writes first false and then true. The first one is classic “old-school” Equals. Following line is using new structural comparison, thus the true as result. Neat, isn’t it?
By the way, F# is now using these interfaces too.
I was punch directly to my face right now. Everytime I’m doing some ADO.NET Data Services (Astoria) speak I’m showing simple models, with almost no modifications. And everything works great. But I found, right now, that if you have entity in your model with property access getter or setter setted to anything except public, the service will not work. Grrr.
I understand that hiding setter causes the updates or inserts to stop working. But why the getter? If I limit my entity set to i.e. AllRead, then the querying should just work. Hmm, it’s probably related to missing select support (in 3.5SP1 as well as 4.0).
Anyway it was really “interesting” to find this out.
Current version of ADO.NET Data Services doesn’t support Select method. Neither the .NET Framework v4. This may cause you problems when your entity contains relatively big blob fields, like photo of user in users table/entity. You can do almost nothing with it.
Today I was facing exactly this problem. While inspecting some easy workarounds I found the problem with non-public members in entity. But I came with another solution. It’s not directly solution to missing Select method support, but kind of workaround for blob fields, mainly focused on web usage.
First I deleted the blob column (in my case Photo) from my entity (in my case User). Then I created stored procedure to return this field from database and created Function import. If you’re working with EF4, then the code for invoking the procedure is generated for you, in EFv1 you have to create it yourself as the procedure returns non-entity result. For this method I created Service Operation method with WebGet and SingleResult attributes, plus MimeType attribute. The method I returning directly byte[] (from .NET 4 you can directly return primitive types).
Sure, this field must be nullable or has some default value to support insert. For update the blob field you have to write another separate method. Yep, some manual work.
Removing the field from EDM (remember you can have more models in you app, hence you don’t have to change your model for desktop app for example) has some drawbacks and limits you. On the other hand, creating the separate method has some advantages. With all these attributes declared, you can call http://something/Service.svc/GetUserPhoto/$value?id=x and get the result back directly, with proper mime type. So you can easily use it in <img /> tag without any further processing (yes, with you could do http://something/Service.svc/Users(x)/Photo/$value, but that means not deleting the “Photo” property from model and when asking for entity getting all the binary data to the client, which is what I’m trying to avoid).
It’s not the best solution one can imagine, sure. But works for selecting as well as updating/inserting, with only small limitation and small amount of extra work.
Here’s the complete code, if you wanna try (no updating/inserting support, from EDM just Photo deleted):
[MimeType("GetUserPhoto", "image/jpeg")]
public class SelectTest : DataService<TestEF>
{
public static void InitializeService(IDataServiceConfiguration config)
{
config.SetEntitySetAccessRule("*", EntitySetRights.All);
config.SetServiceOperationAccessRule("GetUserPhoto", ServiceOperationRights.All);
}
[WebGet]
[SingleResult]
public byte[] GetUserPhoto(int id)
{
return this.CurrentDataSource.GetUserPhoto(id).FirstOrDefault();
}
}
create table Users (ID int primary key, FirstName nvarchar(255) not null, LastName nvarchar(255) not null, Photo varbinary(max));
go
create procedure GetUserPhoto(@ID int)
as
begin
select Photo from Users where ID = @ID;
end
go
