Database Code Challenge(s)

[John]

Alysson,

If you have any spare time, it would be great if you can put some effort towards the VB6 OCX form direction. If you don't see it having value then I'l but it on the back burner. Windows isn't my primary development environment.

Thanks!

John

[AlyssonR]

John,

I shall, but once I have some more back-end written.

At the moment I'm developing in VB6 with a view to converting the back-end to SB later - I'll try to get my front end converted to OCX once I get that far.

It'll be a while before I get to that point since I'm actually still working on that custom database project that has been underway for the past *mumbles* years (and am now making admittedly slow headway).

[John]

Please keep us in the loop with your progress.

[AIR]

Hi, Alysson!

Your DB project sounds like it could make an interesting code challenge.  Would you be willing to submit a code challenge for this?

AIR.

[John]

+1

AIR,

It's always a treat when you visit the Windows world. Sort of like a full moon.

[AlyssonR]

*howls at the full AIR*

Okay, I'll pull together a challenge of some sort.

[John]

I knew I could count on you. Can't wait to see where you are with it.

[AlyssonR]

Database Code Challenges


I have been asked to come up with a code challenge based on some software that I am writing.

General requirements:

The software is an acquisitions and accessions database for a museum-type collection - thus, there are no physical delete operations on any record - a 'deleted from collection' simply flags the record as hidden. All of the artefact records are, in fact, separate disc directories containing a single text file and any number of media files.

The primary requirement is that the textual data must be kept entirely in human-readable digital format (i.e. text files), thus, software-proofing the data. That includes indices and data (images and other media excepted). The artefact record is, in fact, a single document. Even EXIF-type metadata on media files is maintained in separate text documents alongside the media files.

Software installation is not required - just copy the directory, edit the config.ini file to suit, and run.

It is important that the data files may be printed out as-are, and will look like properly formatted text reports.

Directory structure:

Code: [Select]

DBRoot
 |
 |__ Artefact Record
 |       |
 |       |__ Images
 |       |       |___ *.jpg etc.
 |       |
 |       |__ Documents
 |       |       |___ *.doc, .xls etc.
 |       |
 |       |___artefact.record (text file)
 |
 |___ DBPrimaryIndex.index (text file)
 |___ DBPrimaryIndex.schema (text file)
 |
 |___ DB secondary indices & schemas
 

For the sake of the code challenges, each program may reside in the same directory as the data file(s).

I will put up a new challenge chunk as each back-end section of the software is completed (I hope).

