Author Topic: ISAM - An appeal  (Read 2456 times)

Offline AlyssonR

  • Advocate
  • Posts: 125
ISAM - An appeal
« on: December 13, 2016, 12:34:30 PM »
(Cross-posted to Script BASIC forum)

I am developing some software for handling a 'museums accessions' database (for my own use), and given the type of data, it calls for nothing more complex than an indexed, flat file.

In order to minimise dependencies and maximise speed, I want to use an ISAM approach, and wish to avoid using an external RDBMS engine (such as BerkleyDB).

I have used ISAM extensively in the past, but I want to save myself from re-inventing the wheel (yet again) - does anyone here have a suitable source module that I could use, or am I going to have to write it from scratch?

Thanks.

Offline John

  • Forum Support / SB Dev
  • Posts: 2191
    • ScriptBasic Open Source Project
Re: ISAM - An appeal
« Reply #1 on: December 13, 2016, 12:45:34 PM »
Microsoft has brought back .BDase file support from what I've read.

The original Script BASIC distribution had a BerkleyDB extension module but I dropped it due to lack of interest by most.

Personally I would suggest investing your efforts in SQLite.

Offline AlyssonR

  • Advocate
  • Posts: 125
Re: ISAM - An appeal
« Reply #2 on: December 13, 2016, 02:16:29 PM »
I should point out that my decision to go with ISAM was not taken lightly, even though this approach to database management is now considered passť (and is often regarded as abandoned).

An important part of my rationale is to remove external dependencies (especially dependencies on Microsoft proprietary stuff) - and to also get away from the tendency of RDBMSs to produce monolithic files.

For the type of data I am handling, both BerkleyDB and SQLite produce severe hits on potential performance, as well as both producing databases as monolithic files. Part of the reporting system needs to simply count through the (single) data table and to produce a line/page of report per record - which is blisteringly fast when decoupled from an RDBMS - a test run on a sorted, unindexed example CSV file of ~10k records took under 2 seconds to compile the typical reports - for later printing (or not, in this case).

The only non-text components in the system will be the image files, and the least readable data will be rich-formatted (either RTF or HTML) items such as chemical formulae, and the reports (RTF files).

The only truely slow activity in an ISAM model is the the routine housekeeping and re-indexing, and both of those can be optimised as a part of the record update process, as is proper.

Apart from indexes, the only other linked files are .memo files (for rich text and 'enormous field' data) and look-up tables (with, perhaps, a dozen options stored in each) - again, a waste of computing power when used in an RDBMS.

SB is supremely adapted to handle ISAM - deconstructing a record into an array in one line of code (and vice versa), and I do so love ISAM (but that could be because I used it so much when I was still a commercial programmer). It does seem a shame to waste that functionality by using an external product.
« Last Edit: December 14, 2016, 03:07:16 PM by AlyssonR »

Offline John

  • Forum Support / SB Dev
  • Posts: 2191
    • ScriptBasic Open Source Project
Re: ISAM - An appeal
« Reply #3 on: December 13, 2016, 02:21:23 PM »
Quote
SB is supremely adapted to handle ISAM - deconstructing a record into an array in one line of code (and vice versa), and I do so love ISAM (but that could be because I used it so much when I was still a commercial programmer). It does seem a shame to waste that functionality by using an external product.

You're right and all you need is an indexing library to go with it.

Offline AlyssonR

  • Advocate
  • Posts: 125
Re: ISAM - An appeal
« Reply #4 on: December 13, 2016, 02:32:50 PM »
I was, frankly, blown away at the speed of the report generation on my overworked laptop.

The indexing is painstaking, but not terribly difficult to write, as are the search and retrieval/update routines. There is, after all, nothing complicated about ISAM - I was just hoping to save myself a good few days of tedium (coding and testing) so that I could get right into the good stuff (migrating away from Access DB).

If no one else has anything already written, then I'll probably release the source-code here for others to play with. I'll even throw in a schema compiler to generate (and document) the file structures  8)
« Last Edit: December 13, 2016, 02:35:44 PM by AlyssonR »

Offline John

  • Forum Support / SB Dev
  • Posts: 2191
    • ScriptBasic Open Source Project
Re: ISAM - An appeal
« Reply #5 on: December 13, 2016, 02:47:13 PM »
Sounds great!

Please keep us in the loop with your progress. You never know who might chime in with advice or a request.

Offline AlyssonR

  • Advocate
  • Posts: 125
Re: ISAM - An appeal
« Reply #6 on: December 13, 2016, 02:57:49 PM »
Will do.

Offline John

  • Forum Support / SB Dev
  • Posts: 2191
    • ScriptBasic Open Source Project
Re: ISAM - An appeal
« Reply #7 on: December 14, 2016, 10:59:31 AM »
Script BASIC supports file region locks and I'm introducing multi-threading in scriba so you might want to consider a record locking feature in your design. I'm wondering if the locks would support multi-user access?

Offline AlyssonR

  • Advocate
  • Posts: 125
Re: ISAM - An appeal
« Reply #8 on: December 14, 2016, 02:47:34 PM »
In-file (soft) locking will be a part of version 2 (once the basic functionality is there) - it is quite simple to implement, and is flexible.

Once the core functions are up, I want to look into complex searches (multiple index searches).

At that point, I am also looking at record, index, chain(predicate) and file locking to be handled from within the add-in as well as multiple styles of record  deletion and other status setting. By handling the locking logic in the file, so to speak, the number of simultaneous users is effectively handled, regardless of whether SB's locking logic transcends threads and/or users.

As for external tools, it will require a schema processor to compile the schema into the appropriate file set, and probably a reschema processor to change the file layout etc.  Housekeeping will need to include re-index and reorganise, as well as various purge and audit-trail functions - not to mention a DB backup/restore/repair facility.