Flourish PHP Unframework
This is an archived copy of the forum for reference purposes

MySQL table names being quoted, causing an error (using ORM)

posted by wes 8 years ago

Hi Everyone, hope you're all doing well. I've been using the fDatabase class for a while, and decided I wanted to learn a bit more about the fORM layer. I created a very basic site and a simple database, and everything seems correct, but the query won't execute. It seems Flourish is quoting the table name in SQL, which MySQL doesn't like. Pasting the generated SQL into a terminal also causes the error - but removing the quotes works fine! 

USE flourish_test;
CREATE TABLE books (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    tscreated TIMESTAMP NOT NULL,
    tsmodified TIMESTAMP NOT NULL,
    PRIMARY KEY (id)
) TYPE=InnoDB DEFAULT CHARSET=utf8;

In my Book.php class: 

class Book extends fActiveRecord {
    protected function configure() {
        // Automatically have Flourish set the creation & modified timestamps
        fORMDate::configureDateCreatedColumn( $this, 'tscreated' );
        fORMDate::configureDateUpdatedColumn( $this, 'tsmodified' );
    }
}

In my Flourish init.php, I have (along with the normal init stuff): 

fORMDatabase::attach(
    new fDatabase(
        'mysql', 'flourish_test',
        'flourish_test_un', 'flourish_test_pw', 'localhost'
    )
);
fORM::mapClassToTable( 'Book', 'books' );

And in my one front end page, I have: 

$test = new Book();
$test->setTitle( 'The Sun Also Rises' );
$test->store();

This throws the error (though I think incorrectly) from Flourish that "the noun could not be singularized" - but more importantly shows the query in the debug output as: 

INSERT INTO "books" ("name", "tscreated", "tsmodified") VALUES ('The Sun Also Rises', '2011-07-29 14:02:13', '2011-07-29 14:02:13')

I've tried changing the class name, mappings, etc. to and from singular & plural thinking maybe Flourish was getting confused with that, but really that doesn't make sense since I followed the documentation's instructions on singular/plural naming, casing, etc. and also that the SQL is actually 100% accurate - except for the table name being quoted. I read as much as I could before posting this question. I'm losing my mind! Any ideas?

So, to address the issue of quoting, Flourish uses the SQL standard for quoting, double quotes. Unfortunately by default MySQL doesn't like that because when they started they didn't really follow the SQL standard very well. In the process of becoming more standards compliant they had to break backwards compatibility, and they didn't want to beak existing users installations. Thus, you have to enable options to get close to the SQL standard with MySQL.

In fDatabase, right after connecting to MySQL, the following queries are executed. Try executing these and then running the SQL statement: 

SET SQL_MODE = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE';
SET NAMES 'utf8';
SET CHARACTER SET utf8;

In terms of the "noun could not be singularized," can you post the backtrace to the exception? This will help us determine what is triggering it.

posted by wbond 8 years ago

Thanks, you should put that bit about MySQL compatibility in the documentation. I executed the SET SQL_MODE query in a console and was able to then execute the query with the table name quoted. I was logged in as the same user though, so shouldn't Flourish have been able to do the same?

Here's the stack trace - I had changed some naming to "Books" just for example's sake, but the structure and code is the same. The database has one additional field of "description" which isn't important. 

string 'SET SQL_MODE = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE'' (length=71)
string 'SET NAMES 'utf8'' (length=16)
string 'SET CHARACTER SET utf8' (length=22)
string 'SELECT version()' (length=16)
string 'SHOW FULL TABLES WHERE table_type = 'BASE TABLE'' (length=48)
string 'SHOW CREATE TABLE "fonts"' (length=25)
string 'SHOW CREATE TABLE "fonts"' (length=25)
string 'SHOW CREATE TABLE "tags"' (length=24)
string 'SHOW CREATE TABLE "tags_map"' (length=28)
string 'BEGIN' (length=5)
string 'INSERT INTO "fonts" ("name", "created", "modified", "description") VALUES ('Cheesewiz', '2011-07-29 14:02:13', '2011-07-29 14:02:13', NULL)' (length=139)
string 'ROLLBACK' (length=8)

( ! ) Fatal error: Uncaught exception 'fProgrammerException' with message 'The noun specified could not be singularized' in C:\\localhost\\Fontina\\inc\\flourish\\classes\\fGrammar.php on line 575
( ! ) fProgrammerException: The noun specified could not be singularized in C:\\localhost\\Fontina\\inc\\flourish\\classes\\fGrammar.php on line 575
Call Stack
#	Time	Memory	Function	Location
1	0.0007	678080	{main}( )	..\\index.php:0
2	0.0375	6526920	require_once( 'C:\\localhost\\Fontina\\views\\index.php' )	..\\index.php:20
3	0.0623	9122936	fActiveRecord->store( )	..\\index.php:25
posted by wes 8 years ago

