#define db_free
//Deletes all the tables and data structures

var key;

key = ds_map_find_first(_db_tables);
repeat (ds_map_size(_db_tables)) {
    db_table_delete(key);
    key = ds_map_find_next(_db_tables, key);
}

ds_map_destroy(_db_tables);
ds_map_destroy(_db_column_names);
ds_map_destroy(_db_indexes);
ds_map_destroy(_db_types);

#define db_init
//Initializes the database creating the required data structures

globalvar _db_tables,_db_column_names,_db_indexes,_db_types;

_db_tables = ds_map_create(); //holds tables
_db_column_names = ds_map_create(); //holds column names
_db_types = ds_map_create(); //holds column types (string or real)
_db_indexes = ds_map_create(); //holds entry ids for fast lookup

#define db_op_equal
//Operation to test equality

var arg;

if(is_real(argument0)) {arg = real(ds_list_find_value(argument1,0))}
else {arg = ds_list_find_value(argument1,0);}

return argument0 == arg;

#define db_op_gt
//Operation to test if a value in the db is greater than the provided value

var arg;

if(is_real(argument0)) {arg = real(ds_list_find_value(argument1,0))}
else {arg = ds_list_find_value(argument1,0);}

return argument0 > arg;

#define db_op_gte
//Operation to test if a value in the db is greater than or equal to the provided value

var arg;

if(is_real(argument0)) {arg = real(ds_list_find_value(argument1,0))}
else {arg = ds_list_find_value(argument1,0);}

return argument0 >= arg;

#define db_op_lt
//Operation to test if a value in the db is less than the provided value

var arg;

if(is_real(argument0)) {arg = real(ds_list_find_value(argument1,0))}
else {arg = ds_list_find_value(argument1,0);}

return argument0 < arg;

#define db_op_lte
//Operation to test if a value in the db is less than or equal to the provided value

var arg;

if(is_real(argument0)) {arg = real(ds_list_find_value(argument1,0))}
else {arg = ds_list_find_value(argument1,0);}

return argument0 < arg;

#define db_op_between
//Operation to test if a value in the db is between two values (inluding the values themselves)

var arg1,arg2;

if(is_real(argument0)) {
    arg1 = real(ds_list_find_value(argument1,0));
    arg2 = real(ds_list_find_value(argument1,1));
}
else {
    arg1 = ds_list_find_value(argument1,0);
    arg2 = ds_list_find_value(argument1,1);
}



return argument0 >= arg1 && argument0 <= arg2;

#define db_op_not_equal
//Operation to test difference

var arg;

if(is_real(argument0)) {arg = real(ds_list_find_value(argument1,0))}
else {arg = ds_list_find_value(argument1,0);}

return argument0 != arg;

#define db_record_delete
//Deletes a record with the provided id
//Arguments: table,id
//Returns: -1 and an error if there is no record with id

var table,index,row;

table = ds_map_find_value(_db_tables,argument0);
index = ds_map_find_value(_db_indexes,argument0);
if(!ds_map_exists(index,argument1)) {
    show_error("db_record_delete: Nessun record trovato con id " + string(argument1),false); 
    return -1;
}
row = ds_map_find_value(index,argument1);

ds_grid_set_grid_region(table,table,0,row+1,ds_grid_width(table)-1,ds_grid_height(table)-1,0,row);
ds_grid_resize(table,ds_grid_width(table),ds_grid_height(table)-1);

ds_map_replace(index,argument1,-1); //set index for the id to -1

db_table_reindex(argument0); //reindex to keep track of grid changes

#define db_record_exists
//Returns true if a value is present somewhere in the table at the specified column
//Arguments: table,column,value

var table,columns,col;

table = ds_map_find_value(_db_tables,argument0);
columns = ds_map_find_value(_db_column_names,argument0);
col = ds_map_find_value(columns,argument1);

return ds_grid_value_exists(table,col,0,col,ds_grid_height(table)-1,argument2);

#define db_record_find
//Returns a list of elements with the specified condition
//Arguments: table,cond_script,condition(column|arg1|argn),sorting(field -1),limit,offset

