sql/0040755000076400001440000000000007355027726010716 5ustar enderuserssql/logs.sql0100644000076400001440000000421707343615704012400 0ustar enderuserscreate sequence t_gdn_log_id_seq; create view gdn_log_id_seq as select nextval('t_gdn_log_id_seq') as nextval; create table gdn_logs ( log_id integer constraint gdn_logs_pk primary key, log_date timestamp default now() not null, log_level varchar(20) not null constraint gdn_logs_log_level_ck check (log_level in ('notice', 'warn', 'error', 'debug')), log_key varchar(100) not null, message text not null ); create function gdn_log__notice (varchar,varchar) returns integer as ' declare notice__log_key alias for $1; notice__message alias for $2; begin insert into gdn_logs (log_id, log_level, log_key, message) values (gdn_log_id_seq.nextval, ''notice'', notice__log_key, notice__message); return 0; end;' language 'plpgsql'; -- procedure warn create function gdn_log__warn (varchar,varchar) returns integer as ' declare warn__log_key alias for $1; warn__message alias for $2; begin insert into gdn_logs (log_id, log_level, log_key, message) values (gdn_log_id_seq.nextval, ''warn'', warn__log_key, warn__message); return 0; end;' language 'plpgsql'; -- procedure error create function gdn_log__error (varchar,varchar) returns integer as ' declare error__log_key alias for $1; error__message alias for $2; begin insert into gdn_logs (log_id, log_level, log_key, message) values (gdn_log_id_seq.nextval, ''error'', error__log_key, error__message); return 0; end;' language 'plpgsql'; -- procedure debug create function gdn_log__debug (varchar,varchar) returns integer as ' declare debug__log_key alias for $1; debug__message alias for $2; begin insert into gdn_logs (log_id, log_level, log_key, message) values (gdn_log_id_seq.nextval, ''debug'', debug__log_key, debug__message); return 0; end;' language 'plpgsql'; sql/package-model.sql0100644000076400001450000000556707353675100013566 0ustar endercvs /* Begin Package Specific Info */ create table gdn_package_states( state_id serial, name varchar(30) ); -- api create table gdn_packages ( package_id serial, gdn_uri varchar(1000), name varchar(100), description text, summary varchar(2000), url varchar(300), state integer references gdn_package_states(state_id), approved_p boolean not null default 'f', verified_p boolean not null default 'f', -- crypto last_modified_by integer references gdn_user(user_id), last_modified_date timestamp default 'now' not null, last_modified_ip char(20) ); create table gdn_packages_audit ( package_id integer references gdn_packages(package_id), last_modified_by integer, last_modified_date timestamp, last_modified_ip char(20), name varchar(100), state integer, verified_p boolean, ); -- A trigger is used to move information from the main table to -- the audit table create function gdn_packages_audit_tr () returns opaque as ' begin insert into gdn_packages_audit ( package_id, last_modified, last_modifying_user, modifying_ip, name, state, verified_p ) values ( old.package_id, old.last_modified, old.last_modifying_user, old.modified_ip_address old.name, old.state, old.verified_p ); return new; end;' language 'plpgsql'; create trigger gdn_packages_audit_tr after update or delete on gdn_packages for each row execute procedure gdn_packags_audit_tr (); create table gdn_package_keys( key_id serial, key varchar(100) ); create table gdn_package_key_map( package_id integer references gdn_packages(package_id), key_id integer references gdn_package_keys(key_id) ); create unique index on gdn_package_key_map(package_id, key_id); -- packages may have more than 1 author create table gdn_author_pkg_map ( package_id integer references gdn_packages(package_id), author_id integer references gdn_authors(author_id) ); create unique index gdn_author_pkg_map_idx on gdn_author_pkg_map(package_id, author_id); create table gdn_vendor_pkg_map ( package_id integer references gdn_packages(package_id), vendor_id integer references gdn_vendors(vendor_id) ); create unique index gdn_vendor_pkg_map_idx on gdn_vendor_pkg_map(package_id, vendor_id); create table gdn_licenses ( license_id serial, license_name varchar(500) unique not null ); -- packages may have 1 or more licenses -- we don't track license changes on versions... create table gdn_license_pkg_map ( license_id int4 references gdn_licenses(license_id), package_id int4 references gdn_packages(package_id) ); create unique index gdn_license_pkg_map_idx on gdn_license_pkg_map(license_id, package_id); /* end package model */ /* begin package data */ sql/dependencies-model.sql0100644000076400001440000000503607353203341015147 0ustar enderusers -- utilizing the naming convention of debian -- see deb man file section 5 -- requires, suggests, conflicts, etc. create table gdn_dependency_types ( dependency_type_id serial, type_name varchar(30) ); BEGIN insert into gdn_dependency_types (type_name) values ('REQUIRES'); insert into gdn_dependency_types (type_name) values ('SUGGESTS'); insert into gdn_dependency_types (type_name) values ('CONFLICTS'); insert into gdn_dependency_types (type_name) values ('OVERWRITES'); insert into gdn_dependency_types (type_name) values ('EXTERNAL'); END; create table gdn_dependencies ( dependency_id serial, dependency_type varchar(30) references gdn_depedency_types(type_name), version_id integer references gdn_versions(version_id), ); -- directed edges of a DAG create table gdn_dependency_map ( -- the version that created the dependency -- ?? base on package?? dependent_id int4 references gdn_versions(version_id) not null, -- the package that fufills the dependency dependency_id int4 references gdn_packages(package_id), -- external depedencies dispute -- the version of the dependent package dependency_version integer references gdn_versions(version_id), -- the type of dependency debian style dependency_type_id int4 references gdn_depedency_types(dependency_type_id) not null, -- the type of the dependent package, gideon does not manage ext depends -- they must be fufilled by the installer/adminstrator dependency_kind varchar(6) check dependency_type in ('python', 'ext'), ); -- BELOW IS OBSOLETE -- need to do tree lookup with sort keys... sigh, -- sometimes i really like oracle -- which begs the question are dependencies just trees or -- really DAGs. create table gdn_dependencies ( dependency_id serial, dependency_type_id int4 references gdn_depedency_types(dependency_type_id) not null, package_id int4 references gdn_packages(package_id) not null, package_version int4 references gdn_versions(version_id) not null, dpackage_id int4 references gdn_packages(package_id) not null, dpackage_version int4 references gdn_versions(version_id) not null, ); -- non python dependencies that we can't resolve -- automatically create table gdn_ext_dependencies ( ext_dependency_id serial dependency_type_id int4 references gdn_depedency_types(depedency_type_id), dependency_name varchar(300) not null, dependency_url varchar(300) ); */ sql/gideon-create.sql0100644000076400001440000000030107343617463014134 0ustar enderusersDROP DATABASE gideon CREATE DATABASE gideon \! createlang plpgsql gideon \i gideon-logs.sql \i community-model.sql \i package-model.sql --\i rating-model.sql \i categories-model.sql sql/version-model.sql0100644000076400001450000000573207352476207013660 0ustar endercvs/* Begin Package Version MetaData */ -- now we get to more interesting things -- versions contain a packages dependencies -- files, and platforms -- api -- gdn_version__create -- gdn_version__change_state -- gdn_version__edit -- gdn_version__add_platform -- gdn_version__remove_platform -- gdn_version__create_python_version create table gdn_versions ( version_id serial, version_name varchar(20) not null, package_id int4 constraint gdn_versions_pkg_fk references gdn_packages(package_id) not null, creation_date date default 'now', release_date date default 'now', approval_date date, approved_by integer constraint gdn_versions_apprvd_user_fk references gdn_users(user_id) ); create unique index gdn_versions_idx(version_id, package_id); create table gdn_platforms ( platform_id serial, platform_name varchar(50), platform_version varchar(10) ); create table gdn_python_versions ( pyversion_id serial, major int4 not null, minor int4 not null, patch int4 not null, prerelease varchar(5), sort_key integer not null ); create unique index gdn_python_versions_unq_idx on gdn_python_versions(major, minor, patch, prerelease); create table gdn_version_python_version_map ( pyversion_id int4 references gdn_python_versions(pyversion_id), version_id int4 references gdn_versions(version_id) ); /* -- platforms should be tied to version -- not packages. create table gdn_platforms_pkg_map ( platform_id int4 references gdn_platforms(platform_id), package_id int4 references gdn_packages(package_id) ); */ -- don't worry we assume that a version will -- use the same platforms as an older version -- unless otherwise changed. /* actually platforms can be derived from the files uploaded with a package... a package can be uploaded as several different binary/source distrubitions zipped up with the addition of an additional metadata file create table gdn_platforms_version_map ( platform_id int4 references gdn_platforms(platform_id), package_id int4 references gdn_packages(package_id) ); */ /* End version model */ /* Begin Version data */ insert into gdn_python_versions (major, minor, patch, sort_key) values (1, 5, 2, 10); insert into gdn_python_versions (major, minor, patch, sort_key) values (1, 6, 0, 20); insert into gdn_python_versions (major, minor, patch, sort_key) values (1, 6, 1, 30); insert into gdn_python_versions (major, minor, patch, sort_key) values (2, 0, 0, 40); insert into gdn_python_versions (major, minor, patch, sort_key) values (2, 0, 1, 50); insert into gdn_python_versions (major, minor, patch, sort_key) values (2, 0, 1, 60); insert into gdn_python_versions (major, minor, patch, sort_key) values (2, 1, 0, 70); insert into gdn_python_versions (major, minor, patch, sort_key) values (2, 1, 1, 80); sql/categories-model.sql0100644000076400001450000000717207354050112014301 0ustar endercvs /* -- was this part of the pep 243 create table gdn_keywords ( keyword_id serial, keyword varchar(20) not null ); create table gdn_keyword_pkg_map ( keyword_id int4 references gdn_keywords(keyword_id), package_id int4 references gdn_packages(package_id) ); create unique index gdn_keyword_pkg_map_idx on gdn_keyword_pkg_map(keyword_id, package_id); */ create table gdn_categories ( category_id serial, category varchar(50) not null, parent_id integer constraint gdn_categories_parent_id_fk references gdn_categories (category_id), constraint gdn_categories_unq unique (parent_id, category), -- is it legal to create a sub categories subcategories_p boolean not null, tree_sortkey varchar(4000) ); create index gdn_categories_tree_skey_idx on gdn_categories(tree_sortkey); create table gdn_package_category_map ( category_id int4 references gdn_categories(category_id), package_id int4 references gdn_packages(package_id) ); create function gdn_category_insert_tr () returns opaque as ' declare v_parent_sk varchar; max_key varchar; begin if new.parent_id is null then select max(tree_sortkey) into max_key from gdn_categories where parent_id is null; v_parent_sk := ''''; else select max(tree_sortkey) into max_key from gdn_categories where parent_id = new.parent_id; select coalesce(max(tree_sortkey),'''') into v_parent_sk from gdn_categories where category_id = new.parent_id; end if; new.tree_sortkey := v_parent_sk || ''/'' || tree_next_key(max_key); return new; end;' language 'plpgsql'; create trigger gdn_categories_insert_tr before insert on gdn_categories for each row execute procedure gdn_categories_insert_tr (); create function gdn_categories_update_tr () returns opaque as ' declare v_parent_sk varchar; max_key varchar; p_id integer; v_rec record; clr_keys_p boolean default ''t''; begin if new.category_id = old.category_id and ((new.parent_id = old.parent_id) or (new.parent_id is null and old.parent_id is null)) then return new; end if; for v_rec in select category_id from gdn_categories where tree_sortkey like new.tree_sortkey || ''%'' order by tree_sortkey LOOP if clr_keys_p then update gdn_categories set tree_sortkey = null where tree_sortkey like new.tree_sortkey || ''%''; clr_keys_p := ''f''; end if; select parent_id into p_id from gdn_categories where category_id = v_rec.category_id; if p_id is null then select max(tree_sortkey) into max_key from gdn_categories where parent_id = p_id; select coalesce(max(tree_sortkey),'''') into v_parent_sk from gdn_categories where category_id = p_id; end if; update gdn_categories set tree_sortkey = v_parent_sk || ''/'' || tree_next_key(max_key) where category_id = v_rec.category_id; end LOOP; return new; end;' language 'plpgsql'; create trigger gdn_categories_update_tr after update on gdn_categories for each row execute procedure gdn_categories_update_tr (); sql/system-core.sql0100644000076400001440000000056307332372737013712 0ustar enderusers -- system wide configuration settings create table gdn_system ( name varchar(100) unique, value varchar(1000) ); -- applicable to both a package object and -- a package version. -- states might include uploaded, published, etc, -- possibly special state designations for mirroring create table gdn_states ( state_id serial, state varchar(20) );sql/views.sql0100644000076400001450000000004507352561101012206 0ustar endercvs# active packages # review queue # sql/NOTES0100644000076400001440000000070607355030067011521 0ustar enderusers == update == - rather discontent with this - need to simplify and rewrite some of this - after finishing the metadata model ============ category model finish triggers test triggers osd makes version assumptions we can't assume like triple integer element tuple so we use date for sorting versions file model download count package-model - add license data - fix package key redundancy - add in some generic vendors like python sigs sql/license.txt0100644000076400001450000000011707355027726012530 0ustar endercvsauthor: kapil thangavelu license: GPL cvs: $Id: $sql/community-model.sql0100644000076400001440000000254707345313761014562 0ustar enderusers /* Begin Community Model */ create table gdn_persons ( person_id serial, first_name varchar(200) not null, last_name varchar(200) not null, middle_name varchar(200), public_email varchar(300), private_email varchar(300) not null, home_url varchar(300), public_key text ); create table gdn_users ( user_id integer constraint gdn_user_person_fk references gdn_persons(person_id), user_name varchar(100), creation_date date default 'now', creation_ip varchar(16), active_p boolean not null default 't', password varchar(200) ); -- authors/maintainers are different logicaly from -- community users AND those who upload. create table gdn_authors ( author_id int4 constraint gdn_authors_person_fk references gdn_persons(person_id), contact_info varchar(300) ); create table gdn_vendors ( vendor_id serial, name varchar(1000), url varchar(300) ); -- want to be able to allow trusted users to -- take on adminstrative work create table gdn_user_roles ( role_id serial, role_name varchar(20) ); create table gdn_user_role_map ( user_id integer references gdn_persons(person_id), role_id int4 references gdn_user_roles(role_id) ); /* End Community Model */ sql/subscriptions.sql0100644000076400001450000000200407353203616013762 0ustar endercvs create table gdn_subscriptions subcription_id serial, user_id integer references gdn_users(user_id), subscription_type varchar(10) references gdn_subscription_types(sub_type), subscription_object integer not null, frequency integer, last_delivered date, active_p boolean default 't' not null ); -- create comment on column gdn_subscriptions.frequency ' create table gdn_subscription_types ( sub_type char(10) ); -- get updates on new versions of a package insert into gdn_subscription_types ( sub_type ) values ( 'package'); -- get updates on new packages/versions within -- this category/subcat insert into gdn_subscription_types ( sub_type ) values ( 'category'); -- get updates on new packages/version from this -- author insert into gdn_subscription_types ( sub_type ) values ( 'author'); -- recieve updates on all new packages/versions insert into gdn_subscription_types ( sub_type ) values ( 'site'); sql/files-model.sql0100644000076400001450000000343307355027514013265 0ustar endercvs-- file types... why besides platform.. what about docs. -- when in doubt treat package as atomic unit/object -- source is a platform -- relative to the server root or abs -- mirroring strategies create table gdn_file_types ( key varchar(50) constraint gdn_file_types_pk primary key, name varchar(100) ); insert into gdn_file_types (key, name) values ('documentation', 'Package Documentation'); insert into gdn_file_types (key, name) values ('signature', 'File Signature'); insert into gdn_file_types (key, name) values ('checksum', 'File Checksum'); insert into gdn_file_types (key, name) values ('metadata', 'Package Metadata'); insert into gdn_file_types (key, name) values ('distribution', 'Package Distribution') -- NEED To add platform info for os specific distribution files... -- gideon generates xml meta data files -- for each package/version, for interaction -- with a client. these are stored on the fs for -- faster serving. -- not all files are tied to versions, some like documentation -- might require their own versioning... sigh. -- lets take the simplest approach -- version files consist -- of distribution files for that version in some archive format -- either zip, tar.gz, tgz (tar.bz2 not being useable on win32?) -- of a metadata file -- of optionally a signed checksum. create table gdn_files ( file_id serial, version_id integer constraint gdn_files_version_fk references gdn_versions(version_id), platform_id integer constraint gdn_files_platform_fk references gdn_platforms(platform_id), file_size integer, -- in kb file_name varchar(200), file_path varchar(300), file_type varchar(50) references gdn_file_types(key) );