Database backend

Use this forum if you want to discuss a problem or ask a question related to a hMailServer beta release.
User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Database backend

Post by martin » 2007-09-16 12:43

EDIT: Before giving any suggestion in this thread, please be aware that a decision has already been made. :)

As you may know, the next version of hMailServer (v5) will be released as closed source (still free in terms of money and no adware or anything like that). This means that I can no longer bundle MySQL (due to its license). I've requested a quote from MySQL for bundling a embedded version of their software with hMailServer, but I assume that it will be well over my zero-dollar-budget. So I am trying to find some alternatives.

In version 5, I have implemented compatibility with PostgreSQL (which is a terrific database server both in functionality and performance) and I also have a test implementation for SQlite which seems to work fine. I was thinking of using PostgreSQL the same way as I use MySQL today but PostgreSQL is a bit more complicated than MySQL (for instance, it requires a separate low-privilegie user account to run, starts several parallel processes instead of threads among oter things). Suspect that "noobs" would very confused by PostgreSQL consideirng that the current MySQL "bundling" is already almost too complex..

SQlite on the other hand is extremly easy to use since it doesn't require the user to do anything at all (it's a single DLL in the users system). The downside is that it's considerable much slower than PostgreSQL and MySQL since it doesn't support concurrency. I think it will be fast enough for installations with roughly 15-30 users with "average" traffic but with more it will probably become too slow. Also, SQlite lacks some rather basic functionality which will make it harder for me to write code for it.

I could bundle MSSQL but that's like 50MB big which would seem bloated. I could also choose not to bundle anything but then it would be very complicated for new users to test hMailServer. Or I could have seperate installations - one clean with no bundled database server and one with postgres or sqlite.

Anyone got any good tips on other database servers which would be worth looking at? Or any opinions on this subject? :)
Last edited by martin on 2008-01-27 02:37, edited 1 time in total.

GlenC
Senior user
Senior user
Posts: 680
Joined: 2004-08-17 23:31
Location: Santiago, Chile

Post by GlenC » 2007-09-16 14:46

Maybe this is a stupid answer but... I kinda like your sqlite approach. I'm assuming that the installer will be intelligent enough to detect an SQL server running and ask the appropriate questions about which method the user would like to use.

It would also be nice if it were possible to include tools for migrating the database to another type of server if the user changed his mind at a later date.

It would seem to me to make the installation much simpler and for those admins who really need the performance it would be easy enough for them to download and install the database of their choice.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-09-16 16:02

Yes, I'm thinking similar thoughts as well. As long as there are ways to migrate from an embedded database to an external, the performance limitations won't be any real issue since the admin can always choose to install another free database server if more performance is needed..

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Post by ^DooM^ » 2007-09-16 17:02

I presume hMail will still run with MySQL for those that already have external databases?

Have you had a look at http://www.firebirdsql.org/

I have no idea if it's any good though.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-09-16 17:37

Yes, hMailServer will be able to continue to run towards MySQL. I will not distribute any software from MySQL AB though.

Yupp, I started to look closer at Firebird a few hours ago. Seems like it could be a good choice. I've read some reviews of it a few months ago and apparantly it didn't scale well at all. But I'm suspecting that performance is better than in SQlite and there is a version that can be used for embedding as well which is good.

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Post by ^DooM^ » 2007-09-16 18:15

Worth a try :)

koncept
Normal user
Normal user
Posts: 185
Joined: 2006-06-02 23:41
Contact:

Post by koncept » 2007-09-16 20:01

I would opt for what glenc suggested.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-09-16 20:54

koncept, do you mean the idea with using an embedded database or do you mean sqlite specifically? :)

koncept
Normal user
Normal user
Posts: 185
Joined: 2006-06-02 23:41
Contact:

Post by koncept » 2007-09-16 21:04

sorry, i meant the idea - should have clarified that... i don't have a preference for the embedded database (i'll be using mssql most of time). It looks like m$ might have a embedded version of sql, that might be easier than reinventing the db portion for sqlite or firebirdsql
http://www.microsoft.com/sql/editions/c ... fault.mspx

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-09-16 22:46

SQL Server 2005 Compact Edition looks pretty nice. It's limited to 4GB but that should be enough for most installations. Will look into it some more and see if there's any more limitations hidden in the docs.

