Fundraising/Internal-facing/CiviCRM/Creating extensions in Civicrm to manage tables in other WMFF Databases

===== Overview =====
This is useful for migrating displays from Drupal modules to the new Searchkit/Formbuilder implementation or just setting up a GUI to manage the data in tables outside of the CiviCRM database.
There is Upstream documentation for creating an Extension in CiviCRM. According to the documentation,CiviCRM Extensions are packaged pieces of functionality that extend CiviCRM's out-of-the-box functionality, independent of CMS platform.Another term that would come up in this documentation and the official documentation, is the word "Entity." For simplicity, imagine the entity as an object that'd help in managing a database table. Before proceeding, confirm you have the Civix package installed on your WMFF CiviCRM setup. It usually comes prepackaged with the setup but you can confirm with this command:
docker@civicrm:/srv/civi-sites/wmff/drupal/sites/default$ civix civicrm:ping
Note the directory the command was called.
If the "civix" package is installed and the configuration is right, the response of the command should be "Ping Successful"
===== Create Extension =====
The following steps would create an extension template, the official documentation may help:
# Create the desired extension in the WMFF CiviCRM extension directory. See the command below (note the directory the command was called):
docker@civicrm:/srv/civi-sites/wmff/drupal/sites/default/civicrm/extensions$ civix generate:module myextension
If the command was successful, it should create a directory called "myextension" in the extensions directory. The files in the directory are mostly PHP and XML files, you don't need to bother about them at this point.
# Modify the info.xml file that can be found at the base of the myextension directory. You can change the name property from FIXME to the name of the extension, add a description to the description property, and your name/email to the maintainer property.
# Congratulations you have created a new CiviCRM Extension, though it has no features yet.
===== Create Entity from scratch =====
In order to add features and functionality to our extension, an Entity would need to be added. The following steps would create and add an entity to the extension "myextension" created above, the official documentation may help:
# Unlike the extension name, the entity name is case-sensitive and should be in CamelCase. We'd name the entity, MyEntity.
# Move into the myextension directory. See command below:
docker@civicrm:/srv/civi-sites/wmff/drupal/sites/default/civicrm/extensions$ cd myextension
# Create the entity in the myextension directory.
docker@civicrm:/srv/civi-sites/wmff/drupal/sites/default/civicrm/extensions/myextension$ civix generate:entity MyEntity
If the command was succesful, it should create files and new directories.
# The BAO directory contains a MyEntity class that contains the business logic for interacting with the database. The MyEntity class in the Civi/Api4 directory would be useful for creating custom API actions on the entity that's not any of the Create, Read, Update, and Delete actions. Don't bother about these classes and other files.
# The only file that needs to be modified at this stage is the MyEntity.xml file located in
/srv/civi-sites/wmff/drupal/sites/default/civicrm/extensions/myextension/xml/schema/CRM/Myextension/MyEntity.xml
# This file is similar to the SQL schema file that defines a table, in this case the Entity's info.xml file would be defining the entity as it relates to the table and CiviCRM in general. A sample SQL table schema could look like this:
CREATE TABLE IF NOT EXISTS myentity (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `gateway` varchar(255) NULL,
 `order_id` varchar(255) NULL,
 `gateway_txn_id` varchar(255) NULL,
 INDEX `idx_myextension_order_id_gateway` (`order_id`, `gateway`),
 INDEX `idx_myextension_gateway_txn_id_gateway` (`gateway_txn_id`, `gateway`),
 PRIMARY KEY `pk_damaged_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Therefore, the MyEntity.xml file entity would look like:
 <base>CRM/Myextension</base>
 <class>MyEntity</class>
 <name>civicrm_my_entity</name>
 <comment>FIXME</comment>
 <log>true</log>
 <field>
 <name>id</name>
 <type>int unsigned</type>
 <required>true</required>
 <comment>Unique Damaged Table Row ID</comment>
 <html>
 <type>Number</type>
 </html>
 </field>
 <field>
 <name>gateway</name>
 <type>varchar</type>
 <required>false</required>
 <comment>Gateway</comment>
 <html>
 <type>Character</type>
 </html>
 </field>
 <field>
 <name>order_id</name>
 <type>varchar</type>
 <required>false</required>
 <comment>Order ID</comment>
 <html>
 <type>Character</type>
 </html>
 </field>
 <field>
 <name>gateway_txn_id</name>
 <type>varchar</type>
 <required>false</required>
 <comment>Gateway Transaction ID</comment>
 <html>
 <type>Character</type>
 </html>
 </field>
 <primaryKey>
 <name>id</name>
 <autoincrement>true</autoincrement>
 </primaryKey>
 <index>
 <name>idx_myextension_order_id_gateway</name>
 <fieldName>order_id</fieldName>
 <fieldName>gateway</fieldName>
 <unique>true</unique>
 </index>
 <index>
 <name>idx_myextension_gateway_txn_id_gateway</name>
 <fieldName>gateway_txn_id</fieldName>
 <fieldName>gateway</fieldName>
 <unique>true</unique>
 </index>
Checkout the official documentation for the definition of the tags used in the xml file for the database schema on the official documentation here.
# Once the XML fields are filled with the table rows and the primary and foreign keys have been set, the next thing to do would be to generate the SQL, DAO and BAO files.
# In order to generate the SQL, DAO and BAO files, see command below:
docker@civicrm:/srv/civi-sites/wmff/drupal/sites/default/civicrm/extensions/myextension$ civix generate:entity-boilerplate
# The last step should generate the following files: DAO/MyEntity.php, sql/auto_install.sql, sql/auto_uninstall.sql.
# The auto_install.sql should have a command that looks like that in step 6. However, the extension would also drop any similar table on the CiviCRM database before it installs. Confirm it's safe before proceeding to the next step.
# The final step before installing the extension is to add an Upgrader class. This class would run the auto_install.sql and auto_uninstall.sql files at the point of installation and uninstallation. See command to do so below:
docker@civicrm:/srv/civi-sites/wmff/drupal/sites/default/civicrm/extensions/myextension$ civix generate:upgrader
Running the command above should write add a Myextension/Upgrader.php file and Myextension/Upgrader/Base.php to the CRM directory in the extension directory.
# Congratulations on getting to the final step, the last step to take would be to install the extension. This can be done conveniently on the UI by selecting the Administer button in the navigation bar at the top of the CiviCRM page, Click on System Settings on the drop down, and select Extension.
# Scroll down on the Extensions page to find your extension and click on the Enable/Install button on the far right of that row.
===== Create entity from an already existing table on a different database =====
Sometimes, we may want to manage tables from other DBs on the CiviCRM UI. In order to do that we'd have to create an entity for this table, but with a few tweaks.
# Follow steps 1 - 8 described above in the Create entity from scratch instructions. Where your Entity.xml file is defined with the table sql definition.
# Step 1 above should generate the following files: DAO/MyEntity.php, sql/auto_install.sql, sql/auto_uninstall.sql.
# CiviCRM uses a dedicated CiviCRM database, where every Core or extensions entity have a dedicated table defined. However, given that this entity aims to use a table in a separate database, the concept of SQL Views would need to be applied.
# The SQL View would provide a virtual table in CiviCRM that would perform similar operations on the actual table in the original database. To create a view for the new entity: in the sql directory, create a new file called install.sql and add this command
create view {desired_view_name} as select * from {external_database_name}.{external_table_name};
Where "desired_view_name" is the intended name for your view, "external_database_name" is the source database name, and "external_table_name" is the source table name. For example, in the "damaged" extension for the Smashpig Damaged table, here's the command in the install.sql script:
create view damaged_view as select * from smashpig.damaged;
# In order to delete the view when the extension is uninstalled: create another file called: uninstall.sql, then add the following command:
drop view if exists {desired_view_name};
# In the CRM/Myextension/Upgrader.php file, uncomment the install function and uninstall function. In the install/uninstall function, set the execute file to the install/uninstall script created in step 4/5. For example
 public function install() {
 $this->executeSqlFile('sql/install.sql');
 }
# Copy the table name from the sql/auto_install.sql script. Then do a find and replace on the "myextension" (or your extension name) directory, replacing the table name copied from the sql script with your view name. For example, the auto_install.sql script has a command that would create a table called "civicrm_my_entity", but I named the view created from the Smashpig "Damaged" table as "damaged_view". I'd have to find anywhere within the extension where the table name "civicrm_my_entity" is used and replace with "damaged_view".
# Delete the sql/auto_install.sql, sql/auto_uninstall.sql as they'd typically run during installation/uninstalling of your extension. Since the table that the files create wouldn't be used, it'd be unnecessary to have them run at any point.
# Follow steps 11-13 described above in the Create entity from scratch instructions.
# Congratulations, you now have an extension that would help manage an external database set up in CiviCRM.
# Next step would be to Create Custom Formbuilder pages to monitor tables in other WMFF Databases.
====== Related Topics ======
* Create Custom Formbuilder pages to monitor tables in other WMFF Databases
* Exporting SearchKit Search into an extension
* Exporting FormBuilder Pages into an extension