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

AlyssonR

  • Guest
ISAM - An appeal
« on: December 13, 2016, 08:34:36 pm »
(Cross-posted to All 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.

steven

  • Guest
Re: ISAM - An appeal
« Reply #1 on: December 14, 2016, 12:45:51 am »
since you do not want a external database, the only option would be a flatfile database which depending on the database size might not be great performance.

I honestly think sqlite might be the best option for you, it is super easy to use, there are a few examples on this forum.
 

AlyssonR

  • Guest
Re: ISAM - An appeal
« Reply #2 on: December 14, 2016, 11:13:35 pm »
As I said on the All BASIC forum:

Quote
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.

Quite apart from which, for a database that contains a single datafile, a RDBMS is using an inefficient sledgehammer to crack a walnut, when the appropriate, precision nutcracker, ISAM, already exists (if only in potentio). I am quite willing to write the sotware, but I would rather not go re-inventing the wheel if I can help it.

Support

  • Administrator
  • *****
  • Posts: 1
    • View Profile
Re: ISAM - An appeal
« Reply #3 on: December 18, 2016, 05:47:22 pm »
Here is the Script BASIC include file for the BerkleyDB extension module that hasn't been updated in years. If you would like a zip of the extension module source for it, let me know.

Quote from: BDB Documentation
This document describes how to install and use the module BDB developed to help programmers writing database-handling programs in ScriptBasic. The module BDB is based on the Berkeley Data Base from Sleepy Cat Software Inc.

There are separate documents that describe the language (Users’ Guide), the major architecture of the source code (Source Guide).

This document describes the version 2.0 of the module.

The Berkeley Database (bdb) is a set of database handling routines. The program using the bdb directly calls these routines. There is no SQL server or a database daemon. In this sense this is a library collection that can handle simple database structures.

Although it is cumbersome to write complex database application using the Berkeley database lacking SQL interface it pays back on performance.

The Berkeley Database is available on Windows NT as well as on UNIX. It supports transaction handling and is it capable handling really huge databases. Therefore it is an ideal choice for several types of application where there is no need to separate the application from the database.

The bdb module developed for ScriptBasic provides an interface to a subset of the Berkeley Database functions. Using the module the BASIC program can use database handling functions, and transactions. On the other hand BASIC programs can not directly use the shared memory management functions, or locking (only through transactions). Application programmers needing these functions should rather consider developing their own ScriptBasic module using the language C delivering their application specific functions using the underlying Berkeley Database functions.

The functions available to BASIC programmers allow database creation, inserting, deleting, updating, searching data element with single or duplicated keys, start, commit and abort transactions. Some functions are simplified. The BASIC interface does not resemble to the C API of the Berkeley Database. Rather it is a BASIC language database-handling interface that happens to use the Berkeley Database.


'
' FILE: bdb.bas
'
' This is the module declaration of the ScriptBasic external module bdb
'
' To use this module you have to have bdb.dll or bdb.so installed in the
' modules directory.
'
' These implement the interface to the Berkeley Data Base library

module bdb

' Error codes
Const ErrorInvalidFileName      = &H80001
Const ErrorInvalidDbHandle      = &H80002
Const ErrorTransactionnotClosed = &H80003
Const ErrorTransactionNotOpened = &H80004
Const ErrorKeyNotFound          = &H80005
Const ErrorIncomplete           = &H80006
Const ErrorKeyEmpty             = &H80007
Const ErrorKeyExist             = &H80008
Const ErrorLockDeadLock         = &H80009
Const ErrorLockNotGranted       = &H8000A
Const ErrorNotFound             = &H8000B
Const ErrorOldVersion           = &H8000C
Const ErrorRunRecovery          = &H8000D
Const ErrorDeleted              = &H8000E
Const ErrorNeedSplit            = &H8000F
Const ErrorSwapBytes            = &H80010
Const ErrorTxnCkp               = &H80011


' Table types
Const BTree   = &H01
Const Hash    = &H02
Const Recno   = &H04
Const Queue   = &H08
Const Unknown = &H10

' put flags
Const Append      = &HFFFFFFFE
Const NoOverWrite = &HFFFFFFFD

' Table opening flags
Const Create   = &HFFFFFFFE
Const NoMap    = &HFFFFFFFD
Const RdOnly   = &HFFFFFFFB
Const Thread   = &HFFFFFFF7
Const Trunc    = &HFFFFFFEF
Const New      = &HFFFFFFDF

' open the database
' DB = bdb::Open(DataBase,type,flags,unixmode)
declare sub ::Open alias "sb_db_open" lib "bdb"

' close the DB
' bdb::Close DB
declare sub ::Close alias "sb_db_close" lib "bdb"

' put a new, possibly duplicated key/value pairinto DB
' bdb::Put DB,key,value
declare sub ::Put alias "sb_db_put" lib "bdb"

' update the last accessed record
' bdb::Update DB,value
declare sub ::Update alias "sb_db_update" lib "bdb"
' delete the last accessed record
' bdb::DeleteRecord DB
declare sub ::DeleteRecord alias "sb_db_eracrec" lib "bdb"

' get value matching the key from DB
' if there are more than one values for the same key return the first one
' value = bdb::Get(DB,key)
declare sub ::Get alias "sb_db_get" lib "bdb"
' value = bdb::First(DB,key) or value = bdb::First(DB)
declare sub ::First alias "sb_db_get" lib "bdb"

' get the last value from DB
' value = bdb::Last(DB)
declare sub ::Last alias "sb_db_last" lib "bdb"

' get the next value for the key or undef if there are no more
' value = bdb::Next(DB,key)
declare sub ::Next alias "sb_db_next" lib "bdb"
' get the previous value for the key or undef if there are no more
' value = bdb::Previous(DB,key)
declare sub ::Previous alias "sb_db_prev" lib "bdb"

' delete all records for the given key
' bdb::DeleteAll DB,key
declare sub ::DeleteAll alias "sb_db_del" lib "bdb"

' get the key of the last accessed record
' bdb::Key(DB)
declare sub ::Key alias "sb_db_key" lib "bdb"

' transaction handling, no arguments
declare sub ::BeginTransaction alias "sb_db_transact" lib "bdb"
declare sub ::CommitTransaction alias "sb_db_trcommit" lib "bdb"
declare sub ::EndTransaction alias "sb_db_trcommit" lib "bdb"
declare sub ::AbortTransaction alias "sb_db_trabort" lib "bdb"

' delete a database table
' bdb::Drop "databasename"
declare sub ::Drop alias "sb_db_remove" lib "bdb"

end module
 
« Last Edit: December 18, 2016, 07:24:06 pm by support »

Support

  • Administrator
  • *****
  • Posts: 1
    • View Profile
Re: ISAM - An appeal
« Reply #4 on: December 18, 2016, 08:01:27 pm »
I just downloaded and built from source the current BerkleyDB  (without encryption)  I'm going to try and compile the BDB extension module on Linux 64 bit and see if it will work with the current library.

I gave it a shot but the dbenv structure/functionality has changed and the Script BASIC interface.c for the extension module needs upgrading.

Quote from: Oracle
DBENV structure changes:

    The db_errcall, db_errfile, db_errpfx and db_paniccall fields of the DBENV structure have been deprecated and their functionality replaced by the DBENV->set_errcall, DBENV->set_errfile, DBENV->set_errpfx and DBENV->set_paniccall methods.
    The db_verbose field of the DBENV structure has been replaced by the DBENV->set_verbose method.
    The lk_conflicts, lk_detect, lk_max and lk_modes fields of the DBENV structure have been replaced by the DBENV->set_lk_conflicts, DBENV->set_lk_detect and DBENV->set_lk_max methods.
    The lg_max field of the DBENV structure has been replaced by the DBENV->set_lg_max method.
    The mp_mmapsize and mp_size fields of the DBENV structure have been replaced by the DBENV->set_cachesize and DBENV->set_mp_mmapsize methods.
    The tx_info, tx_max and tx_recover fields of the DBENV structure have been replaced by the DBENV->set_tx_max and DBENV->set_tx_recover methods.
    The (unused) DBENV->db_lorder field has been deleted.


jrs@jrs-laptop:~/BerkleyDB/db-6.2.23.NC/build_unix$ sudo make install
[sudo] password for jrs:
Installing DB include files: /usr/include ...
Installing DB library: /usr/lib ...
libtool: install: cp -p .libs/libdb-6.2.so /usr/lib/libdb-6.2.so
libtool: install: cp -p .libs/libdb-6.2.lai /usr/lib/libdb-6.2.la
libtool: install: cp -p .libs/libdb-6.2.a /usr/lib/libdb-6.2.a
libtool: install: chmod 644 /usr/lib/libdb-6.2.a
libtool: install: ranlib /usr/lib/libdb-6.2.a
libtool: install: cp -p libdb.a /usr/lib/libdb.a
libtool: install: chmod 644 /usr/lib/libdb.a
libtool: install: ranlib /usr/lib/libdb.a
libtool: finish: PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/snap/bin:/sbin" ldconfig -n /usr/lib
----------------------------------------------------------------------
Libraries have been installed in:
   /usr/lib

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
   - add LIBDIR to the `LD_LIBRARY_PATH' environment variable
     during execution
   - add LIBDIR to the `LD_RUN_PATH' environment variable
     during linking
   - use the `-Wl,-rpath -Wl,LIBDIR' linker flag
   - have your system administrator add LIBDIR to `/etc/ld.so.conf'

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
----------------------------------------------------------------------
Installing DB utilities: /usr/bin ...
libtool: install: cp -p .libs/db_archive /usr/bin/db_archive
libtool: install: cp -p .libs/db_checkpoint /usr/bin/db_checkpoint
libtool: install: cp -p .libs/db_deadlock /usr/bin/db_deadlock
libtool: install: cp -p .libs/db_dump /usr/bin/db_dump
libtool: install: cp -p .libs/db_hotbackup /usr/bin/db_hotbackup
libtool: install: cp -p .libs/db_load /usr/bin/db_load
libtool: install: cp -p .libs/db_log_verify /usr/bin/db_log_verify
libtool: install: cp -p .libs/db_printlog /usr/bin/db_printlog
libtool: install: cp -p .libs/db_recover /usr/bin/db_recover
libtool: install: cp -p .libs/db_replicate /usr/bin/db_replicate
libtool: install: cp -p .libs/db_stat /usr/bin/db_stat
libtool: install: cp -p .libs/db_tuner /usr/bin/db_tuner
libtool: install: cp -p .libs/db_upgrade /usr/bin/db_upgrade
libtool: install: cp -p .libs/db_verify /usr/bin/db_verify
Installing documentation: /usr/docs ...
jrs@jrs-laptop:~/BerkleyDB/db-6.2.23.NC/build_unix$


This is where it died trying to compile the BDB extension module.


jrs@jrs-laptop:~/sb/source/extensions/bdb$ make -B
gcc -O2 -w -m64 -fPIC -DSTATIC_LINK=1 -c -o /home/jrs/sb/source/bin/mod/obj/bdb/s_interface.o interface.c
interface.c: In function ‘bootmodu’:
interface.c:153:44: error: ‘DB_ENV {aka struct __db_env}’ has no member named ‘mp_size’
 #define X(A,B) if( s=besCONFIG(A) )p->dbenv->B = atol(s);
                                            ^
interface.c:160:3: note: in expansion of macro ‘X’
   X("bdb.limits.mp_size"    ,mp_size)
   ^
makefile:19: recipe for target '/home/jrs/sb/source/bin/mod/obj/bdb/s_interface.o' failed
make: *** [/home/jrs/sb/source/bin/mod/obj/bdb/s_interface.o] Error 1
jrs@jrs-laptop:~/sb/source/extensions/bdb$

« Last Edit: December 19, 2016, 07:14:10 am by support »

Support

  • Administrator
  • *****
  • Posts: 1
    • View Profile
Re: ISAM - An appeal
« Reply #5 on: December 19, 2016, 07:51:32 am »
I got a little farther with the following change to interface.c for the BDB extension module.

//  X("bdb.limits.mp_size"    ,mp_size)
  X("bdb.limits.mp_size"    ,set_cachesize)
 

jrs@jrs-laptop:~/sb/source/extensions/bdb$ make -B
gcc -O2 -w -m64 -fPIC -DSTATIC_LINK=1 -c -o /home/jrs/sb/source/bin/mod/obj/bdb/s_interface.o interface.c
ar -r /home/jrs/sb/source/bin/mod/lib/bdb.a /home/jrs/sb/source/bin/mod/obj/bdb/s_interface.o
gcc -O2 -w -m64 -fPIC -c -o /home/jrs/sb/source/bin/mod/obj/bdb/interface.o interface.c
ld -shared -fPIC -o /home/jrs/sb/source/bin/mod/dll/bdb.so /home/jrs/sb/source/bin/mod/obj/bdb/interface.o /usr/lib/libdb.a
ld: /usr/lib/libdb.a(db_method.o): relocation R_X86_64_32S against `.rodata.str1.1' can not be used when making a shared object; recompile with -fPIC
/usr/lib/libdb.a: error adding symbols: Bad value
makefile:13: recipe for target '/home/jrs/sb/source/bin/mod/dll/bdb.so' failed
make: *** [/home/jrs/sb/source/bin/mod/dll/bdb.so] Error 1
jrs@jrs-laptop:~/sb/source/extensions/bdb$


This looks to me that the BerkleyDB db_method  needs a Makefile change to work as a shared object.

I have spent as much time as I can on this and will leave it in AlyssonR's hands to unravel.
« Last Edit: December 19, 2016, 07:53:56 am by support »

Support

  • Administrator
  • *****
  • Posts: 1
    • View Profile
Re: ISAM - An appeal
« Reply #6 on: December 19, 2016, 09:44:56 am »
It seems the trick to get it to compile is using the -ldb-6.2 not -ldb which seems to be a static version.

I'm trying to find a Script BASIC example script to test with but may need to start from scratch. On a positive note the bdb include file loaded without error.

AlyssonR

  • Guest
Re: ISAM - An appeal
« Reply #7 on: December 19, 2016, 01:28:51 pm »
Interesting. I may end up using BerkleyDB as a fallback position.

I have managed to get about 75% of the ISAM specification put together. Most of the logic is fairly simple and I will probably have an alpha of the basic functionality put together by the end of January. Having had a bit of a play already, it will probably go together in under 3000 lines of code without the added bells and whistles, or the toolset.

The use of scopeing, flexible arrays and those nifty break to array functions make the whole thing pretty simple compared with the stuff of the early 80's.

I may take a look at the BerkleyDB source, but I really am running shy of using that approach now.

I'll get back with progress and development documentation once things are settled (in between writing a couple of papers for publication. Sheesh!)

Support

  • Administrator
  • *****
  • Posts: 1
    • View Profile
Re: ISAM - An appeal
« Reply #8 on: December 19, 2016, 05:44:49 pm »
I was hoping to get the BerkleyDB extension module running again but with it returning to a console prompt with a bdb::Open() call with no error or reason makes me think my decision to drop it was a good one. Same with PostgreSQL.

AlyssonR

  • Guest
Re: ISAM - An appeal
« Reply #9 on: December 19, 2016, 07:17:14 pm »
OUCH! :o

That wasn't even a consideration when I was making the decision to roll my own.

At least with my approach, it will be guaranteed operable with SB as a local file process rather than depending upon an external service.

Thanks for trying, though - it IS appreciated.

Support

  • Administrator
  • *****
  • Posts: 1
    • View Profile
Re: ISAM - An appeal
« Reply #10 on: December 19, 2016, 07:53:56 pm »
Sometimes you need to reassure yourself that you didn't make a bad decision. (BerkleyBD & PostgreSQL ext. modules)

Everything Oracle touches turns to crap. About the only thing I still use Oracle related is VirtualBox.



« Last Edit: December 19, 2016, 09:00:54 pm by support »

AlyssonR

  • Guest
Re: ISAM - An appeal
« Reply #11 on: December 19, 2016, 08:22:17 pm »
I couldn't agree more concerning Oracle.  ::)

Somewhere, I have a standalone copy of MyISAM, but it's for 16-bit systems and is thus Not A Lot of Use™ A bit like the 8-bit Viewdata system I sill have somewhere in the archive (probably the same place as Windows 1.1 and Word 1.0 )

Support

  • Administrator
  • *****
  • Posts: 1
    • View Profile
Re: ISAM - An appeal
« Reply #12 on: February 13, 2017, 03:11:05 am »
Have you looked at the HASH extension module as a basis for your ISAM index engine?

Quote
The module uses the same hash algorithm as the ScriptBasic symbol table handling routine. This is routine comes from the page 436 of the dragon book.

The dragon book:
Aho-Sethi-Ulman : Compilers
     Principles, techniques, and Tools
Addison-Wesley Publishing Company 1986


AlyssonR

  • Guest
Re: ISAM - An appeal
« Reply #13 on: February 19, 2017, 09:46:02 pm »
I haven't, but I will.

Thanks!