From f0222ef00495b9afd8dd4478163484498c6f8260 Mon Sep 17 00:00:00 2001 From: zzandy Date: Thu, 11 Jun 2009 20:20:04 +0000 Subject: [PATCH] Added AltSpider project to implement MySQL-based view enabled solution with different table schema. git-svn-id: https://abrams/svn/LanSpider-repo@23 16f2e333-51b0-4855-8c5a-a66b7be24171 --- src/AltSpider/AltSpider.sln | 20 +++ src/AltSpider/AltSpider/AltModel.cs | 37 +++++ src/AltSpider/AltSpider/AltSpider.csproj | 60 ++++++++ src/AltSpider/AltSpider/Program.cs | 16 +++ src/AltSpider/AltSpider/Properties/AssemblyInfo.cs | 36 +++++ src/AltSpider/Resources/shares.sql | 158 +++++++++++++++++++++ 6 files changed, 327 insertions(+) create mode 100644 src/AltSpider/AltSpider.sln create mode 100644 src/AltSpider/AltSpider/AltModel.cs create mode 100644 src/AltSpider/AltSpider/AltSpider.csproj create mode 100644 src/AltSpider/AltSpider/Program.cs create mode 100644 src/AltSpider/AltSpider/Properties/AssemblyInfo.cs create mode 100644 src/AltSpider/Resources/shares.sql diff --git a/src/AltSpider/AltSpider.sln b/src/AltSpider/AltSpider.sln new file mode 100644 index 0000000..33de294 --- /dev/null +++ b/src/AltSpider/AltSpider.sln @@ -0,0 +1,20 @@ + +Microsoft Visual Studio Solution File, Format Version 10.00 +# Visual Studio 2008 +Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "AltSpider", "AltSpider\AltSpider.csproj", "{CA1574B4-F801-46D6-8ADD-0CD2FE157221}" +EndProject +Global + GlobalSection(SolutionConfigurationPlatforms) = preSolution + Debug|Any CPU = Debug|Any CPU + Release|Any CPU = Release|Any CPU + EndGlobalSection + GlobalSection(ProjectConfigurationPlatforms) = postSolution + {CA1574B4-F801-46D6-8ADD-0CD2FE157221}.Debug|Any CPU.ActiveCfg = Debug|Any CPU + {CA1574B4-F801-46D6-8ADD-0CD2FE157221}.Debug|Any CPU.Build.0 = Debug|Any CPU + {CA1574B4-F801-46D6-8ADD-0CD2FE157221}.Release|Any CPU.ActiveCfg = Release|Any CPU + {CA1574B4-F801-46D6-8ADD-0CD2FE157221}.Release|Any CPU.Build.0 = Release|Any CPU + EndGlobalSection + GlobalSection(SolutionProperties) = preSolution + HideSolutionNode = FALSE + EndGlobalSection +EndGlobal diff --git a/src/AltSpider/AltSpider/AltModel.cs b/src/AltSpider/AltSpider/AltModel.cs new file mode 100644 index 0000000..f492f41 --- /dev/null +++ b/src/AltSpider/AltSpider/AltModel.cs @@ -0,0 +1,37 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace AltSpider +{ + class AltModel + { + + public void SavePath(string path) + { + var parts = path.Split( new []{'/'}, StringSplitOptions.RemoveEmptyEntries ); + + int parentid = 1; + foreach ( string part in parts ) + { + parentid=SaveNode(part, parentid); + } + + } + + private int SaveNode( string part, int parentid ) + { + int nodeid = GetNode( part, parentid ); + if(nodeid == 0) + { + + } + } + + private int GetNode( string part, int parentid ) + { + throw new NotImplementedException(); + } + } +} diff --git a/src/AltSpider/AltSpider/AltSpider.csproj b/src/AltSpider/AltSpider/AltSpider.csproj new file mode 100644 index 0000000..247a788 --- /dev/null +++ b/src/AltSpider/AltSpider/AltSpider.csproj @@ -0,0 +1,60 @@ + + + + Debug + AnyCPU + 9.0.21022 + 2.0 + {CA1574B4-F801-46D6-8ADD-0CD2FE157221} + Exe + Properties + AltSpider + AltSpider + v3.5 + 512 + + + true + full + false + bin\Debug\ + DEBUG;TRACE + prompt + 4 + + + pdbonly + true + bin\Release\ + TRACE + prompt + 4 + + + + + 3.5 + + + 3.5 + + + 3.5 + + + + + + + + + + + + \ No newline at end of file diff --git a/src/AltSpider/AltSpider/Program.cs b/src/AltSpider/AltSpider/Program.cs new file mode 100644 index 0000000..dccbc1a --- /dev/null +++ b/src/AltSpider/AltSpider/Program.cs @@ -0,0 +1,16 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace AltSpider +{ + class Program + { + static void Main(string[] args) + { + var a = new AltModel(); + a.SavePath( "//shell/be/some/path.hpp" ); + } + } +} diff --git a/src/AltSpider/AltSpider/Properties/AssemblyInfo.cs b/src/AltSpider/AltSpider/Properties/AssemblyInfo.cs new file mode 100644 index 0000000..4f02037 --- /dev/null +++ b/src/AltSpider/AltSpider/Properties/AssemblyInfo.cs @@ -0,0 +1,36 @@ +using System.Reflection; +using System.Runtime.CompilerServices; +using System.Runtime.InteropServices; + +// General Information about an assembly is controlled through the following +// set of attributes. Change these attribute values to modify the information +// associated with an assembly. +[assembly: AssemblyTitle("AltSpider")] +[assembly: AssemblyDescription("")] +[assembly: AssemblyConfiguration("")] +[assembly: AssemblyCompany("")] +[assembly: AssemblyProduct("AltSpider")] +[assembly: AssemblyCopyright("Copyright © 2009")] +[assembly: AssemblyTrademark("")] +[assembly: AssemblyCulture("")] + +// Setting ComVisible to false makes the types in this assembly not visible +// to COM components. If you need to access a type in this assembly from +// COM, set the ComVisible attribute to true on that type. +[assembly: ComVisible(false)] + +// The following GUID is for the ID of the typelib if this project is exposed to COM +[assembly: Guid("d08ab11a-22e2-47fc-b9ba-f2a8cc59b3e9")] + +// Version information for an assembly consists of the following four values: +// +// Major Version +// Minor Version +// Build Number +// Revision +// +// You can specify all the values or you can default the Build and Revision Numbers +// by using the '*' as shown below: +// [assembly: AssemblyVersion("1.0.*")] +[assembly: AssemblyVersion("1.0.0.0")] +[assembly: AssemblyFileVersion("1.0.0.0")] diff --git a/src/AltSpider/Resources/shares.sql b/src/AltSpider/Resources/shares.sql new file mode 100644 index 0000000..cca1959 --- /dev/null +++ b/src/AltSpider/Resources/shares.sql @@ -0,0 +1,158 @@ + +-- +-- Database structure for database 'share_index' +-- + + +DROP DATABASE IF EXISTS `share_index`; +CREATE DATABASE IF NOT EXISTS `share_index` DEFAULT CHARACTER SET utf8 ; + +USE `share_index`; + +-- +-- Table structure for table 'node' +-- + +CREATE TABLE IF NOT EXISTS `node` ( + `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `parent_id` INT(10) UNSIGNED DEFAULT NULL, + `lvl` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0', + `name` VARCHAR(244) NOT NULL, + `modified_on` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', + `created_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`id`), + UNIQUE KEY `unique` (`parent_id`,`name`), + FOREIGN KEY (`parent_id`) REFERENCES `node` (`id`) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB; + +CREATE TRIGGER `tr_node_after_update` + BEFORE UPDATE ON `node` FOR EACH ROW + SET NEW.modified_on = CURRENT_TIMESTAMP; + +-- +-- Dumping data for table 'node' +-- + +LOCK TABLES `node` WRITE; + +INSERT INTO `node` (`id`, `parent_id`, `lvl`, `name`, `modified_on`, `created_on`) VALUES + ('1',NULL,0,'root',CURRENT_TIMESTAMP,CURRENT_TIMESTAMP); + +UNLOCK TABLES; + + +-- +-- Table structure for table 'relations' +-- + +CREATE TABLE IF NOT EXISTS `relations` ( + `child_id` INT(10) UNSIGNED NOT NULL, + `parent_id` INT(3) UNSIGNED NOT NULL, + PRIMARY KEY (`child_id`, `parent_id`), + FOREIGN KEY (`child_id`) REFERENCES `node` (`id`) ON UPDATE CASCADE ON DELETE CASCADE, + FOREIGN KEY (`parent_id`) REFERENCES `node` (`id`) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB; + + +-- +-- Table structure for table 'file_info' +-- + +CREATE TABLE IF NOT EXISTS `file_info` ( + `node_id` INT(10) UNSIGNED NOT NULL, + `file_size` BIGINT(20) UNSIGNED DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `uniq_file_info` (`node_id`), + FOREIGN KEY (`node_id`) REFERENCES `node` (`id`) ON UPDATE CASCADE ON DELETE CASCADE +) ENGINE=InnoDB; + + +-- Reset data + +DELETE FROM `file_info`; +DELETE FROM `node` WHERE `name` != 'root'; +DELETE FROM `relations`; + +DROP PROCEDURE IF EXISTS `proc_add_node`; + +DELIMITER // + +CREATE PROCEDURE `proc_add_node` ( name VARCHAR(244), parent_id INT, OUT new_id INT ) +BEGIN + SELECT lvl + 1 INTO @newlvl FROM node WHERE id = parent_id; + + INSERT INTO node (name, parent_id, lvl, modified_on) + VALUES (name, parent_id, @newlvl, CURRENT_TIMESTAMP); + + SET new_id = @@identity; + + WHILE parent_id IS NOT NULL DO + INSERT INTO relations VALUES (new_id, parent_id); + SELECT parent_id INTO parent_id FROM node WHERE id = parent_id; + END WHILE; +END// + +DELIMITER ; + + +DROP PROCEDURE IF EXISTS proc_get_path; + +DELIMITER // + +CREATE PROCEDURE proc_get_path ( id INT, OUT path VARCHAR (266) ) +BEGIN + +END// + +DELIMITER ; + +DROP FUNCTION IF EXISTS get_path; + +DELIMITER // + +CREATE FUNCTION get_path(node_id INT) +RETURNS VARCHAR(266) +READS SQL DATA +BEGIN + SELECT CONCAT('/', name) INTO @val FROM node WHERE id = node_id; + + SELECT IFNULL(CONCAT('/', GROUP_CONCAT(parent.name SEPARATOR '/'), @val), @val) + INTO @val + FROM relations + INNER JOIN node AS parent ON relations.parent_id = parent.id + WHERE relations.child_id = node_id AND parent.parent_id IS NOT NULL + ORDER BY parent.lvl DESC; + + RETURN @val; +END// + +DELIMITER ; + +DROP VIEW IF EXISTS `view_file_system`; + +CREATE VIEW `view_file_system` AS +SELECT id, name, get_path(id) as path +, (SELECT COUNT(42) FROM relations WHERE parent_id = id) AS children +, (SELECT COUNT(42) FROM relations WHERE child_id = id) AS parents +FROM node; + + +SET @id = 0; + +CALL proc_add_node ( 'windows', 1, @id ); +CALL proc_add_node ( 'system32', @id, @id ); +CALL proc_add_node ( 'driver', @id, @id ); +CALL proc_add_node ( 'etc', @id, @id ); + +CALL proc_add_node ( 'documents and settings', 1, @id ); +CALL proc_add_node ( 'alpha', @id, @id ); +CALL proc_add_node ( 'roaming data', @id, @id ); +CALL proc_add_node ( 'mycorp', @id, @id ); + +-- TODO: CALL proc_add_path ( 'windows/system32/drivers/etc/hosts' ); + + +SELECt * FROM `view_file_system`; +SELECT COUNT(1) FROM relations; +DELETE FROM node WHERE id = 7; +SELECT COUNT(1) FROM relations; -- 2.11.4.GIT