On Tue, Nov 1, 2011 at 4:31 PM, Guillaume Lelarge <[email protected]>wrote:
>
> OK, it works now. I understand a lot of improvements could come later,
> but there is one thing that bugs me. And I think it should be fixed
> before applying.
>
> Let's say I wrote this:
>
> SELECT * FROM person JOIN company ON
>
> and try autocompletion afterwards. I get the list of columns in person
> and company, which is great. The issue is that you only have the
> column's name, not the table's name. So you don't know if the column
> refers to the first table or the second. We could say it's not an issue
> if we were sure they were all different. But if I have two times the id
> column, it gets kinda weird. Maybe you should show the table's name in
> front of the column's name.
>
Allright, another week, another patch :-)
Changes since last time:
- Support for aliasses (autocompletion on "select * from person p join
company c on ...) will present a list of "p.id, p.name, c.id, c.name etc.)
- Support for queries without aliasses (select * from person where ...)
- Support for queries without aliasses but with joins (select * from person
join company on ...)
- Support for queries with mixed versions (select * from person p join
company on ...)
- Support for update statements (update person set ...)
- Support for autocompletion after SELECT (select ... from person)
- Fixed a crash
Regards,
Wander Winkelhorst.
diff -U2 -r -a ./old/pgadmin/ctl/ctlSQLBox.cpp ./new//pgadmin/ctl/ctlSQLBox.cpp
--- ./old/pgadmin/ctl/ctlSQLBox.cpp 2011-06-20 21:13:13.000000000 +0200
+++ ./new//pgadmin/ctl/ctlSQLBox.cpp 2011-11-10 14:11:18.188487304 +0100
@@ -620,5 +620,5 @@
-extern "C" char *tab_complete(const char *allstr, const int startptr, const int endptr, void *dbptr);
+extern "C" char *tab_complete(const char *allstr, const char *linestr, const int startptr, const int endptr, void *dbptr);
void ctlSQLBox::OnAutoComplete(wxCommandEvent &rev)
{
@@ -631,11 +631,12 @@
wxString what = GetCurLine().Left(GetCurrentPos() - PositionFromLine(GetCurrentLine()));;
+ wxString alltext = GetCurLine();
int spaceidx = what.Find(' ', true);
char *tab_ret;
if (spaceidx == -1)
- tab_ret = tab_complete(what.mb_str(wxConvUTF8), 0, what.Len() + 1, m_database);
+ tab_ret = tab_complete(alltext.mb_str(wxConvUTF8), what.mb_str(wxConvUTF8), 0, what.Len() + 1, m_database);
else
- tab_ret = tab_complete(what.mb_str(wxConvUTF8), spaceidx + 1, what.Len() + 1, m_database);
+ tab_ret = tab_complete(alltext.mb_str(wxConvUTF8), what.mb_str(wxConvUTF8), spaceidx + 1, what.Len() + 1, m_database);
if (tab_ret == NULL || tab_ret[0] == '\0')
diff -U2 -r -a ./old/pgadmin/utils/tabcomplete.c ./new//pgadmin/utils/tabcomplete.c
--- ./old/pgadmin/utils/tabcomplete.c 2011-06-20 21:13:22.000000000 +0200
+++ ./new//pgadmin/utils/tabcomplete.c 2011-11-09 15:06:25.915448665 +0100
@@ -34,4 +34,7 @@
static char *rl_line_buffer;
+//This one contains all text; for autocompletion purposes:
+static char *rl_alltext_buffer;
+
/*
* Macros to ease typing present in psql, rewritten for our API
@@ -409,7 +412,8 @@
* Entrypoint from the C++ world
*/
-char *tab_complete(const char *allstr, const int startptr, const int endptr, void *dbptr)
+char *tab_complete(const char *linestr, const char *allstr, const int startptr, const int endptr, void *dbptr)
{
- rl_line_buffer = (char *)allstr;
- return psql_completion((char *)(allstr + startptr), startptr,endptr,dbptr);
+ rl_line_buffer = (char *)linestr;
+ rl_alltext_buffer = (char *)allstr;
+ return psql_completion((char *)(linestr + startptr), startptr,endptr,dbptr);
}
diff -U2 -r -a ./old/pgadmin/utils/tab-complete.inc ./new//pgadmin/utils/tab-complete.inc
--- ./old/pgadmin/utils/tab-complete.inc 2011-06-20 21:13:22.000000000 +0200
+++ ./new//pgadmin/utils/tab-complete.inc 2011-11-10 14:35:23.307653263 +0100
@@ -229,4 +229,14 @@
" AND pg_catalog.pg_table_is_visible(c.oid)"
+#define Query_for_list_of_attributes_list \
+"SELECT pg_catalog.quote_ident(relname) || '.' || pg_catalog.quote_ident(attname) "\
+" FROM pg_catalog.pg_attribute a, pg_catalog.pg_class c "\
+" WHERE c.oid = a.attrelid "\
+" AND a.attnum > 0 "\
+" AND NOT a.attisdropped "\
+" AND substring(pg_catalog.quote_ident(attname),1,%d)='%s' "\
+" AND pg_catalog.pg_table_is_visible(c.oid)"\
+" AND pg_catalog.quote_ident(relname) in ("
+
#define Query_for_list_of_databases \
"SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database "\
@@ -317,4 +327,15 @@
/*
+ * This is a list of tables and their corrosponding tablealiasses
+ */
+
+typedef struct
+{
+ char *tablename;
+ char *tablealias;
+ void *nextAlias;
+} pgsql_alias_t;
+
+/*
* This is a list of all "things" in Pgsql, which can show up after CREATE or
* DROP; and there is also a query to get a list of them.
@@ -362,4 +383,95 @@
+char * str_replace (const char *string, const char *substr, const char *replacement) {
+ char *tok = NULL;
+ char *newstr = NULL;
+ char *oldstr = NULL;
+ char *head = NULL;
+
+ /* if either substr or replacement is NULL, duplicate string a let caller handle it */
+ if ( (substr == NULL) || (replacement == NULL) ) return strdup(string);
+ newstr = strdup(string);
+ head = newstr;
+ while ( (tok = strstr(head, substr))) {
+ oldstr = newstr;
+ newstr = malloc( strlen(oldstr) - strlen(substr) + strlen(replacement) + 1 );
+ /*failed to alloc mem, free old string and return NULL */
+ if (newstr == NULL) {
+ free(oldstr);
+ return NULL;
+ }
+ memcpy(newstr, oldstr, tok - oldstr);
+ memcpy(newstr + (tok - oldstr), replacement, strlen(replacement) );
+ memcpy(newstr + (tok - oldstr) + strlen(replacement), tok + strlen(substr), strlen(oldstr) - strlen(substr) - (tok - oldstr) );
+ memset(newstr + strlen(oldstr) - strlen(substr) + strlen(replacement) , 0, 1 );
+ /* move back head right after the last replacement */
+ head = newstr + (tok - oldstr) + strlen(replacement);
+ free(oldstr);
+ }
+ return newstr;
+}
+
+/*
+A function to get all attributes of a list of tables
+Used for autocompletion.
+*/
+static char * complete_with_attr_for_list(char *tablelist, char *text, void *dbptr, pgsql_alias_t *currentAlias) {
+ char* attrquery;
+ char* result;
+
+ char* textCopy = malloc(strlen(text) + 1);
+
+ char *p;
+ if (text[0] == ' ') {
+ //If it starts with a space, don't take the first word
+ p = "";
+ } else {
+ //It didn't start with a space, so the user must be in the middle of a word, and wants to autocomplete that.
+ strcpy(textCopy, text);
+ //Get first word of "text"
+ p = strtok(textCopy, " ");
+ if (p == NULL) {
+ p = "";
+ }
+ }
+
+ attrquery = malloc(strlen(Query_for_list_of_attributes_list) + strlen(tablelist) + 1);
+ strcpy(attrquery, Query_for_list_of_attributes_list);
+ strcat(attrquery, tablelist);
+ result = complete_from_query(p, attrquery, NULL, dbptr);
+
+ free(attrquery);
+ free(textCopy);
+
+ int multipleTables = 0;
+
+ if (currentAlias != NULL) {
+ multipleTables = (currentAlias->nextAlias != NULL);
+ }
+
+ //Replace table names by their aliasses:
+ while (currentAlias != NULL) {
+ char *replaceThis = malloc(strlen(currentAlias->tablename) + 3);
+ strcpy(replaceThis, currentAlias->tablename);
+ strcat(replaceThis, ".");
+ if (currentAlias->tablealias == NULL) {
+ if (!multipleTables) {
+ //Not interested in table names; there is only one table in the query and it doesn't have an alias.
+ result = str_replace(result, replaceThis, "");
+ }
+ } else {
+ char *replaceWith = malloc(strlen(currentAlias->tablealias) + 3);
+ strcpy(replaceWith, currentAlias->tablealias);
+ strcat(replaceWith, ".");
+ result = str_replace(result, replaceThis, replaceWith);
+ free(replaceWith);
+ }
+ free(replaceThis);
+ currentAlias = currentAlias->nextAlias;
+ }
+
+ return result;
+}
+
/* The completion function. Acc. to readline spec this gets passed the text
entered to far and its start and end in the readline buffer. The return value
@@ -401,4 +513,87 @@
(void) end; /* not used */
+ char *buffer;
+ char *tablelist;
+ char *scratch;
+ buffer = malloc(strlen(rl_line_buffer) + 1);
+ tablelist = malloc(strlen(rl_line_buffer) * 2); // times 2 because stuff gets added to this.
+ strcpy (tablelist,"");
+ strcpy(buffer, rl_line_buffer);
+
+ #define START 1
+ #define TABLE 2
+ #define TABLEALIAS 3
+ int grammarState = START;
+ char *p = strtok(buffer, " ");
+
+ pgsql_alias_t *currentAlias = NULL;
+ pgsql_alias_t *firstAlias = NULL;
+
+ while (p) {
+ if (grammarState == TABLE) {
+ //Add tablename to list of tables
+ strcat(tablelist,"'");
+ scratch = malloc(strlen(p) * 2 + 1);
+ PQescapeString(scratch, p, strlen(p));
+ strcat(tablelist, scratch);
+ if (currentAlias != NULL) {
+ currentAlias->nextAlias = malloc(sizeof(pgsql_alias_t));
+ currentAlias = currentAlias->nextAlias;
+ } else {
+ currentAlias = malloc(sizeof(pgsql_alias_t));
+ firstAlias = currentAlias;
+ }
+ currentAlias->nextAlias = NULL;
+ currentAlias->tablealias = NULL;
+ currentAlias->tablename = scratch;
+ strcat(tablelist,"', ");
+
+ if (pg_strcasecmp(p, "JOIN") == 0) {
+ grammarState = TABLE;
+ } else if (pg_strcasecmp(p, "WHERE") == 0) {
+ grammarState = START;
+ } else {
+ //Must be alias :)
+ grammarState = TABLEALIAS;
+ }
+ } else if (grammarState == TABLEALIAS) {
+ if (pg_strcasecmp(p, "JOIN") == 0) {
+ grammarState = TABLE;
+ } else if (pg_strcasecmp(p, "ON") == 0) {
+ grammarState = START;
+ } else if (pg_strcasecmp(p, "WHERE") == 0) {
+ grammarState = START;
+ } else {
+ //Must be alias :)
+ if (currentAlias->tablealias == NULL) { //Only interrested in the first word after a tablename
+ scratch = malloc(strlen(p) * 2 + 1);
+ PQescapeString(scratch, p, strlen(p));
+ currentAlias->tablealias = scratch;
+ }
+
+ grammarState = TABLEALIAS;
+ }
+ } else {
+ if (pg_strcasecmp(p, "FROM") == 0) {
+ grammarState = TABLE;
+ } else if (pg_strcasecmp(p, "UPDATE") == 0) {
+ grammarState = TABLE;
+ } else if (pg_strcasecmp(p, "JOIN") == 0) {
+ grammarState = TABLE;
+ }
+ }
+
+ p = strtok(NULL, " ");
+ }
+
+ free(buffer);
+
+ currentAlias = firstAlias;
+ while (currentAlias != NULL) {
+ currentAlias = currentAlias->nextAlias;
+ }
+
+ strcat(tablelist,"' ')"); //Add dummy to mark end-of-list
+
#ifdef HAVE_RL_COMPLETION_APPEND_CHARACTER
rl_completion_append_character = ' ';
@@ -1356,5 +1551,5 @@
pg_strcasecmp(prev2_wd, "ORDER") == 0 &&
pg_strcasecmp(prev_wd, "BY") == 0)
- COMPLETE_WITH_ATTR(prev3_wd);
+ matches = complete_with_attr_for_list(tablelist, text, dbptr, firstAlias);
/* PREPARE xx AS */
@@ -1389,5 +1584,8 @@
/* SELECT */
- /* naah . . . */
+ /* Sure, why not . . . */
+ else if (pg_strcasecmp(prev_wd, "SELECT") == 0) {
+ matches = complete_with_attr_for_list(tablelist, text, dbptr, firstAlias);
+ }
/* SET, RESET, SHOW */
@@ -1621,5 +1819,5 @@
/* Simple case of the word before the where being the table name */
else if (pg_strcasecmp(prev_wd, "WHERE") == 0)
- COMPLETE_WITH_ATTR(prev2_wd);
+ matches = complete_with_attr_for_list(tablelist, text, dbptr, firstAlias);
/* ... FROM ... */
@@ -1630,4 +1828,25 @@
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsv, NULL);
+/* ... JOIN ... */
+ else if (pg_strcasecmp(prev_wd, "JOIN") == 0 &&
+ pg_strcasecmp(prev3_wd, "COPY") != 0 &&
+ pg_strcasecmp(prev3_wd, "\\copy") != 0)
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsv, NULL);
+
+/* ... ON ... */
+ else if (pg_strcasecmp(prev_wd, "ON") == 0)
+ matches = complete_with_attr_for_list(tablelist, text, dbptr, firstAlias);
+
+/* ... = ... */
+ else if (pg_strcasecmp(prev_wd, "=") == 0)
+ matches = complete_with_attr_for_list(tablelist, text, dbptr, firstAlias);
+
+/* ... AND ... */
+ else if (pg_strcasecmp(prev_wd, "AND") == 0)
+ matches = complete_with_attr_for_list(tablelist, text, dbptr, firstAlias);
+
+/* ... OR ... */
+ else if (pg_strcasecmp(prev_wd, "OR") == 0)
+ matches = complete_with_attr_for_list(tablelist, text, dbptr, firstAlias);
/* Backslash commands */
@@ -1731,4 +1950,15 @@
free(prev4_wd);
free(prev5_wd);
+ free(tablelist);
+
+ currentAlias = firstAlias;
+ pgsql_alias_t *prevAlias = NULL;
+ while (currentAlias != NULL) {
+ free(currentAlias->tablename);
+ free(currentAlias->tablealias);
+ prevAlias = currentAlias;
+ currentAlias = currentAlias->nextAlias;
+ free(prevAlias);
+ }
/* Return our Grand List O' Matches */
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
