CODEWITHSUNDEEP
databasesqlite
user2139641
user2139641

Reputation: 35

How to increase/double size of an sqlite database file

I would like to make some tests on huge size (200+ MB) Sqlite files. I have some relatively small files (10MB) but I would like to test larger files.

Is there any fast way/tool to increase the size of these Sqlite files by duplicating the data inside their tables?

Alternatively, is there any site that can freely provide me with huge Sqlite files for download?

Thank you.

Upvotes: 2

Views: 606

Answers (3)

Jesús López
Jesús López

Reputation: 9221

If you want to test large databases you could generate and insert fake data. There are tools like Bogus to help you.

Here you have an example in c# that creates a database with an Orders table and uses bogus to generate ten million orders:

using Bogus;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BogusSample
{
    public class Order
    {
        public int OrderId { get; set; }
        public int CustomerId { get; set; }
        public int EmployeeId { get; set; }
        public DateTime OrderDate { get; set; }
        public Decimal OrderTotal { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var watch = Stopwatch.StartNew();

            int tenMillion = 10 * 1000 * 1000;

            int orderId = 1;
            var orderFaker = new Faker<Order>()
                .StrictMode(true)
                .RuleFor(x => x.OrderId, f => orderId++)
                .RuleFor(x => x.CustomerId, f => f.Random.Number(1, 2000))
                .RuleFor(x => x.EmployeeId, f => f.Random.Number(1, 100))
                .RuleFor(x => x.OrderDate, f => f.Date.Recent(400))
                .RuleFor(x => x.OrderTotal, f => f.Finance.Amount(10, 1000, 2));

            using (var cn = RecreateDatabase())
            using (var cmd = CreateInsertCommand(cn))
            using (var tx = cn.BeginTransaction())
            {
                foreach (Order fakeOrder in orderFaker.Generate(tenMillion))
                {
                    cmd.Parameters["@OrderId"].Value = fakeOrder.OrderId;
                    cmd.Parameters["@CustomerId"].Value = fakeOrder.CustomerId;
                    cmd.Parameters["@EmployeeId"].Value = fakeOrder.EmployeeId;
                    cmd.Parameters["@OrderDate"].Value = fakeOrder.OrderDate;
                    cmd.Parameters["@OrderTotal"].Value = fakeOrder.OrderTotal;
                    cmd.ExecuteNonQuery();
                }
                tx.Commit();
            }

            Console.WriteLine("Elapsed Time: {0}", watch.Elapsed);
        }

        static SQLiteCommand CreateInsertCommand(SQLiteConnection cn)
        {
            var insertCommandText = @"
INSERT INTO Orders VALUES(@OrderId, @CustomerId, @EmployeeId, @OrderDate, @OrderTotal)
";
            var cmd = new SQLiteCommand(insertCommandText, cn);
            cmd.Parameters.Add("@OrderId", DbType.Int32);
            cmd.Parameters.Add("@CustomerId", DbType.Int32);
            cmd.Parameters.Add("@EmployeeId", DbType.Int32);
            cmd.Parameters.Add("@OrderDate", DbType.DateTime);
            cmd.Parameters.Add("@OrderTotal", DbType.Decimal);

            return cmd;

        }


        static SQLiteConnection RecreateDatabase()
        {
            var dataBasePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Orders.db");
            if (File.Exists(dataBasePath)) File.Delete(dataBasePath);
            var cn = new SQLiteConnection(string.Format("Data Source=\"{0}\"", dataBasePath));
            var createTableCommandText = @"
CREATE TABLE orders 
(
    OrderId INTEGER PRIMARY KEY,
    CustomerId INT NOT NULL,
    EmployeeId INT NOT NULL,
    OrderDate DATETIME NOT NULL,
    OrderTotal NUMERIC(19, 2)
)
";
            using (var cmd = new SQLiteCommand(createTableCommandText, cn))
            {
                cn.Open();
                cmd.ExecuteNonQuery();
            }

            return cn;
        }
    }
}

It took about 30 minutes (5500 rows per second) to insert the rows in my laptop. The database file size was about 512 Mb.

Upvotes: 4

CL.
CL.

Reputation: 180080

To duplicate all data in a table, insert all its data into itself:

INSERT INTO MyTable(a, b, c) SELECT a, b, c FROM MyTable;

If you have an autoincrementing ID, just leave it out of the column lists to automatically get new values. If you have some other primary key, you must force it to get unique values by modifying the original values approriately, for example:

INSERT INTO MyTable(id, b, c) SELECT id + 1000000, b, c FROM MyTable;

Upvotes: 0

EFraim
EFraim

Reputation: 13028

You can create a large cross product without using any external tools. This is an example for 16 rows:

CREATE TABLE DIGITS (D INTEGER);
INSERT INTO DIGITS VALUES (0);
INSERT INTO DIGITS VALUES (1);
CREATE TABLE PROD (A INTEGER, B INTEGER, C INTEGER, D INTEGER);
INSERT INTO PROD SELECT d1.D, d2.D, d3.D, d4.D FROM DIGITS d1, DIGITS d2, DIGITS d3, DIGITS d4;

You should alter the number of columns in the prod table to fit your desired table size.

Upvotes: 1

Related Questions