Unnecessary select queries issued before add

[nachtkinder]

Flask-Appbuilder issues two select queries for each unique field on a model before inserting a new instance of that model. This is a side-effect of validation of unique fields.

Environment

Flask-Appbuilder version: 1.8.1+

pip freeze output (Note Flask-Appbuilder 2.1.3 installed from Git):

apispec==1.3.3 arrow==0.14.2 attrs==19.1.0 Babel==2.7.0 certifi==2019.3.9 chardet==3.0.4 Click==7.0 colorama==0.4.1 defusedxml==0.6.0 Flask==1.0.3 -e git+https://github.com/dpgaspar/Flask-AppBuilder.git@fd8e323fcd59ec4b28df91e12915eeebdf293060#egg=Flask_AppBuilder Flask-Babel==0.12.2 Flask-JWT-Extended==3.18.2 Flask-Login==0.4.1 Flask-OpenID==1.2.5 Flask-SQLAlchemy==2.4.0 Flask-WTF==0.14.2 idna==2.8 itsdangerous==1.1.0 Jinja2==2.10.1 jsonschema==3.0.1 MarkupSafe==1.1.1 marshmallow==2.19.2 marshmallow-enum==1.4.1 marshmallow-sqlalchemy==0.16.3 prison==0.1.1 PyJWT==1.7.1 PyMySQL==0.9.3 pyrsistent==0.15.2 python-dateutil==2.8.0 python3-openid==3.1.0 pytz==2019.1 PyYAML==5.1 requests==2.22.0 six==1.12.0 SQLAlchemy==1.3.4 SQLAlchemy-Utils==0.34.0 urllib3==1.25.3 Werkzeug==0.15.4 WTForms==2.2.1

Describe the expected results

Tell us what should happen.

Paste a minimal example that causes the problem.

app.py.txt

Describe the actual results

MySQL general log contains similar to the following:

2019-06-11T15:22:03.115617Z       445 Query     SELECT count('*') AS count_1                                                     
FROM two_unique  
WHERE two_unique.unique1 = 4
2019-06-11T15:22:03.117482Z       445 Query     SELECT two_unique.id AS two_unique_id, two_unique.unique1 AS two_unique_unique1, two_
unique.unique2 AS two_unique_unique2, two_unique.non_unique AS two_unique_non_unique
FROM two_unique
WHERE two_unique.unique1 = 4
2019-06-11T15:22:03.119085Z       445 Query     SELECT count('*') AS count_1
FROM two_unique
WHERE two_unique.unique2 = 4
2019-06-11T15:22:03.120065Z       445 Query     SELECT two_unique.id AS two_unique_id, two_unique.unique1 AS two_unique_unique1, two_
unique.unique2 AS two_unique_unique2, two_unique.non_unique AS two_unique_non_unique
FROM two_unique
WHERE two_unique.unique2 = 4
2019-06-11T15:22:03.121609Z       445 Query     INSERT INTO two_unique (unique1, unique2, non_unique) VALUES (4, 4, 4)
2019-06-11T15:22:03.122206Z       445 Query     COMMIT

Steps to reproduce

Setup

1. Install MySQL server, enable general query log and create a new schema with create schema flask_app default character set utf8;.
2. Create a new MySQL user and grant it all privileges to flask_app.*.
3. Download app.py.txt and rename to app.py.
4. Amend database connection in app.py with appropriate values for the MySQL database and user created in step 2.
5. Create an admin user with flask fab create-admin.
6. Run app.py

Reproducing the issue

Having followed the instructions above:

1. Monitor MySQL's general query log (sudo tail -f /var/log/mysql.log or similar on Linux platforms)
2. Browse to http://localhost:8080
3. Login as the admin user created in step 5 of the setup instructions above.
4. Select "NoUniqueView" from the main menu.
5. Click the "+" button to add a new record.
6. Fill out the form with appropriate values.
7. Submit the form.
8. Observe the entries in the MySQL general log.
9. Repeat steps 5 through 8 for the "OneUniqueView" and "TwoUniqueView"

You'll notice that when adding a "NoUnique" record that no selections are made from the no_unique table immediately before the insert of the new values. With the other two models you'll notice two select queries from the relevant table for each unique field on the model.

Surely the same can be achieved with at most one query per unique field and perhaps without selecting multiple columns or counting all columns of the model's table?

Regards, Nachtkinder

[dpgaspar]

Hi @nachtkinder,

Thank you for reporting this. I'll check this

[dpgaspar]

