[interchange-cvs] interchange - heins modified 2 files
interchange-cvs at icdevgroup.org
interchange-cvs at icdevgroup.org
Wed Jul 30 01:09:00 EDT 2003
User: heins
Date: 2003-07-30 04:09:20 GMT
Modified: code/UI_Tag flex_select.coretag
Modified: dist/lib/UI/pages/admin db_metaconfig.html
Log:
* Enhance flex_select to accept a SQL query either in the opt hash or
from metadata. There are some shortcomings:
-- More list cannot be used
-- Limit can be used but as we said, no more list
Will change the column header/label value if a field is specified with
"field as 'Header value'".
* TODO: Handle count(*), max(*), etc. and GROUP BY. Maybe someday -- would be
a nice report display mechanism.
Revision Changes Path
1.2 +131 -7 interchange/code/UI_Tag/flex_select.coretag
rev 1.2, prev_rev 1.1
Index: flex_select.coretag
===================================================================
RCS file: /var/cvs/interchange/code/UI_Tag/flex_select.coretag,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- flex_select.coretag 24 Jul 2003 17:28:59 -0000 1.1
+++ flex_select.coretag 30 Jul 2003 04:09:20 -0000 1.2
@@ -29,6 +29,7 @@
$CGI->{$_} = $::Values->{$_};
}
}
+
if($CGI->{mv_return_table}) {
$CGI->{mv_data_table} = delete $CGI->{mv_return_table};
}
@@ -37,6 +38,24 @@
$::Scratch->{ui_class} = $CGI->{ui_class}
if $CGI->{ui_class} && $CGI->{ui_class} =~ /^\w+$/;
+ if($opt->{sql_query}) {
+ my $spec;
+ eval {
+ ($table) = Vend::Scan::sql_statement($opt->{sql_query}, { table_only => 1});
+ };
+ if($@) {
+ $Tag->error( {
+ set => errmsg(
+ "flex-select -- bad query %s: %s",
+ $opt->{sql_query},
+ $@,
+ ),
+ name => 'flex_select',
+ });
+ return undef;
+ }
+ }
+
if($table =~ s/\.(txt|asc)$/_$1/) {
$table =~ s:.*/::;
}
@@ -159,6 +178,39 @@
return flex_select_init($table, $opt);
}
+ my $spec;
+ my $stmt;
+ my $q;
+ if($opt->{sql_query}) {
+ $q = $opt->{sql_query};
+ if($CGI->{ui_sort_field} =~ s/^(\w+)(:[rfn]+)?$/$1/) {
+ my $field = $1;
+ my $opt = $2 || $CGI->{ui_sort_option};
+ $field .= ' DESC', $CGI->{ui_sort_option} = 'r' if $opt =~ /r/i;
+ $q =~ s/
+ \s+ORDER\s+BY
+ \s+(\w+(\s+desc\w*)?)
+ (\s*,\s*\w+(\s+desc\w*)?)*
+ (\s*$|\s+LIMIT\s+\d+(?:\s*,\s*\d+)?)
+ / ORDER BY $field$5/ix
+ or
+ $q =~ s/(\s+LIMIT\s+\d+(?:\s*,\s*\d+)?)/ ORDER BY $field$1/ix
+ or $q .= " ORDER BY $field";
+ }
+
+ eval {
+ ($spec) = Vend::Scan::sql_statement($q);
+ };
+ if($@ || ! $spec->{rt}) {
+ $Tag->error( {
+ set => errmsg("flex-select -- bad query %s: %s", $q, $@),
+ name => 'flex_select',
+ });
+ return undef;
+ }
+ $table = $spec->{rt}->[0];
+ }
+
my $ref = dbref($table)
or do {
my $msg = errmsg("%s: table '%s' does not exist", 'flex_select', $table);
@@ -168,12 +220,69 @@
};
my $ts = $Tmp->{flex_select}{$table} ||= {};
my $meta = $ts->{table_meta} ||= $Tag->meta_record($table, $CGI->{ui_meta_view});
-
+
+ if($meta->{sql_query}) {
+ $q = $meta->{sql_query};
+ if($CGI->{ui_sort_field} =~ s/^(\w+)(:[rfn]+)?$/$1/) {
+ my $field = $1;
+ my $opt = $2 || $CGI->{ui_sort_option};
+ $field .= ' DESC', $CGI->{ui_sort_option} = 'r' if $opt =~ /r/i;
+ $q =~ s/
+ \s+ORDER\s+BY
+ \s+(\w+(\s+desc\w*)?)
+ (\s*,\s*\w+(\s+desc\w*)?)*
+ (\s*$|\s+LIMIT\s+\d+(?:\s*,\s*\d+)?)
+ / ORDER BY $field$5/ix
+ or
+ $q =~ s/(\s+LIMIT\s+\d+(?:\s*,\s*\d+)?)/ ORDER BY $field$1/ix
+ or $q .= " ORDER BY $field";
+ }
+
+ eval {
+ ($spec) = Vend::Scan::sql_statement($q);
+ };
+ if($@ or ! $spec->{rt}) {
+ $Tag->error( {
+ set => errmsg("flex-select -- bad query %s: %s", $q, $@),
+ name => 'flex_select',
+ });
+ return undef;
+ }
+ $table = $spec->{rt}->[0];
+ }
+
if( $table ne $ref->config('name')) {
## Probably transient database
$CGI->{mv_data_table_real} = $table = $ref->config('name');
}
+ if($spec) {
+ if($spec->{rf} and $spec->{rf}[0] ne '*') {
+ my @c;
+ my $header;
+ for(my $i = 0; $i < @{$spec->{rf}}; $i++) {
+ if($spec->{hf}[$i]) {
+ $header++;
+ push @c, $spec->{rf}[$i] . '=' . $spec->{hf}[$i];
+ }
+ else {
+ push @c, $spec->{rf}[$i];
+ }
+ }
+ if($header) {
+ $CGI->{ui_show_fields} = join "\n", @c;
+ }
+ else {
+ $CGI->{ui_show_fields} = join " ", @c;
+ }
+ }
+ if($spec->{tf} and $spec->{tf}[0]) {
+ $CGI->{ui_sort_field} = join ",", @{$spec->{tf}};
+ $CGI->{ui_sort_option} = join ",", @{$spec->{to}};
+ }
+ $CGI->{ui_list_size} = $spec->{ml} if $spec->{ml};
+ }
+
$meta ||= {};
if($CGI->{ui_flex_key}) {
@@ -409,8 +518,10 @@
$CGI->{ui_description_fields} = $show;
}
- my @cols = grep $ref->column_exists($_),
- split /,/, $CGI->{ui_description_fields};
+ my @cols = split /,/, $CGI->{ui_description_fields};
+
+ @cols = grep $ref->column_exists($_), @cols
+ unless $spec;
my %limit_field;
@@ -436,7 +547,7 @@
}
my $fi = $CGI->{mv_data_table_real} || $CGI->{mv_data_table};
- $ts->{sparams} = $ts->{like_spec} ? '' : <<EOF;
+ $ts->{sparams} = ($ts->{like_spec} || $spec) ? '' : <<EOF;
fi=$fi
st=db
@@ -663,7 +774,7 @@
if($o = $m->{ui_sort_option}) {
my @m;
$msg = "sort by %s (%s)";
-#::logDebug("sort field=$col, meta sort_option=$o, ui_sort_option=$CGI->{ui_sort_option}");
+
if($CGI->{ui_sort_field} eq $col) {
if($CGI->{ui_sort_option} =~ /r/) {
$o =~ s/r//;
@@ -671,7 +782,6 @@
else {
$o .= "r";
}
-#::logDebug("sort field=$col, sort_option now=$o, sort_option=$CGI->{ui_sort_option}");
}
push @m, errmsg('reverse') if $o =~ /r/;
push @m, errmsg('case insensitive') if $o =~ /f/;
@@ -837,6 +947,18 @@
#::logDebug("search first_match=$search->{mv_first_match} length=$search->{mv_matchlimit}");
#::logDebug("Found search=" . ::uneval($search));
}
+ elsif($q) {
+ my $db = dbref($table);
+ my $o = {
+ ma => $CGI->{ui_more_alpha},
+ md => $CGI->{ui_more_decade},
+ ml => $CGI->{ui_list_size},
+ more => 1,
+ table => $fi,
+ query => $q,
+ };
+ $ary = $db->query($o);
+ }
else {
#::logDebug("In new search");
$params = escape_scan($ts->{sparams});
@@ -846,8 +968,10 @@
$ary = $search->{mv_results};
}
- finish_search($search);
+ finish_search($search) if $search;
+ $search ||= {};
+
if($CGI->{ui_return_to} and ! $CGI->{ui_return_stack}) {
$edit_extra .= $Tag->return_to('formlink');
}
2.18 +4 -0 interchange/dist/lib/UI/pages/admin/db_metaconfig.html
rev 2.18, prev_rev 2.17
Index: db_metaconfig.html
===================================================================
RCS file: /var/cvs/interchange/dist/lib/UI/pages/admin/db_metaconfig.html,v
retrieving revision 2.17
retrieving revision 2.18
diff -u -r2.17 -r2.18
--- db_metaconfig.html 24 Jul 2003 17:28:59 -0000 2.17
+++ db_metaconfig.html 30 Jul 2003 04:09:20 -0000 2.18
@@ -84,6 +84,7 @@
extended.ui_special_add
extended.ui_more_alpha
extended.ui_more_decade
+ extended.sql_query
fieldmeta
=Edit page
@@ -158,6 +159,7 @@
'extended.panel_style' => 'Panel CSS Style',
'extended.panel_shade' => 'Panel Lightest Shade',
'extended.tab_style' => 'Tab CSS Style',
+ 'extended.sql_query' => 'SQL query to select',
'extended.spread_fields' => 'Fields to edit',
'extended.spread_height' => 'Number of rows',
'extended.spread_meta' => 'Fields to meta display',
@@ -238,6 +240,7 @@
'extended.link_rows_blank' => 'default 1',
'extended.link_row_qual' => 'Default is key field, needs to be different field if auto-numbering. Must not be same as Foreign Key if in auto mode.',
'extended.link_blank_auto' => 'Requires link row qualifier be separate from primary key and foreign key in linked table.',
+ 'extended.sql_query' => 'Cannot use more-list with this. Should not use on large table.',
'extended.tab_horiz_offset' => 'Tab Horizontal Offset',
'extended.tab_vert_offset' => 'Tab Vertical Offset',
'extended.tab_width' => 'Default 100 pixels.',
@@ -269,6 +272,7 @@
'extended.left_width' => 'text_8',
'extended.panel_height' => 'text_5',
'extended.panel_width' => 'text_5',
+ 'extended.sql_query' => 'textarea_3_60',
'extended.spread_fields' => 'move_combo_8',
'extended.spread_height' => 'text_5',
'extended.spread_meta' => 'multiple',
@@ -374,4 +378,4 @@
]
@_UI_STD_FOOTER_@
-<!-- page: @@MV_PAGE@@ version: $Revision: 2.17 $ -->
+<!-- page: @@MV_PAGE@@ version: $Revision: 2.18 $ -->
More information about the interchange-cvs
mailing list