Help:Appending Records in Universe Database

[jrmapes]

Ok since Universe is based on a database system there should be a way to do this but I can't figure it out since it isn't covered in the documentation.

What I am wanting to do is this,

Take the existing Spinward Marches data since it has subsector names, descriptions, world descriptions, xboat routes, and the like already in place which is close to if not completely accurate -- then import a cleaned up and as close to accurate as can be had Spinward Marches UWP and Stellar Data.

This would not be a normal import, in dB terms it would be an APPEND where only the extended UWP with trade codes, Stellar Data, and the like would be updated in each record while leaving the other existing data (xboat routes, world descriptions,etc,) intact.

I am pretty sure you can not do this thru the Universe Software as is, to do it you would most likely have to do it at the dB level. It is just a matter of figuring out the procedure - and that is what has me stumped since I am unfamiliar with the Universe dB software.

To make updates to the dB this way would save a ton of hand entry and the only thing that would remain that would need changing is some of the blank hex allegiance codes so the borders would show correctly.

Any help?

Jerry

 

[Valarian]

You can do an UPDATE through the freehand SQL option within Universe if you're familiar with SQL syntax. Call the freehand SQL option from the top level menu (GM Mode), execute your UPDATE statement, then execute a COMMIT statement to save the changes.

If you're even more comfortable with databases, then there's the IBConsole in the InterBase 6.0 folder installed with Universe. The database user name and password are both TRAVELLER_DBA. You need to register a local server, and then register your databases with this server. The IBConsole has the advantage that the database can be edited in-situ.

Unfortunately, there's not something like SQL Server's DTS or Oracle's SQL*Loader tools that would allow bulk updating of data from file.

 

[Hemdian]

Thanks for responding, Valarian. RL last week was delaying me somewhat. (RL this week doesn't look much better.) I've not had a chance to try it yet but rather than an UPDATE I'd load the new data into a temporary location on the sector grid with a sector code of XX, then using SQL ...

DELETE SYSTEM_1_TABLE
  WHERE SECTOR_CODE = 'SM';

COMMIT;

... followed by ...

INSERT INTO SYSTEM_1_TABLE
  SELECT
      'SM',
      SUBSECTOR_CODE,
      SUBSECTOR_HEX,
      SYSTEM_NAME,
      STARPORT_CODE,
      SIZE_CODE,
      ATMOS_CODE,
      HYDRO_CODE,
      POP_CODE,
      GOV_CODE,
      LAW_CODE,
      TL_CODE,
      BASE_CODE,
      TAS_ZONE,
      POP_DIGIT,
      BELTS_DIGIT,
      GIANTS_DIGIT,
      REMARK_CODE_1,
      REMARK_CODE_2,
      REMARK_CODE_3,
      POPULATION,
      RULESET_CODE,
      EXPLORED_SYSTEM,
      DIAMETER,
      HYDRO_PERCENTAGE,
      CLIMATE,
      MAIN
    FROM SYSTEM_1_TABLE
    WHERE SECTOR_CODE = 'XX';

COMMIT;

... then remove the temporary sector.

That should work but make a backup of that universe first (just in case).

 

[jrmapes]

That looks much easier.

If you were to outright delete the SM sector in System_1 would you not lose all the data that is in the records including those things I listed above?
What if you only wanted to change some of the entries in the record.

For instance, you wanted to keep all the subsector and world names, as well jump routes, sector/subsector/world text descriptions, and the rest that isn't directly given as data in the to be imported SEC file?



What I am trying to do is to replace only the

UWP Proper (xxxxxxx-x)
Bases
Codes & Comments
Zone
PBG
Allegiance
Stellar Data

while leaving all other data intact by using the SQL and not having to re-hand enter/correct everything.

I don't know if it can be done, if it can then the necessary SQL commands like in the previous post would be most excellent.

Jerry

 

[Hemdian]

Using the method I outlined above you'd keep the sector and subsector data, and I believe the borders and routes and notes. They're all stored in separate tables. But you'd loose star data due to referential integrity settings. You could use a variation of the method above to copy the SM star data to the temporary XX sector before you clear out SYSTEM_1_TABLE, then copy it back after ... IIRC its STAR_TABLE you need to look at. (I'm at work at the moment so this is all from memory.)

I had a brief look at updating only certain fields on the system table but I think you run into limitations in Borland's version of SQL (its dead easy with MS SQL Server or Oracle). It should be possible but I need to have a little play with it to find out.

If you know databases then there is a logical schema in PDF form. (Be aware that some entities, like SYSTEM_TABLE are views covering SYSTEM_1_TABLE and SYSTEM_2_TABLE ... in these cases adding to xxxx_1_TABLE will trigger an update to the corresponding xxxx_2_TABLE.)

 

[jrmapes]

Please bare with me, as this sql stuff is still pretty new to me.

Using the method I outlined above you'd keep the sector and subsector data, and I believe the borders and routes and notes. They're all stored in separate tables. But you'd loose star data due to referential integrity settings.

Now is this the same "Star Data" that is or would be imported from the new SEC file or is it a different bird altogether? From what I see, there is only the Stellar Data from the UWP that is used in the World description. I want to make sure we are on the same page and are just using two different terms to describe the same thing.

Your Star Data = My Stellar Data from the UWP?

