tomjaguarpaw / haskell-opaleye Goto Github PK

In anticipation of opaleye-sqlite change pg and PG in names to sql and Sql instead. Examples

• PGTypes module becomes SqlTypes
• PGText type becomes SqlText
• pgText function becomes SqlText

Generally, people should be using Column Bool, Column Int etc, and not Column PGBool. In SQLite columns for types such as Bool don't really exist, so must be implemented as Column SLInt. That's not enough type specificity, so should not be the preferred format.

I've already asked this for the groundhog library (see lykahb/groundhog#41), and now I'm wondering whether opaleye has support or plans to support a streaming-like interface for querying many tuples from a database in constant-space.

This issue #78 suggests that the manipulation tutorial is unclear. Provide an example of inserting text and other types.

Running Aggregate.min function on boolean column generates an invalid query that will fail when ran.

select id, min(closed) from
No function matches the given name and argument types. You might need to add explicit type casts.

Some other functions from the same module might have the same problem, like avg for string fields etc.

Idea: It could be solved by restricting the a type of min with a methodless typeclass. All the datatypes that can be applied to the function would be instances of the typeclass.

Have you thought about implementing with recursive? I'd like to perhaps give it a go, and was thinking it would look something like:

recursive :: Query a -> QueryArr a a -> Query a

This would then generate something like:

with recursive temp (cols) (
  <query1>
  union all
  <query2 with references to the input bound to 'temp'>
  )
select cols from temp

Do you think that makes sense? Do you have any pointers for the implementation?

They confuse things, bloat the interface, and should never be used anyway. Take feature requests for the ones that are in use, or Google for the module names.

PostgreSQL allows one to specify whether null should be sorted before or after non-null values: http://www.postgresql.org/docs/9.4/static/queries-order.html. Could Opaleye be extended to support this?

Synonyms for pgBool False and pgBool True.

It doesn't help the flow of the tutorial. If people really care what is going on under the covers they can run printSQL themselves.

It might be useful to simplify the creation of constants, currently done something like this:

data Foo a b c = Foo a b c

fooColumn :: Foo (Column PGInt4) (Column PGBool) (Column PGText)
fooColumn = Foo (pgInt4 1) (pgBool True) (pgStrictText "foo")

We could use a product profunctor and a Default instance to simplify this to

fooColumn = constant (Foo 1 True "foo")

The downside is that this would probably require some explicit type signatures.

Currently we emit empty SELECT statments for cases where there are zero columns. This syntax is not supported on 9.3 and below, so we'll have to do something else.

In the use of ‘literalColumn’ (imported from Opaleye.PGTypes):
    "'literalColumn' has been moved to Opaleye.Internal.PGTypes"

We use this for our ShowConstant

I'd like to be able to write queries like this:

update users set last_post = (select id from posts where foo) where bar

And similarly for inserts. This was already filed as karamaan/karamaan-opaleye#66 where it was noted that this can fail at runtime if the subselect gives something other than 1 result. But I don't think there's anything that can be done about that.

At the moment you output TRUE, should be 1 or 0.

Hello,
following manipulation tutorial. Int4 insertion works fine but what about inserting PGText ?
let be this:
import Opaleye as O
table' :: O.Table (Maybe (Column O.PGInt4), Column O.PGText, Column O.PGText)
(Column O.PGInt4, Column O.PGText, Column O.PGText)
O.arrangeInsertSql table' (Nothing, "ABC", "TEST")
gives:
Couldn't match expected type ‘Column PGText’
with actual type ‘[Char]’
In the expression: "ABC"
and:
Couldn't match expected type ‘Column PGText’
with actual type ‘[Char]’
In the expression: "TEST"

So, I have to change type of "ABC" into 'Column PGText'

I suppose there is an easy way to do it already.
If not tell me, maybe I could patch it.

Cheers,
anoe

Should be pretty easy to generate a default CREATE statement.

I would imagine:

isDistinctFrom :: Column (Nullable a) -> Column (Nullable a) -> Column PGBool

with the same semantics as http://www.postgresql.org/docs/current/static/functions-comparison.html

I had a look at the queries being generated, but I'm afraid I think that subquery optmization for MySQL is not very effective.

E.g. looking at...

EXPLAIN SELECT `createdAt0_1` as result1_2,
       `updatedAt1_1` as result2_2,
       `originID2_1` as result3_2,
       `originType3_1` as result4_2,
       `partID4_1` as result5_2,
       `dpn5_1` as result6_2,
       `url6_1` as result7_2,
       `provider7_1` as result8_2,
       `markup8_1` as result9_2
FROM (SELECT *
      FROM (SELECT `createdAt` as createdAt0_1,
                   `updatedAt` as updatedAt1_1,
                   `originID` as originID2_1,
                   `originType` as originType3_1,
                   `partID` as partID4_1,
                   `dpn` as dpn5_1,
                   `url` as url6_1,
                   `provider` as provider7_1,
                   `markup` as markup8_1
            FROM source as T1) as T1) as T1;

...produces...

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, PRIMARY, <derived2>, ALL, , , , , 53385, 
2, DERIVED, <derived3>, ALL, , , , , 53385, 
3, DERIVED, T1, ALL, , , , , 63367, 

...so it seems temporary tables are not optimized away.

In SQLite that results in a type error when unmarshalling. Make sure doubles are printed correctly.

The following code does not compile in ghci:

thirteenTable :: Table (Column PGText, Column PGFloat8, Column PGText
                   ,Column PGText, Column PGFloat8, Column PGText
                   ,Column PGText, Column PGFloat8, Column PGText
                   ,Column PGText, Column [PGFloat8], Column PGText
                   ,Column PGText)
                   (Column PGText, Column PGFloat8, Column PGText
                   ,Column PGText, Column PGFloat8, Column PGText
                   ,Column PGText, Column PGFloat8, Column PGText
                   ,Column PGText, Column [PGFloat8], Column PGText
                   ,Column PGText)

 thirteenTable = Table "thirteenTable" (p13 ( required "a1"
                                       , required "a2"
                                       , required "a3"
                                       , required "a4"
                                       , required "a5"
                                       , required "a6"
                                       , required "a7"
                                       , required "a8"
                                       , required "a9"
                                       , required "a10"
                                       , required "a11"
                                       , required "a12"
                                       , required "a13"))

thirteenQuery :: Query(Column PGText, Column PGFloat8, Column PGText
                  ,Column PGText, Column PGFloat8, Column PGText
                  ,Column PGText, Column PGFloat8, Column PGText
                  ,Column PGText, Column [PGFloat8], Column PGText
                  ,Column PGText)
thirteenQuery = queryTable thirteenTable

It give the error:

No instance for (Default
                   Opaleye.Internal.TableMaker.ColumnMaker
                   (Column PGText,
                    Column PGFloat8,
                    Column PGText,
                    Column PGText,
                    Column PGFloat8,
                    Column PGText,
                    Column PGText,
                    Column PGFloat8,
                    Column PGText,
                    Column PGText,
                    Column [PGFloat8],
                    Column PGText,
                    Column PGText)
                   (Column PGText,
                    Column PGFloat8,
                    Column PGText,
                    Column PGText,
                    Column PGFloat8,
                    Column PGText,
                    Column PGText,
                    Column PGFloat8,
                    Column PGText,
                    Column PGText,
                    Column [PGFloat8],
                    Column PGText,
                    Column PGText))
  arising from a use of ‘queryTable’
In the expression: queryTable thirteenTable
In an equation for ‘thirteenQuery’:
    thirteenQuery = queryTable thirteenTable

However removing one of the columns or adding another column causes the example above to work fine.

The following remain to be tested

• union
• aggregate
• left join
• nullability
• all the operators (numeric, logical, etc.)
• creating tables

Rename string to stringAgg and array to arrayAgg for closer correspondence to the names in SQL.

@coubeatczech: Do you have a strong opinion about this?

Currently the functionality for unpacking columns is trapped inside runQueryArrUnpack. Move it to the Unpackspec module.

runQueryArrUnpack :: U.Unpackspec a b
                  -> Query a -> ([HPQ.PrimExpr], PQ.PrimQuery, Tag)
runQueryArrUnpack unpackspec q = (primExprs, primQ, endTag)
  where (columns, primQ, endTag) = runSimpleQueryArrStart q ()
        f pe = ([pe], pe)
        primExprs :: [HPQ.PrimExpr]
        (primExprs, _) = U.runUnpackspec unpackspec f columns

At least put opaleyeCoerce or something. It's no where near as deadly as the name suggests.

Is it possible to add a convenience import to OpalEye? It's a bit intimidating to (as is done in the basic tutorial) have to import 18 modules before writing a single like of functional code, especially if I'm only meaning to take OpalEye for a test drive.

At the moment opaleye (in reality, not in the tutorial) escapes all column names. It should only do that for ones that conflict, to keep the default SQL simpler for human consumption.

Not a bug per se, but is there anything can be done about it?

