* Initial support for pg for `date-diff`

* Make the useful-dates closer to one day

* Add simple FE stuff

* shorter test bodies

* Ensure we can use datediff functions in arithmetic expressions

* Correctly disable datediff for redshift

* simplify var names

* Support week

* cleanup test

* :datediff -> :datetimediff

* ngoc's suggestions

* Better acceptance test for datetimediff

* sort ns

* embrace the different cases for results

* bigquery day month year

* Reverse args

* Update test

* Centralize tests

* Change postgres day, month, year behaviour

* Refactor keep identity

* Tidy tests

* Tidy

* Fix bigquery week

* Add week tests

* Fix bigquery week

* Change mysql day, month, year behaviour

* Add test for hour, minute, second

* Fix postgres hour minute second

* Formatting

* Fix bigquery hour, minute, second

* Formatting

* Fix postgres timestamptz

* WIP

* Allow literals in datediff clauses

* Uncomment tests

* Fix bigquery when reporting timezone is not UTC

* Linting

* Moving away from dataset based tests

* Add timezone tests for week and tidy

* Remove unused import

* Consolidate tests

* Remove with-time-column dataset

* Remove more-useful-dates defdataset

* Remove redshift driver WIP

* Typo

* Move DatetimeLiteral clause into DateTimeExpressionArg

* Try changing test order

* Remove mt/with-report-timezone-id nil

* Add year report timezone tests

* Rename

* Rename

* Remove unused tables from useful-dates

* Remove useful-dates

* Update helper-text-strings

* Tidy

* Swap order of mt/with-report-timezone-id

* Change with-report-timezone-id; notify databases after running test

* Use temp setting for report-timezone instead

* Update helper-test-strings

* Handle literals in `datetimediff-base-base`

* Update src/metabase/driver/postgres.clj

Co-authored-by: metamben <103100869+metamben@users.noreply.github.com>

* Simplify postgres second

* Tidy: prefer hx arithmetic functions

* Drop coercion for string timestamp args for now

* Extract helper

* Revert "Change with-report-timezone-id; notify databases after running test"

This reverts commit 7abb543bed13d9d13295d0e00ad1293a40e40d53.

* Use ->timestamptz

* Use hx/->timestamp

* Rename dataset

* Undo changes to datetime-arithmetics? as these will not match by default

* Use proper format for offset datetime literals

* Shorten datetime literals in tests

* Removing notify-all-databases-updated from report-timezone setting

* Fix datetime-arithmetics test

* Revert "Fix datetime-arithmetics test"

This reverts commit 9141582508170bdbe99c4aa74bc4444e41475be6.

* Revert "Undo changes to datetime-arithmetics? as these will not match by default"

This reverts commit 9cb05f5475b24a78cb89601c918f3440606cc525.

* Add UTC timezone to tests

* Coerce strings to datetimes for ISO formats

* Revert "Removing notify-all-databases-updated from report-timezone setting"

This reverts commit 37356435a2e3981deca9ae76118d857babb2aada.

* Add comment to postgres driver implementation

* Formatting

* Disable datediff from redshift for the moment (for ever?)

* Override redshift driver/database-supports?

* Fix comments mixed up by refactoring

* Fix comments mixed up by refactoring 2

* Tidy comment

* Rename datetimediff to datetime-diff/datetimeDiff

* date-add -> datetime-add

* Linting

* add datetime-subtract to `datetime-arithmetics?`

rework tests a bit as well.

* Update docstring

* Use ->temporal-type and trunc to handle report-timezone for bigquery

