You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
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.
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.
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)
The text was updated successfully, but these errors were encountered: