我正在尝试从 Magento 订单中提取礼品卡代码。其他一些代码使用 Magento API 从 Magento 检索 XML 形式的订单信息，并将 XML 插入到 MS SQL Server 记录中。使用 T-SQL 我可以使用 XML 函数来解析从 Magento API 检索到的 XML 并获得我需要的几乎所有东西，但是实际礼品卡代码存储的唯一地方是 gift_cards 字段，它恰好是一个 php 序列化字符串。
例子:
a:1:{i:0;a:5:{s:1:"i";s:1:"1";s:1:"c";s:12:"00XCY8S3ZXCU";s:1:"a";d:119;s:2:"ba";d:119;s:10:"authorized";d:119;}}
a:3:{i:0;a:5:{s:1:"i";s:2:"10";s:1:"c";s:12:"045EMJJWRCF1";s:1:"a";d:100;s:2:"ba";d:100;s:10:"authorized";d:100;}i:1;a:5:{s:1:"i";s:2:"11";s:1:"c";s:12:"06DUQ7Z5GVT7";s:1:"a";d:101;s:2:"ba";d:101;s:10:"authorized";d:101;}i:2;a:5:{s:1:"i";s:2:"12";s:1:"c";s:12:"07A6MRYW511J";s:1:"a";d:102;s:2:"ba";d:102;s:10:"authorized";d:102;}}
礼品卡码为以“c”为键的数组中的值如:
00XCY8S3ZXCU 045EMJJWRCF1 06DUQ7Z5GVT7 07A6MRYW511J
我目前正尝试通过使用 T-SQL 函数解析值来解决这个问题，这就像试图用 Screwdriver 钉钉子一样。显然这已经被问过 here唯一的建议是在 T-SQL 中从头开始构建一个解析器，但是使用 PHP 反序列化它是更好的选择。
如果 Magento 不在他们的数据库中存储 PHP 序列化数据，然后在他们的 Web 服务中提供仍然序列化的数据，那就太好了，但这就是我必须处理的。我会考虑使用 C# 对其进行转换并将其作为一个单独的字段存储在数据库中，但是能够在 T-SQL 中解析数据会方便得多。如果我要使用 C# 来解析和反序列化 PHP 对象，我可能会将其作为 XML 存储回数据库中，因为这是交换数据的更好格式。
最佳答案
这是我自己想出来的。 post about parsing JSON 鼓励了我并决定为序列化的 php 对象弄清楚。不过，我采用了完全不同的方法。
更新的代码示例为 now published here on github .
序列化后的php字符串:
a:3:{
i:0;
a:5:{
s:1:"i";
s:2:"10";
s:1:"c";
s:12:"045EMJJWRCF1";
s:1:"a";
d:100;
s:2:"ba";
d:100;
s:10:"authorized";
d:100;
}
i:1;
a:5:{
s:1:"i";
s:2:"11";
s:1:"c";
s:12:"06DUQ7Z5GVT7";
s:1:"a";
d:101;
s:2:"ba";
d:101;
s:10:"authorized";
d:101;
}
i:2;
a:5:{
s:1:"i";
s:2:"12";
s:1:"c";
s:12:"07A6MRYW511J";
s:1:"a";
d:102;
s:2:"ba";
d:102;
s:10:"authorized";
d:102;
}
}
我查询得到的结果:
select *
from parsePhpSerializedString('a:3:{i:0;a:5:{s:1:"i";s:2:"10";s:1:"c";s:12:"045EMJJWRCF1";s:1:"a";d:100;s:2:"ba";d:100;s:10:"authorized";d:100;}i:1;a:5:{s:1:"i";s:2:"11";s:1:"c";s:12:"06DUQ7Z5GVT7";s:1:"a";d:101;s:2:"ba";d:101;s:10:"authorized";d:101;}i:2;a:5:{s:1:"i";s:2:"12";s:1:"c";s:12:"07A6MRYW511J";s:1:"a";d:102;s:2:"ba";d:102;s:10:"authorized";d:102;}}')
查询结果:
element_id parent_id var_name var_type var_length value_int value_string value_decimal
----------- ----------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------
1 0 NULL a 3 NULL NULL NULL
2 1 0 a 5 NULL NULL NULL
3 1 1 a 5 NULL NULL NULL
4 1 2 a 5 NULL NULL NULL
5 2 i s 2 NULL 10 NULL
6 2 c s 12 NULL 045EMJJWRCF1 NULL
7 2 a d NULL NULL NULL 100
8 2 ba d NULL NULL NULL 100
9 2 authorized d NULL NULL NULL 100
10 3 i s 2 NULL 11 NULL
11 3 c s 12 NULL 06DUQ7Z5GVT7 NULL
12 3 a d NULL NULL NULL 101
13 3 ba d NULL NULL NULL 101
14 3 authorized d NULL NULL NULL 101
15 4 i s 2 NULL 12 NULL
16 4 c s 12 NULL 07A6MRYW511J NULL
17 4 a d NULL NULL NULL 102
18 4 ba d NULL NULL NULL 102
19 4 authorized d NULL NULL NULL 102
如果我只想要礼品卡代码，我可以编写如下查询:
select value_string
from parsePhpSerializedString('a:3:{i:0;a:5:{s:1:"i";s:2:"10";s:1:"c";s:12:"045EMJJWRCF1";s:1:"a";d:100;s:2:"ba";d:100;s:10:"authorized";d:100;}i:1;a:5:{s:1:"i";s:2:"11";s:1:"c";s:12:"06DUQ7Z5GVT7";s:1:"a";d:101;s:2:"ba";d:101;s:10:"authorized";d:101;}i:2;a:5:{s:1:"i";s:2:"12";s:1:"c";s:12:"07A6MRYW511J";s:1:"a";d:102;s:2:"ba";d:102;s:10:"authorized";d:102;}}')
where parent_id != 0 and
var_name = 'c'
结果:
value_string
-------------
045EMJJWRCF1
06DUQ7Z5GVT7
07A6MRYW511J
下面是解析序列化 PHP 字符串的 T-SQL 函数:
IF OBJECT_ID (N'dbo.parsePhpSerializedString') IS NOT NULL
DROP FUNCTION dbo.parsePhpSerializedString
GO
CREATE FUNCTION dbo.parsePhpSerializedString( @phpSerialized VARCHAR(MAX))
RETURNS @results table
(
element_id int identity(1,1) not null, /* internal surrogate primary key gives the order of parsing and the list order */
parent_id int, /* if the element has a parent then it is in this column. */
var_name varchar(50), /* the name or key of the element in a key/value array list */
var_type varchar(50),
var_length int,
value_int int,
value_string varchar(max),
value_decimal numeric
)
AS
BEGIN
/*
Built by Matt Johnson (matt@evdat.com) 2012-08-14
*/
-- we use this table later for collecting auto generated
-- identity values when inserting records into @results
declare @insertedIds table (
element_id int
)
-- define variables
declare @element_start int
declare @var_type_end int
declare @var_type varchar(50)
declare @element_end int
declare @chunk varchar(max)
declare @var_length_start int
declare @var_length_end int
declare @var_length_string varchar(max)
declare @var_length int
declare @value_start int
declare @value_end int
declare @value_string varchar(max)
declare @value_int int
declare @value_decimal numeric
declare @array_level int
declare @value_string_position int
declare @next_open int
declare @next_close int
declare @parent_id int
declare @element_id int
declare @key_element_id int
declare @inserted_element_id int
declare @var_name varchar(50)
--initialize variables
set @parent_id = 0
--loop through the supplied @phpSerialized string until it's empty
while 1=1 begin
set @element_start = null
set @var_type_end = null
set @var_type = null
set @element_end = null
set @chunk = null
set @var_length_start = null
set @var_length_end = null
set @var_length_string = null
set @var_length = null
set @value_start = null
set @value_end = null
set @value_string = null
set @value_int = null
set @value_decimal = null
set @array_level = null
set @value_string_position = null
set @next_open = null
set @next_close = null
set @var_name = null
--confirm that there is an element to parse and define its starting point
--patindex will return a value of 1 if the pattern is found and this pattern
--will only match if the element starting point is the first character in the
--supplied string. If it is encapsulated in quotes or anything else it will not match
set @element_start = patindex('[asid]:%[;}]', @phpSerialized)
if @element_start <= 0 begin
--if the supplied string is now empty check the existing results table
--for any nested elements in any array elements
--reset the value of @element_id to be safe
set @element_id = null
--only retrieve the first element found containing sub elements to parse
select top 1
@phpSerialized = value_string,
@element_id = element_id
from @results
where var_type = 'a' and
value_string is not null
--set the parent_id to the array's element_id
set @parent_id = @element_id
--if there were no results found then that means there either
--were no arrays to parse, or all arrays have already been parsed
--so break the continuous loop because we are completely done now
if @element_id is null break
--set the @element_start again now that we
--have a new string to parse for elements
set @element_start = patindex('[asid]:%[;}]', @phpSerialized)
end
--find the end of the type of the element then extract the variable type from the string
set @var_type_end = patindex('%:%', @phpSerialized)
set @var_type = substring(@phpSerialized, @element_start, @var_type_end-@element_start)
--generate an error if a variable type is supplied that hasn't been coded for.
if @var_type not like '[asid]' begin
/*
print @var_type
RAISERROR (N'Error parsing php serialized string. Variable type found that has not been defined to parse for.', -- Message text.
16, -- Severity,
1 -- State
)
*/
--apparently errors can't be raised within a function so skip the element
break
end
--array elements contain sub elements so we use different methods for parsing
--sub elements than we do for parsing individual elements.
if @var_type != 'a' begin
--element has no sub elements
--determine the end of this individual element and then extract
--only this individual element from the string
set @element_end = patindex('%;%', @phpSerialized)+1
set @chunk = substring(@phpSerialized, @element_start, @element_end-@element_start)
--strings are serialized differently than numeric elements
if @var_type = 's' begin
--element has var length
--find the starting and ending positions for the var_length and then extract the length
set @var_length_start = @var_type_end+1
set @var_length_end = patindex('%:%', substring(@chunk, @var_length_start, len(@chunk))) + @var_length_start - 1
set @var_length_string = substring(@chunk, @var_length_start, @var_length_end-@var_length_start)
if @var_length_string not like '[^0-9]' begin
--its nice to verify this is actually a number before casting it as such
set @var_length = cast(@var_length_string as int)
end
--find the starting and ending positions for the value and then extract the value
set @value_start = @var_length_end+1
set @value_end = patindex('%;%', @chunk)
--a string value is quoted so remove quotes in start and end of substring for value
--we set the substring starting position +1 just past the start of the quote and then
--set the length of the extracted string -2 to account for both the starting quote and
--ending quote to be removed from the extracted string.
set @value_string = substring(@chunk, @value_start+1, @value_end-@value_start-2)
end else begin
--element does not have a var length
--find the starting and ending positions for the value and then extract the value as a string
set @value_start = @var_type_end+1
set @value_end = patindex('%;%', @chunk)
set @value_string = substring(@chunk, @value_start, @value_end-@value_start)
--determine what value type the string should be converted to
if @var_type = 'i' begin
if @value_string not like '[^0-9.]' begin
set @value_int = cast(@value_string as int)
--clear the value_string because the element's value has been converted to its appropriate type
set @value_string = null
end
end else if @var_type = 'd' begin
if @value_string not like '[^0-9.]' begin
set @value_decimal = cast(@value_string as numeric)
--clear the value_string because the element's value has been converted to its appropriate type
set @value_string = null
end
end
end
end else begin
--element is array and has sub elements
--we are going to chop up the string to try and determine its end so we'll
--first set the string to a variable we can destroy in this process
set @chunk = @phpSerialized
--find the starting and ending positions for the var_length and then extract the length
--arrays use this to state how may elements this array contains
set @var_length_start = @var_type_end+1
set @var_length_end = patindex('%:%', substring(@chunk, @var_length_start, len(@chunk))) + @var_length_start - 1
set @var_length_string = substring(@chunk, @var_length_start, @var_length_end-@var_length_start)
if @var_length_string not like '[^0-9]' begin
set @var_length = cast(@var_length_string as int)
end
--find the value starting position
--later we will find the true end of the value
set @value_start = @var_length_end+1
-- to determine the ending position we have to dig through the sub elements and track the
-- nested level to identify the ending brace for this level
set @array_level = 0
--we start the string position at 1 for the begining of the serialized string
set @value_string_position = 1
-- loop through the value chopping up the chunk while trying to find the ending brace for this array
while 1=1 begin
--find the next open and close braces in the chunk
set @next_open = patindex('%{%', @chunk)
set @next_close = patindex('%}%', @chunk)
--check to see which brace is the next in the chunk
if @next_open > 0 and @next_open < @next_close begin
--found an opening brace
--since this is an opening brace we need to increment the level and strip off
--everything from the chunk before the brace so that we can search for additional braces
--we also note the position in the string for use in finding the end of the value later
--we track the previous position and add to it because we keep chopping off the beginning of
--the chunk as we parse through the string, and later we will need to reference the position
--relative to the entire serialized string.
set @value_string_position = @value_string_position + patindex('%{%', @chunk)-1
set @chunk = substring(@chunk, patindex('%{%', @chunk)+1, len(@chunk))
set @array_level = @array_level + 1
end else if @next_close > 0 begin
--found a closing brace
--print 'found close at level: ' + cast(@array_level as varchar(10)) + '(' + cast(patindex('%}%', @chunk) as varchar(10)) + ')'
--since this is a closing brace we need to decrement the level and strip off
--everything from the chunk before the brace so that we can search for additional braces
--we also note the position in the string for use in finding the end of the value later
--we track the previous position and add to it because we keep chopping off the beginning of
--the chunk as we parse through the string, and later we will need to reference the position
--relative to the entire serialized string.
set @value_string_position = @value_string_position + patindex('%}%', @chunk)+1
set @chunk = substring(@chunk, patindex('%}%', @chunk)+1, len(@chunk))
set @array_level = @array_level - 1
end else break
--once we get back to level 0 we know we've found the end of this array element
--so break the continuous loop now that we have the ending position
if @array_level <= 0 break
end
--set the ending position of the element and the value since the value is the last part of the element
set @element_end = @value_string_position
set @value_end = @element_end
--an array value is surrounded by braces so remove the braces in start and end of the substring value
--we set the substring starting position +1 just past the start of the opening brace and then
--set the length of the extracted string -2 to account for both the opening brace and
--closing brace to be removed from the extracted string.
set @value_string = substring(@phpSerialized, @value_start+1, @value_end-@value_start-2)
set @chunk = substring(@phpSerialized, @element_start, @element_end-@element_start)
-- if the array is empty just set it to null so that
-- we don't try and parse the contents of the array value later.
if @value_string = '' set @value_string = null
end
--we populate the results table differently depending on the element that is being parsed.
--Any element contained in an array has a key element and a value element. Though we parse
--all key elements in an array all we do with them in the results table is set their value
--as the var_name (key) for the element, and store the key_element_id for setting the value
--in the next pass since key/value pairs are listed sequentially in a serialized string.
if @parent_id > 0 and @key_element_id > 0 begin
--parent_id > 0 indicates this is a sub element inside an array
--and the array contents currently being parsed contain key elements and value
--elements sequentially in the serialized string. Because key_element_id is not 0
--we know this must be the value part of the element contained in the array as the
--key was just previously defined.
--update the existing keyed element with it's type, length and value
update @results
set var_type = @var_type,
var_length = @var_length,
value_string = @value_string,
value_int = @value_int,
value_decimal = @value_decimal
where element_id = @key_element_id
set @key_element_id = null
end else if @parent_id > 0 begin
--this element is a part of the array which contains key/value paris and since
--the @key_element_id is 0 or not defined we can asume this is the key of the pair
--determine what the key element type is and cast it as a string to the var_name
if @var_type = 'i' set @var_name = cast(@value_int as varchar(50))
if @var_type = 'd' set @var_name = cast(@value_decimal as varchar(50))
if @var_type = 's' set @var_name = @value_string
--here we are assuming that all keys should be of type s, d, or i... so we if get anything
--other than that throw an error since this code is not setup to handle that.
if @var_type not like '[sid]' begin
/*
print @var_type
RAISERROR (N'Error parsing php serialized string. Variable type found that should be akey but is not of type s, d, or i.', -- Message text.
16, -- Severity,
1 -- State
)
*/
--apparently errors can't be raised within a function so skip the element
break
end
--insert a new record into the results table defining the parent_id and var_name
insert @results
(
parent_id,
var_name
)
output inserted.element_id into @insertedIds
values (
@parent_id,
@var_name
)
--since we stored the identity value in the output set that value to @inserted_element_id
select top 1 @inserted_element_id = element_id from @insertedIds
delete from @insertedIds
--set the key_element_id so that the next pass catches the value and assigns it to this result record
set @key_element_id = @inserted_element_id
end else begin
--this will be executed for parsed strings that are not part of an array
--in which case the elements do not contain value_name keys
--insert the entire element details into the results table
insert into @results
(
parent_id,
var_type,
var_length,
value_string,
value_int,
value_decimal
)
output inserted.element_id into @insertedIds
values (
@parent_id,
@var_type,
@var_length,
@value_string,
@value_int,
@value_decimal
)
--here we capture the identiy value for the inserted record
select top 1 @inserted_element_id = element_id from @insertedIds
delete from @insertedIds
end
-- if the current php serilized string was an array then it would have
-- been parsed and elements added to the results, so we should remove
-- the value_string from the array element in the results table so that
-- it doesn't get parsed again in the loop.
if @element_id is not null begin
--the only strings that get parsed where the @element_id has
--a value is from an array element
update @results
set value_string = null
where element_id = @element_id
end
--since we have parsed this element from the serialized string chop off this element
--from the string and run the rest of it through the loop again to ensure all
--elements have been parsed from the supplied serialized string.
set @phpSerialized = substring(@phpSerialized, @element_end, len(@phpSerialized))
end
return
end
虽然它没有说明所有序列化类型等，但足以满足我的目的，其中包含的评论应该有助于指导任何需要对其进行调整以供自己使用或扩展其功能的人。
关于php - 在 T-SQL 中反序列化 PHP 序列化数据，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/11955723/
我主要使用Ruby来执行此操作，但到目前为止我的攻击计划如下:使用gemsrdf、rdf-rdfa和rdf-microdata或mida来解析给定任何URI的数据。我认为最好映射到像schema.org这样的统一模式，例如使用这个yaml文件，它试图描述数据词汇表和opengraph到schema.org之间的转换:#SchemaXtoschema.orgconversion#data-vocabularyDV:name:namestreet-address:streetAddressregion:addressRegionlocality:addressLocalityphoto:i
有时我需要处理键/值数据。我不喜欢使用数组，因为它们在大小上没有限制(很容易不小心添加超过2个项目，而且您最终需要稍后验证大小)。此外，0和1的索引变成了魔数(MagicNumber)，并且在传达含义方面做得很差(“当我说0时，我的意思是head...”)。散列也不合适，因为可能会不小心添加额外的条目。我写了下面的类来解决这个问题:classPairattr_accessor:head,:taildefinitialize(h,t)@head,@tail=h,tendend它工作得很好并且解决了问题，但我很想知道:Ruby标准库是否已经带有这样一个类？ 最佳
给定一个复杂的对象层次结构，幸运的是它不包含循环引用，我如何实现支持各种格式的序列化？我不是来讨论实际实现的。相反，我正在寻找可能会派上用场的设计模式提示。更准确地说:我正在使用Ruby，我想解析XML和JSON数据以构建复杂的对象层次结构。此外，应该可以将该层次结构序列化为JSON、XML和可能的HTML。我可以为此使用Builder模式吗？在任何提到的情况下，我都有某种结构化数据-无论是在内存中还是文本中-我想用它来构建其他东西。我认为将序列化逻辑与实际业务逻辑分开会很好，这样我以后就可以轻松支持多种XML格式。 最佳答案 我最
我正在尝试使用Curbgem执行以下POST以解析云curl-XPOST\-H"X-Parse-Application-Id:PARSE_APP_ID"\-H"X-Parse-REST-API-Key:PARSE_API_KEY"\-H"Content-Type:image/jpeg"\--data-binary'@myPicture.jpg'\https://api.parse.com/1/files/pic.jpg用这个:curl=Curl::Easy.new("https://api.parse.com/1/files/lion.jpg")curl.multipart_form_
无论您是想搭建桌面端、WEB端或者移动端APP应用，HOOPSPlatform组件都可以为您提供弹性的3D集成架构，同时，由工业领域3D技术专家组成的HOOPS技术团队也能为您提供技术支持服务。如果您的客户期望有一种在多个平台（桌面/WEB/APP，而且某些客户端是“瘦”客户端）快速、方便地将数据接入到3D应用系统的解决方案，并且当访问数据时，在各个平台上的性能和用户体验保持一致，HOOPSPlatform将帮助您完成。利用HOOPSPlatform，您可以开发在任何环境下的3D基础应用架构。HOOPSPlatform可以帮您打造3D创新型产品，HOOPSSDK包含的技术有：快速且准确的CAD
本教程将在Unity3D中混合Optitrack与数据手套的数据流，在人体运动的基础上，添加双手手指部分的运动。双手手背的角度仍由Optitrack提供，数据手套提供双手手指的角度。 01 客户端软件分别安装MotiveBody与MotionVenus并校准人体与数据手套。MotiveBodyMotionVenus数据手套使用、校准流程参照：https://gitee.com/foheart_1/foheart-h1-data-summary.git02 数据转发打开MotiveBody软件的Streaming，开始向Unity3D广播数据；MotionVenus中设置->选项选择Unit
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
我正在尝试在Rails上安装ruby，到目前为止一切都已安装，但是当我尝试使用rakedb:create创建数据库时，我收到一个奇怪的错误:dyld:lazysymbolbindingfailed:Symbolnotfound:_mysql_get_client_infoReferencedfrom:/Library/Ruby/Gems/1.8/gems/mysql2-0.3.11/lib/mysql2/mysql2.bundleExpectedin:flatnamespacedyld:Symbolnotfound:_mysql_get_client_infoReferencedf
文章目录1.开发板选择*用到的资源2.串口通信（个人理解）3.代码分析（注释比较详细）1.主函数2.串口1配置3.串口2配置以及中断函数4.注意问题5.源码链接1.开发板选择我用的是STM32F103RCT6的板子，不过代码大概在F103系列的板子上都可以运行，我试过在野火103的霸道板上也可以，主要看一下串口对应的引脚一不一样就行了，不一样的就更改一下。*用到的资源keil5软件这里用到了两个串口资源，采集数据一个，串口通信一个，板子对应引脚如下：串口1，TX：PA9，RX：PA10串口2，TX：PA2，RX：PA32.串口通信（个人理解）我就从串口采集传感器数据这个过程说一下我自己的理解，
SPI接收数据左移一位问题目录SPI接收数据左移一位问题一、问题描述二、问题分析三、探究原理四、经验总结最近在工作在学习调试SPI的过程中遇到一个问题——接收数据整体向左移了一位（1bit）。SPI数据收发是数据交换，因此接收数据时从第二个字节开始才是有效数据，也就是数据整体向右移一个字节（1byte）。请教前辈之后也没有得到解决，通过在网上查阅前人经验终于解决问题，所以写一个避坑经验总结。实际背景：MCU与一款芯片使用spi通信，MCU作为主机，芯片作为从机。这款芯片采用的是它规定的六线SPI，多了两根线：RDY和INT，这样从机就可以主动请求主机给主机发送数据了。一、问题描述根据从机芯片手