User avatar
mattg
Moderator
Moderator
Posts: 20796
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

database

Post by mattg » 2007-09-17 01:42

The 4Gb limit I believe to be per database not per instance. I know of a developer who uses multiple databases per instance to have much more than 4GB of data available to a program. Another limitation is the use of only one processor thread. But this MS option would give most users a great easy option. The pros can set their own database connections like they do now.

I too have heard good things about Firebird. I know that some developers have used Firebird because of the unrestricted size limitations.

Matt
PS I'm not a tech - just a run of the mill user

plumsauce
Normal user
Normal user
Posts: 43
Joined: 2007-09-11 08:37
Contact:

Post by plumsauce » 2007-09-17 08:11

unless things have changed in the last few months, firebird does have size limitations.

minsik
Normal user
Normal user
Posts: 92
Joined: 2006-08-03 13:08
Location: South Australia

firebird is this big enough?

Post by minsik » 2007-09-17 13:29

Maximum size of database Practically unlimited using multiple database files (largest known database is over 980 GB) 32 TB using multiple files

Seems to be free unlimited as far as i can see. seems like a good alternative and not restricted that i can see...

Firebird is derived from Borland InterBase 6.0
source code. It is open source and has no dual
license. Whether you need it for commercial or
open source applications, it is totally FREE!
Firebird technology has been in use for 20 years,
which makes it a very mature and stable

The embedded version is an amazing variation
of the server. It is a fully featured Firebird
server packed in just a few files. It is very easy
to deploy, since there is no need to install the
server. It is ideal for CDROM catalogs, demos or
standalone desktop applications.
Windows XP
hmailserver (latest beta allways) (except db versions)
test config, trying to convince the boss to use hmail!
ASSP - front end SPam killer

fileman
Normal user
Normal user
Posts: 71
Joined: 2006-02-17 13:42
Location: Italy

Post by fileman » 2007-09-17 19:33

Hsqldb? I don't know how it work and is license, but is the openoffice base built-in db engine.... or open source :P
Last edited by fileman on 2007-09-17 19:39, edited 1 time in total.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-09-17 19:36

I will probably use Microsoft SQL Server 2005 Compact Edition. Seems to be very functional and the performance seems to be nice as well. Have nothing against Java, but it tends to be a bit slow and hsqldb is written in that. :)

fileman
Normal user
Normal user
Posts: 71
Joined: 2006-02-17 13:42
Location: Italy

Post by fileman » 2007-09-17 19:42

I'm using sql compact 3.5 (beta) and I think is a good solution for the hMS

hbruun
New user
New user
Posts: 16
Joined: 2006-10-29 04:08
Contact:

Post by hbruun » 2007-09-18 02:42

SQlite should be lightning fast with a little work to deal with the concurrency issue on the hMailServer side. Transaction handling is the area where you can get the big boost there.

I'm am however saddened by the info about going closed source. Being open source was one of the big pluses for me when I originally came across hMailServer. What caused you to go closed source?

koncept
Normal user
Normal user
Posts: 185
Joined: 2006-06-02 23:41
Contact:

Post by koncept » 2007-09-18 03:11

there is a discussion behind the closing the source here.

http://www.hmailserver.com/forum/viewtopic.php?t=9736

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-09-18 12:21

SQlite should be lightning fast with a little work to deal with the concurrency issue on the hMailServer side.
There's no concurrency issue on the hMailServer side - the concurrency issue is on the SQlite side. There's no way to "deal" with the issue on the hMailServer side since it has to do with how SQlite does lockings.

The documentation for SQlite states that other database servers may be better if there's a lot of clients or high concurrency, which is exactly the case with hMailServer..

buck
New user
New user
Posts: 28
Joined: 2005-07-22 15:06
Location: Stockholm, Sweden

Post by buck » 2007-09-18 13:56

martin wrote:I will probably use Microsoft SQL Server 2005 Compact Edition. Seems to be very functional and the performance seems to be nice as well. Have nothing against Java, but it tends to be a bit slow and hsqldb is written in that. :)
I think a more valid reason for ruling out hsql than being slow is the overall weight of Java as dependency. I actually think that using the embedded solution that involves least work (and thus frees more of your time for "more important" work Martin) would probably be the best one since performance will not be an issue for the users that elect to not run a "real" DB as backend. Postgresql, go-go-go!!! :wink:

Cheers,
Patrik

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-09-18 17:28

Actually, SQL Server 2005 Compact Edition will be easier to embed than Postgres so I'm leaning towards that. No "embedded" version of PostgreSQL exists as far as I know. I will probably finish it within a few days but there's still some other things to be finished before v5 is done. :)

buck
New user
New user
Posts: 28
Joined: 2005-07-22 15:06
Location: Stockholm, Sweden

Post by buck » 2007-09-18 19:08

The reference to Postgresql was with respect to prefered "real" DB not embedded.

westdam
Senior user
Senior user
Posts: 728
Joined: 2006-08-01 21:24
Location: Padova, Italy
Contact:

Post by westdam » 2007-09-18 20:10

mmm
SQL 2005 compact seems very nice..
anyway it's just for the "noob" or the people who havent a external DB backend. Also Firebird sounds nice but just see performance on chart, not in real. So martin, the version 5 will connect to : MS SQL 2005 express, compact ; MySQL 4x,5x ; Firebird?? ; PostgreSQL??

it's quite really??

westdam
Senior user
Senior user
Posts: 728
Joined: 2006-08-01 21:24
Location: Padova, Italy
Contact:

Post by westdam » 2007-09-18 20:22

mmm just a tought..
from the ms site comparing compact with express they said :
Reasons not to use


When you want to run as a service
what exactly they mean? compact edition cant be run as service? it depends on the applications it belong?

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-09-18 21:31

the version 5 will connect to : MS SQL 2005 express, compact ; MySQL 4x,5x ; Firebird?? ; PostgreSQL??
Please see my previous post in this thread. I'm leaning towards using SQL Server 2005 Compact edition.
what exactly they mean? compact edition cant be run as service?
SQL Server 2005 compact edition cannot be run as a Windows service. When used, it's run inside the proecss which is accessing the database, which will typically be hMailServer.exe.

westdam
Senior user
Senior user
Posts: 728
Joined: 2006-08-01 21:24
Location: Padova, Italy
Contact:

Post by westdam » 2007-09-20 01:42

martin wrote:
the version 5 will connect to : MS SQL 2005 express, compact ; MySQL 4x,5x ; Firebird?? ; PostgreSQL??
Please see my previous post in this thread. I'm leaning towards using SQL Server 2005 Compact edition.
what exactly they mean? compact edition cant be run as service?
SQL Server 2005 compact edition cannot be run as a Windows service. When used, it's run inside the proecss which is accessing the database, which will typically be hMailServer.exe.
ok so you probabily use ms sql compact ed. embedded instead mysql but my question was another : do hmailserver will connect also to firebird, postgresql ? or change mind back?

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-09-20 18:34

It will support PostgreSQL but not Firebird.

Martin

Pieter
Normal user
Normal user
Posts: 55
Joined: 2005-07-27 19:20

Post by Pieter » 2007-09-20 19:15

Just my two cents. Currently I'm using hMailServer with the built-in database-server. Honestly, I don't really care much for which database will be used as I'm running the server for a small personal network with only a handfull of users.
The most important thing for me is that the upgrade will be automatic (during installation and no manual stuff) and without loss of data. As long as that's the case, any database is fine by me.

westdam
Senior user
Senior user
Posts: 728
Joined: 2006-08-01 21:24
Location: Padova, Italy
Contact:

Post by westdam » 2007-09-21 01:14

thanks martin all is clear now :)

Frog
Normal user
Normal user
Posts: 55
Joined: 2005-07-28 10:46
Location: Sydney, Australia
Contact:

Post by Frog » 2007-10-31 18:56

SQL Server C.E. 4 is stunning wherever I've used it and 'nearly' as powerful for this kind of thing as its day-to-day brother SQL Server 2005 Standard Edition. I recommend it wholeheartedly!

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-10-31 19:06

Yupp, I've chosen SQL Server 2005 Compact Edition..

kmnair
New user
New user
Posts: 3
Joined: 2005-12-19 10:48

Post by kmnair » 2007-11-05 07:32

Thanks Martin for the claification.

But the question about connection to external MySQL batabases also needs clarification. Shall we assume that V.5 will allow connection to external MySQL DBs with the right connector installed. Aslo, will there be a db setup script included with the distro.

Thanks and regards.

kmnair

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-11-05 21:10

