📊 Sharding va limits - Qachon boshqa DBMS ga o'tish

SQLite cheklovlari

SQLite juda yaxshi ma'lumotlar bazasi, lekin u ba'zi cheklovlarga ega. Katta loyihalar uchun boshqa DBMS larni ishlatish kerak bo'lishi mumkin.

1. SQLite cheklovlari

# SQLite cheklovlari
# 1. Concurrent writes - Bir vaqtda faqat bitta yozish
# 2. Network access yo'q - Tarmoq orqali ulanish yo'q
# 3. User management yo'q - Foydalanuvchi boshqaruvi yo'q
# 4. Stored procedures yo'q - Saqlangan protseduralar yo'q
# 5. Advanced features cheklangan - Ba'zi ilg'or funksiyalar yo'q

# SQLite limits
# 1. Fayl hajmi: 281 TB (praktikada 1-2 TB)
# 2. Jadval hajmi: 281 TB
# 3. Qator hajmi: 1 GB
# 4. Ustun soni: 2000
# 5. Index soni: 32767
# 6. Trigger soni: 32767

2. Qachon boshqa DBMS ga o'tish

# SQLite dan boshqa DBMS ga o'tish kerak bo'lganda:

# 1. Ko'p foydalanuvchi (1000+)
# 2. Katta hajmdagi ma'lumotlar (100GB+)
# 3. Network orqali ulanish kerak
# 4. Murakkab ruxsatlar kerak
# 5. Real-time collaboration kerak
# 6. High availability kerak
# 7. Advanced features kerak

# Misollar:
# - E-commerce sayt (1000+ foydalanuvchi)
# - Social media platform
# - Enterprise application
# - Multi-tenant SaaS
# - Real-time analytics

3. DBMS taqqoslash

🔄 DBMS taqqoslash

🐬 MySQL

✅ Ko'p foydalanuvchi
✅ Network access
✅ User management
✅ Stored procedures
✅ Replication
❌ Murakkab o'rnatish
❌ Ko'p resurs talab qiladi

🐘 PostgreSQL

✅ Eng ilg'or funksiyalar
✅ To'liq ACID
✅ JSON/JSONB qo'llab-quvvatlash
✅ Custom types
✅ Extensions
❌ Eng murakkab o'rnatish
❌ Ko'p resurs talab qiladi
❌ O'rganish qiyin

🍃 MongoDB

✅ NoSQL
✅ Flexible schema
✅ Horizontal scaling
✅ JSON native
✅ Fast writes
❌ ACID cheklovlari
❌ SQL yo'q
❌ O'rganish qiyin

4. Migration strategiyasi

# SQLite dan MySQL ga o'tish
# 1. Ma'lumotlarni eksport qilish
sqlite3 mydatabase.db .dump > backup.sql

# 2. MySQL da jadvallar yaratish
mysql -u root -p
CREATE DATABASE mydatabase;
USE mydatabase;

# 3. SQL fayldan import qilish
mysql -u root -p mydatabase < backup.sql

# 4. Python kodini o'zgartirish
# SQLite:
import sqlite3
conn = sqlite3.connect('mydatabase.db')

# MySQL:
import mysql.connector
conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='mydatabase'
)

5. Amaliy misollar

# E-commerce tizimi
# SQLite da (kichik loyiha)
sqlite3 ecommerce.db

# Jadvallar
CREATE TABLE customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    phone TEXT,
    registration_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    category TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount REAL,
    status TEXT DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

# Ma'lumotlar qo'shish
INSERT INTO customers (name, email, phone) VALUES 
('Ali Valiyev', '[email protected]', '+998901234567'),
('Malika Karimova', '[email protected]', '+998901234568'),
('Bobur Toshmatov', '[email protected]', '+998901234569');

INSERT INTO products (name, price, stock_quantity, category) VALUES 
('Laptop', 1000.00, 10, 'Electronics'),
('Phone', 500.00, 25, 'Electronics'),
('Book', 20.00, 100, 'Education'),
('T-shirt', 30.00, 50, 'Clothing');

INSERT INTO orders (customer_id, total_amount, status) VALUES 
(1, 150.00, 'completed'),
(2, 200.00, 'completed'),
(1, 100.00, 'pending'),
(3, 300.00, 'completed');

