maedhroz commented on code in PR #4572: URL: https://github.com/apache/cassandra/pull/4572#discussion_r3076316386
########## doc/modules/cassandra/pages/developing/cql/transactions-examples.adoc: ########## @@ -0,0 +1,418 @@ += Accord Transaction Design Patterns +:page-nav-title: Transaction Patterns + +This page provides advanced design patterns for Accord transactions. These patterns solve common distributed system challenges that were difficult or impossible to address with eventual consistency. + +For basic syntax, getting started, and migration guides, see xref:developing/cql/transactions.adoc[Accord Transactions]. + +== Pattern: Synchronous Unique Constraints + +Cassandra's primary key enforces uniqueness, but what if you need uniqueness on a non-primary-key column like `email` or `username`? This pattern uses user-maintained lookup tables to enforce multiple unique constraints atomically. + +=== The Challenge + +You have a `users` table keyed by `user_id`, but you also need: + +* Unique `email` addresses +* Unique `username` values +* The ability to **change** email or username while maintaining uniqueness + +=== Schema + +[source,cql] +---- +CREATE TABLE users ( + user_id uuid PRIMARY KEY, + username text, + email text, + display_name text, + created_at timestamp +) WITH transactional_mode = 'full'; + +-- Sidecar tables for uniqueness enforcement +CREATE TABLE username_index ( + username text PRIMARY KEY, + user_id uuid +) WITH transactional_mode = 'full'; + +CREATE TABLE email_index ( + email text PRIMARY KEY, + user_id uuid +) WITH transactional_mode = 'full'; +---- + +=== Creating a User with Unique Constraints + +[source,cql] +---- +BEGIN TRANSACTION + LET existing_username = (SELECT user_id FROM username_index WHERE username = ? LIMIT 1); + LET existing_email = (SELECT user_id FROM email_index WHERE email = ? LIMIT 1); + + IF existing_username IS NULL AND existing_email IS NULL THEN + INSERT INTO users (user_id, username, email, display_name, created_at) + VALUES (?, ?, ?, ?, toTimestamp(now())); + + INSERT INTO username_index (username, user_id) VALUES (?, ?); + INSERT INTO email_index (email, user_id) VALUES (?, ?); + END IF +COMMIT TRANSACTION +---- + +=== Changing a Username (The Hard Part) Review Comment: ```suggestion === Changing a Username ``` ;) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
