Skip to content

Commit 57b099f

Browse files
alnrory-bot
alnr
authored and
ory-bot
committed
feat: add column identity_id to identity_credential_identifiers and session_devices
GitOrigin-RevId: a8fc43b6bba9119a2d9472343eede30978ee72d7
1 parent 7790322 commit 57b099f

12 files changed

Lines changed: 135 additions & 19 deletions

‎identity/credentials.go‎

Lines changed: 8 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -215,17 +215,14 @@ func (c Credentials) Signature() string {
215215
type (
216216
// swagger:ignore
217217
CredentialIdentifier struct {
218-
ID uuid.UUID `db:"id"`
219-
Identifier string `db:"identifier"`
220-
// IdentityCredentialsID is a helper struct field for gobuffalo.pop.
221-
IdentityCredentialsID uuid.UUID `json:"-" db:"identity_credential_id"`
222-
// IdentityCredentialsTypeID is a helper struct field for gobuffalo.pop.
223-
IdentityCredentialsTypeID uuid.UUID `json:"-" db:"identity_credential_type_id"`
224-
// CreatedAt is a helper struct field for gobuffalo.pop.
225-
CreatedAt time.Time `json:"created_at" db:"created_at"`
226-
// UpdatedAt is a helper struct field for gobuffalo.pop.
227-
UpdatedAt time.Time `json:"updated_at" db:"updated_at"`
228-
NID uuid.UUID `json:"-" faker:"-" db:"nid"`
218+
ID uuid.UUID `db:"id"`
219+
Identifier string `db:"identifier"`
220+
IdentityID *uuid.UUID `json:"-" db:"identity_id"`
221+
IdentityCredentialsID uuid.UUID `json:"-" db:"identity_credential_id"`
222+
IdentityCredentialsTypeID uuid.UUID `json:"-" db:"identity_credential_type_id"`
223+
CreatedAt time.Time `json:"created_at" db:"created_at"`
224+
UpdatedAt time.Time `json:"updated_at" db:"updated_at"`
225+
NID uuid.UUID `json:"-" faker:"-" db:"nid"`
229226
}
230227

231228
// swagger:ignore

‎identity/test/pool.go‎

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1420,7 +1420,6 @@ func TestPool(ctx context.Context, p persistence.Persister, m *identity.Manager,
14201420
})
14211421
})
14221422
}
1423-
14241423
})
14251424

