Re: [Haskell-cafe] Re: HDBC or HSQL
I am asking because I am trying to make HAppS a reasonable replacement for all contexts in which you would otherwise use an external relational database except those in which an external SQL database is a specific requirement. My experience with HAppS so far suggests that if one's data set is in the millions, an external database -- or some sort of off-ram storage mechanism is indeed a requirement, because even a 16GB server will eventually run out of memory. For example, I have a demo app (happstutorial.com) that stores jobs or users as records. On average, each record takes about 100B of memory, when checkpointed on hard disk. Not sure how much this amounts to in ram, but but on a 256M workstation with 256M of virtual memory, the checkpointing mechanism had an out of memory error with under 500,000 records. This doesn't seem totally unreasonable to me: 500,000 * 100 = 50,000,000B, which is 20% of my physical memory and 10% of my total (including virtual) memory. Also, as my recent post to haskell cafe says, I can't even fit a 20M element Data.Map.Map Int Int object into memory on my 256M laptop. On a 16GB server, you might get 32X the limit I ran into, but you will still have out of memory errors with 15 million records. Perhaps a recordset in the millions isn't a reasonable requirement, but most web 2.0 type projects are of this scope, I think. Thomas. 2007/8/4 Alex Jacobson [EMAIL PROTECTED]: Have you looked at the HAppS.DBMS.IxSet? It gives you a type safe way to query indexed collections. -Alex- Isto Aho wrote: Hi, I'd like to store small matrices into a db. Number of rows and columns may vary in a way not known in advance. One might use a relation (matrixId, col, row, value) or something like that but if it is possible to put a matrix in one command into db, some queries will be easier. E.g., one relation can store several matrices and it would be easy to query, how many matrices are stored currently. With that above four tuple you can find out the number of unique matrixId's, too, but it is not as easy as with matrices. Anyhow, now I'm not sure if I should stick with HSQL any more... Earlier comments on this thread made me think that maybe it would be a better idea to try to learn enough HDBC. This would be used in a server application. Is HAppS applicable here? e.g. after some tweaking the following works with HSQL: addRows = do dbh - connect server database user_id passwd intoDB dbh ([555,111, 50, 1000]::[Int]) ([21.0,22.0,23.0,24.0]::[Double]) intoDB dbh ([556,111, 50, 1000]::[Int]) ([21.0,22.0,23.0,24.0]::[Double]) intoDB dbh ([]::[Int]) ([]::[Double]) where intoDB dbh i_lst d_lst = catchSql (do let cmd = INSERT INTO trial (intList, dList) VALUES ( ++ toSqlValue i_lst ++ , ++ toSqlValue d_lst ++ ) execute dbh cmd ) (\e - putStrLn $ Problem: ++ show e) Similarly, queries can handle matrices and I like that it is now possible to select those columns or rows from the stored matrix that are needed. E.g. retrieveRecords2 :: Connection - IO [[Double]] retrieveRecords2 c = do -- query c select dList[1:2] from trial = collectRows getRow query c select dList from trial = collectRows getRow where getRow :: Statement - IO [Double] getRow stmt = do lst - getFieldValue stmt dList return lst readTable2 = do dbh - connect server database user_id passwd values - retrieveRecords2 dbh putStrLn $ dLists are : ++ (show values) br, Isto 2007/8/1, Alex Jacobson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]: Out of curiosity, can I ask what you are actually trying to do? I am asking because I am trying to make HAppS a reasonable replacement for all contexts in which you would otherwise use an external relational database except those in which an external SQL database is a specific requirement. -Alex- ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
Re: [Haskell-cafe] Re: HDBC or HSQL
For the reasons described in my previous message, I plan on looking into using takusen with HAppS. 2007/8/4 Alex Jacobson [EMAIL PROTECTED]: Have you looked at the HAppS.DBMS.IxSet? It gives you a type safe way to query indexed collections. -Alex- Isto Aho wrote: Hi, I'd like to store small matrices into a db. Number of rows and columns may vary in a way not known in advance. One might use a relation (matrixId, col, row, value) or something like that but if it is possible to put a matrix in one command into db, some queries will be easier. E.g., one relation can store several matrices and it would be easy to query, how many matrices are stored currently. With that above four tuple you can find out the number of unique matrixId's, too, but it is not as easy as with matrices. Anyhow, now I'm not sure if I should stick with HSQL any more... Earlier comments on this thread made me think that maybe it would be a better idea to try to learn enough HDBC. This would be used in a server application. Is HAppS applicable here? e.g. after some tweaking the following works with HSQL: addRows = do dbh - connect server database user_id passwd intoDB dbh ([555,111, 50, 1000]::[Int]) ([21.0,22.0,23.0,24.0]::[Double]) intoDB dbh ([556,111, 50, 1000]::[Int]) ([21.0,22.0,23.0,24.0]::[Double]) intoDB dbh ([]::[Int]) ([]::[Double]) where intoDB dbh i_lst d_lst = catchSql (do let cmd = INSERT INTO trial (intList, dList) VALUES ( ++ toSqlValue i_lst ++ , ++ toSqlValue d_lst ++ ) execute dbh cmd ) (\e - putStrLn $ Problem: ++ show e) Similarly, queries can handle matrices and I like that it is now possible to select those columns or rows from the stored matrix that are needed. E.g. retrieveRecords2 :: Connection - IO [[Double]] retrieveRecords2 c = do -- query c select dList[1:2] from trial = collectRows getRow query c select dList from trial = collectRows getRow where getRow :: Statement - IO [Double] getRow stmt = do lst - getFieldValue stmt dList return lst readTable2 = do dbh - connect server database user_id passwd values - retrieveRecords2 dbh putStrLn $ dLists are : ++ (show values) br, Isto 2007/8/1, Alex Jacobson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]: Out of curiosity, can I ask what you are actually trying to do? I am asking because I am trying to make HAppS a reasonable replacement for all contexts in which you would otherwise use an external relational database except those in which an external SQL database is a specific requirement. -Alex- ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
Re: [Haskell-cafe] Re: HDBC or HSQL
Will be pushing out the refactored happs repos in the next 2 weeks. The gist is: * HAppS.IxSet provides efficient query operations on haskell sets. * HAppS.State provides ACID, replicated, and soon sharded access to your application state. * HAppS.Network will provide server side HTTP functionality from which to access your replicated state. -Alex- Bulat Ziganshin wrote: Hello Alex, Wednesday, August 1, 2007, 8:34:23 AM, you wrote: I am asking because I am trying to make HAppS a reasonable replacement for all contexts in which you would otherwise use an external relational database except those in which an external SQL database is a specific requirement. where i can read about such usage? ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
Re: [Haskell-cafe] Re: HDBC or HSQL
Have you looked at the HAppS.DBMS.IxSet? It gives you a type safe way to query indexed collections. -Alex- Isto Aho wrote: Hi, I'd like to store small matrices into a db. Number of rows and columns may vary in a way not known in advance. One might use a relation (matrixId, col, row, value) or something like that but if it is possible to put a matrix in one command into db, some queries will be easier. E.g., one relation can store several matrices and it would be easy to query, how many matrices are stored currently. With that above four tuple you can find out the number of unique matrixId's, too, but it is not as easy as with matrices. Anyhow, now I'm not sure if I should stick with HSQL any more... Earlier comments on this thread made me think that maybe it would be a better idea to try to learn enough HDBC. This would be used in a server application. Is HAppS applicable here? e.g. after some tweaking the following works with HSQL: addRows = do dbh - connect server database user_id passwd intoDB dbh ([555,111, 50, 1000]::[Int]) ([21.0,22.0,23.0,24.0]::[Double]) intoDB dbh ([556,111, 50, 1000]::[Int]) ([21.0,22.0,23.0,24.0]::[Double]) intoDB dbh ([]::[Int]) ([]::[Double]) where intoDB dbh i_lst d_lst = catchSql (do let cmd = INSERT INTO trial (intList, dList) VALUES ( ++ toSqlValue i_lst ++ , ++ toSqlValue d_lst ++ ) execute dbh cmd ) (\e - putStrLn $ Problem: ++ show e) Similarly, queries can handle matrices and I like that it is now possible to select those columns or rows from the stored matrix that are needed. E.g. retrieveRecords2 :: Connection - IO [[Double]] retrieveRecords2 c = do -- query c select dList[1:2] from trial = collectRows getRow query c select dList from trial = collectRows getRow where getRow :: Statement - IO [Double] getRow stmt = do lst - getFieldValue stmt dList return lst readTable2 = do dbh - connect server database user_id passwd values - retrieveRecords2 dbh putStrLn $ dLists are : ++ (show values) br, Isto 2007/8/1, Alex Jacobson [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]: Out of curiosity, can I ask what you are actually trying to do? I am asking because I am trying to make HAppS a reasonable replacement for all contexts in which you would otherwise use an external relational database except those in which an external SQL database is a specific requirement. -Alex- ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
Re[2]: [Haskell-cafe] Re: HDBC or HSQL
Hello Alex, Wednesday, August 1, 2007, 8:34:23 AM, you wrote: I am asking because I am trying to make HAppS a reasonable replacement for all contexts in which you would otherwise use an external relational database except those in which an external SQL database is a specific requirement. where i can read about such usage? -- Best regards, Bulatmailto:[EMAIL PROTECTED] ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
Re: [Haskell-cafe] Re: HDBC or HSQL
Hi, I'd like to store small matrices into a db. Number of rows and columns may vary in a way not known in advance. One might use a relation (matrixId, col, row, value) or something like that but if it is possible to put a matrix in one command into db, some queries will be easier. E.g., one relation can store several matrices and it would be easy to query, how many matrices are stored currently. With that above four tuple you can find out the number of unique matrixId's, too, but it is not as easy as with matrices. Anyhow, now I'm not sure if I should stick with HSQL any more... Earlier comments on this thread made me think that maybe it would be a better idea to try to learn enough HDBC. This would be used in a server application. Is HAppS applicable here? e.g. after some tweaking the following works with HSQL: addRows = do dbh - connect server database user_id passwd intoDB dbh ([555,111, 50, 1000]::[Int]) ([21.0,22.0,23.0,24.0 ]::[Double]) intoDB dbh ([556,111, 50, 1000]::[Int]) ([21.0,22.0,23.0,24.0 ]::[Double]) intoDB dbh ([]::[Int]) ([]::[Double]) where intoDB dbh i_lst d_lst = catchSql (do let cmd = INSERT INTO trial (intList, dList) VALUES ( ++ toSqlValue i_lst ++ , ++ toSqlValue d_lst ++ ) execute dbh cmd ) (\e - putStrLn $ Problem: ++ show e) Similarly, queries can handle matrices and I like that it is now possible to select those columns or rows from the stored matrix that are needed. E.g. retrieveRecords2 :: Connection - IO [[Double]] retrieveRecords2 c = do -- query c select dList[1:2] from trial = collectRows getRow query c select dList from trial = collectRows getRow where getRow :: Statement - IO [Double] getRow stmt = do lst - getFieldValue stmt dList return lst readTable2 = do dbh - connect server database user_id passwd values - retrieveRecords2 dbh putStrLn $ dLists are : ++ (show values) br, Isto 2007/8/1, Alex Jacobson [EMAIL PROTECTED]: Out of curiosity, can I ask what you are actually trying to do? I am asking because I am trying to make HAppS a reasonable replacement for all contexts in which you would otherwise use an external relational database except those in which an external SQL database is a specific requirement. -Alex- ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
Re: [Haskell-cafe] Re: HDBC or HSQL
On 7/30/07, John Goerzen [EMAIL PROTECTED] wrote: On 2007-07-25, david48 [EMAIL PROTECTED] wrote: HDBC Supports Mysql only through ODBC :( This is true, unless some MySQL hacker would like to contribute a native module. I don't use MySQL myself and haven't had the time to write an interface to it. I'd be glad to do it but I'm a newbie in haskell, so I don't know where to get started. ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
Re: [Haskell-cafe] Re: HDBC or HSQL
Hi, I was also wandering between these different db-libs and thanks for your information. I tried several (HDBC, HSQL, HaskellDB) and made only small trials. HaskellDB has quite many examples on wiki that gave a quick start to further trials. But, I wasn't able to tell that some of the fields have default values and then it was already time to move on to the HSQL and HDBC trials. Is it possible to use sql-array-types with HDBC with postgresql? I don't remember was this the reason why I eventually tried HSQL - anyhow, it was rather difficult to get started with HDBC but the src test cases helped here. One example in a wiki would do miracles :) HSQL didn't have the array-types but it took only couple of hours to add a sort of support for those. There are some problems though... (indexed table queries returning some nulls is not yet working and ghci seems to be allergic to this) I was even wondering, should I propose a patch in some near future for this. But if HDBC can handle those sql-arrays or if you can give a couple of hints, how to proceed in order to add them there, given your view below, I'd be willing to try to help / to try to use HDBC. br, Isto 2007/7/30, John Goerzen [EMAIL PROTECTED]: On 2007-07-25, George Moschovitis [EMAIL PROTECTED] wrote: I am a Haskell newbie and I would like to hear your suggestions regarding a Database conectivity library: HSQL or HDBC ? which one is better / more actively supported? I am the author of HDBC, so take this for what you will. There were several things that bugged me about HSQL, if memory serves: 1) It segfaulted periodically, at least with PostgreSQL 2) It had memory leaks 3) It couldn't read the result set incrementally. That means that if you have a 2GB result set, you better have 8GB of RAM to hold it. 4) It couldn't reference colums in the result set by position, only by name 5) It didn't support pre-compiled queries (replacable parameters) 6) Its transaction handling didn't permit enough flexibility I initially looked at fixing HSQL, but decided it would be easier to actually write my own interface from scratch. HDBC is patterned loosely after Perl's DBI, with a few thoughts from Java's JDBC, Python's DB-API, and HSQL mixed in. I believe it has fixed all of the above issues. The HDBC backends that I've written (Sqlite3, PostgreSQL, and ODBC) all use Haskell's C memory management tools, which *should* ensure that there is no memory leakage. I use it for production purposes in various applications at work, connecting to both Free and proprietary databases. I also use it in my personal projects. hpodder, for instance, stores podcast information in a Sqlite3 database accessed via HDBC. I have found HDBC+Sqlite3 to be a particularly potent combination for a number of smaller projects. http://software.complete.org/hdbc/wiki/HdbcUsers has a list of some programs that are known to use HDBC. Feel free to add yours to it. -- John ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe -- br, Isto ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
[Haskell-cafe] Re: HDBC or HSQL
On 2007-07-25, George Moschovitis [EMAIL PROTECTED] wrote: I am a Haskell newbie and I would like to hear your suggestions regarding a Database conectivity library: HSQL or HDBC ? which one is better / more actively supported? I am the author of HDBC, so take this for what you will. There were several things that bugged me about HSQL, if memory serves: 1) It segfaulted periodically, at least with PostgreSQL 2) It had memory leaks 3) It couldn't read the result set incrementally. That means that if you have a 2GB result set, you better have 8GB of RAM to hold it. 4) It couldn't reference colums in the result set by position, only by name 5) It didn't support pre-compiled queries (replacable parameters) 6) Its transaction handling didn't permit enough flexibility I initially looked at fixing HSQL, but decided it would be easier to actually write my own interface from scratch. HDBC is patterned loosely after Perl's DBI, with a few thoughts from Java's JDBC, Python's DB-API, and HSQL mixed in. I believe it has fixed all of the above issues. The HDBC backends that I've written (Sqlite3, PostgreSQL, and ODBC) all use Haskell's C memory management tools, which *should* ensure that there is no memory leakage. I use it for production purposes in various applications at work, connecting to both Free and proprietary databases. I also use it in my personal projects. hpodder, for instance, stores podcast information in a Sqlite3 database accessed via HDBC. I have found HDBC+Sqlite3 to be a particularly potent combination for a number of smaller projects. http://software.complete.org/hdbc/wiki/HdbcUsers has a list of some programs that are known to use HDBC. Feel free to add yours to it. -- John ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
[Haskell-cafe] Re: HDBC or HSQL
On 2007-07-25, david48 [EMAIL PROTECTED] wrote: On 7/25/07, George Moschovitis [EMAIL PROTECTED] wrote: I am a Haskell newbie and I would like to hear your suggestions regarding a Database conectivity library: HSQL or HDBC ? which one is better / more actively supported? HDBC Supports Mysql only through ODBC :( This is true, unless some MySQL hacker would like to contribute a native module. I don't use MySQL myself and haven't had the time to write an interface to it. That said, HDBC supports unixODBC quite nicely... ;-) -- John ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
