DuckDB database - small example

[zippo]

Here's what it does:
Database Management: The program opens a DuckDB database file named test.db.
Table Creation: It creates a table called users with fields id and name if it doesn't already exist.
Data Insertion: Inserts sample data into the users table: (1, 'John Doe'), (2, 'Peek Snow'), (3, 'Mark Python').
Query Execution: Executes a SQL query to select all records from the users table.
Result Display: Displays the query results in a user-friendly ListIconGadget window, complete with dynamic column headers based on the query output.

This code demonstrates basic interaction with a DuckDB database using PureBasic, including opening a database, executing a query, and displaying the results in a user interface element.

read: https://duckdb.org/docs/index
download: https://duckdb.org/docs/installation/?v ... ure=x86_64

Code: Select all

; PureBasic program to open a DuckDB database, create a table, insert data, and execute a query, displaying results in a ListIconGadget
; Written by: zippo # 2024-08-28 # 2024-08-27 #
; -- read:: https://duckdb.org/why_duckdb
; -- download:: https://duckdb.org/docs/installation/?version=stable&environment=cplusplus&platform=win&download_method=direct&architecture=x86_64
; -- copy duckdb.dll in dir
;--

Prototype.i DuckDBOpen(filename.p-utf8, *db)
Prototype.i DuckDBConnect(*db, *connection)
Prototype.i DuckDBQuery(*connection, query.p-utf8, *out_result)
Prototype.i DuckDBDestroyResult(*result)
Prototype.i DuckDBResultError(*result)
Prototype.q DuckDBRowCount(*result)
Prototype.q DuckDBColumnCount(*result)
Prototype.i DuckDBValueInt32(*result, column.i, row.q)
Prototype.i DuckDBValueVarchar(*result, column.i, row.q)
Prototype.i DuckDBColumnName(*result, column.i)

#DuckDBSuccess = 0
#DuckDBError = 1

Structure DuckDBResult
    columns.i
    rows.i
    Data.i
    error.i
EndStructure