I certainly can add that to the documentation. As a user, where would you expect that to be found?

So the issue isn't a SQL query being run. For some reason the ORM is having trouble singularizing one of your table names. Usually this is caused by one of your table names not being plural. Flourish looks at your database schema and finds relationships between them. It is likely some of the code during this process is trying to turn a table into a class name and failing.

It seems like your backtrace is incomplete since it should include a line referring to fGrammar, however I am most familiar with the output of fCore backtraces. Can you call fCore::enableExceptionHandling('html'); right before you call fActiveRecord::store()?

posted by wbond 8 years ago

Great, I think right in the fDatabase section would be perfect!

I assume you don't need all of the globals output, since I'm not using forms, sessions, etc., so here's the trace part, with the grammar class you mentioned: 


Uncaught fProgrammerException
-----------------------------
{doc_root}\\index.php(20): require_once('{doc_root}\\in...')
{doc_root}\\views\\index.php(26): fActiveRecord->store()
{doc_root}\\inc\\flourish\\classes\\fActiveRecord.php(2737): fORM::classize('tags_map')
{doc_root}\\inc\\flourish\\classes\\fORM.php(352): fGrammar::singularize('tags_map')
{doc_root}\\inc\\flourish\\classes\\fGrammar.php(575)
posted by wes 8 years ago

Ok, great.

So yeah, the table tags_map is not plural. To fix this, you'll need to call: 

fORM::mapClassToTable('TagsMap', 'tags_map');
posted by wbond 8 years ago

Wow, that was a detailed catch... you really know your stuff! I guess I didn't realize I had to map all of the tables - even if I hadn't created ORM classes for them yet. Thanks for looking into it!

posted by wes 8 years ago

One more question on ORM, Will -

The first function invoking the ORM layer seems to always produce this output: 

string 'SET SQL_MODE = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE'' (length=71)
string 'SET NAMES 'utf8'' (length=16)
string 'SET CHARACTER SET utf8' (length=22)
string 'SELECT version()' (length=16)
string 'SHOW FULL TABLES WHERE table_type = 'BASE TABLE'' (length=48)
string 'SHOW CREATE TABLE "books"' (length=25)
string 'SHOW CREATE TABLE "books"' (length=25)
// this part is just an example of creating a new book
// it changes based on what calls are made, even if just querying all records
string 'BEGIN' (length=5)
string 'INSERT INTO "books" ("title", "tscreated", "tsmodified") VALUES ('The Sun Also Rises', '2011-08-01 17:38:30', '2011-08-01 17:38:30')' (length=132)
string 'COMMIT' (length=6)

No errors, just showing what it's doing to set up the query... but I can't turn it off! I looked everywhere and tried every enableDebug(FALSE) statement I could find.

At first I thought it was an issue with 

fRecordSet::build('Book');

to list all - but then I realized (as in the example above) it always does it the first time ORM is invoked.

posted by wes 8 years ago

The output looks like the output of var_dump(). It is possible you added one somewhere while trying to debug your earlier issue?

posted by wbond 8 years ago

Hmm, good thought but no - even searching all files in the directory only turns up 3 var_dump statements - all in Flourish lib itself (fCore @ 558, fDatabase @ 2691 and fException @ 91).

My entire index page consists of: 

require_once DOC_ROOT . '/inc/flourish/init.php';
$somebook = new Book();
$somebook->setTitle( 'The Sun Also Rises' );
$somebook->store();

My init is basically right out of the example setup: 

require_once dirname(__FILE__) . '/config.php';

fORMDatabase::attach(
    new fDatabase(
        'mysql', 'books',
        $db_un, $db_pw, 'localhost'
    )
);

/**
 * Import all database models
 */
$dh = new DirectoryIterator( DOC_ROOT . '/models' );
foreach ( $dh as $file_info ) {
    if ( $file_info->isFile() && !$file_info->isDot() ) {
        require_once DOC_ROOT . '/models/' . $file_info->getFileName();
    }
}

/**
 * Map all ORM models to flourish
 */
fORM::mapClassToTable( 'Book', 'books' );

// the rest is the log_sql() function from your example, which is commented out, and the session start

Basically if I comment out the 3 $somebook lines, I don't get the output. The Book class itself only has the default timestamp (configureDateCreatedColumn) calls in the configure function.

posted by wes 8 years ago

Hey Will, any ideas on this? Could there be a setting I have that's causing it to log SQL interaction? I can use fDatabase, but I'd rather use the ORM class.

EDIT: I commented out the var_dump statement in fDatabase (line 2691) and that stopped it. I was trying to avoid actually changing the source. Maybe it was because I grabbed the latest from GitHub. Sorry for the confusion!

posted by wes 8 years ago

fDatabase should not contain any calls to var_dump(), so that is probably your culprit.

posted by wbond 8 years ago