Using PowerShell I have made a class based Kijiji webscraper that records progress in a MariaDB table (for use in another project). I had made this as a module with cmdlets months ago but I wanted to revamp it using PowerShell Classes while I was at it.
While they are not as good as C# classes this is baby steps for me. The biggest let down in my mind is that I have little to no documentation accessible to the end user like I would with custom cmdlets. External help will be needed for this.
#requires -modules SimplySQL
Add-Type -AssemblyName System.Web
class DatabaseConnectionProperties{
# Simple class to control database connection parameters
# Variables
[string]$Server
[int]$Port
[string]$Database
[System.Management.Automation.PSCredential]$Credential
# Constructors
DatabaseConnectionProperties(
[string]$Server,
[int]$Port,
# Database Name
[string]$Database,
# Authentication Credentials
[System.Management.Automation.PSCredential]$Credential
){
$this.Server = $Server
$this.Port = $Port
$this.Database = $Database
$this.Credential = $Credential
}
# Return a hashtable of non null/empty class properties
# This can be used for splatting to a db connection
[hashtable] toHashTable(){
$hashtable = @{}
if(-not [string]::IsNullOrEmpty($this.server)){$hashtable.Server = $this.Server}
if($this.Port){$hashtable.Port = $this.Port}
if(-not [string]::IsNullOrEmpty($this.Database)){$hashtable.Database = $this.Database}
if(-not [string]::IsNullOrEmpty($this.Credential)){$hashtable.Credential = $this.Credential}
return $hashtable
}
}
class KijijiListing{
[int]$id
[uri]$url
[string]$price
[string]$title
[string]$distance
[string]$location
[datetime]$posted
[string]$shortdescription
[datetime]$lastsearched
[int]$searchURLID
[uri]$imageurl
[int]$discovered
[byte[]]$image
[string]$changes
static [string[]]$ComparePropertiesToIgnore = "lastsearched","posted","discovered","searchURLID","changes"
static [string]$kijijiDateFormat = "dd/MM/yyyy" # Date time format template
static $parsingRegexes = @{
id = '(?sm)data-ad-id="(\w+)"'
url = '(?sm)data-vip-url="(.*?)"'
price = '(?sm)<div class="price">(.*?)</div>'
image = '(?sm)<div class="image">.*?<img src="(.*?)"'
title = '(?sm)<div class="title">.*?">(.*?)</a>'
distance = '(?sm)<div class="distance">(.*?)</div>'
location = '(?sm)<div class="location">(.*?)<span'
postedTime = '<span class="date-posted">(.*?)</span>'
description = '(?sm)<div class="description">(.*?)<div class="details">'
}
KijijiListing([string]$HTML,[int]$SearchUrlID,[datetime]$Processed){
# Use the raw html of a listing and parse out the present properties.
$this.iD = if($HTML -match [KijijiListing]::parsingRegexes["id"]){$matches[1]};
$this.uRL = if($HTML -match [KijijiListing]::parsingRegexes["url"]){$matches[1]};
$this.price = if($HTML -match [KijijiListing]::parsingRegexes["price"]){$matches[1].trim().trimstart('$')};
$this.title = if($HTML -match [KijijiListing]::parsingRegexes["title"]){[System.Web.HttpUtility]::HtmlDecode($matches[1].trim())};
$this.distance = if($HTML -match [KijijiListing]::parsingRegexes["distance"]){[System.Web.HttpUtility]::HtmlDecode($matches[1].trim())};
$this.location = if($HTML -match [KijijiListing]::parsingRegexes["location"]){[System.Web.HttpUtility]::HtmlDecode($matches[1].trim())};
$this.posted = if($HTML -match [KijijiListing]::parsingRegexes["postedTime"]){
[KijijiListing]::ConvertFromKijijiDate([System.Web.HttpUtility]::HtmlDecode($matches[1].trim()),$Processed)
}
$this.shortDescription = if($HTML -match [KijijiListing]::parsingRegexes["description"]){[System.Web.HttpUtility]::HtmlDecode($matches[1].trim())};
$this.imageURL = if($HTML -match [KijijiListing]::parsingRegexes["image"]){$matches[1]};
$this.searchURLID = $SearchUrlID
$this.lastsearched = $Processed
$this.discovered = 0
}
KijijiListing([int]$ID,[string]$ConnectionName){
# Populate from an id in the database
$selectIDQuery = "SELECT * FROM listings WHERE id=@id LIMIT 1"
Write-Verbose "KijijiListing - $ID`: initiating listing object with id"
# Query the database
if(Test-SqlConnection -ConnectionName $ConnectionName){
# The following query will throw an exception if there is no active connection. Capture it as a terminating exception
Write-Verbose "KijijiListing - $ID`: check for existing listing"
$listingResult = Invoke-SqlQuery -Query $selectIDQuery -Parameters @{id=$ID} -ConnectionName $ConnectionName -Stream
} else {
throw [System.NotSupportedException]"No active SQL connection"
}
if($listingResult){
# Populate the object from the database data wherever a property match between both is found.
Write-Verbose "KijijiListing - $ID`: match found in database"
$properties = $this.psobject.properties.name
foreach($property in $properties){
# If this property is populated in the database row. Do so to this object
if($listingResult.$property){$this.$property = $listingResult.$property}
}
} else {
# No match was found in the database. Cannot create the
throw [System.ArgumentException] "No record found for the id: $ID"
}
}
# Simple list like output of non hidden properties.
[string]toString(){
$properties = $this.psobject.properties.name
$maxPropertyNameLength = ($properties | Measure-Object -Maximum -Property Length).Maximum
return ($properties | ForEach-Object{Write-Output ("{0}: {1}" -f $_.PadRight($maxPropertyNameLength, " "),$this.$_)}) -join "`n"
}
# Invoke-SQLUpdate to add record to database
[object]AddtoDB([string]$ConnectionName){
$InvokeSQLUpdateParameters = @{
Query = "INSERT INTO listings SET id=@id, url=@url, price=@price, title=@title, distance=@distance,
location=@location, posted=@posted, shortdescription=@shortdescription, imageurl=@imageurl,searchurlid=@searchurlid,
lastsearched=@lastsearched,discovered=@discovered, new=@new, changes=@changes"
Parameters = @{iD = $this.iD; uRL = $this.uRL; price = $this.price; title = $this.title; distance = $this.distance;
location = $this.location; posted = $this.posted; shortDescription = $this.shortDescription;
imageURL = $this.imageURL; searchURLID = $this.searchURLID; lastsearched = $this.lastsearched;
discovered = $this.discovered; new = 1; changes=""}
Connectionn = $ConnectionName
}
Write-Verbose "AddtoDB - $($this.id): insert into database"
return (Invoke-SqlUpdate @InvokeSQLUpdateParameters)
}
CompareListing([KijijiListing]$DifferenceListing){
# Using the differencelisting show all properties that are different. Return a list of properties and their differences
$differentProperties = [System.Collections.ArrayList]::new()
# Populate the object from the database data.
$properties = $this.psobject.properties.name.where({$_ -notin [kijijilisting]::ComparePropertiesToIgnore})
Write-Verbose "CompareListing - $($this.id): comparing against $($differentProperties.id)"
foreach($property in $properties){
# If this property is populated in the database row. Do so to this object
if($DifferenceListing.$property -ne $this.$property){
# Some properties have special rules for determining difference
$findings = "Property has changed"
switch($property){
"price"{
# If both prices are numbers then compare. Else give default reason
if($this.price -as [double] -and $DifferenceListing.price -as [double]){
if([double]$this.price -lt [double]$DifferenceListing.price){
$findings = "Price has decreased"
} else {
$findings = "Price has increased"
}
}
}
}
# Add this property and its notes to the list to be returned.
$differentProperties.Add([pscustomobject]@{Property=$property;Findings=$findings})
}
}
# Add the finding back to the $this.changes in json form.
Write-Verbose "CompareListing - $($this.id): compare results: $($differentProperties.Count) differences"
if($differentProperties.Count -gt 0){
Write-Verbose "CompareListing - $($this.id): updating changes"
$this.changes = ConvertTo-Json -Depth 2 -InputObject @($differentProperties)
}
}
# Make changes to an existing listing in a database
[object]UpdateInDB([string]$ConnectionName,[int]$Discovered){
$InvokeSQLUpdateParameters = @{
Query = "UPDATE listings SET url=@url, price=@price, title=@title, distance=@distance,
location=@location, posted=@posted, shortdescription=@shortdescription, imageurl=@imageurl,searchurlid=@searchurlid,
lastsearched=@lastsearched,discovered=@discovered, new=@new, changes=@changes
WHERE id=@id"
# Add all properties using ones in current object. If we are doing an update increase the
Parameters = @{id = $this.iD; uRL = $this.uRL; price = $this.price; title = $this.title; distance = $this.distance;
location = $this.location; posted = $this.posted; shortDescription = $this.shortDescription;
imageURL = $this.imageURL; searchURLID = $this.searchURLID; lastsearched = $this.lastsearched;
discovered = $Discovered + 1; new = 1; changes=$this.changes}
Connectionn = $ConnectionName
}
Write-Verbose "$($this.id): updating in database"
return (Invoke-SqlUpdate @InvokeSQLUpdateParameters)
}
static [datetime] ConvertFromKijijiDate([string]$DateString,[datetime]$BaseDate){
# Trim data that does not need to be in the string
$DateString = $DateString.Replace("ago","").Replace("<","").Trim()
# Determine the string format and adjust the current date accordingly from the base date.
switch -Wildcard ($DateString){
"*minutes*" {
return $BaseDate.AddMinutes(-($DateString.Replace(" minutes","")))
break
}
"*hours*" {
return $BaseDate.AddHours(-($DateString.Replace(" hours","")))
break
}
"*yesterday*"{
# Return yesterday but remove the time
return $BaseDate.AddDays(-1).Date
break
}
default{
# If none of the other options worked assume this is a normal dd/MM/yyyy string
try{
return [DateTime]::ParseExact($DateString, [KijijiListing]::kijijiDateFormat, $null)
} catch {
return $null
}
}
}
return $null
}
}
class KijijiSearch{
# Variables
[int]$searchURLID
[uri]$searchURL
hidden $_webClient = [System.Net.WebClient]::new()
# Identifiable name to associate to the MariaDB Connection
hidden $_databaseConnectionName = "Kijiji"
[datetime]$SearchExecuted
# Search result meta data
$firstListingResultIndex = 0
$lastListingResultIndex = 0
$totalNumberOfSearchResults = 0
$maximumResultsPerSearch = 0
[datetime]$newListingCutoffDate
$listings = [System.Collections.ArrayList]::new()
static $parsingRegexes = @{
# Current listing index as well as total results. Helps determine number of pages.
TotalListingNumbers = '(?sm)<div class="showing">.*?Showing (?<FirstListingResultIndex>[\d,]+) - (?<LastListingResultIndex>[\d,]+) of (?<TotalNumberOfSearchResults>[\d,]+) Ads</div>'
# Determine unique listing html blocks
Listing = '(?sm)data-ad-id="\w+".*?<div class="details">'
}
# Contructors
KijijiSearch(
# Kijiji Search URL
[uri]$URL,
[int]$MaximumResults,
# Database connection parameters
[int]$NewListingThresholdHours,
[DatabaseConnectionProperties]$ConnectionParameters
){
# Initialize the webclient for searching Kijiji. WebClient is used as Invoke-WebRequest has historically halted when browsing Kijiji
$this._webClient.Encoding = [System.Text.Encoding]::UTF8
$this._webClient.CachePolicy = [System.Net.Cache.RequestCachePolicy]::new([System.Net.Cache.RequestCacheLevel]::NoCacheNoStore)
# Initialize the database connection
try{
$databaseConnectionParameters = $ConnectionParameters.toHashTable()
Open-MySqlConnection @databaseConnectionParameters -ConnectionName $this._databaseConnectionName
} catch {
throw "Unable to open SQL connection: $($_[0].Exception.message)"
} finally {
# Remove the connection parameters from memory
Remove-Variable databaseConnectionParameters
}
# Ensure the search URL is validated and run the search.
if([KijijiSearch]::ValidKijijiURL($URL)){
$this.searchURL = $URL
$this.searchURLID = $this.GetSQLSearchURLID()
$this.maximumResultsPerSearch = $MaximumResults
$this.searchURL = [KijijiSearch]::_AddPageNumber($this.searchURL)
$this.newListingCutoffDate = (Get-Date).AddHours(-$NewListingThresholdHours)
} else {
throw [System.ArgumentException]"Failed kijiji url validation"
}
Write-Verbose "KijijiSearch - $($this.toString())"
}
# Simple list like output of non hidden properties.
[string]toString(){
$properties = $this.psobject.properties.name
$maxPropertyNameLength = ($properties | Measure-Object -Maximum -Property Length).Maximum
return ($properties | ForEach-Object{Write-Output ("{0}: {1}" -f $_.PadRight($maxPropertyNameLength, " "),$this.$_)}) -join "`n"
}
[int]GetSQLSearchURLID(){
function Invoke-SQLGetSearchID{
return Invoke-SqlScalar "SELECT urlid FROM searchurls WHERE url = @url" -Parameters @{url=$this.searchURL} -ConnectionName $this._databaseConnectionName
}
# See if this search url is in the URL table. If not add it.
$urlID = Invoke-SQLGetSearchID
if(-not $urlID){
# This ID is not in the database. Add it.
try{
# Insert the URL record into the database.
Invoke-SqlUpdate "INSERT INTO searchurls (url) VALUES (@url)" -Parameters @{url=$this.searchURL} -ConnectionName $this._databaseConnectionName
# Get the new id
$urlID = Invoke-SQLGetSearchID
} catch {Write-Warning ("GetSQLSearchURLID: " + $_[0].Exception.Message)}
}
return $urlID
}
# Instance Methods
Search(){
# Performs a kijiji web search. All found listings are added to an arraylist property for evaluation.
# Set the search execution time to now
$this.SearchExecuted = Get-Date
# Run the search and parse the page.
Write-Verbose "Search - Performing search against $($this.searchURL)"
$rawHTML = $this._webClient.DownloadString($this.searchURL)
# Get search meta data from the first page of the search.
if($rawHTML -match [KijijiSearch]::parsingRegexes["TotalListingNumbers"]){
$this.firstListingResultIndex = $Matches["FirstListingResultIndex"] -as [int]
$this.lastListingResultIndex = $Matches["LastListingResultIndex"] -as [int]
$this.totalNumberOfSearchResults = $Matches["TotalNumberOfSearchResults"] -as [int]
}
# Parse any listings into class objects
if($this.totalNumberOfSearchResults -gt 0){
Write-Verbose "Search - Found $($this.totalNumberOfSearchResults) listing(s)"
$listingsHTML = [regex]::Matches($rawHTML,[KijijiSearch]::parsingRegexes["Listing"]).Value
ForEach($singleListingHTML in $listingsHTML){
$this.listings.add([KijijiListing]::new($singleListingHTML, $this.searchURLID, $this.SearchExecuted))
}
} else {
Write-Verbose "Search - No listing found"
}
}
UpdateSQLListings(){
# Load the currentl listings into the database. New ones will be added outright. If there are conflicts outside a date thresholds then
# updates to current data may be done.
foreach($listing in $this.listings){
# Check each listing to see if it already exists in the database
$duplicateListing = $null
try{
Write-Verbose "UpdateSQLListings - $($listing.id) duplicate search"
$duplicateListing = [KijijiListing]::new($listing.id,$this._databaseConnectionName)
} catch [System.NotSupportedException] {
# Could not connect to SQL database using named connection
throw $_
} catch [System.ArgumentException] {
# No matching listing was found. Continue
} catch {
# Rethrow this exception.
throw $_
}
# If a duplicate listing is found we will need to update it appropriately else
# just add this listing as a new listing.
if($duplicateListing){
# This id historically exists. Check to see if it was found recently.
if($duplicateListing.lastsearched -lt $this.newListingCutoffDate){
# Record the difference between this one and the duplicate listing
$listing.CompareListing($duplicateListing)
# Update the listing in the DB with new information
$listing.UpdateInDB($this._databaseConnectionName, $duplicateListing.discovered)
Write-Verbose "UpdateSQLListings - Updated existing listing $($duplicateListing.id) with new data"
} else {
# This listing is too recent to be considered rediscovered. Ignore it.
Write-Verbose "UpdateSQLListings - Listing $($listing.id) found in database before the cutoff date: $($this.newListingCutoffDate)"
}
} else {
# This ID is not located in the database. Add It
$listing.AddtoDB($this._databaseConnectionName)
}
}
}
Completed(){
# Called to finilize search. Currently just close database connection
if(Get-SqlConnection -ConnectionName $this._databaseConnectionName){
Write-Verbose "Completed - closing connection $($this._databaseConnectionName)"
Close-SqlConnection -ConnectionName $this._databaseConnectionName
}
}
hidden static [uri]_AddPageNumber([uri]$url){
# Deep Kijiji searches are done using page numbers. The first page of the search typically does not have one.
# Add a page number if this url does not have one.
$pageSegmentRegex = "page\-\d+"
if( $url.Segments -match $pageSegmentRegex){
# This url has a page number amongst its segments. Return as is
return $url
} else {
# No page segment located. Add one as the second last segment
return ([System.UriBuilder]::new(
$URL.Scheme,
$URL.Host,
$URL.port,
-join @($url.Segments[0..($url.Segments.Count-2)]) + "page-1/" + $url.Segments[-1],
$URL.Query)
).Uri.AbsoluteUri -as [uri]
}
}
# Static Methods
[boolean] static ValidKijijiURL([uri]$URL){
# Ensure that the URL is well formed kijiji url
return ($url.Host -eq "www.kijiji.ca")
}
}
The I would use this code sample to use my new classes. I would run this as a scheduled task in Windows.
SearchFromConfig.ps1
[cmdletbinding()]
param(
# Path where json config files are stored.
[Alias("Path")]
[string]$ConfigPath
)
# Enum to help give better information about exit code.
Enum ExitCode{
CantFindConfigFile = 10
CantReadConfigFile = 11
}
# Import the classes into scope
Add-Type -AssemblyName System.Web
. "$PSScriptRoot\KijijiClasses.ps1"
if($Verbose.IsPresent){
$previousVerbosePreference = $VerbosePreference
$VerbosePreference = [System.Management.Automation.ActionPreference]::Continue
}
# Import the search alert monitor configs
if(Test-Path $ConfigPath -PathType Leaf){
try{
$searchConfig = Get-Content -Path $ConfigPath -Raw | ConvertFrom-Json
} catch {
exit [ExitCode]::CantReadConfigFile
}
} else {
# throw [System.ArgumentException]"Could not find file."
exit [ExitCode]::CantFindConfigFile
}
# Rebuild the database credentials from file
$databaseCredentials = [System.Management.Automation.PSCredential]::new($searchConfig.db.username, ($searchConfig.db.password | ConvertTo-SecureString))
# Initiate the database connection object
$connection = [DatabaseConnectionProperties]::new($searchConfig.db.server,$searchConfig.db.port,$searchConfig.db.name,$databaseCredentials)
# Cycle each individual search configuration located in this file
foreach($search in $searchConfig.search_config){
# Loop the search urls in this configuration
foreach($searchURL in $search.search_URLS){
# Build the search object
$kijijiSearch = [KijijiSearch]::new($searchURL, $search.searchThreshold, $search.newListingThreshold, $connection)
# Start the search based on config options
$kijijiSearch.Search()
# Load any new listings and update existing ones where applicable
$kijijiSearch.UpdateSQLListings()
# Close the search object.
$kijijiSearch.Completed()
}
}
# Reset verbosity preference
if($Verbose.IsPresent){
$VerbosePreference = $previousVerbosePreference
}
Which I would feed a sample json config to
search.cfg.json
{
"search_config": [
{
"name": "retrovideogames",
"search_URLS" : [
"https://www.kijiji.ca/b-old-video-games/ottawa/c623l1700185r50.0?address=k2c+3p5&ll=45.344290,-75.787911"
],
"searchThreshold" : 20,
"newListingThreshold" : 36
},
{
"name": "boardgames",
"search_URLS" : [
"https://www.kijiji.ca/b-toys-games/ottawa-gatineau-area/board-games/k0c108l1700184r100.0",
"https://www.kijiji.ca/b-toys-games/ottawa-gatineau-area/card-games/k0c108l1700184r100.0"
],
"searchThreshold" : 20,
"newListingThreshold" : 36
}
],
"db":{
"username":"kijiji",
"password": "01000000d08c9ddf0115d1118c7a00c04fc297eb01000000e5530e58913e1547851baf6f14a168f1000000000200000000001066000000010000200000003a51c6638a1b88fd5fa63e4ed3a62158bd425e142c2050466d89de122cbd3318000000000e80000000020000200000001c11ceda3b72d9865378f6dc9712b900c289991f82d436b3f4b2c61e41c64b1d2000000066733af5902a8a76213b7ec2ff6a0bb0589845d9b595b3c306777e1891cbd9f7400000006552746f7d0f1e838e62e93159eefdb0ce5932e58ad57c0fc9222f78a3ce7522ef8168534dd82dcbf88e21d1f26f4a4884f8170e6de605c6a32dd25fc9d9d24a",
"name":"kijiji",
"port":49000
},
}
