Priority of skill development

[Davidk]

Re: Priority of skill development

Still wanting to know why you feel what Nils wrote is a joke.

Nils wrote a considered, factual opinion. Not sure why it would be thought of as a joke.

[NBoje]

Re: Priority of skill development

Kenneth,

I can understand you really like SQLite and that it works well for you, and that's great, but it doesn't change the facts about what SQLite was designed for.

It's best stated on the product website:

https://sqlite.org/whentouse.html

SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.

Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.

SQLite does not compete with client/server databases. SQLite competes with fopen().


RDMBS products like Oracle, SQL Server, DB2 etc. are designed to handle concurrent reads AND writes, and easily supports a very large number of concurrent write operations

Sqlite is not really built for this, as they also state temselves on the website, and the comparison to Access with regards to architechture is valid, it's the same approach that was used by DBase as well, and nearly all other file based database of the past.

I am fully aware that Sqlite can perform better than Access for multiple read only users in a web application, but that,s not the point, the architechure is basically the same.

I understand also that you feel that Sqlite is easier to setup and use, no complicated backups, no stored procedures, etc, and that's probably true, like it's true that's it's easier to drive a family car as opposed to an 18 wheeler truck, but both still have a purpose.

Rob asked how to build a usefull skillset for Alpha, not what is the easiest database I can use where I don't have to learn anything hard.

It really depends on what kind if work you do and what clients you have.

A fully featured RDBMS may be harder to learn, which is why most companies have full time or remote DBA's, but you as a developer can provision a fully loaded Windows server with Sql Server 2016 Express on Amazon AWS in about 10 minutes with NO knowledge of Sql server, and it's ready to use, and will cost you maybe 50 USD each month, and you can use that for as many applications as you want, or you can use Amazon RDS with MariaDb or similar.

If you have any questions on using Sql server or other database systems with Alpha, just start a new thread and ask, and I will do my best to answer there

Brgds
Nils

Sql Server development and DBA services since 1995

[kkfin]

Re: Priority of skill development

Hi Nils,

If you did read my posts you can see that I in every post did tell that SQLite is not suitable for traditional client/server solution where clients has direct access to database. But it is very suitable for application server like Alpha or for Web Server as a backend database. It will beat any SQL client/server database in these solutions.

I hope you Nils undestand the difference. When SQLIte is as a backend server for Alpha or Web server it is not a client/server solution. "Database requests are serialized by the server, so concurrency is not an issue." Happy learning to you.

[iRadiate]

Re: Priority of skill development

Rob indicated that he has not yet started using any SQL product, and I think Kenneth was just offering a valid and optional choice. It all comes down to choices and application requirements.
From looking at the 'whentouse' link above, everything that Kenneth was saying is valid so I think it's just a matter of keeping an open mind and deciding how your application is going to be used.

I use MS SQL myself but I can see where SQLite could offer advantages if your application did not have a large number of concurrent users, did not have a large dataset, or if the content resides on the same physical machine as the application server.

[Peter.Greulich]

Re: Priority of skill development

