Small database got indexes

[The Beez]

Hi 4tH-ers!

Today is the end (at least for me) for a long search to add indexes to the DBM.4TH module. This is a small database library, which allows you to handle small database for up to tens of thousands of records. Up to now the only thing to make it barely usable was the DBMSORT.4TH module. I agree, it was not too elegant, but it was a stopgap solution to make it work.

Today I added indexes. Indexes are made in dynamic memory. You can create an index from scratch by using the IDX.CREATE word. The index is added to the currently active database, e.g.

struct
   8 +field Rank                       \ world rank (2014)
  64 +field Name                       \ name of the building
  32 +field City                       \ city location of building
  16 +field Country                    \ country of this city
   8 +field Height_m                   \ height in meters
   8 +field Height_ft                  \ height in feet
   8 +field Floors                     \ number of floors
   8 +field Built                      \ year building was constructed
end-struct /Skyscraper

/Skyscraper buffer: (Skyscraper)       \ allocate room for buffer

(Skyscraper) /Skyscraper s" skyscrpr.tst" db.declare to Skyscraper
Skyscraper db.use                      \ declare and use it

1000 {cell} db.key Height_ft idx.create abort" Cannot create index"

This will create an index on the "Height_ft" field and treat it as a numeric field when sorting or searching. Searching is done by a binary search, so it will take only 16 I/O's on a 64K records file. It will leave a flag and an address, which is also its identifier. Displaying a file in any order is dead easy, e.g. (index address is TOS):

dup idx.first
begin
  dup idx.error 0=
while
  db.buffer -> Name count type cr
  dup idx.next
repeat dup idx.clear

Reverse is easy as well:

dup idx.last
begin
  dup idx.error 0=
while
  db.buffer -> Name count type cr
  dup idx.previous
repeat dup idx.clear

Finding an entry is just as simple:

dup s" Yingli Tower" rot idx.find
if db.buffer -> Name count type else ." Not found" abort then cr

You can save, close and reload it:

dup s" names.txt" idx.save
idx.close abort" Cannot close index"
{char} s" names.txt" idx.load abort" Not a valid index"

Updating an index is simple, just performs the database action FIRST e.g. inserting (two indexes, TOS and 2OS):

begin
  refill                               \ read next CSV record
while
  db.clear                             \ clear the DB buffer
    Field> db.buffer -> Rank      place
    Field> db.buffer -> Name      place
    Field> db.buffer -> City      place
    Field> db.buffer -> Country   place
    Field> db.buffer -> Height_m  place
    Field> db.buffer -> Height_ft place
    Field> db.buffer -> Floors    place
    Field> db.buffer -> Built     place
  db.insert                            \ read fields and insert record
  dup  idx.insert drop                 \ drop "duplicate" flag
  over idx.insert drop                 \ drop "duplicate" flag
repeat

Of course, you can also extend an index, as long as sufficient dynamic memory is available. I really wanted this one for this release. Note that DBMSORT.4tH will be depreciated when the new library is fully tested and included.

If you used DBMSORT.4TH before, I suggest you download it (in SVN) and try to convert your current sources. If you bump into any problems, I'd like to know of course.

When everything is done, I will be updating the documentation and prepare for a new release. It's about time, isn't it?

Hans Bezemer

[The Beez]

On Sunday, March 22, 2015 at 6:46:49 PM UTC+1, The Beez wrote:
I've been busy testing the stuff and apart from some minor bugs (fixed), it works exactly as advertised. Then I set down and contemplated how I could explain it to you guys. The DBMSORT.4TH code was quite murky and ugly, so I wasn't quite sure. But it proved to be much easier than I anticipated, having converted a few programs myself.

Here is a table from the upcoming manual giving you a few pointers on how to tackle it. Sorry for the screenshot, but I saw no possibility on the short notice how to do it otherwise without having to resort to some major formatting.

Note that when you REALLY don't want to convert your programs, you can continue to use the old stuff - it compiles just fine. However, I won't be distributing nor supporting it anymore.

Hans Bezemer

[The Beez]

On Sunday, March 22, 2015 at 6:46:49 PM UTC+1, The Beez wrote:

I've just dumped DBMSORT.4TH into the trash. I converted all programs, rewrote all documentation, so any trace of it is now eradicated from my world. It was ugly, evil and cumbersome. Indexes are so much nicer to handle!

Hans Bezemer

[The Beez]

On Friday, March 27, 2015 at 7:26:58 PM UTC+1, The Beez wrote:
I've used this library yesterday for some real world application - which is always a risk, because people are waiting for functionality. Apart from some usability issues (which I worked around and fixed last evening and this morning) it works just fine - even with thousands of records. There is no noticeable deterioration of performance, apart from initially building the indexes. Which takes a few seconds anyway.

I think what I built yesterday will help the workfloor in a big way - thanks to 4tH! Without it, I could not have made this in a single day. Of course, the very, very large library helps big time.

Hans Bezemer

[The Beez]

On Thursday, April 9, 2015 at 8:51:43 AM UTC+2, The Beez wrote:
I've added the last program I wanted to add for this release, which is a database query program. You can create a small database with all the worlds skyscrapers and query it by commands like:

  find name empire state building (yes, it will show you that one)
  next 2 city (next 2 buildings in New York)
  first 3 built (gives you the three oldest skyscrapers)
  last 3 height (gives you the three highest skyscrapers)

It's fun!

Hans Bezemer