note description: "Summary description for {TEST_NULL}." author: "" date: "$Date$" revision: "$Revision$" class TEST_NULL inherit TEST_BASIC_DATABASE redefine on_prepare end feature {NONE} -- Prepare on_prepare -- Prepare do Precursor; (create {GLOBAL_SETTINGS}).set_use_extended_types (True) end feature -- Testing test_null do reset_database establish_connection if attached session_control as l_control and then not l_control.is_connected then assert ("Could not connect to database", False) else test_null_load_data test_null_manipulate_data end disconnect end feature {NONE} -- Implementation new_book: BOOK5 -- Filled book to put into database do create Result Result.set_author ("Paul") Result.set_price (4.0) Result.set_quantity (50) Result.set_double_value (2.3) Result.set_year (1980) Result.set_text_value ("test") end test_null_load_data -- Create table in database with same structure as 'new_book' do drop_repository (table_name) if is_odbc or is_sybase then execute_query ("CREATE TABLE DB_NULL_VALUES (title varchar(255), author varchar(80), text_value varchar(max), [year] datetime, quantity int ,[price] float, double_value float)") end if is_mysql then execute_query ("CREATE TABLE `DB_NULL_VALUES` (`title` varchar(255), `author` varchar(80), `text_value` varchar(65532), `year` datetime, `quantity` int(11) ,`price` double, `double_value` double)") end if is_oracle then execute_query ("CREATE TABLE DB_NULL_VALUES (title varchar(255), author varchar(80), text_value varchar(65535), year DATE, quantity int ,price float, double_value float)") end end test_null_manipulate_data -- Insert and update objects using NULL values. local l_repository: DB_REPOSITORY l_db_store: DB_STORE l_dyn_change: DB_DYN_CHANGE l_change: DB_CHANGE l_book: like new_book l_list: ARRAYED_LIST [like new_book] l_void_book: like new_book do -- Setup repository for adding data. create l_repository.make (table_name) l_repository.load create l_db_store.make l_db_store.set_repository (l_repository) -- Adding a book with Void values directly l_book := new_book l_db_store.put (l_book) -- Check that the stored book is valid. db_selection.set_map_name ("Paul", "author_name") l_list := load_list_with_select ("select * from " + sql_table_name (table_name) + " where author=:author_name", new_book) db_selection.unset_map_name ("author_name") if l_list.count = 1 then assert ("1 - Result is not expected", l_list.first ~ new_book) else assert ("1 - Number of results is not expected", False) end if not is_mysql then -- Insert a book with a non-void title l_book := new_book l_book.set_author ("Alfred") l_book.set_title ("There is a title") l_db_store.put (l_book) -- Chec that the stored book is valid db_selection.set_map_name ("Alfred", "author_name") l_list := load_list_with_select ("select * from " + sql_table_name (table_name) + " where author=:author_name", new_book) db_selection.unset_map_name ("author_name") if l_list.count = 1 then assert ("2 - Result is not expected", l_list.first ~ l_book) else assert ("2 - Number of results is not expected", False) end -- Update the book with all NULL attributes, except for `author' using a dynamic query create l_dyn_change.make l_dyn_change.set_map_name (Void, "quantity_name") l_dyn_change.set_map_name (Void, "title_name") l_dyn_change.set_map_name (Void, "year_name") l_dyn_change.set_map_name (Void, "price_name") l_dyn_change.set_map_name (Void, "double_value_name") l_dyn_change.set_map_name (Void, "text_name") l_dyn_change.set_map_name ("Alfred", "author_name") l_dyn_change.prepare_32 ("update " + sql_table_name (table_name) + "[ set quantity = :quantity_name, title = :title_name, year = :year_name, price = :price_name, double_value = :double_value_name, text_value = :text_name where author=:author_name ]") l_dyn_change.unset_map_name ("quantity_name") l_dyn_change.unset_map_name ("title_name") l_dyn_change.unset_map_name ("year_name") l_dyn_change.unset_map_name ("price_name") l_dyn_change.unset_map_name ("double_value_name") l_dyn_change.unset_map_name ("text_name") l_dyn_change.unset_map_name ("author_name") l_dyn_change.execute l_dyn_change.terminate db_selection.set_map_name ("Alfred", "author_name") l_list := load_list_with_select ("select * from " + sql_table_name (table_name) + " where author=:author_name", new_book) db_selection.unset_map_name ("author_name") if l_list.count = 1 and attached l_list.first as l_retrieved_book then create l_void_book l_void_book.set_author (l_retrieved_book.author) assert ("3 - Result is not expected", l_retrieved_book ~ l_void_book) else assert ("3 - Number of results is not expected", False) end execute_query ("DELETE FROM " + table_name + " WHERE author='Alfred'") end -- Insert a book with a non-void title l_book := new_book l_book.set_author ("Alfredo") l_book.set_title ("There is a title") l_db_store.put (l_book) -- Chec that the stored book is valid db_selection.set_map_name ("Alfredo", "author_name") l_list := load_list_with_select ("select * from " + sql_table_name (table_name) + " where author=:author_name", new_book) db_selection.unset_map_name ("author_name") if l_list.count = 1 then assert ("2 - Result is not expected", l_list.first ~ l_book) else assert ("2 - Number of results is not expected", False) end -- Update the book with all NULL attributes, except for `author' using a dynamic query create l_change.make l_change.set_map_name (Void, "quantity_name") l_change.set_map_name (Void, "title_name") l_change.set_map_name (Void, "year_name") l_change.set_map_name (Void, "price_name") l_change.set_map_name (Void, "double_value_name") l_change.set_map_name ("Alfredo", "author_name") l_change.set_query ("update " + sql_table_name (table_name) + " set quantity=:quantity_name,title=:title_name,year=:year_name, price=:price_name,double_value=:double_value_name where author=:author_name") l_change.execute_query l_change.unset_map_name ("quantity_name") l_change.unset_map_name ("title_name") l_change.unset_map_name ("year_name") l_change.unset_map_name ("price_name") l_change.unset_map_name ("double_value_name") l_change.unset_map_name ("author_name") db_selection.set_map_name ("Alfredo", "author_name") l_list := load_list_with_select ("select * from " + sql_table_name (table_name) + " where author=:author_name", new_book) db_selection.unset_map_name ("author_name") if l_list.count = 1 and attached l_list.first as l_retrieved_book then create l_void_book l_void_book.set_author (l_retrieved_book.author) assert ("4 - Result is not expected", l_retrieved_book ~ l_void_book) else assert ("4 - Number of results is not expected", False) end end data_objects: HASH_TABLE [ANY, STRING] -- Data objects once create Result.make (1) end table_name: STRING = "DB_NULL_VALUES" end