reccommend the best database engine lazarus / 16-Databases ---------------------------------------------------------------------- jack616 · 2013-04-30 21:48 I'm creating an application with a built in mySQL database and was wondering if anyone had any advice on which engine (innoDB or MyIsaM) and which collation would be best to use if the database was to have the widest ease of use for people? The database will be fairly small but with linked tables but not updated very much. It will be a low access system and speed isn't much of an issue. Are there any real problems or considerations I'd need to consider? Does any of this even matter in my case do you think? thanks ---------------------------------------------------------------------- taazz · 2013-04-30 22:04 if the data are not updated a lot and the database is in single user mode ee local or embedded then myISAM has the best speed and probably is the wisest choice if on the other hand you need data integrity then innoDB would be my choice. of course I'm against using mysql my self since there are other more complete sql databases out there with a more liberal license like postgres or firebird. ---------------------------------------------------------------------- Leledumbo · 2013-05-01 01:54 Do you need referential integrity (big chance you will, unless your tables are not normalized to at least 3NF) and you want the engine to maintain it? Then InnoDB. Otherwise if you don't need one, or you are willing to maintain it by yourself, go for MyISAM. ---------------------------------------------------------------------- JJVillamor · 2013-05-01 04:12 Instead of a new thread, I hope this won't be much of an OT but rather an expansion of the discussion, what if? 1) speed is preferable 2) about 100-150 records daily 3) suitable for someone not well versed in Sql aside from some basic SQL statements ---------------------------------------------------------------------- BigChimp · 2013-05-01 06:24 @Jack: for my information does "linked tables" mean that MySQL can/will access tables in another database format? Or just the fact that you have set up referential integrity with joins etc? Instead of mysql, why not use Firebird embedded - I prefer this one because it is full-featured, well supported by FPC and allows a very easy upgrade to the server version -  or sqlite? Mysql support in FPC/Lazarus means you have to upgrade your connector every time you use a new client library. The connector may not be available etc. Also, I don't know about under what terms the application will be released, but MySQL licensing policy is rather strict... ---------------------------------------------------------------------- cpalx · 2013-05-01 13:40 postgresql is the best. ---------------------------------------------------------------------- exdatis · 2013-05-01 16:01 I have a very good experience with embedded Firebird, super-srv Firebird. Regards p.s. I have experience with Firebird 2.0 - 2.5 and PostgreSQL 8 - 9.2(excellent db) ---------------------------------------------------------------------- poiuyt555 · 2013-05-02 15:10 I have some very good expirience with 1c:enterprise 8. In my oppinion - create 2-10x faster your apps, rather than as usual. I was working with Visual foxpro earlier. But 1c - have some dependence in distribution of the 1c firm (get the info from 1c firm directly) and the source code of your app may be opened for the future external development (if i correct understand). But create your app - very easy: you dont need to write your own protection system, work in network environment, users, ets... - it is all already done. You just simply set your documents, directoryies and reports... And work... Also it has some build-in administrative tools: save/load your config and databese to an archive, automatic testing anf fixing, adding users, displaying active users, event log, external universal units ex. save and load any data between the equivalent configurations and ets... in my opinion, it provides the creating of your apps with the minimum of source code. Quote1) speed is preferable - 1c has native file server engine, and in my opinion, it works completely fast. (also as written it may works (from web):in a three-tier architecture with a separated application server (1C:Enterprise and MS SQL Server, PostgreSQL or IBM DB2 databases) Quote2) about 100-150 records daily i think this is not a problem for 1c. Quote3) suitable for someone not well versed in Sql aside from some basic SQL statements 1c has (form web) native SQL-like query language, supporting all SQL standard, but extending it with more business tasks-oriented features. but when i was looking for myself for completely independent solution i choose Lazarus+Firebird, but at this moment i do not check this variant and do not work with them. ---------------------------------------------------------------------- BigChimp · 2013-05-02 20:02 @jack616: what do you think of our responses? ---------------------------------------------------------------------- jack616 · 2013-05-03 15:45 My thanks to all who replied: Hi BC - Sorry I just typed a comprehensive answer here and the forum timed out before I could post it and then lost it. Perhaps the forum could be adjusted to keep posts that happens to somehow. MySQL is the only database package I would consider for this project. Essentially your assumptions are correct. But no other formats except I expect I'll include an import/export for CSV. There is no specific networked requirement. I'm not totally convinced I even need to normalise this to get the best out of it. That being said I'm starting out that way because the records are large and complex. The database will be FOC and no licence except a standard use at your own risk disclaimer. As for MySQL licensing - I have no problem with the current status. And client connectors - If FPC doesn't have them then I'll just have to stop using FPC. I don't have time to repeat my info right now (I have to cook tea!!!)  but that seems to be the gist I think. I've never distributed a database for widespread use and thought it worth asking. If anyone spots or knows of a specific issue feel free to pipe in. ---------------------------------------------------------------------- jack616 · 2013-05-03 15:46 PS.. sorry.. comments on collation are welcome - I'm not at all experienced with that issue... TIA jack ---------------------------------------------------------------------- taazz · 2013-05-03 16:09 1) no one asks for the license you are going to share the database or your application. 2) MySQL license does not allow to distribute a MySQL server or embedded engine in any kind of commercial application you need to buy a license to do that. You are not allowed to have mySQL's installation on the CD you distribute to your clients they have to downloaded and install it by them self. 3) I have never considered MySQL as a proper Database and although the features messing are not vital for web applications I would never dream to use it on any application that requires data integrity INNODB tables are closer to my liking but the last time I checked (around the 4.0 era) MySQL was nowhere near to my standards so I have moved on to other databases. Hearing you say that you will stop using lazarus if there is no MySQL support makes me ..... well to put it simple if you where my client you would get charged twice the salary from other clients and in advance. ---------------------------------------------------------------------- silvestre · 2013-05-03 16:20 Many Linux distributions are migrating from MySQL to MariaDB, compatible with the first. Would it be interesting to consider offering support in Lazarus? Note: http://sourceforge.net/projects/zeoslib/ available for Lazarus think supports MariaDB http://www.infoworld.com/t/open-source-software/wikipedia-dumps-mysql- hooks-mariadb-217820?source=footer ---------------------------------------------------------------------- Leledumbo · 2013-05-03 16:46 QuoteMany Linux distributions are migrating from MySQL to MariaDB, compatible with the first. Would it be interesting to consider offering support in Lazarus?Isn't it compatible even at API level? I mean, we don't really have to do anything to connect to MariaDB, simply treating it as MySQL. CMIIW. ---------------------------------------------------------------------- silvestre · 2013-05-03 17:03 Hi Leledumbo, I have no direct experience with MariaDB , maybe the following article will serve to clarify this issue. https://kb.askmonty.org/en/mariadb-vs-mysql-compatibility/ ---------------------------------------------------------------------- jack616 · 2013-05-03 21:51 This thread seems to have been invaded by ignorant trolls - I wont bother  reading it again. ---------------------------------------------------------------------- jwdietrich · 2013-05-03 23:29 Quote from: jack616 on May 03, 2013, 11:51:57 pm This thread seems to have been invaded by ignorant trolls - I wont bother  reading it again. I think that some mixed up the terms database engine and DBMS. ---------------------------------------------------------------------- Leledumbo · 2013-05-04 00:32 QuoteI think that some mixed up the terms database engine and DBMS.Actually the question is rather about storage engine (I don't know whether database engine refers to the same thing or not). ---------------------------------------------------------------------- fabienwang · 2013-05-04 04:16 @Jack616, for collations, i highly suggest to use utf8_general_ci. It supports lots of characters plus it's faster with string operations than utf8_unicode_ci Btw, Leledumbo was right, MariaDB is 100% compatible with MySQL at api level too (even binaries have the same name). ---------------------------------------------------------------------- jwdietrich · 2013-05-04 07:25 Quote from: Leledumbo on May 04, 2013, 02:32:43 amQuoteI think that some mixed up the terms database engine and DBMS.Actually the question is rather about storage engine (I don't know whether database engine refers to the same thing or not). You are right, see http://en.wikipedia.org/wiki/Database_engine. The terminology is a bit unfortunate, of course. ---------------------------------------------------------------------- avra · 2013-05-04 08:51 Quote from: jack616 on May 03, 2013, 11:51:57 pmThis thread seems to have been invaded by ignorant trollsAnd you don't think that your topic name had anything to do with it? ---------------------------------------------------------------------- JJVillamor · 2013-05-07 11:36 Quote from: poiuyt555 on May 02, 2013, 05:10:37 pmI have some very good expirience with 1c:enterprise 8. In my oppinion - create 2-10x faster your apps, rather than as usual. I was working with Visual foxpro earlier. ... but when i was looking for myself for completely independent solution i choose Lazarus+Firebird, but at this moment i do not check this variant and do not work with them. At the least, this is your second post where you seem to join in the discussion then promote "1C:Enterprise". Quote from: Leledumbo on May 03, 2013, 06:46:55 pmQuoteMany Linux distributions are migrating from MySQL to MariaDB, compatible with the first. Would it be interesting to consider offering support in Lazarus?Isn't it compatible even at API level? I mean, we don't really have to do anything to connect to MariaDB, simply treating it as MySQL. CMIIW. Can you share more info about MAriaDB? Right now my choices are SQLLite(easiest), Firebird (I've played around with IB a little), and PostgreSQL (most advance??). My leanings are towards firebird because it is embedable. ---------------------------------------------------------------------- Leledumbo · 2013-05-07 11:59 QuoteCan you share more info about MAriaDB? Err... what do you want to know about it? I think simply googling its name will reveal a lot about it. Even its history in relationship to MySQL is well explained everywhere. ---------------------------------------------------------------------- JJVillamor · 2013-05-07 12:40 Yes I've goggled a little. It's main intent is to be compatible with mysql. I don't really know. Maybe something important that could have been missed. I've read statements that with maridb there is no need to use mysql. ---------------------------------------------------------------------- Leledumbo · 2013-05-07 13:00 QuoteI've read statements that with maridb there is no need to use mysqlIndeed, that's the main point of MariaDB: a drop-in replacement for MySQL with additional features and better performance. ---------------------------------------------------------------------- marcov · 2013-05-07 13:29 Quote from: Leledumbo on May 03, 2013, 06:46:55 pmQuoteMany Linux distributions are migrating from MySQL to MariaDB, compatible with the first. Would it be interesting to consider offering support in Lazarus?Isn't it compatible even at API level? I mean, we don't really have to do anything to connect to MariaDB, simply treating it as MySQL. CMIIW. Mysql isn't compatible with itself at API level ---------------------------------------------------------------------- Leledumbo · 2013-05-07 14:54 QuoteMysql isn't compatible with itself at API level  Let me rephrase: Mysql isn't compatible with itself of different version including minor at API level ---------------------------------------------------------------------- poiuyt555 · 2013-05-07 16:07 QuoteAt the least, this is your second post where you seem to join in the discussion then promote "1C:Enterprise".Sorry. I promise - there will no be more in this topic (1c). Simply you ask: QuoteInstead of a new thread, I hope this won't be much of an OT but rather an expansion of the discussion, what if? 1) speed is preferable 2) about 100-150 records daily 3) suitable for someone not well versed in Sql aside from some basic SQL statementsI undurstand it like recomend any (not only disscussed here MariaDB and ets...). But my english is not very good and i may undesntand it not correct. On other thread i say truth about 1c, form my point of view, as i see it. Add: and i also recomend firebird too: Quotebut when i was looking for myself for completely independent solution i choose Lazarus+Firebird, but at this moment i do not check this variant and do not work with them