Message-ID: <572404702.15988.1711712245652.JavaMail.appbox@confluence> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_15987_2028520512.1711712245652" ------=_Part_15987_2028520512.1711712245652 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
This document describes database schema of Deskzilla/JiraClient = local cac= he.
This is an advanced topic. You need to learn it only if you have to acce= ss the database with your own scripts and tools.
You may need to do so, for example, if your Bugzilla or Jira server data= base has been destroyed or corrupted and you have no backup. In this case, = you may restore your server database data from the local cache.
This document has no hints on how to restore your server database, it de= scribes only how to retrieve data from the local cache.
The local cache is an SQLite database. The database file is "items.db=
", it is located in the root of the workspace. Remote files such as attachments =
are stored in the "download" folder in the root of the workspace.
Item and attribute are basic concepts = of the data schema.
The local cache stores all data as items. An item has a= n identifier (an integer number) and attribute values. Bas= ically, an item is an identifiable collection of key-value= pairs, where keys are attributes.
Attributes correspond to SQL tables. Each attribute tab= le has two columns: "item" and "value". The "item" column contains identifi= ers of items and the "value" column contains value of the = attribute of the item.
Each attribute has an identifier. For example, "com.alm= works.bugzilla:a:bug.summary" is the identifier of the "Summary" field of a= Bugzilla bug.
The "_tables" SQL table contains mapping from attribute= identifiers to their SQL tables.
The following SQL query display all attributes and their SQL tables:
SELECT * FROM _tables;
You may start with this query to explorer available attributes.
This section describes some useful special attributes.<= /p>
The "com.almworks.items.api.sync:a:connection" attribute identifies a connection = an item belongs to. In general it is mapped to the "connection" SQL table.<= /p>
A connection is an item which represents a JiraClient o= r Deskzilla connection.
You may find the connection configuration in the "config.xml" file in th= e root of the workspace. Connection configurations are "provider" tags unde= r the root "engine" tag.
Each connection as GUID identifier in its "id" tag.
You may map a connection configuration to the connection item by this GUID with the following SQL query (replace GUID with your co= nnection's GUID):
SELECT * FROM connection_id where value =3D '068193C9-C4A2-19D8-8000-0= 0005DF6BEED';
Assuming that the connection item is "2285", the follow= ing query selects all items which belong to the conne= ction:
SELECT item FROM connection where value =3D 2285;
Deskzilla and JiraClient do not permanently delete items from their local caches. Instead they mark items as 'exi= sting' with the "existing" SQL table.
In order to test if an item exists or if it has been de= leted, you may use a query like this:
SELECT value FROM existing WHERE item =3D 2285;
The query returns single row with value "1" for existing items=
strong> or empty result set if the item has been deleted.<=
/p>
Secondary items are attachments, comments, Jira links a=
nd so on. Secondary items have an attribute=
strong> which refers to their parent item (such as a Bugzi=
lla bug). For example, Deskzilla stores bug attachments as secondary items=
. Attachment items refer to a bug they belong to =
with the "com.almworks.bugzilla:a:attachment.bug" attribute ("attachment_bug" SQL table). The following query finds all attachments of a Bugzilla bug (bug item is "8431"): This query may return deleted attachments as well. Enum items are projects, components, versions and so on. Primary items (=
Bugzilla bug, Jira issues) refer to enum items by their corresponding attri=
butes. For example, the "com.almworks.bugzilla:a:bug.product" ("bug_product" SQ=
L table) attribute of a Bugzilla bug points to a product item. The following query displays the product item of a Bugzilla bug (bug Secondary=
Items
SELECT * FROM attachment_bug WHERE value =3D 8431;
Enum Items=
SELECT value FROM bug_product WHERE item =3D 8431;
To get a display name of the product (item is "4673"), use the "com.almw= orks.bugzilla:a:product.visualKey" attribute ("product_visualkey" SQL table= ). The query is:
SELECT value FROM product_visualkey WHERE item =3D 4673;
Let's find an attached file of a Bugzilla bug #1584803.
Search "com.almworks.bugzilla:a:bug.id" ("bug_id" table) values fo= r the bug ID:
SELECT item FROM bug_id WHERE value =3D 1584803;
item | |
---|---|
1 | 8431 |
Note, the query may return more results: bugs from different connections= and deleted bugs.
Now, find all attachments of the bug: find items those = refer to the bug item via "com.almworks.bugzilla:a:attachm= ent.bug" ("attachment_bug" table) attribute:
SELECT item FROM attachment_bug WHERE value=3D8431;
item | |
---|---|
1 | 8472 |
2 | 8482 |
3 | 8483 |
4 | 8484 |
5 | 8485 |
6 | 8486 |
7 | 8487 |
Some of these attachments may have been deleted.
SELECT item, value FROM attac=
hment_filename WHERE item in (8472, 8482, 8483, 8485, 8486, 8487)
Now, find file names of these attachments: use the "com.almworks.bugzilla:=
a:attachment.fileName" ("attachment_filename" table) attribute:
item | value | |
---|---|---|
1 | 8472 | 1584803-versiondisplay-2531b1.patch |
2 | 8482 | 1584803-lightning-calendar-253.patch |
3 | 8483 | 1584803-sm-tempfix-60.patch |
4 | 8485 | 1584803-sm-tempfix-60.patch |
5 | 8486 | 1584803-sm-tempfix-60.patch |
6 | 8487 | 1584803-version-beta1.patch |
This query returns only downloaded attachments.
Now we know the file names of the bug's attachments and may find them in=
the "download" folder
.