OpenEuphoria

Re: Searching for Data in EDS

• Posted by irv at take.maxleft.com Nov 12, 2002
• 460 views

On Monday 11 November 2002 05:49 pm, you wrote:
>
> Irv, do you save the index to the database?
>
> I use key -1 as the field name record. I'm thinking to use -2 or somesuch
> negative value as the index. Is it proper to store the index in the
> database file itself, or should that be kept separate? I wouldn't think one
> would want to regen the index every time it's opened...

I store the indices as tables in the database. For a more realistic example, 
suppose I have an employee database, and want quick access by lastname,
city, state, zipcode, work location.

My data would be stored in the main table with a unique key (ssn), and I 
would have additional tables for name_idx, city_idx, state_idx, zip_idx, 
loc_idx.

Since EDS has built-in protection against storing dupe keys, I can use very 
simple code to store the index. For example, to create zip_idx, I just read 
the main data table sequentially, extract each zipcode, and use that as a key 
into the zip_idx table. 

If a record with that key doesn't exist, I can add a record:
key = zip
data = {ssn}

if that key already exists, I can load that record, append the ssn to the 
data, and store it back, so the record now looks like:
key = zip
data = {ssn1, ssn2,....}

So you can see that getting all employees in a given zip code, for example,  
is dead simple and quick. 
1. from the zip_idx table, lookup the record with the key (zip)
2. for each ssn in the retrieved data, read the record from the main table 
using the key (ssn)
Tiny fraction of a second, and you're done.

Regenerating the indexes each time the db is opened is not necessary, but 
you do need to write code to update all indexes with each addttion, deletion 
or change. But (based on experience) you should also write re-indexing 
routines, they will come in handy. 

Regards,
Irv