VistaDB .net database blog

VistaDB 3.4 Build 63 Development

Jason Short — Sat, 19 Jul 2008 02:40:00 GMT

Build #63, 7/18/2008

NOTES

We now have over 600 NUnit tests! New tests are being added for SQL code syntax comparing to SQL CE and SQL Server. Not all syntax will be added, but there are some changes on the way for sure.

The Data Migration Wizard has been changed to allow it to run on 64 bit Vista. Simple fix. The app was set to CPU Any - that meant it would JIT to 64 bit. The problem is that the old VistaDB 2 runtime was only 32 bit. Trying to load it meant you blew up pretty quickly. Also updated DMW to state version 3.4. Tested for SQL CE 3.1 only (built in VS 2005). A SQL CE 3.5 version will come later after the 3.4 release.

ISSUES AND FIXES

#40: Opening a table with 1024 columns in DataGridView - Issue is with an internal limit to the FillWeight in the DataGrid control. Added work around to force the fill weight to be 1 on column creation. This allows the code to run without issue.

#151: DataBuilder > XML Export - Root Element Naming - Change was to the engine, not the databuilder. Altered the export code to make sure the name used is only the filename portion and not the full name of the file.

#223: While viewing Data folder in Data Builder tree view there is no refresh button and F5 does not reload the grid - Added refresh button with icon and F5 hotkey to data viewer.

#755: Identity Column Type bigint and @@identity - IntColumn now correctly looks at the inbound object type prior to attempting conversion.

#709: CLR Proc which return a Table - Fixed sample for CLR Procs.

OTHER NON TICKETED ITEMS

CF framework strikes again - several of our fixes didn't work on the CF and we had to dumb them down. The CF compiler apparently doesn't know how to support function prototypes that use a default param on a function type. Wierd, they make you explicitly set it anyway. And they decided to hide the Dispose method on the Filestream (private). WHY? Using() still works, but any other use and you can't dispose of it. Just Close() it and hope for the best.

INTERFACE CHANGE

New public Interface IVistaDBTableList - this is used as an experiment to extending the DDA interface to include strongly typed members. This interface gives you the ability to get back a strongly typed version of the EnumTables through a function called EnumTablesTyped(). This will return an IVistaDBTableList that may be used in a foreach loop for faster handling without casting, and contains optimized lookups for contains, etc.

IVistaDBDatabase db = DDAUtils.BuildTestDatabase(ref DDAObj, dbname, false, null, false);
IVistaDBTableList tableList = db.EnumTablesTyped();
foreach (string table in tableList)
{
Console.WriteLine("Table is named: " + table);
}

Exposing strongly typed interfaces that are not CLS Compliant

Jason Short — Sat, 19 Jul 2008 00:00:00 GMT

Interface Change

There is a new public Interface IVistaDBTableList - this is used as an experiment to extending the DDA interface to include strongly typed members. This interface gives you the ability to get back a strongly typed version of the EnumTables through a function called EnumTablesTyped(). This will return an IVistaDBTableList that may be used in a foreach loop for faster handling without casting, and contains optimized lookups for contains, etc. Here is an example.

 IVistaDBDatabase db = DDAUtils.BuildTestDatabase(ref DDAObj, dbname, false, null, false); 
 IVistaDBTableList tableList = db.EnumTablesTyped();
 foreach (string table in tableList)
 {
    Console.WriteLine("Table is named: " + table);
 }

CLSCompliant - Tagged IVistaDBTableList members as false

This is mostly an experiment at this point, and this interface may go away in the final build of 3.4. The one major drawback is that the interface has to expose an unsigned long value - this makes us tag that interface and the function and non CLSCompliant. I am not too happy about that, but the TableID is an unsigned long, so how else would you expose it?

Internally the VistaDBTableList is used now for getting and testing all table lists. The performance on this compared to the older ArrayList version is amazing. For some complex table layouts the performance of the new strongly typed class more than twice as fast. See the recent blog post about strongly typing everything!

We need to think about how to expose these types of interfaces through an API without losing the CLSCompliance flag.

I wonder how others are handling this? I have a feeling for the final 3.4 release we will probably have to hide all the unsigned long variables in order to keep our clean interface CLSCompliant.

