sqlite – I want to search record of a single student but I cannot do this with my query

sqlite – I want to search record of a single student but I cannot do this with my query – Android Enthusiasts Stack Exchange

random – Randomly generate data under conditions for sqlite

As part of creating a machine learning exercise, I have an sqlite database that looks like this:
diagram
The “Pizza”, “DateDimension” and “Customer” tables are already populated, there are 5 different pizzas and 10 customers.

How could I generate random data for the sales table, but give conditions to create a pattern?

For example, on Monday/Tuesday a random sales figure between 15 and 25, on the other days of the week a random sales figure between 10 and 20, between 20 and 30 on the weekend.

Knowing that for a day, a sale of 15 pizzas would be for the customer of ID=1 a sale of 2 pizzas of ID=2 and 4 pizzas of ID=3,
for the customer of ID=2 a sale of 3 pizzas of ID=2, and
for the ID=3 customer a sale of 3 pizzas of ID=4 and 3 pizzas of ID=2.

This makes a total of 15 pizzas, of which 8 ID=2 pizzas, 4 ID=3 pizzas, 3 ID=4 pizzas, divided between three customers.

I don’t know if I’m clear enough in my explanations, basically we need a random number of pizza sales for each day, and that this number is itself randomly split between the types of pizza and the customers, under certain conditions in order to follow a pattern in the number of sales per day.

Adding iterators to C++ sqlite wrapper class

I recently put up my C++ class to wrap the sqlite3 c library here:

Thin C++ wrapper for sqlite3 C library

One of the suggestions was rather than to specify a vector for arguments to functions instead specify iterators so users can use whatever collection they see fit.

Not being that familiar with adding such iterator functionality, please review this aspect of the class.

And indeed any other comments you would like to make.

Here is the header, sqlite.hpp:

/*
sqlite - a thin c++ wrapper of sqlite c library
*/

#ifndef SQLITE_HPP_
#define SQLITE_HPP_

// uncomment below to print blob data as hex in ostream overload of column_values
// #define PRINT_BLOB_AS_HEX

#include "sqlite3.h"

#include <string>
#include <vector>
#include <variant>
#include <cstdint>
#include <iostream>
#include <map>

#define EXIT_ON_ERROR(resultcode) 
if (resultcode != SQLITE_OK) 
{ 
    sqlite3_finalize(stmt); 
    return resultcode; 
}

namespace sql {

    /*
    sqlite types can be: NULL, INTEGER, REAL, TEXT, BLOB
    NULL: we don't support this type
    INTEGER: int
    REAL: double
    TEXT: std::string
    BLOB: std::vector<uint8_t>
    */
    using sqlite_data_type = std::variant<int, double, std::string, std::vector<uint8_t> >;

    struct column_values {
        std::string column_name;
        sqlite_data_type column_value;
    };

    struct where_binding {
        std::string column_name;
        sqlite_data_type column_value;
    };

    std::ostream& operator<< (std::ostream& os, const column_values& v);
    std::ostream& operator<< (std::ostream& os, const sqlite_data_type& v);
    std::ostream& operator<< (std::ostream& os, const std::map<std::string, sqlite_data_type>& v);


    class sqlite {
    public:
        sqlite();
        ~sqlite();

        /* database must be opened before calling an sql operation */
        int open(const std::string& filename);

        /* close database connection */
        int close();

        /* INSERT INTO (col1, col2) VALUES (:col1, :col2); table_name is table to insert into
        begin and end are iterators to a collection of column name -> value key value pairs */
        template <typename columns_iterator>
        int insert_into(const std::string& table_name, columns_iterator begin, columns_iterator end);

        /* returns rowid of last successfully inserted row. If no rows
        inserted since this database connectioned opened, returns zero. */
        int last_insert_rowid();

        /* UPDATE contacts SET col1 = value1, col2 = value2, ... WHERE rowid = therowid;
        table_name is table to update,
        columns_begin and columns_end are iterators to a collection of column name -> value key value pairs
        where_clause is WHERE clause predicate expressed as : parameterised query
        where_bindings_begin and end are iterators to a collection of where bindings used in the where clause */
        template <typename columns_iterator, typename where_bindings_iterator>
        int update(
            const std::string& table_name,
            columns_iterator columns_begin, 
            columns_iterator columns_end,
            const std::string& where_clause,
            where_bindings_iterator where_bindings_begin,
            where_bindings_iterator where_bindings_end);

        /* UPDATE contacts SET col1 = value1, col2 = value2, ...;
        same as update(table_name, begin, end, where) except no WHERE clause so potential to change EVERY row. USE WITH CAUTION. */
        template <typename columns_iterator>
        int update(const std::string& table_name, 
            columns_iterator begin,
            columns_iterator end);

        /* DELETE FROM table_name WHERE condition; */
        template <typename where_bindings_iterator>
        int delete_from(const std::string& table_name, 
            const std::string& where_clause, 
            where_bindings_iterator where_bindings_begin,
            where_bindings_iterator where_bindings_end);

        /* DELETE FROM table_name;
        same as delete_from(table_name, where) except no WHERE clause so potential to delete EVERY row. USE WITH CAUTION. */
        int delete_from(const std::string& table_name);

        /* SELECT * FROM table_name WHERE col1 = x;
        table_name is table to select,
        where_clause is WHERE clause predicate expressed as : parameterised query
        where_bindings_begin and end are iterators to a collection of where bindings used in the where clause
        results is a table of values */
        template <typename where_bindings_iterator>
        int select_star(const std::string& table_name,
            const std::string& where_clause,
            where_bindings_iterator where_bindings_begin,
            where_bindings_iterator where_bindings_end,
            std::vector<std::map<std::string, sqlite_data_type>>& results);
        /* SELECT * FROM table_name;
        table_name is table to select,
        results is a table of values */
        int select_star(const std::string& table_name,
            std::vector<std::map<std::string, sqlite_data_type>>& results);

