Generate a sqlite3 db file?

[zhoujin7]

PRAGMA foreign_keys = false;

DROP TABLE IF EXISTS "app";
CREATE TABLE "app" (
  "id" INTEGER,
  "name" TEXT,
  "version" TEXT,
  "description" TEXT,
  "license" TEXT,
  "bucket_repo" TEXT
);

DROP TABLE IF EXISTS "bucket";
CREATE TABLE "bucket" (
  "score" real,
  "bucket_repo" TEXT NOT NULL,
  "apps" integer,
  "stars" integer,
  "forks" integer,
  "updated" text,
  PRIMARY KEY ("bucket_repo")
);

PRAGMA foreign_keys = true;

Maybe need add more table fields.

[zhoujin7]

It can be easy search some info of scoop buckets with some code once generated a sqlite3 db file.

[zhoujin7]

The command scoop search app can only search app that in added buckets. I want to write a cmdlet search all possible apps even it not in buckets that is added.

[zhoujin7]

https://github.com/zhoujin7/crawl-scoop-directory

I fixed it.

[zhoujin7]

I write a simple web api that can be used in query all available apps. https://github.com/zhoujin7/scoop-search

Python Flask or php provide service https://github.com/zhoujin7/scoop-search/blob/master/app.py https://github.com/zhoujin7/scoop-search/blob/master/search.php

Query client: https://github.com/zhoujin7/scoop-search/blob/master/scoopSearch.ps1

I have deployed the program to my cloud server, but the query speed may be slow, even timeout. 🤷‍♂️

[rashil2000]

Hi @zhoujin7 This is a very nice thing! However, while your database updates daily, your query client doesn't seem to be up to date. I have created an issue for it in your repo zhoujin7/scoop-search#2 I created this simple PowerShell function that provides search functionality locally through the commandline, and uses (and updates itself) your SQLite database file.

param(
  [String]$Name,
  [String]$Description,
  [String]$Version,
  [String]$Bucket_Repo,
  [String]$Table = 'app',
  [Switch]$ForceUpdate = $false
)

$DBFile = 'D:\Data\Projects\Random\scoop_directory.db'

if ($Table -eq 'bucket') {
  if (!($Bucket_Repo)) {
    Write-Host "`nEnter bucket repository query.`n" -ForegroundColor Red
    return
  }
  $query = "SELECT bucket_repo,apps,updated,stars FROM bucket WHERE bucket_repo LIKE `"%$Bucket_Repo%`" ORDER BY apps DESC;"
} else {
  if (!($Name -or $Description)) {
    Write-Host "`nEnter at least one of name or description.`n" -ForegroundColor Red
    return
  }
  $query = "SELECT name,version,bucket_repo,description FROM app WHERE name LIKE `"%$Name%`" AND description LIKE `"%$Description%`" AND version LIKE `"%$Version%`" AND bucket_repo LIKE `"%$Bucket_Repo%`" ORDER BY version DESC;"
}

if (!(Test-Path $DBFile) -or ((Get-Date) - (Get-Item $DBFile).LastWriteTime).TotalHours -ge 24 -or $ForceUpdate) {
  Invoke-WebRequest 'https://raw.githubusercontent.com/zhoujin7/crawl-scoop-directory/master/scoop_directory.db' -UseBasicParsing -OutFile $DBFile
  Write-Host "`nUpdated database - $DBFile.`n" -ForegroundColor Green
}

return Invoke-SqliteQuery -Database $DBFile -Query $query

(Run Install-Module PSSQLite before using this.)