On the one hand keeping everything CLSCompliant is a good idea for an API, and makes it much easier to run in low / medium trust environments. On the other hand when you have a legitimate need to expose more than an Int64 to the world how would you do it?

Strongly type everything!

Jason Short — Fri, 18 Jul 2008 22:00:00 GMT

Coming soon

Wow, we just wrapped up Build 63. You should see it on the site sometime this weekend. All I can say is that the new Mike has done a great job of pushing VistaDB along. New bug fixes, NUnit tests, lots of good things happened the past two weeks.

Nunit Suite runtime

The above numbers are the total time required to run the current NUnit test based on the engine from that date. These dates do not correspond to any specific build of the engine, just what was in the revision control system on that date.

The 27 April 2008 Engine took 271 seconds to run the current complete Nunit tests.

The 18 July 2008 Engine took 107.7 seconds to run the same set of tests.

Double performance

That is more than doubling of performance on these tests.

Not bad considering all we have done is minor tuning and tweaking of objects for strongly referencing. We are not 100% complete with strongly typing all the internals of the engine, but the ones that are left are mostly there because they have to be.

There are some aspects of ADO.NET that we can't change or it will break the public interface with the Provider Factories. After seeing the performance differences I am really scratching my head wondering why Microsoft didn't provide strongly typed overloads for ADO.NET. Why can't you request the schema in anything other than an ArrayList? I don't see any changes in the 3.x line either as extension methods to help us here either.

VistaDB NUnit Memory footprint

The above numbers are the total time required to run the current NUnit test based on the engine from that

This chart is likewise a graph of the same engine runs Peak Memory footprint and the amount of memory in use at the end of the complete run. These are not very scientific due to the nature of the Garbage Collector in dot net. If you run the same tests on this exact same machine with only 1 GB of memory you will see totally different results. The only way you can compare is to run the same tests on the same machine. All of these tests were run on the same machine with 4GB of RAM on Windows Vista x64.

The peak memory footprint came down from 190MB to around 140MB. That is respectable, but also shows just how much overhead you incur just by casting objects around. All those temp objects add up, and the GC will get around to them when it is good and ready. You could easily see higher numbers than this just due to when the GC decided to clean up (we don't force GC's in the NUnit suite).

Strongly type it all

Ah, I wish we could. Some things will have to stay weakly typed for ADO.NET. I am hoping that as the Entity Framework provider starts to take better shape we will have a better idea of how to stay strongly typed while providing services to the Entity Framework provider.

Microsoft did make some changes so we can pass around our internal command objects rather than requiring us to convert everything to strings. This should help us quite a bit in managed land since we will not have to parse and tokenize strings all day before the engine can actually do something.

We will continue to make changes in the engine to improve performance, drop the memory footprint, and hopefully improve throughput as well.

Algorithms – no one size fits all

Jason Short — Sat, 12 Jul 2008 01:50:00 GMT

I have been spending quite a bit of time lately thinking about algorithms. Not always specific concrete ones like sorting items in a list. More in the general sense of how to determine the correct process to create a desired outcome.

Easy choices are... easy

There are some places where decisions are obvious. If you need to keep a list of all the indexes contained in a database, or what categories of data are available the choice is usually pretty easy. The choice is also usually minor in terms of the outcome. The route you took to reach the decision is not always important. As long as you get everything in alphabetical order no one cares how you did it.

Subtle choices are much harder

Subtle choices become much harder. Take as an example the caching of indexes from a database. How much of your precious ram and heap do you spend on that? That answer is almost always – it depends. What about things like when to store data in a cache rather than fetch it from disk or remote location? Those choices start becoming harder and harder.

How much is too much?

Now take into account things like the volume of data. Do you plan to store 1 million rows or 10? There are general purpose algorithms that work for both, but then you have optimized for neither case. What if optimizing for one case leads to a 100x improvement in performance for that specific case? Do you implement the optimization all of the time, or conditionally?

Caching and storing of data is another difficult question to answer in general purpose terms. What works great for a single user dedicated app will fail horribly under a severe multi user scenario. There are lots of cases where we in essence optimize for worst case scenarios – always assuming disaster is about to happen. What usually ends up happening is you kill performance for most of the general purpose cases just to handle that one really strange scenario.

General purpose means optimized for none

I have never been a big fan of general purpose systems. I usually build for specific constraints in the system. If I need to store a lookup of several million values I will implement it totally different that a quick look up in a list of less than ten. But where do you draw the line?

With the database engine we have to handle all sorts of scenarios. Should the engine special purpose for both ends of the performance spectrum? Or do you maintain two code bases for such purposes? I can remember years ago using a data engine that you could load the large or small version of the library.

I think in some ways SQL CE is doing this because you have to tell the engine the max size of the database. The engine can then in some ways cheat to optimize what it expects you to do in that case. Tables that would have been kept in RAM or cached will now be spooled to disk or partitioned.

Interesting to think about

I don’t have any answers, but I have been spending a lot of time thinking about it lately for VistaDB and other projects I am working on. It is always difficult to know when you have struck the right balance.

Optimization for your target means having a valid sample to optimize against. But in programming, as with life, it is not always so neat or orderly.

Graffiti CMS 1.1 released with new pricing

Jason Short — Sun, 29 Jun 2008 01:48:00 GMT

Teligent has released the new 1.1 version of Graffiti CMS.

The Graffiti v1.1 edition includes

· bulk comment management

· permissions functionality

· the ability to delete and rename users

· packages, and

· more than 30 bugs fixes

New Pricing

The newest version of Graffiti is only $99 for 3 content authors. (Additional users may be added for just $49 each). Developed with the small business owner in mind it is definately a great bargain for anyone.

Graffiti CMS uses VistaDB

And of course we have to mention they use VistaDB as their default provider for their data storage. It allows them to xcopy deploy to shared hosting and even Mono thanks to our 100% managed database engine.

We use Graffiti CMS

This blog you are on right now is powered by Graffiti CMS!

LINQ test for custom IEnumerator

Jason Short — Fri, 27 Jun 2008 22:07:00 GMT

Mike and I had an interesting discussion this afternoon about LINQ and how it interprets enumerable rows. Needless to say, some test code had to be written to prove or disprove some theories.

Concrete vs Interface

Our thought experiment was what if you had an interface to an object and wanted to expose it to LINQ for query? Did you need to implement IEnumerable on the concrete class, or the interface level? Would it work on both? What about strongly typing and intellisense in the IDE?

Interface wins

It turns out that you can implement it in either place. But if you implement it only in the Concrete class then LINQ can’t see the IEnumerable to give you intellisense.

Concrete example (pun intended)

OK, how did we test this? We took a Row as our base object. We want to store a collection of Rows and iterate over them using a Table (something we do quite a bit). In VistaDB all Table objects actually implement the ITable interface to give them a common set of controls (things like RowCount).

Parts

Row – Stores Data

ITable – Interface for the Table

Table – concrete class for the Table

Questions you can ask with LINQ

Once you have this done you can ask some interesting questions with LINQ.

Give me all the rows that have data > 0 starting with item #2

var foundrows = firsttable.Where(c => c.StringData.Length > 0).Skip(1);

Easier in SQL?

This would be MUCH harder to do without LINQ. This is a powerful query with intellisense. Don’t think of this in terms of SQL, you could do this with SQL as a single execute as well. Think DDA. In DDA you would have to set .First(), then .MoveNext() and then move through each row checking for the length > 0.

DDA with LINQ?

No, this is NOT supported today. We were just using it as a thought experiment. Could you expose something like that through the IEnumerable interface and actually have it work?

The answer is yes. It actually looks like you could do it. Now, how much work would it be in VistaDB? No idea yet. But it is an interesting thought.

