github.com/wfusion/gofusion@v1.1.14/common/infra/watermill/pubsub/sql/schema_adapter_mysql.go (about)

     1  package sql
     2  
     3  import (
     4  	"database/sql"
     5  	"fmt"
     6  	"strings"
     7  
     8  	"github.com/pkg/errors"
     9  
    10  	"github.com/wfusion/gofusion/common/infra/watermill/message"
    11  	"github.com/wfusion/gofusion/common/utils/serialize/json"
    12  )
    13  
    14  // DefaultMySQLSchema is a default implementation of SchemaAdapter based on MySQL.
    15  // If you need some customization, you can use composition to change schema and method of unmarshaling.
    16  //
    17  //	type MyMessagesSchema struct {
    18  //		DefaultMySQLSchema
    19  //	}
    20  //
    21  //	func (m MyMessagesSchema) SchemaInitializingQueries(topic string) []string {
    22  //		createMessagesTable := strings.Join([]string{
    23  //			"CREATE TABLE IF NOT EXISTS " + m.MessagesTable(topic) + " (",
    24  //			"`offset` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,",
    25  //			"`uuid` BINARY(16) NOT NULL,",
    26  //			"`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,",
    27  //			"`payload` JSON DEFAULT NULL,",
    28  //			"`metadata` JSON DEFAULT NULL",
    29  //			");",
    30  //		}, "\n")
    31  //
    32  //		return []string{createMessagesTable}
    33  //	}
    34  //
    35  //	func (m MyMessagesSchema) UnmarshalMessage(row *sql.Row) (offset int, msg *message.Message, err error) {
    36  //		// ...
    37  //
    38  // For debugging your custom schema, we recommend to inject logger with trace logging level
    39  // which will print all SQL queries.
    40  type DefaultMySQLSchema struct {
    41  	// GenerateMessagesTableName may be used to override how the messages table name is generated.
    42  	GenerateMessagesTableName func(topic string) string
    43  
    44  	// SubscribeBatchSize is the number of messages to be queried at once.
    45  	//
    46  	// Higher value, increases a chance of message re-delivery in case of crash or networking issues.
    47  	// 1 is the safest value, but it may have a negative impact on performance when consuming a lot of messages.
    48  	//
    49  	// Default value is 100.
    50  	SubscribeBatchSize int
    51  }
    52  
    53  func (s DefaultMySQLSchema) SchemaInitializingQueries(topic string) []string {
    54  	createMessagesTable := strings.Join([]string{
    55  		"CREATE TABLE IF NOT EXISTS " + s.MessagesTable(topic) + " (",
    56  		"`offset` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,",
    57  		"`uuid` VARCHAR(36) NOT NULL,",
    58  		"`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,",
    59  		"`payload` LONGBLOB DEFAULT NULL,",
    60  		"`metadata` JSON DEFAULT NULL",
    61  		");",
    62  	}, "\n")
    63  
    64  	return []string{createMessagesTable}
    65  }
    66  
    67  func (s DefaultMySQLSchema) InsertQuery(topic string, msgs message.Messages) (string, []any, error) {
    68  	insertQuery := fmt.Sprintf(
    69  		`INSERT INTO %s (uuid, payload, metadata) VALUES %s`,
    70  		s.MessagesTable(topic),
    71  		strings.TrimRight(strings.Repeat(`(?,?,?),`, len(msgs)), ","),
    72  	)
    73  
    74  	args, err := defaultInsertArgs(msgs)
    75  	if err != nil {
    76  		return "", nil, err
    77  	}
    78  
    79  	return insertQuery, args, nil
    80  }
    81  
    82  func (s DefaultMySQLSchema) batchSize() int {
    83  	if s.SubscribeBatchSize == 0 {
    84  		return 100
    85  	}
    86  
    87  	return s.SubscribeBatchSize
    88  }
    89  
    90  func (s DefaultMySQLSchema) SelectQuery(topic string, consumerGroup string,
    91  	offsetsAdapter OffsetsAdapter) (string, []any) {
    92  	nextOffsetQuery, nextOffsetArgs := offsetsAdapter.NextOffsetQuery(topic, consumerGroup)
    93  	selectQuery := `
    94  		SELECT offset, uuid, payload, metadata FROM ` + s.MessagesTable(topic) + `
    95  		WHERE 
    96  			offset > (` + nextOffsetQuery + `)
    97  		ORDER BY 
    98  			offset ASC
    99  		LIMIT ` + fmt.Sprintf("%d", s.batchSize())
   100  
   101  	return selectQuery, nextOffsetArgs
   102  }
   103  
   104  func (s DefaultMySQLSchema) DeleteQuery(topic string, offset int64) (string, []any) {
   105  	return `DELETE FROM ` + s.MessagesTable(topic) + ` WHERE offset = ?`, []any{offset}
   106  }
   107  
   108  func (s DefaultMySQLSchema) UnmarshalMessage(row Scanner) (Row, error) {
   109  	r := Row{}
   110  	err := row.Scan(&r.Offset, &r.UUID, &r.Payload, &r.Metadata)
   111  	if err != nil {
   112  		return Row{}, errors.Wrap(err, "could not scan message row")
   113  	}
   114  
   115  	msg := message.NewMessage(string(r.UUID), r.Payload)
   116  
   117  	if r.Metadata != nil {
   118  		err = json.Unmarshal(r.Metadata, &msg.Metadata)
   119  		if err != nil {
   120  			return Row{}, errors.Wrap(err, "could not unmarshal metadata as JSON")
   121  		}
   122  	}
   123  
   124  	r.Msg = msg
   125  
   126  	return r, nil
   127  }
   128  
   129  func (s DefaultMySQLSchema) MessagesTable(topic string) string {
   130  	if s.GenerateMessagesTableName != nil {
   131  		return s.GenerateMessagesTableName(topic)
   132  	}
   133  	return fmt.Sprintf("`watermill_%s`", topic)
   134  }
   135  
   136  func (s DefaultMySQLSchema) SubscribeIsolationLevel() sql.IsolationLevel {
   137  	// MySQL requires serializable isolation level for not losing messages.
   138  	return sql.LevelSerializable
   139  }