Repository: dmwm/wmcore
Issue: FindDASToUpload Oracle DAO not working

This DAO for the DBSUpload component is not working on an Oracle database, error reads (long):

{{{
(DatabaseError) ORA-01036: illegal variable name/number
 "SELECT das1.dataset_id AS dataset, ds1.Path as Path, das1.algo_id as Algo, das1.in_dbs as in_dbs,\\
               das1.id AS das_id,\\
               da1.app_name AS ApplicationName, \\
               da1.app_ver AS ApplicationVersion, \\
               da1.app_fam AS ApplicationFamily, \\
               da1.PSet_Hash as PSetHash,\\
               da1.Config_Content as PSetContent,\\
               da1.in_dbs AS algo_in_dbs,\\
               ds1.valid_status AS valid_status\\
             FROM dbsbuffer_algo_dataset_assoc das1\\
             INNER JOIN dbsbuffer_algo da1 ON da1.id = das1.algo_id\\
             INNER JOIN dbsbuffer_dataset ds1 ON ds1.id = das1.dataset_id\\
             WHERE das1.id IN\\
             (SELECT DISTINCT das.id\\
             FROM dbsbuffer_algo_dataset_assoc das\\
             INNER JOIN dbsbuffer_dataset ds2 ON ds2.id = das.dataset_id\\
             WHERE EXISTS (SELECT id FROM dbsbuffer_file dbsfile\\
                            WHERE dbsfile.dataset_algo = das.id\\
                            AND dbsfile.status = \\'NOTUPLOADED\\')\\
             AND UPPER(ds2.Path) NOT LIKE \\'BOGUS\\')\\
             " {\\'das\\': 1}

\\tModuleName : WMComponent.DBSUpload.DBSUploadPoller
\\tMethodName : algorithm
\\tClassInstance : None
\\tFileName : /data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/cms/wmcore/0.8.45/lib/python2.6/site-packages/WMComponent/DBSUpload/DBSUploadPoller.py
\\tClassName : None
\\tLineNumber : 284
\\tErrorNr : 0

Traceback: 
Traceback (most recent call last):
  File "/data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/cms/wmcore/0.8.45/lib/python2.6/site-packages/WMComponent/DBSUpload/DBSUploadPoller.py", line 274, in algorithm
    self.sortBlocks()
  File "/data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/cms/wmcore/0.8.45/lib/python2.6/site-packages/WMComponent/DBSUpload/DBSUploadPoller.py", line 307, in sortBlocks
    dasList = self.uploadToDBS.findUploadableDAS()
  File "/data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/cms/wmcore/0.8.45/lib/python2.6/site-packages/WMComponent/DBSUpload/Database/Interface/UploadToDBS.py", line 154, in findUploadableDAS
    transaction=self.existingTransaction())
  File "/data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/cms/wmcore/0.8.45/lib/python2.6/site-packages/WMComponent/DBSUpload/Database/MySQL/FindDASToUpload.py", line 77, in execute
    conn = conn, transaction = transaction)
  File "/data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/cms/wmcore/0.8.45/lib/python2.6/site-packages/WMCore/Database/DBCore.py", line 179, in processData
    returnCursor = returnCursor)
  File "/data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/cms/wmcore/0.8.45/lib/python2.6/site-packages/WMCore/Database/DBCore.py", line 65, in executebinds
    resultProxy = connection.execute(s, b)
  File "/data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/external/py2-sqlalchemy/0.5.2-comp5/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 824, in execute
    return Connection.executors[c](self, object, multiparams, params)
  File "/data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/external/py2-sqlalchemy/0.5.2-comp5/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 888, in _execute_text
    return self.__execute_context(context)
  File "/data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/external/py2-sqlalchemy/0.5.2-comp5/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 896, in __execute_context
    self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context)
  File "/data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/external/py2-sqlalchemy/0.5.2-comp5/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 950, in _cursor_execute
    self._handle_dbapi_exception(e, statement, parameters, cursor, context)
  File "/data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/external/py2-sqlalchemy/0.5.2-comp5/lib/python2.6/site-packages/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception
    raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)
