Database Code Challenge(s)

[AlyssonR]

Strangely, VB6 software will run well under WINE, but that isn't the point.

Once the project is done in VB (which is my go-to for QUICK), I plan to rework at least the back-end in SB when I have time. Right now, I use Windows on my main box, and the museum is on a mix of Windows XP and 7 - and that is not likely to change for a while. I'm far from painted into a corner here - at least, not once I resurrect my Debian box (and update it a bit).

I particularly want to avoid running *anything* on an SQL database when I can do the job with a lot less logic. Remember that flexibility I specified? It's intrinsic to what I have designed - MySQL et al require a stack of admin to change anything on the DB.

Whilst it's true that what I have written isn't pretty (that comes later), and it isn't terribly fast (but the datasets are only in the thousands of records). It isn't terribly disk efficient (disk space is cheap for this size of DB), nor is it extremely comprehensive and it certainly lacks security, but ....

It meets the cost (time, effort and cash) requirements for the product. It doesn't require much setting up (run it from an external HDD). Adding an additional document base just requires an index schema, an initialisation file and a sample form - a task that most people could manage (yeah, yeah ... better fool and all that).

I am happy with my design, and so is the museum. The only thing that worries me is the guy got a crazed look in his eye and started muttering about pages on an old ViewData terminal they have (like I said - some OLD hardware) - and it's a long time since I wrote an interface to a viewdata service.

[John]

This is sounding more and more like a RetroB challenge if where you are feels that comfortable.

[AIR]

This is sounding more and more like a RetroB challenge if where you are feels that comfortable.

I disagree.

Most code challenges (RetroB included) tend to settle on a very focused end result.  I don't think I've ever seen a challenge issued where the goal is to create a full system/stack that is not limited to a single use case.

My perspective on this particular challenge is that it's an amazing opportunity for a chosen programming language to show what it can do.  By that, I mean how seemingly disparate capabilities of a language can be assembled in a way that showcases how well (or bad!!) a given language is at producing the finished product.

One of the things I find intriguing about Alysson's challenge is the requirement that the data be accessible with a simple text editor by anyone in more or less plain English (or your choice of language).  That eliminates solutions using *sql based products, xml, json, etc. 

So the archiving of the data itself could be viewed as requiring the creation of a configuration file format.  Stay with me on this.  If you consider something like the old Windows INI file format you'll see what I'm getting at.  Personally, I think that something like yAML would fit the bill since it offers the flexibility that the challenge would require.  That's how I'm thinking of approaching it, anyway.

So my process would be:

Settle on a serialization format, that is readable and editable with a simple text editor.  But is also easy to work with programmatically.

Work out the FILE structure as Alysson laid out in her challenge post.

Figure out how to present this to an end user so that it makes sense to them.

Figure out how Alysson is going to get the required booze to us in the States and elsewhere.


Sounds like Fun!!!

AIR.

EDIT:  Added link to a quick and dirty overview of the yAML format....

[John]

Is this challange restricted Windows and VB6 as the UI?

[AIR]

I think the logic to manipulate the data doesn't need to be Windows-centric (it wouldn't be in my case) but the gui is a different matter.

However, if you use a cross-platform toolchain it shouldn't matter much, aside from a few gui tweaks to account for differences in positioning/rendering.

The last time I used VB 4/5/6 was in the late 90's, so I probably wouldn't be using that personally.  But I think it's important to consider at some point that the target audience in this case is ultimately Windows-centric.

AIR.

[John]

Alysson showed interest in the SB OCX form proof of concept so I'm assuming that direction is still valid. This doesn't limit the challenge to SB as any COM/OLE  complaint language should work. One would just need to implement a similar callback environment like SB offers.

[John]

PHP would be a good candidate for this challenge. It has an easy to use COM/OLE interface for desktop and something that can go web bound if desired with a wealth of preexisting code.

[AIR]

Quick example using Python and yAML to prototype:

Code: Python

