Reading columns from db following insert in Spark

1. In Spark, following insert you get the Id number of the newly created row from the database.
How will you extend the insert method to read other columns from the database for a specific domain?
Say you have triggers in the database that populate columns on insert, following insert you want to read those populated columns back into your newly created domain object.
Can this be done without modifying 'core.cs'?

2. How will you extend the 'select' method so that it selects 'valid rows.' In this case you have a column 'Valid' which is used in place of 'delete' action ('delete' simply sets this column to 'false').
Since this functionality is applicable to all tables in the database this change is applicable to all domains.
Again can this be done without modifying 'core.cs'?

3. Spark has 'expand' method where you get values from a couple of tables in a one go.
However, the relationship given in Spark is a one-to-many relationship.
Consider this, you have table 'product' and you have a table 'supplier.' The relationship here is a many-to-many relationship because each supplier can have a few products and each product can have a few suppliers. In this case you need a junction table to solve the many-to-many relationship in the database.
How can Spark overcome the junction table and read all suppliers for a given product (or all products for a given supplier)?

I am aware you could construct dynamic SQL statements but I wonder if you could use those 'extension points' that are mentioned in the documentation to achieve the above mentioned points?

Regards,
Menashay

Menashay Givoni, Jan 25, 2014

Reply 1

Menashay, great questions:

1. Extending the Insert statement would not be appropriate. However, you can build a custom string in which several SQL statements are concatenated (separated by a semicolon) and issue that as a separate query; for example, some inserts followed by a select statement. The last select statement will determine what you will get back.

However, it seems to me that with triggers in the middle such a transaction gets a bit hairy. My personal preference would be to issue an insert (or multiple concatenated inserts) and then an extra select query. Indeed, as a general rule you want to minimize the number of trips to the database, but if the code gets too convoluted I would choose to issue an extra statement.

2. The technique you are using is called 'soft deletes'. Of course in each query you could add the WHERE clause 'Active = true', but that gets a bit cumbersome. If you wanted to you can implement 'soft deletes' into Core.cs. This would be in the Domain Layer section inside the CreateSelect, and CreatePaged methods (there could be more, I have not fully researched this). .

3. When issuing M-M selects I would build a dynamic SQL statement and execute it with the Query method.

Hope this helps,
Jack

Jack Poorte, Feb 02, 2014