Command Line Clipboard

Comments [0]

I just needed to copy the output of the DOS command dir to another file. The usual mechanism is to send the output to a file, open the file, then copy the contents to the actual destination file. I was sure there must be an utility that would copy output in the command line to the clipboard and indeed I found such an utility. Kudos to Steve Kemp and his clipboard utility. I was able to do what I needed very easily:

C:\>dir /s /b *.txt | clipboard -

Then just paste into Notepad++ or other application!


Comment Section

Comments are closed.


Altering SQL Server

Comments [0]

I was working on a project last week where I needed to update the columns and data types in tables in SQL Server. It is pretty quick to do this manually such as:

ALTER TABLE orders
    ALTER COLUMN orderid varchar(36) NOT NULL

I prefer this compared to the code SQL Server generates in the change script which typically renames the existing table, creates a new table, copies the data from the previous table to the new table, and then restores indices and keys. While that is appropriate for dramatic changes, it is overkill for something of this nature.

Since I love using tools and scripting, I decided to make a small Python program which would let me just specify which tables and columns to modify. This simplifies maintenance as I only have to manage the tables and columns rather than reading the entire SQL script looking for missing columns or keys.

While I was developing the Python script, I wanted to add enough SQL to avoid obvious errors such as trying to add duplicate primary keys or trying to alter columns that are already NOT NULL. I used MSDN and looked at some of the generated SQL to get the following templates:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('orders') AND COLUMNPROPERTY(id, 'orderid', 'AllowsNull') = 1)
BEGIN
    PRINT 'Altering column: orders.orderid'
    ALTER TABLE orders
        ALTER COLUMN orderid varchar(36) NOT NULL
END
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('orders') AND OBJECTPROPERTY(id, 'TableHasPrimaryKey') = 0)
BEGIN
    PRINT 'Adding primary key: orders.orderid'
    ALTER TABLE orders
        ADD CONSTRAINT PK_orders
            PRIMARY KEY (orderid)
END
GO

So the Python script used the string templates to write the output SQL and it looked like:

#!/usr/bin/env python

"""Generate a T-SQL script to set the appropriate NOT NULL
columns and PRIMARY KEY indices for the database.

TODO: Create indices for specific columns as well
"""

import string

# database properties

tables = {
    "orders":           { 'notnull': [('orderid', 'varchar(36)')],
                          'pk'     : ['orderid'] },

    "accounts":         { 'notnull': [('accountid', 'varchar(50)'),
                                      ('name', 'varchar(100)')],
                          'pk'     : ['accountid'] },

    # More tables and columns...
    }

# Functions

def generatenotnull(table, column, datatype):
    print alternotnull % { 'table':table, 'column':column, 'datatype':datatype }

def generatepk(table, columns):
    print alterpk % { 'table':table, 'columns':string.join(columns, '"') }

# SQL statements and formats

alternotnull = """IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('%(table)s') AND COLUMNPROPERTY(id, '%(column)s', 'AllowsNull') = 1)
BEGIN
    PRINT 'Altering nullable column: %(table)s.%(column)s'
    ALTER TABLE %(table)s
        ALTER COLUMN %(column)s %(datatype)s NOT NULL
END
GO"""

alterpk = """IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('%(table)s') AND OBJECTPROPERTY(id, 'TableHasPrimaryKey') = 0)
BEGIN
    PRINT 'Adding primary key: %(table)s.%(columns)s'
    ALTER TABLE %(table)s
        ADD CONSTRAINT PK_%(table)s
            PRIMARY KEY (%(columns)s)
END
GO"""


# Main entry
if __name__ == "__main__":
    for table in tables:
        print "/* ==== Processing %s ==== */" % table
        tabledict = tables[table]
        for column in tabledict['notnull']:
            generatenotnull(table, column[0], column[1])
        # TODO: Allow for compound primary keys
        generatepk(table, tabledict['pk'])
        print ""

Comment Section

Comments are closed.


During a database schema upgrade yesterday, I had to create a new primary key field for a table and then generate the new primary key values. At the moment this key value is a custom generated value by a Python class, but it will eventually use an RFC 4122 or ISO/IEC 9834-8 compliant algorithm (see Wikipedia for more information).

I had to decide how to generate this value and I ended up using a combination of Python, gawk, and T-SQL. I suppose I could have left out the gawk, but it would have taken almost the same amount of time to do it in Python and it took a just moment to write. I will present three different methods that I could have accomplished the same task, although there were reasons that I used the method that I did.

Method 1 - Python, gawk, and SQL

# Requires Python 2.5 or higher
import uuid
for i in range(200):
    print uuid.uuid4()

C:\>python genuuid.py > uuid.txt

C:\>gawk "/^'/ { i += 1; print \"INSERT @issueid VALUES (\" $1 \", \" i \")\"; }" uuid.txt > uuid.sql

/* Add the table variable to the SQL file */
DECLARE @issueid TABLE (
    issueid varchar(36),
    issuenum int
)

Method 2 - Python and SQL

# Requires Python 2.5 or higher
import uuid

print """DECLARE @issueid TABLE (
    issueid varchar(36),
    issuenum int
)"""

for i in range(200):
    print "INSERT @issueid VALUES ('%s', %d)" % (uuid.uuid4(), i+1)

C:\>python genuuid.py > uuid.sql

Method 3 - SQL

/* Add the table variable to the SQL script */
DECLARE @issueid TABLE (
    issueid varchar(36),
    issuenum int
)
DECLARE @i int

SET @i = 0
WHILE @i < 200
BEGIN
    INSERT @issueid VALUES(NEWID(), @i+1)
    SET @i = @i + 1
END

/* Process table here */

After looking back, Method 2 seems to be the most straight-forward and self-contained method. The reason I chose Option 1 was that it was the first method that I thought of. Now that I am using Python more, I will probably start favoring quick solutions like Method 2.


Comment Section

Comments are closed.


One of the strange requirements I had on a project was to use the joystick button to synchronize the computer clock with an outside contact. The idea was that all of the clocks in the plant would be synchronized periodically by using a digital contact. The software would wait for the joystick button and adjust the clock to the nearest hour.

You must realize that this was long before GPS devices were common and so it seemed like a good idea at the time. This all occurred around the Y2K issue, so adjusting the clock turned out to be the most interesting part of the project. At the time I was very concerned with leap year and the year 2000, so rather than using a component I wrote a little code to calculate the next day. The code had to check if the next day was a new month (with leap year rules) and even a new year.

In researching calendar components, I learned a lot about the history of different calendars. It is very interesting but ultimately you end up wondering how historians are able to diffinitively tell when an event occurred since many countries did not accept calendar changes at the same time. For example, Russia did not adopt the Gregorian calendar until 24 January 1918 and Greece did not adopt it until 1 March 1923. You can check out the Gregorian calendar article on Wikipedia for more information.

Incidentally, the one component that I liked but did not use was Serial Day Number which converts a day into a number and allows you to perform simple mathematics and convert it back to a day. For example, to add a week you would just convert the day to a serial day number and add 7 days and convert it back to a date. Usually that is simple these days with C#, Java, and Python, but it was more complicated with C++ back then.

There is an alternative form of converting a day into a number where instead of a serial number (1, 2, 3, etc.) you convert the day into a human-ledgeable number like 20070314. The basic formula is: x = year * 10000 + month * 100 + date. The advantage of this format is that you can decode the date easily and it can be indexed quickly by a database as opposed to a native date type. The disadvantage is that you have to follow all of the normal calendar math to manipulate the number.


Comment Section

Comments are closed.


A while back I needed the dashboard for the Key Performance Indicators (KPI) to display nicely formatted numbers. When the KPI values started to get large (millions, billions, etc.) the numbers became almost unreadable. In order to simplify the display of the numbers, I ended up converting the values to scientific notation. However in order to make the numbers even more readable, I converted the scientific notation into powers of 103. I converted the floating point number into a string that was still a valid floating point number, but it used the exponential notation such as 1.0e6. I then used a regular expression to convert the exponent into HTML that the As a general practice, I would also put the raw formatted floating point number in the tooltip for the display. The function that actually performs the scientific notation grouping and conversion is toTrioExponential().

// Formats the value as an exponential as a multiple of 3
// NOTE: The result is a string that is still a valid floating point number
function toTrioExponential(value, fixedDigits)
{
    var mag = parseInt(Math.log(Math.abs(value) / Math.LN10);
    var rem = mag % 3;
    if (rem == 2)
        ++mag;
    else if ((rem == 1) || (rem == -2))
        --mag;
    else if (rem == -1)
        mag -= 2;
    var coeff = value * Math.pow(10, -mag);
    if (fixedDigits)
        return coeff.toFixed(fixedDigits) + "e" + mag.toString();
    else
        return coeff.toString() + "e" + mag.toString();
};

The script supports changing the scaling criteria. Just modify the sciNot.scaleDigits and sciNot.scaleThreshold or supply the parameters to the function sciNot.floatAutoScale(elem, floatValue, scaleThreshold, scaleDigits);. The scaleThreshold and scaleDigits parameters are normally optional. To test the script, here is sample output using the default scaling parameters.

Raw number Trio Sci Not Formatted
1000000 1,000,000.00 1,000,000.00
10000000 10,000,000.00 10,000,000.00
100000000 100,000,000.00 100,000,000.00
1000000000 1,000,000,000.00 1,000,000,000.00
10000000000 10,000,000,000.00 10,000,000,000.00
100000000000 0.10e12 0.10  x 10 12
1000000000000 1.00e12 1.00  x 10 12
10000000000000 10.00e12 10.00  x 10 12
100000000000000 0.10e15 0.10  x 10 15
1000000000000000 1e15 1.00  x 10 15

Here is the javascript source for ScientificNotation.


Comment Section

Comments are closed.


<< Older Posts | Newer Posts >>