Data on a server

[rvbfreak]

in other words: i need help understanding sqlite

i cant seem to find an example for how to use it

Edited July 3, 2010 by rvbfreak

[somdcomputerguy]

Most of the function definitions in the 'SQLite Management' subsection in the 'UDF Reference' section of the Help file have code examples. Does that help?

[rvbfreak]

Most of the function definitions in the 'SQLite Management' subsection in the 'UDF Reference' section of the Help file have code examples. Does that help?

I guess my help file is corrupted. It says null a bunch of times in the example.

By the way, with sqlite, i can read and write from a textfile, right?

Im coding a "billboard" application where me and my friend can type something and the other can see it when the script acesses the txt file

Is there a simpler way to do this?

[jchd]

I recommend you perform a fresh install with the latest Scite4AutoIt3 and AutoIt3 itself.

SQLite is a lightweight database engine. It works with its own file format, understandable to SQLite and compatible tools, which is far from being a flat text file.

You'll need to use SQLite functions with SQL statements to access what's stored inside the database.

BTW, SQLite includes a full text search facitily, which you could find useful in your application.

The engine isn't difficult to use and its DBs are simple individual files. The drawback is that it isn't guaranteed safe to use over a network (due to problems with implementations of file locking mecanisms). There are workarounds but slightly less easy to deploy.

Edited July 3, 2010 by jchd

[rvbfreak]

I recommend you perform a fresh install with the latest Scite4AutoIt3 and AutoIt3 itself.

SQLite is a lightweight database engine. It works with its own file format, understandable to SQLite and compatible tools, which is far from being a flat text file.

You'll need to use SQLite functions with SQL statements to access what's stored inside the database.

BTW, SQLite includes a full text search facitily, which you could find useful in your application.

The engine isn't difficult to use and its DBs are simple individual files. The drawback is that it isn't guaranteed safe to use over a network (due to problems with implementations of file locking mecanisms). There are workarounds but slightly less easy to deploy.

could i just use a text file on an ftp server? or would that be too hard to do?

[jchd]

Not hard, just slow and potentially problematic from a concurrency point of view.

You need some of the "ACID properties" (google for explanation) which is the main point of using a database engine, the other being a high-level languages to manage data e.g. SQL.

In your application, managing data should be easy as far as I understand the vague context.

But what exactly should happen when one client is updating the file and one reader wants to read it, and at the same time one wants to append as well? This kind of situations has to rely on ACID-like rules, to be able to isolate writes from reads and guarantee robustness and consistency.

[z0mgItsJohn]

If I'm understanding this correctly, you're basically trying to create a chat client? If that's the case just use TCP. I've got an example in my signature, if you need any extra help with TCP feel free to add me on Yahoo : pk4fun@ymail.com, peace.

- John

[rvbfreak]

If I'm understanding this correctly, you're basically trying to create a chat client? If that's the case just use TCP. I've got an example in my signature, if you need any extra help with TCP feel free to add me on Yahoo : pk4fun@ymail.com, peace.

- John

if i used tcp, could i make it so both users dont have to be online at the same time. i basically want it to

1) Fetch txt document from server

2) Read and display the data

3) Have the ability to add to the text file

im using it so me and my friends can share youtube videos they've found, so really all i need it to do is to send the link

Edited July 4, 2010 by rvbfreak

[z0mgItsJohn]

In that case you would probably be better off creating a web-application using PHP and MySQL. Since I'm bored, I might take a shot at creating something like this.

[jchd]

@John2010zz

Yes a PHP app would fit the bill. Using SQLite (integrated wih PHP) will also make data management even simpler as SQLite requires no installation nor administration at all contrary to MySQL. Note that in this case, using SQLite is safe, as there is only one server using it and its speed makes it a better choice for a number of servers (up to datacenters!).

Using a RDBMS will certainly make it easier to implement a simple but effective multi-categorization of the links.

[z0mgItsJohn]

@rvbfreak

I created a pretty sweet "Video Sharer" in PHP and uploaded it to a free web-host, so you can your friends can use it.

Check it out : http://video-sharer.0fees.net/

PHP Source for anyone who might be interested :

index.php

expandcollapse popup

