Archive auto Primary Keys?

[Sparrowhawk]

Is there any way to get Archive to automatically add a system-generated auto-incrementing integer when a record is created? Like with some kind of trigger if Archive supports that (I have not worked out a way to do this though)

If not, how do people structure multi-file (multi-table) databases? So much data does not lend itself to natural uniqueness (people's names, addresses etc), so how are more complex Archive systems built given this limitation?

[Derek_Stewart]

Hi,

Define a altkey definition of the required Archive functions, before you start Archive, assuming you are starting Archive with EW or EXEC_W.

If you are multitasking Archive just do CTRL C to S*BASIC and use the ALTKEY command to set up the required ALTKEY definition.

Pressing ALT <key> will generate the command in the Archive console window.

See Toolkit 2 manual for ALTKEY command usage.

DBAS adds database extensions of S*BASIC and assembler and csn fo the sane job from bssic and can be compiled with Qliberator, maybe worth a look...

[NormanDunbar]

If not, how do people structure multi-file (multi-table) databases? So much data does not lend itself to natural uniqueness (people's names, addresses etc), so how are more complex Archive systems built given this limitation?

Typing as a retired Oracle Database Administrator, it's a lot easier when you have a proper database system! Mind you, even Oracle didn't have automatically incrementing integers/numbers for surrogate keys until a fairly recent release. Until then, you had to write a trigger to fire "on insert" and that would pull the next number from a sequence.

How did I create databases back in the day with Archive? Well, basically, I used a single table! For my music collection I had a lot of duplicate data, no normalisation here! The rows in the table had my own index number -- from the sticky label I added to each LP, CD, Single, 12" single and cassette tape -- as the "primary" key. Then I had the artist name and title and format. Something like:

Code: Select all

123
Elton John
Don't Let The Sun Go Down On Me
Single

124
Pink Floyd
When The Tigers Broke Free
Single

Each different format for my music, had a separate database!

I believe that the Archive runtime, which I never had, could allow Assembly code routines to be written -- or have I imagined this? I suppose you could, perhaps, do something with that, but I don't recall the standard Archive system having anything like this ability.


HTH

Cheers,
Norm.

[Sparrowhawk]

Hi Derek, Norman,

Thanks for info.

Derek - I don't want to have the user insert a PK via an ALT+Key as they are likely to forget and then there will be gaps.

Amazing that no one in the 80s seemed to need this functionality

Norman - all in one table. ye gods! Fair enough, and i suspect most uses of Archive back in the day worked like that.

Whenever I am reminded that you were an Oracle DBA, I feel such pain on your behalf... heh heh.

Mind you, despite my current project being Aurora PostgreSQL (very nice), I have had to use AthenaSQL recently too. Sigh. I should not be spending the twilight years of my employment suffering that...

[Derek_Stewart]

Hi,

Sorry, I did not understand what you meant, a Archive has a Superbasic style programming language, can't a interrupt trigger be using the WHEN ERROR, clause.

Not being a DBA, I used to use a one to many relationship in my Archive tables, and in some cases a Many-Many could be usdd, but needs a little coding.

I have done some DBA work, in mysql, sqlite, I prefer the Archive system, or as I mentioned DBAS.

[swensont]

In Bill Cables series "Archive Made Easy", in chapter 19, he discusses joins between tables and creating key fields. He describes the user defining the values in the key field; "but is is best if the user can define their own keys and even in QLerk the user has final say". I think if there way a built in way to create a key field, Bill probably would have mentioned it.

If writing procedures to automate Archive, it should be fairly easy to generate unique keys. The only issue might be where to store a variable on disk to recall the last key used (unless you just searched the database for the highest key value and incremented for the next key value.

I tried looking for Bill's "Archive Made Easy" online but could not find it. It was a column published in UPDATE magazine (which has been archived at the Internet Archive). The version I have is all of the articles in one document. Bill has released all of this Archive programs. I need be, I can scan the document I have and get it available online.

Tim

[NormanDunbar]

Whenever I am reminded that you were an Oracle DBA, I feel such pain on your behalf... heh heh.

Ha! Ha! Don't feel sorry for me. It paid the bills for many years, an -- even if I say so myself -- I wasn't too bad at it. I even used to have to tell the guys on site, who worked for Oracle, how to do stuff! As for PostgereSQL, well, I'm a fan of that myself, although I have been known to write the documentation for the Firebird database project (Interbase as was), but I haven't yet set up a "lab" to play with it -- no time these days.

One of my very back boiler projects is to get SQLite running on the QL. Wish me luck!

Cheers,
Norm.

[dilwyn]

I tried looking for Bill's "Archive Made Easy" online but could not find it. It was a column published in UPDATE magazine (which has been archived at the Internet Archive). The version I have is all of the articles in one document. Bill has released all of this Archive programs. I need be, I can scan the document I have and get it available online.

Tim

In case there's anything of use in Bill's DBEasy and DBProgs softwares, they're at https://dilwyn.qlforum.co.uk/database/index.html

[Derek_Stewart]

One of my very back boiler projects is to get SQLite running on the QL. Wish me luck!

I did get all but 3 files in the SQLITE C files to compile, just need to write a QL make file.

[dilwyn]

I have a very (extremely!) vague recollection of looking at doing this a few decades ago (yes, decades ago). Had a look for the code, don't have it any more, assuming I even got it to work at the time. Entirely possible I decided it was impossible, too fiddly or not worth the complex coding. Probably I never got it to work.
All I recall really is that it was very, very fiddly because you couldn't trigger anything during multiple inserts until all the inserts were completed by pressing F4. I have zero knowledge of DBA so this may be nothing like what you are trying to do.

To the best that I can recollect, the coding went something like this. I haven't tested any code, just typed this from memory, bearing in mind how many years since.

The 'key' number was stored in its own little database or in a dummy record at the start or end of file. If there were no records, it was assigned a default value (made 0 or 1 by default if there were no records). Or possibly made 1 higher than the total number of records. The 'key' field had to be a string so that Archive didn't insert 0 when a numeric field.

Remember what record we were at before the INSERT to add records - assumes new records INSERTed go at the end. recnum()

When INSERT finished, assume the database was in sequential order. That the fields inserted were from recnum onward to the end of the file. Work out the number of records entered from the difference in the number of records.

Step through the records, if key field empty insert the key value and increment ready for next occurrence. Not sure, I think you had to DISPLAY the record, use LET to assign the key field, then UPDATE to store the revised records. Means you see each one being updated after they've already been entered once. Messy.

When no more empty key fields, save the key index value in the other database, or put it back in the dummy field either at the start or end of your database.

In summary, work out how many records were added during INSERT, locate those (empty key fields) and insert key values.

Too complicated to be of practical use, maybe that's why I don't remember much and probably never finished the project. But may give an idea for a simpler or alternative way to approach this.