{-# LANGUAGE Arrows #-}
import Opaleye
import qualified Database.PostgreSQL.Simple as PGS
import Control.Arrow

q2 :: Column PGInt4 -> Query (Column PGInt4)
q2 i = proc () -> do
          -- x <- queryTable tableX -< ()  -- one column for simplicity
          -- restrict -< x .== i
          returnA -< i

a2 :: Column PGInt4 -> Query (Column PGInt4)
a2 i = (aggregate groupBy) (q2 i)

main = do
  c <- PGS.connect connectInfo
  r <- runQuery c (a2 1) :: IO [Int]  -- it will “GROUP BY (1)” [sic]
  print r

connectInfo :: PGS.ConnectInfo

In the postgresql, there are more aggregation operators than the user of opaleye can use. Some are just not exposed in the public api, whereas some are just not implemented.

Given these types:

data User' a b = User {uID :: a, uEmail :: b} deriving (Show)
type User = User' Int Text
type UserColumn = User' (Column PGInt4) (Column PGText)

I can run a query and get back IO [User] rather than IO [UserColumn] which is great. But is there any way to insert a User without first manually converting it to a UserColumn?

There should be an aggregator countAll :: Aggregator () (Column PGInt8).

Most pg* functions are named after the haskell type e.g. pgTimeOfDay, but there's pgInt4 and pgInt8 which have postgres names, and "The type names int2, int4, and int8 are extensions, which are also used by some other SQL database systems."

Haskell's Int isn't guaranteed to fit in an int4, only on 32bit systems? For me Int and Int64 are the same.

So I think it would make more sense to have pgInt and pgInt64 instead since 64bit users can't safely map Int to int4.

Perhaps rename null to nullColumn

First reported by bergmark here: karamaan/karamaan-opaleye#75

At the moment the docs are awful since you get a massive list of modules that you can't search through. Make the modules such as Opaleye.Operators hidden so that Opaleye documents everything.

Postgres allows you to insert multiple values in a single insert statement:

silk=# create table t (id integer primary key not null);
CREATE TABLE
silk=# insert into t values (1), (2), (3);
INSERT 0 3

It would be nice if this could also be done from Opaleye, but it looks like currently you can only insert a single row at a time.

Once #58 is complete we should encode the Mandelbrot set: https://twitter.com/rpy/status/622291301002670080/photo/1

I had a stab at this with:

in_ :: Column a -> [Column a] -> Column PGBool
in_ x ys =
  Opaleye.binOp HPQ.OpIn
                x
                (Opaleye.Column (HPQ.ListExpr [y | Opaleye.Column y <- ys]))

However this produces:

WHERE ((id0_2) IN ((10025,10355)))) as T1

Which isn't correct, as there are one too many levels of parenthesis, which changes the semantics to be a list of records, rather than list of integers.

Hello! I've been very impressed with opaleye and I'm looking into using it on a project where I need to be able to compose a dynamic set of filters in order to build a query. It's going well but I have a couple of questions.

So, fist some setup. Given a query that returns users:
usersQuery :: Query UserColumn

I need to be able to compose an assortment of filters in order to get the final set of users. My filters all basically have this form: 1.) accept some users 2.) apply some filter to the users 3.) pass the filtered users along. Like this:

hasFeature featureID value = proc user -> do
    fv <- featureValuesQuery -< ()
    restrict -< (fvUserID .== uID user) 
            .&& (fvFeatureID .== featureID) 
            .&& (fvValue .== value)
    returnA -< user

And this all composes nicely like this:

usersQuery >>> hasFeature 1 (pgString "foo") >>> hasFeature 2 (pgString "bar")

So far so good. My questions are:

1.) Is this a reasonable approach to the problem? As in, will it generate terribly inefficient SQL? Looking at the output of printSql it actually looks pretty reasonable.
2.) I'm currently doing this to compose my list of filters: foldl' (>>>) usersQuery [f1, f2...]. Is there a more idiomatic way to fold over a bunch of arrows?

Thank you!

EDIT: Also, how would I use count to get the number of rows returned?
I'm currently doing this but it seems cumbersome and specific to the type of row I'm counting: aggregate count (usersQuery >>^ uID)

This is coming late, but I didn't realize the impact of the PGTypes change that happened a while back. I'm not sure if this can be solved outside of opaleye?

Take this example:

create table foo (id serial primary key);
create table bar (id serial primary key, foo_id int references foo (id));

The opaleye mapping for this would be

newtype FooId = FooId Int
data Foo' a = Foo { fooId :: a }
type Foo = Foo' FooId
type FooColumn = Foo (Column PGInt)

newtype BarId = BarId Int
data Bar' a b = Foo { barId :: a,  fooBarId :: b }
type Bar = Bar' BarId FooId
type BarColumn = Foo (Column PGInt) (Column PGInt)

If we construct queries joining these tables we might by accident end up with

myQuery = proc () -> do
  f <- queryTable fooTable
  b <- queryTable barTable
  restrict -< fooId f .== barId b
  id -< (f,b)