14261425
t.Run("case=create and update and find", func(t *testing.T) {
@@ -1547,8 +1546,8 @@ func TestPool(ctx context.Context, p persistence.Persister, m *identity.Manager,
15471546
require.NoError(t, p.GetConnection(ctx).RawQuery("INSERT INTO identity_credentials (id, identity_id, nid, identity_credential_type_id, created_at, updated_at, config) VALUES (?, ?, ?, ?, ?, ?, '{}')", cid2, iid, nid2, m[0].ID, time.Now(), time.Now()).Exec())
15481547

15491548
ici1, ici2 := x.NewUUID(), x.NewUUID()
1550-
require.NoError(t, p.GetConnection(ctx).RawQuery("INSERT INTO identity_credential_identifiers (id, identity_credential_id, nid, identifier, created_at, updated_at, identity_credential_type_id) VALUES (?, ?, ?, ?, ?, ?, ?)", ici1, cid1, nid1, "nid1", time.Now(), time.Now(), m[0].ID).Exec())
1551-
require.NoError(t, p.GetConnection(ctx).RawQuery("INSERT INTO identity_credential_identifiers (id, identity_credential_id, nid, identifier, created_at, updated_at, identity_credential_type_id) VALUES (?, ?, ?, ?, ?, ?, ?)", ici2, cid2, nid2, "nid2", time.Now(), time.Now(), m[0].ID).Exec())
1549+
require.NoError(t, p.GetConnection(ctx).RawQuery("INSERT INTO identity_credential_identifiers (id, identity_id, identity_credential_id, nid, identifier, created_at, updated_at, identity_credential_type_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", ici1, iid, cid1, nid1, "nid1", time.Now(), time.Now(), m[0].ID).Exec())
1550+
require.NoError(t, p.GetConnection(ctx).RawQuery("INSERT INTO identity_credential_identifiers (id, identity_id, identity_credential_id, nid, identifier, created_at, updated_at, identity_credential_type_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", ici2, iid, cid2, nid2, "nid2", time.Now(), time.Now(), m[0].ID).Exec())
15521551

15531552
_, err := p.GetIdentity(ctx, nid1, identity.ExpandNothing)
15541553
require.ErrorIs(t, err, sqlcon.ErrNoRows)

‎persistence/sql/identity/persister_identity.go‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -347,6 +347,7 @@ func (p *IdentityPersister) createIdentityCredentials(ctx context.Context, conn
347347

348348
identifiers = append(identifiers, &identity.CredentialIdentifier{
349349
Identifier: identifier,
350+
IdentityID: pointerx.Ptr(cred.IdentityID),
350351
IdentityCredentialsID: cred.ID,
351352
IdentityCredentialsTypeID: ct,
352353
NID: p.NetworkID(ctx),
@@ -1441,7 +1442,6 @@ LIMIT 10
14411442
p.NetworkID(ctx),
14421443
).
14431444
All(&recoveryAddresses)
1444-
14451445
if err != nil {
14461446
return nil, sqlcon.HandleError(err)
14471447
}

‎persistence/sql/migratest/migration_test.go‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@ import (
2121
"github.com/ory/kratos/driver"
2222
"github.com/ory/kratos/driver/config"
2323
"github.com/ory/kratos/identity"
24+
"github.com/ory/kratos/persistence/sql"
2425
"github.com/ory/kratos/selfservice/flow/login"
2526
"github.com/ory/kratos/selfservice/flow/recovery"
2627
"github.com/ory/kratos/selfservice/flow/registration"
@@ -32,8 +33,10 @@ import (
3233
"github.com/ory/kratos/x"
3334
"github.com/ory/pop/v6"
3435
"github.com/ory/x/configx"
36+
"github.com/ory/x/fsx"
3537
"github.com/ory/x/logrusx"
3638
"github.com/ory/x/migratest"
39+
"github.com/ory/x/networkx"
3740
"github.com/ory/x/pagination/keysetpagination"
3841
"github.com/ory/x/popx"
3942
"github.com/ory/x/sqlcon"
@@ -119,7 +122,7 @@ func testDatabase(t *testing.T, db string, c *pop.Connection) {
119122
require.NoError(t, c.Open())
120123

121124
tm, err := popx.NewMigrationBox(
122-
os.DirFS("../migrations/sql"),
125+
fsx.Merge(sql.Migrations, networkx.Migrations),
123126
c, l,
124127
popx.WithTestdata(t, os.DirFS("./testdata")),
125128
popx.WithDumpMigrations(),

‎persistence/sql/migrations/sql/20251104000000000000_identifiers_devices_identity_id.down.sql‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
ALTER TABLE identity_credential_identifiers DROP COLUMN identity_id;
2+
ALTER TABLE session_devices DROP COLUMN identity_id;

‎persistence/sql/migrations/sql/20251104000000000000_identifiers_devices_identity_id.mysql.up.sql‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
ALTER TABLE identity_credential_identifiers ADD COLUMN identity_id char(36) NULL;
2+
ALTER TABLE session_devices ADD COLUMN identity_id char(36) NULL;

‎persistence/sql/migrations/sql/20251104000000000000_identifiers_devices_identity_id.sqlite.down.sql‎

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,50 @@
1+
-- For SQLite, we do all operations in a single migration for simplicity.
2+
3+
CREATE TABLE "_identity_credential_identifiers_tmp" (
4+
"id" TEXT PRIMARY KEY,
5+
"identifier" TEXT NOT NULL,
6+
"identity_credential_id" char(36) NOT NULL,
7+
"created_at" DATETIME NOT NULL,
8+
"updated_at" DATETIME NOT NULL,
9+
"nid" char(36),
10+
"identity_credential_type_id" char(36) NOT NULL,
11+
FOREIGN KEY (identity_credential_id) REFERENCES identity_credentials (id) ON UPDATE NO ACTION ON DELETE CASCADE
12+
);
13+
14+
INSERT INTO _identity_credential_identifiers_tmp (id, identifier, identity_credential_id, created_at, updated_at, nid, identity_credential_type_id)
15+
SELECT id, identifier, identity_credential_id, created_at, updated_at, nid, identity_credential_type_id
16+
FROM identity_credential_identifiers;
17+
18+
DROP TABLE identity_credential_identifiers;
19+
ALTER TABLE "_identity_credential_identifiers_tmp" RENAME TO "identity_credential_identifiers";
20+
21+
22+
CREATE UNIQUE INDEX "identity_credential_identifiers_identifier_nid_type_uq_idx" ON "identity_credential_identifiers" (nid, identity_credential_type_id, identifier);
23+
CREATE INDEX identity_credential_identifiers_nid_i_ici_idx ON "identity_credential_identifiers" (nid, identifier, identity_credential_id);
24+
CREATE INDEX identity_credential_identifiers_ici_nid_i_idx ON "identity_credential_identifiers" (identity_credential_id ASC, nid ASC, identifier ASC);
25+
26+
27+
CREATE TABLE IF NOT EXISTS "_session_devices_tmp"
28+
(
29+
"id" UUID PRIMARY KEY NOT NULL,
30+
"ip_address" VARCHAR(50) DEFAULT '',
31+
"user_agent" VARCHAR(512) DEFAULT '',
32+
"location" VARCHAR(512) DEFAULT '',
33+
"nid" UUID NOT NULL,
34+
"session_id" UUID NOT NULL,
35+
"created_at" timestamp NOT NULL,
36+
"updated_at" timestamp NOT NULL,
37+
CONSTRAINT "session_metadata_sessions_id_fk" FOREIGN KEY ("session_id") REFERENCES "sessions" ("id") ON DELETE cascade,
38+
CONSTRAINT "session_metadata_nid_fk" FOREIGN KEY ("nid") REFERENCES "networks" ("id") ON DELETE cascade,
39+
CONSTRAINT unique_session_device UNIQUE (nid, session_id, ip_address, user_agent)
40+
);
41+
42+
INSERT INTO "_session_devices_tmp" (id, ip_address, user_agent, location, nid, session_id, created_at, updated_at)
43+
SELECT id, ip_address, user_agent, location, nid, session_id, created_at, updated_at
44+
FROM session_devices;
45+
46+
DROP TABLE session_devices;
47+
ALTER TABLE "_session_devices_tmp" RENAME TO "session_devices";
48+
49+
CREATE INDEX session_devices_nid_idx ON session_devices (nid ASC);
50+
CREATE INDEX session_devices_session_id_idx ON session_devices (session_id ASC);

‎persistence/sql/migrations/sql/20251104000000000000_identifiers_devices_identity_id.sqlite.up.sql‎

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,57 @@
1+
-- For SQLite, we do all operations in a single migration for simplicity.
2+
3+
CREATE TABLE IF NOT EXISTS "_identity_credential_identifiers_tmp" (
4+
"id" TEXT PRIMARY KEY,
5+
"identifier" TEXT NOT NULL,
6+
"identity_credential_id" char(36) NOT NULL,
7+
"created_at" DATETIME NOT NULL,
8+
"updated_at" DATETIME NOT NULL,
9+
"nid" char(36),
10+
"identity_credential_type_id" char(36) NOT NULL,
11+
"identity_id" char(36) NOT NULL,
12+
FOREIGN KEY (identity_id) REFERENCES identities (id) ON UPDATE RESTRICT ON DELETE CASCADE,
13+
FOREIGN KEY (nid) REFERENCES networks (id) ON UPDATE RESTRICT ON DELETE CASCADE,
14+
FOREIGN KEY (identity_credential_id) REFERENCES identity_credentials (id) ON UPDATE RESTRICT ON DELETE CASCADE,
15+
FOREIGN KEY (identity_credential_type_id) REFERENCES identity_credential_types (id) ON UPDATE RESTRICT ON DELETE CASCADE
16+
);
17+
18+
19+
INSERT INTO _identity_credential_identifiers_tmp (id, identifier, identity_credential_id, created_at, updated_at, nid, identity_credential_type_id, identity_id)
20+
SELECT ici.id, ici.identifier, ici.identity_credential_id, ici.created_at, ici.updated_at, ici.nid, ici.identity_credential_type_id, ic.identity_id
21+
FROM identity_credential_identifiers ici
22+
INNER JOIN identity_credentials ic ON ici.identity_credential_id = ic.id AND ici.nid = ic.nid;
23+
24+
DROP TABLE identity_credential_identifiers;
25+
ALTER TABLE "_identity_credential_identifiers_tmp" RENAME TO "identity_credential_identifiers";
26+
27+
CREATE UNIQUE INDEX "identity_credential_identifiers_identifier_nid_type_uq_idx" ON "identity_credential_identifiers" (nid, identity_credential_type_id, identifier);
28+
CREATE INDEX identity_credential_identifiers_nid_i_ici_idx ON "identity_credential_identifiers" (nid, identifier, identity_credential_id);
29+
CREATE INDEX identity_credential_identifiers_ici_nid_i_idx ON "identity_credential_identifiers" (identity_credential_id ASC, nid ASC, identifier ASC);
30+
31+
32+
CREATE TABLE IF NOT EXISTS "_session_devices_tmp"
33+
(
34+
"id" UUID PRIMARY KEY NOT NULL,
35+
"identity_id" UUID NOT NULL,
36+
"ip_address" VARCHAR(50) DEFAULT '',
37+
"user_agent" VARCHAR(512) DEFAULT '',
38+
"location" VARCHAR(512) DEFAULT '',
39+
"nid" UUID NOT NULL,
40+
"session_id" UUID NOT NULL,
41+
"created_at" timestamp NOT NULL,
42+
"updated_at" timestamp NOT NULL,
43+
CONSTRAINT "session_metadata_sessions_id_fk" FOREIGN KEY ("session_id") REFERENCES "sessions" ("id") ON DELETE cascade,
44+
CONSTRAINT "session_metadata_nid_fk" FOREIGN KEY ("nid") REFERENCES "networks" ("id") ON DELETE cascade,
45+
CONSTRAINT "session_devices_identity_id_fk" FOREIGN KEY ("identity_id") REFERENCES "identities" ("id") ON DELETE cascade,
46+
CONSTRAINT unique_session_device UNIQUE (nid, session_id, ip_address, user_agent)
47+
);
48+
49+
INSERT INTO "_session_devices_tmp" (id, identity_id, ip_address, user_agent, location, nid, session_id, created_at, updated_at)
50+
SELECT sd.id, s.identity_id, sd.ip_address, sd.user_agent, sd.location, sd.nid, sd.session_id, sd.created_at, sd.updated_at
51+
FROM session_devices sd JOIN sessions s ON sd.session_id = s.id;
52+
53+
DROP TABLE session_devices;
54+
ALTER TABLE "_session_devices_tmp" RENAME TO "session_devices";
55+
56+
CREATE INDEX session_devices_nid_idx ON session_devices (nid ASC);
57+
CREATE INDEX session_devices_session_id_idx ON session_devices (session_id ASC);

‎persistence/sql/migrations/sql/20251104000000000000_identifiers_devices_identity_id.up.sql‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
ALTER TABLE identity_credential_identifiers ADD COLUMN identity_id UUID NULL;
2+
ALTER TABLE session_devices ADD COLUMN identity_id UUID NULL;

‎persistence/sql/persister_session.go‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -286,6 +286,7 @@ func (p *Persister) UpsertSession(ctx context.Context, s *session.Session) (err
286286
device := &(s.Devices[i])
287287
device.SessionID = s.ID
288288
device.NID = s.NID
289+
device.IdentityID = pointerx.Ptr(s.IdentityID)
289290

290291
if device.Location != nil {
291292
device.Location = pointerx.Ptr(stringsx.TruncateByteLen(*device.Location, SessionDeviceLocationMaxLength))

0 commit comments

Comments
 (0)