Database Table Editor

[Guest]

Welcome, Guest. Please login or register.
Did you miss your activation email?

1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[en8scl]

I have just discovered how powerful the @Bindings can be for connecting to the database and love this function.  However it occurs to me that if I were to set up a site which pulls content from the db into datagrids I would want my non-techie editors to be able to change the content in the database tables which are generating the dataset in the same manner they would edit any other content.  Obviously I would want to be able to specify which tables they have access to so that they were not able to corrupt any of the other tables in the DB.  Would it be possible to create an 'ms excel' style editor which would let the users create rows, delete rows and alter content of rows.  (think it would be best if they weren't able to create new or edit field names though in order to save confusion and possible breakdowns in functionality)

Perhaps something along these lines http://www.turboajax.com/turbodbadmin_demo/

[PaulGregory]

Such a feature would indeed mean that more structured "active" data could be used; while MODx's content and TVs are flexible, there are still many times when one wants to have a dedicated table that will need adding to and modifying - either because it makes for better design, or because there is a legacy data table from another application.

Ideally a MODx table editor could be configured to cross-reference between tables, so that cities had a drop-down for country for example, rather than just a number input field.

Frankly, I wouldn't mind if the _configuration_ of Table Editor was extremely techie, if by doing so there could be a safe and simple way for non-techie users to change data.

[davidm]

Interresting concept but how does TurboAdmin differs from phpMyAdmin ? It's a layman's question, I truly don't see it so please enlighten me

It sure is easy to build a database, but the interresting thing is having the ability to extract, filter data without having to build specific MySQL queries, which is already possible with the @SELECT binding... Now what I'd like is a more "natural" language than MySQL to do extractions and filtering, and how do we accomplish that ? Forgive me if I am wrong but is not what the dbAPI is for ? MODx has one and if I understood correctly it will become more powerful with xPDO. Now it would be more technical than what you have in mind, but I kind of agree with Paul this kind of thing has to be handled carefully, might not be a good idea to let any user edit the data structure...

[OpenGeek]

Well, since davidm let the cat out of the bag, I'll go ahead and bring this up here and follow up with a post in the MODx team blog as soon as possible, because these issues are exactly the motivation for some design changes in MODx 1.0 and all of the motivation behind the design of xPDO.

You might be interested in looking at this project I am working on, which will be powering the upcoming MODx 1.0 core framework.  The xPDO (or OpenExpedio) project is my attempt to fill a gap I found in PHP, which is being able to provide a light-weight OR/M framework that can generate an efficient object-oriented API from a description of the model, and manage all aspects of it's relational data source, including automatic-table creation and tools for easily managing data model changes; one that can be deployed on PHP 4, but take full advantage of PHP 5 (i.e. PDO) when available, and one that can work with other database platforms besides MySQL.  It is still in what I would call a beta state, and by no means a complete OR/M solution yet, but it is being used in several client deployments and is definitely usable/testable, and has turned out to be of great help in managing a recent project I was responsible for involving a data model of some 49 different custom objects/tables, including integration with the MODx user/permissions system.  I can make a change to my XML model and regenerate all the classes/maps in half a second, so it's also wonderful for rapid prototyping, and  the consistent API handles all CRUD operations for you; the only additional code and/or SQL required in the generated classes is for handling complex business logic.

It is based on research I've been doing over the last year, and prototypes of the new MODx I've been building over the 6 months, with several existing PHP object-relational tools, including Propel, EZPDO, Metastorage, Doctrine, and a host of others.

I also have versions of my current MakeTable and MakeForm classes from the MODx add-on repository in xPDO, that work with and take advantage of the new XPDO-generated API's.  These are great for quickly building table views and forms for managing the data or building complex custom views into the data; and the consistent OO API makes it possible to write very generic functions for just about anything you want to accomplish.  Complete metadata maps of the objects round out the features, and should make writing a table editor script for it a piece of cake.

All this said (and I have plenty more to say on the subject, don't worry ), management interfaces and wizards for creating/editing xPDO objects/tables, generating xPDO classes, and managing objects created with those classes, are absolutely needed for xPDO, and I'd love to have some help making that happen.  Anyone interested in getting involved, just let me know...

[davidm]

Well, since davidm let the cat out of the bag...

Ooops, I hope the cat didn't escape too early, I sincerely thought you had brought this up in the Developpement and Coding forums...

Nice read, BTW

[TobyL]

Lots to look forward too

Now if I can add my 2c worth, for me the key phrase is

...I would want my non-techie editors to be able to change the content in the database tables which are generating the dataset in the same manner they would edit any other content.

What I would really like to see in a future version is that the editing area of a document is more customisable. What I mean is that if for example I do have a page which pulls data from an external database table, when editing that page the right pane shows an appropriate table editor (instead of having to edit that data outside the document via a module or similar).

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.

Hope I'm being clear...

[PaulGregory]

@davidm - Yes, that TurboAdmin does look like phpMyAdmin only with Ajax, and is inappropriate as an editor because it naturally knows nothing of the relations between tables. The only benefit in duplicating that within MODx would be the time saved flipping to our favoured admin app. Admittedly in some cases (eg where access is only via Plesk and you need to log in to that and find your domain and click on your database and then click on webadmin), this is a massive timesaver!

I think en8scl was after something "excel like" and was only pointing out the look and feel of the Data view (which does look nice) - I don't think he actually wanted a full database editor, he just wanted editors to be able to edit a few tables within MODx.

Yes, as davidm reminds us, the dbAPI lets us build database apps, and the @binding lets us use any table to supply data. This is precisely *why* a generic configurable table editor in the Manager would be so useful. For content, you can have custom "add blog entry" / "add comment" snippets on the frontend if you wish, but most pages will be created and edited using the full, flexible, generic document editor on the backend. For data, we can add custom input and edit facilities to the frontend, but for generic data editing we have nothing. OK, *I'm* perfectly happy using phpMyAdmin, but this is far too raw to let non-techies near.

Sure, the powerful features of dbAPI (and in the future xPDO) let the original poster of this thread build a custom data-editor for his editors to use, but it would still be more desirable to have a generic Database Table Editor.

However, having pointed out this thread during a discussion on the best way to scale ShopX, an eager coder called humaniweb has decided to try hacking together that specific editor so it can be used alongside MODx. For his purposes (maintaining characteristics of a product such as city of origin), this will be fine.

But there is scope for much more:

Right now, the ideal Generic Database Table Editor would be a Manager plugin which itself had a "plugin" config file for each table that was to be edited. There is no need for the Table Editor unless you have added a table to MODx, so the requirement for a techie to write a config would not be an issue. Where a common table is being used as part of another MODx extension - say for example an ecommerce extension that worked with an existing osCommerce database - the authors of the extension could make db editor config files available.

The config files would not be dissimilar to a series of TVs - each field could be ascribed a data type and potentially a binding to another table for drop-down selection.

There would be both a grid view of x rows at a time, and a single-entry view.

The editor would *not* enable structural changes.

The difference xPDO makes
Well, primarily the difference is that it enables MODx to move into data management rather than just content management, and delivers on the promise of a framework. This will make a Database Table Editor easier to write, but doesn't currently fulfil the desire for one - and as OpenGeek recognises, the need is increased. However, there seems a high chance that the focus would be on programming a table editor for xPDO, which could then be integrated with either the MODx manager or a MODx site, as appropriate.

Hmmn. I think OpenGeek will be getting a PM. This looks like exciting times.

[OpenGeek]

Ooops, I hope the cat didn't escape too early, I sincerely thought you had brought this up in the Developpement and Coding forums...

Nice read, BTW

Not too early at all; actually the timing was impeccable.

The difference xPDO makes
Well, primarily the difference is that it enables MODx to move into data management rather than just content management, and delivers on the promise of a framework. This will make a Database Table Editor easier to write, but doesn't currently fulfil the desire for one - and as OpenGeek recognises, the need is increased. However, there seems a high chance that the focus would be on programming a table editor for xPDO, which could then be integrated with either the MODx manager or a MODx site, as appropriate.

Indeed, the need is increased greatly.  The good news is, if we build a generic management interface that works in the current MODx for xPDO, since the manager in 1.0 can be built and deployed in much the same way as the current MODx front-end, we can re-use whatever makes sense to for building appropriate parts of the 1.0 manager.

Hmmn. I think OpenGeek will be getting a PM. This looks like exciting times.

Great, I could use some additional brain- and man-power on this stuff; can't wait to hear from you or anyone else interested in contributing in any way, to xPDO, or the MODx 1.0 efforts.  And yes, I am quite excited about this myself; being the uber-geek that I am.


Cheers

[davidm]

Thanks Paul, you helped me understand what a database table editor would mean, and man this is exciting

The good news is, if we build a generic management interface that works in the current MODx for xPDO, since the manager in 1.0 can be built and deployed in much the same way as the current MODx front-end, we can re-use whatever makes sense to for building appropriate parts of the 1.0 manager.

The last bit about the 1.0 manager is quite an exhilarating thought for me !!!

[humaniweb]

The config files would not be dissimilar to a series of TVs - each field could be ascribed a data type and potentially a binding to another table for drop-down selection.

Wow... well. When integrating TurboAdmin i wasn't thinking about hacking it to enable @binding with drop down menus...
I was just thinking about a simple table editor but you're right it would be so cool.

But i'm excited by the idea.

OpenGeek : do you need some help ? I still have internet access for a few days before disappearing for 3 weeks. I'm not a great developper. Before i would be of no help for you but if you need i can give some of my time to help with this project of TableEditor...

Thanks all for this thread,
Julien.

[ppaulousek]

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!

[TobyL]

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.

[ppaulousek]

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

[PaulGregory]

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

[ppaulousek]

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!