yielding something like

select f.* from foo f inner join bar b on f.id = b.id;

This used to be a type error because fooId f :: Column FooId and barId b :: Column BarId. Now they are both :: Column PGInt and there is no protection against doing nonsensical joins.

So in a lot of ways queries are no longer type safe as I'd like them to be, they are only type safe in the sense that the postgres types are the same. You can misplace any column that happens to have the same representation as another one.

Hi, this issue is going to be a little rambly, but hopefully I can provide some useful information....

As I've mentioned before, I don't really like the way records interact in Opaleye. data Foo a b c d e is not idiomatic Haskell, and it makes adding Opaleye in later difficult. However, the benefit of having named accessors is an indispensable win - so whatever I suggest needs to be able to provide this.

The first observation is that we're doing things this way in order to "vary the idiom". Sometimes we are working with a table where everything is a Column, other times everything is in TableProperties, and other times everything is in Identity (you've executed the query).

This notion comes up a lot - we see it once in generics-sop (all products/sums are parametrized by a functor) and again in vinyl. I would wager that it's possible to achieve what we have at the moment using vinyl - we get named fields and thus order-independent access/construction. Along with this, the specification of tables becomes easier - all you need to do is list the types (it's unclear whether you would list PostgreSQL types or Haskell types), and you only need to do that once. We're then moving towards something like

someTable :: Table '[Int, String, String, UTCTime]

The next observation is that the type inference is pretty hairy - and I'd say that's almost entirely due to Default. With the above idea you now have an inductive structure that you can meaningfully fold over. Running a query simply swaps out the functor from Column to something like:

data PostgreSQLSimple :: * -> * where
  PostgreSQLSimple :: FromField a => PostgreSQLSimple a

(there are details here as to when you're able to capture this constraint, but I can't see any reason it can't be done, off hand).

Again, just by varying the functor you get more useful properties. A left join should be inferrable now, because a left join simply changes your functor from f to Compose Nullable f, perhaps joining Nullable (Nullable a) along the way.

This is bit of a sketch of ideas, but hopefully I'll find time to help contribute some of this work.

Has anyone explored any of these ideas?

Can someone give me hints how to replace Persistent with OpalEye in a scaffolded Yesod 1.4 application?

I wanted to add tests for #58 and found that the test cases on current master are broken. Here is the output:

opaleye-0.3.1.2: test (suite: test)
NOTICE:  table "TABLE2" does not exist, skipping
*** Failed! Exception: 'SqlError {sqlState = "42601", sqlExecStatus = FatalError, sqlErrorMsg = "syntax error at or near \"FROM\"", sqlErrorDetail = "", sqlErrorHint = ""}' (after 1 test):
A permutation
SELECT "column10_1" as result1_2
FROM (SELECT *
      FROM (SELECT *
            FROM (SELECT *
                  FROM (SELECT "column1" as column10_1,
                               "column2" as column21_1
                        FROM "table1" as T1) as T1) as T1
            LIMIT 71) as T1) as T1
test: Failed
opaleye-0.3.1.2: test (suite: tutorial)
Test suite failure for package opaleye-0.3.1.2
    test:  exited with: ExitFailure 1
Logs printed to console

At Fynder we have a lot of custom types. In fact, every table gives rise to (at least) one newtype wrapper around its surrogate id. The following type is useful to "lift" coercions in postgresql-simple to opaleye:

data PGLift a

instance PG.FromField a => Opaleye.QueryRunnerColumnDefault (PGLift a) a where
  queryRunnerColumnDefault = fieldQueryRunnerColumn

Maybe this is something that would fit nicely in opaleye itself?

When I have wrap an id in a newtype, then I need to unwrap it every time when I need to do something useful with it.

newtype CompanyId' id = CompanyId { getCompanyId :: id }
type CompanyId = CompanyId' Int
makeAdaptorAndInstance "pCompanyId" ''C.CompanyId'

Then I need to do the unwrapping everytime:

restrict -< C.getCompanyId companyPK .== (pgInt4 . C.getCompanyId) companyId

Whereas I'd like to write:

restrict -< companyPK .== companyId

Does anyone have some nice solution for this?

Add a function of type toColumn :: a -> Column a. Useful for creating values from newtype'd columns

Opaleye should be tested on multiple GHC versions on every commit.

The generated SQL is pretty verbose. I suggest you try and simplify all the ones in the tutorial with simple rewrite rules. For other databases like SQLite it is common to try and not rely on the database to do clever optimisations.

Apparently table names with capital letters (and presumably other non-lowercase alphabeticals) are not handled properly: #48

Merge the pull request and add a test.