Open Office #openoffice #oobase #oocalc #odbc

Posted on 2010-04-182019-01-26 by Tom Holden

Open Office has a database program (Open Office Base) and a spreadsheet program (Open Office Calc). Base can be connected via ODBC to a SQLite database in much the same way as has been described for Microsoft Access, using the same SQLite ODBC driver (See Part One of MS Access Engine). A .odb file is created, saving the connection information and stored SQL queries. For later access to the SQLite database, one can open the .odb file with Base.

Additionally, like MS Office, one can register the .odb database with Open Office so that its tables and queries can be accessed in text and spreadsheet documents using Open Office Writer and Calc, respectively.

Base has a graphic query design tool similar to that of MS Access as well as a direct SQL editor.

Single queries that have been developed using other SQLite managers work in many cases when copied into the Base SQL editor and run using the RUN SQL Direct mode; some things that do not work:

multiple SQL commands in the one query
runtime input parameters
comment lines before the first SQL word
semi-colon (;) and apostrophe or single quote (‘) in comment

With the RUN SQL Direct mode turned off, most queries that work in other SQLite managers do not work when copied into the SQL View and run. I think in this mode, the commands are OpenOffice.org syntax which may be limited to a subset of the SQLite command set. It is not readily apparent what it is that allows a query to work in the direct mode and not in the OpenOffice mode. The error messages are useless and give no clue where to look.

Extracting the query from AllFacts4Persons9.sql to return all events for the individual worked “out-of-the-box”, complete with very complex date processing, in the Run SQL Directly mode. Opposingly, that query at the foundation of shared events for a couple would not work as it comprises multiple SQL commands. I have attempted to rebuild it using the SQL Design View method and can get it to work without processing the event date field but, using this method, there seems to be no way of incorporating the complex CASE structure employed for date processing. Simply pasting that part into the code generated by Base throws up untraceable errors. Probably the SQL CASE expression is unsupported or is different in the OpenOffice.org syntax.

Base SQL Design View produces what we would call “bloated” code – double quotes around table names and field names, for example, and no short alias for a long table name. It has the virtue of being able to query not only tables but also queries stored previously in the OOBase database. The SQL direct mode cannot query stored queries while the indirect mode does not support UNION.

The inability to incorporate the querying of queries, complex expressions and the UNION operator in one mode makes it very difficult to accomplish in OOBase what one can do outside of it. Moreover, OOBase’s filtering and sorting functions for results work only in the indirect or native mode, not in the SQL direct mode – another shortcoming.

The connection from OOCalc to a registered database can help. Queries and Tables can be selected and results viewed in the Calc viewer. Results can be dragged onto a spreadsheet and filtering applied there. Even more directly, a query can be selected from the Base Queries list to a Calc spreadsheet and the results will populate the sheet.

Unfortunately, OpenOffice and the ODBC connection to the RootsMagic 4 SQLite databases is so limited and failure-prone that it cannot be recommended and prospective users are warned of the prospect of frustration and disappointment. Hence, no further detail is forthcoming from this author on how to use set up and use OpenOffice with SQLite.

Discussions & comments from Wikispaces site

Open Office Base connection to Rootsmagic data base

craigg43
18 April 2010 00:55:04

I’ve tried to create the connection alluded to, but have not been successful.
Could someone please provide the detailed instructions necessary to do this.

ve3meo

ve3meo
18 April 2010 15:12:21

I revised the page, not to provide any further detail but rather to warn you and others away from expecting much out of an OO connection to RootsMagic.

In any case, once you have installed the ODBC driver, you use
Control Panel > Admin Tools > Data Sources (ODBC)> ODBC Data Source Administrator
to
Add a User Data Source > Select SQLite3 ODBC Driver > SQLite3 ODBC DSN Configuration
to
Browse to/Select the RM4 file, assign a DSN name, accept the rest of the field defaults, OK
and
OK to close ODBC Data Source Admin

THEN

open OOBase,
File>Open to Database Wizard,
1. Connect to an existing database > select ODBC
2. Set up ODBC connection > Browse > Data Source > select the DSN you named above
Finish

You can then save a file from OOBase that has all these settings in it for later re-opening from the File>Open menu.

Pulling Together All the Events for An Individual #person #events #lifeline

Posted on 2010-03-102021-03-03 by Tom Holden

This discussion was started around the Database System Catalog page and all posts up to its continuation on this page can be reviewed in full here.

In summary, MarkVS posed this problem:

One thing that I wanted to do, and I expect might be of interest to others, is to be able to
stream all the events for an individual and then, depending on what reporting I wanted to
do, sort and/or group and/or filter the records accordingly.
...
From what I have learned to date it appears to be neceesary to run three separate queries
and combine the results to get the "complete" datastream. These are the queries I think
you need:
1) Firstly the straightforward one. The Events held on the Event Table for individuals need
to be pulled off. It is important to select only those with an OWNERTYPE of 0. You can link
to the various other tables from this data to pick up the FactType description, name of the
person etc etc, as required.
2) Secondly, the family events. These are also held in the Event Table but with an OWNERTYPE
of 1. This means that the event being recorded acutally applies to 2 people. So to get this
data into a stream of events you need to take the value of the OWNERID field and use this to
look up the record in the FAMILY table (ie not the PERSON table as you would if the OWNERTYPE
was 0). In the Family table you will see the Husband and Wife ID and these can be used to pick
up the names of the two people to whom the event applies. Then in theory generate an Event
into the data stream for both of them.
3) Thirdly we need to pick up Shared events. These are recorded in the Witness table. This
holds the Person ID to link back to the Name of the person and the Event ID to link back to
the Event record (and from there to the fact description etc etc).

romermb responded with a query design based on a UNION of five SELECTs, demonstrating the use of constants and NULLs in the SELECT fields to build a uniform results set from each SELECT allowing the UNIONs to append the result sets together.

