Database Table Editor

Oho guys!
I’d like to come back to the difference between content (document) and data management.
If you become involved into the latter after having mastered the first quite well, I fear for you (and MODX) there will be no easy way out!
I’m in that business for more than 10 years, and one of the first signals of failure has always been the attempt to create ONE tool for ALL. The complexity does not derive from technology, it derives from the diversity of purposes.
Just look at a tool like "servoy"
(gotta google it, and there are lots of similar data-management-tools locked up in all those always underestimated side-effects),
which is fairly clever, and you’ll see what you’re going to get involved in.
Finally, I think you have to decide between one of the two ways: data- and business-logic OR content (document) handling.
You will become a Nobel-prize candidate if you manage both in one, easy to use software. I think it’s not only impossible, but will also damage the great results achieved so far with MODx, if you try to join this "we can do everything" approach.

excuse me, but that’s my personal view - and it’s not a newbie’s one...

PS: making a table-structure editable by a webuser is not that difficult at all. What is difficult is to prevent harm and to prevent this type of users/usage from screwing everything up!
Anyone who has the permission to edit datastructures should have passed a MyPHPAdmin (or whatever tool you like) exam as his first test. And for the sake of business and working systems there should be a lot of other tests/exams included!

PS: making a table-structure editable by a webuser is not that difficult at all. What is difficult is to prevent harm and to prevent this type of users/usage from screwing everything up!

I’d see it more as an extension of content management capabilities rather then a full blown data management application. I agree that it would be detrimental to the product if MODx tried to emmulate a tool such as phpMyAdmin but I don’t think the aim is so much for a webuser to edit table structures but simply to be able to edit data in the table in an understandable way. For that there’s no need to touch the structure at all besides of having some way to read the structure (which MODx already has) for configuration purposes.

I’ve just been asked to enhance a MODx site with a kind of "product catalogue". They don’t want a full blown e-commerce system but they do want people to be able to browse through their collection. Now, there’s a few possibilities like incorporating shopping cart software without the shopping cart, do it the MODx ’document based’ way or have a possibility (through a module) to edit the content of a database table directly. With more then 3000 items I’d opt for the latter myself.

Quote from: TobyL at Jul 16, 2006, 08:34 PM

like incorporating shopping cart software without the shopping cart, do it the MODx ’document based’ way or have a possibility (through a module) to edit the content of a database table directly. With more then 3000 items I’d opt for the latter myself.

Yes, why not use a shopping cart solution? A cart is also useful when it’s not about buying, but simply collecting things (documents, descriptions, whatever).
Displaying/Editing a record would not be that difficult in the first place, but it might get difficult if you have to manage different users and their write-permissions. You could also write a module which permits to edit the table’s content in the manager, but right now this would mean one module per table. Not very elegant, I admit it...

Quote from: ppaul at Jul 16, 2006, 06:17 PM

... I think you have to decide between one of the two ways: data- and business-logic OR content (document) handling.

The way I see it, any business logic is up to the individual - although I’d include in that the installing of plugins/modules such as a shopping cart, so there’s a good chance a lot of MODx users would be using the same logic.

MODx already has the ability to use non-document data on a webpage - to transform data into content. There are already people connecting data to MODx. A generic Database Table Editor simply allows that "external" data to be edited by Manager users - it doesn’t change the focus of MODx, and need not change how documents are created and edited.

In fact, if this got into the core, I doubt that the default installation of MODx would have any editable data. It’s purely a common tool for whatever additional tables anyone needs to use. Any other means of data entry/editing could be used. But hopefully a standard tool would mean people could get solutions together quickly, and attract people who are looking to build applications.

I agree that one tool cannot do justice to both content and data. I also believe that the focus of MODx should be on documents. However, for MODx to work as a framework, the Manager needs to be a swiss army knife, with multiple tools in one device.

I looked at Servoy. It’s an application development thingie. I just don’t see the comparison AT ALL. MODx isn’t competing with Servoy, and a quick table editor wouldn’t move it anywhere near that direction. There are however plenty of "applications" that could be built with Servoy that would be better as a web application built with MODx and PHP. Why not a "we can do everything" approach? If you can write it in PHP you can integrate it with MODx. A generic table editor doesn’t mean MODx makes programming any PHP/MySQL application easier, it’s just one less thing to write.

Quote from: PaulGregory at Jul 17, 2006, 10:08 AM

