map_test.go

Documentation: github.com/letsencrypt/boulder/db

     1  package db
     2  
     3  import (
     4  	"context"
     5  	"database/sql"
     6  	"errors"
     7  	"fmt"
     8  	"testing"
     9  
    10  	"github.com/letsencrypt/borp"
    11  
    12  	"github.com/go-sql-driver/mysql"
    13  	"github.com/letsencrypt/boulder/core"
    14  	"github.com/letsencrypt/boulder/test"
    15  	"github.com/letsencrypt/boulder/test/vars"
    16  )
    17  
    18  func TestErrDatabaseOpError(t *testing.T) {
    19  	testErr := errors.New("computers are cancelled")
    20  	testCases := []struct {
    21  		name     string
    22  		err      error
    23  		expected string
    24  	}{
    25  		{
    26  			name: "error with table",
    27  			err: ErrDatabaseOp{
    28  				Op:    "test",
    29  				Table: "testTable",
    30  				Err:   testErr,
    31  			},
    32  			expected: fmt.Sprintf("failed to test testTable: %s", testErr),
    33  		},
    34  		{
    35  			name: "error with no table",
    36  			err: ErrDatabaseOp{
    37  				Op:  "test",
    38  				Err: testErr,
    39  			},
    40  			expected: fmt.Sprintf("failed to test: %s", testErr),
    41  		},
    42  	}
    43  
    44  	for _, tc := range testCases {
    45  		t.Run(tc.name, func(t *testing.T) {
    46  			test.AssertEquals(t, tc.err.Error(), tc.expected)
    47  		})
    48  	}
    49  }
    50  
    51  func TestIsNoRows(t *testing.T) {
    52  	testCases := []struct {
    53  		name           string
    54  		err            ErrDatabaseOp
    55  		expectedNoRows bool
    56  	}{
    57  		{
    58  			name: "underlying err is sql.ErrNoRows",
    59  			err: ErrDatabaseOp{
    60  				Op:    "test",
    61  				Table: "testTable",
    62  				Err:   fmt.Errorf("some wrapper around %w", sql.ErrNoRows),
    63  			},
    64  			expectedNoRows: true,
    65  		},
    66  		{
    67  			name: "underlying err is not sql.ErrNoRows",
    68  			err: ErrDatabaseOp{
    69  				Op:    "test",
    70  				Table: "testTable",
    71  				Err:   fmt.Errorf("some wrapper around %w", errors.New("lots of rows. too many rows.")),
    72  			},
    73  			expectedNoRows: false,
    74  		},
    75  	}
    76  
    77  	for _, tc := range testCases {
    78  		t.Run(tc.name, func(t *testing.T) {
    79  			test.AssertEquals(t, IsNoRows(tc.err), tc.expectedNoRows)
    80  		})
    81  	}
    82  }
    83  
    84  func TestIsDuplicate(t *testing.T) {
    85  	testCases := []struct {
    86  		name            string
    87  		err             ErrDatabaseOp
    88  		expectDuplicate bool
    89  	}{
    90  		{
    91  			name: "underlying err has duplicate prefix",
    92  			err: ErrDatabaseOp{
    93  				Op:    "test",
    94  				Table: "testTable",
    95  				Err:   fmt.Errorf("some wrapper around %w", &mysql.MySQLError{Number: 1062}),
    96  			},
    97  			expectDuplicate: true,
    98  		},
    99  		{
   100  			name: "underlying err doesn't have duplicate prefix",
   101  			err: ErrDatabaseOp{
   102  				Op:    "test",
   103  				Table: "testTable",
   104  				Err:   fmt.Errorf("some wrapper around %w", &mysql.MySQLError{Number: 1234}),
   105  			},
   106  			expectDuplicate: false,
   107  		},
   108  	}
   109  
   110  	for _, tc := range testCases {
   111  		t.Run(tc.name, func(t *testing.T) {
   112  			test.AssertEquals(t, IsDuplicate(tc.err), tc.expectDuplicate)
   113  		})
   114  	}
   115  }
   116  
   117  func TestTableFromQuery(t *testing.T) {
   118  	// A sample of example queries logged by the SA during Boulder
   119  	// unit/integration tests.
   120  	testCases := []struct {
   121  		query         string
   122  		expectedTable string
   123  	}{
   124  		{
   125  			query:         "SELECT id, jwk, jwk_sha256, contact, agreement, initialIP, createdAt, LockCol, status FROM registrations WHERE jwk_sha256 = ?",
   126  			expectedTable: "registrations",
   127  		},
   128  		{
   129  			query:         "\n\t\t\t\t\tSELECT orderID, registrationID\n\t\t\t\t\tFROM orderFqdnSets\n\t\t\t\t\tWHERE setHash = ?\n\t\t\t\t\tAND expires > ?\n\t\t\t\t\tORDER BY expires ASC\n\t\t\t\t\tLIMIT 1",
   130  			expectedTable: "orderFqdnSets",
   131  		},
   132  		{
   133  			query:         "SELECT id, identifierType, identifierValue, registrationID, status, expires, challenges, attempted, token, validationError, validationRecord FROM authz2 WHERE\n\t\t\tregistrationID = :regID AND\n\t\t\tstatus = :status AND\n\t\t\texpires > :validUntil AND\n\t\t\tidentifierType = :dnsType AND\n\t\t\tidentifierValue = :ident\n\t\t\tORDER BY expires ASC\n\t\t\tLIMIT 1 ",
   134  			expectedTable: "authz2",
   135  		},
   136  		{
   137  			query:         "insert into `registrations` (`id`,`jwk`,`jw      k_sha256`,`contact`,`agreement`,`initialIp`,`createdAt`,`LockCol`,`status`) values (null,?,?,?,?,?,?,?,?);",
   138  			expectedTable: "`registrations`",
   139  		},
   140  		{
   141  			query:         "update `registrations` set `jwk`=?, `jwk_sh      a256`=?, `contact`=?, `agreement`=?, `initialIp`=?, `createdAt`=?, `LockCol`      =?, `status`=? where `id`=? and `LockCol`=?;",
   142  			expectedTable: "`registrations`",
   143  		},
   144  		{
   145  			query:         "SELECT COUNT(*) FROM registrations WHERE initialIP = ? AND ? < createdAt AND createdAt <= ?",
   146  			expectedTable: "registrations",
   147  		},
   148  		{
   149  			query:         "SELECT COUNT(*) FROM orders WHERE registrationID = ? AND created >= ? AND created < ?",
   150  			expectedTable: "orders",
   151  		},
   152  		{
   153  			query:         " SELECT id, identifierType, identifierValue, registrationID, status, expires, challenges, attempted, token, validationError, validationRecord FROM authz2 WHERE registrationID = ? AND status IN (?,?) AND expires > ? AND identifierType = ? AND identifierValue IN (?)",
   154  			expectedTable: "authz2",
   155  		},
   156  		{
   157  			query:         "insert into `authz2` (`id`,`identifierType`,`identifierValue`,`registrationID`,`status`,`expires`,`challenges`,`attempted`,`token`,`validationError`,`validationRecord`) values (null,?,?,?,?,?,?,?,?,?,?);",
   158  			expectedTable: "`authz2`",
   159  		},
   160  		{
   161  			query:         "insert into `orders` (`ID`,`RegistrationID`,`Expires`,`Created`,`Error`,`CertificateSerial`,`BeganProcessing`) values (null,?,?,?,?,?,?)",
   162  			expectedTable: "`orders`",
   163  		},
   164  		{
   165  			query:         "insert into `orderToAuthz2` (`OrderID`,`AuthzID`) values (?,?);",
   166  			expectedTable: "`orderToAuthz2`",
   167  		},
   168  		{
   169  			query:         "insert into `requestedNames` (`ID`,`OrderID`,`ReversedName`) values (?,?,?);",
   170  			expectedTable: "`requestedNames`",
   171  		},
   172  		{
   173  			query:         "UPDATE authz2 SET status = :status, attempted = :attempted, validationRecord = :validationRecord, validationError = :validationError, expires = :expires WHERE id = :id AND status = :pending",
   174  			expectedTable: "authz2",
   175  		},
   176  		{
   177  			query:         "insert into `precertificates` (`ID`,`Serial`,`RegistrationID`,`DER`,`Issued`,`Expires`) values (null,?,?,?,?,?);",
   178  			expectedTable: "`precertificates`",
   179  		},
   180  		{
   181  			query:         "INSERT INTO certificateStatus (serial, status, ocspLastUpdated, revokedDate, revokedReason, lastExpirationNagSent, ocspResponse, notAfter, isExpired, issuerID) VALUES (?,?,?,?,?,?,?,?,?,?)",
   182  			expectedTable: "certificateStatus",
   183  		},
   184  		{
   185  			query:         "INSERT INTO issuedNames (reversedName, serial, notBefore, renewal) VALUES (?, ?, ?, ?);",
   186  			expectedTable: "issuedNames",
   187  		},
   188  		{
   189  			query:         "insert into `certificates` (`registrationID`,`serial`,`digest`,`der`,`issued`,`expires`) values (?,?,?,?,?,?);",
   190  			expectedTable: "`certificates`",
   191  		},
   192  		{
   193  			query:         "INSERT INTO certificatesPerName (eTLDPlusOne, time, count) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE count=count+1;",
   194  			expectedTable: "certificatesPerName",
   195  		},
   196  		{
   197  			query:         "insert into `fqdnSets` (`ID`,`SetHash`,`Serial`,`Issued`,`Expires`) values (null,?,?,?,?);",
   198  			expectedTable: "`fqdnSets`",
   199  		},
   200  		{
   201  			query:         "UPDATE orders SET certificateSerial = ? WHERE id = ? AND beganProcessing = true",
   202  			expectedTable: "orders",
   203  		},
   204  		{
   205  			query:         "DELETE FROM orderFqdnSets WHERE orderID = ?",
   206  			expectedTable: "orderFqdnSets",
   207  		},
   208  		{
   209  			query:         "insert into `serials` (`ID`,`Serial`,`RegistrationID`,`Created`,`Expires`) values (null,?,?,?,?);",
   210  			expectedTable: "`serials`",
   211  		},
   212  		{
   213  			query:         "UPDATE orders SET beganProcessing = ? WHERE id = ? AND beganProcessing = ?",
   214  			expectedTable: "orders",
   215  		},
   216  	}
   217  
   218  	for i, tc := range testCases {
   219  		t.Run(fmt.Sprintf("testCases.%d", i), func(t *testing.T) {
   220  			table := tableFromQuery(tc.query)
   221  			test.AssertEquals(t, table, tc.expectedTable)
   222  		})
   223  	}
   224  }
   225  
   226  func testDbMap(t *testing.T) *WrappedMap {
   227  	// NOTE(@cpu): We avoid using sa.NewDBMapFromConfig here because it would
   228  	// create a cyclic dependency. The `sa` package depends on `db` for
   229  	// `WithTransaction`. The `db` package can't depend on the `sa` for creating
   230  	// a DBMap. Since we only need a map for simple unit tests we can make our
   231  	// own dbMap by hand (how artisanal).
   232  	var config *mysql.Config
   233  	config, err := mysql.ParseDSN(vars.DBConnSA)
   234  	test.AssertNotError(t, err, "parsing DBConnSA DSN")
   235  
   236  	dbConn, err := sql.Open("mysql", config.FormatDSN())
   237  	test.AssertNotError(t, err, "opening DB connection")
   238  
   239  	dialect := borp.MySQLDialect{Engine: "InnoDB", Encoding: "UTF8"}
   240  	// NOTE(@cpu): We avoid giving a sa.BoulderTypeConverter to the DbMap field to
   241  	// avoid the cyclic dep. We don't need to convert any types in the db tests.
   242  	dbMap := &borp.DbMap{Db: dbConn, Dialect: dialect, TypeConverter: nil}
   243  	return &WrappedMap{dbMap: dbMap}
   244  }
   245  
   246  func TestWrappedMap(t *testing.T) {
   247  	mustDbErr := func(err error) ErrDatabaseOp {
   248  		t.Helper()
   249  		var dbOpErr ErrDatabaseOp
   250  		test.AssertErrorWraps(t, err, &dbOpErr)
   251  		return dbOpErr
   252  	}
   253  
   254  	ctx := context.Background()
   255  
   256  	testWrapper := func(dbMap Executor) {
   257  		reg := &core.Registration{}
   258  
   259  		// Test wrapped Get
   260  		_, err := dbMap.Get(ctx, reg)
   261  		test.AssertError(t, err, "expected err Getting Registration w/o type converter")
   262  		dbOpErr := mustDbErr(err)
   263  		test.AssertEquals(t, dbOpErr.Op, "get")
   264  		test.AssertEquals(t, dbOpErr.Table, "*core.Registration")
   265  		test.AssertError(t, dbOpErr.Err, "expected non-nil underlying err")
   266  
   267  		// Test wrapped Insert
   268  		err = dbMap.Insert(ctx, reg)
   269  		test.AssertError(t, err, "expected err Inserting Registration w/o type converter")
   270  		dbOpErr = mustDbErr(err)
   271  		test.AssertEquals(t, dbOpErr.Op, "insert")
   272  		test.AssertEquals(t, dbOpErr.Table, "*core.Registration")
   273  		test.AssertError(t, dbOpErr.Err, "expected non-nil underlying err")
   274  
   275  		// Test wrapped Update
   276  		_, err = dbMap.Update(ctx, reg)
   277  		test.AssertError(t, err, "expected err Updating Registration w/o type converter")
   278  		dbOpErr = mustDbErr(err)
   279  		test.AssertEquals(t, dbOpErr.Op, "update")
   280  		test.AssertEquals(t, dbOpErr.Table, "*core.Registration")
   281  		test.AssertError(t, dbOpErr.Err, "expected non-nil underlying err")
   282  
   283  		// Test wrapped Delete
   284  		_, err = dbMap.Delete(ctx, reg)
   285  		test.AssertError(t, err, "expected err Deleting Registration w/o type converter")
   286  		dbOpErr = mustDbErr(err)
   287  		test.AssertEquals(t, dbOpErr.Op, "delete")
   288  		test.AssertEquals(t, dbOpErr.Table, "*core.Registration")
   289  		test.AssertError(t, dbOpErr.Err, "expected non-nil underlying err")
   290  
   291  		// Test wrapped Select with a bogus query
   292  		_, err = dbMap.Select(ctx, reg, "blah")
   293  		test.AssertError(t, err, "expected err Selecting Registration w/o type converter")
   294  		dbOpErr = mustDbErr(err)
   295  		test.AssertEquals(t, dbOpErr.Op, "select")
   296  		test.AssertEquals(t, dbOpErr.Table, "*core.Registration (unknown table)")
   297  		test.AssertError(t, dbOpErr.Err, "expected non-nil underlying err")
   298  
   299  		// Test wrapped Select with a valid query
   300  		_, err = dbMap.Select(ctx, reg, "SELECT id, contact FROM registrationzzz WHERE id > 1;")
   301  		test.AssertError(t, err, "expected err Selecting Registration w/o type converter")
   302  		dbOpErr = mustDbErr(err)
   303  		test.AssertEquals(t, dbOpErr.Op, "select")
   304  		test.AssertEquals(t, dbOpErr.Table, "registrationzzz")
   305  		test.AssertError(t, dbOpErr.Err, "expected non-nil underlying err")
   306  
   307  		// Test wrapped SelectOne with a bogus query
   308  		err = dbMap.SelectOne(ctx, reg, "blah")
   309  		test.AssertError(t, err, "expected err SelectOne-ing Registration w/o type converter")
   310  		dbOpErr = mustDbErr(err)
   311  		test.AssertEquals(t, dbOpErr.Op, "select one")
   312  		test.AssertEquals(t, dbOpErr.Table, "*core.Registration (unknown table)")
   313  		test.AssertError(t, dbOpErr.Err, "expected non-nil underlying err")
   314  
   315  		// Test wrapped SelectOne with a valid query
   316  		err = dbMap.SelectOne(ctx, reg, "SELECT contact FROM doesNotExist WHERE id=1;")
   317  		test.AssertError(t, err, "expected err SelectOne-ing Registration w/o type converter")
   318  		dbOpErr = mustDbErr(err)
   319  		test.AssertEquals(t, dbOpErr.Op, "select one")
   320  		test.AssertEquals(t, dbOpErr.Table, "doesNotExist")
   321  		test.AssertError(t, dbOpErr.Err, "expected non-nil underlying err")
   322  
   323  		// Test wrapped Exec
   324  		_, err = dbMap.ExecContext(ctx, "INSERT INTO whatever (id) VALUES (?) WHERE id = ?", 10)
   325  		test.AssertError(t, err, "expected err Exec-ing bad query")
   326  		dbOpErr = mustDbErr(err)
   327  		test.AssertEquals(t, dbOpErr.Op, "exec")
   328  		test.AssertEquals(t, dbOpErr.Table, "whatever")
   329  		test.AssertError(t, dbOpErr.Err, "expected non-nil underlying err")
   330  	}
   331  
   332  	// Create a test wrapped map. It won't have a type converted registered.
   333  	dbMap := testDbMap(t)
   334  
   335  	// A top level WrappedMap should operate as expected with respect to wrapping
   336  	// database errors.
   337  	testWrapper(dbMap)
   338  
   339  	// Using Begin to start a transaction with the dbMap should return a
   340  	// transaction that continues to operate in the expected fashion.
   341  	tx, err := dbMap.BeginTx(ctx)
   342  	defer func() { _ = tx.Rollback() }()
   343  	test.AssertNotError(t, err, "unexpected error beginning transaction")
   344  	testWrapper(tx)
   345  }
   346
View as plain text