Archive for category Lately Happenings

T-SQL Alphanumeric Increment Function

Posted by M J in Lately Happenings on March 19, 2010

While writing a T-SQL script I needed to be able to increment an alphanumeric number, and I wanted to keep it in T-SQL instead of going to some outside language and dealing with the mix. I searched a bit online to see if anyone had already done this, but all the solutions I found either didn’t work right, or weren’t as thorough as I would have liked. One solution tried to define the alpha portion of the string and the numeric portion and handling them as two separate strings converting the numeric portion, adding to it, and then padding it with zeros. It would work for the most part, but the example did not like the symbols I had in my string, and I did not feel like debugging someone else’s code that I was not quite fond of anyway.

So I built my own function. It increments alphabetic characters and numerical characters from a string and ignores anything else. I reverse the string and process it backwards so I can carry an increment forward to the next character processing it from right to left just like you would do with simple math on paper.

ALTER FUNCTION dbo.fn_USER_IncrementAlphaNumericString
(
@STRING VARCHAR(255)
)
/*****************************************************************
** Name : Sequential Alpha Numeric String Incrementer
**
** Description : This function will increment the given alpha
** numeric string in sequential order up to 255
** characters. (The length can be easily modified)
**
** Written By : Matt Johnson 3/19/2010
** Parameters : varchar string (255)
** Returns : varchar string (255)
**
*****************************************************************/
RETURNS varchar(255) AS
BEGIN
DECLARE @REV_STRING VARCHAR(255),
@POSITION INT,
@CURR_CHAR CHAR(1),
@CURR_CHAR_TYPE VARCHAR(20),
@NEW_CHAR CHAR(1),
@PREV_CHAR CHAR(1),
@PREV_CHAR_TYPE VARCHAR(20),
@NEW_STRING VARCHAR(255),
@INCR_CHAR BIT,
@DEBUG VARCHAR(4000),
@CR CHAR(1)

--SET INITIAL DEFAULTS FOR VARIABLES
SET @INCR_CHAR = 1
SET @POSITION = 1
SET @REV_STRING = REVERSE(@STRING)
SET @NEW_STRING = ''
SET @DEBUG = ''
SET @CR = CHAR(10)

SET @DEBUG = @DEBUG + '---- STRING: ' + @STRING + ' ----' + @CR + @CR

--LOOP THROUGH ALL CHARACTERS IN REVERSE ORDER
--REVERSE ORDER SO THAT WHEN A CHARACTER IS AT ITS
--MAX VALUE AND THAT VALUE NEEDS TO BE RESET AND THE
--INCREMENT CARRIED TO THE NEXT CHARACTER ITS SIMPLER
WHILE @POSITION <= LEN(@STRING) BEGIN

SET @DEBUG = @DEBUG + '---- POSITION: ' + CAST(@POSITION AS VARCHAR(50)) + ' --(' + CAST(@INCR_CHAR AS CHAR(1)) + ')--' + @CR

--GET THE CURRENT POSITION CHARACTER
SET @CURR_CHAR = SUBSTRING(@REV_STRING, @POSITION, 1)
SET @NEW_CHAR = @CURR_CHAR

--DETERMING CHARACTER TYPE
IF @CURR_CHAR LIKE '[0-9]' SET @CURR_CHAR_TYPE = 'NUMERIC'
ELSE IF @CURR_CHAR LIKE '[A-Z]' AND ASCII(@CURR_CHAR) >= 97 AND ASCII(@CURR_CHAR) <= 122 SET @CURR_CHAR_TYPE = 'LOWER ALPHA'
ELSE IF @CURR_CHAR LIKE '[A-Z]' AND ASCII(@CURR_CHAR) >= 65 AND ASCII(@CURR_CHAR) <= 90 SET @CURR_CHAR_TYPE = 'UPPER ALPHA'
ELSE SET @CURR_CHAR_TYPE = 'SYMBOL'

