Solved

skype conversations for long videos

Posted on 2013-12-05
4
898 Views
Last Modified: 2013-12-07
this is the microsoft skype database create statement
Skype using sqlite but because there are no table relationships; an oracle or sql server answer may work.
CREATE TABLE Accounts (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, status INTEGER, pwdchangestatus INTEGER, logoutreason INTEGER, commitstatus INTEGER, suggested_skypename TEXT, skypeout_balance_currency TEXT, skypeout_balance INTEGER, skypeout_precision INTEGER, skypein_numbers TEXT, subscriptions TEXT, cblsyncstatus INTEGER, offline_callforward TEXT, chat_policy INTEGER, skype_call_policy INTEGER, pstn_call_policy INTEGER, avatar_policy INTEGER, buddycount_policy INTEGER, timezone_policy INTEGER, webpresence_policy INTEGER, phonenumbers_policy INTEGER, voicemail_policy INTEGER, authrequest_policy INTEGER, ad_policy INTEGER, partner_optedout TEXT, service_provider_info TEXT, registration_timestamp INTEGER, nr_of_other_instances INTEGER, partner_channel_status TEXT, owner_under_legal_age INTEGER, type INTEGER, skypename TEXT, pstnnumber TEXT, fullname TEXT, birthday INTEGER, gender INTEGER, languages TEXT, country TEXT, province TEXT, city TEXT, phone_home TEXT, phone_office TEXT, phone_mobile TEXT, emails TEXT, homepage TEXT, about TEXT, profile_timestamp INTEGER, received_authrequest TEXT, displayname TEXT, refreshing INTEGER, given_authlevel INTEGER, aliases TEXT, authreq_timestamp INTEGER, mood_text TEXT, timezone INTEGER, nrof_authed_buddies INTEGER, ipcountry TEXT, given_displayname TEXT, availability INTEGER, lastonline_timestamp INTEGER, capabilities BLOB, avatar_image BLOB, assigned_speeddial TEXT, lastused_timestamp INTEGER, authrequest_count INTEGER, assigned_comment TEXT, alertstring TEXT, avatar_timestamp INTEGER, mood_timestamp INTEGER, rich_mood_text TEXT, synced_email BLOB, set_availability INTEGER, options_change_future BLOB, cbl_profile_blob BLOB, authorized_time INTEGER, sent_authrequest TEXT, sent_authrequest_time INTEGER, sent_authrequest_serial INTEGER, buddyblob BLOB, cbl_future BLOB, node_capabilities INTEGER, node_capabilities_and INTEGER, revoked_auth INTEGER, added_in_shared_group INTEGER, in_shared_group INTEGER, authreq_history BLOB, profile_attachments BLOB, stack_version INTEGER, offline_authreq_id INTEGER, verified_email BLOB, verified_company BLOB, flamingo_xmpp_status INTEGER, federated_presence_policy INTEGER, liveid_membername TEXT, uses_jcs INTEGER, roaming_history_enabled INTEGER, cobrand_id INTEGER); 
CREATE TABLE Alerts (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, timestamp INTEGER, partner_name TEXT, is_unseen INTEGER, partner_id INTEGER, partner_event TEXT, partner_history TEXT, partner_header TEXT, partner_logo TEXT, meta_expiry INTEGER, message_header_caption TEXT, message_header_title TEXT, message_header_subject TEXT, message_header_cancel TEXT, message_header_later TEXT, message_content TEXT, message_footer TEXT, message_button_caption TEXT, message_button_uri TEXT, message_type INTEGER, window_size INTEGER, chatmsg_guid BLOB, notification_id INTEGER, event_flags INTEGER, extprop_hide_from_history INTEGER); 
CREATE TABLE AppSchemaVersion (ClientVersion TEXT NOT NULL, SQLiteSchemaVersion INTEGER NOT NULL, SchemaUpdateType INTEGER NOT NULL); 
CREATE TABLE CallMembers (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, identity TEXT, dispname TEXT, languages TEXT, call_duration INTEGER, price_per_minute INTEGER, price_precision INTEGER, price_currency TEXT, payment_category TEXT, type INTEGER, status INTEGER, failurereason INTEGER, sounderror_code INTEGER, soundlevel INTEGER, pstn_statustext TEXT, pstn_feedback TEXT, forward_targets TEXT, forwarded_by TEXT, debuginfo TEXT, videostatus INTEGER, target_identity TEXT, mike_status INTEGER, is_read_only INTEGER, quality_status INTEGER, call_name TEXT, transfer_status INTEGER, transfer_active INTEGER, transferred_by TEXT, transferred_to TEXT, guid TEXT, next_redial_time INTEGER, nrof_redials_done INTEGER, nrof_redials_left INTEGER, transfer_topic TEXT, real_identity TEXT, start_timestamp INTEGER, is_conference INTEGER, quality_problems TEXT, identity_type INTEGER, country TEXT, creation_timestamp INTEGER, stats_xml TEXT, is_premium_video_sponsor INTEGER, is_multiparty_video_capable INTEGER, recovery_in_progress INTEGER, nonse_word TEXT, pk_status INTEGER, call_db_id INTEGER, prime_status INTEGER, nr_of_delivered_push_notifications INTEGER, call_session_guid TEXT, version_string TEXT, ip_address TEXT, is_video_codec_compatible INTEGER, is_seamlessly_upgraded_call INTEGER); 
CREATE TABLE Calls (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, begin_timestamp INTEGER, topic TEXT, is_muted INTEGER, is_unseen_missed INTEGER, host_identity TEXT, mike_status INTEGER, duration INTEGER, soundlevel INTEGER, access_token TEXT, active_members INTEGER, is_active INTEGER, name TEXT, video_disabled INTEGER, joined_existing INTEGER, server_identity TEXT, vaa_input_status INTEGER, is_incoming INTEGER, is_conference INTEGER, is_on_hold INTEGER, start_timestamp INTEGER, quality_problems TEXT, current_video_audience TEXT, premium_video_status INTEGER, premium_video_is_grace_period INTEGER, is_premium_video_sponsor INTEGER, premium_video_sponsor_list TEXT, old_members BLOB, partner_handle TEXT, partner_dispname TEXT, type INTEGER, status INTEGER, failurereason INTEGER, failurecode INTEGER, pstn_number TEXT, old_duration INTEGER, conf_participants BLOB, pstn_status TEXT, members BLOB, conv_dbid INTEGER); 
CREATE TABLE ChatMembers (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, chatname TEXT, identity TEXT, role INTEGER, is_active INTEGER, cur_activities INTEGER, adder TEXT); 
CREATE TABLE Chats (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, name TEXT, options INTEGER, friendlyname TEXT, description TEXT, timestamp INTEGER, activity_timestamp INTEGER, dialog_partner TEXT, adder TEXT, type INTEGER, mystatus INTEGER, myrole INTEGER, posters TEXT, participants TEXT, applicants TEXT, banned_users TEXT, name_text TEXT, topic TEXT, topic_xml TEXT, guidelines TEXT, picture BLOB, alertstring TEXT, is_bookmarked INTEGER, passwordhint TEXT, unconsumed_suppressed_msg INTEGER, unconsumed_normal_msg INTEGER, unconsumed_elevated_msg INTEGER, unconsumed_msg_voice INTEGER, activemembers TEXT, state_data BLOB, lifesigns INTEGER, last_change INTEGER, first_unread_message INTEGER, pk_type INTEGER, dbpath TEXT, split_friendlyname TEXT, conv_dbid INTEGER); 
CREATE TABLE ContactGroups (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, type INTEGER, custom_group_id INTEGER, given_displayname TEXT, nrofcontacts INTEGER, nrofcontacts_online INTEGER, given_sortorder INTEGER, type_old INTEGER, proposer TEXT, description TEXT, associated_chat TEXT, members TEXT, cbl_id INTEGER, cbl_blob BLOB, fixed INTEGER, keep_sharedgroup_contacts INTEGER, chats TEXT, extprop_is_hidden INTEGER, extprop_sortorder_value INTEGER, extprop_is_expanded INTEGER); 
CREATE TABLE "Contacts" (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, type INTEGER, skypename TEXT, pstnnumber TEXT, aliases TEXT, fullname TEXT, birthday INTEGER, gender INTEGER, languages TEXT, country TEXT, province TEXT, city TEXT, phone_home TEXT, phone_office TEXT, phone_mobile TEXT, emails TEXT, hashed_emails TEXT, homepage TEXT, about TEXT, avatar_image BLOB, mood_text TEXT, rich_mood_text TEXT, timezone INTEGER, capabilities BLOB, profile_timestamp INTEGER, nrof_authed_buddies INTEGER, ipcountry TEXT, avatar_timestamp INTEGER, mood_timestamp INTEGER, received_authrequest TEXT, authreq_timestamp INTEGER, lastonline_timestamp INTEGER, availability INTEGER, displayname TEXT, refreshing INTEGER, given_authlevel INTEGER, given_displayname TEXT, assigned_speeddial TEXT, assigned_comment TEXT, alertstring TEXT, lastused_timestamp INTEGER, authrequest_count INTEGER, assigned_phone1 TEXT, assigned_phone1_label TEXT, assigned_phone2 TEXT, assigned_phone2_label TEXT, assigned_phone3 TEXT, assigned_phone3_label TEXT, buddystatus INTEGER, isauthorized INTEGER, popularity_ord INTEGER, external_id TEXT, external_system_id TEXT, isblocked INTEGER, authorization_certificate BLOB, certificate_send_count INTEGER, account_modification_serial_nr INTEGER, saved_directory_blob BLOB, nr_of_buddies INTEGER, server_synced INTEGER, contactlist_track INTEGER, last_used_networktime INTEGER, authorized_time INTEGER, sent_authrequest TEXT, sent_authrequest_time INTEGER, sent_authrequest_serial INTEGER, buddyblob BLOB, cbl_future BLOB, node_capabilities INTEGER, revoked_auth INTEGER, added_in_shared_group INTEGER, in_shared_group INTEGER, authreq_history BLOB, profile_attachments BLOB, stack_version INTEGER, offline_authreq_id INTEGER, node_capabilities_and INTEGER, authreq_crc INTEGER, authreq_src INTEGER, pop_score INTEGER, authreq_nodeinfo BLOB, main_phone TEXT, unified_servants TEXT, phone_home_normalized TEXT, phone_office_normalized TEXT, phone_mobile_normalized TEXT, sent_authrequest_initmethod INTEGER, authreq_initmethod INTEGER, verified_email BLOB, verified_company BLOB, sent_authrequest_extrasbitmask INTEGER, liveid_cid TEXT, extprop_seen_birthday INTEGER, extprop_sms_target INTEGER, extprop_external_data TEXT); 
CREATE TABLE Conversations (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, identity TEXT, type INTEGER, live_host TEXT, live_start_timestamp INTEGER, live_is_muted INTEGER, alert_string TEXT, is_bookmarked INTEGER, given_displayname TEXT, displayname TEXT, local_livestatus INTEGER, inbox_timestamp INTEGER, inbox_message_id INTEGER, unconsumed_suppressed_messages INTEGER, unconsumed_normal_messages INTEGER, unconsumed_elevated_messages INTEGER, unconsumed_messages_voice INTEGER, active_vm_id INTEGER, context_horizon INTEGER, consumption_horizon INTEGER, last_activity_timestamp INTEGER, active_invoice_message INTEGER, spawned_from_convo_id INTEGER, pinned_order INTEGER, creator TEXT, creation_timestamp INTEGER, my_status INTEGER, opt_joining_enabled INTEGER, opt_access_token TEXT, opt_entry_level_rank INTEGER, opt_disclose_history INTEGER, opt_history_limit_in_days INTEGER, opt_admin_only_activities INTEGER, passwordhint TEXT, meta_name TEXT, meta_topic TEXT, meta_guidelines TEXT, meta_picture BLOB, premium_video_status INTEGER, premium_video_is_grace_period INTEGER, guid TEXT, dialog_partner TEXT, meta_description TEXT, premium_video_sponsor_list TEXT, chat_dbid INTEGER, history_horizon INTEGER, extprop_profile_height INTEGER, extprop_chat_width INTEGER, extprop_chat_left_margin INTEGER, extprop_chat_right_margin INTEGER, extprop_entry_height INTEGER, extprop_windowpos_x INTEGER, extprop_windowpos_y INTEGER, extprop_windowpos_w INTEGER, extprop_windowpos_h INTEGER, extprop_window_maximized INTEGER, extprop_window_detached INTEGER, extprop_pinned_order INTEGER, extprop_new_in_inbox INTEGER, extprop_tab_order INTEGER, extprop_video_layout INTEGER, extprop_video_chat_height INTEGER, extprop_chat_avatar INTEGER, extprop_consumption_timestamp INTEGER, extprop_form_visible INTEGER, extprop_recovery_mode INTEGER, mcr_caller TEXT, history_sync_state TEXT, picture TEXT, thread_version TEXT, consumption_horizon_set_at INTEGER, alt_identity TEXT, is_p2p_migrated INTEGER, in_migrated_thread_since INTEGER, is_blocked INTEGER); 
CREATE TABLE DbMeta (key TEXT NOT NULL PRIMARY KEY, value TEXT); 
CREATE TABLE LegacyMessages (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER); 
CREATE TABLE Messages (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, convo_id INTEGER, chatname TEXT, author TEXT, from_dispname TEXT, author_was_live INTEGER, guid BLOB, dialog_partner TEXT, timestamp INTEGER, type INTEGER, sending_status INTEGER, consumption_status INTEGER, edited_by TEXT, edited_timestamp INTEGER, param_key INTEGER, param_value INTEGER, body_xml TEXT, identities TEXT, reason TEXT, leavereason INTEGER, participant_count INTEGER, error_code INTEGER, chatmsg_type INTEGER, chatmsg_status INTEGER, body_is_rawxml INTEGER, oldoptions INTEGER, newoptions INTEGER, newrole INTEGER, pk_id INTEGER, crc INTEGER, remote_id INTEGER, call_guid TEXT, extprop_contact_review_date TEXT, extprop_contact_received_stamp INTEGER, extprop_contact_reviewed INTEGER); 
CREATE TABLE Participants (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, convo_id INTEGER, identity TEXT, rank INTEGER, requested_rank INTEGER, text_status INTEGER, voice_status INTEGER, video_status INTEGER, live_identity TEXT, live_price_for_me TEXT, live_fwd_identities TEXT, live_start_timestamp INTEGER, sound_level INTEGER, debuginfo TEXT, next_redial_time INTEGER, nrof_redials_left INTEGER, last_voice_error TEXT, quality_problems TEXT, live_type INTEGER, live_country TEXT, transferred_by TEXT, transferred_to TEXT, adder TEXT, is_premium_video_sponsor INTEGER, is_multiparty_video_capable INTEGER, live_identity_to_use TEXT, livesession_recovery_in_progress INTEGER, extprop_default_identity INTEGER, last_leavereason INTEGER, is_multiparty_video_updatable INTEGER, real_identity TEXT, live_ip_address TEXT, adding_in_progress_since INTEGER, is_video_codec_compatible INTEGER, is_seamlessly_upgraded_call INTEGER); 
CREATE TABLE SMSes (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, type INTEGER, status INTEGER, failurereason INTEGER, is_failed_unseen INTEGER, timestamp INTEGER, price INTEGER, price_precision INTEGER, price_currency TEXT, reply_to_number TEXT, target_numbers TEXT, target_statuses BLOB, body TEXT, chatmsg_id INTEGER, extprop_hide_from_history INTEGER, extprop_extended INTEGER, identity TEXT, notification_id INTEGER, event_flags INTEGER, reply_id_number TEXT, convo_name TEXT, outgoing_reply_type INTEGER, error_category INTEGER); 
CREATE TABLE Transfers (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, type INTEGER, partner_handle TEXT, partner_dispname TEXT, status INTEGER, failurereason INTEGER, starttime INTEGER, finishtime INTEGER, filepath TEXT, filename TEXT, filesize TEXT, bytestransferred TEXT, bytespersecond INTEGER, chatmsg_guid BLOB, chatmsg_index INTEGER, convo_id INTEGER, pk_id INTEGER, nodeid BLOB, last_activity INTEGER, flags INTEGER, old_status INTEGER, old_filepath INTEGER, accepttime INTEGER, extprop_localfilename TEXT, extprop_hide_from_history INTEGER, extprop_window_visible INTEGER, extprop_handled_by_chat INTEGER, parent_id INTEGER, offer_send_list TEXT); 
CREATE TABLE "VideoMessages" (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, qik_id BLOB, attached_msg_ids TEXT, sharing_id TEXT, status INTEGER, vod_status INTEGER, vod_path TEXT, local_path TEXT, public_link TEXT, progress INTEGER, title TEXT, description TEXT, author TEXT, creation_timestamp INTEGER); 
CREATE TABLE Videos (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, status INTEGER, error TEXT, debuginfo TEXT, dimensions TEXT, media_type INTEGER, duration_1080 INTEGER, duration_720 INTEGER, duration_hqv INTEGER, duration_vgad2 INTEGER, duration_ltvgad2 INTEGER, timestamp INTEGER, hq_present INTEGER, duration_ss INTEGER, ss_timestamp INTEGER, convo_id INTEGER, device_path TEXT); 
CREATE TABLE Voicemails (id INTEGER NOT NULL PRIMARY KEY, is_permanent INTEGER, type INTEGER, partner_handle TEXT, partner_dispname TEXT, status INTEGER, failurereason INTEGER, subject TEXT, timestamp INTEGER, duration INTEGER, allowed_duration INTEGER, playback_progress INTEGER, convo_id INTEGER, chatmsg_guid BLOB, notification_id INTEGER, flags INTEGER, size INTEGER, path TEXT, failures INTEGER, vflags INTEGER, xmsg TEXT, extprop_hide_from_history INTEGER); 