        /* SELECT col1, col2 FROM table_name WHERE col1 = x;
        table_name is table to select,
        name_begin and end are iterators to a collection of column name strings in table to select
        where_clause is the sql WHERE clause
        where_bindings_begin and end are iterators to a collection of where bindings used in the where clause
        results is a table of values */
        template <typename column_names_iterator, typename where_bindings_iterator>
        int select_columns(const std::string& table_name,
            column_names_iterator name_begin,
            column_names_iterator name_end,
            const std::string& where_clause,
            where_bindings_iterator where_bindings_begin,
            where_bindings_iterator where_bindings_end,
            std::vector<std::map<std::string, sqlite_data_type>>& results);

        /* get error text relating to last sqlite error. Call this function
        whenever an operation returns a sqlite error code */
        const std::string get_last_error_description();

    private:
        sqlite3* db_;

        template <typename columns_iterator>
        int bind_fields(sqlite3_stmt* stmt, columns_iterator begin, columns_iterator end);

        template <typename columns_iterator>
        std::string  insert_into_helper(const std::string& table_name, columns_iterator begin, columns_iterator end);

        template <typename columns_iterator>
        std::string update_helper(
            const std::string& table_name,
            columns_iterator begin,
            columns_iterator end,
            const std::string& where_clause);

        template <typename where_binding_iterator>
        int bind_where(sqlite3_stmt* stmt, where_binding_iterator begin, where_binding_iterator end);

        int step_and_finalise(sqlite3_stmt* stmt);

        std::string space_if_required(const std::string& s);

        std::string delete_from_helper(
            const std::string& table_name,
            const std::string& where_clause);

        template <typename column_names_iterator>
        const std::string select_helper(
            const std::string& table_name,
            column_names_iterator name_begin,
            column_names_iterator name_end,
            const std::string& where_clause);
    };

    template <typename columns_iterator>
    int sqlite::bind_fields(sqlite3_stmt* stmt, columns_iterator begin, columns_iterator end) {

        int rc = SQLITE_OK;

        for (auto it = begin; it != end; ++it) {
            std::string next_param{ ':' + it->column_name };
            int idx = sqlite3_bind_parameter_index(stmt, next_param.c_str());

            switch (it->column_value.index()) {
            case 0: rc = sqlite3_bind_int(stmt, idx, std::get<0>(it->column_value)); break;
            case 1: rc = sqlite3_bind_double(stmt, idx, std::get<1>(it->column_value)); break;
            case 2: rc = sqlite3_bind_text(stmt, idx, std::get<2>(it->column_value).c_str(), -1, SQLITE_STATIC); break;
            case 3:
                rc = sqlite3_bind_blob(stmt, idx, std::get<3>(it->column_value).data(), 
                    static_cast<int>(std::get<3>(it->column_value).size()), SQLITE_STATIC);
                break;
            }
        }
        return rc;
    }

    template <typename columns_iterator>
    int sqlite::insert_into(const std::string& table_name, columns_iterator begin, columns_iterator end) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = insert_into_helper(table_name, begin, end);

