PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

Analysing Aussie ACMA amateur database help [Solved]

https://www.purebasic.fr/english/viewtopic.php?t=75977

Page 1 of 1

Analysing Aussie ACMA amateur database help [Solved]

Posted: Tue Sep 22, 2020 8:20 am
by Fangbeast
Oz only. I constantly update my ACMA amateur database and can run basic queries against it (Now up to 1.7 gig) but buggered if I can find a relationship against the various tables.

table: client contains the client address information
table: device_details contains the station callsign (all I want from that table for now)

There are no foreign keys on the entire database and I don't know which tables tie the two together so that I could do a join of some sort for the two tables.

Anyone worked this out yet? I'm stuffed if I know.

Re: Analysing Aussie ACMA amateur database help

Posted: Wed Sep 23, 2020 2:09 pm
by Berikco
:shock:

Re: Analysing Aussie ACMA amateur database help

Posted: Wed Sep 23, 2020 11:31 pm
by Fangbeast
Actually, some kind person told me the link table so I came up with this steaming pile of crap as a first start for the person who wanted it anyway and it does what I want to my satisfaction until the original asker wants more.

Code: Select all

; //  Check if doofus has turned thread safety on //

CompilerIf Not #PB_Compiler_Thread                                                                                                    ; Check to see if thread safe environment is running
  CompilerError "Enable ThreadSafe!"                                                                                                  ; Warn the user that thread safe is not enabled
CompilerEndIf                                                                                                                         ; End of this conditional test

; //    //

UseSQLiteDatabase()                                                                                                                    ; 

; // PureVision created //

Define EventID,MenuID,GadgetID,WindowID

Enumeration 1
  #Window_Acquery
EndEnumeration

#WindowIndex = #PB_Compiler_EnumerationValue

Enumeration 1
  #Gadget_Acquery_Resultslist
  #Gadget_Acquery_Opendatabase
  #Gadget_Acquery_lQuerybox
  #Gadget_Acquery_Querybox
  #Gadget_Acquery_Exitthisprogram
  #Gadget_Acquery_Errors
EndEnumeration

#GadgetIndex = #PB_Compiler_EnumerationValue

Procedure.i Window_Acquery()
  If OpenWindow(#Window_Acquery, 0, 0, 1100, 805, "ACMA data query,  basic", #PB_Window_SystemMenu|#PB_Window_ScreenCentered|#PB_Window_Invisible)
      EditorGadget(#Gadget_Acquery_Resultslist, 5, 5, 1090, 730)
        SetGadgetFont(#Gadget_Acquery_Resultslist, LoadFont(#Gadget_Acquery_Resultslist, "Consolas", 10))
      ButtonGadget(#Gadget_Acquery_Opendatabase, 5, 740, 260, 30, "Open the ACMA database")
        SetGadgetFont(#Gadget_Acquery_Opendatabase, LoadFont(#Gadget_Acquery_Opendatabase, "Consolas", 10))
      TextGadget(#Gadget_Acquery_lQuerybox, 275, 740, 210, 30, "Type query here -->")
        SetGadgetFont(#Gadget_Acquery_lQuerybox, LoadFont(#Gadget_Acquery_lQuerybox, "Consolas", 12))
      StringGadget(#Gadget_Acquery_Querybox, 495, 740, 330, 30, "", #PB_String_BorderLess)
        SetGadgetFont(#Gadget_Acquery_Querybox, LoadFont(#Gadget_Acquery_Querybox, "Consolas", 12))
      ButtonGadget(#Gadget_Acquery_Exitthisprogram, 835, 740, 260, 30, "Exit this program")
        SetGadgetFont(#Gadget_Acquery_Exitthisprogram, LoadFont(#Gadget_Acquery_Exitthisprogram, "Consolas", 10))
      StringGadget(#Gadget_Acquery_Errors, 5, 775, 1090, 25, "", #PB_String_ReadOnly|#PB_String_BorderLess)
        SetGadgetFont(#Gadget_Acquery_Errors, LoadFont(#Gadget_Acquery_Errors, "Consolas", 10))
      HideWindow(#Window_Acquery, #False)
    ProcedureReturn WindowID(#Window_Acquery)
  EndIf
EndProcedure

; //  //

Macro Addline()                                                                                                                       ;
  AddGadgetItem(#Gadget_Acquery_Resultslist, -1,  "----------------------------------------------------------------------------------------------------")
EndMacro                                                                                                                              ; 

; //  Extra events, shortcuts and menu events that I need //

Enumeration #GadgetIndex                                                                                                              ; 
  #Shortcut_StartQuery                                                                                                                 ; 
EndEnumeration                                                                                                                        ; 

; //  //

Structure Programdata                                                                                                                 ; 
  CurrentDirectory.s                                                                                                                   ; 
  
  DatabaseName.s                                                                                                                       ; 
  SmallName.s                                                                                                                          ; 
  DatabaseHandle.i                                                                                                                     ; 
  DatabaseExists.i                                                                                                                     ; 
  
  QueryString.s                                                                                                                        ; 
  UpdateString.s                                                                                                                       ; 
  
  ProgramQuit.i                                                                                                                        ; 
EndStructure                                                                                                                          ; 

; //  //

Global    Program.Programdata                                                                                                         ; 

; //  Routines designed specifically for program management  //

Declare   OpenAcmaDatabase()                                                                                                         ; 
Declare   RunAcmaQuery()                                                                                                             ; 

; //  //

Program\CurrentDirectory.s    = GetCurrentDirectory()                                                                                 ; 

Program\SmallName.s           = #Empty$                                                                                               ;
Program\DatabaseName.s        = #Empty$                                                                                               ;
Program\DatabaseHandle.i      = #False                                                                                                ; 

Program\QueryString.s         = #Empty$                                                                                               ; 
Program\UpdateString.s        = #Empty$                                                                                               ; 

Program\DatabaseExists.i      = #False                                                                                                ; 

; //  //

Procedure OpenAcmaDatabase()                                                                                                         ; 
  
  Program\DatabaseName.s = OpenFileRequester("Open database", #Empty$, "Sqlite (*.sqlite)|*.sqlite", 0)
  
  Program\SmallName.s = GetFilePart(Program\DatabaseName.s)
  
  If Program\DatabaseName.s <> #Empty$
    
    Program\DatabaseExists.i = FileSize(Program\DatabaseName.s)
    
    If Program\DatabaseExists.i <>  -1 Or  Program\DatabaseExists.i  <> -2
      
      AddGadgetItem(#Gadget_Acquery_Resultslist, -1, Program\SmallName.s + " was found, proceeding with opening the database")
      
      Addline()
      
      Program\DatabaseHandle.i  = OpenDatabase(#PB_Any, Program\DatabaseName.s, #Empty$, #Empty$, #PB_Database_SQLite)
      
      If Program\DatabaseHandle.i <> #False 
        
        AddGadgetItem(#Gadget_Acquery_Resultslist, -1, Program\SmallName.s + " was opened, queries will now work")
        
        Addline()
        
      Else
        
        MessageRequester("Database", Program\SmallName.s + "' failed To be opened, queries will not work", #PB_MessageRequester_Ok)
        
      EndIf  
      
    Else
      
      MessageRequester("Database missing", "The database you selected has been deleted by the system in the meantime", #PB_MessageRequester_Ok)
      
    EndIf
    
  Else
    
    MessageRequester("open cancelled", "User cancelled, queries will not work", #PB_MessageRequester_Ok)
    
  EndIf
  
EndProcedure

; //  //

Procedure RunAcmaQuery()                                                                                                             ; 
  
  If Program\DatabaseHandle.i
    
    ClearGadgetItems(#Gadget_Acquery_Resultslist)
    
    QueryText.s = GetGadgetText(#Gadget_Acquery_Querybox)
    
    If QueryText.s  <> #Empty$
      
      CurrentQuery.s = "SELECT LICENCE_NO, CALL_SIGN FROM device_details WHERE CALL_SIGN LIKE '%" + QueryText.s + "%'"
      
      If DatabaseQuery(Program\DatabaseHandle.i, CurrentQuery.s) <> #False
        
        While NextDatabaseRow(Program\DatabaseHandle.i)
          
          LICENCE_NO.s = GetDatabaseString(Program\DatabaseHandle.i, 0)
          
          CALL_SIGN.s  = GetDatabaseString(Program\DatabaseHandle.i, 1)
          
          AddGadgetItem(#Gadget_Acquery_Resultslist, -1,  "Requested license number found ::  " + LICENCE_NO.s)
          
          AddGadgetItem(#Gadget_Acquery_Resultslist, -1,  "Call sign found for this license:: " + CALL_SIGN.s)
          
          AddLine()
          
        Wend
        
        FinishDatabaseQuery(Program\DatabaseHandle.i)
        
        If LICENCE_NO.s <> #Empty$
          
          CurrentQuery.s  = "SELECT CLIENT_NO FROM licence WHERE LICENCE_NO = '" + LICENCE_NO.s + "'"
          
          If DatabaseQuery(Program\DatabaseHandle.i, CurrentQuery.s) <> #False
            
            While NextDatabaseRow(Program\DatabaseHandle.i)
              
              CLIENT_NO.s   = GetDatabaseString(Program\DatabaseHandle.i, 0)
              
              AddGadgetItem(#Gadget_Acquery_Resultslist, -1,  "Client number found in licence index:: " + CLIENT_NO.s)
              
              AddLine()
              
            Wend
            
            FinishDatabaseQuery(Program\DatabaseHandle.i)
            
            If CLIENT_NO.s <> #Empty$
              
              CurrentQuery.s = "SELECT * FROM client WHERE CLIENT_NO = '" + CLIENT_NO.s  + "'"
              
              If DatabaseQuery(Program\DatabaseHandle.i, CurrentQuery.s)  <> #False
                
                NumberOfCOlumns.i = DatabaseColumns(Program\DatabaseHandle.i)
                
                While NextDatabaseRow(Program\DatabaseHandle.i)
                  
                  For ColumnNamesLoop.i = 0 To NumberOfColumns.i - 1
                    
                    AddGadgetItem(#Gadget_Acquery_Resultslist, -1,  DatabaseColumnName(Program\DatabaseHandle.i, ColumnNamesLoop.i)  + " ::  " + GetDatabaseString(Program\DatabaseHandle.i,  ColumnNamesLoop.i))
                    
                  Next ColumnNamesLoop.i
                
                Wend
              
                FinishDatabaseQuery(Program\DatabaseHandle.i)
                
                AddLine()
                
              Else
                
                AddGadgetItem(#Gadget_Acquery_Resultslist, -1,   "Database query failed or was empty:: " + DatabaseError() + "  ::  " + CurrentQuery.s)
                
              EndIf
              
            Else
              
              AddGadgetItem(#Gadget_Acquery_Resultslist, -1,   "No matching data found for that callsign")

            EndIf
            
          Else
            
            AddGadgetItem(#Gadget_Acquery_Resultslist, -1,   "Database query failed or was empty:: " + DatabaseError() + "  ::  " + CurrentQuery.s)
            
          EndIf
          
        Else
          
          AddGadgetItem(#Gadget_Acquery_Resultslist, -1,   "No callsign matching that description was found")
          
        EndIf
        
      Else
        
        AddGadgetItem(#Gadget_Acquery_Resultslist, -1,   "Database query failed or was empty:: " + DatabaseError() + "  ::  " + CurrentQuery.s)
        
      EndIf
      
    Else
      
      AddGadgetItem(#Gadget_Acquery_Resultslist, -1,   "No text entered, nothing to find")
      
    EndIf
    
  Else
    
    AddGadgetItem(#Gadget_Acquery_Resultslist, -1,   "Nothing to do, database is not open")
    
  EndIf
  
EndProcedure

; //  //

If Window_Acquery()
  
  AddKeyboardShortcut(#Window_Acquery,  #PB_Shortcut_Return,  #Shortcut_StartQuery)                                                   ; 
  
  Repeat
    EventID  = WaitWindowEvent()
    MenuID   = EventMenu()
    GadgetID = EventGadget()
    WindowID = EventWindow()
    Select EventID
      Case #PB_Event_CloseWindow
        Select WindowID
          Case #Window_Acquery                  : Program\ProgramQuit.i = #True
        EndSelect
      Case #PB_Event_Menu
        Select menuid
          Case #Shortcut_StartQuery             : RunAcmaQuery()
        EndSelect
      Case #PB_Event_Gadget
        Select GadgetID
          Case #Gadget_Acquery_Opendatabase     : OpenAcmaDatabase()
          Case #Gadget_Acquery_Exitthisprogram  : Program\ProgramQuit.i = #True
        EndSelect
    EndSelect
  Until Program\ProgramQuit.i = #True
  CloseDatabase(Program\DatabaseHandle.i)
  CloseWindow(#Window_Acquery)
EndIf
End

Re: Analysing Aussie ACMA amateur database help [Solved]

Posted: Tue Sep 29, 2020 6:44 am
by Fangbeast
This is what I used to aggregate my tables, cherry picking only the fields that I was interested in. The ACMA database went from 1.7GB to 52MB.

Code: Select all

; //  Check if doofus has turned thread safety on //

CompilerIf Not #PB_Compiler_Thread                                                                                                    ; Check to see if thread safe environment is running
  CompilerError "Enable ThreadSafe!"                                                                                                  ; Warn the user that thread safe is not enabled
CompilerEndIf                                                                                                                         ; End of this conditional test

UseSQLiteDatabase()                                                                                                                    ; 

Structure composite_data
  ; licence
  
  LICENCE_TYPE_NAME.s                                                                                                                  ; licence
  LICENCE_CATEGORY_NAME.s                                                                                                              ; licence
  DATE_ISSUED.s                                                                                                                        ; licence
  DATE_OF_EFFECT.s                                                                                                                     ; licence
  DATE_OF_EXPIRY.s                                                                                                                     ; licence
  STATUS.s                                                                                                                             ; licence
  STATUS_TEXT.s                                                                                                                        ; licence
  
  ; client
  
  LICENCEE.s                                                                                                                           ; client, Linked to licence CLIENT_NO
  TRADING_NAME.s                                                                                                                       ; client
  ACN.s                                                                                                                                ; client
  ABN.s                                                                                                                                ; client
  POSTAL_STREET.s                                                                                                                      ; client
  POSTAL_SUBURB.s                                                                                                                      ; client
  POSTAL_STATE.s                                                                                                                       ; client
  POSTAL_POSTCODE.s                                                                                                                    ; client
  
  ; device_details
  
  CALL_SIGN.s

EndStructure

Structure Programdata                                                                                                                 ; 
  CurrentDirectory.s                                                                                                                   ; 
  
  DatabaseName.s                                                                                                                       ; 
  DatabaseHandle.i                                                                                                                     ; 
  DatabaseExists.i                                                                                                                     ; 
  
  QueryString.s                                                                                                                        ; 
  UpdateString.s                                                                                                                       ; 
EndStructure                                                                                                                          ; 

Global          Program.Programdata                                                                                                   ; 

Program\CurrentDirectory.s    = GetCurrentDirectory()                                                                                 ; 
  
Program\DatabaseName.s        = "E:\ACMA amateur radio database.sqlite"                                                               ;
Program\DatabaseHandle.i      = #False                                                                                                ; 

Program\QueryString.s         = #Empty$                                                                                               ; 
Program\UpdateString.s        = #Empty$                                                                                               ; 

Program\DatabaseExists.i      = #False                                                                                                ; 

Global NewList Slimtable.composite_data()                                                                                            ; 

Program\DatabaseHandle.i = OpenDatabase(#PB_Any, Program\DatabaseName.s, #Empty$, #Empty$, #PB_Database_SQLite)

If Program\DatabaseHandle.i <>  #False
  
  CurrentQuery.s = "SELECT "        + 
                   "m.LICENCE_TYPE_NAME, "           + 
                   "m.LICENCE_CATEGORY_NAME, "       + 
                   "m.DATE_ISSUED, "                 + 
                   "m.DATE_OF_EFFECT, "              + 
                   "m.DATE_OF_EXPIRY, "              + 
                   "m.STATUS, "                      + 
                   "m.STATUS_TEXT, "                 + 
                   "n.LICENCEE, "                    + 
                   "n.TRADING_NAME, "                + 
                   "n.ACN, "                         + 
                   "n.ABN, "                         + 
                   "n.POSTAL_STREET, "               + 
                   "n.POSTAL_SUBURB, "               + 
                   "n.POSTAL_STATE, "                + 
                   "n.POSTAL_POSTCODE, "             + 
                   "c.CALL_SIGN "                    + 
                   "FROM licence AS m "              + 
                   "LEFT JOIN client AS n "          + 
                   "ON m.CLIENT_NO = n.CLIENT_NO "   + 
                   "LEFT JOIN device_details AS c "  + 
                   "ON m.LICENCE_NO = c.LICENCE_NO " + 
                   "WHERE c.CALL_SIGN NOT NULL"
  If DatabaseQuery(Program\DatabaseHandle.i, CurrentQuery.s) <> #False
    While NextDatabaseRow(Program\DatabaseHandle.i)
      AddElement(Slimtable())
      Slimtable()\LICENCE_TYPE_NAME.s     = GetDatabaseString(Program\DatabaseHandle.i,  0)
      Slimtable()\LICENCE_CATEGORY_NAME.s = GetDatabaseString(Program\DatabaseHandle.i,  1)
      Slimtable()\DATE_ISSUED.s           = GetDatabaseString(Program\DatabaseHandle.i,  2)
      Slimtable()\DATE_OF_EFFECT.s        = GetDatabaseString(Program\DatabaseHandle.i,  3)
      Slimtable()\DATE_OF_EXPIRY.s        = GetDatabaseString(Program\DatabaseHandle.i,  4)
      Slimtable()\STATUS.s                = GetDatabaseString(Program\DatabaseHandle.i,  5)
      Slimtable()\STATUS_TEXT.s           = GetDatabaseString(Program\DatabaseHandle.i,  6)
      Slimtable()\LICENCEE.s              = GetDatabaseString(Program\DatabaseHandle.i,  7)
      Slimtable()\TRADING_NAME.s          = GetDatabaseString(Program\DatabaseHandle.i,  8)
      Slimtable()\ACN.s                   = GetDatabaseString(Program\DatabaseHandle.i,  9)
      Slimtable()\ABN.s                   = GetDatabaseString(Program\DatabaseHandle.i, 10)
      Slimtable()\POSTAL_STREET.s         = GetDatabaseString(Program\DatabaseHandle.i, 11)
      Slimtable()\POSTAL_SUBURB.s         = GetDatabaseString(Program\DatabaseHandle.i, 12)
      Slimtable()\POSTAL_STATE.s          = GetDatabaseString(Program\DatabaseHandle.i, 13)
      Slimtable()\POSTAL_POSTCODE.s       = GetDatabaseString(Program\DatabaseHandle.i, 14)
      Slimtable()\CALL_SIGN.s             = GetDatabaseString(Program\DatabaseHandle.i, 15)
    Wend
    FinishDatabaseQuery(Program\DatabaseHandle.i)
  Else
    Debug "The database query failed or contained no results::  " + DatabaseError()
  EndIf
  
  If ListSize(Slimtable())
    
    CurrentUpdate.s = "CREATE TABLE composite("                     + 
                      "LICENCE_TYPE_NAME TEXT, "                    + 
                      "LICENCE_CATEGORY_NAME TEXT, "                + 
                      "DATE_ISSUED TEXT, "                          + 
                      "DATE_OF_EFFECT TEXT, "                       + 
                      "DATE_OF_EXPIRY TEXT, "                       + 
                      "STATUS TEXT, "                               + 
                      "STATUS_TEXT TEXT, "                          + 
                      "LICENCEE TEXT, "                             + 
                      "TRADING_NAME TEXT, "                         + 
                      "ACN TEXT, "                                  + 
                      "ABN TEXT, "                                  + 
                      "POSTAL_STREET TEXT, "                        + 
                      "POSTAL_SUBURB TEXT, "                        + 
                      "POSTAL_STATE TEXT, "                         + 
                      "POSTAL_POSTCODE TEXT, "                      + 
                      "CALL_SIGN TEXT, "                            + 
                      "Recordid INTEGER PRIMARY KEY AUTOINCREMENT)"

    If DatabaseUpdate(Program\DatabaseHandle.i, CurrentUpdate.s) <> #False

      DatabaseUpdate(Program\DatabaseHandle.i, "BEGIN TRANSACTION")

      ForEach Slimtable()

        SetDatabaseString(Program\DatabaseHandle.i,  0, Slimtable()\LICENCE_TYPE_NAME.s)
        SetDatabaseString(Program\DatabaseHandle.i,  1, Slimtable()\LICENCE_CATEGORY_NAME.s)
        SetDatabaseString(Program\DatabaseHandle.i,  2, Slimtable()\DATE_ISSUED.s)
        SetDatabaseString(Program\DatabaseHandle.i,  3, Slimtable()\DATE_OF_EFFECT.s)
        SetDatabaseString(Program\DatabaseHandle.i,  4, Slimtable()\DATE_OF_EXPIRY.s)
        SetDatabaseString(Program\DatabaseHandle.i,  5, Slimtable()\STATUS.s)
        SetDatabaseString(Program\DatabaseHandle.i,  6, Slimtable()\STATUS_TEXT.s)
        SetDatabaseString(Program\DatabaseHandle.i,  7, Slimtable()\LICENCEE.s)
        SetDatabaseString(Program\DatabaseHandle.i,  8, Slimtable()\TRADING_NAME.s)
        SetDatabaseString(Program\DatabaseHandle.i,  9, Slimtable()\ACN.s)
        SetDatabaseString(Program\DatabaseHandle.i, 10, Slimtable()\ABN.s)
        SetDatabaseString(Program\DatabaseHandle.i, 11, Slimtable()\POSTAL_STREET.s)
        SetDatabaseString(Program\DatabaseHandle.i, 12, Slimtable()\POSTAL_SUBURB.s)
        SetDatabaseString(Program\DatabaseHandle.i, 13, Slimtable()\POSTAL_STATE.s)
        SetDatabaseString(Program\DatabaseHandle.i, 14, Slimtable()\POSTAL_POSTCODE.s)
        SetDatabaseString(Program\DatabaseHandle.i, 15, Slimtable()\CALL_SIGN.s)

        CurrentInsert.s = "INSERT INTO composite("  + 
                          "LICENCE_TYPE_NAME, "                       + 
                          "LICENCE_CATEGORY_NAME, "                   + 
                          "DATE_ISSUED, "                             + 
                          "DATE_OF_EFFECT, "                          + 
                          "DATE_OF_EXPIRY, "                          + 
                          "STATUS, "                                  + 
                          "STATUS_TEXT, "                             + 
                          "LICENCEE, "                                + 
                          "TRADING_NAME, "                            + 
                          "ACN, "                                     + 
                          "ABN, "                                     + 
                          "POSTAL_STREET, "                           + 
                          "POSTAL_SUBURB, "                           + 
                          "POSTAL_STATE, "                            + 
                          "POSTAL_POSTCODE, "                         + 
                          "CALL_SIGN) "                               + 
                          "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        If DatabaseUpdate(Program\DatabaseHandle.i, CurrentInsert.s) <> #False
          Debug "Record added to table composite:: " + Slimtable()\CALL_SIGN.s
        Else
          Debug "The new record failed to be added to the composite table:: " + DatabaseError()
        EndIf

      Next Slimtable()

      DatabaseUpdate(Program\DatabaseHandle.i, "COMMIT")

      ; Let's drop the un-needed tables now

      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE access_area")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE access_area_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE antenna")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE antenna_pattern")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE antenna_pattern_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE antenna_polarity")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE antenna_polarity_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE antenna_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE applic_text_block")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE applic_text_block_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE auth_spectrum_area")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE auth_spectrum_area_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE auth_spectrum_freq")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE auth_spectrum_freq_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE bsl")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE bsl_area")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE bsl_area_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE bsl_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE class_of_station")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE class_of_station_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE client")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE client_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE client_type")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE client_type_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE device_details")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE device_details_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE fee_status")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE fee_status_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE industry_cat")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE industry_cat_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE licence")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE licence_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE licence_service")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE licence_service_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE licence_status")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE licence_status_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE licence_subservice")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE licence_subservice_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE licensing_area")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE licensing_area_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE MAP_TILE_STORE")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE nature_of_service")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE nature_of_service_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE reports_text_block")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE reports_text_block_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE satellite")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE satellite_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE saved_queries")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE site")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE site_raw")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE tmp_site_asl")
      DatabaseUpdate(Program\DatabaseHandle.i, "DROP TABLE __WebKitDatabaseInfoTable__")

      DatabaseUpdate(Program\DatabaseHandle.i, "VACUUM")

    Else
      Debug "The database table 'composite' failed to be created"
    EndIf

  Else
    Debug "No Callsigns were found in the ACMA database, programmer error"
  EndIf

Else
  Debug "The requested database failed to be opened"
EndIf

End

Re: Analysing Aussie ACMA amateur database help [Solved]

Posted: Tue Sep 29, 2020 1:13 pm
by Nituvious
oh you poor, poor beast. my last nerd job involved a 800gb database that didn't use foreign keys when it should and could have. I can't help you myself, but just wanted to point at laugh at your pain. sorry :mrgreen:

Re: Analysing Aussie ACMA amateur database help [Solved]

Posted: Wed Sep 30, 2020 1:32 am
by Fangbeast
Nituvious wrote:oh you poor, poor beast. my last nerd job involved a 800gb database that didn't use foreign keys when it should and could have. I can't help you myself, but just wanted to point at laugh at your pain. sorry :mrgreen:
First of all...WOOF! :):)

No pain. ACMA obviously didn't want to make it easy except on their web site and their online examples studiously pointed away from some of the more standard information that we might want to get such as the callsign itself.

Not to worry, I don't need help with this, all worked out. I can either give this code to people who want to slim down the database as I am not allowed to distribute it myself or the code to do callsign (and associated data) on the full 1.7gb database itself.

Going to do that bit today in between headaches:):)

Re: Analysing Aussie ACMA amateur database help [Solved]

Posted: Wed Sep 30, 2020 3:05 pm
by Berikco
That's some nice steaming pile of crap you coded there Fangles :mrgreen: :lol:

Hows the ferrets and the sheeps doing over there? :D

Regards

Re: Analysing Aussie ACMA amateur database help [Solved]

Posted: Thu Oct 01, 2020 12:07 am
by Fangbeast
That's some nice steaming pile of crap you coded there Fangles :mrgreen:
You know how much I love my Great Steaming Piles of Crap don't you Benny?? My eyes tear up, my buttocks shake and I go crosseyed with happiness.
Hows the ferrets and the sheeps doing over there? :D
They were okay until I found out that you had stolen them first, shamefully abused them (And I didn't know some of those positions were possible GASP!) and re-coloured their giblets and shaved their pompoms damn it. Now I can hardly abu...I mean love them myself! Damn you Benny!
Regards
Regards/? After what you did to my stock??? I want compensation!! I want fresh sheep and ferrent, unmarked by you (at least tidy their fur up and put the little silken pink scarf on the girl ferret!!)
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited