Goal: Low level read-only public access to query a large complex MySQL database and return JSON(P)/XML results.
Intro: The following outlines some stuff specific to my own personal open- source project (yet to be built). It should give this discussion some direction... but more generalised answers are of course very welcome. Why?: Technical audience, looking for low level control over results... coding, documenting and maintaining a fully featured API would be to much work for me alone. I plan to build a frontend app that lets people test, share, favourite and vote on queries they are using... it's kind of an experiment in building a community driven API to this database. Environment: A large MySQL database (GB's), Ruby on Rails, hosted on Engine Yard. Database remains *static* for 24 hours - i.e. an incremental import is run once daily. Large static database to be kept separate from the main Rails application database - imports are taken from a 3rd party and this database is only intended for data querying. The Obvious: Strict limitations need to be put in place to avoid exploits and to prevent the whole thing from crashing and burning if the API gains popularity. Knocking Heads: Additional ideas and any technical advice/tips on implementing the following would be warmly welcomed... 1. Enforce read-only access to the database - no matter what's thrown at it! 2. Fail elegantly on syntactically incorrect SQL 3. Terminate & clean up any query's running for more then 10 seconds - a strict timeout. 4. Batch/Limit results to sets of 1000 5. Caching which takes advantage of the database being static for 24hrs 6. Speed - avoid any Rails bottlenecks Me: I'm punching above my skill level with this project... so I expect to do a lot of research, reading and learning. PHP background. Thinking of looking behind the scenes of phpMyAdmin and rbDB (http://github.com/ redox/rbdb) for some insights into DB interfaces to begin with. I like a challenge ^_^ Outro: If you think this is nuts I would love to hear your thoughts on an alternative approach... constructive criticism plz. OMG you made it this far! Thanks for reading! - Chris -- You received this message because you are subscribed to the Google Groups "Ruby or Rails Oceania" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rails-oceania?hl=en.
