See Databases made easy in the Tutorials section. Once you've been through that and understand the basics you can move across to DBD::CSV which will allow you to work with CSV files in your native filesystem (with some restrictions of course).

I know ,CSV is best storage this topic but , I am try at .txt files.

In Standard-SQL database language, there is the concept of 'schema'. A schema is a grouping of tables inside a database. (This resembles the directory (=folder) of a filesystem enough that in my database I sometimes type 'mkdir' instead of 'create schema').

But your mention of cat.txt, makes me think you actually want to store text (from text files). That's possible but it has little to do with 'folders'.

As always with database design, future queries should be specified, not so much (often vague) ideas about storage.

Does cat.txt also contain table-like data? Or is it free-form text?

yes , For example when create database will create a folder(directories) after , when add create table will create .txt files. Example cat.txt ,dog.txt and bird.txt in Animal folder. And their values(1,lion , 2 ,tiger for cat.txt .. (1 ,pitbull for dog.txt) will be in their txt file.

Here is a test program that I did with DBD::CSV (that was also hippo's plan). I've used /tmp as a 'folder'; you should of course eventually choose a better location.

(It would kind of make sense if CREATE DATABASE did create a directory (=folder) but that wasn't implemented (fortunately). So, directory management has to be done separately.)

#!/bin/env perl
use strict;
use warnings;
use DBI;  

my $db       = "animals";
my $table    = "cat";
my $db_dir   = "/tmp/$db";
my $filename = "$db_dir/$table.txt";

if (! -d $db_dir ) { mkdir $db_dir; }
if (! -d $db_dir ) { die "error: no such directory $db_dir "; }

# copied from the DBD::CSV docs, almost unchanged
my $dbh = DBI->connect (
        "dbi:CSV:f_dir=${db_dir};f_ext=.txt;f_lock=2;" .
        "f_encoding=utf8;csv_eol=\n;csv_sep_char=\t;" .
        "csv_quote_char=\";csv_escape_char=\\;csv_class=Text::CSV_XS;"
+ .
        "csv_null=1") or die $DBI::errstr;

my $sep = "\t";
my $eol = "\n";

if ( ! -e $filename ) {
# $dbh->do ("drop table $table");
  $dbh->do ("create table $table (id integer, name char(64))");
  my $rc = $dbh->do("
    insert into $table values 
        (1, " . $dbh->quote ("lion"         ) . ")
      , (2, " . $dbh->quote ("tiger"        ) . ")
      , (3, " . $dbh->quote ("lynx"         ) . ")
      , (4, " . $dbh->quote ("puma"         ) . ")
      , (5, " . $dbh->quote ("leopard"      ) . ")
      , (6, " . $dbh->quote ("mountain lion") . ")"
  );
}

print "-- retrieving all:\n";
my $sth = $dbh->prepare("select * from $table");
my $rc  = $sth->execute;
while (my $rrow = $sth->fetchrow_arrayref) {
  print $rrow->[0], $sep, $rrow->[1], $eol;
}

print "\n-- retrieving '%lion%':\n";
$sth = $dbh->prepare("select * from $table where name like " . $dbh->q
+uote ("%lion%") . "");
$rc  = $sth->execute;
while (my $rrow = $sth->fetchrow_arrayref) {
  print $rrow->[0], $sep, $rrow->[1], $eol;
}

#my $rowcount = $dbh->selectrow_arrayref("select count(*) from $table 
+where name = " . $dbh->quote ("jaguar") )->[0];
#if ($rowcount == 0)  {
if ($dbh->selectrow_arrayref("select count(*) from $table where name =
+ " . $dbh->quote ("jaguar") )->[0] == 0) {
  print "\n-- inserting 'jaguar':\n";
  $rc = $dbh->do("insert into $table values (7, " . $dbh->quote ("jagu
+ar") . ") ");
  print "   returned [$rc]\n\n";
}
else {
  print "\n-- record 'jaguar' exists, NOT inserting; \n\n";
}

print "-- retrieving again:\n";
$sth = $dbh->prepare("select * from $table");
$rc  = $sth->execute;
while (my $rrow = $sth->fetchrow_arrayref) {
  print $rrow->[0], $sep, $rrow->[1], $eol;
}

print "\n-- deleting 'puma':\n";
$rc = $dbh->do("delete from $table where name = " . $dbh->quote ("puma
+") . "");
print "   returned [$rc]\n\n";

print "-- and retrieving once more:\n";
$sth = $dbh->prepare("select * from $table");
$rc  = $sth->execute;
while (my $rrow = $sth->fetchrow_arrayref) {
  print $rrow->[0], $sep, $rrow->[1], $eol;
}
[download]

# Hope this helps...

(I also made a version with postgres reading an underlying text-file, via postgres' file_fdw, but it depends on postgres and is read-only; it therefore seems less handy although the code is compact enough)

update: Changed to use DBI instead of the explicit use DBD::CSV; added conditional INSERT.

Here is some info to get you started with databases.
Others have pointed you to perl interfaces you can use after you setup your database.

• Install Sqlite for your platform from http://sqlite.org/
• (Recommendation) Install a free SQLITE GUI, such as the one from http://sqlitebrowser.org/
• Create a database using the schema and data below
• Query/delete to your heart's content (Sample cmd-line queries below)

BEGIN TRANSACTION;
CREATE TABLE `animal` (
    `id`    INTEGER UNIQUE,
    `type`    TEXT,
    `name`    TEXT,
    PRIMARY KEY(id)
);
INSERT INTO `animal` VALUES
 (1,'cat','Felix'),
 (2,'cat','Sylvester'),
 (3,'cat','Garfield'),
 (4,'lion','Simba'),
 (5,'lion','Elsa'),
 (6,'tiger','Sher Khan'),
 (7,'tiger','Woods'),
 (8,'dog','Rover');
CREATE INDEX `by-type` ON `animal` (`type` ASC);
COMMIT;

CREATE VIEW Summary AS
  Select type, count(*) 
  FROM animal
  GROUP by type;

CREATE VIEW cats_only AS
  SELECT id,name
  FROM animal
  WHERE type='cat';
[download]

$sqlite3 animal.sqlite '.read animal.sql'
[download]

$ sqlite3 -header -column animal.sqlite 
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> select * from summary;
type        count(*)  
----------  ----------
cat         3         
dog         1         
lion        2         
tiger       2         
sqlite> select * from cats_only;
id          name      
----------  ----------
1           Felix     
2           Sylvester 
3           Garfield  
sqlite> .q
[download]

P.S. I have pointed you toward a traditional "relational" database.

Followers of current technology are proponents of "nosql" databases, such as Elastic Search.

Just a small note about SQLite:

It does not enfoce data types like other SQL databases do. This can be a little bit surprising. See FAQ 3: SQLite lets me insert a string into a database column of type integer!

For client-server SQL databases, see also:

• Re^2: Perl and Database
• Re^2: What is your favourite Linux or cross-platform database?
• Re^5: Trouble Connecting to PostgreSQL with DBD::Pg
• Re: A puzzled garbage character issues by use Perl-DBI
• Re^4: SELECT LAST_INSERT_ID does not work

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

I think you will need to learn SQL for that. For accessing the database, DBI is the common way of doing that.

If you are just starting out, DBD::SQLite is a good database to start with.

I think it is not nessasary database connections for that .

I liked NetWallah's post at Re: perl create database with folder system.
I wrote some Perl code for you below. Run it, play with it. Experimentation is definitely encouraged!

In creating the table, Animal, I let the DB assign a unique ID for each "row" (see code). This is normally a good idea- so good that you should explain why you don't want that. This idea guarantees and unique id for each row even with multiple simultaneous writers. There are some "do" statements, but the normal way using the Perl DBI is to "prepare" an SQL statement and then later execute it with some variables. There are a number of ways to get the results of "execute". I show one of those ways.

The Perl DBI comes standard in many (if not all) distributions. I don't think that you will have to install anything to run the code below. I used to use a Firefox SQLite add-on as a UI but I am hearing that this is no longer supported. Bummer.

Anyway, this code "runs":

#!/usr/bin/perl
use strict;
use warnings;

use DBI;

my $dbfile = 'TestDB.sqlite';  #whatever SQLite DB name you want

# SQLite doesn't have a user name or password, but needs a
# "connection"

my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseError =
+> 1})
    or die "Couldn't connect to database: " . DBI->errstr;

# I put in this DROP so that you can run this code again
# and again while you play with it.

$dbh->do("DROP TABLE IF EXISTS Animal");

$dbh->do("CREATE TABLE Animal
             (  id        integer PRIMARY KEY AUTOINCREMENT,
                Type      varchar(10),  
                Name      varchar(10)        
             );
         "); 

my $add_animal = $dbh->prepare ("INSERT INTO Animal (Type,Name)
                                 VALUES (?,?)"); 
                                
## Make a single "transaction" for all inserts
## This is a huge performance increase if making thousands of inserts
## A DB INSERT is "expensive" due to the booking involved
## A million inserts can be part of a single transaction

$dbh->do("BEGIN");  # start a new transaction for all inserts

while (defined (my $line = <DATA>))
{
   chomp $line;
   my ($type,$name) = split /\|/, $line;
  
   $add_animal->execute($type,$name);
}

$dbh->do("COMMIT"); #massive speed up by making one transaction!!

## An Example of Query by Type

my $query_by_type = $dbh->prepare ("SELECT * FROM Animal
                                    WHERE Type IS ?");

$query_by_type->execute('lion'); 
my $table_ref = $query_by_type->fetchall_arrayref;

foreach my $row (@$table_ref)
{
    print "@$row\n";
}

=Prints:
4 lion Simba
5 lion Elsa
=cut


__DATA__
cat|Felix
cat|Sylvester
cat|Garfield
lion|Simba
lion|Elsa
tiger|Sher Khan
dog|Rover
[download]

My first idea was that you want to implement a database just using the file system.

But now I think you are asking about how to implement tree structures in an SQL table.

This is speculation and we are primarily a Perl board, but table like this should do:

id  parent  value 
1   0       animal
2   1       cat
3   2       lion
4   2       tiger
5   4       Siegfried
6   4       Roy
[download]

edit

For instance the path structure /animal/cat/tiger/Roy is represented in the former table.

There are different ways how to query this, like recursively or using self joins.

You can also use a view based on self join if the tree's depth is fixed in order to visualize the path structure.

It's a good idea to use the pre-order indices as ids; if you also store the maximal descendant id for each node (easily generated in pre-order by using the node's id for leaves, and using the last assigned id for non-leaves) you can easily check for descendants: A is B's descendant iff B.id <= A.id <= B.mdi.

($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord
}map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,
[download]

I think imposing order into numerical ° IDs works only for static structures.

If the max depth is fixed I can easily create a performant view where each column represents a level.

SQL views on self joins are even updatetable in mysql.

edit

Of course there are attempts to use a path like strings as ID, ie 0_1 would be a parent of 0_1_15 , but this becomes a can of worms if you need to move branches ...

Cheers Rolf
_{(addicted to the Perl Programming Language and ☆☆☆☆ :)
Wikisyntax for the Monastery}

°) integers to be more precise

See also NoSQL, CouchDB, MongoDB, MongoDB and DB::CouchDB because of TMTOWTDI.

Best regards, Karl