| 244 | === Intermediate Model === |
| 245 | - surrogate keys for all resources |
| 246 | - text and int fields (same as Complete Model, without `*_text` tables) |
| 247 | |
| 248 | The minimal model above is handy for showing the essence of the new model, |
| 249 | but it's too simple in practice. |
| 250 | |
| 251 | I think we need at the very least to support 'integer' type columns, useful for storing dates efficiently, boolean values, and relations to other resources (as the surrogate id will be an integer). |
| 252 | |
| 253 | |
| 254 | === Complete Model === |
| 255 | - surrogate keys for all resources |
| 256 | - int, short and long text fields |
| 257 | |
| 258 | Not absolutely necessary to go that far, this could nevertheless help a lot for the MySQL backend (#6986), possibly also for a future Oracle backend. Don't know about PostgreSQL, but for SQLite this should be indifferent. |
| 259 | |
| 260 | {{{ |
| 261 | #!div style="width: 30%; float: left" |
| 262 | |
| 263 | ==== Schema ==== |
| 264 | |
| 265 | '''resource_schema''' |
| 266 | || ''realm'' || ''prop'' || ''name'' || ''value'' || |
| 267 | |
| 268 | |
| 269 | '''resource_prop''' |
| 270 | || ''id'' || ''name'' || ''value'' || |
| 271 | |
| 272 | '''resource_revprop''' |
| 273 | || ''changeid'' || ''name'' || ''value'' || |
| 274 | |
| 275 | '''resource_change''' |
| 276 | || ''id'' || ''changeid'' || ''name'' || ''value'' || |
| 277 | |
| 278 | |
| 279 | '''resource_prop_text''' |
| 280 | || ''id'' || ''name'' || ''value'' || |
| 281 | |
| 282 | '''resource_revprop_text''' |
| 283 | || ''changeid'' || ''name'' || ''value'' || |
| 284 | |
| 285 | '''resource_change_text''' |
| 286 | || ''id'' || ''changeid'' || ''name'' || ''value'' || |
| 287 | |
| 288 | |
| 289 | '''resource_prop_int''' |
| 290 | || ''id'' || ''name'' || ''value'' || |
| 291 | |
| 292 | '''resource_revprop_int''' |
| 293 | || ''changeid'' || ''name'' || ''value'' || |
| 294 | |
| 295 | '''resource_change_int''' |
| 296 | || ''id'' || ''changeid'' || ''name'' || ''value'' || |
| 297 | |
| 298 | }}} |
| 299 | {{{ |
| 300 | #!div style="width: 60%; border: 2px dotted #ddd; padding: 0 0 1em 3em ; float: left;" |
| 301 | |
| 302 | ==== Example Dataset ==== |
| 303 | |
| 304 | '''resource_schema''' |
| 305 | || ''realm'' || ''prop'' || ''name'' || ''value'' || |
| 306 | || ticket || summary || type || text || |
| 307 | || ticket || description || type || wiki|| |
| 308 | || ticket || reporter || type || string|| |
| 309 | |
| 310 | ---- |
| 311 | |
| 312 | '''resource_prop''' |
| 313 | || ''id'' || ''name'' || ''value'' || |
| 314 | || 0 || reporter || joe || |
| 315 | |
| 316 | '''resource_revprop''' |
| 317 | || ''changeid'' || ''name'' || ''value'' || |
| 318 | || 1 || author || joe || |
| 319 | || 2 || author || joe || |
| 320 | || 3 || author || cboos || |
| 321 | |
| 322 | '''resource_change''' |
| 323 | || ''id'' || ''changeid'' || ''name'' || ''value'' || |
| 324 | || 0 || 1 || reporter ||joe|| |
| 325 | |
| 326 | ---- |
| 327 | |
| 328 | '''resource_prop_text''' |
| 329 | || ''id'' || ''name'' || ''value'' || |
| 330 | || 0 || summary ||Multiple Project Support|| |
| 331 | || 0 ||description||One day... || |
| 332 | |
| 333 | '''resource_revprop_text''' |
| 334 | || ''changeid'' || ''name'' || ''value'' || |
| 335 | || 2 || comment || come on...|| |
| 336 | || 3 || comment || sure... || |
| 337 | |
| 338 | '''resource_change_text''' |
| 339 | || ''id'' || ''changeid'' || ''name'' || ''value'' || |
| 340 | || 0 || 1 || summary ||Multiple Project Support|| |
| 341 | || 0 || 1 ||description||Should be easy...|| |
| 342 | || 0 || 2 ||description||Should be easy... Redmine has it!|| |
| 343 | || 0 || 3 ||description||One day...|| |
| 344 | |
| 345 | ---- |
| 346 | |
| 347 | '''resource_prop_int''' |
| 348 | || ''id'' || ''name'' || ''value'' || |
| 349 | || 0 || id || 130 || |
| 350 | |
| 351 | '''resource_revprop_int''' |
| 352 | || ''changeid'' || ''name'' || ''value'' || |
| 353 | || 1 || date ||5 years ago|| |
| 354 | || 2 || date ||2 years ago|| |
| 355 | || 3 || date ||1 year ago || |
| 356 | |
| 357 | '''resource_change_int''' |
| 358 | || ''id'' || ''changeid'' || ''name'' || ''value'' || |
| 359 | || 0 || 1 || id ||130|| |
| 360 | |
| 361 | }}} |
| 362 | |
| 363 | [[html(<br style="clear: both" />)]] |
| 364 | |
| 365 | |