powershell 操作sqlite数据库 增删改查(windows)

module sqlite的完整源码备份(https://gitee.com/whiter001/sqlite); 原版无人维护, 需要跨平台支持,需自行修改

先安装
install-module sqlite

执行以下demo.ps1文件

import-module SQLite

function mount-db {
	New-PSDrive -PSProvider SQLite -Name db -Root "Data Source=file::memory:,Version=3,New=true" -Scope global | Out-Null;		
	New-Item -path db:/data -Value @{ id = 'INTEGER PRIMARY KEY'; username = "TEXT"; userid = "INTEGER" } | Out-Null;
		
	New-Item -path db:/data -username "USER3" -userid 3 | Out-Null
}
	
function unmount-db {		
	Remove-PSDrive -Name db | Out-Null;	
}

mount-db;
$items = ls db:/data -Filter "USERNAME='USER3'";
$results = $items.userid -eq 3;
$results

set-item db:/data -Filter "USERNAME='USER3'" -Value @{ userid = 1 };
$results = ( get-item db:/data/1 ).userid -eq 1;
$results

remove-item db:/data -Filter "USERNAME='USER3'" 
$results = -not( get-item db:/data/1 -ErrorAction 'silentlycontinue' )
$results
unmount-db;


function get-sqlitefilename {
	return ( "file:$pwd\_" + [IO.Path]::GetRandomFileName() + '.sqlitetest,Version=3,New=true' );
}
function remove-sqliteFiles {
	ls *.sqlitetest | Remove-Item -Force;
}

$filename = get-sqliteFileName;
New-PSDrive -PSProvider SQLite  -Name db -root "Data Source=$filename" | Out-Null;
# raises error when removing an nonexisting table

# adds a new table using DDL
# New-Item -Path db:/MyDb -Value @'
# 			id INTEGER PRIMARY KEY,
# 			a1 TEXT,
# 			a2 INTEGER NOT NULL,
# 			a3 INTEGER NOT NULL,
# 			a4 INTEGER NOT NULL
# '@ | Out-Null;
# or  adds a new table using param slurping
# New-Item -Path db:/MyDb -id INTEGER PRIMARY KEY -a1 TEXT -a2 INTEGER NOT NULL -a3 INTEGER NOT NULL -a4 INTEGER NOT NULL | Out-Null;
# or

# creates a new record using hashtable
New-Item -Path db:/MyDb -Value @{
	id       = "INTEGER PRIMARY KEY";
	username = "TEXT";
	age      = "INTEGER";
} | Out-Null;



# Test-Path db:/mydb;
# or
$result = @( Test-Path db:/mydb );
New-Item -path db:/mydb -username "Beth" -age 39 | Out-Null;
		

$result += ( Test-Path db:/mydb/1 );
$beth = Get-Item -Path db:/mydb/1;
#$beth.age = 38;

# updates an existing record using dynamic parameters
Set-Item -Path db:/mydb/1 -age 38;
$result += ( 38 -eq ( Get-Item db:/mydb/1 ).age );

# updates an existing record using datarow object
$beth = Get-Item -Path db:/mydb/1;
$beth.age = 37;
Set-Item -Path db:/mydb/1 -value $beth;
$result += ( 37 -eq ( Get-Item db:/mydb/1 ).age );

# updates an existing record using dbnull value dynamic object
Set-Item -Path db:/mydb/1 -age $null;
$result += ( [DBNull]::Value -eq ( Get-Item db:/mydb/1 ).age );
		
New-Item -path db:/mydb -value @{ username = "Jimbo"; age = 38 }  | Out-Null;
$result += ( Test-Path db:/mydb/2 );
$result += [bool]( 2 -eq ( ls db:/mydb | measure | select -ExpandProperty count) );
$result += [bool]( 'Jimbo' -eq ( gi db:/mydb/2 ).username );

# removes an existng record
Remove-Item -Path db:/mydb/2;
$result += -not( Test-Path db:/mydb/2 )

# mounts a table as a drive
New-PSDrive -PSProvider SQLite  -Name dbt -root "[Data Source=$filename]\mydb" | Out-Null;
$result += ( Test-Path dbt:/1 );
$item = Get-Item dbt:/1;
$result += $item.username -eq "Beth"

Remove-PSDrive -Name dbt | Out-Null;
Remove-PSDrive -Name db | Out-Null;

$result 

remove-sqliteFiles
# Remove-Module SQLite;