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.
In reference to the post about My First Patch to the Rails trac I mentioned in the edit of the post that I emailed the maintainer. There’s been no response, unfortunately.
So I’ll post the patch here so it’ll get a bit more visibility and hopefully Oracle users can find it helpful in speeding up rake db:migrate on Oracle systems with a lot of indicies.
Patch to speed up Rails’s OracleAdapter#indexes method
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
With Rails there's a useful set of callback methods that can be used within model classes. They include after_create, before_validation and the one I want to talk about after_initialize.
A lot of cool things can be done with after_initialize, especially if one uses the database as a means to store meta-data about a model. Use the after_initialize method to transform your models with Ruby after they're fethced from the database. Consider the following User model definition:
RUBY:
-
class User <ActiveRecord::Base
-
has_many :memberships,
rder =>
"memberships.group_id, memberships.expires_at desc", :group =>
"group_id"
-
end
It's pretty clear that with the :group => "group_id" that I want a unique set of Membership objects. That works well when I've already got a User and do user.memberships but not at all when I do User.find(:first, :include => :memberships); likely because of the way the join is set up and grouping on that may not be possible.
I still wanted to use eager loading so I thought that it would be an OK sacrifice to fetch all the Membership objects even if I wanted to pare it down after I initialize the User object (with eager loading). I defined:
RUBY:
-
class User <ActiveRecord::Base
-
-
def after_initialize
-
do stuff with @memberships instance varible
-
end
-
end
Except it doesn't work. Quickly I found the following from active_record/base.rb (click link for source): right where it does the after_initialize callback! But it wasn't working for me. I needed to dig further.
Further research indicated that before the creation of the @memberships instance variable that would hold the collection of Membership objects the after_initialize callback was being fired off in the User model. Using after_initialize would not work due to the design of ActiveRecord. I'm still searching for an elegant way to work the way I want. I'll post again when I find it!
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.
I've submitted my first patch to the Rails trac today to speed up the OracleAdapter#indexes method (used in migrations). A client at work uses Oracle and in the production environment this method was taking upwards of 45 seconds to run per table!One of their DBAs gave me the SQL there, so I can't take the credit, I just submitted it.
I hope it's accepted. Incidentally, it works and has saved me a lot of time with migrations.
Edit: Yeah, turns out that since turning these adapters into gems patches for them shouldn't be done through Rails trac. How irritating, but whatever. I've sent the patch do the current maintainer for review.
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.
(n.b., the version of Beast we use may not be the most current. This post may not reflect what's in trunk.)
One of our clients from work uses Beast forum. Unfortunately last week it kind of died. Mongrel's logs were complaining that it couldn't allocate memory, the index (ForumsController#index) was taking upwards of 12 seconds to load...
The problem was SQL. Beast is very greedy when it displays the front page; it does a forums.posts.last.created_at to get the timestamp for when the last post was made. This is done for every top-level forum and it will destroy your site.
At about 21:30ish the previous night I disabled the site because my brain was mush and I couldn't focus enough to diagnose the problem to say nothing of finding a solution. The next day when I got to the office I spent the first 90 minutes of the day diagnosing and fixing the problem. I found that I could find the relevant data with a GROUP BY and ORDER BY with one query for all forums... Except the times were the FIRST post and not the LAST post.
So I spent the next two hours figuring out why MySQL didn't want to ORDER BY and formulating a query that was both quick, returned data for every forum and worked. This is the ugly thing that resulted:
SQL:
-
-- moderator_only is assigned from clean data in the controller; it is safe.
-
SELECT posts.id, posts.created_at, posts.forum_id, posts.topic_id, users.login
-
FROM posts
-
INNER JOIN (SELECT MAX(id) AS id FROM posts GROUP BY forum_id) ids ON
-
posts.id = ids.id
-
LEFT OUTER JOIN users
-
ON users.id = posts.user_id
-
LEFT OUTER JOIN forums
-
ON forums.id = posts.forum_id
-
WHERE
-
(forums.moderator_only = 0 OR forums.moderator_only = #{moderator_only}) AND forums.parent_id is null
-
ORDER BY posts.created_at
This is fed into:
RUBY:
-
last_posts = Post.find_by_sql "...."
-
@last_posts = last_posts.group_by &:forum_id
In the view we just substitute forum.posts.last with @last_posts[forum.id].first in all instances. This may have to bet tweaked for your setup because I'm not sure where Beast ends and client-specific stuff begins.
You see there's some parent_id things going on in the SQL. At work we made Beast forum nested. We do plan to make these changes available for others to use but it's just a matter of finding time to get rid of the client-specific modifications and merging to trunk.
This is one of the things that I'm proud about: the SQL query was a pain!
Whenever I need to make a change to data in a Rails-based table I very rarely use a GUI, in fact, the only time I have used a GUI is for Oracle (and we all know how I feel about that). Instead I favour the rails console.
For me I find it easier to use the console because it reinforces the ORM - an entry in the table is just an instance of a class that models that table. I find that Constellation.find(:all, :conditions => "faction_id = 0").each { |c| c.faction_id = nil ; c.save! } is more meaningful (and easier) than sorting by the value of that column and mass updating. Icky
When I need to construct weird SQL queries, however, I'll drop to the mysql or psql shells. GUIs are icky and their bulky interfaces tend to slow complex queries down. Of course, I'm a console geek so I'm slightly biased here.
Anyways, I cringe a little when I see people opening SQL GUIs to find table structure or to find data values and to change single values. But hey, different strokes for different folks!
At work I've been struggling with making one of our projects play nice with Oracle. I got Oracle XE installed through Windows XP in Parallels and just couldn't make my tests work. I kept getting errors that Rails couln't insert a nil value into a non-nil field (id).
What this eventually boiled down to is Rails fixtures MUST have an id attribute on them or you may run into errors! This was hidden behind the scenes since heretofore we were using auto_incrementing MySQL fields. To get this behaviour in Oracle one must do
CODE:
-
select #{table_name}_seq.nextval id from dual
(done by Rails automatically, normally) or set up a Trigger (not done by rails, and would have to be done in a create_table migration).
However, fixtures bypass the normal ActiveRecord::Base#create call and just does a raw execute insert into... Which means there's no value for id, unless it's specified in the fixture. This kept me at it for hours today and I only found it by hacking the fixtures source to make it print in the logs what it was doing!
Let this be a lesson! Always put in ids even if you don't care about their values.
