Monthly Archives: September 2012

Default transanction isolation level in PHP for Firebird

1 Reply

I was hunting some problem in PHP application and thanks to Trace API in Firebird I found terrible default value in PHP. What value? It’s the default transaction isolation level value. In 99% of languages/environments in something close to read committed. But not in PHP.

Let’s have a look at it. If you start a new transaction (or if one is started internally, where you cannot change the isolation level) where you don’t explicitly specify isolation level the IBASE_DEFAULT is used. But this is IBASE_WRITE|IBASE_CONCURRENCY|IBASE_WAIT. This is read-write wait transaction in concurrency mode. This mode is most restrictive, nothing close to read committed. And to make it worse, there’s no way to change this default value in runtime. You can only do it recompiling sources, nothing to be viable in most cases.
So how to solve it? Well, if you created yourself some abstraction layer over ibase_xxx/fbird_xxx function you’ll change it there and you’re done. If not, you’re screwed. :) OK, just kidding. You can play with override_function to override fbird_query, fbird_prepare etc., but when you’re in it, maybe it’s time to create simple thin abstraction. That’s what I did. Find & Replace worked for changing the actual code. :) For the rest I created simple functions like DbQuery, DbPrepare etc. These functions take the transaction, which was created by another function DbTransaction and stored (i.e. in $GLOBALS, dirty right? 8-)), when first needed (and then used until commit/rollback or end of the page’s life).

function DbQuery()
{
	$args = func_get_args();
	array_unshift($args, $GLOBALS['tx']);
	return call_user_func_array('fbird_query', $args);
}

It’s nothing special, only few small pieces to make to together work. First the function doesn’t declare any input parameters, but in fact there are some. Yeah, dynamic languages. I get these via func_get_args, then push to the first position the transaction to use (here I’m using the dirty $GLOBALS ;) ). Finally I use call_user_func_array to call fbird_query. Function fbird_query takes variable number of arguments so it’s not easy to call it directly with parameters in array as I have. I have to thank people in firebird-php mailing list, because I completely forgot about it, though I’m using exactly the same at other places in same project. Return values are same as from fbird_query, so you can then start fetching rows and so on (and it’s also good idea to create wrappers for these too, who knows what will strike in the future).

Remember Firebird and PHP is a good combination.

DbProviderFactories and machine.config in Framework/Framework64 fun

Leave a reply

Fun today. I was trying to use FirebirdClientFactory from FirebirdClient installed in GAC. Because it was used globally I was not editing app.config/web.config but machine.config. I went to Framework etc. directory and modified machine.config. And … and it still wasn’t working. Fast forward, I spent maybe an hour trying to figure out what’s wrong. I was hopeless. Everything seemed fine. Randomly cd-ing through directories and thinking what I’m missing, I realized I didn’t modified machine.config in Framework64. Stupid mistake.

Sure after I corrected myself everything started working. Hope my story will save you time.

DbGeography and DbGeometry in Entity Framework – what’s needed?

Leave a reply

There seems to be a small confusion about what is needed to be able to use spatial data – exposed as DbGeography and DbGeometry types – in Entity Framework.

It very much depends on provider, as expected. Of course you need to have Entity Framework 5 installed (you can get it either from NuGet or it’s inside .NET 4.5). You can happily create context, use classes with above mentioned type(s), until you try to execute some query or do some CUD operation. Then it goes into provider’s internals and it is responsible to provide some solution. It’s interesting that the database doesn’t have to support spatial data necessarily. If there will be some convention on the side of provider about translation into i.e. stored procedures/functions you’re fine.

So the Entity Framework isn’t doing any magic trying to store the spatial data. It simply relies on provider to do it, as with any type like integer or decimal for instance. And how do you know if the provider supports it? Well, very likely it’s mirroring what the database supports. I doubt any general purpose ADO.NET provider will do some magic mapping and storing for spatial data if the database doesn’t support it.

Example for Firebird (doesn’t support spatial data):

class MyContext : DbContext
{
	public MyContext()
		:base(new FbConnection("database=localhost:test.fdb;user=sysdba;password=masterkey;pooling=false"), true)
	{

	}

	public IDbSet<Person> People { get; set; }
}

class Person
{
	public int Id { get; set; }
	public string Name { get; set; }
	public DbGeography Position { get; set; }
}

using (var db = new MyContext())
{
	Console.WriteLine(db.People.Where(x => x.Position.Distance(DbGeography.PointFromText("POINT(1 1)", 4326)) < 100));
}

Results in throwing NotSupportedException originating in FirebirdSql.Data.FirebirdClient.FbProviderManifest.

Let’s intersect somewhere on geoid.