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 >> > > _________________________________________ 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]
