Recipe management software?

Fangbeast · Post by **Fangbeast** » Fri Apr 10, 2009 12:52 am

MealMaster files are text with some very specific formatting issues and UTF considerations. No sure I should be worrying about it much as there are so many differences between various versions of the programs and therefore the format of the text itself.

But would be nice to import a lot of my recipes into my own database. Even the people who did it under linux said they had a lot of difficulty with the changes.

I'll have a go at doing the earliest version first (very different to the current one) and see what I can do with it.

Fangbeast · Post by **Fangbeast** » Sat Feb 13, 2016 1:52 pm

Finally getting around to finishing my recipe manager. it looks either, isn't commercial quality but I don't care as it is for the family.

With that in mind, I asked InfraTec for help and he is helping me with anything import/xml related. Heck, he wrote all the importers himself except for one.

When all import routines work properly, I'll add them to the recipe program and release it.

In the meantime, here is the only importer that I was able to write myself, a BigOven CRB books to SQLite database (for my recipe book format) in case anyone has a use for the code.

Code: Select all

; Author: Fangbeast (MGB Technical Solutions) 2015
; 
; License: Do whatever you want with it except claim that it is yours. You may never, ever sell it.
; 
; Credits; Bernd Krüger-Knauber (InfraTec) for fixing a lot of my blob, import mistakes and just about ever other program that I had issues with.
; People underestimate his knowledge and help, he is a top bloke.
; 

Define EventID, MenuID, GadgetID, WindowID

Enumeration 1
  #Window_BigOvenToSqLite
EndEnumeration

#WindowIndex = #PB_Compiler_EnumerationValue

Enumeration 1
  #Gadget_BigOvenToSqLite_Recipeboxes
  #Gadget_BigOvenToSqLite_Numberofrecipeboxes
  #Gadget_BigOvenToSqLite_TItlelist
  #Gadget_BigOvenToSqLite_Numberoftitles
  #Gadget_BigOvenToSqLite_Numberofpictures
  #Gadget_BigOvenToSqLite_Numberofingredients
  #Gadget_BigOvenToSqLite_Convertpath
  #Gadget_BigOvenToSqLite_Databasename
  #Gadget_BigOvenToSqLite_Getdatabase
  #Gadget_BigOvenToSqLite_Getpath
  #Gadget_BigOvenToSqLite_Convert
  #Gadget_BigOvenToSqLite_Exitprogram
  #Gadget_BigOvenToSqLite_Errorlist
EndEnumeration

#GadgetIndex = #PB_Compiler_EnumerationValue