Open in new window



Because there are limited foreign keys and hidden table relationships, what is your best guess to delete video conversations that are longer than 1 hour.  Which table rows should be deleted.

Background: my php client does not want others to know that he is having 1 hour video conversations.  

I know that it may not be possible to be done.
I also know that it is outside of my php job description.
Any guess?
0
Comment
Question by:rgb192
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 167 total points
Comment Utility
The best way to figure this out is to have a video message that is in the database that is over 1 hour.  Once you have that, here is where I would go.

Look in the VIDEOS table.  There are a bunch of duration columns in there.  That is probably where you will find what you are looking for.

In the VIDEOS table, there is a CONVO_ID column.  My guess is that you can join that to the CONVO_ID in the MESSAGES table and the ID in the CONVERSATIONS table to delete those records.  Not sure how you can link it to the VIDEOMESSAGES table, if there is even a record in there (one possibility is that they have the same ID, but I wouldn't count on that).

Now, those tables may only store saved messages, and there may be something in the file system too (there is a LOCAL_PATH column in VIDEOMESSAGES).

You may also have to look in the CALLS and CALLMEMBERS tables.  Those may have just the record of the call.  Not sure on the link between them (again ID may be the same).  There are a CALL_DURATION and VIDEOSTATUS columns in CALLMEMBERS that should help you find the specific records you are looking for.

It really is a guessing game and you may not remove all traces of what you are looking for, but you should break the records enough that any standard reporting would miss them.
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 333 total points
Comment Utility
My suggestion would be to clear the database completely (on a test computer maybe) and have video conversations for 1 minute.

Then look at all the tables in the database.
Then have another video conversation for 2 minutes and look again. This should give you a good idea of how the database is used. Don't forget to check all the duration fields when you deploy though.

You can also use this tool specifically to look at the Skype databases.
http://suurjaak.github.io/Skyperious/

Not to jump to conclusions, but I can't think of many other reasons why someone would want to hide the fact that he is having long Skype video chats...... Perhaps instead of a database cleanser you should recommend your client to a good marriage counselor?
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 333 total points
Comment Utility
Here's the best source I can find on the database format
http://betrayedspousesclub.blogspot.com/2012/07/skype-maindb.html
You need to sign in to Google to see the spreadsheets, but they are just the fields you already know about. They don't contain any comments or details. (Note the name of the website..... Your client isn't the only one with this issue it would seem)
0
 

Author Closing Comment

by:rgb192
Comment Utility
Thanks for the information so I can tell my client that it is a big job.

I will open another question if I actually end up doing this job.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now