http://qs321.pair.com?node_id=796175

virtualweb has asked for the wisdom of the Perl Monks concerning the following question:

Hi All: This is a continuation from node "Flock to Rename Directory".
http://perlmonks.org/?node_id=795919

Im a novice and many times I come up with alternatives that are not always the wisest way to go about something..

I need advise regarding how to save large quantity of records,(15.000 or so), that needs to be retrieved and changed often, (every 20 seconds or so), and dont how to install in my Windows PC, test my scripts, and run a proper MySQL or MySQL Lite database for development purposes or later move it to the server for final installation.

If I save each record in a single Flat File, it would need to be opened, read, and rewritten, which I assume is not the fastest technique.. If I save all records in a single flat file, (one record per line), reading the whole file into memory every 20 seconds and change what's needed may not be the best way to go either, (knowing there could be 15.000 lines or more...), so I 'm now contemplating something I read in the book entitled "I didnt know you could do that with perl", a technique of saving records in a flat file with SQL language in my perl script.

Has anyone done this, would this be fast and secure..??

Thanx beforehand
VirtualWeb ====================== ADDENDUM Sep 22nd THanx for all the great suggestions

Replies are listed 'Best First'.
Re: Query Language with Flat Files
by marto (Cardinal) on Sep 18, 2009 at 18:45 UTC

    "and dont how to install in my Windows PC, test my scripts, and run a proper MySQL or MySQL Lite database for development purposes or later move it to the server for final installation."

    Do you mean DBD::SQLite (as suggested in Flock to Rename Directory) rather than MySQL Lite? If so, had you read the description:

    "SQLite is a public domain RDBMS database engine that you can find at http://www.sqlite.org/.

    Rather than ask you to install SQLite first, because SQLite is public domain, DBD::SQLite includes the entire thing in the distribution. So in order to get a fast transaction capable RDBMS working for your perl project you simply have to install this module, and nothing else.

    You'd see that you that all you need is this module. Have you done any research on either SQLite or MySQL? You need to learn how to use the tools you need to do the task you've been given. I suggest you spend time reading about these tools. I think DBD::SQLite is adequate for this task.

    All you've done so far is reiterate the specifications for something you need to do, this site isn't a code writing service, we are hear to provide advice and help you to write code, not code it for you. You should read:

    If you are not familiar with Perl:

    If you have a specific perl problem please feel free to post it here, along with your code, the error message and some example data. Perl and OS version are often helpful too. See How do I post a question effectively?.

    'Has anyone done this, would this be fast and secure..??'

    I'm sure this or something very like it has been done before, it is possible, wither or not it will be fast or secure depends on many factors, first and foremost you, and how you approach this task.

    Also please read What shortcuts can I use for linking to other information?, and don't start a new thread for the same problem.

    Update: in FASTER PERL (velocigen velometer mod perl or Perl Server Pages) (posted in 2007) you tell us that you had 90% of a MySQL based CMS system. Is this purely a question of programming Perl on windows or are you out of practice?

    Update: removed extra 'you'.

    Martin

      I highly recommend SQLite.  Sprite on CPAN also allows you to perform "SQL" queries on a delimited textfile.  The other recommendations are great as well.

      mtfpny

