I started working with VistaDB Express this spring as a data backend to use with BlogEngine.NET.  I wanted to make a new data provider for a session I was giving at code camp and wanted to get familiar with a database I didn’t know.  I ended up choosing VistaDB because it looked interesting and because I found so little information about using it.  I also wanted to know why Telligent picked it for Graffiti over something else. 

While I was considering what database to use, SQLite was recommended to me be two different people.  I had recently read a few articles on getting started with it.  It looked interesting and was the other choice I gave consideration to.  Since doing my talk and spending a nice amount of time working with VistaDB, I’ve also had a chance to work with SQLite a bit and thought I’d share my thoughts on the two databases. 

Both SQLite and VistaDB Express are lightweight file based databases that work very well.  They are similar but different enough that there are a few things to consider when thinking about them.  Both have .NET support via ADO.NET data providers and I’ve had no problems with either of them.  Both are small in size and in the small data sets I’ve worked with both have performed very well.  (I read somewhere that SQLite performs better with larger data sets, but I’ve never seen any real stats on the matter.)  SQLite and VistaDB both have nice GUI admin tools available to work with and both databases have been easy to use overall.  Both have small runtimes, work in mono, and support transactions, I believe as well.  Now onto the differences… 

Cost

SQLite is free to use for any purpose.  VistaDB is a commercial product and VistaDB Express is a free for non-commercial use product that requires a link back to the company.  This is a huge difference for some people and is the main strike I have against VistaDB.  If I had a paid commercial product however, the cost of VistaDB is minimal enough that I would not let it sway me too much.  For my personal consulting work, I’m not too excited about the $299 price.  VistaDB Express is a nice alternative for non-commercial use, but the required link isn’t ideal for every circumstance.  (You’ll notice I have the link in the footer of my blog.)

Data Types

I’ve found that VistaDB mirrors SQL Server’s data types very well.  Since I’ve done most of my database work using SQL Server, this suits me very well.  SQLite is more similar to MySQL as far as that goes.  I’m not a MySQL expert (or to be honest even a fan) so take that with a grain of salt.  There is nothing wrong with the data types being different, they just are and it is a distinction.  For me, I’m more comfortable with the SQL Server similarity, but I will freely admit it is a minor issue.

Scripting

Both SQLite and VistaDB support most of the standard SQL I’ve run against them.  I recently noticed that SQLite did not support the ability to change a column in a table via ALTER TABLE which I found to be a bit of a nuisance.  (It seems lame to tell BlogEngine.NET users they have to manually change a data type in a table in the new upgrade.)  Other than that however, I’ve had no issue with either one.

GUI differences

As I said earlier, both SQLite and VistaDB have nice GUI admin tools.  As an open source product with a big following, SQLite has a few GUI admin tools to choose from.  I personally have only ever tried SQLite Administrator.  I have no idea if there are better tools so what I say is based on my work with this tool.  From a functionality perspective, I’ve been able to do basically what I’ve needed to do in both interfaces.  I can honestly say they are both fine tools for the job.  That said, I much prefer my SQL Server Management Studio to both of them. 

Just as in the data types though, VistaDB’s Data Builder tool is a little more SQL Server like.  The look is more similar as is the organization.  It felt a little more comfortable to me as it was more like what I’ve been using for years.  I particularly liked the Data folder under each table for getting to my table’s data quickly. There are a few quirks however including an annoying laptop issue.  (When I have a second display on my laptop, I often move the VistaDB window to the secondary display and close it from there.  When I next open it when the second display is not connected, it opens off the screen and I need to edit the registry to bring back over.)

As I said, the SQLite admin tool was very nice too.  While the look and organization was a bit different, the Edit Data tab in the query area is very nice.  It is a handy way to edit and view/filter data.  There was also a possibly helpful export to excel option easily available.  The interface didn’t seem to have as many quirks but I have spent less time in it.  I found an oddity with editing a table, but it was very minor.

Encryption

One other thing of interest that I found was that VistaDB has an encryption option which will encrypt your database with a password.  In a file based database, this seems nice.  I didn’t see that SQLite had this built in, but I could be wrong about it.  I did see some commercial tools to help with this though.

Documentation

I’ve spent only a little bit of time searching for documentation, looking for examples, and reading forums for each of these products.  My little experience however tells me that SQLite has VistaDB beat in this area.  My google searches regarding SQLite usually brought me right to what I needed and likely had the answer below it as well.

I found a bunch of getting started guides for SQLite and nothing for VistaDB Express besides what comes in the download.

It wasn’t hard to get started with VistaDB however, but a bit more information would have been nice.

Summary

As you might guess from my notes above, I have a slight preference for VistaDB overall.  The cost of VistaDB is an issue and the express version is not an option in many situations you find yourself building software.  There were a number of small things I liked better about VistaDB though and they add up.  The information out there on the web about VistaDB is lacking however.  Hopefully, this will improve with time.

SQLite is very solid and other than the ALTER TABLE issue, I’ve really enjoyed using it too.  It has many things going for it.  I know I can pick up a mac admin tool and as I have a MacBook Pro, this might be a nice option down the road.

Both options are very good and I’ll likely be using both in the future.

With a new release now available, I wanted to write up another guide for those looking to upgrade their blog.  The basic upgrade process is as always fairly simple, but I’m going to branch out and cover more special circumstances.  The guide might seem a bit long, but you aren’t likely to be concerned with all the steps. 

1. Backup

If you’ve read my guides before, you know I’m a big fan of the backup.  If you back it all up and something goes horribly wrong for you, you can just pop in back in place and start again.

I should note that I when I upgraded my blog to 1.4.5, I skipped most of this step.  I backed up my data and my themes and extensions.  Then, I just went for it and I felt bad knowing that I was going to tell everyone to back it all up.  So I’m confessing now as part of my guide that I skipped this.  I do however wish that I had a full backup.  (Not that I lost anything, but it would make for a great screencast to show you exactly how I upgraded my live blog.)

2. Download BlogEngine 1.4.5 (web)

If you haven’t already, now is the time to download the BlogEngine.NET software.  I recommend the web version as it is all ready to go.  Get it here if you don’t already have it.

Now that you have it, unzip it and remove the ready only check in the properties (apply to all subfolders and files).

3. Update your data and configuration

If you use the default XML data storage, you have nothing to do here as usual.  If you are unsure, you likely fall in this category.  Take a moment of silence for your database brethren and move on.

If you are using a database, then you have a little work to do.  In the Setup folder, there are 4 folders, MySQL, SQLite, SQLServer, and VistaDB.  If you are using on of these databases, open the folder, read the readme.txt file and follow the instructions.  There is an upgrade script and a sample web.config.

The web.config has changed since 1.4 and I would recommend you use the version included in the setup folder.  If you are using MySQL or SQLServer, you’ll need to move your connection string over as well. 

If you were using SQLMembershipProvider and SQLRoleProvider, you’ll need to configure these in your web.config.  To make things easier, we have made the dbMembershipProvider and dbRoleProvider, the default database options in our sample web.config.  I personally think the SQL versions are better options, but this is the easiest option for people to get setup and since the other database configs use them, it keeps things simple.

4. Update your web server

Now, we are ready start moving files to your web server.  You do have a backup you can be proud of from step #1 don’t you? If so, you can begin to copy your new files out to your web server.  It is always safest to remove all the folders and files you don’t want to keep before the copy.  For me, these means that I keep my app_data folder and remove everything else before I start my copy.  (I put my theme and extensions back at the end from my backup.)  This way you are sure you have removed all of the previous version.

Anyway, uploading your site will be the longest step of the process.  Be patient.

5. Security Thoughts.

If you are upgrading from BlogEngine.NET 1.4 and have converted to hashed passwords, you can skip this section, you’ve already dealt with it.  If you are using BlogEngine.NET 1.3.1 or earlier, pay attention.  We’ve upgrading the XMLMembership provider security to used hashed passwords by default.  This means if you are using the default security model, you’ll need to need to make some changes.  If you were using the SQLMembership provider, you can skip this as well.

If you fall into the category of people who need to make some changes you have 2 choices.  The recommended choice is to convert to hashed passwords.  To do this, you simply open your users.xml file (found in your app_data folder) and remove the passwords for each users.  (Don’t delete the whole password line, just the password, so that the it shows no password.)  Clearing out the password will allow you to use the password “admin” when logging in.  You should then use the Change Password option to change the password to a better password.  The new password will be hashed.

Your other option is simply to continue to use clear text for your passwords.  To do this, open your web.config and find the Membership provider section.  You can change the passwordFormat from Hashed to Clear.  This option is not as secure and will require you to make this change with each upgrade of BlogEngine.NET in the future.

6. Themes and extensions

If you are using a custom theme by default, you will need to put this back in place before you try to browse out to your site.  If you are using special widgets or controls in that theme that depend on widgets, they will need to be back in place too.  The downside of putting them all in place before trying out the upgrade is they potentially could cause you issues.

Make a note of what files you are putting out on the web server in case you have issues.  If issues arise, you can simply remove these files and manually change your theme to “Standard” to test the BlogEngine setup.

7. Open your site in the browser

It will take a few moments for your site to come up the first time around.  If everything went well, you should see your blog as you remember it.  If you have an error or display issue, see the troubleshooting section below.

If you just did the security upgrade in step #5, log in right away and get those passwords changed from “admin” to something else.

8. Clean up

If you didn’t copy all your extensions out in step #6, now is the time to do so.  Copy them out, and refresh your browser.  It will recompile so don’t be shocked by the delay.

Now is a good time to check over your settings and make sure everything is coming up as you expect. 

Congratulations.  You are upgraded.

Troubleshooting

Don’t panic.

There are plenty of things that could be causing you minor issues that can quickly be corrected.  Here is a bullet list of things of things to check first.

Compile errors

• If you are using a custom theme, widgets, or extensions, remove them, set your default theme to Standard, and try again.  You can change the theme in your settings.xml file or be_Settings table depending on your setup, xml or database.
• If you are using MySQL, be sure to add the system.data section to your web.config.  (You can get this from your original web.config or from this post on the forums.)
• Did you really clean out all the files (except app_data) or did you just overwrite them?  Did something get left over that is causing a problem?  Or did your FTP program miss some files?
• Did you customize your web.config?  Try the one included in the download.  Using your old web.config will get you a nasty looking error.
• If you are using MySQL or SQL Server, please check your connection string. 

Security errors

• Can’t login?  Try clearing out your password as described in step #5.  If that doesn’t help, try using the downloaded users.xml file and logging in as admin.
• An application restart might help out if you are really locked up.
• If you are using a database and the default dbMembershipProvider, you can clear your password in the be_Users table and try logging in as admin.
• Were you using the SQLMembershipProvider and did you configure it properly?  It is not a default setup.

Display errors

• It has been reported that there have been some minor styling changes from 1.3.1 to 1.4.x.  Custom themes may require some minor css updates to get them working.  (I’m not sure what tags have changed as I didn’t have any issues with my custom theme in 1.4 or 1.4.5.)
• If you have an empty widget bar, you will need to re-add you widgets.
• If you just added the Administration widget and it shows up blank, refresh your browser and the options should show up.

Beyond these basics, I’d strongly recommend looking on the BlogEngine.NET forums and asking a question if you don’t see your answer after searching.  Please provide as much detail as possible in your forum post.  The forums are the best way to get help if you are having trouble.

Earlier today, the BlogEngine.NET team published the latest version of BlogEngine.NET, version 1.4.5.  It is a minor release compared to our previous releases, but an important one.

BlogEngine.NET 1.4 came out only a month ago, but we have a few fixes we wanted to get out to the world as soon as we could to make BlogEngine life better for everyone.  We have a few bugs that were hindering new people getting started as well as medium trust issues at certain hosting providers.  In addition, there are a bunch of other bug fixes in this release along with a few goodies to entice you to upgrade even if you are happily working along in 1.4.0.

The latest version has put all the database support I’ve been making available here right in the download now, so you can quickly and easily get your instructions and scripts for SQL Server, MySQL, SQLite, and VistaDB.  In the case of VistaDB and SQLite, we actually have initial database files to start with instead of installation scripts.  All upgrade scripts are included in the setup folder as well.

We have added more features into our Metaweblog API support.  We now support choosing the author by drop down if you are connected as an administrator.  The Live Writer dropdown list will show you all available users you may post for.  In addition, we have also made it easy to enforce SSL for your Metaweblog connection.  If you have it, you may as well, take advantage of it.

There is some cool new APML features along with a few other things to make your blog experience better.  Between the fixes, performance improvements, and new features, you can’t go wrong.

I’ll be working up an upgrade guide and should have it posted in the next 24 hours.

You can download the new version here.

An interesting thread is going around asking how you got started in Software Development.  It seems fun and since Mads tagged me, I thought I’d fess up as well.

How old were you when you started programming?

I guess I started programming when I was about 12 or 13 on my Commodore 64.  It was nothing too wild.  My big project was something I did for the science fair at school.  I had played around with Apple graphics stuff prior to my Commodore, but never got much beyond some simple graphics stuff (and Ultima II) with them.

How did you get started in programming?

