ig_client/storage/
market_persistence.rs

1use chrono::{DateTime, Utc};
2use serde::{Deserialize, Serialize};
3use sqlx::FromRow;
4
5/// Represents a market hierarchy node in the database
6/// This structure is optimized for PostgreSQL storage with proper indexing
7#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
8pub struct MarketHierarchyNode {
9    /// Unique identifier for the node
10    pub id: String,
11    /// Human-readable name of the node
12    pub name: String,
13    /// Parent node ID (NULL for root nodes)
14    pub parent_id: Option<String>,
15    /// Exchange name (e.g., "IG")
16    pub exchange: String,
17    /// Depth level in the hierarchy (0 for root nodes)
18    pub level: i32,
19    /// Full path from root to this node (e.g., "/Indices/Europe/Germany")
20    pub path: String,
21    /// Timestamp when this record was created
22    pub created_at: DateTime<Utc>,
23    /// Timestamp when this record was last updated
24    pub updated_at: DateTime<Utc>,
25}
26
27/// Represents a market instrument in the database
28/// This structure is optimized for PostgreSQL storage with proper indexing
29#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
30pub struct MarketInstrument {
31    /// Unique identifier for the market (epic)
32    pub epic: String,
33    /// Human-readable name of the instrument
34    pub instrument_name: String,
35    /// Type of the instrument (e.g., "SHARES", "INDICES", "CURRENCIES")
36    pub instrument_type: String,
37    /// Node ID where this instrument belongs
38    pub node_id: String,
39    /// Exchange name (e.g., "IG")
40    pub exchange: String,
41    /// Expiry date of the instrument (empty string for perpetual instruments)
42    pub expiry: String,
43    /// Upper price limit for the market
44    pub high_limit_price: Option<f64>,
45    /// Lower price limit for the market
46    pub low_limit_price: Option<f64>,
47    /// Current status of the market
48    pub market_status: String,
49    /// Net change in price since previous close
50    pub net_change: Option<f64>,
51    /// Percentage change in price since previous close
52    pub percentage_change: Option<f64>,
53    /// Time of the last price update
54    pub update_time: Option<String>,
55    /// Time of the last price update in UTC
56    pub update_time_utc: Option<DateTime<Utc>>,
57    /// Current bid price
58    pub bid: Option<f64>,
59    /// Current offer/ask price
60    pub offer: Option<f64>,
61    /// Timestamp when this record was created
62    pub created_at: DateTime<Utc>,
63    /// Timestamp when this record was last updated
64    pub updated_at: DateTime<Utc>,
65}
66
67/// SQL DDL statements for creating the required tables
68pub const CREATE_MARKET_HIERARCHY_TABLE: &str = r#"
69CREATE TABLE IF NOT EXISTS market_hierarchy_nodes (
70    id VARCHAR(255) PRIMARY KEY,
71    name VARCHAR(500) NOT NULL,
72    parent_id VARCHAR(255) REFERENCES market_hierarchy_nodes(id),
73    exchange VARCHAR(50) NOT NULL,
74    level INTEGER NOT NULL DEFAULT 0,
75    path TEXT NOT NULL,
76    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
77    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
78);
79
80-- Indexes for efficient querying
81CREATE INDEX IF NOT EXISTS idx_market_hierarchy_parent_id ON market_hierarchy_nodes(parent_id);
82CREATE INDEX IF NOT EXISTS idx_market_hierarchy_exchange ON market_hierarchy_nodes(exchange);
83CREATE INDEX IF NOT EXISTS idx_market_hierarchy_level ON market_hierarchy_nodes(level);
84CREATE INDEX IF NOT EXISTS idx_market_hierarchy_path ON market_hierarchy_nodes USING gin(to_tsvector('english', path));
85CREATE INDEX IF NOT EXISTS idx_market_hierarchy_name ON market_hierarchy_nodes USING gin(to_tsvector('english', name));
86"#;
87
88/// SQL statement to create the market_instruments table with all necessary columns and indexes.
89/// This table stores detailed information about financial instruments including pricing data,
90/// market status, and relationships to hierarchy nodes.
91pub const CREATE_MARKET_INSTRUMENTS_TABLE: &str = r#"
92CREATE TABLE IF NOT EXISTS market_instruments (
93    epic VARCHAR(255) PRIMARY KEY,
94    instrument_name VARCHAR(500) NOT NULL,
95    instrument_type VARCHAR(100) NOT NULL,
96    node_id VARCHAR(255) NOT NULL REFERENCES market_hierarchy_nodes(id),
97    exchange VARCHAR(50) NOT NULL,
98    expiry VARCHAR(50) NOT NULL DEFAULT '',
99    high_limit_price DOUBLE PRECISION,
100    low_limit_price DOUBLE PRECISION,
101    market_status VARCHAR(50) NOT NULL,
102    net_change DOUBLE PRECISION,
103    percentage_change DOUBLE PRECISION,
104    update_time VARCHAR(50),
105    update_time_utc TIMESTAMPTZ,
106    bid DOUBLE PRECISION,
107    offer DOUBLE PRECISION,
108    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
109    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
110);
111
112-- Indexes for efficient querying
113CREATE INDEX IF NOT EXISTS idx_market_instruments_node_id ON market_instruments(node_id);
114CREATE INDEX IF NOT EXISTS idx_market_instruments_exchange ON market_instruments(exchange);
115CREATE INDEX IF NOT EXISTS idx_market_instruments_type ON market_instruments(instrument_type);
116CREATE INDEX IF NOT EXISTS idx_market_instruments_status ON market_instruments(market_status);
117CREATE INDEX IF NOT EXISTS idx_market_instruments_name ON market_instruments USING gin(to_tsvector('english', instrument_name));
118CREATE INDEX IF NOT EXISTS idx_market_instruments_epic ON market_instruments(epic);
119CREATE INDEX IF NOT EXISTS idx_market_instruments_expiry ON market_instruments(expiry);
120"#;
121
122/// Trigger to automatically update the updated_at timestamp
123pub const CREATE_UPDATE_TIMESTAMP_TRIGGER: &str = r#"
124-- Function to update the updated_at timestamp
125CREATE OR REPLACE FUNCTION update_updated_at_column()
126RETURNS TRIGGER AS $$
127BEGIN
128    NEW.updated_at = NOW();
129    RETURN NEW;
130END;
131$$ language 'plpgsql';
132
133-- Triggers for both tables
134DROP TRIGGER IF EXISTS update_market_hierarchy_nodes_updated_at ON market_hierarchy_nodes;
135CREATE TRIGGER update_market_hierarchy_nodes_updated_at
136    BEFORE UPDATE ON market_hierarchy_nodes
137    FOR EACH ROW
138    EXECUTE FUNCTION update_updated_at_column();
139
140DROP TRIGGER IF EXISTS update_market_instruments_updated_at ON market_instruments;
141CREATE TRIGGER update_market_instruments_updated_at
142    BEFORE UPDATE ON market_instruments
143    FOR EACH ROW
144    EXECUTE FUNCTION update_updated_at_column();
145"#;
146
147impl MarketHierarchyNode {
148    /// Creates a new MarketHierarchyNode
149    pub fn new(
150        id: String,
151        name: String,
152        parent_id: Option<String>,
153        exchange: String,
154        level: i32,
155        path: String,
156    ) -> Self {
157        let now = Utc::now();
158        Self {
159            id,
160            name,
161            parent_id,
162            exchange,
163            level,
164            path,
165            created_at: now,
166            updated_at: now,
167        }
168    }
169
170    /// Builds the full path for a node based on its parent path
171    pub fn build_path(parent_path: Option<&str>, node_name: &str) -> String {
172        match parent_path {
173            Some(parent) if !parent.is_empty() => format!("{parent}/{node_name}"),
174            _ => format!("/{node_name}"),
175        }
176    }
177}
178
179impl MarketInstrument {
180    /// Creates a new MarketInstrument
181    pub fn new(
182        epic: String,
183        instrument_name: String,
184        instrument_type: String,
185        node_id: String,
186        exchange: String,
187    ) -> Self {
188        let now = Utc::now();
189        Self {
190            epic,
191            instrument_name,
192            instrument_type,
193            node_id,
194            exchange,
195            expiry: String::new(),
196            high_limit_price: None,
197            low_limit_price: None,
198            market_status: String::new(),
199            net_change: None,
200            percentage_change: None,
201            update_time: None,
202            update_time_utc: None,
203            bid: None,
204            offer: None,
205            created_at: now,
206            updated_at: now,
207        }
208    }
209
210    /// Parses the update_time_utc from a string if available
211    pub fn parse_update_time_utc(&mut self) {
212        if let Some(ref time_str) = self.update_time
213            && let Ok(parsed_time) = DateTime::parse_from_rfc3339(time_str)
214        {
215            self.update_time_utc = Some(parsed_time.with_timezone(&Utc));
216        }
217    }
218}
219
220#[cfg(test)]
221mod tests {
222    use super::*;
223
224    #[test]
225    fn test_build_path() {
226        assert_eq!(MarketHierarchyNode::build_path(None, "Root"), "/Root");
227        assert_eq!(
228            MarketHierarchyNode::build_path(Some("/Root"), "Child"),
229            "/Root/Child"
230        );
231        assert_eq!(
232            MarketHierarchyNode::build_path(Some("/Root/Child"), "Grandchild"),
233            "/Root/Child/Grandchild"
234        );
235    }
236
237    #[test]
238    fn test_market_hierarchy_node_creation() {
239        let node = MarketHierarchyNode::new(
240            "test_id".to_string(),
241            "Test Node".to_string(),
242            Some("parent_id".to_string()),
243            "IG".to_string(),
244            1,
245            "/Test Node".to_string(),
246        );
247
248        assert_eq!(node.id, "test_id");
249        assert_eq!(node.name, "Test Node");
250        assert_eq!(node.parent_id, Some("parent_id".to_string()));
251        assert_eq!(node.exchange, "IG");
252        assert_eq!(node.level, 1);
253        assert_eq!(node.path, "/Test Node");
254    }
255
256    #[test]
257    fn test_market_instrument_creation() {
258        let mut instrument = MarketInstrument::new(
259            "IX.D.DAX.DAILY.IP".to_string(),
260            "Germany 40".to_string(),
261            "INDICES".to_string(),
262            "node_123".to_string(),
263            "IG".to_string(),
264        );
265
266        assert_eq!(instrument.epic, "IX.D.DAX.DAILY.IP");
267        assert_eq!(instrument.instrument_name, "Germany 40");
268        assert_eq!(instrument.instrument_type, "INDICES");
269        assert_eq!(instrument.node_id, "node_123");
270        assert_eq!(instrument.exchange, "IG");
271
272        // Test update_time_utc parsing
273        instrument.update_time = Some("2023-12-01T10:30:00Z".to_string());
274        instrument.parse_update_time_utc();
275        assert!(instrument.update_time_utc.is_some());
276    }
277}
ig_client/storage/market_persistence.rs

ig_client/storage/
market_persistence.rs
