{
 "metadata": {
  "language": "ruby",
  "name": "",
  "signature": "sha256:72a863652bd208a5456fd3117515df8f4a741560bebd22165b85eb5c1ee8f089"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "ActiveRecord"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Zanim przyst\u0105pimy do pracy, musimy skonfigurowa\u0107 baz\u0119 danych. Poniewa\u017c pracujemy bez u\u017cycia Rails\u00f3w, konieczne jest\n",
      "r\u0119czne zestawienie po\u0142\u0105czenia oraz stworzenie odpowiednich tabel w bazie. Zadania te realizowane s\u0105 przez\n",
      "skrypt `db_setup.rb`. Aby go uruchomi\u0107 wpisujemy\n",
      "```ruby\n",
      "$:.unshift \".\"\n",
      "require 'db_setup'\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 13
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "W dalszych zadaniach b\u0119dzie wykonywa\u0107 polecenia korzystaj\u0105c z nast\u0119puj\u0105cych klas, zmapowanych na odpowiadaj\u0105ce im \n",
      "table w bazie danych:\n",
      "```ruby\n",
      "class Author < ActiveRecord::Base\n",
      "  # name      (string)\n",
      "  # surname   (string)\n",
      "  # born      (datetime) \n",
      "  # died      (datetime)\n",
      "  # image_url (string)\n",
      "  \n",
      "  has_many :books\n",
      "end\n",
      "\n",
      "class Book < ActiveRecord::Base\n",
      "  # title     (string)\n",
      "  # language  (string)\n",
      "  # author    (Author)\n",
      "  # published (integer)\n",
      "\n",
      "  belongs_to :author\n",
      "  has_and_belongs_to_many :genres\n",
      "end\n",
      "\n",
      "class Genre < ActiveRecord::Base\n",
      "  # name  (string)\n",
      "  \n",
      "  has_and_belongs_to_many :books\n",
      "end\n",
      "```"
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "CRUD"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Cztery podstawowe operacje, kt\u00f3re wykonujemy na danych to\n",
      "* tworzenie - **C**reate\n",
      "* odczytywanie - **R**ead\n",
      "* modyfikowanie - **U**pdate\n",
      "* usuwanie - **D**elete\n",
      "\n",
      "W skr\u00f3cie oznaczane s\u0105 one za pomoc\u0105 akronimu CRUD."
     ]
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Create"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "W ActiveRecord (w skr\u00f3cie AR) korzystamy z obiektowego interfejsu. \n",
      "Tworzenie danych wygl\u0105da nast\u0119puj\u0105co:\n",
      "```ruby\n",
      "author = Author.new(name: \"Adam\", surname: \"Mickiewicz\")\n",
      "author.save\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Innymi s\u0142owy tworzymy nowy obiekt Rubiego i wywo\u0142ujemy na nim metod\u0119 `save`. To \u017ce faktycznie zosta\u0142 on dodany do bazy danych \n",
      "mo\u017cemy zweryfikowa\u0107 wyszukuj\u0105c pierwszy obiekt w bazie:\n",
      "```ruby\n",
      "author = Author.first\n",
      "puts author.name\n",
      "puts author.surname\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 15
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Zadanie 1"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Dodaj do bazy 3 autor\u00f3w:\n",
      "* Juliusz S\u0142owacki\n",
      "* Henryk Sienkiewicz\n",
      "* Eliza Orzeszkowa"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Read"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Odczytywanie danych z bazy mo\u017cna realizowa\u0107 na wiele sposob\u00f3w. Najprostszy spos\u00f3b, to wyszukiwanie ich z wykorzystaniem \n",
      "klucza g\u0142\u00f3wego - `id`. S\u0142u\u017cy do tego metoda `find`:\n",
      "```ruby\n",
      "author = Author.find(1)\n",
      "puts author.surname\n",
      "author = Author.find(2)\n",
      "puts author.surname\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 17
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Wykorzystanie tej metody mo\u017ce jedak skutkowa\u0107 wyj\u0105tkiem, je\u015bli w bazie nie ma wiersza z danym kluczem:\n",
      "```ruby\n",
      "author = Author.find(10)\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Mo\u017cemy zabezpieczy\u0107 si\u0119 przed t\u0105 sytuacj\u0105, korzystaj\u0105 z innego wywo\u0142ania `find_by_id`\n",
      "```ruby\n",
      "author = Author.find_by_id(10)\n",
      "p author\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Zadanie 2"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Pr\u00f3ba odczytania p\u00f3l takiego obiektu, r\u00f3wnie\u017c sko\u0144czy si\u0119 wyj\u0105tkiem. Co nale\u017cy zrobi\u0107, \u017ceby wypisa\u0107 imi\u0119 i nazwisko autora\n",
      "wy\u0142\u0105cznie wtedy gdy autor istnieje w bazie? Zaimplementuj metod\u0119 `print_author`, kt\u00f3ra radzi sobie z tym problemem."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "def print_author(id)\n",
      "end\n",
      "\n",
      "# te linijki maj\u0105 pozosta\u0107 niezmienione\n",
      "print_author(1)\n",
      "print_author(10)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 19
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Update"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Modyfikowanie danych realizowane mo\u017ce by\u0107 na kilka sposob\u00f3w. W pierwszej kolejno\u015bci mo\u017cemy zmodyfikowa\u0107 atrybut obiektu i \n",
      "nast\u0119pnie zapisa\u0107 go do bazy\n",
      "```ruby\n",
      "author = Author.find(1)\n",
      "puts author.surname\n",
      "author.surname = \"Mickiewiczowski\"\n",
      "author.save\n",
      "\n",
      "other_author = Author.find(1)\n",
      "puts other_author.surname\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 21
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Mo\u017cna r\u00f3wnie\u017c skorzysta\u0107 z metody `update_attributes`, kt\u00f3ra dzia\u0142a podobnie jak konstruktor, ale dane s\u0105 od razu\n",
      "modyfikowane w bazie\n",
      "```ruby\n",
      "author = Author.find(1)\n",
      "author.update_attributes(name: \"Wojciech\")\n",
      "other_author = Author.find(1)\n",
      "puts other_author.name\n",
      "```"
     ]
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Zadanie 3"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Zmodyfikuj wszystkich autor\u00f3w, tak by ich daty urodzenia i \u015bmierci by\u0142y poprawne. Popraw r\u00f3wnie\u017c imi\u0119 i nazwisko Adama Mickiewicza.\n",
      "Aby wprowadzi\u0107 dat\u0119 skorzystaj z metody `Date.parse`."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 34
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Delete"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Usuwanie danych realizowane jest za pomoc\u0105 wywo\u0142ania `destroy`:\n",
      "```ruby\n",
      "author = Author.find(1)\n",
      "author.destroy\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Zadanie 4"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Poniewa\u017c w\u0142a\u015bnie usun\u0105\u0142e\u015b/\u0119\u0142a\u015b Adama Mickiewicza, ponownie utw\u00f3rz odpowiadaj\u0105cy mu rekord."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "J\u0119zyk zapyta\u0144"
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "`find`, `first`, `last`, `all`"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Metoda `find` pozwala nie tylko pobiera\u0107 pojedynczy obiekt z bazy, ale r\u00f3wnie\u017c kilka obiekt\u00f3w na raz:\n",
      "```ruby\n",
      "authors = Author.find(2,3,4)\n",
      "authors.each do |author|\n",
      "  puts \"#{author.name} #{author.surname}\"\n",
      "end\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Metody `first` oraz `last` zwracaj\u0105 odpowiednio *pierwszy* i *ostatni* rekord w bazie. W domy\u015blnej konfiguracji kolejno\u015b\u0107\n",
      "ta b\u0119dzie odpowiada\u0142a czasowi ich utworzenia.\n",
      "```ruby\n",
      "puts Author.first.surname\n",
      "puts Author.last.surname\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Natomiast metoda `all` zwraca kolekcj\u0119 obejmuj\u0105c\u0105 wszystkie rekordy w bazie danych:\n",
      "```ruby\n",
      "authors = Author.all\n",
      "authors.each do |author|\n",
      "  puts \"#{author.name} #{author.surname}\"\n",
      "end\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Zadanie 5"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Wypisz wszystkich autor\u00f3w znajduj\u0105cych si\u0119\u00a0w bazie wraz z ich datami urodzenia i \u015bmierci. Postaraj si\u0119 sformatowa\u0107 daty,\n",
      "tak by obejmowa\u0142y tylko dzie\u0144, miesi\u0105c i rok - w tej kolejno\u015bci. S\u0142u\u017cy do tego metoda `strftime`."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 39
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "`find_by`"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "AR definiuje r\u00f3wnie\u017c metody pozwalaj\u0105ce na wyszukiwanie rekord\u00f3w na podstawie warto\u015bci atrybut\u00f3w. Najprostsza z nich to `find_by`. Zwraca rekord, kt\u00f3re posiada warto\u015b\u0107 okre\u015blon\u0105 w zapytaniu:\n",
      "```ruby\n",
      "author = Author.find_by_name(\"Adam\")\n",
      "puts author.surname\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 41
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "`where`"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Metoda `where` odpowiada klauzuli `where` z j\u0119zyka SQL. Podstawowa r\u00f3\u017cnica polega na tym, \u017ce warto\u015bci poszczeg\u00f3lnych p\u00f3l okre\u015blamy w postaci par klucz-warto\u015b\u0107. Je\u015bli chcemy uzyska\u0107 pojedynczy wynik dodajemy metod\u0119 `first` lub `last`:\n",
      "```ruby\n",
      "author = Author.where(name: \"Eliza\").first\n",
      "puts author.surname\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Metoda ta ma jednak znacznie wi\u0119ksze mo\u017cliwo\u015bci - mo\u017cna np. podawa\u0107 zakresy warto\u015bci, jako zakresy Rubiego:\n",
      "```ruby\n",
      "authors = Author.where(born: (Date.parse(\"1780-1-1\")..Date.parse(\"1800-12-31\"))\n",
      "authors.each do |author|\n",
      "  puts \"#{author.name} #{author.surname} #{author.born.strftime(\"%d-%m-%Y\")}\"\n",
      "end\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 45
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Metod\u0119 `where` mo\u017cna wywo\u0142ywa\u0107 wielokrotnie. Wtedy wyniki s\u0105 \u0142\u0105czone za pomoc\u0105 operatora koniunkcji. Je\u015bli chcemy\n",
      "u\u017cy\u0107 innego operatora (np. `OR` lub `LIKE`), konieczne jest u\u017cycie nieco innej sk\u0142adni:\n",
      "```ruby\n",
      "authors = Author.where(\"name LIKE 'A%'\")\n",
      "authors.each do |author|\n",
      "  puts author.surname\n",
      "end\n",
      "```  "
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 47
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Je\u015bli dane w napisie przekazanym do metody `where` pochodz\u0105 od u\u017cytkownika aplikacji, to nara\u017camy si\u0119 na atak SQL-injection.\n",
      "Aby go unik\u0105\u0107, warto\u015b\u0107 podan\u0105 przez u\u017cytkownika przekazujemy jako osobny argument, np.\n",
      "```ruby\n",
      "name = \"Adam\"\n",
      "authors = Author.where(\"name = ?\",name)\n",
      "authors.each do |author|\n",
      "  puts author.surname\n",
      "end\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "W tej sytuacji AR sam zadba o odpowiedni\u0105 konwersj\u0119 znak\u00f3w \"niebezpiecznych\"."
     ]
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Zadanie 6"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Znajd\u017a i wypisz wszystkich autor\u00f3w, kt\u00f3rzy zmarli mi\u0119dzy rokiem 1800 a 1900."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "`order`"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Do okre\u015blania kolejno\u015bci wynik\u00f3w s\u0142u\u017cy metoda `order`. Dzia\u0142a ona analogicznie do klauzuli `ORDER` w j\u0119zyku SQL.\n",
      "```ruby\n",
      "authors = Author.order(:born)\n",
      "authors.each do |author|\n",
      "  puts \"#{author.name} #{author.surname} #{author.born}\"\n",
      "end\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 51
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Metoda ta cz\u0119sto jest \u0142\u0105czona z wywo\u0142aniami `first` i `last`\n",
      "```ruby\n",
      "author = Author.order(:born).last\n",
      "puts \"#{author.name} #{author.surname} #{author.born}\"\n",
      "```"
     ]
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Zadanie 7"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Znajd\u017a autora, kt\u00f3ry zmar\u0142 jako ostatni."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "`limit` i `offset`"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Metody `limit` i `offset` dzia\u0142aj\u0105 analogicznie jak ich odpowiedniki w SQL:\n",
      "```ruby\n",
      "Author.limit(2).each do |author|\n",
      "  puts \"#{author.name} #{author.surname} #{author.born}\"\n",
      "end\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "```ruby\n",
      "Author.offset(2).each do |author|\n",
      "  puts \"#{author.name} #{author.surname} #{author.born}\"\n",
      "end\n",
      "```"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 55
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Warto jednak pami\u0119ta\u0107, \u017ceby stosuj\u0105c je okre\u015bli\u0107 po\u017c\u0105dek rekord\u00f3w."
     ]
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "Zadanie 8"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Znajd\u017a i wypisz 3 autor\u00f3w, kt\u00f3rzy zmarli jako pierwsi."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}