        sqlite3_stmt* stmt = NULL;
        EXIT_ON_ERROR(sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, NULL));

        EXIT_ON_ERROR(bind_fields(stmt, begin, end));

        return step_and_finalise(stmt);
    }

        template <typename columns_iterator, typename where_bindings_iterator>
        int sqlite::update(
            const std::string & table_name,
            columns_iterator columns_begin,
            columns_iterator columns_end,
            const std::string & where_clause,
            where_bindings_iterator where_bindings_begin,
            where_bindings_iterator where_bindings_end) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = update_helper(table_name, columns_begin, columns_end, where_clause);

        sqlite3_stmt* stmt = NULL;
        EXIT_ON_ERROR(sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, NULL));

        EXIT_ON_ERROR(bind_fields(stmt, columns_begin, columns_end));

        EXIT_ON_ERROR(bind_where(stmt, where_bindings_begin, where_bindings_end));

        return step_and_finalise(stmt);
    }

    template <typename columns_iterator>
    int sqlite::update(const std::string& table_name,
        columns_iterator begin,
        columns_iterator end) {
        return update(table_name, begin, end, "", {});
    }

    template <typename columns_iterator>
    std::string  sqlite::insert_into_helper(const std::string& table_name, 
        columns_iterator begin, 
        columns_iterator end) {

        std::string sqlfront{ "INSERT INTO " + table_name + " (" };
        std::string sqlend{ ") VALUES (" };

        std::string separator{ "" };
        for (auto field = begin; field != end; ++field) {
            sqlfront += separator + field->column_name;
            sqlend += separator + ':' + field->column_name;
            separator = ",";
        }

        sqlend += ");";
        return sqlfront + sqlend;
    }

    template <typename columns_iterator>
    std::string sqlite::update_helper(
        const std::string& table_name,
        columns_iterator begin, 
        columns_iterator end,
        const std::string& where_clause) {

        std::string sql{ "UPDATE " + table_name + " SET " };

        std::string separator{ "" };
        for (auto field = begin; field != end; ++field) {
            sql += separator + field->column_name + "=:" + field->column_name;
            separator = ",";
        }

        if (!where_clause.empty()) {
            sql += space_if_required(where_clause);
            sql += where_clause;
        }

        sql += ";";

        return sql;
    }

    template <typename where_binding_iterator>
    int sqlite::bind_where(sqlite3_stmt* stmt, where_binding_iterator begin, where_binding_iterator end) { //const std::vector<sql::where_binding>& binding) {

        int rc = SQLITE_OK;

        for (auto param = begin; param != end; ++param) {

            std::string next_param{ ':' + param->column_name };

            int idx = sqlite3_bind_parameter_index(stmt, next_param.c_str());

            switch (param->column_value.index()) {
            case 0: rc = sqlite3_bind_int(stmt, idx, std::get<0>(param->column_value)); break;
            case 1: rc = sqlite3_bind_double(stmt, idx, std::get<1>(param->column_value)); break;
            case 2:  rc = sqlite3_bind_text(stmt, idx, std::get<2>(param->column_value).c_str(), -1, SQLITE_STATIC); break;
            case 3:
                rc = sqlite3_bind_blob(stmt, idx, std::get<3>(param->column_value).data(),
                    static_cast<int>(std::get<3>(param->column_value).size()), SQLITE_STATIC);
                break;
            }
        }
        return rc;
    }

    template <typename column_names_iterator, typename where_bindings_iterator>
    int sqlite::select_columns(const std::string& table_name,
        column_names_iterator name_begin,
        column_names_iterator name_end,
        const std::string& where_clause,
        where_bindings_iterator where_bindings_begin,
        where_bindings_iterator where_bindings_end,
        std::vector<std::map<std::string, sqlite_data_type>>& results) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = select_helper(table_name, name_begin, name_end, where_clause);

        sqlite3_stmt* stmt = NULL;
        EXIT_ON_ERROR(sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, NULL));

        EXIT_ON_ERROR(bind_where(stmt, where_bindings_begin, where_bindings_end));

        int num_cols = sqlite3_column_count(stmt);

        std::vector<std::string> column_names;
        for (int i = 0; i < num_cols; i++) {
            const char* colname = sqlite3_column_name(stmt, i);
            column_names.push_back(colname ? colname : "");
        }

        int rc = 0;
        while ((rc = sqlite3_step(stmt)) != SQLITE_DONE) {
            std::map<std::string, sqlite_data_type> row;
            for (int i = 0; i < num_cols; i++)
            {
                switch (sqlite3_column_type(stmt, i))
                {
                case SQLITE3_TEXT:
                {
                    const unsigned char* value = sqlite3_column_text(stmt, i);
                    int len = sqlite3_column_bytes(stmt, i);
                    row(column_names(i)) = std::string(value, value + len);
                }
                break;
                case SQLITE_INTEGER:
                {
                    row(column_names(i)) = sqlite3_column_int(stmt, i);
                }
                break;
                case SQLITE_FLOAT:
                {
                    row(column_names(i)) = sqlite3_column_double(stmt, i);
                }
                break;
                case SQLITE_BLOB:
                {
                    const uint8_t* value = reinterpret_cast<const uint8_t*>(sqlite3_column_blob(stmt, i));
                    int len = sqlite3_column_bytes(stmt, i);
                    row(column_names(i)) = std::vector<uint8_t>(value, value + len);
                }
                break;
                case SQLITE_NULL:
                {
                    row(column_names(i)) = "null";
                }
                break;
                default:
                    break;
                }
            }
            results.push_back(row);
        }

        return sqlite3_finalize(stmt);
    }

    template <typename column_names_iterator>
    const std::string sqlite::select_helper(
        const std::string& table_name,
        column_names_iterator name_begin,
        column_names_iterator name_end,
        const std::string& where_clause) {

        std::string sql{ "SELECT " };

        std::string separator{ "" };

        for (auto field = name_begin; field != name_end; ++field) {
            sql += separator + *field;
            separator = ",";
        }

        if (name_begin == name_end) {
            sql += "*";
        }

        sql += " FROM " + table_name;

        if (!where_clause.empty()) {
            sql += space_if_required(where_clause);
            sql += where_clause;
        }

        sql += ";";

        return sql;
    }


    template <typename where_bindings_iterator>
    int sqlite::delete_from(const std::string& table_name,
        const std::string& where_clause,
        where_bindings_iterator where_bindings_begin,
        where_bindings_iterator where_bindings_end) {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        const std::string sql = delete_from_helper(table_name, where_clause);

        sqlite3_stmt* stmt = NULL;
        EXIT_ON_ERROR(sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, NULL));

        EXIT_ON_ERROR(bind_where(stmt, where_bindings_begin, where_bindings_end));

        return step_and_finalise(stmt);
    }

    template <typename where_bindings_iterator>
    int sqlite::select_star(const std::string& table_name,
        const std::string& where_clause,
        where_bindings_iterator where_bindings_begin,
        where_bindings_iterator where_bindings_end,
        std::vector<std::map<std::string, sqlite_data_type>>& results) {
        std::string empty;
        return select_columns(table_name, empty.begin(), empty.end(), where_clause, where_bindings_begin, where_bindings_end, results);
    }
} // itel

#endif // SQLITE_HPP_

Implementation file, sqlite.cpp:

#include "sqlite.hpp"

#include <algorithm>
#include <iomanip>
#include <iostream>
#include <sstream>

namespace sql {

