Re: [Gramps-devel] Storage Abstraction: Lessons from PostgreSQL Enhanced + GrampsWeb Integration

[David S. <st...@pr...>]

Hi,

it is indeed hard to read, here a screenshot from my e-mail client: https://postimg.cc/SJqBTRKR

A general comment (without having deciphered your whole message, but possible going in the same direction):

Yes, the way Gramps interacts with databases and the way it relies on a file system shows that it was designed as a desktop app. The fact that we are still able to use it for other purposes is remarkable, but it's true that it requires some workarounds. Redesigning it from scratch - especially with today's tools like SQLAlchemy - one would probably make different design choices.

But rather than thinking about this from a complete reimplementation perspective, I think we have to acknowledge that we have an existing code base, we have users, and any drastic architectural change requires a migration path. When this is worth doing is a matter of tradeoff.

Cheers,
David






Greg Lamberson via Gramps-devel <gra...@li...> schrieb am Donnerstag, 14. August 2025 um 13:41:

> 
> 
> It's a little less than optimal, but i read it fine when it came to me.
> I expected that anyone on this list would 1. Be able to read markdown (there's no html) and 2. Be used to dealing with the limitations of such a limiting platform.
> I can certainly send it again and formatted in a nice flat text only structure. Do you think I should?
> Presumably it'll be formatted better in the GEP.
> 
> 
> Yahoo Mail: Search, Organize, Conquer
> 
> On Thu, Aug 14, 2025 at 2:28 PM, Tim Lyo...@gm... wrote: Note that the sourceforge lists don’t support HTML messages, so this doesn’t make much sense as it is.
> 
> 
> 
> 
> > On 14 Aug 2025, at 11:34 BST, Greg Lamberson via Gramps-devel gra...@li... wrote:
> > 
> > Hello, I posed this a couple days ago at the Gramps Development board at Discourse and now duplicate it here. This will form the basis for a GEP once I figure out how to do that:
> > Dear Gramps developers,
> > I recently completed integrating PostgreSQL Enhanced with GrampsWeb. The exercise required 12 (!!!) workarounds to handle architectural assumptions about storage. I’m sharing this as a concrete example of why we need to revisit the Gramps storage layer architecture.
> > Technical FindingsFilesystem Coupling in Coregramps/gen/db/dbconst.py:73
> > DBBACKEND = "database.txt" # Hardcoded assumptionThis constant propagates through the entire stack. GrampsWeb’s is_tree() function literally checks for this file to determine database existence. For a pure-database backend, I had to create fake filesystem structures.
> > Missing Base Class MethodsGrampsWeb calls these methods not defined in DbGeneric:
> > get_transactions(page, pagesize, **kwargs)set_metadata(key, value)get_metadata(key, default)Every backend implementing web support has to add these independently. There’s no interface contract.
> > Static Backend Registrationgramps_webapi/dbmanager.py:44
> > ALLOWED_DB_BACKENDS = ["sqlite", "postgresql", "sharedpostgresql"]Adding a backend requires patching source files. We should be using entry points or a registry pattern.
> > SQL Dialect Issuespsycopg2 vs psycopg3 incompatibility in PostgreSQL backends:
> > -- psycopg2WHERE x.name IN %s
> > -- psycopg3 WHERE x.name = ANY(%s)Each PostgreSQL variant handles this differently. We need dialect abstraction. Personally I LOATHE psycopg2 due to many long years of pain in implementing its use. You won’t find it in any code I use. It’s a database driver from 2008 replaced in 2019. There’s frankly no reason to use it. And getting it to work in the real world is usually painful due to its slightly different syntax and its age, making finding drivers a pain with most implementations.
> > Architectural ImpactBut this isn’t PostgreSQL-specific. Any non-filesystem backend faces these issues:
> > Backend proliferation: We have 3+ PostgreSQL variants because each developer solves these problems differentlyCode duplication: Same features reimplemented in each backendTesting complexity: No interface contract means no meaningful mocksFeature gaps: Backends implement different subsets of functionalityThe Deeper Issue: DBAPI ArchitectureThese problems stem from fundamental issues with the DBAPI layer:
> > Not Actually DB-API 2.0 Compliant: Despite its name, DBAPI doesn’t follow Python’s DB-API 2.0 specification (PEP 249)SQLite-Centric Design: The entire abstraction assumes SQLite patternsFilesystem Coupling: Directory-based initialization (_initialize(directory, username, password))No Connection Abstraction: Single connection architecture prevents pooling, replicas, or scalingAs documented in previous analyses, DBAPI is actually three things mixed together:
> > SQLite-specific implementation details (should be in SQLite class)SQL database abstractions (should be in a SQL base class)General database operations (already in DbGeneric)The Storage Layer ChallengeThe current inheritance hierarchy creates cascading problems:
> > DbReadBase + DbWriteBase ↓ DbGeneric ↓ DBAPI ← [SQLite assumptions baked in] ↓ SQLite / PostgreSQL (forced to work around DBAPI)Meanwhile, sophisticated patterns exist but are disconnected:
> > Proxy pattern (PrivateProxy, LivingProxy, FilterProxy)Transaction management (DbTxn)Multi-tenant support (SharedDBAPI)These need to be preserved while fixing the storage layer.
> > Architectural DirectionWhat we need:
> > 1. Clean Separation of ConcernsMove SQLite specifics out of DBAPI into SQLite classCreate proper SQL abstraction base classEnable non-filesystem backends without workarounds2. Modern Storage PatternsConnection string support (not just directory paths)Connection pooling capabilitiesSupport for cloud databasesNative JSON types where available3. Backward Compatibility Through LayeringFollowing the “Layered Core with Compatibility Shell” pattern:
> > Existing code continues to work unchangedNew capabilities available through opt-inProgressive migration without breaking changesThe goal isn’t to prescribe a specific solution, but to enable the flexibility that modern genealogy software needs while preserving Gramps’s stability and existing patterns.
> > Why This Matters NowThe genealogy landscape is evolving:
> > Multi-user Collaboration: Families want to work on shared trees simultaneouslyCloud Storage: Users expect data accessible from anywhereScale: Large trees need real database features (indexing, query optimization)Modern Backends: Graph databases for relationships, time-series for eventsThe current architecture blocks all of these use cases.
> > Evidence from the FieldMy PostgreSQL Enhanced integration is just one example. We’re seeing:
> > Multiple PostgreSQL variants (postgresql, sharedpostgresql, postgresqlenhanced)Each solving the same problems differentlyNo standard way to support cloud databasesRepeated workarounds for the same architectural issuesMoving ForwardThis isn’t about one backend or implementation preference. It’s about enabling Gramps to support the storage systems users need, whether that’s:
> > Local SQLite for individual usersPostgreSQL for concurrent accessCloud databases for web deploymentGraph databases for relationship analysisThe 12 workarounds I needed demonstrate that current Gramps architecture is blocking innovation. We can fix this while maintaining full backward compatibility through careful architectural evolution.
> > For DiscussionRather than proposing a specific implementation, I’d like to discuss:
> > Do we agree that storage layer abstraction is needed?What are the non-negotiable compatibility requirements?How can we enable modern backends without disrupting existing ones?What’s the right timeline for architectural evolution?The PostgreSQL Enhanced integration works - but it shouldn’t require 12 hacks. Let’s discuss how to remove these barriers for all backend developers.
> > Working code and detailed analysis available for review
> > 
> > Greg Lamberson
> > lam...@ya...
> > -------------- next part --------------
> > An HTML attachment was scrubbed...
> > 
> > _______________________________________________
> > Gramps-devel mailing list
> > Gra...@li...
> > https://lists.sourceforge.net/lists/listinfo/gramps-devel
> 
> 
> 
> -------------- next part --------------
> An HTML attachment was scrubbed...
> 
> _______________________________________________
> Gramps-devel mailing list
> Gra...@li...
> https://lists.sourceforge.net/lists/listinfo/gramps-devel