10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hello All Members,
I am new to this forum and to the shell scripting. I want to write a shell script to do the following:
Scenario: I have a pipe delimited .txt file with multiple fields in it. The very first row of the file contains the name of the column which resembles the name of the... (18 Replies)
Discussion started by: chetanojha
18 Replies
2. Shell Programming and Scripting
I have a test file that I want to read and insert only certain lines into the
the table based on a filter.
1. Rread the log file 12 Hours back Getdate() -12 Hours
2. Extract the following information on for lines that say "DUMP is
complete"
A. Date
B. Database Name
C.... (2 Replies)
Discussion started by: JolietJake
2 Replies
3. Programming
Hi guys, I would like to seek help on inserting data whenever the switch is on or off to my sensor mySQL database in phpMyAdmin from my control.php. I'm using Raspberry PI as my hardware and follow a few tutorials to create my own Web Control Interface, it works perfectly without insert method.... (1 Reply)
Discussion started by: aoiregion
1 Replies
4. Shell Programming and Scripting
Enclosed is comma separated text file. I need to write a korn shell program that will parse the text file and insert the values into Oracle database.
I need to write the korn shell program on Red Hat Enterprise Linux server.
Oracle database is 10g. (15 Replies)
Discussion started by: shellguy
15 Replies
5. UNIX for Dummies Questions & Answers
hi,
I need to create a bash shell script which picks up data from a text file and in the output file puts it into an html made table. I have to use sed and awk utilties to do this
the input text file will contain data in the format:
job name para1 para2 para3 para4 para4
1 ... (1 Reply)
Discussion started by: intern123
1 Replies
6. Shell Programming and Scripting
Hi all,
I have a data like,
0,R001,2,D
this wants to be loaded into a oracle database table.
Pl let me know how this has to be done.
Thanks in advance (2 Replies)
Discussion started by: raji35
2 Replies
7. Shell Programming and Scripting
Hi....can you guys help me out in this script??
Below is a text file and it contains these:
GEF001 000093625 MKL002510 000001 000000 000000 000000 000000 000000 000001
GEF001 000093625 MKL003604 000001 000000 000000 000000 000000 000000 000001
GEF001 000093625 MKL005675 000001... (4 Replies)
Discussion started by: pallavishetty
4 Replies
8. Shell Programming and Scripting
Hi,
Need to get help from you guys about this issue.
I need to insert data into MySql database from a text file which is located in other server.
The text file is something look like below:
Date | SubscriberNo | Call Duration
20/7/07 | 123456788 | 20
20/7/07 | 123412344 | 30
The... (4 Replies)
Discussion started by: shirleyeow
4 Replies
9. Shell Programming and Scripting
I want to connect to one database and collect data from any table using shell script. (0 Replies)
Discussion started by: rinku
0 Replies
10. Shell Programming and Scripting
Hi....can you guys help me out in this script??
Below is a text file script....called Bukom.txt and it contains these:
BUKOM 20060101 2.5 2.6 2.7 2.8 2.9 2.3 2.1
BUKOM 20060102 2.4 2.5 2.6 2.7 2.7 2.6 2.4
BUKOM 20060103 2.1 ... (9 Replies)
Discussion started by: forevercalz
9 Replies
GAMMU-SMSD-PGSQL(7) Gammu GAMMU-SMSD-PGSQL(7)
NAME
gammu-smsd-pgsql - gammu-smsd(1) backend using PostgreSQL database server as a message storage
DESCRIPTION
PGSQL backend stores all data in a PostgreSQL database server, which parameters are defined by configuration (see gammu-smsdrc for descrip-
tion of configuration options).
For tables description see gammu-smsd-tables.
This backend is based on gammu-smsd-sql.
CONFIGURATION
Before running gammu-smsd you need to create necessary tables in the database, which is described below.
The configuration file then can look like:
[smsd]
service = sql
driver = native_pgsql
host = localhost
See also
gammu-smsdrc
CREATING TABLES
SQL script for creating tables in PostgreSQL database:
--
-- Database: "smsd"
--
-- CREATE USER "smsd" WITH NOCREATEDB NOCREATEUSER;
-- CREATE DATABASE "smsd" WITH OWNER = "smsd" ENCODING = 'UTF8';
-- connect "smsd" "smsd"
-- COMMENT ON DATABASE "smsd" IS 'Gammu SMSD Database';
-- --------------------------------------------------------
--
-- Function declaration for updating timestamps
--
CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION update_timestamp() RETURNS trigger AS $update_timestamp$
BEGIN
NEW."UpdatedInDB" := LOCALTIMESTAMP(0);
RETURN NEW;
END;
$update_timestamp$ LANGUAGE plpgsql;
-- --------------------------------------------------------
--
-- Sequence declarations for tables' primary keys
--
--CREATE SEQUENCE inbox_ID_seq;
--CREATE SEQUENCE outbox_ID_seq;
--CREATE SEQUENCE outbox_multipart_ID_seq;
--CREATE SEQUENCE pbk_groups_ID_seq;
--CREATE SEQUENCE sentitems_ID_seq;
-- --------------------------------------------------------
--
-- Index declarations for tables' primary keys
--
--CREATE UNIQUE INDEX inbox_pkey ON inbox USING btree ("ID");
--CREATE UNIQUE INDEX outbox_pkey ON outbox USING btree ("ID");
--CREATE UNIQUE INDEX outbox_multipart_pkey ON outbox_multipart USING btree ("ID");
--CREATE UNIQUE INDEX pbk_groups_pkey ON pbk_groups USING btree ("ID");
--CREATE UNIQUE INDEX sentitems_pkey ON sentitems USING btree ("ID");
-- --------------------------------------------------------
--
-- Table structure for table "daemons"
--
CREATE TABLE daemons (
"Start" text NOT NULL,
"Info" text NOT NULL
);
--
-- Dumping data for table "daemons"
--
-- --------------------------------------------------------
--
-- Table structure for table "gammu"
--
CREATE TABLE gammu (
"Version" smallint NOT NULL DEFAULT '0'
);
--
-- Dumping data for table "gammu"
--
INSERT INTO gammu ("Version") VALUES(13);
-- --------------------------------------------------------
--
-- Table structure for table "inbox"
--
CREATE TABLE inbox (
"UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"ReceivingDateTime" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"Text" text NOT NULL,
"SenderNumber" varchar(20) NOT NULL DEFAULT '',
"Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
"UDH" text NOT NULL,
"SMSCNumber" varchar(20) NOT NULL DEFAULT '',
"Class" integer NOT NULL DEFAULT '-1',
"TextDecoded" text NOT NULL DEFAULT '',
"ID" serial PRIMARY KEY,
"RecipientID" text NOT NULL,
"Processed" boolean NOT NULL DEFAULT 'false',
CHECK ("Coding" IN
('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression'))
);
--
-- Dumping data for table "inbox"
--
-- --------------------------------------------------------
--
-- Create trigger for table "inbox"
--
CREATE TRIGGER update_timestamp BEFORE UPDATE ON inbox FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
-- --------------------------------------------------------
--
-- Table structure for table "outbox"
--
CREATE TABLE outbox (
"UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"SendingDateTime" timestamp NOT NULL DEFAULT LOCALTIMESTAMP(0),
"SendBefore" time NOT NULL DEFAULT '23:59:59',
"SendAfter" time NOT NULL DEFAULT '00:00:00',
"Text" text,
"DestinationNumber" varchar(20) NOT NULL DEFAULT '',
"Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
"UDH" text,
"Class" integer DEFAULT '-1',
"TextDecoded" text NOT NULL DEFAULT '',
"ID" serial PRIMARY KEY,
"MultiPart" boolean NOT NULL DEFAULT 'false',
"RelativeValidity" integer DEFAULT '-1',
"SenderID" varchar(255),
"SendingTimeOut" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"DeliveryReport" varchar(10) DEFAULT 'default',
"CreatorID" text NOT NULL,
CHECK ("Coding" IN
('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
CHECK ("DeliveryReport" IN ('default','yes','no'))
);
CREATE INDEX outbox_date ON outbox("SendingDateTime", "SendingTimeOut");
CREATE INDEX outbox_sender ON outbox("SenderID");
--
-- Dumping data for table "outbox"
--
-- --------------------------------------------------------
--
-- Create trigger for table "outbox"
--
CREATE TRIGGER update_timestamp BEFORE UPDATE ON outbox FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
-- --------------------------------------------------------
--
-- Table structure for table "outbox_multipart"
--
CREATE TABLE outbox_multipart (
"Text" text,
"Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
"UDH" text,
"Class" integer DEFAULT '-1',
"TextDecoded" text DEFAULT NULL,
"ID" serial,
"SequencePosition" integer NOT NULL DEFAULT '1',
PRIMARY KEY ("ID", "SequencePosition"),
CHECK ("Coding" IN
('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression'))
);
--
-- Dumping data for table "outbox_multipart"
--
-- --------------------------------------------------------
--
-- Table structure for table "pbk"
--
CREATE TABLE pbk (
"ID" serial PRIMARY KEY,
"GroupID" integer NOT NULL DEFAULT '-1',
"Name" text NOT NULL,
"Number" text NOT NULL
);
--
-- Dumping data for table "pbk"
--
-- --------------------------------------------------------
--
-- Table structure for table "pbk_groups"
--
CREATE TABLE pbk_groups (
"Name" text NOT NULL,
"ID" serial PRIMARY KEY
);
--
-- Dumping data for table "pbk_groups"
--
-- --------------------------------------------------------
--
-- Table structure for table "phones"
--
CREATE TABLE phones (
"ID" text NOT NULL,
"UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"TimeOut" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"Send" boolean NOT NULL DEFAULT 'no',
"Receive" boolean NOT NULL DEFAULT 'no',
"IMEI" varchar(35) PRIMARY KEY NOT NULL,
"Client" text NOT NULL,
"Battery" integer NOT NULL DEFAULT -1,
"Signal" integer NOT NULL DEFAULT -1,
"Sent" integer NOT NULL DEFAULT 0,
"Received" integer NOT NULL DEFAULT 0
);
--
-- Dumping data for table "phones"
--
-- --------------------------------------------------------
--
-- Create trigger for table "phones"
--
CREATE TRIGGER update_timestamp BEFORE UPDATE ON phones FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
-- --------------------------------------------------------
--
-- Table structure for table "sentitems"
--
CREATE TABLE sentitems (
"UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"SendingDateTime" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
"DeliveryDateTime" timestamp(0) WITHOUT time zone NULL,
"Text" text NOT NULL,
"DestinationNumber" varchar(20) NOT NULL DEFAULT '',
"Coding" varchar(255) NOT NULL DEFAULT 'Default_No_Compression',
"UDH" text NOT NULL,
"SMSCNumber" varchar(20) NOT NULL DEFAULT '',
"Class" integer NOT NULL DEFAULT '-1',
"TextDecoded" text NOT NULL DEFAULT '',
"ID" serial,
"SenderID" varchar(255) NOT NULL,
"SequencePosition" integer NOT NULL DEFAULT '1',
"Status" varchar(255) NOT NULL DEFAULT 'SendingOK',
"StatusError" integer NOT NULL DEFAULT '-1',
"TPMR" integer NOT NULL DEFAULT '-1',
"RelativeValidity" integer NOT NULL DEFAULT '-1',
"CreatorID" text NOT NULL,
CHECK ("Status" IN
('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
'DeliveryUnknown','Error')),
CHECK ("Coding" IN
('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
PRIMARY KEY ("ID", "SequencePosition")
);
CREATE INDEX sentitems_date ON sentitems("DeliveryDateTime");
CREATE INDEX sentitems_tpmr ON sentitems("TPMR");
CREATE INDEX sentitems_dest ON sentitems("DestinationNumber");
CREATE INDEX sentitems_sender ON sentitems("SenderID");
--
-- Dumping data for table "sentitems"
--
-- --------------------------------------------------------
--
-- Create trigger for table "sentitems"
--
CREATE TRIGGER update_timestamp BEFORE UPDATE ON sentitems FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
Note You can find the script in docs/sql/pgsql.sql as well.
UPGRADING TABLES
The easiest way to upgrade database structure is to backup old one and start with creating new one based on example above.
For upgrading existing database, you can use changes described in smsd-tables-history and then manually update Version field in gammu ta-
ble.
AUTHOR
Michal iha <michal@cihar.com>
COPYRIGHT
2009-2012, Michal iha <michal@cihar.com>
1.31.90 February 24, 2012 GAMMU-SMSD-PGSQL(7)