One of the five early queries is extracted below, showing the constant ‘Principal’ and NULL being used to fill columns to correspond with the layout of the other queries; COLLATE NOCASE being used to replace the RMNOCASE collation sequence that must be embedded in the RootsMagic software and unavailable to third party SQLite managers; COUNT and GROUP BY to flag and skip duplicates. This version was revised by ve3meo with explicit use of INDEXED BY and NOT INDEXED clauses to override sqlite’s query optimiser which was found to adversely affect the speed of this set of queries by a factor of ~1000, in the worst case. Later versions avoided this deprecated practice by hiding the IsPrimary field from the SQLite query optimiser so that it would not choose a slow index; one slight change in the WHERE clause is all that is needed: +NameTable.IsPrimary=1.

The current version is extensively revised from the original and is compatible with a wider set of SQLite managers plus OpenOffice Base than was version 9 and runs as fast or faster. It incorporates facts for individuals, couples, parenting and shared facts with fairly comprehensive date reporting. Now includes persons not in a database tree but named as having a role at an event for a person in a tree.

Download: AllFacts4Person11-OO.sql
TH 2021-03-03 tested on #RM8.

UNION ALL -- add all events for Individual
SELECT
  Facttypetable.Name COLLATE Nocase ,
  'Principal' ,
  Nametable.Ownerid ,
  Nametable.Surname COLLATE Nocase ,
  Nametable.Suffix COLLATE Nocase ,
  Nametable.Prefix COLLATE Nocase ,
  Nametable.Given COLLATE Nocase ,
  NULL ,
  NULL ,
  NULL ,
  NULL ,
  NULL ,
  COUNT( 1 )
FROM
  Eventtable NOT INDEXED INNER JOIN
  Facttypetable NOT INDEXED ON Eventtable.Eventtype = Facttypetable.Facttypeid INNER JOIN
  Nametable INDEXED BY Idxnameownerid ON Eventtable.Ownerid = Nametable.Ownerid
WHERE
  Eventtable.Ownertype = 0 AND Nametable.Isprimary = 1
GROUP BY
  1 ,
  2 ,
  3 ,
  4 ,
  5 ,
  6 ,
  7 ,
  8 ,
  9 ,
  10 ,
  11 ,
  12

Discussions & comments from Wikispaces site

MS Access Engine – The BLOB Fields #msaccess #blobfields #conversion

Posted on 2010-02-232022-04-28 by MVS

If you are using the Microsoft Access database engine to interrogate the RootsMagic SQLite database (see MS Access Engine and MS Access Engine – Event Query) then you will hit the problem that, on the face of it, Access does not ‘understand’ the BLOB fields within the SQLlite database. This wiki describes the workround that you can use to get this data deciphered.

The problem seems to be that the ODBC driver does not correctly interpret the contents of the BLOB field; perhaps because there are more options in SQLite for what might be held in a BLOB than there are in MS Access.

In some cases, if you develop a Query from within Access and run it it will object to even reading the records IF you have included BLOB fields in your output.

As long as you are not using the BLOB fields you will not have a problem; however these BLOB fields are used to hold a lot of interesting information including some of the narrative sentences used in many of the standard RM reports. So you will probably want to use this data sometimes.

There is a simple workround that runs along the same lines as the problem described in the introductory MS Access wikis concerning sorting and selection; that is to use a simple expression to convert the BLOB into a Text field.

For example, if you were looking at the EVENTTABLE and wanted to examine the Note field you could use the following Access expression to get the text diaplyed in a normal format:

Expr1: StrConv([Note],64)

This converts the Unicode content of the BLOB into normal text. The screen shot below shows how this Query might look.

And the scrrenshot below shows the output (note I have included a fourth column showing hte Note field in raw format). The field Expr1 is the converted output – you can replace ‘Expr1’ with any other name that you want the field to be known as:

LIMITATION. The StrConv function has a maximum output length of 255. So if the text contents of your BLOB field are longer than this it will be truncated. I think in most cases, for the purpose of ad hoc reporting, this should not be a problem.

Prev: MS Access Engine | MS Access Engine – Event Query | MS Access Engine – Using Excel | MS Access Engine – EXCEL Pivot Tables

Discussions & comments from Wikispaces site

MS Access Engine – EXCEL Pivot Tables #msaccess #msexcel #pivottable #events

Posted on 2010-02-232022-04-28 by MVS

If you have followed the guides to enable interrogation of the RootsMagic SQLite database via the Microsoft Access engine (MS Access Engine and MS Access Engine – Event Query and MS Access Engine – Using EXCEL) then you will also be able to use the Pivot Table feature of EXCEL to analayse your genealogy data.

The Pivot Table feature in EXCEL is one of the most useful tools availale to quickly sort and summarise data in any database. This is a quick guide to show how easy it is to look at the RM data using the Access Queries that we set up in the earlier posts.

Firstly – fire up Excel (I am using Excel 2007 here – but the Pivot Table feature has existed in earlier versions of EXCEL for some time). Then open your Access databse (which in reality is just a collection of Quries that point to the real data in your live RM database).

Select the MVSALLEventsCombinedQuery (or the equivalent if you named your tables differently). You should then see the usual screen of data, semething like this….

Click the ‘Insert’ tab at the top of the screen and then click the ‘Pivot Table’ button on the ribbon. You should be presented with a screen like this:

Excel will have automatically selected the full range of the spreadsheet and suggested creating a new Worksheet for the Pivot Table. Unless you have any reason to do anything special – just accept these options by clicking the OK button. You will now get a screen like this:.

For this example complete the following steps:

Select and drag the “FactType” field into the Report Filter box.
Select and drag the Surname” field into the Column Lables box.
Select and drag the “Event Year” field into theRow Lables box.
Select and drag the “Event Type” field into the Values box.

Your screen display will now look something like this: (but with different dates and surnames according to the contents of your own database) ::

What this is showing is a count of Events recorded in the RM database for each year for each surname. Unless your RM database is very very small this will be a very large grid. To make it meaningful we need to put in some selection criteria.

