[PostgreSQL] Dynamische tabellen, inserts en casting

[Reneger]

Hoi,

Voor een project ben ik bezig om een dynamische database te maken.
Wat houdt dit in?
De gebruiker moet on-the-fly onderwerpen (Tabellen) aan kunnen maken
Deze tabellen moeten zelf in te delen zijn.

Hiervoor heb ik de volgende aanpak gedaan:
Er is een hoofdtabel (in dit geval: ITO) waar alle metadata in staat:

SQL:

CREATE TABLE ito
(
  id serial NOT NULL,
  name character varying(255) NOT NULL,
  description text NOT NULL,
  status smallint,
  version integer,
  changes text,
  ito_table_name character varying(255),
  ito_owner bigint,
  ito_creator bigint,
);

met nog een aantal FK's e.d.
De metadata van de velden in de dynamische tabellen worden ook opgeslagen, verrassend in de tabel: ito_fields:

SQL:

CREATE TABLE ito_fields
(
  id serial NOT NULL,
  guid uuid NOT NULL DEFAULT (md5(((nextval('guid_id_seq'::regclass) || ''::text) || now())))::uuid,
  column_name character varying(255) NOT NULL,
  name character varying(100) NOT NULL,
  description character varying(255) NOT NULL,
  status smallint,
  indexed boolean,
  field_version integer,
  semantic_change_description text,
  syntactic_change_description text,
  ito_id bigint NOT NULL,
  fieldtype integer NOT NULL,
);

Waarbij fieldtype verwijst naar een native datatype.

Als deze 2 tabellen zijn gevuld voor een onderwerp, wordt de dynamische tabel gecreëerd. dit werkt als een tierelier.

Maar dan:

De users hebben geen toegang op de tabellen zelf. Kortom, via functions wordt de data ontsloten. Hier komt mijn probleem:
Vanuit een functie moet er data toegevoegd worden aan een dynamische tabel.
Omdat je niet weet hoeveel kolommen er in een tabel zitten, is er hier gekozen voor een array. Om toch alle datatypes te ondersteunen, is er gekozen voor een array of text (text[]).

Voor integers, booleans en double precisions is dit geen probleem, maar bij het invoegen van text gaat het mis.

SQL:

CREATE OR REPLACE FUNCTION add(id integer, fields text[])
  RETURNS integer AS
$BODY$
DECLARE
l_line_ending text := ')';
  l_fieldtype integer;
  l_ito_table_name text;
  l_ito_fieldnames text;
  l_field ito_fields%rowtype;
  l_first_loop boolean := true;
  l_values_to_insert text := 'VALUES (';
  l_loop_counter integer := 0;
  l_query text;

