Tweet
Tweet

Slide 1

Slide 1 text

Intro to Databases Rails Bridge Philadelphia December 2016

Slide 2

Slide 2 text

Ernesto Tagwerker @etagwerker

Slide 3

Slide 3 text

Spreadsheets

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

From spreadsheets to databases

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

No content

Slide 8

Slide 8 text

No content

Slide 9

Slide 9 text

Primary Keys

Slide 10

Slide 10 text

Primary Keys

Slide 11

Slide 11 text

No content

Slide 12

Slide 12 text

Relational Databases

Slide 13

Slide 13 text

Relations (Tables)

Slide 14

Slide 14 text

Relational Database Management Systems (DBMS)

Slide 15

Slide 15 text

MySQL Postgres SQLite* MS SQL Server and more … DBMS Alternatives

Slide 16

Slide 16 text

Databases in Rails

Slide 17

Slide 17 text

$ rails new --help # database # (options: mysql/oracle/postgresql/sqlite3/ frontbase/ibm_db/sqlserver/jdbcmysql/ jdbcsqlite3/jdbcpostgresql/jdbc) $ rails new polls —database=postgresql $ rails new polls # defaults to sqlite3

Slide 18

Slide 18 text

Where is the database?

Slide 19

Slide 19 text

SQL (for data) • Create • Retrieve • Update • Delete

Slide 20

Slide 20 text

Rails Migrations # db/migrate/20161124163416_create_topics.rb class CreateTopics < ActiveRecord::Migration def change create_table :topics do |t| t.string :title t.text :description t.timestamps null: false end end end

Slide 21

Slide 21 text

Rails Migrations $ rake db:migrate == 20161124163416 CreateTopics: migrating ===================================== -- create_table(:topics) -> 0.0496s == 20161124163416 CreateTopics: migrated (0.0497s) ============================

Slide 22

Slide 22 text

Rails Migrations CREATE TABLE `topics` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) DEFAULT NULL, `description` text, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`) )

Slide 23

Slide 23 text

SQL (for table definitions) • Create table • Modify table • Drop table

Slide 24

Slide 24 text

Models # app/models/vote.rb class Vote < ActiveRecord::Base belongs_to :user belongs_to :topic end # app/models/topic.rb class Topic < ActiveRecord::Base has_many :votes end # app/models/user.rb class User < ActiveRecord::Base has_many :votes end

Slide 25

Slide 25 text

Models (create) $ rails console > Topic.create(title: 'Intro to Database', description: 'This is awesome!') SQL (1.4ms) INSERT INTO `topics` (`title`, `description`, `created_at`, `updated_at`) VALUES ('Intro to Database', 'This is awesome!', '2016-11-24 16:57:13', '2016-11-24 16:57:13') => #

Slide 26

Slide 26 text

Models (retrieve) > topic = Topic.find 1 Topic Load (1.6ms) SELECT `topics`.* FROM `topics` WHERE `topics`.`id` = 1 LIMIT 1 => #

Slide 27

Slide 27 text

No content

Slide 28

Slide 28 text

Models (update) > topic.title = "Intro to Databases" => "Intro to Databases" > topic.save SQL (2.3ms) UPDATE `topics` SET `title` = 'Intro to Databases', `updated_at` = '2016-11-24 17:00:47' WHERE `topics`.`id` = 1 => true

Slide 29

Slide 29 text

Models (destroy) > topic.delete SQL (10.0ms) DELETE FROM `topics` WHERE `topics`.`id` = 1 => #

Slide 30

Slide 30 text

Models (count) > Topic.count (2.1ms) SELECT COUNT(*) FROM `topics` => 0

Slide 31

Slide 31 text

How to use models

Slide 32

Slide 32 text

Topic.find 123 Topic.first Topic.last Topic.count To interact with tables

Slide 33

Slide 33 text

topic = Topic.find 123 topic.title = “New title” topic.save To modify records

Slide 34

Slide 34 text

Thank you!

Slide 35

Slide 35 text

Questions?