/
IvanTimofeev
/
cranberry
GigaCode
beta
ОбзорЦентр заботыВойти

cranberry

Форк
0
Ветки: 1Коммиты: 95Теги: 19
cranberry
/internal
/db
/
db.go 
395 строк · 9.3 Кб
1
package db
2


3
import (
4
	"database/sql"
5


6
	script "gitverse.ru/IvanTimofeev/cranberry/internal/db/sql"
7
	pbapi "gitverse.ru/IvanTimofeev/cranberry/pkg/grpc"
8
	"gitverse.ru/IvanTimofeev/cranberry/pkg/utility"
9
)
10


11
type Db struct {
12
	coon     *sql.DB
13
	host     string
14
	port     int
15
	login    string
16
	password string
17
	sqlPath  string
18
}
19


20
type Queryable interface {
21
	Query(query string, args ...any) (*sql.Rows, error)
22
}
23


24
func NewDb(host string, port int, login string, password string, sqlPath string) *Db {
25
	return &Db{
26
		host:     host,
27
		port:     port,
28
		login:    login,
29
		password: password,
30
		sqlPath:  sqlPath,
31
	}
32
}
33


34
func (db *Db) Connect() error {
35
	coon, err := sql.Open("sqlite3", "./cranberry.db")
36
	if err != nil {
37
		return err
38
	}
39
	db.coon = coon
40
	return nil
41
}
42


43
func (db *Db) Init() error {
44
	return db.RunTransaction(func(tx *sql.Tx) error {
45
		script, err := script.ReadInitSqlFile(db.sqlPath)
46
		if err != nil {
47
			return err
48
		}
49
		_, err = tx.Exec(string(script))
50
		if err != nil {
51
			return err
52
		}
53
		return nil
54
	})()
55
}
56


57
func (db *Db) SavePeer(name string, addr string, port int, tx *sql.Tx) error {
58
	stmt, err := tx.Prepare("INSERT INTO PEERS (NAME, ADDR, PORT) VALUES (?, ?, ?)")
59
	if err != nil {
60
		return err
61
	}
62
	_, err = stmt.Exec(name, addr, port)
63
	return err
64
}
65


66
func (db *Db) SaveTx(txc *pbapi.TxContainer, bc *pbapi.BlockContainer, tx *sql.Tx) error {
67
	stmt, err := tx.Prepare("INSERT INTO TXS (HASH, BLOCK_HEIGHT, ORDER_IN_BLOCK, FROM_ACC, TO_ACC, AMOUNT, SIGNATURE_R, SIGNATURE_S) VALUES (?, ?, ?, ?, ?, ?, ?, ?)")
68
	if err != nil {
69
		return err
70
	}
71
	from := utility.GetAddrFromPubKey(txc.PubKey)
72
	_, err = stmt.Exec(
73
		txc.Hash,
74
		bc.Header.Height,
75
		*txc.OrderInBlock,
76
		from,
77
		txc.Tx.To,
78
		txc.Tx.Amount,
79
		txc.Signature.R,
80
		txc.Signature.S,
81
	)
82
	return err
83
}
84


85
func (db *Db) SaveBlock(b *pbapi.BlockContainer, tx *sql.Tx) error {
86
	insertBlockHeaderStmt, err := tx.Prepare("INSERT INTO BLOCK_HEADERS (HASH, PREV_HASH, HEIGHT, MERKLE_ROOT) VALUES (?, ?, ?, ?)")
87
	if err != nil {
88
		return err
89
	}
90
	_, err = insertBlockHeaderStmt.Exec(
91
		b.Hash,
92
		b.Header.PrevHash,
93
		b.Header.Height,
94
		b.Header.MerkleRoot,
95
	)
96
	if err != nil {
97
		return err
98
	}
99


100
	for _, s := range b.ValidatorSignatures {
101
		insertBlockHeaderSignatureStmt, err := tx.Prepare("INSERT INTO BLOCK_HEADER_SIGNATURES (BLOCK_HEADER_HASH, VALIDATOR_ID, SIGNATURE_R, SIGNATURE_S) VALUES (?, ?, ?, ?)")
102
		if err != nil {
103
			return err
104
		}
105
		_, err = insertBlockHeaderSignatureStmt.Exec(
106
			b.Hash,
107
			s.Addr,
108
			s.Signature.R,
109
			s.Signature.S,
110
		)
111
		if err != nil {
112
			return err
113
		}
114
	}
115
	return nil
116
}
117


118
func (db *Db) SaveUint64Param(name string, value uint64, tx *sql.Tx) error {
119
	_, ok, err := db.GetUint64Param(name)
120
	if err != nil {
121
		return err
122
	}
123
	if ok {
124
		stmt, err := tx.Prepare("UPDATE PARAMS SET VALUE = ? WHERE NAME = ?")
125
		if err != nil {
126
			return err
127
		}
128
		_, err = stmt.Exec(value, name)
129
		if err != nil {
130
			return err
131
		}
132
	} else {
133
		stmt, err := tx.Prepare("INSERT INTO PARAMS (NAME, VALUE) VALUES (?, ?)")
134
		if err != nil {
135
			return err
136
		}
137
		_, err = stmt.Exec(name, value)
138
		if err != nil {
139
			return err
140
		}
141
	}
142
	return nil
143
}
144


