User:Janm/Mozloc/db
Mozloc uses mozStorage to store project files. So project file is SQLite database, and I use .sqlite extension for it.
Contents
Why hold all string in database?
- seach support
- update without need of previous version
Structure
project
- id_p
- INTEGER PRIMARY KEY. Project Id has no sence if there is just one project in database, but I can imagene databases with more than one project
- type
- STRING. Type of project. It determines the project type component.
version
"Version" is number, that increases everytime when the project is saved. When project is modified, the previous version holds it original value, so it is easy to determine the changes. Each "versioned" table has two columns: "since_v" and "to_v".
- id_v
- INTEGER.
- id_p
- INTEGER. Foreign key reference "project". UNIQUE(id_p, id_v)
file
- id_f
- INTEGER PRIMARY KEY.
- id_p
- INTEGER. Refers to project that owns this file.
- name
- STRING. Name of file. Uses forvard slashes, this is not filesystem path. This name is translated to physical path by related project type component.
- type
- STRING. Type of file. It determines parser and writer component.
- since_v, to_v
- INTEGERs. Used when synchronizing with project files.
file_piece
Holds the current state of file. File is sequence of comments and entities. This table holds the order of keys in file.
- id_f
- INTEGER. Foreign key reference "file".
- position
- INTEGER.
- id_e
- INTEGER. Foreign key references "key".
- comment
- STRING. Comment in file.
- UNIQUE(id_f, position)
- UNIQUE(id_f, id_e)
entity
- id_e
- INTEGER PRIMARY KEY.
- id_f
- INTEGER. Foreign key referecence "file".
- key
- STRING.
- id_s
- INTEGER. Foreign key references "string".
- since_v, to_v
- INTEGER. Version info.
translation
Each entity must have its record in this table for each translated language. For entities, that are not yet translated, the "status" is set to 'auto'.
- original
- Foreign key - id_e in original file
- translated
- Foreign key - id_e in translated file
- status
- STRING. Can be 'user' or 'auto'.
- PRIMARY KEY(original, translated)
string
String pool. Note that string is unique in language, so many keys can refer to one string.
- id_s
- INTEGER. primary key
- lang
- STRING. language code, eg. cs-CZ
- value
- STRING.
- UNIQUE(lang, value)
Queries
To ensure, that this model works.
Files in project
- ?1 - current project id
- ?2 - current project version
SELECT id_f, name, type, since_v = ?2 AS is_modified, (SELECT COUNT(id_e) FROM entity e WHERE f.id_f = e.id_f AND since_v <= ?2 AND to_v >= ?2) AS num_entities, (SELECT COUNT(id_e) FROM entity e INNER JOIN translation t ON (e.id_e = t.orig AND t.status = 'auto') WHERE f.id_f = e.id_f AND since_v <= ?2 AND to_v >= ?2) AS num_untranslated FROM file f WHERE id_p = ?1 AND since_v <= ?2 AND to_v >= ?2 ORDER BY name
another solution
SELECT f.id_f, name, type, (f.since_v = ?2) AS is_modified, COUNT(DISTINCT id_e) AS num_entities, COUNT(status = 'auto') AS num_untranslated FROM file f INNER JOIN entity e ON (f.id_f = e.id_f AND e.since_v <= ?2 AND t.since >= ?2) INNER JOIN translation t ON (e.id_e = t.original) WHERE id_p = ?1 AND f.since_v <= ?2 AND f.to_v >= ?2 GROUP BY f.id_f, name, type ORDER BY name