Overview

The SQLite PowerShell Provider enables you to use SQLite databases from your PowerShell session by mounting the database as a drive. You can then use the standard provider cmdlets to perform CRUD operations on the database tables and records.

The provider supports both persistent (on-disk) and transient (memory-only) SQLite databases. In addition, the provider is transaction-aware.

For more information and examples, please refer to the User's Guide in the Documentation.

Summary

Below is a summary of SQLite PowerShell Provider operations:

PS> import-module SQLite
PS> new-psdrive -name db -psp SQLite -root "Data Source=data.sqlite"

Name Provider Root 
---- -------- ---- 
db   SQLite   [Data Source=data.sqlite]                                             


PS> new-item db:/MyTable -id integer primary key -username text -userid integer not null


PSPath           : SQLite::[Data Source=data.sqlite]\MyTable
PSParentPath     : SQLite::[Data Source=data.sqlite]
PSChildName      : MyTable
PSDrive          : db
PSProvider       : SQLite
PSIsContainer    : True
TABLE_CATALOG    : main
TABLE_SCHEMA     : 
TABLE_NAME       : MyTable
TABLE_TYPE       : table
TABLE_ID         : 1
TABLE_ROOTPAGE   : 2
TABLE_DEFINITION : CREATE TABLE MyTable ( id integer primary key, username text, userid integer not null )



PS> new-item db:/MyTable -username 'beef' -userid 1


PSPath        : SQLite::[Data Source=data.sqlite]\MyTable\1
PSParentPath  : SQLite::[Data Source=data.sqlite]\MyTable
PSChildName   : 1
PSDrive       : db
PSProvider    : SQLite
PSIsContainer : False
id            : 1
username      : beef
userid        : 1



PS> 2..200 | new-item db:/MyTable -username { "User$_" } -userid {$_}


PSPath        : SQLite::[Data Source=data.sqlite]\MyTable\2
PSParentPath  : SQLite::[Data Source=data.sqlite]\MyTable
PSChildName   : 2
PSDrive       : db
PSProvider    : SQLite
PSIsContainer : False
id            : 2
username      : User2
userid        : 2

PSPath        : SQLite::[Data Source=data.sqlite]\MyTable\3
PSParentPath  : SQLite::[Data Source=data.sqlite]\MyTable
PSChildName   : 3
PSDrive       : db
PSProvider    : SQLite
PSIsContainer : False
id            : 3
username      : User3
userid        : 3

# ...

PSPath        : SQLite::[Data Source=data.sqlite]\MyTable\200
PSParentPath  : SQLite::[Data Source=data.sqlite]\MyTable
PSChildName   : 200
PSDrive       : db
PSProvider    : SQLite
PSIsContainer : False
id            : 200
username      : User200
userid        : 200



PS> ls db:/MyTable | select username, id 

username     id
--------     --
beef         1
User2        2
# ...
User200      200


PS> ls db:/MyTable -filter "username='beef'" | select username, id 

username     id
--------     --
beef         1


PS> set-item db:/MyTable -filter "username='beef'" -value @{ username='jimbo' }
PS> ls db:/MyTable -filter "id=1" | select username, id 

username     id
--------     --
jimbo        1

Last edited Jan 22, 2012 at 6:53 PM by beefarino, version 7