building a data access layer for your data using sinatra ... · building a data access layer for...
TRANSCRIPT
![Page 1: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/1.jpg)
Building a Data Access Layer for Your Data Using
Sinatra + Ruby + MySQL
Matt YonkovitPercona Live London 2011
![Page 2: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/2.jpg)
www.percona.com
The dream of developing fast, efficient, applications without detailed knowledge of the
database....
![Page 3: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/3.jpg)
www.percona.com
Way Back Machine
● Old school database design● Business logic goes into stored procedures● Developers do not touch the tables directly● Many applications can access the same procs,
reusing code● Applications can be somewhat modular
![Page 4: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/4.jpg)
www.percona.com
The rise of ORM's
● Object Relational Mapping tools are still very popular● They hide the complexity of the database● They make data interactions seem intuitive in code● They generate sql for you
● They have issues● Generated SQL is often less then optimal● Many times models are not shared across
applications
![Page 5: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/5.jpg)
www.percona.com
Development Today
● Many people have the following already● Web Services● A web front-end/ui● Make heavy use of Javascript● Have lots of data to display● Have more then 1 application using the same
database
![Page 6: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/6.jpg)
ComplexEnvironments
Database
Appwith Direct
SQL
Modelviewcontroller
MVC APP (+ORM)
Public FacingAPI
BatchJobs
● Database changes cascade to multiple applications●Business logic built for 1 application often is duplicated or ignored in another application
![Page 7: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/7.jpg)
Modular design
Database
Appwith Direct
SQL
Modelviewcontroller
MVC APP (+ORM)
BatchJobs
API
BatchJobs
ExternalApps
![Page 8: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/8.jpg)
www.percona.com
What is Sinatra?
A really tiny web framework for building apps in Ruby.
Read all about it:http://www.sinatrarb.com/
http://sinatra-book.gittr.com/
![Page 9: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/9.jpg)
www.percona.com
How To Get started
● Linux Machine● RVM (Ruby Version Manager)
http://beginrescueend.com/● Ruby installed ( any version is ok, I prefer 1.9.2 )● Your favorite database driver/connection library
– Mysql gem, mysql2 gem, active record, sequel, etc.● Other gems depending on your needs:
– Xmlsimple– Json– etc
![Page 10: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/10.jpg)
www.percona.com
Hello World
>>ruby Api.rb -p 9000
How do you run it?
require 'rubygems'require 'sinatra'
get '/' do "Hello world, it's #{Time.now} at the server!"end
Api.rb
Point your web browser to localhost:9000
Thats it!
![Page 11: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/11.jpg)
www.percona.com
Demo of Hello World
![Page 12: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/12.jpg)
www.percona.com
Returning MySQL Data
require 'rubygems'require 'mysql2'require 'sinatra'
@@mysqlclient = Mysql2::Client.new(:host => "localhost", :username => "root", :database => "information_schema")
get '/' do "Hello world, it's #{Time.now} at the server!"end
get '/tables/' do res = Array.new result = @@mysqlclient.query("SELECT * FROM tables",:as => :array) result.each do | row | res.push(row) end return res.to_send
![Page 13: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/13.jpg)
www.percona.com
Demo: Getting MySQL Data Back
![Page 14: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/14.jpg)
www.percona.com
Can do the Same Thing With AR
require 'rubygems'require 'mysql2'require 'active_record'require 'sinatra'
get '/' do "Hello world, it's #{Time.now} at the server!"end
get '/tables/' do MyAPI.get_tables()end
class MyAPI < ActiveRecord::Base class << self
dbconfig = { :adapter => "mysql2", :host => "localhost", :username => "root", :password => "", :database => "information_schema" } ActiveRecord::Base.establish_connection(dbconfig)
def get_tables() query='select * from tables'; self.connection.select_all(query).to_s end
endend
![Page 15: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/15.jpg)
www.percona.com
Demo: Getting MySQL Data Back
![Page 16: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/16.jpg)
www.percona.com
Format the Data!
require 'rubygems'require 'mysql2'require 'active_record'require 'sinatra'require 'csv'require 'sinatra/respond_to'require 'json'require 'xmlsimple'
Sinatra::Application.register Sinatra::RespondTo
get '/' do "Hello world, it's #{Time.now} at the server!"end
get '/tables' do format_response MyAPI.get_tables()end
def format_response (package) respond_to do |wants| wants.txt { package.to_s } wants.csv { package.to_csv } wants.xml { package.to_xml } wants.json { package.to_json } end end
class MyAPI < ActiveRecord::Base class << self
dbconfig = { :adapter => "mysql2", :host => "localhost", :username => "root", :password => "", :database => "information_schema" } ActiveRecord::Base.establish_connection(dbconfig)
def get_tables() query='select * from tables'; self.connection.select_all(query) end
endend
![Page 17: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/17.jpg)
www.percona.com
Demo: Json/XML Formatting!
![Page 18: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/18.jpg)
Filtering Data
get '/tables/?:schema?/?:table?' do format_response MyAPI.get_tables(params)end
def get_tables(params) where = "where 1=1" if ! params[:schema].nil? where = "#{where} and TABLE_SCHEMA = '#{params[:schema]}'" end if ! params[:table].nil? where = "#{where} and TABLE_NAME = '#{params[:table]}'" end query="select * from tables #{where}"; if params[:no_header]
self.connection.select_rows(query) else self.connection.select_all(query) endend
![Page 19: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/19.jpg)
www.percona.com
Demo: Data Filtering!
![Page 20: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/20.jpg)
www.percona.com
Thats All Great and all, But Why is that any Easier?
![Page 21: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/21.jpg)
www.percona.com
Consuming the API
● JSON can be consumed and used natively in many different programing languages
● You can build very feature rich Javascript only applications
● Many popular javascript graphing libraries can easily consume and graph data passed back from the API
![Page 22: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/22.jpg)
www.percona.com
Demo: Putting It Together
● Javascript application that consumes the api● http://datatables.net/● http://www.highcharts.com
![Page 23: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/23.jpg)
www.percona.com
Thank You to Our Sponsors
Platinum Sponsor
Gold Sponsor
Silver Sponsors
![Page 24: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/24.jpg)
www.percona.com
Percona Live London Sponsors
Exhibitor Sponsors
Friends of Percona Sponsors
Media Sponsors
![Page 25: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/25.jpg)
www.percona.com
Annual MySQL Users Conference Presented by Percona Live
The Hyatt Regency Hotel, Santa Clara, CA
April 10th-12th, 2012
Featured SpeakersMark Callaghan, Facebook
Jeremy Zawodny, Craigslist
Marten Mickos, Eucalyptus Systems
Sarah Novotny, Blue Gecko
Peter Zaitsev, Percona
Baron Schwartz, Percona
The Call for Papers is Now Open!
Visit www.percona.com/live/mysql-conference-2012/
![Page 27: Building a Data Access Layer for Your Data Using Sinatra ... · Building a Data Access Layer for Your Data Using Sinatra + Ruby + MySQL Matt Yonkovit Percona Live London 2011](https://reader031.vdocuments.us/reader031/viewer/2022021505/5affc5537f8b9a256b8f0218/html5/thumbnails/27.jpg)
www.percona.com/live