var table,columns,types,records,limit,offset,sort,sort_order,sort_type,sort_field,cond_args,cond_field,cond_script;

table = ds_map_find_value(_db_tables,argument0);
columns = ds_map_find_value(_db_column_names,argument0);
types = ds_map_find_value(_db_types,argument0);

cond_script = argument1;
cond_args = string_parse(argument2,"|",false);
sort = argument3;
limit = argument4;
offset = argument5;
records = ds_list_create();

//Parse conditions
if(cond_script >= 0) {
    cond_field = ds_map_find_value(columns,ds_list_find_value(cond_args,0));
    ds_list_delete(cond_args,0);
}

//Parse sort params if provided
if(sort != "") {
    var sort_args;
    sort_args = string_parse(sort," ",false);
    sort_order = ds_list_find_value(sort_args,1);
    sort_field = ds_map_find_value(columns,ds_list_find_value(sort_args,0));
    sort_type = ds_map_find_value(types,ds_list_find_value(sort_args,0));
    ds_list_destroy(sort_args);
}

//Find all records matching the condition. 
//If sorting is required, add records to a ds_map, otherwise directly to results list
var i,add_record;
if(sort != "") { //fetch all records for comparison and add to a ds_map
    var records_list,records_queue;
    records_list = ds_list_create();
    records_queue = ds_priority_create();
    for(i=0;i<ds_grid_height(table);i+=1) {
        add_record = true;
        if(cond_script >= 0) {add_record = script_execute(cond_script,ds_grid_get(table,cond_field,i),cond_args);}
        if(add_record) {
            if(sort_type == "real") {ds_priority_add(records_queue,ds_grid_get(table,0,i),ds_grid_get(table,sort_field,i));}
            else {ds_list_add(records_list,ds_grid_get(table,sort_field,i)+"|"+string(ds_grid_get(table,0,i)));}
        }
    }
    
    //fetch sorted results from lists
    if(sort_type == "real") {ds_priority_to_ds_list(records_queue,records,sort_order,limit,offset);}
    else {ds_list_sort_records(records_list,records,sort_order,limit,offset);}
    ds_list_destroy(records_list);
    ds_priority_destroy(records_queue);
}
else {//fetch right number of records directly
    for(i=0;i<ds_grid_height(table);i+=1) {
        add_record = true;
        if(cond_script >= 0) {add_record = script_execute(cond_script,ds_grid_get(table,cond_field,i),cond_args);}
        if(add_record) {
            if(offset > 0) {
                offset -= 1;
                continue;
            }
            ds_list_add(records,ds_grid_get(table,0,i));
            if(limit > 0 && ds_list_size(records) >= limit) {break;}
        }
    }
}

ds_list_destroy(cond_args);

/*if(limit == 1) { //single record requested, return an id
    var r_id;
    if(ds_list_size(records)-offset < 1) {r_id = -1;}
    else {r_id = ds_list_find_value(records,offset);}
    ds_list_destroy(records);
    return r_id;
}
else {
    return records;
}*/

return records;

#define db_record_get
//Returns the content of the specified column for the record with the specified id
//Can be used to access specific row data
//Arguments: table,id,attr

var table,index,columns,i,col,row;

table = ds_map_find_value(_db_tables,argument0);
index = ds_map_find_value(_db_indexes,argument0);
columns = ds_map_find_value(_db_column_names,argument0);

if(!ds_map_exists(index,argument1)) {
    show_error("db_record_get: Nessun record trovato con id " + string(argument1),false); 
    return -1;
}

row = ds_map_find_value(index,argument1);
col = ds_map_find_value(columns,argument2);
return ds_grid_get(table,col,row);

#define db_record_get_row
//Returns a ds_list with a the complete row with id in the table
//The result can be processed with db_row_value to extract the data
//Arguments: table,id

var table,index,result,i;

table = ds_map_find_value(_db_tables,argument0);
index = ds_map_find_value(_db_indexes,argument0);
if(!ds_map_exists(index,argument1)) {
    show_error("db_record_get_row: Nessun record trovato con id " + string(argument1),false); 
    return -1;
}
result = ds_list_create();
row = ds_map_find_value(index,argument1);

