magic.data.common

This project is the generic data adapter, that transform dynamically from a lambda node structure into SQL, and polymorphistically invokes your specialised data adapter, resulting in SQL statements executed towards your database type of choice. In addition, it contains helper slots to give you more “raw” database access, in addition to slots helping you to open database connections, create transactions, execute SQL, etc.

How to use [sql.*]

These slots never executes SQL towards your data adapter, but rather simply generates your SQL, and returns the results of the SQL generation process back to you. They’re mostly intended for debugging purposes, and/or learning purposes, and can be interchanged with their [data.xxx] equivalent, and/or their [mysql.xxx]/[mssql.xxx]/[pgsql.xxx] equivalent, etc. You can substitute these slots with for instance [data.xxx] if you wish to actually execute some SQL towards your database adapter of choice.

How to use [sql.*] slots

All of these slots simply generates SQL for you, using the generic SQL dialect syntax, which might or might not work for your database adapter of choice. This allows you to create SQL statements without executing anything towards your database. This allows you to play around with the syntax, to understand how it works, and see how some semantic graph object results in an SQL statement before using it. All of these slots have [data.*] equivalent slots, which again polymorphistically invokes your specialised data adapter’s equivalent, and/or can be parametrised with a database type - Which again resolves to the [mysql.*] equivalent if you supply “mysql” as your [database-type], and/or MySQL is your default database type as configured in your “appsettings.json” file. Hence, the documentation for these slots is also the documentation for your [data.*] slots.

SQL injection attacks

This project protects you automatically against SQL injection attacks, and protect values, and criteria, etc - But you should not allow any potentially insecure clients to dynamically declare which columns to select, and/or field names for your where clauses. It will only protect your values, and not table names or column names against SQL injection attacks.

The project does also not verify that your SQL is possible to execute towards your database, such as verifying that specified tables or columns actually exists. It does its best however, to verify that your Hyperlambda is structured correctly, and that it will create somewhat valid SQL - But you should not assume the SQL the project generates is valid, before you have tested it.

Creating your own data adapter

If you wish to extend Magic to support a custom database type, you can do so using C# for instance. This project contains 4 base classes, which you can inherit from to extend and implement your custom logic.

  • SqlCreateBuilder - Helper class to generate insert SQL statements
  • SqlDeleteBuilder - Helper class to generate delete SQL statements
  • SqlReadBuilder - Helper class to generate select SQL statements
  • SqlUpdateBuilder - Helper class to generate update SQL statements

If you create your own database implementation, you’ll need to inherit from the above classes, and override whatever parts of these classes that doesn’t by default work as your database type needs it to work. If you wish to do this, you would probably benefit from looking at one of the existing specialised implementations, such as the MySQL, SQL Server or PostgreSQL specific implementations.

Topics

  • How to use [data.*] - All of the [data.xxx] slots are actually just polymorphistically evaluating your specialised adapter’s slots, such as for instance [data.connect], that will invoke [mysql.connect] if this is your defa
  • Aggregate results - You can also create aggregate results, by simply adding your aggregate as your column, such as the following illustrates.
  • Joins - The project supports joins by parametrizing your [sql.read] invocation with [join] arguments, beneath your [table] argument(s).
  • Differences between [join] and [where] - There is one crucial semantic difference between a [join] condition and a [where] condition, which is that the library assumes a join is always between two columns, while a where always assume you’re
  • ‘Namespacing’ columns - When you’re joining results from multiple tables, it’s often required that you specify which table you want some resulting column to be fetched from, to avoid confusing your database as to which colum
  • The [where] argument - This argument is common for [sql.update], [sql.delete], and [sql.read], in addition to that a [join] will also be logically parsed much the same way as a [where] argument.
  • Comparison operators - The project supports the following comparison operators.