# Test the table_access_service INSTALL COMPONENT 'file://component_test_table_access'; # INSERT SELECT test_table_access_driver("unknown test"); SELECT test_table_access_driver("INSERT-CUSTOMER-STRESS-1"); SELECT test_table_access_driver("INSERT-CUSTOMER-STRESS-2"); SELECT test_table_access_driver("INSERT-CUSTOMER"); CREATE SCHEMA shop; SELECT test_table_access_driver("INSERT-CUSTOMER"); CREATE TABLE shop.customer (x integer); SELECT test_table_access_driver("INSERT-CUSTOMER-STRESS-3"); SELECT test_table_access_driver("INSERT-CUSTOMER"); DROP TABLE shop.customer; CREATE TABLE shop.customer(x integer, y integer, z integer); SELECT test_table_access_driver("INSERT-CUSTOMER"); DROP TABLE shop.customer; CREATE TABLE shop.customer( `ID` integer not null, `NAME` varchar(50) not null, `ADDRESS` varchar(255)); SELECT test_table_access_driver("INSERT-CUSTOMER-NO-COMMIT"); SELECT * from shop.customer; SELECT test_table_access_driver("INSERT-CUSTOMER-ROLLBACK"); SELECT * from shop.customer; SELECT test_table_access_driver("INSERT-CUSTOMER"); SELECT * from shop.customer; # FETCH SELECT test_table_access_driver("FETCH-ORDER"); CREATE TABLE `shop`.`order` (x integer); SELECT test_table_access_driver("FETCH-ORDER"); DROP TABLE `shop`.`order`; CREATE TABLE `shop`.`order` ( CUSTOMER_ID INTEGER not null, ORDER_ID INTEGER not null, ORDER_COMMENT VARCHAR(50), DATE_CREATED DATE); SELECT test_table_access_driver("FETCH-ORDER"); CREATE TABLE `shop`.`order_line` (y integer); SELECT test_table_access_driver("FETCH-ORDER"); DROP TABLE `shop`.`order_line`; CREATE TABLE `shop`.`order_line` ( ORDER_ID INTEGER not null, LINE_NUM INTEGER not null, ITEM_ID INTEGER not null, UNIT_PRICE DECIMAL(5,2) not null, QTY INTEGER not null); SELECT test_table_access_driver("FETCH-ORDER"); ALTER TABLE `shop`.`order` ADD INDEX index_date_created(DATE_CREATED); SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'shop' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX; SHOW CREATE TABLE `shop`.`order`; SELECT test_table_access_driver("FETCH-ORDER"); ALTER TABLE `shop`.`order` ADD KEY(ORDER_ID); SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'shop' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX; SELECT test_table_access_driver("FETCH-ORDER"); ALTER TABLE `shop`.`order` DROP KEY ORDER_ID; ALTER TABLE `shop`.`order` ADD PRIMARY KEY(ORDER_ID); SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'shop' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX; SELECT test_table_access_driver("FETCH-ORDER"); INSERT INTO `shop`.`order` VALUES(1, 1000, 'This is order 1000', '2023-04-01'); INSERT INTO `shop`.`order` VALUES(1, 1002, 'This is order 1002', '2048-04-01'); SELECT test_table_access_driver("FETCH-ORDER"); INSERT INTO `shop`.`order` VALUES(1, 1001, 'This is order 1001', '2020-12-31'); SELECT test_table_access_driver("FETCH-ORDER"); ALTER TABLE `shop`.`order_line` ADD PRIMARY KEY(ORDER_ID, LINE_NUM); SELECT test_table_access_driver("FETCH-ORDER"); INSERT INTO `shop`.`order_line` VALUES(1000, 1, 888, 12.23, 10); INSERT INTO `shop`.`order_line` VALUES(1000, 2, 777, 34.45, 20); INSERT INTO `shop`.`order_line` VALUES(1000, 3, 666, 8.99, 5); INSERT INTO `shop`.`order_line` VALUES(1002, 1, 555, 100, 1); INSERT INTO `shop`.`order_line` VALUES(1002, 2, 444, 0.99, 1000); SELECT test_table_access_driver("FETCH-ORDER"); INSERT INTO `shop`.`order_line` VALUES(1001, 1, 333, 12, 1); SELECT test_table_access_driver("FETCH-ORDER"); INSERT INTO `shop`.`order_line` VALUES(1001, 2, 222, 24, 5); SELECT test_table_access_driver("FETCH-ORDER"); # INDEX SCAN SELECT test_table_access_driver("INDEX-SCAN"); CREATE TABLE `shop`.`warehouse` (BUILDING_ID integer); SELECT test_table_access_driver("INDEX-SCAN"); DROP TABLE `shop`.`warehouse`; CREATE TABLE `shop`.`warehouse` ( BUILDING_ID integer not null, FLOOR_NUMBER integer not null, ALLEY_NUMBER integer not null, SHELVE_NUMBER integer not null, CAPACITY integer not null, DESCRIPTION VARCHAR(100)); SELECT test_table_access_driver("INDEX-SCAN"); ALTER TABLE `shop`.`warehouse` ADD INDEX some_index(DESCRIPTION); SELECT test_table_access_driver("INDEX-SCAN"); ALTER TABLE `shop`.`warehouse` ADD INDEX `SHELVES`(SHELVE_NUMBER); SELECT test_table_access_driver("INDEX-SCAN"); ALTER TABLE `shop`.`warehouse` DROP INDEX `SHELVES`; ALTER TABLE `shop`.`warehouse` ADD INDEX `SHELVES`( SHELVE_NUMBER, ALLEY_NUMBER, FLOOR_NUMBER, BUILDING_ID); SELECT test_table_access_driver("INDEX-SCAN"); ALTER TABLE `shop`.`warehouse` DROP INDEX `SHELVES`; ALTER TABLE `shop`.`warehouse` ADD INDEX `SHELVES`( BUILDING_ID, ALLEY_NUMBER, FLOOR_NUMBER, SHELVE_NUMBER); SELECT test_table_access_driver("INDEX-SCAN"); ALTER TABLE `shop`.`warehouse` DROP INDEX `SHELVES`; ALTER TABLE `shop`.`warehouse` ADD INDEX `SHELVES`( BUILDING_ID, FLOOR_NUMBER, ALLEY_NUMBER, SHELVE_NUMBER, DESCRIPTION); SELECT test_table_access_driver("INDEX-SCAN"); ALTER TABLE `shop`.`warehouse` DROP INDEX `SHELVES`; ALTER TABLE `shop`.`warehouse` ADD INDEX `SHELVES`( BUILDING_ID, FLOOR_NUMBER, ALLEY_NUMBER, SHELVE_NUMBER); SELECT test_table_access_driver("INDEX-SCAN"); SELECT test_table_access_driver("INDEX-FETCH-B"); SELECT test_table_access_driver("INDEX-FETCH-BF"); SELECT test_table_access_driver("INDEX-FETCH-BFA"); SELECT test_table_access_driver("INDEX-FETCH-BFAS"); INSERT INTO `shop`.`warehouse` VALUES(1007, 4, 3, 12, 50, "no comments"); SELECT test_table_access_driver("INDEX-SCAN"); SELECT test_table_access_driver("INDEX-FETCH-B"); SELECT test_table_access_driver("INDEX-FETCH-BF"); SELECT test_table_access_driver("INDEX-FETCH-BFA"); SELECT test_table_access_driver("INDEX-FETCH-BFAS"); INSERT INTO `shop`.`warehouse` VALUES(1007, 4, 3, 11, 150, "no comments"); SELECT test_table_access_driver("INDEX-SCAN"); SELECT test_table_access_driver("INDEX-FETCH-B"); SELECT test_table_access_driver("INDEX-FETCH-BF"); SELECT test_table_access_driver("INDEX-FETCH-BFA"); SELECT test_table_access_driver("INDEX-FETCH-BFAS"); INSERT INTO `shop`.`warehouse` VALUES(1003, 4, 3, 11, 250, "no comments"); INSERT INTO `shop`.`warehouse` VALUES(1005, 6, 3, 11, 6311, "no comments"); INSERT INTO `shop`.`warehouse` VALUES(1005, 4, 3, 11, 4311, "no comments"); SELECT test_table_access_driver("INDEX-SCAN"); SELECT test_table_access_driver("INDEX-FETCH-B"); SELECT test_table_access_driver("INDEX-FETCH-BF"); SELECT test_table_access_driver("INDEX-FETCH-BFA"); SELECT test_table_access_driver("INDEX-FETCH-BFAS"); INSERT INTO `shop`.`warehouse` VALUES(1003, 4, 3, 11, 250, "no comments"); INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 7, 18, 5718, "no comments"); INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 2, 19, 5219, "no comments"); SELECT test_table_access_driver("INDEX-SCAN"); SELECT test_table_access_driver("INDEX-FETCH-B"); SELECT test_table_access_driver("INDEX-FETCH-BF"); SELECT test_table_access_driver("INDEX-FETCH-BFA"); SELECT test_table_access_driver("INDEX-FETCH-BFAS"); INSERT INTO `shop`.`warehouse` VALUES(1002, 2, 2, 2, 20, "no comments"); INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 5, 33, 5533, "no comments"); INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 5, 22, 5522, "no comments"); INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 5, 11, 11, "no comments"); SELECT test_table_access_driver("INDEX-SCAN"); SELECT test_table_access_driver("INDEX-FETCH-B"); SELECT test_table_access_driver("INDEX-FETCH-BF"); SELECT test_table_access_driver("INDEX-FETCH-BFA"); SELECT test_table_access_driver("INDEX-FETCH-BFAS"); INSERT INTO `shop`.`warehouse` VALUES(1001, 5, 5, 5, 12, "no comments"); INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 5, 2, 552, "no comments"); INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 5, 7, 557, "no comments"); INSERT INTO `shop`.`warehouse` VALUES(1005, 5, 5, 5, 5555, "no comments"); SELECT test_table_access_driver("INDEX-SCAN"); SELECT test_table_access_driver("INDEX-FETCH-B"); SELECT test_table_access_driver("INDEX-FETCH-BF"); SELECT test_table_access_driver("INDEX-FETCH-BFA"); SELECT test_table_access_driver("INDEX-FETCH-BFAS"); DROP TABLE shop.warehouse; DROP TABLE shop.customer; DROP SCHEMA shop; UNINSTALL COMPONENT 'file://component_test_table_access';