Using CFDBINFO and CFZIP for quick database backups
All major database products have tools to let you backup their databases. MySQL makes it super simple with their command line tools. But what if you want to do it with ColdFusion? Doesn't everyone want to do everything with ColdFusion? I know I do! So let's look at a quick example.
My database backup code will work like so:
- Get a list of tables from a datasource.
- Select all rows from the table.
- Convert the query into WDDX.
- Zip the giant XML string and store the result.
The code for all this is incredibly simple:
<cfset datasource="blogdev">
<cfdbinfo datasource="#datasource#" name="tables" type="tables">
This code simply uses the new cfdbinfo tag to query the tables from the datasource.
<!--- One struct to rule them all... --->
<cfset data = structNew()>
I'm going to store all my queries in one struct.
<cfloop query="tables">
<!--- grab all data from table --->
<cfquery name="getData" datasource="#datasource#">
select *
from #table_name#
</cfquery>
<cfset data[table_name] = getData>
</cfloop>
Then I loop over each table and select *. Notice I store the query into the struct. By the way - the cfdbinfo tag also lets you get the columns from a database table. But since this is a "quickie" script, I don't mind using the select *.
<!--- Now serialize into one ginormous string --->
<cfwddx action="cfml2wddx" input="#data#" output="packet">
Then we convert the structure into one XML packet.
<!--- file to store zip --->
<cfset zfile = expandPath("./data.zip")>
<!--- Now zip this baby up --->
<cfzip action="zip" file="#zfile#" overwrite="true">
<cfzipparam content="#packet#" entrypath="data.packet">
</cfzip>
Next I store the string into a zip using the new cfzip and cfzipparam tags. Notice how I feed the string data to the zip using cfzipparam. I don't have to store the text into a temporary file.
<cfoutput>
I retrieved #tables.recordCount# tables from datasource #datasource# and saved it to #zfile#.
</cfoutput>
The last thing I do is output a simple result message so you know how much data was backed up. Here is the complete source in one listing:
<cfset datasource="blogdev">
<cfdbinfo datasource="#datasource#" name="tables" type="tables">
<!--- One struct to rule them all... --->
<cfset data = structNew()>
<cfloop query="tables">
<!--- grab all data from table --->
<cfquery name="getData" datasource="#datasource#">
select *
from #table_name#
</cfquery>
<cfset data[table_name] = getData>
</cfloop>
<!--- Now serialize into one ginormous string --->
<cfwddx action="cfml2wddx" input="#data#" output="packet">
<!--- file to store zip --->
<cfset zfile = expandPath("./data.zip")>
<!--- Now zip this baby up --->
<cfzip action="zip" file="#zfile#" overwrite="true">
<cfzipparam content="#packet#" entrypath="data.packet">
</cfzip>
<cfoutput>
I retrieved #tables.recordCount# tables from datasource #datasource# and saved it to #zfile#.
</cfoutput>