private static void processaCsvZipado(SqliteDB db, string zipName, Func <string, bool> fileFilter, Func <string[], ReceitasModel> extractionFilter)
{
var zip = new LeitorZipTransparencia(zipName);
// Receitas, Brasil, agnóstico ao ano
zip.ShouldProcessFile = fileFilter;
zip.InicioLeituraArquivo += (s, e) => Console.WriteLine(e);
zip.IgnoreFirstLine = true;
var zipLines = zip.ReadLines();
var rows = CSVHelper.DelimiterSplit(zipLines, ';');
var buffer = new DataBuffer <ReceitasModel>(30000, data =>
{
db.BulkInsert(data, addReplace: true);
});
int qtd = 0;
foreach (var row in rows)
{
var cad = extractionFilter(row);
buffer.Add(cad);
qtd++;
if (qtd % buffer.Quantity == 0)
{
Console.WriteLine($"{DateTime.Now.ToLongTimeString()} Processado: {qtd:N0} {cad.NomeDoadorRFB}");
}
}
buffer.Flush();
}
private void MonitorForm_Load(object sender, EventArgs e)
{
// 初始化数据库连接并打开
pgsql = new PgsqlDB(ConfigurationManager.AppSettings.Get("dbHost"),
ConfigurationManager.AppSettings.Get("dbName"),
ConfigurationManager.AppSettings.Get("dbUser"),
ConfigurationManager.AppSettings.Get("dbPassword"));
pgsql.open();
sqlite = new SqliteDB(ConfigurationManager.AppSettings.Get("dbPath"));
sqlite.open();
sqlite.initCameraDatabase();
_log.Info("Pgsql DB & SQLite connected!");
// 初始化视频流显示
videoStream = new MJPEGStream();
videoStream.NewFrame += new NewFrameEventHandler(videoStream_NewFrame);
_log.Info("Video Stream inited!");
// 保存视频
if (!Directory.Exists("_video_saved"))
{
Directory.CreateDirectory("_video_saved");
}
videoWriter = new AVIWriter();
videoWriter.FrameRate = 2;
// 初始化摄像头列表
cameras = sqlite.getCameras();
cameras.ForEach(camera => lbCameras.Items.Add(camera));
}
public BulkDataForm(SqliteDB odb)
{
InitializeComponent();
engine = new ScryfallEngine(odb);
this.db = odb;
backgroundWorker = new BackgroundWorker();
}
public IEnumerable <T> ReadEntireTableDBAsEnumerable([CanBeNull] string orderByField = null)
{
var query = "select MessagePack from " + TableName;
if (orderByField != null)
{
query += " ORDER BY " + orderByField;
}
using (var con = new SQLiteConnection(SqliteDB.GetConnectionstring())) {
using (var cmd = new SQLiteCommand(con)) {
cmd.CommandText = query;
con.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var p = ReadSingleLine(reader);
yield return(p);
}
reader.Close();
con.Close();
}
}
}
public IGetResponse Reset()
{
var db = new SqliteDB();
db.ResetLoanInfo();
return(null);
}
public void SqliteDB_TestConnection()
{
var db = SqliteDB.CreateInMemory();
Assert.Equal(1, db.ExecuteScalar <int>("SELECT 1", null));
Assert.Equal(1, db.QueryFirst <int>("SELECT 1", null));
}
public IEnumerable <T> ReadSubsetOfTableDBAsEnumerable([NotNull] string whereField,
[NotNull] string condition,
[CanBeNull] string orderByField = null)
{
var query = "select MessagePack from " + TableName + " WHERE " + whereField + " = @par1";
if (orderByField != null)
{
query += " ORDER BY " + orderByField;
}
using (var con = new SQLiteConnection(SqliteDB.GetConnectionstring())) {
using (var cmd = new SQLiteCommand(con)) {
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@par1", condition);
con.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var p = ReadSingleLine(reader);
yield return(p);
}
reader.Close();
con.Close();
}
}
}
public void SqliteDB_InMemory_FileName()
{
var db = SqliteDB.CreateInMemory();
Assert.StartsWith("", db.DatabaseFileName);
Assert.True(db.IsInMemoryDatabase);
}
private void RefreshNamesInDatabase([NotNull] ILogger logger)
{
logger.Debug("Refreshing names in database", Stage.ProfileGeneration, "SavableEntry");
_namesInDatabase = new HashSet <string>();
using (var conn = new SQLiteConnection(SqliteDB.GetConnectionstring())) {
conn.Open();
var command1 = conn.CreateCommand();
command1.CommandText = "SELECT Name FROM " + TableName;
var reader = command1.ExecuteReader();
while (reader.Read())
{
var name = (string)reader["Name"];
// ReSharper disable once PossibleNullReferenceException
if (!_namesInDatabase.Contains(name))
{
_namesInDatabase.Add(name);
}
else
{
logger.ErrorM("Found more than one entry for the key " + name + ", probably left over bugs, deleting and reimporting.",
Stage.ProfileGeneration,
"SavableEntry");
DeleteEntryByName(name);
}
}
}
logger.Info("Finished refreshing names in database", Stage.ProfileGeneration, "SavableEntry");
}
public static void run()
{
SqliteDB db = new SqliteDB("myStuff.db");
Console.WriteLine($"Database is at {db.DatabaseFileName}");
// Create a DB Schema
db.CreateTables()
.Add <MyData>()
.Commit();
db.Insert(new
{
MyUID = Guid.NewGuid(),
MyName = "Test"
},
OnConflict.Ignore,
tableName: "MyData");
for (int i = 0; i < 5; i++)
{
db.Insert(new MyData()
{
MyId = i * 3,
MyUID = Guid.NewGuid()
});
}
var allInts = db.Query <int>("SELECT MyId FROM MyData", null).ToArray();
}
public void MakeTableForListOfFieldsIfNotExists(bool idxOnName)
{
if (Fields.Count == 0)
{
throw new Exception("No fields defined for database");
}
var sql = "CREATE TABLE IF NOT EXISTS " + TableName + "(";
foreach (var field in Fields)
{
sql += field.Name + " " + field.Type + ",";
}
sql = sql.Substring(0, sql.Length - 1) + ");";
var indexCmd = "CREATE INDEX if not EXISTS " + TableName + "_idx ON " + TableName + "(Name);";
using (var conn = new SQLiteConnection(SqliteDB.GetConnectionstring())) {
conn.Open();
var command = conn.CreateCommand();
command.CommandText = sql;
//var result =
command.ExecuteNonQuery();
if (idxOnName)
{
var command1 = conn.CreateCommand();
command1.CommandText = indexCmd;
//var result =
command1.ExecuteNonQuery();
}
}
}
/// <summary>
/// Initializes the singleton application object. This is the first line of authored code
/// executed, and as such is the logical equivalent of main() or WinMain().
/// </summary>
public App()
{
this.InitializeComponent();
this.Suspending += OnSuspending;
SqliteDB.InitializeDatabase();
}
public List <T> LoadAllOrMatching([CanBeNull] string fieldName = null, [CanBeNull] string key = null)
{
var query = "select MessagePack from " + TableName;
if (key != null && fieldName != null)
{
query += " WHERE " + fieldName + " = @key";
}
List <T> entries = new List <T>();
//Info("Reading from " + SqliteDB.DBFilename);
using (var con = new SQLiteConnection(SqliteDB.GetConnectionstring())) {
using (var cmd = new SQLiteCommand(con)) {
cmd.CommandText = query;
if (key != null && fieldName != null)
{
cmd.Parameters.AddWithValue("@key", key);
}
con.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var p = ReadSingleLine(reader);
entries.Add(p);
}
reader.Close();
con.Close();
}
}
return(entries);
}
/// <summary>
/// Configuração Banco de dados
/// </summary>
public static void Setup()
{
DB = new SqliteDB("MyShit.db");
DB.CreateTables()
.Add <Item>()
.Commit();
}
public void Setup()
{
BD = new SqliteDB("BDSistemaEstoque.db");
BD.CreateTables()
.Add <ProdutoCadastro>()
.Commit();
}
/// <summary>
/// Configuração do Banco de Dados
/// </summary>
public static void Setup()
{
DB = new SqliteDB("Database.db");
DB.CreateTables()
.Add<Ticket>()
.Add<TicketHist>()
.Commit();
}
public static void initializeDb()
{
string curFile = @"C:\Program Files\Macetim\iplist.db";
db = new SqliteDB(curFile);
var result = db.CreateTables()
.Add <iplist>()
.Commit();
}
public void Config_InMemory_BaseTests()
{
var memDb = SqliteDB.CreateInMemory();
var cfg = ConfigurationDB.FromDB(memDb);
Assert.Equal(":Empty:", cfg.GetConfig("test", "cat", ":Empty:"));
cfg.SetConfig("test", "cat", "myValue");
Assert.Equal("myValue", cfg.GetConfig("test", "cat", ":Empty:"));
}
public void Config_InMemory_Nulls()
{
var memDb = SqliteDB.CreateInMemory();
var cfg = ConfigurationDB.FromDB(memDb);
Assert.Equal(":Empty:", cfg.GetConfig("null", "cat", ":Empty:"));
cfg.SetConfig <string>("null", "cat", null);
Assert.Null(cfg.GetConfig("null", "cat", ":Empty:"));
}
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
WebApiConfig.Register(GlobalConfiguration.Configuration);
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
SqliteDB.init();
}
public void CreateIndexIfNotExists([NotNull] string tgtField)
{
var indexCmd = "CREATE INDEX if not EXISTS " + TableName + "_idx ON " + TableName + "(" + tgtField + ");";
using (var conn = new SQLiteConnection(SqliteDB.GetConnectionstring())) {
conn.Open();
var command = conn.CreateCommand();
command.CommandText = indexCmd;
command.ExecuteNonQuery();
}
}
public void DeleteEntryByName([NotNull] string myKey)
{
using (var conn = new SQLiteConnection(SqliteDB.GetConnectionstring())) {
conn.Open();
var command1 = conn.CreateCommand();
command1.CommandText = "DELETE FROM " + TableName + " WHERE Name = @par1";
Info("Deleting from " + TableName + " the name " + myKey);
command1.Parameters.AddWithValue("@par1", myKey);
command1.ExecuteScalar();
}
}
public void SqliteDB_GetAllTables_Empty()
{
var db = SqliteDB.CreateInMemory();
// Asser that there are no tables
int count = db.ExecuteScalar <int>("SELECT COUNT(1) FROM sqlite_master WHERE type='table'", null);
Assert.Equal(0, count);
// Check that matches with GetAll
Assert.Empty(db.GetAllTables());
}
private static void compararGabineteDoacoes()
{
Console.WriteLine("Abrindo bancos");
SqliteDB dbGabinete = new SqliteDB("gabinete_camara.db");
SqliteDB dbDoacoes = new SqliteDB("doacoes_2020.db");
Console.WriteLine("Selecionando doadores");
var modelDoacoes = dbDoacoes.ExecuteQuery <Robops.Lib.TSE.Contas.ReceitasModel>("SELECT * FROM ReceitasModel WHERE length(DocumentoDoador) = 11 ", null)
.Where(o => o.Ano == 2020)
.OrderBy(o => o.NomeDoadorRFB)
.ToArray();
Console.WriteLine("Selecionando pessoal");
var gabinete = dbGabinete.GetAll <PessoalModel>()
.ToArray();
var hashNomesGabinete = gabinete.Select(o => o.NomeFuncionario.ToUpper())
.ToHashSet();
var deputados = dbGabinete.GetAll <Robops.Lib.Camara.Leg.Deputado>()
.ToDictionary(d => d.Id);
Console.WriteLine("Executando busca");
foreach (var doador in modelDoacoes)
{
if (!hashNomesGabinete.Contains(doador.NomeDoadorRFB.ToUpper()))
{
continue;
}
var gab = gabinete.First(g => g.NomeFuncionario.ToUpper() == doador.NomeDoadorRFB.ToUpper());
// ele pode doar para si mesmo
if (gab.NomeFuncionario.ToUpper() == doador.NomeCandidato.ToUpper())
{
continue;
}
string fileName = "doadores.csv";
var deputado = deputados[gab.IdDeputado];
if (deputado.NomeCivil.ToUpper() == doador.NomeCandidato.ToUpper())
{
fileName = "doadores_pingPong.csv";
}
else
{
fileName = "doadores_gramaVizinho.csv";
}
string text = $"{doador.NomeDoadorRFB.ToUpper()};{gab.NomeDeputado.ToUpper()};{deputado.PartidoLideranca};{doador.NomeCandidato.ToUpper()};{doador.NumeroCandidato};{gab.InicioExercicio:d} - {gab.FimExercicio:d}\r\n";
Console.WriteLine(text);
File.AppendAllText(fileName, text);
}
}
private void Awake()
{
Screen.sleepTimeout = SleepTimeout.NeverSleep;
if (_db != null && _db != this)
{
Destroy(this.gameObject);
}
else
{
_db = new SqliteDB();
}
}
private VolumeDatabase ownerDb; // reference to the owner volumedatabe object
public SqlBackend(string dbPath, bool create, VolumeDatabase ownerDb)
{
_conn_lock = new Object();
conn = SqliteDB.Open(dbPath, create);
disposed = false;
transaction = null;
currentTransactionThread = null;
this.ownerDb = ownerDb;
}
public App()
{
InitializeComponent();
database = new SqliteDB();
if (!IsUserLoggedIn)
{
MainPage = new NavigationPage(new Login());
}
else
{
MainPage = new NavigationPage(new MainPage());
}
}
public static void run(SqliteDB db)
{
db.CreateTables()
.Add <ReceitasModel>()
.Commit();
string path;
//foreach (var file in Directory.GetFiles(@"N:\Organizando\Dados Crawlers\TSE\", "*.zip"))
//{
// processaArquivo(db, file);
//}
processaArquivo2018_2020(db, @"N:\Organizando\Dados Crawlers\TSE\prestacao_de_contas_eleitorais_candidatos_2020.zip");
}
public void MakeCleanTableForListOfFields(bool makeIndexOnName)
{
if (Fields.Count == 0)
{
throw new Exception("No fields defined for database");
}
var sqlDel = "DROP TABLE IF EXISTS " + TableName;
var sql = "CREATE TABLE " + TableName + "(";
foreach (var field in Fields)
{
sql += field.Name + " " + field.Type + ",";
}
var indexCmd = "CREATE INDEX if not EXISTS " + TableName + "_idx ON " + TableName + "(Name);";
sql = sql.Substring(0, sql.Length - 1) + ");";
using (var conn = new SQLiteConnection(SqliteDB.GetConnectionstring())) {
conn.Open();
var command1 = conn.CreateCommand();
command1.CommandText = sqlDel;
var result1 = command1.ExecuteNonQuery();
if (result1 != 0 && result1 != -1)
{
throw new Exception("Dropping the table " + TableName + " failed: Result: " + result1);
}
var command = conn.CreateCommand();
command.CommandText = sql;
var result = command.ExecuteNonQuery();
if (result != 0)
{
throw new Exception("Creating the table " + TableName + " failed.");
}
if (makeIndexOnName)
{
var commandIdx = conn.CreateCommand();
commandIdx.CommandText = indexCmd;
var resultIdx = commandIdx.ExecuteNonQuery();
if (resultIdx != 0)
{
throw new Exception("Creating the index failed.");
}
}
}
}
/// <summary>
/// Select a value from a table using specified column and value
/// </summary>
public static T Get <T>(this ISqliteConnection connection, string tableName, string keyColumn, object keyValue)
{
if (tableName is null)
{
throw new ArgumentNullException(nameof(tableName));
}
if (keyColumn is null)
{
throw new ArgumentNullException(nameof(keyColumn));
}
var data = connection.Query <T>($"SELECT * FROM {tableName} WHERE {keyColumn} = @keyValue LIMIT 1 ", new { keyValue });
return(SqliteDB.getFirstOrDefault(data));
}