@prakhar-gitlab / ai-investment-platform
Code Commits Issues Pulls Sessions CI/CD
main
main
java 172 lines 9.06 KB
Raw
1 package com.aiinvestment.research;
2
3 import org.junit.jupiter.api.Test;
4 import org.flywaydb.core.Flyway;
5 import org.springframework.beans.factory.annotation.Autowired;
6 import org.springframework.boot.test.context.SpringBootTest;
7 import org.springframework.jdbc.core.JdbcTemplate;
8 import org.springframework.test.context.ActiveProfiles;
9
10 import java.util.List;
11
12 import static org.assertj.core.api.Assertions.assertThat;
13 import static org.assertj.core.api.Assertions.assertThatThrownBy;
14
15 @SpringBootTest
16 @ActiveProfiles("test")
17 class ResearchFlywayMigrationTest {
18 @Autowired
19 private JdbcTemplate jdbcTemplate;
20
21 @Test
22 void flywayMigratesResearchSchemaFromEmptyDatabase() {
23 List<String> tables = jdbcTemplate.queryForList(
24 """
25 SELECT table_name
26 FROM information_schema.tables
27 WHERE table_schema = 'research'
28 ORDER BY table_name
29 """,
30 String.class
31 );
32
33 assertThat(tables).contains(
34 "flyway_schema_history_research",
35 "research_documents",
36 "research_events",
37 "research_event_sources",
38 "research_refresh_runs",
39 "research_acquisition_observations",
40 "global_shareholding_snapshots",
41 "global_shareholding_snapshot_values",
42 "global_financial_facts",
43 "global_structured_market_snapshots",
44 "global_market_price_observations",
45 "global_daily_market_bars",
46 "global_stock_rule_engine_results",
47 "market_trading_schedules",
48 "market_trading_calendar_exceptions"
49 );
50 assertThat(tables).doesNotContain("research_refresh_jobs");
51
52 List<String> financialFactColumns = jdbcTemplate.queryForList(
53 "SELECT column_name FROM information_schema.columns WHERE table_schema = 'research' AND table_name = 'global_financial_facts'",
54 String.class
55 );
56 assertThat(financialFactColumns).contains(
57 "instrument_id", "metric", "period_end", "period_type", "reporting_basis", "fact_value",
58 "source_provider", "source_identity", "source_url", "source_mode", "source_tier"
59 );
60
61 List<String> documentColumns = jdbcTemplate.queryForList(
62 "SELECT column_name FROM information_schema.columns WHERE table_schema = 'research' AND table_name = 'research_documents'",
63 String.class
64 );
65 assertThat(documentColumns).contains("document_subtype");
66
67 List<String> marketPriceColumns = jdbcTemplate.queryForList(
68 "SELECT column_name FROM information_schema.columns WHERE table_schema = 'research' AND table_name = 'global_market_price_observations'",
69 String.class
70 );
71 assertThat(marketPriceColumns).contains(
72 "instrument_id", "observed_at", "price", "currency",
73 "provider", "source_url", "retrieved_at"
74 );
75
76 List<String> ruleEngineColumns = jdbcTemplate.queryForList(
77 "SELECT column_name FROM information_schema.columns WHERE table_schema = 'research' AND table_name = 'global_stock_rule_engine_results'",
78 String.class
79 );
80 assertThat(ruleEngineColumns).contains(
81 "global_instrument_id", "rule_engine_version", "input_fingerprint",
82 "calculated_at", "input_as_of", "overall_score", "quality_score",
83 "opportunity_score", "risk_score", "confidence_score",
84 "decision_signal", "partial", "result_json"
85 );
86
87 String version = jdbcTemplate.queryForObject(
88 """
89 SELECT version
90 FROM research.flyway_schema_history_research
91 WHERE success = TRUE
92 ORDER BY installed_rank DESC
93 LIMIT 1
94 """,
95 String.class
96 );
97 assertThat(version).isEqualTo("13");
98 assertThat(tables).contains("global_opportunity_snapshot", "stock_recommendation_history",
99 "recommendation_current_state", "global_opportunity_top_selection", "recommendation_backtest_run");
100
101 Integer nseSessions = jdbcTemplate.queryForObject(
102 "SELECT count(*) FROM research.market_trading_schedules WHERE market_code = 'NSE'", Integer.class
103 );
104 assertThat(nseSessions).isEqualTo(5);
105 List<String> nseSchedule = jdbcTemplate.queryForList(
106 "SELECT mic || '|' || timezone || '|' || regular_open_time || '|' || regular_close_time "
107 + "FROM research.market_trading_schedules WHERE market_code = 'NSE'", String.class
108 );
109 assertThat(nseSchedule).allSatisfy(value -> assertThat(value).isEqualTo("XNSE|Asia/Kolkata|09:15:00|15:30:00"));
110 Integer exceptions = jdbcTemplate.queryForObject(
111 "SELECT count(*) FROM research.market_trading_calendar_exceptions", Integer.class
112 );
113 assertThat(exceptions).isZero();
114 }
115
116 @Test
117 void dailyBarBootstrapCreatesKeysIndexesDateTypesAndConstraints() throws Exception {
118 try (var connection = jdbcTemplate.getDataSource().getConnection()) {
119 var keys = new java.util.TreeMap<Short, String>();
120 try (var rows = connection.getMetaData().getPrimaryKeys(null, "research", "global_daily_market_bars")) {
121 while (rows.next()) keys.put(rows.getShort("KEY_SEQ"), rows.getString("COLUMN_NAME"));
122 }
123 assertThat(keys.values()).containsExactly("global_instrument_id", "trading_date", "provider");
124 var indexes = new java.util.HashSet<String>();
125 try (var rows = connection.getMetaData().getIndexInfo(null, "research", "global_daily_market_bars", false, false)) {
126 while (rows.next()) indexes.add(rows.getString("INDEX_NAME"));
127 }
128 assertThat(indexes).contains("idx_daily_market_bars_date_instrument");
129 try (var rows = connection.getMetaData().getColumns(null, "research", "global_daily_market_bars", "trading_date")) {
130 assertThat(rows.next()).isTrue();
131 assertThat(rows.getInt("DATA_TYPE")).isEqualTo(java.sql.Types.DATE);
132 }
133 }
134 String insert = """
135 INSERT INTO research.global_daily_market_bars
136 (global_instrument_id,trading_date,provider,currency,source_mode,source_url,retrieved_at,high_price,low_price,volume)
137 VALUES ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',DATE '2026-09-01','TEST','INR','REAL','https://example.test',CURRENT_TIMESTAMP,?,?,?)
138 """;
139 assertThatThrownBy(() -> jdbcTemplate.update(insert, 10, 20, 1)).isInstanceOf(org.springframework.dao.DataAccessException.class);
140 assertThatThrownBy(() -> jdbcTemplate.update(insert, 20, 10, -1)).isInstanceOf(org.springframework.dao.DataAccessException.class);
141 jdbcTemplate.update(insert, 20, 10, 0);
142 assertThatThrownBy(() -> jdbcTemplate.update(insert, 20, 10, 0)).isInstanceOf(org.springframework.dao.DataAccessException.class);
143 jdbcTemplate.update("DELETE FROM research.global_daily_market_bars WHERE global_instrument_id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'");
144 }
145
146 @Test
147 void existingVersionTenUpgradesAndRepeatedMigrationIsANoOp() {
148 // Optional disposable PostgreSQL database lets the same upgrade test run
149 // against the production dialect without changing application bootstrap.
150 String url = System.getProperty("dailyBarsUpgradeJdbcUrl",
151 "jdbc:h2:mem:dailyBarUpgrade;DB_CLOSE_DELAY=-1;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE");
152 String username = System.getProperty("dailyBarsUpgradeUsername", "sa");
153 String password = System.getProperty("dailyBarsUpgradePassword", "");
154 Flyway before = Flyway.configure().dataSource(url, username, password)
155 .schemas("research").defaultSchema("research").table("flyway_schema_history_research")
156 .target("10").load();
157 before.migrate();
158 JdbcTemplate existing = new JdbcTemplate(new org.springframework.jdbc.datasource.DriverManagerDataSource(url, username, password));
159 existing.update("""
160 INSERT INTO research.global_market_price_observations
161 (instrument_id,observed_at,price,provider,source_url,retrieved_at)
162 VALUES ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb',CURRENT_TIMESTAMP,100,'EXISTING','https://example.test',CURRENT_TIMESTAMP)
163 """);
164 Flyway upgrade = Flyway.configure().dataSource(url, username, password)
165 .schemas("research").defaultSchema("research").table("flyway_schema_history_research").load();
166 assertThat(upgrade.migrate().migrationsExecuted).isEqualTo(3);
167 assertThat(upgrade.migrate().migrationsExecuted).isZero();
168 assertThat(existing.queryForObject("SELECT COUNT(*) FROM research.global_daily_market_bars", Integer.class)).isZero();
169 assertThat(existing.queryForObject("SELECT COUNT(*) FROM research.global_market_price_observations", Integer.class)).isEqualTo(1);
170 }
171 }
172