Storing genealogy data with MySQL or other databases
GenoPro Home  |  Get Started With My Genealogy Tree  |  Buy  |  Login  |  Privacy  |  Search  |  Site Map
 
GenoPro Support Forum
Home        Members    Who's On
Welcome Guest ( Login | Register )
        


123»»»

Storing genealogy data with MySQL or other databases Expand / Collapse
Author
Message
Post #5763 Posted 7/26/2005 10:43:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: Forum Members
Last Login: 8/17/2005 10:45:46 PM
Posts: 3, Visits: 0
Hello. I was unable to find any discussion of this in these forums, so hopefully I'm not asking a redundant question here. My question is in two parts. The first is; can what I describe below be done using the current version of GenoPro, and the second part is; if not, can I request it as a feature to be added.

I have been looking at various web portals to allow multiple users to edit information in a single database, packages like phpGedView and others. For minor changes these user interfaces are not too bad, but for serious work I definitly prefer the GenoPro GUI. I've been using the free version for about 5 minutes and I'm hooked. Can I set it up so that instead of using a .gno file, the program starts by doing a database query and saves any changes back to the database?

Thank you for your attention, and please let me know if I did not explain my request in great enough detail.

John
Post #5764 Posted 8/18/2005 6:48:13 PM


Forum Master

Forum MasterForum MasterForum MasterForum MasterForum MasterForum MasterForum MasterForum Master This user is an important contributor to the GenoPro community This user is an important contributor to the translation of GenoPro 

Group: Customers
Last Login: 2 days ago @ 4:44:18 PM
GenoPro Version: 2.0.1.6
Posts: 552, Visits: 7,390
Can I set it up so that instead of using a .gno file, the program starts by doing a database query and saves any changes back to the database?


No you can't, as far as I know. :?
GenoPro only creates and work with its gno file. Moreover - when you put the .gno file in the internet you can only view it and not edit it. What you can do is update it on your computer and upload it again.

For your request - Dan should answer if this can be done or not.
Post #5765 Posted 8/18/2005 6:48:13 PM


Legendary Master

Legendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary Master This user is an important contributor to the translation of GenoPro 

Group: Administrators
Last Login: Today @ 10:41:09 AM
GenoPro Version: 2.5.0.2
Posts: 3,638, Visits: 15,175
GenoPro 2.0 was designed to be capable to use an SQL database instead of its .gno and/or file format. Supporting SQL to store data is not that difficult, but requires a bit of work.
First, the SQL tables must be created. If you take a look at the Tag Definitions (Menu -> Tools -> Tag Definitions), GenoPro can generate the SQL statements to create the tables automatically. I did this for a corporate customer during my last consulting job in 1998.
The next thing is to offer a way to connect to the SQL database, typically via a connection string. This may be a registry key to remember the SQL server, username and password, however it may be a small ini/xml file containing the connection string. I have to keep in mind the SQL feature will be used in the corporate world where hundreds of machine may use the same SQL server, and perhaps different databases on the same server. I want a solution "easy to deploy".
Another essential feature is to have a built-in mapping mechanism to convert a column name to another. The idea of an SQL database is to have integration with third party tools. A third party tool may use a different column name than the permanent tag name GenoPro uses. A solution for this would be to have an XML file containing the mapping of a GenoPro tag to an SQL column.
<TagMapping>
<Tags id="Individual">
<TagData name="name.first" sql_column="FirstName" />
<TagData name="name.last" sql_column="LastName" />
</Tags>
</TagMapping>
As you can see, this is not a friday-afternoon feature. The most important thing is to have a real test case. In other words, I need a customer who will be willing to test this feature and give feedback for usability and reliability. The key for success is to have a solution easy to use, so the customer considering an SQL database to store his/her genealogy tree can do it with a few minutes of work.

I am aware of tools using SQL databases such as phpGedView and The Next Generation of Genealogy Sitebuilding© ("TNG"). These are nice tools complement to GenoPro. Once the SQL interface is working, I plan to contact the authors in addition to provide an easy mechanism for a GenoPro user to benefit from those tools.


Post #5766 Posted 8/18/2005 6:48:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: Forum Members
Last Login: 8/17/2005 10:45:46 PM
Posts: 3, Visits: 0
...The most important thing is to have a real test case. In other words, I need a customer who will be willing to test this feature and give feedback for usability and reliability. The key for success is to have a solution easy to use, so the customer considering an SQL database to store his/her genealogy tree can do it with a few minutes of work....

