' ms2pg.vbs - Copyright 2003-2011 David Busby & Edoceo, Inc ' Released under MIT License ' ' Uses ADO Objects to write 'CREATE TABLE' SQL statements and copy a table over ' http://edoceo.com/creo/ms2pg ' ' Usage: ' cscript //NoLogo //S ' cscript ms2pg.vbs /sdatabase:[source_database] /dhostname:[host] ' cscript ms2pg.vbs [options] /sc:"" /sd:"" ' ' @see http://www.connectionstrings.com/ ' option explicit 'Operational Global Options dim g_create_only: g_create_only=false dim g_flush: g_flush=false dim g_lower: g_lower=false dim g_pretend: g_pretend=false dim g_timeself: g_timeself=false dim g_verbose: g_verbose = false dim g_s_hostname: g_s_hostname = "." dim g_s_database: g_s_database = vbNullString dim g_s_username: g_s_username = "sa" dim g_s_password: g_s_password = vbNullString dim g_s_table: g_s_table = vbNullString dim g_d_hostname: g_d_hostname = "." dim g_d_database: g_d_database = vbNullString dim g_d_username: g_d_username = "postgres" dim g_d_password: g_d_password = vbNullString dim g_start_time: g_start_time=now() dim g_c_table: g_c_table=0 dim g_c_row: g_c_row=0 dim g_c_pk: g_c_pk=0 dim g_c_ix: g_c_ix=0 ' Go! do_options dim s_db: set s_db = db_connect("s") dim d_db: set d_db = db_connect("d") copy_database s_db if (g_timeself) then do_stats ' FUNC: db_connect - connection wrapper for source and destination database function db_connect(byval which) dim c,cs set c = createobject("adodb.connection") c.commandtimeout = 20 ' Adjust if necessary c.connectiontimeout = 30 ' Adjust if necessary if (which = "s") then cs = "Provider=sqloledb; " & _ "Data Source=" & g_s_hostname & ";" & _ "Database=" & g_s_database & "; " & _ "User ID=" & g_s_username & ";" & _ "Password=" & g_s_password & ";" if (g_verbose) then wscript.echo "ConnectionString: " & cs end if c.mode = 1 'on error resume next c.open cs 'on error goto 0 elseif (which = "d") then cs = "Driver=PostgreSQL;" & _ "Server=" & g_d_hostname & ";" & _ "Database=" & g_d_database & ";" & _ "User ID=" & g_d_username & ";" & _ "Password=" & g_d_password & ";" if (g_verbose) then wscript.echo "ConnectionString: " & cs if (g_pretend) then wscript.echo "-- Destination: " & cs else c.mode = 3 on error resume next c.open cs on error goto 0 end if end if if (g_pretend=false) then if (c.state <> 1) then wscript.echo "Could not connect to database" & vbcrlf & " " & cs wscript.quit 1 end if end if set db_connect = c end function ' copy_database - dumps all tables from the given database connection sub copy_database(byref dbh) dim sql,f,rs,t,tbl_list ' Build list of tables to copy ' NOTE: This parameter needs to be wrapped with double quotes, other parameters for these sp_* functions don't. ' Enjoy consistent behavior /djb sql = "sp_tables @table_type=" & chr(34) & "'TABLE'" & chr(34) set rs = dbh.execute(sql,,1) do while rs.eof = false if (len(g_s_table)) then if (lcase(rs("table_name")) = lcase(g_s_table)) then tbl_list = tbl_list & rs("table_name") & ":" end if elseif (lcase(rs("table_name")) <> "dtproperties") then tbl_list = tbl_list & rs("table_name") & ":" end if rs.Movenext loop tbl_list = left(tbl_list,len(tbl_list)-1) ' Copy table structure for each t in split(tbl_list,":") do_table(t) next ' Copy Primary Keys for each t in split(tbl_list,":") do_pkey(t) next ' Copy Indexes for each t in split(tbl_list,":") do_index(t) next ' Copy Foreign Keys ' NOTE: sp_fkeys @table_name='something' ' Copy Views ' note: select * from INFORMATION_SCHEMA.VIEWS end sub function do_table(byval t) dim f,rs,sql if (g_flush) then if (g_verbose) then wscript.echo "Destroying destination table: " & t ' TODO: Need to make this have real error checking, existence check? ' on error resume next, use psql -e to figure out sql = "drop table " & munge_case(t) if (g_pretend) then wscript.echo "-- Drop Table: " & t wscript.echo sql & " ;" else d_db.execute "drop table " & t,,1 end if end if ' Create Table if (g_verbose) then wscript.echo "Copying Table: " & t sql = "create table " & munge_case(t) & " (" set rs = s_db.execute("sp_columns @table_name='" & t & "'",,1) do while (rs.eof=false) sql = sql & " " & munge_case(rs("column_name")) & " " & munge_datatype(rs) & "," rs.movenext loop sql = left(sql,len(sql)-1) & " ) without oids; " if (g_pretend) then wscript.echo "-- Create Table: " & t wscript.echo sql & " ;" else d_db.execute sql,,1 end if g_c_table=g_c_table+1 ' Don't Copy Data if (g_create_only) then exit function end if ' Copy Data outVerbose "Copying Data: " & t set rs = s_db.execute("select * from " & t,,1) do while (rs.eof=false) sql = "insert into " & munge_case(t) & " (" for each f in (rs.fields) sql = sql & munge_case(f.name) & "," next sql = left(sql,len(sql)-1) & ") values (" for each f in (rs.fields) sql = sql & munge_data(f) & "," next sql = left(sql,len(sql)-1) & ")" 'wscript.echo "sql: " & sql if (g_pretend) then wscript.echo sql & " ;" else d_db.execute sql,,1 end if g_c_row=g_c_row+1 rs.movenext loop end function 'FUNC: do_pkey - copies the primary key for the table 'NOTE: sp_pkeys @table_name='something' sub do_pkey(byval t) dim rs,sql if (g_verbose) then wscript.echo "Primary Keys: " & t set rs = s_db.execute("sp_pkeys @table_name='" & t & "'",,1) if (rs.eof=false) then sql="alter table only " & munge_case(t) & " add constraint " & munge_case(rs("pk_name")) & "_pkey primary key (" do while (rs.eof=false) sql=sql & munge_case(rs("column_name")) & "," rs.movenext loop sql=left(sql,len(sql)-1) & ")" if (g_pretend) then wscript.echo sql & " ;" else d_db.execute sql,,1 end if g_c_pk=g_c_pk+1 end if end sub 'FUNC: do_index - copies the indexes for the table 'NOTE: sp_special_columns @table_name='something' or sp_statistics @table_name='something' sub do_index(byval t) Dim c,col,i,ixl,pkl,rs,sql,u if (g_verbose) then wscript.echo "Copying Indexes: " & t ' Collect primary key list to not duplicate those set rs = s_db.execute("sp_pkeys @table_name='" & t & "'",,1) do while (rs.eof=false) if (rs("pk_name").actualsize) then pkl=pkl & rs("pk_name") & ":" rs.movenext loop if (len(pkl)>0) then pkl=left(pkl,len(pkl)-1) ' Build Index List, ignore pkl set rs = s_db.execute("sp_statistics @table_name='" & t & "'",,1) do while (rs.eof=false) if (rs("index_name").actualsize) then 'Not already in the list if (instr(1,ixl,rs("index_name") & ":")=0) then ' Not something to ignore if (instr(1,pkl,rs("index_name"))=0) then ixl=ixl & rs("index_name") & ":" end if end if end if rs.movenext loop if (len(ixl)>0) then ixl=left(ixl,len(ixl)-1) 'wscript.echo "ixl:" & ixl for each i in split(ixl,":") col=vbnullstring set rs = s_db.execute("sp_statistics @table_name='" & t & "', @index_name='" & i & "'",,1) do while (rs.eof=false) if (rs("column_name").actualsize) then if (rs("non_unique")=0) then u=true col=col & rs("column_name") & ":" end if rs.movenext loop if (len(col)>0) then col=left(col,len(col)-1) 'wscript.echo "col: " & col sql="create " if (u) then sql = sql & "unique " sql=sql & "index ix_" & munge_case(t) & "_" for each c in split(col,":") sql=sql & munge_case(c) next sql=sql & " on " & munge_case(t) & " (" for each c in split(col,":") sql=sql & munge_case(c) & "," next sql = left(sql,len(sql)-1) & ")" next if (g_pretend) then wscript.echo sql & " ;" else d_db.execute sql,,1 end if g_c_ix=g_c_ix+1 end if 'If previous index not this one then make new sql 'if (pix<>rs("index_name")) then ' sql = "create index ix_" & munge_case(t) & "_" 'end if ' Always add to the column list ' col=col & munge_case(rs("column_name")) & "," 'wscript.echo "col: " & col 'wscript.echo "exec:" & sql ' pix = rs("index_name") ' rs.movenext 'loop end sub ' FUNC: munge_case - converts to lower if necessary function munge_case(byval s) if (g_lower) then munge_case = lcase(s) else munge_case = s end if end function ' FUNC: munge_data - turns column data into an understandable format for an insert statement to postgres ' See: ADO DataTypeEnum function munge_data(byref f) dim x if (f.actualsize=0) then munge_data = "null" ' Numerice types elseif (f.type=20 or f.type=6 or f.type=5 or f.type=3 or f.type=4 or f.type=2 or f.type=17 or f.type=131) then munge_data = f.value ' Character types 129=char,130=nchar,200=varchar,201=text,202=nvarchar,203=ntext elseif (f.type=129 or f.type=130 or f.type=200 or f.type=201 or f.type=202 or f.type=203) then x = f.value x = replace(x,"\","\\") x = replace(x,"'","\'") munge_data = "'" & x & "'" ' date elseif (f.type=135) then munge_data = "'" & f.value & "'" ' bit/boolean elseif (f.type=11) then if (f.value=1) then munge_data="'t'" else munge_data="'f'" end if 'elseif (f.type=72) then else wscript.echo "munge_data: unhandled data type: " & vbCrlf & " Column: " & f.name & vbcrlf & " Type: " & f.type & vbcrlf & " Precision: " & f.precision & vbcrlf & " Scale: " & f.numericscale wscript.quit 1 end if end function ' FUNC: munge_datatype - maps the type to postgres function munge_datatype(byref rs) dim ret ' Datatypes in alphabetical order select case lcase(rs("type_name")) case "bigint" '// adBigInt == int8 ret = "int8" case "bigint identity" ret = "serial8" case "binary" ret = "bytea" case "bit" ret = "boolean" case "char" ret = "char(" & rs("length") & ")" case "datetime" ret = "timestamp without time zone" case "decimal" ret = "numeric(" & rs("precision") & "," & rs("scale") & ")" case "float" ret = "double precision" case "image" ret = "bytea" case "int" ret = "integer" case "int identity" ret = "serial" case "money" ' NOTE: PostgreSQL has a type called money but it's depreciated so don't f-ing use it ret = "numeric(19,4)" case "nchar" ' note: see UNICODE warning on the homepage ret = "char(" & rs("length") & ")" case "ntext" ' note: see UNICODE warning on the homepage ret = "text" case "numeric" ret = "numeric(" & rs("precision") & "," & rs("scale") & ")" case "nvarchar" ' note: see UNICODE warning on the homepage ret = "varchar(" & rs("length") & ")" case "real" ret = "double precision" case "smalldatetime" ret = "timestamp without time zone" case "smallint" ret = "smallint" ' Two byte integer case "smallint identity" ret = "serial" case "smallmoney" ' See comments for money, we upgrade size too ret = "numeric(19,4)" 'case "sql_variant" ' Useless! case "text" ret = "text" 'case "timestamp" ' Error ' One byte integer, junk! case "tinyint" ret = "smallint" case "tinyint identity" ret = "serial" case "uniqueidentifier" ret = "char(36)" case "varbinary" ret = "bytea" case "varchar" ret = "varchar(" & rs("length") & ")" case else wscript.echo "Unhandled Data Type: " & rs("type_name") & " in table " & rs("table_name") wscript.quit 1 end select 'Nulls? if (rs("nullable")=0) then ret = ret & " not null" munge_datatype = ret end function ' FUNC: do_options - parses command line options sub do_options dim arg if (instr(1,lcase(wscript.fullname),"cscript.exe")=0) then wscript.echo "Must be run under cscript or you'll be mad!" wscript.quit 1 end if for each arg in wscript.arguments.unnamed if (arg="-c") then g_create_only=true elseif (arg="-f") then g_flush=true elseif (arg="-l") then g_lower=true elseif (arg="-p") then g_pretend=true elseif (arg="-t") then g_timeself=true elseif (arg="-v") then g_verbose=true end if next with wscript.arguments for each arg in .named 'wscript.echo "Argument: " & arg & " = " & wscript.arguments.named(arg) ' Source if (arg = "shostname") then g_s_hostname = .named(arg) elseif (arg = "sdatabase") then g_s_database = .named(arg) elseif (arg = "susername") then g_s_username = .named(arg) elseif (arg = "spassword") then g_s_password = .named(arg) ' Destination elseif (arg = "dhostname") then g_d_hostname = .named(arg) elseif (arg = "ddatabase") then g_d_database = .named(arg) elseif (arg = "dusername") then g_d_username = .named(arg) elseif (arg = "dpassword") then g_d_password = .named(arg) ' Single Table elseif (arg = "table") then g_s_table = .named(arg) end if next end with if (len(g_s_database)=0) then wscript.echo "A Source database name must be specified say /sdatabase:" wscript.quit 1 end if ' No Destination database name? Use Source database name if (Len(g_d_database)=0) then g_d_database = lcase(g_s_database) end if end sub sub do_stats dim buf,d,stop_time stop_time=now wscript.echo wscript.echo "Start Time: " & g_start_time wscript.echo "Stop Time: " & stop_time buf="ms2pg time: " 'Hours? d=datediff("h",g_start_time,stop_time) if (d) then buf = buf & d & " hours " g_start_time=dateadd("h",d,g_start_time) end if 'Minutes d=datediff("n",g_start_time,stop_time) if (d) then buf = buf & d & " minutes " g_start_time=dateadd("n",d,g_start_time) end if ' Seconds d=datediff("s",g_start_time,stop_time) if (d) then buf = buf & d & " seconds" else buf = buf & "0 seconds" end if buf = buf & " to complete" & vbcrlf buf = buf & "ms2pg stat: " & g_c_table & " tables, " & g_c_row & " rows, " & g_c_pk & " primary keys, " & g_c_ix & " indexes" wscript.echo buf end sub ' outTrace ' @param s output to write if Tracing enabled sub outTrace(byval s) 'if (g_trace) ' wscript.echo "v:" & s 'end if end sub sub outVerbose(byval s) if (g_verbose) then wscript.echo "v:" & s end if end sub