' 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:<database_name>"
    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