####################################
# CONNECT TO THE DATABASE
####################################

$Subs{'CorantoSQL_connectdb'} = <<'END_SUB';
sub CorantoSQL_connectdb {
# This is a subroutine intended for addons that want to connect to the CorantoSQL database.
my ($corantosql_dbh);
eval {
	if ($CConfig{'CorantoSQL_dbhost'} ne "testhost" && $CConfig{'CorantoSQL_dbname'} ne "database name"){
	# Connect to the DB
	$corantosql_dbh = DBI->connect("DBI:$CConfig{'CorantoSQL_dbserver'}:database=$CConfig{'CorantoSQL_dbname'};host=$CConfig{'CorantoSQL_dbhost'}","$CConfig{'CorantoSQL_dbuser'}","$CConfig{'CorantoSQL_dbpass'}");
		if ($CConfig{'CorantoSQL_supportsTransactions'} == "1"){
		$corantosql_dbh->{AutoCommit} = 0;  # enable transactions, if possible
		$corantosql_dbh->{RaiseError} = 1;
		}
	}
};

if ($@){
&CRcough("Could not connect to SQL server. Reason: $@");
}

# Make it possible to read BLOB/LONG fields ( up to 500kb )
$corantosql_dbh->{LongReadLen} = 5120000;
$corantosql_dbh->{LongTruncOk} = 0; # Do NOT truncate reads.

return $corantosql_dbh;
}
END_SUB

########################################
# GET A PARTICULAR NEWS ITEM
########################################

$Subs{'CorantoSQL_GetNewsItem'} = <<'END_SUB';
sub CorantoSQL_GetNewsItem {
# This sub allows other addons to easily retrieve a particular news item from the DB, using $newsid
my $id = shift;
my ($corantosql_dbh,$query,$sth);

$corantosql_dbh = &CorantoSQL_connectdb();

$query = qq~SELECT * FROM $CConfig{'CorantoSQL_tblname'} WHERE newsid='$id'~;
$sth = $corantosql_dbh->prepare($query);
$sth->execute();

	while ($corantosql_ref = $sth->fetchrow_hashref()) {
	GetSQLFields();
	}

$sth->finish();
$corantosql_dbh->disconnect();
}
END_SUB

################################################################
# GET VARIOUS SQL STATEMENTS
################################################################

$Subs{'CorantoSQL_DeleteItemFromDB'} = <<'END_SUB';
sub CorantoSQL_DeleteItemFromDB {
my $newsid = shift;
my ($sql);

$sql = qq~DELETE FROM $CConfig{'CorantoSQL_tblname'} WHERE newsid='$newsid'~;

	if ($CConfig{'CorantoSQL_supportsTransactions'} == "1"){
		eval {
		#$corantosql_dbh->begin_work();
		$corantosql_dbh->do($sql);
		$corantosql_dbh->commit();
		};

		if ($@){
		$corantosql_dbh->rollback(); # undo the incomplete changes
		&CRcough("CorantoSQL database transaction aborted because of $@. All changes to the DB have been reversed.");
		}
	}
	else {
		eval {
		$corantosql_dbh->do($sql);
		};
		if ($@){
		&CRcough("CorantoSQL database altercation aborted because of $@.");
		}
	}
}
END_SUB

$Subs{'CorantoSQL_ModifyNews_SQL'} = <<'END_SUB';
sub CorantoSQL_ModifyNews_SQL {
my $sql = qq~SELECT * FROM $CConfig{'CorantoSQL_tblname'} ORDER BY newstime DESC~;

# The below hook can allow addons to replace the ModifyNewsEdit SQL statement
# HOOK: CorantoSQL_ReplaceModifyNewsSQL
if($Addons{'CorantoSQL_ReplaceModifyNewsSQL'}){my $w;foreach $w (@{$Addons{'CorantoSQL_ReplaceModifyNewsSQL'}}){my $addon=$w->[2];eval ${$w->[0]};AErr($addon,$@)if $@;};}

return $sql;
}
END_SUB

$Subs{'CorantoSQL_ModifyNewsEdit_SQL'} = <<'END_SUB';
sub CorantoSQL_ModifyNewsEdit_SQL {
my $newsid = shift;
my $sql = qq~SELECT * FROM $CConfig{'CorantoSQL_tblname'} WHERE newsid='$newsid'~;

# The below hook can allow addons to replace the ModifyNewsEdit SQL statement
# HOOK: CorantoSQL_ReplaceModifyNewsEditSQL
if($Addons{'CorantoSQL_ReplaceModifyNewsEditSQL'}){my $w;foreach $w (@{$Addons{'CorantoSQL_ReplaceModifyNewsEditSQL'}}){my $addon=$w->[2];eval ${$w->[0]};AErr($addon,$@)if $@;};}

return $sql;
}
END_SUB

$Subs{'CorantoSQL_BuildNewsSQL'} = <<'END_SUB';
sub CorantoSQL_BuildNewsSQL {
my ($db1,$db2,$sql);
$db1 = "$CConfig{'CorantoSQL_dbname'}.$CConfig{'CorantoSQL_tblname'}";
$db2 = "$CConfig{'vBComments_dbname'}.thread";
$sql = "SELECT * FROM $CConfig{'CorantoSQL_tblname'} ORDER BY newstime DESC";

# The below hook can allow addons to replace the BuildNews SQL statement
# HOOK: CorantoSQL_ReplaceBuildNewsSQL
if($Addons{'CorantoSQL_ReplaceBuildNewsSQL'}){my $w;foreach $w (@{$Addons{'CorantoSQL_ReplaceBuildNewsSQL'}}){my $addon=$w->[2];eval ${$w->[0]};AErr($addon,$@)if $@;};}

return $sql;
}
END_SUB

################################################################
# ALLOW A ADDON TO UPDATE A CERTAIN DB FIELD
################################################################

$Subs{'CorantoSQL_AddonUpdateDB'} = <<'END_SUB';
sub CorantoSQL_AddonUpdateDB {
# This sub is intended for addons that want to update a particular database field ( like the "Subject" WHERE newstime=60606606 )
my($field,$value,$newsid) = shift;

my($value_quoted,$query,$corantosql_dbh);
$value_quoted = $corantosql_dbh->quote($value);

$corantosql_dbh = &CorantoSQL_connectdb();

$query = qq~UPDATE $CConfig{'CorantoSQL_tblname'} SET $field='$value_quoted' WHERE newsid='$newsid'~;

#######

if ($CConfig{'CorantoSQL_supportsTransactions'} == "1"){

	eval {
	$corantosql_dbh->do($sql);
	$corantosql_dbh->commit();
	};
	if ($@){
	$corantosql_dbh->rollback(); # undo the incomplete changes
	&CRcough("CorantoSQL database transaction aborted because of $@. All changes to the DB have been reversed.");
	}

}
else {
$corantosql_dbh->do($sql);
}

$corantosql_dbh->disconnect();
}
END_SUB

############################################################################################
# MAKE THE crsql_sqlstuff.pl FILE
############################################################################################

