> This is doing things an OBJECT-ORIENTED way. You want to insert something > into a table. What do you want to insert? Variables? No: you want to > insert an OBJECT. So let there *BE* an object. Right. However I'm using the postgres bindings which as far as I can see they aren't as "objectified" as they could be (or I could be wrong), and I admit I feel quite some reluctance to start messing with them and spreading changes all over the place and ripping its guts apart. What I'm really suggesting is layering your own stuff *on top*.

> For example, suppose the column 'zip' in your data base is renamed from > the US-centric 'zip' to a more globally intelligible 'postcode'. ALL of > your versions break, but the Table1 class can just add two methods You don't go around renaming columns just because one is more intelligible to you than what you "inherited", unless you are very bored. I'm not suggesting that your Squeak code use a new name; I'm pointing out that the SQL code might use a new name and your Squeak code might need to adapt to that. Just because you've "inherited" it doesn't mean the underlying data base won't change; real data bases very often do. (The BIG problem is when the semantics of the data in the real world change but for reasons of inertia the data base schema is not changed.

> * It simply isn't *POSSIBLE* to use the same names in your Smalltalk code > as in your data base. It is, as long as you design your database and you dont have to access third party ones, and thats the only reason I'm comfortable with pulling tricks and "rules" out of my hat.

If you are in a situation where you have full control over the names in the SQL database schemas then fine, but in that case you aren't talking about a data base you've "inherited".

Even then, an SQL column name may reasonably have the form SchemaName . TableName . ColumnName and while you could easily represent that in Squeak as #(SchemaName TableName ColumnName) the result would not be a Squeak variable.

The rest of my reply revolves around this, so if you agree you'll probabily agree with the rest.. and if you don't you'll never agree with anything. > -> Home_Team character(14), All the columns have no underscores in them, on purpose, just to avoid this inconvenience. If we rule out quoted identifiers and identifiers in exotic national language character sets and just stick with basic identifiers, then underscores are not an inconvenience. SQL defines names to be equal if they differ only in case.

sql-to-squeak(name) = letter after underscore => capital other letter or digit => no change underscore => delete

squeak-to-sql(name) = capital letter => underscore, capital letter lower case letter => capital letter digit => no change

so Home_Team -> homeTeam -> HOME_TEAM. and HOME_TEAM is in fact the SQL normal form of the name Home_Team.

In fact, some such rule (and the presence of an underscore in the sql name) would be the only justification for having a capital letter in the squeak name.

Rather more exotic rules of this type _can_ handle quoted identifiers and accented letters, but the results tend not to be readable. If you have full and total control of the SQL names, this is not a problem.

> * The trick does not particularly encourage consistency. SQL names are > not case sensitive. So homeTeam, homeTEAM would both represent the > same SQL name. How exactly does the trick encourage people to consistently > pick one of them, in a way that gentle exhortation would not? Doesnt matter as the column names get lowercased when the postgres bindings return them. Or maybe its seaside.. they get lowercased somewhere. So you are NOT in full control of the names on the Squeak side? Any interface which simply lowercases SQL names is broken; are you sure it does not do something special with underscores?

In any case, it's not the trick that encourages people to use consistent capitalisation; it's some other interface you are building on that forces this.

> * Sometimes in SQL you have to use qualified names. The example data base > that my example comes from includes also > create table Teams (Team ...) > create table League (Team ...) > You have not yet explained how your pun can accomodate SQL names like > Teams.Team or League.Team. select team.team as teamteam That is no answer. It says how you can avoid the problem in some cases; it does not say how the problem can be solved.

> * Some names which are perfectly usable as SQL names are reserved words > in Smalltalk, such as "self", "super", "nil". That hardly counts as a valid objection. Why the **** not? Some names which are usable as SQL names are NOT usable in Smalltalk. PERIOD. This means that a simplistic identity/case mapping WILL NOT WORK IN ALL CASES. It's about as valid as an objection can get.

If you are in full and total control of the SQL names, then yes, you can avoid all the Smalltalk special names as well as the dozens of SQL special names. However, you did not previously inform us that you were in full and total control of the SQL names, and the objection remains an extremely serious objection to a simplistic identity/case name mapping IN THE GENERAL CASE where people want to connect to a data base that they are NOT in full control of.

The general case might reserve a prefix, such as "sQ", and map self to sQself super to sQsuper s_qfoo to sQsQfoo ... A wide range of name mapping rules can be used, but not simplistic identity mapping.

> * You have not yet explained how your pun can accomodate SQL delimited > identifiers. For people who never looked at SQL 92, "foo!" (with the > double quotes) is a perfectly good SQL 92 'delimited identifier'. > Delimited identifiers may be used pretty much anywhere that simple > identifiers may be. Who wants the shotgun? I have no idea what shotgun you are talking about. Anyone who wants to talk to an SQL 92 data base that they are NOT in full control of had better be ready to cope with any legal SQL 92.

Now that you inform us you ARE in the very very special case of building a Smalltalk binding to an SQL data base whose names you have full control of, that tells us that this isn't a problem *for you* and *right now*.

For other people, the fact that SQL 1992 allows some rather surprising identifiers means that crude name identity hacks will not *in general* work.

> * According to the SQL 1992 standard, an identifier may contain non-Latin > letters if such exist according to the <module character set specification> > or the <character set specification>; how are those to be handled by this > pun? And the shells? Again, I have no idea what you mean by "shells". However, accented letters are very much a part of SQL 1992 for very good reason. Only people who have full control of the SQL names do not need to worry about this.

> In this case, the two rows happen to need the same value for their > 'team' columns. But what if they needed DIFFERENT values for their > team columns? > > winners := 'Hawkeyes'. > losers := 'Windies'. > w := Wins new. > w team: winners; against: losers. > w insert. > l := Losses new. > l team: losers; against: winners. > l insert. > > I cannot for the life of me regard any approach which would force > the entry into Wins and the entry into Losses to use the *same* local > variables for opposite purposes as 'safe'. Ah, you saved the real one for the end :) It's a poor example tho (SQL wise), but nobody is forcing you:

In what way is it a poor example? All it is *supposed* to be an example of is inserting two rows that have some column names in common, and it's a simple clear example of that.

If you want (or need) to write the insert statement, do.

Hang on a minute, it's *you* who are writing explicit INSERT statements. Part of the point of my example is that I'm *not* writing INSERT statements; I'm constructing row objects and telling them to insert themselves. In the approach I am suggesting, no SQL syntax appears in the Smalltalk code at all. (Except in the underlying classes.)

Of course the statement was more "exposed" and therefore looked more natural (or less ugly) in my example than it would if it was next to your example.

I cannot understand this. You example DID have "exposed" SQL syntax, and that made it MORE ugly, not less. In fact, it's precisely the exposed SQL syntax that makes it so tricky to get right. Part of my point is that because you are interfacing to SQL code, SQL syntax has to appear *somewhere*, but it should be hidden away as deeply as possible and the great bulk of your code should look like Smalltalk.

Elementary statements: INSERT INTO table (name,...) VALUES (atom,...); => newRow := Table new. newRow name: atom. ... newRow insert.

UPDATE table WHERE cond SET name = value ... => Table updateWhere: [:aRow | cond] set: [:aRow | aRow name: value. ...] Table atName: atom updateWhere: [:aRow | cond] set: [...] "replace Name by any suitable column name"

DELETE FROM table WHERE cond => Table deleteWhere: [:aRow | cond] Table atName: atom deleteWhere: [:aRow | cond] "replace Name by any suitable column name".

SELECT gets a bit more complicated; I'd be inclined to make each SELECT be its own method, and it might make sense for it to be a method of the *result* table.

Perhaps any further discussion should be in terms of actual SELECTs from your application.