class UploadTestEdgeCase(unittest.TestCase): """Upload Feature specific Test Cases will go here""" def setUp(self): """Setup test app""" self.app = create_app('tests.config') self.db = DbConnect(self.app.config) def tearDown(self): """Close test database""" self.db.close() def cleanUpLoggerTemp(self, cursor): ''' clean up table cnx_logger_temperature''' cursor.execute("SELECT logger_temp_id FROM `cnx_logger_temperature`") results = cursor.fetchall() if results is not None: results = list(results) logger_temp_ids = [result[0] for result in results] for logger_temp_id in logger_temp_ids: res = cursor.execute( "DELETE FROM `cnx_logger_temperature` WHERE logger_temp_id=\'%s\'" % (logger_temp_id)) self.db.connection.commit() self.cleanUpMetadataTable(cursor) def cleanUpLoggerType(self, cursor, rec): ''' clean up logger type tables''' biomimic_id = self.db.fetch_existing_bio_id(cursor, rec.get('biomimic_type')) geo_id = self.db.fetch_existing_geo_id(cursor, rec) prop_id = self.db.fetch_existing_prop_id(cursor, rec) logger_id = self.db.find_microsite_id(rec.get('microsite_id')) res = cursor.execute("DELETE FROM `cnx_logger` WHERE logger_id=%s" % (logger_id)) self.db.connection.commit() res = cursor.execute( "DELETE FROM `cnx_logger_biomimic_type` WHERE biomimic_id=%s", biomimic_id) self.db.connection.commit() res = cursor.execute( "DELETE FROM `cnx_logger_geographics` WHERE geo_id=%s", geo_id) self.db.connection.commit() res = cursor.execute( "DELETE FROM `cnx_logger_properties` WHERE prop_id=%s", prop_id) self.db.connection.commit() def cleanUpMetadataTable(self, cursor): ''' clean up table cnx_logger_metadata''' cursor.execute("SELECT logger_id FROM `cnx_logger_metadata`") results = cursor.fetchall() if results is not None: results = list(results) logger_ids = [result[0] for result in results] for logger_id in logger_ids: res = cursor.execute( "DELETE FROM `cnx_logger_metadata` WHERE logger_id=\'%s\'", (logger_id, )) self.db.connection.commit() def test_logger_type_upload_MicrositeId_None(self): """Test that upload Logger Type file without microsite_id will not be inserted to database""" test_filename = 'server/tests/test_data_files/Test/Test_New_Logger_Type_MicrositeId_None.csv' with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post( '/upload', data={ 'loggerTypeFile': (open(test_filename, 'rb'), 'Test_New_Logger_Type_MicrositeId_None.csv') }, follow_redirects=True) query = ( "SELECT * from cnx_logger_biomimic_type where biomimic_type='DummyBiomimicTypeNone'" ) cursor = self.db.connection.cursor() cursor.execute(query) results = cursor.fetchall() results = list(results) self.assertEqual(len(results), 0) def test_logger_temperature_upload_duplicate(self): """Test that Logger Temperature file with duplicate entry cannot be uploaded""" test_type_filename = 'server/tests/test_data_files/Test/Test_New_Logger_Type_Positive.csv' test_temp_filename = 'server/tests/test_data_files/Test/temp_files/DUMMYID_2000_pgsql_Duplicate.txt' with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTypeFile': (open(test_type_filename, 'rb'), 'Test_New_Logger_Type_Positive.csv') }, follow_redirects=True) response = client.post('/upload', data={ 'loggerTempFile': (open(test_temp_filename, 'rb'), 'DUMMYID_2000_pgsql_Duplicate.txt') }, follow_redirects=True) record_type = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave", "start_date": str(datetime.strptime("7/1/2000", '%m/%d/%Y').date()), "end_date": str(datetime.strptime("7/2/2000", '%m/%d/%Y').date()) } where_condition = self.db.build_where_condition(record_type) query = ( "SELECT temp.Time_GMT, temp.Temp_C " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " "INNER JOIN `cnx_logger_temperature` temp ON temp.`logger_id` = logger.`logger_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() results = list(results) self.cleanUpLoggerTemp(cursor) self.cleanUpLoggerType(cursor, record_type) cursor.close() self.assertEqual(len(results), 1)
class UploadTestCase(unittest.TestCase): """Upload Feature specific Test Cases will go here""" def setUp(self): """Setup test app""" self.app = create_app("tests.config") self.db = DbConnect(self.app.config) def tearDown(self): """Close test database""" self.db.close() def clean_up_logger_temp(self, cursor): """ clean up table cnx_logger_temperature""" cursor.execute("SELECT logger_temp_id FROM `cnx_logger_temperature`") results = cursor.fetchall() if results is not None: results = list(results) logger_temp_ids = [result[0] for result in results] for logger_temp_id in logger_temp_ids: res = cursor.execute("DELETE FROM `cnx_logger_temperature` WHERE logger_temp_id=%s", (logger_temp_id,)) self.db.connection.commit() self.clean_up_metadata_table(cursor) def clean_up_logger_type(self, cursor, rec): """ clean up logger type tables""" biomimic_id = self.db.fetch_existing_bio_id(cursor, rec.get("biomimic_type")) geo_id = self.db.fetch_existing_geo_id(cursor, rec) prop_id = self.db.fetch_existing_prop_id(cursor, rec) logger_id = self.db.find_microsite_id(rec.get("microsite_id")) res = cursor.execute("DELETE FROM `cnx_logger` WHERE logger_id=%s", (logger_id,)) self.db.connection.commit() res = cursor.execute("DELETE FROM `cnx_logger_biomimic_type` WHERE biomimic_id=%s", (biomimic_id,)) self.db.connection.commit() res = cursor.execute("DELETE FROM `cnx_logger_geographics` WHERE geo_id=%s", (geo_id,)) self.db.connection.commit() res = cursor.execute("DELETE FROM `cnx_logger_properties` WHERE prop_id=%s", (prop_id,)) self.db.connection.commit() def clean_up_metadata_table(self, cursor): """ clean up table cnx_logger_metadata""" cursor.execute("SELECT logger_id FROM `cnx_logger_metadata`") results = cursor.fetchall() if results is not None: results = list(results) logger_ids = [result[0] for result in results] for logger_id in logger_ids: res = cursor.execute("DELETE FROM `cnx_logger_metadata` WHERE logger_id='%s'", (logger_id,)) self.db.connection.commit() def build_type_where_condition(self, queryDict): """Builds the where_condition for the Select Query""" where = """ WHERE biotype.`biomimic_type`=\'%s\' AND geo.`country`=\'%s\' AND geo.`state_province`= \'%s\' AND geo.`location`=\'%s\'""" % ( queryDict.get("biomimic_type"), queryDict.get("country"), queryDict.get("state_province"), queryDict.get("location"), ) if queryDict.get("zone") != "All": where += " AND prop.`zone`='%s'" % (queryDict.get("zone")) if queryDict.get("sub_zone") != "All": where += " AND prop.`sub_zone`='%s'" % (queryDict.get("sub_zone")) if queryDict.get("wave_exp") != "All": if queryDict.get("wave_exp") == "None": where += " and prop.wave_exp is Null" else: where += " AND prop.`wave_exp`='%s' " % (queryDict.get("wave_exp")) return where def test_uploaded_logger_type_file_extension(self): """Test that uploaded logger type file has correct extensions""" with self.app.test_client() as client: with client.session_transaction() as sess: sess["logged_in"] = True response = client.post( "/upload", data={"loggerTypeFile": (BytesIO(b"logger Type File"), "correctExtensionLoggerTypeFile.csv")}, follow_redirects=True, ) self.assertNotIn(b"File correctExtensionLoggerTypeFile.csv should be in csv format", response.data) def test_uploaded_logger_temp_file_extension(self): """Test that uploaded logger temperature file has correct extensions""" with self.app.test_client() as client: with client.session_transaction() as sess: sess["logged_in"] = True response = client.post( "/upload", data={"loggerTempFile": (BytesIO(b"logger Temp File"), "correctExtensionLoggerTempFile.csv")}, follow_redirects=True, ) self.assertNotIn(b"File correctExtensionLoggerTempFile.pdf should be in csv or txt format", response.data) def test_uploaded_logger_type_file_extension_negative(self): """Test that uploaded logger type file has correct extensions""" with self.app.test_client() as client: with client.session_transaction() as sess: sess["logged_in"] = True response = client.post( "/upload", data={"loggerTypeFile": (BytesIO(b"logger Type File"), "incorrectExtensionLoggerTypeFile.txt")}, follow_redirects=True, ) self.assertIn(b"File incorrectExtensionLoggerTypeFile.txt should be in csv format", response.data) def test_uploaded_logger_temp_file_extension_negative(self): """Test that uploaded logger temperature file has correct extensions""" with self.app.test_client() as client: with client.session_transaction() as sess: sess["logged_in"] = True response = client.post( "/upload", data={"loggerTempFile": (BytesIO(b"logger Temp File"), "incorrectExtensionLoggerTypeFile.pdf")}, follow_redirects=True, ) self.assertIn(b"File incorrectExtensionLoggerTypeFile.pdf should be in csv or txt format", response.data) def test_uploaded_logger_type_file_missing(self): """Test that uploaded logger type file is not missing""" with self.app.test_client() as client: with client.session_transaction() as sess: sess["logged_in"] = True response = client.post( "/upload", data={"loggerTypeFile": (BytesIO(b"logger Type File"), "")}, follow_redirects=True ) self.assertIn(b"Please choose a file first", response.data) def test_uploaded_logger_temp_file_missing(self): """Test that uploaded logger temp file is not missing""" with self.app.test_client() as client: with client.session_transaction() as sess: sess["logged_in"] = True response = client.post( "/upload", data={"loggerTempFile": (BytesIO(b"logger Temp File"), "")}, follow_redirects=True ) self.assertIn(b"Please choose a file first", response.data) def test_logger_type_upload(self): """Test that file with valid Type uploads is inserted in DB.""" test_filename = "server/tests/test_data_files/Test/Test_New_Logger_Type_Positive.csv" microsite_id = None with self.app.test_client() as client: with client.session_transaction() as sess: sess["logged_in"] = True response = client.post( "/upload", data={"loggerTypeFile": (open(test_filename, "rb"), "Test_New_Logger_Type_Positive.csv")}, follow_redirects=True, ) record = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave", } where_condition = self.build_type_where_condition(record) query = ( "SELECT log.microsite_id " "FROM `cnx_logger` log " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = log.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = log.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = log.`prop_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchone() self.clean_up_logger_type(cursor, record) cursor.close() if results is not None: microsite_id = results[0] self.assertEqual(record["microsite_id"], microsite_id) self.assertIn(b"<td># Proper Records</td>\n <td>1</td>", response.data) self.assertIn(b"<td># Corrupt Records</td>\n <td>0</td>", response.data) def test_logger_type_upload_corrupt(self): """Test that Logger Type file with corrupt records cannot be uploaded""" test_filename = "server/tests/test_data_files/Test/Test_New_Logger_Type_Corrupt.csv" with self.app.test_client() as client: with client.session_transaction() as sess: sess["logged_in"] = True response = client.post( "/upload", data={"loggerTypeFile": (open(test_filename, "rb"), "Test_New_Logger_Type_Corrupt.csv")}, follow_redirects=True, ) record_corrupt_ncolumns = { "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave", } where_condition = self.build_type_where_condition(record_corrupt_ncolumns) query = ( "SELECT logger.microsite_id " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() results = list(results) self.assertEqual(len(results), 0) record_corrupt_coordinates = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "A36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave", } where_condition = self.build_type_where_condition(record_corrupt_coordinates) query = ( "SELECT logger.microsite_id " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() results = list(results) self.assertEqual(len(results), 0) cursor.close() def test_logger_type_upload_duplicate(self): """Test that Logger Type file with duplicate Microsite Id cannot be uploaded""" test_filename = "server/tests/test_data_files/Test/Test_New_Logger_Type_Duplicate.csv" with self.app.test_client() as client: with client.session_transaction() as sess: sess["logged_in"] = True response = client.post( "/upload", data={"loggerTypeFile": (open(test_filename, "rb"), "Test_New_Logger_Type_Duplicate.csv")}, follow_redirects=True, ) record = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave", } where_condition = self.build_type_where_condition(record) query = ( "SELECT logger.microsite_id " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() results = list(results) self.clean_up_logger_type(cursor, record) cursor.close() self.assertEqual(len(results), 1) def test_logger_temperature_upload(self): """Test that file with valid Temperature uploads is inserted in DB.""" test_type_filename = "server/tests/test_data_files/Test/Test_New_Logger_Type_Positive.csv" test_temp_filename = "server/tests/test_data_files/Test/temp_files/DUMMYID_2000_pgsql.txt" with self.app.test_client() as client: with client.session_transaction() as sess: sess["logged_in"] = True response = client.post( "/upload", data={"loggerTypeFile": (open(test_type_filename, "rb"), "Test_New_Logger_Type_Positive.csv")}, follow_redirects=True, ) response = client.post( "/upload", data={"loggerTempFile": (open(test_temp_filename, "rb"), "DUMMYID_2000_pgsql.txt")}, follow_redirects=True, ) record_type = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave", "start_date": str(datetime.strptime("7/1/2000", "%m/%d/%Y").date()), "end_date": str(datetime.strptime("7/2/2000", "%m/%d/%Y").date()), } record_temp = [{"Time_GMT": "7/1/2000 2:01", "Temp_C": 14}, {"Time_GMT": "7/1/2000 2:21", "Temp_C": 13.5}] where_condition = self.db.build_where_condition(record_type) query = ( "SELECT DATE_FORMAT(temp.Time_GMT,'%m/%d/%Y %H:%i'), temp.Temp_C " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " "INNER JOIN `cnx_logger_temperature` temp ON temp.`logger_id` = logger.`logger_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() if results is not None: results = list(results) cursor.close() self.assertEqual( datetime.strptime(record_temp[0]["Time_GMT"], "%m/%d/%Y %H:%M"), datetime.strptime(results[0][0], "%m/%d/%Y %H:%M"), ) self.assertEqual(record_temp[0]["Temp_C"], results[0][1]) self.assertEqual( datetime.strptime(record_temp[1]["Time_GMT"], "%m/%d/%Y %H:%M"), datetime.strptime(results[1][0], "%m/%d/%Y %H:%M"), ) self.assertEqual(record_temp[1]["Temp_C"], results[1][1]) self.assertIn(b"<td># Proper Records</td>\n <td>6</td>", response.data) self.assertIn(b"<td># Corrupt Records</td>\n <td>0</td>", response.data) query = """SELECT SUM(meta.logger_count), MIN(meta.logger_min_date), MAX(meta.logger_max_date) FROM `cnx_logger_metadata` meta INNER JOIN `cnx_logger` log ON log.`logger_id`=meta.`logger_id` WHERE log.`microsite_id`=%s""" cursor = self.db.connection.cursor() cursor.execute(query, (record_type["microsite_id"],)) results = cursor.fetchone() if results is not None: count = results[0] min_date = results[1] max_date = results[2] self.clean_up_logger_temp(cursor) self.clean_up_logger_type(cursor, record_type) cursor.close() self.assertEqual(count, 6) self.assertEqual(min_date, datetime(2000, 7, 1, 2, 1)) self.assertEqual(max_date, datetime(2002, 8, 16, 9, 41)) def test_logger_temperature_upload_corrupt(self): """Test that Logger Temperature file with corrupt records cannot be uploaded""" test_type_filename = "server/tests/test_data_files/Test/Test_New_Logger_Type_Positive.csv" test_temp_filename = "server/tests/test_data_files/Test/temp_files/DUMMYID_2000_corrupt.csv" with self.app.test_client() as client: with client.session_transaction() as sess: sess["logged_in"] = True response = client.post( "/upload", data={"loggerTypeFile": (open(test_type_filename, "rb"), "Test_New_Logger_Type_Positive.csv")}, follow_redirects=True, ) response = client.post( "/upload", data={"loggerTempFile": (open(test_temp_filename, "rb"), "DUMMYID_2000_corrupt.txt")}, follow_redirects=True, ) record_type = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave", "start_date": str(datetime.strptime("7/1/2000", "%m/%d/%Y").date()), "end_date": str(datetime.strptime("7/2/2000", "%m/%d/%Y").date()), } where_condition = self.db.build_where_condition(record_type) query = ( "SELECT temp.Time_GMT, temp.Temp_C " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " "INNER JOIN `cnx_logger_temperature` temp ON temp.`logger_id` = logger.`logger_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() results = list(results) self.clean_up_logger_type(cursor, record_type) cursor.close() self.assertEqual(len(results), 0) def test_logger_temperature_upload_missing(self): """Test that Logger Temperature file with missing Microsite Id cannot be uploaded""" test_type_filename = "server/tests/test_data_files/Test/Test_New_Logger_Type_Positive.csv" test_temp_filename = "server/tests/test_data_files/Test/temp_files/DUMMYID2_2000_Missing_Type.txt" with self.app.test_client() as client: with client.session_transaction() as sess: sess["logged_in"] = True response = client.post( "/upload", data={"loggerTypeFile": (open(test_type_filename, "rb"), "Test_New_Logger_Type_Positive.csv")}, follow_redirects=True, ) response = client.post( "/upload", data={"loggerTempFile": (open(test_temp_filename, "rb"), "DUMMYID2_2000_Missing_Type.txt")}, follow_redirects=True, ) record_type = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave", "start_date": str(datetime.strptime("7/1/2000", "%m/%d/%Y").date()), "end_date": str(datetime.strptime("7/2/2000", "%m/%d/%Y").date()), } where_condition = self.db.build_where_condition(record_type) query = ( "SELECT temp.Time_GMT, temp.Temp_C " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " "INNER JOIN `cnx_logger_temperature` temp ON temp.`logger_id` = logger.`logger_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() results = list(results) self.clean_up_logger_type(cursor, record_type) cursor.close() self.assertEqual(len(results), 0) def test_logger_metadata_update(self): """Test that metadata table gets updated with subsequent inserts in DB.""" test_type_filename = "server/tests/test_data_files/Test/Test_New_Logger_Type_Positive.csv" test_temp_filename = "server/tests/test_data_files/Test/temp_files/DUMMYID_2000_pgsql.txt" test_temp_filename2 = "server/tests/test_data_files/Test/temp_files/DUMMYID_2001_pgsql.txt" with self.app.test_client() as client: with client.session_transaction() as sess: sess["logged_in"] = True response = client.post( "/upload", data={"loggerTypeFile": (open(test_type_filename, "rb"), "Test_New_Logger_Type_Positive.csv")}, follow_redirects=True, ) response = client.post( "/upload", data={"loggerTempFile": (open(test_temp_filename, "rb"), "DUMMYID_2000_pgsql.txt")}, follow_redirects=True, ) response = client.post( "/upload", data={"loggerTempFile": (open(test_temp_filename2, "rb"), "DUMMYID_2001_pgsql.txt")}, follow_redirects=True, ) record_type = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave", "start_date": str(datetime.strptime("7/1/2000", "%m/%d/%Y").date()), "end_date": str(datetime.strptime("7/2/2000", "%m/%d/%Y").date()), } query = """SELECT SUM(meta.logger_count), MIN(meta.logger_min_date), MAX(meta.logger_max_date) FROM `cnx_logger_metadata` meta INNER JOIN `cnx_logger` log ON log.`logger_id`=meta.`logger_id` WHERE log.`microsite_id`=%s""" cursor = self.db.connection.cursor() cursor.execute(query, (record_type["microsite_id"],)) results = cursor.fetchone() if results is not None: count = results[0] min_date = results[1] max_date = results[2] self.clean_up_logger_temp(cursor) self.clean_up_logger_type(cursor, record_type) cursor.close() self.assertEqual(count, 12) self.assertEqual(min_date, datetime(2000, 7, 1, 2, 1)) self.assertEqual(max_date, datetime(2006, 8, 16, 9, 41))
class UploadTestCase(unittest.TestCase): """Upload Feature specific Test Cases will go here""" def setUp(self): """Setup test app""" self.app = create_app('tests.config') self.db = DbConnect(self.app.config) def tearDown(self): """Close test database""" self.db.close() def clean_up_logger_temp(self, cursor): ''' clean up table cnx_logger_temperature''' cursor.execute("SELECT logger_temp_id FROM `cnx_logger_temperature`") results = cursor.fetchall() if results is not None: results = list(results) logger_temp_ids = [result[0] for result in results] for logger_temp_id in logger_temp_ids: res = cursor.execute( "DELETE FROM `cnx_logger_temperature` WHERE logger_temp_id=%s", (logger_temp_id, )) self.db.connection.commit() self.clean_up_metadata_table(cursor) def clean_up_logger_type(self, cursor, rec): ''' clean up logger type tables''' biomimic_id = self.db.fetch_existing_bio_id(cursor, rec.get('biomimic_type')) geo_id = self.db.fetch_existing_geo_id(cursor, rec) prop_id = self.db.fetch_existing_prop_id(cursor, rec) logger_id = self.db.find_microsite_id(rec.get('microsite_id')) res = cursor.execute("DELETE FROM `cnx_logger` WHERE logger_id=%s", (logger_id, )) self.db.connection.commit() res = cursor.execute( "DELETE FROM `cnx_logger_biomimic_type` WHERE biomimic_id=%s", (biomimic_id, )) self.db.connection.commit() res = cursor.execute( "DELETE FROM `cnx_logger_geographics` WHERE geo_id=%s", (geo_id, )) self.db.connection.commit() res = cursor.execute( "DELETE FROM `cnx_logger_properties` WHERE prop_id=%s", (prop_id, )) self.db.connection.commit() def clean_up_metadata_table(self, cursor): ''' clean up table cnx_logger_metadata''' cursor.execute("SELECT logger_id FROM `cnx_logger_metadata`") results = cursor.fetchall() if results is not None: results = list(results) logger_ids = [result[0] for result in results] for logger_id in logger_ids: res = cursor.execute( "DELETE FROM `cnx_logger_metadata` WHERE logger_id=\'%s\'", (logger_id, )) self.db.connection.commit() def build_type_where_condition(self, queryDict): """Builds the where_condition for the Select Query""" where = """ WHERE biotype.`biomimic_type`=\'%s\' AND geo.`country`=\'%s\' AND geo.`state_province`= \'%s\' AND geo.`location`=\'%s\'""" % \ (queryDict.get('biomimic_type'), queryDict.get('country'), \ queryDict.get('state_province'), queryDict.get('location')) if queryDict.get('zone') != "All": where += " AND prop.`zone`=\'%s\'" % (queryDict.get('zone')) if queryDict.get('sub_zone') != "All": where += " AND prop.`sub_zone`=\'%s\'" % ( queryDict.get('sub_zone')) if queryDict.get('wave_exp') != "All": if (queryDict.get('wave_exp') == 'None'): where += " and prop.wave_exp is Null" else: where += " AND prop.`wave_exp`=\'%s\' " % ( queryDict.get('wave_exp')) return where def test_uploaded_logger_type_file_extension(self): """Test that uploaded logger type file has correct extensions""" with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTypeFile': (BytesIO(b'logger Type File'), 'correctExtensionLoggerTypeFile.csv') }, follow_redirects=True) self.assertNotIn( b'File correctExtensionLoggerTypeFile.csv should be in csv format', response.data) def test_uploaded_logger_temp_file_extension(self): """Test that uploaded logger temperature file has correct extensions""" with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTempFile': (BytesIO(b'logger Temp File'), 'correctExtensionLoggerTempFile.csv') }, follow_redirects=True) self.assertNotIn( b'File correctExtensionLoggerTempFile.pdf should be in csv or txt format', response.data) def test_uploaded_logger_type_file_extension_negative(self): """Test that uploaded logger type file has correct extensions""" with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTypeFile': (BytesIO(b'logger Type File'), 'incorrectExtensionLoggerTypeFile.txt') }, follow_redirects=True) self.assertIn( b'File incorrectExtensionLoggerTypeFile.txt should be in csv format', response.data) def test_uploaded_logger_temp_file_extension_negative(self): """Test that uploaded logger temperature file has correct extensions""" with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTempFile': (BytesIO(b'logger Temp File'), 'incorrectExtensionLoggerTypeFile.pdf') }, follow_redirects=True) self.assertIn( b'File incorrectExtensionLoggerTypeFile.pdf should be in csv or txt format', response.data) def test_uploaded_logger_type_file_missing(self): """Test that uploaded logger type file is not missing""" with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post( '/upload', data={'loggerTypeFile': (BytesIO(b'logger Type File'), '')}, follow_redirects=True) self.assertIn(b'Please choose a file first', response.data) def test_uploaded_logger_temp_file_missing(self): """Test that uploaded logger temp file is not missing""" with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post( '/upload', data={'loggerTempFile': (BytesIO(b'logger Temp File'), '')}, follow_redirects=True) self.assertIn(b'Please choose a file first', response.data) def test_logger_type_upload(self): """Test that file with valid Type uploads is inserted in DB.""" test_filename = 'server/tests/test_data_files/Test/Test_New_Logger_Type_Positive.csv' microsite_id = None with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTypeFile': (open(test_filename, 'rb'), 'Test_New_Logger_Type_Positive.csv') }, follow_redirects=True) record = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave" } where_condition = self.build_type_where_condition(record) query = ( "SELECT log.microsite_id " "FROM `cnx_logger` log " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = log.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = log.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = log.`prop_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchone() self.clean_up_logger_type(cursor, record) cursor.close() if results is not None: microsite_id = results[0] self.assertEqual(record['microsite_id'], microsite_id) self.assertIn( b"<td># Proper Records</td>\n <td>1</td>", response.data) self.assertIn( b"<td># Corrupt Records</td>\n <td>0</td>", response.data) def test_logger_type_upload_corrupt(self): """Test that Logger Type file with corrupt records cannot be uploaded""" test_filename = 'server/tests/test_data_files/Test/Test_New_Logger_Type_Corrupt.csv' with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTypeFile': (open(test_filename, 'rb'), 'Test_New_Logger_Type_Corrupt.csv') }, follow_redirects=True) record_corrupt_ncolumns = { "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave" } where_condition = self.build_type_where_condition( record_corrupt_ncolumns) query = ( "SELECT logger.microsite_id " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() results = list(results) self.assertEqual(len(results), 0) record_corrupt_coordinates = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "A36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave" } where_condition = self.build_type_where_condition( record_corrupt_coordinates) query = ( "SELECT logger.microsite_id " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() results = list(results) self.assertEqual(len(results), 0) cursor.close() def test_logger_type_upload_duplicate(self): """Test that Logger Type file with duplicate Microsite Id cannot be uploaded""" test_filename = 'server/tests/test_data_files/Test/Test_New_Logger_Type_Duplicate.csv' with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTypeFile': (open(test_filename, 'rb'), 'Test_New_Logger_Type_Duplicate.csv') }, follow_redirects=True) record = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave" } where_condition = self.build_type_where_condition(record) query = ( "SELECT logger.microsite_id " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() results = list(results) self.clean_up_logger_type(cursor, record) cursor.close() self.assertEqual(len(results), 1) def test_logger_temperature_upload(self): """Test that file with valid Temperature uploads is inserted in DB.""" test_type_filename = 'server/tests/test_data_files/Test/Test_New_Logger_Type_Positive.csv' test_temp_filename = 'server/tests/test_data_files/Test/temp_files/DUMMYID_2000_pgsql.txt' with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTypeFile': (open(test_type_filename, 'rb'), 'Test_New_Logger_Type_Positive.csv') }, follow_redirects=True) response = client.post('/upload', data={ 'loggerTempFile': (open(test_temp_filename, 'rb'), 'DUMMYID_2000_pgsql.txt') }, follow_redirects=True) record_type = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave", "start_date": str(datetime.strptime("7/1/2000", '%m/%d/%Y').date()), "end_date": str(datetime.strptime("7/2/2000", '%m/%d/%Y').date()) } record_temp = [{ "Time_GMT": "7/1/2000 2:01", "Temp_C": 14 }, { "Time_GMT": "7/1/2000 2:21", "Temp_C": 13.5 }] where_condition = self.db.build_where_condition(record_type) query = ( "SELECT DATE_FORMAT(temp.Time_GMT,'%m/%d/%Y %H:%i'), temp.Temp_C " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " "INNER JOIN `cnx_logger_temperature` temp ON temp.`logger_id` = logger.`logger_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() if results is not None: results = list(results) cursor.close() self.assertEqual( datetime.strptime(record_temp[0]['Time_GMT'], '%m/%d/%Y %H:%M'), datetime.strptime(results[0][0], '%m/%d/%Y %H:%M')) self.assertEqual(record_temp[0]['Temp_C'], results[0][1]) self.assertEqual( datetime.strptime(record_temp[1]['Time_GMT'], '%m/%d/%Y %H:%M'), datetime.strptime(results[1][0], '%m/%d/%Y %H:%M')) self.assertEqual(record_temp[1]['Temp_C'], results[1][1]) self.assertIn( b"<td># Proper Records</td>\n <td>6</td>", response.data) self.assertIn( b"<td># Corrupt Records</td>\n <td>0</td>", response.data) query = ( """SELECT SUM(meta.logger_count), MIN(meta.logger_min_date), MAX(meta.logger_max_date) FROM `cnx_logger_metadata` meta INNER JOIN `cnx_logger` log ON log.`logger_id`=meta.`logger_id` WHERE log.`microsite_id`=%s""") cursor = self.db.connection.cursor() cursor.execute(query, (record_type["microsite_id"], )) results = cursor.fetchone() if results is not None: count = results[0] min_date = results[1] max_date = results[2] self.clean_up_logger_temp(cursor) self.clean_up_logger_type(cursor, record_type) cursor.close() self.assertEqual(count, 6) self.assertEqual(min_date, datetime(2000, 7, 1, 2, 1)) self.assertEqual(max_date, datetime(2002, 8, 16, 9, 41)) def test_logger_temperature_upload_corrupt(self): """Test that Logger Temperature file with corrupt records cannot be uploaded""" test_type_filename = 'server/tests/test_data_files/Test/Test_New_Logger_Type_Positive.csv' test_temp_filename = 'server/tests/test_data_files/Test/temp_files/DUMMYID_2000_corrupt.csv' with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTypeFile': (open(test_type_filename, 'rb'), 'Test_New_Logger_Type_Positive.csv') }, follow_redirects=True) response = client.post('/upload', data={ 'loggerTempFile': (open(test_temp_filename, 'rb'), 'DUMMYID_2000_corrupt.txt') }, follow_redirects=True) record_type = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave", "start_date": str(datetime.strptime("7/1/2000", '%m/%d/%Y').date()), "end_date": str(datetime.strptime("7/2/2000", '%m/%d/%Y').date()) } where_condition = self.db.build_where_condition(record_type) query = ( "SELECT temp.Time_GMT, temp.Temp_C " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " "INNER JOIN `cnx_logger_temperature` temp ON temp.`logger_id` = logger.`logger_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() results = list(results) self.clean_up_logger_type(cursor, record_type) cursor.close() self.assertEqual(len(results), 0) def test_logger_temperature_upload_missing(self): """Test that Logger Temperature file with missing Microsite Id cannot be uploaded""" test_type_filename = 'server/tests/test_data_files/Test/Test_New_Logger_Type_Positive.csv' test_temp_filename = 'server/tests/test_data_files/Test/temp_files/DUMMYID2_2000_Missing_Type.txt' with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTypeFile': (open(test_type_filename, 'rb'), 'Test_New_Logger_Type_Positive.csv') }, follow_redirects=True) response = client.post('/upload', data={ 'loggerTempFile': (open(test_temp_filename, 'rb'), 'DUMMYID2_2000_Missing_Type.txt') }, follow_redirects=True) record_type = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave", "start_date": str(datetime.strptime("7/1/2000", '%m/%d/%Y').date()), "end_date": str(datetime.strptime("7/2/2000", '%m/%d/%Y').date()) } where_condition = self.db.build_where_condition(record_type) query = ( "SELECT temp.Time_GMT, temp.Temp_C " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " "INNER JOIN `cnx_logger_temperature` temp ON temp.`logger_id` = logger.`logger_id` " ) cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() results = list(results) self.clean_up_logger_type(cursor, record_type) cursor.close() self.assertEqual(len(results), 0) def test_logger_metadata_update(self): """Test that metadata table gets updated with subsequent inserts in DB.""" test_type_filename = 'server/tests/test_data_files/Test/Test_New_Logger_Type_Positive.csv' test_temp_filename = 'server/tests/test_data_files/Test/temp_files/DUMMYID_2000_pgsql.txt' test_temp_filename2 = 'server/tests/test_data_files/Test/temp_files/DUMMYID_2001_pgsql.txt' with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTypeFile': (open(test_type_filename, 'rb'), 'Test_New_Logger_Type_Positive.csv') }, follow_redirects=True) response = client.post('/upload', data={ 'loggerTempFile': (open(test_temp_filename, 'rb'), 'DUMMYID_2000_pgsql.txt') }, follow_redirects=True) response = client.post('/upload', data={ 'loggerTempFile': (open(test_temp_filename2, 'rb'), 'DUMMYID_2001_pgsql.txt') }, follow_redirects=True) record_type = { "microsite_id": "DUMMYID", "site": "DUMMYSITE", "biomimic_type": "Dummybiomimictype", "country": "Dummycountry", "state_province": "Dummystate", "location": "Dummylocation", "field_lat": "36.621933330000", "field_lon": "-121.905316700000", "zone": "DummyZone", "sub_zone": "DummySubZone", "wave_exp": "DummyWave", "start_date": str(datetime.strptime("7/1/2000", '%m/%d/%Y').date()), "end_date": str(datetime.strptime("7/2/2000", '%m/%d/%Y').date()) } query = ( """SELECT SUM(meta.logger_count), MIN(meta.logger_min_date), MAX(meta.logger_max_date) FROM `cnx_logger_metadata` meta INNER JOIN `cnx_logger` log ON log.`logger_id`=meta.`logger_id` WHERE log.`microsite_id`=%s""") cursor = self.db.connection.cursor() cursor.execute(query, (record_type["microsite_id"], )) results = cursor.fetchone() if results is not None: count = results[0] min_date = results[1] max_date = results[2] self.clean_up_logger_temp(cursor) self.clean_up_logger_type(cursor, record_type) cursor.close() self.assertEqual(count, 12) self.assertEqual(min_date, datetime(2000, 7, 1, 2, 1)) self.assertEqual(max_date, datetime(2006, 8, 16, 9, 41))
class UploadTestEdgeCase(unittest.TestCase): """Upload Feature specific Test Cases will go here""" def setUp(self): """Setup test app""" self.app = create_app('tests.config') self.db = DbConnect(self.app.config) def tearDown(self): """Close test database""" self.db.close() def cleanUpLoggerTemp(self, cursor): ''' clean up table cnx_logger_temperature''' cursor.execute("SELECT logger_temp_id FROM `cnx_logger_temperature`") results = cursor.fetchall() if results is not None: results = list(results) logger_temp_ids = [result[0] for result in results] for logger_temp_id in logger_temp_ids: res = cursor.execute("DELETE FROM `cnx_logger_temperature` WHERE logger_temp_id=\'%s\'" % (logger_temp_id)) self.db.connection.commit() self.cleanUpMetadataTable(cursor) def cleanUpLoggerType(self, cursor, rec): ''' clean up logger type tables''' biomimic_id = self.db.fetch_existing_bio_id(cursor, rec.get('biomimic_type')) geo_id = self.db.fetch_existing_geo_id(cursor, rec) prop_id = self.db.fetch_existing_prop_id(cursor, rec) logger_id = self.db.find_microsite_id(rec.get('microsite_id')) res = cursor.execute("DELETE FROM `cnx_logger` WHERE logger_id=%s" % (logger_id)) self.db.connection.commit() res = cursor.execute("DELETE FROM `cnx_logger_biomimic_type` WHERE biomimic_id=%s", biomimic_id) self.db.connection.commit() res = cursor.execute("DELETE FROM `cnx_logger_geographics` WHERE geo_id=%s", geo_id) self.db.connection.commit() res = cursor.execute("DELETE FROM `cnx_logger_properties` WHERE prop_id=%s", prop_id) self.db.connection.commit() def cleanUpMetadataTable(self, cursor): ''' clean up table cnx_logger_metadata''' cursor.execute("SELECT logger_id FROM `cnx_logger_metadata`") results = cursor.fetchall() if results is not None: results = list(results) logger_ids = [result[0] for result in results] for logger_id in logger_ids: res = cursor.execute("DELETE FROM `cnx_logger_metadata` WHERE logger_id=\'%s\'", (logger_id,)) self.db.connection.commit() def test_logger_type_upload_MicrositeId_None(self): """Test that upload Logger Type file without microsite_id will not be inserted to database""" test_filename = 'server/tests/test_data_files/Test/Test_New_Logger_Type_MicrositeId_None.csv' with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTypeFile': (open(test_filename, 'rb'), 'Test_New_Logger_Type_MicrositeId_None.csv') }, follow_redirects=True) query = ("SELECT * from cnx_logger_biomimic_type where biomimic_type='DummyBiomimicTypeNone'") cursor = self.db.connection.cursor() cursor.execute(query) results = cursor.fetchall() results = list(results) self.assertEqual(len(results), 0) def test_logger_temperature_upload_duplicate(self): """Test that Logger Temperature file with duplicate entry cannot be uploaded""" test_type_filename = 'server/tests/test_data_files/Test/Test_New_Logger_Type_Positive.csv' test_temp_filename = 'server/tests/test_data_files/Test/temp_files/DUMMYID_2000_pgsql_Duplicate.txt' with self.app.test_client() as client: with client.session_transaction() as sess: sess['logged_in'] = True response = client.post('/upload', data={ 'loggerTypeFile': (open(test_type_filename, 'rb'), 'Test_New_Logger_Type_Positive.csv') }, follow_redirects=True) response = client.post('/upload', data={ 'loggerTempFile': (open(test_temp_filename, 'rb'), 'DUMMYID_2000_pgsql_Duplicate.txt') }, follow_redirects=True) record_type = { "microsite_id" : "DUMMYID", "site" : "DUMMYSITE", "biomimic_type" : "Dummybiomimictype", "country" : "Dummycountry", "state_province" : "Dummystate", "location" : "Dummylocation", "field_lat" : "36.621933330000", "field_lon" : "-121.905316700000", "zone" : "DummyZone", "sub_zone" : "DummySubZone", "wave_exp" : "DummyWave", "start_date": str(datetime.strptime("7/1/2000",'%m/%d/%Y').date()), "end_date": str(datetime.strptime("7/2/2000",'%m/%d/%Y').date())} where_condition = self.db.build_where_condition(record_type) query = ("SELECT temp.Time_GMT, temp.Temp_C " "FROM `cnx_logger` logger " "INNER JOIN `cnx_logger_biomimic_type` biotype ON biotype.`biomimic_id` = logger.`biomimic_id` " "INNER JOIN `cnx_logger_geographics` geo ON geo.`geo_id` = logger.`geo_id` " "INNER JOIN `cnx_logger_properties` prop ON prop.`prop_id` = logger.`prop_id` " "INNER JOIN `cnx_logger_temperature` temp ON temp.`logger_id` = logger.`logger_id` ") cursor = self.db.connection.cursor() cursor.execute(query + where_condition) results = cursor.fetchall() results = list(results) self.cleanUpLoggerTemp(cursor) self.cleanUpLoggerType(cursor, record_type) cursor.close() self.assertEqual(len(results), 1)