Posts Tagged ‘Firebird’
Firebird .NET provider team is proud to announce next version of ADO.NET provider for Firebird – 2.6.0.
This version contains various bug fixes as well as new features and improvements. And of course support for some of new Firebird server features. Highlights:
- .NET 4 Client Profile support
- Entity Framework 4 support
- Command cancellation support
- Support for UTF8 filenames
All changes can be found at tracker.firebirdsql.org.
You can download it at http://sourceforge.net/projects/firebird/files/firebird-net-provider/2.6.0/ or http://www.firebirdsql.org/index.php?op=files&id=netprovider.
Thanks to all who helped improving and hunting issues.
The conference is starting today. I’ll write here during the days about what’s going on. Also check Twitter with hashtag #FBCon10 for most recent quick messages.
Day 1:
Well, I was not so active posting new content during the day, mainly because I had no connection, so I was only tweeting. Right now we’re slowly approaching the end of first day. All the sessions we’re interesting so far.
Vlad’s about new features in SQL was a good summary of all the pieces of information you collect throughout the year. The next session from Holger about character set was good as well. Though as person from .NET world my databases are alway UTF8, so I don’t care too much. But I understand, that understanding how Firebird works with different characters set is crucial to know, and not only for migrating i.e. legacy database to UTF8 or something like that. Than was my session. Most interesting, of course. 
Well, not a lot of .NET people, only 4 attended. 
Frank’s session (actually two) about trees in database was a great, if you’re not familiar about some basic concepts of storing trees and querying these. Especially if you are (were) not familiar with recursive CTEs (which I’m using a lot).
Right now the last session of day, Holger’s about UDFs and 32bit & 64bit and Lazarus and FreePascal, is slowly starting. As I’m not using UDFs, because I’m relying strongly on built-in functions (and you can’t, right now, write these in managed code ), I’ll just check what are the challenges and problems today with it.
Day 2:
Connection is today little bit better, so I can post something. We’re now in the middle (slowly approaching) of the second day. So far I’ve seen the Vlad’s presentation about ODS improvements in Firebird 3. Although it’s probably not going affect my databases so much, it’s nice to hear somebody explaining the internals as it brings you more complete view on the topic. Then we have seen some nice features of IBExpert tools from Holger. Some are really advanced. And right now I’m sitting on session about Firebird performance comparison and it’s a nice summary of different stuff you can buy and/or do to make (or not) make your database(s) running faster. In the other room Roman is doing session, but as there was some swapping, I don’t know the name .
Dammit. My session is now done, but my computer froze while switching virtual machines. :-\ At that time I really appreciated my effort to keep my system clean and of course my SSD. In under two minutes I was back. At least there was lot more people visiting my session compared to yesterday.
We’re slowly approaching the end of the end of the day 2, only one session comparing Firebird 2.5 architectures is left. Because I’m using 2.5 version for more than a year, I’m confident I know all the important differences. Again, I’m looking to the after-dinner talks, because you’ll learn a lot of stuff (not only Firebird or databases related).
Day 3:
The final day, day 3, is now over. That means also the conference is over. Surprisingly this day was most interesting (related to session topics) for me. This first session was about the Firebird future features from Dmitry (presented by Vlad). Then Thomas’ about audit and trace. The trace is in fact, from using it perspective simple, but a lot of people might not know about it. Roman has a similar session about RIA and Java+Firebird (mine was OData+Firebird), so I was able to see how it’s done in Java world. The IBEBlock language session, well I was surprised how much functions you have available there, some of these really advanced and you can still use it as DLL and scripting. My, already mentioned, OData+Firebird session was, as I was kind of expecting after previous days, based at least on the peoples elaboration at the Q&A, interesting for them as well (and everything was working smoothly ). And that’s pretty much it, last day of conference.
This year, IBExpert team did again great job with organization. Not only conference, but also all the stuff around it (except the internet connection, which was really bad). As a speaker I was in particular pleased with the recording of session. No software hassle, just some frictionless hardware box recording directly from VGA between notebook and projector.
During evening events we did some Mercedes Benz cars driving, as the conference was in their customer center and of course exchanged a lot words about Firebird, topics realted to Firebird, but also absolutely unrelated topics. Not a lot of .NET people, only few, but the others were little bit interested about the .NET world.
The next one is already planned for the beginning of 2012 (of course if somebody will not do one sooner). Looking forward to it already.
I almost forgot to announce the topics touching ADO.NET provider for Firebird. This year I have three sessions. You can find more info, and register, of course, at firebird-conference.com. Here’s the list.
- New features in .NET provider for Firebird
- Connecting to Firebird using .NET provider from various devices – iPad, iPhone, Android, Windows Phone,…
- Creating services for rich internet applications using Firebird and the OData protocol
So if you’re interested in any of these topic, come, ask, participate. Especially hearing scenarios in what you’re using the .NET provider is very valuable for me. I also like talking between sessions, I always learn something new and I’m eager to to my best to answer all questions people are asking.
Remember the challenge I did some time ago with .NET provider for Firebird and MonoTouch? Well because I’ve got access to previews of MonoDroid, why not to try the same here?
Again it’s a pretty challenge for the whole MonoDroid stack, as the provider uses a lot of various pieces from .NET Framework. And taking into account, the MonoDroid is still in previews phase I wasn’t sure I’ll be able to succeed. However I did. With some tweaking, and I kind of remembered the important places from last attempt, so it was faster, I was able to make it work easily.
Application connected to Firebird server and showing server version and data from MON$DABATASE
Cool, isn’t it? Taking into account, that the Windows Phone 7 (because everything there is based on Silverlight) doesn’t contain pieces from ADO.NET, it’s nice that Mono isn’t crippling the objects available.
Still using i.e. OData is probably better idea, but who knows what somebody might wanna create.
Recently I was running NUnit tests we’re using for .NET provider and I got an idea to compare different Firebird versions. Not because it’s any standard test (frankly it’s way not), but because I have to run the tests against different Firebird versions anyway and I see the time. It’s not about the time itself, rather about the performance improvement (or not) between two versions.
I used NUnit’s GUI to run tests. Tests are run in one thread and I was trying to make the environment same as much as possible. The server was processing nothing else, only the commands from tests. The commands in tests are nothing special, it’s more about testing the provider’s correctness than stressing server. But some processing is there. More processing is done in set up (and tear down) methods where database, objects are created and tables are filled with some data. A lot of commands, but simple ones. With this configuration you can easily see, that cache isn’t useful here as well as improved SMP support in 2.5.
Both versions we’re current latest from 2.1 (2.1.3 SuperServer) and 2.5 (2.5.0 SuperClassic) trunks, official builds, 32-bit, default configurations.
I can clearly state, that 2.5 is faster. About 13%. Is it a lot of not? Who cares. This test was very far away from real world scenario, hence for you application it might be even better (or not 8-)). Good news is, that 2.5 really is faster, no empty promises.
Common Table Expressions are a tool to express things in SQL without need to write long queries or use procedural SQL. The ability to write recursive queries is even nicer and yesterday I had an idea how to use it little bit differently.
Often I’m generating data for reports and I’m trying to do as much as I can on database server. One common problem I’m facing with these reports is the need to include some “zero” data even for, in this case, days, where no data were available.
Imagine, you’re doing something like this (Firebird syntax).
select data.d, sum(data.i) from
(
select current_date as d, 1 as i from rdb$database
union all
select current_date, 1 from rdb$database
union all
select current_date+1, 2 from rdb$database
union all
select current_date+3, 5 from rdb$database
union all
select current_date+3, 2 from rdb$database
) data
group by data.d
It’s OK, but you’ll get data only for date that are present in database. But because you’re generating report for whole month, as I did, it would be nice to have there the data too even with “zeros”. Because then the logic on application side is much simpler, you just need to deal how to present the report’s data.
If you have CTEs, it’s pretty easy to use the recursiveness and generate the sequence, join it with original data and do what you have to do.
select range.d, coalesce(sum(data.i), 0) from
(
select current_date as d, 1 as i from rdb$database
union all
select current_date, 1 from rdb$database
union all
select current_date+1, 2 from rdb$database
union all
select current_date+3, 5 from rdb$database
union all
select current_date+3, 2 from rdb$database
) data
right outer join
(
with recursive padding as
(
select current_date as d from rdb$database
union all
select dateadd(day, 1, d) from padding where d < dateadd(month, 1, current_date)
)
select * from padding
) range
on (data.d = range.d)
group by range.d
I'm using here right outer join just to keep the original data above the sequence I'm generating. But you can swap the tables and use left outer join as it may be easier to read and maintain.
The code is pretty straightforward and if you used CTEs ever before, you'll be able to read. I'm simply generating the dates until I'm ready with the sequence (in this case one month) and then joining with original data. The outer join will ensure the holes are filled and nulls are there. These nulls are later processed with coalesce to "zero" values.
If you want to directly control the number of elements in sequence, simply use some counter.
with recursive padding as
(
select current_date as d, 0 as cnt from rdb$database
union all
select dateadd(day, 1, d), cnt+1 from padding where cnt < 10
)
select * from padding
Today the Firebird 2.5 is being released. You can read the press release and most importantly release notes. And sure, you can download it and use/test/deploy.
I’m not going to repeat what’s written in documents above. Instead I’m going to provide some of my information about using it and some thoughts from .NET provider view.
To be honest I’m using Firebird 2.5 in production environment since betas (I made a small mistake and wasn’t able to easily go back and was lazy to do the long process ). And I’m more than pleased with the stability. Even the betas were more stable than 2.1, where I, probably thanks to environment, experienced I/O problems from time to time. The language features are nice too. I don’t have any personal favorite, all are great addition to whole ecosystem. Actually I do have. It’s not a language feature, but a fixed behavior. And it’s affecting .NET provider.
In some cases the left outer join produced wrong results. Unluckily this error was exposed when discovering database structure for Entity Framework, so even you could use 2.1 in production, for development (at least for model generation) you had to use prerelease versions of 2.5 and that’s, if nothing else, little bit inconvenient. Not taking into account some internal protocol and core improvements that are now exposed in provider as well. I.e. cancellation of running command to pin point one.
I could write more and more about the .NET provider and new version. True to be told, I like every improvement I do in provider that may help other to fully unleash the power of Firebird.
Congratulation to us, the Firebird Project, especially the core team. And also to you, users, I hope you’ll enjoy and like the new Firebird 2.5 version as we (I) do.
Note: The MindTheBird campaign team will run a webinar today at 13:00 GMT in anticipation of the launch of Firebird 2.5 Final Release. See the details.
It may came as a shock but Firebird does not have direct support for neither bools nor guids. On the other hand people around Firebird are smart and came with more or less standard solutions for both. The bool is easy, just use number with constraint to 0 or 1. For guid we (ab)use special character set available in engine. It’s called OCTETS and it’s exactly what you think it is. Just a bunch of binary data, without any other interpretation from engine. That means CHAR(16) and the above character set is a perfect match for storing (not only) guids.
On the other side, where .NET Framework and Entity Framework lives, the bool and guid datatypes are core part of both frameworks. This created a small mismatch or better to say inconvenience. It wasn’t showstopper but working with (or actually without) it wasn’t pleasure either.
But this is over, since today, I used similar trick we used for “identity” columns and added two new special keyword (if I can call it like that). #BOOL# and #GUID#. When you use these (we’re looking for these in whole comment, so you can place it anywhere you want), your model will contain properties with accordant types. (Note, we’re not doing any checks whether your underlying datatype is compatible, it’s up to you.) Similarly the internals of Entity Framework support were improved to handle these changes correctly (as well as Model First support).
If you wanna try it, grab it from SVN or weekly builds and enjoy. And report any problems you encounter, of course.
Firebird has a feature allowing you to specify tables you want to lock (read or write and exclusive/protected/shared) when starting transaction. (Note that Firebird still uses MGA/MVCC. This is just a feature to support some scenarios.) We had constants in ADO.NET Provider for Firebird for some time, but using them resulted in wrong parameters being sent to the server and followed by exception.
Today I implemented support for this locking (tracker item, mailing list thread). That means sending proper sequences. The FbTransactionOptions class created earlier for timeout support was extended with new property LockTables. You can use to specify table name and lock specification. The lock specification there is in fact only subset of all options you can specify for transaction (same enumeration). You can put there whatever you want other options will be simply ignored.
Small example:
conn.BeginTransaction(new FbTransactionOptions()
{
TransactionBehavior = FbTransactionBehavior.ReadCommitted, // etc.
LockTables = new Dictionary<string, FbTransactionBehavior>
{
{ "TABLE_1", FbTransactionBehavior.LockWrite | FbTransactionBehavior.Shared },
{ "TABLE_2", FbTransactionBehavior.LockWrite | FbTransactionBehavior.Exclusive }
}
});
Here I’m specifying that for TABLE_1 shared (huh ) write lock will be placed and for TABLE_2 exclusive (that sounds better, isn’t it?) write lock will be placed. Similarly you can go with LockRead.
Available right now in weekly builds and SVN.
There’s a lot of content for latest CTP, CTP4, for Entity Framework “new features”. It’s mainly focused on Code First stuff, that fills the triad with Database First and Model First. I like Code First but what I also like is maintainable code. Hence I was more trying not to use convention-over-configuration, in opinion good for only small projects, and focus on specifying everything the way I want it to be, especially with my database structure (yes, I’m data consistency and storage freak).
Let’s model some kind of simple library and try to use there couple of Entity Framework’s features. And to make things worse, try to do it with ADO.NET provider for Firebird.
Good news is I succeeded. My database structure was my first and non-touchable object (to be honest the first was the idea what to create and how to represent it in database) together with the idea of objects.
Here’s the script for Firebird database (Did you noticed we’re fully Entity Framework 4 compatible?). See the quoted column names? Yes, not nice, but later in mapping in C# it looks nicer. And I mixed the mapping with one for MS SQL Server (see below).
RECREATE TABLE Authors (
"id" INT NOT NULL,
"FirstName" VARCHAR(255) NOT NULL,
"LastName" VARCHAR(255) NOT NULL,
CONSTRAINT PK_Authors PRIMARY KEY ("id")
);
RECREATE TABLE Books (
"id" INT NOT NULL,
"Discriminator" char(2) NOT NULL,
"Title" VARCHAR(1000) NOT NULL,
"Published" TIMESTAMP NOT NULL,
"ID_Author" INT NOT NULL,
"IssuesPerYear" SMALLINT,
"Price" DECIMAL(9,0),
CONSTRAINT PK_Books PRIMARY KEY ("id")
);
RECREATE TABLE Languages (
"id" INT NOT NULL,
"LanguageName" VARCHAR(100) NOT NULL,
"LanguageAbbrevation" CHAR(3),
CONSTRAINT PK_Languages PRIMARY KEY ("id")
);
RECREATE TABLE Translators (
"id" INT NOT NULL,
CONSTRAINT PK_Translators PRIMARY KEY ("id")
);
RECREATE TABLE Translators_Languages (
"ID_Language" INT NOT NULL,
"ID_Translator" INT NOT NULL,
CONSTRAINT PK_Translators_Languages PRIMARY KEY ("ID_Language", "ID_Translator")
);
ALTER TABLE Books ADD CONSTRAINT FK_Book_Author FOREIGN KEY ("ID_Author")
REFERENCES Authors("id")
ON DELETE CASCADE
;
ALTER TABLE Translators_Languages ADD CONSTRAINT FK_TL_Languages FOREIGN KEY ("ID_Language")
REFERENCES Languages("id")
ON DELETE NO ACTION
;
ALTER TABLE Translators_Languages ADD CONSTRAINT FK_TL_Translators FOREIGN KEY ("ID_Translator")
REFERENCES Translators("id")
ON DELETE NO ACTION
;
ALTER TABLE Translators ADD CONSTRAINT FK_TypeConstraint FOREIGN KEY ("id")
REFERENCES Authors("id")
ON DELETE NO ACTION
;
So it’s time to create mapping right? Nope. Now I’ll switch my brain from ER thinking into OO thinking mode. Here’s the world of entities (sure I made some adjustments to fit some Entity Framework features, like Complex Types):
public abstract class Book
{
public int ID { get; protected set; }
public string Title { get; set; }
public DateTime Published { get; set; }
public Author Author { get; set; }
public int AuthorID { get; set; }
}
public class RealBook : Book
{
public decimal Price { get; set; }
}
public class Magazine : Book
{
public short IssuesPerYear { get; set; }
}
public class Author
{
public int ID { get; protected set; }
public Name FullName { get; set; }
public ICollection<Book> Books { get; set; }
public Author()
{
this.Books = new List<Book>();
}
}
public class Translator : Author
{
public ICollection<Language> Languages { get; set; }
public Translator()
{
this.Languages = new List<Language>();
}
}
public class Language
{
public int ID { get; protected set; }
public string LanguageName { get; set; }
public string LanguageAbbrevation { get; set; }
}
#region Complex Types
public class Name
{
public string FirstName { get; set; }
public string LastName { get; set; }
}
#endregion
As you see I’m using pure POCOs.
So far we haven’t touched any Entity Framework related stuff. So it’s time to create our context and all DAL related stuff. Mine is very simple and exposes only few properties and methods, just to keep it simple and focus on the aim.
public class LibraryContext : DbContext
{
public LibraryContext(DbConnection connection)
: base(connection)
{
this.ObjectContext.ContextOptions.LazyLoadingEnabled = false;
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Configurations.Add(new BookConfiguration());
modelBuilder.Configurations.Add(new AuthorConfiguration());
modelBuilder.Configurations.Add(new TranslatorConfiguration());
modelBuilder.Configurations.Add(new LanguageConfiguration());
modelBuilder.Configurations.Add(new NameConfiguration());
}
public string CreateDatabaseScript()
{
return this.ObjectContext.CreateDatabaseScript();
}
public IDbSet<Book> Books
{
get { return this.Set<Book>(); }
}
public IDbSet<Author> Authors
{
get { return this.Set<Author>(); }
}
}
public class DoNothingWithMyDatabase<TContext> : IDatabaseInitializer<TContext>
where TContext : DbContext
{
public void InitializeDatabase(TContext context)
{ }
}
Pretty simple, isn’t it. I’m using new stripped down objects DbContext (← ObjectContext) and IDbSet/DbSet (← IObjectSet/ObjectSet).
You may also notice, I’ve created object which implements IDatabaseInitializer. That’s because, by default, Entity Framework will try to create database for you and check whether your database matches model (CreateDatabaseOnlyIfNotExists). I‘m controlling my database. You have to add somewhere into your application before doing anything with the context call to SetInitializer:
Database.SetInitializer(new DoNothingWithMyDatabase<LibraryContext>());
And finally we’re ready to dive into the mapping. Again, you can type the mapping directly into overridden OnModelCreating, useful if you have only couple of diversions from default conventions. I created configuration classes where I specified as much as I want. And I need (almost) everything to be nailed down (remember, data consistency and storage freak ) as I don’t want to be surprised when something in database or in entities changes and application will start behave weird.
class BookConfiguration : EntityConfiguration<Book>
{
public BookConfiguration()
{
this.HasKey(x => x.ID);
this.Property(x => x.ID).IsIdentity();
this.Property(x => x.Title).IsRequired().IsVariableLength().HasMaxLength(1000).IsUnicode();
this.Property(x => x.Published);
this.HasRequired(x => x.Author).WithMany(a => a.Books).WillCascadeOnDelete().HasConstraint((b, a) => b.AuthorID == a.ID);
this.MapHierarchy()
.Case<Book>(x => new
{
id = x.ID,
Title = x.Title,
Published = x.Published,
ID_Author = x.AuthorID,
})
.Case<RealBook>(x => new
{
Discriminator = "B",
Price = x.Price,
})
.Case<Magazine>(x => new
{
Discriminator = "M",
IssuesPerYear = x.IssuesPerYear,
})
#if FB
.ToTable("BOOKS");
#else
.ToTable("Books");
#endif
}
}
class AuthorConfiguration : EntityConfiguration<Author>
{
public AuthorConfiguration()
{
this.HasKey(x => x.ID);
this.Property(x => x.ID).IsIdentity();
this.HasMany(x => x.Books);
this.MapHierarchy(x => new
{
id = x.ID,
FirstName = x.FullName.FirstName,
LastName = x.FullName.LastName,
})
#if FB
.ToTable("AUTHORS");
#else
.ToTable("Authors");
#endif
this.MapHierarchy().Case<Translator>(x => new
{
id = x.ID,
})
#if FB
.ToTable("TRANSLATORS");
#else
.ToTable("Translators");
#endif
}
}
class TranslatorConfiguration : EntityConfiguration<Translator>
{
public TranslatorConfiguration()
{
this.HasMany(x => x.Languages).WithMany()
#if FB
.Map("TRANSLATORS_LANGUAGES",
#else
.Map("Translators_Languages",
#endif
(t, l) => new
{
ID_Translator = t.ID,
ID_Language = l.ID,
});
}
}
class LanguageConfiguration : EntityConfiguration<Language>
{
public LanguageConfiguration()
{
this.HasKey(x => x.ID);
this.Property(x => x.ID).IsIdentity();
this.Property(x => x.LanguageName).IsRequired().IsVariableLength().HasMaxLength(100).IsUnicode();
this.Property(x => x.LanguageAbbrevation).IsFixedLength().HasMaxLength(3).IsUnicode();
this.MapSingleType(x => new
{
id = x.ID,
LanguageName = x.LanguageName,
LanguageAbbrevation = x.LanguageAbbrevation
})
#if FB
.ToTable("LANGUAGES");
#else
.ToTable("Languages");
#endif
}
}
class NameConfiguration : ComplexTypeConfiguration<Name>
{
public NameConfiguration()
{
this.Property(x => x.FirstName).IsRequired().IsVariableLength().HasMaxLength(255).IsUnicode();
this.Property(x => x.LastName).IsRequired().IsVariableLength().HasMaxLength(255).IsUnicode();
}
}
I don’t know whether it’s worth to describe the lines. Should be understandable if you know how the entities and database look like. Just maybe small notice. Besides Complex Types we used two most common inheritance mapping scenarions – TPH aka Table Per Hierarchy for Books and TPT aka Table Per Type for Authors (there’s also TPC (Table Per Concrete Type)). However if you have questions feel free to use comments, if I’ll know answer I’ll be happy to reply.
And finally some really simple application to test the result:
If you define #define FB it’ll use Firebird database else MS SQL Server.
Database.SetInitializer(new DoNothingWithMyDatabase<LibraryContext>());
Action<LibraryContext> doSomething = (context) =>
{
Console.WriteLine(context.CreateDatabaseScript());
var query = context.Books
.Select(b => new
{
BookName = b.Title,
AuthorName = b.Author.FullName.LastName + ", " + b.Author.FullName.FirstName
})
.OrderBy(x => x.BookName);
Console.WriteLine((query as ObjectQuery).ToTraceString());
var data = query.ToArray();
foreach (var item in data)
{
Console.WriteLine("Book {0} written by {1}.", item.BookName, item.AuthorName);
}
};
#if FB
using (LibraryContext context = new LibraryContext(new FbConnection(@"database=localhost:ctp4;username=sysdba;password=masterkey;pooling=true;")))
{
doSomething(context);
}
#else
using (LibraryContext context = new LibraryContext(new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=ctp4;Integrated Security=True;Pooling=False;MultipleActiveResultSets=True")))
{
doSomething(context);
}
#endif
I’m glad I was able to figure out how to map everything without touching database structure (although it’s pretty straightforward) or letting the default rules to kick in. Hope it will help you if you struggle with something (and hope the naming will not change much in future). It’s also nice to see the whole infrastructure fits together and changing provider, in my case for Firebird’s, doesn’t make the code to blow out.
Scott Guthrie recently posted article about New Embedded Database Support with ASP.NET. This made me think about other options, Firebird in particular, and advantages and disadvantages. What I’m going to do is to very shortly introduce Firebird Embedded here and then compare it with features Scott wrote in his article.
Firebird Embedded, shortly, is Firebird database server in one DLL. No need to install etc., just load this DLL and use it. To be precise, there are some other DLLs, i.e. to support national charsets, but it’s still in under 10MB all. The database itself is built from same sources as “full” server and it’s not limited in any way.
Works with Existing Data APIs – as I said, Firebird Embedded is based on same codebase as “full” server, thus the SQL and API is same. And not only this, the ADO.NET provider for Firebird works with it and you’re programming using same thinking.
No Database Installation Required and Database Files are Stored on Disk – databases created by Firebird Embedded are stored wherever you want, with any extension. Firebird itself doesn’t have any master database, thus even the “full” server works with any (valid database) file.
Shared Web Hosting Scenarios Are Now Supported with SQL CE 4 – sure you don’t need to install anything with Firebird Embedded, that’s why it’s called Embedded. And not only this, from version 2.5, you can open database from different processes, i.e. IIS worker process and Apache workers or some console application doing something in background.
Visual Studio 2010 and Visual Web Developer 2010 Express Support – as the tool set for Firebird Embedded is exactly same as for “full” Firebird, you can use DDEX (aka Server Explorer support), Entity Framework (LINQ), …
Supports Both Development and Production – this is something I’m silently expecting. But yes, you can do the same with Firebird Embedded too.
Easy Migration to SQL Server – ahh, my favorite point. The Firebird Project has of course whopping number of tools to support migration from Embedded to “full”. The most used is … nothing. The databases are fully compatible and you can switch servers without any other tools, migration, conversion, … Just place it where you want it and connect to it, either with “full” or Embedded Firebird. And to switch your application? Again nothing. Same ADO.NET provider, just change connection string, if needed. Mostly you’ll add server IP address and maybe different path and manually switch server type, if you want. Really the migration is so simple. It is one minute task and thanks to same tool set, no matter what you version your targeting during development, your application will work with the other as well without any additional effort.
You like Firebird Embedded? I do, a lot. SQL Server Compact Edition 4 looks promising, but Firebird has something to offer as well. And recall, it’s based on same sources as “full” Firebird, very mature codebase, examined with tons of installations.
The ADO.NET provider for Firebird now fully supports all the new features in Entity Framework 4. Simple. If you’re eager to test it, grab the weekly build and enjoy.
We support the internal improvements as well as the visible changes like i.e. Model First or CreateDatabaseScript method. The template for creating SQL script is now part of sources and sure will be included in final package as well. I expect to improve it on your feedback and also the model generation from designer is pluggable so you can create custom one and use it there.
To support some new features in SQL generation I had to tweak it little bit and as with every change, there’s a change that something goes wrong. Thus I would be more than happy to get some feedback either that it works OK or any queries where it fails.
I’m so happy to cross this milestone about two months after final Visual Studio 2010, .NET Framework 4 (incl. Entity Framework 4) were released. You can expect the official release after some testing, it’s your turn .
As it may look like nothing is going on, it’s not true. Next to Firebird 2.5 new protocol features, I’m also working on Entity Framework v4 support. Before I go further, be sure, that all providers written for Entity Framework v1 are also working with v4.
In fact right now all the major improvements in Entity Framework v4 are supported. You can benefit from features available, like the LIKE translation support or plenty of new functions. My personal favorite is TruncateTime (so I can get rid of workaround). The Model First approach is next in a row. At least basic T4 template for start is my aim. The rest could be done by you, simply modifying the template. And also wiring the template into code so you can use it programmatically too. Under the cover, while working on new stuff I’m also finding ways to optimize the code. Luckily the changes will be noticeable.
The DDEX for Firebird supports Visual Studio 2010 and the full Entity Framework v4 support will be here soon – now you can try a weekly build. Feel free to ask about anything related.
The soon to be released Firebird 2.5 has a new ability to cancel running command (or any operation currently being processed by server) via API (in 2.1 you can do it via monitoring tables). This is a nice feature, interesting not only for database administration tools.
I was working on supporting it for a while in .NET provider, but I’m happy to say that it’s done (though I may tune the boundaries based on feedback). From some initial proposal in list I picked one following the design of SqlClient, as it’s a de facto standard in ADO.NET world.
So right now you can do:
using (FbConnection conn = new FbConnection(@"database=localhost:rrr.fdb;user=sysdba;password=masterkey"))
{
conn.Open();
//conn.DisableCancel();
//conn.EnableCancel();
using (FbCommand cmd = conn.CreateCommand())
{
cmd.CommandText =
@"execute block
as
declare cnt int;
begin
cnt = 999999999;
while (cnt > 0) do
begin
--cnt = cnt-100;
end
end";
IAsyncResult ar = cmd.BeginExecuteNonQuery(null, null);
Thread.Sleep(4000);
Console.WriteLine("Canceling");
cmd.Cancel();
try
{
object result = cmd.EndExecuteNonQuery(ar);
}
catch(FbException ex)
{
Console.WriteLine(ex.Message);
}
and you’ll get the result (even though the loop in fact never ends):
Canceling
operation was cancelled
Of course, you can screw things up by i.e. starting command and then trying to do something else with connection/transaction/command (the ADO.NET providers are not thread safe by default).
Oh, BTW, if you wanna test it, download weekly build.
From time to time I have to run two or more queries that I know will always be two or more – like some first/skip records and also total count. If you write it as two queries and execute, that means two round trips to database. Although it may not matter if the network latency is very small, why not to challenge myself and try to find some workarounds.
Sure you can create some stored procedures and get the data back from these, but I was thinking about more LINQ to Entitiesish way. I recalled a way I one time used inside one project. Although it was done in pure SQL, it, as it turned out, works, kind of, for LINQ to Entities as well.
The idea is using “one row table” and put the queries as columns. Let me demonstrate:
select
(select foo, bar from table1 where ...),
(select baz, foo from table2 where ...)
from OneRowTable;
Where the OneRowTable can be specially created table or i.e. for Firebird RDB$DATABASE or for Oracle Database dual. It isn’t the nicest SQL (and also challenges optimizer), but works. In columns as queries you can put anything you want as long as it is syntactically correct.
OK, what about the Entity Framework or LINQ to Entities respectively. I created the “one row table” first:
create table OneRowTable(x bit primary key);
insert into OneRowTable values (0);
The table needs to have the primary key to be able to import it into entity model, the datatype doesn’t matter (I was using MS SQL, hence the bit).
What about the queries? Similar approach:
var allinone = context.OneRowTable.Select(_ => new
{
AData = context.a.Where(a => a.x.HasValue && a.x.Value > 10).Select(a => new { A1 = a.id, A2 = a.id * 2 }),
BData = context.b.Where(b => b.id < 999).Select(b => new { B1 = b.id, B2 = b.y }),
});
string query = (allinone as ObjectQuery).ToTraceString();
var data = allinone.First();
var adata = data.AData;
var bdata = data.BData;
The a and b are my testing tables. You can check there’s only one query executed. Encapsulating this into some method is only piece of cake.
And how the query looks like? Well for my MS SQL test:
SELECT
[UnionAll1].[x] AS [C1],
[UnionAll1].[C2] AS [C2],
[UnionAll1].[C1] AS [C3],
[UnionAll1].[id] AS [C4],
[UnionAll1].[id1] AS [C5],
[UnionAll1].[C3] AS [C6],
[UnionAll1].[C4] AS [C7],
[UnionAll1].[C5] AS [C8],
[UnionAll1].[C6] AS [C9]
FROM (SELECT
[Project1].[C2] AS [C1],
[Extent1].[x] AS [x],
1 AS [C2],
[Project1].[id] AS [id],
[Project1].[id] AS [id1],
[Project1].[C1] AS [C3],
CAST(NULL AS int) AS [C4],
CAST(NULL AS int) AS [C5],
CAST(NULL AS varchar(1)) AS [C6]
FROM [dbo].[OneRowTable] AS [Extent1]
LEFT OUTER JOIN (SELECT
[Extent2].[id] AS [id],
[Extent2].[id] * 2 AS [C1],
1 AS [C2]
FROM [dbo].[a] AS [Extent2]
WHERE ([Extent2].[x] IS NOT NULL) AND ([Extent2].[x] > 10) ) AS [Project1] ON 1 = 1
UNION ALL
SELECT
2 AS [C1],
[Extent3].[x] AS [x],
[Extent4].[id] AS [id],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS int) AS [C4],
[Extent4].[id] AS [id1],
[Extent4].[id] AS [id2],
[Extent4].[y] AS [y]
FROM [dbo].[OneRowTable] AS [Extent3]
CROSS JOIN [dbo].[b] AS [Extent4]
WHERE [Extent4].[id] < 999) AS [UnionAll1]
ORDER BY [UnionAll1].[x] ASC, [UnionAll1].[C1] ASC
Not exactly the original shape. The translator took another way creating two one row results and using union all to get it into one query. Except this, the query is in general the same (the explicit joins are as result same as the subselects, though little bit more confusing in this case).
Again, this isn’t general purpose way of doing it and may result in worse performance than running queries separately and I would recommend using it only after careful testing and on controlled limited set of queries.
During the Friday I realized, that I did a long time nothing with my iPhone & MonoTouch development environment and I should try something more challenging. As I’m still fighting with some good idea for real world test application and my UIs are looking weird, I decided to turn my attention not to iPhone app directly, but to MonoTouch capabilities.
As a true geek I decided to try to connect to Firebird from iPhone. Although, thinking about it, I’m trying to connect to Firebird (or make it work with) with various technologies (Astoria offline, Silverlight, etc.). Because .NET provider for Firebird is pure C# and we have Mono compatible build, I deduced that it should work with iPhone too.
Sure, it’s a nice challenge to whole MonoTouch stack, because the .NET provider is more about the code than about the application itself. And we’re using there a lot of different things that can go wrong or may not be available or compilable to native code. And I have to say, the guys behind MonoTouch did a great work (I still can’t believe it).
With couple of minor tweaks I was able to create application that connects from iPhone (simulator) through internet to Firebird server. Pure C#, no hacking or major problems.
Application connecting to Firebird server and showing the server version.
First I’m impressed how mature the MonoTouch is. Second I’m still trying think thru all the possibilities you have with this. With some work on UI you can deliver the same database oriented application to Windows Mobile and iPhone using the same business layer (sure some webservice approach would be better, but …).
Společně s Databázovým světem tu máme další Firebird školení. Více informací, včetně možnosti přihlášení na: http://www.dbsvet.cz/view.php?cisloclanku=2010012101.
Starter for (but not only) morning:
Holger Klemt:
Stefan Heymann doing the same as me:
Dmitry Yemanov:
Vlad Khorsun:
Roman Rokytskyy:
In between sessions:
During session:
In between sessions:
Everybody is solving problems during conference:
Talking about (not only) Firebird in hallways:
The conference is about to start. I’ll write here more and more from it, read it top to bottom.
- It’s 8:25, conference rooms are getting the last polish. You can also deduct, that I successfully found the place.
- The conference is held in Bauzentrum in München. There’s a lot of stuff for people who are building houses etc. in hallways. I like it.
- Session from Dmitry. Good for people asking about limitations and features, why are/aren’t there. The final statement is that Firebird Project isn’t trying to implement all features from Oracle, MS SQL Server, PostgreSQL, … But project is definitely open to good ideas from other projects. And no experiments with features.
- The trace and audit session from Vlad just finished. I like this new feature, but I was not paying attention to during development, so this was a nice summary of my shattered knowledge. But what I don’t like is the configuration file format – mix of XML and standard text config file.
And same for output, it’s not some kind of “computer ready” format, like i.e. again XML, it’s just a text, which will be for people harder to parse – but Thomas Steinmaurer already created one application. However the API is open, so probably sooner or later, I hope, somebody will create plugin with this output (and maybe also one for putting the content into database – what about MS SQL and using Analysis Services? 
)
- After these sessions it was Holger’s turn. About the replication of databases. It was interesting to see the different approach based on creating the actual query executed from inside the trigger. Sure it has some limitations and we found some also during the session, but if you’re OK with the way how it works, it’s a good and easy-to-implement solution. But I think, just because I rather wanna to have more “based on values rather than on creating commands” approach to the synchronization/replication I would probably go in a different direction.
- After this session was my session – the PocketPC and Firebird stuff. It was, sure, the best topic of the day , and because describing own presentation is weird, I would just skip it and hand you to the recording which will be available, for sure.
- The final session was a all-hands-session about the Firebird and performance. There’s nothing special to write about it, because everybody put his/her input it was better to be there than not.
- And maybe the most important part was the city sightseeing and dinner during the evening. These “after-events” are great opprotunity to ask anybody you choose about your problem or give an interesting feedback, hence if you’re not here, you’re definitely missing something.
Day 2 is ready. Everybody fresh and relaxed . Weather in München is nice.
- The first session today was from Vlad about mainly some MS C++ runtime issues with recent changes in WinXP, Vista and Win7. If you were deploying the server in the meantime (or you’re using older (sub)versions) it was definitely worth listening. The current versions of Firebird server have not these issues, because it was successfully solved.
- Following session was from Alex about new protocol features, mainly in 2.5. As a provider writer I was a nice overview of what I will need to do in next couple of months to catch up with server. Also if you wanna to use some brand new features, this session was a good opportunity to see what’s going on inside and what you can do (i.e. if you components are not supporting all options).
- If you’re reading this, you missed my presentation, which was the next in schedule. Again I’ll skip any description.
- Session finished right now was Holger’s about backup and restore. Because I saw last presentation from Ivan Prenosil last year about (I think) 10 ways of doing backup I was not expecting something new. But in fact there was something new. The IBExpert except the IBExpert itself has a really nice set of tools, like a scheduler for backup & restore which you can very easily plug into your environment and use it for almost hot backup of your database on different server. Similarly for dealing with metadata changes and pumping data out and in.
- The final session from regular speaker was Dmitry’s about the lock manager. To be honest I was never paying to much attention to fb_lock_print utility and I had only a very limited knowledge about it. But this presentation, especially demos, widened my knowledge little bit and I know, at least, what useful information I can find there, and if I’ll not forgot all stuff also how to use these. And seeing what’s going on inside the lock manager also helps understanding the internals.
- Really the last session of the day was continuation of talking about improving performance of Firebird (itself or tools) and sharing and exchanging the information. Because everybody was contributing with (but mainly Holger) own pieces, it was really great ending of the day.
And here we’re. The last day of conference. Everybody is looking little bit exhausted, but I’m sure we all are gonna to survive no matter what.
- First session of the day, was classical session about “what we can expect in next development and in next year”, held by Dmitry. It was a nice overview what’s going to happen and if you’re not a member of devel and/or architect list (or you’re not reading it often) also nice summary.
- Then the Vlad came in with new SQL features in Firebird 2.5. As I like a 2.5 a lot and I’m using it for more than a year, I was more or less familiar with these features (although I’m using only few of these). So if you’re too lazy to read release notes for betas and RCs, this session was surely good for you.
- Before the lunch Alex had session about security. I liked the small recap of the history of security in Firebird and InterBase. Then the walk thru the new features and improvements was really good and exhausting, nice samples. I’m really looking forward to Firebird 3.0 to test all new security, mainly authentication functions. This could also make life easier for i.e. hosting providers offering Firebird.
- Once again (and for last this year) skipping my session. (BTW I realized that even if you’re reading this text, there’s change, that you’ve seen my presentation, so my yesterdays statement wasn’t completely correct.)
- The session about the BI tools and Firebird from Roman was really nice. My focus is mainly in OLTP, and I’m looking to OLAP world only little bit, and this was a really nice for me to see also the other side of some problems and see, for me unknown, tools and what is and what is not possible and how hard or easy (desining some transformation looked really easy and quick, at a first sigh).
- Following session was from Roman too. Simply about the Java driver and using it. It was really nice to see how it works in Java world and what problems they’re facing and comparing these in my head with .NET world problems. The example of Hibernate was nice too, as it’s a similar tool to Entity Framework and “father” of NHibernate, so I was able to briefly look to different approach for O/M mappers solution.
- The final (of the day) + final (of the conference) session was simply roundtable, where every developer from different parts of Firebird project (core, .NET, Java, …) introduced little bit the subproject he is working on as well as him/herself. I was expecting people asking little bit too much about these subprojects and maybe sharing ideas and needs, but the discussion didn’t furiously started. A pity.
What to say about this years conference? It was good definitely. I was really enjoying my presence as well as my presentations. So see you next year, maybe face to face maybe reading this feed (if I’ll be doing it).
Společně s Databázovým světem tu máme další Firebird školení. Více informací, včetně možnosti přihlášení na: http://www.dbsvet.cz/view.php?cisloclanku=2009110403.