If by real test case you mean a GenoPro Gold user with a desire to see this working and a few systems to test on, then you have yourself a test subject. If you want a large corporate network with hundreds of machines, I don't think I'll be able to help.

John

PS. I'm not a paid user yet because I considered the SQL integration a must have, if you respond that I'm an OK test subject then I'll upgrade tonight. My current setup is a MySQL server running on Linux connected to my private network. I have experience programming in C and C++ but very limited experience with MS Windows internals, please let me know what I can do to help.
Post #5767 Posted 8/18/2005 6:48:13 PM


Legendary Master

Legendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary Master This user is an important contributor to the translation of GenoPro 

Group: Administrators
Last Login: Today @ 10:41:09 AM
GenoPro Version: 2.5.0.2
Posts: 3,638, Visits: 15,175
I mean a customer willing to try and use a SQL (such as MySQL) to build a genealogy tree. The only way to find out if there are problems is to use the feature. I prefer a small customer to do the testing because the communication and flexibility is usually better and faster than a large enterprise. On the other hand, the SQL feature have to scale for a larger customer.

Post #5768 Posted 8/18/2005 6:48:13 PM


Forum Master

Forum MasterForum MasterForum MasterForum MasterForum MasterForum MasterForum MasterForum Master This user is an important contributor to the GenoPro community This user is an important contributor to the translation of GenoPro 

Group: Customers
Last Login: 2 days ago @ 4:44:18 PM
GenoPro Version: 2.0.1.6
Posts: 552, Visits: 7,390
I'm willing to be a test subject, too.
Can you specify in detail what you need us to do?
Do we create our own SQL based tree or you will give us the tool and we use it to create the tree?
Post #5769 Posted 8/18/2005 6:48:13 PM


Legendary Master

Legendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary Master This user is an important contributor to the translation of GenoPro 

Group: Administrators
Last Login: Today @ 10:41:09 AM
GenoPro Version: 2.5.0.2
Posts: 3,638, Visits: 15,175
Great. I have to finish the Beta schedule at http://www.genopro.com/beta/ and then I will post a message for those to get started. First, I will write a tool to generate the SQL statements to create the tables. There is no need to do all this work while the application can generate the SQL code. Then I will have to figure out a method to let the user select which family tree to fetch/update from the SQL database.

Post #5770 Posted 8/18/2005 6:48:13 PM


Famous Writer

Famous WriterFamous WriterFamous WriterFamous WriterFamous WriterFamous WriterFamous WriterFamous Writer This user is a contributor to FamilyTrees.GenoPro.com This user is an important contributor to the GenoPro community 

Group: Customers
Last Login: 11/12/2009 5:34:49 PM
GenoPro Version: 2.0.1.4
Posts: 440, Visits: 3,437
Hi Dan,
I would be interested in trying SQL but have no idea how it works.
I managed to get my head round PHP with a struggle to put a message forum on my family tree web site at http://www.beath.net but no one has used it yet so it seemed a waste of time for the moment.
I have been using Legacy untill now but am thinking of only using GenoPro once the Report Generator is ready as the Beta version has all the fields I need.
I am not sure of the advantages of SQL and even though I installed MYSQL on my computer I can't see how to set it up and create a database.
I am always willing to learn something new even at 60 yrs!
Iain
Post #5771 Posted 8/18/2005 6:48:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: Forum Members
Last Login: 8/17/2005 10:47:15 PM
Posts: 6, Visits: 0
I had a quick look at the tag definitions. Was it your intention to use the tag definitions as the columns in the tables or did you intend changing the structure so that it fitted in to a relational database?
Post #5772 Posted 8/18/2005 6:48:13 PM


Legendary Master

Legendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary Master This user is an important contributor to the translation of GenoPro 

Group: Administrators
Last Login: Today @ 10:41:09 AM
GenoPro Version: 2.5.0.2
Posts: 3,638, Visits: 15,175
I am not sure yet. I am thinking of having all the user-defined tags stored as text in the XML format. Perhaps, a column could be reserved to store the data of all user-defined tags. I don't want to drop the user-defined tags, however I dont' want to create additional tables on the SQL database.

Post #5773 Posted 8/18/2005 6:48:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: Forum Members
Last Login: 8/17/2005 10:47:15 PM
Posts: 6, Visits: 0
I have always tried to avoid having multiple data items in one column as it is awkward and slow to do queries on the data items. I would go for having a separate tables.
Post #5775 Posted 8/18/2005 6:48:13 PM


Legendary Master

Legendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary Master This user is an important contributor to the translation of GenoPro 

