model.go

// Copyright 2017, 2022 Tamás Gulácsi
//
// SPDX-License-Identifier: Apache-2.0

package main

import (
	"bufio"
	"context"
	"database/sql"
	"flag"
	"fmt"
	"io"
	"os"
	"strconv"
	"strings"

	oracall "github.com/tgulacsi/oracall/lib"

	_ "github.com/godror/godror"
)

func generateModel(ctx context.Context, w io.Writer, db *sql.Tx, patterns []string, pkg string) error {
	replUnderscore := strings.NewReplacer("_", "\\_")
	var buf strings.Builder
	buf.WriteByte('(')
	args := make([]any, len(patterns))
	for i, pat := range patterns {
		if i != 0 {
			buf.WriteString(" OR ")
		}
		a := pat
		nm := fmt.Sprintf("a%03d", i+1)
		if strings.IndexByte(pat, '%') >= 0 {
			fmt.Fprintf(&buf, `A.table_name LIKE UPPER(:%s) ESCAPE '\'`, nm)
			a = replUnderscore.Replace(pat)
		} else {
			fmt.Fprintf(&buf, `A.table_name = UPPER(:%s)`, nm)
		}
		args[i] = sql.Named(nm, a)
	}
	buf.WriteByte(')')
	likeWhere := buf.String()
	qry := `SELECT synonym_name, table_name FROM user_synonyms A WHERE ` +
		strings.ReplaceAll(likeWhere, "A.table_name", "A.synonym_name") + `
	UNION ALL
	SELECT table_name, table_name FROM user_tables A WHERE ` +
		likeWhere
	rows, err := db.QueryContext(ctx, qry, args...)
	if err != nil {
		return fmt.Errorf("%s [%q]: %w", qry, args, err)
	}
	defer rows.Close()
	buf.Reset()
	tableNames := make(map[string]string)
	for rows.Next() {
		var syn, tbl string
		if err := rows.Scan(&syn, &tbl); err != nil {
			return fmt.Errorf("scan %s: %w", qry, err)
		}
		tableNames[tbl] = syn
		if buf.Len() == 0 {
			buf.WriteString("A.table_name IN (")
		} else {
			buf.WriteString(", ")
		}
		fmt.Fprintf(&buf, "'%s'", tbl)
	}
	rows.Close()
	buf.WriteString(")")
	likeWhere = buf.String()

	qry = `SELECT table_name, column_name, data_type, data_length, NVL(data_precision, 0), NVL(data_scale, 0), nullable
      FROM user_tab_cols A 
	  WHERE INSTR(column_name, '$') = 0 AND virtual_column = 'NO' AND hidden_column = 'NO' AND 
	        ` + likeWhere + `
	  ORDER BY table_name, column_id`
	rows, err = db.QueryContext(ctx, qry)
	if err != nil {
		return fmt.Errorf("%s: %w", qry, err)
	}
	defer rows.Close()
	cols := make([]Column, 0, 16)

	if flag.NArg() > 0 && !(flag.Arg(0) == "" || flag.Arg(0) == "-") {
		if w, err = os.Create(flag.Arg(0)); err != nil {
			return err
		}
	}
	bw := bufio.NewWriter(w)
	defer bw.Flush()
	if pkg != "" {
		if _, err = bw.WriteString(`// Code generated by oracall. DO NOT EDIT.

package ` + pkg + `

import (
    "context"
    "database/sql"
    "fmt"
    "time"
)

var _ sql.NullInt32
var _ time.Time
`); err != nil {
			return err
		}
	}
	columns := make(map[string]map[string]Column, 16)
	A := func(tbl string, cols []Column) {
		columns[tbl] = make(map[string]Column, len(cols))
		for _, c := range cols {
			columns[tbl][c.Name] = c
		}
		Table{Name: tbl, Cols: cols}.PrintStruct(bw)
	}
	var prev string
	for rows.Next() {
		var tbl string
		var c Column
		var n string
		if err = rows.Scan(&tbl, &c.Name, &c.Type, &c.Len, &c.Prec, &c.Scale, &n); err != nil {
			return fmt.Errorf("%s: %w", qry, err)
		}
		tbl = tableNames[tbl]
		c.Nullable = n == "Y"
		if prev != tbl {
			if prev != "" {
				A(prev, cols)
				cols = cols[:0]
			}
			prev = tbl
		}
		cols = append(cols, c)
	}
	if prev != "" && len(cols) != 0 {
		A(prev, cols)
	}

	{
		qry := `SELECT B.index_name, B.table_name, B.column_name FROM user_ind_columns B, user_indexes A 
            WHERE B.table_name = A.table_name AND B.index_name = A.index_name AND A.uniqueness = 'UNIQUE' AND 
                  ` + likeWhere + `
            ORDER BY A.table_name, B.column_position`
		rows, err := db.QueryContext(ctx, qry)
		if err != nil {
			return fmt.Errorf("%s: %w", qry, err)
		}
		defer rows.Close()

		A := func(idx, tbl string, cols []string) {
			Tbl := camelCase(tbl)
			bw.WriteString(`
func (t ` + Tbl + `) Save(ctx context.Context, tx *sql.Tx) error {
    if ok, err := t.Exists(ctx, tx); err != nil {
        return err
    } else if ok {
        return t.Update(ctx, tx)
    }
    return t.Insert(ctx, tx)
}
`)

			cs := make([]string, len(cols))
			ws := make([]string, len(cols))
			ss := make([]string, len(cols))
			qs := make([]string, len(cols))
			ns := make([]string, len(cols))
			cgt := make([]string, len(cols))
			decl := make([]string, len(cols))
			acs := make([]string, 0, len(columns[tbl]))
			scn := make([]string, 0, cap(acs))
			an := make([]string, 0, cap(acs))
			indexCols := make(map[string]struct{}, len(cols))
			for i, c := range cols {
				col := columns[tbl][c]
				C := camelCase(c)
				acs = append(acs, c)
				scn = append(scn, "&t."+C)
				an = append(an, fmt.Sprintf("sql.Named(%q, t.%s)", C, C))
				cs[i] = C
				ns[i] = fmt.Sprintf("sql.Named(%q, t.%s)", C, C)
				ws[i] = fmt.Sprintf("%s = :%s", c, C)
				ss[i] = "t." + C
				qs[i] = "%v"
				cgt[i] = C + " " + col.GoType()
				decl[i] = C + ": " + C
				indexCols[c] = struct{}{}
			}
			for c := range columns[tbl] {
				if _, ok := indexCols[c]; !ok {
					acs = append(acs, c)
					C := camelCase(c)
					scn = append(scn, "&t."+C)
					an = append(an, fmt.Sprintf("sql.Named(%q, t.%s)", C, C))
				}
			}
			fieldList := strings.Join(acs, ", ")
			where := strings.Join(ws, " AND ")
			members := strings.Join(ss, ", ")
			qS := strings.Join(qs, ", ")
			named := strings.Join(ns, ", ")

			var buf strings.Builder
			for k := range columns[tbl] {
				if _, ok := indexCols[k]; ok {
					continue
				}
				if buf.Len() != 0 {
					buf.WriteString(", ")
				}
				buf.WriteString(k + " = :" + camelCase(k))
			}
			set := buf.String()

			fmt.Fprintf(bw, `
func Load%sBy%s(ctx context.Context, tx *sql.Tx, %s) (%s, error) {
    t := %s{%s}
    err := t.Load(ctx, tx)
    return t, err
}
`,
				Tbl, strings.Join(cs, ""), strings.Join(cgt, ", "), Tbl,
				Tbl, strings.Join(decl, ", "),
			)
			fmt.Fprintf(bw, `
func (t *%s) Load(ctx context.Context, tx *sql.Tx) error {
    const qry = "SELECT %s\n\tFROM %s\n\tWHERE %s"
    if err := tx.QueryRowContext(ctx, qry, 
        %s,
    ).Scan(
        %s,
    ); err != nil {
        return fmt.Errorf("%%s [%s]: %%w", qry, %s, err)
    }
    return nil
}
`,
				Tbl,
				fieldList, tbl, where,
				named,
				strings.Join(scn, ", "),
				qS, members,
			)

			fmt.Fprintf(bw, `
func (t %s) Exists(ctx context.Context, tx *sql.Tx) (bool, error) {
    const qry = "SELECT COUNT(0) FROM DUAL WHERE EXISTS (SELECT 1 FROM %s WHERE %s)"
    var n int32
    if err := tx.QueryRowContext(ctx, qry, %s).Scan(&n); err != nil {
        return false, fmt.Errorf("%%s [%s]: %%w", qry, %s, err)
    }
    return n !=0, nil
}
`,
				Tbl,
				tbl, where,
				named,
				qS, members,
			)

			fmt.Fprintf(bw, `
func (t %s) Update(ctx context.Context, tx *sql.Tx) error {
    const qry = "UPDATE %s SET %s WHERE %s"
    if _, err := tx.ExecContext(ctx, qry, 
        %s,
    ); err != nil {
        return fmt.Errorf("%%s [%s]: %%w", qry, %s, err)
    }
    return nil
}
`,
				Tbl,
				tbl, set, where,
				strings.Join(an, ", "),
				qS, members,
			)
		}

		var prevIdx, prevTbl string
		cols := make([]string, 0, 4)
		for rows.Next() {
			var tbl, idx, col string
			if err = rows.Scan(&idx, &tbl, &col); err != nil {
				return fmt.Errorf("%s: %w", qry, err)
			}
			tbl = tableNames[tbl]
			if prevIdx != idx {
				if prevIdx != "" {
					A(prevIdx, prevTbl, cols)
					cols = cols[:0]
				}
				prevIdx, prevTbl = idx, tbl
			}
			if columns[tbl] == nil {
				panic("nil column")
			}
			c := columns[tbl][col]
			c.InIndex = append(c.InIndex, idx)
			columns[tbl][col] = c
			cols = append(cols, col)
		}
		if prevIdx != "" && len(cols) != 0 {
			A(prevIdx, prevTbl, cols)
		}
	}

	return nil
}

type Table struct {
	Name string
	Cols []Column
}

func (t Table) PrintStruct(w io.Writer) error {
	bw := bufio.NewWriter(w)
	defer bw.Flush()
	nm := camelCase(t.Name)
	bw.WriteString("type " + nm + " struct {\n")
	dc := make([]string, len(t.Cols))
	ph := make([]string, len(t.Cols))
	f := make([]string, len(t.Cols))
	qs := make([]string, len(t.Cols))
	for i, c := range t.Cols {
		c.PrintStruct(bw)
		dc[i] = c.Name
		ph[i] = ":" + strconv.FormatInt(int64(i)+1, 10)
		f[i] = "t." + camelCase(c.Name)
		qs[i] = "%v"
	}
	if _, err := bw.WriteString("}\n"); err != nil {
		return err
	}
	fs := strings.Join(f, ", ")
	fmt.Fprintf(bw, `func (t %s) Insert(ctx context.Context, tx *sql.Tx) error {
const qry = "INSERT INTO %s (%s)\nVALUES (%s)"
    if _, err := tx.ExecContext(ctx, qry, %s); err != nil {
        return fmt.Errorf("%%s [%s]: %%w",
            qry,
            %s,
            err)
    }
    return nil
}
`,
		nm,
		t.Name, strings.Join(dc, ", "), strings.Join(ph, ", "),
		fs,
		strings.Join(qs, ", "), fs,
	)

	return nil
}

