Skip to content
This repository has been archived by the owner on Jan 15, 2019. It is now read-only.

[dev.icinga.com #1108] multi valued inserts on for *contacts #508

Closed
icinga-migration opened this issue Jan 12, 2011 · 9 comments
Closed

Comments

@icinga-migration
Copy link

This issue has been migrated from Redmine: https://dev.icinga.com/issues/1108

Created by mfriedrich on 2011-01-12 14:48:00 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2011-02-03 18:26:34 +00:00)
Target Version: 1.3
Last Update: 2014-12-08 14:34:47 +00:00 (in Redmine)


the idea of this patch is to remove the duplicate key thingy and introduce insert with grouped values...

https://secure.opsera.com/wsvn/wsvn/opsview/trunk/opsview-base/patches/ndoutils\_multi\_valued\_inserts.patch

queries are in dbqueries.c in idoutils, evaluation needed.

diff -ur ndoutils-1.4b3.original/src/dbhandlers.c ndoutils-1.4b3.ton/src/dbhandlers.c
--- ndoutils-1.4b3.original/src/dbhandlers.c    2008-08-13 23:56:14.000000000 +0100
+++ ndoutils-1.4b3.ton/src/dbhandlers.c 2008-08-14 02:12:56.550373784 +0100
@@ -3337,6 +3337,7 @@
        char *ptr2=NULL;
        char *ptr3=NULL;
        int has_been_modified=0;
+       int first;

        if(idi==NULL)
                return NDO_ERROR;
@@ -3537,6 +3538,12 @@
                free(buf1);
                }

+       if(asprintf(&buf1,"INSERT INTO %s (instance_id,host_id,contact_object_id) VALUES "
+                   ,ndo2db_db_tablenames[NDO2DB_DBTABLE_HOSTCONTACTS]
+                  )==-1)
+               buf1=NULL;
+
+       first=1;
        /* save contacts to db */
        mbuf=idi->mbuf[NDO2DB_MBUF_CONTACT];
        for(x=0;xdbinfo.instance_id
                            ,host_id
                            ,member_id
                           )==-1)
-                       buf=NULL;
-      
-               if(asprintf(&buf1,"INSERT INTO %s SET %s ON DUPLICATE KEY UPDATE %s"
-                           ,ndo2db_db_tablenames[NDO2DB_DBTABLE_HOSTCONTACTS]
-                           ,buf
-                           ,buf
-                          )==-1)
                        buf1=NULL;
-
-               result=ndo2db_db_query(idi,buf1);
                free(buf);
-               free(buf1);
+               first=0;
+
                }
+       if(first==0)
+               result=ndo2db_db_query(idi,buf1);
+       free(buf1);

        /* save custom variables to db */
        mbuf=idi->mbuf[NDO2DB_MBUF_CUSTOMVARIABLE];
@@ -3764,6 +3769,7 @@
        char *ptr2=NULL;
        char *ptr3=NULL;
        int has_been_modified=0;
+       int first=0;

        if(idi==NULL)
                return NDO_ERROR;
@@ -3923,6 +3929,12 @@
        for(x=0;x<9;x++)
                free(es[x]);

+       if(asprintf(&buf1,"INSERT INTO %s (instance_id,service_id,contact_object_id) VALUES "
+                   ,ndo2db_db_tablenames[NDO2DB_DBTABLE_SERVICECONTACTS]
+                  )==-1)
+               buf1=NULL;
+
+       first=1;
        /* save contacts to db */
        mbuf=idi->mbuf[NDO2DB_MBUF_CONTACT];
        for(x=0;xdbinfo.instance_id
                            ,service_id
                            ,member_id
                           )==-1)
-                       buf=NULL;
-      
-               if(asprintf(&buf1,"INSERT INTO %s SET %s ON DUPLICATE KEY UPDATE %s"
-                           ,ndo2db_db_tablenames[NDO2DB_DBTABLE_SERVICECONTACTS]
-                           ,buf
-                           ,buf
-                          )==-1)
                        buf1=NULL;
-
-               result=ndo2db_db_query(idi,buf1);
                free(buf);
-               free(buf1);
+               first=0;
+
                }
+       if (first==0)
+               result=ndo2db_db_query(idi,buf1);
+       free(buf1);

        /* save custom variables to db */
        mbuf=idi->mbuf[NDO2DB_MBUF_CUSTOMVARIABLE];

Attachments

Changesets

2011-02-03 15:40:29 +00:00 by mfriedrich 20891b2

idoutils: add multi valued inserts for host|service_contacts instead of single insert/update loops (thx Opsview) #1108

fixes #1108

2011-02-03 18:05:59 +00:00 by mfriedrich cb2d53f

idoutils: drop unique constraints on host|service_contacts tables

refs #1108

2011-02-03 18:06:45 +00:00 by mfriedrich eaa1f9a

idoutils: rework multi valued host|service_contact inserts for oracle and sequences

the default insert like mysql/pgsql use it, does not work.
code splitted up, and combinded query reworked into the form

insert into service_contacts (id, instance_id,service_id,contact_object_id)
select seq_service_contacts.nextval, a, b, c
from
(
select 21 as a,75368 as b,45317 as c from dual
union all
select 21,75368,45391 from dual
union all
select 21,75368,1111 from dual
);

this works for multiple inserts.

refs #1108

Relations:

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2011-01-12 14:48:15 +00:00

  • Subject changed from multi valued inserts on for *contacs to multi valued inserts on for *contacts

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2011-01-27 16:46:21 +00:00

  • File added ndoutils_remove_duplicate_host_contacts.patch

remove the unique keys or do not insert contacts...

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2011-02-03 12:34:20 +00:00

remove duplicate host contacts patch is foobar, not usable. had a problem in my mind about that.

*) ido2db is running

*) core startup/restart
*) NEBTYPE_PROCESS_PRELAUNCH is being sent through idomod
*) config tables are cleared, at least the contacts!
*) so bulk contacts insert is possible, not violating the unique constraint

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2011-02-03 13:38:21 +00:00

that's rather easy to apply in the first place, but without any bindings though.

in order to test, we'll need to assign straigt contacts to host/service definitions.

ido2db: Error: database query failed for 'INSERT INTO icinga_host_contacts (instance_id,service_id,contact_object_id) VALUES (1,1258,4264)' - 'ERROR:  column "service_id" of relation "icinga_host_contacts" does not exist at character 47 ' 

shows that i made a mistake on the hosts ;D

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2011-02-03 13:38:30 +00:00

  • Status changed from New to Assigned
  • Assigned to set to mfriedrich
  • Target Version set to 1.3

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2011-02-03 15:29:14 +00:00

  • Done % changed from 0 to 50

tested working. will hit 1.3 ...

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2011-02-03 16:51:46 +00:00

oracle is a bit different. i thought i had tested that before, but at least oracle treats that stuff different - as usual.

a multi valued insert on oracle with sequence.nextval intact looks like this:

insert into service_contacts (id, instance_id,service_id,contact_object_id) 
select seq_service_contacts.nextval, a, b, c 
from
(
select 21 as a,75368 as b,45317 as c from dual
union all
select 21,75368,45391 from dual
union all
select 21,75368,1111 from dual
);

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2011-02-03 18:26:34 +00:00

  • Category set to 57
  • Status changed from Assigned to Resolved
  • Done % changed from 50 to 100

@icinga-migration
Copy link
Author

Updated by mfriedrich on 2014-12-08 14:34:47 +00:00

  • Project changed from 18 to Core, Classic UI, IDOUtils
  • Category changed from 57 to IDOUtils

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Development

No branches or pull requests

1 participant