Consider Moving to a Code First Database

[narthollis]

For long term maintainability it would be worth looking at moving from the current set of pre-backed DB functions and SQL create script to a Code First database solution.

This would probably be SQLAlchamy or Django (a quick google suggests it's not too hard to utilise just the Django ORM)

The main benefits this code-first approach has are:

• Database migrations (when you make a change to one of your tables, you can generate a migration from the old to the new)
• DB Engine abstraction (it is not a configuration concern as to what database engine (server) you wish to use)

[TheDarkLordSano]

I think it is time to move forward with this. I'm going to start reading up on Django ORM.

I have already downloaded the package to the bot. And I believe migrated the data successfully.

[narthollis]

When I get home (or tomorrow) I'll throw up the db Task (for sharing the connection) I prepared last time I looked at this briefly.

On 8 Jul. 2017 12:44, "TheDarkLordSano" notifications@github.com wrote:

I think it is time to move forward with this. I'm going to start reading up on Django ORM.

I have already downloaded the package to the bot. And I believe migrated the data successfully.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/TheDarkLordSano/HFYBeetusBot/issues/14#issuecomment-313829895, or mute the thread https://github.com/notifications/unsubscribe-auth/AAEIlJPh1gV_vA00bvtYGKGE8FbH-RTnks5sLvQKgaJpZM4NohW0 .

[narthollis]

diff --git a/hfysubs/celery.py b/hfysubs/celery.py
index 0a17b97..2d9c173 100644
--- a/hfysubs/celery.py
+++ b/hfysubs/celery.py
@@ -29,5 +29,20 @@ class RedditTask(Task):
             self._reddit = make_reddit()
         return self._reddit

+
+class DatabaseTask(Task):
+    _database = None
+
+    @property
+    def database(self):
+        if self._database is None:
+            from .database import Database
+            self._database = Database
+        return self._database
+
+
+class DatabaseRedditTask(RedditTask, DatabaseTask):
+    pass
+
 if __name__ == '__main__':
     app.start()

The rest of where I had gotten to can be found at code-first-db (please note this is 100% untested and untried - it was just what I had thrown together a while ago)

[narthollis]

Also, I think that part of the problem with the database is the SQLite has some limitations with multiple processes writing to the same DB at the same time, so it is probably worth looking at shifting to a database server, eg. MySQL or PostgreSQL.

[TheDarkLordSano]

I've the bones of the PostgreSQL installed on the bot. I'm taking "baby" steps here trying to wrap my head fully around how Django works.

[narthollis]

Fair enough. If you have any questions I am on the IRC. (I am always connected to just send a PM and I'll reply when I can)

On 9 Jul. 2017 12:31, "TheDarkLordSano" notifications@github.com wrote:

I've the bones of the PostgreSQL installed on the bot. I'm taking "baby" steps here trying to wrap my head fully around how Django works.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/TheDarkLordSano/HFYBeetusBot/issues/14#issuecomment-313894397, or mute the thread https://github.com/notifications/unsubscribe-auth/AAEIlEP1G8K1t29FS2K5EP2xrA5F9a-oks5sMEJxgaJpZM4NohW0 .

[TheDarkLordSano]

data migration completed into postgresql. Errors occurred along the way which forced me to change table headers. (I know bad thing to-do mid project)

TABLE subscriptions (subscription_id, subscribed_to, subscriber, subreddit, subscribe_date) subscription_id is auto-increment.

TABLE repliedto (id, reddit_id,author,replied_id,timest,sub) id is auto-increment. author was originally user. postgresql hated that. timest was originally timestamp. changed that to avoid errors. timest will default to current timestamp at data entry.

TABLE notifications has not been used since moving over to celery and is no-longer of any use and has been dropped. (data still available because backups.)

[narthollis]

Eh, Names change all the time, it's one of the reasons for using a Code-First database. (Then you can generate automatic migrations to apply the changes to the DB)

The tables look good for what is needed.

[TheDarkLordSano]

Good news and bad news. Good news is the new Pi came. bad news the OS fragged itself when attempting to update the Distro. Data is secure... I've just got to spend the rest of the week flashing the OS and following that up with installing all the modules. Yay...

[TheDarkLordSano]

This is my pass at all the Django setup. The only thing left is updating the queries and verifying queries work.

[narthollis]

These models and the framework code all looks good.