sqlite schema editor?

Databases
1

I seem to remember there being a way to create a database schema using Xojo…
I’m used to using mysql and the database creation utilities that come along with it…
I take my schema and convert it to sql create statements…

I’m not sure that was the vision when Xojo was created but I’m now trying to create a database and schema in Xojo and would like to know how one designs the database within Xojo IDE?

If no such method exist is there another recommended practice for a simple database… even an in memory database.

2

“Insert” a DB to your project, and when you click the database file in the project explorer left-hand pane, the database editor will appear.

3

I recommend NOT using the built-in database editor. To put it lightly, it stinks.

Use any of the free, or inexpensive, or commercial editors available for your database of choice. It will ALWAYS have better features/options than what the Xojo db editor gives you.

4

if you want to use triggers in your database, I’ve only found Navicat to handle them properly
that is: being able to edit them once created, and that are not destroyed if you add or remove a field in the table…

5

thanks all.

6

Use this for SQLite:

tempSQL = "SELECT sql FROM sqlite_master WHERE type = 'table' AND name LIKE '" + myTable + "' " 'gets SQLite to tell you how to build the table. Using name = 'tablename' fails if it's the wrong case! rs = db.SQLSelect(tempSQL)

7

There you get links to sqlite.com (home) and Database.SQLExecute you have an example on how to create a new Table.

For basic needs, the documentation displays enough code to start.

Even if you want to decode a sqlite file, there are enough examples to do that (TableSchema, etc.)

But maybe I do not understand correctly your question.

8

My toolset is just: Xojo + Navicat.

9

I use the SQLite Manager add-on for Firefox.
Seems to be good enough for all Sqlite needs I’ve had.

10

[quote=209745:@Dirk Cleenwerck]I use the SQLite Manager add-on for Firefox.
Seems to be good enough for all Sqlite needs I’ve had.[/quote]
nice and free but you cannot edit a trigger with it, you must delete it and re-make it.
not user friendly in my opinion we are in 2015…

11

[quote=209778:@jean-yves pochez]nice and free but you cannot edit a trigger with it, you must delete it and re-make it.
not user friendly in my opinion we are in 2015…[/quote]
That has more to do with SQLite than the editor. You’ll find this limitation in most editors. In the ones you don’t, they’re copying to a temporary item, creating a new one and copying data in the background.

12

I intend a “real” sql “editor” to have such a handy function as not bothering me with a copy-paste…
it’s not that complicated
all in all it seems the developper does not use his/her software…

13

[quote=210400:@jean-yves pochez]I intend a “real” sql “editor” to have such a handy function as not bothering me with a copy-paste…
it’s not that complicated
all in all it seems the developper does not use his/her software…[/quote]

More years ellapse and more work I have to do to build a (better looking) application. As an example, how do you provide your user a way to set the Column Alignment in a ListBox ? Same apply for changing the ListBox Header, move a column full of data to the left / right / first / last / elsewhere etc. [I am not asking for an answer, I just give hints to explain what I mean]

14

More:

@ sqlite.com, we have the description of the sqlite file. But I do not found where to get the sqlite code to create the used tables, etc. (watch carefully any sqlite data base file… with a hex editor for example).

All I can think of is… to use TableSchema, FieldSchema and build these by hand (think “re-create them by hand”)…

As I saw, when Xojo does not have a feature, one have to search elsewhere for a Class or a Plugin or at least, built it by itself… even for basic features.

15

simply do an SQLExecute ( ) on the database and it should import your tables structure into a xojo database

16

I made a containercontrol with a listbox (i.e. a subclass of ListBox), 2 scrollbars and an info panel
then I made (a lot of) methods to mimic the built-in listbox but with my containercontrol
and adding all that was missing… mainly the ability to manage big list from a database, formatting columns, etc.
also I can drag&drop rows from two listboxes that have a database link 1-n or n-n between them
the list is bound to an sqlite or a postgres database and can be switched at the launch of the App.
it took me around a year to have it working, and now using it is a pleasure …:wink:

17

Jean-Yves:

nearly what I said: the information exists @ sqlite.com, but deep inside the web site. I probably have it in my hard disk (since I downlaoded all html files).

All I have to do now is to make a real quest (to find the info). The string to use is:

“Querying the database schema”

Tip: put it into quotes else sqlite.com search engine will return all found words (yes, “the” too…)

18

Yes, probaly, but the doc states for SQLExecute:

Do you forgot one part ?

19

if you want to do it in reverse, you can use any sql editor around there free or not, they export almost all of them the structure of the tables in a sql syntax. no need to dig into xojo and tableschema

20

I found it:

poll the table “sqlite_master” and you will get it.

The SQLCreate string is in the sql field.

sqlite_master contents is:
type
name
tbl_name
rootpage
sql

I will share asap an application that will help to understand all of these.