artifacts_with_index.sql

CREATE TABLE artifacts(idx INTEGER PRIMARY KEY, group_id TEXT, artifact_id TEXT);
CREATE TABLE indices2(artifact_idx INTEGER, version TEXT, sha1 BLOB, archive_type TEXT);

INSERT INTO artifacts(group_id, artifact_id) SELECT group_id, artifact_id FROM indices GROUP BY group_id, artifact_id;
INSERT INTO indices2(artifact_idx, version, sha1, archive_type) SELECT a.idx, i.version, i.sha1, i.archive_type FROM indices i JOIN artifacts a ON i.group_id = a.group_id AND i.artifact_id = a.artifact_id;;

CREATE INDEX artifacts_idx ON artifacts(group_id, artifact_id);
CREATE INDEX indices2_sha1_idx ON indices2(sha1);
CREATE INDEX indices2_artifact_idx ON indices2(artifact_idx);

DROP TABLE indices;
VACUUM;

index_only.sql

CREATE INDEX artifacts_idx ON indices(group_id, artifact_id);
CREATE INDEX sha1_idx ON indices(sha1);
VACUUM;

to_sha1_blob.go

package main

import (
	"database/sql"
	"encoding/hex"
	"log"

	_ "github.com/glebarez/sqlite"
)

func main() {
	if err := run(); err != nil {
		log.Fatal(err)
	}
}

func run() error {
	db, err := sql.Open("sqlite", "trivy-java.db")
	if err != nil {
		return err
	}
	defer db.Close()

	newDB, err := sql.Open("sqlite", "trivy-java2.db")
	if err != nil {
		return err
	}
	defer newDB.Close()
	newDB.SetMaxOpenConns(1)

	_, err = newDB.Exec("CREATE TABLE `indices`(`group_id` TEXT, `artifact_id` TEXT, `version` TEXT, `sha1` BLOB, archive_type TEXT);")
	if err != nil {
		return err
	}

	rows, err := db.Query("SELECT group_id, artifact_id, version, sha1, archive_type FROM indices")
	if err != nil {
		return err
	}
	defer rows.Close()

	type Index struct {
		groupID, artifactID, version, sha1, archiveType string
	}
	var indices []Index
	for rows.Next() {
		var groupID, artifactID, version, sha1, archiveType string
		if err := rows.Scan(&groupID, &artifactID, &version, &sha1, &archiveType); err != nil {
			return err
		}
		indices = append(indices, Index{
			groupID, artifactID, version, sha1, archiveType,
		})
	}
	rows.Close()

	tx, err := newDB.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	for _, i := range indices {
		sha1b, _ := hex.DecodeString(i.sha1)
		_, err := tx.Exec("INSERT INTO indices(group_id, artifact_id, version, sha1, archive_type) VALUES (?, ?, ?, ?, ?)", i.groupID, i.artifactID, i.version, sha1b, i.archiveType)
		if err != nil {
			log.Printf("%+v", i)
			return err
		}
	}
	return tx.Commit()
}