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