PostgreSQL: نظام إضافات PostgreSQL وFDW والبحث المتجهي

-- سرد الإضافات المتاحة
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY name;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- توليد UUID v4 (عشوائي)
SELECT uuid_generate_v4();

-- توليد UUID v1 (معتمد على الوقت)
SELECT uuid_generate_v1();

-- استخدام كقيمة افتراضية لعمود
CREATE TABLE api_keys (
    id          UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    key_name    TEXT,
    created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- تجزئة كلمة المرور مع ملح
SELECT crypt('MySecret123', gen_salt('bf'));

-- التحقق من كلمة المرور
SELECT crypt('MySecret123', stored_hash) = stored_hash AS is_match;

-- تشفير AES
SELECT encode(encrypt('credit card data'::bytea,
    'secret_key_16bytes'::bytea, 'aes'), 'hex');

-- توليد رمز عشوائي
SELECT encode(gen_random_bytes(32), 'hex');

CREATE TABLE

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- عرض درجة التشابه
SELECT similarity('PostgreSQL', 'Postgres');

-- فهرس GIN trigram للبحث التقريبي السريع
CREATE INDEX idx_products_name_trgm ON products
    USING GIN (name gin_trgm_ops);

-- بحث تقريبي مع عتبة
SELECT name, similarity(name, 'iphon') AS score
FROM products
WHERE name % 'iphon'
ORDER BY score DESC;

     name      | score
---------------+-------
 iPhone 15 Pro |  0.42
 iPhone 14     |  0.38
(2 rows)

-- يجب أن يكون في shared_preload_libraries أولاً
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- أفضل 10 استعلامات حسب وقت التنفيذ الإجمالي
SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- إعادة تعيين الإحصائيات
SELECT pg_stat_statements_reset();

  result  
----------
   42.50
(1 row)

CREATE EXTENSION IF NOT EXISTS postgis;

-- تخزين هندسة النقطة
CREATE TABLE stores (
    id       SERIAL PRIMARY KEY,
    name     TEXT,
    location GEOMETRY(POINT, 4326)
);

-- إدراج إحداثيات (خط طول، خط عرض)
INSERT INTO stores (name, location)
VALUES ('Dubai Mall', ST_SetSRID(ST_MakePoint(55.2796, 25.1972), 4326));

-- العثور على متاجر ضمن 5 كم
SELECT name,
    ST_Distance(location::geography,
        ST_SetSRID(ST_MakePoint(55.2700, 25.2000), 4326)::geography
    ) AS distance_m
FROM stores
WHERE ST_DWithin(location::geography,
    ST_SetSRID(ST_MakePoint(55.2700, 25.2000), 4326)::geography, 5000);

INSERT 0 1

-- الخطوة 1: تثبيت الإضافة
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- الخطوة 2: إنشاء اتصال الخادم
CREATE SERVER legacy_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.1.100', port '5432', dbname 'legacy');

-- الخطوة 3: ربط المستخدم المحلي ببيانات اعتماد بعيدة
CREATE USER MAPPING FOR current_user
    SERVER legacy_db
    OPTIONS (user 'admin', password 'secret123');

-- الخطوة 4: استيراد المخطط الخارجي
IMPORT FOREIGN SCHEMA public
    LIMIT TO (users, products, orders)
    FROM SERVER legacy_db
    INTO legacy_schema;

-- الآن استعلم عن الجداول البعيدة كما لو كانت محلية
SELECT u.name, COUNT(o.id) AS order_count
FROM legacy_schema.users u
JOIN legacy_schema.orders o ON u.id = o.user_id
GROUP BY u.name;

 count 
-------
     5
(1 row)

-- محلي: كتالوج منتجات PG الجديد
-- بعيد: بيانات طلبات MySQL القديمة عبر postgres_fdw

SELECT p.name,
       SUM(loi.quantity) AS total_sold,
       SUM(loi.quantity * loi.unit_price) AS revenue
FROM products p
JOIN legacy_schema.order_items loi ON p.id = loi.product_id
WHERE p.category = 'Electronics'
GROUP BY p.name
ORDER BY revenue DESC;

  result  
----------
   42.50
(1 row)

CREATE EXTENSION IF NOT EXISTS file_fdw;

CREATE SERVER csv_server
    FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE access_logs_csv (
    ip_address    TEXT,
    request_time  TIMESTAMP,
    method        TEXT,
    path          TEXT,
    status_code   INT,
    response_time NUMERIC
) SERVER csv_server
OPTIONS (filename '/var/log/nginx/access.csv', format 'csv', header 'true');

-- تحليل سجلات nginx بـ SQL
SELECT path,
       COUNT(*) AS hits,
       AVG(response_time) AS avg_ms
FROM access_logs_csv
WHERE status_code = 200
GROUP BY path
ORDER BY hits DESC
LIMIT 20;

 count 
-------
     5
(1 row)

-- ترحيل المستخدمين من القديم إلى المحلي
INSERT INTO users (name, email, created_at)
SELECT name, email, created_at
FROM legacy_schema.users
WHERE id NOT IN (SELECT legacy_id FROM users);

-- استخدام dblink للمزامنة التدريجية
CREATE EXTENSION IF NOT EXISTS dblink;

SELECT dblink_connect('legacy', 'host=192.168.1.100 dbname=legacy user=admin password=secret123');

SELECT * FROM dblink('legacy',
    'SELECT id, name, email FROM users WHERE created_at > now() - interval ''1 day'''
) AS t(id BIGINT, name TEXT, email TEXT);

INSERT 0 1

-- تثبيت pgvector
CREATE EXTENSION IF NOT EXISTS vector;

-- جدول منتجات مع متجه تضمين (1536 بعدًا لـ OpenAI)
CREATE TABLE products_vec (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT,
    price       NUMERIC(10,2),
    embedding   vector(1536)
);

CREATE TABLE

-- إدراج منتج مع تضمين
INSERT INTO products_vec (name, category, price, embedding)
VALUES ('Wireless Headphones', 'Electronics', 79.99,
    '[0.012, -0.034, 0.056, ...]'::vector);

-- العثور على أفضل 5 منتجات متشابهة بمسافة جيب التمام
SELECT p.id, p.name, p.category, p.price,
       p.embedding <=> '[0.015, -0.030, 0.050, ...]'::vector AS distance
FROM products_vec p
ORDER BY p.embedding <=> '[0.015, -0.030, 0.050, ...]'::vector
LIMIT 5;

INSERT 0 1

-- فهرس HNSW للبحث التقريبي السريع
CREATE INDEX idx_products_vec_hnsw ON products_vec
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- ضبط دقة البحث مقابل السرعة
SET hnsw.ef_search = 100;

-- استعلام مع الفهرس (أسرع بكثير على مجموعات البيانات الكبيرة)
SELECT name,
       embedding <=> '[0.015, -0.030, 0.050, ...]'::vector AS distance
FROM products_vec
ORDER BY embedding <=> '[0.015, -0.030, 0.050, ...]'::vector
LIMIT 10;

CREATE TABLE

-- تخزين تضمينات المنتجات من نموذج الذكاء الاصطناعي
INSERT INTO products_vec (name, category, price, embedding)
VALUES
    ('Running Shoes',     'Sports',   129.99, array_to_vec(ARRAY[0.1,0.2,0.3])::vector),
    ('Yoga Mat',          'Sports',    39.99, array_to_vec(ARRAY[0.11,0.19,0.31])::vector),
    ('Bluetooth Speaker', 'Electronics', 49.99, array_to_vec(ARRAY[0.5,0.1,0.2])::vector);

-- شاهد المستخدم "Running Shoes"، أوصِ بعناصر مشابهة
WITH target AS (
    SELECT embedding FROM products_vec WHERE name = 'Running Shoes'
)
SELECT p.name, p.category, p.price,
       p.embedding <=> (SELECT embedding FROM target) AS similarity
FROM products_vec p
WHERE p.name != 'Running Shoes'
ORDER BY similarity
LIMIT 3;

INSERT 0 1

-- التحقق من إصدار الإضافة الحالي
SELECT extname, extversion FROM pg_extension ORDER BY extname;

-- ترقية إضافة
ALTER EXTENSION pgvector UPDATE TO '0.7.0';

-- ترقية جميع الإضافات
SELECT extname,
       installed_version,
       default_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
  AND installed_version != default_version;

-- إعداد كامل: إضافات + FDW + pgvector للتجارة الإلكترونية بالذكاء الاصطناعي

-- الخطوة 1: تثبيت الإضافات الأساسية
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pgvector;

-- الخطوة 2: كتالوج منتجات مع بحث متجهي
CREATE TABLE products_ai (
    id          UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT,
    price       NUMERIC(10,2),
    attrs       JSONB DEFAULT '{}',
    embedding   vector(1536)
);

-- الخطوة 3: فهرس GIN للبحث التقريبي بالاسم
CREATE INDEX idx_products_name_trgm ON products_ai
    USING GIN (name gin_trgm_ops);

-- الخطوة 4: فهرس HNSW لتشابه المتجهات
CREATE INDEX idx_products_embedding ON products_ai
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- الخطوة 5: الاتصال بقاعدة البيانات القديمة عبر FDW
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER legacy_db FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '192.168.1.50', port '5432', dbname 'legacy_shop');
CREATE USER MAPPING FOR current_user SERVER legacy_db
    OPTIONS (user 'migrate_user', password 'secure_pass');
IMPORT FOREIGN SCHEMA public LIMIT TO (old_products, old_users)
    FROM SERVER legacy_db INTO legacy;

-- الخطوة 6: ترحيل وتحويل البيانات
INSERT INTO products_ai (name, category, price, attrs)
SELECT name, category, price,
       jsonb_build_object('weight_kg', weight, 'color', color)
FROM legacy.old_products
WHERE active = true;

-- الخطوة 7: بحث مدمج تقريبي + متجهي
SELECT p.name, p.price,
       similarity(p.name, 'wireless earbuds') AS text_score,
       p.embedding <=> '[0.015,-0.030,0.050]'::vector AS vec_dist
FROM products_ai p
WHERE p.name % 'wireless earbuds'
ORDER BY vec_dist ASC
LIMIT 5;