Coverage for mindsdb / integrations / handlers / confluence_handler / confluence_tables.py: 66%

1from typing import List 

2 

3import pandas as pd 

4 

5from mindsdb.integrations.handlers.confluence_handler.confluence_api_client import ConfluenceAPIClient 

6from mindsdb.integrations.libs.api_handler import APIResource 

7from mindsdb.integrations.utilities.sql_utils import FilterCondition, FilterOperator, SortColumn 

8from mindsdb.utilities import log 

9 

10 

11logger = log.getLogger(__name__) 

12 

13 

14class ConfluenceSpacesTable(APIResource): 

15 """ 

16 The table abstraction for the 'spaces' resource of the Confluence API. 

17 """ 

18 

19 def list( 

20 self, 

21 conditions: List[FilterCondition] = None, 

22 limit: int = None, 

23 sort: List[SortColumn] = None, 

24 targets: List[str] = None, 

25 **kwargs, 

26 ): 

27 """ 

28 Executes a parsed SELECT SQL query on the 'spaces' resource of the Confluence API. 

29 

30 Args: 

31 conditions (List[FilterCondition]): The list of parsed filter conditions. 

32 limit (int): The maximum number of records to return. 

33 sort (List[SortColumn]): The list of parsed sort columns. 

34 targets (List[str]): The list of target columns to return. 

35 """ 

36 spaces = [] 

37 client: ConfluenceAPIClient = self.handler.connect() 

38 

39 ids, keys, space_type, status = None, None, None, None 

40 for condition in conditions: 

41 if condition.column == "id": 

42 if condition.op == FilterOperator.EQUAL: 

43 ids = [condition.value] 

44 

45 elif condition.op == FilterOperator.IN: 45 ↛ 46line 45 didn't jump to line 46 because the condition on line 45 was never true

46 ids = condition.value 

47 

48 else: 

49 raise ValueError(f"Unsupported operator '{condition.op}' for column 'id'.") 

50 

51 condition.applied = True 

52 

53 if condition.column == "key": 

54 if condition.op == FilterOperator.EQUAL: 54 ↛ 57line 54 didn't jump to line 57 because the condition on line 54 was always true

55 keys = [condition.value] 

56 

57 elif condition.op == FilterOperator.IN: 

58 keys = condition.value 

59 

60 else: 

61 raise ValueError(f"Unsupported operator '{condition.op}' for column 'key'.") 

62 

63 condition.applied = True 

64 

65 if condition.column == "type": 

66 if condition.op == FilterOperator.EQUAL: 66 ↛ 70line 66 didn't jump to line 70 because the condition on line 66 was always true

67 space_type = condition.value 

68 

69 else: 

70 raise ValueError(f"Unsupported operator '{condition.op}' for column 'type'.") 

71 

72 condition.applied = True 

73 

74 if condition.column == "status": 

75 if condition.op == FilterOperator.EQUAL: 75 ↛ 79line 75 didn't jump to line 79 because the condition on line 75 was always true

76 status = condition.value 

77 

78 else: 

79 raise ValueError(f"Unsupported operator '{condition.op}' for column 'status'.") 

80 

81 condition.applied = True 

82 

83 sort_condition = None 

84 if sort: 84 ↛ 85line 84 didn't jump to line 85 because the condition on line 84 was never true

85 for sort_column in sort: 

86 if sort_column.column in ["id", "key", "name"]: 

87 if sort_column.ascending: 

88 sort_condition = sort_column.column 

89 

90 else: 

91 sort_condition = f"-{sort_column.column}" 

92 

93 sort_column.applied = True 

94 break 

95 

96 spaces = client.get_spaces( 

97 ids=ids, keys=keys, space_type=space_type, status=status, sort_condition=sort_condition, limit=limit 

98 ) 

99 

100 spaces_df = pd.json_normalize(spaces, sep="_") 

101 spaces_df = spaces_df[self.get_columns()] 

102 

103 return spaces_df 

104 

105 def get_columns(self) -> List[str]: 

106 """ 

107 Retrieves the attributes (columns) of the 'spaces' resource. 

108 

109 Returns: 

110 List[Text]: A list of attributes (columns) of the 'spaces' resource. 

111 """ 

112 return [ 

113 "id", 

114 "key", 

115 "name", 

116 "type", 

117 "description_view_representation", 

118 "description_view_value", 

119 "status", 

120 "authorId", 

121 "createdAt", 

122 "homepageId", 

123 "_links_webui", 

124 "currentActiveAlias", 

125 ] 

126 

127 

128class ConfluencePagesTable(APIResource): 

129 """ 

130 The table abstraction for the 'pages' resource of the Confluence API. 

131 """ 

132 

133 def list( 

134 self, 

135 conditions: List[FilterCondition] = None, 

136 limit: int = None, 

137 sort: List[SortColumn] = None, 

138 targets: List[str] = None, 

139 **kwargs, 

140 ): 

141 """ 

142 Executes a parsed SELECT SQL query on the 'pages' resource of the Confluence API. 

143 

144 Args: 

145 conditions (List[FilterCondition]): The list of parsed filter conditions. 

146 limit (int): The maximum number of records to return. 

147 sort (List[SortColumn]): The list of parsed sort columns. 

148 targets (List[str]): The list of target columns to return. 

149 """ 

150 pages = [] 

151 client: ConfluenceAPIClient = self.handler.connect() 

152 

153 page_ids, space_ids, statuses, title = None, None, None, None 

154 for condition in conditions: 

155 if condition.column == "id": 

156 if condition.op == FilterOperator.EQUAL: 156 ↛ 159line 156 didn't jump to line 159 because the condition on line 156 was always true

157 page_ids = [condition.value] 

158 

159 elif condition.op == FilterOperator.IN: 

160 page_ids = condition.value 

161 

162 else: 

163 raise ValueError(f"Unsupported operator '{condition.op}' for column 'page_id'.") 

164 

165 condition.applied = True 

166 

167 if condition.column == "spaceId": 

168 if condition.op == FilterOperator.EQUAL: 168 ↛ 171line 168 didn't jump to line 171 because the condition on line 168 was always true

169 space_ids = [condition.value] 

170 

171 elif condition.op == FilterOperator.IN: 

172 space_ids = condition.value 

173 

174 else: 

175 raise ValueError(f"Unsupported operator '{condition.op}' for column 'spaceId'.") 

176 

177 condition.applied = True 

178 

179 if condition.column == "status": 

180 if condition.op == FilterOperator.EQUAL: 180 ↛ 183line 180 didn't jump to line 183 because the condition on line 180 was always true

181 statuses = [condition.value] 

182 

183 elif condition.op == FilterOperator.IN: 

184 statuses = condition.value 

185 

186 else: 

187 raise ValueError(f"Unsupported operator '{condition.op}' for column 'status'.") 

188 

189 condition.applied = True 

190 

191 if condition.column == "title": 

192 if condition.op == FilterOperator.EQUAL: 192 ↛ 196line 192 didn't jump to line 196 because the condition on line 192 was always true

193 title = condition.value 

194 

195 else: 

196 raise ValueError(f"Unsupported operator '{condition.op}' for column 'title'.") 

197 

198 condition.applied = True 

199 

200 sort_condition = None 

201 if sort: 

202 for sort_column in sort: 202 ↛ 211line 202 didn't jump to line 211 because the loop on line 202 didn't complete

203 if sort_column.column in ["id", "title", "createdAt"]: 203 ↛ 202line 203 didn't jump to line 202 because the condition on line 203 was always true

204 sort_condition = sort_column.column if sort_column.column != "createdAt" else "created-date" 

205 if not sort_column.ascending: 205 ↛ 208line 205 didn't jump to line 208 because the condition on line 205 was always true

206 sort_condition = f"-{sort_condition}" 

207 

208 sort_column.applied = True 

209 break 

210 

211 pages = client.get_pages( 

212 page_ids=page_ids, 

213 space_ids=space_ids, 

214 statuses=statuses, 

215 title=title, 

216 sort_condition=sort_condition, 

217 limit=limit, 

218 ) 

219 

220 pages_df = pd.json_normalize(pages, sep="_") 

221 pages_df = pages_df[self.get_columns()] 

222 

223 return pages_df 

224 

225 def get_columns(self) -> List[str]: 

226 """ 

227 Retrieves the attributes (columns) of the 'pages' resource. 

228 

229 Returns: 

230 List[Text]: A list of attributes (columns) of the 'pages' resource. 

231 """ 

232 return [ 

233 "id", 

234 "status", 

235 "title", 

236 "spaceId", 

237 "parentId", 

238 "parentType", 

239 "position", 

240 "authorId", 

241 "ownerId", 

242 "lastOwnerId", 

243 "createdAt", 

244 "version_createdAt", 

245 "version_message", 

246 "version_number", 

247 "version_minorEdit", 

248 "version_authorId", 

249 "body_storage_representation", 

250 "body_storage_value", 

251 "_links_webui", 

252 "_links_editui", 

253 "_links_tinyui", 

254 ] 

255 

256 

257class ConfluenceBlogPostsTable(APIResource): 

258 """ 

259 The table abstraction for the 'blogposts' resource of the Confluence API. 

260 """ 

261 

262 def list( 

263 self, 

264 conditions: List[FilterCondition] = None, 

265 limit: int = None, 

266 sort: List[SortColumn] = None, 

267 targets: List[str] = None, 

268 **kwargs, 

269 ): 

270 """ 

271 Executes a parsed SELECT SQL query on the 'blogposts' resource of the Confluence API. 

272 

273 Args: 

274 conditions (List[FilterCondition]): The list of parsed filter conditions. 

275 limit (int): The maximum number of records to return. 

276 sort (List[SortColumn]): The list of parsed sort columns. 

277 targets (List[str]): The list of target columns to return. 

278 """ 

279 blogposts = [] 

280 client: ConfluenceAPIClient = self.handler.connect() 

281 

282 post_ids, space_ids, statuses, title = None, None, None, None 

283 for condition in conditions: 

284 if condition.column == "id": 

285 if condition.op == FilterOperator.EQUAL: 285 ↛ 288line 285 didn't jump to line 288 because the condition on line 285 was always true

286 post_ids = [condition.value] 

287 

288 elif condition.op == FilterOperator.IN: 

289 post_ids = condition.value 

290 

291 else: 

292 raise ValueError(f"Unsupported operator '{condition.op}' for column 'id'.") 

293 

294 condition.applied = True 

295 

296 if condition.column == "spaceId": 

297 if condition.op == FilterOperator.EQUAL: 297 ↛ 300line 297 didn't jump to line 300 because the condition on line 297 was always true

298 space_ids = [condition.value] 

299 

300 elif condition.op == FilterOperator.IN: 

301 space_ids = condition.value 

302 

303 else: 

304 raise ValueError(f"Unsupported operator '{condition.op}' for column 'spaceKey'.") 

305 

306 condition.applied = True 

307 

308 if condition.column == "status": 

309 if condition.op == FilterOperator.EQUAL: 309 ↛ 312line 309 didn't jump to line 312 because the condition on line 309 was always true

310 statuses = [condition.value] 

311 

312 elif condition.op == FilterOperator.IN: 

313 statuses = condition.value 

314 

315 else: 

316 raise ValueError(f"Unsupported operator '{condition.op}' for column 'status'.") 

317 

318 condition.applied = True 

319 

320 if condition.column == "title": 

321 if condition.op == FilterOperator.EQUAL: 321 ↛ 325line 321 didn't jump to line 325 because the condition on line 321 was always true

322 title = condition.value 

323 

324 else: 

325 raise ValueError(f"Unsupported operator '{condition.op}' for column 'title'.") 

326 

327 condition.applied = True 

328 

329 sort_condition = None 

330 if sort: 330 ↛ 331line 330 didn't jump to line 331 because the condition on line 330 was never true

331 for sort_column in sort: 

332 if sort_column.column in ["id", "title", "createdAt"]: 

333 sort_condition = sort_column.column if sort_column.column != "createdAt" else "created-date" 

334 if not sort_column.ascending: 

335 sort_condition = f"-{sort_condition}" 

336 

337 sort_column.applied = True 

338 break 

339 

340 blogposts = client.get_blogposts( 

341 post_ids=post_ids, 

342 space_ids=space_ids, 

343 statuses=statuses, 

344 title=title, 

345 sort_condition=sort_condition, 

346 limit=limit, 

347 ) 