for(i=0;i<ds_grid_width(table);i+=1) {
    ds_list_add(result,ds_grid_get(table,i,row));
}

return result;

#define db_record_insert
//Inserts a record in the database table
//Inserts a new record in a table
//Arguments: table,field1,field2,fieldn...

var table,index,i,row,new_id;

table = ds_map_find_value(_db_tables,argument0);
index = ds_map_find_value(_db_indexes,argument0);

if(ds_map_size(index) > 0) {
    new_id = ds_map_size(index)+1;
}
else {
    new_id = 1;
}
row = ds_grid_height(table);

ds_grid_resize(table,ds_grid_width(table),ds_grid_height(table)+1);

ds_grid_set(table,0,row,new_id);
for(i=1;i<ds_grid_width(table);i+=1) {
    ds_grid_set(table,i,row,argument[i]);
}

ds_map_add(index,new_id,row);

#define db_row_value
//Takes a list returned by db_record_get_row and extracts the attribute at the specified column
//Arguments: table,list,column

var columns,i,col;

columns = ds_map_find_value(_db_column_names,argument0);
col = ds_map_find_value(columns,argument2);
return ds_list_find_value(argument1,col);

#define db_record_update
//Updates an attribute of a row
//Arguments: table,id,col,value

var table,index,columns,row,col;

table = ds_map_find_value(_db_tables,argument0);
index = ds_map_find_value(_db_indexes,argument0);
columns = ds_map_find_value(_db_column_names,argument0);
if(!ds_map_exists(index,argument1)) {
    show_error("Nessun record trovato con id " + string(argument1),false); 
    return -1;
}

row = ds_map_find_value(index,argument1);
col = ds_map_find_value(columns,argument2);

ds_grid_set(table,col,row,argument3);

#define db_record_find_first
//Returns the id of the first record having value at the specified column, or -1 if no record found
//Arguments: table,column,value

var table,columns,column,row;

table = ds_map_find_value(_db_tables,argument0);
columns = ds_map_find_value(_db_column_names,argument0);
column = ds_map_find_value(columns,argument1);

if(ds_grid_value_exists(table,column,0,column,ds_grid_height(table)-1,argument2)) {
    row = ds_grid_value_y(table,column,0,column,ds_grid_height(table)-1,argument2);
    return ds_grid_get(table,0,row);
}
else {
    return -1;
}

#define db_table_add_column
//Adds a column to a table
//Arguments: table,column_name,type

var table,columns,types;

table = ds_map_find_value(_db_tables,argument0);
columns = ds_map_find_value(_db_column_names,argument0);
types = ds_map_find_value(_db_types,argument0);

ds_map_add(columns,argument1,ds_map_size(columns));
ds_map_add(types,argument1,argument2);
ds_grid_resize(table,ds_grid_width(table)+1,ds_grid_height(table));

#define db_table_clear
//Deletes all the records of a table and resets the index
//Arguments: table

var table,index;

table = ds_map_find_value(_db_tables,argument0);
index = ds_map_find_value(_db_indexes,argument0);

ds_map_clear(index);
ds_grid_resize(table,ds_grid_width(table),0);

#define db_table_column_names
//Returns a ds_list containing the column names (can be destroyed after usage)
//Arguments: table

var key,list,column_names,queue;

queue = ds_priority_create();
column_names = ds_map_find_value(_db_column_names,argument0);
list = ds_list_create();

key = ds_map_find_first(column_names);
repeat (ds_map_size(column_names)) {
    ds_priority_add(queue,key,ds_map_find_value(column_names,key));
    key = ds_map_find_next(column_names, key);
}

ds_priority_to_ds_list(queue,list,"ASC");
ds_priority_destroy(queue);

return list;

#define db_table_create
//creates a table in the database. 
//If a table already exists with the same name, it is deleted and replaced.
//Column (field) names are string with column|type pairs, 
//where type can be either "real" or "string"
//Arguments: name,n_of_fields,field1|type,field2|type,field_n|type

var table,columns,index,i,types,col;

if(db_table_exists(argument0)) {
    db_table_delete(argument0);
}

table = ds_grid_create(argument1+1,0);
columns = ds_map_create();
types = ds_map_create();

