Using MySQL reserved words as model names

Posted almost 7 years back at Spejman On Rails

The generation of a model with a migration in a ruby on rails application lets to the creation of a database table with the pluralization form of the desired model name. In MySQL, a migration will generate a sql statement like:


CREATE TABLE model_name_pluralized (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY,
`created_on` date DEFAULT NULL, `name` varchar(255) DEFAULT NULL) ENGINE=InnoDB

As you can notice column names are quoted but table name doesn't. If you use as a model name a sigularized form of a MySQL reserved word, the migration that creates this model will generate a statement that will lead to an error like:

Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'databases (`id` int(11) DEFAULT NULL
auto_increment PRIMARY KEY, `created_on` da' at line 1: CREATE TABLE databases (`id` int(11)
DEFAULT NULL auto_increment PRIMARY KEY, `created_on` date DEFAULT NULL, `name` varchar(255)
DEFAULT NULL) ENGINE=InnoDB

Last week I read the Josh Susser Laying Tracks slides who
encourages me to write a patch for this issue.

Before writing anything I tried to find if someone has made something related and I found some tickets related in Rails trac:


The most interesting of this tickets is #4905 which fix all MySQL statements to prevent reserved words crash, but I don't know why isn't included in the code because it's last history is from 05/25/2006. #7850 is closed as duplicated because of #4905. And #3631 history finishes with "don't use reserved words" what in my opinion isn't the best solution.

