DB-Handy
view release on metacpan or search on metacpan
lib/DB/Handy.pm view on Meta::CPAN
keysize => ($cdef ? $cdef->{size} : 0),
coltype => ($cdef ? $cdef->{type} : 'VARCHAR'),
};
}
}
close FH;
$sch->{cols} = [ @cols ];
$sch->{indexes} = { %indexes };
$self->{_tables}{$table} = $sch;
return $sch;
}
sub _rewrite_schema {
my($self, $table, $sch) = @_;
my $sch_file = $self->_file($table, 'sch');
local *FH;
open(FH, "> $sch_file") or return $self->_err("Cannot rewrite schema: $!");
my $ok = print FH "VERSION=1\n";
$ok &&= print FH "RECSIZE=$sch->{recsize}\n";
for my $c (@{$sch->{cols}}) {
$ok &&= print FH "COL=$c->{name}:$c->{type}:$c->{size}:"
. (defined($c->{decl}) ? $c->{decl} : $c->{size}) . "\n";
}
for my $ix (values %{$sch->{indexes}}) {
$ok &&= print FH "IDX=$ix->{name}:$ix->{col}:$ix->{unique}\n";
}
for my $c (sort keys %{$sch->{notnull} || {}}) {
$ok &&= print FH "NOTNULL=$c\n";
}
for my $c (sort keys %{$sch->{defaults} || {}}) {
$ok &&= print FH "DEFAULT=$c:$sch->{defaults}{$c}\n";
}
for my $c (sort keys %{$sch->{checks} || {}}) {
$ok &&= print FH "CHECK=$c:$sch->{checks}{$c}\n";
}
$ok &&= print FH "PK=$sch->{pk}\n" if $sch->{pk};
my $err = $!;
unless ($ok && close(FH)) {
$err = $! if $ok;
close FH if $ok;
return $self->_err("Cannot rewrite schema: $err");
}
return 1;
}
# Does $v look like a number that Perl can use in arithmetic without
# complaining under -w? Leading and trailing whitespace is allowed, an
# optional sign, a decimal part and an exponent; everything else -- the
# empty string, 'abc', '12abc', '0x10', 'Inf', 'NaN' -- is not numeric.
#
# The same test used to be written out three times (index key encoding,
# type validation and record packing) and the FLOAT paths had no test at
# all, which is how "isn't numeric" warnings escaped from inside this
# module. Keep it in one place so the three callers cannot drift apart.
sub _looks_numeric {
my($v) = @_;
return 0 unless defined $v;
return $v =~ /^\s*[-+]?(?:\d+\.?\d*|\.\d+)(?:[eE][-+]?\d+)?\s*$/ ? 1 : 0;
}
# Is $v a well-formed calendar date in YYYY-MM-DD form?
sub _valid_date {
my($v) = @_;
return 0 unless $v =~ /^(\d\d\d\d)-(\d\d)-(\d\d)$/;
my($y, $m, $d) = ($1, $2, $3);
return 0 if ($m < 1) || ($m > 12);
return 0 if $d < 1;
my @mdays = (31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);
my $max = $mdays[$m-1];
if ($m == 2) {
$max = 29 if ((($y % 4) == 0) && (($y % 100) != 0)) || (($y % 400) == 0);
}
return 0 if $d > $max;
return 1;
}
# Return an error message when $v cannot be stored in column $col, or undef
# when it can. NULL (undef or the empty string) is always storable.
sub _type_error {
my($col, $v) = @_;
return undef unless defined($v) && ($v ne '');
my $cn = $col->{name};
if ($col->{type} eq 'INT') {
# A value that is not numeric at all keeps its historical
# behaviour and is stored as 0. Only a numeric value too large
# for the 4-byte field is rejected, because that used to be
# silently clamped to the nearest limit.
return undef unless _looks_numeric($v);
return undef if ($v >= INT_MIN) && ($v <= INT_MAX);
return "Integer out of range for column '$cn': '$v' "
. '(INT holds ' . INT_MIN . ' .. ' . INT_MAX . ')';
}
if ($col->{type} eq 'DATE') {
return undef if _valid_date($v);
return "Invalid DATE for column '$cn': '$v' (expected YYYY-MM-DD)";
}
return undef;
}
sub _pack_record {
my($self, $sch, $row) = @_;
my $data = RECORD_ACTIVE;
for my $col (@{$sch->{cols}}) {
my $v = defined($row->{$col->{name}}) ? $row->{$col->{name}} : '';
my $t = $col->{type};
my $s = $col->{size};
if ($t eq 'INT') {
# A value that is not numeric is stored as 0. It is tested
# here rather than left to int() so that -w stays quiet.
my $iv = _looks_numeric($v) ? int($v) : 0;
$iv = INT_MAX if $iv > INT_MAX;
$iv = INT_MIN if $iv < INT_MIN;
$data .= pack('N', $iv&0xFFFFFFFF);
}
elsif ($t eq 'FLOAT') {
# Same rule as INT, and for the same reason: a value that is
# not numeric is stored as 0 without a warning. Up to 1.08
# this branch handed the raw value to pack() and any string
# that was not a number leaked an "isn't numeric" warning out
# of the module, whatever the caller's warning settings were.
$data .= pack('d', _looks_numeric($v) ? $v+0 : 0);
lib/DB/Handy.pm view on Meta::CPAN
A sort key written as a plain number is the position of a column in the
SELECT list, counting from 1, as in SQL-92. With C<SELECT *> the
positions follow the C<CREATE TABLE> column order. A position outside
that range is an error rather than a sort that quietly does nothing.
An expression is never read as a position, so C<ORDER BY 1+1> still
sorts by the value of that expression.
The one place a position cannot be resolved is C<SELECT *> across a
JOIN, because the combined column list is not known when the sort key is
parsed; use a column name there.
=head1 DATA TYPES
=over 4
=item B<INT>
A 4-byte signed integer stored in big-endian binary form.
Range: -2,147,483,648 to 2,147,483,647.
Stored size on disk: 4 bytes.
A numeric value outside that range is B<rejected> by C<INSERT> and
C<UPDATE> with an "Integer out of range" error; earlier releases clamped
it silently to the nearest limit. A value with a fractional part is
truncated towards zero (C<1.7> is stored as C<1>), and a value that is
not numeric at all is stored as C<0>; neither is an error.
=item B<FLOAT>
An 8-byte IEEE 754 double. Stored size on disk: 8 bytes.
Index keys use an order-preserving encoding so that the binary sort
order of the index matches numeric order. The C<.dat> file itself holds
the machine's native double -- see L</"BUGS AND LIMITATIONS"> for what
that means when a data file is copied between machines.
A value that is not numeric at all is stored as C<0> and is not an
error, the same rule C<INT> follows. C<'abc'>, C<'12abc'>, C<'0x10'>,
C<'Inf'> and C<'NaN'> are all stored as C<0>; only a leading sign, digits,
a decimal point and an exponent are read as a number. Up to 1.08 this
column type had no such test and passed the value straight to C<pack>,
so a non-numeric value emitted an C<isn't numeric> warning from inside
DB::Handy -- twice per row when the column was indexed -- regardless of
the caller's warning settings.
=item B<CHAR(n)>
A fixed-length string of exactly C<n> bytes. Values shorter than C<n> are
NUL-padded on write; trailing NULs are stripped on read.
=item B<VARCHAR(n) / TEXT>
Stored as a fixed 255-byte field regardless of C<n>. Values are
NUL-padded on write; trailing NULs are stripped on read.
B<Note:> Unlike real databases, VARCHAR and TEXT always occupy 255 bytes
on disk; there is no variable-length storage.
=item B<DATE>
A 10-byte fixed string in C<YYYY-MM-DD> form. C<INSERT> and C<UPDATE>
B<reject> a value that is not a well-formed calendar date: the format
must be exactly four digits, a hyphen, two digits, a hyphen and two
digits, the month must be 01-12, and the day must exist in that month of
that year (C<2020-02-29> is accepted, C<2021-02-29> is not; the
four-hundred-year rule is applied, so C<2000-02-29> is accepted and
C<1900-02-29> is not). NULL and the empty string are always accepted.
No date arithmetic is performed. Comparisons are plain string
comparisons, which give the expected result because the format sorts
chronologically. Values written by 1.08 or earlier are not re-validated
on read, so an existing file may still hold something that C<INSERT>
would now refuse.
=back
=head1 CONSTRAINTS
The following column constraints are recognised in C<CREATE TABLE>:
=over 4
=item B<NOT NULL>
id INT NOT NULL
The column may not contain an empty or undefined value. Enforced on
both B<INSERT> and B<UPDATE>.
=item B<DEFAULT value>
salary INT DEFAULT 0
dept VARCHAR(20) DEFAULT 'unknown'
Applied when an INSERT omits the column or supplies an empty value.
=item B<UNIQUE>
CREATE TABLE emp (id INT, email VARCHAR(60) UNIQUE)
CREATE TABLE emp (id INT, email VARCHAR(60), UNIQUE (email))
CREATE UNIQUE INDEX emp_id ON emp (id)
Enforced at INSERT and UPDATE time. The column modifier and the
table-level constraint both create a unique index called
C<< <column>_unique >>; C<CREATE UNIQUE INDEX> names the index itself.
Multiple NULL (empty string) values are allowed, as SQL-92 requires.
=item B<PRIMARY KEY>
CREATE TABLE emp (id INT PRIMARY KEY, name VARCHAR(40))
CREATE TABLE emp (id INT, name VARCHAR(40), PRIMARY KEY (id))
Implies C<NOT NULL> and creates a unique index called
C<< <column>_pk >>, so duplicate keys are rejected on INSERT and on
UPDATE. A column that is both C<PRIMARY KEY> and C<UNIQUE> gets one
index, not two. The index is created with the table, so a table that
was created by DB::Handy 1.08 or earlier does not have one; add it with
C<CREATE UNIQUE INDEX> if the older table needs the constraint.
=item B<CHECK>
salary INT CHECK (salary >= 0)
lib/DB/Handy.pm view on Meta::CPAN
does not exist.
=item C<Cannot open base_dir: E<lt>reasonE<gt>>
The base directory passed to C<new> (or C<connect>) could not be opened.
Check that the path exists and that the process has read permission.
=item C<Cannot open dat 'E<lt>fileE<gt>': E<lt>reasonE<gt>>
A C<.dat> record file could not be opened for reading or writing.
Check file permissions and disk space.
=item C<Cannot read schema: E<lt>reasonE<gt>>
A C<.sch> schema file exists but could not be read.
Check file permissions.
=item C<Cannot create base_dir: E<lt>reasonE<gt>>
C<new> could not create the base directory.
Check parent-directory write permissions.
=item C<Cannot create database 'E<lt>nameE<gt>': E<lt>reasonE<gt>>
C<create_database> could not create the database subdirectory.
Check disk space and write permissions on C<base_dir>.
=item C<Cannot drop database 'E<lt>nameE<gt>': E<lt>reasonE<gt>>
C<drop_database> could not remove the database directory tree.
Check that no files are locked and that write permission is granted.
=item C<DB::Handy connect failed: E<lt>messageE<gt>>
The low-level C<connect> call failed. C<$DB::Handy::errstr> contains
the underlying error set by the failing operation.
=item C<DB::Handy: E<lt>messageE<gt>>
A fatal internal error was raised directly via C<die>.
C<RaiseError> must be enabled (the default) for this message to propagate.
=item C<AutoCommit cannot be turned off: DB::Handy has no transactions>
C<connect> was called with C<AutoCommit =E<gt> 0>. DB::Handy has no
transactions, so the request cannot be honoured and the connection is
refused rather than accepted with a promise it could not keep.
=item C<Value too long for column 'E<lt>colE<gt>': declared E<lt>typeE<gt>(E<lt>nE<gt>), got E<lt>mE<gt> bytes>
An C<INSERT> or C<UPDATE> supplied a value longer than the declared
C<CHAR> or C<VARCHAR> size. The value is rejected; it is never truncated.
=item C<Integer out of range for column 'E<lt>colE<gt>': 'E<lt>valueE<gt>' ...>
An C<INSERT> or C<UPDATE> supplied a value outside the range the declared
integer type can hold.
=item C<Invalid DATE for column 'E<lt>colE<gt>': 'E<lt>valueE<gt>' (expected YYYY-MM-DD)>
A C<DATE> column was given a value that is not an ISO-8601 calendar date.
=item C<CHECK constraint failed on column 'E<lt>colE<gt>'>
An C<INSERT> or C<UPDATE> supplied a value the column's C<CHECK>
expression rejected.
=item C<FULL OUTER JOIN is not supported ...>
=item C<NATURAL JOIN is not supported ...>
=item C<JOIN ... USING is not supported ...>
=item C<Unsupported JOIN condition 'ON E<lt>exprE<gt>' ...>
=item C<Unqualified column in the ON clause of JOIN E<lt>tableE<gt> ...>
=item C<Unsupported WHERE condition in a JOIN query: 'E<lt>partE<gt>' ...>
=item C<Unsupported select item 'E<lt>itemE<gt>' in a JOIN query ...>
=item C<Unknown ORDER BY column 'E<lt>colE<gt>' in a JOIN query>
The query used a JOIN construct the engine cannot execute. See
L</JOIN> for what a JOIN accepts. Each of these messages names the
offending text and says what to write instead. Before 1.09 these
constructs were accepted and quietly answered a different question, so
code that used to "work" may now report an error; see L</BUGS AND
LIMITATIONS>.
=item C<Each SELECT of a set operation must return the same number of columns (E<lt>nE<gt> and E<lt>mE<gt>)>
The branches of a C<UNION>, C<INTERSECT> or C<EXCEPT> disagree on how
many columns they return.
=back
=head1 BUGS AND LIMITATIONS
Please report any bugs or feature requests by e-mail to
E<lt>ina.cpan@gmail.comE<gt>.
When reporting a bug, please include:
=over 4
=item *
A minimal, self-contained test script that reproduces the problem.
=item *
The version of DB::Handy:
perl -MDB::Handy -e 'print DB::Handy->VERSION, "\n"'
=item *
Your Perl version:
perl -V
( run in 0.934 second using v1.01-cache-2.11-cpan-6fb7bf0f510 )