[Bug] v.in.ogr: Link to database table is broken after importing a new .shp file

[geografin]

Describe the bug
Vector .shp files can't be imported with attribute table. If they are imported, database connection breaks.

To Reproduce
Steps to reproduce the behavior:

1. Go to '...' and download any standard region.
2. Try to import this vector file into GRASS with v.in.ogr without -t flag and without any settings in attribute section.
3. See error "Can't create attribute table".
   2a. Try to import with -t flag - the result will be the same.
4. Now take one watershed by your choice and export it with QGIS without any columns, but as automatically it does - with FID column.
5. Download lake polygons from here https://www.hydrosheds.org/products/hydrolakes
6. Try to import lakes to GRASS with v.in.ogr telling key=Hylak_id
7. Check if lakes have attribute table created.
8. Import now your single watershed with key=FID.
9. Try to select lakes with this layer.
10. See the error "No such table HydroLAKES_polys_v10"
11. You can also check with db.tables, that no tables you have in the database. But
12. In the dbln file the link exists.

Expected behavior
I expected any vector to be imported without any supplemental setting with autocreation of an attribute table. Also I expected that tables links are stable, otherwise GRASS can't be used as a database.

System description (please complete the following information):
I make all operations in the same LOCATION with docker container, then I exit and enter from Windows GUI to see results.

• Operating System: Windows 10/Linux with GRASS docker container on Debian.
• GRASS GIS version 8.4.0dev

Additional context
When I imported lakes and a watershed (without a key column) I tried to select lakes with the watershed in Windows GUI, and I managed. Then from docker I failed, and now from GUI I also don't see any table and can't select in the same way (because of unlinked vector).

[geografin]

My principal question is: what is the classical proper way to import .shp files to preserve linking. My LOCATION serve as a database with some rasters and vectors that are always there (they are very big). My script imports any chosen watershed file and cuts big rasters and select vectors with this polygon. So I want to cleanly import and then remove working polygon without destructing the database.

[geografin]

I think there are two problems described in one post, but I trust they come from the unique root. To be honest, I can't describe exactly the moment when the database looses the link.

[tmszi]

Shapefile e.g. hybas_lake_eu_lev01_v1c.shp DBF file contains column name ORDER which is reserved SQLite keyword, and it seems column is not properly escaped inside code during creating table.

1. Download shapefiles e.g.

mkdir /tmp/shp && cd /tmp/shp && wget https://data.hydrosheds.org/file/hydrobasins/customized_with_lakes/hybas_lake_eu_lev01-12_v1c.zip

2. Extract archive with cd /tmp/shp && unzip hybas_lake_eu_lev01-12_v1c.zip
3. Try import some shapefile with

 v.in.ogr input=/tmp/shp/hybas_lake_eu_lev01_v1c.shp output=hybas_lake_eu_lev01_v1 project=test

3. See error

GRASS nc_basic_spm_grass7/PERMANENT:~ > v.in.ogr input=/tmp/shp/hybas_lake_eu_lev01_v1c.shp output=hybas_lake_eu_lev01_v1 project=hybas
Project <hybas> created
Check if OGR layer <hybas_lake_eu_lev01_v1c> contains polygons...
 100%
Creating attribute table for layer <hybas_lake_eu_lev01_v1c>...
DBMI-SQLite driver error:
Error in sqlite3_prepare():
near "ORDER": syntax error

DBMI-SQLite driver error:
Error in sqlite3_prepare():
near "ORDER": syntax error

ERROR: Unable to create table: 'create table hybas_lake_eu_lev01_v1 (cat
       integer, HYBAS_ID integer, NEXT_DOWN integer, NEXT_SINK integer,
       MAIN_BAS integer, DIST_SINK double precision, DIST_MAIN double
       precision, SUB_AREA double precision, UP_AREA double precision,
       PFAF_ID integer, SIDE varchar ( 6 ), LAKE integer, ENDO integer,
       COAST integer, ORDER integer, SORT integer)'

[geografin]

I tried again:

1. Deleted all vector layers from LOCATION.
2. Imported HydroLAKES vector without any settings from GUI on Windows as it is (successfully)
3. Imported basin.shp without any settings as it is (with column FID).
4. Deleted basin from LOCATION and exited from GUI.
5. Run container.
6. Run my script where v.in.ogr for basin vector without any settings and with overwrite
7. Got errors:

Creating attribute table for layer <basin>...
DBMI-SQLite driver error:
Error in sqlite3_prepare():
table basin already exists

DBMI-SQLite driver error:
Error in sqlite3_prepare():
table basin already exists

ERROR: Unable to create table: 'create table basin (cat integer, FID
       integer)'
LAKE_AREA=5
BUFFER=10
WARNING: Coor file of vector map <basin@PERMANENT> is larger than it should
         be (18 bytes excess)
WARNING: East (7.90505033345994e-323) is not larger than West
         (6.95300572703085e-310)
ERROR: Illegal latitude for North: 7.32278e+25

Although db.tables didn't show any table for basin from GUI. That means I can properly import the file from GUI, but can't import it with the same command from linux terminal.
NB: I've deleted basin previously, where the table existing comes from?

[tmszi]

With this fix #3605 I successfully import shapefile from my comment above.

GRASS nc_basic_spm_grass7/PERMANENT:~ > v.in.ogr input=/tmp/shp/hybas_lake_eu_lev01_v1c.shp output=hybas_lake_eu_lev01_v1 project=hybas
Project <hybas> created
Check if OGR layer <hybas_lake_eu_lev01_v1c> contains polygons...
 100%
