How to fake composite primary keys?

Rails
1

I know ActiveRecord doesn’t support composite primary keys, but I need
to use
one, and I need it ASAP. I don’t need any composite foreign keys,
luckily;
what I have is a table that stores old versions of rows in another
table, so
the composite key is an id + date stamp. Would someone tell me a hack I
can
use to support this?

View this message in context:
http://www.nabble.com/How-to-fake-composite-primary-keys--t1388565.html#a3730983
Sent from the RubyOnRails Users forum at Nabble.com.

2

By the way, I need to support inserting, deleting and queries. So if I
understand RDBMSs correctly, using a VIEW to combine the composite key
into
a single key is out of the question.

View this message in context:
http://www.nabble.com/How-to-fake-composite-primary-keys--t1388565.html#a3731239
Sent from the RubyOnRails Users forum at Nabble.com.

3

Why not put in an id column for the sake of rails, even though you do
not use it “logically” as your primary key. Then you can enforce the
uniqueness of your composite primary key through custom validations.
You would also probably want to write some custom routes to aid in
passing around the two pieces of your composite key.

It’s a quick answer, although perhaps not ideal.

-Derrick S.

4

If you need it ASAP then you could just make your own database class. I
know
it’s not pretty, but then, neither is your schema. You have the ability
to
fire off your own SQL statements. I have to resort to that for a couple
of
tables. It’s not fun but it’s also not a show-stopper.

5

No, that’s no good. I cannot change the schema.

Here’s an idea… would someone let me know how to do this better?

(1.) Make a regular class (not derived from ActiveRecord::Base).
If I do this, will I have to use
ActiveRecord::Base.establish_connection(), or will AR still connect
automatically when it needs to?

(2.) Make functions to give the appearance of an AR model, such as

  • Model.new(:col1 => “value1”, :col2 => 2.2222)
  • Model#attributes
  • Model.find(primary key)
  • Model.find([[array],[of],[keys]])
  • Model.find(:all, :conditions => “…”, :limit => 12, :offset => 7,
    :include => …)
  • Model.find(:first, :conditions => [“…?..”, …], :order => “…”,
    :joins
    => “…”)
  • Model.find_by_sql(“select * from hobos”)
  • Model.find_by_sql(“select count(*) from hobos”)
  • Model.find_by_some_database_column(…)
  • Model.find_all_by_some_database_column(…)
  • Model#some_database_column
  • Model#some_database_column?
  • Model#update_attribute(:column, “value”)
  • Model#update_attributes(:col1 => “value1”, :col2 => “value2”)
  • Model.update(primary key, :col1 => “value1”, :col2 => “value2”)
  • Model.update_all(“price=2*price”, “name=‘the price is right’”)
  • Model#save
  • Model#save!
  • Model#reload
  • Model.delete(primary key)
  • Model.delete([[array],[of],[keys]])
  • Model.delete_all([“price != ?”, @the_price_that_is_right])
  • Model#destroy
  • Model.destroy_all([“price != ?”, @the_price_that_is_right])
  • Model#id # returns primary key

Hmm, well, implementing all of this would be prohibitively difficult
(and I
wouldn’t get proper transaction support, and I don’t know how to
implement
validation and the errors collection, etc.), but I could stick to
implementing just the methods that are really needed for my application.

(3.) Do all database access with SQL. The main question I have here is,
how
can I generate ‘safe’ (escaped) SQL statements from an expression
[“name=?,
age=?”, “ol’ Joe”, 93]?

con = ActiveRecord::Base.connection

con is a subclass of ActiveRecord::ConnectionAdapters::AbstractAdapter

Select:

con.select_all(“SELECT * FROM hobos WHERE poor=FALSE”)
con.select_one(“SELECT * FROM hobos WHERE poor=FALSE”)
con.select_values(“SELECT age,name FROM hobos LIMIT 3”) => [34,25,67]

Create/Update/Delete/Execute:

gee, what would happen if I fed a DELETE to update()?

auto_inc_id = con.insert(“INSERT INTO hobos (name,age) VALUES (‘Old
Joe’,66)”)
num_rows = con.update(“UPDATE hobos SET age=57 WHERE age=56”) #
birthday!
num_rows = con.delete(“DELETE FROM hobos WHERE status=‘dead’”)
con.execute(“ALTER TABLE hobos ADD shopping_cart BOOLEAN”)

Transactions:

con.begin_db_transaction()
con.commit_db_transaction()
con.rollback_db_transaction()

–
View this message in context:
http://www.nabble.com/How-to-fake-composite-primary-keys--t1388565.html#a3797889
Sent from the RubyOnRails Users forum at Nabble.com.