Open aviaryan opened 8 years ago
As you marked this as ready: Any guess, when we will see this released?
I have not implemented it yet, so no due date. ( Ready -> In Progress -> Done in waffle)
Thinking about the schema for Clips table, it may go something like the following.
id, PRIMARY KEY AUTO INCREMENT (never changes)
data, text,
channel, int
order number, int
date
clip_file
... other attributes
order number
may change in case of reordering of clips or more generally when a clips move. It gives the order of clips in a channelid
.extension.(I hate BLOBs)Similarly we will use a separate table for channels. It should be simple to implement.
I will continue updating this post with more ideas.
Just a few raw thoughts ... I think you should avoid redundancy with storing clips . Isn't the information about channels stored in the database?
I would suggest something like this
TABLE: channel
-----------------
pk_channel, PRIMARY KEY AUTO INCREMENT (never changes)
name, text
... more attributes
Table: clip
-----------------
pk_clip, PRIMARY KEY AUTO INCREMENT (never changes)
data, text,
clip_file
checksum (md5?)
fk_clipboardformat (foreign key to table clipboardformat)
... other attributes
Table: clip2channel
----------------
pk_clip2channel, PRIMARY KEY AUTO INCREMENT (never changes)
fk_channel (foreign key to table channels)
fk_clip (foreign key to table clip)
date
order number, int
Table: clipboardformat
---------------
pk_clipboardformat, PRIMARY KEY AUTO INCREMENT (never changes)
description, string (CF_BITMAP, CF_DIB ....)
Remarks:
channels
allows storing additional info about channels within database (if needed)clip2channel
allows assignment of a clip to one or more channels. For example:
pk_clip
1) to a certain channel (with pk_channel
1), it gets the following entries in table clip2channel
: 1.) pk_clip2channel
1, fk_channel
1, fk_clip
1 and (if Clip history is implemented as channel as well and has for example pk_channel 0) 2.) pk_clip2channel
2, fk_channel
0, fk_clip
1. If you remove the clip from channel 1, you have to delete row with pk_clip2channel==1
, but row with pk_clip2channel==0
still remains (and therefore the clip is still available in HIstory channel)clip2channel
anymore, whose fk_clip == pk_clip(-to-be-removed)
(referential integrity)date
is moved from table clip
to table clip2channel
. This holds the date the clip was added to the channel (and allows chronological sorting of clips within channel view ..)order number
checksum
within table clip
allows easy identification of duplicate clips.
clip2channel
instead.clip2channel
and identify all rows with identical fk_channel/fk_clip
pairs and remove all but one row ...clipboardformat
for this ... Similarly we will use a separate table for columns.
What do you mean here?
@hoppfrosch I really like the schema you have suggested. It covers every scope of the application I can think of. For the 4th table, I agree that is not needed as the clipboard-type text is managed by the application (translatable).
Similarly we will use a separate table for columns. What do you mean here?
That was a typo. It's channels, not columns.
I have had a look onto database schema of Ditto, which uses also sqlite. They provided also a table clipboardformat
- so I wasn't sure we need the info for Clipjump as well - else there wasn't anything sophisticated to see ... ;-)
I had a little time and started with implementation of a SQLite database class for Clipjump, based on the database model suggested above: https://github.com/hoppfrosch/Clipjump/tree/hoppfrosch/feature/class_clipjumpdb
@hoppfrosch Great. Looks good. One thing I will like to point out is that a helper method could be used for executing the sql and throwing the message in case an error occurs.
If !base.Exec(SQL)
throw, { what: " ClipjumpDB SQLite Error", message: base.ErrorMsg, extra: base.ErrorCode, file: A_LineFile, line: A_LineNumber }
Which helper method do you mean? Is throwing exception the desired error handling method since you haven't used it throughout the yet existing code?
Continuing from #13
Pros of switching to sqlite: