This is the sixth day of my participation in the August More text Challenge. For details, see:August is more challenging

  • Although there are a number of mature questionnaire products in the market, these questionnaire products may not meet our expectations due to various reasons (security, service customization). In the network culture festival held by our school, our department, as the sponsor, needs to collect and make statistics on the results of various activities and competitions. The questionnaire involves a lot of privacy information, and there may be some security problems when using other people’s products. In addition, there will also be many large files in the questionnaire, and the existing questionnaire platform is too expensive for us (the main reason). Therefore, we set out to develop our own department’s questionnaire system.

1. Questionnaire design

Due to the variety of forms of questionnaires, we need to be able to achieve the customization of questionnaires. Therefore, it is impossible to define the questionnaire only by a single table in order to fully realize the function of the questionnaire.

The solution we adopted was to store the public information of the questionnaire in a table, such as: title, introduction, creation time, etc.

In addition, use a table record option, through a Model field, to record the question type and use a one-to-many association to establish a relationship with the questionnaire.

Here’s an implementation using flask-SQLAlchemy:

class Wj(db.Model) :
    "" "questionnaire "" "
    __tablename__ = 'wjs'
    id = db.Column(db.Integer(), primary_key=True)
    title = db.Column(db.String(255))               # titles
    description = db.Column(db.String(255))         # introduction
    isDelete = db.Column(db.Integer, default=0)     # Whether to delete
    isClose = db.Column(db.Integer, default=0)      # Whether it is closed
    confirmed_at = db.Column(db.DateTime, index=True, default=datetime.datetime.now())  # Create time
    options = db.relationship('Option', backref='wj', lazy='dynamic')


class Option(db.Model) :
    "" "options "" "
    __tablename__ = 'options'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(255))               # titles
    model = db.Column(db.Integer, nullable=False)   Select mode (fill in blank 1, radio 2, Multiple 3, file 4)
    description = db.Column(db.String(255))         # Fill in the blank, file upload: Note, select: Option text - use; Segmentation.
    wj_id = db.Column(db.Integer, db.ForeignKey('wjs.id'))
Copy the code

Two, answer storage

The storage problem of questionnaire information is solved. Another problem is how to store the answer sheets. The storage problems are as follows:

  1. The format is uncertain, could be text, could be a file
  2. The length is uncertain, and the number of fields is determined by the file.

These features seem hard to do with A SQL database (I never figured out ≡(▔ : ▔)≡) so simply give up on the idea of storing your answers in mysql. After some research, I feel that using MongoDB generated as noSQL is more in line with the needs.

MongoDB, written by C++ language, is an open source database system based on distributed file storage. MongoDB stores data as a document, and the data structure consists of key=>value pairs. MongoDB documents are similar to JSON objects. Field values can contain other documents, arrays, and arrays of documents.

First, MongoDB is a non-relational database and has no problem with an indefinite number of fields.

In addition, MongoDB operates in ORM much like SQL databases, and the cost of learning is relatively low, making it suitable for rapid development projects (DDL is coming, after all).

The library I’m using is Flask_PyMongo:

mongo.db.uploads.insert({
    "id": wj.id."ip": ip,
    "user_id": user_id,
    "options": options
})
Copy the code

I can insert a document into MongoDB using the insert function (uploads is my library name).

uploads = mongo.db.uploads.find({'id': wj.id})
Copy the code

The find operation uses the find function, which is also intuitive.

Want to learn other operations can go to read more professional articles, here only to do a simple train of thought guide.

3. File upload

As mentioned in the beginning, our activities need to upload large files, which is also one of the purposes of our self-built files.

Implementation ideas:

1. Generate a unique ID for the file, divide the file into several blocks according to the specified block size, label the file in sequence, and count the number of blocks.

2. Upload the file block by block until the background determines that the number of uploads is the same as the number of blocks.

3. Merge blocks and splice the original large file.

4. Delete blocks.

And then the front end tells us that WebUploader can do this.

The specific idea is consistent with the above, but the judgment of the block upload completed by the front end of another request for an interface implementation (really good).

Mysql > add File table to CDN server

# Upload fragment
@main.route('/file', methods=['POST'])
def file_part() :
    task = request.form.get('task_id')  # Get the unique identifier of the file
    chunk = request.form.get('chunk'.0)  # Get the sequence number of this shard in all shards

    upload_file = request.files['file']
    upload_file.save(f'./static/slice/{task}{chunk}' )  # Save fragment
    return jsonify({"status": "200"."msg": "Submission successful"})

# Merge fragments
@main.route('/file', methods=['GET'])
def file_all() :
    target_filename = request.args.get('filename')  Get the name of the file to be uploaded
    task = request.args.get('task_id')  # Get the unique identifier of the file
    chunk = 0  # Fragment number
    with open(f'./static/save/{task}'.'wb') as f:  # Create new file
        while True:
            try:
                filename = './static/slice/%s%d' % (task, chunk)
                source_file = open(filename, 'rb')  # Open each fragment in order
                f.write(source_file.read())  # read fragments and write to new files
                source_file.close()
            except Exception as e:
                break
            chunk += 1
            os.remove(filename)  # Delete fragment
    newFile = File(
        uid=task,       # File unique identifier
        title=target_filename
    )
    db.session.add(newFile)
    db.session.commit()
    return jsonify({"status": "200"."msg": "Submission successful"."uid": newFile.uid})
Copy the code

Four, details

It is impossible for a project to have only a few functions, and there are many details to be implemented slowly. For reference only, the requirements of specific projects are certainly different:

1. In the options section, you can perform some data verification to prevent illegal input by users.

2. The questionnaire shall be able to be closed and opened as required. Users will not be able to deliver the questionnaire after closing.

3. We need to provide a channel for questionnaire download. We use binary data to generate excel stream file to realize the function of questionnaire download.

4.JWT, questionnaire paging query and other operations.

.