Migrate businesses altered from (BC, ULC,CC) to BEN
- Dominant language
- JavaScript
- Stars
- 23
- Forks
- 62
- Avg merge
- 24m
- Merged PRs (30d)
- 1
Description
These businesses are active in Lear and users are filing new filings in Lear (missing oldest filings)
Find all businesses altered from (BC, ULC, CC, C, CUL, CCC) -> (BEN, CBEN) - (filed through notebook)
```
-- Query in lear db
select b.identifier, f.*
from public.filings f
join businesses b on b.id = f.business_id
where
filing_type='alteration'
and meta_data->'alteration'->>'fromLegalType' in ('BC', 'ULC', 'CC', 'C', 'CUL', 'CCC')
and meta_data->'alteration'->>'toLegalType' in ('BEN', 'CBEN')
and b.id in (
select b.id from public.filings f
join businesses b on b.id=f.business_id
where
filing_type='lear_epoch'
and b.legal_type in ('BEN','BC', 'ULC', 'CC', 'CBEN','C', 'CUL', 'CCC')
and filing_json->'filing'->'business'->>'legalType' <> 'BEN')
```
Approach to migrate BC -> BEN
Load previous filings with version data (no changes to primary table), while loading transaction_id for the previous filings should be smaller. Lear require previous filings should have smaller transaction_id, which is used to identify the order of filings
- Option 1: Find all missing transaction ids from the transaction table. (if it takes too long to execute this query then dump the whole missing transaction_id's into a temporary table and use this transaction id to load previous filings)
```
-- Query to find missing transaction_id
SELECT gs.i AS missing_transaction_id
FROM generate_series(1, ) AS gs(i)
LEFT JOIN public.transaction t ON t.id = gs.i
WHERE t.id IS NULL and NOT EXISTS (select * from (
SELECT 'filings' as table_name, count(1) FROM public.filings WHERE transaction_id=gs.i UNION
SELECT 'addresses' as table_name, count(1) FROM public.addresses_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'aliases' as table_name, count(1) FROM public.aliases_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'amalgamating_businesses' as table_name, count(1) FROM public.amalgamating_businesses_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'amalgamations' as table_name, count(1) FROM public.amalgamations_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'businesses' as table_name, count(1) FROM public.businesses_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'jurisdictions' as table_name, count(1) FROM public.jurisdictions_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'offices_held' as table_name, count(1) FROM public.offices_held_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'offices' as table_name, count(1) FROM public.offices_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'parties' as table_name, count(1) FROM public.parties_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'party_roles' as table_name, count(1) FROM public.party_roles_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'resolutions' as table_name, count(1) FROM public.resolutions_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'share_classes' as table_name, count(1) FROM public.share_classes_version WHERE transaction_id=gs.i or end_transaction_id=gs.i UNION
SELECT 'share_series' as table_name, count(1) FROM public.share_series_version WHERE transaction_id=gs.i or end_transaction_id=gs.i
) t2 WHERE count > 0)
ORDER BY gs.i DESC;
- Option 2: Update all the existing filing with new transaction id which is greater than the newly loaded filings
```
Note: Businesses involved in multiple conversion (BC1255957, BC0460007) from BC -> BEN and then BEN -> BC - (Before Directed Launch) - Need to include in both migration scenario
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.