$SQL $ token for db-specific sql in reports

[bobintetley]

Idea for a new $SQL token for reports. This token would be added to _SubstituteSQLParams in the part that substitutes $VAR and $ASK tokens.

It would allow for SQL fragments to be written in token form so that at runtime, the correct SQL can be written for the backend in use.

Reports in the repository converted to use these tokens would therefore not need to set a specific database and could be listed as "Any".

The format within is SQL [tokenname] [comma separated values]

Example tokens. These should map to sql_token functions that are already present in the Database subclasses, which should simplify the code in _SubstituteSQLParams greatly as you're just going to call whatever the method is on dbo.

$SQL CONCAT 'Value1','Value2',o.OwnerName Postgres: 'Value1' || 'Value2' || o.OwnerName MySQL: CONCAT('Value1', 'Value2', o.OwnerName) SQLite: concat('Value1', 'Value2', o.OwnerName)

$SQL DATEADD DateBroughtIn,+,3,months Postgres: DateBroughtIn + INTERVAL '3 months' SQLite: datetime(DateBroughtIn,'+3 months') MySQL: DateBroughtIn + INTERVAL 3 months

$SQL DAYDIFF ad.ReturnDate,ad.MovementDate Postgres: EXTRACT(DAY FROM ad.ReturnDate-ad.MovementDate)::integer MySQL: DATEDIFF(ad.ReturnDate, ad.MovementDate) SQLite: julianday(ad.ReturnDate) - julianday(ad.MovementDate)

$SQL DAY DateOfBirth Postgres: EXTRACT(DAY FROM DateOfBirth)::integer MySQL: DAY(DateOfBirth) SQLite: strftime('%d', DateOfBirth)

$SQL MONTH DateOfBirth (as above, use %m for SQLite month)

$SQL YEAR DateOfBirth (as above, use %y for SQLite year)

[bobintetley]

Once these exist, these should cover every single repository report that has been forced to be postgres only. They can be rewritten to use these tokens.

[bobintetley]

Documentation in the manual is probably not needed for these as normies won't be using them, these functions really help the repository reports be portable.