postgrest 0.5.0.0 → 5.1.0
raw patch · 23 files changed
+1002/−313 lines, 23 filesdep −safedep ~basedep ~hasqldep ~hasql-pool
Dependencies removed: safe
Dependency ranges changed: base, hasql, hasql-pool, hasql-transaction, jose, protolude
Files
- CHANGELOG.md +383/−0
- main/Main.hs +13/−20
- postgrest.cabal +22/−19
- src/PostgREST/ApiRequest.hs +1/−1
- src/PostgREST/App.hs +0/−1
- src/PostgREST/Auth.hs +2/−1
- src/PostgREST/Config.hs +7/−10
- src/PostgREST/DbRequestBuilder.hs +1/−1
- src/PostgREST/DbStructure.hs +59/−89
- src/PostgREST/Middleware.hs +6/−5
- src/PostgREST/OpenAPI.hs +12/−3
- src/PostgREST/Parsers.hs +19/−7
- src/PostgREST/QueryBuilder.hs +33/−23
- src/PostgREST/Types.hs +10/−2
- test/Feature/AndOrParamsSpec.hs +0/−4
- test/Feature/InsertSpec.hs +0/−10
- test/Feature/JsonOperatorSpec.hs +189/−0
- test/Feature/PgVersion95Spec.hs +55/−0
- test/Feature/QuerySpec.hs +75/−107
- test/Feature/RpcSpec.hs +10/−0
- test/Feature/StructureSpec.hs +71/−7
- test/Feature/UpsertSpec.hs +27/−0
- test/Main.hs +7/−3
+ CHANGELOG.md view
@@ -0,0 +1,383 @@+# Change Log++All notable changes to this project will be documented in this file.+This project adheres to [Semantic Versioning](http://semver.org/).++## Unreleased++### Added++### Fixed++## [5.1.0] - 2018-08-31++### Added++- #1099, Add support for getting json/jsonb by array index - @steve-chavez+- #1145, Add materialized view columns to OpenAPI output - @steve-chavez+- #709, Allow embedding on views with subselects/CTE - @steve-chavez+- #1148, OpenAPI: add `required` section for the non-nullable columns - @laughedelic+- #1158, Add summary to OpenAPI doc for RPC functions - @mdr1384++### Fixed++- #1113, Fix UPSERT failing when having a camel case PK column - @steve-chavez+- #945, Fix slow start-up time on big schemas - @steve-chavez+- #1129, Fix view embedding when table is capitalized - @steve-chavez+- #1149, OpenAPI: Change `GET` response type to array - @laughedelic+- #1152, Fix RPC failing when having arguments with reserved or uppercase keywords - @mdr1384+- #905, Fix intermittent empty replies - @steve-chavez+- #1139, Fix JWTIssuedAtFuture failure for valid iat claim - @steve-chavez+- #1141, Fix app.settings resetting on pool timeout - @steve-chavez++### Changed++- #1099, Numbers in json path `?select=data->1->>key` now get treated as json array indexes instead of keys - @steve-chavez+- #1128, Allow finishing a json path with a single arrow `->`. Now a json can be obtained without resorting to casting, Previously: `/json_arr?select=data->>2::json`, now: `/json_arr?select=data->2` - @steve-chavez+- #724, Change server-host default of *4 to 127.0.0.1++### Deprecated++- #724, SIGHUP deprecated, SIGUSR1 should be used instead++## [0.5.0.0] - 2018-05-14++### Added++- The configuration (e.g. `postgrest.conf`) now accepts arbitrary settings that will be passed through as session-local database settings. This can be used to pass in secret keys directly as strings, or via OS environment variables. For instance: `app.settings.jwt_secret = "$(MYAPP_JWT_SECRET)"` will take `MYAPP_JWT_SECRET` from the environment and make it available to postgresql functions as `current_setting('app.settings.jwt_secret')`. Only `app.settings.*` values in the configuration file are treated in this way. - @canadaduane+- #256, Add support for bulk UPSERT with POST and single UPSERT with PUT - @steve-chavez+- #1078, Add ability to specify source column in embed - @steve-chavez+- #821, Allow embeds alias to be used in filters - @steve-chavez+- #906, Add jspath configurable `role-claim-key` - @steve-chavez+- #1061, Add foreign tables to OpenAPI output - @rhyamada++### Fixed++- #828, Fix computed column only working in public schema - @steve-chavez+- #925, Fix RPC high memory usage by using parametrized query and avoiding json encoding - @steve-chavez+- #987, Fix embedding with self-reference foreign key - @steve-chavez+- #1044, Fix view parent embedding when having many views - @steve-chavez+- #781, Fix accepting misspelled desc/asc ordering modificators - @onporat, @steve-chavez++### Changed++- #828, A `SET SCHEMA <db-schema>` is done on each request, this has the following implications:+ - Computed columns now only work if they belong to the db-schema+ - Stored procedures might require a `search_path` to work properly, for further details see https://postgrest.org/en/v5.0/api.html#explicit-qualification+- To use RPC now the `json_to_record/json_to_recordset` functions are needed, these are available starting from PostgreSQL 9.4 - @steve-chavez+- Overloaded functions now depend on the `dbStructure`, restart/sighup may be needed for their correct functioning - @steve-chavez+- #1098, Removed support for:+ + curly braces `{}` in embeds, i.e. `/clients?select=*,projects{*}` can no longer be used, from now on parens `()` should be used `/clients?select=*,projects(*)` - @steve-chavez+ + "in" operator without parens, i.e. `/clients?id=in.1,2,3` no longer supported, `/clients?id=in.(1,2,3)` should be used - @steve-chavez+ + "@@", "@>" and "<@" operators, from now on their mnemonic equivalents should be used "fts", "cs" and "cd" respectively - @steve-chavez++## [0.4.4.0] - 2018-01-08++### Added++- #887, #601, #1007, Allow specifying dictionary and plain/phrase tsquery in full text search - @steve-chavez+- #328, Allow doing GET on rpc - @steve-chavez+- #917, Add ability to map RAISE errorcode/message to http status - @steve-chavez+- #940, Add ability to map GUC to http response headers - @steve-chavez+- #1022, Include git sha in version report - @begriffs+- Faster queries using json_agg - @ruslantalpa++### Fixed++- #876, Read secret files as binary, discard final LF if any - @eric-brechemier+- #968, Treat blank proxy uri as missing - @begriffs+- #933, OpenAPI externals docs url to current version - @steve-chavez+- #962, OpenAPI don't err on nonexistent schema - @steve-chavez+- #954, make OpenAPI rpc output dependent on user privileges - @steve-chavez+- #955, Support configurable aud claim - @statik+- #996, Fix embedded column conflicts table name - @grotsev+- #974, Fix RPC error when function has single OUT param - @steve-chavez+- #1021, Reduce join size in allColumns for faster program start - @nextstopsun+- #411, Remove the need for pk in &select for parent embed - @steve-chavez+- #1016, Fix anonymous requests when configured with jwt-aud - @ruslantalpa++## [0.4.3.0] - 2017-09-06++### Added++- #567, Support more JWT signing algorithms, including RSA - @begriffs+- #889, Allow more than two conditions in a single and/or - @steve-chavez+- #883, Binary output support for RPC - @steve-chavez+- #885, Postgres COMMENTs on SCHEMA/TABLE/COLUMN are used for OpenAPI - @ldesgoui+- #907, Ability to embed using a specific relation when there are multiple between tables - @ruslantalpa+- #930, Split table comment on newline to get OpenAPI operation summary and description - @daurnimator+- #938, Support for range operators - @russelldavies++### Fixed++- #877, Base64 secret read from a file ending with a newline - @eric-brechemier+- #896, Boolean env var interpolation in config file - @begriffs+- #885, OpenAPI repetition reduced by using more definitions- @ldesgoui+- #924, Improve relations initialization time - @9too+- #927, Treat blank pre-request as missing - @begriffs++### Changed++- #938, Deprecate symbol operators with mnemonic names. - @russelldavies++## [0.4.2.0] - 2017-06-11++### Added++- #742, Add connection retrying on startup and SIGHUP - @steve-chavez+- #652, Add and/or params for complex boolean logic - @steve-chavez+- #808, Env var interpolation in config file (helps Docker) - @begriffs+- #878 - CSV output support for RPC - @begriffs++### Fixed++- #822, Treat blank string JWT secret as no secret - @begriffs++## [0.4.1.0] - 2017-04-25++### Added+- Allow requesting binary output on GET - @steve-chavez+- Accept clients requesting `Content-Type: application/json` from / - @feynmanliang+- #493, Updating with empty JSON object makes zero updates @koulakis+- Make HTTP headers and cookies available as GUCs #800 - @ruslantalpa+- #701, Ability to quote values on IN filters - @steve-chavez+- #641, Allow IN filter to have no values - @steve-chavez++### Fixed+- #827, Avoid Warp reaper, extend socket timeout to 1 hour - @majorcode+- #791, malformed nested JSON error - @diogob+- Resource embedding in views referencing tables in public schema - @fab1an+- #777, Empty body is allowed when calling a non-parameterized RPC - @koulakis+- #831, Fix proc resource embedding issue with search_path - @steve-chavez+- #547, Use read-only transaction for stable/immutable RPC - @begriffs++## [0.4.0.0] - 2017-01-19++### Added+- Allow test database to be on another host - @dsimunic+- `Prefer: params=single-object` to treat payload as single json argument in RPC - @dsimunic+- Ability to generate an OpenAPI spec - @mainx07, @hudayou, @ruslantalpa, @begriffs+- Ability to generate an OpenAPI spec behind a proxy - @hudayou+- Ability to set addresses to listen on - @hudayou+- Filtering, shaping and embedding with &select for the /rpc path - @ruslantalpa+- Output names of used-defined types (instead of 'USER-DEFINED') - @martingms+- Implement support for singular representation responses for POST/PATCH requests - @ehamberg+- Include RPC endpoints in OpenAPI output - @begriffs, @LogvinovLeon+- Custom request validation with `--pre-request` argument - @begriffs+- Ability to order by jsonb keys - @steve-chavez+- Ability to specify offset for a deeper level - @ruslantalpa+- Ability to use binary base64 encoded secrets - @TrevorBasinger++### Fixed+- Do not apply limit to parent items - @ruslantalpa+- Fix bug in relation detection when selecting parents two levels up by using the name of the FK - @ruslantalpa+- Customize content negotiation per route - @begriffs+- Allow using nulls order without explicit order direction - @steve-chavez+- Fatal error on postgres unsupported version, format supported version in error message - @steve-chavez+- Prevent database memory cosumption by prepared statements caches - @ruslantalpa+- Use specific columns in the RETURNING section - @ruslantalpa+- Fix columns alias for RETURNING - @steve-chavez++### Changed+- Replace `Prefer: plurality=singular` with `Accept: application/vnd.pgrst.object` - @begriffs+- Standardize arrays in responses for `Prefer: return=representation` - @begriffs+- Calling unknown RPC gives 404, not 400 - @begriffs+- Use HTTP 400 for raise\_exception - @begriffs+- Remove non-OpenAPI schema description - @begriffs+- Use comma rather than semicolon to separate Prefer header values - @begriffs+- Omit total query count by default - @begriffs+- No more reserved `jwt_claims` return type - @begriffs+- HTTP 401 rather than 400 for expired JWT - @begriffs+- Remove default JWT secret - @begriffs+- Use GUC request.jwt.claim.foo rather than postgrest.claims.foo - @begriffs+- Use config file rather than command line arguments - @begriffs++## [0.3.2.0] - 2016-06-10++### Added+- Reload database schema on SIGHUP - @begriffs+- Support "-" in column names - @ruslantalpa+- Support column/node renaming `alias:column` - @ruslantalpa+- Accept posts from HTML forms - @begriffs+- Ability to order embedded entities - @ruslantalpa+- Ability to paginate using &limit and &offset parameters - @ruslantalpa+- Ability to apply limits to embedded entities and enforce --max-rows on all levels - @ruslantalpa, @begriffs+- Add allow response header in OPTIONS - @begriffs++### Fixed+- Return 401 or 403 for access denied rather than 404 - @begriffs+- Omit Content-Type header for empty body - @begriffs+- Prevent role from being changed twice - @begriffs+- Use read-only transaction for read requests - @ruslantalpa+- Include entities from the same parent table using two different foreign keys - @ruslantalpa+- Ensure that Location header in 201 response is URL-encoded - @league+- Fix garbage collector CPU leak - @ruslantalpa et al.+- Return deleted items when return=representation header is sent - @ruslantalpa+- Use table default values for empty object inserts - @begriffs++## [0.3.1.1] - 2016-03-28++### Fixed+- Preserve unicode values in insert,update,rpc (regression) - @begriffs+- Prevent duplicate call to stored procs (regression) - @begriffs+- Allow SQL functions to generate registered JWT claims - @begriffs+- Terminate gracefully on SIGTERM (for use in Docker) - @recmo+- Relation detection fix for views that depend on multiple tables - @ruslantalpa+- Avoid count on plurality=singular and allow multiple Prefer values - @ruslantalpa++## [0.3.1.0] - 2016-02-28++### Fixed+- Prevent query error from infecting later connection - @begriffs, @ruslantalpa, @nikita-volkov, @jwiegley++### Added+- Applies range headers to RPC calls - @diogob++## [0.3.0.4] - 2016-02-12++### Fixed+- Improved usage screen - @begriffs+- Reject non-POSTs to rpc endpoints - @begriffs+- Throw an error for OPTIONS on nonexistent tables - @calebmer+- Remove deadlock on simultaneous contentious updates - @ruslantalpa, @begriffs++## [0.3.0.3] - 2016-01-08++### Fixed+- Fix bug in many-many relation detection - @ruslantalpa+- Inconsistent escaping of table names in read queries - @calebmer++## [0.3.0.2] - 2015-12-16++### Fixed+- Miscalculation of time used for expiring tokens - @calebmer+- Remove bcrypt dependency to fix Windows build - @begriffs+- Detect relations event when authenticator does not have rights to intermediate tables - @ruslantalpa+- Ensure db connections released on sigint - @begriffs+- Fix #396 include records with missing parents - @ruslantalpa+- `pgFmtIdent` always quotes #388 - @calebmer+- Default schema, changed from `"1"` to `public` - @calebmer+- #414 revert to separate count query - @ruslantalpa+- Fix #399, allow inserting in tables with no select privileges using "Prefer: representation=minimal" - @ruslantalpa++### Added+- Allow order by computed columns - @diogob+- Set max rows in response with --max-rows - @begriffs+- Selection by column name (can detect if `_id` is not included) - @calebmer++## [0.3.0.1] - 2015-11-27++### Fixed+- Filter columns on embedded parent items - @ruslantalpa++## [0.3.0.0] - 2015-11-24++### Fixed+- Use reasonable amount of memory during bulk inserts - @begriffs++### Added+- Ensure JWT expires - @calebmer+- Postgres connection string argument - @calebmer+- Encode JWT for procs that return type `jwt_claims` - @diogob+- Full text operators `@>`,`<@` - @ruslantalpa+- Shaping of the response body (filter columns, embed relations) with &select parameter for POST/PATCH - @ruslantalpa+- Detect relationships between public views and private tables - @calebmer+- `Prefer: plurality=singular` for selecting single objects - @calebmer++### Removed+- API versioning feature - @calebmer+- `--db-x` command line arguments - @calebmer+- Secure flag - @calebmer+- PUT request handling - @ruslantalpa++### Changed+- Embed foreign keys with {} rather than () - @begriffs+- Remove version number from binary filename in release - @begriffs++## [0.2.12.1] - 2015-11-12++### Fixed+- Correct order for -> and ->> in a json path - @ruslantalpa+- Return empty array instead of 500 when a set returning function returns an empty result set - @diogob++## [0.2.12.0] - 2015-10-25++### Added+- Embed associations, e.g. `/film?select=*,director(*)` - @ruslantalpa+- Filter columns, e.g. `?select=col1,col2` - @ruslantalpa+- Does not execute the count total if header "Prefer: count=none" - @diogob++### Fixed+- Tolerate a missing role in user creation - @calebmer+- Avoid unnecessary text re-encoding - @ruslantalpa++## [0.2.11.1] - 2015-09-01++### Fixed+- Accepts `*/*` in Accept header - @diogob++## [0.2.11.0] - 2015-08-28+### Added+- Negate any filter in a uniform way, e.g. `?col=not.eq=foo` - @diogob+- Call stored procedures+- Filter NOT IN values, e.g. `?col=notin.1,2,3` - @rall+- CSV responses to GET requests with `Accept: text/csv` - @diogob+- Debian init scripts - @mkhon+- Allow filters by computed columns - @diogob++### Fixed+- Reset user role on error+- Compatible with Stack+- Add materialized views to results in GET / - @diogob+- Indicate insertable=true for views that are insertable through triggers - @diogob+- Builds under GHC 7.10+- Allow the use of columns named "count" in relations queried - @diogob++## [0.2.10.0] - 2015-06-03+### Added+- Full text search, eg `/foo?text_vector=@@.bar`+- Include auth id as well as db role to views (for row-level security)++## [0.2.9.1] - 2015-05-20+### Fixed+- Put -Werror behind a cabal flag (for CI) so Hackage accepts package++## [0.2.9.0] - 2015-05-20+### Added+- Return range headers in PATCH+- Return PATCHed resources if header "Prefer: return=representation"+- Allow nested objects and arrays in JSON post for jsonb columns+- JSON Web Tokens - [Federico Rampazzo](https://github.com/framp)+- Expose PostgREST as a Haskell package++### Fixed+- Return 404 if no records updated by PATCH++## [0.2.8.0] - 2015-04-17+### Added+- Option to specify nulls first or last, eg `/people?order=age.desc.nullsfirst`+- Filter nulls, `?col=is.null` and `?col=isnot.null`+- Filter within jsonb, `?col->a->>b=eq.c`+- Accept CSV in post body for bulk inserts++### Fixed+- Allow NULL values in posts+- Show full command line usage on param errors++## [0.2.7.0] - 2015-03-03+### Added+- Server response logging+- Filter IN values, e.g. `?col=in.1,2,3`+- Return POSTed resource if header "Prefer: return=representation"+- Allow override of default (v1) schema++## [0.2.6.0] - 2015-02-18+### Added+- A changelog+- Filter by substring match, e.g. `?col=like.*hello*` (or ilike for+ case insensitivity).+- Access-Control-Expose-Headers for CORS++### Fixed+- Make filter position match docs, e.g. `?order=col.asc` rather+ than `?order=asc.col`.
main/Main.hs view
@@ -7,8 +7,7 @@ import PostgREST.Config (AppConfig (..), minimumPgVersion, prettyVersion, readOptions)-import PostgREST.DbStructure (getDbStructure, getPgVersion,- fillSessionWithSettings)+import PostgREST.DbStructure (getDbStructure, getPgVersion) import PostgREST.Error (encodeError) import PostgREST.OpenAPI (isMalformedProxyUri) import PostgREST.Types (DbStructure, Schema, PgVersion(..))@@ -33,8 +32,7 @@ import qualified Hasql.Session as H import Network.Wai.Handler.Warp (defaultSettings, runSettings, setHost,- setPort, setServerName,- setTimeout)+ setPort, setServerName) import System.IO (BufferMode (..), hSetBuffering) @@ -64,11 +62,10 @@ :: ThreadId -- ^ This thread is killed if pg version is unsupported -> P.Pool -- ^ The PostgreSQL connection pool -> Schema -- ^ Schema PostgREST is serving up- -> [(Text, Text)] -- ^ Settings or Environment passed in through the config -> IORef (Maybe DbStructure) -- ^ mutable reference to 'DbStructure' -> IORef Bool -- ^ Used as a binary Semaphore -> IO ()-connectionWorker mainTid pool schema settings refDbStructure refIsWorkerOn = do+connectionWorker mainTid pool schema refDbStructure refIsWorkerOn = do isWorkerOn <- readIORef refIsWorkerOn unless isWorkerOn $ do atomicWriteIORef refIsWorkerOn True@@ -86,7 +83,6 @@ ("Cannot run in this PostgreSQL version, PostgREST needs at least " <> pgvName minimumPgVersion) killThread mainTid- fillSessionWithSettings settings dbStructure <- getDbStructure schema actualPgVersion liftIO $ atomicWriteIORef refDbStructure $ Just dbStructure case result of@@ -152,8 +148,7 @@ appSettings = setHost ((fromString . toS) host) -- Warp settings . setPort port- . setServerName (toS $ "postgrest/" <> prettyVersion)- . setTimeout 3600 $+ . setServerName (toS $ "postgrest/" <> prettyVersion) $ defaultSettings -- Checks that the provided proxy uri is formated correctly@@ -186,7 +181,6 @@ mainTid pool (configSchema conf)- (configSettings conf) refDbStructure refIsWorkerOn --@@ -204,15 +198,15 @@ throwTo mainTid UserInterrupt ) Nothing - void $ installHandler sigHUP (- Catch $ connectionWorker- mainTid- pool- (configSchema conf)- (configSettings conf)- refDbStructure- refIsWorkerOn- ) Nothing+ forM_ [sigHUP, sigUSR1] $ \sig ->+ void $ installHandler sig (+ Catch $ connectionWorker+ mainTid+ pool+ (configSchema conf)+ refDbStructure+ refIsWorkerOn+ ) Nothing #endif @@ -230,7 +224,6 @@ mainTid pool (configSchema conf)- (configSettings conf) refDbStructure refIsWorkerOn)
postgrest.cabal view
@@ -2,14 +2,16 @@ description: Reads the schema of a PostgreSQL database and creates RESTful routes for the tables and views, supporting all HTTP verbs that security permits.-version: 0.5.0.0+version: 5.1.0 synopsis: REST API for any Postgres database license: MIT license-file: LICENSE author: Joe Nelson, Adam Baker-homepage: https://github.com/PostgREST/postgrest-maintainer: cred+github@begriffs.com-category: Web+homepage: https://postgrest.org+maintainer: Steve Chávez <stevechavezast@gmail.com>+bug-reports: https://github.com/PostgREST/postgrest/issues+category: Executable, PostgreSQL, Network APIs+extra-source-files: CHANGELOG.md build-type: Simple cabal-version: >=1.10 source-repository head@@ -30,11 +32,11 @@ "-with-rtsopts=-N -I2" default-language: Haskell2010 build-depends: auto-update- , base- , hasql- , hasql-pool+ , base >= 4.8 && < 4.10+ , hasql == 1.1+ , hasql-pool == 0.4.3 , postgrest- , protolude+ , protolude == 0.2.2 , text , time , warp@@ -51,7 +53,7 @@ default-extensions: OverloadedStrings, QuasiQuotes, NoImplicitPrelude build-depends: aeson , ansi-wl-pprint- , base >= 4.8 && < 6+ , base >= 4.8 && < 4.10 , base64-bytestring , bytestring , case-insensitive@@ -62,24 +64,23 @@ , contravariant-extras , either , gitrev- , hasql- , hasql-pool- , hasql-transaction+ , hasql == 1.1+ , hasql-pool == 0.4.3+ , hasql-transaction == 0.5.2 , heredoc , HTTP , http-types , insert-ordered-containers , interpolatedstring-perl6- , jose+ , jose == 0.7.0.0 , lens , lens-aeson , network-uri , optparse-applicative >= 0.13 && < 0.15 , parsec- , protolude >= 0.2+ , protolude == 0.2.2 , Ranged-sets == 0.3.0 , regex-tdfa- , safe , scientific , swagger2 , text@@ -123,7 +124,9 @@ , Feature.CorsSpec , Feature.DeleteSpec , Feature.InsertSpec+ , Feature.JsonOperatorSpec , Feature.NoJwtSpec+ , Feature.PgVersion95Spec , Feature.PgVersion96Spec , Feature.ProxySpec , Feature.QueryLimitedSpec@@ -142,15 +145,15 @@ , aeson-qq , async , auto-update- , base+ , base >= 4.8 && < 4.10 , bytestring , base64-bytestring , case-insensitive , cassava , containers , contravariant- , hasql- , hasql-pool+ , hasql == 1.1+ , hasql-pool == 0.4.3 , heredoc , hjsonschema == 1.5.0.1 , hspec@@ -162,7 +165,7 @@ , monad-control , postgrest , process- , protolude+ , protolude == 0.2.2 , regex-tdfa , time , transformers-base
src/PostgREST/ApiRequest.hs view
@@ -105,7 +105,7 @@ userApiRequest schema req reqBody | isTargetingProc && method `notElem` ["GET", "POST"] = Left ActionInappropriate | topLevelRange == emptyRange = Left InvalidRange- | shouldParsePayload && isLeft payload = either (Left . InvalidBody . toS) undefined payload+ | shouldParsePayload && isLeft payload = either (Left . InvalidBody . toS) witness payload | otherwise = Right ApiRequest { iAction = action , iTarget = target
src/PostgREST/App.hs view
@@ -61,7 +61,6 @@ import Data.Function (id) import Protolude hiding (intercalate, Proxy)-import Safe (headMay) postgrest :: AppConfig -> IORef (Maybe DbStructure) -> P.Pool -> IO UTCTime -> IO () -> Application postgrest conf refDbStructure pool getTime worker =
src/PostgREST/Auth.hs view
@@ -20,6 +20,7 @@ ) where import Control.Lens.Operators+import Control.Lens (set) import qualified Data.Aeson as JSON import qualified Data.HashMap.Strict as M import Data.Time.Clock (UTCTime)@@ -48,7 +49,7 @@ case secret of Nothing -> return JWTMissingSecret Just s -> do- let validation = defaultJWTValidationSettings (maybe (const True) (==) audience)+ let validation = set allowedSkew 1 $ defaultJWTValidationSettings (maybe (const True) (==) audience) eJwt <- runExceptT $ do jwt <- decodeCompact payload verifyClaimsAt validation s time jwt
src/PostgREST/Config.hs view
@@ -132,7 +132,7 @@ <*> C.key "db-anon-role" <*> (mfilter (/= "") <$> C.key "server-proxy-uri") <*> C.key "db-schema"- <*> (fromMaybe "*4" . mfilter (/= "") <$> C.key "server-host")+ <*> (fromMaybe "127.0.0.1" . mfilter (/= "") <$> C.key "server-host") <*> (fromMaybe 3000 . join . fmap coerceInt <$> C.key "server-port") <*> (fmap encodeUtf8 . mfilter (/= "") <$> C.key "jwt-secret") <*> (fromMaybe False . join . fmap coerceBool <$> C.key "secret-is-base64")@@ -141,7 +141,7 @@ <*> (join . fmap coerceInt <$> C.key "max-rows") <*> (mfilter (/= "") <$> C.key "pre-request") <*> pure False- <*> (fmap parsedPairToTextPair <$> C.subassocs "app.settings")+ <*> (fmap (fmap coerceText) <$> C.subassocs "app.settings") <*> (maybe (Right [JSPKey "role"]) parseRoleClaimKey <$> C.key "role-claim-key") case mAppConf of@@ -152,13 +152,6 @@ return appConf where- parsedPairToTextPair :: (Name, Value) -> (Text, Text)- parsedPairToTextPair (k, v) = (k, newValue)- where- newValue = case v of- String textVal -> textVal- _ -> show v- parseJwtAudience :: Name -> C.ConfigParserM (Maybe StringOrURI) parseJwtAudience k = C.key k >>= \case@@ -168,6 +161,10 @@ (Just "") -> pure Nothing aud' -> pure aud' + coerceText :: Value -> Text+ coerceText (String s) = s+ coerceText v = show v+ coerceInt :: (Read i, Integral i) => Value -> Maybe i coerceInt (Number x) = rightToMaybe $ floatingOrInteger x coerceInt (String x) = readMaybe $ toS x@@ -209,7 +206,7 @@ |db-anon-role = "postgres" |db-pool = 10 |- |server-host = "*4"+ |server-host = "127.0.0.1" |server-port = 3000 | |## base url for swagger output
src/PostgREST/DbRequestBuilder.hs view
@@ -231,7 +231,7 @@ | typ == Many -> let ltN = fromMaybe "" (tableName <$> lt) in zipWith (toJoinCondition tN ltN) cols (fromMaybe [] lc1) ++ zipWith (toJoinCondition ftN ltN) fcs (fromMaybe [] lc2)- | typ == Root -> undefined+ | typ == Root -> witness where toJoinCondition :: Text -> Text -> Column -> Column -> JoinCondition toJoinCondition tb ftb c fc =
src/PostgREST/DbStructure.hs view
@@ -10,7 +10,6 @@ , accessibleProcs , schemaDescription , getPgVersion-, fillSessionWithSettings ) where import qualified Hasql.Decoders as HD@@ -33,14 +32,11 @@ import Protolude import Unsafe (unsafeHead) -import Data.Functor.Contravariant (contramap)-import Contravariant.Extras (contrazip2)- getDbStructure :: Schema -> PgVersion -> H.Session DbStructure getDbStructure schema pgVer = do tabs <- H.query () allTables cols <- H.query schema $ allColumns tabs- syns <- H.query () $ allSynonyms cols+ syns <- H.query schema $ allSynonyms cols childRels <- H.query () $ allChildRelations tabs cols keys <- H.query () $ allPrimaryKeys tabs procs <- H.query schema allProcs@@ -388,7 +384,7 @@ pg_catalog.pg_namespace n WHERE r.contype IN ('f', 'p')- AND c.relkind IN ('r', 'v', 'f', 'mv')+ AND c.relkind IN ('r', 'v', 'f', 'm') AND r.conrelid = c.oid AND c.relnamespace = n.oid AND n.nspname NOT IN ('pg_catalog', 'information_schema', $1)@@ -492,7 +488,7 @@ NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT a.attisdropped- AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char"]))+ AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char", 'm'::"char"])) AND (nc.nspname = $1 OR kc.r_oid IS NOT NULL) /*--filter only columns that are FK/PK or in the api schema */ /*--AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text))*/ )@@ -689,79 +685,67 @@ pkFromRow tabs (s, t, n) = PrimaryKey <$> table <*> pure n where table = find (\tbl -> tableSchema tbl == s && tableName tbl == t) tabs -allSynonyms :: [Column] -> H.Query () [Synonym]+allSynonyms :: [Column] -> H.Query Schema [Synonym] allSynonyms cols =- H.statement sql HE.unit (decodeSynonyms cols) True- where- -- query explanation at https://gist.github.com/ruslantalpa/2eab8c930a65e8043d8f- sql = [q|- with view_columns as (- select- c.oid as view_oid,- a.attname::information_schema.sql_identifier as column_name- from pg_attribute a- join pg_class c on a.attrelid = c.oid- join pg_namespace nc on c.relnamespace = nc.oid- where- not pg_is_other_temp_schema(nc.oid)- and a.attnum > 0- and not a.attisdropped- and (c.relkind = 'v'::"char")- and nc.nspname not in ('information_schema', 'pg_catalog')+ H.statement sql (HE.value HE.text) (decodeSynonyms cols) True+ -- query explanation at https://gist.github.com/steve-chavez/7ee0e6590cddafb532e5f00c46275569+ where sql = [q|+ with+ views as (+ select+ n.nspname as view_schema,+ c.relname as view_name,+ r.ev_action as view_definition+ from pg_class c+ join pg_namespace n on n.oid = c.relnamespace+ join pg_rewrite r on r.ev_class = c.oid+ where (c.relkind = 'v'::char) and n.nspname = $1 ),- view_column_usage as (- select distinct- v.oid as view_oid,- nv.nspname::information_schema.sql_identifier as view_schema,- v.relname::information_schema.sql_identifier as view_name,- nt.nspname::information_schema.sql_identifier as table_schema,- t.relname::information_schema.sql_identifier as table_name,- a.attname::information_schema.sql_identifier as column_name,- pg_get_viewdef(v.oid)::information_schema.character_data as view_definition- from pg_namespace nv- join pg_class v on nv.oid = v.relnamespace- join pg_depend dv on v.oid = dv.refobjid- join pg_depend dt on dv.objid = dt.objid- join pg_class t on dt.refobjid = t.oid- join pg_namespace nt on t.relnamespace = nt.oid- join pg_attribute a on t.oid = a.attrelid and dt.refobjsubid = a.attnum-- where- nv.nspname not in ('information_schema', 'pg_catalog')- and v.relkind = 'v'::"char"- and dv.refclassid = 'pg_class'::regclass::oid- and dv.classid = 'pg_rewrite'::regclass::oid- and dv.deptype = 'i'::"char"- and dv.refobjid <> dt.refobjid- and dt.classid = 'pg_rewrite'::regclass::oid- and dt.refclassid = 'pg_class'::regclass::oid- and (t.relkind = any (array['r'::"char", 'v'::"char", 'f'::"char"]))+ removed_subselects as(+ select+ view_schema, view_name,+ regexp_replace(view_definition, ':subselect {.*?:constraintDeps <>} :location', '', 'g') as x+ from views ),- candidates as (- select- vcu.*,- (- select case when match is not null then coalesce(match[8], match[7], match[4]) end- from regexp_matches(- CONCAT('SELECT ', SPLIT_PART(vcu.view_definition, 'SELECT', 2)),- CONCAT('SELECT.*?((',vcu.table_name,')|(\w+))\.(', vcu.column_name, ')(\s+AS\s+("([^"]+)"|([^, \n\t]+)))?.*?FROM.*?(',vcu.table_schema,'\.|)(\2|',vcu.table_name,'\s+(as\s)?\3)'),- 'nsi'- ) match- ) as view_column_name- from view_column_usage as vcu+ target_lists as(+ select+ view_schema, view_name,+ regexp_split_to_array(x, 'targetList') as x+ from removed_subselects+ ),+ last_target_list_wo_tail as(+ select+ view_schema, view_name,+ (regexp_split_to_array(x[array_upper(x, 1)], ':onConflict'))[1] as x+ from target_lists+ ),+ target_entries as(+ select+ view_schema, view_name,+ unnest(regexp_split_to_array(x, 'TARGETENTRY')) as entry+ from last_target_list_wo_tail+ ),+ results as(+ select+ view_schema, view_name,+ substring(entry from ':resname (.*?) :') as view_colum_name,+ substring(entry from ':resorigtbl (.*?) :') as resorigtbl,+ substring(entry from ':resorigcol (.*?) :') as resorigcol+ from target_entries ) select- c.table_schema,- c.table_name,- c.column_name as table_column_name,- c.view_schema,- c.view_name,- c.view_column_name- from view_columns as vc, candidates as c- where- vc.view_oid = c.view_oid- and vc.column_name = c.view_column_name- order by c.view_schema, c.view_name, c.table_name, c.view_column_name+ sch.nspname as table_schema,+ tbl.relname as table_name,+ col.attname as table_column_name,+ res.view_schema,+ res.view_name,+ res.view_colum_name+ from results res+ join pg_class tbl on tbl.oid::text = res.resorigtbl+ join pg_attribute col on col.attrelid = tbl.oid and col.attnum::text = res.resorigcol+ join pg_namespace sch on sch.oid = tbl.relnamespace+ where resorigtbl <> '0'+ order by view_schema, view_name, view_colum_name; |] synonymFromRow :: [Column] -> (Text,Text,Text,Text,Text,Text) -> Maybe Synonym@@ -776,17 +760,3 @@ where sql = "SELECT current_setting('server_version_num')::integer, current_setting('server_version')" versionRow = HD.singleRow $ PgVersion <$> HD.value HD.int4 <*> HD.value HD.text--fillSessionWithSettings :: [(Text, Text)] -> H.Session ()-fillSessionWithSettings settings =- -- Send all of the config settings to the set_config function, using pgsql's `unnest` to transform arrays of values- H.query settings $ H.statement "SELECT set_config(k, v, false) FROM unnest($1, $2) AS f1(k, v)" encoder HD.unit False-- where- -- Take a list of (key, value) pairs and encode each as an array to later bind to the query- -- see Insert Many section at https://hackage.haskell.org/package/hasql-1.1.1/docs/Hasql-Encoders.html- encoder = contramap L.unzip $ contrazip2 (vector HE.text) (vector HE.text)- where- vector value =- HE.value $ HE.array $ HE.arrayDimension foldl' $ HE.arrayValue value-
src/PostgREST/Middleware.hs view
@@ -19,7 +19,7 @@ import PostgREST.Auth (JWTAttempt(..)) import PostgREST.Config (AppConfig (..), corsPolicy) import PostgREST.Error (simpleError)-import PostgREST.QueryBuilder (pgFmtLit, unquoted, pgFmtEnvVar)+import PostgREST.QueryBuilder (pgFmtLit, unquoted, pgFmtSetLocal) import Protolude hiding (concat, null) @@ -32,13 +32,14 @@ JWTInvalid e -> return $ unauthed $ show e JWTMissingSecret -> return $ simpleError status500 [] "Server lacks JWT secret" JWTClaims claims -> do- H.sql $ toS.mconcat $ setSchemaSql ++ setRoleSql ++ claimsSql ++ headersSql ++ cookiesSql+ H.sql $ toS.mconcat $ setSchemaSql ++ setRoleSql ++ claimsSql ++ headersSql ++ cookiesSql ++ appSettingsSql mapM_ H.sql customReqCheck app req where- headersSql = map (pgFmtEnvVar "request.header.") $ iHeaders req- cookiesSql = map (pgFmtEnvVar "request.cookie.") $ iCookies req- claimsSql = map (pgFmtEnvVar "request.jwt.claim.") [(c,unquoted v) | (c,v) <- M.toList claimsWithRole]+ headersSql = pgFmtSetLocal "request.header." <$> iHeaders req+ cookiesSql = pgFmtSetLocal "request.cookie." <$> iCookies req+ claimsSql = pgFmtSetLocal "request.jwt.claim." <$> [(c,unquoted v) | (c,v) <- M.toList claimsWithRole]+ appSettingsSql = pgFmtSetLocal mempty <$> configSettings conf setRoleSql = maybeToList $ (\r -> "set local role " <> r <> ";") . toS . pgFmtLit . unquoted <$> M.lookup "role" claimsWithRole setSchemaSql = ["set schema " <> pgFmtLit (configSchema conf) <> ";"] :: [Text]
src/PostgREST/OpenAPI.hs view
@@ -42,7 +42,8 @@ (tn, (mempty :: Schema) & description .~ tableDescription t & type_ .~ SwaggerObject- & properties .~ fromList (map (makeProperty pks) cs))+ & properties .~ fromList (map (makeProperty pks) cs)+ & required .~ map colName (filter (not . colNullable) cs)) makeProperty :: [PrimaryKey] -> Column -> (Text, Referenced Schema) makeProperty pks c = (colName c, Inline s)@@ -197,8 +198,11 @@ & at 206 ?~ "Partial Content" & at 200 ?~ Inline ((mempty :: Response) & description .~ "OK"- & schema ?~ (Ref $ Reference $ tableName t)+ & schema ?~ Inline (mempty+ & type_ .~ SwaggerArray+ & items ?~ (SwaggerItemsObject $ Ref $ Reference $ tableName t) )+ ) postOp = tOp & parameters .~ map ref ["body." <> tn, "preferReturn"] & at 201 ?~ "Created"@@ -219,8 +223,13 @@ makeProcPathItem :: ProcDescription -> (FilePath, PathItem) makeProcPathItem pd = ("/rpc/" ++ toS (pdName pd), pe) where+ -- Use first line of proc description as summary; rest as description (if present)+ -- We strip leading newlines from description so that users can include a blank line between summary and description+ (pSum, pDesc) = fmap fst &&& fmap (dropWhile (=='\n') . snd) $+ breakOn "\n" <$> pdDescription pd postOp = (mempty :: Operation)- & description .~ pdDescription pd+ & summary .~ pSum+ & description .~ mfilter (/="") pDesc & parameters .~ makeProcParam pd & tags .~ Set.fromList ["(rpc) " <> pdName pd] & produces ?~ makeMimeList [CTApplicationJSON, CTSingularJSON]
src/PostgREST/Parsers.hs view
@@ -57,7 +57,7 @@ pTreePath :: Parser (EmbedPath, Field) pTreePath = do p <- pFieldName `sepBy1` pDelimiter- jp <- optionMaybe pJsonPath+ jp <- option [] pJsonPath return (init p, (last p, jp)) pFieldForest :: Parser [Tree SelectItem]@@ -80,14 +80,26 @@ dash :: Parser Char dash = isDash *> pure '-' -pJsonPathStep :: Parser Text-pJsonPathStep = toS <$> try (string "->" *> pFieldName)+pJsonPath :: Parser JsonPath+pJsonPath = many pJsonOperation+ where+ pJsonOperation :: Parser JsonOperation+ pJsonOperation = pJsonArrow <*> pJsonOperand -pJsonPath :: Parser [Text]-pJsonPath = (<>) <$> many pJsonPathStep <*> ( (:[]) <$> (string "->>" *> pFieldName) )+ pJsonArrow =+ try (string "->>" $> J2Arrow) <|>+ try (string "->" $> JArrow) + pJsonOperand =+ let pJKey = JKey . toS <$> pFieldName+ pJIdx = JIdx . toS <$> ((:) <$> option '+' (char '-') <*> many1 digit) <* pEnd+ pEnd = try (void $ lookAhead (string "->")) <|>+ try (void $ lookAhead (string "::")) <|>+ try eof in+ try pJIdx <|> try pJKey+ pField :: Parser Field-pField = lexeme $ (,) <$> pFieldName <*> optionMaybe pJsonPath+pField = lexeme $ (,) <$> pFieldName <*> option [] pJsonPath aliasSeparator :: Parser () aliasSeparator = char ':' >> notFollowedBy (char ':')@@ -112,7 +124,7 @@ ) <|> do s <- pStar- return ((s, Nothing), Nothing, Nothing, Nothing)+ return ((s, []), Nothing, Nothing, Nothing) pOpExpr :: Parser SingleVal -> Parser OpExpr pOpExpr pSVal = try ( string "not" *> pDelimiter *> (OpExpr True <$> pOperation)) <|> OpExpr False <$> pOperation
src/PostgREST/QueryBuilder.hs view
@@ -1,7 +1,8 @@-{-# LANGUAGE FlexibleInstances #-}-{-# LANGUAGE FlexibleContexts #-}-{-# LANGUAGE DuplicateRecordFields #-}-{-# OPTIONS_GHC -fno-warn-orphans #-}+{-# LANGUAGE FlexibleInstances #-}+{-# LANGUAGE FlexibleContexts #-}+{-# LANGUAGE DuplicateRecordFields #-}+{-# LANGUAGE LambdaCase #-}+{-# OPTIONS_GHC -fno-warn-orphans #-} {-| Module : PostgREST.QueryBuilder Description : PostgREST SQL generating functions.@@ -22,7 +23,7 @@ , requestToCountQuery , unquoted , ResultsWithCount- , pgFmtEnvVar+ , pgFmtSetLocal ) where import qualified Hasql.Query as H@@ -172,9 +173,9 @@ unwords [ "_args_record AS (", "SELECT * FROM " <> (if isObject then "json_to_record" else "json_to_recordset") <> "($1)",- "AS _(" <> intercalate ", " ((\a -> pgaName a <> " " <> pgaType a) <$> pgArgs) <> ")",+ "AS _(" <> intercalate ", " ((\a -> pgFmtIdent (pgaName a) <> " " <> pgaType a) <$> pgArgs) <> ")", ")"]- , intercalate ", " ((\a -> pgaName a <> " := (SELECT " <> pgaName a <> " FROM _args_record)") <$> pgArgs))+ , intercalate ", " ((\a -> pgFmtIdent (pgaName a) <> " := (SELECT " <> pgFmtIdent (pgaName a) <> " FROM _args_record)") <$> pgArgs)) countResultF = if countTotal then "( "<> countQuery <> ")" else "null::bigint" :: Text _procName = qiName qi responseHeaders =@@ -207,7 +208,7 @@ else slashed requestToCountQuery :: Schema -> DbRequest -> SqlQuery-requestToCountQuery _ (DbMutate _) = undefined+requestToCountQuery _ (DbMutate _) = witness requestToCountQuery schema (DbRead (Node (Select{where_=logicForest}, (mainTbl, _, _, _, _)) _)) = unwords [ "SELECT pg_catalog.count(*)",@@ -272,7 +273,7 @@ --the following is just to remove the warning --getQueryParts is not total but requestToQuery is called only after addJoinConditions which ensures the only --posible relations are Child Parent Many- getQueryParts _ _ = undefined+ getQueryParts _ _ = witness requestToQuery schema _ (DbMutate (Insert mainTbl pkCols p@(PayloadJSON _ pType pKeys) onConflct logicForest returnings)) = unwords [ ("WITH " <> ignoredBody) `emptyOnFalse` not payloadIsEmpty,@@ -288,7 +289,7 @@ -- Only used for PUT ("WHERE " <> intercalate " AND " (pgFmtLogicTree (QualifiedIdentifier "" "_") <$> logicForest)) `emptyOnFalse` null logicForest], maybe "" (\x -> (- "ON CONFLICT(" <> intercalate ", " pkCols <> ") " <> case x of+ "ON CONFLICT(" <> intercalate ", " (pgFmtIdent <$> pkCols) <> ") " <> case x of IgnoreDuplicates -> "DO NOTHING" MergeDuplicates ->@@ -394,8 +395,8 @@ pgFmtField table (c, jp) = pgFmtColumn table c <> pgFmtJsonPath jp pgFmtSelectItem :: QualifiedIdentifier -> SelectItem -> SqlFragment-pgFmtSelectItem table (f@(_, jp), Nothing, alias, _) = pgFmtField table f <> pgFmtAs jp alias-pgFmtSelectItem table (f@(_, jp), Just cast, alias, _) = "CAST (" <> pgFmtField table f <> " AS " <> cast <> " )" <> pgFmtAs jp alias+pgFmtSelectItem table (f@(fName, jp), Nothing, alias, _) = pgFmtField table f <> pgFmtAs fName jp alias+pgFmtSelectItem table (f@(fName, jp), Just cast, alias, _) = "CAST (" <> pgFmtField table f <> " AS " <> cast <> " )" <> pgFmtAs fName jp alias pgFmtOrderTerm :: QualifiedIdentifier -> OrderTerm -> SqlFragment pgFmtOrderTerm qi ot = unwords [@@ -447,20 +448,29 @@ where notOp = if hasNot then "NOT" else "" pgFmtLogicTree qi (Stmnt flt) = pgFmtFilter qi flt -pgFmtJsonPath :: Maybe JsonPath -> SqlFragment-pgFmtJsonPath (Just [x]) = "->>" <> pgFmtLit x-pgFmtJsonPath (Just (x:xs)) = "->" <> pgFmtLit x <> pgFmtJsonPath ( Just xs )-pgFmtJsonPath _ = ""+pgFmtJsonPath :: JsonPath -> SqlFragment+pgFmtJsonPath = \case+ [] -> ""+ (JArrow x:xs) -> "->" <> pgFmtJsonOperand x <> pgFmtJsonPath xs+ (J2Arrow x:xs) -> "->>" <> pgFmtJsonOperand x <> pgFmtJsonPath xs+ where+ pgFmtJsonOperand (JKey k) = pgFmtLit k+ pgFmtJsonOperand (JIdx i) = pgFmtLit i <> "::int" -pgFmtAs :: Maybe JsonPath -> Maybe Alias -> SqlFragment-pgFmtAs Nothing Nothing = ""-pgFmtAs (Just xx) Nothing = case lastMay xx of- Just alias -> " AS " <> pgFmtIdent alias+pgFmtAs :: FieldName -> JsonPath -> Maybe Alias -> SqlFragment+pgFmtAs _ [] Nothing = ""+pgFmtAs fName jp Nothing = case jOp <$> lastMay jp of+ Just (JKey key) -> " AS " <> pgFmtIdent key+ Just (JIdx _) -> " AS " <> pgFmtIdent (fromMaybe fName lastKey)+ -- We get the lastKey because on:+ -- `select=data->1->mycol->>2`, we need to show the result as [ {"mycol": ..}, {"mycol": ..} ]+ -- `select=data->3`, we need to show the result as [ {"data": ..}, {"data": ..} ]+ where lastKey = jVal <$> find (\case JKey{} -> True; _ -> False) (jOp <$> reverse jp) Nothing -> ""-pgFmtAs _ (Just alias) = " AS " <> pgFmtIdent alias+pgFmtAs _ _ (Just alias) = " AS " <> pgFmtIdent alias -pgFmtEnvVar :: Text -> (Text, Text) -> SqlFragment-pgFmtEnvVar prefix (k, v) =+pgFmtSetLocal :: Text -> (Text, Text) -> SqlFragment+pgFmtSetLocal prefix (k, v) = "set local " <> pgFmtIdent (prefix <> k) <> " = " <> pgFmtLit v <> ";" trimNullChars :: Text -> Text
src/PostgREST/Types.hs view
@@ -238,8 +238,16 @@ data LogicTree = Expr Bool LogicOperator [LogicTree] | Stmnt Filter deriving (Show, Eq) type FieldName = Text-type JsonPath = [Text]-type Field = (FieldName, Maybe JsonPath)+{-|+ Json path operations as specified in https://www.postgresql.org/docs/9.4/static/functions-json.html+-}+type JsonPath = [JsonOperation]+-- | Represents the single arrow `->` or double arrow `->>` operators+data JsonOperation = JArrow{jOp :: JsonOperand} | J2Arrow{jOp :: JsonOperand} deriving (Show, Eq)+-- | Represents the key(`->'key'`) or index(`->'1`::int`), the index is Text because we reuse our escaping functons and let pg do the casting with '1'::int+data JsonOperand = JKey{jVal :: Text} | JIdx{jVal :: Text} deriving (Show, Eq)++type Field = (FieldName, JsonPath) type Alias = Text type Cast = Text type NodeName = Text
test/Feature/AndOrParamsSpec.hs view
@@ -193,10 +193,6 @@ it "can query columns that begin with and/or reserved words" $ get "/grandchild_entities?or=(and_starting_col.eq.smth, or_starting_col.eq.smth)" `shouldRespondWith` 200 - it "can query jsonb columns" $- get "/grandchild_entities?or=(jsonb_col->a->>b.eq.foo, jsonb_col->>b.eq.bar)&select=id" `shouldRespondWith`- [json|[{id: 4}, {id: 5}]|] { matchStatus = 200, matchHeaders = [matchContentTypeJson] }- it "fails when using IN without () and provides meaningful error message" $ get "/entities?or=(id.in.1,2,id.eq.3)" `shouldRespondWith` [json|{
test/Feature/InsertSpec.hs view
@@ -390,16 +390,6 @@ [json| [{ a: "keepme", b: null }] |] { matchHeaders = [matchContentTypeJson] } - it "can set a json column to escaped value" $ do- _ <- post "/json" [json| { data: {"escaped":"bar"} } |]- request methodPatch "/json?data->>escaped=eq.bar"- [("Prefer", "return=representation")]- [json| { "data": { "escaped":" \"bar" } } |]- `shouldRespondWith` [json| [{ "data": { "escaped":" \"bar" } }] |]- { matchStatus = 200- , matchHeaders = []- }- it "can update based on a computed column" $ request methodPatch "/items?always_true=eq.false"
+ test/Feature/JsonOperatorSpec.hs view
@@ -0,0 +1,189 @@+module Feature.JsonOperatorSpec where++import Test.Hspec+import Test.Hspec.Wai+import Test.Hspec.Wai.JSON+import Network.HTTP.Types++import SpecHelper+import Network.Wai (Application)++import Protolude hiding (get)++spec :: SpecWith Application+spec = describe "json and jsonb operators" $ do+ context "Shaping response with select parameter" $ do+ it "obtains a json subfield one level with casting" $+ get "/complex_items?id=eq.1&select=settings->>foo::json" `shouldRespondWith`+ [json| [{"foo":{"int":1,"bar":"baz"}}] |] -- the value of foo here is of type "text"+ { matchHeaders = [matchContentTypeJson] }++ it "renames json subfield one level with casting" $+ get "/complex_items?id=eq.1&select=myFoo:settings->>foo::json" `shouldRespondWith`+ [json| [{"myFoo":{"int":1,"bar":"baz"}}] |] -- the value of foo here is of type "text"+ { matchHeaders = [matchContentTypeJson] }++ it "fails on bad casting (data of the wrong format)" $+ get "/complex_items?select=settings->foo->>bar::integer"+ `shouldRespondWith` [json| {"hint":null,"details":null,"code":"22P02","message":"invalid input syntax for integer: \"baz\""} |]+ { matchStatus = 400 , matchHeaders = [] }++ it "obtains a json subfield two levels (string)" $+ get "/complex_items?id=eq.1&select=settings->foo->>bar" `shouldRespondWith`+ [json| [{"bar":"baz"}] |]+ { matchHeaders = [matchContentTypeJson] }++ it "renames json subfield two levels (string)" $+ get "/complex_items?id=eq.1&select=myBar:settings->foo->>bar" `shouldRespondWith`+ [json| [{"myBar":"baz"}] |]+ { matchHeaders = [matchContentTypeJson] }++ it "obtains a json subfield two levels with casting (int)" $+ get "/complex_items?id=eq.1&select=settings->foo->>int::integer" `shouldRespondWith`+ [json| [{"int":1}] |] -- the value in the db is an int, but here we expect a string for now+ { matchHeaders = [matchContentTypeJson] }++ it "renames json subfield two levels with casting (int)" $+ get "/complex_items?id=eq.1&select=myInt:settings->foo->>int::integer" `shouldRespondWith`+ [json| [{"myInt":1}] |] -- the value in the db is an int, but here we expect a string for now+ { matchHeaders = [matchContentTypeJson] }++ -- TODO the status code for the error is 404, this is because 42883 represents undefined function+ -- this works fine for /rpc/unexistent requests, but for this case a 500 seems more appropriate+ it "fails when a double arrow ->> is followed with a single arrow ->" $ do+ get "/json_arr?select=data->>c->1"+ `shouldRespondWith` [json|+ {"hint":"No operator matches the given name and argument type(s). You might need to add explicit type casts.",+ "details":null,"code":"42883","message":"operator does not exist: text -> integer"} |]+ { matchStatus = 404 , matchHeaders = [] }+ get "/json_arr?select=data->>c->b"+ `shouldRespondWith` [json|+ {"hint":"No operator matches the given name and argument type(s). You might need to add explicit type casts.",+ "details":null,"code":"42883","message":"operator does not exist: text -> unknown"} |]+ { matchStatus = 404 , matchHeaders = [] }++ context "with array index" $ do+ it "can get array of ints and alias/cast it" $ do+ get "/json_arr?select=data->>0::int&id=in.(1,2)" `shouldRespondWith`+ [json| [{"data":1}, {"data":4}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=idx0:data->>0::int,idx1:data->>1::int&id=in.(1,2)" `shouldRespondWith`+ [json| [{"idx0":1,"idx1":2}, {"idx0":4,"idx1":5}] |]+ { matchHeaders = [matchContentTypeJson] }++ it "can get nested array of ints" $ do+ get "/json_arr?select=data->0->>1::int&id=in.(3,4)" `shouldRespondWith`+ [json| [{"data":8}, {"data":7}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data->0->0->>1::int&id=in.(3,4)" `shouldRespondWith`+ [json| [{"data":null}, {"data":6}] |]+ { matchHeaders = [matchContentTypeJson] }++ it "can get array of objects" $ do+ get "/json_arr?select=data->0->>a&id=in.(5,6)" `shouldRespondWith`+ [json| [{"a":"A"}, {"a":"[1,2,3]"}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data->0->a->>2&id=in.(5,6)" `shouldRespondWith`+ [json| [{"a":null}, {"a":"3"}] |]+ { matchHeaders = [matchContentTypeJson] }++ it "can get array in object keys" $ do+ get "/json_arr?select=data->c->>0::json&id=in.(7,8)" `shouldRespondWith`+ [json| [{"c":1}, {"c":{"d": [4,5,6,7,8]}}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data->c->0->d->>4::int&id=in.(7,8)" `shouldRespondWith`+ [json| [{"d":null}, {"d":8}] |]+ { matchHeaders = [matchContentTypeJson] }++ it "only treats well formed numbers as indexes" $+ get "/json_arr?select=data->0->0xy1->1->23-xy-45->1->xy-6->>0::int&id=eq.9" `shouldRespondWith`+ [json| [{"xy-6":3}] |]+ { matchHeaders = [matchContentTypeJson] }++ context "finishing json path with single arrow ->" $ do+ it "works when finishing with a key" $ do+ get "/json_arr?select=data->c&id=in.(7,8)" `shouldRespondWith`+ [json| [{"c":[1,2,3]}, {"c":[{"d": [4,5,6,7,8]}]}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data->0->a&id=in.(5,6)" `shouldRespondWith`+ [json| [{"a":"A"}, {"a":[1,2,3]}] |]+ { matchHeaders = [matchContentTypeJson] }++ it "works when finishing with an index" $ do+ get "/json_arr?select=data->0->a&id=in.(5,6)" `shouldRespondWith`+ [json| [{"a":"A"}, {"a":[1,2,3]}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data->c->0->d&id=eq.8" `shouldRespondWith`+ [json| [{"d":[4,5,6,7,8]}] |]+ { matchHeaders = [matchContentTypeJson] }++ context "filtering response" $ do+ it "can filter by properties inside json column" $ do+ get "/json?data->foo->>bar=eq.baz" `shouldRespondWith`+ [json| [{"data": {"id": 1, "foo": {"bar": "baz"}}}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json?data->foo->>bar=eq.fake" `shouldRespondWith`+ [json| [] |]+ { matchHeaders = [matchContentTypeJson] }++ it "can filter by properties inside json column using not" $+ get "/json?data->foo->>bar=not.eq.baz" `shouldRespondWith`+ [json| [] |]+ { matchHeaders = [matchContentTypeJson] }++ it "can filter by properties inside json column using ->>" $+ get "/json?data->>id=eq.1" `shouldRespondWith`+ [json| [{"data": {"id": 1, "foo": {"bar": "baz"}}}] |]+ { matchHeaders = [matchContentTypeJson] }++ it "can be filtered with and/or" $+ get "/grandchild_entities?or=(jsonb_col->a->>b.eq.foo, jsonb_col->>b.eq.bar)&select=id" `shouldRespondWith`+ [json|[{id: 4}, {id: 5}]|] { matchStatus = 200, matchHeaders = [matchContentTypeJson] }++ it "can filter by array indexes" $ do+ get "/json_arr?select=data&data->>0=eq.1" `shouldRespondWith`+ [json| [{"data":[1, 2, 3]}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data&data->1->>2=eq.13" `shouldRespondWith`+ [json| [{"data":[[9, 8, 7], [11, 12, 13]]}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data&data->1->>b=eq.B" `shouldRespondWith`+ [json| [{"data":[{"a": "A"}, {"b": "B"}]}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data&data->1->b->>1=eq.5" `shouldRespondWith`+ [json| [{"data":[{"a": [1,2,3]}, {"b": [4,5]}]}] |]+ { matchHeaders = [matchContentTypeJson] }++ it "can filter jsonb" $ do+ get "/jsonb_test?data=eq.{\"e\":1}" `shouldRespondWith`+ [json| [{"id":4,"data":{"e": 1}}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/jsonb_test?data->a=eq.{\"b\":2}" `shouldRespondWith`+ [json| [{"id":1,"data":{"a": {"b": 2}}}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/jsonb_test?data->c=eq.[1,2,3]" `shouldRespondWith`+ [json| [{"id":2,"data":{"c": [1, 2, 3]}}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/jsonb_test?data->0=eq.{\"d\":\"test\"}" `shouldRespondWith`+ [json| [{"id":3,"data":[{"d": "test"}]}] |]+ { matchHeaders = [matchContentTypeJson] }++ context "ordering response" $ do+ it "orders by a json column property asc" $+ get "/json?order=data->>id.asc" `shouldRespondWith`+ [json| [{"data": {"id": 0}}, {"data": {"id": 1, "foo": {"bar": "baz"}}}, {"data": {"id": 3}}] |]+ { matchHeaders = [matchContentTypeJson] }++ it "orders by a json column with two level property nulls first" $+ get "/json?order=data->foo->>bar.nullsfirst" `shouldRespondWith`+ [json| [{"data": {"id": 3}}, {"data": {"id": 0}}, {"data": {"id": 1, "foo": {"bar": "baz"}}}] |]+ { matchHeaders = [matchContentTypeJson] }++ context "Patching record, in a nonempty table" $+ it "can set a json column to escaped value" $ do+ _ <- post "/json" [json| { data: {"escaped":"bar"} } |]+ request methodPatch "/json?data->>escaped=eq.bar"+ [("Prefer", "return=representation")]+ [json| { "data": { "escaped":" \"bar" } } |]+ `shouldRespondWith` [json| [{ "data": { "escaped":" \"bar" } }] |]+ { matchStatus = 200 , matchHeaders = [] }
+ test/Feature/PgVersion95Spec.hs view
@@ -0,0 +1,55 @@+module Feature.PgVersion95Spec where++import Test.Hspec hiding (pendingWith)+import Test.Hspec.Wai+import Test.Hspec.Wai.JSON++import SpecHelper+import Network.Wai (Application)++import Protolude hiding (get)++spec :: SpecWith Application+spec = describe "features supported on PostgreSQL 9.5" $+ context "json array negative index" $ do+ it "can select with negative indexes" $ do+ get "/json_arr?select=data->>-1::int&id=in.(1,2)" `shouldRespondWith`+ [json| [{"data":3}, {"data":6}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data->0->>-2::int&id=in.(3,4)" `shouldRespondWith`+ [json| [{"data":8}, {"data":7}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data->-2->>a&id=in.(5,6)" `shouldRespondWith`+ [json| [{"a":"A"}, {"a":"[1,2,3]"}] |]+ { matchHeaders = [matchContentTypeJson] }++ it "can filter with negative indexes" $ do+ get "/json_arr?select=data&data->>-3=eq.1" `shouldRespondWith`+ [json| [{"data":[1, 2, 3]}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data&data->-1->>-3=eq.11" `shouldRespondWith`+ [json| [{"data":[[9, 8, 7], [11, 12, 13]]}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data&data->-1->>b=eq.B" `shouldRespondWith`+ [json| [{"data":[{"a": "A"}, {"b": "B"}]}] |]+ { matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data&data->-1->b->>-1=eq.5" `shouldRespondWith`+ [json| [{"data":[{"a": [1,2,3]}, {"b": [4,5]}]}] |]+ { matchHeaders = [matchContentTypeJson] }++ it "should fail on badly formed negatives" $ do+ get "/json_arr?select=data->>-78xy" `shouldRespondWith`+ [json|+ {"details": "unexpected 'x' expecting digit, \"->\", \"::\" or end of input",+ "message": "\"failed to parse select parameter (data->>-78xy)\" (line 1, column 11)"} |]+ { matchStatus = 400, matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data->>--34" `shouldRespondWith`+ [json|+ {"details": "unexpected \"-\" expecting digit",+ "message": "\"failed to parse select parameter (data->>--34)\" (line 1, column 9)"} |]+ { matchStatus = 400, matchHeaders = [matchContentTypeJson] }+ get "/json_arr?select=data->>-xy-4" `shouldRespondWith`+ [json|+ {"details":"unexpected \"x\" expecting digit",+ "message":"\"failed to parse select parameter (data->>-xy-4)\" (line 1, column 9)"} |]+ { matchStatus = 400, matchHeaders = [matchContentTypeJson] }
test/Feature/QuerySpec.hs view
@@ -216,23 +216,6 @@ [json| [{"settings":{"foo":{"int":1,"bar":"baz"}}}] |] { matchHeaders = [matchContentTypeJson] } - it "json subfield one level with casting (json)" $- get "/complex_items?id=eq.1&select=settings->>foo::json" `shouldRespondWith`- [json| [{"foo":{"int":1,"bar":"baz"}}] |] -- the value of foo here is of type "text"- { matchHeaders = [matchContentTypeJson] }-- it "rename json subfield one level with casting (json)" $- get "/complex_items?id=eq.1&select=myFoo:settings->>foo::json" `shouldRespondWith`- [json| [{"myFoo":{"int":1,"bar":"baz"}}] |] -- the value of foo here is of type "text"- { matchHeaders = [matchContentTypeJson] }-- it "fails on bad casting (data of the wrong format)" $- get "/complex_items?select=settings->foo->>bar::integer"- `shouldRespondWith` [json| {"hint":null,"details":null,"code":"22P02","message":"invalid input syntax for integer: \"baz\""} |]- { matchStatus = 400- , matchHeaders = []- }- it "fails on bad casting (wrong cast type)" $ get "/complex_items?select=id::fakecolumntype" `shouldRespondWith` [json| {"hint":null,"details":null,"code":"42704","message":"type \"fakecolumntype\" does not exist"} |]@@ -240,34 +223,12 @@ , matchHeaders = [] } -- it "json subfield two levels (string)" $- get "/complex_items?id=eq.1&select=settings->foo->>bar" `shouldRespondWith`- [json| [{"bar":"baz"}] |]- { matchHeaders = [matchContentTypeJson] }-- it "rename json subfield two levels (string)" $- get "/complex_items?id=eq.1&select=myBar:settings->foo->>bar" `shouldRespondWith`- [json| [{"myBar":"baz"}] |]- { matchHeaders = [matchContentTypeJson] }--- it "json subfield two levels with casting (int)" $- get "/complex_items?id=eq.1&select=settings->foo->>int::integer" `shouldRespondWith`- [json| [{"int":1}] |] -- the value in the db is an int, but here we expect a string for now- { matchHeaders = [matchContentTypeJson] }-- it "rename json subfield two levels with casting (int)" $- get "/complex_items?id=eq.1&select=myInt:settings->foo->>int::integer" `shouldRespondWith`- [json| [{"myInt":1}] |] -- the value in the db is an int, but here we expect a string for now- { matchHeaders = [matchContentTypeJson] }- it "requesting parents and children" $ get "/projects?id=eq.1&select=id, name, clients(*), tasks(id, name)" `shouldRespondWith` [json|[{"id":1,"name":"Windows 7","clients":{"id":1,"name":"Microsoft"},"tasks":[{"id":1,"name":"Design w7"},{"id":2,"name":"Code w7"}]}]|] { matchHeaders = [matchContentTypeJson] } - it "requesting parent without specifying primary key" $ do+ it "requesting parent without specifying primary key" $ get "/projects?select=name,client(name)" `shouldRespondWith` [json|[ {"name":"Windows 7","client":{"name": "Microsoft"}},@@ -277,9 +238,6 @@ {"name":"Orphan","client":null} ]|] { matchHeaders = [matchContentTypeJson] }- get "/articleStars?select=createdAt,article(owner),user(name)&limit=1" `shouldRespondWith`- [json|[{"createdAt":"2015-12-08T04:22:57.472738","article":{"owner": "postgrest_test_authenticator"},"user":{"name": "Angela Martin"}}]|]- { matchHeaders = [matchContentTypeJson] } it "requesting parent and renaming primary key" $ get "/projects?select=name,client(clientId:id,name)" `shouldRespondWith`@@ -351,40 +309,11 @@ [json|[{"id":1,"tasks":[{"id":1},{"id":2},{"id":3},{"id":4}]},{"id":2,"tasks":[{"id":5},{"id":6},{"id":7}]},{"id":3,"tasks":[{"id":1},{"id":5}]}]|] { matchHeaders = [matchContentTypeJson] } - it "requesting parents and children on views" $- get "/projects_view?id=eq.1&select=id, name, clients(*), tasks(id, name)" `shouldRespondWith`- [json|[{"id":1,"name":"Windows 7","clients":{"id":1,"name":"Microsoft"},"tasks":[{"id":1,"name":"Design w7"},{"id":2,"name":"Code w7"}]}]|]- { matchHeaders = [matchContentTypeJson] }-- it "requesting parents and children on views with renamed keys" $- get "/projects_view_alt?t_id=eq.1&select=t_id, name, clients(*), tasks(id, name)" `shouldRespondWith`- [json|[{"t_id":1,"name":"Windows 7","clients":{"id":1,"name":"Microsoft"},"tasks":[{"id":1,"name":"Design w7"},{"id":2,"name":"Code w7"}]}]|]- { matchHeaders = [matchContentTypeJson] }-- it "detects parent relations when having many views of a private table" $ do- get "/books?select=title,author(name)&id=eq.5" `shouldRespondWith`- [json|[ { "title": "Farenheit 451", "author": { "name": "Ray Bradbury" } } ]|]- { matchHeaders = [matchContentTypeJson] }- get "/forties_books?select=title,author(name)&limit=1" `shouldRespondWith`- [json|[ { "title": "1984", "author": { "name": "George Orwell" } } ]|]- { matchHeaders = [matchContentTypeJson] }- get "/fifties_books?select=title,author(name)&limit=1" `shouldRespondWith`- [json|[ { "title": "The Catcher in the Rye", "author": { "name": "J.D. Salinger" } } ]|]- { matchHeaders = [matchContentTypeJson] }- get "/sixties_books?select=title,author(name)&limit=1" `shouldRespondWith`- [json|[ { "title": "To Kill a Mockingbird", "author": { "name": "Harper Lee" } } ]|]- { matchHeaders = [matchContentTypeJson] }- it "requesting children with composite key" $ get "/users_tasks?user_id=eq.2&task_id=eq.6&select=*, comments(content)" `shouldRespondWith` [json|[{"user_id":2,"task_id":6,"comments":[{"content":"Needs to be delivered ASAP"}]}]|] { matchHeaders = [matchContentTypeJson] } - it "detect relations in views from exposed schema that are based on tables in private schema and have columns renames" $- get "/articles?id=eq.1&select=id,articleStars(users(*))" `shouldRespondWith`- [json|[{"id":1,"articleStars":[{"users":{"id":1,"name":"Angela Martin"}},{"users":{"id":2,"name":"Michael Scott"}},{"users":{"id":3,"name":"Dwight Schrute"}}]}]|]- { matchHeaders = [matchContentTypeJson] }- it "can embed by FK column name" $ get "/projects?id=in.(1,3)&select=id,name,client_id(id,name)" `shouldRespondWith` [json|[{"id":1,"name":"Windows 7","client_id":{"id":1,"name":"Microsoft"}},{"id":3,"name":"IOS","client_id":{"id":2,"name":"Apple"}}]|]@@ -400,22 +329,79 @@ [json|[{"id":1,"name":"Windows 7","client_id":1,"client":{"id":1,"name":"Microsoft"}},{"id":3,"name":"IOS","client_id":2,"client":{"id":2,"name":"Apple"}}]|] { matchHeaders = [matchContentTypeJson] } - it "can detect fk relations through views to tables in the public schema" $- get "/consumers_view?select=*,orders_view(*)" `shouldRespondWith` 200+ describe "view embedding" $ do+ it "can detect fk relations through views to tables in the public schema" $+ get "/consumers_view?select=*,orders_view(*)" `shouldRespondWith` 200 + it "can request parent without specifying primary key" $+ get "/articleStars?select=createdAt,article(owner),user(name)&limit=1" `shouldRespondWith`+ [json|[{"createdAt":"2015-12-08T04:22:57.472738","article":{"owner": "postgrest_test_authenticator"},"user":{"name": "Angela Martin"}}]|]+ { matchHeaders = [matchContentTypeJson] }++ it "can detect relations in views from exposed schema that are based on tables in private schema and have columns renames" $+ get "/articles?id=eq.1&select=id,articleStars(users(*))" `shouldRespondWith`+ [json|[{"id":1,"articleStars":[{"users":{"id":1,"name":"Angela Martin"}},{"users":{"id":2,"name":"Michael Scott"}},{"users":{"id":3,"name":"Dwight Schrute"}}]}]|]+ { matchHeaders = [matchContentTypeJson] }++ it "works when requesting parents and children on views" $+ get "/projects_view?id=eq.1&select=id, name, clients(*), tasks(id, name)" `shouldRespondWith`+ [json|[{"id":1,"name":"Windows 7","clients":{"id":1,"name":"Microsoft"},"tasks":[{"id":1,"name":"Design w7"},{"id":2,"name":"Code w7"}]}]|]+ { matchHeaders = [matchContentTypeJson] }++ it "works when requesting parents and children on views with renamed keys" $+ get "/projects_view_alt?t_id=eq.1&select=t_id, name, clients(*), tasks(id, name)" `shouldRespondWith`+ [json|[{"t_id":1,"name":"Windows 7","clients":{"id":1,"name":"Microsoft"},"tasks":[{"id":1,"name":"Design w7"},{"id":2,"name":"Code w7"}]}]|]+ { matchHeaders = [matchContentTypeJson] }++ it "detects parent relations when having many views of a private table" $ do+ get "/books?select=title,author(name)&id=eq.5" `shouldRespondWith`+ [json|[ { "title": "Farenheit 451", "author": { "name": "Ray Bradbury" } } ]|]+ { matchHeaders = [matchContentTypeJson] }+ get "/forties_books?select=title,author(name)&limit=1" `shouldRespondWith`+ [json|[ { "title": "1984", "author": { "name": "George Orwell" } } ]|]+ { matchHeaders = [matchContentTypeJson] }+ get "/fifties_books?select=title,author(name)&limit=1" `shouldRespondWith`+ [json|[ { "title": "The Catcher in the Rye", "author": { "name": "J.D. Salinger" } } ]|]+ { matchHeaders = [matchContentTypeJson] }+ get "/sixties_books?select=title,author(name)&limit=1" `shouldRespondWith`+ [json|[ { "title": "To Kill a Mockingbird", "author": { "name": "Harper Lee" } } ]|]+ { matchHeaders = [matchContentTypeJson] }++ it "works with views that have subselects" $+ get "/authors_books_number?select=*,books(title)&id=eq.1" `shouldRespondWith`+ [json|[ {"id":1, "name":"George Orwell","num_in_forties":1,"num_in_fifties":0,"num_in_sixties":0,"num_in_all_decades":1,+ "books":[{"title":"1984"}]} ]|]+ { matchHeaders = [matchContentTypeJson] }++ it "works with views that have case subselects" $+ get "/authors_have_book_in_decade?select=*,books(title)&id=eq.3" `shouldRespondWith`+ [json|[ {"id":3,"name":"Antoine de Saint-Exupéry","has_book_in_forties":true,"has_book_in_fifties":false,"has_book_in_sixties":false,+ "books":[{"title":"The Little Prince"}]} ]|]+ { matchHeaders = [matchContentTypeJson] }++ it "works with views that have subselect in the FROM clause" $+ get "/forties_and_fifties_books?select=title,first_publisher,author:authors(name)&id=eq.1" `shouldRespondWith`+ [json|[{"title":"1984","first_publisher":"Secker & Warburg","author":{"name":"George Orwell"}}]|]+ { matchHeaders = [matchContentTypeJson] }++ it "works with views that have CTE" $+ get "/odd_years_publications?select=title,publication_year,first_publisher,author:authors(name)&id=in.(1,2,3)" `shouldRespondWith`+ [json|[+ {"title":"1984","publication_year":1949,"first_publisher":"Secker & Warburg","author":{"name":"George Orwell"}},+ {"title":"The Diary of a Young Girl","publication_year":1947,"first_publisher":"Contact Publishing","author":{"name":"Anne Frank"}},+ {"title":"The Little Prince","publication_year":1947,"first_publisher":"Reynal & Hitchcock","author":{"name":"Antoine de Saint-Exupéry"}} ]|]+ { matchHeaders = [matchContentTypeJson] }++ it "works when having a capitalized table name and camelCase fk column" $+ get "/foos?select=*,bars(*)" `shouldRespondWith` 200+ describe "path fixed" $ do it "works when requesting children 2 levels" $ get "/clients?id=eq.1&select=id,projects:projects.client_id(id,tasks(id))" `shouldRespondWith` [json|[{"id":1,"projects":[{"id":1,"tasks":[{"id":1},{"id":2}]},{"id":2,"tasks":[{"id":3},{"id":4}]}]}]|] { matchHeaders = [matchContentTypeJson] } - it "works with parent relation" $ do- get "/message?select=id,body,sender:person_detail.sender(name,sent),recipient:person_detail.recipient(name,received)&id=lt.4" `shouldRespondWith`- [json|- [{"id":1,"body":"Hello Jane","sender":{"name":"John","sent":2},"recipient":{"name":"Jane","received":2}},- {"id":2,"body":"Hi John","sender":{"name":"Jane","sent":1},"recipient":{"name":"John","received":1}},- {"id":3,"body":"How are you doing?","sender":{"name":"John","sent":2},"recipient":{"name":"Jane","received":2}}] |]- { matchHeaders = [matchContentTypeJson] }+ it "works with parent relation" $ get "/message?select=id,body,sender:person.sender(name),recipient:person.recipient(name)&id=lt.4" `shouldRespondWith` [json| [{"id":1,"body":"Hello Jane","sender":{"name":"John"},"recipient":{"name":"Jane"}},@@ -423,6 +409,14 @@ {"id":3,"body":"How are you doing?","sender":{"name":"John"},"recipient":{"name":"Jane"}}] |] { matchHeaders = [matchContentTypeJson] } + it "works with a parent view relation" $+ get "/message?select=id,body,sender:person_detail.sender(name,sent),recipient:person_detail.recipient(name,received)&id=lt.4" `shouldRespondWith`+ [json|+ [{"id":1,"body":"Hello Jane","sender":{"name":"John","sent":2},"recipient":{"name":"Jane","received":2}},+ {"id":2,"body":"Hi John","sender":{"name":"Jane","sent":1},"recipient":{"name":"John","received":1}},+ {"id":3,"body":"How are you doing?","sender":{"name":"John","sent":2},"recipient":{"name":"Jane","received":2}}] |]+ { matchHeaders = [matchContentTypeJson] }+ it "works with many<->many relation" $ get "/tasks?select=id,users:users.users_tasks(id)" `shouldRespondWith` [json|[{"id":1,"users":[{"id":1},{"id":3}]},{"id":2,"users":[{"id":1}]},{"id":3,"users":[{"id":1}]},{"id":4,"users":[{"id":1}]},{"id":5,"users":[{"id":2},{"id":3}]},{"id":6,"users":[{"id":2}]},{"id":7,"users":[{"id":2}]},{"id":8,"users":[]}]|]@@ -630,16 +624,6 @@ , matchHeaders = ["Content-Range" <:> "0-1/*"] } - it "by a json column property asc" $- get "/json?order=data->>id.asc" `shouldRespondWith`- [json| [{"data": {"id": 0}}, {"data": {"id": 1, "foo": {"bar": "baz"}}}, {"data": {"id": 3}}] |]- { matchHeaders = [matchContentTypeJson] }-- it "by a json column with two level property nulls first" $- get "/json?order=data->foo->>bar.nullsfirst" `shouldRespondWith`- [json| [{"data": {"id": 3}}, {"data": {"id": 0}}, {"data": {"id": 1, "foo": {"bar": "baz"}}}] |]- { matchHeaders = [matchContentTypeJson] }- it "without other constraints" $ get "/items?order=id.asc" `shouldRespondWith` 200 @@ -771,22 +755,6 @@ respHeaders `shouldSatisfy` matchHeader "Content-Location" "/simple_pk" - describe "jsonb" $ do- it "can filter by properties inside json column" $ do- get "/json?data->foo->>bar=eq.baz" `shouldRespondWith`- [json| [{"data": {"id": 1, "foo": {"bar": "baz"}}}] |]- { matchHeaders = [matchContentTypeJson] }- get "/json?data->foo->>bar=eq.fake" `shouldRespondWith`- [json| [] |]- { matchHeaders = [matchContentTypeJson] }- it "can filter by properties inside json column using not" $- get "/json?data->foo->>bar=not.eq.baz" `shouldRespondWith`- [json| [] |]- { matchHeaders = [matchContentTypeJson] }- it "can filter by properties inside json column using ->>" $- get "/json?data->>id=eq.1" `shouldRespondWith`- [json| [{"data": {"id": 1, "foo": {"bar": "baz"}}}] |]- { matchHeaders = [matchContentTypeJson] } describe "weird requests" $ do it "can query as normal" $ do
test/Feature/RpcSpec.hs view
@@ -78,6 +78,16 @@ get "/rpc/sayhello" `shouldRespondWith` 404 get "/rpc/sayhello?any_arg=value" `shouldRespondWith` 404 + it "works when having uppercase identifiers" $ do+ get "/rpc/quotedFunction?user=mscott&fullName=Michael Scott&SSN=401-32-XXXX" `shouldRespondWith`+ [json|{"user": "mscott", "fullName": "Michael Scott", "SSN": "401-32-XXXX"}|]+ { matchHeaders = [matchContentTypeJson] }+ post "/rpc/quotedFunction"+ [json|{"user": "dschrute", "fullName": "Dwight Schrute", "SSN": "030-18-XXXX"}|]+ `shouldRespondWith`+ [json|{"user": "dschrute", "fullName": "Dwight Schrute", "SSN": "030-18-XXXX"}|]+ { matchHeaders = [matchContentTypeJson] }+ context "shaping the response returned by a proc" $ do it "returns a project" $ do post "/rpc/getproject" [json| { "id": 1} |] `shouldRespondWith`
test/Feature/StructureSpec.hs view
@@ -85,6 +85,27 @@ deleteResponse `shouldBe` Just "No Content" + it "includes an array type for GET responses" $ do+ r <- simpleBody <$> get "/"++ let childGetSchema = r ^? key "paths"+ . key "/child_entities"+ . key "get"+ . key "responses"+ . key "200"+ . key "schema"++ liftIO $+ childGetSchema `shouldBe` Just+ [aesonQQ|+ {+ "items": {+ "$ref": "#/definitions/child_entities"+ },+ "type": "array"+ }+ |]+ it "includes definitions to tables" $ do r <- simpleBody <$> get "/" @@ -113,7 +134,10 @@ "format": "integer", "type": "integer" }- }+ },+ "required": [+ "id"+ ] } |] @@ -163,15 +187,54 @@ ] |] + describe "Materialized view" $++ it "includes materialized view properties" $ do+ r <- simpleBody <$> get "/"++ let method s = key "paths" . key "/materialized_projects" . key s+ summary = r ^? method "get" . key "summary"+ description = r ^? method "get" . key "description"+ parameters = r ^? method "get" . key "parameters"++ liftIO $ do++ summary `shouldBe` Just "A materialized view for projects"++ description `shouldBe` Just "Just a test for materialized views"++ parameters `shouldBe` Just+ [aesonQQ|+ [+ { "$ref": "#/parameters/rowFilter.materialized_projects.id" },+ { "$ref": "#/parameters/rowFilter.materialized_projects.name" },+ { "$ref": "#/parameters/rowFilter.materialized_projects.client_id" },+ { "$ref": "#/parameters/select" },+ { "$ref": "#/parameters/order" },+ { "$ref": "#/parameters/range" },+ { "$ref": "#/parameters/rangeUnit" },+ { "$ref": "#/parameters/offset" },+ { "$ref": "#/parameters/limit" },+ { "$ref": "#/parameters/preferCount" }+ ]+ |]+ describe "RPC" $ do - it "includes body schema for arguments" $ do+ it "includes function summary/description and body schema for arguments" $ do r <- simpleBody <$> get "/"- let args = r ^? key "paths" . key "/rpc/varied_arguments"- . key "post" . key "parameters"- . nth 0 . key "schema" - liftIO $+ let method s = key "paths" . key "/rpc/varied_arguments" . key s+ args = r ^? method "post" . key "parameters" . nth 0 . key "schema"+ summary = r ^? method "post" . key "summary"+ description = r ^? method "post" . key "description"++ liftIO $ do++ summary `shouldBe` Just "An RPC function"++ description `shouldBe` Just "Just a test for RPC function arguments"+ args `shouldBe` Just [aesonQQ| {@@ -213,7 +276,8 @@ "type": "integer" } },- "type": "object"+ "type": "object",+ "description": "An RPC function\n\nJust a test for RPC function arguments" } |]
test/Feature/UpsertSpec.hs view
@@ -198,3 +198,30 @@ [("Prefer", "return=representation"), ("Accept", "application/vnd.pgrst.object+json")] [str| [ { "name": "Ruby", "rank": 11 } ]|] `shouldRespondWith` [json|{ "name": "Ruby", "rank": 11 }|] { matchHeaders = [matchContentTypeSingular] }++ context "with a camel case pk column" $ do+ it "works with POST and merge-duplicates/ignore-duplicates headers" $ do+ request methodPost "/UnitTest" [("Prefer", "return=representation"), ("Prefer", "resolution=merge-duplicates")]+ [json| [+ { "idUnitTest": 1, "nameUnitTest": "name of unittest 1" },+ { "idUnitTest": 2, "nameUnitTest": "name of unittest 2" }+ ]|] `shouldRespondWith` [json|[+ { "idUnitTest": 1, "nameUnitTest": "name of unittest 1" },+ { "idUnitTest": 2, "nameUnitTest": "name of unittest 2" }+ ]|]+ { matchStatus = 201+ , matchHeaders = ["Preference-Applied" <:> "resolution=merge-duplicates", matchContentTypeJson]+ }+ request methodPost "/UnitTest" [("Prefer", "return=representation"), ("Prefer", "resolution=ignore-duplicates")]+ [json| [+ { "idUnitTest": 1, "nameUnitTest": "name of unittest 1" },+ { "idUnitTest": 2, "nameUnitTest": "name of unittest 2" }+ ]|] `shouldRespondWith` [json|[]|]+ { matchStatus = 201+ , matchHeaders = ["Preference-Applied" <:> "resolution=ignore-duplicates", matchContentTypeJson]+ }++ it "works with PUT" $ do+ put "/UnitTest?idUnitTest=eq.1" [str| [ { "idUnitTest": 1, "nameUnitTest": "unit test 1" } ]|] `shouldRespondWith` 204+ get "/UnitTest?idUnitTest=eq.1" `shouldRespondWith`+ [json| [ { "idUnitTest": 1, "nameUnitTest": "unit test 1" } ]|] { matchHeaders = [matchContentTypeJson] }
test/Main.hs view
@@ -6,8 +6,8 @@ import qualified Hasql.Pool as P import PostgREST.App (postgrest)-import PostgREST.Config (pgVersion95, pgVersion96, configSettings)-import PostgREST.DbStructure (getDbStructure, getPgVersion, fillSessionWithSettings)+import PostgREST.Config (pgVersion95, pgVersion96)+import PostgREST.DbStructure (getDbStructure, getPgVersion) import PostgREST.Types (DbStructure(..)) import Control.AutoUpdate (defaultUpdateSettings, mkAutoUpdate, updateAction) import Data.Function (id)@@ -22,6 +22,7 @@ import qualified Feature.CorsSpec import qualified Feature.DeleteSpec import qualified Feature.InsertSpec+import qualified Feature.JsonOperatorSpec import qualified Feature.NoJwtSpec import qualified Feature.QueryLimitedSpec import qualified Feature.QuerySpec@@ -33,6 +34,7 @@ import qualified Feature.AndOrParamsSpec import qualified Feature.RpcSpec import qualified Feature.NonexistentSchemaSpec+import qualified Feature.PgVersion95Spec import qualified Feature.PgVersion96Spec import qualified Feature.UpsertSpec @@ -64,11 +66,12 @@ nonexistentSchemaApp = return $ postgrest (testNonexistentSchemaCfg testDbConn) refDbStructure pool getTime $ pure () let reset :: IO ()- reset = P.use pool (fillSessionWithSettings (configSettings $ testCfg testDbConn)) >> resetDb testDbConn+ reset = resetDb testDbConn actualPgVersion = pgVersion dbStructure extraSpecs = [("Feature.UpsertSpec", Feature.UpsertSpec.spec) | actualPgVersion >= pgVersion95] +++ [("Feature.PgVersion95Spec", Feature.PgVersion95Spec.spec) | actualPgVersion >= pgVersion95] ++ [("Feature.PgVersion96Spec", Feature.PgVersion96Spec.spec) | actualPgVersion >= pgVersion96] specs = uncurry describe <$> [@@ -77,6 +80,7 @@ , ("Feature.CorsSpec" , Feature.CorsSpec.spec) , ("Feature.DeleteSpec" , Feature.DeleteSpec.spec) , ("Feature.InsertSpec" , Feature.InsertSpec.spec)+ , ("Feature.JsonOperatorSpec" , Feature.JsonOperatorSpec.spec) , ("Feature.QuerySpec" , Feature.QuerySpec.spec) , ("Feature.RpcSpec" , Feature.RpcSpec.spec) , ("Feature.RangeSpec" , Feature.RangeSpec.spec)