SQL (Structured Query Language) is the language you use to query and administer relational databases; this page helps MDriven developers understand where SQL fits when their application persists model objects in a database.
SQL in an MDriven application
MDriven models and works with objects. A relational database stores data in tables, rows, and columns. MDriven bridges these representations through object-relational mapping: you write OCL expressions (or LINQ in code), and MDriven translates the required database work into SQL.
For example, an OCL expression can select model objects. When that expression is evaluated against persistence storage, MDriven generates and runs SQL to identify the matching database rows, then fetches the corresponding objects.
SELECT *
FROM SomeTable
WHERE SomeColumn = 'value';
The SQL above illustrates the relational-database form of a query. In normal MDriven application development, express object queries in OCL rather than tying ViewModel behavior directly to table and column names. Read Documentation:Understanding OCL with reference to SQL to learn the differences between the two ways of thinking.
What SQL can do
SQL is used with relational databases to:
- Query and retrieve data.
- Insert, update, and delete rows.
- Create and alter databases, tables, views, and stored procedures.
- Set database permissions.
SQL became an ANSI standard in 1986 and an ISO standard in 1987. Database products may also provide product-specific SQL extensions.
Choose the right MDriven approach
| Need | Recommended starting point | Example |
|---|---|---|
| Find and work with model objects in application behavior | Use OCL | Select the objects that a ViewModel should show. |
| Search or aggregate across a very large amount of persisted data | Use persistence-storage evaluation as described in PSExpression | Find one object among millions of rows, or calculate a database-side count, sum, minimum, maximum, or average. |
| Understand how model classes are represented in a relational database | Read Documentation:SQL Database | Inspect how classes, attributes, and links are stored. |
| Configure or investigate the SQL Server persistence mapper | Read Documentation:SQL Server | Use the SQL Server-specific persistence-mapper information. |
| Detect data changes made by an external system directly in SQL Server | Configure Documentation:SQL Server change tracking | An external service updates a tracked table; MDrivenServer invalidates the affected objects so they are reloaded. |
Direct SQL and external updates
MDrivenServer maintains its own dirty list so clients can discard and reload objects that MDrivenServer knows have changed. It does not automatically discover that another client or service changed the underlying SQL Server tables directly.
If a legacy system or another service reads and writes the same database, enable and configure SQL Server change tracking for the tables that can receive those external changes. The documented approach identifies changed primary keys and uses them to invalidate the corresponding MDriven objects. Do not enable it indiscriminately on every table; target the tables affected by the non-MDriven system.
SQL Server notes
SQL Server is the persistence mapper most frequently used by MDriven. For SQL Server connection failures caused by an untrusted certificate chain, see Documentation:SQL not trusted. For attribute Precision, Scale, and Length behavior that is managed through Transact-SQL, see Documentation:Precision.
Learn and investigate
- Learn how to move from table-oriented SQL thinking to object-oriented OCL: Documentation:Understanding OCL with reference to SQL.
- Learn how classes are stored and mapped: Documentation:SQL Database.
- Learn database search patterns in Documentation:Part 5 Searching in databases.
- For general SQL language instruction, see SQL tutorial.