Click on “Fact Type” in the Pivot Table Field List area. you will see a list of the Fact Types being reported. Click on “Census” and press the OK button. The Pivot Table will be quickly redisplayed showing a summary of Census types only. This will also mean that the Event Years column on the left will be reduced to years in which you have recorded census events (probably 1841, 1851, 1861 etc). You will still have a large number of columns running across the screen – with entries for all the Surnames on your database for which Census events are recorded. My display lookes like this:

Now click on the Surname in the PivotTable Field list and DE-select the “Select All” box at the top of the list of Surnames. Then scroll down and select one or more Surnames that you want to analyse (I am going to pick a couple of variants of the Dawes name in my example, from my database). When you have finished updating the list press the OK button and you will see a new display analysing the data you have selected. Mine looks like this:

As you can see I now have an analysis of the number of occurences of Census events recorded in my database for the specific variants of Surname that I have selected and showing the years they were recorded in.

It is important to remember where the data came from though. I am analysing the MVSALLEventsCombinedQuery. This means that I am listing all the events whether they were recorded against the individual or where I have SHARED them in RootsMagic.

Here is another example where I have altered the selection criteria – I am now only selecting those items with an Event year of 1911 and an Event Type of Census and I have removed the Surname selection so that ALL surnames are being reported. This gives me a grid showing the counts for all the Surnames for which I have 1911 Census records entered:

The PivotTable tool is a very powerful analysis feature. It is worth spending a little while playing with the selectiong and reporting features and this is easily done by draggin fields into and out off the report. You can also filter on the contents of any field, whether it is being rpeorted on or not. You can place multiple fields in any of the Reprot Filter, Column Labels, Row Labels or Values boxes to create more complex summaries.

You can Print your Pivot Table analyses using hte usual EXCEL print features.