Shall we assume that V.5 will allow connection to external MySQL DBs with the right connector installed.
Yes. hMailServer requires a MySQL client (libmysql.dll) to connect to MySQL.
Aslo, will there be a db setup script included with the distro.
Yes. Just as before.

westdam
Senior user
Senior user
Posts: 728
Joined: 2006-08-01 21:24
Location: Padova, Italy
Contact:

Post by westdam » 2007-11-06 00:33

yes kmnair if I've understand right it's the same, except the standard db used. it's ms sql 2005 compact edition instead mysql but hmailserver can connect to MS SQL Express 2005, 2005 , 2000 , Mysql and so on.

it's right martin?

kmnair
New user
New user
Posts: 3
Joined: 2005-12-19 10:48

Post by kmnair » 2007-11-06 03:49

Thanks Martin for the clarification.

Regards

kmnair

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-11-08 19:07

westdam, yes.

fileman
Normal user
Normal user
Posts: 71
Joined: 2006-02-17 13:42
Location: Italy

Post by fileman » 2007-11-09 11:30

with SQL CE hMS use more or less resources (cpu, memory, etc )?

PeterWeb
Normal user
Normal user
Posts: 31
Joined: 2006-11-29 02:04

Post by PeterWeb » 2007-11-09 21:17

GlenC wrote:Maybe this is a stupid answer but... I kinda like your sqlite approach. I'm assuming that the installer will be intelligent enough to detect an SQL server running and ask the appropriate questions about which method the user would like to use.
Agreed, bundled sqlite should be fine for evaluation, and for many smaller users.

I definitely think you should keep Firebird in your support considerations; we've used it (and its Interbase original) for over a decade, often in high-intensity web applications. Easy install/admin - set and forget, solid as a rock.

pvh
New user
New user
Posts: 10
Joined: 2007-06-04 07:36

Mysql licence

Post by pvh » 2007-11-15 16:00

I am not that good in the world of licence, but I thought that if your hMailserver was OpenSource and under the GPL licence, you could still use free MySQL?

Pieter

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Post by ^DooM^ » 2007-11-15 17:02

Hmail 4.4 branch is open source. V5 will be released as closed source.

pvh
New user
New user
Posts: 10
Joined: 2007-06-04 07:36

Post by pvh » 2007-11-15 18:20

[quote="^DooM^"]Hmail 4.4 branch is open source. V5 will be released as closed source.[/quote]

I appreciate yr answer, but gathered that the reason of v5 had to do with MySql licences.

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Post by ^DooM^ » 2007-11-15 18:58

No, V5 was made closed source because Martin has invested a lot of time and effort in expanding hMail. Nothing to do with licensing.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2007-11-15 19:16

V5 was made closed source because Martin has invested a lot of time and effort in expanding hMail.
It's sort of the other way around - that I plan to invest a lot more time in it in the future. :)

pvh
New user
New user
Posts: 10
Joined: 2007-06-04 07:36

Post by pvh » 2007-11-15 20:13

[quote="martin"][quote]V5 was made closed source because Martin has invested a lot of time and effort in expanding hMail.[/quote]

It's sort of the other way around - that I plan to invest a lot more time in it in the future. :)[/quote]

Understandable and appreciated.

It is a good product and makes me happy to use it, anyway. :)

User avatar
PeterK2003
Normal user
Normal user
Posts: 126
Joined: 2005-07-20 17:08
Location: Catawissa, PA

Post by PeterK2003 » 2007-11-15 23:52

i am out of the loop so this is probably a stupid question but....

Why is it going to be closed source?

User avatar
Slug
Moderator
Moderator
Posts: 1369
Joined: 2005-03-13 05:42
Location: Sydney Australia
Contact:

Post by Slug » 2007-11-17 02:40

PeterK2003 wrote:i am out of the loop so this is probably a stupid question but....

Why is it going to be closed source?
This was answered 3 posts up :roll:

Michael
Missing Hmailserver ... Now running Debian servers

User avatar
PeterK2003
Normal user
Normal user
Posts: 126
Joined: 2005-07-20 17:08
Location: Catawissa, PA

Post by PeterK2003 » 2007-11-17 03:55

told you it was a stupid question!

SteveNix
New user
New user
Posts: 1
Joined: 2007-11-21 02:26

