Struktura:
Api:
CREATE SEQUENCE contents_increment START 1 INCREMENT 1 MAXVALUE 9223372036854775807 MINVALUE 1 CACHE 1; CREATE SEQUENCE nodes_increment START 1 INCREMENT 1 MAXVALUE 9223372036854775807 MINVALUE 1 CACHE 1; CREATE TABLE contents ( id bigint DEFAULT NEXTVAL('contents_increment'::text) NOT NULL, content text ); CREATE TABLE nodes ( id bigint DEFAULT NEXTVAL('nodes_increment'::text) NOT NULL, parent bigint, name character varying(100) ); CREATE TABLE asociations ( first_id bigint DEFAULT 0 NOT NULL, second_id bigint DEFAULT 0 NOT NULL, depth bigint DEFAULT 0 NOT NULL ); CREATE TABLE binds ( node_id bigint DEFAULT 0 NOT NULL, content_id bigint DEFAULT 0 NOT NULL ); CREATE INDEX nodes_parent_ind ON nodes USING btree (parent); CREATE INDEX asociations_second_id_depth_ind ON asociations USING btree (second_id, depth); CREATE INDEX asociations_first_id_depth_ind ON asociations USING btree (first_id, depth); CREATE INDEX asociations_first_id_ind ON asociations USING btree (first_id); CREATE INDEX asociations_second_id_ind ON asociations USING btree (second_id); ALTER TABLE ONLY nodes ADD CONSTRAINT nodes_parent_name_key UNIQUE (parent, name); ALTER TABLE ONLY nodes ADD CONSTRAINT nodes_pkey PRIMARY KEY (id); ALTER TABLE ONLY nodes ADD CONSTRAINT "$1" FOREIGN KEY (parent) REFERENCES nodes(id) ON UPDATE NO ACTION ON DELETE CASCADE; ALTER TABLE ONLY contents ADD CONSTRAINT contents_pkey PRIMARY KEY (id); ALTER TABLE ONLY binds ADD CONSTRAINT binds_node_id_content_id_key UNIQUE (node_id, content_id); ALTER TABLE ONLY binds ADD CONSTRAINT binds_pkey PRIMARY KEY (node_id, content_id); ALTER TABLE ONLY binds ADD CONSTRAINT "$1" FOREIGN KEY (content_id) REFERENCES contents(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE ONLY binds ADD CONSTRAINT "$2" FOREIGN KEY (node_id) REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE ONLY asociations ADD CONSTRAINT asociations_pkey PRIMARY KEY (first_id, second_id); ALTER TABLE ONLY asociations ADD CONSTRAINT "$1" FOREIGN KEY (second_id) REFERENCES nodes(id) ON UPDATE NO ACTION ON DELETE CASCADE; ALTER TABLE ONLY asociations ADD CONSTRAINT "$2" FOREIGN KEY (first_id) REFERENCES nodes(id) ON UPDATE NO ACTION ON DELETE CASCADE; SELECT pg_catalog.SETVAL ('nodes_increment', 1, true); SELECT pg_catalog.SETVAL ('contents_increment', 1, true); INSERT INTO nodes (id, parent, name) VALUES (1, NULL, 'main'); INSERT INTO asociations (first_id, second_id, depth) VALUES (1, 1, 0);
-- get_path DROP FUNCTION get_path (bigint); CREATE FUNCTION get_path (bigint) RETURNS text AS ' DECLARE nid ALIAS FOR $1; path TEXT; item RECORD; BEGIN path := ''/''::TEXT; FOR item IN SELECT n.name FROM nodes n, asociations a WHERE n.id = a.first_id AND a.second_id = nid ORDER BY a.depth DESC LOOP path := path || item.name || ''/''::TEXT; END LOOP; RETURN path; END; ' LANGUAGE plpgsql; -- mkdir DROP FUNCTION mkdir (character varying, bigint); CREATE FUNCTION mkdir (character varying, bigint) RETURNS BOOLEAN AS ' DECLARE nn ALIAS FOR $1; np ALIAS FOR $2; BEGIN INSERT INTO nodes (name, parent) VALUES (nn, np); INSERT INTO asociations (first_id, second_id, depth) SELECT first_id, (SELECT id FROM nodes WHERE name = nn AND parent = np), depth + 1 FROM asociations WHERE second_id = np; INSERT INTO asociations (first_id, second_id, depth) VALUES((SELECT id FROM nodes WHERE name = nn AND parent = np), (SELECT id FROM nodes WHERE name = nn AND parent = np), 0); RETURN true; END; ' LANGUAGE plpgsql; -- mv DROP FUNCTION mv (bigint, bigint); CREATE FUNCTION mv (bigint, bigint) RETURNS BOOLEAN AS ' DECLARE nid ALIAS FOR $1; np ALIAS FOR $2; temp1 RECORD; temp2 RECORD; BEGIN FOR temp1 IN SELECT MAX(depth) AS max, second_id FROM asociations WHERE first_id = nid GROUP BY second_id LOOP DELETE FROM asociations WHERE second_id = temp1.second_id AND depth > temp1.max; END LOOP; UPDATE nodes SET parent = np WHERE id = nid; INSERT INTO asociations (first_id, second_id, depth) SELECT first_id, nid, depth + 1 FROM asociations WHERE second_id = np; FOR temp2 IN SELECT second_id, depth FROM asociations WHERE first_id = nid AND depth <> 0 LOOP INSERT INTO asociations (first_id, second_id, depth) SELECT first_id, temp2.second_id, depth + temp2.depth FROM asociations WHERE second_id = nid AND depth <> 0; END LOOP; RETURN true; END; ' LANGUAGE plpgsql; -- mknode DROP FUNCTION mknode (character varying, bigint); CREATE FUNCTION mknode (character varying, bigint) RETURNS BOOLEAN AS ' DECLARE nn ALIAS FOR $1; np ALIAS FOR $2; BEGIN INSERT INTO nodes (name, parent) VALUES (nn, np); INSERT INTO asociations (first_id, second_id, depth) SELECT first_id, (SELECT id FROM nodes WHERE name = nn AND parent = np), depth + 1 FROM asociations WHERE second_id = np; INSERT INTO asociations (first_id, second_id, depth) VALUES((SELECT id FROM nodes WHERE name = nn AND parent = np), (SELECT id FROM nodes WHERE name = nn AND parent = np), 0); RETURN true; END; ' LANGUAGE plpgsql; -- mkcontent DROP FUNCTION mkcontent (bigint); CREATE FUNCTION mkcontent (bigint) RETURNS bigint AS ' DECLARE id ALIAS FOR $1; record RECORD; BEGIN SELECT lastvalue AS id INTO record FROM contents_increment; INSERT INTO contents ( id, content ) VALUES ( record.id, cnt ); INSERT INTO binds ( node_id, content_id ) VALUES ( id, record.id ); RETURN true; END; ' LANGUAGE plpgsql; -- link DROP FUNCTION link (bigint, bigint); CREATE FUNCTION link (bigint, bigint) RETURNS BOOLEAN AS ' INSERT INTO binds (content_id, node_id) VALUES ($1, $2); SELECT TRUE; ' LANGUAGE sql; -- rm DROP FUNCTION rm (bigint); CREATE FUNCTION rm (bigint) RETURNS BOOLEAN AS ' DELETE FROM nodes WHERE id = $1; SELECT TRUE; ' LANGUAGE sql;
Polecam wszystkim, bo mi naprawdę szkoda, że moja praca się marnuje a sam nie mam co z tym zrobić.
Jeżeli jest odpowiedniejsze forum to proszę o przeniesienie.
Ojojoj, wyszedłem poza limit długości posta.
Teraz jeszcze jakby jacyś linuksiarze z Was chcieli potestować i potrzebowali danych testowych to polecam:
Kod
#include<stdlib.h>
#include<errno.h>
#include<dirent.h>
#include<stdio.h>
#include<fcntl.h>
#include<sys/stat.h>
#include<sys/types.h>
#define FALSE 0
#define TRUE !FALSE
int id;
int fid;
void *xmalloc(size_t size)
{
void *ptr;
if((ptr=malloc(size))==NULL)
syserr("malloc");
memset(ptr, 0, size);
return ptr;
}
void xfree(void *ptr)
{
if(ptr)
free(ptr);
}
void writeFile(const char *path)
{
FILE *fp;
int c;
if((fp=fopen(path, "r"))==NULL)
syserr("fopen");
while((c=fgetc(fp))!=EOF)
{
if(c=='\\'||c=='\'')
printf("%c", '\\');
printf("%c", c);
}
if(fclose(fp)!=0)
syserr("fclose");
}
int isRegular(const char *path)
{
struct stat st;
if( stat( path, &st ) == -1 )
return FALSE;
return ( S_ISREG( st.st_mode ) );
}
int isDir(const char *path)
{
struct stat st;
if( stat( path, &st ) == -1 )
return FALSE;
return ( S_ISDIR( st.st_mode ) );
}
unsigned long int insertDir(const char *name, const int pid)
{
extern int id;
id++;
printf("INSERT INTO nodes ( id, name, parent) VALUES ( %d, '%s', %d);\n", id, name, pid);
printf("INSERT INTO asociations ( first_id, second_id, depth ) SELECT first_id, %d, depth + 1 FROM asociations WHERE second_id = %d;\n", id, pid);
printf("INSERT INTO asociations ( first_id, second_id, depth ) VALUES ( %d, %d, 0 );\n", id, id);
return id;
}
void insertFile(const char *path, const char *name, const int pid)
{
extern int id;
extern int fid;
id++;
fid++;
printf("INSERT INTO nodes ( id, name, parent) VALUES ( %d, '%s', %d );\n", id, name, pid);
printf("INSERT INTO asociations ( first_id, second_id, depth ) SELECT first_id, %d, depth + 1 FROM asociations WHERE second_id = %d;\n", id, pid);
printf("INSERT INTO asociations ( first_id, second_id, depth ) VALUES ( %d, %d, 0 );\n", id, id);
printf("INSERT INTO contents ( id, content ) VALUES ( %d, '", fid);
writeFile(path);
printf("' );\n");
printf("INSERT INTO binds (node_id, content_id) VALUES (%d, %d);", id, fid);
}
int insert(const char *path, const int pid)
{
DIR *dir;
struct dirent *dent;
char *nodepath;
unsigned long int npid;
if((dir=opendir(path))==NULL)
return TRUE;
while((dent=readdir(dir))!=NULL)
{
if((strcmp(dent->d_name, ".")==0)||(strcmp(dent->d_name,"..")==0)||(strcmp(dent->d_name,"dev")==0)||(strcmp( dent->d_name,"proc")==0))
{
continue;
}
nodepath=(char *)xmalloc(strlen(path)+strlen(dent->d_name)+2);
sprintf(nodepath, "%s/%s", path, dent->d_name);
if(isDir(nodepath))
{
npid=insertDir(dent->d_name, pid);
insert(nodepath, npid);
}
else if(isRegular(nodepath))
{
insertFile(nodepath, dent->d_name, pid);
}
xfree(nodepath);
}
if(closedir(dir)==-1)
syserr("closedir");
return TRUE;
}
int main(int argc, char *argv[])
{
extern int id;
extern int fid;
id=1;
fid=0;
if(argc<2)
syserr("argc");
if(insert(argv[1], id)==FALSE)
syserr("insert");
return 0;
}
oraz error.c#include<errno.h>
#include<dirent.h>
#include<stdio.h>
#include<fcntl.h>
#include<sys/stat.h>
#include<sys/types.h>
#define FALSE 0
#define TRUE !FALSE
int id;
int fid;
void *xmalloc(size_t size)
{
void *ptr;
if((ptr=malloc(size))==NULL)
syserr("malloc");
memset(ptr, 0, size);
return ptr;
}
void xfree(void *ptr)
{
if(ptr)
free(ptr);
}
void writeFile(const char *path)
{
FILE *fp;
int c;
if((fp=fopen(path, "r"))==NULL)
syserr("fopen");
while((c=fgetc(fp))!=EOF)
{
if(c=='\\'||c=='\'')
printf("%c", '\\');
printf("%c", c);
}
if(fclose(fp)!=0)
syserr("fclose");
}
int isRegular(const char *path)
{
struct stat st;
if( stat( path, &st ) == -1 )
return FALSE;
return ( S_ISREG( st.st_mode ) );
}
int isDir(const char *path)
{
struct stat st;
if( stat( path, &st ) == -1 )
return FALSE;
return ( S_ISDIR( st.st_mode ) );
}
unsigned long int insertDir(const char *name, const int pid)
{
extern int id;
id++;
printf("INSERT INTO nodes ( id, name, parent) VALUES ( %d, '%s', %d);\n", id, name, pid);
printf("INSERT INTO asociations ( first_id, second_id, depth ) SELECT first_id, %d, depth + 1 FROM asociations WHERE second_id = %d;\n", id, pid);
printf("INSERT INTO asociations ( first_id, second_id, depth ) VALUES ( %d, %d, 0 );\n", id, id);
return id;
}
void insertFile(const char *path, const char *name, const int pid)
{
extern int id;
extern int fid;
id++;
fid++;
printf("INSERT INTO nodes ( id, name, parent) VALUES ( %d, '%s', %d );\n", id, name, pid);
printf("INSERT INTO asociations ( first_id, second_id, depth ) SELECT first_id, %d, depth + 1 FROM asociations WHERE second_id = %d;\n", id, pid);
printf("INSERT INTO asociations ( first_id, second_id, depth ) VALUES ( %d, %d, 0 );\n", id, id);
printf("INSERT INTO contents ( id, content ) VALUES ( %d, '", fid);
writeFile(path);
printf("' );\n");
printf("INSERT INTO binds (node_id, content_id) VALUES (%d, %d);", id, fid);
}
int insert(const char *path, const int pid)
{
DIR *dir;
struct dirent *dent;
char *nodepath;
unsigned long int npid;
if((dir=opendir(path))==NULL)
return TRUE;
while((dent=readdir(dir))!=NULL)
{
if((strcmp(dent->d_name, ".")==0)||(strcmp(dent->d_name,"..")==0)||(strcmp(dent->d_name,"dev")==0)||(strcmp( dent->d_name,"proc")==0))
{
continue;
}
nodepath=(char *)xmalloc(strlen(path)+strlen(dent->d_name)+2);
sprintf(nodepath, "%s/%s", path, dent->d_name);
if(isDir(nodepath))
{
npid=insertDir(dent->d_name, pid);
insert(nodepath, npid);
}
else if(isRegular(nodepath))
{
insertFile(nodepath, dent->d_name, pid);
}
xfree(nodepath);
}
if(closedir(dir)==-1)
syserr("closedir");
return TRUE;
}
int main(int argc, char *argv[])
{
extern int id;
extern int fid;
id=1;
fid=0;
if(argc<2)
syserr("argc");
if(insert(argv[1], id)==FALSE)
syserr("insert");
return 0;
}
Kod
#include<stdio.h>
#include<errno.h>
#include<fcntl.h>
void syserr(char *msg)
{
fprintf(stderr, "ERROR: %s ( %d", msg, errno);
if(errno>0 && errno<sys_nerr)
fprintf(stderr, "; %s )\n", strerror(errno));
else
fprintf(stderr, " )\n");
exit(1);
}
Mam nadzieję, że nie muszę dodawać, że framework jest na pgsql (choć łatwo go przerobić, bo pgsql jest prostym językiem).#include<errno.h>
#include<fcntl.h>
void syserr(char *msg)
{
fprintf(stderr, "ERROR: %s ( %d", msg, errno);
if(errno>0 && errno<sys_nerr)
fprintf(stderr, "; %s )\n", strerror(errno));
else
fprintf(stderr, " )\n");
exit(1);
}
I mam też nadzieję, że nie muszę dodawać, że program kompiluje się
Kod
gcc -o pgins pgins.c error.c
a używaKod
./pgins /katalog/do/insertowania | insert.sql