Group: Administrators
Last Login: Today @ 10:41:09 AM
GenoPro Version: 2.5.0.2
Posts: 3,638, Visits: 15,175
One way to update data from GenoPro to Outlook would be to create a "report" that manipulates Outlook via automation. For instance, the new report generator is capable of running the following code:
Report.WriteBr "All the contacts in GenoPro"
For each c in Contacts
strHomePage = c.homepage
If (strHomePage <> "") Then
Report.WriteTextBr strHomePage
End If
Next

Report.WriteBr "<br />All the contacts in Outlook"
Set oAppOutlook = CreateObject("Outlook.Application")
Set oNamespace = oAppOutlook.GetNamespace("MAPI")
Const olFolderContacts = 10
Set oFolderContact = oNamespace.GetDefaultFolder(olFolderContacts)
For Each oContact In oFolderContact.Items
Report.WriteFormattedBr "Contact Name: {&t}", oContact
Next


This code is actually working. The first part, displays the homepages available in the contacts from GenoPro (you can view all contacts in the Table Layout). The second part displays an unsorted list of all the contact names from Outlook.

Ideally, I would like to use:
Report.WriteFormattedBr "Contact Name: {&t} Email={&t}", oContact, oContact.Email1Address

but it does not work. I get the following error:

Error at line 15: Object doesn't support this property or method: 'oContact.Email1Address'
Microsoft VBScript runtime error 800A01B6

I don't know why I am getting this error. According to the documentation, it should work. Maybe it is because I am using Outlook 2000.

The architecture of GenoPro is capable of running macros in VBScript or JScript. At the moment, the macros are not enabled to avoid accidental modifications while generating a report. Eventually, macros will be enabled in GenoPro. In the meantime, you are welcome to read the technical documentation at http://www.genopro.com/newreportgenerator/ to get an overview of this new report generator. I am currently documenting the features of the report generator, so you may find broken links.


Post #5777 Posted 8/18/2005 6:48:14 PM


Legendary Master

Legendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary Master This user is an important contributor to the translation of GenoPro 

Group: Administrators
Last Login: Today @ 10:41:09 AM
GenoPro Version: 2.5.0.2
Posts: 3,638, Visits: 15,175
Your explanation makes a lot of sense. I am not sure how end users will use the new report generator, however I wanted to show how third party components (such as Outlook) can be used by GenoPro.

Post #5778 Posted 8/18/2005 6:48:14 PM


Grand Master

Grand MasterGrand MasterGrand MasterGrand MasterGrand MasterGrand MasterGrand MasterGrand Master This user is a contributor to FamilyTrees.GenoPro.com This user is an important contributor to the GenoPro community 

Group: Customers
Last Login: Today @ 8:46:16 AM
GenoPro Version: 2.0.1.6
Posts: 1,347, Visits: 6,680
Ideally, I would like to use:
Report.WriteFormattedBr "Contact Name: {&t} Email={&t}", oContact, oContact.Email1Address

but it does not work. I get the following error:

Error at line 15: Object doesn't support this property or method: 'oContact.Email1Address'
Microsoft VBScript runtime error 800A01B6

I don't know why I am getting this error. According to the documentation, it should work. Maybe it is because I am using Outlook 2000.


I came across this site Outlook Redemption which purports to provide a solution to this problem 8)



'lego audio video erro ergo disco' or "I read, I listen, I watch, I make mistakes, therefore I learn"
Post #5779 Posted 8/18/2005 6:48:14 PM


Legendary Master

Legendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary Master This user is an important contributor to the translation of GenoPro 

Group: Administrators
Last Login: Today @ 10:41:09 AM
GenoPro Version: 2.5.0.2
Posts: 3,638, Visits: 15,175
Thanks a lot Ron :D

Post #7851 Posted 8/24/2005 8:15:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: Forum Members
Last Login: 10/23/2006 11:33:29 AM
GenoPro Version: 1.99
Posts: 4, Visits: 9
For what it's worth, I don't find GenoPro to be compatible with phpGedView. It seems the formats for storing IDs and such are different. When importing into phpGedView I was able to suppress the errors from unmatched information - educations didn't seem to transfer, etc. It wasn't happy with the different ID format, but everything seemed to process properly.

I made some changes to the GEDCOM in phpGedView today while I was at work, where I don't have access to GenoPro. When I went to download the GEDCOM and import it into GenoPro, it crashed the program.