Post by SteveNix » 2007-11-21 02:38

Here is another vote for FireBird:

I have been using many RDBMSs over the years - all mentioned earlier - but I will stick to FireBird. And people are starting to notice the beauty of this product: See SF.net 2007 CCA http://sourceforge.net/community/cca07

Won 2 - Nominated in 4

Nuff said? :-D

Bimbusko
New user
New user
Posts: 18
Joined: 2007-10-22 02:14

MySQL Problem

Post by Bimbusko » 2008-01-04 01:49

I install HMAIL 5 B280 and i need use old MySQL Data.
But not work DBSETUP. Software freeze.
Upgrade not allowed ?

PS : Hmail 5.0 "close source" - what is this ? Its not freeware ?

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Post by ^DooM^ » 2008-01-04 03:20

hMail v5 will still be free just not open source.

V5 is still in alpha and as such has not yet been tested thoroughly.
I hope you made a backup of your old server before trying the upgrade.

rodolfor
Senior user
Senior user
Posts: 282
Joined: 2005-06-30 09:05
Location: Gubbio - Italy

Post by rodolfor » 2008-01-04 11:51

SQlite is the best alternative. It works well up to 100 account wich is the more than a large number of users asks.
It is simple and it can simplify the live.
Who wants grow to a larger scale could install mysql or mssql before installing hmail.
The setup of hmail could select from: sqlite, mssql and mysql and in case of mssql or mysql use an existing instance and connect or create the database using an existing login.
This is the simplest and powerful way.

timb0311
Normal user
Normal user
Posts: 50
Joined: 2006-09-22 20:15

Post by timb0311 » 2008-01-04 23:13

Just stick with MS SQL since hMail is for Windows. It is going to give you the better performance with the most flexibility.

You could allow users to install CE or Express version with hMail install. Or point to an existing instance of SQL Server they want to use, whether it be CE, Express, Standard, Enterprise, etc.

In any instance you get an mdf file which you can later detach/attach to any instance of SQL server no matter the version. Which comes in handy if you outgrow or switch versions for whatever reason.

It should work on all as long as you are not using any advanced features that you would only find in Standard or higher versions.

rireland
New user
New user
Posts: 11
Joined: 2008-01-21 20:26

Post by rireland » 2008-01-26 17:59

Stumbled over this thread; I have little business sticking my nose in, but 26 years devlopment experience prevents me from moving on:

MS SQL is overkill for your (our...?) needs; thick with peripheral and ancillary features that may be nice to have but add weight and complexity. Good multi-purpose database - which isn't your goal to distribute.

MySQL - ever since Sun took this over I've been skeptical about it's future on the Windows platform; recent releases only bear out my concerns. Good if you like corporate open source licensing (sort of an oxymoron there).

PostgreSQL - always been kinda slow and plodding - although their groups are all a-twitter about the fantastic performance increase of their new version. Makes me wonder...

Firebird - NOW we're cooking. Having used this in a commercial app some years ago, I'm quite fond of it. Lean, simple, very easy to integrate...

Just my two cents - and yeah, I have used them all.

Rick in Detroit

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Post by ^DooM^ » 2008-01-26 19:54

hMail is using SQLite not the full blow package and works perfectly well for current users needs.

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2008-01-26 20:05

hMailServer 5 is using Microsoft SQL Server Compact Edition and not SQlite. :)

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Post by ^DooM^ » 2008-01-26 21:30

That's what i meant :)

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2008-01-27 00:19

Yeah, I know you know. Just wanted to make it clear in case someone else reads this. But I don't think people read this thread. They read the first post, then see that it's so long and replies ignoring the previous discussions. :)

^DooM^
Site Admin
Posts: 13861
Joined: 2005-07-29 16:18
Location: UK

Post by ^DooM^ » 2008-01-27 02:29

Amend the first post :)

User avatar
martin
Developer
Developer
Posts: 6834
Joined: 2003-11-21 01:09
Location: Sweden
Contact:

Post by martin » 2008-01-27 02:37

Good idea. ;)

eballinas
New user
New user
Posts: 1
Joined: 2008-02-11 23:01

my vote for firebired

Post by eballinas » 2008-02-11 23:08

Firebird es excelent.
Free.
Fast.
Secure.
Standard.
Please, use firebird
firebirdsql.org

Post Reply