1. #!/usr/bin/env python
2.  
3. import yaml
4.  
5. with open('testI.yml') as fp:
6.     record = yaml.load(fp)
7.  
8. print "Title: %s" %record["RecordTitle"]
9. print "Entry Number: %s" %record['Entry No']
10. print "Minerals: %s\n"  %record["Species"]['Minerals'][:4]
11. # print "Mineral Colors: %s\n"  %record["Species"]['Minerals']['colour'][:3]
12. print "Address:\n%s" %record['Location']['Address']
13. print "Grid: %s" %record['Location']['Grid Reference']
14. print "GPS: %s\n" %record['Location']['GPS Coordinates']
15.  
16. for x in record["Species"]['Minerals'][:4]:
17.     try:
18.         print "%s: %s" %(x,record['Analytical Data'][x]['.comment'])
19.     except:
20.         continue

[riveraa@MPD ~/Projects/Alysson] $ ./test.py
Title: Mineral Specimen Details
Entry Number: ARC-99-000021
Minerals: ['Zeunerite', 'Rutherfordine', 'Zippeite', 'Chalcopyrite']

Address:
South Terras Mine
St Stephen in Brannel
St Austell
Cornwall, UK

Grid: SW 935 524
GPS: 50.33444,-4.90194

Zeunerite: Colour and crystal form are typical for this location.
Rutherfordine: Tentative ID.
Zippeite: Colour is diagnostic for this location
Chalcopyrite: Well characterised at this location. Deep indigo tarnish on old surfaces.


Sample file converted to yml format is attached.

[John]

Nice approach and it seems to satisfy the readability requirement well.

[AlyssonR]

Apart from using nested stuctures (a VB speciality), you will notice that I don't actually rely on any Windows API calls in the functional module. There is no registry requirement, and by dropping the extracted VB runtime DLLs into the program directory, they don't seem to need registration in the system either (only really applicable to WINE/Linux boxen).

As I said earlier - speed of initial deployment is important right now (I want v1.0 up and in user Beta by New Year), and I will be able to spend time later translating the whole thing into another version of BASIC later. It will also give me a chance to get my head thoroughly around IUP, and perhaps write a translator of VB .frm source into IUP 4 BASIC (have I mentioned how much I detest not being able to do drag & drop form design? Spoiled by VB6, I suppose).

AIR is right that I don't want to be locked into Windows in the long term, but right now it is a convenient platform.
Likewise, I have no desire to be stuck with MySQL (or similar) even though it would be so very simple to implement.
As for PHP .... *shudders* .... one day, maybe.

[AlyssonR]

@ AIR -

Nice.

[John]

I don't see why the EXTRACT function I did in the LIKE + challenge wouldn't work with this challenge. No callbacks , libraries just load to a string from a text file with one statement and use EXTRACT to grab the data you want then use the SB IUP extension module to display it. You can use LED for the form creation without the need to code the UI.

The REPLACE function would allow you to make changes if you needed programmaticly.

With one code base your application would run native on Windows and Linux.

[AlyssonR]

AIR has seen what I'm trying to do - something that is stupidly flexible and easy to configure for a new user.

Essentially, everything has an ini file (or similar) - all in plain English (or whatever language). right now, with VB, I'm stuck with the ASCII character set, but that will change in the future. Whatever happens, I'm actually enjoying writing this software.

Okay, here's the bit I'm currently working on - Index handling.

Like everything else, the primary index is a text file (fixed width record, CrLf termination) and has an external schema.

So - here's a few lines of my test index (yes, it's a part of my mineral collection - makes for excellent test dat for this kind of thing) - there's about 2000 lines of that stuff. The first column is for a "hidden record" flag, and I've trimmed off most of the trailing spaces.

Code: [Select]

|Unique |     |Accession| |other stuff|           |Collection          |  |Analysis | |Field     |
|number |     |reference|                         |name                |  |reference| |reference |
 ARC00796       M00796                              Minerals                                                                                       
 ARC00797       M00797                              Minerals                            F199709144                                                 
 ARC00798       M00798                              Minerals                                                                                       
 ARC00799       M00799                              Minerals                                                                                       
 ARC00800       M00800                              Minerals                A19990301   F199903261                                                 
 ARC00801       M00801                              Minerals                            F199903261                                                 
 ARC00802       M00802                              Minerals                            F199903261                                                 

The first two lines do not appear in the index - they're just for your reference.

And here's the schema for what is actually in there (and I do allow comments in there)

