Auto Generate a Code Column Value in SQL

February 7th, 2010 Craig Tadlock No comments

Often in database design a table has a secondary unique key which defines a random alpha-numeric value; lets call this Code. While the table’s primary key is used for foreign key references in the database, the Code column has several advantages and uses. Since it is not a sequential value it can be shown to the user without giving away possible valuable information; for example giving away your sales volume. It can also be used as a URL query parameter to discourage one-up-attacks (note this should not be a replacement for proper data security).

So now that we are sold on having a Code column, what is the best way to implement it? Here is a method you can use to implement a proper Code column and have the database generate the value for you. This is a similar design pattern to using an IDENTITY column to generate the next value for a primary key. We would like for the column to have a default value of a new Code value, such that the application tier doesn’t have to worry about setting the value. An issue with this in SQL Server is that column default values do not allow for ‘dynamic’ values; we can use a view to get around this. As for the Code generation algorithm I piggybacked on the NEWID() function which generates a new uniqueidentifier. There are countless other algorithms you could use for this.

Create a view to generate the new codes…

CREATE VIEW [dbo].[Codes]
AS
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), ‘-’, ”), 0, 16)) AS Code;

Create a function which returns a new code…

CREATE FUNCTION [dbo].[NewCode]
( )
RETURNS NVARCHAR (50)
AS
BEGIN
RETURN (SELECT Code FROM Codes)
END

Now we can use the dbo.NewCode() function as the default value for our table’s Code column..

ALTER TABLE [dbo].[XXX] ADD  CONSTRAINT [DF_XXX_Code]  DEFAULT ([dbo].[NewCode]()) FOR [Code]

That’s it! Now if you insert a row into the table the Code column will have a new value in it by default. Simple.

Generate Lorem Ipsum Text in MS Word

February 5th, 2010 Craig Tadlock No comments

Here’s a quick tip: To generate “Lorem ipsum” text in Microsoft Word type “=lorem(x, y)”; where x is the number of paragraphs and y is the number is sentences. I regularly use this for fake text when developing websites.

Payoff.com Peaks its Head Out of Stealth Mode

January 28th, 2010 Craig Tadlock No comments

One of our startups Payoff.com has an updated splash page along with a customer survey and links to their Twitter and Facebook accounts. This obviously isn’t a full product launch, but startups have to start somewhere. It’s a good practice to start collecting customer emails and information as early as possible; it will help you guide your product feature prioritization discussions. Take a look at the www.payoff.com site and let me know what you think.

SEO Analysis Tool

January 25th, 2010 Craig Tadlock 1 comment

Everyone knows the importance of SEO for their website. While there are many great traffic analysis tools (Google Analytics, Quantcast…) there are not very many tools for website SEO analysis. The individual search providers have some basic tools to help with this (Google Webmaster Tools) but they aren’t really that useful. A new tool WooRank is looking to fill this gap. It’s a free tool that allows you to plug in a url and generate a detailed SEO analysis report. From looking at the report it generated for this site I can tell it does a decent job of analysis. It covers all the basic items (meta tags, header tags, content %…) and had some others I wasn’t familiar with. These types of tools have an inherent challenge in that SEO is more of an art than a science given we do not know that exact algorithms for the search engines. I don’t see this replacing good human analysis, but it’s a great place to start when analyzing a websites SEO.

From a company perspective I think this is a good usage of the freemium model. Show your customers some initial value and then up sell them to the premium features.

Categories: marketing Tags: , , , ,

Making Project Estimation Fun

January 22nd, 2010 Craig Tadlock 1 comment

Nobody really likes project management and task estimation, but it’s a necessary evil of product development. I’ve seen quite a few ways to handle estimating tasks; some of which work better than others but none of them I would classify as “fun”. Until now… On a current project with a vendor I was exposed to a process and tool called poker planning. It’s a very simple card based game which is used for a group of people to come to a consensus on the estimate for a task list. There is a free tool that will host a game for you called Planning Poker. The process is pretty simple…

  1. Come up with the global user story list for the project
  2. Import these user stories into the Planning Poker tool
  3. The players of the game are the developers who will be implementing the user story, the product owner does not play but needs to be available
  4. For each user story do the following in less than 2 minutes each…
  5. The product owner gives a quick description of the user story
  6. All of the players choose a card which represents the relative amount of work that user story will take to implement; note these are only relative numbers, not absolute man-days
  7. All of the cards are turned over for everyone to see.
  8. The players with the highest and lowest cards must both explain their rationale behind their high and low choices
  9. The turn is played again until their is a consensus about the amount of effort

At the end of the process you have a relative effort value of each user story. The next step is to map and agree on how to translate those values into absolute working days. Once you get into the process it’s quite fun and effective. I suggest you try it out on your next small project or iteration.

WANTED: iPhone and Android Mobile App Developers

January 20th, 2010 Craig Tadlock No comments

We have an immediate need to develop iPhone and Android mobile applications. We are looking for either an independent contractor or small agency to design, develop and test a couple mobile applications for an exciting southern California startup. The partner must have a proven history of successful mobile product delivery and be able to show existing applications. Please submit your resume and examples to biz@tadlockenterprises.com.

WANTED: .NET Developer in SoCal

January 13th, 2010 Craig Tadlock No comments

One of my clients here in southern California has an immediate opening for a full-time .NET developer. If you’re interested email me your resume at biz@tadlockenterprises.com.

