Coverage for mindsdb / integrations / handlers / instatus_handler / instatus_tables.py: 0%

1from typing import List 

2import pandas as pd 

3from mindsdb.integrations.libs.api_handler import APITable 

4from mindsdb_sql_parser import ast 

5from mindsdb.integrations.utilities.sql_utils import extract_comparison_conditions 

6from mindsdb_sql_parser.ast.select.constant import Constant 

7import json 

8import re 

9 

10langCodes = ["ar", "cs", "da", "de", "en", "es", "et", "fi", "fr", "hu", "id", "it", "ja", "ko", 

11 "nl", "no", "pl", "pt", "pt-BR", "ro", "rs", "ru", "sl", "sq", "sv", "tr", "uk", 

12 "vi", "zh", "zh-TW"] 

13 

14 

15class StatusPages(APITable): 

16 

17 # table name in the database 

18 name = 'status_pages' 

19 

20 def select(self, query: ast.Select) -> pd.DataFrame: 

21 """Receive query as AST (abstract syntax tree) and act upon it. 

22 

23 Args: 

24 query (ASTNode): sql query represented as AST. Usually it should be ast.Select 

25 

26 Returns: 

27 pd.DataFrame 

28 """ 

29 conditions = extract_comparison_conditions(query.where) 

30 # Get page id from query 

31 _id = None 

32 for op, arg1, arg2 in conditions: 

33 if arg1 == 'id' and op == '=': 

34 _id = arg2 

35 else: 

36 raise NotImplementedError 

37 

38 # Get column names from query 

39 selected_columns = [] 

40 for target in query.targets: 

41 if isinstance(target, ast.Star): 

42 selected_columns = self.get_columns() 

43 break 

44 elif isinstance(target, ast.Identifier): 

45 selected_columns.append(target.parts[-1]) 

46 else: 

47 raise ValueError(f"Unknown query target {type(target)}") 

48 

49 # 'id' needs to selected when searching with 'id' 

50 temp_selected_columns = selected_columns 

51 if _id and 'id' not in selected_columns: 

52 selected_columns = ['id'] + selected_columns 

53 

54 # Get limit from query 

55 limit = query.limit.value if query.limit else 20 

56 total_results = limit 

57 

58 page_no = 1 # default page no 

59 result_df = pd.DataFrame(columns=selected_columns) 

60 

61 # call instatus api and get the response as pd.DataFrame 

62 while True: 

63 df = self.handler.call_instatus_api(endpoint='/v2/pages', params={'page': page_no, 'per_page': 100}) 

64 if len(df) == 0 or limit <= 0: 

65 break 

66 else: 

67 result_df = pd.concat([result_df, df[selected_columns]], ignore_index=True) 

68 

69 page_no += 1 

70 limit -= len(df) 

71 

72 # select columns from pandas data frame df 

73 if result_df.empty: 

74 result_df = pd.DataFrame(columns=selected_columns) 

75 elif _id: 

76 result_df = result_df[result_df['id'] == _id] 

77 

78 # delete 'id' column if 'id' not present in temp_selected_columns 

79 if 'id' not in temp_selected_columns and 'id' in selected_columns: 

80 result_df = result_df.drop('id', axis=1) 

81 

82 return result_df.head(n=total_results) 

83 

84 def insert(self, query: ast.Insert) -> None: 

85 """Receive query as AST (abstract syntax tree) and act upon it somehow. 

86 

87 Args: 

88 query (ASTNode): sql query represented as AST. Usually it should be ast.Insert 

89 

90 Returns: 

91 None 

92 """ 

93 data = {} 

94 for column, value in zip(query.columns, query.values[0]): 

95 if isinstance(value, str): 

96 try: 

97 value = json.loads(value) 

98 except json.JSONDecodeError: 

99 if value == 'True': 

100 value = True 

101 elif value == 'False': 

102 value = False 

103 data[column.name] = value 

104 self.handler.call_instatus_api(endpoint='/v1/pages', method='POST', json_data=data) 

105 

106 def update(self, query: ast.Update) -> None: 

107 """Receive query as AST (abstract syntax tree) and act upon it somehow. 

108 

109 Args: 

110 query (ASTNode): sql query represented as AST. Usually it should be ast.Update 

111 Returns: 

112 None 

113 """ 

114 conditions = extract_comparison_conditions(query.where) 

115 # Get page id from query 

116 _id = None 

117 for op, arg1, arg2 in conditions: 

118 if arg1 == 'id' and op == '=': 

119 _id = arg2 

120 else: 

121 raise NotImplementedError 

122 

123 data = {} 

124 for key, value in query.update_columns.items(): 

125 if isinstance(value, Constant): 

126 if key == 'components': 

127 data[key] = json.loads(value.value) 

128 else: 

129 data[key] = value.value 

130 

131 if 'components' in data and isinstance(data['components'], str): 

132 data['components'] = json.loads(data['components']) 

133 

134 self.handler.call_instatus_api(endpoint=f'/v2/{_id}', method='PUT', json_data=data) 

135 

136 def get_columns(self, ignore: List[str] = []) -> List[str]: 

137 """columns 

138 

139 Args: 

140 ignore (List[str], optional): exclusion items. Defaults to []. 

141 

142 Returns: 

143 List[str]: available columns with `ignore` items removed from the list. 

144 """ 

145 return [ 

146 "id", 

147 "subdomain", 

148 "name", 

149 "workspaceId", 

150 "logoUrl", 

151 "faviconUrl", 

152 "websiteUrl", 

153 "customDomain", 

154 "publicEmail", 

155 "twitter", 

156 "status", 

157 "subscribeBySms", 

158 "sendSmsNotifications", 

159 "language", 

160 "useLargeHeader", 

161 "brandColor", 

162 "okColor", 

163 "disruptedColor", 

164 "degradedColor", 

165 "downColor", 

166 "noticeColor", 

167 "unknownColor", 

168 "googleAnalytics", 

169 "smsService", 

170 "htmlInMeta", 

171 "htmlAboveHeader", 

172 "htmlBelowHeader", 

173 "htmlAboveFooter", 

174 "htmlBelowFooter", 

175 "htmlBelowSummary", 

176 "uptimeDaysDisplay", 

177 "uptimeOutageDisplay", 

178 "launchDate", 

179 "cssGlobal", 

180 "onboarded", 

181 "createdAt", 

182 "updatedAt" 

183 ] 

184 

185 

186class Components(APITable): 

187 

188 # table name in the database 

189 name = 'components' 

190 

191 def select(self, query: ast.Select) -> pd.DataFrame: 

192 """Receive query as AST (abstract syntax tree) and act upon it. 

193 

194 Args: 

195 query (ASTNode): SQL query represented as AST. Usually it should be ast.Select 

196 

197 Returns: 

198 pd.DataFrame 

199 """ 

200 conditions = extract_comparison_conditions(query.where) 

201 

202 if len(conditions) == 0: 

203 raise Exception('WHERE clause is required') 

204 

205 # Get page id and component id from query 

206 pageId = None 

207 componentId = None 

208 for condition in conditions: 

209 if condition[1] == 'page_id' and condition[0] == '=': 

210 pageId = condition[2] 

211 

212 if condition[1] == 'component_id' and condition[0] == '=': 

213 componentId = condition[2] 

214 

215 # Get column names from query 

216 selected_columns = [] 

217 for target in query.targets: 

218 if isinstance(target, ast.Star): 

219 selected_columns = self.get_columns() 

220 break 

221 elif isinstance(target, ast.Identifier): 

222 selected_columns.append(target.parts[-1]) 

223 else: 

224 raise ValueError(f"Unknown query target {type(target)}") 

225 

226 limit = query.limit.value if query.limit else None 

227 if componentId: 

228 # Call instatus API and get the response as pd.DataFrame 

229 df = self.handler.call_instatus_api(endpoint=f'/v1/{pageId}/components/{componentId}') 

230 for langCode in langCodes: 

231 try: 

232 df[f"translations_name_in_{langCode}"] = df["translations"].apply(lambda x: x.get("name", None)).apply(lambda x: x.get(langCode, None)) 

233 df[f"translations_desc_in_{langCode}"] = df["translations"].apply(lambda x: x.get("description", None)).apply(lambda x: x.get(langCode, None)) 

234 except AttributeError: 

235 df[f"translations_name_in_{langCode}"] = None 

236 df[f"translations_desc_in_{langCode}"] = None 

237 df = df.drop(columns=["translations"]) 

238 

239 result_df = df[selected_columns] 

240 else: 

241 # Call instatus API and get the response as pd.DataFrame 

242 page_size = 100 

243 # Calculate the number of pages required 

244 page_count = (limit + page_size - 1) // page_size if limit else 1 

245 result_df = pd.DataFrame(columns=selected_columns) 

246 

247 # Call instatus API and get the response as pd.DataFrame for each page 

248 for page in range(1, page_count + 1): 

249 current_page_size = min(page_size, limit) if limit else page_size 

250 

251 df = self.handler.call_instatus_api(endpoint=f'/v1/{pageId}/components', params={'page': page, 'per_page': current_page_size}) 

252 # Break if no more data is available or limit is reached 

253 if len(df) == 0 or (limit and limit <= 0) or limit == 0: 

254 break 

255 ''' Add translations_name_in_{langCode} and translations_desc_in_{langCode} columns to the dataframe''' 

256 for i in range(len(df)): 

257 for langCode in langCodes: 

258 try: 

259 df.at[i, f"translations_name_in_{langCode}"] = df.at[i, "translations"].get("name", {}).get(langCode, None) 

260 df.at[i, f"translations_desc_in_{langCode}"] = df.at[i, "translations"].get("description", {}).get(langCode, None) 

261 except AttributeError: 

262 df.at[i, f"translations_name_in_{langCode}"] = None 

263 df.at[i, f"translations_desc_in_{langCode}"] = None 

