Author Archives: davin

Confluence Speakeasy Favorite Menu Extension

So, lately I’ve been doing a lot of customization at work on a product we use called Confluence. It is quite possibly the slickest wiki I have ever used. It has an awesome plugin model and pretty huge market for community developed plugins. One plugin that Atlassian has created allows for creating modular client based extensions to the wiki. It is called Speakeasy.

menuI happen to have a few favorite pages in the wiki that I refer to all the time and I have been wanting a favorite menu that shows up on every page. Alas, I couldn’t find one that existed, so I decided to make my own.

Feel free to download the extension and modify it to your needs.

Update:
I have released this on the Atlassian Marketplace for free.

Leap Motion – A Review

So, I want the Leap Motion to be this awesome thing that makes my computer word kinda like Minority Report. I really want it to be a totally new way of using my machine that doesn’t just replace the keyboard and mouse but can enhance it much the same way that touch interaction has these day. Unfortunately, I just can’t say that right now.

So far it has been kind of a let down … especially the apps that are geared toward native computer interaction. So far the app that Leap has put forward is just way too hard to use. It is super difficult to figure out in 3D space where to put you hand so that you get a cursor on your monitor, and once you have done that actually pointing at something and “clicking” it is super painful. I have to go so slow that it makes it worthless to use. I was hoping that they would give you the ability to calibrate the Leap to the position of your monitor and they in essence allow you to use your monitor as a touch screen … thus making it much more easy to accurately “click” on things and interact with the native OS. Similar to this Youtube video. Alas, they went for the 3D air clicking approach which just doesn’t work.

Granted this is a new device and hopefully it will continue to get better. I do hope that someone decides to create an app that lets you turn ordinary monitors into touch screens … maybe I’ll work on it. icon_smile

Kid Browser

Desmond always amazes me as to how much he can grasp. Kids are such sponges and they can do way more that we think they can do. For example we let him go to a few websites that we have deemed safe for him to look at. I am amazed that he picks up how the games work without Melissa or I telling him how to do them. He has gotten really good at using the mouse.

wommAnyway, occasionally we would let him look at these sites and then later we would find that he minimized the browser and Quicken or something else that we didn’t want him messing with was up on the computer. So, I decided to make a web browser specifically tailored to his age so that he can only go to the sites that we say are ok and he cannot click out of them into anything else. Also, I didn’t want him to easily be able to minimize the browser or close it. Thus, KidBrowser was born.

I don’t claim that it is any great work of programming, and it could probably be more visually appealing, but I am no graphic designer. Anyway, if you have kids and you want to use it yourself feel free to grab a copy and set it up on your machine. I don’t claim that it will work perfectly on your machine. I have only tested it on my box, which is a Windows 7 machine. However, if you have issues let me know and I will try and fix it.

I have created a bitbucket project for this browser, so feel free to download the source and make fun of my coding abilities if you want to.  Links and screenshots are below.

Bitbucket Project
https://bitbucket.org/fredclown/kidbrowser/wiki/Home

Latest Compiled Version
http://bit.ly/sfdF4P

This is what the browser looks like. The left navigation is the web pages that we have deemed are ok for him to look at. You can add as many as you like.  If you add more than will appear in the column you will see scroll arrows appear so that you can scroll the nav.

KidBrowers

This is the config module.  You can use this to add sites to the navigation menu. Currently, I suggest using the Gecko browser option as IE seems to have an issue with at least one site I that I know of, Disney Junior.  The configuration module isn’t pretty and I will probably work on it a bit more, but I wanted to get something made so that the browser could be easily configured. I think most everything is pretty self explanatory. There are a couple things that I will explain below.

Config