DatabaseError: (DatabaseError) ORA-01036: illegal variable name/number
 "SELECT das1.dataset_id AS dataset, ds1.Path as Path, das1.algo_id as Algo, das1.in_dbs as in_dbs,\\
               das1.id AS das_id,\\
               da1.app_name AS ApplicationName, \\
               da1.app_ver AS ApplicationVersion, \\
               da1.app_fam AS ApplicationFamily, \\
               da1.PSet_Hash as PSetHash,\\
               da1.Config_Content as PSetContent,\\
               da1.in_dbs AS algo_in_dbs,\\
               ds1.valid_status AS valid_status\\
             FROM dbsbuffer_algo_dataset_assoc das1\\
             INNER JOIN dbsbuffer_algo da1 ON da1.id = das1.algo_id\\
             INNER JOIN dbsbuffer_dataset ds1 ON ds1.id = das1.dataset_id\\
             WHERE das1.id IN\\
             (SELECT DISTINCT das.id\\
             FROM dbsbuffer_algo_dataset_assoc das\\
             INNER JOIN dbsbuffer_dataset ds2 ON ds2.id = das.dataset_id\\
             WHERE EXISTS (SELECT id FROM dbsbuffer_file dbsfile\\
                            WHERE dbsfile.dataset_algo = das.id\\
                            AND dbsfile.status = \\'NOTUPLOADED\\')\\
             AND UPPER(ds2.Path) NOT LIKE \\'BOGUS\\')\\
             " {\\'das\\': 1}

  File "/data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/cms/wmcore/0.8.45/lib/python2.6/site-packages/WMCore/WorkerThreads/BaseWorkerThread.py", line 160, in **call**
    self.algorithm(parameters)
  File "/data/srv/wmagent/v1.9.5/sw.pre/slc5_amd64_gcc461/cms/wmcore/0.8.45/lib/python2.6/site-packages/WMComponent/DBSUpload/DBSUploadPoller.py", line 284, in algorithm
    raise DBSUploadPollerException(msg)
', 'component_name': 'DBSUpload'}
}}}

Short version is:

The Oracle DAO is receiving this argument:

{\\'das\\': 1}

And therefore throwing the error.

I think the problem is this argument is needed in the MySQL version because that query is split in 2, but in the Oracle override of the sql string the first query is built-in, so when the execute from the MySQL DAO is called (which the Oracle version doesn't override), it passes those arguments to the Oracle query making it fail.


Changed files:
src/python/WMComponent/DBSUpload/Database/MySQL/FindDASToUpload.py, src/python/WMComponent/DBSUpload/Database/Oracle/FindDASToUpload.py

diff --git a/src/python/WMComponent/DBSUpload/Database/MySQL/FindDASToUpload.py b/src/python/WMComponent/DBSUpload/Database/MySQL/FindDASToUpload.py
index 854e30282..bd0002462 100644
--- a/src/python/WMComponent/DBSUpload/Database/MySQL/FindDASToUpload.py
+++ b/src/python/WMComponent/DBSUpload/Database/MySQL/FindDASToUpload.py
@@ -5,7 +5,6 @@ This code should load the necessary information regarding
 dataset-algo combinations from the DBSBuffer.
 
 """
-import logging
 from WMCore.Database.DBFormatter import DBFormatter
 
 
@@ -14,23 +13,31 @@ class FindDASToUpload(DBFormatter):
     Find Uploadable DAS
 
     """
-
-    querySQL = """SELECT DISTINCT dbsfile.dataset_algo AS dasid FROM dbsbuffer_file dbsfile
-                     WHERE dbsfile.status = 'NOTUPLOADED'"""
-
-
-    sql = """SELECT das.dataset_id AS dataset, ds.Path as Path, das.algo_id as Algo, das.in_dbs as in_dbs,
-               das.id AS das_id,
-               ds.valid_status AS valid_status,
-               ds.global_tag AS global_tag,
-               ds.parent AS parent
+    sql = """SELECT das.dataset_id AS dataset,
+                    ds.Path as Path,
+                    das.algo_id as Algo,
+                    das.in_dbs as in_dbs,
+                    das.id AS das_id,
+                    ds.valid_status AS valid_status,
+                    ds.global_tag AS global_tag,
+                    ds.parent AS parent
              FROM dbsbuffer_algo_dataset_assoc das
-             INNER JOIN dbsbuffer_dataset ds ON ds.id = das.dataset_id
-             WHERE das.id = :das
-             AND UPPER(ds.Path) NOT LIKE 'BOGUS'
+             INNER JOIN dbsbuffer_dataset ds ON
+               ds.id = das.dataset_id
+             INNER JOIN dbsbuffer_file dbsfile ON
+               dbsfile.dataset_algo = das.id AND
+               dbsfile.status = 'NOTUPLOADED'
+             WHERE UPPER(ds.Path) NOT LIKE 'BOGUS'
+             GROUP BY das.dataset_id,
+                      ds.Path,
+                      das.algo_id,
+                      das.in_dbs,
+                      das.id,
+                      ds.valid_status,
+                      ds.global_tag,
+                      ds.parent
              """
 
-
     def makeDAS(self, results):
         ret=[]
         for r in results:
@@ -62,18 +69,7 @@ class FindDASToUpload(DBFormatter):
 
     def execute(self, conn=None, transaction = False):
 
-        binds  = {}
-        query  = self.dbi.processData(self.querySQL, binds,
-                                      conn = conn, transaction = transaction)
-        qDict  = self.formatDict(query)
-        idList = []
-        for i in qDict:
-            idList.append({'das': i['dasid']})
-
-        if len(idList) < 1:
-            return []
-
-        result = self.dbi.processData(self.sql, idList, 
-                         conn = conn, transaction = transaction)
+        result = self.dbi.processData(self.sql, binds = {}, conn = conn,
+                                      transaction = transaction)
         
         return self.makeDAS(self.formatDict(result))
diff --git a/src/python/WMComponent/DBSUpload/Database/Oracle/FindDASToUpload.py b/src/python/WMComponent/DBSUpload/Database/Oracle/FindDASToUpload.py
index 2a0fe8e9f..ce74ca0d7 100644
--- a/src/python/WMComponent/DBSUpload/Database/Oracle/FindDASToUpload.py
+++ b/src/python/WMComponent/DBSUpload/Database/Oracle/FindDASToUpload.py
@@ -4,41 +4,10 @@
 This code should load the necessary information regarding
 dataset-algo combinations from the DBSBuffer.
 
-Oracle version
-
 """
-
-
-
-
 from WMComponent.DBSUpload.Database.MySQL.FindDASToUpload import FindDASToUpload as MySQLFindDASToUpload
 
 
 class FindDASToUpload(MySQLFindDASToUpload):
-    """
-    Find Uploadable DAS
-
-    """
-
+    pass
 
-    sql = """SELECT das1.dataset_id AS dataset, ds1.Path as Path, das1.algo_id as Algo, das1.in_dbs as in_dbs,
-               das1.id AS das_id,
-               da1.app_name AS ApplicationName, 
-               da1.app_ver AS ApplicationVersion, 
-               da1.app_fam AS ApplicationFamily, 
-               da1.PSet_Hash as PSetHash,
-               da1.Config_Content as PSetContent,
-               da1.in_dbs AS algo_in_dbs,
-               ds1.valid_status AS valid_status
-             FROM dbsbuffer_algo_dataset_assoc das1
-             INNER JOIN dbsbuffer_algo da1 ON da1.id = das1.algo_id
-             INNER JOIN dbsbuffer_dataset ds1 ON ds1.id = das1.dataset_id
-             WHERE das1.id IN
-             (SELECT DISTINCT das.id
-             FROM dbsbuffer_algo_dataset_assoc das
-             INNER JOIN dbsbuffer_dataset ds2 ON ds2.id = das.dataset_id
-             WHERE EXISTS (SELECT id FROM dbsbuffer_file dbsfile
-                            WHERE dbsfile.dataset_algo = das.id
-                            AND dbsfile.status = 'NOTUPLOADED')
-             AND UPPER(ds2.Path) NOT LIKE 'BOGUS')
-             """