You can save your PivotTable workbook for later use. When you re-open it at a later date it will show the data as it was saved. However, if you select the Tab showing all the items listed in grid view (ie the original grid that resulted form opening the Access databse and selecting the MVSALLEventsCombinedQuery and the click the Data tab and click the Refresh All button on the ribbon Excel will go away and re-stablish the data link and refresh the data on the screen, including your Pivot Table with the current contents of you live RM database.

You can select the PivotTable report grid and produce Excel spreadsheets in the usual way – these too will by updated when you Refresh data on saved reports. For example we can add one to the Dawes analysis I have just designed :

PS – there is also the Pivot Chart function which can be selected instead of creating a Pivot Table. This is another way of, directly, creating graphics from complex data using identical drag/drop and selection functions. You may want to experiment with this feature if you are more into graphs than grids.

END.

Prev: MS Access Engine | MS Access Engine – Event Query | MS Access Engine – Using Excel Next: MS Access Engine – The BLOB Fields

Discussions & comments from Wikispaces site

MS Access Engine – Event Query #msaccess #marriage #sharedevent

Posted on 2010-02-232022-04-28 by MVS

Installing the ODBC driver and setting up the link to the RootsMagic SQLite databse is decribed in the the “MS Access Engine” page. This will allow you to look at the contents of your RM database Tables individually. In fact it will also allow amendment of data unless you set the Access tables to be read -only.

Aside from the obvious shortcoming that looking at one Table is not that helpful or exciting it becomes clear that there is another problem in that you will find that you cannot Sort or Select on all the fields in each table – this soon becomes a seriously limiting feature. For example – if you open the “NameTable” you will see that when you click the little sort icon (a triangle in AccessV7) at the top of each column for most columns you will find the sort functions greyed out. So you are allowed to sort on, say, the OwnerID abut not the Surname, given Name and most of the other fields you would want to use!.

In practice you will need to set up Access Queries (which are in effect SQL queries) to bring together the contents of more than one table and, while settign up these queries, we can also redefine the fields so that sorting is possible on all of them.

Initially I wanted to set up a query that produced a data stream of all the event types for an individual. In practice this requires 4 (perhaps 5) different queries:

the first to generate records for ‘normal’ events
the second to generate records for where the individual is recorde at a Shared Event (by default as a Witness)
the third to generate records for all Males who are married
the fourth to generate records for all Females who are married
the fifth(optional if you want to generate birth or other events for which an individual event record may not exist (I have nto done this myself).

Subsequently we link the output from all four of these Queires into a single query – and this is what we can use in reporting or to look at the data via Excel or any other external tool which can open and use Access databases.

It is important that the format of the output of each individual query is identical to the others; ie same number of fields with same field names and types and length – this is to ensure that the combines Query holds records of an indentical format – even though they are referring to different types of Events in the life of an individual.

If you go to the Query Builder within Access you can pull together the NameTable, EventTable, FactTypeTable and PlaceTable. As you can see you will need to pull in the PlaceTable twice – the scond time the systemw ill automatically give it an alias name. The reason you need to pull in the PlaceTable twice is that it holds both the Address Details and the Place Details – so you need to point to it from two different fields and Access demands that it called in twice and given different names. When you have pulled them in to the Query you need to set up the links shown in the diagram below: if Access generates automatic links that are different from the ones shown below then just right-click to selec tthem and press the Delete button. It is important you only have the links shown below.

Now below this you have the Access field definition; these are the fields that will be output form the Query; so we only need to select the fields that we want to use – not all of the fields in all the tables. However, as you will probably want to use this query for different sorts of reports it is worth including all the obvious fields so that you dont have to amend it in future.

You could simply double click the fields in each table that you wanted to add to the Query output but this will still leave us with the problem of not being able to sort on the fields. To solve this we create a little function in each field. This little function in effect creates a new field that has been stripped out of the Table. When this is done you will find that it has full sort/filter properties. The format of this function is, for example:

Surname: Left([NameTable]![Surname],50)

The creates a new field called ‘Surname’ but it creates it by taking the first 50 characters from the real Surname field in the NameTable. Now you do not have to strip off 50 characters – you can use less or more; but I found 50 to be generous and I used the same length on most of the fields.

So to summarise – if you the field in the oringal Table can be sorted on then just double click to add it to the Query output; it it does not write the little formula above to create a bummy field. Shown in the two screen shots below are the fields I selected for this view:

Now there are a couple of things you need to note. In the left hand field I created a new field called EventType and forced it, using the formula, to enter an “E” in the field. So every record that comes out of this query has an E in the first field. Each of the other three queries has a different value; “M” for male marriage events.”F” for female marriage events and “W” for Witnessed events (although you may want to use and S to indicate Shared events). This is so that when the output of all foru queries are coming through in a single stream I can see within the report generator how they have been generated. Without this indicator they would all look the same.

The other thing is the definition of the EventYear field. Here I changed the little formula to just pick out the 4 digits of the year from Date in the event table. Now this means that any Day or Month information is stripped off. This I found is good enough for most reporting requirements and means you dont need compicated formulas to cope with when the month and day data is missing (quite often) or when the dates are estimated etc etc. There are other topics on this Wiki which go over the format of the date fields. The formula I use is :

EventYear: Mid([EventTable]![Date],4,4)

Now if you save this query (I called mine MVSEventswithPlaces) and run it it should produce a grid view showing all the Event records in your databse. you should also find that yuo can sort and select/filter on every column.

So you can already start to produce meaningful reports from it; however you are not seeing the events which the person is “Sharing” with someone else, nor will you have any Marriage events. So we need to creat three more identical queries as shown below:

The above I call MVSFamilyEventswithPlacesFather.

The above I call MVSFamilyEventswithPlacesMother. Note it is nearly identical to the Father query so it is best to copy the Father Query and Paste it in under the new name then you only need to change the link betwwen the NameTable to the FamilyTable from the FatherID field to the MotherID field.

The last one above I call MVSWitnessEventswithPlaces.

It is important that you create identical list of fields/types/lengths in the output columns though.

You should be able to save and run each of these in turn and confirm and see the output they create in an Access Grid.

There is now one final thins to do; that is to create an overall Query that brings together the output of all four of these Queries. To do this we need to write a little SQL Statement.

In Access go to the Create Query View but htis time close the select Tabkle dialog and Right Click on the name of the Tab. This will give you an option to select SQL View. Take this option and then copy and paste the following into the page:

SELECT *
FROM MVSEventswithPlaces
UNION ALL
Select *
FROM MVSFamilyEventswithPlacesFather
UNION ALL
SELECT *
FROM MVSFamilyEventswithPlacesMother
UNION ALL SELECT *
FROM MVSWitnessEventswithPlaces;

Then save this Query as MVSALLEventsCombined.

NB – This assumes you have named your Queries the same as I named them – if not – then use the names you decided to use in theSQL statement.

Now if you run the MVSALLEventsCombined Query you will have a grid diaplyed which shows all the four different types of events in a single view. You can sort/select as required.

WARNING – Unless you have turned it off these Queries give you WRITE ACCESS to the RM database. Do not change any data through these Grids unless you understand it and know the consequences of changing it.

Prev: MS Access Engine Next: MS Access Engine – Using EXCEL→

Discussions & comments from Wikispaces site

MS Access Engine #msaccess #msexcel #crystalreports #odbc

Posted on 2010-02-232022-04-28 by MVS

I wanted to be able to create my own reports from the RM database and the custom report facility in RM has a number of shortcomings:

it is not very sophisticated and even quite apparenly simple reports are impossible to achieve (eg show me which census records are missing for an individual)
I am not familiar with the tool – but I am more familiar with other tools such as Crystal Reports, Excel and Access and the facilities they give (including a range of superior default report layouts)
There is an issue that any custom reports may get lost when transferring data from one RM database to another through a GED interface.

In addition, although I am familiar with SQL as a concept I have not used it beyond the basic level and therefore the manual construction of INNER, OUTER, UPPER and LOWER joins as well as UNIONS and DIVORCES meant that the use of the various SQL Lite managers was not that helpful. The facilities for automatically linking Tables within Crystal or Access allow users at my level to make the necessary Table Links without having to understand too much of the syntax or how it is achieved.

I quickly discovered that although there is a readily available ODBC driver available it could not be used directly within Crystal Reports (throwing up error internal error messages). Although it did work when using it within VB or Visual Studio. Unfortunatley the latter options then meant the manual coding of the SQL syntax.

After much experimentation and thought i realised that MS Access could be used to link to the RM database using the OBDC driver. This provides a number of benefits to those who may want to have more access to the internals of the RM database:

many users will have MS Office installed including those with the Access option
MS Access provides simplified, visual Table linking options and removes much of the need to know SQL syntax
the MS Access tables are readily understood by most programmes that read databases (unlike SQL Lite). So once you have linked to the RM Database via Access you can get to the data from many, many other programmes; eg EXCEL, WORD and so on. This opens a lot of opportunities to Users who have a modest amount of IT literacy without being full-blown experienced programmers

There a a few quirks to oversome however. Nonetheless, it will take no more than 10 minutes to create a working Access link to the live RM data and a futher 20-30 minutes will both iron out odd quirks to allow flexible access, selection and formatting of reports from your live RM data. This is ‘one-time’ effort – once it is done then new reports can be created or existing reports amended very easily – using the tools you are familiar with.
.
How To Do It.

PLEASE NOTE: I am using MS Access V7. If you have earlier versions there is no specific reason this will not work but some of the dialogs within MS Access have changed and you wil have to select the appropriate options accordingly.

PLEASE NOTE. I have used this on two computers; one running Microsoft Vista and the other Windows 7.

PART ONE – Installing the ODBC Driver.

The ODBC driver for SQL Lite is available free from here.

There are a number of versions of the driver available but unless you are running some old software you would usually select the latest version at the top (this is the version I used).

Download and install the driver. (This was entirely trouble free for me – but if you have any problems you may want to check you are logged on with Administrator priviledges).

PART TWO – Creating the Access Database and Linking to RM

Launch MS Access. Select the option to create a new database and name it and save it.

Select the “External Data” tab at the top and click on the “more” option. You will be given a list of options. Select the top one “ODBC Database”.

You will be presented with a screen asking if you want to Import the Data or Link to the data

If you choose to IMPORT the data you will, when the process is complete, just have a copy of your data in the Access database. This will not be automatically updated when you change your live data. It is unlikely you will want to use this option unless you intend to manipulate and update the data.in a safe environment.

If you select the ‘LINK to Data source by creating a Linked Table’ option then no data will be put in the Access database and program will always go to your live database to get the information you want. This is the option to select if you want to be able to most easily report on the live data in your RM database. After you have selected this option click the OK button.

You will then be presented with a “Create New Data Source” dialog; select the appropriate option and click OK.

You will then be presented with a further “Create New Data Source” dialog; scroll down and select the ‘SQLite3 ODBC Driver’ and click OK.

You will then be presented with a further dialog – just click the Finish button.

You will then be presented with a ‘SQLite3 ODBC DSN Configuration’ dialog. Enter into the Data Source Name field a user friendly name that you want to remember the database by; eg ‘Live RM Data’.

Then use the Browse button to go and select your live (or test) RM database that you actually want to link to.

Ignore all the other fields and press the OK button.

You will then be returned to you previous ODBC dialogs which you can now close with the OK buttons.

Finally you will return to MS Access where you will see the ‘Import Objects’ dialog of all the tables available in your RM database. At this point is best to ‘Select All’ the tables and press the OK button.

You can now save your Access database.

The good news is that you only have to do all of the above ONCE. You now have access to all your RM tables via the Access database and you will see that you can open them and browse them in the normal way from within Access.

BEWARE – as well as browsing you will be able to overtype the data in the database. Do not do this unless you know what you are doing and understand the consequences of altering the data.

Next: MS Access Engine – Event Query

Discussions & comments from Wikispaces site

Births of children as facts #birth #child #events

Posted on 2010-01-142022-04-27 by Tom Holden

The birth of a child is a major life event for a person so I thought it would be worth adding to the stream of events query for a person. This query is designed to be pasted into the AllFacts4Person5.sql file – it follows the same columnar format – or it can be run on its own. For some unknown reason, it was unnecessary to mess with INDEXED BY and NOT INDEXED clauses to get fast results so this query should run on all flavours of SQLite3.

PersonsBegatChildren.sql Download

File updated with labels for Parent name columns and tested on RM7 and #rm8. TH 2021-03-03

Screen shot from SQLite Developer – note the filter drop-down list box on the Count column.

Database System Catalog #datadefinitions

Posted on 2010-01-052019-01-26 by chief superintendent

RootsMagic 4 Database System Catalog

A singular system table, sqlite_master, resides in a SQLite database. The table essentially defines the RootsMagic 4 database tables, indices, and fields as follows:

type	name	tbl_name	rootpage	sql
table	AddressLinkTable	AddressLinkTable	52	CREATE TABLE AddressLinkTable (LinkID INTEGER PRIMARY KEY, OwnerType INTEGER, AddressID INTEGER, OwnerID INTEGER, AddressNum INTEGER, Details TEXT )
index	idxAddressName	AddressTable	19	CREATE INDEX idxAddressName ON AddressTable (Name)
table	AddressTable	AddressTable	18	CREATE TABLE AddressTable (AddressID INTEGER PRIMARY KEY, AddressType INTEGER, Name TEXT COLLATE RMNOCASE, Street1 TEXT, Street2 TEXT, City TEXT, State TEXT, Zip TEXT, Country TEXT, Phone1 TEXT, Phone2 TEXT, Fax TEXT, Email TEXT, URL TEXT, Latitude INTEGER, Longitude INTEGER, Note BLOB )
index	idxChildID	ChildTable	11	CREATE INDEX idxChildID ON ChildTable (ChildID)
index	idxChildFamilyID	ChildTable	12	CREATE INDEX idxChildFamilyID ON ChildTable (FamilyID)
index	idxChildOrder	ChildTable	13	CREATE INDEX idxChildOrder ON ChildTable (ChildOrder)
table	ChildTable	ChildTable	10	CREATE TABLE ChildTable (RecID INTEGER PRIMARY KEY, ChildID INTEGER, FamilyID INTEGER, RelFather INTEGER, RelMother INTEGER, ChildOrder INTEGER, IsPrivate INTEGER, ProofFather INTEGER, ProofMother INTEGER, Note BLOB )
index	idxCitationSourceID	CitationTable	50	CREATE INDEX idxCitationSourceID ON CitationTable (SourceID)
table	CitationTable	CitationTable	49	CREATE TABLE CitationTable (CitationID INTEGER PRIMARY KEY, OwnerType INTEGER, SourceID INTEGER, OwnerID INTEGER, Quality TEXT, IsPrivate INTEGER, Comments BLOB, ActualText BLOB, RefNumber TEXT, Flags INTEGER, Fields BLOB )
index	idxCitationOwnerID	CitationTable	51	CREATE INDEX idxCitationOwnerID ON CitationTable (OwnerID)
index	idxRecType	ConfigTable	3	CREATE INDEX idxRecType ON ConfigTable (RecType)
table	ConfigTable	ConfigTable	2	CREATE TABLE ConfigTable (RecID INTEGER PRIMARY KEY, RecType INTEGER, Title TEXT, DataRec BLOB )
index	idxOwnerDate	EventTable	17	CREATE INDEX idxOwnerDate ON EventTable (OwnerID,SortDate)
index	idxOwnerEvent	EventTable	15	CREATE INDEX idxOwnerEvent ON EventTable (OwnerID,EventType)
table	EventTable	EventTable	14	CREATE TABLE EventTable (EventID INTEGER PRIMARY KEY, EventType INTEGER, OwnerType INTEGER, OwnerID INTEGER, FamilyID INTEGER, PlaceID INTEGER, SiteID INTEGER, Date TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, Status INTEGER, EditDate FLOAT, Sentence BLOB, Details BLOB, Note BLOB )
index	idxExclusionIndex	ExclusionTable	65	CREATE UNIQUE INDEX idxExclusionIndex ON ExclusionTable (ExclusionType, ID1, ID2)
table	ExclusionTable	ExclusionTable	64	CREATE TABLE ExclusionTable (RecID INTEGER PRIMARY KEY, ExclusionType INTEGER, ID1 INTEGER, ID2 INTEGER )
table	FactTypeTable	FactTypeTable	20	CREATE TABLE FactTypeTable (FactTypeID INTEGER PRIMARY KEY, OwnerType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, GedcomTag TEXT, UseValue INTEGER, UseDate INTEGER, UsePlace INTEGER, Sentence BLOB, Flags INTEGER )
index	idxFactTypeAbbrev	FactTypeTable	22	CREATE INDEX idxFactTypeAbbrev ON FactTypeTable (Abbrev)
index	idxFactTypeName	FactTypeTable	21	CREATE INDEX idxFactTypeName ON FactTypeTable (Name)
index	idxFactTypeGedcomTag	FactTypeTable	24	CREATE INDEX idxFactTypeGedcomTag ON FactTypeTable (GedcomTag)
table	FamilyTable	FamilyTable	5	CREATE TABLE FamilyTable (FamilyID INTEGER PRIMARY KEY, FatherID INTEGER, MotherID INTEGER, ChildID INTEGER, HusbOrder INTEGER, WifeOrder INTEGER, IsPrivate INTEGER, Proof INTEGER, SpouseLabel INTEGER, FatherLabel INTEGER, MotherLabel INTEGER, Note BLOB )
index	idxFamilyMotherID	FamilyTable	8	CREATE INDEX idxFamilyMotherID ON FamilyTable (MotherID)
index	idxFamilyFatherID	FamilyTable	6	CREATE INDEX idxFamilyFatherID ON FamilyTable (FatherID)
table	GroupTable	GroupTable	63	CREATE TABLE GroupTable (RecID INTEGER PRIMARY KEY, GroupID INTEGER, StartID INTEGER, EndID INTEGER )
table	LabelTable	LabelTable	69	CREATE TABLE LabelTable (LabelID INTEGER PRIMARY KEY, LabelType INTEGER, LabelValue INTEGER, LabelName TEXT COLLATE RMNOCASE, Description TEXT )
index	idxLabelType	LabelTable	70	CREATE INDEX idxLabelType ON LabelTable (LabelType)
index	idxLinkRmId	LinkTable	58	CREATE INDEX idxLinkRmId ON LinkTable (rmID)
table	LinkTable	LinkTable	57	CREATE TABLE LinkTable (LinkID INTEGER PRIMARY KEY, extSystem INTEGER, LinkType INTEGER, rmID INTEGER, extID TEXT, Modified INTEGER, extVersion TEXT, extDate FLOAT, Status INTEGER, Note BLOB )
index	idxLinkExtId	LinkTable	59	CREATE INDEX idxLinkExtId ON LinkTable (extID)
index	idxMediaOwnerID	MediaLinkTable	30	CREATE INDEX idxMediaOwnerID ON MediaLinkTable (OwnerID)
table	MediaLinkTable	MediaLinkTable	28	CREATE TABLE MediaLinkTable (LinkID INTEGER PRIMARY KEY, MediaID INTEGER, OwnerType INTEGER, OwnerID INTEGER, IsPrimary INTEGER, Include1 INTEGER, Include2 INTEGER, Include3 INTEGER, Include4 INTEGER, SortOrder INTEGER, RectLeft INTEGER, RectTop INTEGER, RectRight INTEGER, RectBottom INTEGER, Note TEXT, Caption TEXT COLLATE RMNOCASE, RefNumber TEXT COLLATE RMNOCASE, Date TEXT, SortDate INTEGER, Description BLOB )
index	idxMediaCaption	MediaLinkTable	31	CREATE INDEX idxMediaCaption ON MediaLinkTable (Caption)
index	idxMediaURL	MultimediaTable	27	CREATE INDEX idxMediaURL ON MultimediaTable (URL)
table	MultimediaTable	MultimediaTable	25	CREATE TABLE MultimediaTable (MediaID INTEGER PRIMARY KEY, MediaType INTEGER, MediaPath TEXT, MediaFile TEXT COLLATE RMNOCASE, URL TEXT, Thumbnail BLOB )
index	idxMediaFile	MultimediaTable	26	CREATE INDEX idxMediaFile ON MultimediaTable (MediaFile)
index	idxSurname	NameTable	35	CREATE INDEX idxSurname ON NameTable (Surname)
index	idxGiven	NameTable	36	CREATE INDEX idxGiven ON NameTable (Given)
table	NameTable	NameTable	32	CREATE TABLE NameTable (NameID INTEGER PRIMARY KEY, OwnerID INTEGER, Surname TEXT COLLATE RMNOCASE, Given TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE, Nickname TEXT COLLATE RMNOCASE, NameType INTEGER, Date TEXT, SortDate INTEGER, IsPrimary INTEGER, IsPrivate INTEGER, Proof INTEGER, EditDate FLOAT, Sentence BLOB, Note BLOB, BirthYear INTEGER, DeathYear INTEGER )
index	idxNameOwnerID	NameTable	34	CREATE INDEX idxNameOwnerID ON NameTable (OwnerID)
index	idxNamePrimary	NameTable	38	CREATE INDEX idxNamePrimary ON NameTable (IsPrimary)
index	idxSurnameGiven	NameTable	37	CREATE INDEX idxSurnameGiven ON NameTable (Surname, Given, BirthYear, DeathYear)
table	PersonTable	PersonTable	4	CREATE TABLE PersonTable (PersonID INTEGER PRIMARY KEY, UniqueID TEXT, Sex INTEGER, EditDate FLOAT, ParentID INTEGER, SpouseID INTEGER, Color INTEGER, Relate1 INTEGER, Relate2 INTEGER, Flags INTEGER, Living INTEGER, IsPrivate INTEGER, Proof INTEGER, Bookmark INTEGER, Note BLOB )
index	idxPlaceName	PlaceTable	41	CREATE INDEX idxPlaceName ON PlaceTable (Name)
index	idxPlaceAbbrev	PlaceTable	42	CREATE INDEX idxPlaceAbbrev ON PlaceTable (Abbrev)
table	PlaceTable	PlaceTable	39	CREATE TABLE PlaceTable (PlaceID INTEGER PRIMARY KEY, PlaceType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, Normalized TEXT, Latitude INTEGER, Longitude INTEGER, LatLongExact INTEGER, MasterID INTEGER, Note BLOB )
index	idxResearchOwnerID	ResearchTable	44	CREATE INDEX idxResearchOwnerID ON ResearchTable (OwnerID)
table	ResearchTable	ResearchTable	43	CREATE TABLE ResearchTable (TaskID INTEGER PRIMARY KEY, TaskType INTEGER, OwnerID INTEGER, OwnerType INTEGER, RefNumber TEXT, Name TEXT COLLATE RMNOCASE, Status INTEGER, Priority INTEGER, Date1 TEXT, Date2 TEXT, Date3 TEXT, SortDate1 INTEGER, SortDate2 INTEGER, SortDate3 INTEGER, Filename TEXT, Details BLOB )
index	idxResearchName	ResearchTable	45	CREATE INDEX idxResearchName ON ResearchTable (Name)
table	RoleTable	RoleTable	60	CREATE TABLE RoleTable (RoleID INTEGER PRIMARY KEY, RoleName TEXT COLLATE RMNOCASE, EventType INTEGER, RoleType INTEGER, Sentence TEXT )
index	idxRoleEventType	RoleTable	62	CREATE INDEX idxRoleEventType ON RoleTable (EventType)
table	SourceTable	SourceTable	46	CREATE TABLE SourceTable (SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, RefNumber TEXT, ActualText TEXT, Comments TEXT, IsPrivate INTEGER, TemplateID INTEGER, Fields BLOB )
index	idxSourceName	SourceTable	48	CREATE INDEX idxSourceName ON SourceTable (Name)
table	SourceTemplateTable	SourceTemplateTable	66	CREATE TABLE SourceTemplateTable (TemplateID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, Description TEXT, Favorite INTEGER, Category TEXT, Footnote TEXT, ShortFootnote TEXT, Bibliography TEXT, FieldDefs BLOB )
index	idxSourceTemplateName	SourceTemplateTable	67	CREATE INDEX idxSourceTemplateName ON SourceTemplateTable (Name)
table	WitnessTable	WitnessTable	53	CREATE TABLE WitnessTable (WitnessID INTEGER PRIMARY KEY, EventID INTEGER, PersonID INTEGER, WitnessOrder INTEGER, Role INTEGER, Sentence TEXT, Note BLOB, Given TEXT COLLATE RMNOCASE, Surname TEXT COLLATE RMNOCASE, Prefix TEXT COLLATE RMNOCASE, Suffix TEXT COLLATE RMNOCASE )
index	idxWitnessEventID	WitnessTable	55	CREATE INDEX idxWitnessEventID ON WitnessTable (EventID)
index	idxWitnessPersonID	WitnessTable	56	CREATE INDEX idxWitnessPersonID ON WitnessTable (PersonID)

Discussions & comments from Wikispaces site

MarkVS

Pulling Together All the Events for An Individual

MarkVS
06 January 2010 16:12:56

One thing that I wanted to do, and I expect might be of interest to others, is to be able to stream all the events for an individual and then, depending on what reporting I wanted to do, sort and/or group and/or filter the records accordingly. I will document elsewhere how to get into the database via Microsoft Access but, whatever tool you are using, getting hold of all the Events for a person is not as straightforward as it might (should?) be.

From what I have learned to date it appears to be neceesary to run three separate queries and combine the results to get the “complete” datastream. These are the queries I think you need:

1) Firstly the straightforward one. The Events held on the Event Table for individuals need to be pulled off. It is important to select only those with an OWNERTYPE of 0. You can link to the various other tables from this data to pick up the FactType description, name of the person etc etc, as required.

2) Secondly, the family events. These are also held in the Event Table but with an OWNERTYPE of 1. This means that the event being recorded acutally applies to 2 people. So to get this data into a stream of events you need to take the value of the OWNERID field and use this to look up the record in the FAMILY table (ie not the PERSON table as you would if the OWNERTYPE was 0). In the Family table you will see the Husband and Wife ID and these can be used to pick up the names of the two people to whom the event applies. Then in theory generate an Event into the data stream for both of them.