348 

349 blogposts_df = pd.json_normalize(blogposts, sep="_") 

350 blogposts_df = blogposts_df[self.get_columns()] 

351 

352 return blogposts_df 

353 

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

355 """ 

356 Retrieves the attributes (columns) of the 'blogposts' resource. 

357 

358 Returns: 

359 List[Text]: A list of attributes (columns) of the 'blogposts' resource. 

360 """ 

361 return [ 

362 "id", 

363 "status", 

364 "title", 

365 "spaceId", 

366 "authorId", 

367 "createdAt", 

368 "version_createdAt", 

369 "version_message", 

370 "version_number", 

371 "version_minorEdit", 

372 "version_authorId", 

373 "body_storage_representation", 

374 "body_storage_value", 

375 "_links_webui", 

376 "_links_editui", 

377 "_links_tinyui", 

378 ] 

379 

380 

381class ConfluenceWhiteboardsTable(APIResource): 

382 """ 

383 The table abstraction for the 'whiteboards' resource of the Confluence API. 

384 """ 

385 

386 def list( 

387 self, 

388 conditions: List[FilterCondition] = None, 

389 limit: int = None, 

390 sort: List[SortColumn] = None, 

391 targets: List[str] = None, 

392 **kwargs, 

393 ): 

394 """ 

395 Executes a parsed SELECT SQL query on the 'whiteboards' resource of the Confluence API. 

396 

397 Args: 

398 conditions (List[FilterCondition]): The list of parsed filter conditions. 

399 limit (int): The maximum number of records to return. 

400 sort (List[SortColumn]): The list of parsed sort columns. 

401 targets (List[str]): The list of target columns to return. 

402 """ 

403 whiteboards = [] 

404 client: ConfluenceAPIClient = self.handler.connect() 

405 

406 whiteboard_ids = None 

407 for condition in conditions: 

408 if condition.column == "id": 408 ↛ 407line 408 didn't jump to line 407 because the condition on line 408 was always true

409 if condition.op == FilterOperator.EQUAL: 409 ↛ 412line 409 didn't jump to line 412 because the condition on line 409 was always true

410 whiteboard_ids = [condition.value] 

411 

412 elif condition.op == FilterOperator.IN: 

413 whiteboard_ids = condition.value 

414 

415 else: 

416 raise ValueError(f"Unsupported operator '{condition.op}' for column 'id'.") 

417 

418 condition.applied = True 

419 

420 if not whiteboard_ids: 

421 raise ValueError("The 'id' column must be provided in the WHERE clause.") 

422 

423 whiteboards = [client.get_whiteboard_by_id(whiteboard_id) for whiteboard_id in whiteboard_ids] 

424 

425 whiteboards_df = pd.json_normalize(whiteboards, sep="_") 

426 whiteboards_df = whiteboards_df[self.get_columns()] 

427 

428 return whiteboards_df 

429 

430 def get_columns(self) -> List[str]: 

431 """ 

432 Retrieves the attributes (columns) of the 'whiteboards' resource. 

433 

434 Returns: 

435 List[Text]: A list of attributes (columns) of the 'whiteboards' resource. 

436 """ 

437 return [ 

438 "id", 

439 "type", 

440 "status", 

441 "title", 

442 "parentId", 

443 "parentType", 

444 "position", 

445 "authorId", 

446 "ownerId", 

447 "createdAt", 

448 "version_createdAt", 

449 "version_message", 

450 "version_number", 

451 "version_minorEdit", 

452 "version_authorId", 

453 ] 

454 

455 

456class ConfluenceDatabasesTable(APIResource): 

457 """ 

458 The table abstraction for the 'databases' resource of the Confluence API. 

459 """ 

460 

461 def list( 

462 self, 

463 conditions: List[FilterCondition] = None, 

464 limit: int = None, 

465 sort: List[SortColumn] = None, 

466 targets: List[str] = None, 

467 **kwargs, 

468 ): 

469 """ 

470 Executes a parsed SELECT SQL query on the 'databases' resource of the Confluence API. 

471 

472 Args: 

473 conditions (List[FilterCondition]): The list of parsed filter conditions. 

474 limit (int): The maximum number of records to return. 

475 sort (List[SortColumn]): The list of parsed sort columns. 

476 targets (List[str]): The list of target columns to return. 

477 """ 