145
func (db *Db) SaveString64Param(name string, value string, tx *sql.Tx) error {
146
	_, ok, err := db.GetString64Param(name)
147
	if err != nil {
148
		return err
149
	}
150
	if ok {
151
		stmt, err := tx.Prepare("UPDATE PARAMS SET VALUE = ? WHERE NAME = ?")
152
		if err != nil {
153
			return err
154
		}
155
		_, err = stmt.Exec(value, name)
156
		if err != nil {
157
			return err
158
		}
159
	} else {
160
		stmt, err := tx.Prepare("INSERT INTO PARAMS (NAME, VALUE) VALUES (?, ?)")
161
		if err != nil {
162
			return err
163
		}
164
		_, err = stmt.Exec(name, value)
165
		if err != nil {
166
			return err
167
		}
168
	}
169
	return nil
170
}
171


172
func (db *Db) GetUint64Param(name string) (uint64, bool, error) {
173
	rows, err := db.coon.Query("SELECT VALUE FROM PARAMS WHERE NAME = ?", name)
174
	if err != nil {
175
		return 0, false, err
176
	}
177
	defer rows.Close()
178
	if rows.Next() {
179
		var value uint64
180
		if err := rows.Scan(&value); err != nil {
181
			return 0, false, err
182
		}
183
		return value, true, nil
184
	}
185
	return 0, false, nil
186
}
187


188
func (db *Db) GetString64Param(name string) (string, bool, error) {
189
	rows, err := db.coon.Query("SELECT VALUE FROM PARAMS WHERE NAME = ?", name)
190
	if err != nil {
191
		return "", false, err
192
	}
193
	defer rows.Close()
194
	if rows.Next() {
195
		var value string
196
		if err := rows.Scan(&value); err != nil {
197
			return "", false, err
198
		}
199
		return value, true, nil
200
	}
201
	return "", false, nil
202
}
203


204
func (db *Db) GetBlockByHeight(height uint64) (*pbapi.BlockContainer, bool, error) {
205
	blockRows, err := db.coon.Query("SELECT HASH, PREV_HASH, HEIGHT, MERKLE_ROOT FROM BLOCK_HEADERS WHERE HEIGHT = ?", height)
206
	if err != nil {
207
		return nil, false, err
208
	}
209
	defer blockRows.Close()
210
	blockContainer := &pbapi.BlockContainer{
211
		Header:              &pbapi.BlockHeader{},
212
		ValidatorSignatures: []*pbapi.SignatureItem{},
213
	}
214
	if blockRows.Next() {
215
		if err := blockRows.Scan(
216
			&blockContainer.Hash,
217
			&blockContainer.Header.PrevHash,
218
			&blockContainer.Header.Height,
219
			&blockContainer.Header.MerkleRoot,
220
		); err != nil {
221
			return nil, false, err
222
		}
223
	}
224
	signaturesRows, err := db.coon.Query("SELECT VALIDATOR_ID, SIGNATURE_R, SIGNATURE_S FROM BLOCK_HEADER_SIGNATURES "+
225
		"WHERE BLOCK_HEADER_HASH = ?", blockContainer.Hash)
226
	if err != nil {
227
		return nil, false, err
228
	}
229
	defer signaturesRows.Close()
230
	for signaturesRows.Next() {
231
		si := &pbapi.SignatureItem{
232
			Signature: &pbapi.Signature{},
233
		}
234
		if err := signaturesRows.Scan(
235
			&si.Addr,
236
			&si.Signature.R,
237
			&si.Signature.S,
238
		); err != nil {
239
			return nil, false, err
240
		}
241
		blockContainer.ValidatorSignatures = append(blockContainer.ValidatorSignatures, si)
242
	}
243
	return blockContainer, true, nil
244
}
245


246
func (db *Db) GetAllTxsByBlockHeight(height uint64) ([]*pbapi.TxContainer, error) {
247
	rows, err := db.coon.Query("SELECT HASH, ORDER_IN_BLOCK, PUB_KEY, TO_ACC, AMOUNT, SIGNATURE_R, SIGNATURE_S FROM "+
248
		"TXS LEFT JOIN ACCOUNTS ON TXS.FROM_ACC = ACCOUNTS.ADDR "+
249
		"WHERE BLOCK_HEIGHT = ? ORDER BY ORDER_IN_BLOCK ASC", height)
250
	if err != nil {
251
		return nil, err
252
	}
253
	defer rows.Close()
254
	txcs := make([]*pbapi.TxContainer, 0)
255
	for rows.Next() {
256
		txc := &pbapi.TxContainer{
257
			Tx:        &pbapi.Tx{},
258
			Signature: &pbapi.Signature{},
259
		}
260
		err = rows.Scan(
261
			&txc.Hash,
262
			&txc.OrderInBlock,
263
			&txc.PubKey,
264
			&txc.Tx.To,
265
			&txc.Tx.Amount,
266
			&txc.Signature.R,
267
			&txc.Signature.S,
268
		)
269
		if err != nil {
270
			return nil, err
271
		}
272
		txcs = append(txcs, txc)
273
	}
274
	return txcs, nil
275
}
276


