programming challenge – SQLite memory database that inserts data read from a CSV file

I wrote this program to make a database in simple memory using SQLite and JDBC. The requirements I was trying to meet are below.

  1. You should consume a CSV file, analyze the data and insert it into the in-memory database.
  2. The name of the table is X and it has 10 columns (called A – J).
  3. If any of the columns in a row of the CSV file is blank, it must be written to a "bad-data-.csv" file and not inserted into the database.
  4. At the end of the process, write the statistics in a log file: a. Number of records received. second. Number of successful records (records that can be inserted in the database). do. Number of failed records (records written in the file "bad-data-.csv").

The file "sample.csv" can be found here: https://filebin.net/aib6j3s9lsxez50u

This is the first time I try to do something with databases or OpenCSV, so let me know if I can structure it better or anything else. I would really appreciate some constructive criticism. Thank you!

                package com.ms3.dbx;

import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import com.opencsv.CSVWriter;

import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.io.Writer;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class SQLiteTest {

private static end string url = "jdbc: sqlite :: memory:";
Private connection connection = null;

static final private string csvPath = "/Users/home/eclipse-workspace/dbx/src/main/java/com/ms3/dbx/sample.csv";
private static final String badDataPath = "/ Users / home / eclipse-workspace / dbx / src / main / java / com / ms3 / dbx / bad-data-";
Private static end string badDataExt = ".csv";

final static private DateFormat df = new SimpleDateFormat ("yyyyMMddhhmmss");
Private static end string badDataFilename = badDataPath + df.format (new Date ()) + badDataExt;

static end static String logFilePath = "/Users/home/eclipse-workspace/dbx/src/main/java/com/ms3/dbx/logFile.log";

private static int recordsReceived = 0;
int static private records successful = 0;
private static int recordsFailed = 0;

static
{
try
{
Class.forName ("org.sqlite.JDBC");
} capture (ClassNotFoundException e)
{
launch the new ExceptionInInitializerError (e);
}
}

// Open the connection to the in-memory database
private void openConnection () throws SQLException
{
if (connection == null || connection.isClosed ())
{
connection = DriverManager.getConnection (url);
}
}

// Close the connection to the database
private void closeConnection () throws SQLException
{
connection.close ();
}

// Create a table X in the database.
void createTable ()
{
try
{
Final statement Statement = connection.createStatement ();

statement.executeUpdate ("CREATE TABLE IF X DOES NOT EXIST"
+ "(ONE TEXT,"
+ "TEXT B"
+ "TEXT C"
+ "D TEXT"
+ "E TEXT"
+ "F TEXT"
+ "G TEXT"
+ "H TEXT"
+ "I TEXT,"
+ "J TEXT);");

} capture (SQLException e)
{
e.getMessage ();
}
}

// Read data from a sample.csv file using OpenCSV
// If there is a blank column in a row, write it in "bad-data-.csv file
// If not, insert the row in the database
// Increase recordsReceived for each row in the file sample.csv
// Increase records. Success for each row that each column has full of data
// Increase recordsFailed for each row that has at least one blank column
void insertFromCSV ()
{
try
{
Reader reader = Files.newBufferedReader (Paths.get (csvPath));
CSVReader csvReader = new CSVReaderBuilder (reader) .withSkipLines (1) .build ();

Writer writer = Files.newBufferedWriter (Paths.get (badDataFilename));

CSVWriter csvWriter = new CSVWriter (writer,
CSVWriter.DEFAULT_SEPARATOR,
CSVWriter.NO_QUOTE_CHARACTER,
CSVWriter.DEFAULT_ESCAPE_CHARACTER,
CSVWriter.DEFAULT_LINE_END);

Rope[] headerRecord = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J"};
csvWriter.writeNext (headerRecord);

PreparedStatement pstatement = connection.prepareStatement ("INSERT INTO X (A, B, C, D, E, F, G, H, I, J)"
+ "VALUES (?,?,?,?,?,?,?,?,?,?,?);");

Rope[] nextRecord;
while ((nextRecord = csvReader.readNext ())! = null)
{
recordsReceived ++;

if (! Arrays.asList (nextRecord) .contains (""))
{
successful records ++;
pstatement.setString (1, nextRecord[0]);
pstatement.setString (2, nextRecord[1]);
pstatement.setString (3, nextRecord[2]);
pstatement.setString (4, nextRecord[3]);
pstatement.setString (5, nextRecord[4]);
pstatement.setString (6, nextRecord[5]);
pstatement.setString (7, nextRecord[6]);
pstatement.setString (8, nextRecord[7]);
pstatement.setString (9, nextRecord[8]);
pstatement.setString (10, nextRecord[9]);
pstatement.executeUpdate ();
} another thing
{
recordsFailed ++;
csvWriter.writeNext (nextRecord);
}
}

csvWriter.close ();

} catch (SQLException | IOException e)
{
e.getMessage ();
}
}

// Check the database and print everything to make sure that the data is actually being inserted
void testDB ()
{
try
{
Final statement Statement = connection.createStatement ();
ResultSet rs = statement.executeQuery ("SELECT * FROM X;");

ResultSetMetaData rsmd = rs.getMetaData ();
int numColumns = rsmd.getColumnCount ();

while (rs.next ())
{
for (int i = 1; i <= numColumns; i ++)
{
System.out.print (rs.getString (i) + "");
}
System.out.println ();
}
} capture (SQLException e)
{
e.getMessage ();
}
}

// Register received, successful and failed records in a log file
void logStats ()
{
try
{
FileWriter fw = new FileWriter (logFilePath);
fw.write ("Registries received:" + records received + " n");
fw.write ("Successful records:" + Effective records + " n");
fw.write ("Failed records:" + failed records);
fw.close ();
} capture (IOException e)
{
e.getMessage ();
}
}

empty main public static (String[] args) throws SQLException
{
SQLiteTest obj = new SQLiteTest ();
obj.openConnection ();
obj.createTable ();
obj.insertFromCSV ();
obj.testDB ();
obj.logStats ();
obj.closeConnection ();
}
}