    std::ostream& operator<< (std::ostream& os, const column_values& v) {

        os << "name: " << v.column_name << ", value: ";

        switch (v.column_value.index()) {
        case 0: os << std::get<0>(v.column_value) << " of type int"; break;
        case 1: os << std::get<1>(v.column_value) << " of type double"; break;
        case 2: os << std::get<2>(v.column_value) << " of type string"; break;
        case 3:
        {
#ifdef PRINT_BLOB_AS_HEX
            auto previous_flags = os.flags();
            std::for_each(std::get<3>(v.column_value).begin(), std::get<3>(v.column_value).end(), (&os)(const uint8_t& byte) {
                os << std::hex << std::setfill('0') << std::setw(2) << (byte & 0xFF) << ' ';
                });
            os << " of type vector<uint8_t>";
            os.setf(previous_flags);
#else
            os << "<blob> of type vector<uint8_t>";
#endif
            break;
        }
        }

        return os;
    }

#ifdef PRINT_BLOB_AS_HEX
    std::ostream& operator<<(std::ostream & os, const std::vector<uint8_t>&v)
    {
        auto previous_flags = os.flags();
        std::for_each(v.begin(), v.end(), (&os)(const uint8_t& byte) {
            os << std::hex << std::setfill('0') << std::setw(2) << (byte & 0xFF) << ' ';
            });
        os.setf(previous_flags);
#else
    std::ostream& operator<<(std::ostream & os, const std::vector<uint8_t>& /* v */)
    {
        os << "<blob>";
#endif
        return os;
    }

    std::ostream& operator<<(std::ostream& os, const sqlite_data_type& v)
    {
        std::visit((&)(const auto& element) {
                os << element;
            }, v);

        return os;
    }

    std::ostream& operator<< (std::ostream& os, const std::map<std::string, sqlite_data_type>& v) {

        for (const auto& element : v) {
            os << element.first << ": " << element.second << '|';
        }

        return os;
    }

    sqlite::sqlite() : db_(nullptr) {}

    sqlite::~sqlite() {
        close();
    }

    int sqlite::open(const std::string& filename) {
        return sqlite3_open(filename.c_str(), &db_);
    }

    int sqlite::close() {
        if (db_ == nullptr) { return SQLITE_ERROR; }

        return sqlite3_close(db_);
    }

    int sqlite::last_insert_rowid() {
        return static_cast<int>(sqlite3_last_insert_rowid(db_));
    }

    int sqlite::delete_from(const std::string& table_name) {
        std::vector<where_binding> empty;
        return delete_from(table_name, "", empty.begin(), empty.end());
    }

    int sqlite::select_star(const std::string& table_name,
        std::vector<std::map<std::string, sqlite_data_type>>& results) {
        std::vector<where_binding> empty;
        return select_star(table_name, "", empty.begin(), empty.end(), results);
    }

    const std::string sqlite::get_last_error_description() {
        if (db_ == nullptr) { return ""; }

        const char* error = sqlite3_errmsg(db_);
        std::string s(error ? error : "");
        return s;
    }

    int sqlite::step_and_finalise(sqlite3_stmt* stmt) {
        if (stmt == nullptr) { return SQLITE_ERROR; }

        // whether error or not we must call finalize
        int rc = sqlite3_step(stmt);
        // SQLITE_ROW = another row ready - possible to configure to return a value - but we just ignore anything returned
        // SQLITE_DONE = finished executing

        // caller is more interested in the result of the step
        int finalise_rc = sqlite3_finalize(stmt);  // de-allocates stmt
        return rc == SQLITE_DONE ? finalise_rc : rc;
    }

    std::string sqlite::space_if_required(const std::string& s) {
        return !s.empty() && s(0) != ' ' ? " " : "";
    }

    std::string sqlite::delete_from_helper(
        const std::string& table_name,
        const std::string& where_clause) {
        std::string sql{ "DELETE FROM " + table_name };

        if (!where_clause.empty()) {
            sql += space_if_required(where_clause);
            sql += where_clause;
        }

        sql += ";";

        return sql;
    }

} // sql

Example main.cpp:

/*
    This example assumes you have created a database as follows:
    sqlite3.exe mydb.db

    CREATE TABLE test (name TEXT, age INTEGER, photo BLOB);
*/
#include "sqlite.hpp"

#include <iostream>
#include <string>
#include <vector>
#include <fstream>
#include <sstream>
#include <iomanip>

using namespace sql;

int main()
{
    sql::sqlite db;
    int rc = db.open("mydb.db");

    std::cout << "db.open returned: " << rc << std::endl;

    // picture from https://en.wikipedia.org/wiki/Mickey_Mouse
    std::ifstream f("Mickey_Mouse.png", std::ios::binary);

    if (!f.good()) {
        std::cout << "failed to open Mickey Mouse bitmap filen";
        return 1;
    }

    std::vector<uint8_t> buffer(std::istreambuf_iterator<char>(f), {});

    std::vector<sql::column_values> params {
        {"name", "Mickey Mouse"},
        {"age", 12},
        {"photo", buffer}
    };

    for (const auto& param : params) {
        std::cout << "inserting param: " << param << std::endl;
    }

    rc = db.insert_into("test", params.begin(), params.end());
    std::cout << "db.insert_into(...) returned: " << rc << std::endl;

    int lastrowid = 0;

    if (rc == SQLITE_OK) {
        lastrowid = db.last_insert_rowid();
        std::cout << "inserted into rowid: " << lastrowid << std::endl;
    }

    // let us now update this record
    std::vector<sql::column_values> updated_params{
    {"name", "Donald Duck"},
    {"age", 23}
    };

    const std::vector<where_binding>& bindings{
        {"rowid", lastrowid}
    };

    rc = db.update("test", updated_params.begin(), updated_params.end(), "WHERE rowid=:rowid", bindings.begin(), bindings.end());
    std::cout << "db.update(...) returned: " << rc << std::endl;

    // try SELECT
    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    // simplest way
    //rc = db.select_star("test", results);

    // using select_column to specifically display sqlite table rowid
    //rc = db.select_columns("test", { "rowid", "name", "age", "photo" }, {}, results);

    // Or pass in rowid and * to display rowid and all other columns
    //rc = db.select_columns("test", { "rowid", "*" }, {}, results);

    const std::vector<where_binding>& select_bindings{
       {"name", "Don%"}
    };

    std::vector<std::string> cols{ "rowid", "*" };
    rc = db.select_columns("test", cols.begin(), cols.end(), "WHERE name LIKE :name", select_bindings.begin(), select_bindings.end(), results);

    std::cout << "db.select_columns(...) returned: " << rc << std::endl;

    // print rows
    int i = 0;
    for (const auto& row : results) {
        std::cout << "row" << ++i << ": " << row << std::endl;
    }

    // finally delete row added
    const std::vector<where_binding>& delete_bindings {
        {"rowid", lastrowid}
    };

    rc = db.delete_from("test", "WHERE rowid=:rowid", delete_bindings.begin(), delete_bindings.end());
    std::cout << "db.delete_from(...) returned: " << rc << std::endl;

    // code below inserts into data into a table that does not exist

    // test to insert into an invalid column
    std::vector<sql::column_values> bad_params{
        {"nave", "Tanner"},
        {"address8", "3 The Avenue"},
        {"postcoode", "GU17 0TR"}
    };

    rc = db.insert_into("contacts", bad_params.begin(), bad_params.end());
    std::cout << "db.insert_into(...) returned: " << rc << std::endl;

    if (rc != SQLITE_OK) {
        std::cout << db.get_last_error_description() << std::endl;
    }
}

test.cpp:

#include "sqlite.hpp"

#include "sqlite3.h" // required for db_initial_setup

#include <string>
#include <iostream>
#include <cstdio>
#include <sstream>
#include <cstdio>
#include <filesystem>
#include <algorithm>

#include "gtest/gtest.h"

using namespace sql;

namespace {

    void db_initial_setup() {

        if (remove("contacts.db") != 0) {
            perror("Error deleting contacts.db");
        }

        // we create using c library so not using any of the code to exercise
        sqlite3* db;
        char* err_msg = 0;

        int rc = sqlite3_open("contacts.db", &db);

        if (rc != SQLITE_OK) {

            std::cerr << "Cannot open database: " << sqlite3_errmsg(db) << std::endl;
            sqlite3_close(db);

            FAIL() << "Cannot open database for testingn";
            return;
        }

        const char* sql() = {
            "DROP TABLE IF EXISTS contacts;"
            "CREATE TABLE contacts (name TEXT, company TEXT, mobile TEXT, ddi TEXT, switchboard TEXT, address1 TEXT, address2 TEXT, address3 TEXT, address4 TEXT, postcode TEXT, email TEXT, url TEXT, category TEXT, notes TEXT);"
            "CREATE INDEX idx_mobile ON contacts (mobile);"
            "CREATE INDEX idx_switchboard ON contacts (switchboard);"
            "CREATE INDEX idx_ddi ON contacts (ddi);",
            "CREATE TABLE calls(timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, callerid TEXT, contactid INTEGER);",
            "INSERT INTO contacts (name, mobile, switchboard, address1, address2, address3, postcode, email, url, category) VALUES("Test Person", "07788111222", "02088884444", "House of Commons", "Westminster", "London", "SW1A 0AA", "test@house.co.uk", "www.house.com", "Supplier");",
            "INSERT INTO calls (callerid, contactid) VALUES("07788111222", 1);"
        };

        size_t num_commands = sizeof(sql) / sizeof(char*);

        for (size_t i = 0; i < num_commands; ++i) {
            rc = sqlite3_exec(db, sql(i), 0, 0, &err_msg);

            if (rc != SQLITE_OK) {

                std::cerr << "SQL error: " << err_msg << std::endl;

                sqlite3_free(err_msg);
                sqlite3_close(db);
            }
        }
        sqlite3_close(db);
    }

    const std::string filename("contacts.db");
    std::vector<std::string> tables{ "contacts", "calls" };
}

class sqlite_cpp_tester : public ::testing::Test {
public:
    void SetUp() {
        db_initial_setup();
    }
};


TEST_F(sqlite_cpp_tester, given_a_valid_db_file_open_close_return_success) {

    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);
    EXPECT_EQ(db.close(), SQLITE_OK);
}