You could use a variation of the method above to copy the SM star data to the temporary XX sector before you clear out SYSTEM_1_TABLE, then copy it back after ... IIRC its STAR_TABLE you need to look at. (I'm at work at the moment so this is all from memory.)

This I believe is what I am looking for/trying to get at. A fairly easy way to Append/Insert the data (if any) that I may want to use from the original System Table that came with Universe into my new system table. Then as you said earlier, I can then delete the original system table and insert my new system table that has been modified with anything I want to keep out of the old one, where the old one was originally.

As I said my biggest concern was loosing all the sector names, subsector names, as well as existing notes and descriptions. But if they are in a separate file and my new worlds/hexes match up correctly (which shouldn't be an issue) then all of that data should not be affected by the System Table exchange.

I had a brief look at updating only certain fields on the system table but I think you run into limitations in Borland's version of SQL (its dead easy with MS SQL Server or Oracle). It should be possible but I need to have a little play with it to find out.

That is what I am afraid of. I have done this sort of thing on MY SQL on my websites but Borland's SQL seems to be a lot more fickle or a lot less capable. But considering it is "free" you can't expect all the bells and whistles. *shrug*

I look forward to your further input on this avenue.

If you know databases then there is a logical schema in PDF form. (Be aware that some entities, like SYSTEM_TABLE are views covering SYSTEM_1_TABLE and SYSTEM_2_TABLE ... in these cases adding to xxxx_1_TABLE will trigger an update to the corresponding xxxx_2_TABLE.)

I think I downloaded that earlier. But can't remember how much I read of it. Time for a refresher it seems.

Thanks for all the help
This is so very different from the days of yore when I was working with Ashton-Tate's dBase III+ or worse the companies were still using COBOL or the old Sperry-Univac standard MAPPER package as their database. That was when SQL was just being invented and wasn't in most dB software packages. I think rBase 5000 was the first I saw with a basic SQL built in but I never got a chance to mess with it much since I switched jobs from programming and dB management at one of Sprint/United Telcom's R&D facilities in the city to a mechanical engineering position with a small local company closer to home. Less pay, but swapped a 3 hr round trip commute for a 20 minute round tripper. Nice being home by 4:15p every afternoon-- anyway...

Jerry

 

[Hemdian]

Hopefully the following wont confuse you (there's a danger it will):

Its the same star data. When the SEC file is loaded the main details (name, UPP, etc) are written into SYSTEM_1_TABLE except for star data and comments. Comments are sorted into values that are set according to specific rules (In, Ni, Ag, Na, Ri, Po, etc) and more arbitary values that are set on an ad hoc basis (Cp, RsB, ...). The first set are thrown away and the database recalculates them (using something called a 'trigger') in SYSTEM_2_TABLE thus ensuring those definitions are consistantly applied. The second group are stored in the remark fields of SYSTEM_1_TABLE ... but only upto 3 per system. Meanwhile the star data in the SEC file is stored in the STAR_TABLE ... 1 record per star. (So if the system is a double then it'll still only have one system record but it'll have 2 star records.)

The core tables in the Universe database are arranged in a hierarchy: sector, subsector, system, and star. (There are more but these are unused in the current release.) Delete a record from one of these and the database will automatically delete its child records, and their children, and so on ... a 'cascade delete'. So if you delete a system record the sector and subsector records remain intact but the corresponding star records are deleted. The border and route tables hang off the sector table and so are also unaffected by deleting a system record. And library data (AKA text) is completely independent. I've tried to use colour in the database diagram to show where cascade deletes can occur.

When I first wrote Universe InterBase appeared superior to MySQL but I think that's now reversed. Regardless Universe 2 will be using MSDE 2005 (AKA MS SQL Server 2005). And I'd love to rewrite the code into VAX COBOL but I don't think many people have access to VAXs these days (my day job up to the end of 2001 was as a VAX COBOL programmer).

 

[jrmapes]

Hopefully the following wont confuse you (there's a danger it will):

... And I'd love to rewrite the code into VAX COBOL but I don't think many people have access to VAXs these days (my day job up to the end of 2001 was as a VAX COBOL programmer).

Nope that made sense. No confusion.

Thanks again for all the help. I will have to find the time over the next few days to tear into it further. I was i the process of trying all of this last week but got interrupted after a guy not paying much attention to where he was going decided that he so enjoyed merging into the next lane of traffic that he decided he wanted to merge into our lane while we were still in it. Since then when not working, it seems all I have been doing is talking on the phone to insurance people. Since the final inspector should be out this afternoon, we should be getting the car into the shop tomorrow or Friday - so hopefully I can start running database swaps later today or at least before the weekend.

I actually like the idea of having the program in COBOL. Heh. Now maybe not necessarily VAX , my old mainstay was MS/VS COBOL but the desktop package I used at home to work on code covered VAX as well IIRC. Great thing about the desktop MS COBOL was the converter was very good and it came with a compiler so you could take your code and your JCL and compile it to a stand alone EXE file. It also came with a top notch screen builder (for the time) so I ended up writing a few programs for games that way.

Although once we developed a data passer to bridge COBOL and dBase it became a lot easier for the end user (dBase had a much better screen I/O interface). We set up it up where you could do all your basic data I/O in dBase (easier to create your data files) then when you needed to do some heavy number crunching it passed it all off to COBOL while simple relational db searches were still done on the desktop. Then when COBOL was done doing whatever crunching it had to do it automatically passed it back to the originating desktop. Course it helped that me and my roommates (we were all Referees/GM's at the time) had 4 computers in the house and a 5th that served as our server. But I used the same on my solo desktop for some time.

I still found this method quicker (after the compile) than using PASCAL or Turbo Pascal. Although PASCAL, especially Turbo, was a breeze to program, even to build databases. But when everyone started switching over to C+ and C++ we couldn't touch it for speed and I was out of the programming field by then so I never devoted much time to C and all that followed. ADA was as close as I came to C but no one was using it except for the DOD and a handful of other govt. agencies.

I still kinda miss doing the COBOL and FORTRAN programs. MS had a great FORTRAN package like its COBOL that could be compiled as well. I still have all those packages too. Wonder if they are still any good? (5-1/4 floppy) I haven't tried to load them onto my desktop in years.

Jerry