478 databases = [] 

479 client: ConfluenceAPIClient = self.handler.connect() 

480 

481 database_ids = None 

482 for condition in conditions: 

483 if condition.column == "id": 483 ↛ 482line 483 didn't jump to line 482 because the condition on line 483 was always true

484 if condition.op == FilterOperator.EQUAL: 484 ↛ 487line 484 didn't jump to line 487 because the condition on line 484 was always true

485 database_ids = [condition.value] 

486 

487 elif condition.op == FilterOperator.IN: 

488 database_ids = condition.value 

489 

490 else: 

491 raise ValueError(f"Unsupported operator '{condition.op}' for column 'id'.") 

492 

493 condition.applied = True 

494 

495 if not database_ids: 

496 raise ValueError("The 'id' column must be provided in the WHERE clause.") 

497 

498 databases = [client.get_database_by_id(database_id) for database_id in database_ids] 

499 

500 databases_df = pd.json_normalize(databases, sep="_") 

501 databases_df = databases_df[self.get_columns()] 

502 

503 return databases_df 

504 

505 def get_columns(self) -> List[str]: 

506 """ 

507 Retrieves the attributes (columns) of the 'databases' resource. 

508 

509 Returns: 

510 List[Text]: A list of attributes (columns) of the 'databases' resource. 

511 """ 

512 return [ 

513 "id", 

514 "type", 

515 "status", 

516 "title", 

517 "parentId", 

518 "parentType", 

519 "position", 

520 "authorId", 

521 "ownerId", 

522 "createdAt", 

523 "version_createdAt", 

524 "version_message", 

525 "version_number", 

526 "version_minorEdit", 

527 "version_authorId", 

528 ] 

529 

530 

531class ConfluenceTasksTable(APIResource): 

532 """ 

533 The table abstraction for the 'tasks' resource of the Confluence API. 

534 """ 

535 

536 def list( 

537 self, 

538 conditions: List[FilterCondition] = None, 

539 limit: int = None, 

540 sort: List[SortColumn] = None, 

541 targets: List[str] = None, 

542 **kwargs, 

543 ): 

544 """ 

545 Executes a parsed SELECT SQL query on the 'tasks' resource of the Confluence API. 

546 

547 Args: 

548 conditions (List[FilterCondition]): The list of parsed filter conditions. 

549 limit (int): The maximum number of records to return. 

550 sort (List[SortColumn]): The list of parsed sort columns. 

551 targets (List[str]): The list of target columns to return. 

552 """ 

553 tasks = [] 

554 client: ConfluenceAPIClient = self.handler.connect() 

555 

556 task_ids = None 

557 space_ids = None 

558 page_ids = None 

559 blogpost_ids = None 

560 created_by_ids = None 

561 assigned_to_ids = None 

562 completed_by_ids = None 

563 status = None 

564 

565 for condition in conditions: 

566 if condition.column == "id": 

567 if condition.op == FilterOperator.EQUAL: 567 ↛ 570line 567 didn't jump to line 570 because the condition on line 567 was always true

568 task_ids = [condition.value] 

569 

570 elif condition.op == FilterOperator.IN: 

571 task_ids = condition.value 

572 

573 else: 

574 raise ValueError(f"Unsupported operator '{condition.op}' for column 'id'.") 

575 

576 condition.applied = True 

577 

578 if condition.column == "spaceId": 

579 if condition.op == FilterOperator.EQUAL: 579 ↛ 582line 579 didn't jump to line 582 because the condition on line 579 was always true

580 space_ids = [condition.value] 

581 

582 elif condition.op == FilterOperator.IN: 

583 space_ids = condition.value 

584 

585 else: 

586 raise ValueError(f"Unsupported operator '{condition.op}' for column 'spaceId'.") 

587 

588 condition.applied = True 

589 

590 if condition.column == "pageId": 

591 if condition.op == FilterOperator.EQUAL: 591 ↛ 594line 591 didn't jump to line 594 because the condition on line 591 was always true

592 page_ids = [condition.value] 

593 

594 elif condition.op == FilterOperator.IN: 

