Jump to content


Photo

Direct database access

database sql schema

  • Please log in to reply
10 replies to this topic

#1 rowan.bradley

rowan.bradley
  • Members
  • 22 posts

Posted 25 May 2012 - 07:08 AM

Is there a way of accessing the TMG database directly? Obviously to do this I would need to thoroughly understand the schema of the database - is this documented somewhere?

The reason I'm asking is that I' ve discovered a rather interesting web based way of displaying genealogy data, and the guy who wrote this has given me a copy of his code. I would like to try it with my genealogy data, but it's in TMG. For this tool it needs to be in some tables in MySQL, and the design of the tables will of course be different from those of TMG, so I need to produce a translation tool, or an export and import tool. I know I could export all the data into GEDCOM and then write a GEDCOM import tool for the display system, but (a) going from a relational database to an idiosynchratic text based format and then back to a relational database seems like hard work, and (B) I don't want to lose any data that might be in TMG but is not in the GEDCOM export file.

Thanks - Rowan

#2 Jim Byram

Jim Byram
  • Moderators
  • 7,340 posts
  • Gender:Male
  • Location:Framingham, MA

Posted 25 May 2012 - 07:24 AM

You can export the data tables to other formats. File / Export

A very old database description is available here...
http://www.whollygen...p?showtopic=381

#3 rowan.bradley

rowan.bradley
  • Members
  • 22 posts

Posted 25 May 2012 - 09:45 AM

You can export the data tables to other formats. File / Export

A very old database description is available here...
http://www.whollygen...p?showtopic=381


You can export the data tables to other formats. File / Export

Thanks for your reply. I've just tried exporting to Excel. What it did was to create 27 individual XLS files, each of which appeared to contain a single database table. In order to make use of this data, I will need to know what each column in each table means, how the data is encoded (where it's not obvious, e.g. with dates), and how the data in the various tables is connected together (e.g. info about "foreign keys").

A very old database description is available here

It's not going to be much good having a database schema for version 3.6 in 1998, if any reasonable amount of development has gone on between then and now (which it surely must have done, to go through 5 major version numbers and countless minor upgrades). Surely they must make the schema avaialble? How do people like John Cardinal who write extensions to TMG manage?

Thanks - Rowan

#4 sam.m

sam.m
  • Senior Members
  • 125 posts
  • Gender:Male

Posted 25 May 2012 - 06:32 PM

You can find more information here

#5 TPG

TPG
  • Members
  • 57 posts
  • Gender:Male
  • Location:Silver Spring, Maryland

Posted 27 May 2012 - 09:28 AM

You might find the following utility that I wrote useful. It displays the actual structure and contents of the TMG files associated with a given TMG Project. You can download it from:
http://www.crestline...Display/tmg.zip.

Save the zip file to an arbitrary directory and then expand it into its component files: tmgFiles.exe and tmgFileList.txt. When you double click on tmgFiles.exe, you then chose a TMG "Project", click on "Find Files", and then choose the particular file to display. It works with TMG version 7 and 8.

Tom Giammo

#6 ggilbert1

ggilbert1

    Glenn Gilbert

  • Senior Members
  • 282 posts
  • Gender:Male
  • Location:Sumner, Washington, USA
  • Interests:Main surnames: Anderson, Gilbert, Graham, Vigne

Posted 27 May 2012 - 11:51 AM

When I attempt to use tmgFiles.exe to open a TMG Project I'm getting an error message: Unable to open table. Provider cannot be found. It may not be properly installed.
tmgfilesError.png
WinXP Pro SP3, TMG 7 & 8 (TMG program files located on logical drive in path H:Program FilesThe Master Genealogist vN, project files located in default path for each version)
Same result on Win7 Pro, 32 bit TMG8 program located in default path, projects located in C:UsersPublicDoucmentsThe Master Genealogist v8Projects

Edited by ggilbert1, 27 May 2012 - 02:50 PM.

Glenn Gilbert
Gilbert-Genealogy

#7 TPG

TPG
  • Members
  • 57 posts
  • Gender:Male
  • Location:Silver Spring, Maryland

Posted 27 May 2012 - 05:28 PM

I apologize! I forgot about the need to have the Foxpro OLE driver installed. I put that in a long time ago and it completely skipped my mind. You can get the Foxpro OLE driver installation package at http://www.microsoft...&displaylang=en. Once you've installed the driver, it should work.

Tom Giammo

#8 rowan.bradley

rowan.bradley
  • Members
  • 22 posts

Posted 28 May 2012 - 02:57 AM

Once you've installed the driver, it should work.

It does. This is a very useful utility. However, it's still going to take some considerable amount of time to puzzle out how all the tables are used, what the fields mean, how the data is encoded, which foreign keys are referred to etc., even with the help of the v3.6 file structure document and the Wikipedia article referred to earlier in this thread.

By the way, does TMG have any concrete plans for migrating to a non Foxpro database?

Thanks - Rowan

#9 ggilbert1

ggilbert1

    Glenn Gilbert

  • Senior Members
  • 282 posts
  • Gender:Male
  • Location:Sumner, Washington, USA
  • Interests:Main surnames: Anderson, Gilbert, Graham, Vigne

Posted 28 May 2012 - 09:39 AM

Thanks - :thumbsup2:

I apologize! I forgot about the need to have the Foxpro OLE driver installed. I put that in a long time ago and it completely skipped my mind. You can get the Foxpro OLE driver installation package at http://www.microsoft...&displaylang=en. Once you've installed the driver, it should work.

Tom Giammo


Glenn Gilbert
Gilbert-Genealogy

#10 TPG

TPG
  • Members
  • 57 posts
  • Gender:Male
  • Location:Silver Spring, Maryland

Posted 28 May 2012 - 05:46 PM

As Jim Byram has noted, the best description made available by TMG is the old version 5 file description at www.whollygenes.com/files/tmgfstr.zip. There have been some structural changes since then, but most of what is described there has remained unchanged. The changes are mainly in the form of additions. The most important sections in the old version 5 description detail the coding of the various fields, such as the date fields - and these have remain unchanged.

At some point in the past year or so, I nagged TMG and they sent me a large diagram of the relationships among the data elements in the various files, as of version 5. This does show which data elements in one file are keys to other files. Some of this is not obvious, since the names of some of the corresponding elements are not identical. Again, this is mostly unchanged. I don't remember whether I had to promise not to distribute the diagram, but perhaps TMG can make that diagram public.

My primary interest in all of this was to create a program that supplemented a gedcom file exported from TMG with additional notes culled from the "witness" data in TMG. I was able to build that program using the information from tmgfiles.exe and the other information noted above.

Tom Giammo

#11 elevator

elevator
  • Senior Members
  • 147 posts
  • Gender:Male
  • Location:California, U.S.A.

Posted 30 May 2012 - 09:22 AM

I have used the Exodus project by norwegian Leif Biberg Kristensen. He migrated from a TMG database to a self-authored solution. It was the precursor for his Yggdrasil project. Both of these are available with full source code on his website. You can read about it here: http://solumslekt.org/forays/tmg.php It does require quite a bit of knowledge of php and Postgres or MySQL.

You can read about Yggdrasil here: http://code.google.c...asil-genealogy/




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users