BEGIN
  select into l_ito_table_name ito_table_name from ito where id = target_ito_id;
  l_ito_fieldnames := 'insert into ' || l_ito_table_name || '(';
    FOR l_field IN SELECT * FROM ito_fields
    WHERE ito_fields.ito_id = target_ito_id
    order by ito_fields.id asc
    LOOP
        l_loop_counter := l_loop_counter +1;
        l_fieldtype := l_field.fieldtype;
        if not l_first_loop THEN
            l_values_to_insert := (l_values_to_insert || ', ');
        end if;
        if l_field.fieldtype = 1 THEN
             l_values_to_insert := (l_values_to_insert || '''' || (fields[l_loop_counter]) || '''' );
        elsif l_field.fieldtype = 2 THEN
            l_values_to_insert := quote_literal(l_values_to_insert || private.cast_to_integer(fields[l_loop_counter]));
        elsif l_field.fieldtype = 3 THEN
            l_values_to_insert := quote_literal(l_values_to_insert || private.cast_to_boolean(fields[l_loop_counter]));
        elsif l_field.fieldtype = 4 THEN
            l_values_to_insert := quote_literal(l_values_to_insert || private.cast_to_float(fields[l_loop_counter]));
        else 
            return 103;
        end if;
        if l_first_loop then
            l_ito_fieldnames := l_ito_fieldnames || l_field.column_name;
            l_first_loop := false;
        else
            l_ito_fieldnames := l_ito_fieldnames || ', ' || l_field.column_name;
        end if;
    END LOOP;
    l_ito_fieldnames := l_ito_fieldnames || l_line_ending;
    l_values_to_insert := ((l_values_to_insert) || (l_line_ending));
    l_query := (l_ito_fieldnames || l_values_to_insert);
    EXECUTE l_query;
  return 0;

END;

Omdat de query aan elkaar geconcatenate wordt, gaat het mis bij het toevoegen van de (single) quotes. Postgres vertikt het om te begrijpen dat het geëscapede quotes zijn. Zelfs bij een constructie als geescapede quotes gaat het nog mis.
Ook bracht de quote_literal/quote_nullable geen uitkomst.


Zie ik hier iets over het hoofd, is er een makkelijkere oplossing? Kortom, hoe maak ik een dynamische insert als ik niet weet op voorhand welke velden ik heb?


Extra info: Postgres 9.1, Windows 7 en Redhat 6.1.

[leuk_he]

lamaar nie goed gelezen

[ Voor 84% gewijzigd door leuk_he op 06-02-2012 15:37 ]

[Remus]

leuk_he schreef op maandag 06 februari 2012 @ 15:35:
Even afgezien dat jij beter uit bent met NO-SQL, en dat het hele idee van SQL onderuit gehaald wordt.. dit is leuk als oefning, maar ga hier niet te ver in door!.

De tabel waarin je insert is statisch. Het is de enige tabel. Dus je kunt gewoon een INSERT statement gebruiken, ipv een dynamische EXECUTE

Het is niet de enige tabel. Die twee tabellen beschrijven de structuur (ofwel: een database in een database), terwijl er wel degelijk fysieke tabellen worden gemaakt op basis van de beschreven structuur.
De query voor een specifieke dynamische tabel wordt opgebouwd in de beschreven FUNCTION.

Ik zelf zou iig niet voor deze oplossingsrichting kiezen. Ik vraag me dan ook sterk af waarom een dergelijke dynamische structuur nodig is (misschien is er nl een makkelijkere oplossing).

[Reneger]

De keuze van PostgreSQL ligt vast. Dit is bepaald door de klant.

De keuze van deze invulling is geheel van mijn zijde.

Ik heb zitten te twijfelen tussen deze oplossing (De dynamische tabellen), of een aantal grote tabellen met de betreffende datatypes, welke met ID's aan elkaar gekoppeld worden (Statische tabellen dus, maar veel groter).
Het nadeel van die laatste is de performance. Dat heeft mij doen besluiten om de oplossing zoals in de startpost beschreven te gaan gebruiken.

-edit-

(misschien is er nl een makkelijkere oplossing).

Enlighten me

[ Voor 8% gewijzigd door Reneger op 06-02-2012 16:42 ]

[Remus]

Reneger schreef op maandag 06 februari 2012 @ 16:41:
De keuze van PostgreSQL ligt vast. Dit is bepaald door de klant.

De keuze van deze invulling is geheel van mijn zijde.

Ik heb zitten te twijfelen tussen deze oplossing (De dynamische tabellen), of een aantal grote tabellen met de betreffende datatypes, welke met ID's aan elkaar gekoppeld worden (Statische tabellen dus, maar veel groter).
Het nadeel van die laatste is de performance. Dat heeft mij doen besluiten om de oplossing zoals in de startpost beschreven te gaan gebruiken.

-edit-

[...]

Enlighten me

Je hebt nog niet uitgelegd wat het op te lossen probleem is, dus een oplossing aandragen is dan een beetje moeilijk.

[Reneger]

De klant wil alles kunnen opslaan. Hiervoor heb ik dus inderdaad een soort van dbase in dbase oplossing gekozen.

Ik loop nu tegen meerdere zaken aan; er moet security gerealiseerd worden (Inclusief sharing), mensen mogen niet rechtstreeks in tabellen vrotten, etc. waarbij de frontends on-the-fly users en groepen moeten kunnen toevoegen, die dus, met de huidige opzet, overal in Postgres moeten toegevoegd worden, alsmede er extra contactgegevens e.d. in de database bijgevoegd moeten worden.
daarnaast moet er ontsloten kunnen worden richting meerdere front-ends.

Ik vraag me nu serieus af of bovenstaande in een database gerealiseerd kan worden, of dat er toch echt een applicatielaag tussen geschoven moet worden...

Excuses voor mn late reactie, beetje druk gehad. Overigens is mijn 1e probleem via een work-around opgelost, ik bouw het record field voor field op...

[Killemov]

Hmmm, deze vraag zou op twee manieren geinterpreteerd kunnen worden:

• De klant heeft een gebrek aan een DBA. Dit zou opgelost kunnen worden met het implementeren van een interface for dummies.
• De data is wel gestructureerd, maar niet gestructureerd genoeg om echt vast te leggen. Dan kun je een excel-achtige oplossing nabouwen of met de XML-mogelijkheden van Postgres zelf aan de slag gaan. http://www.postgresql.org...static/functions-xml.html

[JaQ]

Reneger schreef op vrijdag 10 februari 2012 @ 14:58:
De klant wil alles kunnen opslaan.

Geef je klant een licentie op access, een andere tabel+form generator (magic of zo) of een cursus sql. Wat je nu aan het doen bent zorgt iig niet voor een oplossing die schaalbaar is en zal voor hoofdpijn bij zowel jou als de klant gaan zorgen.

Mocht je toch eigenwijs zijn en je klant gaan beroven, kies dan op zijn minstvoor een oplossing waarmee je niet dynamisch tabellen gaat aanmaken. Sla je data op id, datatype, waarde reeksen (aangevuld met wat extra sexy metadata om het af te maken). Het "bekende" 5 tabellensysteem waarin je "alles" kan opslaan.(op asktom.oracle.com kan je dit model ongetwijfeld terug vinden).

[Reneger]

@Killemov: Het wordt een generiek, open systeem. Wat mijn taak is, het faciliteren van de opslag van alle soorten en onderwerpen, waarbij in mijn opzet de onderwerpen dus tabellen zijn.
De data is dus wel gestructureerd, maar je weet nog niet hoe. XML zou een uitkomst kunnen zijn, maar ik vraag me af in hoeverre er hier op gezocht kan worden. Dat is juist de reden dat ik voor de losse, dynamische, tabellen ben gegaan.
Je behoudt je performance (Losse tables, geen grote brij aan data in de 5 tabellen, zoals JaQ aandraagt), en je kan er relatief simpel in zoeken.

@JaQ: 5 tabellen systeem ben ik mee bekend. Maar als je 10.000 records gaat opslaan, ben je al je performance kwijt. Dit idee hebben we juist om die reden overboord gekieperd.

Na overleg met de klant gaan we toch op deze manier door, ik heb de risico's aangegeven. Dus het is zijn call

[matthijsln]

JaQ schreef op zaterdag 11 februari 2012 @ 02:50:
[...]
Mocht je toch eigenwijs zijn en je klant gaan beroven, kies dan op zijn minstvoor een oplossing waarmee je niet dynamisch tabellen gaat aanmaken. Sla je data op id, datatype, waarde reeksen (aangevuld met wat extra sexy metadata om het af te maken). Het "bekende" 5 tabellensysteem waarin je "alles" kan opslaan.(op asktom.oracle.com kan je dit model ongetwijfeld terug vinden).

Ehm, het ging er juist om dat je dat niet moest doen. Tenminste, als je deze link bedoelde:

Ask Tom "Query on design"

[lapismontes]

code:

  l_ito_fieldnames := 'insert into ' || l_ito_table_name || '(';

[...]

'''' || (fields[l_loop_counter])

Omdat de query aan elkaar geconcatenate wordt.

Ik zou je dit design willen afraden. Zodra je strings aan elkaar gaat plakken zonder validatie is er een mogelijkheid dat je SQL Injection tegen gaat komen.

Wat als ik de functie aanroep met zoiets als:

code:

SELECT  add(2,{"Test","; DELETE FROM ito; --"};

Wat gebeurt er dan?

Ik zou er in mijn ontwerp altijd voor zorgen dat een query niet dynamisch wordt opgebouwd met tekst die een eingebruiker mee kan geven. Naast het feit dat dit een beveiligingslek zou kunnen zijn kan PostgreSQL (of wat voor database dan ook) telkens opnieuw gaan uitvogelen hoe de query uitgevoerd moet worden.

Als je queries met bindvariabelen (prepared statements, parameterized queries) gebruikt wapen je jezelf tegen dit soort beveilgingsproblemen en verbeter je vermoedelijk ook je performance.

[cariolive23]

Is het gebruik van hstore geen oplossing? Dan kun je de boel ook indexeren met GiST of GIN indexen, wat de snelheid ten goede komt.