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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
package main
import (
"database/sql"
"fmt"
_ "github.com/denisenkom/go-mssqldb"
"strings"
)
type SQLConnection struct {
db *sql.DB
}
func (conn *SQLConnection) init() error {
//query := url.Values{}
//query.Add("app name", "MyAppName")
//
//u := &url.URL{
// Scheme: "sqlserver",
// User: url.UserPassword("sa", "Datab@sed1337"),
// Host: fmt.Sprintf("%s:%d", "", 1431),
// // Path: instance, // if connecting to an instance instead of a port
// RawQuery: query.Encode(),
//}
//var err error
//conn.db, err = sql.Open("sqlserver", u.String())
//return err
var err error
conn.db, err = sql.Open("sqlserver", "server=localhost;user id=sa;password=Datab@sed1337;")
return err
}
func (conn *SQLConnection) getTables() ([]string, error) {
result, err := conn.db.Query(
`SELECT TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME as TABLE_NAME
FROM shipping.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'`)
if err != nil {
return nil, err
}
var tables []string
for result.Next() {
var row string
_ = result.Scan(&row)
tables = append(tables, row)
}
return tables, nil
}
func (conn *SQLConnection) getViews() ([]string, error) {
result, err := conn.db.Query(
`select name from sys.views where is_ms_shipped=0;`)
if err != nil {
return nil, err
}
var tables []string
for result.Next() {
var row string
_ = result.Scan(&row)
tables = append(tables, row)
}
return tables, nil
}
func (conn *SQLConnection) getColumns(table string) ([]string, error) {
result, err := conn.db.Query(fmt.Sprintf("SELECT TOP 1 * FROM %s", table))
if err != nil {
return nil, nil
}
return result.Columns()
}
func (conn *SQLConnection) getIdentityColumns(table string) ([]string, error) {
result, err := conn.db.Query(
fmt.Sprintf(
`SELECT c.name FROM shipping.sys.objects o inner join shipping.sys.columns c on o.object_id = c.object_id
where c.is_identity = 1 and o.is_ms_shipped = 0 and o.name = N'%s'`, table))
if err != nil {
return nil, nil
}
var msg []string
cols, _ := result.Columns()
for result.Next() {
boxes := make([]string, len(cols))
dest := make([]interface{}, len(cols))
for i, _ := range dest {
dest[i] = &boxes[i]
}
_ = result.Scan(dest...)
msg = append(msg, boxes[0])
}
_ = result.Close()
return msg, nil
}
func (conn *SQLConnection) getTable(table string) ([][]string, error) {
var err error
var msg [][]string
result, err := conn.db.Query(fmt.Sprintf("SELECT * FROM %s", table))
if err != nil {
return msg, err
}
cols, _ := result.Columns()
for result.Next() {
boxes := make([]string, len(cols))
dest := make([]interface{}, len(cols))
for i, _ := range dest {
dest[i] = &boxes[i]
}
_ = result.Scan(dest...)
msg = append(msg, boxes)
}
_ = result.Close()
return msg, err
}
func (conn *SQLConnection) getOneToManyTable(manyTable string, manyColumn string, oneValue string) ([][]string, error) {
result, err := conn.db.Query(fmt.Sprintf(
"SELECT * FROM %s WHERE %s=N'%s'", manyTable, manyColumn, oneValue))
if err != nil {
return nil, err
}
var msg [][]string
cols, _ := result.Columns()
for result.Next() {
boxes := make([]string, len(cols))
dest := make([]interface{}, len(cols))
for i, _ := range dest {
dest[i] = &boxes[i]
}
_ = result.Scan(dest...)
msg = append(msg, boxes)
}
return msg, nil
}
func (conn *SQLConnection) deleteWhere(table string, constraint string) error {
_, err := conn.db.Query(fmt.Sprintf("DELETE FROM %s WHERE %s", table, constraint))
return err
}
func (conn *SQLConnection) insertIntoTable(table string, columns []string, values []string) error {
_, err := conn.db.Query(fmt.Sprintf(
"INSERT INTO %s (%s) VALUES (N'%s')",
table,
strings.Join(columns, ","),
strings.Join(values, "',N'")))
return err
}
|
