f69ad01029
* optimize db view queries * add more indices * appease gofmt --------- Co-authored-by: Flomp <Flomp@users.noreply.github.com> Co-authored-by: Christian Beutel <>
83 lines
3.9 KiB
Go
83 lines
3.9 KiB
Go
package migrations
|
|
|
|
import (
|
|
"encoding/json"
|
|
|
|
"github.com/pocketbase/pocketbase/core"
|
|
m "github.com/pocketbase/pocketbase/migrations"
|
|
)
|
|
|
|
func init() {
|
|
m.Register(func(app core.App) error {
|
|
collection, err := app.FindCollectionByNameOrId("urytyc428mwlbqq")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
// update collection data
|
|
if err := json.Unmarshal([]byte(`{
|
|
"viewQuery": "SELECT \n a.id, a.user, \n COALESCE(MAX(t.max_lat), 0) AS max_lat, \n COALESCE(MAX(t.max_lon), 0) AS max_lon, \n COALESCE(MIN(t.min_lat), 0) AS min_lat, \n COALESCE(MIN(t.min_lon), 0) AS min_lon \nFROM activitypub_actors a \nLEFT JOIN ( \n SELECT author AS actor_id, \n MAX(lat) AS max_lat, \n MAX(lon) AS max_lon, \n MIN(lat) AS min_lat, \n MIN(lon) AS min_lon \n FROM trails \n GROUP BY author \n UNION ALL \n SELECT ts.actor AS actor_id, \n MAX(t.lat) AS max_lat, \n MAX(t.lon) AS max_lon, \n MIN(t.lat) AS min_lat, \n MIN(t.lon) AS min_lon \n FROM trail_share ts \n JOIN trails t ON t.id = ts.trail \n GROUP BY ts.actor \n UNION ALL \n SELECT a2.id AS actor_id, \n p.max_lat, p.max_lon, p.min_lat, p.min_lon \n FROM activitypub_actors a2 \n CROSS JOIN ( \n SELECT \n MAX(lat) AS max_lat, \n MAX(lon) AS max_lon, \n MIN(lat) AS min_lat, \n MIN(lon) AS min_lon \n FROM trails \n WHERE public = TRUE \n ) p \n) t ON t.actor_id = a.id \nWHERE a.user != \"\"\nGROUP BY a.id;"
|
|
}`), &collection); err != nil {
|
|
return err
|
|
}
|
|
|
|
// remove field
|
|
collection.Fields.RemoveById("_clone_MXPc")
|
|
|
|
// add field
|
|
if err := collection.Fields.AddMarshaledJSONAt(1, []byte(`{
|
|
"cascadeDelete": true,
|
|
"collectionId": "_pb_users_auth_",
|
|
"help": "",
|
|
"hidden": false,
|
|
"id": "_clone_b2Wa",
|
|
"maxSelect": 1,
|
|
"minSelect": 0,
|
|
"name": "user",
|
|
"presentable": false,
|
|
"required": false,
|
|
"system": false,
|
|
"type": "relation"
|
|
}`)); err != nil {
|
|
return err
|
|
}
|
|
|
|
return app.Save(collection)
|
|
}, func(app core.App) error {
|
|
collection, err := app.FindCollectionByNameOrId("urytyc428mwlbqq")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
// update collection data
|
|
if err := json.Unmarshal([]byte(`{
|
|
"viewQuery": "SELECT \n a.id, a.user, \n COALESCE(MAX(t.max_lat), 0) AS max_lat, \n COALESCE(MAX(t.max_lon), 0) AS max_lon, \n COALESCE(MIN(t.min_lat), 0) AS min_lat, \n COALESCE(MIN(t.min_lon), 0) AS min_lon \nFROM activitypub_actors a \nLEFT JOIN ( \n SELECT author AS actor_id, \n MAX(lat) AS max_lat, \n MAX(lon) AS max_lon, \n MIN(lat) AS min_lat, \n MIN(lon) AS min_lon \n FROM trails \n GROUP BY author \n UNION ALL \n SELECT ts.actor AS actor_id, \n MAX(t.lat) AS max_lat, \n MAX(t.lon) AS max_lon, \n MIN(t.lat) AS min_lat, \n MIN(t.lon) AS min_lon \n FROM trail_share ts \n JOIN trails t ON t.id = ts.trail \n GROUP BY ts.actor \n UNION ALL \n SELECT a2.id AS actor_id, \n p.max_lat, p.max_lon, p.min_lat, p.min_lon \n FROM activitypub_actors a2 \n CROSS JOIN ( \n SELECT \n MAX(lat) AS max_lat, \n MAX(lon) AS max_lon, \n MIN(lat) AS min_lat, \n MIN(lon) AS min_lon \n FROM trails \n WHERE public = TRUE \n ) p \n) t ON t.actor_id = a.id \nGROUP BY a.id;"
|
|
}`), &collection); err != nil {
|
|
return err
|
|
}
|
|
|
|
// add field
|
|
if err := collection.Fields.AddMarshaledJSONAt(1, []byte(`{
|
|
"cascadeDelete": true,
|
|
"collectionId": "_pb_users_auth_",
|
|
"help": "",
|
|
"hidden": false,
|
|
"id": "_clone_MXPc",
|
|
"maxSelect": 1,
|
|
"minSelect": 0,
|
|
"name": "user",
|
|
"presentable": false,
|
|
"required": false,
|
|
"system": false,
|
|
"type": "relation"
|
|
}`)); err != nil {
|
|
return err
|
|
}
|
|
|
|
// remove field
|
|
collection.Fields.RemoveById("_clone_b2Wa")
|
|
|
|
return app.Save(collection)
|
|
})
|
|
}
|