346 lines
9.6 KiB
PowerShell
346 lines
9.6 KiB
PowerShell
<#
|
|
Deploy Upright DB Schema for Appwrite 1.8.1 (TablesDB)
|
|
- Writes appwrite.config.json
|
|
- Runs: appwrite push tables --force
|
|
|
|
Usage example:
|
|
.\deploy-upright-schema.ps1 `
|
|
-ProjectId "YOUR_PROJECT_ID" `
|
|
-Endpoint "https://YOUR_HOST_OR_REGION/v1" `
|
|
-DatabaseId "upright" `
|
|
-DatabaseName "upright"
|
|
#>
|
|
|
|
param(
|
|
[Parameter(Mandatory=$true)][string]$ProjectId,
|
|
[Parameter(Mandatory=$true)][string]$Endpoint,
|
|
[Parameter(Mandatory=$true)][string]$DatabaseId,
|
|
[Parameter(Mandatory=$false)][string]$DatabaseName = "upright"
|
|
)
|
|
|
|
$ErrorActionPreference = "Stop"
|
|
|
|
function New-IndexObject {
|
|
param(
|
|
[Parameter(Mandatory=$true)][string]$Key,
|
|
[Parameter(Mandatory=$true)][ValidateSet("key","unique","fulltext")][string]$Type,
|
|
[Parameter(Mandatory=$true)][string[]]$Attributes
|
|
)
|
|
return @{
|
|
key = $Key
|
|
type = $Type
|
|
attributes = $Attributes
|
|
}
|
|
}
|
|
|
|
function New-ColumnString {
|
|
param(
|
|
[Parameter(Mandatory=$true)][string]$Key,
|
|
[int]$Size = 255,
|
|
[bool]$Required = $false,
|
|
[bool]$Array = $false,
|
|
$Default = $null
|
|
)
|
|
return @{
|
|
key = $Key
|
|
type = "string"
|
|
status = "available"
|
|
error = ""
|
|
required = $Required
|
|
array = $Array
|
|
size = $Size
|
|
default = $Default
|
|
}
|
|
}
|
|
|
|
function New-ColumnUrl {
|
|
param(
|
|
[Parameter(Mandatory=$true)][string]$Key,
|
|
[int]$Size = 2048,
|
|
[bool]$Required = $false,
|
|
[bool]$Array = $false,
|
|
$Default = $null
|
|
)
|
|
# In config, url is usually type "string" with format "url"
|
|
return @{
|
|
key = $Key
|
|
type = "string"
|
|
format = "url"
|
|
status = "available"
|
|
error = ""
|
|
required = $Required
|
|
array = $Array
|
|
size = $Size
|
|
default = $Default
|
|
}
|
|
}
|
|
|
|
function New-ColumnDatetime {
|
|
param(
|
|
[Parameter(Mandatory=$true)][string]$Key,
|
|
[bool]$Required = $false,
|
|
[bool]$Array = $false,
|
|
$Default = $null
|
|
)
|
|
return @{
|
|
key = $Key
|
|
type = "datetime"
|
|
status = "available"
|
|
error = ""
|
|
required = $Required
|
|
array = $Array
|
|
default = $Default
|
|
}
|
|
}
|
|
|
|
function New-ColumnBoolean {
|
|
param(
|
|
[Parameter(Mandatory=$true)][string]$Key,
|
|
[bool]$Required = $false,
|
|
[bool]$Array = $false,
|
|
$Default = $null
|
|
)
|
|
return @{
|
|
key = $Key
|
|
type = "boolean"
|
|
status = "available"
|
|
error = ""
|
|
required = $Required
|
|
array = $Array
|
|
default = $Default
|
|
}
|
|
}
|
|
|
|
function New-ColumnInteger {
|
|
param(
|
|
[Parameter(Mandatory=$true)][string]$Key,
|
|
[bool]$Required = $false,
|
|
[bool]$Array = $false,
|
|
$Default = $null
|
|
)
|
|
return @{
|
|
key = $Key
|
|
type = "integer"
|
|
status = "available"
|
|
error = ""
|
|
required = $Required
|
|
array = $Array
|
|
default = $Default
|
|
}
|
|
}
|
|
|
|
function New-ColumnFloat {
|
|
param(
|
|
[Parameter(Mandatory=$true)][string]$Key,
|
|
[bool]$Required = $false,
|
|
[bool]$Array = $false,
|
|
$Default = $null
|
|
)
|
|
return @{
|
|
key = $Key
|
|
type = "float"
|
|
status = "available"
|
|
error = ""
|
|
required = $Required
|
|
array = $Array
|
|
default = $Default
|
|
}
|
|
}
|
|
|
|
function New-ColumnEnumString {
|
|
param(
|
|
[Parameter(Mandatory=$true)][string]$Key,
|
|
[Parameter(Mandatory=$true)][string[]]$Elements,
|
|
[int]$Size = 50,
|
|
[bool]$Required = $false,
|
|
[bool]$Array = $false,
|
|
$Default = $null
|
|
)
|
|
# Enum in Appwrite config is typically string with format "enum" and "elements"
|
|
return @{
|
|
key = $Key
|
|
type = "string"
|
|
format = "enum"
|
|
elements = $Elements
|
|
status = "available"
|
|
error = ""
|
|
required = $Required
|
|
array = $Array
|
|
size = $Size
|
|
default = $Default
|
|
}
|
|
}
|
|
|
|
# Tables
|
|
$usersTableId = "users"
|
|
$accountsTableId = "accounts"
|
|
$productsTableId = "products"
|
|
$productDetailsTableId = "product_details"
|
|
|
|
$tables = @()
|
|
|
|
# users
|
|
$tables += @{
|
|
'$id' = $usersTableId
|
|
'$permissions' = @(
|
|
'create("any")','read("any")','update("any")','delete("any")'
|
|
)
|
|
databaseId = $DatabaseId
|
|
name = "users"
|
|
enabled = $true
|
|
rowSecurity = $false
|
|
columns = @(
|
|
New-ColumnDatetime -Key "user_created_at"
|
|
New-ColumnDatetime -Key "user_updated_at"
|
|
)
|
|
indexes = @()
|
|
}
|
|
|
|
# accounts
|
|
$tables += @{
|
|
'$id' = $accountsTableId
|
|
'$permissions' = @(
|
|
'create("any")','read("any")','update("any")','delete("any")'
|
|
)
|
|
databaseId = $DatabaseId
|
|
name = "accounts"
|
|
enabled = $true
|
|
rowSecurity = $false
|
|
columns = @(
|
|
New-ColumnString -Key "account_owner_user_id" -Size 128 -Required $false
|
|
New-ColumnBoolean -Key "account_team" -Required $true -Default $false
|
|
|
|
New-ColumnEnumString -Key "account_platform" -Elements @("amazon","ebay") -Size 20 -Required $true
|
|
New-ColumnString -Key "account_platform_account_id" -Size 128 -Required $true
|
|
New-ColumnString -Key "account_platform_market" -Size 50 -Required $true
|
|
|
|
New-ColumnString -Key "account_shop_name" -Size 256 -Required $false
|
|
New-ColumnUrl -Key "account_url" -Size 2048 -Required $false
|
|
|
|
New-ColumnEnumString -Key "account_status" -Elements @("active","unknown","disabled") -Size 20 -Required $false
|
|
|
|
New-ColumnDatetime -Key "account_created_at"
|
|
New-ColumnDatetime -Key "account_updated_at"
|
|
)
|
|
indexes = @(
|
|
(New-IndexObject -Key "uniq_platform_market_accountid" -Type "unique" -Attributes @(
|
|
"account_platform","account_platform_market","account_platform_account_id"
|
|
)),
|
|
(New-IndexObject -Key "idx_owner_user" -Type "key" -Attributes @("account_owner_user_id")),
|
|
(New-IndexObject -Key "idx_team" -Type "key" -Attributes @("account_team"))
|
|
)
|
|
}
|
|
|
|
# products (sparscan + vollscan in einem)
|
|
$tables += @{
|
|
'$id' = $productsTableId
|
|
'$permissions' = @(
|
|
'create("any")','read("any")','update("any")','delete("any")'
|
|
)
|
|
databaseId = $DatabaseId
|
|
name = "products"
|
|
enabled = $true
|
|
rowSecurity = $false
|
|
columns = @(
|
|
New-ColumnString -Key "product_account_id" -Size 36 -Required $true
|
|
|
|
New-ColumnEnumString -Key "product_platform" -Elements @("amazon","ebay") -Size 20 -Required $true
|
|
New-ColumnString -Key "product_platform_market" -Size 50 -Required $true
|
|
New-ColumnString -Key "product_platform_product_id" -Size 128 -Required $true
|
|
|
|
New-ColumnUrl -Key "product_url" -Size 2048 -Required $true
|
|
New-ColumnString -Key "product_title" -Size 512 -Required $false
|
|
|
|
New-ColumnEnumString -Key "product_condition" -Elements @(
|
|
"new","used_like_new","used_good","used_ok","parts","unknown"
|
|
) -Size 30 -Required $false -Default "unknown"
|
|
|
|
New-ColumnEnumString -Key "product_status" -Elements @(
|
|
"active","ended","unknown"
|
|
) -Size 20 -Required $false -Default "unknown"
|
|
|
|
# volatile (sparscan)
|
|
New-ColumnFloat -Key "product_price" -Required $false
|
|
New-ColumnString -Key "product_currency" -Size 10 -Required $false
|
|
New-ColumnInteger -Key "product_quantity_available" -Required $false
|
|
New-ColumnInteger -Key "product_quantity_sold" -Required $false
|
|
New-ColumnInteger -Key "product_watch_count" -Required $false
|
|
New-ColumnInteger -Key "product_in_carts_count" -Required $false
|
|
New-ColumnDatetime -Key "product_last_seen_at" -Required $false
|
|
|
|
# scan bookkeeping
|
|
New-ColumnDatetime -Key "product_first_fullscan_at" -Required $false
|
|
New-ColumnDatetime -Key "product_last_fullscan_at" -Required $false
|
|
New-ColumnDatetime -Key "product_last_sparscan_at" -Required $false
|
|
New-ColumnInteger -Key "product_details_version" -Required $false
|
|
)
|
|
indexes = @(
|
|
(New-IndexObject -Key "idx_product_account" -Type "key" -Attributes @("product_account_id")),
|
|
(New-IndexObject -Key "uniq_account_platform_product" -Type "unique" -Attributes @(
|
|
"product_account_id","product_platform_product_id"
|
|
)),
|
|
(New-IndexObject -Key "idx_platform_market" -Type "key" -Attributes @(
|
|
"product_platform","product_platform_market"
|
|
)),
|
|
(New-IndexObject -Key "idx_status" -Type "key" -Attributes @("product_status")),
|
|
(New-IndexObject -Key "ft_title" -Type "fulltext" -Attributes @("product_title"))
|
|
)
|
|
}
|
|
|
|
# product_details (schwer)
|
|
# WICHTIG: Alle Attribute verwenden "product_detail_" Präfix (Singular) nicht "details_"
|
|
$tables += @{
|
|
'$id' = $productDetailsTableId
|
|
'$permissions' = @(
|
|
'create("any")','read("any")','update("any")','delete("any")'
|
|
)
|
|
databaseId = $DatabaseId
|
|
name = "product_details"
|
|
enabled = $true
|
|
rowSecurity = $false
|
|
columns = @(
|
|
New-ColumnString -Key "product_id" -Size 36 -Required $true
|
|
|
|
New-ColumnString -Key "product_detail_category_path" -Size 512 -Required $false
|
|
New-ColumnString -Key "product_detail_brand" -Size 128 -Required $false
|
|
New-ColumnString -Key "product_detail_mpn" -Size 128 -Required $false
|
|
New-ColumnString -Key "product_detail_gtin" -Size 128 -Required $false
|
|
|
|
New-ColumnString -Key "product_detail_description_text" -Size 20000 -Required $false
|
|
|
|
New-ColumnUrl -Key "product_detail_image_urls" -Size 2048 -Required $false -Array $true
|
|
|
|
New-ColumnString -Key "product_detail_item_specifics_json" -Size 20000 -Required $false
|
|
New-ColumnBoolean -Key "product_detail_has_variations" -Required $false
|
|
New-ColumnString -Key "product_detail_variations_json" -Size 20000 -Required $false
|
|
|
|
New-ColumnDatetime -Key "product_detail_last_updated_at" -Required $false
|
|
)
|
|
indexes = @(
|
|
(New-IndexObject -Key "uniq_product_id" -Type "unique" -Attributes @("product_id"))
|
|
)
|
|
}
|
|
|
|
# appwrite.config.json root
|
|
$config = @{
|
|
projectId = $ProjectId
|
|
endpoint = $Endpoint
|
|
tablesDB = @(
|
|
@{
|
|
'$id' = $DatabaseId
|
|
name = $DatabaseName
|
|
enabled = $true
|
|
}
|
|
)
|
|
tables = $tables
|
|
}
|
|
|
|
$configPath = Join-Path -Path (Get-Location) -ChildPath "appwrite.config.json"
|
|
($config | ConvertTo-Json -Depth 20) | Set-Content -Path $configPath -Encoding UTF8
|
|
|
|
Write-Host "Wrote: $configPath"
|
|
Write-Host "Now pushing schema via: appwrite push tables --force"
|
|
|
|
# Push schema (requires: appwrite login, appwrite init project)
|
|
& appwrite push tables --force
|