Re: GEDCOM to Wiki, anyone?

This is from postgres, but it was originally implemented in MySQL and 
used the same stable structure. I support multiple gedcom files by 
loading the data into different postgres schemas and use the same 
applications code. When the application is setup the db login selects 
the schema and only that gedcom is visible and served. so below the 
woodbridge.* are teh tables in the woodbridge schema and "woodbridge" 
represents one of my gedcom files.

-Steve

-- Table: woodbridge.child

-- DROP TABLE woodbridge.child;

CREATE TABLE woodbridge.child
(
   fami varchar(10) NOT NULL,
   seq int4 NOT NULL DEFAULT 0,
   indi varchar(10) NOT NULL,
   CONSTRAINT child_pkey PRIMARY KEY (fami, seq)
)
WITHOUT OIDS;


-- Index: woodbridge.indi_idx

-- DROP INDEX woodbridge.indi_idx;

CREATE INDEX indi_idx
   ON woodbridge.child
   USING btree
   (indi);

-- Table: woodbridge.fami

-- DROP TABLE woodbridge.fami;

CREATE TABLE woodbridge.fami
(
   fami varchar(10) NOT NULL,
   husb varchar(10),
   wife varchar(10),
   marr_date varchar(20),
   marr_plac varchar(60),
   div char(1),
   div_date varchar(20),
   div_plac varchar(60),
   sour varchar(10),
   CONSTRAINT fami_pkey PRIMARY KEY (fami)
)
WITHOUT OIDS;


-- Index: woodbridge.husb_idx

-- DROP INDEX woodbridge.husb_idx;

CREATE INDEX husb_idx
   ON woodbridge.fami
   USING btree
   (husb);

-- Index: woodbridge.wife_idx

-- DROP INDEX woodbridge.wife_idx;

CREATE INDEX wife_idx
   ON woodbridge.fami
   USING btree
   (wife);

-- Table: woodbridge.fams

-- DROP TABLE woodbridge.fams;

CREATE TABLE woodbridge.fams
(
   indi varchar(10) NOT NULL,
   seq int4 NOT NULL DEFAULT 0,
   fami varchar(10) NOT NULL,
   CONSTRAINT fams_pkey PRIMARY KEY (indi, seq)
)
WITHOUT OIDS;


-- Index: woodbridge.fami_idx

-- DROP INDEX woodbridge.fami_idx;

CREATE INDEX fami_idx
   ON woodbridge.fams
   USING btree
   (fami);


-- Table: woodbridge.images

-- DROP TABLE woodbridge.images;

CREATE TABLE woodbridge.images
(
   pid varchar(10) NOT NULL DEFAULT ''::character varying,
   "type" int4 NOT NULL DEFAULT 0,
   hgt int4 NOT NULL DEFAULT 0,
   wid int4 NOT NULL DEFAULT 0,
   file varchar(130) NOT NULL DEFAULT ''::character varying,
   CONSTRAINT images_pkey PRIMARY KEY (file)
)
WITHOUT OIDS;


-- Index: woodbridge.pid

-- DROP INDEX woodbridge.pid;

CREATE INDEX pid
   ON woodbridge.images
   USING btree
   (pid);


-- Table: woodbridge.indi

-- DROP TABLE woodbridge.indi;

CREATE TABLE woodbridge.indi
(
   indi varchar(10) NOT NULL,
   lname varchar(30),
   fname varchar(60),
   title varchar(20),
   lname_sndx varchar(4),
   famc varchar(10),
   sex char(1),
   birt_date varchar(20),
   birt_plac varchar(60),
   chr_date varchar(20),
   chr_plac varchar(60),
   deat_date varchar(20),
   deat_plac varchar(60),
   buri_date varchar(20),
   buri_plac varchar(60),
   refn varchar(20),
   note varchar(10),
   sour varchar(10),
   CONSTRAINT indi_pkey PRIMARY KEY (indi)
)
WITHOUT OIDS;



-- Table: woodbridge.indi_photos

-- DROP TABLE woodbridge.indi_photos;

