Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Check if a database index exists before creating or dropping
  • Using the DBUtil class, you can check if a table or field exists before creating/dropping to avoid errors. Is there any way to do this for table indexes? There is no index_exists() function on the DBUtil class page. I also looked through the class file itself and didn't find anything similar.

    If anyone has any insight if this can even be done, it would be greatly appreciated. Thanks in advance.
  • You can use DB::list_indexes() to get an array with all indexes defined on a table.

    Note that an entry is returned for every column in an index. If you have an index like:

    UNIQUE KEY `indexname` (`fieldA`,`fieldB`);

    the following is returned:

    array (size=2)
      0 => 
        array (size=8)
          'name' => string 'indexname' (length=8)
          'column' => string 'fieldA' (length=8)
          'order' => string '1' (length=1)
          'type' => string 'BTREE' (length=5)
          'primary' => boolean false
          'unique' => boolean true
          'null' => boolean false
          'ascending' => boolean true
      2 => 
        array (size=8)
          'name' => string 'indexname' (length=8)
          'column' => string 'fieldB' (length=5)
          'order' => string '2' (length=1)
          'type' => string 'BTREE' (length=5)
          'primary' => boolean false
          'unique' => boolean true
          'null' => boolean false
          'ascending' => boolean true
  • Thanks for getting back to me. I never got notified on your last message, so I'm only seeing this now.

    The list_indexes() method seems to be specific to MongoDB which I am not using. I did find a workaround to this by using try/catch code when creating or deleting indexes. This way they don't toss exceptions and kill my scripts.

    If you're aware of anything that will work with MySQL, please keep me posted.

    Thanks again for your help! :)
  • Which Fuel version are you on?

    When I do 

    $result = \DB::list_indexes('users_sessionscopes');
    var_dump($result);

    which is a table from the Auth package with a few indexes, I get

    array (size=4)
      0 => 
        array (size=8)
          'name' => string 'PRIMARY' (length=7)
          'column' => string 'id' (length=2)
          'order' => string '1' (length=1)
          'type' => string 'BTREE' (length=5)
          'primary' => boolean true
          'unique' => boolean true
          'null' => boolean false
          'ascending' => boolean true
      1 => 
        array (size=8)
          'name' => string 'session_id' (length=10)
          'column' => string 'session_id' (length=10)
          'order' => string '1' (length=1)
          'type' => string 'BTREE' (length=5)
          'primary' => boolean false
          'unique' => boolean false
          'null' => boolean false
          'ascending' => boolean true
      2 => 
        array (size=8)
          'name' => string 'access_token' (length=12)
          'column' => string 'access_token' (length=12)
          'order' => string '1' (length=1)
          'type' => string 'BTREE' (length=5)
          'primary' => boolean false
          'unique' => boolean false
          'null' => boolean false
          'ascending' => boolean true
      3 => 
        array (size=8)
          'name' => string 'scope' (length=5)
          'column' => string 'scope' (length=5)
          'order' => string '1' (length=1)
          'type' => string 'BTREE' (length=5)
          'primary' => boolean false
          'unique' => boolean false
          'null' => boolean false
          'ascending' => boolean true

    So it works fine here, with MySQL? Tested with both MySQLi and MySQL (=PDO) drivers, using the current 1.9/develop codebase.
  • p.s. This table is defined as:

    --
    -- Table structure for table `users_sessionscopes`
    --

    CREATE TABLE `users_sessionscopes` (
      `id` int(11) NOT NULL,
      `session_id` int(11) NOT NULL,
      `access_token` varchar(50) NOT NULL DEFAULT '',
      `scope` varchar(64) NOT NULL DEFAULT ''
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --
    -- Indexes for dumped tables
    --

    --
    -- Indexes for table `users_sessionscopes`
    --
    ALTER TABLE `users_sessionscopes`
      ADD PRIMARY KEY (`id`),
      ADD KEY `session_id` (`session_id`),
      ADD KEY `access_token` (`access_token`),
      ADD KEY `scope` (`scope`);
  • As always, thanks for your super quick replies. In regards to this, I cannot call the list_indexes() method whatsoever. When I do, FuelPHP tosses an exception at me:

    ErrorException [ Fatal Error ]:
    Call to undefined method Fuel\Core\DB::list_indexes()

    I'm running FuelPHP 1.8. The DB class doesn't make any mention of this method. It's only listed under the MongoDB methods here: https://fuelphp.com/docs/classes/mongo/methods.html.

    No worries though. I found a pretty solid work around. I just run a manual query to show the table indexes and then cycle through the results.

    $results = \DB::query('SHOW INDEX FROM `my_table`')->execute();

    This gives me a list of all the table indexes and I can easily remove them and check for them by key/index name.
  • HarroHarro
    Accepted Answer
    It's in 1.9/dev and not officially released yet I think.

    I'll add "release changed in dev" to the todo list.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion