One of my co-workers actually did the insertions using a powershell script. He removed the names of our servers/databases/passwords, but you should be able to see what he was doing with the attached script.
A few bits to point out:
- near the end of the script you will see /path/to/homes… replace that with whatever your home directory path on the sftp server equals.
- “Vault Desktop” is the label for the external storage we give the students. This can be whatever you want, it is just what we are calling it.
Hope this information helps:
function Connect-MySQL {
Param(
[Parameter(Mandatory = $true)][string]$Username,
[Parameter(Mandatory = $true)][string]$Password,
[Parameter(Mandatory = $true)][string]$Hostname,
[Parameter(Mandatory = $true)][string]$Database
)
$ConnectionString = "server=" + $Hostname + ";port=3306;uid=" + $Username + ";pwd=" + $Password + ";database="+$Database
Try {
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = $ConnectionString
$Connection.Open()
Write-Host "`nConnected to $Database on $Hostname as $Username`n" -ForegroundColor cyan
$Connection
}
Catch {
Write-Host "Unable to connect to $Database on $Hostname using $Username`n$($Error[0])" -ForegroundColor red
}
}
function Get-MySQL {
Param(
[Parameter(Mandatory = $true)][string]$Query,
[Parameter(Mandatory = $true)]$Connection
)
Try {
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
$DataSet.Tables[0]
}
Catch {
Write-Host "Error with query '$Query'`n$($Error[0])" -ForegroundColor red
}
}
function Disconnect-MySQL {
Param(
[Parameter(Mandatory = $true)]$Connection
)
$Connection.Close()
Write-Host "`nConnection to $($Connection.DataSource) closed`n" -ForegroundColor cyan
}
$ProdDBConnection = @{ Username=“dbuser”;Password=“dbpass”;Database=“dbname”;Hostname=“dbhost” }
if ($c = Connect-MySQL @ProdDBConnection) {
$mountID = (Get-MySQL -Connection $c -Query "SELECT MAX(mount_id) AS maxmount FROM oc_external_applicable").maxmount
$accounts = Get-MySQL -Connection $c -Query "SELECT * FROM oc_accounts WHERE uid NOT IN (SELECT a.value FROM oc_external_applicable AS a LEFT JOIN oc_external_mounts AS m ON a.mount_id = m.mount_id WHERE m.mount_point = '/Vault Desktop')"
ForEach ($account in $accounts) {
$accountID = $account.uid
if ($accountID -match("^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$")) {
$mountID++
$accountUsername = ($account.data | ConvertFrom-JSON).email.value.Split("@")[0]
Write-Host ("[{0}] {1} - {2}" -f $mountID.ToString("000000"),$accountID,$accountUsername)
Get-MySQL -Connection $c -Query "INSERT INTO oc_external_applicable (``mount_id``,``type``,``value``) VALUES ('$mountID',3,'$accountID');"
Get-MySQL -Connection $c -Query "INSERT INTO oc_external_options (``mount_id``,``key``,``value``) VALUES ($mountID,'encrypt','true'),($mountID,'previews','true'),($mountID,'enable_sharing','false'),($mountID,'filesystem_check_changes','1'),($mountID,'encoding_compatibility','false');"
Get-MySQL -Connection $c -Query "INSERT INTO oc_external_mounts (``mount_id``,``mount_point``,``storage_backend``,``auth_backend``,``priority``,``type``) VALUES ($mountID,'/Vault Desktop','sftp','password::sessioncredentials',100,2)"
Get-MySQL -Connection $c -Query "INSERT INTO oc_external_config (``mount_id``,``key``,``value``) VALUES ($mountID,'host','vault'),($mountID,'root','/path/to/homes/$accountUsername')"
}
}
Disconnect-MySQL -Connection $c
}