I think Kenneth makes some very good points. I bet that a lot of Alpha developers would be well served with SQLite. I can see now that for many applications I built SQLite would have probably been a better choice overall. And 100,000 (or 1,000,000) hits per day vastly exceeds what most Alpha applications would even remotely approach (that's a wild guess off the top of my head - am I wrong? I seriously doubt it). My biggest application in terms of number of users is about 200 users per day. I suspect SQLite could handle it.

Rob says:

Great info; thanks! I have a very small number of customers who use my system to schedule swimming lessons, so this may not apply. ...but still good to know.

"very small number of customers who use my system" - Sounds like SQLite would be perfect for him.

As Kenneth said:

Both MS SQL Server and MySQL and other siblings are without no doubt good choices.

He didn't say MS SQL Server was no good. MySQL, SQL Server and Oracle (and really, MariaDB) are giants in the industry. People have their preferences as you can see in these posts. Rob (or anyone) won't go wrong if he uses SQL Server or MariaDB. But if it were me, and I only had a handful of users, I likely would try SQLite - even though all my projects are MySQL/MariaDB.

[NBoje]

Re: Priority of skill development

I certainly agree that Sqlite has it use in several applications, but the statement from Kenneth is just confusing and mainly incorrect.

"One interesting thing about SQL databases. SQL Server or MySQL and other traditional sql servers are actually client/server databases. So every SQL send to server makes a round-trip to server. There is then a lot of latency with each SQL command. These servers try to overcome this latency with stored procedure concept (single round-trip).

But SQLite does not have stored procedures. It does not need them. Every SQL statement is a stored procedure. No network traffic, not IPC so very little latency. So SQLite can handle traffic that would kill client/server database performance."


If you put a traditional RDBMS like sql server on your webserver, you will also have zero latency and no network traffic, and unless you have a large database, all data will soon reside in the memory cache, and then I doubt very much that there is any performance difference in read operations between the 2 products.
You don't need to use stored procedures in a Sql Database, it's an option, just as you don't need to use views, it's also an option.
You can get much better performance and easier development by using all the features of the database, but it's not needed.

In real life the latency between web servers and the database server is often very small, you normally put the servers in the same regional data center, and then you have internal server to server network connections with minimum 1, 10 Gbit or more.

Using Sqlite as a web server datacache is probably much better than using the file system, but it also prohibits the use of multiple webservers for redundancy and load balancing, a requirement for many corporate clients

Instead of discussing various database products, I would recommend that any Alpha developer learns the basic concepts of a Sql based database, tables, indexes, views, primary keys, foreign keys etc. They are all needed and useful in an Alpha application, regardless of which database is used, be that Sqlite, Sql server or Mysql etc.

[NBoje]

Re: Priority of skill development

Hi Nils,

If you did read my posts you can see that I in every post did tell that SQLite is not suitable for traditional client/server solution where clients has direct access to database. But it is very suitable for application server like Alpha or for Web Server as a backend database. It will beat any SQL client/server database in these solutions.

I hope you Nils undestand the difference. When SQLIte is as a backend server for Alpha or Web server it is not a client/server solution. "Database requests are serialized by the server, so concurrency is not an issue." Happy learning to you.

Kenneth,

I do understand the difference, I just think you are wrong in your comparisons and statements.

Especially here "Database requests are serialized by the server, so concurrency is not an issue"
Concurrency is always an issue, also for Sqlite, just read the section on WAL,

https://sqlite.org/wal.html

And as you propably agree, Sqlite also gets quite complicated very fast if you need concurrency


Happy learning to you as well

Nils

[Davidk]

Re: Priority of skill development

Nils... there are only a couple of people posting in this thread who have actually used SQLite. I've only ever used it for mobile apps... and it was perfect... but I'd doubt I'd use it for anything else. None of the client work I've done over the last number of years ever called for SQLite. It was always SQL Sever, MySQL, and a bit of MariaDB.

If you choose one of the more traditional sql servers you'll get a lot more assistance in this forum if you have questions regarding the use of sql with Alpha.

[NBoje]

Re: Priority of skill development

Nils... there are only a couple of people posting in this thread who have actually used SQLite. I've only ever used it for mobile apps... and it was perfect... but I'd doubt I'd use it for anything else. None of the client work I've done over the last number of years ever called for SQLite. It was always SQL Sever, MySQL, and a bit of MariaDB.

If you choose one of the more traditional sql servers you'll get a lot more assistance in this forum if you have questions regarding the use of sql with Alpha.

Hi David,

Yes, that's probably true, I would also only consider using it on a mobile device, and that's fairly easy

I don't really need support on neither SQL or Alpha, just wanted to clarify some of the issues on Sql and Alpha.

I have worked on a few projects during the last 4 years where we had different web applications and windows applications use our databases, and it was so much easier to code all the applications, including Alpha, when you could use the common views, stored procedures and functions between the applications.

Brgds
Nils

[kkfin]

Re: Priority of skill development

Kenneth,

I do understand the difference, I just think you are wrong in your comparisons and statements.

Especially here "Database requests are serialized by the server, so concurrency is not an issue"
Concurrency is always an issue, also for Sqlite, just read the section on WAL,

https://sqlite.org/wal.html

And as you propably agree, Sqlite also gets quite complicated very fast if you need concurrency


Happy learning to you as well

Nils

Hi Nils,

I can see that your learning process continues. I am for sure not wrong. The quotation "Database requests are serialized by the server, so concurrency is not an issue" is from SQLite web page so I do not think they lie. Concurrency is not a problem at all when SQLite is used as a backend database for application server or web server. Also if you think client/server model it is just a problem when different clients use same time directly SQLite database. Here clients does not mean customers but a software solution. Directly means that there in not an Application Server between clients and database. So it is a problem in multi tenant solution but not if you use separate databases for customers. This is case for example in desktop applications.

Facts are facts then there are opinions. The fact that not many Alpha users do not use SQLite does just mean that they do not know SQLite. SQLite is a new thing in Alpha AND it is also for him. What comes to his opinion I am sure that still about six years or something he thought that DBF is the best database in the world.

[menehune]

Re: Priority of skill development

I'm just beginning to learn SQL, but none of my projects use SQL yet.

Learning SQL really is about learning concepts like primary/foreign keys, table joins, views and such. Read some tutorials, then fiddle with data, preferably a duplicate set of tables from your swim lesson app.

One tool you already have is the Alpha SQL Genie in the Web Control Panel. Try loading the AppWebServer Demo and use the Northwind connection which links to standard mdb files on your PC, no installation of other software needed. Create select statements using single and multiple tables and Alpha will show the portable SQL syntax as well as an option to see native syntax for various DBMS. Tweak the results with column aliases, filters and ordering.

Anything more complex like insert-update-deletes you can create a simple grid or UX with the option to show the SQL being executed in a message box. For learning views/aggregates/triggers/stored procedures/grant-deny privileges/replication, etc. you'll have to go the DBMS installation route.

Learn SQL syntax first. It will help the most down the road if you move data out of dbf files or start a new project from scratch. SQL statements wrapped in xbasic will make more sense too.

[NBoje]

Re: Priority of skill development

Hi Kenneth

Of course, one should always be learning.

in this case I am afraid that you either simple do not understand what Sqlite is and more important what it isn't?

It's not a a database designed for concurrency, the statement on the Sqlite website merely states that if you use the database and have more concurrent writes than can be handled, you just hope for the best. If you doubt this then read the section on Write-Ahead Logging (WAL)

It's also not designed for business websites that requires redundancy and load balancing.

That doesn't mean it's a bad choice if it fulfil your needs, my point is just that different solutions have different needs, and I agree that a lot of smaller Alpha web solutions can probably use Sqlite as an application server datacache / database, but if you move on to designing larger business or Enterprise solutions, that may be business critical, you will need something else.

I think I will end the discussion here and get back to work :-)