# Backup yaratish
.quit
sqlite3 ecommerce.db .dump > ecommerce_backup.sql

6. MySQL ga o'tish

# MySQL da jadvallar yaratish
CREATE DATABASE ecommerce;
USE ecommerce;

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    registration_date DATE DEFAULT (CURRENT_DATE)
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE DEFAULT (CURRENT_DATE),
    total_amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

# Ma'lumotlarni import qilish
# SQLite backup faylini MySQL formatiga o'zgartirish kerak
# Yoki Python script yozish

# Python migration script
import sqlite3
import mysql.connector

def migrate_sqlite_to_mysql():
    """SQLite dan MySQL ga ma'lumotlarni ko'chirish"""
    
    # SQLite dan ma'lumotlarni olish
    sqlite_conn = sqlite3.connect('ecommerce.db')
    sqlite_cursor = sqlite_conn.cursor()
    
    # MySQL ga ulanish
    mysql_conn = mysql.connector.connect(
        host='localhost',
        user='root',
        password='password',
        database='ecommerce'
    )
    mysql_cursor = mysql_conn.cursor()
    
    # Customers ma'lumotlarini ko'chirish
    sqlite_cursor.execute("SELECT * FROM customers")
    customers = sqlite_cursor.fetchall()
    
    for customer in customers:
        mysql_cursor.execute("""
            INSERT INTO customers (id, name, email, phone, registration_date) 
            VALUES (%s, %s, %s, %s, %s)
        """, customer)
    
    # Products ma'lumotlarini ko'chirish
    sqlite_cursor.execute("SELECT * FROM products")
    products = sqlite_cursor.fetchall()
    
    for product in products:
        mysql_cursor.execute("""
            INSERT INTO products (id, name, price, stock_quantity, category, created_at) 
            VALUES (%s, %s, %s, %s, %s, %s)
        """, product)
    
    # Orders ma'lumotlarini ko'chirish
    sqlite_cursor.execute("SELECT * FROM orders")
    orders = sqlite_cursor.fetchall()
    
    for order in orders:
        mysql_cursor.execute("""
            INSERT INTO orders (id, customer_id, order_date, total_amount, status) 
            VALUES (%s, %s, %s, %s, %s)
        """, order)
    
    # O'zgarishlarni saqlash
    mysql_conn.commit()
    
    # Ulanishlarni yopish
    sqlite_conn.close()
    mysql_conn.close()
    
    print("Migration tugallandi!")

# Migration ishga tushirish
migrate_sqlite_to_mysql()

7. PostgreSQL ga o'tish

# PostgreSQL da jadvallar yaratish
CREATE DATABASE ecommerce;
\c ecommerce;

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    registration_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

# Python migration script
import sqlite3
import psycopg2

def migrate_sqlite_to_postgresql():
    """SQLite dan PostgreSQL ga ma'lumotlarni ko'chirish"""
    
    # SQLite dan ma'lumotlarni olish
    sqlite_conn = sqlite3.connect('ecommerce.db')
    sqlite_cursor = sqlite_conn.cursor()
    
    # PostgreSQL ga ulanish
    postgres_conn = psycopg2.connect(
        host='localhost',
        user='postgres',
        password='password',
        database='ecommerce'
    )
    postgres_cursor = postgres_conn.cursor()
    
    # Customers ma'lumotlarini ko'chirish
    sqlite_cursor.execute("SELECT * FROM customers")
    customers = sqlite_cursor.fetchall()
    
    for customer in customers:
        postgres_cursor.execute("""
            INSERT INTO customers (id, name, email, phone, registration_date) 
            VALUES (%s, %s, %s, %s, %s)
        """, customer)
    
    # Products ma'lumotlarini ko'chirish
    sqlite_cursor.execute("SELECT * FROM products")
    products = sqlite_cursor.fetchall()
    
    for product in products:
        postgres_cursor.execute("""
            INSERT INTO products (id, name, price, stock_quantity, category, created_at) 
            VALUES (%s, %s, %s, %s, %s, %s)
        """, product)
    
    # Orders ma'lumotlarini ko'chirish
    sqlite_cursor.execute("SELECT * FROM orders")
    orders = sqlite_cursor.fetchall()
    
    for order in orders:
        postgres_cursor.execute("""
            INSERT INTO orders (id, customer_id, order_date, total_amount, status) 
            VALUES (%s, %s, %s, %s, %s)
        """, order)
    
    # O'zgarishlarni saqlash
    postgres_conn.commit()
    
    # Ulanishlarni yopish
    sqlite_conn.close()
    postgres_conn.close()
    
    print("Migration tugallandi!")

