Hi Akshay,
It's not issue; Try without quotes. As char data type stores only one
character so it's storing only leading quote (') and
truncating remaining characters (a').
Only exception is when you want to store empty string (char) use two quotes
(either single or double)
they will translated before sending to server.
Apart from this I found that data types "char" and "char"[] (alias to
character and character[] respectively)
were not taken into consideration in sqleditor.js. I have attached updated
patch which fixes this issue.
--
*Harshal Dhumal*
*Sr. Software Engineer*
EnterpriseDB India: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Thu, Sep 14, 2017 at 5:31 PM, Akshay Joshi <akshay.jo...@enterprisedb.com
> wrote:
> Hi Harshal
>
> I have reviewed your patch it looks good to me. Only one issue I have
> found with char[], when I store {'a','b','c'} it gets stored and when I
> refreshed it shows {',','}. Please look into this issue.
>
> On Thu, Sep 14, 2017 at 3:54 PM, Akshay Joshi <
> akshay.jo...@enterprisedb.com> wrote:
>
>> It works, sorry for the noise.
>>
>> On Thu, Sep 14, 2017 at 3:48 PM, Akshay Joshi <
>> akshay.jo...@enterprisedb.com> wrote:
>>
>>> Harshal,
>>>
>>> Can you please rebase the patch, it's not applied with the latest code.
>>>
>>> On Tue, Sep 12, 2017 at 2:37 PM, Akshay Joshi <
>>> akshay.jo...@enterprisedb.com> wrote:
>>>
>>>>
>>>>
>>>> On Tue, Sep 12, 2017 at 2:34 PM, Dave Page <dp...@pgadmin.org> wrote:
>>>>
>>>>> Adding Akshay...
>>>>>
>>>>
>>>> Sure.
>>>>
>>>>>
>>>>> On Tue, Sep 12, 2017 at 10:04 AM, Dave Page <dp...@pgadmin.org> wrote:
>>>>>
>>>>>> Akshay, can you review/commit this please?
>>>>>>
>>>>>> On Mon, Sep 11, 2017 at 3:40 PM, Harshal Dhumal <
>>>>>> harshal.dhu...@enterprisedb.com> wrote:
>>>>>>
>>>>>>> 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
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Dave Page
>>>>>> Blog: http://pgsnake.blogspot.com
>>>>>> Twitter: @pgsnake
>>>>>>
>>>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>>>> The Enterprise PostgreSQL Company
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Dave Page
>>>>> Blog: http://pgsnake.blogspot.com
>>>>> Twitter: @pgsnake
>>>>>
>>>>> EnterpriseDB UK: http://www.enterprisedb.com
>>>>> The Enterprise PostgreSQL Company
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> *Akshay Joshi*
>>>> *Principal Software Engineer *
>>>>
>>>>
>>>>
>>>> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246*
>>>>
>>>
>>>
>>>
>>> --
>>> *Akshay Joshi*
>>> *Principal Software Engineer *
>>>
>>>
>>>
>>> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246*
>>>
>>
>>
>>
>> --
>> *Akshay Joshi*
>> *Principal Software Engineer *
>>
>>
>>
>> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246*
>>
>
>
>
> --
> *Akshay Joshi*
> *Principal Software Engineer *
>
>
>
> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246*
>
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..8dc22cf 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":
@@ -2025,6 +2034,8 @@ define('tools.querytool', [
break;
case "character":
case "character[]":
+ case "\"char\"":
+ case "\"char\"[]":
case "character varying":
case "character varying[]":
if (c.internal_size && c.internal_size >= 0 && c.internal_size != 65535) {
@@ -2033,25 +2044,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 +2998,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 +3025,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)
