dirq.net

Web development tips with a side of sarcasm

June 1, 2014
by Dirk Watkins
0 comments

Automation with Webstorm File Watchers

Automatically update the cache.manifest file whenever you edit other project files.

HTML5 sites can run offline by using Application Cache. If you haven’t used this before here’s a good introduction to AppCache.  The main point you’ll need to understand for this post is that there is a manifest file that lists out the resources to cache for offline use.  The browser checks the manifest before getting any file updates so it’s necessary to modify the manifest if you change your site.  This is a tedious chore.  And as any good, lazy developer, I wanted to automate this process.  The below Bash script could be used to modify the file in any tool, but I’ve been working in Webstorm 8 on a Mac recently, so that’s what’s in this example.

Using: Webstorm 8 on a Mac (OSX, Mavericks) and the Bash Shell

Here’s the full script, there’s a download at the end of the post. The file is named updateCacheManifestTimestamp.sh. It’s not very long but it’s got a bunch of stuff going on.

#!/bin/bash
# open cache.manifest and replace line 2 with the output of DATE as a manifest comment
# save that output to a temp file
# rename the temp file back to cache.manifest overwriting the original
sed -e "2s|.*|# `date`|g" "cache.manifest" > "cache.manifest.tmp" && mv cache.manifest.tmp cache.manifest

First off, it uses sed to update a file.  Sed is kind of a complicated thing to call so let’s break it down.

-e appends the lists of commands

"2s|.*|# `date`|g" does the work. The first part, 2s|, says that on the second line do a substitute, also set the delimiter to a pipe (|). This is important. The pipe will separate the commands that sed will use.

.* is a regular expression and says replace the whole line.

# `date` says insert a hash (#), a space, and then the output of the date command. The backtick (`) characters around date are how we get bash to output date into our replacement string for sed.  It’s important that these are backticks and not single quotes.  If you’re like me and you haven’t used this before, it’s under the tilde (~) on your keyboard.

The next few commands are simpler. They say to open cache.manfest in sed and send the output to cache.manifest.tmp. The && say to then run the next command. mv moves the temp file to a new name, effectively renaming it back to cache.manifest.

Clear as mud? Great!  Let’s move on…

Running it with Bash

Open up your terminal on the Mac or in Webstorm and cd into the directory that holds updateCacheManifestTimestamp.sh. Type this and hit enter:

bash updateCacheManifestTimestamp.sh

Using it as a Webstorm File Watcher

File Watchers in Webstorm are used to watch for changes in files. They’re used to compile SASS or to minify JS.  You can also use them to call your own things and that’s what we’re going to do.

To create a File Watcher, open the Preferences window with CMD-,. It’s also in Webstorm > Preferences. And search the settings for “watcher” or just scroll down until you see File Watchers. Click the + under the list of File Watchers (CMD-N) and use the custom template.

Fill in the fields to match the screenshot:

Update cache.manifest with the current date when an open file is saved.

Update cache.manifest with the current date when an open file is saved.
(Note: my project has an app folder that contains my cache.manifest file and the bash script)

File Type:Any
Scope:Open Files
Program:bash
Arguments:$ProjectFileDir$/app/updateCacheManifestTimestamp.sh
Working Directory:$ProjectFileDir$/app
Output Paths to Refresh:$ProjectFileDir$/app/cache.manifest

After hitting OK, you can edit any file and the cache.manifest will automagically update.  Upload it to the server and your users will be able to get the latest version of your app.  By the way, and this is the way AppCache workds, they’ll need to open their app TWICE. Yes. Twice. The first time to get the latest version in the background and the second time to actually use it.  Not too bad a price to have an offline app.

Download

download the example bash script

May 29, 2014
by Dirk Watkins
0 comments

Check for Objects in SQL Server

Here are some examples of how to check for existing objects in SQL Server.  Use them before you drop/create/modify to avoid errors.

Object Type Existence Checks:

Column Check

Using INFORMATION_SCHEMA.COLUMNS

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
	WHERE
	TABLE_SCHEMA = 'dbo'
	AND TABLE_NAME = 'MyTable'
	AND COLUMN_NAME = 'MyColumn')
BEGIN
	PRINT 'Altering MyTable to include the MyColumn';
	ALTER TABLE dbo.MyTable ADD MyColumn INT NULL;
END
GO

Using COL_LENGTH

