use sqlite as database

[rishabhKalakoti]

fixes #118 TODO

• [x] installed peewee & setup database
• [x] setup user table
• [x] setup session table
• [x] setup submissions table
• [x] setup contest & questions table

[rishabhKalakoti]

Is this implementation okay? let's finalize for one table and then we will do it similarly for other tables

[rishabhKalakoti]

So... I left contest and question database as work has to be done with that in #119 There are a few things I need help in... Defining a primary/ unique key in Submission (username + time should be the joint key) Relationship between tables: Should we implement it? And for rankings I had to convert the recordset into list first in a seperate statement, I could not do that and enumerate (for ranks) in the same statement.

[theSage21]

Defining a primary/ unique key in Submission (username + time should be the joint key)

The indexing docs might be of help

Relationship between tables: Should we implement it?

What relations do you have in mind? I think linking between question,contest,submission,user should happen.

And for rankings I had to convert the recordset into list first in a seperate statement, I could not do that and enumerate (for ranks) in the same statement.

That's fine for now. Things will settle in a while. Let's get to a point where we can stop using shelve and completely switch to peewee.

[rishabhKalakoti]

Database fully implemented (almost). Tasks left:

• Make relationships
• Correct the query for ranking

[rishabhKalakoti]

I got some problems implementing relationships, can't really understand it. Usually foreign keys are a field of another table, but here, the foreign key is a class And, I am unable to implement insert operation with foreign keys, I went thorough the documentation, but can't get the hang of it yet :)

[theSage21]

so foreign keys are a exactly what you mentioned. Simply numbers appearing in another table. But that is at the database level (mysql/sqlite). Right now we are using peewee which is an ORM. So in our case:

class Base(pw.Model):
    class Meta:
        database = db
class Question(Base):
    instructions = pw.CharField()

class Contest(Base):
    title = pw.CharField()

class QuesInContest(Base):
    question = pw.ForeignKeyField(Question)
    contest = pw.ForeignKeyField(Contest)

Peewee handles the index-integer-autoincrement thing for us. We just give it the class.

Now to insert things into QuesInContest table:

contest = Contest.create(...)
ques = Question.create(...)
q_in_c = QuesInContest.create(question=ques, contest=contest)

# or you could supply IDs ( for example when ID is a parameter in an API)
qid = int(ques.id)
cid = int(contest.id)
q_in_c = QuesInContest.create(question=qid, contest=cid)

[theSage21]

I'd prefer to keep master in a working state always so if you could wrap up the relations thing also in this PR it would be wonderful!

[rishabhKalakoti]

I'll give it a shot :)

[rishabhKalakoti]

Should I implement the question table in this too?

[theSage21]

It'll be nice if this was a full system 😊

On Tue 21 May, 2019, 08:47 Rishabh Kalakoti, notifications@github.com wrote:

Should I implement the question table in this too?

— You are receiving this because your review was requested. Reply to this email directly, view it on GitHub https://github.com/PyJaipur/PyJudge/pull/126?email_source=notifications&email_token=AB2WHUMEBYZSNWITRGI5DATPWNSV7A5CNFSM4HN3X7N2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODV2UGBQ#issuecomment-494224134, or mute the thread https://github.com/notifications/unsubscribe-auth/AB2WHUMZL3XM2LLLZSM6K5DPWNSV7ANCNFSM4HN3X7NQ .

[rishabhKalakoti]

The database system looks ready to me now. Relationships implemented. (Bug in ranking for incorrect scoring still there) @theSage21 you can have a look now :)

[theSage21]

This is starting to make sense! :+1: