<?php
/*TODO...
parse the sql query
make a query object or something to put almost everything in an object and make this code simpler
return nested name-value pairs in an associative array and convert it to json before sending aa s JSON content type
send all changes made from Deletes, Updates and Append (mutations) changes for passive data binding
*/
$sql = "SELECT * FROM 'main' WHERE 'destination' = 'Helsinki' AND 'price' > 120"; //example of sql query
//the following assumes the above SQL query has been parsed into fields and conditions
//main header file. Stored as JSON.
$main_db_header_contents = '[{"rowlength":60},{"id":{"length":3,"datatype":"integer"}},{"name_sname":{"length":9,"datatype":"String"}},{"identity":{"length":9,"datatype":"integer"}},{"address":{"length":8,"datatype":"String"}},{"region":{"length":7,"datatype":"String"}},{"hours":{"length":5,"datatype":"Time"}},{"destination":{"length":8,"datatype":"String"}},{"price":{"length":3,"datatype":"integer"}}]'; //json string. fairly immutable so it can be cached on server
$main_db_header = json_decode($main_db_header_contents,true);
//main database file. Columns are fixed length and padded. Stored as strings only. ID field must be sorted alphabetically.
//When fields in this file are updated/added to, their respective index file must be too
$main_db_file_contents = "001|Al-Farabi|123456789|Baghdad |Iraq |21:02|Ottawa |120;002|Smith |123456788|Ottawa |Canada |22:03|Helsinki|131;003|Locke |123456787|Helsinki|Finland|23:01|Istanbul|122;004|Jones |123456786|Istanbul|Turkey |21:02|Helsinki|123;005|Steinman |123455785|Havana |Cuba |22:03|Havana |104;006|Booker |123456784|Moscow |Russia |23:01|Helsinki|115;"; //fixed-length field values
//Destination index file with record id and destination value. Sorted alphabetically.
//E.g., The record with the id of 004 has the destination value of 'Moscow '.
$destination_index_file_contents = "005|Havana ;002|Helsinki;004|Helsinki;006|Helsinki;003|Istanbul;001|Ottawa ";
//Destination index file. Sorted alphabetically despite being numberic.
//E.g., The record with the id of 005 has the price value of 104.
$price_index_file_contents = "005|104;006|115;001|120;003|122;004|123;002|131";
function apply_condition($index_file_contents, $ptype, $value) {
//applies basic logical conditions from the the query
//Uses the given index file and checks its value based on given condition and given value
$found_matches = [];
$index_file_contents_split = explode(";", $index_file_contents);
foreach($index_file_contents_split as $key => $props){
$pairs = explode("|",$props);
$id = $pairs[0];
$val = trim($pairs[1]); //removes trailing spaces for matching non-fiexed-length input value
if ($ptype === "=") {
//checks to see if the given value is equal to the value in the index file
if ($val === $value) {
array_push($found_matches,$id); //add to new array of matches
}
} elseif ($ptype === ">") {
//checks to see if the given value is greater than the value in the index file
if ($val > $value) {
array_push($found_matches,$id);//add to new array of matches
}
}
}
return $found_matches;
}
$parsed_destinations = apply_condition($destination_index_file_contents, "=", "Helsinki");
$parsed_prices = apply_condition($price_index_file_contents, ">", "120");
$parsed_ids = array_intersect($parsed_destinations, $parsed_prices);
sort($parsed_ids); //alphabetically sorts array by ascending (lowest to highest)
//now go through $main_db_file_contents and get selected fields in records matching the ids in $parsed_ids.
$id_length = $main_db_header[1]['id']['length']; //id, length, datatype
$destination_offset = get_field_offset("destination", $main_db_header);
$price_offset = get_field_offset("price", $main_db_header);
$destination_length = $main_db_header[7]['destination']['length']; //8
$price_length = $main_db_header[8]['price']['length']; //3
$row_length = $main_db_header[0]['rowlength']; //147
$main_file_length = strlen($main_db_file_contents); //can be done on a text file using the OS
$total_main_file_rows = ($main_file_length/$row_length);
$results =[];
$starting_row = 0;
for ($i = 0; $i < count($parsed_ids); $i++){
//Starting row is advanced (increased) with each loop. It advances half as far as the previous advancement
$starting_row = get_starting_row($main_db_file_contents, $parsed_ids[$i], $id_length, $row_length, $total_main_file_rows, $starting_row);
$destination = get_field_from_row($main_db_file_contents, $starting_row, $row_length, $destination_offset, $destination_length);
$price = get_field_from_row($main_db_file_contents, $starting_row, $row_length, $price_offset, $price_length);
$result = [$parsed_ids[$i], $destination, $price];
array_push($results, $result);
}
function get_field_from_row($db, $current_row, $row_length, $field_offset, $field_length) {
//gets the row index and uses the field's offset (start) and length (end) to get its value in the database text
$value = substr($db, ($current_row * $row_length) + $field_offset, $field_length);
return $value;
}
function get_starting_row($db, $first_id, $id_length, $row_length, $total_main_file_rows, $offset){
//Finds the record that the given ID is located in and returns its row index
$start_row = (int)floor($total_main_file_rows / 2) + $offset; //halfway point of file
$start_row_id = substr($db, ($start_row) * $row_length , $id_length); //gets current row id
while ($start_row > -1 && $start_row <= $total_main_file_rows) {
if ( $first_id < $start_row_id){
//go back a bit less
$start_row = (int)floor($start_row / 2);
$start_row_id = substr($db, ($start_row) * $row_length , $id_length);
} elseif($start_row_id < $first_id) {
//go forward a bit less
$start_row = $start_row + (int)round($start_row / 2);
$start_row_id = substr($db, ($start_row) * $row_length , $id_length);
} else {
//found the row id that matches the given id. Return its index.
return $start_row;
}
}
}
function get_field_offset($field, $header) {
//goes through the JSON file to get length of each field preceding the given field
//used to get the start of the field in the row (a row offset is needed too)
$total_offset = 0;
foreach ($header as $key => $value){
foreach ($value as $k => $val) {
if ($k != "rowlength"){
if ($field === (string)$k){
return $total_offset;
}
$total_offset += $val['length'] +1; //+1 is needed for each single-character delimeter (|)
}
}
}
}
var_dump($results); //works!!