commit 47bfaac9ec809f3732179d32c9d44f48fb425b84 from: Sergey Bronnikov date: Sun Nov 03 10:41:38 2019 UTC switching to gramps scheme commit - 9c5889a96479f0c4965cb9d217911283f871244b commit + 47bfaac9ec809f3732179d32c9d44f48fb425b84 blob - b4946220f9cad5ef517507986e12a71f2e84cf87 blob + de92062b67ffb5727b8442068ea17af69aaa6ae3 --- cmd/gedcom2sql/gedcom2sql.go +++ cmd/gedcom2sql/gedcom2sql.go @@ -1,6 +1,3 @@ -// Gramps SQL Database Scheme -// https://gramps-project.org/wiki/index.php/Gramps_SQL_Database) - package main import ( @@ -24,57 +21,258 @@ func checkErr(err error) { 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); ` ) @@ -99,7 +297,7 @@ func CreateTable(db *sql.DB) { 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() } @@ -133,7 +331,7 @@ func main() { 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)) @@ -161,9 +359,11 @@ func main() { 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 @@ -1,3 +1,4 @@ + DROP TABLE IF EXISTS note; CREATE TABLE note ( handle CHARACTER(25) PRIMARY KEY, gid CHARACTER(25), @@ -7,6 +8,7 @@ CREATE TABLE note ( note_type2 TEXT, change INTEGER, private BOOLEAN); + DROP TABLE IF EXISTS name; CREATE TABLE name ( handle CHARACTER(25) PRIMARY KEY, primary_name BOOLEAN, @@ -22,6 +24,7 @@ CREATE TABLE name ( call TEXT, nick TEXT, famnick TEXT); + DROP TABLE IF EXISTS surname; CREATE TABLE surname ( handle CHARACTER(25), surname TEXT, @@ -32,6 +35,7 @@ CREATE TABLE surname ( 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, @@ -48,6 +52,7 @@ CREATE TABLE date ( text TEXT, sortval INTEGER, newyear INTEGER); + DROP TABLE IF EXISTS person; CREATE TABLE person ( handle CHARACTER(25) PRIMARY KEY, gid CHARACTER(25), @@ -56,6 +61,7 @@ CREATE TABLE person ( birth_ref_handle TEXT, change INTEGER, private BOOLEAN); + DROP TABLE IF EXISTS family; CREATE TABLE family ( handle CHARACTER(25) PRIMARY KEY, gid CHARACTER(25), @@ -65,6 +71,7 @@ CREATE TABLE family ( the_type1 TEXT, change INTEGER, private BOOLEAN); + DROP TABLE IF EXISTS place; CREATE TABLE place ( handle CHARACTER(25) PRIMARY KEY, gid CHARACTER(25), @@ -78,15 +85,18 @@ CREATE TABLE place ( 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), @@ -95,6 +105,7 @@ CREATE TABLE event ( description TEXT, change INTEGER, private BOOLEAN); + DROP TABLE IF EXISTS citation; CREATE TABLE citation ( handle CHARACTER(25) PRIMARY KEY, gid CHARACTER(25), @@ -103,6 +114,7 @@ CREATE TABLE citation ( source_handle CHARACTER(25), change INTEGER, private BOOLEAN); + DROP TABLE IF EXISTS source; CREATE TABLE source ( handle CHARACTER(25) PRIMARY KEY, gid CHARACTER(25), @@ -112,6 +124,7 @@ CREATE TABLE source ( abbrev TEXT, change INTEGER, private BOOLEAN); + DROP TABLE IF EXISTS media; CREATE TABLE media ( handle CHARACTER(25) PRIMARY KEY, gid CHARACTER(25), @@ -121,6 +134,7 @@ CREATE TABLE media ( checksum INTEGER, change INTEGER, private BOOLEAN); + DROP TABLE IF EXISTS repository_ref; CREATE TABLE repository_ref ( handle CHARACTER(25) PRIMARY KEY, ref CHARACTER(25), @@ -128,6 +142,7 @@ CREATE TABLE repository_ref ( 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), @@ -136,6 +151,7 @@ CREATE TABLE repository ( name TEXT, change INTEGER, private BOOLEAN); + DROP TABLE IF EXISTS link; CREATE TABLE link ( from_type CHARACTER(25), from_handle CHARACTER(25), @@ -143,22 +159,26 @@ CREATE TABLE link ( 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), @@ -167,6 +187,7 @@ CREATE TABLE child_ref ( mrel0 INTEGER, mrel1 CHARACTER(25), private BOOLEAN); + DROP TABLE IF EXISTS lds; CREATE TABLE lds ( handle CHARACTER(25) PRIMARY KEY, type INTEGER, @@ -175,6 +196,7 @@ CREATE TABLE lds ( temple TEXT, status INTEGER, private BOOLEAN); + DROP TABLE IF EXISTS media_ref; CREATE TABLE media_ref ( handle CHARACTER(25) PRIMARY KEY, ref CHARACTER(25), @@ -183,9 +205,11 @@ CREATE TABLE media_ref ( 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, @@ -197,12 +221,14 @@ CREATE TABLE location ( 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, @@ -210,12 +236,14 @@ CREATE TABLE url ( 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,