Comments (12)
@CyanVoxel @rugter100
found a good site to build up a database diagram for the transition to sqlite
https://dbdiagram.io/d
from tagstudio.
I was typing up an issue on this when I saw yours, so I will copy/paste it here.
Here is what I see as the layout of the database. Don't take this as me saying, "do things this way" but more as a starting point for a discussion on what the SQLite database would look like. Then some more well-verse people in Python and this project specifically can improve upon this.
- Entity: (TABLE) These are the files and maybe more?
- id: (INT) Auto-incrementing column.
- filename: (VARCHAR(32)) Example, "the-cake-is-a-lie.png", "cat.jpg", "dog.gif"
- path: (VARCHAR(260)) I chose 260 because that's the max path length in windows. Open to suggestions though, Linux and Mac may have more/less restrictions here..
- fields: (TABLE?) This would be what points to the tags and the collations I believe. If that's the case, I would think this would need to be another table and it would use the ID to point to each tag and collation.
- Tag: (TABLE)
- id: (INT) Auto-incrementing column
- name: (VARCHAR(32)?) the Display name of the tag. I put 32 somewhat randomly.
- shorthand: (VARCHAR(32)?) I'm not sure what this even is. I would love an explanation.
- aliases: (VARCHAR(32)?) If the name is "TV Shows", aliases could include TV, TV Series, etc.
- subtag_ids: (TABLE?) If the Tag is Movies, the subtag_id could point to a tag called Shrek. Like Entity > Fields above, if this holds more than one value, it will need to be it's own table. One more thing I was thinking about here, is that there is a chance a user might accidentally create a loop which could break something in the future. For a very basic example of what I mean, If you make Shrek a subtag of Movies, it should not be possible to make Movies a subtag of shrek. Even if there are tags in between the two like, Movies/DreamWorks/Shrek/Movies
- color: (VARCHAR(32)?) Color the tag will display on the UI.
- Collation: (TABLE) I'm not 100% sure what a collation is in the context of this program. I was thinking that it would be grouping things by images, audio, video, etc. But, that could be a single field in the Entity table. I would love to hear more about this.
- id: (INT) Auto-incrementing column
- title: (VARCHAR(32)?)
- e_ids_and_pages: (?) I'm not sure what this is for.
- sort_order: (SMALLINT) This could be a SMALLINT in SQLite, but in python, it's just an enum and it get's translated as it's transferred between python and SQLite.
- cover_id: (?) Also, not sure about this.
from tagstudio.
I have a lot of experience working with SQL in python. I could try fully implementing this and would love to have it as a feature. I might even see if it could easily support other sql databases like mysql/mariadb, shouldnt be too hard
from tagstudio.
Entity: (TABLE) These are the files and maybe more?
- id: (INT) Auto-incrementing column.
- filename: (VARCHAR(32)) Example, "the-cake-is-a-lie.png", "cat.jpg", "dog.gif"
- path: (VARCHAR(260)) I chose 260 because that's the max path length in windows. Open to suggestions though, Linux and Mac may have more/less restrictions here..
- fields: (TABLE?) This would be what points to the tags and the collations I believe. If that's the case, I would think this would need to be another table and it would use the ID to point to each tag and collation.
Tag: (TABLE)
- id: (INT) Auto-incrementing column
- name: (VARCHAR(32)?) the Display name of the tag. I put 32 somewhat randomly.
- shorthand: (VARCHAR(32)?) I'm not sure what this even is. I would love an explanation.
- aliases: (VARCHAR(32)?) If the name is "TV Shows", aliases could include TV, TV Series, etc.
- subtag_ids: (TABLE?) If the Tag is Movies, the subtag_id could point to a tag called Shrek. Like Entity > Fields above, if this holds more than one value, it will need to be it's own table. One more thing I was thinking about here, is that there is a chance a user might accidentally create a loop which could break something in the future. For a very basic example of what I mean, If you make Shrek a subtag of Movies, it should not be possible to make Movies a subtag of shrek. Even if there are tags in between the two like, Movies/DreamWorks/Shrek/Movies
- color: (VARCHAR(32)?) Color the tag will display on the UI.
Collation: (TABLE) I'm not 100% sure what a collation is in the context of this program. I was thinking that it would be grouping things by images, audio, video, etc. But, that could be a single field in the Entity table. I would love to hear more about this.
- id: (INT) Auto-incrementing column
- title: (VARCHAR(32)?)
- e_ids_and_pages: (?) I'm not sure what this is for.
- sort_order: (SMALLINT) This could be a SMALLINT in SQLite, but in python, it's just an enum and it get's translated as it's transferred between python and SQLite.
- cover_id: (?) Also, not sure about this.
Possible SQL?
CREATE TABLE entities (
id INTEGER PRIMARY KEY AUTOINCREMENT,
filename VARCHAR(32),
path VARCHAR(260),
fields BLOB(?)
);
-- as a collumn above, as a table below
CREATE TABLE Fields (
id INTEGER PRIMARY KEY AUTOINCREMENT,
entity_id INTEGER,
tag_id INTEGER,
collation_id INTEGER,
FOREIGN KEY (entity_id) REFERENCES entities(id),
FOREIGN KEY (tag_id) REFERENCES tags(id),
FOREIGN KEY (collation_id) REFERENCES collations(id)
);
CREATE TABLE tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(32),
shorthand VARCHAR(32),
aliases VARCHAR(32),
color VARCHAR(32)
);
CREATE TABLE subtags (
tag_id INTEGER,
subtag_id INTEGER,
PRIMARY KEY (tag_id, subtag_id),
FOREIGN KEY (tag_id) REFERENCES tags(id),
FOREIGN KEY (subtag_id) REFERENCES tags(id)
);
CREATE TABLE collations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title VARCHAR(32),
entity_ids_and_pages BLOB(?),
sort_order SMALLINT,
cover_id INTEGER
);
from tagstudio.
As of right now this is something I am working on for the Rust/Tauri build of the project, where we are throwing these kind of ambitious breaking changes. I'm tagging @CyanVoxel for his input on if an implementation in the current Python project would be appreciated.
from tagstudio.
@rugter100 That would be great. Just don't do it before @CyanVoxel has the chance to look this over. They might see some glaring oversights :)
from tagstudio.
I have a lot of experience working with SQL in python. I could try fully implementing this and would love to have it as a feature. I might even see if it could easily support other sql databases like mysql/mariadb, shouldnt be too hard
i've forked the repo on my own and i'm trying to implement that too, but i can't quite find where the ts_library.json
gets written to 😢
nvm i'm dumb it's library.py beside ts_core.py...
from tagstudio.
i've forked the repo on my own and i'm trying to implement that too, but i can't quite find where the
ts_library.json
gets written to 😢
In the user's selected library directory, inside the .TagStudio folder
from tagstudio.
Here is what I came up with.
TABLE entities {
id INTEGER pk unique
filename VARCHAR
path VARCHAR
}
TABLE fields {
id INTEGER pk unique
entity_id INTEGER
tag_id INTEGER
collation_id INTEGER
}
TABLE tags {
id INTEGER pk unique
name VARCHAR(32)
shorthand VARCHAR(32)
aliases VARCHAR(32)
color VARCHAR(32)
}
TABLE subtags {
tag_id INTEGER pk unique
subtag_id INTEGER
}
TABLE collations {
id INTEGER pk unique
title VARCHAR(32)
sort_order SMALLINT
cover_id INTEGER
}
Ref: fields.entity_id > entities.id // many-to-one
Ref: fields.tag_id > tags.id // many-to-one
Ref: fields.collation_id > collations.id // many-to-one
Ref: subtags.tag_id > tags.id // many-to-one
Ref: subtags.subtag_id > tags.id // many-to-one
from tagstudio.
I want to put DuckDB into consideration. It believe it plays better with network shares than SQLite. It can read and write from plain text formats too so that functionality could come in handy for the export feature.
from tagstudio.
I want to put DuckDB into consideration. It believe it plays better with network shares than SQLite. It can read and write from plain text formats too so that functionality could come in handy for the export feature.
I'm very interested. I read a few articles and comparisons against SQLite. But, I am curious what you mean by "plays better with network shares than SQLite." Also, I would love to see any proof/source of that.
from tagstudio.
I am mistaken actually. I read this and a couple of other pages of DuckDB and was under the impression that it does not have some of the issues of file locking over SMB (Depending on the SMB server config) that SQLite had given me in the past (Trying to point a Calibre library to a SMB share). A quick test later, it is not the case.
There is still a plus of the ability to R/W to and from plain text files though.
from tagstudio.
Related Issues (20)
- Segmentation fault when using the return key to accept changes to text line field
- Ressources on 'taggable tags' HOT 3
- filename text search HOT 4
- any plan for docker support? HOT 3
- [Feature Request] Add the ability to delete tags HOT 1
- Indexing tool inspiration
- Run the progam via mouse click HOT 1
- [Feature Request] Ignore anything under a directory or in an ignore file HOT 3
- [BUG] Library creation when closing loading Bar is buggy
- [Feature Request] Masonry and Justified View options HOT 2
- Large file misidentified as a decompression bomb HOT 2
- [Feature Request] Aseprite file format support. HOT 2
- [BUG] Small pixel art is blurry and doesn't fit in image preview HOT 2
- [BUG] Blank background is sometimes black and sometimes white HOT 7
- [BUG] Changing file directory HOT 1
- [BUG] Preview Panel Doesn't Resize Horizontally
- [Suggestion] Auto Tag by file type, or at the least search by non tags HOT 1
- [BUG] "Fix Unlinked Entries"/"Refresh Directories" Creates Duplicate Entries for Moved Files
- [BUG] Image Thumbnails Don't Load Occasionally
- Recent Libraries widget only updates when opening a library, not when creating a new library
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from tagstudio.