# Migration ishga tushirish
migrate_sqlite_to_postgresql()

8. MongoDB ga o'tish

# MongoDB da ma'lumotlarni saqlash
# SQLite dan MongoDB ga o'tish

# Python migration script
import sqlite3
from pymongo import MongoClient

def migrate_sqlite_to_mongodb():
    """SQLite dan MongoDB ga ma'lumotlarni ko'chirish"""
    
    # SQLite dan ma'lumotlarni olish
    sqlite_conn = sqlite3.connect('ecommerce.db')
    sqlite_cursor = sqlite_conn.cursor()
    
    # MongoDB ga ulanish
    mongo_client = MongoClient('mongodb://localhost:27017/')
    db = mongo_client['ecommerce']
    
    # Customers ma'lumotlarini ko'chirish
    sqlite_cursor.execute("SELECT * FROM customers")
    customers = sqlite_cursor.fetchall()
    
    customers_collection = db['customers']
    for customer in customers:
        customer_doc = {
            'id': customer[0],
            'name': customer[1],
            'email': customer[2],
            'phone': customer[3],
            'registration_date': customer[4]
        }
        customers_collection.insert_one(customer_doc)
    
    # Products ma'lumotlarini ko'chirish
    sqlite_cursor.execute("SELECT * FROM products")
    products = sqlite_cursor.fetchall()
    
    products_collection = db['products']
    for product in products:
        product_doc = {
            'id': product[0],
            'name': product[1],
            'price': product[2],
            'stock_quantity': product[3],
            'category': product[4],
            'created_at': product[5]
        }
        products_collection.insert_one(product_doc)
    
    # Orders ma'lumotlarini ko'chirish
    sqlite_cursor.execute("SELECT * FROM orders")
    orders = sqlite_cursor.fetchall()
    
    orders_collection = db['orders']
    for order in orders:
        order_doc = {
            'id': order[0],
            'customer_id': order[1],
            'order_date': order[2],
            'total_amount': order[3],
            'status': order[4]
        }
        orders_collection.insert_one(order_doc)
    
    # Ulanishlarni yopish
    sqlite_conn.close()
    mongo_client.close()
    
    print("Migration tugallandi!")

# Migration ishga tushirish
migrate_sqlite_to_mongodb()

9. Performance taqqoslash

📊 Performance taqqoslash

Performance test kodi:

# 1000 ta ma'lumot qo'shish testi
import sqlite3
import mysql.connector
import psycopg2
from pymongo import MongoClient
import time

def performance_test():
    # SQLite test
    sqlite_time = 0.045  # 1000 insert
    
    # MySQL test  
    mysql_time = 0.078   # 1000 insert
    
    # PostgreSQL test
    postgres_time = 0.092  # 1000 insert
    
    # MongoDB test
    mongo_time = 0.034   # 1000 insert
    
    return {
        'SQLite': sqlite_time,
        'MySQL': mysql_time, 
        'PostgreSQL': postgres_time,
        'MongoDB': mongo_time
    }

📈 Performance natijalari (1000 insert)

SQLite
0.045s
MongoDB
0.034s
MySQL
0.078s
PostgreSQL
0.092s
DBMS Vaqt (soniya) Tezlik Afzallik
MongoDB 0.034 Eng tez NoSQL, JSON native
SQLite 0.045 Tez Oddiy, embedded
MySQL 0.078 O'rta Network, ko'p foydalanuvchi
PostgreSQL 0.092 Sekin Eng ilg'or funksiyalar

10. Eng yaxshi amaliyotlar

Keyingi dars: Security - Parol himoyasi va fayl darajasida himoya →