Resolves To:
This is mainly used in conjunction with the “Allow Sub-links” checkbox. Sometimes if you put in a web address it will redirect you to another page. For instance if you put in http://www.disney.com it might redirect you to http://go.disney.com. If you have allow Sub-links checked and your “Site URL” is set to http://www.disney.com then you would be able to visit any links that start with http://www.disney.com, unless specifically blocked in the block section.  However, if the site automatically redirects the home page to something else such as http://go.disney.com then in order to get all of the pages under http://www.disney.com and the home page http://go.disney.com you would need two different entries. That is why implemented the “Resolves To” so that the browser will consider the redirected first page to be apart of the rest of the site.

Allow Sub-links:
This will allow any pages that start with the “Site URL” to be viewed.  Thus is the “Site URL” is http://www.disney.com, then http://www.disney.com/junior would be available as well.

Blocked Sites:
Blocked sites take precedent over the Allowed Sites.  By default everything is blocked except what is specifically allowed.  However, if you allow sub links for a certain site there may still be a couple undesirable pages. You can block these pages specifically. Additionally you can block sub pages or any pages that start with a certain URL. Thus, if http://www.disney.com/junior was allowed with sub-links, you could block the Winnie the Pooh video page by putting in http://www.disney.com/junior/winniethepooh/video for a blocked url. If you clicked “Block Sub-links” then anything under http://www.disney.com/junior/winniethepooh/video would also be blocked … such as http://www.disney.com/junior/winniethepooh/video/theblusteryday.

Finding the Character Set For a SQL Field

The other day I was working on a project and I needed to know all of the different types of characters contained in a SQL field so that I could make a regular expression pattern that would not fail. Thus, I decided to write a stored procedure that would loop through a range of characters and test if that character is contained in a specified field in any records. From there I can get a list of all of the used characters and then more intelligently write my regular expression pattern.

This could also be handy if you are trying to cast a field and you keep getting errors because of a character that won’t cast correctly. An example would be trying to cast a varchar or char field into an int and for some reason or another one of the records has a letter in the field. Your cast will fail and it can be tricky to track down where the issue is. With this stored procedure you can quickly see what the offending character is and then query up all the records containing that character.

Example usage:

exec FieldCharacterSet 'dbo', 'mytable, 'myfield', 1, 0, 255

Feel free to use this if you find is useful, and leave a comment letting me know if it worked well for you.

-- =============================================
-- Author:        Davin Studer
-- Create date: 9/26/2010
-- Description:    This stored procedure will let you
-- know all the characters that are contained within
-- a field of a specified table.  This is handy for things
-- such as creating regular expression patterns and figuring
-- out casting issues.
-- =============================================
create procedure [dbo].[FieldCharacterSet]
    @TableSchema nvarchar(255) = '',-- this is the schema for table to look at
    @TableName nvarchar(255) = '',    -- this is the table to look at
    @FieldName nvarchar(255) = '',    -- this is the field that will be scanned
    @RTrim bit = 1,                    -- should the field be right trimmed before finding the character set ... mostly for char field types
    @LowCharRange int = 0,            -- character scan start point
    @HighCharRange int = 255        -- character scan end point
as
begin
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    set nocount on;
 
    declare
        @count bigint = 0,
        @sql nvarchar(max) = '',
        @selects nvarchar(max) = '',
        @error varchar(200) = '',
        @field nvarchar(264) = '',
        @sqlbuild nvarchar(max) = ''
    declare @chars table(
        [character] nchar(1)
    )
    declare @statements table(
        [sql] varchar(max)
    )
 
    if @TableName = ''
    begin
        set @error = 'You must supply a table name.'
        raiserror (@error, 15, 1)
    end
 
    if @FieldName = ''
    begin
        set @error = 'You must supply a field name.'
        raiserror (@error, 15, 1)
    end
    
    if @error = ''
    begin
        -- Bracket the table name to avoid naming issues
        if left(@TableName, 1) <> '[' and right(@TableName, 1) <> ']'
        begin
            set @TableName = '[' + @TableName + ']'
        end
        
        -- Bracket the field name to avoid naming issues
        if left(@FieldName, 1) <> '[' and right(@FieldName, 1) <> ']'
        begin
            set @FieldName = '[' + @FieldName + ']'
        end
        
        -- Should we rtrim the field to deal with trailing spaces ... mostly for char fields
        if @RTrim = 1
        begin
            set @field = 'rtrim(' + @FieldName + ')'
        end
        
        -- Create a temp table to hold our examples of each character matched
        if object_id('tempdb..##fieldCharacterSetTemp') is not null
        begin
            drop table ##fieldCharacterSetTemp
        end
        set @sql = 'select * into ##fieldCharacterSetTemp from (select t1.* from ' + @TableSchema + '.' + @TableName + ' t1 inner join ' + @TableSchema + '.' + @TableName + ' t2 on 1 = 1 where 1 = 0) X'
        exec(@sql)
 
        --Loop through the ascii characters low to high
        while @LowCharRange <= @HighCharRange
        begin
            --Build the from and where clauses
            set @sqlbuild = ''
            set @sqlbuild = @sqlbuild + 'from '
            if @TableSchema <> ''
            begin
                set @sqlbuild = @sqlbuild + @TableSchema + '.'
            end
            set @sqlbuild = @sqlbuild + @TableName + nchar(13)
            set @sqlbuild = @sqlbuild + 'where ' + @field
            -- If the character is A-Z or a-z then case sensitive compare
            if (@LowCharRange between 65 and 90) or (@LowCharRange between 97 and 122)
            begin
                set @sqlbuild = @sqlbuild + ' COLLATE Latin1_General_CS_AS'
            end
            set @sqlbuild = @sqlbuild + ' like '
            -- Deal with a few special SQL characters
            if @LowCharRange = 37 -- % character
            begin
                set @sqlbuild = @sqlbuild + '''%\%%'' escape ''\''' + nchar(13)
            end
            else if @LowCharRange = 39 -- ' character
            begin
                set @sqlbuild = @sqlbuild + '''%''''%''' + nchar(13)
            end
            else if @LowCharRange = 95 -- _ character
            begin
                set @sqlbuild = @sqlbuild + '''%\_%'' escape ''\''' + nchar(13)
            end
            else
            begin
                set @sqlbuild = @sqlbuild + '''%' + nchar(@LowCharRange) + '%''' + nchar(13)
            end
 
            -- Buld main counting sql
            set @sql = ''
            set @sql = @sql + 'select @countOut = count(*)' + nchar(13)
            set @sql = @sql + @sqlbuild
 
            --Get count of rows that have the character
            execute sp_executesql @sql,N'@countOut bigint = 0 output',@countOut=@count output
 
            --If any rows have this character add it to the result set
            if @count > 0
            begin
                insert into @chars values (nchar(@LowCharRange))
 
                -- Build the select sql to look at example lines that have this character
                set @selects = ''
                set @selects = @selects + 'select top 1 *' + nchar(13)
                set @selects = @selects + @sqlbuild
                
                insert into @statements values (rtrim(replace(@selects, nchar(13), ' ')))
 
                set @sql = ''
                set @sql = @sql + 'insert into ##fieldCharacterSetTemp' +nchar(13)
                set @sql = @sql + @selects
                
                exec(@sql)
            end
            
            -- Next character
            set @LowCharRange = @LowCharRange + 1
        end
 
        select * from @chars
        select * from @statements
        if object_id('tempdb..##fieldCharacterSetTemp') is not null
        begin
            select * from ##fieldCharacterSetTemp
            drop table ##fieldCharacterSetTemp
        end
    end
end

Convert CSV data into a SQL table

Recently I have been working with some report data that is stored statically in a database in CSV format. I had a need to have that CSV data displayed in a tabular format. I searched around and couldn’t find anything written to do this in SQL, so I decided to write a stored procedure to do this very thing. It will take a CSV and parse it out into a SQL table. For instance if I have report data that looks like this…

"FirstName","LastName","DOB","Children"
"John","Smith","1/5/1980","Bob,Sally,Joe,Chris"
"Jane","Smith","2/25/1982","Bob,Sally,Joe,Chris"
"Bruce","Wayne","5/13/1975",""
"Peter","Parker","5/23/1970",""
"","","",""

It would be transformed into this …

FIRSTNAMELASTNAMEDOBCHILDREN
JohnSmith1/5/1980Bob,Sally,Joe,Chris
JaneSmith2/25/1982Bob,Sally,Joe,Chris
BruceWayne5/13/1975 
PeterParker5/23/1970 
    

Note that in the last column the commas are preserved because they are within the quotation mark text qualifiers. Below is the stored procedure that does this. Feel free to use it, and leave a comment if you found it useful.

-- ==========================================================================================
-- Author:  Davin Studer
-- Create date: 4/5/2011
-- Description: This will take a CSV input
-- and transform it into a table
--
-- Params:
-- @string          - The CSV string
-- @textQualifier   - Character that denotes a string within the CSV
-- @columnDelimiter - Character that denotes different columns ... doesn't have to be a comma
-- @rowOneIsHeader  - Does the first row contain column names?
-- ==========================================================================================
create procedure [dbo].[CSVToTable]
    @string nvarchar(max) = '',
    @textQualifier varchar(1) = '',
    @columnDelimiter varchar(1) = ',',
    @rowOneIsHeader bit = 0
as
begin
    -- set nocount on added to prevent extra result sets from
    -- interfering with select statements.
    set nocount on;
 
    -- We need and input string
    if isnull(@string, '') = ''
    begin
        raiserror ('Please supply an CSV string', 15, 1)
        return
    end
 
    -- We need and column delimiter
    if isnull(@columnDelimiter, '') = ''
    begin
        raiserror ('Please supply a column delimiter.', 15, 1)
        return
    end
  
    -- Make sue the user doesn't pass null as a value
    if isnull(@textQualifier, '') = ''
    begin
        set @textQualifier = ''
    end
 
    -- Make sue the user doesn't pass null as a value
    if isnull(@rowOneIsHeader, '') = ''
    begin
        set @rowOneIsHeader = 0
    end
 
    declare
        @columns int = 1,
        @columnNames nvarchar(max) = '',
        @stop bit = 0,
        @position int = 0,
        @temp nvarchar(1) = '',
        @dataStart int = 0,
        @sql nvarchar(max) = '',
        @qualifierToggle bit = 0,
        @tempString nvarchar(max) = '',
        @delimiterReplacementUTFNumber int = 2603
 
    -- Get rid of the ##tempCSVSplitToTable table if it exists
    if object_id('tempdb..##tempCSVSplitToTable') is not null
    begin
        drop table ##tempCSVSplitToTable
    end
 
    -- Get rid of white space
    set @string = rtrim(ltrim(@string))
 
    -- Set the EOL to char(13)
    set @string = replace(@string, char(13) + char(10), char(13))
    set @string = replace(@string, char(10), char(13))
 
    -- Deal with the delimiter character within the text qualifier characters
    if @textQualifier <> ''
    begin
        while @position <> len(@string)
        begin
            set @temp = substring(@string, @position, 1)
            if @temp = @textQualifier
            begin
                if @qualifierToggle = 0
                begin
                    set @qualifierToggle = 1
                end
                else
                begin
                    set @qualifierToggle = 0
                end
            end
            if @temp = @columnDelimiter and @qualifierToggle = 1
            begin
                set @tempString = @tempString + nchar(@delimiterReplacementUTFNumber) -- replace with UTF delimiter replacement character
            end
            else
            begin
                set @tempString = @tempString + @temp
            end
            set @position = @position + 1
        end
 
        set @string = @tempString
    end
 
    -- Get rid of text qualifier ... we don't need it now
    if @textQualifier <> ''
    begin
        set @string = replace(@string, @textQualifier, '')
    end
 
    -- Get column names
    set @position = 1
    while @stop = 0
    begin
        set @temp = substring(@string, @position, 1)
        if @temp = @columnDelimiter
        begin
            set @columns = @columns + 1
            set @columnNames = @columnNames + ','
        end
        else if @temp = char(13)
        begin
            set @stop = 1
        end
        else
        begin
            set @columnNames = @columnNames + @temp
        end
        set @position = @position + 1
    end
 
    set @dataStart = @position
 
    if @rowOneIsHeader = 0
    begin
        set @dataStart = 1
        set @position = 1
        set @columnNames = ''
        while @position - 1 < @columns
        begin
            set @columnNames = @columnNames + ',Column' + cast(@position as varchar(1))
            set @position = @position + 1
        end
        set @columnNames = substring(@columnNames, 2, len(@columnNames))
    end
 
    -- Build ##tempCSVSplitToTable table
    set @sql = @sql + 'create table ##tempCSVSplitToTable (' + char(13) + '['
    set @stop = 0
    set @position = 1
    while @stop = 0
    begin
        set @temp = substring(@columnNames, @position, 1)
        if @temp <> ','
        begin
            set @sql = @sql + @temp
        end
        else
        begin
            set @sql = @sql + '] nvarchar(max),' + char(13) + '['
        end
   
        set @position = @position + 1
 
        if @position - 1 = len(@columnNames)
        begin
            set @sql = @sql + '] nvarchar(max)'
            set @stop = 1
        end
    end
    set @sql = @sql + ')' + char(13)
    exec (@sql)
 
    -- insert values into ##tempCSVSplitToTable table
    set @position = @dataStart
    while @position - 1 < len(@string)
    begin
        set @stop = 0
        set @sql = 'insert into ##tempCSVSplitToTable ([' + replace(@columnNames, ',', '],[') + ']) values ('''
        while @stop = 0
        begin
            set @temp = substring(@string, @position, 1)
 
            -- end of column
            if @temp = @columnDelimiter
            begin
                set @sql = @sql + ''','''
            end
            -- EOL
            else if @temp = char(13) or datalength(@temp) = 0
            begin
                set @stop = 1
            end
            -- deal with apostrophe in data
            else if @temp = ''''
            begin
                set @sql = @sql + ''''''
            end
            -- column data that isn't an apostrophe
            else
            begin
                set @sql = @sql + @temp
            end
 
            set @position = @position + 1
        end 
        set @sql = @sql + ''')'
 
        -- Get rid of any UTF delimiter replacements that were put in to take the place of the delimiter character within the text qualifier
        if @textQualifier <> ''
        begin
            set @sql = replace(@sql, nchar(@delimiterReplacementUTFNumber), @columnDelimiter)
        end
 
        exec(@sql)
    end
 
    select * from ##tempCSVSplitToTable
 
    -- destroy ##tempCSVSplitToTable table
    if object_id('tempdb..##tempCSVSplitToTable') is not null
    begin
        drop table ##tempCSVSplitToTable
    end
end

IE6, IE7, & IE8 On My Box … At the Same Time!

IE6IE7IE8I just installed Windows 7 on my desktop machine. Today I set up XP mode … cause I can. And the first thing I did was to create two virtual machine … one running IE6 and one running IE7. So there you have it. I can now run IE6, IE7, and IE8 on one box. The image is a screenshot of my box with the proof. This will make website debugging so much easier. Although for the most part I’ve kinda let IE6 go, but at least I can know how bad it will look.

Pivoting SQL Data

So, the other day I was trying to pivot some SQL data that was basically in this format…

RECNUMBERCOLNUMBERFIELDNAMEFIELDVALUE
11FirstNameDavin
12LastNameStuder
21FirstNameMelissa
22LastNameStuder

I wanted to see it like this …

RECNUMBERFIRSTNAMELASTNAME
1DavinStuder
2MelissaStuder

It took me a while to figure out the syntax for this, but here it is. It uses the SQL pivot keyword.

This first portion dynamically creates the column names to be used in the pivot.

declare @cols varchar(max)
 
select @cols = stuff (
    (
        select '], [' + FieldName
        from table
        group by FieldName
        order by min(ColNumber)
        for
        xml path('')
    ),
    1,
    2,
    ''
) + ']'

This next part creates a query within a string that we will run with the SQL exec command.

