Some formatting in SQLite

[Iczer]

I'm using  Virtual listview with data are stored in a database in explorer-like script. One column if file size in bytes (as it returned by FileGetSize() function).

What i wanted, is to reformat shown size in bytes to something more readable:

0 ... 2^10 - "*** Bytes"

2^10 ... 2^20 - "*** kBytes"

2^20 ... 2^30 - "*** MBytes"

and so on - ideally it shouldn't affect real data in main table and only affect DisplayView table (or temp view).

But main problem - speed. Is there a some useful  SQLite functions for it?

$sQuery1 = "UPDATE DisplayMemDb.RowRelation SET FileSize = FileSize || ' Bytes' WHERE FileSize < 1024;"
$sQuery2 = "UPDATE DisplayMemDb.RowRelation SET FileSize = round(FileSize/1024) || ' MBytes' WHERE FileSize BETWEEN 1024 AND 1048576;"

 

Edited October 2, 2016 by Iczer

[jchd]

Do you really think speed is a problem? I don't.

Say you have a table declared like this:

CREATE TABLE "C" (
  "Id" INTEGER PRIMARY KEY,
  "Item" CHAR,
  "Size" INTEGER
);

Then here's a view DDL for a display similar to what you want:

CREATE VIEW "CC" AS 
select id,
       item,       
       size,
       case
           when size < 1024 then printf('% 10i', size) || ' bytes'
           when size < 1048576 then printf('%10.2f', (size / 1024.0)) || ' kb'
           when size < 1073741824 then printf('%10.2f', (size / 1048576.0)) || ' Mb'
           when size < 1099511627776 then printf('%10.2f', (size / 1073741824.0)) || ' Gb'
           when size < 1125899906842624 then printf('%10.2f', (size / 1099511627776.0)) || ' Tb' 
       end "Readable size"
from C;

Adapt it to your actual situation and display preferences: field size, number of decimals, extent to Yb, ...