3) Thirdly we need to pick up Shared events. These are recorded in the Witness table. This holds the Person ID to link back to the Name of the person and the Event ID to link back to the Event record (and from there to the fact description etc etc).

It seems to me the Database design could have been a wee bit slicker – but this is the way it appears to me at this time.

I am now intendeing to work on a combined set of queries that will pull all this data together into a single Access query – from which, hopefully, it will be easy to pull off regular reports without having to cope with a lot of multi-table SQL statements.

Regards, MVS

romermb

romermb
06 January 2010 23:08:00

For readability, I made some changes to the headers:

SELECT FactTypeTable.Name COLLATE NOCASE AS Fact, ‘Principal’ AS ‘Role Type’, NameTable1.OwnerID AS RIN, NameTable1.Surname COLLATE NOCASE AS Surname, NameTable1.Suffix COLLATE NOCASE AS Suffix, NameTable1.Prefix COLLATE NOCASE AS Prefix, NameTable1.Given COLLATE NOCASE AS ‘Given Name’, NameTable2.OwnerID AS ‘Spouse RIN’, NameTable2.Surname COLLATE NOCASE AS ‘Spouse Surname’, NameTable2.Suffix COLLATE NOCASE AS ‘Spouse Suffix’, NameTable2.Prefix COLLATE NOCASE AS ‘Spouse Prefix’, NameTable2.Given COLLATE NOCASE AS ‘Spouse Given Name’
FROM EventTable
INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID
INNER JOIN FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID
INNER JOIN NameTable AS NameTable1 ON FamilyTable.FatherID = NameTable1.OwnerID
INNER JOIN NameTable AS NameTable2 ON FamilyTable.MotherID = NameTable2.OwnerID
WHERE EventTable.OwnerType = 1 AND NameTable1.IsPrimary = 1 AND NameTable2.IsPrimary = 1

