haskelldb-users — For questions, suggestions, bug reports and patches

[Haskelldb-users] more on last_insert_id()

[Jeremy S. <jer...@li...>]

Here is a way to get last_insert_id() working for mysql. The nice
thing about this solution is it can be used without modifying the
library code at all. It is based, in part, on test/current_time.hs .


First I defined a module LastInsertID.hs

-->

module LastInsertID where

import Database.HaskellDB.HDBRec
import Database.HaskellDB.HDBRecUtils
import Database.HaskellDB.BoundedString
import System.Time (CalendarTime)
import Database.HaskellDB.Query (Expr, Table, Attr, baseTable)
import Database.HaskellDB.DBSpec
import Database.HaskellDB.FieldType


---------------------------------------------------------------------------
-- Table
---------------------------------------------------------------------------
lastinsertid :: Table
 ((HDBRecCons Last_insert_id (Expr Integer) HDBRecTail))

lastinsertid = baseTable "lastinsertid()" $
	       hdbMakeEntry Last_insert_id

---------------------------------------------------------------------------
-- Fields
---------------------------------------------------------------------------
---------------------------------------------------------------------------
-- Bug_id Field
---------------------------------------------------------------------------

data Last_insert_id = Last_insert_id

instance FieldTag Last_insert_id where fieldName _ = "lastInsertID"

last_insert_id :: Attr Last_insert_id Integer

last_insert_id = mkAttr Last_insert_id

<--

Then in my Main.hs I imported LastInsertId and I defined:

-- | similar to doInsert, except it returns LAST_INSERT_ID()
doInsertLastID :: forall r. (ShowRecRow r, ToPrimExprs r) => Table r -> (HDBRecTail -> r) -> IO Integer
doInsertLastID table record = 
    mysqlConnect opts $ \db -> do insert db table record
				  (r:_) <- dbQuery db lastInsertIDQ
					   ((Rel 0 ["lastInsertID"]) :: Rel ((HDBRecCons Last_insert_id (Expr Integer) HDBRecTail)))
				  return (Row r!last_insert_id)
	where lastInsertIDQ = Project [("lastInsertID", BinExpr (OpOther "last_insert_id()") (ConstExpr "") (ConstExpr ""))] Empty


now I can do something like:

filebug assigned_to short_desc product component =
    do i <- doInsertLastID B.bugs ( B.bug_id << sqlnull #
				    B.groupset << constant 0 #
				    B.assigned_to << constant assigned_to #
				    B.bug_file_loc << constant Nothing #
				    B.bug_severity << constant "normal" #
				    B.bug_status << constant "NEW" #
				    B.creation_ts << sqlnow #
				    B.delta_ts << sqlnow #
				    B.short_desc << constJust short_desc #
				    B.op_sys << constant "other" #
				    B.priority << constant "P1" #
				    B.product << constant product #
				    B.rep_platform << constJust "PC" #
				    B.reporter << constant assigned_to #
				    B.version << constant "Marlin" #
				    B.component << constant component #
				    B.resolution << constant "" #
				    B.target_milestone << constant "---" #
				    B.qa_contact << constant 0  #
				    B.status_whiteboard << constant "" #
				    B.votes << constant 0 #
				    B.keywords << constant "" #
				    B.lastdiffed << sqlnow #
				    B.everconfirmed << constant 1 #
				    B.reporter_accessible << constant 1 #
				    B.cclist_accessible << constant 1
				  )
       return i


NOTE: I originally named the field 'last_insert_id' which worked fine
under mysql 4.1.X but not under mysql 3.23.X. 

OTHER NOTE: yes, MySQL 4.1.X is required if you need sub-selects, but
since I was just doing a bunch of inserts and simple selects, 3.23
works fine.

FINAL NOTE: This is all a hack and should probably be hidden from the
user more elegantly.

Jeremy Shaw.

[Haskelldb-users] Re: HaskellDB question

[Bjorn B. <d00...@dt...>]

