commit - 9c5889a96479f0c4965cb9d217911283f871244b
commit + 47bfaac9ec809f3732179d32c9d44f48fb425b84
blob - b4946220f9cad5ef517507986e12a71f2e84cf87
blob + de92062b67ffb5727b8442068ea17af69aaa6ae3
--- cmd/gedcom2sql/gedcom2sql.go
+++ cmd/gedcom2sql/gedcom2sql.go
-// Gramps SQL Database Scheme
-// https://gramps-project.org/wiki/index.php/Gramps_SQL_Database)
-
package main
import (
const (
sql_table = `
- DROP TABLE IF EXISTS famchild;
- CREATE TABLE famchild (
- famID varchar(40) NOT NULL DEFAULT '',
- child varchar(40) NOT NULL DEFAULT '',
- PRIMARY KEY (famID, child)
- );
-
+ DROP TABLE IF EXISTS note;
+ CREATE TABLE note (
+ handle CHARACTER(25) PRIMARY KEY,
+ gid CHARACTER(25),
+ text TEXT,
+ format INTEGER,
+ note_type1 INTEGER,
+ note_type2 TEXT,
+ change INTEGER,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS name;
+ CREATE TABLE name (
+ handle CHARACTER(25) PRIMARY KEY,
+ primary_name BOOLEAN,
+ private BOOLEAN,
+ first_name TEXT,
+ suffix TEXT,
+ title TEXT,
+ name_type0 INTEGER,
+ name_type1 TEXT,
+ group_as TEXT,
+ sort_as INTEGER,
+ display_as INTEGER,
+ call TEXT,
+ nick TEXT,
+ famnick TEXT);
+ DROP TABLE IF EXISTS surname;
+ CREATE TABLE surname (
+ handle CHARACTER(25),
+ surname TEXT,
+ prefix TEXT,
+ primary_surname BOOLEAN,
+ origin_type0 INTEGER,
+ origin_type1 TEXT,
+ connector TEXT);
+ CREATE INDEX idx_surname_handle ON
+ surname(handle);
+ DROP TABLE IF EXISTS date;
+ CREATE TABLE date (
+ handle CHARACTER(25) PRIMARY KEY,
+ calendar INTEGER,
+ modifier INTEGER,
+ quality INTEGER,
+ day1 INTEGER,
+ month1 INTEGER,
+ year1 INTEGER,
+ slash1 BOOLEAN,
+ day2 INTEGER,
+ month2 INTEGER,
+ year2 INTEGER,
+ slash2 BOOLEAN,
+ text TEXT,
+ sortval INTEGER,
+ newyear INTEGER);
+ DROP TABLE IF EXISTS person;
+ CREATE TABLE person (
+ handle CHARACTER(25) PRIMARY KEY,
+ gid CHARACTER(25),
+ gender INTEGER,
+ death_ref_handle TEXT,
+ birth_ref_handle TEXT,
+ change INTEGER,
+ private BOOLEAN);
DROP TABLE IF EXISTS family;
CREATE TABLE family (
- famID varchar(40) NOT NULL DEFAULT '',
- husband varchar(40) DEFAULT NULL,
- wife varchar(40) DEFAULT NULL,
- marr_date varchar(255) DEFAULT NULL,
- marr_plac varchar(255) DEFAULT NULL,
- marr_sour varchar(255) DEFAULT NULL,
- marb_date varchar(255) DEFAULT NULL,
- marb_plac varchar(255) DEFAULT NULL,
- marb_sour varchar(255) DEFAULT NULL,
- PRIMARY KEY (famID)
- );
-
- DROP TABLE IF EXISTS person_st;
- CREATE TABLE person_st (
- persID varchar(40) NOT NULL DEFAULT '',
- name varchar(255) DEFAULT NULL,
- vorname varchar(255) DEFAULT NULL,
- marname varchar(255) DEFAULT NULL,
- sex char(1) DEFAULT NULL,
- birt_date varchar(255) DEFAULT NULL,
- birt_plac varchar(255) DEFAULT NULL,
- birt_sour varchar(255) DEFAULT NULL,
- taufe_date varchar(255) DEFAULT NULL,
- taufe_plac varchar(255) DEFAULT NULL,
- taufe_sour varchar(255) DEFAULT NULL,
- deat_date varchar(255) DEFAULT NULL,
- deat_plac varchar(255) DEFAULT NULL,
- deat_sour varchar(255) DEFAULT NULL,
- buri_date varchar(255) DEFAULT NULL,
- buri_plac varchar(255) DEFAULT NULL,
- buri_sour varchar(255) DEFAULT NULL,
- occupation varchar(255) DEFAULT NULL,
- occu_date varchar(255) DEFAULT NULL,
- occu_plac varchar(255) DEFAULT NULL,
- occu_sour varchar(255) DEFAULT NULL,
- religion varchar(80) DEFAULT NULL,
- confi_date varchar(255) DEFAULT NULL,
- confi_plac varchar(255) DEFAULT NULL,
- confi_sour varchar(255) DEFAULT NULL,
- note longtext,
- PRIMARY KEY (persID)
- );
+ handle CHARACTER(25) PRIMARY KEY,
+ gid CHARACTER(25),
+ father_handle CHARACTER(25),
+ mother_handle CHARACTER(25),
+ the_type0 INTEGER,
+ the_type1 TEXT,
+ change INTEGER,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS place;
+ CREATE TABLE place (
+ handle CHARACTER(25) PRIMARY KEY,
+ gid CHARACTER(25),
+ title TEXT,
+ value TEXT,
+ the_type0 INTEGER,
+ the_type1 TEXT,
+ code TEXT,
+ long TEXT,
+ lat TEXT,
+ lang TEXT,
+ change INTEGER,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS place_ref;
+ CREATE TABLE place_ref (
+ handle CHARACTER(25) PRIMARY KEY,
+ from_place_handle CHARACTER(25),
+ to_place_handle CHARACTER(25));
+ DROP TABLE IF EXISTS place_name;
+ CREATE TABLE place_name (
+ handle CHARACTER(25) PRIMARY KEY,
+ from_handle CHARACTER(25),
+ value CHARACTER(25),
+ lang CHARACTER(25));
+ DROP TABLE IF EXISTS event;
+ CREATE TABLE event (
+ handle CHARACTER(25) PRIMARY KEY,
+ gid CHARACTER(25),
+ the_type0 INTEGER,
+ the_type1 TEXT,
+ description TEXT,
+ change INTEGER,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS citation;
+ CREATE TABLE citation (
+ handle CHARACTER(25) PRIMARY KEY,
+ gid CHARACTER(25),
+ confidence INTEGER,
+ page CHARACTER(25),
+ source_handle CHARACTER(25),
+ change INTEGER,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS source;
+ CREATE TABLE source (
+ handle CHARACTER(25) PRIMARY KEY,
+ gid CHARACTER(25),
+ title TEXT,
+ author TEXT,
+ pubinfo TEXT,
+ abbrev TEXT,
+ change INTEGER,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS media;
+ CREATE TABLE media (
+ handle CHARACTER(25) PRIMARY KEY,
+ gid CHARACTER(25),
+ path TEXT,
+ mime TEXT,
+ desc TEXT,
+ checksum INTEGER,
+ change INTEGER,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS repository_ref;
+ CREATE TABLE repository_ref (
+ handle CHARACTER(25) PRIMARY KEY,
+ ref CHARACTER(25),
+ call_number TEXT,
+ source_media_type0 INTEGER,
+ source_media_type1 TEXT,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS repository;
+ CREATE TABLE repository (
+ handle CHARACTER(25) PRIMARY KEY,
+ gid CHARACTER(25),
+ the_type0 INTEGER,
+ the_type1 TEXT,
+ name TEXT,
+ change INTEGER,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS link;
+ CREATE TABLE link (
+ from_type CHARACTER(25),
+ from_handle CHARACTER(25),
+ to_type CHARACTER(25),
+ to_handle CHARACTER(25));
+ CREATE INDEX idx_link_to ON
+ link(from_type, from_handle, to_type);
+ DROP TABLE IF EXISTS markup;
+ CREATE TABLE markup (
+ handle CHARACTER(25) PRIMARY KEY,
+ markup0 INTEGER,
+ markup1 TEXT,
+ value INTEGER,
+ start_stop_list TEXT);
+ DROP TABLE IF EXISTS event_ref;
+ CREATE TABLE event_ref (
+ handle CHARACTER(25) PRIMARY KEY,
+ ref CHARACTER(25),
+ role0 INTEGER,
+ role1 TEXT,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS person_ref;
+ CREATE TABLE person_ref (
+ handle CHARACTER(25) PRIMARY KEY,
+ description TEXT,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS child_ref;
+ CREATE TABLE child_ref (
+ handle CHARACTER(25) PRIMARY KEY,
+ ref CHARACTER(25),
+ frel0 INTEGER,
+ frel1 CHARACTER(25),
+ mrel0 INTEGER,
+ mrel1 CHARACTER(25),
+ private BOOLEAN);
+ DROP TABLE IF EXISTS lds;
+ CREATE TABLE lds (
+ handle CHARACTER(25) PRIMARY KEY,
+ type INTEGER,
+ place CHARACTER(25),
+ famc CHARACTER(25),
+ temple TEXT,
+ status INTEGER,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS media_ref;
+ CREATE TABLE media_ref (
+ handle CHARACTER(25) PRIMARY KEY,
+ ref CHARACTER(25),
+ role0 INTEGER,
+ role1 INTEGER,
+ role2 INTEGER,
+ role3 INTEGER,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS address;
+ CREATE TABLE address (
+ handle CHARACTER(25) PRIMARY KEY,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS location;
+ CREATE TABLE location (
+ handle CHARACTER(25) PRIMARY KEY,
+ street TEXT,
+ locality TEXT,
+ city TEXT,
+ county TEXT,
+ state TEXT,
+ country TEXT,
+ postal TEXT,
+ phone TEXT,
+ parish TEXT);
+ DROP TABLE IF EXISTS attribute;
+ CREATE TABLE attribute (
+ handle CHARACTER(25) PRIMARY KEY,
+ the_type0 INTEGER,
+ the_type1 TEXT,
+ value TEXT,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS url;
+ CREATE TABLE url (
+ handle CHARACTER(25) PRIMARY KEY,
+ path TEXT,
+ desc TXT,
+ type0 INTEGER,
+ type1 TEXT,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS datamap;
+ CREATE TABLE datamap (
+ from_handle CHARACTER(25),
+ the_type0 INTEGER,
+ the_type1 TEXT,
+ value_field TXT,
+ private BOOLEAN);
+ DROP TABLE IF EXISTS tag;
+ CREATE TABLE tag (
+ handle CHARACTER(25) PRIMARY KEY,
+ name TEXT,
+ color TEXT,
+ priority INTEGER,
+ change INTEGER);
`
)
func main() {
flag.Usage = func() {
- fmt.Println("\ngedcom2sql is a tool for conversion of GEDCOM to SQLite.")
+ fmt.Println("\ngedcom2sql is a tool for conversion of GEDCOM file to an SQLite DB.")
fmt.Println("\nUsage:")
flag.PrintDefaults()
}
g, _ := d.Decode()
if *verbose {
- fmt.Println("\nPersons (Xref, Name, Sex):")
+ fmt.Println("\nPersons (XRef, Name, Sex):")
fmt.Println("--------------------------")
}
println("Found persons:", len(g.Individual))
buri_plac = event.Place.Name
}
}
- stmt, err := db.Prepare("INSERT INTO person_st (persID, name, birt_date, birt_plac, deat_date, deat_plac, buri_date, buri_plac, sex) values(?, ?, ?, ?, ?, ?, ?, ?, ?)")
+// handle CHARACTER(25) PRIMARY KEY, gid CHARACTER(25), gender INTEGER, death_ref_handle TEXT, birth_ref_handle TEXT, change INTEGER, private BOOLEAN);
+ stmt, err := db.Prepare("INSERT INTO person (handle, gid, gender, death_ref_handle, birth_ref_handle, change, private) values(?, ?, ?, ?, ?, ?, ?)")
checkErr(err)
- _, err = stmt.Exec(rec.Xref, rec.Name[0].Name, birt_date, birt_plac, deat_date, deat_plac, buri_date, buri_plac, rec.Sex)
+ _, err = stmt.Exec(nil, nil, rec.Sex, nil, nil, false)
+ // rec.Name[0].Name, birt_date, birt_plac, deat_date, deat_plac, buri_date, buri_plac, rec.Sex)
checkErr(err)
}
}
blob - abfd333976b35671b037df6835ce79ae02087198
blob + 7b5e2e5014bd09e36073a97117fb76068f547f5e
--- cmd/gedcom2sql/gramps-db1.sql
+++ cmd/gedcom2sql/gramps-db1.sql
+ DROP TABLE IF EXISTS note;
CREATE TABLE note (
handle CHARACTER(25) PRIMARY KEY,
gid CHARACTER(25),
note_type2 TEXT,
change INTEGER,
private BOOLEAN);
+ DROP TABLE IF EXISTS name;
CREATE TABLE name (
handle CHARACTER(25) PRIMARY KEY,
primary_name BOOLEAN,
call TEXT,
nick TEXT,
famnick TEXT);
+ DROP TABLE IF EXISTS surname;
CREATE TABLE surname (
handle CHARACTER(25),
surname TEXT,
connector TEXT);
CREATE INDEX idx_surname_handle ON
surname(handle);
+ DROP TABLE IF EXISTS date;
CREATE TABLE date (
handle CHARACTER(25) PRIMARY KEY,
calendar INTEGER,
text TEXT,
sortval INTEGER,
newyear INTEGER);
+ DROP TABLE IF EXISTS person;
CREATE TABLE person (
handle CHARACTER(25) PRIMARY KEY,
gid CHARACTER(25),
birth_ref_handle TEXT,
change INTEGER,
private BOOLEAN);
+ DROP TABLE IF EXISTS family;
CREATE TABLE family (
handle CHARACTER(25) PRIMARY KEY,
gid CHARACTER(25),
the_type1 TEXT,
change INTEGER,
private BOOLEAN);
+ DROP TABLE IF EXISTS place;
CREATE TABLE place (
handle CHARACTER(25) PRIMARY KEY,
gid CHARACTER(25),
lang TEXT,
change INTEGER,
private BOOLEAN);
+ DROP TABLE IF EXISTS place_ref;
CREATE TABLE place_ref (
handle CHARACTER(25) PRIMARY KEY,
from_place_handle CHARACTER(25),
to_place_handle CHARACTER(25));
+ DROP TABLE IF EXISTS place_name;
CREATE TABLE place_name (
handle CHARACTER(25) PRIMARY KEY,
from_handle CHARACTER(25),
value CHARACTER(25),
lang CHARACTER(25));
+ DROP TABLE IF EXISTS event;
CREATE TABLE event (
handle CHARACTER(25) PRIMARY KEY,
gid CHARACTER(25),
description TEXT,
change INTEGER,
private BOOLEAN);
+ DROP TABLE IF EXISTS citation;
CREATE TABLE citation (
handle CHARACTER(25) PRIMARY KEY,
gid CHARACTER(25),
source_handle CHARACTER(25),
change INTEGER,
private BOOLEAN);
+ DROP TABLE IF EXISTS source;
CREATE TABLE source (
handle CHARACTER(25) PRIMARY KEY,
gid CHARACTER(25),
abbrev TEXT,
change INTEGER,
private BOOLEAN);
+ DROP TABLE IF EXISTS media;
CREATE TABLE media (
handle CHARACTER(25) PRIMARY KEY,
gid CHARACTER(25),
checksum INTEGER,
change INTEGER,
private BOOLEAN);
+ DROP TABLE IF EXISTS repository_ref;
CREATE TABLE repository_ref (
handle CHARACTER(25) PRIMARY KEY,
ref CHARACTER(25),
source_media_type0 INTEGER,
source_media_type1 TEXT,
private BOOLEAN);
+ DROP TABLE IF EXISTS repository;
CREATE TABLE repository (
handle CHARACTER(25) PRIMARY KEY,
gid CHARACTER(25),
name TEXT,
change INTEGER,
private BOOLEAN);
+ DROP TABLE IF EXISTS link;
CREATE TABLE link (
from_type CHARACTER(25),
from_handle CHARACTER(25),
to_handle CHARACTER(25));
CREATE INDEX idx_link_to ON
link(from_type, from_handle, to_type);
+ DROP TABLE IF EXISTS markup;
CREATE TABLE markup (
handle CHARACTER(25) PRIMARY KEY,
markup0 INTEGER,
markup1 TEXT,
value INTEGER,
start_stop_list TEXT);
+ DROP TABLE IF EXISTS event_ref;
CREATE TABLE event_ref (
handle CHARACTER(25) PRIMARY KEY,
ref CHARACTER(25),
role0 INTEGER,
role1 TEXT,
private BOOLEAN);
+ DROP TABLE IF EXISTS person_ref;
CREATE TABLE person_ref (
handle CHARACTER(25) PRIMARY KEY,
description TEXT,
private BOOLEAN);
+ DROP TABLE IF EXISTS child_ref;
CREATE TABLE child_ref (
handle CHARACTER(25) PRIMARY KEY,
ref CHARACTER(25),
mrel0 INTEGER,
mrel1 CHARACTER(25),
private BOOLEAN);
+ DROP TABLE IF EXISTS lds;
CREATE TABLE lds (
handle CHARACTER(25) PRIMARY KEY,
type INTEGER,
temple TEXT,
status INTEGER,
private BOOLEAN);
+ DROP TABLE IF EXISTS media_ref;
CREATE TABLE media_ref (
handle CHARACTER(25) PRIMARY KEY,
ref CHARACTER(25),
role2 INTEGER,
role3 INTEGER,
private BOOLEAN);
+ DROP TABLE IF EXISTS address;
CREATE TABLE address (
handle CHARACTER(25) PRIMARY KEY,
private BOOLEAN);
+ DROP TABLE IF EXISTS location;
CREATE TABLE location (
handle CHARACTER(25) PRIMARY KEY,
street TEXT,
postal TEXT,
phone TEXT,
parish TEXT);
+ DROP TABLE IF EXISTS attribute;
CREATE TABLE attribute (
handle CHARACTER(25) PRIMARY KEY,
the_type0 INTEGER,
the_type1 TEXT,
value TEXT,
private BOOLEAN);
+ DROP TABLE IF EXISTS url;
CREATE TABLE url (
handle CHARACTER(25) PRIMARY KEY,
path TEXT,
type0 INTEGER,
type1 TEXT,
private BOOLEAN);
+ DROP TABLE IF EXISTS datamap;
CREATE TABLE datamap (
from_handle CHARACTER(25),
the_type0 INTEGER,
the_type1 TEXT,
value_field TXT,
private BOOLEAN);
+ DROP TABLE IF EXISTS tag;
CREATE TABLE tag (
handle CHARACTER(25) PRIMARY KEY,
name TEXT,