I looked at Servoy. It’s an application development thingie. I just don’t see the comparison AT ALL. MODx isn’t competing with Servoy, and a quick table editor wouldn’t move it anywhere near that direction. There are however plenty of "applications" that could be built with Servoy that would be better as a web application built with MODx and PHP. Why not a "we can do everything" approach? If you can write it in PHP you can integrate it with MODx. A generic table editor doesn’t mean MODx makes programming any PHP/MySQL application easier, it’s just one less thing to write.

Hi Paul G:
How does this fit together?:
1. no comparision/competition to a development thingie, but
2. a "Why not" to the "we can do everything" approach, which is, exactly, the development thingies thing.

And back to the table-editor issue: The challenge is that for real integration the table-data must be managed like the documents. Users, permissions, workflow etc. And typically, some of these permissions might depend on the content of the table: if, for instance, you have one user responsible for "cars" and another one responsible for "planes", both stored in a "facilities-table" for instance. And business logic gets also quite close. The consequence will most likely be that MODx will not be a CMS anymore, but a PHP/MySQL framework. And with frameworks, the well known problem is, that for each thing you have less to write, there is a bunch of things more to read and to learn. The datagrid-widget implicitly defines a well thought interface: a select-query (for a foreign table) can be handled like a document, since it only displays data. An editing-procedure (insert, update, delete) cannot. There’s a lot of wisdom in making and keeping that difference...

A common, but dangerous way out of this dilemma is called "abstraction", and I fear this is what Jason has in mind with this xPDO stuff.
But to get it more abstract you have to consider the current functionality of the MODx manager as just one of millions of possible logical behaviours of a "generic" manager. In most cases, the abstraction/wrapper approach just doubles or better mirrors the complexity of the underlying technology, sacrifices performance for a virtual, but not a real power of the system. The DBAPI in MODx is just a very small, very tiny example of that: it provides a layer between MODx and the SQL-engine, but it doesn’t make things easier or faster. For a thing as simple as finding out the max value of a given field you have to sidestep and execute your query outside the DBAPI. On the other hand: it took me hours to find out that getDocuments() will only return published docs. If I had done it directly with SQL, i.e. without DBAPI, I could have saved a lot of time... Those are the dangers of "abstraction". Any SQL-"abstraction" will be more complex and difficult to use and less performant than the plain old, incredibly powerful SQL itself.

Just my cents. Sorry for this long post!

Lot’s of good debate here. Just want to offer this...

Content in MODx is data, so we are already in the business of data management. The efforts being discussed here are already in progress and by no means am I attempting to solve everything with one tool. But, the future direction of MODx in my vision is one that includes an object-oriented API mapped to relational db structures (abstraction from SQL and other business logic implementation details), the ability to deploy and migrate core or custom data against various database platforms, rapid prototyping features similar to the approach RoR takes with it’s ActiveRecord facilities, and other framework features.

Consider the following scenario: you need to build an add-on that requires a new table to store data. You can manually create it, author and manage all the queries and business logic, and run with it; or you can define an object, generate the basic CRUD code you need to work with the resulting table data automatically, easily add complex business logic into the generated classes, and use a consistent API approach to working with both MODx itself, as well as the extra classes/tables you can easily define and manage in the framework. When changes occur, you can regenerate the object metadata, possibly refactor a function of the class with some business logic encapsulated, and continue on, quickly, and easily. I’m using this already on some very large projects and the result has been my ability to respond to last minute changes to the data model within 15 minutes and one or two lines of code.

Yes Jason, this is ultra-cool stuff, no question.
But one has to be an "uber-geek" like you to use it - at least compared to what a common MODx user has to accomplish right now to get his site up and running.
I know some of these relational/objectional mapping stuff, together with interface-builders, datasource-integrators, abstractions-layers etc. That’s why I mentioned servoy, also the monster "content-frameworks" like Vignette promise this. But in the end: they do not make it easier but harder than to develop individual solutions. Their "wrapper/integration" levels are mostly crap, compared to the underlying tech they hide away.

I fear two things: it will take more than a lifetime to make MODx work like those tools.
And, I repeate myself, I know : It will not be a CMS anymore, but something veryvery different.
My last point: where’s the benefit? We have this type of tools already, you mentioned RoR yourself. If I’m willing and able to deal with this kind of project/task, I can start to "RoR".

Quote from: ppaul

How does this fit together?:
1. no comparision/competition to a development thingie, but
2. a "Why not" to the "we can do everything" approach, which is, exactly, the development thingies thing.

I did slip in the difference - with MODx and PHP you can do anything. With just PHP you can do anything. With just MODx, you can’t. Therefore with both you can do anything.

