FuelPHP Forums

ccourtois

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.

Harro

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

ccourtois

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! :)

Harro

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.

Harro

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`);

ccourtois

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.

Harro

It's in 1.9/dev and not officially released yet I think.

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

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion