In my quest to RESTify Evedb.info I keep finding more reasons to fawn ove REST (and CRUD): With the most recent Eve Online Database Dump for Revelations I have a need to migrate the data to my own schema. I also need to treat the database dump as a "Master" copy of what should appear in my site; stale data must be removed from my database.
I like to automate things. Prior to the RESTification efforts I would pump out raw SQL files and import them into Postgres in a batch. The SQL took about 45 minutes to import and was often held up due to foreign key issues.
Anyways, with REST I can funnel all of that through my controllers which have that functionality anyways (albeit heavily secured so normal users can't use this CUD API). I can have the same standard method for updating my version of CCP's data.
CCP has a lot of extra data in its Database dump (read: I don't use every column from every table). When I felt the need to import one of those I had to mess about with SQL files. Evedb.info was not using migrations! The SQL files were too big to be under source control. And a better solution was needed.
Enter: Evedb::Updater.
I wrote a class today to use the libcurl libraries for Ruby since the native Ruby Net::HTTP libraries were giving me trouble with the encoding of POST data. The general structure of the class I wrote is the following:
- Initalise the environment (Create a Postgres connection, Curl object, and store the base URL)
- Get a list of all of the IDs of the current model I'm dealing with (custom controller method all_ids since I have index paginated)
- Process an SQL query designed to do the schema change (
SQL:
-
SELECT agentid AS id FROM agtAgents
) for the specific model. This results in a list of things to create, update and destroy
-
- Do the deletions
- Do the creates
- Do the updates
It was a fun bit of code to write! The highlight is probably this bit:
-
module Hash
-
def to_curl
-
[] if self.keys.empty?
-
ret = []
-
self.keys.each do |k|
-
self[k].each do |v,l|
-
ret <<Curl::PostField.content("#{k.to_s}[#{v.to_s}]",l.to_s)
-
end
-
end
-
ret
-
end
-
end
The hash is generated by
-
0.upto(sql_results.num_tuples - 1) do |i|
-
m = { type => {} }
-
sql_results.fields.each do |field|
-
m[type].merge!( { field.to_sym => sql_results.getvalue(i,sql_results.fieldnum(field)).rstrip.fixmscrap.gsub(/'/,'\\\\\'') } )
-
end
The result of the to_curl method is to make the POST data that how we rails folk like it! (agent[name]=foo).
One downside to this is that some models need to query the Rails database (not the CCP one) for information to use. I might be better off modifying those specific models to use virtual attributes and some clever use of before_create!