Wijffelaars, M.W.H. wrote:
> Hi,
> 
> We have a question about HaskellDB on which we saw you were working.
> (We came along pictures of you and your haskell cake :). We are also
> working on a project for school and we have to make a MySQL database,
> but the implementation has to be in Haskell. So we found HaskellDB
> but we cannot get it to run under windows. We hoped you or maybe
> someone else from your group could help us with that.
> 
> When we are trying to make a testprogram based on the example
> programs on the website. But when we try to compile the
> insert-update-delete program it fails.
> 
> ghc --make -package haskelldb -o insert-update-delete
> insert-update-delete.hs
> 
> it says: unknown package haskelldb
> 
> Now we know that this is Windows and not Linux (on Linux it works
> btw), and windows deosn't really work with packages, but we also have
> no idea about what to do instead. Maybe the w32 version of GHC just
> doesn't work the way we expect it to.
> 
> We hope you can help us with this.
> 
> Thanks in advance, greetings
> 
> Kees van Kooten 
 > Martijn Wijffelaars

Hi Kees and Martijn,

I don't work with the win32 build, so I'm sending this to the haskelldb 
mailing list, has...@li... where you can get 
help from Victor who has created the win32 installer. In order to post 
to the mailing list, you need to subscribe at
http://lists.sourceforge.net/lists/listinfo/haskelldb-users The list is 
low traffic and gets no spam.

How did you find our cake by the way? :)

/Bjorn

Re: [Haskelldb-users] Cartesian product

[Bjorn B. <d00...@dt...>]

Thomas L. Bevan wrote:
> Thanks. I had a problem with the code below. It compiles but generates a 
> runtime exception.
> 
> Fail: user error (SqlError {seState = "E", seNativeError = 7, seErrorMsg = 
> "ERROR:  column \"username1\" does not exist\n"})
> 
> userIds = do
>  users <- table phpbb_users
>  group <- table phpbb_user_group
>  -- restrict (users!(U.user_id) .==. group!(G.user_id) )
>  project ( username << users!username #
>            user_email << users!user_email #
>            user_regdate << users!user_regdate #
>            group_id << group!group_id )
> 
> If I uncomment the restriction everything runs smoothly. I suspect that there 
> is a lacunae in the SQL generation code for cases where the JOIN is 
> unqualified.

Thanks for reporting this, it sure looks like a bug. I get the same 
thing with other joins without a restrict too, never tried that before. 
We'll look into it.

/Bjorn

Re: [Haskelldb-users] Cartesian product

[Thomas L. B. <tho...@to...>]

Thanks. I had a problem with the code below. It compiles but generates a 
runtime exception.

Fail: user error (SqlError {seState = "E", seNativeError = 7, seErrorMsg = 
"ERROR:  column \"username1\" does not exist\n"})

userIds = do
 users <- table phpbb_users
 group <- table phpbb_user_group
 -- restrict (users!(U.user_id) .==. group!(G.user_id) )
 project ( username << users!username #
           user_email << users!user_email #
           user_regdate << users!user_regdate #
           group_id << group!group_id )

If I uncomment the restriction everything runs smoothly. I suspect that there 
is a lacunae in the SQL generation code for cases where the JOIN is 
unqualified.

Tom


On Tue, 16 Mar 2004 07:19 pm, Bjorn Bringert wrote:
> Thomas L. Bevan wrote:
> > How is it possible to do join between tables?
>
> Hi Tom,
>
> here is an example of a join between two tables:
>
>        do
>        r <- table time_reports
>        u <- table users
>        restrict (r!userid .==. u!xid)
>        project (first_name << u!first_name
>                 # last_name << u!last_name
>                 # activity << r!activity)
>
> /Bjorn
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users

-- 
A poet who reads his verse in public may have other nasty habits.

Re: [Haskelldb-users] (no subject)

[Bjorn B. <d00...@dt...>]

> I am running a Gentoo GNU/Linux box with
>
> dev-db/postgresql-7.4.1-r4
> haskelldb-0.6
> HSQL-1.2
>
> I am connecting to Postgres via the Postgres driver built into HSQL.
>
> I created the database table with,
>
> CREATE TABLE test_tb1 (c11 INT NOT NULL, c12 INT NULL);
>
> The table description in the database is now,
>
>    Table "public.test_tb1"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  c11    | integer | not null
>  c12    | integer |
>
>
> Please find attached the output of DbDirect with arguments
>
> DbDirect PostgreSQL localhost test postgres postgres Dp037

Hi Tom,

HSQL 1.2.1 contains a fix for this problem. HSQL 1.2 had a bug in the
PostgreSQL driver that causes this behaviour. I actually committed that
fix myself over a month ago, but forgot to update the recommended HSQL
version for HaskellDB from 1.2 to 1.2.1. Sorry about that.

/Bjorn

Re: [Haskelldb-users] Cartesian product

[Bjorn B. <d00...@dt...>]

Thomas L. Bevan wrote:
> How is it possible to do join between tables?

Hi Tom,

here is an example of a join between two tables:

       do
       r <- table time_reports
       u <- table users
       restrict (r!userid .==. u!xid)
       project (first_name << u!first_name
                # last_name << u!last_name
                # activity << r!activity)

/Bjorn

[Haskelldb-users] auto_increment fields

[Jeremy S. <jer...@li...>]

Hello,

One problem I ran into using haskelldb (both the old and new
versions), is how do you deal with auto_increment fields?

Once in a fit of insanity I figured out this little hack:

sqlnull = (Expr (ConstExpr "NULL"))

newprofile = ( userid = sqlnull
	     , login_name = constant "jer...@li..."
	     , cryptpassword = sqlnull
	     , realname= constant "boing"
	     , groupset= constant 0
	     , disabledtext= constant "dis"
	     , mybugslink=constant (1 :: Int)
	     , blessgroupset= constant 0
	     , emailflags=constant "no"
	     )

But then I was immediatly faced with a second problem -- how do I find
out what the autogenerated ID is ?

So I hacked this up:

mysqlInsertNewId connection table assoc
	= do mysqlPrimCommand connection sql
	     res <- mysqlPrimQuery connection "select LAST_INSERT_ID();"
	     fields <- mysqlPrimFields res
	     return (getVal (MysqlValue (head (head fields))))
	where
	  sql	= show (ppInsert (toInsertNew table assoc))

A hack indeed! But, at the time I had to get things working fast, so I
didn't care ;) I should point out this this hack is actually fairly
safe. Note that the insert and the select LAST_INSERT_ID() are both
done on the same connection. As long as I hold that connection open,
LAST_INSERT_ID() will return the last autogenerated ID that was
inserted on that connection, even if records have been submitted in
the meantime on other connections.

See:
http://www.mysql.com/doc/en/Getting_unique_ID.html

I have only tried this with MySQL, so maybe its not a very portable
solution.

In any case, I am now faced with similar problems using the new
haskellDB. This time around, I thought it might be worthwhile to think
about the problem a bit longer :)

Jeremy Shaw.

[Haskelldb-users] Cartesian product

[Thomas L. B. <tho...@to...>]

How is it possible to do join between tables?

Tom

Re: [Haskelldb-users] (no subject)

[Thomas L. B. <tho...@to...>]

I am running a Gentoo GNU/Linux box with

dev-db/postgresql-7.4.1-r4
haskelldb-0.6
HSQL-1.2

I am connecting to Postgres via the Postgres driver built into HSQL.

I created the database table with,

CREATE TABLE test_tb1 (c11 INT NOT NULL, c12 INT NULL);

The table description in the database is now,

   Table "public.test_tb1"
 Column |  Type   | Modifiers
--------+---------+-----------
 c11    | integer | not null
 c12    | integer |


Please find attached the output of DbDirect with arguments

DbDirect PostgreSQL localhost test postgres postgres Dp037

Tom

Re: [Haskelldb-users] (no subject)

[Bjorn B. <d00...@dt...>]

Thomas L. Bevan wrote:
> Following your getting started instructions I think I may have found a bug.
> 
> DbDirect did not correctly identify nullable columns in a PostgreSQL table.
> Instead, it took them to be 'NOT NULL'. 
> 
> The example worked perfectly with MySQL however.

Hi Tom,

I just tested this on my machine and I can't seem to reproduce this 
problem. We'll need some more information to try to figure out what's 
happening:

The CREATE TABLE statement used to create the table (or a listing of the 
the table layout, as given by \d in psql).

The code produced by DbDirect for the table.

Are you using the PostgreSQL driver, or are you using PostgreSQL through 
ODBC?

What operating system are you using?

What versions of the following software are you using:

- HaskellDB
- HSQL
- PostgreSQL server
- PostgreSQL client libraries

If you are using ODBC, what versions you have of

- ODBC or unixODBC
- PostgreSQL ODBC driver


Cheers,

Bjorn Bringert
HaskellDB development team

[Haskelldb-users] (no subject)

[Thomas L. B. <tho...@to...>]

Following your getting started instructions I think I may have found a bug.

DbDirect did not correctly identify nullable columns in a PostgreSQL table.
Instead, it took them to be 'NOT NULL'. 

The example worked perfectly with MySQL however.


Tom

Re: [Haskelldb-users] SQLite support for haskelldb

[Bjorn B. <d00...@dt...>]

Jeremy Shaw wrote:
> Hello,
> 
> I add SQLite support to haskelldb. I have only done a simple select
> test, but it worked ;)
> 
> The sqlite.patches is hopefully all that is needed. I added the
> #ifdefs to the tests directory, but I did not actually run the tests
> again an sqlite database yet.
> 
> The Makefile.patch is not needed for the sqlite stuff. It seemed to
> me like the distclean target ought clean up a few more things that it
> actually does.
> 
> Jeremy Shaw.