595 page_ids = condition.value 

596 

597 else: 

598 raise ValueError(f"Unsupported operator '{condition.op}' for column 'pageId'.") 

599 

600 condition.applied = True 

601 

602 if condition.column == "blogPostId": 602 ↛ 603line 602 didn't jump to line 603 because the condition on line 602 was never true

603 if condition.op == FilterOperator.EQUAL: 

604 blogpost_ids = [condition.value] 

605 

606 elif condition.op == FilterOperator.IN: 

607 blogpost_ids = condition.value 

608 

609 else: 

610 raise ValueError(f"Unsupported operator '{condition.op}' for column 'blogPostId'.") 

611 

612 condition.applied = True 

613 

614 if condition.column == "createdBy": 

615 if condition.op == FilterOperator.EQUAL: 615 ↛ 618line 615 didn't jump to line 618 because the condition on line 615 was always true

616 created_by_ids = [condition.value] 

617 

618 elif condition.op == FilterOperator.IN: 

619 created_by_ids = condition.value 

620 

621 else: 

622 raise ValueError(f"Unsupported operator '{condition.op}' for column 'createdBy'.") 

623 

624 condition.applied = True 

625 

626 if condition.column == "assignedTo": 

627 if condition.op == FilterOperator.EQUAL: 627 ↛ 630line 627 didn't jump to line 630 because the condition on line 627 was always true

628 assigned_to_ids = [condition.value] 

629 

630 elif condition.op == FilterOperator.IN: 

631 assigned_to_ids = condition.value 

632 

633 else: 

634 raise ValueError(f"Unsupported operator '{condition.op}' for column 'assignedTo'.") 

635 

636 condition.applied = True 

637 

638 if condition.column == "completedBy": 

639 if condition.op == FilterOperator.EQUAL: 639 ↛ 642line 639 didn't jump to line 642 because the condition on line 639 was always true

640 completed_by_ids = [condition.value] 

641 

642 elif condition.op == FilterOperator.IN: 

643 completed_by_ids = condition.value 

644 

645 else: 

646 raise ValueError(f"Unsupported operator '{condition.op}' for column 'completedBy'.") 

647 

648 condition.applied = True 

649 

650 if condition.column == "status": 

651 if condition.op == FilterOperator.EQUAL: 651 ↛ 655line 651 didn't jump to line 655 because the condition on line 651 was always true

652 status = condition.value 

653 

654 else: 

655 raise ValueError(f"Unsupported operator '{condition.op}' for column 'status'.") 

656 

657 condition.applied = True 

658 

659 tasks = client.get_tasks( 

660 task_ids=task_ids, 

661 space_ids=space_ids, 

662 page_ids=page_ids, 

663 blogpost_ids=blogpost_ids, 

664 created_by_ids=created_by_ids, 

665 assigned_to_ids=assigned_to_ids, 

666 completed_by_ids=completed_by_ids, 

667 status=status, 

668 limit=limit, 

669 ) 

670 tasks_df = pd.json_normalize(tasks, sep="_") 

671 

672 # Each task will have either a 'pageId' or 'blogPostId' but not both. 

673 # In situations where they are all from the same resource, the other column will be empty. 

674 # We will fill the empty column with None to ensure consistency. 

675 for column in ["pageId", "blogPostId"]: 

676 if column not in tasks_df.columns: 676 ↛ 677line 676 didn't jump to line 677 because the condition on line 676 was never true

677 tasks_df[column] = None 

678 

679 tasks_df = tasks_df[self.get_columns()] 

680 

681 return tasks_df 

682 

683 def get_columns(self) -> List[str]: 

684 """ 

685 Retrieves the attributes (columns) of the 'tasks' resource. 

686 

687 Returns: 

688 List[Text]: A list of attributes (columns) of the 'tasks' resource. 

689 """ 

690 return [ 

691 "id", 

692 "localId", 

693 "spaceId", 

694 "pageId", 

695 "blogPostId", 

696 "status", 

697 "body_storage_representation", 

698 "body_storage_value", 

699 "createdBy", 

700 "assignedTo", 

701 "completedBy", 

702 "createdAt", 

703 "updatedAt", 

704 "dueAt", 

705 "completedAt", 

706 ]