SQL Table Structure and Adding Products/Categories from BASH Shell into Magento

[rip057]

edit:  please realize that this is a venture of myself into learning how the SQL database backend of Magento is formed in order to complete the titled task, I will post replies to myself of the knowledge that I have gained as I try to conquer this task, this is for my reference and also for anyone else trying to accomplish the same.

OK so let me preface this with I spent several weeks doing the scripting for the OFBiz implementation of the titled issue, I was able to add products/categories on the fly from a set of scripts, pretty easily, as to import things into OFBiz it follows an XML structure that is easily obtainable by just creating a sample item/inventory/imaging etc etc and exporting the system then sifting through the exported xml to determine what the structure and content of the import xml needs to be.

I was then introduced to Magento, and I just like the feel, plus OFBiz has a lot of backend that I dont feel i need yet.  I realize that there is a new OFBiz-Connect module made by Hotwax which could interface the two systems... but why have two systems instead of one

Here in lies my issue...  I am really lost with SQL, and am trying to understand the table structure

So I go back to the XML import on OFBiz, it needed certain things to be defined before other things could be defined and while it is java based it operates off of an SQL db.  

Adding an entry through bash to add a product, category, or images has got to be very simple as you can easily add an entry to a table through a sql script, but its the extra stuff that im not so sure about

any links, positive comments, or positive suggestions

ps. i know about magmi, and would much rather have a solid understanding of the table structure as it relates to the product entries so i could customize my own script 

[rip057]

so after exporting the database with mysqldump after inserting a PRODUCTA into a category tree of CATEGORYA-SUBCATEGORYB-SUBSUBCATEGORYC

i see that producta has been inserted into 

catalog product entity

catalog product entity varchar

core url rewrites

and the categories are inserted into the following:

catalog category entity text

catalog category entity varchar

what i dont get is what the many multinumbered comma seperated parentheses enclosed values are and what their constraints are...

I mean am i correct or no that if i add entries into those tables correctly i can add products through a command line application

[rip057]

so a little update, apparently the keys (which are the numbers preceeding text value in parentheses) are defined at the beginning of the table, and those seem easily enough to insert from a mysql command, and they describe what they are and what they reference too...  apparently i need to get a firm understanding of sql if i want to be able to add the items through command line interface and it doesnt seem that difficult, the structure of  `catalog_product_entity` is

`catalog_product_entity`

------------------------------------------------------------------------------------------------------------------------------------------

`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',

`entity_type_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type ID',
`attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Set ID',
`type_id` varchar(32) NOT NULL DEFAULT 'simple' COMMENT 'Type ID',
`sku` varchar(64) DEFAULT NULL COMMENT 'SKU',
`has_options` smallint(6) NOT NULL DEFAULT '0' COMMENT 'Has Options',
`required_options` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Required Options',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Creation Time',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'Update Time',

this means that there are nine columns in the table catalog_product_entity and the ones that can not be empty are defined by the NOT NULL statement, and the default values (if none is specified in the insertion intot the table) are listed for each column.  The entity_id (first column) if not specified on insertion of data into the table is auto incremented from the last one in the table, and following those definitions is a couple of other definitions that further define that table

PRIMARY KEY (`entity_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_ENTITY_TYPE_ID` (`entity_type_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_SKU` (`sku`),
CONSTRAINT `FK_CAT_PRD_ENTT_ATTR_SET_ID_EAV_ATTR_SET_ATTR_SET_ID` FOREIGN KEY (`attribute_set_id`) REFERENCES `eav_attribute_set` (`attribute_set_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_ENTT_TYPE_ID_EAV_ENTT_TYPE_ENTT_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
 

primary key specifies that the entity_id (first column of the table) needs to be a unique value different from all the others in the  table, the KEY definitions assign a relational name to a column and mark it for indexing to help the database, the CONSTRAINT FOREIGN KEY lines direct to another table specifically in this case 'eav_attribute_set' and 'eav_entity_type' , and causes the database to look at those tables and make sure the value input into this table exists in that table.

looking at table definition's for 'eav_attribute_set' and 'eav_entity_type'

'eav_entity_type'

------------------------------------------------------------------------------------------------------------------------------------------

`entity_type_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Type Id',
`entity_type_code` varchar(50) NOT NULL COMMENT 'Entity Type Code',
`entity_model` varchar(255) NOT NULL COMMENT 'Entity Model',
`attribute_model` varchar(255) DEFAULT NULL COMMENT 'Attribute Model',
`entity_table` varchar(255) DEFAULT NULL COMMENT 'Entity Table',
`value_table_prefix` varchar(255) DEFAULT NULL COMMENT 'Value Table Prefix',
`entity_id_field` varchar(255) DEFAULT NULL COMMENT 'Entity Id Field',
`is_data_sharing` smallint(5) unsigned NOT NULL DEFAULT '1' COMMENT 'Defines Is Data Sharing',
`data_sharing_key` varchar(100) DEFAULT 'default' COMMENT 'Data Sharing Key',
`default_attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Default Attribute Set Id',
`increment_model` varchar(255) DEFAULT '' COMMENT 'Increment Model',
`increment_per_store` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Increment Per Store',
`increment_pad_length` smallint(5) unsigned NOT NULL DEFAULT '8' COMMENT 'Increment Pad Length',
`increment_pad_char` varchar(1) NOT NULL DEFAULT '0' COMMENT 'Increment Pad Char',
`additional_attribute_table` varchar(255) DEFAULT '' COMMENT 'Additional Attribute Table',
`entity_attribute_collection` varchar(255) DEFAULT NULL COMMENT 'Entity Attribute Collection',
PRIMARY KEY (`entity_type_id`),
KEY `IDX_EAV_ENTITY_TYPE_ENTITY_TYPE_CODE` (`entity_type_code`)

'eav_attribute_set'

------------------------------------------------------------------------------------------------------------------------------------------

