Using the Shared Database (DMP)

(!
    Example of using shared database.
    This is a variant of the Burglar example where the item values and weights are stored in the database.
    (c) 2019 Fair Isaac Corporation.
!)
model "burglar_with_solution_db"
  options keepassert
  uses "mminsight","mmodbc"

  ! **** Declare public data **** !
  !@insight.manage input
  public declarations
    ! Maximum weight alowed for haul
    !@insight.alias Max Weight
    WTMAX: integer

    ! Index range for items
    Items=1..8
  end-declarations

  !@insight.manage result
  public declarations
    !@insight.alias If Item Taken
    x: array(Items) of mpvar
  end-declarations

  ! *** Declare private data **** !
  declarations
    VALUE: array(Items) of real
    WEIGHT: array(Items) of real
  end-declarations

  ! *** Connect to solution database *** !
  SQLconnect("DSN=customdb")
  assert(getparam("SQLsuccess"))

  ! *** Loading input data *** !
  procedure datainput
    ! *** Create solution database *** !
    SQLexecute("CREATE TABLE IF NOT EXISTS Items (item INTEGER NOT NULL PRIMARY KEY, value DOUBLE NOT NULL, weight DOUBLE NOT NULL)")
    assert(getparam("SQLsuccess"))

    ! *** Populate solution database *** !
    ! Note: this deletes everything in the solution database - this is okay for the purpose of the example, probably not what you want to do
    ! in your actual model.
    SQLexecute("DELETE FROM Items")
    assert(getparam("SQLsuccess"))

    VALUE :: [15,100, 90, 60, 40, 15, 10,  1]
    WEIGHT:: [ 2, 20, 20, 30, 40, 30, 60, 10]
    SQLexecute("INSERT INTO Items (item,value,weight) VALUES (?,?,?)", [VALUE, WEIGHT])
    assert(getparam("SQLsuccess"))

    ! *** Populate the Insight data model *** !
    WTMAX := 102
  end-procedure

  case insightgetmode of
    INSIGHT_MODE_LOAD: do
      ! Prepare the input data then exit
      datainput
      exit(0)
    end-do
    INSIGHT_MODE_RUN: do
      ! Populate the model with input data from the Xpress Insight scenario, then solve the model
      insightpopulate
    end-do
    INSIGHT_MODE_NONE: do
      ! When the model is run in standalone Mosel (outside of Xpress Insight), load the input data then solve the model
      datainput
    end-do
  else
    writeln("Unknown execution mode")
    exit(1)
  end-case

  ! *** Load data from the shared database *** !
  SQLexecute("SELECT item, value, weight FROM items", [VALUE, WEIGHT])
  assert(getparam("SQLsuccess"))

  ! *** Construct the model *** !
  MaxVal:= sum(i in Items) VALUE(i)*x(i)  ! Objective: maximize total value

  ! Weight restriction
  WtMax:= sum(i in Items) WEIGHT(i)*x(i) <= WTMAX

 forall(i in Items) x(i) is_binary  ! All x are 0/1

  ! *** Solve the problem *** !
  insightmaximize(MaxVal)

  ! *** Print out the solution *** !
  writeln("Solution:\n Objective: ", getobjval)
  forall(i in Items) writeln(" x(", i, "): ", x(i).sol)

end-model