Difference between revisions of "GEPS 010: Relational Backend"

From Gramps
Jump to: navigation, search
(→‎Discussions of BSDDB in Python)
m
 
(89 intermediate revisions by 8 users not shown)
Line 1: Line 1:
−
This page is for the discussion of a proposed implementation of an SQL backend for GRAMPS.  
+
{{man warn|This proposal has been withdrawn.|This original GEP was a design to use a relational database backend for Gramps. However, after experiments, it was shown to be too slow. However, it was shown that we could use other backends, but in an non-relational manner. Also, there are many [http://en.wikipedia.org/wiki/NoSQL NOSQL] solutions that would also work. Thus, a new proposal, [[GEPS 032: Database Backend API]] was created.}}
  
−
[[Category:GEPS]]
+
This proposal is also related to [[GEPS 013: Gramps Webapp]] which can create a SQL interface.
  
−
= SQL Backend =
+
There is also a SQL import and export Addon.
  
−
Currently, GRAMPS uses a BSD database as its internal file format. While this is considerably better than, say, an XML format, the choice of the BSD-DB has a considerable number of drawbacks. This proposal explores the use of SQL, specifically SQLite as a replacement.
+
<hr>
 +
 
 +
This page is for the discussion of a proposed implementation of a relational (SQL) backend for GRAMPS.
 +
 
 +
SQL stands for "Structured Query Language" and is pronounced "sequel" (it is a joke: as it came after QUEL, it is its sequel).
 +
 
 +
= Reasons for adding a relational backend =
 +
Currently, GRAMPS uses a [[Gramps_Glossary#bsddb|BSDDB]] database as its internal file format. While this is considerably better than, say, an XML format, the choice of the BSD-DB has a considerable number of drawbacks. This proposal explores the use of SQL as an alternative backend. This should allow easy, single db file implementations (eg, SQLite) to more complex and sophisticated client/server (eg, MySQL).
  
 
First, there are a number of facts related to this proposal:
 
First, there are a number of facts related to this proposal:
  
 
# BSDDB is being removed from the standard distribution of Python (as of Python 2.6)
 
# BSDDB is being removed from the standard distribution of Python (as of Python 2.6)
−
# SQLITE is being added to the standard distribution
+
# SQLITE is being added to the standard Python distribution
−
# BSDDB is not a relational database, but a hierarchical one
+
# BSDDB is not a relational database, but a hierarchical datastore
−
# BSDDB requires quite a bit of the database structure logic to reside in code (rather than in the database)
+
# BSDDB databases do not have schema or data-definitions. BSDDB requires all of the database structure logic to reside in code
−
# SQLite tables of a database reside in a single file
+
# BSDDB is a programmer's API
−
# SQLite can optimize queries (in low-level C) whereas BSDDB is done in Python
+
# SQL is a declarative, independent abstraction layer
−
 
+
# SQL can optimize queries (in low-level C) whereas BSDDB is done in Python
−
Next, are a number of issues that need to be tested, but best guesses suggest:
+
# An SQL version of a GRAMPS DB should be faster
−
 
+
# An SQLite version of a GRAMPS DB should be smaller than a BSDDB file.
−
# An SQLite version of a GRAMPS BSDDB would be at least 4 times smaller
+
# SQL Engines can perform query optimizations
−
# An SQLite version of a GRAMPS BSDDB should be much faster
+
# More code would reside in the db, rather than in Python
−
# An SQLite version of GRAMPS would allow people to create much bigger trees
+
# Enterprise SQL versions of GRAMPS would allow people to create and manage much larger trees
 +
# An SQLite version of GRAMPS might allow people to create larger trees
 +
## Because we move all of the DB logic into SQL, we can focus on making GRAMPS stable with large databases
 +
# SQL code is simpler than the equivalent BSDDB code, because SQL is declarative/abstract and BSDDB is a low-level API
  
 
Further implications:
 
Further implications:
  
 
# A fullscale MySQL backend would be a trivial step from SQLite
 
# A fullscale MySQL backend would be a trivial step from SQLite
 +
True as long as the SQL written to access the database conforms to a particular defined standard. SQLite appears to support (most of) SQL-92. So for portability reasons we must code to the SQL-92 standard if SQLlite is chosen as the lowest common denominator. --[[User:Gburto01|Gburto01]] 23:06, 4 April 2009 (UTC)
 
# Easy to allow multiple users in a SQLite database (uses file-locking)
 
# Easy to allow multiple users in a SQLite database (uses file-locking)
 +
# There is a lot of code that we have written dealing with BSDDB. It would have to all be rewritten in SQL (on the other hand, a lot of code can be deleted, which will make GRAMPS easier to maintain and adapt)
 +
# We will have to develop SQL experts
 +
I have been writing SQL for 20 years :) --[[User:Gburto01|Gburto01]] 23:06, 4 April 2009 (UTC)
 +
 +
Perhaps one of the most important reasons for moving away from a low-level datastore is to allow more sophisticated interfaces. Currently BSDDB offers transactions and recover, or multiuser access, but not both together. If we want to have a GRAMPS webapp or a multiuser GTK interface (for examples), then we would have to either disable transactions and recover (dangerous!) or switch backends. (Another option would be to write a server interface, but the complexity of that is probably on par with just switching backends. See [[GEPS 013: Gramps Webapp]] for more discussion.)
  
 
== Discussions of BSDDB in Python ==
 
== Discussions of BSDDB in Python ==
Line 40: Line 56:
 
PEP 3108 marks BSDDB to be removed:
 
PEP 3108 marks BSDDB to be removed:
 
http://www.python.org/dev/peps/pep-3108/
 
http://www.python.org/dev/peps/pep-3108/
 +
Development is not death however, it will only be out of sync of the python cycle. The home of pybsdb offering the bsddb3 package is hereL http://www.jcea.es/programacion/pybsddb.htm
 +
 +
= Research =
 +
*[[Relational database comparison]]
 +
*[[Database abstraction layers comparison]]
 +
 +
= Questions and concerns =
 +
 +
== Native DB access for other languages ==
 +
If we use a well-known SQL backend, we should consider the ability for other languages to be able to natively access the database. For example, a PHP program should be able to use the same database. Does using a Python-based ORM tie the data to Python? Or can the database still be used natively from other systems?
 +
 +
Using a Python based ORM wont tie the data just to python. any language should be able to access the db just fine. However, they wouldn't have access to pythons orm layer. Since I haven't used a true orm before I'm not certain exactly how it will effect our table relationships but I don't believe they wont make some sense in a relational way. Not that I'm saying we should use it but a quick google search started to bring up things like this [http://pecl.php.net/package/python php python package]. so there may be some hope for even using the orm layer but how complex would we really want to make it! And of course there is always the option of just using an orm and building similar objects in the new language. --[[User:AaronS|AaronS]] 03:30, 26 March 2009 (UTC)
  
−
A sqlite shelve interface for Python:
+
== Power vs Dependencies ==
−
http://bugs.python.org/issue3783
+
Do we want to have an additional layer over the Database Abstraction Layer (eg, an ORM)?
  
−
From http://www.sqlite.org/cvstrac/wiki?p=SqliteCompetitors:
+
PROS:
−
SQLite versus Berkeley DB:
 
  
−
<pre>
+
# Makes GRAMPS code more abstract
−
      Berkeley DB (BDB) is just the data storage layer - it does not
 
−
      support SQL or schemas. In spite of this, BDB is twice the size
 
−
      of SQLite. A comparison between BDB and SQLite is similar to a
 
−
      comparison between assembly language and a dynamic language like
 
−
      Python or Tcl. BDB is probably much faster if you code it
 
−
      carefully. But it is much more difficult to use and considerably
 
−
      less flexible.
 
  
−
      On the other hand BDB has very fine grained locking (although
+
CONS:
−
      it's not very well documented), while SQLite currently has only
 
−
      database-level locking. -- fine grain locking is important for
 
−
      enterprise database engines, but much less so for embedded
 
−
      databases. In SQLite, a writer gets a lock, does an update, and
 
−
      releases the lock all in a few milliseconds. Other readers have
 
−
      to wait a few milliseconds to access the database, but is that
 
−
      really ever a serious problem?
 
−
</pre>
 
  
−
== Comparing from BSDDB to SQLite ==
+
# Makes it harder for other languages to use the native GRAMPS db (but they can use the native db)
 +
# Adds a dependency
  
−
A company that justifies a switch from BSDDB to SQLite; see http://www.tribler.org/DatabaseMigration:
+
Given that GRAMPS's developers have, in the past, written their own db transactions, and their own HTML abstractions, does it make sense to add such a dependency?
  
−
Oracle's description of BSDDB; see http://www.oracle.com/database/docs/Berkeley-DB-v-Relational.pdf. Excerpt:
+
Is the ORM available for all platforms?
  
−
<pre>
+
I'm not certain if we need the orm layer or not. One option would be to just use sqlAlchemy's  SQL Expression Language layer for abstraction and forgo the orm. sqlAlchemy's [http://www.sqlalchemy.org/ home] and [http://www.sqlalchemy.org/docs/05/intro.html intro] might help explain some things. Another is that what ever we do we will be using a DB-API so perhaps we should test how far that takes us first before adding yet another dependency. Is the ORM available for all platforms? if your asking about programming languages no sqlAlchemy is a python module. if your asking about databases the [http://www.sqlalchemy.org/docs/05/dbengine.html#supported-dbapis support databases list] is decent. --[[User:AaronS|AaronS]] 19:16, 26 March 2009 (UTC)
−
Berkeley DB Offers APIs, not Query Languages
 
  
−
Berkeley DB was designed for software developers, by software
+
Actually, I was wondering about OS platforms, but that info is useful, too. --[[User:Dsblank|Dsblank]] 00:40, 27 March 2009 (UTC)
−
developers.  Relational database systems generally provide SQL access
 
−
to the data that they manage, and usually offer some SQL abstraction,
 
−
like ODBC or JDBC, for use in applications.
 
−
</pre>
 
  
−
What BSDDB is not:
+
I only researched python orms so they should work where ever python does. I confirmed sqlalchemy probably does since it supports MS-SQL and MSAccess --[[User:AaronS|AaronS]] 03:33, 27 March 2009 (UTC)
  
−
http://pybsddb.sourceforge.net/ref/intro/dbisnot.html
+
= What now? =
  
−
From previous GRAMPS discussions:
+
Current DBI:
  
−
http://mlblog.osdir.com/genealogy.gramps.devel/2005-02/msg00092.shtml&ei=2MYxSanZNaCgesqz0KQB&usg=AFQjCNG1l3yKZ4YP_L7Yo0cQ8bqWmoJKTQ&sig2=H8x1qf4YrFYlsLFlJUsZ-w
+
* [[Using_database_API]]
  
−
From the GRAMPS archives:
+
== Create Object model==
−
<pre>
 
−
> Now, sometimes we get a request for a major architectural change that we
 
−
> will accept. A good example is the new database backend for the upcoming
 
−
> GRAMPS 2.0. The request came in to support a real database backend so we
 
−
> could support larger databases. We analyzed the request, and felt that
 
−
> it matched the goals of the project and would provide a significant step
 
−
> forward in the usability of the program. The result was a major redesign
 
−
> effort that will soon be released.
 
  
−
I think I and few others are the ones that impacted this decision.  Having an  
+
Going over src/gen/lib/, create an object model of how GRAMPS uses and manipulates genealogy data.
−
850,000 person database tends to be deadly to the XML architecture that we
 
−
were with.  I've been the main person to test the integrity of the system
 
−
with my Gedcom file importing.  When I found that I couldn't import my file
 
−
without extensive data loss, I came to Don and Alex and we all sought for
 
−
solutions.  We found that the XML interface was taking huge amounts of
 
−
memory, and we looked for database backends that would handle the load.  Don
 
−
and Alex came through with the BSDDB backend, and ever since 1.1.3, I've been
 
−
happy as a clam with the Gramps project, because I'm one step closer to
 
−
killing Windows.
 
  
−
I personally want to do away with it, but I need it for other applications.
+
'''For this GEP to succeed it is extremely important that the experienced developers on the devel list agree with the object model'''. This goes without saying. For this to succeed, the developers should agree with all of the major decisions.
−
I've also come to the realization that both Windows and Linux are good, but
 
−
in their own realms. I don't want this to become a huge flame war about
 
−
Linux and Windows. so if you have other questions as to why I feel this way,  
 
−
email me.
 
  
−
> So, would we accept a mySQL database backend? There is a good chance we
+
== Select an SQL framework==
−
> would (depending on the implementation), as long did not impact Aunt
+
# finish research and pick a database.
−
> Martha. We have even architected the backend to support this, since we
+
# finish research and pick a database abstraction layer.
−
> can see that higher end databases could provide additional functionality
 
−
> such as versioning and multiuser support.
 
  
−
We could accept mySQL because of this, but I agree with Don.  If it negatively
+
== Create models/tables ==
−
impacts the end user, why would we want to proceed with it?  I have a friend
 
−
that wondered about mySQL interaction, but he can see the impact that BSDDB
 
−
has had on my database, and he has sided with me as well as the rest of the
 
−
team.  Not to say that this is not a possibility, but we need to remain
 
−
focused on the tasks at hand.
 
  
−
> So, in summary, the project is going in a direction that seems to meet
+
# use the framework to set up a model of the database
−
> the needs of our users. If we changed directions, we might or might not
+
# generate the tables
−
> be able to reach a larger audience, but numbers are not our goal. We
+
# create a dump of bsddb database in the sql database
−
> fully support others submitting patches and other contributions, but
+
# validate that all things present in bsddb are present in the sql database
−
> they will be weighed on how they match the goals of the project (and
+
# check validation rules. Eg, handle should be unique, rules must be added to ensure adding to the family table an object with handle like a person object is '''impossible''' on the database layer. These kind of rules can be done technically (a primary object table with key on handle) or with rules.
−
> most of the patches we've received to date do match the goals). If
+
# best would be a framework that based on the model can generate an admin module to browse the database, see eg the admin module in django.
−
> someone wants us take the project in a different direction, we may or
 
−
> may not be receptive depending if the direction matches our goals.
 
−
> However, we will support your efforts if you decide to fork the project.
 
−
> Who knows, maybe a remerge will occur in the future, or a forked project
 
−
> will make us irrelevent.
 
  
−
I agree with Don on this, numbers don't matter as long as the users are happy. 
+
== New db backend for GRAMPS ==
−
Getting things appropriately nailed down and ready for the end user's use is
 
−
what is paramount.  After all, if there were no users, why would we even have
 
−
a project with which to collaborate in the first place?
 
  
−
We are here for the users, especially Aunt Martha, because of the fact that
+
# write an implementation of [http://gramps.svn.sourceforge.net/viewvc/gramps/trunk/src/gen/db/base.py src/gen/db/base.py] to interface the DB abstraction layer with the rest of gramps. Gramps 3.x only has one implementation: [http://gramps.svn.sourceforge.net/viewvc/gramps/trunk/src/gen/db/dbdir.py src/gen/db/dbdir.py], but in branch22 a gedcom and a gramps xml implementation can be found (these have been deprecated).
−
many people are just moving over to Linux and having something familiar to  
+
# once written, this can be added as an experimental backend to GRAMPS
−
them, like a genealogical program is what matters to them. Making the  
+
## Family Tree manager needs to list the family tree type (bsddb, sqlite), on creation of new family tree user must choose the backend.
−
transition to Linux is hard, don't get me wrong. But we are making it one
+
## User can import .gramps/gedcom files just as this is done with bsddb backend once family tree is set up.
−
step easier by not complicating the user's experience in their move.
+
# it will be very important to use properties in src/gen/lib to make this work. Obtaining a person via get_person_from_handle, should only hit the person table. Only when the calling method needs attributes, should the attribute table be hit. This requires attributes that are not yet defined up to the moment they are accessed. It also means that the gen/lib objects for sql need to be aware of the database as it needs to know where to obtain these values... . This looks like a huge work to me, but definitely doable. Just rewriting gen/lib for an sql datamodel might be easier though, but that means rewriting the core of GRAMPS....
  
−
Like I said before, I'm just a bug finder.  I'm not really a Python
+
I don't understand the use of '''slots''' in the above. How is that idea related to db access? --[[User:Dsblank|Dsblank]] 11:14, 26 March 2009 (UTC)
−
programmer, or anything, but I like to find bugs.  Even if that's all I do on
 
−
this project, I'm rather content. Everyone else that wants to port over to
 
−
other toolkits and whatnot is free to do so.
 
  
−
But also as an end user that's still a greenie to Linux in general, I can say
+
Doug, I meant '''properties'''. The idea is the following. In gen/person an person contains all secondary objects and all references. In a database, this depends on the implementation. So bsddb has a flat table with the person object in one table. So one get. For sql, the secondary objects will be in other tables. Hitting all relevant tables on get of a person would be stupid. We only need to access the tables when the data is needed. In base.py, a person is obtained with get_person_from_handle. This fills up a Person() object. dbdir.py uses the unserialize method to do this. My suggestion of properties would be that with an sql backend, the handle is get, but all the other Person attributes are properties that remain None. If data is needed, the getter checks if value is None, if so, the sql database is hit, otherwise the data is just returned. Just an idea, the concept needs to be worked out. The alternative is changing /gen/lib dramatically, but I don't see the benefit of that. Note that making all objects in /gen/lib new style classes with slots would be a good move nonetheless. [[User:Bmcage|bmcage]] 13:22, 8 April 2009 (UTC)
−
that this program has helped my move over to Linux that much easier. Even if  
 
−
I have only contributed a little in the way of feedback (mostly from the
 
−
end-user perspective).
 
  
−
-Jason
+
Thanks for the clarification... that makes sense now :) The integration of the SQL DB with the existing GRAMPS gen/lib and gen/db does seem to be the biggest job to be done in this project. I've had a look at gen/lib and gen/db and there are lots of assumptions all over the place about how the data is stored. Trying to hide this through the use of properties may work, but in the long run, GRAMPS may need an API overhaul. But I like the idea of seeing how far we can get by using properties. --[[User:Dsblank|Dsblank]] 12:56, 9 April 2009 (UTC)
−
</pre>
 
  
−
From http://osdir.com/ml/genealogy.gramps.user/2004-06/msg00078.html:
+
== Extending base.py ==
  
−
<pre>
+
Once an sql backend is stable, base.py can be extended to offer extra functionality, or better optimize for SQL. Eg, in SQL one would have probably an attribute table. To know which persons have a specific attribute, SQL would select that from the attributes table, and then look up the people. In bsddb it means however to loop over all persons, and obtain the attribute sub table of a person and looking if attribute is present there.
−
Alex said:
 
  
−
    SQLite might be better or it might not, we haven't tried it. A great factor
+
Above clearly indicates that how one goes about in the two backends is very different. The bsddb way will work in sql though (as the get_person method works, and speed should be comparable to bsddb if above deferred obtaining of values via slots is implemented). Nevertheless, a clear mechanism to optimize for sql is needed. Continuing above example, see [http://gramps.svn.sourceforge.net/viewvc/gramps/trunk/src/Filters/Rules/_HasAttributeBase.py _HasAttributeBase.py]
−
    speaking for BSDDB is that it is supported by a standard Python module,
 
−
    bsddb.  
 
  
 +
For sql, one would use the prepare method, obtain all people in a list, then return True or False if person is in this list. As db is passed, db can have a support_sql attribute, and code can be written depending on this setting. This does not look very ideal though.
  
−
Don said:
+
= See Also =
 +
* [[Addon:SQLite Export Import]]
  
−
This is an important factor here - ease of setup and use. GRAMPS is
+
[[Category:GEPS|S]]
−
difficult enough to get installed on some platforms (especially
 
−
KDE-centric systems). Requiring someone to get an SQL database up and
 
−
running to try out the program is probably too much effort. What I've
 
−
discovered is that GRAMPS is one of the first programs that a lot of
 
−
new users want to get running - usually before they have a lot of
 
−
Linux experience. So we can't make the barriers to entry too high.
 
−
</pre>
 

Latest revision as of 00:20, 31 January 2022

Gnome-important.png
This proposal has been withdrawn.

This original GEP was a design to use a relational database backend for Gramps. However, after experiments, it was shown to be too slow. However, it was shown that we could use other backends, but in an non-relational manner. Also, there are many NOSQL solutions that would also work. Thus, a new proposal, GEPS 032: Database Backend API was created.

This proposal is also related to GEPS 013: Gramps Webapp which can create a SQL interface.

There is also a SQL import and export Addon.

This page is for the discussion of a proposed implementation of a relational (SQL) backend for GRAMPS.

SQL stands for "Structured Query Language" and is pronounced "sequel" (it is a joke: as it came after QUEL, it is its sequel).

Reasons for adding a relational backend

Currently, GRAMPS uses a BSDDB database as its internal file format. While this is considerably better than, say, an XML format, the choice of the BSD-DB has a considerable number of drawbacks. This proposal explores the use of SQL as an alternative backend. This should allow easy, single db file implementations (eg, SQLite) to more complex and sophisticated client/server (eg, MySQL).

First, there are a number of facts related to this proposal:

  1. BSDDB is being removed from the standard distribution of Python (as of Python 2.6)
  2. SQLITE is being added to the standard Python distribution
  3. BSDDB is not a relational database, but a hierarchical datastore
  4. BSDDB databases do not have schema or data-definitions. BSDDB requires all of the database structure logic to reside in code
  5. BSDDB is a programmer's API
  6. SQL is a declarative, independent abstraction layer
  7. SQL can optimize queries (in low-level C) whereas BSDDB is done in Python
  8. An SQL version of a GRAMPS DB should be faster
  9. An SQLite version of a GRAMPS DB should be smaller than a BSDDB file.
  10. SQL Engines can perform query optimizations
  11. More code would reside in the db, rather than in Python
  12. Enterprise SQL versions of GRAMPS would allow people to create and manage much larger trees
  13. An SQLite version of GRAMPS might allow people to create larger trees
    1. Because we move all of the DB logic into SQL, we can focus on making GRAMPS stable with large databases
  14. SQL code is simpler than the equivalent BSDDB code, because SQL is declarative/abstract and BSDDB is a low-level API

Further implications:

  1. A fullscale MySQL backend would be a trivial step from SQLite

True as long as the SQL written to access the database conforms to a particular defined standard. SQLite appears to support (most of) SQL-92. So for portability reasons we must code to the SQL-92 standard if SQLlite is chosen as the lowest common denominator. --Gburto01 23:06, 4 April 2009 (UTC)

  1. Easy to allow multiple users in a SQLite database (uses file-locking)
  2. There is a lot of code that we have written dealing with BSDDB. It would have to all be rewritten in SQL (on the other hand, a lot of code can be deleted, which will make GRAMPS easier to maintain and adapt)
  3. We will have to develop SQL experts

I have been writing SQL for 20 years :) --Gburto01 23:06, 4 April 2009 (UTC)

Perhaps one of the most important reasons for moving away from a low-level datastore is to allow more sophisticated interfaces. Currently BSDDB offers transactions and recover, or multiuser access, but not both together. If we want to have a GRAMPS webapp or a multiuser GTK interface (for examples), then we would have to either disable transactions and recover (dangerous!) or switch backends. (Another option would be to write a server interface, but the complexity of that is probably on par with just switching backends. See GEPS 013: Gramps Webapp for more discussion.)

Discussions of BSDDB in Python

BSDDB has had a hard time in Python. Python Developers have been wrestling with trying to keep it stable. Guido finally decided to separate BSDDB from the standard Python Distribution. See discussions:

PEP 3108 marks BSDDB to be removed: http://www.python.org/dev/peps/pep-3108/ Development is not death however, it will only be out of sync of the python cycle. The home of pybsdb offering the bsddb3 package is hereL http://www.jcea.es/programacion/pybsddb.htm

Research

Questions and concerns

Native DB access for other languages

If we use a well-known SQL backend, we should consider the ability for other languages to be able to natively access the database. For example, a PHP program should be able to use the same database. Does using a Python-based ORM tie the data to Python? Or can the database still be used natively from other systems?

Using a Python based ORM wont tie the data just to python. any language should be able to access the db just fine. However, they wouldn't have access to pythons orm layer. Since I haven't used a true orm before I'm not certain exactly how it will effect our table relationships but I don't believe they wont make some sense in a relational way. Not that I'm saying we should use it but a quick google search started to bring up things like this php python package. so there may be some hope for even using the orm layer but how complex would we really want to make it! And of course there is always the option of just using an orm and building similar objects in the new language. --AaronS 03:30, 26 March 2009 (UTC)

Power vs Dependencies

Do we want to have an additional layer over the Database Abstraction Layer (eg, an ORM)?

PROS:

  1. Makes GRAMPS code more abstract

CONS:

  1. Makes it harder for other languages to use the native GRAMPS db (but they can use the native db)
  2. Adds a dependency

Given that GRAMPS's developers have, in the past, written their own db transactions, and their own HTML abstractions, does it make sense to add such a dependency?

Is the ORM available for all platforms?

I'm not certain if we need the orm layer or not. One option would be to just use sqlAlchemy's SQL Expression Language layer for abstraction and forgo the orm. sqlAlchemy's home and intro might help explain some things. Another is that what ever we do we will be using a DB-API so perhaps we should test how far that takes us first before adding yet another dependency. Is the ORM available for all platforms? if your asking about programming languages no sqlAlchemy is a python module. if your asking about databases the support databases list is decent. --AaronS 19:16, 26 March 2009 (UTC)

Actually, I was wondering about OS platforms, but that info is useful, too. --Dsblank 00:40, 27 March 2009 (UTC)

I only researched python orms so they should work where ever python does. I confirmed sqlalchemy probably does since it supports MS-SQL and MSAccess --AaronS 03:33, 27 March 2009 (UTC)

What now?

Current DBI:

Create Object model

Going over src/gen/lib/, create an object model of how GRAMPS uses and manipulates genealogy data.

For this GEP to succeed it is extremely important that the experienced developers on the devel list agree with the object model. This goes without saying. For this to succeed, the developers should agree with all of the major decisions.

Select an SQL framework

  1. finish research and pick a database.
  2. finish research and pick a database abstraction layer.

Create models/tables

  1. use the framework to set up a model of the database
  2. generate the tables
  3. create a dump of bsddb database in the sql database
  4. validate that all things present in bsddb are present in the sql database
  5. check validation rules. Eg, handle should be unique, rules must be added to ensure adding to the family table an object with handle like a person object is impossible on the database layer. These kind of rules can be done technically (a primary object table with key on handle) or with rules.
  6. best would be a framework that based on the model can generate an admin module to browse the database, see eg the admin module in django.

New db backend for GRAMPS

  1. write an implementation of src/gen/db/base.py to interface the DB abstraction layer with the rest of gramps. Gramps 3.x only has one implementation: src/gen/db/dbdir.py, but in branch22 a gedcom and a gramps xml implementation can be found (these have been deprecated).
  2. once written, this can be added as an experimental backend to GRAMPS
    1. Family Tree manager needs to list the family tree type (bsddb, sqlite), on creation of new family tree user must choose the backend.
    2. User can import .gramps/gedcom files just as this is done with bsddb backend once family tree is set up.
  3. it will be very important to use properties in src/gen/lib to make this work. Obtaining a person via get_person_from_handle, should only hit the person table. Only when the calling method needs attributes, should the attribute table be hit. This requires attributes that are not yet defined up to the moment they are accessed. It also means that the gen/lib objects for sql need to be aware of the database as it needs to know where to obtain these values... . This looks like a huge work to me, but definitely doable. Just rewriting gen/lib for an sql datamodel might be easier though, but that means rewriting the core of GRAMPS....

I don't understand the use of slots in the above. How is that idea related to db access? --Dsblank 11:14, 26 March 2009 (UTC)

Doug, I meant properties. The idea is the following. In gen/person an person contains all secondary objects and all references. In a database, this depends on the implementation. So bsddb has a flat table with the person object in one table. So one get. For sql, the secondary objects will be in other tables. Hitting all relevant tables on get of a person would be stupid. We only need to access the tables when the data is needed. In base.py, a person is obtained with get_person_from_handle. This fills up a Person() object. dbdir.py uses the unserialize method to do this. My suggestion of properties would be that with an sql backend, the handle is get, but all the other Person attributes are properties that remain None. If data is needed, the getter checks if value is None, if so, the sql database is hit, otherwise the data is just returned. Just an idea, the concept needs to be worked out. The alternative is changing /gen/lib dramatically, but I don't see the benefit of that. Note that making all objects in /gen/lib new style classes with slots would be a good move nonetheless. bmcage 13:22, 8 April 2009 (UTC)

Thanks for the clarification... that makes sense now :) The integration of the SQL DB with the existing GRAMPS gen/lib and gen/db does seem to be the biggest job to be done in this project. I've had a look at gen/lib and gen/db and there are lots of assumptions all over the place about how the data is stored. Trying to hide this through the use of properties may work, but in the long run, GRAMPS may need an API overhaul. But I like the idea of seeing how far we can get by using properties. --Dsblank 12:56, 9 April 2009 (UTC)

Extending base.py

Once an sql backend is stable, base.py can be extended to offer extra functionality, or better optimize for SQL. Eg, in SQL one would have probably an attribute table. To know which persons have a specific attribute, SQL would select that from the attributes table, and then look up the people. In bsddb it means however to loop over all persons, and obtain the attribute sub table of a person and looking if attribute is present there.

Above clearly indicates that how one goes about in the two backends is very different. The bsddb way will work in sql though (as the get_person method works, and speed should be comparable to bsddb if above deferred obtaining of values via slots is implemented). Nevertheless, a clear mechanism to optimize for sql is needed. Continuing above example, see _HasAttributeBase.py

For sql, one would use the prepare method, obtain all people in a list, then return True or False if person is in this list. As db is passed, db can have a support_sql attribute, and code can be written depending on this setting. This does not look very ideal though.

See Also

Retrieved from "https://gramps-project.org/wiki/index.php?title=GEPS_010:_Relational_Backend&oldid=90068"