Search filter by Skype message

Yesterday I suddenly found out that Skype logs are stored in .sqlite. Great, I thought, there will be a weekend activity.
Today I looked at Habra, found a topic dedicated to the description of the base itself - the theme , as well as to restore this base itself - the theme and mention of the program SkypeLogViewer. Remarkably, I thought, it’s time to write another upturned bicycle.
The idea is simple: sampling and filtering chats via lua - for those who want to practice a little bit in using lua, sql queries and lua-analog linq, as well as those who are not comfortable with the standard Skype search. The application itself is written in C # (WPF).
What happened - look under the cut.


So, let's start with a simple one - connecting libraries necessary for working with lua and sqlite.

The choice fell on NLua and System.Data.Sqlite respectively. For installation, use NuGet.
')
install-package nlua
install-package system.data.sqlite

For convenience and for every fireman we are doing a small class wrapper for lua

public class LuaLogic { public Lua lua = new Lua(); //     public -   C#    lua public void reg(object target, string funcname) { try { lua.RegisterFunction(funcname, target, target.GetType().GetMethod(funcname)); } catch (Exception ex) { } } // lua-   public object[] call(string lua_func, params object[] args) { try { var func = lua[lua_func] as LuaFunction; return func.Call(args); } catch (Exception ex) { return null; } } } 

And yes, I am aware that many people believe that Exception is obliged to be output - that's just the need for this in this particular case, I do not see.

I will not lay out the markup for gui, because here is the description of the GUI elements used in the code:

output - RichTextBox, for outputting various kinds of information, for example, vulgar jokes or ascii-art
runlua - Button to execute lua code. In general, you can hang on changing the file using FileSystemWatcher'a , but this is an amateur
accounts - ComboBox, which will display a list of Skype users who have ever logged on to the computer

Now let's move on to the code itself. Let's start with auxiliary functions.

 static LuaLogic logic = new LuaLogic(); public string current_path = ""; private List<Dictionary<string, object>> data; //   .    -    . private List<Dictionary<string, object>> _query(string comm) { var result = new List<Dictionary<string, object>>(); using (var db = new SQLiteConnection(@"data source=" + current_path)) { db.Open(); using (var command = new SQLiteCommand(comm, db)) { command.CommandTimeout = 999; using (var reader = command.ExecuteReader()) { while (reader.Read()) { result.Add(Enumerable.Range(0, reader.FieldCount) .ToDictionary( reader.GetName, reader.GetValue)); } } } db.Close(); } return result; } //       lua .  ,    List<Dictionary<string, object>>  lua    .    lua-,   lua-. public LuaTable genTable(List<Dictionary<string, object>> d) { logic.lua.NewTable("datatable"); var table = logic.lua.GetTable("datatable"); for(int i=0; i<d.Count; i++) { logic.lua.NewTable("f"); table[i] = logic.lua.GetTable("f"); foreach (var entry in d[i]) { ((LuaTable) table[i])[entry.Key] = entry.Value; } } return table; } /*** ,   lua ***/ //,   .     lua   . public void scanDB(string request=null) { if(data!=null) data.Clear(); data = new List<Dictionary<string, object>>(); data = _query(request??"select from_dispname,body_xml,timestamp from messages order by timestamp desc"); genTable(data); } //   RichTextBox public void _print(object obj) { Dispatcher.Invoke(() =>output.AppendText(obj + "\n")); } // 1     RichTextBox.  . public void _printblock(string text) { Dispatcher.Invoke(() => output.Document.Blocks.Add(new Paragraph(new Run(text)) { Margin = new Thickness(0) })); } // RichTextBox public void _clear() { Dispatcher.Invoke(() => output.Document.Blocks.Clear()); } 

And now - the logic of the application!

 public MainWindow() { InitializeComponent(); //    .        -   ,      var searchpath = Environment.ExpandEnvironmentVariables("%AppData%\\Skype"); var dirs = Directory.GetDirectories(searchpath); //       ,       var userlist = dirs.Where(dir => File.Exists(dir + "\\main.db")).Select(x=>x.Replace(searchpath+"\\", "")).ToList(); accounts.ItemsSource = userlist; accounts.SelectedItem = accounts.Items[0]; //           ComboBox' accounts.SelectionChanged += (sender, args) => current_path = Environment.ExpandEnvironmentVariables("%AppData%\\Skype") + "\\" + accounts.SelectedItem + "\\main.db"; if(userlist.Count>0) current_path = Environment.ExpandEnvironmentVariables("%AppData%\\Skype") + "\\" + userlist[0] + "\\main.db"; else { _print("    ,      ?"); } //      Lua logic.reg(this, "_print"); logic.reg(this, "_clear"); logic.reg(this, "_printblock"); logic.reg(this, "scanDB"); runlua.Click += (sender, args) => { try { new Thread(() => { // 2 -    linq- where  select , ,  . Lua-linq         <a href="http://codea.io/talk/discussion/618/linq-for-lua-functional-collection-class/p1"></a>. logic.lua.DoFile(@"scripts\pseudolinq.lua"); logic.lua.DoFile(@"scripts\script.lua"); // ,    lua-. , ,          lua- logic.call("search_pattern"); }).Start(); } catch (Exception ex) { _printblock(ex.Message); } }; //  ,  ,           Closing += (sender, args) => Process.GetCurrentProcess().Kill(); } 

Well, for a snack - lua-code.

pseudolinq.lua

 LinqArray = {} function LinqArray:new( arr ) Ret = {} Ret.arr = arr; setmetatable( Ret , self ) self.__index = self; return Ret; end --[[function LinqArray:init(items) if items then self:addRange(items) end end]]-- function LinqArray:add(item) table.insert(self.arr, item) end function LinqArray:addRange(items) for k,v in ipairs(items) do self.arr:add(v) end end function LinqArray:where(func) local results = {}; for k, v in ipairs(self.arr) do if func(v) then table.insert(results, v); end end return LinqArray:new(results) end function LinqArray:select(func) local results = {} for k, v in ipairs(self.arr) do _print(func(v)); table.insert(results, func(v)); end return LinqArray:new(results) end 

script.lua

 function search_pattern() --     _clear(); local f = LinqArray:new(datatable); --  - linq-  local filtered = f:where(function(x) return string.len(x["from_dispname"])>1; end):select(function(x) return x["from_dispname"]; end); --     .  .      ,   , .   -  . local i=0; for i=1,#filtered.arr,1 do local arg = filtered.arr[i]; _printblock(arg["from_dispname"]..": "); _print(arg["body_xml"]); end end --,   .       .   ,    1 ,       lua,    if(datatable==nil) then scanDB("select * from messages limit 100"); end 

I will stop a little more in detail on the filter.
In general, it is not necessary to do it in a linq-like format, and the filter itself could be made a query - but this is also an “abnormal programming” hub, you need to add something non-obvious.
Stop is complete.

Basically, that's all.
Thanks for attention!