Forum: Nitro OG [RFC]: Multiple field indices

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 +@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

Very interesting, I will check this in detail when I return home :)

thanks,
-g.