sqlite – Database schema for an interactive novel

I am trying to create an interactive telegram bot in Node-red. To store the story I'm using SQLite

How it works: Bot shows you a part of the story, then shows some answers (buttons) about what you are going to do next (for example: Go ahead!

Depending on your answer, bot gives you the next part of the story.

I am not familiar with the relationships of the DB table.

Currently my database structure is as follows:

Table:

Users (userid PRIMARY KEY INTEGER, user name TEXT, currentstorypositionposition INTEGER)

Table:

Phrases of the story (key phrase KEY KEY INTEGRATED IN THE AUTOINCREMENT, phrase TEXT, answers TEXT)

The Answers field contains identifiers of possible answers in plain text. eg "2,4,6"

And table:

Answers (answerid MAIN CODE INTEGRATOR OF AUTOINCREMENTS, answer TEXT, next story INTEGER)

I suppose I could "connect" sentences of stories and answers to each other using some database relationships. However, I do not know how.

I understand that storing response IDs such as "2,4,6" in the text of the complaint is rare.

Can anyone advise on the best database structure for interactive novels like that?

Thank you.

xamarin.forms – Error: Count and Sum return zero values. / Xamarin – SQLite

I would like to ask about a problem I am having. It turns out that I'm doing an App in Xamarin with SQLite, but I have a problem that does not let me get ahead and I really have no idea why it happens.
I have two tables: "Customers" and "Orders", I need to bring two lists with the number of orders per customer and the sum of the amounts of those orders. For that work I use COUNT and SUM respectively and GROUP BY, but by bringing the list it brings me as results, pure zeros, the clients are correctly, but the quantity of orders and the sums are only zero (0).
It generates real curiosity because I have a twin database in SQL Server and that works great, but in the App it returns those values ​​to me.

In my tables:

[Table("Pedidos")]



public class Orders
{
    [PrimaryKey, AutoIncrement]
    public int Order_ID {get; set; }

    [ForeignKey(typeof(Clientes))]
    public int Customer_ID {get; set; }

    public DateTime Order_date {get; set; }
    public DateTime Date_Delivery {get; set; }
    public string Delivered {get; set; }
    public int Amount {get; set; }

    [ManyToMany(typeof(Pedidos_Productos))]
    public List Products {get; set; }
}

[Table("Clientes")]
public class Clients
{
    [PrimaryKey, AutoIncrement]
    public int Customer_ID {get; set; }

    [Unique]
    public string Name {get; set; }
    public string Telephone {get; set; }
    public string Address {get; set; }
    public string Locality {get; set; }
    public string Email {get; set; }
    public int Balance {get; set; }

    [ManyToMany(typeof(Clientes_Productos))]
    public List Products {get; set; }
}

I use this class to bring the lists:

public class Number of Orders
{
    public string Name {get; set; }
    public int requested number {get; set; }
}

Here the queries to the database:

                public List Bring OrderNumber ()
    {
        return Connection.Query("Select Clients.Name, count (Orders.Of_Order) as Orders from Orders, Clients where Clients.Client_id = Orders.Client_id group by Clients.Name"). ToList ();
    }

    public List BringSumPayments ()
    {
        return Connection.Query("select c.Name, sum (p.Monto) from Customers c, Orders p where c.Id_Client = p.Id_Client group by c.Name"). ToList ();
    }

Here I leave an image of the App at run time to see how it correctly returns the name of the client but the value is zero:

enter the description of the image here

I hope you can help me. Thank you so much

sqlite – The object array in javascript does not return as expected

I have a function that returns a list of objects in Javascript, and I'm calling this function from another and trying to use some of its values, but every time I try to access these values, they become undefined.

This is my function that generates the list; The idea is to create a sqlite3 database if it does not exist, and return an array that contains each event.

listAllEvents () {function
const sqlite3 = require (& # 39; sqlite3 & # 39;). verbose ();
const db = new sqlite3.Database (& # 39; schedule.db & # 39;);

const selectionArray = [];

db.serialize (() => {
db.run (`
CREATE TABLE IF THERE IS NOT ALL (
name of the text,
date text,
primary key of the identification text
)
`);

db.all (& # 39; SELECT * FROM all ORDER BY date & # 39 ;, [], (error, rows) => {
yes (err) {
throw away
}

rows.forEach ((row) => {
selectionArray.push (row);
});
});
});
return selectionArray;
}

I call this function from another, but when I try to access the values ​​in the matrix, they do not seem to be working and I can not understand it.

function displayUpcomingEvents () {
const events = listAllEvents ();

// console.log (events); <- This line here! In the console, it correctly indicates the length of the matrix
// console.log (events.length) <- This line, however, returns 0. Why?
// console.log (events[0]) <- This also does not work, it simply returns "undefined".

for (be i = 0; i <events.length; i + = 1) {
$ (& # 39; # upcomingEvents & # 39;). after (& # 39;
  • asdf
  • & # 39;); } }

    For example, if you had to create two events in the database, through the console,

    Events is an Array (2) with indexes.
    - 0: {name: "my_event", date: "2019-06-04", id: "c017c392d446d4b2"}
    - 1: {name: "my_event_2", date: "2019-06-04", id: "6d655ac8dd02e3fd"},
    
    events.length returns 0,
    and events[0] returns indefinite.
    

    Why is this, and what can I do to solve it?

    Going through the column names in SQLite and executing a command for each column name

    I am trying to execute the following command

    UPDATE the attachments SET colname = TRIM (colname);
    

    in each column name in a SQLite table.

    I found the following approach to get a list of column names in
    an answer to "How to get a list of column names in sqlite3 / iPhone?", namely

    SELECT the name FROM PRAGMA_TABLE_INFO (& # 39; your_table_ & # 39;);
    

    What seems like a nice, clean and simple way to do it. But this method is specific to SQLite, according to the PRAGMA statements.

    In any case, I would like a solution that runs completely in SQLite. Actually I'm calling SQLite from Lua, using LuaSQL. So I could run the loop in Lua if necessary. But doing it inside SQLite just seems cleaner.

    It would be even better if it is a general method that works for any RDBMS. But that might not be possible, and if it were, it would probably be more complicated.

    sqlite – How to read the google content_cache folder of Filestream?

    I have problems with Google Filestream.

    The user's data was not synchronized with the cloud for months. After uninstalling and reinstalling FS client synchronization, it now works fine … but all locally saved files are lost. We have a content_cache folder with more than 40 Gb of data.

    There are no identifications marked with "local-[number]"in metadata_sqlite_db. Maybe we do not have the latest version of the database or the client has not worked as planned.

    Is there any way to reconstruct, analyze or read the content_cache folder?

    There is a big discussion on the FS here: https://stackoverflow.com/questions/51439810/get-google-drive-files-links-using-drive-file-stream/52107704

    Thanks in advance.

    creating a foreign key using the SQLite and Netbeans database browser

    I have created my tables using SQLite database browser,
    but I do not know how to create external keys and about foreign key clauses and connect them through netbeans.

    How to recompress the sqlite file as a .jet file

    I have a .sqlite file and the developer compresses it as a .jet file. Now I can open it with 7zip, I can edit the database but in the end I could not recompress it as a .jet file. I tried all the solutions I saw on the internet, but none of them answered me.

    If someone wants to take a look, download the download link.

    https://wetransfer.com/downloads/ea5a329dff6b4ca48363a6faca2b17af20190516110040/2b647b

    Any help would be great.

    How to recompress the sqlite file as a .jet file

    I have a .sqlite file and the developer compresses it as a .jet file. Now I can open it with 7zip, I can edit the database but in the end I could not recompress it as a .jet file. I tried all the solutions I saw on the internet, but none of them answered me.

    If someone wants to take a look, download the download link.

    https://wetransfer.com/downloads/ea5a329dff6b4ca48363a6faca2b17af20190516110040/2b647b

    Any help would be great.

    How to return data from the database in python using sqlite and Tkinter?

    def search (self):

                    self.name = self.nome.get ()
    self.delete.delete (0, END)
    self.name.insert (INSERT, self.nome)
    self.tele.delete (0, END)
    self.tele.insert (INSERT, self.tele)
    self.telef.delete (0, END)
    self.telef.insert (INSERT, self.telef)
    
    connection = sqlite3.connect ("AgendaBD.db")
    c = cursor connection ()
    c.execute ("SELECT * FROM PEOPLE WHERE nombrep = & # 39;% s & # 39;"% (self.name))
    conexao.commit ()
    
    for the line in c:
    self.name = line[1]
            self.tele = line[2]
            self.telef = line[3]