Database Functionality – CodeGen

The CodeGen Tool and its Role in Synchronization

MemberJunction’s CodeGen tool plays a vital role in the database architecture, primarily focusing on synchronizing the database schema with the platform’s metadata. This tool examines all tables defined in each schema, checking against MemberJunction’s entity table to ensure consistency and accuracy. The entity table, situated at the center of MemberJunction’s metadata universe, tracks all entities (or tables) across all schemas, while another table, named entity fields, keeps track of the columns within each entity.

This process is crucial in maintaining a synchronized state between the user-defined database schema in SQL Server and the metadata used by MemberJunction. The CodeGen tool also monitors changes in the table structure, including updates to columns and foreign key relationships, ensuring that these changes are accurately reflected in MemberJunction’s metadata.

Transactions via Stored Procedures

MemberJunction adopts a specific approach to database transactions, emphasizing security and abstraction. All inserts, updates, and deletes are conducted through stored procedure wrappers rather than direct SQL statements. This method serves several purposes:

  1. Security: By avoiding direct privileges to modify the underlying tables, MemberJunction enhances the security of the database. This approach limits the potential actions that can be performed on the database, constraining the transactional capabilities of user accounts.
  2. Performance: Stored procedures offer performance benefits as they are precompiled, which can result in faster execution compared to ad-hoc SQL queries.
  3. Abstraction: Similar to the rationale behind using views, stored procedures provide another layer of abstraction. This allows for additional database-level logic, separate from the business logic encapsulated in the base entity or the API layer.

Stored procedures are integral to MemberJunction’s design philosophy, ensuring that database operations are secure, efficient, and maintain a clear separation from other layers of the architecture.

Customization and Control

MemberJunction’s architecture allows for a degree of customization in how transactions are managed. While the default behavior is to utilize generated stored procedures, there is flexibility for more complex entities or specific requirements.

📘

DBAs can turn off the automatic generation of these procedures and maintain custom scripts for create, update, and delete operations.

This flexibility ensures that MemberJunction can adapt to various scenarios and requirements, providing tools for customization while maintaining a robust and secure default transaction management system.