UNION

SELECT FactTypeTable.Name COLLATE NOCASE, ‘Principal’, NameTable2.OwnerID, NameTable2.Surname COLLATE NOCASE, NameTable2.Suffix COLLATE NOCASE, NameTable2.Prefix COLLATE NOCASE, NameTable2.Given COLLATE NOCASE, NameTable1.OwnerID, NameTable1.Surname COLLATE NOCASE, NameTable1.Suffix COLLATE NOCASE, NameTable1.Prefix COLLATE NOCASE, NameTable1.Given COLLATE NOCASE
FROM EventTable
INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID
INNER JOIN FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID
INNER JOIN NameTable AS NameTable1 ON FamilyTable.FatherID = NameTable1.OwnerID
INNER JOIN NameTable AS NameTable2 ON FamilyTable.MotherID = NameTable2.OwnerID
WHERE EventTable.OwnerType = 1 AND NameTable1.IsPrimary = 1 AND NameTable2.IsPrimary = 1

UNION

SELECT FactTypeTable.Name COLLATE NOCASE, ‘Principal’, NameTable.OwnerID, NameTable.Surname COLLATE NOCASE, NameTable.Suffix COLLATE NOCASE, NameTable.Prefix COLLATE NOCASE, NameTable.Given COLLATE NOCASE, NULL, NULL, NULL, NULL, NULL
FROM EventTable
INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID
INNER JOIN NameTable ON EventTable.OwnerID = NameTable.OwnerID
WHERE EventTable.OwnerType = 0 AND NameTable.IsPrimary = 1

