如何使用纯文本脚本插入（从文件数据的原始字节） [英] How to insert (raw bytes from file data) using a plain text script

问题描述

数据库：Postgres 9.1

Database: Postgres 9.1

我有一个名为 logos 的表格定义如下：

I have a table called logos defined like this:

create type image_type as enum ('png');
create table logos (
  id UUID primary key,
  bytes bytea not null,
  type image_type not null,
  created timestamp with time zone default current_timestamp not null
);
create index logo_id_idx on logos(id);

我希望能以两种方式将记录插入此表格。

第一个（也是最常见的）方式行将被插入表格中，用户将提供一个PNG图片文件一个html文件上传表单。在服务器上处理请求的代码将接收一个包含PNG图像文件中的数据的字节数组，并使用非常类似于此处。有很多例子，如何在互联网上bytea类型的postgresql字段中插入字节数组。这是一个容易的练习。插入代码的示例如下：

The first (and most common) way rows will be inserted in the table will be that a user will provide a PNG image file via an html file upload form. The code processing the request on the server will receive a byte array containing the data in the PNG image file and insert a record in the table using something very similar to what is explained here. There are plenty of example of how to insert byte arrays into a postgresql field of type bytea on the internet. This is an easy exercise. An example of the insert code would look like this:

insert into logos (id, bytes, type, created) values (?, ?, ?, now()) 

字节将被设置为类似： p>

And the bytes would be set with something like:

...
byte[] bytes = ... // read PNG file into a byte array.
...
ps.setBytes(2, bytes);
...

第二种方式在表中将来自纯文本文件脚本。需要的原因仅仅是将测试数据填充到表中以进行自动化测试，或者以远程开发环境的几个记录初始化数据库。

The second way rows will be inserted in the table will be from a plain text file script. The reason this is needed is only to populate test data into the table for automated tests, or to initialize the database with a few records for a remote development environment.

无论如何在表中输入数据，应用程序显然需要能够从表中选择bytea数据并将其转换回PNG图像。

Regardless of how the data is entered in the table, the application will obviously need to be able to select the bytea data from the table and convert it back into a PNG image.

问题

如何正确编码字节数组，以便能够插入数据在脚本中，只有文件中包含的原始字节存储在数据库中？

How does one properly encode a byte array, to be able to insert the data from within a script, in such a way that only the original bytes contained in the file are stored in the database?

我可以写代码读取文件，语句来填充脚本。但是我不知道如何编码纯文本脚本的字节数组，以便当从psql运行脚本时，图像数据将是相同的，如果使用 setBytes jdbc code。

I can write code to read the file and spit out insert statements to populate the script. But I don't know how to encode the byte array for the plain text script such that when running the script from psql the image data will be the same as if the file was inserted using the setBytes jdbc code.

我想运行这样的脚本：

psql -U username -d dataBase -a -f test_data.sql

推荐答案

在IMO文件中表示 bytea 数据的最简单方法是使用十六进制格式：

The easiest way, IMO, to represent bytea data in an SQL file is to use the hex format:

8.4.1。 byte hex格式

hex格式将二进制数据编码为每个字节2个十六进制数字，最高有效半字节。整个字符串之前是序列 \x （以区别于转义格式）。在某些上下文中，初始反斜杠可能需要通过加倍转义，在反斜杠必须在转义格式中加倍的情况下;详细信息显示如下。十六进制数字可以是大写或小写，并且在数字对之间允许使用空格（但不能在数字对中，也不能在起始 \x 序列中）。十六进制格式与广泛的外部应用程序和协议兼容，并且转换速度比转义格式快，因此它的使用是首选。

The "hex" format encodes binary data as 2 hexadecimal digits per byte, most significant nibble first. The entire string is preceded by the sequence \x (to distinguish it from the escape format). In some contexts, the initial backslash may need to be escaped by doubling it, in the same cases in which backslashes have to be doubled in escape format; details appear below. The hexadecimal digits can be either upper or lower case, and whitespace is permitted between digit pairs (but not within a digit pair nor in the starting \x sequence). The hex format is compatible with a wide range of external applications and protocols, and it tends to be faster to convert than the escape format, so its use is preferred.

示例：

SELECT E'\\xDEADBEEF';

将字节数组转换为十六进制在任何语言一个健全的人（如自己）将用来编写SQL文件生成器。

Converting an array of bytes to hex should be trivial in any language that a sane person (such a yourself) would use to write the SQL file generator.

这篇关于如何使用纯文本脚本插入（从文件数据的原始字节）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！