243 lines
8.1 KiB
Python
Executable file
243 lines
8.1 KiB
Python
Executable file
#!/usr/bin/env python3
|
|
"""
|
|
Generate DBeaver data-sources.json from sdm status --json output
|
|
Organizes PostgreSQL connections by customer folders
|
|
Excludes readonly connections, includes activate connections
|
|
"""
|
|
|
|
import subprocess
|
|
import json
|
|
import re
|
|
import uuid
|
|
import os
|
|
from collections import defaultdict
|
|
|
|
def run_sdm_status():
|
|
try:
|
|
result = subprocess.run(['sdm', 'status', '--json'], capture_output=True, text=True)
|
|
if result.returncode != 0:
|
|
print(f"Error running sdm status: {result.stderr}")
|
|
return None
|
|
return json.loads(result.stdout)
|
|
except FileNotFoundError:
|
|
print("Error: sdm command not found")
|
|
return None
|
|
except json.JSONDecodeError as e:
|
|
print(f"Error parsing JSON: {e}")
|
|
return None
|
|
|
|
def parse_postgres_connections(sdm_data):
|
|
connections = []
|
|
|
|
for item in sdm_data:
|
|
if item.get('type') == 'postgres':
|
|
name = item.get('name', '')
|
|
address = item.get('address', '')
|
|
|
|
if 'readonly' in name:
|
|
continue
|
|
|
|
if ':' in address:
|
|
addr, port = address.split(':')
|
|
port = int(port)
|
|
else:
|
|
continue
|
|
|
|
env_info = parse_connection_name(name)
|
|
if env_info:
|
|
connections.append({
|
|
'name': name,
|
|
'addr': addr,
|
|
'port': port,
|
|
'environment': env_info['environment'],
|
|
'customer': env_info['customer'],
|
|
'stage': env_info['stage']
|
|
})
|
|
|
|
return connections
|
|
|
|
def parse_connection_name(name):
|
|
pattern = r'oc-([^-]+)-([^-]+)-.*'
|
|
match = re.match(pattern, name)
|
|
|
|
if not match:
|
|
return None
|
|
|
|
environment = match.group(1)
|
|
customer = match.group(2)
|
|
|
|
if environment in ['dev', 'nextrc', 'nextrc2']:
|
|
stage = 'internal'
|
|
elif environment in ['stage', 'stage2', 'uat']:
|
|
stage = 'stage'
|
|
elif environment in ['prod', 'prod2']:
|
|
stage = 'production'
|
|
else:
|
|
stage = 'unknown'
|
|
|
|
return {
|
|
'environment': environment,
|
|
'customer': customer,
|
|
'stage': stage
|
|
}
|
|
|
|
def create_dbeaver_connection(conn, connection_id):
|
|
if conn['customer'] == 'internal' or conn['customer'].startswith('feature'):
|
|
folder = 'internal'
|
|
elif conn['stage'] == 'stage':
|
|
folder = conn['customer']
|
|
elif conn['stage'] == 'production':
|
|
folder = conn['customer']
|
|
else:
|
|
folder = 'other'
|
|
|
|
display_name = conn['name'].replace('oc-', '').replace('-rds', '').replace('-activate', '')
|
|
|
|
if 'activate' in conn['name']:
|
|
display_name = f"activate-{display_name}"
|
|
|
|
if 'activate' in conn['name']:
|
|
default_db = "postgres"
|
|
db_url = f"jdbc:postgresql://{conn['addr']}:{conn['port']}/postgres"
|
|
else:
|
|
default_db = "member_dossier"
|
|
db_url = f"jdbc:postgresql://{conn['addr']}:{conn['port']}/member_dossier"
|
|
|
|
return {
|
|
"provider": "postgresql",
|
|
"driver": "postgres-jdbc",
|
|
"name": display_name,
|
|
"save-password": True,
|
|
"folder": folder,
|
|
"configuration": {
|
|
"host": conn['addr'],
|
|
"port": str(conn['port']),
|
|
"database": default_db,
|
|
"url": db_url,
|
|
"configurationType": "MANUAL",
|
|
"home": "/bin",
|
|
"type": conn['environment'],
|
|
"closeIdleConnection": False,
|
|
"provider-properties": {
|
|
"@dbeaver-show-non-default-db@": "true",
|
|
"@dbeaver-show-template-db@": "true",
|
|
"@dbeaver-show-unavailable-db@": "true",
|
|
"show-database-statistics": "false",
|
|
"@dbeaver-read-all-data-types-db@": "false",
|
|
"@dbeaver-use-prepared-statements-db@": "false",
|
|
"postgresql.dd.plain.string": "false",
|
|
"postgresql.dd.tag.string": "false"
|
|
},
|
|
"auth-model": "native"
|
|
}
|
|
}
|
|
|
|
def group_connections(connections):
|
|
folders = defaultdict(list)
|
|
|
|
for conn in connections:
|
|
if conn['customer'] == 'internal' or conn['customer'].startswith('feature'):
|
|
folder = 'internal'
|
|
elif conn['stage'] == 'stage':
|
|
folder = conn['customer']
|
|
elif conn['stage'] == 'production':
|
|
folder = conn['customer']
|
|
else:
|
|
folder = 'other'
|
|
|
|
folders[folder].append(conn)
|
|
|
|
return folders
|
|
|
|
def is_sdm_connection(conn_name):
|
|
"""Check if connection name matches SDM pattern (oc-*-*-*)"""
|
|
pattern = r'oc-([^-]+)-([^-]+)-.*'
|
|
return re.match(pattern, conn_name) is not None
|
|
|
|
def load_existing_config(output_file):
|
|
"""Load existing DBeaver config, return empty if file doesn't exist"""
|
|
if not os.path.exists(output_file):
|
|
return {"folders": {}, "connections": {}}
|
|
|
|
try:
|
|
with open(output_file, 'r') as f:
|
|
return json.load(f)
|
|
except json.JSONDecodeError:
|
|
print("⚠️ Existing config file is invalid, starting fresh")
|
|
return {"folders": {}, "connections": {}}
|
|
|
|
def generate_dbeaver_config(connections, existing_config):
|
|
folders = group_connections(connections)
|
|
|
|
# Start with existing folders
|
|
folders_config = existing_config.get("folders", {}).copy()
|
|
|
|
# Add folders for new SDM connections
|
|
for folder_name in folders.keys():
|
|
if folder_name not in folders_config:
|
|
folders_config[folder_name] = {}
|
|
|
|
# Start with existing connections, filtering out SDM connections
|
|
connections_config = {}
|
|
existing_connections = existing_config.get("connections", {})
|
|
for conn_id, conn in existing_connections.items():
|
|
# Keep non-SDM connections (those that don't start with oc- in their display name)
|
|
display_name = conn.get("name", "")
|
|
# Also check configuration host/type for SDM patterns
|
|
config = conn.get("configuration", {})
|
|
conn_type = config.get("type", "")
|
|
|
|
# Only keep connections that don't match SDM pattern
|
|
if not is_sdm_connection(display_name) and not is_sdm_connection(conn_type):
|
|
connections_config[conn_id] = conn
|
|
|
|
# Add new SDM connections
|
|
for conn in connections:
|
|
connection_id = f"postgres-jdbc-{uuid.uuid4().hex[:8]}-{uuid.uuid4().hex[:8]}"
|
|
conn_config = create_dbeaver_connection(conn, connection_id)
|
|
connections_config[connection_id] = conn_config
|
|
|
|
return {
|
|
"folders": folders_config,
|
|
"connections": connections_config
|
|
}
|
|
|
|
def main():
|
|
print("Generating DBeaver data-sources.json from sdm status --json...")
|
|
|
|
output_file = os.path.expanduser('~/.local/share/DBeaverData/workspace6/Stuzo/.dbeaver/data-sources.json')
|
|
|
|
# Load existing config to preserve non-SDM connections
|
|
existing_config = load_existing_config(output_file)
|
|
existing_count = len(existing_config.get("connections", {}))
|
|
print(f"📁 Found {existing_count} existing connections")
|
|
|
|
sdm_data = run_sdm_status()
|
|
if not sdm_data:
|
|
return
|
|
|
|
connections = parse_postgres_connections(sdm_data)
|
|
print(f"🔍 Found {len(connections)} PostgreSQL connections from SDM")
|
|
|
|
folders = group_connections(connections)
|
|
|
|
for folder_name, conns in folders.items():
|
|
print(f" {folder_name}: {len(conns)} connections")
|
|
|
|
dbeaver_config = generate_dbeaver_config(connections, existing_config)
|
|
|
|
final_count = len(dbeaver_config["connections"])
|
|
preserved_count = final_count - len(connections)
|
|
print(f"💾 Preserved {preserved_count} non-SDM connections")
|
|
print(f"📊 Total connections: {final_count} ({preserved_count} preserved + {len(connections)} SDM)")
|
|
|
|
try:
|
|
with open(output_file, 'w') as f:
|
|
json.dump(dbeaver_config, f, indent='\t')
|
|
print(f"✅ Successfully generated {output_file}")
|
|
print("📝 Note: You may need to restart DBeaver to see the new connections")
|
|
except Exception as e:
|
|
print(f"❌ Error writing file: {e}")
|
|
|
|
if __name__ == '__main__':
|
|
main()
|