UNION

SELECT ‘Alternate name’, ‘Principal’, OwnerID, Surname COLLATE NOCASE, Suffix COLLATE NOCASE, Prefix COLLATE NOCASE, Given COLLATE NOCASE, NULL, NULL, NULL, NULL, NULL
FROM NameTable
WHERE IsPrimary = 0

UNION

SELECT FactTypeTable.Name COLLATE NOCASE, ‘Shared’, NameTable.OwnerID, NameTable.Surname COLLATE NOCASE, NameTable.Suffix COLLATE NOCASE, NameTable.Prefix COLLATE NOCASE, NameTable.Given COLLATE NOCASE, NULL, NULL, NULL, NULL, NULL
FROM WitnessTable
INNER JOIN EventTable ON WitnessTable.EventID = EventTable.EventID
INNER JOIN NameTable ON WitnessTable.PersonID = NameTable.OwnerID
INNER JOIN FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID
WHERE NameTable.IsPrimary = 1

ve3meo

Use of page-related Discussion

ve3meo
07 January 2010 14:52:53

Let’s try to start discussion topics around the most relevant page. For example, the first discussion topic on this ‘Database System Catalogue’ page is about the problem of designing a query that will pull together all the facts for each individual. I think that should have been started with a new page linked from the page ‘Problem Queries’. That would make it more probable for new members to find by being consistent with the wiki navigational structure (immature as it may be). I’ll try to move the essentials of that discussion over to a new ‘Problem Query’ page for continuation there.

