On Thu, Dec 3, 2015 at 4:49 AM, R Smith <rsmith at rsweb.co.za> wrote:
> On 2015/12/03 3:04 AM, Scott Hess wrote: > >> I discourage this kind of usage because it means that in some distant >> future when someone has to make things work with a different database >> engine, they have to grind through and check every weirdo VARCHAR(73) and >> MEDIUMBIGINT declaration someone put in, because none of them have ever >> been tested with range enforcement enabled. So where someone meant >> "VARCHAR(256)" but actually typed "VARCHAR(6)", will your code suddenly >> start throwing clean errors which immediately suggest where to look, or >> will it just muddle through corrupting your data? There can certainly be >> subtle issues in any type, but my experience is that when you're trying to >> decode someone's code, it's easiest when the code says what is actually >> happening, not what it wishes were happening! >> > > I posit that a column declared as: > col VARCHAR(32) NOT NULL > says a whole lot more about what is actually happening than: > col TEXT NOT NULL > I'm saying that: col TEXT NOT NULL fairly describes what is actually happening, but: col VARCHAR(32) NOT NULL implies that things are happening which are not happening. CHAR is kind of happening, in that it is character data, VAR is not happening in that it can store more data than a 1-byte prefix can describe, and 32 is definitely not happening. And sure, I agree a mistyped word can be hard to decode by a future > programmer, but that is a statistical probability in any case. > For most engines if you type VRCHAR(32) you'll get an error. In SQLite you'll get a field with TEXT affinity. If you typed VRCHR(32) SQLite will give you a field with no affinity, which will work just fine for the most part. If you develop code with VARCHAR(6) on another database, you'll notice that your new code isn't storing your 60-character strings pretty quickly, but if it's three years later and you're porting, you might _not_ notice the problem unless you have a good test suite in place. [I'd _love_ something like SQLITE_ENABLE_PEDANTIC and "PRAGMA pedantic = true" to provide an extra layer of checking on these things. When you find that someone made a mistake in shipping code, you have to decide whether to risk fixing it, or just to leave it be, and I'd rather have stronger assertions about this kind of thing up front.] Of course, if you are _currently_ writing cross-platform code, absolutely >> write the cross-platform code! And I will agree that the above position >> has some issues when faced with things such as INT being 32 bits//.... >> etc. >> > > I *ALWAYS* write cross-platform code as far as SQL is concerned. I even > think in this day and age every programmer should, or is there a case for > the opposite? My experience has always been that cross-platform code written to run on a single platform turns out to not be very cross-platform at all. Changing TEXT to VARCHAR as appropriate is the kind of thing which is generally pretty easy to do, the hard parts will be the things that nobody even realized had platform issues, like INT storing 32 bits rather than 64 bits, or subtle differences in treatment of NULL values. -scott
