SCM

[#1011019] Composite type support

View Trackers | Patches | Download .csv | Monitor

Date:
2011-04-24 01:08
Priority:
3
State:
Open
Submitted by:
Yaroslav Potapov (jaroslav)
Assigned to:
Nobody (None)
Category:
Group:
Resolution:
None
 
Summary:
Composite type support

Detailed description
Composite type support

Example:

CREATE TYPE ct AS
(
i smallint,
t character varying,
b bytea,
p inet,
d time with time zone
);

CREATE TABLE tt
(
id integer NOT NULL,
"ctype" ct,
arrctype ct[],
CONSTRAINT pki PRIMARY KEY (id)
)
WITH ( OIDS=FALSE);

INSERT INTO tt (id, ctype, arrctype, ba, iarr, arr)
VALUES
(1, '(11,text,bbb,127.0.0.1,00:19:18.748265+03)', '{"(11,text,bbb,,00:19:18.748265+03)","(11,text,bbb,127.0.0.1,00:19:18.748265+03)"}'),
(2, '(11,text,bbb,127.0.0.1,00:19:18.748265+03)', '{"(11,text,bbb,,00:19:18.748265+03)","(11,text,bbb,127.0.0.1,00:19:18.748265+03)"}');


using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using NpgsqlTypes;
using System.Net;
using Npgsql;

namespace TestProject1
{
[TestClass]
public class CompositeTest
{
/// <summary>
/// Composite test.
/// Metood first writes the data into a database, then reads and compares them.
/// Shows compatibility with arrays.
/// </summary>
[TestMethod()]
public void CompositeTest()
{
// Init composite description
CompositeDescription description = new CompositeDescription("ct",
new ColumnDescription("i", NpgsqlDbType.Smallint),
new ColumnDescription("t", NpgsqlDbType.Varchar),
new ColumnDescription("b", NpgsqlDbType.Bytea),
new ColumnDescription("p", NpgsqlDbType.Inet),
new ColumnDescription("d", NpgsqlDbType.TimeTZ));

// First way to get composite
Composite composite = new Composite(description);
// set values
composite[0] = 11;
composite[1] = "k02\\2jj\",'jj";
composite["b"] = new byte[] { 0, 11, 22, 33, 44, 55, 66, 77 }; // it the same that composite[2] =...
composite[3] = IPAddress.Parse("88.45.88.44");
composite[4] = DateTime.Now;

// Gets array
Composite[] cmpArr = new Composite[2];
cmpArr[0] = composite;

// Second way to get composite
cmpArr[1] = new Composite(description, 11, "some text", new byte[1], null, DateTime.Now);

NpgsqlCommand cmd = new NpgsqlCommand("UPDATE tt SET ctype = @cty, arrctype = @ctyarr WHERE id = 2");

cmd.Parameters.AddWithValue("@cty", composite);
cmd.Parameters.AddWithValue("@ctyarr", cmpArr);

cmd.Connection = new NpgsqlConnection();
cmd.Connection.Open();

int i = cmd.ExecuteNonQuery();
if (i < 1) Assert.Fail("don't update");

cmd.CommandText = "SELECT ctype, arrctype FROM tt WHERE id = 2";
cmd.CommandType = System.Data.CommandType.Text;
using (NpgsqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
//gets composite
Composite c = rdr.GetComposite(0);

//gets composite[]
object obj = rdr.GetValue(1);
//gets first element
Composite cmp1 = (Composite)((object[])obj)[0];

// send and recived data must be equaled
Assert.IsTrue(
(string)composite["t"] == (string)c["t"] &&
(string)composite["t"] == (string)cmp1["t"]);
}
}
}
}
}

Followup

Message
Date: 2011-05-09 10:42
Sender: Yaroslav Potapov

Ups, version 0.2 does not compile.
It fixed in v0.3.
Date: 2011-05-08 23:30
Sender: Yaroslav Potapov

Composite type support patch v0.2 for Npgsql 2.0.11.91 added.

Some serious bugs was fixed. It is still alpha version ;).

I am waiting for your comments and suggestions.
Date: 2011-04-24 20:46
Sender: Yaroslav Potapov

Zip file attached

Changes:

Field Old Value Date By
File Added613: CompositePatch_v0.3_ForNpgsql2.0.11.91.zip2011-05-09 10:42jaroslav
File Added602: CompositePatch_v0.2_ForNpgsql2.0.11.91.zip2011-05-08 23:30jaroslav
File Added596: CompositePatchForNpgsql2.0.11.91.zip2011-04-24 20:46jaroslav
File Added595: CompositePatchForNpgsql2.0.11.91.rar2011-04-24 01:08jaroslav
Powered By FusionForge