What database system to use? Guidance for a database newbie. lazarus / 16-Databases ---------------------------------------------------------------------- Gizmo · 2011-10-18 14:11 Hi I'm getting myself confused about what database system to potentially use for my next little project. The project will require access to a database from several users. Each user, when he logs in, will have a series of 'cases' and within each case will be a variety of values relating to each of his cases. I need it to potentially hold text as well as uploaded\attached files, such as pictures and word processing files. Ideally, I'd prefer my program to connect to a server database, as will other installations of the program within the same network. MSSQL Server is not an option. MySQL would be great but it's perhaps a bit overkill given that my application will not require the intensity of MySQL. So I thought about using SQLite, but that isn't server related I don't think and travels with the application - like Firefox, which uses SQLLite for many of it's settings files. So I read abot FireBird, which looks like a good compromise between the scale of MySQL and the simplicity of SQLLite, but having read http://wiki.freepascal.org/tiOPF and http://wiki.lazarus.freepascal.org/Firebird_in_action and http://sourceforge.net/projects/tiopf/files/ it seems it is not readily updated? I read another thread somewhere where a user stated that a lot of the modern methods don't work with FBLib anymore, due to it's age? So I'm not sure what to go with? I'd like to use FireBird, but it seems the Lazarus library for it is a bit out of date. Can anyone give me some guidance? ---------------------------------------------------------------------- xinyiman · 2011-10-18 14:24 I use firebird with success. But you can use the event as an object Zeos connection to the DB. ---------------------------------------------------------------------- marcov · 2011-10-18 14:24 Firebird should be the best test database driver in both FPC sqldb and Zeos.  I would avoid mysql because of its license and versioning problems. A third contender is postgresql. The links you post are about are mostly about tiopf which is an unrelated framework. ---------------------------------------------------------------------- fredycc · 2011-10-18 15:39 Quote from: marcov on October 18, 2011, 04:24:37 pmFirebird should be the best test database driver in both FPC sqldb and Zeos.  I would avoid mysql because of its license and versioning problems. A third contender is postgresql. The links you post are about are mostly about tiopf which is an unrelated framework. I think the same way, I like firebird along with features and easy installation and management, I use version 2.5.1 with Zeos both Lazarus and Delphi. More, because firebird on other RDBMS http://www.slideshare.net/mindthebird/why-firebird-fact-for-decision- makers ---------------------------------------------------------------------- JD · 2011-10-18 19:31 Firebird with either the Zeos library or IBX for Lazarus library works very well for me. ---------------------------------------------------------------------- Gizmo · 2011-10-18 19:44 OK, excellent. Having read somemore about Zeos (http://wiki.lazarus.freepascal.org/ZeosDBO), it seems like it is the most versatile framework, enabling use with all the main databases, including FireBird. I note it is at v 6.6.6 and v7.0 alpha has not changed since 2009. Is it still an active and continually developed framework? I don't want to start using something that is or is about to be discontinued which is why I am asking.  http://sourceforge.net/projects/zeoslib/files/Zeos%20Database%20Object s/ ---------------------------------------------------------------------- 99Percent · 2011-10-19 02:15 You can use PostgreSQL with Lazarus SQLdb directly with no need of 3rd party components like Zeos (which is overkill IMO). ---------------------------------------------------------------------- otorres · 2011-10-19 03:04 Quote from: 99Percent on October 19, 2011, 04:15:14 amYou can use PostgreSQL with Lazarus SQLdb directly with no need of 3rd party components like Zeos (which is overkill IMO). Of course, i use Firebird with SQLdb and works very fine, but the SQLdb have a problem, the params in design time for TSQLQuery not is storage in the form. ---------------------------------------------------------------------- Leledumbo · 2011-10-19 06:22 I would suggest SQLite3 as it's small, embedded, portable and easy to use. Its license is also very permissive. ---------------------------------------------------------------------- BigChimp · 2011-10-19 07:16 Quote from: LeledumboI would suggest SQLite3 as it's small, embedded, portable and easy to use I don't think this is a good idea as the OP needs multi-user access: Quote from: tedsmith on October 18, 2011, 04:11:15 pmThe project will require access to a database from several users. Each user, when he logs in, will have a series of 'cases' and within each case will be a variety of values relating to each of his cases. I need it to potentially hold text as well as uploaded\attached files, such as pictures and word processing files. Ideally, I'd prefer my program to connect to a server database, as will other installations of the program within the same network. +1 for Firebird, too. Simple installation, not much configuration required. Just works. Nice license (commercial use no problem). I use it with SQLDB but haven't used it in big projects. Nice free visual admin tool: FlameRobin. Several commercial tools, also with freeware versions. A lot of drivers (JDBC, ODBC, .Net,...) if you want to use multiple tools to connect to your database. You could even write a single user test/demo/small business version with an embedded Firebird database that works almost identically as client/server (the connection properties in your code differ a bit). I'd probably choose PostgreSQL otherwise, though I have very little experience with that & FPC/Lazarus. I don't trust MySQL either. ---------------------------------------------------------------------- marcov · 2011-10-19 09:52 Quote from: 99Percent on October 19, 2011, 04:15:14 amYou can use PostgreSQL with Lazarus SQLdb directly with no need of 3rd party components like Zeos (which is overkill IMO). I've no problems with postgresql and Zeos, it works fine. Afaik release versions of sqldb don't have proper transaction support for postgres, and in many cases sqldb is immature. I know, because I work on it :-) ---------------------------------------------------------------------- Leledumbo · 2011-10-19 17:27 QuoteI don't think this is a good idea as the OP needs multi-user access:Whoops! Sorry, didn't see that. ---------------------------------------------------------------------- goodname · 2011-10-19 18:31 Since your storing binary data you may want to do some research on blobs vs. file system storage. Basically do you store the binary data in a database or do you just store file links in a database. Have been using PostgreSQL with SQLDB for a little over a year. Have not had any problems with new database versions. Program runs unchanged on 8.4, 9.0, and 9.1. Any lack of features has had a good response from this forum. Do not know what Transaction support is missing but then there are SQLDB parts I have not figured out yet. Firebird is a popular database engine with Lazarus users. I have no personal experience with Firebird. Negative past experiences with MySQL meant it was not a choice for me. It's feature set is much more complete today then it was when I was using it. There is a new MySQL fork called Drizzle. http://www.drizzle.org/ ---------------------------------------------------------------------- TurboRascal · 2011-10-19 21:27 I like PostgreSQL. It's a mature, stable and very powerful RDBMS, yet it's not a resource hog. Comes with excellent administration tools and works fine with ZEOS and SQLDB. It's also profesionaly supported if needed, because it's corporate backed, and also has a professional feel overall. Quote from: marcov on October 19, 2011, 11:52:27 am... sqldb is immature. I know, because I work on it :-) Uh-oh... Is there any ETA when will it become more reliable? Will it ever become as stable as ZEOS? ---------------------------------------------------------------------- braximo · 2011-10-20 01:12 Also using Firebird with success! ---------------------------------------------------------------------- miquelmatas · 2011-10-20 07:17 Quote from: goodname on October 19, 2011, 08:31:54 pm There is a new MySQL fork called Drizzle. http://www.drizzle.org/ There's another fork named MariaDB http://mariadb.org/ from the original author of MySql. ---------------------------------------------------------------------- fabienwang · 2011-10-20 09:34 Time to forget mysql... PostgreSQL allows more complex queries :p ---------------------------------------------------------------------- BigChimp · 2011-10-20 09:40 Quote from: FabienWang on October 20, 2011, 11:34:33 amTime to forget mysql... Agreed! At least using SQLDB you'd have a lot of trouble as the required components differ depending on which MySQL client libraries you have installed. Apparently the MySQL people like breaking things... Don't know about the forks though. I'd not even look at MySQL but at PostgreSQL or Firebird... ---------------------------------------------------------------------- TurboRascal · 2011-10-20 15:55 Quote from: FabienWang on October 20, 2011, 11:34:33 amTime to forget mysql... PostgreSQL allows more complex queries :p Some people have even gone so far as to question if MySQL can be called a proper SQL  DBMS at all Quote from: BigChimp on October 20, 2011, 11:40:11 amI'd not even look at MySQL but at PostgreSQL or Firebird... Yes, it really seems that practical choice boils down just to those two options; and with sqlite as an alternative for single user access databases... I don't actually know what merits could say if postgres or firebird is better, it seems that it mostly depends on personal choices, habits and by simply how it "feels" to a person. Ok, there are also some differences which could be important in making an informed choice, but I don't have any special requirements to chose that way; someone else however might find some detail which one of them does better or the other completely lacks... ---------------------------------------------------------------------- Gizmo · 2011-10-20 21:56 Hi all An interesting debate. Firebird certainly seems to be popular. Since posting, I've had a few ideas, and I might aim to have a 'local' version of my program, that runs with a local database alongside the installation, and a 'server' version that runs in the way I originally stated - install the FireBird database on the server and have all the other users connect to it from each workstation. Due to my immaturity as a programmer, I might start small with option a) and then extend it to option b) once I've got the local version working well. What I don't want to have to do though is change the database architecture from one kind of database system to another between local and server versions. So with that in mind, am I correct in thinking I can use FireBird 'Embedded' mode for the 'local' version of my program, and it will work a bit like SQLIte, and then I can add\migrate the database code side of things to work with FireBird 'Classic' for the server version (http://www.slideshare.net/mindthebird/why-firebird-fact-for-decision- makers - slide 12\33). If I use SQLLite for the local version on the other hand, I'll then encounter a migration issue when\if I extend the program for larger use using FireBird? Ted ---------------------------------------------------------------------- BigChimp · 2011-10-21 08:10 Quote from: tedsmith on October 20, 2011, 11:56:19 pmSo with that in mind, am I correct in thinking I can use FireBird 'Embedded' mode for the 'local' version of my program, and it will work a bit like SQLIte, and then I can add\migrate the database code side of things to work with FireBird 'Classic' for the server version (http://www.slideshare.net/mindthebird/why-firebird-fact-for-decision- makers - slide 12\33). If I use SQLLite for the local version on the other hand, I'll then encounter a migration issue when\if I extend the program for larger use using FireBird? Ted Ted, You're right. You'd need to change the connection parameters. And if you're on a server where multiple clients can do (near) simultaneously updates, you'll have to think more about your transaction management. Along with a different way to backup your database, that's all that changes, I think. See e.g. http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1#Embedded_d atabase_without_code_changes You could try migrating from a local SQLite to client/server Firebird. You'd have to change the connection components (if you're using SQLDB). They have different flavours of SQL though, so you'd probably have to change some query SQL, but you may not, depending on the complexity. The advantage is that you get to test your code for cross-database compatibility  I'd take the embedded Firebird path though... ---------------------------------------------------------------------- marcov · 2011-10-21 08:28 Quote from: TurboRascal on October 20, 2011, 05:55:36 pm Some people have even gone so far as to question if MySQL can be called a proper SQL  DBMS at all True. But some of those were based on old versions, or wrongly tested (with a storage backend that deliberately cuts corners for performance reasons). Modern mysql passes the test more or less. Still it is simply not a compelling choice if you are free to choose. Quote from: TurboRascal on October 20, 2011, 05:55:36 pmQuote from: BigChimp on October 20, 2011, 11:40:11 amI'd not even look at MySQL but at PostgreSQL or Firebird... Yes, it really seems that practical choice boils down just to those two options; and with sqlite as an alternative for single user access databases... Depends on some requirements. Oracle is an option with Zeos, and if you are windows specific (clients too!), so is SQL Server. Microsoft or not, it is a decent database. But these cost money, and while there are free variants, but like Mysql it only makes sense to do that if you plan to use them if you plan to upgrade to the full thing later. QuoteI don't actually know what merits could say if postgres or firebird is better, it seems that it mostly depends on personal choices, habits and by simply how it "feels" to a person. Ok, there are also some differences which could be important in making an informed choice, but I don't have any special requirements to chose that way; someone else however might find some detail which one of them does better or the other completely lacks... To my knowledge firebird is better (read faster) if you need high write percentages with transactional workloads (btw also much faster then mysql, despite the noise from web newbies). Postgresql has more clustering and other multi-machine options. If you need non-intel (not x86,x86_64, so PPC,Sparc and ARM) support I would also still go with postgresql. Postgresql dialect is also more oracle compatible, so easier to push in an enterprise that already has Oracle. ---------------------------------------------------------------------- goodname · 2011-10-21 18:50 Quote from: marcov on October 21, 2011, 10:28:28 amTo my knowledge firebird is better (read faster) if you need high write percentages with transactional workloads (btw also much faster then mysql, despite the noise from web newbies). Saying that one database engine is faster than another usually leads to a benchmark showing the opposite is true. With mature engines performance issues usually have more to due with poorly built queries or indexes then it does with the engine itself. There are cases where the engine can make a difference such as when they are built to handle specific cases or are forks created to improve areas that the main branch is not addressing. ---------------------------------------------------------------------- TurboRascal · 2011-10-21 23:07 Quote from: marcov on October 21, 2011, 10:28:28 amTrue. But some of those were based on old versions, or wrongly tested (with a storage backend that deliberately cuts corners for performance reasons). Modern mysql passes the test more or less. Still it is simply not a compelling choice if you are free to choose. One of the main complaints is that it doesn't have schemes like "normal" DBMS-es do. Have they progressed in that direction too? I haven't read about it for a while... Quote from: marcov on October 21, 2011, 10:28:28 amTo my knowledge firebird is better (read faster) if you need high write percentages with transactional workloads (btw also much faster then mysql, despite the noise from web newbies). Postgresql has more clustering and other multi-machine options. If you need non-intel (not x86,x86_64, so PPC,Sparc and ARM) support I would also still go with postgresql. Like I said, it depends on the person and purpose To me, those postgres features "read better" I don't have a need for many transactions in a time, so some speed advantage doesn't mean much to me; however, postgres has great distributed database tools which is something I really need...