Difference between revisions of "Temp"

Jump to navigation Jump to search

Temp (view source)

Revision as of 13:51, 7 May 2013

3,272 bytes added ,  13:51, 7 May 2013
cds
(xf)
 
(cds)
 
Line 1: Line 1:
<###################################################################################################################
<# ========================================================================================================================================
Symbox User Migration Utility script
  MySQL function library
  =========================================================================================================================================
  Simon Strutt        Jan 2011
  =========================================================================================================================================


By Simon Strutt - May 2013
  Requires MySQL .NET connector to be installed on machine script is run from (tested with ver 6.2.4)
  Requires Standard.ps1 (for Log func)


Version 0.1
  Version 1  
- Initial creation
  - Initial creation
 
  Version 2
  - Added $SQLnoExit option
 
  Version 3
  - Renamed $LogNonQueries to $LogMySQLNonQueries
  - Added $LogMySQLQueries
 
  Version 4
  - Renamed funcs to more PowerShell standard
 
#>


####################################################################################################################>
function Connect-MySQL([string]$user, [string]$pass, [string]$MySQLHost, [string]$database) {
 
    # Load MySQL .NET Connector Objects
$Symbox_Svr = "tnssymbox"
    [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Symbox_Db = "symbox"
$Symbox_Db_U = "root"
    # Open Connection
$Symbox_DB_P = "dobrutro"
    $connStr = "server=$MySQLHost;port=3306;uid=$user;pwd=$pass;database=$database;Pooling=FALSE"  
 
    try {
$logfile = "symbox_users.log"
        $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
 
        $conn.Open()
$ShortLoop = $true
    } catch [System.Management.Automation.PSArgumentException] {
 
        Log "Unable to connect to MySQL server, do you have the MySQL connector installed..?"
# Pre-amble ------------------------------------------------------------------------------------------------------------------------------
        Log $_
 
        if ($SQLnoExit) {
. .\lib\standard.ps1
            return 0
 
        } else {
Start-Transcript $logfile
            Exit
        }
    } catch {
        Log "Unable to connect to MySQL server..."
        Log $_.Exception.GetType().FullName
        Log $_.Exception.Message
        Log $_
        if ($SQLnoExit) {
            return $false
        } else {
            Exit
        }
    }
    Log ("Connected to MySQL database " + $conn.Database + " on " + $conn.DataSource + " (ver " + $conn.ServerVersion + ")")


Import-Module ActiveDirectory
    return $conn
}


# Connect to Symbox database
function Disconnect-MySQL {
$DB = Connect-MySQL($Symbox_Db_U, $Symbox_DB_P, $Symbox_Svr, $Symbox_Db)
     $SQLconn.Close()
if (!$DB) {
     Log ("Connection to database failed") 1
    Exit
}
}


# Get list of users in database
function Execute-MySQLNonQuery([string]$query) {
$Users = Execute-MySQLQuery ("SELECT * FROM tbl_users;")
    # NonQuery - Insert/Update/Delete query where no return data is required
if ($Users.count -gt 1) {
   
     Log ("Got " + $Users.count + " users from Symbox db to review/process")
    if ($LogMySQLNonQueries) {
} else {
        Log ($query)
    Log ("Error getting users from database") 1
    }
     Exit
   
    try {
        $cmd = $SQLconn.CreateCommand()              # Create command object
        $cmd.CommandText = $query                    # Load query into object
        $RowsInserted = $cmd.ExecuteNonQuery()        # Execute command
        $cmd.Dispose()                               # Dispose of command object
        if ($RowsInserted) {  
            return $RowInserted
        } else {
            return $false
        }
     } catch {
        Log ("Execute-MySQLNonQuery($query) error...")
        Log $_.Exception.GetType().FullName
        Log $_.Exception.Message
        Log $_
        if ($SQLnoExit) {
            return $false
        } else {
            Exit
        }
     }
}
}


foreach ($user in $Users) {
function Execute-MySQLQuery([string]$query) {  
     if (!$user.user_id) {
    # Query - Select etc query where return data is expected
         Continue
   
     if ($LogMySQLQueries) {
         Log ($query)
     }
     }
     Log ("Processing user_id " + $user.user_id + ", user_login " + $user.user_login)
      
    $AdUser = Get-ADUser -Filter {Description -like ("* [" + $user.user_login + " | *")}
    try {
    if (!$AdUser) {
        $cmd = $SQLconn.CreateCommand()                                            # Create command object
         Log ("User login not found in AD as EU imported login")
        $cmd.CommandText = $query                                                  # Load query into object
         Continue
        $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)     # Create data adapter from query command
        $dataSet = New-Object System.Data.DataSet                                  # Create dataset
        $dataAdapter.Fill($dataSet, "data")                                        # Fill dataset from data adapter, with name "data"            
        $cmd.Dispose()
        $dataAdapter.Dispose()
        return $dataSet.Tables["data"]                                              # Returns an array of results
    } catch {
        Log ("Execute-MySQLQuery($query) error...")
         Log $_.Exception.GetType().FullName
        Log $_.Exception.Message
        Log $_
        if ($SQLnoExit) {
            return $false
         } else {
            Exit
        }
     }
     }
}


    $query = "UPDATE tbl_users SET user_login=" + $AdUser.SamAccountName + " WHERE user_id=" + $user.user_id + ";"
function Execute-MySQLScalar([string]$query) {
    Log $query
    Execute-MySQLNonQuery($query)


     if ($ShortLoop) {
     if ($LogMySQLQueries) {
         Log ("Breaking out of user processing...")
        Log ($query)
         Break
    }
   
    # Scalar - Select etc query where a single value of return data is expected
    try {
        $cmd = $SQLconn.CreateCommand()                                            # Create command object
        $cmd.CommandText = $query                                                  # Load query into object
        $cmd.ExecuteScalar()                                                        # Execute command
    } catch {
         Log ("Execute-MySQLScalar($query) error...")
         Log $_.Exception.GetType().FullName
        Log $_.Exception.Message
        Log $_
        if ($SQLnoExit) {
            return $false
        } else {
            Exit
        }
     }
     }
}
}


Disconnect-MySQL
function Escape-MySQLText([string]$text) {
    $text = [regex]::replace($text, "\\", "\\")
    [regex]::replace($text, "'", "\'")
}
Retrieved from ‘https://vwiki.co.uk/index.php?title=Special:MobileDiff/2299’

Navigation menu