Today CCP announced the release of their MS SQL dump. Following in step with past conversions here is a Postgres version. Tested with 8.0.15 but should work on everything.
This dump is odd: CCP is using integers where they mean to use booleans. So in those cases remember 1 is true, 0 is false. Good luck.
Edit: As a note the Eve Online Database Viewer is updated with this dump.
Edit 2 (July 31, 2008): I nearly forgot to give props to bunjiboys for providing the .sql files from which the above dump is derrived.
As I’ve done for previous Eve Online database dumps here’s a Postgres (8.1) dump for Trinity. Hosted at http://www.eve-files.com and can be downloaded by clicking on right here
Included in the zip archive is a Schema file as well as each individual table as a .sql file. They’re numbered 001 to 065. 001 is the schema (001_schema.sql) and is intended to be imported first. The others have no real order as there are no foreign key constraints but were numbered for my testing and sanity.
View the dump online
I don’t like it but I’ll do it. 
I rewrote my Eve Online Database Viewer. With the recent Trinity Dump (Postgres version coming soon) I wanted to rewrite the PHP script in order to better make use of mod_rewrite and to better handle errors.
Just finished a Postgres port of the latest Eve Revelations database dump (as of the 2.2 patch). It can be downloaded from eve-files and can be browsed online at http://lisa.thedoh.com/projects/EO/?db=Rev22
I hope it’s useful for everyone.
In my evedb.info efforts I need a quick way to expire old market orders. The CSV has a duration column that is an integer number of days. Postgres allows its users to typecast between various data types in SQL so there doesn't need to be a SELECT duration FROM ... ; (in code) to_delete = Time.now + duration.days ; DELETE FROM ... WHERE duration < to_delete;
It just so happens that the form for casting an integer to do math with a timestamp is a bit funky. An integer may not be directly added to a timestamp. The magic is:
SQL:
-
DELETE
-
FROM foo
-
WHERE now()> (created_on + CAST( (duration || ' days') AS INTERVAL));
The || ' days' I don't understand just yet but it works!
Following up to this post of mine. The schema can be found here and the full thing (including Schema) can be found here.
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:
RUBY:
-
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
RUBY:
-
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!
Last night I got my hands on the latest db dump and converted it to a Postgres format. Currently the "online version" (basically a PHP script to sort all the info!) is online for browsing for those who may be interested.
I think I'll pg_dump and pop the resulting SQL on Eve files so others can have it.
