Database First (Existing Data)

[Kiwi Chris]

ProcessWire is an excellent framework for building just about anything, with great tools for permissions, input fields, and creating data structures.

With the Combo and Table profields, it's possible to handle just about any data.

These fieldtypes show the potential of ProcessWire to handle SQL tables in a more 'traditional' SQL table format, which leads to one area I wonder might have potential for a new feature.

In addition to ProcessWire, I also do some work with ASP.Net Core, and one of the nice features it has is the ability to work with existing SQL databases, so that you can either build your models in the framework, or use existing SQL Tables.

While working with any SQL data is possible in ProcessWire, it's a lot more hands on, with less built-in support.

InputFields and FieldTypes are separate, and there's no reason why an inputField can't be used for data input for any kind of data, and indeed there are some modules that use inputFields to edit custom table data.

What would be an amazingly useful addition to ProcessWire would be a module that allows mapping of inputFields to fields in an arbitrary SQL table without modifying the structure of the table itself.

This is something the open source Directus project does by using metadata tables to store data about to display input for existing SQL tables, without touching their structure, but documentation isn't great for that project.

I think ProcessWire has all the plumbing in place to enable this kind of functionality, and the Combo fieldtype gets pretty close, but still requires the combo table records to be associated with a ProcessWire page.

Obviously, existing SQL tables won't automatically map to pages, which means directly accessing them on the front-end via URL automatically isn't possible, but often this isn't required as they may be for backend use only, or for consumption within a ProcessWire page. The recent support for URL/Path hooks though, means that if there is a need to directly access existing SQL on the front end via URL, it should be possible to create a hook to do so.

I think adding the ability to create backend data entry for existing SQL tables would fully complete the ProcessWire philosophy of getting out of your way and not making assumptions about your content.

It's currently already best of class in terms of doing this regard to front end presentation, but it's still a bit opinionated about data structures at the back end. This is absolutely fine if you're building a web app from scratch, and works really well in most cases, but there are times where being able to quickly incorporate existing data structures would be useful.

To be clear, this isn't a replacement for the existing pages model, as that would be a huge and unecessary task to completely re-engineer ProcessWire, but rather an enhancement that can sit alongside all the good stuff that's already in ProcessWire so that it's possible to get ProcessWire to handle existing SQL data tables on the backend as neatly as it already does with its own data.

 

 

[Kiwi Chris]

Further to this, I've posted a feature request to Github, for a new method getColumns in WireDatabasePDO class, as this would enable:

$database->getColumns($table) which would be helpful for getting a list of custom table fields and their types.

there's already a getTables method and tableExists method, also a columnExists method, but currently no getColumns.

I might be able to have a go at building a module myself to map custom table columns to inputfields, and of course I could write a method myself, but if other people are accessing custom tables, it would seem to make sense to have a getColumns method in the core. 

Copying the columnExists method, removing the WHERE condition and returning an array of the results rather than a boolean is probably all that's needed to implement a getColumns method.