Hi Jeremy,

your patches have been applied. They should be available in tomorrow's 
(2004-03-07) CVS snapshot.

Thanks for you help,

The HaskellDB development team

[Haskelldb-users] SQLite support for haskelldb

[Jeremy S. <Jer...@li...>]

Hello,

I add SQLite support to haskelldb. I have only done a simple select test, but it worked ;)

The sqlite.patches is hopefully all that is needed. I added the #ifdefs to the tests directory, 
but I did not actually run the tests again an sqlite database yet.

The Makefile.patch is not needed for the sqlite stuff. It seemed to me like the distclean 
target ought clean up a few more things that it actually does.

Jeremy Shaw.

Re: [Haskelldb-users] HaskellDB, CalendarTime, and MySQL

[Bjorn B. <d00...@dt...>]

Jeremy Shaw wrote:
> Hello,
> 
> I don't know if this has been fixed yet -- I am using the nightly tarball from 2004-02-23.
> 
> When I try to insert a date into a mysql database, the SQL string looks like:
> 
> INSERT INTO table1 (id,
> 		   date)
> VALUES (1,
> 	2004-February-27 12:0:0)
> 
> 
> MySQL does not like this and returns an error. I had to make two
> changes to make it happy:
> 
> (1) put quotes around the date string
> (2) use the number 2 instead of the word February
> 
> Here is a simple patch.
> 
> --- orig/src/Query.hs
> +++ mod/src/Query.hs
> @@ -306,8 +306,8 @@
>  instance ShowConstant CalendarTime where
>      showConstant (CalendarTime {ctYear = y, ctMonth = mo, ctDay = d, 
>  				ctHour = h, ctMin = mi, ctSec = s})
> -       = (show y)++"-"++(show mo)++"-"++(show d)++" "++
> -	 (show h)++":"++(show mi)++":"++(show s)
> +       = "\"" ++ (show y)++"-"++(show ((fromEnum mo) + 1))++"-"++(show d)++" "++
> +	 (show h)++":"++(show mi)++":"++(show s)++"\""
>  	 
>  
>  instance ShowConstant a => ShowConstant (Maybe a) where
> 
> 
> Jeremy Shaw.

Hi Jeremy,

thanks fo the bug report. We discovered this problem just a couple of 
days ago. The fix first appeared in the 2004-02-27 snapshot.

