<?php
$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);
//$row_length = $main_db_header[0]['rowlength']; //id, length, datatype
//var_dump($row_length);
//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) {
$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 === "=") {
if ($val === $value) {
array_push($found_matches,$id); //add to new array of matches
}
} elseif ($ptype === ">") {
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); //sort ascending
//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);
$total_main_file_rows = ($main_file_length/$row_length);
//$starting_row = get_starting_row($main_db_file_contents, $parsed_ids[0], $id_length, $row_length, $total_main_file_rows, 0);
$destinations = [];
$prices = [];
$starting_row = 0;
//for ($i = 0; $i < count($parsed_ids); $i++){
for ($i = 0; $i < count($parsed_ids); $i++){
$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);
//var_dump($destination);
array_push($destinations, $destination);
$price = get_field_from_row($main_db_file_contents, $starting_row, $row_length, $price_offset, $price_length);
//var_dump($price);
array_push($prices, $price);
//$starting_row++;
//$starting_row = get_starting_row($main_db_file_contents, $parsed_ids[0], $id_length, $row_length, $total_main_file_rows, $starting_row);
//$starting_row = get_starting_row($main_db_file_contents, $parsed_ids[$i], $id_length, $row_length, $total_main_file_rows, $starting_row);
}
function get_field_from_row($db, $current_row, $row_length, $field_offset, $field_length) {
//$value = substr($db, ($current_row * $row_length) + $field_offset, $field_length);
$value = substr($db, ($current_row * $row_length) + $field_offset, $field_length);
return $value;
}
function get_next_row($db, $id, $id_length, $row_length,$total_main_file_rows, $current_row){
//returns index of row that that has the given id
}
function get_starting_row($db, $first_id, $id_length, $row_length, $total_main_file_rows, $offset){
$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) {
//var_dump($start_row);
print ("\nseeing if ".$first_id." is less than ".$start_row_id);
if ( $first_id < $start_row_id){
print("\nIT LOWER!");
//print ("seeing if ".$first_id." is less than ".$row_id);
//go back a bit less
$start_row = (int)floor($start_row / 2);
//print("\n" . $start_row);
$start_row_id = substr($db, ($start_row) * $row_length , $id_length);
//print("\nstarting row: " . $start_row . "\n");
//print("\n" . $first_id . ", " . $row_id);
} elseif($start_row_id < $first_id) {
//go forward a bit less
//$start_row++; //new
//$start_row = $start_row + (int)round($start_row / 2);
$start_row = (int)ceil($start_row / 2) + $start_row +2;
//$start_row++;
print("\nIT HIGHER!");
//print("\nstarting row: " . $start_row . "\n");
//print("\n" . $start_row);
//print("\n" . $first_id . ", " . $row_id);
} else {
print("\nIT IS THE SAME!");
//print("\nrow found: " . $start_row . "\n");
return $start_row;
}
}
//return $row_id;
}
function get_field_offset($field, $header) {
$total_offset = 0;
foreach ($header as $key => $value){
foreach ($value as $k => $val) {
if ($k != "rowlength"){
if ($field === (string)$k){
//var_dump($k);
return $total_offset;
}
$total_offset += $val['length'] +1; //+1 is needed for single-character delimeter (|)
}
}
}
}
preferences:
62.97 ms | 402 KiB | 5 Q