Database Automation

Schedule queries for execution at specific time intervals or events, ensuring seamless automation that works around the clock for you.

Thanks to the JS script concept featuring onStartup, onHook, onMessage, and onShutdown functions, creating a schedule for onHook script execution with combined SQL and JS functionality is remarkably straightforward.

Let's begin with the onStartup function, where it's easy to define when the onHook function should be called.

onStartup

InDriver.installHook(60000);

InDriver.installHook(3600000);

The code above will start executing onHook function, every full minute and every hour, synchronized with the clock.

In the onHook function, you can easily provide code that tests conditions for more complicated intervals, such as every Monday at 3 pm:

onHook

let ts = new Date(InDriver.hook());

if (ts.getDay() === 1 && ts.getHours() === 15) {

// Execute my Monday 3pm code

}

For instance, let's automate the detection of transaction overflow and trigger an alert when their count exceeds 10,000 within a 1-hour time window:

onHook

let hourTimeWindow = new Date(ts - 3600000);

let data = InDriver.sqlExecute("AzureSQLServer", "SELECT COUNT(*) FROM public.transactions WHERE ts >='" + hourTimeWindow + "';"); let count = data[0].count;

if (count > 10000) {

InDriver.sqlExecute("AzureSQLServer", "INSERT INTO public.alerts (id, ts, table, alert) VALUES (default, '" + ts + "', 'transactions', 'Overflow: " + count + "')");

}

This script demonstrates the power of InDriver's SQL execution capabilities, allowing you to automate the detection of specific conditions, and take immediate actions.

Unlocking the Power of Database Automation: Real-World Use Cases

Explore a range of SQL automation tasks seamlessly executable within InDriver's onHook function:

1. Data Migration:

Move data from the 'source_table' in the 'source_database' to the 'destination_table' in the 'destination_database.'

InDriver.sqlExecute("SQLServer", "INSERT INTO destination_database.destination_table SELECT * FROM source_database.source_table;");

2. Data Replication:

Keep two tables synchronized, ensuring 'replica_table' in 'replica_database' mirrors 'source_table' in 'source_database.'

InDriver.sqlExecute("SQLServer", "UPDATE replica_database.replica_table SET replica_column = (SELECT source_column FROM source_database.source_table);");

3. Data Synchronization:

Keep 'main_table' in 'main_database' synchronized with 'mirror_table' in 'mirror_database.'

InDriver.sqlExecute("SQLServer","UPDATE main_database.main_table SET main_column = (SELECT mirror_column FROM mirror_database.mirror_table);");

4. Data Archiving:

Archive old records from 'active_data' to 'archive_data' for better performance.

InDriver.sqlExecute("SQLServer","INSERT INTO archive_data SELECT * FROM active_data WHERE record_date < '2023-01-01';");

5. Backup and Restore:

Automate database backup and restoration procedures. (example for PostgreSQL)

ProcessApi.start(pg_dump -U username -h localhost databasename >> sqlfile.sql");

ProcessApi.start("pg_restore -U username -d dbname -1 filename.dump");

6. Schema Changes:

Add a new column 'new_column' to 'existing_table.'

InDriver.sqlExecute("ALTER TABLE existing_table ADD new_column INT;");

7. Data Cleansing:

Remove duplicates from 'customer_data.'

InDriver.sqlExecute("DELETE FROM customer_data WHERE rowid NOT IN (SELECT MIN(rowid) FROM customer_data GROUP BY customer_id);");

8. Data Enrichment:

Add 'additional_info' from 'external_data' to 'customer_data.'

InDriver.sqlExecute("UPDATE customer_data SET additional_info = (SELECT additional_info FROM external_data WHERE customer_id = customer_data.customer_id);");

9. Data Masking:

Anonymize 'sensitive_data' by replacing it with a hash.

InDriver.sqlExecute("UPDATE sensitive_data SET sensitive_column = SHA256(sensitive_column);");

10. Audit Trail Creation:

Track changes in 'important_data' with an audit trail.

InDriver.sqlExecute("CREATE TABLE audit_trail AS SELECT * FROM important_data;");

11. Performance Monitoring:

Log execution times for queries in 'performance_log.'

InDriver.sqlExecute("INSERT INTO performance_log (query, execution_time) VALUES ('SELECT * FROM performance_data', GETDATE());");

12. Query Optimization:

Optimize a slow-performing query.

InDriver.sqlExecute("CREATE INDEX idx_name ON performance_data (column_name); ");

13. User Access Management:

Grant 'read' access to 'user' on 'sensitive_data.'

InDriver.sqlExecute("GRANT SELECT ON sensitive_data TO user");

14. Capacity Planning:

Analyze database capacity and adjust storage.

InDriver.sqlExecute("SELECT * FROM sys.database_files;");

15. Data Warehouse Loading:

Load data into 'data_warehouse' from 'source_data.'

InDriver.sqlExecute("INSERT INTO data_warehouse SELECT * FROM source_data;");

16. ETL Processes:

Automate the extraction, transformation, and loading of data.

17. Data Validation:

Ensure 'validated_data' adheres to specified criteria.

InDriver.sqlExecute("SELECT * FROM validated_data WHERE column_name IS NOT NULL;");

18. Notification Systems:

Alert administrators when 'critical_data' exceeds a threshold.

InDriver.sqlExecute("SELECT customer_name, ts, sla FROM sla_data WHERE sla_pecentage < 98%;");

19. Data Comparison:

Compare 'source_data' with 'target_data' for discrepancies.

InDriver.sqlExecute("SELECT * FROM source_data EXCEPT SELECT * FROM target_data;");

20. Dynamic Reporting Views:

Create a dynamic view 'report_view' for customizable reporting.

InDriver.sqlExecute("CREATE VIEW report_view AS SELECT * FROM dynamic_data WHERE condition = 'met'; ");

22. Data Encryption:

Encrypt 'encrypted_data' using a symmetric key.

InDriver.sqlExecute("CREATE SYMMETRIC KEY symmetric_key WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = 'password'; ");

23. SQL Performance Diagnostics

Measures SQL query execution time to detect poor performance and writes a debug log if execution time exceeds 10 seconds.

let startTs = new Date();

let query = "SELECT test_query";

InDriver.sqlExecute(query);

let stopTs = new Date();

let performanceMs = stopTs-startTs;

if (performanceMs > 10000)

InDriver.debug("Low query performance: " + query + " -> " + performanceMs );