In brief, the problem exists (I can't name my models with names like "database", "exist", ...) and the patch too (#4905). Then, what should we do to fix this problem?

Episode 37: Simple Search Form

Posted almost 7 years back at Railscasts

A search form is quite different than other forms, this is because it does not deal with model's attributes. See a good way to add a simple search form in this episode.

Syncing up external Filemaker data with Rails

Posted almost 7 years back at work.rowanhick.com

One of the web apps I work on leverages a Filemaker database. My co worker Brent the expert Filemaker dude, and I had a serious problem to tackle - how to get Rails and Filemaker talking to each other. Now Filemaker has iffy SQL standard support and we didn't necessarily want our web app to run live from the Filemaker database. We spent some time looking at making an ActiveRecord adapter for FM but really at the end of the day it was too much like hard work. There are 3rd party solutions out there that can do this stuff for you, but if your needs dictate that they won't work - our experience may help you. I'm going to write up the theory behind this which has been live in production for a good 6 months + and in the coming weeks will release some code acts_as_syncable or similar... First off we wanted it occassionaly connected, one way synchronising. We had an installed instance of Filemaker Web Publishing engine so we could expose our data as xml views. We used a background rails (backgroundrb) process to query Filemaker every 5-10mins and ask for changed data, pull that data using REXML, and populate/update on the Rails side using ActiveRecord. Here's the mechanics... 1. Each object, needs a last_modified timestamp, whenever an update to the object that affects the data being synced, this last_modified timestamp needs to be updated (for all intents and purposes create an updated_at column on the filemaker side). 2. Next on the Filemaker side create a layout that exposes that data when queried via Web Publishing, as an xml result. We're going to do two different queries so your layout must support these - as per note ii. 3. On the Rails side we need to add two models, and extend the models we want synced. First I created a SyncLog model which will track when syncing occurs (just for informational purposes), then a ClassSyncLog, which will track the models synced, when they were synced, the last timestamp and id of each model synced. 4. For each model on the rails app side, we need a number of properties: - The corresponding id field name from Filemaker - A hash listing each rails field and it's corresponding name on the Filemaker side (we're assuming that these will be different, in our case they were wildly different) - Some call backs, pre process sync, and post process sync if you need to do stuff before/after syncing. 5. We need a model that will actually do the work of syncing the objects. This is the involved part of the process that does the grunt work of syncing so pay careful attention. 5.1 Pass in a list of Class names, for each class name do the following 5.2 Query filemaker using Net::HTTP from the last updated_at timestamp and see if any records have been updated since then, if so move on to 5.3 or go to the next class (well... almost there's a trick here **) 5.3 For that returned result set from Filemaker (from the HTTP Raw data returned), fill an REXML object from the returned xml file. For each of the records in the result set do the following 5.3.1 Find or create the rails side object from the corresponding id in the resultset, check if you can sync it per the pre process rules if you have any. 5.3.2 Iterate over each of the fields in the result set, see if it matches a field in the field mapping on the rails class you're syncing (4). If it does update the current object from the result set. 5.3.3 Finally save the current object and move onto the next record 5.4 Now before moving onto the next class you need to save a ClassSyncLog record, with the last timestamp of the last updated record, and the last id. So next time you have a starting point to retrieve the records. 5.5 Move onto the next class 5.6 After completing all classes write out a log entry to say it's been done. 6. Wait 5mins... and repeat. Of course there's some caveats. i. First is if you're dealing with 100'000's of records you do not want to grab just one resultset back - you're going to run out of memory and/or have problems with getting the full stream of data. So we get the result set query into groups of ~ 2000. ii. Related to this, lets say you have 100'000 records updated at 10:10:01 AM. The first request is going to be for all records greater than the last updated time, for example 9:30AM. So you'd get 2000 in on your 100'000 records, the next time in you ask for > 10:10:01 am, you're going to skip 98'000 records, so you want to do two queries first: a. Give me all records time = last_updated_time AND id > last_updated.id, which will catch the next 2000 records - repeat this one until you get no more results then: b. Give me all records time > last_updated_time - then go back to qry #a for the next repeat. iii. You will have performance issues, this isn't quick by any stretch of the imagination so be a little patient to work through the issues. We're running massive datasets so we often see updates that take hours. Not great but it's ok. iv. Watch your memory, this can be a long running process so throw in 'GC.start' to clean up memory along the way. (eg every resultset retrieved) v. To avoid ID collissions it's strongly recommended you use the MySQL master-master auto-increment-increment + offset scheme, so you stagger your ID inserts between your Filemaker DB(s) and MySQL DB(s). Again look for code in the coming weeks.. I'm wrapping our custom stuff up into an acts_as module for the (limited) audience this may help...

Loading all Rails test fixtures with fixtures :all

Posted almost 7 years back at Cody Fauser

Are you as tired as we were of loading 20+ different fixtures in each of your Rails test classes? We were, and we even added a method all_fixtures() to test_helper.rb to do the loading of all our fixtures for us.

Thankfully though, we don't need our own helper method anymore, as the Rails fixtures() method will now accept a symbol :all, which will instruct the test helper to load all of your fixtures automatically.

1
2
3
4
5
6
7
8

require File.dirname(__FILE__) + '/../test_helper'

class ShopTest < Test::Unit::TestCase
  fixtures :all

  # Your tests here
end

As of Rails 1.2.3 this feature has not yet been merged from the trunk. This means that you'll either need to run Edge Rails from Subversion, or install the beta Rails gems as follows:


sudo gem install -s http://gems.rubyonrails.org rails -y

Happy testing!

Sometimes It's The Little Things, pt 2

Posted almost 7 years back at zerosum dirt(nap) - Home

So my first patch to Rails core was accepted yesterday. It’s a tiny, tiny patch. All it does is add a :method parameter to the auto_complete_field helper so you can do RESTful autocompletion (the filter query should be submitted with a GET, not a POST, if you want to follow the REST conventions).

No big deal really, but it feels good to finally be able to “give back” to the community in a way other than blogging and IRC help. You know, with like, actual code that benefits people other than just me. I’ve contributed to a handful of Java and PHP-based OSS projects over the years, but this is officially my first contribution to a Ruby-based project, and that certainly feels like a step in the right direction.

Ruby-esque JMX

Posted almost 7 years back at Revolution On Rails

The topic of JMX on JRuby came up recently and I decided to play around. I found a great starter on Jeff Mesnil's blog, but I decided I hated the syntax.

Ruby has spoiled me. ActiveRecord has spoiled me.

So I cooked up this little (fully working) example:

#Find all the MBeans matching some object name
mbeans = JMX::MBean.find_all_by_name("cacheStatistics:*")

mbeans.each do |bean|
puts "#{bean.name} "

#Either use methods on the bean object
puts " - CacheHits: #{bean.CacheHits}"

#Or access the attributes hash.
puts " - CacheMisses: #{bean.attributes["CacheHits"]}"
end


The code is ~50lines which I'll post at some point.

I never thought working with java objects could be made to "feel" nice.

I was also chatting with "headius" on #jruby, and he mentioned that Rob Harrop, of Spring fame, had a talk at JavaOne about about something similar called MScript. I'd love to get my hands on those slides.

Ruby-esque JMX

Posted almost 7 years back at Revolution On Rails

The topic of JMX on JRuby came up recently and I decided to play around. I found a great starter on Jeff Mesnil's blog, but I decided I hated the syntax.

Ruby has spoiled me. ActiveRecord has spoiled me.

So I cooked up this little (fully working) example:

#Find all the MBeans matching some object name
mbeans = JMX::MBean.find_all_by_name("cacheStatistics:*")

mbeans.each do |bean|
puts "#{bean.name} "

#Either use methods on the bean object
puts " - CacheHits: #{bean.CacheHits}"

#Or access the attributes hash.
puts " - CacheMisses: #{bean.attributes["CacheHits"]}"
end


The code is ~50lines which I'll post at some point.

I never thought working with java objects could be made to "feel" nice.

I was also chatting with "headius" on #jruby, and he mentioned that Rob Harrop, of Spring fame, had a talk at JavaOne about about something similar called MScript. I'd love to get my hands on those slides.

Acts As Fast But Very Inaccurate Counter

Posted almost 7 years back at Revolution On Rails

Introduction

If you have chosen the InnoDB MySQL engine over MyISAM for its support of transactions, foreign keys and other niceties, you might be aware of its limitations, like much slower count(*). Our DBAs are in a constant lookout for slow queries in production and the ways to keep DBs happy so they recommended that we should try to fix count(). They suggested to check SHOW TABLE STATUS for an approximate count of rows in a table. This morning I wrote acts_as_fast_counter which proved that the speed is indeed improved but the accuracy might be not acceptable. The rest of the post just records details of the exercise.

The approach

I created a model per engine and seeded each with 100K records. Then I run count on each model for a thousand times and measured the results.

The code:

module ActiveRecord; module Acts; end; end 

module ActiveRecord::Acts::ActsAsFastCounter

def self.included(base)
base.extend(ClassMethods)
end

module ClassMethods

def acts_as_fast_counter
self.extend(FastCounterOverrides)
end

module FastCounterOverrides

def count(*args)
if args.empty?
connection.select_one("SHOW TABLE STATUS LIKE '#{ table_name }'")['Rows'].to_i
else
super(*args)
end
end

end

end

end

ActiveRecord::Base.send(:include, ActiveRecord::Acts::ActsAsFastCounter)

# create_table :myisams, :options => 'engine=MyISAM'  do |t|
# t.column :name, :string
# end
# 100_000.times { Myisam.create(:name => Time.now.to_s) }
#
# create_table :innodbs, :options => 'engine=InnoDB' do |t|
# t.column :name, :string
# end
# 100_000.times { Innodb.create(:name => Time.now.to_s) }

class Bench

require 'benchmark'
require 'acts_as_fast_counter'

def self.run
measure
show_count
convert_to_fast_counter
show_count
add_records
show_count
destroy_records
show_count
measure
end

def self.measure
puts "* Benchhmarks:"
n = 1_000
Benchmark.bm(12) do |x|
x.report('MyISAM') { n.times { Myisam.count } }
x.report('InnoDB') { n.times { Innodb.count } }
end
end

def self.convert_to_fast_counter
Innodb.send(:acts_as_fast_counter)
puts "* Converted Innodb to fast counter"
end

def self.add_records
@myisam = Myisam.create(:name => 'One more')
@innodb = Innodb.create(:name => 'One more')
puts "* Added records"
end

def self.destroy_records
@myisam.destroy
@innodb.destroy
puts "* Destroyed records"
end

def self.show_count
puts "* Record count:"
puts " MyISAM: #{ Myisam.count }"
puts " InnoDB: #{ Innodb.count }"
end

end


The results:
* Benchhmarks:
user system total real
MyISAM 0.180000 0.040000 0.220000 ( 0.289983)
InnoDB 0.430000 0.070000 0.500000 ( 35.102496)
* Record count:
MyISAM: 100000
InnoDB: 100000
* Converted Innodb to fast counter
* Record count:
MyISAM: 100000
InnoDB: 100345
* Added records
* Record count:
MyISAM: 100001
InnoDB: 100345
* Destroyed records
* Record count:
MyISAM: 100000
InnoDB: 100345
* Benchhmarks:
user system total real
MyISAM 0.250000 0.030000 0.280000 ( 0.350673)
InnoDB 0.250000 0.040000 0.290000 ( 0.977711)


Final thoughts

The MySQL manual has a clear warning about inaccuracy of the amount of rows in the SHOW TABLE STATUS results:

Rows - The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.


The test confirms it by showing 345 more records then expected thus making it not very useful but for some edge cases. If you know a way to improve the speed of count() on InnoDB with some other approach beyond using a counter table, please share.

Acts As Fast But Very Inaccurate Counter

Posted almost 7 years back at Revolution On Rails

Introduction

If you have chosen the InnoDB MySQL engine over MyISAM for its support of transactions, foreign keys and other niceties, you might be aware of its limitations, like much slower count(*). Our DBAs are in a constant lookout for slow queries in production and the ways to keep DBs happy so they recommended that we should try to fix count(). They suggested to check SHOW TABLE STATUS for an approximate count of rows in a table. This morning I wrote acts_as_fast_counter which proved that the speed is indeed improved but the accuracy might be not acceptable. The rest of the post just records details of the exercise.

The approach

I created a model per engine and seeded each with 100K records. Then I run count on each model for a thousand times and measured the results.

The code:

module ActiveRecord; module Acts; end; end 

module ActiveRecord::Acts::ActsAsFastCounter

def self.included(base)
base.extend(ClassMethods)
end

module ClassMethods

def acts_as_fast_counter
self.extend(FastCounterOverrides)
end

module FastCounterOverrides

def count(*args)
if args.empty?
connection.select_one("SHOW TABLE STATUS LIKE '#{ table_name }'")['Rows'].to_i
else
super(*args)
end
end

end

end

end

ActiveRecord::Base.send(:include, ActiveRecord::Acts::ActsAsFastCounter)

# create_table :myisams, :options => 'engine=MyISAM'  do |t|
# t.column :name, :string
# end
# 100_000.times { Myisam.create(:name => Time.now.to_s) }
#
# create_table :innodbs, :options => 'engine=InnoDB' do |t|
# t.column :name, :string
# end
# 100_000.times { Innodb.create(:name => Time.now.to_s) }

class Bench

require 'benchmark'
require 'acts_as_fast_counter'

def self.run
measure
show_count
convert_to_fast_counter
show_count
add_records
show_count
destroy_records
show_count
measure
end

def self.measure
puts "* Benchhmarks:"
n = 1_000
Benchmark.bm(12) do |x|
x.report('MyISAM') { n.times { Myisam.count } }
x.report('InnoDB') { n.times { Innodb.count } }
end
end

def self.convert_to_fast_counter
Innodb.send(:acts_as_fast_counter)
puts "* Converted Innodb to fast counter"
end

def self.add_records
@myisam = Myisam.create(:name => 'One more')
@innodb = Innodb.create(:name => 'One more')
puts "* Added records"
end

def self.destroy_records
@myisam.destroy
@innodb.destroy
puts "* Destroyed records"
end

def self.show_count
puts "* Record count:"
puts " MyISAM: #{ Myisam.count }"
puts " InnoDB: #{ Innodb.count }"
end

end


The results:
* Benchhmarks:
user system total real
MyISAM 0.180000 0.040000 0.220000 ( 0.289983)
InnoDB 0.430000 0.070000 0.500000 ( 35.102496)
* Record count:
MyISAM: 100000
InnoDB: 100000
* Converted Innodb to fast counter
* Record count:
MyISAM: 100000
InnoDB: 100345
* Added records
* Record count:
MyISAM: 100001
InnoDB: 100345
* Destroyed records
* Record count:
MyISAM: 100000
InnoDB: 100345
* Benchhmarks:
user system total real
MyISAM 0.250000 0.030000 0.280000 ( 0.350673)
InnoDB 0.250000 0.040000 0.290000 ( 0.977711)


Final thoughts

The MySQL manual has a clear warning about inaccuracy of the amount of rows in the SHOW TABLE STATUS results:

Rows - The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.


The test confirms it by showing 345 more records then expected thus making it not very useful but for some edge cases. If you know a way to improve the speed of count() on InnoDB with some other approach beyond using a counter table, please share.

Episode 36: Subversion on Rails

Posted almost 7 years back at Railscasts

This episode will walk you through setting up a Rails project on subversion. It covers some helpful tips and gotchas you may experience along the way.

DataGrid - handy hints

Posted almost 7 years back at work.rowanhick.com

Okay the orders list. It's a plain vanilla DataGrid component bound to the result from an HTTPService call to get an XML representation of an ActiveRecord orders collection. This all pretty easy to get up and running, but I ran into two immediate snags. Requirement #1 - Multicurrency in the totals column. Our system deals with orders in currencies all over the world, so we have price types and associate each order with a price type. Fair enough. Now we don't store price type symbol with the total (naturally) so how do we get it to appear in the same column in the the datagrid. Option 1 (on the Rails side) would be to add some faux accessor and iterate over the orders populating it before we handed it to the to_xml function sending it to Rails (that's not without it's problems). OR Option 2 (on the Flex side) we manipulate it over here. First off we create a little function that takes our current item (row) and the column we're manipulating, then prepends the symbol and appends the code to the amount. private function formatCurrency(item:Object, column:DataGridColumn):String { var field:String = column.dataField; return item.price_type_dollar_sign+' '+item[field]+' '+item.price_type_symbol; } Then in the mxml code for the datagrid <mx:DataGridColumn headerText="Total Amount" dataField="total_amount_payable" labelFunction="formatCurrency"/> Requirement #2 Sorting a field in an order different to that displayed So we have an order status (Draft, Pending Review, Review, Payment Pending, In Manufacturing, Shipped etc). Great, now you put that straight into your datagrid, hit a column header to sort, and realise it's doing an alpha sort on the status name. As you expect (but not as you wanted). Well again there's two ways to accomplish this - one is to pass in custom sort function to figure out the order. OR The other option, is to pipe down in your xml along with the order status name, a sort order numeric value , so Draft is 1 Shipped is 1000, everything in between gets a number etc. Then just as you do for the currency symbol, change the formatting of the column - so we bind our column to the order status sort_order value, but we display the order status name. When the datagrid sorts, it sorts on the bound value, not on the formatted value. Very nifty and simple. Just like this... private function formatStatus(item:Object, column:DataGridColumn):String { return item.order_status_name; } <mx:DataGridColumn headerText="Status" dataField="order_status_id" labelFunction="formatStatus"/> No nasty sort function, and does the trick. That's it for now... till next time.

Javascript: Event.onElementReady

Posted almost 7 years back at Revolution On Rails

We deal with a lot of Javascript at RHG. Rather then create functional one-offs that become hard to maintain and very duplicative, we prefer to have collections of behavior that can be assigned to parts of the document. Our CSS designers especially like this because it makes our markup clean and easy to read. It also means we can have tests that exercise a series of interactions.

However, there is a problem we run into when loading the page over a slow connection: the Javascript is not run until window.onload, meaning our users suffer as they can see part of the page rendered but not use it until it's fully loaded. For those who don't know, window.onload will not execute until all images, CSS, and Javascript files have loaded. Our first solution to this problem was to use onDOMReady. This has worked fairly well and kept our site running reasonably quickly. We've run into problems with onDOMReady in IE6 however, and as a result disabled it in favor of having our pages render all the time. In the IE family of browsers if a script tries to access a part of the DOM before it has been completely processed the browser will raise an exception "operation aborted". After this alert message pops up, the web page becomes unusable and the browser could even crash.

After some careful thinking we decided we would attach our behavior objects in smaller chunks of the DOM. Rather then wait until the window.onload or onDomReady events fire we can use a few inline script tags that call a function that figures out how to attach itself to its most immediate parentNode.

For example, we do this:


<div class="parent">
<div class="bvr-blah">
</div>
<script type="text/javascript">
RHG.Behavior.attach();
</script>
</div>


There could be multiple behaviors in the above block of markup that are all contained within the hypothetical div.parent element.

Now, to allow this to work the div.parent must be ready. Otherwise, IE will cancel the whole page rendering with the "Operation Aborted" alert.

Event.onElementReady checks for features of the DOM element to determine whether or not the element is really ready to be manipulatd. Thankfully, IE doesn't mind if you read from an element before it's ready only if you try and modify it. This method will poll the DOM element until either the nextSibling or the textContent is non null.


Object.extend(Event,{
// check whether or not the DOM element is ready
onElementReady: function(element,callback)
{
if( element && (element.nextSibling || element.textContent) ){
callback();
}
else{
setTimeout( this.onElementReady.bind(this,element,callback), 1 );
}
}
});


Now we can attach our behaviors as the page is rendering and avoid "Operation Aborted" alerts from IE.

Javascript: Event.onElementReady

Posted almost 7 years back at Revolution On Rails

We deal with a lot of Javascript at RHG. Rather then create functional one-offs that become hard to maintain and very duplicative, we prefer to have collections of behavior that can be assigned to parts of the document. Our CSS designers especially like this because it makes our markup clean and easy to read. It also means we can have tests that exercise a series of interactions.

However, there is a problem we run into when loading the page over a slow connection: the Javascript is not run until window.onload, meaning our users suffer as they can see part of the page rendered but not use it until it's fully loaded. For those who don't know, window.onload will not execute until all images, CSS, and Javascript files have loaded. Our first solution to this problem was to use onDOMReady. This has worked fairly well and kept our site running reasonably quickly. We've run into problems with onDOMReady in IE6 however, and as a result disabled it in favor of having our pages render all the time. In the IE family of browsers if a script tries to access a part of the DOM before it has been completely processed the browser will raise an exception "operation aborted". After this alert message pops up, the web page becomes unusable and the browser could even crash.

After some careful thinking we decided we would attach our behavior objects in smaller chunks of the DOM. Rather then wait until the window.onload or onDomReady events fire we can use a few inline script tags that call a function that figures out how to attach itself to its most immediate parentNode.

For example, we do this:


<div class="parent">
<div class="bvr-blah">
</div>
<script type="text/javascript">
RHG.Behavior.attach();
</script>
</div>


There could be multiple behaviors in the above block of markup that are all contained within the hypothetical div.parent element.

Now, to allow this to work the div.parent must be ready. Otherwise, IE will cancel the whole page rendering with the "Operation Aborted" alert.

Event.onElementReady checks for features of the DOM element to determine whether or not the element is really ready to be manipulatd. Thankfully, IE doesn't mind if you read from an element before it's ready only if you try and modify it. This method will poll the DOM element until either the nextSibling or the textContent is non null.


Object.extend(Event,{
// check whether or not the DOM element is ready
onElementReady: function(element,callback)
{
if( element && (element.nextSibling || element.textContent) ){
callback();
}
else{
setTimeout( this.onElementReady.bind(this,element,callback), 1 );
}
}
});


Now we can attach our behaviors as the page is rendering and avoid "Operation Aborted" alerts from IE.

Screenshot #1 - Orders list

Posted almost 7 years back at work.rowanhick.com

Right here's our first look at the prototype app. Nothing too dramatic but gives you an idea of where we're headed with this. As you can see we've got some fairly standard controls, a datagrid, combo boxes, and buttons. Tomorrow we'll show how this is structured, and how I managed to get some fairly neat stuff with the datagrid working that demonstrates the polish already in Flex.

Episode 35: Custom REST Actions

Posted almost 7 years back at Railscasts

REST adds many constraints. It restricts your controllers to seven actions. Normally this is okay, but sometimes you need to add your own custom actions. Learn how in this episode.