It seems that the DBI package changes column names that have dots in them replacing the dots with underscores. Is this really necessary? I understand that dot is an SQL operator; nevertheless, SQLite can handle column names with dots and I suspect other databases can too: C:\tmp2>sqlite3 SQLite version 3.4.0 Enter ".help" for instructions sqlite> create table mytable (`my.field` integer); sqlite> insert into mytable(`my.field`) values (1); sqlite> select `my.field` from mytable; 1
DBI column names
7 messages · Gabor Grothendieck, Sean Davis, Tim Keitt +1 more
Gabor Grothendieck wrote:
It seems that the DBI package changes column names that have dots in them replacing the dots with underscores. Is this really necessary? I understand that dot is an SQL operator; nevertheless, SQLite can handle column names with dots and I suspect other databases can too: C:\tmp2>sqlite3 SQLite version 3.4.0 Enter ".help" for instructions sqlite> create table mytable (`my.field` integer); sqlite> insert into mytable(`my.field`) values (1); sqlite> select `my.field` from mytable; 1
Just fyi....
Postgres (works):
test5=# create temp table mytable ("my.field" integer);
LOG: statement: create temp table mytable ("my.field" integer);
CREATE TABLE
test5=# insert into mytable ("my.field") values (1);
INSERT 0 1
test5=# select "my.field" from mytable;
my.field
----------
1
(1 row)
And mysql (works, also):
mysql> create temporary table mytable (`my.field` integer);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into mytable(`my.field`) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select `my.field` from mytable;
+----------+
| my.field |
+----------+
Sean
| 1 |
+----------+
1 row in set (0.00 sec)
This may be a bit of a hang-over from some of my early work on rpgsql, et al. I had never used SQL (nor really programmed in R) prior to writing the packages and wasn't too savy about quoting options. I remember writing code to swap dots and underscores. The swapping certainly could be relaxed as properly quoted, these characters should not cause problems in R or SQL. THK
On 7/21/07, Sean Davis <sdavis2 at mail.nih.gov> wrote:
Gabor Grothendieck wrote:
It seems that the DBI package changes column names that have dots in them replacing the dots with underscores. Is this really necessary? I understand that dot is an SQL operator; nevertheless, SQLite can handle column names with dots and I suspect other databases can too: C:\tmp2>sqlite3 SQLite version 3.4.0 Enter ".help" for instructions sqlite> create table mytable (`my.field` integer); sqlite> insert into mytable(`my.field`) values (1); sqlite> select `my.field` from mytable; 1
Just fyi....
Postgres (works):
test5=# create temp table mytable ("my.field" integer);
LOG: statement: create temp table mytable ("my.field" integer);
CREATE TABLE
test5=# insert into mytable ("my.field") values (1);
INSERT 0 1
test5=# select "my.field" from mytable;
my.field
----------
1
(1 row)
And mysql (works, also):
mysql> create temporary table mytable (`my.field` integer);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into mytable(`my.field`) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select `my.field` from mytable;
+----------+
| my.field |
+----------+
Sean
| 1 |
+----------+
1 row in set (0.00 sec)
_______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB at stat.math.ethz.ch https://stat.ethz.ch/mailman/listinfo/r-sig-db
Timothy H. Keitt, University of Texas at Austin Contact info and schedule at http://www.keittlab.org/tkeitt/ Reprints at http://www.keittlab.org/tkeitt/papers/ ODF attachment? See http://www.openoffice.org/
1 day later
Gabor Grothendieck wrote:
It seems that the DBI package changes column names that have dots in them replacing the dots with underscores. Is this really necessary?
The DBI package provides a generic make.db.names and a deafult method. In RSQLite, I believe this is only called when tables are created using dbWriteTable. So it isn't as if DBI (or RSQLite) is preventing you from using (strange) column names in your DBs. -- Just making it a bit difficult ;-) The problem with changing this function is that anyone relying on (coding around) this feature is going to get bitten. Maybe we want an additional argument to the dbWriteTable method? + seth
Seth Falcon | Computational Biology | Fred Hutchinson Cancer Research Center http://bioconductor.org
Yes. One idea for the name for the argument might be check.names= . This is the same argument name used in read.table. The value could be TRUE, FALSE or a custom function. TRUE would use make.db.names and FALSE would use the identity function. The default would be TRUE for compatibility with the existing function. Another possibility is associating the value with the driver or the connection; however, that may seem inconsistent to users if dbWriteTable uses it and other functions do not.
On 7/23/07, Seth Falcon <sfalcon at fhcrc.org> wrote:
Gabor Grothendieck wrote:
It seems that the DBI package changes column names that have dots in them replacing the dots with underscores. Is this really necessary?
The DBI package provides a generic make.db.names and a deafult method. In RSQLite, I believe this is only called when tables are created using dbWriteTable. So it isn't as if DBI (or RSQLite) is preventing you from using (strange) column names in your DBs. -- Just making it a bit difficult ;-) The problem with changing this function is that anyone relying on (coding around) this feature is going to get bitten. Maybe we want an additional argument to the dbWriteTable method? + seth -- Seth Falcon | Computational Biology | Fred Hutchinson Cancer Research Center http://bioconductor.org
"Gabor Grothendieck" <ggrothendieck at gmail.com> writes:
Yes. One idea for the name for the argument might be check.names= . This is the same argument name used in read.table. The value could be TRUE, FALSE or a custom function. TRUE would use make.db.names and FALSE would use the identity function. The default would be TRUE for compatibility with the existing function.
I'm not opposed to this suggestion, but would like to hear from other users that this is something that is desired. If this is important to you (any of you), then a patch with test cases added to the unit tests would make it happen much more quickly.
Another possibility is associating the value with the driver or the connection; however, that may seem inconsistent to users if dbWriteTable uses it and other functions do not.
I don't think that is a good idea. + seth
Seth Falcon | Computational Biology | Fred Hutchinson Cancer Research Center http://bioconductor.org
On 7/24/07, Seth Falcon <sfalcon at fhcrc.org> wrote:
"Gabor Grothendieck" <ggrothendieck at gmail.com> writes:
Yes. One idea for the name for the argument might be check.names= . This is the same argument name used in read.table. The value could be TRUE, FALSE or a custom function. TRUE would use make.db.names and FALSE would use the identity function. The default would be TRUE for compatibility with the existing function.
I'm not opposed to this suggestion, but would like to hear from other users that this is something that is desired. If this is important to you (any of you), then a patch with test cases added to the unit tests would make it happen much more quickly.
Another possibility is associating the value with the driver or the connection; however, that may seem inconsistent to users if dbWriteTable uses it and other functions do not.
I don't think that is a good idea. + seth -- Seth Falcon | Computational Biology | Fred Hutchinson Cancer Research Center http://bioconductor.org
Its probably not hugely important so feel free to ignore this if you want but it is noticeable when performing SQL statements on the canonical example, iris. See the examples here to see what I mean and note how we had to refer to Sepal_Length rather than Sepal.Length: http://code.google.com/p/sqldf/