[Nitro] OG [RFC]: Multiple field indices

Mark Van De Vyver mvyver at gmail.com
Thu Oct 11 08:31:55 EDT 2007


Hi Devs,

I have a initial attempt at implementing multiple field indices for Og.
The code isn't ready for submission yet, I'm still working on the DBI
adapter.  However I'd appreciate feedback on any potential
problems/issues.

An initial RDoc is below, then I give some examples and the index code
they generate.
Note re the examples:   They are generated using the MySQL DBD, but
some have Sqlite valid commands, so this is just more for feature
illustration at this point.
For the Rdoc's I hope it is clear but if not not please let me know.
Is there some killer feature missing?

    # Create indices on the table corresponding to klass.  Returns +true+ if no
    # errors are encountered, +false+ is returned otherwise. The index name is
    # constructed in the following way: +<og-prefix><table>_<field>_idx+
    #
    # An single field +:index+ attribute can have optional text set using the
    # following annotations (See below for further details):
    #  - +:index_name+
    #  - +:index_order+
    #  - +:index_length+
    #  - +:index_type+
    #  - +:index_using+
    #  - +:pre_index+
    #  - +:post_index+
    #  - +:pre_index_field+
    #  - +:post_index_field+
    #
    # "CREATE #{pre_index} INDEX #{index_name} #{post_index} ON #{table}
    # (#{pre_field} #{field} #{post_field})"
    #
    # Multiple field indices can be specified using <tt>:index => Hash</tt>.
    # A field may belong to multiple indices, and the +:index+ Hash has the
    # following format:
    #  <tt>{1 => index_details_hash, 2 => index_details_hash, ...}</tt>
    #
    # <b>The contents of a valid index details hash will depend on the database
    # used. Users are responsible for nominating options that are valid for the
    # database being employed.</b>
    #
    # The recognized +index_details_hash+ hash keys (their defaults) and
    # a brief description (alphabetical order):
    #  - +:name+ (constructed) A string containing the index name.  This need
    #     only be provided once of an index, and can be given in any
index_details_hash
    #     of any index field. If not provided a name is constructed from the
    #     table and field names (ordered) and the suffix "_idx".
    #  - +:position+ (alphabetical) The position of the field in the index.  If
    #    a position is not given the field is placed in alphabetical
order, after
    #    the position fields. Discontinuities in position numbering is
respected, and
    #    the alphabetically sorted fields are interleaved between
    #    discontinuously positioned fields.
    #  - +:order+ (+nil+) The field sort order. Recognized values are: +:asc:+,
    #    +:desc+ [MySQL & Sqlite specific].
    #  - +:length+ (+nil+) Index that uses only the leading +:length+ of column
    #    values.  Required for some datatypes. [MySQL specific].
    #  - +:type+ (+nil+) Need only be defined in one field's index hash.
    #    Recognized types are:
    #    * +:unique+,
    #    * +:fulltext+,
    #    * +:spatial+
    #  - +:using+ (+nil+) Recognized values are:
    #    * +:btree+, (MySQL|PG)
    #    * +:hash+, (MySQL|PG)
    #    * +:gist+, (PG)
    #    * +:gin+, (PG)
    #    * +:rtree+ (MySQL)
    #  - +:pre_index+ (+nil+) String to insert before the SQL 'INDEX' keyword.
    #  - +:post_index+ (+nil+) String to insert after the SQL 'INDEX' keyword.
    #  - +:pre_field+ (+nil+) String to insert before the index field name.
    #  - +:post_field+ (+nil+) String to insert after the index field name.
    #
    #  === Example
    # Multiple field index (MySQL so length is required for any text
