7ges ERP Client/Server

🔍 Component Identification

• Map all module tables
• Identify relationships and dependencies
• Analyze existing PHP methods (create, update, validate)
• Document current business rules
• Verify relationship tables expected by code

# Search all table references in PHP code
grep -r "llx_tabla" htdocs/modulo/ | grep -E "(DELETE|INSERT|UPDATE)"

# Verify structure in PostgreSQL
\d llx_tabla*

🏗️ Infrastructure Validation

CRITICAL: Before writing any function, verify that all tables and fields exist.

-- test-modulo-estructura.sql
BEGIN;
SELECT plan(15);

-- Verify table existence
SELECT has_table('llx_tabla_principal', 'Main table must exist');
SELECT has_table('llx_tabla_det', 'Detail table must exist');

-- Verify required fields
SELECT has_column('llx_tabla', 'rowid', 'rowid field must exist');
SELECT has_column('llx_tabla', 'entity', 'entity field must exist');

-- Verify data types
SELECT col_type_is('llx_tabla', 'amount', 'double precision', 'amount must be double precision');

-- Verify constraints
SELECT col_not_null('llx_tabla', 'fk_user_author', 'fk_user_author must be NOT NULL');

SELECT * FROM finish();
ROLLBACK;

⚙️ Development of Functions and Triggers

• Create specific validation functions
• Implement BEFORE INSERT/UPDATE triggers
• Develop automatic calculation functions
• Establish unique code generation
• Configure audit and logging

CREATE OR REPLACE FUNCTION llx_tabla_before_insert() 
RETURNS trigger 
LANGUAGE plpgsql
AS $$
BEGIN
    -- 1. Validaciones obligatorias
    IF NEW.campo_obligatorio IS NULL OR trim(NEW.campo_obligatorio) = '' THEN
        RAISE EXCEPTION 'Required field cannot be empty';
    END IF;
    
    -- 2. Limpieza de datos
    NEW.campo_texto = trim(NEW.campo_texto);
    
    -- 3. Valores por defecto
    NEW.entity = COALESCE(NEW.entity, 1);
    NEW.datec = COALESCE(NEW.datec, NOW());
    
    -- 4. Automatic code generation
    IF NEW.ref IS NULL OR NEW.ref = '' THEN
        NEW.ref := llx_tabla_get_next_ref();
    END IF;
    
    RETURN NEW;
END;
$$;

🧪 Complete Functional Validation

• Tests de existencia de functions y triggers
• Tests de validaciones (casos exitosos y fallidos)
• Automatic calculation tests
• Code generation tests
• Tests de casos extremos y errores

🔧 Code Simplification

Objective: Convert PHP into a thin client that only executes SQL.

public function verify() {
    if (empty($this->nom)) {
        $this->errors[] = 'Nom obligatoire';
        return -1;
    }
    
    if (!empty($this->email) && !filter_var($this->email, FILTER_VALIDATE_EMAIL)) {
        $this->errors[] = 'Email invalide';
        return -1;
    }
    
    // More validations...
    return 0;
}

public function verify() {
    return 0; // PostgreSQL triggers handle ALL validation
}

⚖️ Functional Parity Validation

Rigorous process to ensure PostgreSQL behavior is identical to original PHP.

-- Temporary table for results
CREATE TEMP TABLE test_results (
    test_name varchar(100),
    php_result text,
    pgsql_result text,
    match boolean
);

-- Example comparative test
DO $$
DECLARE
    v_error text;
BEGIN
    -- Attempt invalid insertion in PostgreSQL
    BEGIN
        INSERT INTO llx_tabla (campo_obligatorio) VALUES ('');
        PERFORM fail('Should have failed');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_error = MESSAGE_TEXT;
        INSERT INTO test_results VALUES 
        ('empty_field', 'ErrorFieldRequired', v_error, v_error LIKE '%required%');
    END;
END $$;

-- Show results
SELECT * FROM test_results WHERE NOT match;

📚 Complete Process Record

• MIGRATION_PLAN.md: Detailed pre-implementation plan
• MIGRATION_LOG.md: Chronological change log
• functions-module.sql: Commented PostgreSQL code
• test-module.sql: Complete pgTAP tests
• Screenshots: Evidence of passing tests

htdocs/modulo/
├── MIGRATION_PLAN.md          # Migration plan
├── MIGRATION_LOG.md           # Change log
├── class/modulo.class.php     # Simplified PHP
└── class/modulo.class.php.orig # Backup original

htdocs/install/pgsql/
├── functions/
│   └── functions-modulo.sql   # PostgreSQL Functions
└── tests/
    ├── test-modulo.sql        # Functional tests
    └── test-modulo-estructura.sql # Structure tests