diff options
| author | Kyren223 <Kyren223@proton.me> | 2025-01-09 17:28:46 +0200 |
|---|---|---|
| committer | Kyren223 <Kyren223@proton.me> | 2025-01-09 17:28:46 +0200 |
| commit | 936d3cf690ace9adfdfe0fb44193c11a0cb26d95 (patch) | |
| tree | 3d0699aa5c50efb7ecac3dddd25fe959366b7562 | |
| parent | d0cb135161b18f37c25501cb863fa783cd723758 (diff) | |
Added goose for migrations, refactored SQL schema, server and client
are broken
23 files changed, 480 insertions, 634 deletions
@@ -9,11 +9,18 @@ require ( github.com/davecgh/go-spew v1.1.1 github.com/google/btree v1.1.3 github.com/mattn/go-sqlite3 v1.14.24 - github.com/stretchr/testify v1.9.0 + github.com/pressly/goose/v3 v3.24.1 + github.com/stretchr/testify v1.10.0 github.com/vmihailenco/msgpack/v5 v5.4.1 ) require ( + github.com/mfridman/interpolate v0.0.2 // indirect + github.com/sethvargo/go-retry v0.3.0 // indirect + go.uber.org/multierr v1.11.0 // indirect +) + +require ( github.com/atotto/clipboard v0.1.4 // indirect github.com/aymanbagabas/go-osc52/v2 v2.0.1 // indirect github.com/charmbracelet/x/ansi v0.2.3 @@ -30,9 +37,9 @@ require ( github.com/pmezard/go-difflib v1.0.0 // indirect github.com/rivo/uniseg v0.4.7 // indirect github.com/vmihailenco/tagparser/v2 v2.0.0 // indirect - golang.org/x/crypto v0.28.0 + golang.org/x/crypto v0.31.0 golang.org/x/sync v0.10.0 // indirect - golang.org/x/sys v0.26.0 // indirect - golang.org/x/text v0.19.0 // indirect + golang.org/x/sys v0.28.0 // indirect + golang.org/x/text v0.21.0 // indirect gopkg.in/yaml.v3 v3.0.1 // indirect ) @@ -14,10 +14,16 @@ github.com/charmbracelet/x/term v0.2.0 h1:cNB9Ot9q8I711MyZ7myUR5HFWL/lc3OpU8jZ4h github.com/charmbracelet/x/term v0.2.0/go.mod h1:GVxgxAbjUrmpvIINHIQnJJKpMlHiZ4cktEQCN6GWyF0= github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c= github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= +github.com/dustin/go-humanize v1.0.1 h1:GzkhY7T5VNhEkwH0PVJgjz+fX1rhBrR7pRT3mDkpeCY= +github.com/dustin/go-humanize v1.0.1/go.mod h1:Mu1zIs6XwVuF/gI1OepvI0qD18qycQx+mFykh5fBlto= github.com/erikgeiser/coninput v0.0.0-20211004153227-1c3628e74d0f h1:Y/CXytFA4m6baUTXGLOoWe4PQhGxaX0KpnayAqC48p4= github.com/erikgeiser/coninput v0.0.0-20211004153227-1c3628e74d0f/go.mod h1:vw97MGsxSvLiUE2X8qFplwetxpGLQrlU1Q9AUEIzCaM= github.com/google/btree v1.1.3 h1:CVpQJjYgC4VbzxeGVHfvZrv1ctoYCAI8vbl07Fcxlyg= github.com/google/btree v1.1.3/go.mod h1:qOPhT0dTNdNzV6Z/lhRX0YXUafgPLFUh+gZMl761Gm4= +github.com/google/uuid v1.6.0 h1:NIvaJDMOsjHA8n1jAhLSgzrAzy1Hgr+hNrb57e+94F0= +github.com/google/uuid v1.6.0/go.mod h1:TIyPZe4MgqvfeYDBFedMoGGpEw/LqOeaOT+nhxU+yHo= +github.com/hashicorp/golang-lru/v2 v2.0.7 h1:a+bsQ5rvGLjzHuww6tVxozPZFVghXaHOwFs4luLUK2k= +github.com/hashicorp/golang-lru/v2 v2.0.7/go.mod h1:QeFd9opnmA6QUJc5vARoKUSoFhyfM2/ZepoAG6RGpeM= github.com/lucasb-eyer/go-colorful v1.2.0 h1:1nnpGOrhyZZuNyfu1QjKiUICQ74+3FNCN69Aj6K7nkY= github.com/lucasb-eyer/go-colorful v1.2.0/go.mod h1:R4dSotOR9KMtayYi1e77YzuveK+i7ruzyGqttikkLy0= github.com/mattn/go-isatty v0.0.20 h1:xfD0iDuEKnDkl03q4limB+vH+GxLEtL/jb4xVJSWWEY= @@ -29,6 +35,8 @@ github.com/mattn/go-runewidth v0.0.16 h1:E5ScNMtiwvlvB5paMFdw9p4kSQzbXFikJ5SQO6T github.com/mattn/go-runewidth v0.0.16/go.mod h1:Jdepj2loyihRzMpdS35Xk/zdY8IAYHsh153qUoGf23w= github.com/mattn/go-sqlite3 v1.14.24 h1:tpSp2G2KyMnnQu99ngJ47EIkWVmliIizyZBfPrBWDRM= github.com/mattn/go-sqlite3 v1.14.24/go.mod h1:Uh1q+B4BYcTPb+yiD3kU8Ct7aC0hY9fxUwlHK0RXw+Y= +github.com/mfridman/interpolate v0.0.2 h1:pnuTK7MQIxxFz1Gr+rjSIx9u7qVjf5VOoM/u6BbAxPY= +github.com/mfridman/interpolate v0.0.2/go.mod h1:p+7uk6oE07mpE/Ik1b8EckO0O4ZXiGAfshKBWLUM9Xg= github.com/muesli/ansi v0.0.0-20230316100256-276c6243b2f6 h1:ZK8zHtRHOkbHy6Mmr5D264iyp3TiX5OmNcI5cIARiQI= github.com/muesli/ansi v0.0.0-20230316100256-276c6243b2f6/go.mod h1:CJlz5H+gyd6CUWT45Oy4q24RdLyn7Md9Vj2/ldJBSIo= github.com/muesli/cancelreader v0.2.2 h1:3I4Kt4BQjOR54NavqnDogx/MIoWBFa0StPA8ELUXHmA= @@ -37,31 +45,55 @@ github.com/muesli/reflow v0.3.0 h1:IFsN6K9NfGtjeggFP+68I4chLZV2yIKsXJFNZ+eWh6s= github.com/muesli/reflow v0.3.0/go.mod h1:pbwTDkVPibjO2kyvBQRBxTWEEGDGq0FlB1BIKtnHY/8= github.com/muesli/termenv v0.15.2 h1:GohcuySI0QmI3wN8Ok9PtKGkgkFIk7y6Vpb5PvrY+Wo= github.com/muesli/termenv v0.15.2/go.mod h1:Epx+iuz8sNs7mNKhxzH4fWXGNpZwUaJKRS1noLXviQ8= +github.com/ncruces/go-strftime v0.1.9 h1:bY0MQC28UADQmHmaF5dgpLmImcShSi2kHU9XLdhx/f4= +github.com/ncruces/go-strftime v0.1.9/go.mod h1:Fwc5htZGVVkseilnfgOVb9mKy6w1naJmn9CehxcKcls= github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM= github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4= +github.com/pressly/goose/v3 v3.24.1 h1:bZmxRco2uy5uu5Ng1MMVEfYsFlrMJI+e/VMXHQ3C4LY= +github.com/pressly/goose/v3 v3.24.1/go.mod h1:rEWreU9uVtt0DHCyLzF9gRcWiiTF/V+528DV+4DORug= +github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec h1:W09IVJc94icq4NjY3clb7Lk8O1qJ8BdBEF8z0ibU0rE= +github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec/go.mod h1:qqbHyh8v60DhA7CoWK5oRCqLrMHRGoxYCSS9EjAz6Eo= github.com/rivo/uniseg v0.1.0/go.mod h1:J6wj4VEh+S6ZtnVlnTBMWIodfgj8LQOQFoIToxlJtxc= github.com/rivo/uniseg v0.2.0/go.mod h1:J6wj4VEh+S6ZtnVlnTBMWIodfgj8LQOQFoIToxlJtxc= github.com/rivo/uniseg v0.4.7 h1:WUdvkW8uEhrYfLC4ZzdpI2ztxP1I582+49Oc5Mq64VQ= github.com/rivo/uniseg v0.4.7/go.mod h1:FN3SvrM+Zdj16jyLfmOkMNblXMcoc8DfTHruCPUcx88= -github.com/stretchr/testify v1.9.0 h1:HtqpIVDClZ4nwg75+f6Lvsy/wHu+3BoSGCbBAcpTsTg= -github.com/stretchr/testify v1.9.0/go.mod h1:r2ic/lqez/lEtzL7wO/rwa5dbSLXVDPFyf8C91i36aY= +github.com/sethvargo/go-retry v0.3.0 h1:EEt31A35QhrcRZtrYFDTBg91cqZVnFL2navjDrah2SE= +github.com/sethvargo/go-retry v0.3.0/go.mod h1:mNX17F0C/HguQMyMyJxcnU471gOZGxCLyYaFyAZraas= +github.com/stretchr/testify v1.10.0 h1:Xv5erBjTwe/5IxqUQTdXv5kgmIvbHo3QQyRwhJsOfJA= +github.com/stretchr/testify v1.10.0/go.mod h1:r2ic/lqez/lEtzL7wO/rwa5dbSLXVDPFyf8C91i36aY= github.com/vmihailenco/msgpack/v5 v5.4.1 h1:cQriyiUvjTwOHg8QZaPihLWeRAAVoCpE00IUPn0Bjt8= github.com/vmihailenco/msgpack/v5 v5.4.1/go.mod h1:GaZTsDaehaPpQVyxrf5mtQlH+pc21PIudVV/E3rRQok= github.com/vmihailenco/tagparser/v2 v2.0.0 h1:y09buUbR+b5aycVFQs/g70pqKVZNBmxwAhO7/IwNM9g= github.com/vmihailenco/tagparser/v2 v2.0.0/go.mod h1:Wri+At7QHww0WTrCBeu4J6bNtoV6mEfg5OIWRZA9qds= -golang.org/x/crypto v0.28.0 h1:GBDwsMXVQi34v5CCYUm2jkJvu4cbtru2U4TN2PSyQnw= -golang.org/x/crypto v0.28.0/go.mod h1:rmgy+3RHxRZMyY0jjAJShp2zgEdOqj2AO7U0pYmeQ7U= +go.uber.org/multierr v1.11.0 h1:blXXJkSxSSfBVBlC76pxqeO+LN3aDfLQo+309xJstO0= +go.uber.org/multierr v1.11.0/go.mod h1:20+QtiLqy0Nd6FdQB9TLXag12DsQkrbs3htMFfDN80Y= +golang.org/x/crypto v0.31.0 h1:ihbySMvVjLAeSH1IbfcRTkD/iNscyz8rGzjF/E5hV6U= +golang.org/x/crypto v0.31.0/go.mod h1:kDsLvtWBEx7MV9tJOj9bnXsPbxwJQ6csT/x4KIN4Ssk= golang.org/x/sync v0.10.0 h1:3NQrjDixjgGwUOCaF8w2+VYHv0Ve/vGYSbdkTa98gmQ= golang.org/x/sync v0.10.0/go.mod h1:Czt+wKu1gCyEFDUtn0jG5QVvpJ6rzVqr5aXyt9drQfk= golang.org/x/sys v0.0.0-20210809222454-d867a43fc93e/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg= golang.org/x/sys v0.6.0/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg= -golang.org/x/sys v0.26.0 h1:KHjCJyddX0LoSTb3J+vWpupP9p0oznkqVk/IfjymZbo= -golang.org/x/sys v0.26.0/go.mod h1:/VUhepiaJMQUp4+oa/7Zr1D23ma6VTLIYjOOTFZPUcA= -golang.org/x/term v0.25.0 h1:WtHI/ltw4NvSUig5KARz9h521QvRC8RmF/cuYqifU24= -golang.org/x/term v0.25.0/go.mod h1:RPyXicDX+6vLxogjjRxjgD2TKtmAO6NZBsBRfrOLu7M= -golang.org/x/text v0.19.0 h1:kTxAhCbGbxhK0IwgSKiMO5awPoDQ0RpfiVYBfK860YM= -golang.org/x/text v0.19.0/go.mod h1:BuEKDfySbSR4drPmRPG/7iBdf8hvFMuRexcpahXilzY= +golang.org/x/sys v0.28.0 h1:Fksou7UEQUWlKvIdsqzJmUmCX3cZuD2+P3XyyzwMhlA= +golang.org/x/sys v0.28.0/go.mod h1:/VUhepiaJMQUp4+oa/7Zr1D23ma6VTLIYjOOTFZPUcA= +golang.org/x/term v0.27.0 h1:WP60Sv1nlK1T6SupCHbXzSaN0b9wUmsPoRS9b61A23Q= +golang.org/x/term v0.27.0/go.mod h1:iMsnZpn0cago0GOrHO2+Y7u7JPn5AylBrcoWkElMTSM= +golang.org/x/text v0.21.0 h1:zyQAAkrwaneQ066sspRyJaG9VNi/YJ1NfzcGB3hZ/qo= +golang.org/x/text v0.21.0/go.mod h1:4IBbMaMmOPCJ8SecivzSH54+73PCFmPWxNTLm+vZkEQ= gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405 h1:yhCVgyC4o1eVCa2tZl7eS0r+SDo693bJlVdllGtEeKM= gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0= gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA= gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= +modernc.org/gc/v3 v3.0.0-20240107210532-573471604cb6 h1:5D53IMaUuA5InSeMu9eJtlQXS2NxAhyWQvkKEgXZhHI= +modernc.org/gc/v3 v3.0.0-20240107210532-573471604cb6/go.mod h1:Qz0X07sNOR1jWYCrJMEnbW/X55x206Q7Vt4mz6/wHp4= +modernc.org/libc v1.55.3 h1:AzcW1mhlPNrRtjS5sS+eW2ISCgSOLLNyFzRh/V3Qj/U= +modernc.org/libc v1.55.3/go.mod h1:qFXepLhz+JjFThQ4kzwzOjA/y/artDeg+pcYnY+Q83w= +modernc.org/mathutil v1.6.0 h1:fRe9+AmYlaej+64JsEEhoWuAYBkOtQiMEU7n/XgfYi4= +modernc.org/mathutil v1.6.0/go.mod h1:Ui5Q9q1TR2gFm0AQRqQUaBWFLAhQpCwNcuhBOSedWPo= +modernc.org/memory v1.8.0 h1:IqGTL6eFMaDZZhEWwcREgeMXYwmW83LYW8cROZYkg+E= +modernc.org/memory v1.8.0/go.mod h1:XPZ936zp5OMKGWPqbD3JShgd/ZoQ7899TUuQqxY+peU= +modernc.org/sqlite v1.34.1 h1:u3Yi6M0N8t9yKRDwhXcyp1eS5/ErhPTBggxWFuR6Hfk= +modernc.org/sqlite v1.34.1/go.mod h1:pXV2xHxhzXZsgT/RtTFAPY6JJDEvOTcTdwADQCCWD4k= +modernc.org/strutil v1.2.0 h1:agBi9dp1I+eOnxXeiZawM8F4LawKv4NzGWSaLfyeNZA= +modernc.org/strutil v1.2.0/go.mod h1:/mdcBmfOibveCTBxUl5B5l6W+TTH1FXPLHZE6bTosX0= +modernc.org/token v1.1.0 h1:Xl7Ap9dKaEs5kLoOQeQmPWevfnk/DM5qcLcYlA8ys6Y= +modernc.org/token v1.1.0/go.mod h1:UGzOrNV1mAFSEB63lOFHIpNRUVMvYTc6yu1SMY/XTDM= diff --git a/internal/data/frequencies.sql.go b/internal/data/frequencies.sql.go index 024c069..9ee8994 100644 --- a/internal/data/frequencies.sql.go +++ b/internal/data/frequencies.sql.go @@ -115,84 +115,6 @@ func (q *Queries) GetNetworkFrequencies(ctx context.Context, networkID snowflake return items, nil } -const setFrequencyColor = `-- name: SetFrequencyColor :one -UPDATE frequencies SET - hex_color = ? -WHERE id = ? -RETURNING id, network_id, name, hex_color, perms, position -` - -type SetFrequencyColorParams struct { - HexColor string - ID snowflake.ID -} - -func (q *Queries) SetFrequencyColor(ctx context.Context, arg SetFrequencyColorParams) (Frequency, error) { - row := q.db.QueryRowContext(ctx, setFrequencyColor, arg.HexColor, arg.ID) - var i Frequency - err := row.Scan( - &i.ID, - &i.NetworkID, - &i.Name, - &i.HexColor, - &i.Perms, - &i.Position, - ) - return i, err -} - -const setFrequencyName = `-- name: SetFrequencyName :one -UPDATE frequencies SET - name = ? -WHERE id = ? -RETURNING id, network_id, name, hex_color, perms, position -` - -type SetFrequencyNameParams struct { - Name string - ID snowflake.ID -} - -func (q *Queries) SetFrequencyName(ctx context.Context, arg SetFrequencyNameParams) (Frequency, error) { - row := q.db.QueryRowContext(ctx, setFrequencyName, arg.Name, arg.ID) - var i Frequency - err := row.Scan( - &i.ID, - &i.NetworkID, - &i.Name, - &i.HexColor, - &i.Perms, - &i.Position, - ) - return i, err -} - -const setFrequencyPerms = `-- name: SetFrequencyPerms :one -UPDATE frequencies SET - perms = ? -WHERE id = ? -RETURNING id, network_id, name, hex_color, perms, position -` - -type SetFrequencyPermsParams struct { - Perms int64 - ID snowflake.ID -} - -func (q *Queries) SetFrequencyPerms(ctx context.Context, arg SetFrequencyPermsParams) (Frequency, error) { - row := q.db.QueryRowContext(ctx, setFrequencyPerms, arg.Perms, arg.ID) - var i Frequency - err := row.Scan( - &i.ID, - &i.NetworkID, - &i.Name, - &i.HexColor, - &i.Perms, - &i.Position, - ) - return i, err -} - const swapFrequencies = `-- name: SwapFrequencies :exec UPDATE frequencies SET position = CASE diff --git a/internal/data/members.sql.go b/internal/data/members.sql.go new file mode 100644 index 0000000..c9932cc --- /dev/null +++ b/internal/data/members.sql.go @@ -0,0 +1,224 @@ +// Code generated by sqlc. DO NOT EDIT. +// versions: +// sqlc v1.27.0 +// source: members.sql + +package data + +import ( + "context" + "strings" + + "github.com/kyren223/eko/pkg/snowflake" +) + +const filterUsersInNetwork = `-- name: FilterUsersInNetwork :many +SELECT user_id FROM members +WHERE network_id = ? AND user_id IN (/*SLICE:users*/?) +` + +type FilterUsersInNetworkParams struct { + NetworkID snowflake.ID + Users []snowflake.ID +} + +func (q *Queries) FilterUsersInNetwork(ctx context.Context, arg FilterUsersInNetworkParams) ([]snowflake.ID, error) { + query := filterUsersInNetwork + var queryParams []interface{} + queryParams = append(queryParams, arg.NetworkID) + if len(arg.Users) > 0 { + for _, v := range arg.Users { + queryParams = append(queryParams, v) + } + query = strings.Replace(query, "/*SLICE:users*/?", strings.Repeat(",?", len(arg.Users))[1:], 1) + } else { + query = strings.Replace(query, "/*SLICE:users*/?", "NULL", 1) + } + rows, err := q.db.QueryContext(ctx, query, queryParams...) + if err != nil { + return nil, err + } + defer rows.Close() + var items []snowflake.ID + for rows.Next() { + var user_id snowflake.ID + if err := rows.Scan(&user_id); err != nil { + return nil, err + } + items = append(items, user_id) + } + if err := rows.Close(); err != nil { + return nil, err + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const getMemberById = `-- name: GetMemberById :one +SELECT user_id, network_id, joined_at, is_member, is_admin, is_muted, is_banned, ban_reason FROM members +WHERE network_id = ? AND user_id = ? +` + +type GetMemberByIdParams struct { + NetworkID snowflake.ID + UserID snowflake.ID +} + +func (q *Queries) GetMemberById(ctx context.Context, arg GetMemberByIdParams) (Member, error) { + row := q.db.QueryRowContext(ctx, getMemberById, arg.NetworkID, arg.UserID) + var i Member + err := row.Scan( + &i.UserID, + &i.NetworkID, + &i.JoinedAt, + &i.IsMember, + &i.IsAdmin, + &i.IsMuted, + &i.IsBanned, + &i.BanReason, + ) + return i, err +} + +const getMembers = `-- name: GetMembers :many +SELECT + users.id, users.name, users.public_key, users.description, users.is_public_dm, users.is_deleted, + members.user_id, members.network_id, members.joined_at, members.is_member, members.is_admin, members.is_muted, members.is_banned, members.ban_reason +FROM members +JOIN users ON users.id = members.user_id +WHERE network_id = ? AND is_member = true +` + +type GetMembersRow struct { + User User + Member Member +} + +func (q *Queries) GetMembers(ctx context.Context, networkID snowflake.ID) ([]GetMembersRow, error) { + rows, err := q.db.QueryContext(ctx, getMembers, networkID) + if err != nil { + return nil, err + } + defer rows.Close() + var items []GetMembersRow + for rows.Next() { + var i GetMembersRow + if err := rows.Scan( + &i.User.ID, + &i.User.Name, + &i.User.PublicKey, + &i.User.Description, + &i.User.IsPublicDM, + &i.User.IsDeleted, + &i.Member.UserID, + &i.Member.NetworkID, + &i.Member.JoinedAt, + &i.Member.IsMember, + &i.Member.IsAdmin, + &i.Member.IsMuted, + &i.Member.IsBanned, + &i.Member.BanReason, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Close(); err != nil { + return nil, err + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const getNetworksOfUser = `-- name: GetNetworksOfUser :many +SELECT networks.id, networks.owner_id, networks.name, networks.icon, networks.bg_hex_color, networks.fg_hex_color, networks.is_public FROM networks +JOIN members ON networks.id = members.network_id +WHERE members.user_id = ? +` + +func (q *Queries) GetNetworksOfUser(ctx context.Context, userID snowflake.ID) ([]Network, error) { + rows, err := q.db.QueryContext(ctx, getNetworksOfUser, userID) + if err != nil { + return nil, err + } + defer rows.Close() + var items []Network + for rows.Next() { + var i Network + if err := rows.Scan( + &i.ID, + &i.OwnerID, + &i.Name, + &i.Icon, + &i.BgHexColor, + &i.FgHexColor, + &i.IsPublic, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Close(); err != nil { + return nil, err + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const setMember = `-- name: SetMember :one +INSERT INTO members ( + user_id, network_id, + is_member, is_admin, is_muted, + is_banned, ban_reason +) VALUES ( + ?1, ?2, + ?3, ?4, ?5, + ?6, ?7 +) +ON CONFLICT DO +UPDATE SET + is_member = EXCLUDED.is_member, is_admin = EXCLUDED.is_admin, is_muted = EXCLUDED.is_muted, + is_banned = EXCLUDED.is_banned, ban_reason = EXCLUDED.ban_reason, position = EXCLUDED.position +WHERE user_id = EXCLUDED.user_id AND network_id = EXCLUDED.network_id +RETURNING user_id, network_id, joined_at, is_member, is_admin, is_muted, is_banned, ban_reason +` + +type SetMemberParams struct { + UserID snowflake.ID + NetworkID snowflake.ID + IsMember bool + IsAdmin bool + IsMuted bool + IsBanned bool + BanReason *string +} + +func (q *Queries) SetMember(ctx context.Context, arg SetMemberParams) (Member, error) { + row := q.db.QueryRowContext(ctx, setMember, + arg.UserID, + arg.NetworkID, + arg.IsMember, + arg.IsAdmin, + arg.IsMuted, + arg.IsBanned, + arg.BanReason, + ) + var i Member + err := row.Scan( + &i.UserID, + &i.NetworkID, + &i.JoinedAt, + &i.IsMember, + &i.IsAdmin, + &i.IsMuted, + &i.IsBanned, + &i.BanReason, + ) + return i, err +} diff --git a/internal/data/models.go b/internal/data/models.go index 3b5425f..bb0b271 100644 --- a/internal/data/models.go +++ b/internal/data/models.go @@ -9,6 +9,11 @@ import ( "github.com/kyren223/eko/pkg/snowflake" ) +type BlockedUser struct { + BlockingUserID snowflake.ID + BlockedUserID snowflake.ID +} + type Frequency struct { ID snowflake.ID NetworkID snowflake.ID @@ -18,6 +23,17 @@ type Frequency struct { Position int64 } +type Member struct { + UserID snowflake.ID + NetworkID snowflake.ID + JoinedAt string + IsMember bool + IsAdmin bool + IsMuted bool + IsBanned bool + BanReason *string +} + type Message struct { ID snowflake.ID SenderID snowflake.ID @@ -37,6 +53,12 @@ type Network struct { IsPublic bool } +type TrustedUser struct { + TrustingUserID snowflake.ID + TrustedUserID snowflake.ID + TrustedPublicKey []byte +} + type User struct { ID snowflake.ID Name string @@ -45,26 +67,3 @@ type User struct { IsPublicDM bool IsDeleted bool } - -type UserBlockedUser struct { - BlockerUserID snowflake.ID - BlockedUserID snowflake.ID -} - -type UserNetwork struct { - UserID snowflake.ID - NetworkID snowflake.ID - JoinedAt string - IsMember bool - IsAdmin bool - IsMuted bool - IsBanned bool - BanReason *string - Position *int64 -} - -type UserTrustedUser struct { - TrusterUserID snowflake.ID - TrustedUserID snowflake.ID - TrustedPublicKey []byte -} diff --git a/internal/data/networks.sql.go b/internal/data/networks.sql.go index 9adeeb8..5091602 100644 --- a/internal/data/networks.sql.go +++ b/internal/data/networks.sql.go @@ -84,132 +84,6 @@ func (q *Queries) GetNetworkById(ctx context.Context, id snowflake.ID) (Network, return i, err } -const getPublicNetworks = `-- name: GetPublicNetworks :many -SELECT id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public FROM networks -WHERE is_public = true -` - -func (q *Queries) GetPublicNetworks(ctx context.Context) ([]Network, error) { - rows, err := q.db.QueryContext(ctx, getPublicNetworks) - if err != nil { - return nil, err - } - defer rows.Close() - var items []Network - for rows.Next() { - var i Network - if err := rows.Scan( - &i.ID, - &i.OwnerID, - &i.Name, - &i.Icon, - &i.BgHexColor, - &i.FgHexColor, - &i.IsPublic, - ); err != nil { - return nil, err - } - items = append(items, i) - } - if err := rows.Close(); err != nil { - return nil, err - } - if err := rows.Err(); err != nil { - return nil, err - } - return items, nil -} - -const setNetworkIcon = `-- name: SetNetworkIcon :one -UPDATE networks SET - icon = ?, - bg_hex_color = ?, - fg_hex_color = ? -WHERE id = ? -RETURNING id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public -` - -type SetNetworkIconParams struct { - Icon string - BgHexColor string - FgHexColor string - ID snowflake.ID -} - -func (q *Queries) SetNetworkIcon(ctx context.Context, arg SetNetworkIconParams) (Network, error) { - row := q.db.QueryRowContext(ctx, setNetworkIcon, - arg.Icon, - arg.BgHexColor, - arg.FgHexColor, - arg.ID, - ) - var i Network - err := row.Scan( - &i.ID, - &i.OwnerID, - &i.Name, - &i.Icon, - &i.BgHexColor, - &i.FgHexColor, - &i.IsPublic, - ) - return i, err -} - -const setNetworkIsPublic = `-- name: SetNetworkIsPublic :one -UPDATE networks SET - is_public = ? -WHERE id = ? -RETURNING id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public -` - -type SetNetworkIsPublicParams struct { - IsPublic bool - ID snowflake.ID -} - -func (q *Queries) SetNetworkIsPublic(ctx context.Context, arg SetNetworkIsPublicParams) (Network, error) { - row := q.db.QueryRowContext(ctx, setNetworkIsPublic, arg.IsPublic, arg.ID) - var i Network - err := row.Scan( - &i.ID, - &i.OwnerID, - &i.Name, - &i.Icon, - &i.BgHexColor, - &i.FgHexColor, - &i.IsPublic, - ) - return i, err -} - -const setNetworkName = `-- name: SetNetworkName :one -UPDATE networks SET - name = ? -WHERE id = ? -RETURNING id, owner_id, name, icon, bg_hex_color, fg_hex_color, is_public -` - -type SetNetworkNameParams struct { - Name string - ID snowflake.ID -} - -func (q *Queries) SetNetworkName(ctx context.Context, arg SetNetworkNameParams) (Network, error) { - row := q.db.QueryRowContext(ctx, setNetworkName, arg.Name, arg.ID) - var i Network - err := row.Scan( - &i.ID, - &i.OwnerID, - &i.Name, - &i.Icon, - &i.BgHexColor, - &i.FgHexColor, - &i.IsPublic, - ) - return i, err -} - const transferNetwork = `-- name: TransferNetwork :one UPDATE networks SET owner_id = ? diff --git a/internal/data/users.sql.go b/internal/data/users.sql.go index 33eab3f..f7a7f9a 100644 --- a/internal/data/users.sql.go +++ b/internal/data/users.sql.go @@ -52,25 +52,6 @@ func (q *Queries) DeleteUser(ctx context.Context, id snowflake.ID) error { return err } -const getDeletedUserById = `-- name: GetDeletedUserById :one -SELECT id, name, public_key, description, is_public_dm, is_deleted FROM users -WHERE id = ? AND is_deleted = true -` - -func (q *Queries) GetDeletedUserById(ctx context.Context, id snowflake.ID) (User, error) { - row := q.db.QueryRowContext(ctx, getDeletedUserById, id) - var i User - err := row.Scan( - &i.ID, - &i.Name, - &i.PublicKey, - &i.Description, - &i.IsPublicDM, - &i.IsDeleted, - ) - return i, err -} - const getUserById = `-- name: GetUserById :one SELECT id, name, public_key, description, is_public_dm, is_deleted FROM users WHERE id = ? AND is_deleted = false @@ -108,107 +89,3 @@ func (q *Queries) GetUserByPublicKey(ctx context.Context, publicKey ed25519.Publ ) return i, err } - -const setUserDescription = `-- name: SetUserDescription :one -UPDATE users SET - description = ? -WHERE id = ? AND is_deleted = false -RETURNING id, name, public_key, description, is_public_dm, is_deleted -` - -type SetUserDescriptionParams struct { - Description string - ID snowflake.ID -} - -func (q *Queries) SetUserDescription(ctx context.Context, arg SetUserDescriptionParams) (User, error) { - row := q.db.QueryRowContext(ctx, setUserDescription, arg.Description, arg.ID) - var i User - err := row.Scan( - &i.ID, - &i.Name, - &i.PublicKey, - &i.Description, - &i.IsPublicDM, - &i.IsDeleted, - ) - return i, err -} - -const setUserName = `-- name: SetUserName :one -UPDATE users SET - name = ? -WHERE id = ? AND is_deleted = false -RETURNING id, name, public_key, description, is_public_dm, is_deleted -` - -type SetUserNameParams struct { - Name string - ID snowflake.ID -} - -func (q *Queries) SetUserName(ctx context.Context, arg SetUserNameParams) (User, error) { - row := q.db.QueryRowContext(ctx, setUserName, arg.Name, arg.ID) - var i User - err := row.Scan( - &i.ID, - &i.Name, - &i.PublicKey, - &i.Description, - &i.IsPublicDM, - &i.IsDeleted, - ) - return i, err -} - -const setUserPublicDMs = `-- name: SetUserPublicDMs :one -UPDATE users SET - is_public_dm = ? -WHERE id = ? AND is_deleted = false -RETURNING id, name, public_key, description, is_public_dm, is_deleted -` - -type SetUserPublicDMsParams struct { - IsPublicDM bool - ID snowflake.ID -} - -func (q *Queries) SetUserPublicDMs(ctx context.Context, arg SetUserPublicDMsParams) (User, error) { - row := q.db.QueryRowContext(ctx, setUserPublicDMs, arg.IsPublicDM, arg.ID) - var i User - err := row.Scan( - &i.ID, - &i.Name, - &i.PublicKey, - &i.Description, - &i.IsPublicDM, - &i.IsDeleted, - ) - return i, err -} - -const setUserPublicKey = `-- name: SetUserPublicKey :one -UPDATE users SET - public_key = ? -WHERE id = ? AND is_deleted = false -RETURNING id, name, public_key, description, is_public_dm, is_deleted -` - -type SetUserPublicKeyParams struct { - PublicKey ed25519.PublicKey - ID snowflake.ID -} - -func (q *Queries) SetUserPublicKey(ctx context.Context, arg SetUserPublicKeyParams) (User, error) { - row := q.db.QueryRowContext(ctx, setUserPublicKey, arg.PublicKey, arg.ID) - var i User - err := row.Scan( - &i.ID, - &i.Name, - &i.PublicKey, - &i.Description, - &i.IsPublicDM, - &i.IsDeleted, - ) - return i, err -} diff --git a/internal/server/api/database.go b/internal/server/api/database.go index 0617515..00e8d4d 100644 --- a/internal/server/api/database.go +++ b/internal/server/api/database.go @@ -2,19 +2,26 @@ package api import ( "database/sql" + "embed" "log" _ "github.com/mattn/go-sqlite3" + "github.com/pressly/goose/v3" "github.com/kyren223/eko/pkg/assert" ) +//go:embed migrations/*.sql +var embedMigrations embed.FS + var db *sql.DB func ConnectToDatabase() { var err error db, err = sql.Open("sqlite3", "file:server.db?cache=shared") - assert.NoError(err, "DB should always be accessible") + if err != nil { + log.Fatalln("unable to open db:", err) + } assert.AddFlush(db) log.Println("established connection with the database") @@ -29,6 +36,18 @@ func ConnectToDatabase() { assert.NoError(err, "DB pragmas should always execute with no errors") } + log.Println("opened database, running up migrations...") + + goose.SetBaseFS(embedMigrations) + if err := goose.SetDialect("sqlite3"); err != nil { + assert.NoError(err, "sqlite3 is a valid dialect that shouldn't error") + } + if err := goose.Up(db, "migrations"); err != nil { + db.Close() + log.Fatalln("error running up migrations:", err) + } + log.Println("database up migrations applied successfully") + log.Println("database connection ready to be used") } diff --git a/internal/server/api/migrations/20250109134844_users.sql b/internal/server/api/migrations/20250109134844_users.sql new file mode 100644 index 0000000..8ebb9eb --- /dev/null +++ b/internal/server/api/migrations/20250109134844_users.sql @@ -0,0 +1,12 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS users ( + id INTEGER PRIMARY KEY, + name TEXT NOT NULL, + public_key BLOB NOT NULL UNIQUE, + description TEXT NOT NULL DEFAULT '', + is_public_dm BOOLEAN NOT NULL CHECK (is_public_dm IN (false, true)) DEFAULT true, + is_deleted BOOLEAN NOT NULL CHECK (is_deleted IN (false, true)) DEFAULT false +); + +-- +goose Down +DROP TABLE IF EXISTS users; diff --git a/internal/server/api/migrations/20250109135310_networks.sql b/internal/server/api/migrations/20250109135310_networks.sql new file mode 100644 index 0000000..c3332c8 --- /dev/null +++ b/internal/server/api/migrations/20250109135310_networks.sql @@ -0,0 +1,13 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS networks ( + id INTEGER PRIMARY KEY, + owner_id INT NOT NULL REFERENCES users (id), + name TEXT NOT NULL, + icon TEXT NOT NULL, + bg_hex_color TEXT NOT NULL, + fg_hex_color TEXT NOT NULL, + is_public BOOLEAN NOT NULL CHECK (is_public IN (false, true)) +); + +-- +goose Down +DROP TABLE IF EXISTS networks; diff --git a/internal/server/api/migrations/20250109135557_frequencies.sql b/internal/server/api/migrations/20250109135557_frequencies.sql new file mode 100644 index 0000000..a444af1 --- /dev/null +++ b/internal/server/api/migrations/20250109135557_frequencies.sql @@ -0,0 +1,24 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS frequencies ( + id INTEGER PRIMARY KEY, + network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE, + name TEXT NOT NULL, + hex_color TEXT NOT NULL, + perms INT NOT NULL CHECK (perms IN (0, 1, 2)), + -- 0 no access | 1 read | 2 read & write + position INT NOT NULL +); + +-- +goose StatementBegin +CREATE TRIGGER IF NOT EXISTS on_frequency_delete +AFTER DELETE ON frequencies +BEGIN + UPDATE frequencies SET + position = position - 1 + WHERE network_id = OLD.network_id AND position > OLD.position; +END +-- +goose StatementEnd + +-- +goose Down +DROP TABLE IF EXISTS frequencies; +DROP TRIGGER IF EXISTS on_frequency_delete; diff --git a/internal/server/api/migrations/20250109140401_messages.sql b/internal/server/api/migrations/20250109140401_messages.sql new file mode 100644 index 0000000..92f75f4 --- /dev/null +++ b/internal/server/api/migrations/20250109140401_messages.sql @@ -0,0 +1,18 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS messages ( + id INTEGER PRIMARY KEY, + sender_id INT NOT NULL REFERENCES users (id), + content TEXT NOT NULL, + edited BOOLEAN NOT NULL CHECK (edited IN (false, true)) DEFAULT false, + + frequency_id INT REFERENCES frequencies (id) ON DELETE CASCADE, + receiver_id INT REFERENCES users (id), + + CHECK ( + (frequency_id IS NOT NULL AND receiver_id IS NULL) OR + (frequency_id IS NULL AND receiver_id IS NOT NULL) + ) +); + +-- +goose Down +DROP TABLE IF EXISTS messages; diff --git a/internal/server/api/migrations/20250109141122_members.sql b/internal/server/api/migrations/20250109141122_members.sql new file mode 100644 index 0000000..61fba61 --- /dev/null +++ b/internal/server/api/migrations/20250109141122_members.sql @@ -0,0 +1,15 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS members ( + user_id INT NOT NULL REFERENCES users (id), + network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE, + joined_at TEXT NOT NULL DEFAULT current_timestamp, + is_member BOOLEAN NOT NULL CHECK (is_member IN (false, true)) DEFAULT true, + is_admin BOOLEAN NOT NULL CHECK (is_admin IN (false, true)) DEFAULT false, + is_muted BOOLEAN NOT NULL CHECK (is_muted IN (false, true)) DEFAULT false, + is_banned BOOLEAN NOT NULL CHECK (is_banned IN (false, true)) DEFAULT false, + ban_reason TEXT, -- null ONLY if is_banned is false + PRIMARY KEY (user_id, network_id) +); + +-- +goose Down +DROP TABLE IF EXISTS members; diff --git a/internal/server/api/migrations/20250109142125_trusted_and_blocked_users.sql b/internal/server/api/migrations/20250109142125_trusted_and_blocked_users.sql new file mode 100644 index 0000000..8916378 --- /dev/null +++ b/internal/server/api/migrations/20250109142125_trusted_and_blocked_users.sql @@ -0,0 +1,17 @@ +-- +goose Up +CREATE TABLE IF NOT EXISTS trusted_users ( + trusting_user_id INT NOT NULL REFERENCES users (id), + trusted_user_id INT NOT NULL REFERENCES users (id), + trusted_public_key BLOB NOT NULL, + PRIMARY KEY (trusting_user_id, trusted_user_id) +); + +CREATE TABLE IF NOT EXISTS blocked_users ( + blocking_user_id INT NOT NULL REFERENCES users (id), + blocked_user_id INT NOT NULL REFERENCES users (id), + PRIMARY KEY (blocking_user_id, blocked_user_id) +); + +-- +goose Down +DROP TABLE IF EXISTS trusted_users; +DROP TABLE IF EXISTS blocked_users; diff --git a/internal/server/api/migrations/20250109143707_on_user_delete.sql b/internal/server/api/migrations/20250109143707_on_user_delete.sql new file mode 100644 index 0000000..940bec4 --- /dev/null +++ b/internal/server/api/migrations/20250109143707_on_user_delete.sql @@ -0,0 +1,15 @@ +-- +goose Up +-- +goose StatementBegin +CREATE TRIGGER on_user_delete +AFTER UPDATE OF is_deleted ON users +WHEN NEW.is_deleted = true +BEGIN + DELETE FROM networks WHERE owner_id = NEW.id; + DELETE FROM members WHERE user_id = NEW.id; + DELETE FROM trusted_users WHERE trusting_user_id = NEW.id OR trusted_user_id = NEW.id; + DELETE FROM blocked_users WHERE blocking_user_id = NEW.id OR blocked_user_id = NEW.id; +END +-- +goose StatementEnd + +-- +goose Down +DROP TRIGGER IF EXISTS on_user_delete; diff --git a/internal/server/api/migrations/20250109144057_indices.sql b/internal/server/api/migrations/20250109144057_indices.sql new file mode 100644 index 0000000..1732fb2 --- /dev/null +++ b/internal/server/api/migrations/20250109144057_indices.sql @@ -0,0 +1,9 @@ +-- +goose Up +CREATE INDEX IF NOT EXISTS idx_frequency_network ON frequencies (network_id); +CREATE INDEX IF NOT EXISTS idx_frequency_messages ON messages (frequency_id); +CREATE INDEX IF NOT EXISTS idx_direct_messages ON messages (sender_id, receiver_id); + +-- +goose Down +DROP INDEX IF EXISTS idx_frequency_network; +DROP INDEX IF EXISTS idx_frequency_messages; +DROP INDEX IF EXISTS idx_direct_messages; diff --git a/migrations/20241026174345_init.sql b/migrations/20241026174345_init.sql deleted file mode 100644 index e22ecf0..0000000 --- a/migrations/20241026174345_init.sql +++ /dev/null @@ -1,133 +0,0 @@ --- +goose Up -CREATE TABLE messages ( - id INTEGER PRIMARY KEY, - sender_id INT NOT NULL REFERENCES users (id), - content TEXT NOT NULL, - edited BOOLEAN NOT NULL CHECK (edited IN (false, true)) DEFAULT false, - - frequency_id INT REFERENCES frequencies (id) ON DELETE CASCADE, - receiver_id INT REFERENCES users (id), - - CHECK ( - (frequency_id IS NOT NULL AND receiver_id IS NULL) OR - (frequency_id IS NULL AND receiver_id IS NOT NULL) - ) -); - -CREATE TABLE users ( - id INTEGER PRIMARY KEY, - name TEXT NOT NULL, - public_key BLOB NOT NULL UNIQUE, - description TEXT NOT NULL DEFAULT '', - is_public_dm BOOLEAN NOT NULL CHECK (is_public_dm IN (false, true)) DEFAULT true, - is_deleted BOOLEAN NOT NULL CHECK (is_deleted IN (false, true)) DEFAULT false -); - -CREATE TABLE networks ( - id INTEGER PRIMARY KEY, - owner_id INT NOT NULL REFERENCES users (id), - name TEXT NOT NULL, - icon TEXT NOT NULL, - bg_hex_color TEXT NOT NULL, - fg_hex_color TEXT NOT NULL, - is_public BOOLEAN NOT NULL CHECK (is_public IN (false, true)) -); - -CREATE TABLE frequencies ( - id INTEGER PRIMARY KEY, - network_id INT NOT NULL REFERENCES networks (id) ON DELETE CASCADE, - name TEXT NOT NULL, - hex_color TEXT NOT NULL, - perms INT NOT NULL CHECK (perms IN (0, 1, 2)), - -- 0 no access | 1 read | 2 read & write - position INT NOT NULL -); - -CREATE TABLE users_networks ( - user_id INT NOT NULL REFERENCES users (id), - network_id INT NOT NULL REFERENCES networks (id), - joined_at TEXT NOT NULL DEFAULT current_timestamp, - is_member BOOLEAN NOT NULL CHECK (is_member IN (false, true)) DEFAULT true, - is_admin BOOLEAN NOT NULL CHECK (is_admin IN (false, true)) DEFAULT false, - is_muted BOOLEAN NOT NULL CHECK (is_muted IN (false, true)) DEFAULT false, - is_banned BOOLEAN NOT NULL CHECK (is_banned IN (false, true)) DEFAULT false, - ban_reason TEXT, - position INT, -- null only if is_member = false - PRIMARY KEY (user_id, network_id) -); - -CREATE TABLE user_trusted_users ( - truster_user_id INT NOT NULL REFERENCES users (id), - trusted_user_id INT NOT NULL REFERENCES users (id), - trusted_public_key BLOB NOT NULL, - PRIMARY KEY (truster_user_id, trusted_user_id) -); - -CREATE TABLE user_blocked_users ( - blocker_user_id INT NOT NULL REFERENCES users (id), - blocked_user_id INT NOT NULL REFERENCES users (id), - PRIMARY KEY (blocker_user_id, blocked_user_id) -); - -CREATE INDEX idx_network_name ON networks (name) WHERE is_public = true; -CREATE INDEX idx_frequency_network ON frequencies (network_id); -CREATE INDEX idx_blocked_by_user ON user_blocked_users (blocker_user_id); -CREATE INDEX idx_banned_by_network ON users_networks (network_id) WHERE is_banned = true; -CREATE INDEX idx_direct_messages ON messages (sender_id, receiver_id); -CREATE INDEX idx_frequency_messages ON messages (frequency_id); - --- +goose StatementBegin -CREATE TRIGGER on_user_delete -AFTER UPDATE OF is_deleted ON users -WHEN NEW.is_deleted = true -BEGIN - DELETE FROM networks WHERE owner_id = NEW.id; - DELETE FROM users_networks WHERE user_id = NEW.id; - DELETE FROM user_blocked_users WHERE blocker_user_id = NEW.id OR blocked_user_id = NEW.id; - DELETE FROM user_trusted_users WHERE truster_user_id = NEW.id OR trusted_user_id = NEW.id; - DELETE FROM network_banned_users WHERE banned_user_id = NEW.id; -END --- +goose StatementEnd - --- +goose StatementBegin -CREATE TRIGGER on_frequency_delete -AFTER DELETE ON frequencies -BEGIN - UPDATE frequencies SET - position = position - 1 - WHERE network_id = OLD.network_id AND position > OLD.position; -END --- +goose StatementEnd - --- +goose StatementBegin -CREATE TRIGGER on_network_delete -AFTER DELETE ON networks -BEGIN - UPDATE users_networks SET - position = position - 1 - WHERE user_id IN ( - SELECT user_id FROM users_networks WHERE network_id = OLD.id - ) AND position > ( - SELECT position FROM users_networks WHERE user_id = users_networks.user_id AND network_id = OLD.id - ); - - DELETE FROM users_networks WHERE network_id = OLD.id; -END --- +goose StatementEnd - --- +goose Down -DROP TABLE messages; -DROP TABLE users; -DROP TABLE networks; -DROP TABLE frequencies; -DROP TABLE users_networks; -DROP TABLE user_trusted_users; -DROP TABLE user_blocked_users; -DROP TABLE network_banned_users; - -DROP INDEX idx_network_name; -DROP INDEX idx_frequency_network; -DROP INDEX idx_blocked_by_user; -DROP INDEX idx_banned_by_network; -DROP INDEX idx_direct_messages; -DROP INDEX idx_frequency_messages; diff --git a/query/frequencies.sql b/query/frequencies.sql index a43af54..fba8f51 100644 --- a/query/frequencies.sql +++ b/query/frequencies.sql @@ -26,23 +26,5 @@ UPDATE frequencies SET END WHERE network_id = @network_id AND position IN (@pos1, @pos2); --- name: SetFrequencyName :one -UPDATE frequencies SET - name = ? -WHERE id = ? -RETURNING *; - --- name: SetFrequencyColor :one -UPDATE frequencies SET - hex_color = ? -WHERE id = ? -RETURNING *; - --- name: SetFrequencyPerms :one -UPDATE frequencies SET - perms = ? -WHERE id = ? -RETURNING *; - -- name: DeleteFrequency :exec DELETE FROM frequencies WHERE id = ?; diff --git a/query/members.sql b/query/members.sql new file mode 100644 index 0000000..04e04cd --- /dev/null +++ b/query/members.sql @@ -0,0 +1,37 @@ +-- name: GetMembers :many +SELECT + sqlc.embed(users), + sqlc.embed(members) +FROM members +JOIN users ON users.id = members.user_id +WHERE network_id = ? AND is_member = true; + +-- name: GetMemberById :one +SELECT * FROM members +WHERE network_id = ? AND user_id = ?; + +-- name: GetNetworksOfUser :many +SELECT networks.* FROM networks +JOIN members ON networks.id = members.network_id +WHERE members.user_id = ?; + +-- name: SetMember :one +INSERT INTO members ( + user_id, network_id, + is_member, is_admin, is_muted, + is_banned, ban_reason +) VALUES ( + @user_id, @network_id, + @is_member, @is_admin, @is_muted, + @is_banned, @ban_reason +) +ON CONFLICT DO +UPDATE SET + is_member = EXCLUDED.is_member, is_admin = EXCLUDED.is_admin, is_muted = EXCLUDED.is_muted, + is_banned = EXCLUDED.is_banned, ban_reason = EXCLUDED.ban_reason, position = EXCLUDED.position +WHERE user_id = EXCLUDED.user_id AND network_id = EXCLUDED.network_id +RETURNING *; + +-- name: FilterUsersInNetwork :many +SELECT user_id FROM members +WHERE network_id = ? AND user_id IN (sqlc.slice('users')); diff --git a/query/networks.sql b/query/networks.sql index 64f56dc..b1d01c7 100644 --- a/query/networks.sql +++ b/query/networks.sql @@ -1,7 +1,3 @@ --- name: GetPublicNetworks :many -SELECT * FROM networks -WHERE is_public = true; - -- name: GetNetworkById :one SELECT * FROM networks WHERE id = ?; @@ -16,26 +12,6 @@ INSERT INTO networks ( ) RETURNING *; --- name: SetNetworkName :one -UPDATE networks SET - name = ? -WHERE id = ? -RETURNING *; - --- name: SetNetworkIcon :one -UPDATE networks SET - icon = ?, - bg_hex_color = ?, - fg_hex_color = ? -WHERE id = ? -RETURNING *; - --- name: SetNetworkIsPublic :one -UPDATE networks SET - is_public = ? -WHERE id = ? -RETURNING *; - -- name: TransferNetwork :one UPDATE networks SET owner_id = ? diff --git a/query/users.sql b/query/users.sql index d136f15..8247dda 100644 --- a/query/users.sql +++ b/query/users.sql @@ -6,10 +6,6 @@ WHERE id = ? AND is_deleted = false; SELECT * FROM users WHERE public_key = ? AND is_deleted = false; --- name: GetDeletedUserById :one -SELECT * FROM users -WHERE id = ? AND is_deleted = true; - -- name: CreateUser :one INSERT INTO users ( id, name, public_key @@ -18,30 +14,6 @@ INSERT INTO users ( ) RETURNING *; --- name: SetUserName :one -UPDATE users SET - name = ? -WHERE id = ? AND is_deleted = false -RETURNING *; - --- name: SetUserPublicKey :one -UPDATE users SET - public_key = ? -WHERE id = ? AND is_deleted = false -RETURNING *; - --- name: SetUserDescription :one -UPDATE users SET - description = ? -WHERE id = ? AND is_deleted = false -RETURNING *; - --- name: SetUserPublicDMs :one -UPDATE users SET - is_public_dm = ? -WHERE id = ? AND is_deleted = false -RETURNING *; - -- name: DeleteUser :exec UPDATE users SET is_deleted = true diff --git a/query/users_networks.sql b/query/users_networks.sql deleted file mode 100644 index bde3c3b..0000000 --- a/query/users_networks.sql +++ /dev/null @@ -1,65 +0,0 @@ --- name: GetNetworkBannedUsers :many -SELECT - sqlc.embed(users), - users_networks.ban_reason -FROM users_networks -JOIN users ON users.id = users_networks.user_id -WHERE users_networks.network_id = ?; - --- name: GetNetworkMembers :many -SELECT - sqlc.embed(users), - users_networks.joined_at, - users_networks.is_admin, - users_networks.is_muted -FROM users_networks -JOIN users ON users.id = users_networks.user_id -WHERE users_networks.network_id = ? AND is_member = true; - --- name: GetNetworkMemberById :one -SELECT * -FROM users_networks -WHERE users_networks.network_id = ? AND users_networks.user_id = ?; - --- name: GetUserNetworks :many -SELECT sqlc.embed(networks), users_networks.position FROM networks -JOIN users_networks ON networks.id = users_networks.network_id -WHERE users_networks.user_id = ? -ORDER BY users_networks.position; - --- name: GetUserNetwork :one -SELECT * FROM users_networks -WHERE user_id = ? AND network_id = ?; - --- name: SetMember :one -INSERT INTO users_networks ( - user_id, network_id, - is_member, is_admin, is_muted, - is_banned, ban_reason, position -) VALUES ( - @user_id, @network_id, - @is_member, @is_admin, @is_muted, - @is_banned, @ban_reason, - CASE - WHEN @is_member = false THEN NULL - ELSE (SELECT COUNT(*) FROM users_networks WHERE user_id = @user_id) - END -) -ON CONFLICT DO -UPDATE SET - is_member = EXCLUDED.is_member, is_admin = EXCLUDED.is_admin, is_muted = EXCLUDED.is_muted, - is_banned = EXCLUDED.is_banned, ban_reason = EXCLUDED.ban_reason, position = EXCLUDED.position -WHERE user_id = EXCLUDED.user_id AND network_id = EXCLUDED.network_id -RETURNING *; - --- name: SwapUserNetworks :exec -UPDATE users_networks SET - position = CASE - WHEN position = @pos1 THEN @pos2 - WHEN position = @pos2 THEN @pos1 - END -WHERE user_id = @user_id AND position IN (@pos1, @pos2); - --- name: FilterUsersInNetwork :many -SELECT user_id FROM users_networks -WHERE network_id = ? AND user_id IN (sqlc.slice('users')); @@ -2,7 +2,7 @@ version: "2" sql: - engine: "sqlite" queries: "./query/" - schema: "./migrations" + schema: "./internal/server/api/migrations" gen: go: package: "data" |