moved things aroundHEADmaster

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
new 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;