SET @DEBUG = @DEBUG + 'CURRENT_CHAR: ' + @CURR_CHAR + @CR
SET @DEBUG = @DEBUG + 'CURRENT_CHAR_TYPE: ' + @CURR_CHAR_TYPE + @CR

--CHECK INCREMENT BIT TO SEE IF WE NEED TO INCREMENT THIS CHARACTER
IF @INCR_CHAR = 1 BEGIN
--INCREMENT IS NEEDED

IF @CURR_CHAR_TYPE = 'NUMERIC' BEGIN
--PERFORM NUMERICAL INCREMENT TO THIS CHARACTER
IF @CURR_CHAR = '9' BEGIN
--CHARACTER IS AT ITS MAX SEQUENCABLE VALUE
--RESET THE SEQUENCE AND CARRY THE INCREMENT
--TO THE NEXT CHARACTER
SET @NEW_CHAR = '0'
END ELSE BEGIN
--CONVERT THE CHARACTER TO A NUMBER AND ADD 1 THEN CONVERT BACK TO A CHARACTER
SET @NEW_CHAR = CAST(CAST(@CURR_CHAR AS INT) + 1 AS CHAR(1))
--INCREMENT BIT IS SET TO 0 BECAUSE INCREMENT WAS COMPLETED
SET @INCR_CHAR = 0
END
--ADD NEW CHARACTER TO STRING BUILT IN REVERSE ORDER SO THAT
--THE FINAL RECONSTRUCTION IS IN THE ORIGINAL ORDER
SET @NEW_STRING = @NEW_CHAR + @NEW_STRING
END

ELSE IF @CURR_CHAR_TYPE = 'LOWER ALPHA' BEGIN
--PERFORM ALPHABETIC INCREMENT TO THIS CHARACTER
IF @CURR_CHAR = 'z' BEGIN
--CHARACTER IS AT ITS MAX SEQUENCABLE VALUE
--RESET THE SEQUENCE AND CARRY THE INCREMENT
--TO THE NEXT CHARACTER
SET @NEW_CHAR = 'a'
END ELSE BEGIN
--CONVERT THE CHARACTER TO AN ASCII NUMBER AND ADD 1 THEN CONVERT BACK TO A CHARACTER
SET @NEW_CHAR = CHAR(ASCII(@CURR_CHAR) + 1)
--INCREMENT BIT IS SET TO 0 BECAUSE INCREMENT WAS COMPLETED
SET @INCR_CHAR = 0
END
--ADD NEW CHARACTER TO STRING BUILT IN REVERSE ORDER SO THAT
--THE FINAL RECONSTRUCTION IS IN THE ORIGINAL ORDER
SET @NEW_STRING = @NEW_CHAR + @NEW_STRING
END

ELSE IF @CURR_CHAR_TYPE = 'UPPER ALPHA' BEGIN
--PERFORM ALPHABETIC INCREMENT TO THIS CHARACTER
IF @CURR_CHAR = 'Z' BEGIN
--CHARACTER IS AT ITS MAX SEQUENCABLE VALUE
--RESET THE SEQUENCE AND CARRY THE INCREMENT
--TO THE NEXT CHARACTER
SET @NEW_CHAR = 'A'
END ELSE BEGIN
--CONVERT THE CHARACTER TO AN ASCII NUMBER AND ADD 1 THEN CONVERT BACK TO A CHARACTER
SET @NEW_CHAR = CHAR(ASCII(@CURR_CHAR) + 1)
--INCREMENT BIT IS SET TO 0 BECAUSE INCREMENT WAS COMPLETED
SET @INCR_CHAR = 0
END
--ADD NEW CHARACTER TO STRING BUILT IN REVERSE ORDER SO THAT
--THE FINAL RECONSTRUCTION IS IN THE ORIGINAL ORDER
SET @NEW_STRING = @NEW_CHAR + @NEW_STRING
END