$Subs{'CorantoSQL_GetListsMade'} = <<'END_SUB';
sub CorantoSQL_GetListsMade {
# Builds the lists for GetSQLFields and SQL Insert & Modify statments.
# WORKED IN TESTING

my ($CorantoSQL_FieldListPre,$CorantoSQL_VarList,$CorantoSQL_Getfields,$CorantoSQL_QLoop,$CorantoSQL_FieldList);
my ($c,$sthcc,$sthcc_update,$fieldname,$i,$field_name,$FieldList);

&NeedCFG;
my @field = @fieldDB_internalorder;

# From here until where I say is the code that splits the differant field names up
for ($c = -1, @field){
$c++;
}
for ($i =-1, @field){
	$i++;
	$CorantoSQL_FieldListPre = qq~$field[$i]~;
	#$CorantoSQL_VarList =  qq~'$field[$i]'~;
	#$CorantoSQL_VarList .= ",";
	
    #BIG ?
	if ($i < ($c-1)){
		#$CorantoSQL_VarList .=  "'";
		$CorantoSQL_VarList .=  "\$" . $field[$i];
		#$CorantoSQL_VarList .=  "'";
		$CorantoSQL_VarList .= ", ";
		
		$CorantoSQL_Getfields .= "\$" . $field[$i] . " \=  \$corantosql_ref->{\'".$field[$i]. "\'}\; ";  
		$CorantoSQL_QLoop .= "?,"; 
		$CorantoSQL_FieldListPre .= ", ";
		}
	$CorantoSQL_FieldList .=  $CorantoSQL_FieldListPre;

}
$i--;

#$CorantoSQL_VarList .= qq~'~;
$CorantoSQL_VarList  .=  "\$" . $field[$i];
#$CorantoSQL_VarList .= qq~'~;
$CorantoSQL_Getfields .= "\$" . $field[$i] . " \= \$corantosql_ref->{\'".$field[$i]. "\'}\; ";
$CorantoSQL_QLoop .= "?";

$CConfig{'CorantoSQL_FieldList'} = $CorantoSQL_FieldList; 
$CConfig{'CorantoSQL_GetSQLFields'} = $CorantoSQL_Getfields; 
$CConfig{'CorantoSQL_QLoop'} = $CorantoSQL_QLoop;
$CConfig{'CorantoSQL_Varlist'} = $CorantoSQL_VarList;

# Now, we print the results to a file
my $sqlstuff_fh = CRopen(">$CConfig{'CorantoSQL_path'}/crsql_sqlstuff.pl");

# The INSERT sub
$sthcc = "\$Subs\{\'CorantoSQL_InsertDB\'\} \= \<\<\'END_SUB\'\;\n";
$sthcc .= 'sub CorantoSQL_InsertDB {';
$sthcc .= "\n";

my $sthcc_fieldlist;

$sthcc .= "\n";
$sthcc .= 'my $sthc = $corantosql_dbh->prepare("';
$sthcc .= "INSERT INTO $CConfig{'CorantoSQL_tblname'} ";

$sthcc_fieldlist .= '(';
$sthcc_fieldlist .= "$CConfig{'CorantoSQL_FieldList'}";
$sthcc_fieldlist .= ')';

$sthcc_fieldlist =~ s/\,\)/\)/gi;

$sthcc .= $sthcc_fieldlist;

$sthcc .= ' VALUES (';
$sthcc .= "$CConfig{'CorantoSQL_QLoop'}";
$sthcc .= ")\")\;\n";

## NEW IN BETA 9 ##

# We need this number to pass to $sth->bind_param.... binding starts at 1
my $bind_number = "1";

foreach $field_name(@field){
$sthcc .= '$sthc->bind_param(';
$sthcc .= "$bind_number";
$sthcc .= ', ';
$sthcc .= "\$$field_name";
$sthcc .= ');';
$sthcc .= "\n";

$bind_number++;
}

$sthcc .= "\n";
$sthcc .= '$sthc->execute();';
$sthcc .= "\n\$sthc->finish()\;\n\}\n";
$sthcc .= 'END_SUB';

## END NEW IN BETA 9 ##

# HOOK: CorantoSQL_AppendINSERTSub
if($Addons{'CorantoSQL_AppendINSERTSub'}){my $w;foreach $w (@{$Addons{'CorantoSQL_AppendINSERTSub'}}){my $addon=$w->[2];eval ${$w->[0]};AErr($addon,$@)if $@;};}

$sthcc .= "\n\n";

# Now for GetSQLFields
$sthcc .= "\$Subs\{\'GetSQLFields\'\} \= <<\'END_SUB\'\;\n";
$sthcc .= 'sub GetSQLFields {';
#$sthcc .= "\nlocal \$corantosql_ref \= shift\;\n";
$sthcc .= $CorantoSQL_Getfields;

# Remove unneccessary spaces and such
$sthcc =~ s/  / /gi;
$sthcc =~ s/\; /\;\n/gi;
# STOP THAT HERE!!

$sthcc .= '}';
$sthcc .= "\nEND_SUB";

# HOOK: CorantoSQL_AppendGetSQLFields
if($Addons{'CorantoSQL_AppendGetSQLFields'}){my $w;foreach $w (@{$Addons{'CorantoSQL_AppendGetSQLFields'}}){my $addon=$w->[2];eval ${$w->[0]};AErr($addon,$@)if $@;};}

$sthcc .= "\n\n";

# The UPDATE sub

$sthcc .= "\$Subs\{\'CorantoSQL_UpdateDB\'\} \= <<\'END_SUB\'\;\n";
$sthcc .= 'sub CorantoSQL_UpdateDB {';
$sthcc .= "\n";
foreach $field_name(@field){
	if ($field_name ne "newsid" && $field_name ne "newstime" && $field_name ne "User"){
	$sthcc .= 'my $';
	$sthcc .= "$field_name";
	$sthcc .= 'temp = $corantosql_dbh->quote($';
	$sthcc .= "$field_name)\;\n";
	$sthcc .= 'my $';
	$sthcc .= "$field_name \= ";
	$sthcc .= '$';
	$sthcc .= $field_name;
	$sthcc .= 'temp';
	$sthcc .= "\;\n";
	}
}
$sthcc .= "\n";
$sthcc .= 'my $sthc = $corantosql_dbh->prepare("UPDATE ';
$sthcc .= "$CConfig{'CorantoSQL_tblname'} SET ";

foreach $field_name(@field){
	if ($field_name ne "newsid" && $field_name ne "newstime" && $field_name ne "User"){
	$sthcc_update .= "$field_name";
	$sthcc_update .= '=$';
	$sthcc_update .= "$field_name,";
	}
}

$sthcc .= $sthcc_update;
$sthcc .= ' WHERE newsid=?"';

# Get rid of final ","
$sthcc =~ s/\, WHERE/ WHERE/gi;

$sthcc .= ")\;\n";
$sthcc .= '$sthc->execute($in{nid}';
$sthcc .= ")\;\n\$sthc->finish()\;\}\n";
$sthcc .= 'END_SUB';

$sthcc .= "\n\n";

# NOW TO INCLUDE THE DEVELOPMENT SUBS
my $devsubs = CRopen("<$CConfig{'CorantoSQL_path'}/csql-devsubs.txt");
while(<$devsubs>){
$sthcc .= $_;
}
close($devsubs);

#$sthcc .= "\n\n1\;";

# Filter the CorantoSQL_GetListsMade sub out of crsql_sqlstuff.pl
$sthcc =~ s/\$Subs\{'CorantoSQL_GetListsMade'\}.*?\nEND_SUB//gi;

print $sqlstuff_fh "$sthcc";
close($sqlstuff_fh);
}
END_SUB

#######################################################################################
# CREATE NEW CorantoSQL DB TABLE
#######################################################################################

