Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Offline
Re: [RFC] TXP5 Custom Fields Management
I’m testing so-called virtual custom fields, which are actually db functions/queries definable by site admins. Few dummy examples of article custom fields might be
2*ID
REGEXP_SUBSTR(Title, '\d+')and so on. But, actually, one can use more complex queries, referring to the current article/image/etc, like
SELECT GROUP_CONCAT(id) FROM some_table WHERE content_id = textpattern.IDOne of the advantages over php post-processing is that one can pre-filter the items on db fetch, preserving the pagination.
Does anyone see a reasonable objection/usage of it, before too much work is done? More generally, what types of custom fields would you like to see in txp5?
Offline
Re: [RFC] TXP5 Custom Fields Management
Intriguing. How does that affect storage efficiency? Or is this solely for output wrangling? i.e. is the custom field “definition” written when you create the field and stored as, say, a varchar query string, which is then interpreted every time the field is accessed instead of merely fetched and the corresponding global populated?
Also, at the back of my mind, is that one of us (probably me) is going to have to bite the bullet and write a parameterized query / PDO layer to replace all the txplib_db.php safe_*() functions. There are precisely zero PDO/ORM wrappers in existence that aren’t tied to laravel or some other huge framework that’s 10x the entire footprint of Txp. The closest is probably DBAL, if it’s decoupled from Doctrine, so that might act as a starting point. If not, we’ll roll our own.
Anyway, yeah, if we’re adding virtual fields, keep in mind how we’d interface the capabilities with a parameterized query layer. If it’s possible to preserve pagination in fields (and so forth) and we can expose this and other things like cursors in our abstraction layer, then we’ll have world class efficient querying at our fingertips, across a variety of database vendors.
This is a seriously cool idea, btw. I’ll hopefully have some time later today or tomorrow to play with the branch.
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
Re: [RFC] TXP5 Custom Fields Management
Bloke wrote #341058:
Intriguing. How does that affect storage efficiency? Or is this solely for output wrangling? i.e. is the custom field “definition” written when you create the field and stored as, say, a varchar query string, which is then interpreted every time the field is accessed instead of merely fetched and the corresponding global populated?
Yes, we just store the query definition and then retrieve it and plug into cfs population queries instead of SELECT value ... The overhead for functional queries like SUBSTR(Title, 2) is minimal, more complex queries can be more pricey.
if we’re adding virtual fields, keep in mind how we’d interface the capabilities with a parameterized query layer.
We do not PDO for PDO, right? Some queries would benefit from it, but others might stay old-fashioned, especially since only admins can define them.
Offline
Re: [RFC] TXP5 Custom Fields Management
etc wrote #341059:
Yes, we just store the query definition and then retrieve it and plug into cfs population queries instead of
SELECT value ...
Perfect. That’s insanely cool.
We do not PDO for PDO, right?
Well herein lies my beef with PDO. It’s fine, but the stated aims of it being database agnostic are not quite true. Because some databases like SQL Server and Oracle do things differently to MySQL, which has support for stuff that we use where they don’t (like FIELDS() for extracting stuff in sort order, for example).
So moving lock stock to PDO means we gain some support for other DB engines and get rid of all the doSlash() nonsense with parameterized queries, but lose niceties we have in MySQL derivatives because our queries all need to use (e.g. SQL 92) lowest common denominator features. Plus, last time I checked, things like WHERE field IN (?,?,?,?,?,...) isn’t supported, so we still need some PHP wrangling to transcribe such queries into parameterized versions before sending them off to the DB.
It’s a bit of a mess, so I suspect we’ll need either a halfway house approach with a mix of PHP and SQL to maintain our feature set, OR we stick with mysqli and just retool the queries to use parameters.
Factoring an ORM on top doesn’t seem worth the overhead, especially since our table structures aren’t completely 4NF.
PDO seems like it should be the right way to go, but the devil is in the details, and those details sacrifice some of our performance and flexibility. So perhaps sticking with MySQL derivatives and parametrized mysqli is a better option.
Either way, building support in our DB access layer for cool features like cursors and virtual CFs, pagination, filtering, maybe even Views, should be taken into consideration.
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline