monitor_app/viewdir/idds_database.py

0001 """
0002 iDDS Database browsing views.
0003
0004 This module provides views for browsing the external iDDS database
0005 following the same patterns as the PanDA database browser.
0006 """
0007
0008 from django.shortcuts import render
0009 from django.contrib.auth.decorators import login_required
0010 from django.http import Http404
0011 from django.db import connections
0012 from datetime import datetime, date
0013
0014
0015 @login_required
0016 def idds_database_tables_list(request):
0017     """
0018     iDDS database tables list view using server-side DataTables.
0019     Shows all tables in the iDDS database with counts and last insert times.
0020     """
0021     from django.urls import reverse
0022
0023     # Column definitions for DataTables
0024     columns = [
0025         {'name': 'name', 'title': 'Table Name', 'orderable': True},
0026         {'name': 'count', 'title': 'Row Count', 'orderable': True},
0027         {'name': 'last_insert', 'title': 'Last Insert', 'orderable': True},
0028     ]
0029
0030     context = {
0031         'table_title': 'iDDS Database Overview',
0032         'table_description': 'Server-side processing view of all tables in the iDDS database with row counts and last insert times.',
0033         'ajax_url': reverse('monitor_app:idds_database_tables_datatable_ajax'),
0034         'columns': columns,
0035     }
0036     return render(request, 'monitor_app/database_tables_server.html', context)
0037
0038
0039 @login_required
0040 def idds_database_tables_datatable_ajax(request):
0041     """
0042     AJAX endpoint for server-side DataTables processing of iDDS database tables.
0043     """
0044     from ..utils import DataTablesProcessor, format_datetime
0045
0046     # Initialize DataTables processor
0047     columns = ['name', 'count', 'last_insert']
0048     dt = DataTablesProcessor(request, columns, default_order_column=0, default_order_direction='asc')
0049
0050     # Get iDDS database connection
0051     idds_connection = connections['idds']
0052
0053     # Build table metadata
0054     table_records = []
0055     try:
0056         with idds_connection.cursor() as cursor:
0057             # Get list of all tables in the iDDS database schema
0058             # Note: iDDS uses a specific schema, so we need to query that schema
0059             from django.conf import settings
0060
0061             # Extract schema name from database configuration
0062             schema_name = 'doma_idds'  # Default from environment
0063             try:
0064                 db_config = settings.DATABASES.get('idds', {})
0065                 if 'OPTIONS' in db_config and 'options' in db_config['OPTIONS']:
0066                     options_str = db_config['OPTIONS']['options']
0067                     if 'search_path=' in options_str:
0068                         schema_name = options_str.split('search_path=')[1]
0069             except:
0070                 pass
0071
0072             cursor.execute("""
0073                 SELECT table_name
0074                 FROM information_schema.tables
0075                 WHERE table_schema = %s
0076                 AND table_type = 'BASE TABLE'
0077                 ORDER BY table_name
0078             """, [schema_name])
0079
0080             tables = [row[0] for row in cursor.fetchall()]
0081
0082             for table_name in tables:
0083                 record = {
0084                     'name': table_name,
0085                     'count': 0,
0086                     'last_insert': None
0087                 }
0088
0089                 try:
0090                     # Get row count - use schema-qualified table name
0091                     cursor.execute(f'SELECT COUNT(*) FROM "{schema_name}"."{table_name}"')
0092                     record['count'] = cursor.fetchone()[0]
0093
0094                     # Try to find a timestamp column for last insert
0095                     cursor.execute("""
0096                         SELECT column_name
0097                         FROM information_schema.columns
0098                         WHERE table_schema = %s
0099                         AND table_name = %s
0100                         AND data_type IN ('timestamp', 'timestamp with time zone', 'timestamp without time zone')
0101                         ORDER BY ordinal_position
0102                         LIMIT 1
0103                     """, [schema_name, table_name])
0104
0105                     timestamp_col = cursor.fetchone()
0106                     if timestamp_col:
0107                         cursor.execute(f'SELECT MAX("{timestamp_col[0]}") FROM "{schema_name}"."{table_name}"')
0108                         result = cursor.fetchone()
0109                         if result and result[0]:
0110                             record['last_insert'] = result[0]
0111
0112                 except Exception:
0113                     pass  # Skip tables we can't access
0114
0115                 table_records.append(record)
0116
0117     except Exception as e:
0118         # If we can't connect to iDDS database, return empty result
0119         table_records = []
0120
0121     # Get total counts
0122     records_total = len(table_records)
0123
0124     # Apply search filtering
0125     if dt.search_value:
0126         search_term = dt.search_value.lower()
0127         table_records = [r for r in table_records if search_term in r['name'].lower()]
0128
0129     records_filtered = len(table_records)
0130
0131     # Apply ordering
0132     table_records.sort(key=lambda r: (r[dt.order_column] is None, r[dt.order_column]), reverse=(dt.order_direction == 'desc'))
0133
0134     # Apply pagination
0135     start = dt.start
0136     length = dt.length if dt.length > 0 else len(table_records)
0137     paginated_records = table_records[start:start + length]
0138
0139     # Format data for DataTables
0140     data = []
0141     for record in paginated_records:
0142         from django.urls import reverse
0143         table_url = reverse('monitor_app:idds_database_table_list', args=[record['name']])
0144         table_link = f'<a href="{table_url}">{record["name"]}</a>'
0145         count_str = str(record['count'])
0146         last_insert_str = format_datetime(record['last_insert'])
0147
0148         data.append([table_link, count_str, last_insert_str])
0149
0150     return dt.create_response(data, records_total, records_filtered)
0151
0152
0153 @login_required
0154 def idds_database_table_list(request, table_name):
0155     """
0156     iDDS database individual table view.
0157     """
0158     from django.urls import reverse
0159
0160     # Get iDDS database connection
0161     idds_connection = connections['idds']
0162
0163     # Get column information
0164     columns = []
0165     try:
0166         with idds_connection.cursor() as cursor:
0167             # Get schema name from settings
0168             from django.conf import settings
0169             schema_name = 'doma_idds'  # Default from environment
0170             try:
0171                 db_config = settings.DATABASES.get('idds', {})
0172                 if 'OPTIONS' in db_config and 'options' in db_config['OPTIONS']:
0173                     options_str = db_config['OPTIONS']['options']
0174                     if 'search_path=' in options_str:
0175                         schema_name = options_str.split('search_path=')[1]
0176             except:
0177                 pass
0178
0179             cursor.execute(f'SELECT * FROM "{schema_name}"."{table_name}" LIMIT 1')
0180             columns = [col[0] for col in cursor.description]
0181     except Exception:
0182         raise Http404()
0183
0184     # Convert columns for DataTables format
0185     datatable_columns = [{'name': col, 'title': col.replace('_', ' ').title(), 'orderable': True} for col in columns]
0186
0187     context = {
0188         'table_title': f'iDDS Table: {table_name}',
0189         'table_description': f'iDDS database table contents for {table_name} with search, sorting, and pagination.',
0190         'ajax_url': reverse('monitor_app:idds_database_table_datatable_ajax', kwargs={'table_name': table_name}),
0191         'columns': datatable_columns,
0192         'table_name': table_name,
0193     }
0194     return render(request, 'monitor_app/database_table_list.html', context)
0195
0196
0197 @login_required
0198 def idds_database_table_datatable_ajax(request, table_name):
0199     """
0200     AJAX endpoint for server-side DataTables processing of individual iDDS database table.
0201     """
0202     from ..utils import DataTablesProcessor, format_datetime
0203
0204     # Get iDDS database connection
0205     idds_connection = connections['idds']
0206
0207     # Get column information
0208     columns = []
0209     try:
0210         with idds_connection.cursor() as cursor:
0211             # Get schema name from settings
0212             from django.conf import settings
0213             schema_name = 'doma_idds'  # Default from environment
0214             try:
0215                 db_config = settings.DATABASES.get('idds', {})
0216                 if 'OPTIONS' in db_config and 'options' in db_config['OPTIONS']:
0217                     options_str = db_config['OPTIONS']['options']
0218                     if 'search_path=' in options_str:
0219                         schema_name = options_str.split('search_path=')[1]
0220             except:
0221                 pass
0222
0223             cursor.execute(f'SELECT * FROM "{schema_name}"."{table_name}" LIMIT 1')
0224             columns = [col[0] for col in cursor.description]
0225     except Exception:
0226         raise Http404()
0227
0228     # Initialize DataTables processor
0229     dt = DataTablesProcessor(request, columns, default_order_column=0, default_order_direction='asc')
0230
0231     # Get schema name from settings
0232     from django.conf import settings
0233     schema_name = 'doma_idds'  # Default from environment
0234     try:
0235         db_config = settings.DATABASES.get('idds', {})
0236         if 'OPTIONS' in db_config and 'options' in db_config['OPTIONS']:
0237             options_str = db_config['OPTIONS']['options']
0238             if 'search_path=' in options_str:
0239                 schema_name = options_str.split('search_path=')[1]
0240     except:
0241         pass
0242
0243     # Build base query - use schema-qualified table name
0244     query = f'SELECT * FROM "{schema_name}"."{table_name}"'
0245     count_query = f'SELECT COUNT(*) FROM "{schema_name}"."{table_name}"'
0246     params = []
0247
0248     try:
0249         with idds_connection.cursor() as cursor:
0250             # Get total count
0251             cursor.execute(count_query)
0252             records_total = cursor.fetchone()[0]
0253
0254             # Apply search filtering
0255             where_conditions = []
0256             if dt.search_value:
0257                 search_conditions = []
0258                 for column in columns:
0259                     search_conditions.append(f'CAST("{column}" AS TEXT) ILIKE %s')
0260                     params.append(f'%{dt.search_value}%')
0261                 where_conditions.append(f"({' OR '.join(search_conditions)})")
0262
0263             # Build filtered query
0264             filtered_query = query
0265             filtered_count_query = count_query
0266             if where_conditions:
0267                 where_clause = ' WHERE ' + ' AND '.join(where_conditions)
0268                 filtered_query += where_clause
0269                 filtered_count_query += where_clause
0270
0271             # Get filtered count
0272             cursor.execute(filtered_count_query, params)
0273             records_filtered = cursor.fetchone()[0]
0274
0275             # Apply ordering
0276             if dt.order_column:
0277                 order_direction = 'ASC' if dt.order_direction == 'asc' else 'DESC'
0278                 filtered_query += f' ORDER BY "{dt.order_column}" {order_direction}'
0279
0280             # Apply pagination
0281             filtered_query += f' LIMIT {dt.length} OFFSET {dt.start}'
0282
0283             # Execute main query
0284             cursor.execute(filtered_query, params)
0285             rows = cursor.fetchall()
0286
0287             # Format data for DataTables
0288             data = []
0289             for row in rows:
0290                 formatted_row = []
0291                 for i, value in enumerate(row):
0292                     if value is None:
0293                         formatted_row.append('')
0294                     elif isinstance(value, (datetime, date)):
0295                         formatted_row.append(format_datetime(value))
0296                     else:
0297                         formatted_row.append(str(value))
0298                 data.append(formatted_row)
0299
0300     except Exception as e:
0301         # Return empty result on error
0302         data = []
0303         records_total = 0
0304         records_filtered = 0
0305
0306     return dt.create_response(data, records_total, records_filtered)