wip
atscan.net
1#!/bin/bash
2# migrate_ipinfo.sh - Migrate IP info from endpoints to ip_infos table
3
4# Configuration (edit these)
5DB_HOST="localhost"
6DB_PORT="5432"
7DB_NAME="atscand"
8DB_USER="atscand"
9DB_PASSWORD="Noor1kooz5eeFai9leZagh5ua5eihai4"
10
11# Colors for output
12RED='\033[0;31m'
13GREEN='\033[0;32m'
14YELLOW='\033[1;33m'
15NC='\033[0m' # No Color
16
17echo -e "${GREEN}=== IP Info Migration Script ===${NC}"
18echo ""
19
20# Export password for psql
21export PGPASSWORD="$DB_PASSWORD"
22
23# Check if we can connect
24echo -e "${YELLOW}Testing database connection...${NC}"
25if ! psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SELECT 1;" > /dev/null 2>&1; then
26 echo -e "${RED}Error: Cannot connect to database${NC}"
27 exit 1
28fi
29echo -e "${GREEN}✓ Connected to database${NC}"
30echo ""
31
32# Create ip_infos table if it doesn't exist
33echo -e "${YELLOW}Creating ip_infos table...${NC}"
34psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" << 'SQL'
35CREATE TABLE IF NOT EXISTS ip_infos (
36 ip TEXT PRIMARY KEY,
37 city TEXT,
38 country TEXT,
39 country_code TEXT,
40 asn INTEGER,
41 asn_org TEXT,
42 is_datacenter BOOLEAN,
43 is_vpn BOOLEAN,
44 latitude REAL,
45 longitude REAL,
46 raw_data JSONB,
47 fetched_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
48 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
49);
50
51CREATE INDEX IF NOT EXISTS idx_ip_infos_country_code ON ip_infos(country_code);
52CREATE INDEX IF NOT EXISTS idx_ip_infos_asn ON ip_infos(asn);
53SQL
54
55if [ $? -eq 0 ]; then
56 echo -e "${GREEN}✓ ip_infos table ready${NC}"
57else
58 echo -e "${RED}✗ Failed to create table${NC}"
59 exit 1
60fi
61echo ""
62
63# Count how many endpoints have IP info
64echo -e "${YELLOW}Checking existing data...${NC}"
65ENDPOINT_COUNT=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c \
66 "SELECT COUNT(*) FROM endpoints WHERE ip IS NOT NULL AND ip != '' AND ip_info IS NOT NULL;")
67echo -e "Endpoints with IP info: ${GREEN}${ENDPOINT_COUNT}${NC}"
68
69EXISTING_IP_COUNT=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c \
70 "SELECT COUNT(*) FROM ip_infos;")
71echo -e "Existing IPs in ip_infos table: ${GREEN}${EXISTING_IP_COUNT}${NC}"
72echo ""
73
74# Migrate data
75echo -e "${YELLOW}Migrating IP info data...${NC}"
76psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" << 'SQL'
77-- Migrate IP info from endpoints to ip_infos
78-- Only insert IPs that don't already exist in ip_infos
79INSERT INTO ip_infos (
80 ip,
81 city,
82 country,
83 country_code,
84 asn,
85 asn_org,
86 is_datacenter,
87 is_vpn,
88 latitude,
89 longitude,
90 raw_data,
91 fetched_at,
92 updated_at
93)
94SELECT DISTINCT ON (e.ip)
95 e.ip,
96 e.ip_info->'location'->>'city' AS city,
97 e.ip_info->'location'->>'country' AS country,
98 e.ip_info->'location'->>'country_code' AS country_code,
99 (e.ip_info->'asn'->>'asn')::INTEGER AS asn,
100 e.ip_info->'asn'->>'org' AS asn_org,
101 -- Check if company type is "hosting" for datacenter detection
102 CASE
103 WHEN e.ip_info->'company'->>'type' = 'hosting' THEN true
104 ELSE false
105 END AS is_datacenter,
106 -- Check VPN from security field
107 COALESCE((e.ip_info->'security'->>'vpn')::BOOLEAN, false) AS is_vpn,
108 -- Latitude and longitude
109 (e.ip_info->'location'->>'latitude')::REAL AS latitude,
110 (e.ip_info->'location'->>'longitude')::REAL AS longitude,
111 -- Store full raw data
112 e.ip_info AS raw_data,
113 COALESCE(e.updated_at, CURRENT_TIMESTAMP) AS fetched_at,
114 CURRENT_TIMESTAMP AS updated_at
115FROM endpoints e
116WHERE
117 e.ip IS NOT NULL
118 AND e.ip != ''
119 AND e.ip_info IS NOT NULL
120 AND NOT EXISTS (
121 SELECT 1 FROM ip_infos WHERE ip_infos.ip = e.ip
122 )
123ORDER BY e.ip, e.updated_at DESC NULLS LAST;
124SQL
125
126if [ $? -eq 0 ]; then
127 echo -e "${GREEN}✓ Data migration completed${NC}"
128else
129 echo -e "${RED}✗ Migration failed${NC}"
130 exit 1
131fi
132echo ""
133
134# Show results
135echo -e "${YELLOW}Migration summary:${NC}"
136NEW_IP_COUNT=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c \
137 "SELECT COUNT(*) FROM ip_infos;")
138MIGRATED=$((NEW_IP_COUNT - EXISTING_IP_COUNT))
139echo -e "Total IPs now in ip_infos: ${GREEN}${NEW_IP_COUNT}${NC}"
140echo -e "Newly migrated: ${GREEN}${MIGRATED}${NC}"
141echo ""
142
143# Show sample data
144echo -e "${YELLOW}Sample migrated data:${NC}"
145psql -h "$DB_HOST" -ps "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c \
146 "SELECT ip, city, country, country_code, asn, is_datacenter, is_vpn FROM ip_infos LIMIT 5;"
147echo ""
148
149# Optional: Drop old columns (commented out for safety)
150echo -e "${YELLOW}Cleanup options:${NC}"
151echo -e "To remove old ip_info column from endpoints table, run:"
152echo -e "${RED} ALTER TABLE endpoints DROP COLUMN IF EXISTS ip_info;${NC}"
153echo -e "To remove old user_count column from endpoints table, run:"
154echo -e "${RED} ALTER TABLE endpoints DROP COLUMN IF EXISTS user_count;${NC}"
155echo ""
156
157echo -e "${GREEN}=== Migration Complete ===${NC}"
158
159# Unset password
160unset PGPASSWORD