Click here to Skip to main content
15,498,859 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a place in code where database query is performed.
Code is not mine and I may not make decisions regarding it's architecture.

Recently, we got a task to perform couple of additional queries.
My superiors decided to put the additional queries in the same piece of database code.

Now they want me to optimize somehow that part of the code.
All those ifs and switches "pierce their eyes".

We use sqlx[^] for database queries.

Below is example code, I believe it is self-explanatory, but do ask for clarifications if needed:
Go
/*
     I will use UI windows as an example.
     The point is that all window types have some common properties,
         and some properties specific to each window type.
*/

type ProgressBar struct {

    X                   int
    Y                   int
    Width               int
    Height              int

    LowestValue         int
    Highestvalue        int
    CurrentPosition     int
}

type TextBox struct {

    X                   int
    Y                   int
    Width               int
    Height              int

    TextLength          int
    TextContent         string

}

type Calendar struct {

    X                  int
    Y                  int
    Width              int
    Height             int

    SelectedDate       time.Time
}

var (
    // holds specific SQL query for each window type
    queries             map[int] string
    // each window type has it's own function that returns struct for storing query results
    scanStructs         map[int]func() interface{}

    // since Go has no enum type, we have to simulate them like below
    progressbar     =   1
    textbox         =   2
    calendar        =   3

)

func init(){

    queries                     =   make(map[int]string)
    scanStructs                 =   make(map[int]func() interface{})

    scanStructs[progressbar]    =   func() interface{} { return ProgressBar{} }
    scanStructs[textbox]        =   func() interface{} { return TextBox{} }
    scanStructs[calendar]       =   func() interface{} { return Calendar{} }

    queries[progressbar]        =   "SELECT * FROM progress_bars"
    queries[textbox]            =   "SELECT * FROM textboxes"
    queries[calendar]           =   "SELECT * FROM calendars"

}
 
// I am providing non-refactored version for comparison
func OriginalPerformSqlQuery(type int) ([]interface{}, error) {

    query := string

    switch type {
        case progressbar:
            query =     "SELECT * FROM progress_bars"
        case textbox:
            query =     "SELECT * FROM textboxes"
        case calendar:
            query =     "SELECT * FROM calendars"
        default:  
            return interface{}, 
                   fmt.Errorf("unknown window type")
    }

    rows, err := db.Queryx(query)
    if err != nil {
        return interface{}, 
               fmt.Errorf("db.Queryx() failed: %v", err)
    }

    defer func() { _ = rows.Close() }()

    result := make([]interface{}, 0)

    for rows.Next() {

        switch type {
            case progressbar:
                r     :=     ProgressBar{}

                if err := rows.StructScan(&r); err != nil {
                      return interface{}, 
                             fmt.Errorf("rows.StructScan() failed: %v", err)
                }

                result = append(result, r)

            case textbox:
                r     :=     TextBox{}

                if err := rows.StructScan(&r); err != nil {
                      return interface{}, 
                             fmt.Errorf("rows.StructScan() failed: %v", err)
                }

                result = append(result, r)

            case calendar:
                r     :=     Calendar{}

                if err := rows.StructScan(&r); err != nil {
                      return interface{}, 
                             fmt.Errorf("rows.StructScan() failed: %v", err)
                }

                result = append(result, r)
        }
    }

    return result, nil

}

func PerformSqlQuery(type int) ([]interface{}, error) {

    query, exists = queries[type]
    if !exists {
        return interface{}, 
               fmt.Errorf("unknown window type")
    }

    rows, err := db.Queryx(query)
    if err != nil {
        return interface{}, 
               fmt.Errorf("db.Queryx() failed: %v", err)
    }

    defer func() { _ = rows.Close() }()

    result := make([]interface{}, 0)

    for rows.Next() {
        // get required destination struct
        r := scanStructs[type]()

        if err := rows.StructScan(&r); err != nil {
            return interface{}, 
                   fmt.Errorf("rows.StructScan() failed: %v", err)
        }

        result = append(result, r)
    }

    return result, nil

}


What I have tried:

I have decided to make 2 maps with query type as key, and concrete struct/SQL, as data.
This reduced visual noise significantly, but I came here to ask if there is something else I could do?
Posted
Comments
Gerry Schmitz 22-Nov-21 10:49am    
Your "if err [...]" looks like a candidate for a routine; but I don't know enough about Go "generics" or function pointers, if any.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900