<style type="text/css">
body {
    font-family: Verdana, Arial, Helvetica, sans-serif;
}
.label{
    padding-left: 5px;
    padding-right: 5px;
    font-size: 13px;
    font-weight: bold;
}
.idk{
    padding-left: 5px;
    padding-right: 5px;
}
.input{
    margin-left: 5px;
    margin-right: 13px;
    margin-top: 2px;
    margin-bottom: 2px;
    font-family: Verdana, Arial, Helvetica, sans-serif;
    font-size: 13px;
}
.data{
    padding-left: 5px;
    font-size: 13px;
}
</style>
<title>Video Sharer</title>
<span class="idk"><a href="index.php?p=view">View Videos</a>, <a href="index.php?p=search">Search Videos</a>, <a href="index.php?p=add">Add Video</a></span></br></br>
<?php
    require ('config.php');
    $ip = $_SERVER['REMOTE_ADDR'];
    $page = $_GET['p'];
    if ($page == 'view'){
        if ((isset ($_GET['delete'])) and (isset ($_GET['id']))){
            $id = mysql_real_escape_string ($_GET['id']);
            $check = mysql_fetch_array (mysql_query ("select submitted_by from videos where id = '$id'"));
            if ($check['submitted_by'] == $ip){
                mysql_query ("delete from videos where id = '$id'");
            }
            exit (header ('location: index.php?p=view'));
        }
        $q = mysql_query ("select * from videos order by id desc");
        $count = mysql_num_rows ($q);
        if ($count == 0){
            echo '<span class="label"><font color="red">Error : No videos at this time.</font></span></br></br>';
        }
        else{
            echo '<table>';
            while ($row = mysql_fetch_array ($q)){
                $id = $row['id'];
                $title = $row['title'];
                $description = $row['description'];
                $link = $row['link'];
                $submitted_by = $row['submitted_by'];
                ?>
                <tr bgcolor="CCCCFE">
                    <td class="label">Title</td>
                    <td class="data"><?php echo $title; ?></td>
                </tr>
                <tr bgcolor="EFF0E8">
                    <td></td>
                    <td class="label">Description</td>
                </tr>
                <tr bgcolor="CCCCFE">
                    <td></td>
                    <td class="data" style="border: 2px solid;" width="190px"><?php echo $description; ?></td>
                </tr>
                <tr bgcolor="EFF0E8">
                    <td class="label">Link</td>
                    <td class="data"><a href="<?php echo $link; ?>" target="_blank">Click Here</a></td>
                </tr>
                <?php
                if ($submitted_by == $ip){
                ?>
                <tr bgcolor="CCCCFE">
                    <td></td>
                    <td height="25px"><span class="label">Options :</span><span style="font-size: 13px;"><a href="index.php?p=view&delete&id=<?php echo $id; ?>">Delete</a></span></td>
                </tr>
                <?php
                }
                ?>
                <tr height="17px"></tr>
                <?php
            }
            echo '</table>';
        }
    }
    elseif ($page == 'search'){
        echo '<span class="label"><font color="green">Notice : This feature is soon to come.</font></span>';
    }
    elseif ($page == 'add'){
        if (isset ($_GET['submit'])){
            $title = mysql_real_escape_string ($_POST['title']);
            $description = mysql_real_escape_string ($_POST['description']);
            $link = mysql_real_escape_string ($_POST['link']);
            if (($title == '') or ($description == '') or ($link == '')){
                exit (header ('location: index.php?p=add&msg=1'));
            }
            else{
                mysql_query ("insert into videos (title, description, link, submitted_by) values ('$title','$description','$link','$ip')");
                exit (header ('location: index.php?p=add&msg=2'));
            }
        }
        else{
            if (isset ($_GET['msg'])){
                $msg = $_GET['msg'];
                if ($msg == 1){
                    echo '<span class="label"><font color="red">Error : All required (*) fields must be filled in.</font></span></br></br>';
                }
                elseif ($msg == 2){
                    echo '<span class="label"><font color="green">Notice : Your video has been successfully submitted.</font></span></br></br>';
                }
            }
            ?>
                <form method="POST" action="index.php?p=add&submit">
                    <table>
                        <tr bgcolor="CCCCFE">
                            <td class="label">Title <font color="red">*<font></font></font></td>
                            <td><input class="input" style="width:190px;" type="text" name="title"></td>
                        </tr>
                        <tr bgcolor="EFF0E8">
                            <td></td>
                            <td class="label">Description <font color="red">*<font></font></font></td>
                        </tr>
                        <tr bgcolor="CCCCFE">
                            <td></td>
                            <td><textarea class="input" style="width:190px;" rows="4" name="description"></textarea></td>
                        </tr>
                        <tr bgcolor="EFF0E8">
                            <td class="label">Link <font color="red">*<font></font></font></td>
                            <td><input class="input" style="width:190px;" type="text" name="link"></td>
                        </tr>
                        <tr bgcolor="CCCCFE">
                            <td></td>
                            <td><input class="input" type="submit" value="Add Video"></td>
                        </tr>
                    </table>
                </form>
            <?php
        }
    }
?>

config.php

<?php
    mysql_connect ('host','user','pass');
    mysql_select_db ('database');
?>

db structure

CREATE TABLE IF NOT EXISTS `videos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `description` text NOT NULL,
  `link` text NOT NULL,
  `submitted_by` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

Edited July 5, 2010 by John2010zz