I've read through a handfull of the comments posted here, and would like to add my own bit:
Why do you want to use one of these open-source database systems as your back-end? Call me out-of-place on this site, but databases are currently better in the closed-source world than in the open-source IMHO. You might ask why, well, if you're going to add some functionality to the database server (as an Open-Source-Developer) what are you going to choose? You'll choose the sexiest thing that's available -- something that'll boost speed on selects, or something that'll increase theefficiency of the storage. However, with this, a lot less time and effort gets spent on less noticeable features, like concurrency, transactions, rollbacks, etc. I'm not saying they don't get the attention, I'm saying they don't get AS MUCH attention as they do at a closed-source cathedral like Oracle (Remember, Oracle is the #2 software company in the world -- are you telling me as many people work on MySQL/msql, Ingres/Postgres etc. as work on the Linux Kernel?!?!?). When your ability to sleep at night rests on data being secure, I personally opt for something that has a little more development time and QA time under its belt than an open-source solution.
What am I saying? I'm asking why do you want to use MySQL as a back-end for anything? It's ONLY benefit over a true RDBMS is the speed at which it makes simple selects (SELECT * FROM tablename). That speed comes at the cost of Transactions, Concurrency, and Reliability. With that being said - there are certainly uses for it. Using it as the back-end for a website, and publishing data to it from a company-wide database would be a noteable example. HOWEVER, if you want reliability for your companies data, you *probably* want something more robust. The fact that you're coming from a Windows house makes it even more ludicrous. Sure, you can fiddle with any of these programs (take a look at the Sybase ASE 11.03 version released for Linux -- for FREE) and get them working and integrated with Windows, but why bother? MS SQL Server works quite seemlessly with the Windows world. Don't get me wrong -- I have issues with it, but it does a fairly good job for a small amount of money (*g* take a look at Oracle pricing).
Now, coming from a filemaker solution your boss has probably noticed that Access is the most similar product on the Market. When I say similar, I mean that it has the ability to create interfaces within the program, and yet also functions as a "database". I doubt you'll find that with any other product, instead you'll have to do coding (web -- PHP, ColdFusion, ASP, Perl, or machine -- VB, GTK+, etc.) to create the front-end. My two preferences there would be ColdFusion for the web (quick development time), and VB for the client (again -- windows environment, and VB works pretty damned well with Access / SQL Server).
The problem with Access is that it handles everything as a large file. If many people are accessing this information, then they each need a copy of this file -- and any time you make structural changes to the DB, you have to propogate new copies of the front-end to everyone (hence the preference to a web front-end, or VB where you can code in a check for the current frontend version, and force an update of the VB program if it is not the most recent).
If you are still set on using an open source database, please take a look at the Sybase ASE 11.0.3 for linux (Freely released, no support), as it takes a MS SQL Server ODBC driver, but operates smoothly within that realm. I believe you've seen postings before about how to link with MySQL databases, and you've seen the Postgres zealots trying to convert you (*grin*). However, if you're just looking to change the backend, you can link to these via ODBC in Filemaker5. Sure it's messy, but it won't be any cleaner with Access/msql,Mysql,Ingres,Postgres,Sybase -- about the only thing that'll work very smoothly is Access/MS SQL Server (the joy of a MS dominated world).
And please, if you're worried about the integrity of your data (I don't know how complex your FM dataset is), please, please, please consider using a Relations Database Management System like Sybase, Oracle, Informix, or MS SQL Server.
Every major database has extended and modified the SQL-92 standard to meet their needs. Transact-SQL (MS SQL Server / Sybase SQL Server) does it, PL-SQL (Oracle) does it. Access does it. (Haven't used Informix enough to know). Postgres does it. MySQL most definately does it. Most do come a bit closer to the SQL-92 than standard Access. The rough of it? If you're going to use a database system, you'll have to learn the finer points of the SQL dialect it uses, as the companies add additional features to differentiate themselves from the competition.
Micro$oft considers it a feature that you can piggyback queries passed through an ODBC connection. What does this mean? This means that websites using ODBC connections to run queries (translation: dynamic pages) are extremely vulnerable to "tinkering" with. Basically, if someone is passing variables into a page (say index.asp?variable=5) then you can piggyback your own query after that (say index.asp?variable=5%20DELETE%20FROM%20sysobjects). Or something. Of course you have to have permissions, and you have to understand SQL a bit -- but hey. 'tis a bit scary. See the link to phrack, the relevant info is down towards the bottom. Again, this is old -- as in from SQL Server 6.5 days.
Take a look at this posting from January (yes, I took a look at some SQL Server installs back then to verify it). Basically the default install gives a username of sa with a blank password. You would be surprised at the number of machines that fall for this (even though it is a rather obvious bug -- most people log in as SA to set up their initial production server). Looks like this is the quick bug fixing we can expect from closed source software.
MySQL is much faster in many aspects than a lot of database systems, however, it arrives at that speed by sacraficing transactions. So, if you are interested in speed above all else (slashdot here is a great example -- you have a lot of people trying to access the data from the website, and you want to minimize the load of each request) then MySQL is great. On the other hand, if you are dealing with mission critical data (say credit card transactions), and want to be able to sleep at night knowing that your data is protected, then you are much more interested in the journalling or transaction features of a real RDBMS (I think Devon summed it up nicely). There is a reason that most major e-commerce sites seem to be running Oracle (or one of the other "big boys").
Furthermore, the fact that you cannot nest subqueries drastically reduces the purported speed gains -- because you have to break long queries into several small ones, and parse them at the client end rather than have the DB handle all the parameters and returning a simple result set to you. The lack of stored procedures is rather pathetic in my mind. Stored procedures offer a lot of flexibility that can benefit most development teams. The lack of foreign key constraints is laughable -- this means that you need to rely on interfaces to restrict the data being entered into the database, and in my experience this will inevitably lead to piss-poor database integrity (I'm not saying it's impossible to maintain the integrity here, just a hell of a lot harder). And finally, table level locking. Only one user can write to a table at a time. *ICK* Imagine an interactive system online that requires users inputting data (say something like turbotax). Using MySQL you are limited to one person entering / modifying data at a single time by the database. This is not a good thing if you are expecting many simultaneous users, because you will start having "transactions" queue up, and before long the whole system will hang. There is a reason most database systems use row level locking (and some drop it down further).
I would have to fully support this article, MySQL (while a great product) is not a RDBMS. It does not provide methods for ensuring data integrity thus should not be used in any situation that requires the information in the database to be "secure".
MySQL is much faster in many aspects than a lot of database systems, however, it arrives at that speed by sacraficing transactions. So, if you are interested in speed above all else (slashdot here is a great example -- you have a lot of people trying to access the data from the website, and you want to minimize the load of each request) then MySQL is great. On the other hand, if you are dealing with mission critical data (say credit card transactions), and want to be able to sleep at night knowing that your data is protected, then you are much more interested in the journalling or transaction features of a real RDBMS (I think Devon summed it up nicely). There is a reason that most major e-commerce sites seem to be running Oracle (or one of the other "big boys").
Furthermore, the fact that you cannot nest subqueries drastically reduces the purported speed gains -- because you have to break long queries into several small ones, and parse them at the client end rather than have the DB handle all the parameters and returning a simple result set to you. The lack of stored procedures is rather pathetic in my mind. Stored procedures offer a lot of flexibility that can benefit most development teams. The lack of foreign key constraints is laughable -- this means that you need to rely on interfaces to restrict the data being entered into the database, and in my experience this will inevitably lead to piss-poor database integrity (I'm not saying it's impossible to maintain the integrity here, just a hell of a lot harder). And finally, table level locking. Only one user can write to a table at a time. *ICK* Imagine an interactive system online that requires users inputting data (say something like turbotax). Using MySQL you are limited to one person entering / modifying data at a single time by the database. This is not a good thing if you are expecting many simultaneous users, because you will start having "transactions" queue up, and before long the whole system will hang. There is a reason most database systems use row level locking (and some drop it down further).
I would have to fully support this article, MySQL (while a great product) is not a RDBMS. It does not provide methods for ensuring data integrity thus should not be used in any situation that requires the information in the database to be "secure".
>How difficult would it be to >create a program for all SQL based databases that >would create export scripts that you could import >into any engine that would recognize INSERT >statements? Does something like this exist?
Look into ODBC. I know, it has been tainted by microsoft -- but seriously, it is intended as a standard for communicating with databases. It requires a driver to connect to a database, and then the driver handles all conversions into the native SQL dialect. Code written to access an ODBC driver will work in Sybase/M$ SQL Server, Oracle, MySQL, Informix, Access -- you name it, providing you have a driver to integrate with the database.
As for the Database decision, you may want to take a look at the linux port of Sybase SQL Server ASE 11.0.3. I am more comfortable with the TransactSQL than I am with the MySQL dialect (specifically I found setting up users and permissions to be much easier). The downside (as pointed out to me by a friend) is that Sybase does not dynamically allocate space for the database. This means that you will have to manually increase the size of the database as it grows. This can be a major headache, or a minor irritation depending on your knowledge of SQL.
As a side note: Sybase's SQL Server can be linked with an Access database (what's this mean? you can set up a table in Access that references a table in the Sybase database. This is good for data entry / modification -- but you cannot modify the columns or the underlying database from Access). To do this, download the M$ SQL Server 7 driver from the M$ site (M$ SQL Server and Sybase SQL Server were the same until 6, then they both diverged -- but are still almost identical -- so you can use the free M$ driver to connect with the Sybase database). Then, make sure that your Sybase database is running on port 1433 (default for M$ SQL Server). Then open your Access database, select File->Get External Data->Link Tables. From here you aught to be set, follow the directions to connect to an ODBC datasource (if the MySQL database was set up with an ODBC driver you could use this database instead -- I only tried it with Sybase though). I was able to link tables from a Sybase database running at home, to an Access database at work. Fun Stuff!
Why do you need candy colored cases????? Rackmount cases are much preferred by all the computers I have interviewed!!! They say it gives them much more space to "lounge around", makes it easier for those upgraded parts to be added w/o damaging something else, not to mention the fact that the computers feel much more fashionable while wearing a rackmount case:)
Slashdot Mirror
Why do you want to use one of these open-source database systems as your back-end? Call me out-of-place on this site, but databases are currently better in the closed-source world than in the open-source IMHO. You might ask why, well, if you're going to add some functionality to the database server (as an Open-Source-Developer) what are you going to choose? You'll choose the sexiest thing that's available -- something that'll boost speed on selects, or something that'll increase theefficiency of the storage. However, with this, a lot less time and effort gets spent on less noticeable features, like concurrency, transactions, rollbacks, etc. I'm not saying they don't get the attention, I'm saying they don't get AS MUCH attention as they do at a closed-source cathedral like Oracle (Remember, Oracle is the #2 software company in the world -- are you telling me as many people work on MySQL/msql, Ingres/Postgres etc. as work on the Linux Kernel?!?!?). When your ability to sleep at night rests on data being secure, I personally opt for something that has a little more development time and QA time under its belt than an open-source solution.
What am I saying? I'm asking why do you want to use MySQL as a back-end for anything? It's ONLY benefit over a true RDBMS is the speed at which it makes simple selects (SELECT * FROM tablename). That speed comes at the cost of Transactions, Concurrency, and Reliability. With that being said - there are certainly uses for it. Using it as the back-end for a website, and publishing data to it from a company-wide database would be a noteable example. HOWEVER, if you want reliability for your companies data, you *probably* want something more robust. The fact that you're coming from a Windows house makes it even more ludicrous. Sure, you can fiddle with any of these programs (take a look at the Sybase ASE 11.03 version released for Linux -- for FREE) and get them working and integrated with Windows, but why bother? MS SQL Server works quite seemlessly with the Windows world. Don't get me wrong -- I have issues with it, but it does a fairly good job for a small amount of money (*g* take a look at Oracle pricing).
Now, coming from a filemaker solution your boss has probably noticed that Access is the most similar product on the Market. When I say similar, I mean that it has the ability to create interfaces within the program, and yet also functions as a "database". I doubt you'll find that with any other product, instead you'll have to do coding (web -- PHP, ColdFusion, ASP, Perl, or machine -- VB, GTK+, etc.) to create the front-end. My two preferences there would be ColdFusion for the web (quick development time), and VB for the client (again -- windows environment, and VB works pretty damned well with Access / SQL Server).
The problem with Access is that it handles everything as a large file. If many people are accessing this information, then they each need a copy of this file -- and any time you make structural changes to the DB, you have to propogate new copies of the front-end to everyone (hence the preference to a web front-end, or VB where you can code in a check for the current frontend version, and force an update of the VB program if it is not the most recent).
If you are still set on using an open source database, please take a look at the Sybase ASE 11.0.3 for linux (Freely released, no support), as it takes a MS SQL Server ODBC driver, but operates smoothly within that realm. I believe you've seen postings before about how to link with MySQL databases, and you've seen the Postgres zealots trying to convert you (*grin*). However, if you're just looking to change the backend, you can link to these via ODBC in Filemaker5. Sure it's messy, but it won't be any cleaner with Access/msql,Mysql,Ingres,Postgres,Sybase -- about the only thing that'll work very smoothly is Access/MS SQL Server (the joy of a MS dominated world).
And please, if you're worried about the integrity of your data (I don't know how complex your FM dataset is), please, please, please consider using a Relations Database Management System like Sybase, Oracle, Informix, or MS SQL Server.
Friends don't let friends use FileMaker
Every major database has extended and modified the SQL-92 standard to meet their needs. Transact-SQL (MS SQL Server / Sybase SQL Server) does it, PL-SQL (Oracle) does it. Access does it. (Haven't used Informix enough to know). Postgres does it. MySQL most definately does it. Most do come a bit closer to the SQL-92 than standard Access. The rough of it? If you're going to use a database system, you'll have to learn the finer points of the SQL dialect it uses, as the companies add additional features to differentiate themselves from the competition.
Micro$oft considers it a feature that you can piggyback queries passed through an ODBC connection. What does this mean? This means that websites using ODBC connections to run queries (translation: dynamic pages) are extremely vulnerable to "tinkering" with. Basically, if someone is passing variables into a page (say index.asp?variable=5) then you can piggyback your own query after that (say index.asp?variable=5%20DELETE%20FROM%20sysobjects ). Or something. Of course you have to have permissions, and you have to understand SQL a bit -- but hey. 'tis a bit scary. See the link to phrack, the relevant info is down towards the bottom. Again, this is old -- as in from SQL Server 6.5 days.
http://slashdot.org/articles/00/ 01/16/138234.shtml
Furthermore, the fact that you cannot nest subqueries drastically reduces the purported speed gains -- because you have to break long queries into several small ones, and parse them at the client end rather than have the DB handle all the parameters and returning a simple result set to you. The lack of stored procedures is rather pathetic in my mind. Stored procedures offer a lot of flexibility that can benefit most development teams. The lack of foreign key constraints is laughable -- this means that you need to rely on interfaces to restrict the data being entered into the database, and in my experience this will inevitably lead to piss-poor database integrity (I'm not saying it's impossible to maintain the integrity here, just a hell of a lot harder). And finally, table level locking. Only one user can write to a table at a time. *ICK* Imagine an interactive system online that requires users inputting data (say something like turbotax). Using MySQL you are limited to one person entering / modifying data at a single time by the database. This is not a good thing if you are expecting many simultaneous users, because you will start having "transactions" queue up, and before long the whole system will hang. There is a reason most database systems use row level locking (and some drop it down further).
I would have to fully support this article, MySQL (while a great product) is not a RDBMS. It does not provide methods for ensuring data integrity thus should not be used in any situation that requires the information in the database to be "secure".
Furthermore, the fact that you cannot nest subqueries drastically reduces the purported speed gains -- because you have to break long queries into several small ones, and parse them at the client end rather than have the DB handle all the parameters and returning a simple result set to you. The lack of stored procedures is rather pathetic in my mind. Stored procedures offer a lot of flexibility that can benefit most development teams. The lack of foreign key constraints is laughable -- this means that you need to rely on interfaces to restrict the data being entered into the database, and in my experience this will inevitably lead to piss-poor database integrity (I'm not saying it's impossible to maintain the integrity here, just a hell of a lot harder). And finally, table level locking. Only one user can write to a table at a time. *ICK* Imagine an interactive system online that requires users inputting data (say something like turbotax). Using MySQL you are limited to one person entering / modifying data at a single time by the database. This is not a good thing if you are expecting many simultaneous users, because you will start having "transactions" queue up, and before long the whole system will hang. There is a reason most database systems use row level locking (and some drop it down further).
I would have to fully support this article, MySQL (while a great product) is not a RDBMS. It does not provide methods for ensuring data integrity thus should not be used in any situation that requires the information in the database to be "secure".
>create a program for all SQL based databases that
>would create export scripts that you could import
>into any engine that would recognize INSERT
>statements? Does something like this exist?
Look into ODBC. I know, it has been tainted by microsoft -- but seriously, it is intended as a standard for communicating with databases. It requires a driver to connect to a database, and then the driver handles all conversions into the native SQL dialect. Code written to access an ODBC driver will work in Sybase/M$ SQL Server, Oracle, MySQL, Informix, Access -- you name it, providing you have a driver to integrate with the database.
As for the Database decision, you may want to take a look at the linux port of Sybase SQL Server ASE 11.0.3. I am more comfortable with the TransactSQL than I am with the MySQL dialect (specifically I found setting up users and permissions to be much easier). The downside (as pointed out to me by a friend) is that Sybase does not dynamically allocate space for the database. This means that you will have to manually increase the size of the database as it grows. This can be a major headache, or a minor irritation depending on your knowledge of SQL.
As a side note: Sybase's SQL Server can be linked with an Access database (what's this mean? you can set up a table in Access that references a table in the Sybase database. This is good for data entry / modification -- but you cannot modify the columns or the underlying database from Access). To do this, download the M$ SQL Server 7 driver from the M$ site (M$ SQL Server and Sybase SQL Server were the same until 6, then they both diverged -- but are still almost identical -- so you can use the free M$ driver to connect with the Sybase database). Then, make sure that your Sybase database is running on port 1433 (default for M$ SQL Server). Then open your Access database, select File->Get External Data->Link Tables. From here you aught to be set, follow the directions to connect to an ODBC datasource (if the MySQL database was set up with an ODBC driver you could use this database instead -- I only tried it with Sybase though). I was able to link tables from a Sybase database running at home, to an Access database at work. Fun Stuff!
Why do you need candy colored cases????? Rackmount cases are much preferred by all the computers I have interviewed!!! They say it gives them much more space to "lounge around", makes it easier for those upgraded parts to be added w/o damaging something else, not to mention the fact that the computers feel much more fashionable while wearing a rackmount case :)
Arthur
MarcusAurelius
Vespasian
Trajan