Changes

{{man warn|This page is for the discussion of proposal has been withdrawn.|This original GEP was a proposed implementation of design to use a SQL relational database backend for GRAMPSGramps. 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. }}

A proposed implementation This proposal is being developed in also related to [http[GEPS 013://gramps.svn.sourceforge.net/viewvc/gramps/trunk/src/plugins/export/ExportSql.py?view=markup trunk/src/plugins/export/ExportSql.pyGramps Webapp]] You which can export most of GRAMPS through an create a SQL Export using the Export Assistant. (Currently, the selection options are ignored, and it will output everything)interface.

There is also a SQL stands for "Structured Query Language" import and is pronounced "sequel" (it is a joke: as it came after QUEL, it is its sequel). After you export your GRAMPS data into a file such as ''Untitled_1Addon.sql'' using the above Exporter, then you can use SQL queries like:

<prehr>$ sqlite3 Untitled_1.sqlSQLite version 3.5.9Enter ".help" for instructions

sqlite> This page is for the discussion of a proposed implementation of a relational (SQL) backend for GRAMPS.tablesdates family names people repositoryevents media notes places sources

sqlite> .headers on.headers on sqlite> select * from people;handle|gramps_id|gender|death_ref_index|birth_ref_index|change|marker0|marker1|privateb247d7186567ff472ef|I0000|1|-1|-1|1225135132|-1||0 sqlite> select * from names where surname like SQL stands for "Structured Query Language" and is pronounced "%Smith%sequel";private|first_name|surname|suffix|title|name_type0|name_type1|prefix|patronymic|group_as|sort_as|display_as|call0|Test|Smith|||2|||||0|0| sqlite> .exit$</pre> The current database in GRAMPS would require that you write some code to do this(it is a joke: as it came after QUEL, and you'd need to know some details about the datait is its sequel). = SQL Backend =

# BSDDB is not a relational database, but a hierarchical onedatastore

# SQLite tables of a database reside in a single file Next, are a number of claims that need to be tested: # An SQLite SQL version of a GRAMPS BSDDB may DB should be 4 times smallerfaster# An SQLite version of a GRAMPS BSDDB may be faster## The files may DB should be smallerthan a BSDDB file.## The smaller files may allow more into memorySQL Engines can perform query optimizations## More code would reside in Cthe db, rather than in Python## SQL Engines can perform query optimizations

# 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 (although maybe harder most of) SQL-92. So for portability reasons we must code to setup and maintain; although see Djangothe SQL-92 standard if SQLlite is chosen as the lowest common denominator. --[[User:Gburto01|Gburto01]] 23:06, 4 April 2009 (UTC)

<pre>It's good to see this discussion on gramps and is actually why I'm thinking of givingit another try depending on how hard it is to implement this. Yes I know it will be hardbut probably much easier and productive than starting my own project. I'm a developer myself and when it came time to evaluate gramps the lack of a relational db backend was Perhaps oneof the main most important reasons I decided to keep looking. Don't discount MySQL over SQLite. While I haven't tried it out yet there is an embeddable version of MySQL which might overcome some of sqlites advantages. If a database abstraction layer is used both could be easily supported. They both have their advantages and disadvantages. MySQLAdvantages*far better tools for management and reporting*moving away from a true enterprise low-level database capable of handling serious loads*far more datastore is built into the db. ie auto incrementing fields, stored procedures and on and on. (sqlite may not even have triggers but I can't remember)*far more extensive user base and support. Disadvantages*install size (bloat)*an actual server to setup run and maintain.** there are tools that can do this automatically though and make things almost none existent for an end user. also the embeddable mysql might be an option.*may be difficult to manage / share multiple databases. allow more difficult but very do ablesophisticated interfaces. maybe not even that difficult. it would just take some planning. SQLiteAdvantages*far easier to setup. just start writing to the file! no connection or user accounts.*smaller install (code) size.*easier for users to manage / and share sepperate db's*single file*good support. Disadvantage*while great for what it is it's not an enterprise level database*many "traditional" relational db things are lacking.*while tools exist they aren't as fleshed out and solid as the mysql ones. Personally I think SQLite makes more sense for genealogical software. but mysqlstools Currently BSDDB offers transactions and the fact that it's a "real" enterprise level relational db are serious advantages.-- AaronS</pre> == Transportable Trees == From http://www.sqlite.org/onefile.html: '''Single-file Cross-platform Database''' ''A database in SQLite is a single disk file. Furthermorerecover, the file format is cross-platform. A database that is created on one machine can be copied and used on a different machine with a different architecture. SQLite databases are portable across 32-bit and 64-bit machines and between big-endian and little-endian architectures.'' ''The SQLite database file format is also stable. All releases of of SQLite version 3 can read and write database files created by the very first SQLite 3 release (version 3.0.0) going back to 2004-06-18. This is "backwards compatibility". The developers promise to maintain backwards compatibility of the database file format for all future releases of SQLite 3. "Forwards compatiblity" means that older releases of SQLite can also read and write databases created by newer releases. SQLite is usuallyor multiuser access, but not completely forwards compatible.'' ''The stability of the SQLite database file format and the fact that the file format is cross-platform combine to make SQLite database files an excellent choice as an Application File Formatboth together.'' <pre>The Single disk file of sqlite db would be a major selling point for sqlitefor genealogy software since users share and compare db's all the time.--Aarons</pre> == Additional Issues == 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 want 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 GRAMPS webapp or 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 multiuser GTK interface (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:30examples), 26 March 2009 (UTC) == Database Abstraction Layer == I asked this question on [http://stackoverflow.com/questions/679806/what-are-the-viable-database-abstraction-layers-for-python StackOverflow]. === <strike> CouchDB </strike> ===http://code.google.com/p/couchdb-python/ [http://pylonshq.com/ Pylons] took the time to be able to use it. This is not a db abstraction layer and not even a relational db. === DB-API ===http://wiki.python.org/moin/DatabaseProgramming/ Python has an API to make it easy to move from one SQL-based DB to another called DB-API. Each DB may have multiple different modules available for it. If we settle on this solution then we should do some quick searches would have to make sure we pick the right modules. *MySQL: Yes [http://sourceforge.net/projects/mysql-python MySQLDB] used by SQLAlchemy*SQLite: Yes [http://www.python.org/doc/2.5.2/lib/module-sqlite3.html sqlite3] either disable transactions and recover (included in Python 2.5 or greater) [http://oss.itsystementwicklung.de/trac/pysqlite pysqlite] both used by SQLAlchemy*BSDDB: No. The DB-API looks to be only for relational dbs. *ORM (Object Relational Mapperdangerous!): no "While all DB-API modules have identical APIs (or very similar; not all switch backends support all features), if you are writing the SQL yourself, you will probably be writing SQL in a product-specific dialect, so they are not as interchangeable in practice as they are in theory." [http://stackoverflow.com/questions/679806/what-are-the-viable-database-abstraction-layers-for-python kquinn] === <strike> Django </strike> ===http://www.djangoproject.com/ Django also provides DB independence, but is geared towards web deployment: "Django developed its ORM (and template language) from scratch. While that may have been a pragmatic decision at the time, Python now has SQLAlchemy, a superior database layer that has gained a lot of momentum. '''Django’s in-house ORM lacks multiple database support''', and forces constraints on your database models (e.g. that every database table must have a single, integer primary key). If you choose Django, your project gains a near-inseparable dependency on Django’s ORM and database requirements." [http://marcuscavanaugh.com/blog/python-web-framework-advice/ marcus cavanaugh 2009] Django's DB abstraction probably isn't a good fit. While powerful I doubt any projects are using it outside of Django. ===<strike> pydo </strike>===[http://skunkweb.sourceforge.net/pydo.html pydo] doesn't look viable and looks like limited users and documentation. tied to a webframework. === SQLAlchemy ===http://www.sqlalchemy.org/ *MySQL: Yes*SQLite: Yes*BSDDB: No.  [http://www.sqlalchemy.org/docs/05/dbengine.html#supported-dbapis supported dbs] *ORM (Object Relational Mapper): yes but doesn't force it.*mulitple database support?: yes [http://www.sqlalchemy.org/ source] *Viability: Last release was January 24, 2009. They seem to have an established development team and user base. Project appears to Another option would be 3 years old, "SQLAlchemy is designed to operate with write a DB-API implementation built for a particular database" [http://www.sqlalchemy.org/docs/05/intro.html#api-reference source] "SQLAlchemyserver interface, widely considered to be but the best Python ORM available. SQLAlchemy includes multiple database support and just about any crazy combination complexity of database requirements needed, and it handles ORM very well — yet it also allows you to provide raw SQL as needed." [http://marcuscavanaugh.com/blog/python-web-framework-advice/ marcus cavanaugh 2009] === SQLObject ===http://www.sqlobject.org/ *MySQL: yes*SQLite: yes*BSDDB: no [http://www.sqlobject.org/SQLObject.html#requirements requirements] *ORM (Object Relational Mapper): yes this that is what sqlobject is all about.*mulitple database support?: ? *Viability: Last release was 2008-12-08, 7 developers probably on the project. couldn't find old release dates but first was posted 2003-04-09. link to wiki is broken which isn't the best sign but we've all had those times before... [http://www.sqlobject.org/SQLObject.html#compared-to-other-database-wrappers comparison] [http://pylonshq.com/ Pylons] took the time to be able to use it. === Storm ===https://storm.canonical.com/ *MySQL: yes*SQLite: yes*BSDDB: no *ORM (Object Relational Mapper): yes*mulitple database support?: yes *Viability: currently developed. seems like a fairly good site with some documentation. The bigest drawback is that the project is only a year old. a pro is that it may be easier to use than sqlalchemy. === Recomendations ===Let me preface this by restating that I've never actually used any of these abstraction layers and I'm not yet familiar with the gramps code and developers strengths. Other people par with more knowledge should be the ones making the decision. Also any decisions need to be revistable after we actually start coding in case they just don't work. I've spent the last few days trying to look at the current options for db abstraction. From what I currently know I think I'm going to recommend we use sqlalchemy with sqlite.  sqlite. no server to manage and single file db's will make them easy to share and manage multiple dbs at the same time. also make merging simpler. will allow websites to be developed that will work directly from the db. As long as gramps doesn't switch focus to be some kind of mass user website for editing large trees I think sqlite will fit the bill. sqlalchemy. this seems to have a large following and good documentation. It should allow us to support different db back ends easier in the future. at least some people think it's the best python orm available. it seems to provide good tools for when the ORM starts to get in the way.  Reasons I don't recommend the other options include: MySQL. probably not as user friendly and since gramps isn't a client / server sort of program I don't think it's necessary. DB-APIswitching backends. with See [http://www.python.org/doc/2.5.2/lib/module-sqlite3.html sqlite3] It sounds as if the DB-API in practice doesn't support the changing of dbs as much as might be thought. If we commit to sqlite though this might be an option. SQLObject. this seems like a viable alternative to sqlalchemy but slqlalchemy seems to have more documentation and user acceptance. Also the ORM layer might not step out of the way very nicely. the website says it will but I wasn't quite buying it from the examples. Storm. while this project looks promising and may be easier to use than sqlalchemy it's only a year old and as I was recently burned by picking a fringe tech for my tech stack I'm a bit skittish of anything that doesn't have wide acceptance and use. Additional notes: I was originally advocating for database abstraction not an orm layer. I've never used a true orm and can't fully say how they work in practice. While I'm not solidly on the orm badwagon I do think an orm layer might do gramps some good. There will be situations where simply writing queries will be far easier. Our implementation model should take that into account. from the website sqlalchemy sounds like it will provide both abstraction and an orm and we'll be able to use both as the needs determine. While I don't fully agree with the severity of this [httpGEPS 013://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx articleGramps Webapp] he does make some valid points. There is a reason that true object databases [http://en.wikipedia.org/wiki/Object_oriented_database haven't caught on]. I guess I'm advocating for something like "Developers simply accept that there is no way to efficiently and easily close the loop on the O/R mismatch, and use an O/R-M to solve 80% (or 50% or 95%, or whatever percentage seems appropriate) of the problem and make use of SQL and relational-based access ... to carry them past those areas where an O/R-M would create problems." [http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx article] --[[User:AaronS|AaronS]] 00:52, 26 March 2009 (UTC) === Discusion ===I suspect that we would have something like SQLite as a default, but allow experts to move to more sophisticated backends.  It is quite powerful, but perhaps more sophisticated than what we need. I think we want to find the right balance between power and dependencies. <pre>What you looking for here is called a Database Abstraction Layer they areindeed quite powerful and are exactly what you need. if your going to botherswitching the back end don't waste your time and not use one. you'll kickyourself later if you don't. just be careful which one you choose. I knowthat in php every web framework seems to have their own. I suspect the samefor python. Django has their own but allows for the use of others (if that tells you anything). might be a place to check for alternatives. While their framework might be for websites that shouldn't matter for the DB Abstraction layer. What to look for in a db Abstraction Layer is which dbs it can use. sqlite and mysql are musts, you may even find one that can talk to BSDDB but probably not. Oracle and PostgreSQL are pluses but will probably never be used but who knows what will happen in 5 or 10 yrs. who knows maybe oracle would get fed up with mysql and release the dbopen source charging for service. stranger things have happened.ease of use, readablity and outer joins are also important. don't worry toomuch about how complex of sql queries its supposed to allow you to create sincecomplex queries through a db layer tend to be difficult to create, read and predictdiscussion.ie sub queries and the like. usually those queries are far easier to just build as a query. in my experience a db abstraction layer is good for most of the db io. however, forthe complex stuff a sort of localization object (or even file) is a good bet with namedqueries. this would work similar to how different languages are usually supported inprojects. with a different object or file per db. I'd recommend an actual object with afunction per query over afile of constants/variables since some db's might require a little more manipulation thanothers. again this would only be for the most complex queries. a good rule of thumb wouldbe if you had to start writing parts of the query as strings move it to the db localization object. This db localization object isn't used for all queries because you only want to haveto tweak the minimum amount of queries across dbs--Aarons</pre>

From http://www.sqlite.org/cvstrac/wiki?p=SqliteCompetitors:SQLite versus Berkeley DB:Questions and concerns =

<pre>== Native DB access for other languages == Berkeley DB (BDB) is just the data storage layer If we use a well- it does not support known SQL or schemasbackend, we should consider the ability for other languages to be able to natively access the database. In spite of thisFor example, BDB is twice a PHP program should be able to use the size of SQLitesame database. A comparison between BDB and SQLite is similar to a comparison between assembly language and Does using a dynamic language like Python or Tcl. BDB is probably much faster if you code it carefully. But it is much more difficult -based ORM tie the data to use and considerably less flexible.Python? Or can the database still be used natively from other systems?

On Using a Python based ORM wont tie the data just to python. any language should be able to access the other hand BDB has very db just fine grained locking (although it's not very well documented), while SQLite currently has only database-level locking. -- fine grain locking is important for enterprise database enginesHowever, but much less so for embedded databasesthey wouldn't have access to pythons orm layer. In SQLite, Since I haven't used a writer gets a lock, does an update, and releases the lock all 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 few millisecondsrelational way. Other readers have to wait Not that I'm saying we should use it but a few milliseconds quick google search started to access bring up things like this [http://pecl.php.net/package/python php python package]. so there may be some hope for even using the database, orm layer but how complex would we really want to make it! And of course there is that really ever a serious problem?</pre>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)

== Comparing from BSDDB to SQLite Power vs Dependencies ==Do we want to have an additional layer over the Database Abstraction Layer (eg, an ORM)?

A company that justifies a switch from BSDDB to SQLite; see http://www.tribler.org/DatabaseMigrationPROS:

Berkeley DB was designed # Makes it harder for software developers, by softwaredevelopers. Relational database systems generally provide SQL accessother languages to use the data that native GRAMPS db (but they manage, and usually offer some SQL abstraction,like ODBC or JDBC, for can use in applications.the native db)</pre># Adds a dependency

From previous GRAMPS discussionsI'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)

http://mlblog.osdir.com/genealogyActually, I was wondering about OS platforms, but that info is useful, too.gramps.devel/2005-02/msg00092.shtml&ei=2MYxSanZNaCgesqz0KQB&usg=AFQjCNG1l3yKZ4YP_L7Yo0cQ8bqWmoJKTQ&sig2=H8x1qf4YrFYlsLFlJUsZ-w[[User:Dsblank|Dsblank]] 00:40, 27 March 2009 (UTC)

From the GRAMPS archives:<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 I only researched python orms 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 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 they should work where ever since 1.1python does.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. 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> would (depending on the implementation), as long did not impact Aunt> Martha. We have even architected the backend to support this, confirmed sqlalchemy probably does since we> 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 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> the needs of our users. If we changed directions, we might or might not> be able to reach a larger audience, but numbers are not our goal. We> fully support others submitting patches and other contributions, but> they will be weighed on how they match the goals of the project (supports MS-SQL and> most of the patches we've received to date do match the goals). If> 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. 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 many people are just moving over to Linux and having something familiar to them, like a genealogical program is what matters to them. Making the transition to Linux is hard, don't get me wrong. But we are making it one step easier by not complicating the user's experience in their move. Like I said before, I'm just a bug finder. I'm not really a Python 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 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 endMSAccess -user perspective). -Jason</pre> From http[[User://osdir.com/ml/genealogy.gramps.user/2004-06/msg00078.htmlAaronS|AaronS]] 03: <pre>Alex said:  SQLite might be better or it might not33, we haven't tried it. A great factor speaking for BSDDB is that it is supported by a standard Python module, bsddb. 27 March 2009 (UTC)

Don saidCurrent DBI:

'''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.

# it will be very important to use slots 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? --[[User:Dsblank|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. [[User:Bmcage|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. --[[User:Dsblank|Dsblank]] 12:56, 9 April 2009 (UTC)