Using SQLalchemy in Dexterity schema

How to store dexterity schema fields in MySQL database using sqlalchemy?
Please help with online documentations.

It's been a while, but I thought I'd write down some thoughts here, just in case someone else is wondering about (ab)using Dexterity machinery, ie. trying to use it to manage data in a SQL database.

This might make sense especially for write-intensive applications. For example a logistics or warehousing application that stores information of cargo transfers: It might require storing (and keeping) structured information about hundreds or thousands of cargo transfers per day. Plone could take that for quite a long time, but ultimately the number of stored records will pile up to a point when using ZODB for that would not be a good idea, especially if the data is also extensively indexed.

So, it would be interesting to explore the possibilties of mapping / converting / reflecting the Dexterity schema to SQL table data structure and using the Dexterity add/edit forms for managing the data. I don't know of anyone having done this.

Working with SQL databases using SQLalchemy is not a problem. You might want to take a look at the z3c.saconfig, collective.saconnect and z3c.sqlalchemy packages, and of course SQLAlchemy itself.

As for using Dexterity, it might be possible to reuse the Add/Edit form by overriding the z3c.form getContent method so that it returns the database cursor and primary key of the row being edited, and then use a custom z3c.form DataManager to store / retrieve the data (probably need to parse the Dexterity schema for that, too).

Or something like that.

Having suggested that, it is nevertheless likely that an application with heavy (structured data) write and search requirements might best be developed using something else than Plone.

Dexterity does not provide a storage layer aa we had it in Archetypes...for good or bad...the only halfway working solution is using DataManagers...your implementation will turn into a pile of shitty code but that's how it is. A clear design flaw in Dexterity and I can not image a reasonable clean way how to attach Sqlalchemy. General recommendation: forget it.

-aj

Why would you want to use dexterity for a web app? @petri. Any reuse you get out of the UI will quickly be lost as soon as you want something more custom in your workflow. or UX. The idea of reusing machinery built for content editing for building web apps is a just a bad idea proven wrong again and again in my experience. Whats more, every attempt to put AT data into sql in previous versions of plone never really worked that I could see. For good reason.
Either use the framework elements in zope, z3cforms etc and create a app in code, or use a framework like django or pyramid, or if you need it to be a mini app that is part of a plone site I recommend Plomino. It is built around the idea of create your own web apps inside plone. It doesn't currently integrate with sqlalmchemy but you it would be a lot easier to make it do this than Plone and DX or AT.

AFAIK you can define a factory attribute in the plone behavior registration to change the storage, e.g.: plone.behavior.AnnotationStorage.

See:

I never played a lot with it, anyway I think something similar can be used to reach the goal.

1 Like

@alert thanks for pointing that out. I wonder what hooks (adapters, whatever) that behavior is using to change the storage? I took a quick look at the sources but could not immediately see how it does its magic.

@zopyx for what it's worth, I added an issue for z3c.form for what perhaps might help working with DataManagers and improve the situation a tiny bit. Any constructive suggestions from you would be highly appreciated there.

I guess the magic is done here in the getattr and setattr methods:

I think it is possible to implement a storage that gets and sets stuff from/to a relational DB.

It is possible but you don't want to do it. There were at least two similar implementations of SQL related storages for Archetypes based on setter and getter magic...nothing that worked properly in reality. Not sure how a similar approach should work here in reasonable and half-way generic way. Better perform related RDBMS operation on a intermediate layer like within a browser with explicit operations instead of fiddling around with getattr() and setattr().

-aj

One of these failed experiments coughcoughcough

https://www.andreas-jung.com/contents/sqlstorage-is-dead-long-live-rdbmsstorage-1