got rid of db package and put everything in reader

3 files changed, 139 insertions, 0 deletions

diff --git a/pkg/reader/db.go b/pkg/reader/db.go
new file mode 100644
index 0000000..6f62de1
--- /dev/null
+++ b/pkg/reader/db.go
@@ -0,0 +1,98 @@
+package reader
+
+import (
+	"database/sql"
+	"errors"
+	"fmt"
+	"log"
+	"os"
+	"time"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+func SetupDB(path string) (*sql.DB, error) {
+	stmt_base := `DROP TABLE IF EXISTS datamap;
+				  CREATE TABLE datamap(id INTEGER PRIMARY KEY, name TEXT, date_created TEXT);
+				  DROP TABLE IF EXISTS datamap_line;
+
+				  CREATE TABLE datamap_line(
+					id INTEGER PRIMARY KEY,   
+					dm_id INTEGER,            
+					key TEXT NOT NULL,        
+					sheet TEXT NOT NULL,      
+					cellref TEXT,             
+					FOREIGN KEY (dm_id)       
+					REFERENCES datamap(id) 
+					ON DELETE CASCADE      
+				  );                        
+				 `
+	os.Create(path)
+	db, err := sql.Open("sqlite3", path)
+	if err != nil {
+		return db, errors.New("Cannot open that damn database file")
+	}
+
+	// We probably don't need pragma here but we have it for later.
+	pragma := "PRAGMA foreign_keys = ON;"
+	_, err = db.Exec(pragma)
+	if err != nil {
+		// log.Printf("%q: %s\n", err, pragma)
+		return nil, err
+	}
+
+	_, err = db.Exec(stmt_base)
+	if err != nil {
+		// log.Printf("%q: %s\n", err, stmt_base)
+		return nil, err
+	}
+
+	return db, nil
+}
+
+// TODO - how do we avoid passing in all these params!??!
+//DatamapToDB takes a slice of DatamapLine and writes it to a sqlite3 db file.
+func DatamapToDB(d_path string, data []DatamapLine, dm_name string, dm_path string) error {
+	fmt.Printf("Importing datamap file %s and naming it %s.\n", dm_path, dm_name)
+	// db, err := SetupDB("/home/lemon/.config/datamaps-go/datamaps.db")
+	// if err != nil {
+	// 	return err
+	// }
+	d, err := sql.Open("sqlite3", d_path)
+	if err != nil {
+		return errors.New("Cannot open that damn database file")
+	}
+	tx, err := d.Begin()
+	if err != nil {
+		return err
+	}
+	pragma := "PRAGMA foreign_keys = ON;"
+	_, err = d.Exec(pragma)
+	if err != nil {
+		log.Printf("%q: %s\n", err, pragma)
+		return err
+	}
+	stmt_dm, err := tx.Prepare("INSERT INTO datamap (name, date_created) VALUES(?,?)")
+	if err != nil {
+		return err
+	}
+	_, err = stmt_dm.Exec(dm_name, time.Now())
+
+	stmt_dml, err := tx.Prepare("INSERT INTO datamap_line (dm_id, key, sheet, cellref) VALUES(?,?,?,?);")
+	if err != nil {
+		return err
+	}
+	defer stmt_dm.Close()
+	defer stmt_dml.Close()
+	for _, dml := range data {
+		_, err = stmt_dml.Exec(1, dml.Key, dml.Sheet, dml.Cellref)
+		if err != nil {
+			return err
+		}
+	}
+	err = tx.Commit()
+	if err != nil {
+		return err
+	}
+	return nil
+}
diff --git a/pkg/reader/db_test.go b/pkg/reader/db_test.go
new file mode 100644
index 0000000..5c33343
--- /dev/null
+++ b/pkg/reader/db_test.go
@@ -0,0 +1,41 @@
+package reader
+
+import (
+	"testing"
+)
+
+func TestOpenSQLiteFile(t *testing.T) {
+	db, err := SetupDB("./testdata/test.db")
+	defer db.Close()
+	if err != nil {
+		t.Fatal("Expected to be able to set up the database.")
+	}
+	stmt := `insert into datamap(id, name) values(1,'cock')`
+	_, err = db.Exec(stmt)
+	if err != nil {
+		t.Errorf("Cannot add record to db")
+	}
+	rows, err := db.Query("select name from datamap")
+	if err != nil {
+		t.Errorf("Cannot run select statement")
+	}
+	defer rows.Close()
+	for rows.Next() {
+		var name string
+		err = rows.Scan(&name)
+		if err != nil {
+			t.Errorf("Cannot scan resulting row")
+		}
+	}
+}
+
+func TestDatamapGoesIntoDB(t *testing.T) {
+	d, err := ReadDML("./testdata/datamap.csv")
+	if err != nil {
+		t.Fatal(err)
+	}
+	err = DatamapToDB("./testdata/test.db", d, "First Datamap", "./testdata/test.db")
+	if err != nil {
+		t.Errorf("Unable to write datamap to database file because %v.", err)
+	}
+}
diff --git a/pkg/reader/testdata/test.db b/pkg/reader/testdata/test.db
new file mode 100644
index 0000000..bbdc08f
--- /dev/null
+++ b/pkg/reader/testdata/test.db