More exploring gojq + sqlite + event

a.go

package main

import (
	"bufio"
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"os"

	"github.com/freshteapot/learnalist-api/server/api/database"
	"github.com/freshteapot/learnalist-api/server/pkg/event"
	"github.com/freshteapot/learnalist-api/server/pkg/spaced_repetition"
	"github.com/itchyny/gojq"
)

func testJSON() {
	rawJSON := `{"kind":"api.spacedrepetition","data":{"data":{"body":"{\"show\":\"ifølge\",\"kind\":\"v2\",\"uuid\":\"c2d8d46a19dcd9484f2c01d8353e242fc5ec9354\",\"data\":{\"from\":\"ifølge\",\"to\":\"according to\"},\"settings\":{\"level\":\"0\",\"when_next\":\"2020-10-07T05:06:42Z\",\"created\":\"2020-10-07T04:06:42Z\",\"show\":\"from\"}}","Created":"2020-10-07T04:06:42Z","UUID":"c2d8d46a19dcd9484f2c01d8353e242fc5ec9354","UserUUID":"fc7f0e39-aa15-52d4-b590-e3a2bf9ee86d","WhenNext":"2020-10-07T05:06:42Z"},"kind":"new"},"timestamp":1602043602}`
	var moment event.Eventlog
	err := json.Unmarshal([]byte(rawJSON), &moment)
	fmt.Println(err)
	var srsData spaced_repetition.EventSpacedRepetition
	b, err := json.Marshal(moment.Data)
	fmt.Println(err)
	err = json.Unmarshal(b, &srsData)
	fmt.Println(err)
	fmt.Println(srsData.Data.Body)
}
func main() {
	//testJSON()
	//return
	sqlActivity := `
	CREATE TABLE IF NOT EXISTS spaced_repetition_activity (
		uuid CHARACTER(36) not null,
		action text,
		level text,
		when_day DATETIME not null,
		when_next DATETIME not null,
		kind text,
		user_uuid text,
		UNIQUE(uuid, user_uuid, kind, action, when_next)
	  );
`
	// TODO: Can write logic to massage Body to body, but then need to massage all the other mistakes.
	// golang is okay with it.
	// Use go code as jq doesnt know the difference between Body and body (not jq's fault)

	// To handle deleted as well, I would need another query or more go logic as the jq might be complicated
	jqQuery := `select(
		.kind=="api.spacedrepetition"
	)|
	(
		select(.data.data.Body) |
		.data.data["body"] = .data.data.Body|del(.data.data.Body) |
		.data.data["user_uuid"] = .data.data.UserUUID|del(.data.data.UserUUID) |
		.data.data["created"] = .data.data.Created|del(.data.data.Created) |
		.data.data["when_next"] = .data.data.WhenNext|del(.data.data.WhenNext) |
		.data.data["uuid"] = .data.data.UUID|del(.data.data.UUID)
	)|
	(
		select(
			.data.kind| IN(
				[
					"new",
					"viewed"
				][])
		) |
		{
			action: (
				if .data.kind == "viewed" then .data.action else "created" end
			),
			when:(.timestamp|strftime("%Y-%m-%d")),
			level: (.data.data.body|fromjson|.settings.level),
			when_next: .data.data.when_next,
			kind: .data.kind,
			user_uuid: .data.data.user_uuid,
			uuid: .data.data.uuid,
		}
	)|
	(
		select(
			.data.kind == "deleted"
		) |
		{
			action: .data.kind,
			when:(.timestamp|strftime("%Y-%m-%d")),
			kind: .data.kind,
			user_uuid: .data.data.user_uuid,
			uuid: .data.data.uuid,
		}
	) |
	.`

	query, err := gojq.Parse(jqQuery)
	if err != nil {
		log.Fatalln(err)
	}

	databaseName := "./b.db"
	// TODO how to make this memory
	db := database.NewDB(databaseName)
	ctx := context.Background()

	db.MustExecContext(ctx, `CREATE TABLE IF NOT EXISTS tmp_import (body text);`)
	db.MustExecContext(ctx, sqlActivity)

	go (func() {
		for {
			var action string
			err := db.GetContext(ctx, &action, `SELECT action FROM spaced_repetition_activity WHERE uuid='c3e947e33d89c584117ca609cac42e14076d36b9' LIMIT 1;`)
			if err != nil {
				if err != sql.ErrNoRows {
					fmt.Println(err)
				}
				continue
			}

			if action == "" {
				continue
			}
			fmt.Println(action)
		}
	})()

	//var with string
	//db.Get(&with, `SELECT json_extract(body, '$.action') FROM m.tmp_import LIMIT 1;`)
	//fmt.Println(with)
	//return
	scanner := bufio.NewScanner(os.Stdin)
	for scanner.Scan() {
		var rawJSON interface{}
		_ = json.Unmarshal(scanner.Bytes(), &rawJSON)

		iter := query.Run(rawJSON) // or query.RunWithContext
		for {
			v, ok := iter.Next()
			if !ok {
				break
			}
			if a, ok := v.(error); ok {
				fmt.Println(err, a)
				continue
			}

			if _, ok := v.(map[string]interface{}); !ok {
				log.Fatalln("not json")
			}

			output, _ := json.Marshal(v.(map[string]interface{}))
			//fmt.Println(v.(map[string]interface{}))
			//fmt.Println(string(output))
			db.MustExecContext(ctx, `INSERT INTO tmp_import VALUES(?)`, output)

			// Write to temp table
			// Then use sql to write to proper table

			// or get the keys from the map
			// assume they are the keys in the column
			// import
		}
	}

	//db.MustExecContext(ctx, `INSERT INTO tmp_import SELECT * FROM tmp_import`)
	// TODO: To make this generic, doing this import into the memory db would reduce having to sovle "m.etc"
	// TODO: if we attach the db, can it be used? for reads in the mean time?
	// TODO deleted doesn't get sucked up here
	db.MustExecContext(ctx, `INSERT OR IGNORE INTO spaced_repetition_activity
	SELECT
		json_extract(body, '$.uuid') as uuid,
		json_extract(body, '$.action') as action,
		json_extract(body, '$.level') as level,
		json_extract(body, '$.when') as when_day,
		json_extract(body, '$.when_next') as when_next,
		json_extract(body, '$.kind') as kind,
		json_extract(body, '$.user_uuid') as user_uuid
	 FROM tmp_import`)
	db.MustExecContext(ctx, `CREATE INDEX IF NOT EXISTS spaced_repetition_activity_user_uuid ON spaced_repetition_activity (user_uuid)`)
}