CREATE TABLE woodbridge.indi_photos
(
   indi varchar(10) NOT NULL DEFAULT ''::character varying,
   pid varchar(10) NOT NULL DEFAULT ''::character varying,
   CONSTRAINT indi_photos_pkey PRIMARY KEY (indi, pid)
)
WITHOUT OIDS;


-- Index: woodbridge."key"

-- DROP INDEX woodbridge."key";

CREATE INDEX "key"
   ON woodbridge.indi_photos
   USING btree
   (pid);


-- Table: woodbridge.notes

-- DROP TABLE woodbridge.notes;

CREATE TABLE woodbridge.notes
(
   note varchar(10) NOT NULL,
   seq int4 NOT NULL DEFAULT 0,
   text varchar(80),
   CONSTRAINT notes_pkey PRIMARY KEY (note, seq)
)
WITHOUT OIDS;




-- Table: woodbridge.pgroups

-- DROP TABLE woodbridge.pgroups;

CREATE TABLE woodbridge.pgroups
(
   gid varchar(10) NOT NULL DEFAULT ''::character varying,
   gdesc varchar(200),
   CONSTRAINT pgroups_pkey PRIMARY KEY (gid)
)
WITHOUT OIDS;



-- Table: woodbridge.photos

-- DROP TABLE woodbridge.photos;

CREATE TABLE woodbridge.photos
(
   pid varchar(10) NOT NULL DEFAULT ''::character varying,
   date date,
   tdate varchar(20),
   "type" int4,
   pdesc text,
   pperm int4 NOT NULL DEFAULT 0,
   gid varchar(10),
   CONSTRAINT photos_pkey PRIMARY KEY (pid)
)
WITHOUT OIDS;









Hugh S. Myers wrote:
> What are you using as a schema?
> 
> --hsm
> 
>> -----Original Message-----
>> From: Stephen Woodbridge [mailto:[email protected]] 
>> Sent: Wednesday, February 14, 2007 1:09 PM
>> To: Jim Seymour
>> Cc: Mike Hamilton; [email protected]
>> Subject: Re: GEDCOM to Wiki, anyone?
>>
>>
>> As an alternate to this approach,
>>
>> I maintain my genealogy in FalimyTree maker and dump a GEDCOM 
>> file that 
>> gets loaded into a database. This is then used to serve 
>> genealogy pages. 
>> The database is a drop and reload when I have new data. and I 
>> maintain 
>> the INDI ids to preserve continuity of bookmarks.
>>
>> http://swoodbridge.com/family/Woodbridge/
>>
>> The application hides people that are living unless you have a family 
>> login. There is some risk that someone could hack it, but 
>> then they can 
>> dumpster dive also.
>>
>> -Steve
>>
>> Jim Seymour wrote:
>>> Mike Hamilton wrote:
>>>> I did some preliminary work on exactly this quite some 
>> years back. I
>>>> think I can claim to know the Paul's (excellent) Gedcom 
>> module pretty
>>>> well, having used it to generate custom charts, etc.
>>> Well, then...  I may start hitting you up for some advice...  :-)
>>>
>>>> But there's a huge problem to which I could find no answer; what
>>>> happens when somebody makes a change? These should update 
>> the Gedcom 
>>>> file, which would often have a flow-on effect to the rest 
>> of the Wiki, 
>>>> which would need regenerating.
>>> In my case, this is not a problem.  The GEDCOM file is merely a 
>>> one-time transport mechanism.  Once the pages are up, my Wiki will 
>>> become the official repository.
>>>
>>> In other words, this conversion is a one-way trip.
>>>
>>>> I used MediaWiki, but I see that DokuWiki "makes sure the datafiles
>>>> remain readable outside the Wiki and eases the creation of 
>> structured
>>>> texts", so it looks a much better choice.
>>> Yes, this is the main reason I chose DokuWiki.  The pages 
>> are stored 
>>> as plain text files.  When I first got the Wiki running, I 
>> generated 
>>> most of the initial pages by converting them from HTML 
>> pages (using a 
>>> quick one-shot perl script).
>>>
>>>> Sorry I couldn't give Jim a more positive and useful response
>>> Not a problem.  I'll be spending some time on this project this 
>>> weekend. When I'm done, I'll try to include a page on my wiki about 
>>> the conversion process (in case others might find it useful in the 
>>> future).
>>>
>