Adding Prototype methods to jQuery

Posted on December 7, 2010 in: Javascript

We recently upgraded a project which was using the Prototype JavaScript framework to use jQuery and ran into a few methods which didn’t exist in jQuery at all.

A quick search gave us an easy solution; by adding a prototype property (not related to the Prototype framework) to String.prototype we can give every `string` in our codebase access to these methods as if they were part of the JavaScript spec (or the jQuery library).

E.g. we wanted to add the endsWith() and startsWith() functions on any String object which basically checks a string for a suffix or prefix:

// Allows jQuery to use '.startsWith' and '.endsWith' which are in the Prototype framework
function utilityInit()
{
	String.prototype.endsWith = function (suffix) {
	  return (this.substr(this.length - suffix.length) === suffix);
	}

	String.prototype.startsWith = function(prefix) {
	  return (this.substr(0, prefix.length) === prefix);
	}
}

Note that I wrapped these additions in a function called utilityInit() which I run on document ready…so I can access them anytime I need.

1 Comment

Importing a csv into MySQL using the command line

Posted on November 14, 2010 in: Databases, Mac

UPDATE 16th Nov 2010: I had some issues with the import which I noted in the first code example below

Today I had to import csv into a single mysql table. The destination table only had 5 columns and the csv was around 22MB (around 400,000 records). The csv also contained more columns that I needed for the import. Sometimes I’ll use a GUI to do this as I usually have one open, but it never ceases to amaze me how much slower GUI tools are compared to the command line.

To import the file via Aqua Data Studio took around 20 mins to import on my machine (over the network to the staging server took over 40mins!). As I need to do this more and more I though I’d look at a few command line options. I actually wanted to do this in 2 steps; firstly import the csv, them export it as a sql file and import it into “production” from there.

First we need to import the original .csv, ignoring certrain columns which the destination table didn’t need. Why not open this in excel/open office and `clean` the csv first? Too many rows, by default open office won’t read that many rows, plus it slows the machine down to even try that, double plus it’s super simple to ignore columns you don’t need:

mysql> LOAD DATA LOCAL INFILE '/pathtofile/myfile.csv'
    -> INTO TABLE MyTable
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n'
    -> (col1,col2,@ignore,col3,col4,@ignore,col5);

Note the LINES TERMINATED BY…you really need ‘\r\n’, especially if you .csv had been generated from a Windows machine! Otherwise your import will be kinda funky :(

Also note the ENCLOSED BY ‘”‘, if you skip that option then any data in the .csv what was enclosed by double quotes (strings for eg) will actually import the double quotes into your database which is more than likely not what you want.

Note the `@ignore` user variables specified in the column list. Basically that’s saying that there are extra columns in the csv which I don’t want to import into the database. So by assigning the csv column to a user variable and not assigning the variable to an actual table column you effectively ignore it. You can call the user variable anything you want (prefixed with `@` of course). Calling it `@ignore` made sense to me.

Great now we have our data in the table, took about 2 seconds to run as opposed to 20min in the GUI tool :/

Then I had to quickly massage the data via sql as defined by the business rules as the original csv was `incomplete` in terms of the application requirements. After that was done I exported the table into a sql file:

mysqldump -uroot -p [local_database_name] [table_to_export] --skip-opt --compact --disable-keys --extended-insert --no-create-info > mytable.sql

The options used here skip things in the .sql file like CREATE TABLE etc. They also combine INSERTS and disable the keys to improve the speed of the import. Finally import the production ready sql into:

mysql -uroot -p [production_database] < mytable.sql

Again, the point of this is pure performance. The speed difference is incredible, even on a local machine I don’t know why anyone would bother using GUI tools to import/export data unless you were working with tiny databases/tables.

3 Comments

Case sensitive development on Mac OS X

Posted on October 4, 2010 in: Databases, Mac

At work most of the developers are on Mac OS X but our staging and production environments are Linux based. This means development is case insensitive but stage/production is case sensitive. Although we have development guidelines/standards which dictate file and database naming conventions etc, human error can still occur (in terms of referencing files with a different “case”). We also use a few open source packages where we obviously don’t have control over the naming of files or the references to database tables within those files.

Why is this an issue?

Linux is a case sensitive environment, so calling myClass.cfc when the actual name of the file is MyClass.cfc will fail on Linux, but will be ok during development as Mac OS X isn’t case sensitive by default. The same would happen for MySQL table references in the codebase as tables are stored as files on the file system.

This has been an ongoing issue for us over the years particularly with open source libraries we may be using. So last week I decided to change my working environment to avoid finding these issues only once code had been deployed to staging. Overall the process was simple and really only took an hour or so (and most of that was waiting for files to copy across to the new location).

What I did was to create a case sensitive partition on the Mac and move my “Workspace” (project folders and files) and “MySQL” install to the new partition. Now any case sensitive issues will be found during development which is much preferable to finding them in staging/production :)

