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