type Column struct {
	Name, Type       string
	InIndex          []string
	Len, Prec, Scale int
	Nullable         bool
}

func camelCase(s string) string {
	s = oracall.CamelCase(s)
	for _, e := range []string{"Id", "Xml"} {
		if strings.HasSuffix(s, e) {
			return s[:len(s)-len(e)+1] + strings.ToUpper(e[1:])
		}
	}
	return s
}

func (c Column) PrintStruct(w io.Writer) error {
	_, err := fmt.Fprintf(w, "\t%s\t%s\n", camelCase(c.Name), c.GoType())
	return err
}
func (c Column) GoType() string {
	switch c.Type {
	case "DATE":
		if c.Nullable {
			return "sql.NullTime"
		}
		return "time.Time"
	case "NUMBER":
		if c.Scale > 0 {
			if c.Prec < 8 && !c.Nullable {
				return "float32"
			} else if c.Nullable {
				return "sql.NullFloat64"
			}
			return "float64"
		}
		if c.Prec < 10 {
			if c.Nullable {
				return "sql.NullInt32"
			}
			return "int32"
		} else if c.Prec < 19 {
			if c.Nullable {
				return "sql.NullInt64"
			}
			return "int64"
		}
	case "RAW":
		return "[]byte"
	}
	return "string"
}