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.
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.
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 Spell On Apr 3, 2006, at 2:45 PM, Qwertie wrote:> > 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. > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
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.
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. On 4/7/06, Qwertie <qwertie256@gmail.com> wrote:> > > 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. > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060407/aab33353/attachment.html