Thanks again for reporting it, and let us know if there are any other 
problems whatsoever.

/Bjorn Bringert

[Haskelldb-users] HaskellDB, CalendarTime, and MySQL

[Jeremy S. <jer...@li...>]

Hello,

I don't know if this has been fixed yet -- I am using the nightly tarball from 2004-02-23.

When I try to insert a date into a mysql database, the SQL string looks like:

INSERT INTO table1 (id,
		   date)
VALUES (1,
	2004-February-27 12:0:0)


MySQL does not like this and returns an error. I had to make two
changes to make it happy:

(1) put quotes around the date string
(2) use the number 2 instead of the word February

Here is a simple patch.

--- orig/src/Query.hs
+++ mod/src/Query.hs
@@ -306,8 +306,8 @@
 instance ShowConstant CalendarTime where
     showConstant (CalendarTime {ctYear = y, ctMonth = mo, ctDay = d, 
 				ctHour = h, ctMin = mi, ctSec = s})
-       = (show y)++"-"++(show mo)++"-"++(show d)++" "++
-	 (show h)++":"++(show mi)++":"++(show s)
+       = "\"" ++ (show y)++"-"++(show ((fromEnum mo) + 1))++"-"++(show d)++" "++
+	 (show h)++":"++(show mi)++":"++(show s)++"\""
 	 
 
 instance ShowConstant a => ShowConstant (Maybe a) where


Jeremy Shaw.

[Haskelldb-users] Nightly CVS snapshots now available

[Bjorn B. <d00...@dt...>]

Nightly snapshots of the HaskellDB CVS repository are now available from 
the HaskellDB home page, http://haskelldb.sourceforge.net/#download

The snapshots are automatically generated every morning at 0500 CET. 
They may contain fixes for recently reported bugs and new features, or 
may not work at all.

/Bjorn Bringert

[Haskelldb-users] HaskellDB Gettings Started guide available

[Bjorn B. <d00...@dt...>]

There is now a guide to getting started with HaskellDB available at 
http://haskelldb.sourceforge.net/getting-started.html

/Bjorn Bringert

Re: [Haskelldb-users] Fixat Haskell98 import och install target buggar

[Bjorn B. <d00...@dt...>]

I wrote:
> Krasimir Angelov rapporterade detta:
>=20
>  >     - The PrimQuery imports List module instead of
>  > Data.List which make HaskellDB dependent from
>  > Haskell98 package. In the haskelldb.pkg script the
>  > Haskell98 package is not added in the dependency list.
>  > The package works well with modified PrimQuery where
>  > the List is replaced with Data.List.
>  >
>  >     - In the makefile the ghc-pkg tool is called with
>  > -g option to autogenerate library for GHCi. The
>  > trouble here is that if there already exist HShdb.o
>  > object then the tool ignores the option. The work
>  > arround which I use in HSQL is to remove HShdb.o from
>  > the GHC directory before the install phase.
>=20
> B=E5da var enkla fixar som jag har lagt in i CVS.

Oops, sorry, wrong language. Was meant for a different list.
If you are curious, my comments mean:

Krasimir Angelov reported this:

and

Both are easy fixes that I have put into CVS.

/Bjorn

[Haskelldb-users] Fixat Haskell98 import och install target buggar

[Bjorn B. <d00...@dt...>]

Krasimir Angelov rapporterade detta:

 >     - The PrimQuery imports List module instead of
 > Data.List which make HaskellDB dependent from
 > Haskell98 package. In the haskelldb.pkg script the
 > Haskell98 package is not added in the dependency list.
 > The package works well with modified PrimQuery where
 > the List is replaced with Data.List.
 >
 >     - In the makefile the ghc-pkg tool is called with
 > -g option to autogenerate library for GHCi. The
 > trouble here is that if there already exist HShdb.o
 > object then the tool ignores the option. The work
 > arround which I use in HSQL is to remove HShdb.o from
 > the GHC directory before the install phase.

B=E5da var enkla fixar som jag har lagt in i CVS.

/Bjorn Bringert