Technical ramblings | Mark Brooks
Creating long passwords is one of the easiest ways of improving the security of your applications and resources – here’s a graphic that estimates time it might take to brute force a password depending on the complexity of the character set and its length.
1Password – random password generator
ALTER TABLE tablename ADD COLUMN dayname VARCHAR(20) GENERATED ALWAYS AS (DAYNAME(startdate)) STORED;
This query will add a new column called dayname to the tablename table. The column will be of type VARCHAR with a maximum length of 20 characters. The GENERATED ALWAYS clause tells MariaDB to calculate the value of the column each time a row is inserted or updated. The AS keyword specifies the expression used to calculate the value, in this case the DAYNAME function applied to the startdate column. Finally, the STORED keyword tells MariaDB to store the calculated value in the table so that it can be retrieved more efficiently.
Note that the GENERATED ALWAYS and STORED clauses require MariaDB version 5.7.6 or later. If you are using an earlier version of MariaDB, you can still add a calculated field using a trigger or a view.
For example lets say we were wanting to allocate transactions to a financial year but allow the users to adjust that financial year depending on their circumstances. How could we do that.
Here we have a table called
t0165financeyear
whose structure is
pkid taxyear startdate enddate
And a table of transactions called t023finance
CREATE VIEW v0004financeyear AS SELECT a.pkid pkidt0023, a.banked, a.transactiondate, a.description, a.category, a.Direction, a.hmrcint, a.pkidt0001, a.pkidt0007, a.vamount, a.invoiceno, a.pkidt0011, a.dupdated, a.dcreated, b.pkid pkidt00165, b.taxyear from t0023finance a, t00165taxyears b where a.banked between b.startdate and b.enddate
Remarkably simple – I suspect that we might get cartesian join issues if your start date and end dates in the t00165 table overlap.