[icinga-checkins] icinga.org: icinga-web/next: unique constraint missing in nsm_target-> target_name

git at icinga.org git at icinga.org
Thu Apr 11 16:50:17 CEST 2013


Module: icinga-web
Branch: next
Commit: a76c5452fd14fbf4080718d40ebef16dad8efb37
URL:    https://git.icinga.org/?p=icinga-web.git;a=commit;h=a76c5452fd14fbf4080718d40ebef16dad8efb37

Author: Marius Hein <marius.hein at netways.de>
Date:   Thu Apr 11 16:49:02 2013 +0200

unique constraint missing in nsm_target->target_name

Add index to NsmTarget/target_name to avoid double credential
targets.

fixes #3915

---

 .../database/models/generated/BaseNsmTarget.php    |   30 ++++++++++++--------
 etc/schema/mysql.sql                               |    2 +-
 etc/schema/oracle.sql                              |    9 ++++-
 etc/schema/pgsql.sql                               |    4 ++-
 etc/schema/sqlite.sql                              |    2 +
 etc/schema/updates/mysql/mysql_v1-8-3_to_v1-9.sql  |    4 ++
 .../updates/oracle/oracle_v1-8-3_to_v1-9.sql       |    7 ++++-
 etc/schema/updates/pgsql/pgsql_v1-8-3_to_v1-9.sql  |    5 ++-
 8 files changed, 45 insertions(+), 18 deletions(-)

diff --git a/app/modules/AppKit/lib/database/models/generated/BaseNsmTarget.php b/app/modules/AppKit/lib/database/models/generated/BaseNsmTarget.php
index cdfd50d..085a0a8 100644
--- a/app/modules/AppKit/lib/database/models/generated/BaseNsmTarget.php
+++ b/app/modules/AppKit/lib/database/models/generated/BaseNsmTarget.php
@@ -89,6 +89,12 @@ abstract class BaseNsmTarget extends Doctrine_Record {
                 'autoincrement' => false,
         ));
 
+        $this->index('target_key_unique_target_name', array(
+            'fields' => array(
+                'target_name',
+            ),
+            'type' => 'unique'
+        ));
     }
 
     public function setUp() {
@@ -227,19 +233,19 @@ abstract class BaseNsmTarget extends Doctrine_Record {
                         'target_name'=>"icinga.cronk.admin",
                         'target_description'=>"Allow user to edit and delete all cronks", 'target_class'=>"",
                         'target_type'=>"credential"),
-                array(

-                        'target_id'=>22,

-                        'target_name'=>"IcingaService",

-                        'target_description'=>"Limit data access to specific services",

-                        'target_class'=> "IcingaDataServicePrincipalTarget",

-                        'target_type'=>"icinga"

+                array(
+                        'target_id'=>22,
+                        'target_name'=>"IcingaService",
+                        'target_description'=>"Limit data access to specific services",
+                        'target_class'=> "IcingaDataServicePrincipalTarget",
+                        'target_type'=>"icinga"
                 ),
-                array(

-                        'target_id'=>23,

-                        'target_name'=>"IcingaHost",

-                        'target_description'=>"Limit data access to specific hosts",

-                        'target_class'=> "IcingaDataHostPrincipalTarget",

-                        'target_type'=>"icinga"

+                array(
+                        'target_id'=>23,
+                        'target_name'=>"IcingaHost",
+                        'target_description'=>"Limit data access to specific hosts",
+                        'target_class'=> "IcingaDataHostPrincipalTarget",
+                        'target_type'=>"icinga"
                 )
         );
     }
diff --git a/etc/schema/mysql.sql b/etc/schema/mysql.sql
index 67ecb05..221d673 100644
--- a/etc/schema/mysql.sql
+++ b/etc/schema/mysql.sql
@@ -16,7 +16,7 @@ CREATE TABLE nsm_principal (principal_id INT AUTO_INCREMENT, principal_user_id I
 CREATE TABLE nsm_principal_target (pt_id INT AUTO_INCREMENT, pt_principal_id INT NOT NULL, pt_target_id INT NOT NULL, INDEX pt_target_id_ix_idx (pt_target_id), INDEX pt_principal_id_ix_idx (pt_principal_id), PRIMARY KEY(pt_id)) ENGINE = INNODB;
 CREATE TABLE nsm_role (role_id INT AUTO_INCREMENT, role_name VARCHAR(40) NOT NULL, role_description VARCHAR(255), role_disabled TINYINT DEFAULT '0' NOT NULL, role_created DATETIME NOT NULL, role_modified DATETIME NOT NULL, role_parent INT, INDEX role_parent_idx (role_parent), PRIMARY KEY(role_id)) ENGINE = INNODB;
 CREATE TABLE nsm_session (session_entry_id INT AUTO_INCREMENT, session_id VARCHAR(255) NOT NULL, session_name VARCHAR(255) NOT NULL, session_data LONGTEXT NOT NULL, session_checksum VARCHAR(255) NOT NULL, session_created DATETIME NOT NULL, session_modified DATETIME NOT NULL, PRIMARY KEY(session_entry_id)) ENGINE = INNODB;
-CREATE TABLE nsm_target (target_id INT AUTO_INCREMENT, target_name VARCHAR(45) NOT NULL, target_description VARCHAR(100), target_class VARCHAR(80), target_type VARCHAR(45) NOT NULL, PRIMARY KEY(target_id)) ENGINE = INNODB;
+CREATE TABLE nsm_target (target_id INT AUTO_INCREMENT, target_name VARCHAR(45) NOT NULL, target_description VARCHAR(100), target_class VARCHAR(80), target_type VARCHAR(45) NOT NULL, PRIMARY KEY(target_id), UNIQUE INDEX target_key_unique_target_name_idx (target_name)) ENGINE = INNODB;
 CREATE TABLE nsm_target_value (tv_pt_id INT, tv_key VARCHAR(45), tv_val VARCHAR(45) NOT NULL, PRIMARY KEY(tv_pt_id, tv_key)) ENGINE = INNODB;
 CREATE TABLE nsm_user (user_id INT AUTO_INCREMENT, user_account INT DEFAULT 0 NOT NULL, user_name VARCHAR(127) NOT NULL, user_lastname VARCHAR(40) NOT NULL, user_firstname VARCHAR(40) NOT NULL, user_password VARCHAR(64) NOT NULL, user_salt VARCHAR(64) NOT NULL, user_authsrc VARCHAR(45) DEFAULT 'internal' NOT NULL, user_authid TEXT, user_authkey VARCHAR(64), user_email VARCHAR(254) NOT NULL, user_description VARCHAR(255), user_disabled TINYINT DEFAULT '1' NOT NULL, user_created DATETIME NOT NULL, user_modified DATETIME NOT NULL, UNIQUE INDEX user_name_unique_idx (user_name), INDEX user_search_idx (user_name, user_authsrc, user_disabled, user_authid(127)), PRIMARY KEY(user_id)) ENGINE = INNODB;
 CREATE TABLE nsm_user_preference (upref_id INT AUTO_INCREMENT, upref_user_id INT NOT NULL, upref_val VARCHAR(100), upref_longval LONGTEXT, upref_key VARCHAR(50) NOT NULL, upref_created DATETIME NOT NULL, upref_modified DATETIME NOT NULL, INDEX upref_search_key_idx_idx (upref_key), INDEX principal_role_id_ix_idx (upref_user_id), PRIMARY KEY(upref_id), UNIQUE KEY `upref_user_key_unique_idx` (`upref_user_id`,`upref_key`)) ENGINE = INNODB;
diff --git a/etc/schema/oracle.sql b/etc/schema/oracle.sql
index 5217f04..e1595b4 100644
--- a/etc/schema/oracle.sql
+++ b/etc/schema/oracle.sql
@@ -216,8 +216,13 @@ CREATE TABLE nsm_target
   )
   tablespace &DATATBS;
 alter table nsm_target add constraint nsm_target_pk PRIMARY KEY  (target_id)
-	using index tablespace &IXTBS;
-  
+  using index tablespace &IXTBS;
+
+ALTER TABLE
+  nsm_target
+  add constraint target_key_unique_target_name_uq UNIQUE (target_name)
+  using index tablespace &IXTBS;
+
 --use index organized table because most of all data is within index  
 CREATE TABLE nsm_target_value
   (
diff --git a/etc/schema/pgsql.sql b/etc/schema/pgsql.sql
index 964c54c..3fee260 100644
--- a/etc/schema/pgsql.sql
+++ b/etc/schema/pgsql.sql
@@ -1120,7 +1120,9 @@ ALTER TABLE ONLY nsm_user_role
     ADD CONSTRAINT nsm_user_role_usro_user_id_nsm_user_user_id FOREIGN KEY (usro_user_id) REFERENCES nsm_user(user_id) ON UPDATE CASCADE ON DELETE CASCADE;
 
 
---
+-- Unique key for nsm_target / target_name
+CREATE UNIQUE INDEX target_key_unique_target_name_idx ON nsm_target USING btree (target_name);
+
 -- Name: public; Type: ACL; Schema: -; Owner: postgres
 --
 
diff --git a/etc/schema/sqlite.sql b/etc/schema/sqlite.sql
index a3b801d..7ec826e 100644
--- a/etc/schema/sqlite.sql
+++ b/etc/schema/sqlite.sql
@@ -73,6 +73,8 @@ CREATE TABLE nsm_target (
 	target_type VARCHAR(45) NOT NULL
 );
 
+CREATE UNIQUE INDEX target_key_unique_target_name_idx ON nsm_target(target_name);
+
 CREATE TABLE nsm_target_value (
 	tv_pt_id INTEGER PRIMARY KEY , 
 	tv_key VARCHAR(45), 
diff --git a/etc/schema/updates/mysql/mysql_v1-8-3_to_v1-9.sql b/etc/schema/updates/mysql/mysql_v1-8-3_to_v1-9.sql
index 9a715bb..672557e 100644
--- a/etc/schema/updates/mysql/mysql_v1-8-3_to_v1-9.sql
+++ b/etc/schema/updates/mysql/mysql_v1-8-3_to_v1-9.sql
@@ -9,3 +9,7 @@ ALTER TABLE nsm_user
 -- Extend cronk_xml columns for big Cronks (#3951)
 ALTER TABLE cronk MODIFY COLUMN cronk_xml LONGTEXT;
 
+-- Add unique constrain for target_name/NsmTarget (#3915)
+ALTER TABLE
+  nsm_target
+  ADD UNIQUE INDEX `target_key_unique_target_name_idx` (target_name);
\ No newline at end of file
diff --git a/etc/schema/updates/oracle/oracle_v1-8-3_to_v1-9.sql b/etc/schema/updates/oracle/oracle_v1-8-3_to_v1-9.sql
index 3ef4bbc..dc592c0 100644
--- a/etc/schema/updates/oracle/oracle_v1-8-3_to_v1-9.sql
+++ b/etc/schema/updates/oracle/oracle_v1-8-3_to_v1-9.sql
@@ -4,4 +4,9 @@ INSERT INTO nsm_db_version VALUES ('1','icinga-web/v1.9.0', CURRENT_TIMESTAMP, C
 
 -- User description attribute (#3923)
 ALTER TABLE nsm_user
-    ADD (user_description VARCHAR2(255));
\ No newline at end of file
+    ADD (user_description VARCHAR2(255));
+
+-- Add unique constrain for target_name/NsmTarget (#3915)
+ALTER TABLE
+  nsm_target
+  add constraint target_key_unique_target_name_uq UNIQUE (target_name);
\ No newline at end of file
diff --git a/etc/schema/updates/pgsql/pgsql_v1-8-3_to_v1-9.sql b/etc/schema/updates/pgsql/pgsql_v1-8-3_to_v1-9.sql
index a8f3b79..0ee2f79 100644
--- a/etc/schema/updates/pgsql/pgsql_v1-8-3_to_v1-9.sql
+++ b/etc/schema/updates/pgsql/pgsql_v1-8-3_to_v1-9.sql
@@ -3,4 +3,7 @@ INSERT INTO nsm_db_version VALUES ('1','icinga-web/v1.9.0', NOW(), NOW());
 
 -- User description attribute (#3923)
 ALTER TABLE nsm_user
-  ADD COLUMN user_description character varying(255);
\ No newline at end of file
+  ADD COLUMN user_description character varying(255);
+
+-- Add unique constrain for target_name/NsmTarget (#3915)
+CREATE UNIQUE INDEX target_key_unique_target_name_idx ON nsm_target USING btree (target_name);
\ No newline at end of file





More information about the icinga-checkins mailing list