Fix for KEY bug in lib/plugins.php

[niobe]

I found the function to create other KEYs besides the primary key is broken in PA2.1.

Here is the fix, just change the appropriate section in api_plugin_db_table_create function. Replace this:

Code: Select all

                foreach ($data['keys'] as $key) {
                        if (isset($key['name'])) {
                                $sql .= ",\n KEY `" . $key['name'] . '` (`' . $key['columns'] . '`)';
                        }
                }

with this:

Code: Select all

                foreach ($data['keys'] as $key) {

                        while (list($ky, $value) = each($key)) {
                                //echo "Key: $ky; Value: $value<br />\n";
                                $sql .= ",\n KEY `" . $ky . '` (`' . $value . '`)';
                        }
                }

Now you can create KEYs/INDEXes for faster mysql in your setup.php

Code: Select all

function <plugin_name>_setup_table () {
        <snip>
        $data['keys'][] = array('some_col' => 'some_col', 'another_col' => 'another_col');
        <snip>

cheers,

N

[gandalf]

Thanks for the pointers. I extended your approach to multi-column keys (primary, normal and unique keys) and to more than one (normal, unique) key per table by using

Code: Select all

		/* primary keys, multi-key columns are allowed */
		if (isset($data['primary'])) {
			$sql .= ",\n PRIMARY KEY (`";
			/* remove blanks */
			$no_blanks = str_replace(" ", "", $data['primary']);
			/* add tics to columns names */
			$sql .=  str_replace(",", "`, `", $no_blanks) . '`)';
		}

		/* "normal" keys, multi-key columns are allowed, multiple keys per run are allowed as well */
		if (isset($data['keys'])) {
			foreach ($data['keys'] as $key) {
				if (isset($key['name'])) {
					$sql .= ",\n KEY `" . $key['name'] . '` (`';
					if (isset($key['columns'])) {
						/* remove blanks */
						$no_blanks = str_replace(" ", "", $key['columns']);
						/* add tics to columns names */
						$sql .=  str_replace(",", "`, `", $no_blanks) . '`)';
					}
				}
			}
		}

		/* "unique" keys, multi-key columns are allowed, multiple keys per run are allowed as well */
		if (isset($data['unique'])) {
			foreach ($data['unique'] as $unique) {
				if (isset($unique['name'])) {
					$sql .= ",\n UNIQUE KEY `" . $unique['name'] . '` (`';
					if (isset($unique['columns'])) {
						/* remove blanks */
						$no_blanks = str_replace(" ", "", $unique['columns']);
						/* add tics to columns names */
						$sql .=  str_replace(",", "`, `", $no_blanks) . '`)';
					}
				}
			}
		}

This will be available with cacti 088 as part of the integrated plugin architecture
Reinhard

[niobe]

Sweet, thanks for incorporating the fix.

[gandalf]

de rien
Reinhard