Firebird Embeded Database Help - Newbie lazarus / 16-Databases ---------------------------------------------------------------------- Monotoba · 2013-10-26 16:27 Hi All, (I can be long winded, so to cut to the questions, skip down to **** ) I have been tinkering with Free Pascal a week now and I have a project I want to tackle. It is a simple organizer/catalog app for years worth of photos and art work with the following features: 1. Give a simple drag and drop method to insert images into database. 2. Allow multiple images to be dragged and dropped at once. 3. Allow mass editing of images (to edit all common data such as series info). 4. Allow detailed editing of images records. 5. Store location of work (I have various locations on the property with many years of work filed away). 6. Store info on work on display, where it is being show, show contacts, etc. 7. Allow storage of keywords associated with piece. 8. Allow searching all information and return any results matching selected fields. 9. If no field selected limit search results to predetermined fields. 10. Provide a visually pleasing, and artistic look to application ( I don't want something that looks like a book keeping program). 11. Must be multi-platform so I can share it with friends and family (Linux, Mac OSX, and Windows Vista/7). 12. Must not require any complex database installation. Ok, so given this list I decided to write my app in either python or pascal. After looking at both I chose Pascal and Lazarus. Mostly because I write a lot of python code already and have not done Pascal coding since Delphi.  I enjoyed coding in pascal and so I thought it would be nice to get back to it. And, if written in FP it should run faster than if written in python.  Also, I saw that Firebird could be embedded in FP programs. **** So, my questions are: a. Is Firebird suitable for such a program where large image files will be stored in the database? b. If so, are there any tutorials on embedding firebird in FP programs. if not, is SqlLite a suitable alternative? c. Are there any tutorials on how to support drag and dropping of images in FP? d. Are there any tutorials on writing cross platform apps with FP including such things as handling file paths? Thank you all for your input and suggestions. ---------------------------------------------------------------------- taazz · 2013-10-26 17:07 Quote from: Monotoba on October 26, 2013, 06:27:10 pm**** So, my questions are: a. Is Firebird suitable for such a program where large image files will be stored in the database? No there is no RDBMS which will be suitable for such usage including SQLite, I have not tested an Document databases that are floating around though they might be better on manipulating huge BLOBs than an RDBMS. Quote from: Monotoba on October 26, 2013, 06:27:10 pmb. If so, are there any tutorials on embedding firebird in FP programs. if not, is SqlLite a suitable alternative? To embed firebird is as easy as using the embedded DLL library instead of the dbclient dll I think you can rename it to fbclient.dll included with your application and it will act both as an embedded server and a client for other firebird servers. Quote from: Monotoba on October 26, 2013, 06:27:10 pmc. Are there any tutorials on how to support drag and dropping of images in FP? In short you do not drop images you drop file names take a look at http://wiki.lazarus.freepascal.org/Drop_files_event Quote from: Monotoba on October 26, 2013, 06:27:10 pmd. Are there any tutorials on writing cross platform apps with FP including such things as handling file paths? Errm as long as you stick to stock components and you do not use any 3rd party controls I think your odds are very good to be able to compile the same project in multiple OSs. There is no tutorial you just make sure you stay away from OS specific units like windows.pas or libc on linux etc. ---------------------------------------------------------------------- engkin · 2013-10-27 02:05 Quote from: Monotoba on October 26, 2013, 06:27:10 pma. Is Firebird suitable for such a program where large image files will be stored in the database? When you say "large image files", 1-How many images do you plan to store, roughly? 2-What is the size of the largest image you have? I suggest that you take a look at Sqlite limits page. Mean while: Maximum length of a BLOB is around 2 gigabytes (231 - 1) Maximum database size is 140 terabytes (140,000 gigabytes) Quote from: taazz on October 26, 2013, 07:07:59 pmQuote from: Monotoba on October 26, 2013, 06:27:10 pm**** So, my questions are: a. Is Firebird suitable for such a program where large image files will be stored in the database? No there is no RDBMS which will be suitable for such usage including SQLite, I have not tested an Document databases that are floating around though they might be better on manipulating huge BLOBs than an RDBMS. Taazz, do you mind to elaborate a little more. I wonder what manipulation is needed beside storing/retrieving/deleting/modifying pictures that makes a database like Sqlite not suitable? ---------------------------------------------------------------------- taazz · 2013-10-27 02:27 The storing/retrieving part is serial, it looses completely the random access part that most of the image files will have inside any decent file system, that makes it slower and more memory hungry than the same actions on files inside a file system. To put it simple blobs are not designed to be used that way and a file system will always have better performance and would be easier to handle than any RDBMS on the market today and of course the bigger problem is that if the database file is for any reason damaged you will probably loose all the pictures stored in it while if a disk gets damaged there is a better chance to rescue some of them. ---------------------------------------------------------------------- engkin · 2013-10-27 03:11 Thank you, taazz. ---------------------------------------------------------------------- Groffy · 2013-10-27 08:23 I guess, because using the embedded version of Firebird, you don't need any network/multiuser capabilities for your software. This point can be a pro for storing images in databases. But as already said, depending from size of  images it will take time to store/retrieve them in database, and it will bloat the size of the database file tremendous. Unfortunately the size of the database file will not shrink again when deleting data from database unless you will execute a backup/restore cycle. Depending from which additional data you plan to store inside the database, you might have some additional programming work for keeping data consistency to the externaly stored image files. Best regards ---------------------------------------------------------------------- Monotoba · 2013-11-14 13:56 Thanks guys. I have heard all the arguments for storing images in rmdbs' for many years. There are still some good reasons to use the rdbms to store images. The most important one for this project is that it allows a single backup point. Taaz, your reasons are valid for many projects but as always, there are always trade offs to be made. I have used both methods when developing with MySQL. Sometimes you simply need a single point of storage to reduce complexity and performance and size trade offs are worth it. In this case, the large photos will be read in once and rarely read out. instead, thumbnails will be created and those small thumbnails will be used throughout the system for display purposes. The only time the actual photo data will be read out will be for printing the original image. So this is more of an archival situation. I want to be able to backup the data over a network. So not having to backup the database and then collect, compress, and transmit each image reduces the complexity of this app (which is important since I haven't worked in Pascal in many many years). I am not sure that firebird's dll will work under *nix systems? After looking into SqlLite I decided to go with it. As for image size, I would guess perhaps 250 megs max. ---------------------------------------------------------------------- taazz · 2013-11-14 14:41 no one said that there are no good reasons to store images in an rdbms I have used personally to store small images for GUIs (buttons, treeview icons etc) which could be changed any time required but never actually had the need to change them after the 1st data import. I simple said that using the database as an archiving mechanism for image files that could be opened, edited and saved at any time is the worst usage case for blobs and that was in the requirements you posted. Now you change it to no editing will be done so not much changing once they have imported. If you want a simple single file archive for your image and thumbnails I would still recommend some other kind of container, for example take a look on my code librarian http://sourceforge.net/projects/codelibrarian/ specificaly in the components directory you will find a port of the GPStructureStorage component which is a file system in a file type of component. You can have files, folders and attributes in each file. you can build a custom index file that you can use for fast searches or even use the TDBF component and add a table with all the information you might need and the path to the file it self. This gives you everything you need and it has the fast access times of a simple file. Any way I think that firebird embedded has an SO for linux as well I do not know how well it is supported in other systems though. The 250MB size is it of a single image or the total size of the database? if it is for a single image then better check the maximum file sizes for your database you might find your self in a pit.