Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 54043
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: sewi [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: (no value)
Fixed in: 0.08117



Subject: read only columns (computed columns)
Download (untitled) / with headers
text/plain 1.1k
Sybase ASE supports "computed columns". They are like other columns, but - they don't have a datatype - they don't can't be nullable or non-nullable - they have an SQL expression which defines the value - they can be fixed (computed once) or dynamic (computed on each access) Two samples: - A fixed computed column with expression "GETDATE()" stores the creation date of the row - A special column is called "timestamp" - it holds a stamp (some kind of alltime never-reset commit-counter) of the row's last insert/update Here is a much more explanation: http://www.kodyaz.com/articles/sql- server-computed-column-calculated-column-sample.aspx DBIx::Class tries to insert rows as INSERT INTO table(col1,col2,col3) VALUES(DEFAULT,DEFAULT,DEFAULT) but computed columns must not be part of any INSERT or UPDATE command - the SQL server reports an error if you try. (They can be used as WHERE arguments to UPDATE, of cause.) add_columns should accept something like "read_only => 1" to include this column when fetching from the DB (SELECT) and might be included in UPDATE commands, if required, but must not be a written column in INSERT or UPDATE.
Download (untitled) / with headers
text/plain 1.9k
On Wed Jan 27 14:40:23 2010, SEWI wrote: Show quoted text
> Sybase ASE supports "computed columns". They are like other columns, but > - they don't have a datatype > - they don't can't be nullable or non-nullable > - they have an SQL expression which defines the value > - they can be fixed (computed once) or dynamic (computed on each > access) > > Two samples: > - A fixed computed column with expression "GETDATE()" stores the > creation date of the row > - A special column is called "timestamp" - it holds a stamp (some kind > of alltime never-reset commit-counter) of the row's last insert/update > > Here is a much more explanation: http://www.kodyaz.com/articles/sql- > server-computed-column-calculated-column-sample.aspx > > DBIx::Class tries to insert rows as INSERT INTO table(col1,col2,col3) > VALUES(DEFAULT,DEFAULT,DEFAULT) but computed columns must not be part > of any INSERT or UPDATE command - the SQL server reports an error if > you try. (They can be used as WHERE arguments to UPDATE, of cause.)
Depends on the column type. Some calculated ones are read-only, but some are very well user-updateable. It is the unconditional insertion of DEFAULT that needs to be tuned better. Do you by chance know what is the proper syntax to insert a row where all columns are to be set to their default values? Other databases accept INSERT INTO foo DEFAULT VALUES. Does Sybase have something similar without enumeration? Show quoted text
> add_columns should accept something like "read_only => 1" to include > this column when fetching from the DB (SELECT) and might be included in > UPDATE commands, if required, but must not be a written column in > INSERT or UPDATE.
No. Even if we are to make something similar it definitely can not be called read_only, as the term is very ambiguous. In the world of Moose and general perl enlightenment 'ro' means that an exception will be thrown on a write attempt, not that things will be silently dropped on the floor. In any case, if the default-insert is fixed, the need for such a flag goes away.
Download (untitled) / with headers
text/plain 2.7k
Am Mi 27. Jan 2010, 17:13:15, RIBASUSHI schrieb: Show quoted text
> On Wed Jan 27 14:40:23 2010, SEWI wrote:
[...) Show quoted text
> > DBIx::Class tries to insert rows as INSERT INTO table
(col1,col2,col3) Show quoted text
> > VALUES(DEFAULT,DEFAULT,DEFAULT) but computed columns must not be
part Show quoted text
> > of any INSERT or UPDATE command - the SQL server reports an error
if Show quoted text
> > you try. (They can be used as WHERE arguments to UPDATE, of cause.)
> > Depends on the column type. Some calculated ones are read-only, but
some Show quoted text
> are very well user-updateable. It is the unconditional insertion of
Everything I saw about this issue says that they aren't updateable by user - most of them don't even exist on disk. Trying to update a computed column - even a fixed one - results in "cannot directly insert into or update computed column" error. There might be rare situations when the server is in single-user-rescue mode where it's possible, but I don't think that DBIx::Class would be used in such a situation, you'ld use a sql client to repair your data (base). Show quoted text
> DEFAULT that needs to be tuned better. Do you by chance know what is
the Show quoted text
> proper syntax to insert a row where all columns are to be set to their > default values? Other databases accept INSERT INTO foo DEFAULT VALUES. > Does Sybase have something similar without enumeration? >
> > add_columns should accept something like "read_only => 1" to
include Show quoted text
> > this column when fetching from the DB (SELECT) and might be
included in Show quoted text
> > UPDATE commands, if required, but must not be a written column in > > INSERT or UPDATE.
> > No. Even if we are to make something similar it definitely can not be > called read_only, as the term is very ambiguous. In the world of Moose > and general perl enlightenment 'ro' means that an exception will be > thrown on a write attempt, not that things will be silently dropped on > the floor. In any case, if the default-insert is fixed, the need for > such a flag goes away.
I wrote "like", because it was a sample. Silently discarding the (maybe new) value might be wrong: The SQL server returns a hard error and doesn't process the other parts of the query. I don't know if other SQL servers do similar things, so everything above is only proofed on Sybase ASE. For creating a row with default values: INSERT INTO table(col1,col2,col3) VALUES(1,2,3) should be OK if col1 - 3 are not nullable and don't have any default. It doesn't matter if col4 is nullable and col5 is not_nullable but has a server-side default. I just did a test using this table: create table tmptest ( A int identity , B int DEFAULT 42 not null , C int null ) "INSERT INTO tmptest()" doesn't work, at least one column must be specified. I tried "INSERT INTO tmptest(C) VALUES(3)" and got a row with A=1 (identity/auto_increment), B=42 (default value) and C=3.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.