402 lines
12 KiB
OpenEdge ABL
402 lines
12 KiB
OpenEdge ABL
::requires 'ooSQLite.cls'
|
|
::requires "rxunixsys" LIBRARY
|
|
::requires 'ncurses.cls'
|
|
::requires 'app/utils.rex'
|
|
|
|
::CLASS AddressBookDB PUBLIC
|
|
|
|
::method init
|
|
expose db
|
|
|
|
/* Create database directory if it doesn't exist */
|
|
if SysFileExists(.dbFile) == .false then DO
|
|
SAY "Initializing new address book"
|
|
db = .ooSQLiteConnection~new(.dbFile)
|
|
self~createTables
|
|
END
|
|
Else Do
|
|
Say "Initializing existing address book"
|
|
db = .ooSQLiteConnection~new(.dbFile,.ooSQLite~OPEN_READWRITE)
|
|
End
|
|
return
|
|
|
|
::METHOD getFileName
|
|
expose db
|
|
return db~fileName()
|
|
|
|
::METHOD closeDb
|
|
expose db
|
|
db~Close()
|
|
RETURN
|
|
|
|
::METHOD createTables
|
|
expose db
|
|
|
|
/* Contacts table */
|
|
db~exec("CREATE TABLE IF NOT EXISTS contacts ("||,
|
|
"id INTEGER PRIMARY KEY AUTOINCREMENT,"||,
|
|
"first_name TEXT,"||,
|
|
"last_name TEXT,"||,
|
|
"created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"||,
|
|
"updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"||,
|
|
")")
|
|
|
|
/* Phone numbers table */
|
|
db~exec("CREATE TABLE IF NOT EXISTS phone_numbers ("||,
|
|
"id INTEGER PRIMARY KEY AUTOINCREMENT,"||,
|
|
"contact_id INTEGER,"||,
|
|
"type TEXT,"||, -- home, work, mobile, etc.
|
|
"number TEXT,"||,
|
|
"FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"||,
|
|
")")
|
|
|
|
/* Email addresses table */
|
|
db~exec("CREATE TABLE IF NOT EXISTS email_addresses ("||,
|
|
"id INTEGER PRIMARY KEY AUTOINCREMENT,"||,
|
|
"contact_id INTEGER,"||,
|
|
"type TEXT,"||, -- home, work, etc.
|
|
"email TEXT,"||,
|
|
"FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"||,
|
|
")")
|
|
|
|
/* Physical addresses table */
|
|
db~exec("CREATE TABLE IF NOT EXISTS addresses ("||,
|
|
"id INTEGER PRIMARY KEY AUTOINCREMENT,"||,
|
|
"contact_id INTEGER,"||,
|
|
"type TEXT,"||, -- home, work, etc.
|
|
"street TEXT,"||,
|
|
"city TEXT,"||,
|
|
"state TEXT,"||,
|
|
"postal_code TEXT,"||,
|
|
"country TEXT,"||,
|
|
"FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE"||,
|
|
")")
|
|
return
|
|
|
|
/* Contact CRUD Operations */
|
|
::METHOD addContact
|
|
expose db
|
|
use arg contactDict /* Use a Rexx 'directory' to pass multiple values around */
|
|
sql = "INSERT INTO contacts (first_name, last_name) VALUES ('"contactDict~First_Name"', '"contactDict~Last_Name"')"
|
|
rc = db~exec(sql)
|
|
if rc \= 0 then do
|
|
say "Error adding contact:" db~errMsg()
|
|
return -1
|
|
end
|
|
contactId = db~lastInsertRowId() /* the row id of 'contacts' table is the master id */
|
|
|
|
self~addPhoneNumber(contactId, contactDict["PHONE_TYPE"], contactDict["PHONE_NUMBER"])
|
|
self~addEmailAddress(contactId, contactDict["EMAIL_TYPE"], contactDict["EMAIL_ADDRESS"])
|
|
/* contactId, addressType, street, city, state, postalCode, country */
|
|
self~addRealAddress(contactId, "NA", contactDict~street, contactDict~city, contactDict~state, contactDict~postCode, "NA")
|
|
return contactId
|
|
|
|
::METHOD getContact
|
|
expose db
|
|
use arg contactId
|
|
returnedContent = .Directory~new()
|
|
sql1 = "SELECT * FROM contacts WHERE id = "contactId
|
|
contacts = db~exec(sql1,.true,.ooSQLite~OO_ARRAY_OF_DIRECTORIES)
|
|
/* The result will be rendered as an array of Rexx 'directories'
|
|
* which are basically analogous to python dictionaries. */
|
|
|
|
if contacts = .nil then DO
|
|
Say 'NO CONTACTS FOUND'
|
|
return
|
|
END
|
|
if contacts~size < 1 then do
|
|
Say 'NO CONTACT FOUND'
|
|
return
|
|
END
|
|
return contacts[1] /* Rexx is 1-indexed */
|
|
|
|
/***************************
|
|
* GET ALL CONTACT INFO *
|
|
***************************/
|
|
::METHOD getAllContacts
|
|
expose db
|
|
sql = "SELECT * FROM contacts ORDER BY last_name, first_name"
|
|
contacts = db~exec(sql, .true, .ooSQLite~OO_ARRAY_OF_DIRECTORIES)
|
|
|
|
do contact over contacts
|
|
/* Get Phone Numbers */
|
|
psql = "SELECT * FROM phone_numbers WHERE contact_id = "contact["ID"]
|
|
phones = db~exec(psql,.true,.ooSQLite~OO_ARRAY_OF_DIRECTORIES)
|
|
phone = phones[1] /* just grab the first phone */
|
|
contact["PHONE_NUMBER"] = phone["NUMBER"]
|
|
contact["PHONE_TYPE"] = phone["TYPE"]
|
|
|
|
/* Now get email addresses */
|
|
esql = "SELECT * FROM email_addresses WHERE contact_id = "contact["ID"]
|
|
emails = db~exec(esql,.true,.ooSQLite~OO_ARRAY_OF_DIRECTORIES)
|
|
email = emails[1]
|
|
contact["EMAIL_ADDRESS"] = email["EMAIL"]
|
|
contact["EMAIL_TYPE"] = email["TYPE"]
|
|
END
|
|
return contacts
|
|
|
|
::METHOD searchContacts
|
|
expose db
|
|
use arg searchTerm
|
|
|
|
contactsList = .Array~new()
|
|
|
|
sql = "SELECT id FROM contacts WHERE first_name LIKE '%"searchTerm"%' OR last_name LIKE '%"searchTerm"%' ORDER BY last_name, first_name"
|
|
contactIds = db~exec(sql, .true, .ooSQLite~OO_ARRAY_OF_DIRECTORIES)
|
|
|
|
do contactDir over contactIds
|
|
contactId = contactDir["ID"]
|
|
contact = self~getContact(contactId)
|
|
if contact \= .nil then do
|
|
/* Get Phone Numbers */
|
|
psql = "SELECT * FROM phone_numbers WHERE contact_id = "contact["ID"]
|
|
phones = db~exec(psql,.true,.ooSQLite~OO_ARRAY_OF_DIRECTORIES)
|
|
phone = phones[1] /* just grab the first phone */
|
|
contact["PHONE_NUMBER"] = phone["NUMBER"]
|
|
contact["PHONE_TYPE"] = phone["TYPE"]
|
|
|
|
/* Now get email addresses */
|
|
esql = "SELECT * FROM email_addresses WHERE contact_id = "contact["ID"]
|
|
emails = db~exec(esql,.true,.ooSQLite~OO_ARRAY_OF_DIRECTORIES)
|
|
email = emails[1]
|
|
contact["EMAIL_ADDRESS"] = email["EMAIL"]
|
|
contact["EMAIL_TYPE"] = email["TYPE"]
|
|
|
|
contactsList~append(contact)
|
|
end /* add contact details */
|
|
end
|
|
return contactsList
|
|
|
|
/***********************
|
|
* UPDATE CONTACT *
|
|
***********************/
|
|
::METHOD updateContact
|
|
expose db
|
|
use arg contactId, firstName, lastName
|
|
sql = "UPDATE contacts SET first_name = '"firstName"', last_name = '"lastName"' WHERE id = "contactId
|
|
rc = db~exec(sql)
|
|
if rc \= 0 then do
|
|
say "Error updating contact:" db~errMsg()
|
|
return -1
|
|
end
|
|
return rc
|
|
|
|
/**************************
|
|
* DELETE CONTACT *
|
|
*************************/
|
|
::METHOD deleteContact
|
|
expose db
|
|
use arg contactId
|
|
|
|
/* Delete all related records first (assuming foreign key constraints) */
|
|
self~removeContactPhones(contactId)
|
|
self~removeContactEmails(contactId)
|
|
self~removeContactAddresses(contactId)
|
|
|
|
/* Delete the contact record */
|
|
sql = "DELETE FROM contacts WHERE id = "contactId
|
|
rc = db~exec(sql)
|
|
|
|
if rc \= 0 then do
|
|
say "Error deleting contact:" db~errMsg()
|
|
return -1
|
|
end
|
|
return 0
|
|
|
|
::METHOD getPhoneNumbers
|
|
expose db
|
|
use arg contactId
|
|
|
|
phones = .Array~new
|
|
sql = "SELECT id, type, number FROM phone_numbers WHERE contact_id = "contactId
|
|
phones = db~exec(sql,.true, .ooSQLite~OO_ARRAY_OF_DIRECTORIES)
|
|
|
|
return phones
|
|
|
|
::METHOD addPhoneNumber
|
|
expose db
|
|
use arg contactId, phoneType, phoneNumber
|
|
|
|
if phoneType = .nil | phoneNumber = .nil then return 0
|
|
|
|
sql = "INSERT INTO phone_numbers (contact_id, type, number) VALUES ("contactId", '"phoneType"', '"phoneNumber"')"
|
|
rc = db~exec(sql)
|
|
if rc \= 0 then DO
|
|
Say "Unable to insert phone number for contact id "contactId
|
|
return rc
|
|
END
|
|
phone_id = db~lastInsertRowId()
|
|
return phone_id
|
|
|
|
::METHOD updatePhoneNumber
|
|
expose db
|
|
use arg phoneId, phoneType, phoneNumber
|
|
|
|
sql = "UPDATE phone_numbers SET type = '"phoneType"', number = '"phoneNumber"' WHERE id = "phoneId
|
|
rc = db~exec(sql)
|
|
if rc \= 0 then DO
|
|
Say "Unable to update phone number or type for phone id "contactId
|
|
return rc
|
|
END
|
|
|
|
return rc
|
|
|
|
::METHOD deletePhoneNumber
|
|
expose db
|
|
use arg phoneId
|
|
sql = "DELETE FROM phone_numbers WHERE id = "phoneId
|
|
rc = db~exec(sql)
|
|
if rc \= 0 then DO
|
|
Say "Unable to delete phone id "phoneId
|
|
return rc
|
|
END
|
|
return rc
|
|
|
|
/* Email address operations */
|
|
::METHOD addEmailAddress
|
|
expose db
|
|
use arg contactId, emailType, emailAddress
|
|
|
|
if emailType = .nil | emailAddress = .nil then return 0
|
|
|
|
sql = "INSERT INTO email_addresses (contact_id, type, email) VALUES ("contactId", '"emailType"', '"emailAddress"')"
|
|
rc = db~exec(sql)
|
|
if rc \= 0 then do
|
|
say "Error adding email address:" db~errMsg()
|
|
return -1
|
|
end
|
|
|
|
email_id = db~lastInsertRowId()
|
|
|
|
return email_id
|
|
|
|
::METHOD getEmailAddresses
|
|
expose db
|
|
use arg contactId
|
|
|
|
sql = "SELECT id, type, email FROM email_addresses WHERE contact_id = "contactId
|
|
emails = db~exec(sql, .true, .ooSQLite~OO_ARRAY_OF_DIRECTORIES)
|
|
return emails
|
|
|
|
::METHOD updateEmailAddress
|
|
expose db
|
|
use arg emailId, emailType, emailAddress
|
|
|
|
sql = "UPDATE email_addresses SET type = '"emailType"', email = '"emailaddress"' WHERE id = "emailId
|
|
rc = db~exec(sql)
|
|
if rc \= 0 then do
|
|
say "Error adding email address:" db~errMsg()
|
|
return -1
|
|
end
|
|
return rc
|
|
|
|
|
|
::METHOD deleteEmailAddress
|
|
expose db
|
|
use arg emailId
|
|
|
|
sql = "DELETE FROM email_addresses WHERE id = "emailId
|
|
rc = db~exec(sql)
|
|
if rc \= 0 then do
|
|
say "Error deleting email address:" db~errMsg()
|
|
return -1
|
|
end
|
|
return rc
|
|
|
|
/* Physical address operations */
|
|
|
|
::METHOD addRealAddress
|
|
expose db
|
|
use arg contactId, addressType, street, city, state, postalCode, country
|
|
|
|
if addressType = .nil | street = .nil then return 0
|
|
|
|
sql = "INSERT INTO addresses (contact_id, type, street, city, state, postal_code, country)",
|
|
"VALUES ("contactId", '"addressType"', '"street"', '"city"', '"state"', '"postalCode"', '"country"')"
|
|
rc = db~exec(sql)
|
|
|
|
if rc \= 0 then do
|
|
say "Error adding address:" db~errMsg()
|
|
return -1
|
|
end
|
|
addr_id = db~lastInsertRowId()
|
|
return addr_id
|
|
|
|
::METHOD getRealAddresses
|
|
expose db
|
|
use arg contactId
|
|
|
|
sql = "SELECT id, type, street, city, state, postal_code, country" || ,
|
|
" FROM addresses WHERE contact_id = " contactId
|
|
addresses = db~exec(sql, .true, .ooSQLite~OO_ARRAY_OF_DIRECTORIES)
|
|
return addresses
|
|
|
|
::METHOD updateRealAddress
|
|
expose db
|
|
use arg addressId, addressType, street, city, state, postalCode, country
|
|
|
|
sql = "UPDATE addresses SET type = '"addressType"', street = '"street"', city = '"city"'," || ,
|
|
" state = '"state"', postal_code = '"postalCode"', country = '"country"' WHERE id = "addressId
|
|
rc = db~exec(sql)
|
|
|
|
if rc \= 0 then do
|
|
say "Error updating address:" db~errMsg()
|
|
return -1
|
|
end
|
|
|
|
return rc
|
|
|
|
::METHOD deleteAddress
|
|
expose db
|
|
use arg addressId
|
|
|
|
sql="DELETE FROM addresses WHERE id = "addressId
|
|
rc = db~exec(sql)
|
|
|
|
if rc \= 0 then do
|
|
say "Error removing address:" db~errMsg()
|
|
return -1
|
|
end
|
|
|
|
return 0
|
|
|
|
::METHOD removeContactPhones
|
|
expose db
|
|
use arg contactId
|
|
|
|
sql = "DELETE FROM phone_numbers WHERE contact_id = "contactId
|
|
rc = db~exec(sql)
|
|
|
|
if rc \= 0 then do
|
|
say "Error removing phone numbers:" db~errMsg()
|
|
return -1
|
|
end
|
|
|
|
return 0
|
|
|
|
::METHOD removeContactEmails
|
|
expose db
|
|
use arg contactId
|
|
|
|
sql = "DELETE FROM email_addresses WHERE contact_id = "contactId
|
|
rc = db~exec(sql)
|
|
|
|
if rc \= 0 then do
|
|
say "Error removing email addresses:" db~errMsg()
|
|
return -1
|
|
end
|
|
return 0
|
|
|
|
::METHOD removeContactAddresses
|
|
expose db
|
|
use arg contactId
|
|
|
|
sql = "DELETE FROM addresses WHERE contact_id = "contactId
|
|
rc = db~exec(sql)
|
|
|
|
if rc \= 0 then do
|
|
say "Error removing addresses:" db~errMsg()
|
|
return -1
|
|
end
|
|
return 0
|