Skip to content

BillionMail gives you open-source MailServer, NewsLetter, Email Marketing — fully self-hosted, dev-friendly, and free from monthly fees. Join the discord: https://discord.gg/asfXzBUhZr

10.6 KB
Raw Blame
package database_initialization

import (
	"context"
	"github.com/gogf/gf/v2/database/gdb"
	"github.com/gogf/gf/v2/errors/gerror"
	"github.com/gogf/gf/v2/frame/g"
	"strings"
	"time"
)

func init() {
	registerHandler(func() {

		ctx := context.Background()

		sqlList := []string{

			`-- Relay configuration 
			CREATE TABLE IF NOT EXISTS bm_relay_config (
			id BIGSERIAL PRIMARY KEY,
			remark varchar(255),  
			rtype varchar(30),  -- Relay type: gmail, sendgrid, custom, aws, mailgun, local
			relay_host varchar(255) NOT NULL, -- Relay server address 
			relay_port varchar(10) NOT NULL, -- Relay server port 
			auth_user varchar(255) NOT NULL, -- SMTP auth username
			auth_password varchar(255) NOT NULL, -- SMTP auth password (encrypted)
			ip varchar(255), -- IP for reminding user to update SPF record (optional) 
			host varchar(255), --  Hostname for reminding user to update SPF record (optional)
			helo_name varchar(255), -- HELO hostname 
			skip_tls_verify smallint DEFAULT 0, -- kip TLS verification: 1-skip, 0-do not skip 
			smtp_name varchar(50), --  SMTP server name
			active smallint NOT NULL DEFAULT 1, 
			auth_method varchar(20), -- Authentication method: LOGIN, PLAIN, CRAM-MD5, NONE
			create_time int NOT NULL default 0,
			update_time int NOT NULL default 0
			) `,

			`-- Relay to domain mapping
			CREATE TABLE IF NOT EXISTS bm_relay_domain_mapping (
			id BIGSERIAL PRIMARY KEY,
			relay_id BIGINT NOT NULL, --  Reference to id in bm_relay_config  
			sender_domain varchar(255) NOT NULL, -- Sender domain, e.g. "example.com"
			create_time int NOT NULL default 0
			) `,
			// 域名唯一约束
			`-- Domain to SMTP service name mapping table
			CREATE TABLE IF NOT EXISTS  bm_domain_smtp_transport(
			id BIGSERIAL PRIMARY KEY,
			atype varchar(50) NOT NULL DEFAULT 'relay', -- Transport type,  "relay"   "dedicated_ip"
			domain varchar(255) NOT NULL,  -- Sender domain, e.g. "@example.com"
			smtp_name varchar(255) NOT NULL, -- SMTP service name, e.g. "smtp_Relay_mail_xxxx_cn_gwt3_6_pvz_6"
			unique(domain)
			) `,

			`CREATE INDEX IF NOT EXISTS idx_domain_smtp_transport_domain ON bm_domain_smtp_transport(domain);`,
			`CREATE INDEX IF NOT EXISTS idx_domain_smtp_transport_atype ON bm_domain_smtp_transport(atype);`,
			`CREATE INDEX IF NOT EXISTS idx_domain_smtp_transport_domain_smtpName ON bm_domain_smtp_transport(domain,smtp_name);`,
			`CREATE INDEX IF NOT EXISTS idx_relay_domain_mapping_domain ON bm_relay_domain_mapping(sender_domain);`,
			`CREATE INDEX IF NOT EXISTS idx_relay_domain_mapping_relay_id ON bm_relay_domain_mapping(relay_id);`,
		}

		for _, sql := range sqlList {
			_, err := g.DB().Exec(ctx, sql)
			if err != nil {
				g.Log().Error(ctx, "Failed to create mail server tables:", err)
				return
			}
		}

		if err := cleanupAndIndexRelayDomainMapping(ctx); err != nil {
			g.Log().Error(ctx, "Failed to cleanup duplicates or create unique index:", err)
			return
		}

		// There is a rename table that has been synchronized. Skip.
		existsOld := checkTableExists(ctx, "bm_relay_old")
		if existsOld {
			return
		}

		//  Migrate the old data to the new structure
		if err := migrateRelayData(ctx); err != nil {
			g.Log().Error(ctx, "Failed to migrate relay data:", err)
			return
		}

		// Rename the old table (only after the migration is successful)
		if err := RenameRelayTable(ctx); err != nil {
			g.Log().Error(ctx, "Failed to rename old table bm_relay:", err)
			return
		}
		g.Log().Info(ctx, "Relay configuration migration completed successfully.")

	})
}

