custom table read/write

OK I have read this page:
http://svn.modxcms.com/docs/display/revolution/Using+Custom+Database+Tables+in+your+3rd+Party+Components

However I don’t really want a whole 3rd party component.

I have a simple table with a hand full of columns. Under the old modx it was simple, you created the table and then just started refering to it via things like:

$modx->db->query(sql);
$modx->db->getRecordCount(result);
$modx->db->getRow(result);

It was trivial and easy.

Is there any way to do this now other than:
1) create a component structure in my core directory
2) create an xml schema
3) create a build.config.php file
4) create class templates

Is there no trivial way to do this anymore?

Not knowing all the reasoning behind it it seems overkill when you are not interested in building packages for others, just a simple 20 or so line snippet to do some basic data manipulation on a custom table.

From the original doco link:

...You’ve got some data that uses a table in your MODx database, but you want a way to use xPDO’s object model to access it....

No, I have some data that I simply wish to do a quick sql query on, not use the xPDO’s object model to access it.

Is it still possible?

Oh and if there isn’t then how do I access the data? How do I do an sql query on it?

That examples shows storing via:


But what if I want to retrieve everything where city == ’London’ for example? Sql this is trivial, the original modx this was trivial, bu I don’t see how this is implemented under this process and all we have in the doco is a coming soon ....

Or more precisely if I want to use something like soundex, eg
WHERE `town` SOUNDEX "Sydney"
in my sql query.

I could easily do that in the old modx via $modx->db->query(sql);

Sorry, rather confused over what used to be a simple process.

what I can see, you can still use the old db-api-functions in revo, but:

/**
* Legacy Database API class for MySQL.
*
* @deprecated MODx Revolution - Use modX and xPDO functions instead in order to
* take advantage of support for any database platform with a PDO driver, as
* well as to take advantage of xPDO advanced caching features.
* @author Raymond Irving (June 2005)
* @author Jason Coward <[email protected]>
* @package modx
*/

I’m wondering too how we can do for example this 3-liner with xpdo functions:

Hello scarfy96,

Edit: You’ll have to create a schema to access your database.
I think that there is an xpdo function to reverse engineer your custom table so it can generate the map for you without create the xml schema but i didn’t find it in the doc.
See this topic : http://modxcms.com/forums/index.php?topic=40174.0

See the two code exemples below (Not sure about the second):



For Bruno17

Quote from: Bruno17 at Oct 26, 2009, 07:36 AM

what I can see, you can still use the old db-api-functions in revo, but:
...

I’m wondering too how we can do for example this 3-liner with xpdo functions:

<?php
$rs=$modx->db->query('select * from'.$modx->getFullTablename('custom_table'));
$rows=$modx->db->makeArray($rs);
print_r($rows);
?>

Well, there are a lot of ways, though some have more advantages than others; this one is the quickest to write and requires no model to be built for your custom table:

xPDO::query() is nothing more than a wrapper for the PDO::query() function, so if you understand how to work with PDO, you can use xPDO just like you would PDO. However, xPDO starts where PDO leaves off since it is only the database access layer and nothing more.

xPDO is for building complete class models of your data structures that automatically gives you full CRUD access and a whole range of built-in functions to quickly and easily develop a complete OO API for your custom tables. It also provides two classes for every table when generated, one for database agnostic functionality (known as the domain model) and one for database-specific functionality and optimization (the table model). Once you quickly and easily generate your classes from an xPDO schema (which can also be quickly, and easily reverse-engineered from existing database tables), you can then imitate the above logic without writing any custom code in your classes (or any SQL):

If you want to use the features of the object, including access control, CRUD, and access to custom functions you define for your objects (i.e. business logic), then this would be the way to go; if you want to bypass all of that, then you can use the PDO function wrappers to directly access your custom table.

Thanks all.

All working, much appreciated.

Thanks for clarification, but when I try



I get:

Fatal error: Call to a member function fetchAll() on a non-object in E:\xampp\htdocs\revolution_4\core\model\modx\modscript.class.php(96) : eval()’d code on line 7

XPDO is realy great for bigger projects, I think, but IMO the old db-api-functions are easier to understand and to use for fast&easy database-manipulations and non-hardcore-coders.
Please do not remove them on future releases of revolution!!

[Edit]
Ok, this would work, but isn’t $modx->getFullTablename also deprecated?

The only problem with the example I provided was I changed the rs var to pdoStmt in one spot but not in the other. And if you want to use the table prefix of the MODx model, you should create classes for your custom table. Then using $modx->getTableName(’myCustomClass’) replaces getFullTableName(’table_name’).

XPDO is realy great for bigger projects, I think, but IMO the old db-api-functions are easier to understand and to use for fast&easy database-manipulations and non-hardcore-coders.
Please do not remove them on future releases of revolution!!

Sorry, but they are definitely going away, and tbh using PDO is just as easy or easier than learning different database extensions like mysql_ or pgsql_. The whole point of Revolution is to make MODx database agnostic and allowing users to write SQL in API functions without some kind of abstraction layer for database-specific code is not going to work. I want to discourage the use of SQL in component code at all. The only place it should appear are in the database-specific table classes you generate for a table with xPDO.

If you really don’t want to learn/use ( x )PDO in your MODx components, then may I suggest Evolution.

Quote from: OpenGeek at Oct 27, 2009, 02:05 PM

XPDO is realy great for bigger projects, I think, but IMO the old db-api-functions are easier to understand and to use for fast&easy database-manipulations and non-hardcore-coders.
Please do not remove them on future releases of revolution!!

Sorry, but they are definitely going away, and tbh using PDO is just as easy or easier than learning different database extensions like mysql_ or pgsql_. The whole point of Revolution is to make MODx database agnostic and allowing users to write SQL in API functions without some kind of abstraction layer for database-specific code is not going to work. I want to discourage the use of SQL in component code at all. The only place it should appear are in the database-specific table classes you generate for a table with xPDO.

This is an important point - as we progress to 2.1 and beyond, the code will (and should) become increasingly abstracted to allow for Revolution installs on mssql, pgsql, and other database extensions. There is a huge userbase of users that cannot use MODx currently because of the MySQL limitation. We will be pushing for abstraction; that, tbh, is not for debate. 3PC developers will be strongly encouraged to use the abstracted xPDO layer to take full advantage of this. Those that don’t - well, don’t expect people to use your components when they’re not compatible with their system.

That said, for simple client projects, it is *strongly* advantageous for you to learn basic PDO syntax. (note that jason showed you simple, non-schema/classes queries.) A few reasons why:
- Security layer - PDO implements better security against sql injection
- Easier to read - it’s much easier than parsing massive DB queries that have to use a bunch of old functions to extrapolate the data.
- Upgradability - simply put, you’re going to see PDO become more and more of the PHP DB market, and low-level mysql_connect and such become less and less.

We will be removing the DBAPI in 2.1. That, unfortunately for some, is a reality. If you want to keep using DBAPI (which to be honest, I find much more complex than xPDO), you can stick with Evolution. However, you’re going to be missing the vast feature-rich possibilities of Revolution in doing so.

On a side note, I think it’s important to note that PHP development in general is becoming more abstract; simple scripts are losing ground to more robust, OOP-like APIs and systems. A lot of people aren’t going to like this progression - but such, in essence, is technological progress. Change, whether we like it or not, is an inevitability for the web industry. My experience has shown me that in this industry, either you change with the industry - or go broke trying not to.