0

I need to be able to store files, directories within MySQL, but I don't know how to do it so I can also also have a fairly efficient query to get data from the table for example /swatcat/superscecret.txt

My initial thought was :

DROP TABLE IF EXISTS objects;
CREATE TABLE objects
(
    ID INT NOT NULL AUTO_INCREMENT, 
    filename VARCHAR(200) NOT NULL,
    objectTypeID INT NOT NULL,
    parentID INT,

    PRIMARY KEY (ID),
    FOREIGN KEY (objectTypeID) REFERENCES objectTypes(ID)
);

Object type is either a file or directory:

CREATE TABLE objectTypes
(
    ID INT NOT NULL AUTO_INCREMENT, 
    name VARCHAR(200) NOT NULL UNIQUE,

    PRIMARY KEY (ID)
);

My thought process was parentID would be the directory that the file or directory resides in... The issue though is finding /swatcat/supersecret.txt and what if I decide to rename swatcat to test101 then how do I create a query that could cope with it?

Swatcat
  • 73
  • 6
  • 21
  • 57
  • Thank you for the clarification on the `objectTypeId`. I'm struggling to understand why storing this in a hierarchical manner would be advantageous to storing `filepath` and `filename` in a single record. I'm a fan of using parent/child relationships to store data when it's applicable, but it seems overkill for this usecase. Recursive CTE's can be expensive and you would probably need two passes to make an UPDATE statement. One pass to generate the filepath and find the `id` of the item you are after, and a second to apply an UPDATE to that `id`. – JNevill Feb 12 '18 at 19:26
  • @JNevill I was thinking the same but if a directory changed wouldn't the update be complex? – Swatcat Feb 12 '18 at 19:28
  • You could just hit it with replace: `UPDATE myTable SET filepath = replace(filepath, '/originaldirname/','/newdirname/');` [Kind of like this](http://sqlfiddle.com/#!9/abd6c8/3). The difference is that this will affect more records, where your proposed schema will affect 1. It gets ugly though if there is more than one directory by that name (in a different path). It's simple to hit the right path with the `filepath|filename` schema, but more complicated with a parent/child (although not awful, just more complicated). – JNevill Feb 12 '18 at 19:38
  • @JNevill: It's not only that that recursive CTEs can be expensive, but that filesystems aren't hierarchical. They're a network. (Symbolic links, hard links, etc.) – Mike Sherrill 'Cat Recall' Feb 12 '18 at 20:32
  • 1
    Hi. Questions arising from "thought process" wonderings/wanderings re "issues" are too general for SO. Please read & act on [ask]. That includes summarizing appropriate research. Your question should be closed; but that must now wait until the bonus ends. Moreover nevertheless hierarchical data in SQL both [in general](https://stackoverflow.com/q/4048151/3404097) and [re file systems](https://stackoverflow.com/q/144344/3404097) are answered faqs. Moreover no one can trade off design options without specific designs, context & cost-benefit function: – philipxy Feb 13 '18 at 00:12
  • 1
    My current generic comment re "best" etc: There's no such thing as "better"/"best" in engineering unless you define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". – philipxy Feb 13 '18 at 00:12

1 Answers1

1

Plan A: Have the directory path in a row in a separate table from the filename.

Plan B: Like Plan A, but with the path being multiple rows in a "hierarchical" arrangement in the new table.

Plan C: Leave the "dir/fn" as is, then use LIKE or RLIKE to locate a row, then use SUBSTRING_INDEX (or REGEXP_REPLACE in MariaDB) to substitute.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thank you JNevill and Rick James your suggestions have both resulted in the answer... Which should have been obvious to have directory as separate table. – Swatcat Feb 14 '18 at 13:01