// cleanupAndIndexRelayDomainMapping
func cleanupAndIndexRelayDomainMapping(ctx context.Context) error {
	if !checkTableExists(ctx, "bm_relay_domain_mapping") {
		return gerror.New("Table bm_relay_domain_mapping does not exist")
	}

	var exists int
	err := g.DB().Model("pg_indexes").Fields("1").
		Where("indexname", "uk_relay_domain").
		Scan(&exists)
	if err != nil {
		return err
	}
	if exists == 1 {
		g.Log().Info(ctx, "Unique index uk_relay_domain already exists, skip cleanup.")
		return nil
	}

	_, err = g.DB().Exec(ctx, `
		DELETE FROM bm_relay_domain_mapping 
		WHERE id NOT IN (
			SELECT MIN(id)
			FROM bm_relay_domain_mapping
			GROUP BY relay_id, sender_domain
		)
	`)
	if err != nil {
		return gerror.Wrap(err, "Failed to remove duplicates from bm_relay_domain_mapping")
	}

	_, err = g.DB().Exec(ctx, `
		CREATE UNIQUE INDEX uk_relay_domain 
		ON bm_relay_domain_mapping(relay_id, sender_domain)
	`)
	if err != nil {
		return gerror.Wrap(err, "Failed to create unique index uk_relay_domain")
	}

	g.Log().Info(ctx, "Cleanup duplicates and created unique index on bm_relay_domain_mapping")
	return nil
}

func migrateRelayData(ctx context.Context) error {

	existsOld := checkTableExists(ctx, "bm_relay")
	if !existsOld {
		g.Log().Info(ctx, "Old table bm_relay does not exist, skip migration")
		return nil
	}

	existsNew := checkTableExists(ctx, "bm_relay_config")
	if !existsNew {
		return gerror.New("New table bm_relay_config does not exist, please create tables first")
	}

	newCount, err := g.DB().Model("bm_relay_config").Count()
	if err != nil {
		return gerror.Wrap(err, "Failed to query record count of new table")
	}
	if newCount > 0 {
		g.Log().Debugf(ctx, "bm_relay_config already has data (%d records), skip migration to prevent duplication", newCount)
		return nil
	}

	// 查询旧表数据
	var oldRelays []struct {
		Id            int64   `json:"id"`
		Remark        *string `json:"remark"`
		Rtype         *string `json:"rtype"`
		SenderDomain  *string `json:"sender_domain"`
		RelayHost     *string `json:"relay_host"`
		RelayPort     *string `json:"relay_port"`
		AuthUser      *string `json:"auth_user"`
		AuthPassword  *string `json:"auth_password"`
		Ip            *string `json:"ip"`
		Host          *string `json:"host"`
		HeloName      *string `json:"helo_name"`
		SkipTlsVerify *int    `json:"skip_tls_verify"`
		SmtpName      *string `json:"smtp_name"`
		Active        *int    `json:"active"`
		AuthMethod    *string `json:"auth_method"`
		CreateTime    int     `json:"create_time"`
		UpdateTime    int     `json:"update_time"`
	}

	err = g.DB().Model("bm_relay").
		Fields("id, remark, rtype, sender_domain, relay_host, relay_port, auth_user, auth_password, ip, host, helo_name, skip_tls_verify, smtp_name, active, auth_method, create_time, update_time").
		Scan(&oldRelays)
	if err != nil {
		return gerror.Wrap(err, "Failed to query old table bm_relay")
	}

	if len(oldRelays) == 0 {
		g.Log().Info(ctx, "No data in old table bm_relay, no migration needed")
		return nil
	}

	g.Log().Infof(ctx, "Start migrating %d relay configurations", len(oldRelays))

	tx, err := g.DB().Begin(ctx)
	if err != nil {
		return gerror.Wrap(err, "Failed to begin transaction")
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
			g.Log().Error(ctx, "Transaction rolled back during data migration")
		}
	}()

	for _, r := range oldRelays {

		configData := make(gdb.Map)

		if r.Remark != nil {
			configData["remark"] = *r.Remark
		}
		if r.Rtype != nil {
			configData["rtype"] = *r.Rtype
		}

		if r.RelayHost == nil || *r.RelayHost == "" {
			g.Log().Errorf(ctx, "Skip record ID=%d: relay_host is empty", r.Id)
			continue
		}
		configData["relay_host"] = *r.RelayHost

		if r.RelayPort == nil || *r.RelayPort == "" {
			configData["relay_port"] = "25"
		} else {
			configData["relay_port"] = *r.RelayPort
		}

		if r.AuthUser != nil {
			configData["auth_user"] = *r.AuthUser
		}
		if r.AuthPassword != nil {
			configData["auth_password"] = *r.AuthPassword
		}
		if r.Ip != nil {
			configData["ip"] = *r.Ip
		}
		if r.Host != nil {
			configData["host"] = *r.Host
		}
		if r.HeloName != nil {
			configData["helo_name"] = *r.HeloName
		}
		if r.SkipTlsVerify != nil {
			configData["skip_tls_verify"] = *r.SkipTlsVerify
		} else {
			configData["skip_tls_verify"] = 0
		}

		if r.SmtpName != nil && *r.SmtpName != "" {
			configData["smtp_name"] = *r.SmtpName
		} else {
			// Generate unique name
			configData["smtp_name"] = "MigratedRelay_" + time.Now().Format("20060102_150405") + "_" + strings.ReplaceAll(*r.RelayHost, ".", "_")
		}

		if r.Active != nil {
			configData["active"] = *r.Active
		} else {
			configData["active"] = 1
		}

		if r.AuthMethod != nil {
			configData["auth_method"] = *r.AuthMethod
		} else {
			configData["auth_method"] = "LOGIN"
		}

		configData["create_time"] = r.CreateTime
		configData["update_time"] = r.UpdateTime

		// Insert into bm_relay_config
		result, err := tx.Model("bm_relay_config").Data(configData).Insert()
		if err != nil {
			return gerror.Wrapf(err, "Failed to insert bm_relay_config (ID=%d)", r.Id)
		}

		relayConfigId, err := result.LastInsertId()
		if err != nil {
			return gerror.Wrapf(err, "Failed to get last insert ID (ID=%d)", r.Id)
		}

		// Insert domain mapping
		if r.SenderDomain != nil && *r.SenderDomain != "" {
			mappingData := gdb.Map{
				"relay_id":      relayConfigId,
				"sender_domain": *r.SenderDomain,
				"create_time":   r.CreateTime,
			}
			_, err = tx.Model("bm_relay_domain_mapping").Data(mappingData).Insert()
			if err != nil {
				return gerror.Wrapf(err, "Failed to insert bm_relay_domain_mapping (ID=%d)", r.Id)
			}
		}
	}

	if err = tx.Commit(); err != nil {
		return gerror.Wrap(err, "Failed to commit transaction after data migration")
	}

	g.Log().Infof(ctx, "Data migration succeeded! Migrated %d relay configurations", len(oldRelays))
	return nil
}

func checkTableExists(ctx context.Context, tableName string) bool {
	query := `SELECT EXISTS (
		SELECT FROM information_schema.tables 
		WHERE table_schema = 'public' 
		AND table_name = ?
	)`

	result, err := g.DB().GetOne(ctx, query, tableName)
	if err != nil {
		g.Log().Errorf(ctx, "Error checking if table %s exists: %v", tableName, err)
		return false
	}

	return result["exists"].Bool()
}

// RenameRelayTable Rename the old relay table
func RenameRelayTable(ctx context.Context) error {

	existsNew := checkTableExists(ctx, "bm_relay")
	if !existsNew {
		return gerror.New("Table bm_relay does not exist, no need to rename")
	}

	existsOld := checkTableExists(ctx, "bm_relay_old")
	if !existsOld {
		return gerror.New("Table bm_relay_old already exists, skipping rename operation")
	}

	tx, err := g.DB().Begin(ctx)
	if err != nil {
		return gerror.Wrap(err, "Failed to begin transaction for renaming table")
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
			g.Log().Error(ctx, "Transaction rolled back during table rename")
		}
	}()

	_, err = tx.Exec("ALTER TABLE bm_relay RENAME TO bm_relay_old")

	if err != nil {
		return gerror.Wrap(err, "Failed to rename bm_relay to bm_relay_old")
	}

	if err = tx.Commit(); err != nil {
		return gerror.Wrap(err, "Failed to commit transaction after renaming table")
	}

	g.Log().Info(ctx, "Successfully renamed table bm_relay to bm_relay_old")
	return nil
}