?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
* Makefile.am:
[monodevelop.git] / extras / MonoDevelop.Database / MonoDevelop.Database.Sql.Npgsql / NpgsqlSchemaProvider.cs
blob9b9f7fe41fe4b0ddfa05c7e060cd18153066b1d0
1 //
2 // Authors:
3 // Christian Hergert <chris@mosaix.net>
4 // Ben Motmans <ben.motmans@gmail.com>
5 //
6 // Copyright (C) 2005 Mosaix Communications, Inc.
7 // Copyright (c) 2007 Ben Motmans
8 //
9 // Permission is hereby granted, free of charge, to any person obtaining a copy
10 // of this software and associated documentation files (the "Software"), to deal
11 // in the Software without restriction, including without limitation the rights
12 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
13 // copies of the Software, and to permit persons to whom the Software is
14 // furnished to do so, subject to the following conditions:
15 //
16 // The above copyright notice and this permission notice shall be included in
17 // all copies or substantial portions of the Software.
18 //
19 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
20 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
21 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
22 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
23 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
24 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
25 // THE SOFTWARE.
26 //
27
28 using System;
29 using System.Data;
30 using System.Text;
31 using System.Text.RegularExpressions;
32 using System.Collections.Generic;
33 using Npgsql;
34 using MonoDevelop.Core;
35 namespace MonoDevelop.Database.Sql.Npgsql
36 {
37 public class NpgsqlSchemaProvider : AbstractEditSchemaProvider
38 {
39 public NpgsqlSchemaProvider (IConnectionPool connectionPool)
40 : base (connectionPool)
41 {
42 AddSupportedSchemaActions (SchemaType.Database, SchemaActions.All);
43 AddSupportedSchemaActions (SchemaType.Table, SchemaActions.Create | SchemaActions.Drop | SchemaActions.Rename | SchemaActions.Schema);
44 AddSupportedSchemaActions (SchemaType.View, SchemaActions.All);
45 AddSupportedSchemaActions (SchemaType.Procedure, SchemaActions.All);
46 AddSupportedSchemaActions (SchemaType.TableColumn, SchemaActions.All);
47 AddSupportedSchemaActions (SchemaType.ProcedureParameter, SchemaActions.Schema);
48 AddSupportedSchemaActions (SchemaType.Trigger, SchemaActions.All);
49 AddSupportedSchemaActions (SchemaType.PrimaryKeyConstraint, SchemaActions.Create | SchemaActions.Drop | SchemaActions.Rename | SchemaActions.Schema);
50 AddSupportedSchemaActions (SchemaType.ForeignKeyConstraint, SchemaActions.Create | SchemaActions.Drop | SchemaActions.Rename | SchemaActions.Schema);
51 AddSupportedSchemaActions (SchemaType.CheckConstraint, SchemaActions.Create | SchemaActions.Drop | SchemaActions.Rename | SchemaActions.Schema);
52 AddSupportedSchemaActions (SchemaType.UniqueConstraint, SchemaActions.Create | SchemaActions.Drop | SchemaActions.Rename | SchemaActions.Schema);
53 AddSupportedSchemaActions (SchemaType.Constraint, SchemaActions.Create | SchemaActions.Drop | SchemaActions.Rename | SchemaActions.Schema);
54 AddSupportedSchemaActions (SchemaType.User, SchemaActions.Schema);
55 }
56
57 public override TableSchemaCollection GetTables ()
58 {
59 TableSchemaCollection tables = new TableSchemaCollection ();
60
61 IPooledDbConnection conn = connectionPool.Request ();
62 IDbCommand command = conn.CreateCommand (
63 "SELECT DISTINCT c.relname, n.nspname, u.usename "
64 + "FROM pg_class c, pg_namespace n, pg_user u "
65 + "WHERE c.relnamespace = n.oid "
66 + "AND c.relowner = u.usesysid "
67 + "AND c.relkind='r' AND NOT EXISTS "
68 + " (SELECT 1 FROM pg_rewrite r "
69 + " WHERE r.ev_class = c.oid AND r.ev_type = '1') "
70 + "ORDER BY relname;"
71 );
72
73 try {
74 using (command) {
75 using (IDataReader r = command.ExecuteReader()) {
76 while (r.Read ()) {
77 TableSchema table = new TableSchema (this);
78
79 table.Name = r.GetString (0);
80 table.IsSystemTable = table.Name.StartsWith ("pg_") || table.Name.StartsWith ("sql_");
81 table.SchemaName = r.GetString (1);
82 table.OwnerName = r.GetString (2);
83
84 // StringBuilder sb = new StringBuilder();
85 // sb.AppendFormat ("-- Table: {0}\n", table.Name);
86 // sb.AppendFormat ("-- DROP TABLE {0};\n\n", table.Name);
87 // sb.AppendFormat ("CREATE TABLE {0} (\n", table.Name);
88 //
89 // ColumnSchema[] columns = table.Columns;
90 // string[] parts = new string[columns.Length];
91 // for (int i = 0; i < parts.Length; i++) {
92 // parts[i] = "\t" + columns[i].Definition;
93 // }
94 // sb.Append (String.Join (",\n", parts));
95 //
96 // ConstraintSchema[] cons = table.Constraints;
97 // parts = new string[cons.Length];
98 // if (cons.Length > 0)
99 // sb.Append (",\n");
100 // for (int i = 0; i < parts.Length; i++) {
101 // parts[i] = "\t" + cons[i].Definition;
102 // }
103 // sb.Append (String.Join (",\n", parts));
104 //
105 // sb.Append ("\n);\n");
106 // sb.AppendFormat ("COMMENT ON TABLE {0} IS '{1}';", table.Name, table.Comment);
107 // table.Definition = sb.ToString();
108
109 tables.Add (table);
110 }
111 r.Close ();
112 }
113 }
114 } catch (Exception e) {
115 QueryService.RaiseException (e);
116 }
117 conn.Release ();
118
119 return tables;
120 }
121
122 public override ColumnSchemaCollection GetTableColumns (TableSchema table)
123 {
124 ColumnSchemaCollection columns = new ColumnSchemaCollection ();
125
126 IPooledDbConnection conn = connectionPool.Request ();
127 IDbCommand command = conn.CreateCommand (
128 "SELECT a.attname, a.attnotnull, a.attlen, "
129 + "typ.typname, adef.adsrc "
130 + "FROM "
131 + " pg_catalog.pg_attribute a LEFT JOIN "
132 + " pg_catalog.pg_attrdef adef "
133 + " ON a.attrelid=adef.adrelid "
134 + " AND a.attnum=adef.adnum "
135 + " LEFT JOIN pg_catalog.pg_type t ON a.atttypid=t.oid, "
136 + " pg_catalog.pg_type typ "
137 + "WHERE "
138 + " a.attrelid = (SELECT oid FROM pg_catalog.pg_class "
139 + " WHERE relname='" + table.Name + "') "
140 + "AND a.attnum > 0 AND NOT a.attisdropped "
141 + "AND a.atttypid = typ.oid "
142 + "ORDER BY a.attnum;"
143 );
144 try {
145 using (command) {
146 using (IDataReader r = command.ExecuteReader()) {
147 while (r.Read ()) {
148 ColumnSchema column = new ColumnSchema (this, table);
149
150 column.Name = r.GetString (0);
151 column.DataTypeName = r.GetString (3);
152 column.IsNullable = r.GetBoolean (1);
153 column.DefaultValue = r.IsDBNull (4) ? null : r.GetString (4);
154 column.DataType.LengthRange.Default = r.GetInt32 (2);
155
156 // StringBuilder sb = new StringBuilder();
157 // sb.AppendFormat("{0} {1}{2}",
158 // column.Name,
159 // column.DataTypeName,
160 // (column.DataType.LengthRange.Default > 0) ? ("(" + column.DataType.LengthRange.Default + ")") : "");
161 // sb.AppendFormat(" {0}", column.IsNullable ? "NULL" : "NOT NULL");
162 // if (column.DefaultValue.Length > 0)
163 // sb.AppendFormat(" DEFAULT {0}", column.DefaultValue);
164 // column.Definition = sb.ToString();
165
166 columns.Add (column);
167 }
168 r.Close ();
169 };
170 }
171 } catch (Exception e) {
172 QueryService.RaiseException (e);
173 }
174 conn.Release ();
175
176 return columns;
177 }
178
179 public override ViewSchemaCollection GetViews ()
180 {
181 ViewSchemaCollection views = new ViewSchemaCollection ();
182
183 IPooledDbConnection conn = connectionPool.Request ();
184 IDbCommand command = conn.CreateCommand (
185 "SELECT v.schemaname, v.viewname, v.viewowner, v.definition,"
186 + " (c.oid <= " + LastSystemOID + "), "
187 + "(SELECT description from pg_description pd, "
188 + " pg_class pc WHERE pc.oid=pd.objoid AND pc.relname="
189 + " v.viewname) "
190 + "FROM pg_views v, pg_class c "
191 + "WHERE v.viewname = c.relname "
192 + "ORDER BY viewname"
193 );
194
195 try {
196 using (command) {
197 using (IDataReader r = command.ExecuteReader()) {
198 while (r.Read ()) {
199 ViewSchema view = new ViewSchema (this);
200
201 view.Name = r.GetString (1);
202 view.OwnerName = r.GetString (2);
203 view.SchemaName = r.GetString (0);
204 view.IsSystemView = r.GetBoolean (4);
205 view.Comment = r.IsDBNull (5) ? null : r.GetString (5);
206
207 // StringBuilder sb = new StringBuilder();
208 // sb.AppendFormat ("-- View: {0}\n", view.Name);
209 // sb.AppendFormat ("-- DROP VIEW {0};\n\n", view.Name);
210 // sb.AppendFormat ("CREATE VIEW {0} AS (\n", view.Name);
211 // string core = r.GetString(3);
212 // sb.AppendFormat (" {0}\n);", core.Substring (0, core.Length-1));
213 // view.Definition = sb.ToString ();
214
215 views.Add (view);
216 }
217 r.Close ();
218 }
219 }
220 } catch (Exception e) {
221 QueryService.RaiseException (e);
222 }
223 conn.Release ();
224
225 return views;
226 }
227
228 public override ColumnSchemaCollection GetViewColumns (ViewSchema view)
229 {
230 ColumnSchemaCollection columns = new ColumnSchemaCollection ();
231
232 IPooledDbConnection conn = connectionPool.Request ();
233 IDbCommand command = conn.CreateCommand (
234 "SELECT attname, typname, attlen, attnotnull "
235 + "FROM "
236 + " pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_attrdef adef "
237 + " ON a.attrelid=adef.adrelid "
238 + " AND a.attnum=adef.adnum "
239 + " LEFT JOIN pg_catalog.pg_type t ON a.atttypid=t.oid "
240 + "WHERE "
241 + " a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='"
242 + view.Name + "') "
243 + " AND a.attnum > 0 AND NOT a.attisdropped "
244 + " ORDER BY a.attnum;"
245 );
246 try {
247 using (command) {
248 using (IDataReader r = command.ExecuteReader()) {
249 while (r.Read ()) {
250 ColumnSchema column = new ColumnSchema (this, view);
251
252 column.Name = r.GetString(0);
253 column.DataTypeName = r.GetString (1);
254 column.SchemaName = view.SchemaName;
255 column.IsNullable = r.GetBoolean (3);
256 column.DataType.LengthRange.Default = r.GetInt32 (2);
257
258 columns.Add (column);
259 }
260 r.Close ();
261 };
262 }
263 } catch (Exception e) {
264 QueryService.RaiseException (e);
265 }
266 conn.Release ();
267
268 return columns;
269 }
270
271 public override ProcedureSchemaCollection GetProcedures ()
272 {
273 ProcedureSchemaCollection procedures = new ProcedureSchemaCollection ();
274
275 IPooledDbConnection conn = connectionPool.Request ();
276 IDbCommand command = conn.CreateCommand (
277 "SELECT pc.proname, pc.oid::integer, pl.lanname, pc.prosrc "
278 + "FROM "
279 + " pg_proc pc, "
280 + " pg_user pu, "
281 + " pg_type pt, "
282 + " pg_language pl "
283 + "WHERE pc.proowner = pu.usesysid "
284 + "AND pc.prorettype = pt.oid "
285 + "AND pc.prolang = pl.oid "
286 + "UNION "
287 + "SELECT pc.proname, pt.oid::integer, pl.lanname, pc.prosrc "
288 + "FROM "
289 + " pg_proc pc, "
290 + " pg_user pu, "
291 + " pg_type pt, "
292 + " pg_language pl "
293 + "WHERE pc.proowner = pu.usesysid "
294 + "AND pc.prorettype = 0 "
295 + "AND pc.prolang = pl.oid;"
296 );
297 try {
298 using (command) {
299 using (IDataReader r = command.ExecuteReader()) {
300 while (r.Read ()) {
301 ProcedureSchema procedure = new ProcedureSchema (this);
302
303 procedure.Name = r.GetString (0);
304 procedure.Definition = r.GetString (3);
305 procedure.LanguageName = r.GetString (2);
306
307 if (!r.IsDBNull (1) && r.GetInt32 (1) <= LastSystemOID)
308 procedure.IsSystemProcedure = true;
309
310 procedures.Add (procedure);
311 }
312 r.Close ();
313 }
314 }
315 } catch (Exception e) {
316 QueryService.RaiseException (e);
317 }
318 conn.Release ();
319
320 return procedures;
321 }
322
323 public override ParameterSchemaCollection GetProcedureParameters (ProcedureSchema procedure)
324 {
325 ParameterSchemaCollection parameters = new ParameterSchemaCollection ();
326
327 // FIXME: Won't work properly with overload functions.
328 // Maybe check the number of columns in the parameters for
329 // proper match.
330 IPooledDbConnection conn = connectionPool.Request ();
331 IDbCommand command = conn.CreateCommand (String.Format (
332 "SELECT format_type (prorettype, NULL) "
333 + "FROM pg_proc pc, pg_language pl "
334 + "WHERE pc.prolang = pl.oid "
335 + "AND pc.proname = '{0}';", procedure.Name
336 ));
337 try {
338 using (command) {
339 using (IDataReader r = command.ExecuteReader()) {
340 while (r.Read ()) {
341 ParameterSchema param = new ParameterSchema (this);
342
343 param.DataTypeName = r.GetString (0);
344 param.Name = r.GetString (0);
345 parameters.Add (param);
346 }
347 r.Close ();
348 }
349 }
350 } catch (Exception e) {
351 QueryService.RaiseException (e);
352 }
353 conn.Release ();
354
355 return parameters;
356 }
357
358 public override ConstraintSchemaCollection GetTableConstraints (TableSchema table)
359 {
360 ConstraintSchemaCollection constraints = new ConstraintSchemaCollection ();
361
362 IPooledDbConnection conn = connectionPool.Request ();
363 IDbCommand command = conn.CreateCommand (String.Format (
364 "SELECT "
365 + "pc.conname, "
366 + "pg_catalog.pg_get_constraintdef(pc.oid, true) AS consrc, "
367 + "pc.contype, "
368 + "CASE WHEN pc.contype='u' OR pc.contype='p' THEN ( "
369 + " SELECT "
370 + " indisclustered "
371 + " FROM "
372 + " pg_catalog.pg_depend pd, "
373 + " pg_catalog.pg_class pl, "
374 + " pg_catalog.pg_index pi "
375 + " WHERE "
376 + " pd.refclassid=pc.tableoid "
377 + " AND pd.refobjid=pc.oid "
378 + " AND pd.objid=pl.oid "
379 + " AND pl.oid=pi.indexrelid "
380 + ") ELSE "
381 + " NULL "
382 + "END AS indisclustered "
383 + "FROM "
384 + "pg_catalog.pg_constraint pc "
385 + "WHERE "
386 + "pc.conrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='{0}' "
387 + " AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace "
388 + " WHERE nspname='{1}')) "
389 + "ORDER BY "
390 + "1;", table.Name, table.SchemaName
391 ));
392 try {
393 using (command) {
394 using (IDataReader r = command.ExecuteReader()) {
395 while (r.Read ()) {
396 ConstraintSchema constraint = null;
397
398 //TODO: Add support for Check constraints.
399 switch (r.GetString (2)) {
400 case "f":
401 string match = @".*REFERENCES (.+)\(.*\).*";
402 constraint = new ForeignKeyConstraintSchema (this);
403 if (Regex.IsMatch (r.GetString (1), match))
404 (constraint as ForeignKeyConstraintSchema).ReferenceTableName
405 = Regex.Match (r.GetString (1), match).Groups[0].Captures[0].Value;
406 break;
407 case "u":
408 constraint = new UniqueConstraintSchema (this);
409 break;
410 case "p":
411 default:
412 constraint = new PrimaryKeyConstraintSchema (this);
413 break;
414 }
415
416 constraint.Name = r.GetString (0);
417 constraint.Definition = r.GetString (1);
418
419 int parenOpen = constraint.Definition.IndexOf ('(');
420 if (parenOpen > 0) {
421 int parenClose = constraint.Definition.IndexOf (')');
422 string colstr = constraint.Definition.Substring (parenOpen + 1, parenClose - parenOpen - 1);
423 foreach (string col in colstr.Split (',')) {
424 ColumnSchema column = new ColumnSchema (this, table);
425 column.Name = col.Trim ();
426 constraint.Columns.Add (column);
427 }
428 }
429
430 constraints.Add (constraint);
431 }
432 r.Close ();
433 }
434 }
435 } catch (Exception e) {
436 QueryService.RaiseException (e);
437 }
438 conn.Release ();
439
440 return constraints;
441 }
442
443 public override UserSchemaCollection GetUsers ()
444 {
445 UserSchemaCollection users = new UserSchemaCollection ();
446
447 IPooledDbConnection conn = connectionPool.Request ();
448 IDbCommand command = conn.CreateCommand ("SELECT * FROM pg_user;");
449
450 try {
451 using (command) {
452 using (IDataReader r = command.ExecuteReader ()) {
453 while (r.Read ()) {
454 UserSchema user = new UserSchema (this);
455
456 user.Name = r.GetString (0);
457 user.UserId = String.Format ("{0}", r.GetValue (1));
458 user.Expires = r.IsDBNull (6) ? DateTime.MinValue : r.GetDateTime (6);
459 //user.Options["createdb"] = r.GetBoolean (2);
460 //user.Options["createuser"] = r.GetBoolean (3);
461 user.Password = r.GetString (5);
462
463 StringBuilder sb = new StringBuilder ();
464 sb.AppendFormat ("-- User: \"{0}\"\n\n", user.Name);
465 sb.AppendFormat ("-- DROP USER {0};\n\n", user.Name);
466 sb.AppendFormat ("CREATE USER {0}", user.Name);
467 sb.AppendFormat (" WITH SYSID {0}", user.UserId);
468 if (user.Password != "********")
469 sb.AppendFormat (" ENCRYPTED PASSWORD {0}", user.Password);
470 //sb.AppendFormat (((bool) user.Options["createdb"]) ?
471 // " CREATEDB" : " NOCREATEDB");
472 //sb.AppendFormat (((bool) user.Options["createuser"]) ?
473 // " CREATEUSER" : " NOCREATEUSER");
474 if (user.Expires != DateTime.MinValue)
475 sb.AppendFormat (" VALID UNTIL {0}", user.Expires);
476 sb.Append (";");
477 user.Definition = sb.ToString ();
478
479 users.Add (user);
480 }
481 r.Close ();
482 }
483 }
484 } catch (Exception e) {
485 QueryService.RaiseException (e);
486 }
487 conn.Release ();
488
489 return users;
490 }
491
492 // public override DataTypeSchema GetDataType (string name)
493 // {
494 // if (name == null)
495 // throw new ArgumentNullException ("name");
496 // name = name.ToUpper ();
497 //
498 // DataTypeSchema dts = new DataTypeSchema (this);
499 // dts.Name = name;
500 // switch (name) {
501 // //TODO: IMPLEMENT
502 // case "":
503 // break;
504 // default:
505 // dts = null;
506 // break;
507 // }
508 //
509 // return dts;
510 // }
511
512 //http://www.postgresql.org/docs/8.2/interactive/sql-createdatabase.html
513 public override void CreateDatabase (DatabaseSchema database)
514 {
515 ExecuteNonQuery ("CREATE DATABASE " + database.Name);
516 }
517
518 //http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html
519 public override string GetTableCreateStatement (TableSchema table)
520 {
521 StringBuilder sb = new StringBuilder ();
522
523 sb.Append ("CREATE TABLE ");
524 sb.Append (table.Name);
525 sb.Append (" (");
526
527 bool first = true;
528 foreach (ColumnSchema column in table.Columns) {
529 if (first)
530 first = false;
531 else
532 sb.Append ("," + Environment.NewLine);
533
534 sb.Append (column.Name);
535 sb.Append (' ');
536 sb.Append (column.DataType.GetCreateString (column));
537
538 if (!column.IsNullable)
539 sb.Append (" NOT NULL");
540
541 if (column.HasDefaultValue) {
542 sb.Append (" DEFAULT ");
543 if (column.DefaultValue == null)
544 sb.Append ("NULL");
545 else
546 sb.Append (column.DefaultValue);
547 }
548
549 foreach (ConstraintSchema constraint in column.Constraints)
550 sb.Append (GetConstraintString (constraint, false));
551 }
552
553 foreach (ConstraintSchema constraint in table.Constraints) {
554 sb.Append ("," + Environment.NewLine);
555 sb.Append (GetConstraintString (constraint, true));
556 }
557
558 sb.Append (")");
559
560 if (table.TableSpaceName != null) {
561 sb.Append (" TABLESPACE ");
562 sb.Append (table.TableSpaceName);
563 sb.Append (';');
564 }
565
566 foreach (TriggerSchema trigger in table.Triggers) {
567 sb.Append (Environment.NewLine);
568 sb.Append (GetTriggerCreateStatement (trigger));
569 }
570
571 return sb.ToString ();
572 }
573
574 protected virtual string GetConstraintString (ConstraintSchema constraint, bool isTableConstraint)
575 {
576 StringBuilder sb = new StringBuilder ();
577 sb.Append ("CONSTRAINT ");
578 sb.Append (constraint.Name);
579 sb.Append (' ');
580
581 switch (constraint.ConstraintType) {
582 case ConstraintType.PrimaryKey:
583 sb.Append ("PRIMARY KEY ");
584 if (isTableConstraint)
585 sb.Append (GetColumnsString (constraint.Columns, true));
586 break;
587 case ConstraintType.Unique:
588 sb.Append ("UNIQUE ");
589 if (isTableConstraint)
590 sb.Append (GetColumnsString (constraint.Columns, true));
591 break;
592 case ConstraintType.ForeignKey:
593 sb.Append ("FOREIGN KEY ");
594 sb.Append (GetColumnsString (constraint.Columns, true));
595 sb.Append (" REFERENCES ");
596
597 ForeignKeyConstraintSchema fk = constraint as ForeignKeyConstraintSchema;
598 sb.Append (fk.ReferenceTable);
599 sb.Append (' ');
600 if (fk.ReferenceColumns != null)
601 sb.Append (GetColumnsString (fk.ReferenceColumns, true));
602 break;
603 case ConstraintType.Check:
604 sb.Append ("CHECK (");
605 sb.Append ((constraint as CheckConstraintSchema).Source);
606 sb.Append (")");
607 break;
608 default:
609 throw new NotImplementedException ();
610 }
611
612 return sb.ToString ();
613 }
614
615 //http://www.postgresql.org/docs/8.2/interactive/sql-createview.html
616 public override void CreateView (ViewSchema view)
617 {
618 throw new NotImplementedException ();
619 }
620
621 //http://www.postgresql.org/docs/8.2/interactive/sql-createindex.html
622 //http://www.postgresql.org/docs/8.2/interactive/sql-createconstraint.html
623 public override void CreateIndex (IndexSchema index)
624 {
625 throw new NotImplementedException ();
626 }
627
628 //http://www.postgresql.org/docs/8.2/interactive/sql-createtrigger.html
629 public override void CreateTrigger (TriggerSchema trigger)
630 {
631 string sql = GetTriggerCreateStatement (trigger);
632 ExecuteNonQuery (sql);
633 }
634
635 protected virtual string GetTriggerCreateStatement (TriggerSchema trigger)
636 {
637 StringBuilder sb = new StringBuilder ();
638
639 sb.Append ("CREATE TRIGGER ");
640 sb.Append (trigger.Name);
641
642 switch (trigger.TriggerType) {
643 case TriggerType.Before:
644 sb.Append (" BEFORE");
645 break;
646 case TriggerType.After:
647 sb.Append (" AFTER");
648 break;
649 default:
650 throw new NotImplementedException ();
651 }
652
653 switch (trigger.TriggerEvent) {
654 case TriggerEvent.Insert:
655 sb.Append (" INSERT ");
656 break;
657 case TriggerEvent.Update:
658 sb.Append (" UPDATE ");
659 break;
660 case TriggerEvent.Delete:
661 sb.Append (" DELETE ");
662 break;
663 default:
664 throw new NotImplementedException ();
665 }
666
667 sb.Append ("ON ");
668 sb.Append (trigger.TableName);
669 sb.Append (' ');
670 sb.Append (Environment.NewLine);
671
672 switch (trigger.TriggerFireType) {
673 case TriggerFireType.ForEachRow:
674 sb.Append (" FOR EACH ROW ");
675 break;
676 case TriggerFireType.ForEachStatement:
677 sb.Append (" FOR EACH STATEMENT ");
678 break;
679 default:
680 throw new NotImplementedException ();
681 }
682
683 sb.Append (Environment.NewLine);
684 sb.Append ("EXECUTE PROCEDURE ");
685 sb.Append (trigger.Source);
686 sb.Append (";");
687
688 return sb.ToString ();
689 }
690
691 //http://www.postgresql.org/docs/8.2/interactive/sql-createuser.html
692 public override void CreateUser (UserSchema user)
693 {
694 throw new NotImplementedException ();
695 }
696
697 //http://www.postgresql.org/docs/8.2/interactive/sql-alterdatabase.html
698 public override void AlterDatabase (DatabaseAlterSchema database)
699 {
700 throw new NotImplementedException ();
701 }
702
703 //http://www.postgresql.org/docs/8.2/interactive/sql-altertable.html
704 public override void AlterTable (TableAlterSchema table)
705 {
706 throw new NotImplementedException ();
707 }
708
709 //http://www.postgresql.org/docs/8.2/interactive/sql-alterindex.html
710 public override void AlterIndex (IndexAlterSchema index)
711 {
712 throw new NotImplementedException ();
713 }
714
715 //http://www.postgresql.org/docs/8.2/interactive/sql-altertrigger.html
716 public override void AlterTrigger (TriggerAlterSchema trigger)
717 {
718 throw new NotImplementedException ();
719 }
720
721 //http://www.postgresql.org/docs/8.2/interactive/sql-alteruser.html
722 public override void AlterUser (UserAlterSchema user)
723 {
724 throw new NotImplementedException ();
725 }
726
727 //http://www.postgresql.org/docs/8.2/interactive/sql-dropdatabase.html
728 public override void DropDatabase (DatabaseSchema database)
729 {
730 ExecuteNonQuery ("DROP DATABASE IF EXISTS " + database.Name + ";");
731 }
732
733 //http://www.postgresql.org/docs/8.2/interactive/sql-droptable.html
734 public override void DropTable (TableSchema table)
735 {
736 ExecuteNonQuery ("DROP TABLE IF EXISTS " + table.Name + ";");
737 }
738
739 //http://www.postgresql.org/docs/8.2/interactive/sql-dropview.html
740 public override void DropView (ViewSchema view)
741 {
742 ExecuteNonQuery ("DROP VIEW IF EXISTS " + view.Name + ";");
743 }
744
745 //http://www.postgresql.org/docs/8.2/interactive/sql-dropindex.html
746 public override void DropIndex (IndexSchema index)
747 {
748 ExecuteNonQuery ("DROP INDEX IF EXISTS " + index.Name + " ON " + index.TableName + ";");
749 }
750
751 //http://www.postgresql.org/docs/8.2/interactive/sql-droptrigger.html
752 public override void DropTrigger (TriggerSchema trigger)
753 {
754 ExecuteNonQuery ("DROP TRIGGER IF EXISTS " + trigger.Name + " ON " + trigger.TableName + ";");
755 }
756
757 //http://www.postgresql.org/docs/8.2/interactive/sql-dropuser.html
758 public override void DropUser (UserSchema user)
759 {
760 ExecuteNonQuery ("DROP USER IF EXISTS " + user.Name + ";");
761 }
762
763 //http://www.postgresql.org/docs/8.2/interactive/sql-alterdatabase.html
764 public override void RenameDatabase (DatabaseSchema database, string name)
765 {
766 ExecuteNonQuery ("ALTER DATABASE " + database.Name + " RENAME TO " + name + ";");
767
768 connectionPool.ConnectionContext.ConnectionSettings.Database = name;
769 database.Name = name;
770 }
771
772 //http://www.postgresql.org/docs/8.2/interactive/sql-altertable.html
773 public override void RenameTable (TableSchema table, string name)
774 {
775 ExecuteNonQuery ("ALTER TABLE " + table.Name + " RENAME TO " + name + ";");
776
777 table.Name = name;
778 }
779
780 //http://www.postgresql.org/docs/8.2/interactive/sql-altertable.html
781 public override void RenameView (ViewSchema view, string name)
782 {
783 //this is no copy paste error, it really is "ALTER TABLE"
784 ExecuteNonQuery ("ALTER TABLE " + view.Name + " RENAME TO " + name + ";");
785
786 view.Name = name;
787 }
788
789 //http://www.postgresql.org/docs/8.2/interactive/sql-altertrigger.html
790 public override void RenameTrigger (TriggerSchema trigger, string name)
791 {
792 ExecuteNonQuery ("ALTER TRIGGER " + trigger.Name + " ON " + trigger.TableName + " RENAME TO " + name + ";");
793
794 trigger.Name = name;
795 }
796
797 //http://www.postgresql.org/docs/8.2/interactive/sql-alteruser.html
798 public override void RenameUser (UserSchema user, string name)
799 {
800 ExecuteNonQuery ("ALTER USER " + user.Name + " RENAME TO " + name + ";");
801
802 user.Name = name;
803 }
804
805 public override string GetViewAlterStatement (ViewSchema view)
806 {
807 //'CREATE ' <-- after this we insert
808 return view.Definition.Insert (6, "OR REPLACE ");
809 }
810
811 public override string GetProcedureAlterStatement (ProcedureSchema procedure)
812 {
813 //'CREATE ' <-- after this we insert
814 return procedure.Definition.Insert (6, "OR REPLACE ");
815 }
816
817 /// <summary>
818 /// Last system OID used in postgres to monitor system vs user
819 /// objects. This varies based on the connections Server Version.
820 /// </summary>
821 protected int LastSystemOID {
822 get {
823 IPooledDbConnection conn = connectionPool.Request ();
824 NpgsqlConnection internalConn = conn.DbConnection as NpgsqlConnection;
825 int major = internalConn.ServerVersion.Major;
826 int minor = internalConn.ServerVersion.Minor;
827 conn.Release ();
828
829 if (major == 8)
830 return 17137;
831 else if (major == 7 && minor == 1)
832 return 18539;
833 else if (major == 7 && minor == 2)
834 return 16554;
835 else if (major == 7 && minor == 3)
836 return 16974;
837 else if (major == 7 && minor == 4)
838 return 17137;
839 else
840 return 17137;
841 }
842 }
843 }
844 }
Cloned from the original SVN repo at mono-cvs.ximian.com