An Improved Approach to Generating Configuration Files from a Database

1. An Improved Approach to Generating Configuration Files from a Database Jon Finke Rensselaer Polytechnic Institute

2. Conclusions • Cuts development time • Standard development environment • Host interface written once • Code sharing with web routines • All work done centrally • PL/SQL well integrated to Database • Platform independence

3. The Old Way • Custom program for each file type. • Common file system • Small number of platforms • Standard development environment • Lots of cut and paste C coding • Direct access to tables and views

4. SQL*NET SQL*NET File Format Logic File Format Logic Version Control Version Control Old Model Relational Database

5. The Problems • Loss of common file system • Harder to distribute custom programs • Multiple development environments • Program logic unavailable to web applications. • Minor file format changes require too much work.

6. Relational Database File Format Logic File Format Logic File Format Logic Switcher SQL*NET SQL*NET Version Control Version Control New Model

7. New Way • File format logic stored in DB • Changes made centrally • Generic file extraction program • No changes after installation • Platform independence • Code sharing with web applications • PL/SQL programs are shorter • Faster to write

8. Stored Procedures and Packages • PL/SQL • Procedural extension to SQL • Simple Procedure or Function • Package • Set of procedures and functions • maintains state • Stored and executed by database server

9. PL/SQL • Variables and Constants • SQL built in functions • String, Numeric, Date • Conditionals • Looping Constructs • Exception Handling • Records

10. Generic Program Get Filename Start Null? Done Version? Open File Get Line Null? Write Line

11. “Switcher” functions • Standard API for generic program • Provides access control • Provide filename for simple cases • Also direction, and other attributes • Grouping of multiple simple files • Environment info for specific file generator routines.

12. Switcher Return Null Get_Filename Record? Get Next Target Rec Files>0? Open? Access OK? Return Error Open Target Rec Simple? Return Rec.Filename Return Rec.GetFname()

13. Target Record • Target Name, Sequence Number • Access List • Get Data Routine Name • Get FileName Routine Name (C) • FileName (S) • Direction, DBMS_OUT Flag (S) • Version Info (S)

14. Get_Data (1) Cursor Get_Records is Select Username, Uid Gid, Gecos, Shell, Path from Logins; . . . Procedure Get_Data(Result out varchar2) is R Get_Records%RowType begin if not Get_Records%IsOpen then Open Get_Records; end if;

15. Get_Data (2) Fetch Get_Records into R; if Get_Records%NotFound then Close Get_Records; Result := Null; else Result := R.Username || ‘:*:’ || R.Uid || ‘:’ || R.Gid || ‘:’ || R.Gecos || ‘:’ || R.Path || ‘:’ || R.shell; end if; return;

16. Complex Targets • Get_FileName • Like Get_Data • Stashes context info for Get_Data • Provides version info (optionally) • Keep calling until no more files. • For each file, call Get_Data until no more data.

17. More Conclusions • Replacing existing custom programs • Loading data as well as extraction • Handler for back end processing • File “STDOUT” • Wish I had done this long ago

18. Package Design File Gen Web Server WP File WP Web WP Table Routines Relational Database Tables

19. Files Currently Generated • White Pages • LaTeX, HTML, LDIF, ph, CSV • Hostmaster • Resource Record (bind) • Hostfile • DHCP Config • TSM Billing • Error Reports

20. Files Currently Loaded • TSM (Backup/archive) • Accounting Records • White Pages • CSV staff list (remote campus) • Terminal Server • Accounting logs • lpd (printing) • Accounting logs

21. Futures • GET from program • PUT to program • Boilerplate management • Call file generation from Web • Faster development for web pages • Alternate Boilerplate testing • More platforms

22. An Improved Approach to Generating Configuration Files from a Database Jon Finke Rensselaer Polytechnic Institute finkej@rpi.edu http://www.rpi.edu/~finkej