public static Int32 GetThematicID(String thematicName) { SQLiteConnection connection = DatabaseConnectionHandler.Invoke(); String sql = $"select count(*) from {TableNames.Thematic} where s_name = '{thematicName}'"; int thematicId = -1; if (connection.QueryFirst <int>(sql) == 0) { String sqlInsert = $"insert into {TableNames.Thematic} (s_name) values ('{thematicName}')"; connection.Execute(sqlInsert); } sql = $"select id from {TableNames.Thematic} where s_name = '{thematicName}'"; thematicId = connection.QueryFirst <Int32>(sql); return(thematicId); }
private void ButtonRemoveAllData_OnClick(object sender, RoutedEventArgs e) { File.Delete(Names.Database); if (Directory.Exists("temp")) { Directory.Delete("temp", true); } SQLiteConnection connection = DatabaseConnectionHandler.Invoke(); connection.Execute(Queries.FullDBCreationQuery); DataBase.SaveDefaultNewsSources(); MainWindow.LastDBIdent = 0; this.Close(); }
public static void FillSourceNames(List <Newspaper> newspapers) { SQLiteConnection connection = DatabaseConnectionHandler.Invoke(); List <NewsSource> sources = connection.Query <NewsSource>($"select * from {TableNames.NewsSource} INNER JOIN {TableNames.SourceSiteName} names ON i_site_name_id = names.id").ToList(); foreach (Newspaper newspaper in newspapers) { foreach (NewsSource source in sources) { if (source.id == newspaper.i_source_id) { newspaper.SourceName = source.s_name; break; } } } }
/// <summary> /// Получение максимального индекса в БД чтобы локально их присваивать /// </summary> /// <returns></returns> private static Int32 GetLastDBIdent() { SQLiteConnection connection = DatabaseConnectionHandler.Invoke(); int maxIdent; try { maxIdent = connection.QueryFirst <Int32>($"select max(id) from {TableNames.Newspapers}"); } catch (Exception) { Console.WriteLine("set max id = 0"); maxIdent = 0; } maxIdent++; // Чтобы следующий элемент имел отличный от найденного id return(maxIdent); }
public void Worker_ParseFullNewsFinished(object sender, RunWorkerCompletedEventArgs e) { List <Newspaper> items = e.Result as List <Newspaper>; //SetHeadersSourceList(items); SQLiteConnection connection = DatabaseConnectionHandler.Invoke(); String sql = $"select count(*) from {TableNames.NewspaperFullText}" + $" where i_is_parsed=1;"; Int32 parsed = connection.QueryFirst <Int32>(sql); sql = $"select count(*) from {TableNames.NewspaperFullText};"; Int32 allCount = connection.QueryFirst <Int32>(sql); MessageBox.Show($"Обновление завершено. Загружены полные версии для {parsed} из {allCount}."); ProgressBarParsingNews.Value = 0; _isFullNewsUpdatingNow = false; ButtonUpdate.IsEnabled = true; ButtonUpdate.Content = "Обновить"; ButtonSettings.IsEnabled = true; }
public static void SaveNewsSource(NewsSource source) { SQLiteConnection connection = DatabaseConnectionHandler.Invoke(); string sql = $"insert into {TableNames.SourceSiteName} (s_name) values ('{source.s_name}');"; connection.Execute(sql); //sql = $"select id from {TableNames.SourceSiteName} where rowid=last_insert_rowid();"; sql = $"select max(id) from {TableNames.SourceSiteName};"; int siteNameId = connection.QueryFirst <int>(sql); sql = $"insert into {TableNames.NetworkAddress} (s_url) values ('{source.s_rss_link}');"; connection.Execute(sql); //sql = $"select id from {TableNames.NetworkAddress} where rowid=last_insert_rowid();"; sql = $"select max(id) from {TableNames.NetworkAddress};"; int networkAddressId = connection.QueryFirst <int>(sql); sql = $"insert into {TableNames.NewsSource} (i_site_name_id, i_rss_url_id, i_is_chosen) " + $"values ({siteNameId}, {networkAddressId}, 1);"; connection.Execute(sql); }
public static void SaveImage(ImageDB image) { SQLiteConnection connection = DatabaseConnectionHandler.InvokeMultiple(); string sql = $"insert into {TableNames.NetworkAddress} (s_url) values ('{image.s_url}')"; connection.Execute(sql); int networkId = connection.QueryFirst <int>( $"select id from {TableNames.NetworkAddress} where s_url='{image.s_url}'"); connection.Execute($"insert into {TableNames.LocalFileAddress} " + $"(s_path) values ('{image.s_path}')"); int localId = connection.QueryFirst <int>( $"select id from {TableNames.LocalFileAddress} where s_path='{image.s_path}'"); connection.Execute($"insert into {TableNames.Images} (i_newspaper_id, " + $"i_local_address_id, i_network_address_id) values " + $"({image.i_newspaper_id}, {localId}, {networkId})"); }
public static List <Newspaper> LoadNewspapers(SQLiteConnection connection = null) { if (connection == null) { connection = DatabaseConnectionHandler.Invoke(); } String sql = $"select news.id id, news.s_date s_date, descs.s_text " + $"s_description, heads.s_text s_header, i_thematic_id," + $" i_source_id, i_is_read, s_link from (({TableNames.Newspapers} " + $"news INNER JOIN {TableNames.NewspaperHeader} heads " + $"ON news.i_header_id = heads.id) INNER JOIN {TableNames.NewspaperDescription} " + $"descs ON news.i_description_id = descs.id)"; List <Newspaper> newspapers = connection.Query <Newspaper>(sql).ToList(); FillSourceNames(newspapers); newspapers.Sort((np1, np2) => np2.s_date.CompareTo(np1.s_date)); return(newspapers); }
private void CreateReport(string filePath) { SQLiteConnection connection = DatabaseConnectionHandler.Invoke(); var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("Новости"); ws.Cell("A1").Value = "Дата"; ws.Cell("B1").Value = "Название"; ws.Cell("C1").Value = "Краткое описание"; ws.Cell("D1").Value = "Полный текст (формат HTML)"; ws.Cell("E1").Value = "Название источника"; List <Newspaper> news = DataBase.LoadNewspapers(); for (int i = 0; i < news.Count; i++) // сдвиг на 2 вверх для работы с ячейками экселя { ws.Cell($"A{i + 2}").Value = news[i].s_date; ws.Cell($"B{i + 2}").Value = news[i].s_header; ws.Cell($"C{i + 2}").Value = news[i].s_description; ws.Cell($"D{i + 2}").Value = connection.QueryFirst <String>($"select s_text from {TableNames.NewspaperFullText} where id={news[i].id}"); ws.Cell($"E{i + 2}").Value = news[i].SourceName; } var rngTable = ws.Range("A1:E1"); rngTable.Style.Font.Bold = true; var content = ws.Range($"A1:E{news.Count + 1}"); content.Style.Border.InsideBorder = XLBorderStyleValues.Thin; content.Style.Border.OutsideBorder = XLBorderStyleValues.Thick; ws.Columns(1, 5).AdjustToContents(); File.Delete(filePath); wb.SaveAs(filePath); }
private void ButtonLoadFullDB_Click(object sender, RoutedEventArgs e) { OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.Title = "Выберите файл резервной копии"; openFileDialog.Filter = "Резервная копия базы данных (*.ofrebak)|*.ofrebak"; openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments); if (openFileDialog.ShowDialog() == false) { return; } using (Ionic.Zip.ZipFile zip1 = Ionic.Zip.ZipFile.Read(openFileDialog.FileName)) { foreach (Ionic.Zip.ZipEntry entry in zip1) { entry.Extract(Directory.GetCurrentDirectory(), ExtractExistingFileAction.DoNotOverwrite); } } SQLiteConnection externalConnection = DatabaseConnectionHandler.InvokeWithPath($"{Directory.GetCurrentDirectory()}\\{Names.TempBDFile}"); SQLiteConnection localConnection = DatabaseConnectionHandler.Invoke(); List <Newspaper> externalNews = DataBase.LoadNewspapers(externalConnection); string sql; foreach (Newspaper np in externalNews) // заполнение новостей данными { sql = $"select i_is_parsed from {TableNames.NewspaperFullText} where id={np.id}"; np.i_is_parsed = externalConnection.QueryFirst <bool>(sql); sql = $"select s_text from {TableNames.NewspaperFullText} where id={np.id}"; np.s_full_text = externalConnection.QueryFirst <string>(sql); sql = $"select s_name from {TableNames.Thematic} where id={np.i_thematic_id}"; string thematic = externalConnection.QueryFirst <string>(sql); np.i_thematic_id = DataBase.GetThematicID(thematic); } List <NewsSource> sources = DataBase.LoadNewsSources(externalConnection); sql = $"select image.id id, i_newspaper_id, s_path," + $" s_url from {TableNames.Images} " + $"INNER JOIN {TableNames.LocalFileAddress} " + $"ON image.i_local_address_id = local_address.id " + $"INNER JOIN {TableNames.NetworkAddress} ON " + $"network_address.id = image.i_network_address_id;"; List <ImageDB> images = externalConnection.Query <ImageDB>(sql).ToList(); foreach (NewsSource source in sources) { sql = $"select count(*) from {TableNames.NetworkAddress} " + $"where s_url='{source.s_rss_link}';"; int cnt = localConnection.QueryFirst <int>(sql); if (cnt == 0) { DataBase.SaveNewsSource(source); } } foreach (Newspaper np in externalNews) // статьи получают актуальные локальные айди и сохраняются в базе { List <ImageDB> newsImages = GetNewspaperImages(np.id, images); DataBase.SaveNewspaper(np); foreach (ImageDB image in newsImages) { sql = $"select count(*) from {TableNames.LocalFileAddress} " + $"where s_path = '{image.s_path}';"; int count = localConnection.QuerySingle <int>(sql); if (count != 0) { continue; } DataBase.SaveImage(image); } } File.Delete(Names.TempBDFile); MessageBox.Show("Данные успешно загружены"); }
private void ButtonBase_OnClick(object sender, RoutedEventArgs e) { List <JoinTableElementPack> lst = ListBoxJoins.ItemsSource as List <JoinTableElementPack>; bool execauteSql = false; List <string> tablesInUse = new List <string>(); string sql = $"SELECT # FROM"; foreach (var pack in lst) { if (pack.Element.FirstTable != null && pack.Element.SecondTable != null && pack.Element.FirstField != null && pack.Element.SecondField != null && pack.Element.FirstTable != "-" && pack.Element.SecondTable != "-" && pack.Element.FirstField != "-" && pack.Element.SecondField != "-") // проверка на завершённость строки джойнов { if (execauteSql) { if (!tablesInUse.Contains(pack.Element.FirstTable) && !tablesInUse.Contains(pack.Element.SecondTable)) { MessageBox.Show("Попытка соединить две пары разных таблиц в одном запросе"); return; } } if (!execauteSql) { sql += $" {pack.Element.FirstTable}"; execauteSql = true; } if (pack.Element.FirstTable == pack.Element.SecondTable) { MessageBox.Show("Попытка объединить таблицу с ней же."); return; } if (tablesInUse.Contains(pack.Element.SecondTable)) { sql += $" INNER JOIN {pack.Element.FirstTable} ON " + $"{pack.Element.FirstTable}.{pack.Element.FirstField} = " + $"{pack.Element.SecondTable}.{pack.Element.SecondField}"; } else { sql += $" INNER JOIN {pack.Element.SecondTable} ON " + $"{pack.Element.FirstTable}.{pack.Element.FirstField} = " + $"{pack.Element.SecondTable}.{pack.Element.SecondField}"; } tablesInUse.Add(pack.Element.FirstTable); tablesInUse.Add(pack.Element.SecondTable); } } if (!execauteSql) { foreach (var pack in lst) { if (pack.Element.FirstTable != null && pack.Element.FirstTable != "-") { sql = $"SELECT # FROM {pack.Element.FirstTable}"; execauteSql = true; tablesInUse.Add(pack.Element.FirstTable); break; } if (pack.Element.SecondTable != null && pack.Element.SecondTable != "-") { sql = $"SELECT # FROM {pack.Element.SecondTable}"; execauteSql = true; tablesInUse.Add(pack.Element.SecondTable); break; } } } List <string> selectedColumns = GetSelectedColumns(); if (selectedColumns.Count < 1) { MessageBox.Show("Выберите хотя бы один столбец для вывода."); return; } sql = PerformSelectedColumns(sql, selectedColumns); sql = PerformConditions(sql); if (execauteSql) { try { System.Data.DataTable table = new System.Data.DataTable(); SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(sql, DatabaseConnectionHandler.Invoke()); dataAdapter.Fill(table); DataGridResult.ItemsSource = table.DefaultView; } catch (Exception exception) { MessageBox.Show(sql, "Ошибка: " + exception.Message); } } else { MessageBox.Show( "Для выполнения запроса выберите таблицу, либо установите значения для объединения таблиц."); } //MessageBox.Show(sql); }
private void BuildFullTextView(Newspaper np) { GridScrollView.Children.Clear(); SQLiteConnection connection = DatabaseConnectionHandler.Invoke(); String sql = $"select s_text from {TableNames.NewspaperFullText} " + $"where id={np.id}"; string fullText = ""; try { fullText = connection.QueryFirst <String>(sql); } catch (Exception ex) { Console.WriteLine("full text loading error, np id=" + np.id); } String thematic = connection.QueryFirst <String>( $"select s_name from {TableNames.Thematic} where id={np.i_thematic_id}"); GridScrollView.RowDefinitions.Add(new RowDefinition()); GridScrollView.VerticalAlignment = VerticalAlignment.Top; var tb = new TextBlock(); tb.Inlines.Clear(); Hyperlink hyperLink = new Hyperlink() { NavigateUri = new Uri(np.s_link) }; hyperLink.RequestNavigate += Hyperlink_RequestNavigate; hyperLink.Foreground = (SolidColorBrush)(new BrushConverter().ConvertFrom("#d9d9d9")); hyperLink.Inlines.Add(new Run("Открыть в браузере") { FontWeight = FontWeights.Normal }); tb.Inlines.Add(hyperLink); tb.FontSize = 20; tb.Foreground = (SolidColorBrush)(new BrushConverter().ConvertFrom("#d9d9d9")); GridScrollView.Children.Add(tb); Grid.SetRow(tb, GridScrollView.RowDefinitions.Count - 1); Grid.SetColumnSpan(tb, 2); if (thematic != "-") { var tb6 = new TextBlock(); tb6.Text = thematic; tb6.TextAlignment = TextAlignment.Right; tb6.FontSize = 20; tb6.Foreground = (SolidColorBrush)(new BrushConverter().ConvertFrom("#d9d9d9")); GridScrollView.Children.Add(tb6); Grid.SetRow(tb6, GridScrollView.RowDefinitions.Count - 1); Grid.SetColumn(tb6, 1); } GridScrollView.RowDefinitions.Add(new RowDefinition()); var rect = new System.Windows.Shapes.Rectangle(); rect.Height = 1; rect.Fill = Brushes.Gray; GridScrollView.Children.Add(rect); Grid.SetRow(rect, GridScrollView.RowDefinitions.Count - 1); Grid.SetColumnSpan(rect, 2); GridScrollView.RowDefinitions.Add(new RowDefinition()); var tb3 = new TextBlock(); tb3.Text = "\n" + np.s_header + "\n"; tb3.Foreground = (SolidColorBrush)(new BrushConverter().ConvertFrom("#f5f5f5")); tb3.TextWrapping = TextWrapping.Wrap; tb3.FontWeight = FontWeights.Bold; tb3.FontSize = 28; GridScrollView.Children.Add(tb3); Grid.SetRow(tb3, GridScrollView.RowDefinitions.Count - 1); Grid.SetColumnSpan(tb3, 2); GridScrollView.RowDefinitions.Add(new RowDefinition()); var tb2 = new TextBlock(); tb2.Text = fullText.Length == 0 ? np.s_description : fullText; tb2.Foreground = (SolidColorBrush)(new BrushConverter().ConvertFrom("#f5f5f5")); tb2.TextWrapping = TextWrapping.Wrap; tb2.FontSize = 20; GridScrollView.Children.Add(tb2); Grid.SetRow(tb2, GridScrollView.RowDefinitions.Count - 1); Grid.SetColumnSpan(tb2, 2); SQLiteConnection conn = DatabaseConnectionHandler.Invoke(); sql = $"select i_local_address_id from " + $"{TableNames.Images} where i_newspaper_id={np.id}"; List <int> localAddressIds = conn.Query <int>(sql).ToList(); foreach (int localAddressId in localAddressIds) // загрузка всех картинок { sql = $"select s_path from {TableNames.LocalFileAddress} where id={localAddressId};"; string localPath = conn.QueryFirst <string>(sql); var img = new System.Windows.Controls.Image(); try { img.Source = new BitmapImage(new Uri(Directory.GetCurrentDirectory() + "\\" + localPath)); } catch (Exception ex) { Console.WriteLine($"error in image loading: {localPath} // {ex.Message}"); continue; } GridScrollView.RowDefinitions.Add(new RowDefinition()); GridScrollView.Children.Add(img); Grid.SetRow(img, GridScrollView.RowDefinitions.Count - 1); Grid.SetColumnSpan(img, 2); } }
/// <summary> /// Загружает полную версию новости для каждого элемента. Часть backgroundWorker /// </summary> /// <param name="sender"></param> /// <param name="e"></param> public void Worker_ParseFullNewsDoWork(object sender, DoWorkEventArgs e) { SQLiteConnection connection = DatabaseConnectionHandler.Invoke(); string sql = $"select np.id id, np.s_link s_link from " + $"{TableNames.NewspaperFullText} as ft " + $"INNER JOIN {TableNames.Newspapers} as np on ft.id = np.id " + $"where ft.i_is_parsed = 0"; List <Newspaper> news = connection.Query <Newspaper>(sql).ToList(); int threads = 16; List <List <Newspaper> > parallelWorks = new List <List <Newspaper> >(); int step = news.Count / (threads - 1); if (step < 1) { step = 1; } for (int i = 0; i < news.Count; i += step) // деление на части для параллельного парсинга { List <Newspaper> works = new List <Newspaper>(); for (int j = i; j < i + step && j < news.Count; j++) { works.Add(news[j]); } parallelWorks.Add(works); } bool isServerDown = false; int parsed = 0; Parallel.For(0, threads, (i, state) => { if (i < parallelWorks.Count) { foreach (Newspaper np in parallelWorks[i]) { if (_loadFullNewsWorker.CancellationPending) { break; } string fullText; try { fullText = ParseFullNewspaper.GetFullNewspaperText(np.s_link, np.id); } catch (Exception ex) { Console.WriteLine($"error in parsing newspaper id={np.id}, {ex.Message}, s_link={np.s_link}"); if (ex.Message == "Невозможно соединиться с удаленным сервером") { isServerDown = true; break; } continue; } DataBase.UpdateNewspaperFullText(np.id, fullText); parsed++; if (_loadFullNewsWorker.WorkerReportsProgress) { _loadFullNewsWorker.ReportProgress((int)(((double)parsed / (double)news.Count) * 100)); } //Console.WriteLine(parsed + " / " + news.Count + " np id: " + np.id); } } }); if (isServerDown) { MessageBox.Show("Невозможно соединиться с удаленным сервером, возможно сервер выключен или отсутствует подключение к сети интернет."); return; } e.Result = news; }