Did I get enough of the acronyms and key words in the header?  It looks like soup!  :O

This is a somewhat messy project to build a prototype layer around SQL Server. The reason for this, shockingly, is to allow for a SQL Server to be used by frameworks and systems that normally don’t or can’t access the database directly. In my particular scenario we’re working on getting Ruby on Rails running with JRuby in a Windows Environment. Because we will need to utilize a lot of SQL Server Databases, it seemed like a great idea to build out a layer over the SQL Server (or Servers) so that a Ruby on Rails Web App, ASP.NET MVC, or even a PHP or pure Javascript Application could access the data in the database. What better way to do that then to create a RESTful Web Services Facade over the database.

Some of you might be thinking “Why not use RIA Services?!?!?! Are you mad!!” Well, there is a big problem, RIA Services doesn’t work against SQL 2000 or SQL 2005, which is the database technology that this particular requirement dictated. Well, now that you have context, I’ll dig straight in to what I did building this prototype out.

Kick Out a SQL Server Database Project

I need some data, and a database, with just some of the standard junk you’d expect in a production database. One of the best ways to throw together a database in a really short amount of time, with data, is to use a SQL Server Database Project.

New Database Project (Click for larger image)

You might see this and think, “But you said that the facade is against a SQL Server 2000 or 2005 database!” Well, it is, but to get a database running locally and have this project type work, I’m using my local SQL Server 2008 Express installation. However, I’m limiting myself to data types primarily available to SQL Server 2000 and 2005. So no worries, this works just fine against those archaic databases.  😛

First I ran the following script to create the database and some sample tables with various data types.

DROP DATABASE SomeExistingOrMigratedDatabase
GO
CREATE DATABASE SomeExistingOrMigratedDatabase
GO
USE SomeExistingOrMigratedDatabase
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Person_Village]') AND parent_object_id = OBJECT_ID(N'[dbo].[Person]'))
ALTER TABLE [dbo].[Person] DROP CONSTRAINT [FK_Person_Village]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
DROP TABLE [dbo].[Person]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SomeFlatDenormalizedDataTable]') AND type in (N'U'))
DROP TABLE [dbo].[SomeFlatDenormalizedDataTable]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Village]') AND type in (N'U'))
DROP TABLE [dbo].[Village]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Village]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Village](
	[Id] [uniqueidentifier] NOT NULL,
	[Village] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Village] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SomeFlatDenormalizedDataTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SomeFlatDenormalizedDataTable](
	[Id] [uniqueidentifier] NOT NULL,
	[StarzDate] [datetime] NOT NULL,
	[Numerals] [int] NULL,
	[Numberals] [int] NULL,
	[Monies] [decimal](14, 4) NOT NULL,
	[Day] [int] NOT NULL,
	[Month] [int] NOT NULL,
	[Year] [int] NOT NULL,
	[BigNonsense] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Flotsam] [float] NULL,
	[Jetsam] [float] NULL,
	[SmallishText] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[BiggishText] [nvarchar](2999) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_SomeFlatDenormalizedDataTable] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Person](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DateOfBirth] [datetime] NOT NULL,
	[VillageId] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Person_Village]') AND parent_object_id = OBJECT_ID(N'[dbo].[Person]'))
ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [FK_Person_Village] FOREIGN KEY([VillageId])
REFERENCES [dbo].[Village] ([Id])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Person_Village]') AND parent_object_id = OBJECT_ID(N'[dbo].[Person]'))
ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_Person_Village]

Once the database and tables are created, import the database into the database project. To do this select the “Import Database Objects and Settings…” by right clicking the context menu on the Database Project.

Import Database Objects and Settings…

Select the database just created and click on start. Once the script generation is done, navigate into the project directories and you will see the following scripts have been created.

Generated Scripts (click for larger image)

Next create a new data generation plan in the Data Generation Plans folder (notice I already cheated and have one in the above image).

Creating a Data Generation Plan

Open up the file this creates (I called mine BuildSomeData.dgen). In the file, note I selected the relationship between the Village and People Tables, and set the ratio to 60:1. When you change the data in the Village table it then automatically updates how much data will be generated for the People Table.

Data Generation Plan

When all that is done, hit F5, select the database and the data will be generated. That gets us a database with data to use as an existing source. From here I’ll jump into creating the actual Facade Layer.

NOTES: Once you generate data, depending on how much you decided to generate, you may want to see how big your database is by using the sp_dbhelp stored procedure. I am however, unsure which versions of SQL Server this stored procedure is available in.

Code for this project is available here: https://github.com/Adron/ExistingSqlServerProject

I’ve been asked recently about some books to learn/get updated on/figure out this new found JavaScript craze, so here’s my first three suggestions… 🙂

JavaScript: The Good Parts by Douglas Crockford

I started reading JavaScript: The Good Parts by Douglas Crockford a few months back. I’ve been slowly making my way through the book and wanted to add a few notes, thoughts, and maybe even discuss some of these “Good Parts”.

It seems, even with all the great things happening around Javascript, there are still tons of issues and concerns around using this as a primary language for full stack development. There are concerns around maintainability and other issues also. I like Javascript, but I keep getting that sinking feeling like it is the next realm of “Visual Basic”, which hasn’t held so well throughout the years. If Javascript is going to become the next Visual Basic, I want to be ahead of the curve on the chaos that will surely ensue, and be aware of the signs of poorly designed, poorly built Javascript “Death Marches”. The last thing I want to do is to walk into a shop that has created its own demise through technical debt in Javascript, which I can only imagine could be exponentially worse than a mess in Java or C#.

JavaScript Patterns by Stoyan Stefanov

This book I’ve just picked up, not even started, but decided it is a must have after reviewing the contents. With the concerns of JavaScript kind of being the new “Visual Basic” (and really, not in a language sense, but in a mort sense) when I saw Chapter 2 hits upon “Writing Maintainable Code”, “Writing to be Read”, and “Minimizing Globals” along with a host of other interesting sections I had to spot for this book. JavaScript has and will continue to be in desperate need of good developers who focus strongly on readable, maintainable, simple, and usable code. So far there is a large contingent out there that either don’t pay attention, try to generate everything under the sun, or just simply write crappy code that is completely unmaintainable (or even readable to start with).

JavaScript: The Definitive Guide by David Flanagan

This book is kind of the “bible” of JavaScript. The first edition came out years ago and, in all honesty, I think I’ve seen the book on about every single web developer’s bookshelf I’ve seen. I bet it shows up on those with no bookshelf, but is loaded on a Kindle, or other eBook Reader for those electronically enabled web developers.

I’ve probably seen at least every page of this book, not particularly having read it from beginning to end. This book is full of information regarding JavaScript including all the nooks and crannies of the language that you may or may not want to delve into. Either way, if you write JavaScript even a little, you should have this book somewhere near.

Before even getting to the point of writing a document, there are very distinct user experiences (UX). I have my own preferences, but I am very curious what others think.

When somebody decides to create a new Microsoft Word Document in the Windows Live Site on their Skydrive they’re presented with this interface.

Windows Live Skydrive Microsoft Word Document (Click for full size image)

To start typing in the prospective document you much choose your security, enter a document name, and save the document.  It assumes that you absolutely want a unique name, will have a document viewable by the entire Internet, and that you have to save it just to start.

In Google Docs though the approach is entirely different.  When you create a new Google Document you are presented with the actual document interface as shown.

Google Docs Interface (Click for larger image)

Google Docs assumes that you want to immediately start typing your word processor document. It also assumes you may not know what you want to name the file, nor that you even need to actually save it, until you of course start typing. At that time the document starts to automatically save. It also assumes that this is your document and you don’t want the entire world to be able to view the document.

So I’m left with questions:

• Which interface do people really prefer?
• Do people prefer to start typing immediately or filling out the three pieces of information like the MS Word Doc requires?
• One appears to allow for immediate productivity for the document creator vs. the other one. Is that just my observation or do others see it that way also?

Please let me know your two cents, I’m truly curious about which UX works out better for you.

Official links:

• Node.js Blog
• Node.js Logos

A lot has been going on lately and I’m still working diligently to get caught up from all the meet ups, hackath0ns, off the cuff dev sessions, conferences, and more! Truly chaos, but a lot of fun chaos.

I’ve also been working with NW Cloud to organize things here in the great north west! From Vancouver, Seattle, Portland, and more. If it has to do with cloud computing and events, we’re probably connected in some way. So be sure to check out the NW Cloud Blog and also follow the @NWCloud Twitter account.

In other user group news, SAWSUG (http://www.sawsug.com) or “Seattle AWS User Group” is also coming under the NW Cloud umbrella.  So to check out upcoming SAWSUG meetings we’ll be setting up event listings in most of the regular places (meetup, eventbrite, etc) but the main listing and updates will be on the NW Cloud Site.  For SAWSUG we do have a specific twitter account though at @SAWSUG – so go, sign up, get the RSS and be sure to follow our @NWCloud and @SAWSUG Twitter Accounts.

As for other unrelated events and such…

Mobile App Hackathon (Redmond)

I’m not going to be making this personally, but if you’re into mobile app dev you should check it out.  This is put on by AT&T with guests like Apigee, so it should be a great time.

OSCON

I have an ad over to the right. I generally won’t post any ads, but this is for OSS and OSCON just isn’t really distractive from the core content I post here. It seemed like it would work out just fine to mix in with my regular array of things. If you’re going to OSCON, I’ll be there the whole week; coding, writing, and hanging out around town. So ping me via Twitter (@adronbh) and we’ll hook up for a round of beer, a coffee, or whatever we may.