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: +
__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 :index =>
Hash.
# A field may belong to multiple indices, and the +:index+ Hash has
the
# following format:
# {1 => index_details_hash, 2 => index_details_hash, …}
#
# 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.
#
# 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
