From 0850ea13c19d4f2a382fd7d4bcf6333f446fa68b Mon Sep 17 00:00:00 2001 From: Timotej Lazar Date: Mon, 12 Oct 2015 12:02:43 +0200 Subject: Drop is_visible column from problem table --- TODO | 3 --- db/create.sql | 1 - db/models.py | 4 ++-- scripts/sqlite_to_pg.py | 2 +- 4 files changed, 3 insertions(+), 7 deletions(-) diff --git a/TODO b/TODO index ee56361..da6cf42 100644 --- a/TODO +++ b/TODO @@ -3,6 +3,3 @@ Database - drop 'name' column from problem / problem_group / language tables and move values to corresponding {en,sl}.py files -- drop 'is_visible' column from problem table (or 'visible' attribute from - common.py files) -- maybe: drop 'group' attribute from common.py files? diff --git a/db/create.sql b/db/create.sql index 57c3647..bb7ee35 100644 --- a/db/create.sql +++ b/db/create.sql @@ -68,7 +68,6 @@ create table problem ( problem_group_id integer not null, name varchar(100) not null, identifier varchar(100) not null, - is_visible bool not null default true, constraint problem_pk primary key (id), constraint problem_fk1 foreign key (language_id) references language (id) on delete no action on update cascade, constraint problem_fk2 foreign key (problem_group_id) references problem_group (id) on delete no action on update cascade, diff --git a/db/models.py b/db/models.py index 4eda2ba..b8d64a1 100644 --- a/db/models.py +++ b/db/models.py @@ -27,8 +27,8 @@ class CodeqUser(collections.namedtuple('CodeqUser', ['id', 'username', 'password def solved_problems(user_id, language): return _run_sql('select g.identifier, p.identifier from solution s inner join problem p on p.id = s.problem_id inner join problem_group g on g.id = p.problem_group_id inner join language l on l.id = p.language_id where s.codeq_user_id = %s and l.identifier = %s and s.done = True', (user_id, language), fetch_one=False) -class Problem(collections.namedtuple('Problem', ['id', 'language_id', 'problem_group_id', 'name', 'identifier', 'is_visible'])): - __sql_prefix = 'select id, language_id, problem_group_id, name, identifier, is_visible from problem' +class Problem(collections.namedtuple('Problem', ['id', 'language_id', 'problem_group_id', 'name', 'identifier'])): + __sql_prefix = 'select id, language_id, problem_group_id, name, identifier from problem' @staticmethod def get(**kwargs): diff --git a/scripts/sqlite_to_pg.py b/scripts/sqlite_to_pg.py index a5889d6..29e1317 100644 --- a/scripts/sqlite_to_pg.py +++ b/scripts/sqlite_to_pg.py @@ -176,7 +176,7 @@ if __name__ == '__main__': log('Copying tutor_problem -> problem') copy_table( 'select id, group_id, name, visible from tutor_problem', - 'insert into problem (id, language_id, problem_group_id, name, is_visible, identifier) values (%s, ' + str(_language_prolog_id) + ', %s, %s, %s, %s)', + 'insert into problem (id, language_id, problem_group_id, name, identifier) values (%s, ' + str(_language_prolog_id) + ', %s, %s, %s)', convert_problem, None, 'problem_id_seq') log('Copying tutor_attempt -> solution') -- cgit v1.2.1 From fe0002e7fd5a3631d6a7cd8b0a3cdbd2b55d7aa1 Mon Sep 17 00:00:00 2001 From: Timotej Lazar Date: Mon, 12 Oct 2015 12:57:27 +0200 Subject: Remove unused server.problems.list_problems --- server/problems.py | 31 ------------------------------- 1 file changed, 31 deletions(-) diff --git a/server/problems.py b/server/problems.py index bc124ca..c63d923 100644 --- a/server/problems.py +++ b/server/problems.py @@ -82,34 +82,3 @@ def solutions_for_problems(language, tuples): except AttributeError as me: pass return '\n'.join(solutions) - -def list_problems(language): - conn = get_connection() - try: - cur = conn.cursor() - try: - cur.arraysize = 1000 - cur.execute('select l.identifier, l.name, g.identifier, g.name, p.identifier, p.name from problem p inner join language l on l.id = p.language_id and l.identifier = \''+language+'\' inner join problem_group g on g.id = p.problem_group_id order by l.identifier, g.identifier, p.identifier') - result = [] - previous_language = '' - previous_group = '' - current_sublist = None - row = cur.fetchone() - while row: - current_language = row[0] - current_group = row[2] - if previous_language != current_language or previous_group != current_group: - current_sublist = [] - result.append({'identifier': {'language': current_language, 'group': current_group}, - 'name': {'language': row[1], 'group': row[3]}, - 'problems': current_sublist}) - previous_group = current_group - previous_language = current_language - current_sublist.append({'identifier': row[4], 'name': row[5]}) - row = cur.fetchone() - return result - finally: - cur.close() - finally: - conn.commit() - return_connection(conn) -- cgit v1.2.1 From c4b71579939c9461379472ee36675ff6ecb1b417 Mon Sep 17 00:00:00 2001 From: Timotej Lazar Date: Mon, 12 Oct 2015 13:04:19 +0200 Subject: Drop name column from language/group/problem tables --- db/create.sql | 6 ------ db/models.py | 5 ++--- scripts/build_web_resources.py | 5 ++--- scripts/sqlite_to_pg.py | 10 +++++----- 4 files changed, 9 insertions(+), 17 deletions(-) diff --git a/db/create.sql b/db/create.sql index bb7ee35..4abdb33 100644 --- a/db/create.sql +++ b/db/create.sql @@ -46,19 +46,15 @@ create table user_in_group ( create table language ( id integer not null default nextval('language_id_seq'), - name varchar(30) not null, identifier varchar(30) not null, constraint language_pk primary key (id), - constraint language_uq1 unique (name), constraint language_uq2 unique (identifier) ); create table problem_group ( id integer not null default nextval('problem_group_id_seq'), - name varchar(100) not null, identifier varchar(100) not null, constraint group_pk primary key (id), - constraint group_uq1 unique (name), constraint group_uq2 unique (identifier) ); @@ -66,12 +62,10 @@ create table problem ( id integer not null default nextval('problem_id_seq'), language_id integer not null, problem_group_id integer not null, - name varchar(100) not null, identifier varchar(100) not null, constraint problem_pk primary key (id), constraint problem_fk1 foreign key (language_id) references language (id) on delete no action on update cascade, constraint problem_fk2 foreign key (problem_group_id) references problem_group (id) on delete no action on update cascade, - constraint problem_uq1 unique (language_id, problem_group_id, name), constraint problem_uq2 unique (language_id, problem_group_id, identifier) ); diff --git a/db/models.py b/db/models.py index b8d64a1..17f9681 100644 --- a/db/models.py +++ b/db/models.py @@ -7,7 +7,6 @@ from . import get_connection, return_connection __all__ = ['CodeqUser', 'Solution'] - class CodeqUser(collections.namedtuple('CodeqUser', ['id', 'username', 'password', 'name', 'email', 'is_admin', 'is_active', 'date_joined', 'last_login'])): __sql_prefix = 'select id, username, password, name, email, is_admin, is_active, date_joined, last_login from codeq_user' @@ -27,8 +26,8 @@ class CodeqUser(collections.namedtuple('CodeqUser', ['id', 'username', 'password def solved_problems(user_id, language): return _run_sql('select g.identifier, p.identifier from solution s inner join problem p on p.id = s.problem_id inner join problem_group g on g.id = p.problem_group_id inner join language l on l.id = p.language_id where s.codeq_user_id = %s and l.identifier = %s and s.done = True', (user_id, language), fetch_one=False) -class Problem(collections.namedtuple('Problem', ['id', 'language_id', 'problem_group_id', 'name', 'identifier'])): - __sql_prefix = 'select id, language_id, problem_group_id, name, identifier from problem' +class Problem(collections.namedtuple('Problem', ['id', 'language_id', 'problem_group_id', 'identifier'])): + __sql_prefix = 'select id, language_id, problem_group_id, identifier from problem' @staticmethod def get(**kwargs): diff --git a/scripts/build_web_resources.py b/scripts/build_web_resources.py index e581fd0..d109eb0 100644 --- a/scripts/build_web_resources.py +++ b/scripts/build_web_resources.py @@ -186,7 +186,7 @@ try: lang_data['translations'] = load_translation_data(lang_identifier, language_props) copy_web_resources(lang_identifier, [lang_identifier]) db_add('language', lang_identifier, - {'id': lang_data['id'], 'name': lang_identifier, 'identifier': lang_identifier}) + {'id': lang_data['id'], 'identifier': lang_identifier}) groups_path = os.path.join(lang_path, 'problems') for group_identifier in os.listdir(groups_path): @@ -205,7 +205,7 @@ try: group_data['problems'] = [] group_data['translations'] = load_translation_data(group_package, group_props) db_add('problem_group', group_identifier, - {'id': group_data['id'], 'name': group_identifier, 'identifier': group_identifier}) + {'id': group_data['id'], 'identifier': group_identifier}) for problem_identifier in os.listdir(group_path): problem_path = os.path.join(group_path, problem_identifier) @@ -250,7 +250,6 @@ try: 'id': problem_data['id'], 'language_id': lang_data['id'], 'problem_group_id': group_data['id'], - 'name': problem_identifier, 'identifier': problem_identifier }) diff --git a/scripts/sqlite_to_pg.py b/scripts/sqlite_to_pg.py index 29e1317..b148be6 100644 --- a/scripts/sqlite_to_pg.py +++ b/scripts/sqlite_to_pg.py @@ -130,10 +130,10 @@ def convert_attempt(row): return (row[0], row[1] != 0, row[2], row[3], row[4], '[' + ','.join(new_trace) + ']'), None def convert_problem_group(row): - return (row[0], row[1], filenamefy(row[1])), None + return (row[0], filenamefy(row[1])), None def convert_problem(row): - return (row[0], row[1], row[2], row[3] != 0, filenamefy(row[2])), None + return (row[0], row[1], filenamefy(row[2])), None if __name__ == '__main__': sqc = sqlite3.connect('db.sqlite3') @@ -144,7 +144,7 @@ if __name__ == '__main__': log('Creating language prolog') cur = pgc.cursor() try: - cur.execute("insert into language (name, identifier) values ('Prolog', 'prolog') returning id") + cur.execute("insert into language (identifier) values ('prolog') returning id") _language_prolog_id = cur.fetchone()[0] finally: cur.close() @@ -170,13 +170,13 @@ if __name__ == '__main__': log('Copying tutor_group -> problem_group') copy_table( 'select id, name from tutor_group', - 'insert into problem_group (id, name, identifier) values (%s, %s, %s)', + 'insert into problem_group (id, identifier) values (%s, %s)', convert_problem_group, None, 'problem_group_id_seq') log('Copying tutor_problem -> problem') copy_table( 'select id, group_id, name, visible from tutor_problem', - 'insert into problem (id, language_id, problem_group_id, name, identifier) values (%s, ' + str(_language_prolog_id) + ', %s, %s, %s)', + 'insert into problem (id, language_id, problem_group_id, identifier) values (%s, ' + str(_language_prolog_id) + ', %s, %s)', convert_problem, None, 'problem_id_seq') log('Copying tutor_attempt -> solution') -- cgit v1.2.1 From f7e6d93a890ca74281fffb8e9d59864073c30497 Mon Sep 17 00:00:00 2001 From: Timotej Lazar Date: Mon, 12 Oct 2015 13:06:20 +0200 Subject: Update TODO. It is now empty. We can go home. --- TODO | 5 ----- 1 file changed, 5 deletions(-) delete mode 100644 TODO diff --git a/TODO b/TODO deleted file mode 100644 index da6cf42..0000000 --- a/TODO +++ /dev/null @@ -1,5 +0,0 @@ -Database -======== - -- drop 'name' column from problem / problem_group / language tables and move - values to corresponding {en,sl}.py files -- cgit v1.2.1