Code: [Select]

Schema:
      Database =       Mineral DB
      Filename =       DBPrimaryIndex.schema
      Target =         DBPrimaryIndex.index
      Size =           148
      ;                Record size of index in bytes - will be padded to fit.
      ;                - the index *is* a random access file, after all.

;
; Type:
;       0      = String
;       1      = Boolean String 'True'/'False'
;       2      = Long Integer
;      98      = Deletion Flag ('D' / 'H' / 'X' / ' ')  (= Deleted / Hidden / Expunged / normal)
;      99      = String / Autonumber
;                       Prefix =          (string)
;                       Body =            (picture)
;                       Suffix =          (string)
;                       Datestamp =        null or (picture)

; Class:
;      [none]  = Default index type (plain secondary index)
;      PK      = Primary Key (auto numbered sequence number)
;      SK      = Secondary Key (usually the fully formatted version of the PK,
;                which should be an integer
;      FG      = File Group  - by which record cards are arranged on disk
;                              (directory structure)
;      SG      = Secondary File Group - by which records without an FG
;                                       value are grouped on disk.
;


Field:
                     ; DELETION flag (Boolean)
      Name =           Deleted
      LPos =           1
      Width =          1
      Type =           98
      Class =
      Path =           NONE
      XPath =

Field:
                     ; Sequential DB entry number  - a SUBSET of the ARC Number
                     ; SeqNo
      Name =           SeqNo
      LPos =           5
      Width =          6
      Type =           99
      Class =          PK
      Path =           Main.idx
      XPath =

Field:
                     ; Sequential entry number
                     ; ARCNo
      Name =           ARCNo
      LPos =           2
      Width =          15
      Type =           02
      Class =          SK
      Path =           Main.idx
      XPath =
      Prefix =         ARC
      Body =           9999999
      Suffix =         0
      Timestamp =      0
                     ; System timestamp (19 characters for 'Now()' )

Field:
                     ; Accession Number is the specimen reference for
                     ; all accessioned specimens.
                     ; AccNo
      Name =           Accession No.
      LPos =           17
      Width =          12
      Type =           0
      Class =          FG
      Path =           AccNo.idx
      XPath=           Acquiry Reference

Field:
                     ; Field Number or other reference to material that
                     ; hasn't been accessioned as collection specimens.
                     ; AcqRef
      Name =           Acqusition Reference
      LPos =           29
      Width =          24
      Type =           0
      Class =          SG
      Path =           AcqRef.idx
      XPath =

Field:
                     ; Sub Collection Name
                     ; SubColl
      Name =           SubCollection Name
      LPos =           53
      Width =          24
      Type =           0
      Class =
      Path =           NONE
      XPath =

Field:
                     ; Analytical Reference
                     ; Aref
      Name =           Analytical Reference
      LPos =           77
      Width =          12
      Type =           0
      Class =
      Path =           NONE
      XPath =

Field:
                     ; Field Reference
                     ; FRef
      Name =           Field Reference
      LPos =           89
      Width =          12
      Type =           0
      Class =
      Path =           NONE
      XPath =

End:



There will be something similar for the sub-indices (things like dictionary searchable fields etc).

Each schema that gets read into the program joins the schema structure - and, I was surprised when I came up with this, it allows multiple document bases to operate alongside each other natively. With careful arrangement of the fields, it is possible to define a composite field that includes parts of two or more fields concatenated, or even a subset of a single field (SeqNo is the numerical part of ARCNo). As you may have guessed, SeqNo is also the record number in the primary index, and will reference every related item in the system.

(In case you wonder, the short name in the comment is the field mnemonic from the original Access DB)


I have done something similar with the software configuration file - except that I also allow #INCLUDEs in order to allow the stacking of INI parameters to include such things as defaults, new configurations and session information.

You broke the custom config file? Just delete the contents of THAT file, and the configuration reverts to the defaults. There's even a tool for debugging your config

Once I finish the to do list on that chunk of effort, I'll put it up, too.

[John]

right now, with VB, I'm stuck with the ASCII character set,

If you use Krool's common controls OCX, you would  have unicode support along with current OS theming.

[AlyssonR]

Good point. I had quite forgotten.