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