TEST_F(sqlite_cpp_tester, given_a_valid_insert_select_returns_same_as_inserted) {

    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    const std::vector<sql::column_values> fields {
    {"name", "Mickey Mouse"},
    {"company", "Disney"},
    {"mobile", "07755123456"},
    {"ddi", "01222333333"},
    {"switchboard", "01222444444"},
    {"address1", "1 The Avenue"},
    {"address2", "Greystoke"},
    {"address3", "Lower Wirmwood"},
    {"address4", "Baffleshire"},
    {"postcode", "PO21 4RR"},
    {"email", "mickey@disney.com"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "delightful mouse"}
    };

    EXPECT_EQ(db.insert_into("contacts", fields.begin(), fields.end()), SQLITE_OK);

    int lastrowid = db.last_insert_rowid();

    const std::vector<sql::where_binding>& bindings {
      {"rowid", lastrowid}
    };
    std::vector<std::map<std::string, sql::sqlite_data_type>> results;
    
    EXPECT_EQ(db.select_star("contacts", "WHERE rowid=:rowid", bindings.begin(), bindings.end(), results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results(0)("name"), fields(0).column_value);
    EXPECT_EQ(results(0)("company"), fields(1).column_value);
    EXPECT_EQ(results(0)("mobile"), fields(2).column_value);
    EXPECT_EQ(results(0)("ddi"), fields(3).column_value);
    EXPECT_EQ(results(0)("switchboard"), fields(4).column_value);
    EXPECT_EQ(results(0)("address1"), fields(5).column_value);
    EXPECT_EQ(results(0)("address2"), fields(6).column_value);
    EXPECT_EQ(results(0)("address3"), fields(7).column_value);
    EXPECT_EQ(results(0)("address4"), fields(8).column_value);
    EXPECT_EQ(results(0)("postcode"), fields(9).column_value);
    EXPECT_EQ(results(0)("email"), fields(10).column_value);
    EXPECT_EQ(results(0)("url"), fields(11).column_value);
    EXPECT_EQ(results(0)("category"), fields(12).column_value);
    EXPECT_EQ(results(0)("notes"), fields(13).column_value);
}


TEST_F(sqlite_cpp_tester, given_a_valid_insert_then_update_select_returns_same_as_updated) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    const std::vector<sql::column_values> fields{
    {"name", "Mickey Mouse"},
    {"company", "Disney"},
    {"mobile", "07755123456"},
    {"ddi", "01222333333"},
    {"switchboard", "01222444444"},
    {"address1", "1 The Avenue"},
    {"address2", "Greystoke"},
    {"address3", "Lower Wirmwood"},
    {"address4", "Baffleshire"},
    {"postcode", "PO21 4RR"},
    {"email", "mickey@disney.com"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "delightful mouse"}
    };

    EXPECT_EQ(db.insert_into("contacts", fields.begin(), fields.end()), SQLITE_OK);

    int lastrowid = db.last_insert_rowid();

    // UPDATE
    const std::vector<sql::column_values> updated_fields{
    {"name", "Donald Duck"},
    {"company", "Disney"},
    {"mobile", "07755654321"},
    {"ddi", "01222444444"},
    {"switchboard", "01222555555"},
    {"address1", "2 The Avenue"},
    {"address2", "Greystoke"},
    {"address3", "Lower Wirmwood"},
    {"address4", "Baffleshire"},
    {"postcode", "PO21 4RR"},
    {"email", "donald@disney.com"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "quackers"}
    };

    const std::vector<where_binding>& update_bindings{
      {"rowid", lastrowid}
    };

    const std::string where_clause{ "WHERE rowid=:rowid" };

    EXPECT_EQ(db.update("contacts", updated_fields.begin(), updated_fields.end(), 
        where_clause, update_bindings.begin(), update_bindings.end()), SQLITE_OK);

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    std::vector<std::string> cols { "rowid", "*" };

    EXPECT_EQ(db.select_columns("contacts", cols.begin(), cols.end(), "WHERE rowid=:rowid", update_bindings.begin(), update_bindings.end(), results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results(0)("name"), updated_fields(0).column_value);
    EXPECT_EQ(results(0)("company"), updated_fields(1).column_value);
    EXPECT_EQ(results(0)("mobile"), updated_fields(2).column_value);
    EXPECT_EQ(results(0)("ddi"), updated_fields(3).column_value);
    EXPECT_EQ(results(0)("switchboard"), updated_fields(4).column_value);
    EXPECT_EQ(results(0)("address1"), updated_fields(5).column_value);
    EXPECT_EQ(results(0)("address2"), updated_fields(6).column_value);
    EXPECT_EQ(results(0)("address3"), updated_fields(7).column_value);
    EXPECT_EQ(results(0)("address4"), updated_fields(8).column_value);
    EXPECT_EQ(results(0)("postcode"), updated_fields(9).column_value);
    EXPECT_EQ(results(0)("email"), updated_fields(10).column_value);
    EXPECT_EQ(results(0)("url"), updated_fields(11).column_value);
    EXPECT_EQ(results(0)("category"), updated_fields(12).column_value);
    EXPECT_EQ(results(0)("notes"), updated_fields(13).column_value);
}

TEST_F(sqlite_cpp_tester, given_a_single_quote_in_notes_field_select_returns_same_value_inserted) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    const std::vector<sql::column_values> fields{
    {"name", "Sean O'Hennessey"},
    {"company", "Disney"},
    {"mobile", "07755123456"},
    {"ddi", "01222333333"},
    {"switchboard", "01222444444"},
    {"address1", "1 The Avenue"},
    {"address2", "Greystoke"},
    {"address3", "Lower Wirmwood"},
    {"address4", "Baffleshire"},
    {"postcode", "PO21 4RR"},
    {"email", "mickey@disney.com"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "single quote symbol is '"}
    };

    EXPECT_EQ(db.insert_into("contacts", fields.begin(), fields.end()), SQLITE_OK);

    int lastrowid = db.last_insert_rowid();
    const std::vector<where_binding>& update_bindings{
       {"rowid", lastrowid}
    };

    std::vector<std::string> cols{ "rowid", "*" };
    const std::string where_clause{ "WHERE rowid=:rowid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("contacts", cols.begin(), cols.end(), 
        "WHERE rowid=:rowid", update_bindings.begin(), update_bindings.end(), results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results(0)("name"), fields(0).column_value);
    EXPECT_EQ(results(0)("company"), fields(1).column_value);
    EXPECT_EQ(results(0)("mobile"), fields(2).column_value);
    EXPECT_EQ(results(0)("ddi"), fields(3).column_value);
    EXPECT_EQ(results(0)("switchboard"), fields(4).column_value);
    EXPECT_EQ(results(0)("address1"), fields(5).column_value);
    EXPECT_EQ(results(0)("address2"), fields(6).column_value);
    EXPECT_EQ(results(0)("address3"), fields(7).column_value);
    EXPECT_EQ(results(0)("address4"), fields(8).column_value);
    EXPECT_EQ(results(0)("postcode"), fields(9).column_value);
    EXPECT_EQ(results(0)("email"), fields(10).column_value);
    EXPECT_EQ(results(0)("url"), fields(11).column_value);
    EXPECT_EQ(results(0)("category"), fields(12).column_value);
    EXPECT_EQ(results(0)("notes"), fields(13).column_value);
}


