From mboxrd@z Thu Jan 1 00:00:00 1970 X-Spam-Checker-Version: SpamAssassin 3.4.4 (2020-01-24) on polar.synack.me X-Spam-Level: X-Spam-Status: No, score=-1.9 required=5.0 tests=BAYES_00 autolearn=ham autolearn_force=no version=3.4.4 X-Google-Thread: 103376,bf02c238a92156a3 X-Google-Attributes: gid103376,public X-Google-Language: ENGLISH,ASCII-7-bit Path: g2news1.google.com!news4.google.com!news.glorb.com!border1.nntp.dca.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!feed.cgocable.net!read1.cgocable.net.POSTED!53ab2750!not-for-mail From: "Warren W. Gay VE3WWG" User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.lang.ada Subject: Re: Windows Ada database support. References: <5e3e03a7.0411240431.7e037a4e@posting.google.com> <2004112420030750073%david@bottoncom> <11w2chxxtggn9.a442ecwtujd2$.dlg@40tude.net> In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Message-ID: Date: Tue, 07 Dec 2004 00:16:21 -0500 NNTP-Posting-Host: 24.150.168.167 X-Complaints-To: abuse@cogeco.ca X-Trace: read1.cgocable.net 1102396520 24.150.168.167 (Tue, 07 Dec 2004 00:15:20 EST) NNTP-Posting-Date: Tue, 07 Dec 2004 00:15:20 EST Organization: Cogeco Cable Xref: g2news1.google.com comp.lang.ada:6815 Date: 2004-12-07T00:16:21-05:00 List-Id: Dmitry A. Kazakov wrote: > On Mon, 06 Dec 2004 12:52:40 -0500, Warren W. Gay VE3WWG wrote: >>that people out there use. Some database products (PostgreSQL >>for example) promote the use of Object IDs (basically Row IDs). >>Still others don't support the concept at all (MySQL). A grey >>area exists in a 3rd area (like Informix), which allows you to >>use them at your own peril (Row IDs are unique, if you keep >>within certain database design restrictions). > > I think that this is a shortcoming of the very concept of a relational > view. Relation is not an array. Period. So IF it should be > Ada.Database.Relational, then there will be no such think as row ID. [ But > it is a good sign. Database vendors start to conceive shortcomings of the > existing models. This will erode SQL dominance. ] Well, database theory experts rage wars over issues like this in other newsgroups. All I would personally like to see on a practical level (theory aside) is AGREEMENT and CONSISTENCY. I could live with or without row IDs, as long as all vendors did so uniformly. As soon as one supports the idea, then any Ada package that binds to it must provide access to it (existing database schemas are not going to change to suit the Ada package writer). OTOH, I have no problem WITH row ids, as long as they are unique. The whole row ID problem AFAIK, only really started to be a problem when vendors started permitting other disk configurations that caused the row ids to be "parallel" in some way, and thus non-unique. > Also we should distinguish two cases: > > A. Some Ada application stores its data in a database. The way the data are > organized there is free. Its not clear to me what you mean by "free" here. > B. Accessing existing (but unknown at design time) data base. The data > structure is fixed and the application must adapt to it. I think I know the two points you are raising, but it isn't clear which is A or B. > A is much easier than B. Not everybody needs B. Further B is often bound to > some concrete data base, in which case data base specific bindings make > much sense. So we could ignore B for a while. At least until it will be > clear how to provide at least A. If in one case you mean a package capable of being used in a GUI tool, where it can connect to the database and discover tables, keys, indexes, views, triggers and the like, and allow dynamic operations without any foreknowledge, then agreed, this is much more difficult. This is even more of a nightmare approach wise, because the standard(s) never addressed this need - hence every vendor implements this functionality they way they see fit. While the above is import (for tools), application needs are usually much simpler. Written to do fixed operations, on a fixed set of tables, views and stored procedures. But as I've pointed out, even this simpler case is complex in a multi-vendor world. >>Related to this same issue is how to identify rows that lack >>a natural primary key. Some databases support an identity >>type for the purpose (Sybase), while others use sequences. >>Still others like MySQL use some weird idea of an auto >>increment integer field (I am too lazy to look up the >>specifics for this, but this is documented in the APQ >>manual). >> >>Here's another good one: Some databases allow you to declare >>a VARCHAR(256). Others are restricted to VARCHAR(255), and you >>must switch to a different type (TEXT I think), if you need >>longer fields. >> >>Some support boolean types, and others do not. Some support >>arrays, others do not. If they both support arrays, they >>are guaranteed to work with different rules and syntax. >> >>There seems to be virtually no agreement on how blobs are >>handled and managed, between the different products. > > This is the case B. Blobs? >I think that it could still be possible to solve it in > an OO way. I beg to differ on this one, though I've not tried very hard on this one ;-) Consider some of the challenges: 1) PostgreSQL uses an API that opens/creates etc. and returns an OID. 2) PostgreSQL blobs are referenced by saving a OID in a column of a row. 3) ALL PostgreSQL blob operations must occur within the confines of a transaction (otherwise the operation fails!) 4) MySQL (IIRC), wants you to put the entire blob into a row. 5) MySQL, IIRC, wants you to perform the blob I/O in one operation and IIRC, doesn't care about transactions (optional). 6) PostgreSQL blobs can be operated on like files, with seeks, partial writes, reads etc. I seem to recall there were more problems, but when I started with this list, I decided to leave it for a rainy day!!! ;-) > All database types should be derived from one base. Factory can > be used to create values "like in the column". Differences between VARCHAR, > TEXT, LONGCHAR etc are uninteresting for the application. You might expect so, but the Boolean case and Dates have created a lot of problem. Since you have to work with SQL, how do you satisfy databases that want 0 and 1 for Booleans (or bit?), and the more nomal ones that that True and False? I sheltered the application to some degree from this in APQ, but allowing the fields to be encoded for you (APQ knows in the MySQL case it wants 0 and 1 - I think it was MySQL). But this problem still pokes out in places like hardcoded WHERE clauses : ... WHERE MARRIED = False and ... or WHERE MARRIED = 0 and ... So if MARRIED is BOOLEAN, I think you have to test if you are using MySQL (I think it was them), and then use the 0 and 1 instead (or use APQ to encode a hardcoded False!) Dates, Timestamps and timezones get even more interesting. Versions of databases add to the problems! MySQL in one version formats the dates differently than later versions -- ugh! > In general I do not think that primitive data types are the greatest > problem. The problem is that the semantics of "what and how" slips away. It > is too low level. I think you would be surprised! Just sticking to "normal" primitive data types in APQ, has had me see enough horrors ;-) >>The list of incompatibilities and differences are many >>more. For example, there are differences in the way the >>client libraries work (ability to fetch one row at a time, >>randomly or not (PostgreSQL), must fetch them all into >>client memory for random access, or use one-at-a-time >>sequential access (MySQL), etc.) >> >>The challenges for a "unified Ada access" layer are so >>numerous, that I consider it unachievable. I took a stab >>at providing a "portable" binding in APQ, but had to >>make various compromises along the way (these are documented >>in the manual). > > I think that APQ could become an alternative to ODBC. ODBC tries to swallow > documents and spreadsheets (like Excel), things that are too far from a > "normal" data base. Well, in fairness to ODBC, some sort of generalized interface was called for, and it was "a solution" of sorts. But I never felt it was meant to be used by application programmers. IMHO, any API that has application programmers coding a whole whack of complex API calls to just do a select and return a row (for example), is not "the solution". Application programmers want to focus on the application problem - not API details (most mediochre programmers can't get it right anyway). Once coded, it becomes so complex, that no one wants to make major changes to it. Then comes the patchwork! Many people feel that code generators are the answer. I disagree there also. That is a one-time fix. Eventually, someone has to go back and change it. At this point it is unreadable and near unmaintainable. APQ tries to make it simple for the application programmer. I would much rather be thinking in SQL and Ada terms, than trying trying to remember API calls. APQ's API tries to be self intuitive, and time will tell if it is or not. This has been an interesting diversion ;-) -- Warren W. Gay VE3WWG http://home.cogeco.ca/~ve3wwg