MySQL database backend and integrated console.

[marc]

I've finally finished the preliminary version of my database backend for Celestia.

Here are some screenshots to show what it is capable of.


http://mostlyharmless.sourceforge.net/p ... hots/3.jpg


http://mostlyharmless.sourceforge.net/p ... hots/4.jpg


http://mostlyharmless.sourceforge.net/p ... hots/5.jpg

And this is from the readme that comes with the installer.

Code: Select all

Mostly Harmless v 0.1

First up, it is not a game yet, it is identical to Celestia 1.3.0 but with the following features:

Embedded SQL star database of about 800,000 stars.
Integrated SQL console.
Ability to filter and mark the displayed stars using SQL select statements.

Those familiar with the previous version please note that the following features are not part of this release.
   Gravity
   Randomly generated solar systems.
   Thrusters
   Trajectory plotting.
They will be back though.

Requirements:
   Same as Celestia 1.3.0 but with :
      A little more memory.
         (one of the mySQL buffers is set to 64M to make the queries nice and fast)
      300MB of free hard drive space.


Installation :
   Run the installer. The installer will kick off the generation process which takes about an hour. 

Tutorial :

Here is a quick tutorial of how to use the SQL console once Mostly Harmless is running.

Press Ctrl+Enter to bring up the mySQL console. In this mode you can type any SQL statement.

Here is a simple statement, type the following:
   SELECT pi();
Note that you must have the semicolon on the end to execute the SQL statement.
If a statement is long it can be split up with the enter key.

Press Ctrl+Enter to go back to the SQL console.

Ctrl+A  This brings up the first part of a statement that will Mark/Unmark (toggle) the selected stars.

You should see the following:
mysql> SELECT hip FROM star

Complete the statement like so and press enter.
mysql> SELECT hip FROM star WHERE spectralClass='M' AND distFromSol<10;
The selected stars will be marked with little blue squares.
WARNING: Don't try and mark too many stars as it takes a long time. (I havn't yet waited long eneough to see all the stars marked.)

Bring up the console again and press [Ctrl+Z].
This brings up the last statement which is useful for fixing an error or unmarking stars.
Press enter to unmark the stars.

At startup MH only displays the 100,000 stars that come with celestia. The MH database is based on one of  Pascal Hartmann's databases of about 800,000 stars.

Bring up the console and press [Ctrl+S]. This enters the first part of a statement that will select which stars are displayed by celestia.
To display all the stars simply complete the statement with a ; and press enter.
This query will take a little while so be patient.

To go back to the default database:
   [Ctrl+Enter]
   [Ctrl+S]
Then type
   WHERE hip < 1000000;
   [Enter]

In the console Ctrl+Q,W,E,R,T,Y,U,I  will bring up some pre-set statements to play with.

In summary
Ctrl+Enter         Bring up the console.
Ctrl+Z          last statement
Ctrl+A         mark/unmark stars
Ctrl+S         select stars
Ctrl+Q,W,E,R,T,Y,U,I     preset statements.


SQL is very powerful and im sure that people will be able to come up with some very interesting statements.
If your interested in learning more about SQL have a read of chapter 4 of the MySQL reference manual.
http://www.mysql.com

Lastly. I have replaced the WWWinfo for a star with data from the database. Right click on a star and click Info to bring up all the info in a star's row.


Please post feedback to the mostly harmless forum.
http://mostlyharmless.sourceforge.net/mhforum

Or to the relavent Celestia forum thread.

Credits :
http://mostlyharmless.sourceforge.net/mhforum/viewtopic.php?t=32

Enjoy.


http://mostlyharmless.sourceforge.net


For those interested it uses ver 1.2 of Pascal Hartmann's stars.dat file to create the database. This can be changed.
It is based on Celestia 1.3.0.

The installer is a 25MB download which unpacks and generates automatically.

http://sourceforge.net/project/showfile ... _id=157016

Any comments are welcome.

[Rigel]

This looks very great !

Very good work !

I cannot directly use it because I'm on OS X, but it seems to be a fantastic and useful tool !

Pascal

[ElPelado]

sorry, but what is it? what have you done? i odnt understand

[marc]

Rigel, ive tried not to use too much OS specific code so with a little help eventually this might make its way to linux and OSX.

ElPelado
Ill try and explain by example.

So far this database has one table called 'star'
Each row in the database represents 1 star and contains a number of fields(columns).
These fields include:
hip -hippacaros catalog number
hd -hd catalog number
RA -right ascention
declination
parallax
absoluteMagnitude
parallaxError
starType (normal, white dwarf, black hole)
spectralClass
spectralSubClass
luminosityClass

A very simple query would be :