ELSE BEGIN
--IF INCREMENT IS NEEDED BUT CHARACTER TYPE IS NOT
--ABLE TO BE INCREMENTED SUCH AS A SYMBOL THEN WE
--WILL PASS ON THE CURRENT CHARCTER AND THE NEXT
--CHARACTER WILL ATTEMPT TO GET INCREMENTED (CARRY THE INCREMENT)
SET @NEW_STRING = @CURR_CHAR + @NEW_STRING
END
END ELSE BEGIN
--NO INCREMENT WAS NEEDED SO THE CURRENT CHARACTER IS PASSED ON
SET @NEW_STRING = @CURR_CHAR + @NEW_STRING
END

SET @DEBUG = @DEBUG + 'NEW_CHAR: ' + @NEW_CHAR + @CR
SET @DEBUG = @DEBUG + 'NEW_STRING: ' + @NEW_STRING + @CR
SET @DEBUG = @DEBUG + 'POST_INCR INCR_CHAR: ' + CAST(@INCR_CHAR AS CHAR(1)) + @CR

--ADD ADDITIONAL CHARACTER IF NEEDED
IF @POSITION = LEN(@STRING) AND @INCR_CHAR = 1 BEGIN
--WE ARE AT THE LAST POSITION AND AN INCREMENT STILL NEEDS TO BE
--PERFORMED, SO WE MUST ADD AN ADDITIONAL CHARACTER TO THE STRING SINCE
--ALL OTHER POSITIONS WERE NOT ABLE TO BE INCREMENTED OR WERE AT THEIR
--MAXIMUM SEQUENCE
IF @CURR_CHAR_TYPE = 'NUMERIC' SET @NEW_STRING = '1' + @NEW_STRING
ELSE IF @CURR_CHAR_TYPE = 'LOWER ALPHA' SET @NEW_STRING = 'a' + @NEW_STRING
ELSE IF @CURR_CHAR_TYPE = 'UPPER ALPHA' SET @NEW_STRING = 'A' + @NEW_STRING
ELSE SET @NEW_STRING = 'a' + @NEW_STRING

--IF THE NUMERIC OR ALPHA CHARACTER TYPES ARE NOT MATCHED WE DEFAULT TO
--ADDING A LOWER CASE ALPHA CHARACTER
END

SET @DEBUG = @DEBUG + 'POST_ADD NEW_STRING: ' + @NEW_STRING + @CR

--SET THE PREVIOUS CHARACTER AND TYPE FOR THE NEXT ITERATION OF THE LOOP
SET @PREV_CHAR = @CURR_CHAR
SET @PREV_CHAR_TYPE = @CURR_CHAR_TYPE

--INCREMENT THE POSITION SO THE NEXT ITERATION MOVES TO THE NEXT CHARACTER
SET @POSITION = @POSITION + 1
END

RETURN @NEW_STRING
END
GO

/* TESTING FUNCTION */
SELECT dbo.fn_USER_IncrementAlphaNumericString('A') [A],
dbo.fn_USER_IncrementAlphaNumericString('B') [B],
dbo.fn_USER_IncrementAlphaNumericString('0') [0],
dbo.fn_USER_IncrementAlphaNumericString('1') [1],
dbo.fn_USER_IncrementAlphaNumericString('9') [9],
dbo.fn_USER_IncrementAlphaNumericString('Z') [Z],
dbo.fn_USER_IncrementAlphaNumericString('a') [a],
dbo.fn_USER_IncrementAlphaNumericString('z') [z],
dbo.fn_USER_IncrementAlphaNumericString('99') [99],
dbo.fn_USER_IncrementAlphaNumericString('97') [97],
dbo.fn_USER_IncrementAlphaNumericString('278') [278],
dbo.fn_USER_IncrementAlphaNumericString('123') [123],
dbo.fn_USER_IncrementAlphaNumericString('A-9') [A-9],
dbo.fn_USER_IncrementAlphaNumericString('A9') [A9],
dbo.fn_USER_IncrementAlphaNumericString('-Zz9') [A9],
dbo.fn_USER_IncrementAlphaNumericString('z-2Z13') [z-2Z13],
dbo.fn_USER_IncrementAlphaNumericString('Aa-9999999') [Aa-9999999],
dbo.fn_USER_IncrementAlphaNumericString('Aa-99ZS999-') [Aa-99ZS999-]

