Ralf Junker wrote: > Darren Duncan wrote: > >> Ralf Junker wrote: >> >>> Can you suggest an alternative to a single reserved name to represent the >>> column which uniquely identifies a database record under any and all >>> circumstances? >> Yes, change the interface to RowID into a routine call rather than a column >> name; eg use "RowID()" rather than "RowID". > > I can not see how this would actually work with SQLite. Any use-created RowID column would override and hide the implicit rowid column even for the RowID() function, would it not?
No it wouldn't. You can still access SQLite's hidden RowID no matter what users name their columns. The thing is, since my proposal involves SQLite making syntax for accessing its hidden rowid using a function rather than as a fake column name, that function or the syntax for invoking it can be anything the SQLite developers pick that they know will be in a separate namespace from the one that table columns are in. >> Then when using it in a SELECT, you can say "RowID() as foo" in the select list where "foo" is different than a normal table field. Such is how 'standard' SQL does it. > > What is 'standard" SQL? Can you give an example how this is used with other DB engines? I am not familiar with MySQL, but searching the documentation I could not find that it supports this concept. Maybe others do? Actually, what I was meaning to get at here was the concept of a user's SQL statement using 'as' to rename the result of the special keyword for a rowid et al to some arbitrary other word to represent it as a column name, that didn't conflict with any column names the user chose for their tables. Various examples using (ANSI/ISO SQL:2003) standard SQL or other DBMS did things like this; the other reason for renaming is eg so that when joining 2 tables, the rowid from each table has a distinct column name. >> Any manager app can read the database schema first and generate a name "foo" that is distinct. > > As things are at the moment, the implicit, unambigous RowID can not be retrieved from the database schema if all three "RowID", "_rowid_", and "OId" column names are overridden. This applies to SQL as well as to user-defined functions. Then a candidate fix is for SQLite to use some namespace syntax for referring to those special things that is distinct from the namespaces of user-defined things. If it were me, I would have eg all system-defined operators named sys.foo, and all user-defined ones grouped under usr.foo, or that idea. And don't worry about whether or not doing this is compatible with other DBMSs or not, considering that the whole rowid/oid stuff is very non-portable and wildly implementation dependent anyway. If you want true portability, you do this by ignoring all these special ids and have explicit (unique) keys on your data columns, and then you use your actual data as its own unique identifier ... which is how the true relational model works anyway. Doing other than using the actual data to identify itself is just asking for trouble, and is a main reason why this whole rowid problem started. Doing it right saves trouble and gives portability. In fact, data identifying itself is the only approach I really advocate; any of my other suggestions which may try to use the special rowids are never my first choice, and I have less impetus to argue for them. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