Code: Select all

SELECT * FROM star;


This would select all the information on all the stars in the database.

Just say you want Celestia to only display the stars which are similar to Betelgeuse. which is a M2 Ib class star.
You would enter a query similar to this:

Code: Select all

SELECT hip,hd,RA,declination,parallax,absoluteMagnitude,parallaxError,
starType-1,spectralClass-1,spectralSubClass,luminosityClass-1
FROM star WHERE spectralClass='M'
AND luminosityClass='Ib'
AND spectralSubClass<4;


The first part between the SELECT and WHERE tells the database what information to retrieve for each star. When it is like this my code will use it to display the stars. (the minus one is there as the mySQL enum type starts at one)

The bit after the WHERE tells the database which stars to retrieve information for.

There 24 stars that match this criteria, all the others dissapear.
This query takes 6.35 seconds on a very slow PII350 with 128M of ram. Considering that there are 800,000 stars in the database this is pretty fast.


Or just say you wanted to mark all the stars that have interesting names.

Code: Select all

SELECT hip FROM star WHERE
name NOT LIKE 'tyc%'
AND name NOT LIKE 'hip%'
AND name NOT LIKE 'hd%';

Again on a slow computer this query is quite fast at 12.18 seconds.
My code recognises the 'SELECT hip FROM star' part of the query and will mark the selected 3,312 stars with little blue squares.

If a query doesnt start with either this (use Ctrl+S)

Code: Select all

SELECT hip,hd,RA,declination,parallax,absoluteMagnitude,parallaxError,
starType-1,spectralClass-1,spectralSubClass,luminosityClass-1
FROM star

or this (use Ctrl+A)

Code: Select all

SELECT hip FROM star WHERE