* Log errors caught during sync steps (#26306)

* log errors caught during sync steps

* remove accidental extra parens

* Errors combining datetime interval addition with datetime functions (#26279)

* Add failing tests

* Fix failing tests

* Update shared/src/metabase/mbql/util.cljc

Co-authored-by: Ngoc Khuat <qn.khuat@gmail.com>

* datetime-add and datetime subtract should annotate type by col type

* Fix infer-expression-type for datetime-add/subtract with second, minute, hour

* Undo last commit; they actually always return :type/DateTime

* Fix test based on last commit

* Undo unrelated refactor

* Only test drivers that support expressions

* Only test drivers that support expressions, again

* Update tests from legacy mbql

* Change infered-col-type to be a function again, not macro

* Fix test

Co-authored-by: Ngoc Khuat <qn.khuat@gmail.com>

* whitespace

* Remove comments

* Add explanation for datetime_diff

* Refactor: replace cast and add `mt/with-driver :bigquery-cloud-sdk` where report-timezone is relevant

* Add failing tests

* Fix failing tests

* Technically it should be bigquery-type

* Update modules/drivers/bigquery-cloud-sdk/src/metabase/driver/bigquery_cloud_sdk.clj

Co-authored-by: Cam Saul <1455846+camsaul@users.noreply.github.com>

* Move documentation to metabase.mbql.schema

* Update DatetimeDiffUnits

Co-authored-by: Cam Saul <1455846+camsaul@users.noreply.github.com>

* Add error type and optimize case expressions to driver implementations

* refactor for brevity

* Fix error with postgres

* Fix error with postgres

* Handle string literal parsing in wrap-value-literals

* Remove ->timestamptz

* Add comments + TODOs showing arithmetic expressions should return numeric values

* Add explanation of arithmetic expression as docstring

* Add test for normalize-mbql-clause-tokens

* Remove unused import

* Remove unused form

* Switch tests to use attempted-murders dataset

* Undo optimization that broke tests

* Validate non-temporal types for bigquery

* Add error handling for incorrect types

* Fix mysql type checking

* Fix mysql type checking

* invalid-parameter -> invalid-query

* Use date-trunc and extract

* Remove unit error handling

* DatetimeLiteral -> DateOrDatetimeLiteral

* Remove unused binding

* Fix mysql type checking

Co-authored-by: Callum Herries <hi@callumherries.com>
Co-authored-by: Cal Herries <39073188+calherries@users.noreply.github.com>
Co-authored-by: metamben <103100869+metamben@users.noreply.github.com>
Co-authored-by: Noah Moss <32746338+noahmoss@users.noreply.github.com>
Co-authored-by: Ngoc Khuat <qn.khuat@gmail.com>
Co-authored-by: Cam Saul <1455846+camsaul@users.noreply.github.com>

- Add tests to check when bucketing by week and week-of-year behave consistently, Closes #4910
- While doing the above, found that sqlserver does not respect `start-of-week` setting, so fix that. Fix #25356

* Adding parameters based on the comment

* add tests

Co-authored-by: Ngoc Khuat <qn.khuat@gmail.com>

* Refactor: replace cast and add `mt/with-driver :bigquery-cloud-sdk` where report-timezone is relevant

* Add failing tests

* Fix failing tests

* Technically it should be bigquery-type

* [:field-id id] => [:field id nil] or $name. Except places where tests
are meant to check normalization

* [:datetime-field [:field id] unit] -> [:field id {:temporal-unit unit}]
or !unit.name

* [:fk-> [:field 1] [:field 2]] => [:field 2 {:source-field 1}]

* rename dateAdd to datetimeAdd and dateSubtract to datetimeSubtrac

* Reset changes and go thru everything again to make sure it makes sense

* Test fixes 

* Implement advanced date/time/zone manipulation, part 1

Incorporate new functions into MBQL and add tests:
 - get-year
 - get-quarter
 - get-month
 - get-day
 - get-day-of-week
 - get-hour
 - get-minute
 - get-second

* Fix BigQuery implementations to call extract

Mark as not supported in legacy driver

* Add date extraction fns for Postgres

* Disable in MongoDB (for now at least)

Disable in BigQuery (legacy driver)

Add implementations for presto-jdbc

* Misc cleanup from Jeff's PR

* Update Jeff's implementation of bigquery-cloud-sqk

* Reorganized tests

* Mongo

* Oracle

* Sqlserver

* Sqlite

* Add casting supports for presto

* Remove Jeff's implementation of presto-jdbc because  its parent is
sql-jdbc

* Update presto-jdbc tests to use the same catalog for all datasets

* Add date extraction functions to the expression editor (#25382)

* make sure the semantic type of aggregated columns are integer

* no recursive call in annotate for date-extract func

* get-unit -> temporal-extract(column, unit)

* desguar nested datetime extraction too

* bump driver

* prototype of fix

* test

* add note to driver-changelog.md

* [Toucan 2 prep] Don't invoke Toucan models as functions

* Some fixes

* Test fixes

* Test fix

* [Toucan 2 prep] Don't call `type` or `class` on Toucan models

* Test fixes

* More test fixes 

* [Toucan 2 prep] Don't invoke Toucan models as functions

* Some fixes

* Test fixes

* Fix some small things

* Add Kondo to deps.edn to be able to debug custom hooks from REPL

* Fix macroexpansion hook for with-temp* without values

* Test config (WIP)

* More misc fixes

* Disable :inline-def for tests

* More misc fixes

* Fix $ids and mbql-query kondo hooks.

* Fix with-temporary-setting-values with namespaced symbols

* More misc fixes

* Fix the rest of the easy ones

* Fix hook for mt/dataset

* Horrible hack to work around https://github.com/clj-kondo/clj-kondo/issues/1773 . Custom linter for mbql-query macro

* Fix places calling mbql-query with a keyword table name

* Fix the last few errors in test/

* Fix errors in enterprise/test and shared/test

* Fix driver test errors

* Enable linters on CI

* Enable unresolved-namespace linter for tests

* Appease the namespace linter again

* Test fixes

* Enable unused-binding linter for test/ => 293 warnings

* 259 warnings

* 234 warnings

* => 114 warnings

* Fix the rest of the unused binding warnings in test/

* Fix unused binding errors in enterprise/backend/test

* Fix unused binding lint errors in driver tests

* Test fix 

* Assure Kondo that something is in fact used

* Fix some small things

* Add Kondo to deps.edn to be able to debug custom hooks from REPL

* Fix macroexpansion hook for with-temp* without values

* Test config (WIP)

* More misc fixes

* Disable :inline-def for tests

* More misc fixes

* Fix $ids and mbql-query kondo hooks.

* Fix with-temporary-setting-values with namespaced symbols

* More misc fixes

* Fix the rest of the easy ones

* Fix hook for mt/dataset

* Horrible hack to work around https://github.com/clj-kondo/clj-kondo/issues/1773 . Custom linter for mbql-query macro

* Fix places calling mbql-query with a keyword table name

* Fix the last few errors in test/

* Fix errors in enterprise/test and shared/test

* Fix driver test errors

* Enable linters on CI

* Enable unresolved-namespace linter for tests

* Appease the namespace linter again

* Test fixes

* Enable clj-kondo for driver namespaces

* Fix CI command

* Fix CI config (?)

* Try hardcoding the driver directories in CI config

* Fix some busted stuff

* Enable the redundant fn wrapper linter

* Appease the namespace linter

add support for reference snippet with mongo

Breaking date values into parts should happen in the time zone expected by the user and the construction of the
truncated date should happen in the same timezone. This is especially important when the difference between the
expected timezone and UTC is not an integer number of hours and the truncation resolution is hour. (See #11149).

* Fix SSH tunnel leaks when testing connections

* Appease kondo =(

* Add some more NOTES

* bump snowflake

* Handle Types/TIMESTAMP_WITH_TIMEZONE for snowflake

Snowflake used to return columns of this type as `Types/TIMESTAMP` =
93.

The driver is registered with the legacy stuff

```clojure
(driver/register! :snowflake,
                  :parent #{:sql-jdbc
                            ::sql-jdbc.legacy/use-legacy-classes-for-read-and-set})
```

causing it to hit the following codepath:

```clojure
(defmethod sql-jdbc.execute/read-column-thunk [::use-legacy-classes-for-read-and-set Types/TIMESTAMP]
  [_ ^ResultSet rs _ ^Integer i]
  (fn []
    (when-let [s (.getString rs i)]
      (let [t (u.date/parse s)]
        (log/tracef "(.getString rs i) [TIMESTAMP] -> %s -> %s" (pr-str s) (pr-str t))
        t))))
```

But snowflake now reports the column as `Types/TIMESTAMP_WITH_TIMEZONE`
= 2014 in https://github.com/snowflakedb/snowflake-jdbc/pull/934/files
we no longer hit this string based path for the timestamp with timezone
pathway. Instead it hits

```clojure
(defn- get-object-of-class-thunk [^ResultSet rs, ^long i, ^Class klass]
  ^{:name (format "(.getObject rs %d %s)" i (.getCanonicalName klass))}
  (fn []
    (.getObject rs i klass)))

,,,

(defmethod read-column-thunk [:sql-jdbc Types/TIMESTAMP_WITH_TIMEZONE]
  [_ rs _ i]
  (get-object-of-class-thunk rs i java.time.OffsetDateTime))
```

And `(.getObject ...)` blows up with an unsupported exception.

```java
  // @Override
  public <T> T getObject(int columnIndex, Class<T> type) throws SQLException {
    logger.debug("public <T> T getObject(int columnIndex,Class<T> type)", false);

    throw new SnowflakeLoggedFeatureNotSupportedException(session);
  }
```

There seem to be some `getTimetamp` methods on the
`SnowflakeBaseResultSet` that we could call but for now I'm just
grabbing the string and we'll parse on our side.

One style note, it is not quite clear to me if this should follow the
pattern established in `snowflake.clj` driver of setting `defmethod
sql-jdbc.execute/read-column-thunk [:snowflake
Types/TIMESTAMP_WITH_TIMEZONE]` or if it should follow the legacy
pattern of `defmethod sql-jdbc.execute/read-column-thunk
[::use-legacy-classes-for-read-and-set Types/TIMESTAMP]`. It seems like
almost either would be fine. Just depends on whether other legacy
drivers might like this fix which seems possible?

This parameter makes the impression that Metabase can deal with encrypted
private keys, but this is not the case. The internal keystore password can just
as well be randomly generated in the code.

* `:bulk/update`

* Some refactoring. Add error handling and tests for `:bulk/update`

* Remove unused namespace

* PR feedback and some bug fixes

* Make sure bulk/delete fails if you have all PK columns and also non-PK columns

* Implement caching mechanism for Actions

* Fix more `bulk/delete` tests for Postgres

* Disable kondo error for `u/key-by` being deprecated

* Fix duplicate test setup code in `bulk/delete` tests

* Fix CircleCI job skipping magic: `$CIRCLE_STAGE` has been renamed to `$CIRCLE_JOB`

* Fix Presto JDBC failures from `NOT NULL` column

* Fix GA Card Save expected status code

* Fix error message differences between Postgres versions

* Update Snowflake DDL statements tests

* Fix redundant `let` expressions

* handle different error messages for different PG versions

* Fix CircleCI job skipping magic: `$CIRCLE_STAGE` has been renamed to `$CIRCLE_JOB`

* Bust cache if `$CIRCLE_JOB` is unset

* Print message if `$CIRCLE_JOB` is unset and we're busting the cache

* Fix GoogleAnalytics status code

This is to enable is-empty and non-empty tests on :type/MongoBSONID columns.

Fix compilation of temporal arithmetic for BigQuery and Mongo 5+

* Mongo 4 doesn't support $dateAdd so the generated filters result in an exception.
* Support adding field to interval too (time intervals were not allowed in the first place of an addition)
* Support temporal arithmetic with more than two operands for Mongo

* Add database_required to field metadata

The purpose of this new column is meant to track if the database believes
that the column is required during writeback. It may be that the user
knows better but that is out of scope and would need
another column so that sync never overwrites what a user would set.

Since writeback isn't planned to be added to non transactional-dbs yet
we do not need to calculate requiredness in other drivers.

This could become more sophisticated in the future (look at triggers
that modify the column) simply looking for not-null and non-default
columns gets us a big win in UI.

* Fixing tests

* Fixing more tests

* Default updates to false

* Add database-required to field sync metadata test

* Update driver tests

* Fix tests

* Changes from testing on different dbs

https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)
Based on these descriptions of `getColumns` and testing across dbs made
the following changes:

`COLUMN_DEF` may or may not show autoincrement defaults
`COLUMN_DEF` may be `nil` or `"NULL"` and the docs say it may be `"null"`
`NULLABLE` is an int value, `0` indicating not null

NULLABLE and AUTOINCREMENT may be unknown (`3`, `""`) in those cases the
field will not be marked as required.

* Updating tests from changes

* Fix driver tests

* Cypress tests had fk field ids hardcoded

* Correctly handle fall-through case.

Condp is exhaustive and throws if nothing matches

```
mysql=> (condp = 2
          1 :nope
          3 :nope)
Execution error (IllegalArgumentException) at metabase.driver.mysql/eval169387 (REPL:870).
No matching clause: 2
mysql=>
```

and can take a single default clause at the end with no predicate

```clojure
mysql=> (condp = 2
          1 :nope
          :default-catch-all)
:default-catch-all
```

This was attempted with a regex `#".*"` but this regex does not match
everything!

```clojure
mysql=> (driver/humanize-connection-error-message :mysql
                                                  "hello\nthere")
Execution error (IllegalArgumentException) at metabase.driver.mysql/eval167431$fn (mysql.clj:124).
No matching clause: hello
there
mysql=> (re-matches #".*" "hi\nthere")
nil
```

So just use use the message as the default to be returned which was the
original intention.

* Fix other databases as well

We have already had support for server authentication based on custom
certificates. This change adds support for authenticating the client
based on custom client key and certificate.

CVE info:
Package: com.google.oauth-client:google-oauth-client
Installed Version: 1.31.5
Vulnerability CVE-2021-22573
Severity: HIGH
Fixed Version: 1.33.3

```
  . metabase/bigquery-cloud-sdk /Users/dan/projects/work/metabase/modules/drivers/bigquery-cloud-sdk
    . com.google.cloud/google-cloud-bigquery 1.135.4
      . [truncated]
      . com.google.oauth-client/google-oauth-client 1.31.5

  . metabase/googleanalytics /Users/dan/projects/work/metabase/modules/drivers/googleanalytics
    . com.google.apis/google-api-services-analytics v3-rev20190807-1.32.1
      . com.google.api-client/google-api-client 1.32.1
        . com.google.oauth-client/google-oauth-client 1.31.5
```

I looked into bumping
com.google.apis/google-api-services-analytics-v3-rev20190807-1.32.1
but as far as I can tell from
https://search.maven.org/artifact/com.google.apis/google-api-services-analytics
this is the most recent version so we have to just target the transitive
dep.

For bigquery, it seems we are pretty far behind. 1.135.4 was released in
July 2021, the current version is 2.13.1 released in
June. https://mvnrepository.com/artifact/com.google.cloud/google-cloud-bigquery
I'm hesitant to bump this for a CVE but we need to prioritize this
upgrade.

After this PR:

```
clj -Stree -A:drivers

  . metabase/bigquery-cloud-sdk /Users/dan/projects/work/metabase/modules/drivers/bigquery-cloud-sdk
    . com.google.cloud/google-cloud-bigquery 1.135.4
      . [truncated]
      X com.google.oauth-client/google-oauth-client 1.31.5 :older-version

    . com.google.oauth-client/google-oauth-client 1.33.3
  . metabase/googleanalytics /Users/dan/projects/work/metabase/modules/drivers/googleanalytics
    . com.google.apis/google-api-services-analytics v3-rev20190807-1.32.1
      . com.google.api-client/google-api-client 1.32.1
        X com.google.oauth-client/google-oauth-client 1.31.5 :older-version
```

With the `X` meaning not included and 1.33.3 being top level included so
using that version.

An alert from trivy:

```
Package: com.google.code.gson:gson
Installed Version: 2.8.7
Vulnerability CVE-2022-25647
Severity: HIGH
Fixed Version: 2.8.9
Link: CVE-2022-25647
Trivy
```

running `clj -Sdeps` will not show this dep because it is in two
drivers. Instead running

```
clj A:ee:drivers
```

will find it.

```
. metabase/bigquery-cloud-sdk /Users/dan/projects/work/metabase/modules/drivers/bigquery-cloud-sdk
    . com.google.cloud/google-cloud-bigquery 1.135.4
      . com.google.code.gson/gson 2.8.7
```

and

```
  . metabase/googleanalytics /Users/dan/projects/work/metabase/modules/drivers/googleanalytics
    . com.google.apis/google-api-services-analytics v3-rev20190807-1.32.1
      . com.google.api-client/google-api-client 1.32.1
        . com.google.http-client/google-http-client-gson 1.39.2
          X com.google.code.gson/gson 2.8.6 :older-version
```

This shows: google analytics depends on 2.8.6 but it is not actually
used and bigquery-cloud-sdk depends on 2.8.7 which is the version that
we are ending up with. (The `X` means excluded from the jar with reason
being `:older-version`).

More info:

https://clojure.org/reference/dep_expansion#_tree_printing

```
Trees are built from the trace log and include all considered nodes. Included nodes are prefixed with .. Excluded nodes are prefixed with X. The end of the line will contain the reason code (some codes are suppressed). The current set of reason codes (subject to change) are:

    :new-top-dep - included as top dep (suppressed)

    :new-dep - included as new dep (suppressed)

    :same-version - excluded, same as currently selected dep (suppressed)

    :newer-version - included, newer version than previously selected

    :use-top - excluded, same as top lib but not at top

    :older-version - excluded, older version than previously selected

    :excluded - excluded, node in parent path excluded this lib

    :parent-omitted - excluded, parent node deselected

    :superseded - excluded, this version was deselected

```

THE FIX:

Just put a top level dependency on the version we care about. No need to
exclude the version. Technically only need it in one project as our
build would always use the specified version. But in case anyone builds
with just one or the other included in both for completeness with a
comment indicating the other location.

```clojure
com.google.code.gson/gson {:mvn/version "2.8.9"}
```

PROOF OF FIX:

clj -A:ee:drivers and look for gson

```
  . metabase/bigquery-cloud-sdk /Users/dan/projects/work/metabase/modules/drivers/bigquery-cloud-sdk
    . com.google.cloud/google-cloud-bigquery 1.135.4
      X com.google.code.gson/gson 2.8.7 :older-version
```

```
. metabase/googleanalytics /Users/dan/projects/work/metabase/modules/drivers/googleanalytics
    . com.google.apis/google-api-services-analytics v3-rev20190807-1.32.1
      . com.google.api-client/google-api-client 1.32.1
        . com.google.http-client/google-http-client-gson 1.39.2
          X com.google.code.gson/gson 2.8.6 :older-version
    . com.google.code.gson/gson 2.8.9
```

- 2.8.7 in bigquery-cloud-sdk now has an `X` and `:older-version`
- 2.8.6 in google analytics still has `X` and `:older-version`
- metabase/googleanalytics now has a top level (and included `.`) gson on 2.8.9