I am in the process of designing an architecture for a plugin based application and I have some questions about how to handle database access and logging. The goal of my application is to allow a device my company is manufacturing to send messages over any number of third-party protocols and to any number of third party devices. Because of the nature of my application, I need to log every message that is sent via my system in reasonable detail.
Here's what I am thinking on the Database side: There will be a global message table which stores all of the implementation agnostic data we have, and then there will be a table (or set of tables) for each messaging plugin as defined by the plugin writer which references the Global Message table via a foreign key.
It is handling these other tables that I am having a hard time figuring out how to design safely. On the one hand, I don't particularly want to give the plugins full access to the database (I want them to have the fewest possible privileges), on the other hand I want the plugin developers to have full reign to log any data they need to tables that they define similarly to how plugins work in Wordpress. Additionally, I don't want logging to the Database to be optional at all: Good logging is crucial to my application.
Things I have considered:
- Designing a logging infrastructure which allows a plugin to define (maybe in an XML file) the tables it needs in the DB and a mapping between those tables and objects that they will return, and using those mappings and table definitions to write to the DB the results from method calls.
- Making a naming convention for Stored Procedures in the DB that each plugin must define when defining their tables and automatically calling those stored procedures after sending a message.
- Allowing each plugin to implement a logging method which returns a String holding the proper SQL query.
Each of the above solutions makes me a little uncomfortable, but I'm not sure what other approaches might exist. Any Suggestions?
Implementation Details: I am writing my application in Java, and I am using a hand-rolled plugin solution, so I have a pretty decent amount of flexibility. Since none of the actual code has been written (other than some proof-of concept code which is really just for reference), I can make fairly major changes to my plan without great cost.