Adding support for PostgreSQL, no new MySQLisms, please
Dear Koha-Devels, during the Marseille Hackfest 2012 I started together with Stephane and Christophe from BibLibre to add support for the PostgreSQL database. The ultimate goal of this work is that Koha can run on a PostgreSQL server as as good as it does now on a MySQL server. This mostly boils down to removing the MySQLisms in the current code, which I am currently doing. The collect-all-activity bug for this is 7365, it holds dependencies to the individual bugs like 7802, 7806 etc. (more to come, obviously). There is also a wiki page wiki.koha-community/wiki/PostgreSQL which I update frequently. This work also means that you must not add new MySQLisms in new code. Especially don't use backuotes in column names, write INSERT INTO foo (bar) VALUES (42); instead of INSERT INTO foo(`bar`) VALUES (42); For more idioms, consult above mentioned Wiki page, and if in doubt you can contact me, I am always willing to help with database issues. And please be advised that adding MySQLisms in new code will fail in QA. Thanks for your cooperation! If all goes well, this work should be concluded in a few months (but we are not in a hurry). - Marc Balmer
Hi Marc, On Fri, Mar 23, 2012 at 6:12 AM, Marc Balmer <marc@msys.ch> wrote:
Dear Koha-Devels,
during the Marseille Hackfest 2012 I started together with Stephane and Christophe from BibLibre to add support for the PostgreSQL database. The ultimate goal of this work is that Koha can run on a PostgreSQL server as as good as it does now on a MySQL server.
This mostly boils down to removing the MySQLisms in the current code, which I am currently doing. The collect-all-activity bug for this is 7365, it holds dependencies to the individual bugs like 7802, 7806 etc. (more to come, obviously). There is also a wiki page wiki.koha-community/wiki/PostgreSQL which I update frequently.
This is good work. I attempted to start this years ago, but soon dropped it due to my schedule.
This work also means that you must not add new MySQLisms in new code.
...
And please be advised that adding MySQLisms in new code will fail in QA.
While I agree with this proposition, we are not in the habit as a community of individual developers (or even groups of developers) "dictating" coding practices. The accepted procedure to follow is to present the change/addition via the list to solicit discussion and obtain consensus on the proposition. Furthermore, what "will" and "will not" pass QA is subject to the discretion of the community appointed QA manager, not other individuals. So I would suggest that we solicit some comment on this to give the community the opportunity to express its desire. Kind Regards, Chris
According to rule SQL6 of our Coding Guidelines ( http://wiki.koha-community.org/wiki/Coding_Guidelines#Database), backquotes are not acceptable, as they are a MySQL-ism. Part of the duty of QA is to verify that the coding guidelines are met, so it is reasonable to say that such markings *should* be stripped out of any new incoming patches. As to whether having them results in a "Failed QA" or a followup/rebased patch from a QA team member is up to the discretion and availability of that team member. I know I've been a little lax with this one, as we're still rife with backquotes in our SQL, and one or two more lines aren't going to add any significant additional work to our cleanup efforts. My primary interest is consistency, because it makes for easier to read and maintain code. Once we have done the majority of this cleanup, I'll become stricter about backquotes. The ultimate goal here, to the best of my understanding, is database independence, of which PostgreSQL support is a consequence. We should do our best to adhere to standards in all regards; by following SQL standard practices, we give ourselves more flexibility and adaptability, and decrease the overall potential workload throughout time. Cheers, -Ian 2012/3/23 Chris Nighswonger <cnighswonger@foundations.edu>
Hi Marc,
On Fri, Mar 23, 2012 at 6:12 AM, Marc Balmer <marc@msys.ch> wrote:
Dear Koha-Devels,
during the Marseille Hackfest 2012 I started together with Stephane and Christophe from BibLibre to add support for the PostgreSQL database. The ultimate goal of this work is that Koha can run on a PostgreSQL server as as good as it does now on a MySQL server.
This mostly boils down to removing the MySQLisms in the current code, which I am currently doing. The collect-all-activity bug for this is 7365, it holds dependencies to the individual bugs like 7802, 7806 etc. (more to come, obviously). There is also a wiki page wiki.koha-community/wiki/PostgreSQL which I update frequently.
This is good work. I attempted to start this years ago, but soon dropped it due to my schedule.
This work also means that you must not add new MySQLisms in new code.
...
And please be advised that adding MySQLisms in new code will fail in QA.
While I agree with this proposition, we are not in the habit as a community of individual developers (or even groups of developers) "dictating" coding practices. The accepted procedure to follow is to present the change/addition via the list to solicit discussion and obtain consensus on the proposition.
Furthermore, what "will" and "will not" pass QA is subject to the discretion of the community appointed QA manager, not other individuals.
So I would suggest that we solicit some comment on this to give the community the opportunity to express its desire.
Kind Regards, Chris
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
Am 23.03.12 17:23, schrieb Ian Walls:
According to rule SQL6 of our Coding Guidelines ( http://wiki.koha-community.org/wiki/Coding_Guidelines#Database), backquotes are not acceptable, as they are a MySQL-ism. Part of the duty of QA is to verify that the coding guidelines are met, so it is reasonable to say that such markings *should* be stripped out of any new incoming patches. As to whether having them results in a "Failed QA" or a followup/rebased patch from a QA team member is up to the discretion and availability of that team member.
I know I've been a little lax with this one, as we're still rife with backquotes in our SQL, and one or two more lines aren't going to add any significant additional work to our cleanup efforts. My primary interest is consistency, because it makes for easier to read and maintain code. Once we have done the majority of this cleanup, I'll become stricter about backquotes.
The ultimate goal here, to the best of my understanding, is database independence, of which PostgreSQL support is a consequence. We should do our best to adhere to standards in all regards; by following SQL standard practices, we give ourselves more flexibility and adaptability, and decrease the overall potential workload throughout time.
It is on my todo lists to very soon go through the code and provide a patch that removes the backquotes. I think we are on a good road now, and I try to provide small patches, that address one single problem each. While database independence is a noble goal, it is not achievable. You can support some databases, but not all, at least if you want to use some of the more advanced features a DB system has to offer you. And in an advanced and large application like Koha is, you probably want that. My guess is, that adding support for a second database will show what can be done and what not.
Marc, Without concrete examples of what RDMS-specific features would be desirable for us, I'm more inclined to reach for compliance with standards (and thus any standards-based RDMS) than for adding support for specific new RDMS systems. Now, of course every RDMS is different, and will implement the standard a little differently, so we can't code for every possible system, but I do believe we can get most of the ones worth having if we stick with the standards. Are there any specific features of PostgreSQL that would lead to a beneficial new feature for library patrons? I know there is plenty of literature on it's merits over MySQL, but how do those translate to something that the users of Koha can benefit from? Cheers, -Ian On Fri, Mar 23, 2012 at 13:48, Marc Balmer <marc@msys.ch> wrote:
According to rule SQL6 of our Coding Guidelines ( http://wiki.koha-community.org/wiki/Coding_Guidelines#Database), backquotes are not acceptable, as they are a MySQL-ism. Part of the duty of QA is to verify that the coding guidelines are met, so it is reasonable to say
Am 23.03.12 17:23, schrieb Ian Walls: that
such markings *should* be stripped out of any new incoming patches. As to whether having them results in a "Failed QA" or a followup/rebased patch from a QA team member is up to the discretion and availability of that team member.
I know I've been a little lax with this one, as we're still rife with backquotes in our SQL, and one or two more lines aren't going to add any significant additional work to our cleanup efforts. My primary interest is consistency, because it makes for easier to read and maintain code. Once we have done the majority of this cleanup, I'll become stricter about backquotes.
The ultimate goal here, to the best of my understanding, is database independence, of which PostgreSQL support is a consequence. We should do our best to adhere to standards in all regards; by following SQL standard practices, we give ourselves more flexibility and adaptability, and decrease the overall potential workload throughout time.
It is on my todo lists to very soon go through the code and provide a patch that removes the backquotes. I think we are on a good road now, and I try to provide small patches, that address one single problem each.
While database independence is a noble goal, it is not achievable. You can support some databases, but not all, at least if you want to use some of the more advanced features a DB system has to offer you. And in an advanced and large application like Koha is, you probably want that.
My guess is, that adding support for a second database will show what can be done and what not.
Am 23.03.12 21:39, schrieb Ian Walls:
Marc,
Without concrete examples of what RDMS-specific features would be desirable for us, I'm more inclined to reach for compliance with standards (and thus any standards-based RDMS) than for adding support for specific new RDMS systems. Now, of course every RDMS is different, and will implement the standard a little differently, so we can't code for every possible system, but I do believe we can get most of the ones worth having if we stick with the standards.
The right thing to do is, in order, 1) Use standard SQL wherever possible 2) Use DBI wherever possible 3) Use DBQ if there is really no other solution
Are there any specific features of PostgreSQL that would lead to a beneficial new feature for library patrons? I know there is plenty of literature on it's merits over MySQL, but how do those translate to something that the users of Koha can benefit from?
Yes, of course.
Cheers,
-Ian
On Fri, Mar 23, 2012 at 13:48, Marc Balmer <marc@msys.ch> wrote:
According to rule SQL6 of our Coding Guidelines ( http://wiki.koha-community.org/wiki/Coding_Guidelines#Database), backquotes are not acceptable, as they are a MySQL-ism. Part of the duty of QA is to verify that the coding guidelines are met, so it is reasonable to say
Am 23.03.12 17:23, schrieb Ian Walls: that
such markings *should* be stripped out of any new incoming patches. As to whether having them results in a "Failed QA" or a followup/rebased patch from a QA team member is up to the discretion and availability of that team member.
I know I've been a little lax with this one, as we're still rife with backquotes in our SQL, and one or two more lines aren't going to add any significant additional work to our cleanup efforts. My primary interest is consistency, because it makes for easier to read and maintain code. Once we have done the majority of this cleanup, I'll become stricter about backquotes.
The ultimate goal here, to the best of my understanding, is database independence, of which PostgreSQL support is a consequence. We should do our best to adhere to standards in all regards; by following SQL standard practices, we give ourselves more flexibility and adaptability, and decrease the overall potential workload throughout time.
It is on my todo lists to very soon go through the code and provide a patch that removes the backquotes. I think we are on a good road now, and I try to provide small patches, that address one single problem each.
While database independence is a noble goal, it is not achievable. You can support some databases, but not all, at least if you want to use some of the more advanced features a DB system has to offer you. And in an advanced and large application like Koha is, you probably want that.
My guess is, that adding support for a second database will show what can be done and what not.
On Fri, Mar 23, 2012 at 5:38 PM, Marc Balmer <marc@msys.ch> wrote:
Am 23.03.12 21:39, schrieb Ian Walls:
Marc,
...
Are there any specific features of PostgreSQL that would lead to a beneficial new feature for library patrons? I know there is plenty of literature on it's merits over MySQL, but how do those translate to something that the users of Koha can benefit from?
Yes, of course.
Such as? Kind Regards, Chris
El 23/03/2012 22:22, "Christopher Nighswonger" <chris.nighswonger@gmail.com> escribió:
On Fri, Mar 23, 2012 at 5:38 PM, Marc Balmer <marc@msys.ch> wrote:
Am 23.03.12 21:39, schrieb Ian Walls:
Marc,
...
Are there any specific features of PostgreSQL that would lead to a beneficial new feature for library patrons? I know there is plenty of literature on it's merits over MySQL, but how do those translate to something that the users of Koha can benefit from?
Yes, of course.
Such as?
ExtractValue for custom reports? To+
Tomas,
Are there any specific features of PostgreSQL that would lead to a
beneficial new feature for library patrons? I know there is plenty of literature on it's merits over MySQL, but how do those translate to something that the users of Koha can benefit from?
Yes, of course.
Such as?
ExtractValue for custom reports?
MySQL supports ExtractValue too. Is there something special in the Postgres functionality that makes a difference for the end user? Regards, Jared -- Jared Camins-Esakov Bibliographer, C & P Bibliography Services, LLC (phone) +1 (917) 727-3445 (e-mail) jcamins@cpbibliography.com (web) http://www.cpbibliography.com/
Op 25-03-12 02:28, Jared Camins-Esakov schreef:
MySQL supports ExtractValue too. Is there something special in the Postgres functionality that makes a difference for the end user?
Actually, this is something worth considering when supporting multiple databases: some reports may require a different syntax/function names. While it wouldn't be a blocker or anything like that, it's worth keeping in mind. Robin.
Am 24.03.12 14:44, schrieb Robin Sheat:
Op 25-03-12 02:28, Jared Camins-Esakov schreef:
MySQL supports ExtractValue too. Is there something special in the Postgres functionality that makes a difference for the end user?
Actually, this is something worth considering when supporting multiple databases: some reports may require a different syntax/function names. While it wouldn't be a blocker or anything like that, it's worth keeping in mind.
We adress this problem with the DBQ module, which exists to produce database specific SQL code when there is no standard way to express sth and when there is no way to get it through DBI. See the example we bundled with the path in bug 7365. - mb
Op 25-03-12 03:01, Marc Balmer schreef:
We adress this problem with the DBQ module, which exists to produce database specific SQL code when there is no standard way to express sth and when there is no way to get it through DBI. See the example we bundled with the path in bug 7365.
I'm not going to read the code because it's 3am, but does it rewrite the reports people enter or something? That seems dangerous unless done super-carefully. (Also, the DBQ stuff seems to be quite underdocumented. It doesn't even say what it is!) Robin.
Am 24.03.12 15:07, schrieb Robin Sheat:
Op 25-03-12 03:01, Marc Balmer schreef:
We adress this problem with the DBQ module, which exists to produce database specific SQL code when there is no standard way to express sth and when there is no way to get it through DBI. See the example we bundled with the path in bug 7365.
I'm not going to read the code because it's 3am, but does it rewrite the reports people enter or something? That seems dangerous unless done super-carefully.
No, it does not rewrite any reports. If users enter SQL somewhere, I assume they enter proper SQL for their DB server. Which leads to a small problem: Reports that people share should be flagged as MySQL only, PostgreSQL only, or portable. Well, at least once Koha runs on sth different than MySQL... I am considering to write a set of compatability functions for PostgreSQL that add the most used MySQL functions, but I don't know yet how to mimick server crashes and data loss that can occur in MySQL :>
(Also, the DBQ stuff seems to be quite underdocumented. It doesn't even say what it is!)
Id'd say it becomes clear once look at the bug and code. DBQ stands for Data Base Query and ir produces database specific SQL. It must only be used where there is no other way to get the job done (i.e. don't use it to produce DB specific code when there is a standard SQL way of doing so or when there is corresponding functionality in DBI). - mb
Op 25-03-12 03:19, Marc Balmer schreef:
No, it does not rewrite any reports. If users enter SQL somewhere, I assume they enter proper SQL for their DB server. Which leads to a
OK, so it doesn't address this (quite minor) problem.
I am considering to write a set of compatability functions for PostgreSQL that add the most used MySQL functions, but I don't know yet how to mimick server crashes and data loss that can occur in MySQL :>
I'd have a look through the reports library on the wiki and see what functions are in use. There won't be many. ExtractValue is the only common one I know of, dunno if the pgsql equivalent is syntactically the same.
Id'd say it becomes clear once look at the bug and code. DBQ stands for
The bug doesn't count once the code is in master. The code isn't clear because the documentation doesn't say anything about what it does, so there's no context. So no, not clear at all for someone trying to figure out what a particular call's purpose is. One particular example: =head2 ifNull $value = $dbq->ifNull($a, $b); Returns $a if not null, else return $b =cut This is actually wrong if you take it at face value out of context. It returns a string that does that in the database. That should be noted. But more importantly, there's no documentation saying anything about the purpose of all this. Don't assume people can work it out from the code because that's a waste of their time. Make it explicit. You're creating an API that can be produced by perldoc. (This said, it's nice to see perldoc comments in there at all - so much of the code doesn't have anything.) As an aside, the lack of placeholder use in these functions is a little scary. I know that's hard to do, but it'd be worth trying.
Data Base Query and ir produces database specific SQL. It must only be used where there is no other way to get the job done (i.e. don't use it to produce DB specific code when there is a standard SQL way of doing so or when there is corresponding functionality in DBI).
Then say this somewhere in the documentation. Robin.
Am 24.03.12 15:35, schrieb Robin Sheat:
Op 25-03-12 03:19, Marc Balmer schreef:
No, it does not rewrite any reports. If users enter SQL somewhere, I assume they enter proper SQL for their DB server. Which leads to a
OK, so it doesn't address this (quite minor) problem.
It would mean parsing the SQL and rewriting it. I think - besides that is hard to do right - it would be way over the top.
I am considering to write a set of compatability functions for PostgreSQL that add the most used MySQL functions, but I don't know yet how to mimick server crashes and data loss that can occur in MySQL :>
I'd have a look through the reports library on the wiki and see what functions are in use. There won't be many. ExtractValue is the only common one I know of, dunno if the pgsql equivalent is syntactically the same.
Id'd say it becomes clear once look at the bug and code. DBQ stands for
The bug doesn't count once the code is in master. The code isn't clear because the documentation doesn't say anything about what it does, so there's no context. So no, not clear at all for someone trying to figure out what a particular call's purpose is.
One particular example:
=head2 ifNull
$value = $dbq->ifNull($a, $b);
Returns $a if not null, else return $b
=cut
This is actually wrong if you take it at face value out of context. It returns a string that does that in the database. That should be noted. But more importantly, there's no documentation saying anything about the purpose of all this. Don't assume people can work it out from the code because that's a waste of their time. Make it explicit. You're creating an API that can be produced by perldoc. (This said, it's nice to see perldoc comments in there at all - so much of the code doesn't have anything.)
As an aside, the lack of placeholder use in these functions is a little scary. I know that's hard to do, but it'd be worth trying.
Data Base Query and ir produces database specific SQL. It must only be used where there is no other way to get the job done (i.e. don't use it to produce DB specific code when there is a standard SQL way of doing so or when there is corresponding functionality in DBI).
Then say this somewhere in the documentation.
FWIW, there is some explanations on the wiki at wiki.koha-community.org/wiki/PostgreSQL. It is work in progress, so see it in that context. In the end, it will be documented, it's just that we don't know yet which idioms exactly need to go into DBQ (it will become more clear once we can install Koha on PostgreSQL and are able to actually test the real functionality). - mb
Le 23/03/2012 13:27, Chris Nighswonger a écrit :
Hi Marc, Hi Chris,
Furthermore, what "will" and "will not" pass QA is subject to the discretion of the community appointed QA manager, not other individuals. Marc comment was made after a discussion we had during the hackfest, where I said him a mysqlism won't pass QA. The ` rule is in the coding guidelines, as Ian pointed, and I'll try to be vigilent on this matter
Marc a suggestion= once you'll have listed all "mysqlism" we could add a test to list them, and I could add some code to my pre-commit hook to check that no mysqlism is introduced ! -- Paul POULAIN http://www.biblibre.com Expert en Logiciels Libres pour l'info-doc Tel : (33) 4 91 81 35 08
Am 23.03.12 13:27, schrieb Chris Nighswonger:
Hi Marc,
On Fri, Mar 23, 2012 at 6:12 AM, Marc Balmer <marc@msys.ch> wrote:
Dear Koha-Devels,
during the Marseille Hackfest 2012 I started together with Stephane and Christophe from BibLibre to add support for the PostgreSQL database. The ultimate goal of this work is that Koha can run on a PostgreSQL server as as good as it does now on a MySQL server.
This mostly boils down to removing the MySQLisms in the current code, which I am currently doing. The collect-all-activity bug for this is 7365, it holds dependencies to the individual bugs like 7802, 7806 etc. (more to come, obviously). There is also a wiki page wiki.koha-community/wiki/PostgreSQL which I update frequently.
This is good work. I attempted to start this years ago, but soon dropped it due to my schedule.
This work also means that you must not add new MySQLisms in new code.
...
And please be advised that adding MySQLisms in new code will fail in QA.
While I agree with this proposition, we are not in the habit as a community of individual developers (or even groups of developers) "dictating" coding practices. The accepted procedure to follow is to present the change/addition via the list to solicit discussion and obtain consensus on the proposition.
Furthermore, what "will" and "will not" pass QA is subject to the discretion of the community appointed QA manager, not other individuals.
So I would suggest that we solicit some comment on this to give the community the opportunity to express its desire.
Supporting PostgreSQL is a stated goal of the Koha Community, so it is by sheer nature of the fact that no code can be accepted that is in violation with this goal. It was, however, Paul Poulain, who told me that he will fail MySQLisms in the future. That was not my idea, I only communicated it. And of course it makes sense.
participants (8)
-
Chris Nighswonger -
Christopher Nighswonger -
Ian Walls -
Jared Camins-Esakov -
Marc Balmer -
Paul Poulain -
Robin Sheat -
Tomas Cohen Arazi