Activerelation, stop being so clever.

I nearly flipped by lid today, when doing what should have been a simple activerelation query to get the  'last record'.

Foo.order('column')
SELECT * FROM foo ORDER BY column ASC;


Foo.order('column ASC').last
SELECT TOP 1 * FROM foo ORDER BY column DESC;



Foo.order('column DESC').last
SELECT TOP 1 * FROM foo ORDER BY column ASC;


Foo.order('column DESC').first
SELECT TOP 1 * FROM foo ORDER BY column DESC;


Foo.order('column ASC').last
SELECT TOP 1 * FROM foo ORDER BY column ASC;

Foo.order('column ASC').limit(1)
SELECT TOP 1 * FROM foo ORDER BY column ASC;

Foo.order('column DESC').limit(1)
SELECT TOP 1 * FROM foo ORDER BY column DESC;

Something tells me I'll never like SQL server's TOP 1 syntax, as I spent ages just staring at the end of the queries and wondering "why the heck isn't it choosing the last record, I told it to sort DESC!"

I miss my LIMITs.

Ruby 1.8.*, UTF-8 and Oracle / Oracle Enhanced / OCI-8

... is a mess.

For future reference:

1. Check select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; is AMERICAN_AMERICA.AL32UTF8 or similar
2. In your boot.rb, prior to any gems being loaded, add

   require 'rubygems'
   
   # Set up gems listed in the Gemfile.
   ENV['BUNDLE_GEMFILE'] ||= File.expand_path('../../Gemfile', __FILE__)
   ENV['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'
   
   require 'bundler/setup' if File.exists?(ENV['BUNDLE_GEMFILE'])
   

3. Curse repeatedly at Oracle's database wide character sets, rather than MySQL's table based character sets - if you are migrating, you have to migrate every schema being used.
4. Export your data, change the character set by ALTER DATABASE CHARACTER SET, re-import.

Bad rails

Can someone help this patch get a move on?

Escaping `table names` for mysql - so reserved sql keywords don't cause errors.

Stuck in the middle of MySQL hell

I hate BETWEEN.

How fucking stupid is it that:
SELECT * FROM table WHERE id BETWEEN 1 AND 1000 is not the same as SELECT * FROM table WHERE id BETWEEN 1000 AND 1.

Where's the principle of least fucking surprise.

PHP and Foreign Key Constraints

I hate having a Really Big Database that complains when you do a DELETE FROM table_foo with something obscure like Cannot delete or update a parent row: a foreign key constraint fails.

Great, mysql, just $##%ing wonderful. There's 17 tables that reference table_foo, which one is broken?

I think to myself: surely there's something that will show me all of the keys pointing to this table, yeah?

No. Not even a hint. I was stuck using DB_Visualiser and inspecting tables. Ugh!

Below is a solution I bothered to whip up at work today. $db is an instance of PEAR::DB, CLS_html::Form_Start() is merely a FORM tag with some extras. Ditto CLS_html::input(). By default, PEAR::DB fetchs row names (Fetch Mode Assoc?) rather than numbers.

<?php

/**
* What tables reference $target?
*/
function references($target, $keys) {
 $matches = array();

 foreach ($keys as $key => $tgt) {
     if ($target == $tgt) {
         $matches[] = $key;
     }
 }

 return $matches;
}

function findKeys() {
 global $db;

 $pattern = "/CONSTRAINT `.*` FOREIGN KEY \(`(.*)`\) REFERENCES `(.*)` \(`(.*)`\)/i";

 $sql = "SHOW TABLES";
 $result = $db->query($sql);

 $matches = array();
 $keys = array();

 while ($row = $result->fetchRow()) {
     foreach ($row as $table) {
         $syntax = $db->query(sprintf("SHOW CREATE TABLE %s", $table))->fetchRow();

         preg_match_all($pattern, $syntax["Create Table"], $matches);

         for ($i = 0; $i < count($matches[0]); $i++) {
             $keys[$syntax["Table"] . "." . $matches[1][$i]] = $matches[2][$i] .
                                                         "." . $matches[3][$i];
         }
     }
 }

 return $keys;
}

if (!isset($_REQUEST["field"]) || !isset($_REQUEST["value"])) {
 print CLS_html::Form_Start();

 print '<p>';
 print 'Field<br />';
 print CLS_html::input("text","field");
 print 'Value<br />';
 print CLS_html::input("text","value");
 print CLS_html::input("submit","","Describe");
 print '</p>';

 print CLS_html::Form_End();
} else {
 $keys = findKeys();
 $references = references($_REQUEST["field"],$keys);
 $args = $_REQUEST["value"];


 print '<pre>';

 for ($i=0;$i<count($references); $i++) {
     $parts = explode(".", $references[$i]);
     $sql = sprintf("SELECT * FROM %s
                      WHERE %s = ? LIMIT 1",
                   $parts[0],
                   $references[$i]);
     $result = $db->query($sql, $args);

  
     if ($result->numRows() > 0) {
         print str_replace("?",$_REQUEST["value"],$sql) . ";\n";
     }
 }
 print '</pre>';
}




?>