OpenEuphoria

1. EDB Sorting

• Posted by tbohon Dec 28, 2015
• 1329 views

I'm working with an EDB file and have the need to sort it by a particular column. I've looked at the documentation twice and don't find anything specifically about sorting an EDB file, just general sorting routines.

Does anybody have any thoughts on a efficient way to sort an EDB file? I've thought about retrieving the field to be sorted by along with the key field, sorting that and creating a new database (after renaming the original) in the correct order. I've also considered retrieving the entire database into an array, sorting the array and then replacing the existing database records. Neither excites me a lot and I can't help but think that someone may have already solved this issue.

And yes I could use SQLite but this is an effort to fully understand the Euphoria database system.

Thanks in advance for any thoughts or suggestions.

Tom

3. Re: EDB Sorting

• Posted by ghaberek (admin) Dec 28, 2015
• 1320 views

As Matt pointed out, you can use custom_sort() to do the sorting. You could also build just a sequence of keys sorted by column, if that's all you need. One drawback with this is that you'll be fetching the data values (the "columns") over and over again to do the comparison as you sort the sequence of keys. So this probably isn't the fastest method. But if you have a lot of data, this does save you from having to load all of the data into memory at once to sort the whole sequence.

include std/eds.e 
include std/filesys.e 
include std/sort.e 
 
function column_compare( object key_a, object key_b, integer column ) 
 
    sequence data_a = db_fetch_record( key_a ) 
    sequence data_b = db_fetch_record( key_b ) 
 
    return compare( data_a[column], data_b[column] ) 
end function 
 
function db_sort_keys( integer by_column, integer order = NORMAL_ORDER ) 
 
    integer count = db_table_size() 
    sequence keys = repeat( 0, count ) 
 
    for i = 1 to count do 
        keys[i] = db_record_key(i) 
    end for 
 
    return custom_sort( routine_id("column_compare"), keys, {by_column}, order ) 
end function 
 
procedure main() 
 
    db_create( "demo.edb" ) 
    db_create_table( "demo" ) 
 
    db_insert( 1, {"apple"} ) 
    db_insert( 2, {"orange"} ) 
    db_insert( 3, {"grape"} ) 
    db_insert( 4, {"banana"} ) 
    db_insert( 5, {"melon"} ) 
    db_insert( 6, {"pear"} ) 
 
    sequence keys = db_sort_keys( 1 ) 
 
    for i = 1 to length( keys ) do 
        sequence data = db_fetch_record( keys[i] ) 
        printf( 1, "%d : %s\n", {keys[i],data[1]} ) 
    end for 
 
    db_close() 
    delete_file( "demo.edb" ) 
 
end procedure 
 
main() 

1 : apple 
4 : banana 
3 : grape 
5 : melon 
2 : orange 
6 : pear 

-Greg