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

AlyssonR

  • Guest
ISAM - An appeal
« on: December 13, 2016, 12: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 13, 2016, 04:45:51 PM »
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, 03: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: 19
    • View Profile
Re: ISAM - An appeal
« Reply #3 on: December 18, 2016, 09:47:22 AM »
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.


Code: Script BASIC
  1. '
  2. ' FILE: bdb.bas
  3. '
  4. ' This is the module declaration of the ScriptBasic external module bdb
  5. '
  6. ' To use this module you have to have bdb.dll or bdb.so installed in the
  7. ' modules directory.
  8. '
  9. ' These implement the interface to the Berkeley Data Base library
  10.  
  11. module bdb
  12.  
  13. ' Error codes
  14. Const ErrorInvalidFileName      = &H80001
  15. Const ErrorInvalidDbHandle      = &H80002
  16. Const ErrorTransactionnotClosed = &H80003
  17. Const ErrorTransactionNotOpened = &H80004
  18. Const ErrorKeyNotFound          = &H80005
  19. Const ErrorIncomplete           = &H80006
  20. Const ErrorKeyEmpty             = &H80007
  21. Const ErrorKeyExist             = &H80008
  22. Const ErrorLockDeadLock         = &H80009
  23. Const ErrorLockNotGranted       = &H8000A
  24. Const ErrorNotFound             = &H8000B
  25. Const ErrorOldVersion           = &H8000C
  26. Const ErrorRunRecovery          = &H8000D
  27. Const ErrorDeleted              = &H8000E
  28. Const ErrorNeedSplit            = &H8000F
  29. Const ErrorSwapBytes            = &H80010
  30. Const ErrorTxnCkp               = &H80011
  31.  
  32.  
  33. ' Table types
  34. Const BTree   = &H01
  35. Const Hash    = &H02
  36. Const Recno   = &H04
  37. Const Queue   = &H08
  38. Const Unknown = &H10
  39.  
  40. ' put flags
  41. Const Append      = &HFFFFFFFE
  42. Const NoOverWrite = &HFFFFFFFD
  43.  
  44. ' Table opening flags
  45. Const Create   = &HFFFFFFFE
  46. Const NoMap    = &HFFFFFFFD
  47. Const RdOnly   = &HFFFFFFFB
  48. Const Thread   = &HFFFFFFF7
  49. Const Trunc    = &HFFFFFFEF
  50. Const New      = &HFFFFFFDF
  51.  
  52. ' open the database
  53. ' DB = bdb::Open(DataBase,type,flags,unixmode)
  54. declare sub ::Open alias "sb_db_open" lib "bdb"
  55.  
  56. ' close the DB
  57. ' bdb::Close DB
  58. declare sub ::Close alias "sb_db_close" lib "bdb"
  59.  
  60. ' put a new, possibly duplicated key/value pairinto DB
  61. ' bdb::Put DB,key,value
  62. declare sub ::Put alias "sb_db_put" lib "bdb"
  63.  
  64. ' update the last accessed record
  65. ' bdb::Update DB,value
  66. declare sub ::Update alias "sb_db_update" lib "bdb"
  67. ' delete the last accessed record
  68. ' bdb::DeleteRecord DB
  69. declare sub ::DeleteRecord alias "sb_db_eracrec" lib "bdb"
  70.  
  71. ' get value matching the key from DB
  72. ' if there are more than one values for the same key return the first one
  73. ' value = bdb::Get(DB,key)
  74. declare sub ::Get alias "sb_db_get" lib "bdb"
  75. ' value = bdb::First(DB,key) or value = bdb::First(DB)
  76. declare sub ::First alias "sb_db_get" lib "bdb"
  77.  
  78. ' get the last value from DB
  79. ' value = bdb::Last(DB)
  80. declare sub ::Last alias "sb_db_last" lib "bdb"
  81.  
  82. ' get the next value for the key or undef if there are no more
  83. ' value = bdb::Next(DB,key)
  84. declare sub ::Next alias "sb_db_next" lib "bdb"
  85. ' get the previous value for the key or undef if there are no more
  86. ' value = bdb::Previous(DB,key)
  87. declare sub ::Previous alias "sb_db_prev" lib "bdb"
  88.  
  89. ' delete all records for the given key
  90. ' bdb::DeleteAll DB,key
  91. declare sub ::DeleteAll alias "sb_db_del" lib "bdb"
  92.  
  93. ' get the key of the last accessed record
  94. ' bdb::Key(DB)
  95. declare sub ::Key alias "sb_db_key" lib "bdb"
  96.  
  97. ' transaction handling, no arguments
  98. declare sub ::BeginTransaction alias "sb_db_transact" lib "bdb"
  99. declare sub ::CommitTransaction alias "sb_db_trcommit" lib "bdb"
  100. declare sub ::EndTransaction alias "sb_db_trcommit" lib "bdb"
  101. declare sub ::AbortTransaction alias "sb_db_trabort" lib "bdb"
  102.  
  103. ' delete a database table
  104. ' bdb::Drop "databasename"
  105. declare sub ::Drop alias "sb_db_remove" lib "bdb"
  106.  
  107. end module
  108.  
« Last Edit: December 18, 2016, 11:24:06 AM by support »

Support

  • Administrator
  • *****
  • Posts: 19
    • View Profile
Re: ISAM - An appeal
« Reply #4 on: December 18, 2016, 12: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 18, 2016, 11:14:10 PM by support »

Support

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

Code: C
  1. //  X("bdb.limits.mp_size"    ,mp_size)
  2.   X("bdb.limits.mp_size"    ,set_cachesize)
  3.  


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 18, 2016, 11:53:56 PM by support »

Support

  • Administrator
  • *****
  • Posts: 19
    • View Profile
Re: ISAM - An appeal
« Reply #6 on: December 19, 2016, 01: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, 05:28:51 AM »
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: 19
    • View Profile
Re: ISAM - An appeal
« Reply #8 on: December 19, 2016, 09:44:49 AM »
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, 11:17:14 AM »
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: 19
    • View Profile
Re: ISAM - An appeal
« Reply #10 on: December 19, 2016, 11:53:56 AM »
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, 01:00:54 PM by support »

AlyssonR

  • Guest
Re: ISAM - An appeal
« Reply #11 on: December 19, 2016, 12: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: 19
    • View Profile
Re: ISAM - An appeal
« Reply #12 on: February 12, 2017, 07:11:05 PM »
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, 01:46:02 PM »
I haven't, but I will.

Thanks!