<commit_before>#!/usr/bin/env python

# <NAME>
# July 2016
#
# The MIT License (MIT)
#
# Copyright (c) 2016 Personal Robots Group
# 
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
# 
# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.
# 
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.

import sqlite3 # store game info and personalization

def ss_init_db():
    """ Initalize database with tables for tracking question responses
    in the social stories game.
    """
    # get connection to database
    conn = sqlite3.connect("socialstories.db")
    cursor = conn.cursor()

    # create tables
    cursor.execute('''CREATE TABLE stories_played (
            id          integer     PRIMARY KEY,
            date        date        NOT NULL    default current_date,
            time        timestamp   NOT NULL    default current_timestamp,
            participant text        NOT NULL,
            session     integer     NOT NULL,
            level       integer     NOT NULL,
            story       text        NOT NULL
            )''')

    cursor.execute('''CREATE TABLE questions (
            stories_played_id   integer NOT NULL,
            id                  integer PRIMARY KEY,
            question_num        integer NOT NULL,
            question_type       text    NOT NULL,
            target_emotion      text    NOT NULL,
            response            text,
            FOREIGN KEY(stories_played_id) REFERENCES stories_played(id)
            )''')

    cursor.execute('''CREATE TABLE emotions_in_question (
            questions_id    integer NOT NULL,
            emotion         text    NOT NULL,
            FOREIGN KEY(questions_id) REFERENCES questions(id)
            )''')
    
    conn.commit()
    conn.close()


if __name__ == '__main__':
    ss_init_db()<commit_msg>Update db table structure for story levels<commit_after>#!/usr/bin/env python

# <NAME>
# July 2016
#
# The MIT License (MIT)
#
# Copyright (c) 2016 Personal Robots Group
# 
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
# 
# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.
# 
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.

import sqlite3 # store game info and personalization

def ss_init_db():
    """ Initalize database with tables for tracking question responses
    in the social stories game.
    """
    # get connection to database
    conn = sqlite3.connect("socialstories.db")
    cursor = conn.cursor()

    # create tables
    cursor.execute(''' CREATE TABLE stories (
            id       integer     PRIMARY KEY,
            story_name  text        NOT NULL
            )''')

    cursor.execute(''' CREATE TABLE levels (
            level       integer     PRIMARY KEY,
            num_scenes  integer     NOT NULL,
            in_order    integer     NOT NULL
            )''')

    cursor.execute(''' CREATE TABLE graphics (
            story_id    integer     NOT NULL,
            level_id    integer     NOT NULL,
            scene_num   integer     NOT NULL,
            graphic     text        NOT NULL,
            FOREIGN KEY(story_id) REFERENCES stories(id),
            FOREIGN KEY(level_id) REFERENCES levels(level)
            )''')

    cursor.execute('''CREATE TABLE questions (
            id              integer PRIMARY KEY,
            stories_id      integer NOT NULL,
            question_num    integer NOT NULL,
            question_type   text    NOT NULL,
            target_response text    NOT NULL,
            FOREIGN KEY(stories_id) REFERENCES stories(id)
            )''')

    cursor.execute('''CREATE TABLE responses_in_question (
            questions_id    integer NOT NULL,
            response         text    NOT NULL,
            FOREIGN KEY(questions_id) REFERENCES questions(id)
            )''')

    cursor.execute('''CREATE TABLE responses (
            id                  integer PRIMARY KEY,
            stories_played_id   integer NOT NULL,
            questions_id        integer NOT NULL,
            response            text,
            FOREIGN KEY(stories_played_id) REFERENCES stories_played(id),
            FOREIGN KEY(questions_id) REFERENCES questions(id)
            )''')

    cursor.execute('''CREATE TABLE stories_played (
            id          integer     PRIMARY KEY,
            date        date        NOT NULL    default current_date,
            time        timestamp   NOT NULL    default current_timestamp,
            participant text        NOT NULL,
            session     integer     NOT NULL,
            level_id    integer     NOT NULL,
            story_id    text        NOT NULL,
            FOREIGN KEY(story_id) REFERENCES stories(id),
            FOREIGN KEY(level_id) REFERENCES levels(level)
            )''')
    
    conn.commit()
    conn.close()


if __name__ == '__main__':
    ss_init_db()
{'fraction_non_alphanumeric': 0.06168732990625945, 'fraction_numerical': 0.0030238887208950713, 'mean_word_length': 2.191027496382055, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3259586', 'n_tokens_mistral': 1706, 'n_tokens_neox': 1613, 'n_words': 680}