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.
Analysing Aussie ACMA amateur database help [Solved]
-
Fangbeast
- PureBasic Protozoa
- Posts: 4792
- Joined: Fri Apr 25, 2003 3:08 pm
- Location: Not Sydney!!! (Bad water, no goats)
Analysing Aussie ACMA amateur database help [Solved]
Last edited by Fangbeast on Thu Sep 24, 2020 12:49 am, edited 1 time in total.
Amateur Radio/VK3HAF, (D-STAR/DMR and more), Arduino, ESP32, Coding, Crochet
-
Fangbeast
- PureBasic Protozoa
- Posts: 4792
- Joined: Fri Apr 25, 2003 3:08 pm
- Location: Not Sydney!!! (Bad water, no goats)
Re: Analysing Aussie ACMA amateur database help
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
Amateur Radio/VK3HAF, (D-STAR/DMR and more), Arduino, ESP32, Coding, Crochet
-
Fangbeast
- PureBasic Protozoa
- Posts: 4792
- Joined: Fri Apr 25, 2003 3:08 pm
- Location: Not Sydney!!! (Bad water, no goats)
Re: Analysing Aussie ACMA amateur database help [Solved]
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
Amateur Radio/VK3HAF, (D-STAR/DMR and more), Arduino, ESP32, Coding, Crochet
Re: Analysing Aussie ACMA amateur database help [Solved]
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 
▓▓▓▓▓▒▒▒▒▒░░░░░
-
Fangbeast
- PureBasic Protozoa
- Posts: 4792
- Joined: Fri Apr 25, 2003 3:08 pm
- Location: Not Sydney!!! (Bad water, no goats)
Re: Analysing Aussie ACMA amateur database help [Solved]
First of all...WOOF!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
:)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:):)
Amateur Radio/VK3HAF, (D-STAR/DMR and more), Arduino, ESP32, Coding, Crochet
Re: Analysing Aussie ACMA amateur database help [Solved]
That's some nice steaming pile of crap you coded there Fangles 
Hows the ferrets and the sheeps doing over there?
Regards
Hows the ferrets and the sheeps doing over there?
Regards
-
Fangbeast
- PureBasic Protozoa
- Posts: 4792
- Joined: Fri Apr 25, 2003 3:08 pm
- Location: Not Sydney!!! (Bad water, no goats)
Re: Analysing Aussie ACMA amateur database help [Solved]
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.That's some nice steaming pile of crap you coded there Fangles
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!Hows the ferrets and the sheeps doing over there?
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!!)Regards
Amateur Radio/VK3HAF, (D-STAR/DMR and more), Arduino, ESP32, Coding, Crochet