Please login or register.

Login with username, password and session length
Advanced search  

News:

You need/want an older version of sNews ? Download an older/unsupported version here.

Author Topic: Creating Tables for snews 1.6 in MySQL  (Read 4138 times)

Keyrocks

  • Doug
  • ULTIMATE member
  • ******
  • Karma: 449
  • Posts: 6020
  • Semantically Challenged
    • snews.ca
Creating Tables for snews 1.6 in MySQL
« on: January 23, 2008, 05:58:16 pm »

There have been a few questions or queries that deal with how to format or structure the table generating scripts for sNews into an .sql file.
I've created the script below that can be used to make a default snews16.sql file from scratch. You can then use this file to create tables on a new database using the IMPORT tab in phpMyAdmin.
You can also use this as a guide when you want to add data INSERT strings to any of the tables... or make up a modified table-set.
Feel free to remove the commented bits when if and when you no longer need them as a reference.  :)

Code: [Select]
--
-- Format for sNews 1.6 (default) MySQL database table file
-- (keyrocks)
-- Jan.23.08
-- ------------------------------------------------------------------------------------
-- The table-sets in this file are for sNews 1.6 default package.
-- The section for each table begins wtih a DROP TABLE string to make sure
-- there is no other table with the same-name already existing.
-- -----------------------------------------------------------------------------

-- ARTICLES TABLE
-- ---------------
-- Check existing database for existence of an articles table.
-- If one exists, then drop the table so we can make the new one.
DROP TABLE IF EXISTS articles;

-- Create the 'articles' table

CREATE TABLE articles (
id int(11) primary key auto_increment,
title varchar(100) default NULL,
seftitle varchar(100) default NULL,
  `text` longtext,
  `date` datetime default NULL,
  category int(8) NOT NULL default '0',
  position int(6) default NULL,
  displaytitle char(3) NOT NULL default 'YES',
  displayinfo char(3) NOT NULL default 'YES',
  commentable varchar(5) NOT NULL default '',
  published int(3) NOT NULL default '1',
  description_meta varchar(255) default NULL,
  keywords_meta varchar(255) default NULL
);

-- Dumping Data for articles table:
-- ---------------------------------
-- If there is data to insert into the articles table, add INSERT strings here.
-- Example of first article:
INSERT INTO `articles` VALUES('1', 'Article on Home', 'article-on-home', 'This is the first article posted to Home (by default).', '2008-01-23 09:30:00', '0', '1', 'YES', 'YES', 'YES', '1', 'meta description here', 'meta keywords here');

-- end articles table section ==================================================

-- CATEGORIES TABLE
-- -----------------
-- Check existing database for existence of a categories table.
-- If one exists, then drop the table so we can make the new one.
DROP TABLE IF EXISTS categories;

-- Create the 'categories' table

CREATE TABLE categories (
  id int(8) primary key auto_increment,
  name varchar(100) NOT NULL,
  seftitle varchar(100) NOT NULL,
  description varchar(255) NOT NULL,
  published varchar(4) NOT NULL default 'YES',
  catorder smallint(6) NOT NULL default '0'
);

-- Dumping Data for categories table:
-- ----------------------------------
-- If there is data to insert into the categories table, add INSERT strings here.
-- Example of first category:
INSERT INTO `categories` VALUES('1', 'Category One', 'category-one', 'This is the first category', 'YES', '0');

-- end categories table section ================================================


-- COMMENTS TABLE
-- -----------------
-- Check existing database for existence of a comments table.
-- If one exists, then drop the table so we can make the new one.
DROP TABLE IF EXISTS comments;

-- Create the 'comments' table

CREATE TABLE comments (
  id int(11) primary key auto_increment,
  articleid int(11) default '0',
  name varchar(50),
  url varchar(100) NOT NULL,
  comment text,
  time datetime NOT NULL default '0000-00-00 00:00:00',
  approved varchar(5) NOT NULL default 'True'
);

-- Dumping Data for comments table:
-- ----------------------------------
-- If there is data to insert into the comments table, add INSERT strings here.
-- Example of comment to the first article:
INSERT INTO `comments` VALUES('1', '1', 'username', 'http://mysite.com', 'This is a comment on the first article in the Home page.', '2007-01-08 09:31:00', 'True');

-- end comments table section ==================================================


-- SETTINGS TABLE
-- -----------------
-- Check existing database for existence of a Settings table.
-- If one exists, then drop the table so we can make the new one.
DROP TABLE IF EXISTS settings;

-- Create the 'settings' table

CREATE TABLE settings (
  id int(8) primary key auto_increment,
  name varchar(20) NOT NULL,
  `value` varchar(60) NOT NULL
);

-- Dumping actual data for settings table:
-- ------------------------------------------
INSERT INTO settings VALUES (1, 'website_title', 'sNews 1.6');
INSERT INTO settings VALUES (2, 'home_sef', 'home');
INSERT INTO settings VALUES (3, 'website_description', 'sNews CMS');
INSERT INTO settings VALUES (4, 'website_keywords', 'new, site, snews');
INSERT INTO settings VALUES (5, 'website_email', 'info@mydomain.com');
INSERT INTO settings VALUES (6, 'contact_subject', 'Contact Form');
INSERT INTO settings VALUES (7, 'language', 'EN');
INSERT INTO settings VALUES (8, 'charset', 'UTF-8');
INSERT INTO settings VALUES (9, 'date_format', 'd.m.Y. H:i');
INSERT INTO settings VALUES (10, 'article_limit', '3');
INSERT INTO settings VALUES (11, 'rss_limit', '5');
INSERT INTO settings VALUES (12, 'display_page', '');
INSERT INTO settings VALUES (13, 'display_new_on_home', '');
INSERT INTO settings VALUES (14, 'display_pagination', '');
INSERT INTO settings VALUES (15, 'num_categories', 'on');
INSERT INTO settings VALUES (16, 'approve_comments', '');
INSERT INTO settings VALUES (17, 'comments_order', 'ASC');
INSERT INTO settings VALUES (18, 'comment_limit', '30');
INSERT INTO settings VALUES (19, 'word_filter_enable', '');
INSERT INTO settings VALUES (20, 'word_filter_file', '');
INSERT INTO settings VALUES (21, 'word_filter_change', '');
INSERT INTO settings VALUES (22, 'username', '098f6bcd4621d373cade4e832627b4f6');
INSERT INTO settings VALUES (23, 'password', '098f6bcd4621d373cade4e832627b4f6');

-- end settings table section ==================================================
Logged
Do it now... later may not come.
-------------------------------------------------------------------------------------------------
sNews 1.6 MESU | sNews 1.6 MEMU