declare @query varchar(max)
 
set @query = '
    select RecNumber, ' + @cols + '
    from (
        select RecNumber, FieldName, FieldValue
        from table
    ) dta
    pivot
    (
        min(FieldValue)
        for FieldName in (' + @cols + ')
    ) pvt
'

All that is left to do is to execute the query that was created. I ended up putting this into a stored procedure.

exec(@query)

Google being evil … slightly?

So, I was looking at Google’s homepage in IE8 today and I noticed something funny. I was curious, so I decided to open up Google in the other major browsers to see if the funniness followed. Nope! Only in IE. Below are screenshots of the major browsers (save Google Chrome of course). See if you can spot the difference. I realize that Google can do whatever it wants with its one home page(even though Microsoft can’t do what it wants with its OS, but don’t get me started) and granted IE has its faults, but this seems a bit mean to me … especially since I was using IE8, which is the most standards compliant IE to date(meaning the features that they have implemented conform to industry standards). What do you think, is it “evil” to single out one competing browser in this way?
Firefox:
FF

Opera:
Opera

Safari:
Safari

IE8:
IE8

Clearing a float

I found out a cool way to clear a floated element the other day without having to use a clearing div/break or whatever your favorite method of clearing is. In fact you don’t have to add any extraneous html to achieve the clear. Give it a shot yourself. Here is the key …

