Here is a wee bit of C code that you can compile as a plugin that will give you 
the row size (well, it may be bigger than the actual record size by a few bytes 
but it is pretty close) ... 

works properly for utf-16 encoded databases as well.

-----//----- sqlsize.c -----//-----
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

#ifndef SQLITE_PRIVATE
    #define SQLITE_PRIVATE static
#endif

static inline sqlite_int64 _varIntSize_(sqlite_int64 v)
{
    sqlite_int64 uu;

    if (v<0)
        uu = ~v;
    else
        uu = v;
    if (uu <= 127 )
        return 1;
    else if (uu <= 32767)
        return 2;
    else if (uu <= 8388607)
        return 3;
    else if (uu <= 2147483647)
        return 4;
    else if (uu <= 140737488355327LL)
        return 6;
    else return 8;
}

SQLITE_PRIVATE void _recSizeFunc(sqlite3_context *context, int argc, 
sqlite3_value **argv)
{
    sqlite_int64 maxsize = 0;
    sqlite_int64 sz;
    int i;

    for (i=0; i<argc; i++)
    {
        switch (sqlite3_value_type(argv[i]))
        {
            case SQLITE_NULL:
                maxsize += 1;
                break;
            case SQLITE_INTEGER:
                maxsize += _varIntSize_(sqlite3_value_int64(argv[i])) + 1;
                break;
            case SQLITE_FLOAT:
                maxsize += 9;
                break;
            case SQLITE_TEXT:
                sqlite3_value_blob(argv[i]);
                sz = sqlite3_value_bytes(argv[i]);
                maxsize += sz + _varIntSize_(sz*2+12);
                break;
            case SQLITE_BLOB:
                sz = sqlite3_value_bytes(argv[i]);
                maxsize += sz + _varIntSize_(sz*2+13);
                break;
        }
    }
    sqlite3_result_int64(context, maxsize);
}

#ifdef _WIN32
#ifndef SQLITE_CORE
__declspec(dllexport)
#endif
#endif
int sqlite3_sqlsize_init(sqlite3 *db, char **pzErrMsg, const 
sqlite3_api_routines *pApi)
{
    SQLITE_EXTENSION_INIT2(pApi);

    return sqlite3_create_function(db, "recsize", -1, 
SQLITE_ANY|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS,  0, _recSizeFunc, 0, 0);
}
-----//-----

It is a bit of a PITA to call, but thats how the cookie crumbles ...

>sqlite3 \data\apps\splunk\splunk.db
SQLite version 3.31.0 2020-01-27 11:17:22
Enter ".help" for usage hints.
sqlite> .schema details
CREATE TABLE Details
(
    ApplianceID     integer not null references Appliances(ApplianceID) on 
delete cascade,
    SrcIntID        integer not null references Interfaces(InterfaceID) on 
delete cascade,
    DstIntID        integer not null references Interfaces(InterfaceID) on 
delete cascade,
    Transport       text not null collate nocase,
    SrcHostID       integer not null references Hosts(HostID) on delete cascade,
    SrcPort         integer not null,
    DstHostID       integer not null references Hosts(HostID) on delete cascade,
    DstPort         integer not null,
    Action          integer not null,
    Count           integer not null,
    FileID          integer not null references Files(FileID) on delete cascade
);
sqlite> select _rowid_, recsize(ApplianceID, SrcIntID, DstIntID, Transport, 
SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID) from details 
limit 10;
1|27
2|27
3|27
4|27
5|27
6|28
7|27
8|27
9|28
10|27
sqlite> select max(recsize(ApplianceID, SrcIntID, DstIntID, Transport, 
SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID)) from details;
31
sqlite> select avg(recsize(ApplianceID, SrcIntID, DstIntID, Transport, 
SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID)) from details;
29.4473800800817
sqlite> select min(recsize(ApplianceID, SrcIntID, DstIntID, Transport, 
SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID)) from details;
26
sqlite> select skew(recsize(ApplianceID, SrcIntID, DstIntID, Transport, 
SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID)) from details;
-0.378384651017371
sqlite> select kurt(recsize(ApplianceID, SrcIntID, DstIntID, Transport, 
SrcHostID, SrcPort, DstHostID, DstPort, Action, Count, FileID)) from details;
0.130516904446944

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Deon Brewis
>Sent: Friday, 24 January, 2020 21:14
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] Row length in SQLITE
>
>No I mean e.g.
>
>row 1 = 500 bytes,
>row 2 = 600 bytes
>row 3 = 80 bytes
>row 4 = 300 bytes
>
>etc.
>
>Like the info that DBSTAT gives, but per row, not per page. This doesn't
>need to be performant - it's for usage analysis during development time.
>
>- Deon
>
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Simon Slavin
>Sent: Wednesday, January 22, 2020 5:24 PM
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] Row length in SQLITE
>
>On 22 Jan 2020, at 11:44pm, Deon Brewis <de...@outlook.com> wrote:
>
>> Is there any way to get the length of rows in a table / index in
>sqlite?
>
>Do you mean the count of rows in a table / index ?
>
>   SELECT count(*) FROM MyTable
>
>There's no easy fast way to do this because SQLite doesn't keep that
>number handy anywhere.  It stores the entries in a tree and it would have
>to manually count the leaves of the tree.
>
>----
>
>Or do you mean the count of columns in a table / index ?
>
>    SELECT * FROM MyTable LIMIT 1
>
>and count the number of columns returned.  Or in C
>
>    sqlite3_column_count()
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to