So I'm not sure what's wrong, but the two don't seem to be quite friendly just yet. That said, I think having GenoPro and phpGedView work together would be the ultimate genealogy power app!
Post #7852 Posted 8/24/2005 8:22:03 PM


Legendary Master

Legendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary Master This user is an important contributor to the translation of GenoPro 

Group: Administrators
Last Login: Today @ 10:41:09 AM
GenoPro Version: 2.5.0.2
Posts: 3,638, Visits: 15,175
I am glad you are brigning this issue... I was not aware of it.  Can you write the exact error codes and/or what you did to get around the problems.  I am sure I can do something to resolve this problem.

When I went to download the GEDCOM and import it into GenoPro, it crashed the program.
You are hitting the nail on the head.  Can you send me a sample Gedcom file cause the crash.  If I can get the crash on my machine, I will fix the bug.


Post #7854 Posted 8/24/2005 9:00:04 PM


Legendary Master

Legendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary Master This user is an important contributor to the translation of GenoPro 

Group: Administrators
Last Login: Today @ 10:41:09 AM
GenoPro Version: 2.5.0.2
Posts: 3,638, Visits: 15,175
Thanks for the file!  I am able to get a crash and investigating the issue...

Post #7855 Posted 8/24/2005 9:22:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: Forum Members
Last Login: 10/23/2006 11:33:29 AM
GenoPro Version: 1.99
Posts: 4, Visits: 9
Never thought I'd be so glad to hear about a crash! If you need anything additional from me, you now have my e-mail address. (And my family history! )
Post #7856 Posted 8/24/2005 9:42:37 PM


Legendary Master

Legendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary MasterLegendary Master This user is an important contributor to the translation of GenoPro 

Group: Administrators
Last Login: Today @ 10:41:09 AM
GenoPro Version: 2.5.0.2
Posts: 3,638, Visits: 15,175
Yes, I am happy about this crash.  You have found a "code path" that had never been executed before.  I had a breakpoint to the debugger and a special comment on that specific line of code. I knew this code had never been tested before, but I did not knew why.  Sometimes I review my code and I wonder "is this code useful?" or "under what condition this code is being executed?".  Tonight I got an answer.

What is happening is your file is somewhat "corrupted".  There is an illegal sequence of CRLF in the file, causing GenoPro to miss 0 GLOBAL root tag and therefore interpret the TAGS keyword as a root keyword.  Since I wanted GenoPro to be robust, I wrote some special code for this. Unfortunately this code had never been tested and crashed on you. I am taking this opportunity to kill two birds with one stone, by 1) fixing the crash in case of another misplaced TAGS keyword, and 2) by writing additional code to detect and correct any illegal sequence of CRLF.

0 @source00001@ SOUR 1 TITLE FamilySearch.org 1 PUBL 1910 Census 1 CHAN 2 DATE 24 Aug 2005 3 TIME 9:31:12 0 GLOBAL
1 NAME
2 FULL
3 FORMAT %T %F (%N) %M %L (%L2) %S
2 DISPLAY
3 FORMAT %F %M %L (%L2)
3 LINES 3
1 FONT Arial
1 TAGS
2 ID Individual
2 TAGDATA
3 NAME SSN
3 DISPLAYNAME SSN
3 DESCRIPTION Social Security Number
2 DIALOGLAYOUT
3 NAME Custom Tags
3 DESCRIPTION A custom tag is a generic placeholder to record additional information
4 CONC .
4 CONT You can customize those tags by clicking on the Customize button.
3 TAGS SSN

What to do:

In the meantime, use Notepad and open your .ged file.  Locate the GLOBAL keyword (use the F3 key).  You will see a long line as below

0 @source00001@ SOUR 1 TITLE FamilySearch.org 1 PUBL 1910 Census 1 CHAN 2 DATE 24 Aug 2005 3 TIME 9:31:12 0 GLOBAL
 
 and replace it by the following (split the long lines into smaller, valid Gedcom lines)

0 @source00001@ SOUR
1 TITLE FamilySearch.org
1 PUBL 1910 Census
1 CHAN
2 DATE 24 Aug 2005
3 TIME 9:31:12
0 GLOBAL



« Prev Topic | Next Topic »

123»»»

Reading This Topic Expand / Collapse
Active Users: 0 (0 guests, 0 members, 0 anonymous members)
No members currently viewing this topic.
Forum Moderators: GenoProSupport, JcMorin, Ron

Permissions Expand / Collapse

All times are GMT -5:00, Time now is 11:38 AM

Copyright 1998-2009 GenoPro. All rights reserved. GenoPro and the GenoPro logo are trademarks.