Coverage for mindsdb / integrations / handlers / sharepoint_handler / sharepoint_tables.py: 0%

1import pandas as pd 

2from typing import Text, List, Dict, Any 

3 

4from mindsdb_sql_parser import ast 

5from mindsdb.integrations.libs.api_handler import APITable 

6 

7from mindsdb.integrations.utilities.handlers.query_utilities.select_query_utilities import ( 

8 SELECTQueryParser, 

9 SELECTQueryExecutor, 

10) 

11from mindsdb.integrations.utilities.handlers.query_utilities.delete_query_utilities import ( 

12 DELETEQueryParser, 

13 DELETEQueryExecutor, 

14) 

15 

16from mindsdb.integrations.utilities.handlers.query_utilities.update_query_utilities import ( 

17 UPDATEQueryParser, 

18 UPDATEQueryExecutor, 

19) 

20 

21from mindsdb.integrations.utilities.handlers.query_utilities import INSERTQueryParser 

22 

23 

24class SitesTable(APITable): 

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

26 """ 

27 Pulls Sharepoint Sites data. 

28 

29 Parameters 

30 ---------- 

31 query : ast.Select 

32 Given SQL SELECT query 

33 

34 Returns 

35 ------- 

36 pd.DataFrame 

37 Sharepoint Sites matching the query 

38 

39 Raises 

40 ------ 

41 ValueError 

42 If the query contains an unsupported condition 

43 """ 

44 select_statement_parser = SELECTQueryParser(query, "sites", self.get_columns()) 

45 ( 

46 selected_columns, 

47 where_conditions, 

48 order_by_conditions, 

49 result_limit, 

50 ) = select_statement_parser.parse_query() 

51 

52 sites_df = pd.json_normalize(self.get_sites(limit=result_limit)) 

53 select_statement_executor = SELECTQueryExecutor( 

54 sites_df, selected_columns, where_conditions, order_by_conditions 

55 ) 

56 sites_df = select_statement_executor.execute_query() 

57 

58 return sites_df 

59 

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

61 """Updates data in the Sharepoint "PUT /lists" API endpoint. 

62 

63 Parameters 

64 ---------- 

65 query : ast.Update 

66 Given SQL UPDATE query 

67 

68 Returns 

69 ------- 

70 None 

71 

72 Raises 

73 ------ 

74 ValueError 

75 If the query contains an unsupported condition 

76 """ 

77 update_statement_parser = UPDATEQueryParser(query) 

78 values_to_update, where_conditions = update_statement_parser.parse_query() 

79 

80 sites_df = pd.json_normalize(self.get_sites()) 

81 

82 update_query_executor = UPDATEQueryExecutor(sites_df, where_conditions) 

83 

84 sites_df = update_query_executor.execute_query() 

85 

86 sites_ids = sites_df[["siteId"]].to_dict(orient="records") 

87 

88 self.update_sites(sites_ids, values_to_update) 

89 

90 def get_columns(self) -> List[Text]: 

91 return pd.json_normalize(self.get_sites(limit=1)).columns.tolist() 

92 

93 def get_sites(self, **kwargs) -> List[Dict]: 

94 if not self.handler.connection.check_bearer_token_validity(): 

95 self.handler.connect() 

96 client = self.handler.connection 

97 site_data = client.get_all_sites(**kwargs) 

98 return site_data 

99 

100 def update_sites(self, site_ids: List[dict], values_to_update: dict) -> None: 

101 if not self.handler.connection.check_bearer_token_validity(): 

102 self.handler.connect() 

103 client = self.handler.connection 

104 client.update_sites(site_ids, values_to_update) 

105 

106 

107class ListsTable(APITable): 

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

109 """Inserts data into the Sharepoint "POST /lists" API endpoint. 

110 

111 Parameters 

112 ---------- 

113 query : ast.Insert 

114 Given SQL INSERT query 

115 

116 Returns 

117 ------- 

118 None 

119 

120 Raises 

121 ------ 

122 ValueError 

123 If the query contains an unsupported condition 

124 """ 

125 insert_statement_parser = INSERTQueryParser( 

126 query, 

127 supported_columns=["displayName", "columns", "list", "siteId"], 

128 mandatory_columns=["displayName", "list", "siteId"], 

129 all_mandatory=False, 

130 ) 

131 lists_data = insert_statement_parser.parse_query() 

132 self.create_lists(lists_data) 

133 

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

135 """ 

136 Pulls Sharepoint lists data. 

137 

138 Parameters 

139 ---------- 

140 query : ast.Select 

141 Given SQL SELECT query 

142 

143 Returns 

144 ------- 

145 pd.DataFrame 

146 Sharepoint lists matching the query 

147 

148 Raises 

149 ------ 

150 ValueError 

151 If the query contains an unsupported condition 

152 """ 

