1. RE: EDS Database indexing?
Euman wrote:
> Hello all,
>
> I need to make some changes to an EDS database
> and need some advise.
>
> Lets take an example:
> If I have 8000 records and a user decides to delete record # 6999
> should I leave the key alone and nullify the record space
I would suggest the following.
If the record #'s were a double-linked-list stored in memory then
all you have to do is move pointers when you delete a record. Then
when the file is closed this list would be written with the data
to the file. A Utility program or routine would be executed by the
user to clean the file at his option ( making a backup as it
runs ). Even if the record list gets destroyed in memory the
record's data is still there and can be recovered.
Bernie
2. RE: EDS Database indexing?
> -----Original Message-----
> From: Euman [mailto:euman at bellsouth.net]
> I need to make some changes to an EDS database
> and need some advise.
>
> Lets take an example:
> If I have 8000 records and a user decides to delete record # 6999
> should I leave the key alone and nullify the record space
> or should I rewrite the database moveing all of the records
> from the deleted record # 6999 to #8000 back one key.
Personally, I'd just delete the entire record. As far as EDS is concerned,
the records are 'automatically' moved up by one. Don't confuse the record
number with the record key. EDS automatically 'sorts' the records by their
keys.
> The only difference would be the time to re-write the data
> which really doesnt matter to me cause there is likely not going
> to be more than 40-50k records. What concerns me is
> latter down the road if I chose to use a ListView control
> or a control that returns toplevel down record index's.
> The second thing that concerns me is re-writeing the data,
> would I risk the loss of data? thats what I asked myself.
>
> I need alittle advise how to handle the key/data protions of
> a mid sized database that compensates for records deletion
> and re-indexing.
I'm a little confused regading your keys. Are you using an [auto]number for
the key? Also, what exactly are you referring to with regards to
re-indexing? I don't understant your use of the word 'index'. Do you mean
key, or maybe record number? I don't understand the LIstView scenario
either.
Are you creating a [pseudo]relational database? In other words, are you
relating the keys in your table to data in other tables? If so, why would
you want to change the keys?
Is there a reason to have 'contiguous' keys? I think you're making more
work for yourself.
Matt Lewis
3. RE: EDS Database indexing?
- Posted by gertie at ad-tek.net
Jun 05, 2001
On 5 Jun 2001, at 22:26, Bernie Ryan wrote:
>
>
> Euman wrote:
> > Hello all,
> >
> > I need to make some changes to an EDS database
> > and need some advise.
> >
> > Lets take an example:
> > If I have 8000 records and a user decides to delete record # 6999
> > should I leave the key alone and nullify the record space
>
> I would suggest the following.
> If the record #'s were a double-linked-list stored in memory then
> all you have to do is move pointers when you delete a record. Then
> when the file is closed this list would be written with the data
> to the file. A Utility program or routine would be executed by the
> user to clean the file at his option ( making a backup as it
> runs ). Even if the record list gets destroyed in memory the
> record's data is still there and can be recovered.
I agree wholeheartedly, never erase a database you make a change to! Instead,
rewrite
changes to a new name, use flush(), and then *byte verify* the new file, *then*
rename
the old file. Then be paranoid and verify the old file is renamed properly! A
minute spent
in automated verification can save you a week in doing manual data recovery.
Back
before i bought a battery-powered UPS here, the puter power would go out several
times a day, at random times, for up to an hour, so believe me, while the data
on the
*harddrive* is saf*est*, verify it anyhow! Don't trust floppies, ever! And
*never* use the
recovery option in windoze's scandisk or chkdisk,, they *will* trash the file at
the point
you least expect it and can least afford it.
<ramble mode on> (you thought it was already on?)
Btw, "here" = Alabama, usa. The electricity delivery system got somewhat better
after
the ice storm of 1993(?) left us without power for 10 days and Georgia Power
came in
to fix the lines. Since then, Alabama Power has had time to screw up the system
again, and the UPS beeping is nearly a daily event again. How bad is it? Well, i
have
10 hrs of battery reserve, and then the baby gasoline-powered generator kicks in
to
recharge the battery. I am working on solar power tho, i hate that essentially i
am
paying a ethics-deprived power company to pollute my air to generate electricity
for
me, when it's possible i can do it myself cleaner and freeer (freer? free-r?
free'r?). 
)
Kat
4. RE: EDS Database indexing?
> -----Original Message-----
> From: Euman [mailto:euman at bellsouth.net]
> Thanks Bernie and Matt,'
>
> Matt, the key is what I refer to as the index
>
> suppose I have a key #100 that has data that says it's a parent of
> key #105 and the data in #105 has data that says #100 is the
> parent and #115 is a child of #105 meaning key #100 is the
> grandparent.....(like a family tree, best example I could
> come up with.)
>
> if the user decided to delete #105 for instance, #100 when
> asked for the
> child record would return null
> instead of the sequence of data for key #106 since #105 is no
> longer there.
Again, I think you can simply delete the record, but you have to keep in
mind the difference between the record number (which is used to actually
fetch the record) and the record key (which is used to find the record
number).
Suppose we deleted the record with the key = 105, and the record with key
100 still points to 105 as it's 'child'. Now we pull 100 for some reason,
and want to also retrieve its children. The record's data tells us that 105
is a child, and here's what I'd recommend:
-- assume we've opened the database and selected our table
atom child, recnum, key
object data
key = 100
recnum = db_find_key( key )
if recnum < 0 then
-- no record!
return -- or whatever's appropriate
end if
data = db_record_data( recnum )
child = data[1] -- or however that piece of data is stored
-- now child holds the key value for the child
recnum = db_find_key( child )
if recnum < 0 then
-- no child!
return -- or whatever's appropriate
end if
data = db_record_data( recnum )
Obviously, I'm not checking for no child, etc, but I think you get the idea.
Basically, if the child has been deleted (or never existed), you'll get a
negative record number, and can disregard it. This might also be a good
time to clean your record to remove the reference to the non-existent child
record.
But should we delete the references to the deleted record? This is what's
known as referential integrity, and the answer is that we really should,
although I suppose you might have reasons for not, it's generally considered
good form to maintain integrity. In your example above, this would mean
that when you delete #105, you should alter the data in #100 to no longer
point to #105 (assuming that it did in the first place).
Of course, now the question is, how would you know that 100 points to 105?
Maybe you also record the inverse relationship in the child (ie, point to
the parent). This would make traversing your tree of data easier, but would
increase the storage space, and might not make sense for your application.
You could scan all records looking for said reference, but this wouldn't be
a good solution, unless you didn't have much data to look at.
Another option would be to maintain a separate index in another table. By
index, I'm really talking about something like an associative list. You
could store all indices on tables in one table, or create tables for each
one. Let's look at creating a separate table for each, since this puts more
work onto the shoulders of EDS.
Create a table named PARENT, and one named CHILD. PARENT will be a table
used to track the parents of records, and CHILD will track the children.
So, for your example, you might have these entries in PARENT:
105: {100}
115: {105}
and in CHILD:
100: {105}
105: {115}
We could simply add elements to the sequences that make the record data for
multiple children or parents. Now, if we wanted to delete 105, we'd be able
to tell pretty easily that it is a child of 100, and has 115 as a child. In
fact, we wouldn't need to store this data in our main table at all, but we'd
have to be careful that we updated our index tables PARENT and CHILD each
time we changed something. We also would probably want to create rules
about what could be deleted, or what to do about deleted records. So if
we've deleted 105, do we disallow the action because it has a child? If we
allow it, do we make 100 the parent of 115, or do we make 115 have no
parent?
Currently, I've got a system devised to do similar things in EuSQL, although
all indices are stored as records a single table, using Jiri's a-list code
to manipulate things. I've run into a stumbling block in making things
efficient in running queries (ie, how to combine the indices with
conditional clauses and joins among tables).
Hope this helps,
Matt Lewis
