Brendan Kowitz's .NET Blog

If you are migrating to this cool provider and you are currently running another provider - such as the default FileSystem provider - then you should make sure that you migrate your existing content prior to switching the providers in the web config file.

To do this, go to the Administration page and export your blog's content to BlogML. Then switch the providers in the config file. Then go to the Administration page and import your the content from the BlogML file.

Cheers!

One thing I forget to mention about the database schema I’ve chosen. It currently uses indexed, auto-incrementing ID columns. This means if you’re importing all your posts with GUIDs (or anything non-numeric), they will automatically be assigned a number to fit the schema, so your old post links may break. If there is enough interest for a schema with GUID ID columns then the source is not too hard to modify to support this. However, non-numeric IDs impact the effectiveness of an indexed column.

Hi Brendan, looks as though you have a typo in the UpdateCategory method of the SQLiteDataLayer. In that method you named the string "IaApproved" instead of "IsApproved".

Cheers!

Hi Brendan, I've just been going through some more of the code in the DataProvider and DataAccess classes and it seems to me that you would probably have issues with importing content from BlogML on a number of fronts. The Feedback that you import will still have a GUID as the ID of the post to which it is associated and because you change the post ID to an int they will no longer be attached.

If you'd like to do some tests on this I can send you my BlogML file so that you can import it to see what errors you get.

By the way I'm very much looking forward to using this SQLite provider :)

Cheers!

Thanks for that, good pick up on the sql typo. You’re correct about Feedback and Category IDs. I was simply trying to build the functionality around what databases do best…use indexes. I didn’t really have any initial plans for importing GUIDs and maintaining ID links (kind of defeating the point of BlogML, whoops). It’s also true to say that the auto-incrementing ID columns on the Feedback and Category tables will definitely cause a problem.

However, I am interested to see how SQLite performs with a decent sized blog. Not sure if the best thing in this case is to allow string ID columns, or work out how to convert to numeric IDs and keep the links. I’m open to any ideas or changes.

Does the database that you are using have GUID's? If so I wouldn't be too scared off by using them - especially for a blog which will typically only have thousands or, at the most, tens of thousands of rows. In fact you can actually "tune" GUID's on larger systems (tens of millions of rows) so that the perf. of using them is similar to using ints.

Refer this article for some comparison of metrics:

http://www.informit.com/articles/article.asp?p=25862&seqNum=6

Darren,

For the sake of convenience and interop, why use a GUID at all?

From my understanding, you don't get any benefit to using a GUID over an INT, other than 'it looks cool'. They are no more unique than a plain old INT coming from a sequence and in most cases (ie, not using SQL Server), you'll get worst performance since an index on a GUID field won't perform as well as an index on an INT.

Al.

>> For the sake of convenience and interop, why
>> use a GUID at all?

Because I was too lazy to write a function that would return monotonically increasing integers :-)

GUID's are nice to work with in the middle-tier; in other words, you don't have to run back to the database to get the next ID.

Darren,

I can see why you used a GUID when you started writing SUBv1, you didn't have a database sequence to generate the numbers off.

What I'm thinking of now is a middle ground. If you install SUBv2 without a database (ala XML for storage), then it will continue to generate the GUIDs and everyone will be happy. If you either upgrade or install fresh and intend to use a database (whatever flavour really), then it'll automatically start use sequences for numbering.

How does that sit with you?

Al.

Changing the ID's of posts, categories, and feedback would mean that any current links to a post would break.

My main reason for using a *string* (as opposed to a GUID) for ID's was so that the blog could be totally interoperable with any data storage system and still have the capability to emulate whatever identifier that system used.

For example, if we wrote an Outlook plug-in for SUB what identifiers does Exchange use? And what about an Object Database?

I think that strings are still the best way to go for full compatability with other systems.

If we are talking about perf here I wouldn't get too anal about it. I mean what's a few thousand nanoseconds between friends - especially when most of the data can be cached for very long periods of time.

That’s a valid point. I think I'll modify the schema to allow for string IDs. This way anybody migrating to SUB with BlogML will be able to instantly make use of the SQLite provider. I really don't want to prevent anybody from using it.

That being said. My blog here will continue using the current INT ID schema mainly because I've been happy with it so far.

I can't wait to get some other blogs up and running in SQLite. I wanted to publish this code as a kind of 'beta idea' to see what everyone’s initial reactions would be. So thanks a lot for your input, keep it coming.

Shouldn't take too long to have another update out.