Sample Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LinqTestEnumerator
{
    class Program
    {
        static void Main(string[] args)
        {
            Table firsttable = new Table();

            var foundrows = firsttable.Where(c => c.StringData.Length > 0).Skip(1);
            Console.WriteLine("The following rows where found after the filter was applied");
            foreach (var c in foundrows)
            {
                Console.WriteLine(c.StringData);
            }

            var singlerow = firsttable.First(c => c.StringData.Length > 0);
            Console.WriteLine("Single row: " + singlerow.StringData);


            ITable secondtable = new Table();
            var foundrows2 = secondtable.Where(c => c.StringData.Length > 0).Skip(1);
            Console.WriteLine("The following were found using the ITable class rather than the Table concrete class");
            foreach (var c in foundrows)
            {
                Console.WriteLine(c.StringData);
            }

        }
    }

    /// <summary>
    /// This is our BASE for everything here.  We are trying to store a list of rows for this test
    /// </summary>
    public class Row
    {
        public string StringData;
    }

    /// <summary>
    /// An interface for Table objects - common required data
    /// Note that IEnumerable MUST be implemented at this level, not at the Table level or LINQ can't see it
    /// </summary>
    public interface ITable : IEnumerable<Row>
    {
        // How many rows are in this table
        int RowCount();
    }

    public class Table : ITable
    {
        List<Row> rows;

        public Table()
        {
            rows = new List<Row> { new Row { StringData = "row data 1" }, new Row { StringData = "row data 2" }, new Row { StringData = "row data 3" } };
        }

        public IEnumerator<Row> GetEnumerator()
        {
            return (rows.GetEnumerator());
        }

        // Note that the namespace collision between the Generic and normal Collections of IEnumerator
        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            return (rows.GetEnumerator());
        }
        
        public int RowCount()
        {
            return (rows.Count);
        }
    }
}

VistaDB 3.4 Build 62 - New installer

Jason Short — Thu, 26 Jun 2008 22:18:00 GMT

Build #62, 6/26/2008

NOTES
NEW Setup Application. This version does things very differently under the hood. The serial number input is not validated yet (it will be later). But it should be smart enough to remember your serial number during upgrades.

Foreign Key Lists were using an arraylist internally and walking the entire list doing case insensitive string compares per entry of the value. Changed to a strongly typed dictionary and OrdinalCaseInsensitive for the compare type.

ISSUES AND FIXES

#779: Error 120 when adding a column to a table - Issue was related to a table with FK's casting into a strongly typed dictionary. Fixed.

OTHER NON TICKETED ITEMS

There are a lot of internal changes in this build again. Including some conversion in strongly types, and more changes to the engine for optimizations.

There is also a start of the new Membership Provider for asp.net and the partial implementation of the Role Provider that will be included in 3.4.

EDIT:

Make sure you UNINSTALL 3.x from your machine before running the new installer. It can't detect InnoSetup installs, so it doesn't know how to uninstall them. I am working on that for the final build.

As a quick tip: If you are ever worried about keeping a runtime for testing or anything like that just copy the install path FRAMEWORKS directories. Each one contains a RUNTIME directory with the actual Runtime DLL. Then you can keep them all in a path for testing or just for archival if you like.

There are also some partially completed samples for 3.5 in the Frameworks\Net 3.5\ directory if you care to look. Poke around in the directories, there are lots of interesting things tucked away in there.

Another Mike coming to VistaDB in July

Jason Short — Thu, 19 Jun 2008 06:46:00 GMT

More people

Good news indeed. We have another Mike coming to work with us. Mike has accepted a full time position at VistaDB starting in July.

Another Mike?

Yes, that makes 4 officially associated with the product dev team. Makes it easier on me I guess... I can now always just state that it wasn't me - MIKE broke it!

I will post a brief write up about the new employee when he starts so you guys have a little background prior to seeing him posting on the forums, etc.

VistaDB Coolest Feature?

Jason Short — Wed, 18 Jun 2008 06:57:00 GMT

What do you think the coolest feature of VistaDB is today? I get asked this question quite a bit and I never know how to answer.

Hmm, we have a lot to choose from. I am going to rattle of a few of my favorites and see what you guys come up with also.

Jason's Picks

100% Dot Net - A lot of people don't care about this, but I do. The ability to write something this large and complex as 100% managed code has really proven out the development and runtime efforts of Microsoft. They have a great platform, and it is only growing stronger.

I have recently had to go back and write a bunch of C++ stuff. In many cases my C# versions of the code were faster than the C++ code. Simply amazing.

