Some small issues with MyMeta and PostgreSQL

[snakess]

I noticed that if I create an index on a column the \"IsInPrimaryKey\" property for that column will be set to True.
(At least with PostgreSQL 8)


The \"IsAutoKey\" property is never set to True.

If a column uses a sequence for its default value it could be considered an \"AutoKey\" column.

For PostgreSQL 8 you can use \"select pg_get_serial_sequence('table_name', 'column_name')\" for 7.* you have to query the system tables.

I can post an example if anyone is interested.

Anyway it's a great product, keep up the good work!
/Peter

[mike.griffin]

Hmmm, let me look into this, I do query the system data for all information, let me run this tonight and see what happens, let's work together on this, I like the meta-data to be perfect.

[snakess]

Sounds good to me.
Let me know if I can be of any help.

[mike.griffin]

Do you have any desire to have PostgreSQL dOOdads, they're pretty easy to implement, we could work together. What exactly are you doing with PostgreSQL and if you were to use dOOdads would you want it to use Stored Procedures or total dynamic SQL.

Anyway, think about it ...

[snakess]

At the moment I'm mostly using PostgreSQL with Npgsql in various asp.net projects.

PostgreSQL dOOdads would be cool.
I could try to implement DbAdapters for PostgreSQL as a start.

I think that the improvments to MyMeta that I mentioned in my initial post has to be done before we can make working dOOdads templates.

MyMeta doesn't list any stored procedures for PostgreSQL.
You can get a list of all functions in a schema with a query like:

SELECT p.proname FROM pg_proc p , pg_namespace ns
WHERE p.pronamespace = ns.oid
AND ns.nspname = 'public'

I guess that the primary key issue can be solved by checking the value of indisprimary in pg_index.

If you send me the code / query you use for getting the properties for a column I can fix the IsAutoKey property.

[mike.griffin]

Okay, I will work on these things this weekend, maybe I'll post a test build so you can verify it before we release 1.1

[mike.griffin]

Okay, I've made some progress, I can get a list of prodedure names, and now I'm onto the parameters, should have it done soon.

Also, can you post the code from 'pg_get_serial_sequence'as we need to be backward compatible.

Also, you mentioned something about PrimaryKey, it seems to be working okay, here's my query:

Code: Select all

select a.attname as COLUMN_NAME, c.conname as constraint, d.relname as table from pg_attribute a, pg_index b, pg_constraint c, pg_class d, pg_namespace n WHERE n.nspname = '\" + this.Schema + \"' AND d.relname = '\" + this.Name + \"' AND a.attrelid = b.indexrelid and b.indrelid = c.conrelid and b.indrelid = d.relfilenode and c.contype = 'p'\";

Where this.Name and this.Schema are the Table properties. Try this query on your primary key that has an index on it. I'm using pgAdmin, see if your enterprise tool shows that column as a primary key.

Also, I'm pretty sure PostgreSQL 8 has the INFORMATION_SCHEMA views (it's own schema I believe) if so can you send me the view for COLUMNS and PARAMETERS and PROCEDURES.

Thanx

[snakess]

'pg_get_serial_sequence' is an internal function written in C, it's not a query;

To get the names of the columns that use a sequence for their default values
along with the names of the sequences you can use the following query:

SELECT a.attname AS column_name, c2.relname AS seq_name
FROM pg_class c1, pg_class c2, pg_namespace n, pg_depend d, pg_attribute a
WHERE n.nspname = 'schemaName' AND c1.relname = 'tableName'
AND c2.relkind = 'S'AND c1.relnamespace = n.oid
AND d.refobjid = c1.oid AND c2.oid = d.objid
AND a.attrelid = c1.oid AND d.refobjsubid = a.attnum

Once you have the name of the sequence you can get
the values for \"AutoKeySeed\" and \"AutoKeyIncrement\" with:

SELECT min_value, increment_by FROM \"sequence_name\";


I was unclear about the primary key issue.
The problem isn't that the primary keys aren't found but that all columns that are
indexed are displayed as belonging to the primary key. (pgAdmin does this too)

If you create the following table and view it in MyGeneration (or pgAdmin) you will see
that all three columns appears as they belong to the primary key.

CREATE TABLE test ( col1 int4, col2 int4, col3 int4, PRIMARY KEY (col1, col2));
CREATE INDEX my_index ON test (col3);

To get the names of the columns in the primary key for a table you can use:

