DrySQL --> By Example

Contents

Example #1: Model Generation
Example #2: Association & Validation Generation
Example #3: Logging
Example #4: Using DrySQL with DB2 LUW and iSeries

Example #1: Model Generation


						require_gem 'drysql'
						

						

						employee = Employee.new

The call to Employee.new will create the following class definition in memory (unless the class is already defined):


						class Employee <ActiveRecord::Base


						end

If your employee table is called “employees”, then the associations and validations for employees (as defined by your DB constraints) are generated by DrySQL and the column-instvar mappings are created by ActiveRecord as per usual.

If your employee table is called XYZ123, then you need to explicitly define an Employee class in your Ruby code and call set_table_name “XYZ123” inside the class definition.


						Class Employee <ActiveRecord::Base

						  set_table_name “XYZ123”


						end

That's all!
DrySQL will generate all the associations and validations for your Employee class, and identify all the keys on your employee table the first time you instantiate an Employee object.

The other way to instantiate ActiveRecord objects is by using any of the finder methods (or whatever custom finders you may have created). DrySQL subverts the finder logic and works its magic on object instantiation.


						company = Companies.find(1)

If you have a Companies table in your DB, then the Company class will be defined in memory after the call to find, and the associations and validations will be generated for the Company class. If your Company table is not compatible with the ActiveRecord naming conventions...you know the drill: call set_table_name in your class definition.

Example #2: Association & Validation Generation

Suppose you have a database named drysql and your table layout looks like this:

Note that the following foreign keys exist:

invoices.INVOICE_TYPE references inv_type (INVOICE_TYPE)
pay_app.INVOICE_ID references invoices (INVOICE_ID)
pay_app.PAYMENT_ID references payments (PAYMENT_ID)

>> Now fire up an irb session


						bryan@localhost ~ $ irb
						
irb(main):001:0> require 'rubygems'
						
=> false
						
irb(main):002:0> require_gem 'activerecord'
						
=> true
						
irb(main):003:0> require_gem 'drysql'
						
=> true
						
irb(main):005:0> conf = YAML::load(File.open(File.dirname(__FILE__) + '/database.yml'))
						
=> {"development"=>{"username"=>"evansb", "adapter"=>"mysql", "host"=>"192.168.0.***", "password"=>"******", "database"=>"drysql"}}
						
irb(main):006:0> ActiveRecord::Base.establish_connection(conf["development"])
						
=> #"mysql", :username=>"evansb", :password=>"******", :database=>"drysql", :host=>"192.168.0.***"}, @adapter_method="mysql_connection">
						
irb(main):007:0> class InvoiceType < ActiveRecord::Base
						
irb(main):008:1> set_table_name "inv_type"
						
irb(main):009:1> end
						
=> "InvoiceType"
						
irb(main):010:0> class PaymentApplication < ActiveRecord::Base
						
irb(main):011:1> set_table_name "pay_app"
						
irb(main):012:1> end
						
=> "PaymentApplication"

This chunk of code requires the activerecord and drysql gems, initializes a database connection, and defines 2 data classes. These classes need to be defined and call set_table_name because their table names do not conform to the ActiveRecord naming conventions.



						irb(main):013:0> invoice = Invoice.new
						
GENERATED CLASS: Invoice < ActiveRecord::Base
						
GENERATED ASSOCIATION: Invoice belongs_to :invoiceType, :foreign_key=>INVOICE_TYPE
						
GENERATED ASSOCIATION: Invoice has_many :paymentApplications, :foreign_key=>INVOICE_ID
						
GENERATED ASSOCIATION: Invoice has_many payments, :through=>paymentApplications
						
GENERATED VALIDATION: Invoice validates_numericality_of INVOICE_ID, :allow_nil=>true, :only_integer=>true
						
GENERATED VALIDATION: Invoice validates_nullability_of INVOICE_TYPE
						
GENERATED VALIDATION: Invoice validates_length_of INVOICE_TYPE, :allow_nil=>true, :maximum=>10
						
GENERATED VALIDATION: Invoice validates_length_of BILL_TO_ENTITY, :allow_nil=>true, :maximum=>9
						
GENERATED VALIDATION: Invoice validates_length_of STATUS, :allow_nil=>true, :maximum=>3
						
=> #nil, "INVOICE_TYPE"=>"", "BILL_TO_ENTITY"=>nil, "STATUS"=>nil, @new_record=true>

Here we instantiate a new Invoice object. Since no Invoice class has been defined, DrySQL looks for a matching DB table for "Invoice" and defines the Invoice class as a subclass of ActiveRecord::Base. If no matching table was found, DrySQL would raise a NameError (an example follows later on).

Just before instantiating an Invoice object, DrySQL generates all the appropriate associations and validations for the Invoice class, and logs these generated entities to STDERR, as you can see here. DrySQL does not make assumptions about the names of foreign keys (or primary keys), it just looks them up in the DB's information schema. The associations, validations, and keys are cached on the Invoice class so that they don't need to be re-generated next time we instantiate an Invoice.

Validations
DrySQL can generate the following validations based on the DB's information schema:

validates_numericality_of
validates_length_of
validates_inclusion_of (boolean columns only)
validates nullability_of