`attribute_set_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Attribute Set Id',

`entity_type_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
`attribute_set_name` varchar(255) DEFAULT NULL COMMENT 'Attribute Set Name',
`sort_order` smallint(6) NOT NULL DEFAULT '0' COMMENT 'Sort Order',
PRIMARY KEY (`attribute_set_id`),
UNIQUE KEY `UNQ_EAV_ATTRIBUTE_SET_ENTITY_TYPE_ID_ATTRIBUTE_SET_NAME` (`entity_type_id`,`attribute_set_name`),
KEY `IDX_EAV_ATTRIBUTE_SET_ENTITY_TYPE_ID_SORT_ORDER` (`entity_type_id`,`sort_order`),
CONSTRAINT `FK_EAV_ATTR_SET_ENTT_TYPE_ID_EAV_ENTT_TYPE_ENTT_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE

the values that I have for eav_attribute_set are 

(1,1,'Default',1),(2,2,'Default',1),(3,3,'Default',1),(4,4,'Default',1),(5,5,'Default',1),(6,6,'Default',1),(7,7,'Default',1),(8,8,'Default',1)

these value have attribute_set_id = entity_type_id  Im sure in other more complex systems these will differ but for all intents and purposes for me they will equal the same 

Looking at the values for table 'eav_entity_type' i have this as two of the rows (specifially the rows i am interested in)

(3,'catalog_category','catalog/category','catalog/resource_eav_attribute','catalog/category',NULL,NULL,1,'default',3,NULL,0,8,'0','catalog/eav_attribute','catalog/category_attribute_collection')

(4,'catalog_product','catalog/product','catalog/resource_eav_attribute','catalog/product',NULL,NULL,1,'default',4,NULL,0,8,'0','catalog/eav_attribute','catalog/product_attribute_collection')

so for the instance of inserting into the table 'catalog_product_entity' I would have attribute_set_id and entity_type_id set to 4 for each, so to insert PRODUCTA with sku of SKUA into the table `catalog_product_entity` I would need to issue the mySQL command

INSERT INTO `catalog_product_entity` (entity_id,attribute_set_id,sku) VALUES (4,4,'SKUA');

the rest of the values will just default feel free to fill them in I am going to change the default for the timestamp to getdate() but that is for later

well thats one table im going to take a break and try to understand the next table and how to interrelate it to the this first table

[rip057]

now looking at the table titled `catalog_product_entity_varchar` its definitions are as follows

`catalog_product_entity_varchar`
-------------------------------------------------------------------------------------------------------
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`entity_type_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type ID',
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
`store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
`entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
`value` varchar(255) DEFAULT NULL COMMENT 'Value',
PRIMARY KEY (`value_id`),
UNIQUE KEY `UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ATTRIBUTE_ID` (`attribute_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID` (`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID` (`entity_id`),
CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_VCHR_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE

This means to me that there are 6 columns.

The PRIMARY KEY value_id is unique to each entry into the table and must be different from all others, but this one just like the previous entity_id from table `catalog_product_entity` is automatically incremented and assigned the next number from the last number in the table.

The UNIQUE KEY definition provides that the key UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID is made up of the the three keys entity_id, attribute_id, and store_id and together should be unique over all the rows of the table.

The KEY definitions help build the index for better database functioning as before

The CONSTRAINT definitions reference the 'core_store', 'eav_attribute', and the before mentioned 'catalog_product_entity' tables

lets look at the 'core_store' and 'eav_attribute' tables

the 'core_store' is defined as

'core_store'
-------------------------------------------------------------------------------------------------------
`store_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Store Id',
`code` varchar(32) DEFAULT NULL COMMENT 'Code',
`website_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Website Id',
`group_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Group Id',
`name` varchar(255) NOT NULL COMMENT 'Store Name',
`sort_order` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Sort Order',
`is_active` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Activity',
PRIMARY KEY (`store_id`),
UNIQUE KEY `UNQ_CORE_STORE_CODE` (`code`),
KEY `IDX_CORE_STORE_WEBSITE_ID` (`website_id`),
KEY `IDX_CORE_STORE_IS_ACTIVE_SORT_ORDER` (`is_active`,`sort_order`),
KEY `IDX_CORE_STORE_GROUP_ID` (`group_id`),
CONSTRAINT `FK_CORE_STORE_GROUP_ID_CORE_STORE_GROUP_GROUP_ID` FOREIGN KEY (`group_id`) REFERENCES `core_store_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CORE_STORE_WEBSITE_ID_CORE_WEBSITE_WEBSITE_ID` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE

my values that are default from install are
(0,'admin',0,0,'Admin',0,1),(1,'default',1,1,'Default Store View',0,1)

so from that table you can get the number of the store you want to install the products to

lets look at the second table, 'eav_attribute'

'eav_attribute'
-------------------------------------------------------------------------------------------------------
`attribute_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Attribute Id',
`entity_type_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
`attribute_code` varchar(255) DEFAULT NULL COMMENT 'Attribute Code',
`attribute_model` varchar(255) DEFAULT NULL COMMENT 'Attribute Model',
`backend_model` varchar(255) DEFAULT NULL COMMENT 'Backend Model',
`backend_type` varchar(8) NOT NULL DEFAULT 'static' COMMENT 'Backend Type',
`backend_table` varchar(255) DEFAULT NULL COMMENT 'Backend Table',
`frontend_model` varchar(255) DEFAULT NULL COMMENT 'Frontend Model',
`frontend_input` varchar(50) DEFAULT NULL COMMENT 'Frontend Input',
`frontend_label` varchar(255) DEFAULT NULL COMMENT 'Frontend Label',
`frontend_class` varchar(255) DEFAULT NULL COMMENT 'Frontend Class',
`source_model` varchar(255) DEFAULT NULL COMMENT 'Source Model',
`is_required` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Defines Is Required',
`is_user_defined` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Defines Is User Defined',
`default_value` text COMMENT 'Default Value',
`is_unique` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Defines Is Unique',
`note` varchar(255) DEFAULT NULL COMMENT 'Note',
PRIMARY KEY (`attribute_id`),
UNIQUE KEY `UNQ_EAV_ATTRIBUTE_ENTITY_TYPE_ID_ATTRIBUTE_CODE` (`entity_type_id`,`attribute_code`),
KEY `IDX_EAV_ATTRIBUTE_ENTITY_TYPE_ID` (`entity_type_id`),
CONSTRAINT `FK_EAV_ATTRIBUTE_ENTITY_TYPE_ID_EAV_ENTITY_TYPE_ENTITY_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE

just like the previous table we are only concerned with one value, but i put the whole table to get a clearer more robust picture of what is going on. My values for that table that are of importance, and again these are the default values after only adding the one PRODUCTA and the 3 categories are

(103,4,'custom_design',NULL,NULL,'varchar',NULL,NULL,'select','Custom Design',NULL,'core/design_source_design',0,0,NULL,0,NULL)
(107,4,'page_layout',NULL,NULL,'varchar',NULL,NULL,'select','Page Layout',NULL,'catalog/product_attribute_source_layout',0,0,NULL,0,NULL)
(109,4,'options_container',NULL,NULL,'varchar',NULL,NULL,'select','Display Product Options In',NULL,'catalog/entity_product_attribute_design_options_container',0,0,'container1',0,NULL)
(112,4,'image_label',NULL,NULL,'varchar',NULL,NULL,'text','Image Label',NULL,NULL,0,0,NULL,0,NULL)
(113,4,'small_image_label',NULL,NULL,'varchar',NULL,NULL,'text','Small Image Label',NULL,NULL,0,0,NULL,0,NULL)
(114,4,'thumbnail_label',NULL,NULL,'varchar',NULL,NULL,'text','Thumbnail Label',NULL,NULL,0,0,NULL,0,NULL)
(117,4,'country_of_manufacture',NULL,NULL,'varchar',NULL,NULL,'select','Country of Manufacture',NULL,'catalog/product_attribute_source_countryofmanufacture',0,0,NULL,0,NULL)
(118,4,'msrp_enabled',NULL,'catalog/product_attribute_backend_msrp','varchar',NULL,NULL,'select','Apply MAP',NULL,'catalog/product_attribute_source_msrp_type_enabled',0,0,'2',0,NULL)
(119,4,'msrp_display_actual_price_type',NULL,'catalog/product_attribute_backend_boolean','varchar',NULL,NULL,'select','Display Actual Price',NULL,'catalog/product_attribute_source_msrp_type_price',0,0,'4',0,NULL)
(122,4,'gift_message_available',NULL,'catalog/product_attribute_backend_boolean','varchar',NULL,NULL,'select','Allow Gift Message',NULL,'eav/entity_attribute_source_boolean',0,0,NULL,0,NULL)
(71,4,'name',NULL,NULL,'varchar',NULL,NULL,'text','Name',NULL,NULL,1,0,NULL,0,NULL)
(82,4,'meta_title',NULL,NULL,'varchar',NULL,NULL,'text','Meta Title',NULL,NULL,0,0,NULL,0,NULL)
(83,4,'meta_keyword',NULL,NULL,'text',NULL,NULL,'textarea','Meta Keywords',NULL,NULL,0,0,NULL,0,NULL)
(84,4,'meta_description',NULL,NULL,'varchar',NULL,NULL,'textarea','Meta Description',NULL,NULL,0,0,NULL,0,'Maximum 255 chars')
(85,4,'image',NULL,NULL,'varchar',NULL,'catalog/product_attribute_frontend_image','media_image','Base Image',NULL,NULL,0,0,NULL,0,NULL)
(86,4,'small_image',NULL,NULL,'varchar',NULL,'catalog/product_attribute_frontend_image','media_image','Small Image',NULL,NULL,0,0,NULL,0,NULL)
(87,4,'thumbnail',NULL,NULL,'varchar',NULL,'catalog/product_attribute_frontend_image','media_image','Thumbnail',NULL,NULL,0,0,NULL,0,NULL)
(97,4,'url_key',NULL,'catalog/product_attribute_backend_urlkey','varchar',NULL,NULL,'text','URL Key',NULL,NULL,0,0,NULL,0,NULL)
(98,4,'url_path',NULL,NULL,'varchar',NULL,NULL,'text',NULL,NULL,NULL,0,0,NULL,0,NULL)

now again if you have added attributes im more than sure that they are numbered in the table, some of the above mentioned i didnt enter anything into those fields when i set up PRODUCTA, but again these are the defaults, and the above mentioned attributes are ones that there are references to in the table `catalog_product_entity_varchar`

now that I have looked at what table `catalog_product_entity_varchar` references lets look at what value i have for this table for ease of visibility I am putting the table values enclosed in parentheses and a little shorthand for what the eav_attribute table references are enclosed in brackets. Remember from the top of the post that these values are defined by

(`value_id`, `entity_type_id`, `attribute_id`, `store_id`, `entity_id`, `value`)

The value_id again is automatically incrementing so I dont need to be concerned with that at the moment
I cant figure out what entity_type_id either references to or is related to, but from
http://www.blog.magepsycho.com/magento-eav-structure-role-of-eav_attributes-backend_type-field/
there are 6 different types of entities text, int, varchar, decimal, static, and datetime, maybe 4 is for the varchar entity, needless to say its the only one I am concerned with in this table, so all entries in this table will be done using 4 for this value.
The attribute_id again is referenced in the brackets
The store_id is predominently the admin store, with the default store only being referenced for a url_path
The entity_id references the unique_id for the product from table `catalog_product_entity`
The final column is the actual value of the attribute

My table values from table `catalog_product_entity_varchar`
-------------------------------------------------------------------------------------------------------
(41,4,71,0,2,'PRODUCTA') [name]
(42,4,97,0,2,'producta') [url_key]
(43,4,117,0,2,NULL) [country_of_manufacture]
(44,4,118,0,2,'2') [msrp_enabled]
(45,4,119,0,2,'4') [msrp_display_actual_price_type]
(46,4,82,0,2,NULL) [meta_title]
(47,4,84,0,2,NULL) [meta_description]
(48,4,85,0,2,'/p/a/partabaseimage_1.jpg') [image]
(49,4,86,0,2,'/p/a/partasmallimage_1.jpg') [small_image]
(50,4,87,0,2,'/p/a/partathumbnail_1.jpg') [thumbnail]
(51,4,103,0,2,NULL) [custom_design]
(52,4,107,0,2,NULL) [page_layout]
(53,4,109,0,2,'container1') [options_container]
(54,4,122,0,2,NULL) [gift_message_available]
(55,4,98,1,2,'producta.html') [url_path]
(56,4,98,0,2,'producta.html') [url_path]
(63,4,112,0,2,NULL) [image_label]
(71,4,113,0,2,NULL) [small_image_label]
(80,4,114,0,2,NULL) [thumbnail_label]

now im not 100% sure, but i feel that you could just not insert into the table the things that are left blank, i.e. for instance in my case i dont have anything for country_of_manufacture, meta_title, etc, etc, but i for time being am going to setup my script to just add those lines anyhow for completeness, probably to modified later

for now i am going to stop, because i have ran into a road block.  I need to be able to call back to the product that I just inserted into the table `catalog_product_entity` because i need to reference the PRIMARY KEY from that table to use in this table, so I need to look up variables in sql or how to call a value from a key in one table into another table

[rip057]

so after studying some sql coding it seems that my first insertion was wrong I mistakenly placed the wrong column name, but when this is all through i will put the correct script up at the top to make this much simpler for viewing, but i digress...

the product I am adding is very simple and has the form of

Name: ProductA w/SKUA

Description: Description for ProductA w/SKUA

Short Description: Short Description for ProductA w/SKUA

SKU: SKUA

Weight: 5.000

Status: Enabled

URL Key: producta-w-skua   [it should be noted the system placed this in substituting - for the space and / in the name]

Visibility:  Catalog, Search

with picture uploaded named

parta w/skuathumbnailimage.jpg  that was converted to /p/a/parta_wskuathumbnailimage_1.jpg

parta w/skuabaseimage.jpg  that was converted to /p/a/parta_wskuabaseimage_1.jpg

parta w/skuasmallimage.jpg  that was converted to /p/a/parta_wskuasmallimage_1.jpg

the correct insertion that should be used to place a row in the table `catalog_product_entity` is

INSERT INTO `catalog_product_entity` (entity_type_id,attribute_set_id,sku,created_at,updated_at) VALUES (4,4,'SKUA',utc_timestamp(),utc_timestamp());

now upon placement of the product with SKUA in that table there is are several insertions into table `catalog_product_entity_varchar` along with a variable definition that get the last incremented id value which just happens to be the entity_id from the table `catalog_product_entity`

SET @last_insert_id_from_catalog_product_entity = LAST_INSERT_ID()

INSERT INTO `catalog_product_entity_varchar` (entity_type_id,attribute_id,store_id,entity_id,value) VALUES (4,71,0,@last_insert_id_from_catalog_product_entity,'PRODUCT w/SKUA')

INSERT INTO `catalog_product_entity_varchar` (entity_type_id,attribute_id,store_id,entity_id,value) VALUES (4,97,0,@last_insert_id_from_catalog_product_entity,'producta-w-skua')

INSERT INTO `catalog_product_entity_varchar` (entity_type_id,attribute_id,store_id,entity_id,value) VALUES (4,118,0,@last_insert_id_from_catalog_product_entity,'2')

INSERT INTO `catalog_product_entity_varchar` (entity_type_id,attribute_id,store_id,entity_id,value) VALUES (4,119,0,@last_insert_id_from_catalog_product_entity,'4')

INSERT INTO `catalog_product_entity_varchar` (entity_type_id,attribute_id,store_id,entity_id,value) VALUES (4,85,0,@last_insert_id_from_catalog_product_entity,'/p/a/parta_wskuabaseimage_1.jpg')

INSERT INTO `catalog_product_entity_varchar` (entity_type_id,attribute_id,store_id,entity_id,value) VALUES (4,86,0,@last_insert_id_from_catalog_product_entity,'/p/a/parta_wskuasmallimage_1.jpg')

INSERT INTO `catalog_product_entity_varchar` (entity_type_id,attribute_id,store_id,entity_id,value) VALUES (4,87,0,@last_insert_id_from_catalog_product_entity,'/p/a/parta_wskuathumbnailimage_1.jpg')

INSERT INTO `catalog_product_entity_varchar` (entity_type_id,attribute_id,store_id,entity_id,value) VALUES (4,109,0,@last_insert_id_from_catalog_product_entity,'container1')

INSERT INTO `catalog_product_entity_varchar` (entity_type_id,attribute_id,store_id,entity_id,value) VALUES (4,98,0,@last_insert_id_from_catalog_product_entity,'producta-w-skua.html')

INSERT INTO `catalog_product_entity_varchar` (entity_type_id,attribute_id,store_id,entity_id,value) VALUES (4,98,1,@last_insert_id_from_catalog_product_entity,'producta-w-skua.html')

this skips over the other values that are installed into the database with a null value upon the simple product creation, not sure if those are necessary, and will double check this later

now on to the table 'core_url_rewrites', but for now i will break to try and understand that table

[rip057]

I actually did some more checking and what i consider to be the primary table the one labelled catalog_product_entity

is referenced by all of these tables so if any of your options relate to these tables you will have to apply these steps to the appropriate categories

tables referencing catalog_product_entity

--------------------------------------------------------------------------------------------------------------

`catalog_category_product`
`catalog_category_product_index`
`catalog_compare_item`
`catalog_product_bundle_option`
`catalog_product_bundle_price_index`
`catalog_product_bundle_selection`
`catalog_product_enabled_index`
`catalog_product_entity_datetime`
`catalog_product_entity_decimal`
`catalog_product_entity_gallery`
`catalog_product_entity_group_price`
`catalog_product_entity_int`
`catalog_product_entity_media_gallery`
`catalog_product_entity_media_gallery_value`
`catalog_product_entity_text`
`catalog_product_entity_tier_price`
`catalog_product_entity_varchar`
`catalog_product_index_eav`
`catalog_product_index_eav_decimal`
`catalog_product_index_group_price`
`catalog_product_index_price`
`catalog_product_index_tier_price`
`catalog_product_link`
`catalog_product_option`
`catalog_product_relation`
`catalog_product_super_attribute`
`catalog_product_super_link`
`catalog_product_website`
`cataloginventory_stock_item`
`cataloginventory_stock_status`
`catalogrule_product`
`catalogrule_product_price`
`catalogsearch_result`
`core_url_rewrite`
`downloadable_link`
`downloadable_sample`
`product_alert_price`
`product_alert_stock`
`report_compared_product_index`
`report_viewed_product_aggregated_daily`
`report_viewed_product_aggregated_monthly`
`report_viewed_product_aggregated_yearly`
`report_viewed_product_index`
`sales_bestsellers_aggregated_daily`
`sales_bestsellers_aggregated_monthly`
`sales_bestsellers_aggregated_yearly`
`tag_relation`
`weee_discount`
`weee_tax`
`wishlist_item`

you can use this short bash command to get the tables individually out of a backup of the sql database

grep -A40 "CREATE\ TABLE.*"$1 backup.sql | grep -B40 -m 1 "UNLOCK\ TABLES"

this will show you the values from the table along with the constraints and definitions of the columns

the following tables are all dependant on the table catalog_category_entity

dependencies of catalog_category_entity

--------------------------------------------------------------------------------------------------------------

catalog_category_entity_datetime
catalog_category_entity_decimal
catalog_category_entity_int
catalog_category_entity_text
catalog_category_entity_varchar
catalog_category_product
catalog_category_product_index
core_url_rewrite

out of all the catalog_product_entity dependants, the only ones with actual values in the tables (ie. tables that arent blank) are

catalog_category_product
catalog_category_product_index
catalog_product_entity_datetime
catalog_product_entity_decimal
catalog_product_entity_int
catalog_product_entity_media_gallery
catalog_product_entity_media_gallery_value
catalog_product_entity_text
catalog_product_entity_varchar
catalog_product_index_website
catalog_product_link_type
catalog_product_website
cataloginventory_stock_status
core_url_rewrite
report_event_types

the dependancies of catalog_category_entity table with actual values are

catalog_category_entity_datetime
catalog_category_entity_decimal
catalog_category_entity_int
catalog_category_entity_text
catalog_category_entity_varchar
catalog_category_product
catalog_category_product_index
core_url_rewrite

so i will at least have to go through the entered values in each table and see how they are dependant as to bulk add products into magento i will at least need to add to all of those tables for products

[rip057]

well many of the tables seem to reference not on the product entity id, but also the category entity id, so we should probably enter the category entity into the system immediately after or before the product entity looking at the table catalog_category_entity it has definitions

catalog_category_entity

---------------------------------------------------------------------------------------------------------------------------

`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
`entity_type_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type ID',
`attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attriute Set ID',
`parent_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Parent Category ID',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Creation Time',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'Update Time',
`path` varchar(255) NOT NULL COMMENT 'Tree Path',
`position` int(11) NOT NULL COMMENT 'Position',
`level` int(11) NOT NULL DEFAULT '0' COMMENT 'Tree Level',
`children_count` int(11) NOT NULL COMMENT 'Child Count',
PRIMARY KEY (`entity_id`),
KEY `IDX_CATALOG_CATEGORY_ENTITY_LEVEL` (`level`),
KEY `IDX_CATALOG_CATEGORY_ENTITY_PATH_ENTITY_ID` (`path`,`entity_id`)

the values i have for this table are 

(1,3,0,0,'2015-04-26 20:03:19','2015-04-26 20:03:19','1',0,0,4)  <-- this is the absolute root category and all categories are a part of it

(2,3,3,1,'2015-04-26 20: 03:19','2015-04-26 20:03:19','1/2',1,1,0) <-- this is the default category

(6,3,3,1,'2015-04-27 04:41:05','2015-04-27 04:53:37','1/6',2,1,2)  <-- this is CATEGORYA I created

(7,3,3,6,'2015-04-27 04:43:21','2015-04-27 04:53:53','1/6/7',1,2,1) <-- this is SUBCATEGORYB I created

(8,3,3,7,'2015-04-27 04:45:01','2015-04-27 04:54:09','1/6/7/8',1,3,0) <-- this is SUBSUBCATEGORYC I created

to automate the addition of something to this tables there needs to be a whlie loop so i set up a small procedure and script, now mind you i have set this to insert a category underneath parent category SUBSUBCATEGORYC

delimiter $$
create procedure updatechildcount()
begin
while @entityid_ofparentcategory > 0 do
update catalog_category_entity set children_count=children_count+1 where entity_id = @entityid_ofparentcategory;
set @entityid_ofparentcategory = (select parent_id from catalog_category_entity where entity_id = @entityid_ofparentcategory);
end while;
end $$
delimiter ;

set @entityid_ofparentcategory = (select entity_id from catalog_category_entity_varchar where value='SUBSUBCATEGORYC' and attribute_id='41');
set @path_ofparentcategory = (select path from catalog_category_entity where entity_id = @entityid_ofparentcategory);
set @position_of_category_to_insert = (select count(*) from catalog_category_entity where path = @path_ofparentcategory);
set @level_of_parent_category = (select level from catalog_category_entity where entity_id = @entityid_ofparentcategory);
set @level_of_category_to_insert = @level_of_parent_category + 1;
INSERT INTO catalog_category_entity (entity_type_id,attribute_set_id,parent_id,created_at,updated_at,position,level,children_count) VALUES (3,3,@entityid_ofparentcategory,utc_timestamp(),utc_timestamp(),@position_of_category_to_insert,@level_of_category_to_insert,0);
set @combinedpath = (select concat( @path_ofparentcategory, '/' , last_insert_id()));
update catalog_category_entity set path=@combinedpath where entity_id=last_insert_id();
call updatechildcount()

this solves the automation of the addition of the catalog_category_entity table, I havent quite figured out the next step but i will... this seems to be quite the hairy task, but once its done for me at least i wont have to, but since all of the tables that have foreign key restraints on the primary table catalog_category_entity i have entries in, I will address that shorter list first