No Comments

SVN Hoops

Posted by M J in Lately Happenings on October 5, 2009

A little over a week ago I was working on a project updating some code, and ran into an issue once it came time to commit my code changes to the SVN repository that I had been using. Well after messing with authentication errors for a while and scratching my head I realized that the repository URL had changed from an HTTPS:// address to an HTTP:// address. It ended up being because the account holder for the repository had changed their account plan and downgraded their account to the point where SSL support was not included in their plan. That was great for them, but that meant I was having problems updating the code. I did a little digging and found a solution.

You can change the URL of an SVN repository and relocate it to a different url using the SVN command line tools.

svn switch --relocate  <from URL> <to URL>

That was great once I found out how to change the URL, but then when it came time to use my fancy little GUI SVN client I got rejected again because my command line SVN client just so happened to be running a newer version of SVN (1.6) and the fancy little GUI (Cornerstone) only supported up to v1.5 and refused to work with my working copy since it had been automatically upgraded to v1.6 by my command line client when I did the relocate. So then I got to dig a little bit more.

I found some references to being able to downgrade a working copy of a repository from version 1.5 to version 1.4 from when v1.5 was introduced. It referenced a little Python script tool (change-svn-wc-format.py) that could be used in downgrading a working copy. There was a little bit more information about the tool in a FAQ. I ended up downloading the script and using it to convert the repository from v1.6 to v1.5 and then my SVN GUI client was able to function again and I was able to commit my changes.

Now, about a week later (today) I go in and make a tiny 2 line code change and go to commit it to the same repository… only to find out that it has changed yet again. Apparently the account holder upgraded their account back to where SSL support was provided, and that ended up breaking connecting without SSL over HTTP. I recognized the problem a little quicker this time, and knew what needed to be done, so I found out how to relocate the SVN working copy, and thankfully last week Cornerstone got an upgrade that now supports v1.6 so I didn’t have to go through another downgrade in order to get my commit back up. Maybe now it won’t change again, or maybe I’ll just get better at learning how to adapt when the powers that be make decisions that make my life more difficult.

1 Comment

Party Menu

Posted by M J in Lately Happenings on September 14, 2009

We had a party over at my house this weekend for my sister-in-law and I had a bit of fun throwing together a nice little menu of the party snacks we had planned while the girls came over to watch several movies all afternoon and evening. It had been a little while since I had a chance to design anything, so it was nice and refreshing to pick something up and turn out a nice little menu in a couple hours.

view party menu

No Comments

Local Development

Posted by M J in Lately Happenings on July 21, 2009

I ended up running into a problem with the requirements that Magento Enterprise Edition had on my development machine. It required mhash, and it wasn’t built into MAMP Pro. The company behind MAMP hasn’t released a new version for about a year now, so it appears they aren’t developing it anymore. I did a little digging, and everything I found involved recompiling PHP, and I really didn’t want to mess with that, so I looked into some other packages that would already have it included. It looks like XAMP and ZendServer were my best options, and I decided to go with ZendServer and see how it worked since I had heard of Magento running on ZendServer, and since I’m using Zend Studio for my IDE, I figured why not.

The beauty of using MAMP Pro was that it had a simple GUI interface that allowed me to quickly add new sites for development, restart the services, and since it ran as an application it was simple to only have running when I needed it so it wasn’t taking up additional resources on my system at other times. Basically MAMP Pro saved me a lot of time when it came to setting up a new site as I just typed in a domain name, and browsed for where I wanted the web root path, then hit a button and restarted it all.

