ch5tut25.lyx

#LyX 2.0 created this file. For more info see http://www.lyx.org/
\lyxformat 413
\begin_document
\begin_header
\textclass hobo
\use_default_options true
\begin_modules
logicalmkup
\end_modules
\maintain_unincluded_children false
\language english
\language_package default
\inputencoding auto
\fontencoding global
\font_roman default
\font_sans default
\font_typewriter default
\font_default_family default
\use_non_tex_fonts false
\font_sc false
\font_osf false
\font_sf_scale 100
\font_tt_scale 100

\graphics default
\default_output_format default
\output_sync 0
\bibtex_command default
\index_command default
\float_placement H
\paperfontsize default
\spacing single
\use_hyperref true
\pdf_bookmarks true
\pdf_bookmarksnumbered false
\pdf_bookmarksopen false
\pdf_bookmarksopenlevel 1
\pdf_breaklinks false
\pdf_pdfborder false
\pdf_colorlinks true
\pdf_backref false
\pdf_pdfusetitle true
\papersize default
\use_geometry false
\use_amsmath 0
\use_esint 0
\use_mhchem 1
\use_mathdots 1
\cite_engine basic
\use_bibtopic false
\use_indices false
\paperorientation portrait
\suppress_date true
\use_refstyle 0
\boxbgcolor #e0e0e8
\index Index
\shortcut idx
\color #008000
\end_index
\secnumdepth -1
\tocdepth 2
\paragraph_separation skip
\defskip medskip
\quotes_language english
\papercolumns 1
\papersides 2
\paperpagestyle default
\tracking_changes false
\output_changes false
\html_math_output 0
\html_css_as_file 0
\html_be_strict false
\end_header

\begin_body

\begin_layout Section
Tutorial 25 
\begin_inset Newline newline
\end_inset

Using Hobo Database Index Generation
\end_layout

\begin_layout Standard
\begin_inset Flex Emph
status collapsed

\begin_layout Plain Layout
By Matt Jones
\end_layout

\end_inset


\end_layout

\begin_layout Standard
Defining effective indexes on your data can give massive database performance
 benefits in any application.
 To further this goal, Hobo’s migration generator attempts to provide useful
 indices without any additional code, and provides shorthand for defining
 indices.
\end_layout

\begin_layout Subsubsection*
The 
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
:index
\end_layout

\end_inset

 Option
\end_layout

\begin_layout Standard
Throughout the index generator API, the 
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
:index
\end_layout

\end_inset

 parameter is used to switch indexing on/off and specify an explicit name
 for an index.
 The convention is:
\end_layout

\begin_layout Itemize
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
:index => true
\end_layout

\end_inset

 will switch on indexing for a field not indexed by default; the name used
 is the default name generated by Rails.
\end_layout

\begin_layout Itemize
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
:index => false
\end_layout

\end_inset

 will switch off automatic indexing for a field.
\end_layout

\begin_layout Itemize
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
:index => ‘name’
\end_layout

\end_inset

 will specify a name for the generated index.
 Note that some databases require that index names be unique across the
 entire database, not just the individual table.
\end_layout

\begin_layout Standard
\begin_inset Box Shaded
position "t"
hor_pos "c"
has_inner_box 1
inner_pos "t"
use_parbox 0
use_makebox 0
width "100col%"
special "none"
height "1in"
height_special "totalheight"
status open

\begin_layout Description
Note: Oracle’s 30-character limit for entity names causes problems with
 the default naming scheme that Rails uses for indices.
 The Oracle driver for ActiveRecord attempts to mitigate this by shortening
 overlong index names in 
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
add_index
\end_layout

\end_inset

; unfortunately, this will break the generated down migrations (which rely
 on the original index names).
 The best short-term solution is to pass a manual index name parameter wherever
 possible.
\end_layout

\end_inset


\end_layout

\begin_layout Subsubsection*
Automatic Indexing
\end_layout

\begin_layout Standard
The 
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
belongs_to
\end_layout

\end_inset

 associations will automatically declare an index on their foreign key field;
 polymorphic 
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
belongs_to
\end_layout

\end_inset

 will declare a multi-field index on [
\begin_inset Flex Emph
status collapsed

\begin_layout Plain Layout
association_type
\end_layout

\end_inset

, 
\begin_inset Flex Emph
status collapsed

\begin_layout Plain Layout
foreign_key
\end_layout

\end_inset

].
 
\end_layout

\begin_layout Standard
\begin_inset Flex Emph
status collapsed

\begin_layout Plain Layout
Example:
\end_layout

\end_inset


\end_layout

\begin_layout Standard
\begin_inset Box Shadowbox
position "t"
hor_pos "c"
has_inner_box 1
inner_pos "t"
use_parbox 0
use_makebox 0
width "100col%"
special "none"
height "1in"
height_special "totalheight"
status open

\begin_layout Code

\size footnotesize
class SomeModel < ActiveRecord::Base
\end_layout

\begin_layout Code

\size footnotesize
  hobo_model
\end_layout

\begin_layout Code

\size footnotesize
  belongs_to :other_model
\end_layout

\begin_layout Code

\size footnotesize
  belongs_to :another_model, :index => ‘some_random_name’
\end_layout

\begin_layout Code

\size footnotesize
  belongs_to :fooable, :polymorphic => true
\end_layout

\begin_layout Code

\size footnotesize
end
\end_layout