and binary fields)
    #
    #  class Person
    #   attr_accessor :name, String, :index => {1 => {:name => "my_first_idx",
    #   :position => 2, :type => :unique, :length => 40},
    #                                           4 => {:length => 20}}
    #   attr_accessor :age, Integer, :index => true
    #   attr_accessor :height_a, Float, :index => {1 => {:position => 1},
    #                                              3 => true,
    #                                              4 => true}
    #   attr_accessor :height_b, Float, :index => {1 => true,
    #                                              3 => true,
    #                                              4 => true}
    #  end
    #
    # Multiple field index, illustrating pre and post index keywords (MySQL):
    #
    #  class Person
    #   attr_accessor :name, String, :index => true,
    #                 :pre_index => "UNIQUE", :post_index => "USING HASH",
    #                 :post_index_field =>"(20) ASC"
    #   attr_accessor :age, Integer, :index  => true,
    #                 :pre_index => "FULLTEXT", :post_index => "USING BTREE",
    #                 :post_index_field =>"DESC"
    #   attr_accessor :height, Float, :index => true,
    #                 :pre_index => "SPATIAL", :post_index_field => "ASC"
    #  end
    #
    # Note: Serializable (false) overrides index annotation. Only two indices
    # (+name+ and +age+) are created by the following:
    #
    # class MyClass
    #   attr_accessor :test
    #   attr_accessor :name, String, :doc => 'Hello', :index => true
    #   attr_accessor :age, Fixnum, :index => true
    #   attr_accessor :body, String, :index => true, :serialize => false
    # end
    #
    # === Errors
    # No exceptions are raised by this method.  Specifically it is not possible
    # to discern if an index already exists, and whether this causes
all drivers to
    # raise an exception.  If DbiAdapter#create_one_table_index returns +false+
    # then DbiAdapter#create_table_indices creates an error log entry and
    # returns false.
    #
    # === Notes
    # The +pre_index+, +post_index+, +pre_field+ and +post_feild+ text/keywords
    # are database dependent - please consult the database documentation for
    # details. All table, field, and index names are quoted using the reserved
    # word quote character, see + at options[:rw_quote_character]+.
    # Serializable (false) overrides index annotation.
    #


class Person_013
  attr_accessor :name, String, :index => true, :pre_index => "UNIQUE",
    :post_index => "USING BTREE", :pre_index_field => "",
:post_index_field => "(20) ASC"
  attr_accessor :age, Integer, :index  => true, :pre_index => "UNIQUE",
    :post_index => "USING BTREE", :pre_index_field => "", :index_length => 20,
    :index_order => :desc
  attr_accessor :height, Float, :index => true, :pre_index => "UNIQUE",
    :post_index => "USING BTREE", :pre_index_field => "",
    :post_index_field => "COLLATE collation-name", :index_order => :asc
end

"CREATE UNIQUE INDEX USING BTREE `ogperson_013_name_idx` ON `ogperson_013`
( `name` (20) ASC );"
"CREATE UNIQUE INDEX USING BTREE `ogperson_013_age_idx` ON `ogperson_013`
( `age`(20) DESC );"
"CREATE UNIQUE INDEX USING BTREE `ogperson_013_height_idx` ON `ogperson_013`
( `height` COLLATE collation-name ASC );"


class Person_014
  attr_accessor :name, String, :index => {1 => {:name => "my_own_idx",
:position => 2, :type => :unique, :length => 40},
                                         4 => {:length => 20, :using => :btree}}
  attr_accessor :age, Integer, :index => true, :post_index => "IF NOT EXISTS"
  attr_accessor :height_a, Float, :index => {1 => {:position => 1},
                                            4 => true,
                                            5 => true}
  attr_accessor :height_b, Float, :index => {1 => true,
                                            4 => true,
                                            5 => true}
end

"CREATE INDEX `ogperson_014_height_a_height_b_idx` ON `ogperson_014` (
`height_a`, `height_b` );"
"CREATE INDEX `ogperson_014_height_b_height_a_name_idx` ON
`ogperson_014` ( `height_b`, `height_a`, `name`(40) );"
"CREATE INDEX `ogperson_014_height_a_height_b_name_idx` USING BTREE ON
`ogperson_014` ( `height_a`, `height_b`, `name`(20) );"


Reagrds

Mark


More information about the Nitro-general mailing list