Brand new startup seeks junior to mid level software engineers to help build its new product from the ground up and from a legacy free codebase.  Learn from a talented, experienced team as you contribute to all facets of the product’s underlying technology.
Ideal candidates will
•    Have solid understanding of OO fundamentals
•    Be highly effective communicators
•    Have worked in a disciplined team development environment (specifications, source control, bug tracking, unit testing)
•    Be comfortable with the development of and challenges of web based applications
•    Stand out as quick learners
•    Be able to function independently as appropriate and responsibly while handling multiple tasks
Skills
•    2-3 years .Net Framework (2.0, 3.0, 3.5)
•    2-3 years C#
•    HTML/XHTML
•    1-2 years of ANSI or T-SQL
•    Advanced CSS a plus
•    Advanced JavaScript and/or JavaScript library experience a plus (e.g. jQuery, YUI, Prototype)
•    ASP.Net MVC a plus
Role
•    Work on multiple small projects related to the development of a MVC based web application
•    Develop data queries as needed to support the main application or in the creation of new internal and administrative tools
•    Integrate with multiple 3rd party services and APIs, including the usage of several open source libraries (Subsonic, Moq, Ninject, Solr)
•    Assist senior members of the team in designing technical specifications, designing implementation and architecture details, and assessing new technologies and tools

Categories: jobs Tags: , , , ,

Great UX Prototyping Tool

January 12th, 2010 Craig Tadlock No comments

I’ve been in search of a great prototyping tool for a long time and finally have come across one; Balsamiq. It allows a team to quickly produce application mock ups to represent UX and UI concepts. The part I really like is that it visually represents the application as if you drew it by hand with a pencil; this has the psychological effect of forcing you to look at what the UI represents rather than the look and feel. You should already be doing prototypes for your applications because developers are VERY expense. Take a look at it and let me know what you think.

Categories: ux Tags: , , ,

Remote Admin IIS for TFS 2010 Builds

December 24th, 2009 Craig Tadlock 1 comment

If you are building a web application then one of the key things that needs to be done in the build and deployment process is manipulating a remote IIS server. Your requirements could be as simple as stopping and starting a website or as far as creating websites and application pools on the fly. As I’ve previously discussed the default TFS 2010 build template activities leave much to be desired in this area (read more here).  Lucky for us this is solved fairly easily with a mashup of technologies; Powershell 2.0, appcmd.exe, custom TFS build process templates and custom activities. In this simple example we will do a basic website deployment which includes stopping an existing remote website, deleting the existing files, coping our files to the deployment folder and starting the website back up…

The key to remote IIS administration is Powershell 2.0; it has the ability to run remote commands on servers using WinRM. To setup the build server and deployment server follow these steps. Note these are for Windows Server 2008 R2.

  1. Make sure Powershell 2.0 is installed on both the build agent and deployment server.
  2. Reduce the Powershell execution policy; in Powershell run the command set-executionpolicy RemoteSigned
  3. On the deployment server you will need to enabled remote administration. The simplest way is to run the command winrm -quickconfig and follow the basic questions (all yes). This will enabled administration on HTTP and open a hole in the firewall.
  4. Obviously make sure IIS in installed on the deployment machine.
  5. Make sure the user that will be executing remote commands has the appropriate permissions on the deployment server. For me my user was TFSSERVICE and I gave it administrator rights on the deployment server. You can probably lock the permissions down further if you like.

As a test to make sure this is working outside of the TFS build process try this…

  1. Create a test website (TestWebsite) on the deployment server (DeploymentServer)
  2. On the build server open up a command prompt and run the command: icm deploymentserver “c:\windows\system32\inetsrv\appcmd.exe stop site TestWebsite”
  3. On the deployment server check to see if the website is stopped; if it is then it worked and if not then you will need to debug

Once you have that working you can incorporate it into the TFS 2010 build and deployment system. To make my life easier I created a custom activity to easily execute remote commands; RemoteProcess.xaml. It wraps the InvokeProcess activity and constructs the appropriate Powershell command and handles the errors.

Now it is trivial to implement the above website deployment example. Using the RemoteProcess activity use these as the RemoteCommands and set the RemoteServer argument to DeploymentServer.

Stop Website

“c:\windows\system32\inetsrv\appcmd.exe stop site TestWebsite”

Start Website

“c:\windows\system32\inetsrv\appcmd.exe stop site TestWebsite”

You can see how powerful this is; now you have the ability to execute ANY command on the deployment server. Using this to administer IIS is just the start; this will be useful for SQL server database deployments, windows services, security… anything really.

UPDATE

After working with this for a bit I found a functional bug; the above solution does not return the remote command’s exit code. This is important so that you can have logic for success and failure of the remote command. The solution took me a long time to figure out! The new RemoteCommand should be…

“-noprofile -command “”$x = icm ” + RemoteServer + ” {” + RemoteCommand + “; $lastexitcode}; if ($x.length -gt 1){$x[0]}; exit $x[$x.length - 1]“”"

I created a few arguments on the RemoteProcess activity to support this, Output, Err and ExitCode. So to finish the above example of stopping a website would be…

powershell -noprofile -command “$x = icm DeploymentServer {c:\windows\system32\inetsrv\appcmd.exe stop site TestWebsite; $lastexitcode}; if ($x.length -gt 1){$x[0]}; exit $x[$x.length - 1]“

Hack a Wireless Network for $17

December 8th, 2009 Craig Tadlock No comments

Cloud computing has started to change the way we think about building and hosting applications. The access to large computing resources on demand has allowed applications to rapidly handle their scaling needs. But it also has other uses… like breaking (oh, I mean testing) your (someone else’s) wireless network. WPA Cracker is a cloud solution to crack WPA-PSK wireless networks. It does this by leveraging a 400 CPU cluster and  a 135 million word dictionary. You just need to upload your network traffic capture to their server and after a bit of crunching it spits out they key. They claim it takes about 20 minutes to break, which will cost you all of $17. I think this is great. I’m pretty sure professional hacking groups had access to these types of resources in the past; so it’s really nothing new. Hopefully now that it has become a bit more public people will take the proper security steps to protect their networks, servers and data.