ds_map_add(columns,"id",0) //add default id column
ds_map_add(types,"id","real") //sets id column type to real
for(i=1;i<=argument1;i+=1) {
    col = string_parse(argument[i+1],"|",true);
    ds_map_add(columns,ds_list_find_value(col,0),i);
    ds_map_add(types,ds_list_find_value(col,0),ds_list_find_value(col,1));
    ds_list_destroy(col);
}
index = ds_map_create();

ds_map_add(_db_tables,argument0,table);
ds_map_add(_db_types,argument0,types);
ds_map_add(_db_column_names,argument0,columns);
ds_map_add(_db_indexes,argument0,index);

#define db_table_delete
//Deletes a table in the database
//Arguments: table_name

var table,columns,index,types;

table = ds_map_find_value(_db_tables,argument0);
index = ds_map_find_value(_db_indexes,argument0);
columns = ds_map_find_value(_db_column_names,argument0);
types = ds_map_find_value(_db_types,argument0);

ds_grid_destroy(table);
ds_map_destroy(index);
ds_map_destroy(columns);
ds_map_destroy(types);
ds_map_delete(_db_tables,argument0);
ds_map_delete(_db_indexes,argument0);
ds_map_delete(_db_column_names,argument0);
ds_map_delete(_db_types,argument0);

#define db_table_exists
//Returns true if a table exists
//Arguments: table

return ds_map_exists(_db_tables,argument0);

#define db_table_grid
//returns the complete ds_grid containing the table entries
//Arguments: table

return ds_map_find_value(_db_tables,argument0);

#define db_table_index
//Returns the index (a ds_map of id with their position in the grid) of a table
//Arguments: table

var index;
index = ds_map_find_value(_db_indexes,argument0);

return index;

#define db_table_read
//Read a dump file saved with db_table_write into a table
//Arguments: file

var file,table,index,columns,name,types;

columns = ds_map_create();
table = ds_grid_create(0,0);
index = ds_map_create();
types = ds_map_create();

file = file_text_open_read(argument0);
name = file_text_read_string(file);
file_text_readln(file);
ds_map_read(columns,file_text_read_string(file));
file_text_readln(file);
ds_grid_read(table,file_text_read_string(file));
file_text_readln(file);
ds_map_read(index,file_text_read_string(file));
file_text_readln(file);
ds_map_read(types,file_text_read_string(file));
file_text_close(file);

if(db_table_exists(name)) {db_table_delete(name);}

ds_map_add(_db_tables,name,table);
ds_map_add(_db_column_names,name,columns);
ds_map_add(_db_types,name,types);
ds_map_add(_db_indexes,name,index);

#define db_table_reindex
//Rebuils the index of the table
//Arguments: table

var index,table,i,t_id;

table = ds_map_find_value(_db_tables,argument0);
index = ds_map_find_value(_db_indexes,argument0);

for(i=0;i<ds_grid_height(table);i+=1) {
    t_id = ds_grid_get(table,0,i);
    if(ds_map_exists(index,t_id)) {
        ds_map_replace(index,t_id,i);
    }
    else {
        ds_map_add(index,t_id,i);
    }
}

#define db_table_size
//Returns the number of records in a table
//Arguments: table

var table;

table = ds_map_find_value(_db_tables,argument0);
return ds_grid_height(table);

#define db_table_write
//Saves the provided table to a file
//Arguments: table,path_to_file

var file,table,index,columns,types;

table = ds_map_find_value(_db_tables,argument0);
index = ds_map_find_value(_db_indexes,argument0);
columns = ds_map_find_value(_db_column_names,argument0);
types = ds_map_find_value(_db_types,argument0);

file = file_text_open_write(argument1);
    file_text_write_string(file,argument0);
    file_text_writeln(file);
    file_text_write_string(file,ds_map_write(columns));
    file_text_writeln(file);
    file_text_write_string(file,ds_grid_write(table));
    file_text_writeln(file);
    file_text_write_string(file,ds_map_write(index));
    file_text_writeln(file);
    file_text_write_string(file,ds_map_write(types));
file_text_close(file);