I was always interested in computers growing up and after much begging, my parent’s got me a Commodore 64 for Christmas one year.  I played tons of games on that thing, but I did begin to get interested in making them do more.  When I had the chance to actually learn some programming in High School, I knew I’d like it.  So the Commodore 64 sparked the interest, and 2 high school classes got me started doing much more than the very basics.

What was your first language?

My first language was Basic.

What was the first real program you wrote?

Real?  I guess that science fair project was real as I didn’t fail science.  It had something to do with the periodic table, but I can’t really remember. 

What languages have you used since?

Pascal, Fortran, Lisp, Cobol, C, C++, Assembly, FoxPro, FilePro, Visual Basic, ASP, C#, ASP.NET, JavaScript and probably a few others I’ve missed.

What was your first professional programming gig?

My first programming job was with a real estate appraisal firm.  I got a lot of experience there doing everything from setting up Unix servers, to managing GIS projects, to programming.  It was a very interesting job and I stayed there for almost 5 years.

If you knew then what you know now, would you have started programming?

Probably.  I still find software development challenge and rewarding. 

However, I was highly skilled in kicking a soccer ball as a freshman in high school.  I was terrible at soccer, but I could shoot hard and place the ball well.  If I knew that kicking field goals might make for a nice living, I might have worked in that direction a bit.  With a bit of luck (ok, ok, a ton of luck), I’d be about to head off to training camp for my 15th NFL season with the San Diego Chargers.  If that had not worked out, I’d probably fall back on Software Development.

What is the one thing you would tell new developers?

Read other peoples code and let them read yours.

Reading code is an obvious statement as everyone has searched for a code sample of something or other.  However, reading beyond a short snippit to see a larger picture and to learn new ways to do things, not just specific functions.  There is just something about a reading through a well thought out project to see how the developers made it work.

Also, don’t be afraid to show others your code.  Ideally, you can check out ego at the door while you show your code to the other developers.  While you might not like the feedback you get all the time, you will often learn new ways of looking at something as well as better ways to do things.

Its all about continual improvement!

What’s the most fun you’ve ever had … programming?

Probably building my first web site.  I made a web store, GameSurplus.com back in 2000.  It may have been the ugliest web store to ever exist, but I had fun building it and running it.  (The new owners gave it a face lift.) 

I didn’t really planned on actually building a full web store.  At first, I just bought a bunch of games to sell on eBay.  Then I thought I’d make a static one page site, listing what games I had for sale so people could buy them direct and I could avoid the eBay fees.  It just keep growing and growing and I never really planned it all out.  Next, I added a database to update the games and stock.  Shortly afterward, a shopping cart and PayPal checkout were added.  It went on and on.  (Credit cards, Shipping calculators, tracking numbers, pre-orders, internal inventory reports, etc.)

It was fun as I never knew what it was going to need next and often my wife would work with me helping me to see the best way for the site or admin features to work.  My favorite feature was setting it up to automatically print out all the orders as they came in.  It was always fun to come home for the day and see a nice stack of orders on the printer.

Outgoing?

I guess I get to tag some others, so a few of the people who I’d be interested in hearing from would be Ruslan Tur, Steve Eichert, and Steve Harman.

After a few requests, I decided to go back and do another data provider for BlogEngine.NET.  This time around I’m using SQLite.  SQLite is a file based format very similar to VistaDB Express and it has no restrictions .  It is another fine option for blog data and very easy to work with.

Once again, this is a complete provider setup.  It will store all your blog posts, pages, profiles, users, roles, and settings.  It could be partially implemented by making changes to the default providers in your web.config if you desire.

The process for getting setup is very easy.  The instructions are assuming you have BlogEngine.NET setup already.  If you don’t please check out the initial setup screen cast to walk you through the process.  If you already have an existing blog, be sure to backup before following these steps.

1. Download the SQLite database provider for BlogEngine and unzip it.

2. Download the SQLite ADO.NET Provider binaries.

3. In your SQLite ADO.NET download, find the System.Data.SQLite.DLL and add it to your blog’s bin folder.

4. From the BlogEngine download, copy the BlogEngine.s3db file to your blog’s App_Data folder.

5. Next, replace your blog’s web.config file with the new web.config file from the BlogEngine download.

6. Browse out to your blog and you should see the BlogEngine with SQLite welcome post. 

7. Your User security has been reset, so login as Admin (with the password, admin), add new users, and change your password.

That is it.  You should be now blogging with data stored in SQLite.