264 

265 # Drop the 'translations' column 

266 df = df.drop(columns=["translations"]) 

267 # Concatenate the dataframes 

268 result_df = pd.concat([result_df, df[selected_columns]], ignore_index=True) 

269 

270 if limit: 

271 limit -= len(df) 

272 

273 return result_df 

274 

275 def insert(self, query: ast.Insert) -> None: 

276 """Receive query as AST (abstract syntax tree) and act upon it somehow. 

277 

278 Args: 

279 query (ASTNode): sql query represented as AST. Usually it should be ast.Insert 

280 

281 Returns: 

282 None 

283 """ 

284 data = {'translations': { 

285 "name": {}, 

286 "description": {} 

287 }} 

288 

289 for column, value in zip(query.columns, query.values[0]): 

290 if isinstance(value, Constant): 

291 data[column.name] = json.loads(value.value) if column.name == 'translations' else value.value 

292 elif isinstance(value, str): 

293 try: 

294 if re.match(r'^translations_name_in_[a-zA-Z\-]+$', column.name): 

295 lang_code = column.name.split('_')[-1] 

296 if lang_code not in langCodes: 

297 raise Exception(f'Invalid language code {lang_code}') 

298 data['translations']['name'][lang_code] = value 

299 elif re.match(r'^translations_desc_in_[a-zA-Z\-]+$', column.name): 

300 lang_code = column.name.split('_')[-1] 

301 if lang_code not in langCodes: 

302 raise Exception(f'Invalid language code {lang_code}') 

303 data['translations']['description'][lang_code] = value 

304 else: 

305 data[column.name] = json.loads(value) 

306 except json.JSONDecodeError: 

307 data[column.name] = True if value == 'True' else (False if value == 'False' else value) 

308 

309 page_id = data.pop('page_id', None) 

310 

311 if page_id is not None: 

312 self.handler.call_instatus_api(endpoint=f'/v1/{page_id}/components', method='POST', json_data=data) 

313 

314 def update(self, query: ast.Update) -> None: 

315 """Receive query as AST (abstract syntax tree) and act upon it somehow. 

316 

317 Args: 

318 query (ASTNode): sql query represented as AST. Usually it should be ast.Update 

319 Returns: 

320 None 

321 """ 

322 conditions = extract_comparison_conditions(query.where) 

323 # Get page id and component id from query 

324 pageId = None 

325 componentId = None 

326 for condition in conditions: 

327 if condition[1] == 'page_id' and condition[0] == '=': 

328 pageId = condition[2] 

329 elif condition[1] == 'component_id' and condition[0] == '=': 

330 componentId = condition[2] 

331 else: 

332 raise Exception("page_id and component_id both are required") 

333 

334 data = {'translations': { 

335 "name": {}, 

336 "description": {} 

337 }} 

338 for key, value in query.update_columns.items(): 

339 if isinstance(value, Constant): 

340 if re.match(r'^translations_name_in_[a-zA-Z\-]+$', key): 

341 lang_code = key.split('_')[-1] 

342 if lang_code not in langCodes: 

343 raise Exception(f'Invalid language code {lang_code}') 

344 data['translations']['name'][lang_code] = value.value 

345 elif re.match(r'^translations_desc_in_[a-zA-Z\-]+$', key): 

346 lang_code = key.split('_')[-1] 

347 if lang_code not in langCodes: 

348 raise Exception(f'Invalid language code {lang_code}') 

349 data['translations']['description'][lang_code] = value.value 

350 else: 

351 data[key] = value.value 

352 self.handler.call_instatus_api(endpoint=f'/v1/{pageId}/components/{componentId}', method='PUT', json_data=data) 

353 

354 def get_columns(self, ignore: List[str] = []) -> List[str]: 

355 """columns 

356 

357 Args: 

358 ignore (List[str], optional): exclusion items. Defaults to []. 

359 

360 Returns: 

361 List[str]: available columns with `ignore` items removed from the list. 

362 """ 

363 return [ 

364 "id", 

365 "name", 

366 "nameTranslationId", 

367 "description", 

368 "descriptionTranslationId", 

369 "status", 

370 "order", 

371 "showUptime", 

372 "createdAt", 

373 "updatedAt", 

374 "archivedAt", 

375 "siteId", 

376 "uniqueEmail", 

377 "oldGroup", 

378 "groupId", 

379 "isParent", 

380 "isCollapsed", 

381 "monitorId", 

382 "nameHtml", 

383 "nameHtmlTranslationId", 

384 "descriptionHtml", 

385 "descriptionHtmlTranslationId", 

386 "isThirdParty", 

387 "thirdPartyStatus", 

388 "thirdPartyComponentId", 

389 "thirdPartyComponentServiceId", 

390 "importedFromStatuspage", 

391 "startDate", 

392 "group", 

393 ] + [f'translations_name_in_{langCode}' for langCode in langCodes] + [f'translations_desc_in_{langCode}' for langCode in langCodes]