Component Mysql Script - Table Naming #12

New Issue

Closed

opened 2016-07-16 15:00:57 +00:00 by paolavness · 6 comments

paolavness commented

2016-07-16 15:00:57 +00:00

(Migrated from github.com)

Hello again Llewelyn,

So I'm adding the creation and dump of a countries mysql table in the component. What table naming conventions do I add so that it will pick up the Joomla! table prefix and component name when being installed?

Also, what the the protocol for dropping or updating the table content and structure in future updates? I'm assuming that this is added to the mysql script area to? IE, drop if exists, or update if exists?

Thank you,
Paola

Hello again Llewelyn, So I'm adding the creation and dump of a countries mysql table in the component. What table naming conventions do I add so that it will pick up the Joomla! table prefix and component name when being installed? Also, what the the protocol for dropping or updating the table content and structure in future updates? I'm assuming that this is added to the mysql script area to? IE, drop if exists, or update if exists? Thank you, Paola

Llewellyn commented

2016-07-16 15:11:57 +00:00

Hi Paola in this tutorial around 1:14:59 I do give a quick explanation about these options.

When you have reviewed that and still have questions let me know.

Hi Paola in [this tutorial](https://www.youtube.com/watch?v=CdSKSCTzmRA&list=PLQRGFI8XZ_wtGvPQZWBfDzzlERLQgpMRE&index=6) around 1:14:59 I do give a quick explanation about these options. When you have reviewed that and still have questions let me know.

paolavness commented

2016-07-16 15:19:24 +00:00

(Migrated from github.com)

Hi again,
I have placed the code here but the tutorial - or perhaps i missed it - does not speak about naming conventions for the table - so i have in

CREATE IF NOT EXISTS TABLE '#__mycomponent_countries'

The compile is working but the insert failing -

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS TABLE '#__mycomponent_countries'

Thank you,
Paola

Hi again, I have placed the code here but the tutorial - or perhaps i missed it - does not speak about naming conventions for the table - so i have in `CREATE IF NOT EXISTS TABLE '#__mycomponent_countries'` The compile is working but the insert failing - > You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS TABLE '#__mycomponent_countries' Thank you, Paola

Llewellyn commented

2016-07-16 15:30:51 +00:00

What I have found is that often the fields change, and then you need to go update the dump or the table selection.

Further it is hard for me to debug an issue if I can not see the code, sorry! But if you want met to take a closer look you will need to show me more of the code, not just the error.

About the conventions, if you open the install.mysql.utf8 file in administrator/components/com_mycomponent/sql you will see how component builder builds the tables. That is normally the convention. Next you could instead of the dump, first let component builder export the data for you, by making use of the table select option. Then once compiled open the above mentioned file to see the data that was exported by CB-tool dynamically.

What I have found is that often the fields change, and then you need to go update the dump or the table selection. Further it is hard for me to debug an issue if I can not see the code, sorry! But if you want met to take a closer look you will need to show me more of the code, not just the error. About the conventions, if you open the install.mysql.utf8 file in administrator/components/com_mycomponent/sql you will see how component builder builds the tables. That is normally the convention. Next you could instead of the dump, first let component builder export the data for you, by making use of the table select option. Then once compiled open the above mentioned file to see the data that was exported by CB-tool dynamically.

paolavness commented

2016-07-16 15:46:33 +00:00

(Migrated from github.com)

Hmm. Ok. So what I am understanding from what you are saying is that prefixing '#__' is possibly the correct way to get the component to auto-magically inset the joomla prefix -

so if i use '#__mycompment_countries', the table will be inserted with 'j35xy_mycompment_countries' (where 'j35xy' is the joomla table prefix.

So there's an error in my SQL.

Ok. This is helpful.

Hmm. Ok. So what I am understanding from what you are saying is that prefixing '#__' is possibly the correct way to get the component to auto-magically inset the joomla prefix - so if i use '#__mycompment_countries', the table will be inserted with 'j35xy_mycompment_countries' (where 'j35xy' is the joomla table prefix. So there's an error in my SQL. Ok. This is helpful.

Llewellyn commented

2016-07-16 15:51:02 +00:00

Yes, #__ is the correct blank prefix.

Yes, `#__` is the correct blank prefix.

paolavness commented

2016-07-16 16:05:58 +00:00

(Migrated from github.com)

Coolio thanks again, the bug was in the table name being in quotes. Wish, mysql versions!