Redo Database.go to support the database on Azure

[rileycleavenger]

Description

Need to create our SQL database on Azure and update Database.go. I think I can update the openDatabase() function and have it running correctly according to the example HERE.

Definition of Done

Database is hosted online and not locally

[rileycleavenger]

Passing this issue on to @TylerMetz because he wants more experience with SQL databases

[rileycleavenger]

Completed:

• mealdealz-db was successfully created on Azure
  • created the "DevSchema" and the only current table is DevSchema.UserData
  • added IP access for my house in Texas and @samforstot 's house in Boca
  • created a MealDealz resource group on Azure - can be used for all app hosting
• Successfully made the following functions in Database.go
  • SQLOpenDatabase()
  • SQLCloseDatabase()
  • StoreUserDatabase(u User) - Use this as an example for new functions
• All changes push to this branch

Important Info

• must run

  $ go get -u github.com/microsoft/go-mssqldb

• The SQL queries used for Azure are different than the ones we've been using in Golang
• The Golang code used to interface with a database server is different than the code used to interface with a local .db file
• All tables must be under a schema
• "IF NOT EXISTS" isn't a viable query
• To access the SQL server from Golang locally you have to enable your IPv4 as an endpoint
• Using Azure Data Studio is the best way to test queries on the database on Mac
• The username, password, and server address are in the Discord server
• You need to sign up for Azure with your student account to have admin access for the server

Resources

• Example Golang and Azure SQL Queries
• My Chat Convo

To-do

• Add @TylerMetz and @steeleelliott03 's IPv4 addresses to allow control of the server
• All developers create an Azure account to add Admin access
• @TylerMetz will draft out all of the SQL datatables, optimizing them where needed

• @TylerMetz will redo all of the Database.go functions, while removing all need for the Database struct we defined

  • [x] - StorePublixDatabase(f []FoodItem)
  • [x] - ReadPublixDatabase() []FoodItem
  • [x] - ClearPublixDeals()
  • [ ] - StoreWalmartDatabase(f []FoodItem)
  • [ ] - ReadWalmartDatabase() []FoodItem
  • [ ] - ClearWalmartDeals()
  • [ ] - ReadUserDatabase(userName string) User
  • [ ] - StoreUserPantry(u User)
  • [ ] - InsertPantryItemPost (currUser User, f FoodItem)
  • [ ] - UpdatePantry(currUser User, f []FoodItem)
  • [ ] - GetUserPantry(userName string) Pantry
  • [ ] - StorePubixScrapedTime(t time.Time)
  • [ ] - ReadPublixScrapedTime() time.Time
  • [ ] - StoreWalmartScrapedTime(t time.Time)
  • [ ] - ReadWalmartScrapedTime() time.Time
  • [ ] - WriteJSONRecipes()
  • [ ] - DeleteJSONRecipes()
  • [ ] - ReadJSONRecipes() []Recipe
  • [ ] - WriteNewUserRecipe (currUser User, newRecipe Recipe)
  • [ ] - DeleteUserRecipe (recipeID string)
  • [ ] - ReadAllUserRecipes() []Recipe
  • [ ] - ReadCurrUserRecipes (currUser User) []Recipe
  • [ ] - FavoriteRecipe (currUser User, recipeID string)
  • [ ] - UnfavoriteRecipe (currUser User, recipeID string)
  • [ ] - ReadFavoriteRecipes (currUser User) []Recipe
  • [ ] - getRecipeByID(db *sql.DB, recipeID string) (*Recipe, error)
  • [ ] - getRecipeFromIdUserTable(db *sql.DB, recipeID string) (*Recipe, error)
  • [ ] - getRecipeFromIdJSONTable(db *sql.DB, recipeID string) (*Recipe, error)
  • [ ] - FindFavoriteRecipes(currUser User, routingRecipes []Recommendation) []Recommendation
  • [ ] - GetUserPassword(username string) string
  • [ ] - StoreCookie(username string, cookie string)
  • [ ] - ReadCookie(username string) string
  • [ ] - UserFromCookie(cookie string) User
  • [ ] - ReadList(currUser User) List
  • [ ] - WriteList(newItem FoodItem, currUser User)
  • [ ] - UpdateListItem(newItem FoodItem, currUser User)

[rileycleavenger]

I updated all of the structs in the Backend to match the new tables (as seen in PR #444), here they are:

type FoodItem struct {
    Name      string `json:"Name"`
    FoodType string `json:"FoodType"`
    SaleDetails string `json:"SaleDetails"`
    Quantity int `json:"Quantity"`
}

type GroceryStore struct {
    Name      string
    ZipCode   string
}

type List struct {
    ShoppingList []FoodItem
}

type Pantry struct {
    FoodInPantry []FoodItem
}

type Recipe struct {
    Instructions   string   `json:"instructions"`
    Ingredients    []string `json:"ingredients"`
    Title          string   `json:"title"`
    PictureLink    *string  `json:"pictureLink"`
    RecipeID        string   `json:"recipeID"`
    UserFavorite   bool     `json:"userFavorite"`
}

type Scraper struct {
    PublixDeals []FoodItem
    WalmartDeals []FoodItem
}

type User struct{
    FirstName string `json:"FirstName"`
    LastName string `json:"LastName"`
    Email string `json:"Email"`
        UserName string `json:"UserName"`
        Password string `json:"Password"`
    UserPantry Pantry 
    UserList List 
}

[rileycleavenger]

TO-DO:

• Update Pantry functions: changing this so that buttons will auto send POST req
• List functions: changing the list page to have components to update items
• Favoriting recipes: need to do these
• All recipe functions: need to change to dbo.recipes table