Why can't you use "where voided" & "where not voided" in HQL and then use Hibernate's configuration to manage the translation to the appropriate dialect -- e.g. using hibernate.query.substitutions or some other setting? I would much rather have some well-defined conventions for OpenMRS on how to properly handle booleans & work to get our code to follow the convention (even if it means that there are a few simple configuration steps needed to make the system work with Oracle) vs. abandoning any hope of support for one of the major database engines.
-Burke On Tue, Aug 30, 2011 at 12:01 PM, Saptarshi Purkayastha <[email protected]>wrote: > yes, even for HQL... the reason for this is that HQL has no way to > understand... > If you want to send an integer (voided=0) or where as from (voided='1'), > how is HQL or anybody to understand, unless you know the underlying table > may be implemented as a BOOLEAN or CHAR or INT > > --- > Regards, > Saptarshi PURKAYASTHA > > My Tech Blog: http://sunnytalkstech.blogspot.com > You Live by CHOICE, Not by CHANCE > > > On 30 August 2011 20:49, Darius Jazayeri <[email protected]>wrote: > >> Okay, even for HSQL queries? >> >> -Darius >> >> >> On Tue, Aug 30, 2011 at 11:00 AM, Saptarshi Purkayastha <[email protected] >> > wrote: >> >>> nope... it won't be a problem with Oracle >>> Liquibase will do the conversion to NUMBER(1) automatically and the JDBC >>> driver will convert setBoolean/getBoolean correctly to match these... >>> >>> So, it is not a problem or case of compatibility. It is only when a >>> database supports BOOLEAN type, it is unfair to treat it as a second-class >>> citizen. If the database treats BOOLEAN as second-class, we can't do much >>> otherwise. >>> >>> --- >>> Regards, >>> Saptarshi PURKAYASTHA >>> >>> My Tech Blog: http://sunnytalkstech.blogspot.com >>> You Live by CHOICE, Not by CHANCE >>> >>> >>> On 30 August 2011 19:45, Darius Jazayeri <[email protected]>wrote: >>> >>>> Just to clarify, we will be breaking Oracle compatibility, in order to >>>> provide Postgres compatibility, right? >>>> >>>> (I think that's a correct tradeoff, but want to make that explicit if >>>> we're doing it.) >>>> >>>> -Darius >>>> >>>> >>>> On Tue, Aug 30, 2011 at 6:05 AM, Ben Wolfe <[email protected]> wrote: >>>> >>>>> Ok, if backwards compatibility is only broken for people using >>>>> postgres, I'm fine changing the smallint-->boolean. Modules and core >>>>> could >>>>> continue to work with voided=1/voided=true in mysql. You should >>>>> find/change >>>>> as many places in core and change to just voided=true so that at least >>>>> core >>>>> works with postgres. If a module wants to support postgres they'll have >>>>> to >>>>> change their own sql to voided=true. >>>>> >>>>> Ben >>>>> >>>>> >>>>> On Tue, Aug 30, 2011 at 12:14 PM, Saptarshi Purkayastha < >>>>> [email protected]> wrote: >>>>> >>>>>> Ben, >>>>>> >>>>>> Then we will have to change the other part where instead of true or >>>>>> false, or setBoolean/getBoolean, we will have to change those to >>>>>> setInt/getInt, This will be needed to be done in HSQL, ResultSet and >>>>>> other >>>>>> places because there is inconsistency on either sides. So the changes >>>>>> have >>>>>> to be done one way or the other, if you have to make it work in different >>>>>> database engines. >>>>>> >>>>>> Since our Objects have Boolean properties, I suggested that we make >>>>>> the columns liquibase boolean from smallint. It helps keep someone >>>>>> watching >>>>>> the columns sane... >>>>>> >>>>>> >>>>>> >>>>>> --- >>>>>> Regards, >>>>>> Saptarshi PURKAYASTHA >>>>>> >>>>>> My Tech Blog: http://sunnytalkstech.blogspot.com >>>>>> You Live by CHOICE, Not by CHANCE >>>>>> >>>>>> >>>>>> On 30 August 2011 12:33, Ben Wolfe <[email protected]> wrote: >>>>>> >>>>>>> Roger, this is all hidden behind the scenes in the dao layer. The >>>>>>> api methods and objects do not need to change regardless of what >>>>>>> decision is >>>>>>> made. The conversation you are thinking of was also started by >>>>>>> Saptarshi: >>>>>>> "Why do we have '(big b) Boolean isVoided()' *AND* '(big b) Boolean >>>>>>> getVoided()'?" >>>>>>> >>>>>>> The best approach really is to just leave it as we have it: store all >>>>>>> booleans as integers and refer to them as integers in the code. This >>>>>>> means >>>>>>> we don't break backwards compatibility (which modules developers HATE) >>>>>>> and >>>>>>> also that we're compatible with all database engines. >>>>>>> >>>>>>> Burke/Saptarshi: That regex won't find queries created with Criteria >>>>>>> queries or with HSQL. >>>>>>> >>>>>>> Ben >>>>>>> >>>>>>> >>>>>>> On Mon, Aug 29, 2011 at 9:20 PM, Friedman, Roger (CDC/CGH/DGHA) (CTR) >>>>>>> <[email protected]> wrote: >>>>>>> >>>>>>>> Wouldn't this also change all of our get methods to is methods? I >>>>>>>> know we had a thread about this recently but can't seem to find it. >>>>>>>> **** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> *From:* [email protected] [mailto:[email protected]] *On Behalf Of >>>>>>>> *Saptarshi >>>>>>>> Purkayastha >>>>>>>> *Sent:* Monday, August 29, 2011 1:13 PM >>>>>>>> *To:* [email protected] >>>>>>>> *Subject:* Re: [OPENMRS-DEV] Changes to liquibase xmls**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> Interesting suggestion Burke, to use NOT... and it works for MySQL, >>>>>>>> Postgres and H2... but alas not in SQL Server and Oracle (probably DB2 >>>>>>>> as >>>>>>>> well)!**** >>>>>>>> >>>>>>>> The voided='0' seems to be work on all databases**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> Also there are many more boolean columns than retired and voided. We >>>>>>>> also use voided = false at some places. Nonetheless that >>>>>>>> search-and-replace >>>>>>>> could be the first change.**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> PS: This <http://troels.arvin.dk/db/rdbms/> has been a seminal >>>>>>>> document that I've used lately.**** >>>>>>>> >>>>>>>> >>>>>>>> --- >>>>>>>> Regards, >>>>>>>> Saptarshi PURKAYASTHA >>>>>>>> >>>>>>>> My Tech Blog: http://sunnytalkstech.blogspot.com >>>>>>>> You Live by CHOICE, Not by CHANCE >>>>>>>> >>>>>>>> **** >>>>>>>> >>>>>>>> On 29 August 2011 22:00, Burke Mamlin <[email protected]> >>>>>>>> wrote:**** >>>>>>>> >>>>>>>> Is there any reason *not* to be using "where voided" and "where not >>>>>>>> voided" in queries instead of comparing to a number? Hopefully >>>>>>>> s/(voided|retired)\s*=\s*1/$1/g and s/(voided|retired)\s*=\s*0/not >>>>>>>> $1/g would be reasonably efficient and specific, making finding >>>>>>>> and fixing most of the code relatively easy. Checkstyle could look >>>>>>>> for this >>>>>>>> pattern & throw warnings stating the preferred convention for checking >>>>>>>> boolean values. I'm sure there are other boolean columns to be >>>>>>>> addressed >>>>>>>> and a simple search & replace might not fix them all, but there's no >>>>>>>> reason >>>>>>>> that they need to be all fixed in one step – i.e., (1) fix the >>>>>>>> low-hanging >>>>>>>> fruit, (2) put conventions ± checkstyle checks to reduce the >>>>>>>> likelihood that >>>>>>>> the problem will continue to grow, and then (3) seek out and fix the >>>>>>>> remaining cases focusing on core & distributed modules first.**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> -Burke**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> On Mon, Aug 29, 2011 at 9:26 AM, Michael Seaton <[email protected]> >>>>>>>> wrote:**** >>>>>>>> >>>>>>>> I share Saptarshi's concern, particularly if there are lots of >>>>>>>> modules out there that may be making the "voided = 0" assumptions in >>>>>>>> direct >>>>>>>> SQL queries. Will this be something that is not backwards compatible >>>>>>>> and >>>>>>>> requires lots of module changes in order to successfully upgrade?** >>>>>>>> ** >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> On 08/29/2011 02:18 AM, Ben Wolfe wrote: **** >>>>>>>> >>>>>>>> The changes should be down at the db layer, right? I think that >>>>>>>> should be fine. >>>>>>>> >>>>>>>> Is there a way to tell postgres to treat 0 like a 'false'? Perhaps >>>>>>>> with a db connection string property? >>>>>>>> >>>>>>>> Ben**** >>>>>>>> >>>>>>>> On Sun, Aug 28, 2011 at 5:42 AM, Saptarshi Purkayastha < >>>>>>>> [email protected]> wrote:**** >>>>>>>> >>>>>>>> One of the big problems that I'm encountering at the moment for >>>>>>>> making OpenMRS run on most database servers is with our assumption of >>>>>>>> the >>>>>>>> BOOLEAN type. Because MySQL stores BOOLEAN as TINYINT(1), we have made >>>>>>>> the >>>>>>>> assumption that BOOLEAN will be represented as 0 or 1. Thus, the >>>>>>>> column type >>>>>>>> for (retired, voided etc) in liquibase was using smallint (to make it >>>>>>>> ANSI-SQL :D) as the type and default as 0 (FALSE). This was fairly >>>>>>>> easy to >>>>>>>> change in the liquibase xmls by a search-and-replace (but still >>>>>>>> looking up >>>>>>>> all the columns) **** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> The bigger issue surrounding this assumption is in code, where we >>>>>>>> have made voided=0 in queries or getInt() from ResultSet. These are >>>>>>>> fine for >>>>>>>> the MySQL assumption, but doesn't work with other databases. This is a >>>>>>>> fairly big change to do, with changes at many places in code...**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> Are we ready?? **** >>>>>>>> >>>>>>>> **** >>>>>>>> >>>>>>>> --- >>>>>>>> Regards, >>>>>>>> Saptarshi PURKAYASTHA >>>>>>>> >>>>>>>> My Tech Blog: http://sunnytalkstech.blogspot.com >>>>>>>> You Live by CHOICE, Not by CHANCE >>>>>>>> >>>>>>>> **** >>>>>>>> >>>>>>>> On 18 August 2011 15:59, Ben Wolfe <[email protected]> wrote:**** >>>>>>>> >>>>>>>> The funny thing is that the liquibase changeset files were used for >>>>>>>> the initial scripts was for database portability. If you can export >>>>>>>> them as >>>>>>>> standard sql files and run those just as easily as the liquibase ones, >>>>>>>> go >>>>>>>> for it. The tricky thing to solve would be how to get the progress >>>>>>>> bars to >>>>>>>> work in the initialization wizard. >>>>>>>> >>>>>>>> You can either change the datatypes to a generic ansi standard ones, >>>>>>>> or change to something that liquibase knows about and converts to each >>>>>>>> different datatype on the different dbms's correctly. >>>>>>>> >>>>>>>> Ben **** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> On Wed, Aug 17, 2011 at 10:47 PM, Saptarshi Purkayastha < >>>>>>>> [email protected]> wrote:**** >>>>>>>> >>>>>>>> MySQL longtext is the same as the TEXT of postgresql... for the >>>>>>>> others we have to make database specific options between the data types >>>>>>>> **** >>>>>>>> >>>>>>>> tinyint to smallint in the liquibase-schema-only**** >>>>>>>> >>>>>>>> there are many similar difference that are database specific and we >>>>>>>> have to make database specific datatypes.**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> I'm yet to go through all the changes required... but will make as >>>>>>>> these come. But for datatypes that are incompatible, probably be >>>>>>>> database >>>>>>>> specific.**** >>>>>>>> >>>>>>>> Is that ok?? or we should move those to ANSI-standard ones only??** >>>>>>>> ** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> >>>>>>>> --- >>>>>>>> Regards, >>>>>>>> Saptarshi PURKAYASTHA >>>>>>>> >>>>>>>> My Tech Blog: http://sunnytalkstech.blogspot.com >>>>>>>> You Live by CHOICE, Not by CHANCE >>>>>>>> >>>>>>>> **** >>>>>>>> >>>>>>>> On 18 August 2011 00:48, Darius Jazayeri <[email protected]> >>>>>>>> wrote:**** >>>>>>>> >>>>>>>> Hi Saptarshi, **** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> In principle I'm fine with these, but let's get at the specifics of >>>>>>>> datatypes.**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> We probably use the mysql text, mediumtext, and longtext types a >>>>>>>> lot, because they're really usefully-sized datatypes. What will we >>>>>>>> replace >>>>>>>> those with?**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> What other datatypes are you expecting to change?**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> -Darius**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> On Wed, Aug 17, 2011 at 11:25 AM, Saptarshi Purkayastha < >>>>>>>> [email protected]> wrote:**** >>>>>>>> >>>>>>>> Hi, **** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> While we were recently discussing that OpenMRS should run on >>>>>>>> multiple database servers, I started working on Support Multiple >>>>>>>> Databases in OpenMRS Installation and >>>>>>>> Update<https://tickets.openmrs.org/browse/TRUNK-1925> >>>>>>>> .**** >>>>>>>> >>>>>>>> There are some of the following broad changes that need to be made >>>>>>>> the the liquibase xml so that we can install OpenMRS on different >>>>>>>> databases >>>>>>>> (target MySQL, Postgres, MsSQL... and may be later Oracle).**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> 1.) Change datatypes and create tables that are compatible with all >>>>>>>> the databases (directly for compatible types or database specific for >>>>>>>> non-compatible types)**** >>>>>>>> >>>>>>>> 2.) Remove precision from many columns which are not compatible >>>>>>>> across all those database servers**** >>>>>>>> >>>>>>>> 3.) Divide a larger changeset into smaller changeset so that they >>>>>>>> can be done commonly (by changing syntax) across multiple database >>>>>>>> servers. >>>>>>>> **** >>>>>>>> >>>>>>>> 4.) Due to move to liquibase 2.0, all checksums for changesets have >>>>>>>> been NULL'd and then changed to the new format. This should happen >>>>>>>> automatically, but if anyone depends on these checksums, then you >>>>>>>> should >>>>>>>> reply to this email :)**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> We may also have to change if anywhere non-ANSI syntax or >>>>>>>> MySQL-syntax has been used in the DAO. I haven't reached that far to >>>>>>>> tell >>>>>>>> how many such instances exist, but I hope there aren't many such places >>>>>>>> **** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> What do other developers think about these changes? Any suggestions >>>>>>>> on the way?? Anything that u think should be avoided or done?? These >>>>>>>> are >>>>>>>> fairly large changes and may result in incompatible checksums for >>>>>>>> already >>>>>>>> run changesets.**** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> PS: On a sidenote, I would like to highlight that for new >>>>>>>> installations it is silly that we are doing changesets based >>>>>>>> installations. >>>>>>>> While the world has moved to image based deployments for OS and >>>>>>>> large-software packages, we did the reverse and moved to changesets >>>>>>>> based >>>>>>>> installations. Upgrades are easier through changesets, but for new >>>>>>>> installations they are lengthy and boring. I would like to propose the >>>>>>>> for >>>>>>>> new installations, just an sqldump deploy is easy and fast, while >>>>>>>> keeping to >>>>>>>> changesets for upgrades.**** >>>>>>>> >>>>>>>> >>>>>>>> --- >>>>>>>> Regards, >>>>>>>> Saptarshi PURKAYASTHA >>>>>>>> >>>>>>>> My Tech Blog: http://sunnytalkstech.blogspot.com >>>>>>>> You Live by CHOICE, Not by CHANCE**** >>>>>>>> ------------------------------ >>>>>>>> >>>>>>>> Click here to >>>>>>>> unsubscribe<[email protected]?body=SIGNOFF%20openmrs-devel-l>from >>>>>>>> OpenMRS Developers' mailing list >>>>>>>> **** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> ** ** >>>>>>>> ------------------------------ >>>>>>>> >>>>>>>> Click here to >>>>>>>> unsubscribe<[email protected]?body=SIGNOFF%20openmrs-devel-l>from >>>>>>>> OpenMRS Developers' mailing list >>>>>>>> **** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> ** ** >>>>>>>> ------------------------------ >>>>>>>> >>>>>>>> Click here to >>>>>>>> unsubscribe<[email protected]?body=SIGNOFF%20openmrs-devel-l>from >>>>>>>> OpenMRS Developers' mailing list >>>>>>>> **** >>>>>>>> >>>>>>>> ** ** >>>>>>>> ------------------------------ >>>>>>>> >>>>>>>> Click here to >>>>>>>> unsubscribe<[email protected]?body=SIGNOFF%20openmrs-devel-l>from >>>>>>>> OpenMRS Developers' mailing list >>>>>>>> **** >>>>>>>> ------------------------------ >>>>>>>> >>>>>>>> Click here to >>>>>>>> unsubscribe<[email protected]?body=SIGNOFF%20openmrs-devel-l>from >>>>>>>> OpenMRS Developers' mailing list >>>>>>>> **** >>>>>>>> >>>>>>>> ** ** >>>>>>>> >>>>>>>> ** ** >>>>>>>> ------------------------------ >>>>>>>> >>>>>>>> Click here to >>>>>>>> unsubscribe<[email protected]?body=SIGNOFF%20openmrs-devel-l>from >>>>>>>> OpenMRS Developers' mailing list >>>>>>>> **** >>>>>>>> >>>>>>> >>>>>>> >>>>>> ------------------------------ >>>>>> Click here to >>>>>> unsubscribe<[email protected]?body=SIGNOFF%20openmrs-devel-l>from >>>>>> OpenMRS Developers' mailing list >>>>>> >>>>> >>>>> ------------------------------ >>>>> Click here to >>>>> unsubscribe<[email protected]?body=SIGNOFF%20openmrs-devel-l>from >>>>> OpenMRS Developers' mailing list >>>>> >>>> >>>> >>> ------------------------------ >>> Click here to >>> unsubscribe<[email protected]?body=SIGNOFF%20openmrs-devel-l>from >>> OpenMRS Developers' mailing list >>> >> >> > ------------------------------ > Click here to > unsubscribe<[email protected]?body=SIGNOFF%20openmrs-devel-l>from > OpenMRS Developers' mailing list > _________________________________________ To unsubscribe from OpenMRS Developers' mailing list, send an e-mail to [email protected] with "SIGNOFF openmrs-devel-l" in the body (not the subject) of your e-mail. [mailto:[email protected]?body=SIGNOFF%20openmrs-devel-l]
