Зачем нужен DDL в БД?

Зачем нужен DDL в БД?

DDL (Data Definition Language) — это язык определения структуры базы данных. Это фундаментальная часть системы управления базами данных (СУБД), которая отвечает за создание, изменение и удаление объектов базы данных.

1. Что такое DDL

DDL включает SQL-команды, которые определяют структуру и схему данных:

-- CREATE — создание новых объектов
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

-- ALTER — изменение структуры
ALTER TABLE users ADD COLUMN age INT;

-- DROP — удаление объектов
DROP TABLE users;

2. Основные DDL команды

-- CREATE TABLE — создание таблицы
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- CREATE INDEX — создание индекса
CREATE INDEX idx_email ON users(email);

-- CREATE VIEW — создание представления
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

-- ALTER TABLE — изменение таблицы
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME TO customers;

-- DROP TABLE — удаление таблицы
DROP TABLE users;

-- TRUNCATE — очистка данных
TRUNCATE TABLE users;

3. Отличие от DML и DCL

# DDL (Definition) — структура
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100));
ALTER TABLE products ADD COLUMN price DECIMAL(10, 2);
DROP TABLE products;

# DML (Manipulation) — данные
INSERT INTO products (id, name, price) VALUES (1, 'Book', 29.99);
UPDATE products SET price = 34.99 WHERE id = 1;
DELETE FROM products WHERE id = 1;
SELECT * FROM products;

# DCL (Control) — доступ
GRANT SELECT ON products TO user1;
REVOKE DELETE ON products FROM user1;

4. Почему DDL нужен

1. Определение структуры данных

-- Без DDL мы не можем определить, что такое таблица
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id)
);

2. Управление отношениями между таблицами

-- Первичные ключи (Primary Key)
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(100)
);

-- Внешние ключи (Foreign Key) — связь между таблицами
CREATE TABLE posts (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Уникальные ограничения
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50) UNIQUE
);

3. Оптимизация через индексы

-- Индексы ускоряют поиск
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_post_user ON posts(user_id);
CREATE INDEX idx_created_date ON posts(created_at DESC);

-- Составной индекс
CREATE INDEX idx_user_activity ON users(id, last_login);

4. Обеспечение целостности данных

-- NOT NULL — поле обязательно
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL
);

-- CHECK — ограничение значений
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    CHECK (price > 0)
);

-- DEFAULT — значение по умолчанию
CREATE TABLE logs (
    id INT PRIMARY KEY,
    message VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

5. Пример реальной схемы БД

-- Таблица пользователей
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица постов
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица комментариев
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    text TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица тегов
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

-- Связь много-ко-многим
CREATE TABLE post_tags (
    post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

-- Индексы для оптимизации
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_user_id ON comments(user_id);
CREATE INDEX idx_tags_name ON tags(name);

6. DDL в ORM (Django/SQLAlchemy)

# Django ORM — DDL генерируется автоматически
from django.db import models

class User(models.Model):
    username = models.CharField(max_length=100, unique=True)
    email = models.EmailField(unique=True)
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        indexes = [
            models.Index(fields=['email']),
        ]

# Под капотом Django генерирует SQL DDL
# python manage.py makemigrations — создаёт миграции
# python manage.py migrate — применяет DDL к БД

# SQLAlchemy
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, create_engine
from sqlalchemy.orm import declarative_base, relationship
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    username = Column(String(100), unique=True, nullable=False)
    email = Column(String(100), unique=True, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

class Post(Base):
    __tablename__ = 'posts'
    
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    title = Column(String(200), nullable=False)
    content = Column(String)
    user = relationship('User', back_populates='posts')

# Создание всех таблиц
engine = create_engine('postgresql://localhost/mydb')
Base.metadata.create_all(engine)

7. Миграции и версионирование DDL

# Alembic (миграции для SQLAlchemy)
# Goose (миграции для raw SQL)

# Пример миграции Alembic
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('username', sa.String(100), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('username')
    )
    op.create_index('idx_users_username', 'users', ['username'])

def downgrade():
    op.drop_index('idx_users_username', 'users')
    op.drop_table('users')

8. Важность DDL

DDL критичен для:

• Определения того, как организованы данные
• Обеспечения целостности данных
• Оптимизации производительности (индексы)
• Управления связями между таблицами
• Версионирования структуры БД (миграции)

9. Вывод

DDL — это фундамент базы данных. Без него:

• Невозможно создать таблицы
• Нет целостности данных
• Нет отношений между таблицами
• Невозможно оптимизировать запросы

DDL — это контракт, который определяет, какие данные можно хранить и как они организованы.