Re: Tim Bunce - RE: DBD-DBI Insert Binary Files and using Perl modules in cPanel environment

[pali]

Hi! I do not know if it helps you, but processing binary data with MySQL
or MariaDB server and Perl's DBI can be tricky due to limitation of
MySQL protocol. Some details I wrote to the DBD::MariaDB documentation:

https://metacpan.org/pod/DBD::MariaDB#Working-with-binary-data

Re: Tim Bunce - RE: DBD-DBI Insert Binary Files and using Perl modules in cPanel environment

[John Scoles]

I would agree MySQL my not be the platform you want.  It is limited in what it 
can do.  I would give Postgress SQL a go.  You have the use the  'pg_lo_???' 
functions to manipulate your objects.  They work quite well.

By the way saving  images, mp3 etc  as blobs or lobs to a DB is not used in 
many new systems. It is now a rather dated as  it is much cheaper, more 
efficient and even more secure to save the file up on the cloud someplace (ie 
Amazon S3) and then use the DB to simply point to that file.

Blobs and Lobs where a solution to a problem from 20+ years ago when disk-space 
was very expensive and ban-with was low.  If you had to download a 10mg file 
you would have to break it up into 10~1 mg  blocks and lob/blobs where very 
good at this.  At the time someone with a home line over 128k was quite rare 
56k being the morm and 1meg being fantastically expensive.





From: Kimar Miller 
Sent: September 23, 2018 7:34 PM
To: dbi-users@perl.org
Subject: Tim Bunce - RE: DBD-DBI Insert Binary Files and using Perl modules in 
cPanel environment

Hi Tim - How's it going?

My name is Kimar and I'm looking to find out how to go about the development of 
a video application using the database MySQL as I own my own Domain name and am 
renting web host. This app may extend to greater than just a video but more of 
a multimedia app.

I'm a developer based in Florida; I was trained at Atlantic Technical College 
Coconut Creek. My instructor Ellen Williams; the hardest nut to crack but a 
very generous woman. Lots of years experience and lots of students. I graduated 
July 2017 and I've been doing some serious work on my own until I was 
approached by some friends of mine that have angel investors interested in a 
security login I've created.

I was recently attacked by someone from In2Cable India. I traced the IP that 
was logged in the cPanel. The deal is my app is more secure than my cPanel that 
requires me to have a hardware firewall that much I discovered after the breach 
that there's little I can do about it unless there's a hardware firewall or an 
app that is integrated in my browser to encrypt my submission to the login 
until it hits the server. None the less I have ongoing copyrighting processes 
occurring. You may or may not know about it but copyright protects you more 
than patents. Explaining what your thing does in the copyright proves you are 
the creator based on copyright clause "Works of the Mind".

I am particularly interested in having the system input the actual audio and 
video files such as MP3, or MPEG files to the database. I don't care about 
memory or resources.

CREATE TABLE `video` (
 `ID` int(11) NOT NULL
 PRIMARY KEY ,
  `NAME` varchar(40) DEFAULT NULL,
  `DESCRIPTION` varchar(100) DEFAULT NULL,
 `BLOBFILE` varchar(20) );

That's the table I used and the web page below


 Test submit 


 Test Perl Programming CGI post
http://post.pl>" method="POST">
home.pl: Nr 1 w Polsce. Domeny, Hosting, Serwery WWW, Strony, 
Sklepy
post.pl
Domeny internetowe: 1,2 mln | Serwery wirtualne: 150 tys. | Sklepy internetowe: 
7 tys. | Działamy niezawodnie od 20 lat. Dołącz do 2 mln użytkowników home.pl!



 Select a file to upload
Name: 
Description: 






My question is how can I get the insert to work.

I tried reading the books on this topic and did a global search, I long after 
exhausted all the links available.

I also am interested in using modules, is there a better explanation on using 
the maker object on CPAN its a hard read as I still am lost to some of the 
concepts in the manual.

I heavily rely on the perl.org resources so I decided to ask a 
professional.

RE: Tim Bunce - RE: DBD-DBI Insert Binary Files and using Perl modules in cPanel environment

[Fennell, Brian]

If you encode your binary data as pure ascii, in and out, any database will do 
just fine.

Encodings to consider are mime-64 (https://metacpan.org/pod/MIME::Base64) and 
q-encoding (https://en.wikipedia.org/wiki/Quoted-printable ) - both are 
lossless and can store any binary data.  Hexadecimal representation of the 
binary data also works but is less efficient.  You can also consider 
compressing the data before converting to a pure-ascii representation to help 
address space concerns.  XML with "character entities" ( &d; encoding) is 
another option - or some mix of these.  To store large runs of text in the 
database you can break the text into segments.  If the "binary files" are 
mostly text with some "binary" characters you can use q-encoding fileid, line, 
segment (three fields of type "NUMBER" storing an integer).   If the data is 
truly binary (no discernable structure) you can use fileid, block, segment 
(three NUMBER fields).  You can then use VARCHAR with a size of 256 or 512 or 
1024 (etc) to store the encoded binary data as characters.  Databases which 
offer "BLOB" (Binary Large OBject) or "CLOB" (Character Large OBject) 
capability often do so in a way that trades efficiency for flexibility and 
standards compliance.  The method I suggest should not need anything beyond 
standard capabilities that every database, mysql included, offers.  It may be 
less efficient than choosing a specific database and exploiting the specific 
capabilities of that database.

If you stick to LCD (Least Common Denominator) for your database interactions 
then you can even make the database type configurable at run-time.

I once stored arbitrary text (log) files in an Oracle database which had a 
maximum VARCHAR size of 2048.  My code broke lines up into segments of no more 
than 512 characters and I q-encoded any non-ascii characters.  The source 
character encoding was a one-byte-to-a-character-always (Windows 1252) encoding 
so the worst case was expanding one character to three ( "=3d" represented "=" 
, while "=FC" represented "LATIN SMALL LETTER U WITH DIAERESIS")
I didn't have to worry about the database character set as long as it was a 
superset of ascii.  I could store files of any size and with any length lines.  
I chose simplicity over fighting with the non-standard BLOB or CLOB apis.

If the files had been really binary I could have used 
https://metacpan.org/pod/MIME::Base64 .

This is the perl code I used for MIME Q-Encoding:

sub mimeq
{
my($in)  = @_;
my($out);

  $out =  $in;
  $out =~ s/(\W)/sprintf('=%02x', ord($1))/eg ;

  return $out;

}

sub unmimeq
{
my($in)  = @_;
my($out);

  $out =  $in;
  $out =~ s/=([\da-fA-F][\da-fA-F])/chr hex $1/eg ;

  return $out;

}

And a variation I sometimes use:

sub mimeq-permissive
{
my($in)  = @_;
my($out);

  $out =  $in;
  $out =~ 
s{([^]\-\s|#'a-zA-Z0-9,+.?()*,_.:;%@&/[])}{sprintf("=%02x",ord($1))}eg ;

  return $out;

}

I agree that using a filename or a URL/URI in the database and storing the 
actual file data someplace else, pointed to by the database, is another option, 
and one that is often used.

If your "binary data" isn't very big and you really want to, you can just use 
mime-64 and a VARCHAR and go happily about your business.

The choice is yours.

Perl - there is more than one way to do it.





The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: Tim Bunce - RE: DBD-DBI Insert Binary Files and using Perl modules in cPanel environment

[Fennell, Brian]

Correction:

MYSQL uses "BIGINT" (or another type with "INT" in its name) where Oracle uses 
NUMBER.

https://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm#BABHHAJC

The rest of what I said still stands (I think).

YMMV





The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.