IF COL_LENGTH('MyTable', 'MyColumnName') IS NOT NULL
BEGIN
	PRINT 'Dropping MyColumnName column on MyTable.';
	ALTER TABLE MyTable DROP COLUMN MyColumnName;
END
GO

Column Data Type Check

Check if a column has a datatype of varchar(200), if not, change it

IF NOT EXISTS( SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'VARCHAR'
AND CHARACTER_MAXIMUM_LENGTH = 200
AND TABLE_NAME = 'MyTable'
AND COLUMN_NAME = 'MyColumn')
BEGIN
	PRINT 'Changing datatype of MyTable.MyColumn to vachar(200)';
	ALTER TABLE MyTable
	ALTER COLUMN MyColumn NVARCHAR(200) NULL;
END

Column Default Value Check

IF NOT EXISTS(
	SELECT *
		FROM sys.all_columns c
		JOIN sys.tables t
			ON t.object_id = c.object_id
		JOIN sys.schemas s
			ON s.schema_id = t.schema_id
		JOIN sys.default_constraints d
			ON c.default_object_id = d.object_id
	WHERE
		s.name = 'MySchema'
		AND t.name = 'MyTable'
		AND c.name = 'MyColumn')
BEGIN
	ALTER TABLE [MyTable]
	ADD CONSTRAINT [MyConstraint]
	DEFAULT ((0)) FOR [MyColumn];
END
GO

Foreign Key Check

IF OBJECT_ID('FK_MyForeignKey', 'F') IS NOT NULL
BEGIN
	PRINT 'Removing old foreign key reference';
	ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [FK_MyForeignKey];
END
GO

Function (Scalar) Check

IF OBJECT_ID ('MyFunction'. 'FN') IS NOT NULL
BEGIN
	PRINT 'Dropping MyFunction';
	DROP FUNCTION MyFunction;
END
GO

Index Check

IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'idx_MyIndex')
BEGIN
	PRINT 'Dropping idx_MyIndex to recreate it.'
	DROP INDEX idx_MyIndex ON dbo.MyTable;
END
GO
PRINT 'Creating idx_MyIndex';
GO
CREATE NONCLUSTERED INDEX idx_MyIndex
	ON [dbo].[MyTable] ([myRelatedCol1],[myRelatedCol2],[myRelatedCol3])
	INCLUDE ([myRelatedCol4])
GO

Linked Server Check

IF NOT EXISTS(
	SELECT * FROM sys.servers
	WHERE name = N'MyLinkedServerAliasName')
BEGIN
	--do something
END

Primary Key Check

--check for PK on a schema'd table
IF EXISTS (
	SELECT * FROM sys.key_constraints
	WHERE type = 'PK'
	AND OBJECT_NAME(parent_OBJECT_ID) = 'MyTable'
	AND SCHEMA_NAME([schema_id]) = 'MySchema'
)
BEGIN
	PRINT 'Dropping primary key from MySchema.MyTable';
	ALTER TABLE [MySchema].[MyTable] DROP CONSTRAINT PK_MyPrimaryKey
END
GO

Schema Check

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'MySchema')
BEGIN
	CREATE SCHEMA [MySchema] AUTHORIZATION [dbo];
END

Stored Procedure Check

IF OBJECT_ID(N'MyProcedure', 'P') IS NOT NULL
BEGIN
	PRINT 'Dropping MyProcedure';
	DROP PROCEDURE MyProcedure;
END
GO

Table Check

IF OBJECT_ID(N'dbo.MyTable', 'U') IS NOT NULL
BEGIN
	PRINT N'Dropping dbo.MyTable';
	DROP TABLE dbo.MyTable;
END
GO

User (Database) Check

USE MyDatabase;

IF EXISTS(
	SELECT *
	FROM sys.database_principals
	WHERE name = 'MyUsername')
BEGIN
	--do someting
END

You can also link the database user up with the server login like so:

USE MyDatabase;

SELECT
	sp.name AS ServerLoginName,
	dp.name AS DBUserName
FROM
	sys.server_principals sp
	LEFT JOIN
	sys.database_principals dp ON sp.sid = dp.sid
WHERE
	sp.name = 'MyLogin';

View Check

IF OBJECT_ID(N'MyView', 'V') IS NOT NULL
BEGIN
	PRINT 'Dropping MyView. Its no longer needed.';
	DROP VIEW [dbo].[MyView];
END
GO

Current Executing Database

IF (db_name() = 'MyDatabase')
BEGIN
	PRINT 'We are executing this call against MyDatabase';
