[Info-ingres] [openroad-users] Saving .jpg in Ingres

Wed Aug 3 08:03:07 UTC 2016

Thanks Charaka,

That is good stuff.

First:

You should migrate to Ingres 10.x  and OpenROAD 6.2

I recall seeing 9.x copydb generating very large exports. 

Second:

I avoid copydb -c (I’ve written on this subject previously)

although in this case it doesn’t make much difference to size.

Here are my test results

|crm_contact_person_image|file_content|iietab_234_235|704|1523712|

|crm_cust_email|document_content|iietab_238_239|0|40960|

|crm_document|document_content|iietab_23a_23b|420608|888782848|

|crm_document_repository|document_content|iietab_23c_23d|0|40960|

|crm_email_attachment|attachment_content|iietab_23e_23f|0|40960|

|crm_enduser_contact_person_image|file_content|iietab_241_242|9|57344|

|vms_agreement_document|document_content|iietab_6ee_6ef|299|663552|

|vms_company|comapanylogocontent|iietab_6f1_6f2|15|65536|

|vms_image|visitor_image|iietab_6fd_6fe|6804|14401536|

|vms_visitor_fingerprint|visitor_fingerprint|iietab_707_708|0|40960|

|vms_visitor_image|visitor_image|iietab_709_70a|2184844|4616634368|

copydb -c

-rw-rw-r--. 1 ingres ingres 1392334 Aug  3 17:54 crm_contact_person_image.ingres

-rw-rw-r--. 1 ingres ingres    0 Aug  3 17:54 crm_cust_email.ingres

-rw-rw-r--. 1 ingres ingres 831748295 Aug  3 17:54 crm_document.ingres

-rw-rw-r--. 1 ingres ingres    0 Aug  3 17:54 crm_document_repository.ingres

-rw-rw-r--. 1 ingres ingres    0 Aug  3 17:54 crm_email_attachment.ingres

-rw-rw-r--. 1 ingres ingres 17135 Aug  3 17:54 crm_enduser_contact_person_image.ingres

-rw-rw-r--. 1 ingres ingres 590780 Aug  3 17:54 vms_agreement_document.ingres

-rw-rw-r--. 1 ingres ingres 28319 Aug  3 17:54 vms_company.ingres

-rw-rw-r--. 1 ingres ingres 13458852 Aug  3 17:54 vms_image.ingres

-rw-rw-r--. 1 ingres ingres    0 Aug  3 17:54 vms_visitor_fingerprint.ingres

-rw-rw-r--. 1 ingres ingres 4324618546 Aug  3 17:54 vms_visitor_image.ingres

copydb

-rw-rw-r--. 1 ingres ingres 1391289 Aug  3 17:58 crm_contact_person_image.ingres

-rw-rw-r--. 1 ingres ingres    0 Aug  3 17:58 crm_cust_email.ingres

-rw-rw-r--. 1 ingres ingres 831409805 Aug  3 17:58 crm_document.ingres

-rw-rw-r--. 1 ingres ingres    0 Aug  3 17:58 crm_document_repository.ingres

-rw-rw-r--. 1 ingres ingres    0 Aug  3 17:58 crm_email_attachment.ingres

-rw-rw-r--. 1 ingres ingres 17131 Aug  3 17:58 crm_enduser_contact_person_image.ingres

-rw-rw-r--. 1 ingres ingres 590416 Aug  3 17:58 vms_agreement_document.ingres

-rw-rw-r--. 1 ingres ingres 28606 Aug  3 17:58 vms_company.ingres

-rw-rw-r--. 1 ingres ingres 13448916 Aug  3 17:58 vms_image.ingres

-rw-rw-r--. 1 ingres ingres    0 Aug  3 17:58 vms_visitor_fingerprint.ingres

-rw-rw-r--. 1 ingres ingres 4321404836 Aug  3 17:58 vms_visitor_image.ingres

Third:

Are you able to compress the images to a lower resolution. In my experience you can adequately store typewritten and printed text to about 40KB grayscale.

Last:

The calcs look good for your data.

iietab_91d_91e

15778 x 8K pages = 129,253,376 bytes allocated

2000 x 61163 rows = 122,326,000 bytes  data.   

This is about right allowing for primary key and fill factor

122,326,000 / 47 images = 2,602,681 bytes per image.    This is about the same as your size estimate.

Paul

From: openroad-users at googlegroups.com [mailto:openroad-users at googlegroups.com] On Behalf Of Charaka Wijerathne
Sent: Wednesday, 3 August 2016 3:20 PM
To: paul.white at shift7solutions.com.au
Cc: openroad-users at googlegroups.com; openroad-developer at lists.ingres.com; info-ingres at lists.planetingres.org
Subject: RE: [openroad-users] Saving .jpg in Ingres

Hi Paul,

01:          Through our android system, we are receiving images from Client’s mobile.

Clients take a picture of their Paper Timesheet and will attach and submit with Mobile Timesheet.

Web server that hosted mobile services, will receive this and it will save those details and picture in ingres DB.

GettingImageFromMobile.jpg

SavingToDB.jpg

Querry.jpg

02:          This is the command that used for taking backup,

·         copydb -uingres -dest=. -source=. pit::egans -c timesheet_image

·         sql -uingres pit::egans < copy.out > copyout.LOG

03:          please find attached log file for output of other commands

·         Examine.LOG

Thanks.

Kind Regards,

Charaka Wijerathne

From: Paul White [mailto:paul.white at shift7solutions.com.au] 
Sent: Wednesday, August 03, 2016 10:18 AM
To: Charaka Wijerathne
Cc: openroad-users at googlegroups.com; openroad-developer at lists.ingres.com; info-ingres at lists.planetingres.org
Subject: RE: [openroad-users] Saving .jpg in Ingres

Hi Charaka,

You didn’t say how you inserted the data – I presume OpenROAD.  Please post a simple sample.

And didn’t say how you copied out the table.  Please post the command.

I’m guessing you have used copydb -c

please post the output of these commands

help table timesheet_image; \p\g

select r1.relid as base_table,

       c.attname as column_name,

       r2.relid as extend_table, t.num_rows, t.number_pages * t.table_pagesize tabsize

from iirelation r1,iirelation r2,iiattribute c,iiextended_relation e, iitables t

where r1.reltid=e.etab_base and r2.reltid=e.etab_extension

  and r1.reltid=c.attrelid

  and c.attid=e.etab_attid

  and r1.reltidx=0

  and c.attrelidx=0

and r2.relid = t.table_name

and r1.relid = 'timesheet_image'

order by base_table,column_name; \p\g

help table iietab_XXXXXXX;   -- determined from previous statement

Paul

From: openroad-users at googlegroups.com [mailto:openroad-users at googlegroups.com] On Behalf Of Charaka Wijerathne
Sent: Wednesday, 3 August 2016 2:34 PM
To: openroad-users at googlegroups.com; openroad-developer at lists.ingres.com
Cc: paul.white at shift7solutions.com.au
Subject: [openroad-users] Saving .jpg in Ingres

Hi Guys,

We want to save JPG files in a table (in ingres DB)

Table schema as shown below,

       /* TABLES */

\continue

create table timesheet_image(

       timesheet_image_id   varchar(36) not null default '',

       timesheet_id         varchar(36) not null default '',

       image_no             integer not null default 0,

       content              long byte not null default ''

)

with noduplicates,

nojournaling,

page_size = 8192,

location = (ii_database),

security_audit=(table,norow)

\p\g

modify timesheet_image to btree unique on

       timesheet_image_id

with nonleaffill = 80,

       leaffill = 70,

       fillfactor = 80,

       extend = 16,

       page_size = 8192

\p\g

We have inserted 50 records, then took a backup of this table.

timesheet_image.ingres file is more than 10GB. But all our JPG files were 2MB-3MB

Why this table taking this much of space ?

Could you please advise me what is the best solution to work with (saving & retrieving) JPG files in ingres ?

PC environment,

·         Windows Server 2012 R2

·         Ingres 9.2.1

·         OR 5.0/0804

Thanks.

Kind Regards,

Charaka Wijerathne

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20160803/7fd45978/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 54974 bytes
Desc: not available
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20160803/7fd45978/attachment.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.jpg
Type: image/jpeg
Size: 66326 bytes
Desc: not available
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20160803/7fd45978/attachment-0001.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.jpg
Type: image/jpeg
Size: 31341 bytes
Desc: not available
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20160803/7fd45978/attachment-0002.jpg>