153 select_statement_parser = SELECTQueryParser(query, "lists", self.get_columns()) 

154 ( 

155 selected_columns, 

156 where_conditions, 

157 order_by_conditions, 

158 result_limit, 

159 ) = select_statement_parser.parse_query() 

160 

161 lists_df = pd.json_normalize(self.get_lists(limit=result_limit)) 

162 select_statement_executor = SELECTQueryExecutor( 

163 lists_df, selected_columns, where_conditions, order_by_conditions 

164 ) 

165 lists_df = select_statement_executor.execute_query() 

166 

167 return lists_df 

168 

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

170 """Updates data in the Sharepoint "PUT /lists" API endpoint. 

171 

172 Parameters 

173 ---------- 

174 query : ast.Update 

175 Given SQL UPDATE query 

176 

177 Returns 

178 ------- 

179 None 

180 

181 Raises 

182 ------ 

183 ValueError 

184 If the query contains an unsupported condition 

185 """ 

186 update_statement_parser = UPDATEQueryParser(query) 

187 values_to_update, where_conditions = update_statement_parser.parse_query() 

188 

189 lists_df = pd.json_normalize(self.get_lists()) 

190 

191 update_query_executor = UPDATEQueryExecutor(lists_df, where_conditions) 

192 

193 lists_df = update_query_executor.execute_query() 

194 

195 list_ids = lists_df[["id", "siteId"]].to_dict(orient="records") 

196 

197 self.update_lists(list_ids, values_to_update) 

198 

199 def delete(self, query: ast.Delete) -> None: 

200 """ 

201 Deletes data from the Sharepoint "DELETE /lists" API endpoint. 

202 

203 Parameters 

204 ---------- 

205 query : ast.Delete 

206 Given SQL DELETE query 

207 

208 Returns 

209 ------- 

210 None 

211 

212 Raises 

213 ------ 

214 ValueError 

215 If the query contains an unsupported condition 

216 """ 

217 delete_statement_parser = DELETEQueryParser(query) 

218 where_conditions = delete_statement_parser.parse_query() 

219 

220 lists_df = pd.json_normalize(self.get_lists()) 

221 

222 delete_query_executor = DELETEQueryExecutor(lists_df, where_conditions) 

223 

224 lists_df = delete_query_executor.execute_query() 

225 

226 list_ids = lists_df[["id", "siteId"]].to_dict(orient="records") 

227 self.delete_lists(list_ids) 

228 

229 def get_lists(self, **kwargs) -> List[Dict]: 

230 if not self.handler.connection.check_bearer_token_validity(): 

231 self.handler.connect() 

232 client = self.handler.connection 

233 lists_data = client.get_all_lists(**kwargs) 

234 return lists_data 

235 

236 def delete_lists(self, list_ids: List[dict]) -> None: 

237 if not self.handler.connection.check_bearer_token_validity(): 

238 self.handler.connect() 

239 client = self.handler.connection 

240 client.delete_lists(list_ids) 

241 

242 def update_lists(self, list_ids: List[dict], values_to_update: dict) -> None: 

243 if not self.handler.connection.check_bearer_token_validity(): 

244 self.handler.connect() 

245 client = self.handler.connection 

246 client.update_lists(list_ids, values_to_update) 

247 

248 def create_lists(self, lists_data: List[Dict[Text, Any]]) -> None: 

249 if not self.handler.connection.check_bearer_token_validity(): 

250 self.handler.connect() 

251 client = self.handler.connection 

252 client.create_lists(data=lists_data) 

253 

254 def get_columns(self) -> List[Text]: 

255 return pd.json_normalize(self.get_lists(limit=1)).columns.tolist() 

256 

257 

258class SiteColumnsTable(APITable): 

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

260 """Inserts data into the Sharepoint "POST /columns" API endpoint. 

261 

262 Parameters 

263 ---------- 

264 query : ast.Insert 

265 Given SQL INSERT query 

266 

267 Returns 

268 ------- 

269 None 

270 

271 Raises 

272 ------ 

273 ValueError 

274 If the query contains an unsupported condition 

275 """ 

276 insert_statement_parser = INSERTQueryParser( 

277 query, 

278 supported_columns=[ 

279 "text", 

280 "name", 

281 "indexed", 

282 "enforceUniqueValues", 

283 "hidden", 

284 "siteId", 

285 ], 

286 mandatory_columns=["name", "siteId"], 

287 all_mandatory=False, 

288 ) 

289 site_columns_data = insert_statement_parser.parse_query() 

290 self.create_site_columns(site_columns_data) 

291 

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

293 """ 

294 Pulls Sharepoint columns data. 

295 

296 Parameters 

297 ---------- 

298 query : ast.Select 

299 Given SQL SELECT query 

300 

301 Returns 

302 ------- 

303 pd.DataFrame 

304 Sharepoint Columns matching the query 

305 

306 Raises 

307 ------ 

308 ValueError 

309 If the query contains an unsupported condition 

310 """ 

311 select_statement_parser = SELECTQueryParser( 

312 query, "siteColumns", self.get_columns() 

313 ) 

314 ( 

315 selected_columns, 

316 where_conditions, 

317 order_by_conditions, 

318 result_limit, 

319 ) = select_statement_parser.parse_query() 

320 

321 site_columns_df = pd.json_normalize(self.get_site_columns(limit=result_limit)) 

322 select_statement_executor = SELECTQueryExecutor( 

323 site_columns_df, 

324 selected_columns, 

325 where_conditions, 

326 order_by_conditions, 

327 ) 

328 site_columns_df = select_statement_executor.execute_query() 

329 

330 return site_columns_df 

331 

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

333 """Updates data in the Sharepoint "PUT /columns" API endpoint. 

334 

335 Parameters 

336 ---------- 

337 query : ast.Update 

338 Given SQL UPDATE query 

339 

340 Returns 

341 ------- 

342 None 

343 

344 Raises 

345 ------ 

346 ValueError 

347 If the query contains an unsupported condition 

348 """ 

349 update_statement_parser = UPDATEQueryParser(query) 

350 values_to_update, where_conditions = update_statement_parser.parse_query() 

351 

352 site_columns_df = pd.json_normalize(self.get_site_columns()) 

353 

354 update_query_executor = UPDATEQueryExecutor(site_columns_df, where_conditions) 

355 

356 site_columns_df = update_query_executor.execute_query() 

357 

358 site_columns_ids = site_columns_df[["id", "siteId"]].to_dict(orient="records") 

359 

360 self.update_site_columns(site_columns_ids, values_to_update) 

361 

362 def delete(self, query: ast.Delete) -> None: 

363 """ 

364 Deletes data from the Sharepoint "DELETE /columns" API endpoint. 

365 

366 Parameters 

367 ---------- 

368 query : ast.Delete 

369 Given SQL DELETE query 

370 

371 Returns 

372 ------- 

373 None 

374 

375 Raises 

376 ------ 

377 ValueError 

378 If the query contains an unsupported condition 

379 """ 

380 delete_statement_parser = DELETEQueryParser(query) 

381 where_conditions = delete_statement_parser.parse_query() 

382 

383 site_columns_df = pd.json_normalize(self.get_site_columns()) 

384 

385 delete_query_executor = DELETEQueryExecutor(site_columns_df, where_conditions) 

386 

387 site_columns_df = delete_query_executor.execute_query() 

388 

389 site_columns_ids = site_columns_df[["id", "siteId"]].to_dict(orient="records") 

390 self.delete_site_columns(site_columns_ids) 

391 

392 def get_site_columns(self, **kwargs) -> List[Dict]: 

393 if not self.handler.connection.check_bearer_token_validity(): 

394 self.handler.connect() 

395 client = self.handler.connection 

396 site_columns_data = client.get_all_site_columns(**kwargs) 

397 return site_columns_data 

398 

399 def delete_site_columns(self, sharepoint_column_ids: List[dict]) -> None: 

400 if not self.handler.connection.check_bearer_token_validity(): 

401 self.handler.connect() 

402 client = self.handler.connection 

403 client.delete_site_columns(sharepoint_column_ids) 

404 

405 def update_site_columns( 

406 self, sharepoint_column_ids: List[dict], values_to_update: dict 

407 ) -> None: 

408 if not self.handler.connection.check_bearer_token_validity(): 

409 self.handler.connect() 

410 client = self.handler.connection 

411 client.update_site_columns(sharepoint_column_ids, values_to_update) 

412 

413 def create_site_columns( 

414 self, sharepoint_column_data: List[Dict[Text, Any]] 

415 ) -> None: 

416 if not self.handler.connection.check_bearer_token_validity(): 

417 self.handler.connect() 

418 client = self.handler.connection 

419 client.create_site_columns(data=sharepoint_column_data) 

420 

421 def get_columns(self) -> List[Text]: 

422 return pd.json_normalize(self.get_site_columns(limit=1)).columns.tolist() 

423 

424 

425class ListItemsTable(APITable): 

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

427 """Inserts data into the Sharepoint "POST /items" API endpoint. 

428 

429 Parameters 

430 ---------- 

431 query : ast.Insert 

432 Given SQL INSERT query 

433 

434 Returns 

435 ------- 

436 None 

437 

438 Raises 

439 ------ 

440 ValueError 

441 If the query contains an unsupported condition 

442 """ 

443 insert_statement_parser = INSERTQueryParser( 

444 query, 

445 supported_columns=["fields", "listId", "siteId"], 

446 mandatory_columns=["listId", "siteId"], 

447 all_mandatory=False, 

448 ) 

449 list_items_data = insert_statement_parser.parse_query() 

450 self.create_list_items(list_items_data) 

451 

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

453 """ 

454 Pulls Sharepoint items data. 

455 

456 Parameters 

457 ---------- 

458 query : ast.Select 

459 Given SQL SELECT query 

460 

461 Returns 

462 ------- 

463 pd.DataFrame 

464 Sharepoint items matching the query 

465 

466 Raises 

467 ------ 

468 ValueError 

469 If the query contains an unsupported condition 

470 """ 

471 select_statement_parser = SELECTQueryParser( 

472 query, "listItems", self.get_columns() 

473 ) 

474 ( 

475 selected_columns, 

476 where_conditions, 

477 order_by_conditions, 

478 result_limit, 

479 ) = select_statement_parser.parse_query() 

480 

481 list_items_df = pd.json_normalize(self.get_list_items(limit=result_limit)) 

482 select_statement_executor = SELECTQueryExecutor( 

483 list_items_df, selected_columns, where_conditions, order_by_conditions 

484 ) 

485 list_items_df = select_statement_executor.execute_query() 

486 

487 return list_items_df 

488 

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

490 """Updates data in the Sharepoint "PUT /items" API endpoint. 

491 

492 Parameters 

493 ---------- 

494 query : ast.Update 

495 Given SQL UPDATE query 

496 

497 Returns 

498 ------- 

499 None 

500 

501 Raises 

502 ------ 

503 ValueError 

504 If the query contains an unsupported condition 

505 """ 

506 update_statement_parser = UPDATEQueryParser(query) 

507 values_to_update, where_conditions = update_statement_parser.parse_query() 

508 

509 list_items_df = pd.json_normalize(self.get_list_items()) 

510 

511 update_query_executor = UPDATEQueryExecutor(list_items_df, where_conditions) 

512 

513 list_items_df = update_query_executor.execute_query() 

514 

515 list_items_ids = list_items_df[["id", "siteId", "listId"]].to_dict( 

516 orient="records" 

517 ) 

518 

519 self.update_list_items(list_items_ids, values_to_update) 

520 

521 def delete(self, query: ast.Delete) -> None: 

522 """ 

523 Deletes data from the Sharepoint "DELETE /items" API endpoint. 

524 

525 Parameters 

526 ---------- 

527 query : ast.Delete 

528 Given SQL DELETE query 

529 

530 Returns 

531 ------- 

532 None 

533 

534 Raises 

535 ------ 

536 ValueError 

537 If the query contains an unsupported condition 

538 """ 

539 delete_statement_parser = DELETEQueryParser(query) 

540 where_conditions = delete_statement_parser.parse_query() 

541 

542 list_items_df = pd.json_normalize(self.get_list_items()) 

543 

544 delete_query_executor = DELETEQueryExecutor(list_items_df, where_conditions) 

545 

546 list_items_df = delete_query_executor.execute_query() 

547 

548 list_items_ids = list_items_df[["id", "siteId", "listId"]].to_dict( 

549 orient="records" 

550 ) 

551 self.delete_list_items(list_items_ids) 

552 

553 def get_list_items(self, **kwargs) -> List[Dict]: 

554 if not self.handler.connection.check_bearer_token_validity(): 

555 self.handler.connect() 

556 client = self.handler.connection 

557 list_items_data = client.get_all_items(**kwargs) 

558 return list_items_data 

559 

560 def delete_list_items(self, list_item_ids: List[dict]) -> None: 

561 if not self.handler.connnection.check_connection(): 

562 self.handler.connect() 

563 client = self.handler.connection 

564 client.delete_items(list_item_ids) 

565 

566 def update_list_items( 

567 self, list_items_ids: List[dict], values_to_update: dict 

568 ) -> None: 

569 if not self.handler.connection.check_bearer_token_validity(): 

570 self.handler.connect() 

571 client = self.handler.connection 

572 client.update_items(item_dict=list_items_ids, values_to_update=values_to_update) 

573 

574 def create_list_items(self, list_items_data: List[Dict[Text, Any]]) -> None: 

575 if not self.handler.connection.check_bearer_token_validity(): 

576 self.handler.connect() 

577 client = self.handler.connection 

578 client.create_items(data=list_items_data) 

579 

580 def get_columns(self) -> List[Text]: 

581 return pd.json_normalize(self.get_list_items(limit=1)).columns.tolist()