Wednesday, May 22, 2013

More details Server RC4W: database

If you missed the post More details about Server RC4W, you (really) should have a look. That post talks about program's task, windows, settings window, database manager window, and about which steps are taken when scanning folders for songs and importing data to database.

This post will be more about Server RC4W. As said earlier, server is much more complicated than client, but if you do it right, searching song on client will be much more comfortable.

WARNING: wrong modification database can make program to work unexpectedly. You should have a little knowledge about access database, and should also carefully read the description in this post before doing anything.

Database file location

Server RC4W keeps information of songs title, authors, lyrics, ... in the db.mdb file, that located in the same folder of exe file. In zip-file, this file is blank. However you can download a database file that already contained list of songs of dvd midi california vol 18.

This file can be open with Microsoft Access and doesn't have password-protected.

Content of db.mdb

There is only one table: Songlist

This is the structure of this table:

  1. SID: Song id: This is the only numberic field in this table. For now, SID is defined as following:
    • 1-1000: for server RC4W to manage playing song and songs in queue.
    • 1001-999999 (million minus one): for Walaoke
    • Bigger than 1000000 (million, 7 digits) : for you to fill your number. It can be any as long as it is bigger than 10000000 and unique.
  2. Title: Song title
  3. Pathfile: Path to karaoke file. You can use absolute path (recommend) or relate path (not recommend).
  4. Pathfile_lyric: Path to lyric file (reserved, not used).
  5. Singer: Artist name
  6. Author: Author name
  7. Type: Genres
  8. Abbreviation: Reserved
  9. Lyric: A few words in the beginning of song.
  10. Title2, Singer2, Author2, Lyric2: "form 2" are used for searching purpose. For example: can search Vietnamese without diacritics or Chinese pinyin or Japanese romanji.
  11. RemoteURL, RemoteURL_lyric: reserved, not used
  12. ContentType: content type or file type
  • DVDMIDI: this means this song from DVD MIDI, and it must be correctly written as DVDMIDI. And SID must be set correctly as number in provided songs list.
  • MP3, MIDI, KAR, AVI, WMV, VOB, MKV, ... extentions of media files.
Knowing about this file content can help you check if data was correctly imported or not. Or you can by yourself modify data (with the condition that you knew what you are doing).

Priority order when searching data

When client sends commands "search", server will search with priority order as follow:
  1. SID: Song id first. Normally, you don't have to search songid (this was the purpose when I made this program), but this order is added for back compability with DVDMIDI.
  2. Title, Title2: Song title, and 2nd form of song title
  3. Author, Author2: author, and 2nd form of author name
  4. Singer, Singer2: artist, and 2nd form of artist name
  5. Lyric, Lyric2: lyric and 2nd form of lyric
Searching results in client are also sorted by this order.

Additional information files

In the post More details about Server RC4W additional information files were mentioned, about how program uses them when scanning folders. This post will tell more about this files.
NOTE: You don't have to read this part if you feel that searching by song name (karaoke-file name) is enough.

Importinfo.txt

Importinfo.txt is a specific file of this program to get additional information. This file content is actually a copy of table Songlist in db.mdb:
  • First line: Fields name of table, separated by TAB character.
  • Next lines: data of these fields.
  • Encoding: UTF8 or Unicode 16.
When program scans for media files, if it doesn't find the importinfo.txt file, it will import data to database normally, using file name as song title. The other information will be blank. This means that in client you can only search by song name

Beginning: Database is blank, you use program to scan and import data of songs to database. Then open database with Microsoft Access and fill data by manually. By saying manually, I mean you can copy from excel, text file or actually input manually. Keep in mind that you should backup database before beginning.
You have rich database and want to share: Maybe after a long time manually inputing data, you have a big database and want to share it along with karaoke files for your friends.You should create a importinfo.txt file in your karaoke folders, so that your friend can import files along with additional data.
  1. Open Database Manage
  2. Go to tab database utils
  3. Click "Get song-info for a folder". Select the folder containing karaoke files. The below table will be filled with information about karaoke files in the selected folder (subfolders are not included).
  4. Click "Export table as importinfo.txt". Program will create "importinfo.txt" file in the selected folder, and export this table into it.
  5. Now you have "importinfo.txt" for your karaoke folder. This folder now can be copied to other computer and your friend can import it and get additional data immediately, thank to you.

Lyric files

One of popular karaoke format is MP3+LRC. MP3 is music file, LRC is lyric file. LRC file contains the following information:
  • Song name
  • Author/artist
  • Lyric and timing
You can read more about this format at http://en.wikipedia.org/wiki/LRC_%28file_format%29
With RC4W, you only need to know that this program can use the LRC files having the same name as MP3 file as additional information for this MP3.

INF+MP3+XML

This is bunch of files used in star.zing.vn. It is unnecessory to talk about this file structure. Just know that if you use ZKOP or ZingToWalaoke, this file can import karaoke folders of these two applications.

Database manager - tab Database Utils

Tab Folders Management was told in post More details about Server RC4W, so it will not be told again here. This tab "Database Utils" contains some small tweak, utils for your database:
  • Check if missing files:  Use this to check if there are certain files that actually don't exist on harddisk any more. This missing files will be shown in the below table.
  • Delete items: delete all items shown in the table (delete in database of course)
  • Get songs-info for a folder; Export table as (importinfo.txt):  see above in "Importinfo.txt" section.
  • Export database: Export all database as a text file.
  • Clean database: Delete all items in datababse.
  • Test Search: test the search function (as in client)

No comments :

Post a Comment