wjones
1abf61a05f
- Introduced PostgresSubprocessException to handle errors during PostgreSQL subprocess execution. - Refactored PostgresDatabaseProvider to improve schema initialization script discovery. - Enhanced error handling for psql command execution, including logging of output and errors. - Implemented methods to find the schema initialization script and the psql executable path.
134 lines
5.8 KiB
PowerShell
134 lines
5.8 KiB
PowerShell
# PowerShell script to convert remaining SQL identifiers from quoted PascalCase to lowercase/snake_case
|
|
# This script handles constraints, indexes, and foreign keys
|
|
|
|
param([string]$filepath = "scripts/sql/schema_init/create_database_schema.sql")
|
|
|
|
$content = Get-Content $filepath -Raw
|
|
|
|
# Table name mappings for constraint/index/FK replacement
|
|
$tableMappings = @{
|
|
'"ActivityLogs"' = 'activity_logs'
|
|
'"ApiKeys"' = 'api_keys'
|
|
'"DeviceOptions"' = 'device_options'
|
|
'"Devices"' = 'devices'
|
|
'"CustomItemDisplayPreferences"' = 'custom_item_display_preferences'
|
|
'"DisplayPreferences"' = 'display_preferences'
|
|
'"HomeSection"' = 'home_sections'
|
|
'"ItemDisplayPreferences"' = 'item_display_preferences'
|
|
'"AncestorIds"' = 'ancestor_ids'
|
|
'"AttachmentStreamInfos"' = 'attachment_stream_infos'
|
|
'"BaseItemImageInfos"' = 'base_item_image_infos'
|
|
'"BaseItemMetadataFields"' = 'base_item_metadata_fields'
|
|
'"BaseItemProviders"' = 'base_item_providers'
|
|
'"BaseItemTrailerTypes"' = 'base_item_trailer_types'
|
|
'"BaseItems"' = 'base_items'
|
|
'"Chapters"' = 'chapters'
|
|
'"ImageInfos"' = 'image_infos'
|
|
'"ItemValues"' = 'item_values'
|
|
'"ItemValuesMap"' = 'item_values_map'
|
|
'"KeyframeData"' = 'keyframe_data'
|
|
'"MediaSegments"' = 'media_segments'
|
|
'"MediaStreamInfos"' = 'media_stream_infos'
|
|
'"PeopleBaseItemMap"' = 'people_base_item_map'
|
|
'"Peoples"' = 'peoples'
|
|
'"TrickplayInfos"' = 'trickplay_infos'
|
|
'"UserData"' = 'user_data'
|
|
'"__EFMigrationsHistory"' = '__ef_migrations_history'
|
|
'"AccessSchedules"' = 'access_schedules'
|
|
'"Permissions"' = 'permissions'
|
|
'"Preferences"' = 'preferences'
|
|
'"Users"' = 'users'
|
|
}
|
|
|
|
# Column name mappings (critical columns that appear in constraints)
|
|
$columnMappings = @{
|
|
'"Id"' = 'id'
|
|
'"ParentItemId"' = 'parent_item_id'
|
|
'"ItemId"' = 'item_id'
|
|
'"Index"' = 'index'
|
|
'"UserId"' = 'user_id'
|
|
'"DisplayPreferencesId"' = 'display_preferences_id'
|
|
'"ItemValueId"' = 'item_value_id'
|
|
'"ChapterIndex"' = 'chapter_index'
|
|
'"PeopleId"' = 'people_id'
|
|
'"StreamIndex"' = 'stream_index'
|
|
'"CustomDataKey"' = 'custom_data_key'
|
|
'"MigrationId"' = 'migration_id'
|
|
'"ProductVersion"' = 'product_version'
|
|
'"DayOfWeek"' = 'day_of_week'
|
|
'"StartHour"' = 'start_hour'
|
|
'"EndHour"' = 'end_hour'
|
|
'"Kind"' = 'kind'
|
|
'"Value"' = 'value'
|
|
'"RowVersion"' = 'row_version'
|
|
'"Permission_Permissions_Guid"' = 'permission_permissions_guid'
|
|
'"Preference_Preferences_Guid"' = 'preference_preferences_guid'
|
|
}
|
|
|
|
# Apply table replacements
|
|
foreach ($oldTable in $tableMappings.Keys) {
|
|
$newTable = $tableMappings[$oldTable]
|
|
$content = $content -replace [regex]::Escape($oldTable), $newTable
|
|
}
|
|
|
|
# Apply column replacements
|
|
foreach ($oldCol in $columnMappings.Keys) {
|
|
$newCol = $columnMappings[$oldCol]
|
|
$content = $content -replace [regex]::Escape($oldCol), $newCol
|
|
}
|
|
|
|
# Constraint name replacements (remove quotes from constraint names)
|
|
$content = $content -replace 'ADD CONSTRAINT "([^"]+)"', 'ADD CONSTRAINT $1'
|
|
|
|
# Index name replacements (remove quotes from index names)
|
|
$content = $content -replace 'CREATE (\w+ )?INDEX "([^"]+)"', 'CREATE $1INDEX $2'
|
|
|
|
# Handle remaining quoted identifiers in special cases
|
|
$content = $content -replace '"ProviderId"', 'provider_id'
|
|
$content = $content -replace '"ProviderValue"', 'provider_value'
|
|
$content = $content -replace '"SortOrder"', 'sort_order'
|
|
$content = $content -replace '"ListOrder"', 'list_order'
|
|
$content = $content -replace '"Role"', 'role'
|
|
$content = $content -replace '"Name"', 'name'
|
|
$content = $content -replace '"Path"', 'path'
|
|
$content = $content -replace '"DateCreated"', 'date_created'
|
|
$content = $content -replace '"DateLastActivity"', 'date_last_activity'
|
|
$content = $content -replace '"DateModified"', 'date_modified'
|
|
$content = $content -replace '"DateLastSaved"', 'date_last_saved'
|
|
$content = $content -replace '"LastPlayedDate"', 'last_played_date'
|
|
$content = $content -replace '"Played"', 'played'
|
|
$content = $content -replace '"IsFavorite"', 'is_favorite'
|
|
$content = $content -replace '"PlaybackPositionTicks"', 'playback_position_ticks'
|
|
$content = $content -replace '"Type"', 'type'
|
|
$content = $content -replace '"AccessToken"', 'access_token'
|
|
$content = $content -replace '"DeviceId"', 'device_id'
|
|
$content = $content -replace '"Client"', 'client'
|
|
$content = $content -replace '"Username"', 'username'
|
|
$content = $content -replace '"StartDate"', 'start_date'
|
|
$content = $content -replace '"EndDate"', 'end_date'
|
|
$content = $content -replace '"IsFolder"', 'is_folder'
|
|
$content = $content -replace '"IsVirtualItem"', 'is_virtual_item'
|
|
$content = $content -replace '"PresentationUniqueKey"', 'presentation_unique_key'
|
|
$content = $content -replace '"SeriesPresentationUniqueKey"', 'series_presentation_unique_key'
|
|
$content = $content -replace '"TopParentId"', 'top_parent_id'
|
|
$content = $content -replace '"ProviderId"', 'provider_id'
|
|
$content = $content -replace '"StreamType"', 'stream_type'
|
|
$content = $content -replace '"Language"', 'language'
|
|
$content = $content -replace '"Codec"', 'codec'
|
|
$content = $content -replace '"CommunityRating"', 'community_rating'
|
|
$content = $content -replace '"ProductionYear"', 'production_year'
|
|
$content = $content -replace '"PremiereDate"', 'premiere_date'
|
|
$content = $content -replace '"SortName"', 'sort_name'
|
|
$content = $content -replace '"CleanValue"', 'clean_value'
|
|
$content = $content -replace '"TotalDuration"', 'total_duration'
|
|
$content = $content -replace '"KeyframeTicks"', 'keyframe_ticks'
|
|
$content = $content -replace '"EndTicks"', 'end_ticks'
|
|
$content = $content -replace '"StartTicks"', 'start_ticks'
|
|
$content = $content -replace '"SegmentProviderId"', 'segment_provider_id'
|
|
$content = $content -replace '"AudioStreamIndex"', 'audio_stream_index'
|
|
$content = $content -replace '"SubtitleStreamIndex"', 'subtitle_stream_index'
|
|
$content = $content -replace '"RetentionDate"', 'retention_date'
|
|
|
|
Set-Content -Path $filepath -Value $content
|
|
Write-Host "SQL identifiers conversion complete!"
|