Use database for clips/channel management

[aviaryan]

Continuing from #13

Pros of switching to sqlite:

1. Lesser bugs
2. No need to continuously rename clip files. (pain for cloud syncing)
3. Extensible (extra attributes can be added to clips)
4. Open to users ( http://sqlitebrowser.org )

[hoppfrosch]

As you marked this as ready: Any guess, when we will see this released?

[aviaryan]

I have not implemented it yet, so no due date. ( Ready -> In Progress -> Done in waffle)

[aviaryan]

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

• The 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 channel
• The clip_file is the binary ClipboardAll file's path. We can save such files on disk as id.extension.(I hate BLOBs)
• We can link clips to corresponding history item ID too.
• In case of FIXATE feature, order number will be manipulated to manage ordering of clips.

Similarly we will use a separate table for channels. It should be simple to implement.

I will continue updating this post with more ideas.

[hoppfrosch]

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:

• Introducing table channels allows storing additional info about channels within database (if needed)
• Introducing table clip2channel allows assignment of a clip to one or more channels. For example:
  • if a clip is added (with 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)
  • Doing so one clip content can be assigned to many channels. This avoids redundancy: Clips with exactly the same content have to be stored only once within database.
  • A clip can be removed safely from table "clip", if there is no row in table clip2channel anymore, whose fk_clip == pk_clip(-to-be-removed) (referential integrity)
  • Attribute 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 ..)
  • Same is valid for attribute order number
• Introducing attribute checksum within table clip allows easy identification of duplicate clips.
  • If a new clip has to be added to the database, calculate checksum before adding. If there is a data set with the same checksum - you don't need to add the new clip, as it is identical to the already existing. You have Add only a new entry to table clip2channel instead.
• If you want to activate (the not-yet-existing) option "Remove duplicate Clips from channel", you just have to iterate over table clip2channel and identify all rows with identical fk_channel/fk_clip pairs and remove all but one row ...
• I'm not clear whether you use the clipboardformat (https://msdn.microsoft.com/en-us/library/windows/desktop/ms649013(v=vs.85).aspx) within Clipjump. Nevertheless I added a Lookup-Table clipboardformat for this ...

[hoppfrosch]

Similarly we will use a separate table for columns.

What do you mean here?

[aviaryan]

@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.

[hoppfrosch]

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 ... ;-)

[hoppfrosch]

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

[aviaryan]

@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 }

[hoppfrosch]

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?