aql

From SkyPHP

AQL is a MVC-inspired class for interacting with a database.

Contents 1 PHP Functions 2 Javascript Functions 3 Syntax 4 Models 5 Common Usage Examples 6 Known Bugs & Issues

PHP Functions

• aql::select() - retrieve an array of records
• aql::insert() - insert a new record
• aql::update() - update a record
• aql::profile() - retrieve a single record
• aql::value() - retrieve a single value
• aql::save() - insert and/or update records to multiple tables (and link foreign keys)
• aql::grid() - display a data grid of values
• aql::sql() - convert an AQL statement to SQL(s)
• aql::dd() - display an HTML dropdown using values from the database
• aql::get_aql() - get a model's AQL statement
• aql::get_required() - get a model's required fields
• aql::singular() - get a model's singular title
• aql::plural() - get a model's plural title

Javascript Functions

• save_form() - ajax submit a form and save to database if no validation errors (and optionally redirect to another page)

Syntax

Another table can be left joined to the primary table

• see database schema conventions

primary_table {
    field1,
    field2 as field_name
}
another_table {
    field3
}

Models

Use AQL to build listing pages and profile pages. Start by creating a model.

Common Usage Examples

1. Display the email address for everyone in the database named 'Hamlet'

$rs = aql::select("
    person {
        fname, lname,
        email_address
        where fname ilike 'hamlet'
        order by email_address asc
    }
");
foreach ( $rs as $person ):
    echo $person['email_address'] . '<br />';
endforeach;

2. Same as above example, except use the clause array

$rs = aql::select(
   "person {
        fname, lname,
        email_address
    }",
    array(
        'where' => "fname ilike 'hamlet'",
        'order by' => 'email_address asc'
    )
);
foreach ( $rs as $person ):
    echo $person['email_address'] . '<br />';
endforeach;

3. Same as above example, but use the aql from the 'person' model

$rs = aql::select( 'person', array(
    'where' => "fname ilike 'hamlet'",
    'order by' => 'email_address asc'
));
foreach ( $rs as $person ):
    echo $person['email_address'] . '<br />';
endforeach;

4. Display the email address of the person currently logged in

echo aql::value( 'person.email_address', PERSON_ID );

Known Bugs & Issues

• 'id' in a model causes a problem when saving add-new form
• Encrypted id's in the 'where' portion of the clause array are not automatically decrypted. However, encrypted id's that appear in the actual aql string are decrypted automatically.
• Need the ability to join same table twice using table alias
• Need ability to nest more than just a single one-to-many multi-dimensional recordsets
• Need to make it so ambiguous field name error is not possible
• end_time - start_time as duration <-- could potentially have 'ambiguous' column error
• (end_time - start_time) as duration <-- is incorrectly interpreted as function because it contains ()
• Need ability to save multiple records in the same table. perhaps a syntax like <input name="venue_name[aQ3a46ZsgiI3]" ...>
• need ability to "update T set F = F + 1"
• eliminate the possibility of an unjoined table cartesian -- return error msg
• can't update field_ide values
• components do not appear if there is a line break in the 'join on' expression before the {
• when using an aggregate function, i.e. count(*), the automatic primary key must be removed also from joined tables -- not just primary table
• table { where id = 1 } on a single line causes an error: table.where does not exist
• aql::save doesn't save the last field in a model unless there is a comma after the last field: get_field_name doesn't find it.
• need to create a class called tinymce
• insert_time, update_time, mod__person_id
• join cache table -- add parameter to aql::select that will refresh the join cache
• in a model, don't specify primary_table_id as a field in a joined table or a null left join will cause problems
• can't have a nested table that is also in the main query
• automatic validation should prevent 'value too long for varchar'
• missing closing } crashes aql::select()
• null integer saves as zero
• block aql from inserting/updating id field
• aql::select adds an offset clause if you have: where parent__table_ide = $table_ide
• ability to use a comma between table blocks to create a new sql query recordset instead of left joining the latter table block
• plain text models