bindValue(\':'.$field_name.'\', $'.$field_name.', SQLITE3_'.$field_type.');';
}
function array_map_update_table($field_name ) {
return $field_name.'= :'.$field_name;
}
function array_map_create_controller($field_name ) {
return '$'.$field_name.'= filter_input(INPUT_GET, \''.$field_name.'\', FILTER_SANITIZE_SPECIAL_CHARS);';
}
function array_map_create_table($field_name, $field_type ) {
return $field_name.' '.$field_type;
}
function array_map_before($field_name, $before ) {
return $before.$field_name;
}
function array_dollar_before_comma($field_names) {
$str='';
foreach($field_names as $field_name) {
$str.='$'.$field_name.',';
}
return substr($str, 0,-1);
}
function array_map_form_create($field_name, $field_type ) {
$str = '';
$str .= '';
$str .= '
';
return $str;
}
//ORDER IS RESERVED WORD ORDER BY?
$db_schema = array (
array("ITEM_KEY",
array(
array("ITEM_KEY_ID", "INTEGER" ),
array("ITEM_KEY_NAME", "TEXT" ),
array("ITEM_KEY_PRICE", "INTEGER" )
)
),
array("ORDER_HEADER",
array(
array("ORDER_HEADER_ID", "INTEGER" ),
array("ORDER_HEADER_NAME", "TEXT"),
array("ORDER_HEADER_CUSTOMER_CODE", "TEXT"),
array("ORDER_HEADER_SHIPPING_DATE", "INTEGER"),
)
),
array("ORDER_LINE",
array(
array("ORDER_LINE_ID", "INTEGER" ),
array("ORDER_LINE_ITEM_KEY", "TEXT" ),
array("ORDER_LINE_ITEM_KEY_DESCRIPTION", "TEXT" ),
array("ORDER_LINE_ITEM_KEY_QUANTITY", "INTEGER" ),
)
)
);
$sql_model = "";
$create_sql_statement="";
$select_sql_statement="";
$update_sql_statement="";
$delete_sql_statement="";
$drop_create_table_sql="";
foreach ($db_schema as $table) {
$table_name = $table[0];
//echo "TABLE NAME:".$table_name."\n";
$fields = $table[1];
$array_field_names = array();
$array_field_types = array();
foreach($fields as $field) {
$field_name = $field[0];
$field_type = $field[1];
//echo " FIELD NAME:".$field_name."\n";
//echo " FIELD TYPE:".$field_type."\n";
$array_field_names[] = $field_name;
$array_field_types[] = $field_type;
}
$bind_value_array = array_map('array_map_bind_value',$array_field_names,$array_field_types);
$bind_value_array_string = implode(PHP_EOL, $bind_value_array );
$field_name_joined_comma = implode(', ', $array_field_names);
$fill_dollars = array_fill(0, count($array_field_names), '$');
$fill_colons = array_fill(0, count($array_field_names), ':');;
$field_names_joined_dollar_comma = implode(', ', array_map('array_map_before', $array_field_names , $fill_dollars) );
$field_names_joined_colon_comma = implode(', ', array_map('array_map_before',$array_field_names , $fill_colons) );
$field_name_values_joined = array_map('array_map_create_table', $array_field_names,$array_field_types );
$field_name_values_joined_comma = implode(', ',$field_name_values_joined );
$update_header_sql = implode(', ', array_map('array_map_update_table', $array_field_names));
$drop_create_table_sql .= ' $db->exec(\' DROP table IF EXISTS '.$table_name.';\');'.PHP_EOL;
$drop_create_table_sql .= ' $db->exec(\' CREATE TABLE IF NOT EXISTS '.$table_name.' (';
$drop_create_table_sql .= $field_name_values_joined_comma;
$drop_create_table_sql .= ');\');'.PHP_EOL;
$create_sql_statement .= "function DB_CREATE_".$table_name."(";
$create_sql_statement .= $field_names_joined_dollar_comma;
$create_sql_statement .= ') {'.PHP_EOL;
$create_sql_statement .= ' $db = &get_db(SQLITE3_OPEN_READWRITE);'.PHP_EOL;
$create_sql_statement .= ' $prepare_sql = $db->prepare( \'INSERT INTO '.$table_name.' (';
$create_sql_statement .= $field_name_joined_comma;
$create_sql_statement .= ') VALUES (';
$create_sql_statement .= $field_names_joined_colon_comma;
$create_sql_statement .= ')\');'.PHP_EOL;
$create_sql_statement .= $bind_value_array_string.PHP_EOL;
$create_sql_statement .= ' $prepare_sql->execute();'.PHP_EOL;
$create_sql_statement .= ' $db->close();'.PHP_EOL;
$create_sql_statement .= '}'.PHP_EOL.PHP_EOL;
$update_sql_statement .= "function DB_UPDATE_".$table_name."(";
$update_sql_statement .= $field_names_joined_dollar_comma;
$update_sql_statement .= ') {'.PHP_EOL;
$update_sql_statement .= ' $db = &get_db(SQLITE3_OPEN_READWRITE);'.PHP_EOL;
$update_sql_statement .= ' $prepare_sql = $db->prepare( \'UPDATE '.$table_name.' SET ';
$update_sql_statement .= $update_header_sql;
$update_sql_statement .= ' WHERE '.$table_name.'_ID=:'.$table_name.'_ID;\');';
$update_sql_statement .= $bind_value_array_string.PHP_EOL;
$update_sql_statement .= ' $prepare_sql->execute();'.PHP_EOL;
$update_sql_statement .= ' $db->close();'.PHP_EOL;
$update_sql_statement .= '}'.PHP_EOL.PHP_EOL;
$delete_sql_statement .= "function DB_DELETE_".$table_name."($";
$delete_sql_statement .= $table_name."_ID";
$delete_sql_statement .= ') {'.PHP_EOL;
$delete_sql_statement .= ' $db = &get_db(SQLITE3_OPEN_READWRITE);'.PHP_EOL;
$delete_sql_statement .= ' $prepare_sql = $db->prepare( \'DELETE FROM '.$table_name.' WHERE '.$table_name.'_ID = ?\');'.PHP_EOL;
$delete_sql_statement .= ' $prepare_sql->bindValue(1, $'.$table_name.'_ID, SQLITE3_INTEGER); '.PHP_EOL;
$delete_sql_statement .= ' $prepare_sql->execute();'.PHP_EOL;
$delete_sql_statement .= ' $db->close();'.PHP_EOL;
$delete_sql_statement .= '}'.PHP_EOL.PHP_EOL;
//db_SELECT_ORDER_EXACT_ORDER_ID
//db_SELECT_ORDER_EXACT_ORDER_NAME
//db_SELECT_ORDER_LIKE_ORDER_NAME
//db_SELECT_ORDER_RANGE_ORDER_ID
//variables field name, data type, range,exact,like
foreach($fields as $field) {
$field_name = $field[0];
$field_type = $field[1];
//TODO like might need %% quotations?
$search_criteria=array('RANGE','EXACT','LIKE');
foreach($search_criteria as $search_criterion) {
if($search_criterion =='LIKE' && $field_type=='INTEGER') {
continue;
}
if($search_criterion =='RANGE' && $field_type=='TEXT') {
continue;
}
$select_sql_sub_statement = "function DB_SELECT_".$table_name."_".$search_criterion."_".$field_name."(";
if ($search_criterion =='RANGE') {
$select_sql_sub_statement .= '$LOWER_RANGE, $UPPER_RANGE';
} else {
$select_sql_sub_statement .= '$'.$field_name;
}
$select_sql_sub_statement .= ') {'.PHP_EOL;
$select_sql_sub_statement .= ' $db = &get_db(SQLITE3_OPEN_READONLY);'.PHP_EOL;
//need to continue or break out loop otherwise dangling functions TODO
if($search_criterion == 'EXACT') {
$select_sql_sub_statement .= ' $prepare_sql = $db->prepare( \'SELECT * FROM '.$table_name.' WHERE '.$field_name.' = ?\');'.PHP_EOL;
$select_sql_sub_statement .= ' $prepare_sql->bindValue(1, $'.$field_name.', SQLITE3_'.$field_type.'); '.PHP_EOL;
}
elseif ($search_criterion =='LIKE' && $field_type=='TEXT') {
$select_sql_sub_statement .= ' $prepare_sql = $db->prepare( \'SELECT * FROM '.$table_name.' WHERE '.$field_name.' LIKE ?\');'.PHP_EOL;
$select_sql_sub_statement .= ' $prepare_sql->bindValue(1, $'.$field_name.', SQLITE3_'.$field_type.'); '.PHP_EOL;
}
elseif ($search_criterion =='RANGE' && ($field_type=='INTEGER' || $field_type=='REAL' ) ) {
$select_sql_sub_statement .= ' $prepare_sql = $db->prepare( \'SELECT * FROM '.$table_name.' WHERE '.$field_name.' >= ? AND <= ? \');'.PHP_EOL;
$select_sql_sub_statement .= ' $prepare_sql->bindValue(1, $LOWER_RANGE, SQLITE3_INTEGER); '.PHP_EOL;
$select_sql_sub_statement .= ' $prepare_sql->bindValue(2, $UPPER_RANGE, SQLITE3_INTEGER); '.PHP_EOL;
}
$select_sql_sub_statement .= ' $select_query_result = $prepare_sql->execute();'.PHP_EOL;
$select_sql_sub_statement .= ' $array_return = array();'.PHP_EOL;
$select_sql_sub_statement .= ' if( is_a($select_query_result, \'SQLite3Result\' ) )'.PHP_EOL;
$select_sql_sub_statement .= ' {'.PHP_EOL;
$select_sql_sub_statement .= ' while($arrayrow = $select_query_result->fetchArray(SQLITE3_ASSOC) ){'.PHP_EOL;
$select_sql_sub_statement .= ' $array_return[]=$arrayrow;'.PHP_EOL;
$select_sql_sub_statement .= ' }'.PHP_EOL;
$select_sql_sub_statement .= ' }'.PHP_EOL;
$select_sql_sub_statement .= ' $db->close();'.PHP_EOL;
$select_sql_sub_statement .= ' return $array_return;'.PHP_EOL;
$select_sql_sub_statement .= '}'.PHP_EOL;
$select_sql_statement .=$select_sql_sub_statement.PHP_EOL.PHP_EOL;
}
}
}
//copy
//joins children
//check if id exists
//check if name exists etc
//find
$server_connection = <<<'NOWDOC'
function &get_db($read_write_mode) {
$ROOT=''; //save root of web directory TODO somewhere behind public www
//$read_write_mode=SQLITE3_OPEN_READONLY | SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE
$db = new SQLite3($ROOT.'sqlmodels.sqlite3',$read_write_mode);//pdo create sqlite table
$db->busyTimeout(60000);//60 seconds
$db->query('PRAGMA journal_mode = WAL;'); //multiple readers, https://sqlite.org/wal.html
$db->query('PRAGMA busy_timeout = 60000;');//PRAGMA busy_timeout = milliseconds before retry;
$db->query('PRAGMA cell_size_check = ON;'); //database corruption is detected earlier
$db->query("PRAGMA encoding = 'UTF-8';"); //watch php WHERE clause will not work since ISO encoding vs UTF 8,
$db->query("PRAGMA foreign_keys = ON;"); //off by default , each connection must call this
return $db;
}
NOWDOC;
$init_db = 'function init_db() {';
$init_db .= PHP_EOL.'$db = &get_db(SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);'.PHP_EOL.$drop_create_table_sql.'}'.PHP_EOL;
echo $create_sql_statement.PHP_EOL;
echo $select_sql_statement.PHP_EOL;
echo $update_sql_statement.PHP_EOL;
echo $delete_sql_statement.PHP_EOL;
echo $drop_create_table_sql.PHP_EOL;
$txt='';
$myfile = fopen("sql-crud-orm-models.php", "w") ;
fwrite($myfile, $txt);
fclose($myfile);
//PHP forms==============PHP FORMs================
//view tabular table = view-tabular-TABLE.php
//view form table = view-form-TABLE.php
//update table controller = update-row-TABLE.php
//delete table controller = delete-row-TABLE.php
//create table controller = create-row-TABLE.php
//copy table controller = copy-row-TABLE.php
//search filter function
//controllers
foreach ($db_schema as $table) {
$table_name = $table[0];
//echo "TABLE NAME:".$table_name."\n";
$fields = $table[1];
$array_field_names = array();
$array_field_types = array();
foreach($fields as $field) {
$field_name = $field[0];
$field_type = $field[1];
//echo " FIELD NAME:".$field_name."\n";
//echo " FIELD TYPE:".$field_type."\n";
$array_field_names[] = $field_name;
$array_field_types[] = $field_type;
}
$create_row_controller = '';
$select_table_controller = '';
$update_row_controller = '';
$delete_row_controller = '';
$copy_row_controller = '';
$create_row_controller .= '';
$update_row_controller .= '';
$delete_row_controller .= '';
//todeo get rid of id for create update fiunctioms TODO
//join TODO
$filehandle = fopen("create_row_".$table_name."_controller.php", "w") ;
fwrite($filehandle, $create_row_controller);
fclose($filehandle);
$filehandle = fopen("update_row_".$table_name."_controller.php", "w") ;
fwrite($filehandle, $update_row_controller);
fclose($filehandle);
$filehandle = fopen("delete_row_".$table_name."_controller.php", "w") ;
fwrite($filehandle, $delete_row_controller);
fclose($filehandle);
$create_row_form = '';
//
//
$filehandle = fopen("create_row_".$table_name."_form.php", "w") ;
fwrite($filehandle, $create_row_form);
fclose($filehandle);
}