- OFFICE ADDRESS
- 10037 – 20 Silver Fox Ave. New Minas, Nova Scotia B4N 5K1 CANADA
Customer Support
Charting Companion database schema
Published on: 7 September 2023
When Charting Companion imports a GEDCOM file, it creates an auxiliary database as a workfile, in SQLite format.
Charting Companion can execute SQL Queries by clicking “Find ➔ SQL Query”. Requires version 8.2 or later.
Alternatively, the SQLite file is located in “C:\Users\user-id\AppData\Local\Temp\Progeny\CC\“, has the same name as the GEDCOM file, with the extension .DB. The SQLite file can be displayed and searched with a free utility called “DB Browser for SQLite“.
The content and structure of the SQLite file is explained here.
Tables: PERSONS, PLACES, EVENTS, IMAGES, NOTES, CITATIONS, AUTHORS.
Text is stored as UTF-8.
Tables PERSONS and AUTHORS
| Name | Type | Description |
| PIN | Number | Person Id. Primary key. |
| NAME | Text | Name parts separated by delimiters: x01 (binary ‘1’, not character) Name prefix (Mr., Mrs., Dr.) x02 Given name x03 Middle name(s), Nickname x04 Surname prefix (Von, de, la,) x05 Surname x06 Name suffix (Jr., III, Esq.) |
| NAME_SORT | Text | Sort key: Surname – Given name |
| FATHER_PIN | Number | PIN of father (link to PERSONS.PIN) |
| MOTHER_PIN | Number | PIN of mother (link to PERSONS.PIN) |
| SEX | Number | ‘0’ Unknown, ‘1’ Male, ‘2’ Female |
| REF | Text | User-defined Reference no. (GEDCOM REFN tag) |
| PAR_MRINB | Number | Parents’ Marriage Id no. Matches EVENTS.MRINB |
| SURNAME | Text | Extract of surname, to facilitate SQL Query |
Table PLACES, also Sources
| Name | Type | Description | ||||||||||
| PLACE_NO | Number | Place No. Primary key | ||||||||||
| PLACE_NAME | Text | Name of Place or Source | ||||||||||
| PLAS_TYPE | Number |
|
||||||||||
| REPOSITORY | Number | Source Repository. Links to PLACES.PLACE_NO | ||||||||||
| ORIGINATOR | Number | Source Author. Links to AUTHORS.PIN. | ||||||||||
| PUBLISHER | Number | Source Publisher. Links to PLACES.PLACE_NO | ||||||||||
| LINK | Number | Place name overflow. Links to PLACES.PLACE_NO | ||||||||||
| PUBL_DATE | Number | YYYYMMDD | ||||||||||
| CALL_PHONE | Text | Repository: Phone no. Source: Library Call no. |
Table EVENTS
| Name | Type | Description | ||||||||||||
| PIN | Number | PIN of owner. Primary key. Links to PERSONS.PIN. | ||||||||||||
| MRINB | Number | Id no. of parents’ marriage. | ||||||||||||
| EV_TYPE | Number | Event type. See list at end | ||||||||||||
| EV_SEQNO | Number | If DUAL_KEY = ‘0’, Event sequence no., unique within each Person. If DUAL_KEY = ‘1’, PIN of other Person in relationship (Links to PERSONS.PIN). Is an indexed key. |
||||||||||||
| FROM_DATE | Number | YYYYMMDD of event, or start/from date MM and DD can be zero if unknown. If DATE_TYPE = ‘8’, estimated date. |
||||||||||||
| TO_DATE | Number | YYYYMMDD of end/to date, or dual date MM and DD can be zero if unknown. If DATE_TYPE = ‘8’, PLACE_NO of PLACES that contains text of date (Links to PLACES.PLACE_NO). |
||||||||||||
| EVENTFOOTB | Number | Citation no. | ||||||||||||
| DUAL_KEY | Number | ‘0’ event involving one person ‘1’ Event involving two persons |
||||||||||||
| DATE_TYPE | Number |
|
||||||||||||
| AGEB | Number | Child order (Birth sequence no. within a family) | ||||||||||||
| PLACE_NOB | Number | Place of event. Links to PLACES.PLACE_NO. | ||||||||||||
| DESCR_NOB | Number | Description of event. Links to PLACES.PLACE_NO. | ||||||||||||
| SUB_TYPEB | Number | Non-standard event name when DATE_TYPE = 8 Links to PLACES.PLACE_NO. |
Table IMAGES
| Name | Type | Description |
| PIN | Number | PIN of owner. Primary key. Links to PERSONS.PIN. |
| FILENAME | File path | |
| MEDIATYPE | Number | ‘0’ Undefined, ‘1’ Image, ‘2’ URL |
Table NOTES
| Name | Type | Description |
| PIN | Number | PIN of owner. Primary key. Links to PERSONS.PIN. |
| BIO_NOTES | Text | Text of note. |
| EV_NOTE | Text | GEDCOM NOTE tag |
Table CITATIONS
| Name | Type | Description |
| PIN | Number | PIN of owner. Primary key. Links to PERSONS.PIN. |
| EV_TYPE | Number | Event type that this Citation is linked to |
| EV_SEQNO | Number | Sequence no. of event. Matches EVENTS.EV_SEQNO |
| SOURCE_NOB | Number | Source referenced by this Citation Links to PLACES.PLACE_NO. |
| FOOT_SEQ | Number | Sequence no. of Citation within one event |
Event types
| Value | Description | GEDCOM tag |
| 01 | Birth | BIRT |
| 02 | Marriage | MARR |
| 03 | Death | DEAT |
| 04 | Burial | BURI, CEME |
| 05 | Occupation | EMPL, OCCU |
| 06 | Residence | ADDR, RESD, RESI |
| 07 | Christening | CHR, CHRI |
| 08 | Adoption | ADOP |
| 09 | Census | CENS |
| 10 | Miscellaneous | |
| 11 | Next | |
| 12 | Unknown | |
| 13 | Baptism (LDS) | BAPL |
| 14 | Confirmation | CONF |
| 15 | Bar mitzvah | BARM |
| 16 | Adopted | |
| 17 | Extra 4 | |
| 18 | First communion | FCOM |
| 19 | Extra 0 | |
| 20 | Probate | PROB |
| 21 | Endowment (LDS) | ENDL |
| 22 | Passenger | |
| 23 | Extra 1 | |
| 24 | Immigration | IMMI |
| 25 | Graduation | GRAD |
| 26 | Sealed to Parents (LDS) | SLGC, SLGP |
| 27 | Retirement | RETI |
| 28 | Extra 2 | |
| 29 | Joined | |
| 30 | Sealed to Spouse (LDS) | SLGS |
| 31 | Divorce | DIV |
| 32 | Annulment | ANUL |
| 33 | Baptism | BAPM |
| 34 | Bas mitzvah | BASM |
| 35 | Cremation | CREM |
| 36 | Education | EDUC, _DEG |
| 37 | Emigration | EMIG |
| 38 | Engagement | ENGA |
| 39 | Religion | RELI |
| 40 | Will, testament | WILL |
| 41 | Blessing | BLES, BLSL |
| 42 | LDS confirmation | CONL |
| 43 | Marriage banns | MARB |
| 44 | Marriage contract | MARC |
| 45 | Marriage license | MARL |
| 46 | Marriage settlement | MARS |
| 47 | Naturalization | NATU |
| 48 | LDS Ordinance | ORDN |
| 49 | LDS Ordination | ORDI, ORDL |
| 50 | ILLN | |
| 51 | Travel | TRAV |
| 52 | Military award | MILI_AWA |
| 53 | ELEC, _ELEC | |
| 54 | HONR | |
| 55 | Stillbirth | |
| 56 | User-defined event | EVEN, FACT, ILLE, _ATTR, DATE |
| 57 | User-defined marriage event | MEVEN |
| 59 | Fact | |
| 60 | FTM Fact 1 | _FA1 |
| 61 | FTM Fact 2 | _FA2 |
| 62 | FTM Fact 3 | _FA3 |
| 63 | FTM Fact 4 | _FA4 |
| 64 | FTM Fact 5 | _FA5 |
| 65 | FTM Fact 6 | _FA6 |
| 66 | FTM Fact 7 | _FA7 |
| 67 | FTM Fact 8 | _FA8 |
| 68 | FTM Fact 9 | _FA9 |
| 69 | FTM Fact 10 | _FA10 |
| 70 | FTM Fact 11 | _FA11 |
| 71 | FTM Fact 12 | _FA12 |
| 72 | FTM Fact 13 | _FA13 |
| 73 | Marriage fact | |
| 75 | Email address | EMAL, _EMAIL, _EMAL |
| 76 | Medical | _MDCL |
| 77 | Military service | MILI, _MILT |
| 78 | Description | DSCR |
| 79 | Nationality | NATI |
| 80 | Fax phone no. | _FAX |
| 81 | URL | URL |
| 82 | PROP | |
| 83 | _NAMS | |
| 84 | IDNO | |
| 85 | Social Security Number | SSN |
| 87 | Association | ASSO |
| 88 | Caste | CAST |
| 89 | Adult christening | CHRA |
| 90 | Number of children | NCHI |
| 91 | Number of marriages | NMR |
| 92 | Divorce Filing | DIVF |
| 93 | Title | TITL |
| 94 | LDS Initiatory | WAC |
| 95 | Photo | |
| 96 | Relationship | |
| 97 | DNA Test |
Examples
Refer to this SQL Tutorial for info & examples. Enter the commands in the “SQL Query” box, or DB Browser’s “Execute SQL” tab. See also SQLite Documentation (click on SELECT).
Click on “Save” to save as a CSV or Excel XLSX file. Click on “Clipboard” to copy results to Clipboard.
[1] To find everyone born between 1950 and 1952:select PERSONS.NAME, EVENTS.FROM_DATE, PLACES.PLACE_NAME
from ((EVENTS inner join PERSONS on EVENTS.PIN = PERSONS.PIN) inner join PLACES on EVENTS.PLACE_NOB = PLACES.PLACE_NO)
where EVENTS.FROM_DATE >= 19500000 and EVENTS.FROM_DATE <= 19520000 and EVENTS.EV_TYPE == 1
select PERSONS.NAME from PERSONS where PERSONS.FATHER_PIN = 0 and PERSONS.MOTHER_PIN = 0
select PERSONS.NAME, EVENTS.FROM_DATE
from EVENTS
inner join PERSONS on EVENTS.PIN = PERSONS.PIN
where (EVENTS.FROM_DATE % 10000) / 100 = 7 and EVENTS.FROM_DATE % 100 = 4 and EVENTS.EV_TYPE == 1
select distinct PERSONS.NAME, IMAGES.FILENAME from IMAGES inner join PERSONS on IMAGES.PIN=PERSONS.PIN group by IMAGES.FILENAME order by IMAGES.FILENAME, IMAGES.PIN
select PERSONS.NAME, EVENTS.FROM_DATE, PLACES.PLACE_NAME from ((EVENTS inner join PERSONS on EVENTS.PIN = PERSONS.PIN) inner join PLACES on EVENTS.PLACE_NOB = PLACES.PLACE_NO) where EVENTS.PLACE_NOB = 1122
select PLACES.PLACE_NAME from EVENTS inner join PLACES on EVENTS.TO_DATE = PLACES.PLACE_NO where EVENTS.DATE_TYPE = 8
You can create new tables to extract & combine results for more sophisticated reports and analysis with “Insert into“.
[7] Count all menselect count(*) from PERSONS where SEX = 1
select PIN, NAME from PERSONS where SEX < 1 or SEX > 2
First, run these commands once each time the GEDCOM file is re-imported:
create table COUPLES (HUSB int, WIFE int)
Click “Execute”
INSERT INTO COUPLES (HUSB,WIFE)
select EVENTS.PIN, EVENTS.EV_SEQNO
from EVENTS inner join PERSONS on PERSONS.PIN = EVENTS.PIN and PERSONS.SEX =1 and EVENTS.EV_TYPE = 2
where EVENTS.DUAL_KEY = 1
Click “Execute”
Next, run the following command as often as desired:
select
HUSB as Husband_PIN,
(select PERSONS.NAME from PERSONS where PIN = HUSB) as NAME,
WIFE as Wife_PIN,
(select PERSONS.NAME from PERSONS where PIN = WIFE) as NAME,
(HUSB || ',' || WIFE ) as Marriage_PINs
from COUPLES
where Marriage_PINs not in
(select distinct (FATHER_PIN || ',' || MOTHER_PIN) from PERSONS)
The special alias “NAME” causes the name to be formatted correctly.
If there are several people with the same name, press Ctrl + G in Charting Companion, and enter the PIN to go to that person.
[10] All couples where “Smith” married “Jones”select P1.NAME, P2.NAME
from ((EVENTS inner join PERSONS as P1 on EVENTS.PIN = P1.PIN)
inner join PERSONS as P2 on EVENTS.EV_SEQNO = P2.PIN)
where P1.NAME like "%SMITH%" and P2.NAME like "%JONES%"
Admittedly these are not trivial, but the Charting Companion Facebook User Group might include some SQL gurus who can be of assistance.
Share this page