#define db_table_to_csv
//Saves the table to a CSV file. Fields are separated by the separator character provided
//Arguments: table,path,separator

var table,csv,col,row,elem;

table = db_table_grid(argument0);
csv = file_text_open_write(argument1);
elem = ds_list_create();
for(row=0;row<ds_grid_height(table);row+=1) {
    ds_list_clear(elem);
    for(col=0;col<ds_grid_width(table);col+=1) {
        ds_list_add(elem,ds_grid_get(table,col,row));    
    }
    file_text_csv_writeln(csv,argument2,elem);
}
file_text_close(csv);
ds_list_destroy(elem);

#define string_parse
/*
**  Usage:
**      string_parse(str,token,ignore_null)
**
**  Arguments:
**      str         a string with a certain token seperating the
**                  desired substrings,  string
**      token       a string (usually a single variable) representing
**                  the character(s) that str is seperated by,  string
**      ignore_null whether or not to include empty strings if, for
**                  example, the token was repeated,  bool (true/false)
**
**  Returns:
**      a ds_list containing all substrings taken from str which
**      were seperated by token.
**
**  Example:
**      string_parse("cat|dog|house|bee", "|", true);
**      returns a ds_list containing "cat", "dog", "house", and "bee"
**
**  GMLscripts.com
*/
{
    var str, token, ignore, list, tlen, temp;

    str = argument0;
    token = argument1;
    ignore = argument2;

    list = ds_list_create();
    tlen = string_length( token);

    while( string_length( str) != 0) {
        temp = string_pos( token, str);
        if( temp) {
            if( temp != 1 || !ignore)
            ds_list_add( list, string_copy(str, 1, temp - 1));
            str = string_copy( str, temp + tlen, string_length( str));
        }
        else {
            ds_list_add( list, str);
            str = "";
        }
    }

    return list;
}

#define ds_priority_to_ds_list
//Inserts priority queue values into a ds_list
//Arguments: priority,list,order (ASC,DESC),limit,offset

var i,limit;

if(argument4 >= ds_priority_size(argument0)) {exit;}

if(argument3 <= 0) {limit = ds_priority_size(argument0)-argument4;}
else {limit = min(ds_priority_size(argument0)-argument4,argument3);}

if(string_upper(argument2) == "DESC") {
    repeat(argument4) {ds_priority_delete_max(argument0)}    
    for(i=0;i<limit;i+=1) {
        ds_list_add(argument1,ds_priority_delete_max(argument0));
    }
}
else {
    repeat(argument4) {ds_priority_delete_min(argument0)}
    for(i=0;i<limit;i+=1) {
        ds_list_add(argument1,ds_priority_delete_min(argument0));
    }    
}

#define file_text_csv_writeln
//Scrive una riga di valori nel file csv indicato separati dal carattere separatore indicato
//Arguments: file,separatore,elements_grid

var quoted,delimiter,value,i;

delimiter = argument1;

for(i=0;i<ds_list_size(argument2);i+=1) {
    value = string(ds_list_find_value(argument2,i));

    if(string_count('"',value) + string_count(delimiter,value) + string_count(chr(10),value) + string_count(chr(13),value) > 0 ) {
        value = '"' + string_replace_all(value,'"','""') + '"';
    }

    if(i < ds_list_size(argument2)-1) {value += delimiter;}
    
    file_text_write_string(argument0,value);
}

file_text_writeln(argument0);

#define ds_list_sort_records
//Takes a custom ds_list with strings containing value|id pairs and puts sorted values into another list
//Arguments: custom_list,output_list,order,limit,offset

var i,j,record,limit;

if(argument4 >= ds_list_size(argument0)) {exit;}

if(argument3 <= 0) {limit = ds_list_size(argument0);}
else {limit = min(ds_list_size(argument0),argument3+argument4);}

ds_list_sort(argument0,argument2 != "DESC");

for(i=argument4;i<limit;i+=1) {
    record = ds_list_find_value(argument0,i);
    for(j=string_length(record);j>0;j-=1) {
        if(string_char_at(record,j) == "|") {
            ds_list_add(argument1,real(string_copy(record,j+1,string_length(record)-j)));
            break;
        }
    }
}

