Development:DB layer 2.0 functional testing

Note: This article is a work in progress. Please use the page comments for any recommendations/suggestions for improvement.

Moodle 2.0

Objectives

• Switch to prepared statements - using placeholders and preparing statements protects against sql injection
• Remove the need for data to be add/strip slashed all over the place in the code.
• Enable easier and productive unit tests.

Requirements

• 100% cross-db
• OOP implementation:
  • Full abstraction: Hide underlying libraries (adodb, pdo...) and drivers to Moodle developers completely.
  • Make it so that the database object can be subclassed - this means for unit tests that want to test database access, the test framework can override necessary methods
  • support ? and :param parameter types (independently of the param types supported by each driver).
  • global $DB
  • profiling, logging and exceptions
• Easy to use (consistent and similar to current dmllib 1.0)
• Easy to migrate (provide documentation and utilities to help on that)
• Complete unit testing (self)
• Easy unit testing (moodle - real and mockup)
• Well documented (PHPDocs + use examples)
• Tasks, bugs and progress tracked in MDL-14679
• Due date: June 24th 2008

See also

• XMLDB Documentation: where both xmldb and ddl stuff is explained.
• DDL functions - Documentation for all the Data Definition Language (DDL) functions available inside Moodle.
• DML functions - Documentation for all the Data Manipulation Language (DML) functions available inside Moodle.Moodle 2.0

DDL tests

Based on tests already used in xmldb, the following methods have been implemented in simpletest:

• create_table
• drop_table
• add_enum_field
• add_numeric_field
• drop_field
• change_field_type
• change_field_precision
• change_field_sign
• change_field_nullability
• change_field_default
• add_unique_index
• add_non_unique_index
• find_index_name
• drop_index
• add_unique_key
• add_foreign_unique_key
• drop_key
• add_foreign_key
• drop_foreign_key
• change_field_enum
• rename_index >>>experimental<<<
• rename_key >>>experimental<<<
• rename_field
• rename_table
• table_exists
• field_exists
• index_exists
• find_check_constraint_name
• check_constraint_exists
• find_key_name
• find_sequence_name
• delete_tables_from_xmldb_file
• install_from_xmldb_file
• create_temp_table

DML tests

The following methods have been implemented in unit tests:

• fix_sql_params
• get_tables
• get_indexes
• get_columns
• execute
• get_in_or_equal
• fix_table_names
• get_recordset
• get_recordset_list
• get_recordset_select
• get_recordset_sql
• get_records
• get_records_list
• get_records_select
• get_records_sql
• get_records_menu
• get_records_select_menu
• get_records_sql_menu
• get_record
• get_record_select
• get_record_sql
• get_field
• get_field_select
• get_field_sql
• get_fieldset_select
• get_fieldset_sql
• insert_record_raw
• insert_record
• update_record_raw
• update_record
• set_field
• set_field_select
• count_records
• count_records_select
• count_records_sql
• record_exists
• record_exists_select
• record_exists_sql
• delete_records
• delete_records_select

Still to do

The tests need to be expanded to include boundary conditions, including invalid or incomplete parameters.

• How do these methods behave when given invalid params?
• Are exceptions in place, and are they caught appropriately?