Nouveau module SQLite pour PowerShell
Tout juste revenu de #PSConfEU, et alors que je m’occupe encore des tâches d’organisation (facturation, comptabilité, retours, préparation de la prochaine édition), j’ai réussi à publier synedgy.PSSQlite !
SQLite – base de données dans un fichier unique
Vous avez sans doute déjà utilisé des bases SQLite sans vous en rendre compte : ce sont des bases petites et rapides, encapsulées dans un seul fichier, intégrées dans des applications mobiles, desktop ou parfois côté serveur.
Ne sous-estimez pas sa puissance : malgré sa taille et sa simplicité, elles sont très performantes. SQLite est parfait pour des usages comme le cache de données PowerShell Universal ou une base locale pour un module ou logiciel. Autre avantage : la simplicité du fichier unique facilite les sauvegardes et restaurations pour les équipes opérationnelles !
Limitations de SQLite
SQLite a quelques compromis. Un système de types basique (qui fait aussi sa force) :
- INTEGER
- REAL
- TEXT
- BLOB
- ANY
Une base de données (toute) verrouillée en écriture, ce qui peut poser problème si plusieurs connexions tentent d’écrire en même temps. Toutefois, SQLite est si rapide que cela reste rare, à condition d’écrire quelques lignes à la fois.
Pour une utilisation come cache, tel qu’avec PowerShell Universal, il est conseillé de lancer des jobs périodiques en évitant les chevauchements de processus.
Le module PowerShell synedgy.PSSqlite
Pourquoi créer un nouveau module alors qu’il en existe déjà plusieurs ?
SynEdgy.PSSQlite apporte deux améliorations majeures :
- Utiliser Microsoft.Data.SQLite au lieu de System.Data.SQLite.
- Permettre des opérations CRUD simples sans écrire de SQL.
Le premier avantage profite notamment à PowerShell Universal, où la librairie SQLite est déjà chargée, évitant des conflits de modules et handles sur les DLLs du module.
Le second simplifie la vie pour les développeurs PowerShell qui ne maîtrisent pas ou n’ont pas le temps d’apprendre SQL.
La persistance SQL sans connaître SQL
Connaître SQL est utile, et je ne recommande pas d’ignorer cette technologie sous le faux prétexte que vous êtes un auteur PowerShell et non un DBA. Cela dit, je comprends tout à fait qu’il s’agit d’un obstacle de plus qui empêche de faire les choses correctement. Pour ceux qui n’ont pas les compétences ou le temps d’investir dans l’apprentissage de SQL, j’ai voulu abaisser la barre d’entrée, pour vous aider à voir la valeur…
J’ai mis un exemple minimaliste sur la façon d’utiliser synedgy.PSSqlite dans le dépôt suivant : https://github.com/SynEdgy/SqlitExample
Le dépôt SqlitExample contient un module appelé MyModule et un README.md qui explique comment l’utiliser, la première étape étant de rendre ce module disponible avec Install-Module.
Le principe du module synedgy.PSSqlite est illustré par la fonction Get-Car. L’idée sous-jacente est qu’aucun SQL n’est requis, il suffit d’appeler Get-PSSQliteRow en spécifiant le nom de la table à consulter, de passer quelques autres paramètres que nous expliquerons plus tard, et le SQL est généré dynamiquement, exécuté, et les résultats renvoyés.
function Get-Car
{
[CmdletBinding()]
param
(
[Parameter()]
[string]
$Make,
[Parameter()]
[string]
$Model,
[Parameter()]
[string]
$Colour,
[Parameter()]
[int]
$Year
)
$getPSSqliteRowParams = @{
SqliteDBConfig = (Get-myModuleConfig)
TableName = 'Cars'
ClauseData = $PSBoundParameters
verbose = $Verbose.IsPresent -or $VerbosePreference -in @('Continue', 'Inquire')
}
Get-PSSqliteRow @getPSSqliteRowParams
}Cette fonction transmet les $PSBoundParameters (les paramètres utilisés lors de l’invocation de la commande) au paramètre ClauseData de Get-PSSqliteRow. Ainsi, si vous exécutezGet-Car -Colour yellow -Verbose
La sortie ressemblera à ceci :
Comme vous pouvez le voir, le module génère une instruction SQL avec une clause WHERE qui effectue une validation insensible à la casse pour les lignes dont la colonne Couleur correspond à la couleur jaune. L’instruction SQL générée utilise des paramètres pour réduire les risques d’injections SQL.
Des fonctions similaires existent pour toutes les opérations CRUD :
Get-PSSqliteRowNew-PSSqliteRowSet-PSSqliteRowRemove-PSSqliteRow
Elles ont été implémentées dans le module MyModule.psm1 pour gérer les objets Voiture dans la base de données. Aucun SQL n’est nécessaire.
Comment fonctionne la magie ?
Il n’y a pas de magie et c’est open source, donc je ne révélerai pas d’obscure astuce secrète…
Le module synedgy.PSSqlite s’attend à ce que le module appelant (dans ce cas myModule) ait le fichier yaml de définition de la base de données dans son dossier config.
# ./config/myModule.PSSqliteConfig.yml
DatabasePath: $repository
DatabaseFile: test.db
version: 0.0.3
Schema:
Tables:
cars:
columns:
id:
type: INTEGER
PrimaryKey: true
indexed: true
make:
type: TEXT
model:
type: TEXT
colour:
type: TEXT
year:
type: INTEGERLa partie schéma déclare les tables avec leur nom et leurs colonnes. Vous pouvez également ajouter des contraintes, mais c’est un autre sujet, pour une autre fois.
Ici, la table voitures a la colonne id qui est de type INTEGER et qui est la clé primaire (PRIMARY KEY). En SQLite, cela signifie qu’elle sera incrémentée automatiquement (alias ROWID). La table contient également les colonnes make, model et colour de type TEXT, ainsi que la colonne year de type INTEGER.
Avec ces informations, lorsque nous passons des clés/valeurs à ClauseData pour un SELECT (Get-) ou un DELETE (Remove-) ou à RowData pour un INSERT (New-) ou un UPDATE (Set-), lorsque la clé correspond au nom de la colonne, nous pouvons construire la requête SQL.
Mais puisque nous avons tous les détails de la table dans le schéma, ne pouvons-nous pas générer la base de données ? Absolument !
Générer des instructions CREATE
Puisque nous avons la plupart des informations nécessaires au schéma dans le fichier de configuration, synedgy.PSSQlite peut générer les instructions CREATE à partir d’une configuration, et initialiser votre base de données SQLite.
Mais ce n’est pas quelque chose que vous ferez souvent, vous voudrez généralement initialiser la base de données vous-même avec Initialize-PSSqliteDatabase.
Comme vous pouvez le voir dans la capture d’écran ci-dessus, nous avons créé le fichier de base de données et exécuté le schéma sur la base de données, et la table cars est maintenant disponible dans cette base de données.
Il y a quelques autres choses qui se passent, comme cette table _metadata que nous avons ajoutée, mais nous y reviendrons plus tard…
Invoquer sa propre instruction SQL
Comme vous l’avez vu avec la commande précédente, ce n’est pas parce que le module offre un CRUD simple sans utiliser SQL que nous interdisons de l’invoquer directement. La commande Invoke-PSSqliteQuery vous permet justement de le faire, et elle est utile lorsque vous voulez personnaliser un peu plus l’instruction SQL que vous voulez exécuter.
# Loading the configuration
$dbconfig = Get-PSSqliteDBConfig -Path .\myModule\config\myModule.PSSqliteConfig.yml
# Creating the connection
$conn = New-PSSqliteConnection -ConnectionString $dbconfig.ConnectionString
# Invoking a custom SQL with Parameters
Invoke-PSSqliteQuery -SqliteConnection $conn -CommandText 'SELECT * FROM cars WHERE colour LIKE @colour' -Parameters @{colour = 'Yel%'}
<# assuming we had populated the DB
id : 2
make : Toyota
model : Corolla
colour : Yellow
year : 2024
#>Utilisation de synedgy.PSSqlite dans votre propre module
L’objectif est de permettre à votre module de disposer d’une méthode cohérente pour persister et récupérer des objets dans une base de données. Lorsque vous concevez votre module, il y a quelques astuces qui peuvent vous faciliter le travail :
- Stocker l’objet de configuration dans une variable à l’échelle du module
- Avoir un getter pour l’objet de configuration pour obtenir de cette variable
- Gérer votre connexion et utiliser -KeepAlive de manière adéquate.
L’objet de configuration
Vous verrez que les fonctions New, Get, Set, Remove-PSSqliteRow nécessitent la transmission de la configuration de la base de données. Au lieu de créer l’objet à partir du fichier YAML à chaque fois, vous pouvez faire ceci :
function Get-myModuleConfig
{
[CmdletBinding()]
param
(
[Parameter(DontShow)]
[string]
$ConfigFile,
[Parameter()]
[switch]
$Force
)
if ($null -eq $script:MyModuleDBConfig -or $Force)
{
if ([string]::IsNullOrEmpty($ConfigFile))
{
# Retrieve configuration from the current's module config subdirectory
$ConfigFile = Get-PSSqliteDBConfigFile
}
else
{
Write-Verbose -Message ('Loading configuration from {0}' -f $ConfigFile)
}
$script:MyModuleDBConfig = Get-PSSqliteDBConfig -ConfigFile $ConfigFile
}
return $script:MyModuleDBConfig
}Chaque fois que vous aurez besoin de l’objet de configuration, il sera récupéré à partir d’un emplacement mis en cache (votre variable de portée du module). Sauf s’il n’est pas déjà défini, auquel cas il sera récupéré à la volée et mis en cache.
Par défaut, Get-PSSqliteDBConfigFile va chercher dans le dossier de configuration du module de l’appelant un fichier qui ressemble à ./SQLiteConfig.yml et l’utilise pour créer l’objet de configuration.
Maintenant, à chaque fois que l’objet de configuration est récupéré, il vous suffit d’appeler votre fonction getter, et après un chargement initial depuis Yaml, il sera lu depuis la mémoire.
Gestion des connexions SQL
Chaque fois qu’une instruction SQL est exécutée via une connexion, le module tente de fermer la connexion par défaut. Cela évite de laisser une poignée ouverte sur le fichier de la base de données, ce qui bloque la manipulation du fichier.
Chaque fonction *-SqliteRow accepte un paramètre -SqlConnection, ce qui vous permet de passer une connexion existante. Si vous utilisez également le paramètre -KeepAlive, la connexion ne sera pas fermée à chaque fois pour éviter la surcharge.
# load the config objecty
$dbconfig = Get-PSSqliteDBConfig -Path .\myModule\config\myModule.PSSqliteConfig.yml
# create the connection object
$conn = New-PSSqliteConnection -ConnectionString $dbconfig.ConnectionString
# Insert into the table cars, keepingthe connection opened
$null = New-PSSqliteRow -SqliteDBConfig $dbconfig -SqliteConnection $conn -TableName 'cars' -RowData @{make = 'Renault'; model = 'R5 e-tech'; year = 2025; colour = 'yellow'} -KeepAlive
<# look at the $conn object
$conn
Handle : SQLitePCL.sqlite3
ConnectionString : Data Source=C:\dev\SqlitExample\test.db;
Database : main
DataSource : C:\dev\SqlitExample\test.db
DefaultTimeout : 30
ServerVersion : 3.46.1
State : Open
ConnectionTimeout : 15
CanCreateBatch : False
Site :
Container :
#>
# Get the rows, without keeping the connection opened
Get-PSSqliteRow -SqliteConnection $conn -TableName 'cars' -SqliteDBConfig $dbconfig | FT -a
id make model colour year
-- ---- ----- ------ ----
1 Toyota Corolla Blue 2020
2 Toyota Corolla Yellow 2024
3 Renault R5 e-tech yellow 2025
4 Renault R5 e-tech yellow 2025
5 Renault R5 e-tech yellow 2025
# Checking on the connection
$conn.state
# ClosedEt ensuite ?
Bien que ces fonctionnalités vous permettront probablement d’aller plus loin dans la plupart des cas, il y a quelques idées que j’aimerais mettre en oeuvre :
- Supporter l’insertion en masse (Bulk insert) afin de pouvoir insérer rapidement de nombreuses lignes en une seule transaction (ou revenir en arrière)
- Exporter un schéma de base de données existant vers un fichier yaml
- Avoir un schéma JSON pour la validation du fichier de configuration et l’intellisense
Mais je sais que la priorité est maintenant d’écrire plus de tests et de découvrir les cas limites que les utilisateurs pourraient rencontrer.
Si vous rencontrez des problèmes ou pensez à des améliorations, n’hésitez pas à les partager dans les problèmes sur le repo GitHub : https://github.com/SynEdgy/synedgy.pssqlite
Discover more from SynEdgy
Subscribe to get the latest posts sent to your email.