Best regards
Nils

[menehune]

Re: Priority of skill development

The fact that not many Alpha users do not use SQLite does just mean that they do not know SQLite. SQLite is a new thing in Alpha...

Ken, my company's license expired a while back...before SQLite options. Out of curiosity in the Desktop version can you create an active link table to a SQLite datasource? It looks like Alpha compiled the driver source rather than use the public ODBC.

[kkfin]

Re: Priority of skill development

Things has not changed. In fact you can create an active link table but you can not update/delete/add. However with older (and current) version of Alpha you can use OBDC driver for SQLite but the same problems with linked tables exists. I currently use the ODBC driver to modify/view data in Alphas security tables in server with an desktop application (not alpha) I have build. Thanks to SQLite I can do it easily.

Edit: Sidenote: With older Alpha(and current) you can use Xdialog which has Chrome plugin available. This means that everything you see in web (frontend) you can bring to Alpha. This with SQLite opens hole new world in Desktop development. Here is little example I did when testing Chrome plugin in Xdialog.

[kkfin]

Re: Priority of skill development

Little update to my post above. I am currently testing in Alpha desktop side a third party web server to handle data with Alphas XDialog and using Northwind SQLite as datasource. I did notice that actually this ODBC driver for SQLite (Werners) works without errors(grud) using also Alphas active link tables. My current Alpha version is 4.5.4.0.