# High level direction for database module development **URL:** \ **Category:** Module Development\ **Created:** [November 15, 2022, 11:31pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558 "2022-11-15T23:31:09Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [November 15, 2022, 11:31pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/1 "2022-11-15T23:31:09Z")
I'm working on a module that will, at a high level, run some `sql` scripts from a remote repository against an existing database connection within Ignition. So far, my only experience with building modules has been rebuilding the `gateway-webpage` example - it was more or less self-explanatory (besides the labels for the Config page not showing correctly). I'm looking for guidance on how to accomplish the above task. How I envision it working is, first, a user would create a DB connection. Then, they'd visit the custom module config page and be able to view and select all existing non-faulted database connections from a dropdown. After setting a few other options, they'll click a button that will retrieve a `sql` file from a remote repository and then execute that file on the aforementioned database connection (which will add some tables, relationships, functions, stored procedures, etc). One of the biggest things that doesn't make sense to me right now is how to add the above actions to a button click on the Config page. The GatewayHook class has `setup()`, `startup()` and `shutdown()` methods but I'm assuming those are only when the module is being set up, started or shutdown (clearly) and not for when the user performs an action on the modules Config page (the **`Save Changes`** button). Where would I add the method that will accomplish what I have above? I am ready to slog through the actual implementation (following this guide, in part: [Working with Platform Services - Ignition SDK Programmer's Guide - Ignition Documentation](https://docs.inductiveautomation.com/display/SE/Working+with+Platform+Services)) but I just don't know where to put those source files and how to trigger them off of the **`Save Changes`** button event. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 15, 2022, 11:43pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/2 "2022-11-15T23:43:54Z")
Currently, the way to do an interactive Gateway page (that's documented) is with Wicket. Wicket is a tangled, somewhat confusing mess, _and_ we're using an older version of it in an non-standard way. It's a slog. @pturmel has posted some example code at various points; consider searching this forum for `RecordActionTable` (one possible starting point). If you want to reflect Ignition database connections as your "source of truth" for the possible values in this table, then you could probably build a RecordActionTable off the existing datasource record, remove the ability to add/edit, and "simply" wire in your new actions to add tables/relationships/functions/etc. As an additional wrinkle, I'm not sure how what your plan is for actually _implementing_ these different relationships/procedures/functions/etc, but be warned that targeting multiple DB flavors (MySQL, MSSQL, Postgres) will quickly run you into behavior that's outside of the JDBC standard and may run into issues. ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [November 16, 2022, 4:03am UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/3 "2022-11-16T04:03:29Z")
This is already very helpful... Do you know which version of Wicket is being used in 8.1.20 (or any recent version of 8.1)? This will be helpful when searching for documentation. Regarding the `RecordActionTable` approach - I found another forum thread where you linked your `secret-keeper` project. This looks like an easy way to create multiple instances of a PersistentRecord but it still doesn't look like it'll let me hijack a submit action to execute some additional steps. Regarding what I said about the database operations, my plan was to use the **`SRConnection`** subclass and do something like ```java SRConnection con; int maxVal; try { con = context.getDatasourceManager().getConnection("myDatasource"); con.runPrepQuery("CREATE TABLE sample ... etc", 5); maxVal= (Integer) con.runScalarQuery("SELECT max(col) FROM sample"); } finally { if (con!=null){ con.close(); } } ``` and have a bunch of prepared statements (SQL statements like CREATE TABLE, CREATE PROCEDURE, etc) which would then be run. In this first iteration, I'm only going to focus on one DB vendor and version, namely, SQL Server 2019, so if I can get it working with that, I'll focus on making it working for other DB vendors. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 16, 2022, 4:38pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/4 "2022-11-16T16:38:43Z")
> [@YF129701](#): > > Do you know which version of Wicket is being used in 8.1.20 (or any recent version of 8.1)? `6.30.0`, apparently. > [@YF129701](#): > > Regarding the `RecordActionTable` approach - I found another forum thread where you linked your `secret-keeper` project. This looks like an easy way to create multiple instances of a PersistentRecord but it still doesn't look like it'll let me hijack a submit action to execute some additional steps. The trick is to make your RecordActionTable, but use the _existing_ `com.inductiveautomation.ignition.gateway.datasource.records.DatasourceRecord` class. In your subclass of `RecordActionTable`, you can override `com.inductiveautomation.ignition.gateway.web.components.RecordActionTable#addRecordInstanceActions` to remove the default edit and delete actions and add your own. ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [November 16, 2022, 8:49pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/5 "2022-11-16T20:49:54Z")
> [@paul-griffith](#): > > `6.30.0`, apparently. Phew. That's a whole 3 major versions behind current 🤒 Just resolved my Config page label issues... Before I jump into implementing `addRecordInstanceActions`, what do you think of this approach: I'll start with only one instance of my class and forego `RecordActionTable` for now... I'll add my DB code (something based on below snippet) ```java SRConnection con; int maxVal; try{ con = context.getDatasourceManager().getConnection("myDatasource"); con.runPrepQuery("INSERT INTO example(col) VALUES", 5); maxVal= (Integer) con.runScalarQuery("SELECT max(col) FROM example"); }finally{ if(con!=null){ con.close(); } } ``` right into the `recordAdded` and `recordUpdated` record listeners, so whenever my persistent record either gets added or gets updated, I'll run some statements then. Does this sound like a bad idea or is it workable? ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 16, 2022, 10:08pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/6 "2022-11-16T22:08:26Z")
> [@YF129701](#): > > Does this sound like a bad idea or is it workable? Well, I don't really know or understand what your ultimate goal is. Based on your first post, I'm not sure that you actually need your own PersistentRecord; it seemed like you might be able to 'piggyback' on the existing datasource record and just do your own thing. But maybe not. ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [November 16, 2022, 10:34pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/7 "2022-11-16T22:34:22Z")
My ultimate goal is to "install" or build out a complex data model (tables, queries, stored procedures, functions, etc), script library, and a few other resource types easily AND to use a few other core Ignition features\*. The data model includes not only tables and relationships, but also a ton of stored procedures that make up a comprehensive business logic library. Most of our customer projects usually end up being one of a few types (OEE, T&T, etc) so we find ourselves doing a lot of repetitive setup work that could be automated. \*Regarding my point about core Ignition features - sure, the project setup automation is nice but having a module also allows me to use Ignition's licensing feature. If we can sell a huge library of resources as a line item, we would also like to ~~be reasonably sure our IP isn't cloned/duplicated without permission~~ protect the customer's investment. I'm chatting with our internal guru right now to find out more about the current setup process, so that might clarify some assumptions and whatnot. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 16, 2022, 11:00pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/8 "2022-11-16T23:00:24Z")
> [@YF129701](#): > > ~~be reasonably sure our IP isn't cloned/duplicated without permission~~ protect the customer's investment. While modules are _harder_ to introspect than project resources, keep in mind that it's trivial to decompile Java files. This is more 'security through obscurity'; at most you can run an obfuscation tool as part of your build process, but you can't truly obscure the code to any great depth. ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [November 16, 2022, 11:23pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/9 "2022-11-16T23:23:42Z")
And that's completely fine, in my mind. When we do it manually right now, the customer still has full access to their own database and gateway that we're installing these resources on, so it is definitely not a foolproof method. We also want to stay away from a black box product, so it's important for us that our customers would have the technical ability to modify stuff if they chose to. Regarding the implementation, I added a trivial function within the `recordAdded` and `recordUpdated` record listeners, but it doesn't look like the `recordAdded` function is executing... Is it only triggered if a `RecordActionTable` is used and a new record is added that way? Or maybe if I don't specify default values in my settings record? ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 16, 2022, 11:40pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/10 "2022-11-16T23:40:15Z")
How are you adding new records? Record added notifications will only happen if you're going through our ORM _and_ explicitly send notifications. ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [November 17, 2022, 2:43am UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/11 "2022-11-17T02:43:12Z")
I should've been more clear... I'm not adding a record to the internal db through the `PersistentRecord` interface. What I am doing is adding a record to a datasource I added through the database connections config page. In other words, I added a new DB called `ModuleDev` through SSMS, then added it as a connection on the gateway, and now I reference that connection and run some prepared statements in `GatewayHook.java` like so: ```java // listen for updates to the settings record SMSettingsRecord.META.addRecordListener(new IRecordListener() { @Override public void recordUpdated(SMSettingsRecord sMSettingsRecord) { SRConnection con = null; int minVal; try { String createTableStatement = "CREATE TABLE dbo.Location (LocationID INT NOT NULL PRIMARY KEY, Name VARCHAR(255), CreatedOn DATETIME);"; String insertFirstRecord = "INSERT INTO dbo.Location (LocationID, Name, CreatedOn) VALUES (1, 'OriginalName', CURRENT_TIMESTAMP);"; String retrieveRecord = "SELECT TOP 1 LocationID FROM dbo.Location ORDER BY LocationID ASC;"; con = context.getDatasourceManager().getConnection(sMSettingsRecord.getConnectionName()); con.runPrepUpdate(createTableStatement); con.runPrepUpdate(insertFirstRecord); minVal = (Integer) con.runScalarPrepQuery(retrieveRecord); log.info("Update ran from recordUpdated!", minVal); } catch (SQLException e) { log.error("Unable to update records in recordUpdated! ", e); } finally { if (con != null) { try { con.close(); } catch (SQLException e) { log.error("Unable to close connection con on recordUpdated!", e); } } } log.info("recordUpdated()"); } @Override public void recordAdded(SMSettingsRecord sMSettingsRecord) { // add a table to the database ModuleDev on localhost SRConnection con = null; int minVal; try{ String createTableStatement = "CREATE TABLE dbo.Location (LocationID INT NOT NULL PRIMARY KEY, Name VARCHAR(255), CreatedOn DATETIME);"; String insertFirstRecord = "INSERT INTO dbo.Location (LocationID, Name, CreatedOn) VALUES (1, 'OriginalName', CURRENT_TIMESTAMP);"; String retrieveRecord = "SELECT TOP 1 LocationID FROM dbo.Location ORDER BY LocationID ASC;"; con = context.getDatasourceManager().getConnection(sMSettingsRecord.getConnectionName()); con.runPrepUpdate(createTableStatement); con.runPrepUpdate(insertFirstRecord); minVal = (Integer) con.runScalarPrepQuery(retrieveRecord); log.info("minVal: ", minVal); } catch (SQLException e) { log.error("Error creating table, inserting record or retrieving record! Code: ", e); } finally { if(con != null){ try { con.close(); } catch (SQLException e) { log.error("Unable to close the db connection con! Code: ", e); } } } log.info("recordAdded()"); } @Override public void recordDeleted(KeyValue keyValue) { log.info("recordDeleted()"); } }); log.debug("Setup complete."); } ``` The issue I'm having right now is I'm not understanding (a lot of things but also) why `recordAdded` isn't being executed when the first record is created. Again, I have a single static config page with 3 properties, one of which is used, namely, `getConnectionName()`. When I go to that page, fill out the form and then `Save Changes` the first time, I would expect the `recordAdded` method to execute. What happens is only the `recordUpdated` method executes after I save (and with the snippet above, it isn't hard to see that its going to fail because `dbo.Location` already exists). Is this because I specified default values for the form fields in my `SettingsRecord.java` file? Or is it because, even though I uninstalled the module and reinstalled it many times, the table inside the internal DB is still there so the `recordAdded` method won't execute? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 17, 2022, 4:37am UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/12 "2022-11-17T04:37:44Z")
Ignition's wicket forms are all built around the internal DB's PersistentRecord, which is totally unsuited to any other database backend. I hacked them up for my Ethernet/IP module, but figured out the non-hackish for my Modbus module. It required defining some form components that actually follow Wicket's IModel expectations for POJOs. See this post: > [@Can I use a dynamically populated ComboBox for Gateway Web Field](https://forum.inductiveautomation.com/t/can-i-use-a-dynamically-populated-combobox-for-gateway-web-field/64073/5): > > Nah. Wicket works just fine. It’s just that everything IA does with it uses the PersistentRecord models. I implemented my own. The abstract base: public abstract class AbstractModelEditor\ extends Panel { private static final long serialVersionUID = 1L; protected final WebMarkupContainer body; protected final FormComponent\ editor; /\*\* \* Mimic's Ignition's Field style in the RecordEditForm, but not dependent on \* any PersistentRecord instance. Instead, the appropriate wicket … ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [November 17, 2022, 3:34pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/13 "2022-11-17T15:34:14Z")
I'm assuming you're implying here that the better way to do this without using a `PersistentRecord` would be to use Wicket to define a button that will run my `sql` scripts against a DB (not the internal one)? ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 17, 2022, 4:20pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/14 "2022-11-17T16:20:51Z")
From everything you've described so far, I don't think you need _your own_ PersistentRecord implementation. _My_ preferred approach, based on my understanding of what you're trying to do, would be a RecordActionTable using DatasourceRecord.META, with no edit/delete options, but with your custom hooks to run SQL scripts. IMO that would be easier to implement (and more in line with the platform's aesthetic) than you're likely to get from a bespoke Wicket page. ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [November 17, 2022, 4:51pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/15 "2022-11-17T16:51:37Z")
This is what I'll try to implement, then. Is there a way to restrict the RecordActionTable to a single record (or, alternatively to check and make sure the DBConnectionName field isn't used in more than one entry)? ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 17, 2022, 5:16pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/16 "2022-11-17T17:16:42Z")
I'm not sure what you're asking. I was picturing something like this: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/9/e96530c4a4a6256ff46abfd0b335f68f06779144.png) With a custom RecordActionTable, you can display whatever columns you want. Then you would wire up your custom actions (create tables, create procedures, etc) and they would automatically show up in the 'more' dropdown. That would be your way to tie actions to a particular database. ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [November 17, 2022, 5:38pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/17 "2022-11-17T17:38:56Z")
Sorry, I should've been more clear... Here is more of what I'm after: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/5/65e1554dbc985e63fa1aa2940fea8202da8f93b3.png) Above is my current config page with some BS fields (except the Database connection). The user specifies a database connection name of a connection they configured previously in the DB config page. It doesn't make sense (at the very least for the first version of this module) to have more than one configuration per gateway, just like you don't have a RecordActionTable to configure Redundancy settings on the Redundancy config page. Next, the user would click on the `Save Changes` button, the module will retrieve sql script files from a remote repository (probably through an HTTP call or a git API if I can find one, I don't know yet) and then execute those statements on the database whose name is specified in the Database connection field (in this case, ModuleDev). This is not a very sophisticated approach and has a lot of issues in itself, but I want to get a base case working. Most of this already works. Instead of reading a sql file from a repository, I have a few prepared statements (CREATE TABLE, INSERT INTO, SELECT). How those statements are being triggered is in the `recordUpdated` record listener. I'm trying to figure out why the `recordAdded` listener is not firing when I add the initial module configuration after I install the module. I cleared the PersistentRecord record in the internal DB and still, when I `Save Changes`, I can see in the logs that the `recordUpdated` method was executed, but not `recordAdded`. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 17, 2022, 6:15pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/18 "2022-11-17T18:15:36Z")
Ah, gotcha. That page is a `RecordEditForm` - for your use case, it's probably simplest to just make a new page that recycles some of the concepts of the RecordEditForm, rather than creating your own. `com.inductiveautomation.ignition.gateway.web.components.editors.IEditorSource` is the base class of the individual components in the editing categories. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 17, 2022, 6:39pm UTC](https://forum.inductiveautomation.com/t/high-level-direction-for-database-module-development/66558/19 "2022-11-17T18:39:13Z")
> [@paul-griffith](#): > > would be a RecordActionTable I strongly disagree. An `ActionTable` implementation that does not reference `PersistentRecord` at all is the right answer. `PersistentRecord` is always pointing at the internal database. It cannot be made to point at any other. `IEditorSource`, IIRC, is part of the problem.