Bug: Composite Primary Keys Not Fully Recognized

[Shion1305]

Summary

In go-gorm/sqlite, composite primary keys (i.e., tables with more than one primary key) are not detected correctly. The library recognizes only the first key, failing to mark the other columns as primary keys. This affects table migration and causes incorrect schema interpretation.

GORM Playground Link Recreation with GitHub Actions

I was not able to effectively integrate the gorm playground, so I create my own repository and recreated by GitHub Actions.
Refer This Repository and this GitHub Actions Run

Description

This SQL below creates a table with multiple primary keys.

CREATE TABLE something
(
    column1 INT,
    column2 INT,
    column3 INT,
    PRIMARY KEY (column1, column2)
);

In gorm, we can use ColumnTypes method in Migrator interface to get primary keys in a table.
Calling ColumnTypes after running the SQL should tell us that column1 and column2 are primary keys, however, go-gorm/sqlite says only column1 is a primary key.
Refer this test case below.

func TestColumnTypes(t *testing.T) {
	tc := []struct {
		name              string
		query             string
		expectedIsPrimary map[string]bool
	}{
		{
			name:  "What should be expected",
			query: "CREATE TABLE something\n(\n    column1 INT,\n    column2 INT,\n    column3 INT,\n    PRIMARY KEY (column1, column2)\n);",
			expectedIsPrimary: map[string]bool{
				"column1": true,
				"column2": true,
				"column3": false,
			},
		},
	}
	for _, tt := range tc {
		t.Run(tt.name, func(t *testing.T) {
			dbName := fmt.Sprintf("db_%s.sqlite3", uuid.NewString())
			sqliteConn := sqlite.Open(dbName)
			db, err := gorm.Open(sqliteConn, &gorm.Config{})
			if err != nil {
				t.Errorf("Failed to open new db: %s", err)
			}
			db.Exec(tt.query)
			types, err := db.Migrator().ColumnTypes("something")
			if err != nil {
				t.Errorf("Failed to get column types: %s", err)
			}
			for _, ty := range types {
				pr, valid := ty.PrimaryKey()
				exp := tt.expectedIsPrimary[ty.Name()]
				if pr && valid && exp || !pr && !exp {
					continue
				}
				t.Errorf("Expected %s to be primary key: %t, got: isPrimaryKey: %t, isValid: %t",
					ty.Name(), exp, pr, valid)
			}
		})
	}
}

In this testcase, we call .ColumnTypes after creating a table with 2 primary keys. As column1 and column2 are primary keys, this testcase should pass, but this fails.

Problem: a flaw in getAllColumns() in ddlmod.go

parseDDL() in ddlmod.go loads DDL line by line, if a line start with PRIMARY KEY, it will be passed to getAllColumns() and primary key columns are extracted.

It tries to match the line with this regex: [(,][`|"|'|\t]?(\w+)[`|"|'|\t]?
This regex can process a line like PRIMARY KEY (column1,column2) , however, it fails to process a line like PRIMARY KEY (column1, column2), as indicated in this screenshot.

Contribution

I would like to contribute to fixing this issue. I'm planning to make pull request for this issue in a few days.

[Shion1305]

@jinzhu Thank you for reviewing the PR and merging it! I want to use the new version in my project, could you release a new version?