Import SQL Data

Upload Custom SQL File - The SQL file upload tool in Osclass allows administrators to import and execute SQL scripts directly from the backoffice.

This feature is useful for making database modifications such as inserting new data, updating existing records, altering table structures, or optimizing database performance.

Import SQL Data

To upload a SQL file, follow these steps:

1. Click on the "Select file" button.
2. Choose a .sql file from your local storage.
3. Ensure that the SQL file contains valid statements that comply with the Osclass database structure.
4. Click the "Upload" button to execute the SQL commands.

Using Variables in SQL Files

When creating an SQL file, you can use two special variables that Osclass will replace automatically:

• /*TABLE_PREFIX*/ - This variable will be replaced with the table prefix set in Osclass (usually oc_ by default).
• /*LOCALE_CODE*/ - This variable will be replaced with the default language code configured in Osclass.

Using these variables ensures that your SQL commands are compatible with different installations of Osclass.

SQL Query Examples

Below are some common SQL operations you may perform when uploading a SQL file:

1. Insert Data

INSERT INTO /*TABLE_PREFIX*/t_item (pk_i_id, fk_i_user_id, fk_i_category_id, s_title, s_description, dt_pub_date) 
VALUES (1, 3, 5, 'Sample Listing', 'This is a sample classified listing', NOW());

2. Update Records

UPDATE /*TABLE_PREFIX*/t_item 
SET s_title = 'Updated Listing Title' 
WHERE pk_i_id = 1;

3. Delete Data

DELETE FROM /*TABLE_PREFIX*/t_item 
WHERE pk_i_id = 1;

4. Truncate Table (Remove All Data)

TRUNCATE TABLE /*TABLE_PREFIX*/t_item;

5. Alter Table (Modify Structure)

ALTER TABLE /*TABLE_PREFIX*/t_item 
ADD COLUMN s_extra_info TEXT AFTER s_description;

6. Replace Data (Insert or Update)

REPLACE INTO /*TABLE_PREFIX*/t_item (pk_i_id, fk_i_user_id, fk_i_category_id, s_title, s_description, dt_pub_date) 
VALUES (1, 3, 5, 'Replaced Listing', 'This listing has been updated or inserted', NOW());

7. Create Index for Performance Optimization

CREATE INDEX idx_item_title 
ON /*TABLE_PREFIX*/t_item (s_title);

Best Practices for Uploading SQL Files

• Always create a backup of your database before executing SQL queries.
• Ensure that your SQL syntax is correct to avoid execution errors.
• Use /*TABLE_PREFIX*/ to maintain compatibility across installations.
• Review the execution results in Osclass logs or database tools like phpMyAdmin.

Summary

The SQL file upload tool in Osclass provides administrators with a powerful way to modify and optimize their database efficiently. Using predefined variables and structured queries, users can manage their classified ads platform effectively while maintaining database integrity.