Procedure.i Window_BigOvenToSqLite()
  If OpenWindow(#Window_BigOvenToSqLite, 114, 68, 785, 715, "BigOven recipes to MGBRecipes", #PB_Window_SystemMenu|#PB_Window_ScreenCentered|#PB_Window_Invisible)
    ListIconGadget(#Gadget_BigOvenToSqLite_Recipeboxes, 5, 5, 160, 555, "Boxes", 156, #PB_ListIcon_FullRowSelect|#LVS_NOCOLUMNHEADER)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Recipeboxes, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Recipeboxes, LoadFont(#Gadget_BigOvenToSqLite_Recipeboxes, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Numberofrecipeboxes, 5, 565, 160, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Numberofrecipeboxes, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Numberofrecipeboxes, LoadFont(#Gadget_BigOvenToSqLite_Numberofrecipeboxes, "Comic Sans MS", 10, 0))
    ListIconGadget(#Gadget_BigOvenToSqLite_TItlelist, 170, 5, 610, 555, "Titles", 596, #PB_ListIcon_FullRowSelect|#PB_ListIcon_AlwaysShowSelection|#LVS_NOCOLUMNHEADER)
      SetGadgetColor(#Gadget_BigOvenToSqLite_TItlelist, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_TItlelist, LoadFont(#Gadget_BigOvenToSqLite_TItlelist, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Numberoftitles, 170, 565, 200, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Numberoftitles, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Numberoftitles, LoadFont(#Gadget_BigOvenToSqLite_Numberoftitles, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Numberofpictures, 375, 565, 200, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Numberofpictures, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Numberofpictures, LoadFont(#Gadget_BigOvenToSqLite_Numberofpictures, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Numberofingredients, 580, 565, 200, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Numberofingredients, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Numberofingredients, LoadFont(#Gadget_BigOvenToSqLite_Numberofingredients, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Convertpath, 5, 595, 775, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Convertpath, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Convertpath, LoadFont(#Gadget_BigOvenToSqLite_Convertpath, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Databasename, 5, 625, 775, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Databasename, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Databasename, LoadFont(#Gadget_BigOvenToSqLite_Databasename, "Comic Sans MS", 10, 0))
    ButtonGadget(#Gadget_BigOvenToSqLite_Getdatabase, 5, 655, 190, 25, "Open database")
      SetGadgetFont(#Gadget_BigOvenToSqLite_Getdatabase, LoadFont(#Gadget_BigOvenToSqLite_Getdatabase, "Comic Sans MS", 10, 0))
    ButtonGadget(#Gadget_BigOvenToSqLite_Getpath, 200, 655, 190, 25, "Get path")
      SetGadgetFont(#Gadget_BigOvenToSqLite_Getpath, LoadFont(#Gadget_BigOvenToSqLite_Getpath, "Comic Sans MS", 10, 0))
    ButtonGadget(#Gadget_BigOvenToSqLite_Convert, 395, 655, 190, 25, "Convert now")
      SetGadgetFont(#Gadget_BigOvenToSqLite_Convert, LoadFont(#Gadget_BigOvenToSqLite_Convert, "Comic Sans MS", 10, 0))
    ButtonGadget(#Gadget_BigOvenToSqLite_Exitprogram, 590, 655, 190, 25, "Exit program")
      SetGadgetFont(#Gadget_BigOvenToSqLite_Exitprogram, LoadFont(#Gadget_BigOvenToSqLite_Exitprogram, "Comic Sans MS", 10, 0))
    StringGadget(#Gadget_BigOvenToSqLite_Errorlist, 5, 685, 775, 25, "", #PB_String_ReadOnly)
      SetGadgetColor(#Gadget_BigOvenToSqLite_Errorlist, #PB_Gadget_BackColor, $DBDBDB)
      SetGadgetFont(#Gadget_BigOvenToSqLite_Errorlist, LoadFont(#Gadget_BigOvenToSqLite_Errorlist, "Comic Sans MS", 10, 0))
    HideWindow(#Window_BigOvenToSqLite, 0)
    ProcedureReturn WindowID(#Window_BigOvenToSqLite)
  EndIf
EndProcedure

; My generic includes

Declare.s CountRecords(QueryString.s)
Declare.s DatabaseLastInsertRowId()
Declare.s KillQuote(Instring.s)
Declare   LastLine(Gadget.i, LineNumber.i)
Declare.s MakeSureDirectoryPathExists(Directory.s)
Declare   OpenOtherDatabase()
Declare   OpenSystemDatabase()
Declare.s RepQuote(Instring.s)
Declare.s TextToXML(TextString.s)
Declare.s XMLToText(XMLString.s)

; Custom includes

Declare   ConvertBigOvenToSqLite(CurrentFile.s)
Declare   GetConvertPath()
Declare   SearchEngine(SearchDirectory.s)

; Need a unicode aware version of the API directory creator

Import "shell32.lib"                                                                                ; 
  SHCreateDirectory(*hwnd, pszPath.p-unicode)                                                        ; 
EndImport                                                                                           ; 

; 

UseJPEG2000ImageDecoder()                                                                           ; PureBasic native image decoder
UseJPEGImageDecoder()                                                                               ; PureBasic native image decoder
UsePNGImageDecoder()                                                                                ; PureBasic native image decoder
UseTGAImageDecoder()                                                                                ; PureBasic native image decoder
UseTIFFImageDecoder()                                                                               ; PureBasic native image decoder

; 

UseODBCDatabase()                                                                                   ; 
UseSQLiteDatabase()                                                                                 ; 

; 

#ODBC_ADD_DSN                       =   1 ; Add Data source
#ODBC_ADD_SYS_DSN                   =   4 ; Add SYSTEM Data source
#ODBC_CONFIG_DSN                    =   2 ; Configure (edit) Data source
#ODBC_REMOVE_DSN                    =   3 ; Remove Data source
#ODBC_REMOVE_SYS_DSN                =   6 ; Remove SYSTEM Data source

#SQL_SUCCESS                        =   0
#SQL_SUCCESS_WITH_INFO              =   1
#SQL_ERROR                          =  -1
#SQL_INVALID_HANDLE                 =  -2
#SQL_NO_DATA                        = 100
#SQL_MAX_MESSAGE_LENGTH             = 512
#SQL_NTS                            =  -3
#SQL_HANDLE_ENV                     =   1;?
#SQL_HANDLE_DBC                     =   2;?
#SQL_HANDLE_STMT                    =   3
#SQL_HANDLE_DESC                    =   4;?
#SQL_C_CHAR                         =   1

#ODBC_ERROR_GENERAL_ERR             =   1
#ODBC_ERROR_INVALID_BUFF_LEN        =   2
#ODBC_ERROR_INVALID_HWND            =   3
#ODBC_ERROR_INVALID_STR             =   4
#ODBC_ERROR_INVALID_REQUEST_TYPE    =   5
#ODBC_ERROR_COMPONENT_NOT_FOUND     =   6
#ODBC_ERROR_INVALID_NAME            =   7
#ODBC_ERROR_INVALID_KEYWORD_VALUE   =   8
#ODBC_ERROR_INVALID_DSN             =   9
#ODBC_ERROR_INVALID_INF             =  10
#ODBC_ERROR_REQUEST_FAILED          =  11
#ODBC_ERROR_INVALID_PATH            =  12
#ODBC_ERROR_LOAD_LIB_FAILED         =  13
#ODBC_ERROR_INVALID_PARAM_SEQUENCE  =  14
#ODBC_ERROR_INVALID_LOG_FILE        =  15
#ODBC_ERROR_USER_CANCELED           =  16
#ODBC_ERROR_USAGE_UPDATE_FAILED     =  17
#ODBC_ERROR_CREATE_DSN_FAILED       =  18
#ODBC_ERROR_WRITING_SYSINFO_FAILED  =  19
#ODBC_ERROR_REMOVE_DSN_FAILED       =  20
#ODBC_ERROR_OUT_OF_MEM              =  21
#ODBC_ERROR_OUTPUT_STRING_TRUNCATED =  22

; My personal constants - Program details etc

#Author                         = "Miklos G Bolvary"                                                ; Author's name
#CopyRight                      = "MGB Technical Services 2015 "                                    ; Copyright holder
#Basename                       = "OvenConvert "                                                    ; Base name for ini and dataabse
#Version                        = "v0.00 "                                                          ; Program version
#Program                        = #Basename + #Version                                              ; Copyright string
#Eol                            = Chr(13) + Chr(10)                                                 ; End of line marker
#Fish                           = "<°)))o><²³  "                                                    ; My favourite fish!!
#Traynote                       = #Basename + #Version                                              ; Copyright string

; My personal constants - Program constants that don't need to change

#AtTheEndOfTheList              = -1                                                                ; 

#NoPictureHandle                = 0                                                                 ; 
#NoFileHandle                   = 0                                                                 ; 
#NoFileFound                    = -1                                                                ; 
#NoXMLNodeHandle                = 0                                                                 ; 

#NoBlobFound                    = 0                                                                 ; 
#NoDataFound                    = 0                                                                 ; 
#NoBufferDataFound              = 0                                                                 ; 

#NothingEncoded                 = 0                                                                 ; 
#NothingDecoded                 = 0                                                                 ; 

#NothingSelected                = -1                                                                ; 
#NothingFound                   = 0                                                                 ; 

#NoItems                        = 0                                                                 ; 
#FirstItem                      = 0                                                                 ; 

#EmptyString                    = ""                                                                ; 

#DatabaseQueryFail              = 0                                                                 ; 
#DatabaseUpdateFail             = 0                                                                 ; 
#DatabaseOpenFail               = 0                                                                 ; 

#NotEndOfFile                   = 0                                                                 ; 

; 

Structure ProgramData
  ; Program flags
  QuitValue.i                                                                                       ; 
  ConvertPath.s                                                                                     ; 
  
  ; BigOven specific
  Numberofrecipeboxes.i                                                                             ; 
  Numberoftitles.i                                                                                  ; 
  Numberofpictures.i                                                                                ; 
  Numberofingredients.i                                                                             ; 
  
  ; Database specific
  DatabaseHandle.i                                                                                  ; Handle to the current database
  DatabaseDirectory.s                                                                               ; Directory for the database
  DatabaseName.s                                                                                    ; Current path and name of the system database
  
  ; Directory directives
  CurrentDirectory.s                                                                                ; The current program startup directory
  TemporaryDirectory.s                                                                              ; Temporary files directory
  
  CrbPassword.s                                                                                     ; 
  
  Configfile.s                                                                                      ; 
  LastDatabase.s                                                                                    ; 
  LastConvertPath.s                                                                                 ; 
EndStructure

; 

Global Program.ProgramData                                                                         ; 
Global NewList FoundDirs.s()                                                                      ; List of recursed import directories

; 

Program\CrbPassword         = "mealpoint123"                                                        ; 

; Get working directories variables and create the physical dirs that go with them

Program\CurrentDirectory    = GetCurrentDirectory()                                                 ; Get the current directory name

; Setup the various system directory names and paths

Program\DatabaseDirectory   = Program\CurrentDirectory  + "Database\"                               ; Database directory

; Setup the various system file names and where they go.

Program\DatabaseName        = Program\DatabaseDirectory   + ReplaceString(#Basename, " ", "_") + #Version  +  ".Sqlite" ; Database name and path

; Setup the program config text file

Program\Configfile          = Program\CurrentDirectory + "Configfile.ini"                           ; 

; Create the directories if you can

MakeSureDirectoryPathExists(Program\DatabaseDirectory)                                              ; 

; Count the number of records in an SQLite database

Procedure.s CountRecords(QueryString.s)
  ; 
  If DatabaseQuery(Program\DatabaseHandle, QueryString.s)
    While NextDatabaseRow(Program\DatabaseHandle)
      Records.s = GetDatabaseString(Program\DatabaseHandle, 0)
    Wend
    ProcedureReturn Records.s
  EndIf
  ; 
EndProcedure

; Get the ID of the last inserted record

Procedure.s DatabaseLastInsertRowId()
  ; 
  If DatabaseQuery(Program\DatabaseHandle, "SELECT last_insert_rowid()")
    While NextDatabaseRow(Program\DatabaseHandle)
      RecordId.s = GetDatabaseString(Program\DatabaseHandle, 0)
    Wend
    FinishDatabaseQuery(Program\DatabaseHandle)
  EndIf
  ; 
  ProcedureReturn RecordId.s
  ; 
EndProcedure

; Kill double quotes in strings for display purposes

Procedure.s KillQuote(Instring.s)
  ; 
  ProcedureReturn ReplaceString(Instring.s, "''", "'", 1, 1)
  ; 
EndProcedure

; Go to the last line of a ListIconGadget

Procedure LastLine(Gadget.i, LineNumber.i)
  ; Make sure the current line is visible
  SendMessage_(GadgetID(Gadget.i), #LVM_ENSUREVISIBLE, LineNumber.i, 0)
  ; 
EndProcedure

; Need a unicode aware version of the API directory creator

Procedure.s MakeSureDirectoryPathExists(Directory.s)
  ; 
  ErrorCode.i = SHCreateDirectory(#Null, Directory.s)
  ; 
  Select ErrorCode.i
    Case #ERROR_SUCCESS               : Message.s ="Okay"                                           ; ResultCode = 0
    Case #ERROR_BAD_PATHNAME          : Message.s ="Bad directory path"                             ; ResultCode = 161
    Case #ERROR_FILENAME_EXCED_RANGE  : Message.s ="Directory path too long"                        ; ResultCode = 206
    Case #ERROR_FILE_EXISTS           : Message.s ="Directory already exists"                       ; ResultCode = 80
    Case #ERROR_ALREADY_EXISTS        : Message.s ="Directory already exists"                       ; ResultCode = 183
;   Case #ERROR_CANCELLED             : Message.s ="The user canceled the operation."                ; ResultCode = ??. Not defined in compiler residents
  EndSelect
  ; 
  ProcedureReturn Message.s
  ; 
  ; Debug MakeSureDirectoryPathExists("c:\1\2\3\4\5\6")
  ; 
EndProcedure

; Try to open the system database and create missing tables

Procedure OpenOtherDatabase()
  ; 
  DatabaseName.s = OpenFileRequester("Open database", Program\DatabaseName, "SQLite (*.sqlite)|*.sqlite", 0)
  ; 
  If DatabaseName.s <> #EmptyString
    ; 
    If Program\DatabaseHandle
      CloseDatabase(Program\DatabaseHandle)
    EndIf
    ; 
    Program\DatabaseHandle = OpenDatabase(#PB_Any, Program\DatabaseName, #EmptyString, #EmptyString, #PB_Database_SQLite)
    ; 
    If Program\DatabaseHandle <> #DatabaseOpenFail
      ; 
      Program\LastDatabase = DatabaseName.s
      ; 
    Else
      SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "There was a problem attempting to open " + DatabaseName.s + ", could be corrupt or open by some other process.")
    EndIf 
    ; 
  Else
    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "User cancelled the database chooser, nothing to do.")
  EndIf
  ; 
EndProcedure

; Try to open the system database and create missing tables

Procedure OpenSystemDatabase()
  ; 
  FileHandle.i = OpenFile(#PB_Any, Program\DatabaseName)
  ; 
  If FileHandle.i
    ; 
    CloseFile(FileHandle.i)
    ; 
    Program\DatabaseHandle = OpenDatabase(#PB_Any, Program\DatabaseName, #EmptyString, #EmptyString, #PB_Database_SQLite)
    ; 
    If Program\DatabaseHandle <> #DatabaseOpenFail
      ; Turn on auto database vacuum
      If Not DatabaseUpdate(Program\DatabaseHandle, "PRAGMA auto_vacuum = on")                    ; 
        ;Debug "Could not setup auto database vacuum: " + DatabaseError()
      EndIf                                                                                       ; 
      ; Create the Recipes table
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS Recipes("                                    ; 
      DatabaseUpdate.s + "Recipetitle TEXT, "                                                     ; Title
      DatabaseUpdate.s + "Numberofservings TEXT, "                                                ; YieldText (YieldNumber not used)
      DatabaseUpdate.s + "Recipeauthor TEXT, "                                                    ; 
      DatabaseUpdate.s + "Categories TEXT, "                                                      ; PrimaryIngredient
      DatabaseUpdate.s + "Subcategories TEXT, "                                                   ; AllCategoriesText
      DatabaseUpdate.s + "Preparationtime TEXT, "                                                 ; 
      DatabaseUpdate.s + "Cookingtime TEXT, "                                                     ; 
      DatabaseUpdate.s + "Difficulty TEXT, "                                                      ; 
      DatabaseUpdate.s + "Recipeversion TEXT, "                                                   ; 
      DatabaseUpdate.s + "Recipesource TEXT, "                                                    ; Source
      DatabaseUpdate.s + "Copyright TEXT, "                                                       ; 
      DatabaseUpdate.s + "Cuisine TEXT, "                                                         ; Cuisine
      DatabaseUpdate.s + "Reciperating TEXT, "                                                    ; 
      DatabaseUpdate.s + "Importedfrom TEXT, "                                                    ; 
      DatabaseUpdate.s + "Authorcomments TEXT, "                                                  ; 
      DatabaseUpdate.s + "Instructions TEXT, "                                                    ; Instructions
      DatabaseUpdate.s + "Nutritionaldata TEXT, "                                                 ; 
      DatabaseUpdate.s + "Othercomments TEXT, "                                                   ; Notes
      DatabaseUpdate.s + "Deleted TEXT, "                                                         ; 
      DatabaseUpdate.s + "Updated TEXT, "                                                         ; 
      DatabaseUpdate.s + "Favourite TEXT, "                                                       ; 
      DatabaseUpdate.s + "Locked TEXT, "                                                          ; 
      DatabaseUpdate.s + "Recordid INTEGER PRIMARY KEY AUTOINCREMENT, "                           ; RecipeID
      DatabaseUpdate.s + "UNIQUE (Recipetitle, Instructions) ON CONFLICT FAIL)"                   ; 
      If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)                                 ; 
      Else                                                                                        ; 
        SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not create recipe table in database: " + DatabaseError()) ; 
      EndIf                                                                                       ; 
      ; Create the index table
      DatabaseUpdate.s = "CREATE INDEX IF NOT EXISTS RecipeIndex ON Recipes(Recipetitle)"         ; 
      If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)                                 ; 
      Else                                                                                        ; 
        SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not create index table in database: " + DatabaseError() ); 
      EndIf                                                                                       ; 
      ; Create the picture table
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS Pictures("                                   ; 
      DatabaseUpdate.s + "Pictureid INTEGER PRIMARY KEY AUTOINCREMENT, "                          ; PictureID
      DatabaseUpdate.s + "Picture BLOB, "                                                         ; 
      DatabaseUpdate.s + "Description TEXT, "                                                     ; 
      DatabaseUpdate.s + "Filename TEXT, "                                                        ; 
      DatabaseUpdate.s + "Recordid INTEGER, "                                                     ; 
      DatabaseUpdate.s + "FOREIGN KEY(Recordid) REFERENCES Recipes(Recordid) ON DELETE CASCADE)"  ; 
      If Not DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)                             ; 
        SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, DatabaseUpdate.s  + "  --  "  + DatabaseError()) ; 
      EndIf                                                                                       ; 
      ; Create the ingredients table
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS Ingredients("                                ; 
      DatabaseUpdate.s + "Ingredientid INTEGER PRIMARY KEY AUTOINCREMENT, "                       ; IngredientLinesID
      DatabaseUpdate.s + "Unit TEXT, "                                                            ; TextQty
      DatabaseUpdate.s + "Measure TEXT, "                                                         ; Measure
      DatabaseUpdate.s + "Ingredient TEXT, "                                                      ; Name
      DatabaseUpdate.s + "Preparation TEXT, "                                                     ; PrepNotes
      DatabaseUpdate.s + "Lineorder TEXT, "                                                       ; LineOrder
      DatabaseUpdate.s + "Recordid INTEGER, "                                                     ; RecipeID
      DatabaseUpdate.s + "FOREIGN KEY(Recordid) REFERENCES Recipes(Recordid) ON DELETE CASCADE)"  ; 
      If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)                                 ; 
      Else                                                                                        ; 
        SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not create ingredients table: "  + DatabaseError()) ; 
      EndIf                                                                                       ; 
      ; 
    Else
      SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "There was a serious problem attempting to connect to " + #BaseName + #Version + " system database. Could be corrupt or open by some other process.")
    EndIf 
    ; 
  Else
    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not open or create raw database file.")
  EndIf
  ; 
EndProcedure

; Berikco's routine to properly replace single quotes with double for SQL passing

Procedure.s RepQuote(Instring.s)
  ;
  Protected.s TemporaryString.s
  ;
  For i = 1 To Len(Instring.s)
    If Mid(Instring.s, i, 1) = "'"
      TemporaryString.s = TemporaryString.s + "''"
    Else
      TemporaryString.s = TemporaryString.s + Mid(Instring.s, i, 1)
    EndIf
  Next i
  ;
  ProcedureReturn TemporaryString.s
  ; 
EndProcedure

; Replace bad characters with XML compatible ones. Thorsten Hoeppner

Procedure.s TextToXML(TextString.s)
  ; 
  XMLString.s = ReplaceString(TextString.s,     "&", "&")
  XMLString.s = ReplaceString(XMLString.s,       "<", "<")
  XMLString.s = ReplaceString(XMLString.s,       ">", ">")
  XMLString.s = ReplaceString(XMLString.s,     "'", "&apos;")
  XMLString.s = ReplaceString(XMLString.s,      "€", "&#128")
  XMLString.s = ReplaceString(XMLString.s, #DQUOTE$, """)
  XMLString.s = RemoveString(XMLString.s,  #CR$)
  XMLString.s = ReplaceString(XMLString.s, #LF$, Chr(182))
  ; 
  ProcedureReturn XMLString.s
  ; 
EndProcedure

; Strip illegal characters from text strings

Procedure.s XMLToText(XMLString.s)
  ;
  TextString.s = RemoveString(XMLString.s, #CR$)
  ;
  If Left(TextString.s, 1) = #LF$
    TextString.s = Mid(TextString.s, 2)
  EndIf
  ;
  TextString.s = ReplaceString(TextString.s, "&",          "&")
  TextString.s = ReplaceString(TextString.s, "<",           "<")
  TextString.s = ReplaceString(TextString.s, ">",           ">")
  TextString.s = ReplaceString(TextString.s, "&apos;",         "'")
  TextString.s = ReplaceString(TextString.s, """,    #DQUOTE$)
  TextString.s = ReplaceString(TextString.s, "&#128",          "€")
  TextString.s = ReplaceString(TextString.s, "'",          "'")
  TextString.s = ReplaceString(TextString.s, #LF$,    #EmptyString)
  ;TextString.s = ReplaceString(TextString.s, Chr(182), #LF$)
  TextString.s = ReplaceString(TextString.s, #CR$,    #EmptyString)
  ;
  ProcedureReturn Trim(TextString.s)
  ;
EndProcedure

; Universal, recursive search engine used by many functions

Procedure ConvertBigOvenToSqLite(CurrentFile.s)
  ; Try to configure the MDB data source via ODBC
  If SQLConfigDataSource_(0, #ODBC_ADD_DSN, "Microsoft Access Driver (*.mdb)","Server=127.0.0.1; Description=MyDescription ;DSN=ConnectMDB;DBQ=" + CurrentFile.s + ";UID=;PWD=" + Program\CrbPassword + ";")
    ; Now try to open the current MDB database
    MDBDatabaseHandle.i = OpenDatabase(#PB_Any, "ConnectMDB", #EmptyString, #EmptyString, #PB_Database_ODBC)
    ; Check to see if the database failed to open
    If MDBDatabaseHandle.i  <> #DatabaseOpenFail
      ; Create a new list to hold the recipe ID's in
      NewList RecipeId.s()
      ; Create the ID retrieval query
      DatabaseQuery.s = "SELECT RecipeID FROM Recipes"
      ; Get all the current recipe ID's from the current MDB file
      If DatabaseQuery(MDBDatabaseHandle.i, DatabaseQuery.s) <> #DatabaseQueryFail
        While NextDatabaseRow(MDBDatabaseHandle.i)
          AddElement(RecipeId.s())
          RecipeId.s()  = GetDatabaseString(MDBDatabaseHandle.i, 0)
        Wend
        FinishDatabaseQuery(MDBDatabaseHandle.i)
      Else 
        SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "The database query failed: " + DatabaseError())
      EndIf
      ; Process all the current recipe ID's from the current MDB file
      If ListSize(RecipeId.s())
        ; 
        DatabaseUpdate(Program\DatabaseHandle, "Begin Transaction")
        ; Iterate each RecipeID from the list and process the wanted recipe elements
        ForEach RecipeId.s()
          DatabaseQuery.s = "SELECT Title, YieldText, Instructions, Source, "
          DatabaseQuery.s + "YieldNumber, PrimaryIngredient, Cuisine, "
          DatabaseQuery.s + "PictureFile1, Notes, AllCategoriesText "
          DatabaseQuery.s + "FROM Recipes WHERE RecipeID = " + RecipeId.s()  + ""
          If DatabaseQuery(MDBDatabaseHandle.i, DatabaseQuery.s) <> #DatabaseQueryFail
            While NextDatabaseRow(MDBDatabaseHandle.i) <> 0
              Title.s             = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 0))
              YieldText.s         = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 1))
              Instructions.s      = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 2))
              Source.s            = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 3))
              YieldNumber.s       = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 4))
              PrimaryIngredient.s = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 5))
              Cuisine.s           = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 6))
              PictureFile1.s      = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 7))
              Notes.s             = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 8))
              AllCategoriesText.s = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 9))
              AllCategoriesText.s = ReplaceString(AllCategoriesText.s, "|", ",", #PB_String_NoCase, 1)
              AllCategoriesText.s = Trim(AllCategoriesText.s, ",")
              Updated.s           = FormatDate("%yyyy-%mm-%dd", Date()) ; Standard ISO date format
              ; Save the recipe item into the SQLite database
              DatabaseUpdate.s  = "INSERT INTO Recipes("
              DatabaseUpdate.s  + "Recipetitle, Numberofservings, Recipeauthor, Categories, "
              DatabaseUpdate.s  + "Subcategories, Preparationtime, Cookingtime, Difficulty, "
              DatabaseUpdate.s  + "Recipeversion, Recipesource, Copyright, Cuisine, "
              DatabaseUpdate.s  + "Reciperating, Importedfrom, Authorcomments, Instructions, "
              DatabaseUpdate.s  + "Nutritionaldata, Othercomments, Deleted, Updated, "
              DatabaseUpdate.s  + "Favourite, Locked) "
              DatabaseUpdate.s  + "VALUES("
              DatabaseUpdate.s  + "'"  + RepQuote(Title.s)              + "', " ; Recipetitle
              DatabaseUpdate.s  + "'"  + RepQuote(YieldText.s)          + "', " ; Numberofservings
              DatabaseUpdate.s  + "'"  +                                  "', " ; Recipeauthor
              DatabaseUpdate.s  + "'"  + RepQuote(PrimaryIngredient.s)  + "', " ; Categories
              DatabaseUpdate.s  + "'"  + RepQuote(AllCategoriesText.s)  + "', " ; SubCategoriesText
              DatabaseUpdate.s  + "'"  +                                  "', " ; Preparationtime
              DatabaseUpdate.s  + "'"  +                                  "', " ; Cookingtime
              DatabaseUpdate.s  + "'"  +                                  "', " ; Difficulty
              DatabaseUpdate.s  + "'"  +                                  "', " ; Recipeversion
              DatabaseUpdate.s  + "'"  + RepQuote(Source.s)             + "', " ; Recipesource
              DatabaseUpdate.s  + "'"  + RepQuote(Copyright.s)          + "', " ; Copyright
              DatabaseUpdate.s  + "'"  + RepQuote(Cuisine.s)            + "', " ; Cuisine
              DatabaseUpdate.s  + "'"  +                                  "', " ; Reciperating
              DatabaseUpdate.s  + "'"  +          "BigOven"             + "', " ; Importedfrom
              DatabaseUpdate.s  + "'"  +                                  "', " ; Authorcomments
              DatabaseUpdate.s  + "'"  + RepQuote(Instructions.s)       + "', " ; Instructions
              DatabaseUpdate.s  + "'"  +                                  "', " ; Nutritionaldata
              DatabaseUpdate.s  + "'"  + RepQuote(Notes.s)              + "', " ; Othercomments
              DatabaseUpdate.s  + "'"  + "0"                            + "', " ; Deleted
              DatabaseUpdate.s  + "'"  +          Updated.s             + "', " ; Updated
              DatabaseUpdate.s  + "'"  + "0"                            + "', " ; Favourite
              DatabaseUpdate.s  + "'"  + "0"                            + "')"  ; Locked
              If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s) <> #DatabaseUpdateFail
                LastRecordNumberInserted.s = DatabaseLastInsertRowId()
                If LastRecordNumberInserted.s
                  Program\Numberoftitles  + 1
                  AddGadgetItem(#Gadget_BigOvenToSqLite_TItlelist, #AtTheEndOfTheList, Title.s)
                  LastLine(#Gadget_BigOvenToSqLite_TItlelist, Program\Numberoftitles - 1)
                  SetGadgetText(#Gadget_BigOvenToSqLite_Numberoftitles, "Recipes found: "  + Str(Program\Numberoftitles))                  
                  While WindowEvent() : Wend
                  ; Process the picture into its own table
                  If PictureFile1.s <> #EmptyString
                    Program\Numberofpictures  + 1
                    SetGadgetText(#Gadget_BigOvenToSqLite_Numberofpictures, "Pictures found: "  + Str(Program\Numberofpictures))                  
                    PictureName.s = GetPathPart(CurrentFile.s)  + "Pictures\" + PictureFile1.s
                    PictureFileIn.i = ReadFile(#PB_Any, PictureName.s)
                    If PictureFileIn.i
                      FileInSize.i = Lof(PictureFileIn.i)
                      *Buffer = AllocateMemory(FileInSize.i)
                      If *Buffer
                        If ReadData(PictureFileIn.i, *Buffer, FileInSize.i) = FileInSize.i
                          SetDatabaseBlob(Program\DatabaseHandle, 0, *Buffer, FileInSize.i)
                          DatabaseUpdate.s  = "INSERT INTO Pictures("                 ; 
                         ;DatabaseUpdate.s + "Pictureid, "                            ; Pictureid
                          DatabaseUpdate.s + "Picture, "                              ; Picture
                          DatabaseUpdate.s + "Description, "                          ; Description
                          DatabaseUpdate.s + "Filename, "                             ; Filename
                          DatabaseUpdate.s + "Recordid) "                             ; Recordid
                          DatabaseUpdate.s + "VALUES("                                ; 
                         ;DatabaseUpdate.s + "Pictureid, "                            ; Pictureid
                          DatabaseUpdate.s + "?, "                                    ; Picture
                          DatabaseUpdate.s + "'', "                                   ; Description
                          DatabaseUpdate.s + "'" + RepQuote(PictureFile1.s)  + "', "  ; Filename
                          DatabaseUpdate.s + "'" + LastRecordNumberInserted.s + "')"  ; Recordid
                          If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s) <> #DatabaseUpdateFail
                            NewPictureRecord.s = DatabaseLastInsertRowId()
                            If NewPictureRecord.s <> #EmptyString
                              ; Tell the user something here
                            Else
                              SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not get a new record number for picture data")
                            EndIf
                          Else
                            SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not save the picture data into the database")
                          EndIf
                        Else
                          SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Could not read the picture data into the buffer")
                        EndIf
                        FreeMemory(*Buffer)
                      Else
                        SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "Could not allocate a buffer for the picture data")
                      EndIf
                      CloseFile(PictureFileIn.i)
                    Else
                      SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "Could not open the designated file to read from")
                    EndIf
                  Else
                    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "No picture for this record")
                  EndIf
                Else
                  SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "Failed to get a new database record ID")
                EndIf
              Else
                SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "The database update failed: "  + DatabaseError())
              EndIf
              ; 
            Wend
            FinishDatabaseQuery(MDBDatabaseHandle.i)
            ; Process the ingredients for this recipe
            If LastRecordNumberInserted.s
              DatabaseQuery.s = "SELECT TextQty, Measure, Name, PrepNotes, LineOrder "
              DatabaseQuery.s + "FROM IngredientLines "
              DatabaseQuery.s + "WHERE RecipeID = "    + RecipeId.s()  + ""
              If DatabaseQuery(MDBDatabaseHandle.i, DatabaseQuery.s) <> #DatabaseQueryFail
                While NextDatabaseRow(MDBDatabaseHandle.i)
                  Program\Numberofingredients  + 1
                  SetGadgetText(#Gadget_BigOvenToSqLite_Numberofingredients, "Ingredients found: "  + Str(Program\Numberofingredients))                  
                  TextQty.s   = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 0))
                  Measure.s   = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 1))
                  Name.s      = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 2))
                  PrepNotes.s = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 3))
                  LineOrder.s = KillQuote(GetDatabaseString(MDBDatabaseHandle.i, 4))
                  ; 
                  DatabaseUpdate.s = "INSERT INTO Ingredients("                       ; 
                 ;DatabaseUpdate.s + "Ingredientid, "                                 ; IngredientLinesID
                  DatabaseUpdate.s + "Unit, "                                         ; TextQty
                  DatabaseUpdate.s + "Measure, "                                      ; Measure
                  DatabaseUpdate.s + "Ingredient, "                                   ; Name
                  DatabaseUpdate.s + "Preparation, "                                  ; PrepNotes
                  DatabaseUpdate.s + "Lineorder, "                                    ; LineOrder
                  DatabaseUpdate.s + "Recordid) "                                     ; RecipeID
                  DatabaseUpdate.s + "VALUES("                                        ; 
                  DatabaseUpdate.s + "'" + RepQuote(TextQty.s)        + "', "         ; 
                  DatabaseUpdate.s + "'" + RepQuote(Measure.s)        + "', "         ; 
                  DatabaseUpdate.s + "'" + RepQuote(Name.s)           + "', "         ; 
                  DatabaseUpdate.s + "'" + RepQuote(PrepNotes.s)      + "', "         ; 
                  DatabaseUpdate.s + "'" + RepQuote(LineOrder.s)      + "', "         ; 
                  DatabaseUpdate.s + "'" + LastRecordNumberInserted.s + "')"          ; 
                  If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s) <> #DatabaseUpdateFail
                  Else
                    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "Could not insert the current ingredient into the database")
                  EndIf
                  ; 
                Wend
                FinishDatabaseQuery(MDBDatabaseHandle.i)
              EndIf
            Else
              SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "No last record number created")
            EndIf
            ; 
          Else 
            SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "The database query failed: " + DatabaseError())
          EndIf
          ; 
        Next ;RecipeID()  
        ; 
        DatabaseUpdate(Program\DatabaseHandle, "Commit")
        ; 
      Else
        ; The list is empty, nothing to process
      EndIf
      ; 
      CloseDatabase(MDBDatabaseHandle.i)
      ; 
    Else
      SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "No MDB database handle: " + DatabaseError())
    EndIf
    ; 
    SQLConfigDataSource_(0, #ODBC_REMOVE_DSN, "Microsoft Access Driver (*.mdb)","Server=127.0.0.1; Description=MyDescription ;DSN=ConnectMDB;DBQ=" + CurrentFile.s + ";UID=;PWD=" + Program\CrbPassword + ";")
    ; 
  Else
    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "Could not configure MDB data source: " + DatabaseError())
  EndIf
  ; 
EndProcedure

; 

Procedure GetConvertPath()
  ; 
  ConvertPath.s = PathRequester("CRB convert path", Program\CurrentDirectory)
  ; 
  If ConvertPath.s <> #EmptyString
    SetGadgetText(#Gadget_BigOvenToSqLite_Convertpath, ConvertPath.s)
    Program\LastConvertPath = ConvertPath.s
  Else
    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "[ No BigOven CRB path set yet? ]")
  EndIf
  ; 
EndProcedure

; Universal, recursive search engine used by many functions

Procedure SearchEngine(SearchDirectory.s)
  ; 
  If SearchDirectory.s  <> "[ No BigOven CRB path set yet? ]"
    ; 
    ClearGadgetItems(#Gadget_BigOvenToSqLite_Recipeboxes)
    Program\Numberofrecipeboxes = 0
    SetGadgetText(#Gadget_BigOvenToSqLite_Numberofrecipeboxes, "Boxes found: "  + Str(Program\Numberofrecipeboxes))
    ; 
    ClearGadgetItems(#Gadget_BigOvenToSqLite_TItlelist)
    Program\Numberoftitles = 0
    SetGadgetText(#Gadget_BigOvenToSqLite_Numberoftitles, "Recipes found: "  + Str(Program\Numberofrecipeboxes))
    ; 
    NewList FoundDirectories.s()
    ; 
    If SearchDirectory.s <> #EmptyString
      If Right(SearchDirectory.s, 1) = "\"
        SearchDirectory.s = Left(SearchDirectory.s, Len(SearchDirectory.s) - 1)
      EndIf
      AddElement(FoundDirectories.s())
      FoundDirectories.s() = SearchDirectory.s
      IndexLevel.i = 0
      Repeat
        SelectElement(FoundDirectories.s(), IndexLevel.i)
        If ExamineDirectory(0, FoundDirectories.s(), "*.*")
          Path.s = FoundDirectories.s() + "\"
          While NextDirectoryEntry(0)
            Filename.s = DirectoryEntryName(0)
            Select DirectoryEntryType(0)
              Case 1
                ; 
                If LCase(GetExtensionPart(Path.s + Filename.s)) = "crb"
                  Program\Numberofrecipeboxes + 1
                  AddGadgetItem(#Gadget_BigOvenToSqLite_Recipeboxes, #AtTheEndOfTheList,  Filename.s)
                  SetGadgetText(#Gadget_BigOvenToSqLite_Numberofrecipeboxes, "Boxes found: "  + Str(Program\Numberofrecipeboxes))
                  While WindowEvent() : Wend
                  ConvertBigOvenToSqLite(Path.s + Filename.s)
                EndIf
                ; 
              Case 2
                Filename.s = DirectoryEntryName(0)
                If Filename.s <> ".." And Filename.s <> "."
                  AddElement(FoundDirectories())
                  FoundDirectories() = Path + Filename.s
                EndIf
            EndSelect
          Wend
        EndIf
        IndexLevel.i + 1
      Until IndexLevel.i > ListSize(FoundDirectories()) -1
    EndIf
    ; 
  EndIf
  ; 
EndProcedure

; Main program body

If Window_BigOvenToSqLite()
  ; 
  If FileSize(Program\Configfile) <> #NoFileFound                           ; Config file was found
    ConfigFile.i = ReadFile(#PB_Any, Program\Configfile)
    If ConfigFile.i <> #NoFileHandle
      Program\LastDatabase    = ReadString(ConfigFile.i,  #PB_Ascii)
      SetGadgetText(#Gadget_BigOvenToSqLite_Databasename, Program\LastDatabase)
      Program\LastConvertPath = ReadString(ConfigFile.i,  #PB_Ascii)
      SetGadgetText(#Gadget_BigOvenToSqLite_Convertpath,  Program\LastConvertPath)
      CloseFile(ConfigFile.i)
      SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist, "Config file read, BigOven converter ready to work...")
    Else
      SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,  "Couldn't open up the config file")
    EndIf
  ElseIf FileSize(Program\Configfile) = #NoFileFound
    OpenSystemDatabase()
    SetGadgetText(#Gadget_BigOvenToSqLite_Databasename, Program\DatabaseName)
    Program\LastDatabase    = Program\DatabaseName
    SetGadgetText(#Gadget_BigOvenToSqLite_Convertpath,  "[ No BigOven CRB path set yet? ]")
    Program\LastConvertPath = "[ No BigOven CRB path set yet? ]"
    SetGadgetText(#Gadget_BigOvenToSqLite_Errorlist,    "No config file, default database opened, ready to work")
  EndIf
  ; 
  Program\QuitValue = #False
  ; 
  Repeat
    EventID  = WaitWindowEvent()
    MenuID   = EventMenu()
    GadgetID = EventGadget()
    WindowID = EventWindow()
    Select EventID
      Case #PB_Event_CloseWindow
        Select WindowID
        Case  #Window_BigOvenToSqLite                 : Program\QuitValue = #True
        EndSelect
      Case #PB_Event_Gadget
        Select GadgetID
          Case  #Gadget_BigOvenToSqLite_Getdatabase   : OpenOtherDatabase()
          Case  #Gadget_BigOvenToSqLite_Getpath       : GetConvertPath()
          Case  #Gadget_BigOvenToSqLite_Convert       : SearchEngine(Program\ConvertPath)
          Case  #Gadget_BigOvenToSqLite_Exitprogram   : Program\QuitValue = #True
        EndSelect
    EndSelect
  Until Program\QuitValue = #True
  ; 
  CloseWindow(#Window_BigOvenToSqLite)
  ; 
  ConfigFile.i = CreateFile(#PB_Any, Program\Configfile)
  If ConfigFile.i <> #NoFileHandle
    WriteStringN(ConfigFile.i, Program\LastDatabase,    #PB_Ascii)
    WriteStringN(ConfigFile.i, Program\LastConvertPath, #PB_Ascii)
    CloseFile(ConfigFile.i)
  Else
    Debug "Couldn't open up the config file"
  EndIf
  ; 
EndIf
; 
End

tj1010 · Post by **tj1010** » Sun Feb 14, 2016 8:05 pm

I guess it's a good thing the "format" isn't challenging..

Why people try to invent things instead of just use XML or SQL I'll never know..

Fangbeast · Post by **Fangbeast** » Mon Feb 15, 2016 3:38 am

InfraTec's RecipeML importer massaged with my database specs in mind (my recipe book format) in case anyone has a use for the code.

Code: Select all

; Copyrights, thank yous etc
; 
; ***Program***   
; ***License***   
; ***Credits***   
;                 
; Recipe structures in OpenSystemDatabase and their equivalent in DATA.XML from the FDXZ file
; 
; My recipes table	    RecipeML XML table
; 
; 
; Recipetitle		        Recipe Name
; Numberofservings	    Servings
; Recipeauthor
; Categories		        RecipeTypes
; Subcategories
; Preparationtime	      PreparationTime
; Cookingtime		        CookingTime
; Difficulty
; Recipeversion
; Recipesource		      Source
; Copyright		          Copyright
; Reciperating
; Importedfrom		      WebPage
; Authorcomments		    Comments
; Instructions		      ProcedureText		  ;  Multiple lines of procedure text from source xml
; Nutritionaldata	      RecipeNutrition		;  Multiple lines. All items to be extracted with headings, one per line
; Othercomments		      RecipeTips
; Deleted		            0
; Updated		            CreateDate
; Favourite		          0
; Locked			          0
; Recordid
; 
; 
; My ingredients table	recipe filename.XML table
; 
; 
; Ingredientid
; Unit			            MeasureQuantity
; Measure		            Measure
; Ingredient		        Ingredient
; Preparation		        IngredientName
; Lineorder
; Recordid
; 
; 
; My pictures table	    XML recipe file
; 
; 
; Pictureid
; Picture		            FileName    ; Base64Encoded inside uncompressed XML file
; Recordid
;
; 

EnableExplicit                                                                                       ; 

; Set the system to use SQLite database handling

UseSQLiteDatabase()                                                                                    ; Use the SQLite database backend

; 

; Main modules

Declare.s RecipeCRLFSpaceTrim(RecipeString.s)
Declare   OpenSystemDatabase()
Declare.s KillQuote(Instring.s)
Declare.s RepQuote(Instring.s)
Declare.s XMLToText(XMLString.s)
Declare.s TextToXML(TextString.s)

; Import modules

Declare   RequestRecipeML()
Declare   ImportRecipeMLFile(ImportFileName.s)
Declare   ImportRecipeMLFile_XML(ImportFileName.s, *CurrentNode, CurrentSublevel.i)
Declare   SaveRecipe(ImportFileName.s)

; Program constants that don't need to change. Wordier to allow easier error tracking

#ListIconHeadingArea            = -1                                                                  ; 

#AtTheEndOfTheList              = -1                                                                  ; 

#NoItems                        = 0                                                                   ; 
#FirstItem                      = 0                                                                   ; 

#NothingFound                   = 0                                                                   ; 
#NothingSelected                = -1                                                                  ; 

#NoDataFound                    = 0                                                                   ; 
#NoBlobFound                    = 0                                                                   ; 
#NoPictureHandle                = 0                                                                   ; 
#NoPictureFound                 = -1                                                                  ; 
#NoPictureLoaded                = 0                                                                   ; 
#NoPictureResized               = 0                                                                   ; 

#NoColumnIcon                   = 0                                                                   ; 
#NoData                         = 0                                                                   ; 
#NoCurrentLine                  = -1                                                                  ; Make sure no line can be selected accidentally
#NoRecordNumber                 = ""                                                                  ; 
#EmptyString                    = ""                                                                  ; 

#RecipeDeleted                  = 1                                                                   ; 
#RecipeLocked                   = 1                                                                   ; 
#RecipeFavourite                = 1                                                                   ; 

#DatabaseNotOpen                = 0                                                                   ; 
#DatabaseQueryFail              = 0                                                                   ; 
#DatabaseUpdateFail             = 0                                                                   ; 

#NoFileHandle                   = 0                                                                   ; 
#NotEndOfFile                   = 0                                                                   ; 
#FileIsEmpty                    = 0                                                                   ; 
#FileNotDecompressed            = -1                                                                  ; 
#FileIsDirectory                = -2                                                                  ; 

#DirectoryNotCreated            = 0                                                                   ; 

#NoPrintFileHandle              = 0                                                                   ; 
#NoImportFileHandle             = 0                                                                   ; 
#NoExportFileHandle             = 0                                                                   ; 

#NoMenuCreated                  = 0                                                                   ; 

#TitleBarClockTimerEvent        = 624                                                                 ; Title bar clock timer event

#Fish                           = "<°)))o><²³  "                                                      ; Beware the fish!!

#PictureBufferSize        = 10000000

; Program data variables

Structure ProgramData
  CurrentDirectory.s
  TemporaryDirectory.s
  DatabaseHandle.i
  Databasename.s
EndStructure

; Picture data structure

Structure PictureData
  FileType.s
  FileName.s
  OriginalFileName.s
  Description.s
  *PictureBuffer
  PictureBufferPtr.i
EndStructure

; Ingredient table data structure

Structure IngredientData
; Ingredientid.s  Auto increment field during recipe save
  Unit.s
  Measure.s
  Ingredient.s
  Preparation.s
  LineOrder.s
  RecipeId.s
EndStructure

; Recipe table data structure

Structure RecipeData
  Recipetitle.s
  Numberofservings.s
  Recipeauthor.s
  Categories.s
  Subcategories.s
  Preparationtime.s
  Cookingtime.s
  Difficulty.s
  Recipeversion.s
  Recipesource.s
  Copyright.s
  Reciperating.s
  Importedfrom.s
  Authorcomments.s
  Instructions.s
  Nutritionaldata.s
  Othercomments.s
  Deleted.s
  Updated.s
  Favourite.s
  Locked.s
  RecordId.s
  List IngredientList.IngredientData()
  List PictureList.PictureData()
EndStructure

; Global declarations

Global Program.ProgramData
Global Recipe.RecipeData

; Current dirrectory reference for file use

Program\CurrentDirectory    = GetCurrentDirectory()
Program\TemporaryDirectory  = GetTemporaryDirectory()

; Create the database name that I am going to use

Program\DatabaseName        = Program\CurrentDirectory + "RecipeML recipe file test.sqlite"

; Clear the recipe structure in between recipe reads

Procedure ClearRecipeStructure()
  Recipe\Recipetitle.s      = #EmptyString
  Recipe\Numberofservings.s = #EmptyString
  Recipe\Recipeauthor.s     = #EmptyString
  Recipe\Categories.s       = #EmptyString
  Recipe\Subcategories.s    = #EmptyString
  Recipe\Preparationtime.s  = #EmptyString
  Recipe\Cookingtime.s      = #EmptyString
  Recipe\Difficulty.s       = #EmptyString
  Recipe\Recipeversion.s    = #EmptyString
  Recipe\Recipesource.s     = #EmptyString
  Recipe\Copyright.s        = #EmptyString
  Recipe\Reciperating.s     = #EmptyString
 ;Recipe\Importedfrom.s     = #EmptyString  ; This field needs to persis for the current file session
  Recipe\Authorcomments.s   = #EmptyString
  Recipe\Instructions.s     = #EmptyString
  Recipe\Nutritionaldata.s  = #EmptyString
  Recipe\Othercomments.s    = #EmptyString
  Recipe\Deleted.s          = #EmptyString
  Recipe\Updated.s          = #EmptyString
  Recipe\Favourite.s        = #EmptyString
  Recipe\Locked.s           = #EmptyString
  Recipe\RecordId.s         = #EmptyString
  ClearList(Recipe\IngredientList())
  ForEach Recipe\PictureList()
    FreeMemory(Recipe\PictureList()\PictureBuffer)
  Next
  ClearList(Recipe\PictureList())
EndProcedure

; Trim additional CR LF and spaces in front of input lines, InfraTec/Bernd

Procedure.s RecipeCRLFSpaceTrim(RecipeString.s)
  ; 
  RecipeString.s = Trim(RecipeString.s)           ; Trim spaces at the front and the back
  RecipeString.s = Trim(RecipeString.s, #CR$)     ; Trim extra carriage returns at the front and the back
  RecipeString.s = Trim(RecipeString.s, #LF$)     ; Trim extra line feeds at the front and the back
  RecipeString.s = Trim(RecipeString.s, #CR$)     ; Trim Extra carriage returns at the front and the back
  RecipeString.s = Trim(RecipeString.s)           ; Trim spaces at the front and the back
  ; 
  ProcedureReturn RecipeString.s
  ; 
EndProcedure 

; 

Procedure.s KillQuote(Instring.s)
  ProcedureReturn ReplaceString(Instring.s, "''", "'", 1, 1)
EndProcedure

; Berikco's routine to properly replace single quotes with double for SQL passing

Procedure.s RepQuote(Instring.s)
  Protected i.i, tmp.s
  For i = 1 To Len(Instring.s)
    If Mid(Instring.s, i, 1) = "'"
      tmp.s = tmp.s + "''"
    Else
      tmp.s = tmp.s + Mid(Instring.s, i, 1)
    EndIf
  Next i
  ;
  ProcedureReturn tmp.s
  ; 
EndProcedure

; Replace bad characters with XML compatible ones. Thorsten Hoeppner

Procedure.s TextToXML(TextString.s)
  ; 
  Protected XMLString.s 
  ; 
  XMLString.s = ReplaceString(TextString.s,      "&", "&")
  XMLString.s = ReplaceString(XMLString.s,        "<", "<")
  XMLString.s = ReplaceString(XMLString.s,        ">", ">")
  XMLString.s = ReplaceString(XMLString.s,      "'", "&apos;")
  XMLString.s = ReplaceString(XMLString.s,       "€", "&#128")
  XMLString.s = ReplaceString(XMLString.s, #DQUOTE$, """)
  XMLString.s = RemoveString(XMLString.s,  #CR$)
  XMLString.s = ReplaceString(XMLString.s, #LF$,     Chr(182))
  ; 
  ProcedureReturn XMLString.s
  ; 
EndProcedure

; Strip illegal characters from text strings

Procedure.s XMLToText(XMLString.s)
  ; 
  Protected TextString.s 
  ;
  TextString.s = RemoveString(XMLString.s, #CR$)
  ;
  If Left(TextString.s, 1) = #LF$
    TextString.s = Mid(TextString.s, 2)
  EndIf
  ;
  TextString.s = ReplaceString(TextString.s, "&",       "&")
  TextString.s = ReplaceString(TextString.s, "<",        "<")
  TextString.s = ReplaceString(TextString.s, ">",        ">")
  TextString.s = ReplaceString(TextString.s, "&apos;",      "'")
  TextString.s = ReplaceString(TextString.s, """,  #DQUOTE$)
  TextString.s = ReplaceString(TextString.s, "&#128",       "€")
  TextString.s = ReplaceString(TextString.s, "'",       "'")
  TextString.s = ReplaceString(TextString.s, #LF$, #EmptyString)
 ;TextString.s = ReplaceString(TextString.s, Chr(182),    #LF$))
  TextString.s = ReplaceString(TextString.s, #CR$, #EmptyString)
  ;
  ProcedureReturn Trim(TextString.s)
  ;
EndProcedure

; 

Procedure OpenSystemDatabase()
  Protected FileHandle.i, DatabaseUpdate.s
  ; Create the database if it doesn't already exist.
  FileHandle.i = OpenFile(#PB_Any, Program\DatabaseName)
  ; 
  If FileHandle.i
    ; 
    CloseFile(FileHandle.i)
    ; 
    Program\DatabaseHandle = OpenDatabase(#PB_Any, Program\DatabaseName, #EmptyString, #EmptyString)
    ; 
    If Program\DatabaseHandle
      ; Turn on auto database vacuum
      If Not DatabaseUpdate(Program\DatabaseHandle, "PRAGMA auto_vacuum = on")
      EndIf
      ; Write the Recipes table out
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS Recipes("
      DatabaseUpdate.s + "Recipetitle TEXT, "
      DatabaseUpdate.s + "Numberofservings TEXT, "
      DatabaseUpdate.s + "Recipeauthor TEXT, "
      DatabaseUpdate.s + "Categories TEXT, "
      DatabaseUpdate.s + "Subcategories TEXT, "
      DatabaseUpdate.s + "Preparationtime TEXT, "
      DatabaseUpdate.s + "Cookingtime TEXT, "
      DatabaseUpdate.s + "Difficulty TEXT, "
      DatabaseUpdate.s + "Recipeversion TEXT, "
      DatabaseUpdate.s + "Recipesource TEXT, "
      DatabaseUpdate.s + "Copyright TEXT, "
      DatabaseUpdate.s + "Reciperating TEXT, "
      DatabaseUpdate.s + "Importedfrom TEXT, "
      DatabaseUpdate.s + "Authorcomments TEXT, "
      DatabaseUpdate.s + "Instructions TEXT, "
      DatabaseUpdate.s + "Nutritionaldata TEXT, "
      DatabaseUpdate.s + "Othercomments TEXT, "
      DatabaseUpdate.s + "Deleted TEXT, "
      DatabaseUpdate.s + "Updated TEXT, "
      DatabaseUpdate.s + "Favourite TEXT, "
      DatabaseUpdate.s + "Locked TEXT, "
      DatabaseUpdate.s + "Recordid INTEGER PRIMARY KEY AUTOINCREMENT, "
      DatabaseUpdate.s + "UNIQUE (Recipetitle, Instructions) ON CONFLICT FAIL)"
      If Not DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)
        ;Debug DatabaseUpdate.s  + "  --  "  + DatabaseError()
      EndIf
      ; Create the index on Recipes table
      DatabaseUpdate.s = "CREATE INDEX IF NOT EXISTS Recipesindex ON Recipes("
      DatabaseUpdate.s + "Recipetitle, "
      DatabaseUpdate.s + "Instructions)"
      If Not DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)
        ;Debug DatabaseUpdate.s  + "  --  "  + DatabaseError()
      EndIf
      ; Write the Pictures table out
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS Pictures("
      DatabaseUpdate.s + "Pictureid INTEGER PRIMARY KEY AUTOINCREMENT, "
      DatabaseUpdate.s + "Picture BLOB, "
      DatabaseUpdate.s + "Description TEXT, "
      DatabaseUpdate.s + "FileName TEXT, "
      DatabaseUpdate.s + "Recordid INTEGER, "
      DatabaseUpdate.s + "FOREIGN KEY(Recordid) REFERENCES Recipes(Recordid) ON DELETE CASCADE)"     
      If Not DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)
        ;Debug DatabaseUpdate.s  + "  --  "  + DatabaseError()
      EndIf
      ; Write the ingredients table out
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS Ingredients("
      DatabaseUpdate.s + "Ingredientid INTEGER PRIMARY KEY AUTOINCREMENT, "
      DatabaseUpdate.s + "Unit TEXT, "
      DatabaseUpdate.s + "Measure TEXT, "
      DatabaseUpdate.s + "Ingredient TEXT, "
      DatabaseUpdate.s + "Preparation TEXT, "
      DatabaseUpdate.s + "Lineorder TEXT, "
      DatabaseUpdate.s + "Recordid INTEGER, "
      DatabaseUpdate.s + "FOREIGN KEY(Recordid) REFERENCES Recipes(Recordid) ON DELETE CASCADE)"     
      If Not DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)
        Debug DatabaseUpdate.s  + "  --  "  + DatabaseError()
      EndIf
      ; 
    Else
      MessageRequester("Database open error", "Could not open database file in database mode.", #PB_MessageRequester_Ok)
    EndIf
    ; 
  Else
    ; 
    MessageRequester("Database open error", "Could not open or create raw database file.", #PB_MessageRequester_Ok)
    ; 
  EndIf
  ; 
EndProcedure

; Multiple file import

Procedure.i RequestRecipeML()
  ; 
  Protected ResultFlag.i, RecipeName.s, PackHandle.i
  ; Ask the user for a standard Recipeml XML recipe file
  RecipeName.s = OpenFileRequester("Select recipe file", GetCurrentDirectory(), "Recipeml (*.Recipeml)|*.Recipeml", 0, #PB_Requester_MultiSelection)
  ; If the user didn't cancel, do the code below
  If RecipeName.s <> #EmptyString
    ; 
    DatabaseUpdate(Program\DatabaseHandle, "BEGIN TRANSACTION")
    ; 
    While RecipeName.s 
      ; Check to see if the recipe is valid and undamaged
      If FileSize(RecipeName.s) <> #FileIsEmpty
        ; 
        ImportRecipeMLFile(RecipeName.s)
        ;         If DeleteFile(RecipeName.s, #PB_FileSystem_Force) <> 0
        ;         Else
        ;           Debug "Could not delete the processed recipe"
        ;         EndIf
      Else
        ; Debug "The selected file in the requester was not found on disk?"
      EndIf
      ; 
      RecipeName.s = NextSelectedFileName() 
      ; 
    Wend
    ; 
    DatabaseUpdate(Program\DatabaseHandle, "COMMIT")
    ; 
  Else
    ; Debug "Nothing to do as the user cancelled the operation"
  EndIf
  ; 
  ProcedureReturn ResultFlag
  ; 
EndProcedure

; Import a RecipeML XML file format

Procedure ImportRecipeMLFile(ImportFileName.s)
  ; 
  Protected.i XML.i, RecipeMLFile.i
  Protected.q RecipeMLSize
  Protected   *Buffer, *Node
  ; 
  RecipeMLFile.i = ReadFile(#PB_Any, ImportFilename.s)
  ; 
  If RecipeMLFile.i
    ;
    RecipeMLSize.q = Lof(RecipeMLFile.i)
    *Buffer = AllocateMemory(RecipeMLSize.q)
    If *Buffer
      If ReadData(RecipeMLFile.i, *Buffer, RecipeMLSize.q) = RecipeMLSize.q
        XML = CatchXML(#PB_Any, *Buffer, RecipeMLSize.q)
        If XML
          *Node = MainXMLNode(XML)
          If *Node
            ImportRecipeMLFile_XML(ImportFileName.s, *Node, 0)
          EndIf
          FreeXML(XML)
          Recipe\Categories   = Trim(Recipe\Categories,  ",")
          SaveRecipe(ImportFileName.s)
          ; 
          ClearRecipeStructure()
          ; 
        EndIf
      EndIf
      ; 
      FreeMemory(*Buffer)
      ; 
    EndIf
    ; 
    CloseFile(RecipeMLFile.i)
    ;
  Else
    ; Debug "File wasn't opened, no there is file handle."
  EndIf
  ; 
EndProcedure

; 

Procedure.i ImportRecipeMLFile_XML(ImportFileName.s, *CurrentNode, CurrentSublevel.i)
  ; 
  Protected NodeName.s, *ChildNode, AttributeName.s, AttributeValue.s
  ; 
  If XMLNodeType(*CurrentNode) = #PB_XML_Normal
    ; 
    NodeName.s = GetXMLNodeName(*CurrentNode)
    ; 
    ; Debug NodeName.s
    ; 
    If NodeName.s               = "title"
      ; 
      Recipe\Recipetitle.s      = RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode)))
      Recipe\Recipetitle.s      = ReplaceString(Recipe\Recipetitle.s, "'", "''")                            ;: Debug "Title seems okay: "  + Recipe\Recipetitle
      ; 
    ElseIf NodeName.s           = "cat"
      ; 
      Recipe\Categories.s       + RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode))) + ","        ;: Debug Recipe\Categories
      Recipe\Categories.s       = ReplaceString(Recipe\Categories.s, "'", "''")
      ; 
    ElseIf NodeName.s           = "yield"
      ; 
      Recipe\Numberofservings.s + RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode)))              ;: Debug Recipe\Numberofservings
      ; 
      ElseIf NodeName.s         = "qty"
        AddElement(Recipe\IngredientList())
        Recipe\IngredientList()\Unit        = RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode)))  ;: Debug  Recipe\IngredientList()\Unit
      ElseIf NodeName.s         = "unit"
        Recipe\IngredientList()\Measure     = RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode)))  ;: Debug  Recipe\IngredientList()\Measure
      ElseIf NodeName.s         = "item"
        Recipe\IngredientList()\Ingredient  = RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode)))  ;: Debug  Recipe\IngredientList()\Ingredient
        Recipe\IngredientList()\Ingredient  = ReplaceString(Recipe\IngredientList()\Ingredient, "'", "''")
        ; 
    ElseIf NodeName.s           = "step"
      ; 
      Recipe\Instructions.s     + RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode))) + #LF$         ;: Debug Recipe\Instructions
      Recipe\Instructions.s     = ReplaceString(Recipe\Instructions.s, "'", "''")
      ; 
    EndIf
    ; 
    *ChildNode                  = ChildXMLNode(*CurrentNode)
    ; 
    While *ChildNode <> 0
      ImportRecipeMLFile_XML(ImportFileName.s, *ChildNode, CurrentSublevel + 1)
      *ChildNode = NextXMLNode(*ChildNode)
    Wend   
    ; To make sure to save the last record and free the picture memory if any
    If *ChildNode = 0 And CurrentSublevel = 0
      If Len(Recipe\Recipetitle)  <> 0
        SaveRecipe(ImportFileName.s)  ; Save the filled fields of the recipe structure
        ClearRecipeStructure()
      EndIf
    EndIf    
    ; 
  EndIf
  ; 
EndProcedure

; 

Procedure SaveRecipe(ImportFileName.s)
  Protected DatabaseUpdate.s, FileIn.i, FileInSize, *Buffer
  ; Only proceed if the title field at least has something in it
  If Len(Recipe\Recipetitle) <> 0
    Recipe\Deleted  = "0"
    Recipe\Locked   = "0"
    DatabaseUpdate.s = "INSERT INTO Recipes("
    DatabaseUpdate.s + "Recipetitle, "
    DatabaseUpdate.s + "Numberofservings, "
    DatabaseUpdate.s + "Recipeauthor, "
    DatabaseUpdate.s + "Categories, "
    DatabaseUpdate.s + "Subcategories, "
    DatabaseUpdate.s + "Preparationtime, "
    DatabaseUpdate.s + "Cookingtime, "
    DatabaseUpdate.s + "Difficulty, "
    DatabaseUpdate.s + "Recipeversion, "
    DatabaseUpdate.s + "Recipesource, "
    DatabaseUpdate.s + "Copyright, "
    DatabaseUpdate.s + "Reciperating, "
    DatabaseUpdate.s + "Importedfrom, "
    DatabaseUpdate.s + "Authorcomments, "
    DatabaseUpdate.s + "Instructions, "
    DatabaseUpdate.s + "Nutritionaldata, "
    DatabaseUpdate.s + "Othercomments, "
    DatabaseUpdate.s + "Deleted, "
    DatabaseUpdate.s + "Updated, "
    DatabaseUpdate.s + "Favourite, "
    DatabaseUpdate.s + "Locked)"
    DatabaseUpdate.s + "VALUES("
    DatabaseUpdate.s + "'" +  Recipe\Recipetitle      + "', "
    DatabaseUpdate.s + "'" +  Recipe\Numberofservings + "', "
    DatabaseUpdate.s + "'" +  Recipe\Recipeauthor     + "', "
    DatabaseUpdate.s + "'" +  Recipe\Categories       + "', "
    DatabaseUpdate.s + "'" +  Recipe\Subcategories    + "', "
    DatabaseUpdate.s + "'" +  Recipe\Preparationtime  + "', "
    DatabaseUpdate.s + "'" +  Recipe\Cookingtime      + "', "
    DatabaseUpdate.s + "'" +  Recipe\Difficulty       + "', "
    DatabaseUpdate.s + "'" +  Recipe\Recipeversion    + "', "
    DatabaseUpdate.s + "'" +  Recipe\Recipesource     + "', "
    DatabaseUpdate.s + "'" +  Recipe\Copyright        + "', "
    DatabaseUpdate.s + "'" +  Recipe\Reciperating     + "', "
   ;DatabaseUpdate.s + "'" +  Recipe\Importedfrom     + "', "
    DatabaseUpdate.s + "'" +  "RecipeML"              + "', "
    DatabaseUpdate.s + "'" +  Recipe\Authorcomments   + "', "
    DatabaseUpdate.s + "'" +  Recipe\Instructions     + "', "
    DatabaseUpdate.s + "'" +  Recipe\Nutritionaldata  + "', "
    DatabaseUpdate.s + "'" +  Recipe\Othercomments    + "', "
    DatabaseUpdate.s + ""  +  Recipe\Deleted          + "', "
    DatabaseUpdate.s + "'" +  Recipe\Updated          + "', "
    DatabaseUpdate.s + "'" +  Recipe\Favourite        + "', "
    DatabaseUpdate.s + ""  +  Recipe\Locked           + ")"
    ; Now update the database with the new record
    If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s) <> #DatabaseUpdateFail
      ; Get the new record number if any
      If DatabaseQuery(Program\DatabaseHandle, "SELECT last_insert_rowid()")
        If NextDatabaseRow(Program\DatabaseHandle)
          Recipe\Recordid = GetDatabaseString(Program\DatabaseHandle, 0)
        EndIf
      EndIf
      ; Proceed if we got a new record number
      If Len(Recipe\Recordid)
        ForEach Recipe\IngredientList()
          DatabaseUpdate.s  = "INSERT INTO Ingredients("
          DatabaseUpdate.s  + "Unit, "
          DatabaseUpdate.s  + "Measure, "
          DatabaseUpdate.s  + "Ingredient, "
          DatabaseUpdate.s  + "Preparation, "
          DatabaseUpdate.s  + "Lineorder, "
          DatabaseUpdate.s  + "Recordid) "
          DatabaseUpdate.s  + "VALUES("
          DatabaseUpdate.s  + "'" + Recipe\IngredientList()\Unit            + "', "
          DatabaseUpdate.s  + "'" + Recipe\IngredientList()\Measure         + "', "
          DatabaseUpdate.s  + "'" + Recipe\IngredientList()\Ingredient      + "', "
          DatabaseUpdate.s  + "'" + Recipe\IngredientList()\Preparation     + "', "
          DatabaseUpdate.s  + "'" + Recipe\IngredientList()\Lineorder       + "', "
          DatabaseUpdate.s  + ""  + Recipe\Recordid + ")"
          If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s) <>  #DatabaseUpdateFail
            ; Debug "New ingredient saved for recipe"
          Else
            ; Debug "New igredient not saved for recipe" + DatabaseError()
          EndIf
        Next 
        ; ClearList(Ingredients())
        ForEach Recipe\PictureList()
          SetDatabaseBlob(Program\DatabaseHandle, 0, Recipe\PictureList()\PictureBuffer, Recipe\PictureList()\PictureBufferPtr)
          DatabaseUpdate.s = "INSERT INTO Pictures("
          DatabaseUpdate.s + "Picture, "
          DatabaseUpdate.s + "FileName, "
          DatabaseUpdate.s + "Description, "
          DatabaseUpdate.s + "Recordid) "
          DatabaseUpdate.s + "VALUES("
          DatabaseUpdate.s + "?, "
          DatabaseUpdate.s + "'"  + Recipe\PictureList()\OriginalFileName + "', "
          DatabaseUpdate.s + "'"  + Recipe\PictureList()\Description      + "', "
          DatabaseUpdate.s + ""   + Recipe\Recordid                       + ")"
          ; 
          If DatabaseUpdate(Program\DatabaseHandle, DatabaseUpdate.s)  <> #DatabaseUpdateFail
            ;MessageRequester("Error", DatabaseError())
          EndIf
        Next
          Debug "Saved: " + KillQuote(Recipe\Recipetitle.s) + " Program: "  + Recipe\Importedfrom.s + " File: "  + ImportFileName.s
      Else
        ; Debug "Database failed to be updated so we didn't get a new record number."
      EndIf
      ; 
    Else
      Debug DatabaseUpdate.s
      Debug "---------------------------------------------------------------------------"
      Debug "The database update failed. " + DatabaseError()
    EndIf
    ; Saving an edited record
  Else
    ;Debug ImportFileName.s  + " failed: "  + Recipe\Recipetitle  ; Might already be saved
  EndIf
  ; 
EndProcedure

; Open the system database file

OpenSystemDatabase()                                                                                   ; 

; Get an RecipeML file from the user and then parse it

RequestRecipeML()                                                                                      ; 

; 

End                                                                                                    ;

;

Fangbeast · Post by **Fangbeast** » Thu Feb 18, 2016 6:17 am

Just finished massaging all the individual import routines into one big one prior to putting it all into my main recipe program.

It just grabs the fields that I wanted in my recipe manager and you can change those to suit yourself.

Not convinced that I have eliminated all the bugs but I am having a hard time fixing my own mistakes lately.

InfraTec wrote ALL the import routines. All I did was massage them all together into a single working program.

Feel free to steal and improve it because I sure as hell can't manage any more than this.

It supports:

Living CookBook's FDX and FDXZ
MasterCook I MXP
MasterCook II MX2
MealMaster MMF
Now You're Cooking NYC
RecipeML .recipeml
BigOven CRB

https://www.dropbox.com/s/eyw12ncpu1bxt ... rt.7z?dl=0

Fangbeast · Post by **Fangbeast** » Thu Feb 18, 2016 12:00 pm

This drives me bonkers. The section below crashes at the replacestring statement if I am importing hundreds of recipe files into the program, something about "Memory write at address 0" or something or other.

Code: Select all

Recipe\Instructions.s + RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode))) + #LF$
      
Recipe\Instructions.s = ReplaceString(Recipe\Instructions.s, "'", "''")

But if I combine the two lines into one, it works????? What the hell?

Code: Select all

Recipe\Instructions.s + ReplaceString(RecipeCRLFSpaceTrim(XmlToText(GetXMLNodeText(*CurrentNode))), "'", "''") + #LF$

And when my database starts to get above 900Meg or so, I start getting " could not be opened, there is no file handle." on more files I try to import.

Seriously thinking of giving this recipe monster up at this stage.

Fangbeast · Post by **Fangbeast** » Wed Feb 24, 2016 1:11 am

I persevered, not that anyone is listening of course:):)

Here is the current working first draft of my craptastic, featureless recipe manager.

Lots of things to do under the hood, help pages etc and other things. But it works.

Lovingly fondled by me over many sleepless nights (Oh okay, so it was the heat too!!), now you can lovingly fondle the code as well and tell me that I am the bastard sheep fondling cousin to that goat rooter srod!!

hehehe, wonder how long it takes him to respond, hehehehe

https://www.dropbox.com/s/s9yk29jr0fuhl ... es.7z?dl=0

Fangbeast · Post by **Fangbeast** » Sat Feb 27, 2016 8:35 am

Righty ho, thanks to TI-994a's printing routine, I have uploaded a new build to DropBox along with an installed database of nearly 4,000 recipes that someone asked me for.

So, credits:

InfraTec: All import routines. What a legend!
KenMo: Nice starry rating gadget.
NetMaestro: Lovely MouseHover library
Jumbuck: ListIcon sort routine

srod: Various diseased sheep.

https://www.dropbox.com/s/s9yk29jr0fuhl ... es.7z?dl=0

Since TI's routine uses the vector library, this code will now only function on PB5.41 x86 and above.

And with the database, the package is around 77meg so I hope someone gets a use out of it, still lots to do.

There is also a compiled exe for anyone without the latest pb who just wants to test.

jack · Post by **jack** » Sun Feb 28, 2016 6:58 am

hello Fangbeast
I tried your recipe program but only see about 49 recipes, the left pane where the categories are shows Beef(0), Bread(0) and so on, all categories are 0 but the recipe database is 100MB
also tried to print 1-hour Vegan Shepherd's Pie and the ingredients overlap with the instructions but when printing the Almond Flour-crusted Chicken Piccata only shows the Ingredients and a huge but horizontally compressed "FOOD&WINE" (16cm high by 8.5cm wide on bottom left of page), the screen shows the recipes OK it's just the printing that need work, also how do I get the rest of the recipes to show?

Fangbeast · Post by **Fangbeast** » Sun Feb 28, 2016 1:19 pm

I tried your recipe program but only see about 49 recipes

Since my database could potentially hold up to 450,000 before crawling to a screaming halt, I intentionally limited the database statement to prevent more than 50 in the list.

In procedure FindRecipe(), lines 193 and 199, change this string " ORDER BY Recipetitle ASC LIMIT 50"

to " ORDER BY Recipetitle ASC LIMIT 100" or any other figure that you might find reasonable or just change it to " ORDER BY Recipetitle ASC" to remove any limit

the left pane where the categories are shows Beef(0), Bread(0) and so on, all categories are 0 but the recipe database is 100MB

This one is a bit trickier to explain. I have in excess of over 650,000 recipes (I think) and all the different types put different categories, main categories and cuisine types all over the place, even extra text that doesn't belong in the category fields.

So, I wanted my recipe program to have a MAIN category and subcategories and when recipes are imported, all their subcategory junk is put into my Subcategory field.

At the moment, if you want to match a category, you have to make sure there is a category in the Category field of a recipe for it to match.

When you create a new category or edit an old one, the program does a count of the recipes whose category field matches the one clicked on and updates the counter.

You can always use the search controls at the bottom of the form to search just the subcategory fields.

also tried to print 1-hour Vegan Shepherd's Pie and the ingredients overlap with the instructions but when printing the Almond Flour-crusted Chicken Piccata only shows the Ingredients and a huge but horizontally compressed "FOOD&WINE" (16cm high by 8.5cm wide on bottom left of

I see what you mean, just tried it myself. I will have to ask the person who wrote the print routine as I didn't do it or understand it, he might be able to work out the spacing.

Recipes manager is still in the early stages and there are bound to be heaps of bugs. I just finished the export routines tonight, so tired.

I'll get back to you as soon as I know something

davido · Post by **davido** » Sun Feb 28, 2016 2:49 pm

@jack,
I noticed the same problem.
For some reason I double-clicked the Find icon and suddenly 4863 recipes appeared behind the curtain of green smoke!?

@Fangbeast,
Another really beautiful GUI. Great program. Thank you for sharing.

jack · Post by **jack** » Sun Feb 28, 2016 3:43 pm

I agree with davido, the program looks really good.

Fangbeast, you also need to change the string in the function ShowCategory, line 63.

TI-994A · Post by **TI-994A** » Sun Feb 28, 2016 7:57 pm

jack wrote:...tried to print 1-hour Vegan Shepherd's Pie and the ingredients overlap with the instructions but when printing the Almond Flour-crusted Chicken Piccata only shows the Ingredients and a huge but horizontally compressed "FOOD&WINE"...

Fangbeast wrote:...the person who wrote the print routine ... might be able to work out the spacing.

Sorry about that, but it was originally posted just to demonstrate the use of the vector printing functions; a quick solution not meant for production use.

In any case, I've effected the following fixes and additions:
1. ingredients/method block alignment
2. resized-image aspect ratio
3. page continuation for longer recipes
4. font size adjustment for long titles
5. recipe name as print-job name in spooler

(as named in Fangbeast's Project: _TI_PrintRecipe.pbi)

Code: Select all

;=======================================================================================================
; TI-994A's routine for vector printing
;=======================================================================================================

Procedure TI_PrintRecipe(recipeName.s, image.i, ingredients.s, method.s)
 
  recipeTitleFont   = LoadFont(#PB_Any, "Arial",            24, #PB_Font_Bold)
  recipeBodyFonts   = LoadFont(#PB_Any, "Times New Roman",  14)
  recipeHeaderFonts = LoadFont(#PB_Any, "Times New Roman",  22, #PB_Font_Bold | #PB_Font_Italic)
  
  If PrintRequester()
    If StartPrinting("MGB Recipes: " + recipeName)
      If StartVectorDrawing(PrinterVectorOutput(#PB_Unit_Millimeter))
        
        frameWidth  = 1
        singleSpace = 5
        doubleSpace = 10
        pageMargin  = 10
        pageWidth   = VectorOutputWidth()
        pageHeight  = VectorOutputHeight()
        
        MovePathCursor(pageMargin, pageMargin)
        AddPathLine(pageWidth - (pageMargin * 2), 0,      #PB_Path_Relative)
        AddPathLine(0, pageHeight - (pageMargin * 2),     #PB_Path_Relative)
        AddPathLine(- (pageWidth - (pageMargin  * 2)), 0, #PB_Path_Relative)
        AddPathLine(0, -(pageHeight - (pageMargin * 2)),  #PB_Path_Relative)
        StrokePath(frameWidth, #PB_Path_SquareEnd)
       
        VectorFont(FontID(recipeTitleFont))
        VectorSourceColor(RGBA(0, 0, 155, 255))
        
        titleFontSize = 24 / 2.83
        titleXMargin = (pageWidth - VectorTextWidth(recipeName)) / 2
        While titleXMargin < pageMargin
          If titleFontSize > 1
            titleFontSize - 1
            VectorFont(FontID(recipeTitleFont), titleFontSize)
            titleXMargin = (pageWidth - VectorTextWidth(recipeName)) / 2
          Else
            Break
          EndIf
        Wend
        titleYMargin = pageMargin + singleSpace
        
        MovePathCursor(titleXMargin, titleYMargin)
        DrawVectorText(recipeName)
       
        startX = pageMargin + singleSpace
        startY = PathCursorY() + (singleSpace * 3)
        MovePathCursor(startX, startY)
        
        If IsImage(image)
          If ImageWidth(image) <= ImageHeight(image)
            imgAspect.d = ImageWidth(image) / ImageHeight(image)
            imgHeight.d  = (pageWidth - (pageMargin * 2)) / 2
            imgWidth.d = imgHeight * imgAspect
          Else
            imgAspect.d = ImageHeight(image) / ImageWidth(image)
            imgWidth.d  = (pageWidth - (pageMargin * 2)) / 2
            imgHeight.d = imgWidth * imgAspect
          EndIf
          
          DrawVectorImage(ImageID(image), 255, imgWidth, imgHeight)
          startX = PathCursorX() + singleSpace
        EndIf
        
        imageBase = PathCursorY()
        
        VectorFont(FontID(recipeHeaderFonts))
        VectorSourceColor(RGBA(0, 0, 0, 255))
        MovePathCursor(startX, startY)
        DrawVectorText("Ingredients")
        
        startY = PathCursorY() + doubleSpace
        VectorFont(FontID(recipeBodyFonts))
        MovePathCursor(startX, startY)
        paragraphWidth = pageWidth - (startX + pageMargin + singleSpace)
        paragraphHeight = pageHeight - (startY + pageMargin)
        DrawVectorParagraph(ingredients, paragraphWidth, paragraphHeight)
        
        If IsImage(image) 
          If PathCursorY() > imageBase
            indentY = PathCursorY() + doubleSpace
            indentWidth = (pageWidth - ((pageMargin * 2) + (singleSpace * 3))) - paragraphWidth
          EndIf
          startY = imageBase
        Else
          startY = PathCursorY()
        EndIf
                
        startY + doubleSpace
        startX = pageMargin + singleSpace
        VectorFont(FontID(recipeHeaderFonts))
        VectorSourceColor(RGBA(0, 0, 0, 255))
        MovePathCursor(startX, startY)
        DrawVectorText("Method")
        
        startY = PathCursorY() + doubleSpace
        VectorFont(FontID(recipeBodyFonts))
        MovePathCursor(startX, startY)
        
        If indentY
          paragraphWidth = indentWidth
          paragraphHeight = indentY - (startY - doubleSpace)
          requiredHeight = 0
          
          While requiredHeight < paragraphHeight
            fragmentLen + 1
            If fragmentLen > Len(method)
              Break
            EndIf            
            partialParagraph$ = Mid(method, 1, fragmentLen)
            requiredHeight = VectorParagraphHeight(partialParagraph$, paragraphWidth, paragraphHeight)
          Wend
          
          If fragmentLen < Len(method)
            While Mid(partialParagraph$, fragmentLen, 1) <> " "
              fragmentLen - 1
              If fragmentLen < 1
                Break
              EndIf
            Wend  
          EndIf
          
          partialParagraph$ = Mid(partialParagraph$, 1, fragmentLen)
          DrawVectorParagraph(partialParagraph$, paragraphWidth, paragraphHeight)
          
          method = Mid(method, fragmentLen + 1)
          startY = PathCursorY()
        EndIf
        
        If Trim(method) <> ""
          While Trim(method) <> ""
            
            paragraphWidth = pageWidth - ((pageMargin * 2) + doubleSpace)
            paragraphHeight = pageHeight - (startY + pageMargin)
            requiredHeight = 0
            fragmentLen = 0
            
            While requiredHeight < paragraphHeight
              fragmentLen + 1
              If fragmentLen > Len(method)
                Break
              EndIf
              partialParagraph$ = Mid(method, 1, fragmentLen)
              requiredHeight = VectorParagraphHeight(partialParagraph$, paragraphWidth, paragraphHeight)
            Wend
            
            If fragmentLen < Len(method)
              While Mid(partialParagraph$, fragmentLen, 1) <> " "
                fragmentLen - 1
                If fragmentLen < 1
                  Break
                EndIf
              Wend  
            EndIf
            
            partialParagraph$ = Mid(partialParagraph$, 1, fragmentLen)
            DrawVectorParagraph(partialParagraph$, paragraphWidth, paragraphHeight)
            
            method = Mid(method, fragmentLen + 1)
            
            If Trim(method) = ""
              Break
            EndIf
          
            NewVectorPage()
            
            MovePathCursor(pageMargin, pageMargin)
            AddPathLine(pageWidth - (pageMargin * 2), 0,      #PB_Path_Relative)
            AddPathLine(0, pageHeight - (pageMargin * 2),     #PB_Path_Relative)
            AddPathLine(- (pageWidth - (pageMargin  * 2)), 0, #PB_Path_Relative)
            AddPathLine(0, -(pageHeight - (pageMargin * 2)),  #PB_Path_Relative)
            StrokePath(frameWidth, #PB_Path_SquareEnd)
            
            VectorFont(FontID(recipeTitleFont), titleFontSize)
            VectorSourceColor(RGBA(0, 0, 155, 255))
            
            MovePathCursor(titleXMargin, titleYMargin)
            DrawVectorText(recipeName)            
                        
            startY = PathCursorY() + (singleSpace * 3)   
            VectorFont(FontID(recipeBodyFonts))
            VectorSourceColor(RGBA(0, 0, 0, 255))
            MovePathCursor(startX, startY)
            
          Wend    
        EndIf
        
        StopVectorDrawing()
        
      EndIf
    EndIf
    StopPrinting()
  EndIf
 
EndProcedure

Some screenshots of the fixes:

It's still in no way release-quality, but hopefully it'll serve its purpose.

PS: In a bid to save ink and paper, printing tests were directed to PDF output. These would usually be saved to file, but the recipe names that contain double-quotes would fail. Just FYI.

EDIT (28/2): Inconsistent font size on continuation page fixed.

Fangbeast · Post by **Fangbeast** » Sun Feb 28, 2016 8:14 pm

Hehehe, thanks for the fixes. I don't have the brains of srod's diseased sheep collection to fix it myself but I can strip double quotes from filenames:):):)

Davido and Jack, I don't want nearly 4,000 recipes to appear in my list each time but feel free to alter those lines yourself, you know where they are.

I might put in a program option at some stage to turn that limit on or off as needed.

jack · Post by **jack** » Sun Feb 28, 2016 8:42 pm

sorry to report that for me it still doesn't work right
am using PB 5.42 Beta 5 LTS(x86) same problem when using PB 5.40 LTS(x86) Windows 10 x64
I am printing to PDF using "Microsoft Print to PDF", page one is ok the problem is on page 2

PureBasic Forums - English

Recipe management software?

Re: Recipe management software?

Re: Recipe management software?

Re: Recipe management software?

Re: Recipe management software?

Re: Recipe management software?

Re: Recipe management software?

Re: Recipe management software?

Re: Recipe management software?

Re: Recipe management software?

Re: Recipe management software?

Re: Recipe management software?

Re: Recipe management software?

Re: Recipe management software?

Re: Recipe management software?