From: john <jo...@kl...> - 2012-07-31 02:52:05
|
I am trying to determine what entities might be involved in a Gramps Database, I am interested because I believe that Gramps should be migrated to a SQL DB be that MySQL, Postgres or SQLite. What modelling tools should we use, in order to make exchange of information easier? I believe that the following are the fundamental entities that are involved: Person, Event, Relationship, Location, Source. Are these the fundamentals? Are there others that should be included? What should each of these entities contain? John A -- IDIOT, n. A member of a large and powerful tribe whose influence in human affairs has always been dominant and controlling. |
From: john <jo...@kl...> - 2012-07-31 12:12:12
|
I started this thread because I do not think that BSDDB is necessarily the best database engine today or the best for implementing large, complex data stores. Furthermore as I looked at the current database I felt that the existing DB looks overly complex. I went looking for Gramps design documentation and could not find any, APIs are implementation not design. rest of comments inline. "He who opens a school door, closes a prison" Victor Hugo On 30/07/2012 11:30 PM, Doug Blank wrote: > John, > > Welcome to Gramps! I presume that you are new to Gramps, as there are > already tools to move Gramps data back and forth between SQL > databases. Fairly new. > The most straightforward is the Sqlite import and export addons. You > can get these via the auto updater build into Gramps 3.3 and greater. > See menu -> Preferences -> general -> Check for updates, etc. > > The Sqlite exporter is 100% Gramps complete, exporting all of the > genealogical data (doesn't export other items, like bookmarks, etc.) > It has it all. > > You can also export via the Django exporter (and also import as well). > This allows migration to many SQL-based systems. However, this design > is based on an ORM and is automated. It is not easy to use without the > ORM. OK, so why have we not migrated Gramps to an SQL database. > Finally, we are completing web-based version of Gramps. It uses > relational data for the browsing and querying, but it uses the Gramps > BSDDB internal hierarchical structures for interfacing with Gramps > code. Because we wanted to utilize all of the existing Gramps code, it > was found too slow to do this with a relational data model. But the > web-based version uses both, and can switch back and forth when > convenient. An online version is of little interest to me unless I can maintain *complete* control of my data. > > Please feel free to ask additional questions, and read the extensive > info at the wiki [1]. > > -Doug > > [1] - http://www.gramps-project.org/wiki/index.php?title=Main_page > > On Mon, Jul 30, 2012 at 10:55 PM, john <jo...@kl...> wrote: >> I am trying to determine what entities might be involved in a Gramps >> Database, I am interested because I believe that Gramps should be >> migrated to a SQL DB be that MySQL, Postgres or SQLite. >> >> What modelling tools should we use, in order to make exchange of >> information easier? >> >> I believe that the following are the fundamental entities that are >> involved: Person, Event, Relationship, Location, Source. >> Are these the fundamentals? Are there others that should be included? >> What should each of these entities contain? >> >> John A >> >> >> ------------------------------------------------------------------------------ >> Live Security Virtual Conference >> Exclusive live event will cover all the ways today's security and >> threat landscape has changed and how IT managers can respond. Discussions >> will include endpoint security, mobile security and the latest in malware >> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ >> _______________________________________________ >> Gramps-devel mailing list >> Gra...@li... >> https://lists.sourceforge.net/lists/listinfo/gramps-devel |
From: Benny M. <ben...@gm...> - 2012-07-31 12:50:03
|
2012/7/31 john <jo...@kl...> > I started this thread because I do not think that BSDDB is necessarily > the best database engine today or the best for implementing large, complex > data stores. > Based on what evidence? Over the years since the start of Gramps, people have kept on claiming these things, and time and time again, when somebody tried (yes, also me), the SQL implementation was not good enough. Last attempt by Doug. We are not idiots. Several of us do have formal IT studies. You can look at our BSDDB as NOSQL, so one could claim it is actually the more modern way for complex structures :-) So good luck in trying again. Nobody will stop you, but just claiming things will not make us move away from good old BSDDB. At the moment, the most likely would be we support also a 1-1 move to SQL with our BSDDB datamodel, meaning it is useless as a 'real' SQL layer. BSDDB is a well developed Oracle product, and a lower level datastore than SQL engines. Yes, that means more complex for the application (like we create our own index table as BSSDB can't do some for us), but it also seems to mean more powerfull in our experience. The BSDDB layer of Gramps is several years old, well programmed, and needs very little maintance at the moment. The online version doesn't have to run on a server, you can run it locally. It is open source just as the rest of Gramps Benny > Furthermore as I looked at the current database I felt that the existing > DB looks overly complex. I went looking for Gramps design documentation and > could not find any, APIs are implementation not design. > rest of comments inline. > > "He who opens a school door, closes a prison" Victor Hugo > > On 30/07/2012 11:30 PM, Doug Blank wrote: > > John, > > Welcome to Gramps! I presume that you are new to Gramps, as there are > already tools to move Gramps data back and forth between SQL > databases. > > Fairly new. > > The most straightforward is the Sqlite import and export addons. You > can get these via the auto updater build into Gramps 3.3 and greater. > See menu -> Preferences -> general -> Check for updates, etc. > > The Sqlite exporter is 100% Gramps complete, exporting all of the > genealogical data (doesn't export other items, like bookmarks, etc.) > It has it all. > > You can also export via the Django exporter (and also import as well). > This allows migration to many SQL-based systems. However, this design > is based on an ORM and is automated. It is not easy to use without the > ORM. > > OK, so why have we not migrated Gramps to an SQL database. > > Finally, we are completing web-based version of Gramps. It uses > relational data for the browsing and querying, but it uses the Gramps > BSDDB internal hierarchical structures for interfacing with Gramps > code. Because we wanted to utilize all of the existing Gramps code, it > was found too slow to do this with a relational data model. But the > web-based version uses both, and can switch back and forth when > convenient. > > An online version is of little interest to me unless I can maintain * > complete* control of my data. > > Please feel free to ask additional questions, and read the extensive > info at the wiki [1]. > > -Doug > > [1] - http://www.gramps-project.org/wiki/index.php?title=Main_page > > On Mon, Jul 30, 2012 at 10:55 PM, john <jo...@kl...> <jo...@kl...> wrote: > > I am trying to determine what entities might be involved in a Gramps > Database, I am interested because I believe that Gramps should be > migrated to a SQL DB be that MySQL, Postgres or SQLite. > > What modelling tools should we use, in order to make exchange of > information easier? > > I believe that the following are the fundamental entities that are > involved: Person, Event, Relationship, Location, Source. > Are these the fundamentals? Are there others that should be included? > What should each of these entities contain? > > John A > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > > _______________________________________________ > Gramps-devel mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/gramps-devel > > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Gramps-devel mailing list > Gra...@li... > https://lists.sourceforge.net/lists/listinfo/gramps-devel > > |
From: Brian M. <br...@gr...> - 2012-07-31 12:54:23
|
Hi John, Welcome to Gramps. Every couple of years someone comes along and tells us that we screwed up by not using an SQL backend for the main application. We are kind of used to it. Rather than dive in and rehash all the previous conversation, I'll provide you with a list of links for your enjoyment (this is in no way intended to tell you to "go away", rather just to get you up to speed): http://article.gmane.org/gmane.comp.genealogy.gramps.devel/3464/match=sql http://article.gmane.org/gmane.comp.genealogy.gramps.devel/10142/match=sql http://www.gramps-project.org/wiki/index.php?title=Database_Formats http://www.gramps-project.org/wiki/index.php?title=Relational_database_comparison http://www.gramps-project.org/wiki/index.php?title=GEPS_010:_SQL_Backend http://www.gramps-project.org/wiki/index.php?title=Database_abstraction_layers_comparison http://www.gramps-project.org/wiki/index.php?title=GRAMPS_SQL_Database >I started this thread because I do not think that BSDDB is necessarily the best database engine today or the best for implementing large, complex data stores. Furthermore as I looked at the current database I felt that the existing DB looks overly complex. I went looking for >Gramps design documentation and could not find any, APIs are implementation not design. >rest of comments inline. I know someone has produced a model in the past. I can't find it right now. The reason is because no one uses it. The data structure in the DB matches the API: http://www.gramps-project.org/wiki/index.php?title=Using_database_API >>The most straightforward is the Sqlite import and export addons. You >>can get these via the auto updater build into Gramps 3.3 and greater. >>See menu -> Preferences -> general -> Check for updates, etc. The Sqlite exporter is 100% Gramps complete, exporting all of the >>genealogical data (doesn't export other items, like bookmarks, etc.) >>It has it all. You can also export via the Django exporter (and also import as well). >>This allows migration to many SQL-based systems. However, this design >>is based on an ORM and is automated. It is not easy to use without the >>ORM. >OK, so why have we not migrated Gramps to an SQL database. Because we haven't found a problem that SQL solves. What problem do you want to solve? ~Brian |
From: Doug B. <dou...@gm...> - 2012-07-31 13:44:15
|
On Tue, Jul 31, 2012 at 8:40 AM, Brian Matherly <br...@gr...> wrote: > Hi John, > > Welcome to Gramps. > > Every couple of years someone comes along and tells us that we screwed up by not using an SQL backend for the main application. We are kind of used to it. > > Rather than dive in and rehash all the previous conversation, I'll provide you with a list of links for your enjoyment (this is in no way intended to tell you to "go away", rather just to get you up to speed): > > http://article.gmane.org/gmane.comp.genealogy.gramps.devel/3464/match=sql > http://article.gmane.org/gmane.comp.genealogy.gramps.devel/10142/match=sql > http://www.gramps-project.org/wiki/index.php?title=Database_Formats > http://www.gramps-project.org/wiki/index.php?title=Relational_database_comparison > http://www.gramps-project.org/wiki/index.php?title=GEPS_010:_SQL_Backend > http://www.gramps-project.org/wiki/index.php?title=Database_abstraction_layers_comparison > http://www.gramps-project.org/wiki/index.php?title=GRAMPS_SQL_Database > > >>I started this thread because I do not think that BSDDB is necessarily the best database engine today or the best for implementing large, complex data stores. Furthermore as I looked at the current database I felt that the existing DB looks overly complex. I went looking for >Gramps design documentation and could not find any, APIs are implementation not design. >>rest of comments inline. > > > I know someone has produced a model in the past. I can't find it right now. The reason is because no one uses it. The data structure in the DB matches the API: > http://www.gramps-project.org/wiki/index.php?title=Using_database_API > >>>The most straightforward is the Sqlite import and export addons. You >>>can get these via the auto updater build into Gramps 3.3 and greater. >>>See menu -> Preferences -> general -> Check for updates, etc. The Sqlite exporter is 100% Gramps complete, exporting all of the >>>genealogical data (doesn't export other items, like bookmarks, etc.) >>>It has it all. You can also export via the Django exporter (and also import as well). >>>This allows migration to many SQL-based systems. However, this design >>>is based on an ORM and is automated. It is not easy to use without the >>>ORM. > >>OK, so why have we not migrated Gramps to an SQL database. > > > Because we haven't found a problem that SQL solves. What problem do you want to solve? While I largely agree with Brian's sentiments above, there are many problems that SQL-based systems (and some modern No-SQL database managers) do solve. For example, if you had a large Gramps BSDDB database (say 1 million records) and wanted to find all of the people that had a surname (or alternate surname) that contained the letters "rosenfield", then, you'd have a time-expensive task on your hand. (Gramps names are nested inside the Person object). A SQL system could extract and search those quickly and abstractly (eq, tools for analysis, indices can be added later, etc.). Although Gramps has had some great work to make it run as fast as possible, there are many parts that assume that sequential access is ok, and that makes the whole system run slow with very large db's. Also, Gramps (as is) cannot be used in a multi-user environment. Most any other modern database system would easily allow this. BSDDB has no schema, which makes it really hard for developers to understand what is going on. BSDDB has a number of files and log files, which no one seems to understand completely. Very easy to get corrupted. Finally, BSDDB changes its low-level structures way too often. Genealogical data should be designed to survive long periods. An idea, originally proposed by Gerald, is to use a good, robust SQL system, but to use the Gramps hierarchical data blob (they are serialized Python structures that are base64 encoded). I have done just that with the Django system, and combined that with the relational power too. Sqlite would work great as a hybrid system like that. That is my plan, when DbDictionary is done. -Doug > ~Brian > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Gramps-devel mailing list > Gra...@li... > https://lists.sourceforge.net/lists/listinfo/gramps-devel |
From: Doug B. <dou...@gm...> - 2012-07-31 13:22:00
|
On Tue, Jul 31, 2012 at 8:11 AM, john <jo...@kl...> wrote: > I started this thread because I do not think that BSDDB is necessarily the > best database engine today or the best for implementing large, complex data > stores. Furthermore as I looked at the current database I felt that the > existing DB looks overly complex. I went looking for Gramps design > documentation and could not find any, APIs are implementation not design. > rest of comments inline. John, I'll add to what Benny and Brian have mentioned. I completely agree that BSDDB is not the best choice today. But there weren't many (any?) viable No-SQL, hierarchical databases available many years ago. Today there are many. (My issues with BSDDB are more related to the difficulty in maintaining compatibility across versions and platforms, not about whether it is relational or not). > "He who opens a school door, closes a prison" Victor Hugo > > On 30/07/2012 11:30 PM, Doug Blank wrote: > > John, > > Welcome to Gramps! I presume that you are new to Gramps, as there are > already tools to move Gramps data back and forth between SQL > databases. > > Fairly new. > > The most straightforward is the Sqlite import and export addons. You > can get these via the auto updater build into Gramps 3.3 and greater. > See menu -> Preferences -> general -> Check for updates, etc. > > The Sqlite exporter is 100% Gramps complete, exporting all of the > genealogical data (doesn't export other items, like bookmarks, etc.) > It has it all. > > You can also export via the Django exporter (and also import as well). > This allows migration to many SQL-based systems. However, this design > is based on an ORM and is automated. It is not easy to use without the > ORM. > > OK, so why have we not migrated Gramps to an SQL database. I did an experiment, and, much to my surprise, a relational database design was very slow. Why? Specifically because the Gramps code was written in a particular way that does not favor such a design. But combining the relational with the hierarchical would be a step forward. I am working towards that goal. The first step is to develop a DBI that is completely independent of BSDDB. There is currently the start of a gen/db/dictionary.py implementation that will create a entire Gramps DB interface as a Python Dictionary. When that is complete and tested, then one can easily hook up any database. > Finally, we are completing web-based version of Gramps. It uses > relational data for the browsing and querying, but it uses the Gramps > BSDDB internal hierarchical structures for interfacing with Gramps > code. Because we wanted to utilize all of the existing Gramps code, it > was found too slow to do this with a relational data model. But the > web-based version uses both, and can switch back and forth when > convenient. > > An online version is of little interest to me unless I can maintain complete > control of my data. You dismiss things pretty quick, eh? First, the code is designed to run on a server, but that doesn't mean it has to. And even when it is on a server, it is your server. But my plan is to use the lessons learned in that project to develop an abstract DBI for Gramps so that we are not tied specifically to any one DB in the future. -Doug > Please feel free to ask additional questions, and read the extensive > info at the wiki [1]. > > -Doug > > [1] - http://www.gramps-project.org/wiki/index.php?title=Main_page > > On Mon, Jul 30, 2012 at 10:55 PM, john <jo...@kl...> wrote: > > I am trying to determine what entities might be involved in a Gramps > Database, I am interested because I believe that Gramps should be > migrated to a SQL DB be that MySQL, Postgres or SQLite. > > What modelling tools should we use, in order to make exchange of > information easier? > > I believe that the following are the fundamental entities that are > involved: Person, Event, Relationship, Location, Source. > Are these the fundamentals? Are there others that should be included? > What should each of these entities contain? > > John A > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Gramps-devel mailing list > Gra...@li... > https://lists.sourceforge.net/lists/listinfo/gramps-devel > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Gramps-devel mailing list > Gra...@li... > https://lists.sourceforge.net/lists/listinfo/gramps-devel > |
From: Nick H. <nic...@ho...> - 2012-07-31 21:18:13
Attachments:
relationshipdiagram.dia
|
On 31/07/12 13:40, Brian Matherly wrote: > I know someone has produced a model in the past. I can't find it right now. The reason is because no one uses it. The data structure in the DB matches the API: > http://www.gramps-project.org/wiki/index.php?title=Using_database_API I attach a relationship diagram for Gramps. I think that I translated it into English from the original, but I can't remember who created it. Unfortunately, it is out of date and not entirely accurate. If we created a better version, it could actually be useful. Nick. |
From: Doug B. <dou...@gm...> - 2012-08-01 02:57:08
|
On Tue, Jul 31, 2012 at 5:18 PM, Nick Hall <nic...@ho...> wrote: > On 31/07/12 13:40, Brian Matherly wrote: >> >> I know someone has produced a model in the past. I can't find it right >> now. The reason is because no one uses it. The data structure in the DB >> matches the API: >> http://www.gramps-project.org/wiki/index.php?title=Using_database_API > > I attach a relationship diagram for Gramps. > > I think that I translated it into English from the original, but I can't > remember who created it. > > Unfortunately, it is out of date and not entirely accurate. If we created a > better version, it could actually be useful. Django has tools to automatically create an entity-relationship diagram from the model definitions, which makes it easy to make up-to-date diagrams. I made this one a while back: http://www.gramps-project.org/wiki/images/a/a9/All-tables.gif This one is too small to be useful (I just wanted to get a gestalt of the whole project), and it has too much detail. If we can figure out how to trim the fine-grain details out, I think it would look similar to your dia diagram. -Doug > Nick. > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Gramps-devel mailing list > Gra...@li... > https://lists.sourceforge.net/lists/listinfo/gramps-devel > |
From: Nick H. <nic...@ho...> - 2012-07-31 21:39:14
|
On 31/07/12 14:21, Doug Blank wrote: > I did an experiment, and, much to my surprise, a relational database > design was very slow. Why? Specifically because the Gramps code was > written in a particular way that does not favor such a design. Do you still have the code from your experiment? Perhaps John would be interested in looking at it? As far as I can remember you created a normalised database. The Gramps DB interface has methods to create objects given a handle (primary key). This involved getting data from a number of tables, which was slow. For example, when editing a Person, Gramps creates a Person object using the get_person_from_handle method in the database. In our existing database this involves retrieving a single record from a table. In a normalised relational database this would invlove quite a few table joins. > But > combining the relational with the hierarchical would be a step > forward. I am working towards that goal. The first step is to develop > a DBI that is completely independent of BSDDB. There is currently the > start of a gen/db/dictionary.py implementation that will create a > entire Gramps DB interface as a Python Dictionary. When that is > complete and tested, then one can easily hook up any database. I assume that this uses the hybrid approach of storing all data for an object in a single row in one table. The would only be one table for each Gramps primary object. There are 9 primary objects in Gramps: Person, Family, Event, Place, Source, Citation, Repository, Media, Note. > But my plan is to use the lessons learned in that project to develop > an abstract DBI for Gramps so that we are not tied specifically to any > one DB in the future. I look forward to seeing this developed further. Nick. |
From: Doug B. <dou...@gm...> - 2012-08-01 03:14:41
|
On Tue, Jul 31, 2012 at 5:39 PM, Nick Hall <nic...@ho...> wrote: > On 31/07/12 14:21, Doug Blank wrote: >> I did an experiment, and, much to my surprise, a relational database >> design was very slow. Why? Specifically because the Gramps code was >> written in a particular way that does not favor such a design. > > Do you still have the code from your experiment? Perhaps John would be > interested in looking at it? Yes, it is called "gramps-connect" :) If you turn off the cache, then it will operate in relational database only mode. > As far as I can remember you created a normalised database. The Gramps > DB interface has methods to create objects given a handle (primary > key). This involved getting data from a number of tables, which was slow. > > For example, when editing a Person, Gramps creates a Person object using > the get_person_from_handle method in the database. In our existing > database this involves retrieving a single record from a table. In a > normalised relational database this would invlove quite a few table joins. Yes, exactly. That is the way the Django relational system works normally. Which is fine, except for interacting with Gramps code. > >> But >> combining the relational with the hierarchical would be a step >> forward. I am working towards that goal. The first step is to develop >> a DBI that is completely independent of BSDDB. There is currently the >> start of a gen/db/dictionary.py implementation that will create a >> entire Gramps DB interface as a Python Dictionary. When that is >> complete and tested, then one can easily hook up any database. > > I assume that this uses the hybrid approach of storing all data for an > object in a single row in one table. The would only be one table for > each Gramps primary object. > > There are 9 primary objects in Gramps: Person, Family, Event, Place, > Source, Citation, Repository, Media, Note. Yes, it was very easy to store a serialized, encoded version of each instance in each table (eg, Person.cache, Family.cache, etc). > >> But my plan is to use the lessons learned in that project to develop >> an abstract DBI for Gramps so that we are not tied specifically to any >> one DB in the future. > > I look forward to seeing this developed further. Me too :) Gerald has said that he'd take a look at the DictionaryDb when he can. Then I think it will be very easy to plug in any modern DB. But, even if it is a relational-based system, it will surely want to cache the serialize-encoded representation so that it works in a speedy fashion with existing Gramps code. Then, we could add relational functionality on top. I would recommend Sqlite (at this point) for its backwards-compatibility philosophy, and its simple single-file system [1]. But systems like MongoDB [2] could be explored as well. -Doug [1] - http://www.sqlite.org/onefile.html [2] - http://en.wikipedia.org/wiki/MongoDB > > Nick. > > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Gramps-devel mailing list > Gra...@li... > https://lists.sourceforge.net/lists/listinfo/gramps-devel |
From: john <jo...@kl...> - 2012-08-10 17:02:46
|
I have not forgotten this, just that I am a bit rusty using UML/ER software to develop a ER diagrams. Suggestions as to suitable tools welcome. -- IDIOT, n. A member of a large and powerful tribe whose influence in human affairs has always been dominant and controlling. |