Parent Child Table (part 7)

 And here is the entire Galaxy Module, or Parent Module:



XIncludeFile "../Utils/ThemedMessage.pbi"
XIncludeFile "DB.pbi"
XIncludeFile "SolarSystem.pbi"

DeclareModule GALAXY
  Structure Galaxy
    ID.i                    ; Table Primary Key
    Name.s                  ; Name of Galaxy

    List SolarSystemCollection.SOLARSYSTEM::SolarSystem()
    List DeleteSolarSystemCollection.SOLARSYSTEM::SolarSystem()

    State.i
  EndStructure


  Declare LoadComboName(cbData.i)
  Declare LoadListViewName(lvData.i)
  Declare LoadListIcon(liData.i)
  ;-----------------------------------------------
  Declare.i LoadComboBoxSolarSystemCollectionName(liData.i, *Class.Galaxy)
  Declare.i LoadListViewSolarSystemCollectionName(liData.i, *Class.Galaxy)
  Declare.i ListIconSolarSystemCollection(liData.i, *Class.Galaxy)
  Declare.s ListIconItemSolarSystem(*Class.SOLARSYSTEM::SolarSystem)
  ;-----------------------------------------------
  Declare CreateTable()
  Declare.i Init(*Class.Galaxy)
  Declare.i Find(GalaxyID.i, *Class.Galaxy)
  Declare.i FindByName(Name$, *Class.Galaxy)
  Declare.i Save(*Class.Galaxy)
  Declare.i Delete(*Class.Galaxy)
  Declare.i Validate(*Class.Galaxy)
  Declare.i DeleteValidate(*Class.Galaxy)

EndDeclareModule

