Coverage for mindsdb / integrations / handlers / firebird_handler / firebird_handler.py: 0%
85 statements
« prev ^ index » next coverage.py v7.13.1, created at 2026-01-21 00:36 +0000
1from typing import Optional
3import pandas as pd
4import fdb
6from mindsdb_sql_parser import parse_sql
7from sqlalchemy_firebird.base import FBDialect
8from mindsdb.utilities.render.sqlalchemy_render import SqlalchemyRender
9from mindsdb.integrations.libs.base import DatabaseHandler
11from mindsdb_sql_parser.ast.base import ASTNode
13from mindsdb.utilities import log
14from mindsdb.integrations.libs.response import (
15 HandlerStatusResponse as StatusResponse,
16 HandlerResponse as Response,
17 RESPONSE_TYPE
18)
20logger = log.getLogger(__name__)
23class FirebirdHandler(DatabaseHandler):
24 """
25 This handler handles connection and execution of the Firebird statements.
26 """
28 name = 'firebird'
30 def __init__(self, name: str, connection_data: Optional[dict], **kwargs):
31 """
32 Initialize the handler.
33 Args:
34 name (str): name of particular handler instance
35 connection_data (dict): parameters for connecting to the database
36 **kwargs: arbitrary keyword arguments.
37 """
38 super().__init__(name)
39 self.parser = parse_sql
40 self.dialect = 'firebird'
41 self.connection_data = connection_data
42 self.kwargs = kwargs
44 self.connection = None
45 self.is_connected = False
47 def __del__(self):
48 if self.is_connected is True:
49 self.disconnect()
51 def connect(self) -> StatusResponse:
52 """
53 Set up the connection required by the handler.
54 Returns:
55 HandlerStatusResponse
56 """
58 if self.is_connected is True:
59 return self.connection
61 self.connection = fdb.connect(
62 host=self.connection_data['host'],
63 database=self.connection_data['database'],
64 user=self.connection_data['user'],
65 password=self.connection_data['password'],
66 )
67 self.is_connected = True
69 return self.connection
71 def disconnect(self):
72 """
73 Close any existing connections.
74 """
76 if self.is_connected is False:
77 return
79 self.connection.close()
80 self.is_connected = False
81 return self.is_connected
83 def check_connection(self) -> StatusResponse:
84 """
85 Check connection to the handler.
86 Returns:
87 HandlerStatusResponse
88 """
90 response = StatusResponse(False)
91 need_to_close = self.is_connected is False
93 try:
94 self.connect()
95 response.success = True
96 except Exception as e:
97 logger.error(f'Error connecting to Firebird {self.connection_data["database"]}, {e}!')
98 response.error_message = str(e)
99 finally:
100 if response.success is True and need_to_close:
101 self.disconnect()
102 if response.success is False and self.is_connected is True:
103 self.is_connected = False
105 return response
107 def native_query(self, query: str) -> StatusResponse:
108 """
109 Receive raw query and act upon it somehow.
110 Args:
111 query (str): query in native format
112 Returns:
113 HandlerResponse
114 """
116 need_to_close = self.is_connected is False
118 connection = self.connect()
119 cursor = connection.cursor()
121 try:
122 cursor.execute(query)
123 result = cursor.fetchall()
124 if result:
125 response = Response(
126 RESPONSE_TYPE.TABLE,
127 data_frame=pd.DataFrame(
128 result,
129 columns=[x[0] for x in cursor.description]
130 )
131 )
132 else:
133 connection.commit()
134 response = Response(RESPONSE_TYPE.OK)
135 except Exception as e:
136 logger.error(f'Error running query: {query} on {self.connection_data["database"]}!')
137 response = Response(
138 RESPONSE_TYPE.ERROR,
139 error_message=str(e)
140 )
142 cursor.close()
143 if need_to_close is True:
144 self.disconnect()
146 return response
148 def query(self, query: ASTNode) -> StatusResponse:
149 """
150 Receive query as AST (abstract syntax tree) and act upon it somehow.
151 Args:
152 query (ASTNode): sql query represented as AST. May be any kind
153 of query: SELECT, INTSERT, DELETE, etc
154 Returns:
155 HandlerResponse
156 """
157 renderer = SqlalchemyRender(FBDialect)
158 query_str = renderer.get_string(query, with_failback=True)
159 return self.native_query(query_str)
161 def get_tables(self) -> StatusResponse:
162 """
163 Return list of entities that will be accessible as tables.
164 Returns:
165 HandlerResponse
166 """
168 query = """
169 SELECT RDB$RELATION_NAME
170 FROM RDB$RELATIONS
171 WHERE (RDB$SYSTEM_FLAG <> 1 OR RDB$SYSTEM_FLAG IS NULL) AND RDB$VIEW_BLR IS NULL
172 ORDER BY RDB$RELATION_NAME;
173 """
174 result = self.native_query(query)
175 df = result.data_frame
176 df[df.columns[0]] = df[df.columns[0]].apply(lambda row: row.strip())
177 result.data_frame = df.rename(columns={df.columns[0]: 'table_name'})
178 return result
180 def get_columns(self, table_name: str) -> StatusResponse:
181 """
182 Returns a list of entity columns.
183 Args:
184 table_name (str): name of one of tables returned by self.get_tables()
185 Returns:
186 HandlerResponse
187 """
189 query = f"""
190 SELECT
191 RF.RDB$FIELD_NAME FIELD_NAME,
192 CASE F.RDB$FIELD_TYPE
193 WHEN 7 THEN
194 CASE F.RDB$FIELD_SUB_TYPE
195 WHEN 0 THEN 'SMALLINT'
196 WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
197 WHEN 2 THEN 'DECIMAL'
198 END
199 WHEN 8 THEN
200 CASE F.RDB$FIELD_SUB_TYPE
201 WHEN 0 THEN 'INTEGER'
202 WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
203 WHEN 2 THEN 'DECIMAL'
204 END
205 WHEN 9 THEN 'QUAD'
206 WHEN 10 THEN 'FLOAT'
207 WHEN 12 THEN 'DATE'
208 WHEN 13 THEN 'TIME'
209 WHEN 14 THEN 'CHAR(' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ') '
210 WHEN 16 THEN
211 CASE F.RDB$FIELD_SUB_TYPE
212 WHEN 0 THEN 'BIGINT'
213 WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
214 WHEN 2 THEN 'DECIMAL'
215 END
216 WHEN 27 THEN 'DOUBLE'
217 WHEN 35 THEN 'TIMESTAMP'
218 WHEN 37 THEN 'VARCHAR(' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')'
219 WHEN 40 THEN 'CSTRING' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')'
220 WHEN 45 THEN 'BLOB_ID'
221 WHEN 261 THEN 'BLOB SUB_TYPE ' || F.RDB$FIELD_SUB_TYPE
222 ELSE 'RDB$FIELD_TYPE: ' || F.RDB$FIELD_TYPE || '?'
223 END FIELD_TYPE,
224 IIF(COALESCE(RF.RDB$NULL_FLAG, 0) = 0, NULL, 'NOT NULL') FIELD_NULL,
225 CH.RDB$CHARACTER_SET_NAME FIELD_CHARSET,
226 DCO.RDB$COLLATION_NAME FIELD_COLLATION,
227 COALESCE(RF.RDB$DEFAULT_SOURCE, F.RDB$DEFAULT_SOURCE) FIELD_DEFAULT,
228 F.RDB$VALIDATION_SOURCE FIELD_CHECK,
229 RF.RDB$DESCRIPTION FIELD_DESCRIPTION
230 FROM RDB$RELATION_FIELDS RF
231 JOIN RDB$FIELDS F ON (F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE)
232 LEFT OUTER JOIN RDB$CHARACTER_SETS CH ON (CH.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID)
233 LEFT OUTER JOIN RDB$COLLATIONS DCO ON ((DCO.RDB$COLLATION_ID = F.RDB$COLLATION_ID) AND (DCO.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID))
234 WHERE (RF.RDB$RELATION_NAME = '{table_name.upper()}') AND (COALESCE(RF.RDB$SYSTEM_FLAG, 0) = 0)
235 ORDER BY RF.RDB$FIELD_POSITION;
236 """
237 result = self.native_query(query)
238 df = result.data_frame
239 result.data_frame = df.rename(columns={'FIELD_NAME': 'column_name', 'FIELD_TYPE': 'data_type'})
240 return result