How to retrieve the currently defined column names in a garden table

rbrahmson

I am writing a plugin that filters on the values of several columns in the discussion table. I'd like to validate that the columns I specify in the dynamically constructed SQL are defined in the Discussions table. I've tried the following code snippet:

public function GetColumns($CheckField,$Table = 'Discussion') {
    echo '<br><h>'.__LINE__.' Table:'.$Table;       // generates: "271 Table:Discussion"
    echo '<br>'.__LINE__.' CheckField:'.$CheckField;    // generates: "272 CheckField:DiscussionID"
    $Sql = Gdn::sql()->fetchColumnSql($Table);
    echo '<br>'.__LINE__.' Sql:'.$Sql;      // generates: "274 Sql:show columns from ufce_Discussion"
    $Columns = array();
    $Columns = Gdn::sql()->query($Sql);     //This failes with the errors putlines below.

Unfortunately I get these errors (in the log file):

31 Mar 2016 - 22:14:50 intranet.com Garden Exception: PDO Statement failed to prepare|Gdn_Database|Query|You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join ufce_UserDiscussion w on w.DiscussionID = d2.DiscussionID and w.UserID' at line 2 in /forum/srvr191/intr_html/Vanilla/library/database/class.database.php on 293
31 Mar 2016 - 22:14:50 intranet.com Garden #0 [internal function]: Gdn_ErrorHandler(256, 'PDO Statement f...', '/forum/srvr191...', 293, Array)
31 Mar 2016 - 22:14:50 intranet.com Garden #1 /forum/srvr191/intr_html/Vanilla/library/database/class.database.php(293): trigger_error('PDO Statement f...', 256)
31 Mar 2016 - 22:14:50 intranet.com Garden #2 /forum/srvr191/intr_html/Vanilla/library/database/class.sqldriver.php(1630): Gdn_Database->Query('select w.UserID...', Array, Array)
31 Mar 2016 - 22:14:50 intranet.com Garden #3 /forum/srvr191/intr_html/Vanilla/library/database/class.sqldriver.php(654): Gdn_SQLDriver->Query('select w.UserID...')
31 Mar 2016 - 22:14:50 intranet.com Garden #4 /forum/srvr191/intr_html/Vanilla/applications/vanilla/models/class.discussionmodel.php(348): Gdn_SQLDriver->Get()
31 Mar 2016 - 22:14:50 intranet.com Garden #5 /forum/srvr191/intr_html/Vanilla/applications/vanilla/controllers/class.discussionscontroller.php(136): DiscussionModel->GetWhere(false, 0, '100')
31 Mar 2016 - 22:14:50 intranet.com Garden #6 /forum/srvr191/intr_html/Vanilla/plugins/FilterDiscussion/class.FilterDiscussion.plugin.php(73): DiscussionsController->Index('p1')
.
.
.

From the little I understand it appears than my query for the columns of the discussion table are somehow associated with some other query ("...'left join ufce_UserDiscussion w on w.DiscussionID = d2.DiscussionID and w.UserID' ...").

I'd appreciate some help here. Thanks in advance!

R_J

defineSchema() makes use of Gdn_Schema and Gdn_Schema makes use of Gdn::SQL(), if no database is provided.
You have to create the schema and pass not only the name of the table, but also a new instance of the db. Then Gdn_Schema will use that db and doesn't interfere with the half built sql from your calling function.

$database = new Gdn_Database();
$schema = new Gdn_Schema($table, $database);
$fields = array_keys($schema->fields());
decho($fields);

R_J

Take a look at the class.model.php method filterSchema()

R_J

And I have learned today, that if you work with Gdn::sql() you work with an existing instance of the sql class, so you might have "relicts" from a former sql call or even worse, you destroy the sql that should be built before/after your custom method is called

R_J

I'd say that the shortest "Vanilla way" to check that would be this:

$schema = new Gdn_Schema($table);
$fields = array_keys($schema->fields());
if (!in_array($checkField, $fields)) {
    echo 'Cheater!';
    return;
}

rbrahmson

First, thanks for looking at this and trying to help! I was playing with some of the functions (e.g. trying to call filterchema). Nothing worked...

Back to your suggested code - right of the bat the line "$Schema = new Gdn_Schema($Table);" generates an error:

Garden Exception: PDO Statement failed to prepare|Gdn_Database|Query|You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join ufce_UserDiscussion w on w.DiscussionID = d2.DiscussionID and w.UserID' at line 2 in .../Vanilla/library/database/class.database.php on 29301

I sense that your previous message that what you wrote

"I have learned today, that if you work with Gdn::sql() you work with an existing instance of the sql class, so you might have "relicts" from a former sql call or even worse, you destroy the sql that should be built before/after your custom method is called"

is in play here. This is very disturbing. I hope I am wrong and that all I miss is to initiate some process (ergo the "failed to prepare" in the error message).

hgtonight

Get the schema from the model:

$model = new DiscussionModel();
$fields = $model->defineSchema()->fields();

$fields is an array of all the columns in the model along with their attributes.

Tested as working on Vanilla 2.2.

rbrahmson

Thanks!
I should have mentioned that I am running Vanilla Version 2.1.13. When I run the above code I get:

PHP Fatal error: Call to a member function fields() on a non-object

So I tried to break it into small steps:

$model = new DiscussionModel();
$schema = $model->defineSchema();  //This breaks with the error quoted below
$fields = $schema->fields(); //Generates PHP Fatal error:  Call to a member function fields() on a non-object
var_dump($fields);  //never got here

PDO Statement failed to prepare|Gdn_Database|Query|You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join ufce_UserDiscussion w on w.DiscussionID = d2.DiscussionID and w.UserID'

I can't yet move to 2.2 but I'd like (if possible) to be able to code this for 2.1.13 as well as 2.2...

hgtonight

In 2.1, the Gdn_Model::DefineSchema method doesn't return the schema object. You will have to grab it after the definition:

$model = new DiscussionModel();
$model->DefineSchema();
$schema = model->Schema;
$fields = $schema->Fields();
var_dump($fields);

I didn't test it, because 2.1 should be replaced ASAP.

R_J

What I meant about the Gdn::sql and what I've learned... Try decho(Gdn::sql()); right at the beginning of your function. The context in that you call your function seems to be somewhere, where the sql is currently built up and not yet finished. So what you are trying to do interferes with the program flow where you call your function.

You would need to get your own instance:

$db = new Gdn_Database();
$sql = $db->sql();

or

$sql = clone Gdn::sql();
$sql->reset();

I don't know which is to be preferred...

rbrahmson

Thanks @hgtonight - that fails as well on line 2 of your sample code ($model->DefineSchema()), with the same error.

PDO Statement failed to prepare|Gdn_Database|Query|You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join ufce_UserDiscussion w on w.DiscussionID = d2.DiscussionID and w.UserID'

I am fearful of moving to V2 when I read that some plugins are not compatible. For our departmental intranet I was taking a wait and see approach (less concerned about security because it is behind our firewall).

R_J

defineSchema() makes use of Gdn_Schema and Gdn_Schema makes use of Gdn::SQL(), if no database is provided.
You have to create the schema and pass not only the name of the table, but also a new instance of the db. Then Gdn_Schema will use that db and doesn't interfere with the half built sql from your calling function.

$database = new Gdn_Database();
$schema = new Gdn_Schema($table, $database);
$fields = array_keys($schema->fields());
decho($fields);

R_J

And this also works with the solution hgtonight mentioned:

$model = new DiscussionModel();

$database = new Gdn_Database();
$model->Database = $database;

$model->DefineSchema();
$schema = $model->Schema;
$fields = $schema->Fields();
var_dump($fields);

If you only need the fields, I would use the shorter version from my above post since it has not so much overhead and the "model way" essentially does the same.

If you like to work further with the table, you might like to use the model creation.

rbrahmson

Thank you @R_J and @hgtonight - It works perfectly now (and I also tried it on V2 test server I just installed).
Not only have you solved my specific question but also helped me learn more about Vanilla. One day,in a very distant future (maybe on another Galaxy just behind the MVC123 warmhole), we could chat on the same level...

As usual, this knowledge will percolate into plugins that I will upload to the plugin repository.