c77_dep/USAGE.md

c77_dep Usage Guide

This guide provides comprehensive information on using the c77_dep PostgreSQL extension for database dependency management.

Table of Contents

1. Getting Started
2. Core Views
3. Analysis Views
4. Analysis Functions
5. Common Workflows
6. Use Cases
7. Performance Optimization
8. Integration with Laravel
9. Troubleshooting

Getting Started

After installation, the extension provides immediate access to dependency information through views and functions.

Verifying Installation

SELECT extname, extversion FROM pg_extension WHERE extname = 'c77_dep';

Initial Inspection

Get a quick overview of your database dependencies:

-- Summary of object types and their dependency characteristics
SELECT * FROM c77_dep_object_type_summary;

-- Top objects with many dependencies
SELECT * FROM c77_dep_hub_objects LIMIT 10;

Core Views

c77_dep_dependencies_map

The base view that maps dependencies between tables, views, and materialized views.

SELECT * FROM c77_dep_dependencies_map WHERE relation = 'schema.object_name';

Key columns:

• relation: Object name (schema.name)
• object_type: Type of database object (TABLE, VIEW, MATV)
• deps: Number of direct dependencies
• add_deps: Number of indirect dependencies
• dependents: List of objects that depend on this object
• requirements: List of objects this object depends on

c77_dep_dependencies_map_with_foreign

Enhanced version that also includes foreign tables.

SELECT * FROM c77_dep_dependencies_map_with_foreign 
WHERE object_type = 'FOREIGN TABLE';

Analysis Views

c77_dep_orphaned_objects

Displays objects with no dependencies.

-- Find all orphaned views
SELECT * FROM c77_dep_orphaned_objects 
WHERE object_type = 'VIEW';

c77_dep_schema_complexity

Provides metrics on schema complexity.

-- Identify the most complex schemas
SELECT * FROM c77_dep_schema_complexity 
ORDER BY total_dependencies DESC;

c77_dep_object_type_summary

Statistical summary of object types and their dependency characteristics.

-- See distribution of object types
SELECT * FROM c77_dep_object_type_summary;

c77_dep_risk_assessment

Assesses risk of modifying database objects.

-- Find high-risk objects
SELECT * FROM c77_dep_risk_assessment 
WHERE risk_level = 'HIGH';

c77_dep_hub_objects

Lists objects with many dependencies (dependency hubs).

-- Find key dependency hubs
SELECT * FROM c77_dep_hub_objects;

c77_dep_cleanup_candidates

Identifies objects that may be candidates for cleanup.

-- Find cleanup candidates
SELECT * FROM c77_dep_cleanup_candidates;

Analysis Functions

c77_dep_analyze_drop_impact

Analyzes the impact of dropping a specific object.

-- What would happen if we dropped this table?
SELECT * FROM c77_dep_analyze_drop_impact('myschema.mytable');

c77_dep_detect_circular_dependencies

Identifies circular dependency relationships.

-- Find all circular dependencies
SELECT * FROM c77_dep_detect_circular_dependencies();

-- Find circular dependencies in a specific schema
SELECT * FROM c77_dep_detect_circular_dependencies()
WHERE object1 LIKE 'myschema.%';

c77_dep_export_dependency_graph

Generates a DOT-format graph for visualization with Graphviz.

-- Export dependencies for a specific schema
SELECT c77_dep_export_dependency_graph('myschema');

Sample workflow for visualization:

1. Run the function and save output to a file (e.g., dependencies.dot)
2. Use Graphviz to create a visual graph:

   dot -Tpng dependencies.dot -o dependencies.png
   

c77_dep_simulate_schema_change

Simulates the impact of schema changes.

-- What would be affected by renaming this schema?
SELECT * FROM c77_dep_simulate_schema_change('old_schema', 'new_schema');

c77_dep_generate_migration_order

Suggests a migration order based on dependencies.

-- Get a dependency-based migration plan
SELECT * FROM c77_dep_generate_migration_order();

c77_dep_generate_report

Creates a comprehensive dependency analysis report.

-- Generate a full report
SELECT c77_dep_generate_report();

-- Generate a report for a specific schema
SELECT c77_dep_generate_report('myschema');

Common Workflows

Database Refactoring Planning

1. Generate an overview report:

   SELECT c77_dep_generate_report();
   

2. Identify complex schemas for potential splitting:

   SELECT * FROM c77_dep_schema_complexity 
   ORDER BY total_dependencies DESC LIMIT 5;
   

3. Find circular dependencies that need resolving:

   SELECT * FROM c77_dep_detect_circular_dependencies();
   

4. Identify possible cleanup candidates:

   SELECT * FROM c77_dep_cleanup_candidates;
   

Schema Migration Planning

1. Determine migration order to preserve dependencies:

   SELECT * FROM c77_dep_generate_migration_order();
   

2. For each phase, analyze specific object impacts:

   SELECT * FROM c77_dep_analyze_drop_impact('schema.object');
   

3. Visualize the schema dependencies:

   SELECT c77_dep_export_dependency_graph('schema_name');
   

Use Cases

Database Documentation

Generate detailed dependency documentation for your database:

-- Create comprehensive report
SELECT c77_dep_generate_report();

-- Export visualization
SELECT c77_dep_export_dependency_graph();

Impact Analysis Before Changes

Before modifying a database object:

