94 lines
4.2 KiB
Markdown
94 lines
4.2 KiB
Markdown
# UserNovelDataService Backfill Scripts
|
|
|
|
SQL scripts for backfilling data from UserService and NovelService into UserNovelDataService.
|
|
|
|
## Prerequisites
|
|
|
|
1. **Run EF migrations** on the UserNovelDataService database to ensure all tables exist:
|
|
```bash
|
|
dotnet ef database update --project FictionArchive.Service.UserNovelDataService
|
|
```
|
|
|
|
This will apply the `AddNovelVolumeChapter` migration which creates:
|
|
- `Novels` table (Id, CreatedTime, LastUpdatedTime)
|
|
- `Volumes` table (Id, NovelId FK, CreatedTime, LastUpdatedTime)
|
|
- `Chapters` table (Id, VolumeId FK, CreatedTime, LastUpdatedTime)
|
|
|
|
## Execution Order
|
|
|
|
Run scripts in numeric order:
|
|
|
|
### Extraction (run against source databases)
|
|
1. `01_extract_users_from_userservice.sql` - Run against **UserService** DB
|
|
2. `02_extract_novels_from_novelservice.sql` - Run against **NovelService** DB
|
|
3. `03_extract_volumes_from_novelservice.sql` - Run against **NovelService** DB
|
|
4. `04_extract_chapters_from_novelservice.sql` - Run against **NovelService** DB
|
|
|
|
### Insertion (run against UserNovelDataService database)
|
|
5. `05_insert_users_to_usernoveldataservice.sql`
|
|
6. `06_insert_novels_to_usernoveldataservice.sql`
|
|
7. `07_insert_volumes_to_usernoveldataservice.sql`
|
|
8. `08_insert_chapters_to_usernoveldataservice.sql`
|
|
|
|
## Methods
|
|
|
|
Each script provides three options:
|
|
|
|
1. **SELECT for review** - Review data before export
|
|
2. **Generate INSERT statements** - Creates individual INSERT statements (good for small datasets)
|
|
3. **CSV export/import** - Use PostgreSQL `\copy` for bulk operations (recommended for large datasets)
|
|
|
|
## Example Workflow
|
|
|
|
### Using CSV Export/Import (Recommended)
|
|
|
|
```bash
|
|
# 1. Export from source databases
|
|
psql -h localhost -U postgres -d userservice -c "\copy (SELECT \"Id\", \"OAuthProviderId\", \"CreatedTime\", \"LastUpdatedTime\" FROM \"Users\" WHERE \"Disabled\" = false) TO '/tmp/users_export.csv' WITH CSV HEADER"
|
|
|
|
psql -h localhost -U postgres -d novelservice -c "\copy (SELECT \"Id\", \"CreatedTime\", \"LastUpdatedTime\" FROM \"Novels\") TO '/tmp/novels_export.csv' WITH CSV HEADER"
|
|
|
|
psql -h localhost -U postgres -d novelservice -c "\copy (SELECT \"Id\", \"NovelId\", \"CreatedTime\", \"LastUpdatedTime\" FROM \"Volume\" ORDER BY \"NovelId\", \"Id\") TO '/tmp/volumes_export.csv' WITH CSV HEADER"
|
|
|
|
psql -h localhost -U postgres -d novelservice -c "\copy (SELECT \"Id\", \"VolumeId\", \"CreatedTime\", \"LastUpdatedTime\" FROM \"Chapter\" ORDER BY \"VolumeId\", \"Id\") TO '/tmp/chapters_export.csv' WITH CSV HEADER"
|
|
|
|
# 2. Import into UserNovelDataService (order matters due to FK constraints!)
|
|
psql -h localhost -U postgres -d usernoveldataservice -c "\copy \"Users\" (\"Id\", \"OAuthProviderId\", \"CreatedTime\", \"LastUpdatedTime\") FROM '/tmp/users_export.csv' WITH CSV HEADER"
|
|
|
|
psql -h localhost -U postgres -d usernoveldataservice -c "\copy \"Novels\" (\"Id\", \"CreatedTime\", \"LastUpdatedTime\") FROM '/tmp/novels_export.csv' WITH CSV HEADER"
|
|
|
|
psql -h localhost -U postgres -d usernoveldataservice -c "\copy \"Volumes\" (\"Id\", \"NovelId\", \"CreatedTime\", \"LastUpdatedTime\") FROM '/tmp/volumes_export.csv' WITH CSV HEADER"
|
|
|
|
psql -h localhost -U postgres -d usernoveldataservice -c "\copy \"Chapters\" (\"Id\", \"VolumeId\", \"CreatedTime\", \"LastUpdatedTime\") FROM '/tmp/chapters_export.csv' WITH CSV HEADER"
|
|
```
|
|
|
|
**Important**: Insert order matters due to foreign key constraints:
|
|
1. Users (no dependencies)
|
|
2. Novels (no dependencies)
|
|
3. Volumes (depends on Novels)
|
|
4. Chapters (depends on Volumes)
|
|
|
|
### Using dblink (Cross-database queries)
|
|
|
|
If both databases are on the same PostgreSQL server, you can use `dblink` extension for direct cross-database inserts. See the commented examples in each insert script.
|
|
|
|
## Verification
|
|
|
|
After running the backfill, verify counts match:
|
|
|
|
```sql
|
|
-- Run on UserService DB
|
|
SELECT COUNT(*) as user_count FROM "Users" WHERE "Disabled" = false;
|
|
|
|
-- Run on NovelService DB
|
|
SELECT COUNT(*) as novel_count FROM "Novels";
|
|
SELECT COUNT(*) as volume_count FROM "Volume";
|
|
SELECT COUNT(*) as chapter_count FROM "Chapter";
|
|
|
|
-- Run on UserNovelDataService DB
|
|
SELECT COUNT(*) as user_count FROM "Users";
|
|
SELECT COUNT(*) as novel_count FROM "Novels";
|
|
SELECT COUNT(*) as volume_count FROM "Volumes";
|
|
SELECT COUNT(*) as chapter_count FROM "Chapters";
|
|
```
|