3 require 'hdboo/database'
4 require 'hdboo/sqlparser'
7 def require_sql(module_name, destructive=false, reload=false)
8 unless module_name =~ /([_\w]+(?:\/[_\w]+)*)@([_a-z]+)/
9 raise NameError, module_name
13 Hdboo.resource("#{module_name}.sql", reload) do |sql_script|
14 raise Errno::ENOENT, module_name unless sql_script
15 Hdboo::SQL.sql_eval(db_name, sql_script, destructive)
16 if Hdboo.resource_exist?("#{module_name}.rb")
17 reload ? load(module_name+'.rb')\
18 : require(module_name)
25 def require_sql!(module_name)
26 require_sql(module_name, true, false)
29 def load_sql(module_name)
30 require_sql(module_name, false, true)
33 def load_sql!(module_name)
34 require_sql(module_name, true, true)
41 def self.connect(database_name)
42 Database[database_name]
45 def self.query(sql_script, &block)
46 Database.query(sql_script, &block)
49 def self.sql_eval!(database_name, sql_script)
50 sql_eval(database_name, sql_script, true)
54 def self.sql_eval(database_name, sql_script, destructive=false)
55 sql_ast = Parser.new.parse(sql_script)
58 Parser::ASTWalker.new\
59 .visit(:MODULE_ANNOTATION) do |node|
60 sql_module = node.children.find do |child|
61 child.value.is_a?(Module)
63 node.children.each do |child|
64 if child.value.is_a?(Include)
65 sql_module.elements << child.value.sql_element
69 .visit(:CREATE_TABLE) do |node|
70 sql_module.elements << node.value
72 .visit(:CREATE_VIEW) do |view|
73 sql_module.elements << node.value
75 .visit(:CREATE_PROCEDURE) do |node|
76 sql_module.elements << node.value
80 db = Database[database_name]
81 db.execute(sql_module.ddl) if destructive
82 db.transaction {sql_module.eval(destructive)}
88 def self.new(type, args)
91 annotation_libraries.each do |library|
92 if library.const_defined?(type)
93 clazz = library.const_get(type)
94 break if clazz < Annotation
100 raise 'unknown annotation type: ' + type.to_s
105 raise "annotation #{type.to_s} can not be initialized.: #{$!.message}"
109 def self.annotation_libraries
110 unless @annotation_libraries
111 @annotation_libraries = [
115 ::Hdboo::SQL::Procedure,
116 ::Hdboo::SQL::Procedure::Intercepter,
117 ::Hdboo::SQL::Procedure::Parameter
119 ::Hdboo::Validator.constants.each do |name|
120 validator = ::Hdboo::Validator.const_get(name)
121 if validator < ::Hdboo::Validator
122 validator.send(:include, Annotation)
126 @annotation_libraries
130 attr_accessor :annotation_context
134 attr_accessor :sql_module, :source
136 raise "#{self.class.name}#eval() must be implemented."
140 raise "#{self.class.name}#create() must be implemented."
144 raise "#{self.class.name}#drop() must be implemented."
153 @instances[name.to_sym]
156 def self.register(instance)
157 @instances[instance.fqn] = instance
161 attr_reader :fqn, :schema, :namespace, :elements
162 def initialize(fqn, schema='')
169 namespace.const_get(symbol.to_sym)
173 return @namespace if @namespace
174 unless fqn =~ /\w+(.\w+)*/
175 raise SyntaxError, "invalid namespace format: #{fqn}"
178 segments = fqn.split(/\./)
179 segments.reverse.each_with_index do |name, depth|
180 root = (depth == segments.length-1) ? '::' : ''
181 script = [ "module #{root}#{name}",
187 @rb_module = Kernel.eval('::' + segments.join('::'))
191 def eval(destructive=false)
192 elements.each do |element|
193 element.sql_module = self
194 element.eval(destructive)
203 statements = elements.collect {|each| each.create}\
205 "DELIMITER // \n#{statements}\n//"
209 statements = elements.collect {|each| each.drop}\
210 .reverse.join("\n//\n")
212 'SET FOREIGN_KEY_CHECKS = 0;',
214 'SET FOREIGN_KEY_CHECKS = 1;'].join("\n//\n") + "\n//"
221 attr_reader :package_name
222 def initialize(package_name)
223 @package_name = package_name.to_sym
227 unless Include.const_get(package_name)
228 raise "unkown sql package name: #{package_name}"
230 clazz = Include.const_get(package_name)
237 def eval(destructive)
243 'CREATE TABLE pivot (pos INTEGER) ENGINE = MYISAM',
247 (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
248 (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
249 (21),(22),(23),(24),(25),(26),(27),(28),(29),(30),
256 CREATE FUNCTION CSV_GET(
259 ) RETURNS VARCHAR (1024) DETERMINISTIC
261 RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(csv, ',', pos), ',', -1);
267 CREATE FUNCTION CSV_LENGTH(
269 ) RETURNS INTEGER DETERMINISTIC
274 RETURN LENGTH(csv) - LENGTH(REPLACE(csv, ',', '')) + 1;
283 'DROP TABLE IF EXISTS pivot CASCADE',
284 'DROP FUNCTION IF EXISTS CSV_GET',
285 'DROP FUNCTION IF EXISTS CSV_LENGTH',
291 class ResultSet < Array
293 replace(collect do |row|
304 @instances[name.to_sym]
306 def self.register(table)
307 @instances[table.name] = table
312 attr_reader :name, :elements, :annotations, :clazz
313 def initialize(name, elements, annotations=[])
315 @elements = elements.to_a
316 @annotations = annotations.to_a
317 annotations.each {|each| each.annotation_context = self}
322 @columns = elements.select {|each| each.is_a?(Column)}
329 @constraints = elements.select {|each| each.is_a?(Constraint)}
334 def unique_constraints
335 constraints.select{|any| any.is_a?(Table::Constraint::Unique)}
343 ["DROP TABLE IF EXISTS #{name} CASCADE"]
346 def eval(destructive)
348 @clazz = sql_module.namespace.const_defined?(name)\
352 classified_by.define_subclasses
358 clazz = Class.new(Record)
359 define_accessors(clazz)
360 define_class_accessors(clazz)
361 sql_module.namespace.const_set(name, clazz)
366 clazz = sql_module.namespace.const_get(name)
367 define_accessors(clazz)
368 define_class_accessors(clazz)
372 def define_accessors(clazz)
375 metadata.columns.each do |column|
376 define_method(column.name) do
379 setter_name = (column.name.to_s + '=').to_sym
380 define_method(setter_name) do |val|
382 self[column.name] = val
385 if column.name.to_s =~ /^is_(.+)$/
386 define_method($1 + '?') do
387 result = send(column.name)
388 return (result == 0) ? false : result
395 def define_class_accessors(clazz)
397 Hdboo.singleton_eval(clazz) do
398 define_method(:metadata) do
405 unless @classified_by
406 @classified_by = annotations.find {|each| each.is_a? ClassifiedBy}
412 attr_reader :name, :type, :options, :annotations
413 def initialize(name, type, options=[], annotations=[])
416 @options = options.to_a
417 @annotations = annotations.to_a
418 annotations.each {|each| each.annotation_context = self}
423 validators = annotations.concat(options).push(@type)\
424 .select{|each| each.is_a?(Validator)}
425 @validators = Validator::ValidatorList.new(validators)
431 validators.validate(val, name)
437 def self.new(type, args)
439 unless self.const_defined?(type)
440 raise "unknown constraint type: #{type.to_s}"
442 clazz = self.const_get(type)
447 attr_reader :name, :columns
448 attr_accessor :annotations
453 def initialize(name, columns)
455 @columns = columns.to_a
457 annotations.each {|each| each.annotation_context = self}
461 class PrimaryKey < Unique
465 class ReferencesConstraint
467 attr_reader :reference_table, :reference_columns
468 def initialize(name, columns, reference_table, reference_columns)
470 @columns = columns.to_a
471 @reference_table = reference_table.to_sym
472 @reference_columns = reference_columns.to_a
474 annotations.each {|each| each.annotation_context = self}
480 attr_reader :name, :columns, :annotations
481 def initialize(name, columns)
485 annotations.each {|each| each.annotation_context = self}
491 attr_reader :column_name
492 def initialize(column_name)
493 @column_name = column_name.to_sym
496 def annotation_context=(table)
497 unless table.columns.any? {|any| any.name == column_name}
498 raise "undefined column: #{table.name}.#{@column_name}."
500 @annotation_context = table
503 def define_subclasses
504 table = annotation_context
505 subtype_names.each do |subtype_name|
506 namespace = table.sql_module.namespace
507 next if namespace.const_defined?(subtype_name)
508 subclass = Class.new(table.clazz)
509 namespace.const_set(subtype_name, subclass)
514 power_type.clazz.constants.reject{|any| any == 'Enumerable'}
518 table = annotation_context
519 power_type_name = table.constraints.find do |constraint|
520 constraint.is_a?(Constraint::ReferencesConstraint)\
521 && constraint.columns[0].to_sym == column_name
523 Table[power_type_name]
530 attr_reader :name, :from_tables, :column_names, :annotations
531 def initialize(name, from_tables=[], column_names=[], annotations=[])
533 @from_tables = from_tables.to_a
534 @column_names = column_names.to_a
535 @annotations = annotations.to_a
536 annotations.each {|each| each.annotation_context = self}
548 ["DROP VIEW IF EXISTS #{name} CASCADE"]
554 attr_reader :name, :length
556 def self.new(type, length=nil)
557 clazz = TYPE_TO_CLASS_MAP[type.to_sym]
561 def initialize(length=nil)
566 raise "#{self.class.name}#value_of() must be implemented."
572 if val.nil? || val.is_a?(TrueClass) || val.is_a?(FalseClass)
575 if val.is_a?(::String) && (val =~ /([+|-]?[1-9][0-9]*|0)/)
578 unless val.is_a?(Numeric) && val.integer?
587 ? "must be integer, but was #{val}."\
588 : "must be integer of #{length} digits or less, but was #{val}."
593 val = val.values.first
594 elsif val.is_a?(Array)
609 val = val.values.first
610 elsif val.is_a?(Array)
620 val.nil? || val.is_a?(::String)
624 "must be String, but was #{val.class.name}."
628 val = val.values.first
629 elsif val.is_a?(Array)
637 include SQL::DataType
649 TYPE_TO_CLASS_MAP = {
650 :INTEGER => DataType::Integer,
651 :TINYINT => DataType::Integer,
652 :SMALLINT => DataType::Integer,
653 :BOOL => DataType::Bool,
654 :CHAR => DataType::String,
655 :VARCHAR => DataType::String,
656 :TEXT => DataType::String,
657 :DATETIME => DataType::DateTime,
658 :TIMESTAMP => DataType::DateTime
662 class Record < HashAsObject
667 Table.new('-', [], [])
673 def self.identifier_keys
674 metadata.constraints\
675 .find {|some| some.is_a? Table::Constraint::PrimaryKey}\
677 .map {|each| each.to_sym}
681 self.class.identifier_keys.map {|key| self[key]}
685 return super unless other.is_a? Record
686 return other == self if self.instance_of? Record
687 identifier == other.identifier
691 metadata = clazz.metadata
692 if metadata.classified_by
693 class_name = self[metadata.classified_by.column_name]
694 clazz = metadata.sql_module.namespace.const_get(class_name)
696 clazz.new.replace(self)
699 def validators_for(key)
702 unless self.is_a?(Record)
705 column_for_key = metadata.columns.find{|any|any.name==key}
706 result.concat(column_for_key.validators) if column_for_key
717 @instances[name.to_sym]
720 def self.register(procedure)
721 @instances[procedure.name.to_sym] = procedure
725 attr_reader :name, :owner, :annotations, :parameters
726 def initialize(name, parameters, annotations)
728 @parameters = parameters
729 parameters.each {|each| each.owner = self}
730 @annotations = annotations.to_a
731 annotations.each {|each| each.annotation_context = self}
739 ["DROP PROCEDURE IF EXISTS #{name}"]
742 def eval(destructive)
743 Procedure.register(self)
744 unless name =~ /^([A-Z][A-Za-z0-9]+)_([\w]+)$/
749 clazz = sql_module.namespace.const_get(class_name)
750 @owner = clazz.metadata
752 Hdboo.singleton_eval(clazz, <<-EOS)
753 def #{method_name} (*args, &block)
754 Hdboo::SQL::Procedure[:#{name}].call(*args, &block)
758 clazz.module_eval(<<-EOS)
759 def #{method_name}(kargs={}, &block)
760 kargs = Hdboo.normalize_key(kargs)
761 kargs = self.to_hash.merge(kargs.to_hash)
762 Hdboo::SQL::Procedure[:#{name}].call(kargs, &block)
765 def #{method_name}! (kargs={}, &block)
766 result = #{method_name}(kargs, &block)
767 new_status = result.is_a?(Array)\
771 raise NoDataFound, '#{name}', caller
777 if master_data_loader && destructive
778 master_data_loader.load
781 if constants_definition
782 constants_definition.define_on(clazz)
787 def master_data_loader
788 annotations.find {|any| any.is_a? MasterData}
791 def constants_definition
792 annotations.find {|any| any.is_a? DefineConstants}
795 def call(*args, &block)
799 elsif args.length == 1 && args.first.respond_to?(:to_hash)
800 kargs = Hdboo.normalize_key(args.first.to_hash)
803 parameters.each_with_index do |param, i|
804 kargs[param.name.to_sym] = args[i]
808 intercepters.reverse.each do |intercepter|
809 intercepter.before(kargs, self)
813 intercepters.reverse.each do |intercepter|
815 invocation = lambda do |kargs|
816 intercepter.around(wrapee, kargs, self, &block)
821 result = invocation.call(kargs)
823 intercepters.each do |intercepter|
824 intercepter.after_error($!, self)
828 intercepters.each do |intercepter|
829 result = intercepter.after(result, self)
834 def before(kargs, procedure)
835 errors = Hash.new {|hash, key| hash[key] = []}
836 parameters.each do |parameter|
837 value = kargs[parameter.name]
838 if parameter.required? && value.nil?
839 errors[parameter.name] << 'required.'
841 parameter.validators.each do |validator|
842 unless validator.pass?(value)
843 errors[parameter.name] << validator.message(value)
849 raise InvalidArgument, errors
853 def around(invocation, kargs, procedure, &block)
854 args = parameters.collect {|parameter| kargs[parameter.name]}
855 Database.call_procedure(name, args, &block)
858 def after(result, procedure)
862 def after_error(error, procedure)
868 @intercepters = @annotations\
869 .select {|each| each.kind_of?(Intercepter)}\
878 attr_reader :name, :type, :annotations
881 def initialize(name, type, annotations)
882 @name = name.sub(/^_+/, '').to_sym
884 @annotations = annotations
886 annotations.each {|each| each.annotation_context = self}
889 def annotations= (annotations)
890 annotations.each {|each| each.annotation_context = self}
891 @annotations = annotations
895 return @validators if @validators
896 @validators = annotations.map do |annotation|
897 if annotation.is_a?(ColumnValue)
898 annotation.validators
899 elsif annotation.is_a?(Validator)
909 @optional = annotations.any? {|each| each.is_a?(Optional)}
921 attr_reader :default_value
922 def intialize(default_value=nil)
923 @default_value = default_value
929 attr_reader :table_name
930 def initialize(table_name=nil)
931 @table_name = table_name
935 annotation_context.name
939 procedure = annotation_context.owner
940 namespace = procedure.sql_module.namespace
941 table = table_name.nil?\
943 : namespace.const_get(table_name.to_sym).metadata
944 column = table.columns.find {|each| each.name == column_name}
945 @validators = column.validators
951 def before(kargs, procedure)
954 def around(invocation, kargs, procedure, &block)
955 invocation.call(kargs, &block)
958 def after(result, procedure)
962 def after_error(error, procedure)
966 class OnNoData < Intercepter
968 attr_reader :message_key, :message_text
969 def initialize(message_key, message_text)
970 @message_key = message_key
971 @message_text = message_text
974 def after(result, procedure)
975 if result.nil? || (result.is_a?(Array) && result.empty?)
976 raise NoDataFound.new({@message_key => [@message_text]})
982 class OnExists < Intercepter
984 attr_reader :message_key, :message_text
985 def initialize(message_key, message_text)
986 @message_key = message_key
987 @message_text = message_text
990 def after(result, procedure)
991 unless result.nil? || (result.is_a?(Array) && result.empty?)
992 raise DataConflict.new({@message_key => [@message_text]})
998 class OnConflict < Intercepter
1000 attr_reader :constraint_name, :message_key, :message_text
1001 def initialize(constraint_name, message_key, message_text)
1002 @constraint_name = constraint_name
1003 @message_key = message_key
1004 @message_text = message_text
1007 def after_error(error, procedure)
1008 return unless error.sqlstate == '23000'
1009 table = procedure.owner
1010 if error.message =~ /Duplicate entry '(.*?)' for key '(.+?)'/
1011 #seq_num = $1.to_i - 1
1012 #violated_constraint = table.unique_constraints[seq_num]
1013 #if violated_constraint.name == constraint_name.to_sym
1015 if constraint_name == $2
1016 raise DataConflict, {@message_key => [@message_text]}
1018 elsif error.message =~ /foreign key constraint fails/
1019 if error.message.include?(constraint_name.to_s)
1020 raise DataConflict, {@message_key => [@message_text]}
1027 class Returns < Intercepter
1029 attr_reader :return_type
1031 def initialize(return_type=Record)
1032 if return_type.is_a?(DataType)
1033 @return_type = return_type
1034 elsif return_type.is_a?(String)
1035 @return_type = return_type.to_sym == :Bool\
1036 ? DataType::Bool.new\
1037 : return_type.to_sym
1039 @return_type = return_type
1043 def after(result, procedure)
1044 if return_type.is_a?(Symbol)
1045 type = procedure.sql_module.namespace.const_get(return_type)
1046 raise "unknown type: #{return_type.to_s}" if type.nil?
1049 return nil if result.nil?
1050 unless result.is_a?(Array)
1051 result = RecordSet.new([result])
1053 result.replace(result.collect do |record|
1054 return_type.is_a?(DataType)\
1055 ? return_type.value_of(record)\
1056 : record.as(return_type)
1062 class ReturnsFirst < Returns
1063 def after(result, procedure)
1072 procedure = annotation_context
1077 class DefineConstants
1079 attr_reader :key_column_name
1080 def initialize(key_column_name)
1081 @key_column_name = key_column_name.to_sym
1084 def define_on(clazz)
1085 procedure = annotation_context
1086 procedure.call.each do |record|
1087 constant_key = record[key_column_name]
1088 unless clazz.const_defined?(constant_key)
1089 clazz.const_set(constant_key, record)