Re: Query Language with Flat Files
by Marshall (Canon) on Sep 18, 2009 at 19:21 UTC
    I suggested earlier, DBD::CSV. This is a "full DB" that uses a CSV (Comma Separated Value) file. The advantage of this is that since it uses a "flat" CSV data file, you can easily 1)create the initial DB, 2) watch what happens as you run SQL statements. This module is used like you'd use a massive commercial DB...you connect to the DB, then run SQL on it. It handles multiple processes and deals with all this lock stuff.

    You have a simple data structure. Learn how to use basic SQL with something easy. The code (connect to DB, SQL stuff) will all be same on a bigger DB. The difference will be that on a higher performance DB, you can't just "cat" the DB file to see what is going on.

    You have a "wimp" DB. I mean 15K records with 5 fields that can be represented as a "flat" file, and that is only updated 3 times per minute, is "nothing". It will be fast enough. Get the code working then think about something more sophisticated.

    Update:
    Here is some very simple code to get you started:
    the DBI::CSV didn't used to work on Windows, but it does now (Perl 5.10).
    You will need to read a bit about SQL, but that knowledge will be transportable to other databases. Note that field names come from the file, like "username". This is exactly like an Excel spreadsheet can dump out.

    #!/usr/bin/perl -w use strict; use DBI; #need to have DBI::CSV installed for this script my $db = DBI->connect("DBI:CSV:f_dir=./DEMO.db") or die "Cannot connect: $DBI::errstr"; my $get_rows = $db->prepare("SELECT * from DEMO.db"); $get_rows->execute() || die "can't fetch all rows"; while (my ($name,$username, $time, $n1, $n2) = $get_rows->fetchrow_array) { print "$time $name $username $n1 $n2\n"; } my $get_names = $db->prepare("SELECT username from DEMO.db"); $get_names->execute() || die "can't fetch all usernames"; while (my $name = $get_names->fetchrow_array) { print "$name\n"; } __END__ DATA IN THE FILE: DEMO.db: ------------------------------(this line not in this file) name,username,time,num1,num2 "John Smith",jhon99,"10:30:01",345,765 "John Smith",jhon98,"10:30:01",345,765 "Bob Smith",bsm01,"11:30:01",002,246 ABOVE CODE PRINTS: ------------ 10:30:01 John Smith jhon99 345 765 10:30:01 John Smith jhon98 345 765 11:30:01 Bob Smith bsm01 002 246 jhon99 jhon98 bsm01
Re: Query Language with Flat Files
by planetscape (Chancellor) on Sep 18, 2009 at 18:46 UTC

    You may want to have a look at DBD::AnyData.

    HTH,

    planetscape
      Using DBD::AnyData
      #!/usr/bin/perl -w use strict; use diagnostics; use DBI; my $dbh = DBI->connect( 'dbi:AnyData(RaiseError=>1):' or die $DBI::err +str ); $dbh->func( 'example', 'CSV', 'DEMO.db', { sep_char => ',', # eol => "\015", col_names => 'name,username,time,num1,num2' }, 'ad_catalog' ); my $sth = $dbh->prepare("SELECT time, name, username, num1, num2 FROM +example"); $sth->execute() || die "can't fetch all usernames"; while ( my $row = $sth->fetch ) { print "@$row\n"; } $sth = $dbh->prepare("SELECT username FROM example"); $sth->execute() || die "can't fetch all usernames"; while ( my $row = $sth->fetch ) { print "@$row\n"; }
Re: Query Language with Flat Files
by thunders (Priest) on Sep 18, 2009 at 22:00 UTC

    I don't think that your reasons for not using a database are very good ones.

    Installing a database server like MySQL is fairly trivial on windows. Installing the server on Windows is simply a matter of double clicking an installer and answering some question. Specifically the Windows MSI Installer at this link.

    The installation process, and configuration of your server is fully documented online

    Sure it's a lot of material to have to cover before you can even get started writing code, but you are about to commit to implementing a fairly heavy application that will apparently be making millions of writes a day. It's not the kind of thing you should be doing with a half assed cobbled together process. All of the challenges related to writing large amounts of related data to disc in a reliable, secure, fast and synchronized way have already been solved by teams of very smart people.

Re: Query Language with Flat Files
by merlyn (Sage) on Sep 20, 2009 at 16:18 UTC
    You failed to address my post on DBM::Deep. If you're not going to listen to what people are telling you, why are you asking again?

    -- Randal L. Schwartz, Perl hacker

    The key words "MUST", "MUST NOT", "REQUIRED", "SHALL", "SHALL NOT", "SHOULD", "SHOULD NOT", "RECOMMENDED", "MAY", and "OPTIONAL" in this document are to be interpreted as described in RFC 2119.