SELECT a.attname
FROM pg_class c, pg_namespace n, pg_index i, pg_attribute a
WHERE n.nspname = 'schemaName' AND c.relname = 'tableName'
AND c.relnamespace = n.oid AND i.indrelid = c.oid AND i.indisprimary
AND a.attrelid = i.indexrelid;

or just append \"AND b.indisprimary\" to the query you posted.

I've tested the above queries with PostgreSQL 8.0, 7.4.5 and 7.4

The information_schema views looks like this:

columns:
----------------------------------------------------------------------------------------------------

CREATE OR REPLACE VIEW information_schema.columns AS
SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name, a.attname::information_schema.sql_identifier AS column_name, a.attnum::information_schema.cardinal_number AS ordinal_position,
CASE
WHEN u.usename = \"current_user\"() THEN ad.adsrc
ELSE NULL::text
END::information_schema.character_data AS column_default,
CASE
WHEN a.attnotnull OR t.typtype = 'd'::\"char\" AND t.typnotnull THEN 'NO'::text
ELSE 'YES'::text
END::information_schema.character_data AS is_nullable,
CASE
WHEN t.typtype = 'd'::\"char\" THEN
CASE
WHEN bt.typelem <> 0::oid AND bt.typlen = -1 THEN 'ARRAY'::text
WHEN nbt.nspname = 'pg_catalog'::name THEN format_type(t.typbasetype, NULL::integer)
ELSE 'USER-DEFINED'::text
END
ELSE
CASE
WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'ARRAY'::text
WHEN nt.nspname = 'pg_catalog'::name THEN format_type(a.atttypid, NULL::integer)
ELSE 'USER-DEFINED'::text
END
END::information_schema.character_data AS data_type, information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_maximum_length, information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_octet_length, information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision, information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision_radix, information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_scale, information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS datetime_precision, NULL::information_schema.character_data::information_schema.character_data AS interval_type, NULL::information_schema.character_data::information_schema.character_data AS interval_precision, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS character_set_catalog, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS character_set_schema, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS character_set_name, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS collation_catalog, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS collation_schema, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS collation_name,
CASE
WHEN t.typtype = 'd'::\"char\" THEN current_database()
ELSE NULL::name
END::information_schema.sql_identifier AS domain_catalog,
CASE
WHEN t.typtype = 'd'::\"char\" THEN nt.nspname
ELSE NULL::name
END::information_schema.sql_identifier AS domain_schema,
CASE
WHEN t.typtype = 'd'::\"char\" THEN t.typname
ELSE NULL::name
END::information_schema.sql_identifier AS domain_name, current_database()::information_schema.sql_identifier AS udt_catalog, COALESCE(nbt.nspname, nt.nspname)::information_schema.sql_identifier AS udt_schema, COALESCE(bt.typname, t.typname)::information_schema.sql_identifier AS udt_name, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS scope_catalog, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS scope_schema, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS scope_name, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS maximum_cardinality, a.attnum::information_schema.sql_identifier AS dtd_identifier, 'NO'::information_schema.character_data::information_schema.character_data AS is_self_referencing
FROM pg_attribute a
LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum, pg_class c, pg_namespace nc, pg_user u, pg_type t
JOIN pg_namespace nt ON t.typnamespace = nt.oid
LEFT JOIN (pg_type bt
JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd'::\"char\" AND t.typbasetype = bt.oid
WHERE a.attrelid = c.oid AND a.atttypid = t.oid AND u.usesysid = c.relowner AND nc.oid = c.relnamespace AND a.attnum > 0 AND NOT a.attisdropped AND (c.relkind = 'r'::\"char\" OR c.relkind = 'v'::\"char\") AND (u.usename = \"current_user\"() OR has_table_privilege(c.oid, 'SELECT'::text) OR has_table_privilege(c.oid, 'INSERT'::text) OR has_table_privilege(c.oid, 'UPDATE'::text) OR has_table_privilege(c.oid, 'REFERENCES'::text));

parameters:
------------------------------------------------------------------------------------------------------

CREATE OR REPLACE VIEW information_schema.parameters AS
SELECT current_database()::information_schema.sql_identifier AS specific_catalog, n.nspname::information_schema.sql_identifier AS specific_schema, ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier AS specific_name, pos.n::information_schema.cardinal_number AS ordinal_position, 'IN'::information_schema.character_data::information_schema.character_data AS parameter_mode, 'NO'::information_schema.character_data::information_schema.character_data AS is_result, 'NO'::information_schema.character_data::information_schema.character_data AS as_locator, NULLIF(p.proargnames[pos.n], ''::text)::information_schema.sql_identifier AS parameter_name,
CASE
WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'ARRAY'::text
WHEN nt.nspname = 'pg_catalog'::name THEN format_type(t.oid, NULL::integer)
ELSE 'USER-DEFINED'::text
END::information_schema.character_data AS data_type, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS character_maximum_length, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS character_octet_length, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS character_set_catalog, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS character_set_schema, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS character_set_name, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS collation_catalog, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS collation_schema, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS collation_name, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS numeric_precision, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS numeric_precision_radix, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS numeric_scale, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS datetime_precision, NULL::information_schema.character_data::information_schema.character_data AS interval_type, NULL::information_schema.character_data::information_schema.character_data AS interval_precision, current_database()::information_schema.sql_identifier AS udt_catalog, nt.nspname::information_schema.sql_identifier AS udt_schema, t.typname::information_schema.sql_identifier AS udt_name, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS scope_catalog, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS scope_schema, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS scope_name, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS maximum_cardinality, pos.n::information_schema.sql_identifier AS dtd_identifier
FROM pg_namespace n, pg_proc p, pg_type t, pg_namespace nt, pg_user u, information_schema._pg_keypositions() pos(n)
WHERE n.oid = p.pronamespace AND p.pronargs >= pos.n AND p.proargtypes[pos.n - 1] = t.oid AND t.typnamespace = nt.oid AND p.proowner = u.usesysid AND (u.usename = \"current_user\"() OR has_function_privilege(p.oid, 'EXECUTE'::text));

There is no view named procedures, it's called routines:
-------------------------------------------------------------------------------------------------

CREATE OR REPLACE VIEW information_schema.routines AS
SELECT current_database()::information_schema.sql_identifier AS specific_catalog, n.nspname::information_schema.sql_identifier AS specific_schema, ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier AS specific_name, current_database()::information_schema.sql_identifier AS routine_catalog, n.nspname::information_schema.sql_identifier AS routine_schema, p.proname::information_schema.sql_identifier AS routine_name, 'FUNCTION'::information_schema.character_data::information_schema.character_data AS routine_type, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS module_catalog, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS module_schema, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS module_name, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS udt_catalog, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS udt_schema, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS udt_name,
CASE
WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN 'ARRAY'::text
WHEN nt.nspname = 'pg_catalog'::name THEN format_type(t.oid, NULL::integer)
ELSE 'USER-DEFINED'::text
END::information_schema.character_data AS data_type, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS character_maximum_length, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS character_octet_length, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS character_set_catalog, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS character_set_schema, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS character_set_name, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS collation_catalog, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS collation_schema, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS collation_name, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS numeric_precision, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS numeric_precision_radix, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS numeric_scale, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS datetime_precision, NULL::information_schema.character_data::information_schema.character_data AS interval_type, NULL::information_schema.character_data::information_schema.character_data AS interval_precision, current_database()::information_schema.sql_identifier AS type_udt_catalog, nt.nspname::information_schema.sql_identifier AS type_udt_schema, t.typname::information_schema.sql_identifier AS type_udt_name, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS scope_catalog, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS scope_schema, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS scope_name, NULL::information_schema.cardinal_number::information_schema.cardinal_number AS maximum_cardinality, 0::information_schema.sql_identifier AS dtd_identifier,
CASE
WHEN l.lanname = 'sql'::name THEN 'SQL'::text
ELSE 'EXTERNAL'::text
END::information_schema.character_data AS routine_body,
CASE
WHEN u.usename = \"current_user\"() THEN p.prosrc
ELSE NULL::text
END::information_schema.character_data AS routine_definition,
CASE
WHEN l.lanname = 'c'::name THEN p.prosrc
ELSE NULL::text
END::information_schema.character_data AS external_name, upper(l.lanname::text)::information_schema.character_data AS external_language, 'GENERAL'::information_schema.character_data::information_schema.character_data AS parameter_style,
CASE
WHEN p.provolatile = 'i'::\"char\" THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.character_data AS is_deterministic, 'MODIFIES'::information_schema.character_data::information_schema.character_data AS sql_data_access,
CASE
WHEN p.proisstrict THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.character_data AS is_null_call, NULL::information_schema.character_data::information_schema.character_data AS sql_path, 'YES'::information_schema.character_data::information_schema.character_data AS schema_level_routine, 0::information_schema.cardinal_number AS max_dynamic_result_sets, NULL::information_schema.character_data::information_schema.character_data AS is_user_defined_cast, NULL::information_schema.character_data::information_schema.character_data AS is_implicitly_invocable,
CASE
WHEN p.prosecdef THEN 'DEFINER'::text
ELSE 'INVOKER'::text
END::information_schema.character_data AS security_type, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS to_sql_specific_catalog, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS to_sql_specific_schema, NULL::information_schema.sql_identifier::information_schema.sql_identifier AS to_sql_specific_name, 'NO'::information_schema.character_data::information_schema.character_data AS as_locator
FROM pg_namespace n, pg_proc p, pg_language l, pg_user u, pg_type t, pg_namespace nt
WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner = u.usesysid AND p.prorettype = t.oid AND t.typnamespace = nt.oid AND (u.usename = \"current_user\"() OR has_function_privilege(p.oid, 'EXECUTE'::text));

Not the simplest views I've seen...

[mike.griffin]

Excellent, I think I'll be up late tonight ...

[mike.griffin]

Okay, Justin and I now have both PostgreSQL 7.X and 8.0 installed, I have a few choices, try to get my SQL queries to work with both versions or create an entirely new entry in MyMeta called PostgreSQL2 for folks using 8.+ and that way I can use the Information Schema Views and such, I think this is the way I'm going to go.

BTW: Are you pretty good with PostgreSQL stored proc's, if you take our \"Stored Procedures dOOdads and more ...\" template and make it work on PostgreSQL I can have the dOOdads architecture ready and we can release fully functional doodads in 1.1, it's easier than you think.

[snakess]

I'll try to make a template for PostgreSQL based on the \"Stored Procedures dOOdads and more ...\" this evening/night.

It would be nice to have a property in MyMeta with the sequence name for columns that uses sequences. For now I can get it from the \"Default\" property. I need to return currval('sequence_name') from the insert function.

I'll create the template assuming that the \"IsAutoKey\" and \"IsInPrimaryKey\" will have valid values.

[mike.griffin]

Cool, remember you can actually query the database while your tempalte is executing through IDatabase.ExecuteSql if you need to get data that isn't in our meta data

[mike.griffin]

Progress, I'm making good progress on PostgreSQL 8, there will indeed be a \"PostgreSQL 8+\" in the default settings, I'm using information schema views and system functions, the meta-data is going to be very good.

[snakess]

I've made a rewrite of your template, you can get it at:
http://www.niru.se/files/PostgreSQL_StoredProcs.vbgen

Testing and comments are welcome.
(It's 2 am and this is the first time I've written something in VB)

[mike.griffin]

Woah, excellent, maybe we'll have fully functional PostgreSql dOOdads for MyGen 1.1, I'll check it out, but I'm on hot the pursuit of meta data for the moment.

[mike.griffin]

A quick question, the MyMeta API doesn't deal with Schema's really (this will be in MyMeta II however) so currently I only deal with the Public Schema of PostgreSQL, do you see this as a problem?

[snakess]

I don't think that will be a problem, not for me anyway.

[mike.griffin]

I'll be posting a link for you to test late tonight with a new build for PostgreSQL 8.0, we can unit test before 1.1

[mike.griffin]

I've made a rewrite of your template, you can get it at:
http://www.niru.se/files/PostgreSQL_StoredProcs.vbgen

Testing and comments are welcome.
(It's 2 am and this is the first time I've written something in VB)

I downloaded it and it generated just fine, man you work quick, when I tried to run it however I got a few errors:

Code: Select all

LANGUAGE 'plpgsql';

ERROR: language \"plpgsql\" does not exist

The good news is here is the interim build, just switch to PostgreSQL 8+ in the default settings dialog using the same connection string.
http://demo.mygenerationsoftware.com/mygenV1_PostgreSQL8.exe

[snakess]

I downloaded it and it generated just fine, man you work quick, when I tried to run it however I got a few errors:

Code: Select all

LANGUAGE 'plpgsql';

ERROR: language \"plpgsql\" does not exist

You will have to add PL/pgSQL to your database with:
createlang -U postgres -W plpgsql <dbname>
On windows createlang.exe is in the bin dir of your PostgreSQL install dir,
on Linux it should be in the postgres users path.

I've choosen PL/pgSQL for the update and insert functions, it made them easier to implement.

The functions are not written with named parameters as PostgreSQl 7.*
doesn't support them. I guess that I could use the the driver property
to generate different versions of the functions, I'll look into that.

Notice that the template will not work properly until the IsAutoKey and IsInPrimaryKey properties have been fixed.

The good news is here is the interim build, just switch to PostgreSQL 8+ in the default settings dialog using the same connection string.

Nice!!!
It seems to be working ok, well done!
I can see my functions with parameters and all. I'll let you know if I find anything strange.