this repo has no description
tulkdan.dev
1package src
2
3import (
4 "database/sql"
5 "encoding/json"
6)
7
8type DB struct {
9 connection *sql.DB
10}
11
12func CreateDB(db *sql.DB) (*DB, error) {
13 create := `
14 CREATE TABLE IF NOT EXISTS people (
15 id TEXT NOT NULL PRIMARY KEY,
16 name TEXT,
17 nickname TEXT,
18 birthdate DATETIME,
19 stacks JSONB
20 );`
21
22 if _, err := db.Exec(create); err != nil {
23 return nil, err
24 }
25
26 return &DB{connection: db}, nil
27}
28
29func (db *DB) InsertPeople(person Person) (int64, error) {
30 db.connection.Ping()
31 sql := "INSERT INTO people VALUES(?,?,?,?,?);"
32 statement, err := db.connection.Prepare(sql)
33 if err != nil {
34 return 0, err
35 }
36 defer statement.Close()
37 stack, _ := json.Marshal(person.Stack)
38
39 res, err := statement.Exec(person.Id, person.Name, person.Nickname, person.Birthdate, stack)
40 if err != nil {
41 return 0, err
42 }
43
44 var id int64
45 if id, err = res.LastInsertId(); err != nil {
46 return 0, err
47 }
48
49 return id, nil
50}
51
52func (db *DB) GetPersonById(id string) (Person, error) {
53 row := db.connection.QueryRow(`
54 SELECT
55 id,
56 name,
57 nickname,
58 birthdate,
59 stacks
60 FROM people
61 WHERE id = ?;`, id)
62
63 person := Person{}
64
65 var err error
66 if err = row.Scan(&person.Id, &person.Name, &person.Nickname, &person.Birthdate, &person.Stack); err != nil {
67 return Person{}, err
68 }
69
70 return person, nil
71}
72
73func (db *DB) SearchPerson(param string) ([]Person, error) {
74 rows, err := db.connection.Query(`
75 SELECT
76 id,
77 name,
78 nickname,
79 birthdate,
80 stacks
81 FROM people
82 WHERE LOWER(name) LIKE '%' || ? || '%'
83 OR LOWER(nickname) LIKE '%' || ? || '%'
84 OR LOWER(stacks) LIKE '%' || ? || '%';`, param, param, param)
85
86 if err != nil {
87 return nil, err
88 }
89 defer rows.Close()
90
91 people := []Person{}
92 for rows.Next() {
93 i := Person{}
94 if err = rows.Scan(&i.Id, &i.Name, &i.Nickname, &i.Birthdate, &i.Stack); err != nil {
95 return nil, err
96 }
97
98 people = append(people, i)
99 }
100
101 return people, nil
102}
103
104func (db *DB) CountAllPeople() (int, error) {
105 row := db.connection.QueryRow(`SELECT COUNT(*) FROM people;`)
106
107 var value int
108 if err := row.Scan(&value); err != nil {
109 return 0, err
110 }
111
112 return value, nil
113}