Tuesday, December 28, 2010

The SQL Server and .Net equivalent of PHP and MySQL's SHA1 function

In PHP and MySql there is an sha1(str) function which generates an sha1 hash of a string.

Their usages are simple:
<?php
   echo sha1('password');
?>
//Outputs:5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8
mysql> select sha1('password');
+------------------------------------------+
| sha1('password')                         |
+------------------------------------------+
| 5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8 |
+------------------------------------------+
1 row in set (0.00 sec)
Similarly, .Net has an SHA1 method within the System.Security.Cryptography  namespace which can be used in the following manner to generate the same hash.
 public static string Sha1(string hashStr, Encoding encoding)
{            
   var sha1 = new System.Security.Cryptography.SHA1CryptoServiceProvider();
   var hash = sha1.ComputeHash(encoding.GetBytes(hashStr));
   return BitConverter.ToString(hash).Replace("-", "");            
}


Console.WriteLine(Sha1("password", Encoding.ASCII)); -> 
//Outputs:5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8
In SQL Server there is a HashBytes function which can return the sha1 hash as well. Since the HashBytes function returns a varbinary, the result can be converted to a string using a sql server system function in order to match the php or mysql function.
select sys.fn_varbintohexsubstring(0, HashBytes('SHA1', 'password'),1,0)
--------------------------------------------------------------------------
5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8

(1 row(s) affected)

Saturday, December 18, 2010

Netbeans Native Linux GTK Look and Feel

Fedora updated my Netbeans install last night and I had to set the look and feel in the config file again. Since this happened a few times and I never remember the laf option I thought I would write it down. Especially since the default Netbeans look and feel seems very ugly to me.

To change the laf to GTK add "--laf com.sun.java.swing.plaf.gtk.GTKLookAndFeel" to the netbeans_default_options in the netbeans.conf file. The netbeans.conf file is located in /usr/share/netbeans in the etc subdirectory of the netbeans version you are running.

Thursday, December 16, 2010

Using Windows Powershell To Find A String Within Files

The below will find searchstr in all files with the .txt file extension in the current directory and all sub-directories and place the results in c:\tmp.txt
Select-String searchstr $(dir . -recurse | where {$_.extension -eq ".txt"}) | out-file c:\tmp.txt

Sunday, August 1, 2010

Generating PHP Table Objects from MySql

I needed to generate some PHP table objects for a couple MySql tables so wrote up the query below real quick. I am sure there are better ways to do this through IDE's, tools, or PHP. There will also be some kinks that can be worked out such as turning database fields such as create_date into getCreateDate instead of getCreate_date but this did the trick for me. It will generate class members, getters and setters.
SET @table = 'post';
SET @db = 'blog';

SELECT '<?php'

UNION ALL

SELECT
    CONCAT('class ',
             CONCAT(
                    UCASE(SUBSTRING(@table,1,1)),
                        SUBSTRING(@table,2)),
            '{')

UNION ALL

SELECT
    CONCAT('\tprotected $_', COLUMN_NAME, ';')
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = @table
    and table_schema = @db

UNION ALL

SELECT '\n'

UNION ALL

SELECT
    CONCAT('public function get',
        CONCAT(UCASE(SUBSTRING(COLUMN_NAME,1,1)),
                     SUBSTRING(COLUMN_NAME,2)),
        '() {',
        '\n\t return $this->_', COLUMN_NAME, ';\n','}' )
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = @table
    and table_schema = @db

UNION ALL

SELECT
    CONCAT('public function set',
        CONCAT(UCASE(SUBSTRING(COLUMN_NAME,1,1)),
                     SUBSTRING(COLUMN_NAME,2)),
            '($', COLUMN_NAME, '){',
        '\n\t$this->_',COLUMN_NAME, '= ',
            CASE
                WHEN data_type = 'smallint'
                  OR data_type = 'int'
                    THEN '(int)'
                WHEN data_type = 'varchar'
                  OR data_type = 'text'
                  OR data_type = 'enum'
                    THEN '(string)'
                ELSE ''
            END,
            ' $',COLUMN_NAME, ';',
            '\n\treturn $this;',
    '\n}' )
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = @table
    and table_schema = @db

UNION ALL

SELECT '}';
When used it will generate something like the following:
<?php

class Post {
    
    protected $_id;
    protected $_title;
    protected $_post;
 
    
    public function getId() {
        return $this->_id;
    }
    public function getTitle() {
        return $this->_title;
    }
    public function getPost() {
        return $this->_post;
    }
    
    public function setId($id) {
        $this->_id= (int) $id;
        return $this;
    }
    public function setTitle($title) {
        $this->_title= (string) $title;
        return $this;
    }
    public function setPost($post) {
        $this->_post= (string) $post;
        return $this;
    }
    
}

Tuesday, July 27, 2010

SQL Server and decimal rounding

As part of some analysis I was doing, I needed to take exactly 2 decimal places of a number. For some reason my query was not outputting data I had expected. I pinpointed this down to rounding when converting to a decimal of only 2 decimal places.

For Example:
select cast(10.554 as decimal(5,2)) --returns 10.55
select cast(10.555 as decimal(5,2)) --returns 10.56

To fix this issue all I needed to do was shift the decimal place to the right a couple digits, take the integer portion, and then shift the decimal place to the left a couple digits.

select cast(cast(10.555  * 100 as int) / 100.0 as decimal(5,2)) --returns 10.55

As an alternative approach, we can use the modulus to extract the value after the decimal place we no longer need.

select (10.555%.01) --returns .005

Then subtract the result from the original number:

select cast(10.555 - (10.555%.01) as decimal(5,2)) --returns 10.55