Servoy creates applications and you need to use *their* web client. And it’s all commercial.

Quote from: ppaul at Jul 17, 2006, 11:28 AM

The challenge is that for real integration the table-data must be managed like the documents. Users, permissions, workflow etc.

Well, that would be nice but it is NOT essential. Imagine a Template Variable that was a dropdown of "Apples", "Oranges", and "Bananas". Currently Manager users with the right permissions can add and edit those choices directly in the "Input Option Values". So if the same users can use a table editor to add to a custom fruit table, it just makes things neater. Yes, for many applications, workflow and permissions issues mean that it is easier to code up a custom editor. But for many web applications, a simple table-editor *is* useful; and is potentially also useful while a custom editor is being written.

Quote from: ppaul at Jul 17, 2006, 11:28 AM

And typically, some of these permissions might depend on the content of the table: if, for instance, you have one user responsible for "cars" and another one responsible for "planes", both stored in a "facilities-table" for instance. And business logic gets also quite close.

Very true. It would be good to be able to have multiple config files for one table. In my mind anything approaching business logic is in config files. So it is *enabling* logic, not getting bogged down in it. In theory one user could have access to cars, another planes. The config file could just limit what is displayed - ie "SELECT ... WHERE vehicle_type = 1". Also, a template for new items would enable a custom Add New button that created a row with the "Plane" id. And the user’s id in "created_by". The Database Table Editor need not be defined by tables - there could easily be a list of editors configured: "Car Editor", "Plane Editor", "Boat Editor".

I’m not sure what the datagrid-widget has to do with the Table Editor, as I’m not handling this as parts of a document. May I remind you that I’m discussing a proposed backend feature for MODx admins, not something that could be added to a public MODx website.

Quote from: ppaul at Jul 17, 2006, 11:28 AM

The consequence will most likely be that MODx will not be a CMS anymore, but a PHP/MySQL framework.

What does it say underneath the MODx logo at the top of this page? I think you’ll find that MODx is intended to be both. Making data management easier does not in any way affect usage of MODx purely as a CMS; you will not need to learn anything about the Database Table Editor if you do not wish to edit foreign tables.

And to your latest input:

Quote from: ppaul at Jul 17, 2006, 12:28 PM

But one has to be an "uber-geek" like [OpenGeek] to use it - at least compared to what a common MODx user has to accomplish right now to get his site up and running.

To be frank - in my mind, one has to be an uber-geek to need it, and ideally an uber-geek creating a feature for someone not particularly geeky at all. If you’re not an uber-geek, you don’t need to touch the table config at all. That is, until someone releases an easily installable plugin that uses an extra data table editable with Table Editor, in which case all you’d see of the config was a file to FTP. An example off the top of my head: a Swear Word filter for usercomments. You wouldn’t need to care about the config stuff in the background. You’d just go to Data > SwearWords and add new words and substitutions. In the end, that is going to be less geeky than editing the snippet. But fundamentally, if you have no data that isn’t content, you do not need to use it at all.

Quote from: ppaul at Jul 17, 2006, 12:28 PM

But in the end: they do not make it easier but harder than to develop individual solutions. Their "wrapper/integration" levels are mostly crap, compared to the underlying tech they hide away.

MODx does not hide away PHP. So if you find the framework gets in the way, don’t use it.

EDIT: Actually you added a lot to the previous post that makes it clearer what you mean by crap; and also made it clearer that your concern is the shift to xPDO rather than just the Database Table Editor. Yes, languages that have been around long enough to get a full shelf of documentation are easier to use.

OK, I’m convinced.
A table-editor as you describe it makes sense. We had another topic in this thread with this ORM-abstraction stuff.
If an evolutionary path can be found to get from here to there and it all works as you and Jason describe it - great!
And maybe the ORM-stuff is a chance to clean some things up, I guess those things are mostly etomite-legacies.
An access-layer, for instance, would be great. Right now I have to choose between two evils: using the DBAPI, but not getting what I want, or not using the DBAPI, but loosing access-control. Maybe these architectural issues should be covered elsewhere.

Thanks for this discussion!

Quote from: TobyL at Jul 14, 2006, 10:23 AM

I’ve thought about using a plugin replacing the RTE but I sofar fail to see how that might work. I’ve been working on a table editor module for 0.9x but haven’t been able to finish it yet.

Hi. I didn’t pick up on this earlier. How is this project going, and what is your target featureset? I saw you mention configuration over on the Custom Permissions plugin thread.