-
Notifications
You must be signed in to change notification settings - Fork 1
/
cep_repository_sqlite.go
89 lines (79 loc) · 2.33 KB
/
cep_repository_sqlite.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
package cep
import (
"database/sql"
"fmt"
"sync"
"github.com/guionardo/go-gstools/tools"
_ "github.com/mattn/go-sqlite3" // Import go-sqlite3 library
)
type CEPRepositorySQLite struct {
db *sql.DB
lock sync.Mutex
}
func (r *CEPRepositorySQLite) GetCEP(cep string) (*CEP, error) {
r.lock.Lock()
defer r.lock.Unlock()
cep = tools.JustNumbers(cep)
row, err := r.db.Query("SELECT logradouro,tipo_logradouro,bairro,municipio,uf,data_requisicao FROM cep WHERE cep = ?", cep)
if err != nil {
return nil, err
}
defer row.Close()
for row.Next() {
cepData := &CEP{CEP: cep}
row.Scan(&cepData.Logradouro,
&cepData.TipoLogradouro,
&cepData.Bairro,
&cepData.Municipio,
&cepData.UF,
&cepData.DataRequisicao)
return cepData, nil
}
return nil, fmt.Errorf("CEP %s não encontrado", cep)
}
func (r *CEPRepositorySQLite) SaveCEP(cep *CEP) error {
r.lock.Lock()
defer r.lock.Unlock()
insertStudentSQL := `INSERT INTO cep(cep, logradouro, tipo_logradouro, bairro, municipio, uf, data_requisicao)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(cep) DO UPDATE SET logradouro = ?, tipo_logradouro = ?, bairro = ?, municipio = ?, uf = ?, data_requisicao = ?`
statement, err := r.db.Prepare(insertStudentSQL) // Prepare statement.
// This is good to avoid SQL injections
if err != nil {
return err
}
_, err = statement.Exec(tools.JustNumbers(cep.CEP), cep.Logradouro, cep.TipoLogradouro, cep.Bairro, cep.Municipio, cep.UF, cep.DataRequisicao,
cep.Logradouro, cep.TipoLogradouro, cep.Bairro, cep.Municipio, cep.UF, cep.DataRequisicao) // Execute statement
if err != nil {
return err
}
return nil
}
func NewCEPRepositorySQLite(connectionString string) (repo *CEPRepositorySQLite, err error) {
repo = &CEPRepositorySQLite{}
repo.db, err = sql.Open("sqlite3", connectionString)
if err != nil {
return
}
repo.lock.Lock()
defer repo.lock.Unlock()
createTable(repo.db)
return repo, nil
}
func createTable(db *sql.DB) error {
createCepTableSQL := `CREATE TABLE IF NOT EXISTS cep (
cep TEXT NOT NULL PRIMARY KEY,
logradouro TEXT,
tipo_logradouro TEXT,
bairro TEXT,
municipio TEXT,
uf TEXT,
data_requisicao DATETIME
);` // SQL Statement for Create Table
statement, err := db.Prepare(createCepTableSQL) // Prepare SQL Statement
if err != nil {
return err
}
_, err = statement.Exec() // Execute SQL Statements
return err
}