Transcript An Improved Approach to Generating Configuration Files from a

An Improved Approach to
Generating Configuration Files from
a Database
Jon Finke
Rensselaer Polytechnic Institute
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
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
Old Model
Relational Database
SQL*NET
SQL*NET
File Format Logic
File Format Logic
Version Control
Version Control
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.
New Model
Relational Database
File Format Logic File Format Logic File Format Logic
Switcher
SQL*NET
SQL*NET
Version Control
Version Control
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
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
PL/SQL
• Variables and Constants
• SQL built in functions
– String, Numeric, Date
•
•
•
•
Conditionals
Looping Constructs
Exception Handling
Records
Generic Program
Start
Get Filename
Done
Null?
Version?
Open File
Get Line
Write Line
Null?
“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.
Get_Filename
Open?
Switcher
Get Next
Target Rec
Record?
Access
OK?
Open
Target Rec
Return
Null
Files>0?
Return
Error
Simple?
Return
Rec.Filename
Return
Rec.GetFname()
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)
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;
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;
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.
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
Package Design
File Gen
Web Server
WP File
WP Web
WP Table Routines
Relational Database Tables
Files Currently Generated
• White Pages
– LaTeX, HTML, LDIF, ph, CSV
• Hostmaster
– Resource Record (bind)
– Hostfile
– DHCP Config
• TSM Billing
– Error Reports
Files Currently Loaded
• TSM (Backup/archive)
– Accounting Records
• White Pages
– CSV staff list (remote campus)
• Terminal Server
– Accounting logs
• lpd (printing)
– Accounting logs
Futures
•
•
•
•
GET from program
PUT to program
Boilerplate management
Call file generation from Web
– Faster development for web pages
– Alternate Boilerplate testing
• More platforms
An Improved Approach to
Generating Configuration Files from
a Database
Jon Finke
Rensselaer Polytechnic Institute
[email protected]
http://www.rpi.edu/~finkej