Tested this using the quickhowto example and got only one query to test the unique field

2019-07-10 20:25:18,980:INFO:sqlalchemy.engine.base.Engine:SELECT count(?) AS count_1 
FROM contact_group 
WHERE contact_group.name = ?
2019-07-10 20:25:18,981:INFO:sqlalchemy.engine.base.Engine:('*', '1')
2019-07-10 20:25:18,982:INFO:sqlalchemy.engine.base.Engine:SELECT contact_group.id AS contact_group_id, contact_group.name AS contact_group_name 
FROM contact_group 
WHERE contact_group.name = ?

[nachtkinder]

Hi @dpgaspar ,

Thanks for getting back to me on this. I created a new pipenv and installed only the latest Flask-Appbuilder and mysqlclient, then added the flag to get SQLAlchemy to echo out its queries and it resulted in the following extract when adding a OneUnique item:

2019-07-12 14:46:12,301:INFO:sqlalchemy.engine.base.Engine:('OneUniqueView', 'can_add', 1)
2019-07-12 14:46:12,303 INFO sqlalchemy.engine.base.Engine SELECT count(%s) AS count_1
FROM one_unique WHERE one_unique.unique1 = %s
2019-07-12 14:46:12,303:INFO:sqlalchemy.engine.base.Engine:SELECT count(%s) AS count_1 FROM one_unique WHERE one_unique.unique1 = %s
2019-07-12 14:46:12,303 INFO sqlalchemy.engine.base.Engine ('', 1)
2019-07-12 14:46:12,303:INFO:sqlalchemy.engine.base.Engine:('', 1)
2019-07-12 14:46:12,304 INFO sqlalchemy.engine.base.Engine SELECT one_unique.id AS one_unique_id, one_unique.unique1 AS one_unique_unique1 FROM one_unique WHERE one_unique.unique1 = %s
2019-07-12 14:46:12,304:INFO:sqlalchemy.engine.base.Engine:SELECT one_unique.id AS one_unique_id, one_unique.unique1 AS one_unique_unique1 FROM one_unique WHERE one_unique.unique1 = %s
2019-07-12 14:46:12,304 INFO sqlalchemy.engine.base.Engine (1,)
2019-07-12 14:46:12,304:INFO:sqlalchemy.engine.base.Engine:(1,)
2019-07-12 14:46:12,306 INFO sqlalchemy.engine.base.Engine INSERT INTO one_unique (unique1) VALUES (%s)
2019-07-12 14:46:12,306:INFO:sqlalchemy.engine.base.Engine:INSERT INTO one_unique (unique1) VALUES (%s) 2019-07-12 14:46:12,306 INFO sqlalchemy.engine.base.Engine (1,) 2019-07-12 14:46:12,306:INFO:sqlalchemy.engine.base.Engine:(1,) 2019-07-12 14:46:12,306 INFO sqlalchemy.engine.base.Engine COMMIT 2019-07-12 14:46:12,306:INFO:sqlalchemy.engine.base.Engine:COMMIT

The bolded entries show that the framework is performing first a count() of the rows and then selecting rows that match the supplied value. My point is that the framework only need to do one or the other at that point.

Could you please take a look at the attached app.py.txt to see if I am doing anything stupid?

New output of pipenv run pip freeze:

apispec==2.0.2
attrs==19.1.0
Babel==2.7.0
Click==7.0
colorama==0.4.1
defusedxml==0.6.0
Flask==1.1.1
Flask-AppBuilder==2.1.6
Flask-Babel==0.12.2
Flask-JWT-Extended==3.20.0
Flask-Login==0.4.1
Flask-OpenID==1.2.5
Flask-SQLAlchemy==2.4.0
Flask-WTF==0.14.2
itsdangerous==1.1.0
Jinja2==2.10.1
jsonschema==3.0.1
MarkupSafe==1.1.1
marshmallow==2.19.5
marshmallow-enum==1.4.1
marshmallow-sqlalchemy==0.17.0
mysqlclient==1.4.2.post1
prison==0.1.2
PyJWT==1.7.1
pyrsistent==0.15.3
python-dateutil==2.8.0
python3-openid==3.1.0
pytz==2019.1
PyYAML==5.1.1
six==1.12.0
SQLAlchemy==1.3.5
SQLAlchemy-Utils==0.34.0
Werkzeug==0.15.4
WTForms==2.2.1

This is with Python 3.6.8

Regards, Nachtkinder

[dpgaspar]

Sorry for the delay, I see what you mean. I'll make some tests and get back at this