Rails and Legacy Databases - RailsConf 2009

Editor's Notes

1. So the main thing I want you to take away from this talk is...
4. The point of this talk is to show you that there are ways to work with legacy systems, but that you really, should try to work around them.
5. How many people here are new to Rails? How many people have to work with legacy databases? How many people want to learn how to work with legacy?
6. This is a topic that’s close to me because I work days at a university that uses Oracle and SQL Server databases. In order to use Rails, there were a lot of things I had to figure out.
7. A legacy database could be anything that existed before Rails, like a wordpress blog or a custom project management app.
8. It’s important to note that these aren’t Rails limitations, but rather ActiveRecord limitations.
9. So, we’re going to talk about legacy schemas and Rails. Things like bad table names, non-incrementing primary keys, stored procedures, compound keys
10. We’ll look at some simple fixes first, like overriding table names and primary keys...
11. and then look at some other ways we can make our code easier to read and write while still leveraging the power of Rails
13. ActiveRecord provides mechanisms to handle some simple cases for us.
17. Rails’ alias_attribute method is a macro for redefining the method at runtime. However, it only works with fieldnames that don’t violate Rails’ conventions.
18. Sometimes the stuff that ActiveRecord gives you just isn’t enough.
19. Some databases (SQL SERVER) let you have column names like this. Soaces, dashes, mixed case.
20. Views to the rescue. MySQL, Microsoft SQL Server, and Oracle support inserting into views too, so your Rails database adapter will never know.
21. Can anyone give me examples of non incrementing keys? Some good examples?
22. Not always possible if there’s a certain level of beaurocracy in your institution. Some people also just think meaningful keys are the most bestest idea.
23. This example is BAD BAD BAD. If you don’t know why, then please close your laptop and go sit in the corner.
24. Let’s take a look at using Rails with an existing wordpress database.
25. This is incredibly common. One of our databases joins 5 columns for course records.
30. Wordpress and Rails can work well together.
33. Some public directory information from a university. It’s fed from a Unisys mainframe on a nightly basis using flatfiles.
35. The feed also includes social security numbers. We have to protect those
37. Never forget that views are a security tool. Aside from the benefit they give us with regard to changing how data looks, we can use them to abstract availability of data. I *never* see these used in Rails.
38. In some databases you can use views to cut down complex join logic and still treat them like tables. MySQL doesn’t allow subselects, but MS SQL sure does.
39. Sometimes logic is encapsulated within the database.
42. This throws the results away, but it’s fine if you just want to call something that has no return value. Execute is good for calling any arbitrary SQL, but
43. This will get you a single return value from a procedure.
44. Results come back as an array of hashes, so you access them a little differently, but it’s still pretty nice. A little meta-magic and you can map these to properties of a new instance.
45. There is no escape!
46. Using Oracle? MySQL? PostgreSQL? Try DataMapper!
47. This declares properties and methods. This has lots of potential.
48. If you can declare your columns like you can with DataMapper or even with a view, you’ve solved the major problems.
50. Cygwin and other methods, but it’s known to be slow.
51. A call for help. I know virtually nothing about DataMapper, but a lot about SQL Server. There’s been interest, but no volunteers yet. I’m tempted but I need help.
52. What are some strategies you can use to move from legacy systems?
53. If you’re going to move from one platform to another, you need to know what the differences between the platforms.
54. Each database manufacturer has different types.
56. NUMBER datatype can be used for floats and integers
57. DATE is used for times and dates.
58. NUMBER and CHAR - zero and one - for booleans.
59. Raimonds is a pretty swell guy, and he’s done some great work on this driver which makes working with Oracle very nice.
61. They’re deprecated anyway. If you have to use a database that uses this type, you have to watch out for statements that use equality.
62. This solves a lot of stuff. Different datatypes, date fixes, and excellent stored procedure support.
63. Legacy databases tend to have a lot of these. You need to write code to handle exceptions that your ORM classes might throw if a trigger causes constraints to be violated or if triggers start doing strange things.