Monthly Archives

April 2011

Database Management Systems

Updating PostgreSQL fields using multiple tables

I was involved in a small project where I had to crawl through several systems to mine data, organize it, and filter it down so it could be placed into a database. The actual crawl took many days to complete and unfortunately, a small irregularity occurred during the process. Simply put, INSERTs were being used when in fact UPDATEs with a check should have been implemented.

Imagine my dismay when I found that, although the information was all in the database, it was floating around jumbled and would be difficult to re-organize, so much so that I almost feared that the a re-crawl could be necessary.

Of course, that would have been insane; after all, once you have your data in a database, there should always be some fancy, efficient way of re-organizing it.

At one point during the process, I needed to able to take data from one field and assign it to a similar field in a different table. To do so I required an UPDATE statement of a caliber I had never used (or needed) before.

At first I tried funny (and terrible) variations like:

UPDATE table_a AS a, table_b AS b SET a.this_field = a.thatfield WHERE a.some_id = b.some_id

I’m sure you’re laughing if you can see I was under the delusion that I could use an UPDATE statement like a normal SELECT statement.

Anyhow, after doing a little digging the solution is still elegant and simple simply requiring something still closely resembling an INSERT query.

I ended up using this format instead:

UPDATE private."MyData" MD SET thisid = C.thisid FROM private."ComparisonData" C WHERE MD.thatid = C.thatid AND (other statements..)

As you can see I’m using a Schema called private which you can happily ignore, and some of the tables and fields’ names have been changed to protect the innocent (or perhaps in this case, the guilty 🙂

Manipulating 0 byte or empty files in Linux

There are a surprising amount of times I’ve had to manipulate empty files dispersed amongst a large group of variously sized other files.

A long time ago I remember using some convoluted shell script on my Sun Workstation 3/80 (SunOS baby, none of that fancy Solaris whippersnapper!)

Later when I was using Debian, I wrote little TCL (and even later Python) scripts.

Now I’m using Ubuntu on most of my Desktop (or development I s’pose) machines and it seems things just keep getting easier.

Now I found a method that borders on the rediculous easy, using the find command.


find . -type f -size 0 | xargs command

So lets say we just want to list the files MMmkay?


find . -type f -size 0 | xargs ls -l

Or say we want to remove/delete the files


find . -type f -size 0 | xargs rm -f

Of course I know that this isn’t an Ubuntu specific solution (this should obviously work even on older versions of find and pick-your-unix-flavor or even some inferior operating system (*cough*) where you’ve ported over your unix tools to.

Resolving Prototype Scriptaculous jQuery Conflictions with noConflict() Example

There is a good chance if you are reading this, you were under the same problem I myself had a short while ago…

You’re using Scriptaculous or something else built on top of Prototype and everything works fine when one fine day or hour, you decide you want to incorporate a widget or some code that uses jQuery.

So you do what I did, search around only to have people mention with a high and mighty, auspicious sounding response that the answer to all your problems, the magical cure-all for your what ails ya:

jQuery.noConflict();

Of course they mention that all you have to do is “call this code” after importing the jquery script.. and possibly that you should call it like this ‘$.noConflict();’ or perhaps ‘jQuery.noConflict();’ or laughingly ‘$jQuery.noConflict();’

Some of the smarter (but no more helpful) ones tell you correctly to use assign the noConflict to a variable

like so:
$var = jQuery.noConflict();

Yet they then expect you to just know what to do from there. Great. Thanks. So you spend a couple hours thinking that maybe if you call the statement before the script is imported or right after or before that one or after that one, and then you start to change some of the references in your code.

No luck?

The thing is that all references to $ need to be changed.

Let’s go over an example. I was using a script for a neat little widget on a site that I was working on and it sat on top of Scriptaculous (and of course Prototype). When I dropped in the jQuery code… things went all to hell.

Courage Wolf Says its Better to Have Tried and Failed than to Never Try At All
He may be right, but we're not going to fail this time!

The Scriptaculous is not important, we do not have to make any changes to any of that code at all to get your new jQuery code to work. In my example I dropped in a modalbox, one of my favorites called “Sexylightbox“.

I placed my new modalbox code after my old code.

It required these lines to work:


Import/Src these scripts:


jquery.js
jquery.easing.js
sexylightbox.v2.3.jquery.js

Then it also needed to be initialized:

<script type=”text/javascript>

$(document).ready(function(){

SexyLightbox.initialize({color:’black‘, dir: ‘../../images/sexyimages‘});

});
</script>

Then I had to decide what to name my noConflict variable.. in this case I decided to go with $sexy.

So I changed

 

<script type=”text/javascript>

$(document).ready(function(){

SexyLightbox.initialize({color:’black‘, dir: ‘../../images/sexyimages‘});

});
</script>

to

 

<script type=”text/javascript>

$sexy(document).ready(function(){

SexyLightbox.initialize({color:’black‘, dir: ‘../../images/sexyimages‘});

});
</script>

This is just the first step! I made sure to edit sexylightbox.v2.3.jquery.js and I added in this line to the top of the file:


$sexy = jQuery.noConflict();

Then, I completed it with my finish move and in my editor VI I replaced every instance of the $ character with the $sexy string using a global search and replace command while in Escape mode. If you’re more of the GUI type and using BlueFish or SlickEdit or something else, then you should have a “Replace” and then “Replace All Instances of” option under the Find menubar option.

Then as crazy as it may sound, everything just started working perfectly.

The important thing to keep in mind is that every single instance of where jQuery code is using the $, you have to replace it with the new variable you’ve created with the noConflict function. I wish someone had spelled it out that simply for me.. would have saved me hours of wasted time and frustration.