I should point out that I am using VB6 for developing this software (which means that it'll run under WINE, too).


Challenge 1: Data Records

Next: Challenge 2: Primary Index

[AlyssonR]

Challenge 1 - Data Records:

The data records are, apart from the unique 'card number', unspecified in format (schemaless) - the data is entered in such a way that each field is named in the text, and may contain multi-line values, arrays and simple values. Some system data is tucked away at the end of the record document. This is not JSON or XML (which are not pretty enough to just print out as-is)

You need to be able to read the document into the software and to then (after any alterations) to write it back to the file in the same format as if was read.

I have already done this in VB6, and the program tidies up the tabulation of the document at the same time (neat-freak )

A typical record looks like this:

Code: [Select]

RecordTitle is              Mineral Specimen Details
                            ========================

Entry No:
                            ARC-99-000021

================================================================================
Species:
  Minerals =                Zeunerite
                            Rutherfordine
                            Zippeite
                            Chalcopyrite
    .var                     Blister Copper
                            Quartz
    .var                     Agate
    .colour                  White
                            Opal
    .var                     Common Opal
    .colour                  Transparent
  Matrix =                  Quartz

================================================================================
Location:
                            South Terras Mine
                            St Stephen in Brannel
                            St Austell
                            Cornwall, UK
  Grid Reference =          SW 935 524
  GPS Coordinates =         50.33444,-4.90194

================================================================================
Collection Data:
  collected by =            Self collected
  Collection Date =         1979

================================================================================
Acquisition Data:
  Acquired Date =           14 Jul 1998
  From =                    Self collected
  Field Ref No =            F20180623-STUM-001
  Temp Ref No =             TMP 0 0099

================================================================================
Analytical Data:
  Commentary =              4 species
  Zeunerite =             { Colour and crystal form are typical for this location.
                          }
    .date                    14 Aug 2018
    .Report                  AN-201808-14-01
    .Ref_Number              ZZ8088
  Zippeite =              { Colour is diagnostic for this location
                          }
    .date                    14 Aug 2018
    .Report                  AN-201808-14-02
    .Ref_Number              ZZ9099
  Chalcopyrite =          { Well characterised at this location.
                            Deep indigo tarnish on old surfaces.
                          }
  Rutherfordine =         { Tentative ID.
                          }
    .Reasons                 Visual ID only

================================================================================
Accession Data:
  Accession No =            ZZ9099
  Accession Date =          21 Sept 2000
  Sub-collection =          Main

Image Data:
      Mugshot =             img0001.jpg
      Images =              img0001.jpg
      .Title                Whole Specimen
      .TakenDate            11 Nov 2018
      .Width                11.5mm
      .camera               Fuji FinePix L55
                            img0002.jpg
      .Title                Zeunerite & Chalcopyrite
      .TakenDate            11 Nov 2018
      .FOV                  1.1mm
      .Camera               CL 1600 USB MicroCam

===== DB System Info ===========================================================
 RecordType is               1
 LastSavedTime is           13/11/2018 13:18:37
(Previous Version was       13/11/2018 12:12:47 )
================================================================================
The format is:
Field:               Indicates a group of related data
Sub-Field Name =     Indicates a named sub-field (or sub-field array)
.Property            Indicates a named property of the sub-field
Name =  {            Indicates the start of a multiline data block
        }            End of multiline data.

[AlyssonR]

Still working on the functionality at the back end - nothing that actually *looks* like a database yet - just BASIC modules.

The idea of free-form records came from the first version of my minerals database - using IdeaList.

There is little complex searching and nothing as horrendous as deletions or non-sequential records.

Oh yes, and nothing as suspect as compiled stuff yet - just proof of concept examples. One day, I may even do some documentation (laughs up sleeve and tries to hide the growing nose).

[John]

After a quick peek, sure looks like a wiki would be the best tool for the job.

Check out PBWiki as it's based on a text file format.

[AlyssonR]

I considered PBWiki, amongst literally hundreds of other tools of various sorts.

What I need is a little too rigid for a wiki, too flexible (and lightweight) for a standard database, and really not worth the effort with a document automation/workflow system. Nothing *quite* meets the specification - otherwise I'd have resurrected my copy of IdeaList which really is the closest approach.

I've given up looking for something that is (a) close to suitable, (b) too cheap for words and (c) doesn't provide a cliff-like learning curve, and have decided to write it myself. Once the decision was made, deciding on how to do the job took me a couple of months. Each functional module takes me about a week to write and to test thoroughly, alongside an example project (used for testing) - given that the specifications tend to change a bit as I write.

Once the functional code is written, the rest of the work will just be wiring the whole lot up.

The DB itself only requires administration by a single user, though I am planning on delivering lookup via static HTML and Saxon XSLT (read-only service) with an index system being generated by background scripts (I've done this before).

Besides, using an off-the-shelf solution leaves no room for the satisfaction of developing my own application, and this is the first application that I've written in about 30 years - it's all been admin and glue scripts for a loooong time.

[John]

Is this a hobby project you're working on? A good deed for a non-profit museum?

[AlyssonR]

A bit of both, actually.

I don't program commercially (and haven't done so since the early 1980's) - it's usually an incidental aspect of management/support.

I want something for my own collection (which is the original reason I decided to write the thing), and while chatting with the manager of a small museum, discovered that they want something similar to handle their accessions that will (a) run on older hardware, (b) give access to volunteers (and visitors) to look up information about the collection and (c) allow their archivist to get as much information onto the system as possible. Oh and, (d) to be within their budget (roughly £15 and a pub lunch in the town).

They are constantly looking for additional funding, and I offered a good deed with something I wanted to do in any case.

Once it's out there, I'll be offering it for free to anyone who wants it - there are *so many* small museums and private collectors in the UK who want something more than a card index, but less than an all-singing-all-dancing technicolour database. 99% of our museums are privately funded, small and rely on goodwill for most things.

*falls off of soapbox*

[John]

First and foremost don't lock yourself into Windows because that's all the tools your're familiar with. I think AIR got a whiff of you painting yourself into a corner and suggested the code challenge to get it back on the right path.

My submission would be a Script BASIC application server proxy to Apache with MySQL as the DB running on a next to nothing cost wise Linux instance.