diff -Naur Clean/opennms-daemon/src/main/filtered/etc/database-schema.xml Edit/opennms-daemon/src/main/filtered/etc/database-schema.xml
--- Clean/opennms-daemon/src/main/filtered/etc/database-schema.xml 2008-01-09 11:06:12.000000000 -0500
+++ Edit/opennms-daemon/src/main/filtered/etc/database-schema.xml 2008-01-09 14:10:47.000000000 -0500
@@ -10,7 +10,7 @@
-
+
-
-
-
+
+
+
@@ -50,8 +50,8 @@
-
-
+
+
@@ -62,7 +62,7 @@
-
+
-
+
-
+
-
+
@@ -98,40 +98,40 @@
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-
diff -Naur Clean/opennms-dao/src/main/castor/database-schema.xsd Edit/opennms-dao/src/main/castor/database-schema.xsd
--- Clean/opennms-dao/src/main/castor/database-schema.xsd 2008-01-09 11:06:26.000000000 -0500
+++ Edit/opennms-dao/src/main/castor/database-schema.xsd 2008-01-09 11:09:09.000000000 -0500
@@ -28,7 +28,7 @@
-
+
@@ -92,6 +92,14 @@
+
+
+
+
+
+
+
+
@@ -99,4 +107,4 @@
-
\ No newline at end of file
+
diff -Naur Clean/opennms-dao/src/main/java/org/opennms/netmgt/config/DatabaseSchemaConfigFactory.java Edit/opennms-dao/src/main/java/org/opennms/netmgt/config/DatabaseSchemaConfigFactory.java
--- Clean/opennms-dao/src/main/java/org/opennms/netmgt/config/DatabaseSchemaConfigFactory.java 2008-01-09 11:06:26.000000000 -0500
+++ Edit/opennms-dao/src/main/java/org/opennms/netmgt/config/DatabaseSchemaConfigFactory.java 2008-01-09 12:40:42.000000000 -0500
@@ -3,13 +3,14 @@
//
// OpenNMS(R) is Copyright (C) 2002-2003 The OpenNMS Group, Inc. All rights reserved.
// OpenNMS(R) is a derivative work, containing both original code, included code and modified
-// code that was published under the GNU General Public License. Copyrights for modified
+// code that was published under the GNU General Public License. Copyrights for modified
// and included code are below.
//
// OpenNMS(R) is a registered trademark of The OpenNMS Group, Inc.
//
// Modifications:
//
+// 2008 Jan 08: Use JOIN rather than WHERE to join tables, to allow outer joins
// 2007 Jul 03: Use Java 5 generics. - dj@opennms.org
//
// Copyright (C) 1999-2001 Oculan Corp. All rights reserved.
@@ -22,13 +23,13 @@
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-// GNU General Public License for more details.
+// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
-//
-// For more information contact:
+//
+// For more information contact:
// OpenNMS Licensing
// http://www.opennms.org/
// http://www.opennms.com/
@@ -62,12 +63,12 @@
/**
* This is the singleton class used to load the configuration for the OpenNMS
- * database schemafor the filters from the database-schema xml file.
- *
+ * database schema for the filters from the database-schema xml file.
+ *
* Note: Users of this class should make sure the
* init() is called before calling any other method to ensure the
* config is loaded before accessing other convenience methods.
- *
+ *
* @author Sowmya Nataraj
* @author OpenNMS
*/
@@ -83,13 +84,6 @@
private DatabaseSchema m_config;
/**
- * The set of tables that can be joined directly or indirectly to the
- * primary table
- */
- // FIXME: m_joinable is never read
- //private Set m_joinable = null;
-
- /**
* A map from a table to the join to use to get 'closer' to the primary
* table
*/
@@ -102,7 +96,7 @@
/**
* Private constructor
- *
+ *
* @exception java.io.IOException
* Thrown if the specified config file cannot be read
* @exception org.exolab.castor.xml.MarshalException
@@ -115,26 +109,24 @@
parseXML(cfgIn);
-
cfgIn.close();
- }
-
+ }
+
public DatabaseSchemaConfigFactory(Reader reader) throws IOException, MarshalException, ValidationException {
parseXML(reader);
}
private void parseXML(Reader rdr) throws IOException, MarshalException, ValidationException {
-
+
m_config = (DatabaseSchema) Unmarshaller.unmarshal(DatabaseSchema.class, rdr);
-
+
finishConstruction();
-
}
/**
* Load the config from the default config file and create the singleton
* instance of this factory.
- *
+ *
* @exception java.io.IOException
* Thrown if the specified config file cannot be read
* @exception org.exolab.castor.xml.MarshalException
@@ -158,7 +150,7 @@
/**
* Reload the config from the default config file
- *
+ *
* @exception java.io.IOException
* Thrown if the specified config file cannot be read/loaded
* @exception org.exolab.castor.xml.MarshalException
@@ -175,9 +167,9 @@
/**
* Return the singleton instance of this factory.
- *
+ *
* @return The current factory instance.
- *
+ *
* @throws java.lang.IllegalStateException
* Thrown if the factory has not yet been initialized.
*/
@@ -187,7 +179,7 @@
return m_singleton;
}
-
+
public static synchronized void setInstance(DatabaseSchemaConfigFactory instance) {
m_singleton = instance;
m_loaded = true;
@@ -195,7 +187,7 @@
/**
* Return the database schema.
- *
+ *
* @return the database schema
*/
public synchronized DatabaseSchema getDatabaseSchema() {
@@ -206,7 +198,7 @@
* This method is used to find the table that should drive the construction
* of the join clauses between all table in the from clause. At least one
* table has to be designated as the driver table.
- *
+ *
* @return The name of the driver table
*/
public Table getPrimaryTable() {
@@ -223,8 +215,41 @@
}
/**
+ * Construct m_primaryJoins
+ */
+ private void finishConstruction() {
+ Set joinableSet = new HashSet();
+ Map primaryJoins = new HashMap();
+ joinableSet.add(getPrimaryTable().getName());
+ // loop until we stop adding entries to the set
+ int joinableCount = 0;
+ while (joinableCount < joinableSet.size()) {
+ joinableCount = joinableSet.size();
+ Set newSet = new HashSet(joinableSet);
+ Enumeration e = getDatabaseSchema().enumerateTable();
+ // for each table not already in the set
+ while (e.hasMoreElements()) {
+ Table t = e.nextElement();
+ if (!joinableSet.contains(t.getName()) && (t.getVisable() == null || t.getVisable().equalsIgnoreCase("true"))) {
+ Enumeration ejoin = t.enumerateJoin();
+ // for each join does it join a table in the set?
+ while (ejoin.hasMoreElements()) {
+ Join j = ejoin.nextElement();
+ if (joinableSet.contains(j.getTable())) {
+ newSet.add(t.getName());
+ primaryJoins.put(t.getName(), j);
+ }
+ }
+ }
+ }
+ joinableSet = newSet;
+ }
+ m_primaryJoins = Collections.synchronizedMap(primaryJoins);
+ }
+
+ /**
* Find a table using its name as the search key.
- *
+ *
* @param name
* the name of the table to find
* @return the table if it is found, null otherwise.
@@ -245,12 +270,12 @@
/**
* Find the table which has a visible column named 'colName'
- *
+ *
* @param the
* name of the column to search for
* @return the table containing column 'colName', null if colName is not a
* valid column or if is not visible.
- *
+ *
*/
public Table findTableByVisableColumn(String colName) {
Table table = null;
@@ -275,7 +300,7 @@
/**
* Return a count of the number of tables defined.
- *
+ *
* @return the number of tables in the schema
*/
public int getTableCount() {
@@ -283,113 +308,59 @@
}
/**
- * Construct a joining expression necessary to join the given table to the
- * primary table.
- *
- * @param t
- * the table to create the expression for
- * @return a string representing the joining expression or "" if no
- * expression is found
- */
- public String constructJoinExprForTable(Table t) {
- StringBuffer buf = new StringBuffer();
-
- // change this to use getPrimaryJoinsForTable
- Join[] joins = getPrimaryJoinsForTable(t);
- for (int i = 0; i < joins.length; i++) {
- Join j = joins[i];
- if (i != 0)
- buf.append(" AND ");
- buf.append(i == 0 ? t.getName() : joins[i - 1].getTable()).append('.').append(j.getColumn());
- buf.append(" = ");
- buf.append(j.getTable()).append('.').append(j.getTableColumn());
+ * Return the sequence of tables necessary to join the primary table to the
+ * given tables.
+ *
+ * @param tables
+ * list of Tables to join
+ * @return a list of table names, starting with the primary table, going
+ * to each of the given tables, or a zero-length array if no join
+ * exists or only the primary table was specified
+ */
+ public String[] getJoinTables(Table[] tables) {
+ List joinedTables = new ArrayList();
+
+ for (int i = 0; i < tables.length; i++) {
+ int insertPosition = joinedTables.size();
+ String currentTable = tables[i].getName();
+ while (currentTable != null && !joinedTables.contains(currentTable)) {
+ joinedTables.add(insertPosition, currentTable);
+ Join next = m_primaryJoins.get(currentTable);
+ if (next != null) {
+ currentTable = next.getTable();
+ } else {
+ currentTable = null;
+ }
+ }
}
- return buf.toString();
- }
- /**
- * Returns an array of the names of tables involved in a join of the given
- * table and the primary. The tables are listing in order starting with
- * primary and moving 'toward' the given table.
- *
- * @param t
- * the Table to join
- * @return an array containing the names of the tables involved. If t is the
- * primary or there is no join that reaches the primary table from
- * t, then join list is a zero-length aarray
- */
- public String[] getJoinTablesForTable(Table t) {
- Join[] joins = getPrimaryJoinsForTable(t);
- String[] tables = new String[joins.length + 1];
- tables[joins.length] = t.getName();
- for (int i = 0; i < joins.length; i++) {
- // put these in reverse order so they are from primary toward 't'
- tables[joins.length - 1 - i] = joins[i].getTable();
- }
- return tables;
+ return joinedTables.toArray(new String[joinedTables.size()]);
}
/**
- * Get the sequence of joins that are necessary to joint table t to the
+ * Construct a joining expression necessary to join the given tables to the
* primary table.
- *
- * @param t
- * the table to join
- * @return a list of the join objects for all the tables between the given
- * table and the primary or a zero-length array if t is the primary
- * or no join exists.
- */
- public Join[] getPrimaryJoinsForTable(Table t) {
- Table primary = getPrimaryTable();
-
- Join j = m_primaryJoins.get(t.getName());
- List joins = new ArrayList();
- while (j != null && j.getTable() != null && !j.getTable().equals(primary.getName())) {
- joins.add(j);
- j = m_primaryJoins.get(j.getTable());
- }
-
- if (j != null) {
- joins.add(j);
- }
-
- return joins.toArray(new Join[joins.size()]);
- }
-
- /**
- *
+ *
+ * @param tables
+ * list of Tables to join
+ * @return a string representing the joining expression or "" if no
+ * expression is found
*/
- private void finishConstruction() {
- Table primary = getPrimaryTable();
- Set joinableSet = new HashSet();
- Map primaryJoins = new HashMap();
- joinableSet.add(primary.getName());
- int joinableCount = 0;
- // loop until we stop adding entries to the set
- while (joinableCount < joinableSet.size()) {
- joinableCount = joinableSet.size();
- Set newSet = new HashSet(joinableSet);
- Enumeration e = getDatabaseSchema().enumerateTable();
- // for each table not already in the set
- while (e.hasMoreElements()) {
- Table t = e.nextElement();
- if (!joinableSet.contains(t.getName()) && (t.getVisable() == null || t.getVisable().equalsIgnoreCase("true"))) {
- Enumeration ejoin = t.enumerateJoin();
- // for each join does it join a table in the set?
- while (ejoin.hasMoreElements()) {
- Join j = ejoin.nextElement();
- if (joinableSet.contains(j.getTable())) {
- newSet.add(t.getName());
- primaryJoins.put(t.getName(), j);
- }
- }
- }
+ public String constructJoinExprForTables(Table[] tables) {
+ StringBuffer joinExpr = new StringBuffer();
+
+ String[] joinTables = getJoinTables(tables);
+ joinExpr.append(joinTables[0]);
+ for (int i = 1; i < joinTables.length; i++) {
+ Join currentJoin = m_primaryJoins.get(joinTables[i]);
+ if (currentJoin.getType() != null && !currentJoin.getType().equalsIgnoreCase("inner")) {
+ joinExpr.append(" " + currentJoin.getType().toUpperCase());
}
- joinableSet = newSet;
+ joinExpr.append(" JOIN " + joinTables[i] + " ON (");
+ joinExpr.append(currentJoin.getTable() + "." + currentJoin.getTableColumn() + " = ");
+ joinExpr.append(joinTables[i] + "." + currentJoin.getColumn() + ")");
}
- // FIXME: m_joinable is never read
- //m_joinable = Collections.synchronizedSet(joinableSet);
- m_primaryJoins = Collections.synchronizedMap(primaryJoins);
+ return joinExpr.toString();
}
-}
\ No newline at end of file
+}
diff -Naur Clean/opennms-dao/src/main/java/org/opennms/netmgt/filter/SQLTranslation.java Edit/opennms-dao/src/main/java/org/opennms/netmgt/filter/SQLTranslation.java
--- Clean/opennms-dao/src/main/java/org/opennms/netmgt/filter/SQLTranslation.java 2008-01-09 11:06:26.000000000 -0500
+++ Edit/opennms-dao/src/main/java/org/opennms/netmgt/filter/SQLTranslation.java 2008-01-09 11:26:16.000000000 -0500
@@ -10,6 +10,7 @@
//
// Modifications:
//
+// 2008 Jan 08: Add Outer Joins and 'IS NULL'
// 2007 Jun 29: Add the ability to set a limit on the number of rows returned. - dj@opennms.org
// 2006 Aug 15: Throw more specific exceptions in the static initializer - dj@opennms.org
// 2006 Apr 25: Added setNodeMappingTranslation()
@@ -28,13 +29,13 @@
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-// GNU General Public License for more details.
+// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
-//
-// For more information contact:
+//
+// For more information contact:
// OpenNMS Licensing
// http://www.opennms.org/
// http://www.opennms.com/
@@ -60,6 +61,8 @@
import org.opennms.netmgt.filter.node.AIntegerOctet;
import org.opennms.netmgt.filter.node.AIpaddrIpIdent;
import org.opennms.netmgt.filter.node.AIplikeExprPart;
+import org.opennms.netmgt.filter.node.AIsNullExprPart;
+import org.opennms.netmgt.filter.node.AIsNotNullExprPart;
import org.opennms.netmgt.filter.node.ALessThanEqualExprPart;
import org.opennms.netmgt.filter.node.ALessThanExprPart;
import org.opennms.netmgt.filter.node.ALikeExprPart;
@@ -80,11 +83,11 @@
* the from and where clauses will be built. This information will be passed on
* to a SQLConstruct object by the parser when the expression has been fully
* parsed.
- *
+ *
* @author Jason Johns
* @author Weave
* @author OpenNMS
- *
+ *
*/
public class SQLTranslation extends DepthFirstAdapter {
private DatabaseSchemaConfigFactory m_schemaFactory;
@@ -108,24 +111,24 @@
public static final String VIRTUAL_CATINC_PREFIX = "catinc";
/**
- * The list of tables required to create the approriate SQL statement
+ * The starting node of the parse tree
*/
- private List m_tables;
+ private Start m_node;
/**
- * The list of columns to be returned by the SQL.
+ * A modifier on the selectList (like 'DISTINCT')
*/
- private List m_selectList;
+ private String m_selectModifier;
/**
- * A modifier on the selectList (like 'DISTINCT')
+ * The list of columns to be returned by the SQL.
*/
- private String m_selectModifier;
+ private List m_selectList;
/**
- * The from part of the clause
+ * The list of tables required to create the approriate SQL statement
*/
- private StringBuffer m_from;
+ private List m_tables;
/**
* The where part of the clause
@@ -138,245 +141,49 @@
private StringBuffer m_ipaddr;
/**
- * The starting node of the parse tree
- */
- private Start m_node;
-
- /**
* The limit count for the filter, or null if there is no limit.
*/
private Integer m_limitCount = null;
- /**
- * This method is used to build the join condtions to be added to the where
- * clause of a complete select statement. A join condition will only be
- * built between two tables if the table being looked at has a <join>
- * reference indicating a join condition between itself and the driver
- * table.
- *
- * @return The completed join clause for the WHERE clause
- */
- private String constructJoin() {
- StringBuffer joinBuf = new StringBuffer();
- Iterator i = m_tables.iterator();
- while (i.hasNext()) {
- Table t = (Table) i.next();
- String expr = m_schemaFactory.constructJoinExprForTable(t);
- if (expr != null && !"".equals(expr)) {
- joinBuf.append(" AND ");
- joinBuf.append(expr);
- }
- }
- return joinBuf.toString();
- }
-
- /**
- * Validate the identifier by ensuring it is references in the schema. Also
- * checks for 'virtual columns' be checking the prefix. If it is this turns
- * into a reference to the 'serviceName' column of the service table and the
- * appropriate join.
- */
- private String validateIdent(String ident) {
- String expr = null;
- Table tableForIdent = m_schemaFactory.findTableByVisableColumn(ident);
- if (tableForIdent != null)
- expr = addColumnToStatement(tableForIdent, ident);
-
- if (expr == null && ident.startsWith(VIRTUAL_COLUMN_PREFIX)) {
- String serviceName = ident.substring(VIRTUAL_COLUMN_PREFIX.length());
- // should check against some form of
- // service identifier table, but for now I'm
- // removing this check since it's just used
- // internally
- //
- tableForIdent = m_schemaFactory.findTableByVisableColumn("serviceName");
- if (tableForIdent != null)
- expr = addColumnToStatement(tableForIdent, "serviceName");
- if (expr != null)
- expr = expr + " = '" + serviceName + '\'';
- }
-
- if (expr == null && ident.startsWith(VIRTUAL_NOT_COLUMN_PREFIX)) {
- String serviceName = ident.substring(VIRTUAL_NOT_COLUMN_PREFIX.length());
- // should check against some form of
- // service identifier table, but for now I'm
- // removing this check since it's just used
- // internally
- //
- tableForIdent = m_schemaFactory.findTableByVisableColumn("ipAddr");
- if (tableForIdent != null)
- expr = addColumnToStatement(tableForIdent, "ipAddr");
- if (expr != null)
- expr = expr + " not in (select ipaddr from ifservices,service where service.serviceName ='"+ serviceName + "' and service.serviceID = ifServices.serviceid)";
- }
-
- if (expr == null && ident.startsWith(VIRTUAL_CATINC_PREFIX)) {
- String categoryName = ident.substring(VIRTUAL_CATINC_PREFIX.length());
- //
- // This is a kludge to get Alex's categories working
- //
- tableForIdent = m_schemaFactory.findTableByVisableColumn("nodeID");
- if (tableForIdent != null)
- expr = addColumnToStatement(tableForIdent, "nodeID");
- if (expr != null)
- expr = expr + " in (select nodeid from category_node, categories where categories.categoryID = category_node.categoryID AND categories.categoryName = '"+ categoryName + "')";
- }
-
- if (expr == null) {
- throw new FilterParseException("The token " + ident + " is an illegal column value.");
- }
-
- return expr;
- }
-
- /**
- * Adds a column to the statement. This means insuring that this column is
- * valid, its table is listed in the m_tables list as well as any
- * intermediate tables necessary to join this table to the primary table. It
- * returns the tablename.colname used to reference this column in the SQL.
- *
- * @param colName
- * the name of the column to add
- * @return the 'tablename.column' expression used to reference the column in
- * SQL.
- */
- private String addColumnToStatement(String colName) {
- Table t = m_schemaFactory.findTableByVisableColumn(colName);
- if (t == null)
- throw new FilterParseException("Could not find the column '" + colName + "' in the database schema");
- return addColumnToStatement(t, colName).toString();
- }
-
- /**
- * Adds a column to the statement. The column is assumed to be in the passed
- * in table. This means insuring that this column is valid, its table is
- * listed in the m_tables list as well as any intermediate tables necessary
- * to join this table to the primary table. It returns the tablename.colname
- * used to reference this column in the SQL.
- *
- * @param t
- * A Table that must contain the column colName
- * @param colName
- * the name of the column to add
- * @return the 'tablename.column' expression used to reference the column in
- * SQL.
- *
- * @param t
- * @param colName
- * @return a string represtenting the part of the where portion of the
- * clause for this column
- */
- private String addColumnToStatement(Table t, String colName) {
- String[] joinTableNames = m_schemaFactory.getJoinTablesForTable(t);
- for (int i = 0; i < joinTableNames.length; i++) {
- Table joinTable = m_schemaFactory.getTableByName(joinTableNames[i]);
- if (joinTable == null)
- throw new FilterParseException("Unable to locate visable table for " + joinTableNames[i] + " referrenced in join for table " + t.getName());
- if (!m_tables.contains(joinTable)) {
- if (m_tables.size() == 0)
- m_from.append(joinTable.getName());
- else
- m_from.append(", ").append(joinTable.getName());
-
- m_tables.add(joinTable);
- }
- }
- return t.getName() + "." + colName;
- }
-
- /**
- * This method removes any double quote characters from the start and end of
- * a string and replaces them with single quotes.
- *
- * @param string
- * the string to replace quote characters in
- *
- */
- private String convertString(String string) {
- // for a string we need to change any encapsulating double
- // quotes to single quotes
- //
- StringBuffer buffer = new StringBuffer(string);
- buffer.setCharAt(0, '\'');
- buffer.setCharAt(buffer.length() - 1, '\'');
-
- return buffer.toString();
- }
-
- /**
- * This method checks to ensure that a number appearing in an IP address is
- * within the 0-255 range.
- *
- * @param octet
- * an integer from an ip octet
- *
- * @exception java.lang.IndexOutOfBoundsException
- */
- public void checkIPNum(String octet) {
- try {
- int ipnum = Integer.parseInt(octet);
- if (ipnum < 0 || ipnum > 255)
- throw new IndexOutOfBoundsException("The specified IP octet is not valid, value = " + octet);
- } catch (NumberFormatException e) {
- throw new IndexOutOfBoundsException("The specified IP octet is not valid, value = " + octet);
- }
- }
-
public SQLTranslation(Start node, DatabaseSchemaConfigFactory databaseSchemaConfigFactory) {
m_schemaFactory = databaseSchemaConfigFactory;
-
+
m_node = node;
m_selectList = new ArrayList();
- m_from = new StringBuffer(" FROM ");
-
- m_where = new StringBuffer(" WHERE (");
+ m_where = new StringBuffer(" WHERE ");
m_tables = new ArrayList(m_schemaFactory.getTableCount());
setDefaultTranslation();
}
- private String buildSelectClause() {
- StringBuffer clause = new StringBuffer("SELECT ");
- clause.append(m_selectModifier).append(" ");
-
- for (int i = 0; i < m_selectList.size(); i++) {
- clause.append((String) m_selectList.get(i)).append(i < m_selectList.size() - 1 ? ", " : "");
- }
-
- return clause.toString();
- }
-
/**
* Initializes the pieces of the SQL statement to perform a default query to
* select distinct ip addresses based the query that is built from the rest
* of the rule.
*/
public void setDefaultTranslation() {
-
m_selectModifier = "DISTINCT";
m_selectList.clear();
- m_selectList.add(addColumnToStatement("ipAddr"));
+ m_selectList.add(addColumn("ipAddr"));
}
public void setIPServiceMappingTranslation() {
m_selectModifier = "";
m_selectList.clear();
- m_selectList.add(addColumnToStatement("ipAddr"));
- m_selectList.add(addColumnToStatement("serviceName"));
-
+ m_selectList.add(addColumn("ipAddr"));
+ m_selectList.add(addColumn("serviceName"));
}
public void setNodeMappingTranslation() {
m_selectModifier = "DISTINCT";
m_selectList.clear();
- m_selectList.add(addColumnToStatement("nodeid"));
- m_selectList.add(addColumnToStatement("nodelabel"));
-
+ m_selectList.add(addColumn("nodeid"));
+ m_selectList.add(addColumn("nodelabel"));
}
/**
@@ -387,7 +194,7 @@
* constrained by node, interface, and service if they are not null or
* blank. This select is then anded with the filter rule to get the complete
* SQL statement.
- *
+ *
* @param nodeId
* a node id to constrain against
* @param ipaddr
@@ -400,7 +207,7 @@
m_selectList.clear();
- String ipAddrColumn = addColumnToStatement("ipAddr");
+ String ipAddrColumn = addColumn("ipAddr");
m_selectList.add(ipAddrColumn);
StringBuffer constraint = new StringBuffer();
@@ -408,7 +215,7 @@
if (nodeId != 0) {
if (needAnd)
constraint.append(" AND ");
- String nodeIDColumn = addColumnToStatement("nodeID");
+ String nodeIDColumn = addColumn("nodeID");
constraint.append(nodeIDColumn).append(" = ").append(nodeId);
needAnd = true;
}
@@ -421,7 +228,7 @@
}
if (service != null && !service.equals("")) {
- String serviceColumn = addColumnToStatement("serviceName");
+ String serviceColumn = addColumn("serviceName");
if (needAnd)
constraint.append(" AND ");
constraint.append(serviceColumn).append(" = '").append(service).append('\'');
@@ -429,18 +236,17 @@
}
m_where.append(constraint).append(") AND (");
-
}
public void setInterfaceWithServiceTranslation() {
m_selectModifier = "DISTINCT";
m_selectList.clear();
- m_selectList.add(addColumnToStatement("ipAddr"));
- m_selectList.add(addColumnToStatement("serviceName"));
- m_selectList.add(addColumnToStatement("nodeID"));
+ m_selectList.add(addColumn("ipAddr"));
+ m_selectList.add(addColumn("serviceName"));
+ m_selectList.add(addColumn("nodeID"));
}
-
+
/**
* Set a limit on the number of rows returned.
*/
@@ -448,12 +254,152 @@
m_limitCount = count;
}
- public void outStart(Start node) {
- // finish the where clause by putting in the join clauses to
- // the ipinterface table, separating them from the rest of the
- // where clause
+ /**
+ * This method returns the complete sql statement for the filter that was
+ * parsed. The SQL statement is the result of the select, from, and where
+ * components assembled from the code.
+ *
+ */
+ public String getStatement() {
+ // don't walk tree if there is no tree to walk
+ if (m_node == null)
+ return null;
+
+ // this will walk the tree and build the rest of the sql statement
+ //
+ m_node.apply(this);
+
+ return buildSelectClause() + buildFromClause() + m_where.toString() + buildLimitClause();
+ }
+
+ private String buildSelectClause() {
+ StringBuffer clause = new StringBuffer("SELECT " + m_selectModifier + " ");
+
+ for (int i = 0; i < m_selectList.size(); i++) {
+ clause.append((String) m_selectList.get(i)).append(i < m_selectList.size() - 1 ? ", " : "");
+ }
+
+ return clause.toString();
+ }
+
+ private String buildFromClause() {
+ if (m_tables != null) {
+ return " FROM " + m_schemaFactory.constructJoinExprForTables(m_tables.toArray(new Table[m_tables.size()]));
+ } else {
+ return "";
+ }
+ }
+
+ private String buildLimitClause() {
+ if (m_limitCount != null) {
+ return " LIMIT " + m_limitCount;
+ } else {
+ return "";
+ }
+ }
+
+ /**
+ * Check for 'virtual columns' by checking the prefix. If it is this turns
+ * into a reference to the 'serviceName' column of the service table.
+ */
+ private String validateIdent(String ident) {
+ String expr = null;
+
+ if (ident.startsWith(VIRTUAL_COLUMN_PREFIX)) {
+ String serviceName = ident.substring(VIRTUAL_COLUMN_PREFIX.length());
+ // should check against some form of
+ // service identifier table, but for now I'm
+ // removing this check since it's just used
+ // internally
+ expr = addColumn("serviceName");
+ if (expr != null)
+ expr = expr + " = '" + serviceName + "'";
+ }
+
+ else if (ident.startsWith(VIRTUAL_NOT_COLUMN_PREFIX)) {
+ String serviceName = ident.substring(VIRTUAL_NOT_COLUMN_PREFIX.length());
+ // should check against some form of
+ // service identifier table, but for now I'm
+ // removing this check since it's just used
+ // internally
+ //
+ expr = addColumn("ipAddr");
+ if (expr != null)
+ expr = expr + " not in (select ipaddr from ifservices,service where service.serviceName ='"+ serviceName + "' and service.serviceID = ifServices.serviceid)";
+ }
+
+ else if (ident.startsWith(VIRTUAL_CATINC_PREFIX)) {
+ String categoryName = ident.substring(VIRTUAL_CATINC_PREFIX.length());
+ //
+ // This is a kludge to get Alex's categories working
+ //
+ expr = addColumn("nodeID");
+ if (expr != null)
+ expr = expr + " in (select nodeid from category_node, categories where categories.categoryID = category_node.categoryID AND categories.categoryName = '"+ categoryName + "')";
+ }
+
+ else {
+ throw new FilterParseException("The token " + ident + " is an illegal column value.");
+ }
+
+ return expr;
+ }
+
+ /**
+ * Add a column to the statement. This means ensuring that this column is
+ * valid and its table is listed in the m_tables list. It returns the
+ * tablename.colname used to reference this column in the SQL.
+ *
+ * @param colName
+ * the name of the column to add
+ * @return the 'tablename.column' expression used to reference the column in
+ * SQL.
+ */
+ private String addColumn(String colName) {
+ Table table = m_schemaFactory.findTableByVisableColumn(colName);
+ if (table == null)
+ throw new FilterParseException("Could not find the column '" + colName + "' in the database schema");
+ if (!m_tables.contains(table))
+ m_tables.add(table);
+ return table.getName() + "." + colName;
+ }
+
+ /**
+ * This method removes any double quote characters from the start and end of
+ * a string and replaces them with single quotes.
+ *
+ * @param string
+ * the string to replace quote characters in
+ *
+ */
+ private String convertString(String string) {
+ // for a string we need to change any encapsulating double
+ // quotes to single quotes
//
- m_where.append(")" + constructJoin());
+ StringBuffer buffer = new StringBuffer(string);
+ buffer.setCharAt(0, '\'');
+ buffer.setCharAt(buffer.length() - 1, '\'');
+
+ return buffer.toString();
+ }
+
+ /**
+ * This method checks to ensure that a number appearing in an IP address is
+ * within the 0-255 range.
+ *
+ * @param octet
+ * an integer from an ip octet
+ *
+ * @exception java.lang.IndexOutOfBoundsException
+ */
+ public void checkIPNum(String octet) {
+ try {
+ int ipnum = Integer.parseInt(octet);
+ if (ipnum < 0 || ipnum > 255)
+ throw new IndexOutOfBoundsException("The specified IP octet is not valid, value = " + octet);
+ } catch (NumberFormatException e) {
+ throw new IndexOutOfBoundsException("The specified IP octet is not valid, value = " + octet);
+ }
}
public void caseAAndRule(AAndRule node) {
@@ -481,41 +427,51 @@
}
public void caseAGtrThanExprPart(AGtrThanExprPart node) {
- m_where.append(validateIdent(node.getIdent().getText()));
+ m_where.append(addColumn(node.getColumn().getText()));
m_where.append(" > ");
node.getCompareRight().apply(this);
}
public void caseALessThanExprPart(ALessThanExprPart node) {
- m_where.append(validateIdent(node.getIdent().getText()));
+ m_where.append(addColumn(node.getColumn().getText()));
m_where.append(" < ");
node.getCompareRight().apply(this);
}
public void caseAGtrThanEqualExprPart(AGtrThanEqualExprPart node) {
- m_where.append(validateIdent(node.getIdent().getText()));
+ m_where.append(addColumn(node.getColumn().getText()));
m_where.append(" >= ");
node.getCompareRight().apply(this);
}
public void caseALessThanEqualExprPart(ALessThanEqualExprPart node) {
- m_where.append(validateIdent(node.getIdent().getText()));
+ m_where.append(addColumn(node.getColumn().getText()));
m_where.append(" <= ");
node.getCompareRight().apply(this);
}
public void caseACompareExprPart(ACompareExprPart node) {
- m_where.append(validateIdent(node.getIdent().getText()));
+ m_where.append(addColumn(node.getColumn().getText()));
m_where.append(" = ");
node.getCompareRight().apply(this);
}
public void caseANotEqualExprPart(ANotEqualExprPart node) {
- m_where.append(validateIdent(node.getIdent().getText()));
+ m_where.append(addColumn(node.getColumn().getText()));
m_where.append(" <> ");
node.getCompareRight().apply(this);
}
+ public void caseAIsNullExprPart(AIsNullExprPart node) {
+ m_where.append(addColumn(node.getColumn().getText()));
+ m_where.append(" IS NULL");
+ }
+
+ public void caseIsNotNullExprPart(AIsNotNullExprPart node) {
+ m_where.append(addColumn(node.getColumn().getText()));
+ m_where.append(" IS NOT NULL");
+ }
+
public void inANotExprPart(ANotExprPart node) {
m_where.append("NOT ");
}
@@ -537,9 +493,9 @@
public void caseALikeExprPart(ALikeExprPart node) {
inALikeExprPart(node);
- if (node.getIdent() != null) {
- node.getIdent().apply(this);
- m_where.append(validateIdent(node.getIdent().getText()));
+ if (node.getColumn() != null) {
+ node.getColumn().apply(this);
+ m_where.append(addColumn(node.getColumn().getText()));
}
if (node.getLike() != null) {
node.getLike().apply(this);
@@ -554,9 +510,9 @@
public void caseATildelikeExprPart(ATildelikeExprPart node) {
inATildelikeExprPart(node);
- if (node.getIdent() != null) {
- node.getIdent().apply(this);
- m_where.append(validateIdent(node.getIdent().getText()));
+ if (node.getColumn() != null) {
+ node.getColumn().apply(this);
+ m_where.append(addColumn(node.getColumn().getText()));
}
if (node.getTildelike() != null) {
node.getTildelike().apply(this);
@@ -573,9 +529,9 @@
StringBuffer iplikeMethodCall = new StringBuffer("iplike(");
inAIplikeExprPart(node);
- if (node.getIdent() != null) {
- node.getIdent().apply(this);
- iplikeMethodCall.append(validateIdent(node.getIdent().getText()));
+ if (node.getColumn() != null) {
+ node.getColumn().apply(this);
+ iplikeMethodCall.append(addColumn(node.getColumn().getText()));
}
if (node.getIplike() != null) {
node.getIplike().apply(this);
@@ -708,31 +664,4 @@
}
outAIntegerOctet(node);
}
-
- /**
- * This method returns the complete sql statement for the filter that was
- * parsed. The SQL statement is the result of the select, from, and where
- * components assembled from the code.
- *
- */
- public String getStatement() {
- // don't walk tree if there is no tree to walk
- if (m_node == null)
- return null;
-
- // this will walk the tree and build the rest of the sql statement
- //
- m_node.apply(this);
-
- return buildSelectClause() + m_from.toString() + m_where.toString()
- + buildLimitClause();
- }
-
- private String buildLimitClause() {
- if (m_limitCount != null) {
- return " LIMIT " + m_limitCount;
- } else {
- return "";
- }
- }
}
diff -Naur Clean/opennms-dao/src/main/sablecc/filter.grammar Edit/opennms-dao/src/main/sablecc/filter.grammar
--- Clean/opennms-dao/src/main/sablecc/filter.grammar 2008-01-09 11:06:26.000000000 -0500
+++ Edit/opennms-dao/src/main/sablecc/filter.grammar 2008-01-09 11:09:09.000000000 -0500
@@ -9,11 +9,13 @@
name = letter name_part ( '_' name_part)*;
list = (number* ',')* number*;
range = number* '-' number*;
-service_ident = 'is' name;
-notservice_ident = 'notis' name;
-catinc_ident = 'catinc' name;
+service = 'is' name;
+notservice = 'notis' name;
+catinc = 'catinc' name;
Tokens
+ident = service | notservice | catinc;
+column = name;
semi = ';';
not = '!';
compare = '==';
@@ -22,6 +24,8 @@
gtr_than_equal = '>=';
less_than = '<';
less_than_equal = '<=';
+is_null = 'IS NULL' | 'is null';
+is_not_null = 'IS NOT NULL' | 'is not null';
and = '&';
or = '|';
like = 'LIKE' | 'like';
@@ -31,15 +35,13 @@
rparen = ')';
blank = white_space;
integer = number*;
-ident = service_ident | name;
-notident = notservice_ident | name;
quoted_string = '"' [all - '"']* '"' | ''' [all - ''']* ''';
dot = '.';
star = '*';
octet_list = list;
octet_range = range;
octet_range_list = range ',' list;
-
+
Ignored Tokens
blank;
@@ -52,16 +54,18 @@
{expr_parens} lparen rule rparen;
expr_part = {ident} ident |
- {gtr_than} ident gtr_than compare_right |
- {less_than} ident less_than compare_right |
- {gtr_than_equal} ident gtr_than_equal compare_right |
- {less_than_equal} ident less_than_equal compare_right |
- {compare} ident compare compare_right |
- {not_equal} ident not_equal compare_right |
+ {gtr_than} column gtr_than compare_right |
+ {less_than} column less_than compare_right |
+ {gtr_than_equal} column gtr_than_equal compare_right |
+ {less_than_equal} column less_than_equal compare_right |
+ {compare} column compare compare_right |
+ {not_equal} column not_equal compare_right |
+ {is_null} column is_null |
+ {is_not_null} column is_not_null |
{not} not expr |
- {like} ident like quoted_string |
- {tildelike} ident tildelike quoted_string |
- {iplike} ident iplike ip_ident;
+ {like} column like quoted_string |
+ {tildelike} column tildelike quoted_string |
+ {iplike} column iplike ip_ident;
compare_right = {integer} integer |
{string} quoted_string;