Hi,
Please find attached patch to represent array data in datagrid with curly
braces and
also allow user to save array with values like <null>, <empty string>, '
*""*' and "*''*"
--
*Harshal Dhumal*
*Sr. Software Engineer*
EnterpriseDB India: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/web/pgadmin/feature_tests/test_data.json b/web/pgadmin/feature_tests/test_data.json
index a2457c2..ac00b0b 100644
--- a/web/pgadmin/feature_tests/test_data.json
+++ b/web/pgadmin/feature_tests/test_data.json
@@ -7,12 +7,19 @@
"4": ["", "Hello World", "text"],
"5": ["\"\"", "", "text", "Two double quotes"],
"6": ["\\\"\\\"", "\"\"", "text", "double backslash followed by a double quote"],
- "7": ["\\\\\"\\\\\"", "\\\"\\\"", "text", "double backslash followed by a double quote"],
+ "7": ["\\\\\"\\\\\"", "\\\\\"\\\\\"", "text", "double backslash followed by a double quote"],
"8": ["", "[null]", "text"],
"9": ["", "[51,52]", "json"],
"10": ["[61,62]", "[61,62]", "json"],
"11": ["", "true", "bool"],
- "12": ["", "[null]", "bool"]
+ "12": ["", "[null]", "bool"],
+ "13": ["", "[null]", "text[]"],
+ "14": ["{}", "{}", "text[]"],
+ "15": ["{data,,'',\"\",\\'\\',\\\"\\\"}", "{data,[null],,,'',\"\"}", "text[]"],
+ "16": ["{}", "{}", "int[]"],
+ "17": ["{123,,456}", "{123,[null],456}", "int[]"],
+ "18": ["", "[null]", "boolean[]"],
+ "19": ["{false,,true}", "{false,[null],true}", "boolean[]"]
}
}
-}
\ No newline at end of file
+}
diff --git a/web/pgadmin/feature_tests/view_data_dml_queries.py b/web/pgadmin/feature_tests/view_data_dml_queries.py
index 0b2da0c..153d796 100644
--- a/web/pgadmin/feature_tests/view_data_dml_queries.py
+++ b/web/pgadmin/feature_tests/view_data_dml_queries.py
@@ -67,6 +67,13 @@ CREATE TABLE public.defaults
json_null json,
boolean_defaults boolean DEFAULT true,
boolean_null boolean,
+ text_arr text[],
+ text_arr_empty text[],
+ text_arr_null text[],
+ int_arr integer[],
+ int_arr_empty integer[],
+ boolean_arr boolean[],
+ boolean_arr_null boolean[],
CONSTRAINT defaults_pkey PRIMARY KEY (id)
)
"""
@@ -169,17 +176,16 @@ CREATE TABLE public.defaults
ActionChains(self.driver).move_to_element(cell_el).double_click(
cell_el
).perform()
-
cell_type = data[2]
value = data[0]
- if cell_type == 'int':
+ if cell_type in ['int', 'int[]']:
if value == 'clear':
cell_el.find_element_by_css_selector('input').clear()
else:
ActionChains(self.driver).send_keys(value).perform()
- elif cell_type in ['text', 'json']:
+ elif cell_type in ['text', 'json', 'text[]', 'boolean[]']:
self.page.find_by_xpath(
"//*[contains(@class, 'pg_textarea')]").click()
ActionChains(self.driver).send_keys(value).perform()
@@ -231,7 +237,6 @@ CREATE TABLE public.defaults
self.page.find_by_xpath(row0_cell0_xpath).click()
self.page.find_by_xpath("//*[@id='btn-copy-row']").click()
self.page.find_by_xpath("//*[@id='btn-paste-row']").click()
-
# Update primary key of copied cell
self._update_cell(row1_cell1_xpath, [2, "", "int"])
self.page.find_by_xpath(
@@ -261,13 +266,12 @@ CREATE TABLE public.defaults
self._verify_row_data(False)
def _add_row(self):
- for idx in range(1, len(config_data.keys())):
+ for idx in range(1, len(config_data.keys()) + 1):
cell_xpath = CheckForViewDataTest._get_cell_xpath(
'r'+str(idx), 1
)
time.sleep(0.2)
self._update_cell(cell_xpath, config_data[str(idx)])
-
self.page.find_by_id("btn-save").click() # Save data
# There should be some delay after save button is clicked, as it
# takes some time to complete save ajax call otherwise discard unsaved
@@ -290,12 +294,9 @@ CREATE TABLE public.defaults
# List of row values in an array
cells = [el.text for el in result_row.find_elements_by_tag_name('div')]
-
- for idx in range(1, len(config_data.keys())):
+ for idx in range(1, len(config_data.keys()) + 1):
# after copy & paste row, the first cell of row 1 and
# row 2(being primary keys) won't match
# see if cell values matched to actual value
- if idx != 1 and not is_new_row:
- self.assertEquals(cells[idx], config_data[str(idx)][1])
- elif is_new_row:
+ if (idx != 1 and not is_new_row) or is_new_row:
self.assertEquals(cells[idx], config_data[str(idx)][1])
diff --git a/web/pgadmin/static/js/slickgrid/editors.js b/web/pgadmin/static/js/slickgrid/editors.js
index f3a28dd..80f7cce 100644
--- a/web/pgadmin/static/js/slickgrid/editors.js
+++ b/web/pgadmin/static/js/slickgrid/editors.js
@@ -48,6 +48,10 @@
return $buttons;
}
+ function is_valid_array(val) {
+ val = $.trim(val)
+ return !(val != "" && (val.charAt(0) != '{' || val.charAt(val.length - 1) != '}'));
+ }
/*
* This function handles the [default] and [null] values for cells
* if row is copied, otherwise returns the editor value.
@@ -189,15 +193,41 @@
this.loadValue = function (item) {
var col = args.column;
- if (_.isUndefined(item[args.column.field]) && col.has_default_val) {
- $input.val(defaultValue = "");
- }
- else if (item[args.column.field] === "") {
- $input.val(defaultValue = "''");
+ if (_.isUndefined(item[args.column.field]) || _.isNull(item[args.column.field])) {
+ $input.val(defaultValue = "");
+ return;
}
- else {
- $input.val(defaultValue = item[args.column.field]);
- $input.select();
+
+ if (!args.column.is_array) {
+ if (item[args.column.field] === "") {
+ $input.val(defaultValue = "''");
+ } else if (item[args.column.field] === "''") {
+ $input.val(defaultValue = "\\'\\'");
+ } else if (item[args.column.field] === '""') {
+ $input.val(defaultValue = '\\"\\"');
+ } else {
+ $input.val(defaultValue = item[args.column.field]);
+ $input.select();
+ }
+ } else {
+ var data = [];
+ for (var k in item[args.column.field]) {
+ if (_.isUndefined(item[args.column.field][k]) || _.isNull(item[args.column.field][k])) {
+ data.push('');
+ } else if (item[args.column.field][k] === "") {
+ data.push("''");
+ } else if (item[args.column.field][k] === "''") {
+ data.push("\\'\\'");
+ } else if (item[args.column.field][k] === '""') {
+ data.push('\\"\\"');
+ } else {
+ data.push(item[args.column.field][k]);
+ $input.select();
+ }
+ }
+ defaultValue = data;
+ $input.val('{' + data.join() +'}');
+
}
};
@@ -207,18 +237,43 @@
if (value === "") {
return null;
}
- // single/double quotes represent an empty string
- // If found return ''
- else if (value === "''" || value === '""') {
- return '';
- }
- else {
- // If found string literals - \"\", \'\', \\'\\' and \\\\'\\\\'
- // then remove slashes.
- value = value.replace("\\'\\'", "''");
- value = value.replace('\\"\\"', '""');
- value = value = value.replace(/\\\\/g, '\\');
- return value;
+
+ if (!args.column.is_array) {
+ if (value === "''" || value === '""') {
+ return '';
+ } else if (value === "\\'\\'") {
+ return "''";
+ } else if (value === '\\"\\"') {
+ return '""';
+ } else {
+ return value;
+ }
+ } else {
+
+ // Remove leading { and trailing }.
+ // Also remove leading and trailing whitespaces.
+ var value = $.trim(value.slice(1, -1));
+
+ if(value == '') {
+ return [];
+ }
+
+ var data = [];
+ value = value.split(',');
+ for (var k in value) {
+ if (value[k] == "") {
+ data.push(null); //empty string from editor is null value.
+ } else if (value[k] === "''" || value[k] === '""') {
+ data.push(''); // double quote from editor is blank string;
+ } else if (value[k] === "\\'\\'") {
+ data.push("''");
+ } else if (value[k] === '\\"\\"') {
+ data.push('""');
+ } else {
+ data.push(value[k]);
+ }
+ }
+ return data;
}
};
@@ -233,7 +288,7 @@
return false;
} else {
return (!($input.val() == "" && _.isNull(defaultValue))) &&
- ($input.val() != defaultValue);
+ ($input.val() !== defaultValue);
}
};
@@ -245,6 +300,13 @@
}
}
+ if (args.column.is_array && !is_valid_array($input.val())) {
+ return {
+ valid: false,
+ msg: "Array must start with '{' and end with '}'"
+ };
+ }
+
return {
valid: true,
msg: null
@@ -859,7 +921,13 @@
this.loadValue = function (item) {
defaultValue = item[args.column.field];
- $input.val(defaultValue);
+
+ if(args.column.is_array && !_.isNull(defaultValue) && !_.isUndefined(defaultValue)) {
+ $input.val('{' + defaultValue.join() +'}');
+ } else {
+ $input.val(defaultValue);
+ }
+
$input[0].defaultValue = defaultValue;
$input.select();
};
@@ -868,6 +936,24 @@
if ($input.val() === "") {
return null;
}
+
+ if(args.column.is_array) {
+ // Remove leading { and trailing }.
+ // Also remove leading and trailing whitespaces.
+ var val = $.trim($input.val().slice(1, -1));
+
+ if(val == '') {
+ return [];
+ }
+ val = val.split(',');
+ for (var k in val) {
+ if (val[k] == "") {
+ val[k] = null; //empty string from editor is null value.
+ }
+ }
+ return val;
+ }
+
return $input.val();
};
@@ -887,20 +973,47 @@
};
this.validate = function () {
- if (isNaN($input.val())) {
+ var value = $input.val();
+ if (!args.column.is_array && isNaN(value)) {
return {
valid: false,
- msg: "Please enter a valid integer"
+ msg: "Please enter a valid number"
};
}
-
if (args.column.validator) {
- var validationResults = args.column.validator($input.val());
+ var validationResults = args.column.validator(value);
if (!validationResults.valid) {
return validationResults;
}
}
+ if (args.column.is_array) {
+ if (!is_valid_array(value)) {
+ return {
+ valid: false,
+ msg: "Array must start with '{' and end with '}'"
+ };
+ }
+
+ var val = $.trim(value.slice(1, -1)),
+ arr;
+
+ if(val == '') {
+ arr = [];
+ } else {
+ var arr = val.split(',');
+ }
+
+ for (var k in arr) {
+ if (isNaN(arr[k])) {
+ return {
+ valid: false,
+ msg: "Please enter a valid numbers"
+ };
+ }
+ }
+ }
+
return {
valid: true,
msg: null
diff --git a/web/pgadmin/static/js/slickgrid/formatters.js b/web/pgadmin/static/js/slickgrid/formatters.js
index 1a0d60b..5de2dce 100644
--- a/web/pgadmin/static/js/slickgrid/formatters.js
+++ b/web/pgadmin/static/js/slickgrid/formatters.js
@@ -14,20 +14,45 @@
"Numbers": NumbersFormatter,
"Checkmark": CheckmarkFormatter,
"Text": TextFormatter,
+ "Binary": BinaryFormatter,
+ "JsonStringArray": JsonArrayFormatter,
+ "NumbersArray": NumbersArrayFormatter,
+ "TextArray": TextArrayFormatter,
}
}
});
- function JsonFormatter(row, cell, value, columnDef, dataContext) {
- // If column has default value, set placeholder
+ function NullAndDefaultFormatter(row, cell, value, columnDef, dataContext) {
if (_.isUndefined(value) && columnDef.has_default_val) {
- return "<span class='pull-left disabled_cell'>[default]</span>";
+ return "<span class='pull-left disabled_cell'>[default]</span>";
}
else if (
(_.isUndefined(value) && columnDef.not_null) ||
(_.isUndefined(value) || value === null)
) {
return "<span class='pull-left disabled_cell'>[null]</span>";
+ }
+ return null;
+ }
+
+ function NullAndDefaultNumberFormatter(row, cell, value, columnDef, dataContext) {
+ if (_.isUndefined(value) && columnDef.has_default_val) {
+ return "<span class='pull-right disabled_cell'>[default]</span>";
+ }
+ else if (
+ (_.isUndefined(value) && columnDef.not_null) ||
+ (_.isUndefined(value) || value === null)
+ ) {
+ return "<span class='pull-right disabled_cell'>[null]</span>";
+ }
+ return null;
+ }
+
+ function JsonFormatter(row, cell, value, columnDef, dataContext) {
+ // If column has default value, set placeholder
+ var data = NullAndDefaultFormatter(row, cell, value, columnDef, dataContext);
+ if (data) {
+ return data;
} else {
// Stringify only if it's json object
if (typeof value === "object" && !Array.isArray(value)) {
@@ -48,57 +73,113 @@
}
}
- function NumbersFormatter(row, cell, value, columnDef, dataContext) {
+ function JsonArrayFormatter(row, cell, value, columnDef, dataContext) {
// If column has default value, set placeholder
- if (_.isUndefined(value) && columnDef.has_default_val) {
- return "<span class='pull-right disabled_cell'>[default]</span>";
- }
- else if (
- (_.isUndefined(value) || value === null || value === "") ||
- (_.isUndefined(value) && columnDef.not_null)
- ) {
- return "<span class='pull-right disabled_cell'>[null]</span>";
+ var data = NullAndDefaultFormatter(row, cell, value, columnDef, dataContext);
+ if (data) {
+ return data;
+ } else {
+ var data = [];
+ for (var k in value) {
+ // Stringify only if it's json object
+ var v = value[k];
+ if (typeof v === "object" && !Array.isArray(v)) {
+ return data.push(_.escape(JSON.stringify(v)));
+ } else if (Array.isArray(v)) {
+ var temp = [];
+ $.each(v, function(i, val) {
+ if (typeof val === "object") {
+ temp.push(JSON.stringify(val));
+ } else {
+ temp.push(val)
+ }
+ });
+ return data.push(_.escape("[" + temp.join() + "]"));
+ } else {
+ return data.push(_.escape(v));
+ }
+ }
+ return '{' + data.join() + '}';
}
- else {
+ }
+
+ function NumbersFormatter(row, cell, value, columnDef, dataContext) {
+ // If column has default value, set placeholder
+ var data = NullAndDefaultNumberFormatter(row, cell, value, columnDef, dataContext);
+ if (data) {
+ return data;
+ } else {
return "<span style='float:right'>" + _.escape(value) + "</span>";
}
}
+ function NumbersArrayFormatter(row, cell, value, columnDef, dataContext) {
+ // If column has default value, set placeholder
+ var data = NullAndDefaultNumberFormatter(row, cell, value, columnDef, dataContext);
+ if (data) {
+ return data;
+ } else {
+ data = [];
+ for(var k in value) {
+ if (value[k] == null) {
+ data.push("<span class='disabled_cell'>[null]</span>");
+ } else {
+ data.push(_.escape(value[k]));
+ }
+ }
+ return "<span style='float:right'>{" + data.join() + "}</span>";
+ }
+ }
+
function CheckmarkFormatter(row, cell, value, columnDef, dataContext) {
/* Checkbox has 3 states
* 1) checked=true
* 2) unchecked=false
* 3) indeterminate=null
*/
- if (_.isUndefined(value) && columnDef.has_default_val) {
- return "<span class='pull-left disabled_cell'>[default]</span>";
- }
- else if (
- (_.isUndefined(value) && columnDef.not_null) ||
- (value == null || value === "")
- ) {
- return "<span class='pull-left disabled_cell'>[null]</span>";
+ var data = NullAndDefaultFormatter(row, cell, value, columnDef, dataContext);
+ if (data) {
+ return data;
+ } else {
+ return value ? "true" : "false";
}
- return value ? "true" : "false";
}
function TextFormatter(row, cell, value, columnDef, dataContext) {
// If column has default value, set placeholder
- if (_.isUndefined(value) && columnDef.has_default_val) {
- return "<span class='pull-left disabled_cell'>[default]</span>";
- }
- else if (
- (_.isUndefined(value) && columnDef.not_null) ||
- (_.isUndefined(value) || _.isNull(value))
- ) {
- return "<span class='pull-left disabled_cell'>[null]</span>";
- } else if(columnDef.column_type_internal == 'bytea' ||
- columnDef.column_type_internal == 'bytea[]') {
- return "<span class='pull-left disabled_cell'>[" + _.escape(value) + "]</span>";
- }
- else {
+ var data = NullAndDefaultFormatter(row, cell, value, columnDef, dataContext);
+ if (data) {
+ return data;
+ } else {
return _.escape(value);
}
}
+ function TextArrayFormatter(row, cell, value, columnDef, dataContext) {
+ // If column has default value, set placeholder
+ var data = NullAndDefaultFormatter(row, cell, value, columnDef, dataContext);
+ if (data) {
+ return data;
+ } else {
+ data = [];
+ for(var k in value) {
+ if (value[k] === null) {
+ data.push("<span class='disabled_cell'>[null]</span>");
+ } else {
+ data.push(_.escape(value[k]));
+ }
+ }
+ return "{" + data.join() + "}";
+ }
+ }
+
+ function BinaryFormatter(row, cell, value, columnDef, dataContext) {
+ // If column has default value, set placeholder
+ var data = NullAndDefaultFormatter(row, cell, value, columnDef, dataContext);
+ if (data) {
+ return data;
+ } else {
+ return "<span class='pull-left disabled_cell'>[" + _.escape(value) + "]</span>";
+ }
+ }
})(jQuery);
diff --git a/web/pgadmin/tools/sqleditor/command.py b/web/pgadmin/tools/sqleditor/command.py
index 94aec09..3500d3c 100644
--- a/web/pgadmin/tools/sqleditor/command.py
+++ b/web/pgadmin/tools/sqleditor/command.py
@@ -446,7 +446,8 @@ class TableCommand(GridCommand):
query_res = dict()
count = 0
list_of_rowid = []
- list_of_sql = []
+ operations = ('added', 'updated', 'deleted')
+ list_of_sql = {}
_rowid = None
if conn.connected():
@@ -457,7 +458,7 @@ class TableCommand(GridCommand):
# Iterate total number of records to be updated/inserted
for of_type in changed_data:
# No need to go further if its not add/update/delete operation
- if of_type not in ('added', 'updated', 'deleted'):
+ if of_type not in operations:
continue
# if no data to be save then continue
if len(changed_data[of_type]) < 1:
@@ -480,6 +481,7 @@ class TableCommand(GridCommand):
# For newly added rows
if of_type == 'added':
+ list_of_sql[of_type] = []
# When new rows are added, only changed columns data is
# sent from client side. But if column is not_null and has
@@ -506,12 +508,13 @@ class TableCommand(GridCommand):
nsp_name=self.nsp_name,
data_type=column_type,
pk_names=pk_names)
- list_of_sql.append(sql)
+ list_of_sql[of_type].append({'sql': sql, 'data': data})
# Reset column data
column_data = {}
# For updated rows
elif of_type == 'updated':
+ list_of_sql[of_type] = []
for each_row in changed_data[of_type]:
data = changed_data[of_type][each_row]['data']
pk = changed_data[of_type][each_row]['primary_keys']
@@ -521,11 +524,12 @@ class TableCommand(GridCommand):
object_name=self.object_name,
nsp_name=self.nsp_name,
data_type=column_type)
- list_of_sql.append(sql)
- list_of_rowid.append(data)
+ list_of_sql[of_type].append({'sql': sql, 'data': data})
+ list_of_rowid.append(data.get(client_primary_key))
# For deleted rows
elif of_type == 'deleted':
+ list_of_sql[of_type] = []
is_first = True
rows_to_delete = []
keys = None
@@ -559,33 +563,35 @@ class TableCommand(GridCommand):
no_of_keys=no_of_keys,
object_name=self.object_name,
nsp_name=self.nsp_name)
- list_of_sql.append(sql)
-
- for i, sql in enumerate(list_of_sql):
- if sql:
- status, res = conn.execute_void(sql)
- rows_affected = conn.rows_affected()
-
- # store the result of each query in dictionary
- query_res[count] = {'status': status, 'result': res,
- 'sql': sql, 'rows_affected': rows_affected}
- count += 1
-
- if not status:
- conn.execute_void('ROLLBACK;')
- # If we roll backed every thing then update the message for
- # each sql query.
- for val in query_res:
- if query_res[val]['status']:
- query_res[val]['result'] = 'Transaction ROLLBACK'
-
- # If list is empty set rowid to 1
- try:
- _rowid = list_of_rowid[i] if list_of_rowid else 1
- except Exception:
- _rowid = 0
-
- return status, res, query_res, _rowid
+ list_of_sql[of_type].append({'sql': sql, 'data': {}})
+
+ for opr, sqls in list_of_sql.items():
+ for item in sqls:
+ if item['sql']:
+ status, res = conn.execute_void(
+ item['sql'], item['data'])
+ rows_affected = conn.rows_affected()
+
+ # store the result of each query in dictionary
+ query_res[count] = {'status': status, 'result': res,
+ 'sql': sql, 'rows_affected': rows_affected}
+
+ if not status:
+ conn.execute_void('ROLLBACK;')
+ # If we roll backed every thing then update the message for
+ # each sql query.
+ for val in query_res:
+ if query_res[val]['status']:
+ query_res[val]['result'] = 'Transaction ROLLBACK'
+
+ # If list is empty set rowid to 1
+ try:
+ _rowid = list_of_rowid[count] if list_of_rowid else 1
+ except Exception:
+ _rowid = 0
+
+ return status, res, query_res, _rowid
+ count += 1
# Commit the transaction if there is no error found
conn.execute_void('COMMIT;')
diff --git a/web/pgadmin/tools/sqleditor/static/js/sqleditor.js b/web/pgadmin/tools/sqleditor/static/js/sqleditor.js
index fa9d269..622663f 100644
--- a/web/pgadmin/tools/sqleditor/static/js/sqleditor.js
+++ b/web/pgadmin/tools/sqleditor/static/js/sqleditor.js
@@ -577,7 +577,8 @@ define('tools.querytool', [
column_type: c.column_type,
column_type_internal: c.column_type_internal,
not_null: c.not_null,
- has_default_val: c.has_default_val
+ has_default_val: c.has_default_val,
+ is_array: c.is_array
};
// Get the columns width based on longer string among data type or
@@ -592,26 +593,28 @@ define('tools.querytool', [
else {
options['width'] = column_size[table_name][c.name];
}
-
// If grid is editable then add editor else make it readonly
if (c.cell == 'Json') {
options['editor'] = is_editable ? Slick.Editors.JsonText
: Slick.Editors.ReadOnlyJsonText;
- options['formatter'] = Slick.Formatters.JsonString;
+ options['formatter'] = c.is_array ? Slick.Formatters.JsonStringArray : Slick.Formatters.JsonString;
} else if (c.cell == 'number' ||
$.inArray(c.type, ['oid', 'xid', 'real']) !== -1
) {
options['editor'] = is_editable ? Slick.Editors.CustomNumber
: Slick.Editors.ReadOnlyText;
- options['formatter'] = Slick.Formatters.Numbers;
+ options['formatter'] = c.is_array ? Slick.Formatters.NumbersArray : Slick.Formatters.Numbers;
} else if (c.cell == 'boolean') {
options['editor'] = is_editable ? Slick.Editors.Checkbox
: Slick.Editors.ReadOnlyCheckbox;
- options['formatter'] = Slick.Formatters.Checkmark;
- } else {
+ options['formatter'] = c.is_array ? Slick.Formatters.CheckmarkArray : Slick.Formatters.Checkmark;
+ } else if (c.cell == 'binary') {
+ // We do not support editing binary data in SQL editor and data grid.
+ options['formatter'] = Slick.Formatters.Binary;
+ }else {
options['editor'] = is_editable ? Slick.Editors.pgText
: Slick.Editors.ReadOnlypgText;
- options['formatter'] = Slick.Formatters.Text;
+ options['formatter'] = c.is_array ? Slick.Formatters.TextArray : Slick.Formatters.Text;
}
grid_columns.push(options)
@@ -2002,7 +2005,6 @@ define('tools.querytool', [
',' + c.scale + ')' :
')';
}
-
// Identify cell type of column.
switch (type) {
case "json":
@@ -2012,12 +2014,19 @@ define('tools.querytool', [
col_cell = 'Json';
break;
case "smallint":
+ case "smallint[]":
case "integer":
+ case "integer[]":
case "bigint":
+ case "bigint[]":
case "decimal":
+ case "decimal[]":
case "numeric":
+ case "numeric[]":
case "real":
+ case "real[]":
case "double precision":
+ case "double precision[]":
col_cell = 'number';
break;
case "boolean":
@@ -2033,25 +2042,31 @@ define('tools.querytool', [
}
col_cell = 'string';
break;
+ case "bytea":
+ case "bytea[]":
+ col_cell = 'binary';
+ break;
default:
col_cell = 'string';
}
column_label = c.display_name + '<br>' + col_type;
- var col = {
- 'name': c.name,
- 'display_name': c.display_name,
- 'column_type': col_type,
- 'column_type_internal': type,
- 'pos': c.pos,
- 'label': column_label,
- 'cell': col_cell,
- 'can_edit': self.can_edit,
- 'type': type,
- 'not_null': c.not_null,
- 'has_default_val': c.has_default_val
- };
+ var array_type_bracket_index = type.lastIndexOf('[]'),
+ col = {
+ 'name': c.name,
+ 'display_name': c.display_name,
+ 'column_type': col_type,
+ 'column_type_internal': type,
+ 'pos': c.pos,
+ 'label': column_label,
+ 'cell': col_cell,
+ 'can_edit': self.can_edit,
+ 'type': type,
+ 'not_null': c.not_null,
+ 'has_default_val': c.has_default_val,
+ 'is_array': array_type_bracket_index > -1 && array_type_bracket_index + 2 == type.length
+ };
columns.push(col);
});
@@ -2981,7 +2996,15 @@ define('tools.querytool', [
),
copied_rows = rows.map(function (rowIndex) {
return data[rowIndex];
- });
+ }),
+ array_types = [];
+
+ // for quick look up create list of array data types
+ for (var k in self.columns) {
+ if (self.columns[k].is_array) {
+ array_types.push(self.columns[k].name);
+ }
+ }
rows = rows.length == 0 ? self.last_copied_rows : rows
@@ -3000,7 +3023,8 @@ define('tools.querytool', [
_.each(arr, function (val, i) {
if (arr[i] !== undefined) {
- if (_.isObject(arr[i])) {
+ // Do not stringify array types.
+ if (_.isObject(arr[i]) && array_types.indexOf(i) == -1) {
obj[String(i)] = JSON.stringify(arr[i]);
} else {
obj[String(i)] = arr[i];
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/default/insert.sql b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/default/insert.sql
index f7139e4..23ffcb4 100644
--- a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/default/insert.sql
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/default/insert.sql
@@ -4,13 +4,5 @@ INSERT INTO {{ conn|qtIdent(nsp_name, object_name) }} (
{% if not loop.first %}, {% endif %}{{ conn|qtIdent(col) }}{% endfor %}
) VALUES (
{% for col in data_to_be_saved %}
-{########################################################}
-{# THIS IS TO CHECK IF DATA TYPE IS ARRAY? #}
-{% if data_type[col].endswith('[]') %}
-{% set col_value = "{%s}"|format(data_to_be_saved[col])|qtLiteral %}
-{% else %}
-{% set col_value = data_to_be_saved[col]|qtLiteral %}
-{% endif %}
-{########################################################}
-{% if not loop.first %}, {% endif %}{{ col_value }}{% endfor %}
-);
\ No newline at end of file
+{% if not loop.first %}, {% endif %}%({{ col }})s::{{ data_type[col] }}{% endfor %}
+);
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/default/update.sql b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/default/update.sql
index da649e3..c9dfddf 100644
--- a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/default/update.sql
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/default/update.sql
@@ -1,15 +1,7 @@
{# Update the row with primary keys (specified in primary_keys) #}
UPDATE {{ conn|qtIdent(nsp_name, object_name) }} SET
{% for col in data_to_be_saved %}
-{########################################################}
-{# THIS IS TO CHECK IF DATA TYPE IS ARRAY? #}
-{% if data_type[col].endswith('[]') %}
-{% set col_value = "{%s}"|format(data_to_be_saved[col])|qtLiteral %}
-{% else %}
-{% set col_value = data_to_be_saved[col]|qtLiteral %}
-{% endif %}
-{########################################################}
-{% if not loop.first %}, {% endif %}{{ conn|qtIdent(col) }} = {{ col_value }}{% endfor %}
+{% if not loop.first %}, {% endif %}{{ conn|qtIdent(col) }} = %({{ col }})s::{{ data_type[col] }}{% endfor %}
WHERE
{% for pk in primary_keys %}
-{% if not loop.first %} AND {% endif %}{{ conn|qtIdent(pk) }} = {{ primary_keys[pk]|qtLiteral }}{% endfor %};
\ No newline at end of file
+{% if not loop.first %} AND {% endif %}{{ conn|qtIdent(pk) }} = {{ primary_keys[pk]|qtLiteral }}{% endfor %};
diff --git a/web/pgadmin/utils/driver/psycopg2/__init__.py b/web/pgadmin/utils/driver/psycopg2/__init__.py
index 7cda5ef..11952e1 100644
--- a/web/pgadmin/utils/driver/psycopg2/__init__.py
+++ b/web/pgadmin/utils/driver/psycopg2/__init__.py
@@ -50,32 +50,66 @@ else:
_ = gettext
+unicode_type_for_record = psycopg2.extensions.new_type(
+ (2249,),
+ "RECORD",
+ psycopg2.extensions.UNICODE
+)
+
+unicode_array_type_for_record_array = psycopg2.extensions.new_array_type(
+ (2287,),
+ "ARRAY_RECORD",
+ unicode_type_for_record
+)
+
# This registers a unicode type caster for datatype 'RECORD'.
-psycopg2.extensions.register_type(
- psycopg2.extensions.new_type((2249,), "RECORD",
- psycopg2.extensions.UNICODE)
+psycopg2.extensions.register_type(unicode_type_for_record)
+
+# This registers a array unicode type caster for datatype 'ARRAY_RECORD'.
+psycopg2.extensions.register_type(unicode_array_type_for_record_array)
+
+
+# define type caster to convert various pg types into string type
+pg_types_to_string_type = psycopg2.extensions.new_type(
+ (
+ # To cast bytea, interval type
+ 17, 1186,
+
+ # to cast int4range, int8range, numrange tsrange, tstzrange, daterange
+ 3904, 3926, 3906, 3908, 3910, 3912, 3913,
+
+ # date, timestamp, timestamptz, bigint, double precision
+ 1700, 1082, 1114, 1184, 20, 701,
+
+ # real
+ 700
+ ),
+ 'TYPECAST_TO_STRING', psycopg2.STRING
)
-# This registers a type caster to convert various pg types into string type
-psycopg2.extensions.register_type(
- psycopg2.extensions.new_type(
- (
- # To cast bytea, bytea[] and interval type
- 17, 1001, 1186,
-
- # to cast int4range, int8range, numrange tsrange, tstzrange,
- # daterange
- 3904, 3926, 3906, 3908, 3910, 3912, 3913,
-
- # date, timestamp, timestamptz, bigint, double precision, bigint[]
- 1700, 1082, 1114, 1184, 20, 701, 1016,
-
- # double precision[], real, real[]
- 1022, 700, 1021
- ),
- 'TYPECAST_TO_STRING', psycopg2.STRING)
+# define type caster to convert pg array types of above types into
+# array of string type
+pg_array_types_to_array_of_string_type = psycopg2.extensions.new_array_type(
+ (
+ # To cast bytea[] type
+ 1001,
+
+ # bigint[]
+ 1016,
+
+ # double precision[], real[]
+ 1022, 1021
+ ),
+ 'TYPECAST_TO_ARRAY_OF_STRING', pg_types_to_string_type
)
+# This registers a type caster to convert various pg types into string type
+psycopg2.extensions.register_type(pg_types_to_string_type)
+
+# This registers a type caster to convert various pg array types into
+# array of string type
+psycopg2.extensions.register_type(pg_array_types_to_array_of_string_type)
+
def register_string_typecasters(connection):
if connection.encoding != 'UTF8':
@@ -145,6 +179,7 @@ def register_binary_typecasters(connection):
connection
)
+
class Connection(BaseConnection):
"""
class Connection(object)
