protocol/persistence.go
package protocol
import (
"bytes"
"context"
"database/sql"
"encoding/gob"
"encoding/json"
"strings"
"time"
"github.com/pkg/errors"
"github.com/ethereum/go-ethereum/log"
"github.com/mat/besticon/besticon"
"github.com/status-im/status-go/eth-node/crypto"
"github.com/status-im/status-go/images"
userimage "github.com/status-im/status-go/images"
multiaccountscommon "github.com/status-im/status-go/multiaccounts/common"
"github.com/status-im/status-go/protocol/common"
"github.com/status-im/status-go/protocol/protobuf"
"github.com/status-im/status-go/services/browsers"
)
var (
// ErrMsgAlreadyExist returned if msg already exist.
ErrMsgAlreadyExist = errors.New("message with given ID already exist")
HoursInTwoWeeks = 336
)
// sqlitePersistence wrapper around sql db with operations common for a client.
type sqlitePersistence struct {
*common.RawMessagesPersistence
db *sql.DB
}
func newSQLitePersistence(db *sql.DB) *sqlitePersistence {
return &sqlitePersistence{common.NewRawMessagesPersistence(db), db}
}
func (db sqlitePersistence) SaveChat(chat Chat) error {
err := chat.Validate()
if err != nil {
return err
}
return db.saveChat(nil, chat)
}
func (db sqlitePersistence) SaveChats(chats []*Chat) error {
tx, err := db.db.BeginTx(context.Background(), &sql.TxOptions{})
if err != nil {
return err
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
// don't shadow original error
_ = tx.Rollback()
}()
for _, chat := range chats {
err := db.saveChat(tx, *chat)
if err != nil {
return err
}
}
return nil
}
func (db sqlitePersistence) SaveContacts(contacts []*Contact) error {
tx, err := db.db.BeginTx(context.Background(), &sql.TxOptions{})
if err != nil {
return err
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
// don't shadow original error
_ = tx.Rollback()
}()
for _, contact := range contacts {
err := db.SaveContact(contact, tx)
if err != nil {
return err
}
}
return nil
}
func (db sqlitePersistence) saveChat(tx *sql.Tx, chat Chat) error {
var err error
if tx == nil {
tx, err = db.db.BeginTx(context.Background(), &sql.TxOptions{})
if err != nil {
return err
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
// don't shadow original error
_ = tx.Rollback()
}()
}
// Encode members
var encodedMembers bytes.Buffer
memberEncoder := gob.NewEncoder(&encodedMembers)
if err := memberEncoder.Encode(chat.Members); err != nil {
return err
}
// Encode membership updates
var encodedMembershipUpdates bytes.Buffer
membershipUpdatesEncoder := gob.NewEncoder(&encodedMembershipUpdates)
if err := membershipUpdatesEncoder.Encode(chat.MembershipUpdates); err != nil {
return err
}
// encode last message
var encodedLastMessage []byte
if chat.LastMessage != nil {
encodedLastMessage, err = json.Marshal(chat.LastMessage)
if err != nil {
return err
}
}
// Insert record
stmt, err := tx.Prepare(`INSERT INTO chats(id, name, color, emoji, active, type, timestamp, deleted_at_clock_value, unviewed_message_count, unviewed_mentions_count, last_clock_value, last_message, members, membership_updates, muted, muted_till, invitation_admin, profile, community_id, joined, synced_from, synced_to, first_message_timestamp, description, highlight, read_messages_at_clock_value, received_invitation_admin, image_payload)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?)`)
if err != nil {
return err
}
defer stmt.Close()
var imagePayload []byte
if len(chat.Base64Image) > 0 {
imagePayload, err = userimage.GetPayloadFromURI(chat.Base64Image)
if err != nil {
return err
}
}
_, err = stmt.Exec(
chat.ID,
chat.Name,
chat.Color,
chat.Emoji,
chat.Active,
chat.ChatType,
chat.Timestamp,
chat.DeletedAtClockValue,
chat.UnviewedMessagesCount,
chat.UnviewedMentionsCount,
chat.LastClockValue,
encodedLastMessage,
encodedMembers.Bytes(),
encodedMembershipUpdates.Bytes(),
chat.Muted,
chat.MuteTill,
chat.InvitationAdmin,
chat.Profile,
chat.CommunityID,
chat.Joined,
chat.SyncedFrom,
chat.SyncedTo,
chat.FirstMessageTimestamp,
chat.Description,
chat.Highlight,
chat.ReadMessagesAtClockValue,
chat.ReceivedInvitationAdmin,
imagePayload,
)
if err != nil {
return err
}
return err
}
func (db sqlitePersistence) SetSyncTimestamps(syncedFrom, syncedTo uint32, chatID string) error {
_, err := db.db.Exec(`UPDATE chats SET synced_from = ?, synced_to = ? WHERE id = ?`, syncedFrom, syncedTo, chatID)
return err
}
func (db sqlitePersistence) DeleteChat(chatID string) (err error) {
var tx *sql.Tx
tx, err = db.db.BeginTx(context.Background(), &sql.TxOptions{})
if err != nil {
return
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
// don't shadow original error
_ = tx.Rollback()
}()
_, err = tx.Exec("DELETE FROM chats WHERE id = ?", chatID)
if err != nil {
return
}
_, err = tx.Exec(`DELETE FROM user_messages WHERE local_chat_id = ?`, chatID)
return
}
func (db sqlitePersistence) MuteChat(chatID string, mutedTill time.Time) error {
mutedTillFormatted := mutedTill.Format(time.RFC3339)
_, err := db.db.Exec("UPDATE chats SET muted = 1, muted_till = ? WHERE id = ?", mutedTillFormatted, chatID)
return err
}
func (db sqlitePersistence) UnmuteChat(chatID string) error {
_, err := db.db.Exec("UPDATE chats SET muted = 0, muted_till = 0 WHERE id = ?", chatID)
return err
}
func (db sqlitePersistence) Chats() ([]*Chat, error) {
return db.chats(nil)
}
func (db sqlitePersistence) chats(tx *sql.Tx) (chats []*Chat, err error) {
if tx == nil {
tx, err = db.db.BeginTx(context.Background(), &sql.TxOptions{})
if err != nil {
return
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
// don't shadow original error
_ = tx.Rollback()
}()
}
rows, err := tx.Query(`
SELECT
chats.id,
chats.name,
chats.color,
chats.emoji,
chats.active,
chats.type,
chats.timestamp,
chats.deleted_at_clock_value,
chats.read_messages_at_clock_value,
chats.unviewed_message_count,
chats.unviewed_mentions_count,
chats.last_clock_value,
chats.last_message,
chats.members,
chats.membership_updates,
chats.muted,
chats.muted_till,
chats.invitation_admin,
chats.profile,
chats.community_id,
chats.joined,
chats.synced_from,
chats.synced_to,
chats.first_message_timestamp,
chats.description,
contacts.alias,
chats.highlight,
chats.received_invitation_admin,
chats.image_payload
FROM chats LEFT JOIN contacts ON chats.id = contacts.id
ORDER BY chats.timestamp DESC
`)
if err != nil {
return
}
defer rows.Close()
for rows.Next() {
var (
alias sql.NullString
invitationAdmin sql.NullString
profile sql.NullString
syncedFrom sql.NullInt64
syncedTo sql.NullInt64
firstMessageTimestamp sql.NullInt64
MuteTill sql.NullTime
chat Chat
encodedMembers []byte
encodedMembershipUpdates []byte
lastMessageBytes []byte
imagePayload []byte
)
err = rows.Scan(
&chat.ID,
&chat.Name,
&chat.Color,
&chat.Emoji,
&chat.Active,
&chat.ChatType,
&chat.Timestamp,
&chat.DeletedAtClockValue,
&chat.ReadMessagesAtClockValue,
&chat.UnviewedMessagesCount,
&chat.UnviewedMentionsCount,
&chat.LastClockValue,
&lastMessageBytes,
&encodedMembers,
&encodedMembershipUpdates,
&chat.Muted,
&MuteTill,
&invitationAdmin,
&profile,
&chat.CommunityID,
&chat.Joined,
&syncedFrom,
&syncedTo,
&firstMessageTimestamp,
&chat.Description,
&alias,
&chat.Highlight,
&chat.ReceivedInvitationAdmin,
&imagePayload,
)
if err != nil {
return
}
if invitationAdmin.Valid {
chat.InvitationAdmin = invitationAdmin.String
}
if profile.Valid {
chat.Profile = profile.String
}
// Restore members
membersDecoder := gob.NewDecoder(bytes.NewBuffer(encodedMembers))
err = membersDecoder.Decode(&chat.Members)
if err != nil {
return
}
// Restore membership updates
membershipUpdatesDecoder := gob.NewDecoder(bytes.NewBuffer(encodedMembershipUpdates))
err = membershipUpdatesDecoder.Decode(&chat.MembershipUpdates)
if err != nil {
return
}
if syncedFrom.Valid {
chat.SyncedFrom = uint32(syncedFrom.Int64)
}
if syncedTo.Valid {
chat.SyncedTo = uint32(syncedTo.Int64)
}
if firstMessageTimestamp.Valid {
chat.FirstMessageTimestamp = uint32(firstMessageTimestamp.Int64)
}
if imagePayload != nil {
base64Image, err := userimage.GetPayloadDataURI(imagePayload)
if err == nil {
chat.Base64Image = base64Image
}
}
// Restore last message
if lastMessageBytes != nil {
message := common.NewMessage()
if err = json.Unmarshal(lastMessageBytes, message); err != nil {
return
}
chat.LastMessage = message
}
if MuteTill.Valid {
chat.MuteTill = MuteTill.Time
}
chat.Alias = alias.String
chats = append(chats, &chat)
}
return
}
func (db sqlitePersistence) Chat(chatID string) (*Chat, error) {
var (
chat Chat
encodedMembers []byte
encodedMembershipUpdates []byte
lastMessageBytes []byte
invitationAdmin sql.NullString
profile sql.NullString
syncedFrom sql.NullInt64
syncedTo sql.NullInt64
firstMessageTimestamp sql.NullInt64
MuteTill sql.NullTime
imagePayload []byte
)
var unviewedMessagesCount int
var unviewedMentionsCount int
err := db.db.QueryRow(`
SELECT
id,
name,
color,
emoji,
active,
type,
timestamp,
read_messages_at_clock_value,
deleted_at_clock_value,
unviewed_message_count,
unviewed_mentions_count,
last_clock_value,
last_message,
members,
membership_updates,
muted,
muted_till,
invitation_admin,
profile,
community_id,
joined,
description,
highlight,
received_invitation_admin,
synced_from,
synced_to,
first_message_timestamp,
image_payload
FROM chats
WHERE id = ?
`, chatID).Scan(&chat.ID,
&chat.Name,
&chat.Color,
&chat.Emoji,
&chat.Active,
&chat.ChatType,
&chat.Timestamp,
&chat.ReadMessagesAtClockValue,
&chat.DeletedAtClockValue,
&unviewedMessagesCount,
&unviewedMentionsCount,
&chat.LastClockValue,
&lastMessageBytes,
&encodedMembers,
&encodedMembershipUpdates,
&chat.Muted,
&MuteTill,
&invitationAdmin,
&profile,
&chat.CommunityID,
&chat.Joined,
&chat.Description,
&chat.Highlight,
&chat.ReceivedInvitationAdmin,
&syncedFrom,
&syncedTo,
&firstMessageTimestamp,
&imagePayload,
)
switch err {
case sql.ErrNoRows:
return nil, nil
case nil:
if syncedFrom.Valid {
chat.SyncedFrom = uint32(syncedFrom.Int64)
}
if syncedTo.Valid {
chat.SyncedTo = uint32(syncedTo.Int64)
}
if firstMessageTimestamp.Valid {
chat.FirstMessageTimestamp = uint32(firstMessageTimestamp.Int64)
}
if invitationAdmin.Valid {
chat.InvitationAdmin = invitationAdmin.String
}
if profile.Valid {
chat.Profile = profile.String
}
// Set UnviewedCounts and make sure they are above 0
// Since Chat's UnviewedMessagesCount is uint and the SQL column is INT, it can create a discrepancy
if unviewedMessagesCount < 0 {
unviewedMessagesCount = 0
}
if unviewedMentionsCount < 0 {
unviewedMentionsCount = 0
}
chat.UnviewedMessagesCount = uint(unviewedMessagesCount)
chat.UnviewedMentionsCount = uint(unviewedMentionsCount)
// Restore members
chat.Members = []ChatMember{}
membersDecoder := gob.NewDecoder(bytes.NewBuffer(encodedMembers))
err = membersDecoder.Decode(&chat.Members)
if err != nil {
return nil, err
}
// Restore membership updates
membershipUpdatesDecoder := gob.NewDecoder(bytes.NewBuffer(encodedMembershipUpdates))
err = membershipUpdatesDecoder.Decode(&chat.MembershipUpdates)
if err != nil {
return nil, err
}
// Restore last message
if lastMessageBytes != nil {
message := common.NewMessage()
if err = json.Unmarshal(lastMessageBytes, message); err != nil {
return nil, err
}
chat.LastMessage = message
}
if imagePayload != nil {
base64Image, err := userimage.GetPayloadDataURI(imagePayload)
if err == nil {
chat.Base64Image = base64Image
}
}
if MuteTill.Valid {
chat.MuteTill = MuteTill.Time
}
return &chat, nil
}
return nil, err
}
func (db sqlitePersistence) Contacts() ([]*Contact, error) {
allContacts := make(map[string]*Contact)
rows, err := db.db.Query(`
SELECT
c.id,
c.address,
v.name,
v.verified,
c.alias,
c.display_name,
c.customization_color,
c.identicon,
c.last_updated,
c.last_updated_locally,
c.blocked,
c.removed,
c.bio,
c.local_nickname,
c.contact_request_state,
c.contact_request_local_clock,
c.contact_request_remote_state,
c.contact_request_remote_clock,
i.image_type,
i.payload,
i.clock_value,
COALESCE(c.verification_status, 0) as verification_status,
COALESCE(t.trust_status, 0) as trust_status
FROM contacts c
LEFT JOIN chat_identity_contacts i ON c.id = i.contact_id
LEFT JOIN ens_verification_records v ON c.id = v.public_key
LEFT JOIN trusted_users t ON c.id = t.id;
`)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var (
contact Contact
nickname sql.NullString
contactRequestLocalState sql.NullInt64
contactRequestLocalClock sql.NullInt64
contactRequestRemoteState sql.NullInt64
contactRequestRemoteClock sql.NullInt64
displayName sql.NullString
customizationColor sql.NullString
imageType sql.NullString
ensName sql.NullString
ensVerified sql.NullBool
blocked sql.NullBool
removed sql.NullBool
bio sql.NullString
lastUpdatedLocally sql.NullInt64
identityImageClock sql.NullInt64
imagePayload []byte
)
contact.Images = make(map[string]images.IdentityImage)
err := rows.Scan(
&contact.ID,
&contact.Address,
&ensName,
&ensVerified,
&contact.Alias,
&displayName,
&customizationColor,
&contact.Identicon,
&contact.LastUpdated,
&lastUpdatedLocally,
&blocked,
&removed,
&bio,
&nickname,
&contactRequestLocalState,
&contactRequestLocalClock,
&contactRequestRemoteState,
&contactRequestRemoteClock,
&imageType,
&imagePayload,
&identityImageClock,
&contact.VerificationStatus,
&contact.TrustStatus,
)
if err != nil {
return nil, err
}
if nickname.Valid {
contact.LocalNickname = nickname.String
}
if bio.Valid {
contact.Bio = bio.String
}
if contactRequestLocalState.Valid {
contact.ContactRequestLocalState = ContactRequestState(contactRequestLocalState.Int64)
}
if contactRequestLocalClock.Valid {
contact.ContactRequestLocalClock = uint64(contactRequestLocalClock.Int64)
}
if contactRequestRemoteState.Valid {
contact.ContactRequestRemoteState = ContactRequestState(contactRequestRemoteState.Int64)
}
if contactRequestRemoteClock.Valid {
contact.ContactRequestRemoteClock = uint64(contactRequestRemoteClock.Int64)
}
if displayName.Valid {
contact.DisplayName = displayName.String
}
if customizationColor.Valid {
contact.CustomizationColor = multiaccountscommon.CustomizationColor(customizationColor.String)
}
if ensName.Valid {
contact.EnsName = ensName.String
}
if ensVerified.Valid {
contact.ENSVerified = ensVerified.Bool
}
if blocked.Valid {
contact.Blocked = blocked.Bool
}
if removed.Valid {
contact.Removed = removed.Bool
}
if lastUpdatedLocally.Valid {
contact.LastUpdatedLocally = uint64(lastUpdatedLocally.Int64)
}
previousContact, ok := allContacts[contact.ID]
if !ok {
if imageType.Valid {
contact.Images[imageType.String] = images.IdentityImage{Name: imageType.String, Payload: imagePayload, Clock: uint64(identityImageClock.Int64)}
}
allContacts[contact.ID] = &contact
} else if imageType.Valid {
previousContact.Images[imageType.String] = images.IdentityImage{Name: imageType.String, Payload: imagePayload, Clock: uint64(identityImageClock.Int64)}
allContacts[contact.ID] = previousContact
}
}
var response []*Contact
for key := range allContacts {
response = append(response, allContacts[key])
}
return response, nil
}
func extractImageTypes(images map[string]*protobuf.IdentityImage) []string {
uniqueImageTypesMap := make(map[string]struct{})
for key := range images {
uniqueImageTypesMap[key] = struct{}{}
}
var uniqueImageTypes []string
for key := range uniqueImageTypesMap {
uniqueImageTypes = append(uniqueImageTypes, key)
}
return uniqueImageTypes
}
func generatePlaceholders(count int) string {
placeholders := make([]string, count)
for i := 0; i < count; i++ {
placeholders[i] = "?"
}
return strings.Join(placeholders, ", ")
}
func (db sqlitePersistence) UpdateContactChatIdentity(contactID string, chatIdentity *protobuf.ChatIdentity) (clockUpdated, imagesUpdated bool, err error) {
if chatIdentity.Clock == 0 {
return false, false, errors.New("clock value unset")
}
tx, err := db.db.BeginTx(context.Background(), &sql.TxOptions{})
if err != nil {
return false, false, err
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
// don't shadow original error
_ = tx.Rollback()
}()
extractedImageTypes := extractImageTypes(chatIdentity.Images)
query := "DELETE FROM chat_identity_contacts WHERE contact_id = ?"
if len(extractedImageTypes) > 0 {
query += " AND image_type NOT IN (" + generatePlaceholders(len(extractedImageTypes)) + ")"
}
stmt, err := tx.Prepare(query)
if err != nil {
return false, false, err
}
defer stmt.Close()
args := make([]interface{}, len(extractedImageTypes)+1)
args[0] = contactID
for i, v := range extractedImageTypes {
args[i+1] = v
}
result, err := stmt.Exec(args...)
if err != nil {
return false, false, err
}
imagesUpdated = false
if rowsAffected, err := result.RowsAffected(); err == nil && rowsAffected > 0 {
imagesUpdated = true
}
updateClock := func() (updated bool, err error) {
var newerClockEntryExists bool
err = tx.QueryRow(`SELECT EXISTS(SELECT 1 FROM chat_identity_last_received WHERE chat_id = ? AND clock_value >= ?)`, contactID, chatIdentity.Clock).Scan(&newerClockEntryExists)
if err != nil {
return false, err
}
if newerClockEntryExists {
return false, nil
}
stmt, err := tx.Prepare("INSERT INTO chat_identity_last_received (chat_id, clock_value) VALUES (?, ?)")
if err != nil {
return false, err
}
defer stmt.Close()
_, err = stmt.Exec(
contactID,
chatIdentity.Clock,
)
if err != nil {
return false, err
}
return true, nil
}
clockUpdated, err = updateClock()
if err != nil {
return false, false, err
}
for imageType, image := range chatIdentity.Images {
var exists bool
err := tx.QueryRow(`SELECT EXISTS(SELECT 1 FROM chat_identity_contacts WHERE contact_id = ? AND image_type = ? AND clock_value >= ?)`, contactID, imageType, chatIdentity.Clock).Scan(&exists)
if err != nil {
return clockUpdated, false, err
}
if exists {
continue
}
stmt, err := tx.Prepare(`INSERT INTO chat_identity_contacts (contact_id, image_type, clock_value, payload) VALUES (?, ?, ?, ?)`)
if err != nil {
return clockUpdated, false, err
}
defer stmt.Close()
if image.Payload == nil {
continue
}
// TODO implement something that doesn't reject all images if a single image fails validation
// Validate image URI to make sure it's serializable
_, err = images.GetPayloadDataURI(image.Payload)
if err != nil {
return clockUpdated, false, err
}
_, err = stmt.Exec(
contactID,
imageType,
chatIdentity.Clock,
image.Payload,
)
if err != nil {
return false, false, err
}
imagesUpdated = true
}
return
}
func (db sqlitePersistence) ExpiredMessagesIDs(maxSendCount int) ([]string, error) {
ids := []string{}
rows, err := db.db.Query(`
SELECT
id
FROM
raw_messages
WHERE
(message_type IN (?, ?) OR resend_type=?) AND sent = ? AND send_count <= ?`,
protobuf.ApplicationMetadataMessage_CHAT_MESSAGE,
protobuf.ApplicationMetadataMessage_EMOJI_REACTION,
common.ResendTypeRawMessage,
false,
maxSendCount)
if err != nil {
return ids, err
}
defer rows.Close()
for rows.Next() {
var id string
if err := rows.Scan(&id); err != nil {
return ids, err
}
ids = append(ids, id)
}
return ids, nil
}
func (db sqlitePersistence) SaveContact(contact *Contact, tx *sql.Tx) (err error) {
if tx == nil {
tx, err = db.db.BeginTx(context.Background(), &sql.TxOptions{})
if err != nil {
return
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
// don't shadow original error
_ = tx.Rollback()
}()
}
// Insert record
// NOTE: name, photo and tribute_to_talk are not used anymore, but it's not nullable
// Removing it requires copying over the table which might be expensive
// when there are many contacts, so best avoiding it
stmt, err := tx.Prepare(`
INSERT INTO contacts(
id,
address,
alias,
display_name,
customization_color,
identicon,
last_updated,
last_updated_locally,
local_nickname,
contact_request_state,
contact_request_local_clock,
contact_request_remote_state,
contact_request_remote_clock,
blocked,
removed,
verification_status,
bio,
name,
photo,
tribute_to_talk
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`)
if err != nil {
return
}
defer stmt.Close()
_, err = stmt.Exec(
contact.ID,
contact.Address,
contact.Alias,
contact.DisplayName,
contact.CustomizationColor,
contact.Identicon,
contact.LastUpdated,
contact.LastUpdatedLocally,
contact.LocalNickname,
contact.ContactRequestLocalState,
contact.ContactRequestLocalClock,
contact.ContactRequestRemoteState,
contact.ContactRequestRemoteClock,
contact.Blocked,
contact.Removed,
contact.VerificationStatus,
contact.Bio,
//TODO we need to drop these columns
"",
"",
"",
)
return
}
func (db sqlitePersistence) SaveTransactionToValidate(transaction *TransactionToValidate) error {
compressedKey := crypto.CompressPubkey(transaction.From)
_, err := db.db.Exec(`INSERT INTO messenger_transactions_to_validate(
command_id,
message_id,
transaction_hash,
retry_count,
first_seen,
public_key,
signature,
to_validate)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
transaction.CommandID,
transaction.MessageID,
transaction.TransactionHash,
transaction.RetryCount,
transaction.FirstSeen,
compressedKey,
transaction.Signature,
transaction.Validate,
)
return err
}
func (db sqlitePersistence) UpdateTransactionToValidate(transaction *TransactionToValidate) error {
_, err := db.db.Exec(`UPDATE messenger_transactions_to_validate
SET retry_count = ?, to_validate = ?
WHERE transaction_hash = ?`,
transaction.RetryCount,
transaction.Validate,
transaction.TransactionHash,
)
return err
}
func (db sqlitePersistence) TransactionsToValidate() ([]*TransactionToValidate, error) {
var transactions []*TransactionToValidate
rows, err := db.db.Query(`
SELECT
command_id,
message_id,
transaction_hash,
retry_count,
first_seen,
public_key,
signature,
to_validate
FROM messenger_transactions_to_validate
WHERE to_validate = 1;
`)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var t TransactionToValidate
var pkBytes []byte
err = rows.Scan(
&t.CommandID,
&t.MessageID,
&t.TransactionHash,
&t.RetryCount,
&t.FirstSeen,
&pkBytes,
&t.Signature,
&t.Validate,
)
if err != nil {
return nil, err
}
publicKey, err := crypto.DecompressPubkey(pkBytes)
if err != nil {
return nil, err
}
t.From = publicKey
transactions = append(transactions, &t)
}
return transactions, nil
}
func (db sqlitePersistence) GetWhenChatIdentityLastPublished(chatID string) (t int64, hash []byte, err error) {
rows, err := db.db.Query("SELECT clock_value, hash FROM chat_identity_last_published WHERE chat_id = ?", chatID)
if err != nil {
return t, nil, err
}
defer func() {
err = rows.Close()
}()
for rows.Next() {
err = rows.Scan(&t, &hash)
if err != nil {
return t, nil, err
}
}
return t, hash, nil
}
func (db sqlitePersistence) SaveWhenChatIdentityLastPublished(chatID string, hash []byte) (err error) {
tx, err := db.db.BeginTx(context.Background(), &sql.TxOptions{})
if err != nil {
return err
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
// don't shadow original error
_ = tx.Rollback()
}()
stmt, err := tx.Prepare("INSERT INTO chat_identity_last_published (chat_id, clock_value, hash) VALUES (?, ?, ?)")
if err != nil {
return err
}
defer stmt.Close()
_, err = stmt.Exec(chatID, time.Now().Unix(), hash)
if err != nil {
return err
}
return nil
}
func (db sqlitePersistence) ResetWhenChatIdentityLastPublished(chatID string) (err error) {
tx, err := db.db.BeginTx(context.Background(), &sql.TxOptions{})
if err != nil {
return err
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
// don't shadow original error
_ = tx.Rollback()
}()
stmt, err := tx.Prepare("INSERT INTO chat_identity_last_published (chat_id, clock_value, hash) VALUES (?, ?, ?)")
if err != nil {
return err
}
defer stmt.Close()
_, err = stmt.Exec(chatID, 0, []byte("."))
if err != nil {
return err
}
return nil
}
func (db sqlitePersistence) InsertStatusUpdate(userStatus UserStatus) error {
_, err := db.db.Exec(`INSERT INTO status_updates(
public_key,
status_type,
clock,
custom_text)
VALUES (?, ?, ?, ?)`,
userStatus.PublicKey,
userStatus.StatusType,
userStatus.Clock,
userStatus.CustomText,
)
return err
}
func (db sqlitePersistence) CleanOlderStatusUpdates() error {
now := time.Now()
twoWeeksAgo := now.Add(time.Duration(-1*HoursInTwoWeeks) * time.Hour)
_, err := db.db.Exec(`DELETE FROM status_updates WHERE clock < ?`,
uint64(twoWeeksAgo.Unix()),
)
return err
}
func (db sqlitePersistence) StatusUpdates() (statusUpdates []UserStatus, err error) {
rows, err := db.db.Query(`
SELECT
public_key,
status_type,
clock,
custom_text
FROM status_updates
`)
if err != nil {
return
}
defer rows.Close()
for rows.Next() {
var userStatus UserStatus
err = rows.Scan(
&userStatus.PublicKey,
&userStatus.StatusType,
&userStatus.Clock,
&userStatus.CustomText,
)
if err != nil {
return
}
statusUpdates = append(statusUpdates, userStatus)
}
return
}
func (db sqlitePersistence) DeleteSwitcherCard(cardID string) error {
_, err := db.db.Exec("DELETE from switcher_cards WHERE card_id = ?", cardID)
return err
}
func (db sqlitePersistence) UpsertSwitcherCard(switcherCard SwitcherCard) error {
_, err := db.db.Exec(`INSERT INTO switcher_cards(
card_id,
type,
clock,
screen_id)
VALUES (?, ?, ?, ?)`,
switcherCard.CardID,
switcherCard.Type,
switcherCard.Clock,
switcherCard.ScreenID,
)
return err
}
func (db sqlitePersistence) SwitcherCards() (switcherCards []SwitcherCard, err error) {
rows, err := db.db.Query(`
SELECT
card_id,
type,
clock,
screen_id
FROM switcher_cards
`)
if err != nil {
return
}
defer rows.Close()
for rows.Next() {
var switcherCard SwitcherCard
err = rows.Scan(
&switcherCard.CardID,
&switcherCard.Type,
&switcherCard.Clock,
&switcherCard.ScreenID,
)
if err != nil {
return
}
switcherCards = append(switcherCards, switcherCard)
}
return
}
func (db sqlitePersistence) NextHigherClockValueOfAutomaticStatusUpdates(clock uint64) (uint64, error) {
var nextClock uint64
err := db.db.QueryRow(`
SELECT clock
FROM status_updates
WHERE clock > ? AND status_type = ?
LIMIT 1
`, clock, protobuf.StatusUpdate_AUTOMATIC).Scan(&nextClock)
switch err {
case sql.ErrNoRows:
return 0, common.ErrRecordNotFound
case nil:
return nextClock, nil
default:
return 0, err
}
}
func (db sqlitePersistence) DeactivatedAutomaticStatusUpdates(fromClock uint64, tillClock uint64) (statusUpdates []UserStatus, err error) {
rows, err := db.db.Query(`
SELECT
public_key,
?,
clock + 1,
custom_text
FROM status_updates
WHERE clock > ? AND clock <= ? AND status_type = ?
`, protobuf.StatusUpdate_INACTIVE, fromClock, tillClock, protobuf.StatusUpdate_AUTOMATIC)
if err != nil {
return
}
defer rows.Close()
for rows.Next() {
var userStatus UserStatus
err = rows.Scan(
&userStatus.PublicKey,
&userStatus.StatusType,
&userStatus.Clock,
&userStatus.CustomText,
)
if err != nil {
return
}
statusUpdates = append(statusUpdates, userStatus)
}
return
}
func (db *sqlitePersistence) AddBookmark(bookmark browsers.Bookmark) (browsers.Bookmark, error) {
tx, err := db.db.Begin()
if err != nil {
return bookmark, err
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
_ = tx.Rollback()
}()
insert, err := tx.Prepare("INSERT OR REPLACE INTO bookmarks (url, name, image_url, removed, clock) VALUES (?, ?, ?, ?, ?)")
if err != nil {
return bookmark, err
}
// Get the right icon
finder := besticon.IconFinder{}
icons, iconError := finder.FetchIcons(bookmark.URL)
if iconError == nil && len(icons) > 0 {
icon := finder.IconInSizeRange(besticon.SizeRange{Min: 48, Perfect: 48, Max: 100})
if icon != nil {
bookmark.ImageURL = icon.URL
} else {
bookmark.ImageURL = icons[0].URL
}
} else {
log.Error("error getting the bookmark icon", "iconError", iconError)
}
_, err = insert.Exec(bookmark.URL, bookmark.Name, bookmark.ImageURL, bookmark.Removed, bookmark.Clock)
return bookmark, err
}
func (db *sqlitePersistence) AddBrowser(browser browsers.Browser) (err error) {
tx, err := db.db.Begin()
if err != nil {
return
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
_ = tx.Rollback()
}()
insert, err := tx.Prepare("INSERT OR REPLACE INTO browsers(id, name, timestamp, dapp, historyIndex) VALUES(?, ?, ?, ?, ?)")
if err != nil {
return
}
_, err = insert.Exec(browser.ID, browser.Name, browser.Timestamp, browser.Dapp, browser.HistoryIndex)
insert.Close()
if err != nil {
return
}
if len(browser.History) == 0 {
return
}
bhInsert, err := tx.Prepare("INSERT INTO browsers_history(browser_id, history) VALUES(?, ?)")
if err != nil {
return
}
defer bhInsert.Close()
for _, history := range browser.History {
_, err = bhInsert.Exec(browser.ID, history)
if err != nil {
return
}
}
return
}
func (db *sqlitePersistence) InsertBrowser(browser browsers.Browser) (err error) {
tx, err := db.db.Begin()
if err != nil {
return
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
_ = tx.Rollback()
}()
bInsert, err := tx.Prepare("INSERT OR REPLACE INTO browsers(id, name, timestamp, dapp, historyIndex) VALUES(?, ?, ?, ?, ?)")
if err != nil {
return
}
_, err = bInsert.Exec(browser.ID, browser.Name, browser.Timestamp, browser.Dapp, browser.HistoryIndex)
bInsert.Close()
if err != nil {
return
}
if len(browser.History) == 0 {
return
}
bhInsert, err := tx.Prepare("INSERT INTO browsers_history(browser_id, history) VALUES(?, ?)")
if err != nil {
return
}
defer bhInsert.Close()
for _, history := range browser.History {
_, err = bhInsert.Exec(browser.ID, history)
if err != nil {
return
}
}
return
}
func (db *sqlitePersistence) RemoveBookmark(url string, deletedAt uint64) error {
tx, err := db.db.Begin()
if err != nil {
return err
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
_ = tx.Rollback()
}()
_, err = tx.Exec(`UPDATE bookmarks SET removed = 1, deleted_at = ? WHERE url = ?`, deletedAt, url)
return err
}
func (db *sqlitePersistence) GetBrowsers() (rst []*browsers.Browser, err error) {
tx, err := db.db.Begin()
if err != nil {
return
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
_ = tx.Rollback()
}()
// FULL and RIGHT joins are not supported
bRows, err := tx.Query("SELECT id, name, timestamp, dapp, historyIndex FROM browsers ORDER BY timestamp DESC")
if err != nil {
return
}
defer bRows.Close()
browsersArr := map[string]*browsers.Browser{}
for bRows.Next() {
browser := browsers.Browser{}
err = bRows.Scan(&browser.ID, &browser.Name, &browser.Timestamp, &browser.Dapp, &browser.HistoryIndex)
if err != nil {
return nil, err
}
browsersArr[browser.ID] = &browser
rst = append(rst, &browser)
}
bhRows, err := tx.Query("SELECT browser_id, history from browsers_history")
if err != nil {
return
}
defer bhRows.Close()
var (
id string
history string
)
for bhRows.Next() {
err = bhRows.Scan(&id, &history)
if err != nil {
return
}
browsersArr[id].History = append(browsersArr[id].History, history)
}
return rst, nil
}
func (db *sqlitePersistence) DeleteBrowser(id string) error {
_, err := db.db.Exec("DELETE from browsers WHERE id = ?", id)
return err
}
func (db *sqlitePersistence) GetBookmarkByURL(url string) (*browsers.Bookmark, error) {
bookmark := browsers.Bookmark{}
err := db.db.QueryRow(`SELECT url, name, image_url, removed, clock, deleted_at FROM bookmarks WHERE url = ?`, url).Scan(&bookmark.URL, &bookmark.Name, &bookmark.ImageURL, &bookmark.Removed, &bookmark.Clock, &bookmark.DeletedAt)
if err != nil {
return nil, err
}
return &bookmark, nil
}
func (db *sqlitePersistence) UpdateBookmark(oldURL string, bookmark browsers.Bookmark) error {
tx, err := db.db.Begin()
if err != nil {
return err
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
_ = tx.Rollback()
}()
insert, err := tx.Prepare("UPDATE bookmarks SET url = ?, name = ?, image_url = ?, removed = ?, clock = ?, deleted_at = ? WHERE url = ?")
if err != nil {
return err
}
_, err = insert.Exec(bookmark.URL, bookmark.Name, bookmark.ImageURL, bookmark.Removed, bookmark.Clock, bookmark.DeletedAt, oldURL)
return err
}
func (db *sqlitePersistence) DeleteSoftRemovedBookmarks(threshold uint64) error {
tx, err := db.db.Begin()
if err != nil {
return err
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
_ = tx.Rollback()
}()
_, err = tx.Exec(`DELETE from bookmarks WHERE removed = 1 AND deleted_at < ?`, threshold)
return err
}
func (db *sqlitePersistence) InsertWalletConnectSession(session *WalletConnectSession) error {
tx, err := db.db.Begin()
if err != nil {
return err
}
defer func() {
if err == nil {
err = tx.Commit()
return
}
_ = tx.Rollback()
}()
sessionInsertPreparedStatement, err := tx.Prepare("INSERT OR REPLACE INTO wallet_connect_v1_sessions(peer_id, dapp_name, dapp_url, info) VALUES(?, ?, ?, ?)")
if err != nil {
return err
}
defer sessionInsertPreparedStatement.Close()
_, err = sessionInsertPreparedStatement.Exec(session.PeerID, session.DAppName, session.DAppURL, session.Info)
return err
}
func (db *sqlitePersistence) GetWalletConnectSession() ([]WalletConnectSession, error) {
var sessions []WalletConnectSession
rows, err := db.db.Query("SELECT peer_id, dapp_name, dapp_url, info FROM wallet_connect_v1_sessions ORDER BY dapp_name")
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
session := WalletConnectSession{}
err = rows.Scan(&session.PeerID, &session.DAppName, &session.DAppURL, &session.Info)
if err != nil {
return nil, err
}
sessions = append(sessions, session)
}
return sessions, nil
}
func (db *sqlitePersistence) DeleteWalletConnectSession(peerID string) error {
deleteStatement, err := db.db.Prepare("DELETE FROM wallet_connect_v1_sessions where peer_id=?")
if err != nil {
return err
}
defer deleteStatement.Close()
_, err = deleteStatement.Exec(peerID)
return err
}