[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