63 lines
2.2 KiB
PL/PgSQL
63 lines
2.2 KiB
PL/PgSQL
-- Database initialization script for proxy scraping service
|
|
-- This script creates the proxies table and related indexes
|
|
|
|
-- Create proxies table
|
|
CREATE TABLE IF NOT EXISTS proxies (
|
|
id SERIAL PRIMARY KEY,
|
|
ip_address INET NOT NULL,
|
|
port INTEGER NOT NULL CHECK (port > 0 AND port <= 65535),
|
|
protocol VARCHAR(10) NOT NULL CHECK (protocol IN ('HTTP', 'HTTPS', 'SOCKS4', 'SOCKS5')),
|
|
username VARCHAR(255),
|
|
password VARCHAR(255),
|
|
country_code CHAR(2),
|
|
country_name VARCHAR(100),
|
|
city VARCHAR(100),
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
is_anonymous BOOLEAN DEFAULT FALSE,
|
|
response_time_ms INTEGER,
|
|
last_checked_at TIMESTAMP WITH TIME ZONE,
|
|
last_successful_at TIMESTAMP WITH TIME ZONE,
|
|
failure_count INTEGER DEFAULT 0,
|
|
success_count INTEGER DEFAULT 0,
|
|
usage INTEGER DEFAULT 0,
|
|
source VARCHAR(255),
|
|
notes TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT unique_proxy UNIQUE (ip_address, port, protocol)
|
|
);
|
|
|
|
-- Create indexes for better query performance
|
|
CREATE INDEX IF NOT EXISTS idx_active_protocol ON proxies(is_active, protocol);
|
|
CREATE INDEX IF NOT EXISTS idx_country ON proxies(country_code);
|
|
CREATE INDEX IF NOT EXISTS idx_last_checked ON proxies(last_checked_at);
|
|
CREATE INDEX IF NOT EXISTS idx_response_time ON proxies(response_time_ms) WHERE is_active = TRUE;
|
|
CREATE INDEX IF NOT EXISTS idx_usage ON proxies(usage);
|
|
|
|
-- Create function to update updated_at timestamp
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Create trigger to automatically update updated_at
|
|
DROP TRIGGER IF EXISTS update_proxies_updated_at ON proxies;
|
|
CREATE TRIGGER update_proxies_updated_at
|
|
BEFORE UPDATE ON proxies
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Grant permissions (if needed for non-superuser)
|
|
-- GRANT ALL PRIVILEGES ON TABLE proxies TO your_user;
|
|
-- GRANT USAGE, SELECT ON SEQUENCE proxies_id_seq TO your_user;
|
|
|
|
-- Display initial status
|
|
DO $$
|
|
BEGIN
|
|
RAISE NOTICE 'Database initialized successfully';
|
|
RAISE NOTICE 'Proxies table and indexes created';
|
|
END $$;
|