diff options
| -rwxr-xr-x | DBI_EXAMPLES/DBI_sqlite_alternative.pm | 59 | ||||
| -rw-r--r-- | database/journals.db | bin | 0 -> 2048 bytes | |||
| -rw-r--r-- | database/many_to_many_example.postgres | 16 | ||||
| -rw-r--r-- | database/manytomany_sqlite.sql | 16 | ||||
| -rw-r--r-- | database/mysql_created_with_sql_translator_from_sqlite.sql | 19 | ||||
| -rw-r--r-- | database/schema.sqlite | 8 | ||||
| -rw-r--r-- | sphinx_stuff/sphinx.conf | 51 | ||||
| -rw-r--r-- | sphinx_stuff/sphinx_commands.sh | 2 | ||||
| -rw-r--r-- | sphinx_stuff/sphinxtest.pl | 10 |
9 files changed, 181 insertions, 0 deletions
diff --git a/DBI_EXAMPLES/DBI_sqlite_alternative.pm b/DBI_EXAMPLES/DBI_sqlite_alternative.pm new file mode 100755 index 0000000..d65f8d6 --- /dev/null +++ b/DBI_EXAMPLES/DBI_sqlite_alternative.pm @@ -0,0 +1,59 @@ +package journal_database; +use DBD::SQLite; +use DBI; +use Data::Dumper; +my $dbh = DBI->connect("dbi:SQLite:dbname=./database/journals.db","",""); + + +CREATE TABLE journals( + id INTEGER PRIMARY KEY NOT NULL, --optional + date INTEGER NOT NULL, + fulltext TEXT, + image_url TEXT +); + +sub remove_from_db{ + my $id_to_remove = shift; + my $sth = $dbh->prepare( + "DELETE FROM journals ". + "WHERE id=$id_to_remove;" + ); + $sth->execute(); +} +sub get_entry_by_date { + my $date_int = shift; + my $sth = $dbh->prepare( + "SELECT *". + "FROM journals ". + "WHERE `date`=? " + ); + $sth->execute($date_int); + return $sth->fetchrow_hashref(); +} + +sub add_journal_entry{ + my $entry = shift; + my $sqlCmd = "insert into journals values (?,?,?,?);" ; +# ."on duplicate key update ". +# "id = ?,". +# "date = ?,". +# "fulltext = ?,". +# "image_url = ?,". +# ";"; + my $sth = $dbh->do($sqlCmd, + undef, + $entry->{id}, + $entry->{date} || time(), + $entry->{fulltext}, + $entry->{image_url} + ); + } + + my $entry = {}; + $entry->{id} = '12'; + $entry->{date} = '123432'; + $entry->{fulltext} = 'this is a test of the fulltext'; + $entry->{image_url} = 'this is a test of the image url'; + add_journal_entry($entry); + print Dumper get_entry_by_date(123432); + remove_from_db(12); diff --git a/database/journals.db b/database/journals.db Binary files differnew file mode 100644 index 0000000..8f90768 --- /dev/null +++ b/database/journals.db diff --git a/database/many_to_many_example.postgres b/database/many_to_many_example.postgres new file mode 100644 index 0000000..6a4ef56 --- /dev/null +++ b/database/many_to_many_example.postgres @@ -0,0 +1,16 @@ +CREATE TABLE cds( +id serial PRIMARY KEY -- implicit primary key constraint, +title text NOT NULL +); + +CREATE TABLE artists( +id serial PRIMARY KEY, +name text NOT NULL +); + +CREATE TABLE cds_and_artists( +cds_id integer REFERENCES cds (id) ON UPDATE CASCADE ON DELETE CASCADE, +artists_id integer REFERENCES artisys (id) ON UPDATE CASCADE, +CONSTRAINT cds_and_artists_pkey PRIMARY KEY (cds_id, artists_id) +); + diff --git a/database/manytomany_sqlite.sql b/database/manytomany_sqlite.sql new file mode 100644 index 0000000..2546b5f --- /dev/null +++ b/database/manytomany_sqlite.sql @@ -0,0 +1,16 @@ +CREATE TABLE cds( + id INTEGER PRIMARY KEY NOT NULL, + title TEXT NOT NULL +); +CREATE TABLE artists( + id INTEGER PRIMARY KEY NOT NULL, + name TEXT NOT NULL +); +CREATE TABLE cds_and_artists( + cds_id INTEGER, + artists_id INTEGER, + FOREIGN KEY(cds_id) REFERENCES cds(id) ON DELETE CASCADE, + FOREIGN KEY(artists_id) REFERENCES artists(id) ON DELETE CASCADE +); +CREATE INDEX cdsindex ON cds_and_artists(cds_id); +CREATE INDEX artistsindex ON cds_and_artists(artists_id); diff --git a/database/mysql_created_with_sql_translator_from_sqlite.sql b/database/mysql_created_with_sql_translator_from_sqlite.sql new file mode 100644 index 0000000..9fd0b7f --- /dev/null +++ b/database/mysql_created_with_sql_translator_from_sqlite.sql @@ -0,0 +1,19 @@ +-- +-- Created by SQL::Translator::Producer::MySQL +-- Created on Wed Sep 10 16:34:40 2014 +-- +SET foreign_key_checks=0; + +-- +-- Table: `journals` +-- +CREATE TABLE `journals` ( + `id` INTEGER NOT NULL, + `date` INTEGER NOT NULL comment 'optional', + `fulltext` text NULL, + `image_url` text NULL, + PRIMARY KEY (`id`) +); + +SET foreign_key_checks=1; + diff --git a/database/schema.sqlite b/database/schema.sqlite new file mode 100644 index 0000000..1fe63c0 --- /dev/null +++ b/database/schema.sqlite @@ -0,0 +1,8 @@ +PRAGMA foreign_keys = ON; +CREATE TABLE journals( + id INTEGER PRIMARY KEY NOT NULL, --optional + date INTEGER NOT NULL, + fulltext TEXT, + image_url TEXT +); + diff --git a/sphinx_stuff/sphinx.conf b/sphinx_stuff/sphinx.conf new file mode 100644 index 0000000..24d271b --- /dev/null +++ b/sphinx_stuff/sphinx.conf @@ -0,0 +1,51 @@ +source journal +{ + type = mysql + sql_host = localhost + sql_user = journal_user + sql_pass = journal_password + sql_db = journal +# sql_sock = /var/run/mysqld/mysqld.sock + sql_port = 3306 + + # indexer query + # document_id MUST be the very first field + # document_id MUST be positive (non-zero, non-negative) + # document_id MUST fit into 32 bits + # document_id MUST be unique + sql_query = \ + SELECT \ + `id`, `fulltext`, `image_url` \ + FROM \ + journals; +# +# sql_group_column = assembly +# sql_group_column = model + + # document info query + # ONLY used by search utility to display document information + # MUST be able to fetch document info by its id, therefore + # MUST contain '$id' macro + # + + sql_query_info = SELECT * FROM journals WHERE id=$id +} + +index journal +{ + source = journal + path = /home/pepper/sphinx/journal + morphology = stem_en + + min_word_len = 3 + min_prefix_len = 0 + min_infix_len = 3 +} + +searchd +{ + port = 3312 + log = /var/log/searchd/searchd.log + query_log = /var/log/searchd/query.log + pid_file = /var/log/searchd/searchd.pid +} diff --git a/sphinx_stuff/sphinx_commands.sh b/sphinx_stuff/sphinx_commands.sh new file mode 100644 index 0000000..d721456 --- /dev/null +++ b/sphinx_stuff/sphinx_commands.sh @@ -0,0 +1,2 @@ +sudo sphinx-indexer journal +sudo sphinx-searchd diff --git a/sphinx_stuff/sphinxtest.pl b/sphinx_stuff/sphinxtest.pl new file mode 100644 index 0000000..3ec4f14 --- /dev/null +++ b/sphinx_stuff/sphinxtest.pl @@ -0,0 +1,10 @@ +#!/usr/bin/perl +use Data::Dumper; +use Sphinx::Search; + +$sph = Sphinx::Search->new(); + +$results = $sph->SetMatchMode(SPH_MATCH_ALL) + ->SetSortMode(SPH_SORT_RELEVANCE) + ->Query("dolly"); +print Dumper $results; |