/test/rubeus/extensions/java/sql/test_database_meta_data.rb
Ruby | 257 lines | 225 code | 18 blank | 14 comment | 1 complexity | e012c6f8b8528c54bc6ecdb1d6920ae2 MD5 | raw file
- require 'test/unit'
- require 'rubygems'
- require 'rubeus'
- require 'test/rubeus/extensions/java/sql/test_sql_helper'
- # Test for lib/rubeus/extensions/java/sql/database_meta_data.rb
- class TestDatabaseMetaData < Test::Unit::TestCase
- include TestSqlHelper
- def setup
- setup_connection
- @con.statement do |stmt|
- drop_table_if_exist("TEST", stmt)
- # DDL from http://db.apache.org/derby/docs/dev/ja_JP/ref/rrefsqlj13590.html
- # Index from http://db.apache.org/derby/docs/dev/ja_JP/ref/rrefsqlj20937.html
- create_table_after_drop(<<-"EOS", stmt)
- CREATE TABLE FLIGHTS(
- FLIGHT_ID CHAR(6) NOT NULL ,
- SEGMENT_NUMBER INTEGER NOT NULL ,
- ORIG_AIRPORT CHAR(3),
- DEPART_TIME TIME,
- DEST_AIRPORT CHAR(3),
- ARRIVE_TIME TIME,
- MEAL CHAR(1) CONSTRAINT MEAL_CONSTRAINT
- CHECK (MEAL IN ('B', 'L', 'D', 'S')),
- PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER)
- );
- CREATE TABLE FLTAVAIL(
- FLIGHT_ID CHAR(6) NOT NULL,
- SEGMENT_NUMBER INT NOT NULL,
- FLIGHT_DATE DATE NOT NULL,
- ECONOMY_SEATS_TAKEN INT,
- BUSINESS_SEATS_TAKEN INT,
- FIRSTCLASS_SEATS_TAKEN INT,
- CONSTRAINT FLTAVAIL_PK PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER),
- CONSTRAINT FLTS_FK
- FOREIGN KEY (FLIGHT_ID, SEGMENT_NUMBER)
- REFERENCES Flights (FLIGHT_ID, SEGMENT_NUMBER)
- );
- EOS
- stmt.execute_update('CREATE INDEX IDX_FLIGHT_01 ON Flights(orig_airport)')
- stmt.execute_update('CREATE INDEX IDX_FLIGHT_02 ON Flights(orig_airport, DEPART_TIME desc)')
- stmt.execute_update('CREATE INDEX IDX_FLIGHT_03 ON Flights(DEST_AIRPORT, ARRIVE_TIME desc)')
- create_table_after_drop(<<-"EOS", stmt)
- CREATE TABLE CITIES(
- ID INT NOT NULL CONSTRAINT CITIES_PK PRIMARY KEY,
- CITY_NAME VARCHAR(50)
- );
- CREATE TABLE METROPOLITAN(
- HOTEL_ID INT NOT NULL CONSTRAINT HOTELS_PK PRIMARY KEY,
- HOTEL_NAME VARCHAR(40) NOT NULL,
- CITY_ID INT CONSTRAINT METRO_FK REFERENCES CITIES
- );
- EOS
- stmt.execute_update('CREATE UNIQUE INDEX IDX_CITIES_01 ON CITIES(CITY_NAME)')
- stmt.execute_update('CREATE UNIQUE INDEX IDX_METROPOLITAN_01 ON METROPOLITAN(CITY_ID, HOTEL_NAME)')
- create_table_after_drop(<<-"EOS", stmt)
- CREATE TABLE TEST1(
- ID INT NOT NULL,
- NAME VARCHAR(60)
- );
- EOS
- end
- end
-
- def teardown
- teardown_connection
- end
- def test_table_objects
- tables = @con.meta_data.tables(:schema => "APP", :name_case => :downcase)
- assert_equal 5, tables.length
- assert_equal ['cities', 'flights', 'fltavail', 'metropolitan', 'test1'], tables.map{|t|t.name}.sort
- assert_not_nil tables['flights']
- assert_not_nil tables['fltavail']
- assert_not_nil tables['cities']
- assert_not_nil tables['metropolitan']
- assert_not_nil tables['test1']
- rescue => e
- puts e.to_s
- puts e.backtrace.join("\n ")
- raise e
- end
-
- def assert_column(table, name, type, size, nullable)
- column = table.columns[name]
- assert_not_nil column, "column '#{name}' not found"
- assert_equal(java.sql.Types.const_get(type.to_s.upcase), column.data_type,
- "column '#{name}' type expected #{java.sql.Types.const_get(type.to_s.upcase).inspect} but #{column.data_type.inspect}")
- assert_equal(size, column.size,
- "column '#{name}' size expected #{size.inspect} but #{column.size.inspect}")
- assert_equal(nullable, column.nullable?,
- "column '#{name}' nullable expected #{nullable.inspect} but #{column.nullable?.inspect}")
- assert_equal(nullable ?
- java.sql.DatabaseMetaData.columnNullable :
- java.sql.DatabaseMetaData.columnNoNulls, column.nullable)
- assert_equal column, table[name]
- end
-
- def test_table_object_columns
- tables = @con.meta_data.tables(:schema => "APP", :name_case => :downcase)
- #
- assert_not_nil flights = tables['flights']
- assert_equal 7, flights.columns.length
- assert_column(flights, 'flight_id', :char, 6, false)
- assert_column(flights, 'segment_number', :integer, 10, false)
- assert_column(flights, 'orig_airport', :char, 3, true)
- assert_column(flights, 'depart_time', :time, 8, true)
- assert_column(flights, 'dest_airport', :char, 3, true)
- assert_column(flights, 'arrive_time', :time, 8, true)
- assert_column(flights, 'meal', :char, 1, true)
- #
- assert_not_nil fltavail = tables['fltavail']
- assert_equal 6, fltavail.columns.length
- assert_column(fltavail, 'flight_id', :char, 6, false)
- assert_column(fltavail, 'segment_number', :integer, 10, false)
- assert_column(fltavail, 'flight_date', :date, 10, false)
- assert_column(fltavail, 'economy_seats_taken', :integer, 10, true)
- assert_column(fltavail, 'business_seats_taken', :integer, 10, true)
- assert_column(fltavail, 'firstclass_seats_taken', :integer, 10, true)
- #
- assert_not_nil ctiies = tables['cities']
- assert_equal 2, ctiies.columns.length
- assert_column(ctiies, 'id', :integer, 10, false)
- assert_column(ctiies, 'city_name', :varchar, 50, true)
- #
- assert_not_nil metropolitan = tables['metropolitan']
- assert_equal 3, metropolitan.columns.length
- assert_column(metropolitan, 'hotel_id', :integer, 10, false)
- assert_column(metropolitan, 'hotel_name', :varchar, 40, false)
- assert_column(metropolitan, 'city_id', :integer, 10, true)
- #
- assert_not_nil test1 = tables['test1']
- assert_equal 2, test1.columns.length
- assert_column(test1, 'id', :integer, 10, false)
- assert_column(test1, 'name', :varchar, 60, true)
- rescue => e
- puts e.to_s
- puts e.backtrace.join("\n ")
- raise e
- end
-
- def assert_pk(table, names)
- assert_equal names.length, table.primary_keys.length
- assert_equal names, table.pks.map{|k| k.name}
- assert_equal names, table.primary_keys.map{|k| k.name}
- assert_equal names, table.pk_names
- assert_equal names, table.primary_key_names
- names.each_with_index do |name, index|
- assert_equal name, table.primary_keys[name].name
- assert_equal index + 1, table.primary_keys[name].seq
- assert_equal table.columns[name], table.primary_key_columns[name]
- assert_equal table.columns[name], table.pk_columns[name]
- end
- case names.length
- when 0
- assert_equal nil, table.pk
- assert_equal nil, table.primary_key
- assert_equal nil, table.pk_name
- assert_equal nil, table.primary_key_name
- assert_equal nil, table.pk_column
- assert_equal nil, table.primary_key_column
- when 1
- assert_equal table.pks.first, table.pk
- assert_equal table.pks.first, table.primary_key
- assert_equal table.pk_names.first, table.pk_name
- assert_equal table.pk_names.first, table.primary_key_name
- assert_equal table.pk_columns.first, table.pk_column
- assert_equal table.pk_columns.first, table.primary_key_column
- else
- assert_equal table.pks, table.pk
- assert_equal table.pks, table.primary_key
- assert_equal table.pk_names, table.pk_name
- assert_equal table.pk_names, table.primary_key_name
- assert_equal table.pk_columns, table.pk_column
- assert_equal table.pk_columns, table.primary_key_column
- end
- table.columns.each do |column|
- if table.pk_names.include?(column.name)
- assert_equal true, column.primary_key?, "#{table.name}.#{column.name} shoud be one of pk"
- assert_equal true, column.pk?, "#{table.name}.#{column.name} shoud be one of pk"
- else
- assert_equal false, column.primary_key?, "#{table.name}.#{column.name} shoud not be one of pk"
- assert_equal false, column.pk?, "#{table.name}.#{column.name} shoud not be one of pk"
- end
- end
- end
-
- def test_table_objects_pk
- tables = @con.meta_data.tables(:schema => "APP", :name_case => :downcase)
- assert_pk(tables['flights'], %w(flight_id segment_number))
- assert_pk(tables['fltavail'], %w(flight_id segment_number))
- assert_pk(tables['cities'], %w(id))
- assert_pk(tables['metropolitan'], %w(hotel_id))
- assert_pk(tables['test1'], [])
- end
-
- def assert_index(table, index_name, key_names, asc_descs)
- assert_equal key_names, table.indexes[index_name].keys.map{|k| k.name}
- assert_equal asc_descs, table.indexes[index_name].keys.map{|k| k.asc?}
- end
-
- def test_table_objects_index
- tables = @con.meta_data.tables(:schema => "APP", :name_case => :downcase)
- # assert_equal %w(idx_flight_01 idx_flight_02 idx_flight_03), tables['flights'].indexes.map{|idx| idx.name}
- # assert_equal 3, tables['flights'].indexes.length
- assert_index(tables['flights'], 'idx_flight_01', %w(orig_airport), [true])
- assert_index(tables['flights'], 'idx_flight_02', %w(orig_airport depart_time), [true, false])
- assert_index(tables['flights'], 'idx_flight_03', %w(dest_airport arrive_time), [true, false])
- # assert_equal 0, tables['fltavail'].indexes.length
-
- # assert_equal 1, tables['cities'].indexes.length
- assert_index(tables['cities'], 'idx_cities_01', %w(city_name), [true])
- # assert_equal 1, tables['metropolitan'].indexes.length
- assert_index(tables['metropolitan'], 'idx_metropolitan_01', %w(city_id hotel_name), [true, true])
- # assert_equal 0, tables['test1'].indexes.length
- end
-
- def assert_fk(fk_name, pktable, pkcolumn_names, fktable, fkcolumn_names)
- imported_key = fktable.imported_keys[fk_name]
- exported_key = pktable.exported_keys[fk_name]
- assert_equal fk_name, imported_key.name
- assert_equal fk_name, exported_key.name
-
- assert_equal pktable.name, imported_key.pktable.name
- assert_equal fktable.name, imported_key.fktable.name
- assert_equal pktable.name, exported_key.pktable.name
- assert_equal fktable.name, exported_key.fktable.name
-
- assert_equal pkcolumn_names.length, imported_key.length
- assert_equal fkcolumn_names.length, exported_key.length
- assert_equal imported_key.length, exported_key.length
- pkcolumn_names.each_with_index do |column_name, index|
- [imported_key, exported_key].each do |key|
- assert_equal pkcolumn_names[index], key.pkcolumn_names[index]
- assert_equal pkcolumn_names[index], key.pkcolumns[index].name
- assert_equal pktable, key.pkcolumns[index].table
- assert_equal fkcolumn_names[index], key.fkcolumn_names[index]
- assert_equal fkcolumn_names[index], key.fkcolumns[index].name
- assert_equal fktable, key.fkcolumns[index].table
- end
- end
- end
-
- def test_table_objects_imported_keys_and_exported_keys
- tables = @con.meta_data.tables(:schema => "APP", :name_case => :downcase)
- assert_fk('flts_fk',
- tables['flights'], %w(flight_id segment_number),
- tables['fltavail'], %w(flight_id segment_number))
- assert_fk('metro_fk',
- tables['cities'], %w(id),
- tables['metropolitan'], %w(city_id))
- end
-
- end