1#!/usr/local/bin/python3.8
2
3############################################################################
4#
5# MODULE:       v.db.join
6# AUTHOR(S):    Markus Neteler
7#               Converted to Python by Glynn Clements
8# PURPOSE:      Join a table to a map table
9# COPYRIGHT:    (C) 2007-2009 by Markus Neteler and the GRASS Development Team
10#
11#               This program is free software under the GNU General Public
12#               License (>=v2). Read the file COPYING that comes with GRASS
13#               for details.
14#
15#############################################################################
16
17#%module
18#% description: Joins a database table to a vector map table.
19#% keyword: vector
20#% keyword: attribute table
21#% keyword: database
22#%end
23
24#%option G_OPT_V_MAP
25#% description: Vector map to which to join other table
26#% guidependency: layer,column
27#%end
28
29#%option G_OPT_V_FIELD
30#% description: Layer where to join
31#% guidependency: column
32#%end
33
34#%option G_OPT_DB_COLUMN
35#% description: Identifier column (e.g.: cat) in the vector table to be used for join
36#% required : yes
37#%end
38
39#%option G_OPT_DB_TABLE
40#% key: other_table
41#% description: Other table name
42#% required: yes
43#% guidependency: ocolumn,scolumns
44#%end
45
46#%option G_OPT_DB_COLUMN
47#% key: other_column
48#% description: Identifier column (e.g.: id) in the other table used for join
49#% required: yes
50#%end
51
52#%option G_OPT_DB_COLUMN
53#% key: subset_columns
54#% multiple: yes
55#% required: no
56#% description: Subset of columns from the other table
57#%end
58
59import sys
60import string
61import grass.script as grass
62from grass.exceptions import CalledModuleError
63
64
65def main():
66    map = options['map']
67    layer = options['layer']
68    column = options['column']
69    otable = options['other_table']
70    ocolumn = options['other_column']
71    if options['subset_columns']:
72        scolumns = options['subset_columns'].split(',')
73    else:
74        scolumns = None
75
76    try:
77        f = grass.vector_layer_db(map, layer)
78    except CalledModuleError:
79        sys.exit(1)
80
81    maptable = f['table']
82    database = f['database']
83    driver = f['driver']
84
85    if driver == 'dbf':
86        grass.fatal(_("JOIN is not supported for tables stored in DBF format"))
87
88    if not maptable:
89        grass.fatal(_("There is no table connected to this map. Unable to join any column."))
90
91    # check if column is in map table
92    if column not in grass.vector_columns(map, layer):
93        grass.fatal(_("Column <%s> not found in table <%s>") % (column,
94                                                                maptable))
95
96    # describe other table
97    all_cols_ot = grass.db_describe(otable, driver=driver, database=database)['cols']
98
99    # check if ocolumn is on other table
100    if ocolumn not in [ocol[0] for ocol in all_cols_ot]:
101        grass.fatal(_("Column <%s> not found in table <%s>") % (ocolumn, otable))
102
103    # determine columns subset from other table
104    if not scolumns:
105        # select all columns from other table
106        cols_to_add = all_cols_ot
107    else:
108        cols_to_add = []
109        # check if scolumns exists in the other table
110        for scol in scolumns:
111            found = False
112            for col_ot in all_cols_ot:
113                if scol == col_ot[0]:
114                    found = True
115                    cols_to_add.append(col_ot)
116                    break
117            if not found:
118                grass.warning(_("Column <%s> not found in table <%s>") % (scol, otable))
119
120    all_cols_tt = grass.vector_columns(map, int(layer)).keys()
121
122    select = "SELECT $colname FROM $otable WHERE $otable.$ocolumn=$table.$column"
123    template = string.Template("UPDATE $table SET $colname=(%s);" % select)
124
125    for col in cols_to_add:
126        # skip the vector column which is used for join
127        colname = col[0]
128        if colname == column:
129            continue
130
131        use_len = False
132        if len(col) > 2:
133            use_len = True
134            # Sqlite 3 does not support the precision number any more
135            if driver == "sqlite":
136                use_len = False
137            # MySQL - expect format DOUBLE PRECISION(M,D), see #2792
138            elif driver == "mysql" and col[1] == 'DOUBLE PRECISION':
139                use_len = False
140
141        if use_len:
142            coltype = "%s(%s)" % (col[1], col[2])
143        else:
144            coltype = "%s" % col[1]
145
146        colspec = "%s %s" % (colname, coltype)
147
148        # add only the new column to the table
149        if colname not in all_cols_tt:
150            try:
151                grass.run_command('v.db.addcolumn', map=map,
152                                  columns=colspec, layer=layer)
153            except CalledModuleError:
154                grass.fatal(_("Error creating column <%s>") % colname)
155
156        stmt = template.substitute(table=maptable, column=column,
157                                   otable=otable, ocolumn=ocolumn,
158                                   colname=colname)
159        grass.debug(stmt, 1)
160        grass.verbose(
161            _("Updating column <%s> of vector map <%s>...") %
162            (colname, map))
163        try:
164            grass.write_command('db.execute', stdin=stmt, input='-',
165                                database=database, driver=driver)
166        except CalledModuleError:
167            grass.fatal(_("Error filling column <%s>") % colname)
168
169    # write cmd history
170    grass.vector_history(map)
171
172    return 0
173if __name__ == "__main__":
174    options, flags = grass.parser()
175    sys.exit(main())
176