DailyNotes

DailyNotes copied to clipboard

1. Tested in mysql and sqlite.
2. I added var length and changed type of data key in table note as TEXT
3. Need to review if the length is reasonable.

Everything looks good to me as long as it doesn't mess up sqlite support.

Try yourself, I did try mysql and sqlite

Yeah, I'll try and check later today. Thanks for putting the work in!

Hang on, there is a issue when trying to save a note in mysql, why the data is save as binary? We could have a base64 encode to convert it as ASCIIs

INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> a477f34dbaa4, Initial config
INFO  [alembic.runtime.migration] Running upgrade a477f34dbaa4 -> 9ca5901af374, cleanup
INFO  [alembic.runtime.migration] Running upgrade 9ca5901af374 -> 7bd1ee1840ca, Meta Table
INFO  [alembic.runtime.migration] Running upgrade 7bd1ee1840ca -> c440f31aff28, Add named unique constraint name for note title
INFO  [alembic.runtime.migration] Running upgrade c440f31aff28 -> 9bd71ed6ccff, Remove unique constraint name for note title
[2021-05-11 02:45:54 +0000] [1] [INFO] Starting gunicorn 20.1.0
[2021-05-11 02:45:54 +0000] [1] [INFO] Listening at: http://0.0.0.0:5000 (1)
[2021-05-11 02:45:54 +0000] [1] [INFO] Using worker: sync
[2021-05-11 02:45:54 +0000] [14] [INFO] Booting worker with pid: 14
/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py:1099: SAWarning: TypeDecorator GUID() will not produce a cache key because the ``cache_ok`` flag is not set to True.  Set this flag to True if this type object's state is safe to use in a cache key, or False to disable this warning.
  util.warn(
[2021-05-11 02:46:30,943] ERROR in app: Exception on /api/save_day [PUT]
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.DataError: (1366, "Incorrect string value: '\\xD9\\x04\\xBF{\\xA9E...' for column `dailynotes`.`note`.`data` at row 1")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python3.8/site-packages/flask_jwt_extended/view_decorators.py", line 121, in decorator
    return fn(*args, **kwargs)
  File "/app/app/routes.py", line 83, in save_day
    db.session.flush()
  File "<string>", line 2, in flush
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3262, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3402, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3362, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 453, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 627, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 242, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1219, in _emit_insert_statements
    result = connection._execute_20(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1582, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 320, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1451, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1813, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1994, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.DataError: (pymysql.err.DataError) (1366, "Incorrect string value: '\\xD9\\x04\\xBF{\\xA9E...' for column `dailynotes`.`note`.`data` at row 1")
[SQL: INSERT INTO note (uuid, user_id, data, title, is_date) VALUES (%(uuid)s, %(user_id)s, %(data)s, %(title)s, %(is_date)s)]
[parameters: {'uuid': '4b9300ef882b4cb2a777ef13cdadbf45', 'user_id': 'ca777ecb7072443185dcb5c1e161893d', 'data': b'V\xd9\x04\xbf{\xa9E\xf7W\xb1L>v_\x10s\xf5\xb8q\xec\x10\xaeD\xabVc\xd7\xa4\xa2\x9a|\xb6\xa9c\xcf6\x82', 'title': b'F\xb6\\\x118W[4\xb3\x8b', 'is_date': 1}]
(Background on this error at: http://sqlalche.me/e/14/9h9h)
[2021-05-11 02:47:19,926] ERROR in app: Exception on /api/save_day [PUT]
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.DataError: (1366, "Incorrect string value: '\\x93\\x00\\x18\\x12\\x8F\\xAE...' for column `dailynotes`.`note`.`data` at row 1")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python3.8/site-packages/flask_jwt_extended/view_decorators.py", line 121, in decorator
    return fn(*args, **kwargs)
  File "/app/app/routes.py", line 83, in save_day
    db.session.flush()
  File "<string>", line 2, in flush
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3262, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3402, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3362, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 453, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 627, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 242, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1219, in _emit_insert_statements
    result = connection._execute_20(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1582, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 320, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1451, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1813, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1994, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.8/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.DataError: (pymysql.err.DataError) (1366, "Incorrect string value: '\\x93\\x00\\x18\\x12\\x8F\\xAE...' for column `dailynotes`.`note`.`data` at row 1")
[SQL: INSERT INTO note (uuid, user_id, data, title, is_date) VALUES (%(uuid)s, %(user_id)s, %(data)s, %(title)s, %(is_date)s)]
[parameters: {'uuid': 'b952eae70dc446099a7eac1c1ce1f7d9', 'user_id': 'ca777ecb7072443185dcb5c1e161893d', 'data': b'[\x93\x00\x18\x12\x8f\xaeC\x86\x9f\x9b\xe9\xef\x19(\x0f\x95\xf0\x10r\xb5\xaa\xb1-?\x13v\xe1\x94\xd0&:$\xad\x08\x9c\xf6\xcfX\x1eR\xf8\\\xdc\x9e\xe4\x ... (1107 characters truncated) ... \\P\x04\x83\xcf\x8a\xb7*\x91\x91y\xa9\x08A\xb2^v\xb5}\x82B\x1a"g\n\r\x89\xdd,\xab4x\x9dB+\x04\xadi\xf6\xa3%?#\x07e\x0c(g\xcaz\xf8\xeb\x0b\xa7\xdb\xcd', 'title': b'F\xb6\\\x118W[4\xb3\x8b', 'is_date': 1}]
(Background on this error at: http://sqlalche.me/e/14/9h9h)