If OpenLibrary(0, "duckdb.dll")
    
    Global DuckDBOpen.DuckDBOpen = GetFunction(0, "duckdb_open")
    Global DuckDBConnect.DuckDBConnect = GetFunction(0, "duckdb_connect")
    Global DuckDBQuery.DuckDBQuery = GetFunction(0, "duckdb_query")
    Global DuckDBDestroyResult.DuckDBDestroyResult = GetFunction(0, "duckdb_destroy_result")
    Global DuckDBResultError.DuckDBResultError = GetFunction(0, "duckdb_result_error")
    Global DuckDBRowCount.DuckDBRowCount = GetFunction(0, "duckdb_row_count")
    Global DuckDBColumnCount.DuckDBColumnCount = GetFunction(0, "duckdb_column_count")
    Global DuckDBValueInt32.DuckDBValueInt32 = GetFunction(0, "duckdb_value_int32")
    Global DuckDBValueVarchar.DuckDBValueVarchar = GetFunction(0, "duckdb_value_varchar")
    Global DuckDBColumnName.DuckDBColumnName = GetFunction(0, "duckdb_column_name")
    
    If DuckDBOpen = #Null Or DuckDBConnect = #Null Or DuckDBQuery = #Null Or DuckDBDestroyResult = #Null Or DuckDBResultError = #Null Or DuckDBRowCount = #Null Or DuckDBColumnCount = #Null Or DuckDBValueInt32 = #Null Or DuckDBValueVarchar = #Null Or DuckDBColumnName = #Null
        MessageRequester("Error", "Failed to load DuckDB functions", #PB_MessageRequester_Ok)
        End
    EndIf
    
    db.i
    connection.i
    result.DuckDBResult
    
    If DuckDBOpen("test.db", @db) = #DuckDBSuccess
        Debug "Database opened successfully."
        
        If DuckDBConnect(db, @connection) = #DuckDBSuccess
            Debug "Connected to the database."
            
            ; Create the users table
            createTableQuery.s = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name VARCHAR);"
            status.i = DuckDBQuery(connection, createTableQuery, @result)
            
            If status = #DuckDBSuccess
                Debug "Table created successfully."
                
                ; Insert data into the users table
                insertDataQuery.s = "INSERT INTO users (id, name) VALUES (1, 'John Doe'), (2, 'Peek Snow'), (3, 'Mark Python');"
                status = DuckDBQuery(connection, insertDataQuery, @result)
                
                If status = #DuckDBSuccess
                    Debug "Data inserted successfully."
                    
                    ; Execute the query to select data from the users table
                    selectQuery.s = "SELECT id, name FROM users;"
                    status = DuckDBQuery(connection, selectQuery, @result)
                    
                    If status = #DuckDBSuccess
                        Debug "Query executed successfully."
                        
                        rows.q = DuckDBRowCount(@result)
                        cols.q = DuckDBColumnCount(@result)
                        
                        Debug "Number of rows: " + Str(rows)
                        Debug "Number of columns: " + Str(cols)
                        
                        ; Create a window with a ListIconGadget
                        If OpenWindow(0, 0, 0, 640, 480, "DuckDB Query Results", #PB_Window_SystemMenu | #PB_Window_ScreenCentered | #PB_Window_SizeGadget)
                            ListIconGadget(0, 10, 10, 620, 460, "", 100, #PB_ListIcon_GridLines | #PB_ListIcon_FullRowSelect)
                            
                            ; Add dynamic columns based on the query results
                            For col.q = 0 To cols - 1
                                columnNamePtr.i = DuckDBColumnName(@result, col)
                                If columnNamePtr = 0
                                    columnName.s = "Column " + Str(col)
                                Else
                                    columnName.s = PeekS(columnNamePtr, -1, #PB_UTF8)
                                EndIf
                                AddGadgetColumn(0, col, columnName, 200)
                            Next col
                            
                            If rows > 0 And cols > 0
                                For row.q = 0 To rows - 1
                                    itemText.s = ""
                                    For col.q = 0 To cols - 1
                                        If col > 0
                                            itemText + Chr(10)
                                        EndIf
                                        valuePtr.i = DuckDBValueVarchar(@result, col, row)
                                        If valuePtr = 0
                                            itemText + "NULL"
                                        Else
                                            itemText + PeekS(valuePtr, -1, #PB_UTF8)
                                        EndIf
                                    Next col
                                    AddGadgetItem(0, row, itemText)
                                Next row
                            Else
                                Debug "No rows returned by the query."
                            EndIf
                            
                            ; Wait for the user to close the window
                            Repeat
                            Until WaitWindowEvent() = #PB_Event_CloseWindow
                            
                            ; Free the query result
                            DuckDBDestroyResult(@result)
                        Else
                            Debug "Failed to open window."
                            DuckDBDestroyResult(@result)
                        EndIf
                    Else
                        errorMessage$ = PeekS(DuckDBResultError(@result))
                        Debug "Query failed: " + errorMessage$
                        
                        ; Free the query result even if the query is unsuccessful
                        DuckDBDestroyResult(@result)
                    EndIf
                Else
                    errorMessage$ = PeekS(DuckDBResultError(@result))
                    Debug "Data insertion failed: " + errorMessage$
                    
                    ; Free the query result even if the data insertion is unsuccessful
                    DuckDBDestroyResult(@result)
                EndIf
            Else
                errorMessage$ = PeekS(DuckDBResultError(@result))
                Debug "Table creation failed: " + errorMessage$
                
                ; Free the query result even if the table creation is unsuccessful
                DuckDBDestroyResult(@result)
            EndIf
        Else
            Debug "Failed to connect to the database."
        EndIf
    Else
        Debug "Failed to open database."
    EndIf
    
    CloseLibrary(0)
Else
    MessageRequester("Error", "Failed to load DuckDB library", #PB_MessageRequester_Ok)
EndIf

End

[Caronte3D]

I wasn't know about DuckDB, looks good
Thanks for sharing!

[ebs]

@zippo,

Thanks for the information about DuckDB. Do you have any opinions on DuckDB vs. SQLite, i.e., speed, robustness, in what situation does one or the other have advantages, etc.?

[zippo]

@zippo,

Thanks for the information about DuckDB. Do you have any opinions on DuckDB vs. SQLite, i.e., speed, robustness, in what situation does one or the other have advantages, etc.?

I haven't tested DuckDB's speed yet, but it really works fast when it comes to importing and copying data from various sources. DuckDB is designed to handle large datasets with lightning speed. This means you can perform complex queries and create pivot tables in no time. I also use the export function to Excel, Markdown, and CSV, and everything works perfectly. I discovered this database about a month ago, and PureBasic two months ago. Most of my work is done in Python and C, and I've integrated them with DuckDB.

Give it a try, and I’m sure you’ll love it as much as I do!

[Fred]

It does look very useful, especially to parse json/CSV and filter them

[zippo]

It does look very useful, especially to parse json/CSV and filter them

Parsing a JSON input file and loading it into DuckDB can be done efficiently using SQL commands. DuckDB supports JSON parsing through its SQL functions. Below is an example of how you can parse a JSON file and insert its contents into a DuckDB table.

Let's assume you have a JSON file named data.json with the following content:
json

Code: Select all

[
    {"id": 1, "name": "John Doe", "age": 30},
    {"id": 2, "name": "Jane Smith", "age": 25},
    {"id": 3, "name": "Mark Johnson", "age": 40}
]

Here is a step-by-step guide to parse this JSON file and load it into a DuckDB table:

Create a DuckDB database and connect to it.

Create a table to store the JSON data.

Load the JSON file and insert its contents into the table.

sql
Copy code
-- Step 1: Create a DuckDB database and connect to it
-- This can be done using the DuckDB command-line interface or a script

-- Step 2: Create a table to store the JSON data

Code: Select all

CREATE TABLE users (
    id INTEGER,
    name VARCHAR,
    age INTEGER
);

-- Step 3: Load the JSON file and insert its contents into the table
-- Assuming the JSON file is named 'data.json' and is in the current directory

Code: Select all

INSERT INTO users
SELECT
    json_extract_path_text(value, 'id')::INTEGER AS id,
    json_extract_path_text(value, 'name')::VARCHAR AS name,
    json_extract_path_text(value, 'age')::INTEGER AS age
FROM read_json_auto('data.json');

-- Query the table to verify the data

Code: Select all

SELECT * FROM users;

Explanation:
Create Table: The CREATE TABLE statement defines the structure of the table where the JSON data will be stored.

Insert Data: The INSERT INTO statement reads the JSON file using the read_json_auto function, which automatically detects the JSON structure. The json_extract_path_text function extracts values from the JSON objects and casts them to the appropriate data types before inserting them into the table.

Query Data: The SELECT statement is used to verify that the data has been successfully inserted into the table.

Running the Script:
You can run this script using the DuckDB command-line interface or by embedding it in a script file and executing it with DuckDB.

For example, if you save the script to a file named load_json.sql, you can run it using the DuckDB CLI as follows:

sh
Copy code

Code: Select all

duckdb my_database.db < load_json.sql

This command connects to the DuckDB database my_database.db and executes the SQL commands in load_json.sql.

To emphasize, the database into which you export/import can be MySQL, PostgreSQL, SQLite, and my personal favorite :memory (transient in-memory database).

[Kiffi]

<OT>

By the way: SQLite can also process JSON.

Code: Select all

EnableExplicit

#Database = 0
#JSON = 0

Structure sSuperhero
  Name.s
  Profession.s
  Age.i
EndStructure

Global NewList Superhero.sSuperhero()

Procedure AddSuperhero(Name.s, Profession.s, Age)
  AddElement(Superhero())
  Superhero()\Name = Name
  Superhero()\Profession = Profession
  Superhero()\Age = Age
EndProcedure

AddSuperhero("Bruce Wayne",  "Batman",    40)
AddSuperhero("Clark Kent",   "Superman",  30)
AddSuperhero("Peter Parker", "Spiderman", 20)

UseSQLiteDatabase()

If OpenDatabase(#Database, ":memory:", "", "", #PB_Database_SQLite)
  
  ; CREATE TABLE...
  
  DatabaseUpdate(#Database, "CREATE TABLE Superheroes ( ID INTEGER PRIMARY KEY, JsonData TEXT )")
  
  ; INSERT INTO...
  
  ForEach Superhero()
    
    If CreateJSON(#JSON)
      
      InsertJSONStructure(JSONValue(#JSON), @Superhero(), sSuperhero)
      
      SetDatabaseString(#Database, 0, ComposeJSON(#JSON))
      
      DatabaseUpdate(#Database, "INSERT INTO Superheroes (JsonData) VALUES (?)")
      
      FreeJSON(#JSON)
      
    EndIf
    
  Next
  
  ; SELECT...
  
  If DatabaseQuery(#Database, "SELECT JsonData, JSON_EXTRACT(JsonData, '$.Name') AS Name, JSON_EXTRACT(JsonData, '$.Profession') AS Profession, JSON_EXTRACT(JsonData, '$.Age') AS Age FROM Superheroes")
    
    While NextDatabaseRow(#Database)
      Debug "JSON: " + GetDatabaseString(#Database, 0)
      Debug "Name: " + GetDatabaseString(#Database, 1)
      Debug "Profession: " + GetDatabaseString(#Database, 2)
      Debug "Age: " + GetDatabaseLong(#Database, 3)
      Debug "--------"
    Wend
    
    FinishDatabaseQuery(#Database)
    
  Else
    Debug DatabaseError()
  EndIf
    
  CloseDatabase(#Database)
  
Else
  Debug "Can't open database"
EndIf

SQLite JSON Functions And Operators

</OT>