1

I would like to represent a folder structure in an Sqlite database with Python, where each table behaves as it's own folder and can be either the parent, child or sibling of another.

My current setup has them nested with delimiters like so:

  • ['folder::subfoldera']
  • ['folder::subfolderb']
  • ['folder2::subfolderc']
  • ['folder2::subfolderd']
  • ['last::example::to::represent::depth']

However I don't know how I would represent this nesting in python, when I read from the database, as simply nesting lists wouldn't work because that wouldn't be able to preserve the name of the folder, it would only show the things nested within it.

How should I go about a) storing the tables in the database b) representing the structure of them in Python?

aspiringMaster
  • 59
  • 6
  • You can find a nice [overview of the options here](https://stackoverflow.com/q/4048151/190597). Take note of the advantages and disadvantages of the various methods. It will help you clarify what issues you need to think about before choosing an option. – unutbu Feb 07 '19 at 03:58

1 Answers1

4

It'll be better to store folder structure in a single table, rather creating a table for each folder.

For example, such table could have structure like:

╔═══════════╦══════╦══════════════════════════════════╗
║  Column   ║ Type ║           Description            ║
╠═══════════╬══════╬══════════════════════════════════╣
║ id        ║ int  ║ Unique identifier of the folder  ║
║ parent_id ║ int  ║ id of the parent folder          ║
║ name      ║ text ║ Name of the folder               ║
║ mpath     ║ text ║ Materialized path of parent id's ║
╚═══════════╩══════╩══════════════════════════════════╝

Note about materialized path: it is optional, and could be added to make it faster to run queries like "Get all children of folder 123" without recusive calls.

So, let's pretend you have this folder structure:

/
├── home/
│   ├── aspiring-master
│   │    └── .bashrc
│   └── guest-user
└── var/
    ├── log/
    └── lib/

It could be presented in form of mentioned table like this:

╔════╦═══════════╦═══════════════════╦═══════════╗
║ id ║ parent_id ║       name        ║   mpath   ║
╠════╬═══════════╬═══════════════════╬═══════════╣
║  1 ║ null      ║ "/"               ║ "/"       ║
║  2 ║ 1         ║ "home"            ║ "/1/"     ║
║  3 ║ 2         ║ "aspiring-master" ║ "/1/2/"   ║
║  4 ║ 3         ║ ".bashrc"         ║ "/1/2/3/" ║
║  5 ║ 2         ║ "guest-user"      ║ "/1/2/"   ║
║  6 ║ 1         ║ "var"             ║ "/1/"     ║
║  7 ║ 6         ║ "log"             ║ "/1/6/"   ║
║  8 ║ 6         ║ "lib"             ║ "/1/6/"   ║
╚════╩═══════════╩═══════════════════╩═══════════╝

In python you can use some ORM, such as sqlAlchemy, and, in that case, your folder would be represented as a class, implementing Model:

class Folder(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('folder.id'),
        nullable=True)
    name = db.Column(db.String(128), unique=True, nullable=False)
    mpath = db.Column(db.String(255), unique=True, nullable=False)

Functionality to automatically create mpath value also could be placed in this class.

MihanEntalpo
  • 1,952
  • 2
  • 14
  • 31
  • That's exactly what I was looking for - I actually switched to using sqlalchemy as my ORM and have implemented that structure. How would I go about a pythonic way of caching the structure in memory? – aspiringMaster Feb 09 '19 at 15:00
  • For example, you can create a `@staticmethod getStructure` function in your folder Model class, and a class variable `_structure_cache`, that would store "cached" list of models. When method is called for the first time, it should query database for the models, and store them in _structure_cache. Next time, it would return the date stored in variable – MihanEntalpo Feb 10 '19 at 15:56