-- Check what would be affected
SELECT * FROM c77_dep_analyze_drop_impact('schema.object_name');

-- Assess the risk
SELECT * FROM c77_dep_risk_assessment 
WHERE relation = 'schema.object_name';

Clean-up Operations

Identify unused or problematic objects:

-- Find objects with no dependencies
SELECT * FROM c77_dep_orphaned_objects;

-- Find circular dependencies to resolve
SELECT * FROM c77_dep_detect_circular_dependencies();

Performance Optimization

For large databases, consider these performance tips:

1. Schema-specific analysis: Filter by schema instead of analyzing the entire database

   SELECT * FROM c77_dep_schema_complexity WHERE schema_name = 'myschema';
   

2. Targeted object analysis: Focus on specific objects rather than complete scans

   SELECT * FROM c77_dep_analyze_drop_impact('schema.object');
   

3. Scheduled analysis: Run comprehensive analysis during off-peak hours

4. Progressive analysis: Analyze one schema at a time if full database analysis is too intensive

Integration with Laravel

Laravel applications can benefit from the c77_dep extension for database management, migrations, and monitoring.

Setting Up Laravel with c77_dep

1. Ensure the extension is installed on your PostgreSQL database:

   CREATE EXTENSION c77_dep;
   

2. Create a custom Artisan command to interact with c77_dep:

<?php
// app/Console/Commands/AnalyzeDatabaseDependencies.php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class AnalyzeDatabaseDependencies extends Command
{
    protected $signature = 'db:analyze-dependencies {action} {--schema=} {--object=}';
    protected $description = 'Analyze database dependencies using c77_dep extension';

    public function handle()
    {
        $action = $this->argument('action');
        $schema = $this->option('schema');
        $object = $this->option('object');

        switch ($action) {
            case 'report':
                $this->generateReport($schema);
                break;
            case 'orphaned':
                $this->findOrphanedObjects($schema);
                break;
            case 'impact':
                if (!$object) {
                    $this->error('Please specify an object with --object option');
                    return 1;
                }
                $this->analyzeImpact($object);
                break;
            case 'circular':
                $this->findCircularDependencies($schema);
                break;
            default:
                $this->error('Unknown action. Available actions: report, orphaned, impact, circular');
                return 1;
        }

        return 0;
    }

    protected function generateReport($schema = null)
    {
        $sql = "SELECT c77_dep_generate_report(" . ($schema ? "'$schema'" : "NULL") . ")";
        $report = DB::select($sql)[0]->c77_dep_generate_report;
        $this->line($report);
    }

    protected function findOrphanedObjects($schema = null)
    {
        $sql = "SELECT * FROM c77_dep_orphaned_objects";
        if ($schema) {
            $sql .= " WHERE relation LIKE '$schema.%'";
        }
        $results = DB::select($sql);
        $this->table(['Relation', 'Object Type', 'Owner'], collect($results)->map(function ($item) {
            return (array) $item;
        }));
    }

    protected function analyzeImpact($object)
    {
        $sql = "SELECT * FROM c77_dep_analyze_drop_impact('$object')";
        $results = DB::select($sql);
        $this->table(['Affected Object', 'Object Type', 'Impact Level', 'Dependency Path'], collect($results)->map(function ($item) {
            return (array) $item;
        }));
    }

    protected function findCircularDependencies($schema = null)
    {
        $sql = "SELECT * FROM c77_dep_detect_circular_dependencies()";
        if ($schema) {
            $sql .= " WHERE object1 LIKE '$schema.%'";
        }
        $results = DB::select($sql);
        $this->table(['Object 1', 'Object 2', 'Dependency Type'], collect($results)->map(function ($item) {
            return (array) $item;
        }));
    }
}

3. Register the command in app/Console/Kernel.php:

protected $commands = [
    // ...
    \App\Console\Commands\AnalyzeDatabaseDependencies::class,
];

Using with Laravel Migrations

1. Use dependency information to plan migrations:

php artisan db:analyze-dependencies report

2. Check for circular dependencies before creating migrations:

php artisan db:analyze-dependencies circular

3. Analyze impact before modifying a table:

php artisan db:analyze-dependencies impact --object=schema.table_name

Incorporating into Laravel Projects

1. Schema Changes Review: Before implementing schema changes, analyze dependencies

2. Migration Automation: Use dependency information to generate migrations in the correct order

3. Database Documentation: Generate dependency reports as part of your application documentation

4. CI/CD Integration: Include dependency checks in your continuous integration pipeline:

# In your CI/CD pipeline configuration
database-dependency-check:
  stage: test
  script:
    - php artisan db:analyze-dependencies circular
    - php artisan db:analyze-dependencies orphaned
  only:
    changes:
      - database/migrations/*

Troubleshooting

Common Issues

1. Performance Problems:

   • Limit analysis to specific schemas
   • Use targeted functions instead of full database analysis
   • Run during off-peak hours

2. Incomplete Results:

   • Some logical dependencies might not be captured
   • Check permission issues if certain objects are missing

3. Missing Dependencies:

   • Certain dynamic dependencies created through SQL in functions might not be detected
   • Dependencies created through dynamic SQL might not be visible

Getting Help

For additional help, consult:

• PostgreSQL documentation on system catalogs
• Extension README.md file
• GitHub repository issues (if available)
• PostgreSQL community forums