TEST_F(sqlite_cpp_tester, given_non_alphanumeric_characters_inserted_select_returns_same_value_inserted) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    const std::vector<sql::column_values> fields{
    {"name", "<---------------------->'"},
    {"company", "Dnisne    y"},
    {"mobile", "!!!"0775512345'''6"},
    {"ddi", "{}===================="},
    {"switchboard", "++++++++++++++++++++++++"},
    {"address1", "&&&&&&&&&&&&&&&&&&&&&&&&&"},
    {"address2", "``````````¬|"},
    {"address3", ";'#:@~"},
    {"address4", "'''''''''''''''''''"},
    {"postcode", "!"£$%^&*()_+"},
    {"email", "***************************"},
    {"url", "disney.com"},
    {"category", "cartoonist"},
    {"notes", "1n2n3n4n5n"}
    };

    EXPECT_EQ(db.insert_into("contacts", fields.begin(), fields.end()), SQLITE_OK);

    int lastrowid = db.last_insert_rowid();
    const std::vector<where_binding>& update_bindings{
       {"rowid", lastrowid}
    };

    std::vector<std::string> cols{ "rowid", "*" };
    const std::string where_clause{ "WHERE rowid=:rowid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("contacts", cols.begin(), cols.end(), 
        "WHERE rowid=:rowid", update_bindings.begin(), update_bindings.end(), results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results(0)("name"), fields(0).column_value);
    EXPECT_EQ(results(0)("company"), fields(1).column_value);
    EXPECT_EQ(results(0)("mobile"), fields(2).column_value);
    EXPECT_EQ(results(0)("ddi"), fields(3).column_value);
    EXPECT_EQ(results(0)("switchboard"), fields(4).column_value);
    EXPECT_EQ(results(0)("address1"), fields(5).column_value);
    EXPECT_EQ(results(0)("address2"), fields(6).column_value);
    EXPECT_EQ(results(0)("address3"), fields(7).column_value);
    EXPECT_EQ(results(0)("address4"), fields(8).column_value);
    EXPECT_EQ(results(0)("postcode"), fields(9).column_value);
    EXPECT_EQ(results(0)("email"), fields(10).column_value);
    EXPECT_EQ(results(0)("url"), fields(11).column_value);
    EXPECT_EQ(results(0)("category"), fields(12).column_value);
    EXPECT_EQ(results(0)("notes"), fields(13).column_value);
}

TEST_F(sqlite_cpp_tester, add_integer_value_select_returns_same_value_inserted) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    const std::vector<sql::column_values> fields{
    {"callerid", "0775512345"},
    {"contactid", 2}
    };

    EXPECT_EQ(db.insert_into("calls", fields.begin(), fields.end()), SQLITE_OK);

    const std::vector<where_binding> bindings{
       {"contactid", 2}
    };

    const char* result_cols() { "timestamp", "callerid", "contactid" };
    size_t cols_len = sizeof(result_cols) / sizeof(result_cols(0));
    const std::string where_clause{ "WHERE rowid=:rowid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("calls", result_cols, result_cols+cols_len, 
        "WHERE contactid=:contactid", bindings.begin(), bindings.end(), results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);

    EXPECT_EQ(results(0)("callerid"), fields(0).column_value);
    EXPECT_EQ(results(0)("contactid"), fields(1).column_value);
    // get 3 columns back
    EXPECT_EQ(results(0).size(), 3u);
}

// SELECT (using LIKE)
TEST_F(sqlite_cpp_tester, add_integer_value_select_like_returns_same_value_inserted) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    const sql::column_values fields() {
    {"callerid", "0775512345"},
    {"contactid", 2}
    };

    size_t len = sizeof(fields) / sizeof(fields(0));

    EXPECT_EQ(db.insert_into("calls", fields, fields + len), SQLITE_OK);

    const std::vector<where_binding> bindings{
       {"callerid", "077%"}
    };

    std::vector<std::string> cols { "timestamp", "callerid", "contactid" };
    const std::string where_clause{ "WHERE callerid LIKE :callerid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    EXPECT_EQ(db.select_columns("calls", cols.begin(), cols.end(), 
        where_clause, bindings.begin(), bindings.end(), results), SQLITE_OK);

    EXPECT_EQ(results.size(), 2u);

    EXPECT_EQ(std::get<std::string>(results(0)("callerid")), "07788111222");
    EXPECT_EQ(std::get<int>(results(0)("contactid")), 1);

    EXPECT_EQ(results(1)("callerid"), fields(0).column_value);
    EXPECT_EQ(results(1)("contactid"), fields(1).column_value);
    // get 3 columns back
    EXPECT_EQ(results(0).size(), 3u);
}

// GETCALLS
TEST_F(sqlite_cpp_tester, join_returning_data_from_two_tables_returns_correct_data) {
    sql::sqlite db;
    EXPECT_EQ(db.open("contacts.db"), SQLITE_OK);

    const std::vector<std::string> fields { "calls.timestamp", "contacts.name", "calls.callerid", "contacts.url" };

    const std::string where_clause{ "LEFT JOIN contacts ON calls.contactid = contacts.rowid" };

    std::vector<std::map<std::string, sql::sqlite_data_type>> results;

    const std::vector<where_binding> bindings {};  // none required

    EXPECT_EQ(db.select_columns("calls", fields.begin(), fields.end(), 
        where_clause, bindings.begin(), bindings.end(), results), SQLITE_OK);

    EXPECT_EQ(results.size(), 1u);
    EXPECT_EQ(std::get<2>(results(0)("callerid")), "07788111222");
    EXPECT_EQ(std::get<2>(results(0)("name")), "Test Person");
    EXPECT_EQ(std::get<2>(results(0)("url")), "www.house.com");
    EXPECT_NE(std::get<2>(results(0)("timestamp")), "");
}


int main(int argc, char **argv) {
    testing::InitGoogleTest(&argc, argv);
    return RUN_ALL_TESTS();
}

finder – SQLite for Dummies?

I’m trying to figure out why one of my drives is appearing multiple times in the Finder sidebar.
If I drag the list order a new one is generated.

enter image description here

From georgegarside blog – Mac Finder Sidebar Favourites Location I discovered the names & locations of the two files possibly responsible.
I tried throwing them away, but that was considerably more trouble than it was worth, as defaulting the Finder views entirely is just too much work to put back how I like it.

I checked SQLite frontend for OS X? & got hold of DB Browser for SQLite, thinking I could have a look in more detail & perhaps delete some of the dupes.

However, on attempting to open one of the plists, I’m presented with this indecipherable… erm… cipher.

enter image description here

I have absolutely no clue what information it requires.
How do I get past this bit at least, just so I can at least have a look at the file?

sqlite – why not sqlite3 supports asynchronous IO

With PRAGMA synchronous=FULL, I can make sure the data will be persisted to disk at every operation. This is expected to slow down the execution. I suppose sqlite implements this behavior using wirte(2) + fsync(2).

Observation

However, the latency of fsync(2) depends on the underlying disk. For example, in some of my NVMe SSD (i.e., Samsung-980pro, WD-SN850), this latency is long:
experiment result with fio
This result is obtained by

fio --filename={/dev/nvme0n1, /dev/nvme1n1, /dev/sda} --runtime=15 --size=400G --direct=1 --buffered=0 --rw=randwrite --bs=4k --numjobs=16 --iodepth=1 --group_reporting --name=write --ioengine=sync --fsync={1, 0} --sync={0, 1}

As shown, these two NVMe SSD has longer latency than SATA SSD (Samsung-860evo). One possible reason for this may be NVMe SSD accelerate IO whose io-depth is large (e.g., 32) but when using the synchronous io, the io-depth will degenerate to exactly 1.

Problem

Given this observation, I find when I use PRAGMA synchronous=FULL, some workload (fillrandom in db_bench_sqlite3) runs slower in NVMe SSD (WD-SN850) than in SATA SSD (Intel-S4510)
experiment result with db_bench_sqlite3
Focus on the light-blue bar VS. dark-green bar when synchronous=FULL/EXTRA.
This behavior is more severe in Samsung-980pro (not drawn in the figure)

Possible Solution

when I change the fio parameter from synchronous io to libaio, something changes. Results. As shown, when using O_SYNC and write_aio(), NVMe SSDs become faster than SATA SSD as expected (not understand why write_aio() + fsync() is still problematic). So one possible solution for this problem is to make Sqlite3 support asynchronous IO like libaio (io_setup(2), io_destroy(2), io_submit(2), io_cancel(2), io_getevents(2)) or something new like io_uring.

Conclusion

The asynchronous IO is more friendly to the new NVMe storage devices. But why not SQLite3 does not support it.

There must be reasons why SQLite developers do not reply, I just want to figure it out.

Order a nested table with path index according to list_order in SQLite

I have a SQL table with this nested structure:

CREATE TABLE items (`id` INTEGER, `item_name` VARCHAR(6), `parent_id` INTEGER, `list_order` INTEGER);

My SQLite version does not support window function, currently my query at link SQLite query are returning as follow the result, but it’s not ordered according to list_order.

   id      name_item   parent_id  level  path_index list_order
---------------------------------------------------------------
   1        Pois           0          0     1           4
   2        Então          0          0     2           5
   3        Teste          0          0     3           3
   11       Teste 3        3          1     3.1         2
   12       Teste 2        3          1     3.2         0
   13       Teste 1        3          1     3.3         1
   4        Fundo          0          0     4           2
   7        Profundo       4          1     4.1         1
   8        Dhdhd          4          1     4.2         0
   9        Gagagaga       8          2     4.2.1       1
   14       Fsfsfsfs       8          2     4.2.2       0
   5        Profundo       0          0     5           1
   6        Gigante        0          0     6           6
   86       Teste          0          0     7           0

What I want the path_index column to follow list_order column, like here:

   id      name_item   parent_id  level  path_index list_order
---------------------------------------------------------------
   86       Teste          0          0     1           0
   5        Profundo       0          0     2           1
   4        Fundo          0          0     3           2
   8        Dhdhd          4          1     3.1         0
   14       Fsfsfsfs       8          2     3.1.1       0
   9        Gagagaga       8          2     3.1.2       1
   7        Profundo       4          1     3.2         1
   3        Teste          0          0     4           3
   12       Teste 2        3          1     4.1         0
   13       Teste 1        3          1     4.2         1
   11       Teste 3        3          1     4.3         2
   1        Pois           0          0     5           4
   2        Então          0          0     6           5
   6        Gigante        0          0     7           6

How can I align the path_index sorted according to list_order and level columns?

javascript – Como usar SQLite junto com electron ? desenvolvendo um ERP

Fala galera estou em um projeto de app desktop que preciso que funcione inteiramente no lado do cliente
para isso estou usando a stack Electron,Vue e SQlite , porem estou encontrando dificuldades em como usar o sqlite no projeto , eu consigo me comunicar com a database com esse codigo


let db = new sqlite3.Database('../database/Dados.db',sqlite3.OPEN_READWRITE, (err) => {
if(err){
 return console.error(err.message);
}
console.log("conected sucessufuly with SQLite database")
});

db.close((err) => {
if(err){
 return console.error(err.message);
}
console.log('close the database conection');
});````const sqlite3 = require('sqlite3').verbose();
//cria um objeto database , o metodo dabatanse aceita dois argumentos
//1° é o path para o arquivo db
//2° é o modo que ele ira abrir , pode ser read-only,read-write,default
let db = new sqlite3.Database('../database/Dados.db',sqlite3.OPEN_READWRITE, (err) => {
if(err){
 return console.error(err.message);
}
console.log("conected sucessufuly with SQLite database")
});

db.close((err) => {
if(err){
 return console.error(err.message);
}
console.log('close the database conection');
});

isso em um arquivo separado chamado conection.js , minha duvida é como proceder para usar o database ao longo da aplicação , como disparar querys através de eventos etc qualquer fonte de informação é bem vinda agradeço desde já.

sqlite – Usando JOINs em SQL

No meu projeto, estou tendo algumas dificuldades em retornar valores de outros campos de outras tabelas. Fiz um outro exemplo mais simples para explicar melhor abaixo:

inserir a descrição da imagem aqui

No exemplo, eu quero retornar o nome do cargo e do departamento juntamente com os dados do funcionário. Quando tento fazer uma busca para que retorne os registros do funcionário mesmo não tendo registro ou no campo nome de departamento ou nome de cargo, recebi uma mensagem de que o SQLite não suporta o uso de FULL OUTER JOIN.

SQL Error (1): (SQLITE_ERROR) SQL error or missing database (RIGHT and FULL OUTER JOINs are not currently supported)

Cada cargo e cada departamento eu coloquei um ID e queria que, no caso, para retornar os respectivos nomes, retornar o nome do cargo/departamento e não o ID.
Existe uma maneira de no caso uma pessoa ter 2 cargos na empresa e eu conseguir retornar estes dois cargos caso tivesse os devidos campos id_cargo_1 e id_cargo_2, tendo 2 JOINs na mesma tabela?

sqlite – Best way to arrange a hairdresser database based on their skills

I need some help in a smart way to organize a database for a hairdresser shop.

So far, I understand that I need a table for the hairdressers containing usual data such as id, name, etc.

I also think I need a table for the procedures the hairdressers can perform. This table would contain the procedure id, description, time required, cost, price and so on.

But I don’t see a good way to link those two tables. For example: Professional A can do procedures #1 and #3, but not #2.

How to include this info in the database?

I thought of two possible ways:

  1. Make the procedures be columns in the Professionals table, and assign true in the column in case a professional can perform that procedure;

  2. Make the professionals be columns in the Procedures table, assigning true if the procedure can be performed by that professional.
    Either possibilities don’t see quite the best way to achieve what I want, specially if the number of professionals/procedures get large.

Since I have very little experience in this, I count on your help giving me some insights in this matter.

Thanks in advance.

DreamProxies - Cheapest USA Elite Private Proxies 100 Private Proxies 200 Private Proxies 400 Private Proxies 1000 Private Proxies 2000 Private Proxies ExtraProxies.com - Buy Cheap Private Proxies Buy 50 Private Proxies Buy 100 Private Proxies Buy 200 Private Proxies Buy 500 Private Proxies Buy 1000 Private Proxies Buy 2000 Private Proxies ProxiesLive Proxies-free.com New Proxy Lists Every Day Proxies123