Jump to content
Mark
 Share


4.0 - Prepared Statements

In 4.0, we have made changes to the database class to make use of prepared statements.


For insert and update queries, the syntax is the same as it always has been:

IPSDb::i()->insert( 'table', array( 'foo' => 'bar' ) );



However, where previously the database class would try to work out the type of variable passed to it - it now binds these to a prepared statement.

The real usefulness of this change though, is apparent when you need to use a where clause. Where previously you'd have to do something like this:

$this->DB->buildAndFetch( array( 'select' => '*', 'from' => 'table', 'where' => "foo='" . $this->DB->addSlashes( $foo ) . "'" ) );


You can now do:

IPSDb::i()->buildAndFetch( array( 'select' => '*', 'from' => 'table', 'where' => array( 'foo=?', $foo ) ) );


We calculate the datatype based on the variable datatype, so previously where you had to do things like $this->DB->setDataType( 'foo', 'string' ) when you wanted to store a value like '01' - ew) - you can now just cast the variable to whatever datatype you like.
For example, if you wanted to ensure that the variable was cast as a string to avoid issues where a user name of '007' was detected as an integer and converted to '7' then you'd use:

IPSDb::i()->buildAndFetch( array( 'select' => '*', 'from' => 'table', 'where' => array( 'foo=?', (string) $foo ) ) );


Not only is this easier to type and to read, it ensures that the database class always takes care of escaping things properly.

 Share

Comments

Recommended Comments

I really like this change.  May I also suggest that the DB class be updated to do SQL Transactions too?

 

Transactions are useful when a logical update (like posting a new topic) causes multiple SQL update queries to be executed (like inserts into topics/posts tables and updates to forum counts and last post data). 

 

This would result in a "less corruptible" database if using INNODB storage engine.

 

Also, it is about time that the installer prefer INNODB storage engine (especially if MySQL is version 5.6 or later).

Link to comment
Share on other sites

I really like this change.  May I also suggest that the DB class be updated to do SQL Transactions too?

 

Transactions are useful when a logical update (like posting a new topic) causes multiple SQL update queries to be executed (like inserts into topics/posts tables and updates to forum counts and last post data). 

 

This would result in a "less corruptible" database if using INNODB storage engine.

 

Also, it is about time that the installer prefer INNODB storage engine (especially if MySQL is version 5.6 or later).

 

The IPSDb class actually extends mysqli so you can call IPSDB::i()->autocommit( FALSE ) and so on and it'll work for operations on MyISAM tables. However, due to needing fulltext indexes, MyISAM is required for loads of tables, so it won't provide ACID transactions support in most cases.

 

I'd really like to make use of some InnoDB features, like transactions and foreign keys. As it stands, we have to use MyISAM in many cases because we use fulltext indexes. Other than those tables, the installer has always used what your MySQL Server has set as the default, but the MySQL default was MyISAM up until 5.5.

 

5.6 of course will bring fulltext support to InnoDB, but it'll be a long way off before we can require that version.

Link to comment
Share on other sites

complex where clauses.... how does that work syntax-wise?

IPSDb::i()->buildAndFetch( array( 'select' => '*', 'from' => 'table', 'where' => array( 'foo=? AND bar=?', (string) $foo, (int) $bar ) ) );

Or:

IPSDb::i()->buildAndFetch( array( 'select' => '*', 'from' => 'table', 'where' => array( 'foo=? AND bar=?', array( (string) $foo, (int) $bar ) ) ) );

?

Link to comment
Share on other sites

5.6 of course will bring fulltext support to InnoDB, but it'll be a long way off before we can require that version.

 

I was suggesting that if the installer finds that MySQL is version 5.6 or later, that it default to InnoDB, not that 5.6 be required.

Link to comment
Share on other sites

Beneath the surface, does the new database class use PDO or the mysqli_ library?

 

 

I'd really like to make use of some InnoDB features, like transactions and foreign keys. As it stands, we have to use MyISAM in many cases because we use fulltext indexes.

 

How about providing a Sphinx driver for everything that requires a fulltext index? :smile:

Link to comment
Share on other sites

Beneath the surface, does the new database class use PDO or the mysqli_ library?

 

mysqli. PHP doesn't ship with PDO MySQL support enabled.

 

How about providing a Sphinx driver for everything that requires a fulltext index? :smile:

 

Not everyone has the ability to install Sphinx.

Link to comment
Share on other sites



Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Add a comment...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...