Module GALAXY
  EnableExplicit

  ;-----------------------------------------------
  Declare.i LoadSolarSystemCollection(*Class.Galaxy)
  Declare.i SaveSolarSystemCollection(*Class.Galaxy)
  ;-----------------------------------------------
  Declare Dispose(*Class.Galaxy)

  Procedure CreateTable()
    Protected DBID, Sql$ 

      Sql$ = ""
      Sql$ = Sql$ + "DROP TABLE IF EXISTS GALAXY ;"
      Sql$ = Sql$ + ""
      Sql$ = Sql$ + "CREATE TABLE GALAXY"
      Sql$ = Sql$ + "("
      Sql$ = Sql$ + "	ID INTEGER NOT NULL PRIMARY KEY  AUTOINCREMENT,"
      Sql$ = Sql$ + "		NAME VARCHAR(100)  NOT NULL"
      Sql$ = Sql$ + ""
      Sql$ = Sql$ + ");"
      Sql$ = Sql$ + ""
      Sql$ = Sql$ + ""

    DBID = DB::open() 
    DB::update(DBID, Sql$)

  EndProcedure

  Procedure Init(*Class.Galaxy)
    With *Class

    Dispose(*Class)

      \ID = 0 
      \Name = ""

      NewList \SolarSystemCollection.SOLARSYSTEM::SolarSystem()
      NewList \DeleteSolarSystemCollection.SOLARSYSTEM::SolarSystem()

      \State = DB::#New

    EndWith
  EndProcedure

  Procedure Dispose(*Class.Galaxy)
    ;Dispose all Child Collections
    ForEach SolarSystemCollection()
      SOLARSYSTEM::Dispose(@SolarSystemCollection()) 
    Next 

    FreeList(SolarSystemCollection())
    FreeList(DeleteSolarSystemCollection())

    ClearStructure(*Class, Galaxy)
  EndProcedure

  Procedure LoadComboName(cbData.i)
    Protected DBID, Sql$ 
      ComboBoxEx::ClearItems(cbData)
      DBID = DB::Open(DBTYPE::#Sqlite)
      Sql$ = " Select "
      Sql$ = Sql$ + " ID, "
      Sql$ = Sql$ + " Name "
      Sql$ = Sql$ + " FROM "
      Sql$ = Sql$ + " Galaxy "
      Sql$ = Sql$ + " ORDER BY "
      Sql$ = Sql$ + " Name "

      If DB::Query(DBID, Sql$)
        While NextDatabaseRow(DBID)
          ComboBoxEx::AddItem(cbData, -1, GetDatabaseString(DBID,DatabaseColumnIndex(DBID,"Name")))
          ComboBoxEx::SetItemData(cbData, ComboBoxEx::CountItems(cbData)-1, GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"ID")))
        Wend
      EndIf
      DB::Close(DBID)

  EndProcedure

  Procedure LoadListViewName(lvData.i)
    Protected DBID, Sql$
    If GadgetType(lvData) = #PB_GadgetType_ListView 
      ClearGadgetItems(lvData)
      DBID = DB::Open(DBTYPE::#Sqlite)

      Sql$ = " Select "
      Sql$ = Sql$ + " ID, "
      Sql$ = Sql$ + " Name"
      Sql$ = Sql$ + " FROM "
      Sql$ = Sql$ + " Galaxy "
      Sql$ = Sql$ + " ORDER BY "
      Sql$ = Sql$ + " Name"

      If DB::Query(DBID, Sql$)
        While NextDatabaseRow(DBID) 
        AddGadgetItem(lvData, -1, GetDatabaseString(DBID,DatabaseColumnIndex(DBID,"Name")))
          SetGadgetItemData(lvData, CountGadgetItems(lvData) -1, GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"ID")))
        Wend
      EndIf
      DB::Close(DBID)
    Else
      THEMEDMESSAGE::MessageBox("Program Error LoadListViewName" ,"Procedure called With wrong gadget type!")
    EndIf
  EndProcedure

  Procedure LoadListIcon(liData.i)
    Protected DBID, Sql$ 
    Protected Result 

      DBID = DB::Open(DBTYPE::#Sqlite)
      ListEx::DisableReDraw(liData, #True) 
      ListEx::ClearItems(liData)
      ListEx::RemoveColumn(liData, 0) ;Name

      ;===== Build Column Titles =====-
      ListEx::AddColumn(liData, 0, "Name", 175)
      ListEx::SetColumnAttribute(liData, 0, ListEx::#Align,  ListEx::#Left)

      Sql$ = " Select "
      Sql$ = Sql$ + "Name, "
      Sql$ = Sql$ + "ID "
      Sql$ = Sql$ + "FROM "
      Sql$ = Sql$ + "Galaxy "
      Sql$ = Sql$ + "ORDER BY "
      Sql$ = Sql$ + "Name "

      ;===== Populate Data Into Column =====
      Result = DB::Query(DBID, Sql$)
      If Result > 0
        While NextDatabaseRow(DBID)
          ListEx::AddItem(liData, -1,GetDatabaseString(DBID, DatabaseColumnIndex(DBID,"Name")) + #LF$ ) 
          ListEx::SetItemData(liData, ListEx::CountItems(liData) -1 , GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"ID")) ) 
        Wend
      EndIf

      DB::Close(DBID)

      ListEx::DisableReDraw(liData, #False) 
  EndProcedure


  Procedure.i Find(GalaxyID.i, *Class.Galaxy)
    Protected Sql$, DBID, Result, PictureSize, *picture, BLOBSize
    Protected PassFail = #False 

    Init(*Class)

    DBID = DB::Open(DBTYPE::#Sqlite)
    SetDatabaseLong(DBID, 0, GalaxyID)
    Sql$ = "SELECT "
    Sql$ =  Sql$ + "ID, "
    Sql$ =  Sql$ + "Name"
    Sql$ =  Sql$ + " FROM "
    Sql$ =  Sql$ + "Galaxy "
    Sql$ =  Sql$ + " WHERE "
    Sql$ =  Sql$ + "ID = ? "

    PassFail = DB::Query(DBID, Sql$)
    If PassFail 
      While NextDatabaseRow(DBID)
        With *Class
          \ID = GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"ID")) 
          \Name = GetDatabaseString(DBID, DatabaseColumnIndex(DBID,"Name")) 
          \State = DB::#Clean 
        EndWith 
      Wend
    EndIf

    DB::Close(DBID)

    ClearList(*Class\SolarSystemCollection.SOLARSYSTEM::SolarSystem()) 
    ;-----------------------------------------------
    LoadSolarSystemCollection(*Class)
    ;-----------------------------------------------

    ProcedureReturn PassFail 

  EndProcedure

  Procedure.i FindByName(Name$, *Class.Galaxy)
    Protected Sql$, DBID, Result, PictureSize, *picture, BLOBSize
    Protected PassFail = #False 

    Init(*Class)

    DBID = DB::Open(DBTYPE::#Sqlite)
    SetDatabaseString(DBID, 0, Name$ )
    Sql$ = "SELECT "
    Sql$ =  Sql$ + "ID, "
    Sql$ =  Sql$ + "Name"
    Sql$ =  Sql$ + " FROM "
    Sql$ =  Sql$ + " Galaxy "
    Sql$ =  Sql$ + " WHERE "
    Sql$ =  Sql$ + " Name = ? "

    PassFail = DB::Query(DBID, Sql$)
    If PassFail 
      While NextDatabaseRow(DBID)
        With *Class
          \ID = GetDatabaseLong(DBID, DatabaseColumnIndex(DBID,"ID")) 
          \Name = GetDatabaseString(DBID, DatabaseColumnIndex(DBID,"Name")) 
          \State = DB::#Clean 
        EndWith 
      Wend
    EndIf

    DB::Close(DBID)

    ClearList(*Class\SolarSystemCollection.SOLARSYSTEM::SolarSystem()) 
    ;-----------------------------------------------
    LoadSolarSystemCollection(*Class)
    	;-----------------------------------------------

    ProcedureReturn PassFail 

  EndProcedure


  Procedure.i Save(*Class.Galaxy)
    Protected Sql$, Result, DBID, Length, *MemoryGraphicID 
    Protected PassFail = #False 

    If Validate(*Class) = #False
      ProcedureReturn #False
    Else 
      DBID = DB::Open() 
      With *Class
        SetDatabaseString(DBID, 0, \Name)
      EndWith 

      If *Class\State = DB::#New 
        Sql$ = "INSERT "
        Sql$ = Sql$ + " into "
        Sql$ = Sql$ + "Galaxy" 
        Sql$ = Sql$ + " ("
        Sql$ = Sql$ + "      Name"
        Sql$ = Sql$ + " )"
        Sql$ = Sql$ + " VALUES "
        Sql$ = Sql$ + " (? )"

        PassFail = DB::Update(DBID, Sql$)

        If PassFail 
          *Class\State = DB::#Clean
          *Class\ID = DB::getLastID(DBID, "Galaxy")
        EndIf 

        DB::Close(DBID)


        ;-----------------------------------------------
        SaveSolarSystemCollection(*Class)
        ;-----------------------------------------------

      Else ;>>>>>> UPDATE <<<<<<

        SetDatabaseLong(DBID, 1, *Class\ID) 
        Sql$ = "UPDATE "
        Sql$ = Sql$ + "Galaxy "
        Sql$ = Sql$ + "SET "
        Sql$ = Sql$ + "Name = ? "
        Sql$ = Sql$ + " WHERE "
        Sql$ = Sql$ + "ID = ? "

        PassFail = DB::Update(DBID, Sql$) 

        If PassFail 
          *Class\State = DB::#Clean
        EndIf 

        DB::Close(DBID) 

        ;-----------------------------------------------
        SaveSolarSystemCollection(*Class)
        ;-----------------------------------------------
      EndIf 


    EndIf 

  ProcedureReturn PassFail  

  EndProcedure

  ;=======================================================
  ;=====  LOAD/SAVE CHILD COLLECTION 
  ;=======================================================
  Procedure LoadComboBoxSolarSystemCollectionName(cbData.i, *ChildSolarSystemCollection())
    ComboBoxEx::ClearItems(cbData)
    ForEach *ChildSolarSystemCollection()
      ComboBoxEx::AddItem(cbData, -1, *ChildSolarSystemCollection()\Name)
      ComboBoxEx::SetItemData(cbData, ComboBoxEx::CountItems(cbData), *ChildSolarSystemCollection()\ID)
    Next
  EndProcedure
  Procedure LoadListViewSolarSystemCollectionName(lvData.i, *Child.SolarSystemCollection())
    ClearGadgetItems(lvData)
    ForEach *ChildSolarSystemCollection()
      AddGadgetItem(cbData, -1, *ChildSolarSystemCollection()\Name)
      SetGadgetItemData(cbData, CountGadgetItems(cbData), *ChildSolarSystemCollection()\ID)
    Next
  EndProcedure
  Procedure ListIconSolarSystemCollection(liData.i, *Child.Galaxy)
      ListEx::DisableReDraw(liData, #True) 
      ListEx::ClearItems(liData)
      ListEx::RemoveColumn(liData, 0)
      ListEx::RemoveColumn(liData, 0)
      ListEx::RemoveColumn(liData, 0)

      ;===== Build Column Titles =====
      ListEx::AddColumn(liData, 0, "Name", 175)
      ListEx::AddColumn(liData, 1, "Row", 70)
      ListEx::AddColumn(liData, 2, "Col", 70)
      ListEx::SetColumnAttribute(liData, 0, ListEx::#Align,  ListEx::#Left) 
      ListEx::SetColumnAttribute(liData, 1, ListEx::#Align,  ListEx::#Center) 
      ListEx::SetColumnAttribute(liData, 2, ListEx::#Align,  ListEx::#Center) 

      ;===== Populate Data Into Column =====
      ForEach *Child\SolarSystemCollection()
        ListEx::AddItem(liData, -1, ListIconItemSolarSystem(*Child\SolarSystemCollection()))
        ListEx::SetItemData(liData, ListEx::CountItems(liData)-1, *Child\SolarSystemCollection()\ID ) 
      Next 
      ListEx::DisableReDraw(liData, #False) 
  EndProcedure

  Procedure.s ListIconItemSolarSystem(*Class.SOLARSYSTEM::SolarSystem)
    Protected lvItem$
    lvItem$ =  *Class\Name + #LF$  +  Str(*Class\GRow) + #LF$  +  Str(*Class\GCol) + #LF$ 
    ProcedureReturn lvItem$
  EndProcedure

  Procedure LoadSolarSystemCollection(*Class.Galaxy)
    Protected DBID, Sql$
    DBID = DB::Open(DBTYPE::#Sqlite)
    SetDatabaseLong(DBID, 0, *Class\ID)

    Sql$ = " SELECT ID "
    Sql$ = Sql$ + " FROM SolarSystem "
    Sql$ = Sql$ + " WHERE GalaxyID = ? "

    NewList FID()
    If DB::Query(DBID, Sql$)
      While NextDatabaseRow(DBID) 
        AddElement(FID())
        FID()=GetDatabaseLong(DBID, 0)
      Wend

      DB::Close(DBID)

      ForEach FID()
        AddElement(*Class\SolarSystemCollection())
        SOLARSYSTEM::Find(FID(), *Class\SolarSystemCollection())
      Next
      FreeList(FID())
    EndIf

  EndProcedure


  Procedure SaveSolarSystemCollection(*Class.Galaxy)
    Protected Index, *WorkSolarSystem.SOLARSYSTEM::SolarSystem
    Index = 0
    ForEach *Class\DeleteSolarSystemCollection()
      If *Class\DeleteSolarSystemCollection() > 0
        If SOLARSYSTEM::Delete(*Class\DeleteSolarSystemCollection()) = #False
          THEMEDMESSAGE::MessageBox("Error","Error Deleting Xref Collection 'SolarSystem' !") 
        EndIf
      EndIf
    Next 
    ForEach *Class\SolarSystemCollection()
      ;Link child back to parent
      *Class\SolarSystemCollection()\GalaxyID = *Class\ID
      ;Reset status
      If *Class\SolarSystemCollection()\State = DB::#NewDirty
        *Class\SolarSystemCollection()\State = DB::#New
      EndIf
      ;Save them all
      If *Class\SolarSystemCollection()\State <> DB::#New 
        *Class\SolarSystemCollection()\State = DB::#Dirty
      EndIf

      Select *Class\SolarSystemCollection()\State
        Case DB::#New
          *WorkSolarSystem = *Class\SolarSystemCollection()
          If SOLARSYSTEM::Save(*WorkSolarSystem) = #False
            THEMEDMESSAGE::MessageBox("Error","Error Inserting field 'SolarSystem' !" ) 
          EndIf
        Case DB::#Dirty
          *WorkSolarSystem = *Class\SolarSystemCollection()
          If SOLARSYSTEM::Save(*WorkSolarSystem) = #False
            THEMEDMESSAGE::MessageBox("Error","Error Updating field 'SolarSystem' !") 
          EndIf
        Case DB::#Clean
          ;all good ignore
      EndSelect 

    Next
  EndProcedure
  ;=======================================================

  Procedure.i Delete(*Class.Galaxy)
    Protected Result, DBID 
    Protected Sql$ 

    If DeleteValidate(*Class) = #False 
      ProcedureReturn #False 
    Else 

    ;=======================================================
      ForEach *Class\SolarSystemCollection() 
      SOLARSYSTEM::Delete(*Class\SolarSystemCollection()) 
    Next 
    ;=======================================================

    DBID = DB::Open(DBTYPE::#Sqlite)
    SetDatabaseLong(DBID, 0, *Class\ID) 
    Sql$ = " DELETE "
    Sql$ = Sql$ + "FROM "
    Sql$ = Sql$ + "Galaxy "
    Sql$ = Sql$ + "WHERE "
    Sql$ = Sql$ + "ID = ?  "
    Result = DB::Update(DBID, Sql$) 
    DB::Close(DBID) 
    ProcedureReturn Result 
  EndIf

  EndProcedure


  Procedure.i Validate(*Class.Galaxy)

    Protected DBID, Sql$, PassFail.i = #True 
    Protected ErrMsg.s = ""


    If Len(*Class\Name) < 1 
      PassFail = #False
      ErrMsg = ErrMsg + "Name cannot be less than 1 characters in length!" + #LF$ 
    EndIf
    If Len(*Class\Name) > 100 
      PassFail = #False 
      ErrMsg = ErrMsg + "Name cannot exceed 100 characters!" + #LF$ 
    EndIf 

    ;Unique Check FIELD ==> Galaxy
    If *Class\State = DB::#New  
      DBID = DB::Open(DBTYPE::#Sqlite)
      SetDatabaseString(DBID, 0, *Class\Name)  
      Sql$ = " Select "
      Sql$ = Sql$ + " * "
      Sql$ = Sql$ + " FROM "
      Sql$ = Sql$ + "Galaxy "
      Sql$ = Sql$ + " WHERE "
      Sql$ = Sql$ + "Name = ? "

      If DatabaseQuery(DBID, Sql$)  
        While NextDatabaseRow(DBID) 
          PassFail = #False  
          ErrMsg = ErrMsg + "Name must be unique!" + #LF$  
        Wend  
       EndIf  
      DB::Close(DBID)  
    Else
      DBID = DB::Open(DBTYPE::#Sqlite)
      SetDatabaseString(DBID, 0, *Class\Name) 
      SetDatabaseLong(DBID, 1, *Class\ID) 
      Sql$ = "Select "
      Sql$ = Sql$ + " * "
      Sql$ = Sql$ + " FROM "
      Sql$ = Sql$ + "Galaxy "
      Sql$ = Sql$ + " WHERE "
      Sql$ = Sql$ + "Name = ? "
      Sql$ = Sql$ + " And "
      Sql$ = Sql$ + "ID <> ? "
      If DatabaseQuery(DBID, Sql$) 
        While NextDatabaseRow(DBID) 
          PassFail = #False  
          ErrMsg = ErrMsg + "Name must be unique!" + #LF$  
        Wend 
      EndIf 
      DB::Close(DBID)  
    EndIf  


    If PassFail = #False 
      THEMEDMESSAGE::MessageBox("Validation Error",ErrMsg) 
    EndIf	

    ProcedureReturn PassFail 

  EndProcedure


  Procedure.i DeleteValidate(*Class.Galaxy)
    Protected DBID, Sql$, Cnt, PassFail, ErrMsg.s 
    PassFail.i = #True 
    ErrMsg.s = ""
    ;DBID = DB::Open(DBTYPE::#Sqlite)

    ;Sql$ = "SELECT 
    ;Sql$ = Sql$ + "COUNT(*) As Count "
    ;Sql$ = Sql$ + "FROM "
    ;Sql$ = Sql$ + "DBField "
    ;Sql$ = Sql$ + "WHERE "
    ;Sql$ = Sql$ + "ClassID = ? "

    ;If DB::Query(DBID, Sql$) 
      ;While NextDatabaseRow(DBID)  
        ;Cnt.c =  GetDatabaseLong(DBID, 0)  
        ;If Cnt > 0  
          ;PassFail= #False  
          ;ErrMsg= "Project has Fields, Delete them first!"
        ;EndIf  
      ;Wend  
    ;EndIf
    ;DB::Close(DBID) 

    If PassFail= #False
      THEMEDMESSAGE::MessageBox("Error", ErrMsg) 
    EndIf

    ProcedureReturn PassFail 

  EndProcedure

EndModule


Comments

Post a Comment

Popular posts from this blog

How to build this without writing Code

I'm not sure if I should do this now, because I have not explained a number of additional aspects to the code, but for those of you who are interested.  The foundation of what I shown you here, IS the foundation of code my PBXClassBuilder-Standard generates for you.  And I truly mean generates  for you. You can have all this code generated for you without writing a single line of it yourself. Now PBXClassBuilder-Standard is not perfect, it is approximately 90=95% accurate. And I am constantly tweaking it edging this number ever higher. To use PBXClassBuilder-Standard, You should start with Forms already laid out and built.  Within PBXClassBuilder-Standard you use intuitive screens to define your table structure, while linking the Table Fields to their intended form Fields.  Then link the Tables together as Parent Child, and finally you simply click buttons to generate the code, Including the SQL Code to create the tables. The Whole development process, post For...
Read more

Connecting the Forms to the Data-Layer (part 9)

Here is the whole View Code Sheet Define quitfrmSolarSystem Declare SolarSystemCustom() Declare SolarSystemSetScreen() Declare SolarSystemCreate() Declare SolarSystemModify() Declare SolarSystemDelete() Declare SolarSystemSetData() Declare SolarSystemSave() Declare SolarSystemClose() Procedure SolarSystemSetScreen() Protected String$, x, y, w, h, i, ID Shared WorkSolarSystem Protected EventID, MenuID, GadgetID, WindowID, Index Shared quitfrmSolarSystem quitfrmSolarSystem = #False If Window_frmSolarSystem() SolarSystemCustom() SetActiveWindow(#Window_frmSolarSystem) ;Set Screen Data SetGadgetText(#Gadget_frmSolarSystem_txtName, WorkSolarSystem\Name) SetGadgetText(#Gadget_frmSolarSystem_txtGrow, Str(WorkSolarSystem\GRow)) SetGadgetText(#Gadget_frmSolarSystem_txtGCol, Str(WorkSolarSystem\GCol)) SetActiveGadget(#Gadget_frmSolarSystem_txtName) Repeat EventID =WaitWindowEvent() MenuID =EventMenu() GadgetID =EventG...
Read more

Connecting the Forms to the Data-Layer (part 8)

 So now we are down to just the SolarSystem Code Sheet. This is NOT a rubberstamp of the Galaxy. The MAIN reason is that the Galaxy Records are Sourced directly from the Database, As you will recall our SolarSystem is Sourced from the Galaxy Record's Solar System Collection NOT the Database. This makes for a VERY Significant difference between in the code. Our Procedure are the save ALL the Steps are the same, the differences are in the details. Starting once again at the top, we have our declares: Define quitfrmSolarSystem Declare SolarSystemCustom() Declare SolarSystemSetScreen() Declare SolarSystemCreate() Declare SolarSystemModify() Declare SolarSystemDelete() Declare SolarSystemSetData() Declare SolarSystemSave() Declare SolarSystemClose() And then, as before the SetScreen Procedure, and Control-Loop. Procedure SolarSystemSetScreen() Protected String$, x, y, w, h, i, ID Shared WorkSolarSystem Protected EventID, MenuID, GadgetID, WindowID, Index Shared quitfrm...
Read more