[Nitro] OG [RFC]: Multiple field indices

George Moschovitis george.moschovitis at gmail.com
Thu Oct 11 09:44:38 EDT 2007


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

thanks,
-g.

On 10/11/07, Mark Van De Vyver <mvyver at gmail.com> wrote:
>
> 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
> _______________________________________________
> Nitro-general mailing list
> Nitro-general at rubyforge.org
> http://rubyforge.org/mailman/listinfo/nitro-general
>



-- 
http://gmosx.me.gr
http://phidz.com
http://blog.gmosx.com
http://cull.gr
http://www.joy.gr
http://nitroproject.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://rubyforge.org/pipermail/nitro-general/attachments/20071011/91a2b099/attachment-0001.html 


More information about the Nitro-general mailing list