64bit/Database

Requirements

• Maximum bid USD $5,000 (fixed price for entire project, because large chunks of it are already done, it's your job to take it across the finish line)
• see further requirements DO NOT BID UNLESS YOU MEET REQUIREMENTS!
• After you've read the rest, see the Help
• expert in SQLite, tables, virtual tables, unique indexes, FTS, etc
• expert in implementing SQLite in Qt: QTableView, QSortFilterProxyModel, etc

Deliverable

Using Qt Widgets & C++, you will complete a cross-platform database (using SQLite) with GUI browser with these features:

• utilize SQLite tables (done), and FTS if necessary for fast db searches (?)
• 100% unicode
• “main browser” window has a “Source” list (tree view) on the left side (listing the “Library” and other “Playlists”) (single column, heirarchical, as some act as folders) (this part is working well enough but needs finishing touches)
• “main browser” window has a “Tracks” list (table view) on the right side side (showing contents of currently selected playlist) (multi-column, flat, configurable) (bulk of your work is in implementing this)
• tracks lists maybe use "model backed view", using QSortFilterProxyModel? or whatever it takes to "instantly" switch between playlists which may show a hundred thousand tracks
• scroll bar thumb should pre-load to actual count of list even if list only shows first page of items, ie: dragging the thumb to the bottom causing the next page to load is NOT what i want, i want that to mean actually scrolling to the end of the list, and it must be fast to load said playlist (currently it is very slow as it pre-pages in all content, so i'm doing it a stupid way)
• playlist may contain tracks (songs) OR other playlists (ie: like folders)
• when selecting a “Source” on the left, show the contents of that source on the right, in the “Tracks” list (done?)
• fully configurable and customizable list of meta data “sort columns” on the right, ability to hide / show / drag reorder as user wishes (old DB model & data provided, you create new DB model) (mostly done?)
• ability to sort by any meta data column (quickly), sorts are "stable", ie: if user clicks to sort sort by artist THEN clicks to sort by song name, songs will be sorted by song name, but within each group of songs by the same name, they'll be sorted by artist. primary, secondary, tertiary etc. sort spec is remembered per playlist. (mostly done?)
• drag and drop reordering of tracks (Rows) when sorted by “#” (no reorder when sorted by any other column), including dis-contiguous groups (mostly done?)
• ability to drag and drop single or group from tracks list (on right) to other playlists (on left) (mostly done?)
• searches must scale to hundreds of thousands of items, and be very quick (probably never more than 500k items, yet each item may have three dozen elements which may be strings, numbers, date / times, booleans etc)
• search function will search only the shown (visible) meta data columns, does NOT search in columns that are hidden
• search must have these features. Specifically, support for quoted, numeric, targeted (only a specific sort column), and "exact"
• database updates / queries must never block UI thread (expensive ops must be on back thread, & obviously be thread safe)
• a lot of the above is kinda done: ability to convert existing DB model to new DB model (so when new app is run, old data is sucked in and converted), then on subsequent runs it reads new DB data file(s), playlists, partial drag and drop support: you'll be picking up where a previous developer left off
• note some "playlists" are not part of the main DB (not stored in main table), and need separate persistent storage, merged in viewer upon running the app, but still separately saved. eg: music stores. users should be free to delete these DB tables in the file system between runs, and not mess up the main tables. users can turn these stores on and off at will in the preferences UI, they each have their own separate playlist (store) but also they (or a subset) MAY be included (shown) within the main Library playlist along with the rest of the user's songs (so it's a little tricky). alread kinda done: updating these stores (getting new catalog) must be done in such a way as to merge new changes without invalidating the existing song IDs)
• Similarly CDs, they have a separate playlist not shown in the library, but meta data is stored for them, so next time you put i the same CD, you see it's data
• i think currently the Library playlist just shows the ENTIRE (main) db table, but it should instead show a separate, curated table containing only the tracks we want to see in the Library (QSortFilterProxyModel) (ie: don't show music store songs unless the user turns that on, don't show songs from any mounted CDs)
• in the Table View, needs to show custom controls for certain cells: colorized rows, popup menus, color picker, star-ratings, check boxes, date/time viewer, timecode
• Bonus: ability to break out a copy of any playlist (tracks list) into separate window, showing only that tracks list, window has no adornment or other controls, but may be used for drag and drop, either source or destination. list shown will update if model is changed in any other viewer (renaming, add / remove / reordering etc)

In the provided VM

• you will have my already-working shell program which has:
  • menus and windows, commands are hooked up, but may crash
  • “main browser” window shell where you will will find my preliminary explorations of creating the DB UI, and the previous worker's progress on the "to do list"
  • old DB model code that works and loads a library, converting it. current sqlite DB stored in big binary file, current playlist meta data is stored in “plist” files (CoreFoundation xml) which can be read in (sample code provided)
• The reference app (kJams Pro) is already installed in the VM.

The Point of this project is to basically re-creating the kJams app functionality in Qt (64bit). This reference app already implements all of what is described above (in a 32bit app).

Links to get you started

QSortFilterProxyModel Class
my forum post
reordering columns
reordering rows
reading order after reordering
anoter post about reordering

Milestones

Total project $5,000
10% payout at each milestone. Note that even if the "as needed" milestones are not used, you will get paid for them upon completion of project.

Split into two sections:

1. Section 1:
   1. familiarize self with code base (This may take two weeks!). ask questions.
   2. ensure loading a playlist with hundreds of thousands of items is instant (switching between playlists, remembering previous search)
      1. fixed: scroll bar pagination is by (10) instead of by (current number of visible rows - 1)
      2. fixed: shows max 60 rows instead of all rows that will fit
      3. fixed: dragging thumb to bottom of tall list seems incorrectly calibrated
   3. get rid of "QSortFilterProxyModel" entirely. use "QSqlRelationalTableModel" directly
      1. fixed: when sorted by anything other than "PLI", scroll bar has beach balls
      2. fixed: especially ensure that "User Sort Order" scrolls as smoothly as PLI, and that drag and drop works to reorder
   4. custom table view controls (colorized rows, checkbox, popup menu, color picker, star ratings, timecode, date-time)
      1. issue tracking now has it's own page
   5. optimize initial database load (delete .ksql file, cause rebuild, with large library)
2. Section 2:
   1. hook up Qt "SearchBox" to the existing search APIs (ie: capture keystrokes, cut/pastes, start a new search each time anything in search box changes, using existing APIs)
   2. implement search functionality defined here, must be fast (ie: use fast search tables?) (can negotiate breaking up this milestone after you have started work and understand scope). code already exists for the start of this, which you will use as jumping off point. be sure sorts are stable (are they?) (be sure you know what that means), and sorts must be fast, obviously, even with 300,000 items
   3. ability to load / update music stores correctly, with ability for user to delete them between runs, or turn them off / on in preferences (work has been done on this, may be mostly working already, just have to do it the Qt way and not direct access to SQLite DB as was previously done, ask me)
   4. extra milestone as needed
   5. extra milestone as needed

What's already working

Quite a lot:

• tree view
• table view with all the columns
• sorting (!?) (i didn't even do anything?)
• here's a screenshot: