File: PgsqlSchemaLoader.StoredProcedures.cs

package info (click to toggle)
mono 4.6.2.7+dfsg-1
  • links: PTS, VCS
  • area: main
  • in suites: stretch
  • size: 778,148 kB
  • ctags: 914,052
  • sloc: cs: 5,779,509; xml: 2,773,713; ansic: 432,645; sh: 14,749; makefile: 12,361; perl: 2,488; python: 1,434; cpp: 849; asm: 531; sql: 95; sed: 16; php: 1
file content (137 lines) | stat: -rw-r--r-- 5,232 bytes parent folder | download | duplicates (2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
#region MIT license
// 
// MIT license
//
// Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne
// 
// Permission is hereby granted, free of charge, to any person obtaining a copy
// of this software and associated documentation files (the "Software"), to deal
// in the Software without restriction, including without limitation the rights
// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
// copies of the Software, and to permit persons to whom the Software is
// furnished to do so, subject to the following conditions:
// 
// The above copyright notice and this permission notice shall be included in
// all copies or substantial portions of the Software.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
// THE SOFTWARE.
// 
#endregion
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DbLinq.Util;

namespace DbLinq.PostgreSql
{
    partial class PgsqlSchemaLoader
    {
        /// <summary>
        /// represents one row from pg_proc table
        /// </summary>
        protected class DataStoredProcedure
        {
            public long proowner;
            public string proname;
            public bool proretset;
            public long prorettype;
            public string formatted_prorettype;

            /// <summary>
            /// species types of in-args, eg. '23 1043'
            /// </summary>
            public string proargtypes;

            /// <summary>
            /// species types of in,out args, eg. '{23,1043,1043}'
            /// </summary>
            public string proallargtypes;

            /// <summary>
            /// param names, eg {i1,i2,o2}
            /// </summary>
            public string proargnames;

            /// <summary>
            /// specifies in/out modes - eg. '{i,i,o}'
            /// </summary>
            public string proargmodes;

            public override string ToString() { return "Pg_Proc " + proname; }
        }

        protected virtual DataStoredProcedure ReadProcedure(IDataReader rdr)
        {
            DataStoredProcedure procedure = new DataStoredProcedure();
            int field = 0;
            procedure.proowner = rdr.GetAsNumeric<long>(field++);
            procedure.proname = rdr.GetAsString(field++);
            procedure.proretset = rdr.GetAsBool(field++);
            procedure.prorettype = rdr.GetAsNumeric<long>(field++);
            procedure.formatted_prorettype = rdr.GetAsString(field++);
            procedure.proargtypes = rdr.GetAsString(field++);
            procedure.proallargtypes = rdr.GetAsString( field++);
            procedure.proargnames = rdr.GetAsString(field++);
            procedure.proargmodes = rdr.GetAsString(field++);
            return procedure;
        }

        protected virtual List<DataStoredProcedure> ReadProcedures(IDbConnection conn, string db)
        {
            string sql = @"
SELECT pr.proowner, pr.proname, pr.proretset, pr.prorettype, pg_catalog.format_type(pr.prorettype, NULL) 
  ,pr.proargtypes, pr.proallargtypes, pr.proargnames, pr.proargmodes
FROM pg_proc pr, pg_type tp 
WHERE tp.oid = pr.prorettype AND pr.proisagg = FALSE 
AND tp.typname <> 'trigger' 
AND pr.pronamespace IN ( SELECT oid FROM pg_namespace 
WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema' ); 

";

            return DataCommand.Find<DataStoredProcedure>(conn, sql, ":db", db, ReadProcedure);
        }

        protected virtual int GetTypeNames(IDbConnection conn, string db, Dictionary<long, string> oid_to_name_map)
        {
            string sql = @"
SELECT pg_catalog.format_type(:typeOid, NULL)
";
            int numDone = 0;

            //clone to prevent 'collection was modified' exception
            Dictionary<long, string> oid_to_name_map2 = new Dictionary<long, string>(oid_to_name_map);

            foreach (var kv in oid_to_name_map2)
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    if (kv.Value != null)
                        continue; //value already known

                    long typeOid = kv.Key;
                    IDbDataParameter parameter = cmd.CreateParameter();
                    parameter.ParameterName = ":typeOid";
                    parameter.Value = typeOid;
                    cmd.Parameters.Add(parameter);
                    //cmd.CommandText = sql.Replace(":typeOid", typeOid.ToString());
                    numDone++;
                    object typeName1 = cmd.ExecuteScalar();
                    string typeName2 = typeName1 as string;
                    oid_to_name_map[typeOid] = typeName2; //eg. dic[23] = "integer"
                }
            }
            return numDone;
        }

    }
}