Tom

Gedcom Help #gedcom #datadefinitions

Posted on 2010-01-032019-01-26 by chief superintendent

Gedcom 5.5

Gedcom 5.5 in Windows Help (chm) format

File Types #filetypes #datadefinitions #database

Posted on 2010-01-032019-01-26 by Tom Holden

RootsMagic 4 File Types

Ext	Description	More
rmgc	family tree database	SQLite 3-format file readable with SQLiteManagers
rmgb	family tree database backup	ZIP-format file extractable not only within RM, but also by PKZip, WinRAR, et al.
st	Source Template master file used to create sourcetemplatetable on creation of family tree database	Source Templates
adl	user’s spelling dictionary created by application	Addict 4
adm	regional spelling dictionary selected during program installation.	Addict 4
adt	thesaurus	Addict 3
adu	system and user’s autocorrect spelling dictionaries	Addict 4 – system Addict 4.1 – user
exe	Application
dll	Application extensions
chm	Help files
def	1 XML database file defining dimensions for selected Avery address and shipping labels
elf	log of application exception incidents	EurekaLog

Discussions & comments from Wikispaces site

Open Office Base connection to Rootsmagic data base

Discussions & comments from Wikispaces site

Incredible speedup using one index

Incredible speedup using a temp table

Discussions & comments from Wikispaces site

Use sqlite CAST to convert to text

Discussions & comments from Wikispaces site

Wow!

Discussions & comments from Wikispaces site

Neat trick getting around RMNOCASE

Discussions & comments from Wikispaces site

RMNOCASE Collation Issues?

SQL Server, Etc.

Modify a Shared Events Query

Creating DSN’s, 64 bit Windows 7

Nested Joins and Compatible SQL between Access and SQLite

RootsMagic 4 Database System Catalog

Discussions & comments from Wikispaces site

Pulling Together All the Events for An Individual

Use of page-related Discussion

Gedcom 5.5

RootsMagic 4 File Types