My ideas for The Atlantis Interactive Schema Engine
I first became aware of the open source Atlantis Interactive Schema Engine on Febuary 24th 2011 thanks to a retweet by Paul Randal. Its a .NET assembly which contains most of the “heavy lifting” code related to the schema operations in their various products. Matt Whitfield, the sole proprietor of Atlantis Interactive, is going to setup an account on codeplex or one of the other forges so people can post bugs, feature requests, and of course patches. Personally, I have several ideas for feature requests. However, I have no channel at the moment for these requests besides emailing Matt. While Matt has been very responsive, I wanted the conversation to be more public, I talked to Matt and he agreed it was a good idea to create put them on this blog post. Please use the comments to add your own bugs, feature requests, or patches, or comment on my feature requests.
Now, here are the feature requests.
Integration with ILSpy
ILSpy is a free and open source .NET decompiler. Development was accelerated by the fact that Redgate Reflector ceased to be free. It would be great if the library could decompile CLR assemblies to C# with this tool. It would be great to produce a diff of the decompiled source of two CLR assemblies.
Powershell related improvements
Powershell Cmdlets
I’ve written about using the schema engine with powershell previously. Its a relatively pleasant experience, but it could be improved with powershell cmdlets. I’m pretty new to powershell, so I don’t have a clear direction on where I’d like these to go. However, I might consider taking the initiative on this one.
Shortened namespaces for the Enums
Basically, in powershell you have no using statements, and the Enums that are used as parameters are several namespaces deep. It might not make sense to change them. However, I am just throwing this out there.
More flexibility for creating SchemaReaders
I’m looking to be able to do a few things:
- Pass an existing connection object
- Create a pooled connection string. While that doesn’t make sense for desktop apps, it might make sense if I was using the library from a web app. Being able to sync databases from a web app would be really cool.
Get the existing connection string from SchemaCollection
Its self explanatory.
Be able to script a schema to a Microsoft Sql Server Project
I’d like to be able to point the library at a database and have a function in SchemaCollection that will script everything to a visual studio database project. Initially it would be easiest to hard code this functionality. However, it would probably be best if it was template (t4?) based so that you could easily support other database versions.
Naturally a long term possibility would be to be able to do a schema sync between databases and sql server projects.
Support for Mono
I actually started a port of this. Its very easy to get it to compile (comment out like 3 lines), but you do lose Geospatial support. For this reason, it might make sense to have separate mono and .NET builds. In my limited time testing it I could not get my linux vm to connect to SQL server, so I don’t know if it would actually work. I want to pursue this path a bit myself, even if it just leads to me filing upstream bugs in mono.
Support for other databases
It seems like the object model is pretty well organized. That being said I realize this exercise would be far from mechanical. I don’t think cross database compares will ever be practical on a whole database level. Below are the specific databases I’d like to see supported.
Microsoft Access
I don’t actually see much of a case for this other than “it can be done” and all windows machines have support for the JetSQL engine.
SQLite
Support for SQLite in .net is very good via System.Data.SQLite.
Postgres
Its been a while since I used NPgsql, but its pretty stable and robust. That being said, there will always be edge cases here since postgres is so customizable and extensible.
Conclusion
So thats a tall list of requests. When we have an issue tracker I will be transferring them all there. Until then, use the comments.
A BSON ObjectId implementation in Javascript
Recently I spent a few days implementing the BSON ObjectId data type used by MongoDB in javascript so I could generate ObjectIds in a a web browser. I originally had a specific problem to solve. However, I ended reworking my approach in that instance so I did not have to generate ObjectIds in the browser. Despite this, the code is still a valid approach to solving my original problem. The code is available on the justaprogrammer github org, The project is called ObjectId.js. Although the git repository contains a sample html file to illustrate its usage, all you need is the javascript file, and if you want to support IE6, json2.js.
How It Works
Originally, I wanted an implementation of ObjectId that could interact with the format that the WCF DataContractJsonSerializer serialized objects of the type MongoDB.Bson.ObjectId in the official 10gen MongoDB C# driver. This format looked like this:
{
"timestamp":1298346771,
"machine":8775338,
"pid":20092,
"increment":0
}
Now, it was trivial a JSON object that looked like this. The real issue was filling those 4 values in a sensible manner. I ended up settling on the following:
- timestamp: This is supposed to be seconds since the unix epoch. Javascript represents time as milliseconds since the UNIX epoch. Therefore all I had to do was set this to Math.floor(new Date().valueOf() / 1000)
- machine: This is normally the first three bytes of the md5 hash of the hostname. Since I can’t access that from the browser I store a random number in html5 local storage and a cookie for fallback. This makes the machine id probably consistent for a given combination of machine, logon and browser. Naturally, I could use something like evercookie to make the machine part of the object id more sticky. However, I felt this was “good enough”
- pid: Pid is generated every time ObjectId.js is executed, so usually once per page. This means that pid changes each page load, but remains consistent across Ajax calls on a single page reload.
- increment: This was simple. pid++ every time I generate a new ObjectId.
So if you need to generate ObjectId’s in javascript, check out this class. If you find a bug or have an improvement, fork and send a pull request. Happy coding!.
How many ways can we ‘script’ a stored proc or UDF from the command line?
Update: Added link to the scripts on github.
If you’ve worked with SQL Server for any length of time, you’ve probably written some stored procedures and user defined functions. If you’re like me, you’ve written many. Usually, when you edit a stored proc or UDF. you do it from Visual Studio or Sql Server Management Studio (SSMS). However, what if you want to script the sproc or udf from the command line? Well, there are quite a few ways to skin this cat, and I’ll go through a few.
Using sqlcmd
The simplest way to do this is the command line client that ships with SQL server 2005 and up, sqlcmd, and a system stored proc called sp_helptext. For our example lets have sp_helptext script itself.
sqlcmd -S .\sqlexpress2k8r2 -d master -h-1 -s"" -W -w 1024 -Q "EXEC sp_helptext 'sp_help'" -o sp_helptext.sql
Lets breakdown the arguments:
- -S .\sqlexpress2k8 this is the named instance of sql server I am connecting to. If you want to connect to the local default instance, you can omit this
- -d master This is the database we want to connect to. This can be omitted if you want to script an object from the users default catalog.
- -h-1 -s”" -W -w 1024 These format the output of sqlcmd in a way that makes sense for this task. If you want to understand these options, look at the sqlcmd msdn page, or this stackoverflow question.
- -Q “EXEC sp_helptext ‘sp_helptext’” -Q means “execute this T-SQL and exit.
- -o sp_helptext.sql This writes the command output to the text file sp_helptext.sql
Using a batch file
Now we could easily wrap this in a batch file as I demonstrate here:
C:\Users\zippy\Documents\deleteme>type scriptProcOrUdf.cmd
@echo off
sqlcmd -S %1 -d %2 -h-1 -s"" -W -w 1024 -Q "EXEC sp_helptext '%3'" -o "%3.sql"
So we saved ourselves a few keystrokes with a 2 line batch file.
Using Powershell
Batch files work, but they’re so 2002. Powershell is the CLI of choice these days on windows. So lets see how we would do this in powershell:
So apparently it took me 26 lines of Powershell (if you remove the BSD License) to accomplish what I could have done in a 2 line batch file. Whats the point you might be asking? Well, honestly if you just want the quickest way to script stored procedures to a file from the command line, I’d use the batch file. However, if you wanted to make changes to this script, for example, make it script all stored procedures and udfs in the database. You’d have an easier job with Powershell. You could turn the body of the script into a Poweshell function that takes a SqlConnection object and object name as a parameter. You could then get a list of all the stored procedures and functions with the T-SQL statement SELECT [ROUTINE_NAME], [ROUTINE_TYPE], [ROUTINE_DEFINITION] FROM [INFORMATION_SCHEMA].[ROUTINES]. I’ll leave writing that script as an exercise to the reader.
Using Powershell and “Original Thinking” to do it in less than 26 LOC
In the fictional Star Trek universe, Denny Crane, James T. Kirk cheated on the Kobayashi Maru by reprogramming the no-win scenario to allow him to win. He was awarded for his original thinking. Our scenario is less dire. However, like Kirk, we can change the rules. I chose to use a the open source .NET assemble Atlantis.SchemaEngine.dll. This dll is used for synchronizing schema between two SQL server databases. We don’t want to synchronize or compare anything. However, the library synchronizes database schema’s by generating SQL scripts. Therefore, it can generate DDL for any script-able object in a database. So lets take a look at our leaner, meaner script.
So we went from 26 to 15 lines. That’s a savings of 42%. Some of those are pretty long lines since we have to fully qualify our namespaces in Powershell. However, its a net simplification compared to making the ADO.NET calls directly.
Conclusion
I showed you 4 ways to script a stored proc. I also left plenty of room for you the reader to explore on your own. The code for ScriptProc.ps1 and Script-Object.ps1 lives in the justaprogrammer powershell github repo. If you have any improvements, feel free to leave a comment or pingback below. Also, pull requests are always welcome. Happy Scripting!
RedGate Reflector 7 news roundup
Below is a series of links related to todays news that RedGate will cease to offer a free version of its .NET Reflector product.
- The ZDNet article that broke the story
- The official RedGate announcement
- Youtube interview by the CEO of RedGate
- Redgate forum dedicated to this announcement
- JetBrains blog post alluding to a replacement they have been working on
- Jb Evain has announced he is working on a Mono.Cecil based decompiler on twitter
- For completeness, my earlier post on the matter
- Update (2011-02-05): Miguel de Icaza weighs in.
Stackoverflow Links
There are some stackoverflow questions that might be in need of updating as a result of the fallout of this.
In addition, hopefully there will be some activity on the Reflector section of alternativeto.net.
RedGate is no longer free? I say open source it!
As of today, RedGate’s .NET Reflector is no longer free. It is now $35. In an interview their CEO claimed they can not justify dedicating any developer resources to the product under the current free model.
I am a big fan of Redgate’s SQL Compare tool, it makes DB deploys as easy as code deploys. I’ve used .NET Reflector, although naturally its not the sort of thing I use on a regular basis. I had mixed reactions at first. I do think that $35 is a fair price for the tool. I am glad that they will continue to develop it, and I have no doubt that will happen if a revenue stream is realized from the product. That being said, perhaps it would be better to open source it.
Visual Studio, Line Numbers and Ankhsvn
Although I am a git and DVCS true believer, I still deal with SVN. In Visual Studio this usually means I use AnkhSVN. For anyone paying for the VisualSVN client, I really think you should evaluate this tool. Its Visual Studio Integration is superb and I’ve had no usability problems in recent versions.
One thing about it annoyed me was caused by it integrating too well with Visual Studio. That problem was it is the display of line numbers on the editor screen. I setup Visual Studio to display line numbers in editor windows, and this has the side effect of displaying line numbers in the log message editor of the commit screen. I find this annoying. I don’t need line numbers for my commit log files and they take up precious screen real estate.
Luckily I found a solution. Apparently, in the current version of AnkhSVN, the commit editor has its own section in the Visual Studio Text editor options. So turning off the line numbers on the commit screen, but not the other editor screens can be done from the Tools | Options menu.
Options|TextEditor|General
Options|TextEditor|Log Messages (AnkhSVN)
After that, the line numbers disappear in the commit dialog.
Its the little things like this that make a software experience truly great.
Open Source Software Roundup July 2009 to December 2010
Originally this was supposed to be an end of year round up. However, two things happened. First, I did not finish it before the end of the year. Secondly, I realized that it would be better to list software I’ve discovered over the past 18 months as opposed to a year, due to various personal events that started in July 2009.
If I had to use one word to describe my relationship with technology in the past 18 months, it would have to be the Russian word Glasnov. That word, refers the open policies adopted by Mikhail Gorbachev that lead to the fall of the Berlin wall and communist Russia. I say this because I’ve used many open source programs for the first time in the past 18 months, and how I work with technology has changed greatly in that time. I therefore decided to present a list of some of the open source packages I think are very important. I’ve broken them up into two groups. The first group is programs I started using after July 2009. The second is programs I’ve been using since before then.
New Programs
Far Manager (website)
The Far Archive Manager is a filemanager originally developed by the author of WinRAR. It was recently made open source. It is a console mode orthodox file manager. I became attracted to Far for its ability to handle large directories and unc paths well. However, I became a true believer because of the plugins. There are plugins for everything. For example, the WinSCP plugin lets you connect remotely to scp and sftp shares, and the 7-zip plugin lets you manage archives as if they were directories. However, there are also plugins for the Service Control Manager and event viewer. The latest version even has UAC elevation integrated, although some plugins, notably the 7-zip one, does not. This means I can run far normally, download something to c:\users\zippy\Downloads in Chrome, and copy it to c:\Program Files\something and I will get a UAC prompt. However, if I download a zip file I cannot copy its contents directly to c:\program files.
Ditto Clipboard Manager (website)
I’ve never use a clipboard manager before. Then Stan, the founder of this blog, told me to download Ditto. I don’t know how I lived life without it. Ditto keeps a stack of everything you copy to the clipboard. That stack can be accessed by pressing ctrl+`. There are advanced options suck as searching the clipboard stack, but even this most basic mode of operation can make you more productive.
MongoDB (website)
What can I say about MongoDB? I was first introduced to MongoDB at NYPHP on October 27th 2010. On that evening on the 12th floor of 590 Madison Avenue, Kristina Chodorow destroyed everything I held right, holy and just about data storage and management. So I asked a lot of questions during the presentation, and convinced her into coming down to LILUG and LIPHP for encore performances. I decided there was some merit to all this heresy for some cases. However, I didn’t actually use MongoDB until May of 2010. Then on June 3rd Eliot merged my first patch into the MongoDB code base. I’m still not a MongoDB true believer, but it definitely has its purposes, and I recommend all DBAs should walk a mile in its moccasins before hurling brimstone at it.
Git (website)
Contributing to MongoDB forced me to learn git, and for that I am forever greatful. Distributed version control is the way to go, especially for open source projects. Git is a little weird to use, but that is what happens when a Finnish operating system developer writes a version control system. The other thing that happens is you get a VCS that is very fast.
SConstruct (scons) build system (website)
Scons is like make, except its written in python. MongoDB uses scons as its build system. If I were starting a new C/C++ project I’d consider using scons as the build system. I struggle with editing the mongodb Sconstruct file since I don’t know python, but I was able to pick up enough python to be reasonably productive.
BouncyCastle for .NET (website)
I’ve used the PGP SDK in a previous life when I wrote ETL jobs. Recently, I have to generate a feed for someone else to ETL and they wanted it PGP encrypted. I discovered that there was a free, open source alternative to the PGP SDK that was written in C#. This meant I did not have to mess with managed C++ or P/Invoke, in addition to not paying for PGP license.
GPG4Win (website)
We began to use PGP encryption to store some important documents at my company. As such I installed GPG. The GUI is a little lacking, but the command line version works great.
PHPManager for IIS (website)
PHP Manager is a plugin for IIS manager that allows you to manage your PHP installation on IIS. Quite simply, if you run PHP on IIS and you are not using this tool, you’re doing it wrong.
PHPManager lets you load and unload modules as well as edit all your php.ini settings. It also will tell you if you are doing things wrong in your configuration file, such as not setting a time zone. Finally, phpmanager has a “just open my PHP.ini file in notepad” button. I must stress the importance of installing this addin on any IIS server where PHP is installed.
GreenShot (website)
Greenshot is a great Screen Capture utility for windows. It lacks the video capture features of the closed source tool Jing, which I also use, but it cannot be beat for still capture. Greenshot has all the simple image editing you wold want from such an app. You can highlight, crop, obfuscate, and annotate your images. You can then save them or add them to the clipboard. The obfuscating is a great feature because sometimes you want to post a screen shot on your blog or a support forum, and usually there is sensitive information in it, like your companies code or a private email. Before greenshot, I’d fire up gimp to do this. Greenshot streamlines the process greatly.
Old Favorites
VIM (website)
Vim is my favorite text editor on any operating system. Version 7.3, the latest version, was released in August.
VideoLan (website)
VideoLan is a great no frills media player. However, as of late it is my main media player.
Thunderbird (website – zindus plugin)
I’ve been using gmail for years for my personal email. I really never saw a need for a traditional email client. Then I realized I wasn’t backing up my email. So now I install Thunderbird on all my machines and keep it running in the background to backup my gmail account. I also have zindus to backup my google contacts. While I should probably switch to a more lightweight method like fetchmail or mutt, thunderbird serves my needs for now.
GIMP (website – windows downloads)
I have a confession to make. I don’t know how to use photoshop. I’ve been using gimp these years, so I think photoshop has a weird UI and gimp is normal.
Inkscape (website)
Inkscape is a vector graphics program. Its native format is SVG with some extensions. I used it to generate the graphics for the jquery.collapsiblePanel plugin.
IIS7 Web Application Enabled Protocols: Yet Another WCF Gotcha!
One of the cool things about IIS 7.0 and WCF is the ability to serve WCF endpoints with non-http bindings. Naturally, this new feature presents new opportunities for the developer to get frustrated by WCF configuration headaches. This blog post is about one of them.
I was writing a WCF web service that had three endpoints, Json, Soap 1.1, and net.tcp. This services primary purpose was to be the middleware for the mongo database where my applications data was held. In the end, I didn’t need the net.tcp endpoint, making this exercise a complete waste of time. The reasons for my architectural decisions for this app are the subject of another blog post. For now, lets just say if you are supposed to learn more from your failures than your successes, I should get an honorary doctorate for this app.
In the past I’ve mixed soap and net.tcp in EXE hosted WCF services with great success. However, since I’ve yet to find a JSONP endpoint solution for WCF that allows request parameters, I needed to host this in IIS on the same site as my project. So I made a .svc file in my website, added the service dll as a reference, and ran it. I promptly got the the following error:
It was a bit frustrating to find the solution, but I did eventually. In IIS manager you have to select Advanced Settings in the applications Application folder, or website if the app is running at the root. In the advanced settings dialog is an option called Enabled Protocols. It probably contains the value http or http,https. You simple have to append ,net.tcp to the current value.
After that, everything works.
As an epilogue to this adventure, since I forgot to take all the screenshots needed for this blog article at work, I ended up having to make an example project to reproduce the error at home. As such I took an older example WCF project I wrote called EchoService and adding a website host to it in addition to the exe host. This improved version of EchoService can be found at the justaprogrammer github org. Feel free to use this service s the basis for any WCF related instructional materials. The code is licensed under the very permissive MIT license.
What’s new in JsonViewer?
This post is the announcement of the justaprogrammer fork of JsonViewer, an excellent codeplex project whose maintainer has not logged into codeplex in a while and has not seem my patches.
JsonViewer is a suite of three .NET tools that visual render JSON via .NET WinForms. These tools are:
- JsonView: the standalone EXE form.
- JsonVisualizer: A Visual Studio Debugging visualizer
- JsonInspector: An inspector for the popular and excellent debugging proxy Fiddler.
This fork started as a result of me scratching some very small and specific itches I will describe below.
Obtaining source and binaries
I have created a github project for this fork in the justaprogrammer github organization. There is one download there currently. More will be added periodically and news will appear on this blog.
Human readable DateTime display
I originally used the Fiddler inspector to debug WCF services I wrote with JSON endpoints. It worked very well for this. However, some of my response contracts had DataTime properties. These were rendered as /Date(SECONDS_SINCE_1970)/. This format was pretty useless in that I had to copy the value to a javascript console like firebug to get a human readable date. After I got no responses on my bug report, I did a bit of twiddling until the output was human readablke.
Request Inspector
Since I was debugging WCF services, both my requests and my responses were JSON formatted strings. However, JsonViewer only implemented a response inspector. Once again, I filed a ticket, saw no response, did a little bit of refactoring and I had a request and response viewer.
The future
I have an experimental branch where I got JsonViewer to build against the latest version of Newtonsoft.Json.dll. I got it working, but I lost the DateTime formatting in the process. Also, code needs some major refactoring use the new version of the library optimally. Finally, I hope to attract feature requests and code from existing fans of JsonViewer until the point where the original maintainer becomes involved in the original project.