OpenEuphoria

Re: Setting up a simple database using EDB

• Posted by Pete Lomax <petelomax at blueyonder.co.uk> Jan 28, 2005
• 435 views

On Thu, 27 Jan 2005 11:41:54 -0800, Alex Caracatsanis
<guest at RapidEuphoria.com> wrote:

I recommend trying Matt's EuSQL (see his post), but I am also
compelled to write about using database.e direct.

>I need to create a database of CUSTOMERS. Each customer has a unique number -
>ID - going sequentially from #0001 to #9999. We record the SURNAME, FORENAME,
>MIDDLE_NAME, DATE_OF_BIRTH, ADDRESS, and TEL_NO.
>
>Using EDS, I think it would be correct to make CUSTOMERS the TABLE - yes?
>What should the KEY be? The ID sounds ideal, but then how do we search for 
>customer Smith, John James; born 01/01/2005; at ADDRESS; etc... ?

First you make a primary table called customers:
	key=ID, Data={Surname,Forename,Middlename,DOB,Address,Phone}

You then write significant chunks of the application, but always
update the database though a handful of trivial routines, eg:
insertCust(), updateCust(), deleteCust(), etc. Most of these are
one-liners, but the important point is they are all in one place.

>Sometimes we need to find a particular ID (Eg: "Get the details for customer
>#00345"). But usually we've forgotten his ID, and our question is "Get the
>details for Smith - I forget his first name". Occasionally we're left a 
>garbled message to ring TEL_NO xx xxxx xxxx. Our task then becomes "Get the
>details for the customer with that number".

As the project progresses, you might find that getting a customer by
telephone number is not that important, so you can just read from
start to finish, looking for a match. But you might find that reading
by surname (especially eg if you want to list in surname order) will
benefit greatly from a surname key, so add:

	key={Surname,ID} Data=0

ID makes it unique, and you should /never/ unnecessarily duplicate the
Data from the primary record (not deliberately, anyway).

You can add these secondary tables any time you like, though you will
need to write some code along the lines of (pseudocode):

	if not db_select_table(blah) then	-- first time, create index
		db_create_table(blah)
		db_select_table(CUSTOMER)
		for i=1 to db_table_size() do
			db_select_table(CUSTOMER)	--important!
			ID=db_record_key(i)
			y=db_record_data(i)
			db_select_table(blah) -- also left at end for
			db_insert({y[n],ID},0)
		end for
	end if		
	db_record_key(...)
	...

and change your insert(), update(), and delete() routines to maintain
the two tables in parallel, eg:

	deleteCust(ID)
		db_select_table(CUSTOMER)
		k=db_record_key(ID)
		y=db_record_data(k)
		db_delete_record(k)
		db_select_table(SURNAMEIDX)
		k=db_record_key({y[n],ID})
		db_delete_record(k)

High-level databases (such as EUSQL) handle a fair amount of this
automagically, but they don't actually do it any more efficiently.

>So what should be the KEY, and what should be the DATA, in a database that
>will make it possible for us to find that customer using any of his details?
>And what routine(s) will make that search possible?

You might use k=db_find_key({Surname,0}), which will set k negative,
so you can use db_record_key(-k) to find the next entry, and carry on
from there, fetching the primary data and checking it carefully.

EuSQL is almost certainly a more flexible route, but I hope at least
part of this gave some understanding of the inner workings.

Regards,
Pete