CODEWITHSUNDEEP
phpmysql
Repox
Repox

Reputation: 15475

Check if database tables exists - performance?

I want to make some simple objects/models for unspecified frameworks/systems.

Additionally I want to use MySQL as backend for my data.

My goal is simple implementation - small changes to a configuration file and thats basically it.

My problem is that I'm convinced I need to check, when using my model, that the database tables actually exists - and if not, create the table(s) for me.

I was thinking something like:

<?php

  class MyObject
  {

    public function __construct()
    {
      $dal->query("SHOW TABLES LIKE 'MyTable'");

      if($dal->num_rows() == 0)
        $this->_createTables();
    }
    ...
  }
?>

But I'm worried about the performance with this model - I'm looking for either confirmation on the efficiency of my solution or a better solution.

Upvotes: 0

Views: 1068

Answers (4)

Jason
Jason

Reputation: 2049

also, you may want to consider just calling 'show tables', then caching it at the class level in a private var, so you could do a simple isset later, eg. 

isset($this->tables_cache[$db_name][$table_name])

this would allow you to scale a bit better with more tables.

you could also save this as a json structure or serialized struct in your filesystem, loading it on __construct of your class, and (re)saving it on __destruct.

Upvotes: 0

scoates
scoates

Reputation: 853

In my opinion, and depending on your application's needs, you might be better off checking for this condition only if an error occurs, and to assume that the table exists otherwise. Something like this would avoid the extra query on every instance (you should, however, put the check into its own method).

public function insert($data, $spiralingToDeath=false)
{
  // (do actual insertion here)

  if ($this->isError) {
    // nothing obvious

    if ($spiralingToDeath) {
      // recursion check
      throw new DBException("Tried to create a table and failed.");
    } else {
      $dal->query("SHOW TABLES LIKE 'MyTable'");

      if($dal->num_rows() == 0) {
         $this->_createTables();
      } 
      // try again:
      $this->insert($data, true);
   }
 }
}

Upvotes: 2

seriousdev
seriousdev

Reputation: 7656

What about CREATE TABLE IF NOT EXISTS?

Upvotes: 1

thejh
thejh

Reputation: 45578

Require the user to change the configuration through your editor and make your editor modify the table layout before it saves the configuration.

Upvotes: 0

Related Questions