277
func (db *Db) SaveAccount(acc *pbapi.Account, tx *sql.Tx) error {
278
	_, ok, err := db.GetAccount(acc.Addr)
279
	if err != nil {
280
		return err
281
	}
282
	if ok {
283
		stmt, err := tx.Prepare("UPDATE ACCOUNTS SET PUB_KEY = ?, BALANCE = ? WHERE ADDR = ?")
284
		if err != nil {
285
			return err
286
		}
287
		_, err = stmt.Exec(acc.PubKey, acc.Balance, acc.Addr)
288
		if err != nil {
289
			return err
290
		}
291
	} else {
292
		stmt, err := tx.Prepare("INSERT INTO ACCOUNTS (ADDR, PUB_KEY, BALANCE) VALUES (?, ?, ?)")
293
		if err != nil {
294
			return err
295
		}
296
		_, err = stmt.Exec(acc.Addr, acc.PubKey, acc.Balance)
297
		if err != nil {
298
			return err
299
		}
300
	}
301
	return nil
302
}
303


304
func (db *Db) GetAccountByTx(addr string, dbtx *sql.Tx) (*pbapi.Account, bool, error) {
305
	return db.getAccount(addr, dbtx)
306
}
307


308
func (db *Db) GetAccount(addr string) (*pbapi.Account, bool, error) {
309
	return db.getAccount(addr, db.coon)
310
}
311


312
func (db *Db) getAccount(addr string, q Queryable) (*pbapi.Account, bool, error) {
313
	rows, err := q.Query("SELECT ADDR, PUB_KEY, BALANCE FROM ACCOUNTS WHERE ADDR = ?", addr)
314
	if err != nil {
315
		return nil, false, err
316
	}
317
	defer rows.Close()
318
	if rows.Next() {
319
		acc := &pbapi.Account{}
320
		if err := rows.Scan(
321
			&acc.Addr,
322
			&acc.PubKey,
323
			&acc.Balance); err != nil {
324
			return nil, false, err
325
		}
326
		return acc, true, nil
327
	}
328
	return nil, false, nil
329
}
330


331
func (db *Db) GetValidator(addr string) (*pbapi.Validator, bool, error) {
332
	rows, err := db.coon.Query("SELECT ADDR, PUB_KEY FROM VALIDATORS WHERE ADDR = ?", addr)
333
	if err != nil {
334
		return nil, false, err
335
	}
336
	defer rows.Close()
337
	if rows.Next() {
338
		validator := &pbapi.Validator{}
339
		if err := rows.Scan(
340
			&validator.Addr,
341
			&validator.PubKey); err != nil {
342
			return nil, false, err
343
		}
344
		return validator, true, nil
345
	}
346
	return nil, false, nil
347
}
348


349
func (db *Db) SaveValidator(validator *pbapi.Validator, tx *sql.Tx) error {
350
	_, ok, err := db.GetValidator(validator.Addr)
351
	if err != nil {
352
		return err
353
	}
354
	if ok {
355
		stmt, err := tx.Prepare("UPDATE VALIDATORS SET PUB_KEY = ? WHERE ADDR = ?")
356
		if err != nil {
357
			return err
358
		}
359
		_, err = stmt.Exec(validator.PubKey, validator.Addr)
360
		if err != nil {
361
			return err
362
		}
363
	} else {
364
		stmt, err := tx.Prepare("INSERT INTO VALIDATORS (ADDR, PUB_KEY) VALUES (?, ?)")
365
		if err != nil {
366
			return err
367
		}
368
		_, err = stmt.Exec(validator.Addr, validator.PubKey)
369
		if err != nil {
370
			return err
371
		}
372
	}
373
	return nil
374
}
375


376
func (db *Db) RunTransaction(txfn func(tx *sql.Tx) error) func() error {
377
	return func() error {
378
		tx, err := db.coon.Begin()
379
		if err != nil {
380
			return err
381
		}
382
		err = txfn(tx)
383
		if err != nil {
384
			if rerr := tx.Rollback(); rerr != nil {
385
				return rerr
386
			}
387
			return err
388
		}
389
		err = tx.Commit()
390
		if err != nil {
391
			return err
392
		}
393
		return nil
394
	}
395
}
396

Использование cookies

Мы используем файлы cookie в соответствии с Политикой конфиденциальности и Политикой использования cookies.

Нажимая кнопку «Принимаю», Вы даете АО «СберТех» согласие на обработку Ваших персональных данных в целях совершенствования нашего веб-сайта и Сервиса GitVerse, а также повышения удобства их использования.

Запретить использование cookies Вы можете самостоятельно в настройках Вашего браузера.