In this last installment of the serial validation series, we’re taking a look at the database structures used by the web side scripts. You’ll need to know some SQL-92 or T-SQL basics, if you don’t already please visit http://www.mysql.com/ and www.microsoft.com/sql/ to get acquainted.
First, some database design. We’ll keep serial data on one table and user data on another table.
The serials table columns:
id (bigint) , productname (nvarchar), serial (nvarchar), activated (tinyint)
The users table columns:
id (bigint), firstname (nvarchar), lastname (nvarchar), username (nvarchar), password (nvarchar)
During validation we can use the SQL SELECT statement to make sure the serial number has not been used before and has not been activated yet:
SELECT id FROM serials WHERE serial=@serial AND activated != 1
This is a T-SQL compliant query, where @serial is the parameter containing the serial number being queried. If this query doesn’t return a value, that’ll mean that the serial number specified is either invalid or has already been activated.
Otherwise, we can go ahead and activate this serial number. The T-SQL statement for this activation is:
UPDATE serials SET activated=1 WHERE serial=@serial
Now, all that remains to do is to add an entry in the users table:
INSERT INTO users(firstname,lastname,username,password) VALUES(@firstname,@lastname,@username,@password);
My suggestion for you is to implement all of the above statements as separate stored procedures and not stand-alone “injected” queries lying around inside multiple web script files. It’s better that way since code maintenance overhead is reduced – just like replacing all separate Run Program statements with a common Label statement like we did earlier for the MSIcode update script.
So as you can see, in a few simple lines, we’ve implemented the business logic/workflow of our application, in an integrated fashion with InstallAware. You’ve probably noticed that there isn’t any directly InstallAware related content in this post, but we’ve had a lot of requests for samples of web server back-end behavior for things like serial activation, so we hope this helps!
Thank you and we’ll be back soon,
Panagiotis Kefalidis
Software Design Team Lead
InstallAware Software Corporation