1# Test methods with long descriptive names can omit docstrings 2# pylint: disable=missing-docstring 3 4import pickle 5import contextlib 6import unittest.mock 7import unittest 8import string 9 10import numpy as np 11from numpy.testing import assert_almost_equal 12 13from Orange.data.sql.backend.base import BackendError 14from Orange.data import filter, ContinuousVariable, DiscreteVariable, \ 15 StringVariable, TimeVariable, Table, Domain 16from Orange.data.sql.table import SqlTable 17from Orange.preprocess.discretize import EqualWidth 18from Orange.statistics.basic_stats import BasicStats, DomainBasicStats 19from Orange.statistics.contingency import Continuous, Discrete, get_contingencies 20from Orange.statistics.distribution import get_distributions 21from Orange.tests.sql.base import DataBaseTest as dbt 22 23 24class TestSqlTable(unittest.TestCase, dbt): 25 def setUpDB(self): 26 self.conn, self.iris = self.create_iris_sql_table() 27 28 def tearDownDB(self): 29 self.drop_iris_sql_table() 30 31 def float_variable(self, size): 32 return [i * .1 for i in range(size)] 33 34 def discrete_variable(self, size): 35 return ["mf"[i % 2] for i in range(size)] 36 37 def string_variable(self, size): 38 return string.ascii_letters[:size] 39 40 @contextlib.contextmanager 41 def sql_table_from_data(self, data, guess_values=True): 42 params, table_name = self.create_sql_table(data) 43 yield SqlTable(params, table_name, 44 inspect_values=guess_values) 45 46 self.drop_sql_table(table_name) 47 48 @dbt.run_on(["postgres"]) 49 def test_constructs_correct_attributes(self): 50 data = list(zip(self.float_variable(21), 51 self.discrete_variable(21), 52 self.string_variable(21))) 53 with self.sql_table_from_data(data) as table: 54 self.assertEqual(len(table.domain.variables), 2) 55 self.assertEqual(len(table.domain.metas), 1) 56 57 float_attr, discrete_attr = table.domain.variables 58 string_attr, = table.domain.metas 59 60 self.assertIsInstance(float_attr, ContinuousVariable) 61 self.assertEqual(float_attr.name, "col0") 62 self.assertTrue('"col0"' in float_attr.to_sql()) 63 64 self.assertIsInstance(discrete_attr, DiscreteVariable) 65 self.assertEqual(discrete_attr.name, "col1") 66 self.assertTrue('"col1"' in discrete_attr.to_sql()) 67 self.assertEqual(discrete_attr.values, ('f', 'm')) 68 69 self.assertIsInstance(string_attr, StringVariable) 70 self.assertEqual(string_attr.name, "col2") 71 self.assertTrue('"col2"' in string_attr.to_sql()) 72 73 @dbt.run_on(["postgres"]) 74 def test_make_attributes(self): 75 table1 = SqlTable(self.conn, self.iris) 76 table2 = SqlTable(self.conn, self.iris) 77 self.assertEqual(table1.domain[0], table2.domain[0]) 78 79 @dbt.run_on(["postgres", "mssql"]) 80 def test_len(self): 81 with self.sql_table_from_data(zip(self.float_variable(26))) as table: 82 self.assertEqual(len(table), 26) 83 84 with self.sql_table_from_data(zip(self.float_variable(0))) as table: 85 self.assertEqual(len(table), 0) 86 87 @dbt.run_on(["postgres", "mssql"]) 88 def test_bool(self): 89 with self.sql_table_from_data(()) as table: 90 self.assertEqual(bool(table), False) 91 with self.sql_table_from_data(zip(self.float_variable(1))) as table: 92 self.assertEqual(bool(table), True) 93 94 @dbt.run_on(["postgres", "mssql"]) 95 def test_len_with_filter(self): 96 data = zip(self.discrete_variable(26)) 97 with self.sql_table_from_data(data) as table: 98 self.assertEqual(len(table), 26) 99 100 filtered_table = filter.SameValue(table.domain[0], 'm')(table) 101 self.assertEqual(len(filtered_table), 13) 102 103 table.domain[0].add_value('x') 104 filtered_table = filter.SameValue(table.domain[0], 'x')(table) 105 self.assertEqual(len(filtered_table), 0) 106 107 @dbt.run_on(["postgres", "mssql"]) 108 def test_XY_small(self): 109 mat = np.random.randint(0, 2, (20, 3)) 110 conn, table_name = self.create_sql_table(mat) 111 sql_table = SqlTable(conn, table_name, 112 type_hints=Domain([], DiscreteVariable( 113 name='col2', values=('0', '1', '2')))) 114 assert_almost_equal(sql_table.X, mat[:, :2]) 115 assert_almost_equal(sql_table.Y.flatten(), mat[:, 2]) 116 self.drop_sql_table(table_name) 117 118 @dbt.run_on(["postgres", "mssql"]) 119 @unittest.mock.patch("Orange.data.sql.table.AUTO_DL_LIMIT", 100) 120 def test_XY_large(self): 121 from Orange.data.sql.table import AUTO_DL_LIMIT as DLL 122 mat = np.random.randint(0, 2, (DLL + 100, 3)) 123 conn, table_name = self.create_sql_table(mat) 124 sql_table = SqlTable(conn, table_name, 125 type_hints=Domain([], DiscreteVariable( 126 name='col2', values=('0', '1', '2')))) 127 self.assertRaises(ValueError, lambda: sql_table.X) 128 self.assertRaises(ValueError, lambda: sql_table.Y) 129 with self.assertRaises(ValueError): 130 sql_table.download_data(DLL + 10) 131 # Download partial data 132 sql_table.download_data(DLL + 10, partial=True) 133 assert_almost_equal(sql_table.X, mat[:DLL + 10, :2]) 134 assert_almost_equal(sql_table.Y.flatten()[:DLL + 10], mat[:DLL + 10, 2]) 135 # Download all data 136 sql_table.download_data() 137 assert_almost_equal(sql_table.X, mat[:, :2]) 138 assert_almost_equal(sql_table.Y.flatten(), mat[:, 2]) 139 self.drop_sql_table(table_name) 140 141 @dbt.run_on(["postgres", "mssql"]) 142 def test_download_data(self): 143 mat = np.random.randint(0, 2, (20, 3)) 144 conn, table_name = self.create_sql_table(mat) 145 for member in ('X', 'Y', 'metas', 'W', 'ids'): 146 sql_table = SqlTable(conn, table_name, 147 type_hints=Domain([], DiscreteVariable( 148 name='col2', values=('0', '1', '2')))) 149 self.assertFalse(getattr(sql_table, member) is None) 150 # has all necessary class members to create a standard Table 151 Table.from_table(sql_table.domain, sql_table) 152 self.drop_sql_table(table_name) 153 154 @dbt.run_on(["postgres", "mssql"]) 155 def test_query_all(self): 156 table = SqlTable(self.conn, self.iris, inspect_values=True) 157 results = list(table) 158 159 self.assertEqual(len(results), 150) 160 161 @dbt.run_on(["postgres", "mssql"]) 162 def test_unavailable_row(self): 163 table = SqlTable(self.conn, self.iris) 164 self.assertRaises(IndexError, lambda: table[151]) 165 166 @dbt.run_on(["postgres", "mssql"]) 167 def test_query_subset_of_attributes(self): 168 table = SqlTable(self.conn, self.iris) 169 attributes = [ 170 self._mock_attribute("sepal length"), 171 self._mock_attribute("sepal width"), 172 self._mock_attribute("double width", '2 * "sepal width"') 173 ] 174 results = list(table._query( 175 attributes 176 )) 177 178 self.assertSequenceEqual( 179 results[:5], 180 [(5.1, 3.5, 7.0), 181 (4.9, 3.0, 6.0), 182 (4.7, 3.2, 6.4), 183 (4.6, 3.1, 6.2), 184 (5.0, 3.6, 7.2)] 185 ) 186 187 @dbt.run_on(["postgres"]) 188 def test_query_subset_of_rows(self): 189 table = SqlTable(self.conn, self.iris) 190 all_results = list(table._query()) 191 192 results = list(table._query(rows=range(10))) 193 self.assertEqual(len(results), 10) 194 self.assertSequenceEqual(results, all_results[:10]) 195 196 results = list(table._query(rows=range(10))) 197 self.assertEqual(len(results), 10) 198 self.assertSequenceEqual(results, all_results[:10]) 199 200 results = list(table._query(rows=slice(None, 10))) 201 self.assertEqual(len(results), 10) 202 self.assertSequenceEqual(results, all_results[:10]) 203 204 results = list(table._query(rows=slice(10, None))) 205 self.assertEqual(len(results), 140) 206 self.assertSequenceEqual(results, all_results[10:]) 207 208 @dbt.run_on(["postgres", "mssql"]) 209 def test_getitem_single_value(self): 210 table = SqlTable(self.conn, self.iris, inspect_values=True) 211 self.assertAlmostEqual(table[0, 0], 5.1) 212 213 @dbt.run_on(["postgres", "mssql"]) 214 def test_type_hints(self): 215 table = SqlTable(self.conn, self.iris, inspect_values=True) 216 self.assertEqual(len(table.domain.variables), 5) 217 self.assertEqual(len(table.domain.metas), 0) 218 table = SqlTable(self.conn, self.iris, inspect_values=True, 219 type_hints=Domain([], [], metas=[ 220 StringVariable("iris")])) 221 self.assertEqual(len(table.domain.variables), 4) 222 self.assertEqual(len(table.domain.metas), 1) 223 224 @dbt.run_on(["postgres"]) 225 def test_joins(self): 226 table = SqlTable( 227 self.conn, 228 """SELECT a."sepal length", 229 b. "petal length", 230 CASE WHEN b."petal length" < 3 THEN '<' 231 ELSE '>' 232 END AS "qualitative petal length" 233 FROM iris a 234 INNER JOIN iris b ON a."sepal width" = b."sepal width" 235 WHERE a."petal width" < 1 236 ORDER BY a."sepal length", b. "petal length" ASC""", 237 type_hints=Domain([DiscreteVariable( 238 name="qualitative petal length", 239 values=('<', '>'))], [])) 240 241 self.assertEqual(len(table), 498) 242 self.assertAlmostEqual(list(table[497]), [5.8, 1.2, 0.]) 243 244 def _mock_attribute(self, attr_name, formula=None): 245 if formula is None: 246 formula = '"%s"' % attr_name 247 248 class Attr: 249 name = attr_name 250 251 @staticmethod 252 def to_sql(): 253 return formula 254 255 return Attr 256 257 @dbt.run_on(["postgres"]) 258 def test_universal_table(self): 259 _, table_name = self.construct_universal_table() 260 261 SqlTable(self.conn, """ 262 SELECT 263 v1.col2 as v1, 264 v2.col2 as v2, 265 v3.col2 as v3, 266 v4.col2 as v4, 267 v5.col2 as v5 268 FROM %(table_name)s v1 269 INNER JOIN %(table_name)s v2 ON v2.col0 = v1.col0 AND v2.col1 = 2 270 INNER JOIN %(table_name)s v3 ON v3.col0 = v2.col0 AND v3.col1 = 3 271 INNER JOIN %(table_name)s v4 ON v4.col0 = v1.col0 AND v4.col1 = 4 272 INNER JOIN %(table_name)s v5 ON v5.col0 = v1.col0 AND v5.col1 = 5 273 WHERE v1.col1 = 1 274 ORDER BY v1.col0 275 """ % dict(table_name='"%s"' % table_name)) 276 277 self.drop_sql_table(table_name) 278 279 def construct_universal_table(self): 280 values = [] 281 for row in range(1, 6): 282 for col in range(1, 6): 283 values.extend((row, col, row * col)) 284 table = Table.from_numpy(None, np.array(values).reshape((-1, 3))) 285 return self.create_sql_table(table) 286 287 IRIS_VARIABLE = DiscreteVariable( 288 "iris", values=('Iris-setosa', 'Iris-virginica', 'Iris-versicolor')) 289 290 @dbt.run_on(["postgres", "mssql"]) 291 def test_class_var_type_hints(self): 292 iris = SqlTable(self.conn, self.iris, 293 type_hints=Domain([], self.IRIS_VARIABLE)) 294 295 self.assertEqual(len(iris.domain.class_vars), 1) 296 self.assertEqual(iris.domain.class_vars[0].name, 'iris') 297 298 @dbt.run_on(["postgres", "mssql"]) 299 def test_meta_type_hints(self): 300 iris = SqlTable( 301 self.conn, 302 self.iris, 303 type_hints=Domain([], metas=[self.IRIS_VARIABLE]), 304 ) 305 306 self.assertEqual(len(iris.domain.metas), 1) 307 self.assertEqual(iris.domain.metas[0].name, "iris") 308 np.testing.assert_array_equal( 309 iris.metas.flatten(), [0] * 50 + [2] * 50 + [1] * 50 310 ) 311 312 @dbt.run_on(["postgres", "mssql"]) 313 def test_metas_type_hints(self): 314 iris = SqlTable(self.conn, self.iris, 315 type_hints=Domain([], [], metas=[self.IRIS_VARIABLE])) 316 317 self.assertEqual(len(iris.domain.metas), 1) 318 self.assertEqual(iris.domain.metas[0].name, 'iris') 319 320 @dbt.run_on(["postgres", "mssql"]) 321 def test_select_all(self): 322 iris = SqlTable(self.conn, "SELECT * FROM iris", 323 type_hints=Domain([], self.IRIS_VARIABLE)) 324 325 self.assertEqual(len(iris.domain.variables), 5) 326 327 @dbt.run_on(["postgres"]) 328 def test_discrete_bigint(self): 329 table = np.arange(6).reshape((-1, 1)) 330 conn, table_name = self.create_sql_table(table, ['bigint']) 331 332 sql_table = SqlTable(conn, table_name, inspect_values=False) 333 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 334 335 sql_table = SqlTable(conn, table_name, inspect_values=True) 336 self.assertFirstAttrIsInstance(sql_table, DiscreteVariable) 337 self.drop_sql_table(table_name) 338 339 @dbt.run_on(["postgres", "mssql"]) 340 def test_continous_bigint(self): 341 table = np.arange(25).reshape((-1, 1)) 342 conn, table_name = self.create_sql_table(table, ['bigint']) 343 344 sql_table = SqlTable(conn, table_name, inspect_values=False) 345 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 346 347 sql_table = SqlTable(conn, table_name, inspect_values=True) 348 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 349 self.drop_sql_table(table_name) 350 351 @dbt.run_on(["postgres"]) 352 def test_discrete_int(self): 353 table = np.arange(6).reshape((-1, 1)) 354 conn, table_name = self.create_sql_table(table, ['int']) 355 356 sql_table = SqlTable(conn, table_name, inspect_values=False) 357 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 358 359 sql_table = SqlTable(conn, table_name, inspect_values=True) 360 self.assertFirstAttrIsInstance(sql_table, DiscreteVariable) 361 self.drop_sql_table(table_name) 362 363 @dbt.run_on(["postgres", "mssql"]) 364 def test_continous_int(self): 365 table = np.arange(25).reshape((-1, 1)) 366 conn, table_name = self.create_sql_table(table, ['int']) 367 368 sql_table = SqlTable(conn, table_name, inspect_values=False) 369 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 370 371 sql_table = SqlTable(conn, table_name, inspect_values=True) 372 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 373 self.drop_sql_table(table_name) 374 375 @dbt.run_on(["postgres"]) 376 def test_discrete_smallint(self): 377 table = np.arange(6).reshape((-1, 1)) 378 conn, table_name = self.create_sql_table(table, ['smallint']) 379 380 sql_table = SqlTable(conn, table_name, inspect_values=False) 381 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 382 383 sql_table = SqlTable(conn, table_name, inspect_values=True) 384 self.assertFirstAttrIsInstance(sql_table, DiscreteVariable) 385 self.drop_sql_table(table_name) 386 387 @dbt.run_on(["postgres", "mssql"]) 388 def test_continous_smallint(self): 389 table = np.arange(25).reshape((-1, 1)) 390 conn, table_name = self.create_sql_table(table, ['smallint']) 391 392 sql_table = SqlTable(conn, table_name, inspect_values=False) 393 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 394 395 sql_table = SqlTable(conn, table_name, inspect_values=True) 396 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 397 self.drop_sql_table(table_name) 398 399 @dbt.run_on(["postgres"]) 400 def test_boolean(self): 401 table = np.array(['F', 'T', 0, 1, 'False', 'True']).reshape(-1, 1) 402 conn, table_name = self.create_sql_table(table, ['boolean']) 403 404 sql_table = SqlTable(conn, table_name, inspect_values=False) 405 self.assertFirstAttrIsInstance(sql_table, DiscreteVariable) 406 407 sql_table = SqlTable(conn, table_name, inspect_values=True) 408 self.assertFirstAttrIsInstance(sql_table, DiscreteVariable) 409 self.drop_sql_table(table_name) 410 411 @dbt.run_on(["postgres", "mssql"]) 412 def test_discrete_char(self): 413 table = np.array(['M', 'F', 'M', 'F', 'M', 'F']).reshape(-1, 1) 414 conn, table_name = self.create_sql_table(table, ['char(1)']) 415 416 sql_table = SqlTable(conn, table_name, inspect_values=False) 417 self.assertFirstMetaIsInstance(sql_table, StringVariable) 418 419 sql_table = SqlTable(conn, table_name, inspect_values=True) 420 self.assertFirstAttrIsInstance(sql_table, DiscreteVariable) 421 self.drop_sql_table(table_name) 422 423 @dbt.run_on(["postgres"]) 424 def test_discrete_bigger_char(self): 425 """Test if the discrete values are the same for bigger char fields""" 426 table = np.array(['M', 'F', 'M', 'F', 'M', 'F']).reshape(-1, 1) 427 conn, table_name = self.create_sql_table(table, ['char(10)']) 428 429 sql_table = SqlTable(conn, table_name, inspect_values=True) 430 self.assertSequenceEqual(sql_table.domain[0].values, ['F', 'M']) 431 self.drop_sql_table(table_name) 432 433 @dbt.run_on(["postgres", "mssql"]) 434 def test_meta_char(self): 435 table = np.array(list('ABCDEFGHIJKLMNOPQRSTUVW')).reshape(-1, 1) 436 conn, table_name = self.create_sql_table(table, ['char(1)']) 437 438 sql_table = SqlTable(conn, table_name, inspect_values=False) 439 self.assertFirstMetaIsInstance(sql_table, StringVariable) 440 441 sql_table = SqlTable(conn, table_name, inspect_values=True) 442 self.assertFirstMetaIsInstance(sql_table, StringVariable) 443 self.drop_sql_table(table_name) 444 445 # test if NULL is transformed to emtpy string 446 table = np.array(list("ABCDEFGHIJKLMNOPQRSTUVW") + [None]).reshape( 447 -1, 1 448 ) 449 conn, table_name = self.create_sql_table(table, ["char(1)"]) 450 451 sql_table = SqlTable(conn, table_name, inspect_values=False) 452 self.assertFirstMetaIsInstance(sql_table, StringVariable) 453 self.assertEqual("", sql_table.metas[-1, 0]) 454 455 sql_table = SqlTable(conn, table_name, inspect_values=True) 456 self.assertFirstMetaIsInstance(sql_table, StringVariable) 457 self.assertEqual("", sql_table.metas[-1, 0]) 458 self.drop_sql_table(table_name) 459 460 @dbt.run_on(["postgres", "mssql"]) 461 def test_discrete_varchar(self): 462 table = np.array(['M', 'F', 'M', 'F', 'M', 'F']).reshape(-1, 1) 463 conn, table_name = self.create_sql_table(table, ['varchar(1)']) 464 465 sql_table = SqlTable(conn, table_name, inspect_values=False) 466 self.assertFirstMetaIsInstance(sql_table, StringVariable) 467 468 sql_table = SqlTable(conn, table_name, inspect_values=True) 469 self.assertFirstAttrIsInstance(sql_table, DiscreteVariable) 470 self.drop_sql_table(table_name) 471 472 @dbt.run_on(["postgres", "mssql"]) 473 def test_meta_varchar(self): 474 table = np.array(list('ABCDEFGHIJKLMNOPQRSTUVW')).reshape(-1, 1) 475 conn, table_name = self.create_sql_table(table, ['varchar(1)']) 476 477 sql_table = SqlTable(conn, table_name, inspect_values=False) 478 self.assertFirstMetaIsInstance(sql_table, StringVariable) 479 480 sql_table = SqlTable(conn, table_name, inspect_values=True) 481 self.assertFirstMetaIsInstance(sql_table, StringVariable) 482 self.drop_sql_table(table_name) 483 484 @dbt.run_on(["postgres"]) 485 def test_time_date(self): 486 table = np.array(['2014-04-12', '2014-04-13', '2014-04-14', 487 '2014-04-15', '2014-04-16']).reshape(-1, 1) 488 conn, table_name = self.create_sql_table(table, ['date']) 489 490 sql_table = SqlTable(conn, table_name, inspect_values=False) 491 self.assertFirstAttrIsInstance(sql_table, TimeVariable) 492 493 sql_table = SqlTable(conn, table_name, inspect_values=True) 494 self.assertFirstAttrIsInstance(sql_table, TimeVariable) 495 496 @dbt.run_on(["postgres"]) 497 def test_time_time(self): 498 table = np.array(['17:39:51', '11:51:48.46', '05:20:21.492149', 499 '21:47:06', '04:47:35.8']).reshape(-1, 1) 500 conn, table_name = self.create_sql_table(table, ['time']) 501 502 sql_table = SqlTable(conn, table_name, inspect_values=False) 503 self.assertFirstAttrIsInstance(sql_table, TimeVariable) 504 505 sql_table = SqlTable(conn, table_name, inspect_values=True) 506 self.assertFirstAttrIsInstance(sql_table, TimeVariable) 507 self.drop_sql_table(table_name) 508 509 @dbt.run_on(["postgres"]) 510 def test_time_timetz(self): 511 table = np.array(['17:39:51+0200', '11:51:48.46+01', '05:20:21.4921', 512 '21:47:06-0600', '04:47:35.8+0330']).reshape(-1, 1) 513 conn, table_name = self.create_sql_table(table, ['timetz']) 514 515 sql_table = SqlTable(conn, table_name, inspect_values=False) 516 self.assertFirstAttrIsInstance(sql_table, TimeVariable) 517 518 sql_table = SqlTable(conn, table_name, inspect_values=True) 519 self.assertFirstAttrIsInstance(sql_table, TimeVariable) 520 self.drop_sql_table(table_name) 521 522 @dbt.run_on(["postgres"]) 523 def test_time_timestamp(self): 524 table = np.array(['2014-07-15 17:39:51.348149', 525 '2008-10-05 11:51:48.468149', 526 '2008-11-03 05:20:21.492149', 527 '2015-01-02 21:47:06.228149', 528 '2016-04-16 04:47:35.892149']).reshape(-1, 1) 529 conn, table_name = self.create_sql_table(table, ['timestamp']) 530 531 sql_table = SqlTable(conn, table_name, inspect_values=False) 532 self.assertFirstAttrIsInstance(sql_table, TimeVariable) 533 534 sql_table = SqlTable(conn, table_name, inspect_values=True) 535 self.assertFirstAttrIsInstance(sql_table, TimeVariable) 536 self.drop_sql_table(table_name) 537 538 @dbt.run_on(["postgres"]) 539 def test_time_timestamptz(self): 540 table = np.array(['2014-07-15 17:39:51.348149+0200', 541 '2008-10-05 11:51:48.468149+02', 542 '2008-11-03 05:20:21.492149+01', 543 '2015-01-02 21:47:06.228149+0100', 544 '2016-04-16 04:47:35.892149+0330']).reshape(-1, 1) 545 conn, table_name = self.create_sql_table(table, ['timestamptz']) 546 547 sql_table = SqlTable(conn, table_name, inspect_values=False) 548 self.assertFirstAttrIsInstance(sql_table, TimeVariable) 549 550 sql_table = SqlTable(conn, table_name, inspect_values=True) 551 self.assertFirstAttrIsInstance(sql_table, TimeVariable) 552 self.drop_sql_table(table_name) 553 554 @dbt.run_on(["postgres", "mssql"]) 555 def test_double_precision(self): 556 table = np.arange(25).reshape((-1, 1)) 557 conn, table_name = self.create_sql_table(table, ['double precision']) 558 559 sql_table = SqlTable(conn, table_name, inspect_values=False) 560 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 561 562 sql_table = SqlTable(conn, table_name, inspect_values=True) 563 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 564 self.drop_sql_table(table_name) 565 566 @dbt.run_on(["postgres", "mssql"]) 567 def test_numeric(self): 568 table = np.arange(25).reshape((-1, 1)) 569 conn, table_name = self.create_sql_table(table, ['numeric(15, 2)']) 570 571 sql_table = SqlTable(conn, table_name, inspect_values=False) 572 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 573 574 sql_table = SqlTable(conn, table_name, inspect_values=True) 575 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 576 self.drop_sql_table(table_name) 577 578 @dbt.run_on(["postgres", "mssql"]) 579 def test_real(self): 580 table = np.arange(25).reshape((-1, 1)) 581 conn, table_name = self.create_sql_table(table, ['real']) 582 583 sql_table = SqlTable(conn, table_name, inspect_values=False) 584 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 585 586 sql_table = SqlTable(conn, table_name, inspect_values=True) 587 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 588 self.drop_sql_table(table_name) 589 590 @dbt.run_on(["postgres"]) 591 def test_serial(self): 592 table = np.arange(25).reshape((-1, 1)) 593 conn, table_name = self.create_sql_table(table, ['serial']) 594 595 sql_table = SqlTable(conn, table_name, inspect_values=False) 596 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 597 598 sql_table = SqlTable(conn, table_name, inspect_values=True) 599 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 600 self.drop_sql_table(table_name) 601 602 @dbt.run_on(["postgres>90200"]) 603 def test_smallserial(self): 604 table = np.arange(25).reshape((-1, 1)) 605 conn, table_name = self.create_sql_table(table, ['smallserial']) 606 607 sql_table = SqlTable(conn, table_name, inspect_values=False) 608 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 609 610 sql_table = SqlTable(conn, table_name, inspect_values=True) 611 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 612 self.drop_sql_table(table_name) 613 614 @dbt.run_on(["postgres>90200"]) 615 def test_bigserial(self): 616 table = np.arange(25).reshape((-1, 1)) 617 conn, table_name = self.create_sql_table(table, ['bigserial']) 618 619 sql_table = SqlTable(conn, table_name, inspect_values=False) 620 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 621 622 sql_table = SqlTable(conn, table_name, inspect_values=True) 623 self.assertFirstAttrIsInstance(sql_table, ContinuousVariable) 624 self.drop_sql_table(table_name) 625 626 @dbt.run_on(["postgres"]) 627 def test_text(self): 628 table = np.array(list('ABCDEFGHIJKLMNOPQRSTUVW')).reshape((-1, 1)) 629 conn, table_name = self.create_sql_table(table, ['text']) 630 631 sql_table = SqlTable(conn, table_name, inspect_values=False) 632 self.assertFirstMetaIsInstance(sql_table, StringVariable) 633 634 sql_table = SqlTable(conn, table_name, inspect_values=True) 635 self.assertFirstMetaIsInstance(sql_table, StringVariable) 636 self.drop_sql_table(table_name) 637 638 @dbt.run_on(["postgres"]) 639 def test_other(self): 640 table = np.array(['bcd4d9c0-361e-bad4-7ceb-0d171cdec981', 641 '544b7ddc-d861-0201-81c8-9f7ad0bbf531', 642 'b35a10f7-7901-f313-ec16-5ad9778040a6', 643 'b267c4be-4a26-60b5-e664-737a90a40e93']).reshape(-1, 1) 644 conn, table_name = self.create_sql_table(table, ['uuid']) 645 646 sql_table = SqlTable(conn, table_name, inspect_values=False) 647 self.assertFirstMetaIsInstance(sql_table, StringVariable) 648 649 sql_table = SqlTable(conn, table_name, inspect_values=True) 650 self.assertFirstMetaIsInstance(sql_table, StringVariable) 651 652 filters = filter.Values([filter.FilterString(-1, filter.FilterString.Equal, 'foo')]) 653 self.assertEqual(len(filters(sql_table)), 0) 654 self.drop_sql_table(table_name) 655 656 @dbt.run_on(["postgres", "mssql"]) 657 def test_recovers_connection_after_sql_error(self): 658 conn, table_name = self.create_sql_table( 659 np.arange(25).reshape((-1, 1))) 660 sql_table = SqlTable(conn, table_name) 661 662 try: 663 broken_query = "SELECT 1/%s FROM %s" % ( 664 sql_table.domain.attributes[0].to_sql(), sql_table.table_name) 665 with sql_table.backend.execute_sql_query(broken_query) as cur: 666 cur.fetchall() 667 except BackendError: 668 pass 669 670 working_query = "SELECT %s FROM %s" % ( 671 sql_table.domain.attributes[0].to_sql(), sql_table.table_name) 672 with sql_table.backend.execute_sql_query(working_query) as cur: 673 cur.fetchall() 674 self.drop_sql_table(table_name) 675 676 @dbt.run_on(["postgres"]) 677 def test_basic_stats(self): 678 iris = SqlTable(self.conn, self.iris, inspect_values=True) 679 stats = BasicStats(iris, iris.domain['sepal length']) 680 self.assertAlmostEqual(stats.min, 4.3) 681 self.assertAlmostEqual(stats.max, 7.9) 682 self.assertAlmostEqual(stats.mean, 5.8, 1) 683 self.assertEqual(stats.nans, 0) 684 self.assertEqual(stats.non_nans, 150) 685 686 domain_stats = DomainBasicStats(iris, include_metas=True) 687 self.assertEqual(len(domain_stats.stats), 688 len(iris.domain.variables) + len(iris.domain.metas)) 689 stats = domain_stats['sepal length'] 690 self.assertAlmostEqual(stats.min, 4.3) 691 self.assertAlmostEqual(stats.max, 7.9) 692 self.assertAlmostEqual(stats.mean, 5.8, 1) 693 self.assertEqual(stats.nans, 0) 694 self.assertEqual(stats.non_nans, 150) 695 696 @dbt.run_on(["postgres"]) 697 @unittest.mock.patch("Orange.data.sql.table.LARGE_TABLE", 100) 698 def test_basic_stats_on_large_data(self): 699 # By setting LARGE_TABLE to 100, iris will be treated as 700 # a large table and sampling will be used. As the table 701 # is actually small, time base sampling should return 702 # all rows, so the same assertions can be used. 703 iris = SqlTable(self.conn, self.iris, inspect_values=True) 704 stats = BasicStats(iris, iris.domain['sepal length']) 705 self.assertAlmostEqual(stats.min, 4.3) 706 self.assertAlmostEqual(stats.max, 7.9) 707 self.assertAlmostEqual(stats.mean, 5.8, 1) 708 self.assertEqual(stats.nans, 0) 709 self.assertEqual(stats.non_nans, 150) 710 711 domain_stats = DomainBasicStats(iris, include_metas=True) 712 self.assertEqual(len(domain_stats.stats), 713 len(iris.domain.variables) + len(iris.domain.metas)) 714 stats = domain_stats['sepal length'] 715 self.assertAlmostEqual(stats.min, 4.3) 716 self.assertAlmostEqual(stats.max, 7.9) 717 self.assertAlmostEqual(stats.mean, 5.8, 1) 718 self.assertEqual(stats.nans, 0) 719 self.assertEqual(stats.non_nans, 150) 720 721 @dbt.run_on(["postgres", "mssql"]) 722 def test_distributions(self): 723 iris = SqlTable(self.conn, self.iris, inspect_values=True) 724 725 dists = get_distributions(iris) 726 self.assertEqual(len(dists), 5) 727 dist = dists[0] 728 self.assertAlmostEqual(dist.min(), 4.3) 729 self.assertAlmostEqual(dist.max(), 7.9) 730 self.assertAlmostEqual(dist.mean(), 5.8, 1) 731 732 @dbt.run_on(["postgres"]) 733 def test_contingencies(self): 734 iris = SqlTable(self.conn, self.iris, inspect_values=True) 735 iris.domain = Domain(iris.domain[2:4] + (EqualWidth()(iris, iris.domain['sepal width']),), 736 iris.domain['iris']) 737 738 conts = get_contingencies(iris) 739 self.assertEqual(len(conts), 3) 740 self.assertIsInstance(conts[0], Continuous) 741 self.assertIsInstance(conts[1], Continuous) 742 self.assertIsInstance(conts[2], Discrete) 743 744 @dbt.run_on(["postgres"]) 745 def test_pickling_restores_connection_pool(self): 746 iris = SqlTable(self.conn, self.iris, inspect_values=True) 747 iris2 = pickle.loads(pickle.dumps(iris)) 748 749 self.assertEqual(iris[0], iris2[0]) 750 751 @dbt.run_on(["postgres"]) 752 def test_list_tables_with_schema(self): 753 with self.backend.execute_sql_query("DROP SCHEMA IF EXISTS orange_tests CASCADE") as cur: 754 cur.execute("CREATE SCHEMA orange_tests") 755 cur.execute("CREATE TABLE orange_tests.efgh (id int)") 756 cur.execute("INSERT INTO orange_tests.efgh (id) VALUES (1)") 757 cur.execute("INSERT INTO orange_tests.efgh (id) VALUES (2)") 758 759 try: 760 tables = self.backend.list_tables("orange_tests") 761 self.assertTrue(any([t.name == "efgh" for t in tables])) 762 SqlTable(self.conn, tables[0], inspect_values=True) 763 finally: 764 with self.backend.execute_sql_query("DROP SCHEMA IF EXISTS orange_tests CASCADE"): 765 pass 766 767 def assertFirstAttrIsInstance(self, table, variable_type): 768 self.assertGreater(len(table.domain.variables), 0) 769 attr = table.domain[0] 770 self.assertIsInstance(attr, variable_type) 771 772 def assertFirstMetaIsInstance(self, table, variable_type): 773 self.assertGreater(len(table.domain.metas), 0) 774 attr = table.domain[-1] 775 self.assertIsInstance(attr, variable_type) 776 777 778if __name__ == "__main__": 779 unittest.main() 780