.box {
    border: 1px solid #999999;
}
 
#header {
    width: 217px;
    margin: 0px 0px 5px 0px;
    padding: 5px;
}
 
#container {
    overflow: hidden; /*This is the magic.  It will clear the footer without needing a clearing div*/
}
 
#left {
    width: 100px;
    float: left;
    margin: 0px 0px 5px 0px;
    padding: 5px;
}
 
#right {
    width: 100px;
    float: left;
    margin: 0px 0px 5px 5px;
    padding: 5px;
}
 
#footer {
    width: 217px;
    padding: 5px;
}

The HTML

<html>
    <body>
        <div id="header" class="box">
            Header
        </div>
        <div id="container">
            <div id="left" class="box">
                Left
            </div>
            <div id ="right" class="box">
                Right
            </div>
        </div>
        <div id="footer" class="box">
            Footer
        </div>
    </body>
</html>

Chaining SQL Record Filters

So, I’ve actually been using this technique for a while, but recently on a project I have been working on I again ran into the need for it.  Many times creating an application you might find yourself with a bunch of checkboxes (or anything for that matter) that you might use to build a SQL where clause, such as …

select field1, field2, field3
from table
where
    checkbox1 = 0
    and checkbox2 = 1
    and checkbox3 = 1

The problem is that when you are building the where clause you have to check to see if you are on the first item.  If so, then do not add an “and”, otherwise add the “and”.  Or say you have a static SQL query that you often use and you want to quickly comment out the first item in the where clause but you don’t want to totally delete it.  You may have to do this …

select field1, field2, field3
from table
where
    --checkbox1 = 0
    /*and*/ checkbox2 = 1
    and checkbox3 = 1

I find this to be somewhat awkward to do.  If you are dynamically building a where clause it adds more code to have to decipher, or if you have a static query it adds unneeded edits.  I prefer the below method …

select field1, field2, field3
from table
where
    1 = 1
    and checkbox1 = 0
    and checkbox2 = 1
    and checkbox3 = 1

By adding the “1 = 1” I can simply add every piece of the where clause with the “and” in front and I don’t have check if I am on the first item or not.  Also it makes it easy to selectively comment out parts of my static query without having to do much typing as the below example shows.

select field1, field2, field3
from table
where
    1 = 1
    --and checkbox1 = 0
    and checkbox2 = 1
    --and checkbox3 = 1

For me this has been very helpful in many projects, including my current one.  Maybe somebody else will find this approach helpful as well.