Other languages: Français ⋅ English

SQLite Data Structure

The current v8 data structure is described to assist with developing sql queries. This structure is used by QualCoder 3.2. Older database structures are changed when opened to match this format.

Tables are listed alphabetically.

Table: annotation

anid integer A unique auto-number

fid integer Links to the source.id to identify the text file. Annotations are only for text files.

pos0 integer The starting character position in the text file.

pos1 integer The ending character position in the text file.

memo text Contains the annotation text.

owner text The coder who created the entry.

date text The date and time the entry was created, format yyyy-mm-dd hh:mm:ss

Table: attribute

attrid integer A unique auto-number.

name text The name of the attribute. This will match the name in attribute_type.

attr_type text This is either 'case' or 'file'

value text This store the attribute value as text, even if it is meant to be a numeric value. You may need to cast(value as real)

id integer This field represents a source id or a case id.

owner text The coder who created the entry.

date text The date and time the entry was created, format yyyy-mm-dd hh:mm:ss

Table: attribute_type

name text The unique name of the attribute.

owner text The coder who created the entry.

date text The date and time the entry was created, format yyyy-mm-dd hh:mm:ss

memo text Contains a memo about the attribute type.

caseOrFile text This is either 'case' or 'file'

valuetype text This is either 'character' or 'numeric'

Table: case_text

id integer Unique autonumber.

caseid integer Links to the cases.caseid.

fid integer links to the source.id. These are only for text files.

pos0 integer The starting character position in the text file. Null if not a text file.

pos1 integer The ending character position in the text file. Null if not a text file.

memo text Contains the memo text.

owner text The coder who created the entry.

date text The date and time the entry was created, format yyyy-mm-dd hh:mm:ss

Table: cases

caseid integer A unique auto-number.

name text The name of the case.

memo text Contains the memo text.

owner text The coder who created the entry.

date text The date and time the entry was created, format yyyy-mm-dd hh:mm:ss

Table: code_av

avid integer A unique auto-number.

id integer Links to the source.id to identify the source A/V file.

pos0 integer The starting milliseconds position in the A/V file.

pos1 integer The ending milliseconds position in the A/V file.

cid integer The code_name id to identify the code.

memo text Contains the memo text.

owner text The coder who created the entry.

date text The date and time the entry was created, format yyyy-mm-dd hh:mm:ss

important integer If assigned 1 it is an important code.

Table: code_cat

catid integer A unique auto-number.

name text The name of the category.

memo text Contains the memo text.

owner text The coder who created the entry.

date text The date and time the entry was created, format yyyy-mm-dd hh:mm:ss

supercatid integer This represents the parent category of this category. It is Null if it is a top category.

Table: code_image

imgid integer A unique auto-number.

id integer Links to the source.id to identify the source image file.

x1 real The left x position in pixels in the image file. Stored as decimal value.

y1 real The top y position in pixels in the image file. Stored as decimal value.

width real The number of pixels across. Stored as decimal value.

height real The number of pixels down. Stored as decimal value.

cid integer The code_name id to identify the code.

memo text Contains the memo text.

owner text The coder who created the entry.

date text The date and time the entry was created, format yyyy-mm-dd hh:mm:ss

important integer If assigned 1 it is an important code.

pdf_page integer Null or a number representing the page of the pdf

Table: code_name

cid integer A unique auto-number.

catid integer The code_cat.catid if this code is part of a category. Null if not part of a category.

name text The unique name of the code.

memo text Contains the memo text.

owner text The coder who created the entry.

date text The date and time the entry was created, format yyyy-mm-dd hh:mm:ss

color text A hex value ranging from '#000000' to '#FFFFFF' QualCoder recognises 120 colours so can apply light or dark text over the top.

Table: code_text

ctid integer A unique auto-number. (added in v3).

cid integer The code_name id to link the code.

fid integer The file source id to link the text file.

seltext text A copy of the text of this coded text segment.

pos0 integer The starting character position in the text file. Null if not a text file.

pos1 integer The ending character position in the text file. Null if not a text file.

memo text Contains the memo text.

owner text The coder who created the entry.

date text The date and time the entry was created, format yyyy-mm-dd hh:mm:ss

avid integer Link to a coded A/V segment time position. See table code_av.

important integer If assigned 1 it is an important code.