The install of ZendServer went pretty smooth, but once it was installed I found a lot of configuration issues that I needed to change in order to use ZendServer the way I was using MAMP Pro for simple and quick development sites running locally. After searching all over the place trying to find an answer for one thing, and then researching another I decided to document everything I went through and posted it up on the Zend Forum. (Magento EE Dev Environment Setup)

Here is what I did to set it up….
Login to ZendServer admin and set password
Login to phpmyadmin and set root password

change username and group in /usr/local/zend/apache2/conf/httpd.conf

CODE:
User myusername
Group mygroupname

I know I had MAMP Pro setup to run under my username, and I think this had to do with permissions when I go and modify all the files it made it easier to work with if everything was running under the same username. I imagine there are some security implications to this, but since my local network is firewalled, and I don’t leave ZendServer running when I’m out of the office, I don’t think I’m at much risk here.

uncomment virtual hosts include in /usr/local/zend/apache2/conf/httpd.conf

CODE:
# Virtual hosts
Include conf/extra/httpd-vhosts.conf

I wanted to be able to run multiple sites from my system using virtual hosts like I have in MAMP Pro

change port and virtual hosts in /usr/local/zend/apache2/conf/extra/

CODE:
NameVirtualHost *:80

<VirtualHost *:80>
DocumentRoot "/Users/myusername/Sites/mage1"
ServerName mage1.local

<Directory "/Users/myusername/Sites/mage1">
Options Includes FollowSymLinks
AllowOverride All
Order allow,deny
Allow from all
</Directory>

ErrorLog "logs/mage1.local-error_log"
CustomLog "logs/mage1.local-access_log" common
</VirtualHost>

<VirtualHost *:80>
ServerName mage2.local
DocumentRoot "/Users/myusername/Sites/dhstyles"

<Directory "/Users/myusername/Sites/dhstyles">
Options Includes FollowSymLinks
AllowOverride All
Order allow,deny
Allow from all
</Directory>

ErrorLog "logs/mage2.local-error_log"
CustomLog "logs/mage2.local-access_log" common
</VirtualHost>

The directory references were in my MAMP Pro configuration so I copied them over into here. I also set the directory path to be in the user space so it’s easier to manage my site’s code with Zend Studio and other applications.

comment the skip-networking in /usr/local/zend/mysql/data/my.cnf

CODE:
#skip-networking

For some reason network connectivity is disabled by default, and you have to uncomment this line in order to enable it again. This will allow Magento and other database tools such as Navicat to connect to the MySQL installation.

edit /etc/hosts file for DNS resolution

CODE:
127.0.0.1 mage1.local
127.0.0.1 mage2.local

Adding entries to the hosts file will allow you to do DNS resolution in the browser and allow the named virtual hosts to work properly.

restart

CODE:
zendctl.sh restart

No Comments

Bing

Posted by M J in Lately Happenings on June 2, 2009

When you drop a heavy rock in an empty pale you’d probably describe the sound as a bang, but if you drop a penny in an empty pale the sound may be more appropriately described as a bing.

Microsoft decided to rebrand their failed attempts at a search engine this week, calling their new service Bing (www.bing.com), rather than Live Search. I tried it out and kicked the tires a bit and it was decent, I was impressed by the ground Microsoft made in providing a better search experience then they had provided in the past, but I still think they are a ways off from where Google is now, and that they are still playing catch-up. I did however think the image search was nice with the dynamically loaded AJAX page that you can scroll through until it’s loaded all the results instead of having to page through an endless pile of pages.

I still remember the days before Google was around, and the mess that had to be waded through to get any kind of results from a search engine, and Google saved the day. They were the hero then, and I think they have gained the loyalty both with having come to the rescue, and by staying ahead of the crowd. Google has lead inovation in how we find things online. Microsoft has been for a while just playing catchup to the inovation on the web. Microsoft offers just about the same feature set as Google in a lot of ways, but I prefer the experience and simplicity that Google worked hard to maintain. I feel that Microsoft still tries to do too much and thus doesn’t provide as polished a solution.

2 Comments

Photo Management

Posted by M J in Lately Happenings on March 30, 2009

I purchased downloaded and installed Lightroom last weekend to manage some of my photos and provide a quick way to organize and quickly correct photos. Currently the process flow I’m thinking of using is to bring photos I shoot directly into Lightroom first, then go through and remove the images I don’t think should be there, then export a low res copy of the photos to iPhoto ’09 and export the full res photos to one of my two SmugMug sites.

I like the idea of using Lightroom to quickly browse through and correct photos, and then get them up to SmugMug after any corrections. The problem is I’d rather use iPhoto for basic viewing of the images on the laptop or to show them to someone. I also like the concept of the Faces and Places in iPhoto though I may not use them if I can’t figure out a way to get the data back into the Lightroom catalog or the original raw images. From iPhoto I like the Facebook uploading and synchronizing. I don’t want to store a full res copy of all my images in iPhoto because my laptop’s hard drive has limited space, but if I can keep a copy of all my images at a low res without it taking up too much space that would be ideal, because then I can browse and present the images wherever I’m at quickly and it doesn’t take up as much of a chunk of storage as my raw images. I like the non-destructive editing in Lightroom and the preservation of the raw images. The other thing I’m trying to accomplish is that I want to store the original raw images on my home server, but I will likely start out with them on my laptop first then migrate them to the server later. I plan on using Lightroom on my laptop make all my changes, then offload the images to the server later. I discovered I can use Lightroom catalogs in an offline fashion if it has pre-built the preview data for images, but I can’t make edits to the images unless the catalog can see the original images. I’m not sure exactly how I’m going to manage this yet. I may end up having all the catalogs both on my laptop and on the server, and then just needing to sync them up whenever I update them on my laptop or something.

My biggest challenge here is keeping everything in sync. I’m likely going to shoot then dump the images to Lightroom on my laptop, or my wife’s laptop, then once home or via some other means transfer the originals and catalog to my home server for permanent  storage where I have mirrored hard drives. At some point I will want to export images from Lightroom into iPhoto and up to SmugMug. Then once they are in iPhoto I need to get them up to Facebook or something like that. If I’m moving images around between computers, and moving Lightroom catalogs around this gets a bit tricky, adding to that the time it takes to upload full res images to SmugMug makes it that much more complicated. Then add to that anytime something is updated on Facebook, iPhoto, Smugmug or Lightroom and then getting that information synced back up everywhere, this is a big hurdle.

So my challenge thus far is to figure out how to make this happen and happen in a seamless fashion so I don’t spend all my time just trying to mange my photos, so that I can spend the time I have enjoying my photos.

No Comments

iPhone and Touch Pets

Posted by M J in Lately Happenings on March 30, 2009

The other day while I was watching a video for the developer preview of the iPhone OS 3 slated to come out this summer, they had a game demo for Touch Pets Dogs by ngmoco:) and Kathryn saw me watching the video and fell in love wth the demo of the game. She cried for several minutes wanting to see more of the dogs, so I’m sure once I get an iPhone I’ll get a cute little game to entertain my little girl.

I’m somewhat waiting on the 3G coverage in our area, and somewhat on a new update to the iPhone this summer. If I do get one, the plan is to get one for Ashley and one for myself, that I don’t have to share my phone.

No Comments

Yes Yes I Know

Posted by M J in Lately Happenings on March 30, 2009

A while back Ashley wrote up a bunch of pages that I was supposed to review before we actually listed them. I still need to look over them and then remove the passwords on the pages.

I just recently got WordPress updated to the latest version, and am now working on getting more things updated here.

1 Comment

Lately Happenings

Posted by M J in Lately Happenings on March 22, 2009

I purchased iPhoto ’09 for my MacBook Pro and have started updating Facebook more regularly with photos.

No Comments