Setting up the new partition was done using iPartition but you could just as easily use the Disk Utility. I setup a new 50GB jhfsx partition (HFS Journaled case sensitive). Once that was done I copied my workspace folder across, then the MySQL folder. Finally, create symlinks for both the workspace and mysql paths so I didn’t run into any pathing issues in my apache conf etc.

Steps:

  1. Create your case sensitive jhfsx (HFS Journaled case sensitive) partition. 50GB was fine for me; your requirements may vary. Remember that you need enough space for your MySQL databases and your project files (both current and future).
  2. Stop MySQL 
    $ mysqladmin -uroot -p shutdown
  3. Copy the MySQL and Workspaces folders to the new paritition (I named my new partition “Learnosity”) 
    $ sudo cp -rp /usr/local/mysql  /Volumes/Learnosity
    $ sudo cp -rp /users/michaelsharman/Workspaces  /Volumes/Learnosity
  4. symlink your previous paths to the new partition to save you having to remap apache confs and MySQL commands etc 
    $ sudo ln -sf /Volumes/Learnosity/mysql  /usr/local/mysql
    $ sudo ln -sf /Volumes/Learnosity/Workspaces  /users/michaelsharman/Workspaces
  5. Set the MySQL lower_case_table_names to 0 (same as it is on Linux by default) by adding this line in /etc/my.cnf 
    lower_case_table_names = 0

    If you run $ mysqladmin -uroot -p variables you can see all global variables for your MySQL instance.

  6. Start MySQL 
    $ sudo mysqld -uroot

That should be it, don’t need to do anything else except change the location to your Workspaces folder in your editor of choice.

3 Comments

Tip when adding “allowMultiQueries=true” to your MySQL DSN in ColdFusion

Posted on September 8, 2010 in: ColdFusion, Databases

Scenario…I’m using ColdFusion 9 and MySQL 5. I already had my DSN setup but had a requirement to get the last inserted id from a MySQL INSERT statement (I’m using auto-incrementing integer’s).

As most people know, the best way to do this (in MySQL) is using the last_insert_id() function. I wanted to attach this to the end of my INSERT statement for performance reasons, as I’d only be sending one query to the server instead of separate ones. Plus I’m not even sure if I’d get the correct id back in a production environment if I sent it through as a separate query.

Anyway…for this to happen you need to add “allowMultiQueries=true” to your DSN connection string in ColdFusion Administrator (advanced settings) which I did. However I got an error when I tried to run the code, CF didn’t like me running 2 queries at once. Hmm…restarted CF which should have reloaded the drivers. No luck. Ok…how about restarting MySQL? Nope…

Finally took a comment from Will Tomlinson over on Ben Nadel’s blog to help me out. Delete and recreate the DSN, happy days!

Who want’s some sample code?

<cffunction name="addStuff" access="public" output="false" returnType="numeric">
	<cfargument name="ref" type="string" required="true">
	<cfargument name="stage" type="string" required="true">

	<cfset var q = "">

	<cfquery name="q" datasource="#variables.instance.config.dsn#">
		INSERT INTO mytable
			(
				ref
				, stage
			)
		VALUES
			(
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.ref#">
				, <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.stage#">
			);SELECT LAST_INSERT_ID() AS newId
	</cfquery>		

	<cfreturn q.newId>
</cffunction>
9 Comments

CFML issue on Railo with the accelerate cache tag

Posted on May 27, 2010 in: ColdFusion, Railo

We’ve been using Brandon Purcell’s excellent accelerate custom cache tag for a few projects. I had an issue today while testing a new app which is about to go live. It seemed that whatever I did nothing would be cached.

Upon investigation it seemed that the caching worked fine on Adobe ColdFusion but not on Railo. A quick look at the codebase brought me to this line:

<cfif IsDefined("application.accelerator.#scriptName#.#primarykey#.#secondaryKey#")

Basically this was always returning false, even when I knew the key was in the application scope. I changed it to the following:

<cfif structKeyExists(application,"accelerator[scriptName][primarykey][secondaryKey]")

Success! (Thanks AJ for the final structKeyExists)

Just a quick fyi to anyone else who might be using this custom tag on Railo.

1 Comment