Table: journal

jid integer A unique auto-number.

name text Name of the journal.

jentry text Contains the journal text.

owner text The coder who created the entry.

date text The date and time the entry was created, format yyyy-mm-dd hh:mm:ss

Table: project

Should contain one row.

databaseversion text Currently v1, v2 or v3. QualCoder will update to the most recent version (adds columns to tables).

date text The date and time the entry was created, format yyyy-mm-dd hh:mm:ss

memo text A memo about the overall project.

about text Should describe the current QualCoder version used. e.g. QualCoder 2.5

bookmarkfile integer The id of the source text file. Used for jumping to the bookmark.

bookmarkpos integer The character position in the text file. Used for jumping to the bookmark.

codername text The current coder name.

recently_used_codes text Contained code_ids of recently used text codes, space delimited.

Table: source

id integer A unique auto-number.

name text The name with fileextension of the file. The file extension helps to determine if hte file is text, image or A/V. If the file has '.transcribed' as a suffix it is associated with the matching A/V file.

fulltext text The fulltext of a text file, in UTF-8 encoding. Null if an image or A/V file.

memo text Contains the memo text.

owner text The coder who created the entry.

date text The date and time the entry was created, format yyyy-mm-dd hh:mm:ss

mediapath text

av_text_id integer A link for an audio/video source to the corresponding text file transcript.

If the mediapath is empty it is an internally stored text file within the project folder.

If the mediapath begins with 'docs:' it is a link to an external text file. The fulltext will still be loaded into 'fulltext' for use.

If the mediapath begins with: '/images/' , '/audio/', '/video/' the image, audio or video file is stored internally in the project folder.

If the mediapath begins with: 'images:' , 'audio:', 'video:' the image, audio or video file is linked to externally. The full path to the file is stored.

Table: stored_sql

title text The title of the sql.

description text A detailed description of the sql function.

grouper text Potential to organise sql into groups.

ssql text The user generated sql.

Table: ris

Used for RIS bibliography

risid integer Identifier for this bibliography entry.

tag text Short RIS tag.

longtag text Long text RIS tag.

value text text data for the tag.

Graph visualisation tables

Several tables to store details of save graphs then the items within the graphs.

Table: graph

grid integer Unique identifier.

name text Unique graph name.

description text Text description.

date text Creation date.

scene_width integer Used to set scene size on load.

scene_height integer Used to set scene size on load.

Table: gr_av_item

Displays an arrow icon for a playable A/V coded segment.

gr_avid integer primary key

grid integer

avid integer

x integer

y integer

pos0 integer

pos1 integer

filepath text

tooltip text

color text

Table: gr_case_text_item

Displays details of the case, with formatting parameters.

gcaseid integer primary key

grid integer - Links to graph table

x integer

y integer

caseid integer

font_size integer

bold integer

color text

displaytext text

Table: gr_cdct_line_item

Display line linking codes and categories

glineid integer primary key

grid integer

fromcatid integer

fromcid integer

tocatid integer

tocid integer

color text

linewidth real

linetype text

isvisible integer

Table: gr_cdct_text_item

Displays coded text with some formatting parameters.

gtextid integer primary key

grid integer - Links to graph table

x integer

y integer

supercatid integer

catid integer

cid integer

font_size integer

bold integer

isvisible integer

displaytext text

Table: gr_file_text_item

Displays file details with formatting parameters.

gfileid integer primary key

grid integer

x integer

y integer

fid integer - Link to source table

font_size integer

bold integer

color text

displaytext text

Table: gr_free_line_item

Displays user defined line link between objects

glineid integer primary key

grid integer

fromcatid integer

fromcid integer

tocatid integer

tocid integer

color text

linewidth real

linetype text

isvisible integer

Table: gr_free_text_item

Displays free text, or memos with formatting parameters.

gfreeid integer primary key

grid integer

freetextid integer

x integer

y integer

free_text text

font_size integer

bold integer

color text

tooltip text

ctid integer

memo_ctid integer

memo_imid integer

memo_avid integer

Table: gr_pix_item

Displays coded image.

grpixid integer primary key

grid integer

imid integer

x integer

y integer

px integer - x position within original image

py integer - y position within original image

w integer - width in original image

h integer - height within original image

filepath text

tooltip text

pdf_page integer - if an image of a pdf