Re: [sqlite] SQLite allows RowID to be the name of a column
On May 27, 2008, at 12:50 PM, Nicolas Williams wrote: On Mon, May 26, 2008 at 11:20:27AM -0400, D. Richard Hipp wrote: SQLite already allows three different names for the rowid: rowid, oid, _rowid_. If all three names are taken, for example if the user has a table like this: CREATE TABLE badidea( rowid TEXT, oid TEXT, _rowid_ TEXT ); Then you cannot access the rowid. It just cannot be done. But how often does that happen really? If it does happen, then perhaps SQLiteSpy could pop up a dialog box saying that it cannot display the content of the table and explaining why not. I agree. BUT, if there's also an INTEGER PRIMARY KEY column, then there should be an API by which to find out what that column's name is for a given table. PRAGMA table_info(tablename); In the output of this pragma if there is only a single column with the pk set to 1 and if the type of that column is integer, then that column is your integer primary key. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
Your example doesn't counter my suggestion at all, which is to use the data only and not a special rowid. So you put 2 identical rows in a table. Since rows in a table are unordered, there isn't even an ordinal position to distinguish the 2 occurrences of that same row. Since they are identical, they are redundant, and so they are equivalent to just 1 such row. So updating both copies is perfectly fine. Though better yet is to not store a second copy in the first place. LOL English isn't my first language but I think you are joking ... If I want to make a table with a list of people (name and age) I can have two or more row with the same name and age and they aren't redundant and the implicit rowid is different. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
Federico Granata wrote: Your example doesn't counter my suggestion at all, which is to use the data only and not a special rowid. So you put 2 identical rows in a table. Since rows in a table are unordered, there isn't even an ordinal position to distinguish the 2 occurrences of that same row. Since they are identical, they are redundant, and so they are equivalent to just 1 such row. So updating both copies is perfectly fine. Though better yet is to not store a second copy in the first place. LOL English isn't my first language but I think you are joking ... If I want to make a table with a list of people (name and age) I can have two or more row with the same name and age and they aren't redundant and the implicit rowid is different. The normal proper way to do what you said is to declare a table like this: CREATE TABLE person ( person_id INT PRIMARY KEY, name TEXT, birthdate DATE ) In my example, you are using only the normal data, which is the 3 columns specified, and you are not referring to a column you didn't declare (rowid or whatever), but by a column you did declare, person_id. By contrast, defining a table like this is inferior: CREATE TABLE person ( name TEXT, birthdate DATE ) In that example, the rowid would be generated and you can't use something externally meaningful (such as SSN) to distinguish one Joe from another. My point still stands. Or my other point of adding a LIMIT clause to UPDATE also stands if you want to create tables the second way. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
The normal proper way to do what you said is to declare a table like this: CREATE TABLE person ( person_id INT PRIMARY KEY, name TEXT, birthdate DATE ) In my example, you are using only the normal data, which is the 3 columns specified, and you are not referring to a column you didn't declare (rowid or whatever), but by a column you did declare, person_id. By contrast, defining a table like this is inferior: CREATE TABLE person ( name TEXT, birthdate DATE ) In that example, the rowid would be generated and you can't use something externally meaningful (such as SSN) to distinguish one Joe from another. I can use the generated rowid to link this table to another containing meaningful data or I don't need other data besides the ones in this table. I can use an explicitly defined rowid or the automatically generated one ... My point still stands. Or my other point of adding a LIMIT clause to UPDATE also stands if you want to create tables the second way. you can use LIMIT in UPDATE only if data isn't meaningful (in this case you can habe only one row), otherwise ignoring the generated rowid would break the db ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
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? 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? 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. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
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
Re: [sqlite] SQLite allows RowID to be the name of a column
Bradley A. Town wrote: Ralf Junker wrote: This alerts me to a potential danger for SQLite managers which must rely on some means to retrieve THE RowID which uniquely identifies a record for in-grid table editing. If the RowID name can be hijacked by other columns and given another purpose, it poses the danger that wrong wrong columns are updated and data is corrupted. How can I access the RowID given the above table declaration? I know about the OID and _ROWID_ synonyms, but searching the documentation I find that they, too, can be used by other columns. I can therefore not see any non-ambiguous, reserved column name or API call to retrieve the implicit RowID value in such cases, especially if no primary key has been set like in the above schema. Any thoughts, especially from the SQLite developers? Thanks, Ralf Creating another thread for this to avoid thread hijacking. Thanks, I did not mean to hijack the thread. But thinking more about hijacking RowID I am glad this is now a separate thread. Lack of a reseverd RowID column name to guarantee unambiguous record operations by general SQLite tools is a potential thread to data security IMO. I would very much appreciate if this could be addressed in a future version of SQLite! Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
But thinking more about hijacking RowID I am glad this is now a separate thread. Lack of a reseverd RowID column name to guarantee unambiguous record operations by general SQLite tools is a potential thread to data security IMO. I would very much appreciate if this could be addressed in a future version of SQLite! have you seen here http://www.sqlite.org/autoinc.html ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
Ralf Junker wrote: But thinking more about hijacking RowID I am glad this is now a separate thread. Lack of a reseverd RowID column name to guarantee unambiguous record operations by general SQLite tools is a potential thread to data security IMO. I think the real problem here is that SQL allows you to have non-distinct rows in a table, when all rows should be distinct. Working within SQL's flaws, the solution here is for every table to have a unique constraint on one or more table columns. Then applications just use that to uniquely identify the row. Rows should be identifiable by user-visible data, not hidden data, since a database is supposed to model reality and people identify things based on their someway-visible attributes. On a separate note, it is best for one to be able to name a table or column et al anything one wants, with all the choice of names as you can store in a text column for user data. Reserved words aren't an issue as long as entity names are referred to with an unambiguously different syntax, such as quoted identifiers as SQL does support. Then database users don't have to worry about implementation details and can name tables and columns whatever they want; saying they can't name their column RowID is a leaky abstraction. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
Federico Granata wrote: have you seen here http://www.sqlite.org/autoinc.html ? Yes, I did. This documentation actually made me realize that the problem is not an implementation flaw but a design error, IMO. See my other answer in this thread for more rationale. Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
Darren Duncan wrote: I think the real problem here is that SQL allows you to have non-distinct rows in a table, when all rows should be distinct. SQLite's implicit RowID does not allow non-distinct values (except for NULL, but this is documented behavior and only maintained for backwards compatability. It might change in a future version. The sooner, the better, IMHO). Working within SQL's flaws, the solution here is for every table to have a unique constraint on one or more table columns. Then applications just use that to uniquely identify the row. This is exactly the concept of RowID. Nothing wrong with that. My only criticism is that this concept can be rendered non-functional by redefining the RowID so that it violates the uniqueness constraint. Example: CREATE TABLE x ( RowID TEXT); Now the implicit unique RowID is no longer accessible via the RowID column. Workarounds are _rowid_ or OID, but they can be overwritten as well: CREATE TABLE x ( RowID TEXT, _rowid_ text, oid text); For this table, it is no longer possible to access the implicit, unique RowID. General database applications (GUI managers, for example) can no longer (re-) identify a particular record! Rows should be identifiable by user-visible data, not hidden data, since a database is supposed to model reality and people identify things based on their someway-visible attributes. This is what INTEGER PRIMARY KEY is for: It works as a duplicate for the implicity RowID: CREATE TABLE x ( ID INTEGER PRIMARY KEY); For this table, the visible ID and the implicit RowID access the same unique data. This is the recommended usage and poses no problems. Problems only arise if RowID is re-defined differently as demonstrated above! On a separate note, it is best for one to be able to name a table or column et al anything one wants, with all the choice of names as you can store in a text column for user data. Reserved words aren't an issue as long as entity names are referred to with an unambiguously different syntax, such as quoted identifiers as SQL does support. Then database users don't have to worry about implementation details and can name tables and columns whatever they want; saying they can't name their column RowID is a leaky abstraction. Sure we all dislike restrictions. 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? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows RowID to be the name of a column
Ralf Junker wrote: On a separate note, it is best for one to be able to name a table or column et al anything one wants, with all the choice of names as you can store in a text column for user data. Reserved words aren't an issue as long as entity names are referred to with an unambiguously different syntax, such as quoted identifiers as SQL does support. Then database users don't have to worry about implementation details and can name tables and columns whatever they want; saying they can't name their column RowID is a leaky abstraction. Sure we all dislike restrictions. 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. 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. Any manager app can read the database schema first and generate a name foo that is distinct. -- Darren Duncan P.S. Alternately, you can eliminate RowID entirely as a user-visible concept, leaving it just to SQLite's internals. And also disallow storing duplicate rows, so that the values of the row fields themselves will uniquely identify a database record under any and all circumstances. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite allows RowID to be the name of a column
Ralf Junker wrote: My oversight, sorry for that. I never questioned that SQLite would reject reserved word column names, but I now see that this is not so. This alerts me to a potential danger for SQLite managers which must rely on some means to retrieve THE RowID which uniquely identifies a record for in-grid table editing. If the RowID name can be hijacked by other columns and given another purpose, it poses the danger that wrong wrong columns are updated and data is corrupted. How can I access the RowID given the above table declaration? I know about the OID and _ROWID_ synonyms, but searching the documentation I find that they, too, can be used by other columns. I can therefore not see any non-ambiguous, reserved column name or API call to retrieve the implicit RowID value in such cases, especially if no primary key has been set like in the above schema. Any thoughts, especially from the SQLite developers? Thanks, Ralf Creating another thread for this to avoid thread hijacking. Brad Town ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