\end_inset


\end_layout

\begin_layout Standard
Will generate the following in an up migration:
\end_layout

\begin_layout Standard
\begin_inset Box Shadowbox
position "t"
hor_pos "c"
has_inner_box 1
inner_pos "t"
use_parbox 0
use_makebox 0
width "100col%"
special "none"
height "1in"
height_special "totalheight"
status open

\begin_layout Code

\size footnotesize
add_index :some_models, :other_model_id
\end_layout

\begin_layout Code

\size footnotesize
add_index :some_models, :another_model_id, :name => ‘some_random_name’
\end_layout

\begin_layout Code

\size footnotesize
add_index :some_models, [:fooable_type, :fooable_id]
\end_layout

\end_inset


\end_layout

\begin_layout Standard
Lifecycle state fields will also be automatically indexed, as will the 
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
inheritance_column
\end_layout

\end_inset

 of an STI parent class.
\end_layout

\begin_layout Subsubsection*
Indexing in the ‘fields do’ block
\end_layout

\begin_layout Standard
Within the standard fields block, indexes can be declared as part of a field,
 just like the 
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
:required
\end_layout

\end_inset

 or 
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
:unique
\end_layout

\end_inset

 options.
 Fields that also have the 
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
:unique
\end_layout

\end_inset

 option will automatically declare a unique index.
\end_layout

\begin_layout Standard
\begin_inset Flex Emph
status collapsed

\begin_layout Plain Layout
Example:
\end_layout

\end_inset


\end_layout

\begin_layout Standard
\begin_inset Box Shadowbox
position "t"
hor_pos "c"
has_inner_box 1
inner_pos "t"
use_parbox 0
use_makebox 0
width "100col%"
special "none"
height "1in"
height_special "totalheight"
status open

\begin_layout Code

\size footnotesize
class SomeModel < ActiveRecord::Base
\end_layout

\begin_layout Code

\size footnotesize
  fields do
\end_layout

\begin_layout Code

\size footnotesize
    name :string, :index => true
\end_layout

\begin_layout Code

\size footnotesize
    unique_field :string, :unique, :index => ‘foo’
\end_layout

\begin_layout Code

\size footnotesize
  end
\end_layout

\begin_layout Code

\size footnotesize
end
\end_layout

\end_inset


\end_layout

\begin_layout Standard
Will generate the following in an up migration:
\end_layout

\begin_layout Standard
\begin_inset Box Shadowbox
position "t"
hor_pos "c"
has_inner_box 1
inner_pos "t"
use_parbox 0
use_makebox 0
width "100col%"
special "none"
height "1in"
height_special "totalheight"
status open

\begin_layout Code

\size footnotesize
add_index :some_models, :name
\end_layout

\begin_layout Code

\size footnotesize
add_index :some_models, :unique_field, :name => ‘foo’, :unique => true
\end_layout

\end_inset


\end_layout

\begin_layout Subsubsection*
Indexing in the model
\end_layout

\begin_layout Standard
More complicated indexes may need to be declared outside the fields block.
 For instance, specific slow-running SQL queries may benefit from a multi-field
 index.
 The index method provides a simple interface for specifying any type of
 index on the model.
\end_layout

\begin_layout Standard
\begin_inset Flex Emph
status collapsed

\begin_layout Plain Layout
Example:
\end_layout

\end_inset


\end_layout

\begin_layout Standard
\begin_inset Box Shadowbox
position "t"
hor_pos "c"
has_inner_box 1
inner_pos "t"
use_parbox 0
use_makebox 0
width "100col%"
special "none"
height "1in"
height_special "totalheight"
status open

\begin_layout Code

\size footnotesize
class SomeModel < ActiveRecord::Base
\end_layout

\begin_layout Code

\size footnotesize
  fields do
\end_layout

\begin_layout Code

\size footnotesize
    last_name :string
\end_layout

\begin_layout Code

\size footnotesize
    first_name :string
\end_layout

\begin_layout Code

\size footnotesize
  end
\end_layout

\begin_layout Code

\size footnotesize
  index [:last_name, :first_name]
\end_layout

\begin_layout Code

\size footnotesize
end
\end_layout

\end_inset


\end_layout

\begin_layout Standard
Will generate the following in an up migration:
\end_layout

\begin_layout Standard
\begin_inset Box Shadowbox
position "t"
hor_pos "c"
has_inner_box 1
inner_pos "t"
use_parbox 0
use_makebox 0
width "100col%"
special "none"
height "1in"
height_special "totalheight"
status open

\begin_layout Code

\size footnotesize
add_index :some_models, [:last_name, :first_name]
\end_layout

\end_inset


\end_layout

\begin_layout Standard
When declaring a multi-field index, the order is relevant - consult your
 database’s manual for more detail (for example, section 7.4.3 of the MySQL
 5.0 Reference).
\end_layout

\begin_layout Standard
The index method currently supports two options:
\end_layout

\begin_layout Itemize
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
:name
\end_layout

\end_inset

 - use to specify the name of the index.
 If not given, the Rails default will be used.
\end_layout

\begin_layout Itemize
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
:unique
\end_layout

\end_inset

 - passing 
\begin_inset Flex Code
status collapsed

\begin_layout Plain Layout
:unique => true
\end_layout

\end_inset

 will specify the creation of a unique index.
\end_layout

\end_body
\end_document