User:Janm/Mozloc/db

From MozillaWiki
Jump to: navigation, search

Mozloc uses mozStorage to store project files. So project file is SQLite database, and I use .sqlite extension for it.

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