Buckets:

download
raw
90 kB
import pytest import psycopg from enum import Enum as PyEnum from sqlalchemy import ( Column, Integer, String, Boolean, DateTime, Float, BigInteger, Text, ForeignKey, Enum, Index, UniqueConstraint, text, ) from sqlalchemy.dialects.postgresql import JSONB, TSVECTOR from sqlalchemy.orm import declarative_base, Session from deepsel.sqlalchemy import DatabaseManager class Status(PyEnum): ACTIVE = "active" INACTIVE = "inactive" PENDING = "pending" class Priority(PyEnum): LOW = "low" MEDIUM = "medium" HIGH = "high" class TestDatabaseManagerTableCreation: """Test table creation and basic schema operations.""" def test_creates_new_table_with_columns(self, pg_conn, sqlalchemy_db_url): """Test that DatabaseManager creates a new table with specified columns.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), nullable=False, unique=True) name = Column(String(100)) models_pool = {"users": User} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Assert table exists result = pg_conn.execute("SELECT to_regclass('users') IS NOT NULL").fetchone() assert result[0] is True # Assert columns exist with correct types cols = {row[0]: (row[1], row[2]) for row in pg_conn.execute(""" SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name='users' ORDER BY ordinal_position """).fetchall()} assert "id" in cols assert cols["id"] == ("integer", "NO") assert "email" in cols assert cols["email"] == ("character varying", "NO") assert "name" in cols assert cols["name"] == ("character varying", "YES") # Assert primary key exists pk = pg_conn.execute(""" SELECT constraint_name FROM information_schema.table_constraints WHERE table_name='users' AND constraint_type='PRIMARY KEY' """).fetchone() assert pk is not None # Assert unique constraint exists on email unique_constraints = pg_conn.execute(""" SELECT i.relname FROM pg_index x JOIN pg_class t ON t.oid = x.indrelid JOIN pg_class i ON i.oid = x.indexrelid WHERE t.relname = 'users' AND x.indisunique """).fetchall() assert len(unique_constraints) >= 1 def test_creates_table_with_bigint_primary_key(self, pg_conn, sqlalchemy_db_url): """Test creating table with BigInteger primary key (BIGSERIAL).""" Base = declarative_base() class Article(Base): __tablename__ = "articles" id = Column(BigInteger, primary_key=True) title = Column(String(200), nullable=False) models_pool = {"articles": Article} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Assert table exists result = pg_conn.execute( "SELECT to_regclass('articles') IS NOT NULL" ).fetchone() assert result[0] is True # Assert id column is bigint with identity col_info = pg_conn.execute(""" SELECT data_type, is_identity FROM information_schema.columns WHERE table_name='articles' AND column_name='id' """).fetchone() assert col_info[0] == "bigint" assert col_info[1] == "YES" def test_creates_table_with_composite_primary_key(self, pg_conn, sqlalchemy_db_url): """Test creating table with composite primary key.""" Base = declarative_base() class UserRole(Base): __tablename__ = "user_roles" user_id = Column(Integer, primary_key=True) role_id = Column(Integer, primary_key=True) granted_at = Column(String(50)) models_pool = {"user_roles": UserRole} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Assert composite primary key exists pk_cols = pg_conn.execute(""" SELECT kcu.column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name WHERE tc.table_name='user_roles' AND tc.constraint_type='PRIMARY KEY' ORDER BY kcu.ordinal_position """).fetchall() pk_column_names = [col[0] for col in pk_cols] assert pk_column_names == ["user_id", "role_id"] def test_creates_multiple_tables(self, pg_conn, sqlalchemy_db_url): """Test creating multiple tables in one operation.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) class Post(Base): __tablename__ = "posts" id = Column(Integer, primary_key=True) title = Column(String(200)) class Comment(Base): __tablename__ = "comments" id = Column(Integer, primary_key=True) content = Column(Text) models_pool = {"users": User, "posts": Post, "comments": Comment} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Assert all tables exist tables = [row[0] for row in pg_conn.execute(""" SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema() """).fetchall()] assert "users" in tables assert "posts" in tables assert "comments" in tables class TestDatabaseManagerColumnOperations: """Test column addition, modification, and removal.""" def test_adds_new_column_to_existing_table(self, pg_conn, sqlalchemy_db_url): """Test adding a new column to an existing table.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) models_pool = {"users": User} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Now add a new column Base2 = declarative_base() class User2(Base2): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) name = Column(String(100)) age = Column(Integer) models_pool2 = {"users": User2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Assert new columns exist cols = [row[0] for row in pg_conn.execute(""" SELECT column_name FROM information_schema.columns WHERE table_name='users' """).fetchall()] assert "name" in cols assert "age" in cols def test_removes_column_from_existing_table(self, pg_conn, sqlalchemy_db_url): """Test removing a column from an existing table.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) name = Column(String(100)) age = Column(Integer) models_pool = {"users": User} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Now remove columns Base2 = declarative_base() class User2(Base2): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) models_pool2 = {"users": User2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Assert columns are removed cols = [row[0] for row in pg_conn.execute(""" SELECT column_name FROM information_schema.columns WHERE table_name='users' """).fetchall()] assert "name" not in cols assert "age" not in cols assert "email" in cols def test_changes_column_nullable(self, pg_conn, sqlalchemy_db_url): """Test changing column nullable constraint.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), nullable=True) models_pool = {"users": User} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Change to NOT NULL with default Base2 = declarative_base() class User2(Base2): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), nullable=False, default="default@example.com") models_pool2 = {"users": User2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Assert nullable changed is_nullable = pg_conn.execute(""" SELECT is_nullable FROM information_schema.columns WHERE table_name='users' AND column_name='email' """).fetchone()[0] assert is_nullable == "NO" def test_changes_column_type(self, pg_conn, sqlalchemy_db_url): """Test changing column data type.""" Base = declarative_base() class Product(Base): __tablename__ = "products" id = Column(Integer, primary_key=True) price = Column(Integer) models_pool = {"products": Product} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Change type to Float Base2 = declarative_base() class Product2(Base2): __tablename__ = "products" id = Column(Integer, primary_key=True) price = Column(Float) models_pool2 = {"products": Product2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Assert type changed data_type = pg_conn.execute(""" SELECT data_type FROM information_schema.columns WHERE table_name='products' AND column_name='price' """).fetchone()[0] assert data_type == "double precision" class TestDatabaseManagerConstraints: """Test constraint operations (unique, index, foreign key).""" def test_adds_unique_constraint(self, pg_conn, sqlalchemy_db_url): """Test adding unique constraint to a column.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) models_pool = {"users": User} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Add unique constraint Base2 = declarative_base() class User2(Base2): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), unique=True) models_pool2 = {"users": User2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Assert unique constraint exists unique_constraints = pg_conn.execute(""" SELECT constraint_name FROM information_schema.table_constraints WHERE table_name='users' AND constraint_type='UNIQUE' """).fetchall() assert len(unique_constraints) >= 1 def test_removes_unique_constraint(self, pg_conn, sqlalchemy_db_url): """Test removing unique constraint from a column.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), unique=True) models_pool = {"users": User} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Remove unique constraint Base2 = declarative_base() class User2(Base2): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), unique=False) models_pool2 = {"users": User2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Assert unique constraint removed (only PK unique constraint should remain) unique_constraints = pg_conn.execute(""" SELECT constraint_name FROM information_schema.table_constraints WHERE table_name='users' AND constraint_type='UNIQUE' """).fetchall() # Should have no UNIQUE constraints (PK is separate) assert len(unique_constraints) == 0 def test_adds_index(self, pg_conn, sqlalchemy_db_url): """Test adding index to a column.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) models_pool = {"users": User} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Add index Base2 = declarative_base() class User2(Base2): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), index=True) models_pool2 = {"users": User2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Assert index exists (non-unique) indexes = pg_conn.execute(""" SELECT i.relname FROM pg_index x JOIN pg_class t ON t.oid = x.indrelid JOIN pg_class i ON i.oid = x.indexrelid WHERE t.relname = 'users' AND NOT x.indisunique AND NOT x.indisprimary """).fetchall() assert len(indexes) >= 1 def test_removes_index(self, pg_conn, sqlalchemy_db_url): """Test removing index from a column.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), index=True) models_pool = {"users": User} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Remove index Base2 = declarative_base() class User2(Base2): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), index=False) models_pool2 = {"users": User2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Assert index removed indexes = pg_conn.execute(""" SELECT i.relname FROM pg_index x JOIN pg_class t ON t.oid = x.indrelid JOIN pg_class i ON i.oid = x.indexrelid WHERE t.relname = 'users' AND NOT x.indisunique AND NOT x.indisprimary """).fetchall() assert len(indexes) == 0 def test_composite_index_survives_columns_without_index_flag( self, pg_conn, sqlalchemy_db_url ): """A non-unique composite index on columns with index=False is left alone. Regression: the per-column index diff used to match the composite once per constituent column — the first pass dropped it, the second pass hit `UndefinedObject`, and the rolled-back boot crashlooped forever. """ # Pre-seed the DB with an app-managed composite index — simulates the # hvac fsm_sync_item table. pg_conn.execute(""" CREATE TABLE sync_item ( id SERIAL PRIMARY KEY, organization_id INTEGER, created_at TIMESTAMP, visit_id INTEGER ) """) pg_conn.execute(""" CREATE INDEX ix_sync_item_org_created ON sync_item (organization_id, created_at) """) pg_conn.commit() # Two boots: the first must not crash, the second must be idempotent. for _ in range(2): Base_n = declarative_base() SyncItem_n = type( "SyncItem", (Base_n,), { "__tablename__": "sync_item", "id": Column(Integer, primary_key=True), "organization_id": Column(Integer), "created_at": Column(DateTime), "visit_id": Column(Integer, index=True), }, ) DatabaseManager( sqlalchemy_declarative_base=Base_n, db_url=sqlalchemy_db_url, models_pool={"sync_item": SyncItem_n}, ) indexes = pg_conn.execute(""" SELECT i.relname FROM pg_index x JOIN pg_class t ON t.oid = x.indrelid JOIN pg_class i ON i.oid = x.indexrelid WHERE t.relname = 'sync_item' AND NOT x.indisunique AND NOT x.indisprimary """).fetchall() names = {row[0] for row in indexes} # The composite index survives, and the single-column index=True # reconciliation still works alongside it. assert "ix_sync_item_org_created" in names assert any("visit_id" in name for name in names) def test_adds_foreign_key_constraint(self, pg_conn, sqlalchemy_db_url): """Test adding foreign key constraint.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) class Post(Base): __tablename__ = "posts" id = Column(Integer, primary_key=True) title = Column(String(200)) user_id = Column(Integer, ForeignKey("users.id")) models_pool = {"users": User, "posts": Post} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Assert foreign key exists fks = pg_conn.execute(""" SELECT tc.constraint_name, kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='posts' """).fetchall() assert len(fks) == 1 assert fks[0][1] == "user_id" assert fks[0][2] == "users" assert fks[0][3] == "id" def test_updates_foreign_key_reference(self, pg_conn, sqlalchemy_db_url): """Test updating foreign key to reference different table/column.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) class Author(Base): __tablename__ = "authors" id = Column(Integer, primary_key=True) name = Column(String(255)) class Post(Base): __tablename__ = "posts" id = Column(Integer, primary_key=True) title = Column(String(200)) user_id = Column(Integer, ForeignKey("users.id")) models_pool = {"users": User, "authors": Author, "posts": Post} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Update foreign key to reference authors Base2 = declarative_base() class User2(Base2): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) class Author2(Base2): __tablename__ = "authors" id = Column(Integer, primary_key=True) name = Column(String(255)) class Post2(Base2): __tablename__ = "posts" id = Column(Integer, primary_key=True) title = Column(String(200)) user_id = Column(Integer, ForeignKey("authors.id")) models_pool2 = {"users": User2, "authors": Author2, "posts": Post2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Assert foreign key now references authors fks = pg_conn.execute(""" SELECT ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='posts' """).fetchone() assert fks[0] == "authors" assert fks[1] == "id" def test_removes_foreign_key_constraint(self, pg_conn, sqlalchemy_db_url): """Test removing foreign key constraint.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) class Post(Base): __tablename__ = "posts" id = Column(Integer, primary_key=True) title = Column(String(200)) user_id = Column(Integer, ForeignKey("users.id")) models_pool = {"users": User, "posts": Post} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Remove foreign key Base2 = declarative_base() class User2(Base2): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) class Post2(Base2): __tablename__ = "posts" id = Column(Integer, primary_key=True) title = Column(String(200)) user_id = Column(Integer) models_pool2 = {"users": User2, "posts": Post2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Assert foreign key removed fks = pg_conn.execute(""" SELECT constraint_name FROM information_schema.table_constraints WHERE table_name='posts' AND constraint_type='FOREIGN KEY' """).fetchall() assert len(fks) == 0 class TestDatabaseManagerEnumTypes: """Test enum type creation and modification.""" def test_creates_enum_type_and_column(self, pg_conn, sqlalchemy_db_url): """Test creating enum type and using it in a column.""" Base = declarative_base() class Task(Base): __tablename__ = "tasks" id = Column(Integer, primary_key=True) status = Column(Enum(Status)) models_pool = {"tasks": Task} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Assert enum type exists enum_types = pg_conn.execute(""" SELECT typname, enumlabel FROM pg_type t JOIN pg_enum e ON t.oid = e.enumtypid JOIN pg_namespace n ON t.typnamespace = n.oid WHERE typname LIKE '%status%' AND n.nspname = current_schema() ORDER BY e.enumsortorder """).fetchall() assert len(enum_types) == 3 enum_values = [e[1] for e in enum_types] assert "ACTIVE" in enum_values assert "INACTIVE" in enum_values assert "PENDING" in enum_values # Assert column uses enum type col_type = pg_conn.execute(""" SELECT udt_name FROM information_schema.columns WHERE table_name='tasks' AND column_name='status' """).fetchone()[0] assert "status" in col_type def test_adds_value_to_existing_enum(self, pg_conn, sqlalchemy_db_url): """Test adding new value to existing enum type.""" Base = declarative_base() class Task(Base): __tablename__ = "tasks" id = Column(Integer, primary_key=True) status = Column(Enum(Status, name="status")) models_pool = {"tasks": Task} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Create new enum with additional value but same name class StatusV2(PyEnum): ACTIVE = "active" INACTIVE = "inactive" PENDING = "pending" COMPLETED = "completed" Base2 = declarative_base() class Task2(Base2): __tablename__ = "tasks" id = Column(Integer, primary_key=True) status = Column(Enum(StatusV2, name="status")) models_pool2 = {"tasks": Task2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Assert new enum value exists enum_values = [row[0] for row in pg_conn.execute(""" SELECT enumlabel FROM pg_type t JOIN pg_enum e ON t.oid = e.enumtypid JOIN pg_namespace n ON t.typnamespace = n.oid WHERE typname = 'status' AND n.nspname = current_schema() ORDER BY e.enumsortorder """).fetchall()] assert "COMPLETED" in enum_values assert len(enum_values) == 4 class TestDatabaseManagerTableDeletion: """Test table deletion operations.""" def test_drops_removed_table(self, pg_conn, sqlalchemy_db_url): """Test that tables removed from models are dropped from database.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) class Post(Base): __tablename__ = "posts" id = Column(Integer, primary_key=True) title = Column(String(200)) models_pool = {"users": User, "posts": Post} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Remove posts table from models Base2 = declarative_base() class User2(Base2): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) models_pool2 = {"users": User2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Assert posts table is dropped tables = [row[0] for row in pg_conn.execute(""" SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema() """).fetchall()] assert "posts" not in tables assert "users" in tables def test_preserves_alembic_version_table(self, pg_conn, sqlalchemy_db_url): """Test that alembic_version table is not dropped.""" # Create alembic_version table pg_conn.execute(""" CREATE TABLE alembic_version ( version_num VARCHAR(32) NOT NULL, CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num) ) """) pg_conn.commit() Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) models_pool = {"users": User} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Assert alembic_version table still exists tables = [row[0] for row in pg_conn.execute(""" SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema() """).fetchall()] assert "alembic_version" in tables class TestDatabaseManagerCompositeUniqueConstraints: """Test composite unique constraints with organization_id.""" def test_creates_composite_unique_constraint_with_organization_id( self, pg_conn, sqlalchemy_db_url ): """Test creating composite unique constraint for multi-tenant tables.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) organization_id = Column(Integer, nullable=False) email = Column(String(255), unique=True) models_pool = {"users": User} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Assert composite unique constraint exists constraints = pg_conn.execute(""" SELECT tc.constraint_name, string_agg(kcu.column_name, ', ' ORDER BY kcu.ordinal_position) as columns FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name WHERE tc.table_name = 'users' AND tc.constraint_type = 'UNIQUE' GROUP BY tc.constraint_name """).fetchall() # Should have composite unique constraint on email and organization_id composite_found = False for constraint in constraints: if "email" in constraint[1] and "organization_id" in constraint[1]: composite_found = True break assert composite_found class TestDatabaseManagerLegacyUniqueConstraintReconciliation: """Test that legacy single-column unique constraints on multi-tenant tables are dropped and replaced with composite (col, organization_id) constraints. Reproduces the alcoris-site bug where `menu_string_id_key` (single-column UNIQUE on string_id) blocked installing a theme into a second organization because seed rows from org 1 already occupied the string_id namespace. """ def _list_unique_constraints(self, pg_conn, table_name): return pg_conn.execute(f""" SELECT tc.constraint_name, string_agg(kcu.column_name, ',' ORDER BY kcu.ordinal_position) AS cols FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name WHERE tc.table_name = '{table_name}' AND tc.constraint_type = 'UNIQUE' GROUP BY tc.constraint_name ORDER BY tc.constraint_name """).fetchall() def test_drops_legacy_single_unique_when_composite_exists( self, pg_conn, sqlalchemy_db_url ): """A pre-existing single-column unique constraint alongside the composite gets dropped, leaving only the composite.""" # Pre-seed the DB with both constraints — simulates the alcoris menu table. pg_conn.execute(""" CREATE TABLE menu ( id SERIAL PRIMARY KEY, string_id VARCHAR(255), organization_id INTEGER NOT NULL, CONSTRAINT menu_string_id_key UNIQUE (string_id), CONSTRAINT menu_string_id_organization_id_unique UNIQUE (string_id, organization_id) ) """) pg_conn.commit() Base = declarative_base() class Menu(Base): __tablename__ = "menu" id = Column(Integer, primary_key=True) string_id = Column(String(255), unique=True) organization_id = Column(Integer, nullable=False) DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool={"menu": Menu}, ) constraints = self._list_unique_constraints(pg_conn, "menu") names = {c[0] for c in constraints} cols = {c[1] for c in constraints} assert "menu_string_id_key" not in names assert "string_id,organization_id" in cols # The composite must survive — per-org duplicates must remain allowed. assert len(constraints) == 1 def test_replaces_legacy_single_unique_with_composite( self, pg_conn, sqlalchemy_db_url ): """When only a legacy single-column constraint exists, it gets dropped and the composite is created in its place.""" pg_conn.execute(""" CREATE TABLE menu ( id SERIAL PRIMARY KEY, string_id VARCHAR(255), organization_id INTEGER NOT NULL, CONSTRAINT menu_string_id_key UNIQUE (string_id) ) """) pg_conn.commit() Base = declarative_base() class Menu(Base): __tablename__ = "menu" id = Column(Integer, primary_key=True) string_id = Column(String(255), unique=True) organization_id = Column(Integer, nullable=False) DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool={"menu": Menu}, ) constraints = self._list_unique_constraints(pg_conn, "menu") names = {c[0] for c in constraints} cols = {c[1] for c in constraints} assert "menu_string_id_key" not in names assert "string_id,organization_id" in cols def test_per_org_duplicates_allowed_after_reconciliation( self, pg_conn, sqlalchemy_db_url ): """End-to-end behavioural check: after reconciliation, two organizations can hold the same string_id; the same string_id within one org cannot.""" pg_conn.execute(""" CREATE TABLE menu ( id SERIAL PRIMARY KEY, string_id VARCHAR(255), organization_id INTEGER NOT NULL, CONSTRAINT menu_string_id_key UNIQUE (string_id) ) """) pg_conn.commit() Base = declarative_base() class Menu(Base): __tablename__ = "menu" id = Column(Integer, primary_key=True) string_id = Column(String(255), unique=True) organization_id = Column(Integer, nullable=False) DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool={"menu": Menu}, ) pg_conn.execute( "INSERT INTO menu (string_id, organization_id) VALUES ('finance', 1)" ) # Same string_id under a different org — previously blocked, now allowed. pg_conn.execute( "INSERT INTO menu (string_id, organization_id) VALUES ('finance', 2)" ) pg_conn.commit() # But within the same org, duplicate is still rejected. with pytest.raises(psycopg.errors.UniqueViolation): pg_conn.execute( "INSERT INTO menu (string_id, organization_id) VALUES ('finance', 2)" ) pg_conn.commit() pg_conn.rollback() def test_leaves_non_multitenant_single_unique_alone( self, pg_conn, sqlalchemy_db_url ): """Tables without organization_id keep their global single-column unique constraint untouched — these are intentionally globally unique (e.g. country, locale, currency, organization itself).""" Base = declarative_base() class Country(Base): __tablename__ = "country" id = Column(Integer, primary_key=True) string_id = Column(String(255), unique=True) DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool={"country": Country}, ) constraints = self._list_unique_constraints(pg_conn, "country") cols = {c[1] for c in constraints} assert "string_id" in cols # No composite was invented for a table without organization_id. assert "string_id,organization_id" not in cols def test_reconciliation_is_idempotent(self, pg_conn, sqlalchemy_db_url): """Running migration twice on an already-reconciled table is a no-op.""" Base = declarative_base() class Menu(Base): __tablename__ = "menu" id = Column(Integer, primary_key=True) string_id = Column(String(255), unique=True) organization_id = Column(Integer, nullable=False) DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool={"menu": Menu}, ) before = self._list_unique_constraints(pg_conn, "menu") Base2 = declarative_base() class Menu2(Base2): __tablename__ = "menu" id = Column(Integer, primary_key=True) string_id = Column(String(255), unique=True) organization_id = Column(Integer, nullable=False) DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool={"menu": Menu2}, ) after = self._list_unique_constraints(pg_conn, "menu") assert before == after class TestDatabaseManagerPrimaryKeyChanges: """Test primary key modification operations.""" def test_changes_primary_key_columns(self, pg_conn, sqlalchemy_db_url): """Test changing primary key from single to composite.""" Base = declarative_base() class UserRole(Base): __tablename__ = "user_roles" id = Column(Integer, primary_key=True) user_id = Column(Integer) role_id = Column(Integer) models_pool = {"user_roles": UserRole} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Change to composite primary key Base2 = declarative_base() class UserRole2(Base2): __tablename__ = "user_roles" user_id = Column(Integer, primary_key=True) role_id = Column(Integer, primary_key=True) models_pool2 = {"user_roles": UserRole2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Assert composite primary key exists pk_cols = [row[0] for row in pg_conn.execute(""" SELECT kcu.column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name WHERE tc.table_name='user_roles' AND tc.constraint_type='PRIMARY KEY' ORDER BY kcu.ordinal_position """).fetchall()] assert pk_cols == ["user_id", "role_id"] class TestDatabaseManagerComplexScenarios: """Test complex real-world scenarios.""" def test_full_schema_evolution(self, pg_conn, sqlalchemy_db_url): """Test complete schema evolution: create, modify, add relations.""" # Step 1: Create initial schema Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) models_pool = {"users": User} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Step 2: Add columns and constraints Base2 = declarative_base() class User2(Base2): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), unique=True, nullable=False) name = Column(String(100)) is_active = Column(Boolean, default=True) models_pool2 = {"users": User2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Step 3: Add related table with foreign key Base3 = declarative_base() class User3(Base3): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), unique=True, nullable=False) name = Column(String(100)) is_active = Column(Boolean, default=True) class Post3(Base3): __tablename__ = "posts" id = Column(Integer, primary_key=True) title = Column(String(200), nullable=False) content = Column(Text) user_id = Column(Integer, ForeignKey("users.id")) status = Column(Enum(Status), default=Status.PENDING) models_pool3 = {"users": User3, "posts": Post3} DatabaseManager( sqlalchemy_declarative_base=Base3, db_url=sqlalchemy_db_url, models_pool=models_pool3, ) # Verify final state tables = [row[0] for row in pg_conn.execute(""" SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema() ORDER BY table_name """).fetchall()] assert "users" in tables assert "posts" in tables # Verify users table structure user_cols = dict(pg_conn.execute(""" SELECT column_name, is_nullable FROM information_schema.columns WHERE table_name='users' """).fetchall()) assert user_cols["email"] == "NO" assert user_cols["name"] == "YES" assert user_cols["is_active"] == "YES" # Verify foreign key fks = pg_conn.execute(""" SELECT COUNT(*) FROM information_schema.table_constraints WHERE table_name='posts' AND constraint_type='FOREIGN KEY' """).fetchone()[0] assert fks == 1 def test_handles_circular_foreign_keys(self, pg_conn, sqlalchemy_db_url): """Test handling of circular foreign key dependencies.""" Base = declarative_base() class Department(Base): __tablename__ = "departments" id = Column(Integer, primary_key=True) name = Column(String(100)) manager_id = Column(Integer, ForeignKey("employees.id")) class Employee(Base): __tablename__ = "employees" id = Column(Integer, primary_key=True) name = Column(String(100)) department_id = Column(Integer, ForeignKey("departments.id")) models_pool = {"departments": Department, "employees": Employee} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Verify both tables exist tables = [row[0] for row in pg_conn.execute(""" SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema() ORDER BY table_name """).fetchall()] assert "departments" in tables assert "employees" in tables # Verify foreign keys exist dept_fks = pg_conn.execute(""" SELECT COUNT(*) FROM information_schema.table_constraints WHERE table_name='departments' AND constraint_type='FOREIGN KEY' """).fetchone()[0] emp_fks = pg_conn.execute(""" SELECT COUNT(*) FROM information_schema.table_constraints WHERE table_name='employees' AND constraint_type='FOREIGN KEY' """).fetchone()[0] assert dept_fks == 1 assert emp_fks == 1 class TestDatabaseManagerTSVectorSupport: """Test TSVECTOR column type support.""" def test_creates_tsvector_column(self, pg_conn, sqlalchemy_db_url): """Test that DatabaseManager creates a table with a TSVECTOR column.""" Base = declarative_base() class Document(Base): __tablename__ = "documents" id = Column(Integer, primary_key=True) title = Column(String(255)) search_vector = Column(TSVECTOR) models_pool = {"documents": Document} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) cols = {row[0]: row[1] for row in pg_conn.execute(""" SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'documents' ORDER BY ordinal_position """).fetchall()} assert "search_vector" in cols assert cols["search_vector"] == "tsvector" def test_tsvector_column_not_recreated_on_rerun(self, pg_conn, sqlalchemy_db_url): """Test that TSVECTOR column is not dropped/recreated on subsequent runs.""" Base = declarative_base() class Document(Base): __tablename__ = "documents" id = Column(Integer, primary_key=True) title = Column(String(255)) search_vector = Column(TSVECTOR) models_pool = {"documents": Document} # First run - creates table DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Insert a row to detect if column gets dropped pg_conn.execute( "INSERT INTO documents (id, title, search_vector) VALUES (1, 'test', to_tsvector('english', 'hello world'))" ) # Second run - should not drop/recreate the column Base2 = declarative_base() class Document2(Base2): __tablename__ = "documents" id = Column(Integer, primary_key=True) title = Column(String(255)) search_vector = Column(TSVECTOR) models_pool2 = {"documents": Document2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # If column was dropped, the row's search_vector would be gone result = pg_conn.execute( "SELECT search_vector IS NOT NULL FROM documents WHERE id = 1" ).fetchone() assert result[0] is True class TestDatabaseManagerServerDefault: """Test server_default support for columns.""" def test_server_default_applied_on_new_column(self, pg_conn, sqlalchemy_db_url): """Test that server_default is applied when creating a new column.""" Base = declarative_base() class Event(Base): __tablename__ = "events" id = Column(Integer, primary_key=True) name = Column(String(100)) created_at = Column(Text, server_default=text("now()"), nullable=False) models_pool = {"events": Event} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Assert column has a server default col_default = pg_conn.execute(""" SELECT column_default FROM information_schema.columns WHERE table_name='events' AND column_name='created_at' """).fetchone()[0] assert col_default is not None assert "now()" in col_default def test_server_default_with_string_value(self, pg_conn, sqlalchemy_db_url): """Test server_default with a plain string value.""" Base = declarative_base() class Config(Base): __tablename__ = "config" id = Column(Integer, primary_key=True) status = Column(String(50), server_default="active") models_pool = {"config": Config} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) col_default = pg_conn.execute(""" SELECT column_default FROM information_schema.columns WHERE table_name='config' AND column_name='status' """).fetchone()[0] assert col_default is not None assert "active" in col_default class TestDatabaseManagerForeignKeyActions: """Test ON DELETE / ON UPDATE support for foreign keys.""" def test_foreign_key_ondelete_cascade(self, pg_conn, sqlalchemy_db_url): """Test that ON DELETE CASCADE is applied to foreign keys.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) class Post(Base): __tablename__ = "posts" id = Column(Integer, primary_key=True) title = Column(String(200)) user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE")) models_pool = {"users": User, "posts": Post} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Check confdeltype: 'c' = CASCADE, 'a' = NO ACTION, 'n' = SET NULL result = pg_conn.execute(""" SELECT confdeltype FROM pg_constraint WHERE conrelid = 'posts'::regclass AND contype = 'f' """).fetchone() assert result is not None assert result[0] == "c" # CASCADE def test_foreign_key_ondelete_set_null(self, pg_conn, sqlalchemy_db_url): """Test that ON DELETE SET NULL is applied to foreign keys.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) class Post(Base): __tablename__ = "posts" id = Column(Integer, primary_key=True) title = Column(String(200)) user_id = Column(Integer, ForeignKey("users.id", ondelete="SET NULL")) models_pool = {"users": User, "posts": Post} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) result = pg_conn.execute(""" SELECT confdeltype FROM pg_constraint WHERE conrelid = 'posts'::regclass AND contype = 'f' """).fetchone() assert result is not None assert result[0] == "n" # SET NULL def test_foreign_key_onupdate_cascade(self, pg_conn, sqlalchemy_db_url): """Test that ON UPDATE CASCADE is applied to foreign keys.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) class Post(Base): __tablename__ = "posts" id = Column(Integer, primary_key=True) title = Column(String(200)) user_id = Column(Integer, ForeignKey("users.id", onupdate="CASCADE")) models_pool = {"users": User, "posts": Post} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # confupdtype: 'c' = CASCADE result = pg_conn.execute(""" SELECT confupdtype FROM pg_constraint WHERE conrelid = 'posts'::regclass AND contype = 'f' """).fetchone() assert result is not None assert result[0] == "c" # CASCADE class TestDatabaseManagerJSONBDefaults: """Test JSONB column default formatting.""" def test_jsonb_column_with_dict_default(self, pg_conn, sqlalchemy_db_url): """Test creating JSONB column with dict default produces valid SQL.""" Base = declarative_base() class Settings(Base): __tablename__ = "settings" id = Column(Integer, primary_key=True) config = Column(JSONB, default={}) models_pool = {"settings": Settings} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Verify column exists and default is valid col_default = pg_conn.execute(""" SELECT column_default FROM information_schema.columns WHERE table_name='settings' AND column_name='config' """).fetchone()[0] assert col_default is not None assert "jsonb" in col_default.lower() or "{}" in col_default def test_jsonb_column_with_list_default(self, pg_conn, sqlalchemy_db_url): """Test creating JSONB column with list default produces valid SQL.""" Base = declarative_base() class Tags(Base): __tablename__ = "tags" id = Column(Integer, primary_key=True) values = Column(JSONB, default=[]) models_pool = {"tags": Tags} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) col_default = pg_conn.execute(""" SELECT column_default FROM information_schema.columns WHERE table_name='tags' AND column_name='values' """).fetchone()[0] assert col_default is not None assert "jsonb" in col_default.lower() or "[]" in col_default def test_jsonb_column_with_nested_dict_default(self, pg_conn, sqlalchemy_db_url): """Test JSONB column with nested dict default.""" Base = declarative_base() class Preferences(Base): __tablename__ = "preferences" id = Column(Integer, primary_key=True) data = Column(JSONB, default={"theme": "dark", "notifications": True}) models_pool = {"preferences": Preferences} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Insert a row using the default and verify it's valid JSON pg_conn.execute("INSERT INTO preferences (id) VALUES (1)") result = pg_conn.execute("SELECT data FROM preferences WHERE id = 1").fetchone() # The default should have been applied as valid JSONB col_info = pg_conn.execute(""" SELECT data_type FROM information_schema.columns WHERE table_name='preferences' AND column_name='data' """).fetchone()[0] assert col_info == "jsonb" class TestDatabaseManagerSafeVarcharChanges: """Test safe VARCHAR/TEXT length changes without data loss.""" def test_varchar_length_increase_preserves_data(self, pg_conn, sqlalchemy_db_url): """Test that changing VARCHAR(100) to VARCHAR(255) preserves existing data.""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) name = Column(String(100)) models_pool = {"users": User} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Insert data pg_conn.execute("INSERT INTO users (id, name) VALUES (1, 'Alice Johnson')") pg_conn.commit() # Change VARCHAR length Base2 = declarative_base() class User2(Base2): __tablename__ = "users" id = Column(Integer, primary_key=True) name = Column(String(255)) models_pool2 = {"users": User2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Verify data is preserved result = pg_conn.execute("SELECT name FROM users WHERE id = 1").fetchone() assert result[0] == "Alice Johnson" # Verify new type col_info = pg_conn.execute(""" SELECT character_maximum_length FROM information_schema.columns WHERE table_name='users' AND column_name='name' """).fetchone() assert col_info[0] == 255 def test_varchar_length_decrease_preserves_data(self, pg_conn, sqlalchemy_db_url): """Test that decreasing VARCHAR length preserves data (if it fits).""" Base = declarative_base() class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) name = Column(String(255)) models_pool = {"users": User} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) # Insert short data that will fit in smaller column pg_conn.execute("INSERT INTO users (id, name) VALUES (1, 'Bob')") pg_conn.commit() # Decrease VARCHAR length Base2 = declarative_base() class User2(Base2): __tablename__ = "users" id = Column(Integer, primary_key=True) name = Column(String(100)) models_pool2 = {"users": User2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) # Verify data is preserved result = pg_conn.execute("SELECT name FROM users WHERE id = 1").fetchone() assert result[0] == "Bob" def test_text_to_varchar_preserves_data(self, pg_conn, sqlalchemy_db_url): """Test that changing TEXT to VARCHAR preserves data.""" Base = declarative_base() class Article(Base): __tablename__ = "articles" id = Column(Integer, primary_key=True) content = Column(Text) models_pool = {"articles": Article} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) pg_conn.execute("INSERT INTO articles (id, content) VALUES (1, 'Hello world')") pg_conn.commit() # Change to VARCHAR Base2 = declarative_base() class Article2(Base2): __tablename__ = "articles" id = Column(Integer, primary_key=True) content = Column(String(500)) models_pool2 = {"articles": Article2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) result = pg_conn.execute("SELECT content FROM articles WHERE id = 1").fetchone() assert result[0] == "Hello world" def test_varchar_to_text_preserves_data(self, pg_conn, sqlalchemy_db_url): """Test that changing VARCHAR to TEXT preserves data.""" Base = declarative_base() class Article(Base): __tablename__ = "articles" id = Column(Integer, primary_key=True) content = Column(String(200)) models_pool = {"articles": Article} DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool=models_pool, ) pg_conn.execute( "INSERT INTO articles (id, content) VALUES (1, 'Some text here')" ) pg_conn.commit() # Change to TEXT Base2 = declarative_base() class Article2(Base2): __tablename__ = "articles" id = Column(Integer, primary_key=True) content = Column(Text) models_pool2 = {"articles": Article2} DatabaseManager( sqlalchemy_declarative_base=Base2, db_url=sqlalchemy_db_url, models_pool=models_pool2, ) result = pg_conn.execute("SELECT content FROM articles WHERE id = 1").fetchone() assert result[0] == "Some text here" class TestDatabaseManagerDeclaredUniqueConstraints: """NB-B10 / NB-D4 — table-level ``UniqueConstraint``s declared in ``__table_args__`` must reach Postgres. New tables are built with ``CREATE TABLE "x" ();`` plus one ``ADD COLUMN`` per column, so before the fix only column-level ``unique=True`` was ever materialised and every composite constraint in a consumer app was decorative. """ def _unique_constraints(self, pg_conn, table_name): return pg_conn.execute(f""" SELECT tc.constraint_name, string_agg(kcu.column_name, ',' ORDER BY kcu.ordinal_position) AS cols FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name WHERE tc.table_name = '{table_name}' AND tc.constraint_type = 'UNIQUE' GROUP BY tc.constraint_name ORDER BY tc.constraint_name """).fetchall() def test_creates_declared_composite_unique_constraint_on_new_table( self, pg_conn, sqlalchemy_db_url ): Base = declarative_base() class VisitEquipment(Base): __tablename__ = "visit_equipment" id = Column(Integer, primary_key=True) visit_id = Column(Integer, nullable=False) equipment_id = Column(Integer, nullable=False) __table_args__ = ( UniqueConstraint("visit_id", "equipment_id", name="uq_visit_equipment"), ) DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool={"visit_equipment": VisitEquipment}, ) constraints = self._unique_constraints(pg_conn, "visit_equipment") assert ("uq_visit_equipment", "visit_id,equipment_id") in constraints # And the constraint actually bites. pg_conn.execute( "INSERT INTO visit_equipment (id, visit_id, equipment_id) VALUES (1, 1, 1)" ) pg_conn.commit() with pytest.raises(psycopg.errors.UniqueViolation): pg_conn.execute( "INSERT INTO visit_equipment (id, visit_id, equipment_id) " "VALUES (2, 1, 1)" ) pg_conn.rollback() def test_adds_declared_constraint_to_an_existing_table( self, pg_conn, sqlalchemy_db_url ): """The constraint is added on a later boot too, not only at creation.""" pg_conn.execute(""" CREATE TABLE visit_equipment ( id SERIAL PRIMARY KEY, visit_id INTEGER NOT NULL, equipment_id INTEGER NOT NULL ) """) pg_conn.commit() Base = declarative_base() class VisitEquipment(Base): __tablename__ = "visit_equipment" id = Column(Integer, primary_key=True) visit_id = Column(Integer, nullable=False) equipment_id = Column(Integer, nullable=False) __table_args__ = ( UniqueConstraint("visit_id", "equipment_id", name="uq_visit_equipment"), ) DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool={"visit_equipment": VisitEquipment}, ) names = {c[0] for c in self._unique_constraints(pg_conn, "visit_equipment")} assert "uq_visit_equipment" in names def test_is_idempotent_across_boots(self, pg_conn, sqlalchemy_db_url): def build(): Base = declarative_base() class VisitEquipment(Base): __tablename__ = "visit_equipment" id = Column(Integer, primary_key=True) visit_id = Column(Integer, nullable=False) equipment_id = Column(Integer, nullable=False) __table_args__ = ( UniqueConstraint( "visit_id", "equipment_id", name="uq_visit_equipment" ), ) DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool={"visit_equipment": VisitEquipment}, ) build() build() constraints = self._unique_constraints(pg_conn, "visit_equipment") assert len(constraints) == 1 def test_duplicate_rows_do_not_abort_the_migration( self, pg_conn, sqlalchemy_db_url ): """Existing rows that violate the declared constraint are logged and skipped — the rest of the schema sync still completes.""" pg_conn.execute(""" CREATE TABLE visit_equipment ( id SERIAL PRIMARY KEY, visit_id INTEGER NOT NULL, equipment_id INTEGER NOT NULL ) """) pg_conn.execute( "INSERT INTO visit_equipment (visit_id, equipment_id) " "VALUES (1, 1), (1, 1)" ) pg_conn.commit() Base = declarative_base() class VisitEquipment(Base): __tablename__ = "visit_equipment" id = Column(Integer, primary_key=True) visit_id = Column(Integer, nullable=False) equipment_id = Column(Integer, nullable=False) note = Column(String(50)) __table_args__ = ( UniqueConstraint("visit_id", "equipment_id", name="uq_visit_equipment"), ) DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool={"visit_equipment": VisitEquipment}, ) names = {c[0] for c in self._unique_constraints(pg_conn, "visit_equipment")} assert "uq_visit_equipment" not in names # The rest of the sync still ran. cols = {row[0] for row in pg_conn.execute(""" SELECT column_name FROM information_schema.columns WHERE table_name = 'visit_equipment' """).fetchall()} assert "note" in cols def test_column_level_unique_still_becomes_composite_on_tenant_tables( self, pg_conn, sqlalchemy_db_url ): """The new reconciler must not double up with the multi-tenant one.""" Base = declarative_base() class Menu(Base): __tablename__ = "menu" id = Column(Integer, primary_key=True) string_id = Column(String(255), unique=True) organization_id = Column(Integer, nullable=False) DatabaseManager( sqlalchemy_declarative_base=Base, db_url=sqlalchemy_db_url, models_pool={"menu": Menu}, ) constraints = self._unique_constraints(pg_conn, "menu") assert len(constraints) == 1 assert constraints[0][1] == "string_id,organization_id" class TestDatabaseManagerUniqueConstraintDiff: """The per-column ``unique=`` diff must agree with itself about which Postgres constraint a column flag *owns*: ``(col)`` on a plain table, ``(col, organization_id)`` on a multi-tenant one. Anything wider — declared in ``__table_args__`` or created by hand — is never owned by a flag, so it is neither dropped by ``unique=False`` nor treated as satisfying ``unique=True``. Regressions guarded here: dropping the composite when the flag is removed on a tenant table, and not crashing when a model spells the owned shape out again as a declared ``UniqueConstraint``. """ def _unique_constraints(self, pg_conn, table_name): return pg_conn.execute(f""" SELECT tc.constraint_name, string_agg(kcu.column_name, ',' ORDER BY kcu.ordinal_position) AS cols FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema WHERE tc.table_name = '{table_name}' AND tc.table_schema = current_schema() AND tc.constraint_type = 'UNIQUE' GROUP BY tc.constraint_name ORDER BY tc.constraint_name """).fetchall() @staticmethod def _boot(db_url, table_name, model_factory): Base = declarative_base() model = model_factory(Base) DatabaseManager( sqlalchemy_declarative_base=Base, db_url=db_url, models_pool={table_name: model}, ) # ------------------------------------------------------------------ plain def test_plain_table_new_unique_column_gets_exactly_one_constraint( self, pg_conn, sqlalchemy_db_url ): """``ADD COLUMN ... UNIQUE`` used to create an auto-named ``_key`` constraint next to the explicit ``_unique`` one — two indexes for one flag. Only the named constraint should exist now.""" def users(Base): class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), unique=True) return User self._boot(sqlalchemy_db_url, "users", users) constraints = self._unique_constraints(pg_conn, "users") assert constraints == [("users_email_unique", "email")] def test_plain_table_unique_flag_is_idempotent_across_boots( self, pg_conn, sqlalchemy_db_url ): def users(Base): class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), unique=True) return User for _ in range(3): self._boot(sqlalchemy_db_url, "users", users) constraints = self._unique_constraints(pg_conn, "users") assert constraints == [("users_email_unique", "email")] def test_plain_table_adding_flag_creates_exactly_one_constraint( self, pg_conn, sqlalchemy_db_url ): def plain(Base): class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) return User def unique(Base): class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), unique=True) return User self._boot(sqlalchemy_db_url, "users", plain) self._boot(sqlalchemy_db_url, "users", unique) # A third boot must recognise the constraint it just made. self._boot(sqlalchemy_db_url, "users", unique) constraints = self._unique_constraints(pg_conn, "users") assert constraints == [("users_email_unique", "email")] def test_plain_table_removing_flag_drops_every_owned_constraint( self, pg_conn, sqlalchemy_db_url ): """Databases built by older releases carry both the ``_key`` and the ``_unique`` constraint for one flag. Removing the flag drops both.""" pg_conn.execute(""" CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255), CONSTRAINT users_email_key UNIQUE (email), CONSTRAINT users_email_unique UNIQUE (email) ) """) pg_conn.commit() def plain(Base): class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), unique=False) return User self._boot(sqlalchemy_db_url, "users", plain) assert self._unique_constraints(pg_conn, "users") == [] def test_plain_table_flag_plus_declared_single_column_is_stable( self, pg_conn, sqlalchemy_db_url ): """``Column(unique=True)`` *and* ``UniqueConstraint("email")`` describe the same constraint. Repeated boots must neither crash on a duplicate name nor pile up extra constraints.""" def users(Base): class User(Base): __tablename__ = "users" __table_args__ = (UniqueConstraint("email", name="uq_users_email"),) id = Column(Integer, primary_key=True) email = Column(String(255), unique=True) return User for _ in range(3): self._boot(sqlalchemy_db_url, "users", users) constraints = self._unique_constraints(pg_conn, "users") assert len(constraints) == 1 assert constraints[0][1] == "email" def test_plain_table_handmade_composite_is_left_alone( self, pg_conn, sqlalchemy_db_url ): """A composite created outside the models whose *first* column is a non-unique model column used to be misread as that column's unique constraint and dropped on the next boot.""" pg_conn.execute(""" CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255), tenant VARCHAR(50), CONSTRAINT users_email_tenant_key UNIQUE (email, tenant) ) """) pg_conn.commit() def users(Base): class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255)) tenant = Column(String(50)) return User for _ in range(2): self._boot(sqlalchemy_db_url, "users", users) constraints = self._unique_constraints(pg_conn, "users") assert constraints == [("users_email_tenant_key", "email,tenant")] def test_plain_table_handmade_composite_does_not_satisfy_flag( self, pg_conn, sqlalchemy_db_url ): """``unique=True`` means globally unique on that column; a wider composite does not provide that, so the single-column constraint is still added — and the composite is kept.""" pg_conn.execute(""" CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255), tenant VARCHAR(50), CONSTRAINT users_email_tenant_key UNIQUE (email, tenant) ) """) pg_conn.commit() def users(Base): class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) email = Column(String(255), unique=True) tenant = Column(String(50)) return User for _ in range(2): self._boot(sqlalchemy_db_url, "users", users) constraints = self._unique_constraints(pg_conn, "users") assert constraints == [ ("users_email_tenant_key", "email,tenant"), ("users_email_unique", "email"), ] # ----------------------------------------------------------- multi-tenant def test_tenant_table_removing_flag_drops_composite( self, pg_conn, sqlalchemy_db_url ): """Regression: the diff flagged the composite as this column's constraint but the drop only matched ``(col)``, so the composite survived forever and kept rejecting per-org duplicates.""" def unique(Base): class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) organization_id = Column(Integer, nullable=False) email = Column(String(255), unique=True) return User def plain(Base): class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) organization_id = Column(Integer, nullable=False) email = Column(String(255), unique=False) return User self._boot(sqlalchemy_db_url, "users", unique) assert self._unique_constraints(pg_conn, "users") == [ ("users_email_organization_id_unique", "email,organization_id") ] self._boot(sqlalchemy_db_url, "users", plain) assert self._unique_constraints(pg_conn, "users") == [] # Behavioural check: the same email twice in one org is now allowed. pg_conn.execute( "INSERT INTO users (email, organization_id) VALUES ('a@x', 1), ('a@x', 1)" ) pg_conn.commit() def test_tenant_table_removing_flag_drops_legacy_single_and_composite( self, pg_conn, sqlalchemy_db_url ): pg_conn.execute(""" CREATE TABLE menu ( id SERIAL PRIMARY KEY, string_id VARCHAR(255), organization_id INTEGER NOT NULL, CONSTRAINT menu_string_id_key UNIQUE (string_id), CONSTRAINT menu_string_id_organization_id_unique UNIQUE (string_id, organization_id) ) """) pg_conn.commit() def menu(Base): class Menu(Base): __tablename__ = "menu" id = Column(Integer, primary_key=True) string_id = Column(String(255), unique=False) organization_id = Column(Integer, nullable=False) return Menu self._boot(sqlalchemy_db_url, "menu", menu) assert self._unique_constraints(pg_conn, "menu") == [] def test_tenant_table_adding_flag_creates_only_the_composite( self, pg_conn, sqlalchemy_db_url ): def plain(Base): class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) organization_id = Column(Integer, nullable=False) email = Column(String(255)) return User def unique(Base): class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) organization_id = Column(Integer, nullable=False) email = Column(String(255), unique=True) return User self._boot(sqlalchemy_db_url, "users", plain) self._boot(sqlalchemy_db_url, "users", unique) self._boot(sqlalchemy_db_url, "users", unique) assert self._unique_constraints(pg_conn, "users") == [ ("users_email_organization_id_unique", "email,organization_id") ] def test_tenant_table_flag_plus_declared_composite_is_stable( self, pg_conn, sqlalchemy_db_url ): """Regression: hiding the declared ``(string_id, organization_id)`` from the per-column diff made it believe the flag's constraint was missing, so the second boot re-added it under an existing name and the migration aborted with ``DuplicateTable``.""" def menu(Base): class Menu(Base): __tablename__ = "menu" __table_args__ = ( UniqueConstraint( "string_id", "organization_id", name="uq_menu_string_id_org" ), ) id = Column(Integer, primary_key=True) organization_id = Column(Integer, nullable=False) string_id = Column(String(255), unique=True) return Menu for _ in range(3): self._boot(sqlalchemy_db_url, "menu", menu) constraints = self._unique_constraints(pg_conn, "menu") assert len(constraints) == 1 assert constraints[0][1] == "string_id,organization_id" def test_tenant_table_flag_recognises_composite_under_declared_name( self, pg_conn, sqlalchemy_db_url ): """When the database already holds the composite under the declared name, the column flag must accept it rather than add a second one.""" pg_conn.execute(""" CREATE TABLE menu ( id SERIAL PRIMARY KEY, string_id VARCHAR(255), organization_id INTEGER NOT NULL, CONSTRAINT uq_menu_string_id_org UNIQUE (string_id, organization_id) ) """) pg_conn.commit() def menu(Base): class Menu(Base): __tablename__ = "menu" __table_args__ = ( UniqueConstraint( "string_id", "organization_id", name="uq_menu_string_id_org" ), ) id = Column(Integer, primary_key=True) organization_id = Column(Integer, nullable=False) string_id = Column(String(255), unique=True) return Menu for _ in range(2): self._boot(sqlalchemy_db_url, "menu", menu) assert self._unique_constraints(pg_conn, "menu") == [ ("uq_menu_string_id_org", "string_id,organization_id") ] def test_tenant_table_declared_composite_without_flag_survives_boots( self, pg_conn, sqlalchemy_db_url ): """Spelling the tenant composite out in ``__table_args__`` instead of using ``unique=True`` is valid and must not be flip-flopped: the column has no flag, so the diff must not see the composite as *its* constraint and drop it.""" def users(Base): class User(Base): __tablename__ = "users" __table_args__ = ( UniqueConstraint("email", "organization_id", name="uq_users_email"), ) id = Column(Integer, primary_key=True) organization_id = Column(Integer, nullable=False) email = Column(String(255)) return User for _ in range(3): self._boot(sqlalchemy_db_url, "users", users) assert self._unique_constraints(pg_conn, "users") == [ ("uq_users_email", "email,organization_id") ] def test_tenant_table_removing_flag_keeps_declared_composite( self, pg_conn, sqlalchemy_db_url ): """Flag and declared constraint both present, then the flag goes away while the declaration stays: the declared constraint must survive.""" def with_flag(Base): class User(Base): __tablename__ = "users" __table_args__ = ( UniqueConstraint("email", "organization_id", name="uq_users_email"), ) id = Column(Integer, primary_key=True) organization_id = Column(Integer, nullable=False) email = Column(String(255), unique=True) return User def declared_only(Base): class User(Base): __tablename__ = "users" __table_args__ = ( UniqueConstraint("email", "organization_id", name="uq_users_email"), ) id = Column(Integer, primary_key=True) organization_id = Column(Integer, nullable=False) email = Column(String(255)) return User self._boot(sqlalchemy_db_url, "users", with_flag) self._boot(sqlalchemy_db_url, "users", declared_only) self._boot(sqlalchemy_db_url, "users", declared_only) constraints = self._unique_constraints(pg_conn, "users") assert len(constraints) == 1 assert constraints[0][1] == "email,organization_id" def test_tenant_table_reversed_column_order_satisfies_flag( self, pg_conn, sqlalchemy_db_url ): """``UNIQUE (organization_id, email)`` is the same constraint as ``UNIQUE (email, organization_id)``; a hand-built one in the other order must not be duplicated.""" pg_conn.execute(""" CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255), organization_id INTEGER NOT NULL, CONSTRAINT users_org_email_key UNIQUE (organization_id, email) ) """) pg_conn.commit() def users(Base): class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) organization_id = Column(Integer, nullable=False) email = Column(String(255), unique=True) return User for _ in range(2): self._boot(sqlalchemy_db_url, "users", users) assert self._unique_constraints(pg_conn, "users") == [ ("users_org_email_key", "organization_id,email") ] def test_tenant_table_declared_wide_composite_survives_boots( self, pg_conn, sqlalchemy_db_url ): """A declared composite that does not involve ``organization_id`` on a tenant table. Its first column has no flag; the old first-column match would have flagged it as a stale unique and dropped it every other boot.""" def visit_equipment(Base): class VisitEquipment(Base): __tablename__ = "visit_equipment" __table_args__ = ( UniqueConstraint( "visit_id", "equipment_id", name="uq_visit_equipment" ), ) id = Column(Integer, primary_key=True) organization_id = Column(Integer, nullable=False) visit_id = Column(Integer, nullable=False) equipment_id = Column(Integer, nullable=False) return VisitEquipment for _ in range(3): self._boot(sqlalchemy_db_url, "visit_equipment", visit_equipment) assert self._unique_constraints(pg_conn, "visit_equipment") == [ ("uq_visit_equipment", "visit_id,equipment_id") ] def test_tenant_table_handmade_wide_composite_is_left_alone( self, pg_conn, sqlalchemy_db_url ): """Same as above but the composite exists only in the database.""" pg_conn.execute(""" CREATE TABLE visit_equipment ( id SERIAL PRIMARY KEY, organization_id INTEGER NOT NULL, visit_id INTEGER NOT NULL, equipment_id INTEGER NOT NULL, CONSTRAINT visit_equipment_handmade UNIQUE (visit_id, equipment_id) ) """) pg_conn.commit() def visit_equipment(Base): class VisitEquipment(Base): __tablename__ = "visit_equipment" id = Column(Integer, primary_key=True) organization_id = Column(Integer, nullable=False) visit_id = Column(Integer, nullable=False) equipment_id = Column(Integer, nullable=False) return VisitEquipment for _ in range(2): self._boot(sqlalchemy_db_url, "visit_equipment", visit_equipment) assert self._unique_constraints(pg_conn, "visit_equipment") == [ ("visit_equipment_handmade", "visit_id,equipment_id") ]
import pytest
import psycopg
from enum import Enum as PyEnum
from sqlalchemy import (
Column,
Integer,
String,
Boolean,
DateTime,
Float,
BigInteger,
Text,
ForeignKey,
Enum,
Index,
UniqueConstraint,
text,
)
from sqlalchemy.dialects.postgresql import JSONB, TSVECTOR
from sqlalchemy.orm import declarative_base, Session
from deepsel.sqlalchemy import DatabaseManager
class Status(PyEnum):
ACTIVE = "active"
INACTIVE = "inactive"
PENDING = "pending"
class Priority(PyEnum):
LOW = "low"
MEDIUM = "medium"
HIGH = "high"
class TestDatabaseManagerTableCreation:
"""Test table creation and basic schema operations."""
def test_creates_new_table_with_columns(self, pg_conn, sqlalchemy_db_url):
"""Test that DatabaseManager creates a new table with specified columns."""
Base = declarative_base()
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
email = Column(String(255), nullable=False, unique=True)
name = Column(String(100))
models_pool = {"users": User}
DatabaseManager(
sqlalchemy_declarative_base=Base,
db_url=sqlalchemy_db_url,
models_pool=models_pool,
)
# Assert table exists
result = pg_conn.execute("SELECT to_regclass('users') IS NOT NULL").fetchone()
assert result[0] is True
# Assert columns exist with correct types
cols = {row[0]: (row[1], row[2]) for row in pg_conn.execute("""
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name='users'
ORDER BY ordinal_position
""").fetchall()}
assert "id" in cols
assert cols["id"] == ("integer", "NO")
assert "email" in cols
assert cols["email"] == ("character varying", "NO")
assert "name" in cols
assert cols["name"] == ("character varying", "YES")
# Assert primary key exists
pk = pg_conn.execute("""
SELECT constraint_name
FROM information_schema.table_constraints
WHERE table_name='users' AND constraint_type='PRIMARY KEY'
""").fetchone()
assert pk is not None
# Assert unique constraint exists on email
unique_constraints = pg_conn.execute("""
SELECT i.relname
FROM pg_index x
JOIN pg_class t ON t.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
WHERE t.relname = 'users' AND x.indisunique
""").fetchall()
assert len(unique_constraints) >= 1
def test_creates_table_with_bigint_primary_key(self, pg_conn, sqlalchemy_db_url):
"""Test creating table with BigInteger primary key (BIGSERIAL)."""
Base = declarative_base()
class Article(Base):
__tablename__ = "articles"
id = Column(BigInteger, primary_key=True)
title = Column(String(200), nullable=False)

Xet Storage Details

Size:
90 kB
·
Xet hash:
2a25af3802c7ddbea80ca9a02ae86b791263e67683f3e291f1c550cdd9e7ffd9

Xet efficiently stores files, intelligently splitting them into unique chunks and accelerating uploads and downloads. More info.