Creating attribute table for layer <hybas_lake_eu_lev01_v1c>...
Importing 1 features (OGR layer <hybas_lake_eu_lev01_v1c>)...
 100%
-----------------------------------------------------
Registering primitives...
-----------------------------------------------------
Cleaning polygons
-----------------------------------------------------
Breaking polygons...
Breaking polygons (pass 1: select break points)...
 100%
Breaking polygons (pass 2: break at selected points)...
 100%
-----------------------------------------------------
Removing duplicates...
 100%
-----------------------------------------------------
Breaking boundaries...
 100%
-----------------------------------------------------
Removing duplicates...
 100%
-----------------------------------------------------
Cleaning boundaries at nodes...
 100%
-----------------------------------------------------
Merging boundaries...
 100%
-----------------------------------------------------
Removing dangles...
 100%
-----------------------------------------------------
Building areas...
-----------------------------------------------------
Removing bridges...
 100%
-----------------------------------------------------
Registering primitives...
Building areas...
 100%
Attaching islands...
 100%
-----------------------------------------------------
Finding centroids for OGR layer <hybas_lake_eu_lev01_v1c>...
 100%
-----------------------------------------------------
Writing centroids...
 100%
-----------------------------------------------------
5193 input polygons
Total area: 1.8739E+13 (9466 areas)
Area without category: 4.66917E+11 (4273 areas)
-----------------------------------------------------
Copying features...
 100%
Building topology for vector map <hybas_lake_eu_lev01_v1@PERMANENT>...
Registering primitives...
Building areas...
 100%
Attaching islands...
 100%
Attaching centroids...
 100%

But it is not possible see attribute table data with GUI tool (error message is showed, because table column name is DB reserved keyword and is not escaped).

But with command db.select module it is possible query hybas_lake_eu_lev01_v1 table successfully:

GRASS hybas/PERMANENT:~ > db.tables
hybas_lake_eu_lev01_v1

GRASS hybas/PERMANENT:~ > db.select table=hybas_lake_eu_lev01_v1
cat|HYBAS_ID|NEXT_DOWN|NEXT_SINK|MAIN_BAS|DIST_SINK|DIST_MAIN|SUB_AREA|UP_AREA|PFAF_ID|SIDE|LAKE|ENDO|COAST|ORDER|SORT
1|2010000010|0|2010000010|2010000010|0|0|18236071.2|18236071|2|M|0|0|1|0|1

[geografin]

The same error I got for basin2 file which is simply exported from HydroSHEDS basins, like @tmszi proposed.

Check if OGR layer <basin2> contains polygons...
 100%
Creating attribute table for layer <basin2>...
DBMI-SQLite driver error:
Error in sqlite3_prepare():
table basin already exists

DBMI-SQLite driver error:
Error in sqlite3_prepare():
table basin already exists

ERROR: Unable to create table: 'create table basin (cat integer, HYBAS_ID
       integer, NEXT_DOWN integer, NEXT_SINK integer, MAIN_BAS integer,
       DIST_SINK double precision, DIST_MAIN double precision, SUB_AREA
       double precision, UP_AREA double precision, PFAF_ID integer, SIDE
       varchar ( 6 ), LAKE integer, ENDO integer, COAST integer, ORDER
       integer, SORT integer)'
LAKE_AREA=5
BUFFER=10
WARNING: Coor file of vector map <basin@PERMANENT> is larger than it should
         be (18 bytes excess)
WARNING: East (7.90505033345994e-323) is not larger than West
         (6.95273213512812e-310)
ERROR: Illegal latitude for North: 7.32278e+25
ERROR: Illegal latitude for North: 192

Importing basin from such a full and clean file is preferrable.

[geografin]

I haven't managed to load the table of HydroLAKES layer still.
The error with basin file seem to be corrected, but after basin had been loaded, the HydroLAKES table seem to loose the link again.

DBMI-SQLite driver error:
Error in sqlite3_prepare():
select * from HydroLAKES_polys_v10 where 0 = 1
no such table: HydroLAKES_polys_v10

DBMI-SQLite driver error:
Error in sqlite3_prepare():
select * from HydroLAKES_polys_v10 where 0 = 1
no such table: HydroLAKES_polys_v10

And by the way, yes, the basin shp was imported, but there is no features in the table, and the error is "Unable to open select cursor" when I try to display the attribute table.

[tmszi]

I haven't managed to load the table of HydroLAKES layer still. The error with basin file seem to be corrected, but after basin had been loaded, the HydroLAKES table seem to loose the link again.

DBMI-SQLite driver error:
Error in sqlite3_prepare():
select * from HydroLAKES_polys_v10 where 0 = 1
no such table: HydroLAKES_polys_v10

DBMI-SQLite driver error:
Error in sqlite3_prepare():
select * from HydroLAKES_polys_v10 where 0 = 1
no such table: HydroLAKES_polys_v10

And by the way, yes, the basin shp was imported, but there is no features in the table, and the error is "Unable to open select cursor" when I try to display the attribute table.

Could @geografin post exact reproducible steps, please (same as in my comment above #3604 (comment))?

[tmszi]

And by the way, yes, the basin shp was imported, but there is no features in the table, and the error is "Unable to open select cursor" when I try to display the attribute table.

Vector map attribute table (GUI tool) should be will open with this fix #3614 or use v.db.select or db.select inside emulator terminal. GUI tool require much more changes to work with attribute table which contains columns names with reserved DB backend keywords.