XCopy deployment - Very cool to be able to xcopy my databases across boxes / storage keys / etc and still open the same database from Mono, Windows, CF editions.

SQL Server syntax - This is a biggie to most people. And it is very nice. I would like to see the parser abstracted at some point so people could write their own parsers on top of VistaDB low level engine. Something like lolcats query language would just be funny, but a parser for mySQL would be quite functional.

Isolated Storage - Cool to some people in Active Directory environments. Otherwise I don't think a lot of people care.

Data Builder with Source - It is a good tool, but I don't think it is great yet. It still need a lot of cleanup and polish. Mike and I have a few ideas here, but they will take more time to complete (and maybe the new hire can start here). It does show images from a table (sort of nice), but need a few more friendly features.

TSQL Procs - This is the uber coolness that attracts most people. SQL CE can't do it. No one else runs SQL Server stored procedures like we do.

CLR Procs - I personally think that CLR Procs are about 100x more powerful than a TSQL Proc, but more difficult to create. Have any good ideas for a CLR Proc demo? Let us know.

DDA - Having a choice in APIs can be a good thing. Need quick and dirty, go DDA. A lot of people seem to shy away from it because they are then off the ADO.NET 2 provider roadmap. It is not that bad of a thing. Gets the job done, and you can create some very neat looking apps with very small code bases.

Visual Studio Integration - This is pretty awesome, but I don't think that many people use it. It involves some training, but is a very powerful tool.

What do you think?

CLRProcs and VistaDBContext

Jason Short — Tue, 17 Jun 2008 23:28:00 GMT

What is the VistaDBContext object?

Think of the VistaDBContext as your host container for the CLRProc. It is the part of the engine that is kept per connection and allows the hosted CLRProc to communicate to the Engine through a Pipe object. This is very similar to how it works in SQL Server.

Why can't you communicate direct to the engine?

The VistaDB Engine is not in the same namespace as the procedure. The procedure has to be loaded and then JIT'd in order to run from within the database. But that does not mean the engine can trust the assembly entirely. It keeps itself isolated from the stored proc as much as it can.

What about threading withing a CLR Proc?

If you create a thread within a CLRProc is does not have any VistaDBContext associated with it. This means you cannot call back to the engine from the worker thread. You might do this if you are performing some very long process and only want to update the database that the process completed (could do this with a new Process as well).

The only way to communicate back to the database in this case would be as a totally new client (Open a new VistaDBConnection to the database).

This comes with all the normal drawbacks and benefits. You get to use the ConnectionPool. But if you have the database open in Exclusive mode you will not be able to access it from the second thread's connection.

As an example let's say you had a CLRProc that FTP'd a file to another server and was going to take a while. You decide to spin up a thread to complete this task. The new thread then would have to create a new VistaDBConnection and perform an update to the table telling it the transfer completed.

What happens when you open a connection to a database from within a CLR Proc?

If you open a new Connection it is just like a separate application connecting to the database. Unless you use the VistaDBContext your clr procedure may as well be running within another application. There is no other communication channel other than the context pipes between the engine and hosted procedures. This is important to understanding why certain things cannot be performed within a hosted clr procedure.

What is the difference between a method tagged with FillRow?

The difference is in how it may be called from within your SQL code. Do you wish to call it like a UDF function? Then FillRow is not required. You may call the function using EXEC Functionname(someparam), or SET @VARIABLE = Functionname(someparam). But you cannot select * from Functionname(someparam) unless you have a way to enumerate over the rows. This is the purpose of the FillRow attribute - it tells the VistaDB Engine what function must be called to assign the values to each row in the output.

Context can be a tricky subject because it is not one with much information (even from the SQL Server folks). There is also a DDA ability to communicate through their own DDA Channel Pipe which was moddled after the SQL Channel Pipe.

I continue to be amazed at the great things you can do with VistaDB, and just how subtle from of the examples truely are. The current CLRProcs demo is actually demonstrating around 15 topics that are not always apparent. We will be breaking it up into multiple applications to demo key concepts better and hopefully remove some of the confusion around CLRProcs. They are quite powerful and easy to code too.