1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
|
---------------------------------------------------------
KexiDB module IDEAS, ISSUES, PROPOSALS
Copyright (C) 2003 Jaroslaw Staniek js at iidea dot pl
Started: 2003-08-01
Kexi home page: Users: http://www.koffice.org/kexi/ Developers: http://kexi-project.org
Changes:
2005-09-16 Global BLOBs Storage
---------------------------------------------------------
|
| This document is expected to be independent of given KexiDB dirver implementation,
| but proposals are described here are after certain decisions has been made:
| -drivers are (by current importance): SQLite (embedded),
| ODBC (universality reasons ), Mysql (historical reasons), PostgreSQL
|
-----------------------------------------------------------------
1. EXTENSION: Option for setting user-defined error messages on selected error types.
Since new KexiDB has fully i18n'd user-visible messages, it outperforms other products as MSA
(that often shows confusing message coming directly from the engine). There is possibility
to provide users with table of error messages that they can personalize. These settings can
be properties of table (query, form, etc.) and thus can be inherited (eg. by form from a table).
By default, i18n'd messages from KexiDB can be presented.
<EXAMPLE>
Before we set our message for UNIQUE KEY conflict for table Cars (name varchar UNIQUE, price integer),
we have got this message when try to violate 'unique' restionction:
"There is already added one record with field 'name' set to 'Dodge' in table 'Cars'.
This field must be unique."
Now, we want more descriptive message: "There should be only one car named 'Dodge'!".
We write this not as above, but rather: "There should be only one car named %field%!",
since %field% is automatically substituted by given field value. After this we get message
that is easier to understand for everyday user. Of course Kexi (and competitors too) never will
be so clever as a human in building such messages automatically.
</EXAMPLE>
<NOTE>Inheriting messages: it will be more conventient to set up custom messages (if required)
for table definition rather than just in form. If we set up given message in for a table, every
form that uses this table as data source (end every query that do so) can use this message when needed.
This note is also useful for other types of properties that can be inherited by extended objects from.
</NOTE>
-----------------------------------------------------------------
2. KexiDB classes inheritance diagram
TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO TODO
Object
DriverManager
Driver
[DRIVER]Driver
Connection
[DRIVER]Connection
[DRIVER] should be substituted by SQLite, MySQL, etc.
These are classess to reimplement in the drivers.
ConnectionData
Field
Cursor
[DRIVER]Cursor
FieldList
TableSchema
QuerySchema
IndexSchema
Transaction
TransactionGuard
BaseExpr
NArgExpr
UnaryExpr
BinaryExpr
ConstExpr
VariableExpr
-----------------------------------------------------------------
3. Table schema, query schema, etc. storage
Table or query schemas are extended with additional properties,
usually not available from most db engines or even related to gui.
//This table contains list of kexi object stored within project (tables, queries, etc.)
kexi__objects
o_id integer unsigned primary key auto_increment,
o_type byte, //type id taken from KexiDB::ObjectTypes
o_name varchar(200),
o_caption varchar(200), //optional, enriched name, can contain non-latin1 characters
o_help varchar
//This table contains object's data, such as form's xml def.
kexi__objectdata
o_id integer REFERENCES kexi__objects(o_id),
o_data varchar,
o_sub_id varchar(200) //sometimes single object can have more than one data
//-these can be identified by o_sub_id
//if not needed, leave it as NULL
//This table contains fields that are members of given kexi object.
//Not all object types should be allowed, now allowed are tables and queries
kexi__fields
t_id integer REFERENCES kexi__objects(o_id),
f_type byte,
f_name varchar(200),
f_length integer,
f_precision integer,
f_constraints integer,
f_options integer,
f_default varchar,
//these are additional properties:
f_order integer,
f_caption varchar(200),
f_help varchar
//This table contains a list of parts defined for this database
kexi__parts
p_id integer unsigned primary key auto_increment,
p_name varchar(200),
p_mime varchar(200),
p_url varchar
TODO: add version?
//This table contains global (predefined or not) properties for this database
kexi__db
db_property varchar(32),
db_value varchar
//Predefined properties stored in kexi__db:
property="kexidb_major_ver", value=<major_version>
Where: <version> is a string containing integer in textual form that denotes
KexiDB implementation major version used to create or update this database.
This property is useful for KexiDB library and applications to denote
what KexiDB features could be available. This ensures backward compatibility
and (at least in some cases) -- forward compatibility.
property="kexidb_minor_ver", value=<minor_version>
like above -- minor KexiDB version
property="project_caption", value=<string>
property="project_desc", value=<string>
TODO: put more props. todo - creator, created date, etc. (also to KexiProjectData)
// Global BLOBs Storage (BLOB == BINARY LARGE OBJECT)
// Designed to store any kind of static images, sound and other binary data
//Table:
kexi__blobs
o_id integer unsigned primary key auto_increment, //unique project-wide identifier of a BLOB
o_data BLOB,
o_name varchar(200), //optional, name of the blob,
//can be e.g. a name of original file the data comes from,
//can contain non-latin1 characters
o_caption varchar(200), //optional, enriched name, can contain non-latin1 characters
o_mime varchar(200) NOT NULL, //MIME type of a BLOB, e.g. image/jpg
o_folder_id integer unsigned //references kexi__gallery_folders.f_id
//If null, the BLOB only points to virtual "All" folder
//WILL BE USED in Kexi >=1.1
//(not yet implemented, planned for 1.1)
//User-defined folders for better BLOBs categorizing.
//For simplification, folders have no lowlevel name but only caption.
//By default there are no physical folders, but only following virtual:
//-"All" folder
//-"Used" folder (computation of this takes some time because design of forms
// and reports needs to be parsed)
//-"Unused" folder (the same note as for "Used)
//-"By type" folder with "jpeg", "png" subfolders, etc. - available by grepping using BLOB's o_mime
kexi__gallery_folders
f_id integer unsigned primary key auto_increment, //unique id of a folder
f_parent_id integer unsigned //references f_id, can be null for top level folders
f_caption varchar(200), //optional, enriched name, can contain non-latin1 characters
f_notes varchar, //optional notes
-----------------------------------------------------------------
4. Extended table schema information:
Docs available online:
- http://kexi-project.org/wiki/wikiview/index.php?ExtendedTableSchemaInformation
- Table Column Lookup: http://kexi-project.org/wiki/wikiview/index.php?KexiDBLookupColumns
-----------------------------------------------------------------
5. DEPRECATED TABLES
//This table contains additional information for query schemas
kexi__querydata
q_id integer REFERENCES kexi__objects(o_id),
q_sql varchar, //raw sql text - stored in the unchanged form entered by a user (or generated)
q_valid boolean, //query schema may be invalid (syntactically or logically,
//but still can be stored)
q_parent_tab integer REFERENCES kexi__objects(o_id) //tqparent table of this query
//This table contains information about fields defined in select queries
kexi__queryfields
q_id integer REFERENCES kexi__objects(o_id),
f_order integer, //order of this field in query schema
f_id integer REFERENCES kexi__objects(f_order), //may be NULL if a field is asterisk
f_tab_asterisk integer REFERENCES kexi__objects(o_id), //if this is "single-table" asterisk
//this member references to valid tables id, otherwise is NULL
f_alltab_asterisk boolean //true if this is "all-tables" asterisk, otherwise false
//This table contains information about tables used by select queries
kexi__querytables
q_id integer REFERENCES kexi__objects(o_id),
t_id integer REFERENCES kexi__objects(o_id), //references to table used by given q_id
t_order integer //order of t_id table in query schema's tables list
|