END
GO

March 10, 2014
by Dirk Watkins
0 comments

Test Your App on Slow Connections

The world is not on broadband.  A lot of people are still on dialup, or on cell phone connections.  Make sure you develop your web site and configure your servers for people on slow connections.

A great way to determine how your site behaves on slow connections is to test.  If you’re on a Mac you can do this with Apple’s Network Link Conditioner, part of Hardware IO Tools.  The download is behind a login so I’ll explain that later.

What it Does

The Network Link Conditioner will limit your bandwidth in and out of your computer.  Even to localhost. There’s a couple presets that you can play with, like wifi, Edge and 3G cell service, etc.

Network Link Conditioner

How to Get It

  1. Visit the Download section of Apple’s Developer Center
  2. Search for Network Link Conditioner  or Hardware IO Tools for Xcode
  3. Download Hardware IO Tools for Xcode (latest as of this writing was October 2013)
  4. In the DMG package is the Network Link Conditioner.
    network link conditioner install
  5. Double click the Network Link Conditioner.prefPane and it will install it into your System Preferences.

You’ll then be able to go into Apple > System Preferences and see a shortcut for the tool.

What to Do

Now that you know how your site behaves on slow connections, what should you do? Well, there’s a ton.  Here’s a shortlist:

  • Minify and bundle your javascript and css files.
  • Gzip compress the files being transferred from your web server.
  • Use sprite sheets to bundle images.
  • Load only what you need.

Oh, and Remember..

You’ll need to turn off the Network Link Conditioner when you’re done.  It’s limiting ALL traffic going in and out of your computer.

Happy programming.

Guinness has a thick head

November 23, 2013
by Dirk Watkins
0 comments

How to pour Guinness from a can

Here’s the secret to pouring Guinness from a can into a pint glass.  Open the can, quickly jam it in the pint upside down, and slowly it pull out.  That’s it.

And if that isn’t clear enough, I made a little video for you.

It even “stands up” to the pencil test.

20131123-162359.jpg

November 11, 2013
by Dirk Watkins
0 comments

My Subversion Backup Script

In the name of all that’s good – backup your Subversion repository!

Your repo is the history of the code that runs your business.  Make sure you back that beast up.  You can safely back it up while developers are using it by running the dump command.

Take a dump

I like to script my dumps (backups) with a Windows batch script and then compress the output for long term storage. Here’s my batch script:

Requires:

@ECHO OFF
cd e:

set ts=%date:~10,4%%date:~4,2%%date:~7,2%
ECHO %ts%
set repoPath=E:\Repositories\Development
set tempPath=C:\SvnBackupStagingArea\DevRepoDump%ts%.svn
set backupPath=\\networkShareForBackup\DevRepoDump%ts%.svn.7z

ECHO Dumping SVN Repo 
svnadmin dump %repoPath% > %tempPath%

ECHO Zipping
cd "c:\Program Files\7-Zip"

set tempPathZip=%tempPath%.7z

7z a %tempPathZip% %tempPath%

ECHO Moving to network share
move %tempPathZip% %backupPath%

ECHO Cleaning up
del %tempPath%

ECHO Done.
ECHO Presented for you in it's smallest form, your backup: 
ECHO %backupPath%
pause

The script creates a nice timestamp (ts) to append to the backup filename – it’ll end up to be something like DevRepoDemp20131111.svn.7z when it’s done. This depends on how your dates are set up in Windows so you might need to change this or remove it.

It then sets the temporary staging path and the final zipped up dump path for where you want the result to end up. The server that has the repository should be where you stage the backup file, so it can backup and zip as fast as possible. Make sure you have at least 1.5 times more free space available than the size of the repository – it’ll need to create a dump and then zip that dump.

SVNADMIN DUMP creates a hot copy of the repository. This works even if developers are using the repo while you are backing it up – Those particular changes probably won’t be in this backup though. That’s why you should run this on a schedule, preferably at least once a day that people are not using the repo, like around lunch time :)

Note: If you are using TortoiseSVN, make sure you have also installed the command line tools with it. Check your machine for svnadmin.exe – if you do not have it, re-install TortoiseSVN and include the tools.

7-Zip will compress the backup copy. And yes, this makes a big difference in the resulting file size. Our repo was 10 gig and it zipped down to 3.5g.

It’ll then move the zip to a network share, deleting the temp file.

Download the zipped batch file

Let me know if this helped you out.