$Subs{'CorantoSQL_CreateNewDB'} = <<'END_SUB';
sub CorantoSQL_CreateNewDB {
my ($i,$sql_table_format);

$sql_table_format = "CREATE TABLE $CConfig{'CorantoSQL_tblname'} (";
$sql_table_format .= qq~User VARCHAR (255) null,Category VARCHAR (255) null,newsid VARCHAR (35) not null default '0',newstime INT(10) not null default '0',Subject VARCHAR (255) null~;

	foreach $i (@fieldDB_internalorder){
		if ($i ne "newstime" && $i ne "newsid" && $i ne "Category" && $i ne "User" && $i ne "Subject"){
			
				if ($fieldDB{$i}->{'FieldType'} == 1){
				$sql_table_format .= ",$i VARCHAR (255) null";
				}
				elsif ($fieldDB{$i}->{'FieldType'} == 2){
				$sql_table_format .= ",$i longtext null";
				}
				elsif ($fieldDB{$i}->{'FieldType'} == 4){
				$sql_table_format .= ",$i VARCHAR (255) null";
				}
				else {
				$sql_table_format .= ",$i LONGBLOB null";
				}

		}
	}

########################################################
# Now to add PRIMARY KEYs and INDEXs
# Which speeds up any SELECT statements significantly.
########################################################

# Add a primary key based on "newsid"... this is faster than INDEX, because primary keys must be unique and no other record can contain the exact same value in "newsid"
$sql_table_format .= ",PRIMARY KEY(newsid)";

# Add a ordinary INDEX to "newstime". Not as good as a primary key, but much faster than not using anything at all.
$sql_table_format .= ",INDEX corantosql_indx (newstime)";

$sql_table_format .= ")\;";

# NOW, WE PRINT OUT THE CONTENTS OF $sthcc TO A FILE
#my $sqlstuff_fh = $addon->open(">$CConfig{'CorantoSQL_path'}/crsql_tableformat.pl");
#print $sqlstuff_fh "$sql_table_format";
#close($sqlstuff_fh);

	eval {
	$corantosql_dbh->do($sql_table_format);
	};
	if ($@){
	&CRcough("CorantoSQL couldn't create the database table. Error: $@");
	}

# HOOK: CorantoSQL_AddAField
if($Addons{'CorantoSQL_AddAField'}){my $w;foreach $w (@{$Addons{'CorantoSQL_AddAField'}}){my $addon=$w->[2];eval ${$w->[0]};AErr($addon,$@)if $@;};}

}
END_SUB

################################################
# PROCESS DB TRANSACTIONS
################################################

$Subs{'CorantoSQL_ProcessTransaction'} = <<'END_SUB';
sub CorantoSQL_ProcessTransaction {
my $sub = shift;

# This sub acts as a layer for the CorantoSQL_InsertDB and CorantoSQL_UpdateDB subs and allows for transaction support.
# So if there's a error during a insert/update, the DB is rolled back to a previously usable state.

	if ($CConfig{'CorantoSQL_supportsTransactions'} == "1"){
		eval {
		#$corantosql_dbh->begin_work();
		&{$sub}();
		$corantosql_dbh->commit();   # commit the changes if we get this far
		};

		if ($@){
		$corantosql_dbh->rollback(); # undo the incomplete changes
		&CRcough("CorantoSQL database transaction aborted because of $@. All changes to the DB have been reversed.");
		}
	}
	else {
		eval {
		&{$sub}();
		};
		if ($@){
		&CRcough("CorantoSQL database altercation aborted because of $@.");
		}
	}
}
END_SUB

$Subs{'CorantoSQL_ProcessTransactionSQL'} = <<'END_SUB';
sub CorantoSQL_ProcessTransactionSQL {
my $sql = shift;

	if ($CConfig{'CorantoSQL_supportsTransactions'} == "1"){
		eval {
		#$corantosql_dbh->begin_work();
		$corantosql_dbh->do($sql);
		$corantosql_dbh->commit();   # commit the changes if we get this far
		};

		if ($@){
		$corantosql_dbh->rollback(); # undo the incomplete changes
		&CRcough("CorantoSQL database transaction aborted because of $@. All changes to the DB have been reversed.");
		}
	}
	else {
		eval {
		$corantosql_dbh->do($sql);
		};
		if ($@){
		&CRcough("CorantoSQL database altercation aborted because of $@.");
		}
	}
}
END_SUB

#################################################

1;