FWD » Core Development » Database

We need testcases that explore the following as comprehensively as possible. The intention is to provide complete coverage of the 4GL compatibility of the "database/persistence" support.

See Database/Persistence Testcases for status of the testcases.

• Assignment (Field) Tests
  • = assignment operator for fields
  • ASSIGN statement database-specific forms
  • batching (how multiple database field assignments differ in an ASSIGN statement versus implementing the same changes in separate assigment statements)
  • NO-ERROR
• Buffer Scoping Tests (make sure to start with the cases in our documentation, scroll down to "Record Scopes", these were used to initially identify the scoping rules)
• Built-in Function (Database) Tests
  • all database built-ins
  • include "special" ones like the various record funcs which take non-regular syntax (things that cannot normally exist in an expression)
  • non-UDFs (database related functions that can only be called from business logic, not in WHERE clauses)
  • UDFs (built-ins used in WHERE clauses) ECF: this may not be different than above, I am including it here to be complete
• CAN-FIND Tests
  • in a WHERE clause, nested at various levels (2, 3, 4)
    • using AND CAN-FIND(...)
    • using OR CAN-FIND(...)
  • outside of a WHERE clause
  • default lock (NO-LOCK)
  • SHARE-LOCK/EXCLUSIVE-LOCK
• CONNECT and DISCONNECT Tests
  • explicit database connect/disconnect
  • connect options
• Cross-Database Join Tests
  • across permanent databases
  • across a permanent database and the temp-table database (note that in the FWD implementation, temp-tables and permanent tables are in separate databases; this affects the conversion of such joins, even if OE handles it differently)
• Dynamic Database Tests
  • queries
  • temp-tables
  • validation expression
  • ::
  • buffer handle
  • buffer-field handle
• Extent Field Tests
  • schema and DMO conversion
  • WHERE clause extent subscripting
    • for both complex expressions and literals
    • out of bounds subscripts (above and below)
    • ranges (e.g. var[1 for 2], to the degree they can be referenced in expressions)
  • range extents (outside of expressions)
  • unsubscripted extent references
  • initialization
  • assignment (including bulk and individual element)
• Flushing and Validation Tests
  • the goal is to have a large variety of tests which together define the behavior of when newly created records, or records read from the database and updated, need to be validated and flushed (back) to the database; we need to understand:
    • are validation and flushing joined at the hip, or are there cases when they are performed separately?
    • what are the conditions under which a newly created record is validated? flushed?
    • what are the conditions under which an updated record is validated? flushed?
    • what is the relationship between validation/flushing of:
      • field updates?
      • index updates?
      • partial index updates?
      • does it differ between newly created records and records read from the database?
    • what are the conditions under which a write triggers should fire (or should not fire), and is this related to validation and flushing?
• Handle (Database) Tests
  • (including all of their attributes and methods)
  • buffer objects
  • buffer fields
  • data-relation
  • data-source
  • query object
• I18N (Database) Tests
  • collation
  • conversion to/from the database charset
  • UTF-8 and other CPs (stick to those we support currently)
• LOB (Database) Tests
  • CLOB
  • BLOB
  • database-related COPY-LOB
• Lock Tests
  • NO-LOCK
  • SHARE-LOCK (including implicit usage)
  • EXCLUSVE-LOCK
  • NO-WAIT
  • how locks get released (implicitly and explicitly via RELEASE), including interactions with buffer scoping
  • how locks get downgraded, including interactions with buffer scoping
  • remote database tests
• Multi-Tenancy Tests
  • TBD how these will look and what 4GL features we will support; currently, we are taking a database-per-tenant approach to implementation, see #6229
• Multi-Database Tests
• Parameter Passing (Database) Tests
  • Modes
    • input
    • input-output
    • output
    • return (where possible)
  • Types
    • buffer
    • dataset
    • dataset-handle
    • table
    • table-handle
  • Other options
    • APPEND
    • BY-VALUE (default), BY-REFERENCE, BIND (for the types that support these options)
  • functions
  • internal/external procs
  • OO methods
  • extents
• ProDataSet Tests
  • data sources
  • data relations
  • datasets
  • before table
  • all related method/attributes
  • scope behavior
  • fill
  • merging updates
  • flushing
  • trigger events
• Query and Query-Integrated Block Tests
  • (all forms including multi-table forms)
  • FIND
    • FIRST/LAST/NEXT/PREVIOUS, plus the implicit unique find
    • FIND literal (i.e., the short form for find the unique record with the literal value on the primary (unique) index)
  • FOR blocks and loops including FIRST, LAST, EACH and no qualifier
  • DO PRESELECT
  • REPEAT PRESELECT
  • OPEN QUERY
    • with and without DEFINE QUERY
    • multiple OPEN QUERY statements sharing same DEFINE QUERY
  • other features
    • USE-INDEX (can affect ordering)
    • SCROLLING queries
      • NEXT, PREV, FIRST, CURRENT and LAST along with QUERY-OFF-END behavior
      • REPOSITION with ROWID, RECID, ROW, FORWARDS and BACKWARDS
      • INDEXED-REPOSITION
      • NUM-RESULTS
    • BREAK keyword generating groups including ACCUM, ACCUMULATE, FIRST-OF, LAST-OF
    • BY clause used for ordering is important
    • complex BY clauses (expressions, not just fields)
    • default ordering if no BY clause exists
      • single-table query on a table (without index)
      • single-table query on a table (with index)
      • multi-table query on tables (with/without index combinations)
      • DESCENDING
    • [LEFT] OUTER-JOIN clause
    • MAX-ROWS limit
    • FIELDS/EXCEPT options
    • all non-FIND types should have some multi-table tests:
      • all permanent tables
      • all temp-tables
      • mixture of permanent tables and temp-tables
• SAVE CACHE Tests
  • ECF: TBD; we implement this feature differently than OE (see Non-Standard Save Cache Implementation)
• Schema Features Tests
  • unique and primary constraints of indexes (is this already part of "Validation" bullet?)
  • mandatory fields (is this already part of "Validation" bullet?)
  • INITIAL, POSITION, ORDER, DECIMALS, DESCRIPTION, LABEL, FORMAT specification of fields
  • schema triggers
• Security (Database) Tests
  • (CA, OM: TBD need test ideas here)
• Sequence Tests
  • minimal/default definition
  • using all options
  • runtime functions/statements to interact with both static and dynamic forms
• Serialization/Deserialization Tests
  • XML
  • JSON
• Shared Resource (Database) Tests
  • buffers
  • queries
  • temp-tables
• Table Copying, Emptying and Comparison Tests
  • COPY-TEMP-TABLE handle based method
  • EMPTY-TEMP-TABLE statement
  • BUFFER-COPY statement and handle based method
    • same table and different tables with different structure
    • various options, including:
      • EXCEPT/USING
      • ASSIGN
      • NO-LOBS
  • BUFFER-COMPARE statement and handle based method
    • same table and different tables with different structure
    • various options, including
      • EXCEPT/USING
      • CASE-SENSITIVE/BINARY
      • SAVE [ RESULT IN ]
      • [ EXPLICIT ] COMPARES
      • NO-LOBS
• Trigger Tests
  • only schema based / only session based / both kind of triggers defined
  • trigger events: CREATE, DELETE, FIND, WRITE and ASSIGN
  • NEW BUFFER and OLD BUFFER in WRITE session-based triggers
  • ensure trigger is fired at the right time: when the buffer is released or the transaction ends (other cases)
  • triggers firing other triggers / how we avoid executing the same trigger in an infinite loop
  • the effect of return error/no-apply in database triggers
  • session based triggers
    • scoping
    • deregistration
    • replacement
• Two-Phase Commit Tests (future; not implemented in FWD yet)
• WHERE Clause Tests (expression processing)
  • literals of all data types and unknown value
  • all field data types (some only are possible in temp-tables)
  • operators including precedence (including CONTAINS and :: which are database-specific)
    • logical OR
    • logical AND
    • bitwise OR
    • bitwise XOR
    • bitwise AND
    • logical NOT
    • bitwise NOT
    • =, EQ, <>, NE, <, LT, >, GT, ≤, LE, ≥, GE, MATCHES, BEGINS, CONTAINS
    • binary +, binary -
    • *, /, MODULO
    • unary +, unary -
    • : and ::
    • ()
  • function calls including:
    • built-in functions (for which we have SQL UDFs)
    • built-ins which can't be UDFs (ECF: aren't there some 4GL built-in functions which cannot be or are not implemented as a database UDF?)
    • functions implemented in 4GL code:
      • which do not reference the query's buffer
      • which do reference the query's buffer
  • handle-based attributes and methods, including chaining
  • system handles, "instance" handles and system handles that are referenced by instance handles
  • OO properties, data members, class event references, method calls including chaining and both instance and static references
• Word Index Tests
  • CONTAINS including full range of wildcard and specs
  • all supported data types and extents

Some of the above items (e.g. ProDataSets) already have testcases and can be included once they are reworked in #6858. I'm just trying to have a complete list that we can use to confirm when we are done.

I expect these tests to be split into smaller functional groupings that can be run on their own. I don't expect a single set of tests which includes all of these categories.

Initial priorities:

• validation/flushing
• triggers (especially write triggers)
• datasets
• open for discussion, in case starting with simpler constructs and building on them makes more sense...