Salam hangat buat semuanya ya.
Disini saya hanya ingin menambahkan script ataupun fungsi restore database melalui bahasa pemrograman Coldfusion dari bacaan saya terdahulu mengenai "Backup Database Ke Dalam File Zip Dengan Coldfusion".
Dimana hasil Backup Database terdahulu menggunakan ekstensi zip dan disini saya akan memberikan script ataupun fungsi restore database dari file zip ke Database yang kita inginkan.
Langkah Pertama
Masukkan Source Code Dibawah ini kedalam editor yang mas bro punya.
<cffunction name="RestoreDatabase" returnType="string" output="false">
<cfargument name="dbsource" type="string" required="true">
<cfargument name="zFilePath" type="string" required="true">
<cfargument name="Eksekusi" type="string" required="true">
<cfzip action="read" file="#zFilePath#" entrypath="data.packet.xml" variable="wddxText" />
<cfwddx action="wddx2cfml" input="#wddxText#" output="qDatabase" />
<cfif arguments.Eksekusi EQ "view">
<!--- View Table in Database --->
<cfset XtempFOrm = "<style type='text/css'>body{ font-family:Verdana, Arial, Helvetica, sans-serif; padding:30px 80px 30px 80px; text-align:left; border-top:20px solid ##006699; margin:0;}h1{ color:##006699; font-size:16px; font-weight:bold;}h2{ color:##006699; font-size:12px; font-weight:bold;}p, label{ color:##666666; font-size:10px;}label.sys { color:##CCCCCC}td { width:50%; vertical-align:top}</style><form action='#CGI.SCRIPT_NAME#' method='post'>">
<cfset XtempFOrm = "#XtempFOrm#<table summary='restore' width='550'><tr><td>">
<cfloop list="#ListSort(StructKeyList(qDatabase), "text")#" index="table">
<cfset XtempFOrm = "#XtempFOrm#<input type='checkbox' name='tablename' id='tablename_#table#' value='#table#' /><label for='tablename_#table#' class='#Left(table,3)#'>#table#</label><br />">
</cfloop>
<cfset XtempFOrm = "#XtempFOrm#</td><td><input type='checkbox' name='delete' id='delete' value='1' /><label for='delete'>Tick the box to show that you understand that the existing data will be deleted!</label><br /><input type='submit' name='submit' value='restore' /></td></tr></table></form>">
<cfreturn XtempFOrm>
<!--- End View Table in Database --->
<cfelseif arguments.Eksekusi EQ "restore">
<!--- Restore Table in Database --->
<cfset LstCekStatus ="CF_SQL_BIGINT,CF_SQL_DATE,CF_SQL_FLOAT,CF_SQL_FLOAT,CF_SQL_INTEGER,CF_SQL_MONEY,CF_SQL_NUMERIC,CF_SQL_REAL,CF_SQL_SMALLINT,CF_SQL_MONEY4,CF_SQL_TINYINT">
<cfset lstStatus ="">
<cfif StructKeyExists(form, "tablename")>
<cfif Not StructKeyExists(form, "delete")>
<cfset lstStatus ="<p>You didn't tick the delete box :)</p>">
<cfelse>
<cfloop list="#StructKeyList(qDatabase)#" index="index">
<cfif ListFindNoCase(form.tablename, index) eq 0>
<cfset lstStatus = "<p>Skipping #index# table</p><br />">
<cfelse>
<cfset lstStatus = "<h2>Restoring #index# table</h2><br />">
<!---<cfdump var="#qDatabase[index].data#">--->
<cfset qSchema = qDatabase[index].schema />
<cfset qData = qDatabase[index].data />
<cfquery name="qDelete_#index#" datasource="#arguments.dbsource#">
DELETE FROM [#index#]
</cfquery>
<cfif ListFindNoCase( ValueList(qSchema.type_name), "int identity" )>
<cfset bIdentityInsert = True />
<cfelse>
<cfset bIdentityInsert = False />
</cfif>
<cfif bIdentityInsert>
<cfquery name="qIDENTITY_INSERT_#index#_ON" datasource="#arguments.dbsource#">
SET IDENTITY_INSERT [#index#] ON
</cfquery>
</cfif>
<cfloop from="1" to="#qData.RecordCount#" index="iCurrentrow">
<cfquery name="qInsert_#index#_#iCurrentrow#" datasource="#arguments.dbsource#">
INSERT INTO [#index#] (
#ValueList(qSchema.column_name)#
)
VALUES (
<cfloop from="1" to="#qSchema.RecordCount#" index="iColumn">
<cfset columnName = qSchema["column_name"][iColumn] />
<cfset columnType = getCFDataType(qSchema["type_name"][iColumn]) />
<cfif ListFindNoCase(LstCekStatus,columnType,",") NEQ 0 AND len(trim(qData[columnName][iCurrentrow])) EQ 0>
NULL
<cfelse>
<cfqueryparam value="#qData[columnName][iCurrentrow]#" cfsqltype="#columnType#" />
</cfif>
<cfif iColumn lt qSchema.RecordCount>,</cfif>
</cfloop>
)
</cfquery>
</cfloop>
<cfif bIdentityInsert>
<cfquery name="qIDENTITY_INSERT_#index#_OFF" datasource="#arguments.dbsource#">
SET IDENTITY_INSERT [#index#] OFF
</cfquery>
</cfif>
</cfif>
</cfloop>
</cfif>
</cfif>
<cfreturn lstStatus>
<!--- End Restore Table in Database --->
</cfif>
</cffunction>
<cffunction name="getCFDataType" returntype="string" hint="I return the cfsqltype for the cfqueryparam function">
<cfargument name="type" required="true" type="string" />
<cfset var private = StructNew() />
<cfswitch expression="#ListFirst(LCase(Trim(Arguments.Type)), ' ')#">
<cfcase value="bigint">
<cfset private.CFDataType = "CF_SQL_BIGINT" />
</cfcase>
<cfcase value="binary,image,sql_variant,sysname,varbinary">
<cfset private.CFDataType = "" />
</cfcase>
<cfcase value="bit">
<cfset private.CFDataType = "CF_SQL_BIT" />
</cfcase>
<cfcase value="char,nchar">
<cfset private.CFDataType = "CF_SQL_CHAR" />
</cfcase>
<cfcase value="smalldatetime">
<cfset private.CFDataType = "CF_SQL_DATE" />
</cfcase>
<cfcase value="decimal">
<cfset private.CFDataType = "CF_SQL_FLOAT" />
</cfcase>
<cfcase value="float">
<cfset private.CFDataType = "CF_SQL_FLOAT" />
</cfcase>
<cfcase value="int">
<cfset private.CFDataType = "CF_SQL_INTEGER" />
</cfcase>
<cfcase value="money">
<cfset private.CFDataType = "CF_SQL_MONEY" />
</cfcase>
<cfcase value="ntext,text">
<cfset private.CFDataType = "CF_SQL_LONGVARCHAR" />
</cfcase>
<cfcase value="numeric,numeric()">
<cfset private.CFDataType = "CF_SQL_NUMERIC" />
</cfcase>
<cfcase value="nvarchar,varchar">
<cfset private.CFDataType = "CF_SQL_VARCHAR" />
</cfcase>
<cfcase value="real">
<cfset private.CFDataType = "CF_SQL_REAL" />
</cfcase>
<cfcase value="smallint">
<cfset private.CFDataType = "CF_SQL_SMALLINT" />
</cfcase>
<cfcase value="smallmoney">
<cfset private.CFDataType = "CF_SQL_MONEY4" />
</cfcase>
<cfcase value="datetime,timestamp">
<cfset private.CFDataType = "CF_SQL_TIMESTAMP" />
</cfcase>
<cfcase value="tinyint">
<cfset private.CFDataType = "CF_SQL_TINYINT" />
</cfcase>
<cfcase value="uniqueidentifier">
<cfset private.CFDataType = "CF_SQL_IDSTAMP" />
</cfcase>
<cfdefaultcase>
<cfthrow message="#ListFirst(LCase(Trim(Arguments.Type)), ' ')#" />
</cfdefaultcase>
</cfswitch>
<cfreturn Trim(private.CFDataType) />
</cffunction>
Lalu Simpan Source Code ini dengan nama file yang berakhiran ".cfm" misalkan "RestoreDatabase.cfm" kedalam direktori wwwroot Coldfusion yang sudah terinstal.
Langkah Kedua
Masukkan Source Code Dibawah ini kedalam editor yang mas bro punya (Masih didalam file "RestoreDatabase.cfm").
<cfoutput>
<!DOCTYPE html">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Import Database Tables</title>
</head>
<body>
<h1>Restore Data</h1>
<!---
Restore a database dari zip file.
#RestoreDatabase("dbsource","DbFile","Action")#
@param dbsource Datasource (DNS). (Required)
@param DbFile Lokasi File dari backup database (dalam zip file).
@ param Action tindakan yang akan dilakukan (view = berfungsi untuk menampilkan isi dari Backup Database file yang ada, dan restore = untuk melakukan restore data dari data yang sudah dipilih)
@author Darwan Leonardo Sitepu (dlns2001@yahoo.com)
@version 1, February 21, 2011
--->
#RestoreDatabase("darwanlns.com","c:\ColdFusion9\wwwroot\BackupDatabase.zip","view")#
<cfif StructKeyExists(form, "tablename")>
#RestoreDatabase("darwanlns.com","c:\ColdFusion9\wwwroot\BackupDatabase.zip","restore")#
</cfif>
</body>
</html>
</cfoutput>
Keterangan :
darwanlns.com = Nama Datasource yang sudah dibuat di Coldfusion
c:\ColdFusion9\wwwroot\BackupDatabase.zip = Alamat dari file Database yang akan direstore
view = Menampilkan Isi dari database yang terdapat di zip file
restore = Melakukan restore database dari pilihan yang sudah dilakukan.
Langkah Ketiga :
Untuk Menjalankan Source code yang sudah mas bro punya, mas bro ketikkan saja alamat di browser mas bro misalkan.
http://localhost:8500/RestoreDatabase.cfm
Maka Contoh Hasil dari source code diatas akan seperti ini
Selamat Mencoba Mas Bro :)
Tidak ada komentar:
Posting Komentar