I'm a newbie converting a large Foxpro database (188 MB) to Ruby on
Rails (RoR) so we can get reports out of it. Our accounting service
sends us weekly updates of the entire database. We don't enter any
data (that's done at the accounting service). The biggest tables are
Customers (now 15,000 rows, 2.5 MB) and Transactions (525K rows, 112
MB). Each of the Foxpro tables has its own key field, which is quite
different from RoR's autoincrementing inntegers.
For example, the Foxpro Customers table is indexed by Account Number,
a 5-digit string that ranges from '00001' to '15221'. So it's almost
the same as an ID field, but not quite.
I have it running using a standard RoR setup including the usual
autoincrementing ID fields. Right now, before loading new data, I do a
Customer.delete_all and then import using the superb DBF Ruby gem.
Ditto the Transactions table (I call it Xactions so as not to confuse
it with the transaction method). Each time I do this, the table starts
incrementing the IDs from where it left off. So the second time I load
Customers it starts with ID 15222, the third time with 30487 (more
customers), etc. Therefore, :id has no relationship with the Account
Number. Moreover, in order to hook up the other tables with Customers,
I have to go through each row, look up the account number, and find
the associated :id. Given a database of this size, it takes HOURS.
Worse, the problem with the Xactions table is we'll run out of :id
numbers at some point (11 digits).
I tried using the Account Number string as the foreign key, but RoR
REALLY wants an integer field and wouldn't accept it.
I thought I'd solve this by setting :id => false in the table
migration and then adding a t.column :id :integer right afterwards.
Then, when I load the data, I'd convert the Account Number string to
the :id integer. Simple. Sweet.
The PROBLEM is that RoR has NO WAY to save or update data without an
existing :id. As the Thomas/Hansson _Agile Web Development_ book makes
clear, RoR just won't save anything in this situation.
I can think of several solutions:
1. Keep the existing setup and just load the data after hours. So what
if it takes all night?
2. Delete the table and then recreate it, so the :id of the reloaded
table starts at 1. I don't know how to do this programmatically. Is
there another way to reset the :id counter without deleting the table?
3. Doing the shortcut with MySQL would be a piece of cake, but I don't
know the syntax to save or update a record in an ActiveRecord context
behind RoR's back.