SPOT SPECIFICATION ================== Note: The data files supplied are in unsorted plain text tab-delimited format with one record per line. The field sequence is specified at the start of each file. The current SQL table definitions are given below, with any differences from the original Omnis database structure being highlighted. The remainder of the document is the original specification which, apart from the SQL changes, is still applicable. Changes From The Original Specification --------------------------------------- This is a system maintenance update for SPOT+SPEX, reflecting the changes subsequent to transferring the data from Omnis on an old Apple Mac to a SQL relational database on a much faster Unix PC (between 40-60 times faster!). The format of the tab-delimited *.unl unload files and the REFS*.txt analysis files is the same as before, apart from the alterations as noted below. The analysis report width has been increased from 80 to 120 characters; single-line truncated comments will have "..." appended; multi-line wrapped comments are truncated to the first line and will have ";" appended. I'll probably sort out preserving long comments in the analysis report at some other time. Changes in the file definitions below are marked in CAPS. The changes are: *+Combine FCOMMT.FCTEXT1 + FCTEXT2 + FCTEXT3 into a single larger FCTEXT char(255) field and tidy/expand the entries. *+Increase FEATURE.FETITLE to char(140) and tidy/expand the entries. *+Add FEATURE.FEVER char(1) for advert version numbers. * The enlarged FCOMMT.FCTEXT & FEATURE.FETITLE fields may now contain embedded "\ " (backslash space) tags in the *.unl files, which indicate a line-break in the original data. The line breaks are removed in the unloaded files so that they will still always have one line per record. *+Increase FMAGZNE.FMNAME to char(40) and expand abbreviated titles. *+Increase FPUBLR.FPPNAME to char(48) and expand abbreviated titles. *+Record all personal names in FPUBLR consistently as "first last" name (previously they could be either "first last" or "last, first" depending upon the type). *+Increase FGAMES.FGNAME to char(64) and expand abbreviated titles. *+Discard composite publisher codes, so there are now three pairs of individual publisher/cassette price fields in FGAMES: FGPUBL/FGPRORG, fgpubl2/FGPRBUD, fgpubl3/fgproth; with each publisher code relating to a single publisher/label. Tidy up the publisher codings and delete all the now unused composite codes. *+Discard the unique char(18) index on FGNAME and tidy up the titling anomalies caused by that index. Replace with a separate non-unique FGTKEY char(18) compacted search key index, comprising the first 18 alpha-numeric characters from FGNAME, up-shifted, excluding punctuation & spaces but including "-". *+Discard the 99-line limit on references attached to a title, so all references for a topic can now be attached under one title. Merge divided topics and tidy up the titles. A topic may now have up to 36^2 = 1,296 index references attached to it. *+Check & correct for any title/publisher/price inconsistencies between the SPOT & SPEX databases. * As the ellipsis character (C9h on the Mac) isn't available on this Unix PC, the "~" will be used instead as a shorthand character on new entries. Old entries might still contain C9h. Accented letters in titles & comments have been replaced with plain letters and other special characters replaced with the equivalent word (eg. 720degrees, Currah microSpeech). Current SQL Table Definitions ----------------------------- create table fcommt ( FCTEXT CHAR(255) , fclink serial not null ) in spotdbs ; create unique index fcommt_ix1 on fcommt ( fclink ) ; create table feature ( FETITLE CHAR(140) , feauthr char(4) , feseq serial not null , FEVER CHAR(1) ) in spotdbs ; create unique index feature_ix1 on feature ( feseq ) ; create index feature_ix2 on feature ( feauthr ) ; create table fgames ( FGNAME CHAR(64) NOT NULL , fgpubl char(4) not null , fgtype char(2) not null , fglink serial not null , fgprorg decimal(5,2) not null , fgprbud decimal(5,2) not null , fgratng char(4) , fgprdsk decimal(5,2) not null , fgcomsq integer not null , fppubl char(4) not null , fttype char(2) not null , fclink integer not null , FGPUBL2 CHAR(4) , FGPUBL3 CHAR(4) , FGPROTH DECIMAL(5,2) NOT NULL , FGTKEY CHAR(18) NOT NULL ) in spotdbs ; create unique index fgames_ix1 on fgames ( fglink ) ; create index fgames_ix3 on fgames ( fclink ) ; create index fgames_ix4 on fgames ( fppubl ) ; create index fgames_ix5 on fgames ( fttype ) ; create index fgames_ix2 on fgames ( fgtkey ) ; create table fhosts ( fhmag char(2) , fhhost char(2) , fhaddr char(58) , fhname char(36) , fhadmin char(36) , fhemail char(36) ) in spotdbs ; create index fhosts_ix1 on fhosts ( fhmag ) ; create table fmagzne ( fmmag char(2) , FMNAME CHAR(40) ) in spotdbs ; create unique index fmagzne_ix1 on fmagzne ( fmmag ) ; create table fpublr ( fppubl char(4) , FPPNAME CHAR(48) ) in spotdbs ; create unique index fpublr_ix1 on fpublr ( fppubl ) ; create table frefcde ( ffentry char(1) , ffname char(12) ) in spotdbs ; create unique index frefcde_ix1 on frefcde ( ffentry ) ; create table frefs ( frlink1 integer not null , frentry char(1) not null , frmag char(2) not null , fryymm char(4) not null , frpage decimal(4,1) not null , frissue decimal(4,2) not null , frlink2 integer not null , frseq serial not null , feseq integer not null ) in spotdbs ; create unique index frefs_ix1 on frefs ( frseq ) ; create index frefs_ix2 on frefs ( frlink1, frentry, frmag, fryymm ) ; create index frefs_ix3 on frefs ( frlink2 ) ; create index frefs_ix4 on frefs ( feseq ) ; create index frefs_ix5 on frefs ( frmag ) ; create table ftypes ( fttype char(2) , fttname char(24) ) in spotdbs ; create unique index ftypes_ix1 on ftypes ( fttype ) ; THE ORIGINAL SPECIFICATION ========================== FILES ----- FGAMES Topic headers. A topic may be a product (software or hardware), magazine feature or program listing. Note that all non-product topics have a title and pub/auth code starting with a "-", so that they can easily be eliminated from selections. Although the file is called FGAMES, this is just a historical relic from its origins; the database now holds entries for a lot more Spectrum topics than just games. FCOMMT Topic comments. An optional extension to an FGAMES record, containing supporting comments. FPUBLR Publishers and Authors: codes and names look-up file. FTYPES Topic types: codes and descriptions look-up file. FREFS Topic references. The core of the index; the magazine codes, issue dates and page numbers which have references to the parent topic. FEATURE Feature titles and author codes. An optional extension to an FREFS record, provided primarily for the above use, but can be used to add any supporting comment against a reference. It is also used to hold the contents list for compilation tapes, where the compilation tape itself is the parent topic. FMAGZNE Magazine codes and titles look-up file. Note that although SPOT covers primarily entries for the five titles: Crash, Micro Adventurer, Sinclair User, Your Spectrum, Your Sinclair; there are also entries for special items such as the YS Trainspotters Guide and Smash Tips Special. FHOSTS Internet Website hosts look-up file. Associated with FMAGZNE, this holds address and contact information for each Website which has links from SPOT*ON[line]. This saves space by allowing just a partial URL to be recorded against a reference, the host address part (and optionally an initial path) being recorded here. FREFCDE Reference type codes and description look-up file. RELATIONSHIPS ------------- Note: A field in () indicates a field that is not held explicitly in the file, but is appended to each record in the supplied data file to enable the requisite parent-child relationships to be constructed. Note: --> = one-to-many; --- = one-to-one; a relationship in [] is optional. FPUBLR.FPPUBL --> FGAMES.FGPUBL FPUBLR.FPPUBL [-->] FEATURE.FEAUTHR FTYPES.FTTYPE --> FGAMES.FGTYPE FCOMMT.FCLINK [---] FGAMES.(FCLINK) FREFS.FRLINK2 [<--] FGAMES.FGLINK FREFS.(FESEQ) [---] FEATURE.FESEQ FREFS.FRMAG <-- FMAGZNE.FMMAG FREFS.FRENTRY <-- FREFCDE.FFENTRY FMAGZNE.FMMAG [-->] FHOSTS.FHMAG FILE DEFINITIONS ---------------- Note: The data type 'Sequence' is equivalent to a serial ID; ie. a unique integer to specifically identify an individual record within a file, automatically generated when a new record is inserted. ------------------------------------------------------------------------------- FILE FORMAT FCOMMT AS AT 11 FEB 96 (15:42) PAGE 1 NAME TYPE LENGTH DEC PLACES INDEXED? INDEX LEN DATA LEN ------------------------------------------------------------------------------- 1 FCTEXT1 National 32 NO 32 Comment line 1 2 FCTEXT2 National 32 NO 32 Comment line 2 3 FCTEXT3 National 32 NO 32 Comment line 3 4 FCLINK Sequence YES 4 This record's unique serial ID. ------------------------------------------------------------------------------- FILE FORMAT FEATURE AS AT 23 SEP 97 (10:52) PAGE 1 NAME TYPE LENGTH DEC PLACES INDEXED? INDEX LEN DATA LEN ------------------------------------------------------------------------------- 1 FETITLE National 35 NO 35 A supporting comment for a topic reference entry; eg. a feature article title , a compilation contents list, a sequence comment (eg. "solution part 2"), a partial URL for a magazine site link, program authors, etc. 2 FEAUTHR Char 4 YES 4 4 Feature author code (optional) 3 FESEQ Sequence YES 4 This record's unique serial ID To determine if a FEATURE record contains a partial URL, the rule is that FEATURE.FEAUTHR will equal FHOSTS.FHHOST, in which case the full URL = "http://" + FHOSTS.FHADDR + "/" + FEATURE.FETITLE . If there is a FEATURE record associated with an FREFS entry but FEATURE.FEAUTHR does not map to FHOSTS.FHHOST, then it will map to FPUBLR.FPPUBL, generally to provide a feature article author name from FPUBLR.FPPNAME. Note that there are some author codes, starting with "." or "@" (plus -16K,-1K,-1K0,-2K), which are used as shorthand for recurring comments, so looking up the FEAUTHR code for one of those from FPUBLR then you'll just give the comment text again. ------------------------------------------------------------------------------- FILE FORMAT FGAMES AS AT 8 OCT 94 (16:25) PAGE 1 NAME TYPE LENGTH DEC PLACES INDEXED? INDEX LEN DATA LEN ------------------------------------------------------------------------------- 1 FGNAME Char 40 YES 18 40 Topic title 2 FGPUBL Char 4 NO 4 Publisher/Author code 3 FGTYPE Char 2 NO 2 Topic type code 4 FGLINK Sequence YES 4 This record's unique serial ID 5 FGPRORG Number 2 NO 6 Original full price 6 FGPRBUD Number 2 NO 6 Re-issue price or original budget price 7 FGRATNG Char 4 NO 4 Rating codes: all ratings have been adjusted into a range 1-9; the four characters represent the review ratings from Crash, Micro Adventurer, Sinclair User and Your Spectrum/Sinclair, respectively. Blank or "-" indicates no review; "?" indicates reviewed but no rating given. 8 FGPRDSK Number 2 NO 6 Alternative disk or microdrive price. 9 FGCOMSQ Number 0 NO 6 Unique serial ID of attached comment (=FCLINK) ------------------------------------------------------------------------------- FILE FORMAT FHOSTS AS AT 24 SEP 98 (12:48) PAGE 1 NAME TYPE LENGTH DEC PLACES INDEXED? INDEX LEN DATA LEN ------------------------------------------------------------------------------- 1 FHMAG Char 2 YES 2 2 Magazine/booklet code 2 FHHOST Char 2 NO 2 Internet host code 3*NOTE*FHNAME National 36 NO 36 Website title 4*NOTE*FHADDR National 58 NO 58 Internet host address (excluding "http://") 5 FHADMIN National 36 NO 36 Name of site administrator 6 FHEMAIL National 36 NO 36 Site administrator's e-mail address *NOTE* that FHNAME & FHADDR are 3rd & 4th fields in the original Omnis data file, but their positions are swapped around in exported data and in the SQL database version. Note that FHOSTS may contain multiple entries for a particular site, to save repeating sub-directory paths; eg. The Type Fantastic magazine type-ins archive has the files for each magazine in a different directory, so there's multiple entries for TTFn, each with a different FHOSTS.FHHOST code. ------------------------------------------------------------------------------- FILE FORMAT FMAGZNE AS AT 10 MAY 94 (20:54) PAGE 1 NAME TYPE LENGTH DEC PLACES INDEXED? INDEX LEN DATA LEN ------------------------------------------------------------------------------- 1 FMMAG Char 2 YES 2 2 Magazine/booklet code 2 FMNAME National 16 NO 16 Magazine/booklet title ------------------------------------------------------------------------------- FILE FORMAT FPUBLR AS AT 10 MAY 94 (20:53) PAGE 1 NAME TYPE LENGTH DEC PLACES INDEXED? INDEX LEN DATA LEN ------------------------------------------------------------------------------- 1 FPPUBL Char 4 YES 4 4 Publisher/Author code 2 FPPNAME National 32 NO 32 Publisher/Author name ------------------------------------------------------------------------------- FILE FORMAT FREFCDE AS AT 31 OCT 99 (20:23) PAGE 1 NAME TYPE LENGTH DEC PLACES INDEXED? INDEX LEN DATA LEN ------------------------------------------------------------------------------- 1 FFENTRY Char 1 YES 1 1 Reference type code 2 FFNAME National 12 NO 12 Reference type description ------------------------------------------------------------------------------- FILE FORMAT FREFS AS AT 29 JUL 02 (14:23) PAGE 1 NAME TYPE LENGTH DEC PLACES INDEXED? INDEX LEN DATA LEN ------------------------------------------------------------------------------- 1 FRLINK1 Number 0 YES 19 6 Unique serial ID of parent topic (=FGLINK) 2 FRENTRY Char 1 NO 1 Reference entry type code 3 FRMAG Char 2 NO 2 Reference entry magazine code 4 FRYYMM Char 4 NO 4 Magazine issue year and month (YYMM) Note that the first four fields comprise a composite index so that references can be retrieved by parent topic code and listed in ascending sequence by entry type + magazine title + issue date. 5 FRPAGE Number 1 NO 6 Magazine page number The decimal place is used for distinguishing distinct links to different entries on the same page. 6 FRISSUE Number 2 NO 6 Magazine issue month and year (MM.YY) 7 FRLINK2 Number 0 YES 6 6 Unique serial ID of parent topic (=FGLINK). This has to be recorded twice, once as part of a composite index and once as an individual index, because of the restrictions on index searches in Omnis3. 8 FRSEQ Sequence YES 4 This record's unique serial ID ------------------------------------------------------------------------------- FILE FORMAT FTYPES AS AT 10 MAY 94 (20:53) PAGE 1 NAME TYPE LENGTH DEC PLACES INDEXED? INDEX LEN DATA LEN ------------------------------------------------------------------------------- 1 FTTYPE Char 2 YES 2 2 Topic type code 2 FTTNAME National 24 NO 24 Topic type description OBSCURE INFORMATION ------------------- "-" Prefixes on Titles and Publisher Codes ------------------------------------------ In titles it's to distinguish feature entries and reader's type-in programs from commercial products, and in publisher codes it's to distinguish authors (usually associated with feature entries and reader's type-in programs) from publishers (usually associated with commercial products). So the initial "-" indicates feature entries and reader's type-in programs and their associated authors. This avoids them getting mixed in with the commercial products when doing queries sorted alphabetically (as the "-" titles all come first), and also makes it easy to select or reject one type or the other to narrow down query results. It also avoids confusion when a type-in program and a commercial program might both otherwise have the same title. (In the cases where a type-in program was subsequently released commercially, or vice versa, like some of the Cambridge Award games, then all the entries should be under the commercial title.) Split Reference Lists --------------------- Some titles with a lot of entries, such as The Hobbit, are split up into two or three groups, usually with a number embedded in the title. This is because the Omnis docs don't specify how big the screen arrays can be, and I restricted them to 99 lines; so when an array filled up I used to add a new entry rather than extend the array. I've subsequently found that I could probably extend the array size to something fairly large (hundreds anyway) so all the entries for a topic would fit under one title. However, other programs have now been written based on a 2-digit, 99-line maximum element count per array; plus displaying a full 99-line array is rather slow anyway; so extending the size isn't practical. Title Cross-Reference Entries ----------------------------- Some topic titles have a '@' in the rightmost (40th) character position. This is to indicate a cross-reference entry for either: a) a product known by more than one title; or b) the second product of a twin-pack cassette. The '@' is to indicate that the topic entry is not for a distinct product. In case (a) there will be a "cf." comment indicating the primary title. In the case of (b), where the product title is of the form "Title2 + Title1", the second title will be the primary title. Advert Summary Entries ---------------------- Under the company headings in SPEX I've been adding comments for full-page multi-product adverts, which would be either the advert title or the first few product titles; the intent being that people can see when the advert changed and how many different ones were placed. Here's an example: Full-page ad - [Ollo +] Sodov the Sorcerer [...(+4)] There are three types of symbol used here: the brackets []; the parentheses (); the ellipsis. The brackets indicate a non-Spectrum item, an ellipsis indicates "more unspecified", and the parentheses contain how many more. So in the above example Ollo was the main advertised product (non-Spectrum), Sodov the Sorcerer was a Spectrum product in the advert, and there were four other non-Spectrum products advertised. -- Another Fine Product from: Jim Grimwood, Weardale, England at http://www.users.globalnet.co.uk/~jg27paw4/ Home of the Spectrum Oracle on Trumpton magazine index (SPOT*On) and the Your Spectrum Unofficial Archive (YrUA?) and the Type Fantastic Sinclair Magazine Type-in Programs Archive (TTFn) --