then the output will be overlayed on the screen like the second screenshot. The query in this case was:
SELECT name,classification,systemType FROM star WHERE systemType='Binary';
(I've temporarily defined a binary star as any that have a neighbour closer that 0.1 LY)

You can even use it as a calculator.
SELECT 2 * 6;

In short it helps to create nice visualisations of the stars.

[Psykotik]

Fortunatly, I have some knowledge of SQl langage. To bad, that's a very strong know-how for everyone...

But it works well, I like it !

Keep the work, dude !

[marc]

Psykotik, I'm glad you like it. Please post any interesting SQL statements you come up with.

The next version is out.

Here is the Installer. (3MB)
http://sourceforge.net/project/showfile ... _id=158420

changes:
The installer is now only 3MB instead of 25MB.
The star marking process is 6 times faster.
Limited the no of stars that can be marked to a configurable number.
Added cut and paste capability to the SQL console.
All the stars are now unmarked when the displayed stars are changed.

The installer is now a patch for Celestia which brings the size down to only 3MB.
Just install it to your celestia directory.
It should not harm your celestia 1.3.0 installation, and will uninstall cleanly. (but backup anyway)

The database is based on whatever stars.dat you are using in your celestia installation.
I recommend getting one of Pascal Hartmann's stars.dat files and adding it to celestia before you install this patch.
http://perso.wanadoo.fr/celestia.stars/

please read the release notes :
http://sourceforge.net/project/shownote ... _id=158420

Comments welcome.

[marc]

Ive been doing some futher testing with Pascal's 2.1C star database. There are a few bugs when handling such a large number of stars. Im performing some more tweaking of the DB settings and some code changes which should have it working even faster and using less memory.

It will still work fine with the default stars.dat and probably even the 2.1A database.

[marc]

Some more screenshots.


http://mostlyharmless.sourceforge.net/p ... hots/9.jpg


http://mostlyharmless.sourceforge.net/p ... hots/8.jpg

[Gordon nolog]

Hi Marc,

I have tested your prg, it's great, thx.

I have some questions/suggestion :

1 - as installation process is quite long, could you give a minor priority to the process so the pc dosen't get too slow for other apps ?

2 - during installation and when I launched your patch; the programs tried to connect to internet. Do you know why ?

3 - in the database you didn't put the radius which I think could be usefull. Is it possible to add it ?

4 - would it be possible to have a windowed version for creating sql requests ?


bye

G.

[marc]

Gordon, im glad you like it and thanks for the feedback.
First up, can tell me what OS you are running, your processor specs and how much ram you have?
To answer your points:

1 - as installation process is quite long, could you give a minor priority to the process so the pc dosen't get too slow for other apps ?

How long did it take? If someone can show me a very easy way to do this that is cross platform compatible I will do it. As a workaround, in XP Pro you can right click on a process in the Task Manager to reduce its priority. As with any installation process its best to just leave it alone, lest windows crashes and you have to start again. Im still learning about MySQL and i think there are still some speed optimisations to come.

2 - during installation and when I launched your patch; the programs tried to connect to internet. Do you know why ?

This is a good one thanks for pointing it out. It is not intentional.
Can anyone else confirm this?
It must be either the embedded server starting up or the embedded client trying to connect to it. It is probably just trying to find out what the IP address of localhost is. From the command line could you run ping localhost and tell me if your PC tries to connect?
The next version will have --skip-networking specified when it starts up so this might fix it, though in the mySQL docs it says that the embedded server doesnt support networking.

3 - in the database you didn't put the radius which I think could be usefull. Is it possible to add it ?

Can do, it's now on my to do list. http://mostlyharmless.sourceforge.net/m ... c.php?p=82

4 - would it be possible to have a windowed version for creating sql requests ?

Im not exactly sure what you mean by this, but you can use the star database with a standard MySQL daemon and then use something like SQLYog to play with it. Doing this while using my patch can cause trouble though. I have added cut and paste to v1.1 so you can write the statements in notepad.

[ElPelado]

which file should i donwload from the page to install it??
i have windows 98 and celestia 1.3.0
what are the celestia patches??

[HB]

Hi, Marc,

One of the pictures shows some usefull information about the selected star.
Question1: Is there any SQL statement which will provide this information automatically after I choosen an star from the celestia list or just picked a star from the screen?
Question2: What is the exact SQL-statement to obtain this kind of information?

Anyway this approach of supplying information independent of Internet is a step in the right direction.

HB

[marc]

ElPelado

Sorry, the names ive given the packages are a bit confusing.
Download version 0.1.1 (celestia patch). It's just the database stuff and is installed alongside celestia.

Version 0.1.0 (mostly harmless) is an older version and contains both the db stuff and all the datafiles (textures etc) from celestia 1.3.0. Dont bother with it.

Previous patches did completly different things, gravity etc. They all have accompanying readmes.

HB.

Q1
Right click on a star and click info to bring up the data like it is in http://mostlyharmless.sourceforge.net/p ... hots/5.jpg.
I have been lazy and just overwritten the code which used to get the information from the internet. I should really make a new context menu item.

Q2
SELECT * FROM star WHERE hip = 0;
or
SELECT * FROM star WHERE name = 'Sol'

They all show the same information, the right click version just shows it in a format that will fit on the screen.

[Gordon]

Marc,

Quote:
2 - during installation and when I launched your patch; the programs tried to connect to internet. Do you know why ?
From the command line could you run ping localhost and tell me if your PC tries to connect?

I did it and my firewall (ZoneAlarm) say nothing, but as "ping" is allowed I am not sure it's relevant. I went on the log to see what was written :

here is the detail if this can help :
Rating : high
type : new program
source IP : nothing
program : celestia
destination : 212.198.0.91:53
direction : outgoing
source DNS : nothing
destination DNS : nothing

4 - would it be possible to have a windowed version for creating sql requests ?
Im not exactly sure what you mean by this ...

What I mean is that it's quite hard for a non specialist to write sql queries. So what I am thinking of as 'windowed' would be something where you could just choose in listbox the database for the from part, then fields for the select part, and simple condition for the where part and then create the sql query automatically.

NEW point :

The Mark shortcut "ctrl+a" :

- It was not obvious for me that it has to be done before doing the sql query.

- in my version (1.3.0) it also make me accelarate, you should maybe take another shortcut.


In any case, as it is now it's already a very cool prg. Thx.

G.

[marc]

Gordon

The networking issue has me stumped. Well see what happens with V0.1.2

What I mean is that it's quite hard for a non specialist to write sql queries. So what I am thinking of as 'windowed' would be something where you could just choose in listbox the database for the from part, then fields for the select part, and simple condition for the where part and then create the sql query automatically.

Implementing this would require a lot of work, and the SQL console will always be more powerful, users who put the effort in to learn a little SQL will be most satisfied. The main reason i made the console was to make later game development easier. The star marking and selection was a spinoff of this that was relativley easy to implement. (and fun) Ill be fixing the bugs and then will move on to the game related stuff.

The Mark shortcut "ctrl+a" :

- It was not obvious for me that it has to be done before doing the sql query.

- in my version (1.3.0) it also make me accelarate, you should maybe take another shortcut.

The next version will change the way this works a little. Im aware of the accelleration problem.

Thanks for the feedback.


Added much later..

version 0.1.2 is out. Please go to this thread. http://www.celestiaproject.net/forum/viewtopic ... 6458#16458

[Mikeydude750]

The search feature is really cool. Highlighting the stars like that is genius.

Maybe Chris will pick up on this hint...it would be a great feature.