Git Product home page Git Product logo

usgo-sql-schemas's People

Contributors

michaelhiiva avatar vash3g avatar

Stargazers

avatar avatar avatar avatar

Watchers

avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar avatar

Forkers

michaelhiiva vash3g codeguru42

usgo-sql-schemas's Issues

Update Database Schemas to meet Relational Database Normal Forms

This will be a future change that will have a significant impact across all USGO applications. However, updating our databases to meet Normal Forms will make future projects far easier, as well as having a beneficial impact on performance.
For example, there are two player columns in the game table, instead there should be a player_game table that sits between player and game. Joining players to games more than once is bad style, and will lead to worse performance.
The first step will be planning the structure of the new database. Second, we will need scripts that will copy data from the old, to the new database. Step three will be updating existing USGO applications to match the new structure.
I advise the creation of views that mimic existing table structure as a way to bridge the gap, allowing for modest changes to existing applications.
Best would be to have stored procedures written that will better use the performance features of relational databases, which can then pass the data back to the front end via API's or direct calls through a DB connection.
This is a project I intend to champion, although it is not a priority at this time.

password_plaintext considered harmful

The members table has this field. It probably shouldn't. My recommendations:

  1. make sure it isn't used anywhere
  2. drop the field from the table
  3. update the schema
  4. profit

Add DATETIME Support for Tables

Summary

There is a combination of DATE and DATETIME used within the schema. In order to support time zones within the AGAGD usgo/agagd#131, one needs DATETIME; however, there are several instances of DATE within the database which need change in order to support Django's datetimes and time zones. Problematic, when trying to use django-tables2's DateTimeColumn, as it results in a error:

app_1  | Internal Server Error: /
app_1  | Traceback (most recent call last):
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/core/handlers/exception.py", line 41, in inner
app_1  |     response = get_response(request)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/core/handlers/base.py", line 249, in _legacy_get_response
app_1  |     response = self._get_response(request)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/core/handlers/base.py", line 187, in _get_response
app_1  |     response = self.process_exception_by_middleware(e, request)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/core/handlers/base.py", line 185, in _get_response
app_1  |     response = wrapped_callback(request, *callback_args, **callback_kwargs)
app_1  |   File "./agagd_core/views/core.py", line 44, in index
app_1  |     'tournaments': t_table,
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/shortcuts.py", line 30, in render
app_1  |     content = loader.render_to_string(template_name, context, request, using=using)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/loader.py", line 68, in render_to_string
app_1  |     return template.render(context, request)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/backends/django.py", line 66, in render
app_1  |     return self.template.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 207, in render
app_1  |     return self._render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 199, in _render
app_1  |     return self.nodelist.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 990, in render
app_1  |     bit = node.render_annotated(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 957, in render_annotated
app_1  |     return self.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/loader_tags.py", line 177, in render
app_1  |     return compiled_parent._render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 199, in _render
app_1  |     return self.nodelist.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 990, in render
app_1  |     bit = node.render_annotated(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 957, in render_annotated
app_1  |     return self.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/loader_tags.py", line 72, in render
app_1  |     result = block.nodelist.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 990, in render
app_1  |     bit = node.render_annotated(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 957, in render_annotated
app_1  |     return self.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/defaulttags.py", line 322, in render
app_1  |     return nodelist.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 990, in render
app_1  |     bit = node.render_annotated(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 957, in render_annotated
app_1  |     return self.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django_tables2/templatetags/django_tables2.py", line 169, in render
app_1  |     return template.render(context={'table': table}, request=request)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/backends/django.py", line 66, in render
app_1  |     return self.template.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 207, in render
app_1  |     return self._render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 199, in _render
app_1  |     return self.nodelist.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 990, in render
app_1  |     bit = node.render_annotated(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 957, in render_annotated
app_1  |     return self.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/loader_tags.py", line 63, in render
app_1  |     result = self.nodelist.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 990, in render
app_1  |     bit = node.render_annotated(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 957, in render_annotated
app_1  |     return self.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/loader_tags.py", line 63, in render
app_1  |     result = self.nodelist.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 990, in render
app_1  |     bit = node.render_annotated(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 957, in render_annotated
app_1  |     return self.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/loader_tags.py", line 63, in render
app_1  |     result = self.nodelist.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 990, in render
app_1  |     bit = node.render_annotated(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/base.py", line 957, in render_annotated
app_1  |     return self.render(context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/template/defaulttags.py", line 173, in render
app_1  |     len_values = len(values)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django_tables2/rows.py", line 340, in __len__
app_1  |     length = len(self.data)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/db/models/query.py", line 232, in __len__
app_1  |     self._fetch_all()
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/db/models/query.py", line 1121, in _fetch_all
app_1  |     self._result_cache = list(self._iterable_class(self))
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/db/models/query.py", line 62, in __iter__
app_1  |     for row in compiler.results_iter(results):
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 847, in results_iter
app_1  |     row = self.apply_converters(row, converters)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 832, in apply_converters
app_1  |     value = converter(value, expression, self.connection, self.query.context)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/db/backends/mysql/operations.py", line 239, in convert_datetimefield_value
app_1  |     value = timezone.make_aware(value, self.connection.timezone)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/django/utils/timezone.py", line 285, in make_aware
app_1  |     return timezone.localize(value, is_dst=is_dst)
app_1  |   File "/home/django/.local/lib/python3.7/site-packages/pytz/__init__.py", line 244, in localize
app_1  |     if dt.tzinfo is not None:
app_1  | AttributeError: 'datetime.date' object has no attribute 'tzinfo'

schema.sql - DATE

95:    `Game_Date` DATE NOT NULL,
110:    `Elab_Date` DATE NOT NULL,
116:    `legacy_updated` DATE DEFAULT NULL,
117:    `legacy_web_updated` DATE DEFAULT NULL,
125:    `renewal_due` DATE DEFAULT NULL,
126:    `join_date` DATE DEFAULT NULL,
127:    `dob` DATE DEFAULT NULL,
261:    `Elab_Date` DATE NOT NULL,
263:    `MExp` DATE NOT NULL DEFAULT '1900-01-01',
296:    `Elab_Date` DATE DEFAULT NULL,
337:    `entrydate` DATE DEFAULT NULL
343:    `Tournament_Date` DATE NOT NULL,
350:    `Elab_Date` DATE NOT NULL,

Expected Behaviour

DATE columns are converted to DATETIME columns

Notes

  • Not know what consequence a change such as this would have on the various applications which use AGAGD's database schema.

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.