Aware: model_pstmt.h Source File

00001 
00002 /* useful macros */
00003 
00004 #define AWMODEL_LOG_PRIVERROR(model, uid)\
00005   aw_log(&( (model) -> mon -> logger), AW_ERROR_LOG_LEVEL, "model: %s failed for '%u' (not allowed)", \
00006          __FUNCTION__, (uid));
00007 
00008 #define AWMODEL_GET_PSTMT(model, name) (model) -> db . stmts . name
00009 
00010 #define AWMODEL_LOG_SQLERROR(model, name) \
00011   aw_log(&( (model) -> mon -> logger), AW_ERROR_LOG_LEVEL, "model: %s failed on '%s'(%s)", \
00012          __FUNCTION__, (model) -> db . stmts . name . sql , sqlite3_errmsg( (model) -> db . state . conn));
00013 #define AWMODEL_LOG_SQLERROR2(model, stmt) \
00014   aw_log(&( (model) -> mon -> logger), AW_ERROR_LOG_LEVEL, "model: %s failed on '%s'(%s)", \
00015          __FUNCTION__, (stmt) -> sql , sqlite3_errmsg( (model) -> db . state . conn));
00016 
00017 #define AWMODEL_SET_TEXT(model, name, index, mbr, mbrsize) \
00018 {\
00019  const byte_t *txt = (const byte_t *)sqlite3_column_text((AWMODEL_GET_PSTMT(model,name)).compiled, (index)); \
00020  strncpy((mbr), (txt)?txt:"", (mbrsize)-1);\
00021  mbr[(mbrsize)-1]=0; /* keep sure terminated */ \
00022 }
00023 
00024 #define AWMODEL_SET_TEXT_MBR(model, name, index, mbr) \
00025 {\
00026  AWMODEL_SET_TEXT(model, name, index, mbr, sizeof(mbr)); \
00027 }
00028 #define AWMODEL_SET_TEXT_MBR2(stmt, index, mbr) \
00029 {\
00030  const size_t mbrsize=sizeof(mbr); \
00031  const byte_t *txt = (byte_t *)sqlite3_column_text(stmt -> compiled, (index)); \
00032  strncpy((mbr), (txt)?txt:"", (mbrsize)-1);\
00033  mbr[(mbrsize)-1]=0; /* keep sure terminated */ \
00034 }
00035 
00036 #define AWMODEL_SET_INT_MBR(model, name, index, mbr) \
00037 {\
00038  mbr = sqlite3_column_int((AWMODEL_GET_PSTMT(model,name)).compiled, index); \
00039 }
00040 #define AWMODEL_SET_INT_MBR2(stmt, index, mbr) \
00041 {\
00042  mbr = sqlite3_column_int(stmt->compiled, index); \
00043 }
00044 
00045 #define AWMODEL_BIND_INT(model, name, index, integer) \
00046 {\
00047   if ( unlikely( sqlite3_bind_int((AWMODEL_GET_PSTMT(model,name)).compiled, (index), (integer)) != SQLITE_OK ) ) { \
00048     AWMODEL_LOG_SQLERROR(model, name); \
00049     goto sqlerror; \
00050   }\
00051 } 
00052 
00053 #define AWMODEL_BIND_DOUBLE(model, name, index, real) \
00054 {\
00055   if ( unlikely( sqlite3_bind_double((AWMODEL_GET_PSTMT(model,name)).compiled, (index), (real)) != SQLITE_OK ) ) { \
00056     AWMODEL_LOG_SQLERROR(model, name); \
00057     goto sqlerror; \
00058   }\
00059 } 
00060 
00061 #define AWMODEL_BIND_TEXT(model, name, index, text) \
00062 {\
00063   if ( unlikely( sqlite3_bind_text(AWMODEL_GET_PSTMT(model,name) . compiled, \
00064                                    (index), (text), strlen(text), SQLITE_STATIC) != SQLITE_OK  ) ) { \
00065     AWMODEL_LOG_SQLERROR(model, name); \
00066     goto sqlerror; \
00067   }\
00068 } 
00069 
00070 #define AWMODEL_DO_ROLLBACK(model) \
00071 {\
00072   if ( aw_model_execute_stmt(model, &(model->db.stmts.rollback)) != SQLITE_DONE) \
00073     aw_log(&(model->mon->logger), AW_ERROR_LOG_LEVEL, "model: %s failed (%s)", \
00074            __FUNCTION__, sqlite3_errmsg(model->db.state.conn)); \
00075 \
00076   aw_model_reset_stmt(model, &(model->db.stmts.rollback)); \
00077 }
00078 
00079 #define AWMODEL_DO_COMMIT(model)\
00080 {\
00081   if ( aw_model_execute_stmt(model, &(model->db.stmts.commit)) != SQLITE_DONE ) \
00082     aw_log(&(model->mon->logger), AW_ERROR_LOG_LEVEL, "model: %s failed (%s)", \
00083            __FUNCTION__, sqlite3_errmsg(model->db.state.conn)); \
00084 \
00085   aw_model_reset_stmt(model, &(model->db.stmts.commit)); \
00086 }
00087 
00088 #ifdef AWMODEL_PSTMT_INIT
00089 /* must be used init_db_resources() */
00090 #undef AWMODEL_PSTMT
00091 #define AWMODEL_PSTMT(name, sqlstring) model -> db . stmts . name . sql = sqlstring ;
00092 #else
00093 #define AWMODEL_PSTMT(name, sqlstring) aw_model_stmt_t name ;
00094 #endif
00095 
00096 
00097 /* Delcare all the prepared sql statments */
00098 
00099 AWMODEL_PSTMT(begin, "begin")
00100 AWMODEL_PSTMT(commit, "commit ; end")
00101 AWMODEL_PSTMT(rollback, "rollback ; end")
00102 
00103 AWMODEL_PSTMT(settings_queryby_kw,
00104               "select val,note from aw_settings where kw=?")
00105 AWMODEL_PSTMT(settings_update,
00106               "update aw_settings set val=? where kw=?")
00107 
00108 AWMODEL_PSTMT(auth, "select password,id,ro from aw_user where loginid = ? and active=1")
00109 
00110 AWMODEL_PSTMT(user_create,
00111               "insert into aw_user (loginid,active) values(?,1)")
00112 AWMODEL_PSTMT(user_queryby_loginid, 
00113               "select id from aw_user where loginid = ?")
00114 AWMODEL_PSTMT(user_queryby_id, 
00115               "select loginid,password,lastname,firstname,active,notify,notifymode,ro,deleted from aw_user where id = ?")
00116 AWMODEL_PSTMT(user_query_listing, 
00117               "select id,loginid,lastname,firstname,active,deleted from aw_user "
00118               "order by loginid")
00119 AWMODEL_PSTMT(user_update, 
00120               "update aw_user set "
00121               "loginid=?,password=?,lastname=?,firstname=?,active=?,notify=?,notifymode=?,ro=?,deleted=? "
00122               "where id = ?")
00123 
00124 
00125 AWMODEL_PSTMT(userinfo_query, "select street1,street2,city,state,zip,country,"
00126                      "homephone,workphone,fax,email,mobile,pager,notes,id from aw_userinfo where user_id = ?")
00127 AWMODEL_PSTMT(userinfo_update, 
00128               "update aw_userinfo set "
00129               "street1=?,street2=?,city=?,state=?,zip=?,country=?,"
00130               "homephone=?,workphone=?,fax=?,email=?,mobile=?,pager=?,notes=?"
00131               "where id = ?")
00132 
00133 AWMODEL_PSTMT(group_create,
00134               "insert into aw_group (name) values(?)")
00135 AWMODEL_PSTMT(group_queryby_id, 
00136               "select name,description,deleted from aw_group "
00137               "where id = ?")
00138 AWMODEL_PSTMT(group_queryby_name, 
00139               "select id from aw_group where name = ?")
00140 AWMODEL_PSTMT(group_query_user_slisting, 
00141               "select g.id,g.name from aw_groupmember m, aw_group g "
00142               "where m.user_id = ? and m.group_id = g.id and g.deleted <> 1 "
00143               "order by g.name")
00144 AWMODEL_PSTMT(group_query_notuser_slisting, 
00145               "select GID,GNAME from "
00146               "( select id as GID,name as GNAME from aw_group where deleted <> 1 except "
00147               " select g.id as GID,g.name as GNAME from aw_groupmember m, aw_group g "
00148               " where m.user_id = ? and m.group_id = g.id and g.deleted <> 1 ) "
00149               "order by GNAME")
00150 AWMODEL_PSTMT(group_query_node_slisting, 
00151               "select g.id,g.name from aw_node2group n2g, aw_group g "
00152               "where n2g.node_id = ? and n2g.group_id = g.id and g.deleted <> 1 "
00153               "order by g.name")
00154 AWMODEL_PSTMT(group_query_notnode_slisting, 
00155               "select GID,GNAME from "
00156               "( select id as GID,name as GNAME from aw_group where deleted <> 1 except "
00157               " select g.id as GID,g.name as GNAME from aw_node2group n2g, aw_group g "
00158               " where n2g.node_id = ? and n2g.group_id = g.id and g.deleted <> 1 ) "
00159               "order by GNAME")
00160 AWMODEL_PSTMT(user_query_group_slisting, 
00161               "select u.id,u.loginid from aw_groupmember m, aw_user u "
00162               "where m.group_id = ? and m.user_id = u.id and u.deleted <> 1 "
00163               "order by m.priorityorder asc") // NOTE: ordering by the priorityorder
00164 AWMODEL_PSTMT(user_query_notgroup_slisting, 
00165               "select UID,ULOGINID from "
00166               "( select id as UID,loginid as ULOGINID from aw_user where deleted <> 1 except "
00167               " select u.id as UID,u.loginid as ULOGINID from aw_groupmember m, aw_user u "
00168               " where m.group_id = ? and m.user_id = u.id and u.deleted <> 1) "
00169               "order by ULOGINID")
00170 AWMODEL_PSTMT(group_query_listing, 
00171               "select id,name,description,deleted from aw_group "
00172               "order by name")
00173 AWMODEL_PSTMT(group_update, 
00174               "update aw_group set "
00175               "name=?,description=?,deleted=? "
00176               "where id = ?")
00177 
00178 
00179 AWMODEL_PSTMT(groupmember_user_delete_tagged, 
00180               "delete from aw_groupmember where user_id = ? and tag=1")
00181 AWMODEL_PSTMT(groupmember_user_tag, 
00182               "update aw_groupmember set tag = 1 where user_id = ?")
00183 AWMODEL_PSTMT(groupmember_group_delete, 
00184               "delete from aw_groupmember where group_id = ?")
00185 AWMODEL_PSTMT(groupmember_group_delete_tagged, 
00186               "delete from aw_groupmember where group_id = ? and tag=1")
00187 AWMODEL_PSTMT(groupmember_group_tag, 
00188               "update aw_groupmember set tag = 1 where group_id = ?")
00189 AWMODEL_PSTMT(groupmember_insert, 
00190               "insert into aw_groupmember (group_id,user_id) values (?,?)")
00191 AWMODEL_PSTMT(groupmember_remove_tag, 
00192               "update aw_groupmember set tag = 0 where group_id = ? and user_id = ?")
00193 
00194 AWMODEL_PSTMT(subnet_create,
00195               "insert into aw_subnet (netmask,active) values(?,1)")
00196 AWMODEL_PSTMT(subnet_update,
00197               "update aw_subnet set active=?,deleted=? where id=?")
00198 AWMODEL_PSTMT(subnet_queryby_netmask, 
00199               "select id from aw_subnet where netmask = ?")
00200 AWMODEL_PSTMT(subnet_query_listing, 
00201               "select id,netmask,active,deleted from aw_subnet "
00202               "order by netmask")
00203 
00204 AWMODEL_PSTMT(nodegrp_create,
00205               "insert into aw_nodegrp (name) values(?)")
00206 AWMODEL_PSTMT(nodegrp_queryby_id, 
00207               "select name,description,deleted from aw_nodegrp "
00208               "where id = ?")
00209 AWMODEL_PSTMT(nodegrp_queryby_name, 
00210               "select id from aw_nodegrp where name = ?")
00211 AWMODEL_PSTMT(nodegrp_query_listing, 
00212               "select id,name,outage,deleted from aw_nodegrp "
00213               "order by name")
00214 AWMODEL_PSTMT(nodegrp_update, 
00215               "update aw_nodegrp set "
00216               "name=?,description=?,deleted=? "
00217               "where id = ?")
00218 AWMODEL_PSTMT(node_query_nodegrp_slisting, 
00219               "select n.id,"
00220               " coalesce(n.username,n.nmbname,n.snmpname,n.mdnsname,n.ipname) as name"
00221               " from aw_node2nodegrp n2g, aw_node n "
00222               "where n2g.nodegrp_id = ? and n2g.node_id = n.id and n.deleted <> 1 "
00223               "order by name") 
00224 AWMODEL_PSTMT(node_query_notnodegrp_slisting, 
00225               "select NID,NNAME from "
00226               "( select n.id as NID,"
00227               " coalesce(n.username,n.nmbname,n.snmpname,n.mdnsname,n.ipname) as NNAME"
00228               " from aw_node n where n.deleted <> 1 except "
00229               " select n.id as NID,"
00230               " coalesce(n.username,n.nmbname,n.snmpname,n.mdnsname,n.ipname) as NNAME"
00231               " from aw_node2nodegrp n2g, aw_node n "
00232               "where n2g.nodegrp_id = ? and n2g.node_id = n.id and n.deleted <> 1) "
00233               "order by NNAME")
00234 AWMODEL_PSTMT(nodegrp_apply_active,
00235               "update aw_node set active=?"
00236               " where deleted=0 and id in (select n.id from aw_node2nodegrp n2g, aw_node n"
00237               "                            where n2g.nodegrp_id=? and n2g.node_id=n.id)")
00238 AWMODEL_PSTMT(nodegrp_apply_deleted,
00239               "update aw_node set deleted=?"
00240               " where id in (select n.id from aw_node2nodegrp n2g, aw_node n"
00241               "              where n2g.nodegrp_id=? and n2g.node_id=n.id)")
00242 
00243 AWMODEL_PSTMT(node_user_create,
00244               "insert into aw_node (username,active) values(?,1)")
00245 AWMODEL_PSTMT(node_mdns_create,
00246               "insert into aw_node (mdnsname,active) values(?,1)")
00247 AWMODEL_PSTMT(node_queryby_id, 
00248               "select username,nmbname,snmpname,mdnsname,ipname,"
00249               " description,active,deleted from aw_node where id = ?")
00250 AWMODEL_PSTMT(node_update, 
00251               "update aw_node set "
00252               "username=?,description=?,active=?,deleted=? "
00253               "where id = ?")
00254 AWMODEL_PSTMT(node_queryby_username, 
00255               "select id from aw_node where username = ?")
00256 AWMODEL_PSTMT(node_queryby_mdnsname, 
00257               "select id from aw_node where mdnsname = ?")
00258 AWMODEL_PSTMT(node_query_listing, 
00259               "select n.id, coalesce(n.username,n.nmbname,n.snmpname,n.mdnsname,n.ipname) as name,n.outage,n.active,n.deleted "
00260               "from aw_node n "
00261               "order by name")
00262 AWMODEL_PSTMT(node_query_outage,
00263               "select n.id, coalesce(n.username,n.nmbname,n.snmpname,n.mdnsname,n.ipname) as name,n.outage,n.active,n.deleted"
00264               " from aw_node n"
00265               " where n.active=1 and n.outage=1"
00266               " order by n.lastmodtime desc limit ?")
00267 
00268 AWMODEL_PSTMT(node_query_nodegrp_listing, 
00269               "select n.id, coalesce(n.username,n.nmbname,n.snmpname,n.mdnsname,n.ipname) as name,n.outage,n.active,n.deleted "
00270               "from aw_node n, aw_node2nodegrp n2g "
00271               "where n2g.nodegrp_id = ? and n2g.node_id = n.id "
00272               "order by name")
00273 AWMODEL_PSTMT(node2nodegrp_nodegrp_delete, 
00274               "delete from aw_node2nodegrp where nodegrp_id = ?")
00275 AWMODEL_PSTMT(node2nodegrp_insert, 
00276               "insert into aw_node2nodegrp (nodegrp_id,node_id) values (?,?)")
00277 
00278 AWMODEL_PSTMT(node2group_group_delete, 
00279               "delete from aw_node2group where node_id = ?")
00280 AWMODEL_PSTMT(node2group_insert, 
00281               "insert into aw_node2group (group_id,node_id) values (?,?)")
00282 
00283 
00284 AWMODEL_PSTMT(nodeasset_queryby_nodeid,
00285               "select "
00286               "manufacturer,vendor,modelnumber,serialnumber,circuitid,"
00287               "assetnumber,os,rack,slot,port,region,division,department,"
00288               "address1,address2,city,state,country,zip,building,floor,"
00289               "room,vendorphone,vendorfax,vendoremail,vendorim,vendorurl,"
00290               "vendorassetnumber,dateinstalled,lease,leaseexpires,supportphone,"
00291               "maintcontract,maintcontractexpires,comment"
00292               " from aw_nodeasset where node_id=?")
00293 AWMODEL_PSTMT(nodeasset_updateby_nodeid,
00294               "update aw_nodeasset set "
00295               "manufacturer=?,vendor=?,modelnumber=?,serialnumber=?,circuitid=?,"
00296               "assetnumber=?,os=?,rack=?,slot=?,port=?,region=?,division=?,department=?,"
00297               "address1=?,address2=?,city=?,state=?,country=?,zip=?,building=?,floor=?,"
00298               "room=?,vendorphone=?,vendorfax=?,vendoremail=?,vendorim=?,vendorurl=?,"
00299               "vendorassetnumber=?,dateinstalled=?,lease=?,leaseexpires=?,supportphone=?,"
00300               "maintcontract=?,maintcontractexpires=?,comment=?"
00301               " where node_id=?")
00302 
00303 
00304 AWMODEL_PSTMT(interface_simple_create,
00305               "insert into aw_interface (node_id,ip,active) values(?,?,?)")
00306 AWMODEL_PSTMT(interface_update,
00307               "update aw_interface set"
00308               " active=?,deleted=?,"
00309               " ip=?,snmp_community=?,handlerto=?,handlercycle=?,handlercron=?,maintcron=?,perf=?,traffic=?"
00310               " where id=?")
00311 AWMODEL_PSTMT(interface_name_queryby_id, 
00312               "select coalesce(n.username,n.nmbname,n.snmpname,n.mdnsname,n.ipname) || ' ' ||"
00313               " ifnull(coalesce(i.dnsname,i.nmbname,i.snmpname),'') || ' ' || ifnull(i.ip,i.ifname) as name"
00314               " from aw_interface i, aw_node n"
00315               " where i.id = ? and i.node_id = n.id")
00316 AWMODEL_PSTMT(interface_queryby_ip, 
00317               "select id,node_id from aw_interface where ip = ?")
00318 AWMODEL_PSTMT(interface_queryby_nodeip, 
00319               "select id from aw_interface where node_id = ? and ip = ?")
00320 AWMODEL_PSTMT(interface_queryby_id, 
00321               "select id,"
00322               " ifnull(coalesce(dnsname,nmbname,snmpname),'') || ' ' || ifnull(ip,ifname) as name,"
00323               " outage,up,snmp,active,deleted,"
00324               " ip,snmp_community,handlerto,handlercycle,handlercron,maintcron,perf,traffic "
00325               "from aw_interface "
00326               "where id = ? ")
00327 AWMODEL_PSTMT(interface_query_node_listing, 
00328               "select id,"
00329               " ifnull(coalesce(dnsname,nmbname,snmpname),'') || ' ' || ifnull(ip,ifname) as name,"
00330               " outage,up,snmp,active,deleted,"
00331               " ip,snmp_community,handlerto,handlercycle,handlercron,maintcron,perf,traffic "
00332               "from aw_interface "
00333               "where node_id = ? "
00334               "order by name")
00335 AWMODEL_PSTMT(interface_netmon_listing,
00336               "select id,"
00337               " ifnull(coalesce(dnsname,nmbname,snmpname,ip,ifname),'') as name,"
00338               " outage,up,snmp,active,deleted,"
00339               " ip,snmp_community,handlerto,handlercycle,handlercron,maintcron,perf,traffic "
00340               "from aw_interface "
00341               "where active = 1 and ip <> ''")
00342 
00343 AWMODEL_PSTMT(service_simple_create,
00344               "insert into aw_service (interface_id,service,active) values(?,?,?)")
00345 AWMODEL_PSTMT(service_mdns_create,
00346               "insert into aw_service (node_id,interface_id,service,ip,port,proto) values(?,?,?,?,?,?)")
00347 AWMODEL_PSTMT(service_unique,
00348               "select 1 from aw_service"
00349               " where id<>? and ip=? and port=? and proto=?")
00350 AWMODEL_PSTMT(service_update,
00351               "update aw_service set"
00352               " active=?,deleted=?,"
00353               " service=?,ip=?,port=?,proto=?,"
00354               " handler=?,handlerto=?,handlercycle=?,handlercron=?,maintcron=?,perf=?"
00355               " where id=?")
00356 AWMODEL_PSTMT(service_query_port2service, 
00357               "select service,proto from aw_port2service")
00358 AWMODEL_PSTMT(service_name_queryby_id, 
00359               "select coalesce(n.username,n.nmbname,n.snmpname,n.mdnsname,n.ipname) || ' (' "
00360               " || s.ip || ') ' || s.service || ' ' || s.proto || ':' || s.port as name "
00361               " from aw_service s, aw_node n"
00362               " where s.id = ? and s.node_id=n.id")
00363 AWMODEL_PSTMT(service_id_queryby_ipport, 
00364               "select s.id from aw_service s where s.ip=? and s.port=?")
00365 AWMODEL_PSTMT(service_queryby_service, 
00366               "select id from aw_service where interface_id = ? and service = ?")
00367 AWMODEL_PSTMT(service_queryby_id, 
00368               "select id,service || ' ' || proto || ':' || port as name, "
00369               " up,active,deleted, "
00370               " service,ip,port,proto,"
00371               " handler,handlerto,handlercycle,handlercron,maintcron,perf "
00372               "from aw_service "
00373               "where id = ? ")
00374 AWMODEL_PSTMT(service_query_interface_listing, 
00375               "select id,service || ' ' || proto || ':' || port as name, "
00376               " up,active,deleted, "
00377               " service,ip,port,proto,"
00378               " handler,handlerto,handlercycle,handlercron,maintcron,perf "
00379               "from aw_service "
00380               "where interface_id = ? "
00381               "order by name")
00382 AWMODEL_PSTMT(service_netmon_listing,
00383               "select id,service || ' ' || proto || ':' || port as name, "
00384               " up,active,deleted, "
00385               " service,ip,port,proto,"
00386               " handler,handlerto,handlercycle,handlercron,maintcron,perf "
00387               "from aw_service "
00388               "where active=1 and ip <> '' and port > 0")
00389 AWMODEL_PSTMT(service_port_listing,
00390               "select port,proto,service from aw_port2service order by service")
00391 
00392 AWMODEL_PSTMT(ia_event_insert,
00393               "insert into aw_iaevent (interface_id,eventtime,up,priority)"
00394               " select id,julianday(datetime(?,'unixepoch')),?,? from aw_interface where id=? and up <> ?")
00395 AWMODEL_PSTMT(sa_event_insert,
00396               "insert into aw_saevent (service_id,eventtime,up,type,priority)"
00397               " select id,julianday(datetime(?,'unixepoch')),?,?,? from aw_service where id=? and up <> ?")
00398 AWMODEL_PSTMT(nm_event_insert,
00399               "insert into aw_nmevent (node_id,eventtime,msg,priority)"
00400               " select id,julianday(datetime(?,'unixepoch')),?,?"
00401               "  from aw_node where username=? or nmbname=? or snmpname=? or mdnsname=? or ipname=?")
00402 AWMODEL_PSTMT(event_query_today, // all events from today
00403               "select dt,type,msg from ("
00404               "select ia.eventtime as sortkey,"
00405               " datetime(ia.eventtime,'localtime') as dt,"
00406               " ia.up as type,"
00407               " coalesce(n.username,n.nmbname,n.snmpname,n.mdnsname,n.ipname) || ' ' ||"
00408               "  ifnull(coalesce(i.dnsname,i.nmbname,i.snmpname,i.ifname,i.snmpip),'') || ' ' || i.ip || ' ' ||"
00409               "   case when ia.up=1 then 'Up' else 'Down or Unreachable' end as msg"
00410               " from aw_iaevent ia, aw_interface i, aw_node n"
00411               " where ia.interface_id=i.id and i.node_id=n.id and ia.eventtime > julianday(date('now','localtime')) "
00412               "union "
00413               "select sa.eventtime as sortkey,"
00414               " datetime(sa.eventtime,'localtime') as dt,"
00415               " sa.up as type,"
00416               " coalesce(n.username,n.nmbname,n.snmpname,n.mdnsname,n.ipname) || ' (' "
00417               " || s.ip || ') ' || s.service || ' ' || s.proto || ':' || s.port || ' ' ||"
00418               "   case when sa.up=1 then 'Up' else 'Down or Unreachable' end as msg"
00419               " from aw_saevent sa, aw_service s, aw_node n"
00420               " where sa.service_id = s.id and s.node_id=n.id and sa.eventtime > julianday(date('now','localtime'))"
00421               "union "
00422               "select nm.eventtime as sortkey,"
00423               " datetime(nm.eventtime,'localtime') as dt,"
00424               " -1 as type,"
00425               " nm.msg as msg"
00426               " from aw_nmevent nm"
00427               " where nm.eventtime > julianday(date('now','localtime'))"
00428               ") order by sortkey desc limit ?"
00429               )
00430 
00431 AWMODEL_PSTMT(alert_insert,
00432               "insert into aw_alert (group_id,user_id,priorityorder,uname,msg) "
00433               "values (?,?,?,?,?)")
00434 
00435 /* get the active users in each group sorted by group and priority in group,
00436    take 1st row in each group
00437  */
00438 #define SELECT_USERS_FOR_ALERT(group_select) \
00439 "select g.id as group_id,u.id as user_id,u2g.priorityorder as porder,  "\
00440 "  u.firstname || ' ' || u.lastname as uname                           "\
00441 "  from aw_user u,aw_group g,aw_groupmember u2g                        "\
00442 "  where g.id in                                                       "\
00443       group_select \
00444 "     and g.deleted = 0                                                "\
00445 "     and g.id = u2g.group_id                                          "\
00446 "     and u2g.user_id = u.id                                           "\
00447 "     and u.active = 1                                                 "\
00448 "     order by group_id asc,porder asc                                 "\
00449 
00450 #define INTERFACE_ALERT_GROUPS \
00451 "(select group_id from aw_interface i, aw_node2group n2g where i.id = ? and i.node_id = n2g.node_id) "
00452 AWMODEL_PSTMT(alert_interface_user_query, SELECT_USERS_FOR_ALERT( INTERFACE_ALERT_GROUPS ) )
00453 #define SERVICE_ALERT_GROUPS \
00454 "(select group_id from aw_service s, aw_node2group n2g where s.id = ? and s.node_id = n2g.node_id) "
00455 AWMODEL_PSTMT(alert_service_user_query, SELECT_USERS_FOR_ALERT( SERVICE_ALERT_GROUPS ) )
00456 #define MESSAGE_ALERT_GROUPS \
00457 "(select group_id from aw_node n, aw_node2group n2g where (n.username=? or n.nmbname=? or n.snmpname=? or n.ipname=?) and n.id = n2g.node_id) "
00458 AWMODEL_PSTMT(alert_message_user_query, SELECT_USERS_FOR_ALERT( MESSAGE_ALERT_GROUPS ) )
00459 
00460 /* find the users that have NOT acknowledged their alerts by group */
00461 AWMODEL_PSTMT(alert_escalate_user_nack_query,  
00462               "select a.group_id,a.aid,a.priorityorder,a.msg"
00463               " from aw_alert a"
00464               " where a.state = 'nack'"
00465               " and escalated = 0"
00466               " and julianday(datetime('now')) - a.lastmodtime >= ?" // escalate after time limit
00467               " order by a.group_id asc,a.aid asc,a.priorityorder desc")
00468 AWMODEL_PSTMT(alert_escalate_user_nack_insert, 
00469               "insert into aw_alert (group_id,user_id,priorityorder,uname,msg,aid)"
00470               " select ? as group_id, u2g.user_id as user_id, u2g.priorityorder as priorityorder,"
00471               "  u.firstname || ' ' || u.lastname as uname,"
00472               "  ? as msg, ? as aid"
00473               "  from aw_user u,aw_group g,aw_groupmember u2g"
00474               "  where g.id = ?"
00475               "     and g.deleted = 0"
00476               "     and g.id = u2g.group_id"
00477               "     and u2g.priorityorder = ?" // previous priorityorder + 1
00478               "     and u2g.user_id = u.id"
00479               "     and u.active = 1")
00480 AWMODEL_PSTMT(alert_getuser_from_row,
00481               "select user_id from aw_alert where id=?")
00482 AWMODEL_PSTMT(alert_getuser_notifyinfo,
00483               "select u.notifymode, ui.email, ui.pager"
00484               " from aw_user u, aw_userinfo ui"
00485               " where u.id=? and ui.user_id=u.id and u.notify=1")
00486 AWMODEL_PSTMT(alert_user_slisting,
00487               "select msg"
00488               " from aw_alert where user_id=? and state='nack'"
00489               " order by lastmodtime desc"
00490               " limit ?")
00491 
00492