[Feature Request] Support table creation/alteration operations in resolver configuration

[boolw]

Description Currently, the table name extraction in dbresolver only matches SELECT, UPDATE, MERGE INTO, and INSERT operations to determine which database to use. However, in some scenarios, we also need to route DDL operations (CREATE TABLE, ALTER TABLE) to specific databases based on the resolver configuration.

Current Behavior The current regex pattern in utils.go only matches:

• SELECT FROM
• UPDATE
• MERGE INTO
• INSERT INTO

var fromTableRegexp = regexp.MustCompile("(?i)(?:FROM|UPDATE|MERGE INTO|INSERT [a-z ]*INTO) ['`\"]?([a-zA-Z0-9_]+)([ '`\",)]|$)")

These operations will be routed to the configured database based on the table name. However, DDL operations like CREATE TABLE and ALTER TABLE are not included in this pattern, which means they will always be executed on the primary database.

Expected Behavior The resolver should also support routing DDL operations to specific databases based on the configuration. This would be useful in scenarios such as:

• Creating tables in specific databases during initialization
• Performing schema migrations on specific databases
• Managing table structures in a sharded database setup Proposed Solution Extend the regex pattern to include DDL operations:

var fromTableRegexp = regexp.MustCompile("(?i)(?:FROM|UPDATE|MERGE INTO|INSERT [a-z ]*INTO|CREATE TABLE|ALTER TABLE) ['`\"]?([a-zA-Z0-9_]+)([ '`\",)]|$)")

Use Case Example

dbresolver.Register(dbresolver.Config{
    Replicas: []gorm.Dialector{replica1},
}, "users_*")

// This DDL should be executed on the configured database, not the primary
db.Exec("CREATE TABLE users_202401 (id int, name varchar(255))")

It is best to support configuration of getTableFromRawSQL function, which can provide more flexibility, or support a configuration callback function inside getResolver function

Additional Context This feature would be particularly useful in scenarios involving:

• Database sharding
• Table partitioning
• Multi-tenant applications
• Database migration tools Let me know if you need any clarification or have questions about this feature request.