In databases that support check constraints validates_each, validates_inclusion_of, validates_exclusion_of can be generated, but I have not yet implemented this. validates_uniqueness_of can be generated as well, but I don't see the value in performing an application-side validation if you need to query the DB to do it.

Note that even though there is a NOT NULL constraint on invoices.invoice_id DrySQL does not generate a validates_nullability_of for this column. This is because the column is automatically generated by the DB, so the DB would accept a null value for this column in an insert statement. DrySQL simply generates application-side validations that mirror those defined on the DB, it does not make assumptions.

Associations
DrySQL can generate the following associations based on the DB's information schema:


			belongs_to

			 has_many

			 has_one

			 has_many :through


		
	
		
DrySQL creates an A belongs_to B association for every pair of classes A & B, where A has a foreign key into B. 
		


		
For every such pair of classes A & B, DrySQL also creates a B has_many A association. A special case for this occurs when A's foreign key into B is a unique key within A. In this case, DrySQL creates a B has_one A association.
		


		
For our B class, as defined above, DrySQL generates a B has_many X :through=> A for every foreign key on A that is not already associated with B. Let me attempt to make that more clear. To find has_many :through associations for B, DrySQL iterates over each existing has_many association for B (call it Y), and creates a B has_many X :through=> Y for the class referenced by each foreign key in Y that B is not already associated with. The reason for this last constraint is that if B already has an association to X, then the generation of a B has_many X :through=> Y will overwrite the existing assocation between B and X.
		


		
In our example above, DrySQL generates an Invoice has_many :payments, :through=>:paymentApplication association. If our Invoice class already had an association with the Payment class, DrySQL wouldn't generate the Invoice has_many :payments, :through=> :paymentApplication because it would overrule the existing association. Obviously, this algorithm breaks down in a situation where B has_many X, :through=>[V, Y, Z], as the declaration of each association between B and X would overwrite the previous one. I'm not sure that there is a solution to this problem, other than to explicitly declare your desired association between B and X in your class definition for B. If an association already exists between B and X, DrySQL honors it and will not overwrite it with a has_many :through.
		

		
has_and_belongs_to_many association generation is not implemented at this point 
		

		
			
			
				
				
					
						

						irb(main):015:0> monkey = Monkey.new
						
No matching table could be found for class: Monkey
						
NameError: NameError
						
	from /usr/lib/ruby/gems/1.8/gems/drysql-0.1.1/lib/dependencies.rb:25:in `const_missing'
						
	from /usr/lib/ruby/gems/1.8/gems/activesupport-1.3.1/lib/active_support/dependencies.rb:131:in `const_missing'
						
	from (irb):6
						

					
				
			

			
		
		

		
		Here we attempt to instantiate a Monkey object. Since the "Monkey" class is not defined, DrySQL looks for a table to match it. No such table is found in our drysql DB, so DrySQL raises a NameError

		

		

		


              
		Example #3:  Logging
		



              How do I know what associations, validations, etc are generated for my model class in memory?

              DrySQL logs all generated entities (associations, validations, classes) to STDERR.

              		

			
			
				
					
						STDERR
					

				
				

					
						
						GENERATED ASSOCIATION: Invoice belongs_to :invoiceType, :foreign_key=>INVOICE_TYPE
						
GENERATED ASSOCIATION: Invoice has_many :appliedPayments, :foreign_key=>INVOICE_ID
						
GENERATED ASSOCIATION: Invoice has_many payments, :through=>appliedPayments
						
GENERATED VALIDATION: Invoice validates_numericality_of INVOICE_ID, :allow_nil=>true, :only_integer=>true
						
GENERATED VALIDATION: Invoice validates_nullability_of INVOICE_TYPE
						
GENERATED VALIDATION: Invoice validates_length_of INVOICE_TYPE, :allow_nil=>true, :maximum=>10
						
GENERATED CLASS: Bry < ActiveRecord::Base
						
GENERATED VALIDATION: Bry validates_nullability_of NAME
						
GENERATED VALIDATION: Bry validates_length_of NAME, :allow_nil=>true, :maximum=>25
					

				
			
			
		
		
				

		

		

             
		Example #4:  Using DrySQL with DB2 LUW and iSeries
		
		
DrySQL implements extensions to IBM's DB2 Rails adapter that offer support for DB2 Linux/Unix/Windows as well as iSeries. 
		You must download and correctly install IBM's DB2 Rails adapter (ibm_db) and driver in order to use DrySQL with DB2.
		

			DB2 on Rails Starter Toolkit on Alphaworks
			IBM_DB on Rubyforge
		
		
		

DrySQL's support for iSeries requires the following:
		
		

		 You must install and configure DB2 Connect
		 Your schema must contain the iSeries information_schema views. These are created automatically by DB2 following execution of a CREATE SCHEMA statement, 
		with the exception of schemas created as old-style iSeries "libraries".
		
		

		Example Database Config for DB2
		

db2:
  
  adapter: ibm_db
  
  database: DRYSQL
  
  username: evansb
  
  schema: evansb
  
  password: ********
  
  host: 192.168.*.***
 
  port: 50000