Coverage for tests/test_postgres.py: 22%

1# This file is part of felis. 

2# 

3# Developed for the LSST Data Management System. 

4# This product includes software developed by the LSST Project 

5# (https://www.lsst.org). 

6# See the COPYRIGHT file at the top-level directory of this distribution 

7# for details of code ownership. 

8# 

9# This program is free software: you can redistribute it and/or modify 

10# it under the terms of the GNU General Public License as published by 

11# the Free Software Foundation, either version 3 of the License, or 

12# (at your option) any later version. 

13# 

14# This program is distributed in the hope that it will be useful, 

15# but WITHOUT ANY WARRANTY; without even the implied warranty of 

16# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 

17# GNU General Public License for more details. 

18# 

19# You should have received a copy of the GNU General Public License 

20# along with this program. If not, see <https://www.gnu.org/licenses/>. 

21 

22import os 

23import unittest 

24 

25import yaml 

26from sqlalchemy import text 

27 

28from felis.datamodel import Schema 

29from felis.db.utils import DatabaseContext 

30from felis.metadata import MetaDataBuilder 

31from felis.tests.postgresql import TemporaryPostgresInstance, setup_postgres_test_db # type: ignore 

32 

33TESTDIR = os.path.abspath(os.path.dirname(__file__)) 

34TEST_YAML = os.path.join(TESTDIR, "data", "sales.yaml") 

35 

36 

37class TestPostgresql(unittest.TestCase): 

38 """Test PostgreSQL database setup.""" 

39 

40 postgresql: TemporaryPostgresInstance 

41 

42 @classmethod 

43 def setUpClass(cls) -> None: 

44 # Create the postgres test server. 

45 cls.postgresql = cls.enterClassContext(setup_postgres_test_db()) 

46 super().setUpClass() 

47 

48 def test_initialize_create_and_drop(self) -> None: 

49 """Test database initialization, creation, and deletion in 

50 PostgreSQL. 

51 """ 

52 # Create the schema and metadata 

53 yaml_data = yaml.safe_load(open(TEST_YAML)) 

54 schema = Schema.model_validate(yaml_data) 

55 md = MetaDataBuilder(schema).build() 

56 

57 # Initialize the database 

58 ctx = DatabaseContext(md, self.postgresql.engine) 

59 ctx.initialize() 

60 ctx.create_all() 

61 

62 # Get the names of the tables without the schema prepended 

63 table_names = [name.split(".")[-1] for name in md.tables.keys()] 

64 

65 # Check that the tables and columns are created 

66 with self.postgresql.begin() as conn: 

67 res = conn.execute(text("SELECT table_name FROM information_schema.tables")) 

68 tables = [row[0] for row in res.fetchall()] 

69 for table_name in table_names: 

70 self.assertIn(table_name, tables) 

71 # Check that all columns are created 

72 expected_columns = [col.name for col in md.tables[f"sales.{table_name}"].columns] 

73 res = conn.execute( 

74 text("SELECT column_name FROM information_schema.columns WHERE table_name = :table_name"), 

75 {"table_name": table_name}, 

76 ) 

77 actual_columns = [row[0] for row in res.fetchall()] 

78 self.assertSetEqual(set(expected_columns), set(actual_columns)) 

79 

80 # Drop the schema 

81 ctx.drop() 

82 

83 # Check that the "sales" schema was dropped 

84 with self.postgresql.begin() as conn: 

85 res = conn.execute( 

86 text("SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'sales'") 

87 ) 

88 schemas = [row[0] for row in res.fetchall()] 

89 self.assertNotIn("sales", schemas) 

90 

91 def test_create_and_drop_indexes(self) -> None: 

92 """Test creating and dropping indexes separately from table 

93 creation. 

94 """ 

95 # Load the schema 

96 yaml_data = yaml.safe_load(open(TEST_YAML)) 

97 schema = Schema.model_validate(yaml_data) 

98 

99 # Create metadata without indexes 

100 md_no_indexes = MetaDataBuilder(schema, skip_indexes=True).build() 

101 

102 # Initialize the database and create tables (without indexes) 

103 ctx = DatabaseContext(md_no_indexes, self.postgresql.engine) 

104 ctx.initialize() 

105 ctx.create_all() 

106 

107 # Create metadata with indexes to get the index definitions 

108 md_with_indexes = MetaDataBuilder(schema, skip_indexes=False).build() 

109 ctx_with_indexes = DatabaseContext(md_with_indexes, self.postgresql.engine) 

110 

111 def check_indexes_exist(should_exist: bool, message: str) -> None: 

112 """Check if indexes exist or don't exist in the database.""" 

113 with self.postgresql.begin() as conn: 

114 for table in md_with_indexes.tables.values(): 

115 for index in table.indexes: 

116 if index.name is not None: 

117 exists = DatabaseContext._index_exists(conn, table, index) 

118 if should_exist: 

119 self.assertTrue( 

120 exists, 

121 f"Index '{index.name}' {message}", 

122 ) 

123 else: 

124 self.assertFalse( 

125 exists, 

126 f"Index '{index.name}' {message}", 

127 ) 

128 

129 # Check that indexes don't exist yet 

130 check_indexes_exist(False, "should not exist yet") 

131 

132 # Create the indexes 

133 ctx_with_indexes.create_indexes() 

134 

135 # Check that indexes now exist 

136 check_indexes_exist(True, "should exist after creation") 

137 

138 # Create the indexes again; should not raise an error 

139 ctx_with_indexes.create_indexes() 

140 

141 # Drop the indexes 

142 ctx_with_indexes.drop_indexes() 

143 

144 # Check that indexes were dropped 

145 check_indexes_exist(False, "should not exist after dropping") 

146 

147 # Clean up: drop the schema 

148 ctx.drop()